opensourcecode/Hyper-M_Admin_Portal/services/training.php

<?php
include "Connection.php";
require_once(dirname(__FILE__)."/_api/global.php");
$conn = new Connection();
 

if(isset($_REQUEST["funName"])) {
	$db = $conn->getConnection();
	$fun = $_REQUEST["funName"];
	switch($fun) {
		case "participantlist":
        case "trainerlist":        
        case "traininglist":   
        case "addtraining": 
        case "edittraining":   
        case "deletetraining":       
        case "trainingdetails":    
            
        case "batchlist":
        case "batchuserlist":    
        //case "addbatch": 
        //case "editbatch":    
        //case "deletebatch":        
			$fun();     
		break;
        
	}
}
        
// PARTICIPANT  LIST
function participantlist() {
	global $conn;
	try {
        $lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';
        
		if(isset($_SESSION["userid"]))
        {
            $dealercond='';
            $cond='';
            
            //GET LEVEL
            $lvl_id=(isset($_REQUEST["lvl_id"]) )?escape_str($_REQUEST["lvl_id"]):$conn->message(false,$conn->getMessage($lang,'training','error5'));
        
             if((isset($_REQUEST["zone"])))
            {
                $zone = (isset($_REQUEST["zone"]))?escape_str($_REQUEST["zone"]):'';
                if($zone)
                {
                    $zone_arr=@explode(',',$zone);
                    $zone_quotes = array_map("quotes_array", $zone_arr);                 
                    $dealercond.=" AND zone IN (".@implode(',',$zone_quotes).")";
                }else
                {
                    $dealercond.=" AND zone IN ('')";
                }
            }
            if(isset($_REQUEST["city"]) )
            {
                $city = (isset($_REQUEST["city"]) )?escape_str($_REQUEST["city"]):'';
                if($city)
                {
                    $city_arr=@explode(',',$city);
                    $city_quotes = array_map("quotes_array", $city_arr);                 
                    $dealercond.=" AND city IN (".@implode(',',$city_quotes).")";
                }else
                {
                    $dealercond.=" AND city IN ('')";
                }
            }        
            if((isset($_REQUEST["dealer"])))
            {
                $dealer = (isset($_REQUEST["dealer"]))?escape_str($_REQUEST["dealer"]):'';
                if($dealer)
                {
                    $dealer_arr=@explode(',',$dealer);
                    $dealer_quotes = array_map("quotes_array", $dealer_arr);                 
                    $cond.=" AND dealer IN (".@implode(',',$dealer_quotes).")";
                    $dealer_code_cond=" AND dealer_code IN (".@implode(',',$dealer_quotes).")";
                }else
                {
                    $cond.=" AND dealer IN ('')";
                    $dealer_code_cond=" AND dealer_code IN ('')";
                }
            }
            //FOR EDIT SCHEDULE
            if((isset($_REQUEST["schedule_id"])))
            {
                $edit_cond=" AND s.schedule_id!='".escape_str($_REQUEST["schedule_id"])."'";
            }
            
            $user_cond=(isset($_REQUEST["user_type"]) &&  $_REQUEST["user_type"]=='1')?" AND role=15":" AND role>7 AND role<14";   
        
            $data_arr=array();
            $dealercond=($dealercond!='')?" AND dealer_code IN  (SELECT * FROM ".DMS_DEALER." WHERE status='1' AND ".$dealercond.")":"";
        
            $dealer_arr=$conn->getQueryValue("SELECT * FROM ".DMS_DEALER." WHERE  status='1'  ".$dealer_code_cond.$dealercond);
            
            if(count($dealer_arr)>0)
            {  
              
                foreach($dealer_arr as $res_dealer)
                {
                    $arr=new stdClass();
                    
                $query_str = "SELECT userid,`smpin`, CONCAT(fname,lname) as name FROM ".TABLE_USER." WHERE status='1' ".$user_cond." AND is_deleted='0' AND dealer_code='".$res_dealer->dealer_code."' AND smpin IN (SELECT smpin FROM ".TABLE_USER_LEARNING." WHERE current_status='1' AND lvl_id='".$lvl_id."') AND userid NOT IN (SELECT p.userid FROM ".TABLE_TRAINING_PARTICIPANT." p JOIN ".TABLE_TRAINING_SCHEDULE." s ON p.schedule_id=s.schedule_id WHERE s.status<4 AND s.is_deleted=0 ".$edit_cond.")  ORDER BY dealer_code"; 
                    $user_arr=$conn->getQueryValue($query_str);
                    if(count($user_arr)>0)
                    {
                        $arr->user =$user_arr;
                        $arr->dealer_code=$res_dealer->dealer_code;
                        $arr->dealer_name=$res_dealer->dealer_name;
                        $data_arr[]=$arr;
                    }
                }
            }
            $conn->message(true,$conn->getMessage($lang,'training','success1'), $data_arr); 
            
		}else {
			$conn->message(false,$conn->getMessage($lang,'training','error1'));
		}
	}
	catch(PDOException $e) {
		$conn->message(false,$conn->getMessage($lang,'training','error4'));
	}	
}
        
