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

(-)a/installer/data/mysql/kohastructure.sql (-2 / +24 lines)
Lines 2680-2686 CREATE TABLE `aqorders` ( Link Here
2680
  `listprice` decimal(28,6) default NULL,
2680
  `listprice` decimal(28,6) default NULL,
2681
  `totalamount` decimal(28,6) default NULL,
2681
  `totalamount` decimal(28,6) default NULL,
2682
  `datereceived` date default NULL,
2682
  `datereceived` date default NULL,
2683
  `booksellerinvoicenumber` mediumtext,
2683
  invoiceid int(11) default NULL,
2684
  `freight` decimal(28,6) default NULL,
2684
  `freight` decimal(28,6) default NULL,
2685
  `unitprice` decimal(28,6) default NULL,
2685
  `unitprice` decimal(28,6) default NULL,
2686
  `quantityreceived` smallint(6) NOT NULL default 0,
2686
  `quantityreceived` smallint(6) NOT NULL default 0,
Lines 2712-2718 CREATE TABLE `aqorders` ( Link Here
2712
  KEY `biblionumber` (`biblionumber`),
2712
  KEY `biblionumber` (`biblionumber`),
2713
  KEY `budget_id` (`budget_id`),
2713
  KEY `budget_id` (`budget_id`),
2714
  CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2714
  CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2715
  CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2715
  CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2716
  CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE
2716
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2717
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2717
2718
2718
2719
Lines 2729-2734 CREATE TABLE `aqorders_items` ( Link Here
2729
  KEY `ordernumber` (`ordernumber`)
2730
  KEY `ordernumber` (`ordernumber`)
2730
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2731
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2731
2732
2733
2734
--
2735
-- Table structure for table aqinvoices
2736
--
2737
2738
DROP TABLE IF EXISTS aqinvoices;
2739
CREATE TABLE aqinvoices (
2740
  invoiceid int(11) NOT NULL AUTO_INCREMENT,    -- ID of the invoice, primary key
2741
  invoicenumber mediumtext NOT NULL,    -- Name of invoice
2742
  booksellerid int(11) NOT NULL,    -- foreign key to aqbooksellers
2743
  shipmentdate date default NULL,   -- date of shipment
2744
  billingdate date default NULL,    -- date of billing
2745
  closedate date default NULL,  -- invoice close date, NULL means the invoice is open
2746
  shipmentcost decimal(28,6) default NULL,  -- shipment cost
2747
  shipmentcost_budgetid int(11) default NULL,   -- foreign key to aqbudgets, link the shipment cost to a budget
2748
  PRIMARY KEY (invoiceid),
2749
  CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE,
2750
  CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE
2751
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2752
2753
2732
--
2754
--
2733
-- Table structure for table `fieldmapping`
2755
-- Table structure for table `fieldmapping`
2734
--
2756
--
(-)a/installer/data/mysql/updatedatabase.pl (-1 / +59 lines)
Lines 4943-4948 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { Link Here
4943
    SetVersion($DBversion);
4943
    SetVersion($DBversion);
4944
}
4944
}
4945
4945
4946
$DBversion = "XXX";
4947
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4948
    $dbh->do("
4949
        CREATE TABLE aqinvoices (
4950
          invoiceid int(11) NOT NULL AUTO_INCREMENT,
4951
          invoicenumber mediumtext NOT NULL,
4952
          booksellerid int(11) NOT NULL,
4953
          shipmentdate date default NULL,
4954
          billingdate date default NULL,
4955
          closedate date default NULL,
4956
          shipmentcost decimal(28,6) default NULL,
4957
          shipmentcost_budgetid int(11) default NULL,
4958
          PRIMARY KEY (invoiceid),
4959
          CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE,
4960
          CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE
4961
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8
4962
    ");
4963
4964
    # Fill this new table with existing invoices
4965
    my $sth = $dbh->prepare("
4966
        SELECT aqorders.booksellerinvoicenumber AS invoicenumber, aqbasket.booksellerid
4967
        FROM aqorders
4968
          LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
4969
        WHERE aqorders.booksellerinvoicenumber IS NOT NULL
4970
          AND aqorders.booksellerinvoicenumber != ''
4971
        GROUP BY aqorders.booksellerinvoicenumber
4972
    ");
4973
    $sth->execute;
4974
    my $results = $sth->fetchall_arrayref({});
4975
    $sth = $dbh->prepare("
4976
        INSERT INTO aqinvoices (invoicenumber, booksellerid) VALUES (?,?)
4977
    ");
4978
    foreach(@$results) {
4979
        $sth->execute($_->{'invoicenumber'}, $_->{'booksellerid'});
4980
    }
4981
4982
    # Add the column in aqorders, fill it with correct value
4983
    # and then drop booksellerinvoicenumber column
4984
    $dbh->do("
4985
        ALTER TABLE aqorders
4986
        ADD COLUMN invoiceid int(11) default NULL AFTER booksellerinvoicenumber,
4987
        ADD CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE
4988
    ");
4989
4990
    $dbh->do("
4991
        UPDATE aqorders, aqinvoices
4992
        SET aqorders.invoiceid = aqinvoices.invoiceid
4993
        WHERE aqorders.booksellerinvoicenumber = aqinvoices.invoicenumber
4994
    ");
4995
4996
    $dbh->do("
4997
        ALTER TABLE aqorders
4998
        DROP COLUMN booksellerinvoicenumber
4999
    ");
5000
5001
    print "Upgrade to $DBversion done (Add aqinvoices table) \n";
5002
    SetVersion ($DBversion);
5003
}
5004
4946
5005
4947
=head1 FUNCTIONS
5006
=head1 FUNCTIONS
4948
5007
4949
- 

Return to bug 5339