opensourcecode/Hyper-M_Admin_Portal/services/videotraining.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 "traininglist":
        case "addtraining":
        case "role": 
        case "getroleuser":   
        case "getrmlist":     
        case "getamlist": 
        case "getdealerlist":
        case "getdselist":    
        case "typelist":    
            
        case "gettraineruser":         
        case "getzmuser":     
        case "getrmuser":          
        case "getamuser":
        case "getdealeruser":    
            
        case "deletetraining":  
        case "trainingparticipantlist":    
        case "addparticipant":       
        case "deleteparticipant":     
        case "ilearnuserlist":         
			$fun();     
		break;
        
	}
}
        
//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"]);
        
        //$_SESSION["userid"]=1;
		if(isset($_SESSION["userid"]))
        {
            
            $excond=''; 
            if ($key!='') {
				$excond .= " AND  title like '%".$key."%'  ";
			}
            //PAGINATION
            if (!empty($pn) and !empty($nr)) {
				// Navigation
    			$limitcond = " limit ".(($pn - 1) *  $nr).", " . $nr;
			}
            
			$query_str = "SELECT SQL_CALC_FOUND_ROWS  * FROM ".TABLE_VIDEOTRAININGSCHEDULE." WHERE is_deleted='0' AND status=1 AND is_admin=1 ORDER BY fromdate DESC,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); 
            if(count($training_arr)>0)
            {
                foreach($training_arr as $akey=>$res_train)
                { 
                    $user_str = "SELECT fname,lname,emp_code FROM ".TABLE_USER." WHERE userid='".$res_train->organiser."'   LIMIT 1 "; 
                    $user_arr=$conn->getQueryValue($user_str);
					$training_arr[$akey]->organisercode=$user_arr[0]->emp_code;
                    $training_arr[$akey]->organisername=trim($user_arr[0]->fname." ".$user_arr[0]->lname);
                } 
            }else
            {
                $training_arr=array();
            }
			
            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,'videotraining','success1'), $arr); 
			}else{
				$conn->message(false,$conn->getMessage($lang,'videotraining','error2'));
			}
		}
		else{
			$conn->message(false,$conn->getMessage($lang,'videotraining','error1'));
		}
	}
	catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'videotraining','error4'));
	}
}
      
//ROLE
function role () {
    global $conn;
	try { 
        
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';   
        //$_SESSION["userid"]=1;
		if(isset($_SESSION["userid"]))
        {  
            $cond.=' AND id=5';
            //$cond.=' AND ( id=14 OR (id>3 AND id<8)) ';    
            $query_str = "SELECT `id`, `rolename`  FROM ".TABLE_ROLE." WHERE status=1 ".$cond; 
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);
                        
        
            $conn->message(true,$conn->getMessage($lang,'videotraining','success1'), $arr);        
		}
		else{
			$conn->message(false,$conn->getMessage($lang,'videotraining','error1'));
		}
	}
	catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'videotraining','error4'));
	}
}
//GET ROLE USER
function getroleuser () {
    global $conn;
	try { 
        
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';   
        //$_SESSION["userid"]=1;
		if(isset($_SESSION["userid"]))
        {  
            $role=escape_str($_REQUEST['role']);
            if(!in_array($role,array("4","5","6","7","14"))) $conn->message(false,$conn->getMessage($lang,'videotraining','error3'));
        
            $query_str = "SELECT userid,emp_code,fname,lname,dealer_code,CASE WHEN role=14 THEN dealer_code WHEN role=5 THEN emp_code ELSE emailid END as value FROM ".TABLE_USER." WHERE is_deleted=0 AND status='1' AND role='".$role."' ORDER BY fname ASC,userid ASC ";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);
                        
        
            $conn->message(true,$conn->getMessage($lang,'videotraining','success1'), $arr);        
		}
		else{
			$conn->message(false,$conn->getMessage($lang,'videotraining','error1'));
		}
	}
	catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'videotraining','error4'));
	}
}



