opensourcecode/Hyper-M_Admin_Portal/services/_api/controller/leaderboard.php

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



//SECURITY 
if ( ! defined('APPLICATION_PATH')) exit('No direct script access allowed');
//FUNCTION LISTS
define('MIN_ASSESSMENT_PERCENT',"60");

class leaderboard
{
	 
	function __construct($parent)
	{
		//GET PAGE MESSAGE
		$this->page_data=globalfunc::getMessage($parent,$parent->lang,'leaderboard');		 
		if(count($parent->params)==0)
		{			
			 $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
		}else
		{
			$qry_params=explode ("?",$parent->params[0]);
			$parameter_val=strtolower($qry_params[0]);
			  
            switch($parameter_val){
                case "sales":
                case "assessment":
                case "location": 
                case "myassessment":    
                case "myteamassessment": 
                case "mysales":   
                case "myteamsales":     
                
                    $this->$parameter_val($parent );
                break;

                default:

                    //$this->leaderboardFunc($parent );
                break;
            }	
                    
		}
		$parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
	}

	/* /////////////////////////////// SALES //////////////////////////////////////// */
	private function sales($parent)
    {
        //leaderboard/sales/{{year}}/{{month}}
		globalfunc::checkMethodType($parent,array('GET'));	
		globalfunc::checkAuthrozation($parent,array('type'=>'user'));
        projectfunc::authorizedRole($parent,array('8','9','10','11','12','13','15'));
        $data_arr=array();
        //FOR AREA WISE LEADER BOARD
        if(count($parent->params)==3)
        {

            $year_data = escape_str($parent->params[1]);
            $last_data = escape_str($parent->params[2]);
            $last_data_arr= explode('?',$last_data);
            $month_data=trim($last_data_arr[0]);

            //GET ALL 20 USER RANK
            $start_date=date('Y-m-d',strtotime($year_data."-".$month_data."-01"));
            $end_date=date('Y-m-t',strtotime($year_data."-".$month_data."-01"));


            //AREA LEADERBOARD COND
            $dealer_code=$parent->current_user_data->dealer_code;        
            $query_allareadealerarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,am_email ","  dealer_code='".$dealer_code."'  AND status='1' AND is_deleted='0'","1");      

            $dealer_arr=array();
            if($query_allareadealerarr['nr']) 
            {   
                $join_cond="JOIN ".DMS_DEALER." as d ON d.dealer_code=u.dealer_code ";
                $area_cond=" and d.am_email ='".$query_allareadealerarr['result'][0]->am_email."'";
            }else
            {
                $area_cond=" and u.dealer_code IN ('')";
            }
        }else if(count($parent->params)==4)
        {
            //FOR PANINDIA,ZONE,REGION WISE LEADER BOARD
            $dealer_code=$parent->current_user_data->dealer_code;        
            switch(strtolower(escape_str($parent->params[1])))
            {
                case "panindia":
                    //COND
                    $join_cond='';
                    $area_cond='';
                    break;
                case "zone":
                    //COND
                    $query_allareadealerarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,zm_email ","  dealer_code='".$dealer_code."'  AND status='1' AND is_deleted='0'","1");      
                    $dealer_arr=array();
                    if($query_allareadealerarr['nr']) 
                    {
                        $join_cond="JOIN ".DMS_DEALER." as d ON d.dealer_code=u.dealer_code ";
                        $area_cond=" and d.zm_email ='".$query_allareadealerarr['result'][0]->zm_email."'";
                    }else
                    {
                        $area_cond=" and u.dealer_code IN ('')";
                    }
                    break; 
                case "region":
                    //COND
                    $query_allareadealerarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,rm_email ","  dealer_code='".$dealer_code."'  AND status='1' AND is_deleted='0'","1");       
                    $dealer_arr=array();
                    if($query_allareadealerarr['nr']) 
                    {
                        $join_cond="JOIN ".DMS_DEALER." as d ON d.dealer_code=u.dealer_code ";
                        $area_cond=" and d.rm_email ='".$query_allareadealerarr['result'][0]->rm_email."'";
                    }else
                    {
                        $area_cond=" and u.dealer_code IN ('')";
                    }
                    break;  
                default :
                    $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
                break;
            }
            
            $year_data = escape_str($parent->params[2]);
            $last_data = escape_str($parent->params[3]);
            $last_data_arr= explode('?',$last_data);
            $month_data=trim($last_data_arr[0]);

            //GET ALL 20 USER RANK
            $start_date=date('Y-m-d',strtotime($year_data."-".$month_data."-01"));
            $end_date=date('Y-m-t',strtotime($year_data."-".$month_data."-01"));
                
        }else
        {
            $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
        }
        
        //NEW ROLE CONDITION
        $user_cond=($parent->current_user_role>7 && $parent->current_user_role<14)?" AND (u.role>7 AND u.role<14) ":" AND u.role=15 ";
        
         
        //TOP 20 AREA SALES LEADERBOARD
        $parent->executeQuery("SET @rownum := 0");                    
        //$get_user_rank_str = "SELECT * FROM (SELECT @rownum := @rownum + 1 AS rank, salecount,targetcount,smpin,userid,last_sale,first_sale,sale_per FROM (SELECT c.*, CASE WHEN targetcount=0 THEN 0 ELSE (salecount/targetcount)*100 END as 'sale_per' FROM ( SELECT a.userid,a.smpin,a.salecount,b.targetcount,a.last_sale,a.first_sale FROM (SELECT COALESCE(COUNT(s.id),'0') as scount, COALESCE(SUM(s.sales_count),'0') as salecount, u.smpin,u.userid,MAX(s.sale_date) last_sale,MIN(s.sale_date) first_sale,u.dealer_code,u.role FROM ".TABLE_USER." as u JOIN ".DMS_DEALER." ds ON u.dealer_code=ds.dealer_code  LEFT JOIN (SELECT id,smpin,sale_date,sales_count FROM ".DMS_EMPLOYEE_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON s.smpin=u.smpin ".$join_cond." WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." ".$area_cond." GROUP BY u.smpin ) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.smpin,u.userid,u.dealer_code,u.role FROM ".TABLE_USER." as u LEFT JOIN (SELECT id,smpin,target FROM ".TABLE_USER_SALETARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON t.smpin=u.smpin ".$join_cond." WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." ".$area_cond."  GROUP BY u.smpin ) as b ON a.userid=b.userid )c  ORDER BY cast(sale_per as SIGNED) DESC,cast(salecount as SIGNED) DESC,cast(targetcount as SIGNED) DESC,last_sale ASC,first_sale ASC,userid ASC)as a )as k LIMIT 20";
        $get_user_rank_str = "SELECT * FROM (SELECT @rownum := @rownum + 1 AS rank, salecount,targetcount,smpin,userid ,sale_per FROM (SELECT c.*, CASE WHEN targetcount=0 THEN 0 ELSE (salecount/targetcount)*100 END as 'sale_per' FROM ( SELECT a.userid,a.smpin,a.salecount,b.targetcount  FROM (SELECT COALESCE(COUNT(s.id),'0') as scount, COALESCE(SUM(s.sales_count),'0') as salecount, u.smpin,u.userid ,u.dealer_code,u.role FROM ".TABLE_USER." as u JOIN ".DMS_DEALER." ds ON u.dealer_code=ds.dealer_code  LEFT JOIN (SELECT id,smpin,sale_date,sales_count FROM ".DMS_EMPLOYEE_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON s.smpin=u.smpin ".$join_cond." WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." ".$area_cond." GROUP BY u.smpin ) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.smpin,u.userid,u.dealer_code,u.role FROM ".TABLE_USER." as u LEFT JOIN (SELECT id,smpin,target FROM ".TABLE_USER_SALETARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON t.smpin=u.smpin ".$join_cond." WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." ".$area_cond."  GROUP BY u.smpin ) as b ON a.userid=b.userid )c  ORDER BY cast(sale_per as SIGNED) DESC,cast(salecount as SIGNED) DESC,cast(targetcount as SIGNED) DESC,userid ASC)as a )as k LIMIT 20";
        $get_user_rank_arr=$parent->getQueryValue($get_user_rank_str);
        if(count($get_user_rank_arr)>0)
        {
            foreach ($get_user_rank_arr as $key => $value)
            {
                $get_user_rank_arr[$key]->user=projectfunc::getuserdata($parent,$value->userid);
                $get_user_rank_arr[$key]->percentage=round($value->sale_per);
            }   
             $data_arr= $get_user_rank_arr;
        }                   
       
        
        //GET USER DATA
        $parent->executeQuery("SET @rownum := 0");                    
        //$get_current_user_rank_str = "SELECT * FROM (SELECT @rownum := @rownum + 1 AS rank, salecount,targetcount,smpin,userid,last_sale,first_sale,sale_per FROM (SELECT c.*, CASE WHEN targetcount=0 THEN 0 ELSE (salecount/targetcount)*100 END as 'sale_per' FROM ( SELECT a.userid,a.smpin,a.salecount,b.targetcount,a.last_sale,a.first_sale FROM (SELECT COALESCE(COUNT(s.id),'0') as scount,  COALESCE(SUM(s.sales_count),'0') as salecount, u.smpin,u.userid,MAX(s.sale_date) last_sale,MIN(s.sale_date) first_sale,u.dealer_code,u.role FROM ".TABLE_USER." as u JOIN ".DMS_DEALER." ds ON u.dealer_code=ds.dealer_code  LEFT JOIN (SELECT id,smpin,sale_date,sales_count FROM ".DMS_EMPLOYEE_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON s.smpin=u.smpin ".$join_cond." WHERE u.is_deleted=0  AND u.status=1 ".$user_cond." ".$area_cond." GROUP BY u.smpin ) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.smpin,u.userid,u.dealer_code,u.role FROM ".TABLE_USER." as u LEFT JOIN (SELECT id,smpin,target FROM ".TABLE_USER_SALETARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON t.smpin=u.smpin ".$join_cond." WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." ".$area_cond."  GROUP BY u.smpin ) as b ON a.userid=b.userid )c ORDER BY cast(sale_per as SIGNED) DESC,cast(salecount as SIGNED) DESC,cast(targetcount as SIGNED) DESC,last_sale ASC,first_sale ASC,userid ASC)as a )as k  WHERE userid='".$parent->current_user_id."' LIMIT 1";
        $get_current_user_rank_str = "SELECT * FROM (SELECT @rownum := @rownum + 1 AS rank, salecount,targetcount,smpin,userid,sale_per FROM (SELECT c.*, CASE WHEN targetcount=0 THEN 0 ELSE (salecount/targetcount)*100 END as 'sale_per' FROM ( SELECT a.userid,a.smpin,a.salecount,b.targetcount  FROM (SELECT COALESCE(COUNT(s.id),'0') as scount,  COALESCE(SUM(s.sales_count),'0') as salecount, u.smpin,u.userid,u.dealer_code,u.role FROM ".TABLE_USER." as u JOIN ".DMS_DEALER." ds ON u.dealer_code=ds.dealer_code  LEFT JOIN (SELECT id,smpin,sale_date,sales_count FROM ".DMS_EMPLOYEE_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON s.smpin=u.smpin ".$join_cond." WHERE u.is_deleted=0  AND u.status=1 ".$user_cond." ".$area_cond." GROUP BY u.smpin ) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.smpin,u.userid,u.dealer_code,u.role FROM ".TABLE_USER." as u LEFT JOIN (SELECT id,smpin,target FROM ".TABLE_USER_SALETARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON t.smpin=u.smpin ".$join_cond." WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." ".$area_cond."  GROUP BY u.smpin ) as b ON a.userid=b.userid )c ORDER BY cast(sale_per as SIGNED) DESC,cast(salecount as SIGNED) DESC,cast(targetcount as SIGNED) DESC,userid ASC)as a )as k  WHERE userid='".$parent->current_user_id."' LIMIT 1";
        $get_user_rank_arr=$parent->getQueryValue($get_current_user_rank_str);
        // print_r($get_user_rank_arr);
        
        $cur_data_arr=new stdClass();
        $udata=projectfunc::getuserdata($parent,$parent->current_user_id);
        $cur_data_arr->user=$udata;
        foreach($udata as $key=>$res)
        {
            $cur_data_arr->$key=$res;
        }           
        $cur_data_arr->rank='';
        $cur_data_arr->salecount='';
        $cur_data_arr->percentage='';
        
        
        // print_r($cur_data_arr);
        if(count($get_user_rank_arr)>0)
        {
            //print_r($cur_data_arr);
            foreach($get_user_rank_arr[0] as $okey=>$objval)
            {
                 $cur_data_arr->$okey= $objval;                 
            }
            $cur_data_arr->percentage=round($cur_data_arr->sale_per);
            
        }
        
        //$get_count_arr=$parent->getQueryValue("SELECT COUNT( userid) total_count FROM ( SELECT a.userid,a.smpin,a.salecount,b.targetcount,a.last_sale,a.first_sale FROM (SELECT COALESCE(COUNT(s.id),'0') as scount, COALESCE(SUM(s.sales_count),'0') as salecount, u.smpin,u.userid,MAX(s.sale_date) last_sale,MIN(s.sale_date) first_sale,u.dealer_code,u.role FROM ".TABLE_USER." as u JOIN ".DMS_DEALER." ds ON u.dealer_code=ds.dealer_code  LEFT JOIN (SELECT id,smpin,sale_date,sales_count FROM ".DMS_EMPLOYEE_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON s.smpin=u.smpin ".$join_cond." WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." ".$area_cond." GROUP BY u.smpin ) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.smpin,u.userid,u.dealer_code,u.role FROM ".TABLE_USER." as u LEFT JOIN (SELECT id,smpin,target FROM ".TABLE_USER_SALETARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON t.smpin=u.smpin ".$join_cond." WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  ".$area_cond."  GROUP BY u.smpin ) as b ON a.userid=b.userid )c LIMIT 1");    
        $get_count_arr=$parent->getQueryValue("SELECT COUNT( userid) total_count FROM ( SELECT a.userid,a.smpin,a.salecount,b.targetcount FROM (SELECT COALESCE(COUNT(s.id),'0') as scount, COALESCE(SUM(s.sales_count),'0') as salecount, u.smpin,u.userid,u.dealer_code,u.role FROM ".TABLE_USER." as u JOIN ".DMS_DEALER." ds ON u.dealer_code=ds.dealer_code  LEFT JOIN (SELECT id,smpin,sale_date,sales_count FROM ".DMS_EMPLOYEE_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON s.smpin=u.smpin ".$join_cond." WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." ".$area_cond." GROUP BY u.smpin ) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.smpin,u.userid,u.dealer_code,u.role FROM ".TABLE_USER." as u LEFT JOIN (SELECT id,smpin,target FROM ".TABLE_USER_SALETARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON t.smpin=u.smpin ".$join_cond." WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  ".$area_cond."  GROUP BY u.smpin ) as b ON a.userid=b.userid )c LIMIT 1");  
        $total_count=(count($get_count_arr)>0)?$get_count_arr[0]->total_count:"0";
                    
        //LOG
        $logstr='LEADERBOARD SALES';			 
        globalfunc::insertUserLog($parent,$logstr);
        $parent->response(array('response'=>'success', 'data'=>$data_arr,'user_data'=>$cur_data_arr,'total_count'=>$total_count,'message'=>$this->page_data['success1'] ),200);
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
                
    /* /////////////////////////////// ASSESSMENT //////////////////////////////////////// */
	private function assessment($parent)
    {
        //assessment/sales/{{year}}/{{month}}
		globalfunc::checkMethodType($parent,array('GET','POST'));		
		//globalfunc::checkParamCount($parent,1);
		globalfunc::checkAuthrozation($parent,array('type'=>'user'));
        projectfunc::authorizedRole($parent,array('8','9','10','11','12','13','15'));        
        //$year_data = escape_str($parent->params[1]);
        //$last_data = escape_str($parent->params[2]);
        //$last_data_arr= explode('?',$last_data);
        //$month_data=trim($last_data_arr[0]);
        
        //USER COND
        //$user_cond_arr=projectfunc::getUserAssessmentCond($parent);               
        //$user_cond=" AND ".@implode(' AND ',$user_cond_arr);
        
        if($parent->get_request_method()=='POST')
        {
            $REQUEST_KEYS=array('month'=>'string','year'=>'number');
            globalfunc::mandatoryKey($parent,$REQUEST_KEYS);
            $month=escape_str($parent->_request['month']);
            $year=escape_str($parent->_request['year']);
            
            $date=$year."-".$month."-01";
            $start_date=date('Y-m',strtotime($date))."-01";  
            $end_date=date('Y-m-t',strtotime($date));  
            
        }else
        {
            $start_date=date('Y-m',strtotime(CUR_DATE))."-01";
            $end_date=date('Y-m-t', strtotime(CUR_DATE));
        }
        $date_cond=" AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59'";
        
        if(count($parent->params)==1)
        {
            //AREA LEADERBOARD COND
            $dealer_code=$parent->current_user_data->dealer_code;        
            $query_allareadealerarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,am_email ","  dealer_code='".$dealer_code."'  AND status='1' AND is_deleted='0'","1");      

            $dealer_arr=array();
            if($query_allareadealerarr['nr']) 
            {
                $join_cond="JOIN ".DMS_DEALER." as dd ON dd.dealer_code=u.dealer_code ";
                $area_cond=" and dd.am_email ='".$query_allareadealerarr['result'][0]->am_email."'";
            }else
            {
                $area_cond=" and u.dealer_code IN ('')";
            }
                
        }else if(count($parent->params)==2)
        {
             //FOR PANINDIA,ZONE,REGION WISE LEADER BOARD
            $dealer_code=$parent->current_user_data->dealer_code;  
            $type_data=explode('?',strtolower(escape_str($parent->params[1])));
            switch($type_data[0])
            {
                case "panindia":
                    //COND
                    $join_cond='';
                    $area_cond='';
                    break;
                case "zone":
                    //COND
                    $query_allareadealerarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,zm_email ","  dealer_code='".$dealer_code."'  AND status='1' AND is_deleted='0'","1");      
                    $dealer_arr=array();
                    if($query_allareadealerarr['nr']) 
                    {
                        $join_cond="JOIN ".DMS_DEALER." as dd ON dd.dealer_code=u.dealer_code ";
                        $area_cond=" and dd.zm_email ='".$query_allareadealerarr['result'][0]->zm_email."'";
                    }else
                    {
                        $area_cond=" and u.dealer_code IN ('')";
                    }
                    break; 
                case "region":
                    //COND
                    $query_allareadealerarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,rm_email ","  dealer_code='".$dealer_code."'  AND status='1' AND is_deleted='0'","1");       
                    $dealer_arr=array();
                    if($query_allareadealerarr['nr']) 
                    {
                        $join_cond="JOIN ".DMS_DEALER." as dd ON dd.dealer_code=u.dealer_code ";
                        $area_cond=" and dd.rm_email ='".$query_allareadealerarr['result'][0]->rm_email."'";
                    }else
                    {
                        $area_cond=" and u.dealer_code IN ('')";
                    }
                    break;  
                default :
                    $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
                    break;
            }
        }else
        {
            $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
        }
		
        //NEW ROLE CONDITION
        $user_cond_assess=($parent->current_user_role>7 && $parent->current_user_role<14)?" AND a.user_type=0 ":" AND a.user_type=1 ";
        $user_cond=($parent->current_user_role>7 && $parent->current_user_role<14)?" AND (u.role>7 AND u.role<14) ":" AND u.role=15 ";
        
        
        //GET ALL ASSESSMENT LIST WHICH PUBLISHED 
        $assessment_str = "SELECT a.assessmentid FROM `".TABLE_ASSESSMENT."` as a  WHERE  a.status='1' AND a.atype='0' AND a.assettypeid='1' ".$user_cond_assess.$date_cond;
        $assessment_arr=$parent->getQueryValue($assessment_str);
        //ASSESSMENT COUNT
        $assessment_count=(count($assessment_arr)>0)?count($assessment_arr):'0';
        
        
        $data_arr=array();
        if(count($assessment_arr)>0)
        {     
            foreach($assessment_arr as $res_assess)
            {
                $assessmentid_arr[]=$res_assess->assessmentid;
            }
            
            //GET ALL 20 USER RANK
            //$parent->executeQuery("SET @rownum := 0");                    
            $get_user_rank_str = "SELECT SUM(highscore)as user_score,u.userid,COALESCE(ar.total_time,'0')as total_time,MAX(au.modified_on) as last_modified,u.dealer_code,u.role  FROM ".TABLE_USER." as u JOIN ".DMS_DEALER." d ON u.dealer_code=d.dealer_code JOIN (SELECT * FROM  ".TABLE_ASSESSMENT_USER."  WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") AND highscorearid!=0 ) as au ON u.userid=au.userid  LEFT JOIN (SELECT SUM(time_taken)as total_time,assessmentid,userid FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") GROUP BY userid )as ar ON u.userid=ar.userid ".$join_cond."  WHERE  u.is_deleted=0 AND u.status=1 ".$user_cond." ".$area_cond." GROUP BY u.userid ORDER BY user_score DESC,ar.total_time ASC,last_modified ASC,u.userid ASC   ";
            $get_user_rank_arr=$parent->getQueryValue($get_user_rank_str);
            if($get_user_rank_arr)
            {
                 foreach($get_user_rank_arr as $res_user)
                 {
                    $user_data_arr=new stdClass();
                    $get_time_str = "SELECT * FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") AND userid='".$res_user->userid."'";
                    $get_time_arr=$parent->getQueryValue($get_time_str);

                    $getuser_data= projectfunc::getuserdata($parent,$res_user->userid);  
                    $user_data_arr->user=$getuser_data;
                    $user_data_arr->rank='0';
                    $user_data_arr->tot_user_score=(count($get_time_arr)>0)?$res_user->user_score:'-';
                    $user_data_arr->percentage_score=(count($get_time_arr)>0)?'0':'-';  
                    
                    $user_data_arr->total_time= $res_user->total_time;
                    $user_data_arr->last_time=(count($get_time_arr)>0)?$res_user->last_modified:'-';
                    $user_data_arr->last_time_str=(count($get_time_arr)>0)?date('Y-m-d',strtotime($res_user->last_modified)):'-';
                    
                    //GET EACH USER COND
                    
                    $user_cond=array();
//                    if($getuser_data->role)
//                    {
//                        $user_cond[]=" (a.filter_role LIKE '%".$getuser_data->role."%' OR a.filter_role='')";
//                    }
                    if($getuser_data->zone)
                    {
                        $user_cond[]=" (a.filter_zone LIKE '%".$getuser_data->zone."%' OR a.filter_zone='')";
                    }
                    if($getuser_data->city)
                    {
                        $user_cond[]=" (a.filter_region LIKE '%".$getuser_data->city."%' OR a.filter_region='')";
                    }
                    if($getuser_data->dealer_code)
                    {
                        $user_cond[]=" (a.filter_dealer LIKE '%".$getuser_data->dealer_code."%'  OR a.filter_dealer='')";
                    }   
                    if($getuser_data->role>7)
                    {
                        $user_cond[]=" (a.segment = '".$getuser_data->segment."' OR a.segment =0)";
                    }
                    $assess_user_cond=" AND ".@implode(' AND ',$user_cond);
                   
                    
                    
                   $count_assessment_str = "SELECT COUNT(a.assessmentid) as acount FROM `".TABLE_ASSESSMENT."` as a  WHERE a.status='1' AND a.atype='0' AND a.assettypeid='1'  AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59' AND a.enddate>='".$user_data_arr->user->user_created_on."' AND  assessmentid IN (".@implode(',',$assessmentid_arr).") ".$user_cond_assess." ". $assess_user_cond." LIMIT 1";
                    $count_assessment_arr=$parent->getQueryValue($count_assessment_str); 
                    $user_data_arr->percentage_score=(count($assessment_arr)>0)?round($res_user->user_score/$count_assessment_arr[0]->acount):'0';
                    $user_data_arr->percentage=$user_data_arr->percentage_score;
                
                    if((($assessment_count/$count_assessment_arr[0]->acount)*100)>=MIN_ASSESSMENT_PERCENT)
                    {      
                        $data_arr[]=$user_data_arr;
                    }
                 }
            }
            
            //GET CURRENT USER RANK
            $cur_data_arr=new stdClass();
            $udata=projectfunc::getuserdata($parent,$parent->current_user_id);
            $cur_data_arr->user=$udata;
            foreach($udata as $key=>$res)
            {
                $cur_data_arr->$key=$res;
            } 
                    
            $cur_data_arr->rank='-';
            $cur_data_arr->tot_user_score='-';
            $cur_data_arr->percentage_score='-';
            $cur_data_arr->percentage=$cur_data_arr->percentage_score;
            $cur_data_arr->total_time='0';
            $cur_data_arr->last_time='-';
            $cur_data_arr->last_time_str='-';
            if(count($data_arr)>0)
            {
                array_multisort(array_column($data_arr, 'percentage_score'),  SORT_DESC,array_column($data_arr, 'total_time'), SORT_ASC,$data_arr);
                $rank=1;
                foreach($data_arr as $key=>$res)
                { 
                    $data_arr[$key]->rank=$rank;                    
                    if($res->user->userid==$parent->current_user_id)
                    {
                        $cur_data_arr->rank=$rank;
                        $cur_data_arr->tot_user_score=$res->tot_user_score;
                        $cur_data_arr->percentage_score=$res->percentage_score;
                        $cur_data_arr->percentage=$res->percentage;
                        //$cur_data_arr->total_time=(count($get_time_arr)>0)?$get_current_user_rank_arr[0]->total_time:'-';
                        $cur_data_arr->total_time= $res->total_time;
                        $cur_data_arr->last_time= $res->last_time;
                        $cur_data_arr->last_time_str=($res->last_time!='')?date('Y-m-d',strtotime($res->last_time)):'-';
                    }
                    $rank++;
                }
            } 
            
        } else {
            $cur_data_arr=new stdClass();
            $udata=projectfunc::getuserdata($parent,$parent->current_user_id);
            $cur_data_arr->user=$udata;
            foreach($udata as $key=>$res)
            {
                $cur_data_arr->$key=$res;
            } 
            
            $cur_data_arr->rank='-';
            $cur_data_arr->tot_user_score='-';
            $cur_data_arr->percentage_score='-';
            $cur_data_arr->percentage=$cur_data_arr->percentage_score;
            $cur_data_arr->total_time='0';
            $cur_data_arr->last_time='-';
            $cur_data_arr->last_time_str='-';
            $get_count_arr=array();
        }
        
        $total_count=count($data_arr);
        if(count($data_arr)>0)
        {
            $temp=array_chunk($data_arr,20);
            $data_arr=$temp[0];
        }
                
        
        //LOG
        $logstr='LEADERBOARD ASSESSMENT';		 
        globalfunc::insertUserLog($parent,$logstr);
        $parent->response(array('response'=>'success','data'=>$data_arr,'user_data'=>$cur_data_arr,'total_count'=>$total_count,  'message'=>$this->page_data['success1']),200);
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
    
    /* /////////////////////////////// LOCATION ARRAY //////////////////////////////////////// */
    private function generate_location_arr($parent)
    {
                
        switch($parent->current_user_role)
        {
            case '3':                
                $location_arr=array();
                $zm_arr=array();
                $rm_arr=array();            
                //DSE LOCATION DATA
                $location_arr[]=array("id"=>"P","title"=>"Pan India");
                //ZM
                $query_all_zmarr=$parent->selectQuery(DMS_DEALER,"zm_name,zm_email "," status='1' AND is_deleted='0' GROUP BY zm_email","");
                if($query_all_zmarr['nr']>0)
                {
                    foreach($query_all_zmarr['result']as $res)
                    {
                        $zm_data=array("id"=>"Z||".$res->zm_email,"title"=>"ZM - ".$res->zm_name);
                        $zm_arr[]=$zm_data;
                        $location_arr[]=$zm_data;
                    }
                
                }
                //RM
                $query_all_rmarr=$parent->selectQuery(DMS_DEALER,"rm_name,rm_email "," status='1' AND is_deleted='0' GROUP BY rm_email","");
                if($query_all_rmarr['nr']>0)
                {
                    foreach($query_all_rmarr['result']as $res)
                    {
                        $rm_data=array("id"=>"R||".$res->rm_email,"title"=>"RM - ".$res->rm_name);
                        $rm_arr[]=$rm_data;
                        $location_arr[]=$rm_data;
                    }
                
                }
                //AM
                $query_all_amarr=$parent->selectQuery(DMS_DEALER,"am_name,am_email "," status='1' AND is_deleted='0' GROUP BY am_email","");
                if($query_all_amarr['nr']>0)
                {
                    foreach($query_all_amarr['result']as $res)
                    {
                        $location_arr[]=array("id"=>"A||".$res->am_email,"title"=>"AM - ".$res->am_name);
                    }
                
                }
                //print_r($location_arr);
                $teamrank_arr[]=array("id"=>'DSE',"title"=>"Sales Executive",'location'=>$location_arr); 
                //$teamrank_arr[]=array("id"=>'IDSE',"title"=>"International Bike Sales Executive",'location'=>$location_arr); 
                
                
                //AM LOCATION DATA
                $location_arr=array();
                $location_arr[]=array("id"=>"P","title"=>"Pan India");
                if(count($zm_arr)>0)
                {
                    foreach($zm_arr as $zm_val)
                    {
                        $location_arr[]=$zm_val;
                    }
                }
                if(count($rm_arr)>0)
                {
                    foreach($rm_arr as $rm_val)
                    {
                        $location_arr[]=$rm_val;
                    }
                }
                $teamrank_arr[]=array("id"=>"AM","title"=>"Area Manager",'location'=>$location_arr);
                
                //RM LOCATION DATA
                $location_arr=array();
                $location_arr[]=array("id"=>"P","title"=>"Pan India");
                if(count($zm_arr)>0)
                {
                    foreach($zm_arr as $zm_val)
                    {
                        $location_arr[]=$zm_val;
                    }
                }
                $teamrank_arr[]=array("id"=>"RM","title"=>"Regional Manager",'location'=>$location_arr);
                
                //ZM LOCATION DATA
                $location_arr=array();
                $location_arr[]=array("id"=>"P","title"=>"Pan India");
                $teamrank_arr[]=array("id"=>"ZM","title"=>"Zonal Manager",'location'=>$location_arr);
                break;
            case '4':                
                $location_arr=array();
                $zm_arr=array();
                $rm_arr=array();            
                //DSE LOCATION DATA
            
                //ZM
                $zm_data=array("id"=>"Z||".$parent->current_user_data->emailid,"title"=>"Zonal manager - ".$parent->current_user_data->fname." ".$parent->current_user_data->lname);
                $zm_arr[]=$zm_data;
                $location_arr[]=$zm_data;
                
                //RM
                $query_all_rmarr=$parent->selectQuery(DMS_DEALER,"rm_name,rm_email "," status='1' AND is_deleted='0' AND zm_email='".$parent->current_user_data->emailid."' GROUP BY rm_email","");
                if($query_all_rmarr['nr']>0)
                {
                    foreach($query_all_rmarr['result']as $res)
                    {
                        $rm_data=array("id"=>"R||".$res->rm_email,"title"=>"RM - ".$res->rm_name);
                        $rm_arr[]=$rm_data;
                        $location_arr[]=$rm_data;
                    }
                
                }
                //AM
                $query_all_amarr=$parent->selectQuery(DMS_DEALER,"am_name,am_email "," status='1' AND zm_email='".$parent->current_user_data->emailid."' AND is_deleted='0' GROUP BY am_email","");
                if($query_all_amarr['nr']>0)
                {
                    foreach($query_all_amarr['result']as $res)
                    {
                        $location_arr[]=array("id"=>"A||".$res->am_email,"title"=>"AM - ".$res->am_name);
                    }
                
                }
                $teamrank_arr[]=array("id"=>'DSE',"title"=>"Sales Executive",'location'=>$location_arr); 
                //$teamrank_arr[]=array("id"=>'IDSE',"title"=>"International Bike Sales Executive",'location'=>$location_arr);
                //AM LOCATION DATA
                $location_arr=array();
            
                if(count($zm_arr)>0)
                {
                    foreach($zm_arr as $zm_val)
                    {
                        $location_arr[]=$zm_val;
                    }
                }
                if(count($rm_arr)>0)
                {
                    foreach($rm_arr as $rm_val)
                    {
                        $location_arr[]=$rm_val;
                    }
                }
                $teamrank_arr[]=array("id"=>"AM","title"=>"Area Manager",'location'=>$location_arr);
                
                //RM LOCATION DATA
                $location_arr=array();
                $location_arr[]=array("id"=>"P","title"=>"Pan India");
                if(count($zm_arr)>0)
                {
                    foreach($zm_arr as $zm_val)
                    {
                        $location_arr[]=$zm_val;
                    }
                }
                $teamrank_arr[]=array("id"=>"RM","title"=>"Regional Manager",'location'=>$location_arr);            
                break;  
            case '5':                
                $location_arr=array();
                $zm_arr=array();
                $rm_arr=array();            
                //DSE LOCATION DATA
                $query_zone=$parent->selectQuery(DMS_HOUSER,"zone"," emp_code='".$parent->current_user_data->emp_code."'","1");
                $zone=($query_zone['nr']>0)?$query_zone['result'][0]->zone:'';
                //ZM
                $query_all_zmarr=$parent->selectQuery(DMS_DEALER,"zm_name,zm_email "," status='1' AND zone='".$zone."' AND is_deleted='0' GROUP BY zm_email","");
                if($query_all_zmarr['nr']>0)
                {
                    foreach($query_all_zmarr['result']as $res)
                    {
                        $zm_data=array("id"=>"Z||".$res->zm_email,"title"=>"ZM - ".$res->zm_name);
                        $zm_arr[]=$zm_data;
                        $location_arr[]=$zm_data;
                    }
                
                }
                //RM
                $query_all_rmarr=$parent->selectQuery(DMS_DEALER,"rm_name,rm_email "," status='1' AND zone='".$zone."' AND is_deleted='0' GROUP BY rm_email","");
                if($query_all_rmarr['nr']>0)
                {
                    foreach($query_all_rmarr['result']as $res)
                    {
                        $rm_data=array("id"=>"R||".$res->rm_email,"title"=>"RM - ".$res->rm_name);
                        $rm_arr[]=$rm_data;
                        $location_arr[]=$rm_data;
                    }
                
                }
                //AM
                $query_all_amarr=$parent->selectQuery(DMS_DEALER,"am_name,am_email "," status='1' AND zone='".$zone."' AND is_deleted='0' GROUP BY am_email","");
                if($query_all_amarr['nr']>0)
                {
                    foreach($query_all_amarr['result']as $res)
                    {
                        $location_arr[]=array("id"=>"A||".$res->am_email,"title"=>"AM - ".$res->am_name);
                    }
                
                }
                $teamrank_arr[]=array("id"=>'DSE',"title"=>"Sales Executive",'location'=>$location_arr); 
                //$teamrank_arr[]=array("id"=>'IDSE',"title"=>"International Bike Sales Executive",'location'=>$location_arr);
                
                //AM LOCATION DATA
                $location_arr=array();
            
                if(count($zm_arr)>0)
                {
                    foreach($zm_arr as $zm_val)
                    {
                        $location_arr[]=$zm_val;
                    }
                }
                if(count($rm_arr)>0)
                {
                    foreach($rm_arr as $rm_val)
                    {
                        $location_arr[]=$rm_val;
                    }
                }
                $teamrank_arr[]=array("id"=>"AM","title"=>"Area Manager",'location'=>$location_arr);
                
                //RM LOCATION DATA
                $location_arr=array();
                $location_arr[]=array("id"=>"P","title"=>"Pan India");
                if(count($zm_arr)>0)
                {
                    foreach($zm_arr as $zm_val)
                    {
                        $location_arr[]=$zm_val;
                    }
                }
                $teamrank_arr[]=array("id"=>"RM","title"=>"Regional Manager",'location'=>$location_arr);
                break; 
            case '6':                
                $location_arr=array();
            
                $rm_arr=array();            
                //DSE LOCATION DATA
            
                //RM
                $rm_data=array("id"=>"R||".$parent->current_user_data->emailid,"title"=>"Regional Manager - ".$parent->current_user_data->fname." ".$parent->current_user_data->lname);
                $rm_arr[]=$rm_data;
                $location_arr[]=$rm_data;
            
                //AM
                $query_all_amarr=$parent->selectQuery(DMS_DEALER,"am_name,am_email "," status='1' AND rm_email='".$parent->current_user_data->emailid."' AND is_deleted='0' GROUP BY am_email","");
                if($query_all_amarr['nr']>0)
                {
                    foreach($query_all_amarr['result']as $res)
                    {
                        $location_arr[]=array("id"=>"A||".$res->am_email,"title"=>"AM - ".$res->am_name);
                    }
                
                }
                $teamrank_arr[]=array("id"=>'DSE',"title"=>"Sales Executive",'location'=>$location_arr); 
                //$teamrank_arr[]=array("id"=>'IDSE',"title"=>"International Bike Sales Executive",'location'=>$location_arr);
                
                //AM LOCATION DATA
                $location_arr=array();
            
                if(count($rm_arr)>0)
                {
                    foreach($rm_arr as $rm_val)
                    {
                        $location_arr[]=$rm_val;
                    }
                }                
                $teamrank_arr[]=array("id"=>"AM","title"=>"Area Manager",'location'=>$location_arr);            
                break;   
            case '7':                
                $location_arr=array(); 
                $location_arr[]=array("id"=>"A||".$parent->current_user_data->emailid,"title"=>"AM - ".$parent->current_user_data->fname." ".$parent->current_user_data->lname); 
                $teamrank_arr[]=array("id"=>'DSE',"title"=>"Sales Executive",'location'=>$location_arr);       
                //$teamrank_arr[]=array("id"=>'IDSE',"title"=>"International Bike Sales Executive",'location'=>$location_arr);
                break;     
        }
        return $teamrank_arr;
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
    
    /* /////////////////////////////// LOCATION //////////////////////////////////////// */
	private function location($parent)
    { 
        globalfunc::checkMethodType($parent,array('GET'));	
        globalfunc::checkAuthrozation($parent,array('type'=>'user'));
        projectfunc::authorizedRole($parent,array(3,4,5,6,7,8,9,10,11,12,13,14,15));
        
        $data_arr=array('myrank'=>array(),'teamrank'=>array());
        $myrank_arr=array();
        $teamrank_arr=array();
        switch($parent->current_user_role)
        {
            case '3': //HO
                $myrank_arr=array(); 
                $teamrank_arr=$this->generate_location_arr($parent);                
                break;
            case '4': //ZM
                $myrank_arr[]=array("id"=>"P","title"=>"Pan India");
                $teamrank_arr=$this->generate_location_arr($parent);
                break;
            case '5'://TRAINER
                $myrank_arr=array(); 
                $teamrank_arr=$this->generate_location_arr($parent);
                break;
            case '6': //RM
                $myrank_arr[]=array("id"=>"P","title"=>"Pan India");
                $myrank_arr[]=array("id"=>"Z","title"=>"Zone");
                $teamrank_arr=$this->generate_location_arr($parent);
                break;
            case '7': //AM
                $myrank_arr[]=array("id"=>"P","title"=>"Pan India");
                $myrank_arr[]=array("id"=>"Z","title"=>"Zone");
                $myrank_arr[]=array("id"=>"R","title"=>"Region");
                $teamrank_arr=$this->generate_location_arr($parent);   
                break;
            case '8':
            case '9':
            case '10':
            case '11':
            case '12':
            case '13':
                $myrank_arr[]=array("id"=>"P","title"=>"Pan India");
                $myrank_arr[]=array("id"=>"Z","title"=>"Zone");
                $myrank_arr[]=array("id"=>"R","title"=>"Region");
                $myrank_arr[]=array("id"=>"A","title"=>"Area"); 
                $teamrank_arr=$this->generate_location_arr($parent);
                break;
            case '14':
                $myrank_arr=array();
                $teamrank_arr=$this->generate_location_arr($parent);            
                break;
            case '15':
                $myrank_arr[]=array("id"=>"P","title"=>"Pan India");
                $myrank_arr[]=array("id"=>"Z","title"=>"Zone");
                $myrank_arr[]=array("id"=>"R","title"=>"Region");
                $myrank_arr[]=array("id"=>"A","title"=>"Area"); 
                $teamrank_arr=$this->generate_location_arr($parent);
                break;  
        }
        $data_arr=array('myrank'=>$myrank_arr,'teamrank'=>$teamrank_arr);
        $parent->response(array('response'=>'success','data'=>$data_arr,'message'=>$this->page_data['success1']),200);
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
            
    /* /////////////////////////////// MY ASSESSMENT //////////////////////////////////////// */
	private function myassessment($parent)
    {
        globalfunc::checkMethodType($parent,array('POST'));	
        globalfunc::checkParamCount($parent,1);
		globalfunc::checkAuthrozation($parent,array('type'=>'user'));
        projectfunc::authorizedRole($parent,array(4,5,6,7,8,9,10,11,12,13,15));
        
        $REQUEST_KEYS=array('month'=>'string','year'=>'number','location'=>'string');
		globalfunc::mandatoryKey($parent,$REQUEST_KEYS);
		$month=escape_str($parent->_request['month']);
        $year=escape_str($parent->_request['year']);
        $location=escape_str($parent->_request['location']);
        
        $obj=new stdClass();
        $obj->month=$month;
        $obj->year=$year;
        $obj->location=$location;
        switch($parent->current_user_role )
        {
            case '4'://zm 
                $this->zmassessment($parent,$obj);
                break;
           case '5'://trainer 
                $this->trainerassessment($parent,$obj);
                break; 
            case '6'://rm 
                $this->rmassessment($parent,$obj);
                break;
            case '7'://am 
                $this->amassessment($parent,$obj);
                break;
            case '8'://normal dse 
            case '9':
            case '10':
            case '11':
            case '12':
            case '13': 
            case '15':    
                $this->dseassessment($parent,$obj);
                break;
        } 
        //$parent->response(array('response'=>'success', 'data'=>$data_arr,'user_data'=>$cur_data_arr,'total_count'=>$total_count,'message'=>$this->page_data['success1'] ),200);
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
    
    /* /////////////////////////////// zmassessment //////////////////////////////////////// */
    private function zmassessment($parent,$obj)
    {
        if(!isset($obj)||!isset($obj->month)||!isset($obj->year)||!isset($obj->location))  globalfunc::callErrorMsg($parent,array('message'=>'INVALID PARAMS - zmassessment')); 
        $date=$obj->year."-".$obj->month."-01";
        $start_date=date('Y-m',strtotime($date))."-01";  
        $end_date=date('Y-m-t',strtotime($date));  
        $date_cond=" AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59'";
        $emailid=$parent->current_user_data->emailid; 
        
        switch($obj->location)
        {
            case "P":
                $user_cond=" AND u.role=4";
            break;
            
            default :
               $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
               break;
        }
        //USER TYPE    
		$user_cond_assess=" AND a.user_type=3 ";
        
        
        //GET ALL ASSESSMENT LIST WHICH PUBLISHED 
        $assessment_str = "SELECT a.assessmentid FROM `".TABLE_ASSESSMENT."` as a  WHERE  a.status='1' AND a.atype='0' AND a.assettypeid='1' ".$user_cond_assess.$date_cond;
        $assessment_arr=$parent->getQueryValue($assessment_str);
        //ASSESSMENT COUNT
        $assessment_count=(count($assessment_arr)>0)?count($assessment_arr):'0';
        
        
        $data_arr=array();
        if(count($assessment_arr)>0)
        {     
            foreach($assessment_arr as $res_assess)
            {
                $assessmentid_arr[]=$res_assess->assessmentid;
            }
            
            //GET ALL 20 USER RANK
            //$parent->executeQuery("SET @rownum := 0");                    
            $get_user_rank_str = "SELECT SUM(highscore)as user_score,u.userid,COALESCE(ar.total_time,'0')as total_time,MAX(au.modified_on) as last_modified,u.role  FROM ".TABLE_USER." as u  JOIN (SELECT * FROM  ".TABLE_ASSESSMENT_USER."  WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") AND highscorearid!=0 ) as au ON u.userid=au.userid  LEFT JOIN (SELECT SUM(time_taken)as total_time,assessmentid,userid FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") GROUP BY userid )as ar ON u.userid=ar.userid WHERE  u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid ORDER BY user_score DESC,ar.total_time ASC,last_modified ASC,u.userid ASC   ";
            $get_user_rank_arr=$parent->getQueryValue($get_user_rank_str);
            if($get_user_rank_arr)
            {
                 foreach($get_user_rank_arr as $res_user)
                 {
                    $user_data_arr=new stdClass();
                    $get_time_str = "SELECT * FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") AND userid='".$res_user->userid."'";
                    $get_time_arr=$parent->getQueryValue($get_time_str);

                    $user_data_arr->user=projectfunc::getuserdata($parent,$res_user->userid);
                    $user_data_arr->rank='0';
                    $user_data_arr->tot_user_score=(count($get_time_arr)>0)?$res_user->user_score:'-';
                    $user_data_arr->percentage_score=(count($get_time_arr)>0)?'0':'-';  
                    
                    $user_data_arr->total_time= $res_user->total_time;
                    $user_data_arr->last_time=(count($get_time_arr)>0)?$res_user->last_modified:'-';
                    $user_data_arr->last_time_str=(count($get_time_arr)>0)?date('Y-m-d',strtotime($res_user->last_modified)):'-';
            
                    
                   $count_assessment_str = "SELECT COUNT(a.assessmentid) as acount FROM `".TABLE_ASSESSMENT."` as a  WHERE a.status='1' AND a.atype='0' AND a.assettypeid='1'  AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59' AND a.enddate>='".$user_data_arr->user->user_created_on."' AND  assessmentid IN (".@implode(',',$assessmentid_arr).") ".$user_cond_assess." ". $assess_user_cond." LIMIT 1";
                    $count_assessment_arr=$parent->getQueryValue($count_assessment_str); 
                    $user_data_arr->percentage_score=(count($assessment_arr)>0)?round($res_user->user_score/$count_assessment_arr[0]->acount):'0';
                    $user_data_arr->percentage=$user_data_arr->percentage_score;
                    
                    if((($assessment_count/$count_assessment_arr[0]->acount)*100)>=MIN_ASSESSMENT_PERCENT)
                    {      
                        $data_arr[]=$user_data_arr;
                    }
                 }
            }
            
            //GET CURRENT USER RANK
            $cur_data_arr=new stdClass();
            $udata=projectfunc::getuserdata($parent,$parent->current_user_id);
            $cur_data_arr->user=$udata;
            foreach($udata as $key=>$res)
            {
                $cur_data_arr->$key=$res;
            } 
                    
            $cur_data_arr->rank='-';
            $cur_data_arr->tot_user_score='-';
            $cur_data_arr->percentage_score='-';
            $cur_data_arr->percentage=$cur_data_arr->percentage_score;
            $cur_data_arr->total_time='0';
            $cur_data_arr->last_time='-';
            $cur_data_arr->last_time_str='-';
            if(count($data_arr)>0)
            {
                array_multisort(array_column($data_arr, 'percentage_score'),  SORT_DESC,array_column($data_arr, 'total_time'), SORT_ASC,$data_arr);
                $rank=1;
                foreach($data_arr as $key=>$res)
                { 
                    $data_arr[$key]->rank=$rank;                    
                    if($res->user->userid==$parent->current_user_id)
                    {
                        $cur_data_arr->rank=$rank;
                        $cur_data_arr->tot_user_score=$res->tot_user_score;
                        $cur_data_arr->percentage_score=$res->percentage_score;
                        $cur_data_arr->percentage=$res->percentage;
                        //$cur_data_arr->total_time=(count($get_time_arr)>0)?$get_current_user_rank_arr[0]->total_time:'-';
                        $cur_data_arr->total_time= $res->total_time;
                        $cur_data_arr->last_time= $res->last_time;
                        $cur_data_arr->last_time_str=($res->last_time!='')?date('Y-m-d',strtotime($res->last_time)):'-';
                    }
                    $rank++;
                }
            } 
            
        } else {
            $cur_data_arr=new stdClass();
            $udata=projectfunc::getuserdata($parent,$parent->current_user_id);
            $cur_data_arr->user=$udata;
            foreach($udata as $key=>$res)
            {
                $cur_data_arr->$key=$res;
            } 
            
            $cur_data_arr->rank='-';
            $cur_data_arr->tot_user_score='-';
            $cur_data_arr->percentage_score='-';
            $cur_data_arr->percentage=$cur_data_arr->percentage_score;
            $cur_data_arr->total_time='0';
            $cur_data_arr->last_time='-';
            $cur_data_arr->last_time_str='-';
            $get_count_arr=array();
        }
        
        $total_count=count($data_arr);
        if(count($data_arr)>0)
        {
            $temp=array_chunk($data_arr,20);
            $data_arr=$temp[0];
        }
                
        
        //LOG
        $logstr='LEADERBOARD MY ASSESSMENT';		 
        globalfunc::insertUserLog($parent,$logstr);
        $parent->response(array('response'=>'success','data'=>$data_arr,'user_data'=>$cur_data_arr,'total_count'=>$total_count,'message'=>$this->page_data['success1']),200);    
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
    
    /* /////////////////////////////// trainerassessment //////////////////////////////////////// */
    private function trainerassessment($parent,$obj)
    {
        if(!isset($obj)||!isset($obj->month)||!isset($obj->year)||!isset($obj->location))  globalfunc::callErrorMsg($parent,array('message'=>'INVALID PARAMS - trainerassessment')); 
        $date=$obj->year."-".$obj->month."-01";
        $start_date=date('Y-m',strtotime($date))."-01";  
        $end_date=date('Y-m-t',strtotime($date));  
        $date_cond=" AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59'";
        $emailid=$parent->current_user_data->emailid; 
        
        switch($obj->location)
        {
            case "P":
                $user_cond=" AND u.role=5";
            break;
            
            default :
               $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
               break;
        }
        //USER TYPE    
		$user_cond_assess=" AND a.user_type=4 ";
        
        
        //GET ALL ASSESSMENT LIST WHICH PUBLISHED 
        $assessment_str = "SELECT a.assessmentid FROM `".TABLE_ASSESSMENT."` as a  WHERE  a.status='1' AND a.atype='0' AND a.assettypeid='1' ".$user_cond_assess.$date_cond;
        $assessment_arr=$parent->getQueryValue($assessment_str);
        //ASSESSMENT COUNT
        $assessment_count=(count($assessment_arr)>0)?count($assessment_arr):'0';
        
        
        $data_arr=array();
        if(count($assessment_arr)>0)
        {     
            foreach($assessment_arr as $res_assess)
            {
                $assessmentid_arr[]=$res_assess->assessmentid;
            }
            
            //GET ALL 20 USER RANK
            //$parent->executeQuery("SET @rownum := 0");                    
            $get_user_rank_str = "SELECT SUM(highscore)as user_score,u.userid,COALESCE(ar.total_time,'0')as total_time,MAX(au.modified_on) as last_modified,u.role  FROM ".TABLE_USER." as u  JOIN (SELECT * FROM  ".TABLE_ASSESSMENT_USER."  WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") AND highscorearid!=0 ) as au ON u.userid=au.userid  LEFT JOIN (SELECT SUM(time_taken)as total_time,assessmentid,userid FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") GROUP BY userid )as ar ON u.userid=ar.userid WHERE  u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid ORDER BY user_score DESC,ar.total_time ASC,last_modified ASC,u.userid ASC   ";
            $get_user_rank_arr=$parent->getQueryValue($get_user_rank_str);
            if($get_user_rank_arr)
            {
                 foreach($get_user_rank_arr as $res_user)
                 {
                    $user_data_arr=new stdClass();
                    $get_time_str = "SELECT * FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") AND userid='".$res_user->userid."'";
                    $get_time_arr=$parent->getQueryValue($get_time_str);

                    $user_data_arr->user=projectfunc::getuserdata($parent,$res_user->userid);
                    $user_data_arr->rank='0';
                    $user_data_arr->tot_user_score=(count($get_time_arr)>0)?$res_user->user_score:'-';
                    $user_data_arr->percentage_score=(count($get_time_arr)>0)?'0':'-';  
                    
                    $user_data_arr->total_time= $res_user->total_time;
                    $user_data_arr->last_time=(count($get_time_arr)>0)?$res_user->last_modified:'-';
                    $user_data_arr->last_time_str=(count($get_time_arr)>0)?date('Y-m-d',strtotime($res_user->last_modified)):'-';
            
                    
                   $count_assessment_str = "SELECT COUNT(a.assessmentid) as acount FROM `".TABLE_ASSESSMENT."` as a  WHERE a.status='1' AND a.atype='0' AND a.assettypeid='1'  AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59' AND a.enddate>='".$user_data_arr->user->user_created_on."' AND  assessmentid IN (".@implode(',',$assessmentid_arr).") ".$user_cond_assess." ". $assess_user_cond." LIMIT 1";
                    $count_assessment_arr=$parent->getQueryValue($count_assessment_str); 
                    $user_data_arr->percentage_score=(count($assessment_arr)>0)?round($res_user->user_score/$count_assessment_arr[0]->acount):'0';
                    $user_data_arr->percentage=$user_data_arr->percentage_score;
                    
                    if((($assessment_count/$count_assessment_arr[0]->acount)*100)>=MIN_ASSESSMENT_PERCENT)
                    {      
                        $data_arr[]=$user_data_arr;
                    }
                 }
            }
            
            //GET CURRENT USER RANK
            $cur_data_arr=new stdClass();
            $udata=projectfunc::getuserdata($parent,$parent->current_user_id);
            $cur_data_arr->user=$udata;
            foreach($udata as $key=>$res)
            {
                $cur_data_arr->$key=$res;
            } 
                    
            $cur_data_arr->rank='-';
            $cur_data_arr->tot_user_score='-';
            $cur_data_arr->percentage_score='-';
            $cur_data_arr->percentage=$cur_data_arr->percentage_score;
            $cur_data_arr->total_time='0';
            $cur_data_arr->last_time='-';
            $cur_data_arr->last_time_str='-';
            if(count($data_arr)>0)
            {
                array_multisort(array_column($data_arr, 'percentage_score'),  SORT_DESC,array_column($data_arr, 'total_time'), SORT_ASC,$data_arr);
                $rank=1;
                foreach($data_arr as $key=>$res)
                { 
                    $data_arr[$key]->rank=$rank;                    
                    if($res->user->userid==$parent->current_user_id)
                    {
                        $cur_data_arr->rank=$rank;
                        $cur_data_arr->tot_user_score=$res->tot_user_score;
                        $cur_data_arr->percentage_score=$res->percentage_score;
                        $cur_data_arr->percentage=$res->percentage;
                        //$cur_data_arr->total_time=(count($get_time_arr)>0)?$get_current_user_rank_arr[0]->total_time:'-';
                        $cur_data_arr->total_time= $res->total_time;
                        $cur_data_arr->last_time= $res->last_time;
                        $cur_data_arr->last_time_str=($res->last_time!='')?date('Y-m-d',strtotime($res->last_time)):'-';
                    }
                    $rank++;
                }
            } 
            
        } else {
            $cur_data_arr=new stdClass();
            $udata=projectfunc::getuserdata($parent,$parent->current_user_id);
            $cur_data_arr->user=$udata;
            foreach($udata as $key=>$res)
            {
                $cur_data_arr->$key=$res;
            } 
            
            $cur_data_arr->rank='-';
            $cur_data_arr->tot_user_score='-';
            $cur_data_arr->percentage_score='-';
            $cur_data_arr->percentage=$cur_data_arr->percentage_score;
            $cur_data_arr->total_time='0';
            $cur_data_arr->last_time='-';
            $cur_data_arr->last_time_str='-';
            $get_count_arr=array();
        }
        
        $total_count=count($data_arr);
        if(count($data_arr)>0)
        {
            $temp=array_chunk($data_arr,20);
            $data_arr=$temp[0];
        }
                
        
        //LOG
        $logstr='LEADERBOARD MY ASSESSMENT';		 
        globalfunc::insertUserLog($parent,$logstr);
        $parent->response(array('response'=>'success','data'=>$data_arr,'user_data'=>$cur_data_arr,'total_count'=>$total_count,'message'=>$this->page_data['success1']),200);    
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
    
    /* /////////////////////////////// rmassessment //////////////////////////////////////// */
    private function rmassessment($parent,$obj)
    {
        if(!isset($obj)||!isset($obj->month)||!isset($obj->year)||!isset($obj->location))  globalfunc::callErrorMsg($parent,array('message'=>'INVALID PARAMS - rmassessment'));
        
        $date=$obj->year."-".$obj->month."-01";
        $start_date=date('Y-m',strtotime($date))."-01";  
        $end_date=date('Y-m-t',strtotime($date));  
        $date_cond=" AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59'";
        $emailid=$parent->current_user_data->emailid; 
        
        switch($obj->location)
        {
            case "P":
                $user_cond=" AND u.role=6";
            break;
            case "Z":
                //COND
                $query_all_amarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,rm_email "," status='1' AND is_deleted='0' AND zm_email IN (SELECT zm_email FROM ".DMS_DEALER." WHERE rm_email='".$emailid."' ) GROUP BY rm_email","");  
                $emailid_arr=array();
                if($query_all_amarr['nr']>0)
                {
                    foreach($query_all_amarr['result'] as $res)
                    {
                        $emailid_arr[]="'".$res->rm_email."'";
                    }
                    $user_cond=" AND u.role=6 AND u.emailid IN (".implode(',',$emailid_arr).")";
                }else
                {
                    $user_cond=" AND u.role=6 AND u.emailid IN ('".$emailid."')";
                }
               break; 
            default :
               $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
               break;
        }
        //USER TYPE    
		$user_cond_assess=" AND a.user_type=5 ";
         
        //GET ALL ASSESSMENT LIST WHICH PUBLISHED 
        $assessment_str = "SELECT a.assessmentid FROM `".TABLE_ASSESSMENT."` as a  WHERE  a.status='1' AND a.atype='0' AND a.assettypeid='1' ".$user_cond_assess.$date_cond;
        $assessment_arr=$parent->getQueryValue($assessment_str);
        //ASSESSMENT COUNT
        $assessment_count=(count($assessment_arr)>0)?count($assessment_arr):'0';
    
        $data_arr=array();
        if(count($assessment_arr)>0)
        {     
            foreach($assessment_arr as $res_assess)
            {
                $assessmentid_arr[]=$res_assess->assessmentid;
            }
            
            //GET ALL 20 USER RANK
            //$parent->executeQuery("SET @rownum := 0");                    
            $get_user_rank_str = "SELECT SUM(highscore)as user_score,u.userid,COALESCE(ar.total_time,'0')as total_time,MAX(au.modified_on) as last_modified,u.role  FROM ".TABLE_USER." as u  JOIN (SELECT * FROM  ".TABLE_ASSESSMENT_USER."  WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") AND highscorearid!=0 ) as au ON u.userid=au.userid  LEFT JOIN (SELECT SUM(time_taken)as total_time,assessmentid,userid FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") GROUP BY userid )as ar ON u.userid=ar.userid WHERE  u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid ORDER BY user_score DESC,ar.total_time ASC,last_modified ASC,u.userid ASC   ";
            $get_user_rank_arr=$parent->getQueryValue($get_user_rank_str);
            if($get_user_rank_arr)
            {
                 foreach($get_user_rank_arr as $res_user)
                 {
                    $user_data_arr=new stdClass();
                    $get_time_str = "SELECT * FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") AND userid='".$res_user->userid."'";
                    $get_time_arr=$parent->getQueryValue($get_time_str);

                    $user_data_arr->user=projectfunc::getuserdata($parent,$res_user->userid);
                    $user_data_arr->rank='0';
                    $user_data_arr->tot_user_score=(count($get_time_arr)>0)?$res_user->user_score:'-';
                    $user_data_arr->percentage_score=(count($get_time_arr)>0)?'0':'-';  
                    
                    $user_data_arr->total_time= $res_user->total_time;
                    $user_data_arr->last_time=(count($get_time_arr)>0)?$res_user->last_modified:'-';
                    $user_data_arr->last_time_str=(count($get_time_arr)>0)?date('Y-m-d',strtotime($res_user->last_modified)):'-';
            
                    
                   $count_assessment_str = "SELECT COUNT(a.assessmentid) as acount FROM `".TABLE_ASSESSMENT."` as a  WHERE a.status='1' AND a.atype='0' AND a.assettypeid='1'  AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59' AND a.enddate>='".$user_data_arr->user->user_created_on."' AND  assessmentid IN (".@implode(',',$assessmentid_arr).") ".$user_cond_assess." ". $assess_user_cond." LIMIT 1";
                    $count_assessment_arr=$parent->getQueryValue($count_assessment_str); 
                    $user_data_arr->percentage_score=(count($assessment_arr)>0)?round($res_user->user_score/$count_assessment_arr[0]->acount):'0';
                    $user_data_arr->percentage=$user_data_arr->percentage_score;
                                                           
                    if((($assessment_count/$count_assessment_arr[0]->acount)*100)>=MIN_ASSESSMENT_PERCENT)
                    {      
                        $data_arr[]=$user_data_arr;
                    }
                 }
            }
            
            //GET CURRENT USER RANK
            $cur_data_arr=new stdClass();
            $udata=projectfunc::getuserdata($parent,$parent->current_user_id);
            $cur_data_arr->user=$udata;
            foreach($udata as $key=>$res)
            {
                $cur_data_arr->$key=$res;
            } 
                    
            $cur_data_arr->rank='-';
            $cur_data_arr->tot_user_score='-';
            $cur_data_arr->percentage_score='-';
            $cur_data_arr->percentage=$cur_data_arr->percentage_score;
            $cur_data_arr->total_time='0';
            $cur_data_arr->last_time='-';
            $cur_data_arr->last_time_str='-';
            if(count($data_arr)>0)
            {
                array_multisort(array_column($data_arr, 'percentage_score'),  SORT_DESC,array_column($data_arr, 'total_time'), SORT_ASC,$data_arr);
                $rank=1;
                foreach($data_arr as $key=>$res)
                { 
                    $data_arr[$key]->rank=$rank;                    
                    if($res->user->userid==$parent->current_user_id)
                    {
                        $cur_data_arr->rank=$rank;
                        $cur_data_arr->tot_user_score=$res->tot_user_score;
                        $cur_data_arr->percentage_score=$res->percentage_score;
                        $cur_data_arr->percentage=$res->percentage;
                        //$cur_data_arr->total_time=(count($get_time_arr)>0)?$get_current_user_rank_arr[0]->total_time:'-';
                        $cur_data_arr->total_time= $res->total_time;
                        $cur_data_arr->last_time= $res->last_time;
                        $cur_data_arr->last_time_str=($res->last_time!='')?date('Y-m-d',strtotime($res->last_time)):'-';
                    }
                    $rank++;
                }
            } 
            
        } else {
            $cur_data_arr=new stdClass();
            $udata=projectfunc::getuserdata($parent,$parent->current_user_id);
            $cur_data_arr->user=$udata;
            foreach($udata as $key=>$res)
            {
                $cur_data_arr->$key=$res;
            } 
            
            $cur_data_arr->rank='-';
            $cur_data_arr->tot_user_score='-';
            $cur_data_arr->percentage_score='-';
            $cur_data_arr->percentage=$cur_data_arr->percentage_score;
            $cur_data_arr->total_time='0';
            $cur_data_arr->last_time='-';
            $cur_data_arr->last_time_str='-';
            $get_count_arr=array();
        }
        
        $total_count=count($data_arr);
        if(count($data_arr)>0)
        {
            $temp=array_chunk($data_arr,20);
            $data_arr=$temp[0];
        }
                
        
        //LOG
        $logstr='LEADERBOARD MY ASSESSMENT';		 
        globalfunc::insertUserLog($parent,$logstr);
        $parent->response(array('response'=>'success','data'=>$data_arr,'user_data'=>$cur_data_arr,'total_count'=>$total_count,'message'=>$this->page_data['success1']),200);
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
    
    /* /////////////////////////////// amassessment //////////////////////////////////////// */
    private function amassessment($parent,$obj)
    {
        if(!isset($obj)||!isset($obj->month)||!isset($obj->year)||!isset($obj->location))  globalfunc::callErrorMsg($parent,array('message'=>'INVALID PARAMS - amassessment'));
        
        $date=$obj->year."-".$obj->month."-01";
        $start_date=date('Y-m',strtotime($date))."-01";  
        $end_date=date('Y-m-t',strtotime($date));  
        $date_cond=" AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59'";
        $emailid=$parent->current_user_data->emailid; 
        
        switch($obj->location)
        {
            case "P":
                $user_cond=" AND u.role=7";
            break;
            case "Z":
                //COND
                $query_all_amarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,am_email "," status='1' AND is_deleted='0' AND zm_email IN (SELECT zm_email FROM ".DMS_DEALER." WHERE am_email='".$emailid."' ) GROUP BY am_email","");  
                $emailid_arr=array();
                if($query_all_amarr['nr']>0)
                {
                    foreach($query_all_amarr['result'] as $res)
                    {
                        $emailid_arr[]="'".$res->am_email."'";
                    }
                    $user_cond=" AND u.role=7 AND u.emailid IN (".implode(',',$emailid_arr).")";
                }else
                {
                    $user_cond=" AND u.role=7 AND u.emailid IN ('".$emailid."')";
                }
               break; 
            case "R":
                $query_all_amarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,am_email "," status='1' AND is_deleted='0' AND rm_email IN (SELECT rm_email FROM ".DMS_DEALER." WHERE am_email='".$emailid."' ) GROUP BY am_email","");  
                $emailid_arr=array();
                if($query_all_amarr['nr']>0)
                {
                    foreach($query_all_amarr['result'] as $res)
                    {
                        $emailid_arr[]="'".$res->am_email."'";
                    }
                    $user_cond=" AND u.role=7 AND u.emailid IN (".implode(',',$emailid_arr).")";
                }else
                {
                    $user_cond=" AND u.role=7 AND u.emailid IN ('".$emailid."')";
                } 
                break; 
            default :
               $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
               break;
        }
        //USER TYPE    
		$user_cond_assess=" AND a.user_type=6 ";
          
        //GET ALL ASSESSMENT LIST WHICH PUBLISHED 
        $assessment_str = "SELECT a.assessmentid FROM `".TABLE_ASSESSMENT."` as a  WHERE  a.status='1' AND a.atype='0' AND a.assettypeid='1' ".$user_cond_assess.$date_cond;
        $assessment_arr=$parent->getQueryValue($assessment_str);
        //ASSESSMENT COUNT
        $assessment_count=(count($assessment_arr)>0)?count($assessment_arr):'0';
        
        
        $data_arr=array();
        if(count($assessment_arr)>0)
        {     
            foreach($assessment_arr as $res_assess)
            {
                $assessmentid_arr[]=$res_assess->assessmentid;
            }
            
            //GET ALL 20 USER RANK
            $get_user_rank_str = "SELECT SUM(highscore)as user_score,u.userid,COALESCE(ar.total_time,'0')as total_time,MAX(au.modified_on) as last_modified,u.role  FROM ".TABLE_USER." as u  JOIN (SELECT * FROM  ".TABLE_ASSESSMENT_USER."  WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") AND highscorearid!=0 ) as au ON u.userid=au.userid  LEFT JOIN (SELECT SUM(time_taken)as total_time,assessmentid,userid FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") GROUP BY userid )as ar ON u.userid=ar.userid WHERE  u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid ORDER BY user_score DESC,ar.total_time ASC,last_modified ASC,u.userid ASC   ";
            $get_user_rank_arr=$parent->getQueryValue($get_user_rank_str);
            if($get_user_rank_arr)
            {
                 foreach($get_user_rank_arr as $res_user)
                 {
                    $user_data_arr=new stdClass();
                    $get_time_str = "SELECT * FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") AND userid='".$res_user->userid."'";
                    $get_time_arr=$parent->getQueryValue($get_time_str);

                    $user_data_arr->user=projectfunc::getuserdata($parent,$res_user->userid);
                    $user_data_arr->rank='0';
                    $user_data_arr->tot_user_score=(count($get_time_arr)>0)?$res_user->user_score:'-';
                    $user_data_arr->percentage_score=(count($get_time_arr)>0)?'0':'-';  
                    
                    $user_data_arr->total_time= $res_user->total_time;
                    $user_data_arr->last_time=(count($get_time_arr)>0)?$res_user->last_modified:'-';
                    $user_data_arr->last_time_str=(count($get_time_arr)>0)?date('Y-m-d',strtotime($res_user->last_modified)):'-';
            
                    
                   $count_assessment_str = "SELECT COUNT(a.assessmentid) as acount FROM `".TABLE_ASSESSMENT."` as a  WHERE a.status='1' AND a.atype='0' AND a.assettypeid='1'  AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59' AND a.enddate>='".$user_data_arr->user->user_created_on."' AND  assessmentid IN (".@implode(',',$assessmentid_arr).") ".$user_cond_assess." ". $assess_user_cond." LIMIT 1";
                    $count_assessment_arr=$parent->getQueryValue($count_assessment_str); 
                    $user_data_arr->percentage_score=(count($assessment_arr)>0)?round($res_user->user_score/$count_assessment_arr[0]->acount):'0';
                    $user_data_arr->percentage=$user_data_arr->percentage_score;
                                                           
                    if((($assessment_count/$count_assessment_arr[0]->acount)*100)>=MIN_ASSESSMENT_PERCENT)
                    {      
                        $data_arr[]=$user_data_arr;
                    }
                 }
            }
            
            //GET CURRENT USER RANK
            $cur_data_arr=new stdClass();
            $udata=projectfunc::getuserdata($parent,$parent->current_user_id);
            $cur_data_arr->user=$udata;
            foreach($udata as $key=>$res)
            {
                $cur_data_arr->$key=$res;
            } 
                    
            $cur_data_arr->rank='-';
            $cur_data_arr->tot_user_score='-';
            $cur_data_arr->percentage_score='-';
            $cur_data_arr->percentage=$cur_data_arr->percentage_score;
            $cur_data_arr->total_time='0';
            $cur_data_arr->last_time='-';
            $cur_data_arr->last_time_str='-';
            if(count($data_arr)>0)
            {
                array_multisort(array_column($data_arr, 'percentage_score'),  SORT_DESC,array_column($data_arr, 'total_time'), SORT_ASC,$data_arr);
                $rank=1;
                foreach($data_arr as $key=>$res)
                { 
                    $data_arr[$key]->rank=$rank;                    
                    if($res->user->userid==$parent->current_user_id)
                    {
                        $cur_data_arr->rank=$rank;
                        $cur_data_arr->tot_user_score=$res->tot_user_score;
                        $cur_data_arr->percentage_score=$res->percentage_score;
                        $cur_data_arr->percentage=$res->percentage;
                        //$cur_data_arr->total_time=(count($get_time_arr)>0)?$get_current_user_rank_arr[0]->total_time:'-';
                        $cur_data_arr->total_time= $res->total_time;
                        $cur_data_arr->last_time= $res->last_time;
                        $cur_data_arr->last_time_str=($res->last_time!='')?date('Y-m-d',strtotime($res->last_time)):'-';
                    }
                    $rank++;
                }
            } 
            
        } else {
            $cur_data_arr=new stdClass();
            $udata=projectfunc::getuserdata($parent,$parent->current_user_id);
            $cur_data_arr->user=$udata;
            foreach($udata as $key=>$res)
            {
                $cur_data_arr->$key=$res;
            } 
            
            $cur_data_arr->rank='-';
            $cur_data_arr->tot_user_score='-';
            $cur_data_arr->percentage_score='-';
            $cur_data_arr->percentage=$cur_data_arr->percentage_score;
            $cur_data_arr->total_time='0';
            $cur_data_arr->last_time='-';
            $cur_data_arr->last_time_str='-';
            $get_count_arr=array();
        }
        
        $total_count=count($data_arr);
        if(count($data_arr)>0)
        {
            $temp=array_chunk($data_arr,20);
            $data_arr=$temp[0];
        }
                
        
        //LOG
        $logstr='LEADERBOARD MY ASSESSMENT';		 
        globalfunc::insertUserLog($parent,$logstr);
        $parent->response(array('response'=>'success','data'=>$data_arr,'user_data'=>$cur_data_arr,'total_count'=>$total_count,'message'=>$this->page_data['success1']),200);
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
    
    /* /////////////////////////////// dseassessment //////////////////////////////////////// */
    private function dseassessment($parent,$obj)
    {
        if(!isset($obj)||!isset($obj->month)||!isset($obj->year)||!isset($obj->location))  globalfunc::callErrorMsg($parent,array('message'=>'INVALID PARAMS - dseassessment'));
         
        $dealer_code=$parent->current_user_data->dealer_code;  
        
        $date=$obj->year."-".$obj->month."-01";
        $start_date=date('Y-m',strtotime($date))."-01";  
        $end_date=date('Y-m-t',strtotime($date));  
        $date_cond=" AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59'";
        switch($obj->location)
        {
            case "P":
                $join_cond='';
                $area_cond='';
            break;
            case "Z":
                //COND
                $query_allareadealerarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,zm_email ","  dealer_code='".$dealer_code."'  AND status='1' AND is_deleted='0'","1");      
                $dealer_arr=array();
                if($query_allareadealerarr['nr']) 
                {
                    $join_cond="JOIN ".DMS_DEALER." as dd ON dd.dealer_code=u.dealer_code ";
                    $area_cond=" and dd.zm_email ='".$query_allareadealerarr['result'][0]->zm_email."'";
                }else
                {
                    $area_cond=" and u.dealer_code IN ('')";
                }
               break; 
            case "R":
                //COND
               $query_allareadealerarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,rm_email ","  dealer_code='".$dealer_code."'  AND status='1' AND is_deleted='0'","1");       
               $dealer_arr=array();
               if($query_allareadealerarr['nr']) 
               {
                   $join_cond="JOIN ".DMS_DEALER." as dd ON dd.dealer_code=u.dealer_code ";
                   $area_cond=" and dd.rm_email ='".$query_allareadealerarr['result'][0]->rm_email."'";
               }else
               {
                   $area_cond=" and u.dealer_code IN ('')";
               }
               break;  
            case "A":
                
                //COND
                $query_allareadealerarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,am_email ","  dealer_code='".$dealer_code."'  AND status='1' AND is_deleted='0'","1");       
                $dealer_arr=array();
                if($query_allareadealerarr['nr']) 
                {
                    $join_cond="JOIN ".DMS_DEALER." as dd ON dd.dealer_code=u.dealer_code ";
                    $area_cond=" and dd.am_email ='".$query_allareadealerarr['result'][0]->am_email."'";
                }else
                {
                    $area_cond=" and u.dealer_code IN ('')";
                }
               break;    
            default :
               $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
               break;
       }
                
		
        //NEW ROLE CONDITION
        $user_cond_assess=($parent->current_user_role>7 && $parent->current_user_role<14)?" AND a.user_type=0 ":" AND a.user_type=1 ";
        $user_cond=($parent->current_user_role>7 && $parent->current_user_role<14)?" AND (u.role>7 AND u.role<14) ":" AND u.role=15 ";
        
        
        //GET ALL ASSESSMENT LIST WHICH PUBLISHED 
        $assessment_str = "SELECT a.assessmentid FROM `".TABLE_ASSESSMENT."` as a  WHERE  a.status='1' AND a.atype='0' AND a.assettypeid='1' ".$user_cond_assess.$date_cond;
        $assessment_arr=$parent->getQueryValue($assessment_str);
        //ASSESSMENT COUNT
        $assessment_count=(count($assessment_arr)>0)?count($assessment_arr):'0';
        
        
        $data_arr=array();
        if(count($assessment_arr)>0)
        {     
            foreach($assessment_arr as $res_assess)
            {
                $assessmentid_arr[]=$res_assess->assessmentid;
            }
            
            //GET ALL 20 USER RANK
            //$parent->executeQuery("SET @rownum := 0");                    
            $get_user_rank_str = "SELECT SUM(highscore)as user_score,u.userid,COALESCE(ar.total_time,'0')as total_time,MAX(au.modified_on) as last_modified,u.dealer_code,u.role  FROM ".TABLE_USER." as u JOIN ".DMS_DEALER." d ON u.dealer_code=d.dealer_code JOIN (SELECT * FROM  ".TABLE_ASSESSMENT_USER."  WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") AND highscorearid!=0 ) as au ON u.userid=au.userid  LEFT JOIN (SELECT SUM(time_taken)as total_time,assessmentid,userid FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") GROUP BY userid )as ar ON u.userid=ar.userid ".$join_cond."  WHERE  u.is_deleted=0 AND u.status=1 ".$user_cond." ".$area_cond." GROUP BY u.userid ORDER BY user_score DESC,ar.total_time ASC,last_modified ASC,u.userid ASC   ";
            $get_user_rank_arr=$parent->getQueryValue($get_user_rank_str);
            if($get_user_rank_arr)
            {
                 foreach($get_user_rank_arr as $res_user)
                 {
                    $user_data_arr=new stdClass();
                    $get_time_str = "SELECT * FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") AND userid='".$res_user->userid."'";
                    $get_time_arr=$parent->getQueryValue($get_time_str);

                    $getuser_data= projectfunc::getuserdata($parent,$res_user->userid);  
                    $user_data_arr->user=$getuser_data;
                    $user_data_arr->rank='0';
                    $user_data_arr->tot_user_score=(count($get_time_arr)>0)?$res_user->user_score:'-';
                    $user_data_arr->percentage_score=(count($get_time_arr)>0)?'0':'-';  
                    
                    $user_data_arr->total_time= $res_user->total_time;
                    $user_data_arr->last_time=(count($get_time_arr)>0)?$res_user->last_modified:'-';
                    $user_data_arr->last_time_str=(count($get_time_arr)>0)?date('Y-m-d',strtotime($res_user->last_modified)):'-';
                    
                    //GET EACH USER COND
                   
                    $user_cond=array();
//                    if($getuser_data->role)
//                    {
//                        $user_cond[]=" (a.filter_role LIKE '%".$getuser_data->role."%' OR a.filter_role='')";
//                    }
                    if($getuser_data->zone)
                    {
                        $user_cond[]=" (a.filter_zone LIKE '%".$getuser_data->zone."%' OR a.filter_zone='')";
                    }
                    if($getuser_data->city)
                    {
                        $user_cond[]=" (a.filter_region LIKE '%".$getuser_data->city."%' OR a.filter_region='')";
                    }
                    if($getuser_data->dealer_code)
                    {
                        $user_cond[]=" (a.filter_dealer LIKE '%".$getuser_data->dealer_code."%'  OR a.filter_dealer='')";
                    }   
                    if($getuser_data->role>7)
                    {
                        $user_cond[]=" (a.segment = '".$getuser_data->segment."' OR a.segment =0)";
                    }
                    $assess_user_cond=" AND ".@implode(' AND ',$user_cond);
                    
                    
                   $count_assessment_str = "SELECT COUNT(a.assessmentid) as acount FROM `".TABLE_ASSESSMENT."` as a  WHERE a.status='1' AND a.atype='0' AND a.assettypeid='1'  AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59' AND a.enddate>='".$user_data_arr->user->user_created_on."' AND  assessmentid IN (".@implode(',',$assessmentid_arr).") ".$user_cond_assess." ". $assess_user_cond." LIMIT 1";
                    $count_assessment_arr=$parent->getQueryValue($count_assessment_str); 
                    $user_data_arr->percentage_score=(count($assessment_arr)>0)?round($res_user->user_score/$count_assessment_arr[0]->acount):'0';
                    $user_data_arr->percentage=$user_data_arr->percentage_score;
            
                    if((($assessment_count/$count_assessment_arr[0]->acount)*100)>=MIN_ASSESSMENT_PERCENT)
                    {      
                        $data_arr[]=$user_data_arr;
                    }
                 }
            }
            
            //GET CURRENT USER RANK
            $cur_data_arr=new stdClass();
            $udata=projectfunc::getuserdata($parent,$parent->current_user_id);
            $cur_data_arr->user=$udata;
            foreach($udata as $key=>$res)
            {
                $cur_data_arr->$key=$res;
            } 
                    
            $cur_data_arr->rank='-';
            $cur_data_arr->tot_user_score='-';
            $cur_data_arr->percentage_score='-';
            $cur_data_arr->percentage=$cur_data_arr->percentage_score;
            $cur_data_arr->total_time='0';
            $cur_data_arr->last_time='-';
            $cur_data_arr->last_time_str='-';
            if(count($data_arr)>0)
            {
                array_multisort(array_column($data_arr, 'percentage_score'),  SORT_DESC,array_column($data_arr, 'total_time'), SORT_ASC,$data_arr);
                $rank=1;
                foreach($data_arr as $key=>$res)
                { 
                    $data_arr[$key]->rank=$rank;                    
                    if($res->user->userid==$parent->current_user_id)
                    {
                        $cur_data_arr->rank=$rank;
                        $cur_data_arr->tot_user_score=$res->tot_user_score;
                        $cur_data_arr->percentage_score=$res->percentage_score;
                        $cur_data_arr->percentage=$res->percentage;
                        //$cur_data_arr->total_time=(count($get_time_arr)>0)?$get_current_user_rank_arr[0]->total_time:'-';
                        $cur_data_arr->total_time= $res->total_time;
                        $cur_data_arr->last_time= $res->last_time;
                        $cur_data_arr->last_time_str=($res->last_time!='')?date('Y-m-d',strtotime($res->last_time)):'-';
                    }
                    $rank++;
                }
            } 
            
        } else {
            $cur_data_arr=new stdClass();
            $udata=projectfunc::getuserdata($parent,$parent->current_user_id);
            $cur_data_arr->user=$udata;
            foreach($udata as $key=>$res)
            {
                $cur_data_arr->$key=$res;
            } 
            
            $cur_data_arr->rank='-';
            $cur_data_arr->tot_user_score='-';
            $cur_data_arr->percentage_score='-';
            $cur_data_arr->percentage=$cur_data_arr->percentage_score;
            $cur_data_arr->total_time='0';
            $cur_data_arr->last_time='-';
            $cur_data_arr->last_time_str='-';
            $get_count_arr=array();
        }
        
        $total_count=count($data_arr);
        if(count($data_arr)>0)
        {
            $temp=array_chunk($data_arr,20);
            $data_arr=$temp[0];
        }
                
        
        //LOG
        $logstr='LEADERBOARD MY ASSESSMENT';		 
        globalfunc::insertUserLog($parent,$logstr);
        $parent->response(array('response'=>'success','data'=>$data_arr,'user_data'=>$cur_data_arr,'total_count'=>$total_count,  'message'=>$this->page_data['success1']),200);
    
        
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
    
    /* /////////////////////////////// MYTEAM ASSESSMENT //////////////////////////////////////// */
	private function myteamassessment($parent)
    {
        globalfunc::checkMethodType($parent,array('POST'));	
        globalfunc::checkParamCount($parent,1);
		globalfunc::checkAuthrozation($parent,array('type'=>'user'));
        projectfunc::authorizedRole($parent,array(3,4,5,6,7,14));
        
        $REQUEST_KEYS=array('month'=>'string','year'=>'number','role'=>'string','location'=>'string');
		globalfunc::mandatoryKey($parent,$REQUEST_KEYS);        
		$month=escape_str($parent->_request['month']);
        $year=escape_str($parent->_request['year']);
        $role=escape_str($parent->_request['role']);
        $location=escape_str($parent->_request['location']);
        $location_data=@explode('||',$location);
        $location_level=$location_data[0];
        $location_email=isset($location_data[1])?$location_data[1]:'';
        
        $obj=new stdClass();
        $obj->month=$month;
        $obj->year=$year;
        $obj->role=$role;
        $obj->level=$location_level;
        $obj->email=$location_email;
        switch($obj->role)
        {
            case 'DSE': 
            case 'IDSE':    
                $this->dseteamassessment($parent,$obj);
                break;
            case 'AM':
                $this->amteamassessment($parent,$obj);
                break;
            case 'RM': 
                $this->rmteamassessment($parent,$obj);
                break;
           case 'TR': 
                $this->trainerteamassessment($parent,$obj);
                break; 
            case 'ZM': 
                $this->zmteamassessment($parent,$obj);
                break;
            default :
                $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
                break;
            
        } 
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
            
    /* /////////////////////////////// ZMTEAMASSESSMENT //////////////////////////////////////// */
    private function zmteamassessment($parent,$obj)
    {
        if(!isset($obj)||!isset($obj->month)||!isset($obj->year)||!isset($obj->level)||!isset($obj->email))  globalfunc::callErrorMsg($parent,array('message'=>'INVALID PARAMS - zmteamassessment'));
        
        $date=$obj->year."-".$obj->month."-01";
        $start_date=date('Y-m',strtotime($date))."-01";  
        $end_date=date('Y-m-t',strtotime($date));  
        $date_cond=" AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59'";
        $data_arr=array();    
        switch($obj->level)
        {
            case "P":
                $user_cond=" AND u.role=4";
            break;
            
            default :
               $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
               break;
        }
        //USER TYPE    
		$user_cond_assess=" AND a.user_type=3 ";
        
        
        //GET ALL ASSESSMENT LIST WHICH PUBLISHED 
        $assessment_str = "SELECT a.assessmentid FROM `".TABLE_ASSESSMENT."` as a  WHERE  a.status='1' AND a.atype='0' AND a.assettypeid='1' ".$user_cond_assess.$date_cond;
        $assessment_arr=$parent->getQueryValue($assessment_str);
        //ASSESSMENT COUNT
        $assessment_count=(count($assessment_arr)>0)?count($assessment_arr):'0';
        
        
        $data_arr=array();
        if(count($assessment_arr)>0)
        {     
            foreach($assessment_arr as $res_assess)
            {
                $assessmentid_arr[]=$res_assess->assessmentid;
            }
            
            //GET ALL 20 USER RANK
            //$parent->executeQuery("SET @rownum := 0");                    
            $get_user_rank_str = "SELECT SUM(highscore)as user_score,u.userid,COALESCE(ar.total_time,'0')as total_time,MAX(au.modified_on) as last_modified,u.role  FROM ".TABLE_USER." as u  JOIN (SELECT * FROM  ".TABLE_ASSESSMENT_USER."  WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") AND highscorearid!=0 ) as au ON u.userid=au.userid  LEFT JOIN (SELECT SUM(time_taken)as total_time,assessmentid,userid FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") GROUP BY userid )as ar ON u.userid=ar.userid WHERE  u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid ORDER BY user_score DESC,ar.total_time ASC,last_modified ASC,u.userid ASC   ";
            $get_user_rank_arr=$parent->getQueryValue($get_user_rank_str);
            if($get_user_rank_arr)
            {
                foreach($get_user_rank_arr as $res_user)
                {
                    $user_data_arr=new stdClass();
                    $get_time_str = "SELECT * FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") AND userid='".$res_user->userid."'";
                    $get_time_arr=$parent->getQueryValue($get_time_str);

                    $user_data_arr->user=projectfunc::getuserdata($parent,$res_user->userid);
                    $user_data_arr->rank='0';
                    $user_data_arr->tot_user_score=(count($get_time_arr)>0)?$res_user->user_score:'-';
                    $user_data_arr->percentage_score=(count($get_time_arr)>0)?'0':'-';  
                    
                    $user_data_arr->total_time= $res_user->total_time;
                    $user_data_arr->last_time=(count($get_time_arr)>0)?$res_user->last_modified:'-';
                    $user_data_arr->last_time_str=(count($get_time_arr)>0)?date('Y-m-d',strtotime($res_user->last_modified)):'-';
            
                    
                   $count_assessment_str = "SELECT COUNT(a.assessmentid) as acount FROM `".TABLE_ASSESSMENT."` as a  WHERE a.status='1' AND a.atype='0' AND a.assettypeid='1'  AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59' AND a.enddate>='".$user_data_arr->user->user_created_on."' AND  assessmentid IN (".@implode(',',$assessmentid_arr).") ".$user_cond_assess." ". $assess_user_cond." LIMIT 1";
                    $count_assessment_arr=$parent->getQueryValue($count_assessment_str); 
                    $user_data_arr->percentage_score=(count($assessment_arr)>0)?round($res_user->user_score/$count_assessment_arr[0]->acount):'0';
                    $user_data_arr->percentage=$user_data_arr->percentage_score;
                    
                    if((($assessment_count/$count_assessment_arr[0]->acount)*100)>=MIN_ASSESSMENT_PERCENT)
                    {      
                        $data_arr[]=$user_data_arr;
                    }
                }
                if(count($data_arr)>0)
                {
                    array_multisort(array_column($data_arr, 'percentage_score'),  SORT_DESC,array_column($data_arr, 'total_time'), SORT_ASC,$data_arr);
                    $rank=1;
                    foreach($data_arr as $key=>$res)
                    { 
                        $data_arr[$key]->rank=$rank;  
                        $rank++;
                    }
                } 
            }
        }  
        
        $total_count=count($data_arr);
