<?php

function get_quality_check_headers($jo_id) {
    $sql = "SELECT sa.id,
                sa.trans_no,
                sa.reference,
                sa.type,
                l.location_name,
                sa.tran_date,
                IF(sa.increase = 1 , 'Yes', 'No') increase,  
                sa.comments,
                IF(sa.sample_items = 1 , 'Yes', 'No') sample_items,
                sa.approval_status 
            FROM ".TB_PREF."quality_checks sa  
                JOIN ".TB_PREF."locations l 
                    ON sa.loc_code = l.loc_code        
            WHERE sa.type = ".db_escape(ST_DIRECT_PURCHASE_REQ)." 
                AND sa.jo_id = ".db_escape($jo_id);
    
    return db_query($sql, "get_quality_check_headers");
}

function write_quality_check_header($jo_id, $type, $dpr_no, &$id, &$check_trans_no, $reference, $project_id, $location, $comments, $date, $check_row_state, $source_id=0) {
    global $Refs;
     
        if ($check_trans_no > 0) {
            $af_row = update_quality_check_header($id, $reference, $comments, $date, $check_row_state);
                if (! ($af_row > 0)) {
                    return 0;
                }
        } else {
            $check_trans_no = get_next_trans_no($type);
            $id = insert_quality_check_header($jo_id, $type, $dpr_no, $check_trans_no, $reference, $project_id, $location, $comments, $date, $source_id);
        }
    
        $Refs->save($type, $check_trans_no, $reference);

        if (! ($id > 0)) {
            return 0;
        }

        return $id;
}

function write_quality_check_header_by_type_trans_no($type, $trans_no, $reference, $project_id, $location, $comments, $date, $source_id=0) {
    global $Refs;
     
        if ($trans_no > 0) {
            $af_row = update_quality_check_header_by_type_trans_no($type, $trans_no, $reference, $location, $comments, $date);
                if (! ($af_row > 0)) {
                    return 0;
                }
        } else {
            $trans_no = get_next_trans_no($type);
            $inser_id = insert_quality_check_header($type, $trans_no, $reference, $project_id, $location, $comments, $date, $source_id);
                if (! ($inser_id > 0)) {
                    return 0;
                }
        }
    
        $Refs->save($type, $trans_no, $reference);

        return $trans_no;
}
     
function insert_quality_check_header($jo_id, $type, $dpr_no, $trans_no, $reference, $project_id, $location, $comments, $date, $source_id=0) {
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
        $sql = "INSERT INTO ".TB_PREF."quality_checks
                      ( type, dpr_no, trans_no, jo_id, 
                        reference, project_id, loc_code, comments, 
                        tran_date, created_by, created_on, source_id )         
                VALUES( ".db_escape($type).", ".db_escape($dpr_no).", ".db_escape($trans_no).", ".db_escape($jo_id).", 
                        ".db_escape($reference).", ".db_escape($project_id).", ".db_escape($location).", ".db_escape($comments).", 
                        ".db_escape($date).", ".db_escape($created_by).", ".db_escape($created_on).", ".db_escape($source_id).")";

            db_query($sql, 'insert_quality_check_header');
            return db_insert_id();        
}

function update_quality_check_header($id, $reference, $comments, $date, $check_row_state) {   
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $sql = "UPDATE ".TB_PREF."quality_checks
                SET reference = ".db_escape($reference).",
                    tran_date = ".db_escape($date).",
                    comments = ".db_escape($comments).", 
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on).",
                    row_state = ". db_new_row_state($check_row_state)." 
                WHERE id = ".db_escape($id)."   
                    AND row_state = ".db_escape($check_row_state);    

            db_query($sql, "update_quality_check_header");
            return db_num_affected_rows();
}

function insert_quality_check_line($trans_no, $type, $stock_id, $item_type, $item_description, $tran_unit, $tran_qty, $qty, $conversion_factor, $standard_cost, 
                                   $project_site_id, $line_comment, $task_id, $source_line_id=0, $line_id=0) {
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
        if ($line_id > 0) {
            $sql = "INSERT INTO ".TB_PREF."quality_check_details
                          ( trans_id, trans_no, type, stock_id, 
                            tran_unit, item_type, item_description, 
                            tran_qty, qty, conversion_factor, task_id, 
                            standard_cost, project_site_id, line_comment, 
                            created_by, created_on, source_line_id)        
                    VALUE ( ".db_escape($line_id).", ".db_escape($trans_no).", ".db_escape($type).", ".db_escape($stock_id).", 
                            ".db_escape($tran_unit).", ".db_escape($item_type).", ".db_escape($item_description).",  
                            ".db_escape($tran_qty).", ".db_escape($qty).", ".db_escape($conversion_factor).", ".db_escape($task_id).", 
                            ".db_escape($standard_cost).", ".db_escape($project_site_id).", ".db_escape($line_comment).", 
                            ".db_escape($created_by).", ".db_escape($created_on).", ".db_escape($source_line_id)." )";     
        } else {
            $sql = "INSERT INTO ".TB_PREF."quality_check_details
                          ( trans_no, type, stock_id, tran_unit, 
                            item_type, item_description, tran_qty, 
                            qty, conversion_factor, task_id, 
                            standard_cost, project_site_id, line_comment, 
                            created_by, created_on, source_line_id)        
                    VALUE ( ".db_escape($trans_no).", ".db_escape($type).", ".db_escape($stock_id).", ".db_escape($tran_unit).", 
                            ".db_escape($item_type).", ".db_escape($item_description).", ".db_escape($tran_qty).", 
                            ".db_escape($qty).", ".db_escape($conversion_factor).", ".db_escape($task_id).", 
                            ".db_escape($standard_cost).", ".db_escape($project_site_id).", ".db_escape($line_comment).", 
                            ".db_escape($created_by).", ".db_escape($created_on).", ".db_escape($source_line_id)." )";     
        }

            db_query($sql, 'insert_quality_check_line');
            return db_insert_id();  
}

function get_quality_check_header_record($id) {
    $sql = "SELECT * 
            FROM ".TB_PREF."quality_checks    
            WHERE id = ".db_escape($id);
    
        $result = db_query($sql, "get_quality_check_header_record");
        return db_fetch($result);
}

function get_quality_check_header($id) {
    $sql = "SELECT sa.*,
                l.location_name,
                pm.description project_description,
                u.real_name requestor_name 
            FROM ".TB_PREF."quality_checks sa 
                JOIN ".TB_PREF."projects_master pm 
                    ON sa.project_id = pm.project_id 
                JOIN ".TB_PREF."locations l 
                    ON sa.loc_code = l.loc_code 
                JOIN ".TB_PREF."users u 
                    ON sa.created_by = u.id 
            WHERE sa.id = ".db_escape($id);
    
        $result = db_query($sql, "get_quality_check_header");
        return db_fetch($result);
}

function get_quality_check_header_type_trans_no($type, $trans_no) {
    $sql = "SELECT sa.*,
                l.location_name,
                pm.description project_description,
                u.real_name requestor_name 
            FROM ".TB_PREF."quality_checks sa 
                JOIN ".TB_PREF."projects_master pm 
                    ON sa.project_id = pm.project_id 
                JOIN ".TB_PREF."locations l 
                    ON sa.loc_code = l.loc_code 
                JOIN ".TB_PREF."users u 
                    ON sa.created_by = u.id 
            WHERE sa.type = ".db_escape($type)." 
                AND sa.trans_no = ".db_escape($trans_no);
    
        $result = db_query($sql, "get_quality_check_header_type_trans_no");
        return db_fetch($result);
}

function get_quality_check_header_record_type_trans_no($type, $trans_no) {
    $sql = "SELECT * 
            FROM ".TB_PREF."quality_checks    
            WHERE type = ".db_escape($type)." 
                AND trans_no = ".db_escape($trans_no);
    
        $result = db_query($sql, "get_quality_check_header_record_type_trans_no");
        return db_fetch($result);
}

