<?php

function get_material_issue_cost_details($project_id, $job_id, $stock_id, $location) {
    $sql = "SELECT im.project_id, l.location_name, sk.editable, di.item_description item_transaction_description, sk.description sm_description, 
                pm.description project_name, u.name tran_unit_name, sm.* 
            FROM ".TB_PREF."issue_materials im 
                JOIN ".TB_PREF."issue_material_details di  
                    ON im.im_no = di.im_no  
                JOIN ".TB_PREF."stock_moves sm 
                    ON im.im_no = sm.trans_no 
                        AND di.mr_line_id = sm.ref_line_id 
                        AND sm.type = ".db_escape(ST_MATERIAL_ISSUE)." 
                JOIN ".TB_PREF."stock_master sk  
                    ON sm.stock_id = sk.stock_id  
                LEFT JOIN ".TB_PREF."projects_master pm  
                    ON pm.project_id = im.project_id 
                LEFT JOIN ".TB_PREF."item_units u   
                    ON sm.tran_unit = u.abbr 
                LEFT JOIN ".TB_PREF."locations l    
                    ON sm.loc_code = l.loc_code  
            WHERE sm.type = ".db_escape(ST_MATERIAL_ISSUE);
    
        if ($project_id > 0) {
            $sql .= " AND im.project_id = ".db_escape($project_id);
        }
        if ($job_id > 0) {
            $sql .= " AND sm.project_site_id = ".db_escape($job_id);
        }
        if (strlen($stock_id) > 0) {
            $sql .= " AND sm.stock_id = ".db_escape($stock_id); 
        }
        if (strlen($location) > 0) {
            $sql .= " AND sm.loc_code = ".db_escape($location);
        }

        $sql .= " ORDER BY sm.tran_date, sm.trans_id ";
        
        return db_query($sql, "get_material_issue_cost_details");
}

function get_material_return_cost_details($project_id, $job_id, $stock_id, $location) {
    $sql = "SELECT rm.project_id, l.location_name, sk.editable, di.item_description item_transaction_description, sk.description sm_description, 
                pm.description project_name, u.name tran_unit_name, sm.* 
            FROM ".TB_PREF."return_materials rm 
                JOIN ".TB_PREF."return_material_details di  
                    ON rm.rm_no = di.rm_no  
                JOIN ".TB_PREF."stock_moves sm 
                    ON rm.rm_no = sm.trans_no 
                        AND di.id = sm.ref_line_id 
                        AND sm.type = ".db_escape(ST_MATERIAL_RETURN)." 
                JOIN ".TB_PREF."stock_master sk  
                    ON sm.stock_id = sk.stock_id  
                LEFT JOIN ".TB_PREF."projects_master pm  
                    ON pm.project_id = rm.project_id 
                LEFT JOIN ".TB_PREF."item_units u   
                    ON sm.tran_unit = u.abbr 
                LEFT JOIN ".TB_PREF."locations l    
                    ON sm.loc_code = l.loc_code  
            WHERE sm.type = ".db_escape(ST_MATERIAL_RETURN);
    
        if ($project_id > 0) {
            $sql .= " AND rm.project_id = ".db_escape($project_id);
        }
        if ($job_id > 0) {
            $sql .= " AND sm.project_site_id = ".db_escape($job_id);
        }
        if (strlen($stock_id) > 0) {
            $sql .= " AND sm.stock_id = ".db_escape($stock_id); 
        }
        if (strlen($location) > 0) {
            $sql .= " AND sm.loc_code = ".db_escape($location);
        }

        $sql .= " ORDER BY sm.tran_date, sm.trans_id ";
        
        return db_query($sql, "get_material_return_cost_details");
}

