<?php
/****************************
 * File : trainingreportexport.php
 * Author : Cygnusa
 * @2018
 ****************************/

//SECURITY
if (!defined('APPLICATION_PATH')) {
    exit('No direct script access allowed');
}

//FUNCTION LISTS

class trainingreportexport
{
    public $user_lvl = 0;
    public function __construct($parent)
    {
        //GET PAGE MESSAGE
        $this->page_data = globalfunc::getMessage($parent, $parent->lang, 'report');
        if (count($parent->params) == 0) {
            $parent->response(array('response' => 'failed', 'message' => 'PAGE NOT FOUND'), 404);
        } else {
            $qry_params = explode("?", $parent->params[0]);
            $parameter_val = strtolower($qry_params[0]);

            switch ($parameter_val) {
                case "classroom":
                case "feedback":
                case "productivity":
                case "nomination":
                case "others":
                case "decline":
                case "absent":
                case "closure":
                case "manual_attendance":
                case "various":
                    $this->$parameter_val($parent);
                    break;
                default:
                    break;
            }
        }
        $parent->response(array('response' => 'failed', 'message' => 'PAGE NOT FOUND'), 404);
    }

    /* /////////////////////////////// MANAGER WISE FILTER //////////////////////////////////////////////// */
    private function getmanagerconditon_arr($parent)
    {
        $this->user_lvl = 0;
        projectfunc::authorizedRole($parent, array('3', '4', '5', '6', '7', '14'));
        $zmemail = escape_str($parent->_request["zmemail"]);
        $rmemail = escape_str($parent->_request["rmemail"]);
        $amemail = escape_str($parent->_request["amemail"]);
        $dealer_code = escape_str($parent->_request["dealer_code"]);

        $dealer_cond = array();
        if ($parent->current_user_role == '3') {
            $this->user_lvl = 'ho';
        } else if ($parent->current_user_role == '4') {
            $this->user_lvl = 'zm';
            $dealer_cond[] = "zm_email='" . $parent->current_user_data->emailid . "'";
        } else if ($parent->current_user_role == '5') {

            $get_trainer_str = "SELECT h.zone FROM " . DMS_HOUSER . " h JOIN " . TABLE_USER . " u ON u.emp_code=h.emp_code WHERE u.userid='" . $parent->current_user_id . "' AND u.role='5' LIMIT 1";
            $trainer_arr = $parent->getQueryValue($get_trainer_str);
            $dealer_cond[] = (count($trainer_arr) > 0) ? "zone='" . $trainer_arr[0]->zone . "'" : "zone='0'";
            $this->user_lvl = 'trainer';
        } else if ($parent->current_user_role == '6') {
            $this->user_lvl = 'rm';
            $dealer_cond[] = "rm_email='" . $parent->current_user_data->emailid . "'";
        } else if ($parent->current_user_role == '7') {
            $this->user_lvl = 'am';
            $dealer_cond[] = "am_email='" . $parent->current_user_data->emailid . "'";
        } else if ($parent->current_user_role == '14') {
            $this->user_lvl = 'dealer';
            $dealer_cond[] = "dealer_code='" . $parent->current_user_data->dealer_code . "'";
        }

        if ($zmemail != 'all' && $zmemail != '') {
            $this->user_lvl = 'zm';
            $dealer_cond[] = "zm_email='" . $zmemail . "'";
        }
        if ($rmemail != 'all' && $rmemail != '') {
            $this->user_lvl = 'rm';
            $dealer_cond[] = "rm_email='" . $rmemail . "'";
        }
        if ($amemail != 'all' && $amemail != '') {
            $this->user_lvl = 'am';
            $dealer_cond[] = "am_email='" . $amemail . "'";
        }
        if ($dealer_code != 'all' && $dealer_code != '') {
            $this->user_lvl = 'dealer';
            $dealer_cond[] = "dealer_code='" . $dealer_code . "'";
        }
        return $dealer_cond;
    }

