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

(-)a/installer/data/mysql/kohastructure.sql (-2 / +24 lines)
Lines 2608-2614 CREATE TABLE `aqorders` ( Link Here
2608
  `listprice` decimal(28,6) default NULL,
2608
  `listprice` decimal(28,6) default NULL,
2609
  `totalamount` decimal(28,6) default NULL,
2609
  `totalamount` decimal(28,6) default NULL,
2610
  `datereceived` date default NULL,
2610
  `datereceived` date default NULL,
2611
  `booksellerinvoicenumber` mediumtext,
2611
  invoiceid int(11) default NULL,
2612
  `freight` decimal(28,6) default NULL,
2612
  `freight` decimal(28,6) default NULL,
2613
  `unitprice` decimal(28,6) default NULL,
2613
  `unitprice` decimal(28,6) default NULL,
2614
  `quantityreceived` smallint(6) NOT NULL default 0,
2614
  `quantityreceived` smallint(6) NOT NULL default 0,
Lines 2640-2646 CREATE TABLE `aqorders` ( Link Here
2640
  KEY `biblionumber` (`biblionumber`),
2640
  KEY `biblionumber` (`biblionumber`),
2641
  KEY `budget_id` (`budget_id`),
2641
  KEY `budget_id` (`budget_id`),
2642
  CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2642
  CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2643
  CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2643
  CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2644
  CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE
2644
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2645
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2645
2646
2646
2647
Lines 2657-2662 CREATE TABLE `aqorders_items` ( Link Here
2657
  KEY `ordernumber` (`ordernumber`)
2658
  KEY `ordernumber` (`ordernumber`)
2658
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2659
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2659
2660
2661
2662
--
2663
-- Table structure for table aqinvoices
2664
--
2665
2666
DROP TABLE IF EXISTS aqinvoices;
2667
CREATE TABLE aqinvoices (
2668
  invoiceid int(11) NOT NULL AUTO_INCREMENT,    -- ID of the invoice, primary key
2669
  invoicenumber mediumtext NOT NULL,    -- Name of invoice
2670
  booksellerid int(11) NOT NULL,    -- foreign key to aqbooksellers
2671
  shipmentdate date default NULL,   -- date of shipment
2672
  billingdate date default NULL,    -- date of billing
2673
  closedate date default NULL,  -- invoice close date, NULL means the invoice is open
2674
  shipmentcost decimal(28,6) default NULL,  -- shipment cost
2675
  shipmentcost_budgetid int(11) default NULL,   -- foreign key to aqbudgets, link the shipment cost to a budget
2676
  PRIMARY KEY (invoiceid),
2677
  CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE,
2678
  CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE
2679
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2680
2681
2660
--
2682
--
2661
-- Table structure for table `fieldmapping`
2683
-- Table structure for table `fieldmapping`
2662
--
2684
--
(-)a/installer/data/mysql/updatedatabase.pl (-1 / +59 lines)
Lines 4719-4724 if (C4::Context->preference("Version") < TransformToNum($DBversion)) { Link Here
4719
    SetVersion ($DBversion);
4719
    SetVersion ($DBversion);
4720
}
4720
}
4721
4721
4722
$DBversion = "XXX";
4723
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4724
    $dbh->do("
4725
        CREATE TABLE aqinvoices (
4726
          invoiceid int(11) NOT NULL AUTO_INCREMENT,
4727
          invoicenumber mediumtext NOT NULL,
4728
          booksellerid int(11) NOT NULL,
4729
          shipmentdate date default NULL,
4730
          billingdate date default NULL,
4731
          closedate date default NULL,
4732
          shipmentcost decimal(28,6) default NULL,
4733
          shipmentcost_budgetid int(11) default NULL,
4734
          PRIMARY KEY (invoiceid),
4735
          CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE,
4736
          CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE
4737
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8
4738
    ");
4739
4740
    # Fill this new table with existing invoices
4741
    my $sth = $dbh->prepare("
4742
        SELECT aqorders.booksellerinvoicenumber AS invoicenumber, aqbasket.booksellerid
4743
        FROM aqorders
4744
          LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
4745
        WHERE aqorders.booksellerinvoicenumber IS NOT NULL
4746
          AND aqorders.booksellerinvoicenumber != ''
4747
        GROUP BY aqorders.booksellerinvoicenumber
4748
    ");
4749
    $sth->execute;
4750
    my $results = $sth->fetchall_arrayref({});
4751
    $sth = $dbh->prepare("
4752
        INSERT INTO aqinvoices (invoicenumber, booksellerid) VALUES (?,?)
4753
    ");
4754
    foreach(@$results) {
4755
        $sth->execute($_->{'invoicenumber'}, $_->{'booksellerid'});
4756
    }
4757
4758
    # Add the column in aqorders, fill it with correct value
4759
    # and then drop booksellerinvoicenumber column
4760
    $dbh->do("
4761
        ALTER TABLE aqorders
4762
        ADD COLUMN invoiceid int(11) default NULL AFTER booksellerinvoicenumber,
4763
        ADD CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE
4764
    ");
4765
4766
    $dbh->do("
4767
        UPDATE aqorders, aqinvoices
4768
        SET aqorders.invoiceid = aqinvoices.invoiceid
4769
        WHERE aqorders.booksellerinvoicenumber = aqinvoices.invoicenumber
4770
    ");
4771
4772
    $dbh->do("
4773
        ALTER TABLE aqorders
4774
        DROP COLUMN booksellerinvoicenumber
4775
    ");
4776
4777
    print "Upgrade to $DBversion done (Add aqinvoices table) \n";
4778
    SetVersion ($DBversion);
4779
}
4780
4722
=head1 FUNCTIONS
4781
=head1 FUNCTIONS
4723
4782
4724
=head2 DropAllForeignKeys($table)
4783
=head2 DropAllForeignKeys($table)
4725
- 

Return to bug 5339