<?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 "userslist": 
		case "adduser":
        case "edituser":
        case "deleteuser": 
        case "inactiveuserlist":    
        case "importuser":
        case "activeuser":    
        case "importdealer":    
			$fun();     
		break;
        
	}
}
        
// USERS LIST
function userslist() {
	global $conn;
	try {
        $lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';
        
		$pn = $_REQUEST["pn"];
		$nr = $_REQUEST["nr"];
		$key = escape_str($_REQUEST["key"]);
        //$_SESSION["userid"]=1;
		if(isset($_SESSION["userid"])){
            //$rcond=($_SESSION["role"]==1)?" AND a.role <> 0 ":" AND  a.role>2";
            $rcond=" AND a.role>2";
            $userstr = "SELECT SQL_CALC_FOUND_ROWS * FROM ((SELECT id,smpin,emp_code,`fname`, `lname`,'emp' as type,`dealer_code`, `emailid`, `mobile`,`role`,`zone`,`date_of_join`,status,segment FROM `".DMS_EMPLOYEE."`)  UNION  (SELECT id,'' as smpin, emp_code,`fname`, `lname`,'other' as type,'' as dealer_code, `emailid`, `mobile`,`role`,`zone`,`date_of_join`,status,'0' as segment FROM `".DMS_HOUSER."`) UNION  (SELECT userid as id,'' as smpin, emp_code,`fname`, `lname`,'admin' as type,'' as dealer_code, `emailid`, `mobileno` as mobile,`role`,'' as zone,'' as date_of_join,status,'0' as segment FROM `".TABLE_USER."` WHERE role=2 AND is_deleted='0'))a WHERE 1=1";
            
			//$userstr = "SELECT SQL_CALC_FOUND_ROWS a.`userid`, a.`smpin`, a.`emp_code`, a.`password`, a.`fname`, a.`lname`, a.`emailid`, a.`mobileno`, a.`role`, a.`dealer_code`, a.`address_1`, a.`address_2`, a.`address_3`,  a.`profile_photo`, a.`dob`, a.`doj`, a.`country`, a.`auth_token`, a.`otp`, a.`otp_expiry`, a.`language`, a.`is_married`, a.`marriage_date`, a.`status`, a.`last_password_change`, a.`is_first_login`, a.`is_profile_updated`, a.`is_deleted`,  b.status as status_name, b.statusid FROM " . TABLE_USER . " a LEFT JOIN (SELECT * FROM " . TABLE_STATUS . ") b ON b.statusid = a.status WHERE a.is_deleted = 0 ".$rcond;
            
			if (!empty($key)) {
				$userstr .= " and ((a.emp_code like '%".$key."%') or (a.fname like '%".$key."%') or (a.lname like '%".$key."%') or (a.mobile like '%".$key."%') OR (a.dealer_code LIKE '%".$key."%') )  ";
			}
			if (!empty($pn) and !empty($nr)) {
				// Navigation
    			$userstr .= " limit " .  (($pn - 1) *  $nr) . ", " . $nr;
			}
        
			$query = $conn->query($userstr);

			// 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;

			$arr['total_records'] = $total_records;
            $userlist=$query->fetchAll(PDO::FETCH_OBJ);
            if(count($userlist)>0)
            {
                foreach($userlist as $mkey=>$res)
                {
                    $userlist[$mkey]->dob=($res->dob=='0000-00-00')?'':$res->dob;
                    $userlist[$mkey]->is_registered='0';
                    //REGISTER CHECK CONDITION
                    if($res->type=='emp')
                    {
                        $check_result_str = "SELECT userid FROM `".TABLE_USER."` WHERE 	smpin='".$res->smpin."'  LIMIT 1";  
                        $check_result_arr=$conn->getQueryValue($check_result_str);  
                        
                        $userlist[$mkey]->is_registered=(count($check_result_arr)>0)?"1":"0";
                        
                    }else if($res->type=='other')
                    {
                        $check_result_str = "SELECT userid FROM `".TABLE_USER."` WHERE 	emp_code='".$res->emp_code."'  LIMIT 1";  
                        $check_result_arr=$conn->getQueryValue($check_result_str); 
                        
                        $userlist[$mkey]->is_registered=(count($check_result_arr)>0)?"1":"0";
                    }
                    
                    if($res->type=='admin')
                    {
                        $userlist[$mkey]->roleid=$res->role;
                        $userlist[$mkey]->role="Admin";
                    }else
                    {
                        //GET ROLE
                        $query_str = "SELECT `id`, `rolename`  FROM ".TABLE_ROLE." WHERE status=1 AND rolename='".$res->role."' LIMIT 1";
                        $check_result_arr=$conn->getQueryValue($query_str); 
                    
                        $userlist[$mkey]->roleid=(count($check_result_arr)>0)?$check_result_arr[0]->id:"0";
                    }
                }
            }
            
			$arr['users'] = $userlist;
			if(count($arr) != 0) {
				$conn->message(true,$conn->getMessage($lang,'user','success1'),$arr );
			}else {
				$conn->message(false,$conn->getMessage($lang,'user','error2'));
			}
		}else {
			$conn->message(false,$conn->getMessage($lang,'user','error1'));
		}
	}
	catch(PDOException $e) {
		$conn->message(false,$conn->getMessage($lang,'user','error4'));
	}	
}

      
// ADD USERS
function adduser() {
	global $conn;
	try {
        $lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';
        
		if(isset($_SESSION["userid"]))
        { 
            $role = escape_str($_REQUEST["role"]);            
            if($role>7 || $role==15)
            {
               //GET EMPLOYEE 
                $smpin = escape_str($_REQUEST["smpin"]); 
                $emp_code = escape_str($_REQUEST["emp_code"]); 
                $fname = escape_str($_REQUEST["fname"]); 
                $lname = escape_str($_REQUEST["lname"]); 
                $dealer_code = escape_str($_REQUEST["dealer_code"]); 
                $emailid = escape_str($_REQUEST["emailid"]);
                $mobile= escape_str($_REQUEST["mobile"]);
                $date_of_join=(escape_str($_REQUEST["date_of_join"]))? escape_str($_REQUEST["date_of_join"]):'';
                $status=1;
                $segment=(escape_str($_REQUEST["segment"]))? escape_str($_REQUEST["segment"]):'0';
                
                if(!($smpin!='' && $emp_code!='' && $fname!=''   && $dealer_code!='' && $emailid!='' && $mobile!='' && $date_of_join!=''))
                {
                    $conn->message(false,$conn->getMessage($lang,'user','error6')); 
                }
                
                //GET ROLE
                $query_str = "SELECT `id`, `rolename`  FROM ".TABLE_ROLE." WHERE status=1 AND id='".$role."' LIMIT 1";
                $check_result_arr=$conn->getQueryValue($query_str); 
                
                if(!count($check_result_arr)){ $conn->message(false,$conn->getMessage($lang,'user','error5')); }                
                $rolename=$check_result_arr[0]->rolename;
                
                //GET SMPIN
                $query_str = "SELECT `id`  FROM ".DMS_EMPLOYEE." WHERE smpin='".$smpin."' LIMIT 1";
                $check_result_arr=$conn->getQueryValue($query_str); 
                
                if(count($check_result_arr)){ $conn->message(false,$conn->getMessage($lang,'user','error7')); }    
        
                //GET EMP CODE
                $query_str = "SELECT `id`  FROM ".DMS_HOUSER." WHERE emp_code='".$smpin."' LIMIT 1";
                $check_result_arr=$conn->getQueryValue($query_str);                
                if(count($check_result_arr)){ $conn->message(false,$conn->getMessage($lang,'user','error12')); }  
                
                
                $insertstr = "INSERT INTO ".DMS_EMPLOYEE." (  `smpin`, `emp_code`, `fname`, `lname`, `dealer_code`, `role`, `emailid`, `mobile`,   `date_of_join`,`segment`, `status`, `created_on`, `modified_on`) VALUES(  '".$smpin."', '".$emp_code."', '".$fname."', '".$lname."', '".$dealer_code."', '".$rolename."', '".$emailid."', '".$mobile."',  '".$date_of_join."','".$segment."', '".$status."', '".NOW."', '".NOW."')";
                $insertquery = $conn->query($insertstr);
                $insertid = $conn->lastinsetid();       
                
                $insert_log_str="INSERT INTO `".DMS_EMPLOYEE_LOG."` (  `smpin`, `emp_code`, `fname`, `lname`, `dealer_code`, `role`, `emailid`, `mobile`,   `date_of_join`,`segment`, `status`, `created_on`, `modified_on`) VALUES(  '".$smpin."', '".$emp_code."', '".$fname."', '".$lname."', '".$dealer_code."', '".$rolename."', '".$emailid."', '".$mobile."',  '".$date_of_join."','".$segment."', '".$status."', '".NOW."', '".NOW."')";
                $insert_log_query = $conn->query($insert_log_str);
                
            }else if(($role>2 && $role<7)||$role=='14'|| $role=='16')
            {
                //GET HOUSER
                // $smpin = escape_str($_REQUEST["smpin"]); 
                $smpin='';
                $emp_code = escape_str($_REQUEST["emp_code"]); 
                $fname = escape_str($_REQUEST["fname"]); 
                $lname = escape_str($_REQUEST["lname"]); 
                $dealer_code = escape_str($_REQUEST["dealer_code"]); 
                $emailid = escape_str($_REQUEST["emailid"]);
                $mobile= escape_str($_REQUEST["mobile"]);
                //$date_of_join= escape_str($_REQUEST["date_of_join"]);
                $date_of_join='';
                $zone= escape_str($_REQUEST["zone"]);
                $status=1;
                
                if(!( $emp_code!='' && $fname!='' &&  $emailid!='' && $mobile!=''  ))
                {
                    $conn->message(false,$conn->getMessage($lang,'user','error6')); 
                }
                
                if(!( $dealer_code!='')&& ($role=='14' || $role=='16') )
                {
                    $conn->message(false,$conn->getMessage($lang,'user','error6')); 
                }
                
                if(!( $zone!='' )  && $role=='5' )
                {
                    $conn->message(false,$conn->getMessage($lang,'user','error6')); 
                }
                
                //GET ROLE
                $query_str = "SELECT `id`, `rolename`  FROM ".TABLE_ROLE." WHERE status=1 AND id='".$role."' LIMIT 1";
                $check_result_arr=$conn->getQueryValue($query_str);    
                
                if(!count($check_result_arr)){ $conn->message(false,$conn->getMessage($lang,'user','error5')); }
                $rolename=$check_result_arr[0]->rolename;
                
                //GET EMP CODE
                $query_str = "SELECT `id`  FROM ".DMS_HOUSER." WHERE emp_code='".$emp_code."' LIMIT 1";
                $check_result_arr=$conn->getQueryValue($query_str);
                if(count($check_result_arr)){ $conn->message(false,$conn->getMessage($lang,'user','error8')); }   
                
                //GET SMPIN
                $query_str = "SELECT `id`  FROM ".DMS_EMPLOYEE." WHERE smpin='".$emp_code."' LIMIT 1";
                $check_result_arr=$conn->getQueryValue($query_str);                 
                if(count($check_result_arr)){ $conn->message(false,$conn->getMessage($lang,'user','error13')); }    
                
               $insertstr = "INSERT INTO ".DMS_HOUSER." (   `emp_code`, `fname`, `lname`, `dealer_code`,   `role`, `emailid`, `mobile`,`zone`, `date_of_join`, `status`, `created_on`, `modified_on`) VALUES(   '".$emp_code."', '".$fname."', '".$lname."','".$dealer_code."',  '".$rolename."', '".$emailid."', '".$mobile."', '".$zone."' , '".$date_of_join."', '".$status."', '".NOW."', '".NOW."')";
                $insertquery = $conn->query($insertstr);
                $insertid = $conn->lastinsetid();   
                
            }else if($role==2)
            {
                $emp_code = escape_str($_REQUEST["emp_code"]); 
                $password = $_REQUEST["password"]; 
                $fname = escape_str($_REQUEST["fname"]); 
                $lname = escape_str($_REQUEST["lname"]); 
                $emailid = escape_str($_REQUEST["emailid"]);
                $mobile= escape_str($_REQUEST["mobile"]);
                $status=1;
                
                if(!( $emp_code!='' && $password!='' &&  $fname!='' &&  $emailid!='' && $mobile!=''  ))
                {
                    $conn->message(false,$conn->getMessage($lang,'user','error6')); 
                }
                
                 //GET EMP CODE
                $query_str = "SELECT `userid`  FROM ".TABLE_USER." WHERE emp_code='".$emp_code."' AND role<3 LIMIT 1";
                $check_result_arr=$conn->getQueryValue($query_str); 
                
                if(count($check_result_arr)){ $conn->message(false,$conn->getMessage($lang,'user','error9')); }   
                
                $insertstr = "INSERT INTO ".TABLE_USER." ( `emp_code`, `password`, `fname`, `lname`, `emailid`, `mobileno`,language,role, `status`,`is_deleted`, `created_on`, `modified_on`) VALUES(  '".$emp_code."','".md5($password)."', '".$fname."', '".$lname."',   '".$emailid."', '".$mobile."','en_us',2, '1','0',  '".NOW."', '".NOW."')";
                $insertquery = $conn->query($insertstr);
                $insertid = $conn->lastinsetid();   
            }
            $conn->message(true,$conn->getMessage($lang,'user','success1')); 
		}else {
			$conn->message(false,$conn->getMessage($lang,'user','error1'));
		}
	}
	catch(PDOException $e) {
		$conn->message(false,$conn->getMessage($lang,'user','error4'));
	}	
}