    /* /////////////////////////////// CLASSROOM //////////////////////////////////////////////// */
    private function classroom($parent)
    {
        globalfunc::checkMethodType($parent, array('POST'));
        globalfunc::checkParamCount($parent, 1);
        globalfunc::checkAuthrozation($parent, array('type' => 'user'));

        $limit_str = globalfunc::setPageLimit($parent, $parent->params[0]);
        $limit_cond = ($limit_str) ? " LIMIT " . $limit_str : '';
        $dealer_code_cond = '';

        //MANAGER WISE DEALER CONDITION
        $dealer_cond_arr = $this->getmanagerconditon_arr($parent);
        if (count($dealer_cond_arr) > 0) {
            //APPEND prefix
            array_walk($dealer_cond_arr, function (&$value, $key) {$value = 'd.' . $value;});
            $user_cond .= " AND " . @implode(" AND ", $dealer_cond_arr);
        }

        $start_date = escape_str($parent->_request['from']);
        $end_date = escape_str($parent->_request['to']);

        $REQUEST_KEYS = array('from' => 'date|Y-m-d', 'to' => 'date|Y-m-d');
        globalfunc::mandatoryKey($parent, $REQUEST_KEYS);

        $date_cond = " AND l.created_on>='" . $start_date . " 00:00:00' AND l.created_on<='" . $end_date . " 23:59:59' ";
        $tdate_cond = " AND (( DATE(s.start_date) BETWEEN '" . $start_date . "' AND '" . $end_date . "') OR ( DATE(s.end_date) BETWEEN '" . $start_date . "' AND '" . $end_date . "') OR ( '" . $start_date . "'  BETWEEN DATE(s.start_date) AND DATE(s.end_date)) OR ('" . $end_date . "' BETWEEN DATE(s.start_date) AND DATE(s.end_date)))";

        $data_arr = array();
        $ret_data = array();
        switch ($this->user_lvl) {
            case 'ho':
                //ZM
                $query_str = "SELECT zm_name,zm_email,count(d.id) as dealer_count FROM " . DMS_DEALER . " d WHERE status=1 AND is_deleted='0' " . $user_cond . " GROUP BY zm_email";
                $get_data = $parent->getQueryValue($query_str);

                $data_arr['header'] = array("ZM", "Total Dealer", "Total DSE", "Base Trained", "Base Certified", "Bronze Trained", "Bronze certified", "Silver L1 Trained", "Silver L1 Certified", "Silver L2 Trained", "Silver L2 Certified", "Silver Certified", "Gold L1 Trained", "Gold L1 Certified", "Gold L2 Trained", "Gold L2 Certified", "Gold Certified");

                if (count($get_data) > 0) {
                    $dealer_count = 0;
                    foreach ($get_data as $val) {

                        $dsequery_str = "SELECT  count(DISTINCT e.id) as dse_count FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND d.zm_email='" . $val->zm_email . "'";
                        $dse_data = $parent->getQueryValue($dsequery_str);
                        $dse_count = count($dse_data) > 0 ? $dse_data[0]->dse_count : 0;

                        $base_trained = 0;
                        $base_certified = 0;
                        $bronze_trained = 0;
                        $bronze_certified = 0;
                        $silver_l1_trained = 0;
                        $silver_l1_certified = 0;
                        $silver_l2_trained = 0;
                        $silver_l2_certified = 0;
                        $silver_certified = 0;
                        $gold_l1_trained = 0;
                        $gold_l1_certified = 0;
                        $gold_l2_trained = 0;
                        $gold_l2_certified = 0;
                        $gold_certified = 0;

                        $certificate_query_str = "SELECT  count(DISTINCT e.id) as dse_count,lvl_id FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code JOIN " . TABLE_USER_LEARNING . " l ON e.smpin=l.smpin WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND d.zm_email='" . $val->zm_email . "' " . $date_cond . " AND l.status=3 GROUP BY l.lvl_id";
                        $dse_data = $parent->getQueryValue($certificate_query_str);

                        if (count($dse_data) > 0) {
                            foreach ($dse_data as $lvl) {
                                switch ($lvl->lvl_id) {
                                    case '1':$base_certified = $lvl->dse_count;
                                        break;
                                    case '2':$bronze_certified = $lvl->dse_count;
                                        break;
                                    case '3':$silver_l1_certified = $lvl->dse_count;
                                        break;
                                    case '4':$silver_l2_certified = $lvl->dse_count;
                                        $silver_certified = $silver_l2_certified;
                                        break;
                                    case '5':$gold_l1_certified = $lvl->dse_count;
                                        break;
                                    case '6':$gold_l2_certified = $lvl->dse_count;
                                        $gold_certified = $gold_l2_certified;
                                        break;
                                }
                            }
                        }

                        $trained_query_str = "SELECT  count(DISTINCT e.id) as dse_count,s.lvl_id  FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code JOIN " . TABLE_USER . " u ON u.smpin=e.smpin JOIN " . TABLE_TRAINING_PARTICIPANT . " p ON p.userid=u.userid JOIN " . TABLE_TRAINING_SCHEDULE . " s ON s.schedule_id=p.schedule_id WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND p.certification=1 AND s.is_deleted=0 AND d.zm_email='" . $val->zm_email . "' " . $tdate_cond . " GROUP BY s.lvl_id";
                        $trained_dse_data = $parent->getQueryValue($trained_query_str);

                        if (count($trained_dse_data) > 0) {
                            foreach ($trained_dse_data as $lvl) {
                                switch ($lvl->lvl_id) {
                                    case '1':$base_trained = $lvl->dse_count;
                                        break;
                                    case '2':$bronze_trained = $lvl->dse_count;
                                        break;
                                    case '3':$silver_l1_trained = $lvl->dse_count;
                                        break;
                                    case '4':$silver_l2_trained = $lvl->dse_count;
                                        break;
                                    case '5':$gold_l1_trained = $lvl->dse_count;
                                        break;
                                    case '6':$gold_l2_trained = $lvl->dse_count;
                                        break;
                                }
                            }
                        }

                        $ret_data[] = array($val->zm_name, $val->dealer_count, $dse_count, $base_trained, $base_certified, $bronze_trained, $bronze_certified, $silver_l1_trained, $silver_l1_certified, $silver_l2_trained, $silver_l2_certified, $silver_certified, $gold_l1_trained, $gold_l1_certified, $gold_l2_trained, $gold_l2_certified, $gold_certified);

                        $tot_dealer_count += $val->dealer_count;
                        $tot_dse_count += $dse_count;
                    }
                }
                $data_arr['value'] = $ret_data;
                //TOTAL
                $tot_base_trained = 0;
                $tot_base_certified = 0;
                $tot_bronze_trained = 0;
                $tot_bronze_certified = 0;
                $tot_silver_l1_trained = 0;
                $tot_silver_l1_certified = 0;
                $tot_silver_l2_trained = 0;
                $tot_silver_l2_certified = 0;
                $tot_silver_certified = 0;
                $tot_gold_l1_trained = 0;
                $tot_gold_l1_certified = 0;
                $tot_gold_l2_trained = 0;
                $tot_gold_l2_certified = 0;
                $tot_gold_certified = 0;

                $certificate_query_str = "SELECT  count(DISTINCT e.id) as dse_count,lvl_id FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code JOIN " . TABLE_USER_LEARNING . " l ON e.smpin=l.smpin WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND l.status=3 " . $date_cond . " GROUP BY l.lvl_id";
                $dse_data = $parent->getQueryValue($certificate_query_str);

                if (count($dse_data) > 0) {
                    foreach ($dse_data as $lvl) {
                        switch ($lvl->lvl_id) {
                            case '1':$tot_base_certified = $lvl->dse_count;
                                break;
                            case '2':$tot_bronze_certified = $lvl->dse_count;
                                break;
                            case '3':$tot_silver_l1_certified = $lvl->dse_count;
                                break;
                            case '4':$tot_silver_l2_certified = $lvl->dse_count;
                                $tot_silver_certified = $tot_silver_l2_certified;
                                break;
                            case '5':$tot_gold_l1_certified = $lvl->dse_count;
                                break;
                            case '6':$tot_gold_l2_certified = $lvl->dse_count;
                                $tot_gold_certified = $tot_gold_l2_certified;
                                break;
                        }
                    }
                }

                $trained_query_str = "SELECT  count(DISTINCT e.id) as dse_count,s.lvl_id  FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code JOIN " . TABLE_USER . " u ON u.smpin=e.smpin JOIN " . TABLE_TRAINING_PARTICIPANT . " p ON p.userid=u.userid JOIN " . TABLE_TRAINING_SCHEDULE . " s ON s.schedule_id=p.schedule_id WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND p.certification=1 AND s.is_deleted=0  " . $tdate_cond . "  GROUP BY s.lvl_id";
                $trained_dse_data = $parent->getQueryValue($trained_query_str);

                if (count($trained_dse_data) > 0) {
                    foreach ($trained_dse_data as $lvl) {
                        switch ($lvl->lvl_id) {
                            case '1':$tot_base_trained = $lvl->dse_count;
                                break;
                            case '2':$tot_bronze_trained = $lvl->dse_count;
                                break;
                            case '3':$tot_silver_l1_trained = $lvl->dse_count;
                                break;
                            case '4':$tot_silver_l2_trained = $lvl->dse_count;
                                break;
                            case '5':$tot_gold_l1_trained = $lvl->dse_count;
                                break;
                            case '6':$tot_gold_l2_trained = $lvl->dse_count;
                                break;
                        }
                    }
                }

                $data_arr['footer'] = array('Total', $tot_dealer_count, $tot_dse_count, $tot_base_trained, $tot_base_certified, $tot_bronze_trained, $tot_bronze_certified, $tot_silver_l1_trained, $tot_silver_l1_certified, $tot_silver_l2_trained, $tot_silver_l2_certified, $tot_silver_certified, $tot_gold_l1_trained, $tot_gold_l1_certified, $tot_gold_l2_trained, $tot_gold_l2_certified, $tot_gold_certified);
                break;
            case 'zm':
                //RM
                $query_str = "SELECT rm_name,rm_email,count(d.id) as dealer_count FROM " . DMS_DEALER . " d WHERE status=1 AND is_deleted='0' " . $user_cond . " GROUP BY rm_email";
                $get_data = $parent->getQueryValue($query_str);

                $data_arr['header'] = array("RM", "Total Dealer", "Total DSE", "Base Trained", "Base Certified", "Bronze Trained", "Bronze certified", "Silver L1 Trained", "Silver L1 Certified", "Silver L2 Trained", "Silver L2 Certified", "Silver Certified", "Gold L1 Trained", "Gold L1 Certified", "Gold L2 Trained", "Gold L2 Certified", "Gold Certified");

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

                        $dsequery_str = "SELECT  count(DISTINCT e.id) as dse_count FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND d.rm_email='" . $val->rm_email . "'";
                        $dse_data = $parent->getQueryValue($dsequery_str);
                        $dse_count = count($dse_data) > 0 ? $dse_data[0]->dse_count : 0;

                        $base_trained = 0;
                        $base_certified = 0;
                        $bronze_trained = 0;
                        $bronze_certified = 0;
                        $silver_l1_trained = 0;
                        $silver_l1_certified = 0;
                        $silver_l2_trained = 0;
                        $silver_l2_certified = 0;
                        $silver_certified = 0;
                        $gold_l1_trained = 0;
                        $gold_l1_certified = 0;
                        $gold_l2_trained = 0;
                        $gold_l2_certified = 0;
                        $gold_certified = 0;

                        $certificate_query_str = "SELECT  count(DISTINCT e.id) as dse_count,lvl_id FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code JOIN " . TABLE_USER_LEARNING . " l ON e.smpin=l.smpin WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND d.rm_email='" . $val->rm_email . "' " . $date_cond . " AND l.status=3 GROUP BY l.lvl_id";
                        $dse_data = $parent->getQueryValue($certificate_query_str);

                        if (count($dse_data) > 0) {
                            foreach ($dse_data as $lvl) {
                                switch ($lvl->lvl_id) {
                                    case '1':$base_certified = $lvl->dse_count;
                                        break;
                                    case '2':$bronze_certified = $lvl->dse_count;
                                        break;
                                    case '3':$silver_l1_certified = $lvl->dse_count;
                                        break;
                                    case '4':$silver_l2_certified = $lvl->dse_count;
                                        $silver_certified = $silver_l2_certified;
                                        break;
                                    case '5':$gold_l1_certified = $lvl->dse_count;
                                        break;
                                    case '6':$gold_l2_certified = $lvl->dse_count;
                                        $gold_certified = $gold_l2_certified;
                                        break;
                                }
                            }
                        }

                        $trained_query_str = "SELECT  count(DISTINCT e.id) as dse_count,s.lvl_id  FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code JOIN " . TABLE_USER . " u ON u.smpin=e.smpin JOIN " . TABLE_TRAINING_PARTICIPANT . " p ON p.userid=u.userid JOIN " . TABLE_TRAINING_SCHEDULE . " s ON s.schedule_id=p.schedule_id WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND p.certification=1 AND s.is_deleted=0 AND d.rm_email='" . $val->rm_email . "' " . $tdate_cond . " GROUP BY s.lvl_id";
                        $trained_dse_data = $parent->getQueryValue($trained_query_str);

                        if (count($trained_dse_data) > 0) {
                            foreach ($trained_dse_data as $lvl) {
                                switch ($lvl->lvl_id) {
                                    case '1':$base_trained = $lvl->dse_count;
                                        break;
                                    case '2':$bronze_trained = $lvl->dse_count;
                                        break;
                                    case '3':$silver_l1_trained = $lvl->dse_count;
                                        break;
                                    case '4':$silver_l2_trained = $lvl->dse_count;
                                        break;
                                    case '5':$gold_l1_trained = $lvl->dse_count;
                                        break;
                                    case '6':$gold_l2_trained = $lvl->dse_count;
                                        break;
                                }
                            }
                        }

                        $ret_data[] = array($val->rm_name, $val->dealer_count, $dse_count, $base_trained, $base_certified, $bronze_trained, $bronze_certified, $silver_l1_trained, $silver_l1_certified, $silver_l2_trained, $silver_l2_certified, $silver_certified, $gold_l1_trained, $gold_l1_certified, $gold_l2_trained, $gold_l2_certified, $gold_certified);

                        $tot_dealer_count += $val->dealer_count;
                        $tot_dse_count += $dse_count;
                    }
                }
                $data_arr['value'] = $ret_data;
                //TOTAL
                $tot_base_trained = 0;
                $tot_base_certified = 0;
                $tot_bronze_trained = 0;
                $tot_bronze_certified = 0;
                $tot_silver_l1_trained = 0;
                $tot_silver_l1_certified = 0;
                $tot_silver_l2_trained = 0;
                $tot_silver_l2_certified = 0;
                $tot_silver_certified = 0;
                $tot_gold_l1_trained = 0;
                $tot_gold_l1_certified = 0;
                $tot_gold_l2_trained = 0;
                $tot_gold_l2_certified = 0;
                $tot_gold_certified = 0;

