opensourcecode/Hyper-M_Admin_Portal/services/question.php

<?php
include "Connection.php";
$conn = new Connection();

if(isset($_REQUEST["funName"])) {
	$db = $conn->getConnection();
	$fun = $_REQUEST["funName"];
	switch($fun) {
        
		case "questionlist":
        case "addquestion":
        case "editquestion":   
        case "deletequestion":
        case "questionoptionlist":     
        case "questiondetails":               
			$fun();          
		break;
        
	}
}
        

// QUESTION LIST
function questionlist() {
	global $conn;
	try{
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';
		//$pageData = $conn->loadJSON($lang);
		$pn = escape_str($_REQUEST["pn"]);
		$nr = escape_str($_REQUEST["nr"]);
		$key = escape_str($_REQUEST["key"]);        
        $cateid = escape_str($_REQUEST['cateid']);
        
        
		if(isset($_SESSION["userid"])) {
            
            // Search
            if (!empty($key)) {
				$econd= " AND ((a.questiontext like '%".$key."%')  OR (f.questiontype like '%$key%') ) ";
			}
            
            if ($cateid!='') {
				$econd.= " and  a.cateid='".$cateid."'";
			}
            
            $certified = escape_str($_REQUEST['certified']);
            if ($certified!='') {
				$econd.= " and  a.cateid>'0'";
			}
            
            $tag = escape_str($_REQUEST['tag']);
            if ($tag!='') {
				$econd.= " and  a.tag='".$tag."'";
			}
            
			$selectstr = "SELECT SQL_CALC_FOUND_ROWS a.id,a.id as qid,a.cateid,a.questiontypeid, a.tag, a.`questiontext`, a.`qimage`, a.`qvideo`, a.`creatorid`, a.`status`,f.questiontype FROM ".TABLE_QUESTION." a INNER JOIN " . TABLE_QUESTIONTYPE . " f ON f.id = a.questiontypeid WHERE 1=1 ".$econd." ORDER BY a.id DESC";
        

			// Pagination
			if (!empty($pn) and !empty($nr)) {
				// Navigation
    			$selectstr .= " limit " .  (($pn - 1) *  $nr) . ", " . $nr;
			}
    		$query = $conn->query($selectstr);

			// 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;
            
            $question_arr=$query->fetchAll(PDO::FETCH_OBJ);
            
            if(count($question_arr)>0)
            {
                require_once  (dirname(__FILE__).'/awsfileserver.php'); 
                $s3=awsfileserver::connectS3();
                foreach($question_arr as $qkey=>$res_ques)
                {
                    $check_ques_str = "SELECT * FROM ".TABLE_ASSESSMENT_QUESTION." WHERE qid = '".$res_ques->id."' LIMIT 1";
                    $check_ques_query = $conn->query($check_ques_str);                    
                    $question_arr[$qkey]->is_edit=($check_ques_query->rowCount()>0)?"0":"1";
                    
                    $image_url='';
                    if($res_ques->qimage!='')
                    { 
                        $obj = new stdClass();
                        $obj->key_name=S3_QUESTION_URL.$res_ques->qimage;			
                        $url=awsfileserver::getS3Url($s3,S3_BUCKET,$obj);	
                        $image_url=$url;
                    }
                    $question_arr[$qkey]->qimage_url=$image_url;
                    
                    $video_url='';
                    if($res_ques->qvideo!='')
                    { 
                        $obj = new stdClass();
                        $obj->key_name=S3_QUESTION_URL.$res_ques->qvideo;			
                        $url=awsfileserver::getS3Url($s3,S3_BUCKET,$obj);	
                        $video_url=$url;
                    }
                    $question_arr[$qkey]->qvideo_url=$video_url;
                    
                    switch($res_ques->questiontypeid)
                    {
                        case '1':
                        case '2': 
                            $question_arr[$qkey]->type='1';
                            break;
                        case '3':    
                        case '4': 
                        case '5': 
                        case '6': 
                            $question_arr[$qkey]->type='2';
                            break;
                    }
                        
                }
            }

			$arr['total_records'] = $total_records;
			$arr['questions'] = $question_arr;
        

			if(count($arr) != 0)
            {        
				$conn->message(true,$conn->getMessage($lang,'question','success1'),$arr );
			}else{
				$conn->message(false,$conn->getMessage($lang,'question','error3'));
			}
		}else{
			$conn->message(false,$conn->getMessage($lang,'question','error1'));
		}
	}catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'question','error4'));
	}	
}

