sql/alter2.1.php

   1 <?php
   2 /**********************************************************************
   3     Copyright (C) FrontAccounting, LLC.
   4         Released under the terms of the GNU General Public License, GPL,
   5         as published by the Free Software Foundation, either version 3
   6         of the License, or (at your option) any later version.
   7     This program is distributed in the hope that it will be useful,
   8     but WITHOUT ANY WARRANTY; without even the implied warranty of
   9     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
  10     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
  11 ***********************************************************************/
  12 class fa2_1 {
  13         var $version = '2.1';   // version installed
  14         var $description;
  15         var $sql = 'alter2.1.sql';
  16
  17         function fa2_1() {
  18                 $this->description = _('Upgrade from version 2.0 to 2.1');
  19         }
  20         //
  21         //      Install procedure. All additional changes
  22         //      not included in sql file should go here.
  23         //
  24         function install($company, $force)
  25         {
  26                 global $db;
  27
  28         /*
  29         Statement below is allowed only for MySQL >=4.0.4:
  30         UPDATE `0_bank_trans`, `0_bank_accounts`
  31                 SET 0_bank_trans.bank_act=0_bank_accounts.id
  32                 WHERE 0_bank_trans.bank_act=0_bank_accounts.account_code;
  33         */
  34                 $sql = "SELECT id, account_code FROM ".TB_PREF."bank_accounts";
  35                 if(!($res = db_query($sql))) {
  36                         display_error(_("Cannot retrieve bank accounts codes")
  37                                 .':<br>'. db_error_msg($db));
  38                         return false;
  39                 }
  40                 while ($acc = db_fetch($res)) {
  41                         $sql = "UPDATE ".TB_PREF."bank_trans SET bank_act='"
  42                                 .$acc['id']."' WHERE bank_act=".$acc['account_code'];
  43                         if (db_query($sql)==false) {
  44                         display_error(_("Cannot update bank transactions")
  45                                 .':<br>'. db_error_msg($db));
  46                                 return false;
  47                         }
  48                 }
  49                 // copy all item codes from stock_master into item_codes
  50                 $sql = "SELECT `stock_id`,`description`,`category_id` FROM ".TB_PREF."stock_master";
  51                 $result = db_query($sql);
  52                 if (!$result) {
  53                         display_error(_("Cannot select stock identificators")
  54                                 .':<br>'. db_error_msg($db));
  55                         return false;
  56                 } else {
  57                         while ($row = db_fetch_assoc($result)) {
  58                                 $sql = "INSERT IGNORE "
  59                                         .TB_PREF."item_codes (`item_code`,`stock_id`,`description`,`category_id`)
  60                                         VALUES('".$row['stock_id']."','".$row['stock_id']."','"
  61                                         .$row['description']."','".$row['category_id']."')";
  62                                 $res2 = db_query($sql);
  63                                 if (!$res2) {
  64                                         display_error(_("Cannot insert stock id into item_codes")
  65                                                 .':<br>'. db_error_msg($db));
  66                                         return false;
  67                                 }
  68                         }
  69                 }
  70                 // remove obsolete bank_trans_types table
  71                 // (DROP queries are skipped during non-forced upgrade)
  72                 $sql = "DROP TABLE IF EXISTS `0_bank_trans_types`";
  73                 db_query($sql);
  74                 //
  75                 //      Move all debtor and supplier trans tax details to new table
  76                 // (INSERT INTO t  SELECT ... FROM t ... available after 4.0.14)
  77                 // No easy way to restore net amount for 0% tax rate for moved
  78                 // FA 2.0 transactions, but who cares?
  79                 //
  80         $move_sql =array(
  81         "debtor_trans_tax_details" =>
  82                 "SELECT tr.tran_date, tr.type, tr.trans_no, dt.tax_type_id,
  83                         dt.rate, dt.included_in_price, dt.amount, tr.reference as ref,
  84                         tr.rate as ex_rate
  85                 FROM ".TB_PREF."debtor_trans_tax_details dt
  86                         LEFT JOIN ".TB_PREF."trans_tax_details tt
  87                                 ON dt.debtor_trans_no=tt.trans_no
  88                                 AND dt.debtor_trans_type=tt.trans_type,
  89                         ".TB_PREF."debtor_trans tr
  90                 WHERE tt.trans_type is NULL
  91                         AND dt.debtor_trans_no = tr.trans_no
  92                         AND dt.debtor_trans_type = tr.type",
  93
  94         "supp_invoice_tax_items" =>
  95                 "SELECT tr.tran_date, tr.type, tr.trans_no, st.tax_type_id,
  96                         st.rate, st.included_in_price, st.amount, tr.supp_reference as ref,
  97                         tr.rate as ex_rate
  98                         FROM ".TB_PREF."supp_invoice_tax_items st
  99                                 LEFT JOIN ".TB_PREF."trans_tax_details tt
 100                                         ON st.supp_trans_no=tt.trans_no
 101                                         AND st.supp_trans_type=tt.trans_type,
 102                                         ".$pref."supp_trans tr
 103                                 WHERE tt.trans_type is NULL
 104                                         AND st.supp_trans_no = tr.trans_no
 105                                         AND st.supp_trans_type = tr.type");
 106
 107         foreach ($move_sql as $tbl => $sql) {
 108                 if (!check_table(TB_PREF, $tbl)){
 109                         $res = db_query($sql, "Cannot retrieve trans tax details from $tbl");
 110                         while ($row = db_fetch($res)) {
 111                                 $net_amount = $row['rate'] == 0 ?
 112                                         0 : ($row['included_in_price'] ?
 113                                                         ($row['amount']/$row['rate']*(100-$row['rate']))
 114                                                         :($row['amount']/$row['rate']*100));
 115                                 $sql2 = "INSERT INTO ".TB_PREF."trans_tax_details
 116                                 (trans_type,trans_no,tran_date,tax_type_id,rate,ex_rate,
 117                                         included_in_price, net_amount, amount, memo)
 118                                 VALUES ('".$row['type']."','".$row['trans_no']."','"
 119                                         .$row['tran_date']."','".$row['tax_type_id']."','"
 120                                         .$row['rate']."','".$row['ex_rate']."','"
 121                                         .$row['included_in_price']."','".$net_amount
 122                                         ."','".$row['amount']."','".$row['ref']."')";
 123                                 db_query($sql2, "Cannot move trans tax details from $tbl");
 124                         }
 125                         db_query("DROP TABLE ".TB_PREF.$tbl, "cannot remove $tbl");
 126                 }
 127         }
 128
 129                 return true;
 130         }
 131         //
 132         //      Checking before install
 133         //
 134         function pre_check($pref)
 135         {
 136         // We cannot perform successfull upgrade on system where the
 137         // trans tax details tables was deleted during previous try.
 138                 if (check_table($pref, 'debtor_trans_tax_details')
 139                         || check_table($pref, 'supp_invoice_tax_items')) {
 140                         display_error(_("Seems that system upgrade to version 2.1 has
 141                         been performed for this company already.<br> If something has gone
 142                         wrong and you want to retry upgrade process you MUST perform
 143                         database restore from last backup file first."));
 144
 145                         return false;
 146                 }
 147
 148                 return true; // true when ok, fail otherwise
 149         }
 150         //
 151         //      Test if patch was applied before.
 152         //
 153         function installed($pref) {
 154                 $n = 4; // number of features to be installed
 155                 if (!check_table($pref, 'item_codes')) $n--;
 156 //              if (!check_table($pref, 'company', 'foreign_codes')) $n--;
 157                 if (!check_table($pref, 'suppliers', 'credit_limit')) $n--;
 158                 if (!check_table($pref, 'bank_trans', 'reconciled',
 159                         array('Type'=>'date'))) $n--;
 160                 if (!check_table($pref, 'trans_tax_details')) $n--;
 161                 return $n == 0 ? true : 5 - $n;
 162         }
 163 };
 164
 165 $install = new fa2_1;