<?php

function get_sql_for_site_material_returns_inquiry($id, $reference, $to_location, $from_date, $to_date, $from_project_id, $to_project_id, $company_code,
                                                   $employee_code){
    $sql = "SELECT  smr.id,
                    smr.reference,
                    l2.location_name to_location_name,
                    smr.tran_date,
                    pm.description from_project_description,
                    pm2.description to_project_description,
                    gc.company_name,
                    smr.employee_code,
                    smr.total,
                    smr.company_code,
                    smr.status 
                    
            FROM ".TB_PREF."site_material_returns smr  
                    LEFT JOIN ".TB_PREF."projects_master pm 
                        ON smr.from_project_id = pm.project_id 
                    LEFT JOIN ".TB_PREF."projects_master pm2 
                        ON smr.to_project_id = pm2.project_id    
                    LEFT JOIN ".TB_PREF."locations l2 
                        ON smr.to_location = l2.loc_code  
                    LEFT JOIN group_companies gc 
                        ON smr.company_code = gc.company_code 
                               
            WHERE 1 = 1 ";
    
    if($id > 0 || strlen($reference) > 0){
        if($id > 0){
            $sql .= " AND smr.id = ".db_escape($id);
        }
        if(strlen($reference) > 0){
            $sql .= " AND smr.reference = ".db_escape($reference);
        }
    }else{
        $sql .= " AND smr.tran_date >= ".db_escape(date2sql($from_date))." AND smr.tran_date <= ".db_escape(date2sql($to_date)); 


        if($to_location != ''){        
            $sql .= " AND smr.to_location = ".db_escape($to_location);
        }

        if($from_project_id > 0){
            $sql .= " AND smr.from_project_id = ".db_escape($from_project_id);
        }
        if($to_project_id > 0){
            $sql .= " AND smr.to_project_id = ".db_escape($to_project_id);
        }
        if(strlen($company_code) > 0){
            $sql .= " AND smr.company_code = ".db_escape($company_code);
        }
        if(strlen($employee_code) > 0){
            $sql .= " AND smr.employee_code = ".db_escape($employee_code);
        }
    }
    
    $sql .= " ORDER BY smr.id";
    return $sql;
}   

function view_site_material_returns($trans_no){
    
    $sql = "SELECT smr.reference,  
                l2.location_name to_location_name,
                pm.description from_project_description,
                pm2.description to_project_description, smr.tran_date, 
                smr.comments, 
                gc.company_name,
                smr.employee_code,
                smr.comments,
                smr.company_code,
                smr.tran_date, 
                smr.status,
                smrd.id, 
                smrd.stock_id, 
                sm.description item_description, 
                smrd.tran_unit, 
                smrd.tran_qty, 
                smrd.tran_unit_cost, 
                smrd.standard_cost, 
                pt.description from_task_description,
                pt2.description to_task_description,
                ps.description from_project_site_description,
                ps2.description to_project_site_description,
                smrd.tran_unit_cost * smrd.tran_qty line_total 
                    
            FROM ".TB_PREF."site_material_return_details smrd 
                JOIN ".TB_PREF."site_material_returns smr  
                    ON smrd.trans_no = smr.id  
                INNER JOIN ".TB_PREF."projects_master pm 
                    ON smr.from_project_id = pm.project_id 
                INNER JOIN ".TB_PREF."projects_master pm2 
                    ON smr.to_project_id = pm2.project_id 
                INNER JOIN ".TB_PREF."locations l2  
                    ON smr.to_location = l2.loc_code 
                LEFT JOIN group_companies gc 
                        ON smr.company_code = gc.company_code 
                    
                LEFT JOIN ".TB_PREF."project_tasks pt 
                    ON smrd.from_task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."project_tasks pt2 
                    ON smrd.to_task_id = pt2.task_id 
                LEFT JOIN ".TB_PREF."project_sites ps  
                    ON smrd.from_project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_sites ps2   
                    ON smrd.to_project_site_id = ps2.id 
                JOIN ".TB_PREF."stock_master sm    
                    ON smrd.stock_id = sm.stock_id  
                    
            WHERE smrd.trans_no = ".db_escape($trans_no);
    
    return db_query($sql, "could not view_site_material_returns");
}

?>