<?php
/**********************************************************************
***********************************************************************/
//-------------------------------------------------------------------------------------------------------------
function get_sql_for_purchase_requisitions($data_after, $date_before, $requisition_number = null, $reference = null,
                                        $location = null, $project_id = null, $originator_id = null)
{

    $data_after = date2sql($data_after);
    $date_before = date2sql($date_before);
        
	$sql = "SELECT 
                pr.pr_no, 
                pr.reference,  
                l.location_name location,
                p.description, 
                pr.pr_date,
                u.real_name originator_name,
                pr.created_on,
                pr.approval_status,
                pr.pr_complete 
		FROM ".TB_PREF."purchase_requisitions as pr, 
                ".TB_PREF."projects_master as p, 
                ".TB_PREF."locations l,
                ".TB_PREF."users as u
		WHERE pr.project_id = p.project_id AND 
                l.loc_code = pr.location AND 
                pr.created_by = u.id ";


        if (strlen($reference)>0 || $requisition_number > 0){
            if($requisition_number > 0){
                $sql .= "AND pr.pr_no = ".db_escape($requisition_number);
            }
        
            if ($reference != ""){
                $sql .= "AND pr.reference LIKE ".db_escape('%'. $reference . '%');
            }
        }
        else{
            if (isset($location) && $location != ALL_TEXT){
                    $sql .= " AND pr.location = ".db_escape($location);
            }

            if (isset($project_id) && $project_id > 0){
                            $sql .= " AND pr.project_id =".db_escape($project_id);
                    }

            if (isset($originator_id) && $originator_id > 0){
                            $sql .= " AND pr.created_by =".db_escape($originator_id);
                    }

            $sql .= " AND pr.created_on >= ".db_escape($data_after). " AND pr.created_on <= ".db_escape($date_before);
        }

        $sql .= " GROUP BY pr.pr_no";
        return $sql;
}

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

function view_purchase_requitition_header($pr_no)
{
    $sql = "SELECT pr.*,
                l.location_name,
                pm.description project,
                u.real_name originator_name 
            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                 
                LEFT JOIN ".TB_PREF."users u ON pr.created_by = u.id 
            WHERE 
                pr.pr_no = ".$pr_no ;
    
    $result = db_query($sql,"Could not view_purchase_requitition_header");

    return db_fetch($result);
    
}


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