//GET RM LIST
function getrmlist () {
    global $conn;
	try { 
        
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';   
        
		if(isset($_SESSION["userid"]))
        {   
            $role=escape_str($_REQUEST['role']); 
            $value=escape_str($_REQUEST['value']);           
            if(!is_array($value)&& count($value)>0) $conn->message(false,$conn->getMessage($lang,'videotraining','error5'));
            
            if($role=='5')
            {
                $empcode_quotes = array_map("quotes_array", $value);
                $cond=" AND emp_code IN (".@implode(",",$empcode_quotes).")";
                
                $get_trainer_str = "SELECT  zone FROM ".DMS_HOUSER."  WHERE  status='1' ".$cond." LIMIT 1";              
                $trainer_arr=$conn->getQueryValue($get_trainer_str);
                
                $dcond=(count($trainer_arr)>0)?" AND zone='".$trainer_arr[0]->zone."'":" AND zone=''";
            }else
            {
                $email_quotes = array_map("quotes_array", $value);
                $dcond=" AND zm_email IN (".@implode(",",$email_quotes).")"; 
            }
        
            $query_str = "SELECT rm_name as name,rm_email as value FROM ".DMS_DEALER." WHERE status='1' AND is_deleted='0' ".$dcond." GROUP BY rm_email ";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);
                        
        
            $conn->message(true,$conn->getMessage($lang,'videotraining','success1'), $arr);        
		}
		else{
			$conn->message(false,$conn->getMessage($lang,'videotraining','error1'));
		}
	}
	catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'videotraining','error4'));
	}
}

//GET AM LIST
function getamlist () {
    global $conn;
	try { 
        
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';   
        
		if(isset($_SESSION["userid"]))
        {   
        
            $value=escape_str($_REQUEST['value']);           
            if(!is_array($value)&& count($value)>0) $conn->message(false,$conn->getMessage($lang,'videotraining','error5'));
            
        
            $email_quotes = array_map("quotes_array", $value);
            $dcond=" AND rm_email IN (".@implode(",",$email_quotes).")"; 
        
        
            $query_str = "SELECT am_name as name,am_email as value FROM ".DMS_DEALER." WHERE status='1' AND is_deleted='0' ".$dcond." GROUP BY am_email ";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);
                        
        
            $conn->message(true,$conn->getMessage($lang,'videotraining','success1'), $arr);        
		}
		else{
			$conn->message(false,$conn->getMessage($lang,'videotraining','error1'));
		}
	}
	catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'videotraining','error4'));
	}
}

//GET DEALER LIST
function getdealerlist () {
    global $conn;
	try { 
        
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';   
        
		if(isset($_SESSION["userid"]))
        {   
        
            $value=escape_str($_REQUEST['value']);           
            if(!is_array($value)&& count($value)>0) $conn->message(false,$conn->getMessage($lang,'videotraining','error5'));
            
        
            $email_quotes = array_map("quotes_array", $value);
            $dcond=" AND am_email IN (".@implode(",",$email_quotes).")"; 
        
        
            $query_str = "SELECT dealer_name as name,dealer_code as value FROM ".DMS_DEALER." WHERE status='1' AND is_deleted='0' ".$dcond."";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);
                        
        
            $conn->message(true,$conn->getMessage($lang,'videotraining','success1'), $arr);        
		}
		else{
			$conn->message(false,$conn->getMessage($lang,'videotraining','error1'));
		}
	}
	catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'videotraining','error4'));
	}
}