                $certificate_query_str = "SELECT  count(DISTINCT e.id) as dse_count,lvl_id FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code JOIN " . TABLE_USER_LEARNING . " l ON e.smpin=l.smpin WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND l.status=3 " . $user_cond . " " . $date_cond . " GROUP BY l.lvl_id";
                $dse_data = $parent->getQueryValue($certificate_query_str);

                if (count($dse_data) > 0) {
                    foreach ($dse_data as $lvl) {
                        switch ($lvl->lvl_id) {
                            case '1':$tot_base_certified = $lvl->dse_count;
                                break;
                            case '2':$tot_bronze_certified = $lvl->dse_count;
                                break;
                            case '3':$tot_silver_l1_certified = $lvl->dse_count;
                                break;
                            case '4':$tot_silver_l2_certified = $lvl->dse_count;
                                $tot_silver_certified = $tot_silver_l2_certified;
                                break;
                            case '5':$tot_gold_l1_certified = $lvl->dse_count;
                                break;
                            case '6':$tot_gold_l2_certified = $lvl->dse_count;
                                $tot_gold_certified = $tot_gold_l2_certified;
                                break;
                        }
                    }
                }

                $trained_query_str = "SELECT  count(DISTINCT e.id) as dse_count,s.lvl_id  FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code JOIN " . TABLE_USER . " u ON u.smpin=e.smpin JOIN " . TABLE_TRAINING_PARTICIPANT . " p ON p.userid=u.userid JOIN " . TABLE_TRAINING_SCHEDULE . " s ON s.schedule_id=p.schedule_id WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND p.certification=1 AND s.is_deleted=0 " . $user_cond . " " . $tdate_cond . "  GROUP BY s.lvl_id";
                $trained_dse_data = $parent->getQueryValue($trained_query_str);

                if (count($trained_dse_data) > 0) {
                    foreach ($trained_dse_data as $lvl) {
                        switch ($lvl->lvl_id) {
                            case '1':$tot_base_trained = $lvl->dse_count;
                                break;
                            case '2':$tot_bronze_trained = $lvl->dse_count;
                                break;
                            case '3':$tot_silver_l1_trained = $lvl->dse_count;
                                break;
                            case '4':$tot_silver_l2_trained = $lvl->dse_count;
                                break;
                            case '5':$tot_gold_l1_trained = $lvl->dse_count;
                                break;
                            case '6':$tot_gold_l2_trained = $lvl->dse_count;
                                break;
                        }
                    }
                }

                $data_arr['footer'] = array('Total', $tot_dealer_count, $tot_dse_count, $tot_base_trained, $tot_base_certified, $tot_bronze_trained, $tot_bronze_certified, $tot_silver_l1_trained, $tot_silver_l1_certified, $tot_silver_l2_trained, $tot_silver_l2_certified, $tot_silver_certified, $tot_gold_l1_trained, $tot_gold_l1_certified, $tot_gold_l2_trained, $tot_gold_l2_certified, $tot_gold_certified);
                break;
            case 'trainer':
                //RM
                $query_str = "SELECT rm_name,rm_email,count(d.id) as dealer_count FROM " . DMS_DEALER . " d WHERE status=1 AND is_deleted='0' " . $user_cond . " GROUP BY rm_email";
                $get_data = $parent->getQueryValue($query_str);
                $data_arr['header'] = array("RM", "Total Dealer", "Total DSE", "Base Trained", "Base Certified", "Bronze Trained", "Bronze certified", "Silver L1 Trained", "Silver L1 Certified", "Silver L2 Trained", "Silver L2 Certified", "Silver Certified", "Gold L1 Trained", "Gold L1 Certified", "Gold L2 Trained", "Gold L2 Certified", "Gold Certified");

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