// EDIT USERS
function edituser() {
	global $conn;
	try {
        $lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';
        
		if(isset($_SESSION["userid"]))
        { 
            $id = escape_str($_REQUEST["id"]);
            $role = escape_str($_REQUEST["role"]);            
            if($role>7|| $role==15)
            {
                //GET EMPLOYEE
                $query_str = "SELECT *  FROM ".DMS_EMPLOYEE." WHERE  id='".$id."' LIMIT 1";
                $check_data_arr=$conn->getQueryValue($query_str); 
                
                if(!(count($check_data_arr)>0))  $conn->message(false,$conn->getMessage($lang,'user','error2')); 
                
                $smpin = escape_str($_REQUEST["smpin"]); 
                $emp_code = escape_str($_REQUEST["emp_code"]); 
                $fname = escape_str($_REQUEST["fname"]); 
                $lname = escape_str($_REQUEST["lname"]); 
                $dealer_code = escape_str($_REQUEST["dealer_code"]); 
                $emailid = escape_str($_REQUEST["emailid"]);
                $mobile= escape_str($_REQUEST["mobile"]);
                $date_of_join=(escape_str($_REQUEST["date_of_join"]))? escape_str($_REQUEST["date_of_join"]):'';
                $status=1;
                $segment=(escape_str($_REQUEST["segment"]))? escape_str($_REQUEST["segment"]):'0';
                
                if(strtolower($smpin)!=strtolower($check_data_arr[0]->smpin))
                {
                    //GET SMPIN
                    $query_str = "SELECT `userid`  FROM ".TABLE_USER." WHERE smpin='".$check_data_arr[0]->smpin."'  AND is_deleted='0' AND role>7 LIMIT 1";
                    $check_user_arr=$conn->getQueryValue($query_str);                                         
                    if(count($check_user_arr)>0)
                    {
                        $conn->message(false,$conn->getMessage($lang,'user','error10')); 
                    }
                }
                
                if(!($smpin!='' && $emp_code!='' && $fname!=''   && $dealer_code!='' && $emailid!='' && $mobile!='' && $date_of_join!=''))
                {
                    $conn->message(false,$conn->getMessage($lang,'user','error6')); 
                }
        
                //GET SMPIN
                $query_str = "SELECT `id`  FROM ".DMS_EMPLOYEE." WHERE smpin='".$smpin."' AND id!='".$id."'   LIMIT 1";
                $check_result_arr=$conn->getQueryValue($query_str);                 
                if(count($check_result_arr)){ $conn->message(false,$conn->getMessage($lang,'user','error7')); }    
                
                //GET EMP CODE
                $query_str = "SELECT `id`  FROM ".DMS_HOUSER." WHERE emp_code='".$smpin."' LIMIT 1";
                $check_result_arr=$conn->getQueryValue($query_str); 
                if(count($check_result_arr)){ $conn->message(false,$conn->getMessage($lang,'user','error12')); }    
                
                $updatestr = "UPDATE ".DMS_EMPLOYEE." SET `smpin`= '".$smpin."', `emp_code`='".$emp_code."', `fname`= '".$fname."', `lname`='".$lname."', `dealer_code`= '".$dealer_code."', `emailid`= '".$emailid."', `mobile`='".$mobile."',`date_of_join`='".$date_of_join."',segment='".$segment."', `modified_on`='".NOW."' WHERE id='".$id."'";
                $updatequery = $conn->query($updatestr);  
                
            }else if(($role>2 && $role<=7)||$role=='14' ||$role=='16' )
            {
                //GET HOUSER
                $query_str = "SELECT *  FROM ".DMS_HOUSER." WHERE  id='".$id."' LIMIT 1";
                $check_data_arr=$conn->getQueryValue($query_str); 
                
                if(!(count($check_data_arr)>0))  $conn->message(false,$conn->getMessage($lang,'user','error2')); 
                
                $smpin='';
                $emp_code = escape_str($_REQUEST["emp_code"]); 
                $fname = escape_str($_REQUEST["fname"]); 
                $lname = escape_str($_REQUEST["lname"]); 
                $dealer_code = escape_str($_REQUEST["dealer_code"]); 
                $emailid = escape_str($_REQUEST["emailid"]);
                $mobile= escape_str($_REQUEST["mobile"]);
                $date_of_join= escape_str($_REQUEST["date_of_join"]);
                $zone= escape_str($_REQUEST["zone"]);
                $status=1;
                
                if(!($emp_code!='' && $fname!='' &&  $emailid!='' && $mobile!=''  ))
                {
                    $conn->message(false,$conn->getMessage($lang,'user','error6')); 
                }
                
                if(!( $dealer_code!='')&& $role=='14' )
                {
                    $conn->message(false,$conn->getMessage($lang,'user','error6')); 
                }
                
                if(!( $zone!='' )  && $role=='5' )
                {
                    $conn->message(false,$conn->getMessage($lang,'user','error6')); 
                }
                
                if(strtolower($emp_code)!=strtolower($check_data_arr[0]->emp_code))
                {
                    //GET SMPIN
                    $query_str = "SELECT `userid`  FROM ".TABLE_USER." WHERE emp_code='".$check_data_arr[0]->emp_code."'  AND is_deleted='0' AND (role<8 OR role='14') AND role>2 LIMIT 1";
                    $check_user_arr=$conn->getQueryValue($query_str); 
                                        
                    if(count($check_user_arr)>0)
                    {
                        $conn->message(false,$conn->getMessage($lang,'user','error10')); 
                    }
                }
                                
                //GET EMP CODE
                $query_str = "SELECT `id`  FROM ".DMS_HOUSER." WHERE emp_code='".$emp_code."' AND id!='".$id."' LIMIT 1";
                $check_result_arr=$conn->getQueryValue($query_str); 
                if(count($check_result_arr)){ $conn->message(false,$conn->getMessage($lang,'user','error8')); }   
                
                //GET SMPIN
                $query_str = "SELECT `id`  FROM ".DMS_EMPLOYEE." WHERE smpin='".$emp_code."' LIMIT 1";
                $check_result_arr=$conn->getQueryValue($query_str);                 
                if(count($check_result_arr)){ $conn->message(false,$conn->getMessage($lang,'user','error13')); }    
                
                
                $updatestr = "UPDATE ".DMS_HOUSER."  SET  `emp_code`='".$emp_code."', `fname`='".$fname."', `lname`='".$lname."', dealer_code='".$dealer_code."' ,`emailid`='".$emailid."', `mobile`='".$mobile."',  zone='".$zone."',`modified_on`='".NOW."'  WHERE id='".$id."'";
                $updatequery = $conn->query($updatestr); 
                
            }else if($role==2)
            {
                //GET ADMIN
                $query_str = "SELECT *  FROM ".TABLE_USER." WHERE  userid='".$id."' AND role=2 LIMIT 1";
                $check_data_arr=$conn->getQueryValue($query_str); 
                
                if(!(count($check_data_arr)>0))  $conn->message(false,$conn->getMessage($lang,'user','error2')); 
                
                
                $emp_code = escape_str($_REQUEST["emp_code"]); 
                //$password = escape_str($_REQUEST["password"]); 
                $fname = escape_str($_REQUEST["fname"]); 
                $lname = escape_str($_REQUEST["lname"]); 
                $emailid = escape_str($_REQUEST["emailid"]);
                $mobile= escape_str($_REQUEST["mobile"]);
                $status=1;
                
                if(!( $emp_code!='' && $password!='' &&  $fname!='' &&  $emailid!='' && $mobile!=''  ))
                {
                    $conn->message(false,$conn->getMessage($lang,'user','error6')); 
                }
                
//                if($password!='')
//                {
//                    $cond=",`password`=".md5($password)."";
//                }
                
                 //GET EMP CODE
                $query_str = "SELECT `userid` FROM ".TABLE_USER." WHERE emp_code='".$emp_code."' AND role<3 AND  userid!='".$id."'  LIMIT 1";
                $check_result_arr=$conn->getQueryValue($query_str);                 
                if(count($check_result_arr)){ $conn->message(false,$conn->getMessage($lang,'user','error9')); }   
                
                $updatestr = "UPDATE  ".TABLE_USER." SET `emp_code`='".$emp_code." ,`fname`='".$fname."', `lname`='".$lname."', `emailid`='".$emailid."', `mobileno`='".$mobile."' ,`modified_on`='".NOW."' ".$cond." WHERE  userid='".$id."' ";
                $updatequery = $conn->query($updatestr);  
            }
            $conn->message(true,$conn->getMessage($lang,'user','success1')); 
		}else {
			$conn->message(false,$conn->getMessage($lang,'user','error1'));
		}
	}
	catch(PDOException $e) {
		$conn->message(false,$conn->getMessage($lang,'user','error4'));
	}	
}