// ADD QUESTION
function addquestion() {
	global $conn;
	try {
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';	
                        
		if(isset($_SESSION["userid"])){
            
			$userid = $_SESSION["userid"];        
			$questiontext = escape_str($_REQUEST["questiontext"]);
			$questiontypeid = escape_str($_REQUEST["questiontypeid"]);
            $cateid= (isset($_REQUEST["cateid"]))?escape_str($_REQUEST["cateid"]):'0';
            $tag = escape_str($_REQUEST["tag"]);
            
            if(!($questiontext!='' && $questiontypeid!='' && $tag!='')) $conn->message(false,"Data Missing"); 
            
			$childs = (isset($_REQUEST["child"]) && in_array($questiontypeid,array('1','2','5','6')))?json_decode($_REQUEST["child"]):array();            
            //$supplierid = (in_array($questiontypeid,array('1','2')))?$supplierid:0;
            
            switch($questiontypeid) 
            {
               case "1":
               case "2":
               case "5":
               case "6":    
                   if(!(count($childs)>0 && is_array($childs))){ $conn->message(false,$conn->getMessage($lang,'question','error5')); }
                   
                   $is_correct=0;
                   if(count($childs)>0)
                   {
                        for($n = 0;$n < count($childs);$n++)
                        {
                            if(isset($childs[$n]->iscorrect) && $childs[$n]->iscorrect=='1'){ $is_correct=1; }                            
                        }
                        if( $is_correct=='0' && $questiontypeid<5){  $conn->message(false,$conn->getMessage($lang,'question','error6')); }
                   }
               break;  
               case "3":
               case "4":
               break; 
            }            
			
			$insertstr = "INSERT INTO ".TABLE_QUESTION." (  questiontext, questiontypeid,cateid,tag,status, creatorid, created_on, modified_on) VALUES('".$questiontext."', '".$questiontypeid."','".$cateid."','".$tag."', '1', '".$userid."', '".NOW."', '".NOW."')";
			$insertquery = $conn->query($insertstr);
			$insertid = $conn->lastinsetid();
        
			// FILE UPLOAD
            if(isset($_FILES['file']) && $_FILES['file']['name']) {
            	// S3 UPLOAD
	            require_once (dirname(__FILE__).'/awsfileserver.php'); 

            	$file_name = $_FILES['file']['name'];
            	$fileParts  = pathinfo($file_name);	
				$fileExtension = strtolower($fileParts['extension']);			
				$filetype = $fileExtension;

                $obj = new stdClass();
                $obj->filekey = 'file';
                //S3 OBJ
                $s3 = awsfileserver::connectS3();
				if($filetype != 'mp4')
                {
	                $obj->size = '10'; //IN MB
	                $obj->type = array('jpg','jpeg','png');	
	                $conn->validateFile($obj);			
	                $obj->path = "../".S3_QUESTION_URL;
	                $obj->filename = 'question_image_'.date('Ymd')."_".uniqid();			
	                $qimage = $conn->uploadFile($obj);
	                
	                awsfileserver::checkupload($s3,S3_BUCKET,S3_QUESTION_URL.$qimage,dirname(__FILE__)."/../".S3_QUESTION_URL.$qimage);	                
	                //DELETE AFTER MOVE TO S3
	                @unlink(dirname(__FILE__).'/../'.S3_QUESTION_URL.$qimage);
                    
	                $updatestr = "UPDATE ".TABLE_QUESTION." SET qimage = '".$qimage."' WHERE id='".$insertid."'";					
				}
				else {
                    $obj->size = '512';
                    $obj->type = array('mp4');	
	                $conn->validateFile($obj);			
	                $obj->path = "../".S3_QUESTION_URL;
	                $obj->filename = 'question_video_'.date('Ymd')."_".uniqid();			
	                $qvideo = $conn->uploadFile($obj);
               
	                awsfileserver::checkupload($s3,S3_BUCKET,S3_QUESTION_URL.$qvideo,dirname(__FILE__)."/../".S3_QUESTION_URL.$qvideo);	                
	                //DELETE AFTER MOVE TO S3
	                @unlink(dirname(__FILE__).'/../'.S3_QUESTION_URL.$qvideo);
                    
	                $updatestr = "UPDATE ".TABLE_QUESTION." SET qvideo = '".$qvideo."' WHERE id='".$insertid."'";					
				}
                $updatequery = $conn->query($updatestr);
            }

			// Question options data inserting		
            if((in_array($questiontypeid,array('1','2','5','6'))))
            {
                if($childs) {
                    for($id = 0;$id < count($childs);$id++){
               
                        $iscorrect = (isset($childs[$id]->iscorrect) && $childs[$id]->iscorrect=='1')?"1":"0";
                        $opttext = escape_str($childs[$id]->opttext);
                        $score = $childs[$id]->score;
               
                        $choiceinsert_arr[] = " ('".$insertid."','".$opttext."','".$score."','".$iscorrect."','".$userid."','".NOW."','".NOW."')";
                    }

                    $choiceinsertstr = "INSERT INTO ".TABLE_QUESTIONOPTION." (qid, opttext, score, iscorrect, creatorid, created_on, modified_on) VALUES ".@implode(',',$choiceinsert_arr);
                    $choiceinsertquery = $conn->query($choiceinsertstr);
                }
            }
			
			$conn->message(true,$conn->getMessage($lang,'question','success1'),getQuestion ($insertid));
			
		}
		else {
			$conn->message(false,$conn->getMessage($lang,'question','error1'));
		}
	}catch(PDOException $e){
        echo $e;
		$conn->message(false,$conn->getMessage($lang,'question','error4'));
	}	
}

