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 4712-4717 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { Link Here
4712
    SetVersion($DBversion);
4712
    SetVersion($DBversion);
4713
}
4713
}
4714
4714
4715
$DBversion = "XXX";
4716
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4717
    $dbh->do("
4718
        CREATE TABLE aqinvoices (
4719
          invoiceid int(11) NOT NULL AUTO_INCREMENT,
4720
          invoicenumber mediumtext NOT NULL,
4721
          booksellerid int(11) NOT NULL,
4722
          shipmentdate date default NULL,
4723
          billingdate date default NULL,
4724
          closedate date default NULL,
4725
          shipmentcost decimal(28,6) default NULL,
4726
          shipmentcost_budgetid int(11) default NULL,
4727
          PRIMARY KEY (invoiceid),
4728
          CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE,
4729
          CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE
4730
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8
4731
    ");
4732
4733
    # Fill this new table with existing invoices
4734
    my $sth = $dbh->prepare("
4735
        SELECT aqorders.booksellerinvoicenumber AS invoicenumber, aqbasket.booksellerid
4736
        FROM aqorders
4737
          LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
4738
        WHERE aqorders.booksellerinvoicenumber IS NOT NULL
4739
          AND aqorders.booksellerinvoicenumber != ''
4740
        GROUP BY aqorders.booksellerinvoicenumber
4741
    ");
4742
    $sth->execute;
4743
    my $results = $sth->fetchall_arrayref({});
4744
    $sth = $dbh->prepare("
4745
        INSERT INTO aqinvoices (invoicenumber, booksellerid) VALUES (?,?)
4746
    ");
4747
    foreach(@$results) {
4748
        $sth->execute($_->{'invoicenumber'}, $_->{'booksellerid'});
4749
    }
4750
4751
    # Add the column in aqorders, fill it with correct value
4752
    # and then drop booksellerinvoicenumber column
4753
    $dbh->do("
4754
        ALTER TABLE aqorders
4755
        ADD COLUMN invoiceid int(11) default NULL AFTER booksellerinvoicenumber,
4756
        ADD CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE
4757
    ");
4758
4759
    $dbh->do("
4760
        UPDATE aqorders, aqinvoices
4761
        SET aqorders.invoiceid = aqinvoices.invoiceid
4762
        WHERE aqorders.booksellerinvoicenumber = aqinvoices.invoicenumber
4763
    ");
4764
4765
    $dbh->do("
4766
        ALTER TABLE aqorders
4767
        DROP COLUMN booksellerinvoicenumber
4768
    ");
4769
4770
    print "Upgrade to $DBversion done (Add aqinvoices table) \n";
4771
    SetVersion ($DBversion);
4772
}
4773
4715
=head1 FUNCTIONS
4774
=head1 FUNCTIONS
4716
4775
4717
=head2 DropAllForeignKeys($table)
4776
=head2 DropAllForeignKeys($table)
4718
- 

Return to bug 5339