<?php

/* * **************************
 * File : report_enquiry_download.php
 * Author : Cygnusa
 * @2020
 * ************************** */

// included in reportdownload.php

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

/* /////////////////////////////////// BLACK BACKGROUND  /////////////////////////////////// */
function setCellBlackBorder($columns, $objPHPExcel)
{

    for ($i = 0; $i < count($columns); $i++) {
        $objPHPExcel->getActiveSheet()->getStyle($columns[$i])->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle($columns[$i])->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle($columns[$i])->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle($columns[$i])->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle($columns[$i])->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB("000000");
        $objPHPExcel->getActiveSheet()->getStyle($columns[$i])->getFont()->setSize(12)->getColor()->setRGB("FFFFFF");
        $objPHPExcel->getActiveSheet()->getStyle($columns[$i])->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle($columns[$i])->getAlignment()->setWrapText(true);
    }
}

/* /////////////////////////////////// RATIO GCD  /////////////////////////////////// */
function gcd($a, $b)
{
    if ($b == '0') {
        return '0';
    }
    return ($a % $b) ? gcd($b, $a % $b) : $b;
}
/* ///////////////////////////////////////////////////////////////////////////////////// */

/* /////////////////////////////////// RATIO  /////////////////////////////////// */
function ratio($x, $y)
{
    $gcd = gcd($x, $y);
    if ($gcd == '0') {
        return ' : ';
    }
    return ($x / $gcd) . ' : ' . ($y / $gcd);
}
/* ///////////////////////////////////////////////////////////////////////////////////// */

//enquirystatusdseexport
function enquirystatusdseexport()
{
    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 {
                //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') {
                $mcond .= ' AND e.is_current=1 ';
            }
            if ($segment != '' && $segment > 0) {
                $mcond .= " AND e.segment='" . $segment . "' ";
            }

            $header = array('Zone', 'ZM', 'RM', 'AM', 'Dealer Name', 'Dealer Code', 'DSE name', 'SMPIN', 'Segment', 'Hot', 'Warm', 'Cold', 'Postponed', 'Lost');

            require_once dirname(__FILE__) . DIRECTORY_SEPARATOR . 'PHPExcel.php';
            require_once dirname(__FILE__) . DIRECTORY_SEPARATOR . 'PHPExcel/Writer/Excel2007.php';
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->setActiveSheetIndex(0);

            $row += 2;
            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $row, "ENQUIRY STATUS REPORT");
            $objPHPExcel->getActiveSheet()->mergeCells("A" . $row . ":F" . $row);
            $objPHPExcel->getActiveSheet()->getStyle("A" . $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("A" . $row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle("A" . $row)->getFont()->setSize(14);

            $row++;
            $bordercells_arr = array();
            $m = 0;
            foreach ($header as $head) {
                $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($m))->setWidth('30');
                $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($m) . $row, $head);
                $bordercells_arr[] = getColumnFromNumber($m++) . $row;
            }
            setCellBorder($bordercells_arr, $objPHPExcel);
            $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);

            $getquery_str = "SELECT e.smpin,e.fname,e.lname,e.mobile,e.dealer_code,e.segment,d.zone,d.dealer_name,d.`zm_name`,d. `rm_name`, d.`am_name`  FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code WHERE e.status=1 AND d.status=1 AND d.is_deleted='0' " . $mcond . " ORDER BY e.zone ASC,e.dealer_code ASC,e.smpin ASC";
            $get_data = $conn->getQueryValue($getquery_str);
            if (count($get_data) > 0) {
                foreach ($get_data as $val) {

                    $manager_cond = " AND enq.smpin='" . $val->smpin . "'";
                    $hot_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.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  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.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  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.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  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.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  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.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(enq.sales_count),0) as data_count FROM " . DMS_EMPLOYEE_SALES . " enq WHERE sale_date>='" . $from_date . "' AND  sale_date<='" . $to_date . "' " . $manager_cond;
                    //$sales_arr = $conn->getQueryValue($sales_str);
                    //$tot_sales = (count($sales_arr) > 0) ? $sales_arr[0]->data_count : '0';

                    $row++;
                    $i = 0;
                    //'Zone', 'ZM', 'RM', 'AM', 'Dealer Name', 'Dealer Code', 'DSE name', 'SMPIN', 'Segment', 'Hot', 'Warm', 'Cold', 'Postponed', 'Lost', 'Sales'
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->zone);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->zm_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->rm_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->am_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->dealer_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->dealer_code);
                    $objPHPExcel->getActiveSheet()->getStyle('F' . $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); //dealer_code
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, trim($val->fname . " " . $val->lname));
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->smpin);
                    $objPHPExcel->getActiveSheet()->getStyle('H' . $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); //dealer_code

                    switch ($val->segment) {
                        case '1':$segment_str = 'Parents/Guardian';
                            break;
                        case '2':$segment_str = 'Instructor';
                            break;
                        default:$segment_str = 'Student Learner';
                            break;
                    }

                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $segment_str);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $hot_count);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $warm_count);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $cold_count);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $postponed_count);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $lost_count);
                    // $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $tot_sales);

                }
            }

            $objPHPExcel->getActiveSheet()->setTitle('ENQUIRY STATUS REPORT');
            $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
            $fpath = "../" . S3_REPORT_URL;
            $filename = "enquiry_status_dse_report_" . uniqid() . ".xlsx";
            $objWriter->save($fpath . $filename);

            //S3 UPLOAD
            //S3 LIB
            require dirname(__FILE__) . '/awsfileserver.php';

            //S3 OBJ
            $s3 = awsfileserver::connectS3();
            awsfileserver::checkupload($s3, S3_BUCKET, S3_REPORT_URL . $filename, dirname(__FILE__) . "/../" . S3_REPORT_URL . $filename);

            //DELETE AFTER MOVE TO S3
            @unlink(dirname(__FILE__) . '/../' . S3_REPORT_URL . $filename);

            $obj = new stdClass();
            $obj->key_name = S3_REPORT_URL . $filename;
            $url = awsfileserver::getS3Url($s3, S3_BUCKET, $obj);

            $returnarr = array("report_url" => $url);
            $conn->message(true, $conn->getMessage($lang, 'report', 'success1'), $returnarr);

        } else {
            $conn->message(false, $conn->getMessage($lang, 'report', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'report', 'error4'));
    }
}
/* ///////////////////////////////////////////////////////////////////////////////////// */

