url = "/tickets" layout = "default" title = "Tickets" is_hidden = 0 == addJs('assets/vendor/SumoSelect/jquery.sumoselect.min.js'); $this->addCss('assets/vendor/SumoSelect/sumoselect.min.css'); $this->addJs('assets/js/new-ticket.v1.3.js'); $this->addJs('assets/vendor/FancyBox/jquery.fancybox.min.js'); $this->addCss('assets/vendor/FancyBox/jquery.fancybox.min.css'); $this->addJs('assets/vendor/AirDatepicker/js/datepicker.min.js'); $this->addJs('assets/vendor/AirDatepicker/js/i18n/datepicker.en.js'); $this->addCss('assets/vendor/AirDatepicker/css/datepicker.min.css'); $locations = Db::select('select * from custom_locations'); $this['locations'] = $locations; $this['cities'] = Db::select(' SELECT * FROM custom_cities '); $pendingMethod = ''; if($userAccessLevel == 'admin'){ $pendingMethod = 't.OPS="0" AND t.DEV="0" AND t.MAIN="0"'; }else if($userAccessLevel == 'ops' ){ $pendingMethod = 't.status = 1 AND t.OPS'; }else if($userAccessLevel == 'dev'){ $pendingMethod = 't.status = 1 AND t.DEV = 1'; }else if($userAccessLevel == 'maintenance'){ $pendingMethod = 't.responsible_user_id IS NULL'; }else if($userAccessLevel == 'store'){ }else if($userAccessLevel == 'technical'){ } if($userAccessLevel == 'admin' || $userAccessLevel == 'ops' || $userAccessLevel == 'dev'){ $allBrands = Db::select('SELECT DISTINCT(b.code), b.description FROM custom_userRestaurantAccess AS ura INNER JOIN custom_restaurants AS r ON ura.restaurantID = r.id INNER JOIN custom_brands AS b ON b.code = r.brandCode WHERE ura.userID = "'.$userId.'" '); $brandsPending = Db::select(' SELECT b.code, b.description, COUNT(b.code) AS numberOfPending FROM custom_tickets AS t INNER JOIN custom_restaurants AS r ON t.restaurant_id = r.id INNER JOIN custom_brands AS b ON b.code = r.brandCode WHERE '.$pendingMethod.' AND r.id IN (SELECT ua.restaurantID FROM custom_userRestaurantAccess AS ua INNER JOIN custom_restaurants AS r ON ua.restaurantID = r.id WHERE userID = "'.$userId.'") GROUP BY b.code '); }else if($userAccessLevel == 'maintenance'){ $allBrands = Db::select('SELECT * from custom_brands'); $brandsPending = Db::select(' SELECT b.code, b.description, COUNT(b.code) AS numberOfPending FROM custom_tickets AS t INNER JOIN custom_restaurants AS r ON t.restaurant_id = r.id INNER JOIN custom_brands AS b ON b.code = r.brandCode WHERE '.$pendingMethod.' AND t.MAIN = 1 GROUP BY b.code '); $newTickets = Db::select(' SELECT b.code, b.description, SUM(case when t.seen = 0 then 1 else 0 end) AS numberOfUnseen FROM custom_tickets AS t INNER JOIN custom_restaurants AS r ON t.restaurant_id = r.id INNER JOIN custom_brands AS b ON b.code = r.brandCode WHERE t.MAIN = 1 GROUP BY b.code '); } $pendingsCounter = 0; if($userAccessLevel == 'admin'|| $userAccessLevel == 'maintenance' || $userAccessLevel == 'ops' || $userAccessLevel == 'dev'){ foreach($allBrands as $key => $brand){ foreach ($brandsPending as $bP) { if($brand->code == $bP->code ){ $allBrands[$key]->numberOfPending = $bP->numberOfPending; $pendingsCounter += $bP->numberOfPending; } } } if($userAccessLevel == 'maintenance'){ $unseenCounter = 0; foreach($allBrands as $key => $brand){ foreach ($newTickets as $bP) { if($brand->code == $bP->code ){ $allBrands[$key]->numberOfUnseen = $bP->numberOfUnseen; $unseenCounter += $bP->numberOfUnseen; } } } $this['unseenCounter'] = $unseenCounter; } $this['brands'] = $allBrands; } $this['pendingsCounter'] = $pendingsCounter; $this['status'] = Db::select(' SELECT * FROM custom_status '); $this['users'] = Db::select('SELECT u.id, u.name FROM custom_users AS u WHERE accessLevelCode="technical" AND archiveUser= 0 '); } function onAddNewTicket(){ //get the values sent from ajax $image = $_POST['image']; $location = $_POST['location']; $restaurant = $_POST['restaurant']; $comment = $_POST['comment']; $isImportant = $_POST['isImportant']; $date_of_creation = date("Y-m-d"); if($isImportant == 1){ //wrap the mysql call into a try and catch so that we can catch any errors try { Db::insert("INSERT INTO custom_tickets(MAIN, photo, restaurant_id, location_code, comments, date_of_creation, important, admin_comments, main_comments, technician_comments) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",[1,$image,$restaurant,$location,$comment, $date_of_creation, $isImportant, "", "", ""]); $res = Db::select("select code, name from custom_restaurants where id='".$restaurant."'"); // Log::info($res); $emailAddresses = Db::select('select email from custom_users where accessLevelCode = "maintenance" '); $length = count($emailAddresses); $associativeArray = array(); for ($i =0; $i < $length; $i++ ) { $associativeArray[$emailAddresses[$i]->email] = '' ; } $vars = ['code' => $res[0]->code, 'name' => $res[0]->name, 'comments' => $comment, 'date' => date("d/m/Y"), 'location' => $location, 'photo' => $image]; // Log::info($associativeArray); Mail::sendTo($associativeArray, 'newTicket', $vars); Flash::success('Settings successfully saved!'); return 'ok'; } catch (\PDOException $e) { # do something or render a custom error page //\Flash::success('flash from ajax handler'); //return ['#flashDiv' => $this->renderPartial('flashMessage')]; Log::info("Cannot add ticket"); Log::info($image); Log::info($e); Log::info($restaurant. ', '.$location. ', '.$comment. ', '. $date_of_creation. ', '. $isImportant); return 'There was an error with adding the Ticket to the database!'; } }else{ //wrap the mysql call into a try and catch so that we can catch any errors try { Db::insert("INSERT INTO custom_tickets(photo, restaurant_id, location_code, comments, date_of_creation, important, admin_comments, main_comments, technician_comments) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)",[$image,$restaurant,$location,$comment, $date_of_creation, $isImportant, "", "", ""]); Flash::success('Settings successfully saved!'); return 'ok'; } catch (\PDOException $e) { # do something or render a custom error page //\Flash::success('flash from ajax handler'); //return ['#flashDiv' => $this->renderPartial('flashMessage')]; Log::info("Cannot add ticket 2"); Log::info($e); Log::info($restaurant. ', '.$location. ', '.$comment. ', '. $date_of_creation. ', '. $isImportant); Log::info($image); return 'There was an error with adding the Ticket to the database!'; } } } function onDeleteUser(){ $id = $_POST['ticketID']; try{ Db::table('custom_tickets') ->where('id', $id) ->update([ "status" => 5]); return "ok"; }catch (\PDOException $e){ return "error"; } /* $respUser = Db::select('SELECT responsible_user_id FROM custom_tickets WHERE id="'.$id.'"'); Log::info($respUser[0]->responsible_user_id); if($respUser[0]->responsible_user_id == null || $respUser[0]->responsible_user_id == ""){ Db::select('DELETE FROM custom_tickets WHERE id="'.$id.'"'); return ['#loadTickets' => $this->renderPartial('loadTickets.htm', [ ])]; }else{ return "error"; }*/ //first delete all restaurants linked to the account and then delete the account } function onLoadImage(){ $id = $_POST['id']; $photo = Db::select('select photo from custom_tickets WHERE id="'.$id.'"'); return $photo; } function onUpdateCheckbox(){ $id = $_POST['id']; $dep = $_POST['dep']; $checked = $_POST['checked']; // $photo = Db::update('select photo from custom_tickets WHERE id="'.$id.'"'); Db::table('custom_tickets') ->where('id', $id) ->update([ $dep => $checked]); } function onUpdateDates(){ $id = $_POST['id']; $date = $_POST['date']; $pieces = explode(" - ", $date); if( count($pieces) == 1 ){ $startDate = date("Y-m-d", strtotime($pieces[0])); $endDate = $startDate; }else{ $startDate = date("Y-m-d", strtotime($pieces[0])); $endDate = date("Y-m-d", strtotime($pieces[1])); } // $photo = Db::update('select photo from custom_tickets WHERE id="'.$id.'"'); try{ Db::table('custom_tickets') ->where('id', $id) ->update([ "execution_date_from" => $startDate, "execution_date_to" => $endDate]); Db::table('custom_tickets') ->where('id', $id) ->update([ "status" => 1]); return "ok"; }catch (\PDOException $e){ return "error"; } } function onUpdateUser(){ $id = $_POST['id']; $userID = $_POST['userID']; try{ Db::table('custom_tickets') ->where('id', $id) ->update([ "responsible_user_id" => $userID, "seen" => 1]); return "ok"; }catch (\PDOException $e){ return "error"; } } function onSendEmail(){ $id = $_POST['id']; $brandCode = $_POST['brandCode']; $otherDev = $_POST['otherDev']; try{ $emailAddresses = Db::select('select ud.email from custom_user_department AS ud WHERE ud.brand_id= "'.$brandCode.'" AND ud.resp_department_id="'.$otherDev.'"'); $ticketInfo = Db::select(' SELECT t.photo, t.comments, t.date_of_creation, r.code, r.name , l.description FROM custom_tickets AS t INNER JOIN custom_restaurants AS r ON t.restaurant_id = r.id INNER JOIN custom_locations AS l ON t.location_code = l.code WHERE t.id= "'.$id.'"'); //return $ticketInfo; if(empty($emailAddresses)){ return "error1"; }else{ $length = count($emailAddresses); $associativeArray = array(); for ($i =0; $i < $length; $i++ ) { $associativeArray[$emailAddresses[$i]->email] = '' ; } $vars = ['code' => $ticketInfo[0]->code, 'name' => $ticketInfo[0]->name, 'comments' => $ticketInfo[0]->comments, 'date' => $ticketInfo[0]->date_of_creation, 'location' => $ticketInfo[0]->description, 'photo' => $ticketInfo[0]->photo]; Mail::sendTo($associativeArray, 'newTicket', $vars); /*Mail::send('newTicket', $vars, function($message) { $message->to($associativeArray, 'Admin Person'); $message->subject('This is a reminder'); }); */ try{ if($otherDev == 1){ $column="OPS"; }else if($otherDev == 2){ $column="DEV"; } Db::table('custom_tickets') ->where('id', $id) ->update([ $column => 1]); return "ok"; }catch (\PDOException $e){ return "error2"; } } }catch (\PDOException $e){ return "error"; } } function onLoadCities(){ $brandCode = $_POST['brandCode']; $userId = Session::get('userID'); $userAccessLevel = Session::get('userAccessLevel'); $pendingMethod = ''; if($userAccessLevel == 'admin'){ $pendingMethod = 't.OPS="0" AND t.DEV="0" AND t.MAIN="0"'; }else if($userAccessLevel == 'ops' ){ $pendingMethod = 't.status = 1 AND t.OPS'; }else if($userAccessLevel == 'dev'){ $pendingMethod = 't.status = 1 AND t.DEV = 1'; }else if($userAccessLevel == 'maintenance'){ $pendingMethod = 't.responsible_user_id IS NULL'; }else if($userAccessLevel == 'store'){ }else if($userAccessLevel == 'technical'){ } if($userAccessLevel == 'admin' || $userAccessLevel == 'ops' || $userAccessLevel == 'dev'){ $allCities = Db::select(' SELECT DISTINCT(c.cityDescription), c.code FROM custom_userRestaurantAccess AS ura LEFT JOIN custom_restaurants AS r ON r.id = ura.restaurantID LEFT JOIN custom_cities AS c ON c.code = r.city WHERE ura.userID="'.$userId.'" AND r.brandCode = "'.$brandCode.'" '); $cities = Db::select(' SELECT r.city, c.cityDescription, COUNT(r.city) AS numberOfPending FROM custom_tickets AS t INNER JOIN custom_restaurants AS r ON t.restaurant_id = r.id INNER JOIN custom_brands AS b ON b.code = r.brandCode INNER JOIN custom_cities AS c ON c.code = r.city WHERE '.$pendingMethod.' AND b.code="'.$brandCode.'" AND r.id IN (SELECT custom_userRestaurantAccess.restaurantID FROM custom_userRestaurantAccess WHERE userID = "'.$userId.'") GROUP BY r.city '); }else{ $allCities = Db::select(' SELECT DISTINCT(r.city), c.code, c.cityDescription FROM custom_restaurants AS r INNER JOIN custom_cities AS c ON c.code = r.city WHERE r.brandCode = "'.$brandCode.'" '); $cities = Db::select(' SELECT r.city, c.cityDescription, COUNT(r.city) AS numberOfPending FROM custom_tickets AS t INNER JOIN custom_restaurants AS r ON t.restaurant_id = r.id INNER JOIN custom_brands AS b ON b.code = r.brandCode INNER JOIN custom_cities AS c ON c.code = r.city WHERE '.$pendingMethod.' AND b.code="'.$brandCode.'" AND t.MAIN = 1 GROUP BY r.city '); $newTickets = Db::select(' SELECT r.city, c.cityDescription, SUM(case when t.seen = 0 then 1 else 0 end) AS numberOfUnseen FROM custom_tickets AS t INNER JOIN custom_restaurants AS r ON t.restaurant_id = r.id INNER JOIN custom_brands AS b ON b.code = r.brandCode INNER JOIN custom_cities AS c ON c.code = r.city WHERE b.code="'.$brandCode.'" AND t.MAIN = 1 GROUP BY r.city '); } //we add teh number of pendings on each brand foreach($allCities as $key => $city){ foreach ($cities as $c) { if($city->code == $c->city ){ $allCities[$key]->numberOfPending = $c->numberOfPending; } } } if($userAccessLevel == 'maintenance'){ foreach($allCities as $key => $city){ foreach ($newTickets as $c) { if($city->code == $c->city ){ $allCities[$key]->numberOfUnseen = $c->numberOfUnseen; } } } } return $allCities; } function onLoadRestaurants(){ $userId = Session::get('userID'); $brandCode = $_POST['brandCode']; $cityCode = $_POST['cityCode']; $userAccessLevel = Session::get('userAccessLevel'); $pendingMethod = ''; if($userAccessLevel == 'admin'){ $pendingMethod = 't.OPS="0" AND t.DEV="0" AND t.MAIN="0"'; }else if($userAccessLevel == 'ops' ){ $pendingMethod = 't.status = 1 AND t.OPS'; }else if($userAccessLevel == 'dev'){ $pendingMethod = 't.status = 1 AND t.DEV'; }else if($userAccessLevel == 'maintenance'){ $pendingMethod = 't.responsible_user_id IS NULL'; }else if($userAccessLevel == 'store'){ }else if($userAccessLevel == 'technical'){ } if($userAccessLevel == 'admin' || $userAccessLevel == 'ops' || $userAccessLevel == 'dev'){ $allRestaurants = Db::select(' SELECT DISTINCT(r.id), r.name, r.code FROM custom_userRestaurantAccess AS ura LEFT JOIN custom_restaurants AS r ON r.id = ura.restaurantID WHERE ura.userID="'.$userId.'" AND r.city="'.$cityCode.'" AND r.brandCode="'.$brandCode.'" '); $restaurants = Db::select(' SELECT r.id, r.code, r.name, r.city, COUNT(r.id) AS numberOfPending FROM custom_tickets AS t INNER JOIN custom_restaurants AS r ON t.restaurant_id = r.id INNER JOIN custom_brands AS b ON b.code = r.brandCode WHERE '.$pendingMethod.' AND b.code="'.$brandCode.'" AND r.city="'.$cityCode.'" GROUP BY r.id '); }else{ $allRestaurants = Db::select(' SELECT id,code,name FROM custom_restaurants WHERE city="'.$cityCode.'" AND brandCode="'.$brandCode.'" '); $restaurants = Db::select(' SELECT r.id, r.code, r.name, r.city, COUNT(r.id) AS numberOfPending FROM custom_tickets AS t INNER JOIN custom_restaurants AS r ON t.restaurant_id = r.id INNER JOIN custom_brands AS b ON b.code = r.brandCode WHERE '.$pendingMethod.' AND b.code="'.$brandCode.'" AND r.city="'.$cityCode.'" AND t.MAIN = 1 GROUP BY r.id '); $newTickets = Db::select(' SELECT r.id, r.code, r.name, r.city, SUM(case when t.seen = 0 then 1 else 0 end) AS numberOfUnseen FROM custom_tickets AS t INNER JOIN custom_restaurants AS r ON t.restaurant_id = r.id INNER JOIN custom_brands AS b ON b.code = r.brandCode INNER JOIN custom_cities AS c ON c.code = r.city WHERE b.code="'.$brandCode.'" AND r.city="'.$cityCode.'" AND t.MAIN = 1 GROUP BY r.id '); } foreach($allRestaurants as $key => $rest){ foreach ($restaurants as $r) { if($rest->id == $r->id ){ $allRestaurants[$key]->numberOfPending = $r->numberOfPending; } } } if($userAccessLevel == 'maintenance'){ foreach($allRestaurants as $key => $rest){ foreach ($newTickets as $r) { if($rest->id == $r->id ){ $allRestaurants[$key]->numberOfUnseen = $r->numberOfUnseen; } } } } return $allRestaurants; } function onLoadTickets(){ $brandCode = $_POST['brandCode']; $cityCode = $_POST['cityCode']; $restaurant = $_POST['restaurant']; $status = $_POST['status']; $noFilter = $_POST['noFilter']; $ops = $_POST['ops']; $dev = $_POST['dev']; $main = $_POST['main']; return ['#loadTickets' => $this->renderPartial('loadTickets.htm', [ 'brandCode' => $brandCode, 'cityCode' => $cityCode, 'restaurant' => $restaurant, 'status' => $status, 'noFilter' => $noFilter, 'ops' => $ops, 'dev' => $dev, 'main' => $main ])]; } function onUpdateAdminComment(){ $id = $_POST['id']; $comment = $_POST['comment']; try{ Db::table('custom_tickets') ->where('id', $id) ->update([ "admin_comments" => $comment]); return "ok"; }catch (\PDOException $e){ return "error"; } } function onUpdateMainComment(){ $id = $_POST['id']; $comment = $_POST['comment']; $isImportant = Db::select(' SELECT important, restaurant_id FROM custom_tickets WHERE id='.$id.' '); try{ Db::table('custom_tickets') ->where('id', $id) ->update([ "main_comments" => $comment]); if($isImportant[0]->important == 1){ $emailAddresses = Db::select(' SELECT * FROM custom_userRestaurantAccess AS ura INNER JOIN custom_users AS cu ON cu.id = ura.userID WHERE ura.restaurantID='.$isImportant[0]->restaurant_id.' AND cu.accessLevelCode="store" AND cu.doNotSendEmail=0'); $length = count($emailAddresses); if($length >0){ $associativeArray = array(); for ($i =0; $i < $length; $i++ ) { $associativeArray[$emailAddresses[$i]->email] = '' ; } // Log::info('res id'); // Log::info($isImportant[0]->restaurant_id); // Log::info($associativeArray); $vars = ['id' => $id, 'comment' => $comment]; Mail::sendTo($associativeArray, 'importantMainComment', $vars); } } return "ok"; }catch (\PDOException $e){ return "error"; } } function onUpdateStatus(){ $id = $_POST['id']; $status = $_POST['status']; try{ Db::table('custom_tickets') ->where('id', $id) ->update([ "status" => $status]); return "ok"; }catch (\PDOException $e){ return "error"; } } function onUpdateShouldDelete(){ $id = $_POST['id']; $shouldDelete = $_POST['shouldDelete']; try{ Db::table('custom_tickets') ->where('id', $id) ->update([ "shouldDelete" => $shouldDelete]); return "ok"; }catch (\PDOException $e){ return "error"; } } function onUpdateImportant(){ $id = $_POST['id']; $important = $_POST['status']; try{ Db::table('custom_tickets') ->where('id', $id) ->update([ "important" => $important]); return "ok"; }catch (\PDOException $e){ return "error"; } } function onMassUpdate(){ $ids = $_POST['ids']; $status = $_POST['status']; $technician = $_POST['technician']; $date = $_POST['date']; $comments = $_POST['comments']; $dateFormatted = date("Y-m-d", strtotime($date)); /* Log::info('------'); Log::info('Status ='.$status); Log::info($technician); Log::info($date); Log::info($comments); */ $idsArr = json_decode($ids); if($status !== '-1'){ // Log::info('STATUS'); try{ Db::table('custom_tickets') ->whereIn('id', $idsArr) ->update([ "status" => $status]); }catch (\PDOException $e){ return $e; } } if($technician !== '-1'){ // Log::info('TECHNICIAN'); try{ Db::table('custom_tickets') ->whereIn('id', $idsArr) ->update([ "responsible_user_id" => $technician, "seen" => 1]); }catch (\PDOException $e){ return $e; } } if($date !== ''){ // Log::info('DATE'); try{ Db::table('custom_tickets') ->whereIn('id', $idsArr) ->update([ "execution_date_from" => $dateFormatted, "execution_date_to" => $dateFormatted]); }catch (\PDOException $e){ return $e; } } if($comments !== ''){ //Log::info('COMMENTS'); try{ Db::table('custom_tickets') ->whereIn('id', $idsArr) ->update([ "main_comments" => $comments]); }catch (\PDOException $e){ return $e; } } // return implode(',', array_map('intval', $idsArr)); } function onMarkAsSeen(){ $id = $_POST['id']; $markAsSeen = $_POST['markAsSeen']; try{ Db::table('custom_tickets') ->where('id', $id) ->update([ "seen" => $markAsSeen]); return "ok"; }catch (\PDOException $e){ return "error"; } } ?> ==

Tickets

{% if userAccessLevel is same as("admin") or userAccessLevel is same as("maintenance") or userAccessLevel is same as("ops") or userAccessLevel is same as("dev") %}
{# #}
{% if userAccessLevel is same as("admin") %}
{% endif %}
{% endif %} {% if userAccessLevel is same as("admin") or userAccessLevel is same as("maintenance") %}

{% else %}
{% endif %}
{% partial 'loadTickets' %}