X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=sales%2Fincludes%2Fdb%2Fsales_order_db.inc;h=b3cd6553952df0fa6b4118c549e894c1ae49c4be;hb=91bede4226ba8b45151f0501ac88eb946ed5f2a1;hp=636dea7ebf4af549985c84742c6867cc58183625;hpb=49fa30e88e27b311edaca3ae3789d62e4629eb39;p=fa-stable.git diff --git a/sales/includes/db/sales_order_db.inc b/sales/includes/db/sales_order_db.inc index 636dea7e..b3cd6553 100644 --- a/sales/includes/db/sales_order_db.inc +++ b/sales/includes/db/sales_order_db.inc @@ -22,7 +22,7 @@ function add_sales_order(&$order) $total = $order->get_trans_total(); $sql = "INSERT INTO ".TB_PREF."sales_orders (order_no, type, debtor_no, trans_type, branch_code, customer_ref, reference, comments, ord_date, order_type, ship_via, deliver_to, delivery_address, contact_phone, - freight_cost, from_stk_loc, delivery_date, payment_terms, total) + freight_cost, from_stk_loc, delivery_date, payment_terms, total, prep_amount) VALUES (" .db_escape($order_no) . "," .db_escape($order_type) . "," . db_escape($order->customer_id) . ", " .db_escape($order->trans_type) . "," .db_escape($order->Branch) . ", ". db_escape($order->cust_ref) .",". @@ -38,7 +38,8 @@ function add_sales_order(&$order) db_escape($order->Location) .", " . db_escape($del_date) . "," . db_escape($order->payment) . "," . - db_escape($total). ")"; + db_escape($total) . "," . + db_escape($order->prep_amount).")"; db_query($sql, "order Cannot be Added"); @@ -87,6 +88,9 @@ function add_sales_order(&$order) $line->discount_percent)"; db_query($sql, "order Details Cannot be Added"); + // Now mark quotation line as processed + if ($order->trans_type == ST_SALESORDER && $line->src_id) + update_parent_line(ST_SALESORDER, $line->src_id, $line->qty_dispatched); // clear all the quote despite all or the part was ordered } /* inserted line items into sales order details */ add_audit_trail($order->trans_type, $order_no, $order->document_date); @@ -163,6 +167,10 @@ function update_sales_order($order) $total = $order->get_trans_total(); begin_transaction(); + hook_db_prewrite($order, $order->trans_type); + + if ($order->trans_type == ST_SALESORDER) + $allocs = get_payments_for($order_no, ST_SALESORDER); $sql = "UPDATE ".TB_PREF."sales_orders SET type =".db_escape($order->so_type)." , debtor_no = " . db_escape($order->customer_id) . ", @@ -181,12 +189,20 @@ function update_sales_order($order) delivery_date = " .db_escape($del_date). ", version = ".($version+1).", payment_terms = " .db_escape($order->payment). ", - total = ". db_escape($total) ." + total = ". db_escape($total). ", + prep_amount = ". db_escape($order->prep_amount) ." WHERE order_no=" . db_escape($order_no) ." AND trans_type=".$order->trans_type." AND version=".$version; db_query($sql, "order Cannot be Updated, this can be concurrent edition conflict"); + $id_tokeep = array(); + foreach ($order->line_items as $line) { + array_push($id_tokeep , $line->id); + } + $id_list = implode(', ', $id_tokeep); + $sql = "DELETE FROM ".TB_PREF."sales_order_details WHERE order_no =" . db_escape($order_no) . " AND trans_type=".$order->trans_type; + $sql .= " AND id NOT IN ($id_list)"; db_query($sql, "Old order Cannot be Deleted"); @@ -227,25 +243,39 @@ function update_sales_order($order) } } } - $sql = "INSERT INTO ".TB_PREF."sales_order_details - (id, order_no, trans_type, stk_code, description, unit_price, quantity, - discount_percent, qty_sent) - VALUES ("; - $sql .= db_escape($line->id ? $line->id : 0) . "," - .$order_no . ",".$order->trans_type."," - .db_escape($line->stock_id) . "," - .db_escape($line->item_description) . ", " - .db_escape($line->price) . ", " - .db_escape($line->quantity) . ", " - .db_escape($line->discount_percent) . ", " - .db_escape($line->qty_done) ." )"; - - db_query($sql, "Old order Cannot be Inserted"); - + if (!$line->id) //new line + $sql = "INSERT INTO ".TB_PREF."sales_order_details + (order_no, trans_type, stk_code, description, unit_price, quantity, + discount_percent, qty_sent) + VALUES (".$order_no . ",".$order->trans_type."," + .db_escape($line->stock_id) . "," + .db_escape($line->item_description) . ", " + .db_escape($line->price) . ", " + .db_escape($line->quantity) . ", " + .db_escape($line->discount_percent) . ", " + .db_escape($line->qty_done) ." )"; + else + $sql = "UPDATE ".TB_PREF."sales_order_details + SET id=".db_escape($line->id).", + order_no=$order_no, + trans_type=".$order->trans_type.", + stk_code=".db_escape($line->stock_id).", + description=".db_escape($line->item_description).", + unit_price=".db_escape($line->price).", + quantity=".db_escape($line->quantity).", + discount_percent=".db_escape($line->discount_percent).", + qty_sent=".db_escape($line->qty_done)." + WHERE id = ".db_escape($line->id); + + db_query($sql, "Old order Cannot be updated"); } /* inserted line items into sales order details */ + if ($order->trans_type == ST_SALESORDER) + reallocate_payments($order_no, ST_SALESORDER, $ord_date, $total, $allocs); add_audit_trail($order->trans_type, $order_no, $order->document_date, _("Updated.")); $Refs->save($order->trans_type, $order_no, $order->reference); + + hook_db_postwrite($order, $order->trans_type); commit_transaction(); if ($loc_notification == 1 && count($st_ids) > 0) { @@ -276,27 +306,38 @@ 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, " - ."ship.shipper_name, " + ."stype.factor, " + ."ship.shipper_name, " ."tax_group.name AS tax_group_name , " - ."tax_group.id AS tax_group_id " - ."FROM ".TB_PREF."sales_orders sorder, " + ."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 + LEFT JOIN (SELECT order_, sum(alloc) inv_allocs FROM ".TB_PREF."debtor_trans + WHERE type=".ST_SALESINVOICE." AND order_=".db_escape($order_no)." GROUP BY order_) + inv ON sorder.trans_type=".ST_SALESORDER." AND inv.order_=sorder.order_no + LEFT JOIN ".TB_PREF."shippers ship ON ship.shipper_id = sorder.ship_via," .TB_PREF."debtors_master cust," .TB_PREF."sales_types stype, " .TB_PREF."tax_groups tax_group, " .TB_PREF."cust_branch branch," - .TB_PREF."locations loc, " - .TB_PREF."shippers ship + .TB_PREF."locations loc WHERE sorder.order_type=stype.id AND branch.branch_code = sorder.branch_code AND branch.tax_group_id = tax_group.id AND sorder.debtor_no = cust.debtor_no AND loc.loc_code = sorder.from_stk_loc - AND ship.shipper_id = sorder.ship_via AND sorder.trans_type = " . db_escape($trans_type) ." AND sorder.order_no = " . db_escape($order_no ); @@ -324,8 +365,9 @@ function get_sales_order_details($order_no, $trans_type) { .TB_PREF."sales_order_details.quantity, discount_percent, qty_sent as qty_done, " - .TB_PREF."stock_master.units, - ".TB_PREF."stock_master.material_cost + " + .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 @@ -353,7 +395,7 @@ function read_sales_order($order_no, &$order, $trans_type) $myrow["tax_group_name"], $myrow["contact_phone"]); $order->set_sales_type($myrow["sales_type_id"], $myrow["sales_type"], - $myrow["tax_included"], 0); // no default price calculations on edit + $myrow["tax_included"], $myrow["factor"]); // no default price calculations on edit $order->set_location($myrow["from_stk_loc"], $myrow["location_name"]); @@ -367,6 +409,12 @@ function read_sales_order($order_no, &$order, $trans_type) $order->due_date = sql2date($myrow["delivery_date"]); $order->document_date = sql2date($myrow["ord_date"]); + $order->prepaid = $myrow["prepaid"]; + $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']); + $result = get_sales_order_details($order_no, $order->trans_type); if (db_num_rows($result) > 0) { @@ -380,7 +428,6 @@ function read_sales_order($order_no, &$order, $trans_type) } } - hook_db_postread($order, $trans_type); return true; } @@ -431,8 +478,10 @@ function get_invoice_duedate($terms, $invdate) if ($myrow['day_in_following_month'] > 0) $duedate = add_days(end_month($invdate), $myrow['day_in_following_month']); - else + elseif ($myrow['days_before_due'] > 0) $duedate = add_days($invdate, $myrow['days_before_due']); + else + $duedate = $invdate; return $duedate; } @@ -487,17 +536,25 @@ function get_branch_to_order($customer_id, $branch_id) { return db_query($sql,"Customer Branch Record Retreive"); } -function get_sql_for_sales_orders_view($selected_customer, $trans_type) +/* + Supported filters: + + InvoiceTemplates + DeliveryTemplates + OutstandingOnly + PrepaidOrders +*/ +function get_sql_for_sales_orders_view($selected_customer, $trans_type, $trans_no, $filter, + $stock_item=null, $from='', $to='', $ref='', $location='', $customer_id=ALL_TEXT) { - global $selected_stock_item; - + $sql = "SELECT sorder.order_no, sorder.reference, debtor.name, branch.br_name," - .($_POST['order_view_mode']=='InvoiceTemplates' - || $_POST['order_view_mode']=='DeliveryTemplates' ? + .($filter=='InvoiceTemplates' + || $filter=='DeliveryTemplates' ? "sorder.comments, " : "sorder.customer_ref, ") ."sorder.ord_date, sorder.delivery_date, @@ -506,8 +563,18 @@ function get_sql_for_sales_orders_view($selected_customer, $trans_type) sorder.type, debtor.curr_code, Sum(line.qty_sent) AS TotDelivered, - Sum(line.quantity) AS TotQuantity - FROM ".TB_PREF."sales_orders as sorder, " + Sum(line.quantity) AS TotQuantity, + Sum(line.invoiced) AS TotInvoiced, + alloc, + prep_amount, + allocs.ord_payments, + inv.inv_payments, + sorder.total + FROM ".TB_PREF."sales_orders as sorder + LEFT JOIN (SELECT trans_no_to, sum(amt) ord_payments FROM ".TB_PREF."cust_allocations WHERE trans_type_to=".ST_SALESORDER." GROUP BY trans_no_to) + allocs ON sorder.trans_type=".ST_SALESORDER." AND allocs.trans_no_to=sorder.order_no + LEFT JOIN (SELECT order_, sum(prep_amount) inv_payments FROM ".TB_PREF."debtor_trans WHERE type=".ST_SALESINVOICE." GROUP BY order_) + inv ON sorder.trans_type=".ST_SALESORDER." AND inv.order_=sorder.order_no," .TB_PREF."sales_order_details as line, " .TB_PREF."debtors_master as debtor, " .TB_PREF."cust_branch as branch @@ -518,53 +585,94 @@ function get_sql_for_sales_orders_view($selected_customer, $trans_type) AND sorder.branch_code = branch.branch_code AND debtor.debtor_no = branch.debtor_no"; - if (isset($_POST['OrderNumber']) && $_POST['OrderNumber'] != "") + if (isset($trans_no) && $trans_no != "") { // search orders with number like - $number_like = "%".$_POST['OrderNumber']; - $sql .= " AND sorder.order_no LIKE ".db_escape($number_like) - ." GROUP BY sorder.order_no"; + $number_like = "%".$trans_no; + $sql .= " AND sorder.order_no LIKE ".db_escape($number_like); +// ." GROUP BY sorder.order_no"; } - elseif (isset($_POST['OrderReference']) && $_POST['OrderReference'] != "") + elseif ($ref != "") { // search orders with reference like - $number_like = "%".$_POST['OrderReference']."%"; - $sql .= " AND sorder.reference LIKE ".db_escape($number_like) - ." GROUP BY sorder.order_no"; + $number_like = "%".$ref."%"; + $sql .= " AND sorder.reference LIKE ".db_escape($number_like); +// ." GROUP BY sorder.order_no"; } else // ... or select inquiry constraints { - if ($_POST['order_view_mode']!='DeliveryTemplates' && $_POST['order_view_mode']!='InvoiceTemplates') + if ($filter!='DeliveryTemplates' && $filter!='InvoiceTemplates' && $filter!='OutstandingOnly') { - $date_after = date2sql($_POST['OrdersAfterDate']); - $date_before = date2sql($_POST['OrdersToDate']); + $date_after = date2sql($from); + $date_before = date2sql($to); $sql .= " AND sorder.ord_date >= '$date_after'" ." AND sorder.ord_date <= '$date_before'"; } + } if ($trans_type == ST_SALESQUOTE && !check_value('show_all')) - $sql .= " AND sorder.delivery_date >= '".date2sql(Today())."'"; + $sql .= " AND sorder.delivery_date >= '".date2sql(Today())."' AND line.qty_sent=0"; // show only outstanding, not realized quotes + if ($selected_customer != -1) $sql .= " AND sorder.debtor_no=".db_escape($selected_customer); - if (isset($selected_stock_item)) - $sql .= " AND line.stk_code=".db_escape($selected_stock_item); + if (isset($stock_item)) + $sql .= " AND line.stk_code=".db_escape($stock_item); - if (isset($_POST['StockLocation']) && $_POST['StockLocation'] != ALL_TEXT) - $sql .= " AND sorder.from_stk_loc = ".db_escape($_POST['StockLocation']); + if ($location) + $sql .= " AND sorder.from_stk_loc = ".db_escape($location); - if ($_POST['order_view_mode']=='OutstandingOnly') + if ($filter=='OutstandingOnly') $sql .= " AND line.qty_sent < line.quantity"; - elseif ($_POST['order_view_mode']=='InvoiceTemplates' || $_POST['order_view_mode']=='DeliveryTemplates') + + if ($filter=='PrepaidOrders') + $sql .= " AND prep_amount>0"; + + elseif ($filter=='InvoiceTemplates' || $filter=='DeliveryTemplates') $sql .= " AND sorder.type=1"; + //Chaiatanya : New Filter + if ($customer_id != ALL_TEXT) + $sql .= " AND sorder.debtor_no = ".db_escape($customer_id); + $sql .= " GROUP BY sorder.order_no, sorder.debtor_no, sorder.branch_code, sorder.customer_ref, sorder.ord_date, sorder.deliver_to"; - } return $sql; } -?> \ No newline at end of file + +//-------------------------------------------------------------------------------------------------- +function update_prepaid_so_line($line_id, $qty_invoiced) +{ + $sql = "UPDATE ".TB_PREF."sales_order_details + SET invoiced = invoiced + ".(float)$qty_invoiced." + WHERE id=".db_escape($line_id); + + db_query($sql, "The document detail record could not be updated with invoiced qty"); + return true; +} +/* + Returns array of all issued invoices to sales order $order_no, optinally up to trans_no==$up_to +*/ +function get_sales_order_invoices($order_no) +{ + $sql = "SELECT trans_no, dt.type as type, tran_date, reference, prep_amount + FROM ".TB_PREF."debtor_trans dt + LEFT JOIN ".TB_PREF."voided v ON v.type=dt.type AND v.id=dt.trans_no + WHERE ISNULL(v.id) AND dt.type=".ST_SALESINVOICE." AND dt.order_=".db_escape($order_no) + ." ORDER BY dt.tran_date, dt.reference, dt.trans_no"; + + return db_query($sql, "cannot retrieve sales invoices for sales order"); +} + +function is_sales_order_started($order_no) +{ + $sql = "SELECT count(*) FROM ".TB_PREF."sales_order_details WHERE order_no=".db_escape($order_no)." AND trans_type=".ST_SALESORDER + ." AND (invoiced!=0 OR qty_sent!=0)"; + + $result = db_fetch(db_query($sql, "cannot retrieve sales invoices for sales order")); + return $result[0]; +}