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


function get_sql_for_mr_inquiry($required_by_from = null, $required_by_to = null, 
//            $created_on_from = null, $created_on_to = null,  
            $issuing_location = "",
            $mr_no = 0,  $mr_reference = "", $mr_complete = null,
            $project_id = null, $originator = null,
            $stock_item = null)
{
        $filter = "";
        
        if($required_by_from != null)
        {
            $filter = " AND mr.mr_date > ".db_escape(date2sql($required_by_from));
        }
        
        if($required_by_to != null)
        {
                $filter .= " AND mr.mr_date < ".db_escape(date2sql($required_by_to));
        }
        
//        if($created_on_from != null)
//        {
//                $filter .= " AND mr.created_on > ".db_escape(date2sql($created_on_from));
//        }
//        
//        if($created_on_to != null)
//        {
//                $filter .= " AND mr.created_on < ".db_escape(date2sql($created_on_to));
//        }
        
        if($issuing_location != "")
        {
                $filter .= " AND mr.issuing_location = ".db_escape($issuing_location);            
        }
        
        if ($mr_no > 0)
        {
                $filter .= " AND mr.mr_no = ".$mr_no;
        }
        
        if ($project_id > 0)
        {
                $filter .= " AND mr.project_id = ".db_escape($project_id); 
        }
        
        if ($originator > 0)
        {
                $filter .= " AND mr.created_by = ".db_escape($originator); 
        }
        

        $filter .= " AND mr.mr_complete = ".db_escape($mr_complete);         

        
        //$filter = "";
        
        if ($stock_item == null)
        {
            $sql = "SELECT 
                        mr.mr_no,
                        mr.reference, 
                        il.location_name issuing_location ,
                        p.description,
                        mr.mr_date mr_required_date,
                        u.real_name requestor_name,
                        mr.approval_status, 
                        mr.created_on,
                        mr.closed_for_prs 
                    FROM 
                        ".TB_PREF."material_requisitions mr,
                        ".TB_PREF."projects_master as p, 
                        ".TB_PREF."locations il,
                        ".TB_PREF."users u 
                    WHERE  mr.project_id = p.project_id AND 
                        mr.issuing_location = il.loc_code AND 
                        mr.created_by = u.id "
                        .$filter." 
                    ORDER BY
                        mr.mr_no";
        }
        else
        {
            $filter .= " AND sm.stock_id = ".db_escape($stock_item);         
        
            $sql = "SELECT DISTINCT
                        mr.mr_no,
                        mr.reference, 
                        mr.issuing_location,
                        mr.mr_date mr_required_date,
                        mr.comments, 
                        mr.mr_complete,
                        u.real_name requestor_name,
                        mr.created_on,
                        mr.approval_status,
                        mr.created_on,
                        mr.closed_for_prs 
                    FROM 
                        ".TB_PREF."material_requisitions mr,
                        ".TB_PREF."users u,
                        ".TB_PREF."material_requisition_details mrd,
                        ".TB_PREF."stock_master sm
                    WHERE 
                        mr.created_by = u.id AND 
                        mr.mr_no = mrd.mr_no AND 
                        mrd.stock_id = sm.stock_id "
                        .$filter."  
                    ORDER BY
                        mr.mr_no";
        }

	return $sql;
}


function view_material_requisition_header($mr_no)
{
    $sql = "SELECT 
                mr.mr_no,
                mr.reference, 
                mr.issuing_location,
                il.location_name issuing_location_name,              
                mr.mr_date mr_required_date,
                mr.comments, 
                mr.approval_status, 
                mr.mr_complete,
                mr.project_id,
                pm.description project_description,
                ap.approval_path_name approval_path_description,
                mr.next_approval_group_id,
                u.real_name requestor_name,
                mr.created_by,
                mr.created_on, 
                mr.movement_type, 
                mt.name movement_type_name, 
                mr.linked_field_type,
                mr.sample_items,
                mr.linked_field_id,
                mr.row_state,
                mr.pr_no 
            FROM ".TB_PREF."material_requisitions mr 
                INNER JOIN ".TB_PREF."projects_master pm ON 
                    mr.project_id = pm.project_id 
                INNER JOIN ".TB_PREF."locations il ON 
                    mr.issuing_location = il.loc_code                 
                INNER JOIN ".TB_PREF."users u ON 
                     mr.created_by = u.id 
                LEFT JOIN ".TB_PREF."movement_types mt ON 
                    mr.movement_type = mt.id                  
                LEFT JOIN ".TB_PREF."approval_path ap ON 
                    pm.approval_path_id = ap.approval_path_id 
                
            WHERE  
                mr.mr_no = ".  db_escape($mr_no);
    
    $result = db_query($sql,"Material requisition could not be retreived");

    return db_fetch($result);

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

function view_material_requisition_details($mr_no)
{
    $sql = "SELECT 
                mrd.id,
                mrd.mr_no,
                mrd.stock_id,
                sm.description,
                sm.units,
                mrd.required_date,
                mrd.price,
                sm.material_cost + sm.labour_cost + sm.overhead_cost AS standard_cost,
                mrd.requested_qty,
                mrd.issued_qty,
                mrd.returned_qty,
                mrd.ordered_qty,
                mrd.line_comment,
                mrd.line_complete, 
                mrd.created_by, 
                mrd.created_on,
                mrd.requested_unit,
                mrd.requested_unit_qty, 
                mrd.req_unit_conv_factor,
                sm.high_value_item,
                sm.sample_item,
                sm.reference_cost, 
                sm.stock_control_method, 
                mrd.pr_line_id,
                mrd.task_id,
                pt.description task_description,
                mrd.project_site_id,
                ps.description project_site_description,
                u.name requested_unit_name,
                u.decimals 
            FROM 
                ".TB_PREF."material_requisition_details mrd 
                INNER JOIN ".TB_PREF."stock_master sm ON 
                    mrd.stock_id = sm.stock_id   
                LEFT JOIN ".TB_PREF."project_tasks pt ON
                    mrd.task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."project_sites ps ON
                    mrd.project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."item_units u ON
                    mrd.requested_unit = u.abbr     
            WHERE 
                mrd.mr_no = ".db_escape($mr_no)." 
            ORDER BY
                mrd.id";
    
    $result = db_query($sql,"Material requisition details could not be retreived");

    return $result;

}






        
?>