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';
17 var $beta = false; // upgrade from 2.2 or 2.3beta;
20 $this->description = _('Upgrade from version 2.2 to 2.3');
24 // Install procedure. All additional changes
25 // not included in sql file should go here.
27 function install($pref, $force)
29 global $db_version, $dflt_lang;
31 $this->preconf = $this->fix_extensions();
36 // all specials below are already done on 2.3beta
38 $sql = "SELECT debtor_no, payment_terms FROM {$pref}debtors_master";
40 $result = db_query($sql);
42 display_error("Cannot read customers"
43 .':<br>'. db_error_msg($db));
46 // update all sales orders and transactions with customer std payment terms
47 while($cust = db_fetch($result)) {
48 $sql = "UPDATE {$pref}debtor_trans SET "
49 ."payment_terms = '" .$cust['payment_terms']
50 ."' WHERE debtor_no='".$cust['debtor_no']."'";
51 if (db_query($sql)==false) {
52 display_error("Cannot update cust trans payment"
53 .':<br>'. db_error_msg($db));
56 $sql = "UPDATE {$pref}sales_orders SET "
57 ."payment_terms = '" .$cust['payment_terms']
58 ."' WHERE debtor_no='".$cust['debtor_no']."'";
59 if (db_query($sql)==false) {
60 display_error("Cannot update sales order payment"
61 .':<br>'. db_error_msg($db));
65 if (!$this->update_totals($pref)) {
66 display_error("Cannot update order totals");
69 if (!$this->update_line_relations($pref)) {
70 display_error("Cannot update sales document links");
73 //remove obsolete and temporary columns.
74 // this have to be done here as db_import rearranges alter query order
76 'crm_persons' => array('tmp_id','tmp_class'),
77 'debtors_master' => array('email'),
78 'cust_branch' => array('phone', 'phone2', 'fax', 'email'),
79 'suppliers' => array('phone', 'phone2', 'fax', 'email'),
80 'debtor_trans' => array('trans_link')
83 foreach($dropcol as $table => $columns)
84 foreach($columns as $col) {
85 if (db_query("ALTER TABLE `{$pref}{$table}` DROP `$col`")==false) {
86 display_error("Cannot drop {$table}.{$col} column:<br>".db_error_msg($db));
90 // remove old preferences table after upgrade script has been executed
91 $sql = "DROP TABLE IF EXISTS `{$pref}company`";
95 $this->update_lang_cfg();
96 return update_company_prefs(array('version_id'=>$db_version), $pref);
99 // Checking before install
101 function pre_check($pref, $force)
104 if ($this->beta && !$force)
105 $this->sql = 'alter2.3rc.sql';
110 // Test if patch was applied before.
112 function installed($pref) {
113 $this->beta = !check_table($pref, 'suppliers', 'tax_included');
115 $n = 1; // number of patches to be installed
120 if (!check_table($pref, 'comments', 'type', array('Key'=>'MUL'))) $patchcnt++;
121 if (!check_table($pref, 'sys_prefs')) $patchcnt++;
122 if (!check_table($pref, 'sales_orders', 'payment_terms')) $patchcnt++;
124 if (!check_table($pref, 'purch_orders', 'tax_included')) $patchcnt++;
125 return $n == $patchcnt ? true : ($patchcnt ? ($patchcnt.'/'. $n) : 0);
127 //=========================================================================================
128 // 2.3 specific update functions
134 function update_totals($pref)
136 global $path_to_root;
138 include_once("$path_to_root/sales/includes/cart_class.inc");
139 include_once("$path_to_root/purchasing/includes/po_class.inc");
140 $cart = new cart(ST_SALESORDER);
141 $sql = "SELECT order_no, trans_type FROM {$pref}sales_orders";
142 $orders = db_query($sql);
145 while ($order = db_fetch($orders)) {
146 read_sales_order($order['order_no'], $cart, $order['trans_type']);
147 $result = db_query("UPDATE {$pref}sales_orders
148 SET total=".$cart->get_trans_total()
149 ." WHERE order_no=".$order[0]);
150 unset($cart->line_items);
153 $cart = new purch_order();
154 $sql = "SELECT order_no FROM {$pref}purch_orders";
155 $orders = db_query($sql);
158 while ($order_no = db_fetch($orders)) {
159 read_po($order_no[0], $cart);
160 $result = db_query("UPDATE {$pref}purch_orders SET total=".$cart->get_trans_total());
161 unset($cart->line_items);
166 //------------------------------------------------------------------------------
167 // Retreive parent document number(s) for given transaction
169 function get_parent_trans_2_2($trans_type, $trans_no) {
171 $sql = 'SELECT trans_link FROM
172 '.TB_PREF.'debtor_trans WHERE trans_no='.db_escape($trans_no)
173 .' AND type='.db_escape($trans_type).' AND trans_link!=0';
175 $result = db_query($sql, 'Parent document numbers cannot be retrieved');
177 if (db_num_rows($result)) {
178 $link = db_fetch($result);
179 return array($link['trans_link']);
181 if ($trans_type!=ST_SALESINVOICE) return 0; // this is credit note with no parent invoice
182 // invoice: find batch invoice parent trans.
183 $sql = 'SELECT trans_no FROM
184 '.TB_PREF.'debtor_trans WHERE
185 (trans_link='.db_escape($trans_no).' AND type='. get_parent_type($trans_type) .')';
187 $result = db_query($sql, 'Delivery links cannot be retrieved');
190 if(db_num_rows($result)>0) {
191 while($link = db_fetch($result)) {
192 $delivery[] = $link['trans_no'];
195 return count($delivery) ? $delivery : 0;
199 Reorganizing document relations. Due to the design issue in pre 2.3 db structure
200 there can be sales documents with lines not properly linked to parents. This rare
201 cases will be described in error log.
203 function update_line_relations($pref)
205 global $path_to_root, $systypes_array;
207 require_once("$path_to_root/includes/sysnames.inc");
209 $sql = "SELECT d.type, trans_no, order_ FROM {$pref}debtor_trans d
210 LEFT JOIN {$pref}voided v ON d.type=v.type AND d.trans_no=v.id
211 WHERE ISNULL(v.type) AND
212 (d.type=".ST_CUSTDELIVERY
213 ." OR d.type=".ST_SALESINVOICE
214 ." OR d.type=".ST_CUSTCREDIT.")";
215 $result = db_query($sql);
219 while ($trans = db_fetch($result)) {
220 $type = $trans['type'];
221 $trans_no = $trans['trans_no'];
225 $lines = get_customer_trans_details($type, $trans_no);
226 $n = db_num_rows($lines);
228 if ($type==ST_CUSTDELIVERY)
229 $src_lines = get_sales_order_details($trans['order_'], ST_SALESORDER);
231 $src_lines = get_customer_trans_details(get_parent_type($type),
232 $this->get_parent_trans_2_2($type, $trans_no));
234 $src_n = db_num_rows($src_lines);
236 if (($type == ST_CUSTCREDIT) && ($src_n == 0))
237 continue; // free credit note has no src lines
239 $max = $type == ST_CUSTDELIVERY ? $n : max($src_n, $n);
241 for($i = 0, $j=0; $i < $max; $i++) {
242 if (!($doc_line = @db_fetch($lines)))
245 if(!($src_line = @db_fetch($src_lines)))
248 if ($type == ST_CUSTDELIVERY)
249 $src_line['stock_id'] = $src_line['stk_code']; // SO details has another field name
251 if ($src_line['stock_id'] == $doc_line['stock_id']
252 && ($src_line['quantity'] >= $doc_line['quantity'])) {
254 $sql = "UPDATE {$pref}debtor_trans_details SET src_id = {$src_line['id']}
255 WHERE id = {$doc_line['id']}";
262 error_log("Line level relations error for ".$systypes_array[$type]." #$trans_no.");
268 function fix_extensions()
270 global $path_to_root, $next_extension_id, $installed_languages;
273 foreach($installed_languages as $i => $lang) {
274 if (!isset($lang['path'])) {
275 $code = $lang['code'];
276 $installed_languages[$i]['path'] = 'lang/'.$code;
277 $installed_languages[$i]['package'] = $code;
285 $installed_extensions= get_company_extensions();
286 if (!isset($next_extension_id))
287 $next_extension_id = 1;
290 /* Old extension modules are uninstalled - they need manual porting after
291 heavy changes in extension system in FA2.3
293 foreach($installed_extensions as $i => $ext)
295 if (isset($ext['title'])) // old type entry
297 if ($ext['type'] == 'module') {
298 $new['type'] = 'extension';
299 $new['tabs'][] = array(
300 'url' => $ext['filename'],
301 'access' => isset($ext['access']) ? $ext['access'] : 'SA_OPEN',
302 'tab_id' => $ext['tab'],
303 'title' => $ext['title']
305 $new['path'] = $ext['path'];
309 $new['type'] = 'extension';
310 $new['tabs'] = array();
311 $new['path'] = 'modules/'.$ext['path'];
312 $new['entries'][] = array(
313 'url' => $ext['filename'],
314 'access' => isset($ext['access']) ? $ext['access'] : 'SA_OPEN',
315 'tab_id' => $ext['tab'],
316 'title' => $ext['title']
319 if (isset($ext['acc_file']))
320 $new['acc_file'] = $ext['acc_file'];
321 $new['name'] = $ext['name'];
322 $new['package'] = $new['package'] = '';
325 $new_exts[$i] = $new;
329 // Preserve non-standard themes
330 $path = $path_to_root.'/themes/';
332 $themedir = opendir($path);
333 while (false !== ($fname = readdir($themedir)))
335 if ($fname!='.' && $fname!='..' && $fname!='CVS' && is_dir($path.$fname)
336 && !in_array($fname, array('aqua', 'cool', 'default')))
338 foreach($installed_extensions as $ext)
339 if ($ext['path'] == 'themes/'.$fname) // skip if theme is already listed
341 $new_exts[$next_extension_id++] = array(
342 'name' => 'Theme '. ucwords($fname),
346 'path' => 'themes/'.$fname
352 if (count($new_exts)) {
353 return update_extensions($new_exts);
358 function update_lang_cfg()
360 global $dflt_lang, $installed_languages;
362 foreach($installed_languages as $n => $lang) {
363 if ($lang['code'] == 'en_GB') {
364 $installed_languages[$n] = array('code'=>'C','name'=>'English',
365 'encoding'=>'iso-8859-1', 'path' => '', 'package' => '');
366 if ($dflt_lang == 'en_GB')
375 $install = new fa2_3;