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); } /* //////////////////////////////////////////////////////////////////////////////////////////// */ } ?>