function view_pr_details($pr_no)
{
	$sql = "SELECT prd.*,
                sm.description, 
                sm.stock_control_method,
                sm.material_cost + sm.labour_cost + sm.overhead_cost AS standard_cost,
                sm.mb_flag,
                pt.description task_description,
                ps.description project_site_description,
                ru.name req_unit_name 
            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 ru ON 
                        prd.requested_unit = ru.abbr      
                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 get_sql_for_approve_purchase_requisition($current_user_id)
{

	$sql = "SELECT 
                pr.pr_no, 
                pr.reference,  
                l.location_name location,
                p.description, 
                pr.pr_date,
                creu.real_name,
                pr.approval_status,
                pr.created_on 
            FROM ".TB_PREF."purchase_requisitions as pr, 
                ".TB_PREF."projects_master as p, 
                ".TB_PREF."locations l,
                ".TB_PREF."approvers as apr, 
                ".TB_PREF."user_data_access_groups as udag,
                ".TB_PREF."users creu   
            WHERE pr.project_id = p.project_id AND 
                l.loc_code = pr.location AND 
                (pr.approval_status = ".AS_AWAITING_APPROVAL." OR pr.approval_status = ".AS_WITH_THE_APPROVER.") AND 
                pr.next_approval_group_id = apr.approval_group_id AND 
                p.dag_id = udag.dag_id AND 
                udag.user_id = apr.user_id AND  
                pr.created_by = creu.id AND 
                apr.user_id = ".$current_user_id;
		
	return $sql;
}

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

function get_pr_issues_for_pr($pr_number)
{

	$sql = "SELECT
                    pri.pri_no,
                    pri.reference,  
                    pri.source_pr_no,                 
                    l.location_name location,
                    pri.created_on,
                    u.real_name originator_name,                   
                    pri.po_no                    

		FROM  ".TB_PREF."purchase_requisition_issues as pri,                     
                        ".TB_PREF."locations l,
                        ".TB_PREF."users as u
                    
		WHERE l.loc_code = pri.issuing_location AND                      
                        pri.created_by = u.id AND 
                        pri.source_pr_no = ".db_escape($pr_number)." ";


    return db_query($sql, "could not get_pr_issues_for_pr");		
    
}


function get_pr_returns_for_pr($pr_no)
{

	$sql = "SELECT
                    pri.prr_no,
                    pri.reference,  
                    pri.pr_no,                 
                    l.location_name location,
                    pri.created_on,
                    u.real_name originator_name,                   
                    pri.cn_no                    

		FROM  ".TB_PREF."purchase_requisition_returns as pri,                     
                        ".TB_PREF."locations l,
                        ".TB_PREF."users as u
                    
		WHERE l.loc_code = pri.location AND                      
                        pri.created_by = u.id AND 
                        pri.pr_no = ".db_escape($pr_no)." ";


    return db_query($sql, "could not get_pr_issues_for_pr");		
    
}

function get_sql_for_outstanding_pr_search($order_number, $order_reference, $orders_after_date, $orders_to_date, $stock_location,
                                            $reqester_id)
{
	//global $all_items, $order_number, $selected_stock_item;
	
	$sql = "SELECT pr.pr_no, pr.reference, pr.pr_date, pm.description project_description, l.location_name, 
                        u.real_name reqester_name,
                        pr.approval_status 
                FROM ".TB_PREF."purchase_requisitions pr 
                    JOIN ".TB_PREF."purchase_requisition_details prd 
                        ON pr.pr_no = prd.requisition_id 
                    JOIN ".TB_PREF."projects_master pm 
                        ON pm.project_id = pr.project_id 
                    JOIN ".TB_PREF."locations l 
                        ON l.loc_code = pr.location 
                    JOIN ".TB_PREF."users u 
                        ON u.id = pr.created_by 
                WHERE prd.quantity > prd.issued_qty 
                    AND pr.pr_complete = 0 
                    AND (pr.approval_status = ".db_escape(AS_APPROVED)." OR pr.approval_status = ".db_escape(AS_ISSUED).")";
                
            if(isset($order_number) && $order_number > 0)
            {
                $sql .= " AND pr.pr_no = ".db_escape($order_number);
            }
            elseif(isset($order_reference) && strlen($order_reference) > 0){
                $sql .= " AND pr.reference LIKE ".db_escape('%'. $order_reference . '%');
            }
            else
            {
                $sql .= "  AND pr.pr_date >= ".db_escape(date2sql($orders_after_date));
                $sql .= "  AND pr.pr_date <= ".db_escape(date2sql($orders_to_date));
            
                if ($stock_location != '')
                {
                        $sql .= " AND pr.location = ".db_escape($_POST['StockLocation']);
                }              


                if (($reqester_id) > 0)
                {
                        $sql .= " AND pr.created_by  =".db_escape($reqester_id);
                }
            
            } //end not order number selected

	$sql .= " GROUP BY pr.pr_no";
	return $sql;
}


function get_outstanding_purchase_requisition_details($from_date, $to_date, $pr_no, $reference,
                                                            $StockLocation, $project_id, $originator_id)
{
    $sql = "SELECT prd.*,
                pr.reference, 
                pr.pr_date,
                pm.description project_description,
                sm.description item_description,
                sm.mb_flag, 
                u.name unit_name 
            FROM ".TB_PREF."purchase_requisition_details prd  
                JOIN ".TB_PREF."stock_master sm 
                    ON prd.item_code = sm.stock_id 
                JOIN ".TB_PREF."purchase_requisitions pr 
                    ON pr.pr_no = prd.requisition_id 
                JOIN ".TB_PREF."projects_master pm 
                    ON pm.project_id = pr.project_id 
                JOIN ".TB_PREF."item_units u  
                    ON u.abbr = prd.requested_unit   
            WHERE pr.pr_complete = 0 
                AND pr.approval_status IN (".AS_APPROVED.",".AS_ISSUED.",".AS_COMPLETED.",".AS_CLOSED.") 
                AND pr_date >= ".db_escape($from_date)." 
                AND pr_date <= ".db_escape($to_date);
    
    if ($pr_no > 0)
    {
            $sql .= " AND pr.created_by  =".db_escape($pr_no);
    }
    if (strlen($reference) > 0)
    {
            $sql .= " AND pr.reference LIKE ".db_escape($reference."%");
    }
    if (strlen($StockLocation) > 0)
    {
            $sql .= " AND pr.location = ".db_escape($StockLocation);
    }
    if ($project_id > 0)
    {
            $sql .= " AND pr.project_id  =".db_escape($project_id);
    }
    if ($originator_id > 0)
    {
            $sql .= " AND pr.created_by  =".db_escape($project_id);
    }

    $sql .= " ORDER BY pm.description, pr_date LIMIT 1000";
    return db_query($sql, "get_outstanding_purchase_requisition_details");		
    
}

function get_purchase_requisition_detail_related_transactions($prd_id)
{
    $sql = "SELECT pr_po.id pr_allocation_id,
                pod.order_no po_no,
                grni.grn_batch_id grn_no,
                prid.pri_no  
            FROM ".TB_PREF."purchase_requisition_details prd  
                LEFT JOIN ".TB_PREF."pr_po_details pr_po 
                    ON prd.id = pr_po.pr_line_id 
                LEFT JOIN ".TB_PREF."purch_order_details pod 
                    ON pod.po_detail_item = pr_po.po_line_id 
                LEFT JOIN ".TB_PREF."grn_items grni 
                    ON grni.po_detail_item = pod.po_detail_item 
                LEFT JOIN ".TB_PREF."purchase_requistion_issue_details prid 
                    ON prid.pr_line_id = prd.id OR prid.grn_item_id = grni.id 
            WHERE prd.id =".db_escape($prd_id);
    
     return db_query($sql, "get_purchase_requisition_detail_related_transactions");	
    
}

function get_pr_returns_for_pr_issue($pri_no)
{

	$sql = "SELECT
                    pri.prr_no,
                    pri.reference,  
                    pri.pr_no,                 
                    l.location_name location,
                    pri.created_on,
                    u.real_name originator_name,                   
                    pri.cn_no                    

		FROM  ".TB_PREF."purchase_requisition_returns as pri,                     
                        ".TB_PREF."locations l,
                        ".TB_PREF."users as u
                    
		WHERE l.loc_code = pri.location AND                      
                        pri.created_by = u.id AND 
                        pri.pri_no = ".db_escape($pri_no)." ";


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

function get_grn_qty_by_prd_no($prd_id) {
    $sql = "SELECT SUM(grni.qty_recd) total_base_unit_qty 
            FROM ".TB_PREF."purchase_requisition_details prd  
                JOIN ".TB_PREF."pr_po_details pr_po 
                    ON prd.id = pr_po.pr_line_id 
                JOIN ".TB_PREF."purch_order_details pod 
                    ON pod.po_detail_item = pr_po.po_line_id 
                JOIN ".TB_PREF."grn_items grni 
                    ON grni.po_detail_item = pod.po_detail_item 
            WHERE prd.id = ".db_escape($prd_id);
    
     $result = db_query($sql, "get_grn_qty_by_prd_no");	
     return db_result_value($result, 'total_base_unit_qty');
}