<?php
/**********************************************************************
***********************************************************************/
//include_once( "../includes/references.inc");
//-------------------------------------------------------------------------------------------------------------

function add_purchase_requisition($pr_cart, $items, $reference, 
                                    $location, $pr_complete, $project_id, 
                                    $pr_date, $release, $source_type, $source_order_id)
{
	global $loc_notification, $path_to_root, $Refs;
    
    $next_approval_group_id = $pr_cart->next_approval_group_id;
    $current_user_id = $_SESSION['wa_current_user']->user;
        
	begin_transaction();
	$args = func_get_args();
	$args = (object)array_combine(array('cart', 'items', 'reference', 
                                        'location', 'pr_complete', 'project_id', 
                                        'pr_date', 'release', 'source_type', 'source_order_id'), $args);
	$args->trans_no = 0;
	hook_db_prewrite($args, ST_PURCH_REQ);
        
        $pr_no = 0;
        $total = $pr_cart->total;
        
        $pr_no = add_pr_header($reference, $location, $pr_complete, 
                                $project_id, $total, $pr_date, 
                                $source_type, $source_order_id);

        if(!$pr_no > 0){
            cancel_transaction();
            return array("success" => false, "tran_no" => $pr_no, "message" => get_message('ANOTHER_USER_HAS_UPDATED'));
        }
        else{
            foreach ($items as $line_item)
            {
                if(!isset($line_item->item_description) || $line_item->item_description == ''){
                    $temp_itm = get_stock_item_edit_info($line_item->stock_id);
                    $line_item->item_description = $temp_itm['description'];
                }
                $pr_line_id = add_pr_item($pr_no, 
                                        $line_item->stock_id, $line_item->quantity, $line_item->units, 
                                        $line_item->price, $line_item->required_date, $line_item->line_comment, 
                                        $line_item->task_id, $line_item->project_site_id, 0, 
                                        $line_item->tran_unit, $line_item->tran_qty, $line_item->convertion_factor,
                                        $line_item->item_description);
                
                
                if($line_item->row_id > 0){// As this is new PR, row_id is the row id of source line
                    // This PR has been generated from another transaction
                    switch ($pr_cart->source_type) {
                        case ST_MANUISSUE:
                            add_wo_pr_detail($line_item->row_id, $line_item->quantity, 
                                                    $line_item->price, $line_item->tran_unit, 
                                                    $line_item->tran_qty, $line_item->convertion_factor, 
                                                    $line_item->required_date, $pr_line_id);
                            update_wo_material_ordered_qty($line_item->row_id, $line_item->quantity);
                            break;
                        case ST_SALESORDER:
                            add_so_pr_detail($line_item->row_id, ST_PURCH_REQ, $line_item->quantity, 
                                                    $line_item->price, $line_item->tran_unit, 
                                                    $line_item->tran_qty, $line_item->convertion_factor, 
                                                    $line_item->required_date, $pr_line_id);
                            
                            $sod_line = get_sod_item($line_item->row_id);
                            $close_sod_line = 0;
                            $total_qty_ordered = $sod_line['quantity_ordered'] + $line_item->quantity;
                            if($total_qty_ordered >= $sod_line['quantity']){
                                $close_sod_line  = 1;
                            }
                            $num_rows = update_sod_line_ordered_qty($line_item->row_id, $total_qty_ordered, $close_sod_line, $line_item->row_state);
                            if($num_rows != 1){
                                cancel_transaction();
                                return array("success" => false, "tran_no" => $pr_no, "message" => "Another user has updated Sales Order item [". $line_item->stock_id."].");
                            }
                            break;
                        case ST_MATERIAL_REQ;
                            add_mr_pr_detail($line_item->row_id, $line_item->quantity, 
                                                    $line_item->price, $line_item->tran_unit, 
                                                    $line_item->tran_qty, $line_item->convertion_factor, 
                                                    $line_item->required_date, $pr_line_id);
                            $umioq = update_mr_item_ordered_qty($line_item->row_id, $line_item->quantity);
                            if($umioq != 1){
                                cancel_transaction();
                                return array("success" => false, "tran_no" => $pr_no, "message" => "Another user has updated Material REquisition item [". $line_item->stock_id."].");
                            }
                            break;
                        default:
                            break;
                    }
                    

                    
                }
            }
        }

        $Refs->save(ST_PURCH_REQ, $pr_no, $reference);
        
        $date_ = sql2date(date('y-m-d'));
        add_audit_trail(ST_PURCH_REQ, $pr_no, $date_);

        $args->trans_no = $pr_no;


        hook_db_postwrite($args, ST_PURCH_REQ);

        if(!$release){
            commit_transaction();
            return array("success" => true, "tran_no" => $pr_no, "message" => "Purchase requisition $pr_no has been added");
        }
        else{
            if($pr_complete == 1){
                $tasks_summary = $total;
            }
            else{
                $tasks_summary = calculate_pr_task_remaining_totals($items);
            }
            
            $info = approve_document(ST_PURCH_REQ, $pr_no, $project_id, $tasks_summary, 
                                        $next_approval_group_id, $current_user_id, 0);

            if($info['success'] !== true){
                cancel_transaction();
                return array("success" => $info['success'], "message" => $info['msg']);
            }

            if($pr_complete == 0){
                if($info['approval_status'] == AS_APPROVED){            
                    $apc_msg = allocate_to_tasks(ST_PURCH_REQ, $pr_no, 
                                                $tasks_summary, $current_user_id);
                    if($apc_msg['success'] !== true)
                    {
                        cancel_transaction();
                        return array("success" => $apc_msg['success'], "tran_no" => $pr_no, "message" => $info['message']);
                    }
                }
            }
            commit_transaction();
            return array("success" => true, "tran_no" => $pr_no, "message" => $info['message']);
        }
}
//----------------------------------------------------------------------------

function update_purchase_requisition($pr_no, $pr_cart, $reference, 
                                    $location, $pr_complete, $project_id, 
                                    $pr_date, $release, $row_state)
{
	global $loc_notification, $path_to_root, $Refs;
        
    $info = "";
    $current_user_id = $_SESSION['wa_current_user']->user;
    $items = $pr_cart->line_items;
    $next_approval_group_id = $pr_cart->next_approval_group_id;
    $updated_by = $_SESSION['wa_current_user']->user;

    $total = $pr_cart->total;
    
	begin_transaction();
	$args = func_get_args();
	$args = (object)array_combine(array('pr_no', 'pt_cart', 'reference', 
                                        'location','pr_complete', 'project_id', 
                                        'pr_date', 'release', 'row_state'), $args);
	$args->trans_no = 0;
	hook_db_prewrite($args, ST_PURCH_REQ);

    $upr_msg = update_pr_header($pr_no, $reference, $location, $pr_complete, $project_id, $total, $row_state);
    if($upr_msg["success"] !==  true){
        return array("success" => false, "tran_no" => $pr_no, "message" => $upr_msg['message']);
    }

    delete_pr_items($pr_no);

    foreach ($items as $line_item)
    {
            recreate_pr_item($pr_no, $line_item, $updated_by, 0);
    }

    add_audit_trail(ST_PURCH_REQ, $pr_no, $pr_date);
    $args->trans_no = $pr_no;

    hook_db_postwrite($args, ST_PURCH_REQ);
	
    
    if(!$release){
        commit_transaction();
        return array("success" => true, "tran_no"=>$pr_no, "message" => "Purchase requisition $pr_no has been updated");
    }
    else{
        if($pr_complete == 1){
            $tasks_summary = $total;
        }
        else{
            $tasks_summary = calculate_pr_task_remaining_totals($items);
        }      
        
        $info = approve_document(ST_PURCH_REQ, $pr_no, $project_id, $tasks_summary, 
                                    $next_approval_group_id, $current_user_id, db_new_row_state($row_state));

        if($info['success'] !== true){
            cancel_transaction();
            return array("success" => $info['success'], "message" => $info['message']);
        }

        if($pr_complete == 0){
            if($info['approval_status'] == AS_APPROVED){            
                $apc_msg = allocate_to_tasks(ST_PURCH_REQ, $pr_no, 
                                            $tasks_summary, $current_user_id);
                if($apc_msg['success'] !== true)
                {
                    cancel_transaction();
                    return array("success" => $apc_msg['success'], "tran_no" => $pr_no, "message" => $info['message']);
                }
            }
        }
        commit_transaction();
        return array("success" => true, "tran_no" => $pr_no, "message" => $info['message']);
    }
    
    
        
}