// DELETE USERS
function deleteuser() {
	global $conn;
	try {
        $lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';
      
		if(isset($_SESSION["userid"]))
        {  
            $id = escape_str($_REQUEST["id"]); 
            $role = escape_str($_REQUEST["roleid"]); 
            
            if($role>7 || $role=='15')
            {
                //GET EMPLOYEE
                $query_str = "SELECT *  FROM ".DMS_EMPLOYEE." WHERE  id='".$id."' LIMIT 1";
                $check_data_arr=$conn->getQueryValue($query_str); 
                
                if(!(count($check_data_arr)>0))  $conn->message(false,$conn->getMessage($lang,'user','error2')); 
                
                $query_str = "DELETE FROM ".DMS_EMPLOYEE." WHERE  id='".$id."' LIMIT 1";
                $conn->executeQuery($query_str);
                
                $query_str = "UPDATE  ".TABLE_USER." SET is_deleted='1' WHERE  smpin='".$check_data_arr[0]->smpin."' and role>7 LIMIT 1";
                $conn->executeQuery($query_str);
        
                $conn->message(true,$conn->getMessage($lang,'user','success1')); 
            }else if(($role>2 && $role<=7)||$role=='14' || $role=='16')
            {
                //GET OTHER
                $query_str = "SELECT *  FROM ".DMS_HOUSER." WHERE  id='".$id."' LIMIT 1";
                $check_data_arr=$conn->getQueryValue($query_str); 
                
                if(!(count($check_data_arr)>0))  $conn->message(false,$conn->getMessage($lang,'user','error2'));     
                
                $query_str = "DELETE FROM ".DMS_HOUSER." WHERE  id='".$id."' LIMIT 1";
                $conn->executeQuery($query_str);
                
                $query_str = "UPDATE  ".TABLE_USER." SET is_deleted='1' WHERE  emp_code='".$check_data_arr[0]->emp_code."'  LIMIT 1";
                $conn->executeQuery($query_str);
                
                $conn->message(true,$conn->getMessage($lang,'user','success1')); 
            }else if($role==2)
            {
                //GET ADMIN
                $query_str = "SELECT *  FROM ".TABLE_USER." WHERE  userid='".$id."' AND role=2 LIMIT 1";
                $check_data_arr=$conn->getQueryValue($query_str); 
                
                if(!(count($check_data_arr)>0))  $conn->message(false,$conn->getMessage($lang,'user','error2')); 
                
                $query_str = "UPDATE  ".TABLE_USER." SET is_deleted='1' WHERE  userid='".$id."' LIMIT 1";
                $conn->executeQuery($query_str);
                
                $conn->message(true,$conn->getMessage($lang,'user','success1')); 
            }
        }else {
			$conn->message(false,$conn->getMessage($lang,'user','error1'));
		}
    }
	catch(PDOException $e) {
		$conn->message(false,$conn->getMessage($lang,'user','error4'));
	}	
}


