<?php

function get_job_material_return_details_with_and_without_issue_req_line($req_trans_no, $return_trans_no) {
    $w_sql = get_job_material_return_details($req_trans_no, $return_trans_no);
    
    $wo_sql = get_job_material_return_details_with_job_material_issues_not_issue_req_line($req_trans_no, $return_trans_no);
    
        $sql_all = "$w_sql UNION $wo_sql";
    
        $sql = "SELECT z.* 
                FROM ($sql_all) z ";
        
        return db_query($sql, "get_job_material_return_details_with_and_without_issue_req_line");
}

function get_job_material_return_details($req_trans_no, $return_trans_no) {
    $sql = "SELECT mrd.trans_id, is_rd.trans_id issue_req_line_id, iss_d.trans_id issue_line_id, iss_d.conversion_factor, iss_d.item_type, iss_d.item_description, 
                iss_d.tran_qty, iss_d.qty, iss_d.tran_unit, iss_d.returned_quantity, iss_d.returned_alloc, am.description stock_description, am.stock_id, am.mb_flag, 
                am.stock_control_method, un.name unit_name, ps.description site_description, pt.description task_description, rtn_d.trans_id return_line_id, 
                rtn_d.tran_unit return_tran_unit, rtn_d.tran_qty return_tran_qty, rtn_d.conversion_factor return_conversion_factor, rtn_d.line_comment, iss_d.project_site_id, 
                rtn_d.project_site_id return_site_id, un_2.name return_unit_name, mrd.trans_no req_trans_no, is_rd.trans_no issue_req_trans_no, iss_d.trans_no issue_trans_no,
                iss_d.row_state issue_d_row_state, is_rd.row_state issue_req_d_row_state, mrd.row_state req_d_row_state, iss_d.standard_cost, iss_d.task_id  
            FROM ".TB_PREF."job_material_requisition_details mrd 
                JOIN ".TB_PREF."job_material_requisition_details is_rd 
                    ON mrd.trans_id = is_rd.source_line_id  
                        AND is_rd.type = ".db_escape(ST_JOB_MATERIAL_ISSUE_REQ)." 
                JOIN ".TB_PREF."job_material_issue_details iss_d 
                    ON is_rd.trans_id = iss_d.source_line_id  
                        AND iss_d.type = ".db_escape(ST_JOB_MATERIAL_ISSUE)." 
                JOIN ".TB_PREF."job_material_issues iss  
                    ON iss_d.type = iss.type   
                        AND iss_d.trans_no = iss.trans_no 
                        AND iss.approval_status IN (".implode(',', array(AS_APPROVED, AS_COMPLETED)).")";
    
    $sql_1 = " LEFT JOIN ".TB_PREF."job_material_issue_details rtn_d 
                    ON iss_d.trans_id = rtn_d.source_line_id  
                        AND rtn_d.type = ".db_escape(ST_JOB_MATERIAL_RETURN)." 
                        AND rtn_d.trans_no = ".db_escape($return_trans_no)." 
                LEFT JOIN ".TB_PREF."stock_master am 
                    ON iss_d.stock_id = am.stock_id 
                LEFT JOIN ".TB_PREF."item_units un  
                    ON iss_d.tran_unit = un.abbr  
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON iss_d.project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_tasks pt 
                    ON iss_d.task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."item_units un_2   
                    ON rtn_d.tran_unit = un_2.abbr  
            WHERE mrd.trans_no = ".db_escape($req_trans_no)." 
                AND mrd.type = ".db_escape(ST_JOB_MATERIAL_REQ)." 
            ORDER BY mrd.trans_id";
    
        return "($sql $sql_1)";
}

function get_job_material_return_details_with_job_material_issues_not_issue_req_line($req_trans_no, $return_trans_no) {
    $sql = "SELECT mrd.trans_id, 0 issue_req_line_id, iss_d.trans_id issue_line_id, iss_d.conversion_factor, iss_d.item_type, iss_d.item_description, 
                iss_d.tran_qty, iss_d.qty, iss_d.tran_unit, iss_d.returned_quantity, iss_d.returned_alloc, am.description stock_description, am.stock_id, am.mb_flag, 
                am.stock_control_method, un.name unit_name, ps.description site_description, pt.description task_description, rtn_d.trans_id return_line_id, 
                rtn_d.tran_unit return_tran_unit, rtn_d.tran_qty return_tran_qty, rtn_d.conversion_factor return_conversion_factor, rtn_d.line_comment, iss_d.project_site_id, 
                rtn_d.project_site_id return_site_id, un_2.name return_unit_name, mrd.trans_no req_trans_no, is_rd.trans_no issue_req_trans_no, iss_d.trans_no issue_trans_no,
                iss_d.row_state issue_d_row_state, is_rd.row_state issue_req_d_row_state, mrd.row_state req_d_row_state, iss_d.standard_cost, iss_d.task_id  
            FROM ".TB_PREF."job_material_requisition_details mrd 
                JOIN ".TB_PREF."job_material_requisition_details is_rd 
                    ON mrd.trans_id = is_rd.source_line_id  
                        AND is_rd.type = ".db_escape(ST_JOB_MATERIAL_ISSUE_REQ)." 
                JOIN ".TB_PREF."job_material_requisitions is_r 
                    ON is_rd.type = is_r.type   
                        AND is_rd.trans_no = is_r.trans_no 
                JOIN ".TB_PREF."job_material_issues iss  
                    ON is_r.id = iss.jmr_no   
                        AND is_r.type = ".db_escape(ST_JOB_MATERIAL_ISSUE_REQ)." 
                        AND iss.type = ".db_escape(ST_JOB_MATERIAL_ISSUE)." 
                JOIN ".TB_PREF."job_material_issue_details iss_d 
                    ON iss_d.type = iss.type   
                        AND iss_d.trans_no = iss.trans_no 
                        AND iss.approval_status IN (".implode(',', array(AS_APPROVED, AS_COMPLETED)).")";
    
    $sql_1 = " LEFT JOIN ".TB_PREF."job_material_issue_details rtn_d 
                    ON iss_d.trans_id = rtn_d.source_line_id  
                        AND rtn_d.type = ".db_escape(ST_JOB_MATERIAL_RETURN)." 
                        AND rtn_d.trans_no = ".db_escape($return_trans_no)." 
                LEFT JOIN ".TB_PREF."stock_master am 
                    ON iss_d.stock_id = am.stock_id 
                LEFT JOIN ".TB_PREF."item_units un  
                    ON iss_d.tran_unit = un.abbr  
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON iss_d.project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_tasks pt 
                    ON iss_d.task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."item_units un_2   
                    ON rtn_d.tran_unit = un_2.abbr  
            WHERE mrd.trans_no = ".db_escape($req_trans_no)." 
                AND mrd.type = ".db_escape(ST_JOB_MATERIAL_REQ)." 
                AND iss_d.source_line_id = 0 
            GROUP BY iss_d.trans_id 
            ORDER BY mrd.trans_id";
    
        return "($sql $sql_1)";
}

?>