View | Details | Raw Unified | Return to bug 5339
Collapse All | Expand All

(-)a/installer/data/mysql/kohastructure.sql (-2 / +24 lines)
Lines 2631-2637 CREATE TABLE `aqorders` ( Link Here
2631
  `listprice` decimal(28,6) default NULL,
2631
  `listprice` decimal(28,6) default NULL,
2632
  `totalamount` decimal(28,6) default NULL,
2632
  `totalamount` decimal(28,6) default NULL,
2633
  `datereceived` date default NULL,
2633
  `datereceived` date default NULL,
2634
  `booksellerinvoicenumber` mediumtext,
2634
  invoiceid int(11) default NULL,
2635
  `freight` decimal(28,6) default NULL,
2635
  `freight` decimal(28,6) default NULL,
2636
  `unitprice` decimal(28,6) default NULL,
2636
  `unitprice` decimal(28,6) default NULL,
2637
  `quantityreceived` smallint(6) NOT NULL default 0,
2637
  `quantityreceived` smallint(6) NOT NULL default 0,
Lines 2663-2669 CREATE TABLE `aqorders` ( Link Here
2663
  KEY `biblionumber` (`biblionumber`),
2663
  KEY `biblionumber` (`biblionumber`),
2664
  KEY `budget_id` (`budget_id`),
2664
  KEY `budget_id` (`budget_id`),
2665
  CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2665
  CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2666
  CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2666
  CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2667
  CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE
2667
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2668
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2668
2669
2669
2670
Lines 2680-2685 CREATE TABLE `aqorders_items` ( Link Here
2680
  KEY `ordernumber` (`ordernumber`)
2681
  KEY `ordernumber` (`ordernumber`)
2681
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2682
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2682
2683
2684
2685
--
2686
-- Table structure for table aqinvoices
2687
--
2688
2689
DROP TABLE IF EXISTS aqinvoices;
2690
CREATE TABLE aqinvoices (
2691
  invoiceid int(11) NOT NULL AUTO_INCREMENT,    -- ID of the invoice, primary key
2692
  invoicenumber mediumtext NOT NULL,    -- Name of invoice
2693
  booksellerid int(11) NOT NULL,    -- foreign key to aqbooksellers
2694
  shipmentdate date default NULL,   -- date of shipment
2695
  billingdate date default NULL,    -- date of billing
2696
  closedate date default NULL,  -- invoice close date, NULL means the invoice is open
2697
  shipmentcost decimal(28,6) default NULL,  -- shipment cost
2698
  shipmentcost_budgetid int(11) default NULL,   -- foreign key to aqbudgets, link the shipment cost to a budget
2699
  PRIMARY KEY (invoiceid),
2700
  CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE,
2701
  CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE
2702
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2703
2704
2683
--
2705
--
2684
-- Table structure for table `fieldmapping`
2706
-- Table structure for table `fieldmapping`
2685
--
2707
--
(-)a/installer/data/mysql/updatedatabase.pl (-1 / +60 lines)
Lines 4932-4937 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { Link Here
4932
    SetVersion($DBversion);
4932
    SetVersion($DBversion);
4933
}
4933
}
4934
4934
4935
$DBversion = "XXX";
4936
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4937
    $dbh->do("
4938
        CREATE TABLE aqinvoices (
4939
          invoiceid int(11) NOT NULL AUTO_INCREMENT,
4940
          invoicenumber mediumtext NOT NULL,
4941
          booksellerid int(11) NOT NULL,
4942
          shipmentdate date default NULL,
4943
          billingdate date default NULL,
4944
          closedate date default NULL,
4945
          shipmentcost decimal(28,6) default NULL,
4946
          shipmentcost_budgetid int(11) default NULL,
4947
          PRIMARY KEY (invoiceid),
4948
          CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE,
4949
          CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE
4950
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8
4951
    ");
4952
4953
    # Fill this new table with existing invoices
4954
    my $sth = $dbh->prepare("
4955
        SELECT aqorders.booksellerinvoicenumber AS invoicenumber, aqbasket.booksellerid
4956
        FROM aqorders
4957
          LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
4958
        WHERE aqorders.booksellerinvoicenumber IS NOT NULL
4959
          AND aqorders.booksellerinvoicenumber != ''
4960
        GROUP BY aqorders.booksellerinvoicenumber
4961
    ");
4962
    $sth->execute;
4963
    my $results = $sth->fetchall_arrayref({});
4964
    $sth = $dbh->prepare("
4965
        INSERT INTO aqinvoices (invoicenumber, booksellerid) VALUES (?,?)
4966
    ");
4967
    foreach(@$results) {
4968
        $sth->execute($_->{'invoicenumber'}, $_->{'booksellerid'});
4969
    }
4970
4971
    # Add the column in aqorders, fill it with correct value
4972
    # and then drop booksellerinvoicenumber column
4973
    $dbh->do("
4974
        ALTER TABLE aqorders
4975
        ADD COLUMN invoiceid int(11) default NULL AFTER booksellerinvoicenumber,
4976
        ADD CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE
4977
    ");
4978
4979
    $dbh->do("
4980
        UPDATE aqorders, aqinvoices
4981
        SET aqorders.invoiceid = aqinvoices.invoiceid
4982
        WHERE aqorders.booksellerinvoicenumber = aqinvoices.invoicenumber
4983
    ");
4984
4985
    $dbh->do("
4986
        ALTER TABLE aqorders
4987
        DROP COLUMN booksellerinvoicenumber
4988
    ");
4989
4990
    print "Upgrade to $DBversion done (Add aqinvoices table) \n";
4991
    SetVersion ($DBversion);
4992
}
4993
4994
4935
=head1 FUNCTIONS
4995
=head1 FUNCTIONS
4936
4996
4937
=head2 DropAllForeignKeys($table)
4997
=head2 DropAllForeignKeys($table)
4938
- 

Return to bug 5339