function calculate_pr_task_remaining_totals($line_items)
{
        $task_costs = array();
        
        foreach ($line_items as $line_item)
        {
//            $line_remaining_total = $line_item->requested_qty* $line_item->estimate_price;
//            (requested_unit_qty - (issued_qty * req_unit_conv_factor))* estimate_price
            $remaining_qty = $line_item->tran_qty - ($line_item->issued_qty /  $line_item->convertion_factor);
            if($remaining_qty < 0){
                $remaining_qty = 0;
            }
            $line_remaining_total = $remaining_qty * $line_item->estimate_price;
            add_to_task_cost($task_costs, $line_item->task_id, $line_remaining_total, $line_remaining_total, $line_remaining_total);
            
        }
        
        return $task_costs;
}


function close_pr($pr_no, $error_msg = "")
{
    read_pr($pr_no, $pr_cart);
    
    $current_user_id = $_SESSION['wa_current_user']->user;
    
    begin_transaction();
    if($pr_cart->approval_status == AS_APPROVED || $pr_cart->approval_status == AS_ISSUED){
        $can_complete_pr = false;
        $tasks_summary = calculate_pr_task_remaining_totals($pr_cart->line_items);
        get_task_cost_details($tasks_summary);
        $rta_msg = remove_task_allocations(ST_PURCH_REQ, $pr_no, 
                                $tasks_summary, $current_user_id);
        if($rta_msg['success'] !== true){
            cancel_transaction();
            return $rta_msg;
        }
        else{
            $can_complete_pr = true;
        }
    }
    else{
        $can_complete_pr = true;
    }
    
    if($can_complete_pr){
        $info['approval_status'] = AS_COMPLETED;
        $info['tran_no'] = $pr_no;
        $info['row_state'] = $pr_cart->row_state;
        $info['current_user_id'] = $_SESSION['wa_current_user']->user;
        $info['tran_type'] = ST_PURCH_REQ;
        $info['change_type'] = '';

        update_document_status($info);
        if($info['success'] === true ){
            commit_transaction();
            return array("success" => true, "message" => " PR $pr_no has been closed.");
        }
        else{
            cancel_transaction();
            return array("success" => false, "message" => $info['message']);
        }
    }

    cancel_transaction();
	return array("success" => false, "message" => "Could not close PR $pr_no.");
}

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

function add_pr_header($reference, $location, $pr_complete, 
                            $project_id, $total, $pr_date,
                             $source_type, $source_order_id)

{
        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date('Y-m-d H:i:s');
        $next_approval_group_id = 0;
        $pr_date = date2sql($pr_date);
        
        $sql = "INSERT INTO ".TB_PREF."purchase_requisitions (reference, location, 
                                                project_id, pr_complete, 
                                                total, next_approval_group_id,
                                                pr_date, 
                                                source_type, source_order_id,
                                                created_by, created_on) 
                VALUES (".db_escape($reference).", ".db_escape($location).", "
                                .db_escape($project_id).", ".db_escape($pr_complete).", "
                                .db_escape($total).", ".db_escape($next_approval_group_id).", "
                                .db_escape($pr_date).", "
                                .db_escape($source_type).", ".db_escape($source_order_id).", "
                                .db_escape($created_by).", ".db_escape($created_on).")";

        $error_msg = "The material requisition record cannot be inserted";

	db_query($sql, $error_msg);

	return db_insert_id();
        
}

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

function add_pr_item($pr_no,  
                        $stock_id, $quantity, $units, 
                        $price, $required_date, $line_comment, 
                        $task_id, $project_site_id, $pr_complete, 
                        $tran_unit, $tran_qty, $convertion_factor,
                        $item_description)
{
        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date('Y-m-d H:i:s');
        
        $sql = "INSERT INTO ".TB_PREF."purchase_requisition_details(requisition_id, 
                        item_code, item_pr_description,
                        quantity, units, estimate_price, 
                        required_date, line_comment, 
                        task_id, project_site_id,
                        line_complete, requested_unit, 
                        requested_unit_qty, req_unit_conv_factor, 
                        created_by, created_on) 
                VALUES (".db_escape($pr_no).", "
                        .db_escape($stock_id).", ".db_escape($item_description).", "
                        .db_escape($quantity).", ".db_escape($units).", ".db_escape($price).", "
                        .db_escape(date2sql($required_date)).", ".db_escape($line_comment).", "
                        .db_escape($task_id).", ".db_escape($project_site_id).", "
                        .db_escape($pr_complete).", ".db_escape($tran_unit).", "
                        .db_escape($tran_qty).", ".db_escape($convertion_factor).", "
                        .db_escape($created_by).", ".db_escape($created_on).")";
                
	db_query($sql, "could not add purchase requisition item");
    
    return db_insert_id();
}

function update_requisition_detail($selected_id, $item_code, $purpose, $order_quantity, $estimate_price)
{
	$sql = "UPDATE ".TB_PREF."requisition_details SET item_code=".db_escape($item_code)
			.", purpose=".db_escape($purpose)
			.", order_quantity=".db_escape($order_quantity)
			.", estimate_price=".db_escape($estimate_price)
			.", quantity=".db_escape($order_quantity)
			.", price=".db_escape($estimate_price)
			."WHERE requisition_detail_id=".db_escape($selected_id);

	db_query($sql, "could not update requisition detail");
}

function update_requisition_allocation($pr_po_line_id, $pr_line_id, $order_quantity, $order_price, 
                                        $order_unit, $order_unit_qty, $order_unit_price, $order_unit_conversion_factor,
                                        $order_unit_price_discount, $supplier_id, $line_loc_code, $delivery_required_date, 
                                        $minimum_price, $minimum_price_discount, $min_price_supp_id,
                                        $item_description, $project_id, $project_site_id, $row_state)
        
{
    begin_transaction();
    $num_rows = update_requisition_allocation_line($pr_po_line_id, $order_quantity, $order_price, 
                                        $order_unit, $order_unit_qty, $order_unit_price, $order_unit_conversion_factor,
                                        $order_unit_price_discount, $supplier_id, $line_loc_code, $delivery_required_date, 
                                        $minimum_price, $minimum_price_discount, $min_price_supp_id, 
                                        $item_description, $project_id, $project_site_id, $row_state);
    
    if($num_rows !== 1){
        cancel_transaction();
        return array("success" => false, "message" => "Could not update requisition allocation detail.");
    }
    
    $uploq_msg = update_pr_line_order_quantity($pr_line_id);
    if($uploq_msg["success"] !== true){
        cancel_transaction();
        return $uploq_msg;
    }
    
    commit_transaction();
    return array("success" => true, "message" => "PR Allocation detail has been updated");
}


