<?php

function add_job_order(&$jo_id, $jo_ref, $date_, $vehicle_id, $req_department_id, $department_id, $section_id, $engineer_code, $tech_officer_code, $customer_name='', 
                       $customer_address='', $description='', $parent_jo_id=0, $source_type=-1, $source_trans_no=0, $job_owner_id='', $approval_status=0, $service=0) {
    global $Refs;

    $fun_args = func_get_args();
    $args = (object)array_combine(array('jo_id', 'jo_ref', 'date', 'vehicle_id', 'req_department_id', 'department_id', 'section_id', 'engineer_code', 'tech_officer_code', 
                                        'customer_name', 'customer_address', 'description', 'parent_jo_id', 'source_type', 'source_trans_no', 'job_owner_id', 
                                        'approval_status', 'service'), $fun_args);

//	$args->joid = 0;
//	hook_db_prewrite($args, ST_JOB_ORDER);

	$date = date2sql($date_);
        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date('Y-m-d H:i:s');
        
        $sql = "INSERT INTO ".TB_PREF."job_orders  
                      ( parent_jo_id, jo_ref, date_, vehicle_id, 
                        req_department_id, department_id, section_id, engineer_code, 
                        tech_officer_code, customer_name, customer_address, description, 
                        source_type, source_trans_no, next_job_owner_id, approval_status, 
                        service, created_by, created_on )         
                VALUES( ".db_escape($parent_jo_id).", ".db_escape($jo_ref).", ".db_escape($date).", ".db_escape($vehicle_id).", 
                        ".db_escape($req_department_id).", ".db_escape($department_id).", ".db_escape($section_id).", ".db_escape($engineer_code).", 
                        ".db_escape($tech_officer_code).", ".db_escape($customer_name).", ".db_escape($customer_address).", ".db_escape($description).", 
                        ".db_escape($source_type).", ".db_escape($source_trans_no).", ".db_escape($job_owner_id).", ".db_escape($approval_status).",  
                        ".db_escape($service).", ".db_escape($created_by).", ".db_escape($created_on).")";
        
            db_query($sql, "add_job_order");
            $jo_id = db_insert_id();
        
            if (! ($jo_id > 0)) {
                cancel_transaction();

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

	$Refs->save(ST_JOB_ORDER, $jo_id, $jo_ref);
        
//	add_audit_trail(ST_JOB_ORDER, $joid, $date_);
//
//	$args->joid = $joid;
//	hook_db_postwrite($args, ST_JOB_ORDER);
//
//	if ($type != WO_ADVANCED)
//	{
//	 	$stockitem = get_item($stock_id);
//		release_job_order($joid, $date_, '');
//		if ($costs != 0)
//			add_jo_costs_journal($joid, $costs, WO_OVERHEAD, $cr_acc, $date_, $stockitem["dimension_id"], $stockitem["dimension2_id"]);
//
//		if ($labour != 0)
//			add_jo_costs_journal($joid, $labour, WO_LABOUR, $cr_lab_acc, $date_, $stockitem["dimension_id"], $stockitem["dimension2_id"]);
//
//		if ($type == WO_UNASSEMBLY)
//			$units_reqd = -$units_reqd;
//
//		$ref = $Refs->get_next(ST_MANURECEIVE, null, $date_);
//		job_order_produce($joid, $ref, $units_reqd, $date_, '', true);
//	}

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

function update_job_order($jo_id, $jo_ref, $date_, $vehicle_id, $req_department_id, $department_id, $section_id, $engineer_code, $tech_officer_code, $customer_name, 
                          $customer_address, $instruction_id, $description, $approval_status, $service, &$row_state) {
    
    $fun_args = func_get_args();
    $args = (object)array_combine(array('jo_id', 'jo_ref', 'date', 'vehicle_id', 'req_department_id', 'department_id', 'section_id', 'engineer_code', 'tech_officer_code', 
                                        'customer_name', 'customer_address', 'instruction_id', 'description', 'approval_status', 'service', 'row_state'), $fun_args);
//	hook_db_prewrite($args, ST_JOB_ORDER);

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

        $sql = "UPDATE ".TB_PREF."job_orders 
                SET jo_ref = ".db_escape($jo_ref).",
                    date_ = ".db_escape($date).", 
                    vehicle_id = ".db_escape($vehicle_id).",
                    req_department_id = ".db_escape($req_department_id).",
                    department_id = ".db_escape($department_id).",
                    section_id = ".db_escape($section_id).",
                    engineer_code = ".db_escape($engineer_code).",
                    tech_officer_code = ".db_escape($tech_officer_code).",
                    customer_name = ".db_escape($customer_name).",
                    customer_address = ".db_escape($customer_address).",
                    instruction_id = ".db_escape($instruction_id).",
                    description = ".db_escape($description).",
                    approval_status = ".db_escape($approval_status).",
                    service = ".db_escape($service).",
                    row_state = ".db_escape($new_row_state).",
                    updated_by = ".db_escape($updated_by).",
                    updated_on = ".db_escape($updated_on)." 
                WHERE id = ".db_escape($jo_id)." 
                    AND row_state = ".db_escape($row_state);

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

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

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

//	update_comments(ST_JOB_ORDER, $joid, null, $memo_);
//	add_audit_trail(ST_JOB_ORDER, $joid, $date_, _("Updated."));
//
//	hook_db_postwrite($args, ST_JOB_ORDER);
    
        $row_state = $new_row_state;
        
        return array('success' => true, 'message' => "");
}

function delete_job_order($joid, $stock_id, $qty, $date)
{
	begin_transaction();
	hook_db_prevoid(ST_JOB_ORDER, $joid);

	// delete the job order requirements
	delete_jo_requirements($joid);

	// delete the actual job order
	$sql = "DELETE FROM ".TB_PREF."job_orders WHERE id=".db_escape($joid);
	db_query($sql,"The job order could not be deleted");

	delete_comments(ST_JOB_ORDER, $joid);
	add_audit_trail(ST_JOB_ORDER, $joid, $date, _("Canceled."));

	commit_transaction();
}

//--------------------------------------------------------------------------------------

function get_job_order($joid, $allow_null=false)
{
    $sql = "SELECT jo.*,st.description As StockItemName,l.location_name,
    		l.delivery_address,l.email, l.contact, st.inventory_account, st.wip_account
			FROM ".TB_PREF."job_orders jo
			LEFT JOIN ".TB_PREF."voided v ON v.id=jo.id and v.type=".ST_JOB_ORDER.","
			.TB_PREF."stock_master st, "
			.TB_PREF."locations l
		WHERE ISNULL(v.id)
		AND st.stock_id=jo.stock_id
		AND	l.loc_code=jo.loc_code
		AND jo.id=".db_escape($joid)."
		GROUP BY jo.id";

	$result = db_query($sql, "The job order could not be retrieved");

	if (!$allow_null && db_num_rows($result) == 0)
	{
		display_db_error("Could not find job order ".(int)$joid, $sql);
		return false;
	}

	return db_fetch($result);
}

function get_job_order_record($jo_id) {
    $sql = "SELECT *
            FROM ".TB_PREF."job_orders 
            WHERE id = ".db_escape($jo_id);

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

//--------------------------------------------------------------------------------------

function job_order_has_productions($joid)
{
	$sql = "SELECT COUNT(*) FROM ".TB_PREF."jo_manufacture WHERE joborder_id=".db_escape($joid);
	$result = db_query($sql, "query job order for productions");

	$myrow = db_fetch_row($result);
	return ($myrow[0] > 0);
}


//--------------------------------------------------------------------------------------

function job_order_has_issues($joid)
{
	$sql = "SELECT COUNT(*) FROM ".TB_PREF."jo_issues WHERE joborder_id=".db_escape($joid);
	$result = db_query($sql, "query job order for issues");

	$myrow = db_fetch_row($result);
	return ($myrow[0] > 0);
}

//--------------------------------------------------------------------------------------

function job_order_has_payments($joid)
{
	$result = get_gl_jo_cost_trans($joid);

    return (db_num_rows($result) != 0);
}

//--------------------------------------------------------------------------------------

function release_job_order($joid, $releaseDate, $memo_)
{
	begin_transaction();

	$myrow = get_job_order($joid);
	$stock_id = $myrow["stock_id"];

	$date = date2sql($releaseDate);

	$sql = "UPDATE ".TB_PREF."job_orders SET released_date='$date',
		released=1 WHERE id = ".db_escape($joid);
	db_query($sql, "could not release job order");

	// create jo_requirements records according to current BOM
	create_jo_requirements($joid, $stock_id);

	add_comments(ST_JOB_ORDER, $joid, $releaseDate, $memo_);
	add_audit_trail(ST_JOB_ORDER, $joid, sql2date($myrow['date_']), _("Released."));

	commit_transaction();
}

//--------------------------------------------------------------------------------------

function close_job_order($joid)
{
	$sql = "UPDATE ".TB_PREF."job_orders SET closed=1 WHERE id = ".db_escape($joid);
	db_query($sql, "could not close job order");
}

//--------------------------------------------------------------------------------------

function job_order_is_closed($joid)
{
	$sql = "SELECT closed FROM ".TB_PREF."job_orders WHERE id = ".db_escape($joid);
	$result = db_query($sql, "could not query job order");
	$row = db_fetch_row($result);
	return ($row[0] > 0);
}

//--------------------------------------------------------------------------------------
/*
	Update finished items quantity in job order, and close order either if all the order is produced,
	or on user demand. Returns calculated unit cost on close, or null otherwise.
*/
function job_order_update_finished_quantity($joid, $quantity, $force_close=0)
{

	$sql = "UPDATE ".TB_PREF."job_orders SET units_issued = units_issued + ".db_escape($quantity)."
		WHERE id = ".db_escape($joid). " AND !closed";

	db_query($sql, "The job order issued quantity couldn't be updated");

	$sql = "UPDATE ".TB_PREF."job_orders SET closed = ((units_issued >= units_reqd) OR ".db_escape($force_close).")
		WHERE id = ".db_escape($joid);

	db_query($sql, "The job order couldn't be closed");

	return db_num_affected_rows();	// returns 1 if WO has been closed
}

function reopen_job_order($joid)
{
		// FIXME: update_material_cost
//		update_material_cost($job_order['stock_id'], -$job_order['units_issued'], ... );

		void_gl_trans(ST_JOB_ORDER, $joid);

		void_stock_move(ST_JOB_ORDER, $joid);

		$sql = "UPDATE ".TB_PREF."job_orders SET closed=0 WHERE id = "
			.db_escape($joid);

		db_query($sql, "The job order couldn't be reopened");
}

//--------------------------------------------------------------------------------------
/*
	Void all job order related operations
*/
function void_job_order($joid)
{
	begin_transaction();
	hook_db_prevoid(ST_JOB_ORDER, $joid);

	$job_order = get_job_order($joid);

	if ($job_order['closed'])
		reopen_job_order($joid);

	if ($job_order['units_issued'])
	{
		$prods = get_job_order_productions($joid);
		while ($prod = db_fetch($prods))
			void_job_order_produce($prod['id']);
	}

	// void addtional material issues
	$issues = get_job_order_issues($joid);
	while($issue = db_fetch($issues))
		void_job_order_issue($issue['issue_no']);

	// void all related stock moves
	void_stock_move(ST_JOB_ORDER, $joid);

	// void any related costing records
	void_jo_costing($joid);

	// clear the requirements units received
	void_jo_requirements($joid);

	// close joborder
	// FIXME: due to bug in 2.4 db scheme (conflicting jo_ref unique key) we have to fix jo_ref here:
	$sql = "UPDATE ".TB_PREF."job_orders SET closed=1,units_reqd=0,units_issued=0, jo_ref=CONCAT(jo_ref, ' ', '".uniqid()."') WHERE id = "
		.db_escape($joid);
	db_query($sql, "The job order couldn't be voided");

	commit_transaction();
}

function get_sql_for_job_orders($outstanding_only, $stock_id, $location = ALL_TEXT, $order_no='', $order_ref = '', $overdue = false)
{
	$sql = "SELECT
		joborder.id,
		joborder.jo_ref,
		joborder.type,
		location.location_name,
		item.description,
		joborder.units_reqd,
		joborder.units_issued,
		joborder.date_,
		joborder.required_by,
		joborder.released_date,
		joborder.closed,
		joborder.released,
		joborder.stock_id,
		unit.decimals
		FROM ".TB_PREF."job_orders as joborder
			LEFT JOIN ".TB_PREF."voided v ON v.id=joborder.id and v.type=".ST_JOB_ORDER.","
			.TB_PREF."stock_master as item,"
			.TB_PREF."item_units as unit,"
			.TB_PREF."locations as location
		WHERE ISNULL(v.id)
			AND joborder.stock_id=item.stock_id 
			AND joborder.loc_code=location.loc_code
			AND item.units=unit.abbr";

	if (check_value('OpenOnly') || $outstanding_only != 0)
	{
		$sql .= " AND joborder.closed=0";
	}

	if ($location != ALL_TEXT)
	{
		$sql .= " AND joborder.loc_code=".db_escape($location);
	}

	if ($order_no != '')
	{
		$sql .= " AND joborder.id LIKE ".db_escape('%'.$order_no.'%');
	}

	if ($order_ref != '')
	{
		$sql .= " AND joborder.jo_ref LIKE ".db_escape('%'.$order_ref.'%');
	}

	if ($stock_id != ALL_TEXT)
	{
		$sql .= " AND joborder.stock_id=".db_escape($stock_id);
	}

	if ($overdue)
	{
		$Today = date2sql(Today());

		$sql .= " AND joborder.required_by < '$Today' ";
	}
	$sql .= " ORDER BY joborder.id DESC";
	return $sql;
}

function get_sql_for_job_orders_inquiry($id, $parent_jo_id, $has_complain, $customer_name, $reference, $from_date_, $to_date_, $vehicle_id, $req_department_id, $department_id, 
                                        $section_id, $next_job_owner_id, $job_accepted, $job_completed, $approval_status, $sub_job, $engineer_code, $tech_officer_code, 
                                        $service) {
    
    $sql = "SELECT jo.id, jo.jo_ref, jo.date_, jo.vehicle_id, a.description vehicle_name, rdp.value_name req_department, dp.value_name department, sc.value_name section, 
                jo.engineer_code, jo.tech_officer_code, jo.next_job_owner_id, jo.service, jo.job_accepted, jo.job_completed, jo.approval_status, jo.parent_jo_id, 
                jo.source_trans_no, jo.customer_name   
            FROM ".TB_PREF."job_orders jo 
                LEFT JOIN ".TB_PREF."asset_master a 
                    ON jo.vehicle_id = a.asset_id 
                LEFT JOIN ".TB_PREF."list_values rdp  
                    ON jo.req_department_id = rdp.id  
                        AND rdp.list_code IN ('REQ_DEPARTMENTS', 'DEPARTMENTS') 
                LEFT JOIN ".TB_PREF."list_values dp  
                    ON jo.department_id = dp.id  
                        AND dp.list_code = 'DEPARTMENTS'
                LEFT JOIN ".TB_PREF."list_values sc   
                    ON jo.section_id = sc.id  
            WHERE 1 = 1";
    
        if ($id > 0) {
            $sql .= " AND jo.id = ".db_escape($id);
        }
    
        if ($parent_jo_id > 0) {
            $sql .= " AND jo.parent_jo_id = ".db_escape($parent_jo_id);
        }
    
        if (! ($id > 0 || $parent_jo_id > 0)) {
            
            if ($has_complain == 'Yes') {
                $sql .= " AND jo.source_trans_no > 0";
            } elseif ($has_complain == 'No') {
                $sql .= " AND jo.source_trans_no = 0";
            }
            
            if (strlen($customer_name) > 1) {
                $sql .= " AND jo.customer_name LIKE '%$customer_name%'";
            }
            
            if (strlen($reference) > 0) {
                $sql .= " AND jo.jo_ref = ".db_escape($reference);
            }
            
            $sql .= " AND jo.date_ >= ".db_escape(date2sql($from_date_))." AND jo.date_ <= ".db_escape(date2sql($to_date_));

            if ($vehicle_id > 0) {
                $sql .= " AND jo.vehicle_id = ".db_escape($vehicle_id);
            }
            
            if ($req_department_id > 0) {
                $sql .= " AND jo.req_department_id = ".db_escape($req_department_id);
            }
            
            if ($department_id > 0) {
                $sql .= " AND jo.department_id = ".db_escape($department_id);
            }
            
            if ($section_id > 0) {
                $sql .= " AND jo.section_id = ".db_escape($section_id);
            }
            
            if (strlen($next_job_owner_id) > 1) {
                $sql .= " AND jo.next_job_owner_id = ".db_escape($next_job_owner_id);
            }
            
            if (strlen($engineer_code) > 1) {
                $sql .= " AND jo.engineer_code = ".db_escape($engineer_code);
            }
            
            if (strlen($tech_officer_code) > 1) {
                $sql .= " AND jo.tech_officer_code = ".db_escape($tech_officer_code);
            }
            
            if ($service == 'Yes') {
                $sql .= " AND jo.service = 1";
            } elseif ($service == 'No') {
                $sql .= " AND jo.service = 0";
            }

            if ($job_accepted != '') {
                $sql .= " AND jo.job_accepted = ".db_escape($job_accepted);
            }

            if ($job_completed != '') {
                $sql .= " AND jo.job_completed = ".db_escape($job_completed);
            }

            if ($approval_status > -1) {
                $sql .= " AND jo.approval_status = ".db_escape($approval_status);
            }
            
            if ($sub_job == 'Yes') {
                $sql .= " AND jo.parent_jo_id > 0";
            } elseif ($sub_job == 'No') {
                $sql .= " AND jo.parent_jo_id = 0";
            }
        }
        
	$sql .= " ORDER BY jo.id DESC";
        
	return $sql;
}

function get_sql_for_my_pending_jobs_inquiry($from_date_, $to_date_, $current_user_id) {
    $sql = "SELECT jo.id, jo.jo_ref, jo.date_, jo.vehicle_id, a.description vehicle_name, rdp.value_name req_department, dp.value_name department, sc.value_name section, 
                jo.engineer_code, jo.tech_officer_code, jo.next_job_owner_id, jo.job_accepted, jo.job_completed, jo.released, jo.parent_jo_id, jo.source_trans_no, 
                jo.customer_name   
            FROM ".TB_PREF."job_orders jo 
                JOIN ".TB_PREF."users u 
                    ON jo.next_job_owner_id = u.employee_code  
                LEFT JOIN ".TB_PREF."asset_master a 
                    ON jo.vehicle_id = a.asset_id 
                LEFT JOIN ".TB_PREF."list_values rdp  
                    ON jo.req_department_id = rdp.id  
                        AND rdp.list_code IN ('REQ_DEPARTMENTS', 'DEPARTMENTS') 
                LEFT JOIN ".TB_PREF."list_values dp  
                    ON jo.department_id = dp.id  
                        AND dp.list_code = 'DEPARTMENTS'
                LEFT JOIN ".TB_PREF."list_values sc   
                    ON jo.section_id = sc.id  
            WHERE jo.date_ >= ".db_escape(date2sql($from_date_))." AND jo.date_ <= ".db_escape(date2sql($to_date_))." 
                AND u.id = ".db_escape($current_user_id);
        
	return $sql;
}

function update_job_order_row_state($jo_id, $row_state) {
    $sql = "UPDATE ".TB_PREF."job_orders   
            SET row_state = ".db_escape(db_new_row_state($row_state))." 
            WHERE id = ".db_escape($jo_id)." 
                AND row_state = ".db_escape($row_state);

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

function get_job_order_section_details($id) {
    $sql = "SELECT sc.*, lv.value_name section_name  
            FROM ".TB_PREF."job_order_sections sc 
                JOIN ".TB_PREF."list_values lv   
                    ON sc.section_id = lv.id 
                        AND lv.list_code = 'DEPARTMENTS' 
            WHERE sc.id = ".db_escape($id);

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

function create_jobs_array($id=0, $reference='', $from_date_=null, $to_date_=null, $vehicle_id=0, $department_id=0, $section_id=0) {
    $jobs_array = array();
    
    $result = get_job_order_details_by_filters($id, $reference, $from_date_, $to_date_, $vehicle_id, $department_id, $section_id);
        while ($row = db_fetch($result)) {
            $jo_id = $row['id'];
            $jobs_array[$jo_id] = "[$jo_id] - ".$row['vehicle_full_name'];
        }
        
        return $jobs_array;
}

function get_job_orders_by_filters($parent_jo_id=0, $jo_id=0, $reference='', $from_date_=null, $to_date_=null, $vehicle_id=0, $department_id=0, $section_id=0, 
                                   $engineer_code=0, $tech_officer_code=0, $next_job_owner_id=0, $job_accepted=null, $job_completed=null, $req_department_id=0, 
                                   $instruction_id=0, $not_sub_job=false, $service=null, $group_by_str=null, $order_by_str=null, $fetch=false) {
    $sql = "SELECT * 
            FROM ".TB_PREF."job_orders 
            WHERE 1 = 1";
    
        if ($parent_jo_id > 0) {
            $sql .= " AND parent_jo_id = ".db_escape($parent_jo_id);
        }
        if ($jo_id > 0) {
            $sql .= " AND id = ".db_escape($jo_id);
        }
        if (strlen($reference) > 0) {
            $sql .= " AND jo_ref = ".db_escape($reference);
        }
        if ($from_date_ !== null) {
            $sql .= " AND date_ >= ".db_escape(date2sql($from_date_));
        }
        if ($to_date_ !== null) {
            $sql .= " AND date_ <= ".db_escape(date2sql($to_date_));
        }
        if ($vehicle_id > 0) {
            $sql .= " AND vehicle_id = ".db_escape($vehicle_id);
        }
        if ($department_id > 0) {
            $sql .= " AND department_id = ".db_escape($department_id);
        }
        if ($section_id > 0) {
            $sql .= " AND section_id = ".db_escape($section_id);
        }
        if ($engineer_code != 0) {
            $sql .= " AND engineer_code = ".db_escape($engineer_code);
        }
        if ($tech_officer_code != 0) {
            $sql .= " AND tech_officer_code = ".db_escape($tech_officer_code);
        }
        if ($next_job_owner_id != 0) {
            $sql .= " AND next_job_owner_id = ".db_escape($next_job_owner_id);
        }
        if ($job_accepted !== null) {
            $sql .= " AND job_accepted = ".db_escape($job_accepted);
        }
        if ($job_completed !== null) {
            $sql .= " AND job_completed = ".db_escape($job_completed);
        }
        if ($req_department_id > 0) {
            $sql .= " AND req_department_id = ".db_escape($req_department_id);
        }
        if ($instruction_id > 0) {
            $sql .= " AND instruction_id = ".db_escape($instruction_id);
        }
        if ($not_sub_job === true) {
            $sql .= " AND parent_jo_id = 0";
        }
        if ($service !== null) {
            $sql .= " AND service = ".db_escape($service);
        }
        if ($group_by_str !== null) {
            $sql .= " GROUP BY $group_by_str ";
        }
        if ($order_by_str !== null) {
            $sql .= " ORDER BY $order_by_str ";
        }
        
	$result = db_query($sql, "get_job_orders_by_filters");
            if ($fetch === false) {
                return $result;
            } else {
                return db_fetch($result);
            }
}

function get_job_order_details_by_filters($id=0, $reference='', $from_date_=null, $to_date_=null, $vehicle_id=0, $department_id=0, $section_id=0, $engineer_code=0, 
                                          $tech_officer_code=0, $next_job_owner_id=0, $job_accepted='', $job_completed='', $parent_jo_id=0, $req_department_id=0, 
                                          $instruction_id=0, $approval_status=-1, $source_type=-1, $source_trans_no=0, $fetch=false, $order_by='') {
    
    $sql = "SELECT jo.*, a.description vehicle_name, rdp.value_name req_department_name, dp.value_name department_name, sc.value_name section_name, 
                CONCAT(a.asset_code, ' - ', a.description) vehicle_full_name, su.real_name issuing_officer, su.employee_code issuing_officer_emp_code, 
                itc.value_name instruction_name, a.last_service_date, a.asset_code   
            FROM ".TB_PREF."job_orders jo 
                LEFT JOIN ".TB_PREF."asset_master a 
                    ON jo.vehicle_id = a.asset_id 
                LEFT JOIN ".TB_PREF."list_values rdp  
                    ON jo.req_department_id = rdp.id  
                        AND rdp.list_code IN ('REQ_DEPARTMENTS', 'DEPARTMENTS') 
                LEFT JOIN ".TB_PREF."list_values dp  
                    ON jo.department_id = dp.id  
                        AND dp.list_code = 'DEPARTMENTS'
                LEFT JOIN ".TB_PREF."list_values sc   
                    ON jo.section_id = sc.id  
                LEFT JOIN ".TB_PREF."list_values itc   
                    ON jo.instruction_id = itc.id  
                        AND itc.list_code = 'JOB_INSTRUCTIONS'    
                LEFT JOIN ".TB_PREF."users su    
                    ON jo.created_by = su.id  
            WHERE 1 = 1";
    
        if ($id > 0) {
            $sql .= " AND jo.id = ".db_escape($id);
        }
        if (strlen($reference) > 0) {
            $sql .= " AND jo.jo_ref = ".db_escape($reference);
        }
        if ($from_date_ !== null) {
            $sql .= " AND jo.date_ >= ".db_escape(date2sql($from_date_));
        }
        if ($to_date_ !== null) {
            $sql .= " AND jo.date_ <= ".db_escape(date2sql($to_date_));
        }
        if ($vehicle_id > 0) {
            $sql .= " AND jo.vehicle_id = ".db_escape($vehicle_id);
        }
        if ($department_id > 0) {
            $sql .= " AND jo.department_id = ".db_escape($department_id);
        }
        if ($section_id > 0) {
            $sql .= " AND jo.section_id = ".db_escape($section_id);
        }
        if ($engineer_code != 0) {
            $sql .= " AND jo.engineer_code = ".db_escape($engineer_code);
        }
        if ($tech_officer_code != 0) {
            $sql .= " AND jo.tech_officer_code = ".db_escape($tech_officer_code);
        }
        if ($next_job_owner_id != 0) {
            $sql .= " AND jo.next_job_owner_id = ".db_escape($next_job_owner_id);
        }
        if (strlen($job_accepted) > 0) {
            $sql .= " AND jo.job_accepted = ".db_escape($job_accepted);
        }
        if (strlen($job_completed) > 0) {
            $sql .= " AND jo.job_completed = ".db_escape($job_completed);
        }
        if ($parent_jo_id > 0) {
            $sql .= " AND jo.parent_jo_id = ".db_escape($parent_jo_id);
        }
        if ($req_department_id > 0) {
            $sql .= " AND jo.req_department_id = ".db_escape($req_department_id);
        }
        if ($instruction_id > 0) {
            $sql .= " AND jo.instruction_id = ".db_escape($instruction_id);
        }
        if ($approval_status > -1) {
            $sql .= " AND jo.approval_status = ".db_escape($approval_status);
        }
        if ($source_type > -1) {
            $sql .= " AND jo.source_type = ".db_escape($source_type);
        }
        if ($source_trans_no > 0) {
            $sql .= " AND jo.source_trans_no = ".db_escape($source_trans_no);
        }
        
        if (strlen($order_by) > 0) {
            $sql .= " ORDER BY $order_by";
        } else {
            $sql .= " ORDER BY jo.id";
        }
        
	$result = db_query($sql, "get_job_order_details_by_filters");
            if ($fetch === false) {
                return $result;
            } else {
                return db_fetch($result);
            }
}

function update_job_order_next_approval_state($jo_id, $new_next_job_owner_id, $row_state) {
    $sql = "UPDATE ".TB_PREF."job_orders   
            SET row_state = ".db_escape(db_new_row_state($row_state)).",  
                next_job_owner_id = ".db_escape($new_next_job_owner_id)." 
            WHERE id = ".db_escape($jo_id)." 
                AND row_state = ".db_escape($row_state);

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

function update_job_order_job_acceptance_state($jo_id, $job_accepted, $row_state) {
    $sql = "UPDATE ".TB_PREF."job_orders   
            SET row_state = ".db_escape(db_new_row_state($row_state)).",  
                job_accepted = ".db_escape($job_accepted)." 
            WHERE id = ".db_escape($jo_id)." 
                AND row_state = ".db_escape($row_state);

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

function update_job_order_job_complete_state($jo_id, $job_completed, $row_state) {
    $sql = "UPDATE ".TB_PREF."job_orders   
            SET row_state = ".db_escape(db_new_row_state($row_state)).",  
                job_completed = ".db_escape($job_completed)." 
            WHERE id = ".db_escape($jo_id)." 
                AND row_state = ".db_escape($row_state);

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

function update_sub_job_orders_request_department($jo_id, $req_department_id, $row_state) {
    $sql = "UPDATE ".TB_PREF."job_orders   
            SET row_state = ".db_escape(db_new_row_state($row_state)).",  
                req_department_id = ".db_escape($req_department_id)." 
            WHERE id = ".db_escape($jo_id)." 
                AND row_state = ".db_escape($row_state);

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

function update_all_sub_job_orders_request_department($parent_jo_id, $req_department_id) {
    $result = get_job_orders_by_filters($parent_jo_id);
        while ($row = db_fetch($result)) {
            $id = $row['id'];
            
            $af_row = update_sub_job_orders_request_department($id, $req_department_id, $row['row_state']);
                if ($af_row != 1) {
                    return array('success' => false, 'meaasge' => "Could not update sub job card #$id request department.");
                }
        }

        return array('success' => true, 'meaasge' => "Sub job cards request department have been updated.");
}

function get_job_orders_by_source($source_type, $source_trans_no, $fetch=false) {
    $sql = "SELECT * 
            FROM ".TB_PREF."job_orders 
            WHERE source_type = ".db_escape($source_type)." 
                AND source_trans_no = ".db_escape($source_trans_no);

        $result = db_query($sql, "get_job_orders_by_source");
            if ($fetch === false) {
                return $result;
            } else {
                return db_fetch($result);
            }
}

function update_job_owner($jo_id, $next_job_owner_id, $department_id, $section_id, $row_state) {
    $sql = "UPDATE ".TB_PREF."job_orders   
            SET row_state = ".db_escape(db_new_row_state($row_state)).",
                next_job_owner_id = ".db_escape($next_job_owner_id).", 
                department_id = ".db_escape($department_id).", 
                section_id = ".db_escape($section_id)." 
            WHERE id = ".db_escape($jo_id)." 
                AND row_state = ".db_escape($row_state);

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

function create_job_order_department_section_list_codes_array($department_id=0) {
    $section_list_codes_array = array();
    
        if ($department_id > 0) {
            $row = get_listvalue_by_id($department_id);
                $department_code = $row['value_code'];
                $section_list_codes_array[$department_code] = "'VMS_$department_code'";
                
                return $section_list_codes_array;
        }
        
        $result = get_all_list_values('DEPARTMENTS');
            while ($dep_row = db_fetch($result)) {
                $department_code = $dep_row['value_code'];
                $section_list_codes_array[$department_code] = "'VMS_$department_code'";
            }
    
            return $section_list_codes_array;
}

function get_job_order_all_sections($section_list_codes_array) {
    $sql = "SELECT * 
            FROM ".TB_PREF."list_values    
            WHERE list_code IN (".implode(',', $section_list_codes_array).")";

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

// if want can add more filters
function get_job_orders_count_by_filters($from_date=null, $to_date=null, $department_id=0, $section_id=0, $approval_status=-1, $req_department_id=0, $vehicle_id=0, 
                                         $not_ap_status_array=array()) {
    $sql = "SELECT COUNT(id) total   
            FROM ".TB_PREF."job_orders     
            WHERE 1 = 1";
    
        if ($from_date !== null && $to_date !== null) {
            $sql .= " AND date_ >= ".db_escape($from_date)." AND date_ <= ".db_escape($to_date);
        }
        if ($department_id > 0) {
            $sql .= " AND department_id = ".db_escape($department_id);
        }
        if ($section_id > 0) {
            $sql .= " AND section_id = ".db_escape($section_id);
        }
        if ($approval_status > -1) {
            $sql .= " AND approval_status = ".db_escape($approval_status);
        }
        if ($vehicle_id > 0) {
            $sql .= " AND vehicle_id = ".db_escape($vehicle_id);
        }
        if ($req_department_id > 0) {
            $sql .= " AND req_department_id = ".db_escape($req_department_id);
        }
        if (count($not_ap_status_array) > 0) {
            $sql .= " AND approval_status NOT IN (". implode(',', $not_ap_status_array).")";
        }

        $result = db_query($sql, "get_job_orders_count_by_filters");
        return db_result_value($result, 'total');
}


function get_job_order_cost($jo_id) {
    $sql_1 = "SELECT SUM(d.standard_cost * d.qty) total_cost
                FROM ".TB_PREF."job_material_issue_details d  
                    JOIN ".TB_PREF."job_material_issues h  
                        ON d.type = h.type   
                            AND d.trans_no = h.trans_no    
                WHERE h.jo_id = ".db_escape($jo_id) ." 
                    AND h.type IN (".implode(',', array(ST_JOB_MATERIAL_ISSUE)).")"; 
    
    $sql_2 = "SELECT SUM(d.standard_cost * d.qty * -1) total_cost
                FROM ".TB_PREF."job_material_issue_details d  
                    JOIN ".TB_PREF."job_material_issues h  
                        ON d.type = h.type   
                            AND d.trans_no = h.trans_no    
                WHERE h.jo_id = ".db_escape($jo_id) ." 
                    AND h.type IN (".implode(',', array(ST_JOB_MATERIAL_RETURN)).")"; 
    
    $sql_3 = "SELECT SUM(rate * num_days_worked) total_cost
                FROM ".TB_PREF."job_order_labours   
                WHERE jo_id = ".db_escape($jo_id);
    
        $sql = "SELECT SUM(tt.total_cost) job_cost
                FROM ($sql_1 UNION $sql_2 UNION $sql_3) tt ";
    
            $result = db_query($sql, "get_job_order_cost");
            return db_result_value($result, 'job_cost');
}

function update_asset_last_service_date($asset_id, $last_service_date_) {
    $sql = "UPDATE ".TB_PREF."asset_master    
            SET last_service_date = ".db_escape(date2sql($last_service_date_))."   
            WHERE asset_id = ".db_escape($asset_id);;

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

function get_sql_for_maintenance_schedule_inquiry($from_schedule_date_, $to_schedule_date_) {
    $sql = "SELECT asset_id, asset_code, last_service_date, next_service_days, DATE_ADD(last_service_date, INTERVAL next_service_days DAY) next_service_date  
            FROM ".TB_PREF."asset_master  
            WHERE last_service_date > ".db_escape(INFINITE_START_DATE)." 
                AND DATE_ADD(last_service_date, INTERVAL next_service_days DAY) >= ".db_escape(date2sql($from_schedule_date_))." 
                AND DATE_ADD(last_service_date, INTERVAL next_service_days DAY) <= ".db_escape(date2sql($to_schedule_date_));
    
	$sql .= " ORDER BY last_service_date";
        
	return $sql;
}

function get_job_order_vehicle_tool_last_service($vehicle_id=0, $jo_id=0, $not_jo_id=0, $group_by_str=null, $order_by_str=null, $limit=0, $fetch=false) {
    $sql = "SELECT * 
            FROM ".TB_PREF."job_orders 
            WHERE service = 1";
    
        if ($vehicle_id > 0) {
            $sql .= " AND vehicle_id = ".db_escape($vehicle_id);
        }
        if ($jo_id > 0) {
            $sql .= " AND id = ".db_escape($jo_id);
        }
        if ($not_jo_id > 0) {
            $sql .= " AND id <> ".db_escape($not_jo_id);
        }
        if ($group_by_str !== null) {
            $sql .= " GROUP BY $group_by_str ";
        }
        if ($order_by_str !== null) {
            $sql .= " ORDER BY $order_by_str ";
        }
        if ($limit > 0) {
            $sql .= " LIMIT $limit ";
        }
        
	$result = db_query($sql, "get_job_order_vehicle_tool_last_service");
            if ($fetch === false) {
                return $result;
            } else {
                return db_fetch($result);
            }
}

function get_job_order_project_id($jo_id) {
    $sql = "SELECT pm.project_id, pm.project_code  
            FROM ".TB_PREF."job_orders jo 
                JOIN ".TB_PREF."list_values lv   
                    ON jo.department_id = lv.id 
                JOIN ".TB_PREF."projects_master pm    
                    ON CONCAT('WMS_', lv.value_code) = pm.project_code  
            WHERE jo.id = ".db_escape($jo_id);

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

function get_job_order_details($id) {
    $sql = "SELECT jo.*, a.description vehicle_name, rdp.value_name req_department_name, dp.value_name department_name, sc.value_name section_name, 
                CONCAT(a.asset_code, ' - ', a.description) vehicle_full_name, su.real_name issuing_officer, su.employee_code issuing_officer_emp_code, 
                itc.value_name instruction_name, a.last_service_date, a.asset_code  
            FROM ".TB_PREF."job_orders jo 
                LEFT JOIN ".TB_PREF."asset_master a 
                    ON jo.vehicle_id = a.asset_id 
                LEFT JOIN ".TB_PREF."list_values rdp  
                    ON jo.req_department_id = rdp.id  
                        AND rdp.list_code IN ('REQ_DEPARTMENTS', 'DEPARTMENTS') 
                LEFT JOIN ".TB_PREF."list_values dp  
                    ON jo.department_id = dp.id  
                        AND dp.list_code = 'DEPARTMENTS'
                LEFT JOIN ".TB_PREF."list_values sc   
                    ON jo.section_id = sc.id  
                LEFT JOIN ".TB_PREF."list_values itc   
                    ON jo.instruction_id = itc.id  
                        AND itc.list_code = 'JOB_INSTRUCTIONS'    
                LEFT JOIN ".TB_PREF."users su    
                    ON jo.created_by = su.id  
            WHERE jo.id = ".db_escape($id);
    
        $result = db_query($sql, "get_job_order_details");
        return db_fetch($result);
}

function check_job_order_has_transactions($jo_id) {
    global $systypes_array;
    
    $sql = "SELECT *
            FROM ".TB_PREF."job_material_requisitions  
            WHERE jo_id = ".db_escape($jo_id); 
    
        $result = db_query($sql, "check_job_order_has_transactions");
        $row = db_fetch($result);
    
        if (isset($row['id']) && $row['id'] > 0) {
            $tp_name = $systypes_array[$row['type']];
            $trans_no = $row['trans_no'];
            
            return array('has_trans' => true, 'message' => "Job Card #$jo_id has $tp_name trans #$trans_no.");
        }
        
    $sql_2 = "SELECT *
            FROM ".TB_PREF."job_material_issues   
            WHERE jo_id = ".db_escape($jo_id); 
    
        $result_2 = db_query($sql_2, "check_job_order_has_transactions");
        $row_2 = db_fetch($result_2);
    
        if (isset($row_2['id']) && $row_2['id'] > 0) {
            $tp_name = $systypes_array[$row_2['type']];
            $trans_no = $row_2['trans_no'];
            
            return array('has_trans' => true, 'message' => "Job Card #$jo_id has $tp_name trans #$trans_no.");
        }
        
    $sql_3 = "SELECT *
            FROM ".TB_PREF."direct_purchase_requisitions    
            WHERE jo_id = ".db_escape($jo_id); 
    
        $result_3 = db_query($sql_3, "check_job_order_has_transactions");
        $row_3 = db_fetch($result_3);
    
        if (isset($row_3['id']) && $row_3['id'] > 0) {
            $tp_name = $systypes_array[$row_3['type']];
            $trans_no = $row_3['trans_no'];
            
            return array('has_trans' => true, 'message' => "Job Card #$jo_id has $tp_name trans #$trans_no.");
        }
        
    $sql_4 = "SELECT *
            FROM ".TB_PREF."outside_material_requisitions     
            WHERE jo_id = ".db_escape($jo_id); 
    
        $result_4 = db_query($sql_4, "check_job_order_has_transactions");
        $row_4 = db_fetch($result_4);
    
        if (isset($row_4['id']) && $row_4['id'] > 0) {
            $tp_name = $systypes_array[$row_4['type']];
            $trans_no = $row_4['trans_no'];
            
            return array('has_trans' => true, 'message' => "Job Card #$jo_id has $tp_name trans #$trans_no.");
        }
        
    $sql_5 = "SELECT *
            FROM ".TB_PREF."material_lists      
            WHERE jo_id = ".db_escape($jo_id); 
    
        $result_5 = db_query($sql_5, "check_job_order_has_transactions");
        $row_5 = db_fetch($result_5);
    
        if (isset($row_5['id']) && $row_5['id'] > 0) {
            $tp_name = $systypes_array[$row_5['type']];
            $trans_no = $row_5['trans_no'];
            
            return array('has_trans' => true, 'message' => "Job Card #$jo_id has $tp_name trans #$trans_no.");
        }
        
        return array('has_trans' => false, 'message' => "");
}

?>