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

function get_recalculate_subcontractor_purchase_order_task_details($task_id)
{

    $sql = "SELECT ".ST_SUBCONTRACTOR_PURCH_ORDER." type,
                    h.id tran_no, 
                    d.task_id, 
                    SUM(d.rate * (d.quantity - d.invoiced_qty) ) task_cost, 
                    h.approved_by created_by, 
                    h.approved_on created_on 
            FROM ".TB_PREF."subcontractor_purchase_order_details d   
                JOIN ".TB_PREF."subcontractor_purchase_orders h  
                    ON d.sc_po_id = h.id   
            WHERE h.approval_status IN(".db_escape(AS_APPROVED).",".db_escape(AS_COMPLETED).",".db_escape(AS_ISSUED).") AND 
                d.task_id = ".db_escape($task_id)." AND 
                d.quantity > d.invoiced_qty 
            GROUP BY h.id, d.task_id 
            ORDER BY h.approved_on ";

        return db_query($sql);

}


function get_recalculate_material_requisition_task_details($task_id)
{

    $sql = "SELECT ".ST_MATERIAL_REQ." type,
                        mr.mr_no tran_no, 
                        mrd.task_id, 
                        SUM(mrd.price * (mrd.requested_qty - mrd.issued_qty)) task_cost, 
                        mr.approved_by created_by, 
                        mr.approved_on created_on 
                FROM ".TB_PREF."material_requisition_details mrd  
                    JOIN ".TB_PREF."material_requisitions mr 
                        ON mrd.mr_no = mr.mr_no  
                WHERE mr.approval_status IN(".db_escape(AS_APPROVED).", ".db_escape(AS_COMPLETED).", ".db_escape(AS_ISSUED).") AND 
                    mrd.task_id = ".db_escape($task_id)." AND 
                    mrd.requested_qty > mrd.issued_qty 
                GROUP BY mr.mr_no, mrd.task_id 
                ORDER BY mr.approved_on ";

        return db_query($sql);

}


function get_recalculate_purchase_requisition_task_details($task_id)
{

    $sql = "SELECT ".ST_PURCH_REQ." type,
                        pr.pr_no tran_no, 
                        prd.task_id, 
                        SUM(prd.estimate_price / req_unit_conv_factor * (prd.quantity - prd.issued_qty) ) task_cost, 
                        pr.approved_by created_by, 
                        pr.approved_on created_on 
                FROM ".TB_PREF."purchase_requisition_details prd  
                    JOIN ".TB_PREF."purchase_requisitions pr 
                        ON prd.requisition_id = pr.pr_no  
                WHERE pr.approval_status IN(".db_escape(AS_APPROVED).",".db_escape(AS_COMPLETED).",".db_escape(AS_ISSUED).") AND 
                    prd.task_id = ".db_escape($task_id)." AND 
                    prd.quantity > prd.issued_qty 
                GROUP BY pr.pr_no, prd.task_id 
                ORDER BY pr.approved_on ";

        return db_query($sql);

}


function get_recalculate_vehicle_request_task_details($task_id)
{

    $sql = "SELECT ".ST_VEHICLE_REQUEST." type,
                        vr.id tran_no, 
                        vr.task_id, 
                        vr.amount task_cost, 
                        vr.approved_by created_by, 
                        vr.approved_on created_on 
                FROM ".TB_PREF."vehicle_requests vr 
                WHERE vr.approval_status IN(".db_escape(AS_APPROVED).",".db_escape(AS_COMPLETED).",".db_escape(AS_ISSUED).") AND 
                    vr.task_id = ".db_escape($task_id)." AND vr.closed = 0 
                ORDER BY vr.approved_on ";

        return db_query($sql);

}


function get_recalculate_iou_request_task_details($task_id)
{

    $sql = "SELECT ".ST_I_OWE_YOU." type,
                        iou.id tran_no, 
                        ioud.task_id, 
                        ioud.amount task_cost, 
                        iou.approved_by created_by, 
                        iou.approved_on created_on,
                        iou.batch_no 
                FROM ".TB_PREF."i_owe_you_details ioud   
                    JOIN ".TB_PREF."i_owe_you iou ON 
                        iou.id = ioud.iou_id  
                    LEFT JOIN ".TB_PREF."i_owe_you_batches ioub ON 
                        iou.batch_no = ioub.batch_no AND   
                            iou.batch_no > 0 AND ioub.approval_status IN(".db_escape(AS_NEW).",".db_escape(AS_AWAITING_APPROVAL).","
                                                                    .db_escape(AS_BACK_TO_ORIGINATOR).",".db_escape(AS_WITH_THE_APPROVER).") 
                WHERE iou.approval_status IN(".db_escape(AS_APPROVED).") AND 
                    ioud.task_id = ".db_escape($task_id)." AND 
                    iou.batch_complete = 0  
                        
                GROUP BY iou.id, ioud.task_id 
                ORDER BY iou.approved_on ";

        return db_query($sql);

}


function get_recalculate_tool_request_task_details($task_id)
{

    $sql = "SELECT ".ST_TOOL_REQUEST." type,
                        tr.id tran_no, 
                        trd.task_id, 
                        SUM(trd.no_of_days * trd.rate * (trd.quantity - trd.issued_qty) ) task_cost, 
                        tr.approved_by created_by, 
                        tr.approved_on created_on 
                FROM ".TB_PREF."tool_request_details trd   
                    JOIN ".TB_PREF."tool_requests tr ON 
                        trd.ar_id = tr.id   
                WHERE tr.approval_status IN(".db_escape(AS_APPROVED).") AND 
                    trd.task_id = ".db_escape($task_id)." AND 
                    trd.quantity > trd.issued_qty
                GROUP BY tr.id, trd.task_id 
                ORDER BY tr.approved_on ";

        return db_query($sql);

}

function get_recalculate_tool_issue_task_details($task_id)
{

    $sql = "SELECT ".ST_TOOL_ISSUE." type,
                        ti.id tran_no, 
                        tid.task_id, 
                        SUM(tid.no_of_days * tid.rate * (tid.quantity - tid.returned_qty) ) task_cost, 
                        ti.created_by, 
                        ti.created_on 
                FROM ".TB_PREF."tool_issue_details tid   
                    JOIN ".TB_PREF."tool_issues ti ON 
                        tid.ai_id = ti.id   
                WHERE ti.fully_returned = 0 AND 
                    tid.task_id = ".db_escape($task_id)." AND 
                    tid.quantity > tid.returned_qty 
                GROUP BY ti.id, tid.task_id 
                ORDER BY ti.approved_on ";

        return db_query($sql);

}


?>