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                 $this->preconf = $this->fix_extensions();
  22         }
  23
  24         //
  25         //      Install procedure. All additional changes
  26         //      not included in sql file should go here.
  27         //
  28         function install($pref, $force)
  29         {
  30                 global $core_version;
  31
  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 {$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 {$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 {$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($pref)) {
  66                                 display_error("Cannot update order totals");
  67                                 return false;
  68                         }
  69                         if (!$this->update_line_relations($pref)) {
  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 `{$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 `{$pref}company`";
  92                         if (!db_query($sql))
  93                                 return false;
  94                 }
  95                 return  update_company_prefs(array('version_id'=>$core_version), $pref);
  96         }
  97         //
  98         //      Checking before install
  99         //
 100         function pre_check($pref, $force)
 101         {
 102                 if ($this->beta && !$force)
 103                         $this->sql = 'alter2.3rc.sql';
 104
 105                 return true;
 106         }
 107         //
 108         //      Test if patch was applied before.
 109         //
 110         function installed($pref) {
 111                 $this->beta = !check_table($pref, 'suppliers', 'tax_included');
 112
 113                 $n = 1; // number of patches to be installed
 114                 $patchcnt = 0;
 115
 116                 if (!$this->beta) {
 117                         $n += 3;
 118                         if (!check_table($pref, 'comments', 'type', array('Key'=>'MUL'))) $patchcnt++;
 119                         if (!check_table($pref, 'sys_prefs')) $patchcnt++;
 120                         if (!check_table($pref, 'sales_orders', 'payment_terms')) $patchcnt++;
 121                 }
 122                 if (!check_table($pref, 'purch_orders', 'tax_included')) $patchcnt++;
 123                 return $n == $patchcnt ? true : ($patchcnt ? ($patchcnt.'/'. $n) : 0);
 124         }
 125         //=========================================================================================
 126         //      2.3 specific update functions
 127         //
 128
 129         /*
 130                 Update order totals
 131         */
 132         function update_totals($pref)
 133         {
 134                 global $path_to_root;
 135
 136                 include_once("$path_to_root/sales/includes/cart_class.inc");
 137                 include_once("$path_to_root/purchasing/includes/po_class.inc");
 138                 $cart = new cart(ST_SALESORDER);
 139                 $sql = "SELECT order_no FROM {$pref}sales_orders";
 140                 $orders = db_query($sql);
 141                 if (!$orders)
 142                         return false;
 143
 144                 while ($order_no = db_fetch($orders)) {
 145                         read_sales_order($order_no[0], $cart, ST_SALESORDER);
 146                         $result = db_query("UPDATE {$pref}sales_orders SET total=".$cart->get_trans_total());
 147                         unset($cart->line_items);
 148                 }
 149                 unset($cart);
 150                 $cart = new purch_order();
 151                 $sql = "SELECT order_no FROM {$pref}purch_orders";
 152                 $orders = db_query($sql);
 153                 if (!$orders)
 154                          return false;
 155                 while ($order_no = db_fetch($orders)) {
 156                         read_po($order_no[0], $cart);
 157                         $result = db_query("UPDATE {$pref}purch_orders SET total=".$cart->get_trans_total());
 158                         unset($cart->line_items);
 159                 }
 160                 return true;
 161         }
 162
 163         //------------------------------------------------------------------------------
 164         //      Retreive parent document number(s) for given transaction
 165         //
 166         function get_parent_trans_2_2($trans_type, $trans_no) {
 167
 168                 $sql = 'SELECT trans_link FROM
 169                                 '.TB_PREF.'debtor_trans WHERE trans_no='.db_escape($trans_no)
 170                                 .' AND type='.db_escape($trans_type).' AND trans_link!=0';
 171
 172                 $result = db_query($sql, 'Parent document numbers cannot be retrieved');
 173
 174                 if (db_num_rows($result)) {
 175                         $link = db_fetch($result);
 176                         return array($link['trans_link']);
 177                 }
 178                 if ($trans_type!=ST_SALESINVOICE) return 0;     // this is credit note with no parent invoice
 179                 // invoice: find batch invoice parent trans.
 180                 $sql = 'SELECT trans_no FROM
 181                                 '.TB_PREF.'debtor_trans WHERE
 182                                 (trans_link='.db_escape($trans_no).' AND type='. get_parent_type($trans_type) .')';
 183
 184                 $result = db_query($sql, 'Delivery links cannot be retrieved');
 185
 186                 $delivery = array();
 187                 if(db_num_rows($result)>0) {
 188                         while($link = db_fetch($result)) {
 189                                 $delivery[] = $link['trans_no'];
 190                         }
 191                 }
 192                 return count($delivery) ? $delivery : 0;
 193         }
 194
 195         /*
 196                 Reorganizing document relations. Due to the design issue in pre 2.3 db structure
 197                 there can be sales documents with lines not properly linked to parents. This rare
 198                 cases will be described in error log.
 199         */
 200         function update_line_relations($pref)
 201         {
 202                 global $path_to_root, $systypes_array;
 203
 204                 require_once("$path_to_root/includes/sysnames.inc");
 205
 206                 $sql =  "SELECT d.type, trans_no, order_ FROM {$pref}debtor_trans d
 207                         LEFT JOIN {$pref}voided v ON d.type=v.type AND d.trans_no=v.id
 208                                 WHERE ISNULL(v.type) AND
 209                                 (d.type=".ST_CUSTDELIVERY
 210                                 ." OR d.type=".ST_SALESINVOICE
 211                                 ." OR d.type=".ST_CUSTCREDIT.")";
 212                 $result = db_query($sql);
 213                 if (!$result)
 214                         return false;
 215
 216                 while ($trans = db_fetch($result)) {
 217                         $type = $trans['type'];
 218                         $trans_no = $trans['trans_no'];
 219                         $invalid = 0;
 220                         $msg ='';
 221
 222                         $lines = get_customer_trans_details($type, $trans_no);
 223                         $n = db_num_rows($lines);
 224
 225                         if ($type==ST_CUSTDELIVERY)
 226                                 $src_lines = get_sales_order_details($trans['order_'], ST_SALESORDER);
 227                         else
 228                                 $src_lines =  get_customer_trans_details(get_parent_type($type),
 229                                         $this->get_parent_trans_2_2($type, $trans_no));
 230
 231                         $src_n = db_num_rows($src_lines);
 232
 233                         if (($type == ST_CUSTCREDIT) && ($src_n == 0))
 234                                  continue;  // free credit note has no src lines
 235
 236                         $max = $type == ST_CUSTDELIVERY ? $n : max($src_n, $n);
 237
 238                         for($i = 0, $j=0; $i < $max; $i++) {
 239                                 if (!($doc_line = @db_fetch($lines)))
 240                                         break;
 241
 242                                 if(!($src_line = @db_fetch($src_lines)))
 243                                         break;
 244
 245                                 if ($type == ST_CUSTDELIVERY)
 246                                         $src_line['stock_id'] = $src_line['stk_code']; // SO details has another field name
 247
 248                                 if ($src_line['stock_id'] == $doc_line['stock_id']
 249                                         && ($src_line['quantity'] >= $doc_line['quantity'])) {
 250
 251                                         $sql = "UPDATE {$pref}debtor_trans_details SET src_id = {$src_line['id']}
 252                                                 WHERE id = {$doc_line['id']}";
 253                                         if (!db_query($sql))
 254                                                 return false;
 255                                         $j++;
 256                                 }
 257                         }
 258                         if ($j != $n) {
 259                                 error_log("Line level relations error for ".$systypes_array[$type]." #$trans_no.");
 260                         }
 261                 }
 262         return true;
 263         }
 264
 265         function fix_extensions()
 266         {
 267                 global $path_to_root, $next_extension_id, $installed_languages;
 268
 269                 $lang_chd = false;
 270                 foreach($installed_languages as $i => $lang) {
 271                         if (!isset($lang['path'])) {
 272                                 $installed_languages[$i]['path'] = 'lang/'.$code;
 273                                 $installed_languages[$i]['package'] = 'lang/'.$code;
 274                                 $lang_chd = true;
 275                         }
 276                 }
 277                 if ($lang_chd)
 278                         write_lang();
 279
 280                 $installed_extensions= get_company_extensions();
 281
 282                 if (!isset($next_extension_id))
 283                         $next_extension_id = 1;
 284                 $new_exts = array();
 285
 286                 foreach($installed_extensions as $i => $ext)
 287                 {
 288                         if (isset($ext['title'])) // old type entry
 289                         {
 290                                 if ($ext['type'] == 'module') {
 291                                         $new['type'] = 'extension';
 292                                         $new['tabs'][] = array(
 293                                                 'url' => $ext['filename'],
 294                                                 'access' => isset($ext['access']) ? $ext['access'] : 'SA_OPEN',
 295                                                 'tab_id' => $ext['tab'],
 296                                                 'title' => $ext['title']
 297                                         );
 298                                         $new['path'] = $ext['path'];
 299                                 }
 300                                 else // plugin
 301                                 {
 302                                         $new['type'] = 'extension';
 303                                         $new['tabs'] = array();
 304                                         $new['path'] = 'modules/'.$ext['path'];
 305                                         $new['entries'][] = array(
 306                                                 'url' => $ext['filename'],
 307                                                 'access' => isset($ext['access']) ? $ext['access'] : 'SA_OPEN',
 308                                                 'tab_id' => $ext['tab'],
 309                                                 'title' => $ext['title']
 310                                         );
 311                                 }
 312                                 if (isset($ext['acc_file']))
 313                                         $new['acc_file'] = $ext['acc_file'];
 314                                 $new['name'] = $ext['name']; // albo access_string(title)
 315                                 $new['package'] = $new['package'] = '';
 316                                 $new['active'] = 1;
 317
 318                                 $new_exts[$i] = $new;
 319                         }
 320                 }
 321                 // Add non-standard themes
 322                 $path = $path_to_root.'/themes/';
 323                 $themes = array();
 324                 $themedir = opendir($path);
 325                 while (false !== ($fname = readdir($themedir)))
 326                 {
 327                         if ($fname!='.' && $fname!='..' && $fname!='CVS' && is_dir($path.$fname)
 328                                 && !in_array($fname, array('aqua', 'cool', 'default')))
 329                         {
 330                                 foreach($installed_extensions as $ext)
 331                                         if ($ext['path'] == 'themes/'.$fname) // skip if theme is already listed
 332                                                 continue 2;
 333                                 $new_exts[$next_extension_id++] = array(
 334                                         'name' => 'Theme '. ucwords($fname),
 335                                         'package' => $fname,
 336                                         'type' => 'theme',
 337                                         'active' => true,
 338                                         'path' => 'themes/'.$fname
 339                                 );
 340                         }
 341                 }
 342                 closedir($themedir);
 343
 344                 if (count($new_exts)) {
 345                         return update_extensions($new_exts);
 346                 } else
 347                         return true;
 348         }
 349 }
 350
 351 $install = new fa2_3;
 352
 353 ?>