//TRAINING LIST
function traininglist () {
	global $conn;
	try { 
        
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';
        
		$pn = escape_str($_REQUEST["pn"]);
		$nr = escape_str($_REQUEST["nr"]);
		$key = escape_str($_REQUEST["key"]);
        
        
		if(isset($_SESSION["userid"])) {
            
            $excond='';
            
            $month = escape_str($_REQUEST["month"]);
            $year = escape_str($_REQUEST["year"]);
            
            if ($month!='' && $year!='') {
                $startmonth=date('Y-m-d',strtotime($year."-".$month."-01"))." 00:00:00"; 
                $nextmonth=date('Y-m-d',strtotime('+1 month',strtotime($startmonth)))." 23:59:59";
                
        
                $excond .= " AND ((a.start_date BETWEEN CAST('".$startmonth."' AS DATE) AND CAST('".$nextmonth."' AS DATE)) OR (a.end_date BETWEEN CAST('".$startmonth."' AS DATE)AND CAST('".$nextmonth."' AS DATE))OR ( CAST('".$startmonth."' AS DATE) BETWEEN a.start_date AND a.end_date) OR ( CAST('".$nextmonth."' AS DATE) BETWEEN a.start_date AND a.end_date))";
			}
        
            if ($key!='') {
				$excond .= " AND a.schedule_name like '%".$key."%'  ";
			}
            //PAGINATION
            if (!empty($pn) and !empty($nr)) {
				// Navigation
    			$limitcond = " limit ".(($pn - 1) *  $nr).", " . $nr;
			}
            
			$query_str = "SELECT SQL_CALC_FOUND_ROWS a.`schedule_id`, a.`lvl_id`, a.`schedule_name`, a.`trainer_id`, a.`start_date`, a.`end_date`, a.`ques_count`, a.`location`,a.assessmentname,a.assessmenttime, a.`assessment_type`, a.`buffer_day`, a.`filter_zone`, a.`filter_region`, a.`filter_dealer`, a.`is_deleted`, a.`status`, a.`created_on`, a.`modified_on`,l.lvl_name FROM ".TABLE_TRAINING_SCHEDULE." as a JOIN ".TABLE_TRAINING_LEVEL." as l ON l.lvl_id=a.lvl_id   WHERE  a.is_deleted='0' ".$excond." ORDER BY  a.schedule_id DESC ".$limitcond;
            $query = $conn->query($query_str);

			// Total records
			$rows_query = $conn->query("SELECT FOUND_ROWS() as total_records");
			$rows_result = $rows_query->fetchAll(PDO::FETCH_OBJ);
			$total_records = $rows_result[0]->total_records;
            $training_arr= $query->fetchAll(PDO::FETCH_OBJ);
            $check_result_arr=array();
            if(count($training_arr)>0)
            {
                foreach($training_arr as $akey=>$res_train)
                {
                    $assessment_type_str="";
                    switch($res_train->assessment_type)
                    {
                        case "0":     
                            $assessment_type_str="Pre Assessment";
                        break;
                        case "1":   
                            $assessment_type_str="Post Assessment";
                        break;
                    
                    }
                    $schedule__status_str="";
                    switch($res_train->status)
                    {
                        case "1":     
                            
                            if(strtotime($res_train->start_date)>strtotime(NOW))
                            {
                                $schedule__status_str="Not Started";
                            }else if(strtotime($res_train->end_date)>strtotime(NOW))
                            {
                                $schedule__status_str="Expired";
                            }else
                            {
                                $schedule__status_str="In-progress";
                            }
                        break;
                        case "2":   
                            $schedule__status_str="Closed";
                        break;
                        case "3":   
                            $schedule__status_str="Closed";
                        break;
                         case "4":   
                            $schedule__status_str="Closed";
                        break;
                    
                    }
                    $training_arr[$akey]->assessment_type_str=$assessment_type_str;
                    //EDITABLE CHECK 
                    $training_arr[$akey]->is_edit='1';
                    if(strtotime($res_train->start_date)<strtotime(NOW))
                    {
                        $training_arr[$akey]->is_edit='0';
                        
                    }else
                    {
                        if($res_train->status=='1')
                        {
                        
                            if($res_train->assessment_type=='0' && $res_train->status=='1')
                            {
                                $assess_str = "SELECT au.* FROM ".TABLE_ASSESSMENT_USER." as au JOIN ".TABLE_TRAINING_ASSESSMENT." as ta ON ta.assessmentid=au.assessmentid AND au.userid=ta.userid  WHERE ta.schedule_id='".$res_train->schedule_id."' LIMIT 1 "; 
                                $assess_arr=$conn->getQueryValue($assess_str);
                                $training_arr[$akey]->is_edit=(count($assess_arr)>0)?"0":"1";
                            }
                        }else
                        {
                             $training_arr[$akey]->is_edit='0';
                        }
                    }
                    
                    $training_arr[$akey]->starttime=date('H:i',strtotime($res_train->start_date));
                    $training_arr[$akey]->endtime=date('H:i',strtotime($res_train->end_date));
                            
                    $training_arr[$akey]->startdate=date('Y-m-d',strtotime($res_train->start_date));
                    $training_arr[$akey]->enddate=date('Y-m-d',strtotime($res_train->end_date));
                    
                }
            }
            if (!empty($pn) and !empty($nr)){    
                $arr['total_records'] = $total_records;
            }
			$arr['training'] = $training_arr;
			if(count($arr) != 0){
				$conn->message(true,$conn->getMessage($lang,'training','success1'), $arr); 
			}else{
				$conn->message(false,$conn->getMessage($lang,'training','error2'));
			}
		}
		else{
			$conn->message(false,$conn->getMessage($lang,'training','error1'));
		}
	}
	catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'training','error4'));
	}
}

        
//ADD TRAINING
function addtraining () {
	global $conn;
	try { 
        
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';
                        
		if(isset($_SESSION["userid"]))
        {
            $schedule_name = escape_str($_REQUEST["schedule_name"]);	 
            $lvl_id = escape_str($_REQUEST["lvl_id"]);                        
            $ques_count=escape_str($_REQUEST["ques_count"]);
            $startdate=escape_str($_REQUEST["startdate"]);
            $enddate=escape_str($_REQUEST["enddate"]);
            
            if(!( $startdate!='' && $enddate!=''  ))
            {
                $conn->message(false,"DATA MISSING");
            }
            
            $starttime=escape_str($_REQUEST["starttime"]);
            $endtime=escape_str($_REQUEST["endtime"]);
            if($starttime!='' && $endtime!='')
            {
                $startdate.=" ".$starttime;
                $enddate.=" ".$endtime;
            }else
            {
                $startdate.=" 00:00:00";
                $enddate.=" 23:59:59";
            }  
            
            $trainer_id=escape_str($_REQUEST["trainer_id"]);
            $filter_region='';
            $filter_dealer='';        
           // $filter_region=escape_str($_REQUEST["filter_region"]);
            //$filter_dealer=escape_str($_REQUEST["filter_dealer"]);
            
            $travel_desc=escape_str($_REQUEST["travel_desc"]);
            $hotel_desc=escape_str($_REQUEST["hotel_desc"]);
            
            $assessment_type=escape_str($_REQUEST["assessment_type"]); //0-Before,1-After
            $assessmentname=escape_str($_REQUEST["assessmentname"]);
            if(!in_array($assessment_type, array('0','1','2')))$conn->message(false,$conn->getMessage($lang,'training','error6'));
            
            $randomcond=escape_str($_REQUEST["randomcond"]);
            $assessmenttime=escape_str($_REQUEST["assessmenttime"]);
            //$buffer_day=escape_str($_REQUEST["buffer_day"]);
            $buffer_day='1';
            $location='';
            $participant_json=$_REQUEST["participant"];
            $participant_arr=json_decode($participant_json);
             
            $user_type=escape_str($_REQUEST["user_type"]);
            
			 
            //VALIDATION 
            if(!($schedule_name!='' && $lvl_id!='' &&  $ques_count>0 && $startdate!='' && $enddate!='' && $trainer_id!='' && $travel_desc!='' && $hotel_desc!='' && $assessmentname!='' && $randomcond!='' && $assessmenttime!='' && $buffer_day>0 && $participant_json!='' && count($participant_arr)>0))
            {
                $conn->message(false,"DATA MISSING");
            }
			
            
            //CHECK QUESTION COUNT EXIST
            $ques_count_valid='0';
            $q_count=0;
            $assess_str = "SELECT COUNT(id) as ques_count FROM ".TABLE_QUESTION." WHERE status='1' AND cateid='".$lvl_id."' LIMIT 1"; 
            $assess_arr=$conn->getQueryValue($assess_str); 
            if(count($assess_arr)>0)
            {
                $ques_count_valid=($ques_count<=$assess_arr[0]->ques_count)?'1':'0';
                $q_count=$assess_arr[0]->ques_count;
            }    
            if($ques_count_valid=='0') $conn->message(false,'Add Question\'s in Question Bank for selected Category. Total Question for selected Category : '.$q_count);
            
            //INSERT
            $insert_qry = "INSERT INTO ".TABLE_TRAINING_SCHEDULE ." ( `lvl_id`, `user_type`, `schedule_name`, `trainer_id`, `start_date`, `end_date`, `assessmentname`, `assessmenttime`, `randomcond`, `ques_count`, `location`, `assessment_type`, `buffer_day`, `filter_zone`, `filter_region`, `filter_dealer`, `travel_desc`, `hotel_desc`, `is_deleted`, `status`, `created_on`, `modified_on`) VALUES ( '".$lvl_id."','".$user_type."','".$schedule_name."', '".$trainer_id."','".$startdate."', '".$enddate."','".$assessmentname."','".$assessmenttime."','".$randomcond."','".$ques_count."','".$location."','".$assessment_type."','".$buffer_day."','".$filter_zone."','".$filter_region."','".$filter_dealer."','".$travel_desc."','".$hotel_desc."','0','1','".NOW."', '".NOW."')"; 
            $conn->query($insert_qry);
			$schedule_id = $conn->lastinsetid();
           
            if(count($participant_arr)>0)
            {
                foreach($participant_arr as $userid)
                {
                    $ins_participant_arr[] =" ( '".$schedule_id."', '".$userid."', '0', '0', '0', '".$_SESSION["userid"]."','".NOW."', '".NOW."')"; 
                }
                
                $insert_qry = "INSERT INTO ".TABLE_TRAINING_PARTICIPANT ." ( `schedule_id`, `userid`, `is_attendance`, `score`, `assessment_score`, `creatorid`, `created_on`, `modified_on`) VALUES ".@implode(',',$ins_participant_arr); 
                $conn->query($insert_qry);
            }
                        
             if($assessment_type!=2){
               //GET ASSESSMENT START DATE
            $assessment_startdate=($assessment_type=='0')?NOW:date('Y-m-d H:i:s',strtotime($enddate));
            //GET ASSESSMENT END DATE
            $assessment_enddate=date('Y-m-d H:i:s',strtotime('+'.$buffer_day.' day',strtotime($enddate))); 
             }

            if($assessment_type==2)
            {
               //GET ASSESSMENT START DATE
                $assessment_startdate=NOW;
                //GET ASSESSMENT END DATE
                $assessment_enddate=date('Y-m-d H:i:s',strtotime('+'.$buffer_day.' day',strtotime($enddate))); 


                //INSERT ASSESSMENT
                $insert_qry = "INSERT INTO ".TABLE_ASSESSMENT ." ( `atype`, `lvl_id`, `assessmentname`, `description`, `assessmentimage`, `assessmenttype`, `assettypeid`, `assessmenttimetype`, `assessmenttime`, `attemptslimit`, `publisheddate`, `enddate`, `randomcond`, `filter_role`, `filter_zone`, `filter_region`, `filter_dealer`, `assessmentnotification`, `total_question`,`schedule_id`, `status`, `creatorid`, `created_on`, `modified_on`) VALUES ('1','".$lvl_id."','".$assessmentname."','".$assessmentname."','','1','1','1','".$assessmenttime."','1','".$assessment_startdate."','".$assessment_enddate."','".$randomcond."','','','','','','".$ques_count."','".$schedule_id."','1','".$_SESSION["userid"]."','".NOW."','".NOW."')"; 
                $conn->query($insert_qry);
                $assessmentid = $conn->lastinsetid();

                if(count($participant_arr)>0)
                {
                    foreach($participant_arr as $userid)
                    {            

                        $ins_assess_arr[] =" ( '".$assessmentid."','".$schedule_id."', '".$userid."', '0')"; 
                    }
                }
                //ASSESSMENT PARTICPANT ADD
                $insert_qry = "INSERT INTO ".TABLE_TRAINING_ASSESSMENT ." ( `assessmentid`, `schedule_id`, `userid`, `is_attended`) VALUES ".@implode(',',$ins_assess_arr);
                $conn->query($insert_qry);

                //ASSESSMENT QUESTION ADD
                $rcond=($randomcond)?"ORDER BY RAND()":"ORDER BY id ASC";
                $ques_str = "SELECT *  FROM ".TABLE_QUESTION." WHERE status='1' AND cateid='".$lvl_id."' ".$rcond." LIMIT ".$ques_count; 
                $ques_arr=$conn->getQueryValue($ques_str);
                if(count($ques_arr)>0)
                {
                    foreach($ques_arr as $res_ques)
                    {
                        $ins_ques_arr[] =" ( '".$assessmentid."','".$res_ques->id."', '1', '".$_SESSION["userid"]."','".NOW."', '".NOW."')"; 
                    }

                    //ASSESSMENT QUESTION
                    $insert_qry = "INSERT INTO ".TABLE_ASSESSMENT_QUESTION ." (`assessmentid`, `qid`, `is_mandatory`, `creatorid`, `created_on`, `modified_on`) VALUES ".@implode(',',$ins_ques_arr);
                    $conn->query($insert_qry);

                } 

                //GET ASSESSMENT START DATE
                $assessment_startdate=date('Y-m-d H:i:s',strtotime($enddate));
                //GET ASSESSMENT END DATE
                $assessment_enddate=date('Y-m-d H:i:s',strtotime('+'.$buffer_day.' day',strtotime($enddate))); 
                $ins_ques_arr=array();
                $ins_assess_arr=array();

            } 

            //INSERT ASSESSMENT
            $insert_qry = "INSERT INTO ".TABLE_ASSESSMENT ." ( `atype`, `lvl_id`, `assessmentname`, `description`, `assessmentimage`, `assessmenttype`, `assettypeid`, `assessmenttimetype`, `assessmenttime`, `attemptslimit`, `publisheddate`, `enddate`, `randomcond`, `filter_role`, `filter_zone`, `filter_region`, `filter_dealer`, `assessmentnotification`, `total_question`,`schedule_id`, `status`, `creatorid`, `created_on`, `modified_on`) VALUES ('1','".$lvl_id."','".$assessmentname."','".$assessmentname."','','1','1','1','".$assessmenttime."','1','".$assessment_startdate."','".$assessment_enddate."','".$randomcond."','','','','','','".$ques_count."','".$schedule_id."','1','".$_SESSION["userid"]."','".NOW."','".NOW."')"; 
            $conn->query($insert_qry);
            $assessmentid = $conn->lastinsetid();

            if(count($participant_arr)>0)
            {
                foreach($participant_arr as $userid)
                { 
                    $ins_assess_arr[] =" ( '".$assessmentid."','".$schedule_id."', '".$userid."', '0')"; 
                }
            }
            //ASSESSMENT PARTICPANT ADD
            $insert_qry = "INSERT INTO ".TABLE_TRAINING_ASSESSMENT ." ( `assessmentid`, `schedule_id`, `userid`, `is_attended`) VALUES ".@implode(',',$ins_assess_arr);
            $conn->query($insert_qry);

            //ASSESSMENT QUESTION ADD
            $rcond=($randomcond)?"ORDER BY RAND()":"ORDER BY id ASC";
            $ques_str = "SELECT *  FROM ".TABLE_QUESTION." WHERE status='1' AND cateid='".$lvl_id."' ".$rcond." LIMIT ".$ques_count; 
            $ques_arr=$conn->getQueryValue($ques_str);
            if(count($ques_arr)>0)
            {
                foreach($ques_arr as $res_ques)
                {
                    $ins_ques_arr[] =" ( '".$assessmentid."','".$res_ques->id."', '1', '".$_SESSION["userid"]."','".NOW."', '".NOW."')"; 
                }

                //ASSESSMENT QUESTION
                $insert_qry = "INSERT INTO ".TABLE_ASSESSMENT_QUESTION ." (`assessmentid`, `qid`, `is_mandatory`, `creatorid`, `created_on`, `modified_on`) VALUES ".@implode(',',$ins_ques_arr);
                $conn->query($insert_qry);

            }
			
			sendScheduleNotification($schedule_id);
                        
            $conn->message(true,$conn->getMessage($lang,'training','success1'));
		}
		else{
			$conn->message(false,$conn->getMessage($lang,'training','error1'));
		}
	}
	catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'training','error4'));
	}
}

