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($pref, $force)
  28         {
  29                 global $db_version, $dflt_lang;
  30
  31                 if (!$this->preconf)
  32                         return false;
  33
  34                 if (!$this->beta) {
  35                         // all specials below are already done on 2.3beta
  36
  37                         $sql = "SELECT debtor_no, payment_terms FROM {$pref}debtors_master";
  38
  39                         $result = db_query($sql);
  40                         if (!$result) {
  41                                 display_error("Cannot read customers"
  42                                 .':<br>'. db_error_msg($db));
  43                                 return false;
  44                         }
  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));
  53                                         return false;
  54                                 }
  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));
  61                                         return false;
  62                                 }
  63                         }
  64                         if (!$this->update_totals($pref)) {
  65                                 display_error("Cannot update order totals");
  66                                 return false;
  67                         }
  68                         if (!$this->update_line_relations($pref)) {
  69                                 display_error("Cannot update sales document links");
  70                                 return false;
  71                         }
  72                         //remove obsolete and temporary columns.
  73                         // this have to be done here as db_import rearranges alter query order
  74                         $dropcol = array(
  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')
  80                         );
  81
  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));
  86                                                 return false;
  87                                         }
  88                                 }
  89                         // remove old preferences table after upgrade script has been executed
  90                         $sql = "DROP TABLE IF EXISTS `{$pref}company`";
  91                         if (!db_query($sql))
  92                                 return false;
  93                 }
  94                 $this->update_lang_cfg();
  95                 return  update_company_prefs(array('version_id'=>$db_version), $pref);
  96         }
  97         //
  98         //      Checking before install
  99         //
 100         function pre_check($pref, $force)
 101         {
 102
 103                 if ($this->beta && !$force)
 104                         $this->sql = 'alter2.3rc.sql';
 105
 106                 return true;
 107         }
 108         //
 109         //      Test if patch was applied before.
 110         //
 111         function installed($pref) {
 112                 $this->beta = !check_table($pref, 'suppliers', 'tax_included');
 113                 $this->preconf = $this->fix_extensions();
 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($pref)
 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 FROM {$pref}sales_orders";
 142                 $orders = db_query($sql);
 143                 if (!$orders)
 144                         return false;
 145
 146                 while ($order_no = db_fetch($orders)) {
 147                         read_sales_order($order_no[0], $cart, ST_SALESORDER);
 148                         $result = db_query("UPDATE {$pref}sales_orders SET total=".$cart->get_trans_total());
 149                         unset($cart->line_items);
 150                 }
 151                 unset($cart);
 152                 $cart = new purch_order();
 153                 $sql = "SELECT order_no FROM {$pref}purch_orders";
 154                 $orders = db_query($sql);
 155                 if (!$orders)
 156                          return false;
 157                 while ($order_no = db_fetch($orders)) {
 158                         read_po($order_no[0], $cart);
 159                         $result = db_query("UPDATE {$pref}purch_orders SET total=".$cart->get_trans_total());
 160                         unset($cart->line_items);
 161                 }
 162                 return true;
 163         }
 164
 165         //------------------------------------------------------------------------------
 166         //      Retreive parent document number(s) for given transaction
 167         //
 168         function get_parent_trans_2_2($trans_type, $trans_no) {
 169
 170                 $sql = 'SELECT trans_link FROM
 171                                 '.TB_PREF.'debtor_trans WHERE trans_no='.db_escape($trans_no)
 172                                 .' AND type='.db_escape($trans_type).' AND trans_link!=0';
 173
 174                 $result = db_query($sql, 'Parent document numbers cannot be retrieved');
 175
 176                 if (db_num_rows($result)) {
 177                         $link = db_fetch($result);
 178                         return array($link['trans_link']);
 179                 }
 180                 if ($trans_type!=ST_SALESINVOICE) return 0;     // this is credit note with no parent invoice
 181                 // invoice: find batch invoice parent trans.
 182                 $sql = 'SELECT trans_no FROM
 183                                 '.TB_PREF.'debtor_trans WHERE
 184                                 (trans_link='.db_escape($trans_no).' AND type='. get_parent_type($trans_type) .')';
 185
 186                 $result = db_query($sql, 'Delivery links cannot be retrieved');
 187
 188                 $delivery = array();
 189                 if(db_num_rows($result)>0) {
 190                         while($link = db_fetch($result)) {
 191                                 $delivery[] = $link['trans_no'];
 192                         }
 193                 }
 194                 return count($delivery) ? $delivery : 0;
 195         }
 196
 197         /*
 198                 Reorganizing document relations. Due to the design issue in pre 2.3 db structure
 199                 there can be sales documents with lines not properly linked to parents. This rare
 200                 cases will be described in error log.
 201         */
 202         function update_line_relations($pref)
 203         {
 204                 global $path_to_root, $systypes_array;
 205
 206                 require_once("$path_to_root/includes/sysnames.inc");
 207
 208                 $sql =  "SELECT d.type, trans_no, order_ FROM {$pref}debtor_trans d
 209                         LEFT JOIN {$pref}voided v ON d.type=v.type AND d.trans_no=v.id
 210                                 WHERE ISNULL(v.type) AND
 211                                 (d.type=".ST_CUSTDELIVERY
 212                                 ." OR d.type=".ST_SALESINVOICE
 213                                 ." OR d.type=".ST_CUSTCREDIT.")";
 214                 $result = db_query($sql);
 215                 if (!$result)
 216                         return false;
 217
 218                 while ($trans = db_fetch($result)) {
 219                         $type = $trans['type'];
 220                         $trans_no = $trans['trans_no'];
 221                         $invalid = 0;
 222                         $msg ='';
 223
 224                         $lines = get_customer_trans_details($type, $trans_no);
 225                         $n = db_num_rows($lines);
 226
 227                         if ($type==ST_CUSTDELIVERY)
 228                                 $src_lines = get_sales_order_details($trans['order_'], ST_SALESORDER);
 229                         else
 230                                 $src_lines =  get_customer_trans_details(get_parent_type($type),
 231                                         $this->get_parent_trans_2_2($type, $trans_no));
 232
 233                         $src_n = db_num_rows($src_lines);
 234
 235                         if (($type == ST_CUSTCREDIT) && ($src_n == 0))
 236                                  continue;  // free credit note has no src lines
 237
 238                         $max = $type == ST_CUSTDELIVERY ? $n : max($src_n, $n);
 239
 240                         for($i = 0, $j=0; $i < $max; $i++) {
 241                                 if (!($doc_line = @db_fetch($lines)))
 242                                         break;
 243
 244                                 if(!($src_line = @db_fetch($src_lines)))
 245                                         break;
 246
 247                                 if ($type == ST_CUSTDELIVERY)
 248                                         $src_line['stock_id'] = $src_line['stk_code']; // SO details has another field name
 249
 250                                 if ($src_line['stock_id'] == $doc_line['stock_id']
 251                                         && ($src_line['quantity'] >= $doc_line['quantity'])) {
 252
 253                                         $sql = "UPDATE {$pref}debtor_trans_details SET src_id = {$src_line['id']}
 254                                                 WHERE id = {$doc_line['id']}";
 255                                         if (!db_query($sql))
 256                                                 return false;
 257                                         $j++;
 258                                 }
 259                         }
 260                         if ($j != $n) {
 261                                 error_log("Line level relations error for ".$systypes_array[$type]." #$trans_no.");
 262                         }
 263                 }
 264         return true;
 265         }
 266
 267         function fix_extensions()
 268         {
 269                 global $path_to_root, $next_extension_id, $installed_languages;
 270
 271                 $lang_chd = false;
 272                 foreach($installed_languages as $i => $lang) {
 273                         if (!isset($lang['path'])) {
 274                                 $code = $lang['code'];
 275                                 $installed_languages[$i]['path'] = 'lang/'.$code;
 276                                 $installed_languages[$i]['package'] = $code;
 277                                 $lang_chd = true;
 278                         }
 279                 }
 280                 if ($lang_chd)
 281                         write_lang();
 282
 283                 $installed_extensions= get_company_extensions();
 284
 285                 if (!isset($next_extension_id))
 286                         $next_extension_id = 1;
 287                 $new_exts = array();
 288
 289                 foreach($installed_extensions as $i => $ext)
 290                 {
 291                         if (isset($ext['title'])) // old type entry
 292                         {
 293                                 if ($ext['type'] == 'module') {
 294                                         $new['type'] = 'extension';
 295                                         $new['tabs'][] = 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                                         $new['path'] = $ext['path'];
 302                                 }
 303                                 else // plugin
 304                                 {
 305                                         $new['type'] = 'extension';
 306                                         $new['tabs'] = array();
 307                                         $new['path'] = 'modules/'.$ext['path'];
 308                                         $new['entries'][] = array(
 309                                                 'url' => $ext['filename'],
 310                                                 'access' => isset($ext['access']) ? $ext['access'] : 'SA_OPEN',
 311                                                 'tab_id' => $ext['tab'],
 312                                                 'title' => $ext['title']
 313                                         );
 314                                 }
 315                                 if (isset($ext['acc_file']))
 316                                         $new['acc_file'] = $ext['acc_file'];
 317                                 $new['name'] = $ext['name']; // albo access_string(title)
 318                                 $new['package'] = $new['package'] = '';
 319                                 $new['active'] = 1;
 320
 321                                 $new_exts[$i] = $new;
 322                         }
 323                 }
 324                 // Add non-standard themes
 325                 $path = $path_to_root.'/themes/';
 326                 $themes = array();
 327                 $themedir = opendir($path);
 328                 while (false !== ($fname = readdir($themedir)))
 329                 {
 330                         if ($fname!='.' && $fname!='..' && $fname!='CVS' && is_dir($path.$fname)
 331                                 && !in_array($fname, array('aqua', 'cool', 'default')))
 332                         {
 333                                 foreach($installed_extensions as $ext)
 334                                         if ($ext['path'] == 'themes/'.$fname) // skip if theme is already listed
 335                                                 continue 2;
 336                                 $new_exts[$next_extension_id++] = array(
 337                                         'name' => 'Theme '. ucwords($fname),
 338                                         'package' => $fname,
 339                                         'type' => 'theme',
 340                                         'active' => true,
 341                                         'path' => 'themes/'.$fname
 342                                 );
 343                         }
 344                 }
 345                 closedir($themedir);
 346
 347                 if (count($new_exts)) {
 348                         return update_extensions($new_exts);
 349                 } else
 350                         return true;
 351         }
 352
 353         function update_lang_cfg()
 354         {
 355                 global $dflt_lang, $installed_languages;
 356
 357                 foreach($installed_languages as $n => $lang) {
 358                         if ($lang['code'] == 'en_GB') {
 359                                 $installed_languages[$n] = array('code'=>'C','name'=>'English',
 360                                         'encoding'=>'iso-8859-1', 'path' => '', 'package' => '');
 361                                 if ($dflt_lang == 'en_GB')
 362                                         $dflt_lang = 'C';
 363                                 write_lang();
 364                         }
 365                 }
 366         }
 367 }
 368
 369 $install = new fa2_3;
 370
 371 ?>