X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=sales%2Fincludes%2Fsales_db.inc;h=88727e80982a3fd0aa088bbfdc8ec2daf18c102e;hb=9478a0cdeaf9201d2f3947e11e58d90354050025;hp=55d6138516dfee8b84464be2176db5d81f8453f1;hpb=e871e7b7f097ec86efa815e2005786710afb79e4;p=fa-stable.git diff --git a/sales/includes/sales_db.inc b/sales/includes/sales_db.inc index 55d61385..88727e80 100644 --- a/sales/includes/sales_db.inc +++ b/sales/includes/sales_db.inc @@ -1,11 +1,24 @@ . +***********************************************************************/ include_once($path_to_root . "/includes/banking.inc"); include_once($path_to_root . "/includes/db/inventory_db.inc"); include_once($path_to_root . "/sales/includes/db/sales_order_db.inc"); include_once($path_to_root . "/sales/includes/db/sales_credit_db.inc"); include_once($path_to_root . "/sales/includes/db/sales_invoice_db.inc"); include_once($path_to_root . "/sales/includes/db/sales_delivery_db.inc"); +include_once($path_to_root . "/sales/includes/db/sales_types_db.inc"); +include_once($path_to_root . "/sales/includes/db/sales_points_db.inc"); +include_once($path_to_root . "/sales/includes/db/sales_groups_db.inc"); +include_once($path_to_root . "/sales/includes/db/recurrent_invoices_db.inc"); include_once($path_to_root . "/sales/includes/db/custalloc_db.inc"); include_once($path_to_root . "/sales/includes/db/cust_trans_db.inc"); include_once($path_to_root . "/sales/includes/db/cust_trans_details_db.inc"); @@ -34,39 +47,135 @@ function add_stock_move_customer($type, $stock_id, $trans_id, $location, $date_, // $amount is in CUSTOMER'S currency function add_gl_trans_customer($type, $type_no, $date_, $account, $dimension, $dimension2, - $amount, $customer_id, $err_msg="") + $amount, $customer_id, $err_msg="", $rate=0) { if ($err_msg == "") $err_msg = "The customer GL transaction could not be inserted"; return add_gl_trans($type, $type_no, $date_, $account, $dimension, $dimension2, "", $amount, get_customer_currency($customer_id), - payment_person_types::customer(), $customer_id, $err_msg); + PT_CUSTOMER, $customer_id, $err_msg, $rate); } //---------------------------------------------------------------------------------------- -function get_price ($stock_id, $currency, $sales_type_id) +function get_calculated_price($stock_id, $add_pct) +{ + $avg = get_standard_cost($stock_id); + if ($avg == 0) + return 0; + return round2($avg * (1 + $add_pct / 100), user_price_dec()); +} + +function round_to_nearest($price, $round_to) +{ + if ($price == 0) + return 0; + $pow = pow(10, user_price_dec()); + if ($pow >= $round_to) + $mod = ($pow % $round_to); + else + $mod = ($round_to % $pow); + if ($mod != 0) + $price = ceil($price) - ($pow - $round_to) / $pow; + else + $price = ceil($price * ($pow / $round_to)) / ($pow / $round_to); + return $price; + +} + +function get_price ($stock_id, $currency, $sales_type_id, $factor=null, $date=null) { - $sql = "SELECT ".TB_PREF."prices.price + if ($date == null) + $date = new_doc_date(); + + if ($factor === null) + { + $myrow = get_sales_type($sales_type_id); + $factor = $myrow['factor']; + } + + $add_pct = get_company_pref('add_pct'); + $base_id = get_base_sales_type(); + $home_curr = get_company_currency(); + // AND (sales_type_id = $sales_type_id OR sales_type_id = $base_id) + $sql = "SELECT price, curr_abrev, sales_type_id FROM ".TB_PREF."prices - WHERE ".TB_PREF."prices.stock_id = '" . $stock_id . "' - AND ".TB_PREF."prices.sales_type_id = " . $sales_type_id . " - AND ".TB_PREF."prices.curr_abrev = '$currency'"; + WHERE stock_id = ".db_escape($stock_id)." + AND (curr_abrev = ".db_escape($currency)." OR curr_abrev = ".db_escape($home_curr).")"; $result = db_query($sql, "There was a problem retrieving the pricing information for the part $stock_id for customer"); - - if (db_num_rows($result) != 0) + $num_rows = db_num_rows($result); + $rate = round2(get_exchange_rate_from_home_currency($currency, $date), + user_exrate_dec()); + $round_to = get_company_pref('round_to'); + $prices = array(); + while($myrow = db_fetch($result)) { - /*There is a price from one of the above so return that */ - $myrow = db_fetch_row($result); - return $myrow[0]; + $prices[$myrow['sales_type_id']][$myrow['curr_abrev']] = $myrow['price']; } - else + $price = false; + if (isset($prices[$sales_type_id][$home_curr])) { - return 0; + $price = $prices[$sales_type_id][$home_curr] / $rate; } + elseif (isset($prices[$base_id][$currency])) + { + $price = $prices[$base_id][$currency] * $factor; + } + elseif (isset($prices[$base_id][$home_curr])) + { + $price = $prices[$base_id][$home_curr] * $factor / $rate; + } + elseif ($num_rows == 0 && $add_pct != -1) + { + $price = get_calculated_price($stock_id, $add_pct); + if ($currency != $home_curr) + $price /= $rate; + if ($factor != 0) + $price *= $factor; + } + if ($price === false) + return 0; + elseif ($round_to != 1) + return round_to_nearest($price, $round_to); + else + return round2($price, user_price_dec()); +} +//---------------------------------------------------------------------------------------- +// +// Get price for given item or kit. +// When $std==true price is calculated as a sum of all included stock items, +// otherwise all prices set for kits and items are accepted. +// +function get_kit_price($item_code, $currency, $sales_type_id, $factor=null, + $date=null, $std = false) +{ + $kit_price = 0.00; + if (!$std) { + $kit_price = get_price( $item_code, $currency, $sales_type_id, + $factor, $date); + if ($kit_price !== false) { + return $kit_price; + } + } + // no price for kit found, get total value of all items + $kit = get_item_kit($item_code); + + while($item = db_fetch($kit)) { + if ($item['item_code'] != $item['stock_id']) { + // foreign/kit code + $kit_price += $item['quantity'] * get_kit_price( $item['stock_id'], + $currency, $sales_type_id, $factor, $date, $std); + + } else { + // stock item + $kit_price += $item['quantity'] * get_price( $item['stock_id'], + $currency, $sales_type_id, $factor, $date); + } + } + return $kit_price; } //----------------------------------------------------------------------------- @@ -77,15 +186,16 @@ function set_document_parent($cart) if (count($cart->src_docs) == 1) { - // if this child document has only one parent - update child link - $del_no = key($cart->src_docs); + // if this child document has only one parent - update child link + $src = array_keys($cart->src_docs); + $del_no = reset($src); - $sql = 'UPDATE '.TB_PREF.'debtor_trans SET trans_link = ' . $del_no . - ' WHERE type='.$cart->trans_type.' AND trans_no='. $inv_no ; - db_query($sql, 'Child document link cannot be updated'); + $sql = 'UPDATE '.TB_PREF.'debtor_trans SET trans_link = ' . $del_no . + ' WHERE type='.db_escape($cart->trans_type).' AND trans_no='. $inv_no ; + db_query($sql, 'Child document link cannot be updated'); } - if ($cart->trans_type != 10) + if ($cart->trans_type != ST_SALESINVOICE) return 0; // the rest is batch invoice specific @@ -113,7 +223,7 @@ function set_document_parent($cart) //-------------------------------------------------------------------------------------------------- function get_parent_type($type) { - $parent_types = array( 11=>10, 10=>13, 13=>30 ); + $parent_types = array( ST_CUSTCREDIT => ST_SALESINVOICE, ST_SALESINVOICE => ST_CUSTDELIVERY, ST_CUSTDELIVERY => ST_SALESORDER ); return isset($parent_types[$type]) ? $parent_types[$type] : 0; } @@ -122,18 +232,18 @@ function update_parent_line($doc_type, $line_id, $qty_dispatched) { $doc_type = get_parent_type($doc_type); - //echo "update line: $line_id, $doc_type, $qty_dispatch"; +// echo "update line: $line_id, $doc_type, $qty_dispatched"; if ($doc_type==0) return false; else { - if ($doc_type==30) + if ($doc_type==ST_SALESORDER) $sql = "UPDATE ".TB_PREF."sales_order_details SET qty_sent = qty_sent + $qty_dispatched - WHERE id=$line_id"; + WHERE id=".db_escape($line_id); else $sql = "UPDATE ".TB_PREF."debtor_trans_details SET qty_done = qty_done + $qty_dispatched - WHERE id=$line_id"; + WHERE id=".db_escape($line_id); } db_query($sql, "The parent document detail record could not be updated"); return true; @@ -146,7 +256,7 @@ function get_location(&$cart) { $sql = "SELECT ".TB_PREF."locations.* FROM ".TB_PREF."stock_moves," .TB_PREF."locations". - " WHERE type=".$cart->trans_type. + " WHERE type=".db_escape($cart->trans_type). " AND trans_no=".key($cart->trans_no). " AND qty!=0 ". " AND ".TB_PREF."locations.loc_code=".TB_PREF."stock_moves.loc_code"; @@ -179,7 +289,7 @@ function read_sales_trans($doc_type, $trans_no, &$cart) else $cart->trans_no = array($trans_no[0]=>$myrow["version"]); - $cart->set_sales_type($myrow["tpe"], $myrow["sales_type"], $myrow["tax_included"]); + $cart->set_sales_type($myrow["tpe"], $myrow["sales_type"], $myrow["tax_included"],0); $cart->set_customer($myrow["debtor_no"], $myrow["DebtorName"], $myrow["curr_code"], $myrow["discount"]); @@ -192,18 +302,11 @@ function read_sales_trans($doc_type, $trans_no, &$cart) $cart->trans_link = $myrow["trans_link"]; $cart->due_date = sql2date($myrow["due_date"]); $cart->document_date = sql2date($myrow["tran_date"]); - + $cart->dimension_id = $myrow['dimension_id']; // added 2.1 Joe Hunt 2008-11-12 + $cart->dimension2_id = $myrow['dimension2_id']; $cart->Comments = ''; foreach ( $trans_no as $trans ) { - $coms = get_comments($doc_type,$trans); - while($row=db_fetch($coms)) { - $text = $row['memo_']; - if ($text!='') { - if ($cart->Comments!='') - $cart->Comments .= "\n"; - $cart->Comments .= $text; - } - } + $cart->Comments .= get_comments_string($doc_type,$trans); } // FIX this should be calculated sum() for multiply parents @@ -221,7 +324,8 @@ function read_sales_trans($doc_type, $trans_no, &$cart) $result = get_customer_trans_details($doc_type,$trans_no); if (db_num_rows($result) > 0) { for($line_no=0; $myrow = db_fetch($result); $line_no++) { - $cart->add_to_cart($line_no,$myrow["stock_id"],$myrow["quantity"], + $cart->line_items[$line_no] = new line_details( + $myrow["stock_id"],$myrow["quantity"], $myrow["unit_price"], $myrow["discount_percent"], $myrow["qty_done"], $myrow["standard_cost"], $myrow["StockDescription"],$myrow["id"], $myrow["debtor_trans_no"]);