<?php
/**********************************************************************
***********************************************************************/
//----------------------------------------------------------------------------------------

function save_sales_order_header(&$order)
{
    $msgh = update_sales_order_header($order);
    if($msgh['success'] !== true){
            return $msgh;
    }

    save_sales_order_taxes($order);
    
    update_sales_order_totals($order);
    
    return array("success" => true, "tran_no" => $order->order_no, "message" => "");
}


function save_sales_order(&$order)
{
    if($order->order_no > 0){
        $msgh = update_sales_order_header($order);
        if($msgh['success'] !== true){
            return $msgh;
        }
        $msgl = save_sales_order_line($order);
        if($msgl['success'] !== true){
            return $msgl;
        }
    }
    else{
        $msgh = add_sales_order_header($order);
        if($msgh['success'] !== true){
            return $msgh;
        }
        $order->order_no = $msgh['tran_no'];
        
        $msgl = save_sales_order_line($order);
        if($msgl['success'] !== true){
            return $msgl;
        }
    }
    
    save_sales_order_taxes($order);
    
    update_sales_order_totals($order);
    
    return array("success" => true, "tran_no" => $order->order_no, "message" => "");
}



function save_delete_sales_order_item(&$order, $id)
{
    $msgh = delete_sales_order_item($order, $id);
    if($msgh['success'] !== true){
            return $msgh;
    }

    save_sales_order_taxes($order);
    
    update_sales_order_totals($order);
    
    return array("success" => true, "tran_no" => $order->order_no, "message" => "");
}

function update_sales_order_totals($order)
{
    $order_total = $order->get_trans_total();
    $taxes_total = $order->get_taxes();
    $staxes_total = $order->get_staxes();
    
    $sql = "UPDATE ".TB_PREF."sales_orders 
            SET total = " .db_escape($order_total) .",      
                taxes = " .db_escape($taxes_total) .",     
                staxes = " .db_escape($staxes_total) ."     
            WHERE order_no=" . db_escape($order->order_no) ." AND 
                trans_type=".db_escape($order->trans_type);
    
	db_query($sql, "update_sales_order_totals");
}

function save_sales_order_taxes($order)
{
    $taxes = $order->calculate_save_sales_order_taxes($order->freight_cost);
    
    delete_sales_order_tax_details($order->order_no, $order->trans_type);
    
    foreach ($taxes as $tax_line){
            if($tax_line['Net'] > 0){ // $po_tax_line['Value'] can be zero for excempted taxes
                add_sales_order_tax_details($order->order_no, $order->trans_type,
                                        $tax_line['tax_type_id'], $tax_line['rate'], $order->tax_included, $tax_line['Value'], 
                                        $tax_line['Net'], 1, $order->document_date, '', $tax_line['suspended']);
            }
    }
}

function save_sales_order_line(&$order)
{
    
    if($order->line_items[0]->id > 0){
        return update_sales_order_line($order);
    }
    else{
        return add_sales_order_line($order);
    }
}

function update_sales_order_line($order)
{
    $order_no = $order->order_no;
    $line = $order->line_items[0];
    $kit_parent = '';
    
    $user_id = $_SESSION['wa_current_user']->user;
    $time = date('Y-m-d H:i:s');
        
    $sql = "UPDATE ".TB_PREF."sales_order_details 
                SET description = ".db_escape($line->item_description).", 
                    unit_price = ".db_escape($line->price).",  
                    quantity = ".db_escape($line->quantity).", 
                    discount_percent = ".db_escape($line->discount_percent).", 
                    order_unit = ".db_escape($line->tran_unit).", 
                    order_unit_qty = ".db_escape($line->tran_qty).", 
                    convertion_factor = ".db_escape($line->convertion_factor).", 
                    order_unit_price = ".db_escape($line->tran_unit_price).", 
                    minimum_price = ".db_escape($line->minimum_price).", 
                    list_price = ".db_escape($line->list_price).", 
                    kit_parent = ".db_escape($kit_parent).", 
                    tax_type_id = ".db_escape($line->tax_type).", 
                    tax_group_id = ".db_escape($order->tax_group_id).", 
                    order_type = ".db_escape($order->sales_type).", 
                    updated_by = ".db_escape($user_id).", 
                    updated_on = ".db_escape($time)." 
                WHERE order_no=" . db_escape($order_no) ." AND 
                    trans_type=".$order->trans_type." AND 
                    id =".$line->id;
    
    db_query($sql, "update_sales_order_line");
    
    $n = db_num_affected_rows();
    if($n !=  1){
        return array("success" => false, "tran_no" => $order_no, "message" => "Another user has updated the sales order line.");
    }
    else{
        return array("success" => true, "tran_no" => $order_no, "message" => "");
    }

}

