<?php

function add_service_complain($ref, $date_, $vehicle_id, $req_department_id, $customer_name, $customer_address, $description, $booking, $schedule_date) {
    global $Refs;

    $fun_args = func_get_args();
    $args = (object)array_combine(array('ref', 'date', 'vehicle_id', 'req_department_id', 'customer_name', 'customer_address', 'description', 'booking', 'schedule_date'), 
                                  $fun_args);

        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date('Y-m-d H:i:s');
        
        $sql = "INSERT INTO ".TB_PREF."service_complains  
                      ( ref, date_, vehicle_id, req_department_id, 
                        customer_name, customer_address, description, booking, 
                        schedule_date, created_by, created_on )         
                VALUES( ".db_escape($ref).", ".db_escape(date2sql($date_)).", ".db_escape($vehicle_id).", ".db_escape($req_department_id).", 
                        ".db_escape($customer_name).", ".db_escape($customer_address).", ".db_escape($description).", ".db_escape($booking).", 
                        ".db_escape(date2sql($schedule_date)).", ".db_escape($created_by).", ".db_escape($created_on).")";
        
            db_query($sql, "add_service_complain");
            $cmp_id = db_insert_id();
        
            if (! ($cmp_id > 0)) {
                cancel_transaction();

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

        $next_ref = $Refs->get_next(ST_SERVICE_COMPLAIN);
	$Refs->save(ST_SERVICE_COMPLAIN, $cmp_id, $next_ref);
        
	return array('success' => true, 'message' => "", 'cmp_id' => $cmp_id);
}

function update_service_complain($cmp_id, $ref, $date_, $vehicle_id, $req_department_id, $customer_name, $customer_address, $description, $booking, $schedule_date, 
                                 $row_state) {
    
    $fun_args = func_get_args();
    $args = (object)array_combine(array('cmp_id', 'ref', 'date', 'vehicle_id', 'req_department_id', 'customer_name', 'customer_address', 'description', 'booking', 
                                        'schedule_date', 'row_state'), $fun_args);

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

        $sql = "UPDATE ".TB_PREF."service_complains 
                SET ref = ".db_escape($ref).",
                    date_ = ".db_escape(date2sql($date_)).", 
                    vehicle_id = ".db_escape($vehicle_id).",
                    req_department_id = ".db_escape($req_department_id).",
                    customer_name = ".db_escape($customer_name).",
                    customer_address = ".db_escape($customer_address).",
                    description = ".db_escape($description).",
                    booking = ".db_escape($booking).",
                    schedule_date = ".db_escape(date2sql($schedule_date)).",
                    row_state = ".db_escape(db_new_row_state($row_state)).",
                    updated_by = ".db_escape($updated_by).",
                    updated_on = ".db_escape($updated_on)." 
                WHERE id = ".db_escape($cmp_id)." 
                    AND row_state = ".db_escape($row_state);

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

function get_service_complain_record($cmp_id) {
    $sql = "SELECT *
            FROM ".TB_PREF."service_complains 
            WHERE id = ".db_escape($cmp_id);

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

function get_sql_for_service_complains_inquiry($id, $jo_id, $has_job, $reference, $from_date_, $to_date_, $vehicle_id, $req_department_id, $issuing_officer, $customer_name, 
                                               $closed, $from_schedule_date_=null, $to_schedule_date_=null, $customer_address='', $complain='', $booking='') {
    
    $sql = "SELECT cmp.id, cmp.ref, cmp.date_, cmp.vehicle_id, a.description vehicle_name, rdp.value_name req_department, iso.real_name, cmp.customer_name, 
                cmp.customer_address, cmp.description, cmp.closed, jo.source_type, jo.source_trans_no, jo.id jo_id, cmp.booking, cmp.schedule_date 
            FROM ".TB_PREF."service_complains cmp  
                LEFT JOIN ".TB_PREF."asset_master a 
                    ON cmp.vehicle_id = a.asset_id 
                LEFT JOIN ".TB_PREF."list_values rdp  
                    ON cmp.req_department_id = rdp.id  
                        AND rdp.list_code IN ('REQ_DEPARTMENTS', 'DEPARTMENTS') 
                LEFT JOIN ".TB_PREF."users iso    
                    ON cmp.created_by = iso.id  
                LEFT JOIN ".TB_PREF."job_orders jo  
                    ON cmp.id = jo.source_trans_no  
                        AND jo.source_type = ".db_escape(ST_SERVICE_COMPLAIN)."  
            WHERE 1 = 1";
    
        if ($id > 0) {
            $sql .= " AND cmp.id = ".db_escape($id);
        }
    
        if ($jo_id > 0) {
            $sql .= " AND jo.id = ".db_escape($jo_id);
        }
    
        if (! ($id > 0 || $jo_id > 0)) {
            
            if ($has_job == 'Yes') {
                $sql .= " AND jo.source_trans_no > 0";
            } elseif ($has_job == 'No') {
                $sql .= " AND jo.source_trans_no IS NULL";
            }
            
            if (strlen($reference) > 0) {
                $sql .= " AND cmp.ref = ".db_escape($reference);
            }
            
            if ($from_date_ !== null && $to_date_ !== null) {
                $sql .= " AND cmp.date_ >= ".db_escape(date2sql($from_date_))." AND cmp.date_ <= ".db_escape(date2sql($to_date_));
            }

            if ($vehicle_id > 0) {
                $sql .= " AND cmp.vehicle_id = ".db_escape($vehicle_id);
            }
            
            if ($req_department_id > 0) {
                $sql .= " AND cmp.req_department_id = ".db_escape($req_department_id);
            }
            
            if ($issuing_officer > 0) {
                $sql .= " AND cmp.created_by = ".db_escape($issuing_officer);
            }
            
            if (strlen($customer_name) > 1) {
                $sql .= " AND cmp.customer_name LIKE ".db_escape('%'.$customer_name.'%');
            }
            
            if ($closed == 'Yes') {
                $sql .= " AND cmp.closed = 1";
            } elseif ($closed == 'No') {
                $sql .= " AND cmp.closed = 0";
            }
            
            if ($from_schedule_date_ !== null && $to_schedule_date_ !== null) {
                $sql .= " AND cmp.schedule_date >= ".db_escape(date2sql($from_schedule_date_))." AND cmp.schedule_date <= ".db_escape(date2sql($to_schedule_date_));
            }
            
            if (strlen($customer_address) > 1) {
                $sql .= " AND cmp.customer_address LIKE ".db_escape('%'.$customer_address.'%');
            }
            
            if (strlen($complain) > 1) {
                $sql .= " AND cmp.description LIKE ".db_escape('%'.$complain.'%');
            }
            
            if ($booking == 'Yes') {
                $sql .= " AND cmp.booking = 1";
            } elseif ($booking == 'No') {
                $sql .= " AND cmp.booking = 0";
            }
        }
        
	$sql .= " ORDER BY cmp.id DESC";
        
	return $sql;
}

function update_service_complain_row_state($cmp_id, $row_state) {
    $sql = "UPDATE ".TB_PREF."service_complains   
            SET row_state = ".db_escape(db_new_row_state($row_state))." 
            WHERE id = ".db_escape($cmp_id)." 
                AND row_state = ".db_escape($row_state);

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

function get_service_complains_by_filters($id=0, $reference='', $from_date_=null, $to_date_=null, $vehicle_id=0, $req_department_id=0, $issuing_officer=0, $customer_name='', 
                                          $fetch=false, $order_by='') {
    $sql = "SELECT * 
            FROM ".TB_PREF."service_complains 
            WHERE 1 = 1";
    
        if ($id > 0) {
            $sql .= " AND id = ".db_escape($id);
        }
        if (strlen($reference) > 0) {
            $sql .= " AND 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 ($req_department_id > 0) {
            $sql .= " AND req_department_id = ".db_escape($req_department_id);
        }
        if ($issuing_officer > 0) {
            $sql .= " AND created_by = ".db_escape($issuing_officer);
        }
        if (strlen($customer_name) > 1) {
            $sql .= " AND customer_name LIKE ".db_escape($customer_name);
        }
        
        if (strlen($order_by) > 0) {
            $sql .= " ORDER BY $order_by";
        } else {
            $sql .= " ORDER BY id";
        }
        
	$result = db_query($sql, "get_service_complains_by_filters");
            if ($fetch === false) {
                return $result;
            } else {
                return db_fetch($result);
            }
}

function get_service_complain_details_by_filters($id=0, $reference='', $from_date_=null, $to_date_=null, $vehicle_id=0, $req_department_id=0, $issuing_officer=0, 
                                                 $customer_name='', $closed=null, $has_job=null, $jo_id=0, $customer_address='', $complain='', $booking=null, $fetch=false, 
                                                 $order_by='') {
    
    $sql = "SELECT cmp.*, a.description vehicle_name, rdp.value_name req_department_name, CONCAT(a.asset_code, ' - ', a.description) vehicle_full_name, 
                su.real_name issuing_officer, jo.id jo_id, su.employee_code issuing_officer_emp_code   
            FROM ".TB_PREF."service_complains cmp  
                LEFT JOIN ".TB_PREF."asset_master a 
                    ON cmp.vehicle_id = a.asset_id 
                LEFT JOIN ".TB_PREF."list_values rdp  
                    ON cmp.req_department_id = rdp.id  
                        AND rdp.list_code IN ('REQ_DEPARTMENTS', 'DEPARTMENTS') 
                LEFT JOIN ".TB_PREF."users su    
                    ON cmp.created_by = su.id  
                LEFT JOIN ".TB_PREF."job_orders jo 
                    ON cmp.id = jo.source_trans_no  
                        AND jo.source_type = ".db_escape(ST_SERVICE_COMPLAIN)."  
            WHERE 1 = 1";
    
        if ($id > 0) {
            $sql .= " AND cmp.id = ".db_escape($id);
        }
        if (strlen($reference) > 0) {
            $sql .= " AND cmp.ref = ".db_escape($reference);
        }
        if ($from_date_ !== null) {
            $sql .= " AND cmp.date_ >= ".db_escape(date2sql($from_date_));
        }
        if ($to_date_ !== null) {
            $sql .= " AND cmp.date_ <= ".db_escape(date2sql($to_date_));
        }
        if ($vehicle_id > 0) {
            $sql .= " AND cmp.vehicle_id = ".db_escape($vehicle_id);
        }
        if ($req_department_id > 0) {
            $sql .= " AND cmp.req_department_id = ".db_escape($req_department_id);
        }
        if ($issuing_officer > 0) {
            $sql .= " AND cmp.created_by = ".db_escape($issuing_officer);
        }
        if (strlen($customer_name) > 1) {
            $sql .= " AND cmp.customer_name LIKE ".db_escape('%'.$customer_name.'%');
        }
        if ($closed !== null) {
            $sql .= " AND cmp.closed = ".db_escape($closed);
        }
        if ($has_job !== null) {
            if ($has_job == 'Yes') {
                $sql .= " AND jo.source_trans_no > 0";
            } elseif ($has_job == 'No') {
                $sql .= " AND jo.source_trans_no IS NULL";
            }
        }
        
        if ($jo_id > 1) {
            $sql .= " AND jo.id = ".db_escape($jo_id);
        }
        
        if (strlen($customer_address) > 1) {
            $sql .= " AND cmp.customer_address LIKE ".db_escape('%'.$customer_address.'%');
        }

        if (strlen($complain) > 1) {
            $sql .= " AND cmp.description LIKE ".db_escape('%'.$complain.'%');
        }

        if ($booking !== null) {
            $sql .= " AND cmp.booking = ".db_escape($booking);
        } 
        
        if (strlen($order_by) > 0) {
            $sql .= " ORDER BY $order_by";
        } else {
            $sql .= " ORDER BY cmp.date_ DESC, cmp.id";
        }
        
	$result = db_query($sql, "get_service_complain_details_by_filters");
            if ($fetch === false) {
                return $result;
            } else {
                return db_fetch($result);
            }
}

function close_service_complain($cmp_id, $close_description, $row_state) {
    $sql = "UPDATE ".TB_PREF."service_complains   
            SET closed = 1,  
                close_description = ".db_escape($close_description).", 
                row_state = ".db_escape(db_new_row_state($row_state))." 
            WHERE id = ".db_escape($cmp_id)." 
                AND closed = 0";

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

function get_service_complains_count_by_filters($from_date=null, $to_date=null, $req_department_id=0, $vehicle_id=0, $closed=null, $has_job=null) {
    $sql = "SELECT COUNT(cmp.id) total   
            FROM ".TB_PREF."service_complains cmp      
                LEFT JOIN ".TB_PREF."job_orders jo 
                    ON cmp.id = jo.source_trans_no  
                        AND jo.source_type = ".db_escape(ST_SERVICE_COMPLAIN)."  
            WHERE 1 = 1";
    
        if ($from_date !== null && $to_date !== null) {
            $sql .= " AND cmp.date_ >= ".db_escape($from_date)." AND cmp.date_ <= ".db_escape($to_date);
        }
        if ($req_department_id > 0) {
            $sql .= " AND cmp.req_department_id = ".db_escape($req_department_id);
        }
        if ($vehicle_id > 0) {
            $sql .= " AND cmp.vehicle_id = ".db_escape($vehicle_id);
        }
        if ($closed !== null) {
            $sql .= " AND cmp.closed = ".db_escape($closed);
        }
        if ($has_job !== null) {
            if ($has_job == 1) {
                $sql .= " AND jo.source_trans_no > 0";
            } elseif ($has_job == 0) {
                $sql .= " AND jo.source_trans_no IS NULL";
            }
        }

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

function get_service_complain_details($id) {
    $sql = "SELECT cmp.*, a.description vehicle_name, rdp.value_name req_department_name, CONCAT(a.asset_code, ' - ', a.description) vehicle_full_name, 
                su.real_name issuing_officer, jo.id jo_id, su.employee_code issuing_officer_emp_code   
            FROM ".TB_PREF."service_complains cmp  
                LEFT JOIN ".TB_PREF."asset_master a 
                    ON cmp.vehicle_id = a.asset_id 
                LEFT JOIN ".TB_PREF."list_values rdp  
                    ON cmp.req_department_id = rdp.id  
                        AND rdp.list_code IN ('REQ_DEPARTMENTS', 'DEPARTMENTS') 
                LEFT JOIN ".TB_PREF."users su    
                    ON cmp.created_by = su.id  
                LEFT JOIN ".TB_PREF."job_orders jo 
                    ON cmp.id = jo.source_trans_no  
                        AND jo.source_type = ".db_escape(ST_SERVICE_COMPLAIN)."  
            WHERE cmp.id = ".db_escape($id);
    
        $result = db_query($sql, "get_service_complain_details");
        return db_fetch($result);
}

?>