File manager

File manager - Edit - /home/autoph/public_html/connectv1/app/finance/dashboard/details_count.php

Back
<?php include_once("../../../cfg/db.php"); include_once("../../../api/controllers/utility.php"); $utility = new Utility(); $type = $db->escape($_POST['type']); $json_arr['rows'] = array(); $arr = array(); $granted_company_ids = $utility->isNotEmpty($_SESSION['user']['company_permissions_implode']) ? $_SESSION['user']['company_permissions_implode'] : '-1' ; $current_year = $db->select("SELECT YEAR(CURDATE()) AS year_now"); if($type == 1){ // ----------------------------------------------- FNI DASHBOARD CATEGORIZED BOX COUNT--> $company = ""; $dealer = ""; if(isset($_POST['company']) && $_POST['company'] != 0){ $company = $db->escape($_POST['company']); $company = " AND f.company_id = $company "; } else { $company = " AND f.company_id IN(" . $granted_company_ids . ") "; } if(isset($_POST['dealer']) && $_POST['dealer'] != 0){ $dealer = $db->escape($_POST['dealer']); $dealer = " AND f.dealer_id = $dealer "; } $current_date = $db->select("SELECT DATE_FORMAT(NOW(),'%Y-%m-%d')"); // if(isset($_POST['company']) && $_POST['company'] != 0){ // $company = $db->escape($_POST['company']); // $company = " AND f.company_id = $company "; // } // else { // $company = " AND f.company_id IN(" . $granted_company_ids . ") "; // } // if(isset($_POST['dealer']) && $_POST['dealer'] != 0){ // $dealer = $db->escape($_POST['dealer']); // $dealer = " AND f.dealer_id = $dealer "; // } // ------------------------------------------------------------------ CATEGORIZED -->> $categorized_count_query = "SELECT COUNT() FROM finance f WHERE 1 AND f.status = 1 " . $company . $dealer . " GROUP BY f.plate_cs_number1, f.plate_cs_number2"; // echo $categorized_count_query; return; // ------------------------------------------------------------------ INACTIVE -->> $record_count = 0; $company = ""; $dealer = ""; if(isset($_POST['company']) && $_POST['company'] != 0){ $company = $db->escape($_POST['company']); $company = " AND f1.company_id = $company "; } else { $company = " AND f1.company_id IN(" . $granted_company_ids . ") "; } if(isset($_POST['dealer']) && $_POST['dealer'] != 0){ $dealer = $db->escape($_POST['dealer']); $dealer = " AND f1.dealer_id = $dealer "; } $current_year = $db->select("SELECT YEAR(CURDATE()) AS current_year"); $previous_year = $current_year - 1; $current_date = $db->select("SELECT CURDATE()"); $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 f.status = 1 // GROUP BY f1.plate_cs_number1, f1.plate_cs_number2 // HAVING max_year = '$previous_year'"; $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.$dealer." GROUP BY f1.plate_cs_number1, f1.plate_cs_number2 HAVING max_year = '$previous_year'"; // echo sprintf($inactive_dormant, $count); return; $inactive_dormant_list = $db->sql_query(sprintf($inactive_dormant, $fields)); $inactive_dormant_count = $db->select("SELECT COUNT(1) FROM (".sprintf($inactive_dormant, $fields).") AS count"); // foreach($inactive_dormant_list AS $row){ // $year_stated = substr($row['policy_end_date'], 0, 4); // $year_stated = $year_stated++; // $plate_cs_number1 = $row['plate_cs_number1']; // $plate_cs_number2 = $row['plate_cs_number2']; // $next_query = $db->select("SELECT COUNT(1) FROM finance WHERE plate_cs_number1 = '$plate_cs_number1' AND plate_cs_number2 = '$plate_cs_number2' AND YEAR(policy_end_date) = '$year_stated'"); // $record_count = $record_count + $next_query; // } // ------------------------------------------------------------------ NEWLY ADDED -->> $company = ""; $dealer = ""; if(isset($_POST['company']) && $_POST['company'] != 0){ $company = $db->escape($_POST['company']); $company = " AND f.company_id = $company "; } else { $company = " AND f.company_id IN(" . $granted_company_ids . ") "; } if(isset($_POST['dealer']) && $_POST['dealer'] != 0){ $dealer = $db->escape($_POST['dealer']); $dealer = " AND f.dealer_id = $dealer "; } $current_year = $db->select("SELECT YEAR(CURDATE()) AS current_year"); $fdate = $current_year . "-01-01"; $ldate = $current_year . "-12-31"; $date_between = " AND policy_start_date BETWEEN '" . $fdate . "' AND '" . $ldate . "'"; $newly_added_query = "SELECT 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 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." 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"; // $newly_added_query = "SELECT COUNT() // FROM finance f1 // WHERE "; // echo $newly_added_query; return; // ------------------------------------------------------------------ WITH VEHICLE RECORD -->> $company = ""; $dealer = ""; if(isset($_POST['company']) && $_POST['company'] != 0){ $company = $db->escape($_POST['company']); $company = " AND t1.company_id = $company "; } else { $company = " AND t1.company_id IN(" . $granted_company_ids . ") "; } if(isset($_POST['dealer']) && $_POST['dealer'] != 0){ $dealer = $db->escape($_POST['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_end_date, '%b %d, %Y') AS policy_end_date, sb.name AS brand, scm.name AS model "; $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_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 GROUP BY f.plate_cs_number1, plate_cs_number2"; $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_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 GROUP BY f.plate_cs_number1, plate_cs_number2"; $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_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 GROUP BY f.plate_cs_number1, plate_cs_number2"; $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_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 GROUP BY f.plate_cs_number1, plate_cs_number2"; $query = "SELECT COUNT(1) FROM ($plate_query1 UNION $plate_query2 UNION $cs_query1 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 ".$company.$dealer." GROUP BY t1.plate_number, t1.conduction_sticker ORDER BY policy_end_date DESC"; // echo sprintf($query); return; $arr['categorized'] = number_format($db->select("SELECT COUNT(1) FROM(".$categorized_count_query.") AS count")); // $arr['individual_insurance'] = number_format($db->select($individual_insurance_query)); $arr['inactive'] = number_format($inactive_dormant_count); $arr['newly_added'] = number_format($db->select("SELECT COUNT(1) FROM(".$newly_added_query.") AS count")); $arr['vehicle_record'] = number_format($db->select("SELECT COUNT(1) FROM(".$query.") AS count")); // $arr['vehicle_connected'] = number_format($db->select($added_today_query)); echo json_encode($arr); } ?>

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