function add_sales_order_line($order)
{
    $order_no = $order->order_no;
    $line = $order->line_items[0];
    $kit_parent = '';
    
    $user_id = $_SESSION['wa_current_user']->user;
    $time = date('Y-m-d H:i:s');
            
    $sql = "INSERT INTO ".TB_PREF."sales_order_details (order_no, trans_type, stk_code, 
                                description, unit_price, quantity, 
                                discount_percent, order_unit, order_unit_qty, 
                                convertion_factor, order_unit_price, minimum_price, 
                                list_price, kit_parent, tax_type_id,
                                tax_group_id, order_type,
                                created_by, created_on) 
            VALUES (";
                    $sql .= db_escape($order_no). ",".db_escape($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->tran_unit).", ".db_escape($line->tran_qty).", "
                            .db_escape($line->convertion_factor).", ".db_escape($line->tran_unit_price).", ".db_escape($line->minimum_price).", "
                            .db_escape($line->list_price).", ".db_escape($kit_parent).",".db_escape($line->tax_type).","
                            .db_escape($order->tax_group_id).", ".db_escape($order->sales_type).","
                            .db_escape($user_id).", ".db_escape($time).") ";
                    
    db_query($sql, "add_sales_order_line");

    $s = db_insert_id();
    if(!($s> 0)){
        return array("success" => false, "tran_no" => $order_no, "message" => "Could not add sales order line.");
    }        
	// 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
            $so_line_id = db_insert_id();
            update_parent_line(ST_SALESORDER, $line->src_id, $line->quantity); 
            add_sq_so_details($line, $so_line_id, $order->customer_id, $order->Branch);
    }
    
    $order->order_no = $order_no;
    
    return array("success" => true, "tran_no" => $order->order_no, "message" => "");
    
}


function update_sales_order_header($order)
{
    	global $loc_notification, $path_to_root, $Refs;

	$del_date = date2sql($order->due_date);
	$ord_date = date2sql($order->document_date);
	$order_no =  $order->order_no;
	$version = 1; //current($order->trans_no);
//	$total = $order->get_trans_total(); 

//    if($total == 0){
//        return array("success" => false, "tran_no" => $order_no, "message" => "UNEXPECTED: Order total is zero. Please try updating each line and saving.");
//    }
    
	hook_db_prewrite($order, $order->trans_type);
        
        $user_id = $_SESSION['wa_current_user']->user;
        $time = date('Y-m-d H:i:s');

	$sql = "UPDATE ".TB_PREF."sales_orders 
                SET type =".db_escape($order->so_type)." ,
                    debtor_no = " . db_escape($order->customer_id) . ",
                    branch_code = " . db_escape($order->Branch) . ",
                    customer_ref = ". db_escape($order->cust_ref) .",
                    reference = ". db_escape($order->reference) .",
                    comments = ". db_escape($order->Comments) .",
                    ord_date = " . db_escape($ord_date) . ",
                    order_type = " .db_escape($order->sales_type) . ",
                    ship_via = " . db_escape($order->ship_via) .",
                    deliver_to = " . db_escape($order->deliver_to) . ",
                    delivery_address = " . db_escape($order->delivery_address) . ",
                    contact_phone = " .db_escape($order->phone) . ",
                    freight_cost = " .db_escape($order->freight_cost) .",
                    from_stk_loc = " .db_escape($order->Location) .",
                    project_id = " .db_escape($order->project_id) .",
                    delivery_date = " .db_escape($del_date). ",
                    version = ".db_escape($version+1).",
                    payment_terms = " .db_escape($order->payment). ",
                    approval_status = " .db_escape($order->approval_status). ",
                    salesman_id = " .db_escape($order->salesman_id) .",    
                    tax_group_id = " .db_escape($order->tax_group_id) .",    
                    tax_included = " .db_escape($order->tax_included) .",    
                    row_state = " . db_new_row_state($order->row_state). ",
                    updated_by = " .db_escape($user_id) .",    
                    updated_on = " . db_escape($time). "
                WHERE order_no=" . db_escape($order_no) ." AND 
                    trans_type=".db_escape($order->trans_type)." AND  
                    row_state=".db_escape($order->row_state);
    
	db_query($sql, "order Cannot be Updated, this can be concurrent edition conflict");
    
        $n = db_num_affected_rows();
        if($n !=  1){
            return array("success" => false, "tran_no" => $order_no, "message" => "Another user has updated the sales order $order_no");
        }
        else{
            return array("success" => true, "tran_no" => $order_no, "message" => "");
        }
}