//GET DSE LIST
function getdselist () {
    global $conn;
	try { 
        
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';   
        
		if(isset($_SESSION["userid"]))
        {   
        
            $value=escape_str($_REQUEST['value']);           
            if(!is_array($value)&& count($value)>0) $conn->message(false,$conn->getMessage($lang,'videotraining','error5'));
            $typeid=escape_str($_REQUEST["typeid"]);
        
            $quotes = array_map("quotes_array", $value);
            $usercond=" AND d.dealer_code IN (".@implode(",",$quotes).")"; 
            switch ($typeid)
            {
                case "base":
                   $usercond.=" and l.lvl_id=1 AND l.current_status=1";
                   $jointable="JOIN ".TABLE_USER_LEARNING." l ON u.smpin=l.smpin";
                    break;
                case "bronze":
                    $usercond.=" and l.lvl_id=2 AND l.current_status=1";
                   $jointable="JOIN ".TABLE_USER_LEARNING." l ON u.smpin=l.smpin";
                    break;
                case "silverlevel1":
                   $usercond.=" and l.lvl_id=3 AND l.current_status=1 and l.status in (0,5,6)";
                   $jointable="JOIN ".TABLE_USER_LEARNING." l ON u.smpin=l.smpin";
                    break;
                 case "silverlevel2":
                   $usercond.=" and l.lvl_id=3 AND l.current_status=1 and l.status in (4,2,3,7)";
                   $jointable="JOIN ".TABLE_USER_LEARNING." l ON u.smpin=l.smpin";
                    break;
                case "goldlevel1":
                   $usercond.=" and l.lvl_id=4 AND l.current_status=1 and l.status in (0,5,6)";
                   $jointable="JOIN ".TABLE_USER_LEARNING." l ON u.smpin=l.smpin";
                    break;
                case "goldlevel2":
                    $usercond.=" and l.lvl_id=4 and l.status in (4,2,3,7)";
                    $jointable="JOIN ".TABLE_USER_LEARNING." l ON u.smpin=l.smpin";
                    break;  
                default:
                    $jointable=''; 
                break;
            }  
            
            $query_str = "SELECT u.userid,u.fname,u.lname,u.smpin,u.dealer_code,d.dealer_name,u.segment,CASE WHEN u.segment=0 THEN 'Student Learner' WHEN u.segment=1 THEN 'Parents/Guardian' WHEN u.segment=2 THEN 'Instructor' END as segment_str,u.profile_photo FROM ".TABLE_USER." u LEFT JOIN ".DMS_EMPLOYEE." e ON u.smpin=e.smpin LEFT JOIN ".DMS_DEALER." d ON u.dealer_code=d.dealer_code ".$jointable." WHERE e.is_current='1' AND u.status='1' AND u.is_deleted='0' AND u.role>7 AND u.role<14 ".$usercond." ORDER BY  u.dealer_code ASC,u.fname ASC,u.userid ASC";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);
                        
        
            $conn->message(true,$conn->getMessage($lang,'videotraining','success1'), $arr);        
		}
		else{
			$conn->message(false,$conn->getMessage($lang,'videotraining','error1'));
		}
	}
	catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'videotraining','error4'));
	}
}

