sql/alter2.3.php

   1 <?php
   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 {
  13         var $version = '2.3';   // version installed
  14         var $description;
  15         var $sql = 'alter2.3.sql';
  16         var $preconf = true;
  17         var $beta = false; // upgrade from 2.2 or 2.3beta;
  18
  19         function fa2_3() {
  20                 $this->description = _('Upgrade from version 2.2 to 2.3');
  21         }
  22
  23         //
  24         //      Install procedure. All additional changes
  25         //      not included in sql file should go here.
  26         //
  27         function install($company, $force)
  28         {
  29                 global $db_version, $dflt_lang;
  30
  31                 $this->preconf = $this->fix_extensions();
  32                 if (!$this->preconf)
  33                         return false;
  34
  35                 if (!$this->beta) {
  36                         // all specials below are already done on 2.3beta
  37
  38                         $sql = "SELECT debtor_no, payment_terms FROM ".TB_PREF."debtors_master";
  39
  40                         $result = db_query($sql);
  41                         if (!$result) {
  42                                 display_error("Cannot read customers"
  43                                 .':<br>'. db_error_msg($db));
  44                                 return false;
  45                         }
  46                         // update all sales orders and transactions with customer std payment terms
  47                         while($cust = db_fetch($result)) {
  48                                 $sql = "UPDATE ".TB_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));
  54                                         return false;
  55                                 }
  56                                 $sql = "UPDATE ".TB_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));
  62                                         return false;
  63                                 }
  64                         }
  65                         if (!$this->update_totals()) {
  66                                 display_error("Cannot update order totals");
  67                                 return false;
  68                         }
  69                         if (!$this->update_line_relations()) {
  70                                 display_error("Cannot update sales document links");
  71                                 return false;
  72                         }
  73                         //remove obsolete and temporary columns.
  74                         // this have to be done here as db_import rearranges alter query order
  75                         $dropcol = array(
  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')
  81                         );
  82
  83                         foreach($dropcol as $table => $columns)
  84                                 foreach($columns as $col) {
  85                                         if (db_query("ALTER TABLE `".TB_PREF."{$table}` DROP `$col`")==false) {
  86                                                 display_error("Cannot drop {$table}.{$col} column:<br>".db_error_msg($db));
  87                                                 return false;
  88                                         }
  89                                 }
  90                         // remove old preferences table after upgrade script has been executed
  91                         $sql = "DROP TABLE IF EXISTS `".TB_PREF."company`";
  92                         if (!db_query($sql))
  93                                 return false;
  94                 }
  95                 $this->update_lang_cfg();
  96                 return  update_company_prefs(array('version_id'=>$db_version));
  97         }
  98         //
  99         //      Checking before install
 100         //
 101         function pre_check($pref, $force)
 102         {
 103
 104                 if ($this->beta && !$force)
 105                         $this->sql = 'alter2.3rc.sql';
 106
 107                 return true;
 108         }
 109         //
 110         //      Test if patch was applied before.
 111         //
 112         function installed($pref) {
 113                 $this->beta = !check_table($pref, 'suppliers', 'tax_included');
 114
 115                 $n = 1; // number of patches to be installed
 116                 $patchcnt = 0;
 117
 118                 if (!$this->beta) {
 119                         $n += 3;
 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++;
 123                 }
 124                 if (!check_table($pref, 'purch_orders', 'tax_included')) $patchcnt++;
 125                 return $n == $patchcnt ? true : ($patchcnt ? ($patchcnt.'/'. $n) : 0);
 126         }
 127         //=========================================================================================
 128         //      2.3 specific update functions
 129         //
 130
 131         /*
 132                 Update order totals
 133         */
 134         function update_totals()
 135         {
 136                 global $path_to_root;
 137
 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 ".TB_PREF."sales_orders";
 142                 $orders = db_query($sql);
 143                 if (!$orders)
 144                         return false;
 145                 while ($order = db_fetch($orders)) {
 146                         read_sales_order($order['order_no'], $cart, $order['trans_type']);
 147                         $result = db_query("UPDATE ".TB_PREF."sales_orders
 148                                 SET total=".$cart->get_trans_total()
 149                                 ." WHERE order_no=".$order[0]);
 150                         unset($cart->line_items);
 151                 }
 152                 unset($cart);
 153                 $cart = new purch_order();
 154                 $sql = "SELECT order_no FROM ".TB_PREF."purch_orders";
 155                 $orders = db_query($sql);
 156                 if (!$orders)
 157                          return false;
 158                 while ($order_no = db_fetch($orders)) {
 159                         read_po($order_no[0], $cart);
 160                         $result = db_query("UPDATE ".TB_PREF."purch_orders SET total=".$cart->get_trans_total());
 161                         unset($cart->line_items);
 162                 }
 163                 return true;
 164         }
 165
 166         //------------------------------------------------------------------------------
 167         //      Retreive parent document number(s) for given transaction
 168         //
 169         function get_parent_trans_2_2($trans_type, $trans_no) {
 170
 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';
 174
 175                 $result = db_query($sql, 'Parent document numbers cannot be retrieved');
 176
 177                 if (db_num_rows($result)) {
 178                         $link = db_fetch($result);
 179                         return array($link['trans_link']);
 180                 }
 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) .')';
 186
 187                 $result = db_query($sql, 'Delivery links cannot be retrieved');
 188
 189                 $delivery = array();
 190                 if(db_num_rows($result)>0) {
 191                         while($link = db_fetch($result)) {
 192                                 $delivery[] = $link['trans_no'];
 193                         }
 194                 }
 195                 return count($delivery) ? $delivery : 0;
 196         }
 197
 198         /*
 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.
 202         */
 203         function update_line_relations()
 204         {
 205                 global $path_to_root, $systypes_array;
 206
 207                 require_once("$path_to_root/includes/sysnames.inc");
 208
 209                 $sql =  "SELECT d.type, trans_no, order_ FROM ".TB_PREF."debtor_trans d
 210                         LEFT JOIN ".TB_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);
 216                 if (!$result)
 217                         return false;
 218
 219                 while ($trans = db_fetch($result)) {
 220                         $type = $trans['type'];
 221                         $trans_no = $trans['trans_no'];
 222                         $invalid = 0;
 223                         $msg ='';
 224
 225                         $lines = get_customer_trans_details($type, $trans_no);
 226                         $n = db_num_rows($lines);
 227
 228                         if ($type==ST_CUSTDELIVERY)
 229                                 $src_lines = get_sales_order_details($trans['order_'], ST_SALESORDER);
 230                         else
 231                                 $src_lines =  get_customer_trans_details(get_parent_type($type),
 232                                         $this->get_parent_trans_2_2($type, $trans_no));
 233
 234                         $src_n = db_num_rows($src_lines);
 235
 236                         if (($type == ST_CUSTCREDIT) && ($src_n == 0))
 237                                  continue;  // free credit note has no src lines
 238
 239                         $max = $type == ST_CUSTDELIVERY ? $n : max($src_n, $n);
 240
 241                         for($i = 0, $j=0; $i < $max; $i++) {
 242                                 if (!($doc_line = @db_fetch($lines)))
 243                                         break;
 244
 245                                 if(!($src_line = @db_fetch($src_lines)))
 246                                         break;
 247
 248                                 if ($type == ST_CUSTDELIVERY)
 249                                         $src_line['stock_id'] = $src_line['stk_code']; // SO details has another field name
 250
 251                                 if ($src_line['stock_id'] == $doc_line['stock_id']
 252                                         && ($src_line['quantity'] >= $doc_line['quantity'])) {
 253
 254                                         $sql = "UPDATE ".TB_PREF."debtor_trans_details SET src_id = {$src_line['id']}
 255                                                 WHERE id = {$doc_line['id']}";
 256                                         if (!db_query($sql))
 257                                                 return false;
 258                                         $j++;
 259                                 }
 260                         }
 261                         if ($j != $n) {
 262                                 error_log("Line level relations error for ".$systypes_array[$type]." #$trans_no.");
 263                         }
 264                 }
 265         return true;
 266         }
 267
 268         function fix_extensions()
 269         {
 270                 global $path_to_root, $next_extension_id, $installed_languages;
 271
 272                 $lang_chd = false;
 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;
 278                                 $lang_chd = true;
 279                         }
 280                 }
 281                 if ($lang_chd)
 282                         write_lang();
 283
 284
 285                 $installed_extensions= get_company_extensions();
 286                 if (!isset($next_extension_id))
 287                         $next_extension_id = 1;
 288                 $new_exts = array();
 289
 290 /*      Old extension modules are uninstalled - they need manual porting after
 291         heavy changes in extension system in FA2.3
 292
 293                 foreach($installed_extensions as $i => $ext)
 294                 {
 295                         if (isset($ext['title'])) // old type entry
 296                         {
 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']
 304                                         );
 305                                         $new['path'] = $ext['path'];
 306                                 }
 307                                 else // plugin
 308                                 {
 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']
 317                                         );
 318                                 }
 319                                 if (isset($ext['acc_file']))
 320                                         $new['acc_file'] = $ext['acc_file'];
 321                                 $new['name'] = $ext['name'];
 322                                 $new['package'] = $new['package'] = '';
 323                                 $new['active'] = 1;
 324
 325                                 $new_exts[$i] = $new;
 326                         }
 327                 }
 328 */
 329                 // Preserve non-standard themes
 330                 $path = $path_to_root.'/themes/';
 331                 $themes = array();
 332                 $themedir = opendir($path);
 333                 while (false !== ($fname = readdir($themedir)))
 334                 {
 335                         if ($fname!='.' && $fname!='..' && $fname!='CVS' && is_dir($path.$fname)
 336                                 && !in_array($fname, array('aqua', 'cool', 'default')))
 337                         {
 338                                 foreach($installed_extensions as $ext)
 339                                         if ($ext['path'] == 'themes/'.$fname) // skip if theme is already listed
 340                                                 continue 2;
 341                                 $new_exts[$next_extension_id++] = array(
 342                                         'name' => 'Theme '. ucwords($fname),
 343                                         'package' => $fname,
 344                                         'type' => 'theme',
 345                                         'active' => true,
 346                                         'path' => 'themes/'.$fname
 347                                 );
 348                         }
 349                 }
 350                 closedir($themedir);
 351
 352                 if (count($new_exts)) {
 353                         return update_extensions($new_exts);
 354                 } else
 355                         return true;
 356         }
 357
 358         function update_lang_cfg()
 359         {
 360                 global $dflt_lang, $installed_languages;
 361
 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')
 367                                         $dflt_lang = 'C';
 368                                 write_lang();
 369                         }
 370                 }
 371         }
 372
 373 }
 374
 375 $install = new fa2_3;
 376
 377 ?>