function add_sales_order_header(&$order)
{
    global $loc_notification, $path_to_root, $Refs;

    hook_db_prewrite($order, $order->trans_type);
    $order_no = get_next_trans_no($order->trans_type);
    $del_date = date2sql($order->due_date);
    $order_type = 0; // this is default on new order
    
//    $total = $order->get_trans_total();
//    if($total == 0){
//        return array("success" => false, "tran_no" => $order_no, "message" => "UNEXPECTED: Order total is zero. Please try updating each line and saving.");
//    }
    
    $total = 0;
    
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
    // Update quotation status
    if ($order->trans_type == ST_SALESORDER && $order->order_no > 0){
        update_sales_source_document_status(ST_SALESQUOTE, $order->order_no, AS_ORDERED, $order->row_state, $created_by);
    }
    
    $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, tax_group_id,
                    tax_included,
                    project_id, total, approval_status, 
                    salesman_id, created_by, created_on)
            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) .",". 
            db_escape($order->reference) .",". db_escape($order->Comments) .",'" .date2sql($order->document_date) . "', " .
            db_escape($order->sales_type) . ", " .db_escape($order->ship_via)."," .db_escape($order->deliver_to) . "," .
            db_escape($order->delivery_address) . ", " .db_escape($order->phone) . ", " . db_escape($order->freight_cost) .", " . 
            db_escape($order->Location) .", " .db_escape($del_date) . "," .
            db_escape($order->payment) . "," .db_escape($order->tax_group_id) . "," .
            db_escape($order->tax_included) . "," .
            db_escape($order->project_id) . "," .db_escape($total). "," .db_escape($order->approval_status). ",".
            db_escape($order->salesman_id) . "," .db_escape($created_by) . "," .db_escape($created_on). ")";

    db_query($sql, "order could not be added");
    
    $s = db_num_affected_rows();
    if(!($s> 0)){
        return array("success" => false, "tran_no" => $order_no, "message" => "Could not add new sales order header record.");
    }
    
    add_order_category_transaction($order->trans_type, $order_no, $order->order_category_id);
    
    $order->order_no = $order_no;
    
    return array("success" => true, "tran_no" => $order->order_no, "message" => "");
    
}