//enquirytestridedseexport
function enquirytestridedseexport()
{
    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 {
                //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') {
                $mcond .= ' AND e.is_current=1 ';
            }
            if ($segment != '' && $segment > 0) {
                $mcond .= " AND e.segment='" . $segment . "' ";
            }

            $header = array('Zone', 'ZM', 'RM', 'AM', 'Dealer Name', 'Dealer Code', 'DSE name', 'SMPIN', 'Segment', 'Enquiry Created HYPER-M', 'Enquiry Created from Backend', 'Test Ride From HYPER-M APP', 'Brochure Shared', 'Quotation Shared');

            require_once dirname(__FILE__) . DIRECTORY_SEPARATOR . 'PHPExcel.php';
            require_once dirname(__FILE__) . DIRECTORY_SEPARATOR . 'PHPExcel/Writer/Excel2007.php';
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->setActiveSheetIndex(0);

            $row += 2;
            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $row, "ENQUIRY TESTRIDE REPORT");
            $objPHPExcel->getActiveSheet()->mergeCells("A" . $row . ":F" . $row);
            $objPHPExcel->getActiveSheet()->getStyle("A" . $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("A" . $row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle("A" . $row)->getFont()->setSize(14);

            $row++;
            $bordercells_arr = array();
            $m = 0;
            foreach ($header as $head) {
                $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($m))->setWidth('30');
                $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($m) . $row, $head);
                $bordercells_arr[] = getColumnFromNumber($m++) . $row;
            }
            setCellBorder($bordercells_arr, $objPHPExcel);
            $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);

            $getquery_str = "SELECT e.smpin,e.fname,e.lname,e.mobile,e.dealer_code,e.segment,d.zone,d.dealer_name,d.`zm_name`,d. `rm_name`, d.`am_name`  FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code WHERE e.status=1 AND d.status=1 AND d.is_deleted='0' " . $mcond . " ORDER BY e.zone ASC,e.dealer_code ASC,e.smpin ASC";
            $get_data = $conn->getQueryValue($getquery_str);
            if (count($get_data) > 0) {
                foreach ($get_data as $val) {

                    $manager_cond = " AND enq.smpin='" . $val->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' 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  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';

                    $row++;
                    $i = 0;
                    //'Zone', 'ZM', 'RM', 'AM', 'Dealer Name', 'Dealer Code', 'DSE name', 'SMPIN', 'Segment', 'Hot', 'Warm', 'Cold', 'Postponed', 'Lost', 'Sales'
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->zone);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->zm_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->rm_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->am_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->dealer_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->dealer_code);
                    $objPHPExcel->getActiveSheet()->getStyle('F' . $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); //dealer_code
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, trim($val->fname . " " . $val->lname));
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->smpin);
                    $objPHPExcel->getActiveSheet()->getStyle('H' . $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); //dealer_code

                    switch ($val->segment) {
                        case '1':$segment_str = 'Parents/Guardian';
                            break;
                        case '2':$segment_str = 'Instructor';
                            break;
                        default:$segment_str = 'Student Learner';
                            break;
                    }

                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $segment_str);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $tot_count);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $backtot_count);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $tottest_count);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $brochure_count);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $quotation_count);

                }
            }

            $objPHPExcel->getActiveSheet()->setTitle('ENQUIRY TESTRIDE REPORT');
            $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
            $fpath = "../" . S3_REPORT_URL;
            $filename = "enquiry_testride_dse_report_" . uniqid() . ".xlsx";
            $objWriter->save($fpath . $filename);

            //S3 UPLOAD
            //S3 LIB
            require dirname(__FILE__) . '/awsfileserver.php';

            //S3 OBJ
            $s3 = awsfileserver::connectS3();
            awsfileserver::checkupload($s3, S3_BUCKET, S3_REPORT_URL . $filename, dirname(__FILE__) . "/../" . S3_REPORT_URL . $filename);

            //DELETE AFTER MOVE TO S3
            @unlink(dirname(__FILE__) . '/../' . S3_REPORT_URL . $filename);

            $obj = new stdClass();
            $obj->key_name = S3_REPORT_URL . $filename;
            $url = awsfileserver::getS3Url($s3, S3_BUCKET, $obj);

            $returnarr = array("report_url" => $url);
            $conn->message(true, $conn->getMessage($lang, 'report', 'success1'), $returnarr);

        } else {
            $conn->message(false, $conn->getMessage($lang, 'report', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'report', 'error4'));
    }
}
/* ///////////////////////////////////////////////////////////////////////////////////// */

