<?php




function get_recalculate_tool_usage_charges_task_details($task_id)
{
     $sql = "SELECT ".ST_TOOL_USAGE_CHARGE." type,
                    h.id tran_no,
                    tid.task_id,
                    SUM(d.no_of_units * d.rate) task_cost,
                    h.created_by,
                    h.created_on                    
            FROM ".TB_PREF."tool_usage_charges h 
                JOIN ".TB_PREF."tool_usage_charge_details d ON 
                    h.id = d.charge_no  
                JOIN ".TB_PREF."tool_issue_details tid ON 
                    tid.id = d.issue_detail_id  
            WHERE tid.task_id = ".db_escape($task_id)." 
            GROUP BY h.id, tid.task_id 
            ORDER BY h.created_on ";

    return db_query($sql);
}

function get_recalculate_miscellaneous_project_costs_task_details($task_id)
{
     $sql = "SELECT ".ST_MISCELLANEOUS_PROJECT_COST." type,
                    ub.id tran_no,
                    ubd.task_id,
                    SUM(ubd.quantity * ubd.rate) task_cost,
                    ub.approved_by created_by,
                    ub.approved_on created_on                    
            FROM ".TB_PREF."miscellaneous_project_costs ub
                JOIN ".TB_PREF."miscellaneous_project_cost_details ubd
                ON ub.id = ubd.mpc_id
            WHERE ubd.task_id = ".db_escape($task_id)."
                AND ub.approval_status = ".AS_APPROVED." 
            GROUP BY ub.id, ubd.task_id 
            ORDER BY ub.created_on ";

    return db_query($sql);
}

function get_recalculate_utility_bill_task_details($task_id)
{
     $sql = "SELECT ".ST_UTILITY_BILLS." type,
                    ub.id tran_no,
                    ubd.task_id,
                    SUM(ubd.amount) task_cost,
                    ub.approved_by created_by,
                    ub.approved_on created_on                    
            FROM ".TB_PREF."utility_bills ub
                JOIN ".TB_PREF."utility_bill_details ubd
                ON ub.id = ubd.ub_id
            WHERE ubd.task_id = ".db_escape($task_id)."
                AND ub.approval_status = ".AS_APPROVED."
            GROUP BY ub.id, ubd.task_id 
            ORDER BY ub.created_on ";

    return db_query($sql);
}


function get_recalculate_utility_bill_credit_note_task_details($task_id)
{
     $sql = "SELECT ".ST_UTILITY_BILL_CREDIT_NOTE." type,
                    ub.id tran_no,
                    ubd.task_id,
                    SUM(ubd.amount) task_cost,
                    ub.approved_by created_by,
                    ub.approved_on created_on,
                    IF(ub.rate_include_taxes = 0, 1, (ub.total - ub.taxes) /ub.total) tax_factor 
            FROM ".TB_PREF."utility_bill_credit_notes ub
                JOIN ".TB_PREF."utility_bill_credit_note_details ubd
                ON ub.id = ubd.ubcn_id 
            WHERE ubd.task_id = ".db_escape($task_id)."
                AND ub.approval_status = ".AS_APPROVED."
            GROUP BY ub.id, ubd.task_id 
            ORDER BY ub.created_on ";

    return db_query($sql);
}


function get_supplier_transport_invoice_task_details($task_id)
{
     $sql = "SELECT ".ST_SUPPLIER_TRANSPORT_INVOICES." type,
                    sti.id tran_no,
                    sti.task_id,
                    SUM(vrc.cost) task_cost,
                    sti.approved_by created_by,
                    sti.approved_on created_on                    
            FROM ".TB_PREF."supplier_transport_invoices sti
                JOIN ".TB_PREF."vehicle_running_chart vrc
                ON sti.id = vrc.inv_no
            WHERE sti.task_id = ".db_escape($task_id)."
                AND sti.approval_status = ".AS_APPROVED."
            GROUP BY sti.id, sti.task_id 
            ORDER BY sti.approved_on ";

    return db_query($sql);
}

