File manager

File manager - Edit - /home/autoph/public_html/data03252025consolidation/app/table/finance_table.php

Back
<?php include_once("../../cfg/db.php"); include_once("../../api/controllers/utility.php"); $utility = new Utility(); $json_arr['rows'] = array(); $granted_company_ids = $utility->isNotEmpty($_SESSION['user']['company_permissions_implode']) ? $_SESSION['user']['company_permissions_implode'] : '-1' ; $type = $db->escape($_GET['type']); $search = ""; $offset = ""; $offset_limit = ""; if(isset($_GET['search'])){ $search = $db->escape(trim($_GET['search'])); } if(isset($_GET['offset'])){ $offset = $db->escape(trim($_GET['offset'])); } if(isset($_GET['limit'])){ $limit = $db->escape(trim($_GET['limit'])); $offset_limit = " LIMIT ". $offset.",".$limit; } if($type == 1){ // finance table list $start_date = ""; $end_date = ""; $company = ""; $dealer = ""; $date_q = ""; $filter_box_q = ""; $start_date = $db->escape($_GET['start_date']); $end_date = $db->escape($_GET['end_date']); $filter_box = ""; if(isset($_GET['start_date']) && $_GET['start_date'] != ""){ //policy start date starting range $start_date = $db->escape($_GET['start_date']); } if(isset($_GET['end_date']) && $_GET['end_date'] != ""){ //policy start date ending range $end_date = $db->escape($_GET['end_date']); $date_q = " AND f.policy_start_date BETWEEN '$start_date' AND '$end_date' "; } if(isset($_GET['company']) && $_GET['company'] != 0){ $company = $db->escape($_GET['company']); $company = " AND f.company_id = '$company' "; } else { $company = " AND f.company_id IN(" . $granted_company_ids . ") "; } $dealer = $db->escape($_GET['dealer']); if($dealer != 0 && $dealer != ""){ $dealer = " AND f.dealer_id = '$dealer' "; } else { $dealer = ""; } if(isset($_GET['filter_box']) && $_GET['filter_box'] != 0){ $filter_box = $db->escape($_GET['filter_box']); $current_date = $db->select("SELECT DATE_FORMAT(NOW(),'%Y-%m-%d')"); if($filter_box == 1){ // categorized $filter_box_q = " GROUP BY f.plate_cs_number1, f.plate_cs_number2"; } else if($filter_box == 2){ // individual insurance $filter_box_q = ""; } else if($filter_box == 3){ // expiring this month $fdate = date('Y-m-01'); $ldate = date('Y-m-t'); $filter_box_q = " AND f.policy_end_date BETWEEN '$fdate' AND '$ldate' GROUP BY f.plate_cs_number1, f.plate_cs_number2"; } else if($filter_box == 4){ // added today $filter_box_q = " AND DATE_FORMAT(f.date_uploaded, '%%Y-%%m-%%d') = '$current_date'"; } } else { $filter_box_q = " GROUP BY f.plate_cs_number1, f.plate_cs_number2"; } $count = " COUNT() "; $fields = ""; if($filter_box == 2){ $fields = " f.id, f.plate_cs_number1, f.plate_cs_number2, f.policy_number, f.customer_fullname, DATE_FORMAT(f.policy_start_date, '%b %d, %Y') AS policy_start_date, DATE_FORMAT(f.policy_end_date, '%b %d, %Y') AS policy_end_date, sc.code AS company, sd.name AS dealer "; } else if($filter_box == 4){ $fields = " f.id, f.plate_cs_number1, f.plate_cs_number2, f.policy_number, f.customer_fullname, DATE_FORMAT(f.policy_start_date, '%b %d, %Y') AS policy_start_date, DATE_FORMAT(f.policy_end_date, '%b %d, %Y') AS policy_end_date, sc.code AS company, sd.name AS dealer "; } else { $fields = " f.id, f.plate_cs_number1, f.plate_cs_number2, f.policy_number, f.customer_fullname, DATE_FORMAT(f.policy_start_date, '%b %d, %Y') AS policy_start_date, DATE_FORMAT(MAX(f.policy_end_date), '%b %d, %Y') AS policy_end_date, sc.code AS company, sd.name AS dealer "; } $finance_table_query = "SELECT %s FROM finance f INNER JOIN source_company sc ON sc.id = f.company_id INNER JOIN source_dealer sd ON sd.id = f.dealer_id WHERE (concat(f.plate_cs_number1, f.plate_cs_number2, f.policy_number, f.customer_fullname) LIKE '%%%s%%') AND f.status = 1 " . $company . $dealer . $date_q . $filter_box_q . " ORDER BY f.date_uploaded DESC"; // echo sprintf($finance_table_query, $fields, $search); return; $finance_list_query = $db->sql_query(sprintf($finance_table_query, $fields, $search) . $offset_limit); // $finance_list_count = $db->select("SELECT COUNT() FROM (" . sprintf($finance_table_query, $count, $search) . ") AS COUNT"); $finance_list_count = 0; if($filter_box == 2){ $finance_list_count = $db->select(sprintf($finance_table_query, $count, $search)); } else if($filter_box == 4){ $finance_list_count = $db->select(sprintf($finance_table_query, $count, $search)); } else { $finance_list_count = $db->select("SELECT COUNT() FROM (" . sprintf($finance_table_query, $count, $search) . ") AS COUNT"); } while($row = $finance_list_query->fetch_assoc()) { $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'plate_cs_number1'=>$row['plate_cs_number1'], 'plate_cs_number'=>$row['plate_cs_number2'] != "" ? $row['plate_cs_number1'] . " / " . $row['plate_cs_number2'] : $row['plate_cs_number1'], 'policy_number'=>$row['policy_number'], 'customer_fullname'=>$row['customer_fullname'], 'inception_date'=>$row['policy_start_date'] . " - " . $row['policy_end_date'], 'company'=>$row['company'], 'dealer'=>$row['dealer'] ))); } $json_arr['total'] = $finance_list_count; echo json_encode($json_arr); } else if($type == 2){ // ---------------------------------------------- finance_information.js -->> $plate_cs_number_1 = ""; $plate_cs_number_2 = ""; $where_clause_query = ""; $plate_cs_number = $db->escape($_GET['plate_cs_number']); if(strpos($plate_cs_number, '/') > 0){ $multi_data = array(); $multi_data = explode("/", $plate_cs_number); $plate_cs_number_1 = $multi_data[0]; $plate_cs_number_2 = $multi_data[1]; $where_clause_query = "(f.plate_cs_number1 = '$plate_cs_number_1' OR f.plate_cs_number1 = '$plate_cs_number_2' OR f.plate_cs_number2 = '$plate_cs_number_1' OR f.plate_cs_number2 = '$plate_cs_number_2') "; } else { $plate_cs_number_1 = $plate_cs_number; $where_clause_query = "(f.plate_cs_number1 = '$plate_cs_number_1' OR f.plate_cs_number2 = '$plate_cs_number_1') "; } $count = " COUNT() "; $fields = " f.id AS record_id, f.plate_cs_number1, f.plate_cs_number2, f.policy_number, f.customer_fullname, f.customer_email, fsb.name AS brand, fibt.name AS insurance_type, fip.name AS insurance_provider, f.customer_mobile_number, DATE_FORMAT(f.policy_issue_date, '%b %d, %Y') AS policy_issue_date, DATE_FORMAT(f.policy_start_date, '%b %d, %Y') AS policy_start_date, DATE_FORMAT(f.policy_end_date, '%b %d, %Y') AS policy_end_date, f.sales_consultant "; $finance_information_query = "SELECT %s FROM finance f INNER JOIN finance_ins_business_type fibt ON fibt.id = f.insurance_business_type INNER JOIN finance_ins_provider fip ON fip.id = f.insurance_provider INNER JOIN finance_source_brand fsb ON fsb.id = f.brand_id WHERE f.status = 1 AND " . $where_clause_query . " ORDER BY f.policy_issue_date ASC"; // echo sprintf($finance_information_query, $fields); return; $finance_information_query_list = $db->sql_query(sprintf($finance_information_query, $fields) . $offset_limit); $finance_information_count = $db->select(sprintf($finance_information_query, $count)); $ctr = 1; while($row = $finance_information_query_list->fetch_assoc()) { $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'no'=>$ctr, 'finance_id'=>$row['record_id'], 'policy_number'=>$row['policy_number'], 'customer_fullname'=>$row['customer_fullname'], 'customer_mobile_number'=>$row['customer_mobile_number'], 'customer_email'=>$row['customer_email'], 'brand'=>$row['brand'], 'insurance_type'=>$row['insurance_type'], 'insurance_provider'=>$row['insurance_provider'], 'policy_issue_date'=>$row['policy_issue_date'], 'policy_start_end_date'=>$row['policy_start_date'] . " - " . $row['policy_end_date'] ))); $ctr++; } $json_arr['total'] = $finance_information_count; echo json_encode($json_arr); } else if($type == 3){ // -------------------------------------------- finance_remaining_days.js -->> $finance_id = $db->escape($_GET['fin_id']); // $plate_cs_number = $db->escape($_GET['plate_cs_number']); // $policy_issue_date = $db->escape($_GET['policy_issue_date']); $rem_day = array(); $sms_status = ""; $remaining_days = $db->select("SELECT remaining_days FROM auto_txt_blast WHERE id = 2"); $rem_day = explode(",", $remaining_days); $policy_end_date = $db->select("SELECT policy_end_date FROM finance WHERE id = '$finance_id'"); $date_diff_now = $db->select("SELECT DATEDIFF(NOW(), '2021-11-23')"); for($i=0; $i < count($rem_day); $i++){ $remaining = $rem_day[$i]; $var_date = $db->select("SELECT DATE_FORMAT(DATE_SUB('" . $policy_end_date . "', INTERVAL " . $remaining . " DAY), '%b %d, %Y')"); $var_date_db = $db->select("SELECT DATE_FORMAT(DATE_SUB('" . $policy_end_date . "', INTERVAL " . $remaining . " DAY), '%Y-%m-%d')"); $var_date_now = $db->select("SELECT DATEDIFF(NOW(), '$var_date_db')"); $current_date = $db->select("SELECT CURDATE()"); $count = " COUNT() "; $fields = " tbirr.sms_response "; $sms_query = "SELECT %s FROM `txtblast_insurance_reminder_record` tbirr INNER JOIN `finance` f ON f.id = tbirr.finance_id WHERE tbirr.finance_id = '$finance_id' AND f.policy_end_date = '$policy_end_date' AND tbirr.reminder_type = '$remaining'"; // echo sprintf($sms_query, $fields); exit; $get_record_count = $db->select(sprintf($sms_query, $count)); if($get_record_count > 0){ $sms_status = $db->select(sprintf($sms_query, $fields)); } else { // echo "".$var_date_db." > ".$current_date.""; return; if($var_date_db < $current_date){ $sms_status = 200; // passed } else { $sms_status = 100; // waiting } } $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'reminder_type'=>$rem_day[$i] . "-day reminder", 'reminder_date'=>$var_date, 'sms_status'=>$sms_status == 0 ? "<span class='badge bg-success'>SMS SENT</span>" : ($sms_status == 1 ? "<span class='badge bg-danger'>INVALID NUMBER</span>" : ($sms_status == 2 ? "<span class='badge bg-danger'>UNSUPPORTED #</span>" : ($sms_status == 30 ? "<span class='badge bg-info'>UNSUBSCRIBED</span>" : ($sms_status == -2 ? "<span class='badge bg-info'>SMS send feature is not yet activated</span>" : ($sms_status == -1 ? "<span class='badge bg-warning'>PENDING</span>" : ($sms_status == 200 ? "<span class='badge bg-secondary'>PASSED</span>" : ($sms_status == -3 ? "<span class='badge bg-dark'>SKIPPED</span>" : "<span class='badge bg-primary'>WAITING</span>"))))))) ))); $sms_status = ""; } echo json_encode($json_arr); } else if($type == 4){ // --------------------------------------- FINANCE REPORT : dist/js/finance/finance_reports.js -->> $policy_end_date_start = ""; $policy_end_date_end = ""; $filter_date = ""; $company = ""; $dealer = ""; $brand = ""; $model = ""; $variant = ""; $remaining_days = ""; $inactive = ""; $fields = ""; $set = 0; // 1-rem days, 2-expiration date, 3-inactive $current_year = $db->select("SELECT YEAR(CURDATE()) AS current_year"); $previous_year = $current_year - 1; $current_date = $db->select("SELECT CURDATE()"); if(isset($_GET['start_date']) && $_GET['start_date'] != ""){ $policy_end_date_start = $db->escape($_GET['start_date']); } if(isset($_GET['end_date']) && $_GET['end_date'] != ""){ $policy_end_date_end = $db->escape($_GET['end_date']); } if(isset($_GET['company']) && $_GET['company'] != 0){ $company = $db->escape($_GET['company']); $company = " AND f.company_id = '$company' "; } else { $company = " AND f.company_id IN (".$granted_company_ids.")"; } if(isset($_GET['dealer']) && $_GET['dealer'] != 0){ $dealer = $db->escape($_GET['dealer']); $dealer = " AND f.dealer_id = '$dealer' "; } if(isset($_GET['brand']) && $_GET['brand'] != 0){ $brand = $db->escape($_GET['brand']); $brand = " AND t1.brand_id = '$brand' "; } if(isset($_GET['model']) && $_GET['model'] != 0){ $model = $db->escape($_GET['model']); $model = " AND t1.model_id = '$model' "; } if(isset($_GET['variant']) && $_GET['variant'] != 0){ $variant = $db->escape($_GET['variant']); $variant = " AND t1.variant_id = '$variant'"; } if(isset($_GET['remaining_days']) && $_GET['remaining_days'] != 0){ $remaining_days = $db->escape($_GET['remaining_days']); $rem_day = array(); $rem_day = explode(",", $remaining_days); $remaining_start_date = $db->select("SELECT DATE_ADD(CURDATE(), INTERVAL ".$rem_day[0]." DAY)"); $remaining_end_date = $db->select("SELECT DATE_ADD(CURDATE(), INTERVAL ".$rem_day[1]." DAY)"); $remaining_days = " AND f.policy_end_date BETWEEN '$remaining_start_date' AND '$remaining_end_date' "; } else { $remaining_days = ""; } if($policy_end_date_start != ""){ $filter_date = " AND DATE_FORMAT(policy_end_date, '%%Y-%%m-%%d') BETWEEN '$policy_end_date_start' AND '$policy_end_date_end' "; } if(isset($_GET['inactive']) && $_GET['inactive'] != 0){ $set = 3; $inactive = $db->escape($_GET['inactive']); $current_date = $db->select("SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS today"); $sub_date = $db->select("SELECT DATE_SUB(CURDATE(), INTERVAL " . $inactive . " DAY) AS day;"); if($inactive == 1){ // All inactive $inactive = " HAVING max_year <= '$current_date'"; } else { $inactive = " HAVING max_year <= '$sub_date'"; // $filter_date = " AND f.policy_end_date < '$sub_date' "; } } $count = " COUNT() "; if($set == 3){ // inactive filter is selected $fields = " MAX(f.policy_end_date) as max_year, f.id, f.customer_fullname, f.plate_cs_number1, f.plate_cs_number2, f.policy_number, sc.code AS company, sd.name AS dealer, DATE_FORMAT(f.policy_end_date, '%b %d, %Y') AS policy_end_date,vehicle_release_year,finance_ins_provider.name as provider,f.bank_name,scm.name as model,scmv.name as variant"; } else { $fields = " f.id, f.customer_fullname, f.plate_cs_number1, f.plate_cs_number2, f.policy_number, sc.code AS company, sd.name AS dealer, DATE_FORMAT(f.policy_end_date, '%b %d, %Y') AS policy_end_date,v.vehicle_release_year,finance_ins_provider.name as provider,f.bank_name,scm.name as model,scmv.name as variant "; } $query = "SELECT %s FROM finance f INNER JOIN source_company sc ON sc.id = f.company_id INNER JOIN source_dealer sd ON sd.id = f.dealer_id LEFT JOIN finance_ins_provider ON f.insurance_provider = finance_ins_provider.id LEFT JOIN vehicle v ON f.plate_cs_number1 = v.plate_number -- OR f.plate_cs_number1 = v.conduction_sticker LEFT OUTER JOIN source_car_model scm ON v.`model_id` = scm.id LEFT OUTER JOIN source_car_model_variant scmv ON v.model_variant_description = scmv.id WHERE 1 AND f.status = 1 AND concat(f.customer_fullname, f.policy_number, f.plate_cs_number1, f.plate_cs_number2) LIKE '%%%s%%' " . $filter_date . $company . $dealer . $variant . $remaining_days . " GROUP BY f.plate_cs_number1, f.plate_cs_number2 " . $inactive . " ORDER BY f.policy_end_date DESC"; // echo sprintf($query, $fields, $search) ." $offset_limit";exit; // echo "SELECT COUNT(1) FROM (".sprintf($query, $fields, $search) . ") AS COUNT"; return; $finance_report_query_list = $db->sql_query(sprintf($query, $fields, $search) ." $offset_limit"); // $var = "SELECT COUNT(1) FROM (" . sprintf($query, $fields, $search) . ") AS COUNT"; // echo $var; return; // $finance_report_query_count = $db->select(sprintf($query, $count, $search)); $finance_report_query_count = $db->select("SELECT COUNT(1) FROM (" . sprintf($query, $fields, $search) . ") AS COUNT"); while($row = $finance_report_query_list->fetch_assoc()) { $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'id'=>$row['id'], 'policy_number'=>$row['policy_number'], 'plate_cs_number'=>$row['plate_cs_number1'] != "" && $row['plate_cs_number2'] == "" ? $row['plate_cs_number1'] : ($row['plate_cs_number1'] == "" && $row['plate_cs_number2'] != "" ? $row['plate_cs_number2'] : $row['plate_cs_number1'] . "/" . $row['plate_cs_number2']), 'customer_fullname'=>$row['customer_fullname'], 'company_dealer'=>$row['company'] . " / " . $row['dealer'], 'model_year' => $row['vehicle_release_year'], 'policy_end_date'=>$row['policy_end_date'], 'provider' => $row['provider'], 'bank' => $row['bank_name'], 'model' => $row['model'], 'variant' => $row['variant'] ))); } $json_arr['total'] = $finance_report_query_count; echo json_encode($json_arr); } else if($type == 5){ // finance dashboard $box = $db->escape($_GET['box']); $company = ""; $dealer = ""; if(isset($_GET['company']) && $_GET['company'] != 0){ $company = $db->escape($_GET['company']); $company = " AND f.company_id = $company "; } else { $company = " AND f.company_id IN(" . $granted_company_ids . ") "; } if(isset($_GET['dealer']) && $_GET['dealer'] != 0){ $dealer = $db->escape($_GET['dealer']); $dealer = " AND f.dealer_id = $dealer "; } if($box == 1){ // BOX 1 $count = " COUNT(1) "; $fields = " f.id, f.plate_cs_number1, f.plate_cs_number2, f.policy_number, DATE_FORMAT(MAX(f.policy_start_date), '%b %d, %Y') AS policy_start_date, DATE_FORMAT(MAX(f.policy_end_date), '%b %d, %Y') AS policy_end_date, sc.code AS company, sd.name AS dealer "; $query = "SELECT %s FROM finance f INNER JOIN source_company sc ON sc.id = f.company_id INNER JOIN source_dealer sd ON sd.id = f.dealer_id WHERE 1 AND concat(f.plate_cs_number1, '', f.plate_cs_number2) LIKE '%%%s%%' AND f.status = 1 ".$company.$dealer." GROUP BY f.plate_cs_number1, f.plate_cs_number2 ORDER BY f.policy_end_date DESC"; // echo sprintf($query, $fields, $search); return; $query_list = $db->sql_query(sprintf($query, $fields, $search) . $offset_limit); $query_count = $db->select("SELECT COUNT(1) FROM(".sprintf($query, $count, $search).") AS count"); while($row = $query_list->fetch_assoc()) { $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'id'=>$row['id'], 'plate_cs_number'=>$row['plate_cs_number2'] != "" ? $row['plate_cs_number1'] . " / " . $row['plate_cs_number2'] : $row['plate_cs_number1'], 'plate_cs_number1'=>$row['plate_cs_number1'], 'policy_number'=>$row['policy_number'], 'policy_start_end_date'=>$row['policy_start_date'] . " - " . $row['policy_end_date'], 'company_dealer'=>$row['company'] . " / " . $row['dealer'] ))); } $json_arr['total'] = $query_count; echo json_encode($json_arr); } else if($box == 2){ // BOX 2 $current_year = $db->select("SELECT YEAR(CURDATE()) AS current_year"); $previous_year = $current_year - 1; $current_date = $db->select("SELECT CURDATE()"); $company_b2 = ""; $dealer_b2 = ""; if(isset($_GET['company']) && $_GET['company'] != 0){ $company_b2 = $db->escape($_GET['company']); $company_b2 = " AND f1.company_id = $company_b2 "; } else { $company_b2 = " AND f1.company_id IN(" . $granted_company_ids . ") "; } if(isset($_GET['dealer']) && $_GET['dealer'] != 0){ $dealer_b2 = $db->escape($_GET['dealer']); $dealer_b2 = " AND f1.dealer_id = $dealer_b2 "; } $fields = " MAX(YEAR(f1.policy_end_date)) as max_year, f1.id, DATE_FORMAT(MAX(f1.policy_start_date), '%b %d, %Y') AS policy_start_date, DATE_FORMAT(MAX(f1.policy_end_date), '%b %d, %Y') AS policy_end_date, f1.policy_number, f1.plate_cs_number1, f1.plate_cs_number2, sc.code AS company, sd.name AS dealer "; $count = " COUNT(1) "; $inactive_dormant = "SELECT %s FROM finance f1 INNER JOIN source_company sc ON sc.id = f1.company_id INNER JOIN source_dealer sd ON sd.id = f1.dealer_id WHERE 1 AND f1.status = 1 ".$company_b2.$dealer_b2." AND (concat(f1.policy_end_date, f1.policy_number, f1.plate_cs_number1) LIKE '%%%s%%' OR f1.plate_cs_number2 LIKE '%%$search%%') GROUP BY f1.plate_cs_number1, f1.plate_cs_number2 HAVING max_year = '$previous_year'"; // echo sprintf($inactive_dormant, $fields, $search); return; // echo sprintf($inactive_dormant, $fields, $search); return; $inactive_dormant_list = $db->sql_query(sprintf($inactive_dormant, $fields, $search) . $offset_limit); $inactive_dormant_count = $db->select("SELECT COUNT(1) FROM(".sprintf($inactive_dormant, $fields, $search).") AS count"); // $inactive_dormant_count = $db->select(sprintf($inactive_dormant, $search, $search)); while($data = $inactive_dormant_list->fetch_assoc()) { $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'id'=>$data['id'], 'plate_cs_number'=>$data['plate_cs_number2'] != "" ? $data['plate_cs_number1'] . " / " . $data['plate_cs_number2'] : $data['plate_cs_number1'], 'plate_cs_number1'=>$data['plate_cs_number1'], 'policy_number'=>$data['policy_number'], 'policy_start_end_date'=>$data['policy_start_date'] . " - " . $data['policy_end_date'], 'company_dealer'=>$data['company'] . " / " . $data['dealer'] ))); } $json_arr['total'] = $inactive_dormant_count; echo json_encode($json_arr); } else if($box == 3){ // BOX 3 $current_year = $db->select("SELECT YEAR(CURDATE()) AS current_year"); $fdate = $current_year . "-01-01"; $ldate = $current_year . "-12-31"; $date_between = " AND YEAR(f.policy_start_date) = '$current_year'"; $fields = " MAX(YEAR(f.policy_start_date)) as max_year, f.id, DATE_FORMAT(f.policy_start_date, '%b %d, %Y') AS policy_start_date, DATE_FORMAT(f.policy_end_date, '%b %d, %Y') AS policy_end_date, f.plate_cs_number1, f.plate_cs_number2, f.policy_number, sc.code AS company, sd.name AS dealer "; $count = " COUNT(1) "; $newly_added_query = "SELECT %s FROM finance f INNER JOIN source_company sc ON sc.id = f.company_id INNER JOIN source_dealer sd ON sd.id = f.dealer_id WHERE 1 AND f.status = 1 ".$company.$dealer." AND concat(f.policy_end_date, f.plate_cs_number1, f.plate_cs_number2, f.policy_number) LIKE '%%%s%%' GROUP BY f.plate_cs_number1 HAVING COUNT(f.plate_cs_number1) = 1 AND max_year = '$current_year' ORDER BY f.policy_start_date DESC"; // echo sprintf($newly_added_query, $fields, $search); return; $newly_added_list = $db->sql_query(sprintf($newly_added_query, $fields, $search) . $offset_limit); $newly_added_count = $db->select("SELECT COUNT(1) FROM(".sprintf($newly_added_query, $fields, $search).") AS count"); while($data = $newly_added_list->fetch_assoc()) { $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'id'=>$data['id'], 'plate_cs_number'=>$data['plate_cs_number2'] != "" ? $data['plate_cs_number1'] . " / " . $data['plate_cs_number2'] : $data['plate_cs_number1'], 'plate_cs_number1'=>$data['plate_cs_number1'], 'policy_number'=>$data['policy_number'], 'policy_start_end_date'=>$data['policy_start_date'] . " - " . $data['policy_end_date'], 'company_dealer'=>$data['company'] . " / " . $data['dealer'] ))); } $json_arr['total'] = $newly_added_count; echo json_encode($json_arr); } else if($box == 4){ // BOX 4 $company = ""; $dealer = ""; if(isset($_GET['company']) && $_GET['company'] != 0){ $company = $db->escape($_GET['company']); $company = " AND t1.company_id = $company "; } else { $company = " AND t1.company_id IN(" . $granted_company_ids . ") "; } if(isset($_GET['dealer']) && $_GET['dealer'] != 0){ $dealer = $db->escape($_GET['dealer']); $dealer = " AND t1.dealer_id = $dealer "; } $fields = " t1.id, t1.customer_fullname, t1.plate_number, t1.conduction_sticker, t1.policy_number, sc.code AS company, sd.name AS dealer, DATE_FORMAT(t1.policy_start_date, '%b %d, %Y') AS policy_start_date, DATE_FORMAT(t1.policy_end_date, '%b %d, %Y') AS policy_end_date, sb.name AS brand, scm.name AS model "; $count = " COUNT(1) "; $plate_query1 = "SELECT f.id, f.customer_fullname, f.policy_number, v.plate_number, '' AS conduction_sticker, f.company_id, f.dealer_id, v.brand_id AS brand_id, v.model_id AS model_id, v.model_variant_description AS variant_id, f.policy_start_date, f.policy_end_date, f.status FROM finance f INNER JOIN vehicle v ON v.plate_number = f.plate_cs_number1 INNER JOIN source_company sc ON f.company_id = sc.id INNER JOIN source_dealer sd ON f.dealer_id = sd.id INNER JOIN source_brand sb ON sb.id = v.brand_id INNER JOIN source_car_model scm ON scm.id = v.model_id INNER JOIN source_car_model_variant scmv ON scmv.id = v.model_variant_description"; $plate_query2 = "SELECT f.id, f.customer_fullname, f.policy_number, v.plate_number, '' AS conduction_sticker, f.company_id, f.dealer_id, v.brand_id AS brand_id, v.model_id AS model_id, v.model_variant_description AS variant_id, f.policy_start_date, f.policy_end_date, f.status FROM finance f INNER JOIN vehicle v ON v.plate_number = f.plate_cs_number2 INNER JOIN source_company sc ON f.company_id = sc.id INNER JOIN source_dealer sd ON f.dealer_id = sd.id INNER JOIN source_brand sb ON sb.id = v.brand_id INNER JOIN source_car_model scm ON scm.id = v.model_id INNER JOIN source_car_model_variant scmv ON scmv.id = v.model_variant_description"; $cs_query1 = "SELECT f.id, f.customer_fullname, f.policy_number, '' AS plate_number, v.conduction_sticker, f.company_id, f.dealer_id, v.brand_id AS brand_id, v.model_id AS model_id, v.model_variant_description AS variant_id, f.policy_start_date, f.policy_end_date, f.status FROM finance f INNER JOIN vehicle v ON v.conduction_sticker = f.plate_cs_number1 INNER JOIN source_company sc ON f.company_id = sc.id INNER JOIN source_dealer sd ON f.dealer_id = sd.id INNER JOIN source_brand sb ON sb.id = v.brand_id INNER JOIN source_car_model scm ON scm.id = v.model_id INNER JOIN source_car_model_variant scmv ON scmv.id = v.model_variant_description"; $cs_query2 = "SELECT f.id, f.customer_fullname, f.policy_number, '' AS plate_number, v.conduction_sticker, f.company_id, f.dealer_id, v.brand_id AS brand_id, v.model_id AS model_id, v.model_variant_description AS variant_id, f.policy_start_date, f.policy_end_date, f.status FROM finance f INNER JOIN vehicle v ON v.conduction_sticker = f.plate_cs_number2 INNER JOIN source_company sc ON f.company_id = sc.id INNER JOIN source_dealer sd ON f.dealer_id = sd.id INNER JOIN source_brand sb ON sb.id = v.brand_id INNER JOIN source_car_model scm ON scm.id = v.model_id INNER JOIN source_car_model_variant scmv ON scmv.id = v.model_variant_description"; $with_vehicle_query = "SELECT %s FROM ($plate_query1 UNION $cs_query1 UNION $plate_query2 UNION $cs_query2) t1 INNER JOIN source_company sc ON sc.id = t1.company_id INNER JOIN source_dealer sd ON sd.id = t1.dealer_id INNER JOIN source_brand sb ON sb.id = t1.brand_id INNER JOIN source_car_model scm ON scm.id = t1.model_id WHERE 1 AND t1.status = 1 AND concat(customer_fullname, policy_number, plate_number, conduction_sticker) LIKE '%%%s%%'".$company.$dealer." GROUP BY t1.plate_number, t1.conduction_sticker ORDER BY policy_end_date DESC"; // echo sprintf($query, $fields, $search); return; $with_vehicle_list = $db->sql_query(sprintf($with_vehicle_query, $fields, $search) . $offset_limit); $with_vehicle_count = $db->select("SELECT COUNT(1) FROM(".sprintf($with_vehicle_query, $count, $search).") AS count"); while($data = $with_vehicle_list->fetch_assoc()) { $plate_cs_number = ""; $plate_number = $data['plate_number']; $conduction_sticker = $data['conduction_sticker']; if($plate_number != ""){ if($conduction_sticker != ""){ $plate_cs_number = $plate_number . " / " . $conduction_sticker; } else { $plate_cs_number = $plate_number; } } else { $plate_cs_number = $conduction_sticker; } $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'id'=>$data['id'], 'plate_cs_number'=>$plate_cs_number, 'plate_cs_number1'=>$plate_cs_number, 'policy_number'=>$data['policy_number'], 'policy_start_end_date'=>$data['policy_start_date'] . " - " . $data['policy_end_date'], 'company_dealer'=>$data['company'] . " / " . $data['dealer'] ))); } $json_arr['total'] = $with_vehicle_count; echo json_encode($json_arr); } } else if($type == 6){ // --------------------------------------- fni activity log : resources/views/common/scripts/finance/finance_information.js [function: queryParamsFinanceActivityLogTable]-->> $plate_cs_number1 = $db->escape($_GET['plate_cs_number1']); $query = "SELECT DATE_FORMAT(ac.datetime, '%b %d, %Y / %h:%i:%s %p') AS datetime, ac.description, concat(e.first_name, ' ', e.last_name, ' (', e.employee_id, ')') AS done_by FROM all_activity_log_fni_n_pdc ac INNER JOIN employee e ON e.id = ac.done_by WHERE plate_cs_number1 = '$plate_cs_number1' AND type = 1 ORDER BY ac.datetime DESC"; $log_list = $db->sql_query($query); while($data = $log_list->fetch_assoc()) { $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'datetime'=>$data['datetime'], 'description'=>$data['description'], 'done_by'=>$data['done_by'] ))); } $json_arr['total'] = $db->select("SELECT COUNT(1) FROM (".$query.") AS count"); echo json_encode($json_arr); } else if($type == 7){ // customer information insurance $arr_found = $db->escape($_GET['plate_cs_number']); // echo $arr_found; exit; $json = ""; if(!$arr_found == 0){ $json = implode("\', \'", $arr_found); $count = " COUNT() "; $fields = ""; $fields = " f.id, f.plate_cs_number1, f.plate_cs_number2, f.policy_number, DATE_FORMAT(f.policy_issue_date, '%b %d, %Y') AS policy_issue_date, DATE_FORMAT(f.policy_start_date, '%b %d, %Y') AS policy_start_date, DATE_FORMAT(f.policy_end_date, '%b %d, %Y') AS policy_end_date, fip.name AS insurance_company, fibt.name AS insurance_type "; $finance_table_query = "SELECT %s FROM finance f INNER JOIN finance_ins_business_type fibt ON fibt.id = f.insurance_business_type INNER JOIN finance_ins_provider fip ON fip.id = f.insurance_provider WHERE (concat(f.plate_cs_number1, f.plate_cs_number2, f.policy_number, f.customer_fullname) LIKE '%%%s%%') AND f.status = 1 AND `plate_cs_number1` IN('".$json."') OR `plate_cs_number2` IN('".$json."') ORDER BY f.date_uploaded DESC"; // echo sprintf($finance_table_query, $fields, $search); return; $finance_list_query = $db->sql_query(sprintf($finance_table_query, $fields, $search) . $offset_limit); // $finance_list_count = $db->select("SELECT COUNT() FROM (" . sprintf($finance_table_query, $count, $search) . ") AS COUNT"); $finance_list_count = 0; $finance_list_count = $db->select("SELECT COUNT(*) FROM (" . sprintf($finance_table_query, $count, $search) . ") AS COUNT"); while($row = $finance_list_query->fetch_assoc()) { $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'id'=>$row['id'], 'plate_cs_number1'=>$row['plate_cs_number1'], 'plate_cs_number'=>$row['plate_cs_number2'] != "" ? $row['plate_cs_number1'] . " / " . $row['plate_cs_number2'] : $row['plate_cs_number1'], 'policy_number'=>$row['policy_number'], 'policy_start_end_date'=>$row['policy_start_date'] . " - " . $row['policy_end_date'], 'policy_issue_date'=>$row['policy_issue_date'], 'insurance_type'=>$row['insurance_type'], 'insurance_provider'=>$row['insurance_company'] ))); } $json_arr['total'] = $finance_list_count; } echo json_encode($json_arr); } ?>

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