//        if(count($data_arr)>0)
//        {
//            $temp=array_chunk($data_arr,20);
//            $data_arr=$temp[0];
//        }
                
        
        //LOG
        $logstr='LEADERBOARD TEAM ASSESSMENT';			 
        globalfunc::insertUserLog($parent,$logstr);
        $parent->response(array('response'=>'success','data'=>$data_arr, 'total_count'=>$total_count,'message'=>$this->page_data['success1']),200);    
         
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
    
    /* /////////////////////////////// TRAINERTEAMASSESSMENT //////////////////////////////////////// */
    private function trainerteamassessment($parent,$obj)
    {
        if(!isset($obj)||!isset($obj->month)||!isset($obj->year)||!isset($obj->level)||!isset($obj->email))  globalfunc::callErrorMsg($parent,array('message'=>'INVALID PARAMS - trainerteamassessment'));
        
        $date=$obj->year."-".$obj->month."-01";
        $start_date=date('Y-m',strtotime($date))."-01";  
        $end_date=date('Y-m-t',strtotime($date));  
        $date_cond=" AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59'";
        $data_arr=array();    
        switch($obj->level)
        {
            case "P":
                $user_cond=" AND u.role=5";
            break;
            
            default :
               $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
               break;
        }
        //USER TYPE    
		$user_cond_assess=" AND a.user_type=4 ";
        
        
        //GET ALL ASSESSMENT LIST WHICH PUBLISHED 
        $assessment_str = "SELECT a.assessmentid FROM `".TABLE_ASSESSMENT."` as a  WHERE  a.status='1' AND a.atype='0' AND a.assettypeid='1' ".$user_cond_assess.$date_cond;
        $assessment_arr=$parent->getQueryValue($assessment_str);
        //ASSESSMENT COUNT
        $assessment_count=(count($assessment_arr)>0)?count($assessment_arr):'0';
        
        
        $data_arr=array();
        if(count($assessment_arr)>0)
        {     
            foreach($assessment_arr as $res_assess)
            {
                $assessmentid_arr[]=$res_assess->assessmentid;
            }
            
            //GET ALL 20 USER RANK
            //$parent->executeQuery("SET @rownum := 0");                    
            $get_user_rank_str = "SELECT SUM(highscore)as user_score,u.userid,COALESCE(ar.total_time,'0')as total_time,MAX(au.modified_on) as last_modified,u.role  FROM ".TABLE_USER." as u  JOIN (SELECT * FROM  ".TABLE_ASSESSMENT_USER."  WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") AND highscorearid!=0 ) as au ON u.userid=au.userid  LEFT JOIN (SELECT SUM(time_taken)as total_time,assessmentid,userid FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") GROUP BY userid )as ar ON u.userid=ar.userid WHERE  u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid ORDER BY user_score DESC,ar.total_time ASC,last_modified ASC,u.userid ASC   ";
            $get_user_rank_arr=$parent->getQueryValue($get_user_rank_str);
            if($get_user_rank_arr)
            {
                foreach($get_user_rank_arr as $res_user)
                {
                    $user_data_arr=new stdClass();
                    $get_time_str = "SELECT * FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") AND userid='".$res_user->userid."'";
                    $get_time_arr=$parent->getQueryValue($get_time_str);

                    $user_data_arr->user=projectfunc::getuserdata($parent,$res_user->userid);
                    $user_data_arr->rank='0';
                    $user_data_arr->tot_user_score=(count($get_time_arr)>0)?$res_user->user_score:'-';
                    $user_data_arr->percentage_score=(count($get_time_arr)>0)?'0':'-';  
                    
                    $user_data_arr->total_time= $res_user->total_time;
                    $user_data_arr->last_time=(count($get_time_arr)>0)?$res_user->last_modified:'-';
                    $user_data_arr->last_time_str=(count($get_time_arr)>0)?date('Y-m-d',strtotime($res_user->last_modified)):'-';
            
                    
                   $count_assessment_str = "SELECT COUNT(a.assessmentid) as acount FROM `".TABLE_ASSESSMENT."` as a  WHERE a.status='1' AND a.atype='0' AND a.assettypeid='1'  AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59' AND a.enddate>='".$user_data_arr->user->user_created_on."' AND  assessmentid IN (".@implode(',',$assessmentid_arr).") ".$user_cond_assess." ". $assess_user_cond." LIMIT 1";
                    $count_assessment_arr=$parent->getQueryValue($count_assessment_str); 
                    $user_data_arr->percentage_score=(count($assessment_arr)>0)?round($res_user->user_score/$count_assessment_arr[0]->acount):'0';
                    $user_data_arr->percentage=$user_data_arr->percentage_score;
                    
                    if((($assessment_count/$count_assessment_arr[0]->acount)*100)>=MIN_ASSESSMENT_PERCENT)
                    {      
                        $data_arr[]=$user_data_arr;
                    }
                }
                if(count($data_arr)>0)
                {
                    array_multisort(array_column($data_arr, 'percentage_score'),  SORT_DESC,array_column($data_arr, 'total_time'), SORT_ASC,$data_arr);
                    $rank=1;
                    foreach($data_arr as $key=>$res)
                    { 
                        $data_arr[$key]->rank=$rank;  
                        $rank++;
                    }
                } 
            }
        }  
        
        $total_count=count($data_arr);
