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.3'; // version installed
15 var $sql = 'alter2.3.sql';
19 $this->description = _('Upgrade from version 2.2 to 2.3');
23 // Install procedure. All additional changes
24 // not included in sql file should go here.
26 function install($pref, $force)
28 $sql = "SELECT debtor_no, payment_terms FROM {$pref}debtors_master";
30 $result = db_query($sql);
32 display_error("Cannot read customers"
33 .':<br>'. db_error_msg($db));
36 // update all sales orders and transactions with customer std payment terms
37 while($cust = db_fetch($result)) {
38 $sql = "UPDATE {$pref}debtor_trans SET "
39 ."payment_terms = '" .$cust['payment_terms']
40 ."' WHERE debtor_no='".$cust['debtor_no']."'";
41 if (db_query($sql)==false) {
42 display_error("Cannot update cust trans payment"
43 .':<br>'. db_error_msg($db));
46 $sql = "UPDATE {$pref}sales_orders SET "
47 ."payment_terms = '" .$cust['payment_terms']
48 ."' WHERE debtor_no='".$cust['debtor_no']."'";
49 if (db_query($sql)==false) {
50 display_error("Cannot update sales order payment"
51 .':<br>'. db_error_msg($db));
55 if (!$this->update_totals($pref)) {
56 display_error("Cannot update order totals");
59 if (!$this->update_line_relations($pref)) {
60 display_error("Cannot update sales document links");
63 //remove obsolete and temporary columns.
64 // this have to be done here as db_import rearranges alter query order
66 'crm_persons' => array('tmp_id','tmp_class'),
67 'debtors_master' => array('email'),
68 'cust_branch' => array('phone', 'phone2', 'fax', 'email'),
69 'suppliers' => array('phone', 'phone2', 'fax', 'email'),
70 'debtor_trans' => array('trans_link')
73 foreach($dropcol as $table => $columns)
74 foreach($columns as $col) {
75 if (db_query("ALTER TABLE `{$pref}{$table}` DROP `$col`")==false) {
76 display_error("Cannot drop {$table}.{$col} column:<br>".db_error_msg($db));
80 // remove old preferences table after upgrade script has been executed
81 $sql = "DROP TABLE IF EXISTS `{$pref}company`";
83 return db_query($sql) && update_company_prefs(array('version_id'=>'2.3'), $pref);
86 // Checking before install
88 function pre_check($pref, $force)
93 // Test if patch was applied before.
95 function installed($pref) {
96 $n = 3; // number of patches to be installed
99 if (!check_table($pref, 'comments', 'type', array('Key'=>'MUL'))) $patchcnt++;
100 if (!check_table($pref, 'sys_prefs')) $patchcnt++;
101 if (!check_table($pref, 'sales_orders', 'payment_terms')) $patchcnt++;
104 return $n == 0 ? true : $patchcnt;
106 //=========================================================================================
107 // 2.3 specific update functions
113 function update_totals($pref)
115 global $path_to_root;
117 include_once("$path_to_root/sales/includes/cart_class.inc");
118 include_once("$path_to_root/purchasing/includes/po_class.inc");
119 $cart = new cart(ST_SALESORDER);
120 $sql = "SELECT order_no FROM {$pref}sales_orders";
121 $orders = db_query($sql);
125 while ($order_no = db_fetch($orders)) {
126 read_sales_order($order_no[0], $cart, ST_SALESORDER);
127 $result = db_query("UPDATE {$pref}sales_orders SET total=".$cart->get_trans_total());
128 unset($cart->line_items);
131 $cart = new purch_order();
132 $sql = "SELECT order_no FROM {$pref}purch_orders";
133 $orders = db_query($sql);
136 while ($order_no = db_fetch($orders)) {
137 read_po($order_no[0], $cart);
138 $result = db_query("UPDATE {$pref}purch_orders SET total=".$cart->get_trans_total());
139 unset($cart->line_items);
144 //------------------------------------------------------------------------------
145 // Retreive parent document number(s) for given transaction
147 function get_parent_trans_2_2($trans_type, $trans_no) {
149 $sql = 'SELECT trans_link FROM
150 '.TB_PREF.'debtor_trans WHERE trans_no='.db_escape($trans_no)
151 .' AND type='.db_escape($trans_type).' AND trans_link!=0';
153 $result = db_query($sql, 'Parent document numbers cannot be retrieved');
155 if (db_num_rows($result)) {
156 $link = db_fetch($result);
157 return array($link['trans_link']);
159 if ($trans_type!=ST_SALESINVOICE) return 0; // this is credit note with no parent invoice
160 // invoice: find batch invoice parent trans.
161 $sql = 'SELECT trans_no FROM
162 '.TB_PREF.'debtor_trans WHERE
163 (trans_link='.db_escape($trans_no).' AND type='. get_parent_type($trans_type) .')';
165 $result = db_query($sql, 'Delivery links cannot be retrieved');
168 if(db_num_rows($result)>0) {
169 while($link = db_fetch($result)) {
170 $delivery[] = $link['trans_no'];
173 return count($delivery) ? $delivery : 0;
177 Reorganizing document relations. Due to the design issue in pre 2.3 db structure
178 there can be sales documents with lines not properly linked to parents. This rare
179 cases will be described in error log.
181 function update_line_relations($pref)
183 global $path_to_root, $systypes_array;
185 require_once("$path_to_root/includes/sysnames.inc");
187 $sql = "SELECT d.type, trans_no, order_ FROM {$pref}debtor_trans d
188 LEFT JOIN {$pref}voided v ON d.type=v.type AND d.trans_no=v.id
189 WHERE ISNULL(v.type) AND
190 (d.type=".ST_CUSTDELIVERY
191 ." OR d.type=".ST_SALESINVOICE
192 ." OR d.type=".ST_CUSTCREDIT.")";
193 $result = db_query($sql);
197 while ($trans = db_fetch($result)) {
198 $type = $trans['type'];
199 $trans_no = $trans['trans_no'];
203 $lines = get_customer_trans_details($type, $trans_no);
204 $n = db_num_rows($lines);
206 if ($type==ST_CUSTDELIVERY)
207 $src_lines = get_sales_order_details($trans['order_'], ST_SALESORDER);
209 $src_lines = get_customer_trans_details(get_parent_type($type),
210 $this->get_parent_trans_2_2($type, $trans_no));
212 $src_n = db_num_rows($src_lines);
214 if (($type == ST_CUSTCREDIT) && ($src_n == 0))
215 continue; // free credit note has no src lines
217 $max = $type == ST_CUSTDELIVERY ? $n : max($src_n, $n);
219 for($i = 0, $j=0; $i < $max; $i++) {
220 if (!($doc_line = @db_fetch($lines)))
223 if(!($src_line = @db_fetch($src_lines)))
226 if ($type == ST_CUSTDELIVERY)
227 $src_line['stock_id'] = $src_line['stk_code']; // SO details has another field name
229 if ($src_line['stock_id'] == $doc_line['stock_id']
230 && ($src_line['quantity'] >= $doc_line['quantity'])) {
232 $sql = "UPDATE {$pref}debtor_trans_details SET src_id = {$src_line['id']}
233 WHERE id = {$doc_line['id']}";
240 error_log("Line level relations error for ".$systypes_array[$type]." #$trans_no.");
247 $install = new fa2_3;