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

(-)a/installer/data/mysql/kohastructure.sql (-2 / +24 lines)
Lines 2710-2716 CREATE TABLE `aqorders` ( Link Here
2710
  `listprice` decimal(28,6) default NULL,
2710
  `listprice` decimal(28,6) default NULL,
2711
  `totalamount` decimal(28,6) default NULL,
2711
  `totalamount` decimal(28,6) default NULL,
2712
  `datereceived` date default NULL,
2712
  `datereceived` date default NULL,
2713
  `booksellerinvoicenumber` mediumtext,
2713
  invoiceid int(11) default NULL,
2714
  `freight` decimal(28,6) default NULL,
2714
  `freight` decimal(28,6) default NULL,
2715
  `unitprice` decimal(28,6) default NULL,
2715
  `unitprice` decimal(28,6) default NULL,
2716
  `quantityreceived` smallint(6) NOT NULL default 0,
2716
  `quantityreceived` smallint(6) NOT NULL default 0,
Lines 2742-2748 CREATE TABLE `aqorders` ( Link Here
2742
  KEY `biblionumber` (`biblionumber`),
2742
  KEY `biblionumber` (`biblionumber`),
2743
  KEY `budget_id` (`budget_id`),
2743
  KEY `budget_id` (`budget_id`),
2744
  CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2744
  CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2745
  CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2745
  CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2746
  CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE
2746
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2747
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2747
2748
2748
2749
Lines 2759-2764 CREATE TABLE `aqorders_items` ( Link Here
2759
  KEY `ordernumber` (`ordernumber`)
2760
  KEY `ordernumber` (`ordernumber`)
2760
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2761
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2761
2762
2763
2764
--
2765
-- Table structure for table aqinvoices
2766
--
2767
2768
DROP TABLE IF EXISTS aqinvoices;
2769
CREATE TABLE aqinvoices (
2770
  invoiceid int(11) NOT NULL AUTO_INCREMENT,    -- ID of the invoice, primary key
2771
  invoicenumber mediumtext NOT NULL,    -- Name of invoice
2772
  booksellerid int(11) NOT NULL,    -- foreign key to aqbooksellers
2773
  shipmentdate date default NULL,   -- date of shipment
2774
  billingdate date default NULL,    -- date of billing
2775
  closedate date default NULL,  -- invoice close date, NULL means the invoice is open
2776
  shipmentcost decimal(28,6) default NULL,  -- shipment cost
2777
  shipmentcost_budgetid int(11) default NULL,   -- foreign key to aqbudgets, link the shipment cost to a budget
2778
  PRIMARY KEY (invoiceid),
2779
  CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE,
2780
  CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE
2781
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2782
2783
2762
--
2784
--
2763
-- Table structure for table `fieldmapping`
2785
-- Table structure for table `fieldmapping`
2764
--
2786
--
(-)a/installer/data/mysql/updatedatabase.pl (-1 / +60 lines)
Lines 5086-5091 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { Link Here
5086
    SetVersion($DBversion);
5086
    SetVersion($DBversion);
5087
}
5087
}
5088
5088
5089
$DBversion = "XXX";
5090
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
5091
    $dbh->do("
5092
        CREATE TABLE aqinvoices (
5093
          invoiceid int(11) NOT NULL AUTO_INCREMENT,
5094
          invoicenumber mediumtext NOT NULL,
5095
          booksellerid int(11) NOT NULL,
5096
          shipmentdate date default NULL,
5097
          billingdate date default NULL,
5098
          closedate date default NULL,
5099
          shipmentcost decimal(28,6) default NULL,
5100
          shipmentcost_budgetid int(11) default NULL,
5101
          PRIMARY KEY (invoiceid),
5102
          CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE,
5103
          CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE
5104
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8
5105
    ");
5106
5107
    # Fill this new table with existing invoices
5108
    my $sth = $dbh->prepare("
5109
        SELECT aqorders.booksellerinvoicenumber AS invoicenumber, aqbasket.booksellerid
5110
        FROM aqorders
5111
          LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
5112
        WHERE aqorders.booksellerinvoicenumber IS NOT NULL
5113
          AND aqorders.booksellerinvoicenumber != ''
5114
        GROUP BY aqorders.booksellerinvoicenumber
5115
    ");
5116
    $sth->execute;
5117
    my $results = $sth->fetchall_arrayref({});
5118
    $sth = $dbh->prepare("
5119
        INSERT INTO aqinvoices (invoicenumber, booksellerid) VALUES (?,?)
5120
    ");
5121
    foreach(@$results) {
5122
        $sth->execute($_->{'invoicenumber'}, $_->{'booksellerid'});
5123
    }
5124
5125
    # Add the column in aqorders, fill it with correct value
5126
    # and then drop booksellerinvoicenumber column
5127
    $dbh->do("
5128
        ALTER TABLE aqorders
5129
        ADD COLUMN invoiceid int(11) default NULL AFTER booksellerinvoicenumber,
5130
        ADD CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE
5131
    ");
5132
5133
    $dbh->do("
5134
        UPDATE aqorders, aqinvoices
5135
        SET aqorders.invoiceid = aqinvoices.invoiceid
5136
        WHERE aqorders.booksellerinvoicenumber = aqinvoices.invoicenumber
5137
    ");
5138
5139
    $dbh->do("
5140
        ALTER TABLE aqorders
5141
        DROP COLUMN booksellerinvoicenumber
5142
    ");
5143
5144
    print "Upgrade to $DBversion done (Add aqinvoices table) \n";
5145
    SetVersion ($DBversion);
5146
}
5147
5148
5089
=head1 FUNCTIONS
5149
=head1 FUNCTIONS
5090
5150
5091
=head2 DropAllForeignKeys($table)
5151
=head2 DropAllForeignKeys($table)
5092
- 

Return to bug 5339