function get_inventory_receipt_cost_details($project_id, $job_id, $type, $stock_id, $location, $req_type) {
    $sql = "SELECT sa.project_id, l.location_name, sk.editable, di.item_description item_transaction_description, sk.description sm_description, 
                pm.description project_name, u.name tran_unit_name, sm.* 
            FROM ".TB_PREF."stock_adjustments sa 
                JOIN ".TB_PREF."stock_moves sm 
                    ON sa.id = sm.trans_no 
                        AND sa.type = sm.type  
                JOIN ".TB_PREF."stock_adjustment_request_details di  
                    ON di.trans_id = sm.ref_line_id   
                        AND di.type = ".db_escape($req_type)." 
                JOIN ".TB_PREF."stock_master sk  
                    ON sm.stock_id = sk.stock_id  
                LEFT JOIN ".TB_PREF."projects_master pm  
                    ON pm.project_id = sa.project_id 
                LEFT JOIN ".TB_PREF."item_units u   
                    ON sm.tran_unit = u.abbr 
                LEFT JOIN ".TB_PREF."locations l    
                    ON sm.loc_code = l.loc_code  
            WHERE sm.type = ".db_escape($type);
    
        if ($project_id > 0) {
            $sql .= " AND sa.project_id = ".db_escape($project_id);
        }
        if ($job_id > 0) {
            $sql .= " AND sm.project_site_id = ".db_escape($job_id);
        }
        if (strlen($stock_id) > 0) {
            $sql .= " AND sm.stock_id = ".db_escape($stock_id); 
        }
        if (strlen($location) > 0) {
            $sql .= " AND sm.loc_code = ".db_escape($location);
        }

        $sql .= " ORDER BY sm.tran_date, sm.trans_id ";
        
        return db_query($sql, "get_inventory_receipt_cost_details");
}

function get_pr_issue_cost_details($project_id, $job_id, $stock_id, $location) {
    $sql = "SELECT pri.project_id, l.location_name, sk.editable, di.description item_transaction_description, sk.description sm_description, 
                pm.description project_name, u.name tran_unit_name, sm.* 
            FROM ".TB_PREF."purchase_requisition_issues pri   
                JOIN ".TB_PREF."purchase_requistion_issue_details di 
                    ON pri.pri_no = di.pri_no  
                JOIN ".TB_PREF."stock_moves sm 
                    ON pri.pri_no = sm.trans_no 
                        AND di.id = sm.ref_line_id   
                JOIN ".TB_PREF."stock_master sk  
                    ON sm.stock_id = sk.stock_id  
                LEFT JOIN ".TB_PREF."projects_master pm  
                    ON pm.project_id = pri.project_id 
                LEFT JOIN ".TB_PREF."item_units u   
                    ON sm.tran_unit = u.abbr 
                LEFT JOIN ".TB_PREF."locations l    
                    ON sm.loc_code = l.loc_code  
            WHERE sm.type = ".db_escape(ST_PR_ISSUE);
    
        if ($project_id > 0) {
            $sql .= " AND pri.project_id = ".db_escape($project_id);
        }
        if ($job_id > 0) {
            $sql .= " AND sm.project_site_id = ".db_escape($job_id);
        }
        if (strlen($stock_id) > 0) {
            $sql .= " AND sm.stock_id = ".db_escape($stock_id); 
        }
        if (strlen($location) > 0) {
            $sql .= " AND sm.loc_code = ".db_escape($location);
        }

        $sql .= " ORDER BY sm.tran_date, sm.trans_id ";
        
        return db_query($sql, "get_pr_issue_cost_details");
}

function get_pr_return_cost_details($project_id, $job_id, $stock_id, $location) {
    $sql = "SELECT prr.project_id, l.location_name, sk.editable, di.description item_transaction_description, sk.description sm_description, 
                pm.description project_name, u.name tran_unit_name, sm.* 
            FROM ".TB_PREF."purchase_requisition_returns prr   
                JOIN ".TB_PREF."purchase_requistion_return_details di  
                    ON prr.prr_no = di.prr_no  
                JOIN ".TB_PREF."stock_moves sm 
                    ON prr.prr_no = sm.trans_no 
                        AND di.id = sm.ref_line_id   
                JOIN ".TB_PREF."stock_master sk  
                    ON sm.stock_id = sk.stock_id  
                LEFT JOIN ".TB_PREF."projects_master pm  
                    ON pm.project_id = prr.project_id 
                LEFT JOIN ".TB_PREF."item_units u   
                    ON sm.tran_unit = u.abbr 
                LEFT JOIN ".TB_PREF."locations l    
                    ON sm.loc_code = l.loc_code  
            WHERE sm.type = ".db_escape(ST_PR_RETURN);
    
        if ($project_id > 0) {
            $sql .= " AND prr.project_id = ".db_escape($project_id);
        }
        if ($job_id > 0) {
            $sql .= " AND sm.project_site_id = ".db_escape($job_id);
        }
        if (strlen($stock_id) > 0) {
            $sql .= " AND sm.stock_id = ".db_escape($stock_id); 
        }
        if (strlen($location) > 0) {
            $sql .= " AND sm.loc_code = ".db_escape($location);
        }

        $sql .= " ORDER BY sm.tran_date, sm.trans_id ";
        
        return db_query($sql, "get_pr_return_cost_details");
}