function get_quality_check_details($trans_no, $type) {
    $sql = "SELECT ard.*,
                am.description stock_description,
                am.stock_id,
                am.mb_flag,
                un.name unit_name,
                ps.description site_description, 
                pt.description task_description, 
                am.stock_control_method   
            FROM ".TB_PREF."quality_check_details ard 
                LEFT JOIN ".TB_PREF."stock_master am 
                    ON ard.stock_id = am.stock_id 
                LEFT JOIN ".TB_PREF."item_units un  
                    ON ard.tran_unit = un.abbr  
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON ard.project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_tasks pt 
                    ON ard.task_id = pt.task_id 
            WHERE ard.trans_no = ".db_escape($trans_no)." 
                AND ard.type = ".db_escape($type);
    
        return db_query($sql, "get_quality_check_details");
}

function get_quality_check_detail_lines_by_filters($trans_id=0, $trans_no=0, $type=null, $stock_id=null, $source_line_id=0, $has_source_line=null) {
    $sql = "SELECT *   
            FROM ".TB_PREF."quality_check_details  
            WHERE 1 = 1";
    
        if ($trans_id > 0) {
            $sql .= " AND trans_id = ".db_escape($trans_id);
        }
        if ($trans_no > 0) {
            $sql .= " AND trans_no = ".db_escape($trans_no);
        }
        if ($type !== null) {
            $sql .= " AND type = ".db_escape($type);
        }
        if ($stock_id !== null) {
            $sql .= " AND stock_id = ".db_escape($stock_id);
        }
        if ($source_line_id > 0) {
            $sql .= " AND source_line_id = ".db_escape($source_line_id);
        }
        
        if ($has_source_line === true) {
            $sql .= " AND source_line_id > 0 ";
        } elseif ($has_source_line === false) {
            $sql .= " AND source_line_id = 0 ";
        }
    
        return db_query($sql, "get_quality_check_detail_lines_by_filters");
}

function get_direct_purchase_requisition_with_quality_check_details($req_trans_no, $check_trans_no, $type=ST_QUALITY_CHECK) {
    $sql = "SELECT ard.*,
                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, 
                qchd.trans_id check_line_id, 
                qchd.item_type check_item_type, 
                qchd.stock_id check_stock_id, 
                qchd.item_description check_item_description, 
                qchd.tran_unit check_tran_unit, 
                qchd.tran_qty check_tran_qty, 
                qchd.project_site_id check_site_id,  
                qchd.line_comment check_line_comment    
            FROM ".TB_PREF."direct_purchase_requisition_details ard 
                LEFT JOIN ".TB_PREF."stock_master am 
                    ON ard.stock_id = am.stock_id 
                LEFT JOIN ".TB_PREF."item_units un  
                    ON ard.tran_unit = un.abbr  
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON ard.project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_tasks pt 
                    ON ard.task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."quality_check_details qchd 
                    ON ard.trans_id = qchd.source_line_id  
                        AND qchd.type = ".db_escape($type)." 
                        AND qchd.trans_no = ".db_escape($check_trans_no)." 
            WHERE ard.trans_no = ".db_escape($req_trans_no)." 
                AND ard.type = ".db_escape(ST_DIRECT_PURCHASE_REQ)." 
            ORDER BY ard.trans_id";
    
        return db_query($sql, "get_direct_purchase_requisition_with_quality_check_details");
}

function get_quality_checks_without_dp_req_details($check_trans_no) {
    $sql = "SELECT ard.*,
                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, 
                ard.trans_id check_line_id, 
                ard.item_type check_item_type, 
                ard.stock_id check_stock_id, 
                ard.item_description check_item_description, 
                ard.tran_unit check_tran_unit, 
                ard.tran_qty check_tran_qty  
            FROM ".TB_PREF."quality_check_details ard 
                LEFT JOIN ".TB_PREF."stock_master am 
                    ON ard.stock_id = am.stock_id 
                LEFT JOIN ".TB_PREF."item_units un  
                    ON ard.tran_unit = un.abbr  
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON ard.project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_tasks pt 
                    ON ard.task_id = pt.task_id 
            WHERE ard.trans_no = ".db_escape($check_trans_no)." 
                AND ard.type = ".db_escape(ST_QUALITY_CHECK)." 
                AND ard.source_line_id = 0  
            ORDER BY ard.trans_id";
    
        return db_query($sql, "get_quality_checks_without_dp_req_details");
}

function get_quality_check_detail_records_with_no_source($type, $trans_no) {
    $sql = "SELECT * 
            FROM ".TB_PREF."quality_check_details  
            WHERE type = ".db_escape($type)." 
                AND trans_no = ".db_escape($trans_no)." 
                AND source_line_id = 0";
    
        return db_query($sql, "get_quality_check_detail_records_with_no_source");
}

function get_quality_check_details_lines_count($trans_no, $type, $has_source_line=null, $not_id=0) {
    $sql = "SELECT COUNT(trans_id) lines_count 
            FROM ".TB_PREF."quality_check_details 
            WHERE trans_no = ".db_escape($trans_no)." 
                AND type = ".db_escape($type);
    
        if ($has_source_line === false) {
            $sql .= " AND source_line_id = 0";
        } elseif ($has_source_line === true) {
            $sql .= " AND source_line_id > 0";
        }
        
        if ($not_id > 0) {
            $sql .= " AND trans_id <> ".db_escape($not_id);
        }
    
        $result = db_query($sql, 'get_quality_check_details_lines_count');
        return db_result_value($result, 'lines_count');
}

function get_sql_for_quality_check_inquiry($trans_no, $reference, $project_id, $from_date, $to_date, $loc_code, $trans_status) {
    $sql = "SELECT  sa.id,
                sa.trans_no,
                sa.reference,
                sa.type,
                l.location_name,
                sa.tran_date,
                pm.description project_description,
                IF(sa.increase = 1 , 'Yes', 'No') increase,  
                sa.comments,
                IF(sa.sample_items = 1 , 'Yes', 'No') sample_items,
                sa.approval_status 
            FROM ".TB_PREF."quality_checks sa  
                JOIN ".TB_PREF."projects_master pm 
                    ON sa.project_id = pm.project_id 
                JOIN ".TB_PREF."locations l 
                    ON sa.loc_code = l.loc_code        
            WHERE sa.type = ".db_escape(ST_DIRECT_PURCHASE_REQ);
    
        if ($trans_no > 0){
            $sql .= " AND sa.trans_no = ".db_escape($trans_no);
        } else {
        
            if (strlen($reference) > 0) {
                $sql .= " AND sa.reference = ".db_escape($reference);
            }

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

            $sql .= " AND sa.tran_date >= ".db_escape(date2sql($from_date))." AND sa.tran_date <= ".db_escape(date2sql($to_date)); 

            if ($loc_code != '') {        
                $sql .= " AND sa.loc_code = ".db_escape($loc_code);
            }
            
            if ($trans_status > -1) {        
                $sql .= " AND sa.approval_status = ".db_escape($trans_status);
            }                      
        }
    
        $sql .= " ORDER BY sa.tran_date, sa.id";
    
    return $sql;
}

function update_quality_check_header_approval_status($type, $trans_no, $pre_status, $new_status) {
    $approved_by = $_SESSION['wa_current_user']->user;
    $approved_on = date('Y-m-d H:i:s');

        $sql = "UPDATE ".TB_PREF."quality_checks
                SET approval_status = ".db_escape($new_status).",
                    approved_by = ".db_escape($approved_by).", 
                    approved_on = ".db_escape($approved_on)." 
                WHERE type = ".db_escape($type)."   
                    AND trans_no = ".db_escape($trans_no)."   
                    AND approval_status = ".db_escape($pre_status);    

            db_query($sql, "update_quality_check_header_approval_status");
            return db_num_affected_rows();
}

