<?php

function add_job_order_labour($jo_id, $row_state, $employee_code, $from_date, $to_date, $num_days_worked, $emp_rate_category, $rate, $description) {
    $fun_args = func_get_args();
    $args = (object)array_combine(array('jo_id', 'row_state', 'employee_code', 'from_date', 'to_date', 'num_days_worked', 'emp_rate_category', 
                                        'rate', 'description'), $fun_args);

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

        $sql = "INSERT INTO ".TB_PREF."job_order_labours   
                      ( jo_id, employee_code, from_date, 
                        to_date, num_days_worked, emp_rate_category, 
                        rate, description, created_by, created_on )         
                VALUES( ".db_escape($jo_id).", ".db_escape($employee_code).", ".db_escape(date2sql($from_date)).", 
                        ".db_escape(date2sql($to_date)).", ".db_escape($num_days_worked).", ".db_escape($emp_rate_category).", 
                        ".db_escape($rate).", ".db_escape($description).", ".db_escape($created_by).", ".db_escape($created_on).")";

            db_query($sql, "add_job_order_labour");
            $id = db_insert_id();

            if (! ($id > 0)) {
                cancel_transaction();

                return array('success' => false, 'message' => 'Could not add job card labour.');
            }

            $hd_af_row = update_job_order_row_state($jo_id, $row_state);
                if ($hd_af_row != 1) {
                    cancel_transaction();

                    return array('success' => false, 'message' => 'Another user has updated job card. Could not update job card row state.');
                }

	return array('success' => true, 'message' => "", 'id' => $id);
}

function update_job_order_labour($jo_id, $id, $row_state, $employee_code, $from_date, $to_date, $num_days_worked, $emp_rate_category, $rate, 
                                 $description) {
    $fun_args = func_get_args();
    $args = (object)array_combine(array('jo_id', 'id', 'row_state', 'employee_code', 'from_date', 'to_date', 'num_days_worked', 'emp_rate_category', 
                                        'rate', 'description'), $fun_args);

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

        $sql = "UPDATE ".TB_PREF."job_order_labours  
                SET employee_code = ".db_escape($employee_code).",
                    from_date = ".db_escape(date2sql($from_date)).",
                    to_date = ".db_escape(date2sql($to_date)).",
                    num_days_worked = ".db_escape($num_days_worked).",
                    emp_rate_category = ".db_escape($emp_rate_category).",
                    rate = ".db_escape($rate).",
                    description = ".db_escape($description).",
                    updated_by = ".db_escape($updated_by).",
                    updated_on = ".db_escape($updated_on)." 
                WHERE id = ".db_escape($id);

            db_query($sql, "update_job_order_labour");
            $num_af_row = db_num_affected_rows();

            if ($num_af_row != 1) {
                cancel_transaction();

                return array('success' => false, 'message' => "Could not update job card labour record #$id.");
            }

            $hd_af_row = update_job_order_row_state($jo_id, $row_state);
                if ($hd_af_row != 1) {
                    cancel_transaction();

                    return array('success' => false, 'message' => 'Another user has updated job card. Could not update job card row state.');
                }

        return array('success' => true, 'message' => "");
}

function get_job_order_labours_details($jo_id) {
    $sql = "SELECT er.*, lv.value_name rate_category_name  
            FROM ".TB_PREF."job_order_labours er 
                JOIN ".TB_PREF."list_values lv   
                    ON er.emp_rate_category = lv.id 
            WHERE er.jo_id = ".db_escape($jo_id);

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

function get_job_order_labour_record($id) {
    $sql = "SELECT *
            FROM ".TB_PREF."job_order_labours  
            WHERE id = ".db_escape($id);

	$result = db_query($sql, "get_job_order_labour_record");
        return db_fetch($result);
}

function delete_job_order_labour($id) {
    $sql = "DELETE 
            FROM ".TB_PREF."job_order_labours  
            WHERE id = ".db_escape($id);

	db_query($sql, "delete_job_order_labour");
        return db_num_affected_rows();
}

function check_job_order_labour_overlapping($employee_code, $from_date, $to_date, $not_id=0) {
    $sql = "SELECT er.*, lv.value_name rate_category_name  
            FROM ".TB_PREF."job_order_labours er 
                JOIN ".TB_PREF."list_values lv  
                    ON er.emp_rate_category = lv.id 
            WHERE er.employee_code = ".db_escape($employee_code)." 
                AND ((er.to_date >= ".db_escape(date2sql($from_date))." AND er.from_date <= ".db_escape(date2sql($to_date)).") 
                OR (er.to_date >= ".db_escape(date2sql($to_date))." AND er.from_date <= ".db_escape(date2sql($from_date))."))";
        
        if ($not_id > 0) {
            $sql .= " AND er.id <> ".db_escape($not_id);
        }
        
	$result = db_query($sql, "check_job_order_labour_overlapping");
	return db_fetch($result);
}

function get_job_order_labour_details_by_filters($id=0, $jo_id=0, $employee_code=0, $from_date_='', $to_date_='', $emp_rate_category=0, $fetch=false) {
    $sql = "SELECT la.*, rc.value_name emp_rate_category_name 
            FROM ".TB_PREF."job_order_labours la  
                JOIN ".TB_PREF."list_values rc   
                    ON la.emp_rate_category = rc.id  
                        AND rc.list_code = 'EMPLOYEE_RATE_CATEGORY' 
            WHERE 1 = 1 ";
    
        if ($id > 0) {
            $sql .= " AND la.id = ".db_escape($id);
        }
        if ($jo_id > 0) {
            $sql .= " AND la.jo_id = ".db_escape($jo_id);
        }
        if (strlen($employee_code) > 0) {
            $sql .= " AND la.employee_code = ".db_escape($employee_code);
        }
        if ($from_date_ != '' && $to_date_ != '') {
            $sql .= " AND ((la.to_date >= ".db_escape(date2sql($from_date_))." AND la.from_date <= ".db_escape(date2sql($to_date_)).") 
                        OR (la.to_date >= ".db_escape(date2sql($to_date_))." AND la.from_date <= ".db_escape(date2sql($from_date_))."))";
        }
        if ($emp_rate_category > 0) {
            $sql .= " AND la.emp_rate_category = ".db_escape($emp_rate_category);
        }
        
        $sql .= " ORDER BY la.id DESC";
        
	$result = db_query($sql, "get_job_order_labour_details_by_filters");
            if ($fetch === false) {
                return $result;
            } else {
                return db_fetch($result);
            }
}

?>