opensourcecode/Hyper-M_Admin_Portal/services/report_enquiry.php

<?php

/* * **************************
 * File : report_enquiry.php
 * Author : Cygnusa
 * @2020
 * ************************** */
// included in report.php

/* /////////////////////////////////// RATIO  /////////////////////////////////// */
function ratio($x, $y)
{
    $ratio = ' : ';
    if ($y > 0 && $x > 0) {
        $val = round(($x / $y), 2);
        $val = (is_float($val * 1)) ? $val : (int) $val;
        $ratio = '1 : ' . $val;
    }
    return $ratio;
}
/* ///////////////////////////////////////////////////////////////////////////////////// */

/* /////////////////////////////////// ROUND  /////////////////////////////////// */
function floatround($val)
{
    if ($val != '-' || $val != '') {
        $val = (is_float($val * 1)) ? round($val, 2) : $val;
    }
    return $val;

}
/* ///////////////////////////////////////////////////////////////////////////////////// */

function enquiry_report()
{
    global $conn;
    $reporttype = escape_str($_REQUEST["reporttype"]);
    switch ($reporttype) {
        case 'enquirystatus':
        case 'enquirytestride':
        case 'conversionratio':
        case 'productivity':

            //case 'convertedsales':
            //case 'achievedvstarget':
            //case 'productivity':
            $reporttype();
            break;

        default:
            $conn->message(false, "PAGE NOT FOUND");exit;
            break;
    }
}

function enquirystatus()
{
    global $conn;
    try {
        $lang = "en_us";
        $pageData = $conn->loadJSON($lang);
        //$_SESSION["userid"] = 1;

        if (isset($_SESSION["userid"])) {

            $zone = escape_str($_REQUEST["zone"]);
            $zm_email = escape_str($_REQUEST["zm_email"]);
            $rm_email = escape_str($_REQUEST["rm_email"]);
            $am_email = escape_str($_REQUEST["am_email"]);
            $dealer_code = escape_str($_REQUEST["dealer_code"]);
            $smpin = escape_str($_REQUEST["smpin"]);
            $segment = escape_str($_REQUEST["segment"]);

            $start = escape_str($_REQUEST["start"]);
            $end = escape_str($_REQUEST["end"]);

            if (!($start != '' && $end != '')) {
                $conn->message(false, $conn->getMessage($lang, 'report', 'error7'));
            }

            $from_date = date('Y-m', strtotime($start . "-01")) . "-01";
            $to_date = date('Y-m-t', strtotime($end . "-01"));

            if (!(isValidDateTimeString($from_date, 'Y-m-d', 'Asia/Kolkata') && isValidDateTimeString($to_date, 'Y-m-d', 'Asia/Kolkata'))) {
                $conn->message(false, $conn->getMessage($lang, 'report', 'error7'));
            }

            $user_cond = '';
            if ($smpin != 'all' && $smpin != '') {
                //AM
                $type = "smpin";
            } else if ($dealer_code != 'all' && $dealer_code != '') {
                //AM
                $type = "dealer";
            } else if ($am_email != 'all' && $am_email != '') {
                //AM
                $type = "am";
            } else if ($rm_email != 'all' && $rm_email != '') {
                //RM
                $type = "rm";
            } else if ($zm_email != 'all' && $zm_email != '') {
                //ZM
                $type = "zm";
            } else if ($zone != 'all' && $zone != '') {
                //PAN
                $user_cond = '';
                $type = "zone";
            } else {
                //PAN
                $user_cond = '';
                $type = "pan";
            }

            $mcond = '';
            if (($smpin != 'all' && $smpin != '') || ($dealer_code != 'all' && $dealer_code != '') || ($am_email != 'all' && $am_email != '') || ($rm_email != 'all' && $rm_email != '') || ($zm_email != 'all' && $zm_email != '') || ($zone != 'all' && $zone != '')) {
                $mcond = '';
                $mcond .= ($smpin != 'all' && $smpin != '') ? " AND e.smpin='" . $smpin . "'" : '';
                $mcond .= ($dealer_code != 'all' && $dealer_code != '') ? " AND d.dealer_code='" . $dealer_code . "'" : '';
                $mcond .= ($am_email != 'all' && $am_email != '') ? " AND d.am_email='" . $am_email . "'" : '';
                $mcond .= ($rm_email != 'all' && $rm_email != '') ? " AND d.rm_email='" . $rm_email . "'" : '';
                $mcond .= ($zm_email != 'all' && $zm_email != '') ? " AND d.zm_email='" . $zm_email . "'" : '';
                $mcond .= ($zone != 'all' && $zone != '') ? " AND d.zone='" . $zone . "'" : '';

            }

            $returnarr = array();
            $tablejoin = '';
            $regtype = (escape_str($_REQUEST["regtype"]) == '1' || !isset($_REQUEST["regtype"])) ? "1" : "0";

            if ($regtype == '1') {
                $user_cond .= ' AND e.is_current=1 ';
            }

            if ($segment != '' && $segment > 0) {
                $user_cond .= " AND e.segment='" . $segment . "' ";
            }

            if ($type == 'pan') {

                $header = array('Zone', 'Total Dealer', 'Total DSE', 'Registered DSE', 'Total Enquiries', 'Hot', 'Warm', 'Cold', 'Postponed', 'Lost', 'Sales');

                //GET ALL ZM
                $getzmquery_str = "SELECT zone FROM " . DMS_DEALER . " d WHERE status=1 AND is_deleted='0'  GROUP BY zone ORDER BY zone ASC";
                $getzm_data = $conn->getQueryValue($getzmquery_str);

                if (count($getzm_data) > 0) {
                    foreach ($getzm_data as $val) {

                        $manager_cond = " AND d.zone='" . $val->zone . "'" . $user_cond;
                        $dealer_cond = "AND d.zone='" . $val->zone . "'";

                        $dealer_str = "SELECT COUNT(d.id) data_count FROM " . DMS_DEALER . " d WHERE d.status=1 AND d.is_deleted='0' " . $dealer_cond . " LIMIT 1";
                        $dealer_arr = $conn->getQueryValue($dealer_str);
                        $dealer_count = (count($dealer_arr) > 0) ? $dealer_arr[0]->data_count : '0';

                        $dse_str = "SELECT COUNT(e.id) data_count FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond . " LIMIT 1";
                        $dse_arr = $conn->getQueryValue($dse_str);
                        $dse_count = (count($dse_arr) > 0) ? $dse_arr[0]->data_count : '0';

                        $user_str = "SELECT COUNT(u.userid) data_count FROM " . TABLE_USER . " u JOIN " . DMS_EMPLOYEE . " e ON u.smpin=e.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND u.is_deleted=0 AND u.role>7 AND u.role<14 " . $manager_cond . " LIMIT 1";
                        $user_arr = $conn->getQueryValue($user_str);
                        $user_count = (count($user_arr) > 0) ? $user_arr[0]->data_count : '0';

                        $tot_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59'  " . $manager_cond;
                        $tot_arr = $conn->getQueryValue($tot_enq_str);
                        $tot_count = (count($tot_arr) > 0) ? $tot_arr[0]->data_count : '0';

                        $hot_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND  enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND enq.status='H' AND enq.enquiry_stage NOT IN ('Z','O','P','V')" . $manager_cond;
                        $hot_arr = $conn->getQueryValue($hot_enq_str);
                        $hot_count = (count($hot_arr) > 0) ? $hot_arr[0]->data_count : '0';

                        $warm_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND  enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND enq.status='W' AND enq.enquiry_stage NOT IN ('Z','O','P','V')" . $manager_cond;
                        $warm_arr = $conn->getQueryValue($warm_enq_str);
                        $warm_count = (count($warm_arr) > 0) ? $warm_arr[0]->data_count : '0';

                        $cold_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND  enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND enq.status='P' AND enq.enquiry_stage NOT IN ('Z','O','P','V')" . $manager_cond;
                        $cold_arr = $conn->getQueryValue($cold_enq_str);
                        $cold_count = (count($cold_arr) > 0) ? $cold_arr[0]->data_count : '0';

                        $postponed_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND  enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND  enq.status='E' AND enq.enquiry_stage NOT IN ('Z','O','P','V')" . $manager_cond;
                        $postponed_arr = $conn->getQueryValue($postponed_enq_str);
                        $postponed_count = (count($postponed_arr) > 0) ? $postponed_arr[0]->data_count : '0';

                        $lost_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND  enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND enq.status='L' " . $manager_cond;
                        $lost_arr = $conn->getQueryValue($lost_enq_str);
                        $lost_count = (count($lost_arr) > 0) ? $lost_arr[0]->data_count : '0';

                        $sales_str = "SELECT COALESCE(SUM(s.sales_count),0) as data_count FROM " . DMS_EMPLOYEE_SALES . " s JOIN " . DMS_EMPLOYEE . " as e ON e.smpin=s.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND  s.sale_date>='" . $from_date . "' AND s.sale_date<='" . $to_date . "' " . $manager_cond;
                        $sales_arr = $conn->getQueryValue($sales_str);
                        $tot_sales = (count($sales_arr) > 0) ? $sales_arr[0]->data_count : '0';

                        $returnarr[] = array($val->zone, $dealer_count, $dse_count, $user_count, $tot_count, $hot_count, $warm_count, $cold_count, $postponed_count, $lost_count, $tot_sales);
                    }
                }
            } else if (($type == 'zone') || ($type == 'zm') || ($type == 'rm') || ($type == 'am')) {

                $header = array('Total Dealer', 'Total DSE', 'Registered DSE', 'Total Enquiries', 'Hot', 'Warm', 'Cold', 'Postponed', 'Lost', 'Sales');

                $manager_cond = $mcond . $user_cond;
                $dealer_cond = $mcond;
                $dealer_str = "SELECT COUNT(d.id) data_count FROM " . DMS_DEALER . " d WHERE d.status=1 AND d.is_deleted='0' " . $dealer_cond . " LIMIT 1";
                $dealer_arr = $conn->getQueryValue($dealer_str);
                $dealer_count = (count($dealer_arr) > 0) ? $dealer_arr[0]->data_count : '0';

                $dse_str = "SELECT COUNT(e.id) data_count FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond . " LIMIT 1";
                $dse_arr = $conn->getQueryValue($dse_str);
                $dse_count = (count($dse_arr) > 0) ? $dse_arr[0]->data_count : '0';

                $user_str = "SELECT COUNT(u.userid) data_count FROM " . TABLE_USER . " u JOIN " . DMS_EMPLOYEE . " e ON u.smpin=e.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND u.is_deleted=0 AND u.role>7 AND u.role<14 " . $manager_cond . " LIMIT 1";
                $user_arr = $conn->getQueryValue($user_str);
                $user_count = (count($user_arr) > 0) ? $user_arr[0]->data_count : '0';

                $tot_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59'  " . $manager_cond;
                $tot_arr = $conn->getQueryValue($tot_enq_str);
                $tot_count = (count($tot_arr) > 0) ? $tot_arr[0]->data_count : '0';

                $hot_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND  enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND enq.status='H' AND enq.enquiry_stage NOT IN ('Z','O','P','V') " . $manager_cond;
                $hot_arr = $conn->getQueryValue($hot_enq_str);
                $hot_count = (count($hot_arr) > 0) ? $hot_arr[0]->data_count : '0';

                $warm_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND  enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND enq.status='W' AND enq.enquiry_stage NOT IN ('Z','O','P','V') " . $manager_cond;
                $warm_arr = $conn->getQueryValue($warm_enq_str);
                $warm_count = (count($warm_arr) > 0) ? $warm_arr[0]->data_count : '0';

                $cold_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND  enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND enq.status='P' AND enq.enquiry_stage NOT IN ('Z','O','P','V') " . $manager_cond;
                $cold_arr = $conn->getQueryValue($cold_enq_str);
                $cold_count = (count($cold_arr) > 0) ? $cold_arr[0]->data_count : '0';

                $postponed_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND  enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND  enq.status='E' AND enq.enquiry_stage NOT IN ('Z','O','P','V') " . $manager_cond;
                $postponed_arr = $conn->getQueryValue($postponed_enq_str);
                $postponed_count = (count($postponed_arr) > 0) ? $postponed_arr[0]->data_count : '0';

                $lost_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND  enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND enq.status='L' " . $manager_cond;
                $lost_arr = $conn->getQueryValue($lost_enq_str);
                $lost_count = (count($lost_arr) > 0) ? $lost_arr[0]->data_count : '0';

                $sales_str = "SELECT COALESCE(SUM(s.sales_count),0) as data_count FROM " . DMS_EMPLOYEE_SALES . " s JOIN " . DMS_EMPLOYEE . " as e ON e.smpin=s.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND  s.sale_date>='" . $from_date . "' AND s.sale_date<='" . $to_date . "' " . $manager_cond;
                $sales_arr = $conn->getQueryValue($sales_str);
                $tot_sales = (count($sales_arr) > 0) ? $sales_arr[0]->data_count : '0';

                $returnarr[] = array($dealer_count, $dse_count, $user_count, $tot_count, $hot_count, $warm_count, $cold_count, $postponed_count, $lost_count, $tot_sales);

            } else if ($type == 'dealer') {
                $header = array('Total DSE', 'Registered DSE', 'Total Enquiries', 'Hot', 'Warm', 'Cold', 'Postponed', 'Lost', 'Sales');

                $manager_cond = $mcond . $user_cond;

                $dse_str = "SELECT COUNT(e.id) data_count FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond . " LIMIT 1";
                $dse_arr = $conn->getQueryValue($dse_str);
                $dse_count = (count($dse_arr) > 0) ? $dse_arr[0]->data_count : '0';

                $user_str = "SELECT COUNT(u.userid) data_count FROM " . TABLE_USER . " u JOIN " . DMS_EMPLOYEE . " e ON u.smpin=e.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND u.is_deleted=0 AND u.role>7 AND u.role<14 " . $manager_cond . " LIMIT 1";
                $user_arr = $conn->getQueryValue($user_str);
                $user_count = (count($user_arr) > 0) ? $user_arr[0]->data_count : '0';

                $tot_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59'  " . $manager_cond;
                $tot_arr = $conn->getQueryValue($tot_enq_str);
                $tot_count = (count($tot_arr) > 0) ? $tot_arr[0]->data_count : '0';

                $hot_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND  enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND enq.status='H' AND enq.enquiry_stage NOT IN ('Z','O','P','V') " . $manager_cond;
                $hot_arr = $conn->getQueryValue($hot_enq_str);
                $hot_count = (count($hot_arr) > 0) ? $hot_arr[0]->data_count : '0';

                $warm_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND  enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND enq.status='W' AND enq.enquiry_stage NOT IN ('Z','O','P','V') " . $manager_cond;
                $warm_arr = $conn->getQueryValue($warm_enq_str);
                $warm_count = (count($warm_arr) > 0) ? $warm_arr[0]->data_count : '0';

                $cold_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND  enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND enq.status='P' AND enq.enquiry_stage NOT IN ('Z','O','P','V') " . $manager_cond;
                $cold_arr = $conn->getQueryValue($cold_enq_str);
                $cold_count = (count($cold_arr) > 0) ? $cold_arr[0]->data_count : '0';

                $postponed_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND  enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND  enq.status='E' AND enq.enquiry_stage NOT IN ('Z','O','P','V') " . $manager_cond;
                $postponed_arr = $conn->getQueryValue($postponed_enq_str);
                $postponed_count = (count($postponed_arr) > 0) ? $postponed_arr[0]->data_count : '0';

                $lost_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND  enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND enq.status='L' " . $manager_cond;
                $lost_arr = $conn->getQueryValue($lost_enq_str);
                $lost_count = (count($lost_arr) > 0) ? $lost_arr[0]->data_count : '0';

                $sales_str = "SELECT COALESCE(SUM(s.sales_count),0) as data_count FROM " . DMS_EMPLOYEE_SALES . " s JOIN " . DMS_EMPLOYEE . " as e ON e.smpin=s.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND  s.sale_date>='" . $from_date . "' AND s.sale_date<='" . $to_date . "' " . $manager_cond;
                $sales_arr = $conn->getQueryValue($sales_str);
                $tot_sales = (count($sales_arr) > 0) ? $sales_arr[0]->data_count : '0';

                $returnarr[] = array($dse_count, $user_count, $tot_count, $hot_count, $warm_count, $cold_count, $postponed_count, $lost_count, $tot_sales);

            } else if ($type == 'smpin') {
                $header = array('Total Enquiries', 'Hot', 'Warm', 'Cold', 'Postponed', 'Lost', 'Sales');

                $manager_cond = " AND e.smpin ='" . $smpin . "'";

                $tot_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59'  " . $manager_cond;
                $tot_arr = $conn->getQueryValue($tot_enq_str);
                $tot_count = (count($tot_arr) > 0) ? $tot_arr[0]->data_count : '0';

                $hot_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND  enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND enq.status='H' AND enq.enquiry_stage NOT IN ('Z','O','P','V') " . $manager_cond;
                $hot_arr = $conn->getQueryValue($hot_enq_str);
                $hot_count = (count($hot_arr) > 0) ? $hot_arr[0]->data_count : '0';

                $warm_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND  enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND enq.status='W' AND enq.enquiry_stage NOT IN ('Z','O','P','V')" . $manager_cond;
                $warm_arr = $conn->getQueryValue($warm_enq_str);
                $warm_count = (count($warm_arr) > 0) ? $warm_arr[0]->data_count : '0';

                $cold_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND  enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND enq.status='P' AND enq.enquiry_stage NOT IN ('Z','O','P','V')" . $manager_cond;
                $cold_arr = $conn->getQueryValue($cold_enq_str);
                $cold_count = (count($cold_arr) > 0) ? $cold_arr[0]->data_count : '0';

                $postponed_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND  enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND  enq.status='E' AND enq.enquiry_stage NOT IN ('Z','O','P','V')" . $manager_cond;
                $postponed_arr = $conn->getQueryValue($postponed_enq_str);
                $postponed_count = (count($postponed_arr) > 0) ? $postponed_arr[0]->data_count : '0';

                $lost_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND  enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND enq.status='L' " . $manager_cond;
                $lost_arr = $conn->getQueryValue($lost_enq_str);
                $lost_count = (count($lost_arr) > 0) ? $lost_arr[0]->data_count : '0';

                $sales_str = "SELECT COALESCE(SUM(s.sales_count),0) as data_count FROM " . DMS_EMPLOYEE_SALES . " s JOIN " . DMS_EMPLOYEE . " as e ON e.smpin=s.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND  s.sale_date>='" . $from_date . "' AND s.sale_date<='" . $to_date . "' " . $manager_cond;
                $sales_arr = $conn->getQueryValue($sales_str);
                $tot_sales = (count($sales_arr) > 0) ? $sales_arr[0]->data_count : '0';

                $returnarr[] = array($tot_count, $hot_count, $warm_count, $cold_count, $postponed_count, $lost_count, $tot_sales);
            }

            $list_arr = $returnarr;
            $return_arr = array('list' => $list_arr, 'header' => $header, 'q' => $dse_str);
            $conn->message(true, $conn->getMessage($lang, 'report', 'success1'), $return_arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'report', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'report', 'error4'));
    } catch (Exception $e) {
        $conn->message(false, $e->getMessage());
    }
}