function get_quality_check_lines_total_by_source_line($type, $source_line_id, $not_trans_id=0, $approval_status_array=array(AS_REJECTED), $not_in=true) {
    $sql = "SELECT SUM(ln.qty) total_qty 
            FROM ".TB_PREF."quality_check_details ln 
                JOIN ".TB_PREF."quality_checks hd  
                    ON ln.type = hd.type 
                        AND ln.trans_no = hd.trans_no  
            WHERE ln.source_line_id = ".db_escape($source_line_id)." 
                AND hd.type = ".db_escape($type);
    
        if ($not_trans_id > 0) {
            $sql .= " AND ln.trans_id <> ".db_escape($not_trans_id);
        }
    
        if (count($approval_status_array) > 0) {
            if ($not_in === true) {
                $sql .= " AND hd.approval_status NOT IN (".implode(",", $approval_status_array).")";
            } elseif ($not_in === false) {
                $sql .= " AND hd.approval_status IN (".implode(",", $approval_status_array).")";
            } 
        }
    
        $result = db_query($sql, 'get_quality_check_lines_total_by_source_line');
        return db_result_value($result, 'total_qty');
}

function get_quality_check_lines_total_by_source_line_approval_status($type, $source_line_id, $approval_status, $not_trans_id=0) {
    $sql = "SELECT SUM(ln.qty) total_qty 
            FROM ".TB_PREF."quality_check_details ln 
                JOIN ".TB_PREF."quality_checks hd  
                    ON ln.type = hd.type 
                        AND ln.trans_no = hd.trans_no  
            WHERE ln.source_line_id = ".db_escape($source_line_id)." 
                AND hd.type = ".db_escape($type)."
                AND hd.approval_status = ".db_escape($approval_status);
    
        if ($not_trans_id > 0) {
            $sql .= " AND ln.trans_id <> ".db_escape($not_trans_id);
        }
    
        $result = db_query($sql, 'get_quality_check_lines_total_by_source_line_approval_status');
        return db_result_value($result, 'total_qty');
}

function get_sql_for_quality_checks_inquiry($trans_no, $req_trans_no, $jo_id, $has_job, $reference, $project_id, $from_date, $to_date, $loc_code, $ap_status=-1, 
                                            $only_outstanding=false) {
    $sql = "SELECT sa.jo_id, req.trans_no req_trans_no,
                sa.trans_no,
                sa.reference,
                sa.type,
                l.location_name,
                sa.tran_date,
                pm.description project_description,
                IF(sa.increase = 1 , 'Yes', 'No') increase,  
                sa.comments,
                sa.approval_status, 
                sa.id, 
                sa.project_id  
            FROM ".TB_PREF."quality_checks sa  
                JOIN ".TB_PREF."direct_purchase_requisitions req 
                    ON sa.dpr_no = req.id 
                JOIN ".TB_PREF."projects_master pm 
                    ON sa.project_id = pm.project_id 
                JOIN ".TB_PREF."locations l 
                    ON sa.loc_code = l.loc_code        
            WHERE sa.type = ".db_escape(ST_QUALITY_CHECK);
    
            if ($only_outstanding === true) {        
                $sql .= " AND sa.approval_status IN ( ".implode(',', array(AS_APPROVED)).")";
            }
    
            if ($req_trans_no > 0 || $trans_no > 0 || $jo_id > 0) {
                if ($trans_no > 0) {
                    $sql .= " AND sa.trans_no = ".db_escape($trans_no);
                }
                
                if ($req_trans_no > 0) {
                    $sql .= " AND req.trans_no = ".db_escape($req_trans_no);
                }
                
                if ($jo_id > 0){
                    $sql .= " AND sa.jo_id = ".db_escape($jo_id);
                } 

            } else {
                
                if ($has_job == 'Yes') {
                    $sql .= " AND sa.jo_id > 0";
                } elseif ($has_job == 'No') {
                    $sql .= " AND sa.jo_id = 0";
                } 
                
                if (strlen($reference) > 0) {
                    $sql .= " AND sa.reference = ".db_escape($reference);
                }

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

                if ($loc_code != '') {        
                    $sql .= " AND sa.loc_code = ".db_escape($loc_code);
                }
                
                $sql .= " AND sa.tran_date >= ".db_escape(date2sql($from_date))." AND sa.tran_date <= ".db_escape(date2sql($to_date)); 

                if ($ap_status > -1) {        
                    $sql .= " AND sa.approval_status = ".db_escape($ap_status);
                }  
            }
    
        $sql .= " ORDER BY sa.tran_date DESC, sa.trans_no DESC";
    
        return $sql;
}

function get_sql_for_dp_receipts_returns_inquiry($trans_no, $req_trans_no, $jo_id, $has_job, $ref, $type, $project_id, $from_date, $to_date, $loc_code, $ap_status) {
    $sql = "SELECT sa.jo_id, req.trans_no req_trans_no,
                req.type req_type,
                sa.trans_no,
                sa.reference,
                sa.type,
                l.location_name,
                sa.tran_date,
                pm.description project_description,
                IF(sa.increase = 1 , 'Yes', 'No') increase,  
                sa.comments,
                sa.approval_status, 
                sa.id,   
                sa.project_id   
            FROM ".TB_PREF."quality_checks sa  
                JOIN ".TB_PREF."quality_checks req 
                    ON sa.dpr_no = req.id 
                JOIN ".TB_PREF."projects_master pm 
                    ON sa.project_id = pm.project_id 
                JOIN ".TB_PREF."locations l 
                    ON sa.loc_code = l.loc_code        
            WHERE sa.type = ".db_escape($type);
    
        if ($req_trans_no > 0 || $trans_no > 0 || $jo_id > 0) {
            if ($req_trans_no > 0) {
                $sql .= " AND req.trans_no = ".db_escape($req_trans_no);
            }
            
            if ($trans_no > 0) {
                $sql .= " AND sa.trans_no = ".db_escape($trans_no);
            }
            
            if ($jo_id > 0){
                $sql .= " AND sa.jo_id = ".db_escape($jo_id);
            } 

        } else {
                
            if ($has_job == 'Yes') {
                $sql .= " AND sa.jo_id > 0";
            } elseif ($has_job == 'No') {
                $sql .= " AND sa.jo_id = 0";
            } 
            
            if (strlen($ref) > 0) {
                $sql .= " AND sa.reference = ".db_escape($ref);
            }
            
            if ($type > -1) {
                $sql .= " AND sa.type = ".db_escape($type);
            }
            
            if($project_id > 0){
                $sql .= " AND sa.project_id = ".db_escape($project_id);
            }

            if ($loc_code != '') {        
                $sql .= " AND sa.loc_code = ".db_escape($loc_code);
            }
            
            $sql .= " AND sa.tran_date >= ".db_escape(date2sql($from_date))." AND sa.tran_date <= ".db_escape(date2sql($to_date)); 
            
            if ($ap_status > -1) {        
                $sql .= " AND sa.approval_status = ".db_escape($ap_status);
            }  
        }
    
        $sql .= " ORDER BY sa.tran_date DESC, sa.trans_no DESC";
    
        return $sql;
}

