File manager

File manager - Edit - /home/autoph/public_html/connectv1/api/ltms_report/ltms_sales_report.php

Back
<?php include_once("../../cfg/db.php"); include_once("../../api/controllers/utility.php"); if(!isset($_SESSION['user']['id'])){ echo "Session Expired."; exit; } $utility = new Utility(); if (isset($_REQUEST['apiKey']) && $_REQUEST['apiKey']=='11113487258290') { // Initialize variables $start_date = ''; $end_date = ''; $date_filter = ''; $ltms_filter = ''; $company_filter =''; $dealer_filter =''; if (isset($_REQUEST['cid']) && $_REQUEST['cid'] !== "") { $company_id = $db->escape(trim($_REQUEST['cid'])); $company_filter = " AND scd.company_id='$company_id' "; } if (isset($_REQUEST['did']) && $_REQUEST['did'] !== "") { $dealer_id = $db->escape(trim($_REQUEST['did'])); $dealer_filter = " AND scd.dealer_id='$dealer_id' "; } // Check if start_date and end_date are provided in the request if (isset($_REQUEST['date_from_sales']) && isset($_REQUEST['date_to_sales'])) { $start_date = $db->escape(trim($_REQUEST['date_from_sales'])); $end_date = $db->escape(trim($_REQUEST['date_to_sales'])); // Validate dates if (!empty($start_date) && !empty($end_date)) { $date_filter = " AND s.activity_date BETWEEN '$start_date' AND '$end_date'"; } } // Check if LTMS filter is provided in the request $ltms_filter = ''; // Initialize ltms_filter if (isset($_REQUEST['ltms_status_sales']) && $_REQUEST['ltms_status_sales'] !== "") { $ltms_status = $db->escape(trim($_REQUEST['ltms_status_sales'])); // Check LTMS status and construct the appropriate filter if ($ltms_status == '1') { // Has LTMS $ltms_filter = " AND EXISTS (SELECT 1 FROM customer_ltms cl WHERE cl.customer_id = c.id)"; } elseif ($ltms_status == '0') { // No LTMS $ltms_filter = " AND NOT EXISTS (SELECT 1 FROM customer_ltms cl WHERE cl.customer_id = c.id)"; } } // Handle pagination $limit = isset($_REQUEST['limit']) ? intval($_REQUEST['limit']) : 50; $offset = isset($_REQUEST['offset']) ? intval($_REQUEST['offset']) : 0; // Fetch total data count $total_data_query = $db->connect()->query(" SELECT COUNT(DISTINCT s.id) AS total FROM sales AS s LEFT JOIN vehicle AS v ON s.vehicle_id = v.id LEFT JOIN customer AS c ON v.customer_record_id = c.id LEFT JOIN customer_contact as cc ON c.id = cc.customer_id LEFT JOIN source_company_dealer AS scd ON v.company_dealer_id = scd.id LEFT JOIN source_company as sc ON scd.company_id = sc.id LEFT JOIN source_dealer as sd ON scd.dealer_id = sd.id WHERE 1 $date_filter $ltms_filter $company_filter $dealer_filter "); $total_data = $total_data_query->fetch_assoc()['total']; // Fetch filtered data with pagination $data_query = $db->connect()->query(" SELECT DISTINCT s.id, v.conduction_sticker AS cs_number, s.activity_date, s.deal_date, s.sales_person, s.upload_by, s.vehicle_id, v.id AS vid, v.customer_record_id, c.id AS customer_id, c.corporation_id, c.corporation_name, c.first_name, c.middle_name, c.last_name, c.date_of_birth, c.type, IF(EXISTS (SELECT 1 FROM customer_ltms cl WHERE cl.customer_id = c.id), 1, 0) AS has_tag_ltms, sc.code as company_code,sc.name as company_name, sd.code as dealer_code, sd.name as dealer_name,cc.mobile_phone_1 FROM sales AS s LEFT JOIN vehicle AS v ON s.vehicle_id = v.id LEFT JOIN customer AS c ON v.customer_record_id = c.id LEFT JOIN customer_contact as cc ON c.id = cc.customer_id LEFT JOIN source_company_dealer AS scd ON v.company_dealer_id = scd.id LEFT JOIN source_company as sc ON scd.company_id = sc.id LEFT JOIN source_dealer as sd ON scd.dealer_id = sd.id WHERE 1 $date_filter $ltms_filter $company_filter $dealer_filter ORDER BY s.id, s.activity_date DESC LIMIT $limit OFFSET $offset "); // echo" // SELECT DISTINCT s.id, v.conduction_sticker AS cs_number, s.activity_date, s.deal_date, // s.sales_person, s.upload_by, s.vehicle_id, // v.id AS vid, v.customer_record_id, c.id AS customer_id, // c.corporation_id, c.corporation_name, c.first_name, c.middle_name, c.last_name, c.date_of_birth, c.type, // IF(EXISTS (SELECT 1 FROM customer_ltms cl WHERE cl.customer_id = c.id), 1, 0) AS has_tag_ltms, // sc.code as company_code,sc.name as company_name, sd.code as dealer_code, sd.name as dealer_name,cc.mobile_phone_1 // FROM sales AS s // LEFT JOIN vehicle AS v ON s.vehicle_id = v.id // LEFT JOIN customer AS c ON v.customer_record_id = c.id // LEFT JOIN customer_contact as cc ON c.id = cc.customer_id // LEFT JOIN source_company_dealer AS scd ON v.company_dealer_id = scd.id // LEFT JOIN source_company as sc ON scd.company_id = sc.id // LEFT JOIN source_dealer as sd ON scd.dealer_id = sd.id // WHERE 1 $date_filter $ltms_filter $company_filter $dealer_filter // ORDER BY s.id, s.activity_date DESC // LIMIT $limit OFFSET $offset // ";exit; $results = []; if ($data_query->num_rows > 0) { while ($row = $data_query->fetch_assoc()) { $results[] = $row; } } // Return the response echo json_encode(['total_data' => $total_data, 'results' => $results]); } ?>

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