//EDIT TRAINING
function edittraining () {
	global $conn;
	try { 
        
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';
                   
		if(isset($_SESSION["userid"]))
        {
            $schedule_id = escape_str($_REQUEST["schedule_id"]);
            
            //CHECK TRAINING
            $check_result_str = "SELECT * FROM `".TABLE_TRAINING_SCHEDULE."` WHERE schedule_id='".$schedule_id."' AND is_deleted='0'  LIMIT 1";  
            $check_result_arr=$conn->getQueryValue($check_result_str);

            
            if(!count($check_result_arr)>0)
            {
               
                $conn->message(false,$conn->getMessage($lang,'training','error7'));
            }
            $is_edit='1';
            if(strtotime($check_result_arr[0]->start_date)<strtotime(NOW))
            {
            
                $is_edit='0';

            }else
            {
                
                if($check_result_arr[0]->status=='1')
                {

                    if($check_result_arr[0]->assessment_type=='0' && $check_result_arr[0]->status=='1')
                    {
                       $assess_str = "SELECT au.* FROM ".TABLE_ASSESSMENT_USER." as au JOIN ".TABLE_TRAINING_ASSESSMENT." as ta ON ta.assessmentid=au.assessmentid AND au.userid=ta.userid  WHERE ta.schedule_id='".$check_result_arr[0]->schedule_id."' LIMIT 1 "; 
                        $assess_arr=$conn->getQueryValue($assess_str);
                        
                        $is_edit=(count($assess_arr)>0)?"0":"1";
                    }
                }else
                {
                     $is_edit='0';
                }
            }
            if($is_edit=='1')  $conn->message(false,$conn->getMessage($lang,'training','error8'));
            
                        
            $schedule_name = escape_str($_REQUEST["schedule_name"]);	 
            $lvl_id = escape_str($_REQUEST["lvl_id"]);                        
            $ques_count=escape_str($_REQUEST["ques_count"]);
            $startdate=escape_str($_REQUEST["startdate"]);
            $enddate=escape_str($_REQUEST["enddate"]);
            
            if(!( $startdate!='' && $enddate!=''  ))
            {
                $conn->message(false,"DATA MISSING");
            }
            
            $starttime=escape_str($_REQUEST["starttime"]);
            $endtime=escape_str($_REQUEST["endtime"]);
            if($starttime!='' && $endtime!='')
            {
                $startdate.=" ".$starttime;
                $enddate.=" ".$endtime;
            }else
            {
                $startdate.=" 00:00:00";
                $enddate.=" 23:59:59";
            }  
            
            $trainer_id=escape_str($_REQUEST["trainer_id"]);
            $filter_region=escape_str($_REQUEST["filter_region"]);
            $filter_dealer=escape_str($_REQUEST["filter_dealer"]);
            
            $travel_desc=escape_str($_REQUEST["travel_desc"]);
            $hotel_desc=escape_str($_REQUEST["hotel_desc"]);
            
            $assessment_type=escape_str($_REQUEST["assessment_type"]); //0-Before,1-After
            $assessmentname=escape_str($_REQUEST["assessmentname"]);
            if(!in_array($assessment_type, array('0','1','2')))$conn->message(false,$conn->getMessage($lang,'training','error6'));
            
            $randomcond=escape_str($_REQUEST["randomcond"]);
            $assessmenttime=escape_str($_REQUEST["assessmenttime"]);
            $buffer_day=escape_str($_REQUEST["buffer_day"]);
            $location='';
             $participant_json=$_REQUEST["participant"];
            $participant_arr=json_decode($participant_json);
           
            //VALIDATION 
            if(!($schedule_id!='' && $schedule_name!='' && $lvl_id!='' &&  $ques_count>0 && $startdate!='' && $enddate!='' && $trainer_id!='' && $travel_desc!='' && $hotel_desc!='' && $assessmentname!='' && $randomcond!='' && $assessmenttime!='' && $buffer_day>0 && $participant_json!='' && count($participant_arr)>0))
            { 
                        
                $conn->message(false,"DATA MISSING");
            }
                        
            //CHECK QUESTION COUNT EXIST
            $ques_count_valid='0';
            $q_count=0;
             $assess_str = "SELECT COUNT(id) as ques_count FROM ".TABLE_QUESTION." WHERE status='1' AND cateid='".$lvl_id."' LIMIT 1"; 
            $assess_arr=$conn->getQueryValue($assess_str);
            if(count($assess_arr)>0)
            {
                $ques_count_valid=($ques_count<=$assess_arr[0]->ques_count)?'1':'0';
                $q_count=$assess_arr[0]->ques_count;
            }    
            if($ques_count_valid=='0') $conn->message(false,'Question in Question bank less than Question count. Total Question count: '.$q_count);
                        
            
            //UPDATE
             $update_qry = "UPDATE  ".TABLE_TRAINING_SCHEDULE ." SET lvl_id='".$lvl_id."' , `schedule_name`='".$schedule_name."', `trainer_id`='".$trainer_id."', `start_date`='".$startdate."', `end_date`='".$enddate."', `assessmentname`='".$assessmentname."', `assessmenttime`='".$assessmenttime."', `randomcond`='".$randomcond."', `ques_count`='".$ques_count."', `assessment_type`='".$assessment_type."', `buffer_day`='".$buffer_day."', `filter_zone`='".$filter_zone."', `filter_region`='".$filter_region."', `filter_dealer`='".$filter_dealer."', `travel_desc`='".$travel_desc."', `hotel_desc`='".$hotel_desc."', `modified_on`='".NOW."' WHERE schedule_id='".$schedule_id."'"; 
            $conn->query($update_qry); 
            
            //DELETE FROM
            $delete_qry = "DELETE FROM ".TABLE_TRAINING_PARTICIPANT." WHERE schedule_id='".$schedule_id."'";
            $conn->query($delete_qry);
            
            $select_str=" SELECT * FROM ".TABLE_TRAINING_ASSESSMENT." WHERE schedule_id='".$schedule_id."'";
            $select_arr=$conn->getQueryValue($select_str);
            if(count($select_arr)>0)                
            {
                //DELETE ASSESSMENT
                $delete_qry = "DELETE FROM ".TABLE_ASSESSMENT." WHERE assessmentid='".$select_arr[0]->assessmentid."'";
                $conn->query($delete_qry);
                //DELETE ASSESSMENT QUESTION.
                $delete_qry = "DELETE FROM ".TABLE_ASSESSMENT_QUESTION." WHERE assessmentid='".$select_arr[0]->assessmentid."'";
                $conn->query($delete_qry);
                 //DELETE TRAINING ASSESSMENT 
                $delete_qry = "DELETE FROM ".TABLE_TRAINING_ASSESSMENT." WHERE schedule_id='".$schedule_id."'";
                $conn->query($delete_qry);
                        
            }
            
            
            if(count($participant_arr)>0)
            { 
                foreach($participant_arr as $userid)
                {
                    $ins_participant_arr[] =" ( '".$schedule_id."', '".$userid."', '0', '0', '0', '".$_SESSION["userid"]."','".NOW."', '".NOW."')"; 
                }
                
                $insert_qry = "INSERT INTO ".TABLE_TRAINING_PARTICIPANT ." ( `schedule_id`, `userid`, `is_attendance`, `score`, `assessment_score`, `creatorid`, `created_on`, `modified_on`) VALUES ".@implode(',',$ins_participant_arr); 
                $conn->query($insert_qry);
            }
            
            if($assessment_type!=2){
              //START DATE            
            $assessment_startdate=($assessment_type=='0')?NOW:date('Y-m-d H:i:s',strtotime($enddate));
            //GET ASSESSMENT END DATE
            $assessment_enddate=date('Y-m-d H:i:s',strtotime('+'.$buffer_day.' day',strtotime($enddate)));   
            }
            
            if($assessment_type==2){

                    //GET ASSESSMENT START DATE
            $assessment_startdate=NOW;
            //GET ASSESSMENT END DATE
            $assessment_enddate=date('Y-m-d H:i:s',strtotime('+'.$buffer_day.' day',strtotime($enddate))); 

                            //INSERT ASSESSMENT
           $insert_qry = "INSERT INTO ".TABLE_ASSESSMENT ." ( `atype`, `lvl_id`, `assessmentname`, `description`, `assessmentimage`, `assessmenttype`, `assettypeid`, `assessmenttimetype`, `assessmenttime`, `attemptslimit`, `publisheddate`, `enddate`, `randomcond`, `filter_role`, `filter_zone`, `filter_region`, `filter_dealer`, `assessmentnotification`, `total_question`,schedule_id, `status`, `creatorid`, `created_on`, `modified_on`) VALUES ('1','".$lvl_id."','".$assessmentname."','".$assessmentname."','','1','1','1','".$assessmenttime."','1','".$assessment_startdate."','".$assessment_enddate."','".$randomcond."','','','','','','".$ques_count."','".$schedule_id."','1','".$_SESSION["userid"]."','".NOW."','".NOW."')";  
            $conn->query($insert_qry);
            $assessmentid = $conn->lastinsetid();
                  
            if(count($participant_arr)>0)
            {
                foreach($participant_arr as $userid)
                {                        
                    $ins_assess_arr[] =" ( '".$assessmentid."','".$schedule_id."', '".$userid."', '0' )"; 
                }
                
                 //ASSESSMENT PARTICPANT ADD
               $insert_qry = "INSERT INTO ".TABLE_TRAINING_ASSESSMENT ." ( `assessmentid`, `schedule_id`, `userid`, `is_attended`) VALUES ".@implode(',',$ins_assess_arr);
                
                $conn->query($insert_qry);
            }
           

            //ASSESSMENT QUESTION ADD
            $rcond=($randomcond)?"ORDER BY RAND()":"ORDER BY id ASC";
           $ques_str = "SELECT *  FROM ".TABLE_QUESTION." WHERE status='1' AND cateid='".$lvl_id."' ".$rcond." LIMIT ".$ques_count; 
            $ques_arr=$conn->getQueryValue($ques_str);
            
            if(count($ques_arr)>0)
            {
                foreach($ques_arr as $res_ques)
                {
                    $ins_ques_arr[] =" ( '".$assessmentid."','".$res_ques->id."', '1', '".$_SESSION["userid"]."','".NOW."', '".NOW."')"; 
                }

                //ASSESSMENT QUESTION
                $insert_qry = "INSERT INTO ".TABLE_ASSESSMENT_QUESTION ." (`assessmentid`, `qid`, `is_mandatory`, `creatorid`, `created_on`, `modified_on`) VALUES ".@implode(',',$ins_ques_arr);
                $conn->query($insert_qry);

            }


            //GET ASSESSMENT START DATE
            $assessment_startdate=date('Y-m-d H:i:s',strtotime($enddate));
            //GET ASSESSMENT END DATE
            $assessment_enddate=date('Y-m-d H:i:s',strtotime('+'.$buffer_day.' day',strtotime($enddate))); 
            $ins_ques_arr=array();
            $ins_assess_arr=array();

            }

           

            //INSERT ASSESSMENT
           $insert_qry = "INSERT INTO ".TABLE_ASSESSMENT ." ( `atype`, `lvl_id`, `assessmentname`, `description`, `assessmentimage`, `assessmenttype`, `assettypeid`, `assessmenttimetype`, `assessmenttime`, `attemptslimit`, `publisheddate`, `enddate`, `randomcond`, `filter_role`, `filter_zone`, `filter_region`, `filter_dealer`, `assessmentnotification`, `total_question`,schedule_id, `status`, `creatorid`, `created_on`, `modified_on`) VALUES ('1','".$lvl_id."','".$assessmentname."','".$assessmentname."','','1','1','1','".$assessmenttime."','1','".$assessment_startdate."','".$assessment_enddate."','".$randomcond."','','','','','','".$ques_count."','".$schedule_id."','1','".$_SESSION["userid"]."','".NOW."','".NOW."')";  
            $conn->query($insert_qry);
            $assessmentid = $conn->lastinsetid();
                  
            if(count($participant_arr)>0)
            {
                foreach($participant_arr as $userid)
                {                        
                    $ins_assess_arr[] =" ( '".$assessmentid."','".$schedule_id."', '".$userid."', '0' )"; 
                }
                
                 //ASSESSMENT PARTICPANT ADD
               $insert_qry = "INSERT INTO ".TABLE_TRAINING_ASSESSMENT ." ( `assessmentid`, `schedule_id`, `userid`, `is_attended`) VALUES ".@implode(',',$ins_assess_arr);
                
                $conn->query($insert_qry);
            }
           

            //ASSESSMENT QUESTION ADD
            $rcond=($randomcond)?"ORDER BY RAND()":"ORDER BY id ASC";
           $ques_str = "SELECT *  FROM ".TABLE_QUESTION." WHERE status='1' AND cateid='".$lvl_id."' ".$rcond." LIMIT ".$ques_count; 
            $ques_arr=$conn->getQueryValue($ques_str);
            
            if(count($ques_arr)>0)
            {
                foreach($ques_arr as $res_ques)
                {
                    $ins_ques_arr[] =" ( '".$assessmentid."','".$res_ques->id."', '1', '".$_SESSION["userid"]."','".NOW."', '".NOW."')"; 
                }

                //ASSESSMENT QUESTION
                $insert_qry = "INSERT INTO ".TABLE_ASSESSMENT_QUESTION ." (`assessmentid`, `qid`, `is_mandatory`, `creatorid`, `created_on`, `modified_on`) VALUES ".@implode(',',$ins_ques_arr);
                $conn->query($insert_qry);

            }
                        
            $conn->message(true,$conn->getMessage($lang,'training','success1'));
		}
		else{
			$conn->message(false,$conn->getMessage($lang,'training','error1'));
		}
	}
	catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'training','error4'));
	}
}