// UPDATE QUESTION
function editquestion() {
	global $conn;
	try {
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';	
        
		if(isset($_SESSION["userid"])){
            
			$id = escape_str($_REQUEST["id"]);
			$userid = $_SESSION["userid"];     
            //$supplierid = $_REQUEST["supplierid"];
			$questiontext = escape_str($_REQUEST["questiontext"]);
			$questiontypeid = escape_str($_REQUEST["questiontypeid"]);			
			$childs = (isset($_REQUEST["child"]))?json_decode($_REQUEST["child"]):array();
			$cateid= (isset($_REQUEST["cateid"]))?escape_str($_REQUEST["cateid"]):'0';
            $tag = escape_str($_REQUEST["tag"]);
               
            if(!($questiontext!='' && $questiontypeid!='' && $tag!='' && $id!='')) $conn->message(false,"Data Missing"); 
            
            switch($questiontypeid)
            {
                case "1":
                case "2":
                case "5":
                case "6":  
                   if(!(count($childs)>0 && is_array($childs))){ $conn->message(false,$conn->getMessage($lang,'question','error5'));}
                   
                   $is_correct=0;
                   if(count($childs)>0)
                   {
                        for($n = 0;$n < count($childs);$n++)
                        {
                            if(isset($childs[$n]->iscorrect) && $childs[$n]->iscorrect=='1'){ $is_correct=1; }
                            
                        }
                        if( $is_correct=='0' && $questiontypeid<5){ $conn->message(false,$conn->getMessage($lang,'question','error6'));}
                   }
               break;  
               case "3":
               case "4":
               break; 
            }
            
            
            $check_ques_str = "SELECT * FROM ".TABLE_QUESTION." WHERE id = '".$id."' LIMIT 1";
			$check_ques_arr=$conn->getQueryValue($check_ques_str);
            
            if(!(count($check_ques_arr)>0)) $conn->message(false,$conn->getMessage($lang,'question','error9')); 

			// FILE UPLOAD
            if(isset($_FILES['file']) && $_FILES['file']['name']) {
            	// S3 UPLOAD
	            require_once (dirname(__FILE__).'/awsfileserver.php'); 

            	$file_name = $_FILES['file']['name'];
            	$fileParts  = pathinfo($file_name);	
				$fileExtension = strtolower($fileParts['extension']);			
				$filetype = $fileExtension;

                $obj = new stdClass();
                $obj->filekey = 'file';
                
                $s3 = awsfileserver::connectS3();
				if($filetype != 'mp4') {
	                $obj->size = '10'; //IN MB
	                $obj->type = array('jpg','jpeg','png');	
	                $conn->validateFile($obj);			
	                $obj->path = "../".S3_QUESTION_URL;
	                $obj->filename = 'question_image_'.date('Ymd')."_".uniqid();			
	                $qimage = $conn->uploadFile($obj);
	                
	                awsfileserver::checkupload($s3,S3_BUCKET,S3_QUESTION_URL.$qimage,dirname(__FILE__)."/../".S3_QUESTION_URL.$qimage);	                
	                //DELETE AFTER MOVE TO S3
	                @unlink(dirname(__FILE__).'/../'.S3_QUESTION_URL.$qimage);
	                $updatestr = "UPDATE ".TABLE_QUESTION." SET qimage = '".$qimage."' WHERE id='".$id."'";					
                    
                    if($check_ques_arr[0]->qimage!='')
                    {
                        //awsfileserver::deleteobject($s3,S3_BUCKET,S3_QUESTION_URL.$check_ques_arr[0]->qimage);
                    }                    
				}
				else
                {
                    $obj->size = '512';
                    $obj->type = array('mp4');	
	                $conn->validateFile($obj);			
	                $obj->path = "../".S3_QUESTION_URL;
	                $obj->filename = 'question_video_'.date('Ymd')."_".uniqid();			
	                $qvideo = $conn->uploadFile($obj);
                
	                awsfileserver::checkupload($s3,S3_BUCKET,S3_QUESTION_URL.$qvideo,dirname(__FILE__)."/../".S3_QUESTION_URL.$qvideo);
	                
	                //DELETE AFTER MOVE TO S3
	                @unlink(dirname(__FILE__).'/../'.S3_QUESTION_URL.$qvideo);
	                $updatestr = "UPDATE ".TABLE_QUESTION." SET qvideo = '".$qvideo."' WHERE id='".$id."'";		
                    
                    if($check_ques_arr[0]->qvideo!='')
                    {
                        //awsfileserver::deleteobject($s3,S3_BUCKET,S3_QUESTION_URL.$check_ques_arr[0]->qvideo);
                    }
				}
                $updatequery = $conn->query($updatestr);
            }
			
			$upddate_str = "UPDATE ".TABLE_QUESTION." SET cateid='".$cateid."',questiontext='".$questiontext."',tag='".$tag."',questiontypeid='".$questiontypeid."', creatorid='".$userid."', modified_on = '".NOW."' ".$updatecond." WHERE id='".$id."'";
			$insertquery = $conn->query($upddate_str);
            
            //DELETE OPTIONS
			$deleteoptstr = "DELETE FROM ".TABLE_QUESTIONOPTION." WHERE qid = '".$id."'";			
			$deleteoptquery = $conn->query($deleteoptstr); 
            $qid=$id;
            if((in_array($questiontypeid,array('1','2','5','6'))))
            {
                if($childs) {
                    for($id = 0;$id < count($childs);$id++){
                       $iscorrect = (isset($childs[$id]->iscorrect)&&  $childs[$id]->iscorrect=='1')?"1":"0";
                       $opttext = escape_str($childs[$id]->opttext);
                       $score = $childs[$id]->score;
                
                        $choiceinsert_arr[] = " ('".$qid."','".escape_str($opttext)."','".$score."','".$iscorrect."','".$userid."','".NOW."','".NOW."')";
                    }

                    $choiceinsertstr = "INSERT INTO ".TABLE_QUESTIONOPTION." (qid, opttext, score, iscorrect, creatorid, created_on, modified_on) VALUES ".@implode(',',$choiceinsert_arr);
                    $choiceinsertquery = $conn->query($choiceinsertstr);
                }
            }
			
            $conn->message(true,$conn->getMessage($lang,'question','success1'),getQuestion ($qid));
		}else{
			$conn->message(false,$conn->getMessage($lang,'question','error1'));
		}
	}catch(PDOException $e){
		$conn->message(false,$conn->getMessage($lang,'question','error4'));
	}	
}