function enquirytestride()
{
    global $conn;
    try {
        $lang = "en_us";
        $pageData = $conn->loadJSON($lang);
        //$_SESSION["userid"] = 1;

        if (isset($_SESSION["userid"])) {

            $zone = escape_str($_REQUEST["zone"]);
            $zm_email = escape_str($_REQUEST["zm_email"]);
            $rm_email = escape_str($_REQUEST["rm_email"]);
            $am_email = escape_str($_REQUEST["am_email"]);
            $dealer_code = escape_str($_REQUEST["dealer_code"]);
            $smpin = escape_str($_REQUEST["smpin"]);
            $segment = escape_str($_REQUEST["segment"]);

            $start = escape_str($_REQUEST["start"]);
            $end = escape_str($_REQUEST["end"]);

            if (!($start != '' && $end != '')) {
                $conn->message(false, $conn->getMessage($lang, 'report', 'error7'));
            }

            $from_date = date('Y-m', strtotime($start . "-01")) . "-01";
            $to_date = date('Y-m-t', strtotime($end . "-01"));

            if (!(isValidDateTimeString($from_date, 'Y-m-d', 'Asia/Kolkata') && isValidDateTimeString($to_date, 'Y-m-d', 'Asia/Kolkata'))) {
                $conn->message(false, $conn->getMessage($lang, 'report', 'error7'));
            }

            $user_cond = '';
            if ($smpin != 'all' && $smpin != '') {
                //AM
                $type = "smpin";
            } else if ($dealer_code != 'all' && $dealer_code != '') {
                //AM
                $type = "dealer";
            } else if ($am_email != 'all' && $am_email != '') {
                //AM
                $type = "am";
            } else if ($rm_email != 'all' && $rm_email != '') {
                //RM
                $type = "rm";
            } else if ($zm_email != 'all' && $zm_email != '') {
                //ZM
                $type = "zm";
            } else if ($zone != 'all' && $zone != '') {
                //PAN
                $user_cond = '';
                $type = "zone";
            } else {
                //PAN
                $user_cond = '';
                $type = "pan";
            }

            $mcond = '';
            if (($smpin != 'all' && $smpin != '') || ($dealer_code != 'all' && $dealer_code != '') || ($am_email != 'all' && $am_email != '') || ($rm_email != 'all' && $rm_email != '') || ($zm_email != 'all' && $zm_email != '') || ($zone != 'all' && $zone != '')) {
                $mcond = '';
                $mcond .= ($smpin != 'all' && $smpin != '') ? " AND e.smpin='" . $smpin . "'" : '';
                $mcond .= ($dealer_code != 'all' && $dealer_code != '') ? " AND d.dealer_code='" . $dealer_code . "'" : '';
                $mcond .= ($am_email != 'all' && $am_email != '') ? " AND d.am_email='" . $am_email . "'" : '';
                $mcond .= ($rm_email != 'all' && $rm_email != '') ? " AND d.rm_email='" . $rm_email . "'" : '';
                $mcond .= ($zm_email != 'all' && $zm_email != '') ? " AND d.zm_email='" . $zm_email . "'" : '';
                $mcond .= ($zone != 'all' && $zone != '') ? " AND d.zone='" . $zone . "'" : '';

            }

            $returnarr = array();
            $tablejoin = '';
            $regtype = (escape_str($_REQUEST["regtype"]) == '1' || !isset($_REQUEST["regtype"])) ? "1" : "0";

            if ($regtype == '1') {
                $user_cond .= ' AND e.is_current=1 ';
            }

            if ($segment != '' && $segment > 0) {
                $user_cond .= " AND e.segment='" . $segment . "' ";
            }

            if ($type == 'pan') {

                $header = array('Zone', 'Total Dealer', 'Total DSE', 'Registered DSE', 'Enquiry Created HYPER-M', 'Enquiry Created from Backend', 'Test Ride From HYPER-M APP', 'Brochure shared', 'Quotation Shared');

                //GET ALL ZM
                $getzmquery_str = "SELECT zone FROM " . DMS_DEALER . " d WHERE status=1 AND is_deleted='0'  GROUP BY zone ORDER BY zone ASC";
                $getzm_data = $conn->getQueryValue($getzmquery_str);

                if (count($getzm_data) > 0) {
                    foreach ($getzm_data as $val) {

                        $manager_cond = " AND d.zone='" . $val->zone . "'" . $user_cond;
                        $dealer_cond = "AND d.zone='" . $val->zone . "'";

                        $dealer_str = "SELECT COUNT(d.id) data_count FROM " . DMS_DEALER . " d WHERE d.status=1 AND d.is_deleted='0' " . $dealer_cond . " LIMIT 1";
                        $dealer_arr = $conn->getQueryValue($dealer_str);
                        $dealer_count = (count($dealer_arr) > 0) ? $dealer_arr[0]->data_count : '0';

                        $dse_str = "SELECT COUNT(e.id) data_count FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond . " LIMIT 1";
                        $dse_arr = $conn->getQueryValue($dse_str);
                        $dse_count = (count($dse_arr) > 0) ? $dse_arr[0]->data_count : '0';

                        $user_str = "SELECT COUNT(u.userid) data_count FROM " . TABLE_USER . " u JOIN " . DMS_EMPLOYEE . " e ON u.smpin=e.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND u.is_deleted=0 AND u.role>7 AND u.role<14 " . $manager_cond . " LIMIT 1";
                        $user_arr = $conn->getQueryValue($user_str);
                        $user_count = (count($user_arr) > 0) ? $user_arr[0]->data_count : '0';
                        //star enq
                        $tot_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59'   AND enq.is_starapp=1" . $manager_cond;
                        $tot_arr = $conn->getQueryValue($tot_enq_str);
                        $tot_count = (count($tot_arr) > 0) ? $tot_arr[0]->data_count : '0';
                        //non star enq
                        $backtot_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND enq.is_starapp=0" . $manager_cond;
                        $backtot_arr = $conn->getQueryValue($backtot_enq_str);
                        $backtot_count = (count($backtot_arr) > 0) ? $backtot_arr[0]->data_count : '0';
                        //star enq test
                        $tottest_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59'   AND enq.is_starapp=1 AND enq.is_testride=1" . $manager_cond;
                        $tottest_arr = $conn->getQueryValue($tottest_enq_str);
                        $tottest_count = (count($tottest_arr) > 0) ? $tottest_arr[0]->data_count : '0';

                        $brochure_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . TABLE_ENQBROCHURESHARE . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code  WHERE d.is_deleted=0 AND e.status=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' " . $manager_cond;
                        $brochure_arr = $conn->getQueryValue($brochure_enq_str);
                        $brochure_count = (count($brochure_arr) > 0) ? $brochure_arr[0]->data_count : '0';

                        $quotation_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . TABLE_QUOTATIONSHARE . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code  WHERE d.is_deleted=0 AND e.status=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' " . $manager_cond;
                        $quotation_arr = $conn->getQueryValue($quotation_enq_str);
                        $quotation_count = (count($quotation_arr) > 0) ? $quotation_arr[0]->data_count : '0';

                        $returnarr[] = array($val->zone, $dealer_count, $dse_count, $user_count, $tot_count, $backtot_count, $tottest_count, $brochure_count, $quotation_count);
                    }
                }
            } else if (($type == 'zone') || ($type == 'zm') || ($type == 'rm') || ($type == 'am')) {

                $header = array('Total Dealer', 'Total DSE', 'Registered DSE', 'Enquiry Created HYPER-M APP', 'Enquiry Created from Backend', 'Test Ride From HYPER-M APP', 'Brochure shared', 'Quotation Shared');

                $manager_cond = $mcond . $user_cond;
                $dealer_cond = $mcond;
                $dealer_str = "SELECT COUNT(d.id) data_count FROM " . DMS_DEALER . " d WHERE d.status=1 AND d.is_deleted='0' " . $dealer_cond . " LIMIT 1";
                $dealer_arr = $conn->getQueryValue($dealer_str);
                $dealer_count = (count($dealer_arr) > 0) ? $dealer_arr[0]->data_count : '0';

                $dse_str = "SELECT COUNT(e.id) data_count FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond . " LIMIT 1";
                $dse_arr = $conn->getQueryValue($dse_str);
                $dse_count = (count($dse_arr) > 0) ? $dse_arr[0]->data_count : '0';

                $user_str = "SELECT COUNT(u.userid) data_count FROM " . TABLE_USER . " u JOIN " . DMS_EMPLOYEE . " e ON u.smpin=e.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND u.is_deleted=0 AND u.role>7 AND u.role<14 " . $manager_cond . " LIMIT 1";
                $user_arr = $conn->getQueryValue($user_str);
                $user_count = (count($user_arr) > 0) ? $user_arr[0]->data_count : '0';
                //star enq
                $tot_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59'   AND enq.is_starapp=1" . $manager_cond;
                $tot_arr = $conn->getQueryValue($tot_enq_str);
                $tot_count = (count($tot_arr) > 0) ? $tot_arr[0]->data_count : '0';
                //non star enq
                $backtot_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND enq.is_starapp=0" . $manager_cond;
                $backtot_arr = $conn->getQueryValue($backtot_enq_str);
                $backtot_count = (count($backtot_arr) > 0) ? $backtot_arr[0]->data_count : '0';
                //star enq test
                $tottest_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59'   AND enq.is_starapp=1 AND enq.is_testride=1" . $manager_cond;
                $tottest_arr = $conn->getQueryValue($tottest_enq_str);
                $tottest_count = (count($tottest_arr) > 0) ? $tottest_arr[0]->data_count : '0';

                $brochure_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . TABLE_ENQBROCHURESHARE . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code  WHERE d.is_deleted=0 AND e.status=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' " . $manager_cond;
                $brochure_arr = $conn->getQueryValue($brochure_enq_str);
                $brochure_count = (count($brochure_arr) > 0) ? $brochure_arr[0]->data_count : '0';

                $quotation_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . TABLE_QUOTATIONSHARE . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code  WHERE d.is_deleted=0 AND e.status=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' " . $manager_cond;
                $quotation_arr = $conn->getQueryValue($quotation_enq_str);
                $quotation_count = (count($quotation_arr) > 0) ? $quotation_arr[0]->data_count : '0';

                $returnarr[] = array($dealer_count, $dse_count, $user_count, $tot_count, $backtot_count, $tottest_count, $brochure_count, $quotation_count);

            } else if ($type == 'dealer') {
                $header = array('Total DSE', 'Registered DSE', 'Enquiry Created HYPER-M App', 'Enquiry Created from Backend', 'Test Ride From HYPER-M App', 'Brochure shared', 'Quotation Shared');

                $manager_cond = $mcond . $user_cond;

                $dse_str = "SELECT COUNT(e.id) data_count FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond . " LIMIT 1";
                $dse_arr = $conn->getQueryValue($dse_str);
                $dse_count = (count($dse_arr) > 0) ? $dse_arr[0]->data_count : '0';

                $user_str = "SELECT COUNT(u.userid) data_count FROM " . TABLE_USER . " u JOIN " . DMS_EMPLOYEE . " e ON u.smpin=e.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND u.is_deleted=0 AND u.role>7 AND u.role<14 " . $manager_cond . " LIMIT 1";
                $user_arr = $conn->getQueryValue($user_str);
                $user_count = (count($user_arr) > 0) ? $user_arr[0]->data_count : '0';

                //star enq
                $tot_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59'   AND enq.is_starapp=1" . $manager_cond;
                $tot_arr = $conn->getQueryValue($tot_enq_str);
                $tot_count = (count($tot_arr) > 0) ? $tot_arr[0]->data_count : '0';
                //non star enq
                $backtot_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' AND enq.is_starapp=0" . $manager_cond;
                $backtot_arr = $conn->getQueryValue($backtot_enq_str);
                $backtot_count = (count($backtot_arr) > 0) ? $backtot_arr[0]->data_count : '0';
                //star enq test
                $tottest_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59'   AND enq.is_starapp=1 AND enq.is_testride=1" . $manager_cond;
                $tottest_arr = $conn->getQueryValue($tottest_enq_str);
                $tottest_count = (count($tottest_arr) > 0) ? $tottest_arr[0]->data_count : '0';

                $brochure_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . TABLE_ENQBROCHURESHARE . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code  WHERE d.is_deleted=0 AND e.status=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' " . $manager_cond;
                $brochure_arr = $conn->getQueryValue($brochure_enq_str);
                $brochure_count = (count($brochure_arr) > 0) ? $brochure_arr[0]->data_count : '0';

                $quotation_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . TABLE_QUOTATIONSHARE . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code  WHERE d.is_deleted=0 AND e.status=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' " . $manager_cond;
                $quotation_arr = $conn->getQueryValue($quotation_enq_str);
                $quotation_count = (count($quotation_arr) > 0) ? $quotation_arr[0]->data_count : '0';

                $returnarr[] = array($dse_count, $user_count, $tot_count, $backtot_count, $tottest_count, $brochure_count, $quotation_count);

            } else if ($type == 'smpin') {
                $header = array('Enquiry Created HYPER-M App', 'Enquiry Created from Backend', 'Test Ride From HYPER-M App', 'Brochure shared', 'Quotation Shared');

                $manager_cond = " AND enq.smpin ='" . $smpin . "'";

                $tot_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq  WHERE  enq.is_dms=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59'  AND enq.is_starapp=1 " . $manager_cond;
                $tot_arr = $conn->getQueryValue($tot_enq_str);
                $tot_count = (count($tot_arr) > 0) ? $tot_arr[0]->data_count : '0';

                //non star enq
                $backtot_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq  WHERE  enq.is_dms=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' " . $manager_cond;
                $backtot_arr = $conn->getQueryValue($backtot_enq_str);
                $backtot_count = (count($backtot_arr) > 0) ? $backtot_arr[0]->data_count : '0';
                //star enq test
                $tottest_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq  WHERE enq.is_dms=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59'   AND enq.is_starapp=1 AND enq.is_testride=1" . $manager_cond;
                $tottest_arr = $conn->getQueryValue($tottest_enq_str);
                $tottest_count = (count($tottest_arr) > 0) ? $tottest_arr[0]->data_count : '0';

                $brochure_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . TABLE_ENQBROCHURESHARE . " as enq WHERE enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' " . $manager_cond;
                $brochure_arr = $conn->getQueryValue($brochure_enq_str);
                $brochure_count = (count($brochure_arr) > 0) ? $brochure_arr[0]->data_count : '0';

                $quotation_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . TABLE_QUOTATIONSHARE . " as enq  WHERE  enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59' " . $manager_cond;
                $quotation_arr = $conn->getQueryValue($quotation_enq_str);
                $quotation_count = (count($quotation_arr) > 0) ? $quotation_arr[0]->data_count : '0';

                $returnarr[] = array($tot_count, $backtot_count, $tottest_count, $brochure_count, $quotation_count);
            }

            $list_arr = $returnarr;
            $return_arr = array('list' => $list_arr, 'header' => $header);
            $conn->message(true, $conn->getMessage($lang, 'report', 'success1'), $return_arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'report', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'report', 'error4'));
    } catch (Exception $e) {
        $conn->message(false, $e->getMessage());
    }
}
function conversionratio()
{
    global $conn;
    try {
        $lang = "en_us";
        $pageData = $conn->loadJSON($lang);
        //$_SESSION["userid"] = 1;

        if (isset($_SESSION["userid"])) {

            $zone = escape_str($_REQUEST["zone"]);
            $zm_email = escape_str($_REQUEST["zm_email"]);
            $rm_email = escape_str($_REQUEST["rm_email"]);
            $am_email = escape_str($_REQUEST["am_email"]);
            $dealer_code = escape_str($_REQUEST["dealer_code"]);
            $smpin = escape_str($_REQUEST["smpin"]);
            $segment = escape_str($_REQUEST["segment"]);

            $start = escape_str($_REQUEST["start"]);
            $end = escape_str($_REQUEST["end"]);

            if (!($start != '' && $end != '')) {
                $conn->message(false, $conn->getMessage($lang, 'report', 'error7'));
            }

            $from_date = date('Y-m', strtotime($start . "-01")) . "-01";
            $to_date = date('Y-m-t', strtotime($end . "-01"));

            if (!(isValidDateTimeString($from_date, 'Y-m-d', 'Asia/Kolkata') && isValidDateTimeString($to_date, 'Y-m-d', 'Asia/Kolkata'))) {
                $conn->message(false, $conn->getMessage($lang, 'report', 'error7'));
            }

            $user_cond = '';
            if ($smpin != 'all' && $smpin != '') {
                //AM
                $type = "smpin";
            } else if ($dealer_code != 'all' && $dealer_code != '') {
                //AM
                $type = "dealer";
            } else if ($am_email != 'all' && $am_email != '') {
                //AM
                $type = "am";
            } else if ($rm_email != 'all' && $rm_email != '') {
                //RM
                $type = "rm";
            } else if ($zm_email != 'all' && $zm_email != '') {
                //ZM
                $type = "zm";
            } else if ($zone != 'all' && $zone != '') {
                //PAN
                $user_cond = '';
                $type = "zone";
            } else {
                //PAN
                $user_cond = '';
                $type = "pan";
            }

            $mcond = '';
            if (($smpin != 'all' && $smpin != '') || ($dealer_code != 'all' && $dealer_code != '') || ($am_email != 'all' && $am_email != '') || ($rm_email != 'all' && $rm_email != '') || ($zm_email != 'all' && $zm_email != '') || ($zone != 'all' && $zone != '')) {
                $mcond = '';
                $mcond .= ($smpin != 'all' && $smpin != '') ? " AND e.smpin='" . $smpin . "'" : '';
                $mcond .= ($dealer_code != 'all' && $dealer_code != '') ? " AND d.dealer_code='" . $dealer_code . "'" : '';
                $mcond .= ($am_email != 'all' && $am_email != '') ? " AND d.am_email='" . $am_email . "'" : '';
                $mcond .= ($rm_email != 'all' && $rm_email != '') ? " AND d.rm_email='" . $rm_email . "'" : '';
                $mcond .= ($zm_email != 'all' && $zm_email != '') ? " AND d.zm_email='" . $zm_email . "'" : '';
                $mcond .= ($zone != 'all' && $zone != '') ? " AND d.zone='" . $zone . "'" : '';

            }

            $returnarr = array();
            $tablejoin = '';
            $regtype = (escape_str($_REQUEST["regtype"]) == '1' || !isset($_REQUEST["regtype"])) ? "1" : "0";

            if ($regtype == '1') {
                $user_cond .= ' AND e.is_current=1 ';
            }

            if ($segment != '' && $segment > 0) {
                $user_cond .= " AND e.segment='" . $segment . "' ";
            }

            if ($type == 'pan') {

                $header = array('Zone', 'Total Dealer', 'Total DSE', 'Enquiry Target', 'Enquiry Achieved', 'Enquiry Achieved %', 'Sales Target', 'Sales Achieved', 'Sales Achieved %', 'Sales Conversion Ratio', 'Conversion %');

                //GET ALL ZM
                $getzmquery_str = "SELECT zone FROM " . DMS_DEALER . " d WHERE status=1 AND is_deleted='0'  GROUP BY zone ORDER BY zone ASC";
                $getzm_data = $conn->getQueryValue($getzmquery_str);

                if (count($getzm_data) > 0) {
                    foreach ($getzm_data as $val) {

                        $manager_cond = " AND d.zone='" . $val->zone . "'" . $user_cond;
                        $dealer_cond = "AND d.zone='" . $val->zone . "'";

                        $dealer_str = "SELECT COUNT(d.id) data_count FROM " . DMS_DEALER . " d WHERE d.status=1 AND d.is_deleted='0' " . $dealer_cond . " LIMIT 1";
                        $dealer_arr = $conn->getQueryValue($dealer_str);
                        $dealer_count = (count($dealer_arr) > 0) ? $dealer_arr[0]->data_count : '0';

                        $dse_str = "SELECT COUNT(e.id) data_count FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond . " LIMIT 1";
                        $dse_arr = $conn->getQueryValue($dse_str);
                        $dse_count = (count($dse_arr) > 0) ? $dse_arr[0]->data_count : '0';

                        $user_str = "SELECT COUNT(u.userid) data_count FROM " . TABLE_USER . " u JOIN " . DMS_EMPLOYEE . " e ON u.smpin=e.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND u.is_deleted=0 AND u.role>7 AND u.role<14 " . $manager_cond . " LIMIT 1";
                        $user_arr = $conn->getQueryValue($user_str);
                        $user_count = (count($user_arr) > 0) ? $user_arr[0]->data_count : '0';

                        //sales target
                        $salestarget_str = "SELECT COALESCE(SUM(t.target),0) as data_count FROM " . TABLE_USER_SALETARGET . " t JOIN " . DMS_EMPLOYEE . " as e ON e.smpin=t.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND  t.sale_month>='" . $from_date . "' AND t.sale_month<='" . $to_date . "' " . $manager_cond;
                        $salestarget_arr = $conn->getQueryValue($salestarget_str);
                        $salestarget_count = (count($salestarget_arr) > 0) ? $salestarget_arr[0]->data_count : '0';

                        $enq_target = 0;
                        switch ($segment) {
                            case '1':$enq_target_count = $salestarget_count * 5;
                                break;
                            case '2':$enq_target_count = $salestarget_count * 3;
                                break;
                            default:$enq_target_count = $salestarget_count * 3;
                                break;
                        }

                        //enquiry achieved
                        $tot_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59'  " . $manager_cond;
                        $tot_arr = $conn->getQueryValue($tot_enq_str);
                        $totenq_count = (count($tot_arr) > 0) ? $tot_arr[0]->data_count : '0';
                        $enq_per = ($enq_target_count > 0) ? ($totenq_count / $enq_target_count) * 100 : '-';
                        $enq_per = floatround($enq_per);

                        //sales acheived
                        $sales_str = "SELECT COALESCE(SUM(s.sales_count),0) as data_count FROM " . DMS_EMPLOYEE_SALES . " s JOIN " . DMS_EMPLOYEE . " as e ON e.smpin=s.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND  s.sale_date>='" . $from_date . "' AND s.sale_date<='" . $to_date . "' " . $manager_cond;
                        $sales_arr = $conn->getQueryValue($sales_str);
                        $tot_sales = (count($sales_arr) > 0) ? $sales_arr[0]->data_count : '0';
                        $sales_per = ($salestarget_count > 0) ? ($tot_sales / $salestarget_count) * 100 : '-';
                        $sales_per = floatround($sales_per);

                        $sale_ratio = ratio($totenq_count, $tot_sales);
                        $conversion_per = ($totenq_count > 0) ? ($tot_sales / $totenq_count) * 100 : '-';
                        $conversion_per = floatround($conversion_per);

                        $returnarr[] = array($val->zone, $dealer_count, $dse_count, $enq_target_count, $totenq_count, $enq_per, $salestarget_count, $tot_sales, $sales_per, $sale_ratio, $conversion_per);
                    }
                }
            } else if (($type == 'zone') || ($type == 'zm') || ($type == 'rm') || ($type == 'am')) {

                $header = array('Total Dealer', 'Total DSE', 'Enquiry Target', 'Enquiry Achieved', 'Enquiry Achieved %', 'Sales Target', 'Sales Achieved', 'Sales Achieved %', 'Sales Conversion Ratio', 'Conversion %');

                $manager_cond = $mcond . $user_cond;
                $dealer_cond = $mcond;
                $dealer_str = "SELECT COUNT(d.id) data_count FROM " . DMS_DEALER . " d WHERE d.status=1 AND d.is_deleted='0' " . $dealer_cond . " LIMIT 1";
                $dealer_arr = $conn->getQueryValue($dealer_str);
                $dealer_count = (count($dealer_arr) > 0) ? $dealer_arr[0]->data_count : '0';

                $dse_str = "SELECT COUNT(e.id) data_count FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond . " LIMIT 1";
                $dse_arr = $conn->getQueryValue($dse_str);
                $dse_count = (count($dse_arr) > 0) ? $dse_arr[0]->data_count : '0';
                //sales target
                $salestarget_str = "SELECT COALESCE(SUM(t.target),0) as data_count FROM " . TABLE_USER_SALETARGET . " t JOIN " . DMS_EMPLOYEE . " as e ON e.smpin=t.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND  t.sale_month>='" . $from_date . "' AND t.sale_month<='" . $to_date . "' " . $manager_cond;
                $salestarget_arr = $conn->getQueryValue($salestarget_str);
                $salestarget_count = (count($salestarget_arr) > 0) ? $salestarget_arr[0]->data_count : '0';

                $enq_target = 0;
                switch ($segment) {
                    case '1':$enq_target_count = $salestarget_count * 5;
                        break;
                    case '2':$enq_target_count = $salestarget_count * 3;
                        break;
                    default:$enq_target_count = $salestarget_count * 3;
                        break;
                }

                //enquiry achieved
                $tot_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59'  " . $manager_cond;
                $tot_arr = $conn->getQueryValue($tot_enq_str);
                $totenq_count = (count($tot_arr) > 0) ? $tot_arr[0]->data_count : '0';
                $enq_per = ($enq_target_count > 0) ? ($totenq_count / $enq_target_count) * 100 : '-';
                $enq_per = floatround($enq_per);

                //sales acheived
                $sales_str = "SELECT COALESCE(SUM(s.sales_count),0) as data_count FROM " . DMS_EMPLOYEE_SALES . " s JOIN " . DMS_EMPLOYEE . " as e ON e.smpin=s.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND  s.sale_date>='" . $from_date . "' AND s.sale_date<='" . $to_date . "' " . $manager_cond;
                $sales_arr = $conn->getQueryValue($sales_str);
                $tot_sales = (count($sales_arr) > 0) ? $sales_arr[0]->data_count : '0';
                $sales_per = ($salestarget_count > 0) ? ($tot_sales / $salestarget_count) * 100 : '-';
                $sales_per = floatround($sales_per);

                $sale_ratio = ratio($totenq_count, $tot_sales);
                $conversion_per = ($totenq_count > 0) ? ($tot_sales / $totenq_count) * 100 : '-';
                $conversion_per = floatround($conversion_per);

                $returnarr[] = array($dealer_count, $dse_count, $enq_target_count, $totenq_count, $enq_per, $salestarget_count, $tot_sales, $sales_per, $sale_ratio, $conversion_per);

            } else if ($type == 'dealer') {
                $header = array('Total DSE', 'Enquiry Target', 'Enquiry Achieved', 'Enquiry Achieved %', 'Sales Target', 'Sales Achieved', 'Sales Achieved %', 'Sales Conversion Ratio', 'Conversion %');

                $manager_cond = $mcond . $user_cond;

                $dse_str = "SELECT COUNT(e.id) data_count FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond . " LIMIT 1";
                $dse_arr = $conn->getQueryValue($dse_str);
                $dse_count = (count($dse_arr) > 0) ? $dse_arr[0]->data_count : '0';

                //sales target
                $salestarget_str = "SELECT COALESCE(SUM(t.target),0) as data_count FROM " . TABLE_USER_SALETARGET . " t JOIN " . DMS_EMPLOYEE . " as e ON e.smpin=t.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND  t.sale_month>='" . $from_date . "' AND t.sale_month<='" . $to_date . "' " . $manager_cond;
                $salestarget_arr = $conn->getQueryValue($salestarget_str);
                $salestarget_count = (count($salestarget_arr) > 0) ? $salestarget_arr[0]->data_count : '0';

                $enq_target = 0;
                switch ($segment) {
                    case '1':$enq_target_count = $salestarget_count * 5;
                        break;
                    case '2':$enq_target_count = $salestarget_count * 3;
                        break;
                    default:$enq_target_count = $salestarget_count * 3;
                        break;
                }

                //enquiry achieved
                $tot_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59'  " . $manager_cond;
                $tot_arr = $conn->getQueryValue($tot_enq_str);
                $totenq_count = (count($tot_arr) > 0) ? $tot_arr[0]->data_count : '0';
                $enq_per = ($enq_target_count > 0) ? ($totenq_count / $enq_target_count) * 100 : '-';
                $enq_per = floatround($enq_per);

                //sales acheived
                $sales_str = "SELECT COALESCE(SUM(s.sales_count),0) as data_count FROM " . DMS_EMPLOYEE_SALES . " s JOIN " . DMS_EMPLOYEE . " as e ON e.smpin=s.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND  s.sale_date>='" . $from_date . "' AND s.sale_date<='" . $to_date . "' " . $manager_cond;
                $sales_arr = $conn->getQueryValue($sales_str);
                $tot_sales = (count($sales_arr) > 0) ? $sales_arr[0]->data_count : '0';
                $sales_per = ($salestarget_count > 0) ? ($tot_sales / $salestarget_count) * 100 : '-';
                $sales_per = floatround($sales_per);

                $sale_ratio = ratio($totenq_count, $tot_sales);
                $conversion_per = ($totenq_count > 0) ? ($tot_sales / $totenq_count) * 100 : '-';
                $conversion_per = floatround($conversion_per);

                $returnarr[] = array($dse_count, $enq_target_count, $totenq_count, $enq_per, $salestarget_count, $tot_sales, $sales_per, $sale_ratio, $conversion_per);

            } else if ($type == 'smpin') {
                $header = array('Enquiry Target', 'Enquiry Achieved', 'Enquiry Achieved %', 'Sales Target', 'Sales Achieved', 'Sales Achieved %', 'Sales Conversion Ratio', 'Conversion %');

                $manager_cond = " AND e.smpin ='" . $smpin . "'";

                //sales target
                $salestarget_str = "SELECT COALESCE(SUM(t.target),0) as data_count FROM " . TABLE_USER_SALETARGET . " t JOIN " . DMS_EMPLOYEE . " as e ON e.smpin=t.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND  t.sale_month>='" . $from_date . "' AND t.sale_month<='" . $to_date . "' " . $manager_cond;
                $salestarget_arr = $conn->getQueryValue($salestarget_str);
                $salestarget_count = (count($salestarget_arr) > 0) ? $salestarget_arr[0]->data_count : '0';

                $enq_target = 0;
                switch ($segment) {
                    case '1':$enq_target_count = $salestarget_count * 5;
                        break;
                    case '2':$enq_target_count = $salestarget_count * 3;
                        break;
                    default:$enq_target_count = $salestarget_count * 3;
                        break;
                }

                //enquiry achieved
                $tot_enq_str = "SELECT  COUNT(enq.id) as data_count FROM " . DMS_ENQUIRY_MASTER . " as enq LEFT JOIN " . DMS_EMPLOYEE . " e ON e.smpin=enq.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND enq.is_dms=1 AND enq.created_on>='" . $from_date . " 00:00:00' AND enq.created_on<='" . $to_date . " 23:59:59'  " . $manager_cond;
                $tot_arr = $conn->getQueryValue($tot_enq_str);
                $totenq_count = (count($tot_arr) > 0) ? $tot_arr[0]->data_count : '0';
                $enq_per = ($enq_target_count > 0) ? ($totenq_count / $enq_target_count) * 100 : '-';
                $enq_per = floatround($enq_per);

                //sales acheived
                $sales_str = "SELECT COALESCE(SUM(s.sales_count),0) as data_count FROM " . DMS_EMPLOYEE_SALES . " s JOIN " . DMS_EMPLOYEE . " as e ON e.smpin=s.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND  s.sale_date>='" . $from_date . "' AND s.sale_date<='" . $to_date . "' " . $manager_cond;
                $sales_arr = $conn->getQueryValue($sales_str);
                $tot_sales = (count($sales_arr) > 0) ? $sales_arr[0]->data_count : '0';
                $sales_per = ($salestarget_count > 0) ? ($tot_sales / $salestarget_count) * 100 : '-';
                $sales_per = floatround($sales_per);

                $sale_ratio = ratio($totenq_count, $tot_sales);
                $conversion_per = ($totenq_count > 0) ? ($tot_sales / $totenq_count) * 100 : '-';
                $conversion_per = floatround($conversion_per);

                $returnarr[] = array($enq_target_count, $totenq_count, $enq_per, $salestarget_count, $tot_sales, $sales_per, $sale_ratio, $conversion_per);
            }

            $list_arr = $returnarr;
            $return_arr = array('list' => $list_arr, 'header' => $header);
            $conn->message(true, $conn->getMessage($lang, 'report', 'success1'), $return_arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'report', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'report', 'error4'));
    } catch (Exception $e) {
        $conn->message(false, $e->getMessage());
    }
}

