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, $debtor_no)
  50 {
  51         $sql = "SELECT ".TB_PREF."prices.price
  52                 FROM ".TB_PREF."prices, ".TB_PREF."debtors_master
  53                 WHERE ".TB_PREF."debtors_master.sales_type=".TB_PREF."prices.sales_type_id
  54                         AND ".TB_PREF."debtors_master.debtor_no='" . $debtor_no . "'
  55                         AND ".TB_PREF."prices.stock_id = '" . $stock_id . "'
  56                         AND ".TB_PREF."prices.curr_abrev = ".TB_PREF."debtors_master.curr_code";
  57
  58         $result = db_query($sql, "There was a problem retrieving the pricing information for the part $stock_id for customer");
  59
  60         if (db_num_rows($result) != 0)
  61         {
  62                 /*There is a price from one of the above so return that */
  63                 $myrow = db_fetch_row($result);
  64                 return $myrow[0];
  65         }
  66         else
  67         {
  68                 return 0;
  69         }
  70
  71 }
  72
  73 //-----------------------------------------------------------------------------
  74
  75 function set_document_parent($cart)
  76 {
  77         $inv_no = key($cart->trans_no);
  78
  79         if (count($cart->src_docs) == 1) {
  80
  81         // if this child document has only one parent - update child link
  82         $del_no = key($cart->src_docs);
  83
  84         $sql = 'UPDATE '.TB_PREF.'debtor_trans SET trans_link = ' . $del_no .
  85                 ' WHERE type='.$cart->trans_type.' AND trans_no='. $inv_no ;
  86         db_query($sql, 'Child document link cannot be updated');
  87
  88         }
  89         if ($cart->trans_type != 10)
  90                 return 0;
  91
  92         // the rest is batch invoice specific
  93
  94         foreach ($cart->line_items as $line) {
  95                 if ($line->quantity != $line->qty_dispatched) {
  96                         return 1;       // this is partial invoice
  97                 }
  98         }
  99
 100         $sql = 'UPDATE '.TB_PREF.'debtor_trans SET trans_link = ' . $inv_no .
 101         ' WHERE type='.get_parent_type($cart->trans_type).' AND (';
 102
 103         $deliveries = array_keys($cart->src_docs);
 104
 105         foreach ($deliveries as $key=>$del)
 106                 $deliveries[$key] = 'trans_no='.$del;
 107
 108         $sql .= implode(' OR ', $deliveries) . ')';
 109         db_query($sql, 'Delivery links cannot be updated');
 110
 111         return 0; // batch or complete invoice
 112 }
 113
 114 //--------------------------------------------------------------------------------------------------
 115 function get_parent_type($type)
 116 {
 117         $parent_types = array( 11=>10, 10=>13, 13=>30 );
 118         return isset($parent_types[$type]) ?  $parent_types[$type] : 0;
 119 }
 120
 121 //--------------------------------------------------------------------------------------------------
 122 function update_parent_line($doc_type, $line_id, $qty_dispatched)
 123 {
 124         $doc_type = get_parent_type($doc_type);
 125
 126         //echo "update line: $line_id, $doc_type, $qty_dispatch";
 127         if ($doc_type==0)
 128                 return false;
 129         else {
 130                 if ($doc_type==30)
 131                         $sql = "UPDATE ".TB_PREF."sales_order_details
 132                                 SET qty_sent = qty_sent + $qty_dispatched
 133                                 WHERE id=$line_id";
 134                 else
 135                         $sql = "UPDATE ".TB_PREF."debtor_trans_details
 136                                 SET qty_done = qty_done + $qty_dispatched
 137                                 WHERE id=$line_id";
 138         }
 139         db_query($sql, "The parent document detail record could not be updated");
 140         return true;
 141 }
 142
 143 //--------------------------------------------------------------------------------------------------
 144 // find inventory location for given transaction
 145 //
 146 function get_location(&$cart)
 147 {
 148         $sql = "SELECT ".TB_PREF."locations.* FROM ".TB_PREF."stock_moves,"
 149                 .TB_PREF."locations".
 150                 " WHERE type=".$cart->trans_type.
 151                 " AND trans_no=".key($cart->trans_no).
 152                 " AND qty!=0 ".
 153                 " AND ".TB_PREF."locations.loc_code=".TB_PREF."stock_moves.loc_code";
 154         $result = db_query($sql, 'Retreiving inventory location');
 155
 156
 157         if (db_num_rows($result)) {
 158                 return db_fetch($result);
 159         }
 160         return null;
 161 }
 162 //--------------------------------------------------------------------------------------------------
 163 // Generic read debtor transaction into cart
 164 //
 165 //      $trans_no - array of trans nums; special case trans_no==0 - new doc
 166 //
 167 function read_sales_trans($doc_type, $trans_no, &$cart)
 168 {
 169         if (!is_array($trans_no) && $trans_no)
 170                         $trans_no = array($trans_no);
 171
 172         $cart->trans_type = $doc_type;
 173         if (!$trans_no) { // new document
 174                 $cart->trans_no = $trans_no;
 175         } else {
 176                 // read header data from first document
 177                 $myrow = get_customer_trans($trans_no[0],$doc_type);
 178                 if (count($trans_no)>1)
 179                         $cart->trans_no = get_customer_trans_version($doc_type, $trans_no);
 180                 else
 181                         $cart->trans_no = array($trans_no[0]=>$myrow["version"]);
 182
 183                 $cart->set_sales_type($myrow["tpe"], $myrow["sales_type"], $myrow["tax_included"]);
 184
 185                 $cart->set_customer($myrow["debtor_no"], $myrow["DebtorName"],
 186                         $myrow["curr_code"], $myrow["discount"]);
 187
 188                 $cart->set_branch($myrow["branch_code"], $myrow["tax_group_id"],
 189                         $myrow["tax_group_name"],       $myrow["phone"], $myrow["email"]);
 190
 191                 $cart->reference = $myrow["reference"];
 192                 $cart->order_no = $myrow["order_"];
 193                 $cart->trans_link = $myrow["trans_link"];
 194                 $cart->due_date = sql2date($myrow["due_date"]);
 195                 $cart->document_date = sql2date($myrow["tran_date"]);
 196
 197                 $cart->Comments = '';
 198                 foreach ( $trans_no as $trans ) {
 199                         $coms =  get_comments($doc_type,$trans);
 200                         while($row=db_fetch($coms)) {
 201                                 $text = $row['memo_'];
 202                                 if ($text!='') {
 203                                         if ($cart->Comments!='')
 204                                                 $cart->Comments .= "\n";
 205                                         $cart->Comments .= $text;
 206                                 }
 207                         }
 208                 }
 209
 210                 // FIX this should be calculated sum() for multiply parents
 211
 212                 $cart->set_delivery($myrow["ship_via"], $myrow["br_name"],
 213                 $myrow["br_address"], $myrow["ov_freight"]);
 214
 215                 $location = 0;
 216                 $myrow = get_location($cart); // find location from movement
 217
 218                 if($myrow!=null) {
 219                         $cart->set_location($myrow['loc_code'], $myrow['location_name']);
 220                 }
 221
 222                 $result = get_customer_trans_details($doc_type,$trans_no);
 223                 if (db_num_rows($result) > 0) {
 224                         for($line_no=0; $myrow = db_fetch($result); $line_no++) {
 225                                 $cart->add_to_cart($line_no,$myrow["stock_id"],$myrow["quantity"],
 226                                         $myrow["unit_price"], $myrow["discount_percent"],
 227                                         $myrow["qty_done"], $myrow["standard_cost"],
 228                                         $myrow["StockDescription"],$myrow["id"], $myrow["debtor_trans_no"]);
 229                         }
 230                 }
 231         } // !newdoc
 232
 233         return true;
 234 }
 235 //----------------------------------------------------------------------------------------
 236 ?>