sales/includes/sales_db.inc

   1 <?php
   2
   3 include_once($path_to_root . "/includes/banking.inc");
   4 include_once($path_to_root . "/includes/db/inventory_db.inc");
   5 include_once($path_to_root . "/sales/includes/db/sales_order_db.inc");
   6 include_once($path_to_root . "/sales/includes/db/sales_credit_db.inc");
   7 include_once($path_to_root . "/sales/includes/db/sales_invoice_db.inc");
   8 include_once($path_to_root . "/sales/includes/db/sales_delivery_db.inc");
   9 include_once($path_to_root . "/sales/includes/db/custalloc_db.inc");
  10 include_once($path_to_root . "/sales/includes/db/cust_trans_db.inc");
  11 include_once($path_to_root . "/sales/includes/db/cust_trans_details_db.inc");
  12 include_once($path_to_root . "/sales/includes/db/payment_db.inc");
  13 include_once($path_to_root . "/sales/includes/db/branches_db.inc");
  14 include_once($path_to_root . "/sales/includes/db/customers_db.inc");
  15
  16 //----------------------------------------------------------------------------------------
  17 // $price in customer's currency
  18 // $quantity is used as is (if it's neg it's neg, if it's pos it's pos)
  19 // $std_cost is in home currency
  20 // $show_or_hide 1 show this item in invoice/credit views, 0 to hide it (used for write-off items)
  21 // $type is 10 (invoice) or 11 (credit)
  22
  23 function add_stock_move_customer($type, $stock_id, $trans_id, $location, $date_, $reference,
  24         $quantity, $std_cost, $show_or_hide=1, $price=0, $discount_percent=0)
  25 {
  26         return add_stock_move($type, $stock_id, $trans_id, $location, $date_, $reference,
  27                 $quantity, $std_cost, 0, $show_or_hide, $price, $discount_percent,
  28                 "The customer stock movement record cannot be inserted");
  29 }
  30
  31 //----------------------------------------------------------------------------------------
  32 // add a debtor-related gl transaction
  33 // $date_ is display date (non-sql)
  34 // $amount is in CUSTOMER'S currency
  35
  36 function add_gl_trans_customer($type, $type_no, $date_, $account, $dimension, $dimension2,
  37         $amount, $customer_id, $err_msg="")
  38 {
  39         if ($err_msg == "")
  40                 $err_msg = "The customer GL transaction could not be inserted";
  41
  42         return add_gl_trans($type, $type_no, $date_, $account, $dimension, $dimension2, "", $amount,
  43                 get_customer_currency($customer_id),
  44                 payment_person_types::customer(), $customer_id, $err_msg);
  45 }
  46
  47 //----------------------------------------------------------------------------------------
  48
  49 function get_price ($stock_id, $currency, $sales_type_id, $factor, $date=null)
  50 {
  51         if ($date == null)
  52             $date = Today();
  53
  54         $sql = "SELECT ".TB_PREF."prices.price
  55                 FROM ".TB_PREF."prices
  56                 WHERE ".TB_PREF."prices.stock_id = '" . $stock_id . "' "
  57                 ." AND ".TB_PREF."prices.sales_type_id = " . $sales_type_id
  58                 ." AND ".TB_PREF."prices.curr_abrev = '$currency'";
  59
  60         $msg = "There was a problem retrieving the pricing information for the part $stock_id for customer";
  61         $result = db_query($sql, $msg);
  62
  63         if (db_num_rows($result) != 0)
  64         {
  65                 $myrow = db_fetch_row($result);
  66                 return $myrow[0];
  67         }
  68         if ($factor == 0) return 0; // auto price calculations off
  69
  70         $base_id = get_base_sales_type();
  71         $home_curr = get_company_currency();
  72
  73     // get all prices which we can use to guess the price.
  74     // alternative is make up to 2 additional sql queries
  75         $sql = "SELECT ".TB_PREF."prices.price,".TB_PREF."prices.curr_abrev,
  76                 ".TB_PREF."prices.sales_type_id
  77                 FROM ".TB_PREF."prices
  78                 WHERE ".TB_PREF."prices.stock_id = '" . $stock_id . "' "
  79                 ." AND (".TB_PREF."prices.sales_type_id = " . $sales_type_id
  80                 ." OR ".TB_PREF."prices.sales_type_id = " . $base_id.")"
  81                 ." AND (".TB_PREF."prices.curr_abrev = '$currency'"
  82                 ." OR ".TB_PREF."prices.curr_abrev = '$home_curr')";
  83
  84         $result = db_query($sql, $msg);
  85
  86         $prices = array();
  87         while($myrow = db_fetch($result))
  88         {
  89             $prices[$myrow['sales_type_id']][$myrow['curr_abrev']] = $myrow['price'];
  90         }
  91
  92         $rate = round(get_exchange_rate_from_home_currency($currency, $date),
  93             user_exrate_dec());
  94         $price = 0.00;
  95
  96         if (isset($prices[$sales_type_id][$home_curr]))
  97         {
  98             $price = $prices[$sales_type_id][$home_curr] / $rate;
  99         }
 100         if (isset($prices[$base_id][$currency]))
 101         {
 102             $price =$prices[$base_id][$currency] * $factor;
 103         }
 104         if (isset($prices[$base_id][$home_curr]))
 105         {
 106             $price =$prices[$base_id][$home_curr] * $factor / $rate;
 107         }
 108
 109         return round($price, user_price_dec());
 110 }
 111
 112 //-----------------------------------------------------------------------------
 113
 114 function set_document_parent($cart)
 115 {
 116         $inv_no = key($cart->trans_no);
 117
 118         if (count($cart->src_docs) == 1) {
 119
 120         // if this child document has only one parent - update child link
 121         $del_no = key($cart->src_docs);
 122
 123         $sql = 'UPDATE '.TB_PREF.'debtor_trans SET trans_link = ' . $del_no .
 124                 ' WHERE type='.$cart->trans_type.' AND trans_no='. $inv_no ;
 125         db_query($sql, 'Child document link cannot be updated');
 126
 127         }
 128         if ($cart->trans_type != 10)
 129                 return 0;
 130
 131         // the rest is batch invoice specific
 132
 133         foreach ($cart->line_items as $line) {
 134                 if ($line->quantity != $line->qty_dispatched) {
 135                         return 1;       // this is partial invoice
 136                 }
 137         }
 138
 139         $sql = 'UPDATE '.TB_PREF.'debtor_trans SET trans_link = ' . $inv_no .
 140         ' WHERE type='.get_parent_type($cart->trans_type).' AND (';
 141
 142         $deliveries = array_keys($cart->src_docs);
 143
 144         foreach ($deliveries as $key=>$del)
 145                 $deliveries[$key] = 'trans_no='.$del;
 146
 147         $sql .= implode(' OR ', $deliveries) . ')';
 148         db_query($sql, 'Delivery links cannot be updated');
 149
 150         return 0; // batch or complete invoice
 151 }
 152
 153 //--------------------------------------------------------------------------------------------------
 154 function get_parent_type($type)
 155 {
 156         $parent_types = array( 11=>10, 10=>13, 13=>30 );
 157         return isset($parent_types[$type]) ?  $parent_types[$type] : 0;
 158 }
 159
 160 //--------------------------------------------------------------------------------------------------
 161 function update_parent_line($doc_type, $line_id, $qty_dispatched)
 162 {
 163         $doc_type = get_parent_type($doc_type);
 164
 165         //echo "update line: $line_id, $doc_type, $qty_dispatch";
 166         if ($doc_type==0)
 167                 return false;
 168         else {
 169                 if ($doc_type==30)
 170                         $sql = "UPDATE ".TB_PREF."sales_order_details
 171                                 SET qty_sent = qty_sent + $qty_dispatched
 172                                 WHERE id=$line_id";
 173                 else
 174                         $sql = "UPDATE ".TB_PREF."debtor_trans_details
 175                                 SET qty_done = qty_done + $qty_dispatched
 176                                 WHERE id=$line_id";
 177         }
 178         db_query($sql, "The parent document detail record could not be updated");
 179         return true;
 180 }
 181
 182 //--------------------------------------------------------------------------------------------------
 183 // find inventory location for given transaction
 184 //
 185 function get_location(&$cart)
 186 {
 187         $sql = "SELECT ".TB_PREF."locations.* FROM ".TB_PREF."stock_moves,"
 188                 .TB_PREF."locations".
 189                 " WHERE type=".$cart->trans_type.
 190                 " AND trans_no=".key($cart->trans_no).
 191                 " AND qty!=0 ".
 192                 " AND ".TB_PREF."locations.loc_code=".TB_PREF."stock_moves.loc_code";
 193         $result = db_query($sql, 'Retreiving inventory location');
 194
 195
 196         if (db_num_rows($result)) {
 197                 return db_fetch($result);
 198         }
 199         return null;
 200 }
 201 //--------------------------------------------------------------------------------------------------
 202 // Generic read debtor transaction into cart
 203 //
 204 //      $trans_no - array of trans nums; special case trans_no==0 - new doc
 205 //
 206 function read_sales_trans($doc_type, $trans_no, &$cart)
 207 {
 208         if (!is_array($trans_no) && $trans_no)
 209                         $trans_no = array($trans_no);
 210
 211         $cart->trans_type = $doc_type;
 212         if (!$trans_no) { // new document
 213                 $cart->trans_no = $trans_no;
 214         } else {
 215                 // read header data from first document
 216                 $myrow = get_customer_trans($trans_no[0],$doc_type);
 217                 if (count($trans_no)>1)
 218                         $cart->trans_no = get_customer_trans_version($doc_type, $trans_no);
 219                 else
 220                         $cart->trans_no = array($trans_no[0]=>$myrow["version"]);
 221
 222                 $cart->set_sales_type($myrow["tpe"], $myrow["sales_type"], $myrow["tax_included"],0);
 223
 224                 $cart->set_customer($myrow["debtor_no"], $myrow["DebtorName"],
 225                         $myrow["curr_code"], $myrow["discount"]);
 226
 227                 $cart->set_branch($myrow["branch_code"], $myrow["tax_group_id"],
 228                         $myrow["tax_group_name"],       $myrow["phone"], $myrow["email"]);
 229
 230                 $cart->reference = $myrow["reference"];
 231                 $cart->order_no = $myrow["order_"];
 232                 $cart->trans_link = $myrow["trans_link"];
 233                 $cart->due_date = sql2date($myrow["due_date"]);
 234                 $cart->document_date = sql2date($myrow["tran_date"]);
 235
 236                 $cart->Comments = '';
 237                 foreach ( $trans_no as $trans ) {
 238                         $coms =  get_comments($doc_type,$trans);
 239                         while($row=db_fetch($coms)) {
 240                                 $text = $row['memo_'];
 241                                 if ($text!='') {
 242                                         if ($cart->Comments!='')
 243                                                 $cart->Comments .= "\n";
 244                                         $cart->Comments .= $text;
 245                                 }
 246                         }
 247                 }
 248
 249                 // FIX this should be calculated sum() for multiply parents
 250
 251                 $cart->set_delivery($myrow["ship_via"], $myrow["br_name"],
 252                 $myrow["br_address"], $myrow["ov_freight"]);
 253
 254                 $location = 0;
 255                 $myrow = get_location($cart); // find location from movement
 256
 257                 if($myrow!=null) {
 258                         $cart->set_location($myrow['loc_code'], $myrow['location_name']);
 259                 }
 260
 261                 $result = get_customer_trans_details($doc_type,$trans_no);
 262                 if (db_num_rows($result) > 0) {
 263                         for($line_no=0; $myrow = db_fetch($result); $line_no++) {
 264                                 $cart->add_to_cart($line_no,$myrow["stock_id"],$myrow["quantity"],
 265                                         $myrow["unit_price"], $myrow["discount_percent"],
 266                                         $myrow["qty_done"], $myrow["standard_cost"],
 267                                         $myrow["StockDescription"],$myrow["id"], $myrow["debtor_trans_no"]);
 268                         }
 269                 }
 270         } // !newdoc
 271
 272         return true;
 273 }
 274 //----------------------------------------------------------------------------------------
 275 ?>