function get_subcontractor_invoice_task_details($task_id)
{
    $sql = "SELECT ".ST_SUBCONTRACTOR_INVOICE." type,
                    si.order_no tran_no,
                    sid.task_id,
                    SUM(sid.amount) task_cost,
                    si.approved_by created_by,
                    si.approved_on created_on                    
            FROM ".TB_PREF."subcontractor_invoices si 
                JOIN ".TB_PREF."subcontractor_invoice_details sid
                ON si.order_no = sid.order_no
            WHERE sid.task_id = ".db_escape($task_id)."
                AND si.approval_status = ".AS_APPROVED."
            GROUP BY si.order_no, sid.task_id 
            ORDER BY si.approved_on ";

    return db_query($sql);
}


function get_subcontractor_credit_notes_task_details($task_id)
{
     $sql = "SELECT ".ST_SUBCONTRACTOR_CREDIT_NOTE." type,
                    scn.order_no tran_no,
                    scnd.task_id,
                    SUM(-scnd.amount) task_cost,
                    scn.approved_by created_by,
                    scn.approved_on created_on,
                    IF(scn.rate_include_taxes = 0, 1, (scn.total - scn.taxes) /scn.total) tax_factor 
            FROM ".TB_PREF."subcontractor_credit_notes scn  
                JOIN ".TB_PREF."subcontractor_credit_note_details scnd
                ON scn.order_no = scnd.order_no
            WHERE scnd.task_id = ".db_escape($task_id)."
                    AND scn.approval_status = ".AS_APPROVED."  
                    AND scnd.task_id > 0 
            GROUP BY scn.order_no, scnd.task_id 
            ORDER BY scn.approved_on ";

    return db_query($sql);
}


function get_task_cost_adjustment_task_details($task_id)
{
    $sql = "SELECT ".ST_PROJECT_COST_ADJUST." type,
                    tca.id tran_no,
                    tca.task_id,
                    SUM(tca.amount) task_cost,  
                    tca.added_by created_by,
                    tca.added_on created_on  
            FROM ".TB_PREF."task_cost_adjustments tca 
            WHERE tca.task_id = ".db_escape($task_id)."
            GROUP BY tca.id, tca.task_id 
            ORDER BY tca.added_on";

    return db_query($sql);
}

function get_vehicle_running_chart_batch_task_details($task_id)
{
    $sql = "SELECT ".ST_VEHICLE_RUNNING_CHART_BATCH." type,
                    vrc.batch_no tran_no,
                    vrc.task_id,
                    SUM(vrc.cost) task_cost,  
                    vrc.created_by,
                    vrc.created_on  
            FROM ".TB_PREF."vehicle_running_chart vrc 
            WHERE vrc.task_id = ".db_escape($task_id)." AND 
                    vrc.batch_no > 0 
            GROUP BY vrc.batch_no, vrc.task_id 
            ORDER BY vrc.created_on";

    return db_query($sql);
}

function get_material_return_task_details($task_id)
{
     $sql = "SELECT ".ST_MATERIAL_RETURN." type,
                    rm.rm_no tran_no,
                    rmd.task_id,
                    SUM(rmd.unit_cost * rmd.qty_returned)* (-1) task_cost,
                    rm.created_by,
                    rm.created_on                    
            FROM ".TB_PREF."return_materials rm 
                JOIN ".TB_PREF."return_material_details rmd
                ON rm.rm_no = rmd.rm_no
            WHERE rmd.task_id = ".db_escape($task_id)."
            GROUP BY rm.rm_no, rmd.task_id 
            ORDER BY rm.created_on ";

    return db_query($sql);
}

function get_i_owe_you_expenses_task_details($task_id)
{
    $sql = "SELECT ".ST_I_OWE_YOU_BATCH." type,
                    ioye.iou_batch_no tran_no,
                    ioye.task_id,
                    SUM(ioye.approved_amount) task_cost,  
                    ioyb.approved_by created_by,
                    ioyb.approved_on created_on  
            FROM ".TB_PREF."i_owe_you_batches ioyb
                JOIN ".TB_PREF."i_owe_you_expenses ioye
                ON ioyb.batch_no = ioye.iou_batch_no
            WHERE ioye.task_id = ".db_escape($task_id)."
                AND ioyb.approval_status = ".AS_APPROVED." 
            GROUP BY ioye.iou_batch_no, ioye.task_id 
            ORDER BY ioyb.approved_on ";

    return db_query($sql);
}

