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

function add_pr_return_header($batch_no, $reference, $location, $prr_date, $project_id, $pri_no, $pr_no, $company_code, $employee_code, $comment) {
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');

	$sql = "INSERT INTO ".TB_PREF."purchase_requisition_returns(
                        batch_no, reference, 
                        location, prr_date, 
                        project_id, pri_no, pr_no, 
                        company_code, employee_code, 
                        comment, 
                        created_by, created_on) 
                VALUES (".db_escape($batch_no).", ".db_escape($reference).", "
                        .db_escape($location).", ".db_escape($prr_date).", "
                        .db_escape($project_id).", ".db_escape($pri_no).", ".db_escape($pr_no).", "
                        .db_escape($company_code).", ".db_escape($employee_code).", "
                        .db_escape($comment).", "
                        .db_escape($created_by).", ".db_escape($created_on).")";

	$error_msg = "The purchase requisition return could not be inserted";

	db_query($sql, $error_msg);

	return db_insert_id();
}

function add_pr_return_item($prr_no, $item_code, $description, $qty, $unit_cost, $tran_unit, $tran_qty, $conversion_factor, $pr_line_id, 
                            $task_id, $project_site_id, $grn_item_id, $cn_line_id, $cost_source, $pri_line_id) {
    $sql = "INSERT INTO ".TB_PREF."purchase_requistion_return_details (
                    prr_no, 
                    item_code, description, 
                    qty, unit_cost, 
                    tran_unit, tran_qty, 
                    conversion_factor, pr_line_id, 
                    task_id, project_site_id, 
                    grn_item_id, cn_line_id, 
                    cost_source, pri_line_id) 
            VALUES (".db_escape($prr_no).", "
                    .db_escape($item_code).", ".db_escape($description).", "
                    .db_escape($qty).", ".db_escape($unit_cost).", "
                    .db_escape($tran_unit).", ".db_escape($tran_qty).", "
                    .db_escape($conversion_factor).", ".db_escape($pr_line_id).", "
                    .db_escape($task_id).", ".db_escape($project_site_id).", "
                    .db_escape($grn_item_id).", ".db_escape($cn_line_id).", "
                    .db_escape($cost_source).", ".db_escape($pri_line_id).")";
    
    db_query($sql, "add_pr_return_item");
    
    return db_insert_id();
}



function get_pr_item_total_returned_qty($pr_line_id)
{
    $sql = "SELECT SUM(qty) total_returned_qty  
            FROM ".TB_PREF."purchase_requistion_return_details  
            WHERE pr_line_id = ".db_escape($pr_line_id);
    
    $result = db_query($sql, "get_pr_item_total_returned_qty");
    return db_result_value($result, "total_returned_qty");
}



function get_prr_lines_header_total($prr_no)
{
    $sql = "SELECT SUM(qty * unit_cost) prr_total  
            FROM ".TB_PREF."purchase_requistion_return_details  
            WHERE prr_no = ".db_escape($prr_no);
    
    $result = db_query($sql, "get_prr_lines_header_total");
    return db_result_value($result, "prr_total");
}




function update_pr_return_header_total($prr_no, $prr_total)
{

    $sql = "UPDATE ".TB_PREF."purchase_requisition_returns    
                SET total = ".db_escape($prr_total)." 
            WHERE prr_no = ".db_escape($prr_no);
    
    db_query($sql, "update_pr_return_header_total");
//    if(!(db_num_affected_rows() > 0)){
//        return array("success" => false, "message" => "Could not Update PR Issues Header #$pri_no Total.");
//    }else{
//        return array("success" => true, "message" => "");
//    }
}

function get_pr_return_lines_total($pri_no)
{
    
//    $sql = "SELECT SUM(FORMAT(unit_cost * conversion_factor,2) * tran_qty) lines_total 
//            FROM ".TB_PREF."purchase_requistion_issue_details   
//            WHERE pri_no = ".  db_escape($pri_no);
    
    $sql = "SELECT (unit_cost * conversion_factor) tran_unit_cost , tran_qty 
            FROM ".TB_PREF."purchase_requistion_issue_details   
            WHERE pri_no = ".  db_escape($pri_no);
    
    $result = db_query($sql, "could not get_pr_issue_header_details_lines_total");
    
    $total = 0;
    while ($row = db_fetch($result)) {
        $nn = round($row['tran_unit_cost'],2);
        $tran_qty = doubleval($row['tran_qty']);
        $tt = doubleval($nn * $tran_qty);
        $total += $tt;
    }
    return $total;
}



function get_pr_return_record_by_batch_no($batch_no, $pri_no)
{
    $sql = "SELECT *   
            FROM ".TB_PREF."purchase_requisition_returns  
            WHERE batch_no = ".db_escape($batch_no)." 
                AND pri_no = ".db_escape($pri_no);
    
    $result = db_query($sql, "get_pr_return_record_by_batch_no");
    return db_fetch($result);
}



function get_pr_return_record($prr_no)
{
    $sql = "SELECT *   
            FROM ".TB_PREF."purchase_requisition_returns  
            WHERE  prr_no = ".db_escape($prr_no);
    
    $result = db_query($sql, "get_pr_return_record");
    return db_fetch($result);
}


function get_pr_details_for_prr_cost_charging($pr_no, $prr_no)
{

        $sql = "SELECT prd.*,
                        prrd.id prrd_line_id,
                        prrd.unit_cost prrd_unit_cost,
                        prrd.qty prrd_qty

            FROM ".TB_PREF."purchase_requisition_details prd     
                LEFT JOIN ".TB_PREF."purchase_requistion_return_details prrd 
                    ON prrd.pr_line_id = prd.id  
                    AND prrd.prr_no = ".db_escape($prr_no)." 
            WHERE prd.requisition_id = ".db_escape($pr_no);
    
    
	return db_query($sql, "get_pr_details_for_prr_cost_charging");

}

function get_prr_total_returned_qty_by_pri_line_id($pri_line_id) {
    $sql = "SELECT SUM(qty) total_returned_qty  
            FROM ".TB_PREF."purchase_requistion_return_details  
            WHERE pri_line_id = ".db_escape($pri_line_id);
    
    $result = db_query($sql, "get_prr_total_returned_qty_by_pri_line_id");
    return db_result_value($result, "total_returned_qty");
}

function update_pri_detail_line_returned_qty($pri_line_id, $pri_returned_qty) {
    $sql = "UPDATE ".TB_PREF."purchase_requistion_issue_details     
                SET returned_qty = ".db_escape($pri_returned_qty)." 
            WHERE id = ".db_escape($pri_line_id)." 
                AND qty_issued >= ".db_escape($pri_returned_qty);
    
        db_query($sql, "update_pri_detail_line_returned_qty");
        
        return db_num_affected_rows();
}