//enquiryconversionratiodseexport
function enquiryconversionratiodseexport()
{
    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 {
                //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') {
                $mcond .= ' AND e.is_current=1 ';
            }
            if ($segment != '' && $segment > 0) {
                $mcond .= " AND e.segment='" . $segment . "' ";
            }
//
            $header = array('Zone', 'ZM', 'RM', 'AM', 'Dealer Name', 'Dealer Code', 'DSE name', 'SMPIN', 'Segment', 'Enquiry Target', 'Enquiry Achieved', 'Sales Target', 'Sales Achieved', 'Sales Conversion Ratio', 'Conversion %');

            require_once dirname(__FILE__) . DIRECTORY_SEPARATOR . 'PHPExcel.php';
            require_once dirname(__FILE__) . DIRECTORY_SEPARATOR . 'PHPExcel/Writer/Excel2007.php';
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->setActiveSheetIndex(0);

            $row += 2;
            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $row, "ENQUIRY CONVERSION RATIO REPORT");
            $objPHPExcel->getActiveSheet()->mergeCells("A" . $row . ":F" . $row);
            $objPHPExcel->getActiveSheet()->getStyle("A" . $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("A" . $row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle("A" . $row)->getFont()->setSize(14);

            $row++;
            $bordercells_arr = array();
            $m = 0;
            foreach ($header as $head) {
                $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($m))->setWidth('30');
                $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($m) . $row, $head);
                $bordercells_arr[] = getColumnFromNumber($m++) . $row;
            }
            setCellBorder($bordercells_arr, $objPHPExcel);
            $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);

            $getquery_str = "SELECT e.smpin,e.fname,e.lname,e.mobile,e.dealer_code,e.segment,d.zone,d.dealer_name,d.`zm_name`,d. `rm_name`, d.`am_name`  FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code WHERE e.status=1 AND d.status=1 AND d.is_deleted='0' " . $mcond . " ORDER BY e.zone ASC,e.dealer_code ASC,e.smpin ASC";
            $get_data = $conn->getQueryValue($getquery_str);
            if (count($get_data) > 0) {
                foreach ($get_data as $val) {

                    $manager_cond = " AND enq.smpin='" . $val->smpin . "'";

                    $row++;
                    $i = 0;

                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->zone);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->zm_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->rm_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->am_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->dealer_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->dealer_code);
                    $objPHPExcel->getActiveSheet()->getStyle('F' . $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); //dealer_code
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, trim($val->fname . " " . $val->lname));
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->smpin);
                    $objPHPExcel->getActiveSheet()->getStyle('H' . $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); //dealer_code

                    switch ($val->segment) {
                        case '1':$segment_str = 'Parents/Guardian';
                            break;
                        case '2':$segment_str = 'Instructor';
                            break;
                        default:$segment_str = 'Student Learner';
                            break;
                    }

                    //sales target
                    $salestarget_str = "SELECT COALESCE(SUM(t.target),0) as data_count FROM " . TABLE_USER_SALETARGET . " t  WHERE t.sale_month>='" . $from_date . "' AND t.sale_month<='" . $to_date . "' AND t.smpin='" . $val->smpin . "'";
                    $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 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;
                    $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 WHERE s.sale_date>='" . $from_date . "' AND s.sale_date<='" . $to_date . "' AND s.smpin='" . $val->smpin . "'";
                    $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);

                    //'Zone', 'ZM', 'RM', 'AM', 'Dealer Name', 'Dealer Code', 'DSE name', 'SMPIN', 'Segment', 'Enquiry Target', 'Enquiry Achieved', 'Enquiry Achieved %', 'Sales Target', 'Sales Achieved', 'Sales Achieved %', 'Sales Conversion Ratio', 'Conversion %'
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $segment_str);

                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $enq_target_count);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $totenq_count);
                    //$objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $enq_per);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $salestarget_count);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $tot_sales);
                    //$objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $sales_per);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $sale_ratio);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $conversion_per);

                }
            }

            $objPHPExcel->getActiveSheet()->setTitle('ENQUIRY CONVERSION REPORT');
            $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
            $fpath = "../" . S3_REPORT_URL;
            $filename = "enquiry_conversionratio_dse_report_" . uniqid() . ".xlsx";
            $objWriter->save($fpath . $filename);

            //S3 UPLOAD
            //S3 LIB
            require dirname(__FILE__) . '/awsfileserver.php';

            //S3 OBJ
            $s3 = awsfileserver::connectS3();
            awsfileserver::checkupload($s3, S3_BUCKET, S3_REPORT_URL . $filename, dirname(__FILE__) . "/../" . S3_REPORT_URL . $filename);

            //DELETE AFTER MOVE TO S3
            @unlink(dirname(__FILE__) . '/../' . S3_REPORT_URL . $filename);

            $obj = new stdClass();
            $obj->key_name = S3_REPORT_URL . $filename;
            $url = awsfileserver::getS3Url($s3, S3_BUCKET, $obj);

            $returnarr = array("report_url" => $url);
            $conn->message(true, $conn->getMessage($lang, 'report', 'success1'), $returnarr);

        } else {
            $conn->message(false, $conn->getMessage($lang, 'report', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'report', 'error4'));
    }
}
/* ///////////////////////////////////////////////////////////////////////////////////// */