//ADD TRAINING
function addtraining () {
	global $conn;
	try { 
        
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';
                
        $userid=escape_str($_REQUEST['userid']);
        $role=escape_str($_REQUEST['role']);
        
        $title=escape_str($_REQUEST['title']);
        $description=escape_str($_REQUEST['description']);
        $fromdate=escape_str($_REQUEST['fromdate']);
        $todate=escape_str($_REQUEST['todate']);
        
        if(strtotime($fromdate)<NOW) $conn->message(false,$conn->getMessage($lang,'videotraining','error7'));
        if(strtotime($todate)<NOW) $conn->message(false,$conn->getMessage($lang,'videotraining','error7'));
        if(strtotime($fromdate)>strtotime($todate)) $conn->message(false,"to datetime should be greater than from datetime");
        $dse=$_REQUEST['dse'];
        $dse=json_decode($dse); //sending as string
        $channel=escape_str($_REQUEST['channel']);
        $typeid=escape_str($_REQUEST['typeid']);
        
        //UPDATED FOR APP TEAM
        $channeltemp = explode("_",$channel);
        $gettrname="select emp_code from tbl__user where userid='".$userid."'";
        $trname_arr=$conn->getQueryValue($gettrname);                
        $channel = $trname_arr[0]->emp_code."_".$channeltemp[1]."_".$channeltemp[2];
        
        if(!($userid!=''&& $role!='' && $title!='' && $description!='' &&  $fromdate!='' && $todate!='')) $conn->message(false,$conn->getMessage($lang,'videotraining','error8'));
        if(!(is_array($dse) && count($dse)>0)) $conn->message(false,$conn->getMessage($lang,'videotraining','error6'));
        
        //INSERT 				
        $insert_str = "INSERT INTO ".TABLE_VIDEOTRAININGSCHEDULE."(typeid, `title`, `description`, `fromdate`, `todate`,channel, `status`, `organiser_role`, `organiser`, is_admin,`created_on`, `modified_on`) VALUES ('".$typeid."','".$title."','".$description."','".$fromdate."','".$todate."','".$channel."','1','".$role."','".$userid."',1,'".NOW."','".NOW."')";
        $conn->executeQuery($insert_str);               
        $trainingid=$conn->lastinsetid();
        
        foreach($dse as $userid)
        {
            $ins_arr[]="('".$trainingid."', '".$userid."', 1,'".NOW."','".NOW."')";
        }
        $insert_str = "INSERT INTO ".TABLE_VIDEOSCHEDULEUSER." ( `sid`, `userid`, `status`, `created_on`, `modified_on`) VALUES ".@implode(",", $ins_arr);
        $conn->executeQuery($insert_str);                
        notificationtriggervideotraining($trainingid); //TRIGGER NOTIFICATION
        $conn->message(true,$conn->getMessage($lang,'videotraining','success1'), $arr);     
        
    }
	catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'training','error4'));
	}
} 

function notificationtriggervideotraining($trainingid,$extobj=NULL)
{
    global $conn;
    $schedule_arr=$conn->selectQuery(TABLE_VIDEOTRAININGSCHEDULE ,"*","id='".$trainingid."'","1");

    if($extobj!=NULL)
    {
        if(isset($extobj->userid)&& is_array($extobj->userid))
        {                
            $excond=" AND u.userid IN(".implode(',',$extobj->userid).")";
        }
    }
    $ntype=($extobj!=NULL && isset($extobj->type))?$extobj->type:"";

    if($ntype=='removed')
    {
        $get_list_arr=$conn->selectQuery(TABLE_USER." u " ,"u.userid "," u.is_deleted=0 ".$excond."  ORDER BY u.userid ","");
    }else
    {
        $get_list_arr=$conn->selectQuery("(SELECT * FROM ".TABLE_VIDEOSCHEDULEUSER." WHERE sid='".$trainingid."' )sv JOIN ".TABLE_USER." u ON sv.userid=u.userid   " ,"u.userid "," u.is_deleted=0 ".$excond."  ORDER BY u.userid ","");
    }
    if($schedule_arr['nr']>0 && $get_list_arr['nr']>0)
    {

        if($schedule_arr['nr']>0)
        {                
            $obj=new stdClass();  
            $obj->id=$trainingid; 
            $notificationdata= json_encode($obj);
            $notificationtype='9';
            $ins_arr=array();
            switch($ntype)
            {
                case 'removed':
                    $notificationtitle="i-Learn - ".$schedule_arr['result'][0]->title ." cancelled for you";
                    $notificationtext="i-Learn - ".$schedule_arr['result'][0]->title." schdeuled on ".date('d-M-Y h:i A', strtotime($schedule_arr['result'][0]->fromdate))." is cancelled for you.";
                break;   
                case 'delete':
                    $notificationtitle="i-Learn - ".$schedule_arr['result'][0]->title ." cancelled";
                    $notificationtext="i-Learn - ".$schedule_arr['result'][0]->title." schdeuled on ".date('d-M-Y h:i A', strtotime($schedule_arr['result'][0]->fromdate))." is cancelled.";
                    $ins_arr[]=" ('".$notificationid."', '".$schedule_arr['result'][0]->organiser."', '0', '".NOW."')"; //ADD ORGANISER ALSO
                break;    
                default:
                    $notificationtitle="New i-Learn - ".$schedule_arr['result'][0]->title;
                    $notificationtext="New i-Learn - ".$schedule_arr['result'][0]->title." schdeuled on ".date('d-M-Y h:i A', strtotime($schedule_arr['result'][0]->fromdate)).".";
                    $ins_arr[]=" ('".$notificationid."', '".$schedule_arr['result'][0]->organiser."', '0', '".NOW."')"; //ADD ORGANISER ALSO
                break;
            }
            $conn->executeQuery("INSERT INTO `".TABLE_NOTIFICATION."` (  `notificationtitle`, `notificationtext`, `notificationdata`, `notificationtype`,  `status`, `publisheddate`, `created_on`, `modified_on`) VALUES ('".escape_str($notificationtitle)."','".escape_str($notificationtext)."','".$notificationdata."','".$notificationtype."' ,'1','".NOW."','".NOW."','".NOW."')");
            $notificationid=$conn->lastinsetid();
            $data=array('notification_id'=>$notificationid,'title'=>$notificationtitle,'message'=> $notificationtext,'notificationtype'=>$notificationtype,'notificationdata'=>$notificationdata,'created_date'=>NOW);

            
            foreach($get_list_arr['result'] as $res)
            {
                $ins_arr[]=" ('".$notificationid."', '".$res->userid."', '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); 
            }
        }
    }
}

