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

(-)a/installer/data/mysql/kohastructure.sql (-2 / +24 lines)
Lines 2607-2613 CREATE TABLE `aqorders` ( Link Here
2607
  `listprice` decimal(28,6) default NULL,
2607
  `listprice` decimal(28,6) default NULL,
2608
  `totalamount` decimal(28,6) default NULL,
2608
  `totalamount` decimal(28,6) default NULL,
2609
  `datereceived` date default NULL,
2609
  `datereceived` date default NULL,
2610
  `booksellerinvoicenumber` mediumtext,
2610
  invoiceid int(11) default NULL,
2611
  `freight` decimal(28,6) default NULL,
2611
  `freight` decimal(28,6) default NULL,
2612
  `unitprice` decimal(28,6) default NULL,
2612
  `unitprice` decimal(28,6) default NULL,
2613
  `quantityreceived` smallint(6) NOT NULL default 0,
2613
  `quantityreceived` smallint(6) NOT NULL default 0,
Lines 2639-2645 CREATE TABLE `aqorders` ( Link Here
2639
  KEY `biblionumber` (`biblionumber`),
2639
  KEY `biblionumber` (`biblionumber`),
2640
  KEY `budget_id` (`budget_id`),
2640
  KEY `budget_id` (`budget_id`),
2641
  CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2641
  CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2642
  CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2642
  CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2643
  CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE
2643
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2644
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2644
2645
2645
2646
Lines 2656-2661 CREATE TABLE `aqorders_items` ( Link Here
2656
  KEY `ordernumber` (`ordernumber`)
2657
  KEY `ordernumber` (`ordernumber`)
2657
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2658
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2658
2659
2660
2661
--
2662
-- Table structure for table aqinvoices
2663
--
2664
2665
DROP TABLE IF EXISTS aqinvoices;
2666
CREATE TABLE aqinvoices (
2667
  invoiceid int(11) NOT NULL AUTO_INCREMENT,    -- ID of the invoice, primary key
2668
  invoicenumber mediumtext NOT NULL,    -- Name of invoice
2669
  booksellerid int(11) NOT NULL,    -- foreign key to aqbooksellers
2670
  shipmentdate date default NULL,   -- date of shipment
2671
  billingdate date default NULL,    -- date of billing
2672
  closedate date default NULL,  -- invoice close date, NULL means the invoice is open
2673
  shipmentcost decimal(28,6) default NULL,  -- shipment cost
2674
  shipmentcost_budgetid int(11) default NULL,   -- foreign key to aqbudgets, link the shipment cost to a budget
2675
  PRIMARY KEY (invoiceid),
2676
  CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE,
2677
  CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE
2678
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2679
2680
2659
--
2681
--
2660
-- Table structure for table `fieldmapping`
2682
-- Table structure for table `fieldmapping`
2661
--
2683
--
(-)a/installer/data/mysql/updatedatabase.pl (-1 / +59 lines)
Lines 4684-4689 if (C4::Context->preference("Version") < TransformToNum($DBversion)) { Link Here
4684
    SetVersion($DBversion);
4684
    SetVersion($DBversion);
4685
}
4685
}
4686
4686
4687
$DBversion = "XXX";
4688
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4689
    $dbh->do("
4690
        CREATE TABLE aqinvoices (
4691
          invoiceid int(11) NOT NULL AUTO_INCREMENT,
4692
          invoicenumber mediumtext NOT NULL,
4693
          booksellerid int(11) NOT NULL,
4694
          shipmentdate date default NULL,
4695
          billingdate date default NULL,
4696
          closedate date default NULL,
4697
          shipmentcost decimal(28,6) default NULL,
4698
          shipmentcost_budgetid int(11) default NULL,
4699
          PRIMARY KEY (invoiceid),
4700
          CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE,
4701
          CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE
4702
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8
4703
    ");
4704
4705
    # Fill this new table with existing invoices
4706
    my $sth = $dbh->prepare("
4707
        SELECT aqorders.booksellerinvoicenumber AS invoicenumber, aqbasket.booksellerid
4708
        FROM aqorders
4709
          LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
4710
        WHERE aqorders.booksellerinvoicenumber IS NOT NULL
4711
          AND aqorders.booksellerinvoicenumber != ''
4712
        GROUP BY aqorders.booksellerinvoicenumber
4713
    ");
4714
    $sth->execute;
4715
    my $results = $sth->fetchall_arrayref({});
4716
    $sth = $dbh->prepare("
4717
        INSERT INTO aqinvoices (invoicenumber, booksellerid) VALUES (?,?)
4718
    ");
4719
    foreach(@$results) {
4720
        $sth->execute($_->{'invoicenumber'}, $_->{'booksellerid'});
4721
    }
4722
4723
    # Add the column in aqorders, fill it with correct value
4724
    # and then drop booksellerinvoicenumber column
4725
    $dbh->do("
4726
        ALTER TABLE aqorders
4727
        ADD COLUMN invoiceid int(11) default NULL AFTER booksellerinvoicenumber,
4728
        ADD CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE
4729
    ");
4730
4731
    $dbh->do("
4732
        UPDATE aqorders, aqinvoices
4733
        SET aqorders.invoiceid = aqinvoices.invoiceid
4734
        WHERE aqorders.booksellerinvoicenumber = aqinvoices.invoicenumber
4735
    ");
4736
4737
    $dbh->do("
4738
        ALTER TABLE aqorders
4739
        DROP COLUMN booksellerinvoicenumber
4740
    ");
4741
4742
    print "Upgrade to $DBversion done (Add aqinvoices table) \n";
4743
    SetVersion ($DBversion);
4744
}
4745
4687
=head1 FUNCTIONS
4746
=head1 FUNCTIONS
4688
4747
4689
=head2 DropAllForeignKeys($table)
4748
=head2 DropAllForeignKeys($table)
4690
- 

Return to bug 5339