//productivitydseexport
function productivitydseexport()
{
    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 {
                //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') {
                $mcond .= ' AND e.is_current=1 ';
            }
            if ($segment != '' && $segment > 0) {
                $mcond .= " AND e.segment='" . $segment . "' ";
            }
//
            $header = array('Zone', 'ZM', 'RM', 'AM', 'Dealer Name', 'Dealer Code', 'DSE name', 'SMPIN', 'Segment', 'Total Sales');

            require_once dirname(__FILE__) . DIRECTORY_SEPARATOR . 'PHPExcel.php';
            require_once dirname(__FILE__) . DIRECTORY_SEPARATOR . 'PHPExcel/Writer/Excel2007.php';
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->setActiveSheetIndex(0);

            $row += 2;
            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $row, "ENQUIRY PRODUCTIVITY REPORT");
            $objPHPExcel->getActiveSheet()->mergeCells("A" . $row . ":F" . $row);
            $objPHPExcel->getActiveSheet()->getStyle("A" . $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("A" . $row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle("A" . $row)->getFont()->setSize(14);

            $row++;
            $bordercells_arr = array();
            $m = 0;
            foreach ($header as $head) {
                $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($m))->setWidth('30');
                $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($m) . $row, $head);
                $bordercells_arr[] = getColumnFromNumber($m++) . $row;
            }
            setCellBorder($bordercells_arr, $objPHPExcel);
            $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);

            $getquery_str = "SELECT e.smpin,e.fname,e.lname,e.mobile,e.dealer_code,e.segment,d.zone,d.dealer_name,d.`zm_name`,d. `rm_name`, d.`am_name`  FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code WHERE e.status=1 AND d.status=1 AND d.is_deleted='0' " . $mcond . " ORDER BY e.zone ASC,e.dealer_code ASC,e.smpin ASC";
            $get_data = $conn->getQueryValue($getquery_str);
            if (count($get_data) > 0) {
                foreach ($get_data as $val) {

                    $manager_cond = " AND enq.smpin='" . $val->smpin . "'";

                    $row++;
                    $i = 0;
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->zone);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->zm_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->rm_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->am_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->dealer_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->dealer_code);
                    $objPHPExcel->getActiveSheet()->getStyle('F' . $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); //dealer_code
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, trim($val->fname . " " . $val->lname));
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->smpin);
                    $objPHPExcel->getActiveSheet()->getStyle('H' . $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); //dealer_code

                    switch ($val->segment) {
                        case '1':$segment_str = 'Parents/Guardian';
                            break;
                        case '2':$segment_str = 'Instructor';
                            break;
                        default:$segment_str = 'Student Learner';
                            break;
                    }

                    //sales acheived
                    $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='" . $val->smpin . "'";
                    $sales_arr = $conn->getQueryValue($sales_str);
                    $tot_sales = (count($sales_arr) > 0) ? $sales_arr[0]->data_count : '0';

                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $segment_str);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $tot_sales);

                }
            }

            $objPHPExcel->getActiveSheet()->setTitle('ENQUIRY PRODUCTIVITY REPORT');
            $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
            $fpath = "../" . S3_REPORT_URL;
            $filename = "enquiry_productivity_dse_report_" . uniqid() . ".xlsx";
            $objWriter->save($fpath . $filename);

            //S3 UPLOAD
            //S3 LIB
            require dirname(__FILE__) . '/awsfileserver.php';

            //S3 OBJ
            $s3 = awsfileserver::connectS3();
            awsfileserver::checkupload($s3, S3_BUCKET, S3_REPORT_URL . $filename, dirname(__FILE__) . "/../" . S3_REPORT_URL . $filename);

            //DELETE AFTER MOVE TO S3
            @unlink(dirname(__FILE__) . '/../' . S3_REPORT_URL . $filename);

            $obj = new stdClass();
            $obj->key_name = S3_REPORT_URL . $filename;
            $url = awsfileserver::getS3Url($s3, S3_BUCKET, $obj);

            $returnarr = array("report_url" => $url);
            $conn->message(true, $conn->getMessage($lang, 'report', 'success1'), $returnarr);

        } else {
            $conn->message(false, $conn->getMessage($lang, 'report', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'report', 'error4'));
    }
}
/* ///////////////////////////////////////////////////////////////////////////////////// */