function add_sales_order(&$order)
{
	global $loc_notification, $path_to_root, $Refs;

	begin_transaction();
	hook_db_prewrite($order, $order->trans_type);
	$order_no = get_next_trans_no($order->trans_type);
	$del_date = date2sql($order->due_date);
	$order_type = 0; // this is default on new order
	$total = $order->get_trans_total();
    if($total == 0){
        return array("success" => false, "tran_no" => $order_no, "message" => "UNEXPECTED: Order total is zero. Please try updating each line and saving.");
    }
    
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
    // Update quotation status
    if ($order->trans_type == ST_SALESORDER && $order->order_no > 0){
        update_sales_source_document_status(ST_SALESQUOTE, $order->order_no, AS_ORDERED, $order->row_state, $created_by);
    }
    
	$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, 
                        project_id, total, approval_status, 
                        salesman_id, created_by, created_on)
		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) .",". 
                db_escape($order->reference) .",". db_escape($order->Comments) .",'" .date2sql($order->document_date) . "', " .
                db_escape($order->sales_type) . ", " .db_escape($order->ship_via)."," .db_escape($order->deliver_to) . "," .
                db_escape($order->delivery_address) . ", " .db_escape($order->phone) . ", " . db_escape($order->freight_cost) .", " . 
                db_escape($order->Location) .", " .db_escape($del_date) . "," .db_escape($order->payment) . "," .
                db_escape($order->project_id) . "," .db_escape($total). "," .db_escape($order->approval_status). ",".
                db_escape($order->salesman_id) . "," .db_escape($created_by) . "," .db_escape($created_on). ")";

	db_query($sql, "order could not be added");
    $s = db_num_affected_rows();
    if(!($s> 0)){
        return array("success" => false, "tran_no" => $order_no, "message" => "Could not add new sales order $order_no.");
    }
    
    add_order_category_transaction($order->trans_type, $order_no, $order->order_category_id);
    
	$order->order_no = ($order_no);

	if ($loc_notification == 1)
	{
		include_once($path_to_root . "/inventory/includes/inventory_db.inc");
		$st_ids = array();
		$st_names = array();
		$st_num = array();
		$st_reorder = array();
	}
	foreach ($order->line_items as $line)
	{
		if ($loc_notification == 1 && is_inventory_item($line->stock_id)){
			$loc = calculate_reorder_level($order->Location, $line, $st_ids, $st_names, $st_num, 
                                $st_reorder); 
        }
        
        if(isset($line->kit_parent) && strlen($line->kit_parent) > 0){
            $kit_parent = $line->kit_parent;
        }
        else{
            $kit_parent = '';
        }

        if($line->price == 0){
//            return array("success" => false, "tran_no" => $order_no, "message" => "UNEXPECTED: Item ".$line->stock_id." - ".$line->item_description." base unit price is zero. Please try updating each line and saving.");
        }
    
		$sql = "INSERT INTO ".TB_PREF."sales_order_details (order_no, trans_type, stk_code, 
                                description, unit_price, quantity, 
                                discount_percent, order_unit, order_unit_qty, 
                                convertion_factor, order_unit_price, minimum_price, 
                                list_price, kit_parent) 
                VALUES (";
                        $sql .= $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->tran_unit).", ".db_escape($line->tran_qty).", "
                                .db_escape($line->convertion_factor).", ".db_escape($line->tran_unit_price).", ".db_escape($line->minimum_price).", "
                                .db_escape($line->list_price).", ".db_escape($kit_parent).") ";
		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
            $so_line_id = db_insert_id();
            update_parent_line(ST_SALESORDER, $line->src_id, $line->quantity); 
            add_sq_so_details($line, $so_line_id, $order->customer_id, $order->Branch);
            
        }
	} /* 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);

	hook_db_postwrite($order, $order->trans_type);
	commit_transaction();

	if ($loc_notification == 1 && count($st_ids) > 0){
		send_reorder_email($loc, $st_ids, $st_names, $st_num, $st_reorder);
    }
	//return $order_no;
    return array("success" => true, "tran_no" => $order_no, "message" => "Sales order $order_no added.");
}

//----------------------------------------------------------------------------------------

function delete_sales_order($order_no, $trans_type)
{
    return;
    
	begin_transaction();
	hook_db_prevoid($trans_type, $order_no);

	$sql = "DELETE FROM ".TB_PREF."sales_orders WHERE order_no=" . db_escape($order_no) 
		. " AND trans_type=".db_escape($trans_type);

	db_query($sql, "order Header Delete");

	$sql = "DELETE FROM ".TB_PREF."sales_order_details WHERE order_no =" 
		.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."));
	commit_transaction();
}

//----------------------------------------------------------------------------------------
// Mark changes in sales_order_details
//
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";

    db_query($sql, 'Concurrent editing conflict while sales order update');

  }
  
}


function delete_sales_order_item($order, $id)
{
    $sql = "DELETE FROM ".TB_PREF."sales_order_details 
            WHERE order_no =" .db_escape($order->order_no) . " 
                AND trans_type=".db_escape($order->trans_type)." 
                AND id=".db_escape($id)." 
                AND qty_sent= 0 
                AND quantity_ordered = 0";
    
    db_query($sql, "delete_sales_order_item");
    
    $n = db_num_affected_rows();
    if($n == 1){
        return array("success" => true, "message" => '');
    }
    else{
        return array("success" => false, "message" => "Another user has updated the sales order line.");
    }
}

function update_sales_order_row_state($order_no, $row_state)
{

    $sql= 'UPDATE '.TB_PREF.'sales_orders 
              SET row_state = '.db_new_row_state($row_state).'
              WHERE order_no='. db_escape($order_no).' 
                  AND row_state ='. db_escape($row_state).' 
                  AND trans_type='.db_escape(ST_SALESORDER); 

    db_query($sql, 'update_sales_order_row_state');

    $n = db_num_affected_rows();
    if($n == 1){
        return array("success" => true, "message" => '');
    }
    else{
        return array("success" => false, "message" => "Another user has updated the source sales order.");
    }
  
}

//----------------------------------------------------------------------------------------

function update_sales_order($order)
{
	global $loc_notification, $path_to_root, $Refs;

	$del_date = date2sql($order->due_date);
	$ord_date = date2sql($order->document_date);
	$order_no =  ($order->order_no);
	$version= current($order->order_no);
	$total = $order->get_trans_total(); 

    if($total == 0){
        return array("success" => false, "tran_no" => $order_no, "message" => "UNEXPECTED: Order total is zero. Please try updating each line and saving.");
    }
    
	begin_transaction();
	hook_db_prewrite($order, $order->trans_type);

	$sql = "UPDATE ".TB_PREF."sales_orders 
            SET type =".db_escape($order->so_type)." ,
                debtor_no = " . db_escape($order->customer_id) . ",
                branch_code = " . db_escape($order->Branch) . ",
                customer_ref = ". db_escape($order->cust_ref) .",
                reference = ". db_escape($order->reference) .",
                comments = ". db_escape($order->Comments) .",
                ord_date = " . db_escape($ord_date) . ",
                order_type = " .db_escape($order->sales_type) . ",
                ship_via = " . db_escape($order->ship_via) .",
                deliver_to = " . db_escape($order->deliver_to) . ",
                delivery_address = " . db_escape($order->delivery_address) . ",
                contact_phone = " .db_escape($order->phone) . ",
                freight_cost = " .db_escape($order->freight_cost) .",
                from_stk_loc = " .db_escape($order->Location) .",
                project_id = " .db_escape($order->project_id) .",
                delivery_date = " .db_escape($del_date). ",
                version = ".($version+1).",
                payment_terms = " .db_escape($order->payment). ",
                total = ". db_escape($total) .",
                approval_status = " .db_escape($order->approval_status). ",
                salesman_id = " .db_escape($order->salesman_id) .",    
                row_state = " . db_new_row_state($order->row_state). "
            WHERE order_no=" . db_escape($order_no) ." AND 
                trans_type=".$order->trans_type." AND 
                version=".$version." AND 
                row_state=".$order->row_state;
    
	db_query($sql, "order Cannot be Updated, this can be concurrent edition conflict");
    
    if(db_num_affected_rows() !=  1){
        return array("success" => false, "tran_no" => $order_no, "message" => "Another user has updated the sales order $order_no");
    }

	$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");

	if ($loc_notification == 1)
	{
		include_once($path_to_root . "/inventory/includes/inventory_db.inc");
		$st_ids = array();
		$st_names = array();
		$st_num = array();
		$st_reorder = array();
	}
	foreach ($order->line_items as $line)
	{
		if ($loc_notification == 1 && is_inventory_item($line->stock_id)){
			$loc = calculate_reorder_level($order->Location, $line, $st_ids, $st_names, $st_num, $st_reorder); 
        }
        
		if($line->id) {
			$sql = "UPDATE ".TB_PREF."sales_order_details
						SET description = ".db_escape($line->item_description) . ", 
								unit_price = ".db_escape($line->price) . ",
								quantity = ".db_escape($line->quantity) . ",
								discount_percent = ".db_escape($line->discount_percent) . ",
								minimum_price = ".db_escape($line->minimum_price) . ",
								order_unit = ".db_escape($line->tran_unit) . ",
								order_unit_qty = ".db_escape($line->tran_qty) . ",
								order_unit_price = ".db_escape($line->tran_unit_price) . ",
                                convertion_factor = ".db_escape($line->convertion_factor) . ",
								minimum_price = ".db_escape($line->minimum_price) . ",
                                list_price = ".db_escape($line->list_price) . "
						WHERE id = {$line->id}";
            db_query($sql, "Order line could not be updated");
		}
		else {
            if(isset($line->kit_parent) && strlen($line->kit_parent) > 0){
                $kit_parent = $line->kit_parent;
            }
            else{
                $kit_parent = '';
            }
        
            $sql = "INSERT INTO ".TB_PREF."sales_order_details (order_no, trans_type, stk_code, 
                                description, unit_price, quantity, 
                                discount_percent, order_unit, order_unit_qty, 
                                convertion_factor, order_unit_price, minimum_price, 
                                list_price, kit_parent) 
                    VALUES (".db_escape($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->tran_unit).", ".db_escape($line->tran_qty).", "
                                .db_escape($line->convertion_factor).", ".db_escape($line->tran_unit_price).", ".db_escape($line->minimum_price).", "
                                .db_escape($line->list_price).", ".db_escape($kit_parent).") ";
            db_query($sql, "Updating order new line could not be inserted");
        }

	} /* inserted line items into sales order details */

	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){
		send_reorder_email($loc, $st_ids, $st_names, $st_num, $st_reorder);
    }
        //return db_num_affected_rows();
    return array("success" => true, "tran_no" => $order_no, "message" => "Sales order $order_no updated");
}