function update_requisition_allocation_line($pr_po_line_id, $order_quantity, $order_price, 
                                        $order_unit, $order_unit_qty, $order_unit_price,
                                        $order_unit_conversion_factor, $order_unit_price_discount, 
                                        $supplier_id, $line_loc_code, $required_date, 
                                        $minimum_price, $minimum_price_discount, $min_price_supp_id, 
                                        $item_description, $project_id, $project_site_id, $row_state)
{
        $updated_by = $_SESSION['wa_current_user']->user;
        $updated_on = date('Y-m-d H:i:s');
        
        $sql = "UPDATE ".TB_PREF."pr_po_details 
                SET item_description=".db_escape($item_description).", 
                    order_quantity=".db_escape($order_quantity).", 
                    order_price=".db_escape($order_price).", 
                    order_unit=".db_escape($order_unit).", 
                    order_unit_qty=".db_escape($order_unit_qty).", 
                    order_unit_price=".db_escape($order_unit_price).", 
                    order_unit_conversion_factor=".db_escape($order_unit_conversion_factor).", 
                    order_unit_price_discount=".db_escape($order_unit_price_discount).", 
                    supplier_id=".db_escape($supplier_id).", 
                    line_loc_code=".db_escape($line_loc_code).", 
                    required_date=".db_escape(date2sql($required_date)).", 
                    minimum_price=".db_escape($minimum_price).", 
                    minimum_price_discount=".db_escape($minimum_price_discount).", 
                    min_price_supp_id=".db_escape($min_price_supp_id).",
                    project_id=".db_escape($project_id).",
                    project_site_id=".db_escape($project_site_id).",
                    updated_by=".db_escape($updated_by).",
                    updated_on=". db_escape($updated_on).",
                    row_state=". db_new_row_state($row_state)." 
                WHERE id=".db_escape($pr_po_line_id)." AND 
                    row_state = ".db_escape($row_state);
        
	db_query($sql, "could not update requisition allocation detail");
    
    return db_num_affected_rows();
}

function update_pr_line($pr_line_id, $total_qty_ordered, $close_pr_line, $row_state, $closed_for_indents = 0)
{
    $sql = "UPDATE ".TB_PREF."purchase_requisition_details 
            SET quantity_ordered =".db_escape($total_qty_ordered).", 
                line_complete=".db_escape($close_pr_line).",   
                closed_for_indents=".db_escape($closed_for_indents).",   
                row_state=". db_new_row_state($row_state)."   
            WHERE id = ".db_escape($pr_line_id)." AND 
                row_state = ".db_escape($row_state);
        
	db_query($sql, "could not update requisition line");
    
    return db_num_affected_rows();
}

function update_pr_line_order_quantity($pr_line_id, $deleting = false)
{
    $result = get_pr_line_requisition_allocations($pr_line_id);
    $total_qty = 0;
    while ($myrow = db_fetch($result)){
        $total_qty += $myrow['order_quantity'];
    }
    
    
    $pr_line = get_pr_item($pr_line_id);
    $closed_for_indents = $pr_line['closed_for_indents'];
    $close_pr_line  = 0;
//    $total_qty_ordered = $total_qty + $order_quantity;
    $total_qty_ordered = $total_qty;
    
        
    if($total_qty_ordered >= $pr_line['quantity']){
        $close_pr_line  = 1;
        $closed_for_indents = 1;
    }
    if($deleting){// In case of reopned PR line, PR line should not be closed
        if($pr_line['line_complete'] == 0){
            $close_pr_line = 0;
            $closed_for_indents = $pr_line['closed_for_indents'];
        }
    }
    
    $num_rows = update_pr_line($pr_line_id, $total_qty_ordered, $close_pr_line, $pr_line['row_state'], $closed_for_indents);
    if($num_rows !== 1){
        cancel_transaction();
        return array("success" => false, "message" => "Another user has updated purchase requisition line $pr_line_id.");
    }
    
    return array("success" => true, "message" => "");
}

function get_pr_edit_info($pr_no)
{
        
    $sql = " SELECT * FROM ".TB_PREF."purchase_requisitions 
                WHERE pr_no = ".$pr_no ;
    
    $result = db_query($sql,"Purchase requisition could not be retreived");

    return db_fetch($result);

}

function add_requisition_allocation($pr_line_id, $order_quantity,$order_price, 
                                        $order_unit, $order_unit_qty, $order_unit_price, $order_unit_conversion_factor,
                                        $order_unit_price_discount, $supplier_id, $line_loc_code, 
                                        $delivery_required_date, $minimum_price, $minimum_price_discount, 
                                        $min_price_supp_id, $item_code, $pr_po_item_description,
                                        $project_id, $project_site_id)
{
    begin_transaction();
    $ra_line_id = add_requisition_allocation_line($pr_line_id, $order_quantity,$order_price, 
                                        $order_unit, $order_unit_qty, $order_unit_price, $order_unit_conversion_factor,
                                        $order_unit_price_discount, $supplier_id, $line_loc_code, 
                                        $delivery_required_date, $minimum_price, $minimum_price_discount, 
                                        $min_price_supp_id, $item_code, $pr_po_item_description,
                                        $project_id, $project_site_id);
    
    if($ra_line_id <= 0){
        cancel_transaction();
        return array("success" => false, "message" => "Could not add requisition allocation detail.");
    }
    
    $uploq_msg = update_pr_line_order_quantity($pr_line_id);
    if($uploq_msg["success"] !== true){
        cancel_transaction();
        return $uploq_msg;
    }
    
    commit_transaction();
    return array("success" => true, "message" => "New PR Allocation detail has been added.");
}



function get_pr_line_requisition_allocations($pr_line_id)
{
    $sql = "SELECT * FROM ".TB_PREF."pr_po_details WHERE pr_line_id = ".$pr_line_id;
    
    return db_query($sql, "Could not get pr line requisition allocations.");
    
}


function get_pr_line_ordered_qty($pr_line_id)
{
    $sql = "SELECT SUM(order_quantity) total_qty_ordered FROM ".TB_PREF."pr_po_details WHERE pr_line_id = ".$pr_line_id;
    
    $result = db_query($sql, "Could not get_pr_line_ordered_qty.");
    
    return db_fetch($result);
    
}


function add_requisition_allocation_line($selected_id, $order_quantity,$order_price, 
                                        $order_unit, $order_unit_qty, $order_unit_price, $order_unit_conversion_factor,
                                        $order_unit_price_discount, $supplier_id, $line_loc_code, 
                                        $required_date, $minimum_price, $minimum_price_discount, 
                                        $min_price_supp_id, $item_code, $pr_po_item_description,
                                        $project_id, $project_site_id)