//        if(count($data_arr)>0)
//        {
//            $temp=array_chunk($data_arr,20);
//            $data_arr=$temp[0];
//        }
                
        
        //LOG
        $logstr='LEADERBOARD TEAM ASSESSMENT';			 
        globalfunc::insertUserLog($parent,$logstr);
        $parent->response(array('response'=>'success','data'=>$data_arr, 'total_count'=>$total_count,'message'=>$this->page_data['success1']),200);    
         
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
            
    /* /////////////////////////////// RMTEAMASSESSMENT //////////////////////////////////////// */
    private function rmteamassessment($parent,$obj)
    {
        if(!isset($obj)||!isset($obj->month)||!isset($obj->year)||!isset($obj->level)||!isset($obj->email)) globalfunc::callErrorMsg($parent,array('message'=>'INVALID PARAMS - rmteamassessment'));
        
        $date=$obj->year."-".$obj->month."-01";
        $start_date=date('Y-m',strtotime($date))."-01";  
        $end_date=date('Y-m-t',strtotime($date));  
        $date_cond=" AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59'";
        $data_arr=array();   
        switch($obj->level)
        {
            case "P":
                $user_cond=" AND u.role=6";
            break;
            case "Z":
                //COND
                $email_cond= " AND zm_email='".$obj->email."' ";
                $query_all_amarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,rm_email "," status='1' AND is_deleted='0' ".$email_cond." GROUP BY rm_email","");  
                $emailid_arr=array();
                if($query_all_amarr['nr']>0)
                {
                    foreach($query_all_amarr['result'] as $res)
                    {
                        $emailid_arr[]="'".$res->rm_email."'";
                    }
                    $user_cond=" AND u.role=6 AND u.emailid IN (".implode(',',$emailid_arr).")";
                }else
                {
                    $user_cond=" AND u.role=6 AND u.emailid IN ('')";
                }
               break; 
            default :
               $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
               break;
        }
        //USER TYPE    
		$user_cond_assess=" AND a.user_type=5 ";
         
        //GET ALL ASSESSMENT LIST WHICH PUBLISHED 
        $assessment_str = "SELECT a.assessmentid FROM `".TABLE_ASSESSMENT."` as a  WHERE  a.status='1' AND a.atype='0' AND a.assettypeid='1' ".$user_cond_assess.$date_cond;
        $assessment_arr=$parent->getQueryValue($assessment_str);
        //ASSESSMENT COUNT
        $assessment_count=(count($assessment_arr)>0)?count($assessment_arr):'0';
    
        $data_arr=array();
        if(count($assessment_arr)>0)
        {     
            foreach($assessment_arr as $res_assess)
            {
                $assessmentid_arr[]=$res_assess->assessmentid;
            }
            
            //GET ALL 20 USER RANK
            //$parent->executeQuery("SET @rownum := 0");                    
            $get_user_rank_str = "SELECT SUM(highscore)as user_score,u.userid,COALESCE(ar.total_time,'0')as total_time,MAX(au.modified_on) as last_modified,u.role  FROM ".TABLE_USER." as u  JOIN (SELECT * FROM  ".TABLE_ASSESSMENT_USER."  WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") AND highscorearid!=0 ) as au ON u.userid=au.userid  LEFT JOIN (SELECT SUM(time_taken)as total_time,assessmentid,userid FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") GROUP BY userid )as ar ON u.userid=ar.userid WHERE  u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid ORDER BY user_score DESC,ar.total_time ASC,last_modified ASC,u.userid ASC   ";
            $get_user_rank_arr=$parent->getQueryValue($get_user_rank_str);
            if($get_user_rank_arr)
            {
                 foreach($get_user_rank_arr as $res_user)
                 {
                    $user_data_arr=new stdClass();
                    $get_time_str = "SELECT * FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") AND userid='".$res_user->userid."'";
                    $get_time_arr=$parent->getQueryValue($get_time_str);

                    $user_data_arr->user=projectfunc::getuserdata($parent,$res_user->userid);
                    $user_data_arr->rank='0';
                    $user_data_arr->tot_user_score=(count($get_time_arr)>0)?$res_user->user_score:'-';
                    $user_data_arr->percentage_score=(count($get_time_arr)>0)?'0':'-';  
                    
                    $user_data_arr->total_time= $res_user->total_time;
                    $user_data_arr->last_time=(count($get_time_arr)>0)?$res_user->last_modified:'-';
                    $user_data_arr->last_time_str=(count($get_time_arr)>0)?date('Y-m-d',strtotime($res_user->last_modified)):'-';
            
                    
                   $count_assessment_str = "SELECT COUNT(a.assessmentid) as acount FROM `".TABLE_ASSESSMENT."` as a  WHERE a.status='1' AND a.atype='0' AND a.assettypeid='1'  AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59' AND a.enddate>='".$user_data_arr->user->user_created_on."' AND  assessmentid IN (".@implode(',',$assessmentid_arr).") ".$user_cond_assess." ". $assess_user_cond." LIMIT 1";
                    $count_assessment_arr=$parent->getQueryValue($count_assessment_str); 
                    $user_data_arr->percentage_score=(count($assessment_arr)>0)?round($res_user->user_score/$count_assessment_arr[0]->acount):'0';
                    $user_data_arr->percentage=$user_data_arr->percentage_score;
                                                           
                    if((($assessment_count/$count_assessment_arr[0]->acount)*100)>=MIN_ASSESSMENT_PERCENT)
                    {      
                        $data_arr[]=$user_data_arr;
                    }
                 }
            }
            
            if(count($data_arr)>0)
            {
                array_multisort(array_column($data_arr, 'percentage_score'),  SORT_DESC,array_column($data_arr, 'total_time'), SORT_ASC,$data_arr);
                $rank=1;
                foreach($data_arr as $key=>$res)
                { 
                    $data_arr[$key]->rank=$rank;
                    $rank++;
                }
            } 
            
        } 
        
        $total_count=count($data_arr);
