File manager

File manager - Edit - /home/autoph/public_html/data03252025consolidation/src/Models/NatureOfBusiness.php

Back
<?php namespace App\Models; use App\Core\Database; use App\Core\Model; use App\Core\Sms; class NatureOfBusiness extends Model { public function getNatureOfBusinessCategoryCount($array_data) { $query = " SELECT snob.id,snob.name,COUNT(1) as count FROM (SELECT snob.id, IF(snob.category_id = 0,snob.id,snob.category_id) AS category_id, snob.name, snob.is_parent FROM customer c INNER JOIN vehicle v ON c.id = v.customer_record_id INNER JOIN sales s ON v.id = s.vehicle_id LEFT OUTER JOIN source_nature_of_business snob ON c.nature_of_business_id = snob.id LEFT OUTER JOIN source_company_dealer scd ON v.company_dealer_id = scd.id LEFT OUTER JOIN source_company sc ON scd.company_id = sc.id LEFT OUTER JOIN source_dealer sd ON scd.dealer_id = sd.id WHERE 1 /fix where statement/ AND s.activity_date != '' AND (snob.category_id != 0 OR snob.is_parent = 1) %s /company/ %s /dealer/ %s /snob/ %s /sales date/ GROUP BY c.id ORDER BY s.activity_date DESC) t1 INNER JOIN source_nature_of_business snob ON t1.category_id = snob.id WHERE snob.is_parent = 1 GROUP BY snob.id ORDER BY count DESC "; $result = Database::connect()->runBaseQuery(sprintf($query, $array_data['company_access'], $array_data['dealer_access'], $array_data['category'], $array_data['sales_date'])); return $result; } public function getNatureOfBusinessCategories() { $query = "SELECT * FROM source_nature_of_business WHERE 1 AND is_parent = 1 AND status = 1 ORDER BY name ASC"; $result = Database::connect()->runBaseQuery($query); return $result; } public function getNatureOfBusinessInfo($id) { $query = "SELECT * FROM source_nature_of_business WHERE 1 AND id = ?"; $queryType = "i"; $queryValue = array( $id, ); return Database::connect()->runQuery($query, $queryType, $queryValue); } public function getNatureOfBusinessDataQuery() { return "SELECT t1.* FROM ( SELECT c.id, IF( snob.category_id = 0, snob.id, snob.category_id ) AS category_id, IF( c.type = 1, CONCAT( c.last_name, ', ', c.first_name, ' ', c.middle_name ), c.corporation_name ) AS customer, c.type AS customer_type, cc.mobile_phone_1 AS mobile, cc.email_1 AS email, sc.code AS company, sd.code AS dealer, s.activity_date AS date, s.id AS sales_id, sfu.called, sfu.sms, snob.name AS nob FROM customer c INNER JOIN customer_contact cc ON c.id = cc.customer_id INNER JOIN vehicle v ON c.id = v.customer_record_id INNER JOIN sales s ON v.id = s.vehicle_id LEFT OUTER JOIN source_nature_of_business snob ON c.nature_of_business_id = snob.id LEFT OUTER JOIN source_company_dealer scd ON v.company_dealer_id = scd.id LEFT OUTER JOIN source_company sc ON scd.company_id = sc.id LEFT OUTER JOIN source_dealer sd ON scd.dealer_id = sd.id LEFT OUTER JOIN source_follow_ups sfu ON s.id = sfu.sales_id WHERE 1 /fix where statement/ AND s.activity_date != '' AND ( snob.category_id != 0 OR snob.is_parent = 1 ) %s /company/ %s /dealer/ %s /sales _date/ GROUP BY c.id %s /order/ ) t1 INNER JOIN source_nature_of_business snob ON t1.category_id = snob.id WHERE 1 %s %s /limit/ "; } public function getNatureOfBusinessDataCount($array_data) { $query_final = "SELECT COUNT(1) FROM (" . sprintf($this->getNatureOfBusinessDataQuery(), $array_data['company_access'], $array_data['dealer_access'], $array_data['sales_date'], $array_data['sort'], $array_data['category'], '') . ") t2"; $result = Database::connect()->selectBaseQuery($query_final); return $result; } public function getNatureOfBusinessData($array_data) { $query = sprintf($this->getNatureOfBusinessDataQuery(), $array_data['company_access'], $array_data['dealer_access'], $array_data['sales_date'], $array_data['sort'], $array_data['category'], $array_data['offset_limit']); // echo $query; exit; $result = Database::connect()->runBaseQuery($query); return $result; } public function tableAction($array_data) { $offset = $array_data['offset']; $limit = $array_data['limit']; $sales_id = $array_data['sales_id']; $fields = " sfu.`id`, DATE_FORMAT(sfu.`sms`, '%b %d, %Y / %h:%i:%s %p') AS sms, DATE_FORMAT(sfu.`called`, '%b %d, %Y / %h:%i:%s %p') AS called, concat(e1.`first_name`, ' ', e1.`last_name`) AS sms_done_by, concat(e2.`first_name`, ' ', e2.`last_name`) AS call_done_by, sfu.`remarks`, sfu.`status` "; $count = " COUNT(1) "; $json_arr['rows'] = array(); $action_list = "SELECT %s FROM `source_follow_ups` sfu INNER JOIN `employee` e1 ON e1.id = sfu.sms_done_by INNER JOIN `employee` e2 ON e2.id = sfu.call_done_by WHERE `sales_id` = '$sales_id'"; // echo sprintf($action_list, $fields); exit; $action_list_query = Database::connect()->runBaseQuery(sprintf($action_list, $fields)); $action_list_count = Database::connect()->selectBaseQuery(sprintf($action_list, $count)); $flag = 0; $count = 2; if(empty($action_list_query)){ while($count != 0){ if($flag == 0){ $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'action' => "SMS", 'data' => '<span><i class="fas fa-times-circle" style="color: #b22222;"></i></span>', 'done_by' => '<span><i class="fas fa-times-circle" style="color: #b22222;"></i></span>' ))); $flag = 1; } else { $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'id' => $row['id'], 'action' => "CALL", 'data' => '<span><i class="fas fa-times-circle" style="color: #b22222;"></i></span>', 'done_by' => '<span><i class="fas fa-times-circle" style="color: #b22222;"></i></span>' ))); $flag = 0; } $count--; } } else { foreach ($action_list_query AS $row) { while($count != 0){ if($flag == 0){ $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'id' => $row['id'], 'action' => "SMS", 'remarks' => $row['remarks'], 'data' => $row['sms'] == null ? '<span><i class="fas fa-times-circle" style="color: #b22222;"></i></span>' : $row['sms'], 'done_by' => $row['sms'] == null ? '<span><i class="fas fa-times-circle" style="color: #b22222;"></i></span>' : $row['sms_done_by'] ))); $flag = 1; } else { $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'id' => $row['id'], 'action' => "CALL", 'remarks' => $row['remarks'], 'data' => $row['called'] == null ? '<span><i class="fas fa-times-circle" style="color: #b22222;"></i></span>' : $row['called'], 'done_by' => $row['called'] == null ? '<span><i class="fas fa-times-circle" style="color: #b22222;"></i></span>' : $row['call_done_by'] ))); $flag = 0; } $count--; } } } $json_arr['total'] = $action_list_count; return $json_arr; } public function smsCall($array_data) { $type = $array_data['type']; $sales_id = $array_data['sales_id']; $phone = $array_data['phone']; $json_arr = array(); $count = Database::connect()->selectBaseQuery("SELECT COUNT(1) FROM `source_follow_ups` WHERE `sales_id` = '$sales_id'"); if($count > 0){ // has record if($type == "sms"){ $is_done = Database::connect()->selectBaseQuery("SELECT `sms` FROM `source_follow_ups` WHERE `sales_id` = '$sales_id'"); if(!is_null($is_done)){ $json_arr['status'] = 3; // done } else { $sms_status = $this->sendMessage($phone); if($sms_status == 0){ // send sms success $current_datetime = Database::connect()->selectBaseQuery("SELECT NOW()"); $current_user = $_SESSION['user']['id']; $update_query = "UPDATE `source_follow_ups` SET `sms` = '$current_datetime', `sms_done_by` = '$current_user' WHERE `sales_id` = '$sales_id'"; // echo $update_query; exit; $update = Database::connect()->updateBaseQuery($update_query); if($update > 0){ $json_arr['status'] = 0; // success } else { $json_arr['status'] = 1; // database failed } } else { $json_arr['status'] = 2; // sms failed } } } else { // call $is_done = Database::connect()->selectBaseQuery("SELECT `called` FROM `source_follow_ups` WHERE `sales_id` = '$sales_id'"); if(!is_null($is_done)){ $json_arr['status'] = 3; // done } else { $current_datetime = Database::connect()->selectBaseQuery("SELECT NOW()"); $current_user = $_SESSION['user']['id']; $update_query = "UPDATE `source_follow_ups` SET `called` = '$current_datetime', `call_done_by` = '$current_user' WHERE `sales_id` = '$sales_id'"; $update = Database::connect()->updateBaseQuery($update_query); if($update > 0){ $json_arr['status'] = 0; // success } else { $json_arr['status'] = 1; // failed } } } } else { if($type == "sms"){ $is_done = Database::connect()->selectBaseQuery("SELECT `sms` FROM `source_follow_ups` WHERE `sales_id` = '$sales_id'"); if($is_done != ""){ $json_arr['status'] = 3; // done } else { $sms_status = $this->sendMessage($phone); if($sms_status == 0){ $current_datetime = Database::connect()->selectBaseQuery("SELECT NOW()"); $current_user = $_SESSION['user']['id']; $insert_query = "INSERT INTO `source_follow_ups`(`sales_id`, `sms`, `sms_done_by`) VALUES('$sales_id', '$current_datetime', '$current_user')"; $insert = Database::connect()->updateBaseQuery($insert_query); if($insert > 0){ $json_arr['status'] = 0; // success } else { $json_arr['status'] = 1; // database failed } } else { $json_arr['status'] = 2; // sms failed } } } else { // call // echo "SELECT `called` FROM `source_follow_ups` WHERE `sales_id` = '$sales_id'"; exit; $is_done = Database::connect()->selectBaseQuery("SELECT `called` FROM `source_follow_ups` WHERE `sales_id` = '$sales_id'"); if($is_done != ""){ $json_arr['status'] = 3; // done } else { $current_datetime = Database::connect()->selectBaseQuery("SELECT NOW()"); $current_user = $_SESSION['user']['id']; $insert_query = "INSERT INTO `source_follow_ups`(`sales_id`, `called`, `call_done_by`) VALUES('$sales_id', '$current_datetime', '$current_user')"; $insert = Database::connect()->updateBaseQuery($insert_query); if($insert > 0){ $json_arr['status'] = 0; // success } else { $json_arr['status'] = 1; // failed } } } } // print_r($json_arr); exit; return $json_arr; } public function remarks($array_data) { $sales_id = $array_data['sales_id']; $remarks = $array_data['remarks']; $json_arr = array(); $is_existing = Database::connect()->selectBaseQuery("SELECT COUNT(1) FROM `source_follow_ups` WHERE `sales_id` = '$sales_id'"); if($is_existing > 0){ $current_datetime = Database::connect()->selectBaseQuery("SELECT DATE_FORMAT((SELECT NOW()), '%b %d, %Y / %h:%i:%s %p')"); $current_user = $_SESSION['user']['first_name'] . " " . $_SESSION['user']['last_name'] . " [" . $_SESSION['user']['id'] . "] "; $remarks_last_update = $current_user . $current_datetime; $query = "UPDATE `source_follow_ups` SET `remarks` = '$remarks', `remarks_last_update` = '$remarks_last_update' WHERE `sales_id` = '$sales_id'"; $update = Database::connect()->updateBaseQuery($query); if($update > 0){ $json_arr['status'] = 0; // update success } else { $json_arr['status'] = 1; // update failed } } else { // not existing $current_datetime = Database::connect()->selectBaseQuery("SELECT DATE_FORMAT((SELECT NOW()), '%b %d, %Y / %h:%i:%s %p')"); $current_user = $_SESSION['user']['first_name'] . " " . $_SESSION['user']['last_name'] . " [" . $_SESSION['user']['id'] . "] "; $remarks_last_update = $current_user . $current_datetime; $query = "INSERT INTO `source_follow_ups`(`sales_id`, `remarks`, `remarks_last_update`, `status`) VALUES('$sales_id', '$remarks', '$remarks_last_update', 1)"; // echo $query; exit; $insert = Database::connect()->updateBaseQuery($query); if($insert > 0){ $json_arr['status'] = 0; // insert success } else { $json_arr['status'] = 1; // insert failed } } return $json_arr; } public function sendMessage($phone) { $query = "SELECT `message_header`, `message_body`, `message_footer` FROM `auto_txt_blast` WHERE `id` = 5"; $message_contents = Database::connect()->runBaseQuery($query); $header = ""; $body = ""; $footer = ""; foreach($message_contents AS $row){ $header = $row['message_header']; $body = $row['message_body']; $footer = $row['message_footer']; } $full_message = $header . "\n\n" . $body . "\n\n" . $footer; $sms_data['message'] = $full_message; $sms_data['mobile'] = $phone; $sms_response = Sms::sendSms($sms_data); $status = null; if($sms_response == 0){ // success $status = 0; } else { $status = 1; } return $status; } public function loadRemarks($array_data) { $sales_id = $array_data['sales_id']; $json_arr = array(); $is_existing = Database::connect()->selectBaseQuery("SELECT COUNT(1) FROM `source_follow_ups` WHERE `sales_id` = '$sales_id'"); if($is_existing > 0){ $query = "SELECT `remarks_last_update` FROM `source_follow_ups` WHERE `sales_id` = $sales_id"; $remarks_log = Database::connect()->selectBaseQuery($query); if(!is_null($remarks_log)){ $json_arr['status'] = 0; // goods $json_arr['remarks_log'] = $remarks_log; $json_arr['remarks'] = Database::connect()->selectBaseQuery("SELECT `remarks` FROM `source_follow_ups` WHERE `sales_id` = $sales_id"); } else { $json_arr['status'] = 1; // not good } } else { $json_arr['status'] = 1; // not good } return $json_arr; } }

| ver. 1.4 | . | PHP 7.3.33 | Generation time: 0 | proxy | phpinfo | Settings