                        $dsequery_str = "SELECT  count(DISTINCT e.id) as dse_count FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND d.rm_email='" . $val->rm_email . "'";
                        $dse_data = $parent->getQueryValue($dsequery_str);
                        $dse_count = count($dse_data) > 0 ? $dse_data[0]->dse_count : 0;

                        $base_trained = 0;
                        $base_certified = 0;
                        $bronze_trained = 0;
                        $bronze_certified = 0;
                        $silver_l1_trained = 0;
                        $silver_l1_certified = 0;
                        $silver_l2_trained = 0;
                        $silver_l2_certified = 0;
                        $silver_certified = 0;
                        $gold_l1_trained = 0;
                        $gold_l1_certified = 0;
                        $gold_l2_trained = 0;
                        $gold_l2_certified = 0;
                        $gold_certified = 0;

                        $certificate_query_str = "SELECT  count(DISTINCT e.id) as dse_count,lvl_id FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code JOIN " . TABLE_USER_LEARNING . " l ON e.smpin=l.smpin WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND d.rm_email='" . $val->rm_email . "' " . $date_cond . " AND l.status=3 GROUP BY l.lvl_id";
                        $dse_data = $parent->getQueryValue($certificate_query_str);

                        if (count($dse_data) > 0) {
                            foreach ($dse_data as $lvl) {
                                switch ($lvl->lvl_id) {
                                    case '1':$base_certified = $lvl->dse_count;
                                        break;
                                    case '2':$bronze_certified = $lvl->dse_count;
                                        break;
                                    case '3':$silver_l1_certified = $lvl->dse_count;
                                        break;
                                    case '4':$silver_l2_certified = $lvl->dse_count;
                                        $silver_certified = $silver_l2_certified;
                                        break;
                                    case '5':$gold_l1_certified = $lvl->dse_count;
                                        break;
                                    case '6':$gold_l2_certified = $lvl->dse_count;
                                        $gold_certified = $gold_l2_certified;
                                        break;
                                }
                            }
                        }

                        $trained_query_str = "SELECT  count(DISTINCT e.id) as dse_count,s.lvl_id  FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code JOIN " . TABLE_USER . " u ON u.smpin=e.smpin JOIN " . TABLE_TRAINING_PARTICIPANT . " p ON p.userid=u.userid JOIN " . TABLE_TRAINING_SCHEDULE . " s ON s.schedule_id=p.schedule_id WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND p.certification=1 AND s.is_deleted=0 AND d.rm_email='" . $val->rm_email . "' " . $tdate_cond . " GROUP BY s.lvl_id";
                        $trained_dse_data = $parent->getQueryValue($trained_query_str);

                        if (count($trained_dse_data) > 0) {
                            foreach ($trained_dse_data as $lvl) {
                                switch ($lvl->lvl_id) {
                                    case '1':$base_trained = $lvl->dse_count;
                                        break;
                                    case '2':$bronze_trained = $lvl->dse_count;
                                        break;
                                    case '3':$silver_l1_trained = $lvl->dse_count;
                                        break;
                                    case '4':$silver_l2_trained = $lvl->dse_count;
                                        break;
                                    case '5':$gold_l1_trained = $lvl->dse_count;
                                        break;
                                    case '6':$gold_l2_trained = $lvl->dse_count;
                                        break;
                                }
                            }
                        }

                        $ret_data[] = array($val->rm_name, $val->dealer_count, $dse_count, $base_trained, $base_certified, $bronze_trained, $bronze_certified, $silver_l1_trained, $silver_l1_certified, $silver_l2_trained, $silver_l2_certified, $silver_certified, $gold_l1_trained, $gold_l1_certified, $gold_l2_trained, $gold_l2_certified, $gold_certified);