//----------------------------------------------------------------------------------------

function add_sq_so_details($line, $so_line_id, $customer_id, $branch_id)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
    $sql = "INSERT INTO ".TB_PREF."sq_so_details (
                            sq_line_id, so_line_id, debtor_no, 
                            branch_code, stock_id, order_qty, 
                            order_price, discount_percent, order_unit, 
                            order_unit_qty, order_unit_price, order_unit_conversion_factor, 
                            created_by, created_on) 
            VALUES (".db_escape($line->src_id).", ".db_escape($so_line_id).", ".db_escape($customer_id).", "
                            .db_escape($branch_id).", ".db_escape($line->stock_id).", ".db_escape($line->quantity).", "
                            .db_escape($line->price).", ".db_escape($line->discount_percent).", ".db_escape($line->tran_unit).", "
                            .db_escape($line->tran_qty).", ".db_escape($line->tran_unit_price).", ".db_escape($line->convertion_factor).", "
                            .db_escape($created_by).", ".db_escape($created_on).")";

    db_query($sql, "SQ SO details could not be added");    
        
}



//----------------------------------------------------------------------------------------

function get_sales_order_header_record($order_no, $trans_type)
{
	$sql = "SELECT *
                FROM ".TB_PREF."sales_orders sorder 
                WHERE trans_type = " . db_escape($trans_type) ."
                    AND sorder.order_no = " . db_escape($order_no );
        
	$result = db_query($sql, "get_sales_order_header");
	return db_fetch($result);
}

//----------------------------------------------------------------------------------------

function get_sales_order_line_record($line_id)
{
	$sql = "SELECT *
                FROM ".TB_PREF."sales_order_details  
                WHERE id = " . db_escape($line_id );
        
	$result = db_query($sql, "get_sales_order_line_record");
	return db_fetch($result);
}

//----------------------------------------------------------------------------------------

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 , 
                    IF(sorder.tax_group_id = 0, tax_group.id, sorder.tax_group_id)  AS tax_group_id, 
                    cust.tax_id,
                    pm.description project_description 
            FROM ".TB_PREF."sales_orders sorder 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."projects_master pm     
            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 pm.project_id = sorder.project_id 
                    AND sorder.trans_type = " . db_escape($trans_type) ."
                    AND sorder.order_no = " . db_escape($order_no );

	$result = db_query($sql, "order Retreival");

	$num = db_num_rows($result);
	if ($num > 1)
	{
		display_warning("You have duplicate document in database: (type:$trans_type, number:$order_no).");
	}
	else if ($num == 1)
	{
		return db_fetch($result);
	}
	else{
		display_warning("You have missing or invalid sales document in database (type:$trans_type, number:$order_no).");
    }

}

//----------------------------------------------------------------------------------------

