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 ***********************************************************************/
13 var $version = '2.1'; // version installed
15 var $sql = 'alter2.1.sql';
18 $this->description = _('Upgrade from version 2.0 to 2.1');
21 // Install procedure. All additional changes
22 // not included in sql file should go here.
24 function install($company, $force)
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;
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));
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));
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);
53 display_error(_("Cannot select stock identificators")
54 .':<br>'. db_error_msg($db));
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);
64 display_error(_("Cannot insert stock id into item_codes")
65 .':<br>'. db_error_msg($db));
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`";
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?
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,
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",
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,
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");
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");
125 db_query("DROP TABLE ".TB_PREF.$tbl, "cannot remove $tbl");
132 // Checking before install
134 function pre_check($pref)
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."));
148 return true; // true when ok, fail otherwise
151 // Test if patch was applied before.
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;
165 $install = new fa2_1;