//enquiry_dealer_report_terms_condition
function enquiry_dealer_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 {
                //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 . "'" : '';

            }

            $returnarr = array();
            $tablejoin = '';

//
            $header = array('Zone', 'ZM', 'RM', 'AM', 'Dealer Name', 'Dealer Code', 'T&C Acceptance', 'T&C Acceptance Date
            ');

            require_once dirname(__FILE__) . DIRECTORY_SEPARATOR . 'PHPExcel.php';
            require_once dirname(__FILE__) . DIRECTORY_SEPARATOR . 'PHPExcel/Writer/Excel2007.php';
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->setActiveSheetIndex(0);

            $row += 2;
            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $row, "DEALER T&C ACCEPTANCE REPORT");
            $objPHPExcel->getActiveSheet()->mergeCells("A" . $row . ":F" . $row);
            $objPHPExcel->getActiveSheet()->getStyle("A" . $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("A" . $row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle("A" . $row)->getFont()->setSize(14);

            $row++;
            $bordercells_arr = array();
            $m = 0;
            foreach ($header as $head) {
                $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($m))->setWidth('30');
                $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($m) . $row, $head);
                $bordercells_arr[] = getColumnFromNumber($m++) . $row;
            }
            setCellBorder($bordercells_arr, $objPHPExcel);
            $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);

            $getquery_str = "SELECT d.dealer_code,d.zone,d.dealer_name,d.`zm_name`,d. `rm_name`, d.`am_name`,d.is_enquirymodule  FROM  " . DMS_DEALER . " d WHERE  d.status=1 AND d.is_deleted='0' " . $mcond . " ORDER BY d.dealer_code ASC ";
            $get_data = $conn->getQueryValue($getquery_str);
            if (count($get_data) > 0) {
                foreach ($get_data as $val) {

                    $manager_cond = " AND enq.smpin='" . $val->smpin . "'";

                    $row++;
                    $i = 0;
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->zone);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->zm_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->rm_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->am_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->dealer_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->dealer_code);
                    $objPHPExcel->getActiveSheet()->getStyle('F' . $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); //dealer_code

                    $terms_str = ($val->is_enquirymodule == '1') ? 'Yes' : 'No';
                    $terms_date = '-';
                    if ($val->is_enquirymodule == '1') {
                        $datequery_str = "SELECT l.logid,l.created_on  FROM  " . TABLE_USER_LOG . " l JOIN (SELECT userid FROM " . TABLE_USER . " WHERE role=14 AND dealer_code='" . $val->dealer_code . "') u ON u.userid=l.userid WHERE l.module='ACCEPT LEAD MODULE' ORDER BY l.logid desc LIMIT 1";
                        $date_data = $conn->getQueryValue($datequery_str);
                        $terms_date = (count($date_data) > 0) ? date('d-M-Y', strtotime($date_data[0]->created_on)) : '-';
                    }
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $terms_str);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $terms_date);
                }
            }

            $objPHPExcel->getActiveSheet()->setTitle('DEALER T&C ACCEPTANCE');
            $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
            $fpath = "../" . S3_REPORT_URL;
            $filename = "dealer_tc_report_" . uniqid() . ".xlsx";
            $objWriter->save($fpath . $filename);

            //S3 UPLOAD
            //S3 LIB
            require dirname(__FILE__) . '/awsfileserver.php';

            //S3 OBJ
            $s3 = awsfileserver::connectS3();
            awsfileserver::checkupload($s3, S3_BUCKET, S3_REPORT_URL . $filename, dirname(__FILE__) . "/../" . S3_REPORT_URL . $filename);

            //DELETE AFTER MOVE TO S3
            @unlink(dirname(__FILE__) . '/../' . S3_REPORT_URL . $filename);

            $obj = new stdClass();
            $obj->key_name = S3_REPORT_URL . $filename;
            $url = awsfileserver::getS3Url($s3, S3_BUCKET, $obj);

            $returnarr = array("report_url" => $url);
            $conn->message(true, $conn->getMessage($lang, 'report', 'success1'), $returnarr);

        } else {
            $conn->message(false, $conn->getMessage($lang, 'report', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'report', 'error4'));
    }
}
/* ///////////////////////////////////////////////////////////////////////////////////// */