//GET TYPE LIST
function typelist () {
    global $conn;
	try { 
        
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';   
        // $_SESSION["userid"]=1;
		if(isset($_SESSION["userid"]))
        {   
            $training_str = "SELECT type_id as id,title FROM ".TABLE_VIDEOTRAININGTITLE." ORDER BY id ASC";
            $trainer_arr=$conn->getQueryValue($training_str);
            $arr=(count($trainer_arr)>0)?$trainer_arr:array();
                        
            $conn->message(true,$conn->getMessage($lang,'videotraining','success1'), $arr);        
		}
		else{
			$conn->message(false,$conn->getMessage($lang,'videotraining','error1'));
		}
	}
	catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'videotraining','error4'));
	}
}

//GET ROLE USER
function gettraineruser () {
    global $conn;
	try { 
        
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';   
        //$_SESSION["userid"]=1;
		if(isset($_SESSION["userid"]))
        {  
            $role='5';
                
            $query_str = "SELECT userid,emp_code,fname,lname,dealer_code,CASE WHEN role=14 THEN dealer_code WHEN role=5 THEN emp_code ELSE emailid END as value FROM ".TABLE_USER." WHERE is_deleted=0 AND status='1' AND role='".$role."' ORDER BY fname ASC,userid ASC ";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);
                        
        
            $conn->message(true,$conn->getMessage($lang,'videotraining','success1'), $arr);        
		}
		else{
			$conn->message(false,$conn->getMessage($lang,'videotraining','error1'));
		}
	}
	catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'videotraining','error4'));
	}
}

//GET ZM
function getzmuser () {
    global $conn;
	try { 
        
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';   
        //$_SESSION["userid"]=1;
		if(isset($_SESSION["userid"]))
        {  
            $role='4';
                
            $query_str = "SELECT userid,emp_code,fname,lname,dealer_code, emailid as value FROM ".TABLE_USER." WHERE is_deleted=0 AND status='1' AND role='".$role."' ORDER BY fname ASC,userid ASC ";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);
                        
        
            $conn->message(true,$conn->getMessage($lang,'videotraining','success1'), $arr);        
		}
		else{
			$conn->message(false,$conn->getMessage($lang,'videotraining','error1'));
		}
	}
	catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'videotraining','error4'));
	}
}