{
        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date('Y-m-d H:i:s');
        
        $sql = "INSERT INTO ".TB_PREF."pr_po_details("
                            . "pr_line_id, order_quantity, "
                            . "item_code, item_description, "
                            . "order_price, supplier_id, line_loc_code, "
                            . "order_unit, order_unit_qty, "
                            . "order_unit_price, order_unit_conversion_factor, order_unit_price_discount, "
                            . "minimum_price, minimum_price_discount, "
                            . "min_price_supp_id, required_date, "
                            . "project_id, project_site_id, "
                            . "created_by, created_on) "
                ." values(" .db_escape($selected_id).",".db_escape($order_quantity).","
                            .db_escape($item_code).",".db_escape($pr_po_item_description).","
                            .db_escape($order_price).",".db_escape($supplier_id).",".db_escape($line_loc_code).","
                            .db_escape($order_unit).",".db_escape($order_unit_qty).","
                            .db_escape($order_unit_price).",".db_escape($order_unit_conversion_factor).",".db_escape($order_unit_price_discount).","
                            .db_escape($minimum_price).",".db_escape($minimum_price_discount).","
                            .db_escape($min_price_supp_id).",".db_escape(date2sql($required_date)).","
                            .db_escape($project_id).",".db_escape($project_site_id).","
                            .db_escape($created_by).",".db_escape($created_on).")";

	db_query($sql, "Could not add_requisition_allocation_line");
    
    return db_insert_id();
        
}


function delete_requisition_allocation_line($pr_po_line_id, $deleting = false)
{
    $pr_po_line = get_pr_allocation_record($pr_po_line_id);
    
    $sql = "DELETE FROM ".TB_PREF."pr_po_details 
            WHERE id = ".$pr_po_line_id." AND po_line_id = 0";
    
    db_query($sql, "Could not delete requisition allocation detail");
    
    $n = db_num_affected_rows();
    if(!($n > 0)){
        return array("success" => false, "message" => "PR Allocation detail could not be deleted.");
    }

    $pr_line_id = $pr_po_line['pr_line_id'];
    $uprlqtymsg = update_pr_line_order_quantity($pr_line_id, $deleting);
    if($uprlqtymsg['success'] !== true){
        return $uprlqtymsg;
    }
        
//    $roprl_msg = reopen_pr_line($pr_po_line['pr_line_id']);
//    if($roprl_msg['success'] !== true){
//        return $roprl_msg;
//    }

    return array("success" => true, "message" => "PR Allocation detail has been deleted.");
    
}

function reopen_pr_line($pr_line_id)
{
        $completed = $closed_for_indents = 0;
        
        $sql = "UPDATE ".TB_PREF."purchase_requisition_details 
                    SET line_complete = ".db_escape($completed).",
                        closed_for_indents = ".db_escape($closed_for_indents)." 
                    WHERE id=".db_escape($pr_line_id);

	db_query($sql, "Could not reopen_pr_line");
    
    return array("success" => true, "message" => "Purchase Requisition line has been reopned.");
}


function complete_pr_line($pr_line_id)
{
        $completed = 1;

        
        $sql = "UPDATE ".TB_PREF."purchase_requisition_details "
                    . " SET line_complete = ".db_escape($completed) 
                    ." WHERE id=".db_escape($pr_line_id);

	db_query($sql, "could not complete_pr_line");
}


function get_all_requisition_details($requisitionid)
{
	$sql = "SELECT * 
            FROM ".TB_PREF."item_codes ic 
                    INNER JOIN ".TB_PREF."requisition_details rd ON 
                            ic.item_code = rd.item_code
                    LEFT JOIN ".TB_PREF."requisitions req ON 
                            req.requisition_id=rd.requisition_id
            WHERE (req.completed=0 AND rd.requisition_id = ".db_escape($requisitionid).")";

	return db_query($sql, "could not get all requisition_details");
}

function get_pr_allocation_record($pr_po_id)
{
	$sql = "SELECT * 
            FROM ".TB_PREF."pr_po_details 
            WHERE id = ".db_escape($pr_po_id);

	$result = db_query($sql, "get_pr_allocation_record");
    return db_fetch($result);
}

function get_open_requisition_details($department_id=0, $project_id=0, $requester_id=0, $pr_no = 0)
{
        $sql = "SELECT  
                    pr.pr_no, 
                    pr.location pr_loc_code, 
                    pr.created_on,
                    pr.project_id,
                    prd.task_id,
                    ic.item_code, 
                    ic.description, 
                    ppd.order_quantity,
                    ppd.supplier_id,
                    ppd.line_loc_code,
                    ppd.order_unit,
                    ppd.order_unit_qty,
                    ppd.order_unit_price,
                    ppd.order_unit_price_discount,
                    ppd.id pr_po_line_id,
                    prd.id pr_line_id,
                    prd.line_comment, 
                    prd.quantity, 
                    prd.units,
                    prd.estimate_price, 
                    prd.required_date, 
                    pm.description project_description,
                    prd.requested_unit,
                    prd.requested_unit_qty,
                    prd.item_pr_description,
                    prd.req_unit_conv_factor,
                    sup.supp_name supplier_name,
                    ppd.item_description pr_po_item_description,
                    ppd.po_line_id,
                    origi.real_name originator_name,
                    p_offic.real_name created_pofficer_name,
                    p_offiu.real_name updated_pofficer_name,
                    pod.delivery_date po_line_delivery_date  
                FROM 
                    ".TB_PREF."item_codes ic 
                    INNER JOIN ".TB_PREF."purchase_requisition_details prd ON ic.item_code = prd.item_code
                    INNER JOIN ".TB_PREF."purchase_requisitions pr ON prd.requisition_id = pr.pr_no
                    INNER JOIN ".TB_PREF."projects_master pm ON pr.project_id = pm.project_id 
                    INNER JOIN ".TB_PREF."users origi ON pr.created_by = origi.id   
                    LEFT JOIN ".TB_PREF."pr_po_details ppd ON prd.id = ppd.pr_line_id 
                    LEFT JOIN ".TB_PREF."suppliers sup ON ppd.supplier_id = sup.supplier_id  
                    LEFT JOIN ".TB_PREF."users p_offic ON ppd.created_by = p_offic.id
                    LEFT JOIN ".TB_PREF."users p_offiu ON ppd.updated_by = p_offiu.id 
                    LEFT JOIN ".TB_PREF."purch_order_details pod ON ppd.po_line_id = pod.po_detail_item  
                WHERE  ((prd.line_complete = 0) OR  
                        (prd.line_complete = 1 AND ppd.po_line_id = 0)) AND 
                        (pr.approval_status = ".AS_APPROVED." OR pr.approval_status = ".AS_ISSUED." OR pr.approval_status = ".AS_COMPLETED.") ";
        
        if($pr_no > 0){
            $sql .= " AND pr.pr_no = ".db_escape($pr_no);
        }
        else{
            if($department_id > 0 || $project_id > 0 || $requester_id > 0){
                if($department_id > 0){
                    $sql .= " AND pm.department_id = ".db_escape($department_id);
                }
                if($project_id > 0){
                    $sql .= " AND pr.project_id = ".db_escape($project_id);
                }
                if($requester_id > 0){
                    $sql .= " AND pr.created_by = ".db_escape($requester_id);
                }
            }
            else{
                $sql .= " AND pr.project_id = ".db_escape("dddddddddddddddddd");
            }
        }
            
        $sql .= " ORDER BY pr.pr_no, prd.id";
                
                                   
	return db_query($sql, "could not get all requisition_details");
}