// IMPORT USERS
function importuser() {
	global $conn;
	try {
        $lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';
        
		if(isset($_SESSION["userid"]))
        { 
            $userrole=escape_str($_REQUEST["roleid"]);
            if(isset($_FILES['file']) && $_FILES['file']['name'])
            {
                $obj = new stdClass();
                $obj->filekey = 'file';
                $obj->size = '10'; //IN MB
                $obj->type = array('xlsx');	
                $conn->validateFile($obj);			
                $obj->path = "../hypermupload/import/";
                $obj->filename = 'import_'.date('Ymd')."_".uniqid();			
                $import_file = $conn->uploadFile($obj);     
        
            }else
            {
               $conn->message(false,$conn->getMessage($lang,'user','error11')); 
            }
        
            			
            switch($userrole)
            {
                case "2":
                    //ADMIN
                    adminimport($import_file);                  
                    break;
                case "3":
                case "4":               
                case "6":
                case "7":
                
                    //MANAGER USERS
                    managerimport($import_file); 
                    break;
                case "5":                       
                    //TRAINER USERS  
                    trainerimport($import_file);
                    break;
                case "14":
                    dealerimport($import_file);
                    break;
                case "8":
                case "9":
                case "10":
                case "11":
                case "12":    
                case "13":  
                case "15":    
                   //DSE LEVEL USERS
                   dseimport($import_file);
                   break;
                case "16":
                    experienceimport($import_file);
                    break;   
            }
            
                
        }else {
			$conn->message(false,$conn->getMessage($lang,'user','error1'));
		}
    }
	catch(PDOException $e) {
		$conn->message(false,$conn->getMessage($lang,'user','error4'));
	}	
}

//ADMIN IMPORT
function adminimport($import_file) {
    global $conn;
    
    require('excelreader/excel_reader2.php');
    require('excelreader/SpreadsheetReader.php');
    $Spreadsheet = new SpreadsheetReader('../hypermupload/import/'.$import_file);
    $Spreadsheet->ChangeSheet(0);	
    
    $error=0;
    $columncount=6;
    $insertcount=0;
                
    
    //$excelheadarr=array("2"=>"IMPORT ADMIN USER","3"=>"IMPORT USER","4"=>"IMPORT USER","5"=>"IMPORT USER","6"=>"IMPORT USER","7"=>"IMPORT USER","8"=>"IMPORT USER","9"=>"IMPORT USER","10"=>"IMPORT USER","11"=>"IMPORT USER","12"=>"IMPORT USER","13"=>"IMPORT USER");
    $excelhead="IMPORT ADMIN USER";
    $exceltitle=array("s.no","username","password","name","emailid","mobile");    
    $error_text[1]="INVALID EXCEL";
    $error_text[2]="MISSING FIELDS";
    $error_text[3]="USERNAME ALREADY EXIST";
    $error_text[4]="EMPTY DATA";
    $error_text[5]="USERNAME NOT UNIQUE";
    
    $emp_code=array();
    $ins_qryarr=array();
    $emp_code_arr=array();
    foreach ($Spreadsheet as $Key => $Row)
    {
        if($Key<=1)	
        {
            if($Key==0)
            {
                if($Row[0]!=$excelhead)
                {									
                    //INVALID EXCEL	
                    $conn->message(false,$error_text[1]);
                    break;
                }
            }else if($Key==1)
            { 
                for($m=0;$m<$columncount;$m++)
                {

                    if(!strtolower($Row[$m])==strtolower($exceltitle[$m]))
                    {
                        $conn->message(false,$error_text[1]);
                        break;										
                    }										
                }                
            }
        }else  
        {
            $i=1;
            $emp_code = trim(escape_str($Row[$i++]));
            //UNIQUE USERNAME CHECK
            if(in_array($emp_code,$emp_code_arr))
            {
                $conn->message(false,$error_text[5]." IN ROW NO.".($Key+1));
            }
            $emp_code_arr[]=strtolower($emp_code);
            $password = $Row[$i++];
            $fname = escape_str($Row[$i++]);
            $lname='';
            $emailid = escape_str($Row[$i++]);
            $mobile = escape_str($Row[$i++]);
            
            //DATA VALIDATION
            if(!( $emp_code!='' && $password!='' &&  $fname!='' &&  $emailid!='' && $mobile!=''  ))
            {
                $conn->message(false,$error_text[2]." IN ROW NO.".($Key+1));
            }
                        
            //GET EMP CODE
            $query_str = "SELECT `userid`  FROM ".TABLE_USER." WHERE emp_code='".$emp_code."' AND role<3 LIMIT 1";
            $check_result_arr=$conn->getQueryValue($query_str); 

            if(count($check_result_arr)){ $conn->message(false,$error_text[3]." IN ROW NO.".($Key+1)); }            
            $ins_qryarr[] ="(  '".$emp_code."','".md5($password)."', '".$fname."', '".$lname."',   '".$emailid."', '".$mobile."','en_us',2, '1','0',  '".NOW."', '".NOW."')";
            $insertcount++;
        }
    }
    
    if(count($ins_qryarr)>0)
    {
        $insert_str = "INSERT INTO ".TABLE_USER." ( `emp_code`, `password`, `fname`, `lname`, `emailid`, `mobileno`,language,role, `status`,`is_deleted`, `created_on`, `modified_on`) VALUES ".@implode(',',$ins_qryarr);
        $insertquery = $conn->query($insert_str);
        
        $conn->message(true,$conn->getMessage('en_us','user','success1')); 
    }else
    {
       $conn->message(false,$error_text[4]); 
    }                
}

