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