function get_requisition_detail($update_type, $selected_id)
{
	$sql = "SELECT 
                    pr.pr_no,
                    prd.id pr_line_id,
                    prd.item_code , 
                    prd.quantity requested_quantity,
                    prd.quantity_ordered,
                    pr.project_id,
                    pr.location pr_loc_code,
                    prl.location_name pr_location_name,
                    sm.units base_unit, 
                    sm.description item_description,
                    sm.editable editable_descri,  
                    prd.estimate_price, 
                    pm.description project_description,
                    td.value_name task_description,
                    sm.description, 
                    prd.item_pr_description, 
                    prd.line_comment, 
                    prd.requested_unit,
                    prd.requested_unit_qty,
                    prd.required_date,
                    prd.project_site_id,
                    ps.description project_site_name,
                    ru.convertion_factor req_unit_conversion_factor,
                    u.name requested_unit_name,  
                    ppd.item_code pr_po_item_code,
                    ppd.item_description pr_po_item_description,
                    ppd.supplier_id ,
                    ppd.order_quantity,
                    ppd.order_unit,
                    ppd.order_unit_qty,
                    ppd.order_unit_price,
                    ppd.order_unit_price_discount,
                    ou.convertion_factor order_unit_conversion_factor,
                    ppd.required_date order_line_required_date,
                    ppd.id pr_po_line_id,
                    ppd.line_loc_code,
                    ppd.project_site_id pr_po_project_site_id,
                    ppd.row_state 
                FROM ".TB_PREF."purchase_requisition_details prd                     
                    INNER JOIN ".TB_PREF."purchase_requisitions pr ON 
                        prd.requisition_id = pr.pr_no 
                    INNER JOIN ".TB_PREF."projects_master pm ON 
                        pr.project_id = pm.project_id 
                    INNER JOIN ".TB_PREF."stock_master sm ON 
                        prd.item_code = sm.stock_id   
                    INNER JOIN ".TB_PREF."item_units u ON 
                        prd.requested_unit = u.abbr   
                    INNER JOIN ".TB_PREF."alternate_units ru ON 
                        ru.stock_id = sm.stock_id AND 
                        prd.requested_unit = ru.alternate_unit 
                    LEFT JOIN ".TB_PREF."pr_po_details ppd ON 
                        prd.id = ppd.pr_line_id 
                    LEFT JOIN ".TB_PREF."locations prl ON 
                        pr.location = prl.loc_code 
                    LEFT JOIN ".TB_PREF."project_tasks pt ON 
                        prd.task_id = pt.task_id  
                    LEFT JOIN ".TB_PREF."list_values td ON 
                        pt.type_id = td.id  
                    LEFT JOIN ".TB_PREF."alternate_units ou ON 
                        ou.stock_id = sm.stock_id AND 
                        ppd.order_unit = ou.alternate_unit 
                    LEFT JOIN ".TB_PREF."project_sites ps ON 
                        prd.project_site_id = ps.id 
                WHERE ";
    
    if($update_type == "update_pr_po_line"){
        $sql .= "ppd.id=".db_escape($selected_id);
    }
    else{
        $sql .= "prd.id=".db_escape($selected_id);
    }

	$result = db_query($sql, "could not get requisition detail");

	return db_fetch($result);
}

function delete_requisition_detail($selected_id)
{
	$sql="DELETE FROM ".TB_PREF."requisition_details WHERE requisition_detail_id=".db_escape($selected_id);

	db_query($sql, "could not delete requisition detail");
}





function get_pr_header($pr_no)
{
	$sql = "SELECT u.real_name requestor_name,
                        pr.*,
                        l.location_name issuing_location_name,
                        pm.description project_description,
                        ap.approval_path_name approval_path_description,
                        pr.pr_date pr_required_date
            FROM ".TB_PREF."purchase_requisitions pr 
                    INNER JOIN ".TB_PREF."users u ON 
                            pr.created_by = u.id 
                    INNER JOIN ".TB_PREF."projects_master pm ON 
                            pr.project_id = pm.project_id  
                    INNER JOIN ".TB_PREF."locations l ON 
                            pr.location = l.loc_code 
                    LEFT JOIN ".TB_PREF."project_approval_paths pap ON 
                            pap.project_id = pr.project_id AND pap.approval_path_type = 'PR'  
                    LEFT JOIN ".TB_PREF."approval_path ap ON 
                            pap.approval_path_id = ap.approval_path_id 
                    
            WHERE pr_no = ".db_escape($pr_no);

	$result = db_query($sql, "could not get requisition header");

	return db_fetch($result);
}
function get_pr_details($pr_no, $po_no=0)
{
	if($po_no > 0){
        $sql = "SELECT prd.*,
                IF(prd.item_pr_description = '',sm.description , prd.item_pr_description ) description, 
                sm.stock_control_method,
                sm.material_cost + sm.labour_cost + sm.overhead_cost AS standard_cost,
                sm.mb_flag,
                sm.editable descr_editable, 
                pt.description task_description,
                ps.description project_site_description,
                
                pod.quantity_ordered po_ordered_qty,
                pod.quantity_received  po_received_qty,
                pod.po_detail_item po_detail_id,
                prd.requisition_id,
                pr_po.id pr_po_line_id,              
                pod.unit_price  

            FROM ".TB_PREF."purchase_requisition_details prd 
                INNER JOIN ".TB_PREF."pr_po_details pr_po ON 
                        prd.id = pr_po.pr_line_id                     
                INNER JOIN ".TB_PREF."purch_order_details pod ON 
                        pr_po.po_line_id = pod.po_detail_item      
                INNER JOIN ".TB_PREF."stock_master sm ON 
                        prd.item_code = sm.stock_id        
                LEFT JOIN ".TB_PREF."project_tasks pt ON 
                        prd.task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."project_sites ps ON 
                        prd.project_site_id = ps.id                 
            WHERE 
                prd.requisition_id = ".db_escape($pr_no)." AND 
                pod.order_no = ".db_escape($po_no);
    }
    else{
        $sql = "SELECT prd.*,
                IF(prd.item_pr_description = '',sm.description , prd.item_pr_description ) description, 
                sm.stock_control_method,
                sm.material_cost + sm.labour_cost + sm.overhead_cost AS standard_cost,
                sm.mb_flag,
                sm.editable descr_editable, 
                pt.description task_description,
                ps.description project_site_description,
                
                0 po_ordered_qty,
                0 po_received_qty,
                0 po_detail_id,
                0 requisition_id,
                0 pr_po_line_id,
                0 unit_price  
                 
            FROM ".TB_PREF."purchase_requisition_details prd 
                INNER JOIN ".TB_PREF."purchase_requisitions pr ON 
                        prd.requisition_id = pr.pr_no   
                INNER JOIN ".TB_PREF."stock_master sm ON 
                        prd.item_code = sm.stock_id        
                LEFT JOIN ".TB_PREF."project_tasks pt ON 
                        prd.task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."project_sites ps ON 
                        prd.project_site_id = ps.id                 
            WHERE 
                prd.requisition_id = ".db_escape($pr_no);
    }
    
	return db_query($sql, "could not get requisition details");

}

