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

function get_purchase_requisition_task_cost_details($trans_no)
{

    $sql="SELECT pr.pr_no trans_no,
                pr.approval_status,
                SUM(prd.estimate_price * prd.requested_unit_qty) AS task_cost,
                prd.task_id 
        FROM  ".TB_PREF."purchase_requisitions pr 
                JOIN ".TB_PREF."purchase_requisition_details prd ON 
                        pr.pr_no = prd.requisition_id 
        WHERE pr.pr_no =".db_escape($trans_no)." 
        GROUP BY prd.task_id ";

    return db_query($sql,"Could not get_purchase_requisition_task_cost_details");

}


function get_material_requisition_task_cost_details($trans_no)
{

    $sql="SELECT mr.mr_no trans_no,
                mr.approval_status,
                SUM(mrd.price * mrd.requested_qty) AS task_cost,
                mrd.task_id 
        FROM  ".TB_PREF."material_requisitions mr 
                JOIN ".TB_PREF."material_requisition_details mrd ON 
                        mr.mr_no = mrd.mr_no 
        WHERE mr.mr_no =".db_escape($trans_no)." 
        GROUP BY mrd.task_id ";

    return db_query($sql,"Could not get_material_requisition_task_cost_details");

}

function get_inventory_adjustment_request_task_cost_details($trans_type, $trans_no)
{

    $sql="SELECT mr.id trans_no,
                mr.type trans_type,
                mr.approval_status,
                SUM(mrd.standard_cost * mrd.qty) AS task_cost,
                mrd.task_id 
        FROM  ".TB_PREF."stock_adjustment_requests mr 
                JOIN ".TB_PREF."stock_adjustment_request_details mrd ON 
                        mr.id = mrd.trans_no 
        WHERE mr.id =".db_escape($trans_no)." 
                AND mr.type =".db_escape($trans_type)." 
        GROUP BY mrd.task_id ";

    return db_query($sql,"Could not get_inventory_adjustment_request_task_cost_details");

}

 

function get_work_order_task_cost_details($trans_no)
{
    $sql="SELECT po.order_no trans_no,
                po.approval_status,
                SUM(pod.order_unit_price * pod.order_unit_qty) AS task_cost,
                po.task_id 
        FROM  ".TB_PREF."purch_orders po 
                JOIN ".TB_PREF."purch_order_details pod ON 
                        po.order_no = pod.order_no 
        WHERE po.order_no =".db_escape($trans_no)." 
        GROUP BY po.task_id ";

    return db_query($sql,"Could not get_work_order_task_cost_details");
}


function get_i_owe_you_task_cost_details($trans_no)
{
    $sql="SELECT iou.id trans_no,
                iou.approval_status,
                SUM(ioud.amount) AS task_cost,
                ioud.task_id 
        FROM  ".TB_PREF."i_owe_you iou 
                JOIN ".TB_PREF."i_owe_you_details ioud ON 
                        iou.id = ioud.iou_id 
        WHERE iou.id =".db_escape($trans_no)." 
        GROUP BY ioud.task_id ";

    return db_query($sql,"Could not get_i_owe_you_task_cost_details");
}



function get_i_owe_you_batch_task_cost_details($trans_no)
{
    $sql="SELECT ioub.batch_no trans_no,
                ioub.approval_status,
                SUM(ioue.approved_amount) AS task_cost,
                ioue.task_id 
        FROM  ".TB_PREF."i_owe_you_batches ioub 
                JOIN ".TB_PREF."i_owe_you_expenses ioue ON 
                        ioub.batch_no = ioue.iou_batch_no 
        WHERE ioub.batch_no =".db_escape($trans_no)." 
        GROUP BY ioue.task_id ";

    return db_query($sql,"Could not get_i_owe_you_batch_task_cost_details");
}


function get_vehicle_request_task_cost_details($trans_no)
{
    $sql="SELECT vr.id trans_no,
                vr.approval_status,
                SUM(vr.amount) AS task_cost,
                vr.task_id 
        FROM  ".TB_PREF."vehicle_requests vr 
               
        WHERE vr.id =".db_escape($trans_no)." 
        GROUP BY vr.task_id ";

    return db_query($sql,"Could not get_vehicle_request_task_cost_details");
}