function get_daily_item_issue_cost_details($project_id, $job_id, $type, $stock_id, $location) {
    $sql = "SELECT tran.project_id, l.location_name, sk.editable, di.item_description item_transaction_description, sk.description sm_description, 
                pm.description project_name, u.name tran_unit_name, sm.*  
            FROM ".TB_PREF."daily_item_issues tran   
                JOIN ".TB_PREF."daily_item_issue_details di  
                    ON di.trans_no = tran.trans_no  
                        AND di.type = tran.type   
                JOIN ".TB_PREF."stock_moves sm 
                    ON di.trans_no = sm.trans_no 
                        AND di.type = sm.type 
                        AND di.trans_id = sm.ref_line_id  
                        AND sm.type = ".db_escape($type)." 
                LEFT JOIN ".TB_PREF."stock_master sk  
                    ON sm.stock_id = sk.stock_id   
                LEFT JOIN ".TB_PREF."projects_master pm  
                    ON pm.project_id = tran.project_id 
                LEFT JOIN ".TB_PREF."item_units u   
                    ON sm.tran_unit = u.abbr    
                LEFT JOIN ".TB_PREF."locations l    
                    ON sm.loc_code = l.loc_code 
            WHERE tran.type = ".db_escape($type)." 
                AND tran.approval_status = ".db_escape(AS_APPROVED)." 
                AND di.daily_item_type = ".db_escape(NSIT_STOCK_ITEM);

        if ($project_id > 0) {
            $sql .= " AND tran.project_id = ".db_escape($project_id);
        }
        if ($job_id > 0) {
            $sql .= " AND sm.project_site_id = ".db_escape($job_id);
        }
        if (strlen($stock_id) > 0) {
            $sql .= " AND sm.stock_id = ".db_escape($stock_id); 
        }
        if (strlen($location) > 0) {
            $sql .= " AND sm.loc_code = ".db_escape($location);
        }

        $sql .= " ORDER BY sm.tran_date, sm.trans_id ";
        
        return db_query($sql, "get_daily_item_issue_cost_details");
}

function get_raw_material_issue_cost_details($project_id, $job_id, $type, $stock_id, $location) {
    $sql = "SELECT tran.project_id, l.location_name, sk.editable, di.item_description item_transaction_description, sk.description sm_description, 
                pm.description project_name, u.name tran_unit_name, sm.*  
            FROM ".TB_PREF."raw_material_issues tran   
                JOIN ".TB_PREF."raw_material_issue_details di  
                    ON di.trans_no = tran.trans_no  
                        AND di.type = tran.type   
                JOIN ".TB_PREF."stock_moves sm 
                    ON di.trans_no = sm.trans_no 
                        AND di.type = sm.type 
                        AND di.id = sm.ref_line_id  
                        AND sm.type = ".db_escape($type)." 
                LEFT JOIN ".TB_PREF."stock_master sk  
                    ON sm.stock_id = sk.stock_id   
                LEFT JOIN ".TB_PREF."projects_master pm  
                    ON pm.project_id = tran.project_id 
                LEFT JOIN ".TB_PREF."item_units u   
                    ON sm.tran_unit = u.abbr    
                LEFT JOIN ".TB_PREF."locations l    
                    ON sm.loc_code = l.loc_code 
            WHERE tran.type = ".db_escape($type)." 
                AND tran.approval_status = ".db_escape(AS_APPROVED);

        if ($project_id > 0) {
            $sql .= " AND tran.project_id = ".db_escape($project_id);
        }
        if ($job_id > 0) {
            $sql .= " AND sm.project_site_id = ".db_escape($job_id);
        }
        if (strlen($stock_id) > 0) {
            $sql .= " AND sm.stock_id = ".db_escape($stock_id); 
        }
        if (strlen($location) > 0) {
            $sql .= " AND sm.loc_code = ".db_escape($location);
        }

        $sql .= " ORDER BY sm.tran_date, sm.trans_id ";
        
        return db_query($sql, "get_raw_material_issue_cost_details");
}

