<?php

function add_job_activity($jo_id, $row_state, $activity_type, $act_date, $description, $editable=1, $next_job_owner_id='', $type=-1, $trans_no=0) {
    $fun_args = func_get_args();
    $args = (object)array_combine(array('jo_id', 'row_state', 'activity_type', 'act_date', 'description', 'editable', 'next_job_owner_id', 'type', 'trans_no'), $fun_args);

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

        $sql = "INSERT INTO ".TB_PREF."job_activities   
                      ( jo_id, activity_type, act_date, 
                        next_job_owner_id, type, trans_no, 
                        description, editable, created_by, created_on )         
                VALUES( ".db_escape($jo_id).", ".db_escape($activity_type).", ".db_escape(date2sql($act_date)).", 
                        ".db_escape($next_job_owner_id).", ".db_escape($type).", ".db_escape($trans_no).",
                        ".db_escape($description).", ".db_escape($editable).", ".db_escape($created_by).", ".db_escape($created_on).")";

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

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

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

            $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_activity($jo_id, $id, $row_state, $activity_type, $act_date, $description) {
    $fun_args = func_get_args();
    $args = (object)array_combine(array('jo_id', 'id', 'row_state', 'activity_type', 'act_date', 'description'), $fun_args);

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

        $sql = "UPDATE ".TB_PREF."job_activities  
                SET description = ".db_escape($description).",
                    activity_type = ".db_escape($activity_type).",
                    act_date = ".db_escape(date2sql($act_date)).",
                    updated_by = ".db_escape($updated_by).",
                    updated_on = ".db_escape($updated_on)." 
                WHERE id = ".db_escape($id);

            db_query($sql, "update_job_activity");
            $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_activity_details($jo_id, $check_attachments=false) {
    $attach_sql = ' 0 attach_id';
    
    if ($check_attachments === true) {
        $attach_sql = ' att.id attach_id ';
    }
    
        $sql = "SELECT jal.*, tp.value_name activity_type_name, su.real_name, up.real_name updated_user, $attach_sql  
                FROM ".TB_PREF."job_activities jal 
                    JOIN ".TB_PREF."list_values tp 
                        ON jal.activity_type = tp.id 
                    LEFT JOIN ".TB_PREF."users su  
                        ON jal.created_by = su.id 
                    LEFT JOIN ".TB_PREF."users up   
                        ON jal.updated_by = up.id";

            if ($check_attachments === true) {
                $sql .= " LEFT JOIN ".TB_PREF."attachments att    
                            ON jal.id = att.trans_no 
                                AND att.type_no = ".db_escape(ST_JOB_ACTIVITY);
            }

            $sql .= " WHERE jal.jo_id = ".db_escape($jo_id);

            if ($check_attachments === true) {
                $sql .= " GROUP BY jal.id";
            }

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

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

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

function check_job_activity_is_last_entered_record($jo_id, $id) {
    $sql = "SELECT *
            FROM ".TB_PREF."job_activities  
            WHERE jo_id = ".db_escape($jo_id)." 
                AND id > ".db_escape($id)." 
            ORDER BY id DESC LIMIT 1";

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

function get_job_activity_record_by_jo_id_and_activity_type($jo_id, $activity_type, $desc=true) {
    $sql = "SELECT *
            FROM ".TB_PREF."job_activities  
            WHERE jo_id = ".db_escape($jo_id)." 
                AND activity_type = ".db_escape($activity_type);
    
        if ($desc === true) {
            $sql .= " ORDER BY id DESC LIMIT 1";
        } else {
            $sql .= " ORDER BY id ASC LIMIT 1";
        }

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

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

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

function get_job_activities_details_by_filters($id=0, $jo_id=0, $activity_type=0, $from_date_=null, $to_date_=null, $fetch=false) {
    $sql = "SELECT ja.*, at.value_name activity_type_name, su.real_name, up.real_name updated_user 
            FROM ".TB_PREF."job_activities ja   
                JOIN ".TB_PREF."list_values at    
                    ON ja.activity_type = at.id  
                        AND at.list_code = 'JOB_ACTIVITY_TYPES' 
                LEFT JOIN ".TB_PREF."users su  
                    ON ja.created_by = su.id 
                LEFT JOIN ".TB_PREF."users up   
                    ON ja.updated_by = up.id 
            WHERE 1 = 1 ";
    
        if ($id > 0) {
            $sql .= " AND ja.id = ".db_escape($id);
        }
        if ($jo_id > 0) {
            $sql .= " AND ja.jo_id = ".db_escape($jo_id);
        }
        if ($activity_type > 0) {
            $sql .= " AND ja.activity_type = ".db_escape($activity_type);
        }
        if ($from_date_ !== null && $to_date_ !== null) {
            $sql .= " AND ja.act_date >= ".db_escape(date2sql($from_date_))." AND ja.act_date <= ".db_escape(date2sql($to_date_));
        }
        
        $sql .= " ORDER BY ja.id DESC";
        
	$result = db_query($sql, "get_job_activities_details_by_filters");
            if ($fetch === false) {
                return $result;
            } else {
                return db_fetch($result);
            }
}

function check_change_job_card_selected_section_belong_to_correct_department($department_id, $section_id) {
    $dep_row = get_listvalue_by_id($department_id);
        $section_list_code = "VMS_".$dep_row['value_code'];
        
    $sec_row = get_list_value($section_id);
        if ($sec_row['list_code'] != $section_list_code) {
            return array('correct_section' => false, 'message' => "Selected section is not belong to the selectd department.");
        }
        
        return array('correct_section' => true, 'message' => "");
}

// one activity row may has more than one attachment, however this query gets only one attachment id
function get_job_activities_details_with_attachment_by_filters($id=0, $jo_id=0, $activity_type=0, $from_date_=null, $to_date_=null, $fetch=false) {
    $sql = "SELECT ja.*, at.value_name activity_type_name, su.real_name, up.real_name updated_user, att.id attach_id  
            FROM ".TB_PREF."job_activities ja   
                JOIN ".TB_PREF."list_values at    
                    ON ja.activity_type = at.id  
                        AND at.list_code = 'JOB_ACTIVITY_TYPES' 
                LEFT JOIN ".TB_PREF."users su  
                    ON ja.created_by = su.id 
                LEFT JOIN ".TB_PREF."users up   
                    ON ja.updated_by = up.id 
                LEFT JOIN ".TB_PREF."attachments att    
                    ON ja.id = att.trans_no 
                        AND att.type_no = ".db_escape(ST_JOB_ACTIVITY)." 
            WHERE 1 = 1 ";
    
        if ($id > 0) {
            $sql .= " AND ja.id = ".db_escape($id);
        }
        if ($jo_id > 0) {
            $sql .= " AND ja.jo_id = ".db_escape($jo_id);
        }
        if ($activity_type > 0) {
            $sql .= " AND ja.activity_type = ".db_escape($activity_type);
        }
        if ($from_date_ !== null && $to_date_ !== null) {
            $sql .= " AND ja.act_date >= ".db_escape(date2sql($from_date_))." AND ja.act_date <= ".db_escape(date2sql($to_date_));
        }
        
        $sql .= " GROUP BY ja.id ORDER BY ja.id DESC";
        
	$result = db_query($sql, "get_job_activities_details_with_attachment_by_filters");
            if ($fetch === false) {
                return $result;
            } else {
                return db_fetch($result);
            }
}

?>