function update_pr_header($pr_no, $reference, $location, $pr_complete, $project_id, $total, $row_state)
        //$reference, $location, $pr_complete, $project_id, $total, $pr_date
{
        $updated_by = $_SESSION['wa_current_user']->user;
        $updated_on = date('Y-m-d H:i:s');

	$sql = "UPDATE ".TB_PREF."purchase_requisitions 
                SET  
                    location = ".db_escape($location).", 
                    total = ".db_escape($total).", 
                    pr_complete = ".db_escape($pr_complete).",  
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on).",  
                    project_id = ".db_escape($project_id).", 
                    row_state = ". db_new_row_state($row_state)."       
                WHERE pr_no = ".$pr_no." AND row_state = ".$row_state;

		$error_msg = "Could not update purchase requisition record";

	db_query($sql, $error_msg);

	if(db_num_affected_rows() == 1){
        return array("success" => true, "message" => "");
    }
    else{
        return array("success" => false, "message" => "Another user has updated PR $pr_no");
    }
}

function delete_pr_items($pr_no, $error_msg = "")
{
    $sql = " DELETE FROM ".TB_PREF."purchase_requisition_details 
            WHERE requisition_id = ".$pr_no." AND issued_qty = 0 AND quantity_ordered = 0";
    
    $sql = " DELETE FROM ".TB_PREF."purchase_requisition_details 
            WHERE requisition_id = ".$pr_no;
    
    
    	if ($error_msg == "")
		$error_msg = "The purchase requisition items could not be removed";

	db_query($sql, $error_msg);
}

function recreate_pr_item($pr_no, $li, $updated_by, $pr_complete)
{
    
        if(!isset($li->item_description) || $li->item_description == ''){
            $temp_itm = get_stock_item_edit_info($li->stock_id);
            $li->item_description = $temp_itm['description'];
        }
        
        $required_date = date2sql($li->required_date);
        $updated_on = date('Y-m-d H:i:s');
        
        if($li->created_by)
            $created_by = $li->created_by;
        else {
            $created_by = $updated_by;
        }
        
        if($li->created_on)
            $created_on = $li->created_on;
        else {
            $created_on = $updated_on;
        }
        
        //$$created_on = date2sql($created_on);
        
	$sql = "INSERT INTO ".TB_PREF."purchase_requisition_details(
                        id, requisition_id, 
                        item_code, item_pr_description, quantity, 
                        units, estimate_price,
                        required_date, line_comment, 
                        task_id, project_site_id,
                        line_complete,
                        requested_unit, 
                        requested_unit_qty, req_unit_conv_factor, 
                        created_by, created_on, 
                        updated_by, updated_on) 
                VALUES (".db_escape($li->row_id).", ".db_escape($pr_no).", "
                        .db_escape($li->stock_id).", ".db_escape($li->item_description).", ".db_escape($li->requested_qty).", "
                        .db_escape($li->units).", ".db_escape($li->estimate_price).", "
                        .db_escape(date2sql($li->required_date)).", ".db_escape($li->line_comment).", "
                        .db_escape($li->task_id).", ".db_escape($li->project_site_id).", "
                        .db_escape($pr_complete).", "
                        .db_escape($li->tran_unit).", "
                        .db_escape($li->tran_qty).", ".db_escape($li->convertion_factor).", "
                        .db_escape($created_by).", ".db_escape($created_on).", "
                        .db_escape($updated_by).", ".db_escape($updated_on).")";
                

    $error_msg = "The material transfer requisition record cannot be inserted";

	db_query($sql, $error_msg);

}
//-----------------------------------------------------------------------------------------------

function calculate_pr_total($pr_cart)
{
        $remaining_total = 0;
        foreach ($pr_cart->line_items as $line_item)
        {
            $line_remaining_total = $line_item->tran_qty * $line_item->estimate_price;
            $remaining_total += $line_remaining_total;
        }
        
        return $remaining_total;
}

function update_pr_item_issued_qty($row_id, $total_issued_qty)
{    

	$sql = "UPDATE ".TB_PREF."purchase_requisition_details 
                SET issued_qty = ".db_escape($total_issued_qty)."  
                WHERE   id = ".db_escape($row_id)." 
                    AND quantity >= ".db_escape($total_issued_qty);

	$error_msg = "update_pr_item_issued_qty";

	db_query($sql, $error_msg);

	return db_num_affected_rows();
}


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

    return db_fetch($result);

}

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

function check_all_pr_lines_issued($source_pr_no)
{
    $sql = "SELECT SUM(IF(quantity - (issued_qty - returned_qty), 1, 0)) outstanding_line_count  
            FROM ".TB_PREF."purchase_requisition_details 
            WHERE requisition_id = ".db_escape($source_pr_no);

    $result = db_query($sql);
    $line_count = db_fetch($result);
    if(!$line_count){
        return true;
    }
    else{
        if($line_count['outstanding_line_count'] == 0){
            return true;
        }
        else{
            return false;
        }
    }
    
}


function get_pr_detail_info($pr_line_id)
{
        
    $sql = "SELECT prd.*,
                ru.name unit_name,
                lu.name req_unit,
                sm.description item_description, 
                sm.stock_control_method,
                sm.material_cost + sm.labour_cost + sm.overhead_cost AS standard_cost,
                bu.name base_unit_name,
                pt.description task_description,
                ps.description project_site_description,
                pr_po.id allocation_id
            FROM ".TB_PREF."purchase_requisition_details prd
                INNER JOIN ".TB_PREF."stock_master sm ON 
                        prd.item_code = sm.stock_id 
                INNER JOIN ".TB_PREF."item_units bu ON 
                        bu.abbr=sm.units
                LEFT JOIN ".TB_PREF."project_tasks pt ON 
                        prd.task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."project_sites ps ON 
                        prd.project_site_id = ps.id  
                LEFT JOIN ".TB_PREF."item_units ru ON 
                        ru.abbr=prd.requested_unit
                LEFT JOIN ".TB_PREF."item_units lu ON 
                        lu.abbr=prd.units
                LEFT JOIN ".TB_PREF."pr_po_details pr_po ON 
                        pr_po.pr_line_id=prd.id
            WHERE 
                prd.id = ".$pr_line_id ;
    
    $result = db_query($sql,"Purchase requisition item detail could not be retreived");

    return db_fetch($result);

}
function get_pr_allocation_ids($pr_line_id)
{
        
    $sql = "SELECT pr_po.id allocation_id
            FROM ".TB_PREF."pr_po_details pr_po 
            WHERE 
                pr_po.pr_line_id = ".$pr_line_id ;
    
    $result = db_query($sql,"Purchase requisition allocation id could not be retreived");

    return $result;

}

//function get_pr_allocation_ids_for_po_line($row_id)
//{
//        
//    $sql = "SELECT id allocation_id
//            FROM ".TB_PREF."pr_po_details pr_po 
//            WHERE 
//                po_line_id = ".$row_id ;
//    
//    $result = db_query($sql,"Could not get_pr_allocation_ids_for_po_line");
//
//    return $result;
//
//}
//
//function get_pr_line_ids_for_po_line($row_id)
//{
//        
//    $sql = "SELECT DISTINCT prd.id, prd.requisition_id 
//            FROM ".TB_PREF."pr_po_details pr_po 
//                JOIN ".TB_PREF."purchase_requisition_details prd 
//                    ON prd.id = pr_po.pr_line_id 
//            WHERE pr_po.po_line_id = ".$row_id ;
//    
//    $result = db_query($sql,"get_pr_line_ids_for_po_line");
//
//    return $result;
//
//}