//GET RM
function getrmuser () {
    global $conn;
	try { 
        
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';   
        //$_SESSION["userid"]=1;
		if(isset($_SESSION["userid"]))
        {  
            $role='6';
            $zmid=escape_str($_REQUEST['pid']);
            
            $user_str = "SELECT emailid FROM ".TABLE_USER." WHERE userid='".$zmid."' AND role=4";
            $user_arr=$conn->getQueryValue($user_str);
            $cond=(count($user_arr)>0)? " AND d.zm_email='".$user_arr[0]->emailid."'":" AND d.zm_email='00'";
                
                
            $query_str = "SELECT u.userid,u.emp_code,u.fname,u.lname,u.dealer_code, u.emailid as value FROM ".TABLE_USER." u JOIN ".DMS_DEALER." d ON u.emailid=d.rm_email  WHERE d.is_deleted=0 AND u.is_deleted=0 AND u.status='1' AND u.role='".$role."' ".$cond." GROUP BY u.userid ORDER BY u.fname ASC,u.userid ASC ";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);
                        
        
            $conn->message(true,$conn->getMessage($lang,'videotraining','success1'), $arr);        
		}
		else{
			$conn->message(false,$conn->getMessage($lang,'videotraining','error1'));
		}
	}
	catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'videotraining','error4'));
	}
}


//GET AM
function getamuser () {
    global $conn;
	try { 
        
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';   
        //$_SESSION["userid"]=1;
		if(isset($_SESSION["userid"]))
        {  
            $role='7';
            $rmid=escape_str($_REQUEST['pid']);
            
            $user_str = "SELECT emailid FROM ".TABLE_USER." WHERE userid='".$rmid."' AND role=6";
            $user_arr=$conn->getQueryValue($user_str);
            $cond=(count($user_arr)>0)? " AND d.rm_email='".$user_arr[0]->emailid."'":" AND d.rm_email='00'";
                
                
            $query_str = "SELECT u.userid,u.emp_code,u.fname,u.lname,u.dealer_code, u.emailid as value FROM ".TABLE_USER." u JOIN ".DMS_DEALER." d ON u.emailid=d.am_email WHERE d.is_deleted=0 AND u.is_deleted=0 AND u.status='1' AND u.role='".$role."' ".$cond." GROUP BY u.userid ORDER BY u.fname ASC,u.userid ASC ";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);
                        
        
            $conn->message(true,$conn->getMessage($lang,'videotraining','success1'), $arr);        
		}
		else{
			$conn->message(false,$conn->getMessage($lang,'videotraining','error1'));
		}
	}
	catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'videotraining','error4'));
	}
}

//GET  DEALERUSER
function getdealeruser () {
    global $conn;
	try { 
        
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';   
        //$_SESSION["userid"]=1;
		if(isset($_SESSION["userid"]))
        {  
            $role='14';
            $amid=escape_str($_REQUEST['pid']);
            
            $user_str = "SELECT emailid FROM ".TABLE_USER." WHERE userid='".$amid."' AND role=7";
            $user_arr=$conn->getQueryValue($user_str);
            $cond=(count($user_arr)>0)? " AND d.am_email='".$user_arr[0]->emailid."'":" AND d.am_email='00'";
                
                
            $query_str = "SELECT u.userid,u.emp_code,u.fname,u.lname,u.dealer_code, u.dealer_code as value FROM ".TABLE_USER." u JOIN ".DMS_DEALER." d ON d.dealer_code=u.dealer_code  WHERE d.is_deleted=0 AND u.is_deleted=0 AND u.status='1' AND u.role='".$role."' ".$cond." GROUP BY u.userid ORDER BY u.fname ASC,u.userid ASC ";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);
                        
        
            $conn->message(true,$conn->getMessage($lang,'videotraining','success1'), $arr);        
		}
		else{
			$conn->message(false,$conn->getMessage($lang,'videotraining','error1'));
		}
	}
	catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'videotraining','error4'));
	}
}