function get_sales_order_details($order_no, $trans_type) {
	$sql = "SELECT sod.*,
                sod.qty_sent as qty_done, 
                sm.units,sm.mb_flag,sm.material_cost + sm.labour_cost + sm.overhead_cost AS standard_cost
                FROM ".TB_PREF."sales_order_details sod, "
                        .TB_PREF."stock_master sm
                WHERE sod.stk_code = sm.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");
}

//----------------------------------------------------------------------------------------

function get_sales_order_detail_records($order_no, $trans_type) {
	$sql = "SELECT sod.* 
                FROM ".TB_PREF."sales_order_details sod 
                WHERE order_no =" . db_escape($order_no) ." AND 
                        trans_type = " . db_escape($trans_type) . " 
                ORDER BY id";

	return db_query($sql, "get_sales_order_detail_records");
}

//----------------------------------------------------------------------------------------

function read_sales_order($order_no, &$order, $trans_type)
{

	$myrow = get_sales_order_header($order_no, $trans_type);

	$order->order_no = $order_no;
	
        $order->trans_type = $myrow['trans_type'];
	$order->so_type =  $myrow["type"];
	$order->trans_no = array($order_no=> $myrow["version"]);
        
        $order->project_id =  $myrow["project_id"];
        $order->project_description =  $myrow["project_description"];
        
	$order->set_customer($myrow["debtor_no"], $myrow["name"], $myrow["curr_code"], $myrow["discount"], $myrow["payment_terms"]);

        $tax_group = get_tax_group($myrow["tax_group_id"]);
	$order->set_branch($myrow["branch_code"], $myrow["tax_group_id"], $tax_group["name"], $myrow["contact_phone"]);

	$order->set_sales_type($myrow["sales_type_id"], $myrow["sales_type"], $myrow["tax_included"], $myrow["factor"]); // no default price calculations on edit

	$order->set_location($myrow["from_stk_loc"], $myrow["location_name"]);

	$order->set_delivery($myrow["ship_via"], $myrow["deliver_to"], $myrow["delivery_address"], $myrow["freight_cost"]);
    
        $order->set_order_category($myrow['trans_type'], $order_no);

	$order->cust_ref = $myrow["customer_ref"];
	$order->sales_type =$myrow["order_type"];
        $order->salesman_id =$myrow["salesman_id"];
	$order->reference = $myrow["reference"];
	$order->Comments = $myrow["comments"];
	$order->due_date = sql2date($myrow["delivery_date"]);
	$order->document_date = sql2date($myrow["ord_date"]);
        $order->approval_status = $myrow["approval_status"];
        $order->row_state = $myrow["row_state"];
        $order->sales_order_no = $order_no;

	$result = get_sales_order_details($order_no, $order->trans_type);
	if (db_num_rows($result) > 0)
	{
		$line_no=0;
		while ($linerow = db_fetch($result))
		{
                        $line_no = $linerow["id"];
			$order->add_to_cart($line_no,$linerow["stk_code"],$linerow["quantity"],
				$linerow["unit_price"], $linerow["discount_percent"],$linerow["qty_done"], 
                                $linerow["standard_cost"], $linerow["description"], $linerow["id"],
                                0, 0, $linerow["order_unit"],
                                $linerow["order_unit_qty"], $linerow["convertion_factor"], $linerow["order_unit_price"]);

                        $order->line_items[$line_no]->list_price =  $linerow["list_price"]; 
                        $order->line_items[$line_no]->quantity_ordered =  $linerow["quantity_ordered"]; 
                        $order->line_items[$line_no]->qty_dispatched =  $linerow["qty_dispatched"]; 
                        $order->line_items[$line_no]->row_state = $linerow["row_state"];
//                      $line_no++;
		}
	}

	return true;
}

//----------------------------------------------------------------------------------------

function sales_order_has_deliveries($order_no)
{
	$sql = "SELECT SUM(qty_sent) FROM ".TB_PREF.
	"sales_order_details WHERE order_no=".db_escape($order_no)
	." AND trans_type=".ST_SALESORDER;

	$result = db_query($sql, "could not query for sales order usage");

	$row = db_fetch_row($result);

	if ($row[0] > 0)
		return true;  // 2010-04-21 added check for eventually voided deliveries, Joe Hunt
	$sql = "SELECT order_ FROM ".TB_PREF."debtor_trans WHERE type=".ST_CUSTDELIVERY." AND order_=".db_escape($order_no);
	$result = db_query($sql,"The related delivery notes could not be retreived");
	return (db_num_rows($result) > 0);	
}

//----------------------------------------------------------------------------------------

function close_sales_order($order_no)
{
	// set the quantity of each item to the already sent quantity. this will mark item as closed.
	$sql = "UPDATE ".TB_PREF."sales_order_details
		SET quantity = qty_sent WHERE order_no = ".db_escape($order_no)
		." AND trans_type=".ST_SALESORDER;

	db_query($sql, "The sales order detail record could not be updated");
}

//---------------------------------------------------------------------------------------------------------------

