X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=sales%2Fincludes%2Fdb%2Fsales_order_db.inc;h=9d43fb3af94b8427c5cc9cb3d2da0f8e9ba81beb;hb=d907208297556310cc7c51fcb9086869c880ab2d;hp=ff61aaa5eef5b8c091a48ef38a43c2741131fa52;hpb=c8145b03ed9754bd9552ccdac38b41b73550a69f;p=fa-stable.git diff --git a/sales/includes/db/sales_order_db.inc b/sales/includes/db/sales_order_db.inc index ff61aaa5..9d43fb3a 100644 --- a/sales/includes/db/sales_order_db.inc +++ b/sales/includes/db/sales_order_db.inc @@ -72,7 +72,7 @@ function add_sales_order(&$order) } /* inserted line items into sales order details */ add_audit_trail($order->trans_type, $order_no, $order->document_date); - $Refs->save($order->trans_type, $order_no, $order->reference); + $Refs->save($order->trans_type, $order_no, $order->reference, null, $order->fixed_asset); hook_db_postwrite($order, $order->trans_type); commit_transaction(); @@ -89,6 +89,8 @@ function delete_sales_order($order_no, $trans_type) begin_transaction(); hook_db_prevoid($trans_type, $order_no); + $order = get_sales_order_header($order_no, $trans_type); + $sql = "DELETE FROM ".TB_PREF."sales_orders WHERE order_no=" . db_escape($order_no) . " AND trans_type=".db_escape($trans_type); @@ -98,9 +100,7 @@ function delete_sales_order($order_no, $trans_type) .db_escape($order_no) . " AND trans_type=".db_escape($trans_type); db_query($sql, "order Detail Delete"); - delete_reference($trans_type, $order_no); - - add_audit_trail($trans_type, $order_no, Today(), _("Deleted.")); + add_audit_trail($trans_type, $order_no, sql2date($order['ord_date']), _("Deleted.")); commit_transaction(); } @@ -111,7 +111,7 @@ function update_sales_order_version($order) { foreach ($order as $so_num => $so_ver) { $sql= 'UPDATE '.TB_PREF.'sales_orders SET version=version+1 WHERE order_no='. db_escape($so_num). - ' AND version='.$so_ver . " AND trans_type=30"; + ' AND version='.$so_ver . " AND trans_type=".ST_SALESORDER; db_query($sql, 'Concurrent editing conflict while sales order update'); } } @@ -132,7 +132,7 @@ function update_sales_order($order) hook_db_prewrite($order, $order->trans_type); if ($order->trans_type == ST_SALESORDER) - $allocs = get_payments_for($order_no, ST_SALESORDER); + $allocs = get_payments_for($order_no, $order->trans_type, $order->customer_id); $sql = "UPDATE ".TB_PREF."sales_orders SET type =".db_escape($order->so_type)." , debtor_no = " . db_escape($order->customer_id) . ", @@ -209,9 +209,9 @@ function update_sales_order($order) } /* inserted line items into sales order details */ if ($order->trans_type == ST_SALESORDER) - reallocate_payments($order_no, ST_SALESORDER, $ord_date, $total, $allocs); + reallocate_payments($order_no, ST_SALESORDER, $ord_date, $total, $allocs, $order->customer_id); add_audit_trail($order->trans_type, $order_no, $order->document_date, _("Updated.")); - $Refs->save($order->trans_type, $order_no, $order->reference); + $Refs->save($order->trans_type, $order_no, $order->reference, null, $order->fixed_asset); hook_db_postwrite($order, $order->trans_type); commit_transaction(); @@ -223,24 +223,24 @@ function update_sales_order($order) function get_sales_order_header($order_no, $trans_type) { - $sql = "SELECT sorder.*, " - ."cust.name, " - ."cust.curr_code, " - ."cust.address, " - ."loc.location_name, " - ."cust.discount, " - ."stype.sales_type, " - ."stype.id AS sales_type_id, " - ."stype.tax_included, " - ."stype.factor, " - ."ship.shipper_name, " - ."tax_group.name AS tax_group_name , " - ."tax_group.id AS tax_group_id, " - ."cust.tax_id," - ."sorder.alloc," - ."IFNULL(allocs.ord_allocs, 0)+IFNULL(inv.inv_allocs ,0) AS sum_paid," - ."sorder.prep_amount>0 as prepaid" - ." FROM ".TB_PREF."sales_orders sorder + $sql = "SELECT sorder.*, + cust.name, + cust.curr_code, + cust.address, + loc.location_name, + cust.discount, + stype.sales_type, + stype.id AS sales_type_id, + stype.tax_included, + stype.factor, + ship.shipper_name, + tax_group.name AS tax_group_name, + tax_group.id AS tax_group_id, + cust.tax_id, + sorder.alloc, + IFNULL(allocs.ord_allocs, 0)+IFNULL(inv.inv_allocs ,0) AS sum_paid, + sorder.prep_amount>0 as prepaid + FROM ".TB_PREF."sales_orders sorder LEFT JOIN (SELECT trans_no_to, sum(amt) ord_allocs FROM ".TB_PREF."cust_allocations WHERE trans_type_to=".ST_SALESORDER." AND trans_no_to=".db_escape($order_no)." GROUP BY trans_no_to) allocs ON sorder.trans_type=".ST_SALESORDER." AND allocs.trans_no_to=sorder.order_no @@ -280,20 +280,19 @@ function get_sales_order_header($order_no, $trans_type) //---------------------------------------------------------------------------------------- function get_sales_order_details($order_no, $trans_type) { - $sql = "SELECT id, stk_code, unit_price, " - .TB_PREF."sales_order_details.description," - .TB_PREF."sales_order_details.quantity, - discount_percent, - qty_sent as qty_done, " - .TB_PREF."stock_master.units," - .TB_PREF."stock_master.mb_flag," - .TB_PREF."stock_master.material_cost + " - .TB_PREF."stock_master.labour_cost + " - .TB_PREF."stock_master.overhead_cost AS standard_cost - FROM ".TB_PREF."sales_order_details, ".TB_PREF."stock_master - WHERE ".TB_PREF."sales_order_details.stk_code = ".TB_PREF."stock_master.stock_id - AND order_no =" . db_escape($order_no) - ." AND trans_type = " . db_escape($trans_type) . " ORDER BY id"; + $sql = "SELECT id, stk_code, unit_price, + line.description, + line.quantity, + discount_percent, + qty_sent as qty_done, + item.units, + item.mb_flag, + item.material_cost + FROM ".TB_PREF."sales_order_details line," + .TB_PREF."stock_master item + WHERE line.stk_code = item.stock_id + AND order_no =".db_escape($order_no) + ." AND trans_type = ".db_escape($trans_type) . " ORDER BY id"; return db_query($sql, "Retreive order Line Items"); } @@ -333,7 +332,7 @@ function read_sales_order($order_no, &$order, $trans_type) $order->alloc = $myrow['alloc']; $order->sum_paid = $myrow["sum_paid"]; // sum of all prepayments to so (also invoiced) $order->prep_amount = $myrow["prep_amount"]; - $order->prepayments = get_payments_for($order_no, $myrow['trans_type']); + $order->prepayments = get_payments_for($order_no, $myrow['trans_type'], $myrow['debtor_no']); $result = get_sales_order_details($order_no, $order->trans_type); if (db_num_rows($result) > 0) @@ -343,7 +342,7 @@ function read_sales_order($order_no, &$order, $trans_type) { $order->add_to_cart($line_no,$myrow["stk_code"],$myrow["quantity"], $myrow["unit_price"], $myrow["discount_percent"], - $myrow["qty_done"], $myrow["standard_cost"], $myrow["description"], $myrow["id"] ); + $myrow["qty_done"], $myrow["material_cost"], $myrow["description"], $myrow["id"] ); $line_no++; } } @@ -409,8 +408,8 @@ function get_customer_to_order($customer_id) { // Now check to ensure this account is not on hold */ $sql = "SELECT cust.name, - cust.address, " - .TB_PREF."credit_status.dissallow_invoices, + cust.address, + credit_status.dissallow_invoices, cust.sales_type AS salestype, cust.dimension_id, cust.dimension2_id, @@ -421,15 +420,15 @@ function get_customer_to_order($customer_id) { cust.discount, cust.payment_terms, cust.pymt_discount, - cust.credit_limit - Sum(IFNULL(IF(trans.type=11 OR trans.type=12 OR trans.type=2, + cust.credit_limit - Sum(IFNULL(IF(trans.type IN(".implode(',', array(ST_CUSTCREDIT, ST_CUSTPAYMENT, ST_BANKDEPOSIT))."), -1, 1) * (ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount),0)) as cur_credit FROM ".TB_PREF."debtors_master cust LEFT JOIN ".TB_PREF."debtor_trans trans ON trans.type!=".ST_CUSTDELIVERY." AND trans.debtor_no = cust.debtor_no," - .TB_PREF."credit_status, " + .TB_PREF."credit_status credit_status," .TB_PREF."sales_types stype WHERE cust.sales_type=stype.id - AND cust.credit_status=".TB_PREF."credit_status.id - AND cust.debtor_no = ".db_escape($customer_id) + AND cust.credit_status=credit_status.id + AND cust.debtor_no=".db_escape($customer_id) ." GROUP by cust.debtor_no"; $result =db_query($sql,"Customer Record Retreive"); @@ -439,19 +438,19 @@ function get_customer_to_order($customer_id) { function get_branch_to_order($customer_id, $branch_id) { // the branch was also selected from the customer selection so default the delivery details from the customer branches table cust_branch. The order process will ask for branch details later anyway - $sql = "SELECT ".TB_PREF."cust_branch.br_name, " - .TB_PREF."cust_branch.br_address, " - .TB_PREF."cust_branch.br_post_address, " - ." default_location, location_name, default_ship_via, " - .TB_PREF."tax_groups.name AS tax_group_name, " - .TB_PREF."tax_groups.id AS tax_group_id - FROM ".TB_PREF."cust_branch, " - .TB_PREF."tax_groups, " - .TB_PREF."locations - WHERE ".TB_PREF."cust_branch.tax_group_id = ".TB_PREF."tax_groups.id - AND ".TB_PREF."locations.loc_code=default_location - AND ".TB_PREF."cust_branch.branch_code=".db_escape($branch_id)." - AND ".TB_PREF."cust_branch.debtor_no = ".db_escape($customer_id); + $sql = "SELECT branch.br_name, + branch.br_address, + branch.br_post_address, + default_location, location_name, default_ship_via, + tax_group.name AS tax_group_name, + tax_group.id AS tax_group_id + FROM ".TB_PREF."cust_branch branch," + .TB_PREF."tax_groups tax_group," + .TB_PREF."locations location + WHERE branch.tax_group_id = tax_group.id + AND location.loc_code=default_location + AND branch.branch_code=".db_escape($branch_id)." + AND branch.debtor_no=".db_escape($customer_id); return db_query($sql,"Customer Branch Record Retreive"); } @@ -467,6 +466,10 @@ function get_branch_to_order($customer_id, $branch_id) { function get_sql_for_sales_orders_view($trans_type, $trans_no, $filter, $stock_item='', $from='', $to='', $ref='', $location=ALL_TEXT, $customer_id=ALL_TEXT) { + if ($filter=='OutstandingOnly') + $order_value = 'Sum(line.unit_price*(line.quantity-line.qty_sent)*(1-line.discount_percent))+freight_cost'; + else + $order_value = 'Sum(line.unit_price*line.quantity*(1-line.discount_percent))+freight_cost'; $sql = "SELECT sorder.order_no, @@ -479,7 +482,7 @@ function get_sql_for_sales_orders_view($trans_type, $trans_no, $filter, ."sorder.ord_date, sorder.delivery_date, sorder.deliver_to, - Sum(line.unit_price*line.quantity*(1-line.discount_percent))+freight_cost AS OrderValue, + $order_value AS OrderValue, sorder.type, debtor.curr_code, Sum(line.qty_sent) AS TotDelivered, @@ -560,7 +563,8 @@ function get_sql_for_sales_orders_view($trans_type, $trans_no, $filter, sorder.branch_code, sorder.customer_ref, sorder.ord_date, - sorder.deliver_to"; + sorder.deliver_to + ORDER BY sorder.order_no DESC"; return $sql; } @@ -606,3 +610,42 @@ function sales_order_set_template($id, $status) $sql = "UPDATE ".TB_PREF."sales_orders SET type = ".db_escape($status)." WHERE order_no=".db_escape($id); db_query($sql, "Can't change sales order type"); } + +/* + Check whether sales order is issued in prepaid mode and already opened +*/ + +function is_prepaid_order_open($order_no) +{ + $sql = "SELECT count(*) + FROM ".TB_PREF."sales_orders o, + ((SELECT trans_no_to FROM ".TB_PREF."cust_allocations + WHERE trans_type_to=".ST_SALESORDER." AND trans_no_to=".db_escape($order_no).") + UNION + (SELECT order_ FROM ".TB_PREF."debtor_trans + WHERE type=".ST_SALESINVOICE." AND order_=".db_escape($order_no).")) related + WHERE + o.prep_amount>0 + AND o.trans_type = ".ST_SALESORDER." + AND o.order_no = " . db_escape($order_no); + + $result = db_fetch(db_query($sql, "cannot check prepaid order open")); + + return $result[0]; +} + +function last_sales_order_detail($order, $field) +{ + $sql = "SELECT $field + FROM ".TB_PREF."sales_order_details d + WHERE order_no = + (SELECT order_no FROM ".TB_PREF."sales_orders o + WHERE debtor_no=" . db_escape($order->customer_id) . " + ORDER BY order_no DESC LIMIT 1) + ORDER BY d.id DESC LIMIT 1"; + + $last_query=db_query($sql, "Could not retrieve last order detail"); + $row = db_fetch_row($last_query); + return $row == false ? false : $row[0]; +} +