// DELETE QUESTION
function deletequestion() {
	global $conn;
	try {
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';
		if(isset($_SESSION["userid"]))
        {
			$userid = $_SESSION["userid"];
			$id = escape_str($_REQUEST["id"]);
            
            
			$selectassessstr = "SELECT * FROM ".TABLE_ASSESSMENT_QUESTION." WHERE qid='".$id."' LIMIT 1";
			$selectassessquesy = $conn->query($selectassessstr);
			if($selectassessquesy->rowCount() == 0)
            {
				$selectquest = "SELECT * FROM ".TABLE_ASSESSMENT_USERRESULT." WHERE qid='".$id."'";
				$selectquery = $conn->query($selectquest);
				if($selectquery->rowCount() == 0) {
					$delete1 = $conn->query("DELETE FROM ".TABLE_QUESTION." WHERE id='".$id."'");
					$selectstr = "SELECT * FROM ".TABLE_QUESTIONOPTION." WHERE qid='".$id."'";
					$selectquery = $conn->query($selectstr);
					$arr = $selectquery->fetchAll(PDO::FETCH_OBJ);
					$conn->message(true,$conn->getMessage($lang,'question','success1') );
				}
				else{
					$conn->message(false,$conn->getMessage($lang,'question','error8'));
				}
                
                $ques_arr=$conn->getQueryValue($selectassessstr);
                //S3 IMAGE REMOVE
                $s3 = awsfileserver::connectS3();
                if($ques_arr[0]->qimage!='')
                {
                   // awsfileserver::deleteobject($s3,S3_BUCKET,S3_QUESTION_URL.$ques_arr[0]->qimage);
                }  
                //S3 VIDEO REMOVE
                if($ques_arr[0]->qvideo!='')
                {
                    //awsfileserver::deleteobject($s3,S3_BUCKET,S3_QUESTION_URL.$ques_arr[0]->qvideo);
                } 
			}else{
				$conn->message(false,$conn->getMessage($lang,'question','error7'));
			}
			
		}else{
			$conn->message(false,$conn->getMessage($lang,'question','error1'));
		}
	}
	catch(PDOException $e) {
		$conn->message(false,$conn->getMessage($lang,'question','error4'));
	}	
}

