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

(-)a/installer/data/mysql/kohastructure.sql (-2 / +24 lines)
Lines 2709-2715 CREATE TABLE `aqorders` ( Link Here
2709
  `listprice` decimal(28,6) default NULL,
2709
  `listprice` decimal(28,6) default NULL,
2710
  `totalamount` decimal(28,6) default NULL,
2710
  `totalamount` decimal(28,6) default NULL,
2711
  `datereceived` date default NULL,
2711
  `datereceived` date default NULL,
2712
  `booksellerinvoicenumber` mediumtext,
2712
  invoiceid int(11) default NULL,
2713
  `freight` decimal(28,6) default NULL,
2713
  `freight` decimal(28,6) default NULL,
2714
  `unitprice` decimal(28,6) default NULL,
2714
  `unitprice` decimal(28,6) default NULL,
2715
  `quantityreceived` smallint(6) NOT NULL default 0,
2715
  `quantityreceived` smallint(6) NOT NULL default 0,
Lines 2741-2747 CREATE TABLE `aqorders` ( Link Here
2741
  KEY `biblionumber` (`biblionumber`),
2741
  KEY `biblionumber` (`biblionumber`),
2742
  KEY `budget_id` (`budget_id`),
2742
  KEY `budget_id` (`budget_id`),
2743
  CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2743
  CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2744
  CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2744
  CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2745
  CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE
2745
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2746
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2746
2747
2747
2748
Lines 2758-2763 CREATE TABLE `aqorders_items` ( Link Here
2758
  KEY `ordernumber` (`ordernumber`)
2759
  KEY `ordernumber` (`ordernumber`)
2759
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2760
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2760
2761
2762
2763
--
2764
-- Table structure for table aqinvoices
2765
--
2766
2767
DROP TABLE IF EXISTS aqinvoices;
2768
CREATE TABLE aqinvoices (
2769
  invoiceid int(11) NOT NULL AUTO_INCREMENT,    -- ID of the invoice, primary key
2770
  invoicenumber mediumtext NOT NULL,    -- Name of invoice
2771
  booksellerid int(11) NOT NULL,    -- foreign key to aqbooksellers
2772
  shipmentdate date default NULL,   -- date of shipment
2773
  billingdate date default NULL,    -- date of billing
2774
  closedate date default NULL,  -- invoice close date, NULL means the invoice is open
2775
  shipmentcost decimal(28,6) default NULL,  -- shipment cost
2776
  shipmentcost_budgetid int(11) default NULL,   -- foreign key to aqbudgets, link the shipment cost to a budget
2777
  PRIMARY KEY (invoiceid),
2778
  CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE,
2779
  CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE
2780
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2781
2782
2761
--
2783
--
2762
-- Table structure for table `fieldmapping`
2784
-- Table structure for table `fieldmapping`
2763
--
2785
--
(-)a/installer/data/mysql/updatedatabase.pl (-1 / +60 lines)
Lines 5036-5041 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { Link Here
5036
    SetVersion($DBversion);
5036
    SetVersion($DBversion);
5037
}
5037
}
5038
5038
5039
$DBversion = "XXX";
5040
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
5041
    $dbh->do("
5042
        CREATE TABLE aqinvoices (
5043
          invoiceid int(11) NOT NULL AUTO_INCREMENT,
5044
          invoicenumber mediumtext NOT NULL,
5045
          booksellerid int(11) NOT NULL,
5046
          shipmentdate date default NULL,
5047
          billingdate date default NULL,
5048
          closedate date default NULL,
5049
          shipmentcost decimal(28,6) default NULL,
5050
          shipmentcost_budgetid int(11) default NULL,
5051
          PRIMARY KEY (invoiceid),
5052
          CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE,
5053
          CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE
5054
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8
5055
    ");
5056
5057
    # Fill this new table with existing invoices
5058
    my $sth = $dbh->prepare("
5059
        SELECT aqorders.booksellerinvoicenumber AS invoicenumber, aqbasket.booksellerid
5060
        FROM aqorders
5061
          LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
5062
        WHERE aqorders.booksellerinvoicenumber IS NOT NULL
5063
          AND aqorders.booksellerinvoicenumber != ''
5064
        GROUP BY aqorders.booksellerinvoicenumber
5065
    ");
5066
    $sth->execute;
5067
    my $results = $sth->fetchall_arrayref({});
5068
    $sth = $dbh->prepare("
5069
        INSERT INTO aqinvoices (invoicenumber, booksellerid) VALUES (?,?)
5070
    ");
5071
    foreach(@$results) {
5072
        $sth->execute($_->{'invoicenumber'}, $_->{'booksellerid'});
5073
    }
5074
5075
    # Add the column in aqorders, fill it with correct value
5076
    # and then drop booksellerinvoicenumber column
5077
    $dbh->do("
5078
        ALTER TABLE aqorders
5079
        ADD COLUMN invoiceid int(11) default NULL AFTER booksellerinvoicenumber,
5080
        ADD CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE
5081
    ");
5082
5083
    $dbh->do("
5084
        UPDATE aqorders, aqinvoices
5085
        SET aqorders.invoiceid = aqinvoices.invoiceid
5086
        WHERE aqorders.booksellerinvoicenumber = aqinvoices.invoicenumber
5087
    ");
5088
5089
    $dbh->do("
5090
        ALTER TABLE aqorders
5091
        DROP COLUMN booksellerinvoicenumber
5092
    ");
5093
5094
    print "Upgrade to $DBversion done (Add aqinvoices table) \n";
5095
    SetVersion ($DBversion);
5096
}
5097
5098
5039
=head1 FUNCTIONS
5099
=head1 FUNCTIONS
5040
5100
5041
=head2 DropAllForeignKeys($table)
5101
=head2 DropAllForeignKeys($table)
5042
- 

Return to bug 5339