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


function get_batch_item_issue_pr_lines($bii_line_id, $from_date, $to_date)
{

        $sql = "SELECT prd.*,
                sm.description stock_master_description, 
                sm.stock_control_method,
                sm.material_cost + sm.labour_cost + sm.overhead_cost AS standard_cost,
                sm.mb_flag,
                sm.editable descr_editable, 
                
                pt.description task_description,
                ps.description project_site_description,
                
                pod.quantity_ordered po_ordered_qty,
                pod.quantity_received  po_received_qty,
                pod.po_detail_item po_detail_id,
                pod.order_no po_no,
                
                prd.requisition_id trans_no,
                pr_po.id pr_po_line_id,              
                pod.unit_price,
                
                iu.name requested_unit_name,
                
                biih.project_id 

            FROM ".TB_PREF."batch_item_issue_details biid  
                INNER JOIN ".TB_PREF."batch_item_issues biih 
                    ON biih.type = biid.type 
                    AND biih.trans_no = biid.trans_no 
                INNER JOIN ".TB_PREF."purchase_requisition_details prd 
                    ON prd.item_code = biid.stock_id 
                INNER JOIN ".TB_PREF."pr_po_details pr_po ON 
                        prd.id = pr_po.pr_line_id                     
                INNER JOIN ".TB_PREF."purch_order_details pod ON 
                        pr_po.po_line_id = pod.po_detail_item      
                INNER JOIN ".TB_PREF."stock_master sm ON 
                        prd.item_code = sm.stock_id        
                INNER JOIN ".TB_PREF."item_units iu 
                    ON iu.abbr = prd.requested_unit         
                INNER JOIN ".TB_PREF."purchase_requisitions prh  
                    ON prh.pr_no = prd.requisition_id 
                    AND prh.approval_status IN (".AS_APPROVED.",".AS_ISSUED.")
                    AND prh.project_id = biih.project_id 
                    AND prh.pr_complete = 0 
                LEFT JOIN ".TB_PREF."project_tasks pt ON 
                        prd.task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."project_sites ps ON 
                        prd.project_site_id = ps.id                 
            WHERE biid.id = ".db_escape($bii_line_id)." 
                AND prd.required_date >= ".db_escape($from_date)." AND prd.required_date <= ".db_escape($to_date)." 
                AND prd.quantity > (issued_qty - returned_qty) 
            ORDER BY prh.pr_date, prd.required_date, prd.id ";
    
	return db_query($sql, "could not get requisition details");

}


function delete_batch_item_issue_detail_transactions($type, $trans_no, $source_type_id, $bii_line_id) {
    
        $sql = "DELETE FROM ".TB_PREF."batch_item_issue_transactions 
                WHERE type = ".db_escape($type)." 
                        AND trans_no = ".db_escape($trans_no)." 
                        AND source_type_id = ".db_escape($source_type_id)." 
                        AND bii_line_id = ".db_escape($bii_line_id)." 
                        AND target_line_id = 0 ";

        db_query($sql, 'delete_batch_item_issue_detail_transactions'); 
}