function get_invoice_duedate($terms, $invdate)
{
	if (!is_date($invdate))
	{
		return new_doc_date();
	}
	
	$myrow = get_payment_terms($terms);
	
	if (!$myrow)
		return $invdate;

	if ($myrow['day_in_following_month'] > 0)
		$duedate = add_days(end_month($invdate), $myrow['day_in_following_month']);
	else
		$duedate = add_days($invdate, $myrow['days_before_due']);
	return $duedate;
}

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.sales_type AS salestype,
		  cust.dimension_id,
		  cust.dimension2_id,
		  stype.sales_type,
		  stype.tax_included,
		  stype.factor,
		  cust.curr_code,
		  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,
			-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."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)
		." GROUP by cust.debtor_no";

	$result =db_query($sql,"Customer Record Retreive");
	return 	db_fetch($result);
}

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);

  	    return db_query($sql,"Customer Branch Record Retreive");
}

function get_sql_for_sales_orders_view($selected_customer, $trans_type, $order_no, $filter, 
	$stock_item=null, $from='', $to='', $ref='', $location='', $customer_id=ALL_TEXT)
{

	$sql = "SELECT 
                sorder.order_no,
                sorder.reference,
                cat_trans.order_category_reference,
                debtor.name,
                branch.br_name,"
                .($filter=='InvoiceTemplates' 
                    || $filter=='DeliveryTemplates' ?
                 "sorder.comments, " : "sorder.customer_ref, ")
                ."sorder.ord_date,
                sorder.delivery_date,
                sorder.deliver_to,
                Sum(line.unit_price*line.quantity*(1-line.discount_percent))+freight_cost AS OrderValue,
                sorder.type,
                debtor.curr_code,
                Sum(line.qty_sent) AS TotDelivered,
                Sum(line.quantity) AS TotQuantity,
                sorder.approval_status,
                sorder.closed_for_prs 
                
		FROM ".TB_PREF."sales_orders as sorder 
                INNER JOIN ".TB_PREF."sales_order_details as line ON 
                        sorder.order_no = line.order_no AND sorder.trans_type = line.trans_type 
                INNER JOIN ".TB_PREF."debtors_master as debtor ON 
                        sorder.debtor_no = debtor.debtor_no 
                INNER JOIN ".TB_PREF."cust_branch as branch ON 
                        sorder.branch_code = branch.branch_code AND debtor.debtor_no = branch.debtor_no 
                LEFT JOIN ".TB_PREF."sales_order_category_transactions cat_trans ON 
                        sorder.trans_type = cat_trans.tran_type AND sorder.order_no = cat_trans.tran_type_no 
        WHERE 
                sorder.trans_type = ".db_escape($trans_type);

	if (isset($order_no) && $order_no != "")
	{
		// search orders with number like 
		$number_like = "%".$order_no;
		$sql .= " AND sorder.order_no LIKE ".db_escape($number_like);
//				." GROUP BY sorder.order_no";
	}
	elseif ($ref != "")
	{
		// search orders with reference like 
		$number_like = "%".$ref."%";
		$sql .= " AND sorder.reference LIKE ".db_escape($number_like);
//				." 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($filter == "ApproveOrdersMode"){
            $sql .= " AND sorder.approval_status IN (".AS_NEW.",".AS_WITH_THE_APPROVER.",".AS_AWAITING_APPROVAL.") ";
        }
	}
    
    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 (isset($stock_item))
        $sql .= " AND line.stk_code=".db_escape($stock_item);

    if ($location)
        $sql .= " AND sorder.from_stk_loc = ".db_escape($location);

    if ($filter=='OutstandingOnly')
        $sql .= " AND line.qty_sent < line.quantity AND sorder.approval_status =".db_escape(AS_APPROVED);

    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;
    
    
}

function add_so_pr_detail($wo_material_id, $order_type, $order_quantity, 
                    $order_price, $order_unit, 
                    $order_unit_qty, $order_unit_conversion_factor, 
                    $required_date, $pr_line_id)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
        
        
	$sql = "INSERT INTO ".TB_PREF."sales_order_pr( 
                    so_detail_id, order_type, order_quantity, 
                    order_price, order_unit, 
                    order_unit_qty, order_unit_conversion_factor, 
                    required_date, pr_line_id, 
                    created_by, created_on) 
            VALUES (".db_escape($wo_material_id).", ".db_escape($order_type) . ",".db_escape($order_quantity) . ","
                    .db_escape($order_price) . ", ".db_escape($order_unit) . ", "
                    .db_escape($order_unit_qty) . ", ".db_escape($order_unit_conversion_factor) . ", "
                    .db_escape(date2sql($required_date)) . ", ".db_escape($pr_line_id) . ", "
                    .db_escape($created_by) . ", ".db_escape($created_on).")";

	db_query($sql,"Could not add_wo_pr_detail");
}

function get_sod_item($row_id)
{
        
    $sql = "SELECT *  
            FROM ".TB_PREF."sales_order_details 
            WHERE id = ".$row_id ;
    
    $result = db_query($sql,"Purchase requisition item detail could not be retreived");

    return db_fetch($result);

}

