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 extends fa_patch {
  13         var $previous = '2.2rc';                // applicable database version
  14         var $version = '2.3rc'; // version installed
  15         var $description;
  16         var $sql = 'alter2.3.sql';
  17         var $preconf = true;
  18         var $beta = false; // upgrade from 2.2 or 2.3beta;
  19
  20         function __construct() {
  21                 $this->description = _('Upgrade from version 2.2 to 2.3');
  22         }
  23
  24         //
  25         //      Install procedure. All additional changes
  26         //      not included in sql file should go here.
  27         //
  28         function install($company, $force=false)
  29         {
  30                 global $db_version, $dflt_lang;
  31
  32                 $this->preconf = $this->fix_extensions();
  33                 if (!$this->preconf)
  34                         return false;
  35
  36                 if (!$this->beta) {
  37                         // all specials below are already done on 2.3beta
  38
  39                         $sql = "SELECT debtor_no, payment_terms FROM ".TB_PREF."debtors_master";
  40
  41                         $result = db_query($sql);
  42                         if (!$result) {
  43                                 display_error("Cannot read customers"
  44                                 .':<br>'. db_error_msg($db));
  45                                 return false;
  46                         }
  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));
  55                                         return false;
  56                                 }
  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));
  63                                         return false;
  64                                 }
  65                         }
  66                         if (!$this->update_totals()) {
  67                                 display_error("Cannot update order totals");
  68                                 return false;
  69                         }
  70                         if (!$this->update_line_relations()) {
  71                                 display_error("Cannot update sales document links");
  72                                 return false;
  73                         }
  74                         //remove obsolete and temporary columns.
  75                         // this have to be done here as db_import rearranges alter query order
  76                         $dropcol = array(
  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')
  82                         );
  83
  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));
  88                                                 return false;
  89                                         }
  90                                 }
  91                         // remove old preferences table after upgrade script has been executed
  92                         $sql = "DROP TABLE IF EXISTS `".TB_PREF."company`";
  93                         if (!db_query($sql))
  94                                 return false;
  95                 }
  96                 $this->update_lang_cfg();
  97                 return  update_company_prefs(array('version_id'=>$db_version));
  98         }
  99         //
 100         //      Checking before install
 101         //
 102         function prepare()
 103         {
 104
 105                 if ($this->beta)
 106                         $this->sql = 'alter2.3rc.sql';
 107
 108                 return true;
 109         }
 110
 111         //=========================================================================================
 112         //      2.3 specific update functions
 113         //
 114
 115         /*
 116                 Update order totals
 117         */
 118         function update_totals()
 119         {
 120                 global $path_to_root;
 121
 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);
 127                 if (!$orders)
 128                         return false;
 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);
 135                 }
 136                 unset($cart);
 137                 $cart = new purch_order();
 138                 $sql = "SELECT order_no FROM ".TB_PREF."purch_orders";
 139                 $orders = db_query($sql);
 140                 if (!$orders)
 141                          return false;
 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);
 146                 }
 147                 return true;
 148         }
 149
 150         //------------------------------------------------------------------------------
 151         //      Retreive parent document number(s) for given transaction
 152         //
 153         function get_parent_trans_2_2($trans_type, $trans_no) {
 154
 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';
 158
 159                 $result = db_query($sql, 'Parent document numbers cannot be retrieved');
 160
 161                 if (db_num_rows($result)) {
 162                         $link = db_fetch($result);
 163                         return array($link['trans_link']);
 164                 }
 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) .')';
 170
 171                 $result = db_query($sql, 'Delivery links cannot be retrieved');
 172
 173                 $delivery = array();
 174                 if(db_num_rows($result)>0) {
 175                         while($link = db_fetch($result)) {
 176                                 $delivery[] = $link['trans_no'];
 177                         }
 178                 }
 179                 return count($delivery) ? $delivery : 0;
 180         }
 181
 182         /*
 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.
 186         */
 187         function update_line_relations()
 188         {
 189                 global $path_to_root, $systypes_array;
 190
 191                 require_once("$path_to_root/includes/sysnames.inc");
 192
 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);
 200                 if (!$result)
 201                         return false;
 202
 203                 while ($trans = db_fetch($result)) {
 204                         $type = $trans['type'];
 205                         $trans_no = $trans['trans_no'];
 206                         $invalid = 0;
 207                         $msg ='';
 208
 209                         $lines = get_customer_trans_details($type, $trans_no);
 210                         $n = db_num_rows($lines);
 211
 212                         if ($type==ST_CUSTDELIVERY)
 213                                 $src_lines = get_sales_order_details($trans['order_'], ST_SALESORDER);
 214                         else
 215                                 $src_lines =  get_customer_trans_details(get_parent_type($type),
 216                                         $this->get_parent_trans_2_2($type, $trans_no));
 217
 218                         $src_n = db_num_rows($src_lines);
 219
 220                         if (($type == ST_CUSTCREDIT) && ($src_n == 0))
 221                                  continue;  // free credit note has no src lines
 222
 223                         $max = $type == ST_CUSTDELIVERY ? $n : max($src_n, $n);
 224
 225                         for($i = 0, $j=0; $i < $max; $i++) {
 226                                 if (!($doc_line = @db_fetch($lines)))
 227                                         break;
 228
 229                                 if(!($src_line = @db_fetch($src_lines)))
 230                                         break;
 231
 232                                 if ($type == ST_CUSTDELIVERY)
 233                                         $src_line['stock_id'] = $src_line['stk_code']; // SO details has another field name
 234
 235                                 if ($src_line['stock_id'] == $doc_line['stock_id']
 236                                         && ($src_line['quantity'] >= $doc_line['quantity'])) {
 237
 238                                         $sql = "UPDATE ".TB_PREF."debtor_trans_details SET src_id = {$src_line['id']}
 239                                                 WHERE id = {$doc_line['id']}";
 240                                         if (!db_query($sql))
 241                                                 return false;
 242                                         $j++;
 243                                 }
 244                         }
 245                         if ($j != $n) {
 246                                 error_log("Line level relations error for ".$systypes_array[$type]." #$trans_no.");
 247                         }
 248                 }
 249         return true;
 250         }
 251
 252         function fix_extensions()
 253         {
 254                 global $path_to_root, $next_extension_id, $installed_languages;
 255
 256                 $lang_chd = false;
 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;
 262                                 $lang_chd = true;
 263                         }
 264                 }
 265                 if ($lang_chd)
 266                         write_lang();
 267
 268                 $installed_extensions= get_company_extensions();
 269                 if (!isset($next_extension_id))
 270                         $next_extension_id = 1;
 271                 $new_exts = array();
 272
 273 /*      Old extension modules are uninstalled - they need manual porting after
 274         heavy changes in extension system in FA2.3
 275
 276                 foreach($installed_extensions as $i => $ext)
 277                 {
 278                         if (isset($ext['title'])) // old type entry
 279                         {
 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']
 287                                         );
 288                                         $new['path'] = $ext['path'];
 289                                 }
 290                                 else // plugin
 291                                 {
 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']
 300                                         );
 301                                 }
 302                                 if (isset($ext['acc_file']))
 303                                         $new['acc_file'] = $ext['acc_file'];
 304                                 $new['name'] = $ext['name'];
 305                                 $new['package'] = $new['package'] = '';
 306                                 $new['active'] = 1;
 307
 308                                 $new_exts[$i] = $new;
 309                         }
 310                 }
 311 */
 312                 // Preserve non-standard themes
 313                 $path = $path_to_root.'/themes/';
 314                 $themes = array();
 315                 $themedir = opendir($path);
 316                 while (false !== ($fname = readdir($themedir)))
 317                 {
 318                         if ($fname!='.' && $fname!='..' && $fname!='CVS' && is_dir($path.$fname)
 319                                 && !in_array($fname, array('aqua', 'cool', 'default')))
 320                         {
 321                                 foreach($installed_extensions as $ext)
 322                                         if ($ext['path'] == 'themes/'.$fname) // skip if theme is already listed
 323                                                 continue 2;
 324                                 $new_exts[$next_extension_id++] = array(
 325                                         'name' => 'Theme '. ucwords($fname),
 326                                         'package' => $fname,
 327                                         'type' => 'theme',
 328                                         'active' => true,
 329                                         'path' => 'themes/'.$fname
 330                                 );
 331                         }
 332                 }
 333                 closedir($themedir);
 334
 335                 if (count($new_exts)) {
 336                         return update_extensions($new_exts);
 337                 } else
 338                         return true;
 339         }
 340
 341         function update_lang_cfg()
 342         {
 343                 global $dflt_lang, $installed_languages;
 344
 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')
 350                                         $dflt_lang = 'C';
 351                                 write_lang();
 352                         }
 353                 }
 354         }
 355
 356 }
 357
 358 $install = new fa2_3;
 359