function get_sql_for_outstanding_direct_purchase_receipts($trans_no, $req_trans_no, $jo_id, $has_job, $reference, $project_id, $from_date, $to_date, $loc_code) {
    $sql = "SELECT rcp.jo_id, req.trans_no req_trans_no,
                req.type req_type,
                rcp.trans_no,
                rcp.reference,
                rcp.type,
                l.location_name,
                rcp.tran_date,
                pm.description project_description,
                rcp.comments,
                rcp.approval_status, 
                rcp.id  
            FROM ".TB_PREF."quality_checks rcp  
                JOIN ".TB_PREF."quality_check_details rcp_d 
                    ON rcp.type = rcp_d.type 
                        AND rcp.trans_no = rcp_d.trans_no 
                JOIN ".TB_PREF."quality_checks req 
                    ON rcp.dpr_no = req.id 
                JOIN ".TB_PREF."projects_master pm 
                    ON rcp.project_id = pm.project_id 
                JOIN ".TB_PREF."locations l 
                    ON rcp.loc_code = l.loc_code        
            WHERE rcp.type IN (".implode(',', array(ST_DIRECT_PURCHASE_RECEIPT)).") 
                AND rcp.approval_status = ".db_escape(AS_APPROVED)." 
                AND rcp_d.qty > rcp_d.returned_quantity";
    
        if ($req_trans_no > 0 || $trans_no > 0 || $jo_id > 0) {
            if ($req_trans_no > 0) {
                $sql .= " AND req.trans_no = ".db_escape($req_trans_no);
            }
            
            if ($trans_no > 0) {
                $sql .= " AND rcp.trans_no = ".db_escape($trans_no);
            }
            
            if ($jo_id > 0){
                $sql .= " AND rcp.jo_id = ".db_escape($jo_id);
            } 

        } else {
                
            if ($has_job == 'Yes') {
                $sql .= " AND rcp.jo_id > 0";
            } elseif ($has_job == 'No') {
                $sql .= " AND rcp.jo_id = 0";
            } 
            
            if (strlen($reference) > 0) {
                $sql .= " AND rcp.reference = ".db_escape($reference);
            }
            
            if($project_id > 0){
                $sql .= " AND rcp.project_id = ".db_escape($project_id);
            }

            if ($loc_code != '') {        
                $sql .= " AND rcp.loc_code = ".db_escape($loc_code);
            }
            
            $sql .= " AND rcp.tran_date >= ".db_escape(date2sql($from_date))." AND rcp.tran_date <= ".db_escape(date2sql($to_date)); 
        }
    
        $sql .= " GROUP BY rcp.trans_no 
                  ORDER BY rcp.tran_date, rcp.type, rcp.trans_no";
    
        return $sql;
}

function delete_quality_check_detail_lines($type, $trans_no, $has_source_line=null) {
    $sql = "DELETE FROM ".TB_PREF."quality_check_details 
            WHERE type = ".db_escape($type)." 
                AND trans_no = ".db_escape($trans_no);
    
        if ($has_source_line === false) {
            $sql .= " AND source_line_id = 0";
        } elseif ($has_source_line === true) {
            $sql .= " AND source_line_id > 0";
        }
    
	db_query($sql, "delete_quality_check_detail_lines");
        return db_num_affected_rows();
}

function delete_quality_check_detail_line_by_id($id) {
    $sql = "DELETE FROM ".TB_PREF."quality_check_details 
            WHERE trans_id = ".db_escape($id);
    
	db_query($sql, "delete_quality_check_detail_line_by_id");
        return db_num_affected_rows();
}

function get_quality_check_detail_record($line_id) {
    $sql = "SELECT *
            FROM ".TB_PREF."quality_check_details 
            WHERE trans_id = ".db_escape($line_id);
    
	$result = db_query($sql, "get_quality_check_detail_record");
        return db_fetch($result);
}

function get_quality_check_detail_record_type_trans_no_line_no($type, $trans_no, $line_id) {
    $sql = "SELECT *
            FROM ".TB_PREF."quality_check_details 
            WHERE type = ".db_escape($type)." 
                AND trans_no = ".db_escape($trans_no)." 
                AND trans_id = ".db_escape($line_id);
    
	$result = db_query($sql, "get_quality_check_detail_record_type_trans_no_line_no");
        return db_fetch($result);
}

function get_quality_check_details_line_by_type_trans_no_line_no($type, $trans_no, $line_id) {
    $sql = "SELECT ard.*,
                am.description stock_description,
                am.stock_id,
                am.mb_flag,
                un.name tran_unit_name,
                un2.name base_unit_name,
                ps.description site_description, 
                pt.description task_description 
            FROM ".TB_PREF."quality_check_details ard 
                LEFT JOIN ".TB_PREF."stock_master am 
                    ON ard.stock_id = am.stock_id 
                LEFT JOIN ".TB_PREF."item_units un  
                    ON ard.tran_unit = un.abbr  
                LEFT JOIN ".TB_PREF."item_units un2   
                    ON am.units = un2.abbr  
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON ard.project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_tasks pt 
                    ON ard.task_id = pt.task_id 
            WHERE ard.trans_no = ".db_escape($trans_no)." 
                AND ard.type = ".db_escape($type)." 
                AND ard.trans_id = ".db_escape($line_id);
    
        $result = db_query($sql, "get_quality_check_details_line_by_type_trans_no_line_no");
        return db_fetch($result);
}

function update_quality_check_row_state($type, $trans_no, $row_state) {
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $sql = "UPDATE ".TB_PREF."quality_checks 
                SET row_state = ". db_new_row_state($row_state).",
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on)." 
                WHERE type = ".db_escape($type)." 
                    AND trans_no = ".db_escape($trans_no)." 
                    AND row_state = ".db_escape($row_state);

            db_query($sql, "update_quality_check_row_state");
            return db_num_affected_rows();
}

function get_quality_check_headers_by_dpr_no_type($dpr_no, $type) {
    $sql = "SELECT sa.*,
                l.location_name,
                pm.description project_description,
                u.real_name requestor_name 
            FROM ".TB_PREF."quality_checks sa 
                JOIN ".TB_PREF."projects_master pm 
                    ON sa.project_id = pm.project_id 
                JOIN ".TB_PREF."locations l 
                    ON sa.loc_code = l.loc_code 
                JOIN ".TB_PREF."users u 
                    ON sa.created_by = u.id 
            WHERE sa.dpr_no = ".db_escape($dpr_no)." 
                AND sa.type = ".db_escape($type);
    
        return db_query($sql, "get_quality_check_headers_by_dpr_no_type");
}

function update_direct_purchase_requisition_line_total_checked_qty($trans_id, $type, $req_line_row_state=0) {
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $total_qty_ = get_quality_check_lines_total_by_source_line($type, $trans_id);
    $total_qty = abs($total_qty_);
    
            $sql = "UPDATE ".TB_PREF."direct_purchase_requisition_details   
                    SET checked_quantity = ".db_escape($total_qty).",  
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($updated_on).",  
                        row_state = ". db_new_row_state($req_line_row_state)." 
                    WHERE trans_id = ".db_escape($trans_id)." 
                        AND qty >= $total_qty - returned_quantity";

            db_query($sql, 'update_direct_purchase_requisition_line_total_checked_qty');
            return db_num_affected_rows();
}

function update_direct_purchase_requisition_lines_total_checked_qty($trans_no, $type) {
    $updated_by = $_SESSION['wa_current_user']->user;
    
    $result = get_quality_check_detail_lines_by_filters(trans_no: $trans_no, type: $type, has_source_line: true);
        while ($row = db_fetch($result)) {
            $source_line_id = $row["source_line_id"];
            
            $total_qty_ = get_quality_check_lines_total_by_source_line($type, $source_line_id);
            $total_qty = abs($total_qty_);
            
            $updated_on = date('Y-m-d H:i:s');
            
                $sql = "UPDATE ".TB_PREF."direct_purchase_requisition_details   
                        SET checked_quantity = ".db_escape($total_qty).",  
                            updated_by = ".db_escape($updated_by).", 
                            updated_on = ".db_escape($updated_on).",  
                            row_state = row_state + 1  
                        WHERE trans_id = ".db_escape($source_line_id)." 
                            AND qty >= $total_qty";

                db_query($sql, 'update_direct_purchase_requisition_lines_total_checked_qty');
                $af_row = db_num_affected_rows();
                
                if ($af_row != 1) {
                    return array("success" => false, "message" => "Could not update direct purchase requisitions details line #$source_line_id total checked quantity.");
                }
        }
    
        return array("success" => true, "message" => "");
}