function update_sod_line_ordered_qty($so_line_id, $total_qty_ordered, $close_so_line, $row_state)
{
    $sql = "UPDATE ".TB_PREF."sales_order_details 
            SET quantity_ordered =".db_escape($total_qty_ordered).", 
                line_complete=".db_escape($close_so_line).",
                row_state=". db_new_row_state($row_state)."
            WHERE id = ".db_escape($so_line_id)." 
                AND row_state =". db_escape($row_state);
        
    db_query($sql, "Could not update_sod_line_ordered_qty");
    
    return db_num_affected_rows();
}


function close_sales_order_for_prs($so_id)
{
        $sql = "UPDATE ".TB_PREF."sales_orders SET closed_for_prs = 1 
                WHERE order_no = ". db_escape($so_id);
                
                
	return db_query($sql, "Could not close_sales_order_for_prs");
    
}

function update_sales_order_detail_qty_sent($sod_id, $sod_qty_sent)
{
    $sql = "UPDATE ".TB_PREF."sales_order_details 
            SET qty_sent = ".db_escape($sod_qty_sent)." 
            WHERE id = ". db_escape($sod_id);
                
                
	return db_query($sql, "update_sales_order_detail_qty_sent");
}


//----------------------------------------------------------------------------------------


function add_sales_order_tax_details($so_no, $trans_type, 
                                    $tax_id, $rate, $included, $amount, 
                                    $net_amount, $ex_rate, $tran_date, $memo,
                                    $suspended=0)
{
	$sql = "INSERT INTO ".TB_PREF."sales_order_tax_details 
                        (order_no, trans_type,
                        tran_date, tax_type_id, 
                        rate, ex_rate,
                        included_in_price, net_amount, 
                        amount, memo,
                        suspended)
            VALUES (".db_escape($so_no).",".db_escape($trans_type).","
                        .db_escape(date2sql($tran_date)).",".db_escape($tax_id).","
                        .db_escape($rate).",".db_escape($ex_rate).","
                        .db_escape($included ? 1:0).",".db_escape($net_amount).","
                        .db_escape($amount).",".db_escape($memo).","
                        .db_escape($suspended).")";

	db_query($sql, "add_sales_order_tax_details");

}

//----------------------------------------------------------------------------------------


function delete_sales_order_tax_details($so_no, $trans_type)
{
	$sql = "DELETE FROM ".TB_PREF."sales_order_tax_details 
                WHERE order_no=".db_escape($so_no)." 
                        AND trans_type=".db_escape($trans_type);
	db_query($sql, "Could not delete_po_tax_details");

}

//----------------------------------------------------------------------------------------

function get_sales_order_all_tax_details($order_no, $trans_type)
{
    $sql = "SELECT sot.*, 
                tt.sequence,
                tt.name, 
                tt.name, 
                tt.add_to_price, 
                tt.name tax_type_name,
                tt.sales_gl_code,
                tt.purchasing_gl_code 
            FROM ".TB_PREF."sales_order_tax_details sot 
                JOIN ".TB_PREF."tax_types tt 
                    ON sot.tax_type_id = tt.id 
            WHERE sot.order_no = ".db_escape($order_no)." 
                AND sot.trans_type = ".db_escape($trans_type)." 
            ORDER BY tt.sequence ";
                
    return db_query($sql, "get_sales_order_tax_details");
    
}


//----------------------------------------------------------------------------------------

function get_sales_order_tax_records($order_no, $trans_type)
{
    $sql = "SELECT sot.* 
            FROM ".TB_PREF."sales_order_tax_details sot  
            WHERE sot.suspended = 0 
                AND sot.order_no = ".db_escape($order_no)." 
                AND sot.trans_type = ".db_escape($trans_type);
                
    return db_query($sql, "get_sales_order_tax_records");
    
}

//----------------------------------------------------------------------------------------

function get_sales_order_stax_records($order_no, $trans_type)
{
    $sql = "SELECT sot.* 
            FROM ".TB_PREF."sales_order_tax_details sot  
            WHERE sot.suspended = 1 
                AND sot.order_no = ".db_escape($order_no)." 
                AND sot.trans_type = ".db_escape($trans_type);
                
    return db_query($sql, "get_sales_order_stax_records");
    
}

//----------------------------------------------------------------------------------------

function get_miss_match_sales_order_lines_with_sales_type_and_tax_group($order_no, $type){
    $sql = "SELECT so.order_type so_order_type,
                    sod.order_type sod_order_type,
                    so.tax_group_id so_tax_group_id,
                    sod.tax_group_id sod_tax_group_id,
                    sod.id sod_id,
                    sod.order_no  
                    
            FROM ".TB_PREF."sales_orders so   
                JOIN ".TB_PREF."sales_order_details sod 
                    ON so.order_no = sod.order_no 
                    AND so.trans_type = sod.trans_type  
            WHERE so.order_no = ".db_escape($order_no)." 
                AND sod.order_no = so.order_no  
                AND sod.trans_type = ".db_escape($type)." 
                AND so.trans_type = sod.trans_type";
                
    return db_query($sql, "could not get_miss_match_sales_order_header_and_details");
}


?>