//        if(count($data_arr)>0)
//        {
//            $temp=array_chunk($data_arr,20);
//            $data_arr=$temp[0];
//        }
                
        
        //LOG
        $logstr='LEADERBOARD TEAM ASSESSMENT';		 
        globalfunc::insertUserLog($parent,$logstr);
        $parent->response(array('response'=>'success','data'=>$data_arr, 'total_count'=>$total_count,'message'=>$this->page_data['success1']),200);
         
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
    
    /* /////////////////////////////// AMTEAMASSESSMENT //////////////////////////////////////// */
    private function amteamassessment($parent,$obj)
    {
        if(!isset($obj)||!isset($obj->month)||!isset($obj->year)||!isset($obj->level)||!isset($obj->email))  globalfunc::callErrorMsg($parent,array('message'=>'INVALID PARAMS - amteamassessment'));
        
        $date=$obj->year."-".$obj->month."-01";
        $start_date=date('Y-m',strtotime($date))."-01";  
        $end_date=date('Y-m-t',strtotime($date));  
        $date_cond=" AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59'";
        $data_arr=array();    
        switch($obj->level)
        {
            case "P":
                $user_cond=" AND u.role=7";
            break;
            case "Z":
                //COND
                $email_cond= " AND zm_email='".$obj->email."' ";
            
                $query_all_amarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,am_email "," status='1' AND is_deleted='0' ".$email_cond." GROUP BY am_email","");  
                $emailid_arr=array();
                if($query_all_amarr['nr']>0)
                {
                    foreach($query_all_amarr['result'] as $res)
                    {
                        $emailid_arr[]="'".$res->am_email."'";
                    }
                    $user_cond=" AND u.role=7 AND u.emailid IN (".implode(',',$emailid_arr).")";
                }else
                {
                    $user_cond=" AND u.role=7 AND u.emailid IN ('')";
                }
               break; 
            case "R":
                $email_cond= " AND rm_email='".$obj->email."' ";
                $query_all_amarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,am_email "," status='1' AND is_deleted='0' ".$email_cond." GROUP BY am_email","");  
                $emailid_arr=array();
                if($query_all_amarr['nr']>0)
                {
                    foreach($query_all_amarr['result'] as $res)
                    {
                        $emailid_arr[]="'".$res->am_email."'";
                    }
                    $user_cond=" AND u.role=7 AND u.emailid IN (".implode(',',$emailid_arr).")";
                }else
                {
                    $user_cond=" AND u.role=7 AND u.emailid IN ('')";
                } 
                break; 
            default :
               $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
               break;
        }
        //USER TYPE    
		$user_cond_assess=" AND a.user_type=6 ";
          
        //GET ALL ASSESSMENT LIST WHICH PUBLISHED 
        $assessment_str = "SELECT a.assessmentid FROM `".TABLE_ASSESSMENT."` as a  WHERE  a.status='1' AND a.atype='0' AND a.assettypeid='1' ".$user_cond_assess.$date_cond;
        $assessment_arr=$parent->getQueryValue($assessment_str);
        //ASSESSMENT COUNT
        $assessment_count=(count($assessment_arr)>0)?count($assessment_arr):'0';
        
        
        $data_arr=array();
        if(count($assessment_arr)>0)
        {     
            foreach($assessment_arr as $res_assess)
            {
                $assessmentid_arr[]=$res_assess->assessmentid;
            }
            
            //GET ALL 20 USER RANK
            $get_user_rank_str = "SELECT SUM(highscore)as user_score,u.userid,COALESCE(ar.total_time,'0')as total_time,MAX(au.modified_on) as last_modified,u.role  FROM ".TABLE_USER." as u  JOIN (SELECT * FROM  ".TABLE_ASSESSMENT_USER."  WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") AND highscorearid!=0 ) as au ON u.userid=au.userid  LEFT JOIN (SELECT SUM(time_taken)as total_time,assessmentid,userid FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") GROUP BY userid )as ar ON u.userid=ar.userid WHERE  u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid ORDER BY user_score DESC,ar.total_time ASC,last_modified ASC,u.userid ASC   ";
            $get_user_rank_arr=$parent->getQueryValue($get_user_rank_str);
            if($get_user_rank_arr)
            {
                 foreach($get_user_rank_arr as $res_user)
                 {
                    $user_data_arr=new stdClass();
                    $get_time_str = "SELECT * FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") AND userid='".$res_user->userid."'";
                    $get_time_arr=$parent->getQueryValue($get_time_str);

                    $user_data_arr->user=projectfunc::getuserdata($parent,$res_user->userid);
                    $user_data_arr->rank='0';
                    $user_data_arr->tot_user_score=(count($get_time_arr)>0)?$res_user->user_score:'-';
                    $user_data_arr->percentage_score=(count($get_time_arr)>0)?'0':'-';  
                    
                    $user_data_arr->total_time= $res_user->total_time;
                    $user_data_arr->last_time=(count($get_time_arr)>0)?$res_user->last_modified:'-';
                    $user_data_arr->last_time_str=(count($get_time_arr)>0)?date('Y-m-d',strtotime($res_user->last_modified)):'-';
            
                    
                   $count_assessment_str = "SELECT COUNT(a.assessmentid) as acount FROM `".TABLE_ASSESSMENT."` as a  WHERE a.status='1' AND a.atype='0' AND a.assettypeid='1'  AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59' AND a.enddate>='".$user_data_arr->user->user_created_on."' AND  assessmentid IN (".@implode(',',$assessmentid_arr).") ".$user_cond_assess." ". $assess_user_cond." LIMIT 1";
                    $count_assessment_arr=$parent->getQueryValue($count_assessment_str); 
                    $user_data_arr->percentage_score=(count($assessment_arr)>0)?round($res_user->user_score/$count_assessment_arr[0]->acount):'0';
                    $user_data_arr->percentage=$user_data_arr->percentage_score;
                                                           
                    if((($assessment_count/$count_assessment_arr[0]->acount)*100)>=MIN_ASSESSMENT_PERCENT)
                    {      
                        $data_arr[]=$user_data_arr;
                    }
                }
                
                //RANK SETTING
                if(count($data_arr)>0)
                {
                    array_multisort(array_column($data_arr, 'percentage_score'),  SORT_DESC,array_column($data_arr, 'total_time'), SORT_ASC,$data_arr);
                    $rank=1;
                    foreach($data_arr as $key=>$res)
                    { 
                        $data_arr[$key]->rank=$rank;
                        $rank++;
                    }
                } 
            } 
        }  
        
        $total_count=count($data_arr);
