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 invoices (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 invoices
2663
--
2664
2665
DROP TABLE IF EXISTS invoices;
2666
CREATE TABLE invoices (
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 invoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE,
2677
  CONSTRAINT invoices_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 / +58 lines)
Lines 4663-4668 ENDOFRENEWAL Link Here
4663
    print "Upgrade to $DBversion done (Added a system preference to allow renewal of Patron account either from todays date or from existing expiry date in the patrons account.)\n";
4663
    print "Upgrade to $DBversion done (Added a system preference to allow renewal of Patron account either from todays date or from existing expiry date in the patrons account.)\n";
4664
    SetVersion($DBversion);
4664
    SetVersion($DBversion);
4665
}
4665
}
4666
4667
$DBversion = "XXX";
4668
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4669
    $dbh->do("
4670
        CREATE TABLE invoices (
4671
          invoiceid int(11) NOT NULL AUTO_INCREMENT,
4672
          invoicenumber mediumtext NOT NULL,
4673
          booksellerid int(11) NOT NULL,
4674
          shipmentdate date default NULL,
4675
          billingdate date default NULL,
4676
          closedate date default NULL,
4677
          shipmentcost decimal(28,6) default NULL,
4678
          shipmentcost_budgetid int(11) default NULL,
4679
          PRIMARY KEY (invoiceid),
4680
          CONSTRAINT invoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE,
4681
          CONSTRAINT invoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE
4682
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8
4683
    ");
4684
4685
    # Fill this new table with existing invoices
4686
    my $sth = $dbh->prepare("
4687
        SELECT aqorders.booksellerinvoicenumber AS invoicenumber, aqbasket.booksellerid
4688
        FROM aqorders
4689
          LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
4690
        WHERE aqorders.booksellerinvoicenumber IS NOT NULL
4691
        GROUP BY aqorders.booksellerinvoicenumber
4692
    ");
4693
    $sth->execute;
4694
    my $results = $sth->fetchall_arrayref({});
4695
    $sth = $dbh->prepare("
4696
        INSERT INTO invoices (invoicenumber, booksellerid) VALUES (?,?)
4697
    ");
4698
    foreach(@$results) {
4699
        $sth->execute($_->{'invoicenumber'}, $_->{'booksellerid'});
4700
    }
4701
4702
    # Add the column in aqorders, fill it with correct value
4703
    # and then drop booksellerinvoicenumber column
4704
    $dbh->do("
4705
        ALTER TABLE aqorders
4706
        ADD COLUMN invoiceid int(11) default NULL AFTER booksellerinvoicenumber
4707
    ");
4708
4709
    $dbh->do("
4710
        UPDATE aqorders, invoices
4711
        SET aqorders.invoiceid = invoices.invoiceid
4712
        WHERE aqorders.booksellerinvoicenumber = invoices.invoicenumber
4713
    ");
4714
4715
    $dbh->do("
4716
        ALTER TABLE aqorders
4717
        DROP COLUMN booksellerinvoicenumber
4718
    ");
4719
4720
    print "Upgrade to $DBversion done (Add invoices table) \n";
4721
    SetVersion ($DBversion);
4722
}
4723
4666
=head1 FUNCTIONS
4724
=head1 FUNCTIONS
4667
4725
4668
=head2 DropAllForeignKeys($table)
4726
=head2 DropAllForeignKeys($table)
4669
- 

Return to bug 5339