//MANAGER IMPORT
function managerimport($import_file) {
    global $conn;
    
    require('excelreader/excel_reader2.php');
    require('excelreader/SpreadsheetReader.php');
    $Spreadsheet = new SpreadsheetReader('../hypermupload/import/'.$import_file);
    $Spreadsheet -> ChangeSheet(0);	
    
    $error=0;
    $columncount=7;
    $insertcount=0;
                
    $excelhead="IMPORT USER";
    $exceltitle=array("s.no","employee code","first name","last name","emailid","mobile","date of join");    
    $error_text[1]="INVALID EXCEL";
    $error_text[2]="MISSING FIELDS";
    $error_text[3]="EMPOYEE CODE ALREADY EXIST";
    $error_text[4]="EMPTY DATA";
    $error_text[5]="EMPOYEE CODE NOT UNIQUE";
    $error_text[6]="ROLE NOT FOUND";
    $error_text[7]="EMPOYEE CODE CAN'T USE";
    
    //GET ROLE
    $userrole=escape_str($_REQUEST["roleid"]);
    $query_str = "SELECT `id`, `rolename`  FROM ".TABLE_ROLE." WHERE status=1 AND id='".$userrole."' LIMIT 1";
    $check_result_arr=$conn->getQueryValue($query_str);    

    if(!count($check_result_arr)){ $conn->message(false,$error_text[6]); }
    $rolename=$check_result_arr[0]->rolename;
                
    $emp_code=array();
    $ins_qryarr=array();
    $emp_code_arr=array();
    foreach ($Spreadsheet as $Key => $Row)
    {
        if($Key<=1)	
        {
            if($Key==0)
            {
                if($Row[0]!=$excelhead)
                {									
                    //INVALID EXCEL	
                    $conn->message(false,$error_text[1]);
                    break;
                }
            }else if($Key==1)
            { 
                for($m=0;$m<$columncount;$m++)
                {

                    if(!strtolower($Row[$m])==strtolower($exceltitle[$m]))
                    {
                        $conn->message(false,$error_text[1]);
                        break;										
                    }										
                }                
            }
        }else  
        {
            $i=1;
            $emp_code = trim(escape_str($Row[$i++]));
            if(in_array($emp_code,$emp_code_arr))
            {
                $conn->message(false,$error_text[5]." IN ROW NO.".($Key+1));
            }
            $emp_code_arr[]=strtolower($emp_code); 
            $fname = escape_str($Row[$i++]);
            $lname= escape_str($Row[$i++]);
            $emailid = escape_str($Row[$i++]);
            $mobile = escape_str($Row[$i++]);
            $date_of_join = escape_str($Row[$i++]);
            $zone='';
            $status='1';
            if(!( $emp_code!='' && $fname!='' &&   $emailid!='' && $mobile!=''   ))
            {
                $conn->message(false,$error_text[2]." IN ROW NO.".($Key+1));
            }
            
            //GET EMP CODE
            $query_str = "SELECT `id`  FROM ".DMS_HOUSER." WHERE emp_code='".$emp_code."' LIMIT 1";
            $check_result_arr=$conn->getQueryValue($query_str);                
            if(count($check_result_arr)){ $conn->message(false,$error_text[3]." IN ROW NO.".($Key+1)); }           
            
            //GET SMPIN
            $query_str = "SELECT `id`  FROM ".DMS_EMPLOYEE." WHERE smpin='".$emp_code."' LIMIT 1";
            $check_result_arr=$conn->getQueryValue($query_str); 
            if(count($check_result_arr)){ $conn->message(false,$error_text[7]." IN ROW NO.".($Key+1)); }     
            
            
            $ins_qryarr[] ="(  '".$emp_code."', '".$fname."', '".$lname."', '".$rolename."', '".$emailid."', '".$mobile."', '".$zone."' , '".$date_of_join."', '".$status."', '".NOW."', '".NOW."')";
            
        }
    }
    
    if(count($ins_qryarr)>0)
    {
        $insert_str = "INSERT INTO ".DMS_HOUSER." ( `emp_code`, `fname`, `lname`,   `role`, `emailid`, `mobile`,`zone`, `date_of_join`, `status`, `created_on`, `modified_on`) VALUES ".@implode(',',$ins_qryarr);
        $insertquery = $conn->query($insert_str);
        
        $conn->message(true,$conn->getMessage('en_us','user','success1')); 
    }else
    {
       $conn->message(false,$error_text[4]); 
    }                
}

//TRAINER IMPORT
function trainerimport($import_file) {
    global $conn;
    
    require('excelreader/excel_reader2.php');
    require('excelreader/SpreadsheetReader.php');
    $Spreadsheet = new SpreadsheetReader('../hypermupload/import/'.$import_file);
    $Spreadsheet -> ChangeSheet(0);	
    
    $error=0;
    $columncount=8;
    $insertcount=0;
                
    $excelhead="IMPORT USER";
    $exceltitle=array("s.no","employee code","first name","last name","emailid","mobile","date of join","zone");    
    $error_text[1]="INVALID EXCEL";
    $error_text[2]="MISSING FIELDS";
    $error_text[3]="EMPOYEE CODE ALREADY EXIST";
    $error_text[4]="EMPTY DATA";
    $error_text[5]="EMPOYEE CODE NOT UNIQUE";
    $error_text[6]="ROLE NOT FOUND";
    $error_text[7]="EMPOYEE CODE CAN'T USE";
    
    //GET ROLE
    $userrole=escape_str($_REQUEST["roleid"]);
    $query_str = "SELECT `id`, `rolename`  FROM ".TABLE_ROLE." WHERE status=1 AND id='".$userrole."' LIMIT 1";
    $check_result_arr=$conn->getQueryValue($query_str);    

    if(!count($check_result_arr)){ $conn->message(false,$error_text[6]); }
    $rolename=$check_result_arr[0]->rolename;
    
    $emp_code=array();
    $ins_qryarr=array();
    $emp_code_arr=array();
    foreach ($Spreadsheet as $Key => $Row)
    {
        if($Key<=1)	
        {
            if($Key==0)
            {
                if($Row[0]!=$excelhead)
                {									
                    //INVALID EXCEL	
                    $conn->message(false,$error_text[1]);
                    break;
                }
            }else if($Key==1)
            { 
                for($m=0;$m<$columncount;$m++)
                {

                    if(!strtolower($Row[$m])==strtolower($exceltitle[$m]))
                    {
                        $conn->message(false,$error_text[1]);
                        break;										
                    }										
                }                
            }
        }else  
        {
            $i=1;
            $emp_code = trim(escape_str($Row[$i++]));
            if(in_array($emp_code,$emp_code_arr))
            {
                $conn->message(false,$error_text[5]." IN ROW NO.".($Key+1));
            }
            $emp_code_arr[]=strtolower($emp_code); 
            $fname = escape_str($Row[$i++]);
            $lname= escape_str($Row[$i++]);
            $emailid = escape_str($Row[$i++]);
            $mobile = escape_str($Row[$i++]);
            $date_of_join = escape_str($Row[$i++]);
            $zone = escape_str($Row[$i++]);
            $status='1';
            
            if(!( $emp_code!='' && $fname!='' &&   $emailid!='' && $mobile!='' && $zone!=''  ))
            {
                $conn->message(false,$error_text[2]." IN ROW NO.".($Key+1));
            }
            
            //GET EMP CODE
            $query_str = "SELECT `id`  FROM ".DMS_HOUSER." WHERE emp_code='".$emp_code."' LIMIT 1";
            $check_result_arr=$conn->getQueryValue($query_str);
            if(count($check_result_arr)){ $conn->message(false,$error_text[3]." IN ROW NO.".($Key+1)); }            
            
            //GET SMPIN
            $query_str = "SELECT `id`  FROM ".DMS_EMPLOYEE." WHERE smpin='".$emp_code."' LIMIT 1";
            $check_result_arr=$conn->getQueryValue($query_str); 
            if(count($check_result_arr)){ $conn->message(false,$error_text[7]." IN ROW NO.".($Key+1)); }  
            
            $ins_qryarr[] ="(  '".$emp_code."', '".$fname."', '".$lname."', '".$rolename."', '".$emailid."', '".$mobile."', '".$zone."' , '".$date_of_join."', '".$status."', '".NOW."', '".NOW."')";
            
        }
    }
    
    if(count($ins_qryarr)>0)
    {
        $insert_str = "INSERT INTO ".DMS_HOUSER." ( `emp_code`, `fname`, `lname`,   `role`, `emailid`, `mobile`,`zone`, `date_of_join`, `status`, `created_on`, `modified_on`) VALUES ".@implode(',',$ins_qryarr);
        $insertquery = $conn->query($insert_str);        
        $conn->message(true,$conn->getMessage('en_us','user','success1'));  
    }else
    {
       $conn->message(false,$error_text[4]); 
    }                
}