function productivity()
{
    global $conn;
    try {
        $lang = "en_us";
        $pageData = $conn->loadJSON($lang);
        //$_SESSION["userid"] = 1;

        if (isset($_SESSION["userid"])) {

            $zone = escape_str($_REQUEST["zone"]);
            $zm_email = escape_str($_REQUEST["zm_email"]);
            $rm_email = escape_str($_REQUEST["rm_email"]);
            $am_email = escape_str($_REQUEST["am_email"]);
            $dealer_code = escape_str($_REQUEST["dealer_code"]);
            $smpin = escape_str($_REQUEST["smpin"]);
            $segment = escape_str($_REQUEST["segment"]);

            $start = escape_str($_REQUEST["start"]);
            $end = escape_str($_REQUEST["end"]);

            if (!($start != '' && $end != '')) {
                $conn->message(false, $conn->getMessage($lang, 'report', 'error7'));
            }

            $from_date = date('Y-m', strtotime($start . "-01")) . "-01";
            $to_date = date('Y-m-t', strtotime($end . "-01"));

            if (!(isValidDateTimeString($from_date, 'Y-m-d', 'Asia/Kolkata') && isValidDateTimeString($to_date, 'Y-m-d', 'Asia/Kolkata'))) {
                $conn->message(false, $conn->getMessage($lang, 'report', 'error7'));
            }

            $user_cond = '';
            if ($smpin != 'all' && $smpin != '') {
                //AM
                $type = "smpin";
            } else if ($dealer_code != 'all' && $dealer_code != '') {
                //AM
                $type = "dealer";
            } else if ($am_email != 'all' && $am_email != '') {
                //AM
                $type = "am";
            } else if ($rm_email != 'all' && $rm_email != '') {
                //RM
                $type = "rm";
            } else if ($zm_email != 'all' && $zm_email != '') {
                //ZM
                $type = "zm";
            } else if ($zone != 'all' && $zone != '') {
                //PAN
                $user_cond = '';
                $type = "zone";
            } else {
                //PAN
                $user_cond = '';
                $type = "pan";
            }

            $mcond = '';
            if (($smpin != 'all' && $smpin != '') || ($dealer_code != 'all' && $dealer_code != '') || ($am_email != 'all' && $am_email != '') || ($rm_email != 'all' && $rm_email != '') || ($zm_email != 'all' && $zm_email != '') || ($zone != 'all' && $zone != '')) {
                $mcond = '';
                $mcond .= ($smpin != 'all' && $smpin != '') ? " AND e.smpin='" . $smpin . "'" : '';
                $mcond .= ($dealer_code != 'all' && $dealer_code != '') ? " AND d.dealer_code='" . $dealer_code . "'" : '';
                $mcond .= ($am_email != 'all' && $am_email != '') ? " AND d.am_email='" . $am_email . "'" : '';
                $mcond .= ($rm_email != 'all' && $rm_email != '') ? " AND d.rm_email='" . $rm_email . "'" : '';
                $mcond .= ($zm_email != 'all' && $zm_email != '') ? " AND d.zm_email='" . $zm_email . "'" : '';
                $mcond .= ($zone != 'all' && $zone != '') ? " AND d.zone='" . $zone . "'" : '';

            }

            $returnarr = array();
            $tablejoin = '';
            $regtype = (escape_str($_REQUEST["regtype"]) == '1' || !isset($_REQUEST["regtype"])) ? "1" : "0";

            if ($regtype == '1') {
                $user_cond .= ' AND e.is_current=1 ';
            }

            if ($segment != '' && $segment > 0) {
                $user_cond .= " AND e.segment='" . $segment . "' ";
            }

            if ($type == 'pan') {
//Total Sales    Zero Productive DSEs    Productivity 1 to 5    Productivity 6 to 10    Productivity 11 to 20    Productivity 21 to 30    Productivity 30 and above     Average Productivity

                $header = array('Zone', 'Total Dealer', 'Total DSE', 'Total Sales', 'Zero Productive DSEs', 'Productivity 1 to 5', 'Productivity 6 to 10', 'Productivity 11 to 20', 'Productivity 21 to 30', 'Productivity 30 and above', 'Average Productivity');

                //GET ALL ZM
                $getzmquery_str = "SELECT zone FROM " . DMS_DEALER . " d WHERE status=1 AND is_deleted='0'  GROUP BY zone ORDER BY zone ASC";
                $getzm_data = $conn->getQueryValue($getzmquery_str);

                if (count($getzm_data) > 0) {
                    foreach ($getzm_data as $val) {

                        $manager_cond = " AND d.zone='" . $val->zone . "'" . $user_cond;
                        $dealer_cond = "AND d.zone='" . $val->zone . "'";

                        $dealer_str = "SELECT COUNT(d.id) data_count FROM " . DMS_DEALER . " d WHERE d.status=1 AND d.is_deleted='0' " . $dealer_cond . " LIMIT 1";
                        $dealer_arr = $conn->getQueryValue($dealer_str);
                        $dealer_count = (count($dealer_arr) > 0) ? $dealer_arr[0]->data_count : '0';

                        $dse_str = "SELECT COUNT(e.id) data_count FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond . " LIMIT 1";
                        $dse_arr = $conn->getQueryValue($dse_str);
                        $dse_count = (count($dse_arr) > 0) ? $dse_arr[0]->data_count : '0';

                        $sales_str = "SELECT COALESCE(SUM(s.sales_count),0) as data_count FROM " . DMS_EMPLOYEE_SALES . " s JOIN " . DMS_EMPLOYEE . " as e ON e.smpin=s.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND  s.sale_date>='" . $from_date . "' AND s.sale_date<='" . $to_date . "' " . $manager_cond;
                        $sales_arr = $conn->getQueryValue($sales_str);
                        $tot_sales = (count($sales_arr) > 0) ? $sales_arr[0]->data_count : '0';

                        $zero_productive_emp_str = "SELECT  COUNT(DISTINCT e.smpin)  as data_count FROM " . DMS_EMPLOYEE . " as e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code LEFT
                        JOIN (SELECT id,smpin FROM " . DMS_EMPLOYEE_SALES . "  WHERE  sale_date>='" . $from_date . "'  AND  sale_date<='" . $to_date . "') s ON e.smpin=s.smpin
                        WHERE d.is_deleted=0  AND e.status=1  AND s.smpin IS NULL  " . $manager_cond;
                        $zero_productive_arr = $conn->getQueryValue($zero_productive_emp_str);
                        $zero_productive_count = (count($zero_productive_arr) > 0) ? $zero_productive_arr[0]->data_count : '0';

                        $productive5_str = "SELECT  COUNT(DISTINCT e.smpin)  as data_count FROM " . DMS_EMPLOYEE . " as e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code                         JOIN (SELECT id,smpin FROM " . DMS_EMPLOYEE_SALES . "  WHERE  sale_date>='" . $from_date . "'  AND  sale_date<='" . $to_date . "' GROUP BY smpin HAVING COUNT(id)>=1 AND COUNT(id)<=5) s ON e.smpin=s.smpin
                        WHERE d.is_deleted=0  AND e.status=1 " . $manager_cond;
                        $productive5_arr = $conn->getQueryValue($productive5_str);
                        $productive5_count = (count($productive5_arr) > 0) ? $productive5_arr[0]->data_count : '0';

                        $productive10_str = "SELECT  COUNT(DISTINCT e.smpin)  as data_count FROM " . DMS_EMPLOYEE . " as e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code
                        JOIN (SELECT id,smpin FROM " . DMS_EMPLOYEE_SALES . "  WHERE  sale_date>='" . $from_date . "'  AND  sale_date<='" . $to_date . "' GROUP BY smpin HAVING COUNT(id)>5 AND COUNT(id)<=10) s ON e.smpin=s.smpin
                        WHERE d.is_deleted=0  AND e.status=1 " . $manager_cond;
                        $productive10_arr = $conn->getQueryValue($productive10_str);
                        $productive10_count = (count($productive10_arr) > 0) ? $productive10_arr[0]->data_count : '0';

                        $productive20_str = "SELECT  COUNT(DISTINCT e.smpin)  as data_count FROM " . DMS_EMPLOYEE . " as e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code
                        JOIN (SELECT id,smpin FROM " . DMS_EMPLOYEE_SALES . "  WHERE  sale_date>='" . $from_date . "'  AND  sale_date<='" . $to_date . "' GROUP BY smpin HAVING COUNT(id)>10 AND COUNT(id)<=20) s ON e.smpin=s.smpin
                        WHERE d.is_deleted=0  AND e.status=1 " . $manager_cond;
                        $productive20_arr = $conn->getQueryValue($productive20_str);
                        $productive20_count = (count($productive20_arr) > 0) ? $productive20_arr[0]->data_count : '0';

                        $productive30_str = "SELECT  COUNT(DISTINCT e.smpin)  as data_count FROM " . DMS_EMPLOYEE . " as e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code
                        JOIN (SELECT id,smpin FROM " . DMS_EMPLOYEE_SALES . "  WHERE  sale_date>='" . $from_date . "'  AND  sale_date<='" . $to_date . "' GROUP BY smpin HAVING COUNT(id)>20 AND COUNT(id)<=30) s ON e.smpin=s.smpin
                        WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond;
                        $productive30_arr = $conn->getQueryValue($productive30_str);
                        $productive30_count = (count($productive30_arr) > 0) ? $productive30_arr[0]->data_count : '0';

                        $productiveabove30_str = "SELECT  COUNT(DISTINCT e.smpin)  as data_count FROM " . DMS_EMPLOYEE . " as e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code
                        JOIN (SELECT id,smpin FROM " . DMS_EMPLOYEE_SALES . "  WHERE  sale_date>='" . $from_date . "'  AND  sale_date<='" . $to_date . "' GROUP BY smpin HAVING COUNT(id)>30) s ON e.smpin=s.smpin
                        WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond;
                        $productiveabove30_arr = $conn->getQueryValue($productiveabove30_str);
                        $productiveabove30_count = (count($productiveabove30_arr) > 0) ? $productiveabove30_arr[0]->data_count : '0';

                        $avg_productive = ($dse_count > 0) ? ($tot_sales / $dse_count) : '0';
                        $avg_productive = floatround($avg_productive);

                        $returnarr[] = array($val->zone, $dealer_count, $dse_count, $tot_sales, $zero_productive_count, $productive5_count, $productive10_count, $productive20_count, $productive30_count, $productiveabove30_count, $avg_productive);
                    }
                }
            } else if (($type == 'zone') || ($type == 'zm') || ($type == 'rm') || ($type == 'am')) {

                $header = array('Total Dealer', 'Total DSE', 'Total Sales', 'Zero Productive DSEs', 'Productivity 1 to 5', 'Productivity 6 to 10', 'Productivity 11 to 20', 'Productivity 21 to 30', 'Productivity 30 and above', 'Average Productivity');

                $manager_cond = $mcond . $user_cond;
                $dealer_cond = $mcond;
                $dealer_str = "SELECT COUNT(d.id) data_count FROM " . DMS_DEALER . " d WHERE d.status=1 AND d.is_deleted='0' " . $dealer_cond . " LIMIT 1";
                $dealer_arr = $conn->getQueryValue($dealer_str);
                $dealer_count = (count($dealer_arr) > 0) ? $dealer_arr[0]->data_count : '0';

                $dse_str = "SELECT COUNT(e.id) data_count FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond . " LIMIT 1";
                $dse_arr = $conn->getQueryValue($dse_str);
                $dse_count = (count($dse_arr) > 0) ? $dse_arr[0]->data_count : '0';

                $sales_str = "SELECT COALESCE(SUM(s.sales_count),0) as data_count FROM " . DMS_EMPLOYEE_SALES . " s JOIN " . DMS_EMPLOYEE . " as e ON e.smpin=s.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND  s.sale_date>='" . $from_date . "' AND s.sale_date<='" . $to_date . "' " . $manager_cond;
                $sales_arr = $conn->getQueryValue($sales_str);
                $tot_sales = (count($sales_arr) > 0) ? $sales_arr[0]->data_count : '0';

                $zero_productive_emp_str = "SELECT  COUNT(DISTINCT e.smpin)  as data_count FROM " . DMS_EMPLOYEE . " as e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code LEFT
                JOIN (SELECT id,smpin FROM " . DMS_EMPLOYEE_SALES . "  WHERE  sale_date>='" . $from_date . "'  AND  sale_date<='" . $to_date . "') s ON e.smpin=s.smpin
                WHERE d.is_deleted=0  AND e.status=1  AND s.smpin IS NULL  " . $manager_cond;
                $zero_productive_arr = $conn->getQueryValue($zero_productive_emp_str);
                $zero_productive_count = (count($zero_productive_arr) > 0) ? $zero_productive_arr[0]->data_count : '0';

                $productive5_str = "SELECT  COUNT(DISTINCT e.smpin)  as data_count FROM " . DMS_EMPLOYEE . " as e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code                         JOIN (SELECT id,smpin FROM " . DMS_EMPLOYEE_SALES . "  WHERE  sale_date>='" . $from_date . "'  AND  sale_date<='" . $to_date . "' GROUP BY smpin HAVING COUNT(id)>=1 AND COUNT(id)<=5) s ON e.smpin=s.smpin
                        WHERE d.is_deleted=0  AND e.status=1 " . $manager_cond;
                $productive5_arr = $conn->getQueryValue($productive5_str);
                $productive5_count = (count($productive5_arr) > 0) ? $productive5_arr[0]->data_count : '0';

                $productive10_str = "SELECT  COUNT(DISTINCT e.smpin)  as data_count FROM " . DMS_EMPLOYEE . " as e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code
                JOIN (SELECT id,smpin FROM " . DMS_EMPLOYEE_SALES . "  WHERE  sale_date>='" . $from_date . "'  AND  sale_date<='" . $to_date . "' GROUP BY smpin HAVING COUNT(id)>5 AND COUNT(id)<=10) s ON e.smpin=s.smpin
                WHERE d.is_deleted=0  AND e.status=1 " . $manager_cond;
                $productive10_arr = $conn->getQueryValue($productive10_str);
                $productive10_count = (count($productive10_arr) > 0) ? $productive10_arr[0]->data_count : '0';

                $productive20_str = "SELECT  COUNT(DISTINCT e.smpin)  as data_count FROM " . DMS_EMPLOYEE . " as e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code
                JOIN (SELECT id,smpin FROM " . DMS_EMPLOYEE_SALES . "  WHERE  sale_date>='" . $from_date . "'  AND  sale_date<='" . $to_date . "' GROUP BY smpin HAVING COUNT(id)>10 AND COUNT(id)<=20) s ON e.smpin=s.smpin
                WHERE d.is_deleted=0  AND e.status=1 " . $manager_cond;
                $productive20_arr = $conn->getQueryValue($productive20_str);
                $productive20_count = (count($productive20_arr) > 0) ? $productive20_arr[0]->data_count : '0';

                $productive30_str = "SELECT  COUNT(DISTINCT e.smpin)  as data_count FROM " . DMS_EMPLOYEE . " as e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code
                JOIN (SELECT id,smpin FROM " . DMS_EMPLOYEE_SALES . "  WHERE  sale_date>='" . $from_date . "'  AND  sale_date<='" . $to_date . "' GROUP BY smpin HAVING COUNT(id)>20 AND COUNT(id)<=30) s ON e.smpin=s.smpin
                WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond;
                $productive30_arr = $conn->getQueryValue($productive30_str);
                $productive30_count = (count($productive30_arr) > 0) ? $productive30_arr[0]->data_count : '0';

                $productiveabove30_str = "SELECT  COUNT(DISTINCT e.smpin)  as data_count FROM " . DMS_EMPLOYEE . " as e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code
                JOIN (SELECT id,smpin FROM " . DMS_EMPLOYEE_SALES . "  WHERE  sale_date>='" . $from_date . "'  AND  sale_date<='" . $to_date . "' GROUP BY smpin HAVING COUNT(id)>30) s ON e.smpin=s.smpin
                WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond;
                $productiveabove30_arr = $conn->getQueryValue($productiveabove30_str);
                $productiveabove30_count = (count($productiveabove30_arr) > 0) ? $productiveabove30_arr[0]->data_count : '0';

                $avg_productive = ($dse_count > 0) ? ($tot_sales / $dse_count) : '0';
                $avg_productive = floatround($avg_productive);

                $returnarr[] = array($dealer_count, $dse_count, $tot_sales, $zero_productive_count, $productive5_count, $productive10_count, $productive20_count, $productive30_count, $productiveabove30_count, $avg_productive);

            } else if ($type == 'dealer') {
                $header = array('Total DSE', 'Total Sales', 'Zero Productive DSEs', 'Productivity 1 to 5', 'Productivity 6 to 10', 'Productivity 11 to 20', 'Productivity 21 to 30', 'Productivity 30 and above', 'Average Productivity');

                $manager_cond = $mcond . $user_cond;

                $dse_str = "SELECT COUNT(e.id) data_count FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond . " LIMIT 1";
                $dse_arr = $conn->getQueryValue($dse_str);
                $dse_count = (count($dse_arr) > 0) ? $dse_arr[0]->data_count : '0';

                $sales_str = "SELECT COALESCE(SUM(s.sales_count),0) as data_count FROM " . DMS_EMPLOYEE_SALES . " s JOIN " . DMS_EMPLOYEE . " as e ON e.smpin=s.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND  s.sale_date>='" . $from_date . "' AND s.sale_date<='" . $to_date . "' " . $manager_cond;
                $sales_arr = $conn->getQueryValue($sales_str);
                $tot_sales = (count($sales_arr) > 0) ? $sales_arr[0]->data_count : '0';

                $zero_productive_emp_str = "SELECT  COUNT(DISTINCT e.smpin)  as data_count FROM " . DMS_EMPLOYEE . " as e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code LEFT
                JOIN (SELECT id,smpin FROM " . DMS_EMPLOYEE_SALES . "  WHERE  sale_date>='" . $from_date . "'  AND  sale_date<='" . $to_date . "') s ON e.smpin=s.smpin
                WHERE d.is_deleted=0  AND e.status=1  AND s.smpin IS NULL  " . $manager_cond;
                $zero_productive_arr = $conn->getQueryValue($zero_productive_emp_str);
                $zero_productive_count = (count($zero_productive_arr) > 0) ? $zero_productive_arr[0]->data_count : '0';

                $productive5_str = "SELECT  COUNT(DISTINCT e.smpin)  as data_count FROM " . DMS_EMPLOYEE . " as e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code                         JOIN (SELECT id,smpin FROM " . DMS_EMPLOYEE_SALES . "  WHERE  sale_date>='" . $from_date . "'  AND  sale_date<='" . $to_date . "' GROUP BY smpin HAVING COUNT(id)>=1 AND COUNT(id)<=5) s ON e.smpin=s.smpin
                        WHERE d.is_deleted=0  AND e.status=1 " . $manager_cond;
                $productive5_arr = $conn->getQueryValue($productive5_str);
                $productive5_count = (count($productive5_arr) > 0) ? $productive5_arr[0]->data_count : '0';

                $productive10_str = "SELECT  COUNT(DISTINCT e.smpin)  as data_count FROM " . DMS_EMPLOYEE . " as e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code
                JOIN (SELECT id,smpin FROM " . DMS_EMPLOYEE_SALES . "  WHERE  sale_date>='" . $from_date . "'  AND  sale_date<='" . $to_date . "' GROUP BY smpin HAVING COUNT(id)>5 AND COUNT(id)<=10) s ON e.smpin=s.smpin
                WHERE d.is_deleted=0  AND e.status=1 " . $manager_cond;
                $productive10_arr = $conn->getQueryValue($productive10_str);
                $productive10_count = (count($productive10_arr) > 0) ? $productive10_arr[0]->data_count : '0';

                $productive20_str = "SELECT  COUNT(DISTINCT e.smpin)  as data_count FROM " . DMS_EMPLOYEE . " as e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code
                JOIN (SELECT id,smpin FROM " . DMS_EMPLOYEE_SALES . "  WHERE  sale_date>='" . $from_date . "'  AND  sale_date<='" . $to_date . "' GROUP BY smpin HAVING COUNT(id)>10 AND COUNT(id)<=20) s ON e.smpin=s.smpin
                WHERE d.is_deleted=0  AND e.status=1 " . $manager_cond;
                $productive20_arr = $conn->getQueryValue($productive20_str);
                $productive20_count = (count($productive20_arr) > 0) ? $productive20_arr[0]->data_count : '0';

                $productive30_str = "SELECT  COUNT(DISTINCT e.smpin)  as data_count FROM " . DMS_EMPLOYEE . " as e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code
                JOIN (SELECT id,smpin FROM " . DMS_EMPLOYEE_SALES . "  WHERE  sale_date>='" . $from_date . "'  AND  sale_date<='" . $to_date . "' GROUP BY smpin HAVING COUNT(id)>20 AND COUNT(id)<=30) s ON e.smpin=s.smpin
                WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond;
                $productive30_arr = $conn->getQueryValue($productive30_str);
                $productive30_count = (count($productive30_arr) > 0) ? $productive30_arr[0]->data_count : '0';

                $productiveabove30_str = "SELECT  COUNT(DISTINCT e.smpin)  as data_count FROM " . DMS_EMPLOYEE . " as e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code
                JOIN (SELECT id,smpin FROM " . DMS_EMPLOYEE_SALES . "  WHERE  sale_date>='" . $from_date . "'  AND  sale_date<='" . $to_date . "' GROUP BY smpin HAVING COUNT(id)>30) s ON e.smpin=s.smpin
                WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond;
                $productiveabove30_arr = $conn->getQueryValue($productiveabove30_str);
                $productiveabove30_count = (count($productiveabove30_arr) > 0) ? $productiveabove30_arr[0]->data_count : '0';

                $avg_productive = ($dse_count > 0) ? ($tot_sales / $dse_count) : '0';
                $avg_productive = floatround($avg_productive);

                $returnarr[] = array($dse_count, $tot_sales, $zero_productive_count, $productive5_count, $productive10_count, $productive20_count, $productive30_count, $productiveabove30_count, $avg_productive);

            } else if ($type == 'smpin') {
                //$header = array('Total Sales', 'Zero Productive DSEs', 'Productivity 1 to 5', 'Productivity 6 to 10', 'Productivity 11 to 20', 'Productivity 21 to 30', 'Productivity 30 and above', 'Average Productivity');

                $header = array('Total Sales');

                $manager_cond = " AND e.smpin ='" . $smpin . "'";

                $sales_str = "SELECT COALESCE(SUM(s.sales_count),0) as data_count FROM " . DMS_EMPLOYEE_SALES . " s  WHERE  s.sale_date>='" . $from_date . "' AND s.sale_date<='" . $to_date . "' AND s.smpin ='" . $smpin . "'";
                $sales_arr = $conn->getQueryValue($sales_str);
                $tot_sales = (count($sales_arr) > 0) ? $sales_arr[0]->data_count : '0';

                $zero_productive_count = '-';
                $productive5_count = '-';
                $productive10_count = '-';
                $productive20_count = '-';
                $productive30_count = '-';
                $productiveabove30_count = '-';
                $avg_productive = '-';
                $returnarr[] = array($tot_sales);
                // $returnarr[] = array($tot_sales, $zero_productive_count, $productive5_count, $productive10_count, $productive20_count, $productive30_count, $productiveabove30_count, $avg_productive);
            }

            $list_arr = $returnarr;
            $return_arr = array('list' => $list_arr, 'header' => $header);
            $conn->message(true, $conn->getMessage($lang, 'report', 'success1'), $return_arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'report', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'report', 'error4'));
    } catch (Exception $e) {
        $conn->message(false, $e->getMessage());
    }
}