function get_purchase_requistion_issues_task_details($task_id)
{
    $sql = "SELECT ".ST_PR_ISSUE." type,
                    pri.pri_no tran_no,
                    prid.task_id,
                    SUM(prid.unit_cost * prid.qty_issued) task_cost,
                    pri.created_by,
                    pri.created_on                    
            FROM ".TB_PREF."purchase_requisition_issues pri 
                JOIN ".TB_PREF."purchase_requistion_issue_details prid
                ON pri.pri_no = prid.pri_no
            WHERE prid.task_id = ".db_escape($task_id)."
            GROUP BY pri.pri_no, prid.task_id 
            ORDER BY pri.created_on ";

    return db_query($sql);
}

function get_grn_task_details($task_id)
{
    $sql = "SELECT ".ST_SUPPRECEIVE." type
                    sm.trans_no tran_no, 
                    sm.task_id, 
                    SUM(sm.standard_cost * sm.qty) task_cost, 
                    gb.created_by, 
                    gb.created_on
            FROM ".TB_PREF."grn_items gi
                JOIN ".TB_PREF."grn_batch gb 
                ON gi.grn_batch_id = gb.id 
                JOIN ".TB_PREF."stock_moves sm 
                ON gb.id = sm.trans_no 
            WHERE sm.task_id = ".db_escape($task_id)." 
                AND sm.type = ".ST_SUPPRECEIVE."
            GROUP BY gb.id, sm.task_id";

    return db_query($sql);
}


function get_stock_count_task_details($task_id)
{

    $sql = "SELECT ".ST_STOCK_COUNT." type,
                    sm.trans_no tran_no, 
                    sm.task_id,
                    SUM(sm.standard_cost * sm.qty) task_cost,
                    sc.created_by,
                    sc.created_on                    
            FROM ".TB_PREF."stock_counts sc
                JOIN ".TB_PREF."stock_moves sm 
                ON sc.stock_count_no = sm.trans_no
            WHERE sm.task_id = ".db_escape($task_id)." 
                AND sm.type = ".ST_STOCK_COUNT." 
            GROUP BY sm.trans_no, sm.task_id";

    return db_query($sql);
    
}


function get_inventory_adjustments_task_cost_details($type, $task_id)
{
      
    $sql = "SELECT sa.type, 
                    IF(pm.purchase_to_stock = 0, SUM(sm.standard_cost * sm.qty)* (-1), SUM(sm.standard_cost * sm.qty)) task_cost,
                    sm.trans_no tran_no, 
                    sm.task_id,
                    sa.created_by,
                    sa.created_on                    
            FROM ".TB_PREF."stock_adjustments sa
                JOIN ".TB_PREF."stock_moves sm
                ON sa.type = sm.type
                JOIN ".TB_PREF."project_tasks pt
                ON pt.task_id = sm.task_id
                JOIN ".TB_PREF."projects_master pm
                ON pm.project_id = pt.project_id
            WHERE sa.type = ".db_escape($type)."
                AND sa.id = sm.trans_no
                AND sm.task_id = ".db_escape($task_id)." 
            GROUP BY sa.type, sm.trans_no, sm.task_id";
    
//    $sql = "SELECT sa.type,
//                    sm.trans_no tran_no, 
//                    sm.task_id,
//                    SUM(sm.standard_cost * sm.qty) task_cost,
//                    created_by,
//                    created_on                    
//            FROM ".TB_PREF."stock_adjustments sa
//                JOIN ".TB_PREF."stock_moves sm
//                ON sa.type = sm.type 
//                AND sa.id = sm.trans_no
//            WHERE sa.type = ".db_escape($type)." 
//                AND sm.task_id = ".db_escape($task_id)." 
//            GROUP BY sa.type, sm.trans_no, sm.task_id";

    return db_query($sql);

}