function get_quality_check_with_direct_purchase_receipt_details($qck_trans_no, $trans_no, $type=ST_DIRECT_PURCHASE_RECEIPT) {
    $sql = "SELECT qckd.*,
                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, 
                rcpd.trans_id rcp_line_id, 
                rcpd.tran_unit rcp_tran_unit, 
                rcpd.tran_qty rcp_tran_qty, 
                rcpd.item_type rcp_item_type, 
                rcpd.stock_id rcp_stock_id, 
                rcpd.project_site_id rcp_site_id,    
                rcpd.line_comment rcp_line_comment,    
                rcpd.standard_cost unit_price,    
                rcpd.item_description rcp_item_description,    
                am2.stock_control_method rcp_stock_control_method 
            FROM ".TB_PREF."quality_check_details qckd 
                LEFT JOIN ".TB_PREF."stock_master am 
                    ON qckd.stock_id = am.stock_id 
                LEFT JOIN ".TB_PREF."item_units un  
                    ON qckd.tran_unit = un.abbr  
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON qckd.project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_tasks pt 
                    ON qckd.task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."quality_check_details rcpd 
                    ON qckd.trans_id = rcpd.source_line_id  
                        AND rcpd.type = ".db_escape($type)." 
                        AND rcpd.trans_no = ".db_escape($trans_no)." 
                LEFT JOIN ".TB_PREF."stock_master am2  
                    ON rcpd.stock_id = am2.stock_id 
            WHERE qckd.trans_no = ".db_escape($qck_trans_no)." 
                AND qckd.type = ".db_escape(ST_QUALITY_CHECK)." 
            ORDER BY qckd.trans_id";
    
        return db_query($sql, "get_quality_check_with_direct_purchase_receipt_details");
}

function get_direct_purchase_receipt_with_quality_check_lines_details($rcp_trans_no, $type) {
    $sql = "SELECT rcp_d.*,
                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, 
                qc_d.trans_id qc_line_id, 
                qc_d.tran_unit qc_tran_unit, 
                qc_d.tran_qty qc_tran_qty, 
                qc_d.item_type qc_item_type, 
                qc_d.stock_id qc_stock_id, 
                qc_d.project_site_id qc_site_id,    
                qc_d.line_comment qc_line_comment,    
                qc_d.item_description qc_item_description,    
                am2.stock_control_method qc_stock_control_method,  
                rq_d.trans_id req_line_id, 
                rq_d.item_type req_item_type, 
                rq_d.stock_id req_stock_id, 
                rq_d.item_description req_item_description  
            FROM ".TB_PREF."quality_check_details rcp_d 
                LEFT JOIN ".TB_PREF."stock_master am 
                    ON rcp_d.stock_id = am.stock_id 
                LEFT JOIN ".TB_PREF."item_units un  
                    ON rcp_d.tran_unit = un.abbr  
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON rcp_d.project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_tasks pt 
                    ON rcp_d.task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."quality_check_details qc_d 
                    ON rcp_d.source_line_id = qc_d.trans_id   
                        AND qc_d.type = ".db_escape(ST_QUALITY_CHECK)." 
                LEFT JOIN ".TB_PREF."stock_master am2  
                    ON qc_d.stock_id = am2.stock_id 
                LEFT JOIN ".TB_PREF."direct_purchase_requisition_details rq_d 
                    ON qc_d.source_line_id = rq_d.trans_id   
                        AND rq_d.type = ".db_escape(ST_DIRECT_PURCHASE_REQ)." 
            WHERE rcp_d.trans_no = ".db_escape($rcp_trans_no)." 
                AND rcp_d.type = ".db_escape($type)." 
            ORDER BY rcp_d.trans_id";
    
        return db_query($sql, "get_direct_purchase_receipt_with_quality_check_lines_details");
}

function get_direct_purchase_receipt_with_direct_purchase_return_details($rcp_trans_no, $return_trans_no) {
    $sql = "SELECT rcpd.*, 
                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, 
                rtnd.trans_id rtn_line_id, 
                rtnd.tran_unit return_tran_unit, 
                rtnd.tran_qty return_tran_qty,   
                rtnd.conversion_factor return_conversion_factor,   
                un_2.name return_unit_name 
            FROM ".TB_PREF."quality_check_details rcpd 
                JOIN ".TB_PREF."quality_checks rcp  
                    ON rcpd.trans_no = rcp.trans_no  
                        AND rcpd.type = rcp.type 
                LEFT JOIN ".TB_PREF."stock_master am 
                    ON rcpd.stock_id = am.stock_id 
                LEFT JOIN ".TB_PREF."item_units un  
                    ON rcpd.tran_unit = un.abbr  
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON rcpd.project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_tasks pt 
                    ON rcpd.task_id = pt.task_id 
                            
                LEFT JOIN ".TB_PREF."quality_check_details rtnd 
                    ON rcpd.trans_id = rtnd.source_line_id  
                        AND rtnd.type = ".db_escape(ST_DIRECT_PURCHASE_RETURN)." 
                        AND rtnd.trans_no = ".db_escape($return_trans_no)." 
                LEFT JOIN ".TB_PREF."item_units un_2   
                    ON rtnd.tran_unit = un_2.abbr  
            WHERE rcpd.trans_no = ".db_escape($rcp_trans_no)." 
                AND rcpd.type = ".db_escape(ST_DIRECT_PURCHASE_RECEIPT)." 
                AND rcp.approval_status = ".db_escape(AS_APPROVED)." 
            ORDER BY rcpd.trans_id";
    
        return db_query($sql, "get_direct_purchase_receipt_with_direct_purchase_return_details");
}

function save_quality_check_lines_grn_returned_qty_total($type, $trans_no, $grn_return_type) {
    $result = get_quality_check_lines($type, $trans_no);
        while ($row = db_fetch($result)) {
            $id = $row['trans_id'];
            $row_state = $row['row_state'];
            
            $cal_up_msg = calculate_and_update_quality_check_line_grn_returned_qty_total($id, $grn_return_type, $row_state);
                if ($cal_up_msg['success'] !== true) {
                    return $cal_up_msg;
                }
        }
        
        return array("success" => true, "message" => '');
}

function calculate_and_update_quality_check_line_grn_returned_qty_total($id, $type, $qch_line_row_state=0, $filter_type=null) {
    global $systypes_array;
    
    $qch_type = ST_QUALITY_CHECK;
    
    if ($type == ST_DIRECT_PURCHASE_RECEIPT) {
        $af_row = update_quality_check_line_grn_alloc_qty_and_grn_qty($id, $qch_line_row_state);
            if (! ($af_row == 1)) {
                return array("success" => false, "message" => "Could not update Q.Check line #$id total GRN qty.");
            } 
    } elseif ($type == ST_DIRECT_PURCHASE_RETURN) {
        $af_row = update_quality_check_line_return_alloc_qty_and_return_qty($id, $qch_line_row_state, $filter_type);
            if (! ($af_row == 1)) {
                return array("success" => false, "message" => "Could not update Q.Check line #$id total returned qty.");
            } 
    }
    
        return array("success" => true, "message" => $systypes_array[$qch_type]." Detail Q.Check Line #$id GRN and Returned qty has been updated.");
}