function enquiry_report_terms_condition()
{
    global $conn;
    try {
        $lang = "en_us";
        $pageData = $conn->loadJSON($lang);
        //$_SESSION["userid"] = 1;

        if (isset($_SESSION["userid"])) {

            $zone = escape_str($_REQUEST["zone"]);
            $zm_email = escape_str($_REQUEST["zm_email"]);
            $rm_email = escape_str($_REQUEST["rm_email"]);
            $am_email = escape_str($_REQUEST["am_email"]);
            $dealer_code = escape_str($_REQUEST["dealer_code"]);

            $user_cond = '';
            if ($dealer_code != 'all' && $dealer_code != '') {
                //AM
                $type = "dealer";
            } else if ($am_email != 'all' && $am_email != '') {
                //AM
                $type = "am";
            } else if ($rm_email != 'all' && $rm_email != '') {
                //RM
                $type = "rm";
            } else if ($zm_email != 'all' && $zm_email != '') {
                //ZM
                $type = "zm";
            } else if ($zone != 'all' && $zone != '') {
                //PAN
                $user_cond = '';
                $type = "zone";
            } else {
                //PAN
                $user_cond = '';
                $type = "pan";
            }

            $mcond = '';
            if (($dealer_code != 'all' && $dealer_code != '') || ($am_email != 'all' && $am_email != '') || ($rm_email != 'all' && $rm_email != '') || ($zm_email != 'all' && $zm_email != '') || ($zone != 'all' && $zone != '')) {
                $mcond = '';

                $mcond .= ($dealer_code != 'all' && $dealer_code != '') ? " AND d.dealer_code='" . $dealer_code . "'" : '';
                $mcond .= ($am_email != 'all' && $am_email != '') ? " AND d.am_email='" . $am_email . "'" : '';
                $mcond .= ($rm_email != 'all' && $rm_email != '') ? " AND d.rm_email='" . $rm_email . "'" : '';
                $mcond .= ($zm_email != 'all' && $zm_email != '') ? " AND d.zm_email='" . $zm_email . "'" : '';
                $mcond .= ($zone != 'all' && $zone != '') ? " AND d.zone='" . $zone . "'" : '';
            }

            if ($type == 'pan') {

                $header = array('Zone', 'Total Dealer', 'T&C Accepted', 'T&C Not Accepted');

                //GET ALL ZM
                $getzmquery_str = "SELECT zone FROM " . DMS_DEALER . " d WHERE status=1 AND is_deleted='0'  GROUP BY zone ORDER BY zone ASC";
                $getzm_data = $conn->getQueryValue($getzmquery_str);

                if (count($getzm_data) > 0) {
                    foreach ($getzm_data as $val) {

                        $manager_cond = " AND d.zone='" . $val->zone . "'" . $user_cond;

                        $dealer_str = "SELECT COUNT(d.id) data_count FROM " . DMS_DEALER . " d WHERE d.status=1 AND d.is_deleted='0' " . $manager_cond . " LIMIT 1";
                        $dealer_arr = $conn->getQueryValue($dealer_str);
                        $dealer_count = (count($dealer_arr) > 0) ? $dealer_arr[0]->data_count : '0';

                        $acceptdealer_str = "SELECT COUNT(d.id) data_count FROM " . DMS_DEALER . " d WHERE d.status=1 AND d.is_deleted='0' AND is_enquirymodule=1 " . $manager_cond . " LIMIT 1";
                        $acceptdealer_arr = $conn->getQueryValue($acceptdealer_str);
                        $acceptdealer_count = (count($acceptdealer_arr) > 0) ? $acceptdealer_arr[0]->data_count : '0';

                        $nonaccept_count = $dealer_count - $acceptdealer_count;
                        $returnarr[] = array($val->zone, $dealer_count, $acceptdealer_count, $nonaccept_count);
                    }
                }
            } else if (($type == 'zone') || ($type == 'zm') || ($type == 'rm') || ($type == 'am')) {

                $header = array('Total Dealer', 'T&C Accepted', 'T&C Not Accepted');

                $manager_cond = $mcond . $user_cond;
                $dealer_str = "SELECT COUNT(d.id) data_count FROM " . DMS_DEALER . " d WHERE d.status=1 AND d.is_deleted='0' " . $manager_cond . " LIMIT 1";
                $dealer_arr = $conn->getQueryValue($dealer_str);
                $dealer_count = (count($dealer_arr) > 0) ? $dealer_arr[0]->data_count : '0';

                $acceptdealer_str = "SELECT COUNT(d.id) data_count FROM " . DMS_DEALER . " d WHERE d.status=1 AND d.is_deleted='0' AND is_enquirymodule=1 " . $manager_cond . " LIMIT 1";
                $acceptdealer_arr = $conn->getQueryValue($acceptdealer_str);
                $acceptdealer_count = (count($acceptdealer_arr) > 0) ? $acceptdealer_arr[0]->data_count : '0';

                $nonaccept_count = $dealer_count - $acceptdealer_count;
                $returnarr[] = array($dealer_count, $acceptdealer_count, $nonaccept_count);

            } else if ($type == 'dealer') {
                $header = array('Total Dealer', 'T&C Accepted', 'T&C Not Accepted');

                $manager_cond = $mcond . $user_cond;

                $dse_str = "SELECT COUNT(e.id) data_count FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 AND is_enquirymodule=1 " . $manager_cond . " LIMIT 1";
                $dse_arr = $conn->getQueryValue($dse_str);
                $dse_count = (count($dse_arr) > 0) ? $dse_arr[0]->data_count : '0';
                $acceptdealer_txt = ($dse_count > 0) ? 'Accepted' : '-';
                $nonaccept_txt = ($dse_count > 0) ? '-' : 'Not Accepted';

                $returnarr[] = array(1, $acceptdealer_txt, $nonaccept_txt);
            }

            $list_arr = $returnarr;
            $return_arr = array('list' => $list_arr, 'header' => $header);
            $conn->message(true, $conn->getMessage($lang, 'report', 'success1'), $return_arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'report', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'report', 'error4'));
    } catch (Exception $e) {
        $conn->message(false, $e->getMessage());
    }
}

