opensourcecode/Hyper-M_Admin_Portal/services/excelupload.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 "dsedata": 
		case "dsesalesdata":
        case "dsecertification":
			$fun();     
		break;
        
	}
}
               
//USERS DSE
function dsedata() {
    global $conn;
    $lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';
    try { 
        if(isset($_SESSION["userid"]))
        {
            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_dse_'.date('Ymd')."_".uniqid();			
                $import_file = $conn->uploadFile($obj);     

            }else
            {
               $conn->message(false,$conn->getMessage($lang,'excelupload','error2')); 
            }

            require('excelreader/excel_reader2.php');
            require('excelreader/SpreadsheetReader.php');
            $Spreadsheet = new SpreadsheetReader('../hypermupload/import/'.$import_file);
            $Spreadsheet -> ChangeSheet(0);	

            $update_emp=array();       
            $update_arr=array();
            $ins_arr=array();
            $no_update_smpin=array();
            $log_arr=array();
            $smpin_arr=array();

            $header_arr=array("S.No","SMPIN","EMPLOYEE CODE","DEALER CODE","FIRST NAME","LAST NAME","EMAILID","MOBILE","DATE OF JOIN","SEGMENT");
            foreach ($Spreadsheet as $Key => $Row)
            {
                if($Key<=1)	
                {
                    if($Key==0)
                    {
                        if(!(strpos(strtolower($Row[0]), 'import user' ) !== false)||$Row[0]=='') {
        
                            $conn->message(false,$conn->getMessage($lang,'excelupload','error3')); 
                        }
                    }else
                    {
                        if(count($Row)>=9 || count($Row)>=10)
                        {
                            foreach($Row as $m=>$val)
                            {
                                if($header_arr[$m]!=$val)
                                {
                                    $conn->message(false,$conn->getMessage($lang,'excelupload','error3')); 
                                }
                            }
                        }else
                        {
                            $conn->message(false,$conn->getMessage($lang,'excelupload','error3')); 
                        }
                    }
                }else  
                { //S.No	SMPIN	EMPLOYEE CODE	DEALER CODE	FIRST NAME	LAST NAME	EMAILID	MOBILE	DATE OF JOIN

                    $i=1;
                    $smpin = trim(escape_str($Row[$i++]));             
                    $emp_code = trim(escape_str($Row[$i++]));           
                    $dealer_code = trim(escape_str($Row[$i++])); 
                    $fname = trim(escape_str($Row[$i++])); 
                    $lname = trim(escape_str($Row[$i++])); 
                    $email = trim(escape_str($Row[$i++]));
                    $mobile = trim(escape_str($Row[$i++]));
                    $doj = trim(escape_str($Row[$i++]));
                    $doj = date('Y-m-d',strtotime($doj));

                    $segment_str =(count($Row)>=10)? strtolower(trim(escape_str($Row[$i++]))):'0';
                    switch($segment_str)
                    {
                        case 'Parents/Guardian': $segment='1';
                            break;
                        case 'Instructor':$segment='2';
                            break;
                        default:
                            $segment='0';   
                        break;    
                    }

                    if($smpin!='')
                    {
                        $smpin_arr[]=$smpin;

                        $query_str = "SELECT *  FROM ".DMS_EMPLOYEE." WHERE smpin='".$smpin."' LIMIT 1 ";
                        $result_arr=$conn->getQueryValue($query_str);
                        if(count($result_arr)>0)
                        {
                            if($result_arr[0]->emp_code!=$smpin || $result_arr[0]->fname!=$fname || $result_arr[0]->lname!=$lname || $result_arr[0]->dealer_code!=$dealer_code || $result_arr[0]->mobile!=$mobile || $result_arr[0]->date_of_join!=$doj || $result_arr[0]->segment!=$segment)
                            {
                                $segment_str=($segment==0)?"":",segment='".$segment."'";
                                $update_emp[]=" UPDATE `".DMS_EMPLOYEE."` SET   `emp_code`='".$smpin."', `fname`='".$fname."', `lname`='".$lname."', `dealer_code`= '".$dealer_code."',mobile='".$mobile."',   `date_of_join`='".$doj."' ".$segment_str.", `modified_on`='".NOW."',status=1,is_current=1 WHERE smpin='".$smpin."';";
                            }else
                            {
                                $no_update_smpin[]=$smpin;
                            }

                            $query_str = "SELECT *  FROM ".TABLE_USER." WHERE smpin='".$smpin."' AND role>7 LIMIT 1 ";
                            $user_result_arr=$conn->getQueryValue($query_str);
                            if(count($user_result_arr)>0)
                            {
                                if($user_result_arr[0]->emp_code!=$smpin || $user_result_arr[0]->fname!=$fname || $user_result_arr[0]->lname!=$lname || $user_result_arr[0]->dealer_code!=$dealer_code || $user_result_arr[0]->mobileno!=$mobile || $user_result_arr[0]->doj!=$doj|| $user_result_arr[0]->segment!=$segment)
                                {
                                    $segment_str=($segment==0)?"":",segment='".$segment."'";
                                    $update_arr[]=" UPDATE `".TABLE_USER."` SET   `emp_code`='".$smpin."', `fname`='".$fname."', `lname`='".$lname."', `dealer_code`= '".$dealer_code."',mobileno='".$mobile."', `doj`='".$doj."' ".$segment_str."  WHERE  role>7 AND smpin='".$smpin."';";    
                                }
                            }

                        }else
                        {
                            //$ins_arr[]="`smpin`, `emp_code`, `fname`, `lname`, `dealer_code`, `role`,   `mobile`,   `date_of_join`, `status`, `created_on`, `modified_on`)";                
                            $ins_arr[]=" ('".$smpin."', '".$smpin."',  '".$fname."', '".$lname."', '".$dealer_code."', 'Sales Executive(DSE)','".$mobile."', '".$doj."','".$segment."','1','1', '".NOW."', '".NOW."')";
                        }

                        $log_arr[]=" ('".$smpin."', '".$smpin."',  '".$fname."', '".$lname."', '".$dealer_code."', 'Sales Executive(DSE)','".$mobile."', '".$doj."','".$segment."','1','1', '".NOW."', '".NOW."')";
                    }

                }
            }

            //OLD STATUS
            $update_str=" UPDATE `tbl__dms_employee` SET is_current=0 WHERE is_current=1";
            $conn->executeQuery($update_str);  

            //INSERT EMPLOYEE            
            if(count($ins_arr)>0)
            {
                $insert_chunk=array_chunk($ins_arr, 1000);
                foreach($insert_chunk as $chunk_ins)
                {
                    $insert_str="INSERT INTO `".DMS_EMPLOYEE."`(`smpin`, `emp_code`, `fname`, `lname`, `dealer_code`, `role`,   `mobile`,   `date_of_join`,segment, `status`,is_current, `created_on`, `modified_on`) VALUES ".@implode(',',$chunk_ins);
                    $conn->executeQuery($insert_str);  
                }

            }

            //EMPLOYEE LOG
            if(count($log_arr)>0)
            {
                //DELETE LOG
                $delete_chunk=array_chunk($smpin_arr, 500);
                foreach($delete_chunk as $delete_smpin)
                {
                    $delete_smpin_str = array_map("quotes_array", $delete_smpin);  
                    $delete_str="DELETE FROM ".DMS_EMPLOYEE_LOG." WHERE  DATE_FORMAT(created_on,'%Y-%m-%d')='".CUR_DATE."' AND smpin IN (".@implode(',',$delete_smpin_str).")" ;
                    $conn->executeQuery($delete_str);  
                }

                $insert_chunk=array_chunk($log_arr, 1000);
                foreach($insert_chunk as $chunk_ins)
                {
                    $insert_str="INSERT INTO `".DMS_EMPLOYEE_LOG."`(`smpin`, `emp_code`, `fname`, `lname`, `dealer_code`, `role`,   `mobile`,   `date_of_join`,segment, `status`,is_current, `created_on`, `modified_on`) VALUES ".@implode(',',$chunk_ins);
                    $conn->executeQuery($insert_str);  
                } 
            }

            //UPDATE modified_on
            if(count($no_update_smpin)>0)
            {
                $update_chunk=array_chunk($no_update_smpin, 1000);
                foreach($update_chunk as $chunk_ins)
                {
                    $quotes_smpin = array_map("quotes_array",$chunk_ins);  

                    $update_str=" UPDATE `".DMS_EMPLOYEE."` SET `modified_on`='".NOW."',status=1,is_current=1 WHERE smpin IN (".@implode(',',$quotes_smpin).");";
                    $conn->executeQuery($update_str);  
                }
            }
            $insert_count=count($ins_arr);
            
            
            $query_str = "SELECT smpin FROM `".DMS_EMPLOYEE_LOG."` GROUP BY smpin HAVING COUNT(smpin)>5 " ;
            $log_arr=$conn->getQueryValue($query_str);
            if(count($log_arr)>0)
            {
                foreach($log_arr as $res_log)
                {
                    //DELETE LAST created_on
                    $query_str = "DELETE from ".DMS_EMPLOYEE_LOG." WHERE smpin='".$res_log->smpin."' order by created_on ASC limit 1" ;
                    $conn->executeQuery($query_str);
                }
            }
                        
            //UPDATE EMPLOYEE
            if(count($update_emp)>0)
            {
                foreach($update_emp as $emp)
                {
                    $conn->executeQuery($emp);  
                } 
            }
            $update_count=count($update_emp);
            //UPDATE USER
            if(count($update_arr)>0)
            {
                foreach($update_arr as $user)
                {
                    $conn->executeQuery($user); 
                }
            }
                        
            @unlink("../hypermupload/import/".$import_file);
            
            $arr=array('insert'=>$insert_count,'upload'=>$update_count);
            $conn->message(true,$conn->getMessage($lang,'excelupload','success1'),$arr );
        }else {
            $conn->message(false,$conn->getMessage($lang,'excelupload','error1'));
        }
    }
	catch(PDOException $e) {
		$conn->message(false,$conn->getMessage($lang,'excelupload','error4'));
	}	
}

              
//DSE SALES
function dsesalesdata() {
    global $conn;
    try { 
        $lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';
        if(isset($_SESSION["userid"]))
        {
            if(isset($_FILES['file']) && $_FILES['file']['name'])
            {
                $obj = new stdClass();
                $obj->filekey = 'file';
                $obj->size = '10'; //IN MB
                $obj->type = array('csv');	
                $conn->validateFile($obj);			
                $obj->path = "../hypermupload/import/";
                $obj->filename = 'import_dsesales_'.date('Ymd')."_".uniqid();			
                $import_file = $conn->uploadFile($obj);     

            }else
            {
               $conn->message(false,$conn->getMessage($lang,'excelupload','error2')); 
            }

            $salesdate=escape_str($_REQUEST["salesdate"]);
            if(!$salesdate)
            {
                $conn->message(false,$conn->getMessage($lang,'excelupload','error4'));   
                        
            }
            $salesdate=date('Y-m-',strtotime($salesdate))."01";

            require('excelreader/excel_reader2.php');
            require('excelreader/SpreadsheetReader.php');
            $Spreadsheet = new SpreadsheetReader('../hypermupload/import/'.$import_file);
            $Spreadsheet -> ChangeSheet(0);	

            $update_emp=array();       
            $update_arr=array();
            $ins_arr=array();
            $header_arr=array("SMPIN","MUL Dealer Code","Dealer Name","Model Name","Net Retail as on Date");
            foreach ($Spreadsheet as $Key => $Row)
            {
                if($Key==0)	
                {

                    //heading 
                    if(count($Row)>=4)
                    {
                        foreach($Row as $m=>$val)
                        {
                            if($header_arr[$m]!=$val)
                            {
                                $conn->message(false,$conn->getMessage($lang,'excelupload','error3'));
                            }
                        }
                    }else
                    {
                         $conn->message(false,$conn->getMessage($lang,'excelupload','error3'));
                    }
                }else  
                { //S.No	SMPIN	EMPLOYEE CODE	DEALER CODE	FIRST NAME	LAST NAME	EMAILID	MOBILE	DATE OF JOIN
                    $i=0;
                    $smpin = trim(escape_str($Row[$i++]));             
                    $dealer_code = trim(escape_str($Row[$i++])); 
                    $dealer_name = trim(escape_str($Row[$i++])); 
                    $model = trim(escape_str($Row[$i++])); 
                    $sales_count = trim(escape_str($Row[$i++]));

                    if($smpin!='')
                    {    

                        switch(strtolower($model))
                        {
                            case "access-z":
                            case "access":
                                $model_name="ACCESS 125";
                                break;
                            case "burgman":
                                $model_name="BURGMAN";
                                break;

                            case "gixxer-sf":
                            case "gixxer":
                                $model_name="GIXXER";
                                break;
                            case "gl150":
                                $model_name="INTRUDER";
                                break;     
                            case "hayate":
                            case "let's":
                            case "let\'s":    
                                $model_name="OTHERS";
                                break; 
                            default : $model_name=''; break;
                        }
                        if($model_name==''){continue;}
                        $ins_arr[]=" ('".$smpin."', '".$model_name."', '".$salesdate."', '".$sales_count."', '".NOW."', '".NOW."')";
                    }    

                }
            }


            if(count($ins_arr)>0)
            {

                //DELETE OLD
                $delete_str = "DELETE FROM  ".DMS_EMPLOYEE_SALES." WHERE   `sale_date`='".$salesdate."'";
                $deletequery = $conn->executeQuery($delete_str);

                $query_str = "SELECT `id`  FROM ".DMS_EMPLOYEE_SALES."  ORDER BY id desc  LIMIT 1";
                $result_arr=$conn->getQueryValue($query_str); 

                if(count($result_arr)>0)
                {
                    $autoinc=$result_arr[0]->id+1;
                }else
                {
                    $autoinc=1;
                }
                //AUTOINCREMENT UPDATE
                $conn->executeQuery("ALTER TABLE ".DMS_EMPLOYEE_SALES." auto_increment = ".$autoinc);

                $insert_chunk=array_chunk($ins_arr, 1000);
                foreach($insert_chunk as $chunk_ins)
                {
                    $insert_str= "INSERT INTO ".DMS_EMPLOYEE_SALES."( `smpin`, `model`, `sale_date`, `sales_count`, `created_on`, `modified_on`) VALUES ".@implode(',',$chunk_ins);      
                    $conn->executeQuery($insert_str);                    
                } 
            }
                            
            @unlink("../hypermupload/import/".$import_file); 
            
            $arr=array('insert'=>$ins_arr );
            $conn->message(true,$conn->getMessage($lang,'excelupload','success1'),$arr );            
        }else {
            $conn->message(false,$conn->getMessage($lang,'excelupload','error1'));
        }
    }
	catch(PDOException $e) {
		$conn->message(false,$conn->getMessage($lang,'excelupload','error4'));
	}	
}


            
//DSE CERTIFICATION
function dsecertification() {
    global $conn;
    try { 
        $lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';
        $_SESSION["userid"]=1;
        if(isset($_SESSION["userid"]))
        {
            if(isset($_FILES['file']) && $_FILES['file']['name'])
            {
                $obj = new stdClass();
                $obj->filekey = 'file';
                $obj->size = '30'; //IN MB
                $obj->type = array('xlsx');	
                $conn->validateFile($obj);			
                $obj->path = "../hypermupload/import/";                            
                $obj->filename = 'import_dsecertification_'.date('Ymd')."_".uniqid();			
                $import_file = $conn->uploadFile($obj);     

            }else
            {
               $conn->message(false,$conn->getMessage($lang,'excelupload','error2')); 
            }
                            

            require('excelreader/excel_reader2.php');
            require('excelreader/SpreadsheetReader.php');
            $Spreadsheet = new SpreadsheetReader('../hypermupload/import/'.$import_file);
            $Spreadsheet -> ChangeSheet(0);	

            $smpin_arr=array();                
            $ins_arr=array();
            $ins_qryarr=array();
            $ins_historyarr=array();
            $update_pointarr=array();
            $log_insert=array();
           
            
            $header_arr=array("S.No","SMPIN","CERTIFICATE","FINAL STATUS","SEGMENT");
            foreach ($Spreadsheet as $Key => $Row)
            {
                if($Key==0)	
                {

                    //heading 
                    if(count($Row)>=5)
                    {
                        foreach($Row as $m=>$val)
                        {
                            if(strtolower($header_arr[$m])!=strtolower($val))
                            {
                                $conn->message(false,$conn->getMessage($lang,'excelupload','error3'));
                            }
                        }
                    }else
                    {
                         $conn->message(false,$conn->getMessage($lang,'excelupload','error3'));
                    }
                }else  
                { //"S.No","SMPIN","CERTIFICATE","FINAL STATUS","SEGMENT"
                    
                    if(!count($Row)==5)
                    {
                        $conn->message(false,$conn->getMessage($lang,'excelupload','error3'));
                    }
                    
                    $i=1;
                    $smpin = trim(escape_str($Row[$i++]));             
                    $certificate = trim(escape_str($Row[$i++])); 
                    $certificate_status = trim(escape_str($Row[$i++])); 

                    if($smpin!='')
                    {    
                        //CHECK SMPIN ALREDY EXIST
                        if(in_array($smpin, $smpin_arr))
                        {
                           $conn->message(false, "SMPIN REPEATING IN ROW NO.".($Key+1).".");
                        }
                        $smpin_arr[]=$smpin;
                        
                        //certificate STRING CHECK
                        if(!in_array(strtolower($certificate_status),array('certified','not certified','not-certified','de-certified','de certified')))
                        {
                            $conn->message(false, "INVALID STATUS IN ROW NO.".($Key+1).".");
                        }    
                        $c_status = (strtolower($certificate_status)=='certified')?"3":"2";
                        if($c_status=='3')
                        {
                            //SMPIN COMPLETED CERTIFICATION
                            $smpin_arr[]="'".$smpin."'";
                            switch(strtolower($certificate))
                            {
                                case 'base': 
                                    $ins_qryarr[]=" ('".$smpin."','1', '".$c_status."','0','".NOW."', '".NOW."')"; 
                                    $ins_qryarr[]=" ('".$smpin."','2', '0','1','".NOW."', '".NOW."')"; 
                                    //LOG
                                    $log_insert[]=" ('".$smpin."',1, 1,'".NOW."', '".NOW."')"; 
                                    break;
                                case 'bronze':
                                    $ins_qryarr[]=" ('".$smpin."','1', '".$c_status."','0','".NOW."', '".NOW."')"; 
                                    $ins_qryarr[]=" ('".$smpin."','2', '".$c_status."','0','".NOW."', '".NOW."')"; 
                                    $ins_qryarr[]=" ('".$smpin."','3', '0','1','".NOW."', '".NOW."')"; 
                                    //LOG
                                    $log_insert[]=" ('".$smpin."',2, 1,'".NOW."', '".NOW."')"; 
                                    $query_str = "SELECT `eid`  FROM ".TABLE_USER_EPOINTS." WHERE smpin='".$smpin."'";
                                    $result_arr=$conn->getQueryValue($query_str); 
                                    if(count($result_arr)>0)
                                    {
                                       $history_str =  "SELECT * FROM ".TABLE_USER_EPOINTS_HISTORY." WHERE smpin='".$smpin."' AND type='reward_bronze' LIMIT 1";
                                       $history_arr=$conn->getQueryValue($history_str);  
                                       
                                       if(count($history_arr)>0)
                                       {
                                           
                                       }else
                                       {
                                            $update_pointarr[]=" UPDATE ".TABLE_USER_EPOINTS." SET points=points+".REWARD_POINTS_BRONZE." WHERE smpin='".$smpin."' "; 
                                            $ins_historyarr[]="( '".$smpin."','reward_bronze','0','Points for completion of Bronze Level - Learning path','".REWARD_POINTS_BRONZE."','C','".NOW."', '".NOW."' )";
                                        }
                                    }else
                                    {
                                        $ins_pointarr[]="( '".$smpin."','".REWARD_POINTS_BRONZE."','".NOW."', '".NOW."' )";
                                    }
                                   
                                    break;
                                case 'silver':
                                    $ins_qryarr[]=" ('".$smpin."','1', '".$c_status."','0','".NOW."', '".NOW."')";
                                    $ins_qryarr[]=" ('".$smpin."','2', '".$c_status."','0','".NOW."', '".NOW."')";
                                    $ins_qryarr[]=" ('".$smpin."','3', '".$c_status."','0','".NOW."', '".NOW."')"; 
                                    $ins_qryarr[]=" ('".$smpin."','4', '0','1','".NOW."', '".NOW."')"; 
                                    //LOG
                                    $log_insert[]=" ('".$smpin."',3, 1,'".NOW."', '".NOW."' )"; 
                                    $query_str = "SELECT `eid`  FROM ".TABLE_USER_EPOINTS." WHERE smpin='".$smpin."'";
                                    $result_arr=$conn->getQueryValue($query_str); 
                                    if(count($result_arr)>0)
                                    {
                                        $history_str =  "SELECT * FROM ".TABLE_USER_EPOINTS_HISTORY." WHERE smpin='".$smpin."' AND type='reward_silver' LIMIT 1";
                                        $history_arr=$conn->getQueryValue($history_str);  

                                        if(count($history_arr)>0)
                                        {

                                        }else
                                        {
                                            $update_pointarr[]=" UPDATE ".TABLE_USER_EPOINTS." SET points=points+".REWARD_POINTS_SILVER." WHERE smpin='".$smpin."' ";
                                            $ins_historyarr[]=" ('".$smpin."','reward_silver','0','Points for completion of Silver Level - Learning path','".REWARD_POINTS_SILVER."','C','".NOW."', '".NOW."' )";
                                        }
                                    }else
                                    {
                                        $ins_pointarr[]="( '".$smpin."','".REWARD_POINTS_SILVER."','".NOW."', '".NOW."' )";
                                    }
                                    
                                    break;
                                case 'gold':
                                    $ins_qryarr[]=" ('".$smpin."','1', '".$c_status."','0','".NOW."', '".NOW."')";
                                    $ins_qryarr[]=" ('".$smpin."','2', '".$c_status."','0','".NOW."', '".NOW."')";
                                    $ins_qryarr[]=" ('".$smpin."','3', '".$c_status."','0','".NOW."', '".NOW."')"; 
                                    $ins_qryarr[]=" ('".$smpin."','4', '".$c_status."','0','".NOW."', '".NOW."')"; 
                                    
                                    $log_insert[]="( '".$smpin."',4, 1,'".NOW."', '".NOW."' )"; 
                                    $query_str = "SELECT `eid`  FROM ".TABLE_USER_EPOINTS." WHERE smpin='".$smpin."'";
                                    $result_arr=$conn->getQueryValue($query_str); 
                                    if(count($result_arr)>0)
                                    {
                                        $history_str =  "SELECT * FROM ".TABLE_USER_EPOINTS_HISTORY." WHERE smpin='".$smpin."' AND type='reward_gold' LIMIT 1";
                                        $history_arr=$conn->getQueryValue($history_str);  

                                        if(count($history_arr)>0)
                                        {

                                        }else
                                        {
                                            $update_pointarr[]=" UPDATE ".TABLE_USER_EPOINTS." SET points=points+".REWARD_POINTS_GOLD." WHERE smpin='".$smpin."' ";
                                            $ins_historyarr[]=" ('".$smpin."','reward_gold','0','Points for completion of Gold Level - Learning path','".REWARD_POINTS_GOLD."','C','".NOW."', '".NOW."' )";
                                        }
                                    }else
                                    {
                                        $ins_pointarr[]=" ('".$smpin."','".REWARD_POINTS_GOLD."','".NOW."', '".NOW."' )";
                                    }
                                   
                                    break;
                                default:
                                    $conn->message(false, "INVALID CERTIFICATE IN ROW NO.".($Key+1).".");
                                    break;
                            }
                        }else
                        {
                            $lvl_id=0;
                            switch(strtolower($certificate))
                            {
                                case 'base': 
                                    $lvl_id=1;
                                    break;
                                case 'bronze':
                                    $lvl_id=2;
                                    break;
                                case 'silver':
                                    $lvl_id=3;
                                    break;
                                case 'gold':
                                    $lvl_id=4;
                                    break;
                                default:
                                    $conn->message(false, "INVALID CERTIFICATE IN ROW NO.".($Key+1).".");
                                    break;
                            }
                            
                            //FAILED IN CERTIFICATION 
                            $log_insert[]="( '".$smpin."','".$lvl_id."', 0,'".NOW."', '".NOW."' )";
                        }
                            
                    }    

                }
            }
                                
            //DELETE CURRENT LEARNING
            if(count($smpin_arr)>0)
            { 
               $smpin_chunk=array_chunk($smpin_arr, 1000);
               foreach($smpin_chunk as $delete_smpin)
               {
                   $conn->executeQuery("DELETE FROM ".TABLE_USER_LEARNING." WHERE smpin IN (".@implode(',',$delete_smpin).")");
               }
            }
            
            //INSERT LEARNING
            if(count($ins_qryarr)>0)
            {
                $insert_chunk=array_chunk($ins_qryarr, 1000);
                foreach($insert_chunk as $chunk_ins)
                {
                  $conn->executeQuery("INSERT INTO ".TABLE_USER_LEARNING." ( `smpin`, `lvl_id`, `status`, `current_status`, `created_on`, `modified_on`) VALUES ".@implode(',',$chunk_ins));
                                
                }              
            }
            //INSERT POINTS
            if(count($ins_pointarr)>0)
            { 
                $insert_chunk=array_chunk($ins_pointarr, 1000);
                foreach($insert_chunk as $chunk_ins)
                {
                    $conn->executeQuery("INSERT INTO ".TABLE_USER_EPOINTS."  ( `smpin`, `points`, `created_on`, `modified_on`) VALUES ".@implode(',',$chunk_ins));             } 
            }
            
            //INSERT HISTROY
            if(count($ins_historyarr)>0)
            { 
                $insert_chunk=array_chunk($ins_historyarr, 1000);
                foreach($insert_chunk as $chunk_ins)
                {
                    $conn->executeQuery("INSERT INTO ".TABLE_USER_EPOINTS_HISTORY." ( `smpin`, `type`, `type_id`, `description`, `points`, `transaction_type`, `created_on`, `modified_on`) VALUES ".@implode(',',$chunk_ins));
                    
                } 
            }
            
            //UPDATE POINTS
            if(count($update_pointarr)>0)
            {                

                foreach($update_pointarr as $val)
                {
                   $conn->executeQuery($val);
                }              
            } 
            
            //INSERT LOG
            if(count($log_insert)>0)
            {
               $insert_chunk=array_chunk($log_insert, 1000);
               foreach($insert_chunk as $chunk_ins)
               {
                   $conn->executeQuery("INSERT INTO ".TABLE_USER_LEARNING_UPLOADLOG." ( `smpin`, `lvl_id`, `status`, `created_on`, `modified_on`) VALUES ".@implode(',',$chunk_ins));
               } 
            }            
            
            @unlink("../hypermupload/import/".$import_file);             
            $arr=array('insert'=>$ins_arr );
            $conn->message(true,$conn->getMessage($lang,'excelupload','success1'),$arr );            
        }else {
            $conn->message(false,$conn->getMessage($lang,'excelupload','error1'));
        }
    }
	catch(PDOException $e) {
		$conn->message(false,$conn->getMessage($lang,'excelupload','error4'));
	}	
}
?>