//DEALER IMPORT
function dealerimport($import_file) {
    global $conn;
    
    require('excelreader/excel_reader2.php');
    require('excelreader/SpreadsheetReader.php');
    $Spreadsheet = new SpreadsheetReader('../hypermupload/import/'.$import_file);
    $Spreadsheet -> ChangeSheet(0);	
    
    $error=0;
    $columncount=9;
    $insertcount=0;
                
    $excelhead="IMPORT USER";
    $exceltitle=array("s.no","employee code","first name","last name","emailid","mobile","date of join","dealer code");    
    $error_text[1]="INVALID EXCEL";
    $error_text[2]="MISSING FIELDS";
    $error_text[3]="EMPOYEE CODE ALREADY EXIST";
    $error_text[4]="EMPTY DATA";
    $error_text[5]="EMPOYEE CODE NOT UNIQUE";
    $error_text[6]="ROLE NOT FOUND";
    $error_text[7]="EMPOYEE CODE CAN'T USE";
    
    //GET ROLE
    $userrole=escape_str($_REQUEST["roleid"]);
    $query_str = "SELECT `id`, `rolename`  FROM ".TABLE_ROLE." WHERE status=1 AND id='".$userrole."' LIMIT 1";
    $check_result_arr=$conn->getQueryValue($query_str);    

    if(!count($check_result_arr)){ $conn->message(false,$error_text[6]); }
    $rolename=$check_result_arr[0]->rolename;
    
    $emp_code=array();
    $ins_qryarr=array();
    $emp_code_arr=array();
    foreach ($Spreadsheet as $Key => $Row)
    {
        if($Key<=1)	
        {
            if($Key==0)
            {
                if($Row[0]!=$excelhead)
                {									
                    //INVALID EXCEL	
                    $conn->message(false,$error_text[1]);
                    break;
                }
            }else if($Key==1)
            { 
                for($m=0;$m<$columncount;$m++)
                {

                    if(!strtolower($Row[$m])==strtolower($exceltitle[$m]))
                    {
                        $conn->message(false,$error_text[1] );
                        break;										
                    }										
                }                
            }
        }else  
        {
            $i=1;
            $emp_code = trim(escape_str($Row[$i++]));
            if(in_array($emp_code,$emp_code_arr))
            {
                $conn->message(false,$error_text[5]." IN ROW NO.".($Key+1));
            }
            $emp_code_arr[]=strtolower($emp_code); 
            $fname = escape_str($Row[$i++]);
            $lname= escape_str($Row[$i++]);
            $emailid = escape_str($Row[$i++]);
            $mobile = escape_str($Row[$i++]);
            $date_of_join = escape_str($Row[$i++]); 
            $dealer_code = escape_str($Row[$i++]);
            $status='1';
            $zone='';
            if(!( $emp_code!='' && $fname!='' &&   $emailid!='' && $mobile!='' && $dealer_code!=''  ))
            {
                $conn->message(false,$error_text[2]." IN ROW NO.".($Key+1));
            }
            
            //GET EMP CODE
            $query_str = "SELECT `id`  FROM ".DMS_HOUSER." WHERE emp_code='".$emp_code."' LIMIT 1";
            $check_result_arr=$conn->getQueryValue($query_str);
            if(count($check_result_arr)){ $conn->message(false,$error_text[3]." IN ROW NO.".($Key+1)); }            
            
            //GET SMPIN
            $query_str = "SELECT `id`  FROM ".DMS_EMPLOYEE." WHERE smpin='".$emp_code."' LIMIT 1";
            $check_result_arr=$conn->getQueryValue($query_str); 
            if(count($check_result_arr)){ $conn->message(false,$error_text[7]." IN ROW NO.".($Key+1)); }  
            
            $ins_qryarr[] ="(  '".$emp_code."', '".$fname."', '".$lname."','".$dealer_code."' ,'".$rolename."', '".$emailid."', '".$mobile."', '".$zone."',  '".$date_of_join."', '".$status."', '".NOW."', '".NOW."')";
            
        }
    }
    
    if(count($ins_qryarr)>0)
    {
        $insert_str = "INSERT INTO ".DMS_HOUSER." ( `emp_code`, `fname`, `lname`, `dealer_code`,  `role`, `emailid`, `mobile`,`zone`, `date_of_join`, `status`, `created_on`, `modified_on`) VALUES ".@implode(',',$ins_qryarr);
        $insertquery = $conn->query($insert_str);        
        $conn->message(true,$conn->getMessage('en_us','user','success1'));  
    }else
    {
       $conn->message(false,$error_text[4]); 
    }                
}