//temp_suspension
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 {
                //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') {
                $mcond .= ' AND e.is_current=1 ';
            }
            if ($segment != '' && $segment > 0) {
                $mcond .= " AND e.segment='" . $segment . "' ";
            }
//
            $header = array('Zone', 'ZM', 'RM', 'AM', 'Dealer Name', 'Dealer Code', 'DSE name', 'SMPIN', 'Segment', 'Date', 'Action', 'Remarks');

            require_once dirname(__FILE__) . DIRECTORY_SEPARATOR . 'PHPExcel.php';
            require_once dirname(__FILE__) . DIRECTORY_SEPARATOR . 'PHPExcel/Writer/Excel2007.php';
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->setActiveSheetIndex(0);

            $row += 2;
            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $row, "TEMPORARY SUSPENSION REPORT");
            $objPHPExcel->getActiveSheet()->mergeCells("A" . $row . ":F" . $row);
            $objPHPExcel->getActiveSheet()->getStyle("A" . $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("A" . $row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle("A" . $row)->getFont()->setSize(14);

            $row++;
            $bordercells_arr = array();
            $m = 0;
            foreach ($header as $head) {
                $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($m))->setWidth('30');
                $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($m) . $row, $head);
                $bordercells_arr[] = getColumnFromNumber($m++) . $row;
            }
            setCellBorder($bordercells_arr, $objPHPExcel);
            $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);

            $reason_arr = array();
            $reasonlist_str = "SELECT id,name FROM " . TABLE_DSE_ACTIVE_DEACTIVE_LIST;
            $reason_data = $conn->getQueryValue($reasonlist_str);
            if (count($reason_data) > 0) {
                foreach ($reason_data as $res) {
                    $reason_arr[$res->id] = $res->name;
                }
            }

            $getquery_str = "SELECT e.smpin,e.fname,e.lname,e.mobile,e.dealer_code,e.segment,d.zone,d.dealer_name,d.`zm_name`,d. `rm_name`, d.`am_name`, h.created_on,h.remarks,h.remarkoption,h.status  FROM " . TABLE_USER_STATUS_HISTORY . " h JOIN " . TABLE_USER . " u ON h.userid=u.userid JOIN  " . DMS_EMPLOYEE . " e  ON u.smpin=e.smpin JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code WHERE e.status=1 AND d.status=1 AND d.is_deleted='0' AND h.created_on>='" . $from_date . " 00:00:00' AND h.created_on<='" . $to_date . " 23:59:59' " . $mcond . "  ORDER BY e.zone ASC,e.dealer_code ASC,e.smpin ASC";
            $get_data = $conn->getQueryValue($getquery_str);
            if (count($get_data) > 0) {
                foreach ($get_data as $val) {

                    $manager_cond = " AND enq.smpin='" . $val->smpin . "'";

                    $row++;
                    $i = 0;
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->zone);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->zm_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->rm_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->am_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->dealer_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->dealer_code);
                    $objPHPExcel->getActiveSheet()->getStyle('F' . $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); //dealer_code
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, trim($val->fname . " " . $val->lname));
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $val->smpin);
                    $objPHPExcel->getActiveSheet()->getStyle('H' . $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); //dealer_code

                    switch ($val->segment) {
                        case '1':$segment_str = 'Parents/Guardian';
                            break;
                        case '2':$segment_str = 'Instructor';
                            break;
                        default:$segment_str = 'Student Learner';
                            break;
                    }

                    //sales acheived
                    $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='" . $val->smpin . "'";
                    $sales_arr = $conn->getQueryValue($sales_str);
                    $tot_sales = (count($sales_arr) > 0) ? $sales_arr[0]->data_count : '0';

                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $segment_str);
                    $date_str = date('d-m-Y', strtotime($val->created_on));
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $date_str);
                    switch ($val->status) {
                        case '1':
                            $status_str = 'Active';
                            break;
                        case '2':
                            $status_str = 'Inactive';
                            break;
                        default:
                            $status_str = '-';
                            break;
                    }

                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $status_str);

                    $remark_str = ($val->remarkoption > 0) ? $reason_arr[$val->remarkoption] : '-';
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++) . $row, $remark_str);

                }
            }

            $objPHPExcel->getActiveSheet()->setTitle('SUSPENSION REPORT');
            $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
            $fpath = "../" . S3_REPORT_URL;
            $filename = "temporary_suspension_report_" . uniqid() . ".xlsx";
            $objWriter->save($fpath . $filename);

            //S3 UPLOAD
            //S3 LIB
            require dirname(__FILE__) . '/awsfileserver.php';

            //S3 OBJ
            $s3 = awsfileserver::connectS3();
            awsfileserver::checkupload($s3, S3_BUCKET, S3_REPORT_URL . $filename, dirname(__FILE__) . "/../" . S3_REPORT_URL . $filename);

            //DELETE AFTER MOVE TO S3
            @unlink(dirname(__FILE__) . '/../' . S3_REPORT_URL . $filename);

            $obj = new stdClass();
            $obj->key_name = S3_REPORT_URL . $filename;
            $url = awsfileserver::getS3Url($s3, S3_BUCKET, $obj);

            $returnarr = array("report_url" => $url);
            $conn->message(true, $conn->getMessage($lang, 'report', 'success1'), $returnarr);

        } else {
            $conn->message(false, $conn->getMessage($lang, 'report', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'report', 'error4'));
    }
}
/* ///////////////////////////////////////////////////////////////////////////////////// */