//DELETE TRAINING
function deletetraining () {
	global $conn;
	try { 
        $lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us'; 
        if(isset($_SESSION["userid"]))
        { 
            $deleteid=escape_str($_REQUEST['id']);

            $get_list_arr=$conn->selectQuery(TABLE_VIDEOTRAININGSCHEDULE." s " ,"s.id"," s.status='1' AND s.is_deleted=0 AND s.id='".$deleteid."' AND s.is_admin=1","1");
            if(!$get_list_arr['nr']>0) $conn->message(false,$conn->getMessage($lang,'videotraining','error2'));

            $delete_str= "DELETE FROM  ".TABLE_VIDEOTRAININGSCHEDULE ." WHERE id='".$deleteid."' and is_admin='1'";
            $conn->executeQuery($delete_str);
            
            $conn->message(true,$conn->getMessage($lang,'videotraining','success1'));        
        }
		else{
			$conn->message(false,$conn->getMessage($lang,'videotraining','error1'));
		}
        
    } catch (Exception $ex) {
        $conn->message(false,$conn->getMessage($lang,'videotraining','error4'));
    }
}
//GET TRAINING PARTICIPANT LIST
function trainingparticipantlist () {
    global $conn;
	try { 
        
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';   
        //$_SESSION["userid"]=1;
		if(isset($_SESSION["userid"]))
        {  
            $id=escape_str($_REQUEST['id']); 
            
            $get_list_arr=$conn->selectQuery(TABLE_VIDEOTRAININGSCHEDULE ,"id"," is_deleted=0 AND id='".$id."' AND  is_admin=1","1");
            if(!$get_list_arr['nr']>0) $conn->message(false,$conn->getMessage($lang,'videotraining','error2'));          
            
            $query_str="SELECT u.userid,u.fname,u.lname,u.smpin,u.dealer_code,d.dealer_name,u.segment,CASE WHEN u.segment=0 THEN 'Student Learner' WHEN u.segment=1 THEN 'Parents/Guardian' WHEN u.segment=2 THEN 'Instructor' END as segment_str  FROM ".TABLE_VIDEOSCHEDULEUSER." vu JOIN  ".TABLE_USER." u ON vu.userid=u.userid LEFT JOIN ".DMS_EMPLOYEE." e ON u.smpin=e.smpin LEFT JOIN ".DMS_DEALER." d ON u.dealer_code=d.dealer_code  WHERE u.status='1' AND u.is_deleted='0' AND vu.sid='".$id."' ORDER BY  u.dealer_code ASC,u.fname ASC,u.userid ASC";
            $arr=$conn->getQueryValue($query_str);  
        
            $conn->message(true,$conn->getMessage($lang,'videotraining','success1'), $arr);        
		}
		else{
			$conn->message(false,$conn->getMessage($lang,'videotraining','error1'));
		}
	}
	catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'videotraining','error4'));
	}
}

//ADD PARTICIPANT
function addparticipant () {
    global $conn;
	try { 
        
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';   
        //$_SESSION["userid"]=1;
		if(isset($_SESSION["userid"]))
        {  
            $id=escape_str($_REQUEST['id']); 
            $userids=escape_str($_REQUEST['userid']);  
            $trainingid=$id;
            
            $get_list_arr=$conn->selectQuery(TABLE_VIDEOTRAININGSCHEDULE ,"id","   is_deleted=0 AND id='".$id."' AND is_admin=1","1");
            if(!$get_list_arr['nr']>0) $conn->message(false,$conn->getMessage($lang,'videotraining','error2'));
            
            if(is_array($userids) && count($userids)>0)
            {   

                $ins_arr=array();
                $uids=array();
                foreach($userids as $userid)
                {
                    $ins_arr[]=" ('".$trainingid."','".$userid."','1','".NOW."','".NOW."')";
                    $uids[]=$userid;
                }
                
                //check already exist
                $check_arr=$conn->selectQuery(TABLE_VIDEOSCHEDULEUSER ,"id","   sid='".$trainingid."'  AND userid IN (".@implode(",",$uids).")","1");
                if($check_arr['nr']>0 )$conn->message(false,$conn->getMessage($lang,'videotraining','error9'));
                
                $insert_usr= "INSERT INTO ".TABLE_VIDEOSCHEDULEUSER ." (`sid`, `userid`, `status`, `created_on`, `modified_on`) VALUES ".@implode(',',$ins_arr);
                $conn->executeQuery($insert_usr);
                
                
                $extobj=new stdClass();
                $extobj->userid=$uids;
                notificationtriggervideotraining($trainingid,$extobj);
                
                $conn->message(true,$conn->getMessage($lang,'videotraining','success1')); 
            }else
            {
                $conn->message(false,$conn->getMessage($lang,'videotraining','error5'));
            }
                
		}
		else{
			$conn->message(false,$conn->getMessage($lang,'videotraining','error1'));
		}
	}
	catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'videotraining','error4'));
	}
}