function get_dpr_lines_total_qty($type, $source_line_id, $ap_array, $not_trans_id=0) {
    $sql = "SELECT SUM(ln.qty) total_qty 
            FROM ".TB_PREF."quality_check_details ln 
                JOIN ".TB_PREF."quality_checks hd  
                    ON ln.type = hd.type 
                        AND ln.trans_no = hd.trans_no  
            WHERE ln.source_line_id = ".db_escape($source_line_id)." 
                AND hd.type = ".db_escape($type)."
                AND hd.approval_status IN (". implode(',', $ap_array).")";
    
        if ($not_trans_id > 0) {
            $sql .= " AND ln.trans_id <> ".db_escape($not_trans_id);
        }
    
        $result = db_query($sql, 'get_dpr_lines_total_qty');
        return db_result_value($result, 'total_qty');
}

function update_quality_check_line_grn_alloc_qty_and_grn_qty($trans_id, $qch_line_row_state=0) {
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $total_grn_alloc_qty_ = get_dpr_lines_total_qty(ST_DIRECT_PURCHASE_RECEIPT, $trans_id, array(AS_NEW, AS_WITH_THE_APPROVER, AS_AWAITING_APPROVAL, 
                                                        AS_BACK_TO_ORIGINATOR));
        $total_grn_alloc_qty = abs($total_grn_alloc_qty_);

        $total_return_qty_ = get_dpr_lines_total_qty(ST_DIRECT_PURCHASE_RETURN, $trans_id, array(AS_APPROVED));
        $total_return_qty = abs($total_return_qty_);

        $total_grn_qty_ = get_dpr_lines_total_qty(ST_DIRECT_PURCHASE_RECEIPT, $trans_id, array(AS_APPROVED));
        $total_grn_qty = abs($total_grn_qty_);

        $exact_grn_qty = $total_grn_qty - $total_return_qty;
        $grn_qty_grn_alloc_qty = $exact_grn_qty + $total_grn_alloc_qty;
            
            $sql = "UPDATE ".TB_PREF."quality_check_details  
                    SET grn_alloc = ".db_escape($total_grn_alloc_qty).",  
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($updated_on).",  
                        row_state = ".db_new_row_state($qch_line_row_state)." 
                    WHERE trans_id = ".db_escape($trans_id)." 
                        AND qty >= $grn_qty_grn_alloc_qty";
            
            db_query($sql, 'update_quality_check_line_grn_alloc_qty_and_grn_qty');
                $af_row = db_num_affected_rows();
            
                    if ($af_row != 1) {
                        return 0;
                    }
            
    $nw_qch_line_row_state = db_new_row_state($qch_line_row_state);
    $nw_updated_on = date('Y-m-d H:i:s');
            
            $sql_1 = "UPDATE ".TB_PREF."quality_check_details  
                    SET grn_quantity = ".db_escape($total_grn_qty).",  
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($nw_updated_on).",  
                        row_state = ".db_new_row_state($nw_qch_line_row_state)." 
                    WHERE trans_id = ".db_escape($trans_id)." 
                        AND qty >= $grn_qty_grn_alloc_qty";
            
            db_query($sql_1, 'update_quality_check_line_grn_alloc_qty_and_grn_qty');
                $af_row_2 = db_num_affected_rows();

                    if ($af_row_2 != 1) {
                        return 0;
                    } 
                    
        return 1;
}

function update_quality_check_line_return_alloc_qty_and_return_qty($trans_id, $qch_line_row_state=0, $filter_type= ST_DIRECT_PURCHASE_RECEIPT) {
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $ap_status = array(AS_APPROVED, AS_COMPLETED);
        $total_return_alloc_qty_ = get_quality_check_lines_column_total_by_source_line_approval_status($filter_type, $trans_id, $ap_status, 0, "returned_alloc");
        $total_return_alloc_qty = abs($total_return_alloc_qty_);

        $total_return_qty_ = get_quality_check_lines_column_total_by_source_line_approval_status($filter_type, $trans_id, $ap_status, 0, "returned_quantity");
        $total_return_qty = abs($total_return_qty_);

        $total_all_returned = $total_return_alloc_qty + $total_return_qty;
            
            $sql = "UPDATE ".TB_PREF."quality_check_details  
                    SET returned_alloc = ".db_escape($total_return_alloc_qty).",  
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($updated_on).",  
                        row_state = ".db_new_row_state($qch_line_row_state)." 
                    WHERE trans_id = ".db_escape($trans_id)." 
                        AND qty >= $total_all_returned";
            
            db_query($sql, 'update_quality_check_line_return_alloc_qty_and_return_qty');
                $af_row = db_num_affected_rows();
            
                    if ($af_row != 1) {
                        return 0;
                    }
            
    $nw_qch_line_row_state = db_new_row_state($qch_line_row_state);
    $nw_updated_on = date('Y-m-d H:i:s');
            
            $sql_1 = "UPDATE ".TB_PREF."quality_check_details  
                    SET returned_quantity = ".db_escape($total_return_qty).",  
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($nw_updated_on).",  
                        row_state = ".db_new_row_state($nw_qch_line_row_state)." 
                    WHERE trans_id = ".db_escape($trans_id)." 
                        AND qty >= $total_all_returned";
            
            db_query($sql_1, 'update_quality_check_line_return_alloc_qty_and_return_qty');
                $af_row_2 = db_num_affected_rows();

                    if ($af_row_2 != 1) {
                        return 0;
                    }

        return 1;
}

function get_allocated_quality_check_lines($type, $trans_no) {
    $sql = "SELECT * 
            FROM ".TB_PREF."quality_check_details  
            WHERE type = ".db_escape($type)." 
                AND trans_no = ".db_escape($trans_no)." 
                AND ( grn_alloc > 0 OR returned_alloc > 0 )"; 
    
        return db_query($sql, "get_allocated_quality_check_lines");
}

function check_all_quality_check_lines_fully_grn($type, $trans_no) {
    $alloc_recs = get_allocated_quality_check_lines($type, $trans_no);
        if (db_num_rows($alloc_recs) > 0) {
            return false;
        }
    
    $sql = "SELECT qty, SUM(grn_quantity - returned_quantity) exact_grn_qty 
            FROM ".TB_PREF."quality_check_details  
            WHERE type = ".db_escape($type)." 
                AND trans_no = ".db_escape($trans_no)." 
            GROUP BY trans_id 
            HAVING exact_grn_qty < qty ";
            
        $result = db_query($sql, "check_all_quality_check_lines_fully_grn");
            if (db_num_rows($result) > 0) {
                return false;
            } 
            
        return true;
}

function get_quality_check_lines($type, $trans_no) {
    $sql = "SELECT * 
            FROM ".TB_PREF."quality_check_details  
            WHERE type = ".db_escape($type)." 
                AND trans_no = ".db_escape($trans_no); 
    
        return db_query($sql, "get_quality_check_lines");
}

function save_direct_purchase_receipt_lines_returned_qty_total($rcp_type, $rcp_trans_no) {
    global $systypes_array;
    
    $qc_line_ids_array = array();
    $result = get_quality_check_lines($rcp_type, $rcp_trans_no);
        while ($row = db_fetch($result)) {
            $id = $row['trans_id'];
            $row_state = $row['row_state'];
            $source_line_id = $row['source_line_id'];
            
            $af_row = update_direct_purchase_receipt_line_returned_alloc_qty_and_returned_qty($id, $row_state);
                if ($af_row != 1) {
                    return array("success" => false, "message" => "Could not update ".$systypes_array[$rcp_type]." GRN line #$id total returned qty.");
                }
            
                if ($source_line_id > 0) {
                    $qc_line_ids_array[$source_line_id] = $source_line_id;
                }
        }
        
        return array("success" => true, "message" => $systypes_array[$rcp_type]." returned qty has been saved.", "qc_line_ids_array" => $qc_line_ids_array);
}