// TRAINER LIST
function trainerlist() {
	global $conn;
	try {
        $lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';
                        
		if(isset($_SESSION["userid"]))
        { 
                        
            $query_str = "SELECT u.userid, CONCAT(u.fname,u.lname) as name,h.zone,u.emp_code FROM ".TABLE_USER." as u JOIN ".DMS_HOUSER." as h ON u.emp_code=h.emp_code  WHERE u.role=5 AND u.status='1' AND u.is_deleted='0' ORDER BY h.zone,u.emp_code"; 
            $user_arr=$conn->getQueryValue($query_str);
                        
                        
            $conn->message(true,$conn->getMessage($lang,'training','success1'), $user_arr); 
            
		}else {
			$conn->message(false,$conn->getMessage($lang,'training','error1'));
		}
	}
	catch(PDOException $e) {
		$conn->message(false,$conn->getMessage($lang,'training','error4'));
	}	
}
                        

// TRAINING DETAILS
function trainingdetails() {
	global $conn;
	try {
        $lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';
                        
		if(isset($_SESSION["userid"]))
        {
            $schedule_id=escape_str($_REQUEST["schedule_id"]);
            
            //CHECK TRAINING
            $check_result_str = "SELECT * FROM `".TABLE_TRAINING_SCHEDULE."` WHERE schedule_id='".$schedule_id."' AND is_deleted='0'  LIMIT 1";  
            $check_result_arr=$conn->getQueryValue($check_result_str);
            if(!count($check_result_arr)>0)
            {
                $conn->message(false,$conn->getMessage($lang,'training','error7'));
            }
            
            //GET ALL PARTICIPANT
            $check_result_arr[0]->participants=array();
            $participant_str = "SELECT u.userid,u.`smpin`, CONCAT(u.fname,u.lname) as name FROM `".TABLE_TRAINING_PARTICIPANT."` as p JOIN ".TABLE_USER." as u ON p.userid=u.userid  WHERE u.is_deleted='0' AND u.status='1' AND p.schedule_id='".$schedule_id."' GROUP BY p.userid";  
            $participant_arr=$conn->getQueryValue($participant_str);
            if(count($participant_arr)>0)
            {
                $user_arr=array();
                $parr=array();
                
                foreach($participant_arr as $res_p)
                {
                    $user_arr[]=$res_p->userid;  
                    $parr[]=$res_p;
                }
                $check_result_arr[0]->participant_id=$user_arr;
                $check_result_arr[0]->participants=$parr;
                
                $check_result_arr[0]->startdate=date('Y-m-d',strtotime($check_result_arr[0]->start_date));
                $check_result_arr[0]->enddate=date('Y-m-d',strtotime($check_result_arr[0]->end_date));
                
                $check_result_arr[0]->starttime=date('H:i',strtotime($check_result_arr[0]->start_date));
                $check_result_arr[0]->endtime=date('H:i',strtotime($check_result_arr[0]->end_date));
                        
            }                        
            $conn->message(true,$conn->getMessage($lang,'training','success1'), $check_result_arr); 
		}else {
			$conn->message(false,$conn->getMessage($lang,'training','error1'));
		}
	}
	catch(PDOException $e) {
		$conn->message(false,$conn->getMessage($lang,'training','error4'));
	}	
}