//        if(count($data_arr)>0)
//        {
//            $temp=array_chunk($data_arr,20);
//            $data_arr=$temp[0];
//        }
                
        
        //LOG
        $logstr='LEADERBOARD TEAM ASSESSMENT';		 
        globalfunc::insertUserLog($parent,$logstr);
        $parent->response(array('response'=>'success','data'=>$data_arr, 'total_count'=>$total_count,'message'=>$this->page_data['success1']),200);
         
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
            
    /* /////////////////////////////// DSETEAMASSESSMENT //////////////////////////////////////// */
    private function dseteamassessment($parent,$obj)
    {
        if(!isset($obj)||!isset($obj->month)||!isset($obj->year)||!isset($obj->level)||!isset($obj->email))  globalfunc::callErrorMsg($parent,array('message'=>'INVALID PARAMS - dealerteamassessment'));
        
        $date=$obj->year."-".$obj->month."-01";
        $start_date=date('Y-m',strtotime($date))."-01";  
        $end_date=date('Y-m-t',strtotime($date));  
        $date_cond=" AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59'";
        $data_arr=array();
//        switch($obj->level)
//        {
//            case "P":
//                $join_cond='';
//                $area_cond='';
//            break;
//            case "Z":
//                //COND
//            
//                if($obj->email!='') 
//                {
//                    $join_cond="JOIN ".DMS_DEALER." as dd ON dd.dealer_code=u.dealer_code ";
//                    $area_cond=" and dd.zm_email ='".$obj->email."'";
//                }else
//                {
//                    $area_cond=" and u.dealer_code IN ('')";
//                }
//               break; 
//            case "R":
//                //COND
//            
//               if($obj->email!='') 
//               {
//                   $join_cond="JOIN ".DMS_DEALER." as dd ON dd.dealer_code=u.dealer_code ";
//                   $area_cond=" and dd.rm_email ='".$obj->email."'";
//               }else
//               {
//                   $area_cond=" and u.dealer_code IN ('')";
//               }
//               break;  
//            case "A":                
//                //COND            
//                if($obj->email!='') 
//                {
//                    $join_cond="JOIN ".DMS_DEALER." as dd ON dd.dealer_code=u.dealer_code ";
//                    $area_cond=" and dd.am_email ='".$obj->email."'";
//                }else
//                {
//                    $area_cond=" and u.dealer_code IN ('')";
//                }
//               break;    
//            default :
//               $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
//               break;
//       }
        $zm_email = escape_str($parent->_request["zmemail"]);
        $rm_email = escape_str($parent->_request["rmemail"]);
        $am_email = escape_str($parent->_request["amemail"]);      
        
        $mcond='';
        if($parent->current_user_role!='14')
        {
            $mcond=($am_email!='all' && $am_email!='')?" AND dd.am_email='".$am_email."'":''; 
        }
        $mcond.=($rm_email!='all' && $rm_email!='')?" AND dd.rm_email='".$rm_email."'":'';   
		if($parent->current_user_role!='5')
		{		
			$mcond.=($zm_email!='all' && $zm_email!='')?" AND dd.zm_email='".$zm_email."'":'';  
        }
        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);
            $mcond.=(count($trainer_arr)>0)?"AND dd.zone='".$trainer_arr[0]->zone."'":"AND dd.zone='0'";
        }
        
        if($parent->current_user_role=='14')
        {
            $get_dealer_str = "SELECT d.am_email FROM  ".TABLE_USER." u JOIN ".DMS_DEALER." as d ON u.dealer_code=d.dealer_code WHERE u.userid='".$parent->current_user_id."' AND u.is_deleted=0 LIMIT 1";
            $dealer_arr=$parent->getQueryValue($get_dealer_str);
            
            $mcond.=(count($dealer_arr)>0)?"AND dd.am_email='".$dealer_arr[0]->am_email."'":"AND dd.am_email=''";
        }
        
        $join_cond="JOIN ".DMS_DEALER." as dd ON dd.dealer_code=u.dealer_code ";
        $area_cond=$mcond;
        
		
        //NEW ROLE CONDITION
        $user_cond_assess=($obj->role=='DSE')?" AND a.user_type=0 ":" AND a.user_type=1 ";
        $user_cond=($obj->role=='DSE')?" AND (u.role>7 AND u.role<14) ":" AND u.role=15 ";
           
        //GET ALL ASSESSMENT LIST WHICH PUBLISHED 
        $assessment_str = "SELECT a.assessmentid FROM `".TABLE_ASSESSMENT."` as a  WHERE  a.status='1' AND a.atype='0' AND a.assettypeid='1' ".$user_cond_assess.$date_cond;
        $assessment_arr=$parent->getQueryValue($assessment_str);
        //ASSESSMENT COUNT
        $assessment_count=(count($assessment_arr)>0)?count($assessment_arr):'0';
        
        
        $data_arr=array();
        if(count($assessment_arr)>0)
        {     
            foreach($assessment_arr as $res_assess)
            {
                $assessmentid_arr[]=$res_assess->assessmentid;
            }
            
            //GET ALL 20 USER RANK
            //$parent->executeQuery("SET @rownum := 0");                    
            $get_user_rank_str = "SELECT SUM(highscore)as user_score,u.userid,COALESCE(ar.total_time,'0')as total_time,MAX(au.modified_on) as last_modified,u.dealer_code,u.role  FROM ".TABLE_USER." as u JOIN ".DMS_DEALER." d ON u.dealer_code=d.dealer_code JOIN (SELECT * FROM  ".TABLE_ASSESSMENT_USER."  WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") AND highscorearid!=0 ) as au ON u.userid=au.userid  LEFT JOIN (SELECT SUM(time_taken)as total_time,assessmentid,userid FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") GROUP BY userid )as ar ON u.userid=ar.userid ".$join_cond."  WHERE  u.is_deleted=0 AND u.status=1 ".$user_cond." ".$area_cond." GROUP BY u.userid ORDER BY user_score DESC,ar.total_time ASC,last_modified ASC,u.userid ASC   ";
            $get_user_rank_arr=$parent->getQueryValue($get_user_rank_str);
            if($get_user_rank_arr)
            {
                 foreach($get_user_rank_arr as $res_user)
                 {
                    $user_data_arr=new stdClass();
                    $get_time_str = "SELECT * FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid IN (".@implode(',',$assessmentid_arr).") AND userid='".$res_user->userid."'";
                    $get_time_arr=$parent->getQueryValue($get_time_str);

                    $getuser_data= projectfunc::getuserdata($parent,$res_user->userid);  
                    $user_data_arr->user=$getuser_data;
                    $user_data_arr->rank='0';
                    $user_data_arr->tot_user_score=(count($get_time_arr)>0)?$res_user->user_score:'-';
                    $user_data_arr->percentage_score=(count($get_time_arr)>0)?'0':'-';  
                    
                    $user_data_arr->total_time= $res_user->total_time;
                    $user_data_arr->last_time=(count($get_time_arr)>0)?$res_user->last_modified:'-';
                    $user_data_arr->last_time_str=(count($get_time_arr)>0)?date('Y-m-d',strtotime($res_user->last_modified)):'-';
                    
                    //GET EACH USER COND
                   
                    $user_cond=array();
//                    if($getuser_data->role)
//                    {
//                        $user_cond[]=" (a.filter_role LIKE '%".$getuser_data->role."%' OR a.filter_role='')";
//                    }
                    if($getuser_data->zone)
                    {
                        $user_cond[]=" (a.filter_zone LIKE '%".$getuser_data->zone."%' OR a.filter_zone='')";
                    }
                    if($getuser_data->city)
                    {
                        $user_cond[]=" (a.filter_region LIKE '%".$getuser_data->city."%' OR a.filter_region='')";
                    }
                    if($getuser_data->dealer_code)
                    {
                        $user_cond[]=" (a.filter_dealer LIKE '%".$getuser_data->dealer_code."%'  OR a.filter_dealer='')";
                    }
                    if($getuser_data->role>7)
                    {
                        $user_cond[]=" (a.segment = '".$getuser_data->segment."' OR a.segment =0)";
                    }
                    
                    
                    
                    $assess_user_cond=" AND ".@implode(' AND ',$user_cond);
                    
                    
                   $count_assessment_str = "SELECT COUNT(a.assessmentid) as acount FROM `".TABLE_ASSESSMENT."` as a  WHERE a.status='1' AND a.atype='0' AND a.assettypeid='1'  AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59' AND a.enddate>='".$user_data_arr->user->user_created_on."' AND  assessmentid IN (".@implode(',',$assessmentid_arr).") ".$user_cond_assess." ". $assess_user_cond." LIMIT 1";
                    $count_assessment_arr=$parent->getQueryValue($count_assessment_str); 
                    $user_data_arr->percentage_score=(count($assessment_arr)>0)?round($res_user->user_score/$count_assessment_arr[0]->acount):'0';
                    $user_data_arr->percentage=$user_data_arr->percentage_score;
                    
                    if((($assessment_count/$count_assessment_arr[0]->acount)*100)>=MIN_ASSESSMENT_PERCENT)
                    {      
                        $data_arr[]=$user_data_arr;
                    }
                }
                 
                 //RANK SETTING
                if(count($data_arr)>0)
                {
                    array_multisort(array_column($data_arr, 'percentage_score'),  SORT_DESC,array_column($data_arr, 'total_time'), SORT_ASC,$data_arr);
                    $rank=1;
                    foreach($data_arr as $key=>$res)
                    { 
                        $data_arr[$key]->rank=$rank;                    
                        $rank++;
                    }
                }
            }  
        } 
        
        $total_count=count($data_arr);
        if(count($data_arr)>0)
        {
            $temp=array_chunk($data_arr,20);
            $data_arr=$temp[0];
        }
           
        //LOG
        $logstr='LEADERBOARD TEAM ASSESSMENT';		 
        globalfunc::insertUserLog($parent,$logstr);
        $parent->response(array('response'=>'success','data'=>$data_arr,'total_count'=>$total_count,  'message'=>$this->page_data['success1']),200);
         
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
     
    /* /////////////////////////////// MY SALES //////////////////////////////////////// */
	private function mysales($parent)
    {
        globalfunc::checkMethodType($parent,array('POST'));	
		globalfunc::checkAuthrozation($parent,array('type'=>'user'));
        
        globalfunc::checkMethodType($parent,array('POST'));	
        globalfunc::checkParamCount($parent,1);
		globalfunc::checkAuthrozation($parent,array('type'=>'user'));
        projectfunc::authorizedRole($parent,array(4,5,6,7,8,9,10,11,12,13,15));
        
        $REQUEST_KEYS=array('month'=>'string','year'=>'number','location'=>'string');
		globalfunc::mandatoryKey($parent,$REQUEST_KEYS);
		$month=escape_str($parent->_request['month']);
        $year=escape_str($parent->_request['year']);
        $location=escape_str($parent->_request['location']);
        
        $obj=new stdClass();
        $obj->month=$month;
        $obj->year=$year;
        $obj->location=$location;
        switch($parent->current_user_role )
        {
            case '4'://zm 
                $this->zmsales($parent,$obj);
                break;
            case '5'://trainer 
                $this->trainersales($parent,$obj);
                break; 
            case '6'://rm 
                $this->rmsales($parent,$obj);
                break;
            case '7'://am 
                $this->amsales($parent,$obj);
                break;
            case '8'://normal dse 
            case '9':
            case '10':
            case '11':
            case '12':
            case '13': 
            case '15':    
                $this->dsesales($parent,$obj);
                break;
        } 
            
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
    
    /* /////////////////////////////// ZMSALES //////////////////////////////////////// */
    private function zmsales($parent,$obj)
    {
        if(!isset($obj)||!isset($obj->month)||!isset($obj->year)||!isset($obj->location))  globalfunc::callErrorMsg($parent,array('message'=>'INVALID PARAMS - zmsales')); 
        $date=$obj->year."-".$obj->month."-01";
        $start_date=date('Y-m',strtotime($date))."-01";  
        $end_date=date('Y-m-t',strtotime($date));  
        $data_arr=array();    
        $emailid=$parent->current_user_data->emailid; 
        switch($obj->location)
        {
            case "P":
                $user_cond=" AND u.role=4";
            break;
            
            default :
               $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
               break;
        }
        //TOP 20 AREA SALES LEADERBOARD
        $parent->executeQuery("SET @rownum := 0");                    
        $get_user_rank_str = "SELECT * FROM (SELECT @rownum := @rownum + 1 AS rank, salecount,targetcount, userid,sale_per FROM (SELECT c.*, CASE WHEN targetcount=0 THEN 0 ELSE (salecount/targetcount)*100 END as 'sale_per' FROM (SELECT a.userid, a.salecount,b.targetcount  FROM (SELECT  COALESCE(SUM(s.sales_count),'0') as salecount, u.userid FROM ".TABLE_USER." as u LEFT JOIN (SELECT zm_email,sale_date,sales_count FROM  ".VIEW_ZM_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON u.emailid=s.zm_email   WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.userid  FROM ".TABLE_USER." as u LEFT JOIN (SELECT zm_email,target FROM ".VIEW_ZM_SALESTARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON u.emailid=t.zm_email  WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." GROUP BY u.userid ) as b ON a.userid=b.userid )c  ORDER BY cast(sale_per as SIGNED) DESC,cast(salecount as SIGNED) DESC,cast(targetcount as SIGNED) DESC,userid ASC)as a )as k LIMIT 20";
        $get_user_rank_arr=$parent->getQueryValue($get_user_rank_str);
        if(count($get_user_rank_arr)>0)
        {
            foreach ($get_user_rank_arr as $key => $value)
            {
                $get_user_rank_arr[$key]->user=projectfunc::getuserdata($parent,$value->userid);
                $get_user_rank_arr[$key]->percentage=round($value->sale_per);
            }   
             $data_arr= $get_user_rank_arr;
        }                   
       
        
        //GET USER DATA
        $parent->executeQuery("SET @rownum := 0");                    
        $get_current_user_rank_str = "SELECT * FROM (SELECT @rownum := @rownum + 1 AS rank, salecount,targetcount, userid,sale_per FROM (SELECT c.*, CASE WHEN targetcount=0 THEN 0 ELSE (salecount/targetcount)*100 END as 'sale_per' FROM (SELECT a.userid, a.salecount,b.targetcount  FROM (SELECT  COALESCE(SUM(s.sales_count),'0') as salecount, u.userid FROM ".TABLE_USER." as u LEFT JOIN (SELECT zm_email,sale_date,sales_count FROM  ".VIEW_ZM_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON u.emailid=s.zm_email WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.userid  FROM ".TABLE_USER." as u LEFT JOIN (SELECT zm_email,target FROM ".VIEW_ZM_SALESTARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON u.emailid=t.zm_email WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid ) as b ON a.userid=b.userid )c  ORDER BY cast(sale_per as SIGNED) DESC,cast(salecount as SIGNED) DESC,cast(targetcount as SIGNED) DESC,userid ASC)as a )as k  WHERE userid='".$parent->current_user_id."' LIMIT 1";
        $get_user_rank_arr=$parent->getQueryValue($get_current_user_rank_str);
        // print_r($get_user_rank_arr);
        
        $cur_data_arr=new stdClass();
        $udata=projectfunc::getuserdata($parent,$parent->current_user_id);
        $cur_data_arr->user=$udata;
        foreach($udata as $key=>$res)
        {
            $cur_data_arr->$key=$res;
        }           
        $cur_data_arr->rank='';
        $cur_data_arr->salecount='';
        $cur_data_arr->percentage='';
        
        
        // print_r($cur_data_arr);
        if(count($get_user_rank_arr)>0)
        {
            //print_r($cur_data_arr);
            foreach($get_user_rank_arr[0] as $okey=>$objval)
            {
                 $cur_data_arr->$okey= $objval;                 
            }
            $cur_data_arr->percentage=round($cur_data_arr->sale_per);
            
        }
        
        $get_count_arr=$parent->getQueryValue("SELECT COUNT( userid) total_count FROM ( SELECT a.userid, a.salecount,b.targetcount  FROM (SELECT  COALESCE(SUM(s.sales_count),'0') as salecount, u.userid FROM ".TABLE_USER." as u LEFT JOIN (SELECT zm_email,sale_date,sales_count FROM  ".VIEW_ZM_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON u.emailid=s.zm_email   WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.userid  FROM ".TABLE_USER." as u LEFT JOIN (SELECT zm_email,target FROM ".VIEW_ZM_SALESTARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON u.emailid=t.zm_email  WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." GROUP BY u.userid ) as b ON a.userid=b.userid )c LIMIT 1");        
        $total_count=(count($get_count_arr)>0)?$get_count_arr[0]->total_count:"0";
                    
        //LOG
        $logstr='LEADERBOARD MY SALES';			 
        globalfunc::insertUserLog($parent,$logstr);
        $parent->response(array('response'=>'success', 'data'=>$data_arr,'user_data'=>$cur_data_arr,'total_count'=>$total_count,'message'=>$this->page_data['success1'] ),200); 
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
    
    /* /////////////////////////////// TRAINERSALES //////////////////////////////////////// */
    private function trainersales($parent,$obj)
    {
        if(!isset($obj)||!isset($obj->month)||!isset($obj->year)||!isset($obj->location))  globalfunc::callErrorMsg($parent,array('message'=>'INVALID PARAMS - zmsales')); 
        $date=$obj->year."-".$obj->month."-01";
        $start_date=date('Y-m',strtotime($date))."-01";  
        $end_date=date('Y-m-t',strtotime($date));  
        $data_arr=array();    
        //$emailid=$parent->current_user_data->emailid; 
        
        //$emailid=$parent->current_user_data->emailid;  
        $zone=$parent->current_user_data->zone;  
        switch($obj->location)
        {
            case "P":
                $user_cond=" AND u.role=5";
            break;
            
            default :
               $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
               break;
        }
        //TOP 20 AREA SALES LEADERBOARD
        $parent->executeQuery("SET @rownum := 0");                    
        $get_user_rank_str = "SELECT * FROM (SELECT @rownum := @rownum + 1 AS rank, salecount,targetcount, userid,sale_per FROM (SELECT c.*, CASE WHEN targetcount=0 THEN 0 ELSE (salecount/targetcount)*100 END as 'sale_per' FROM (SELECT a.userid, a.salecount,b.targetcount  FROM (SELECT  COALESCE(SUM(s.sales_count),'0') as salecount, u.userid FROM ".TABLE_USER." as u JOIN ".DMS_HOUSER." h ON u.emp_code=h.emp_code LEFT JOIN (SELECT zone,sale_date,sales_count FROM  ".VIEW_ZONE_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON h.zone=s.zone   WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.userid  FROM ".TABLE_USER." as u JOIN ".DMS_HOUSER." h ON u.emp_code=h.emp_code LEFT JOIN (SELECT zone,target FROM ".VIEW_ZONE_SALESTARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON h.zone=t.zone  WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." GROUP BY u.userid ) as b ON a.userid=b.userid )c  ORDER BY cast(sale_per as SIGNED) DESC,cast(salecount as SIGNED) DESC,cast(targetcount as SIGNED) DESC,userid ASC)as a )as k LIMIT 20";
        $get_user_rank_arr=$parent->getQueryValue($get_user_rank_str);
        if(count($get_user_rank_arr)>0)
        {
            foreach ($get_user_rank_arr as $key => $value)
            {
                $get_user_rank_arr[$key]->user=projectfunc::getuserdata($parent,$value->userid);
                $get_user_rank_arr[$key]->percentage=round($value->sale_per);
            }   
             $data_arr= $get_user_rank_arr;
        }                   
       
        
        //GET USER DATA
        $parent->executeQuery("SET @rownum := 0");                    
        $get_current_user_rank_str = "SELECT * FROM (SELECT @rownum := @rownum + 1 AS rank, salecount,targetcount, userid,sale_per FROM (SELECT c.*, CASE WHEN targetcount=0 THEN 0 ELSE (salecount/targetcount)*100 END as 'sale_per' FROM (SELECT a.userid, a.salecount,b.targetcount  FROM (SELECT  COALESCE(SUM(s.sales_count),'0') as salecount, u.userid FROM ".TABLE_USER." as u JOIN ".DMS_HOUSER." h ON u.emp_code=h.emp_code  LEFT JOIN (SELECT zone,sale_date,sales_count FROM  ".VIEW_ZONE_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON h.zone=s.zone WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.userid  FROM ".TABLE_USER." as u JOIN ".DMS_HOUSER." h ON u.emp_code=h.emp_code  LEFT JOIN (SELECT zone,target FROM ".VIEW_ZONE_SALESTARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON h.zone=t.zone WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid ) as b ON a.userid=b.userid )c  ORDER BY cast(sale_per as SIGNED) DESC,cast(salecount as SIGNED) DESC,cast(targetcount as SIGNED) DESC,userid ASC)as a )as k  WHERE userid='".$parent->current_user_id."' LIMIT 1";
        $get_user_rank_arr=$parent->getQueryValue($get_current_user_rank_str);
        // print_r($get_user_rank_arr);
        
        $cur_data_arr=new stdClass();
        $udata=projectfunc::getuserdata($parent,$parent->current_user_id);
        $cur_data_arr->user=$udata;
        foreach($udata as $key=>$res)
        {
            $cur_data_arr->$key=$res;
        }           
        $cur_data_arr->rank='';
        $cur_data_arr->salecount='';
        $cur_data_arr->percentage='';
        
        
        // print_r($cur_data_arr);
        if(count($get_user_rank_arr)>0)
        {
            //print_r($cur_data_arr);
            foreach($get_user_rank_arr[0] as $okey=>$objval)
            {
                 $cur_data_arr->$okey= $objval;                 
            }
            $cur_data_arr->percentage=round($cur_data_arr->sale_per);
            
        }
        
        $get_count_arr=$parent->getQueryValue("SELECT COUNT( userid) total_count FROM ( SELECT a.userid, a.salecount,b.targetcount  FROM (SELECT  COALESCE(SUM(s.sales_count),'0') as salecount, u.userid FROM ".TABLE_USER." as u JOIN ".DMS_HOUSER." h ON u.emp_code=h.emp_code  LEFT JOIN (SELECT zone,sale_date,sales_count FROM  ".VIEW_ZONE_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON h.zone=s.zone   WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.userid  FROM ".TABLE_USER." as u JOIN ".DMS_HOUSER." h ON u.emp_code=h.emp_code  LEFT JOIN (SELECT zone,target FROM ".VIEW_ZONE_SALESTARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON h.zone=t.zone  WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." GROUP BY u.userid ) as b ON a.userid=b.userid )c LIMIT 1");        
        $total_count=(count($get_count_arr)>0)?$get_count_arr[0]->total_count:"0";
                    
        //LOG
        $logstr='LEADERBOARD MY SALES';			 
        globalfunc::insertUserLog($parent,$logstr);
        $parent->response(array('response'=>'success', 'data'=>$data_arr,'user_data'=>$cur_data_arr,'total_count'=>$total_count,'message'=>$this->page_data['success1'] ),200);  
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
    
    /* /////////////////////////////// RMSALES //////////////////////////////////////// */
    private function rmsales($parent,$obj)
    {
        if(!isset($obj)||!isset($obj->month)||!isset($obj->year)||!isset($obj->location))  globalfunc::callErrorMsg($parent,array('message'=>'INVALID PARAMS - rmsales')); 
        $date=$obj->year."-".$obj->month."-01";
        $start_date=date('Y-m',strtotime($date))."-01";  
        $end_date=date('Y-m-t',strtotime($date)); 
        $data_arr=array();
        $emailid=$parent->current_user_data->emailid;         
        switch($obj->location)
        {
            case "P":
                $user_cond=" AND u.role=6";
            break;
            case "Z":
                //COND
                $query_all_amarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,rm_email "," status='1' AND is_deleted='0' AND zm_email IN (SELECT zm_email FROM ".DMS_DEALER." WHERE rm_email='".$emailid."' ) GROUP BY rm_email","");  
                $emailid_arr=array();
                if($query_all_amarr['nr']>0)
                {
                    foreach($query_all_amarr['result'] as $res)
                    {
                        $emailid_arr[]="'".$res->rm_email."'";
                    }
                    $user_cond=" AND u.role=6 AND u.emailid IN (".implode(',',$emailid_arr).")";
                }else
                {
                    $user_cond=" AND u.role=6 AND u.emailid IN ('".$emailid."')";
                }
               break; 
            default :
               $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
               break;
        }
            
        //TOP 20 AREA SALES LEADERBOARD
        $parent->executeQuery("SET @rownum := 0");                    
        $get_user_rank_str = "SELECT * FROM (SELECT @rownum := @rownum + 1 AS rank, salecount,targetcount, userid,sale_per FROM (SELECT c.*, CASE WHEN targetcount=0 THEN 0 ELSE (salecount/targetcount)*100 END as 'sale_per' FROM (SELECT a.userid, a.salecount,b.targetcount  FROM (SELECT  COALESCE(SUM(s.sales_count),'0') as salecount, u.userid FROM ".TABLE_USER." as u LEFT JOIN (SELECT rm_email,sale_date,sales_count FROM  ".VIEW_RM_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON u.emailid=s.rm_email   WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.userid  FROM ".TABLE_USER." as u LEFT JOIN (SELECT rm_email,target FROM ".VIEW_RM_SALESTARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON u.emailid=t.rm_email  WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." GROUP BY u.userid ) as b ON a.userid=b.userid )c  ORDER BY cast(sale_per as SIGNED) DESC,cast(salecount as SIGNED) DESC,cast(targetcount as SIGNED) DESC,userid ASC)as a )as k LIMIT 20";
        $get_user_rank_arr=$parent->getQueryValue($get_user_rank_str);
        if(count($get_user_rank_arr)>0)
        {
            foreach ($get_user_rank_arr as $key => $value)
            {
                $get_user_rank_arr[$key]->user=projectfunc::getuserdata($parent,$value->userid);
                $get_user_rank_arr[$key]->percentage=round($value->sale_per);
            }   
             $data_arr= $get_user_rank_arr;
        }                   
       
        
        //GET USER DATA
        $parent->executeQuery("SET @rownum := 0");                    
        $get_current_user_rank_str = "SELECT * FROM (SELECT @rownum := @rownum + 1 AS rank, salecount,targetcount, userid,sale_per FROM (SELECT c.*, CASE WHEN targetcount=0 THEN 0 ELSE (salecount/targetcount)*100 END as 'sale_per' FROM (SELECT a.userid, a.salecount,b.targetcount  FROM (SELECT  COALESCE(SUM(s.sales_count),'0') as salecount, u.userid FROM ".TABLE_USER." as u LEFT JOIN (SELECT rm_email,sale_date,sales_count FROM  ".VIEW_RM_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON u.emailid=s.rm_email WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.userid  FROM ".TABLE_USER." as u LEFT JOIN (SELECT rm_email,target FROM ".VIEW_RM_SALESTARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON u.emailid=t.rm_email WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid ) as b ON a.userid=b.userid )c  ORDER BY cast(sale_per as SIGNED) DESC,cast(salecount as SIGNED) DESC,cast(targetcount as SIGNED) DESC,userid ASC)as a )as k  WHERE userid='".$parent->current_user_id."' LIMIT 1";
        $get_user_rank_arr=$parent->getQueryValue($get_current_user_rank_str);
        // print_r($get_user_rank_arr);
        
        $cur_data_arr=new stdClass();
        $udata=projectfunc::getuserdata($parent,$parent->current_user_id);
        $cur_data_arr->user=$udata;
        foreach($udata as $key=>$res)
        {
            $cur_data_arr->$key=$res;
        }           
        $cur_data_arr->rank='';
        $cur_data_arr->salecount='';
        $cur_data_arr->percentage='';
        
        
        // print_r($cur_data_arr);
        if(count($get_user_rank_arr)>0)
        {
            //print_r($cur_data_arr);
            foreach($get_user_rank_arr[0] as $okey=>$objval)
            {
                 $cur_data_arr->$okey= $objval;                 
            }
            $cur_data_arr->percentage=round($cur_data_arr->sale_per);
            
        }
        
        $get_count_arr=$parent->getQueryValue("SELECT COUNT( userid) total_count FROM ( SELECT a.userid, a.salecount,b.targetcount  FROM (SELECT  COALESCE(SUM(s.sales_count),'0') as salecount, u.userid FROM ".TABLE_USER." as u LEFT JOIN (SELECT rm_email,sale_date,sales_count FROM  ".VIEW_RM_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON u.emailid=s.rm_email   WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.userid  FROM ".TABLE_USER." as u LEFT JOIN (SELECT rm_email,target FROM ".VIEW_RM_SALESTARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON u.emailid=t.rm_email  WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." GROUP BY u.userid ) as b ON a.userid=b.userid )c LIMIT 1");        
        $total_count=(count($get_count_arr)>0)?$get_count_arr[0]->total_count:"0";
                    
        //LOG
        $logstr='LEADERBOARD MY SALES';			 
        globalfunc::insertUserLog($parent,$logstr);
        $parent->response(array('response'=>'success', 'data'=>$data_arr,'user_data'=>$cur_data_arr,'total_count'=>$total_count,'message'=>$this->page_data['success1'] ),200); 
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
    
    /* /////////////////////////////// AMSALES //////////////////////////////////////// */
    private function amsales($parent,$obj)
    {
        if(!isset($obj)||!isset($obj->month)||!isset($obj->year)||!isset($obj->location))  globalfunc::callErrorMsg($parent,array('message'=>'INVALID PARAMS - amsales')); 
        $date=$obj->year."-".$obj->month."-01";
        $start_date=date('Y-m',strtotime($date))."-01";  
        $end_date=date('Y-m-t',strtotime($date)); 
        $data_arr=array();
        $emailid=$parent->current_user_data->emailid;  
        switch($obj->location)
        {
            case "P":
                $user_cond=" AND u.role=7";
            break;
            case "Z":
                //COND
                $query_all_amarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,am_email "," status='1' AND is_deleted='0' AND zm_email IN (SELECT zm_email FROM ".DMS_DEALER." WHERE am_email='".$emailid."' ) GROUP BY am_email","");  
                $emailid_arr=array();
                if($query_all_amarr['nr']>0)
                {
                    foreach($query_all_amarr['result'] as $res)
                    {
                        $emailid_arr[]="'".$res->am_email."'";
                    }
                    $user_cond=" AND u.role=7 AND u.emailid IN (".implode(',',$emailid_arr).")";
                }else
                {
                    $user_cond=" AND u.role=7 AND u.emailid IN ('".$emailid."')";
                }
               break; 
            case "R":
                $query_all_amarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,am_email "," status='1' AND is_deleted='0' AND rm_email IN (SELECT rm_email FROM ".DMS_DEALER." WHERE am_email='".$emailid."' ) GROUP BY am_email","");  
                $emailid_arr=array();
                if($query_all_amarr['nr']>0)
                {
                    foreach($query_all_amarr['result'] as $res)
                    {
                        $emailid_arr[]="'".$res->am_email."'";
                    }
                    $user_cond=" AND u.role=7 AND u.emailid IN (".implode(',',$emailid_arr).")";
                }else
                {
                    $user_cond=" AND u.role=7 AND u.emailid IN ('".$emailid."')";
                } 
                break; 
            default :
               $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
               break;
        }
            
        //TOP 20 AREA SALES LEADERBOARD
        $parent->executeQuery("SET @rownum := 0");                    
        $get_user_rank_str = "SELECT * FROM (SELECT @rownum := @rownum + 1 AS rank, salecount,targetcount, userid,sale_per FROM (SELECT c.*, CASE WHEN targetcount=0 THEN 0 ELSE (salecount/targetcount)*100 END as 'sale_per' FROM (SELECT a.userid, a.salecount,b.targetcount  FROM (SELECT  COALESCE(SUM(s.sales_count),'0') as salecount, u.userid FROM ".TABLE_USER." as u LEFT JOIN (SELECT am_email,sale_date,sales_count FROM  ".VIEW_ASM_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON u.emailid=s.am_email   WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.userid  FROM ".TABLE_USER." as u LEFT JOIN (SELECT am_email,target FROM ".VIEW_ASM_SALESTARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON u.emailid=t.am_email  WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." GROUP BY u.userid ) as b ON a.userid=b.userid )c  ORDER BY cast(sale_per as SIGNED) DESC,cast(salecount as SIGNED) DESC,cast(targetcount as SIGNED) DESC,userid ASC)as a )as k LIMIT 20";
        $get_user_rank_arr=$parent->getQueryValue($get_user_rank_str);
        if(count($get_user_rank_arr)>0)
        {
            foreach ($get_user_rank_arr as $key => $value)
            {
                $get_user_rank_arr[$key]->user=projectfunc::getuserdata($parent,$value->userid);
                $get_user_rank_arr[$key]->percentage=round($value->sale_per);
            }   
             $data_arr= $get_user_rank_arr;
        }                   
       
        
        //GET USER DATA
        $parent->executeQuery("SET @rownum := 0");                    
        $get_current_user_rank_str = "SELECT * FROM (SELECT @rownum := @rownum + 1 AS rank, salecount,targetcount, userid,sale_per FROM (SELECT c.*, CASE WHEN targetcount=0 THEN 0 ELSE (salecount/targetcount)*100 END as 'sale_per' FROM (SELECT a.userid, a.salecount,b.targetcount  FROM (SELECT  COALESCE(SUM(s.sales_count),'0') as salecount, u.userid FROM ".TABLE_USER." as u LEFT JOIN (SELECT am_email,sale_date,sales_count FROM  ".VIEW_ASM_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON u.emailid=s.am_email WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.userid  FROM ".TABLE_USER." as u LEFT JOIN (SELECT am_email,target FROM ".VIEW_ASM_SALESTARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON u.emailid=t.am_email WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid ) as b ON a.userid=b.userid )c  ORDER BY cast(sale_per as SIGNED) DESC,cast(salecount as SIGNED) DESC,cast(targetcount as SIGNED) DESC,userid ASC)as a )as k  WHERE userid='".$parent->current_user_id."' LIMIT 1";
        $get_user_rank_arr=$parent->getQueryValue($get_current_user_rank_str);
        // print_r($get_user_rank_arr);
        
        $cur_data_arr=new stdClass();
        $udata=projectfunc::getuserdata($parent,$parent->current_user_id);
        $cur_data_arr->user=$udata;
        foreach($udata as $key=>$res)
        {
            $cur_data_arr->$key=$res;
        }           
        $cur_data_arr->rank='';
        $cur_data_arr->salecount='';
        $cur_data_arr->percentage='';
        
        
        // print_r($cur_data_arr);
        if(count($get_user_rank_arr)>0)
        {
            //print_r($cur_data_arr);
            foreach($get_user_rank_arr[0] as $okey=>$objval)
            {
                 $cur_data_arr->$okey= $objval;                 
            }
            $cur_data_arr->percentage=round($cur_data_arr->sale_per);
            
        }
        
        $get_count_arr=$parent->getQueryValue("SELECT COUNT( userid) total_count FROM ( SELECT a.userid, a.salecount,b.targetcount  FROM (SELECT  COALESCE(SUM(s.sales_count),'0') as salecount, u.userid FROM ".TABLE_USER." as u LEFT JOIN (SELECT am_email,sale_date,sales_count FROM  ".VIEW_ASM_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON u.emailid=s.am_email   WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.userid  FROM ".TABLE_USER." as u LEFT JOIN (SELECT am_email,target FROM ".VIEW_ASM_SALESTARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON u.emailid=t.am_email  WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." GROUP BY u.userid ) as b ON a.userid=b.userid )c LIMIT 1");        
        $total_count=(count($get_count_arr)>0)?$get_count_arr[0]->total_count:"0";
                    
        //LOG
        $logstr='LEADERBOARD MY SALES';			 
        globalfunc::insertUserLog($parent,$logstr);
        $parent->response(array('response'=>'success', 'data'=>$data_arr,'user_data'=>$cur_data_arr,'total_count'=>$total_count,'message'=>$this->page_data['success1'] ),200); 
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
                
    /* /////////////////////////////// DSESALES //////////////////////////////////////// */
    private function dsesales($parent,$obj)
    {
        if(!isset($obj)||!isset($obj->month)||!isset($obj->year)||!isset($obj->location))  globalfunc::callErrorMsg($parent,array('message'=>'INVALID PARAMS - dsesales')); 
        $date=$obj->year."-".$obj->month."-01";
        $start_date=date('Y-m',strtotime($date))."-01";  
        $end_date=date('Y-m-t',strtotime($date)); 
        
        $date_cond=" AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59'";
        $dealer_code=$parent->current_user_data->dealer_code; 
        $data_arr=array();
        switch($obj->location)
        {
            case "P":
                //COND
                $join_cond='';
                $area_cond='';
                break;
            case "Z":
                //COND
                $query_allareadealerarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,zm_email ","  dealer_code='".$dealer_code."'  AND status='1' AND is_deleted='0'","1");      
                $dealer_arr=array();
                if($query_allareadealerarr['nr']) 
                {
                    $join_cond="JOIN ".DMS_DEALER." as d ON d.dealer_code=u.dealer_code ";
                    $area_cond=" and d.zm_email ='".$query_allareadealerarr['result'][0]->zm_email."'";
                }else
                {
                    $area_cond=" and u.dealer_code IN ('')";
                }
                break; 
            case "R":
                //COND
                $query_allareadealerarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,rm_email ","  dealer_code='".$dealer_code."'  AND status='1' AND is_deleted='0'","1");       
                $dealer_arr=array();
                if($query_allareadealerarr['nr']) 
                {
                    $join_cond="JOIN ".DMS_DEALER." as d ON d.dealer_code=u.dealer_code ";
                    $area_cond=" and d.rm_email ='".$query_allareadealerarr['result'][0]->rm_email."'";
                }else
                {
                    $area_cond=" and u.dealer_code IN ('')";
                }
                break; 
            case "A":
                
                //COND
                $query_allareadealerarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,am_email ","  dealer_code='".$dealer_code."'  AND status='1' AND is_deleted='0'","1");       
                $dealer_arr=array();
                if($query_allareadealerarr['nr']) 
                {
                    $join_cond="JOIN ".DMS_DEALER." as dd ON dd.dealer_code=u.dealer_code ";
                    $area_cond=" and dd.am_email ='".$query_allareadealerarr['result'][0]->am_email."'";
                }else
                {
                    $area_cond=" and u.dealer_code IN ('')";
                }
                break;    
            default :
                $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
                break;
        }
        //NEW ROLE CONDITION
        $user_cond=($parent->current_user_role>7 && $parent->current_user_role<14)?" AND (u.role>7 AND u.role<14) ":" AND u.role=15 ";
        
        //TOP 20 AREA SALES LEADERBOARD
        $parent->executeQuery("SET @rownum := 0");                    
        $get_user_rank_str = "SELECT * FROM (SELECT @rownum := @rownum + 1 AS rank, salecount,targetcount,smpin,userid ,sale_per FROM (SELECT c.*, CASE WHEN targetcount=0 THEN 0 ELSE (salecount/targetcount)*100 END as 'sale_per' FROM ( SELECT a.userid,a.smpin,a.salecount,b.targetcount  FROM (SELECT COALESCE(COUNT(s.id),'0') as scount, COALESCE(SUM(s.sales_count),'0') as salecount, u.smpin,u.userid ,u.dealer_code,u.role FROM ".TABLE_USER." as u JOIN ".DMS_DEALER." ds ON u.dealer_code=ds.dealer_code  LEFT JOIN (SELECT id,smpin,sale_date,sales_count FROM ".DMS_EMPLOYEE_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON s.smpin=u.smpin ".$join_cond." WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." ".$area_cond." GROUP BY u.smpin ) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.smpin,u.userid,u.dealer_code,u.role FROM ".TABLE_USER." as u LEFT JOIN (SELECT id,smpin,target FROM ".TABLE_USER_SALETARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON t.smpin=u.smpin ".$join_cond." WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." ".$area_cond."  GROUP BY u.smpin ) as b ON a.userid=b.userid )c  ORDER BY cast(sale_per as SIGNED) DESC,cast(salecount as SIGNED) DESC,cast(targetcount as SIGNED) DESC ,userid ASC)as a )as k LIMIT 20";
        $get_user_rank_arr=$parent->getQueryValue($get_user_rank_str);
        if(count($get_user_rank_arr)>0)
        {
            foreach ($get_user_rank_arr as $key => $value)
            {
                $get_user_rank_arr[$key]->user=projectfunc::getuserdata($parent,$value->userid);
                $get_user_rank_arr[$key]->percentage=round($value->sale_per);
            }   
             $data_arr= $get_user_rank_arr;
        }                   
       
        
        //GET USER DATA
        $parent->executeQuery("SET @rownum := 0");                    
        $get_current_user_rank_str = "SELECT * FROM (SELECT @rownum := @rownum + 1 AS rank, salecount,targetcount,smpin,userid, sale_per FROM (SELECT c.*, CASE WHEN targetcount=0 THEN 0 ELSE (salecount/targetcount)*100 END as 'sale_per' FROM ( SELECT a.userid,a.smpin,a.salecount,b.targetcount  FROM (SELECT COALESCE(COUNT(s.id),'0') as scount,  COALESCE(SUM(s.sales_count),'0') as salecount, u.smpin,u.userid ,u.dealer_code,u.role FROM ".TABLE_USER." as u JOIN ".DMS_DEALER." ds ON u.dealer_code=ds.dealer_code  LEFT JOIN (SELECT id,smpin,sale_date,sales_count FROM ".DMS_EMPLOYEE_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON s.smpin=u.smpin ".$join_cond." WHERE u.is_deleted=0  AND u.status=1 ".$user_cond." ".$area_cond." GROUP BY u.smpin ) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.smpin,u.userid,u.dealer_code,u.role FROM ".TABLE_USER." as u LEFT JOIN (SELECT id,smpin,target FROM ".TABLE_USER_SALETARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON t.smpin=u.smpin ".$join_cond." WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." ".$area_cond."  GROUP BY u.smpin ) as b ON a.userid=b.userid )c ORDER BY cast(sale_per as SIGNED) DESC,cast(salecount as SIGNED) DESC,cast(targetcount as SIGNED) DESC, userid ASC)as a )as k  WHERE userid='".$parent->current_user_id."' LIMIT 1";
        $get_user_rank_arr=$parent->getQueryValue($get_current_user_rank_str);
        // print_r($get_user_rank_arr);
        
        $cur_data_arr=new stdClass();
        $udata=projectfunc::getuserdata($parent,$parent->current_user_id);
        $cur_data_arr->user=$udata;
        foreach($udata as $key=>$res)
        {
            $cur_data_arr->$key=$res;
        }           
        $cur_data_arr->rank='';
        $cur_data_arr->salecount='';
        $cur_data_arr->percentage='';
        
        
        // print_r($cur_data_arr);
        if(count($get_user_rank_arr)>0)
        {
            //print_r($cur_data_arr);
            foreach($get_user_rank_arr[0] as $okey=>$objval)
            {
                 $cur_data_arr->$okey= $objval;                 
            }
            $cur_data_arr->percentage=round($cur_data_arr->sale_per);
            
        }
        
        $get_count_arr=$parent->getQueryValue("SELECT COUNT( userid) total_count FROM ( SELECT a.userid,a.smpin,a.salecount,b.targetcount FROM (SELECT COALESCE(COUNT(s.id),'0') as scount, COALESCE(SUM(s.sales_count),'0') as salecount, u.smpin,u.userid ,u.dealer_code,u.role FROM ".TABLE_USER." as u JOIN ".DMS_DEALER." ds ON u.dealer_code=ds.dealer_code  LEFT JOIN (SELECT id,smpin,sale_date,sales_count FROM ".DMS_EMPLOYEE_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON s.smpin=u.smpin ".$join_cond." WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." ".$area_cond." GROUP BY u.smpin ) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.smpin,u.userid,u.dealer_code,u.role FROM ".TABLE_USER." as u LEFT JOIN (SELECT id,smpin,target FROM ".TABLE_USER_SALETARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON t.smpin=u.smpin ".$join_cond." WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  ".$area_cond."  GROUP BY u.smpin ) as b ON a.userid=b.userid )c LIMIT 1");        
        $total_count=(count($get_count_arr)>0)?$get_count_arr[0]->total_count:"0";
                    
        //LOG
        $logstr='LEADERBOARD MY SALES';			 
        globalfunc::insertUserLog($parent,$logstr);
        $parent->response(array('response'=>'success', 'data'=>$data_arr,'user_data'=>$cur_data_arr,'total_count'=>$total_count,'message'=>$this->page_data['success1'] ),200);    
        
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
    
    /* /////////////////////////////// MYTEAM SALES //////////////////////////////////////// */
	private function myteamsales($parent)
    {
        globalfunc::checkMethodType($parent,array('POST'));	
        globalfunc::checkParamCount($parent,1);
		globalfunc::checkAuthrozation($parent,array('type'=>'user'));
        projectfunc::authorizedRole($parent,array(3,4,5,6,7,14));
        
        $REQUEST_KEYS=array('month'=>'string','year'=>'number','role'=>'string','location'=>'string');
		globalfunc::mandatoryKey($parent,$REQUEST_KEYS);        
		$month=escape_str($parent->_request['month']);
        $year=escape_str($parent->_request['year']);
        $role=escape_str($parent->_request['role']);
        $location=escape_str($parent->_request['location']);
        $location_data=@explode('||',$location);
        $location_level=$location_data[0];
        $location_email=isset($location_data[1])?$location_data[1]:'';
        
        $obj=new stdClass();
        $obj->month=$month;
        $obj->year=$year;
        $obj->role=$role;
        $obj->level=$location_level;
        $obj->email=$location_email;
        switch($obj->role)
        {
            case 'DSE': 
            case 'IDSE':    
                $this->dseteamsales($parent,$obj);
                break;
            case 'AM':
                $this->amteamsales($parent,$obj);
                break;
            case 'RM': 
                $this->rmteamsales($parent,$obj);
                break;
            case 'TR': 
                $this->trainerteamsales($parent,$obj);
                break;
            case 'ZM': 
                $this->zmteamsales($parent,$obj);
                break;
            default :
                $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
                break;
        } 
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
            
    /* /////////////////////////////// ZMTEAMSALES //////////////////////////////////////// */   
    private function zmteamsales($parent,$obj)
    {
        if(!isset($obj)||!isset($obj->month)||!isset($obj->year)||!isset($obj->level)||!isset($obj->email))  globalfunc::callErrorMsg($parent,array('message'=>'INVALID PARAMS - zmteamassessment'));
        $date=$obj->year."-".$obj->month."-01";
        $start_date=date('Y-m',strtotime($date))."-01";  
        $end_date=date('Y-m-t',strtotime($date));  
        $date_cond=" AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59'";
        $data_arr=array();    
        switch($obj->level)
        {
            case "P":
                $user_cond=" AND u.role=4";
            break;
            
            default :
               $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
               break;
        }
        //TOP 20 AREA SALES LEADERBOARD
        $parent->executeQuery("SET @rownum := 0");                    
        $get_user_rank_str = "SELECT * FROM (SELECT @rownum := @rownum + 1 AS rank, salecount,targetcount, userid,sale_per FROM (SELECT c.*, CASE WHEN targetcount=0 THEN 0 ELSE (salecount/targetcount)*100 END as 'sale_per' FROM (SELECT a.userid, a.salecount,b.targetcount  FROM (SELECT  COALESCE(SUM(s.sales_count),'0') as salecount, u.userid FROM ".TABLE_USER." as u LEFT JOIN (SELECT zm_email,sale_date,sales_count FROM  ".VIEW_ZM_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON u.emailid=s.zm_email   WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.userid  FROM ".TABLE_USER." as u LEFT JOIN (SELECT zm_email,target FROM ".VIEW_ZM_SALESTARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON u.emailid=t.zm_email  WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." GROUP BY u.userid ) as b ON a.userid=b.userid )c  ORDER BY cast(sale_per as SIGNED) DESC,cast(salecount as SIGNED) DESC,cast(targetcount as SIGNED) DESC,userid ASC)as a )as k";
        // LIMIT 20
        $get_user_rank_arr=$parent->getQueryValue($get_user_rank_str);
        if(count($get_user_rank_arr)>0)
        {
            foreach ($get_user_rank_arr as $key => $value)
            {
                $get_user_rank_arr[$key]->user=projectfunc::getuserdata($parent,$value->userid);
                $get_user_rank_arr[$key]->percentage=round($value->sale_per);
            }   
             $data_arr= $get_user_rank_arr;
        }  
        
        $get_count_arr=$parent->getQueryValue("SELECT COUNT( userid) total_count FROM ( SELECT a.userid, a.salecount,b.targetcount  FROM (SELECT  COALESCE(SUM(s.sales_count),'0') as salecount, u.userid FROM ".TABLE_USER." as u LEFT JOIN (SELECT zm_email,sale_date,sales_count FROM  ".VIEW_ZM_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON u.emailid=s.zm_email   WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.userid  FROM ".TABLE_USER." as u LEFT JOIN (SELECT zm_email,target FROM ".VIEW_ZM_SALESTARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON u.emailid=t.zm_email  WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." GROUP BY u.userid ) as b ON a.userid=b.userid )c LIMIT 1");        
        $total_count=(count($get_count_arr)>0)?$get_count_arr[0]->total_count:"0";
                    
        //LOG
        $logstr='LEADERBOARD MY TEAM SALES';			 
        globalfunc::insertUserLog($parent,$logstr);
        $parent->response(array('response'=>'success', 'data'=>$data_arr,'total_count'=>$total_count,'message'=>$this->page_data['success1'] ),200); 
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
    
     /* /////////////////////////////// TRAINERTEAMSALES //////////////////////////////////////// */   
    private function trainerteamsales($parent,$obj)
    {
        if(!isset($obj)||!isset($obj->month)||!isset($obj->year)||!isset($obj->level)||!isset($obj->email))  globalfunc::callErrorMsg($parent,array('message'=>'INVALID PARAMS - zmteamassessment'));
        $date=$obj->year."-".$obj->month."-01";
        $start_date=date('Y-m',strtotime($date))."-01";  
        $end_date=date('Y-m-t',strtotime($date));  
        $date_cond=" AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59'";
        $data_arr=array();    
            
                    
        //LOG
        $logstr='LEADERBOARD MY TEAM SALES';			 
        globalfunc::insertUserLog($parent,$logstr);
        $parent->response(array('response'=>'success', 'data'=>array(),'total_count'=>0,'message'=>$this->page_data['success1'] ),200); 
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
    
    /* /////////////////////////////// RMTEAMSALES //////////////////////////////////////// */   
    private function rmteamsales($parent,$obj)
    {
        if(!isset($obj)||!isset($obj->month)||!isset($obj->year)||!isset($obj->level)||!isset($obj->email))  globalfunc::callErrorMsg($parent,array('message'=>'INVALID PARAMS - zmteamassessment'));
        
        $date=$obj->year."-".$obj->month."-01";
        $start_date=date('Y-m',strtotime($date))."-01";  
        $end_date=date('Y-m-t',strtotime($date));  
        $date_cond=" AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59'";
        $data_arr=array();    
        switch($obj->level)
        {
            case "P":
                $user_cond=" AND u.role=6";
            break;
            case "Z":
                //COND
                $email_cond= " AND zm_email='".$obj->email."' ";
                $query_all_amarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,rm_email "," status='1' AND is_deleted='0' ".$email_cond." GROUP BY rm_email","");  
                $emailid_arr=array();
                if($query_all_amarr['nr']>0)
                {
                    foreach($query_all_amarr['result'] as $res)
                    {
                        $emailid_arr[]="'".$res->rm_email."'";
                    }
                    $user_cond=" AND u.role=6 AND u.emailid IN (".implode(',',$emailid_arr).")";
                }else
                {
                    $user_cond=" AND u.role=6 AND u.emailid IN ('')";
                }
               break; 
            default :
               $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
               break;
        }
        //TOP 20 AREA SALES LEADERBOARD
        $parent->executeQuery("SET @rownum := 0");                    
        $get_user_rank_str = "SELECT * FROM (SELECT @rownum := @rownum + 1 AS rank, salecount,targetcount, userid,sale_per FROM (SELECT c.*, CASE WHEN targetcount=0 THEN 0 ELSE (salecount/targetcount)*100 END as 'sale_per' FROM (SELECT a.userid, a.salecount,b.targetcount  FROM (SELECT  COALESCE(SUM(s.sales_count),'0') as salecount, u.userid FROM ".TABLE_USER." as u LEFT JOIN (SELECT rm_email,sale_date,sales_count FROM  ".VIEW_RM_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON u.emailid=s.rm_email   WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.userid  FROM ".TABLE_USER." as u LEFT JOIN (SELECT rm_email,target FROM ".VIEW_RM_SALESTARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON u.emailid=t.rm_email  WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." GROUP BY u.userid ) as b ON a.userid=b.userid )c  ORDER BY cast(sale_per as SIGNED) DESC,cast(salecount as SIGNED) DESC,cast(targetcount as SIGNED) DESC,userid ASC)as a )as k";
        // LIMIT 20
        $get_user_rank_arr=$parent->getQueryValue($get_user_rank_str);
        if(count($get_user_rank_arr)>0)
        {
            foreach ($get_user_rank_arr as $key => $value)
            {
                $get_user_rank_arr[$key]->user=projectfunc::getuserdata($parent,$value->userid);
                $get_user_rank_arr[$key]->percentage=round($value->sale_per);
            }   
             $data_arr= $get_user_rank_arr;
        }                   
            
        
        $get_count_arr=$parent->getQueryValue("SELECT COUNT( userid) total_count FROM ( SELECT a.userid, a.salecount,b.targetcount  FROM (SELECT  COALESCE(SUM(s.sales_count),'0') as salecount, u.userid FROM ".TABLE_USER." as u LEFT JOIN (SELECT rm_email,sale_date,sales_count FROM  ".VIEW_RM_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON u.emailid=s.rm_email   WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.userid  FROM ".TABLE_USER." as u LEFT JOIN (SELECT rm_email,target FROM ".VIEW_RM_SALESTARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON u.emailid=t.rm_email  WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." GROUP BY u.userid ) as b ON a.userid=b.userid )c LIMIT 1");        
        $total_count=(count($get_count_arr)>0)?$get_count_arr[0]->total_count:"0";
                    
        //LOG
        $logstr='LEADERBOARD MY TEAM SALES';			 
        globalfunc::insertUserLog($parent,$logstr);
        $parent->response(array('response'=>'success', 'data'=>$data_arr, 'total_count'=>$total_count,'message'=>$this->page_data['success1'] ),200); 
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
    
    /* /////////////////////////////// AMTEAMSALES //////////////////////////////////////// */   
    private function amteamsales($parent,$obj)
    {
        if(!isset($obj)||!isset($obj->month)||!isset($obj->year)||!isset($obj->level)||!isset($obj->email))  globalfunc::callErrorMsg($parent,array('message'=>'INVALID PARAMS - zmteamassessment'));
        $date=$obj->year."-".$obj->month."-01";
        $start_date=date('Y-m',strtotime($date))."-01";  
        $end_date=date('Y-m-t',strtotime($date));  
        $date_cond=" AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59'";
        $data_arr=array();    
        switch($obj->level)
        {
            case "P":
                $user_cond=" AND u.role=7";
            break;
            case "Z":
                //COND
                $email_cond= " AND zm_email='".$obj->email."' ";
            
                $query_all_amarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,am_email "," status='1' AND is_deleted='0' ".$email_cond." GROUP BY am_email","");  
                $emailid_arr=array();
                if($query_all_amarr['nr']>0)
                {
                    foreach($query_all_amarr['result'] as $res)
                    {
                        $emailid_arr[]="'".$res->am_email."'";
                    }
                    $user_cond=" AND u.role=7 AND u.emailid IN (".implode(',',$emailid_arr).")";
                }else
                {
                    $user_cond=" AND u.role=7 AND u.emailid IN ('')";
                }
               break; 
            case "R":
                $email_cond= " AND rm_email='".$obj->email."' ";
                $query_all_amarr=$parent->selectQuery(DMS_DEALER,"`dealer_code`,am_email "," status='1' AND is_deleted='0' ".$email_cond." GROUP BY am_email","");  
                $emailid_arr=array();
                if($query_all_amarr['nr']>0)
                {
                    foreach($query_all_amarr['result'] as $res)
                    {
                        $emailid_arr[]="'".$res->am_email."'";
                    }
                    $user_cond=" AND u.role=7 AND u.emailid IN (".implode(',',$emailid_arr).")";
                }else
                {
                    $user_cond=" AND u.role=7 AND u.emailid IN ('')";
                } 
                break; 
            default :
               $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
               break;
        }
        
        //TOP 20 AREA SALES LEADERBOARD
        $parent->executeQuery("SET @rownum := 0");                    
        $get_user_rank_str = "SELECT * FROM (SELECT @rownum := @rownum + 1 AS rank, salecount,targetcount, userid,sale_per FROM (SELECT c.*, CASE WHEN targetcount=0 THEN 0 ELSE (salecount/targetcount)*100 END as 'sale_per' FROM (SELECT a.userid, a.salecount,b.targetcount  FROM (SELECT  COALESCE(SUM(s.sales_count),'0') as salecount, u.userid FROM ".TABLE_USER." as u LEFT JOIN (SELECT am_email,sale_date,sales_count FROM  ".VIEW_ASM_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON u.emailid=s.am_email   WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.userid  FROM ".TABLE_USER." as u LEFT JOIN (SELECT am_email,target FROM ".VIEW_ASM_SALESTARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON u.emailid=t.am_email  WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." GROUP BY u.userid ) as b ON a.userid=b.userid )c  ORDER BY cast(sale_per as SIGNED) DESC,cast(salecount as SIGNED) DESC,cast(targetcount as SIGNED) DESC,userid ASC)as a )as k";
        // LIMIT 20
        $get_user_rank_arr=$parent->getQueryValue($get_user_rank_str);
        if(count($get_user_rank_arr)>0)
        {
            foreach ($get_user_rank_arr as $key => $value)
            {
                $get_user_rank_arr[$key]->user=projectfunc::getuserdata($parent,$value->userid);
                $get_user_rank_arr[$key]->percentage=round($value->sale_per);
            }   
             $data_arr= $get_user_rank_arr;
        }                   
            
        $get_count_arr=$parent->getQueryValue("SELECT COUNT( userid) total_count FROM ( SELECT a.userid, a.salecount,b.targetcount  FROM (SELECT  COALESCE(SUM(s.sales_count),'0') as salecount, u.userid FROM ".TABLE_USER." as u LEFT JOIN (SELECT am_email,sale_date,sales_count FROM  ".VIEW_ASM_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON u.emailid=s.am_email   WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  GROUP BY u.userid) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.userid  FROM ".TABLE_USER." as u LEFT JOIN (SELECT am_email,target FROM ".VIEW_ASM_SALESTARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON u.emailid=t.am_email  WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." GROUP BY u.userid ) as b ON a.userid=b.userid )c LIMIT 1");        
        $total_count=(count($get_count_arr)>0)?$get_count_arr[0]->total_count:"0";
                    
        //LOG
        $logstr='LEADERBOARD MY TEAM SALES';			 
        globalfunc::insertUserLog($parent,$logstr);
        $parent->response(array('response'=>'success', 'data'=>$data_arr,'total_count'=>$total_count,'message'=>$this->page_data['success1'] ),200); 
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
    
    /* /////////////////////////////// DSETEAMSALES //////////////////////////////////////// */   
    private function dseteamsales($parent,$obj)
    {
        if(!isset($obj)||!isset($obj->month)||!isset($obj->year)||!isset($obj->level)||!isset($obj->email))  globalfunc::callErrorMsg($parent,array('message'=>'INVALID PARAMS - dseteamsales'));
        $date=$obj->year."-".$obj->month."-01";
        $start_date=date('Y-m',strtotime($date))."-01";  
        $end_date=date('Y-m-t',strtotime($date));  
        //$date_cond=" AND a.publisheddate>='".$start_date." 00:00:00'  AND a.publisheddate<='".$end_date." 23:59:59'";
        $data_arr=array();
        /*
        switch($obj->level)
        {
            case "P":
                $join_cond='';
                $area_cond='';
            break;
            case "Z":
                //COND
            
                if($obj->email!='') 
                {
                    $join_cond="JOIN ".DMS_DEALER." as dd ON dd.dealer_code=u.dealer_code ";
                    $area_cond=" and dd.zm_email ='".$obj->email."'";
                }else
                {
                    $area_cond=" and u.dealer_code IN ('')";
                }
               break; 
            case "R":
                //COND
            
               if($obj->email!='') 
               {
                   $join_cond="JOIN ".DMS_DEALER." as dd ON dd.dealer_code=u.dealer_code ";
                   $area_cond=" and dd.rm_email ='".$obj->email."'";
               }else
               {
                   $area_cond=" and u.dealer_code IN ('')";
               }
               break;  
            case "A":                
                //COND            
                if($obj->email!='') 
                {
                    $join_cond="JOIN ".DMS_DEALER." as dd ON dd.dealer_code=u.dealer_code ";
                    $area_cond=" and dd.am_email ='".$obj->email."'";
                }else
                {
                    $area_cond=" and u.dealer_code IN ('')";
                }
               break;    
            default :
               $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404);
               break;
       }
        */
        $zm_email = escape_str($parent->_request["zmemail"]);
        $rm_email = escape_str($parent->_request["rmemail"]);
        $am_email = escape_str($parent->_request["amemail"]);  
        
        $mcond='';
        if($parent->current_user_role!='14')
        {
            $mcond=($am_email!='all' && $am_email!='')?" AND dd.am_email='".$am_email."'":''; 
        }
        $mcond.=($rm_email!='all' && $rm_email!='')?" AND dd.rm_email='".$rm_email."'":'';  
		if($parent->current_user_role!='5')
        {		
			$mcond.=($zm_email!='all' && $zm_email!='')?" AND dd.zm_email='".$zm_email."'":'';    
        }
        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);
            $mcond.=(count($trainer_arr)>0)?"AND dd.zone='".$trainer_arr[0]->zone."'":"AND dd.zone='0'";
        }
        
        if($parent->current_user_role=='14')
        {
            $get_dealer_str = "SELECT d.am_email FROM  ".TABLE_USER." u JOIN ".DMS_DEALER." as d ON u.dealer_code=d.dealer_code WHERE u.userid='".$parent->current_user_id."' AND u.is_deleted=0 LIMIT 1";
            $dealer_arr=$parent->getQueryValue($get_dealer_str);
            
            $mcond.=(count($dealer_arr)>0)?"AND dd.am_email='".$dealer_arr[0]->am_email."'":"AND dd.am_email=''";
        }
        
        $join_cond="JOIN ".DMS_DEALER." as dd ON dd.dealer_code=u.dealer_code ";
        $area_cond=$mcond;
        
		
        //NEW ROLE CONDITION
        $user_cond=($obj->role=='DSE')?" AND (u.role>7 AND u.role<14) ":" AND u.role=15 ";
        
        //TOP 20 AREA SALES LEADERBOARD
        $parent->executeQuery("SET @rownum := 0");                    
        $get_user_rank_str = "SELECT * FROM (SELECT @rownum := @rownum + 1 AS rank, salecount,targetcount,smpin,userid, sale_per FROM (SELECT c.*, CASE WHEN targetcount=0 THEN 0 ELSE (salecount/targetcount)*100 END as 'sale_per' FROM ( SELECT a.userid,a.smpin,a.salecount,b.targetcount  FROM (SELECT COALESCE(COUNT(s.id),'0') as scount, COALESCE(SUM(s.sales_count),'0') as salecount, u.smpin,u.userid ,u.dealer_code,u.role FROM ".TABLE_USER." as u JOIN ".DMS_DEALER." ds ON u.dealer_code=ds.dealer_code  LEFT JOIN (SELECT id,smpin,sale_date,sales_count FROM ".DMS_EMPLOYEE_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON s.smpin=u.smpin ".$join_cond." WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." ".$area_cond." GROUP BY u.smpin ) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.smpin,u.userid,u.dealer_code,u.role FROM ".TABLE_USER." as u LEFT JOIN (SELECT id,smpin,target FROM ".TABLE_USER_SALETARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON t.smpin=u.smpin ".$join_cond." WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." ".$area_cond."  GROUP BY u.smpin ) as b ON a.userid=b.userid )c  ORDER BY cast(sale_per as SIGNED) DESC,cast(salecount as SIGNED) DESC,cast(targetcount as SIGNED) DESC, userid ASC)as a )as k LIMIT 20";
        $get_user_rank_arr=$parent->getQueryValue($get_user_rank_str);
        if(count($get_user_rank_arr)>0)
        {
            foreach ($get_user_rank_arr as $key => $value)
            {
                $get_user_rank_arr[$key]->user=projectfunc::getuserdata($parent,$value->userid);
                $get_user_rank_arr[$key]->percentage=round($value->sale_per);
            }   
            $data_arr= $get_user_rank_arr;
        }                   
            
        
        $get_count_arr=$parent->getQueryValue("SELECT COUNT( userid) total_count FROM ( SELECT a.userid,a.smpin,a.salecount,b.targetcount FROM (SELECT COALESCE(COUNT(s.id),'0') as scount, COALESCE(SUM(s.sales_count),'0') as salecount, u.smpin,u.userid,u.dealer_code,u.role FROM ".TABLE_USER." as u JOIN ".DMS_DEALER." ds ON u.dealer_code=ds.dealer_code  LEFT JOIN (SELECT id,smpin,sale_date,sales_count FROM ".DMS_EMPLOYEE_SALES." WHERE sale_date>='".$start_date."' AND sale_date<='".$end_date."') as s ON s.smpin=u.smpin ".$join_cond." WHERE u.is_deleted=0 AND u.status=1 ".$user_cond." ".$area_cond." GROUP BY u.smpin ) as a JOIN  (SELECT  COALESCE(SUM(t.target),'0') as targetcount,u.smpin,u.userid,u.dealer_code,u.role FROM ".TABLE_USER." as u LEFT JOIN (SELECT id,smpin,target FROM ".TABLE_USER_SALETARGET." WHERE sale_month>='".$start_date."' AND sale_month<='".$end_date."')as t ON t.smpin=u.smpin ".$join_cond." WHERE u.is_deleted=0 AND u.status=1 ".$user_cond."  ".$area_cond."  GROUP BY u.smpin ) as b ON a.userid=b.userid )c LIMIT 1");        
        $total_count=(count($get_count_arr)>0)?$get_count_arr[0]->total_count:"0";
                    
        //LOG
        $logstr='LEADERBOARD MY TEAM SALES';			 
        globalfunc::insertUserLog($parent,$logstr);
        $parent->response(array('response'=>'success', 'data'=>$data_arr, 'total_count'=>$total_count,'message'=>$this->page_data['success1'] ),200); 
    }
    /* //////////////////////////////////////////////////////////////////////////////////////////// */
}

?>