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

(-)a/installer/data/mysql/kohastructure.sql (-2 / +24 lines)
Lines 2715-2721 CREATE TABLE `aqorders` ( Link Here
2715
  `listprice` decimal(28,6) default NULL,
2715
  `listprice` decimal(28,6) default NULL,
2716
  `totalamount` decimal(28,6) default NULL,
2716
  `totalamount` decimal(28,6) default NULL,
2717
  `datereceived` date default NULL,
2717
  `datereceived` date default NULL,
2718
  `booksellerinvoicenumber` mediumtext,
2718
  invoiceid int(11) default NULL,
2719
  `freight` decimal(28,6) default NULL,
2719
  `freight` decimal(28,6) default NULL,
2720
  `unitprice` decimal(28,6) default NULL,
2720
  `unitprice` decimal(28,6) default NULL,
2721
  `quantityreceived` smallint(6) NOT NULL default 0,
2721
  `quantityreceived` smallint(6) NOT NULL default 0,
Lines 2747-2753 CREATE TABLE `aqorders` ( Link Here
2747
  KEY `biblionumber` (`biblionumber`),
2747
  KEY `biblionumber` (`biblionumber`),
2748
  KEY `budget_id` (`budget_id`),
2748
  KEY `budget_id` (`budget_id`),
2749
  CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2749
  CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2750
  CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2750
  CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2751
  CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE
2751
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2752
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2752
2753
2753
2754
Lines 2764-2769 CREATE TABLE `aqorders_items` ( Link Here
2764
  KEY `ordernumber` (`ordernumber`)
2765
  KEY `ordernumber` (`ordernumber`)
2765
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2766
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2766
2767
2768
2769
--
2770
-- Table structure for table aqinvoices
2771
--
2772
2773
DROP TABLE IF EXISTS aqinvoices;
2774
CREATE TABLE aqinvoices (
2775
  invoiceid int(11) NOT NULL AUTO_INCREMENT,    -- ID of the invoice, primary key
2776
  invoicenumber mediumtext NOT NULL,    -- Name of invoice
2777
  booksellerid int(11) NOT NULL,    -- foreign key to aqbooksellers
2778
  shipmentdate date default NULL,   -- date of shipment
2779
  billingdate date default NULL,    -- date of billing
2780
  closedate date default NULL,  -- invoice close date, NULL means the invoice is open
2781
  shipmentcost decimal(28,6) default NULL,  -- shipment cost
2782
  shipmentcost_budgetid int(11) default NULL,   -- foreign key to aqbudgets, link the shipment cost to a budget
2783
  PRIMARY KEY (invoiceid),
2784
  CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE,
2785
  CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE
2786
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2787
2788
2767
--
2789
--
2768
-- Table structure for table `fieldmapping`
2790
-- Table structure for table `fieldmapping`
2769
--
2791
--
(-)a/installer/data/mysql/updatedatabase.pl (-1 / +60 lines)
Lines 5146-5151 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { Link Here
5146
    SetVersion($DBversion);
5146
    SetVersion($DBversion);
5147
}
5147
}
5148
5148
5149
$DBversion = "XXX";
5150
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
5151
    $dbh->do("
5152
        CREATE TABLE aqinvoices (
5153
          invoiceid int(11) NOT NULL AUTO_INCREMENT,
5154
          invoicenumber mediumtext NOT NULL,
5155
          booksellerid int(11) NOT NULL,
5156
          shipmentdate date default NULL,
5157
          billingdate date default NULL,
5158
          closedate date default NULL,
5159
          shipmentcost decimal(28,6) default NULL,
5160
          shipmentcost_budgetid int(11) default NULL,
5161
          PRIMARY KEY (invoiceid),
5162
          CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE,
5163
          CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE
5164
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8
5165
    ");
5166
5167
    # Fill this new table with existing invoices
5168
    my $sth = $dbh->prepare("
5169
        SELECT aqorders.booksellerinvoicenumber AS invoicenumber, aqbasket.booksellerid
5170
        FROM aqorders
5171
          LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
5172
        WHERE aqorders.booksellerinvoicenumber IS NOT NULL
5173
          AND aqorders.booksellerinvoicenumber != ''
5174
        GROUP BY aqorders.booksellerinvoicenumber
5175
    ");
5176
    $sth->execute;
5177
    my $results = $sth->fetchall_arrayref({});
5178
    $sth = $dbh->prepare("
5179
        INSERT INTO aqinvoices (invoicenumber, booksellerid) VALUES (?,?)
5180
    ");
5181
    foreach(@$results) {
5182
        $sth->execute($_->{'invoicenumber'}, $_->{'booksellerid'});
5183
    }
5184
5185
    # Add the column in aqorders, fill it with correct value
5186
    # and then drop booksellerinvoicenumber column
5187
    $dbh->do("
5188
        ALTER TABLE aqorders
5189
        ADD COLUMN invoiceid int(11) default NULL AFTER booksellerinvoicenumber,
5190
        ADD CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE
5191
    ");
5192
5193
    $dbh->do("
5194
        UPDATE aqorders, aqinvoices
5195
        SET aqorders.invoiceid = aqinvoices.invoiceid
5196
        WHERE aqorders.booksellerinvoicenumber = aqinvoices.invoicenumber
5197
    ");
5198
5199
    $dbh->do("
5200
        ALTER TABLE aqorders
5201
        DROP COLUMN booksellerinvoicenumber
5202
    ");
5203
5204
    print "Upgrade to $DBversion done (Add aqinvoices table) \n";
5205
    SetVersion ($DBversion);
5206
}
5207
5208
5149
=head1 FUNCTIONS
5209
=head1 FUNCTIONS
5150
5210
5151
=head2 DropAllForeignKeys($table)
5211
=head2 DropAllForeignKeys($table)
5152
- 

Return to bug 5339