// DELETE TRAINING 
function deletetraining() {
	global $conn;
	try {
        $lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';
                        
		if(isset($_SESSION["userid"]))
        {
            $schedule_id=escape_str($_REQUEST["schedule_id"]);
            
            //CHECK TRAINING
            $check_result_str = "SELECT * FROM `".TABLE_TRAINING_SCHEDULE."` WHERE schedule_id='".$schedule_id."' AND is_deleted='0'  LIMIT 1";  
            $check_result_arr=$conn->getQueryValue($check_result_str);
            if(!count($check_result_arr)>0)
            {
                $conn->message(false,$conn->getMessage($lang,'training','error7'));
            }
            
            $is_edit='1';
            if(strtotime($check_result_arr[0]->start_date)<strtotime(NOW))
            {
                $is_edit='0';

            }else
            {
                if($check_result_arr[0]->status=='1')
                {

                    if($check_result_arr[0]->assessment_type=='0' && $check_result_arr[0]->status=='1')
                    {
                        $assess_str = "SELECT au.* FROM ".TABLE_ASSESSMENT_USER." as au JOIN ".TABLE_TRAINING_ASSESSMENT." as ta ON ta.assessmentid=au.assessmentid AND au.userid=ta.userid  WHERE ta.schedule_id='".$check_result_arr[0]->schedule_id."' LIMIT 1 "; 
                        $assess_arr=$conn->getQueryValue($assess_str);
                        $is_edit=(count($assess_arr)>0)?"0":"1";
                    }
                }else
                {
                     $is_edit='0';
                }
            }
            if($is_edit=='1')  $conn->message(false,$conn->getMessage($lang,'training','error8'));
            
            
            $conn->executeQuery("DELETE FROM ".TABLE_TRAINING_SCHEDULE." WHERE schedule_id='".$schedule_id."'"); 
            $conn->executeQuery("DELETE FROM ".TABLE_TRAINING_PARTICIPANT." WHERE schedule_id='".$schedule_id."'"); 
            $assess_str = "SELECT * FROM `".TABLE_TRAINING_ASSESSMENT."` WHERE schedule_id='".$schedule_id."'   LIMIT 1";  
            $assess_arr=$conn->getQueryValue($assess_str);
            if(count($assess_arr)>0)
            {
				$assessmentid=$assess_arr[0]->assessmentid;
                //$conn->executeQuery("DELETE FROM ".TABLE_TRAINING_PARTICIPANT." WHERE schedule_id='".$schedule_id."'"); 
                $conn->executeQuery("DELETE FROM ".TABLE_ASSESSMENT." WHERE assessmentid='".$assessmentid."'"); 
                $conn->executeQuery("DELETE FROM ".TABLE_ASSESSMENT_QUESTION." WHERE assessmentid='".$assessmentid."'"); 
            }
                        
            $conn->message(true,$conn->getMessage($lang,'training','success1'), $check_result_arr); 
		}else {
			$conn->message(false,$conn->getMessage($lang,'training','error1'));
		}
	}
	catch(PDOException $e) {
		$conn->message(false,$conn->getMessage($lang,'training','error4'));
	}	
}

        

