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