function get_purchase_requitition($row_id)
{
    $sql = "SELECT pr.*,
                l.location_name,
                pm.description project                
            FROM ".TB_PREF."purchase_requisitions pr
                LEFT JOIN ".TB_PREF."locations l ON l.loc_code = pr.location  
                LEFT JOIN ".TB_PREF."projects_master pm ON pm.project_id =pr.project_id                
            WHERE 
                pr.pr_no = ".$row_id ;
    
    $result = db_query($sql,"Purchase requisition detail could not be retreived");

    return db_fetch($result);
    
}

function view_pr_line_details($pr_line_id)
{
     $sql = "SELECT prd.* ,
                    pm.description project_description,
                    pt.description task_description,
                    sm.description item_description,
                    prd.required_date, 
                    loc.location_name,
                    prd.requested_unit,
                    prd.requested_unit_qty
            FROM ".TB_PREF."purchase_requisition_details prd 
                    INNER JOIN ".TB_PREF."purchase_requisitions pr ON 
                            prd.requisition_id = pr.pr_no                     
                    INNER JOIN ".TB_PREF."projects_master pm 
                            ON pr.project_id = pm.project_id
                    INNER JOIN ".TB_PREF."stock_master sm ON 
                            prd.item_code = sm.stock_id  
                    LEFT JOIN ".TB_PREF."project_tasks pt ON
                            prd.task_id = pt.task_id 
                    LEFT JOIN ".TB_PREF."locations loc
                            ON pr.location = loc.loc_code
            WHERE prd.id=".db_escape($pr_line_id);
          
	$result = db_query($sql,"PR PO Line could not be retrieved.");
    
    return db_fetch($result);

}


function view_pr_line_allocations($pr_line_id)
{
     $sql = "SELECT pro.* ,
                    s.supp_name supplier,
                    s.supp_address,
                    pod.order_no po_no,
                    pr.pr_no, 
                    prd.requisition_id,
                    prd.item_code,
                    pm.description project_description,
                    pt.description task_description,
                    sm.description,
                    prd.required_date, 
                    loc.location_name,
                    prd.requested_unit,
                    prd.requested_unit_qty
            FROM ".TB_PREF."pr_po_details  pro 
                    INNER JOIN ".TB_PREF."purchase_requisition_details prd ON 
                            pro.pr_line_id = prd.id 
                    INNER JOIN ".TB_PREF."purchase_requisitions pr ON 
                            prd.requisition_id = pr.pr_no
                    INNER JOIN ".TB_PREF."stock_master sm ON 
                            prd.item_code = sm.stock_id 
                    LEFT JOIN ".TB_PREF."suppliers s 
                            ON s.supplier_id = pro.supplier_id  
                    LEFT JOIN ".TB_PREF."purch_order_details pod 
                            ON pro.po_line_id = pod.po_detail_item  
                    LEFT JOIN ".TB_PREF."projects_master pm 
                            ON pr.project_id = pm.project_id
                    LEFT JOIN ".TB_PREF."project_tasks pt ON
                            prd.task_id = pt.task_id 
                     LEFT JOIN ".TB_PREF."locations loc
                            ON loc.loc_code = pro.line_loc_code
            WHERE prd.id=".db_escape($pr_line_id);
          
	$result = db_query($sql,"PR PO Line could not be retrieved.");
    
    return $result;

}






function close_purchase_requisition_lines_for_indents($pr_line_id)
{
        $sql = "UPDATE ".TB_PREF."purchase_requisition_details SET closed_for_indents = 1 
                WHERE id = ". db_escape($pr_line_id);
                
                
	return db_query($sql, "Could not close_purchase_requisition_lines_for_indents");
    
}

function get_pr_allocation_details($pr_line_id)
{
     $sql = "SELECT pro.* ,
                    s.supp_name supplier,
                    s.supp_address,
                    loc.location_name,
                    prd.item_code,
                    prd.requisition_id,
                    sm.description stock_item,
                    pd.order_no
            FROM ".TB_PREF."pr_po_details  pro
                    LEFT JOIN ".TB_PREF."purchase_requisition_details prd ON 
                        prd.id= pro.pr_line_id   
                    LEFT JOIN ".TB_PREF."purch_order_details pd ON
                        pd.po_detail_item = pro.po_line_id
                    LEFT JOIN ".TB_PREF."suppliers s 
                            ON s.supplier_id = pro.supplier_id 
                    LEFT JOIN ".TB_PREF."locations loc
                            ON loc.loc_code = pro.line_loc_code
                    LEFT JOIN ".TB_PREF."stock_master sm
                            ON sm.stock_id = prd.item_code
                    LEFT JOIN ".TB_PREF."locations loc
                            ON loc.loc_code = pro.line_loc_code
            WHERE prd.id=".db_escape($pr_line_id);
          
	$result = db_query($sql,"PR PO Line could not be retrieved.");
    
    return $result;

}

function get_pos_for_pr_issues($pr_no)
{
    $user_id = $_SESSION['wa_current_user']->user;
    
    $sql = "SELECT DISTINCT po.*, pm.description project_description, sup.supp_name   
            FROM ".TB_PREF."purchase_requisition_details prd, 
                    ".TB_PREF."pr_po_details prpo, 
                    ".TB_PREF."purch_order_details pod, 
                    ".TB_PREF."purch_orders po,
                    ".TB_PREF."projects_master pm,
                    ".TB_PREF."user_data_access_groups as udag,
                    ".TB_PREF."suppliers sup  
            WHERE prd.id = prpo.pr_line_id AND 
                    prpo.po_line_id = pod.po_detail_item AND 
                    pod.order_no = po.order_no AND 
                    po.project_id = pm.project_id AND 
                    pm.dag_id = udag.dag_id AND 
                    udag.user_id = ".db_escape($user_id)." AND 
                    po.supplier_id = sup.supplier_id AND 
                    prd.requisition_id = ".db_escape($pr_no);
    
    $result = db_query($sql,"Could not get_pos_for_pr_issues");
    
    return $result;
}

function get_po_details_for_pr_issues($pr_no, $po_no)
{
    $sql = "SELECT prd.id prd_id, prd.requisition_id, prd.item_code,
                    prpo.po_line_id,
                    pod.quantity_ordered, 
                    pod.order_unit_qty, 
                    pod.order_unit_conv_factor, 
                    pod.order_unit_price, 
                    pod.order_unit_price_discount, 
                    pod.unit_price, 
                    pod.quantity_received,
                    pod.order_no po_no 
            FROM ".TB_PREF."purchase_requisition_details prd,
                    ".TB_PREF."pr_po_details prpo,
                    ".TB_PREF."purch_order_details pod 
            WHERE 
                    prd.id = prpo.pr_line_id AND 
                    prpo.po_line_id = pod.po_detail_item AND 
                    prd.requisition_id = ".db_escape($pr_no)." AND 
                    pod.order_no = ".db_escape($po_no);

    $result = db_query($sql,"Could not get_po_details_for_pr_issues");
    
    return $result;
}