function get_supplier_transport_invoices_task_cost_details($trans_no)
{
    $sql="SELECT sti.id trans_no,
                sti.approval_status,
                IF(tax_included=1,SUM(sti.total), SUM(sti.total + sti.taxes)) AS task_cost,
                sti.task_id 
                
        FROM  ".TB_PREF."supplier_transport_invoices sti 
              
        WHERE sti.id =".db_escape($trans_no)." 
        GROUP BY sti.task_id ";

    return db_query($sql,"Could not get_supplier_transport_invoices_task_cost_details");
}


function get_subcontractor_invoices_task_cost_details($trans_no)
{
    $sql="SELECT sci.order_no trans_no,
                sci.approval_status,
                SUM(scid.amount) AS task_cost,
                scid.task_id 
        FROM  ".TB_PREF."subcontractor_invoices sci 
                JOIN ".TB_PREF."subcontractor_invoice_details scid ON 
                        sci.order_no = scid.order_no 
        WHERE sci.order_no =".db_escape($trans_no)." AND
                scid.task_id > 0

        GROUP BY scid.task_id ";

    return db_query($sql,"Could not get_subcontractor_invoices_task_cost_details");
}

 
function get_utility_bills_task_cost_details($trans_no)
{
    $sql="SELECT ub.id trans_no,
                ub.approval_status,
                SUM(ubd.amount) AS task_cost,
                ubd.task_id 
        FROM  ".TB_PREF."utility_bills ub 
                JOIN ".TB_PREF."utility_bill_details ubd ON 
                        ub.id = ubd.ub_id 
        WHERE ub.id =".db_escape($trans_no)." 
        GROUP BY ubd.task_id ";

    return db_query($sql,"Could not get_utility_bills_task_cost_details");
}

 
function get_subcontractor_purchase_orders_task_cost_details($trans_no)
{
    $sql="SELECT spo.id trans_no,
                spo.approval_status,
                SUM(spod.quantity * spod.rate) AS task_cost,
                spod.task_id 
        FROM  ".TB_PREF."subcontractor_purchase_orders spo 
                JOIN ".TB_PREF."subcontractor_purchase_order_details spod ON 
                        spo.id = spod.sc_po_id 
        WHERE spo.id =".db_escape($trans_no)." 
        GROUP BY spod.task_id ";

    return db_query($sql,"Could not get_subcontractor_purchase_orders_task_cost_details");
}


function get_subcontractor_credit_notes_task_cost_details($trans_no)
{
    $sql="SELECT sccn.order_no trans_no,
                sccn.approval_status,
                SUM(sccnd.amount) AS task_cost,
                sccnd.task_id 
        FROM  ".TB_PREF."subcontractor_credit_notes sccn 
                JOIN ".TB_PREF."subcontractor_credit_note_details sccnd ON 
                        sccn.order_no = sccnd.order_no 
        WHERE sccn.order_no =".db_escape($trans_no)." AND
                sccnd.task_id > 0

        GROUP BY sccnd.task_id ";

    return db_query($sql,"Could not get_subcontractor_credit_notes_task_cost_details");
}


function get_task_boq_details($task_id)
{
	$sql = "SELECT pt.task_id,
                        pt.task_code,
                        pt.description task_description,
                        pt.budget,
                        pt.budget_used,
                        pt.budget_allocated,
                        ptg.description task_group_description,
                        boqg.description boq_group_description 
                FROM ".TB_PREF."project_tasks pt 
                        INNER JOIN ".TB_PREF."project_task_groups ptg ON 
                                pt.task_group_id = ptg.id 
                        LEFT JOIN ".TB_PREF."project_boq_item_groups boqg ON  
                                ptg.boq_group_id = boqg.id 
                WHERE pt.task_id = ".db_escape($task_id);
	
	$result = db_query($sql,"Could not get_task_boq_details");
	
	return db_fetch($result);
}


function get_boq_item_details($task_id)
{
    
    $sql = "SELECT boqi.id,
                boqi.code boq_item_code, 
                boqi.description boq_item_description,
                boqi.max_quantity,
                boqi.completed_qty total_work_done 
            FROM ".TB_PREF."project_tasks pt 
                JOIN ".TB_PREF."project_task_groups ptg ON 
                    pt.task_group_id = ptg.id 
                JOIN ".TB_PREF."project_boq_items boqi ON 
                    ptg.code = boqi.code 
            WHERE pt.task_id = ".db_escape($task_id); 
            

    $result = db_query($sql,"Could not get_boq_item_details");
    return db_fetch($result);
    

    
}



?>