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


function get_sql_for_pr_returns($date_after_, $date_before_, $prr_number, $pr_number,
                                $location, $project_id, $originator_id)
{
    $date_after = date2sql($date_after_);
    $date_before = date2sql($date_before_);
        
	$sql = "SELECT
                    prr.prr_no,
                    prr.reference,  
                    prr.pr_no,                 
                    l.location_name location,
                    p.description, 
                    prr.created_on,
                    u.real_name originator_name,
                    prr.cn_no 

		FROM  ".TB_PREF."purchase_requisition_returns as prr                    
                    JOIN ".TB_PREF."projects_master as p 
                        ON prr.project_id = p.project_id 
                    JOIN ".TB_PREF."locations l 
                        ON l.loc_code = prr.location 
                    JOIN ".TB_PREF."users as u 
                        ON prr.created_by = u.id                                        
		WHERE prr.prr_date >= ".db_escape($date_after). " 
                    AND prr.prr_date <= ".db_escape($date_before);


        if (isset($prr_number) && $prr_number > 0){
            $sql .= "AND prr.prr_no = ".db_escape($prr_number);
        }
        
        if (isset($pr_number) && $pr_number > 0){
            $sql .= "AND prr.source_pr_no = ".db_escape($pr_number);
        }
   

        if (isset($location) && $location != ALL_TEXT){
                $sql .= " AND prr.location = ".db_escape($location);
        }
		
        if (isset($project_id) && $project_id > 0){
			$sql .= " AND prr.project_id =".db_escape($project_id);
		}
        
        if (isset($originator_id) && $originator_id > 0){
			$sql .= " AND prr.created_by =".db_escape($originator_id);
		}


     //   $sql .= " GROUP BY pri.source_pr_no";
        return $sql;
    
}


function view_pr_return($trans_no)
{
    $sql = "SELECT pri.prr_no,
                    pri.pri_no,
                    pri.reference,
                    pri.pr_no,     
                    pri.cn_no,     
                    pri.created_on,
                    lo.location_name,
                    pm.description project_description,
                    us.real_name originator_name,
                    pri.* 

            FROM ".TB_PREF."purchase_requisition_returns pri,
                    ".TB_PREF."locations lo,
                    ".TB_PREF."projects_master pm,
                    ".TB_PREF."users us
             
            WHERE lo.loc_code = pri.location AND
                pm.project_id = pri.project_id AND
                us.id = pri.created_by AND
                pri.prr_no=".db_escape($trans_no);
    
    return db_query($sql);              
}


function view_pr_returns_details($trans_no)
{
    $sql = "SELECT prd.description,                    
                    prd.item_code,
                    prd.tran_unit, 
                    prd.tran_qty,
                    prd.project_site_id,
                    prd.pr_line_id,
                    prd.unit_cost,
                    prd.conversion_factor,
                    ps.description site_name,
                    pt.description task_description,
                    pt.task_code,
                    mov.qty,
                    mov.standard_cost,
                    sm.mb_flag 

            FROM ".TB_PREF."purchase_requistion_return_details prd 
                JOIN ".TB_PREF."project_tasks pt ON 
                    prd.task_id = pt.task_id 
                JOIN ".TB_PREF."stock_master sm ON 
                    prd.item_code = sm.stock_id 
                LEFT JOIN ".TB_PREF."stock_moves mov ON
                    mov.type = ".db_escape(ST_PR_RETURN)." AND 
                    mov.stock_id = prd.item_code AND 
                    mov.trans_no = prd.prr_no AND 
                    mov.ref_line_id = prd.id  
                LEFT JOIN ".TB_PREF."project_sites ps ON
                    prd.project_site_id = ps.id
                
            WHERE prd.prr_no=".db_escape($trans_no);
    
      return db_query($sql);    
    
}


?>