function get_sql_for_purchase_allocation_requisitions($requisition_number, $req_date_from,$req_date_to,$project,$supplier,$po_no)
{
  
      $sql = "SELECT  
                    pr.pr_no, 
                    pm.description project_description,
                    ms.location_name loc_name_req, 
                    CONCAT(ic.stock_id,'-', ic.description) item_name,
                    iu.name unit_name,
                    prd.requested_unit_qty,
                    prd.required_date, 
                    sup.supp_name supplier_name ,
                    lp.location_name,
                    ppd.order_unit,
                    ppd.order_quantity,
                    ppd.order_unit_price,
                    ppd.order_unit_price_discount,
                    pod.order_no,
                    prd.id pr_line_id
                FROM 
                    ".TB_PREF."purchase_requisition_details prd 
                    LEFT JOIN  ".TB_PREF."pr_po_details ppd ON
                            ppd.pr_line_id = prd.id
                     LEFT JOIN ".TB_PREF."purch_order_details pod ON
                             pod.po_detail_item = ppd.po_line_id      
                    LEFT JOIN ".TB_PREF."purchase_requisitions pr ON
                            prd.requisition_id = pr.pr_no
                    LEFT JOIN ".TB_PREF."projects_master pm ON 
                            pr.project_id = pm.project_id 
                    LEFT JOIN ".TB_PREF."suppliers sup ON 
                            ppd.supplier_id = sup.supplier_id 
                    LEFT JOIN ".TB_PREF."locations lp ON 
                            ppd.line_loc_code = lp.loc_code 
                    LEFT JOIN ".TB_PREF."locations ms ON 
                             pr.location = ms.loc_code 
                    LEFT JOIN ".TB_PREF."stock_master ic ON 
                            ic.stock_id = prd.item_code
                    LEFT JOIN ".TB_PREF."item_units iu ON 
                            iu.abbr = prd.requested_unit
                    LEFT JOIN ".TB_PREF."item_units um ON 
                            um.abbr = ppd.order_unit
                    WHERE  prd.required_date >=".db_escape($req_date_from)."
                        AND prd.required_date <=".db_escape($req_date_to);
                
            if($requisition_number != '')
            {
                $sql .=" AND pr.pr_no =".db_escape($requisition_number);
                
            }
            if($project != '')
            {
                $sql .=" AND pr.project_id =".db_escape($project);
            }
           
            if($supplier != '' )
            {
                $sql .=" AND ppd.supplier_id =".db_escape($supplier);
            }     
             if($po_no != '' )
            {
                $sql .=" AND pod.order_no =".db_escape($po_no);
            }  
            
             $sql .= " ORDER BY pr.pr_no";
	return $sql;
    
 
    
}


function get_source_pr_ids($source_type, $source_order_id)
{
    
    $sql = "SELECT pr.pr_no             
            FROM ".TB_PREF."purchase_requisitions pr                 
            WHERE 
                pr.source_type = ".db_escape($source_type)." AND 
                pr.source_order_id = ".db_escape($source_order_id);
    
    return db_query($sql,"Could not get_pr_source_ids");

}



function get_pr_allocation_lines_of_pr($requisition_number)
{
  
      $sql = "SELECT  
                    pr.pr_no, 
                    pm.description project_description,
                    ms.location_name loc_name_req, 
                    CONCAT(ic.stock_id,'-', prd.item_pr_description) item_name,
                    iu.name unit_name,
                    prd.requested_unit_qty,
                    prd.required_date, 
                    sup.supp_name supplier_name ,
                    lp.location_name,
                    ppd.order_unit,
                    ppd.order_quantity,
                    ppd.order_unit_price,
                    ppd.order_unit_price_discount,
                    pod.order_no,
                    prd.id pr_line_id,
                    ppd.id pr_po_line_id 
                FROM 
                    ".TB_PREF."purchase_requisition_details prd 
                    LEFT JOIN  ".TB_PREF."pr_po_details ppd ON
                            ppd.pr_line_id = prd.id
                    LEFT JOIN ".TB_PREF."purch_order_details pod ON
                             pod.po_detail_item = ppd.po_line_id      
                    LEFT JOIN ".TB_PREF."purchase_requisitions pr ON
                            prd.requisition_id = pr.pr_no
                    LEFT JOIN ".TB_PREF."projects_master pm ON 
                            pr.project_id = pm.project_id 
                    LEFT JOIN ".TB_PREF."suppliers sup ON 
                            ppd.supplier_id = sup.supplier_id 
                    LEFT JOIN ".TB_PREF."locations lp ON 
                            ppd.line_loc_code = lp.loc_code 
                    LEFT JOIN ".TB_PREF."locations ms ON 
                             pr.location = ms.loc_code 
                    LEFT JOIN ".TB_PREF."stock_master ic ON 
                            ic.stock_id = prd.item_code
                    LEFT JOIN ".TB_PREF."item_units iu ON 
                            iu.abbr = prd.requested_unit
                    LEFT JOIN ".TB_PREF."item_units um ON 
                            um.abbr = ppd.order_unit
                    WHERE pr.pr_no =".db_escape($requisition_number)." 
                    ORDER BY pr.pr_no";
      
      
	return db_query($sql,"Could not get_pr_allocation_lines_of_pr");
    
 
    
}


function get_pr_allocation_line_details($pr_po_id)
{
  
      $sql = "SELECT  
                    pr.pr_no, 
                    pm.description project_description,
                    ms.location_name loc_name_req, 
                    CONCAT(ic.stock_id,'-', ic.description) item_name,
                    iu.name unit_name,
                    prd.requested_unit_qty,
                    prd.required_date, 
                    sup.supp_name supplier_name ,
                    lp.location_name,
                    ppd.order_unit,
                    ppd.order_quantity,
                    ppd.order_unit_price,
                    ppd.order_unit_price_discount,
                    pod.order_no,
                    prd.id pr_line_id,
                    ppd.id pr_po_line_id 
                FROM 
                    ".TB_PREF."purchase_requisition_details prd 
                    LEFT JOIN  ".TB_PREF."pr_po_details ppd ON
                            ppd.pr_line_id = prd.id
                    LEFT JOIN ".TB_PREF."purch_order_details pod ON
                             pod.po_detail_item = ppd.po_line_id      
                    LEFT JOIN ".TB_PREF."purchase_requisitions pr ON
                            prd.requisition_id = pr.pr_no
                    LEFT JOIN ".TB_PREF."projects_master pm ON 
                            pr.project_id = pm.project_id 
                    LEFT JOIN ".TB_PREF."suppliers sup ON 
                            ppd.supplier_id = sup.supplier_id 
                    LEFT JOIN ".TB_PREF."locations lp ON 
                            ppd.line_loc_code = lp.loc_code 
                    LEFT JOIN ".TB_PREF."locations ms ON 
                             pr.location = ms.loc_code 
                    LEFT JOIN ".TB_PREF."stock_master ic ON 
                            ic.stock_id = prd.item_code
                    LEFT JOIN ".TB_PREF."item_units iu ON 
                            iu.abbr = prd.requested_unit
                    LEFT JOIN ".TB_PREF."item_units um ON 
                            um.abbr = ppd.order_unit
                    WHERE ppd.id =".db_escape($pr_po_id)." 
                    ORDER BY pr.pr_no";
      
      
    $result = db_query($sql,"Could not get_pr_allocation_line_details");
    
    return db_fetch($result);
}

function get_source_purchase_requisitions($source_type, $source_order_id)
{
    
    $sql = "SELECT *       
            FROM ".TB_PREF."purchase_requisitions pr                 
            WHERE 
                pr.source_type = ".db_escape($source_type)." AND 
                pr.source_order_id = ".db_escape($source_order_id);
    
    return db_query($sql,"Could not get_pr_source_ids");

}
?>