function update_direct_purchase_receipt_line_returned_alloc_qty_and_returned_qty($trans_id, $rcp_line_row_state=0) {
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $total_return_alloc_qty_ = get_dpr_lines_total_qty(ST_DIRECT_PURCHASE_RETURN, $trans_id, array(AS_NEW, AS_WITH_THE_APPROVER, AS_AWAITING_APPROVAL, 
                                                           AS_BACK_TO_ORIGINATOR));
        $total_return_alloc_qty = abs($total_return_alloc_qty_);

        $total_return_qty_ = get_dpr_lines_total_qty(ST_DIRECT_PURCHASE_RETURN, $trans_id, array(AS_APPROVED, AS_COMPLETED));
        $total_return_qty = abs($total_return_qty_);

        $total_of_both_returned = $total_return_alloc_qty + $total_return_qty;
            
            $sql = "UPDATE ".TB_PREF."quality_check_details  
                    SET returned_alloc = ".db_escape($total_return_alloc_qty).",  
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($updated_on).",  
                        row_state = ".db_new_row_state($rcp_line_row_state)." 
                    WHERE trans_id = ".db_escape($trans_id)." 
                        AND qty >= $total_of_both_returned";
            
            db_query($sql, 'update_direct_purchase_receipt_line_returned_alloc_qty_and_returned_qty');
                $af_row = db_num_affected_rows();
            
                    if ($af_row != 1) {
                        return 0;
                    }
            
    $nw_rcp_line_row_state = db_new_row_state($rcp_line_row_state);
    $nw_updated_on = date('Y-m-d H:i:s');
            
            $sql_1 = "UPDATE ".TB_PREF."quality_check_details  
                    SET returned_quantity = ".db_escape($total_return_qty).",  
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($nw_updated_on).",  
                        row_state = ".db_new_row_state($nw_rcp_line_row_state)." 
                    WHERE trans_id = ".db_escape($trans_id)." 
                        AND qty >= $total_of_both_returned";
            
            db_query($sql_1, 'update_direct_purchase_receipt_line_returned_alloc_qty_and_returned_qty');
                $af_row_2 = db_num_affected_rows();

                    if ($af_row_2 != 1) {
                        return 0;
                    }

        return 1;
}

function get_allocated_direct_purchase_receipt_lines($type, $trans_no) {
    $sql = "SELECT * 
            FROM ".TB_PREF."quality_check_details  
            WHERE type = ".db_escape($type)." 
                AND trans_no = ".db_escape($trans_no)." 
                AND returned_alloc > 0 "; 
    
        return db_query($sql, "get_allocated_direct_purchase_receipt_lines");
}

function check_all_direct_purchase_receipt_lines_fully_returned($type, $trans_no) {
    $alloc_recs = get_allocated_direct_purchase_receipt_lines($type, $trans_no);
        if (db_num_rows($alloc_recs) > 0) {
            return false;
        }
    
    $sql = "SELECT qty, returned_quantity  
            FROM ".TB_PREF."quality_check_details  
            WHERE type = ".db_escape($type)." 
                AND trans_no = ".db_escape($trans_no)." 
            GROUP BY trans_id 
            HAVING returned_quantity < qty ";
            
        $result = db_query($sql, "check_all_direct_purchase_receipt_lines_fully_returned");
            if (db_num_rows($result) > 0) {
                return false;
            } 
            
        return true;
}

function get_quality_check_headers_by_filters($jo_id=0, $id=0, $type=0, $trans_no=0, $loc_code='', $project_id=0, $from_date=null, $to_date=null) {
    $sql = "SELECT sa.*,
                l.location_name,
                pm.description project_description,
                u.real_name requestor_name 
            FROM ".TB_PREF."quality_checks sa 
                JOIN ".TB_PREF."projects_master pm 
                    ON sa.project_id = pm.project_id 
                JOIN ".TB_PREF."locations l 
                    ON sa.loc_code = l.loc_code 
                JOIN ".TB_PREF."users u 
                    ON sa.created_by = u.id 
            WHERE 1 = 1 ";
    
        if ($jo_id > 0) {
            $sql .= " AND sa.jo_id = ".db_escape($jo_id);
        }
        if ($id > 0) {
            $sql .= " AND sa.id = ".db_escape($id);
        }
        if ($type > 0) {
            $sql .= " AND sa.type = ".db_escape($type);
        }
        if ($trans_no > 0) {
            $sql .= " AND sa.trans_no = ".db_escape($trans_no);
        }
        if (strlen($loc_code) > 0) {
            $sql .= " AND sa.loc_code = ".db_escape($loc_code);
        }
        if ($project_id > 0) {
            $sql .= " AND sa.project_id = ".db_escape($project_id);
        }
        if ($from_date !== null) {
            $sql .= " AND sa.tran_date >= ".db_escape($from_date);
        }
        if ($to_date !== null) {
            $sql .= " AND sa.tran_date <= ".db_escape($to_date);
        }
    
        return db_query($sql, "get_quality_check_headers_by_filters");
}

function get_sql_for_approve_quality_checks($current_user_id, $type) {
    $sql = "SELECT qc.id,
                qc.trans_no,
                qc.type,
                qc.jo_id,
                qc.reference,
                qc.tran_date,                   
                l.location_name,                   
                qc.project_id,                   
                pm.description project_description,
                c.real_name,
                qc.created_on,
                qc.approval_status 
            FROM ".TB_PREF."quality_checks qc
                JOIN ".TB_PREF."projects_master pm 
                    ON qc.project_id = pm.project_id
                JOIN ".TB_PREF."users c 
                    ON qc.created_by = c.id  
                JOIN ".TB_PREF."approvers apr 
                    ON qc.next_approval_group_id = apr.approval_group_id  
                JOIN ".TB_PREF."user_data_access_groups udag 
                    ON pm.dag_id = udag.dag_id 
                        AND udag.user_id = apr.user_id  
                JOIN ".TB_PREF."users u 
                    ON apr.user_id = u.id 
                LEFT JOIN ".TB_PREF."locations l 
                    ON qc.loc_code = l.loc_code   
            WHERE qc.approval_status IN (".implode(',', array(AS_AWAITING_APPROVAL, AS_WITH_THE_APPROVER)).") 
                AND apr.user_id = ".db_escape($current_user_id)." 
                AND qc.type = ".db_escape($type);
      
        return $sql;
}

function get_quality_check_line_details_with_dpr_line_details($trans_no, $type) {
    $sql = "SELECT ard.*,
                am.description stock_description,
                am.stock_id,
                am.mb_flag,
                un.name unit_name,
                ps.description site_description, 
                pt.description task_description, 
                am.stock_control_method,
                rq_d.item_type req_item_type, 
                rq_d.stock_id req_stock_id, 
                rq_d.item_description req_item_description, 
                rq_d.tran_unit req_tran_unit, 
                rq_d.tran_unit req_tran_unit 
            FROM ".TB_PREF."quality_check_details ard 
                LEFT JOIN ".TB_PREF."stock_master am 
                    ON ard.stock_id = am.stock_id 
                LEFT JOIN ".TB_PREF."item_units un  
                    ON ard.tran_unit = un.abbr  
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON ard.project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_tasks pt 
                    ON ard.task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."direct_purchase_requisition_details rq_d 
                    ON ard.source_line_id = rq_d.trans_id 
                        AND rq_d.type = ".db_escape(ST_DIRECT_PURCHASE_REQ)." 
                LEFT JOIN ".TB_PREF."item_units rq_un  
                    ON rq_d.tran_unit = rq_un.abbr 
            WHERE ard.trans_no = ".db_escape($trans_no)." 
                AND ard.type = ".db_escape($type);
    
        return db_query($sql, "get_quality_check_line_details_with_dpr_line_details");
}

