<?php

/****************************
 * File :  projectfunctions.php
 * Author : Cygnusa
 * @2018
 *
 *    Project functions
 *
 ***************************/

class projectfunc
{
    /* /////////////////////////////////// DEVELOPER ERROR /////////////////////////////////// */
    public static function callErrorMsg($parent, $data)
    {
        if (!isset($data)) {
            $parent->response(array('response' => 'failed', 'message' => 'PHP UNDEFINED ERROR'), 500);
        }

        if (!isset($data['message']) || $data['message'] == '' || $data['message'] == null) {
            $parent->response(array('response' => 'failed', 'message' => 'PHP UNDEFINED ERROR'), 500);
        }

        $parent->response(array('response' => 'failed', 'message' => $data['message']), 500);
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// SUPPLIER STATUS /////////////////////////////////// */
    public static function checkUserPasswordExpiry($parent, $userid = 0)
    {
//        if(isset($parent->current_user_id)&& isset($parent->current_user_data->last_password_change))
        //        {
        //            //DATA FROM current_user_data
        //            $is_password_expiry='0';
        //            if($parent->current_user_data->last_password_change!='0000-00-00 00:00:00')
        //            {
        //
        //                $password_exipry_date=globalfunc::generateExpiryDate($parent,PASSWORD_EXPIRY_DAY);
        //               //$password_exipry_date."<".$parent->current_user_data->last_password_change." |";
        //                $is_password_expiry=(strtotime( $password_exipry_date)> strtotime($parent->current_user_data->last_password_change))?"1":"0";
        //                $parent->current_user_data->is_password_expiry= $is_password_expiry;
        //            }
        //        }else if(isset($parent->current_user_id))
        //        {
        //            //DATA FROM current_user_data not available
        //            $is_password_expiry='0';
        //            $parent->current_user_data->is_password_expiry=$is_password_expiry;
        //            $user_qry_str="SELECT * FROM ".TABLE_USER." WHERE is_deleted='0' AND status='1' AND userid='".$parent->current_user_id."' LIMIT 1";
        //            $user_arr=$parent->getQueryValue($user_qry_str);
        //
        //            if(count($user_arr)>0)
        //            {
        //                if($user_arr[0]->last_password_change!='0000-00-00 00:00:00')
        //                {
        //                    $password_exipry_date=globalfunc::generateExpiryDate($parent,PASSWORD_EXPIRY_DAY);
        //                    $is_password_expiry=(strtotime($password_exipry_date)> strtotime($user_arr[0]->last_password_change))?"1":"0";
        //                    $parent->current_user_data->is_password_expiry= $is_password_expiry;
        //                }
        //            }
        //
        //        }else if($userid!='0')
        //        {
        //            $is_password_expiry='0';
        //            //DATA FROM userid
        //            $user_qry_str="SELECT * FROM ".TABLE_USER." WHERE is_deleted='0' AND status='1' AND userid='".$userid."'  LIMIT 1";
        //            $user_arr=$parent->getQueryValue($user_qry_str);
        //
        //            if(count($user_arr)>0)
        //            {
        //                if($user_arr[0]->last_password_change!='0000-00-00 00:00:00')
        //                {
        //                    $password_exipry_date=globalfunc::generateExpiryDate($parent,PASSWORD_EXPIRY_DAY);
        //                    $is_password_expiry=(strtotime($password_exipry_date)> strtotime($user_arr[0]->last_password_change))?"1":"0";
        //                    $parent->current_user_data->is_password_expiry= $is_password_expiry;
        //                }
        //            }
        //        }
        return '0';
        //return $is_password_expiry;
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// CONTENT STATUS /////////////////////////////////// */
    public static function getContentStatus($parent, $contentid, $userid = 0)
    {
        if ($userid == 0 && $parent->current_user_id) {
            $userid = $parent->current_user_id;
        }

        $status = '0'; //0-Not Started, 1-In progress, 2-Completed
        if (!isset($contentid)) {
            self::callErrorMsg($parent, array('message' => 'projectfunctions(getContentStatus):REQUIRED contentid'));
        }

        //GET COUNT OF USER TRAINING TOPIC CONTENT STATUS
        $get_user_content_str = "SELECT * FROM " . TABLE_USER_COMPLETE_CONTENT . " WHERE contentid='" . $contentid . "'  AND userid='" . $userid . "' AND status>0 LIMIT 1";
        $get_user_content_arr = $parent->getQueryValue($get_user_content_str);

        if (count($get_user_content_arr) > 0) {
            $status = (string) $get_user_content_arr[0]->status;
        }
        switch ($status) {
            case '0':$status_str = 'Not Started';
                break;
            case '1':$status_str = 'In progress';
                break;
            case '2':$status_str = 'Completed';
                break;
            default:$status_str = 'undefined';
                break;
        }

        return array('status' => $status, 'status_str' => $status_str);
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// PDF CROS ISSUE FIX /////////////////////////////////// */
    public static function pdfUrlStr($parent, $s3_url_str)
    {
        $s3_url_arr = @explode('?', $s3_url_str);
        $s3_split_url_str = @explode('amazonaws.com/', $s3_url_arr[0]);
        $encode_file_url = @base64_encode($s3_split_url_str[1]);

        return SITE_URL . "_api/" . $parent->device_type . "/" . $parent->lang . "/pdfviewer/" . $encode_file_url;
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// USER LEARNING LEVEL  /////////////////////////////////// */
    public static function userLearningLevel($parent, $userid = 0)
    {
        //0-Not Started Grey, 1-Current Orange ,2 failed red,3 Completed Green
        //$lvl_arr=array('basic'=>'10','bronze'=>'3','silver'=>'1','gold'=>'1');
        if ($userid == 0 && $parent->current_user_id) {
            $userid = $parent->current_user_id;
        }
        $user_learnpath_str = "SELECT COALESCE(a.status,0) as basic,COALESCE(b.status,0) as bronze,COALESCE(c.status,0) as silver,COALESCE(d.status,0) as gold FROM
(SELECT COUNT(l.lvl_id) as c,l.lvl_id,l.status  FROM " . TABLE_USER_LEARNING . " l RIGHT JOIN " . TABLE_USER . " u ON l.smpin=u.smpin WHERE u.userid='" . $userid . "' AND l.lvl_id=1) as a,
(SELECT COUNT(l.lvl_id) as c,l.lvl_id,l.status  FROM " . TABLE_USER_LEARNING . " l RIGHT JOIN " . TABLE_USER . " u ON l.smpin=u.smpin WHERE u.userid='" . $userid . "' AND l.lvl_id=2) as b,
(SELECT COUNT(l.lvl_id) as c,l.lvl_id,l.status  FROM " . TABLE_USER_LEARNING . " l RIGHT JOIN " . TABLE_USER . " u ON l.smpin=u.smpin WHERE u.userid='" . $userid . "' AND l.lvl_id=4) as c,
(SELECT COUNT(l.lvl_id) as c,l.lvl_id,l.status  FROM " . TABLE_USER_LEARNING . " l RIGHT JOIN " . TABLE_USER . " u ON l.smpin=u.smpin WHERE u.userid='" . $userid . "' AND l.lvl_id=6) as d";
        $user_learnpath_arr = $parent->getQueryValue($user_learnpath_str);

        if (count($user_learnpath_str) > 0) {

            $new_learnpath_str = "SELECT  COALESCE(c.status,0) as silver,COALESCE(d.status,0) as gold FROM
(SELECT COUNT(l.lvl_id) as c,l.lvl_id,l.status  FROM " . TABLE_USER_LEARNING . " l RIGHT JOIN " . TABLE_USER . " u ON l.smpin=u.smpin WHERE u.userid='" . $userid . "' AND l.lvl_id=3) as c,
(SELECT COUNT(l.lvl_id) as c,l.lvl_id,l.status  FROM " . TABLE_USER_LEARNING . " l RIGHT JOIN " . TABLE_USER . " u ON l.smpin=u.smpin WHERE u.userid='" . $userid . "' AND l.lvl_id=5) as d";
            $new_learnpath_arr = $parent->getQueryValue($new_learnpath_str);
            if ($user_learnpath_arr[0]->silver == 0 || $user_learnpath_arr[0]->gold == 0) {
                //SILVER 1 COND
                if (count($new_learnpath_arr) > 0 && $new_learnpath_arr[0]->silver > 0) {
                    switch ($new_learnpath_arr[0]->silver) {
                        case '2':$user_learnpath_arr[0]->silver = 6;
                            break;
                        case '3':$user_learnpath_arr[0]->silver = 7;
                            break;
                    }
                }
                //GOLD 1 COND
                if (count($new_learnpath_arr) > 0 && $new_learnpath_arr[0]->gold > 0) {
                    switch ($new_learnpath_arr[0]->gold) {
                        case '2':$user_learnpath_arr[0]->gold = 6;
                            break;
                        case '3':$user_learnpath_arr[0]->gold = 7;
                            break;
                    }
                }
            }
        }

        return (array) $user_learnpath_arr[0];
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// USER LEVEL ID /////////////////////////////////// */
    public static function getUserLvlId($parent, $userid = 0)
    {
        /*
        1 Basic
        2 Bronze
        3 Silver
        4 Gold
         */

        $lvl_id = '0';
        //GET USER ID
        if ($userid == 0 && $parent->current_user_id) {
            $userid = $parent->current_user_id;
        }

        $user_learn_str = "SELECT l.*  FROM  " . TABLE_USER_LEARNING . " l JOIN " . TABLE_USER . " u ON l.smpin=u.smpin WHERE u.userid='" . $userid . "' AND l.current_status='1' ORDER BY lvl_id DESC LIMIT 1";
        $user_learn_arr = $parent->getQueryValue($user_learn_str);
        if (count($user_learn_arr) > 0) {
            $lvl_id = (count($user_learn_arr) > 0) ? $user_learn_arr[0]->lvl_id : 0;
        }
        return $lvl_id;
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// USER LEVEL ID /////////////////////////////////// */
    public static function getUserAssessmentCond($parent, $userid = 0)
    {
        $user_cond = array();
        if ($userid == 0 && $parent->current_user_id) {
            $assess_userid = $parent->current_user_id;
        } else {
            $assess_userid = $userid;
        }
        $userdata = self::getuserdata($parent, $assess_userid);

        //$user_cond[]=" ((atype=0) OR ( atype='1'  AND assessmentid IN (SELECT assessmentid FROM ".TABLE_TRAINING_ASSESSMENT." WHERE userid='".$assess_userid."' ))) AND (enddate>='".$userdata->created_on."' OR  enddate='0000-00-00 00:00:00')";  //atype 0 Normal,1 Level Based
        $user_cond[] = " ((atype=0) OR ( atype='1'  AND (assessmentid IN (SELECT assessmentid FROM " . TABLE_TRAINING_ASSESSMENT . " WHERE userid='" . $assess_userid . "' ) OR assessmentid IN (SELECT assessmentid FROM " . TABLE_TRAINING_SURVEY . " WHERE userid='" . $assess_userid . "' ) )  )) AND (enddate>='" . $userdata->created_on . "' OR  enddate='0000-00-00 00:00:00')"; //atype 0 Normal,1 Level Based

        if ($userdata->role > 7 && $userdata->role != 14 && $userdata->role != 16) {
            if ($userdata->zone) {
                $user_cond[] = " (FIND_IN_SET('" . $userdata->zone . "',filter_zone) OR filter_zone='')";
            }
            if ($userdata->city) {
                $user_cond[] = " (FIND_IN_SET('" . $userdata->city . "',filter_region) OR filter_region='')";
            }
            if ($userdata->dealer_code) {
                $user_cond[] = " (FIND_IN_SET('" . $userdata->dealer_code . "',filter_dealer) OR filter_dealer='')";
            }
        }
//        if($userid )
        //        {
        ////            if($userdata->role)
        ////            {
        ////                $user_cond[]=" (FIND_IN_SET('".$userdata->role."',filter_role) OR filter_role='')";
        ////            }
        //            if($userdata->zone)
        //            {
        //                $user_cond[]=" (FIND_IN_SET('".$userdata->zone."',filter_zone) OR filter_zone='')";
        //            }
        //            if($userdata->city)
        //            {
        //                $user_cond[]=" (FIND_IN_SET('".$userdata->city."',filter_region) OR filter_region='')";
        //            }
        //            if($userdata->dealer_code)
        //            {
        //                $user_cond[]=" (FIND_IN_SET('".$userdata->dealer_code."',filter_dealer) OR filter_dealer='')";
        //            }
        //
        //        }else
        //        if($userid==0 && $parent->current_user_id)
        //        {
        //            //$userdata=self::getuserdata($parent,$parent->current_user_id);
        ////            if($userdata->role)
        ////            {
        ////                $user_cond[]=" (filter_role LIKE '%".$userdata->role."%' OR filter_role='')";
        ////            }
        //            if($userdata->zone)
        //            {
        //                $user_cond[]=" (filter_zone  LIKE '%".$userdata->zone."%' OR filter_zone='')";
        //            }
        //            if($userdata->city)
        //            {
        //                $user_cond[]=" (filter_region  LIKE '%".$userdata->city."%' OR filter_region='')";
        //            }
        //            if($userdata->dealer_code)
        //            {
        //                $user_cond[]=" (filter_dealer  LIKE '%".$userdata->dealer_code."%'  OR filter_dealer='')";
        //            }
        //        }
        //print_r($user_cond);
        return $user_cond;
        //return '';
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// CONTENT STATUS /////////////////////////////////// */
    public static function getUserfiltervalue($parent, $filterdata, $userid = 0)
    {
        // role,zone,city,dealer_code
        $field_arr = array('role', 'zone', 'city', 'dealer_code');
        $filter_str = '';

        if ($userid == 0 && $parent->current_user_id) {
            $userid = $parent->current_user_id;
        }

        if ($userid && is_array($filterdata)) {

            $query_user = $parent->getQueryValue("SELECT * FROM " . TABLE_USER . "  WHERE  userid='" . $userid . "' LIMIT 1");
            if (count($query_user) > 0) {
                $emp_user = $parent->getQueryValue("SELECT * FROM " . DMS_DEALER . "  WHERE  dealer_code='" . $query_user[0]->dealer_code . "' LIMIT 1");
                foreach ($field_arr as $res_field) {
                    if (in_array($res_field, $filterdata)) {
                        switch ($res_field) {
                            case 'role':$filter_str .= " AND (filter_role='' OR FIND_IN_SET('" . $query_user[0]->role . "',filter_role))";
                                break;
                            case 'zone':$filter_str .= " AND (filter_zone='' OR FIND_IN_SET('" . $emp_user[0]->zone . "',filter_zone))";
                                break;
                            case 'city':$filter_str .= " AND (filter_region='' OR FIND_IN_SET('" . $emp_user[0]->city . "',filter_region))";
                                break;
                            case 'dealer_code':$filter_str .= " AND(filter_dealer='' OR FIND_IN_SET('" . $query_user[0]->dealer_code . "',filter_dealer))";
                                break;
                        }
                    }

                }

            }
        }
        return $filter_str;
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// USER ROLE ID  /////////////////////////////////// */
    public static function getUserRoleId($parent, $rolename)
    {
        $role_id = '0';
        if ($rolename != '') {
            $query_role = $parent->getQueryValue("SELECT id FROM " . TABLE_ROLE . "  WHERE  rolename='" . trim($rolename) . "' LIMIT 1");
            if (count($query_role) > 0) {$role_id = $query_role[0]->id;} else {self::callErrorMsg($parent, array('message' => 'Invalid Role please contact support team.'));}
        }
        return $role_id;
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////// USER DATA //////////////////////////////////////////////// */
    public static function getuserdata($parent, $userid)
    {
        if (!isset($userid)) {
            self::callErrorMsg($parent, array('message' => 'projectfunctions(getuserdata):REQUIRED userid'));
        }

        $query_getuserarr = $parent->selectQuery(TABLE_USER, " `userid`, `smpin`, `emp_code`,   `fname`, `lname`, `emailid`, `mobileno`, `role`,  `dealer_code`, `address_1`, `address_2`, `address_3`, `valid_token`, `validate_time`, `profile_photo`, `dob`, `doj`,segment, `country`,   `language`, `is_married`, `marriage_date`,`mpin`,epin , `last_password_change`,  `status`,  `is_first_login`, `is_profile_updated`, `created_on`, `modified_on` ", "userid='" . $userid . "' AND is_deleted='0' ", "1");
        if ($query_getuserarr['nr'] <= 0) {
            $parent->response(array('response' => 'failed', 'message' => "User not found"), 406);
        }

        $query_getuserarr['result'][0]->username = ($query_getuserarr['result'][0]->role < 8 || $query_getuserarr['result'][0]->role == '14' || $query_getuserarr['result'][0]->role == '16') ? $query_getuserarr['result'][0]->emp_code : $query_getuserarr['result'][0]->smpin;
        $query_getuserarr['result'][0]->dob_time = ($query_getuserarr['result'][0]->dob == '0000-00-00') ? "0000-00-00 00:00:00" : $query_getuserarr['result'][0]->dob . " 00:00:00";
        $query_getuserarr['result'][0]->doj_time = ($query_getuserarr['result'][0]->doj == '0000-00-00') ? "0000-00-00 00:00:00" : $query_getuserarr['result'][0]->doj . " 00:00:00";
        $query_getuserarr['result'][0]->marriage_date_time = ($query_getuserarr['result'][0]->marriage_date == '0000-00-00') ? "0000-00-00 00:00:00" : $query_getuserarr['result'][0]->marriage_date . " 00:00:00";

        if ($query_getuserarr['result'][0]->profile_photo != '') {
            //CONNECT S3
            $s3 = awsfileserver::connectS3();
            $query_getuserarr['result'][0]->profile_photo = awsfileserver::getS3FileUrl($s3, S3_BUCKET, S3_PROFILE_IMAGE_URL . $query_getuserarr['result'][0]->profile_photo);
        } else {
            $query_getuserarr['result'][0]->profile_photo = USER_NOIMAGE_URL;
        }

        //GET ROLE
        $query_rolearr = $parent->selectQuery(TABLE_ROLE, "*", "id='" . $query_getuserarr['result'][0]->role . "' ", "1");
        $query_getuserarr['result'][0]->rolename = ($query_rolearr['nr'] > 0) ? $query_rolearr['result'][0]->rolename : '';

        //GET DELAER
        $query_getuserarr['result'][0]->dealer = new stdClass();
        $query_getdealerarr = $parent->selectQuery(DMS_DEALER, "`dealer_code`, `dealer_name`, `city`, `state`, `zone`, `country`,address,email, `phone`, `zm_name`, `rm_name`, `am_name`, `zm_email`, `rm_email`, `am_email`, `zm_mobile`, `rm_mobile`, `am_mobile`,is_enquirymodule", "dealer_code='" . $query_getuserarr['result'][0]->dealer_code . "' ", "1");
        $query_getuserarr['result'][0]->dealer = ($query_getdealerarr['nr'] > 0) ? $query_getdealerarr['result'][0] : new stdClass();
        $query_getuserarr['result'][0]->is_enquirymodule = ($query_getdealerarr['nr'] > 0) ? $query_getdealerarr['result'][0]->is_enquirymodule : 0;

        $query_getuserarr['result'][0]->country = 'India';
        if ($query_getuserarr['result'][0]->role == '5') {
            $query_dataarr = $parent->selectQuery(DMS_HOUSER, "*", "emp_code='" . $query_getuserarr['result'][0]->emp_code . "' ", "1");
            $query_getuserarr['result'][0]->zone = ($query_dataarr['nr'] > 0) ? $query_dataarr['result'][0]->zone : '';
        } else {
            $query_getuserarr['result'][0]->zone = ($query_getdealerarr['nr'] > 0) ? $query_getdealerarr['result'][0]->zone : '';
        }
        $query_getuserarr['result'][0]->state = ($query_getdealerarr['nr'] > 0) ? $query_getdealerarr['result'][0]->state : '';
        $query_getuserarr['result'][0]->city = ($query_getdealerarr['nr'] > 0) ? $query_getdealerarr['result'][0]->city : '';

        $query_getuserarr['result'][0]->is_password_expired = self::checkUserPasswordExpiry($parent, $userid);

        if ($query_getuserarr['result'][0]->role > '7' && $query_getuserarr['result'][0]->role != '14' && $query_getuserarr['result'][0]->role != '16') {
            $query_getuserarr['result'][0]->is_redeem_point = (self::checkValidReedem($parent, $userid)) ? "1" : "0";
            $query_getuserarr['result'][0]->wallet_amount = self::userWalletamount($parent, $query_getuserarr['result'][0]->smpin);
        } else {
            $query_getuserarr['result'][0]->is_redeem_point = '0';
            $query_getuserarr['result'][0]->wallet_amount = '0';
        }

        return $query_getuserarr['result'][0];
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// USER SEGEMENT  /////////////////////////////////// */
    public static function userSegment($parent, $smpin)
    {
        $segment = '0';
        if ($smpin) {
            $query_arr = $parent->getQueryValue("SELECT segment FROM " . DMS_EMPLOYEE . "  WHERE  smpin='" . $smpin . "' LIMIT 1");
            if (!count($query_arr) > 0) {
                $parent->response(array('response' => 'failed', 'message' => 'SMPIN NOT FOUND'), 406);
            }

            $segment = $query_arr[0]->segment;
        } else {
            $parent->response(array('response' => 'failed', 'message' => 'SMPIN NOT FOUND'), 406);
        }

        return $segment;
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// USER SALE TARGET  /////////////////////////////////// */
    public static function userSalesTarget($parent, $userid, $model = '', $year = '', $month = '')
    {
        $user_target = '0';
        if ($userid) {
            $userdata = self::getuserdata($parent, $userid);
            $qry_cond = '';

            if ($model != '') {
                $qry_cond .= " AND model='" . $model . "'";
            }

            if ($month != '' && $year != '') {
                $qry_cond .= " AND sale_month>='" . date('Y-m-d', strtotime($year . "-" . $month . "-01")) . "' AND sale_month<='" . date('Y-m-t', strtotime($year . "-" . $month . "-01")) . "'";

            } else if ($year != '') {
                $qry_cond .= " AND sale_month>='" . $year . "-01-01' AND sale_month<='" . $year . "-12-31'";
            }

            $query_target = $parent->getQueryValue("SELECT COALESCE(SUM(target),0) as total_target FROM " . TABLE_USER_SALETARGET . "  WHERE  smpin='" . $userdata->smpin . "' " . $qry_cond . " LIMIT 1");
            if (count($query_target) > 0) {
                $user_target = (string) $query_target[0]->total_target;
            }

        }

        return $user_target;
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// SMPIN USER SALE TARGET  /////////////////////////////////// */
    public static function userSmpinSalesTarget($parent, $smpin, $model = '', $year = '', $month = '')
    {
        $user_target = '0';
        if ($smpin) {

            $qry_cond = '';
            if ($model != '') {
                $qry_cond .= " AND model='" . $model . "'";
            }

            if ($month != '' && $year != '') {
                $qry_cond .= " AND sale_month>='" . date('Y-m-d', strtotime($year . "-" . $month . "-01")) . "' AND sale_month<='" . date('Y-m-t', strtotime($year . "-" . $month . "-01")) . "'";

            } else if ($year != '') {
                $qry_cond .= " AND sale_month>='" . $year . "-01-01' AND sale_month<='" . $year . "-12-31'";
            }

            $query_target = $parent->getQueryValue("SELECT COALESCE(SUM(target),0) as total_target FROM " . TABLE_USER_SALETARGET . "  WHERE  smpin='" . $smpin . "' " . $qry_cond . " LIMIT 1");
            if (count($query_target) > 0) {
                $user_target = (string) $query_target[0]->total_target;
            }

        }
        return $user_target;
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// USER SALE ACHIEVED  /////////////////////////////////// */
    public static function userSalesAchieved($parent, $userid, $model = '', $year = '', $month = '')
    {
        $user_sales = '0';
        if ($userid) {
            $userdata = self::getuserdata($parent, $userid);
            $qry_cond = '';
            if ($model != '') {
                $qry_cond .= " AND model='" . $model . "'";
            }

            if ($month != '' && $year != '') {
                $qry_cond .= " AND sale_date>='" . date('Y-m-d', strtotime($year . "-" . $month . "-01")) . "' AND sale_date<='" . date('Y-m-t', strtotime($year . "-" . $month . "-01")) . "'";

            } else if ($year != '') {
                $qry_cond .= " AND sale_date>='" . $year . "-01-01' AND sale_date<='" . $year . "-12-31'";
            }

            $query_user_sale = $parent->getQueryValue("SELECT SUM(sales_count) as total_sales FROM " . DMS_EMPLOYEE_SALES . "  WHERE smpin='" . $userdata->smpin . "' " . $qry_cond . " LIMIT 1");
            if (count($query_user_sale) > 0) {
                $user_sales = ($query_user_sale[0]->total_sales) ? (string) $query_user_sale[0]->total_sales : '0';
            }

        }
        return $user_sales;
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// SMPIN USER SALE ACHIEVED  /////////////////////////////////// */
    public static function userSmpinSalesAchieved($parent, $smpin, $model = '', $year = '', $month = '')
    {
        $user_sales = '0';
        if ($smpin) {
            $qry_cond = '';
            if ($model != '') {
                $qry_cond .= " AND model='" . $model . "'";
            }

            if ($month != '' && $year != '') {
                $qry_cond .= " AND sale_date>='" . date('Y-m-d', strtotime($year . "-" . $month . "-01")) . "' AND sale_date<='" . date('Y-m-t', strtotime($year . "-" . $month . "-01")) . "'";

            } else if ($year != '') {
                $qry_cond .= " AND sale_date>='" . $year . "-01-01' AND sale_date<='" . $year . "-12-31'";
            }

            $query_user_sale = $parent->getQueryValue("SELECT SUM(sales_count) as total_sales FROM " . DMS_EMPLOYEE_SALES . "  WHERE smpin='" . $smpin . "' " . $qry_cond . " LIMIT 1");
            if (count($query_user_sale) > 0) {
                $user_sales = ($query_user_sale[0]->total_sales) ? (string) $query_user_sale[0]->total_sales : '0';
            }

        }
        return $user_sales;
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// USER ASSESSMENT VALID  /////////////////////////////////// */
    public static function userAssessmentValid($parent, $userid, $assessmentid)
    {

        $user_cond_arr = self::getUserAssessmentCond($parent, $userid);
        $user_cond = " AND " . @implode(' AND ', $user_cond_arr);

//        if($parent->current_user_role>7)
        //        {
        //            $user_cond.=($parent->current_user_role>7 && $parent->current_user_role<14)?" AND a.user_type=0 ":" AND a.user_type=1 ";
        //        }
        switch ($parent->current_user_role) {
            //NORMAL DSE
            case '8':
            case '9':
            case '10':
            case '11':
            case '12':
            case '13':
                $user_cond .= " AND a.user_type=0 ";
                break;
            //International Bike Sales Executive
            case '15':
                $user_cond .= " AND a.user_type=1 ";
                break;
            //HO Manager
            case '3':
                $user_cond .= " AND a.user_type=2 ";
                break;
            //Zonal Manager
            case '4':
                $user_cond .= " AND a.user_type=3 ";
                break;
            //Trainer
            case '5':
                $user_cond .= " AND a.user_type=4 ";
                break;
            //Regional Manager
            case '6':
                $user_cond .= " AND a.user_type=5 ";
                break;
            //Area Manager
            case '7':
                $user_cond .= " AND a.user_type=6 ";
                break;
            //Dealer
            case '14':
                $user_cond .= " AND a.user_type=7 ";
                break;
            case '16':
                $user_cond .= " AND a.user_type=8 ";
                break;
        }
        //NEW SEGMENT CONDITION
        if ($parent->current_user_role > 7 && $parent->current_user_role < 14) {
            $user_cond .= " AND (a.segment='0' OR a.segment='" . $parent->current_user_data->segment . "')";
        }
        $assessment_str = "SELECT a.*  FROM  " . TABLE_ASSESSMENT . " as a WHERE a.status='1' AND a.assettypeid='1' AND a.assessmentid='" . $assessmentid . "'  AND a.publisheddate<='" . NOW . "' " . $user_cond . " LIMIT 1";
        $assessment_arr = $parent->getQueryValue($assessment_str);

        if (count($assessment_arr) > 0) {
            if ($assessment_arr[0]->atype == '0') {
                //EXPIRED CHECK
                if (!(strtotime($assessment_arr[0]->enddate) >= strtotime(CUR_DATE . " 00:00:00") && $assessment_arr[0]->enddate != '0000-00-00 00:00:00')) {
                    $parent->response(array('response' => 'failed', 'message' => 'Assessment is expired'), 406);
                }

                //PUBLISHED CHECK
                if (!(strtotime($assessment_arr[0]->publisheddate) < strtotime(NOW))) {
                    $parent->response(array('response' => 'failed', 'message' => $msg["error4"]), 406);
                }

            }
        } else {
            $parent->response(array('response' => 'failed', 'message' => 'Assessment is not available'), 406);
        }

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

    /* /////////////////////////////////// USER SURVEY VALID  /////////////////////////////////// */
    public static function userSurveyValid($parent, $userid, $assessmentid)
    {

        $user_cond_arr = self::getUserAssessmentCond($parent, $userid);
        $user_cond = " AND " . @implode(' AND ', $user_cond_arr);

        if ($parent->current_user_role > 7) {
            $user_cond .= ($parent->current_user_role > 7 && $parent->current_user_role < 14) ? " AND a.user_type=0 " : " AND a.user_type=1 ";
        }

        if ($parent->current_user_role > 7 && $parent->current_user_role < 14) {
            $user_cond .= " AND (a.segment='0' OR a.segment='" . $parent->current_user_data->segment . "')";
        }
        $assessment_str = "SELECT a.*  FROM  " . TABLE_ASSESSMENT . " as a WHERE a.status='1' AND a.assettypeid='2' AND a.assessmentid='" . $assessmentid . "'  AND a.publisheddate<='" . NOW . "' " . $user_cond;
        $assessment_arr = $parent->getQueryValue($assessment_str);

        if (count($assessment_arr) > 0) {

            //EXPIRED CHECK
            if (!(strtotime($assessment_arr[0]->enddate) >= strtotime(CUR_DATE . " 00:00:00") && $assessment_arr[0]->enddate != '0000-00-00 00:00:00')) {
                $parent->response(array('response' => 'failed', 'message' => 'Feedback is expired'), 406);
            }

            //PUBLISHEDD CHECK
            if (!(strtotime($assessment_arr[0]->publisheddate) < strtotime(NOW))) {
                $parent->response(array('response' => 'failed', 'message' => $msg["error4"]), 406);
            }

        } else {
            $parent->response(array('response' => 'failed', 'message' => 'Assessment is not available'), 406);
        }

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

    /* /////////////////////////////////// GET CONTENT  /////////////////////////////////// */
    public static function getContent($parent, $contentid, $userid = 0)
    {
        if ($userid == 0 && $parent->current_user_id) {
            $userid = $parent->current_user_id;
        }

        $data_arr = array();
        //COMMAN USER FILTER
        $user_filter_str = self::getUserfiltervalue($parent, array('role', 'zone', 'city', 'dealer_code'), $userid);
        $user_cond .= $user_filter_str;

        $getuser_content_arr = $parent->selectQuery(TABLE_ASSET_CONTENT, "*", " status='1' AND is_deleted='0' AND contentid='" . $contentid . "'  " . $user_cond . " ", "1");
        if ($getuser_content_arr['nr']) {
            //CONNECT S3
            $s3 = awsfileserver::connectS3();
            foreach ($getuser_content_arr['result'] as $res) {
                //GET USER STATUS OF CONTENT
                $content_status_arr = self::getContentStatus($parent, $res->contentid);
                $res->status = $content_status_arr['status'];
                $res->status_str = $content_status_arr['status_str'];

                $res->content_files = self::getContentFiles($parent, $s3, $res->contentid);
                $data_arr[] = $res;
            }
        }
        //print_r($data_arr);
        return $data_arr;
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////// TOPIC CONTENT  //////////////////////////////////////////////// */
    public static function getContentFiles($parent, $s3, $contentid)
    {
        $data_arr = array();
        //GET FILES ONLY ONE FILE
        $getuser_contentfiles_arr = $parent->selectQuery(TABLE_ASSET_FILE . " as f JOIN " . TABLE_MASTER_LANGUAGE . " as l ON l.language_id=f.language_id", " f.`language_id`, f.`filepath`,f.filetypeid, f.`thumbpath`, f.`filetypeid`, f.`filetype`, f.`filesize`, f.`filequality`, l.`language_code`, l. `language_title`", " f.contentid='" . $contentid . "'", "");
        if ($getuser_contentfiles_arr['nr']) {
            foreach ($getuser_contentfiles_arr['result'] as $tkey => $res) {
                //S3 FILE URL
                $fileurl = '';
                if ($res->filepath != '') {
                    $url = awsfileserver::getS3FileUrl($s3, S3_BUCKET, S3_TOPICS_URL . $res->filepath);
                    //PDF ISSUE FIX FOR WEB DEVICE
                    $url = (strtolower($parent->device_type) == 'web' && $res->filetypeid == '1') ? projectfunc::pdfUrlStr($parent, $url) : $url;
                    $fileurl = $url;
                }
                $getuser_contentfiles_arr['result'][$tkey]->file_url = $fileurl;
                //S3 THUMBIMAGE URL
                $thumb_fileurl = '';
                if ($res->thumbpath != '') {
                    $thumb_fileurl = awsfileserver::getS3FileUrl($s3, S3_BUCKET, S3_TOPICS_URL . $res->thumbpath);
                }
                $getuser_contentfiles_arr['result'][$tkey]->thumb_fileurl = $thumb_fileurl;
            }

            $data_arr = $getuser_contentfiles_arr['result'];
        }

        return $data_arr;
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// MANAGER LEARNING DASHBOARD  /////////////////////////////////// */
    public static function managerLearningDashboard($parent, $role, $userid = 0)
    {
        //0-Not Started Grey, 1-Current Orange ,2 failed red,3 Completed Green
        $lvl_arr = array('basic' => '0', 'bronze' => '0', 'silver' => '0', 'gold' => '0', 'total' => 0);

        $userdata = self::getuserdata($parent, $userid);
        $user_cond = "";
        switch ($role) {

            case '3': //HO Manager
                $user_cond = "";
                break;
            case '4': //Zonal Manager
                $user_cond = " AND u.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE zm_email='" . $userdata->emailid . "')";
                break;
            case '5': //Trainer
                $get_trainer_str = "SELECT h.* FROM " . DMS_HOUSER . " h JOIN " . TABLE_USER . " u ON u.emp_code=h.emp_code WHERE u.userid='" . $res_user->userid . "' LIMIT 1";
                $trainer_arr = $parent->getQueryValue($get_trainer_str);
                $zone_cond = (count($trainer_arr) > 0) ? " zone='" . $trainer_arr[0]->zone . "'" : " zone='0'";

                $user_cond = " AND u.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE " . $zone_cond . ")";
                break;
            case '6': //Regional Manager
                $user_cond = " AND u.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE rm_email='" . $userdata->emailid . "')";
                break;
            case '7': //Area Manager
                $user_cond = " AND u.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE am_email='" . $userdata->emailid . "')";
                break;
            case '14': //Dealer
                $user_cond = " AND u.dealer_code ='" . $userdata->dealer_code . "'";
                break;

//            case '9': break;
            //            case '10': break;
            //            case '11': break;
            //            case '12': break;
            //            case '13': break;
            default:$parent->response(array('response' => 'failed', 'message' => 'Sorry No access'), 401);
                break;
        }

        /* $user_learnpath_str = "SELECT COALESCE(a.c,0) as basic,COALESCE(b.c,0) as bronze,COALESCE(c.c,0) as silver,COALESCE(d.c,0) as gold  FROM
        (SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM ".TABLE_USER_LEARNING." l  JOIN ".TABLE_USER." u ON l.smpin=u.smpin WHERE u.role>7 AND u.role!=14 AND u.is_deleted=0 AND u.status=1  AND ((l.lvl_id='2' AND l.current_status='1' AND l.status!='3') OR (l.lvl_id='1' AND l.current_status='1' AND l.status='3' )) ".$user_cond.") as a,
        (SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM ".TABLE_USER_LEARNING." l  JOIN ".TABLE_USER." u ON l.smpin=u.smpin WHERE u.role>7 AND u.role!=14 AND u.is_deleted=0 AND u.status=1  AND ((l.lvl_id='3' AND l.current_status='1' AND l.status!='3') OR (l.lvl_id='2' AND l.current_status='1'  AND l.status='3')) ".$user_cond.") as b,
        (SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM ".TABLE_USER_LEARNING." l  JOIN ".TABLE_USER." u ON l.smpin=u.smpin WHERE u.role>7 AND u.role!=14 AND u.is_deleted=0 AND u.status=1  AND ((l.lvl_id='4' AND l.current_status='1' AND l.status!='3') OR (l.lvl_id='3' AND l.current_status='1' AND l.status='3'))".$user_cond.") as c,
        (SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM ".TABLE_USER_LEARNING." l  JOIN ".TABLE_USER." u ON l.smpin=u.smpin WHERE u.role>7 AND u.role!=14 AND u.is_deleted=0 AND u.status=1 AND l.current_status='1' AND l.lvl_id=4  AND l.status='3' ".$user_cond.") as d";
         */
        $user_learnpath_str = "SELECT COALESCE(a.c,0) as basic,COALESCE(b.c,0) as bronze,COALESCE(c.c,0) as silver,COALESCE(d.c,0) as gold  FROM
(SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM " . TABLE_USER_LEARNING . " l  JOIN " . TABLE_USER . " u ON l.smpin=u.smpin WHERE u.role>7 AND u.role!=14 AND u.role!=16 AND u.is_deleted=0 AND u.status=1  AND  l.lvl_id='1' AND l.status='3'  " . $user_cond . ") as a,
(SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM " . TABLE_USER_LEARNING . " l  JOIN " . TABLE_USER . " u ON l.smpin=u.smpin WHERE u.role>7 AND u.role!=14 AND u.role!=16 AND u.is_deleted=0 AND u.status=1  AND  l.lvl_id='2' AND l.status='3'  " . $user_cond . ") as b,
(SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM " . TABLE_USER_LEARNING . " l  JOIN " . TABLE_USER . " u ON l.smpin=u.smpin WHERE u.role>7 AND u.role!=14 AND u.role!=16 AND u.is_deleted=0 AND u.status=1  AND l.lvl_id='3'  AND l.status='3' " . $user_cond . ") as c,
(SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM " . TABLE_USER_LEARNING . " l  JOIN " . TABLE_USER . " u ON l.smpin=u.smpin WHERE u.role>7 AND u.role!=14 AND u.role!=16 AND u.is_deleted=0 AND u.status=1  AND l.lvl_id=4  AND l.status='3' " . $user_cond . ") as d";
        $user_learnpath_arr = $parent->getQueryValue($user_learnpath_str);

        if (count($user_learnpath_arr) > 0) {
            $lvl_arr = $user_learnpath_arr[0];
            $lvl_arr->total = (string) ($lvl_arr->basic + $lvl_arr->bronze + $lvl_arr->silver + $lvl_arr->gold);
        }
        $user_tot_count = $parent->getQueryValue(" SELECT COUNT(u.userid) as ucount  FROM  " . TABLE_USER . " u  WHERE u.role>7 AND u.role!=14 AND u.role!=16 AND   u.is_deleted=0 AND u.status=1  " . $user_cond);
        $lvl_arr->user_total = $user_tot_count[0]->ucount;
        $lvl_arr->qry = $user_learnpath_str;
        return (array) $lvl_arr;

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

    /* /////////////////////////////////// MANAGER ASSESSMENT DASHBOARD  /////////////////////////////////// */
    public static function managerAssessmentDashboard($parent, $role, $userid = 0)
    {
        //0-Not Started Grey, 1-Current Orange ,2 failed red,3 Completed Green
        $assessment_arr = array('published' => '0', 'active' => '0', 'expired' => '0');
        //return $assessment_arr;

        $userdata = self::getuserdata($parent, $userid);
        $user_cond = "";
        switch ($role) {

            case '3': //HO Manager
                $user_cond = "";
                break;
            case '4': //Zonal Manager
                $get_zone_str = "SELECT zone FROM " . DMS_DEALER . "  WHERE zm_email='" . $userdata->emailid . "' GROUP BY zone ";
                $zone_arr = $parent->getQueryValue($get_zone_str);
                if (count($zone_arr) > 0) {
                    foreach ($zone_arr as $res_zone) {
                        $zone_cond_arr[] = " filter_zone LIKE '%" . $res_zone->zone . "%'";
                    }
                    $user_cond .= " AND (filter_zone='' OR " . @implode(" OR ", $zone_cond_arr) . " ) ";
                } else {
                    $user_cond .= " AND (filter_zone='') ";
                }
                break;
            case '5': //Trainer
                $get_trainer_str = "SELECT h.* FROM " . DMS_HOUSER . " h JOIN " . TABLE_USER . " u ON u.emp_code=h.emp_code WHERE u.userid='" . $res_user->userid . "' LIMIT 1";
                $trainer_arr = $parent->getQueryValue($get_trainer_str);
                $user_cond = " AND (filter_zone='' OR filter_zone LIKE '%" . $trainer_arr[0]->zone . "%') ";
                break;
            case '6': //Regional Manager
                $get_city_str = "SELECT city,zone FROM " . DMS_DEALER . "  WHERE rm_email='" . $userdata->emailid . "' GROUP BY city ";
                $city_arr = $parent->getQueryValue($get_city_str);
                if (count($city_arr) > 0) {
                    $zone_arr = array();
                    foreach ($city_arr as $res_city) {
                        if (!in_array($res_city->zone, $zone_arr)) {
                            $zone_arr[] = $res_city->zone;
                        }

                        $city_cond_arr[] = " filter_region LIKE '%" . $res_city->city . "%'";
                    }
                    $user_cond .= " AND ( filter_region='' OR " . @implode(" OR ", $city_cond_arr) . " ) ";

                    if (count($zone_arr) > 0) {
                        foreach ($zone_arr as $zone) {
                            $zone_cond_arr[] = " filter_zone LIKE '%" . $zone . "%'";
                        }
                        $user_cond .= " AND (filter_zone='' OR " . @implode(" OR ", $zone_cond_arr) . " ) ";
                    } else {
                        $user_cond .= " AND (filter_zone='') ";
                    }
                } else {
                    $user_cond .= " AND (filter_region='') ";
                }

                break;
            case '7': //Area Manager
                $get_dealer_str = "SELECT dealer_code,city,zone FROM " . DMS_DEALER . "  WHERE am_email='" . $userdata->emailid . "' GROUP BY dealer_code ";
                $dealer_arr = $parent->getQueryValue($get_dealer_str);
                if (count($dealer_arr) > 0) {
                    $zone_arr = array();
                    $city_arr = array();
                    foreach ($dealer_arr as $res_dealer) {
                        if (!in_array($res_dealer->zone, $zone_arr)) {
                            $zone_arr[] = $res_dealer->zone;
                        }

                        if (!in_array($res_dealer->city, $city_arr)) {
                            $city_arr[] = $res_dealer->city;
                        }

                        $dealer_cond_arr[] = " filter_dealer LIKE '%" . $res_dealer->dealer_code . "%'";
                    }
                    $user_cond .= " AND ( filter_dealer='' OR " . @implode(" OR ", $dealer_cond_arr) . " ) ";

                    if (count($zone_arr) > 0) {
                        foreach ($zone_arr as $zone) {
                            $zone_cond_arr[] = " filter_zone LIKE '%" . $zone . "%'";
                        }
                        $user_cond .= " AND (filter_zone='' OR " . @implode(" OR ", $zone_cond_arr) . " ) ";
                    } else {
                        $user_cond .= " AND (filter_zone='') ";
                    }

                    if (count($city_arr) > 0) {
                        foreach ($city_arr as $city) {
                            $city_cond_arr[] = " filter_region LIKE '%" . $city . "%'";
                        }
                        $user_cond .= " AND (filter_region='' OR " . @implode(" OR ", $city_cond_arr) . " ) ";
                    } else {
                        $user_cond .= " AND (filter_region='') ";
                    }

                } else {
                    $user_cond .= " AND (filter_dealer='') ";
                }
                break;
            case '14': //Dealer
                $get_dealer_str = "SELECT dealer_code,city,zone FROM " . DMS_DEALER . "  WHERE dealer_code='" . $userdata->dealer_code . "' LIMIT 1";
                $dealer_arr = $parent->getQueryValue($get_dealer_str);
                if (count($dealer_arr) > 0) {
                    $zone_arr = array();
                    $city_arr = array();
                    foreach ($dealer_arr as $res_dealer) {
                        if (!in_array($res_dealer->zone, $zone_arr)) {
                            $zone_arr[] = $res_dealer->zone;
                        }

                        if (!in_array($res_dealer->city, $city_arr)) {
                            $city_arr[] = $res_dealer->city;
                        }

                        $dealer_cond_arr[] = " filter_dealer LIKE '%" . $res_dealer->dealer_code . "%'";
                    }
                    $user_cond .= " AND ( filter_dealer='' OR " . @implode(" OR ", $dealer_cond_arr) . " ) ";

                    if (count($zone_arr) > 0) {
                        foreach ($zone_arr as $zone) {
                            $zone_cond_arr[] = " filter_zone LIKE '%" . $zone . "%'";
                        }
                        $user_cond .= " AND (filter_zone='' OR " . @implode(" OR ", $zone_cond_arr) . " ) ";
                    } else {
                        $user_cond .= " AND (filter_zone='') ";
                    }

                    if (count($city_arr) > 0) {
                        foreach ($city_arr as $city) {
                            $city_cond_arr[] = " filter_region LIKE '%" . $city . "%'";
                        }
                        $user_cond .= " AND (filter_region='' OR " . @implode(" OR ", $city_cond_arr) . " ) ";
                    } else {
                        $user_cond .= " AND (filter_region='') ";
                    }

                } else {
                    $user_cond .= " AND (filter_dealer='') ";
                }
                break;

//            case '9': break;
            //            case '10': break;
            //            case '11': break;
            //            case '12': break;
            //            case '13': break;
            default:$parent->response(array('response' => 'failed', 'message' => 'Sorry No access'), 401);
                break;
        }

        $assessment_str = "SELECT COUNT(assessmentid) as acount FROM " . TABLE_ASSESSMENT . " WHERE atype='0' AND status='1' AND assettypeid='1'  AND  publisheddate<='" . NOW . "' " . $user_cond . " LIMIT 1";
        $get_assessment_arr = $parent->getQueryValue($assessment_str);
        if (count($get_assessment_arr) > 0) {
            $expired_assessment_str = "SELECT COUNT(assessmentid) as acount FROM " . TABLE_ASSESSMENT . " WHERE atype='0' AND status='1'  AND  assettypeid='1'  AND  publisheddate<='" . NOW . "' AND enddate>='" . NOW . "' " . $user_cond . " LIMIT 1";
            $expired_assessment_arr = $parent->getQueryValue($expired_assessment_str);

            $active_count = (string) $get_assessment_arr[0]->acount - $expired_assessment_arr[0]->acount;
            $assessment_arr = array('published' => $get_assessment_arr[0]->acount, 'active' => $active_count, 'expired' => $expired_assessment_arr[0]->acount);
        }

        return (array) $assessment_arr;

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

    /* /////////////////////////////////// MANAGER SALES DASHBOARD  /////////////////////////////////// */
    public static function managersalesDashboard($parent, $role, $year, $month, $userid = 0)
    {
        //0-Not Started Grey, 1-Current Orange ,2 failed red,3 Completed Green
        //$sales_arr=array('target'=>'10','achieved'=>'4','month'=>date('M',strtotime('2018-'.$month."-01")),'year'=>$year);
        //return $sales_arr;
        $userdata = self::getuserdata($parent, $userid);
        $user_cond = "";
        switch ($role) {

            case '3': //HO Manager
                $user_cond = "";
                break;
            case '4': //Zonal Manager
                $user_cond = " AND u.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE zm_email='" . $userdata->emailid . "')";
                break;
            case '5': //Trainer
                $get_trainer_str = "SELECT h.* FROM " . DMS_HOUSER . " h JOIN " . TABLE_USER . " u ON u.emp_code=h.emp_code WHERE u.userid='" . $res_user->userid . "' LIMIT 1";
                $trainer_arr = $parent->getQueryValue($get_trainer_str);
                $zone_cond = (count($trainer_arr) > 0) ? " zone='" . $trainer_arr[0]->zone . "'" : " zone='0'";

                $user_cond = " AND u.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE " . $zone_cond . ")";
                break;
            case '6': //Regional Manager
                $user_cond = " AND u.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE rm_email='" . $userdata->emailid . "')";
                break;
            case '7': //Area Manager
                $user_cond = " AND u.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE am_email='" . $userdata->emailid . "')";
                break;
            case '14': //Dealer
                $user_cond = " AND u.dealer_code='" . $userdata->dealer_code . "'";
                break;
//            case '9': break;
            //            case '10': break;
            //            case '11': break;
            //            case '12': break;
            //            case '13': break;
            default:$parent->response(array('response' => 'failed', 'message' => 'Sorry No access'), 401);
                break;
        }

        $user_target_str = "SELECT COALESCE(SUM(t.target),0) as user_target FROM " . TABLE_USER . " u JOIN " . TABLE_USER_SALETARGET . " as t ON u.smpin=t.smpin   WHERE u.status='1' AND u.is_deleted='0' AND t.sale_month>='" . date('Y-m-d', strtotime($year . "-" . $month . "-01")) . "' AND t.sale_month<='" . date('Y-m-t', strtotime($year . "-" . $month . "-01")) . "' " . $user_cond;
        $user_target_arr = $parent->getQueryValue($user_target_str);
        $target = (count($user_target_arr) > 0) ? $user_target_arr[0]->user_target : '0';

        $user_achieve_str = "SELECT COALESCE(SUM(s.sales_count),0) as user_achieve FROM " . TABLE_USER . " u JOIN " . DMS_EMPLOYEE_SALES . " as s ON u.smpin=s.smpin WHERE u.status='1' AND u.is_deleted='0' AND s.sale_date>='" . date('Y-m-d', strtotime($year . "-" . $month . "-01")) . "' AND s.sale_date<='" . date('Y-m-t', strtotime($year . "-" . $month . "-01")) . "' " . $user_cond;
        $user_achieve_arr = $parent->getQueryValue($user_achieve_str);
        $achieved = (count($user_achieve_arr) > 0) ? $user_achieve_arr[0]->user_achieve : '0';

        $sales_arr = array('target' => $user_target_arr[0]->user_target, 'achieved' => $achieved, 'month' => date('M', strtotime('2018-' . $month . "-01")), 'year' => $year);

        return (array) $sales_arr;

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

    /* /////////////////////////////////// SET CELL BORDER  /////////////////////////////////// */
    public static function setCellBorder($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("0A64B5");
            $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);
        }
    }
    /* //////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// SET CELL BORDER  /////////////////////////////////// */
    public static function highlighRows($columns, $objPHPExcel)
    {
        for ($i = 0; $i < count($columns); $i++) {
            $objPHPExcel->getActiveSheet()->getStyle($columns[$i])->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB("FF0000");
        }
    }
    /* //////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// USER COURSE COMPLETE STATUS  /////////////////////////////////// */
    public static function userCourseCompleteStatus($parent, $userid)
    {
        if (!isset($userid)) {
            self::callErrorMsg($parent, array('message' => 'projectfunctions(userCourseCompleteStatus):REQUIRED userid'));
        }

        $user_filter_str = self::getUserfiltervalue($parent, array('role', 'zone', 'city', 'dealer_code'), $userid);
        $user_cond = str_replace("filter_", "c.filter_", $user_filter_str);

        //GET USER ALL CONTENT COUNT
        $get_course_str = "SELECT COUNT(c.`contentid`) as totcontent FROM " . TABLE_ASSET_CONTENT . " as c JOIN " . TABLE_ASSET_TOPIC . " as t ON t.assettopicid=c.assettopicid WHERE t.status='1' AND t.is_deleted='0' AND c.status='1' AND c.is_deleted='0' " . $user_cond;
        $get_course_arr = $parent->getQueryValue($get_course_str);
        $total_content = (count($get_course_arr) > 0) ? $get_course_arr[0]->totcontent : '0';
        //GET USER COMPLETED CONTENT COUNT
        $get_user_completed_str = "SELECT COUNT(c.`contentid`) as totcontent FROM " . TABLE_ASSET_CONTENT . " as c JOIN " . TABLE_ASSET_TOPIC . " as t ON t.assettopicid=c.assettopicid JOIN (SELECT * FROM " . TABLE_USER_COMPLETE_CONTENT . " WHERE userid='" . $userid . "' AND status='2') as a ON a.contentid=c.contentid WHERE t.status='1' AND t.is_deleted='0' AND c.status='1' AND c.is_deleted='0' " . $user_cond;
        $get_user_completed_arr = $parent->getQueryValue($get_user_completed_str);
        $user_content = (count($get_user_completed_arr) > 0) ? $get_user_completed_arr[0]->totcontent : '0';

        return array('completed' => $user_content, 'total' => $total_content);
    }
    /* //////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// USER COURSE COMPLETE STATUS  /////////////////////////////////// */
    public static function userSegmentCourseCompleteStatus($parent, $userid, $segment)
    {
        if (!isset($userid)) {
            self::callErrorMsg($parent, array('message' => 'projectfunctions(userCourseCompleteStatus):REQUIRED userid'));
        }

        $user_filter_str = self::getUserfiltervalue($parent, array('role', 'zone', 'city', 'dealer_code'), $userid);
        $user_cond = str_replace("filter_", "c.filter_", $user_filter_str);

        switch ($segment) {
            case "1":$user_cond .= " AND t.segment IN (0,1)  AND c.segment IN (0,1)";
                break;
            case "2":$user_cond .= " AND t.segment IN (0,2)  AND c.segment IN (0,2)";
                break;
            default:$user_cond .= " AND t.segment IN (0)  AND c.segment IN (0)";
                break;
        }

        //GET USER ALL CONTENT COUNT
        $get_course_str = "SELECT COUNT(c.`contentid`) as totcontent FROM " . TABLE_ASSET_CONTENT . " as c JOIN " . TABLE_ASSET_TOPIC . " as t ON t.assettopicid=c.assettopicid WHERE t.status='1' AND t.is_deleted='0' AND c.status='1' AND c.is_deleted='0' " . $user_cond;
        $get_course_arr = $parent->getQueryValue($get_course_str);
        $total_content = (count($get_course_arr) > 0) ? $get_course_arr[0]->totcontent : '0';
        //GET USER COMPLETED CONTENT COUNT
        $get_user_completed_str = "SELECT COUNT(c.`contentid`) as totcontent FROM " . TABLE_ASSET_CONTENT . " as c JOIN " . TABLE_ASSET_TOPIC . " as t ON t.assettopicid=c.assettopicid JOIN (SELECT * FROM " . TABLE_USER_COMPLETE_CONTENT . " WHERE userid='" . $userid . "' AND status='2') as a ON a.contentid=c.contentid WHERE t.status='1' AND t.is_deleted='0' AND c.status='1' AND c.is_deleted='0' " . $user_cond;
        $get_user_completed_arr = $parent->getQueryValue($get_user_completed_str);
        $user_content = (count($get_user_completed_arr) > 0) ? $get_user_completed_arr[0]->totcontent : '0';

        return array('completed' => $user_content, 'total' => $total_content);
    }
    /* //////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// USER COURSE COMPLETE STATUS  /////////////////////////////////// */
    public static function managerCourseCompleteStatus($parent, $userid, $role)
    {
        if (!isset($userid)) {
            self::callErrorMsg($parent, array('message' => 'projectfunctions(userCourseCompleteStatus):REQUIRED userid'));
        }

//        $user_filter_str=self::getUserfiltervalue($parent,array('role','zone','city','dealer_code'),$userid);
        //        $user_cond=str_replace("filter_","c.filter_",$user_filter_str);

        $user_cond = " AND (FIND_IN_SET('" . $role . "',c.filter_role) OR c.filter_role='')";

        //GET USER ALL CONTENT COUNT
        $get_course_str = "SELECT COUNT(c.`contentid`) as totcontent FROM " . TABLE_ASSET_CONTENT . " as c JOIN " . TABLE_ASSET_TOPIC . " as t ON t.assettopicid=c.assettopicid WHERE t.status='1' AND t.is_deleted='0' AND c.status='1' AND c.is_deleted='0' " . $user_cond;
        $get_course_arr = $parent->getQueryValue($get_course_str);
        $total_content = (count($get_course_arr) > 0) ? $get_course_arr[0]->totcontent : '0';
        //GET USER COMPLETED CONTENT COUNT
        $get_user_completed_str = "SELECT COUNT(c.`contentid`) as totcontent FROM " . TABLE_ASSET_CONTENT . " as c JOIN " . TABLE_ASSET_TOPIC . " as t ON t.assettopicid=c.assettopicid JOIN (SELECT * FROM " . TABLE_USER_COMPLETE_CONTENT . " WHERE userid='" . $userid . "' AND status='2') as a ON a.contentid=c.contentid WHERE t.status='1' AND t.is_deleted='0' AND c.status='1' AND c.is_deleted='0' " . $user_cond;
        $get_user_completed_arr = $parent->getQueryValue($get_user_completed_str);
        $user_content = (count($get_user_completed_arr) > 0) ? $get_user_completed_arr[0]->totcontent : '0';

        return array('completed' => $user_content, 'total' => $total_content);
    }
    /* //////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////// AUTHORIZED ROLE //////////////////////////////////////////////// */
    public static function authorizedRole($parent, $role_arr)
    {
        if (isset($role_arr) && is_array($role_arr)) {

            return (in_array($parent->current_user_role, $role_arr)) ? true : $parent->response(array('response' => 'failed', 'message' => 'Unauthorize'), 401);
        } else {
            self::callErrorMsg($parent, array('message' => 'projectfunctions(authorizedUser):REQUIRED role_arr'));
        }
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////// USER ADD POINTS //////////////////////////////////////////////// */
    public static function userAddPoints($parent, $obj, $userid = 0)
    {
        $status = 0;
        if ($userid == 0 && $parent->current_user_id) {
            if ($parent->current_user_id) {
                $userid = $parent->current_user_id;
                $smpin = $parent->current_smpin;
            } else {
                self::callErrorMsg($parent, array('message' => 'projectfunctions(userAddPoints):REQUIRED userid'));
            }
        } else {
            $user_str = "SELECT *  FROM  " . TABLE_USER . "  WHERE userid='" . $userid . "' AND is_deleted=0 LIMIT 1";
            $user_arr = $parent->getQueryValue($user_str);
            if (count($user_arr) > 0) {
                $smpin = $user_arr[0]->smpin;
            } else {
                self::callErrorMsg($parent, array('message' => 'projectfunctions(userAddPoints):REQUIRED USER NOT FOUND'));
            }
        }

        if (!isset($obj->type)) {
            self::callErrorMsg($parent, array('message' => 'projectfunctions(userAddPoints):REQUIRED obj->type'));
        }

        if (!isset($obj->type_id)) {
            self::callErrorMsg($parent, array('message' => 'projectfunctions(userAddPoints):REQUIRED obj->type_id'));
        }

        if (!isset($obj->points)) {
            self::callErrorMsg($parent, array('message' => 'projectfunctions(userAddPoints):REQUIRED obj->points'));
        }

        $obj->description = (isset($obj->description)) ? $obj->description : "";

        //NEED TO CHECK POINTS ALREADY ADDED
        $points_str = "SELECT *  FROM  " . TABLE_USER_EPOINTS_HISTORY . "  WHERE smpin='" . $smpin . "' AND type='" . $obj->type . "' AND type_id='" . $obj->type_id . "' AND transaction_type='C'";
        $points_arr = $parent->getQueryValue($points_str);
        if (count($points_arr) > 0) {

            //POINTS ALREADY ADDED
        } else {
            $parent->executeQuery("INSERT INTO " . TABLE_USER_EPOINTS_HISTORY . "( `smpin`, `type`, `type_id`, `description`, `points`, `transaction_type`, `created_on`, `modified_on`) VALUES ('" . $smpin . "','" . escape_str($obj->type) . "','" . $obj->type_id . "','" . escape_str($obj->description) . "','" . $obj->points . "' ,'C','" . NOW . "','" . NOW . "')");
            //UPDATE POINT IN USER
            $parent->executeQuery("UPDATE  " . TABLE_USER_EPOINTS . " SET points=points+" . $obj->points . " WHERE smpin='" . $smpin . "' ");
            $status = 1;
        }
        return $status;
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// MANAGER LEARNING DASHBOARD  /////////////////////////////////// */
    public static function newmanagerLearningDashboard($parent, $role, $email, $dealer_code = '')
    {
        //0-Not Started Grey, 1-Current Orange ,2 failed red,3 Completed Green
        $lvl_arr = (object) array('basic' => '0', 'bronze' => '0', 'silver' => '0', 'gold' => '0', 'total' => 0);

        //$userdata=projectfunc::getuserdata($parent,$userid);
        $user_cond = "";
        switch ($role) {

            case '3': //HO Manager
                $user_cond = " AND u.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE is_deleted='0'  AND status='1')";
                break;
            case '4': //Zonal Manager
                $user_cond = " AND u.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE zm_email='" . $email . "' AND is_deleted='0'  AND status='1')";
                break;
            case '5': //Trainer
                $get_trainer_str = "SELECT h.* FROM " . DMS_HOUSER . " h JOIN " . TABLE_USER . " u ON u.emp_code=h.emp_code WHERE u.emailid='" . $email . "' LIMIT 1";
                $trainer_arr = $parent->getQueryValue($get_trainer_str);
                $zone_cond = (count($trainer_arr) > 0) ? " zone='" . $trainer_arr[0]->zone . "'" : " zone='0'";

                $user_cond = " AND u.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE " . $zone_cond . " AND is_deleted='0'  AND status='1')";
                break;
            case '6': //Regional Manager
                $mcond = '';
                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);
                    $manager_cond .= (count($trainer_arr) > 0) ? " zone='" . $trainer_arr[0]->zone . "'" : " zone='0'";
                } else {
                    $zmemail = escape_str($parent->_request["zmemail"]);
                    $mcond = ($zmemail != 'all' && $zmemail != '') ? " AND zm_email='" . $zmemail . "'" : '';
                }
                $user_cond = " AND u.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE rm_email='" . $email . "' AND is_deleted='0'  AND status='1' " . $mcond . " )";
                break;
            case '7': //Area Manager
                $mcond = '';
                if ($parent->current_user_role != '5') {
                    $zmemail = escape_str($parent->_request["zmemail"]);
                    $mcond = ($zmemail != 'all' && $zmemail != '') ? " AND zm_email='" . $zmemail . "'" : '';
                }
                $rmemail = escape_str($parent->_request["rmemail"]);
                $mcond .= ($rmemail != 'all' && $rmemail != '') ? " AND rm_email='" . $rmemail . "'" : '';

                $user_cond = " AND u.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE am_email='" . $email . "' AND is_deleted='0'  AND status='1'  " . $mcond . ")";
                break;
            case '14': //Dealer
                $user_cond = " AND u.dealer_code='" . $parent->current_user_data->dealer_code . "'";
                break;
//            case '9': break;
            //            case '10': break;
            //            case '11': break;
            //            case '12': break;
            //            case '13': break;
            default:$parent->response(array('response' => 'failed', 'message' => 'Sorry No access'), 401);
                break;
        }

        /*
         * $user_learnpath_str = "SELECT COALESCE(a.c,0) as basic,COALESCE(b.c,0) as bronze,COALESCE(c.c,0) as silver,COALESCE(d.c,0) as gold  FROM
        (SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM ".TABLE_USER_LEARNING." l  JOIN ".TABLE_USER." u ON l.smpin=u.smpin WHERE  u.role>7 AND u.role!=14 AND u.is_deleted=0 AND u.status=1 AND l.current_status='1' AND l.lvl_id=1 ".$user_cond.") as a,
        (SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM ".TABLE_USER_LEARNING." l  JOIN ".TABLE_USER." u ON l.smpin=u.smpin WHERE  u.role>7 AND u.role!=14 AND  u.is_deleted=0 AND u.status=1 AND l.current_status='1' AND l.lvl_id=2 ".$user_cond.") as b,
        (SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM ".TABLE_USER_LEARNING." l  JOIN ".TABLE_USER." u ON l.smpin=u.smpin WHERE  u.role>7 AND u.role!=14 AND  u.is_deleted=0 AND u.status=1 AND l.current_status='1' AND l.lvl_id=3 ".$user_cond.") as c,
        (SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM ".TABLE_USER_LEARNING." l  JOIN ".TABLE_USER." u ON l.smpin=u.smpin WHERE   u.role>7 AND u.role!=14 AND  u.is_deleted=0 AND u.status=1 AND l.current_status='1' AND l.lvl_id=4 ".$user_cond.") as d";
         */
        /*
        $user_learnpath_str = "SELECT COALESCE(a.c,0) as basic,COALESCE(b.c,0) as bronze,COALESCE(c.c,0) as silver,COALESCE(d.c,0) as gold  FROM
        (SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM ".TABLE_USER_LEARNING." l  JOIN ".TABLE_USER." u ON l.smpin=u.smpin WHERE u.role>7 AND u.role!=14 AND u.is_deleted=0 AND u.status=1  AND ((l.lvl_id='2' AND l.current_status='1' AND l.status!='3') OR (l.lvl_id='1' AND l.current_status='1' AND l.status='3' )) ".$user_cond.") as a,
        (SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM ".TABLE_USER_LEARNING." l  JOIN ".TABLE_USER." u ON l.smpin=u.smpin WHERE u.role>7 AND u.role!=14 AND u.is_deleted=0 AND u.status=1  AND ((l.lvl_id='3' AND l.current_status='1' AND l.status!='3') OR (l.lvl_id='2' AND l.current_status='1'  AND l.status='3')) ".$user_cond.") as b,
        (SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM ".TABLE_USER_LEARNING." l  JOIN ".TABLE_USER." u ON l.smpin=u.smpin WHERE u.role>7 AND u.role!=14 AND u.is_deleted=0 AND u.status=1  AND ((l.lvl_id='4' AND l.current_status='1' AND l.status!='3') OR (l.lvl_id='3' AND l.current_status='1' AND l.status='3'))".$user_cond.") as c,
        (SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM ".TABLE_USER_LEARNING." l  JOIN ".TABLE_USER." u ON l.smpin=u.smpin WHERE u.role>7 AND u.role!=14 AND u.is_deleted=0 AND u.status=1 AND l.current_status='1' AND l.lvl_id=4  AND l.status='3' ".$user_cond.") as d";
         */
        /*
        $user_learnpath_str = "SELECT COALESCE(a.c,0) as basic,COALESCE(b.c,0) as bronze,COALESCE(c.c,0) as silver,COALESCE(d.c,0) as gold  FROM
        (SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM ".TABLE_USER_LEARNING." l  JOIN ".TABLE_USER." u ON l.smpin=u.smpin WHERE u.role>7 AND u.role!=14 AND u.role!=16 AND u.is_deleted=0 AND u.status=1  AND  l.lvl_id='1'  AND l.status='3' ".$user_cond.") as a,
        (SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM ".TABLE_USER_LEARNING." l  JOIN ".TABLE_USER." u ON l.smpin=u.smpin WHERE u.role>7 AND u.role!=14 AND u.role!=16 AND u.is_deleted=0 AND u.status=1  AND  l.lvl_id='2'   AND l.status='3'  ".$user_cond.") as b,
        (SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM ".TABLE_USER_LEARNING." l  JOIN ".TABLE_USER." u ON l.smpin=u.smpin WHERE u.role>7 AND u.role!=14 AND u.role!=16 AND u.is_deleted=0 AND u.status=1  AND  l.lvl_id='3'  AND l.status='3' ".$user_cond.") as c,
        (SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM ".TABLE_USER_LEARNING." l  JOIN ".TABLE_USER." u ON l.smpin=u.smpin WHERE u.role>7 AND u.role!=14 AND u.role!=16 AND u.is_deleted=0 AND u.status=1 AND l.lvl_id=4  AND l.status='3' ".$user_cond.") as d";
         */
        $user_cond .= " AND e.is_current='1' ";
        //AND u.status=1
        $user_learnpath_str = "SELECT COALESCE(a.c,0) as basic,COALESCE(b.c,0) as bronze,COALESCE(c.c,0) as silver,COALESCE(d.c,0) as gold  FROM
(SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM " . TABLE_USER_LEARNING . " l  JOIN " . TABLE_USER . " u ON l.smpin=u.smpin JOIN " . DMS_EMPLOYEE . " e ON u.smpin=e.smpin WHERE u.role>7 AND u.role!=14 AND u.role!=16 AND u.is_deleted=0   AND  l.lvl_id='1'  AND l.status='3' " . $user_cond . ") as a,
(SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM " . TABLE_USER_LEARNING . " l  JOIN " . TABLE_USER . " u ON l.smpin=u.smpin JOIN " . DMS_EMPLOYEE . " e ON u.smpin=e.smpin WHERE u.role>7 AND u.role!=14 AND u.role!=16 AND u.is_deleted=0  AND  l.lvl_id='2'   AND l.status='3'  " . $user_cond . ") as b,
(SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM " . TABLE_USER_LEARNING . " l  JOIN " . TABLE_USER . " u ON l.smpin=u.smpin JOIN " . DMS_EMPLOYEE . " e ON u.smpin=e.smpin WHERE u.role>7 AND u.role!=14 AND u.role!=16 AND u.is_deleted=0  AND  l.lvl_id='3'  AND l.status='3' " . $user_cond . ") as c,
(SELECT COUNT(l.smpin) as c,l.lvl_id,l.status  FROM " . TABLE_USER_LEARNING . " l  JOIN " . TABLE_USER . " u ON l.smpin=u.smpin JOIN " . DMS_EMPLOYEE . " e ON u.smpin=e.smpin WHERE u.role>7 AND u.role!=14 AND u.role!=16 AND u.is_deleted=0  AND l.lvl_id=4  AND l.status='3' " . $user_cond . ") as d";
        //$parent->executeQuery("INSERT INTO `".TABLE_QUERY_LOG."`(  `query_str`, `query_type`, `created_on`) VALUES ('". escape_str($user_learnpath_str)."', 'dashlear', '".NOW."')");

        $user_learnpath_arr = $parent->getQueryValue($user_learnpath_str);

        if (count($user_learnpath_arr) > 0) {
            $lvl_arr = $user_learnpath_arr[0];
            $lvl_arr->total = (string) ($lvl_arr->basic + $lvl_arr->bronze + $lvl_arr->silver + $lvl_arr->gold);

        }
        $user_tot_count = $parent->getQueryValue(" SELECT COUNT(u.userid) as ucount  FROM " . TABLE_USER . " u JOIN " . DMS_EMPLOYEE . " e ON u.smpin=e.smpin  WHERE u.role>7 AND u.role!=14 AND u.role!=16 AND  u.is_deleted=0  " . $user_cond);
        $lvl_arr->user_total = $user_tot_count[0]->ucount;
        //$lvl_arr->qry= $user_learnpath_str;
        return (array) $lvl_arr;

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

    /* /////////////////////////////////// AREA MANAGER ASSESSMENT DASHBOARD  /////////////////////////////////// */
    public static function newmanagerAssessmentDashboard($parent, $role, $email)
    {
        //0-Not Started Grey, 1-Current Orange ,2 failed red,3 Completed Green
        $assessment_arr = array('published' => '0', 'active' => '0', 'expired' => '0');
        //return $assessment_arr;

        //$userdata=projectfunc::getuserdata($parent,$userid);
        $user_cond = "";
        switch ($role) {

            case '3': //HO Manager
                $user_cond = "";
                break;
            case '4': //Zonal Manager
                $get_zone_str = "SELECT zone FROM " . DMS_DEALER . "  WHERE zm_email='" . $email . "' AND is_deleted='0'  AND status='1'  GROUP BY zone ";
                $zone_arr = $parent->getQueryValue($get_zone_str);
                if (count($zone_arr) > 0) {
                    foreach ($zone_arr as $res_zone) {
                        $zone_cond_arr[] = " filter_zone LIKE '%" . $res_zone->zone . "%'";
                    }
                    $user_cond .= " AND (filter_zone='' OR " . @implode(" OR ", $zone_cond_arr) . " ) ";
                } else {
                    $user_cond .= " AND (filter_zone='') ";
                }
                break;
            case '5': //Trainer
                $get_trainer_str = "SELECT h.* FROM " . DMS_HOUSER . " h JOIN " . TABLE_USER . " u ON u.emp_code=h.emp_code WHERE u.emailid='" . $email . "' LIMIT 1";
                $trainer_arr = $parent->getQueryValue($get_trainer_str);
                $user_cond = " AND (filter_zone='' OR filter_zone LIKE '%" . $trainer_arr[0]->zone . "%') ";
                break;
            case '6': //Regional Manager
                $get_city_str = "SELECT city,zone FROM " . DMS_DEALER . "  WHERE rm_email='" . $email . "' AND is_deleted='0'  AND status='1' GROUP BY city ";
                $city_arr = $parent->getQueryValue($get_city_str);
                if (count($city_arr) > 0) {
                    $zone_arr = array();
                    foreach ($city_arr as $res_city) {
                        if (!in_array($res_city->zone, $zone_arr)) {
                            $zone_arr[] = $res_city->zone;
                        }

                        $city_cond_arr[] = " filter_region LIKE '%" . $res_city->city . "%'";
                    }
                    $user_cond .= " AND ( filter_region='' OR " . @implode(" OR ", $city_cond_arr) . " ) ";

                    if (count($zone_arr) > 0) {
                        foreach ($zone_arr as $zone) {
                            $zone_cond_arr[] = " filter_zone LIKE '%" . $zone . "%'";
                        }
                        $user_cond .= " AND (filter_zone='' OR " . @implode(" OR ", $zone_cond_arr) . " ) ";
                    } else {
                        $user_cond .= " AND (filter_zone='') ";
                    }
                } else {
                    $user_cond .= " AND (filter_region='') ";
                }

                break;
            case '7': //Area Manager
                $get_dealer_str = "SELECT dealer_code,city,zone FROM " . DMS_DEALER . "  WHERE am_email='" . $email . "' AND is_deleted='0'  AND status='1' GROUP BY dealer_code ";
                $dealer_arr = $parent->getQueryValue($get_dealer_str);
                if (count($dealer_arr) > 0) {
                    $zone_arr = array();
                    $city_arr = array();
                    foreach ($dealer_arr as $res_dealer) {
                        if (!in_array($res_dealer->zone, $zone_arr)) {
                            $zone_arr[] = $res_dealer->zone;
                        }

                        if (!in_array($res_dealer->city, $city_arr)) {
                            $city_arr[] = $res_dealer->city;
                        }

                        $dealer_cond_arr[] = " filter_dealer LIKE '%" . $res_dealer->dealer_code . "%'";
                    }
                    $user_cond .= " AND ( filter_dealer='' OR " . @implode(" OR ", $dealer_cond_arr) . " ) ";

                    if (count($zone_arr) > 0) {
                        foreach ($zone_arr as $zone) {
                            $zone_cond_arr[] = " filter_zone LIKE '%" . $zone . "%'";
                        }
                        $user_cond .= " AND (filter_zone='' OR " . @implode(" OR ", $zone_cond_arr) . " ) ";
                    } else {
                        $user_cond .= " AND (filter_zone='') ";
                    }

                    if (count($city_arr) > 0) {
                        foreach ($city_arr as $city) {
                            $city_cond_arr[] = " filter_region LIKE '%" . $city . "%'";
                        }
                        $user_cond .= " AND (filter_region='' OR " . @implode(" OR ", $city_cond_arr) . " ) ";
                    } else {
                        $user_cond .= " AND (filter_region='') ";
                    }

                } else {
                    $user_cond .= " AND (filter_dealer='') ";
                }
                break;
            case '14': //DEALER
                $get_dealer_str = "SELECT dealer_code,city,zone FROM " . DMS_DEALER . "  WHERE   dealer_code='" . $parent->current_user_data->dealer_code . "' AND is_deleted='0'  AND status='1'  LIMIT 1";
                $dealer_arr = $parent->getQueryValue($get_dealer_str);
                if (count($dealer_arr) > 0) {
                    $zone_arr = array();
                    $city_arr = array();
                    foreach ($dealer_arr as $res_dealer) {
                        if (!in_array($res_dealer->zone, $zone_arr)) {
                            $zone_arr[] = $res_dealer->zone;
                        }

                        if (!in_array($res_dealer->city, $city_arr)) {
                            $city_arr[] = $res_dealer->city;
                        }

                        $dealer_cond_arr[] = " filter_dealer LIKE '%" . $res_dealer->dealer_code . "%'";
                    }
                    $user_cond .= " AND ( filter_dealer='' OR " . @implode(" OR ", $dealer_cond_arr) . " ) ";

                    if (count($zone_arr) > 0) {
                        foreach ($zone_arr as $zone) {
                            $zone_cond_arr[] = " filter_zone LIKE '%" . $zone . "%'";
                        }
                        $user_cond .= " AND (filter_zone='' OR " . @implode(" OR ", $zone_cond_arr) . " ) ";
                    } else {
                        $user_cond .= " AND (filter_zone='') ";
                    }

                    if (count($city_arr) > 0) {
                        foreach ($city_arr as $city) {
                            $city_cond_arr[] = " filter_region LIKE '%" . $city . "%'";
                        }
                        $user_cond .= " AND (filter_region='' OR " . @implode(" OR ", $city_cond_arr) . " ) ";
                    } else {
                        $user_cond .= " AND (filter_region='') ";
                    }

                } else {
                    $user_cond .= " AND (filter_dealer='') ";
                }
                break;
//            case '9': break;
            //            case '10': break;
            //            case '11': break;
            //            case '12': break;
            //            case '13': break;
            default:$parent->response(array('response' => 'failed', 'message' => 'Sorry No access'), 401);
                break;
        }
        switch ($parent->current_user_role) {
            case '3':
                break;
            case '4':
            case '5':
                $user_cond .= " AND user_type IN (0,1,5,6,7,8)";
                break;
            case '6':
                $user_cond .= " AND user_type IN (0,1,6,7,8)";
                break;
            case '7':
                $user_cond .= " AND user_type IN (0,1,7,8)";
                break;
            case '14':
                $user_cond .= " AND user_type IN (0,1)";
                break;
            default:
                $user_cond .= " AND user_type IN (0,1)";
                break;
        }

        $assessment_str = "SELECT COUNT(assessmentid) as acount FROM " . TABLE_ASSESSMENT . " WHERE atype='0' AND status='1'  AND  assettypeid='1'  AND  publisheddate<='" . NOW . "' " . $user_cond . " LIMIT 1";
        $get_assessment_arr = $parent->getQueryValue($assessment_str);
        if (count($get_assessment_arr) > 0) {
            $expired_assessment_str = "SELECT COUNT(assessmentid) as acount FROM " . TABLE_ASSESSMENT . " WHERE atype='0' AND status='1'  AND  assettypeid='1'  AND  publisheddate<='" . NOW . "' AND enddate>='" . NOW . "' " . $user_cond . " LIMIT 1";
            $expired_assessment_arr = $parent->getQueryValue($expired_assessment_str);

            $active_count = (string) $get_assessment_arr[0]->acount - $expired_assessment_arr[0]->acount;
            $assessment_arr = array('published' => $get_assessment_arr[0]->acount, 'active' => $active_count, 'expired' => $expired_assessment_arr[0]->acount);
        }

        return (array) $assessment_arr;

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

    /* /////////////////////////////////// AREA MANAGER SALES DASHBOARD  /////////////////////////////////// */
    public static function newmanagersalesDashboard($parent, $role, $year, $month, $email)
    {

        $user_cond = "";
        switch ($role) {

            case '3': //HO Manager
                $user_cond = " AND e.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE is_deleted='0'  AND status='1')";
                break;
            case '4': //Zonal Manager
                $user_cond = " AND e.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE zm_email='" . $email . "' AND is_deleted='0'  AND status='1')";
                break;
            case '5': //Trainer
                $get_trainer_str = "SELECT h.* FROM " . DMS_HOUSER . " h JOIN " . TABLE_USER . " u ON u.emp_code=h.emp_code WHERE u.emailid='" . $email . "' LIMIT 1";
                $trainer_arr = $parent->getQueryValue($get_trainer_str);
                $zone_cond = (count($trainer_arr) > 0) ? " zone='" . $trainer_arr[0]->zone . "'" : " zone='0'";

                $user_cond = " AND e.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE " . $zone_cond . "  AND is_deleted='0'  AND status='1')";
                break;
            case '6': //Regional Manager
                $mcond = '';
                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);
                    $manager_cond .= (count($trainer_arr) > 0) ? " zone='" . $trainer_arr[0]->zone . "'" : " zone='0'";
                } else {
                    $zmemail = escape_str($parent->_request["zmemail"]);
                    $mcond = ($zmemail != 'all' && $zmemail != '') ? " AND zm_email='" . $zmemail . "'" : '';
                }
                $user_cond = " AND e.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE rm_email='" . $email . "'  AND is_deleted='0'  AND status='1' " . $mcond . ")";

                break;
            case '7': //Area Manager
                $mcond = '';
                if ($parent->current_user_role != '5') {
                    $zmemail = escape_str($parent->_request["zmemail"]);
                    $mcond = ($zmemail != 'all' && $zmemail != '') ? " AND zm_email='" . $zmemail . "'" : '';
                }
                $rmemail = escape_str($parent->_request["rmemail"]);
                $mcond .= ($rmemail != 'all' && $rmemail != '') ? " AND rm_email='" . $rmemail . "'" : '';

                $user_cond = " AND e.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE am_email='" . $email . "'  AND is_deleted='0'  AND status='1'  " . $mcond . ")";
                break;
            case '14': //Dealer
                $user_cond = " AND e.dealer_code ='" . $parent->current_user_data->dealer_code . "' ";
                break;
//            case '9': break;
            //            case '10': break;
            //            case '11': break;
            //            case '12': break;
            //            case '13': break;
            default:$parent->response(array('response' => 'failed', 'message' => 'Sorry No access'), 401);
                break;
        }

        $dealer_code = escape_str($parent->_request["dealer_code"]);
        $user_cond .= ($dealer_code != 'all' && $dealer_code != '') ? " AND e.dealer_code='" . $dealer_code . "'" : '';

        $is_history = (isset($parent->_request["is_history"]) && $parent->_request["is_history"] == 1) ? "1" : "0";
        if ($is_history == 0) {
            $user_cond .= " AND e.is_current='1' AND e.status=1 ";
        } else {
            $user_cond .= " AND e.status=1 ";
        }
        //u.status='1' AND
        //$user_target_str = "SELECT COALESCE(SUM(t.target),0) as user_target FROM ".TABLE_USER." u JOIN ".DMS_EMPLOYEE." e ON u.smpin=e.smpin JOIN ".TABLE_USER_SALETARGET." as t ON u.smpin=t.smpin   WHERE  u.is_deleted='0' AND t.sale_month>='".date('Y-m-d',strtotime($year."-".$month."-01"))."' AND t.sale_month<='".date('Y-m-t',strtotime($year."-".$month."-01"))."' ".$user_cond;
        $user_target_str = "SELECT COALESCE(SUM(t.target),0) as user_target FROM " . DMS_EMPLOYEE . " e LEFT JOIN " . TABLE_USER_SALETARGET . " as t ON e.smpin=t.smpin WHERE t.sale_month>='" . date('Y-m-d', strtotime($year . "-" . $month . "-01")) . "' AND t.sale_month<='" . date('Y-m-t', strtotime($year . "-" . $month . "-01")) . "' " . $user_cond;
        $user_target_arr = $parent->getQueryValue($user_target_str);
        $target = (count($user_target_arr) > 0) ? $user_target_arr[0]->user_target : '0';
        //u.status='1' AND
        //$user_achieve_str = "SELECT COALESCE(SUM(s.sales_count),0) as user_achieve FROM ".TABLE_USER." u JOIN ".DMS_EMPLOYEE." e ON u.smpin=e.smpin JOIN ".DMS_EMPLOYEE_SALES." as s ON u.smpin=s.smpin WHERE u.is_deleted='0' AND s.sale_date>='".date('Y-m-d',strtotime($year."-".$month."-01"))."' AND s.sale_date<='".date('Y-m-t',strtotime($year."-".$month."-01"))."' ".$user_cond;
        $user_achieve_str = "SELECT COALESCE(SUM(s.sales_count),0) as user_achieve FROM " . DMS_EMPLOYEE . " e LEFT JOIN " . DMS_EMPLOYEE_SALES . " as s ON e.smpin=s.smpin WHERE s.sale_date>='" . date('Y-m-d', strtotime($year . "-" . $month . "-01")) . "' AND s.sale_date<='" . date('Y-m-t', strtotime($year . "-" . $month . "-01")) . "' " . $user_cond;
        $user_achieve_arr = $parent->getQueryValue($user_achieve_str);
        $achieved = (count($user_achieve_arr) > 0) ? $user_achieve_arr[0]->user_achieve : '0';

        $sales_arr = array('target' => $target, 'achieved' => $achieved, 'month' => date('M', strtotime('2018-' . $month . "-01")), 'year' => $year);

        return (array) $sales_arr;

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

    /* /////////////////////////////////// AREA MANAGER SALES DASHBOARD  /////////////////////////////////// */
    public static function managersalesEnquiryDashboard($parent, $role, $year, $month, $email)
    {

        $user_cond = "";
        switch ($role) {

            case '3': //HO Manager
                $user_cond = " AND e.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE is_deleted='0'  AND status='1')";
                break;
            case '4': //Zonal Manager
                $user_cond = " AND e.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE zm_email='" . $email . "' AND is_deleted='0'  AND status='1')";
                break;
            case '5': //Trainer
                $get_trainer_str = "SELECT h.* FROM " . DMS_HOUSER . " h JOIN " . TABLE_USER . " u ON u.emp_code=h.emp_code WHERE u.emailid='" . $email . "' LIMIT 1";
                $trainer_arr = $parent->getQueryValue($get_trainer_str);
                $zone_cond = (count($trainer_arr) > 0) ? " zone='" . $trainer_arr[0]->zone . "'" : " zone='0'";

                $user_cond = " AND e.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE " . $zone_cond . "  AND is_deleted='0'  AND status='1')";
                break;
            case '6': //Regional Manager
                $mcond = '';
                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);
                    $manager_cond .= (count($trainer_arr) > 0) ? " zone='" . $trainer_arr[0]->zone . "'" : " zone='0'";
                } else {
                    $zmemail = escape_str($parent->_request["zmemail"]);
                    $mcond = ($zmemail != 'all' && $zmemail != '') ? " AND zm_email='" . $zmemail . "'" : '';
                }
                $user_cond = " AND e.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE rm_email='" . $email . "'  AND is_deleted='0'  AND status='1' " . $mcond . ")";

                break;
            case '7': //Area Manager
                $mcond = '';
                if ($parent->current_user_role != '5') {
                    $zmemail = escape_str($parent->_request["zmemail"]);
                    $mcond = ($zmemail != 'all' && $zmemail != '') ? " AND zm_email='" . $zmemail . "'" : '';
                }
                $rmemail = escape_str($parent->_request["rmemail"]);
                $mcond .= ($rmemail != 'all' && $rmemail != '') ? " AND rm_email='" . $rmemail . "'" : '';

                $user_cond = " AND e.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE am_email='" . $email . "'  AND is_deleted='0'  AND status='1'  " . $mcond . ")";
                break;
            case '14': //Dealer
                $user_cond = " AND e.dealer_code ='" . $parent->current_user_data->dealer_code . "' ";
                break;
//            case '9': break;
            //            case '10': break;
            //            case '11': break;
            //            case '12': break;
            //            case '13': break;
            default:$parent->response(array('response' => 'failed', 'message' => 'Sorry No access'), 401);
                break;
        }

        $dealer_code = escape_str($parent->_request["dealer_code"]);
        $user_cond .= ($dealer_code != 'all' && $dealer_code != '') ? " AND e.dealer_code='" . $dealer_code . "'" : '';

        $user_cond .= " AND e.is_current='1' AND e.status=1 AND u.status='1' AND u.is_deleted = 0 ";

        $user_target_str = "SELECT COALESCE(SUM(t.target),0) as user_target FROM " . DMS_EMPLOYEE . " e JOIN " . TABLE_USER . " u ON e.smpin=u.smpin LEFT JOIN " . TABLE_USER_SALETARGET . " as t ON e.smpin=t.smpin WHERE t.sale_month>='" . date('Y-m-d', strtotime($year . "-" . $month . "-01")) . "' AND t.sale_month<='" . date('Y-m-t', strtotime($year . "-" . $month . "-01")) . "' " . $user_cond;
        $user_target_arr = $parent->getQueryValue($user_target_str);
        $target = (count($user_target_arr) > 0) ? $user_target_arr[0]->user_target : '0';

        $user_achieve_str = "SELECT COALESCE(SUM(s.sales_count),0) as user_achieve FROM " . DMS_EMPLOYEE . " e JOIN " . TABLE_USER . " u ON e.smpin=u.smpin LEFT JOIN " . DMS_EMPLOYEE_SALES . " as s ON e.smpin=s.smpin WHERE s.sale_date>='" . date('Y-m-d', strtotime($year . "-" . $month . "-01")) . "' AND s.sale_date<='" . date('Y-m-t', strtotime($year . "-" . $month . "-01")) . "' " . $user_cond;
        $user_achieve_arr = $parent->getQueryValue($user_achieve_str);
        $achieved = (count($user_achieve_arr) > 0) ? $user_achieve_arr[0]->user_achieve : '0';

        $sales_arr = array('target' => $target, 'achieved' => $achieved, 'month' => date('M', strtotime('2018-' . $month . "-01")), 'year' => $year);

        return (array) $sales_arr;

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

    /* /////////////////////////////////// MANAGER ENQUIRY TARGET DASHBOARD  /////////////////////////////////// */
    public static function managerEnquiryTarget($parent, $role, $year, $month, $email)
    {

        $user_cond = "";
        switch ($role) {

            case '3': //HO Manager
                $user_cond = " AND e.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE is_deleted='0'  AND status='1')";
                break;
            case '4': //Zonal Manager
                $user_cond = " AND e.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE zm_email='" . $email . "' AND is_deleted='0'  AND status='1')";
                break;
            case '5': //Trainer
                $get_trainer_str = "SELECT h.* FROM " . DMS_HOUSER . " h JOIN " . TABLE_USER . " u ON u.emp_code=h.emp_code WHERE u.emailid='" . $email . "' LIMIT 1";
                $trainer_arr = $parent->getQueryValue($get_trainer_str);
                $zone_cond = (count($trainer_arr) > 0) ? " zone='" . $trainer_arr[0]->zone . "'" : " zone='0'";

                $user_cond = " AND e.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE " . $zone_cond . "  AND is_deleted='0'  AND status='1')";
                break;
            case '6': //Regional Manager
                $mcond = '';
                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);
                    $manager_cond .= (count($trainer_arr) > 0) ? " zone='" . $trainer_arr[0]->zone . "'" : " zone='0'";
                } else {
                    $zmemail = escape_str($parent->_request["zmemail"]);
                    $mcond = ($zmemail != 'all' && $zmemail != '') ? " AND zm_email='" . $zmemail . "'" : '';
                }
                $user_cond = " AND e.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE rm_email='" . $email . "'  AND is_deleted='0'  AND status='1' " . $mcond . ")";

                break;
            case '7': //Area Manager
                $mcond = '';
                if ($parent->current_user_role != '5') {
                    $zmemail = escape_str($parent->_request["zmemail"]);
                    $mcond = ($zmemail != 'all' && $zmemail != '') ? " AND zm_email='" . $zmemail . "'" : '';
                }
                $rmemail = escape_str($parent->_request["rmemail"]);
                $mcond .= ($rmemail != 'all' && $rmemail != '') ? " AND rm_email='" . $rmemail . "'" : '';

                $user_cond = " AND e.dealer_code IN (SELECT dealer_code FROM " . DMS_DEALER . " WHERE am_email='" . $email . "'  AND is_deleted='0'  AND status='1'  " . $mcond . ")";
                break;
            case '14': //Dealer
                $user_cond = " AND e.dealer_code ='" . $parent->current_user_data->dealer_code . "' ";
                break;
//            case '9': break;
            //            case '10': break;
            //            case '11': break;
            //            case '12': break;
            //            case '13': break;
            default:$parent->response(array('response' => 'failed', 'message' => 'Sorry No access'), 401);
                break;
        }

        $dealer_code = escape_str($parent->_request["dealer_code"]);
        $user_cond .= ($dealer_code != 'all' && $dealer_code != '') ? " AND e.dealer_code='" . $dealer_code . "'" : '';

        $user_cond .= " AND e.is_current='1' AND e.status=1  AND u.status='1' AND u.is_deleted = 0";

        $user_target_str = "SELECT COALESCE(SUM(t.target),0) as user_target FROM " . DMS_EMPLOYEE . " e JOIN " . TABLE_USER . " u ON e.smpin=u.smpin LEFT JOIN " . TABLE_USER_SALETARGET . " as t ON e.smpin=t.smpin WHERE t.sale_month>='" . date('Y-m-d', strtotime($year . "-" . $month . "-01")) . "' AND t.sale_month<='" . date('Y-m-t', strtotime($year . "-" . $month . "-01")) . "' AND e.segment=1 " . $user_cond;
        $user_target_arr = $parent->getQueryValue($user_target_str);
        $mtarget = (count($user_target_arr) > 0) ? $user_target_arr[0]->user_target : '0';

        $user_target_str = "SELECT COALESCE(SUM(t.target),0) as user_target FROM " . DMS_EMPLOYEE . " e JOIN " . TABLE_USER . " u ON e.smpin=u.smpin LEFT JOIN " . TABLE_USER_SALETARGET . " as t ON e.smpin=t.smpin WHERE t.sale_month>='" . date('Y-m-d', strtotime($year . "-" . $month . "-01")) . "' AND t.sale_month<='" . date('Y-m-t', strtotime($year . "-" . $month . "-01")) . "' AND e.segment=2 " . $user_cond;
        $user_target_arr = $parent->getQueryValue($user_target_str);
        $starget = (count($user_target_arr) > 0) ? $user_target_arr[0]->user_target : '0';

        $user_target_str = "SELECT COALESCE(SUM(t.target),0) as user_target FROM " . DMS_EMPLOYEE . " e LEFT JOIN " . TABLE_USER_SALETARGET . " as t ON e.smpin=t.smpin JOIN " . TABLE_USER . " u ON e.smpin=u.smpin WHERE t.sale_month>='" . date('Y-m-d', strtotime($year . "-" . $month . "-01")) . "' AND t.sale_month<='" . date('Y-m-t', strtotime($year . "-" . $month . "-01")) . "' AND e.segment=0 " . $user_cond;
        $user_target_arr = $parent->getQueryValue($user_target_str);
        $otarget = (count($user_target_arr) > 0) ? $user_target_arr[0]->user_target : '0';

        $target = ($mtarget * 5) + ($starget * 3) + ($otarget * 3);

        $sales_arr = array('target' => $target, 'month' => date('M', strtotime('2018-' . $month . "-01")), 'year' => $year);

        return (array) $sales_arr;

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

    /* /////////////////////////////////// CHECK VALID REEDEM  /////////////////////////////////// */
    public static function checkValidReedem($parent, $userid = 0)
    {

        $ret = false;
        if ($userid == 0 && $parent->current_user_id) {
            $userid = $parent->current_user_id;
        }
        $user_str = "SELECT u.userid,u.smpin,e.points  FROM  " . TABLE_USER . " as u JOIN " . TABLE_USER_EPOINTS . " e ON e.smpin=u.smpin  WHERE u.userid='" . $userid . "' AND u.is_deleted=0 AND u.role>7 LIMIT 1";
        $user_arr = $parent->getQueryValue($user_str);
        if (count($user_arr) > 0) {
            $smpin = $user_arr[0]->smpin;
            $userid = $user_arr[0]->userid;

            $data['setting'] = 'REDEEM';
            $data = globalfunc::getSiteSetting($parent, $data);

            $min_redeem_point = $data->REDEEM_MIN_VALUE;
            if ($user_arr[0]->points >= $min_redeem_point) {
                //NEW LEVEL BASED
                //$query_learn=$parent->selectQuery(TABLE_USER_LEARNING,"smpin,lvl_id","smpin='".$smpin."' AND (status=3 OR (lvl_id IN(3,4) AND status NOT IN(4,6))) ORDER by lvl_id DESC","1");
                $query_learn = $parent->selectQuery(TABLE_USER_LEARNING, "smpin,lvl_id", "smpin='" . $smpin . "' AND status=3 ORDER by lvl_id DESC", "1");
                if ($query_learn['nr'] > 0) {
                    $ret = true;
                }
            }
        }
        return $ret;
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// GENERATE WOOHOO COMMON TOKEN  /////////////////////////////////// */
    public static function generateWoohoo($parent)
    {
        // $consumerKey = WOOHOO_CONSUMERKEY; // eg : 797987988989798798797
        // $consumerSecret = WOOHOO_CONSUMERSECRET; // eg : 898980980980909890090
        // $username = WOOHOO_USERNAME; // client@client.com
        // $password = WOOHOO_PASSWORD; //clientpassword

        // //Oauth url's
        // $temporaryCredentialsRequestUrl = WOOHOO_TEMPCREDENTIALSREQUESTURL; //request token url
        // $authorizationUrl = WOOHOO_AUTHORIZATIONURL; //authorize url
        // $accessTokenRequestUrl = WOOHOO_ACCESSTOKENREQUESTURL; //access token url

        // try {

        //     $data['setting'] = 'REDEEM';
        //     $redeem_set = globalfunc::getSiteSetting($parent, $data);

        //     if (isset($redeem_set->WOOHOO_OAUTH2_CODE) && isset($redeem_set->WOOHOO_OAUTH2_TOKEN) && isset($redeem_set->WOOHOO_TOKEN_EXPIRY) && strtotime($redeem_set->WOOHOO_TOKEN_EXPIRY) > strtotime(NOW)) {
        //         $exipry_time = globalfunc::generateExpiryTime($parent, "1440");
        //         $token_secret_data_arr = $parent->selectQuery(TABLE_SITE_SETTING, "*", "setting_type='REDEEM' AND setting_title='WOOHOO_TOKEN_EXPIRY'  AND is_active='1' ", "1");
        //         if ($token_secret_data_arr['nr']) {
        //             //$parent->executeQuery(" UPDATE " . TABLE_SITE_SETTING . " SET  value='" . $exipry_time . "'  WHERE id='" . $token_secret_arr['result'][0]->id . "' ");
        //         } else {
        //             $parent->executeQuery(" INSERT INTO " . TABLE_SITE_SETTING . " (`setting_type`, `setting_title`, `value`, `is_active`, `created_on`, `modified_on`) VALUES ('REDEEM','WOOHOO_TOKEN_EXPIRY','" . $exipry_time . "','1','" . NOW . "','" . NOW . "')");
        //         }

        //     } else {
        //         //OATH2 CODE GENERATE
        //         $ch = curl_init();
        //         $url = WOOHOO_AUTHORIZATION_CODE_URL;
        //         $post = array('clientId' => WOOHOO_CONSUMERKEY, 'username' => WOOHOO_USERNAME, 'password' => WOOHOO_PASSWORD);
        //         $header = array("Content-Type: application/json");
        //         $options = array(CURLOPT_POST => 1, CURLOPT_POSTFIELDS => json_encode($post), CURLOPT_HTTPHEADER => $header, CURLOPT_URL => $url, CURLOPT_CONNECTTIMEOUT => 60, CURLOPT_TIMEOUT => 20, CURLOPT_RETURNTRANSFER => true);
        //         curl_setopt_array($ch, $options);
        //         $response = curl_exec($ch);
        //         $verifierResponse = json_decode($response);
        //         $httpcode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        //         curl_close($ch);
        //         if (isset($verifierResponse->authorizationCode) && $verifierResponse->authorizationCode != '') {

        //             //OAUTH2 CODE
        //             $authorizationCode = $verifierResponse->authorizationCode;
        //             $oauth_token_arr = $parent->selectQuery(TABLE_SITE_SETTING, "*", "setting_type='REDEEM' AND setting_title='WOOHOO_OAUTH2_CODE' AND is_active='1' ", "1");
        //             if ($oauth_token_arr['nr']) {
        //                 $parent->executeQuery(" UPDATE " . TABLE_SITE_SETTING . " SET  value='" . $authorizationCode . "'  WHERE id='" . $oauth_token_arr['result'][0]->id . "' ");
        //             } else {
        //                 $parent->executeQuery(" INSERT INTO " . TABLE_SITE_SETTING . " (`setting_type`, `setting_title`, `value`, `is_active`, `created_on`, `modified_on`) VALUES ('REDEEM','WOOHOO_OAUTH2_CODE','" . $authorizationCode . "','1','" . NOW . "','" . NOW . "')");
        //             }

        //             $nch = curl_init();
        //             $url = WOOHOO_AUTHORIZATION_TOKEN_URL;
        //             $post = array('clientId' => WOOHOO_CONSUMERKEY, 'clientSecret' => WOOHOO_CONSUMERSECRET, 'authorizationCode' => $authorizationCode);
        //             $header = array("Content-Type: application/json");
        //             $options = array(CURLOPT_POST => 1, CURLOPT_POSTFIELDS => json_encode($post), CURLOPT_HTTPHEADER => $header, CURLOPT_URL => $url, CURLOPT_CONNECTTIMEOUT => 60, CURLOPT_TIMEOUT => 20, CURLOPT_RETURNTRANSFER => true);
        //             curl_setopt_array($nch, $options);
        //             $response = curl_exec($nch);
        //             $res_arr = json_decode($response);
        //             //print_r($res_arr);
        //             $httpcode = curl_getinfo($nch, CURLINFO_HTTP_CODE);
        //             curl_close($nch);

        //             if (isset($res_arr->token) && $res_arr->token != '') {
        //                 //OAUTH2 TOKEN
        //                 $oauth2_token = $res_arr->token;
        //                 $oauth_token_arr = $parent->selectQuery(TABLE_SITE_SETTING, "*", "setting_type='REDEEM' AND setting_title='WOOHOO_OAUTH2_TOKEN' AND is_active='1' ", "1");
        //                 if ($oauth_token_arr['nr']) {
        //                     $parent->executeQuery(" UPDATE " . TABLE_SITE_SETTING . " SET  value='" . $oauth2_token . "'  WHERE id='" . $oauth_token_arr['result'][0]->id . "' ");
        //                 } else {
        //                     $parent->executeQuery(" INSERT INTO " . TABLE_SITE_SETTING . " (`setting_type`, `setting_title`, `value`, `is_active`, `created_on`, `modified_on`) VALUES ('REDEEM','WOOHOO_OAUTH2_TOKEN','" . $oauth2_token . "','1','" . NOW . "','" . NOW . "')");
        //                 }

        //             } else {
        //                 $parent->response(array('response' => 'failed', 'message' => 'Sorry, a server error occurred. Please wait a bit and try again'), 406);
        //             }

        //             //WOOHOO_TOKEN_EXPIRY
        //             $exipry_time = globalfunc::generateExpiryTime($parent, "1440");
        //             $expiry_data_arr = $parent->selectQuery(TABLE_SITE_SETTING, "*", "setting_type='REDEEM' AND setting_title='WOOHOO_TOKEN_EXPIRY'  AND is_active='1' ", "1");
        //             if ($expiry_data_arr['nr']) {

        //                 $parent->executeQuery(" UPDATE " . TABLE_SITE_SETTING . " SET  value='" . $exipry_time . "'  WHERE id='" . $expiry_data_arr['result'][0]->id . "' ");
        //             } else {
        //                 $parent->executeQuery("INSERT INTO " . TABLE_SITE_SETTING . " (`setting_type`, `setting_title`, `value`, `is_active`, `created_on`, `modified_on`) VALUES ('REDEEM','WOOHOO_TOKEN_EXPIRY','" . $exipry_time . "','1','" . NOW . "','" . NOW . "')");
        //             }
        //             return true;
        //         } else {
        //             $parent->response(array('response' => 'failed', 'message' => 'SERVER ERROR call'), 500);
        //         }

        //     }

        // } catch (Exception $e) {
        //     // print_r($e->getMessage());
        //     //print_r($e->lastResponse);
        //     $parent->response(array('response' => 'failed', 'message' => 'SERVER ERROR ' . $e->getMessage()), 500);
        // }
        // //once the access tokens and access tokens secret are generated using them with the consumerKey and consumerSecret
        // //api calls can be made

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

    /* /////////////////////////////////// USER EPOINT AND WALLET CREATE  /////////////////////////////////// */
    public static function userTableCreate($parent, $smpin)
    {
        if (isset($smpin) && $smpin) {
            //CHECK TABLE_USER_EPOINTS EXIST
            $query_user_points = $parent->selectQuery(TABLE_USER_EPOINTS, "*", "smpin='" . $smpin . "'", "1");
            if (!($query_user_points['nr'] > 0)) {
                $insert_points_str = "INSERT INTO " . TABLE_USER_EPOINTS . "(`smpin`, `points`, `created_on`, `modified_on`) VALUES ('" . $smpin . "','0','" . NOW . "','" . NOW . "')";
                $parent->executeQuery($insert_points_str);
            }
            //CHECK TABLE_USER_WALLET EXIST
            $query_wallet_arr = $parent->selectQuery(TABLE_USER_WALLET, "*", "smpin='" . $smpin . "'", "1");
            if (!($query_wallet_arr['nr'] > 0)) {
                $insert_wallet_str = "INSERT INTO " . TABLE_USER_WALLET . "(`smpin`, `amount`, `created_on`, `modified_on`) VALUES ('" . $smpin . "','0','" . NOW . "','" . NOW . "')";
                $parent->executeQuery($insert_wallet_str);
            }
        } else {
            self::callErrorMsg($parent, array('message' => 'projectfunctions(userTableCreate):REQUIRED smpin'));
        }

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

    /* /////////////////////////////////// USER WALLET AMOUNT  /////////////////////////////////// */
    public static function userWalletamount($parent, $smpin)
    {
        if (isset($smpin) && $smpin) {
            $wallet_amount = '0';

            $user_str = "SELECT amount  FROM  " . TABLE_USER_WALLET . "  WHERE smpin='" . $smpin . "' LIMIT 1";
            $user_arr = $parent->getQueryValue($user_str);
            if (count($user_arr) > 0) {
                $wallet_amount = $user_arr[0]->amount;
            }
            return $wallet_amount;
        } else {
            self::callErrorMsg($parent, array('message' => 'projectfunctions(userWalletamount):REQUIRED smpin'));
        }
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////// USER ADD WALLET //////////////////////////////////////////////// */
    public static function userAddWallet($parent, $obj, $userid = 0)
    {
        if ($userid == 0 && $parent->current_user_id) {
            if ($parent->current_user_id) {
                $userid = $parent->current_user_id;
                $smpin = $parent->current_smpin;
            } else {
                self::callErrorMsg($parent, array('message' => 'projectfunctions(userAddWallet):REQUIRED userid'));
            }
        } else {
            $user_str = "SELECT *  FROM  " . TABLE_USER . "  WHERE userid='" . $userid . "' AND is_deleted=0 LIMIT 1";
            $user_arr = $parent->getQueryValue($user_str);
            if (count($user_arr) > 0) {
                $smpin = $user_arr[0]->smpin;
            } else {
                self::callErrorMsg($parent, array('message' => 'projectfunctions(userAddWallet):REQUIRED USER NOT FOUND'));
            }
        }

        if (!isset($obj->type)) {
            self::callErrorMsg($parent, array('message' => 'projectfunctions(userAddWallet):REQUIRED obj->type'));
        }

        if (!isset($obj->type_id)) {
            self::callErrorMsg($parent, array('message' => 'projectfunctions(userAddWallet):REQUIRED obj->type_id'));
        }

        if (!isset($obj->amount)) {
            self::callErrorMsg($parent, array('message' => 'projectfunctions(userAddWallet):REQUIRED obj->amount'));
        }

        $obj->description = (isset($obj->description)) ? $obj->description : "";

        //NEED TO CHECK AMOUNT ALREADY ADDED
        $amount_str = "SELECT *  FROM  " . TABLE_USER_WALLET_HISTORY . "  WHERE smpin='" . $smpin . "' AND type='" . $obj->type . "' AND type_id='" . $obj->type_id . "' AND transaction_type='C'";
        $amount_arr = $parent->getQueryValue($amount_str);
        if (count($amount_arr) > 0) {
            //AMOUNT ALREADY ADDED
        } else {
            $lvl_points = (isset($obj->lvl_points)) ? $obj->lvl_points : 0;
            $parent->executeQuery("INSERT INTO " . TABLE_USER_WALLET_HISTORY . "( `smpin`, `type`, `type_id`, `description`, `lvl_points`, `amount`, `transaction_type`, `created_on`, `modified_on`) VALUES ('" . $smpin . "','" . $obj->type . "','" . $obj->type_id . "','" . $obj->description . "','" . $lvl_points . "','" . $obj->amount . "' ,'C','" . NOW . "','" . NOW . "')");
            //UPDATE POINT IN USER
            $parent->executeQuery("UPDATE  " . TABLE_USER_WALLET . " SET amount=amount+" . $obj->amount . " WHERE smpin='" . $smpin . "' ");
        }

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

    /* /////////////////////////////// SEND USER NOTIFICATION //////////////////////////////////////////////// */
    public static function sendUserNotification($parent, $obj, $userid = 0)
    {
        // if ($userid == 0) {
        //     if ($parent->current_user_id) {
        //         $userid = $parent->current_user_id;
        //     } else {
        //         self::callErrorMsg($parent, array('message' => 'projectfunctions(sendUserNotification):REQUIRED userid'));
        //     }
        // }

        // if (!isset($obj)) {
        //     self::callErrorMsg($parent, array('message' => 'projectfunctions(sendUserNotification):REQUIRED obj'));
        // }

        // $key_arr = array('notificationtitle', 'notificationtext', 'notificationtype', 'notificationdata');
        // foreach ($key_arr as $val) {
        //     if (!isset($obj->$val)) {
        //         self::callErrorMsg($parent, array('message' => 'projectfunctions(sendUserNotification):REQUIRED ' . $val));
        //     }

        // }

        // $notificationtitle = $obj->notificationtitle;
        // $notificationtext = $obj->notificationtext;
        // $notificationtype = $obj->notificationtype;
        // $notificationdata = json_encode($obj->notificationdata);

        // //INSERT NOTIFICATION
        // $parent->executeQuery("INSERT INTO `" . TABLE_NOTIFICATION . "` ( `notificationtitle`, `notificationtext`, `notificationdata`, `notificationtype`,  `status`, `publisheddate`, `created_on`, `modified_on`) VALUES ('" . escape_str($notificationtitle) . "','" . escape_str($notificationtext) . "','" . $notificationdata . "','" . $notificationtype . "', '1','" . NOW . "','" . NOW . "','" . NOW . "')");
        // $notificationid = $parent->lastInsertId();

        // $data = array('notification_id' => $notificationid, 'title' => $notificationtitle, 'message' => $notificationtext, 'notificationtype' => $notificationtype, 'notificationdata' => $notificationdata, 'created_date' => NOW);

        // $user_id_arr = array();
        // $ins_arr = array();
        // $parent->executeQuery("INSERT INTO " . TABLE_NOTIFICATION_USER . "(`notificationid`, `userid`, `status`,  `created_on`) VALUES ('" . $notificationid . "','" . $userid . "', '0', '" . NOW . "')");

        // $usertokenarr['ios'] = array();
        // $usertokenarr['android'] = array();
        // //GET DEVICE DETAILS
        // $user_device_str = "SELECT ud.devicetype,ud.token FROM `" . TABLE_USER_DEVICES . "` as ud JOIN (SELECT userid FROM " . TABLE_USER . " WHERE userid='" . $userid . "' AND status='1' AND is_deleted='0'  )  as u ON ud.userid=u.userid  WHERE ud.is_deleted='0' AND (devicetype='ios' OR devicetype='android')  AND token!=''";
        // $user_device_arr = $parent->getQueryValue($user_device_str);
        // if (count($user_device_arr) > 0) {
        //     foreach ($user_device_arr as $res_dev) {
        //         //print_r($user_device_arr);
        //         if (strtolower($res_dev->devicetype) == 'ios') {
        //             $usertokenarr['ios'][] = $res_dev->token;
        //         } else if (strtolower($res_dev->devicetype) == 'android') {
        //             $usertokenarr['android'][] = $res_dev->token;
        //         }
        //     }

        //     globalfunc::sendUserNotification($data, $usertokenarr);
        // }

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

    /* /////////////////////////////////// USER UNAPPROVED AMOUNT  /////////////////////////////////// */
    public static function userUnapprovedamount($parent, $smpin)
    {
        if (isset($smpin) && $smpin) {
            $tot_amount = '0';

            $user_str = "SELECT SUM(amount) as tot_amount  FROM  " . TABLE_USER_WALLET_APPROVE . "  WHERE smpin='" . $smpin . "' AND is_approved=0 LIMIT 1";
            $user_arr = $parent->getQueryValue($user_str);
            if (count($user_arr) > 0) {
                $tot_amount = ($user_arr[0]->tot_amount) ? $user_arr[0]->tot_amount : 0;
            }
            return $tot_amount;
        } else {
            self::callErrorMsg($parent, array('message' => 'projectfunctions(userUnapprovedamount):REQUIRED smpin'));
        }
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// USER UNAPPROVED AMOUNT  /////////////////////////////////// */
    public static function setSaleEditpermisson($parent, $smpin, $date)
    {
        $is_edit = 0;
        if (isset($smpin) && $smpin != '' && isset($date) && $date != '') {

            if (date('Y-m', strtotime(CUR_DATE)) == date('Y-m', strtotime($date))) //CURRENT MONTH CHECK
            {
                $dealer_submitted = 0;
                $am_submitted = 0;
                //DEALER CONDITION
                if ($parent->current_user_role == '14') {
                    $def_edit_date = date('Y-m', strtotime($date)) . "-" . DEALER_SETSALE_DATE;
                    $check_dealer_str = "SELECT *  FROM  " . TABLE_USER_SALESTARGET_STATUS . "  WHERE smpin='" . $smpin . "' AND roleid=14 AND sale_month='" . date('Y-m', strtotime(CUR_DATE)) . "-01' LIMIT 1";
                    $check_dealer_arr = $parent->getQueryValue($check_dealer_str);
                    $dealer_submitted = (count($check_dealer_arr) > 0) ? 1 : 0;

                    if ((strtotime(CUR_DATE) > strtotime($def_edit_date)) || $dealer_submitted == '1') {
                        $check_query_str = "SELECT *  FROM  " . TABLE_USER_SALESTARGET_SET . "  WHERE  emp_code='" . $parent->current_user_data->emp_code . "' AND edit_date>='" . CUR_DATE . "' AND roleid='" . $parent->current_user_role . "' AND (smpin='" . $smpin . "' OR smpin='') LIMIT 1";
                        $check_arr = $parent->getQueryValue($check_query_str);
                        $is_edit = (count($check_arr) > 0) ? 1 : 0;
                    } else {
                        $is_edit = 1;
                    }
                } else if ($parent->current_user_role == '7') {
                    $def_start_date = date('Y-m', strtotime($date)) . "-" . DEALER_SETSALE_DATE;
                    $def_edit_date = date('Y-m', strtotime($date)) . "-" . AM_SETSALE_DATE;

                    $check_dealer_str = "SELECT *  FROM  " . TABLE_USER_SALESTARGET_STATUS . "  WHERE smpin='" . $smpin . "' AND roleid=14 AND sale_month='" . date('Y-m', strtotime(CUR_DATE)) . "-01' LIMIT 1";
                    $check_dealer_arr = $parent->getQueryValue($check_dealer_str);
                    $dealer_submitted = (count($check_dealer_arr) > 0) ? 1 : 0;

                    if (strtotime(CUR_DATE) > strtotime("2020-03-07")) //NEW REQUEST BY 
                    {
                        $check_am_str = "SELECT *  FROM  " . TABLE_USER_SALESTARGET_STATUS . "  WHERE smpin='" . $smpin . "' AND roleid=7 AND sale_month='" . date('Y-m', strtotime(CUR_DATE)) . "-01' LIMIT 1";
                        $check_am_arr = $parent->getQueryValue($check_am_str);
                        $am_submitted = (count($check_am_arr) > 0) ? 1 : 0;
                    } else {
                        $am_submitted = 0;
                    }
                    if ((strtotime(CUR_DATE) > strtotime($def_edit_date)) || $am_submitted == '1') {
                        $check_query_str = "SELECT *  FROM  " . TABLE_USER_SALESTARGET_SET . "  WHERE  emp_code='" . $parent->current_user_data->emp_code . "' AND edit_date>='" . CUR_DATE . "' AND roleid='" . $parent->current_user_role . "' AND (smpin='" . $smpin . "' OR smpin='') LIMIT 1";
                        $check_arr = $parent->getQueryValue($check_query_str);
                        $is_edit = (count($check_arr) > 0) ? 1 : 0;
                    } else {
                        $is_edit = ((strtotime(CUR_DATE) > strtotime($def_start_date)) || $dealer_submitted == 1) ? 1 : 0;
                    }
                }
            }

            return $is_edit;
        } else {
            self::callErrorMsg($parent, array('message' => 'projectfunctions(setSaleEditpermisson):REQUIRED smpin'));
        }
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// USER UNAPPROVED AMOUNT  /////////////////////////////////// */
    public static function cronLog($parent, $obj)
    {
        $type = isset($obj->type) ? $obj->type : '';
        $insert_count = isset($obj->insert_count) ? $obj->insert_count : '';
        $update_count = isset($obj->update_count) ? $obj->update_count : '';
        $status = isset($obj->status) ? $obj->status : '0';
        $apirequest = isset($obj->apirequest) ? $obj->apirequest : '';

        $parent->executeQuery("INSERT INTO " . DMS_CRON_LOG . " (`type`, `insert_count`, `update_count`,apirequest, `status`, `userid`, `created_on`) VALUES ('" . escape_str($type) . "', '" . escape_str($insert_count) . "', '" . escape_str($update_count) . "','" . escape_str($apirequest) . "', '" . escape_str($status) . "','" . $parent->current_user_id . "', '" . NOW . "')");
        return $parent->lastInsertId();
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// CRON  /////////////////////////////////// */
    public static function updateCronLog($parent, $obj)
    {
        $id = isset($obj->id) ? $obj->id : '';
        if ($id) {
            $update_arr = array();
            $apiresponse = isset($obj->apiresponse) ? $obj->apiresponse : '';
            if ($apiresponse) {
                $update_arr[] = " apiresponse='" . escape_str($apiresponse) . "'";
            }

            $insert_count = isset($obj->insert_count) ? $obj->insert_count : '';
            if ($insert_count) {
                $update_arr[] = " insert_count='" . escape_str($insert_count) . "'";
            }

            $update_count = isset($obj->update_count) ? $obj->update_count : '';
            if ($update_count) {
                $update_arr[] = " update_count='" . escape_str($update_count) . "'";
            }

            $status = isset($obj->status) ? $obj->status : '0';
            if ($status) {
                $update_arr[] = " status='" . escape_str($status) . "'";
            }

            if (count($update_arr) > 0) {
                $update_str = @implode(",", $update_arr);
                $parent->executeQuery("UPDATE " . DMS_CRON_LOG . " SET " . $update_str . " WHERE id='" . $id . "'");
            }
        }
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// MODEL IMAGE FOR ENQUIRY  /////////////////////////////////// */
    public static function modelimage($parent, $obj)
    {
        $model_image = NOIMAGE_URL;
        if (isset($obj->model) && isset($obj->variant)) {
            $append_url = SITE_URL . "explore_assets/model/" . strtolower($parent->device_type) . "/";
            switch (strtoupper($obj->model)) {
                case 'AC':
                case 'ACZ': //ACCESS

                    $model_data = $parent->selectQuery(TABLE_COMPARE_MODEL, "model_id,model_image, CASE WHEN model_image!='' THEN CONCAT('" . $append_url . "',model_image) ELSE '" . NOIMAGE_URL . "' END AS 'model_image_url'", "model_id='" . ACCESS_ID . "' AND  is_deleted=0 AND model_type=0", "1");
                    $model_image = ($model_data['nr'] > 0) ? $model_data['result'][0]->model_image_url : NOIMAGE_URL;
                    break;
                case 'GL': //INTRUDER
                    $model_data = $parent->selectQuery(TABLE_COMPARE_MODEL, "model_id,model_image, CASE WHEN model_image!='' THEN CONCAT('" . $append_url . "',model_image) ELSE '" . NOIMAGE_URL . "' END AS 'model_image_url'", "model_id='" . INTRUDER_ID . "' AND  is_deleted=0 AND model_type=0", "1");
                    $model_image = ($model_data['nr'] > 0) ? $model_data['result'][0]->model_image_url : NOIMAGE_URL;
                    break;
                case 'GSX': //GIXXER  & GIXXER 250

                    $id = (strpos(strtoupper($obj->variant), 'GSX250') !== false || strpos($obj->variantname, 'GIXXER 250') !== false) ? GIXXER_250_ID : GIXXER_ID;
                    $model_data = $parent->selectQuery(TABLE_COMPARE_MODEL, "model_id,model_image, CASE WHEN model_image!='' THEN CONCAT('" . $append_url . "',model_image) ELSE '" . NOIMAGE_URL . "' END AS 'model_image_url'", "model_id='" . $id . "' AND  is_deleted=0 AND model_type=0", "1");
                    $model_image = ($model_data['nr'] > 0) ? $model_data['result'][0]->model_image_url : NOIMAGE_URL;
                    break;
                case 'GSXSF': //GIXXER SF & GIXXER SF 250
                    $id = (strpos(strtoupper($obj->variant), 'GSXF250') !== false || strpos($obj->variantname, 'GIXXER SF 250') !== false) ? GIXXER_SF_250_ID : GIXXER_SF_ID;
                    $model_data = $parent->selectQuery(TABLE_COMPARE_MODEL, "model_id,model_image, CASE WHEN model_image!='' THEN CONCAT('" . $append_url . "',model_image) ELSE '" . NOIMAGE_URL . "' END AS 'model_image_url'", "model_id='" . $id . "' AND  is_deleted=0 AND model_type=0", "1");
                    $model_image = ($model_data['nr'] > 0) ? $model_data['result'][0]->model_image_url : NOIMAGE_URL;
                    break;

                case 'UB125': //BURGMAN
                    $model_data = $parent->selectQuery(TABLE_COMPARE_MODEL, "model_id,model_image, CASE WHEN model_image!='' THEN CONCAT('" . $append_url . "',model_image) ELSE '" . NOIMAGE_URL . "' END AS 'model_image_url'", "model_id='" . BURGMAN_ID . "' AND  is_deleted=0 AND model_type=0", "1");
                    $model_image = ($model_data['nr'] > 0) ? $model_data['result'][0]->model_image_url : NOIMAGE_URL;
                    break;

            }
        }
        return $model_image;
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

    /* /////////////////////////////////// DATE DIFF TEXT ENQ  /////////////////////////////////// */
    public static function enqdatediff($parent, $from, $to)
    {
        //2 hrs overdue
        //2 hrs to go
        $diff = (strtotime($from) < strtotime($to)) ? 1 : 0;
        $str = '';
        $date1 = new DateTime($from);
        $date2 = $date1->diff(new DateTime($to));
        $year_diff = $date2->y;
        $month_diff = $date2->m;
        $day_diff = $date2->d;
        $hour_diff = $date2->h;
        $min_diff = $date2->i;
        if ($year_diff > 0) {
            $str .= ($year_diff > 1) ? $year_diff . ' yrs' : $year_diff . ' yr';
        }
        if ($month_diff > 0) {
            $str .= ($str != '') ? " " : '';
            $str .= ($month_diff > 1) ? $month_diff . ' months' : $month_diff . ' month';
        }
        if ($day_diff > 0) {
            $str .= ($str != '') ? " " : '';
            $str .= ($day_diff > 0) ? $day_diff . ' days' : $day_diff . ' day';
        }
        if ($hour_diff > 0) {
            $str .= ($str != '') ? " " . $hour_diff : $hour_diff;
        }
        if ($min_diff > 0) {
            //$str.=($str!='')?" ".$hour_diff .' hrs':$hour_diff .' hrs';
            $str .= ($hour_diff > 0) ? " hr" : '';
            $str .= ($hour_diff > 1) ? "s" : '';
            $str .= ($str != '') ? " " : '';
            if (!$hour_diff > 0) {
                $str .= ($min_diff > 0) ? round($min_diff) . ' mins' : $min_diff . ' min';
            }
        }
        $str = ($str == '') ? "0 mins" : $str;
        $str .= ($diff) ? " to go" : " overdue";

        return $str;
    }
    /* ///////////////////////////////////////////////////////////////////////////////////// */

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

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

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