and from a string
* generated by the above *_cell function.
* This is usefull to use the result of the function within a pager
* which already include the
...
* However, for function which doesn't return a string
* but echo, we need to capture the output of the echo first.
*/
function start_extract_cell() {
// We need
ob_start();
}
function end_extract_cell() {
$content = ob_get_contents();
ob_end_clean();
return extract_cell($content);
}
function extract_cell($td) {
if(preg_match('/\s*
(.*)<\/td>\s*/s', $td, $matches)) {
return $matches[1];
}
else {
return $td;
}
}
/* remove unchecked data so each
* funcion doesn't to check what's been checked or not.
*/
function filter_data(&$data) {
if(!isset($data['detail'])) return;
$new_details = array();
foreach(@$data['detail'] as $detail_id => $values) {
if(@$values['check'] === 'on') $new_details[$detail_id]=$values;
}
$data['detail'] = $new_details;
}
function post_to_detail_ids() {
if(!isset($_POST['detail'])) return;
$detail_ids = array();
foreach($_POST['detail'] as $detail_id => $values) {
array_push($detail_ids, $detail_id);
}
return $detail_ids;
}
function update_extra_order_details() {
if(!isset($_POST['Update']) || $_POST['Update'] != 'Update') return;
$cart = post_to_detail_ids();
if($cart === null) return;
begin_transaction();
hook_db_prewrite($cart, 'order_xtra');
// update in database all field
$date_fields = array('hold_until_date', 'required_date', 'expiry_date', 'priority');
foreach($_POST['detail'] as $detail_id => $values) {
$set = array();
$comment=$values['comment'];
array_push($set, " comment='$comment' "); // erase comment if needed.
foreach($date_fields as $date_field) {
if(isset($values[$date_field])) {
$input_date = trim($values[$date_field]);
if($input_date == "") {
// Only the priority field can be set to null.
if($date_field == 'priority') array_push($set," $date_field = NULL");
} else {
$date = date2sql($input_date);
array_push($set," $date_field='$date' ");
}
}
}
if(!empty($set)) {
$sql = "UPDATE ".TB_PREF."sales_order_details SET ".implode($set,', ')." WHERE id = $detail_id ";
if(!db_query($sql)) {
display_error('Problem while updating order details. Try again');
cancel_transaction();
return;
}
}
}
hook_db_postwrite($cart, 'order_xtra');
commit_transaction();
display_notification('Order details updated');
}
function split_order_details() {
if(!isset($_POST['Split']) || $_POST['Split'] != 'Split') return;
$splitter = new Splitter($_POST);
begin_transaction();
$cart = post_to_detail_ids();
if($cart === null) return;
hook_db_prewrite($cart, 'order_xtra');
if($splitter->splitAll()) display_warning("Items have been split.");
hook_db_postwrite($cart, 'order_xtra');
commit_transaction();
}
function bulk_update_order_details() {
if(!isset($_POST['Bulk']) || $_POST['Bulk'] != 'Bulk') return;
$bulk_updater = new BulkUpdater($_POST);
begin_transaction();
$cart = post_to_detail_ids();
if($cart === null) return;
hook_db_prewrite($cart, 'order_xtra');
$bulk_updater->update();
hook_db_postwrite($cart, 'order_xtra');
commit_transaction();
}
function compute_input_name($row, $field) {
$row_id = $row['id'];
return "detail[$row_id][$field]";
}
function view_link($dummy, $order_no)
{
return get_customer_trans_view_str(ST_SALESORDER, $order_no);
}
function edit_link($row, $order_no) {
return pager_link( _("# $order_no"),
"/sales/sales_order_entry.php?ModifyOrderNumber=" .$row['order_no']);
}
function item_link($dummy, $stock_id)
{
return pager_link( _($stock_id),
"/modules/order_line_extra/item_schedule.php?stock_id=" .$stock_id);
}
function order_link($row)
{
return pager_link( _("Sales Order"),
"/sales/sales_order_entry.php?NewQuoteToSalesOrder=" .$row['order_no'], ICON_DOC);
}
function customer_link($row) {
return customer_link2($row['debtor_ref'], $row['debtor_no']);
}
function customer_link2($name, $id) {
return pager_link(_($name), "/modules/order_line_extra/order_lines_view.php?customer_id=${id}");
}
function aggregate_comment($row) {
$comment = $row['order_comment'].";".$row['detail_comment'];
if($comment) {
$comments = array_map('trim', explode(';', $comment));
$comments = array_filter($comments);
$first = array_shift($comments);
if(count($comments) == 0)
return $first;
else {
$tooltip = implode(' - ', $comments);
return "$first more $tooltip";
}
}
return '';
}
function available_quantity($row, $available) {
$quantity = $row['quantity'];
$class = '';
if($available== 0) $class = $row['qoh'] == 0 ? 'stockmankobg' : 'limited';
else if($quantity > $available) $class = 'partial';
else if(OrderXtraConfig::early(sql2date($row['hold_until_date']))) $class = 'early';
return "$available";
}
function input_date_details($row, $field_name, $date) {
$row_id = $row['id'];
$name = compute_input_name($row, $field_name);
$_POST[$name] = sql2date($date);
start_extract_cell();
date_cells (null, $name, 'title', null, 0,0,1001);
return end_extract_cell();
return "
";
}
function input_priority_date_details($row, $date) {
return input_date_details($row, 'priority', $date);
}
function input_hold_until_date_details($row, $date) {
return input_date_details($row, 'hold_until_date', $date);
}
function input_required_date_details($row, $date) {
return input_date_details($row, 'required_date', $date);
}
function input_expiry_date_details($row, $date) {
return input_date_details($row, 'expiry_date', $date);
}
function input_comment_details($row, $comment) {
$row_id = $row['id'];
return "
";
}
function input_check_details($row, $dummy) {
$row_id = $row['id'];
return "
";
}
function pick_checkbox($row)
{
$id = $row['debtor_no']."_".$row['branch_code'];
$name = "chgpck" .$id;
$value = $row['quantity_to_pick'] !== null;
// save also in hidden field for testing during 'Update'
return checkbox(null, $name, $value, true,
_('Pick all possible item for this branch.'))
. hidden('pick['.$id.']', $value, false);
}
function get_order_details_extra($customer_id, $location, $item_like, $order_id) {
$select = "sod.id
, so.order_no
, stk_code
, sod.quantity - qty_sent quantity
, GREATEST(0, LEAST(qoh.quantity - quantity_before - COALESCE(tp.quantity,0) + COALESCE(p.quantity, 0) , sod.quantity - qty_sent)) AS available
, quantity_before
, qoh.quantity AS qoh
, p.quantity AS quantity_to_pick
, sod.`priority`
, hold_until_date
, required_date
, expiry_date
,comment";
$from = TB_PREF."sales_order_details sod
JOIN ".TB_PREF."sales_orders so ON (so.order_no = sod.order_no
AND so.trans_type = sod.trans_type
AND so.trans_type = ".ST_SALESORDER."
AND so.debtor_no = $customer_id
)
JOIN ".TB_PREF."denorm_qoh qoh ON (stock_id = stk_code AND loc_code = '$location')
LEFT JOIN ".TB_PREF."denorm_order_details_queue d ON (d.id = sod.id)
LEFT JOIN (".pick_query().") p ON (detail_id = sod.id)
LEFT JOIN (".totalpick_query().") tp ON (tp.stock_id = sod.stk_code)
";
$where = " sod.quantity > qty_sent
";
if($item_like) {
if($item_like[0]=='/') {
$regexp = substr($item_like, 1);
$where .= " AND stk_code RLIKE '$regexp'";
}
else
$where .= " AND stk_code LIKE '$item_like'";
}
if($order_id) {
$where .= " AND so.order_no = $order_id";
}
return array('select' => $select, 'from' => $from, 'where' => $where);
}
function get_order_summary($location) {
$held_condition = OrderXtraConfig::sql_held_condition();
$pick = pick_query();
$sub = "SELECT debtor_no, debtor_ref, branch_code, branch_ref, stk_code
, MIN(delivery_date)
, SUM(d.quantity) AS quantity
, SUM((d.quantity)*unit_price*(1-discount_percent/100)) AS amount
, MIN(required_date)
, GROUP_CONCAT(distinct comments separator ';') AS order_comment
, GROUP_CONCAT(distinct comment separator ';') AS detail_comment
, MAX(quantity_before) AS quantity_before
, @available := GREATEST(LEAST(d.quantity, COALESCE(qoh.quantity,0) - COALESCE(tp.quantity,0) + COALESCE(p.quantity,0) - quantity_before), 0)
, SUM(IF($held_condition, @available , 0)) AS quantity_held
, SUM(IF($held_condition, 0, @available)) AS available_quantity
, SUM(IF($held_condition, 0, @available*unit_price*(1-discount_percent/100))) AS available_amount
, SUM(p.quantity) AS quantity_to_pick
, SUM(p.quantity*unit_price) AS amount_to_pick
FROM ".TB_PREF."sales_order_details sod
JOIN ".TB_PREF."sales_orders so ON (so.order_no = sod.order_no
AND so.trans_type = sod.trans_type
AND so.trans_type = ".ST_SALESORDER."
)
JOIN ".TB_PREF."debtors_master USING (debtor_no)
JOIN ".TB_PREF."cust_branch USING (debtor_no, branch_code)
LEFT JOIN ($pick) p ON (detail_id = sod.id)
JOIN ".TB_PREF."denorm_order_details_queue d ON (d.id = sod.id)
LEFT JOIN ".TB_PREF."denorm_qoh qoh ON (qoh.stock_id = stk_code AND loc_code = '$location')
LEFT JOIN (".totalpick_query().") tp ON (tp.stock_id = sod.stk_code)
WHERE sod.quantity > qty_sent AND ".ST_SALESORDER."
GROUP BY debtor_no, branch_code, stk_code
";
//$sub = TB_PREF."order_summary_view";
//
$sql = array( "select" => "debtor_no, branch_code, debtor_ref, branch_ref
, `min(delivery_date)` as delivery_date
, sum(sub.quantity) as quantity
, sum(sub.amount) as amount
, sum(sub.available_quantity)
, sum(sub.available_amount)
, sum(quantity_held) as quantity_help
, min(`min(required_date)`) as required_date
, group_concat(distinct order_comment separator ';') as order_comment
, group_concat(distinct detail_comment separator ';') as detail_comment
, sum(quantity_to_pick) as quantity_to_pick
, sum(amount_to_pick) as amount_to_pick
", "from" => " ($sub) sub
LEFT JOIN ".TB_PREF."denorm_qoh qoh ON (stock_id = stk_code AND loc_code = '$location')
", "group" => " debtor_no, branch_code
");
return $sql;
}
?>