trip_access_type;
if ($accessType == 1) {
$url = "PENDING TASK";
} else {
$url = "PENDING RESERVATION";
}
$office = Office::all();
$company = Companies::all();
$userID = Auth::user()->id;
$getCompanyAccess = User::where("id", $userID)->value('company_access');
$getLocation = User::where("id", $userID)->value('location');
$tripType = User::where("id", $userID)->value('trip_access_type');
//dd($collectResult);
$pending = explode(",", $getCompanyAccess);
$myRequest = Reservation::where("logged_by", auth()->user()->id)->whereNotIn("task_status", ["paid"])->orderBy("reservation_id", "DESC")->paginate(40);
if ($accessType == '0') {
$allTrips = Trip::where("office", $getLocation)->whereDate('pick_up_date', Carbon::today())->get();
$allReservation = Reservation::where("office", $getLocation)->whereNotIn("task_status", ["paid", "cancelled"])->orderBy("reservation_id", "DESC")->paginate(30);
} else {
$allTrips = Trip::whereDate('pick_up_date', Carbon::today())->get();
if ($accessType == "0" || $accessType == "1") {
$allReservation = Reservation::whereNotIn("task_status", ["paid", "cancelled"])->orderBy("reservation_id", "DESC")->paginate(30);
} else if ($accessType == "2") {
$allReservation = Reservation::whereNotIn("task_status", ["paid", "cancelled"])->orderBy("reservation_id", "DESC")->paginate(30);
} else if ($accessType == "3") {
$allReservation = Reservation::whereNotIn("task_status", ["paid"])->orderBy("reservation_id", "DESC")->paginate(30);
} else {
$allReservation = Reservation::whereNotIn("task_status", '!=', "draft")->orderBy("reservation_id", "DESC")->paginate(30);
}
}
$data = ['url' => $url, "office" => $office, "companies" => $company, "tripsbycid" => $allTrips, "myRequest" => $myRequest, "allReservation" => $allReservation];
return view('trips/tripsbycid', $data);
}
public function allreservation() {
//BD = 0, Reservation = 1, Accountant = 2, Admin = 3
$data = [];
$getCompanyAccess = [];
$allTrips = "";
$url = "ALL RESERVATION";
$userID = Auth::user()->id;
$getCompanyAccess = User::where("id", $userID)->value('company_access');
$pending = explode(",", $getCompanyAccess);
$accessType = Auth::user()->trip_access_type;
if ($getCompanyAccess != "" && $getCompanyAccess != '0') {
// $allTrips = Trip::whereIn("cid", $pending)->whereDate('pick_up_date', Carbon::today())->get();
if ($accessType == "0" || $accessType == "1") {
$allReservation = Reservation::whereIn("cid", $pending)->orderBy("reservation_id", "DESC")->paginate(50);
} else if ($accessType == "2" || $accessType == "3") {
$allReservation = Reservation::whereIn("cid", $pending)->orderBy("reservation_id", "DESC")->paginate(50);
} else {
$allReservation = Reservation::whereIn("cid", $pending)->orderBy("reservation_id", "DESC")->paginate(50);
}
} else {
//$allTrips = Trip::whereDate('pick_up_date', Carbon::today())->get();
if ($accessType == "0" || $accessType == "1") {
$allReservation = Reservation::orderBy("reservation_id", "DESC")->paginate(50);
} else if ($accessType == "2" || $accessType == "3") {
$allReservation = Reservation::orderBy("reservation_id", "DESC")->paginate(50);
//$icollection = collect($allReservation);
// return $icollection->count();
//return redirect('/home');
//return redirect()->route('route.name');
//return $now = now(); //Time zone returns an array
//logger('User has logged in.', ['id' => auth()->user()->name]);
// return $path = app_path();
// return $path = base_path();
// return $current = url()->current(); //Return the current URL
//return $full = url()->full();
//return $previous = url()->previous();
//$headers = ['only', 'we'];
//abort(404, 'You Do not Have Access.', $headers);
//return $user = auth()->user()->name;
// config(['app.debug' => true]);
//return back();
//info('Some helpful information!');
//info('User login attempt failed.', ['id' => $user->id]);
//abort_if(! Auth::user()->isAdmin(), 403);
// $allReservation = $icollection->firstWhere('logged_by', '1');
//return $icollection->firstWhere('task_status', '=', "profoma_sent");
//$filtered = $icollection->except(['reservation_id', 'cid']);
//$concatenated = $icollection->concat($icollection)->concat(['name' => 'Johnny Doe']);
//return $concatenated->all();
//return $collection;
} else {
$allReservation = Reservation::orderBy("reservation_id", "DESC")->paginate(50);
}
}
$data = ['url' => $url, "allReservation" => $allReservation];
return view('trips/allreservations', $data);
}
public function alltrips() {
$data = [];
$getCompanyAccess = [];
$allTrips = "";
$office = Office::all();
$url = "ALL TRIPS";
$userID = Auth::user()->id;
$getCompanyAccess = User::where("id", $userID)->value('company_access');
$companies = Companies::where("comp_status", "Active")->get();
$pending = explode(",", $getCompanyAccess);
//whereDate('pick_up_date', Carbon::today())
$allTrips = Trip::whereNotIn("trip_status", ["cancelled"])->where("job_status", "0")->orderBy(DB::raw('ABS(DATEDIFF(pick_up_date, NOW()))'))->paginate(50);
$cancelledTrips = Trip::where("trip_status", "cancelled")->skip(0)->take(100)->orderBy("id", "DESC")->get();
$completedTrips = Trip::where("job_status", "2")->skip(0)->take(500)->orderBy("id", "DESC")->get();
$data = ['url' => $url, "tripsbycid" => $allTrips, "completedTrips" => $completedTrips, "cancelledTrips" => $cancelledTrips, "companies" => $companies, "office" => $office];
return view('trips/alltrips', $data);
}
public function singletripsearch(Request $request) {
$url = "SEARCHING TRIPS";
$data = [];
$reservation_id = $request->reservation_id;
$office = Office::all();
$companies = Companies::where("comp_status", "Active")->get();
//$reservation_id = $request->reservationID;
if ($reservation_id == "" && $request->office == "" && $request->pick_up_date == "" && $request->end_date == "" && $request->cid == "" && $request->passenger_names == "" && $request->vehicle_id == "" && $request->trip_status == "" && $request->job_status == "") {
return redirect('get/alltrips');
}
if (isset($_SERVER['QUERY_STRING']) && trim($_SERVER['QUERY_STRING']) != '') {
$expsql = '';
$firstExplode = explode('&', $_SERVER['QUERY_STRING']);
foreach ($firstExplode as $expItem) {
$secExp = explode('=', trim($expItem));
if (isset($secExp[1]) && trim($secExp[0]) != 'url' && trim($secExp[0]) != 'modroute' && trim($secExp[0]) != '_token') {
//$explode_exp1 = explode('_', trim($secExp[0]));
if (trim($secExp[1]) == 'cid') {
$expsql .= (trim($secExp[1]) != '') ? '`' . $secExp[0] . '` = "' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
} elseif ($secExp[0] === 'pick_up_date') {
// $expsql .= (trim($secExp[1]) != '') ? 'DATE(`' . substr($secExp[0], 0) . '`) >= ' . '"' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
$expsql .= (trim($secExp[1]) != '') ? 'DATE(end_date) >= ' . '"' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
} elseif ($secExp[0] === 'end_date') {
$expsql .= (trim($secExp[1]) != '') ? 'DATE(`' . substr($secExp[0], 0) . '`) <= ' . '"' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
} else if ($secExp[0] === 'cid') {
$expsql .= (trim($secExp[1]) != '') ? '`' . $secExp[0] . '` = "' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
} else
$expsql .= (trim($secExp[1]) != '') ? '`' . $secExp[0] . '` like ' . '"%' . str_replace('+', ' ', $secExp[1]) . '%" AND ' : '';
}else {
$expsql .= "";
}
}
$formatexpsql = substr($expsql, 0, -4);
//$formatexpsql = trim($formatexpsql) != '' ? $formatexpsql.' AND ': '';
$allTrips = DB::table("trips")->whereRaw(DB::raw($formatexpsql))->get();
//$allTrips = Trip::where($formatexpsql)->paginate(100);
} else {
$allTrips = '';
}
/* if(isset($search)){
$allTrips = Trip::where('reservation_id', '=', $search)
->orWhere('passenger_names', 'LIKE', '%'.$search.'%')
->orWhere('pick_up_date', '=', $search)
->orWhere('vehicle_id', 'LIKE', '%'.$search.'%')
->paginate(30)
->setpath('');
$allTrips->appends(array(
'q' => $search
));
}else if(isset($request->pickupdate) && isset($request->endate) && isset($request->office) ){
$allTrips = Trip::where('pick_up_date', '>=', $request->pickupdate)
->where("end_date", '<=', $request->endate)->where("office", $request->office)->paginate(100);
}else if(isset($request->pickupdate) && isset($request->endate) && isset($request->cid) ){
$allTrips = Trip::where('pick_up_date', '>=', $request->pickupdate)
->where("end_date", '<=', $request->endate)->where("cid", $request->cid)->paginate(100);
}else if(isset($request->pickupdate) && isset($request->endate)){
$allTrips = Trip::where('pick_up_date', '>=', $request->pickupdate)->where("end_date", '<=', $request->endate)->paginate(100);
}else if(isset($request->office)){
$allTrips = Trip::where('office', $request->office)
->paginate(100);
}else if(isset($request->office) && isset($request->cid)){
$allTrips = Trip::where('office', $request->office)->where('cid', $request->cid)->paginate(100);
}else if(isset($request->cid)){
$allTrips = Trip::where('cid', $request->cid)
->paginate(100);
}
*
*/
if (count($allTrips) > 0) {
$data = ['url' => $url, "tripsbycid" => $allTrips, "companies" => $companies, "office" => $office];
return view('trips/alltrips', $data);
} else {
$data = ['url' => $url, "tripsbycid" => $allTrips, "companies" => $companies, "office" => $office, "message" => "No record Found"];
return view('trips/alltrips', $data);
}
}
public function tripsearch(Request $request) {
$url = "SEARCHING TASK";
$data = [];
$search = $request->search;
$office = Office::all();
$company = Companies::all();
$reservation_id = $request->reservation_id;
if ($reservation_id == "" && $request->office == "" && $request->reservation_date == "" && $request->cid == "" && $request->contact_fullname == "" && $request->task_status == "") {
return redirect('get/tripsbycompanys');
}
if (isset($_SERVER['QUERY_STRING']) && trim($_SERVER['QUERY_STRING']) != '') {
$expsql = '';
$firstExplode = explode('&', $_SERVER['QUERY_STRING']);
foreach ($firstExplode as $expItem) {
$secExp = explode('=', trim($expItem));
if (isset($secExp[1]) && trim($secExp[0]) != 'url' && trim($secExp[0]) != 'modroute' && trim($secExp[0]) != '_token') {
//$explode_exp1 = explode('_', trim($secExp[0]));
if (trim($secExp[1]) == 'cid') {
$expsql .= (trim($secExp[1]) != '') ? '`' . $secExp[0] . '` = "' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
} elseif (trim($secExp[1]) == 'reservation_id') {
$expsql .= (trim($secExp[1]) != '') ? '`' . $secExp[0] . '` = "' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
} elseif ($secExp[0] === 'reservation_date') {
$expsql .= (trim($secExp[1]) != '') ? 'DATE(`' . substr($secExp[0], 0) . '`) = ' . '"' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
} else if ($secExp[0] === 'cid') {
$expsql .= (trim($secExp[1]) != '') ? '`' . $secExp[0] . '` = "' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
} else
$expsql .= (trim($secExp[1]) != '') ? '`' . $secExp[0] . '` like ' . '"%' . str_replace('+', ' ', $secExp[1]) . '%" AND ' : '';
}else {
$expsql .= "";
}
}
$formatexpsql = substr($expsql, 0, -4);
//$formatexpsql = trim($formatexpsql) != '' ? $formatexpsql.' AND ': '';
$allReservation = DB::table("reservations")->whereRaw(DB::raw($formatexpsql))->get();
} else {
$allReservation = '';
}
/*
if(isset($request->office) && isset($request->cid)){
$allReservation = Reservation::where(['office' => $request->office, "cid" => $request->cid])->paginate(50);
}else if(isset($request->office)){
$allReservation = Reservation::where('office', '=', $request->office)->paginate(50);
}else if(isset($request->cid)){
$allReservation = Reservation::where('cid', '=', $request->cid)->paginate(50);
}else if(isset($request->search)){
$allReservation = Reservation::where('reservation_id', '=', $search)
->orWhere('contact_name', 'LIKE', '%'.$search.'%')
->orWhere('contact_fullname', 'LIKE', '%'.$search.'%')
->paginate(50)
->setpath('');
$allReservation->appends(array(
'q' => $search
));
}
*
*/
if ($allReservation) {
$data = ['url' => $url, "office" => $office, "companies" => $company, "allReservation" => $allReservation];
return view('trips/tripsbycid', $data);
} else {
return $this->index();
// $data = ['url' => $url, "office"=>$office, "companies" => $company, "allReservation" => $allReservation];
//return view('trips/tripsbycid', $data);
}
}
public function confirmtrip(Request $request) {
//BD = 0, Reservation = 1, Accountant = 2, Admin = 3
$data = [];
$accessType = Auth::user()->trip_access_type;
if (isset($request->dataid) && ($accessType == "1" || $accessType == "3")) {
$idate = date("Y-m-d H:i:s");
$audit = "| Confirmed By " . Auth::user()->name . " at $idate |
";
$array = ["task_status" => "confirmed", "auditTrail" => $audit];
$response = Reservation::where("reservation_id", $request->dataid)->update($array);
$array2 = ["trip_status" => "confirmed", "task_status" => "4", "confirmed_by" => Auth::user()->id, "auditTrail" => $audit];
$trip_response = Trip::whereIn("reservation_id", [$request->dataid])->update($array2);
if ($response) {
$data = ["status" => 200];
} else {
$data = ["status" => 400];
}
} else {
$data = ["status" => 500];
}
return response()->json($data);
}
public function reservationtrips($id) {
$data = [];
if (isset($id)) {
$response = Trip::where("reservation_id", $id)->get();
$confirmedSum = Trip::where(["reservation_id" => $id, "trip_status" => "confirmed"])->sum("total_cost");
$submittedSum = Trip::where(["reservation_id" => $id, "trip_status" => "submitted"])->sum("total_cost");
$canclledSum = Trip::where(["reservation_id" => $id, "trip_status" => "cancelled"])->sum("total_cost");
$data = ["status" => 200, "confirmedSum" => @number_format($confirmedSum, 2), "canclledSum" => @number_format($canclledSum, 2), "submittedSum" => @number_format($submittedSum, 2), "success" => TripResource::collection($response)];
} else {
$data = ["status" => 400, "success" => ''];
}
return response()->json($data);
}
public function getvdrive() {
$data = [];
$drivers = Drivers::orderBy('name', 'ASC')->get();
$vehicles = Vehicles::where("vehicle_status", "Active")->orderBy('plate_number', 'ASC')->get();
$data = ["status" => 200, "drivers" => $drivers, "vehicles" => $vehicles];
return response()->json($data);
}
public function addResource($id = "") {
//BD = 0, Reservation = 1, Accountant = 2, Admin = 3
//Use the ID to get Status
$accessType = Auth::user()->trip_access_type;
$id = Trip::where("id", $id)->value("task_status");
$checknewstatus = Trip::where("id", $id)->value("trip_status");
if ($id == "") {
return redirect()->route('tripsearch');
} else if ($id == "4") {
echo "I am confirmed. Please go Back <<";
} else {
echo "You cannot add Resources to a trip that is not confirmed
";
$data = [];
$getCompanyAccess = [];
$allTrips = "";
$url = "PENDING TASK";
$userID = Auth::user()->id;
$getCompanyAccess = User::where("id", $userID)->value('company_access');
$pending = explode(",", $getCompanyAccess);
if ($getCompanyAccess != "" && $getCompanyAccess != '0') {
$allTrips = Trip::whereIn("cid", $pending)->paginate(50);
$allReservation = Reservation::whereIn("cid", $pending)->paginate(50);
} else {
$allTrips = Trip::paginate(50);
$allReservation = Reservation::paginate(50);
}
$data = ['url' => $url, "tripsbycid" => $allTrips, "allReservation" => $allReservation];
return response()->view('trips/tripsbycid', $data, 200)->header("Refresh", "5;url=/tripsearch");
}
}
public function resourcesAdded(Request $request) {
//BD = 0, Reservation = 1, Accountant = 2, Admin = 3
$online = false;
$data = [];
$accessType = Auth::user()->trip_access_type;
if (isset($request->id) && ($accessType == "1" || $accessType == "3")) {
$explode_pickup = explode(" ", $request->pick_up_date);
$explode_end_date = explode(" ", $request->end_date);
/////////////////////////Number of Days /////////////////////////////////////////////////////
$sPickDate = Carbon::parse($explode_pickup[0]);
$sEndDate = Carbon::parse($explode_end_date[0]);
$days = $sEndDate->diffInDays($sPickDate);
$days = $days == 0 ? $days + 1 : $days + 1;
$totalCost = $days * $request->price;
$array = [
"pick_up_date" => $explode_pickup[0],
"pickup_time" => isset($explode_pickup[1]) ? $explode_pickup[1] : '00:00',
"end_date" => $explode_end_date[0],
"end_time" => isset($explode_end_date[1]) ? $explode_end_date[1] : '00:00',
"pickup_street" => $request->pickup_street,
"actual_street" => $request->actual_street,
"price" => $request->price,
"total_cost" => $totalCost,
"driver" => $request->drivers,
"vehicle_id" => $request->vehicles,
"number_of_days" => $days
];
$save = Trip::where("id", $request->id)->update($array);
$driverName = Drivers::where("driver_id", $request->drivers)->value("name");
$phone_no = Drivers::where("driver_id", $request->drivers)->value("phone_no");
$vehicleMake = Vehicles::where("plate_number", $request->vehicles)->value("vehicle_make+model");
$resID = Trip::where("id", $request->id)->value("reservation_id");
$passengerNo = $request->passenger_phone_numbers;
$passenger_names = $request->passenger_names;
$sms = 'Thank you for making your reservation with Hertz Nigeria. Your Ref No is ' . $request->id
. " Departure " . ucwords($request->pickup_street) . ", Destination " . ucwords($request->actual_street) . " Vehicle: #" . $request->vehicles . "; Driver: " . $driverName . " Driver Phone no: " . $phone_no . " Pickup Date/Time: " . $request->pick_up_date . " " . $request->end_date . " Vehicle Make: " . $vehicleMake . " Should you have any questions regarding our service, please contact "
. "info@rentacar.ng or call 234 903 886 9179 Enjoy your trip.";
$driverSMS = 'You have a reservation Job, with task No ' . $resID . ' Passenger Name ' . ucwords($passenger_names)
. " Passenger Number " . $passengerNo . ", Departure " . ucwords($request->pickup_street) . ", Destination " . ucwords($request->actual_street) . " Pickup Date/Time: " . $request->pick_up_date . " End Date " . $request->end_date . " "
. "Please ensure you are ready for the trip. kindly confirm from your reservation officer if there are any other additional information";
;
$smsSent = $this->initiateSmsActivation($passengerNo, $sms);
$smsDriver = $this->initiateSmsActivation($phone_no, $driverSMS);
$user = Auth::user()->name;
$iDate = date('Y-m-d H:i:s');
$auditTrail = "| Updated By $user with the following items " . json_encode($array) . " at $iDate |
";
$auditUpdate = DB::statement("UPDATE `trips` SET `driver_sms` = '" . $driverSMS . "', `passenger_sms` = '" . $sms . "' , `auditTrail` = CASE WHEN auditTrail = '' THEN '$auditTrail' ELSE CONCAT(`auditTrail`, '$auditTrail') END WHERE `id` = '" . $request->id . "' ");
//////////////////////////////////// SENDING EMAIL /////////////////////////////////////////////
if ($request->email_address && $request->email_address != "" && $online == true) {
Mail::send(['html' => 'invoice.passenger-message'], $data, function ($message) use($data) {
// dd($data['email']);
$message->from('info@sys.c-ileasing.com', 'Hertz Rental');
$message->subject('LOGISTICS DETAILS FOR YOUR TRIP');
//$message->to($data['email'])->bcc('oladejo.lasisi@c-ileasing.com');
$message->to($data['email']);
$message->sender(Auth::user()->email, Auth::user()->name);
$message->getSwiftMessage();
});
}
//////////////////////////////////// END OF SENDING EMAIL //////////////////////////////////////
if ($auditUpdate) {
$data = ["status" => 200, "success" => $array, "id" => $request->id, "passenger_names" => $passenger_names, "driverName" => $driverName, "phone_no" => $phone_no, "email" => $request->email_address];
} else {
$data = ["status" => 400, "success" => 'Error Updating Request, Please Try Later Or Contact Admin!'];
}
} else {
$data = ["status" => 500];
}
return response()->json($data);
}
//////////////////////////////////////////////////////SMS FUNCTION/////////////////////////////////////////
public function initiateSmsActivation($phone_number, $message) {
$isError = 0;
$errorMessage = true;
//Preparing post parameters
$postData = array(
'username' => $this->SMS_USERNAME,
'password' => $this->SMS_PASSWORD,
'message' => $message,
'sender' => $this->SMS_SENDER,
'mobiles' => $phone_number,
'response' => $this->RESPONSE_TYPE
);
//$url = "http://portal.bulksmsnigeria.net/api/"; // portal.nigeriabulksms.com
$url = "http://portal.nigeriabulksms.com/api/";
$ch = curl_init();
curl_setopt_array($ch, array(
CURLOPT_URL => $url,
CURLOPT_RETURNTRANSFER => true,
CURLOPT_POST => true,
CURLOPT_POSTFIELDS => $postData
));
//Ignore SSL certificate verification
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
//get response
$output = curl_exec($ch);
//Print error if any
if (curl_errno($ch)) {
$isError = true;
$errorMessage = curl_error($ch);
}
curl_close($ch);
if ($isError) {
return array('error' => 1, 'message' => $errorMessage);
} else {
return array('error' => 0);
}
}
public function selectOfficeFirst() {
$url = "SELECT OFFICE";
$userID = Auth::user()->id;
$getCompanyAccess = User::where("id", $userID)->value('company_access');
//Returns all Offices
$office = Office::all();
$data = ['url' => $url, 'office' => $office];
return view('trips/select_office_to_add_trip', $data);
}
public function processofficerequest(Request $request) {
$json = [];
$accessStatus = "submitted";
try{
$accessType = Auth::user()->trip_access_type;
$accessStatus = $accessType != "1" ? "submitted" : "confirmed";
}catch(\Exception $e){
}
if ($request->office) {
$json = response([ 'status' => 400, 'data' => "please select office"]);
}
try {
$reservation = new Reservation;
$reservation->office = $request->office;
//$reservation->cid = Auth::user()->cid;
$reservation->logged_by = Auth::user()->id;
$reservation->created_by = Auth::user()->name;
$reservation->task_status = $accessStatus;
$reservation->type = "admin";
$reservation->random_num = rand(11111111, 999999) . "" . time() . date('Ymd');
$reservation->confirm_payment = "";
$reservation->reservation_date = date('Y-m-d');
$reservation->auditTrail = "| Created By " . Auth::user()->name . " at " . date('Y-m-d') . " |
";
$officeName = Office::where("office_id", $request->office)->value("office_name");
$success = $reservation->save();
$id = $reservation->id;
$json = response([ 'status' => 200, 'office_id' => $request->office, 'id' => $id, 'random' => $reservation->random_num, 'data' => $officeName]);
} catch (Exception $ex) {
$route = request()->route()->getAction('controller');
$ex->getMessage();
$b = new MailException();
$success = $b::sendErrorMsg("moses.ocho@c-ileasing.com", "oladejo.lasisi@c-ileasing.com", "onuh.peter@c-ileasing.com", $route, $ex->getMessage());
$json = response([ 'status' => 401, 'data' => "we cannot process your request at the moment"]);
}
return response()->json($json);
}
public function add_reservation($office_id, $id, $random, $ioffice) {
$url = "NEW RESERVATION";
$button = "";
//Use the reservation id to check the status of the trip
$checkStatus = Reservation::where("reservation_id", $id)->value("task_status");
//if profoma has been sent please see the accountant
if ($checkStatus == "profoma_sent") {
echo "You cannot edit/add to the trip/reservation a profoma has been sent, please contact account"
. "to modify the the invoice";
return;
}
if (isset($_SERVER['QUERY_STRING']) && $_SERVER['QUERY_STRING'] != '') {
$activateTab = explode("=", $_SERVER['QUERY_STRING']);
if ($activateTab[0] == "add") {
$activateTab = 'add';
$url = "ADD TRIP";
$button = "save";
} else if ($activateTab[0] == "edit") {
$activateTab = 'add';
$url = "Edit TRIP";
$button = "update";
} else if ($activateTab[0] == "profoma") {
$activateTab = 'profoma';
$url = "Add Trip To Profoma";
$button = "profoma";
}
} else {
$activateTab = 'false';
$url = "NEW RESERVATION";
}
/* if(!isset($_SERVER['QUERY_STRING']) && trim($_SERVER['QUERY_STRING']) == ''){
$activateTab = 'false';
$url = "NEW RESERVATION";
} */
$array = ["reservation_id" => $id, "random_num" => $random];
$exist = Reservation::where($array)->value("random_num");
$getifExist = Reservation::where($array)->get()->first();
if ($office_id == "" || $id == "" || $random == "" || $ioffice == "") {
return redirect()->route('add_new_trip');
} else if (!$exist || $exist == "") {
return redirect()->route('add_new_trip');
} else {
$data = [];
$getCompanyAccess = [];
$allTrips = "";
/* $matchThese = ['module_url' => $url];
$module_category = Modules::where($matchThese)->value('module_category');
$module_user = Modules::where($matchThese)->value('module_user');
if((!in_array($module_category, explode(', ', Auth::user()->category_access)) || (!in_array($module_user, array('0', Auth::user()->user_type))))){
Session::flash('info', 'You do not have access to that module');
return redirect()->back();
}
*
*/
$userID = Auth::user()->id;
$getCompanyAccess = User::where("id", $userID)->value('company_access');
$matchThese = ['reservation' => '1'];
$serviceType = ServiceType::where($matchThese)->get();
$additionalService = AdditionalService::all();
$allTrips = Trip::where("reservation_id", $id)->get();
//Returns all Offices
$office = Office::all();
//$companies = Companies::all();
$matchTheseCompany = ['comp_status' => "Active"];
$companies = Companies::where($matchTheseCompany)->orderBy("company_name")->get();
$getVehicleTypes = "SELECT vehicle_id, service_type, vehicle_type, `vehicle_make+model`, vehicle_status, office, COUNT(vehicle_id) As dCount FROM `vehicles` wHERE vehicle_status='Active' GROUP BY vehicle_type";
$vehicleCategory = collect(DB::select(DB::raw($getVehicleTypes)));
$data = ['url' => $url, "activateTab" => $activateTab, "button" => $button, 'existingTrips' => $allTrips, "getifExist" => $getifExist, "reservationID" => $id, 'office_id' => $office_id, "random" => $random, "office" => $office, 'sType' => $serviceType, 'vCat' => $vehicleCategory, 'comp' => $companies, 'ioffice' => $ioffice, 'additionalService' => $additionalService];
return view('trips/add_reservation_trip', $data);
}
}
public function saveTrip(Request $request) {
//BD = 0, Reservation = 1, Accountant = 2, Admin = 3
$json = [];
$plate = "";
// $bookingID = BookingInvoice::select("id")->where("cid", $request->client_name)->where("invoice_status", "!=", "4")->get();
//dd($bookingID);
/*
* Goto BookingInvoice get the id of all paid invoice for the current month
* $bookingID = BookingInvoice::where("id", $request->client_name, "invoice_status", "4")->pluck("id")
* Goto BookingInvoiceItem to sum the total net for the month
*
*
*/
if ($request->reservationID == "" || $request->service_type == "" || $request->passengerName == "") {
$json = ["status" => 400, "msg" => 'Please make sure service type and passenger name is filled'];
} else {
try {
$sPickDate = Carbon::parse($request->pick_up_date);
$sEndDate = Carbon::parse($request->end_date);
$days = $sEndDate->diffInDays($sPickDate);
$days = $days == 0 ? $days + 1 : $days + 1;
$accessType = Auth::user()->trip_access_type;
$accessStatus = $accessType != "1" ? "pending" : "confirmed";
$trip = new Trip;
$trip->service_type = $request->service_type;
$trip->office = $request->office;
$trip->passenger_names = $request->passengerName;
$trip->email_address = $request->passengers_email;
$trip->passenger_phone_numbers = $request->passengers_phone;
$trip->departure = $request->depature;
$trip->destination = $request->destination;
$trip->pick_up_date = $request->pick_up_date;
$trip->pickup_time = $request->pick_up_time;
$trip->end_date = $request->end_date;
$trip->end_time = $request->end_date_time;
$trip->vehicle_type = $request->vehicle_type;
$trip->number_of_days = $days;
$trip->reservation_id = $request->reservationID;
$trip->_token = $request->_token;
$trip->trip_status = $accessStatus;
$trip->logged_by = Auth::user()->id;
$trip->created_by = Auth::user()->id;
$trip->actual_street = $request->actual_street;
$trip->pickup_street = $request->pick_up_location;
$trip->price = $request->costoftrip;
$trip->vehicle_make = $request->vehicle_make;
$trip->auditTrail = "| Trip Added By " . Auth::user()->name . " at " . date('Y-m-d') . " |
";
$trip->rental_conditions = isset($request->rental_conditions) ? $request->rental_conditions : '';
//Reservation Team will not need to confirm so this should be 4 rather than 2
$trip->task_status = 0;
if ($request->credit_type === "non_credit") {
$trip->cid = 96;
} else if ($request->credit_type === "credit") {
$trip->cid = $request->client_name;
}
//This sql will be dealt with later
$getVehicleTypes = "SELECT `plate_number`, `vehicle_id`, `service_type`, `vehicle_make+model` as vehicle_make,
(SELECT office_name from offices WHERE office_id = office) as office_location , `vehicle_type`,`vehicle_status`, `vehicle_driver`, COUNT(vehicle_id) as dCount
FROM `vehicles`
WHERE vehicle_type = '" . $request->vehicle_type . "' AND service_type ='rental' AND vehicle_status = 'Active'
AND office IN (SELECT locations_cost.office from locations_cost WHERE departure = '" . $request->depature . "')
AND vehicle_id NOT IN
(SELECT vehicle_id from maintenances WHERE maintenances.vehicle_id = vehicles.vehicle_id AND maint_status < 6)
AND vehicle_id NOT in
(SELECT vehicle_id from trips WHERE
(task_status = '4' AND job_status != 2 AND date(NOW()) < date('" . $request->end_date . "') AND date('" . $request->pick_up_time . "') > date(NOW())) ||
(task_status = '4' AND job_status != 2 AND date(NOW()) < date('" . $request->end_date . "') AND date('" . $request->pick_up_time . "') < date(NOW()))
GROUP BY vehicle_id ) GROUP by vehicle_type, office, `vehicle_make+model` ORDER BY rand() LIMIT 1";
$plate_number = collect(DB::select(DB::raw($getVehicleTypes)));
if ($plate_number) {
foreach ($plate_number as $get) {
$plate = $get->plate_number;
}
}
$trip->vehicle_id = $plate;
//Calculating the total amount
$matchThese = ['departure' => $request->depature, 'destination' => $request->destination,
'vehicle_type' => $request->vehicle_type, 'cid' => $request->client_id,
'service_type' => $request->service_type, 'vehicle_make' => $request->vehicle_make];
$pricemain = LocationCost::where($matchThese)->value('cost');
$discount_web = LocationCost::where($matchThese)->value('discount_web');
//Additional Web
$addition_web = LocationCost::where($matchThese)->value('addition_web');
$fullPrice = (($request->costoftrip * $days)) + $request->additional_cost;
$trip->total_cost = $fullPrice;
$statusBtn = $request->statusBtn;
if ($statusBtn === "adding..") {
$tripAdd = $trip->save();
} else if ($statusBtn === "updating..") {
//Declare Update Array Here...
$updateArry = [
"service_type" => $request->service_type,
"passenger_names" => $request->passengerName,
"email_address" => $request->passengers_email,
"passenger_phone_numbers" => $request->passengers_phone,
"departure" => $request->departure,
"destination" => $request->destination,
"pick_up_date" => $request->pick_up_date,
"pickup_time" => $request->pick_up_time,
"end_date" => $request->end_date,
"end_time" => $request->end_date_time,
"vehicle_type" => $request->vehicle_type,
"number_of_days" => $days,
"reservation_id" => $request->reservationID,
"actual_street" => $request->actual_street,
"pickup_street" => $request->pick_up_location,
"price" => $request->costoftrip,
"vehicle_make" => $request->vehicle_make,
"rental_conditions" => isset($request->rental_conditions) ? $request->rental_conditions : '',
"total_cost" => $fullPrice
];
$tripAdd = Trip::where("id", $request->edit_trip_id)->update($updateArry);
//Update Trails
$userName = Auth::user()->name;
$iDate = date('Y-m-d H:i:s');
$auditTrail = "| Updated By $userName with the following items " . json_encode($updateArry) . " at $iDate |
";
$auditUpdate = DB::statement("UPDATE `trips` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$auditTrail' ELSE CONCAT(`auditTrail`, '$auditTrail') END WHERE `id` = '" . $request->edit_trip_id . "' ");
} else if ($statusBtn === "profoma..") {
$tripAdd = $trip->save();
try{
$BookingInvoiceID = BookingInovice::where("reservation_id", $request->reservationID)->value('booking_invoice_item_id');
//Also insert the trip to profoma invoice
$addtoProfoma = BookingInvoiceItem::create([
'booking_invoice_item_id' => $BookingInvoiceID,
'reservation_id' => $request->reservationID,
'cid' => $request->client_name,
'startDate' => $request->pick_up_date,
'description' => $request->service_type. " for ". $request->passengerName. " from ". $request->pick_up_location.
" to ". $request->actual_street. " from ". $request->pick_up_date. $request->pick_up_time. " to ".
$request->end_date. $request->end_date_time. " Vehicle(s): ". $plate,
'unit_cost' => $request->costoftrip,
'vat' => $request->costoftrip * 0.5,
'qty' => 1,
'tripID' => $request->tripID,
'logged_by' => Auth::user()->id,
'net_value' => ($request->costoftrip) + ($request->costoftrip * 0.5),
]);
}catch(\Exception $e){
//we should log here
\Log::error($e->toString());
}
} else {
$tripAdd = $trip->save();
}
$lastID = $trip->id;
$accessStatus2 = $accessType != "1" ? "submitted" : "confirmed";
//Update the cost in the reservation table
if ($request->credit_type === "credit" && $statusBtn === "adding..") {
$reservationCost = "UPDATE `reservations` SET `task_status` = '$accessStatus2', `comment` = '$request->comment', `no_of_passenger` = '$request->no_of_passenger', `contact_phone_number`= '$request->contact_phone', `contact_email_address` = '$request->contact_email', `contact_fullname`= '$request->contact_fullname', `contact_name_id`= '$request->contact_name_id', `client_type`= '$request->client_type', `credit_type` = '$request->credit_type', `cost` = '$fullPrice', `client_name` = '$request->client_name', `cid` = '$request->client_name' WHERE `reservation_id` = '$request->reservationID'";
} else if ($request->credit_type === "non_credit" && $statusBtn === "adding..") {
$reservationCost = "UPDATE `reservations` SET `task_status` = '$accessStatus2', `contact_name` = '$request->client_name_bd', `comment` = '$request->comment', `contact_phone_number`= '$request->contact_phone', `contact_email_address` = '$request->contact_email', `contact_fullname`= '$request->contact_fullname_bd', `contact_name_id`= '$request->contact_name_id', `client_type`= '$request->client_type', `credit_type` = '$request->credit_type', `cost` = '$fullPrice', `client_name` = '96', `cid` = '96' WHERE `reservation_id` = '$request->reservationID'";
} else if ($request->credit_type === "credit" && $statusBtn === "updating..") {
$reservationCost = "UPDATE `reservations` SET `comment` = '$request->comment', `contact_phone_number`= '$request->contact_phone', `contact_email_address` = '$request->contact_email', `contact_fullname`= '$request->contact_fullname', `contact_name_id`= '$request->contact_name_id', `client_type`= '$request->client_type', `credit_type` = '$request->credit_type', `cost` = '$fullPrice', `client_name` = '$request->client_name', `cid` = '$request->client_name' WHERE `reservation_id` = '$request->reservationID'";
} else if ($request->credit_type === "non_credit" && $statusBtn === "updating..") {
$reservationCost = "UPDATE `reservations` SET `comment` = '$request->comment', `contact_phone_number`= '$request->contact_phone', `contact_email_address` = '$request->contact_email', `contact_fullname`= '$request->contact_fullname', `contact_name_id`= '$request->contact_name_id', `client_type`= '$request->client_type', `credit_type` = '$request->credit_type', `cost` = '$fullPrice', `client_name` = '$request->client_name', `cid` = '$request->client_name' WHERE `reservation_id` = '$request->reservationID'";
} else if ($request->credit_type === "credit" && $statusBtn === "profoma..") {
$reservationCost = "UPDATE `reservations` SET `comment` = '$request->comment', `contact_phone_number`= '$request->contact_phone', `contact_email_address` = '$request->contact_email', `contact_fullname`= '$request->contact_fullname', `contact_name_id`= '$request->contact_name_id', `client_type`= '$request->client_type', `credit_type` = '$request->credit_type', `cost` = '$fullPrice', `client_name` = '$request->client_name', `cid` = '$request->client_name' WHERE `reservation_id` = '$request->reservationID'";
} else if ($request->credit_type === "non_credit" && $statusBtn === "profoma..") {
$reservationCost = "UPDATE `reservations` SET `comment` = '$request->comment', `contact_phone_number`= '$request->contact_phone', `contact_email_address` = '$request->contact_email', `contact_fullname`= '$request->contact_fullname', `contact_name_id`= '$request->contact_name_id', `client_type`= '$request->client_type', `credit_type` = '$request->credit_type', `cost` = '$fullPrice', `client_name` = '$request->client_name', `cid` = '$request->client_name' WHERE `reservation_id` = '$request->reservationID'";
}
$reservationUpdate = collect(DB::update(DB::raw($reservationCost)));
if ($reservationUpdate) {
$json = [
"status" => 200,
'rID' => $request->reservationID,
'tripID' => $lastID,
'passengerName' => $request->passengerName,
'pick_up_date' => $request->pick_up_date,
'end_date' => $request->end_date,
'depature' => $request->pick_up_location,
'destination' => $request->actual_street,
'vehicle_type' => $request->vehicle_type,
'service_type' => $request->service_type,
'costoftrip' => $request->costoftrip,
"statusBtn" => $statusBtn,
"total_cost" => $fullPrice
//'tripID' => $lastID
];
}
} catch (Exception $ex) {
$route = request()->route()->getAction('controller');
$ex->getMessage();
$b = new MailException();
$success = $b::sendErrorMsg("oladejo.lasisi@c-ileasing.com", "oladejo.lasisi@c-ileasing.com", "david.olushola@c-ileasing.com", $route, $ex->getMessage());
$json = response([ 'status' => 401, "statusBtn" => $statusBtn, 'data' => "we cannot process your request at the moment"]);
}
}
return response()->json($json);
}
public function tripEdit(Request $request) {
$json = [];
$checkStatus = Trip::where("id", $request->id)->value("trip_status");
$jobStatus = Trip::where("id", $request->id)->value("job_status");
if ($jobStatus == "0" || $jobStatus == "") {
$sendtripdetails = Trip::where("id", $request->id)->get()->first();
$json = ["status" => 200, "content" => new TripEditResource($sendtripdetails)];
} else {
$json = ["status" => 400, "jb" => Jobmodel::where("id", $jobStatus)->value("name"),];
}
return response()->json($json);
}
public function deleteTrip(Request $request) {
$json = [];
$whologedIn = Auth::user()->id;
$checkStatus = Trip::where("id", $request->id)->value("trip_status");
$getResID = Trip::where("id", $request->id)->value("reservation_id");
$getStatus = Reservation::where("reservation_id", $getResID)->value("task_status");
$getCreatedBy = Reservation::where("reservation_id", $getResID)->value("logged_by");
if (($getCreatedBy == $whologedIn) && ($getStatus == "confirmed" || $getStatus == "submitted" ||
$getStatus == "cancelled")) {
//You need to also delete additional service if it exist
$deleteAdd = AdditionalServiceTrip::where("trip_id", $request->id)->forcedelete();
$delete = Trip::where("id", $request->id)->delete();
$json = ["status" => 200];
} else {
$json = ["status" => 400, "status" => $checkStatus];
}
return response()->json($json);
}
public function closingtrip(Request $request) {
$json = [];
$checkStatus = Trip::where("id", $request->id)->value("trip_status");
if ($checkStatus == "confirmed" && $request->id) {
$tripArray = ["job_status" => 2];
$closeTrip = Trip::where("id", $request->id)->update($tripArray);
$iDate = date('Y-m-d H:i:s');
$auditTrail = "| Trip Closed By " . Auth::user()->name . " on $iDate |
";
$auditUpdate = DB::statement("UPDATE `trips` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$auditTrail' ELSE CONCAT(`auditTrail`, '$auditTrail') END WHERE `id` = '" . $request->id . "' ");
$json = ["status" => 200];
} else {
$json = ["status" => 400, "status" => $checkStatus];
}
return response()->json($json);
}
public function closereservation_trips(Request $request) {
$json = [];
$task_status = Reservation::where("reservation_id", $request->iClose)->value("task_status");
$invoiceStatus = BookingInvoice::where("reservation_id", $request->iClose)->value('invoice_status');
if ($task_status == "confirmed") {
$BookingInvoice = new BookingInvoice;
$BookingInvoice->reservation_date = Reservation::where("reservation_id", $request->iClose)->value("reservation_date");
$BookingInvoice->reservation_created_at = Reservation::where("reservation_id", $request->iClose)->value("created_at");
$BookingInvoice->invoice_date = date('Y-m-d H:i:s');
$BookingInvoice->cid = Reservation::where("reservation_id", $request->iClose)->value("cid");
$BookingInvoice->office = Reservation::where("reservation_id", $request->iClose)->value("office");
$BookingInvoice->client_type = Reservation::where("reservation_id", $request->iClose)->value("client_type");
$BookingInvoice->credit_type = Reservation::where("reservation_id", $request->iClose)->value("credit_type");
$BookingInvoice->contact_name = Reservation::where("reservation_id", $request->iClose)->value("contact_fullname");
$BookingInvoice->contact_email = Reservation::where("reservation_id", $request->iClose)->value("contact_email_address");
$BookingInvoice->phone_number = Reservation::where("reservation_id", $request->iClose)->value("contact_phone_number");
//$BookingInvoice->purchase_order = $request->purchase_order;
$BookingInvoice->invoice_template = 'plain';
$BookingInvoice->tin_number = '01056712-001';
$BookingInvoice->vat_number = '01056712-001';
//$BookingInvoice->payment_term = $request->payment_term;
//$BookingInvoice->bank_id = '4';
$BookingInvoice->reservation_id = $request->iClose;
$BookingInvoice->created_by = Auth::user()->id;
$BookingInvoice->comments = "• This rate Includes Fueling
• Please note that our working period is between 7am –6pm.
• Normal Overtime Rate (7pm – 12 midnight) ----------------N500 per hour
• Abnormal Overtime Rate (12 midnight—6 am) ------------N1000 per hour
• Weekend Allowance-----------------------------------------------N1, 500 per day
• Public holiday allowance-------------------------------------------N2,500 per day • Travel Allowance------------------------------------------------------N2,500 per day
• Outstation allowance------------------------------------------------N5,000 per night
• Toll – pass is excluded";
//$BookingInvoice->bank_details = $request->bankDetails;
$BookingInvoice->auditTrail = "| Profoma Created By '" . Auth::user()->name . "' at '" . date('Y-m-d') . "' ";
$matchThese = ['reservation_id' => $request->iClose];
$contactperson = Reservation::where($matchThese)->value('contact_name');
$BookingInvoice->contact_person = $contactperson;
$BookingInvoice->invoice_status = "9";
$BookingInvoice->save();
$lastID = $BookingInvoice->id;
$bookingInvoiceItem = Trip::where("reservation_id", $request->iClose)->get();
//$additionalCost = AdditionalServiceTrip::where("reservation_id", $request->iClose)->sum("total_cost");
// AdditionalServiceTrip::where("tripID", $get->id)->value("total_cost")
if ($bookingInvoiceItem) {
foreach ($bookingInvoiceItem as $get) {
$answer = BookingInvoiceItem::create([
'booking_invoice_item_id' => $lastID,
'reservation_id' => $get->reservation_id,
'cid' => $get->cid,
'startDate' => $get->pick_up_date,
'description' => ucwords(str_replace("_", " ", $get->service_type)) . " for " . $get->passenger_names . " from " . $get->pickup_street . " to " . $get->actual_street . " from " . $get->pick_up_date . " to " . $get->end_date . " Vehicle: " . $get->vehicle_make,
'unit_cost' => $get->total_cost + AdditionalServiceTrip::where("trip_id", $get->id)->value("total_cost"),
//'vat' => $get->vat,
'qty' => '1',
'tripID' => $get->id,
'logged_by' => Auth::user()->id,
'net_value' => $get->total_cost + AdditionalServiceTrip::where("trip_id", $get->id)->value("total_cost"),
]);
}
} // End of if($bookingInvoiceItem){
$updateReservation = "UPDATE `reservations` SET `task_status` = 'invoice_pending', `invoice_id` = '$lastID' WHERE `reservation_id` = '" . $request->iClose . "' ";
$dR = collect(DB::update(DB::raw($updateReservation)));
$tripArray = ["job_status" => 2];
$closeTrip = DB::statement("UPDATE `trips` SET `job_status` = '2' WHERE `reservation_id` = '" . $request->iClose . "' AND `trip_status` = 'confirmed' ");
$iDate = date('Y-m-d H:i:s');
$auditTrail = " |
| Trip Closed By " . Auth::user()->name . " on $iDate |
";
$auditTrail2 = "| Trip Closed By " . Auth::user()->name . " on $iDate |
";
$auditUpdate = DB::statement("UPDATE `trips` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$auditTrail' ELSE CONCAT(`auditTrail`, '$auditTrail') END WHERE `reservation_id` = '" . $request->iClose . "' ");
$auditUpdate2 = DB::statement("UPDATE `trips` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$auditTrail2' ELSE CONCAT(`auditTrail`, '$auditTrail2') END WHERE `reservation_id` = '" . $request->iClose . "' ");
$json = ["status" => 200];
} else {
$json = ["status" => 400, "status" => $task_status];
}
return response()->json($json);
}
public function canceltrip(Request $request) {
$json = [];
$checkStatus = Trip::where("id", $request->id)->value("trip_status");
$jobStatus = Trip::where("id", $request->id)->value("job_status");
$reservation_id = Trip::where("id", $request->id)->value("reservation_id");
$res_task_status = Reservation::where("reservation_id", $reservation_id)->value("task_status");
/* if($res_task_status == "profoma_sent"){
$json = ["status" => 400, "status" =>$checkStatus];
}else
*
*/
if ($res_task_status == "paid") {
$json = ["status" => 400, "status" => $checkStatus];
} else if ($jobStatus != '0') {
$json = ["status" => 400, "status" => $checkStatus];
} else {
$tripArray = ["trip_status" => "cancelled"];
$closeTrip = Trip::where("id", $request->id)->update($tripArray);
//Delete the trip in profoma
$deleteinprofoma = BookingInvoiceItem::where("tripID", $request->id)->forcedelete();
$iDate = date('Y-m-d H:i:s');
$auditTrail = "| Trip Cancelled By " . Auth::user()->name . " on $iDate |
";
$auditUpdate = DB::statement("UPDATE `trips` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$auditTrail' ELSE CONCAT(`auditTrail`, '$auditTrail') END WHERE `id` = '" . $request->id . "' ");
$json = ["status" => 200];
}
return response()->json($json);
}
public function ideleteTrip(Request $request) {
$json = [];
$accessType = Auth::user()->trip_access_type;
$checkStatus = Trip::where("id", $request->id)->value("trip_status");
$logged_by = Trip::where("id", $request->id)->value("logged_by");
if ($checkStatus == "submitted" || $checkStatus == "pending" && $request->id && ($logged_by == auth()->user()->id)) {
$deleteAdd = AdditionalServiceTrip::where("trip_id", $request->id)->forcedelete();
$idelete = Trip::where("id", $request->id)->delete();
$json = ["status" => 200];
} else {
$json = ["status" => 400, "msg" => $checkStatus];
}
return response()->json($json);
}
public function deleteReservation(Request $request) {
$json = [];
$accessType = Auth::user()->trip_access_type;
//if(isset($request->id) && ($accessType == "1" || $accessType == "3")){ }
$checkStatus = Reservation::where("reservation_id", $request->iDelete)->value("task_status");
$logged_by = Trip::where("reservation_id", $request->iDelete)->value("logged_by");
if ($checkStatus == "submitted") {
$deleteAdd = AdditionalServiceTrip::where("reservation_id", $request->iDelete)->forcedelete();
$removeallTrips = Trip::where("reservation_id", $request->iDelete)->delete();
$deleteReservation = Reservation::where("reservation_id", $request->iDelete)->delete();
$route = request()->route()->getAction('controller');
/*
$mssage = "Reservation Deleted ". $request->iDelete;
$b = new MailException();
$success = $b::sendErrorMsg("oladejo.lasisi@c-ileasing.com", "oladejo.lasisi@c-ileasing.com",
"david.olushola@c-ileasing.com", $route, $mssage);
*
*/
$json = ["status" => 200];
} else if ($checkStatus == "confirmed") {
$array = [
'task_status' => 'cancelled',
'auditTrail' => 'Canceled by ' . Auth::user()->id . ' ' . date('Y-m-d H:i:s'),
];
$deleteReservation = Reservation::where("reservation_id", $request->iDelete)->update($array);
$array2 = [
'trip_status' => 'cancelled',
'task_status' => 2,
'auditTrail' => 'Canceled by ' . Auth::user()->id . ' ' . date('Y-m-d H:i:s'),
];
$removeallTrips = Trip::where("reservation_id", $request->iDelete)->update($array2);
$json = ["status" => 200];
} else {
$json = ["status" => 400, "msg" => $checkStatus];
}
return response()->json($json);
}
public function sendmailtoclient($id) {
$data = [];
$allTrips = "";
$url = "SEND EMAIL TO CLIENT";
$userID = Auth::user()->id;
$allTrips = Trip::where("id", $id)->get()->first();
$data = ['url' => $url, "id" => $id, "trips" => $allTrips];
return view('trips/sendmailtoclient', $data);
}
public function checkansendmail(Request $request) {
$json = [];
if (isset($_POST[$request->client_email])) {
$clientEmail = $request->client_email;
$tripid = $request->tripid;
$cc = $request->cc;
$subject = $request->subject;
$service_type = $request->service_type;
$pick_up_date = $request->pick_up_date;
$pickup_time = $request->pickup_time;
$end_date = $request->end_date;
$end_time = $request->end_time;
$pickup_street = $request->pickup_street;
$actual_street = $request->actual_street;
$driver = $request->driver;
$passenger_names = $request->passenger_names;
$data = ['emailto' => $clientEmail, 'pname' => $passenger_names, 'emailcc' => $cc, 'tripid' => $tripid,
'subject' => $subject, 'service_type' => $service_type, 'pick_up_date' => $pick_up_date
, 'pickup_time' => $pickup_time, 'end_date' => $end_date, 'end_time' => $end_time
, 'pickup_street' => $pickup_street, 'actual_street' => $actual_street, 'driver' => $driver
];
Mail::send(['html' => 'email.toclient'], $data, function ($message) use($data) {
$message->from('info@sys.c-ileasing.com', 'HERTZ NIGERIA');
$message->to($data->emailto)->cc($data->emailcc);
//$message->bcc("oladejo.lasisi@c-ileasing.com");
$message->subject("LOGISTICS DETAILS FOR TRIP");
$message->sender("ADMIN", Auth::user()->name);
$message->getSwiftMessage();
});
$json = ["status" => 200];
}
return response()->json($json);
}
public function tripdetails($id) {
$data = [];
$allTrips = "";
$pName = strtoupper(Trip::where("id", $id)->value("passenger_names"));
$url = "TRIP DETAIL FOR $pName";
$allTrips = Trip::where("id", $id)->get()->first();
$addService = AdditionalServiceTrip::where("trip_id", $id)->get();
$data = ['url' => $url, "id" => $id, "trips" => $allTrips, "addService" => $addService];
return view('trips/trip_details', $data);
}
public function profomaInvoice($id) {
$data = [];
$invoiceExist = "";
$template = "";
//Check if invoice Exist
$bookingInvoice = BookingInvoice::where("reservation_id", $id)->value("id");
if ($bookingInvoice) {
$invoiceExist = $bookingInvoice;
$template = BookingInvoice::where("id", $bookingInvoice)->value("invoice_template");
}
$allTrips = "";
$trips = Trip::where(["reservation_id" => $id, "trip_status" => "confirmed"])->get();
$url = "CREATE PROFOMA INVOICE";
$reservation = Reservation::where("reservation_id", $id)->get()->first();
$bank = Bank::all();
$data = ['url' => $url, "invoiceExist" => $invoiceExist, "template" => $template, "dBanks" => $bank, "id" => $id, "trips" => $trips, "reservation" => $reservation];
return view('trips/profoma', $data);
}
public function viewinvoice(Request $request, $id) {
$loggeby = "false";
if ($request->input('view') === "true") {
$data = [];
$QueryString = $request->input('view');
$allTrips = "";
//$trips = Trip::where(["reservation_id"=> $id, "trip_status" => "confirmed"])->get();
$trips = BookingInvoiceItem::where(["reservation_id" => $id])->get();
$url = "VIEW INVOICE";
//$reservation = Reservation::where("reservation_id", $id)->get()->first();
$bookingInvoice = BookingInvoice::where("reservation_id", $id)->get()->first();
$bank = Bank::all();
$accessType = Auth::user()->trip_access_type;
$createdBy = BookingInvoice::where(["reservation_id" => $id])->value("created_by");
$invoiceTotalSum = BookingInvoiceItem::where(["reservation_id" => $id])->sum('net_value');
if ($createdBy == Auth::user()->id) {
$loggeby = "true";
}
$data = ['url' => $url, 'invoiceSum' => $invoiceTotalSum, 'accessType' => $accessType, 'loggeby' => $loggeby, 'createdBy' => $createdBy, 'QueryString' => $QueryString, "dBanks" => $bank, "id" => $id, "trips" => $trips, "bookingInvoice" => $bookingInvoice];
return view('trips/final_invoice', $data);
} else {
echo "not valid, please contact administrator";
}
}
public function create_profoma(Request $request) {
$json = [];
//dd($request);
if (isset($request->reservationID) && isset($request->grandTotalID)) {
// dd($request->grandTotalID);
$invoiceStatus = BookingInvoice::where("reservation_id", $request->reservationID)->value('invoice_status');
if ($invoiceStatus && ($invoiceStatus != "5")) {
$json = ['status' => 1, 'msg' => "You cannot create that invoice. Plase check invoice Status and ensure previous invoice has either been approved or cancelled"];
} else {
//reservation_created_at
$BookingInvoice = new BookingInvoice;
$BookingInvoice->reservation_date = $request->reservationDate;
$BookingInvoice->reservation_created_at = $request->reservationDate;
$BookingInvoice->invoice_date = date('Y-m-d H:i:s');
$BookingInvoice->cid = $request->cid;
$BookingInvoice->office = $request->office;
$BookingInvoice->client_type = $request->client_type;
$BookingInvoice->credit_type = $request->credit_type;
$BookingInvoice->contact_name = $request->contact_name;
$BookingInvoice->contact_email = $request->contact_email;
$BookingInvoice->phone_number = $request->contact_phone_number;
$BookingInvoice->purchase_order = $request->purchase_order;
$BookingInvoice->invoice_template = $request->template;
$BookingInvoice->tin_number = $request->tin_number;
$BookingInvoice->vat_number = $request->vat_number;
$BookingInvoice->payment_term = $request->payment_term;
$BookingInvoice->bank_id = $request->getBanks;
$BookingInvoice->reservation_id = $request->reservationID;
$BookingInvoice->created_by = Auth::user()->id;
$BookingInvoice->comments = $request->terms;
$BookingInvoice->bank_details = $request->bankDetails;
$BookingInvoice->auditTrail = "| Profoma Created By '" . Auth::user()->name . "' at '" . date('Y-m-d') . "' ";
$matchThese = ['reservation_id' => $request->reservationID];
$contactperson = Reservation::where($matchThese)->value('contact_name');
$BookingInvoice->contact_person = $contactperson;
$BookingInvoice->invoice_status = "8";
$BookingInvoice->save();
$lastID = $BookingInvoice->id;
if (isset($request->amount) && isset($request->totalCost)) {
$sum = 0;
for ($i = 0; $i < count($request->amount); $i++) {
$answer = BookingInvoiceItem::create([
'booking_invoice_item_id' => $lastID,
'reservation_id' => $request->reservationID,
'cid' => $request->cleint_ID,
'startDate' => $request->startDate[$i],
'description' => $request->textDescription[$i],
'unit_cost' => $request->amount[$i],
'vat' => $request->vatable[$i],
'qty' => $request->iqty[$i],
'tripID' => $request->tripID[$i],
'logged_by' => Auth::user()->id,
'net_value' => (int) $request->amount[$i] + (int) $request->vatable[$i],
]);
}
$updateReservation = "UPDATE `reservations` SET `task_status` = 'profoma_sent', `invoice_id` = '$lastID' WHERE `reservation_id` = '" . $request->reservationID . "' ";
$dR = collect(DB::update(DB::raw($updateReservation)));
if ($request->checkID) {
$checkID = $request->checkID;
for ($i = 0; $i < count($checkID); $i++) {
// $matchThese = ['id' => $request->checkID[$i]];
$updateTrips = "UPDATE `trips` SET `task_status` = '4', `resource_type` = 'profoma_sent' WHERE `id` = '" . $request->checkID[$i] . "' ";
$dR = collect(DB::update(DB::raw($updateTrips)));
}
}
$extraStore = "INSERT INTO booking_store_values SET amount = '" . $request->totalUnitcostID . "', "
. "vat = '" . $request->totalVatID . "', netValue = '" . $request->grandTotalID . "' , "
. "invoice_id = '" . $lastID . "' , cid = '" . $request->cleint_ID . "' ";
$d = collect(DB::insert(DB::raw($extraStore)));
}
$pdf = "";
//Send mail to all ICU team both in Lagos and PH and Abuja
$accessType = Auth::user()->trip_access_type;
$bookingInvoiceItem = BookingInvoiceItem::where("booking_invoice_item_id", $lastID)->get();
$bookingsInvoice = BookingInvoice::where("id", $lastID)->get()->first();
$idata = ["booking" => $bookingsInvoice, "useraccess" => $accessType, "bookingItem" => $bookingInvoiceItem];
if ($request->template == "hertz") {
$pdf = PDF::loadView('trips.invoice-hertz', $idata);
} else if ($request->template == "plain") {
$pdf = PDF::loadView('trips.invoice-download-plain', $idata);
} else {
$pdf = PDF::loadView('trips.invoice-download-plain', $idata);
}
$totalSum = BookingInvoiceItem::where("booking_invoice_item_id", $lastID)->sum("net_value");
$userName = User::where("id", Auth::user()->id)->value("name");
$data = ['office' => $request->office, 'userName' => $userName, 'cName' => $request->contact_name, 'resID' => $request->reservationID,
'type' => $request->credit_type, 'cid' => Companies::where("companies_id", $request->cid)->value("company_name"), 'total_sum' => $totalSum];
// $message->sender("ADMIN", "ADMIN");
/* Mail::send(['html' => 'email.toicu'], $data, function ($message) use($data, $pdf) {
$message->from('info@sys.c-ileasing.com', 'HERTZ NIGERIA');
$message->to("vincent.abu@c-ileasing.com")->cc("nkiruka.enwenam@c-ileasing.com");
$message->bcc("oladejo.lasisi@c-ileasing.com");
$message->subject("PROFOMA INVOICE CREATED");
$message->attachData($pdf->output(), "invoice.pdf");
$message->getSwiftMessage();
});
*
*/
$json = ['status' => 2, 'msg' => "Invoice Successfully Generated!", "bookingID" => $lastID];
}
} else {
$json = ['status' => 3, 'msg' => "Error process page please try again!"];
}
return response()->json($json);
}
///////////////////////////////////////// FINAL INVOICE FOR PROFOMA /////////////////////////////////////////////
public function finalInvoice(Request $request) {
$json = [];
$date = date("Y-m-d");
// dd($request);
if (isset($request->bookingInoviceID) && isset($request->grandTotalID)) {
$invoiceStatus = BookingInvoice::where("id", $request->bookingInoviceID)->value('invoice_status');
if ($invoiceStatus && (($invoiceStatus == "4") || ($invoiceStatus == "5"))) {
$json = ['status' => 1, 'msg' => "You cannot update that invoice. Plase check invoice Status and ensure previous invoice has either been approved or cancelled"];
} else {
$updateArray = [
"contact_name" => $request->contact_name,
"contact_email" => $request->contact_email,
"phone_number" => $request->contact_phone_number,
"purchase_order" => $request->purchase_order,
"invoice_template" => $request->template,
"tin_number" => $request->tin_number,
"vat_number" => $request->vat_number,
//"payment_term" => $request->payment_term,
"bank_id" => $request->getBanks,
"comments" => $request->terms,
//"invoice_status" => isset($request->payments) ? $request->payments : 0,
//"payment_date" => isset($request->paymentDate) ? $request->paymentDate : $date,
// "evidence_payment_text" => isset($request->evidence_payment_text) ? $request->evidence_payment_text : "0",
"bank_details" => $request->bankDetails
];
$doUpdate = BookingInvoice::where("id", $request->bookingInoviceID)->update($updateArray);
$iDate = date('Y-m-d H:i:s');
$auditTrail = " |
| Invoice Updated By " . Auth::user()->name . " on $iDate |
";
$auditUpdate = DB::statement("UPDATE `booking_invoice` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$auditTrail' ELSE CONCAT(`auditTrail`, '$auditTrail') END WHERE `id` = '" . $request->bookingInoviceID . "' ");
$accessType = Auth::user()->trip_access_type;
if ($accessType === 2) {
$updateReservationStatus = DB::statement("UPDATE `reservations` SET `task_status` = 'payment_pending' WHERE `reservation_id` = '" . $request->reservationID . "' ");
$paid = DB::statement("UPDATE `booking_invoice` SET `invoice_status` = '10' WHERE `id` = '" . $request->bookingInoviceID . "' ");
}
//Update Trip
$trips_paid = DB::statement("UPDATE `trips` SET `job_status` = 2 WHERE `reservation_id` = '" . $request->reservationID . "' AND `trip_status` = 'confirmed' ");
/* if($request->payments == '4'){
$paid = DB::statement("UPDATE `booking_invoice` SET `paid_by` = '".Auth::user()->id."' WHERE `id` = '" . $request->bookingInoviceID . "' ");
$iauditTrail = "| Invoice Approved By ".Auth::user()->name." on $iDate |
";
$auditUpdate = DB::statement("UPDATE `booking_invoice` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$iauditTrail' ELSE CONCAT(`auditTrail`, '$iauditTrail') END WHERE `id` = '" . $request->bookingInoviceID . "' ");
//Update Reservation
$ir_paid = DB::statement("UPDATE `reservations` SET `task_status` = 'paid', `confirm_payment` = '".Auth::user()->name."' , `confirmed_by` = '".Auth::user()->id."' WHERE `reservation_id` = '".$request->reservationID."' ");
//Update Trip
$trips_paid = DB::statement("UPDATE `trips` SET `paid_by` = ".Auth::user()->id." WHERE `reservation_id` = '" . $request->reservationID . "' AND `trip_status` = 'confirmed' ");
//Addd Audit Trails to trips
$iiauditTrail = "| Invoice Approved By ".Auth::user()->name." on $iDate with Invoice ID of $request->bookingInoviceID |
";
$iauditUpdate = DB::statement("UPDATE `trips` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$iiauditTrail' ELSE CONCAT(`auditTrail`, '$iiauditTrail') END WHERE `reservation_id` = '" . $request->reservationID . "' AND `trip_status` = 'confirmed' ");
}
if($request->payments == '5'){
$paid = DB::statement("UPDATE `booking_invoice` SET `cancelled_by` = '".Auth::user()->id."' WHERE `id` = '" . $request->bookingInoviceID . "' ");
$iauditTrail = "| Invoice Cancelled By ".Auth::user()->name." on $iDate |
";
$auditUpdate = DB::statement("UPDATE `booking_invoice` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$iauditTrail' ELSE CONCAT(`auditTrail`, '$iauditTrail') END WHERE `id` = '" . $request->bookingInoviceID . "' ");
//Update Reservation
$ir_paid = DB::statement("UPDATE `reservations` SET `task_status` = 'cancelled', WHERE `reservation_id` = '".$request->reservationID."' ");
//Update Trip
$trips_paid = DB::statement("UPDATE `trips` SET `trip_status` = 'cancelled' WHERE `reservation_id` = '" . $request->reservationID . "' ");
//Addd Audit Trails to trips
$iiauditTrail = "| Invoice Cancelled By ".Auth::user()->name." on $iDate with Invoice ID of $request->bookingInoviceID |
";
$iauditUpdate = DB::statement("UPDATE `trips` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$iiauditTrail' ELSE CONCAT(`auditTrail`, '$iiauditTrail') END WHERE `reservation_id` = '" . $request->reservationID . "' ");
}
*/
if (isset($request->invoiceID)) {
$sum = 0;
for ($i = 0; $i < count($request->invoiceID); $i++) {
$invoiceid_array = [
'description' => $request->textDescription[$i],
'unit_cost' => $request->amount[$i],
'vat' => $request->vatable[$i],
'qty' => $request->iqty[$i],
'net_value' => ((int) $request->amount[$i] * (int) $request->iqty[$i]) + (int) $request->vatable[$i],
];
$doBookingInvoiceItems = BookingInvoiceItem::where("invoice_id", $request->invoiceID[$i])->update($invoiceid_array);
}
}
if (isset($request->addedId)) {
// $sum = 0;
for ($j = 0; $j < count($request->addedId); $j++) {
// print_r($request->addedId[$j]);
$ianswer = BookingInvoiceItem::create([
'booking_invoice_item_id' => $request->bookingInoviceID,
'reservation_id' => $request->reservationID,
'cid' => $request->cleint_ID,
'startDate' => $request->istartDate[$j],
'description' => $request->itextDescription[$j],
'unit_cost' => $request->iamount[$j],
'qty' => $request->iiqty[$j],
'vat' => $request->ivatable[$j],
'logged_by' => Auth::user()->id,
'net_value' => ((int) $request->iamount[$j] * (int) $request->iiqty[$j]) + (int) $request->ivatable[$j],
]);
}
}
// dd($request->evidencefile);
//Update files to server
/*
if(isset($request->evidencefile)){
$this->validate($request, [
'evidencefile' => 'required|image|mimes:jpeg,png,jpg,gif,svg|max:2048',
]);
if ($request->hasFile('evidencefile')) {
$image = $request->file('evidencefile');
$name = rand(100, 100000).time().'.'.$image->getClientOriginalExtension();
$destinationPath = public_path('payment_evidence');
$image->move($destinationPath, $name);
$array = [
"evidence_payment_image" => $name
];
$update = BookingInvoice::where("id", $request->bookingInoviceID)->update($array);
}
}
*/
$json = ['status' => 2, 'msg' => "Invoice Successfully Updated!"];
}
} else {
$json = ['status' => 3, 'msg' => "Error process page please try again!"];
}
return response()->json($json);
}
////////////////////////////////////////// END OF FINAL INVOICE FOR PROFOMA /////////////////////////////////////
public function downloadInvoice($id, $template) {
if ($id && $template) {
$bookingsInvoice = BookingInvoice::where("id", $id)->get()->first();
$bookingInvoiceItem = BookingInvoiceItem::where("booking_invoice_item_id", $id)->get();
/* $bookingInvoiceItemWithTrip = BookingInvoiceItem::where("booking_invoice_item_id", $id)
-where("tripID", "!=", "0")->get();
dd($bookingInvoiceItemWithTrip);
$bookingInvoiceItemWithoutTrip = BookingInvoiceItem::where("booking_invoice_item_id", $id)
->where("tripID", "0")->get();
*/
$accessType = Auth::user()->trip_access_type;
$data = ["booking" => $bookingsInvoice, "useraccess" => $accessType, "bookingItem" => $bookingInvoiceItem];
if ($template == "hertz") {
//return view('trips.invoice-hertz', $data);
$pdf = PDF::loadView('trips.invoice-hertz', $data);
} else if ($template == "plain") {
$pdf = PDF::loadView('trips.invoice-download-plain', $data);
} else {
$pdf = PDF::loadView('trips.invoice-download-plain', $data);
}
return $pdf->download('invoice' . rand(11111, 99999) . time() . '.pdf');
}else{
return 'No template was selected';
}
}
public function gatepass($id) {
$accessType = Auth::user()->trip_access_type;
if (($accessType == "1" || $accessType == "3") && $id) {
$trips = Trip::where("id", $id)->get();
$data = ["gatepass" => $trips];
$pdf = PDF::loadView('trips.gate-pass', $data);
$time = rand(10, 30) . time();
return $pdf->download('gate_pass"' . $time . '".pdf');
} else {
echo "You do not have access to download gate pass, Only Reservation can download";
}
}
public function paid_invoice() {
$url = "PAID INVOICES";
$userID = Auth::user()->id;
$getCompanyAccess = User::where("id", $userID)->value('company_access');
$paidInvoice = BookingInvoice::where("invoice_status", "4")->paginate(40);
$data = ['url' => $url, "paidInvoice" => $paidInvoice];
return view('trips/paidInvoices', $data);
}
public function loadaddservice() {
$json = AdditionalService::all();
return response()->json($json);
}
public function additionalService(Request $request) {
$json = [];
if (isset($request->id)) {
$reservationID = Trip::where("id", $request->id)->value("reservation_id");
$cid = Trip::where("id", $request->id)->value("cid");
$service = new AdditionalServiceTrip;
$service->reservation_id = $reservationID;
$service->cid = $cid;
$service->trip_id = $request->id;
$service->service = $request->addService;
$service->service_cost = $request->cost;
$service->quantity = $request->qty;
$service->total_cost = $request->qty * $request->cost;
//$service->created_by = Auth::user()->id;
$saveRequest = $service->save();
if ($saveRequest) {
$json = ["status" => 200];
} else {
$json = ["status" => 400];
}
} else {
$json = ["status" => 500];
}
return response()->json($json);
}
public function uploadDriver(Request $request) {
if (isset($request->id) && isset($request->uploadpix)) {
$this->validate($request, [
'uploadpix' => 'required|image|mimes:jpeg,png,jpg,gif,svg|max:2048',
]);
if ($request->hasFile('uploadpix')) {
$image = $request->file('uploadpix');
$name = rand(100, 100000) . time() . '.' . $image->getClientOriginalExtension();
$destinationPath = public_path('images/driver_image');
$image->move($destinationPath, $name);
$array = [
"driver_picture" => $name
];
$update = Drivers::where("driver_id", $request->id)->update($array);
if ($update) {
return response()->json([
"status" => 200,
"msg" => "Booking Saved"
], 200);
} else {
return response()->json([
"status" => 400,
"msg" => "Error Uploading Photo"
], 200);
}
}
} // End of if(isset($request->id)){
}
// uploadDriver(Request $request){
public function listofinvoices() {
$date = date('m');
//BD = 0, Reservation = 1, Accountant = 2, Admin = 3
$data = [];
$getCompanyAccess = [];
$allTrips = "";
$accessType = Auth::user()->trip_access_type;
if ($accessType == 2 || $accessType == 3) {
$url = "LIST OF INVOICES";
} else {
$url = "ALL INVOICES";
}
$userID = Auth::user()->id;
$office = Office::all();
$company = Companies::all();
/////////////////////////////////////RAW SQL /////////////////////////////////////////////
$yearOffice = "SELECT count(*) AS totalReservation, office, GROUP_CONCAT(reservation_id) AS rids, YEAR(reservation_date) YEAR FROM reservations WHERE task_status IN ('paid', 'profoma_sent', 'confirmed') GROUP BY YEAR(reservation_date), office";
$toYearOffice = collect(DB::select(DB::raw($yearOffice)));
/////////////////////////////////////RAW SQL ////////////////////////////////////////////
//////////////////////////////////// GRAPH FOR INVOICE THIS MONTH ///////////////////////////////////////////
$paid_this_month = "SELECT id, reservation_date, created_at, office, cid, contact_person, reservation_id, count(reservation_id) as total, invoice_status, GROUP_CONCAT(reservation_id) as total_invoice_count FROM `booking_invoice` WHERE MONTH(created_at) = '02' AND invoice_status = '4' group by office";
$paid_this_month = collect(DB::select(DB::raw($paid_this_month)));
/////////////////////////////////// END OF GRAPH FOR INVOICE THIS MONTH /////////////////////////////////
if ($accessType == 2 || $accessType == 3 || $accessType == 4) {
$allInvoices = Reservation::whereNotIn("task_status", ["submitted", "draft", "profoma_sent", "confirmed"])->orderBy("reservation_id", "DESC")->paginate(30);
$allInvoices_grouped = "SELECT GROUP_CONCAT(reservation_id) as mainids, COUNT(reservation_id) as total, cid, office, contact_name, contact_fullname, task_status FROM `reservations` WHERE task_status IN('payment_pending') AND cid NOT IN (96) GROUP BY cid ORDER BY office";
$allInvoices_grouped = collect(DB::select(DB::raw($allInvoices_grouped)));
} else {
echo "You do not have access to view invoice";
return;
$allInvoices = Reservation::orderBy("reservation_id", "DESC")->paginate(50);
$allInvoices_grouped = "";
}
$data = ['url' => $url, "toYearOffice" => $toYearOffice, "paid_this_month" =>$paid_this_month, "allInvoices_grouped" =>$allInvoices_grouped, "accessType" => $accessType, "invoices" => $allInvoices, "office" => $office, "companies" => $company];
return view('trips/allinvoices', $data);
}
public function earningsGraph($month="") {
//set the year of expenses to show
$moth_to_fetch = $month ? $month : date('m');
//$earnings = $this->genmod->getYearEarnings($year_to_fetch);
$paid_this_month = "SELECT reservation_date, created_at, payment_date, office, count(reservation_id) as total, invoice_status FROM `booking_invoice` WHERE MONTH(reservation_date) = '$moth_to_fetch' AND invoice_status = '4' group by office";
$paid_this_month = collect(DB::select(DB::raw($paid_this_month)));
$lastEarnings = 0;
$monthEarnings = array();
$hightEarn['highestEarning'] = 0;
$dataarr = [];
//$allMonths = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
$allLocations = ["1", "2", "3", "4", "5", "6", "7", "8", "9"];
if($paid_this_month){
foreach ($allLocations as $locations) {
foreach ($paid_this_month as $get) {
// $earningMonth = date("M", strtotime($get->payment_date));
$office = $get->office;
if ($locations == $office) {
$lastEarnings += $get->total;
$monthEarnings[$locations] = $lastEarnings;
} else{
if (!array_key_exists($locations, $monthEarnings)) {
$monthEarnings[$locations] = 0;
}
}
}
if ($lastEarnings > $hightEarn['highestEarning']) {
$hightEarn['highestEarning'] = $lastEarnings;
}
$lastEarnings = 0;
}
foreach ($monthEarnings as $me) {
$dataarr[] = $me;
}
}else{
foreach ($locations as $locations) {
$dataarr[] = 0;
}
}
//add info into array
$json = array("total_earnings" => $dataarr, 'earningsYear'=>$moth_to_fetch);
return response()->json($json);
}
public function invoicesearch(Request $request) {
$url = "SEARCHING INVOICE";
$data = [];
$office = Office::all();
$company = Companies::all();
if ($request->reservation_id == "" && $request->reservation_date == "" && $request->task_status == "" &&
$request->office == "" && $request->cid == "" && $request->invoice_id == "") {
return $this->listofinvoices();
}
/* else if($secExp[0] === 'invoice_id'){
$expsql .= (trim($secExp[1]) != '') ? '`'.$secExp[0] .'` = "' .$secExp[1]. '" AND ' : '';
}
*
*/
$date = date('m');
$paid_this_month = "SELECT id, reservation_date, created_at, office, cid, contact_person, reservation_id, count(reservation_id) as total, invoice_status, GROUP_CONCAT(reservation_id) as total_invoice_count FROM `booking_invoice` WHERE MONTH(created_at) = '$date' AND invoice_status = '4' group by office";
$paid_this_month = collect(DB::select(DB::raw($paid_this_month)));
if (isset($_SERVER['QUERY_STRING']) && trim($_SERVER['QUERY_STRING']) != '') {
$expsql = '';
$firstExplode = explode('&', $_SERVER['QUERY_STRING']);
foreach ($firstExplode as $expItem) {
$secExp = explode('=', trim($expItem));
if (isset($secExp[1]) && trim($secExp[0]) != 'url' && trim($secExp[0]) != 'modroute' && trim($secExp[0]) != '_token') {
//$explode_exp1 = explode('_', trim($secExp[0]));
if ($secExp[0] === 'reservation_date') {
$expsql .= (trim($secExp[1]) != '') ? 'DATE(`' . substr($secExp[0], 0) . '`) >= ' . '"' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
} elseif ($secExp[0] === 'created_at') {
$expsql .= (trim($secExp[1]) != '') ? 'DATE(`' . substr($secExp[0], 0) . '`) <= ' . '"' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
} else if ($secExp[0] === 'office') {
$expsql .= (trim($secExp[1]) != '') ? '`' . $secExp[0] . '` = "' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
} else if ($secExp[0] === 'cid') {
$expsql .= (trim($secExp[1]) != '') ? '`' . $secExp[0] . '` = "' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
} else
$expsql .= (trim($secExp[1]) != '') ? '`' . $secExp[0] . '` like ' . '"%' . str_replace('+', ' ', $secExp[1]) . '%" AND ' : '';
}else {
$expsql .= "";
}
}
$formatexpsql = substr($expsql, 0, -4);
//$formatexpsql = trim($formatexpsql) != '' ? $formatexpsql.' AND ': '';
$allInvoices = DB::table("reservations")->whereRaw(DB::raw($formatexpsql))->get();
//$allTrips = Trip::where($formatexpsql)->paginate(100);
} else {
$allInvoices = '';
}
///////////////////////////////////////////// RAW SQL FOR YEAR //////////////////////////////////////
$yearOffice = "SELECT count(*) AS totalReservation, GROUP_CONCAT(reservation_id) AS rids, office, YEAR(reservation_date) YEAR FROM reservations WHERE task_status IN ('paid', 'proforma_sent', 'confirmed') GROUP BY YEAR(reservation_date), office";
$toYearOffice = collect(DB::select(DB::raw($yearOffice)));
$accessType = Auth::user()->trip_access_type;
$allInvoices_grouped = "SELECT GROUP_CONCAT(reservation_id) as mainids, COUNT(reservation_id) as total, cid, office, contact_name, contact_fullname, task_status FROM `reservations` WHERE task_status IN('payment_pending') AND cid NOT IN (96) GROUP BY cid ORDER BY office";
$allInvoices_grouped = collect(DB::select(DB::raw($allInvoices_grouped)));
if ($allInvoices) {
$data = ['url' => $url, "invoices" => $allInvoices, "paid_this_month" =>$paid_this_month, "allInvoices_grouped" =>$allInvoices_grouped, "accessType" => $accessType, "toYearOffice" => $toYearOffice, "office" => $office, "companies" => $company];
return view('trips/allinvoices', $data);
}
}
public function yearmonth($office, $year, $rids) {
$url = "INVOICE SUMMARY";
$data = [];
if ($office && $year && $rids) {
// $q = "SELECT count(*) AS RequestCount, SUM(dAmount) AS TotalSUM, YEAR(dateCreated) YEAR, MONTH(dateCreated) Month from cash_newrequestdb where extract(YEAR from dateCreated) = $year AND dCashierwhopaid != '' AND approvals IN('4', '8') AND `CurrencyType` IN ('naira', 'NGN', '') GROUP BY YEAR(dateCreated), MONTH(dateCreated)";
$yeartoMonth = "SELECT count(*) AS totalReservation, GROUP_CONCAT(reservation_id) AS rids, YEAR(reservation_date) YEAR, MONTH(reservation_date) Month FROM reservations WHERE extract(YEAR from reservation_date) = '$year' AND office = '$office' AND reservation_id IN ($rids)GROUP BY YEAR(reservation_date), MONTH(reservation_date)";
$toYearMonthOffice = collect(DB::select(DB::raw($yeartoMonth)));
$data = ['url' => $url, "rids" => $rids, "toYearMonthOffice" => $toYearMonthOffice, "year" => $year, "office" => $office];
return view('trips/summaryofinvoices', $data);
} else {
echo "Important Variable to process report missing";
}
}
public function completedtrips(Request $request) {
$data = [];
$url = "COMPLETED TRIPS";
$accessType = Auth::user()->trip_access_type;
$office = Office::all();
$companies = Companies::where("comp_status", "Active")->get();
$allTrips = Trip::where("job_status", "2")->paginate(50);
$period = \App\Period::where("id", "1")->value('period');
$year = \App\Period::where("id", "1")->value('year');
// commented on 28/02/2022
// $monthendreview = "SELECT GROUP_CONCAT(id) AS id, cid, office, reservation_id, vehicle_id, pick_up_date, end_date, number_of_days, driver, vehicle_type, job_status, created_at, COUNT(vehicle_id) as occurence, SUM(number_of_days) as actual_days, MONTHNAME(pick_up_date) AS 'month_name', YEAR(pick_up_date) as YEAR FROM `trips` WHERE job_status = 2 AND MONTH(pick_up_date) = $period GROUP BY vehicle_id ORDER BY office, cid";
$search_month = $request->has('search_month') && $request->search_month != '' ? $request->search_month : null ;
$search_year = $request->has('search_year') && $request->search_year != '' ? $request->search_year : null ;
if($search_month && $search_year){
$monthendreview = "SELECT GROUP_CONCAT(id) AS id, cid, office, reservation_id, vehicle_id, pick_up_date, end_date, number_of_days, driver, vehicle_type, job_status, created_at, COUNT(vehicle_id) as occurence, SUM(number_of_days) as actual_days, MONTHNAME(pick_up_date) AS 'month_name', YEAR(pick_up_date) as YEAR FROM `trips` WHERE job_status = 2 AND MONTH(pick_up_date) = $search_month AND YEAR(pick_up_date) = $search_year GROUP BY vehicle_id ORDER BY office, cid";
}else{
$monthendreview = "SELECT GROUP_CONCAT(id) AS id, cid, office, reservation_id, vehicle_id, pick_up_date, end_date, number_of_days, driver, vehicle_type, job_status, created_at, COUNT(vehicle_id) as occurence, SUM(number_of_days) as actual_days, MONTHNAME(pick_up_date) AS 'month_name', YEAR(pick_up_date) as YEAR FROM `trips` WHERE job_status = 2 AND MONTH(pick_up_date) = $period AND YEAR(pick_up_date) = $year GROUP BY vehicle_id ORDER BY office, cid";
}
$monthendreview = collect(DB::select(DB::raw($monthendreview)));
//let's update the logic for actual work days
//==============================================
//first get all trips this month
// $trips = Trip::where('vehicle_id', $monthendreview[0]->vehicle_id)->get();
// $trips = $trips->filter(function($trip) use ($search_month, $search_year) {
// $date = Carbon::parse($trip->created_at);
// return $date->month == $search_month && $date->year == $search_year;
// });
// dd(Carbon::parse($trips[0]->created_at)->month);
foreach($monthendreview as $a_review){
$trips = Trip::whereIn('id', explode(',', $a_review->id))->get();
$daysSet = [];
//Then add trip days to a set
foreach($trips as $trip){
$days_array = $this->getTripDays($trip);
for($i = 0; $i < count($days_array); $i++){
if(count($daysSet) == 0){
$daysSet[] = $days_array[$i];
}else if(!in_array($days_array[$i], $daysSet)){
$daysSet[] = $days_array[$i];
}
}
}
//actual days is length of set
$a_review->actual_days = count($daysSet);
}
// dd($monthendreview);
$years = collect(DB::select(DB::raw('SELECT YEAR(pick_up_date) as year FROM trips GROUP BY year ORDER BY year DESC')))->pluck('year');
// dd($years);
$getUtilization = \App\AnayseUtilization::where(['month_val' => $period, 'YEAR' => $year])->get();
$utilizationTypes = \App\Utilizationtype::where("fleet", "1")->where('del', '0')->get();
$downtimeSummary = "SELECT office, cid, count(vehicle) as no_of_vehicles, SUM(expected_days_work) as expected_days_work, SUM(days_worked) as days_worked, SUM(downtime) as downtime_days, SUM(rate) as cumulative_rate, downtime_reason FROM `reservation_utilization_analysis` WHERE month_val = '$period' AND year = '$year' AND downtime_reason != '' GROUP BY office, downtime_reason";
$downtimeSummary = collect(DB::select(DB::raw($downtimeSummary)));
$data = ['years' => $years, 'url' => $url, "tripsbycid" => $allTrips, "period" => $period, "year"=>$year, "utilizationTypes" =>$utilizationTypes, "downtimeSummary" => $downtimeSummary, "getUtilization" =>$getUtilization, "monthendreview" => $monthendreview, "companies" => $companies, "office" => $office];
return view('trips/completed_trips', $data);
}
public function getTripDays($trip){
$tripDays = [];
if( Carbon::parse($trip->pick_up_date)->isSameDay(Carbon::parse($trip->end_date)) ){
$tripDays[] = Carbon::parse($trip->pick_up_date)->format('Y-m-d');
}else{
$aTripDay = Carbon::parse($trip->pick_up_date);
do{
$tripDays[] = $aTripDay->format('Y-m-d');
$aTripDay = $aTripDay->copy()->addDay();
}while(!$aTripDay->isSameDay(Carbon::parse($trip->end_date)));
$tripDays[] = $aTripDay->format('Y-m-d');
}
return $tripDays;
}
public function completedclosedtrips(Request $request) {
$allTrips = "";
$msg = "";
$url = "SEARCHING COMPLETED TRIPS";
$data = [];
$office = Office::all();
$companies = Companies::where("comp_status", "Active")->get();
$accessType = Auth::user()->trip_access_type;
if ($request->search == "" && $request->pickupdate == "" && $request->endate == "" && $request->office == "" && $request->cid == "") {
return redirect('get/completed_trips');
}
if ($accessType == '1') { //|| $accessType == '3'
if (isset($request->search)) {
$completedTrips = Trip::where('reservation_id', '=', $request->search)
->orWhere('passenger_names', 'LIKE', '%' . $request->search . '%')
->orWhere('vehicle_id', '=', $request->search)->paginate(30)
->setpath('');
$completedTrips->appends(array(
'q' => $request->search
));
} else if (isset($request->pickupdate) && isset($request->endate) && isset($request->office)) {
$allTrips = Trip::where('pick_up_date', '>=', $request->pickupdate)
->where("end_date", '<=', $request->endate)->where("office", $request->office)->paginate(50);
} else if (isset($request->pickupdate) && isset($request->endate) && isset($request->cid)) {
$allTrips = Trip::where('pick_up_date', '>=', $request->pickupdate)
->where("end_date", '<=', $request->endate)->where("cid", $request->cid)->paginate(50);
} else if (isset($request->pickupdate) && isset($request->endate)) {
$allTrips = Trip::where('pick_up_date', '>=', $request->pickupdate)->where("end_date", '<=', $request->endate)->paginate(50);
} else if (isset($request->office) && isset($request->cid)) {
$allTrips = Trip::where('office', $request->office)->where('cid', $request->cid)->paginate(50);
} else if (isset($request->office)) {
$allTrips = Trip::where('office', $request->office)
->paginate(50);
} else if (isset($request->cid)) {
$allTrips = Trip::where('cid', $request->cid)
->paginate(50);
}
if ($allTrips) {
$data = ['url' => $url, "tripsbycid" => $allTrips, "companies" => $companies, "office" => $office];
return view('trips/completed_trips', $data);
} else {
return $this->completedtrips();
// return redirect()
}
} else {
echo "You do not have access to close trip! Only Reservation Team can close trip. please see the reservation person in your unit";
}
}
public function deletetriprofoma(Request $request) {
if (isset($request->iDelete)) {
$json = [];
$checkStatus = Trip::where("id", $request->iDelete)->value("trip_status");
$jobStatus = Trip::where("id", $request->iDelete)->value("job_status");
$reservation_id = Trip::where("id", $request->iDelete)->value("reservation_id");
$res_task_status = Reservation::where("reservation_id", $reservation_id)->value("task_status");
$bookingInvoice_status = BookingInvoice::where("reservation_id", $reservation_id)->value("invoice_status");
if ($res_task_status == "paid") {
$json = ["status" => 400, "msg" => "You cannot cancel a trip with profoma paid"];
} else if ($bookingInvoice_status && $bookingInvoice_status == "4") {
$json = ["status" => 401, "msg" => "The Trip you want to cancel has a paid invoice, please see the accountant"];
} else if ($checkStatus == "cancelled") {
$json = ["status" => 402, "msg" => "The Trip has already been cancelled"];
} else {
$tripArray = ["trip_status" => "cancelled"];
$closeTrip = Trip::where("id", $request->iDelete)->update($tripArray);
//Delete trip in booking item
$deleteinprofoma = BookingInvoiceItem::where("tripID", $request->iDelete)->forcedelete();
$iDate = date('Y-m-d H:i:s');
$auditTrail = "| Trip Cancelled By " . Auth::user()->name . " on $iDate |
";
$auditUpdate = DB::statement("UPDATE `trips` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$auditTrail' ELSE CONCAT(`auditTrail`, '$auditTrail') END WHERE `id` = '" . $request->id . "' ");
$json = ["status" => 200];
}
return response()->json($json);
}
}
public function deletefrominvoicetrip(Request $request) {
$json = [];
$accessType = Auth::user()->trip_access_type;
$username = Auth::user()->name;
//if(isset($request->id) && ($accessType == "1" || $accessType == "3")){ }
//Check if the invoice has been paid for
if (isset($request->id)) {
$getInoviceID = BookingInvoiceItem::where("invoice_id", $request->id)->value("booking_invoice_item_id");
$getInvoiceStatus = BookingInvoice::where("id", $getInoviceID)->value("invoice_status");
$loggedBy = BookingInvoiceItem::where("invoice_id", $request->id)->value("logged_by");
$getTripID = BookingInvoiceItem::where("invoice_id", $request->id)->value("tripID");
if ($getInvoiceStatus == 4) {
$json = ["status" => 400, "msg" => "You cannot delete request from invoice that has been paid for"];
} else if ($getTripID == 0 && ($accessType == 2 || $accessType == 3)) {
$deleteBookingItem = BookingInvoiceItem::where("invoice_id", $request->id)->forcedelete();
$json = ["status" => 200, "msg" => "You have successfully deleted the additional booking item!"];
} else if ($getTripID != 0) {
// } else if ($getTripID != 0 && (Auth::user()->id === $loggedBy)) {
//Delete the Booking Item
$deleteBookingItem = BookingInvoiceItem::where("invoice_id", $request->id)->forcedelete();
//Update Not Delete the Trip
//$deleteTrip = Trip::where("id", $getTripID)->forcedelete();
$date = date('Y-m-d');
$array = [
"trip_status" => 'cancelled',
"task_status" => 2,
];
$deleteTrip = Trip::where("id", $getTripID)->update($array);
//Delete the Additional Servic
$deleteAddserviceTrip = AdditionalServiceTrip::where("trip_id", $getTripID)->forcedelete();
$json = ["status" => 200, "msg" => "You have successfully deleted both the booking item and the trip!"];
} else {
$json = ["status" => 401, "msg" => "You Cannot delete request, kindly inform the requester to delete it"];
}
}
return response()->json($json);
}
public function sendmyInvoice(Request $request) {
$json = [];
if (isset($request->bookingInoviceID)) {
$bookingInoviceID = $request->bookingInoviceID;
$cid = $request->cid;
$reservationID = $request->reservationID;
$cto = $request->cto;
$cc = $request->cc;
$subject = $request->subject;
$pdf = "";
//Send mail to all ICU team both in Lagos and PH and Abuja
$accessType = Auth::user()->trip_access_type;
$bookingInvoiceItem = BookingInvoiceItem::where("booking_invoice_item_id", $bookingInoviceID)->get();
$bookingsInvoice = BookingInvoice::where("id", $bookingInoviceID)->get()->first();
$idata = ["booking" => $bookingsInvoice, "useraccess" => $accessType, "bookingItem" => $bookingInvoiceItem];
$getTemplate = BookingInvoice::where("id", $bookingInoviceID)->value("invoice_template");
if ($getTemplate == "hertz") {
$pdf = PDF::loadView('trips.invoice-hertz', $idata);
} else if ($getTemplate == "plain") {
$pdf = PDF::loadView('trips.invoice-download-plain', $idata);
} else {
$pdf = PDF::loadView('trips.invoice-download-plain', $idata);
}
$totalSum = BookingInvoiceItem::where("booking_invoice_item_id", $bookingInoviceID)->sum("net_value");
$userName = User::where("id", Auth::user()->id)->value("name");
$office = BookingInvoice::where("id", $bookingInoviceID)->value("office");
$contactPerson = BookingInvoice::where("id", $bookingInoviceID)->value("contact_name");
$credit_type = BookingInvoice::where("id", $bookingInoviceID)->value("credit_type");
$data = ['office' => $office, 'accessType' => $accessType, 'cc' => $cc, 'subject' => $subject, 'cto' => $cto, 'userName' => $userName, 'cName' => $contactPerson, 'resID' => $reservationID,
'type' => $credit_type, 'cid' => Companies::where("companies_id", $cid)->value("company_name"), 'total_sum' => $totalSum];
//$explodecc = explode(",", $cc);
$cc = explode(',',$cc);
// $message->sender("ADMIN", "ADMIN");
Mail::send(['html' => 'email.emailinvoice'], $data, function ($message) use($subject, $cc, $cto, $data, $pdf) {
$message->from('info@sys.c-ileasing.com', 'HERTZ NIGERIA');
$message->to($cto);
$message->to($cc);
//$message->bcc("oladejo.lasisi@c-ileasing.com");
$message->subject($subject);
$message->attachData($pdf->output(), "invoice.pdf");
$message->getSwiftMessage();
});
$json = ["status" => 200];
} else {
$json = ["status" => 500];
}
return response()->json($json);
}
////////////////////////////////////////////// PAY FOR INVOICE ///////////////////////////////////////////////
public function payforinvoice(Request $request) {
$reservationID = $request->reservationID;
$json = [];
$date = date("Y-m-d");
if (isset($request->bookingInoviceID) && isset($request->amountpaid)) {
$invoiceStatus = BookingInvoice::where("id", $request->bookingInoviceID)->value('invoice_status');
if ($invoiceStatus && (($invoiceStatus == "4") || ($invoiceStatus == "5"))) {
$json = ['status' => 1, 'msg' => "You cannot update that invoice. Plase check invoice Status and ensure previous invoice has either been approved or cancelled"];
} else {
$updateArray = [
"payment_date" => isset($request->paymentDate) ? $request->paymentDate : $date,
//"purchase_order" => $request->purchase_order,
"invoice_status" => isset($request->payments) ? $request->payments : 0,
"payment_term" => isset($request->payment_term) ? $request->payment_term : 'Null',
"evidence_payment_text" => isset($request->evidence_payment_text) ? $request->evidence_payment_text : "0",
"bank_details" => isset($request->bankDetails) ? $request->bankDetails : 'Null',
"amount_paid" => isset($request->amountpaid) ? $request->amountpaid : '0',
"nameofdepositor" => isset($request->nameofdepositor) ? $request->nameofdepositor : '',
"paid_by" => Auth::user()->id,
];
$doUpdate = BookingInvoice::where("id", $request->bookingInoviceID)->update($updateArray);
if ($request->payments == '4') {
//Update Reservation
$ir_paid = Reservation::where('reservation_id', $reservationID)->update([
'task_status' => 'paid',
'amount_paid' => $request->amountpaid,
'confirm_payment' => Auth::user()->name,
'confirmed_by' => Auth::user()->id,
]);
//Update Trip
$trips_paid = Trip::where(['reservation_id' => $reservationID, 'trip_status' => 'confirmed'])->update([
'paid_by' => Auth::user()->id,
'job_status' => '2',
]);
$iDate = date("Y-m-d H:i:s");
//Audit trail booking
$iauditTrail = "| Invoice Approved By ".Auth::user()->name." on $iDate |
";
$auditUpdate = DB::statement("UPDATE `booking_invoice` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$iauditTrail' ELSE CONCAT(`auditTrail`, '$iauditTrail') END WHERE `id` = '" . $request->bookingInoviceID . "' ");
//Addd Audit Trails to trips
$iiauditTrail = "| Invoice Approved By " . Auth::user()->name . " on $iDate with Invoice ID of $request->bookingInoviceID |
";
$iauditUpdate = DB::statement("UPDATE `trips` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$iiauditTrail' ELSE CONCAT(`auditTrail`, '$iiauditTrail') END WHERE `reservation_id` = '" . $request->reservationID . "' AND `trip_status` = 'confirmed' ");
///////////////////////////////////////// //Send email to client ////////////////////////////////////////////
$bookingInoviceID = $request->bookingInoviceID;
//$cto = BookingInvoiceItem::where("booking_invoice_item_id", $bookingInoviceID)->sum("contact_email");
$cto = "oladejo.lasisi@c-ileasing.com";
// if($cto) {
// $pdf = "";
// //Send mail to all ICU team both in Lagos and PH and Abuja
// $accessType = Auth::user()->trip_access_type;
// $bookingInoviceID = $request->bookingInoviceID;
// $bookingInvoiceItem = BookingInvoiceItem::where("booking_invoice_item_id", $bookingInoviceID)->get();
// $bookingsInvoice = BookingInvoice::where("id", $bookingInoviceID)->first();
// $idata = ["booking" => $bookingsInvoice, "useraccess" => $accessType, "bookingItem" => $bookingInvoiceItem];
// $getTemplate = BookingInvoice::where("id", $bookingInoviceID)->value("invoice_template");
// $pdf = PDF::loadView('trips.invoice-hertz', $idata);
// $totalSum = BookingInvoiceItem::where("booking_invoice_item_id", $bookingInoviceID)->sum("net_value");
// $userName = User::where("id", Auth::user()->id)->value("name");
// $cc = User::where("id", Auth::user()->id)->value("email");
// $subject = "Invoice Payment Confirmation";
// //$cto = BookingInvoiceItem::where("booking_invoice_item_id", $bookingInoviceID)->sum("contact_email");
// $cid = BookingInvoiceItem::where("booking_invoice_item_id", $bookingInoviceID)->value("cid");
// $office = BookingInvoice::where("id", $bookingInoviceID)->value("office");
// $contactPerson = BookingInvoice::where("id", $bookingInoviceID)->value("contact_name");
// $credit_type = BookingInvoice::where("id", $bookingInoviceID)->value("credit_type");
// $reservationID = BookingInvoice::where("id", $bookingInoviceID)->value("reservation_id");
// $data = ['office' => $office, 'accessType' => $accessType, 'cc' => $cc, 'subject' => $subject, 'cto' => $cto,
// 'userName' => $userName, 'cName' => $contactPerson, 'resID' => $reservationID,
// 'type' => $credit_type, 'cid' => Companies::where("companies_id", $cid)->value("company_name"),
// 'total_sum' => $totalSum];
// $message->sender("ADMIN", "ADMIN");
// Mail::send(['html' => 'email.payment_confirm'], $data, function ($message) use($subject, $reservationID, $cc, $cto, $data, $pdf) {
// $message->from('info@sys.c-ileasing.com', 'HERTZ PAYMENT CONFIRMATION');
// $message->to($cto);
// $message->to($cc);
// $message->bcc("oladejo.lasisi@c-ileasing.com");
// $message->subject($subject);
// $message->attachData($pdf->output(), $reservationID."invoice.pdf");
// $message->getSwiftMessage();
// });
// }
///////////////////////////////////////// // End of Send email to client ////////////////////////////////////////////
}
//amount_paid
if ($request->payments == '5') {
$paid = DB::statement("UPDATE `booking_invoice` SET `cancelled_by` = '" . Auth::user()->id . "' WHERE `id` = '" . $request->bookingInoviceID . "' ");
$iauditTrail = "| Invoice Cancelled By " . Auth::user()->name . " on $iDate |
";
$auditUpdate = DB::statement("UPDATE `booking_invoice` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$iauditTrail' ELSE CONCAT(`auditTrail`, '$iauditTrail') END WHERE `id` = '" . $request->bookingInoviceID . "' ");
//Update Reservation
$ir_paid = DB::statement("UPDATE `reservations` SET `task_status` = 'cancelled', WHERE `reservation_id` = '" . $request->reservationID . "' ");
//Update Trip
$trips_paid = DB::statement("UPDATE `trips` SET `trip_status` = 'cancelled' WHERE `reservation_id` = '" . $request->reservationID . "' ");
//Addd Audit Trails to trips
$iiauditTrail = "| Invoice Cancelled By " . Auth::user()->name . " on $iDate with Invoice ID of $request->bookingInoviceID |
";
$iauditUpdate = DB::statement("UPDATE `trips` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$iiauditTrail' ELSE CONCAT(`auditTrail`, '$iiauditTrail') END WHERE `reservation_id` = '" . $request->reservationID . "' ");
}
// dd($request->evidencefile);
//Update files to server
if (isset($request->evidencefile)) {
$this->validate($request, [
'evidencefile' => 'required|image|mimes:jpeg,png,jpg,gif,svg|max:2048',
]);
if ($request->hasFile('evidencefile')) {
$image = $request->file('evidencefile');
$name = rand(100, 100000) . time() . '.' . $image->getClientOriginalExtension();
$destinationPath = public_path('payment_evidence');
$image->move($destinationPath, $name);
$array = [
"evidence_payment_image" => $name
];
$update = BookingInvoice::where("id", $request->bookingInoviceID)->update($array);
}
}
$json = ['status' => 2, 'msg' => "Invoice Successfully Updated!"];
}
} else {
$json = ['status' => 3, 'msg' => "Error process page please try again!"];
}
return response()->json($json);
}
////////////////////////////////////////////// PAY FOR INVOICE END ////////////////////////////////////////////
public function icancelProfoma(Request $request) {
if (isset($request->iDelete)) {
$json = [];
//$checkStatus = Trip::where("id", $request->iDelete)->value("trip_status");
//$jobStatus = Trip::where("id", $request->iDelete)->value("job_status");
//$reservation_id = Trip::where("id", $request->iDelete)->value("reservation_id");
$res_task_status = Reservation::where("reservation_id", $request->iDelete)->value("task_status");
$bookingInvoice_status = BookingInvoice::where("reservation_id", $request->iDelete)->value("invoice_status");
if ($res_task_status == "paid") {
$json = ["status" => 400, "msg" => "You cannot cancel a that profoma"];
} else if ($bookingInvoice_status && $bookingInvoice_status == "4") {
$json = ["status" => 401, "msg" => "The Reservation has been paid for, you cannot cancel it"];
} else {
$taskArray = ["task_status" => "cancelled"];
$tripArray = ["trip_status" => "cancelled", "job_status" => 3];
$cancelReseration = Reservation::where("reservation_id", $request->iDelete)->update($taskArray);
$jobStatus = Trip::where("reservation_id", $request->iDelete)->update($tripArray);
//Delete trip in booking item
$invoiceStatus = ["invoice_status" => "5"];
$deleteinprofoma = BookingInvoice::where("reservation_id", $request->iDelete)->update($invoiceStatus);
$deleteBookingItem = BookingInvoiceItem::where("reservation_id", $request->iDelete)->update(["del" => 1]);
$iDate = date('Y-m-d H:i:s');
$auditTrail = "| Trip Cancelled By " . Auth::user()->name . " on $iDate |
";
$auditTrai2 = "| Profoma Cancelled By " . Auth::user()->name . " on $iDate |
";
$auditTrai3 = "| Reservation Cancelled By " . Auth::user()->name . " on $iDate |
";
$auditUpdate = DB::statement("UPDATE `trips` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$auditTrail' ELSE CONCAT(`auditTrail`, '$auditTrail') END WHERE `reservation_id` = '" . $request->iDelete . "' ");
$auditUpdate2 = DB::statement("UPDATE `booking_invoice` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$auditTrai2' ELSE CONCAT(`auditTrail`, '$auditTrai2') END WHERE `reservation_id` = '" . $request->iDelete . "' ");
$auditUpdate3 = DB::statement("UPDATE `reservations` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$auditTrai3' ELSE CONCAT(`auditTrail`, '$auditTrai3') END WHERE `reservation_id` = '" . $request->iDelete . "' ");
$json = ["status" => 200];
}
return response()->json($json);
}
}
public function pendingJobs(Request $request) {
$data = [];
if (isset($request->vehicles)) {
$explode = explode(" ", $request->pickupDate);
$tripdetails = Trip::where('vehicle_id', $request->vehicles)
->where('pick_up_date', '>=', $explode[0])->get();
if (!empty($tripdetails)) {
$data = ["status" => 200, "trip_details" => TripResource::collection($tripdetails)];
} else {
$data = ["status" => 400, "trip_details" => ""];
}
}
return response()->json($data);
}
public function closeTask(Request $request) {
$json = [];
$tripArray = ["job_status" => 2];
$closeTrip = Trip::where(["reservation_id" => $request->reservationID, "task_status" => "4"])->update($tripArray);
$reservationArray = ["task_status" => "invoice_pending"];
//$updateReservation = Reservation::where("reservation_id", $request->reservationID)->update($reservationArray);
$updateReservation = DB::statement("UPDATE `reservations` SET `task_status` = 'invoice_pending' WHERE `reservation_id` = '" . $request->reservationID . "' ");
$BookingInvoiceArray = ["invoice_status" => "9"];
$updateBookingInvoice = BookingInvoice::where("reservation_id", $request->reservationID)->update($BookingInvoiceArray);
$iDate = date('Y-m-d H:i:s');
$auditTrail = "| Trip Closed By " . Auth::user()->name . " on $iDate |
";
$auditUpdate = DB::statement("UPDATE `trips` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$auditTrail' ELSE CONCAT(`auditTrail`, '$auditTrail') END WHERE `reservation_id` = '" . $request->reservationID . "' ");
$auditUpdate2 = DB::statement("UPDATE `reservations` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$auditTrail' ELSE CONCAT(`auditTrail`, '$auditTrail') END WHERE `reservation_id` = '" . $request->reservationID . "' ");
if ($updateReservation) {
$json = ["status" => 200];
} else {
$json = ["status" => 400, "status" => "null"];
}
return response()->json($json);
}
public function invoiceexportsearch(Request $request) {
$allInvoice = "";
$accessType = Auth::user()->trip_access_type;
$url = "SEARCHED RESULT";
if (isset($_SERVER['QUERY_STRING']) && trim($_SERVER['QUERY_STRING']) != '') {
$expsql = '';
$firstExplode = explode('&', $_SERVER['QUERY_STRING']);
foreach ($firstExplode as $expItem) {
$secExp = explode('=', trim($expItem));
if (isset($secExp[1]) && trim($secExp[0]) != 'url' && trim($secExp[0]) != 'modroute' && trim($secExp[0]) != '_token') {
//$explode_exp1 = explode('_', trim($secExp[0]));
/*
*elseif ($secExp[0] === 'invoice_date') {
$expsql .= (trim($secExp[1]) != '') ? 'DATE(`' . substr($secExp[0], 0) . '`) >= ' . '"' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
} elseif ($secExp[0] === 'created_at') {
$expsql .= (trim($secExp[1]) != '') ? 'DATE(`' . substr($secExp[0], 0) . '`) <= ' . '"' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
}
*/
if (trim($secExp[1]) == 'cid') {
$expsql .= (trim($secExp[1]) != '') ? '`' . $secExp[0] . '` = "' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
} elseif ($secExp[0] === 'reservation_date') {
$expsql .= (trim($secExp[1]) != '') ? 'DATE(`' . substr($secExp[0], 0) . '`) >= ' . '"' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
}elseif ($secExp[0] === 'reservation_created_at') {
$expsql .= (trim($secExp[1]) != '') ? 'DATE(`' . substr($secExp[0], 0) . '`) <= ' . '"' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
} else if ($secExp[0] === 'office') {
$expsql .= (trim($secExp[1]) != '') ? '`' . $secExp[0] . '` = "' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
} else
$expsql .= (trim($secExp[1]) != '') ? '`' . $secExp[0] . '` like ' . '"%' . str_replace('+', ' ', $secExp[1]) . '%" AND ' : '';
}else {
$expsql .= "";
}
}
$formatexpsql = substr($expsql, 0, -4);
//$formatexpsql = trim($formatexpsql) != '' ? $formatexpsql.' AND ': '';
$allInvoice = DB::table("booking_invoice")->whereRaw(DB::raw($formatexpsql))->get();
} else {
$allInvoice = '';
}
if ($allInvoice) {
$data = ['url' => $url, "invoices" => $allInvoice, "accessType" => $accessType];
return view('trips/reports', $data);
}
}
////////////////////////////////////////// TO BE DELETED /////////////////////////////////////////////////////
public function viewinvoiceFivepercent(Request $request, $id) {
$loggeby = "false";
if ($request->input('view') === "true") {
$data = [];
$QueryString = $request->input('view');
$allTrips = "";
//$trips = Trip::where(["reservation_id"=> $id, "trip_status" => "confirmed"])->get();
$trips = BookingInvoiceItem::where(["reservation_id" => $id])->get();
$url = "VIEW INVOICE FIVE PERCENT";
//$reservation = Reservation::where("reservation_id", $id)->get()->first();
$bookingInvoice = BookingInvoice::where("reservation_id", $id)->get()->first();
$bank = Bank::all();
$accessType = Auth::user()->trip_access_type;
$createdBy = BookingInvoice::where(["reservation_id" => $id])->value("created_by");
$invoiceTotalSum = BookingInvoiceItem::where(["reservation_id" => $id])->sum('net_value');
if ($createdBy == Auth::user()->id) {
$loggeby = "true";
}
$data = ['url' => $url, 'invoiceSum' => $invoiceTotalSum, 'accessType' => $accessType, 'loggeby' => $loggeby, 'createdBy' => $createdBy, 'QueryString' => $QueryString, "dBanks" => $bank, "id" => $id, "trips" => $trips, "bookingInvoice" => $bookingInvoice];
return view('trips/final_invoice_fivepercent', $data);
} else {
echo "not valid, please contact administrator";
}
}
public function downloadInvoiceFive($id, $template) {
if ($id && $template) {
$bookingsInvoice = BookingInvoice::where("id", $id)->get()->first();
$bookingInvoiceItem = BookingInvoiceItem::where("booking_invoice_item_id", $id)->get();
$accessType = Auth::user()->trip_access_type;
$data = ["booking" => $bookingsInvoice, "useraccess" => $accessType, "bookingItem" => $bookingInvoiceItem];
if ($template == "hertz") {
$pdf = PDF::loadView('trips.invoice-hertz-five', $data);
} else if ($template == "plain") {
$pdf = PDF::loadView('trips.invoice-download-plain-five', $data);
} else {
$pdf = PDF::loadView('trips.invoice-download-plain', $data);
}
return $pdf->download('invoice' . rand(11111, 99999) . time() . '.pdf');
}
}
///////////////////////////////////////// END OF TO BE DELETED /////////////////////////////////////////////
public function uploadPO(Request $request) {
if (isset($request->id) && isset($request->uploadpix)) {
$this->validate($request, [
'uploadpix' => 'required|mimes:jpeg,png,jpg,pdf,docx,doc,gif,svg|max:2000048',
]);
if ($request->hasFile('uploadpix')) {
$image = $request->file('uploadpix');
$name = rand(100, 100000) . time() . '.' . $image->getClientOriginalExtension();
$destinationPath = public_path('images/reservationPO');
$image->move($destinationPath, $name);
$array = [
"file_name" => $name,
"comment" => $request->comment,
"reservation_id" => $request->id
];
$insert = \App\ReservationUploads::create($array);
if ($insert) {
return response()->json([
"status" => 200,
"msg" => "PO Uploaded Successfully"
], 200);
} else {
return response()->json([
"status" => 400,
"msg" => "Error Uploading Photo"
], 200);
}
}
} // End of if(isset($request->id)){
}
// uploadDriver(Request $request){
public function exportripreport(Request $request) {
$url = "EXPORT TRIP REPORT";
$data = [];
$reservation_id = $request->reservation_id;
$office = Office::all();
$companies = Companies::where("comp_status", "Active")->get();
//$reservation_id = $request->reservationID;
if ($reservation_id == "" && $request->office == "" && $request->pick_up_date == "" && $request->end_date == "" && $request->cid == "" && $request->passenger_names == "" && $request->vehicle_id == "" && $request->trip_status == "" && $request->job_status == "") {
return redirect('get/alltrips');
}
if (isset($_SERVER['QUERY_STRING']) && trim($_SERVER['QUERY_STRING']) != '') {
$expsql = '';
$firstExplode = explode('&', $_SERVER['QUERY_STRING']);
foreach ($firstExplode as $expItem) {
$secExp = explode('=', trim($expItem));
if (isset($secExp[1]) && trim($secExp[0]) != 'url' && trim($secExp[0]) != 'modroute' && trim($secExp[0]) != '_token') {
//$explode_exp1 = explode('_', trim($secExp[0]));
if (trim($secExp[1]) == 'cid') {
$expsql .= (trim($secExp[1]) != '') ? '`' . $secExp[0] . '` = "' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
} elseif ($secExp[0] === 'pick_up_date') {
$expsql .= (trim($secExp[1]) != '') ? 'DATE(`' . substr($secExp[0], 0) . '`) >= ' . '"' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
} elseif ($secExp[0] === 'end_date') {
$expsql .= (trim($secExp[1]) != '') ? 'DATE(`' . substr($secExp[0], 0) . '`) <= ' . '"' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
} else if ($secExp[0] === 'cid') {
$expsql .= (trim($secExp[1]) != '') ? '`' . $secExp[0] . '` = "' . str_replace('+', ' ', $secExp[1]) . '" AND ' : '';
} else
$expsql .= (trim($secExp[1]) != '') ? '`' . $secExp[0] . '` like ' . '"%' . str_replace('+', ' ', $secExp[1]) . '%" AND ' : '';
}else {
$expsql .= "";
}
}
$formatexpsql = substr($expsql, 0, -4);
//$formatexpsql = trim($formatexpsql) != '' ? $formatexpsql.' AND ': '';
$allTrips = DB::table("trips")->whereRaw(DB::raw($formatexpsql))->get();
//$allTrips = Trip::where($formatexpsql)->paginate(100);
} else {
$allTrips = '';
}
if (count($allTrips) > 0) {
$data = ['url' => $url, "tripsbycid" => $allTrips, "companies" => $companies, "office" => $office];
return view('trips/trips_reports', $data);
}
}
public function viewiamges($id) {
$viewImage = \App\ReservationUploads::where("reservation_id", $id)->get();
echo "
ALL UPLOADED EVIDENCE OF PAYMENT
";
if ($viewImage) {
foreach ($viewImage as $iget) {
echo "Click Here to View File
";
}
}
echo "";
}
public function monthendetails($id = "") {
$url = "EXPORT TRIP REPORT";
$data = [];
if ($id == "") {
echo "Important Parameter Missing";
} else {
$result = Trip::whereIn("id", [$id])->get();
$getAllTrips = "SELECT * FROM trips WHERE id IN ($id)";
$result = collect(DB::select(DB::raw($getAllTrips)));
$data = ['url' => $url, "result" => $result];
return view('trips/report_details', $data);
}
}
public function succesfullyCreated($id, $random) {
$url = "Reservation Succcessfuly";
$userID = Auth::user()->id;
$status = Reservation::where("reservation_id", $id)->value("task_status");
$data = ['url' => $url, 'id' => $id, 'userID' => $userID, 'status' => $status];
return view('trips/bookingsuccessful', $data);
}
public function analyzeReport($period, $year) {
$years = collect(DB::select(DB::raw('SELECT YEAR(pick_up_date) as year FROM trips GROUP BY year ORDER BY year DESC')))->pluck('year');
$data = [];
$getUtilization = "";
$url = "ANALYZE REPORT";
$accessType = Auth::user()->trip_access_type;
$office = Office::all();
$companies = Companies::where("comp_status", "Active")->get();
$allTrips = Trip::where("job_status", "2")->paginate(50);
// $period = \App\Period::where("id", "1")->value('period');
//comment on 28/02/2022
// $monthendreview = "SELECT GROUP_CONCAT(id) AS id, cid, office, reservation_id, vehicle_id, pick_up_date, end_date, number_of_days, GROUP_CONCAT(driver) AS driver, vehicle_type, job_status, created_at, COUNT(vehicle_id) AS occurence, SUM(number_of_days) as actual_days, MONTH(pick_up_date) AS imonth, MONTHNAME(pick_up_date) AS 'month_name', YEAR(pick_up_date) as YEAR FROM `trips` WHERE job_status = 2 AND MONTH(pick_up_date) = $period GROUP BY vehicle_id ORDER BY office, cid";
$monthendreview = "SELECT GROUP_CONCAT(id) AS id, cid, office, reservation_id, vehicle_id, pick_up_date, end_date, number_of_days, GROUP_CONCAT(driver) AS driver, vehicle_type, job_status, created_at, COUNT(vehicle_id) AS occurence,SUM(number_of_days) as actual_days, MONTH(pick_up_date) AS imonth, MONTHNAME(pick_up_date) AS 'month_name', YEAR(pick_up_date) as YEAR FROM `trips` WHERE job_status = 2 AND MONTH(pick_up_date) = $period AND YEAR(pick_up_date) = $year GROUP BY vehicle_id ORDER BY office, cid";
$monthendreview = collect(DB::select(DB::raw($monthendreview)));
if ($monthendreview) {
$downtime_days = 0;
foreach ($monthendreview as $get) {
$expectedworkday = \App\Vehicles::where("plate_number", $get->vehicle_id)->value('number_of_days');
$expectedworkday == 0 ? 1 : $expectedworkday;
if ($get->actual_days > $expectedworkday) {
$downtime_days = 0;
} else {
$downtime_days = $expectedworkday - $get->actual_days;
}
$downtime_percent = "";
if (($downtime_days && $downtime_days > 0 ) && $expectedworkday) {
$downtime_percent = $downtime_days / $expectedworkday * 100;
} else {
$downtime_percent = "100%";
}
$percent_of_utilization = 0;
if($downtime_days > 0){
$percent_of_utilization = (\App\Vehicles::where("plate_number", $get->vehicle_id)->value('number_of_days') - $downtime_days) / $expectedworkday * 100;
}
$revenue_loss = $downtime_days * \App\Vehicles::where("plate_number", $get->vehicle_id)->value('rate');
//Check for the priod 'YEAR' => $year
$check_period = \App\AnayseUtilization::where(["month_val" => $get->imonth, 'vehicle' => $get->vehicle_id])->value('month_val');
if($check_period){
$ianswer = \App\AnayseUtilization::where(['vehicle' => $get->vehicle_id, 'month_val' => $get->imonth, 'YEAR' => $get->YEAR])->update([
'reservation_id' => $get->id,
'year' => $get->YEAR,
'month' => $get->month_name,
'month_val' => $get->imonth,
'cid' => $get->cid,
'office' => $get->office,
'vehicle' => $get->vehicle_id,
'drivers' => $get->driver,
'occurence' => $get->occurence,
'expected_days_work' => $expectedworkday,
'days_worked' => $get->actual_days,
'unclosed_jobs' => \App\Trip::where("vehicle_id", $get->vehicle_id)->where("job_status", ['0', '1'])->whereMonth('pick_up_date', '=', $period)->count('vehicle_id'),
'downtime' => $downtime_days,
'downtime_percent' => $downtime_percent,
'percent_utilization' => $percent_of_utilization,
'rate' => \App\Vehicles::where("plate_number", $get->vehicle_id)->value('rate'),
'revenue_loss' => $revenue_loss,
]);
//get total trips this month:
// $trips_count = Trip::where('vehicle_id', $get->vehicle_id])->filter(function($trip){
// return
// })->count();
}else{
//Do Insert
$ianswer = \App\AnayseUtilization::create([
'reservation_id' => $get->id,
'year' => $get->YEAR,
'month' => $get->month_name,
'month_val' => $get->imonth,
'cid' => $get->cid,
'office' => $get->office,
'vehicle' => $get->vehicle_id,
'drivers' => $get->driver,
'occurence' => $get->occurence,
'expected_days_work' => $expectedworkday,
'days_worked' => $get->actual_days,
'unclosed_jobs' => \App\Trip::where("vehicle_id", $get->vehicle_id)->where("job_status", ['0', '1'])->whereMonth('pick_up_date', '=', $period)->count('vehicle_id'),
'downtime' => $downtime_days,
'downtime_percent' => $downtime_percent,
'percent_utilization' => $percent_of_utilization,
'rate' => \App\Vehicles::where("plate_number", $get->vehicle_id)->value('rate'),
'revenue_loss' => $revenue_loss,
]);
}
// dd($ianswer);
}
}
$userOffice = Auth::user()->location;
$getUtilization = \App\AnayseUtilization::where(['month_val' => $period, 'YEAR' => $year, 'office' =>$userOffice])->get();
$utilizationTypes = \App\Utilizationtype::where("fleet", "1")->where('del', '0')->get();
$period = \App\Period::where("id", "1")->value('period');
$year = \App\Period::where("id", "1")->value('year');
$downtimeSummary = "SELECT office, cid, count(vehicle) as no_of_vehicles, SUM(expected_days_work) as expected_days_work, SUM(days_worked) as days_worked, SUM(downtime) as downtime_days, SUM(rate) as cumulative_rate, downtime_reason FROM `reservation_utilization_analysis` WHERE month_val = '$period' AND year = '$year' AND downtime_reason != '' GROUP BY office, downtime_reason";
$downtimeSummary = collect(DB::select(DB::raw($downtimeSummary)));
// print_r($monthendreview);
$data = ['url' => $url, "tripsbycid" => $allTrips, 'year'=>$year, 'years'=>$years, 'downtimeSummary' => $downtimeSummary, 'utilizationTypes' =>$utilizationTypes, 'getUtilization' =>$getUtilization, "period" => $period, "monthendreview" => $monthendreview, "companies" => $companies, "office"=>$office];
return view('trips/completed_trips', $data);
}
public function addUtilization(Request $request){
$json = [];
$updateUtilization = \App\AnayseUtilization::where("id", $request->dataid)->update(["downtime_reason" => $request->utilize_name]);
$json = ["status" => 200];
return response()->json($json);
}
public function showmergedinvice($id){
//BD = 0, Reservation = 1, Accountant = 2, Admin = 3
$data = [];
$getCompanyAccess = [];
$allTrips = "";
$accessType = Auth::user()->trip_access_type;
if ($accessType == 2 || $accessType == 3) {
$url = "MERGED INVOICES";
} else {
$url = "ALL INVOICES";
}
$userID = Auth::user()->id;
$office = Office::all();
$company = Companies::all();
/////////////////////////////////////RAW SQL ////////////////////////////////////////////
if ($accessType == 2 || $accessType == 3 || $accessType == 4) {
$pushed_invoices = "SELECT * FROM `reservations` WHERE reservation_id IN($id)";
$pushed_invoices = collect(DB::select(DB::raw($pushed_invoices)));
} else {
echo "You do not have access to view invoice";
return;
}
$data = ['url' => $url, "pushed_invoices" =>$pushed_invoices, "accessType" => $accessType, "office" => $office, "companies" => $company];
return view('trips/allinvoices_merged', $data);
}
public function generatemoreinvoice(Request $request) {
$data = [];
$accessType = Auth::user()->trip_access_type;
$url = "MERGED INVOICES";
$userID = Auth::user()->id;
$office = Office::all();
$company = Companies::all();
$bank = Bank::all();
$joincid = "";
if (isset($request->allcheckedID)) {
foreach ($request->allcheckedID as $key => $value) {
$getCID = BookingInvoice::where("reservation_id", $value)->value("cid") . "
";
$joincid .= ",". $getCID;
//$getCID = Companies::where("companies_id", $value)->value("company_name");
}
$implodeRequest = implode(",", $request->allcheckedID);
$explodeRequest = explode(",", $implodeRequest);
$newcount = count($explodeRequest);
$getFirstItem = $explodeRequest[0];
if ($accessType == 2 || $accessType == 3) {
$bookingInvoice = BookingInvoice::where("reservation_id", $getFirstItem)->first();
$trips = BookingInvoiceItem::whereIn("reservation_id", $explodeRequest)->orderBy("reservation_id", "DESC")->get();
$invoiceSum = BookingInvoiceItem::whereIn("reservation_id", $explodeRequest)->sum("net_value");
$data = ['url' => $url, "trips" => $trips, "invoiceSum" => $invoiceSum, "dBanks" => $bank, "implodeRequest" => $implodeRequest, "bookingInvoice" => $bookingInvoice, "explodeRequest" => $explodeRequest, "accessType" => $accessType, "office" => $office, "companies" => $company];
return view('trips/multiple_invoices', $data);
}
}
}
public function downloadInvoiceMultiple($id) {
if ($id) {
$explodeids = explode(",", $id);
$bookingsInvoice = BookingInvoice::where("reservation_id", $explodeids[0])->get()->first();
$bookingInvoiceItem = "SELECT * FROM `booking_invoice_item` WHERE reservation_id IN($id)";
$bookingInvoiceItem = collect(DB::select(DB::raw($bookingInvoiceItem)));
// $bookingsInvoice = BookingInvoice::where("id", $explodeids[0])->get()->first();
//$bookingInvoiceItem = BookingInvoiceItem::where("booking_invoice_item_id", $id)->get();
$accessType = Auth::user()->trip_access_type;
$data = ["booking" => $bookingsInvoice, "id" =>$id, "useraccess" => $accessType, "bookingItem" => $bookingInvoiceItem];
$pdf = PDF::loadView('trips.invoice-download-plain-multiple', $data);
return $pdf->download('multiple_invoice' . rand(11111, 99999) . time() . '.pdf');
}
}
public function paymentpendingsGraph($month="") {
//set the year of expenses to show
$moth_to_fetch = $month ? $month : date('m');
//$earnings = $this->genmod->getYearEarnings($year_to_fetch);
$unpaid_this_month = "SELECT reservation_date, office, count(reservation_id) as total, task_status FROM `reservations` where MONTH(reservation_date) = '$moth_to_fetch' AND task_status NOT IN('paid', 'cancelled', 'submitted', 'confirmed') group by office";
$unpaid_this_month = collect(DB::select(DB::raw($unpaid_this_month)));
$lastEarnings = 0;
$monthEarnings = array();
$hightEarn['highestEarning'] = 0;
$dataarr = [];
//$allMonths = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
$allLocations = ["1", "2", "3", "4", "5", "6", "7", "8", "9"];
if($unpaid_this_month){
foreach ($allLocations as $locations) {
foreach ($unpaid_this_month as $get) {
// $earningMonth = date("M", strtotime($get->payment_date));
$office = $get->office;
if ($locations == $office) {
$lastEarnings += $get->total;
$monthEarnings[$locations] = $lastEarnings;
} else{
if (!array_key_exists($locations, $monthEarnings)) {
$monthEarnings[$locations] = 0;
}
}
}
if ($lastEarnings > $hightEarn['highestEarning']) {
$hightEarn['highestEarning'] = $lastEarnings;
}
$lastEarnings = 0;
}
foreach ($monthEarnings as $me) {
$dataarr[] = $me;
}
}else{
foreach ($locations as $locations) {
$dataarr[] = 0;
}
}
//add info into array
$json = array("total_pending_payments" => $dataarr, 'pending_invoice_Year'=>$moth_to_fetch);
return response()->json($json);
}
public function unclosedtripsLoss($month="") {
//set the year of expenses to show
$moth_to_fetch = $month ? $month : date('m');
//$earnings = $this->genmod->getYearEarnings($year_to_fetch);
$unpaid_this_month = "SELECT office, count(id) as total FROM `trips` where MONTH(pick_up_date) = '$moth_to_fetch' AND MONTH(end_date) = '$moth_to_fetch' AND job_status IN('0', '1') group by office";
$unpaid_this_month = collect(DB::select(DB::raw($unpaid_this_month)));
$lastEarnings = 0;
$monthEarnings = array();
$hightEarn['highestEarning'] = 0;
$dataarr = [];
//$allMonths = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
$allLocations = ["1", "2", "3", "4", "5", "6", "7", "8", "9"];
if($unpaid_this_month){
foreach ($allLocations as $locations) {
foreach ($unpaid_this_month as $get) {
// $earningMonth = date("M", strtotime($get->payment_date));
$office = $get->office;
if ($locations == $office) {
$lastEarnings += $get->total;
$monthEarnings[$locations] = $lastEarnings;
} else{
if (!array_key_exists($locations, $monthEarnings)) {
$monthEarnings[$locations] = 0;
}
}
}
if ($lastEarnings > $hightEarn['highestEarning']) {
$hightEarn['highestEarning'] = $lastEarnings;
}
$lastEarnings = 0;
}
foreach ($monthEarnings as $me) {
$dataarr[] = $me;
}
}else{
foreach ($locations as $locations) {
$dataarr[] = 0;
}
}
//add info into array
$json = array("total_pending_unclosed" => $dataarr, 'close_trips'=>$moth_to_fetch);
return response()->json($json);
}
public function sendpaymentreceipt(Request $request){
$invoiceStatus = BookingInvoice::where("id", $request->bookingInoviceID)->value('invoice_status');
if ($invoiceStatus != "4") {
$json = ['status' => 1, 'msg' => "You cannot send payment receipt for a client that has not paid"];
} else{
$pdf = "";
//Send mail to all ICU team both in Lagos and PH and Abuja
$accessType = Auth::user()->trip_access_type;
$bookingInoviceID = $request->bookingInoviceID;
$bookingInvoiceItem = BookingInvoiceItem::where("booking_invoice_item_id", $bookingInoviceID)->get();
$bookingsInvoice = BookingInvoice::where("id", $bookingInoviceID)->get()->first();
$contactPerson = BookingInvoice::where("id", $bookingInoviceID)->value("contact_name");
$totalSum = BookingInvoiceItem::where("booking_invoice_item_id", $bookingInoviceID)->sum("net_value");
$company = Companies::find(BookingInvoice::where("id", $bookingInoviceID)->value("cid"));
$contactPhone = $company->value("company_phone_number");
//$cto = BookingInvoice::where("id", $bookingInoviceID)->value("contact_email");
$cto = "oladejo.lasisi@c-ileasing.com";
$idata = ["booking" => $bookingsInvoice, "totalSum" => $totalSum, "contactPerson" =>$contactPerson, "useraccess" => $accessType, "bookingItem" => $bookingInvoiceItem];
$getTemplate = BookingInvoice::where("id", $bookingInoviceID)->value("invoice_template");
$pdf = PDF::loadView('trips.payment_invoice_hertz', $idata);
// $time = rand(10, 30) . time();
// return $pdf->download('payment_receipt-"' . $bookingInoviceID . ' - ' . $time . '".pdf');
$userName = User::where("id", Auth::user()->id)->value("name");
$cc = User::where("id", Auth::user()->id)->value("email");
$subject = "Payment Receipt";
$cid = BookingInvoiceItem::where("booking_invoice_item_id", $bookingInoviceID)->value("cid");
$office = BookingInvoice::where("id", $bookingInoviceID)->value("office");
$credit_type = BookingInvoice::where("id", $bookingInoviceID)->value("credit_type");
$reservationID = BookingInvoice::where("id", $bookingInoviceID)->value("reservation_id");
$data = ['office' => $office, 'accessType' => $accessType, 'cc' => $cc, 'subject' => $subject, 'cto' => $cto,
'userName' => $userName, 'cName' => $contactPerson, 'resID' => $reservationID, 'subject' => $subject,
'type' => $credit_type, 'cid' => Companies::where("companies_id", $cid)->value("company_name"),
'total_sum' => $totalSum];
// return response()->json(['status' => 2, 'msg' => ['subject' => $subject, 'resID' => $reservationID, 'cc' => $cc, 'cto' => $cto, 'data' => $data, 'pdf' => $pdf]]);
// $message->sender("ADMIN", "ADMIN");
/*
Mail::send(['html' => 'email.payment_confirm'], $data, function ($message) use($subject, $reservationID, $cc, $cto, $data, $pdf) {
$message->from('info@sys.c-ileasing.com', 'HERTZ PAYMENT RECEIPT');
$message->to($cto);
$message->to($cc);
$message->bcc("oladejo.lasisi@c-ileasing.com");
$message->subject($subject);
$message->attachData($pdf->output(), $reservationID."_invoice.pdf");
$message->getSwiftMessage();
});
*/
Mail::send('email.payment_confirm', $data, function($message) use ($data){
$message->from('info@sys.c-ileasing.com', 'HERTZ PAYMENT RECEIPT');
$message->to($data['cto']);
$message->to($data['cc']);
$message->subject($data['subject']);
$message->bcc("oladejo.lasisi@c-ileasing.com");
});
SMSService::sendSurveyMessage($contactPerson, $contactPhone);
$json = ['status' => 2, 'msg' => "Reciept Successfully Sent!"];
}
return response()->json($json);
}
////////////////////////////////////////////// CANCEL INVOICE ///////////////////////////////////////////////
public function cancelInvoice(Request $request) {
$json = [];
$date = date("Y-m-d");
// dd($request);
if (isset($request->bookingInoviceID) && isset($request->reservationID)) {
$invoiceStatus = BookingInvoice::where("id", $request->bookingInoviceID)->value('invoice_status');
if ($invoiceStatus && (($invoiceStatus == "4") || ($invoiceStatus == "5"))) {
$json = ['status' => 1, 'msg' => "You cannot update that invoice. Plase check invoice Status and ensure previous invoice has either been approved or cancelled"];
} else {
$updateArray = [
"payment_date" => $date,
"invoice_status" => 5,
"payment_term" => 'Null',
"evidence_payment_text" => "0",
"bank_details" => 'Null',
"amount_paid" => '0',
"nameofdepositor" => '',
];
$doUpdate = BookingInvoice::where("id", $request->bookingInoviceID)->update($updateArray);
$iDate = date('Y-m-d H:i:s');
$auditTrail = "| Invoice Updated By " . Auth::user()->name . " on $iDate with Total Amount paid $request->amountpaid |
";
$auditUpdate = DB::statement("UPDATE `booking_invoice` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$auditTrail' ELSE CONCAT(`auditTrail`, '$auditTrail') END WHERE `id` = '" . $request->bookingInoviceID . "' ");
//amount_paid
if ($request->payments == '5') {
$paid = DB::statement("UPDATE `booking_invoice` SET `cancelled_by` = '" . Auth::user()->id . "' WHERE `id` = '" . $request->bookingInoviceID . "' ");
$iauditTrail = "| Invoice Cancelled By " . Auth::user()->name . " on $iDate |
";
$auditUpdate = DB::statement("UPDATE `booking_invoice` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$iauditTrail' ELSE CONCAT(`auditTrail`, '$iauditTrail') END WHERE `id` = '" . $request->bookingInoviceID . "' ");
//Update Reservation
$ir_paid = DB::statement("UPDATE `reservations` SET `task_status` = 'cancelled' WHERE `reservation_id` = '" . $request->reservationID . "' ");
//Update Trip
$trips_paid = DB::statement("UPDATE `trips` SET `trip_status` = 'cancelled' WHERE `reservation_id` = '" . $request->reservationID . "' ");
//Addd Audit Trails to trips
$iiauditTrail = "| Invoice Cancelled By " . Auth::user()->name . " on $iDate with Invoice ID of $request->bookingInoviceID |
";
$iauditUpdate = DB::statement("UPDATE `trips` SET `auditTrail` = CASE WHEN auditTrail = '' THEN '$iiauditTrail' ELSE CONCAT(`auditTrail`, '$iiauditTrail') END WHERE `reservation_id` = '" . $request->reservationID . "' ");
}
$json = ['status' => 2, 'msg' => "Invoice Successfully Updated As Cancelled!"];
}
} else {
$json = ['status' => 3, 'msg' => "Error process page please try again!"];
}
return response()->json($json);
}
////////////////////////////////////////////// CANCEL INVOICE END ////////////////////////////////////////////
public function vehicleAvailabilitySchedule(Request $request){
$vehicle_availability = $this->vehicleAvailability($request);
$vehiclemakes = DB::table('vehiclemakes')->pluck('name');
return view('available_vehicles')->with(['available' => $vehicle_availability['available_vehicles'], 'hertz_companies' => $vehicle_availability['hertz_companies'], 'url' => $vehicle_availability['url'], 'vehiclemakes' => $vehiclemakes]);
}
public function companyVehicleAvailability(Request $request, $company_id){
$vehicle_availability = $this->vehicleAvailabilityByCompany($request, $company_id);
$vehiclemakes = DB::table('vehiclemakes')->pluck('name');
return view('available_vehicles')->with(['available' => $vehicle_availability['available_vehicles'], 'url' => $vehicle_availability['url'], 'vehiclemakes' => $vehiclemakes]);
}
}