+
+/*
+ Supported filters:
+
+ InvoiceTemplates
+ DeliveryTemplates
+ OutstandingOnly
+ PrepaidOrders
+*/
+function get_sql_for_sales_orders_view($trans_type, $trans_no, $filter,
+ $stock_item='', $from='', $to='', $ref='', $location=ALL_TEXT, $customer_id=ALL_TEXT, $show_voided = 0)
+{
+ 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,
+ sorder.reference,
+ debtor.name,
+ branch.br_name,"
+ .($filter=='InvoiceTemplates'
+ || $filter=='DeliveryTemplates' ?
+ "sorder.comments, " : "sorder.customer_ref, ")
+ ."sorder.ord_date,
+ sorder.delivery_date,
+ sorder.deliver_to,
+ $order_value AS OrderValue,
+ sorder.type,
+ debtor.curr_code,
+ Sum(line.qty_sent) AS TotDelivered,
+ Sum(line.quantity) AS TotQuantity,
+ Sum(line.invoiced) AS TotInvoiced,
+ alloc,
+ prep_amount,
+ allocs.ord_payments,
+ inv.inv_payments,
+ sorder.total,
+ sorder.trans_type,
+ sorder.debtor_no
+ 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
+ WHERE sorder.order_no = line.order_no
+ AND sorder.trans_type = line.trans_type
+ AND sorder.trans_type = ".db_escape($trans_type)."
+ AND sorder.debtor_no = debtor.debtor_no
+ AND sorder.branch_code = branch.branch_code
+ AND debtor.debtor_no = branch.debtor_no";
+
+ if (isset($trans_no) && $trans_no != "")
+ {
+ // search orders with number like
+ $number_like = "%".$trans_no;
+ $sql .= " AND sorder.order_no LIKE ".db_escape($number_like);
+// ." GROUP BY sorder.order_no";
+ }
+ elseif ($ref != "")
+ {
+ // search orders with reference like
+ $sql .= " AND sorder.reference LIKE ".db_escape('%' . $ref . '%');
+// ." GROUP BY sorder.order_no";
+ }
+ else // ... or select inquiry constraints
+ {
+ if ($filter!='DeliveryTemplates' && $filter!='InvoiceTemplates' && $filter!='OutstandingOnly')
+ {
+ $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())."' 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 ($stock_item != ALL_TEXT)
+ $sql .= " AND line.stk_code=".db_escape($stock_item);
+
+ if ($location != ALL_TEXT)
+ $sql .= " AND sorder.from_stk_loc = ".db_escape($location);
+
+ if ($filter=='OutstandingOnly')
+ $sql .= " AND line.qty_sent < line.quantity";
+
+ 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";
+ if ($show_voided == 0)
+ $sql .= " HAVING OrderValue != 0";
+ $sql .= " ORDER BY sorder.order_no DESC";
+ return $sql;
+}
+
+//--------------------------------------------------------------------------------------------------
+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];
+}
+
+//---------------------------------------------------------------------------------------------
+//
+// Mark/unmark sales order as template.
+//
+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];
+}
+