function get_daily_tool_return_cost_details($project_id, $job_id, $type) {
    $sql = "SELECT tran.project_id, l.location_name, sk.asset_code, sk.description, sm.id trans_id, sm.qty tran_qty, di.rate, 0 convertion_factor, 
                0 standard_cost, pm.description project_name, '' tran_unit_name, sm.*, sm.trans_type type 
            FROM ".TB_PREF."daily_tool_issues tran   
                JOIN ".TB_PREF."daily_tool_issue_details di  
                    ON di.trans_no = tran.trans_no  
                        AND di.type = tran.type   
                JOIN ".TB_PREF."asset_moves sm 
                    ON di.trans_no = sm.trans_no 
                        AND di.type = sm.trans_type 
                        AND di.id = sm.line_id  
                        AND sm.trans_type = ".db_escape($type)." 
                LEFT JOIN ".TB_PREF."asset_master sk  
                    ON sm.asset_id = sk.asset_id    
                LEFT JOIN ".TB_PREF."projects_master pm  
                    ON pm.project_id = tran.project_id 
                LEFT JOIN ".TB_PREF."asset_locations l    
                    ON sm.loc_code = l.loc_code 
            WHERE tran.type = ".db_escape($type)." 
                AND tran.approval_status = ".db_escape(AS_APPROVED);

        if ($project_id > 0) {
            $sql .= " AND tran.project_id = ".db_escape($project_id);
        }
        if ($job_id > 0) {
            $sql .= " AND sm.site_id = ".db_escape($job_id);
        }

        $sql .= " ORDER BY sm.tran_date, sm.id ";
        
        return db_query($sql, "get_daily_tool_return_cost_details");
}

function get_tool_usage_charges_details($project_id, $job_id, $type) {
    $sql = "SELECT ti.project_id, l.location_name, am.asset_code, am.description, tucd.id trans_id, tid.quantity tran_qty, tucd.no_of_units, tucd.rate, 
                0 convertion_factor, 0 standard_cost, pm.description project_name, '' tran_unit_name, tid.*, $type type, ti.tran_date, ti.id trans_no 
            FROM ".TB_PREF."tool_usage_charges tuc 
                JOIN ".TB_PREF."tool_usage_charge_details tucd 
                    ON tuc.id = tucd.charge_no 
                JOIN ".TB_PREF."tool_issue_details tid 
                    ON tid.id = tucd.issue_detail_id 
                JOIN ".TB_PREF."asset_master am 
                    ON am.asset_id = tid.asset_id                 
                JOIN ".TB_PREF."tool_issues ti  
                    ON ti.id = tid.ai_id                 
                JOIN ".TB_PREF."projects_master pm   
                    ON pm.project_id = ti.project_id  
                LEFT JOIN ".TB_PREF."asset_locations l    
                    ON ti.location = l.loc_code 
            WHERE 1 = 1 ";
            
        if ($project_id > 0) {
            $sql .= " AND ti.project_id = ".db_escape($project_id);
        }
        if ($job_id > 0) {
            $sql .= " AND tid.site_id = ".db_escape($job_id);
        }

        $sql .= " ORDER BY tucd.from_date, tucd.id ";
        
        return db_query($sql, "get_tool_usage_charges_details");
}



function get_job_cost_adjustments_details($project_id, $job_id, $fixed_type, $type) 
{
    $sql = "SELECT  jca.type,
                    jca.trans_no,
                    jca.tran_date,
                    jcad.trans_id,
                    jcad.item_description,
                    jcad.cost,
                    jcad.line_comment
            FROM ".TB_PREF."job_cost_additions as jca 
                JOIN ".TB_PREF."job_cost_addition_details as jcad 
                    ON jcad.type = jca.type 
                    AND jcad.trans_no = jca.trans_no 
            WHERE jca.type = ". db_escape($fixed_type);
    
    
    if ($project_id) {
        $sql .= " AND jca.project_id = ". db_escape($project_id);
    }
    if ($job_id) {
        $sql .= " AND jca.project_site_id = ". db_escape($job_id);
    }
    if ($type) {
        $sql .= " AND jca.type = ". db_escape($type);
    }
    


    $sql .= " ORDER BY jca.tran_date, jca.trans_no ";
        
    return db_query($sql, "get_job_cost_adjustments_details");
}