//DELETE PARTICIPANT
function deleteparticipant () {
    global $conn;
	try { 
        
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';   
        //$_SESSION["userid"]=1;
		if(isset($_SESSION["userid"]))
        {  
            $trainingid=escape_str($_REQUEST['id']); 
            $userid=escape_str($_REQUEST['userid']);    
            
            
            $delete_msg= "DELETE FROM  ".TABLE_VIDEOSCHEDULEUSER ." WHERE sid='".$trainingid."' and userid='".$userid."'";
            $conn->executeQuery($delete_msg);

            //TRIGGER NOTIFICATION
            $extobj=new stdClass();
            $extobj->userid=array($userid);
            $extobj->type='removed';
            notificationtriggervideotraining($trainingid,$extobj);
            
        
            $conn->message(true,$conn->getMessage($lang,'videotraining','success1'));        
		}
		else{
			$conn->message(false,$conn->getMessage($lang,'videotraining','error1'));
		}
	}
	catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'videotraining','error4'));
	}
}

      
//ilearnuserlist LIST
function ilearnuserlist () {
	global $conn;
	try { 
        
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us'; 
		$role = escape_str($_REQUEST["role"]); 
        $userid = escape_str($_REQUEST["userid"]); 
        
		if(isset($_SESSION["userid"]))
        {
             
            $get_trainer_str = "SELECT h.zone FROM ".DMS_HOUSER." h JOIN ".TABLE_USER." u ON u.emp_code=h.emp_code WHERE u.userid='".$userid."' AND u.role='5' LIMIT 1";
            $trainer_arr=$conn->getQueryValue($get_trainer_str);
            if(!count($trainer_arr)>0) $conn->message(false,$conn->getMessage($lang,'videotraining','error10'));
            switch ($role)
            {
                case "4": 
                    $dealer_cond=" ON u.emailid=d.zm_email ";
                    $usercond=" AND u.role=4 ";
                    break;
                case "6": 
                    $dealer_cond=" ON u.emailid=d.rm_email ";
                    $usercond=" AND u.role=6 AND d.zone='".$trainer_arr[0]->zone."'";
                    break;
                case "7": 
                    $dealer_cond=" ON u.emailid=d.am_email ";
                    $usercond=" AND u.role=7 AND d.zone='".$trainer_arr[0]->zone."'";
                    break;
                default :$conn->message(false,$conn->getMessage($lang,'videotraining','error3')); break;
            }

            $query_str="SELECT u.userid,u.fname,u.lname,u.emp_code as smpin,'' as  dealer_code,'' as  dealer_name,'' as  segment,''  as segment_str,u.profile_photo FROM ".TABLE_USER." u LEFT JOIN ".DMS_DEALER." d ".$dealer_cond."  WHERE  u.status='1' AND u.is_deleted='0' ".$usercond." GROUP BY u.userid ORDER BY u.fname ASC,u.userid ASC";
            $data_arr=$conn->getQueryValue($query_str);
            $arr=(count($data_arr)>0)?$data_arr:array();
            
            
			if(count($arr) != 0){
				$conn->message(true,$conn->getMessage($lang,'videotraining','success1'), $arr); 
			}else{
				$conn->message(false,$conn->getMessage($lang,'videotraining','error2'));
			}
		}
		else{
			$conn->message(false,$conn->getMessage($lang,'videotraining','error1'));
		}
	}
	catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'videotraining','error4'));
	}
}
?>