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 $core_version;
  30
  31                 if (!$this->beta) {
  32                         // all specials below are already done on 2.3beta
  33
  34                         $sql = "SELECT debtor_no, payment_terms FROM {$pref}debtors_master";
  35
  36                         $result = db_query($sql);
  37                         if (!$result) {
  38                                 display_error("Cannot read customers"
  39                                 .':<br>'. db_error_msg($db));
  40                                 return false;
  41                         }
  42                         // update all sales orders and transactions with customer std payment terms
  43                         while($cust = db_fetch($result)) {
  44                                 $sql = "UPDATE {$pref}debtor_trans SET "
  45                                         ."payment_terms = '" .$cust['payment_terms']
  46                                         ."' WHERE debtor_no='".$cust['debtor_no']."'";
  47                                 if (db_query($sql)==false) {
  48                                         display_error("Cannot update cust trans payment"
  49                                         .':<br>'. db_error_msg($db));
  50                                         return false;
  51                                 }
  52                                 $sql = "UPDATE {$pref}sales_orders SET "
  53                                         ."payment_terms = '" .$cust['payment_terms']
  54                                         ."' WHERE debtor_no='".$cust['debtor_no']."'";
  55                                 if (db_query($sql)==false) {
  56                                         display_error("Cannot update sales order payment"
  57                                         .':<br>'. db_error_msg($db));
  58                                         return false;
  59                                 }
  60                         }
  61                         if (!$this->update_totals($pref)) {
  62                                 display_error("Cannot update order totals");
  63                                 return false;
  64                         }
  65                         if (!$this->update_line_relations($pref)) {
  66                                 display_error("Cannot update sales document links");
  67                                 return false;
  68                         }
  69                         //remove obsolete and temporary columns.
  70                         // this have to be done here as db_import rearranges alter query order
  71                         $dropcol = array(
  72                                 'crm_persons' => array('tmp_id','tmp_class'),
  73                                 'debtors_master' => array('email'),
  74                                 'cust_branch' => array('phone', 'phone2', 'fax', 'email'),
  75                                 'suppliers' => array('phone', 'phone2', 'fax', 'email'),
  76                                 'debtor_trans' => array('trans_link')
  77                         );
  78
  79                         foreach($dropcol as $table => $columns)
  80                                 foreach($columns as $col) {
  81                                         if (db_query("ALTER TABLE `{$pref}{$table}` DROP `$col`")==false) {
  82                                                 display_error("Cannot drop {$table}.{$col} column:<br>".db_error_msg($db));
  83                                                 return false;
  84                                         }
  85                                 }
  86                         // remove old preferences table after upgrade script has been executed
  87                         $sql = "DROP TABLE IF EXISTS `{$pref}company`";
  88                         if (!db_query($sql))
  89                                 return false;
  90                 }
  91                 return  update_company_prefs(array('version_id'=>$core_version), $pref);
  92         }
  93         //
  94         //      Checking before install
  95         //
  96         function pre_check($pref, $force)
  97         {
  98                 if ($this->beta && !$force)
  99                         $this->sql = 'alter2.3rc.sql';
 100
 101                 return true;
 102         }
 103         //
 104         //      Test if patch was applied before.
 105         //
 106         function installed($pref) {
 107                 $this->beta = !check_table($pref, 'suppliers', 'tax_included');
 108
 109                 $n = 1; // number of patches to be installed
 110                 $patchcnt = 0;
 111
 112                 if (!$this->beta) {
 113                         $n += 3;
 114                         if (!check_table($pref, 'comments', 'type', array('Key'=>'MUL'))) $patchcnt++;
 115                         if (!check_table($pref, 'sys_prefs')) $patchcnt++;
 116                         if (!check_table($pref, 'sales_orders', 'payment_terms')) $patchcnt++;
 117                 }
 118                 if (!check_table($pref, 'purch_orders', 'tax_included')) $patchcnt++;
 119                 return $n == $patchcnt ? true : ($patchcnt ? ($patchcnt.'/'. $n) : 0);
 120         }
 121         //=========================================================================================
 122         //      2.3 specific update functions
 123         //
 124
 125         /*
 126                 Update order totals
 127         */
 128         function update_totals($pref)
 129         {
 130                 global $path_to_root;
 131
 132                 include_once("$path_to_root/sales/includes/cart_class.inc");
 133                 include_once("$path_to_root/purchasing/includes/po_class.inc");
 134                 $cart = new cart(ST_SALESORDER);
 135                 $sql = "SELECT order_no FROM {$pref}sales_orders";
 136                 $orders = db_query($sql);
 137                 if (!$orders)
 138                         return false;
 139
 140                 while ($order_no = db_fetch($orders)) {
 141                         read_sales_order($order_no[0], $cart, ST_SALESORDER);
 142                         $result = db_query("UPDATE {$pref}sales_orders SET total=".$cart->get_trans_total());
 143                         unset($cart->line_items);
 144                 }
 145                 unset($cart);
 146                 $cart = new purch_order();
 147                 $sql = "SELECT order_no FROM {$pref}purch_orders";
 148                 $orders = db_query($sql);
 149                 if (!$orders)
 150                          return false;
 151                 while ($order_no = db_fetch($orders)) {
 152                         read_po($order_no[0], $cart);
 153                         $result = db_query("UPDATE {$pref}purch_orders SET total=".$cart->get_trans_total());
 154                         unset($cart->line_items);
 155                 }
 156                 return true;
 157         }
 158
 159         //------------------------------------------------------------------------------
 160         //      Retreive parent document number(s) for given transaction
 161         //
 162         function get_parent_trans_2_2($trans_type, $trans_no) {
 163
 164                 $sql = 'SELECT trans_link FROM
 165                                 '.TB_PREF.'debtor_trans WHERE trans_no='.db_escape($trans_no)
 166                                 .' AND type='.db_escape($trans_type).' AND trans_link!=0';
 167
 168                 $result = db_query($sql, 'Parent document numbers cannot be retrieved');
 169
 170                 if (db_num_rows($result)) {
 171                         $link = db_fetch($result);
 172                         return array($link['trans_link']);
 173                 }
 174                 if ($trans_type!=ST_SALESINVOICE) return 0;     // this is credit note with no parent invoice
 175                 // invoice: find batch invoice parent trans.
 176                 $sql = 'SELECT trans_no FROM
 177                                 '.TB_PREF.'debtor_trans WHERE
 178                                 (trans_link='.db_escape($trans_no).' AND type='. get_parent_type($trans_type) .')';
 179
 180                 $result = db_query($sql, 'Delivery links cannot be retrieved');
 181
 182                 $delivery = array();
 183                 if(db_num_rows($result)>0) {
 184                         while($link = db_fetch($result)) {
 185                                 $delivery[] = $link['trans_no'];
 186                         }
 187                 }
 188                 return count($delivery) ? $delivery : 0;
 189         }
 190
 191         /*
 192                 Reorganizing document relations. Due to the design issue in pre 2.3 db structure
 193                 there can be sales documents with lines not properly linked to parents. This rare
 194                 cases will be described in error log.
 195         */
 196         function update_line_relations($pref)
 197         {
 198                 global $path_to_root, $systypes_array;
 199
 200                 require_once("$path_to_root/includes/sysnames.inc");
 201
 202                 $sql =  "SELECT d.type, trans_no, order_ FROM {$pref}debtor_trans d
 203                         LEFT JOIN {$pref}voided v ON d.type=v.type AND d.trans_no=v.id
 204                                 WHERE ISNULL(v.type) AND
 205                                 (d.type=".ST_CUSTDELIVERY
 206                                 ." OR d.type=".ST_SALESINVOICE
 207                                 ." OR d.type=".ST_CUSTCREDIT.")";
 208                 $result = db_query($sql);
 209                 if (!$result)
 210                         return false;
 211
 212                 while ($trans = db_fetch($result)) {
 213                         $type = $trans['type'];
 214                         $trans_no = $trans['trans_no'];
 215                         $invalid = 0;
 216                         $msg ='';
 217
 218                         $lines = get_customer_trans_details($type, $trans_no);
 219                         $n = db_num_rows($lines);
 220
 221                         if ($type==ST_CUSTDELIVERY)
 222                                 $src_lines = get_sales_order_details($trans['order_'], ST_SALESORDER);
 223                         else
 224                                 $src_lines =  get_customer_trans_details(get_parent_type($type),
 225                                         $this->get_parent_trans_2_2($type, $trans_no));
 226
 227                         $src_n = db_num_rows($src_lines);
 228
 229                         if (($type == ST_CUSTCREDIT) && ($src_n == 0))
 230                                  continue;  // free credit note has no src lines
 231
 232                         $max = $type == ST_CUSTDELIVERY ? $n : max($src_n, $n);
 233
 234                         for($i = 0, $j=0; $i < $max; $i++) {
 235                                 if (!($doc_line = @db_fetch($lines)))
 236                                         break;
 237
 238                                 if(!($src_line = @db_fetch($src_lines)))
 239                                         break;
 240
 241                                 if ($type == ST_CUSTDELIVERY)
 242                                         $src_line['stock_id'] = $src_line['stk_code']; // SO details has another field name
 243
 244                                 if ($src_line['stock_id'] == $doc_line['stock_id']
 245                                         && ($src_line['quantity'] >= $doc_line['quantity'])) {
 246
 247                                         $sql = "UPDATE {$pref}debtor_trans_details SET src_id = {$src_line['id']}
 248                                                 WHERE id = {$doc_line['id']}";
 249                                         if (!db_query($sql))
 250                                                 return false;
 251                                         $j++;
 252                                 }
 253                         }
 254                         if ($j != $n) {
 255                                 error_log("Line level relations error for ".$systypes_array[$type]." #$trans_no.");
 256                         }
 257                 }
 258         return true;
 259         }
 260 }
 261
 262 $install = new fa2_3;
 263
 264 ?>