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_3 extends fa_patch {
13 var $previous = '2.2rc'; // applicable database version
14 var $version = '2.3rc'; // version installed
16 var $sql = 'alter2.3.sql';
18 var $beta = false; // upgrade from 2.2 or 2.3beta;
21 $this->description = _('Upgrade from version 2.2 to 2.3');
25 // Install procedure. All additional changes
26 // not included in sql file should go here.
28 function install($company, $force=false)
30 global $db_version, $dflt_lang;
32 $this->preconf = $this->fix_extensions();
37 // all specials below are already done on 2.3beta
39 $sql = "SELECT debtor_no, payment_terms FROM ".TB_PREF."debtors_master";
41 $result = db_query($sql);
43 display_error("Cannot read customers"
44 .':<br>'. db_error_msg($db));
47 // update all sales orders and transactions with customer std payment terms
48 while($cust = db_fetch($result)) {
49 $sql = "UPDATE ".TB_PREF."debtor_trans SET "
50 ."payment_terms = '" .$cust['payment_terms']
51 ."' WHERE debtor_no='".$cust['debtor_no']."'";
52 if (db_query($sql)==false) {
53 display_error("Cannot update cust trans payment"
54 .':<br>'. db_error_msg($db));
57 $sql = "UPDATE ".TB_PREF."sales_orders SET "
58 ."payment_terms = '" .$cust['payment_terms']
59 ."' WHERE debtor_no='".$cust['debtor_no']."'";
60 if (db_query($sql)==false) {
61 display_error("Cannot update sales order payment"
62 .':<br>'. db_error_msg($db));
66 if (!$this->update_totals()) {
67 display_error("Cannot update order totals");
70 if (!$this->update_line_relations()) {
71 display_error("Cannot update sales document links");
74 //remove obsolete and temporary columns.
75 // this have to be done here as db_import rearranges alter query order
77 'crm_persons' => array('tmp_id','tmp_class'),
78 'debtors_master' => array('email'),
79 'cust_branch' => array('phone', 'phone2', 'fax', 'email'),
80 'suppliers' => array('phone', 'phone2', 'fax', 'email'),
81 'debtor_trans' => array('trans_link')
84 foreach($dropcol as $table => $columns)
85 foreach($columns as $col) {
86 if (db_query("ALTER TABLE `".TB_PREF."{$table}` DROP `$col`")==false) {
87 display_error("Cannot drop {$table}.{$col} column:<br>".db_error_msg($db));
91 // remove old preferences table after upgrade script has been executed
92 $sql = "DROP TABLE IF EXISTS `".TB_PREF."company`";
96 $this->update_lang_cfg();
97 return update_company_prefs(array('version_id'=>$db_version));
100 // Checking before install
106 $this->sql = 'alter2.3rc.sql';
111 //=========================================================================================
112 // 2.3 specific update functions
118 function update_totals()
120 global $path_to_root;
122 include_once("$path_to_root/sales/includes/cart_class.inc");
123 include_once("$path_to_root/purchasing/includes/po_class.inc");
124 $cart = new cart(ST_SALESORDER);
125 $sql = "SELECT order_no, trans_type FROM ".TB_PREF."sales_orders";
126 $orders = db_query($sql);
129 while ($order = db_fetch($orders)) {
130 read_sales_order($order['order_no'], $cart, $order['trans_type']);
131 $result = db_query("UPDATE ".TB_PREF."sales_orders
132 SET total=".$cart->get_trans_total()
133 ." WHERE order_no=".$order[0]);
134 unset($cart->line_items);
137 $cart = new purch_order();
138 $sql = "SELECT order_no FROM ".TB_PREF."purch_orders";
139 $orders = db_query($sql);
142 while ($order_no = db_fetch($orders)) {
143 read_po($order_no[0], $cart);
144 $result = db_query("UPDATE ".TB_PREF."purch_orders SET total=".$cart->get_trans_total());
145 unset($cart->line_items);
150 //------------------------------------------------------------------------------
151 // Retreive parent document number(s) for given transaction
153 function get_parent_trans_2_2($trans_type, $trans_no) {
155 $sql = 'SELECT trans_link FROM
156 '.TB_PREF.'debtor_trans WHERE trans_no='.db_escape($trans_no)
157 .' AND type='.db_escape($trans_type).' AND trans_link!=0';
159 $result = db_query($sql, 'Parent document numbers cannot be retrieved');
161 if (db_num_rows($result)) {
162 $link = db_fetch($result);
163 return array($link['trans_link']);
165 if ($trans_type!=ST_SALESINVOICE) return 0; // this is credit note with no parent invoice
166 // invoice: find batch invoice parent trans.
167 $sql = 'SELECT trans_no FROM
168 '.TB_PREF.'debtor_trans WHERE
169 (trans_link='.db_escape($trans_no).' AND type='. get_parent_type($trans_type) .')';
171 $result = db_query($sql, 'Delivery links cannot be retrieved');
174 if(db_num_rows($result)>0) {
175 while($link = db_fetch($result)) {
176 $delivery[] = $link['trans_no'];
179 return count($delivery) ? $delivery : 0;
183 Reorganizing document relations. Due to the design issue in pre 2.3 db structure
184 there can be sales documents with lines not properly linked to parents. This rare
185 cases will be described in error log.
187 function update_line_relations()
189 global $path_to_root, $systypes_array;
191 require_once("$path_to_root/includes/sysnames.inc");
193 $sql = "SELECT d.type, trans_no, order_ FROM ".TB_PREF."debtor_trans d
194 LEFT JOIN ".TB_PREF."voided v ON d.type=v.type AND d.trans_no=v.id
195 WHERE ISNULL(v.type) AND
196 (d.type=".ST_CUSTDELIVERY
197 ." OR d.type=".ST_SALESINVOICE
198 ." OR d.type=".ST_CUSTCREDIT.")";
199 $result = db_query($sql);
203 while ($trans = db_fetch($result)) {
204 $type = $trans['type'];
205 $trans_no = $trans['trans_no'];
209 $lines = get_customer_trans_details($type, $trans_no);
210 $n = db_num_rows($lines);
212 if ($type==ST_CUSTDELIVERY)
213 $src_lines = get_sales_order_details($trans['order_'], ST_SALESORDER);
215 $src_lines = get_customer_trans_details(get_parent_type($type),
216 $this->get_parent_trans_2_2($type, $trans_no));
218 $src_n = db_num_rows($src_lines);
220 if (($type == ST_CUSTCREDIT) && ($src_n == 0))
221 continue; // free credit note has no src lines
223 $max = $type == ST_CUSTDELIVERY ? $n : max($src_n, $n);
225 for($i = 0, $j=0; $i < $max; $i++) {
226 if (!($doc_line = @db_fetch($lines)))
229 if(!($src_line = @db_fetch($src_lines)))
232 if ($type == ST_CUSTDELIVERY)
233 $src_line['stock_id'] = $src_line['stk_code']; // SO details has another field name
235 if ($src_line['stock_id'] == $doc_line['stock_id']
236 && ($src_line['quantity'] >= $doc_line['quantity'])) {
238 $sql = "UPDATE ".TB_PREF."debtor_trans_details SET src_id = {$src_line['id']}
239 WHERE id = {$doc_line['id']}";
246 error_log("Line level relations error for ".$systypes_array[$type]." #$trans_no.");
252 function fix_extensions()
254 global $path_to_root, $next_extension_id, $installed_languages;
257 foreach($installed_languages as $i => $lang) {
258 if (!isset($lang['path'])) {
259 $code = $lang['code'];
260 $installed_languages[$i]['path'] = 'lang/'.$code;
261 $installed_languages[$i]['package'] = $code;
268 $installed_extensions= get_company_extensions();
269 if (!isset($next_extension_id))
270 $next_extension_id = 1;
273 /* Old extension modules are uninstalled - they need manual porting after
274 heavy changes in extension system in FA2.3
276 foreach($installed_extensions as $i => $ext)
278 if (isset($ext['title'])) // old type entry
280 if ($ext['type'] == 'module') {
281 $new['type'] = 'extension';
282 $new['tabs'][] = array(
283 'url' => $ext['filename'],
284 'access' => isset($ext['access']) ? $ext['access'] : 'SA_OPEN',
285 'tab_id' => $ext['tab'],
286 'title' => $ext['title']
288 $new['path'] = $ext['path'];
292 $new['type'] = 'extension';
293 $new['tabs'] = array();
294 $new['path'] = 'modules/'.$ext['path'];
295 $new['entries'][] = array(
296 'url' => $ext['filename'],
297 'access' => isset($ext['access']) ? $ext['access'] : 'SA_OPEN',
298 'tab_id' => $ext['tab'],
299 'title' => $ext['title']
302 if (isset($ext['acc_file']))
303 $new['acc_file'] = $ext['acc_file'];
304 $new['name'] = $ext['name'];
305 $new['package'] = $new['package'] = '';
308 $new_exts[$i] = $new;
312 // Preserve non-standard themes
313 $path = $path_to_root.'/themes/';
315 $themedir = opendir($path);
316 while (false !== ($fname = readdir($themedir)))
318 if ($fname!='.' && $fname!='..' && $fname!='CVS' && is_dir($path.$fname)
319 && !in_array($fname, array('aqua', 'cool', 'default')))
321 foreach($installed_extensions as $ext)
322 if ($ext['path'] == 'themes/'.$fname) // skip if theme is already listed
324 $new_exts[$next_extension_id++] = array(
325 'name' => 'Theme '. ucwords($fname),
329 'path' => 'themes/'.$fname
335 if (count($new_exts)) {
336 return update_extensions($new_exts);
341 function update_lang_cfg()
343 global $dflt_lang, $installed_languages;
345 foreach($installed_languages as $n => $lang) {
346 if ($lang['code'] == 'en_GB') {
347 $installed_languages[$n] = array('code'=>'C','name'=>'English',
348 'encoding'=>'iso-8859-1', 'path' => '', 'package' => '');
349 if ($dflt_lang == 'en_GB')
358 $install = new fa2_3;