<?php

function get_outstanding_tool_issue_details($asset_id, $trans_no, $type, $project_id, $job_id, $location, $employee_code, $from_date_, $to_date_) {
    $from_date = date2sql($from_date_);
    $to_date = date2sql($to_date_);
    
    $filters = '';
    
    if (strlen($asset_id) > 0) {
        $filters .= " AND tran.asset_id = ".db_escape($asset_id); 
    }
    if ($project_id > 0) {
        $filters .= " AND hd.project_id = ".db_escape($project_id);
    }
    if ($job_id > 0) {
        $filters .= " AND tran.site_id = ".db_escape($job_id);
    }
    
    $filters .= " AND hd.tran_date >= ".db_escape($from_date)." 
                  AND hd.tran_date <= ".db_escape($to_date);
    
        $sql_1 = "( SELECT tran.id, ".db_escape(ST_TOOL_ISSUE)." type, tran.ai_id trans_no, 
                        pm.description project_name,
                        hd.company_code,
                        hd.employee_code,
                        hd.tran_date,
                        tran.asset_id,
                        am.description asset_description,
                        ps.description job_name,  
                        tran.quantity,
                        tran.returned_qty returned_quantity,
                        tran.line_comment,
                        l.location_name 
                    FROM ".TB_PREF."tool_issues hd  
                        JOIN ".TB_PREF."tool_issue_details tran 
                            ON hd.id = tran.ai_id 
                        JOIN ".TB_PREF."asset_locations l 
                            ON hd.location = l.loc_code 
                        JOIN ".TB_PREF."projects_master pm 
                            ON hd.project_id = pm.project_id  
                        JOIN ".TB_PREF."asset_master am 
                            ON tran.asset_id = am.asset_id 
                        LEFT JOIN ".TB_PREF."project_sites ps 
                            ON tran.site_id = ps.id 
                    WHERE tran.quantity > tran.returned_qty 
                        $filters";
        
                        if ($trans_no) {
                            $sql_1 .= " AND tran.ai_id = ".db_escape($trans_no);
                        }
                        if ($type > 0) {
                            $sql_1 .= " AND ".db_escape(ST_TOOL_ISSUE)." = ".db_escape($type);
                        }
                        if ($employee_code > 0) {
                            $sql_1 .= " AND hd.employee_code = ".db_escape($employee_code);
                        }
                        if (strlen($location) > 0) {
                            $sql_1 .= " AND hd.location = ".db_escape($location);
                        }
            $sql_1 .= " )";
    
        $sql_2 = "( SELECT tran.id, tran.type, tran.trans_no, 
                        pm.description project_name,
                        hd.company_code,
                        tran.employee_code,
                        hd.tran_date,
                        tran.asset_id,
                        am.description asset_description,
                        ps.description job_name,  
                        tran.quantity,
                        tran.returned_quantity,
                        tran.line_comment,
                        l.location_name 
                    FROM ".TB_PREF."daily_tool_issues hd  
                        JOIN ".TB_PREF."daily_tool_issue_details tran 
                            ON hd.id = tran.trans_no  
                                AND hd.type = tran.type  
                        JOIN ".TB_PREF."asset_locations l 
                            ON tran.loc_code = l.loc_code 
                        JOIN ".TB_PREF."asset_master am 
                            ON tran.asset_id = am.asset_id 
                        JOIN ".TB_PREF."projects_master pm 
                            ON hd.project_id = pm.project_id  
                        LEFT JOIN ".TB_PREF."project_sites ps 
                            ON tran.site_id = ps.id 
                    WHERE tran.quantity > tran.returned_quantity 
                        AND tran.type = ".db_escape(ST_DAILY_TOOL_ISSUE)." 
                        $filters ";
        
                        if ($trans_no) {
                            $sql_2 .= " AND tran.trans_no = ".db_escape($trans_no);
                        }
                        if ($type > 0) {
                            $sql_2 .= " AND tran.type = ".db_escape($type);
                        }
                        if ($employee_code > 0) {
                            $sql_2 .= " AND tran.employee_code = ".db_escape($employee_code);
                        }
                        if (strlen($location) > 0) {
                            $sql_2 .= " AND tran.location = ".db_escape($location);
                        }

            $sql_2 .= " )";
        
        $sql = " $sql_1 ";
        $sql .= " UNION $sql_2";
                    
        $sql .= " ORDER BY tran_date, id";

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

?>