function get_batch_item_issue_transactions_records_count($bii_line_id)
{
    $sql = "SELECT COUNT(id) lines_count 
            FROM ".TB_PREF."batch_item_issue_transactions biit 
            WHERE biit.bii_line_id = ".db_escape($bii_line_id);  
    
    $result = db_query($sql, "get_batch_item_issue_transactions_records_count");
    
    return db_result_value($result, "lines_count");
    
}
function add_batch_item_issue_transaction($type, $trans_no, $bii_line_id, 
                        $source_type_id, $source_type_no, $source_line_id, $po_line_id, 
                        $tran_unit, $tran_qty, 
                        $conversion_factor, $qty, 
                        $movement_type_id, $linked_field_type, $linked_field_id, 
                        $project_site_id, $task_id=0, $tran_unit_cost=0) {
    
    $user_id = $_SESSION['wa_current_user']->user;
    $time = date('Y-m-d H:i:s');
    
        $sql = "INSERT INTO ".TB_PREF."batch_item_issue_transactions(
                        type, trans_no, bii_line_id, 
                        source_type_id, source_type_no, 
                        source_line_id, po_line_id,
                        tran_unit, tran_qty, 
                        conversion_factor, qty, 
                        movement_type, linked_field_type, linked_field_id, 
                        project_site_id, task_id,
                        tran_unit_cost, 
                        created_by, created_on)         
                VALUES( ".db_escape($type).", ".db_escape($trans_no).", ".db_escape($bii_line_id).", "
                        .db_escape($source_type_id).",".db_escape($source_type_no).", "
                        .db_escape($source_line_id).", ".db_escape($po_line_id).", "
                        .db_escape($tran_unit).",".db_escape($tran_qty).", "
                        .db_escape($conversion_factor).", ".db_escape($qty).", "
                        .db_escape($movement_type_id).", ".db_escape($linked_field_type).", ".db_escape($linked_field_id).", "
                        .db_escape($project_site_id).", ".db_escape($task_id).", "
                        .db_escape($tran_unit_cost).", "
                        .db_escape($user_id).", ".db_escape($time).")";

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


function get_batch_item_issue_transaction_record($bii_line_id, $source_type_id, $source_type_no, $source_line_id)
{
    $sql = "SELECT biit.*  
            FROM ".TB_PREF."batch_item_issue_transactions biit 
            WHERE biit.bii_line_id = ".db_escape($bii_line_id)." 
                AND biit.source_type_id = ".db_escape($source_type_id)." 
                AND biit.source_type_no = ".db_escape($source_type_no)."   
                AND biit.source_line_id = ".db_escape($source_line_id);  
    
    $result = db_query($sql, "get_batch_item_issue_transaction_record");
    
    return db_fetch($result);
}

function get_batch_item_issue_source_line_allocated_qty($source_type_id, $source_line_id, $bii_trans_line_id)
{
    $sql = "SELECT SUM(biit.qty) total_allocated  
            FROM ".TB_PREF."batch_item_issue_transactions biit 
                JOIN ".TB_PREF."batch_item_issues biih 
                    ON biih.type = biit.type 
                    AND biih.trans_no = biit.trans_no 
            WHERE biih.approval_status = ".db_escape(AS_NEW)."   
                AND biit.source_type_id = ".db_escape($source_type_id)."   
                AND biit.source_line_id = ".db_escape($source_line_id)." 
                AND biit.id <> ".db_escape($bii_trans_line_id);  
    
    $result = db_query($sql, "get_batch_item_issue_source_line_allocated_qty");
    
    return db_result_value($result, "total_allocated");
    
}

function get_batch_item_issue_detail_line_allocated_qty($bii_line_id, $source_type_id=0)
{
    $sql = "SELECT SUM(biit.qty) total_allocated  
            FROM ".TB_PREF."batch_item_issue_transactions biit 
            WHERE biit.bii_line_id = ".db_escape($bii_line_id);
    
    if($source_type_id > 0){
        $sql .= " AND biit.source_type_id = ".db_escape($source_type_id);
    }
    
    $result = db_query($sql, "get_batch_item_issue_detail_line_allocated_qty");
    
    return db_result_value($result, "total_allocated");
    
}

function delete_batch_item_issue_detail_transaction($id) {
    
        $sql = "DELETE FROM ".TB_PREF."batch_item_issue_transactions 
                WHERE id = ".db_escape($id)." 
                        AND target_line_id = 0 ";

        db_query($sql, 'delete_batch_item_issue_detail_transaction'); 
}




function get_batch_item_issue_mr_lines($bii_line_id, $from_date, $to_date)
{

        $sql = "SELECT mrd.*,
                0 pr_po_line_id,
                0 po_detail_id,
                mrh.mr_no trans_no,
                mrd.stock_id item_code,
                sm.description stock_master_description, 
                sm.stock_control_method,
                sm.material_cost + sm.labour_cost + sm.overhead_cost AS standard_cost,
                sm.mb_flag,
                sm.editable descr_editable,                 
                ps.description project_site_description,                  
                iu.name requested_unit_name,                
                biih.project_id 

            FROM ".TB_PREF."batch_item_issue_details biid  
                INNER JOIN ".TB_PREF."batch_item_issues biih 
                    ON biih.type = biid.type 
                    AND biih.trans_no = biid.trans_no 
                INNER JOIN ".TB_PREF."material_requisition_details mrd 
                    ON mrd.stock_id = biid.stock_id 
                INNER JOIN ".TB_PREF."material_requisitions mrh  
                    ON mrh.mr_no = mrd.mr_no 
                    AND mrh.project_id = biih.project_id  
                    AND mrh.approval_status IN (".AS_APPROVED.",".AS_ISSUED.")                       
                INNER JOIN ".TB_PREF."stock_master sm ON 
                        mrd.stock_id = sm.stock_id        
                INNER JOIN ".TB_PREF."item_units iu 
                    ON iu.abbr = mrd.requested_unit         
                LEFT JOIN ".TB_PREF."project_sites ps ON 
                        mrd.project_site_id = ps.id                 
            WHERE biid.id = ".db_escape($bii_line_id)." 
                AND mrd.required_date >= ".db_escape($from_date)." AND mrd.required_date <= ".db_escape($to_date)." 
                AND mrd.requested_qty > (mrd.issued_qty - mrd.returned_qty) 
            ORDER BY mrh.mr_date, mrd.required_date, mrd.id ";
    
	return db_query($sql, "get_batch_item_issue_mr_lines");

}


function get_sql_for_batch_item_issue_pr_transactions($bii_line_id)
{

        $sql = "SELECT 
                ".db_escape(ST_PURCH_REQ)." source_type_id,
                prd.id,
                prd.requisition_id trans_no,
                prd.quantity requested_qty,
                prd.issued_qty,
                prd.returned_qty,
                prd.requested_unit,
                prd.item_pr_description item_trans_description,
                biit.tran_qty, biit.po_line_id, biit.target_type_id, biit.target_type_no, biit.target_line_id, biit.id biit_id, 
                sm.description stock_master_description, sm.stock_control_method, sm.material_cost + sm.labour_cost + sm.overhead_cost AS standard_cost, sm.mb_flag, sm.editable descr_editable, 
                pod.order_no po_no,
                ps.description project_site_description,                 
                biit.task_id               
            FROM ".TB_PREF."batch_item_issue_transactions biit  
                INNER JOIN ".TB_PREF."batch_item_issue_details biid 
                        ON biid.id = biit.bii_line_id 
                INNER JOIN ".TB_PREF."purchase_requisition_details prd 
                        ON  biit.source_type_id = ".db_escape(ST_PURCH_REQ)." AND biit.source_type_no = prd.requisition_id AND biit.source_line_id = prd.id        
                INNER JOIN ".TB_PREF."purch_order_details pod 
                        ON pod.po_detail_item = biit.po_line_id     
                INNER JOIN ".TB_PREF."stock_master sm ON 
                        prd.item_code = sm.stock_id               
                LEFT JOIN ".TB_PREF."project_sites ps ON 
                        biit.project_site_id = ps.id                 
            WHERE biit.bii_line_id = ".db_escape($bii_line_id);
    
	return $sql;

}


function get_sql_for_batch_item_issue_mr_transactions($bii_line_id)
{
     
        
    $sql = " SELECT 
                ".db_escape(ST_MATERIAL_REQ)." source_type_id, 
                mrd.id, 
                mrd.mr_no trans_no, 
                mrd.requested_qty, 
                mrd.issued_qty, 
                mrd.returned_qty, 
                mrd.requested_unit, 
                biid.item_description item_trans_description,
                biit.tran_qty, biit.po_line_id, biit.target_type_id, biit.target_type_no, biit.target_line_id, biit.id biit_id, 
                sm.description stock_master_description, sm.stock_control_method, sm.material_cost + sm.labour_cost + sm.overhead_cost AS standard_cost, sm.mb_flag, sm.editable descr_editable, 
                0 po_no,
                ps.description project_site_description, 
                biit.task_id         

            FROM ".TB_PREF."batch_item_issue_transactions biit  
                INNER JOIN ".TB_PREF."batch_item_issue_details biid  
                    ON biid.id = biit.bii_line_id 
                INNER JOIN ".TB_PREF."material_requisition_details mrd                     
                    ON  biit.source_type_id = ".db_escape(ST_MATERIAL_REQ)." AND biit.source_type_no = mrd.mr_no AND biit.source_line_id = mrd.id       
                INNER JOIN ".TB_PREF."stock_master sm ON 
                        mrd.stock_id = sm.stock_id               
                LEFT JOIN ".TB_PREF."project_sites ps ON 
                        biit.project_site_id = ps.id                 
            WHERE biit.bii_line_id = ".db_escape($bii_line_id);
    
	return $sql;

}


function get_sql_for_batch_item_issue_dii_transaction($bii_line_id)
{
     
        
    $sql = " SELECT 
                ".db_escape(ST_DAILY_ITEM_ISSUE)." source_type_id, 
                biit.source_line_id id, 
                biit.source_type_no trans_no,
                0 requested_qty, 
                0 issued_qty, 
                0 returned_qty, 
                biid.tran_unit requested_unit, 
                biid.item_description item_trans_description,
                biit.tran_qty, biit.po_line_id, biit.target_type_id, biit.target_type_no, biit.target_line_id, biit.id biit_id, 
                sm.description stock_master_description, sm.stock_control_method, sm.material_cost + sm.labour_cost + sm.overhead_cost AS standard_cost, sm.mb_flag, sm.editable descr_editable, 
                0 po_no,
                ps.description project_site_description,                 
                biit.task_id 

            FROM ".TB_PREF."batch_item_issue_transactions biit  
                INNER JOIN ".TB_PREF."batch_item_issue_details biid  
                    ON biid.id = biit.bii_line_id      
                INNER JOIN ".TB_PREF."stock_master sm ON 
                        biid.stock_id = sm.stock_id               
                LEFT JOIN ".TB_PREF."project_sites ps ON 
                        biid.project_site_id = ps.id                 
            WHERE biit.source_type_id = ".db_escape(ST_DAILY_ITEM_ISSUE)." 
                AND biit.bii_line_id = ".db_escape($bii_line_id);
    
	return $sql;

}

function get_batch_item_issue_line_selected_transactions($bii_line_id)
{
    $sql_pr = get_sql_for_batch_item_issue_pr_transactions($bii_line_id);
    $sql_mr = get_sql_for_batch_item_issue_mr_transactions($bii_line_id);
    $sql_dii = get_sql_for_batch_item_issue_dii_transaction($bii_line_id);
    
    $sql = $sql_pr." UNION ".$sql_mr." UNION ".$sql_dii;
    
    return db_query($sql, "get_batch_item_issue_line_selected_transactions");
}



function get_batch_item_issue_detail_no_task_dii_records($trans_no, $type_id)
{
    $sql = "SELECT biit.*  
            FROM ".TB_PREF."batch_item_issue_transactions biit 
            WHERE biit.source_type_id = ".db_escape(ST_DAILY_ITEM_ISSUE)." 
                    AND biit.type = ".db_escape($type_id)." 
                    AND biit.trans_no = ".db_escape($trans_no)." 
                    AND biit.task_id <= 0";
    
    return db_query($sql, "get_batch_item_issue_detail_no_task_dii_records"); 
    
}


function get_batch_item_issue_processed_source_transactions($type, $trans_no)
{
     
    $sql = " SELECT DISTINCT biit.source_type_id,
                            biit.source_type_no 
            FROM ".TB_PREF."batch_item_issue_transactions biit  
            WHERE biit.type = ".db_escape($type)." 
                AND biit.trans_no = ".db_escape($trans_no)." 
                AND biit.target_line_id > 0 ";
    
    
    $sql .= " ORDER BY biit.source_type_id, biit.source_type_no ";

    return db_query($sql, "get_batch_item_issue_processed_source_transactions");

}

function get_batch_item_issue_processed_target_transactions($type, $trans_no)
{
     
    $sql = " SELECT DISTINCT biit.target_type_id,
                            biit.target_type_no 
            FROM ".TB_PREF."batch_item_issue_transactions biit  
            WHERE biit.type = ".db_escape($type)." 
                AND biit.trans_no = ".db_escape($trans_no)." 
                AND biit.target_line_id > 0 ";
    
    
    $sql .= " ORDER BY biit.target_type_id, biit.target_type_no ";

    return db_query($sql, "get_batch_item_issue_processed_target_transactions");

}

function get_batch_item_issue_not_processed_transactions($type, $trans_no)
{
    $sql = " SELECT biit.id  
            FROM ".TB_PREF."batch_item_issue_transactions biit  
            WHERE biit.type = ".db_escape($type)." 
                AND biit.trans_no = ".db_escape($trans_no)." 
                AND biit.target_line_id = 0 ";
    
    
    $sql .= " ORDER BY biit.id  ";

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