function sendScheduleNotification ($schedule_id)
{
    global $conn;        
	 
	
    $query_str = "SELECT * FROM `".TABLE_TRAINING_PARTICIPANT."` WHERE schedule_id='".$schedule_id."' "; 
    $part_arr=$conn->getQueryValue($query_str);

    if(count($part_arr)>0)
    {
		
        $obj=new stdClass();        

        $notificationdata= json_encode($obj);
        $notificationtype='5';
        
        $query_str = "SELECT * FROM `".TABLE_TRAINING_SCHEDULE."` WHERE schedule_id='".$schedule_id."'  limit 1 "; 
		$schedule_arr=$conn->getQueryValue($query_str); 
		 
		 
        $notificationtext="New Training Scheduled  - `".$schedule_arr[0]->schedule_name."` from  date : ".date('d M Y',strtotime($schedule_arr[0]->start_date))." - ".date('d M Y',strtotime($schedule_arr[0]->end_date));            
         
        $user_arr=array();    
        if(count($check_user_arr)>0)
        {
			foreach ($part_arr as $res_user)
            {
                $user_arr[]=$res_user->userid;
            }
			 
            //INSERT NOTITFICATION
            $conn->executeQuery("INSERT INTO `".TABLE_NOTIFICATION."` (  `notificationtitle`, `notificationtext`, `notificationdata`, `notificationtype`,  `status`, `publisheddate`, `created_on`, `modified_on`) VALUES ('".escape_str($schedule_arr[0]->schedule_name)."','".escape_str($notificationtext)."','".$notificationdata."','".$notificationtype."' ,'1','".NOW."','".NOW."','".NOW."')");

            $notificationid=$conn->lastinsetid();
            $data=array('notification_id'=>$notificationid,'title'=>$assessment_arr[0]->schedule_name,'message'=>$notificationtext,'notificationtype'=>$notificationtype,'notificationdata'=>$notificationdata,'created_date'=>NOW);

            if(count($user_arr)>0)
            {
                $ins_arr=array();
                foreach($user_arr as $res)
                {
                    $ins_arr[]=" ('".$notificationid."', '".$res."', '0', '".NOW."')";
                }
                
                $insert_chunk=array_chunk($ins_arr, 1000);
                foreach($insert_chunk as $chunk_ins)
                {
                    $conn->executeQuery("INSERT INTO ".TABLE_NOTIFICATION_USER."(`notificationid`, `userid`, `status`,  `created_on`) VALUES ".@implode(',',$chunk_ins)."  ON DUPLICATE KEY UPDATE `status` =0");
                }
                
                $usertokenarr['ios']=array();
                $usertokenarr['android']=array();

                $user_device_str = "SELECT ud.devicetype,ud.token FROM `".TABLE_USER_DEVICES."` as ud JOIN (SELECT userid FROM ".TABLE_NOTIFICATION_USER." WHERE notificationid='".$notificationid."')  as u ON ud.userid=u.userid  WHERE ud.is_deleted='0' AND (devicetype='ios' OR devicetype='android')  AND token!=''";
                $user_device_arr = $conn->getQueryValue($user_device_str);
                if(count($user_device_arr)>0)                            
                {

                    foreach($user_device_arr as $res_user)
					{
						if(strtolower($res_user->devicetype)=='ios')
						{
							$usertokenarr['ios'][]=$res_user->token;
						}else if(strtolower($res_user->devicetype)=='android')
						{
							$usertokenarr['android'][]=$res_user->token;
						}
					}

                    // $conn->sendUserNotification($data,$usertokenarr);
                }
                
            }
        }
    }
    
}