function temp_suspension()
{
    global $conn;
    try {
        $lang = "en_us";
        $pageData = $conn->loadJSON($lang);
        //$_SESSION["userid"] = 1;

        if (isset($_SESSION["userid"])) {

            $zone = escape_str($_REQUEST["zone"]);
            $zm_email = escape_str($_REQUEST["zm_email"]);
            $rm_email = escape_str($_REQUEST["rm_email"]);
            $am_email = escape_str($_REQUEST["am_email"]);
            $dealer_code = escape_str($_REQUEST["dealer_code"]);
            $smpin = escape_str($_REQUEST["smpin"]);
            $segment = escape_str($_REQUEST["segment"]);

            $start = escape_str($_REQUEST["start"]);
            $end = escape_str($_REQUEST["end"]);

            if (!($start != '' && $end != '')) {
                $conn->message(false, $conn->getMessage($lang, 'report', 'error7'));
            }

            $from_date = date('Y-m', strtotime($start . "-01")) . "-01";
            $to_date = date('Y-m-t', strtotime($end . "-01"));

            if (!(isValidDateTimeString($from_date, 'Y-m-d', 'Asia/Kolkata') && isValidDateTimeString($to_date, 'Y-m-d', 'Asia/Kolkata'))) {
                $conn->message(false, $conn->getMessage($lang, 'report', 'error7'));
            }

            $user_cond = '';
            if ($smpin != 'all' && $smpin != '') {
                //AM
                $type = "smpin";
            } else if ($dealer_code != 'all' && $dealer_code != '') {
                //AM
                $type = "dealer";
            } else if ($am_email != 'all' && $am_email != '') {
                //AM
                $type = "am";
            } else if ($rm_email != 'all' && $rm_email != '') {
                //RM
                $type = "rm";
            } else if ($zm_email != 'all' && $zm_email != '') {
                //ZM
                $type = "zm";
            } else if ($zone != 'all' && $zone != '') {
                //PAN
                $user_cond = '';
                $type = "zone";
            } else {
                //PAN
                $user_cond = '';
                $type = "pan";
            }

            $mcond = '';
            if (($smpin != 'all' && $smpin != '') || ($dealer_code != 'all' && $dealer_code != '') || ($am_email != 'all' && $am_email != '') || ($rm_email != 'all' && $rm_email != '') || ($zm_email != 'all' && $zm_email != '') || ($zone != 'all' && $zone != '')) {
                $mcond = '';
                $mcond .= ($smpin != 'all' && $smpin != '') ? " AND e.smpin='" . $smpin . "'" : '';
                $mcond .= ($dealer_code != 'all' && $dealer_code != '') ? " AND d.dealer_code='" . $dealer_code . "'" : '';
                $mcond .= ($am_email != 'all' && $am_email != '') ? " AND d.am_email='" . $am_email . "'" : '';
                $mcond .= ($rm_email != 'all' && $rm_email != '') ? " AND d.rm_email='" . $rm_email . "'" : '';
                $mcond .= ($zm_email != 'all' && $zm_email != '') ? " AND d.zm_email='" . $zm_email . "'" : '';
                $mcond .= ($zone != 'all' && $zone != '') ? " AND d.zone='" . $zone . "'" : '';
            }
            $regtype = (escape_str($_REQUEST["regtype"]) == '1' || !isset($_REQUEST["regtype"])) ? "1" : "0";

            if ($regtype == '1') {
                $user_cond .= ' AND e.is_current=1 ';
            }

            if ($segment != '' && $segment > 0) {
                $user_cond .= " AND e.segment='" . $segment . "' ";
            }

            if ($type == 'pan') {

                $header = array('Zone', 'Total DSE', 'Inactive DSE', 'DSEs Activated');

                //GET ALL ZM
                $getzmquery_str = "SELECT zone FROM " . DMS_DEALER . " d WHERE status=1 AND is_deleted='0'  GROUP BY zone ORDER BY zone ASC";
                $getzm_data = $conn->getQueryValue($getzmquery_str);

                if (count($getzm_data) > 0) {
                    foreach ($getzm_data as $val) {

                        $manager_cond = " AND d.zone='" . $val->zone . "'" . $user_cond;

                        $dse_str = "SELECT COUNT(e.id) data_count FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond . " LIMIT 1";
                        $dse_arr = $conn->getQueryValue($dse_str);
                        $dse_count = (count($dse_arr) > 0) ? $dse_arr[0]->data_count : '0';

                        $active_str = "SELECT COUNT(h.id) data_count FROM " . TABLE_USER_STATUS_HISTORY . " h JOIN " . TABLE_USER . " u ON h.userid=u.userid JOIN " . DMS_EMPLOYEE . " e ON e.smpin=u.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond . "  AND h.created_on>='" . $from_date . " 00:00:00' AND h.created_on<='" . $to_date . " 23:59:59' AND h.status='1' LIMIT 1";
                        $activedse_arr = $conn->getQueryValue($active_str);
                        $activedse_count = (count($activedse_arr) > 0) ? $activedse_arr[0]->data_count : '0';

                        $inactive_str = "SELECT COUNT(h.id) data_count FROM " . TABLE_USER_STATUS_HISTORY . " h JOIN " . TABLE_USER . " u ON h.userid=u.userid JOIN " . DMS_EMPLOYEE . " e ON e.smpin=u.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond . "  AND h.created_on>='" . $from_date . " 00:00:00' AND h.created_on<='" . $to_date . " 23:59:59' AND h.status='2' LIMIT 1";
                        $inactivedse_arr = $conn->getQueryValue($inactive_str);
                        $inactivedse_count = (count($inactivedse_arr) > 0) ? $inactivedse_arr[0]->data_count : '0';

                        $returnarr[] = array($val->zone, $dse_count, $activedse_count, $inactivedse_count);
                    }
                }
            } else if (($type == 'zone') || ($type == 'zm') || ($type == 'rm') || ($type == 'am') || $type == 'dealer') {

                $header = array('Total DSE', 'Inactive DSE', 'DSEs Activated');
                $manager_cond = $mcond . $user_cond;

                $dse_str = "SELECT COUNT(e.id) data_count FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond . " LIMIT 1";
                $dse_arr = $conn->getQueryValue($dse_str);
                $dse_count = (count($dse_arr) > 0) ? $dse_arr[0]->data_count : '0';

                $active_str = "SELECT COUNT(h.id) data_count FROM " . TABLE_USER_STATUS_HISTORY . " h JOIN " . TABLE_USER . " u ON h.userid=u.userid JOIN " . DMS_EMPLOYEE . " e ON e.smpin=u.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond . "  AND h.created_on>='" . $from_date . " 00:00:00' AND h.created_on<='" . $to_date . " 23:59:59' AND h.status='1' LIMIT 1";
                $activedse_arr = $conn->getQueryValue($active_str);
                $activedse_count = (count($activedse_arr) > 0) ? $activedse_arr[0]->data_count : '0';

                $inactive_str = "SELECT COUNT(h.id) data_count FROM " . TABLE_USER_STATUS_HISTORY . " h JOIN " . TABLE_USER . " u ON h.userid=u.userid JOIN " . DMS_EMPLOYEE . " e ON e.smpin=u.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond . "  AND h.created_on>='" . $from_date . " 00:00:00' AND h.created_on<='" . $to_date . " 23:59:59' AND h.status='2' LIMIT 1";
                $inactivedse_arr = $conn->getQueryValue($inactive_str);
                $inactivedse_count = (count($inactivedse_arr) > 0) ? $inactivedse_arr[0]->data_count : '0';

                $returnarr[] = array($dse_count, $activedse_count, $inactivedse_count);

            } else if ($type == 'smpin') {

                $header = array('Inactive DSE', 'DSEs Activated');

                $manager_cond = " AND e.smpin='" . $smpin . "'";
                $active_str = "SELECT COUNT(h.id) data_count FROM " . TABLE_USER_STATUS_HISTORY . " h JOIN " . TABLE_USER . " u ON h.userid=u.userid JOIN " . DMS_EMPLOYEE . " e ON e.smpin=u.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond . "  AND h.created_on>='" . $from_date . " 00:00:00' AND h.created_on<='" . $to_date . " 23:59:59' AND h.status='1' LIMIT 1";
                $activedse_arr = $conn->getQueryValue($active_str);
                $activedse_count = (count($activedse_arr) > 0) ? $activedse_arr[0]->data_count : '0';

                $inactive_str = "SELECT COUNT(h.id) data_count FROM " . TABLE_USER_STATUS_HISTORY . " h JOIN " . TABLE_USER . " u ON h.userid=u.userid JOIN " . DMS_EMPLOYEE . " e ON e.smpin=u.smpin JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE d.is_deleted=0 AND e.status=1 " . $manager_cond . "  AND h.created_on>='" . $from_date . " 00:00:00' AND h.created_on<='" . $to_date . " 23:59:59' AND h.status='2' LIMIT 1";
                $inactivedse_arr = $conn->getQueryValue($inactive_str);
                $inactivedse_count = (count($inactivedse_arr) > 0) ? $inactivedse_arr[0]->data_count : '0';

                $returnarr[] = array($activedse_count, $inactivedse_count);
            }

            $list_arr = $returnarr;
            $return_arr = array('list' => $list_arr, 'header' => $header);
            $conn->message(true, $conn->getMessage($lang, 'report', 'success1'), $return_arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'report', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'report', 'error4'));
    } catch (Exception $e) {
        $conn->message(false, $e->getMessage());
    }
}