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)
35 // all specials below are already done on 2.3beta
37 $sql = "SELECT debtor_no, payment_terms FROM {$pref}debtors_master";
39 $result = db_query($sql);
41 display_error("Cannot read customers"
42 .':<br>'. db_error_msg($db));
45 // update all sales orders and transactions with customer std payment terms
46 while($cust = db_fetch($result)) {
47 $sql = "UPDATE {$pref}debtor_trans SET "
48 ."payment_terms = '" .$cust['payment_terms']
49 ."' WHERE debtor_no='".$cust['debtor_no']."'";
50 if (db_query($sql)==false) {
51 display_error("Cannot update cust trans payment"
52 .':<br>'. db_error_msg($db));
55 $sql = "UPDATE {$pref}sales_orders SET "
56 ."payment_terms = '" .$cust['payment_terms']
57 ."' WHERE debtor_no='".$cust['debtor_no']."'";
58 if (db_query($sql)==false) {
59 display_error("Cannot update sales order payment"
60 .':<br>'. db_error_msg($db));
64 if (!$this->update_totals($pref)) {
65 display_error("Cannot update order totals");
68 if (!$this->update_line_relations($pref)) {
69 display_error("Cannot update sales document links");
72 //remove obsolete and temporary columns.
73 // this have to be done here as db_import rearranges alter query order
75 'crm_persons' => array('tmp_id','tmp_class'),
76 'debtors_master' => array('email'),
77 'cust_branch' => array('phone', 'phone2', 'fax', 'email'),
78 'suppliers' => array('phone', 'phone2', 'fax', 'email'),
79 'debtor_trans' => array('trans_link')
82 foreach($dropcol as $table => $columns)
83 foreach($columns as $col) {
84 if (db_query("ALTER TABLE `{$pref}{$table}` DROP `$col`")==false) {
85 display_error("Cannot drop {$table}.{$col} column:<br>".db_error_msg($db));
89 // remove old preferences table after upgrade script has been executed
90 $sql = "DROP TABLE IF EXISTS `{$pref}company`";
94 return update_company_prefs(array('version_id'=>$db_version), $pref);
97 // Checking before install
99 function pre_check($pref, $force)
102 if ($this->beta && !$force)
103 $this->sql = 'alter2.3rc.sql';
108 // Test if patch was applied before.
110 function installed($pref) {
111 $this->beta = !check_table($pref, 'suppliers', 'tax_included');
112 $this->preconf = $this->fix_extensions();
114 $n = 1; // number of patches to be installed
119 if (!check_table($pref, 'comments', 'type', array('Key'=>'MUL'))) $patchcnt++;
120 if (!check_table($pref, 'sys_prefs')) $patchcnt++;
121 if (!check_table($pref, 'sales_orders', 'payment_terms')) $patchcnt++;
123 if (!check_table($pref, 'purch_orders', 'tax_included')) $patchcnt++;
124 return $n == $patchcnt ? true : ($patchcnt ? ($patchcnt.'/'. $n) : 0);
126 //=========================================================================================
127 // 2.3 specific update functions
133 function update_totals($pref)
135 global $path_to_root;
137 include_once("$path_to_root/sales/includes/cart_class.inc");
138 include_once("$path_to_root/purchasing/includes/po_class.inc");
139 $cart = new cart(ST_SALESORDER);
140 $sql = "SELECT order_no FROM {$pref}sales_orders";
141 $orders = db_query($sql);
145 while ($order_no = db_fetch($orders)) {
146 read_sales_order($order_no[0], $cart, ST_SALESORDER);
147 $result = db_query("UPDATE {$pref}sales_orders SET total=".$cart->get_trans_total());
148 unset($cart->line_items);
151 $cart = new purch_order();
152 $sql = "SELECT order_no FROM {$pref}purch_orders";
153 $orders = db_query($sql);
156 while ($order_no = db_fetch($orders)) {
157 read_po($order_no[0], $cart);
158 $result = db_query("UPDATE {$pref}purch_orders SET total=".$cart->get_trans_total());
159 unset($cart->line_items);
164 //------------------------------------------------------------------------------
165 // Retreive parent document number(s) for given transaction
167 function get_parent_trans_2_2($trans_type, $trans_no) {
169 $sql = 'SELECT trans_link FROM
170 '.TB_PREF.'debtor_trans WHERE trans_no='.db_escape($trans_no)
171 .' AND type='.db_escape($trans_type).' AND trans_link!=0';
173 $result = db_query($sql, 'Parent document numbers cannot be retrieved');
175 if (db_num_rows($result)) {
176 $link = db_fetch($result);
177 return array($link['trans_link']);
179 if ($trans_type!=ST_SALESINVOICE) return 0; // this is credit note with no parent invoice
180 // invoice: find batch invoice parent trans.
181 $sql = 'SELECT trans_no FROM
182 '.TB_PREF.'debtor_trans WHERE
183 (trans_link='.db_escape($trans_no).' AND type='. get_parent_type($trans_type) .')';
185 $result = db_query($sql, 'Delivery links cannot be retrieved');
188 if(db_num_rows($result)>0) {
189 while($link = db_fetch($result)) {
190 $delivery[] = $link['trans_no'];
193 return count($delivery) ? $delivery : 0;
197 Reorganizing document relations. Due to the design issue in pre 2.3 db structure
198 there can be sales documents with lines not properly linked to parents. This rare
199 cases will be described in error log.
201 function update_line_relations($pref)
203 global $path_to_root, $systypes_array;
205 require_once("$path_to_root/includes/sysnames.inc");
207 $sql = "SELECT d.type, trans_no, order_ FROM {$pref}debtor_trans d
208 LEFT JOIN {$pref}voided v ON d.type=v.type AND d.trans_no=v.id
209 WHERE ISNULL(v.type) AND
210 (d.type=".ST_CUSTDELIVERY
211 ." OR d.type=".ST_SALESINVOICE
212 ." OR d.type=".ST_CUSTCREDIT.")";
213 $result = db_query($sql);
217 while ($trans = db_fetch($result)) {
218 $type = $trans['type'];
219 $trans_no = $trans['trans_no'];
223 $lines = get_customer_trans_details($type, $trans_no);
224 $n = db_num_rows($lines);
226 if ($type==ST_CUSTDELIVERY)
227 $src_lines = get_sales_order_details($trans['order_'], ST_SALESORDER);
229 $src_lines = get_customer_trans_details(get_parent_type($type),
230 $this->get_parent_trans_2_2($type, $trans_no));
232 $src_n = db_num_rows($src_lines);
234 if (($type == ST_CUSTCREDIT) && ($src_n == 0))
235 continue; // free credit note has no src lines
237 $max = $type == ST_CUSTDELIVERY ? $n : max($src_n, $n);
239 for($i = 0, $j=0; $i < $max; $i++) {
240 if (!($doc_line = @db_fetch($lines)))
243 if(!($src_line = @db_fetch($src_lines)))
246 if ($type == ST_CUSTDELIVERY)
247 $src_line['stock_id'] = $src_line['stk_code']; // SO details has another field name
249 if ($src_line['stock_id'] == $doc_line['stock_id']
250 && ($src_line['quantity'] >= $doc_line['quantity'])) {
252 $sql = "UPDATE {$pref}debtor_trans_details SET src_id = {$src_line['id']}
253 WHERE id = {$doc_line['id']}";
260 error_log("Line level relations error for ".$systypes_array[$type]." #$trans_no.");
266 function fix_extensions()
268 global $path_to_root, $next_extension_id, $installed_languages;
271 foreach($installed_languages as $i => $lang) {
272 if (!isset($lang['path'])) {
273 $code = $lang['code'];
274 $installed_languages[$i]['path'] = 'lang/'.$code;
275 $installed_languages[$i]['package'] = $code;
282 $installed_extensions= get_company_extensions();
284 if (!isset($next_extension_id))
285 $next_extension_id = 1;
288 foreach($installed_extensions as $i => $ext)
290 if (isset($ext['title'])) // old type entry
292 if ($ext['type'] == 'module') {
293 $new['type'] = 'extension';
294 $new['tabs'][] = array(
295 'url' => $ext['filename'],
296 'access' => isset($ext['access']) ? $ext['access'] : 'SA_OPEN',
297 'tab_id' => $ext['tab'],
298 'title' => $ext['title']
300 $new['path'] = $ext['path'];
304 $new['type'] = 'extension';
305 $new['tabs'] = array();
306 $new['path'] = 'modules/'.$ext['path'];
307 $new['entries'][] = array(
308 'url' => $ext['filename'],
309 'access' => isset($ext['access']) ? $ext['access'] : 'SA_OPEN',
310 'tab_id' => $ext['tab'],
311 'title' => $ext['title']
314 if (isset($ext['acc_file']))
315 $new['acc_file'] = $ext['acc_file'];
316 $new['name'] = $ext['name']; // albo access_string(title)
317 $new['package'] = $new['package'] = '';
320 $new_exts[$i] = $new;
323 // Add non-standard themes
324 $path = $path_to_root.'/themes/';
326 $themedir = opendir($path);
327 while (false !== ($fname = readdir($themedir)))
329 if ($fname!='.' && $fname!='..' && $fname!='CVS' && is_dir($path.$fname)
330 && !in_array($fname, array('aqua', 'cool', 'default')))
332 foreach($installed_extensions as $ext)
333 if ($ext['path'] == 'themes/'.$fname) // skip if theme is already listed
335 $new_exts[$next_extension_id++] = array(
336 'name' => 'Theme '. ucwords($fname),
340 'path' => 'themes/'.$fname
346 if (count($new_exts)) {
347 return update_extensions($new_exts);
353 $install = new fa2_3;