// Question Option List
function questionoptionlist() {
	global $conn;
	try {
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';
         
		if(isset($_SESSION["userid"]))
        {                 
			$id = escape_str($_REQUEST["id"]);
			$selectstr = "SELECT * FROM " . TABLE_QUESTIONOPTION . " WHERE qid='".$id."'";
			$selectquery = $conn->query($selectstr);
			$arr = $selectquery->fetchAll(PDO::FETCH_OBJ);
            
            if(!count($arr)>0)
            {
                $arr=array();
            }
            $conn->message(true,$conn->getMessage($lang,'question','success1'),$arr );			
		}
		else {
			$conn->message(false,$conn->getMessage($lang,'question','error1'));
		}
	}
	catch(PDOException $e) {
		$conn->message(false,$conn->getMessage($lang,'question','error4'));
	}	
}

// GET QUESTION
function getQuestion($qid)
{
	global $conn;
    $data_arr=array();
    if($qid!='')
    {
        require_once (dirname(__FILE__).'/awsfileserver.php'); 
        $s3=awsfileserver::connectS3();
        
        $data_arr=$conn->getQueryValue( "SELECT a.id,a.id as qid,a.questiontypeid,a.cateid, a.tag,a.`questiontext`, a.`qimage`, a.`qvideo`, a.`creatorid`, a.`status`,f.questiontype FROM ".TABLE_QUESTION." a INNER JOIN " . TABLE_QUESTIONTYPE . " f ON f.id = a.questiontypeid WHERE a.id='".$qid."' LIMIT 1");
        
        if(count($data_arr)>0)
        {
            $check_ques_str = "SELECT * FROM ".TABLE_ASSESSMENT_QUESTION." WHERE qid = '".$qid."' LIMIT 1";
            $check_ques_query = $conn->query($check_ques_str);                    
            $data_arr[0]->is_edit=($check_ques_query->rowCount()>0)?"0":"1";
        }
        
        $image_url='';
        if($data_arr[0]->qimage!='')
        { 
            $obj = new stdClass();
            $obj->key_name=S3_QUESTION_URL.$data_arr[0]->qimage;			
            $url=awsfileserver::getS3Url($s3,S3_BUCKET,$obj);	
            $image_url=$url;
        }
        $data_arr[0]->qimage_url=$image_url;

        $video_url='';
        if($data_arr[0]->qvideo!='')
        { 
            $obj = new stdClass();
            $obj->key_name=S3_QUESTION_URL.$data_arr[0]->qvideo;			
            $url=awsfileserver::getS3Url($s3,S3_BUCKET,$obj);	
            $video_url=$url;
        }
        $data_arr[0]->qvideo_url=$video_url;
        
        $data_arr=(count($data_arr)>0)?$data_arr[0]:array();
    }
    return $data_arr;
}


// Question 
function questiondetails() {
	global $conn;
	try {
		$lang = (isset($_REQUEST["lang"]))?$_REQUEST["lang"]:'en_us';
         
		if(isset($_SESSION["userid"]))
        {                 
			$id = escape_str($_REQUEST["id"]);
            
            $selectstr = "SELECT a.id,a.id as qid,a.cateid,a.questiontypeid, a.tag, a.`questiontext`, a.`qimage`, a.`qvideo`, a.`creatorid`, a.`status`,f.questiontype FROM ".TABLE_QUESTION." a INNER JOIN " . TABLE_QUESTIONTYPE . " f ON f.id = a.questiontypeid WHERE a.id='".$id."' LIMIT 1"; 
    		$query = $conn->query($selectstr);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);
            
            if(count($arr)>0)
            {
                $selectstr = "SELECT * FROM " . TABLE_QUESTIONOPTION . " WHERE qid='".$id."'";
                $selectquery = $conn->query($selectstr);
                $options = $selectquery->fetchAll(PDO::FETCH_OBJ);
                $arr[0]->options=(count($options)>0)?$options:array();
            }else
            {
                $arr=array();
            }
            $conn->message(true,$conn->getMessage($lang,'question','success1'),$arr );			
		}
		else {
			$conn->message(false,$conn->getMessage($lang,'question','error1'));
		}
	}
	catch(PDOException $e) {
		$conn->message(false,$conn->getMessage($lang,'question','error4'));
	}	
}


?>