function get_quality_check_line_with_direct_purchase_requisition_line($trans_id) {
    $sql = "SELECT qc_d.*,
                un.name unit_name,
                ps.description site_description, 
                rq_d.item_type req_item_type, 
                rq_d.stock_id req_stock_id, 
                rq_d.item_description req_item_description, 
                rq_d.tran_unit req_tran_unit, 
                un.name req_unit_name,
                rq_d.tran_qty req_tran_qty  
            FROM ".TB_PREF."quality_check_details qc_d  
                LEFT JOIN ".TB_PREF."item_units un  
                    ON qc_d.tran_unit = un.abbr  
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON qc_d.project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."direct_purchase_requisition_details rq_d 
                    ON qc_d.source_line_id = rq_d.trans_id 
                        AND rq_d.type = ".db_escape(ST_DIRECT_PURCHASE_REQ)." 
                LEFT JOIN ".TB_PREF."item_units rq_un  
                    ON rq_d.tran_unit = rq_un.abbr  
            WHERE qc_d.trans_id = ".db_escape($trans_id)." 
            ORDER BY qc_d.trans_id";
    
        $result = db_query($sql, "get_quality_check_line_with_direct_purchase_requisition_line");
        return db_fetch($result);
}

function get_quality_check_lines_with_edited_item_codes($q_trans_no) {
    $sql = "SELECT qc_d.*,
                rq_d.trans_id req_line_id, 
                rq_d.item_type req_item_type, 
                rq_d.stock_id req_stock_id, 
                rq_d.item_description req_item_description, 
                rq_d.tran_unit req_tran_unit, 
                rq_d.tran_qty req_tran_qty  
            FROM ".TB_PREF."quality_check_details qc_d 
                JOIN ".TB_PREF."quality_checks qc_h 
                    ON qc_d.trans_no = qc_h.trans_no   
                        AND qc_d.type = qc_h.type   
                JOIN ".TB_PREF."direct_purchase_requisitions rq_h 
                    ON qc_h.dpr_no = rq_h.id    
                JOIN ".TB_PREF."direct_purchase_requisition_details rq_d  
                    ON rq_h.trans_no = rq_d.trans_no
                        AND rq_h.type = rq_d.type   
                        AND qc_d.source_line_id = rq_d.trans_id   
            WHERE qc_h.type = ".db_escape(ST_QUALITY_CHECK)." 
                AND qc_h.trans_no = ".db_escape($q_trans_no)." 
                AND rq_h.type = ".db_escape(ST_DIRECT_PURCHASE_REQ)." 
                AND qc_d.stock_id <> rq_d.stock_id 
            ORDER BY qc_d.trans_id";
    
        return db_query($sql, "get_quality_check_lines_with_edited_item_codes");
}

function get_direct_purchase_lines_with_edited_item_codes($q_trans_no, $rcp_trans_no) {
    $sql = "SELECT rc_d.*,
                qc_d.trans_id qc_line_id, 
                qc_d.item_type qc_item_type, 
                qc_d.stock_id qc_stock_id, 
                qc_d.item_description qc_item_description, 
                qc_d.tran_unit qc_tran_unit, 
                qc_d.tran_qty qc_tran_qty  
            FROM ".TB_PREF."quality_check_details rc_d 
                JOIN ".TB_PREF."quality_checks rc_h 
                    ON rc_d.trans_no = rc_h.trans_no   
                        AND rc_d.type = rc_h.type   
                JOIN ".TB_PREF."quality_checks qc_h 
                    ON rc_h.dpr_no = qc_h.id    
                JOIN ".TB_PREF."quality_check_details qc_d  
                    ON qc_h.trans_no = qc_d.trans_no
                        AND qc_h.type = qc_d.type   
                        AND rc_d.source_line_id = qc_d.trans_id   
            WHERE rc_h.trans_no = ".db_escape($rcp_trans_no)." 
                AND rc_h.type = ".db_escape(ST_DIRECT_PURCHASE_RECEIPT)." 
                AND qc_h.trans_no = ".db_escape($q_trans_no)." 
                AND qc_h.type = ".db_escape(ST_QUALITY_CHECK)." 
                AND rc_d.stock_id <> qc_d.stock_id 
            ORDER BY qc_d.trans_id";
    
        return db_query($sql, "get_direct_purchase_lines_with_edited_item_codes");
}

function get_quality_check_lines_with_direct_purchase_requisitions_by_qc_line_ids($qc_line_ids_array) {
    $sql = "SELECT qc_d.trans_id,
                rq_h.id req_id,
                rq_h.trans_no dp_req_trans_no,
                rq_h.approval_status dp_req_approval_status,
                rq_h.row_state req_row_state,
                qc_h.trans_no req_trans_no,  
                rq_d.trans_id req_trans_id,  
                rq_d.row_state req_line_row_state 
            FROM ".TB_PREF."quality_check_details qc_d 
                JOIN ".TB_PREF."quality_checks qc_h 
                    ON qc_d.trans_no = qc_h.trans_no   
                        AND qc_d.type = qc_h.type   
                JOIN ".TB_PREF."direct_purchase_requisitions rq_h 
                    ON qc_h.dpr_no = rq_h.id    
                JOIN ".TB_PREF."direct_purchase_requisition_details rq_d  
                    ON rq_h.trans_no = rq_d.trans_no
                        AND rq_h.type = rq_d.type   
                        AND qc_d.source_line_id = rq_d.trans_id   
            WHERE qc_h.type = ".db_escape(ST_QUALITY_CHECK)." 
                AND rq_h.type = ".db_escape(ST_DIRECT_PURCHASE_REQ)." 
                AND qc_d.trans_id IN (".implode(",", $qc_line_ids_array).")  
            ORDER BY qc_d.trans_id";
    
        return db_query($sql, "get_quality_check_lines_with_direct_purchase_requisitions_by_qc_line_ids");
}

function get_quality_check_lines_column_total_by_source_line_approval_status($type, $source_line_id, $approval_status, $not_trans_id=0, $column="qty") {
    $sql = "SELECT SUM(ln.$column) total_qty 
            FROM ".TB_PREF."quality_check_details ln 
                JOIN ".TB_PREF."quality_checks hd  
                    ON ln.type = hd.type 
                        AND ln.trans_no = hd.trans_no  
            WHERE ln.source_line_id = ".db_escape($source_line_id)." 
                AND hd.type = ".db_escape($type)."
                AND hd.approval_status IN (".implode(",", $approval_status).")";
    
        if ($not_trans_id > 0) {
            $sql .= " AND ln.trans_id <> ".db_escape($not_trans_id);
        }
    
        $result = db_query($sql, 'get_quality_check_lines_column_total_by_source_line_approval_status');
        return db_result_value($result, 'total_qty');
}

function update_direct_purchase_requisition_line_column_total_qty($trans_id, $type, $approval_status, $req_line_row_state=0, $column="") {
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $total_qty_ = get_quality_check_lines_column_total_by_source_line_approval_status($type, $trans_id, $approval_status, 0, $column);
        $total_qty = abs($total_qty_);
    
            $sql = "UPDATE ".TB_PREF."direct_purchase_requisition_details   
                    SET $column = ".db_escape($total_qty).",  
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($updated_on).",  
                        row_state = ".db_new_row_state($req_line_row_state)." 
                    WHERE trans_id = ".db_escape($trans_id);
            
            if ($column == "returned_quantity") { // if wanted can be added more conditions
                $sql .= " AND checked_quantity >= $total_qty + returned_alloc";
            } elseif ($column == "returned_alloc") {
                $sql .= " AND checked_quantity >= $total_qty + returned_quantity";
            } 

            db_query($sql, 'update_direct_purchase_requisition_line_column_total_qty');
            return db_num_affected_rows();
}

?>