function get_material_issues_task_cost_details($task_id)
{

    $sql = "SELECT ".ST_MATERIAL_ISSUE." type,
                    im.im_no tran_no, 
                    imd.task_id, 
                    SUM(imd.unit_cost * imd.qty_issued) task_cost, 
                    im.created_by, 
                    im.created_on
            FROM ".TB_PREF."issue_material_details imd 
                JOIN ".TB_PREF."issue_materials im 
                ON imd.im_no = im.im_no 
            WHERE imd.task_id = ".db_escape($task_id)."
            GROUP BY im.im_no, imd.task_id 
            ORDER BY im.created_on ";

    return db_query($sql);

}

function update_rebuilt_total_task_cost($task_id)
{
    $total_cost = 0;
    
    $sql = "SELECT id,
                   amount
            FROM ".TB_PREF."task_costs_rebuild
            WHERE task_id = ".db_escape($task_id)." AND allocation = 0 
            ORDER BY added_on, task_id ";
    
    $result = db_query($sql);
    
    while($row = db_fetch($result))
    {
        $total_cost = ($total_cost + $row['amount']);
        
        $sq = "UPDATE ".TB_PREF."task_costs_rebuild
                SET total_cost = ".db_escape($total_cost)."
                WHERE id = ".db_escape($row['id'])."";
        
        db_query($sq);
    }
}

function add_to_task_costs_rebuild($type, $tran_no, $task_id, $amount, $created_by, $created_on, $allocation)
{
    $ist = "INSERT INTO ".TB_PREF."task_costs_rebuild (
                    tran_type, tran_no, 
                    task_id, amount,
                    added_by, added_on,
                    allocation) 
                VALUES (".db_escape($type).", ".db_escape($tran_no).", " 
                    .db_escape($task_id).", ".db_escape($amount).", "
                    .db_escape($created_by).", ".db_escape($created_on).", "
                    .db_escape($allocation).")";
    
     db_query($ist);

}


function delete_task_cost_rebuild_task_details($task_id)
{
    $sql = "DELETE  FROM ".TB_PREF."task_costs_rebuild  
            WHERE task_id = ".db_escape($task_id);

    return db_query($sql);

}


function delete_task_cost_cost_recalculations($task_id)
{
    $sql = "DELETE  FROM ".TB_PREF."project_task_cost_recalculations  
            WHERE task_id = ".db_escape($task_id);

    return db_query($sql);

}

function add_task_cost_cost_recalculation($task_id, $row_state)
{
    $id_user = $_SESSION['wa_current_user']->user;
    $datetime = date('Y-m-d H:i:s');        

    $sql = "INSERT INTO ".TB_PREF."project_task_cost_recalculations (
                        task_id, task_row_state, 
                        start_time, 
                        created_by, created_on, 
                        jobq_request_id)
            VALUES (".db_escape($task_id).", ".db_escape($row_state).", "
                        .db_escape($datetime).", "
                        .db_escape($id_user).", ".db_escape($datetime).", 0)";
        
    db_query($sql);
    
    return db_insert_id();
}

function update_task_cost_recalculation_completed($task_id, $row_state)
{

    $datetime = date('Y-m-d H:i:s');        

    $sql = "UPDATE ".TB_PREF."project_task_cost_recalculations 
                    SET end_time = ".db_escape($datetime)." 
            WHERE  task_row_state = ".db_escape($row_state)." AND 
                    task_id = ".db_escape($task_id);
        
    db_query($sql);
    
    return db_num_affected_rows();
}

function get_task_cost_recalculation_record($task_id)
{
    $sql = "SELECT *  
            FROM ".TB_PREF."project_task_cost_recalculations 
            WHERE task_id=".db_escape($task_id);
    
    $result = db_query($sql); 
    return db_fetch($result);
}

function update_task_cost_recalculation_cost_updated($task_id, $task_current_row_state)
{

    $id_user = $_SESSION['wa_current_user']->user;
    $datetime = date('Y-m-d H:i:s');       

    $sql = "UPDATE ".TB_PREF."project_task_cost_recalculations 
                    SET updated_by = ".db_escape($id_user).", 
                        updated_on = ".db_escape($datetime)." 
            WHERE  task_row_state = ".db_escape($task_current_row_state)." AND 
                    task_id = ".db_escape($task_id);
        
    db_query($sql);
    
    return db_num_affected_rows();
}


?>