// BATCH LIST
function batchlist() {

	global $conn;
	try {
        $lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';

        $userid = escape_str($_REQUEST["userid"]);
       	$pn = escape_str($_REQUEST["pn"]);
		$nr = escape_str($_REQUEST["nr"]);
		
         $limitcond='';               
		if(isset($_SESSION["userid"]))
        { 
        	//PAGINATION
            if (!empty($pn) and !empty($nr)) {
				// Navigation
    		$limitcond = " limit ".(($pn - 1) *  $nr).", " . $nr;
			} 
            $check_str = "SELECT * FROM ".TABLE_TRAINERBATCH."  WHERE  trainerid='".$userid."'".$limitcond;  
            $check_result_arr=$conn->getQueryValue($check_str);
            if(count($check_result_arr)>0)
            {
            	$conn->message(true,$conn->getMessage($lang,'training','success1'), $check_result_arr); 
                
            }else{
            	$conn->message(false,$conn->getMessage($lang,'training','error7'));
            }
                        
            
            
		}else {
			$conn->message(false,$conn->getMessage($lang,'training','error1'));
		}
	}
	catch(PDOException $e) {
		$conn->message(false,$conn->getMessage($lang,'training','error4'));
	}	
}

// BATCH USER LIST
function batchuserlist() {

    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';

        $batchid = escape_str($_REQUEST["batchid"]);
        $pn = escape_str($_REQUEST["pn"]);
        $nr = escape_str($_REQUEST["nr"]);
        
         $limitcond='';               
        if(isset($_SESSION["userid"]))
        { 
            //PAGINATION
            if (!empty($pn) and !empty($nr)) {
                // Navigation
            $limitcond = " limit ".(($pn - 1) *  $nr).", " . $nr;
            } 
           // $check_str = "SELECT * FROM ".TABLE_TRAINERBATCHUSER."  WHERE  batchid='".$batchid."'".$limitcond;
  $check_str = "SELECT b.smpin,u.fname,u.lname,u.dealer_code,d.dealer_name FROM ".TABLE_TRAINERBATCHUSER." b  JOIN ".TABLE_USER." u ON u.smpin=b.smpin JOIN ".DMS_DEALER." d ON d.dealer_code=u.dealer_code WHERE u.role>7 AND u.role<14 AND b.batchid='".$batchid."' GROUP BY b.smpin ORDER BY u.smpin ASC,u.userid ASC $limitcond";  

            $batch_result_arr=$conn->getQueryValue($check_str);
            if(count($batch_result_arr)>0)
            {
                $conn->message(true,$conn->getMessage($lang,'training','success1'), $batch_result_arr); 
                
            }else{
                $conn->message(false,$conn->getMessage($lang,'training','error7'));
            }
                        
            
            
        }else {
            $conn->message(false,$conn->getMessage($lang,'training','error1'));
        }
    }
    catch(PDOException $e) {
        $conn->message(false,$conn->getMessage($lang,'training','error4'));
    }   
}

?>