function get_utility_bill_cost_details($project_id, $job_id, $type) 
{
    $sql = "SELECT ubh.project_id, ubh.taxes, ubh.total,
                ubd.id trans_id, ubd.bill_reference, ubd.description, ubd.amount, ubd.expense_type_id, ubd.expense_category_id, ubd.expense_item_id, ubd.project_site_id,
                0 tran_qty, 1 convertion_factor, ubd.amount standard_cost, '' location_name, '' stock_id, 0 editable, '' sm_description, '' item_transaction_description, '' tran_unit_name, 
                pm.description project_name, 
                $type type, ubh.date tran_date, ubh.id trans_no 
            FROM ".TB_PREF."utility_bills ubh  
                JOIN ".TB_PREF."utility_bill_details ubd  
                    ON ubh.id = ubd.ub_id                  
                JOIN ".TB_PREF."projects_master pm   
                    ON pm.project_id = ubh.project_id  
                JOIN ".TB_PREF."project_sites ps    
                    ON ps.id = ubd.project_site_id  
            WHERE ubh.approval_status = ".AS_APPROVED;
            
        if ($project_id > 0) {
            $sql .= " AND ubh.project_id = ".db_escape($project_id);
        }
        if ($job_id > 0) {
            $sql .= " AND ubd.project_site_id = ".db_escape($job_id);
        }

        $sql .= " ORDER BY ubh.date, ubd.id ";
        
        return db_query($sql, "get_utility_bill_cost_details");
}

function get_utility_bill_cn_cost_details($project_id, $job_id, $type) 
{
    $sql = "SELECT ubh.project_id, ubh.taxes, ubh.total,
                ubd.id trans_id, ubd.bill_reference, ubd.description, (ubd.amount * -1) amount, ubd.expense_type_id, ubd.expense_category_id, ubd.expense_item_id, ubd.project_site_id,
                0 tran_qty, 1 convertion_factor, ubd.amount standard_cost, '' location_name, '' stock_id, 0 editable, '' sm_description, '' item_transaction_description, '' tran_unit_name, 
                pm.description project_name, 
                $type type, ubh.date tran_date, ubh.id trans_no 
            FROM ".TB_PREF."utility_bill_credit_notes ubh  
                JOIN ".TB_PREF."utility_bill_credit_note_details ubd  
                    ON ubh.id = ubd.ubcn_id                  
                JOIN ".TB_PREF."projects_master pm   
                    ON pm.project_id = ubh.project_id  
                JOIN ".TB_PREF."project_sites ps    
                    ON ps.id = ubd.project_site_id  
            WHERE ubh.approval_status = ".AS_APPROVED;
            
        if ($project_id > 0) {
            $sql .= " AND ubh.project_id = ".db_escape($project_id);
        }
        if ($job_id > 0) {
            $sql .= " AND ubd.project_site_id = ".db_escape($job_id);
        }

        $sql .= " ORDER BY ubh.date, ubd.id ";
        
        return db_query($sql, "get_utility_bill_cn_cost_details");
}

function get_i_owe_you_cost_details($project_id, $job_id, $type) 
{
    $sql = "SELECT h.project_id, 
                0 tran_qty, 0 convertion_factor, 0 standard_cost, '' location_name,
                d.id trans_id, d.bill_no, d.expence_description, d.approved_amount , 
                $type type, h.tran_date, h.batch_no trans_no 
            FROM ".TB_PREF."i_owe_you_batches h  
                JOIN ".TB_PREF."i_owe_you_expenses d  
                    ON h.batch_no = d.iou_batch_no                  
                JOIN ".TB_PREF."projects_master pm   
                    ON pm.project_id = h.project_id  
                JOIN ".TB_PREF."project_sites ps    
                    ON ps.id = d.project_site_id  
            WHERE h.approval_status = 30 
                AND d.expense_category_id <> ".EC_STOCK_ITEM_RECEIPT;
            
        if ($project_id > 0) {
            $sql .= " AND h.project_id = ".db_escape($project_id);
        }
        if ($job_id > 0) {
            $sql .= " AND d.project_site_id = ".db_escape($job_id);
        }

        $sql .= " ORDER BY h.tran_date, d.id ";
        
        return db_query($sql, "get_i_owe_you_cost_details");
}