//EXPERIENCE IMPORT
function experienceimport($import_file) {
    global $conn;
    
    require('excelreader/excel_reader2.php');
    require('excelreader/SpreadsheetReader.php');
    $Spreadsheet = new SpreadsheetReader('../hypermupload/import/'.$import_file);
    $Spreadsheet -> ChangeSheet(0);	
    
    $error=0;
    $columncount=9;
    $insertcount=0;
                
    $excelhead="IMPORT USER";
    $exceltitle=array("s.no","employee code","first name","last name","emailid","mobile","dealer code");    
    $error_text[1]="INVALID EXCEL";
    $error_text[2]="MISSING FIELDS";
    $error_text[3]="EMPOYEE CODE ALREADY EXIST";
    $error_text[4]="EMPTY DATA";
    $error_text[5]="EMPOYEE CODE NOT UNIQUE";
    $error_text[6]="ROLE NOT FOUND";
    $error_text[7]="EMPOYEE CODE CAN'T USE";
    
    //GET ROLE
    $userrole=escape_str($_REQUEST["roleid"]);
    $query_str = "SELECT `id`, `rolename`  FROM ".TABLE_ROLE." WHERE status=1 AND id='".$userrole."' LIMIT 1";
    $check_result_arr=$conn->getQueryValue($query_str);    

    if(!count($check_result_arr)){ $conn->message(false,$error_text[6]); }
    $rolename=$check_result_arr[0]->rolename;
    
    $emp_code=array();
    $ins_qryarr=array();
    $emp_code_arr=array();
    foreach ($Spreadsheet as $Key => $Row)
    {
        if($Key<=1)	
        {
            if($Key==0)
            {
                if($Row[0]!=$excelhead)
                {									
                    //INVALID EXCEL	
                    $conn->message(false,$error_text[1]);
                    break;
                }
            }else if($Key==1)
            { 
                for($m=0;$m<$columncount;$m++)
                {

                    if(!strtolower($Row[$m])==strtolower($exceltitle[$m]))
                    {
                        $conn->message(false,$error_text[1] );
                        break;										
                    }										
                }                
            }
        }else  
        {
            $i=1;
            $emp_code = trim(escape_str($Row[$i++]));
            if(in_array($emp_code,$emp_code_arr))
            {
                $conn->message(false,$error_text[5]." IN ROW NO.".($Key+1));
            }
            $emp_code_arr[]=strtolower($emp_code); 
            $fname = escape_str($Row[$i++]);
            $lname= escape_str($Row[$i++]);
            $emailid = escape_str($Row[$i++]);
            $mobile = escape_str($Row[$i++]);
            $date_of_join='0000-00-00';    
            $dealer_code = escape_str($Row[$i++]);
            $status='1';
            $zone='';
            if(!( $emp_code!='' && $fname!='' &&   $emailid!='' && $mobile!='' && $dealer_code!=''  ))
            {
                $conn->message(false,$error_text[2]." IN ROW NO.".($Key+1));
            }
            
            //GET EMP CODE
            $query_str = "SELECT `id`  FROM ".DMS_HOUSER." WHERE emp_code='".$emp_code."' LIMIT 1";
            $check_result_arr=$conn->getQueryValue($query_str);
            if(count($check_result_arr)){ $conn->message(false,$error_text[3]." IN ROW NO.".($Key+1)); }     
            
            //GET SMPIN
            $query_str = "SELECT `id`  FROM ".DMS_EMPLOYEE." WHERE smpin='".$emp_code."' LIMIT 1";
            $check_result_arr=$conn->getQueryValue($query_str); 
            if(count($check_result_arr)){ $conn->message(false,$error_text[7]." IN ROW NO.".($Key+1)); }              
             
            $ins_qryarr[] ="(  '".$emp_code."', '".$fname."', '".$lname."','".$dealer_code."' ,'".$rolename."', '".$emailid."', '".$mobile."', '".$zone."',  '".$date_of_join."', '".$status."', '".NOW."', '".NOW."')";
            
        }
    }
    
    if(count($ins_qryarr)>0)
    {
        $insert_str = "INSERT INTO ".DMS_HOUSER." ( `emp_code`, `fname`, `lname`, `dealer_code`,  `role`, `emailid`, `mobile`,`zone`, `date_of_join`, `status`, `created_on`, `modified_on`) VALUES ".@implode(',',$ins_qryarr);
        $insertquery = $conn->query($insert_str);        
        $conn->message(true,$conn->getMessage('en_us','user','success1'));  
    }else
    {
       $conn->message(false,$error_text[4]); 
    }                
}

//DSE IMPORT
function dseimport($import_file) {
    global $conn;
    
    require('excelreader/excel_reader2.php');
    require('excelreader/SpreadsheetReader.php');
    $Spreadsheet = new SpreadsheetReader('../hypermupload/import/'.$import_file);
    $Spreadsheet -> ChangeSheet(0);	
    
                
    $error=0;
    $columncount=8;
    $insertcount=0;
                
    $excelhead="IMPORT USER";
    $exceltitle=array("s.no","smpin","employee code","dealer code","first name","last name","emailid","mobile","date of join");    
    $error_text[1]="INVALID EXCEL";
    $error_text[2]="MISSING FIELDS";
    $error_text[3]="SMPIN ALREADY EXIST";
    $error_text[4]="EMPTY DATA";
    $error_text[5]="SMPIN NOT UNIQUE";
    $error_text[6]="ROLE NOT FOUND";
    $error_text[7]="SMPIN CAN'T USE";
    
    //GET ROLE
    $userrole=escape_str($_REQUEST["roleid"]);
    $query_str = "SELECT `id`, `rolename`  FROM ".TABLE_ROLE." WHERE status=1 AND id='".$userrole."' and id IN (8,9,10,11,12,13,15) LIMIT 1";
    $check_result_arr=$conn->getQueryValue($query_str);    

    if(!count($check_result_arr)){ $conn->message(false,$error_text[6]); }
    $rolename=$check_result_arr[0]->rolename;
    
    $emp_code=array();
    $ins_qryarr=array();
    $smpin_arr=array();
    foreach ($Spreadsheet as $Key => $Row)
    {
        if($Key<=1)	
        {
            if($Key==0)
            {
                if($Row[0]!=$excelhead)
                {									
                    //INVALID EXCEL	
                    $conn->message(false,$error_text[1]);
                    break;
                }
            }else if($Key==1)
            { 
                for($m=0;$m<$columncount;$m++)
                {

                    if(!strtolower($Row[$m])==strtolower($exceltitle[$m]))
                    {
                        $conn->message(false,$error_text[1]);
                        break;										
                    }										
                }                
            }
        }else  
        {
            $i=1;
            $smpin = trim(escape_str($Row[$i++]));
            $emp_code = trim(escape_str($Row[$i++]));
            if(in_array($smpin,$smpin_arr))
            {
                $conn->message(false,$error_text[5]." IN ROW NO.".($Key+1));
            }
            $smpin_arr[]=strtolower($smpin); 
            $dealer_code = escape_str($Row[$i++]);
            $fname = escape_str($Row[$i++]);
            $lname= escape_str($Row[$i++]);
            $emailid = escape_str($Row[$i++]);
            $mobile = escape_str($Row[$i++]);
            $date_of_join = escape_str($Row[$i++]);
            $status='1';
            
            if(!($smpin!='' && $emp_code!='' && $fname!='' &&   $emailid!='' && $mobile!=''  ))
            {
                $conn->message(false,$error_text[2]." IN ROW NO.".($Key+1));
            }
            
            //GET SMPIN
            $query_str = "SELECT `id`  FROM ".DMS_EMPLOYEE." WHERE smpin='".$smpin."' LIMIT 1";
            $check_result_arr=$conn->getQueryValue($query_str); 
            if(count($check_result_arr)){ $conn->message(false,$error_text[3]." IN ROW NO.".($Key+1)); }            
            
            //GET SMPIN
            $query_str = "SELECT `id`  FROM ".DMS_HOUSER." WHERE emp_code='".$smpin."' LIMIT 1";
            $check_result_arr=$conn->getQueryValue($query_str); 
            if(count($check_result_arr)){ $conn->message(false,$error_text[7]." IN ROW NO.".($Key+1)); }        
            
            $ins_qryarr[] ="(  '".$smpin."', '".$emp_code."', '".$fname."', '".$lname."', '".$dealer_code."', '".$rolename."', '".$emailid."', '".$mobile."',  '".$date_of_join."', '".$status."', '".NOW."', '".NOW."')";
            
        }
    }
    
    if(count($ins_qryarr)>0)
    {
		$ids_arr=array_chunk($ins_qryarr,1000);	
		if(count($ids_arr)>0)
		{
			foreach($ids_arr as $groupids)
			{	
				$insert_str = "INSERT INTO ".DMS_EMPLOYEE." (  `smpin`, `emp_code`, `fname`, `lname`, `dealer_code`, `role`, `emailid`, `mobile`,   `date_of_join`, `status`, `created_on`, `modified_on`) VALUES ".@implode(',',$groupids);
				$insertquery = $conn->query($insert_str);  
			}
		}					
        $conn->message(true,$conn->getMessage('en_us','user','success1')); 
    }else
    {
       $conn->message(false,$error_text[4]); 
    }                
}