                        $tot_dealer_count += $val->dealer_count;
                        $tot_dse_count += $dse_count;
                    }
                }
                $data_arr['value'] = $ret_data;
                //TOTAL
                $tot_base_trained = 0;
                $tot_base_certified = 0;
                $tot_bronze_trained = 0;
                $tot_bronze_certified = 0;
                $tot_silver_l1_trained = 0;
                $tot_silver_l1_certified = 0;
                $tot_silver_l2_trained = 0;
                $tot_silver_l2_certified = 0;
                $tot_silver_certified = 0;
                $tot_gold_l1_trained = 0;
                $tot_gold_l1_certified = 0;
                $tot_gold_l2_trained = 0;
                $tot_gold_l2_certified = 0;
                $tot_gold_certified = 0;

                $certificate_query_str = "SELECT  count(DISTINCT e.id) as dse_count,lvl_id FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code JOIN " . TABLE_USER_LEARNING . " l ON e.smpin=l.smpin WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND l.status=3 " . $user_cond . " " . $date_cond . " GROUP BY l.lvl_id";
                $dse_data = $parent->getQueryValue($certificate_query_str);

                if (count($dse_data) > 0) {
                    foreach ($dse_data as $lvl) {
                        switch ($lvl->lvl_id) {
                            case '1':$tot_base_certified = $lvl->dse_count;
                                break;
                            case '2':$tot_bronze_certified = $lvl->dse_count;
                                break;
                            case '3':$tot_silver_l1_certified = $lvl->dse_count;
                                break;
                            case '4':$tot_silver_l2_certified = $lvl->dse_count;
                                $tot_silver_certified = $tot_silver_l2_certified;
                                break;
                            case '5':$tot_gold_l1_certified = $lvl->dse_count;
                                break;
                            case '6':$tot_gold_l2_certified = $lvl->dse_count;
                                $tot_gold_certified = $tot_gold_l2_certified;
                                break;
                        }
                    }
                }

                $trained_query_str = "SELECT  count(DISTINCT e.id) as dse_count,s.lvl_id  FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code JOIN " . TABLE_USER . " u ON u.smpin=e.smpin JOIN " . TABLE_TRAINING_PARTICIPANT . " p ON p.userid=u.userid JOIN " . TABLE_TRAINING_SCHEDULE . " s ON s.schedule_id=p.schedule_id WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND p.certification=1 AND s.is_deleted=0 " . $user_cond . " " . $tdate_cond . "  GROUP BY s.lvl_id";
                $trained_dse_data = $parent->getQueryValue($trained_query_str);

                if (count($trained_dse_data) > 0) {
                    foreach ($trained_dse_data as $lvl) {
                        switch ($lvl->lvl_id) {
                            case '1':$tot_base_trained = $lvl->dse_count;
                                break;
                            case '2':$tot_bronze_trained = $lvl->dse_count;
                                break;
                            case '3':$tot_silver_l1_trained = $lvl->dse_count;
                                break;
                            case '4':$tot_silver_l2_trained = $lvl->dse_count;
                                break;
                            case '5':$tot_gold_l1_trained = $lvl->dse_count;
                                break;
                            case '6':$tot_gold_l2_trained = $lvl->dse_count;
                                break;
                        }
                    }
                }

                $data_arr['footer'] = array('Total', $tot_dealer_count, $tot_dse_count, $tot_base_trained, $tot_base_certified, $tot_bronze_trained, $tot_bronze_certified, $tot_silver_l1_trained, $tot_silver_l1_certified, $tot_silver_l2_trained, $tot_silver_l2_certified, $tot_silver_certified, $tot_gold_l1_trained, $tot_gold_l1_certified, $tot_gold_l2_trained, $tot_gold_l2_certified, $tot_gold_certified);
                break;
            case 'rm':
                //AM
                $query_str = "SELECT am_name,am_email,count(d.id) as dealer_count FROM " . DMS_DEALER . " d WHERE status=1 AND is_deleted='0' " . $user_cond . " GROUP BY am_email";
                $get_data = $parent->getQueryValue($query_str);
                $data_arr['header'] = array("AM", "Total Dealer", "Total DSE", "Base Trained", "Base Certified", "Bronze Trained", "Bronze certified", "Silver L1 Trained", "Silver L1 Certified", "Silver L2 Trained", "Silver L2 Certified", "Silver Certified", "Gold L1 Trained", "Gold L1 Certified", "Gold L2 Trained", "Gold L2 Certified", "Gold Certified");

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

                        $dsequery_str = "SELECT  count(DISTINCT e.id) as dse_count FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND d.am_email='" . $val->am_email . "'";
                        $dse_data = $parent->getQueryValue($dsequery_str);
                        $dse_count = count($dse_data) > 0 ? $dse_data[0]->dse_count : 0;

                        $base_trained = 0;
                        $base_certified = 0;
                        $bronze_trained = 0;
                        $bronze_certified = 0;
                        $silver_l1_trained = 0;
                        $silver_l1_certified = 0;
                        $silver_l2_trained = 0;
                        $silver_l2_certified = 0;
                        $silver_certified = 0;
                        $gold_l1_trained = 0;
                        $gold_l1_certified = 0;
                        $gold_l2_trained = 0;
                        $gold_l2_certified = 0;
                        $gold_certified = 0;

                        $certificate_query_str = "SELECT  count(DISTINCT e.id) as dse_count,lvl_id FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code JOIN " . TABLE_USER_LEARNING . " l ON e.smpin=l.smpin WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND d.am_email='" . $val->am_email . "' " . $date_cond . " AND l.status=3 GROUP BY l.lvl_id";
                        $dse_data = $parent->getQueryValue($certificate_query_str);

                        if (count($dse_data) > 0) {
                            foreach ($dse_data as $lvl) {
                                switch ($lvl->lvl_id) {
                                    case '1':$base_certified = $lvl->dse_count;
                                        break;
                                    case '2':$bronze_certified = $lvl->dse_count;
                                        break;
                                    case '3':$silver_l1_certified = $lvl->dse_count;
                                        break;
                                    case '4':$silver_l2_certified = $lvl->dse_count;
                                        $silver_certified = $silver_l2_certified;
                                        break;
                                    case '5':$gold_l1_certified = $lvl->dse_count;
                                        break;
                                    case '6':$gold_l2_certified = $lvl->dse_count;
                                        $gold_certified = $gold_l2_certified;
                                        break;
                                }
                            }
                        }

                        $trained_query_str = "SELECT  count(DISTINCT e.id) as dse_count,s.lvl_id  FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code JOIN " . TABLE_USER . " u ON u.smpin=e.smpin JOIN " . TABLE_TRAINING_PARTICIPANT . " p ON p.userid=u.userid JOIN " . TABLE_TRAINING_SCHEDULE . " s ON s.schedule_id=p.schedule_id WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND p.certification=1 AND s.is_deleted=0 AND d.am_email='" . $val->am_email . "' " . $tdate_cond . "  GROUP BY s.lvl_id";
                        $trained_dse_data = $parent->getQueryValue($trained_query_str);

                        if (count($trained_dse_data) > 0) {
                            foreach ($trained_dse_data as $lvl) {
                                switch ($lvl->lvl_id) {
                                    case '1':$base_trained = $lvl->dse_count;
                                        break;
                                    case '2':$bronze_trained = $lvl->dse_count;
                                        break;
                                    case '3':$silver_l1_trained = $lvl->dse_count;
                                        break;
                                    case '4':$silver_l2_trained = $lvl->dse_count;
                                        break;
                                    case '5':$gold_l1_trained = $lvl->dse_count;
                                        break;
                                    case '6':$gold_l2_trained = $lvl->dse_count;
                                        break;
                                }
                            }
                        }

                        $ret_data[] = array($val->am_name, $val->dealer_count, $dse_count, $base_trained, $base_certified, $bronze_trained, $bronze_certified, $silver_l1_trained, $silver_l1_certified, $silver_l2_trained, $silver_l2_certified, $silver_certified, $gold_l1_trained, $gold_l1_certified, $gold_l2_trained, $gold_l2_certified, $gold_certified);

                        $tot_dealer_count += $val->dealer_count;
                        $tot_dse_count += $dse_count;
                    }
                }
                $data_arr['value'] = $ret_data;
                //TOTAL
                $tot_base_trained = 0;
                $tot_base_certified = 0;
                $tot_bronze_trained = 0;
                $tot_bronze_certified = 0;
                $tot_silver_l1_trained = 0;
                $tot_silver_l1_certified = 0;
                $tot_silver_l2_trained = 0;
                $tot_silver_l2_certified = 0;
                $tot_silver_certified = 0;
                $tot_gold_l1_trained = 0;
                $tot_gold_l1_certified = 0;
                $tot_gold_l2_trained = 0;
                $tot_gold_l2_certified = 0;
                $tot_gold_certified = 0;

                $certificate_query_str = "SELECT  count(DISTINCT e.id) as dse_count,lvl_id FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code JOIN " . TABLE_USER_LEARNING . " l ON e.smpin=l.smpin WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND l.status=3 " . $user_cond . "  " . $date_cond . "   GROUP BY l.lvl_id";
                $dse_data = $parent->getQueryValue($certificate_query_str);

                if (count($dse_data) > 0) {
                    foreach ($dse_data as $lvl) {
                        switch ($lvl->lvl_id) {
                            case '1':$tot_base_certified = $lvl->dse_count;
                                break;
                            case '2':$tot_bronze_certified = $lvl->dse_count;
                                break;
                            case '3':$tot_silver_l1_certified = $lvl->dse_count;
                                break;
                            case '4':$tot_silver_l2_certified = $lvl->dse_count;
                                $tot_silver_certified = $tot_silver_l2_certified;
                                break;
                            case '5':$tot_gold_l1_certified = $lvl->dse_count;
                                break;
                            case '6':$tot_gold_l2_certified = $lvl->dse_count;
                                $tot_gold_certified = $tot_gold_l2_certified;
                                break;
                        }
                    }
                }

                $trained_query_str = "SELECT  count(DISTINCT e.id) as dse_count,s.lvl_id  FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code JOIN " . TABLE_USER . " u ON u.smpin=e.smpin JOIN " . TABLE_TRAINING_PARTICIPANT . " p ON p.userid=u.userid JOIN " . TABLE_TRAINING_SCHEDULE . " s ON s.schedule_id=p.schedule_id WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND p.certification=1 AND s.is_deleted=0 " . $user_cond . " " . $tdate_cond . "  GROUP BY s.lvl_id";
                $trained_dse_data = $parent->getQueryValue($trained_query_str);

                if (count($trained_dse_data) > 0) {
                    foreach ($trained_dse_data as $lvl) {
                        switch ($lvl->lvl_id) {
                            case '1':$tot_base_trained = $lvl->dse_count;
                                break;
                            case '2':$tot_bronze_trained = $lvl->dse_count;
                                break;
                            case '3':$tot_silver_l1_trained = $lvl->dse_count;
                                break;
                            case '4':$tot_silver_l2_trained = $lvl->dse_count;
                                break;
                            case '5':$tot_gold_l1_trained = $lvl->dse_count;
                                break;
                            case '6':$tot_gold_l2_trained = $lvl->dse_count;
                                break;
                        }
                    }
                }

                $data_arr['footer'] = array('Total', $tot_dealer_count, $tot_dse_count, $tot_base_trained, $tot_base_certified, $tot_bronze_trained, $tot_bronze_certified, $tot_silver_l1_trained, $tot_silver_l1_certified, $tot_silver_l2_trained, $tot_silver_l2_certified, $tot_silver_certified, $tot_gold_l1_trained, $tot_gold_l1_certified, $tot_gold_l2_trained, $tot_gold_l2_certified, $tot_gold_certified);
                break;
            case 'am':
                //AM
                $query_str = "SELECT dealer_code,dealer_name,count(d.id) as dealer_count FROM " . DMS_DEALER . " d WHERE status=1 AND is_deleted='0' " . $user_cond . " ";
                $get_data = $parent->getQueryValue($query_str);
                $data_arr['header'] = array("Dealer", "Total DSE", "Base Trained", "Base Certified", "Bronze Trained", "Bronze certified", "Silver L1 Trained", "Silver L1 Certified", "Silver L2 Trained", "Silver L2 Certified", "Silver Certified", "Gold L1 Trained", "Gold L1 Certified", "Gold L2 Trained", "Gold L2 Certified", "Gold Certified");

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

                        $dsequery_str = "SELECT  count(DISTINCT e.id) as dse_count FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND d.dealer_code='" . $val->dealer_code . "'";
                        $dse_data = $parent->getQueryValue($dsequery_str);
                        $dse_count = count($dse_data) > 0 ? $dse_data[0]->dse_count : 0;

                        $base_trained = 0;
                        $base_certified = 0;
                        $bronze_trained = 0;
                        $bronze_certified = 0;
                        $silver_l1_trained = 0;
                        $silver_l1_certified = 0;
                        $silver_l2_trained = 0;
                        $silver_l2_certified = 0;
                        $silver_certified = 0;
                        $gold_l1_trained = 0;
                        $gold_l1_certified = 0;
                        $gold_l2_trained = 0;
                        $gold_l2_certified = 0;
                        $gold_certified = 0;

                        $certificate_query_str = "SELECT  count(DISTINCT e.id) as dse_count,lvl_id FROM " . DMS_EMPLOYEE . " e  JOIN " . TABLE_USER_LEARNING . " l ON e.smpin=l.smpin WHERE e.status=1 AND e.is_current=1 AND e.dealer_code='" . $val->dealer_code . "' " . $date_cond . " AND l.status=3 GROUP BY l.lvl_id";
                        $dse_data = $parent->getQueryValue($certificate_query_str);

                        if (count($dse_data) > 0) {
                            foreach ($dse_data as $lvl) {
                                switch ($lvl->lvl_id) {
                                    case '1':$base_certified = $lvl->dse_count;
                                        break;
                                    case '2':$bronze_certified = $lvl->dse_count;
                                        break;
                                    case '3':$silver_l1_certified = $lvl->dse_count;
                                        break;
                                    case '4':$silver_l2_certified = $lvl->dse_count;
                                        $silver_certified = $silver_l2_certified;
                                        break;
                                    case '5':$gold_l1_certified = $lvl->dse_count;
                                        break;
                                    case '6':$gold_l2_certified = $lvl->dse_count;
                                        $gold_certified = $gold_l2_certified;
                                        break;
                                }
                            }
                        }

                        $trained_query_str = "SELECT  count(DISTINCT e.id) as dse_count,s.lvl_id  FROM " . DMS_EMPLOYEE . " e  JOIN " . TABLE_USER . " u ON u.smpin=e.smpin JOIN " . TABLE_TRAINING_PARTICIPANT . " p ON p.userid=u.userid JOIN " . TABLE_TRAINING_SCHEDULE . " s ON s.schedule_id=p.schedule_id WHERE e.status=1 AND e.is_current=1 AND p.certification=1 AND s.is_deleted=0 AND e.dealer_code='" . $val->dealer_code . "' " . $tdate_cond . "  GROUP BY s.lvl_id";
                        $trained_dse_data = $parent->getQueryValue($trained_query_str);

                        if (count($trained_dse_data) > 0) {
                            foreach ($trained_dse_data as $lvl) {
                                switch ($lvl->lvl_id) {
                                    case '1':$base_trained = $lvl->dse_count;
                                        break;
                                    case '2':$bronze_trained = $lvl->dse_count;
                                        break;
                                    case '3':$silver_l1_trained = $lvl->dse_count;
                                        break;
                                    case '4':$silver_l2_trained = $lvl->dse_count;
                                        break;
                                    case '5':$gold_l1_trained = $lvl->dse_count;
                                        break;
                                    case '6':$gold_l2_trained = $lvl->dse_count;
                                        break;
                                }
                            }
                        }

                        $ret_data[] = array($val->dealer_name . '-' . $val->dealer_code, $dse_count, $base_trained, $base_certified, $bronze_trained, $bronze_certified, $silver_l1_trained, $silver_l1_certified, $silver_l2_trained, $silver_l2_certified, $silver_certified, $gold_l1_trained, $gold_l1_certified, $gold_l2_trained, $gold_l2_certified, $gold_certified);

                        $tot_dealer_count += $val->dealer_count;
                        $tot_dse_count += $dse_count;
                    }
                }
                $data_arr['value'] = $ret_data;

                //TOTAL
                $tot_base_trained = 0;
                $tot_base_certified = 0;
                $tot_bronze_trained = 0;
                $tot_bronze_certified = 0;
                $tot_silver_l1_trained = 0;
                $tot_silver_l1_certified = 0;
                $tot_silver_l2_trained = 0;
                $tot_silver_l2_certified = 0;
                $tot_silver_certified = 0;
                $tot_gold_l1_trained = 0;
                $tot_gold_l1_certified = 0;
                $tot_gold_l2_trained = 0;
                $tot_gold_l2_certified = 0;
                $tot_gold_certified = 0;

                $certificate_query_str = "SELECT  count(DISTINCT e.id) as dse_count,lvl_id FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code JOIN " . TABLE_USER_LEARNING . " l ON e.smpin=l.smpin WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND l.status=3 " . $user_cond . " " . $date_cond . " GROUP BY l.lvl_id";
                $dse_data = $parent->getQueryValue($certificate_query_str);

                if (count($dse_data) > 0) {
                    foreach ($dse_data as $lvl) {
                        switch ($lvl->lvl_id) {
                            case '1':$tot_base_certified = $lvl->dse_count;
                                break;
                            case '2':$tot_bronze_certified = $lvl->dse_count;
                                break;
                            case '3':$tot_silver_l1_certified = $lvl->dse_count;
                                break;
                            case '4':$tot_silver_l2_certified = $lvl->dse_count;
                                $tot_silver_certified = $tot_silver_l2_certified;
                                break;
                            case '5':$tot_gold_l1_certified = $lvl->dse_count;
                                break;
                            case '6':$tot_gold_l2_certified = $lvl->dse_count;
                                $tot_gold_certified = $tot_gold_l2_certified;
                                break;
                        }
                    }
                }

                $trained_query_str = "SELECT  count(DISTINCT e.id) as dse_count,s.lvl_id  FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code JOIN " . TABLE_USER . " u ON u.smpin=e.smpin JOIN " . TABLE_TRAINING_PARTICIPANT . " p ON p.userid=u.userid JOIN " . TABLE_TRAINING_SCHEDULE . " s ON s.schedule_id=p.schedule_id WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND p.certification=1 AND s.is_deleted=0 " . $user_cond . " " . $tdate_cond . "  GROUP BY s.lvl_id";
                $trained_dse_data = $parent->getQueryValue($trained_query_str);

                if (count($trained_dse_data) > 0) {
                    foreach ($trained_dse_data as $lvl) {
                        switch ($lvl->lvl_id) {
                            case '1':$tot_base_trained = $lvl->dse_count;
                                break;
                            case '2':$tot_bronze_trained = $lvl->dse_count;
                                break;
                            case '3':$tot_silver_l1_trained = $lvl->dse_count;
                                break;
                            case '4':$tot_silver_l2_trained = $lvl->dse_count;
                                break;
                            case '5':$tot_gold_l1_trained = $lvl->dse_count;
                                break;
                            case '6':$tot_gold_l2_trained = $lvl->dse_count;
                                break;
                        }
                    }
                }

                $data_arr['footer'] = array('Total', $tot_dealer_count, $tot_dse_count, $tot_base_trained, $tot_base_certified, $tot_bronze_trained, $tot_bronze_certified, $tot_silver_l1_trained, $tot_silver_l1_certified, $tot_silver_l2_trained, $tot_silver_l2_certified, $tot_silver_certified, $tot_gold_l1_trained, $tot_gold_l1_certified, $tot_gold_l2_trained, $tot_gold_l2_certified, $tot_gold_certified);
                break;
            case 'dealer':
                $query_str = "SELECT fname,lname,smpin FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON d.dealer_code=e.dealer_code WHERE e.status=1 AND e.is_current=1 " . $user_cond . " ";
                $get_data = $parent->getQueryValue($query_str);

                $data_arr['header'] = array("DSE", "SMPIN", "Base Trained", "Base Certified", "Bronze Trained", "Bronze certified", "Silver L1 Trained", "Silver L1 Certified", "Silver L2 Trained", "Silver L2 Certified", "Silver Certified", "Gold L1 Trained", "Gold L1 Certified", "Gold L2 Trained", "Gold L2 Certified", "Gold Certified");

                $tot_dse = count($get_data);

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

                        $certificate_query_str = "SELECT  count(DISTINCT e.id) as dse_count,lvl_id FROM " . DMS_EMPLOYEE . " e  JOIN " . TABLE_USER_LEARNING . " l ON e.smpin=l.smpin WHERE e.status=1 AND e.is_current=1 AND e.smpin='" . $val->smpin . "' " . $date_cond . " AND l.status=3 GROUP BY l.lvl_id";
                        $dse_data = $parent->getQueryValue($certificate_query_str);

                        $base_trained = '';
                        $base_certified = '';
                        $bronze_trained = '';
                        $bronze_certified = '';
                        $silver_l1_trained = '';
                        $silver_l1_certified = '';
                        $silver_l2_trained = '';
                        $silver_l2_certified = '';
                        $silver_certified = '';
                        $gold_l1_trained = '';
                        $gold_l1_certified = '';
                        $gold_l2_trained = '';
                        $gold_l2_certified = '';
                        $gold_certified = '';

                        if (count($dse_data) > 0) {
                            foreach ($dse_data as $lvl) {
                                switch ($lvl->lvl_id) {
                                    case '1':$base_certified = ($lvl->dse_count > 0) ? "Completed" : '';
                                        break;
                                    case '2':$bronze_certified = ($lvl->dse_count > 0) ? "Completed" : '';
                                        break;
                                    case '3':$silver_l1_certified = ($lvl->dse_count > 0) ? "Completed" : '';
                                        break;
                                    case '4':$silver_l2_certified = ($lvl->dse_count > 0) ? "Completed" : '';
                                        $silver_certified = $silver_l2_certified;
                                        break;
                                    case '5':$gold_l1_certified = ($lvl->dse_count > 0) ? "Completed" : '';
                                        break;
                                    case '6':$gold_l2_certified = ($lvl->dse_count > 0) ? "Completed" : '';
                                        $gold_certified = $gold_l2_certified;
                                        break;
                                }
                            }
                        }

                        $trained_query_str = "SELECT  count(DISTINCT e.id) as dse_count,s.lvl_id  FROM " . DMS_EMPLOYEE . " e  JOIN " . TABLE_USER . " u ON u.smpin=e.smpin JOIN " . TABLE_TRAINING_PARTICIPANT . " p ON p.userid=u.userid JOIN " . TABLE_TRAINING_SCHEDULE . " s ON s.schedule_id=p.schedule_id WHERE e.status=1 AND e.is_current=1 AND p.certification=1 AND s.is_deleted=0 AND e.smpin='" . $val->smpin . "' " . $tdate_cond . "  GROUP BY s.lvl_id";
                        $trained_dse_data = $parent->getQueryValue($trained_query_str);

                        if (count($trained_dse_data) > 0) {
                            foreach ($trained_dse_data as $lvl) {
                                switch ($lvl->lvl_id) {
                                    case '1':$base_trained = ($lvl->dse_count > 0) ? "Completed" : '';
                                        break;
                                    case '2':$bronze_trained = ($lvl->dse_count > 0) ? "Completed" : '';
                                        break;
                                    case '3':$silver_l1_trained = ($lvl->dse_count > 0) ? "Completed" : '';
                                        break;
                                    case '4':$silver_l2_trained = ($lvl->dse_count > 0) ? "Completed" : '';
                                        break;
                                    case '5':$gold_l1_trained = ($lvl->dse_count > 0) ? "Completed" : '';
                                        break;
                                    case '6':$gold_l2_trained = ($lvl->dse_count > 0) ? "Completed" : '';
                                        break;
                                }
                            }
                        }

                        $ret_data[] = array($val->fname . '-' . $val->lname, $val->smpin, $base_trained, $base_certified, $bronze_trained, $bronze_certified, $silver_l1_trained, $silver_l1_certified, $silver_l2_trained, $silver_l2_certified, $silver_certified, $gold_l1_trained, $gold_l1_certified, $gold_l2_trained, $gold_l2_certified, $gold_certified);
                    }
                }
                $data_arr['value'] = $ret_data;

                //TOTAL
                $tot_base_trained = 0;
                $tot_base_certified = 0;
                $tot_bronze_trained = 0;
                $tot_bronze_certified = 0;
                $tot_silver_l1_trained = 0;
                $tot_silver_l1_certified = 0;
                $tot_silver_l2_trained = 0;
                $tot_silver_l2_certified = 0;
                $tot_silver_certified = 0;
                $tot_gold_l1_trained = 0;
                $tot_gold_l1_certified = 0;
                $tot_gold_l2_trained = 0;
                $tot_gold_l2_certified = 0;
                $tot_gold_certified = 0;

                $certificate_query_str = "SELECT  count(DISTINCT e.id) as dse_count,lvl_id FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code JOIN " . TABLE_USER_LEARNING . " l ON e.smpin=l.smpin WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND l.status=3 " . $user_cond . " " . $date_cond . " GROUP BY l.lvl_id";
                $dse_data = $parent->getQueryValue($certificate_query_str);

                if (count($dse_data) > 0) {
                    foreach ($dse_data as $lvl) {
                        switch ($lvl->lvl_id) {
                            case '1':$tot_base_certified = $lvl->dse_count;
                                break;
                            case '2':$tot_bronze_certified = $lvl->dse_count;
                                break;
                            case '3':$tot_silver_l1_certified = $lvl->dse_count;
                                break;
                            case '4':$tot_silver_l2_certified = $lvl->dse_count;
                                $tot_silver_certified = $tot_silver_l2_certified;
                                break;
                            case '5':$tot_gold_l1_certified = $lvl->dse_count;
                                break;
                            case '6':$tot_gold_l2_certified = $lvl->dse_count;
                                $tot_gold_certified = $tot_gold_l2_certified;
                                break;
                        }
                    }
                }

                $trained_query_str = "SELECT  count(DISTINCT e.id) as dse_count,s.lvl_id  FROM " . DMS_EMPLOYEE . " e JOIN " . DMS_DEALER . " d ON e.dealer_code=d.dealer_code JOIN " . TABLE_USER . " u ON u.smpin=e.smpin JOIN " . TABLE_TRAINING_PARTICIPANT . " p ON p.userid=u.userid JOIN " . TABLE_TRAINING_SCHEDULE . " s ON s.schedule_id=p.schedule_id WHERE e.status=1 AND e.is_current=1 AND d.status=1 AND d.is_deleted='0' AND p.certification=1 AND s.is_deleted=0 " . $user_cond . " " . $tdate_cond . "  GROUP BY s.lvl_id";
                $trained_dse_data = $parent->getQueryValue($trained_query_str);

                if (count($trained_dse_data) > 0) {
                    foreach ($trained_dse_data as $lvl) {
                        switch ($lvl->lvl_id) {
                            case '1':$tot_base_trained = $lvl->dse_count;
                                break;
                            case '2':$tot_bronze_trained = $lvl->dse_count;
                                break;
                            case '3':$tot_silver_l1_trained = $lvl->dse_count;
                                break;
                            case '4':$tot_silver_l2_trained = $lvl->dse_count;
                                break;
                            case '5':$tot_gold_l1_trained = $lvl->dse_count;
                                break;
                            case '6':$tot_gold_l2_trained = $lvl->dse_count;
                                break;
                        }
                    }
                }

                $data_arr['footer'] = array('Total', $tot_dse, $tot_base_trained, $tot_base_certified, $tot_bronze_trained, $tot_bronze_certified, $tot_silver_l1_trained, $tot_silver_l1_certified, $tot_silver_l2_trained, $tot_silver_l2_certified, $tot_silver_certified, $tot_gold_l1_trained, $tot_gold_l1_certified, $tot_gold_l2_trained, $tot_gold_l2_certified, $tot_gold_certified);

                break;
        }

        //Excel class library
        require_once dirname(__FILE__) . '/../../PHPExcel.php';
        require_once dirname(__FILE__) . '/../../PHPExcel/Writer/Excel2007.php';

        $objPHPExcel = new PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);

        //EXCEL HEADER
        $row += 2;
        $objPHPExcel->getActiveSheet()->SetCellValue('A' . $row, "TRAINING REPORT");
        $objPHPExcel->getActiveSheet()->mergeCells("A" . $row . ":K" . $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 += 2;
        $bordercells_arr = array("A" . $row, "B" . $row, "C" . $row, "D" . $row, "E" . $row);
        projectfunc::setCellBorder($bordercells_arr, $objPHPExcel);
        $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);

        //LOG
        $logstr = 'TRAINING CLASSROOM REPORT EXPORT';
        globalfunc::insertUserLog($parent, $logstr);

        $objPHPExcel->setActiveSheetIndex(0);
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        $fpath = "../../" . S3_REPORT_URL;
        $filename = "training_classroom_report_" . uniqid() . ".xlsx";
        $objWriter->save($fpath . $filename);

        //CONNECT S3
        $s3 = awsfileserver::connectS3();
        awsfileserver::checkupload($parent, $s3, S3_BUCKET, S3_REPORT_URL . $filename, dirname(__FILE__) . "/../../../" . S3_REPORT_URL . $filename);
        //delete report
        @unlink(dirname(__FILE__) . "/../../../" . S3_REPORT_URL . $filename);
        $filename = awsfileserver::getS3FileUrl($s3, S3_BUCKET, S3_REPORT_URL . $filename);

        $parent->response(array('response' => 'success', 'data' => $filename, 'message' => $this->page_data['success1']), 200);

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

    /* /////////////////////////////// FEEDBACK //////////////////////////////////////////////// */
    private function feedback($parent)
    {
        globalfunc::checkMethodType($parent, array('POST'));
        globalfunc::checkParamCount($parent, 2);
        globalfunc::checkAuthrozation($parent, array('type' => 'user'));

        $parent->response(array('response' => 'success', 'data' => $data_arr, 'message' => $this->page_data['success1']), 200);
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////// PRODUCTIVITY //////////////////////////////////////////////// */
    private function productivity($parent)
    {
        globalfunc::checkMethodType($parent, array('POST'));
        globalfunc::checkParamCount($parent, 2);
        globalfunc::checkAuthrozation($parent, array('type' => 'user'));

        $parent->response(array('response' => 'success', 'data' => $data_arr, 'message' => $this->page_data['success1']), 200);
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////// NOMINATION //////////////////////////////////////////////// */
    private function nomination($parent)
    {
        globalfunc::checkMethodType($parent, array('POST'));
        globalfunc::checkParamCount($parent, 2);
        globalfunc::checkAuthrozation($parent, array('type' => 'user'));

        $parent->response(array('response' => 'success', 'data' => $data_arr, 'message' => $this->page_data['success1']), 200);
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////// OTHER //////////////////////////////////////////////// */
    private function others($parent)
    {
        globalfunc::checkMethodType($parent, array('POST'));
        globalfunc::checkParamCount($parent, 2);
        globalfunc::checkAuthrozation($parent, array('type' => 'user'));

        $parent->response(array('response' => 'success', 'data' => $data_arr, 'message' => $this->page_data['success1']), 200);
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////// DECLINE //////////////////////////////////////////////// */
    private function decline($parent)
    {
        globalfunc::checkMethodType($parent, array('POST'));
        globalfunc::checkParamCount($parent, 2);
        globalfunc::checkAuthrozation($parent, array('type' => 'user'));

        $parent->response(array('response' => 'success', 'data' => $data_arr, 'message' => $this->page_data['success1']), 200);
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////// ABSENT //////////////////////////////////////////////// */
    private function absent($parent)
    {
        globalfunc::checkMethodType($parent, array('POST'));
        globalfunc::checkParamCount($parent, 2);
        globalfunc::checkAuthrozation($parent, array('type' => 'user'));

        $parent->response(array('response' => 'success', 'data' => $data_arr, 'message' => $this->page_data['success1']), 200);
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////// CLOSURE //////////////////////////////////////////////// */
    private function closure($parent)
    {
        globalfunc::checkMethodType($parent, array('POST'));
        globalfunc::checkParamCount($parent, 2);
        globalfunc::checkAuthrozation($parent, array('type' => 'user'));

        $parent->response(array('response' => 'success', 'data' => $data_arr, 'message' => $this->page_data['success1']), 200);
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////// MANUAL_ATTENDANCE //////////////////////////////////////////////// */
    private function manual_attendance($parent)
    {
        globalfunc::checkMethodType($parent, array('POST'));
        globalfunc::checkParamCount($parent, 2);
        globalfunc::checkAuthrozation($parent, array('type' => 'user'));

        $parent->response(array('response' => 'success', 'data' => $data_arr, 'message' => $this->page_data['success1']), 200);
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////// VARIOUS //////////////////////////////////////////////// */
    private function various($parent)
    {
        globalfunc::checkMethodType($parent, array('POST'));
        globalfunc::checkParamCount($parent, 2);
        globalfunc::checkAuthrozation($parent, array('type' => 'user'));

        $parent->response(array('response' => 'success', 'data' => $data_arr, 'message' => $this->page_data['success1']), 200);
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */

}