// INACTIVE USERS LIST
function inactiveuserlist() {
	global $conn;
	try {
        $lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';
        
		$pn = $_REQUEST["pn"];
		$nr = $_REQUEST["nr"];
		$key = escape_str($_REQUEST["key"]);
        //$_SESSION["userid"]=1;
		if(isset($_SESSION["userid"])){
            //$rcond=($_SESSION["role"]==1)?" AND a.role <> 0 ":" AND  a.role>2";
            $rcond=" AND a.role>2";
            $userstr = "SELECT SQL_CALC_FOUND_ROWS u.`userid`, u.`smpin`, u.`emp_code`,u.`fname`,u. `lname`,u. `emailid`,u.`mobileno`, u.`dealer_code` FROM  `".TABLE_USER."` u WHERE u.role>2 AND u.is_deleted='0' AND u.status='2'";
            
			//$userstr = "SELECT SQL_CALC_FOUND_ROWS a.`userid`, a.`smpin`, a.`emp_code`, a.`password`, a.`fname`, a.`lname`, a.`emailid`, a.`mobileno`, a.`role`, a.`dealer_code`, a.`address_1`, a.`address_2`, a.`address_3`,  a.`profile_photo`, a.`dob`, a.`doj`, a.`country`, a.`auth_token`, a.`otp`, a.`otp_expiry`, a.`language`, a.`is_married`, a.`marriage_date`, a.`status`, a.`last_password_change`, a.`is_first_login`, a.`is_profile_updated`, a.`is_deleted`,  b.status as status_name, b.statusid FROM " . TABLE_USER . " a LEFT JOIN (SELECT * FROM " . TABLE_STATUS . ") b ON b.statusid = a.status WHERE a.is_deleted = 0 ".$rcond;
            
			if (!empty($key)) {
				$userstr .= " and ((u.smpin like '%".$key."%') or (u.fname like '%".$key."%') or (u.lname like '%".$key."%') or (u.mobileno like '%".$key."%') OR (u.dealer_code LIKE '%".$key."%') )  ";
			}
			if (!empty($pn) and !empty($nr)) {
				// Navigation
    			$userstr .= " limit " .  (($pn - 1) *  $nr) . ", " . $nr;
			}
        
			$query = $conn->query($userstr);

			// 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;

			$arr['total_records'] = $total_records;
            $userlist=$query->fetchAll(PDO::FETCH_OBJ);
            if(count($userlist)>0)
            {
                foreach($userlist as $mkey=>$res)
                { 
                    //DEALER
                    $check_result_str = "SELECT * FROM `".DMS_DEALER."` WHERE 	dealer_code='".$res->dealer_code."'  LIMIT 1";  
                    $check_result_arr=$conn->getQueryValue($check_result_str); 
                    
                    $userlist[$mkey]->dealer_name=(count($check_result_arr)>0)?$check_result_arr[0]->dealer_name:'';
                
                }
            }
            
			$arr['users'] = $userlist;
			if(count($arr) != 0) {
				$conn->message(true,$conn->getMessage($lang,'user','success1'),$arr );
			}else {
				$conn->message(false,$conn->getMessage($lang,'user','error2'));
			}
		}else {
			$conn->message(false,$conn->getMessage($lang,'user','error1'));
		}
	}
	catch(PDOException $e) {
		$conn->message(false,$conn->getMessage($lang,'user','error4'));
	}	
}
    //ACTIVE USERS UPDATE
function activeuser() {
	global $conn;
	try {
        $lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';
        
		if(isset($_SESSION["userid"]))
        { 
            $userid = escape_str($_REQUEST["userid"]);
            
            $query_str = "UPDATE  ".TABLE_USER." SET status='1' WHERE  userid='".$userid."' AND is_deleted=0 LIMIT 1";
            $conn->executeQuery($query_str);

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

//DEALER IMPORT
function importdealer() {
    global $conn;
    
     
    require('excelreader/excel_reader2.php');
    require('excelreader/SpreadsheetReader.php');
    $Spreadsheet = new SpreadsheetReader('../hypermupload/import/12.xlsx');
    $Spreadsheet -> ChangeSheet(0);	
      
	 
    foreach ($Spreadsheet as $Key => $Row)
    { 
        if($Key>5)	
        {
            $i=1; 
            $zone = escape_str($Row[$i++]);
            $state= escape_str($Row[$i++]);
			$i+=1;
            $city = escape_str($Row[$i++]);
            $dealer_name = escape_str($Row[$i++]);
			$i+=1;
            $dealer_code = escape_str($Row[$i++]); 
            $i+=1;
			$zm_name = escape_str($Row[$i++]);
			$rm_name = escape_str($Row[$i++]);
			$am_name = escape_str($Row[$i++]);
			
			$zm_email = escape_str($Row[$i++]);
			$rm_email = escape_str($Row[$i++]);
			$am_email = escape_str($Row[$i++]);
			
			$zm_mobile = escape_str($Row[$i++]);
			$rm_mobile = escape_str($Row[$i++]);
			$am_mobile = escape_str($Row[$i++]);
			
            $status='1';
             
            
            //GET EMP CODE
            $query_str = "SELECT `id`  FROM ".DMS_DEALER." WHERE dealer_code='".$dealer_code."' LIMIT 1";
            $check_result_arr=$conn->getQueryValue($query_str);

			if(count($check_result_arr)>0)
			{				
				echo $update_query="UPDATE ".DMS_DEALER." SET `dealer_code`= '".$dealer_code."', `dealer_name`='".$dealer_name."', `city`='".$city."', `state`='".$state."', `zone`='".$zone ."', `zm_name`='".$zm_name."', `rm_name`= '".$rm_name."', `am_name`='".$am_name."',  `zm_email`='".$zm_email."',`rm_email`='".$rm_email."', `am_email`='".$am_email ."',`zm_mobile`='".$zm_mobile."',`rm_mobile`='".$rm_mobile."',`am_mobile`= '".$am_mobile."', `modified_on`='".NOW."' WHERE dealer_code='".$dealer_code."'; ";
				
				echo PHP_EOL;
			}else{
				
				
				$ins_qryarr[] ="('".$dealer_code."', '".$dealer_name."','".$city."', '".$state."', '".$zone ."', 'India', '', '".$zm_name."', '".$rm_name."','".$am_name."', '".$zm_email."', '".$rm_email."', '".$am_email ."', '".$zm_mobile."', '".$rm_mobile."', '".$am_mobile."', '1',0, '".NOW."', '".NOW."')";
			}
            
        }
    }
    
    if(count($ins_qryarr)>0)
    {
        echo  $insert_str = "INSERT INTO ".DMS_DEALER." (dealer_code , `dealer_name`, `city`, `state`, `zone`, `country`, `phone`, `zm_name`, `rm_name`, `am_name`, `zm_email`, `rm_email`, `am_email`, `zm_mobile`, `rm_mobile`, `am_mobile`, `status`, `is_deleted`, `created_on`, `modified_on`) VALUES ".@implode(',',$ins_qryarr);
       // $insertquery = $conn->query($insert_str);        
        $conn->message(true,$conn->getMessage('en_us','user','success1'));  
    }else
    {
       $conn->message(false,$error_text[4]); 
    }                
}

?>