<?php  
 
/* <summary>
	 Class contains methods related to state module
	 <author>
	 Beatle Buddy 2017.
	 </author>
	 Version 1.0
   </summary>
*/
 
class include_performancelogmonthly
{
	/*
	<summary>
		Default constructor
	</summary>	
	*/
	
	function __CONSTRUCT(){
	
	}


	/////////////new report start///////////////
	public function fetchStation($stationId = 0){
		global $objDB;
		
		if($stationId != 0)
		$SQL = "SELECT * FROM baris_station WHERE stationId = ".$stationId;
		else
		$SQL = "SELECT * FROM baris_station where OrgId = ".$_SESSION['UserInfo']['OrgID'];
		
		$rsStaInfo = $objDB->sql_query($SQL);
		return $rsStaInfo;		
	}

	public function FetchQuestion($queId = 0){
		global $objDB;
		
		if($queId != 0)
		$SQL = "SELECT * FROM baris_question WHERE queId = ".$queId;
		else
		$SQL = "SELECT * FROM baris_question";
		
		$rsQueInfo = $objDB->sql_query($SQL);
		return $rsQueInfo;		
	}

	public function FetchAuditor($userId = 0){
		global $objDB;
		
		if($userId != 0)
		$SQL = "SELECT * FROM baris_userlogin WHERE db_usertype = 'auditor' and userId = ".$userId;
		else
		$SQL = "SELECT * FROM baris_userlogin where db_usertype = 'auditor' and reportType like '%WAT%' and OrgId = ".$_SESSION['UserInfo']['OrgID'];
		
		$rsAudInfo = $objDB->sql_query($SQL);
		return $rsAudInfo;		
	}

	public function SelectBarisParamName($paramID){
		global $objDB;

		$SQL = "SELECT paramName FROM baris_param WHERE paramId = ".$paramID;
		$rsParamInfo = $objDB->sql_query($SQL);
		return $rsParamInfo[0]['paramName'];
		
	}

	public function SelectBarisPageName($pageId){
		global $objDB;
		$SQL = "SELECT db_pagename FROM baris_page WHERE pageId = ".$pageId;
		$rsPageInfo = $objDB->sql_query($SQL);
		return $rsPageInfo[0]['db_pagename'];
	}

	public function getcommondata($table,$data,$fieldname,$fieldid){
		global $objDB;

		$SQL = "SELECT ".$data." FROM ".$table." WHERE ".$fieldname." = ".$fieldid;
		$rsParamInfo = $objDB->sql_query($SQL);
		return $rsParamInfo[0][$data];
		
	}


	


	public function fetchContentRemark($PageParamID,$tokenID="",$coachNo=""){
		global $objDB;
		//$SQL = "SELECT pageConId FROM beatle_pagecontent WHERE LOWER(db_pageConTitle) = '".strtolower(QUESTION_DEFAULT_SENTENCE)."'";
/*		$SQL = "SELECT pageConId,db_pageConTitle FROM beatle_pagecontent WHERE LOWER(db_pageselection) = 'multiline'";
		$AboutUSayIArr = $objDB->sql_query($SQL);
		
		$AboutUSayI_ID = $AboutUSayIArr[0]['pageConId'];*/

		
		
		//$SQL = "SELECT * FROM baris_survey WHERE is_submit = 'Y' AND tokenid = (SELECT tokenid FROM beatle_survey WHERE db_surveyUserid = ".$CustomerID." AND orgid = ".$_SESSION['OtherInfo']['OrgID']." ORDER BY created_date DESC LIMIT 1)";
		
		//$SQL .= "	AND db_surveyPageId = 15";

		$SQL = "SELECT db_surveyValue FROM baris_survey WHERE db_surveyPageid = '15' AND is_submit = 'Y' ";

		if(!empty($PageParamID))
		$SQL .= " AND db_surveyParamId = ".$PageParamID. " ";

		if(!empty($coachNo))
		$SQL .= " AND db_coachNo = '".$coachNo."' ";


		if(!empty($tokenID))
		$SQL .= " AND tokenid = '".$tokenID. "' ";
		
		
		
		$SQL .= " AND created_date BETWEEN '".date("Y-m-d",strtotime($this->FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($this->ToDate))." 23:59:59' ";


		//echo $SQL;
		//die;
		$rsData = $objDB->sql_query($SQL);
		if(count($rsData) > 0){
			return $rsData[0]['db_surveyValue'];	
		}else{
			return "No any data found";	
		}
		
		
	}

	public function getPagesData($id){
		global $objDB;
		$SQL = "SELECT * FROM baris_page WHERE pageId = ".$id;
		$rsOwnerInfo = $objDB->sql_query($SQL);
		return $rsOwnerInfo;
	}
	
	public function getMonthlyTarget($queId,$subqueId,$pageId,$month,$year){
		global $objDB;
		$SQL = "SELECT * FROM baris_target WHERE OrgID = ".$_SESSION['UserInfo']['OrgID']." AND  queId = ".$queId." AND subqueId = ".$subqueId." AND pageId = ".$pageId." AND month = ".$month." AND year = ".$year;
		$rsOwnerInfo = $objDB->sql_query($SQL);
		return $rsOwnerInfo;
	}
	
                public function FetchValueScores($queId,$subqueId,$PageParamID,$PageID="",$tokenID=""){
		global $objDB;
		//$pageIDArr = IndustryPageIDS($pType,$BranchID,$IndustryID);	
		
		$SQL = "SELECT db_surveyValue AS AvgValue FROM baris_survey WHERE is_submit = 'Y' ";

		if(!empty($queId))
		$SQL .= " AND db_surveyQuestionId = ".$queId." " ;

		if(!empty($subqueId))
		$SQL .= " AND db_surveySubQuestionId = ".$subqueId." " ;

		if(!empty($PageParamID))
		$SQL .= " AND db_surveyParamId = ".$PageParamID. " ";

		if(!empty($PageID))
		$SQL .= " AND db_surveyPageId = '".$PageID. "' ";

		if(!empty($tokenID))
		$SQL .= " AND tokenid = '".$tokenID. "' ";
		
		$rsCountSurvey = $objDB->sql_query($SQL);		
		
		// if($rsCountSurvey[0]['AvgValue'] == "0"){
		// 	$rsCountSurvey[0]['AvgValue'] == "0";
		// }elseif ($rsCountSurvey[0]['AvgValue'] == "1") {
		// 	$rsCountSurvey[0]['AvgValue'] == "1";
		// }else{
		// 	$rsCountSurvey[0]['AvgValue'] = "X";
		// }
		
		return $rsCountSurvey[0]['AvgValue'];								
		
	}
	public function FetchValueScore($queId,$subqueId,$PageParamID,$PageID="",$month="",$year=""){
		global $objDB;
		//$pageIDArr = IndustryPageIDS($pType,$BranchID,$IndustryID);	
		
		$SQL = "SELECT SUM(db_surveyValue) AS AvgValue FROM baris_survey WHERE db_surveyPageid = ".$PageID." AND db_surveyValue AND is_submit = 'Y' AND OrgId = ".$_SESSION['UserInfo']['OrgID'];

		if(!empty($queId))
		$SQL .= " AND db_surveyQuestionId = ".$queId." " ;

		if(!empty($subqueId))
		$SQL .= " AND db_surveySubQuestionId = ".$subqueId." " ;

		if(!empty($this->StationId))
		$SQL .= " 
		AND db_surveyStationId = ".$this->StationId." " ;

		if(!empty($PageParamID))
		$SQL .= " AND db_surveyParamId = ".$PageParamID. " ";

		$SQL .= " AND year(created_date) between ".$year." and ".$year." and month(created_date) between ".$month." and ".$month." ";
		
		
		$rsCountSurvey = $objDB->sql_query($SQL);		
		
		return $rsCountSurvey[0]['AvgValue'];								
		
	}


	/////////////new report end/////////////////
	
	
	
	
	//  Method to fetch all user info.
	public function FetchAllUserInfo(){
		global $objDB;
		$SQL = "SELECT * FROM admin_master WHERE IsActive='Y' AND IsDeleted='N' ORDER BY AdminID DESC LIMIT 5";
		$rsUserInfo = $objDB->sql_query($SQL);
		return $rsUserInfo;	
	}



public function FetchQuestionsDatas($QuestionId,$subqueId,$date){
		global $objDB;
		
		
		
		$SQL = "SELECT tokenid FROM
		baris_survey WHERE is_submit = 'Y' ";

		$SQL .= " 
			AND created_date BETWEEN '".date("Y-m-d",strtotime($date))." 00:00:00' AND '".date("Y-m-d",strtotime($date))." 23:59:59' ";

		$SQL .= " AND OrgID = ".$_SESSION['UserInfo']['OrgID']." " ;

		$SQL .= " 
		AND db_surveyQuestionId = ".$QuestionId ;

		$SQL .= " 
		AND db_surveySubQuestionId = ".$subqueId ;
		
		$SQL .= " GROUP BY tokenid ORDER BY created_date DESC";
		
		$rsUserInfo_ = $objDB->sql_query($SQL);
		/*echo "<pre>";
        print_r($rsUserInfo_);
        echo "</pre>";
        die();*/
        $surveyArr = array();
        for ($i=0;$i< count($rsUserInfo_);$i++)
        {

        	//echo $rsUserInfo_[$i]['tokenid'];
        	$SQL = "SELECT
		baris_survey.surveyId,
		baris_survey.db_surveyStationId,
		baris_survey.db_surveyUserid,
		baris_survey.db_surveyQuestionId,
		baris_survey.db_surveyParamId,
		baris_survey.db_coachNo,
		baris_survey.db_coachName,
		baris_survey.db_surveyPageId,
		baris_survey.db_surveyValue,
		baris_survey.OrgID,
		baris_survey.DivisionId,
		baris_survey.created_date,
		baris_survey.updated_date,
		baris_survey.tokenid,
		baris_survey.is_submit,
		baris_survey.db_trainno,
		baris_survey.db_depart_time,
		baris_survey.db_totalCoachCount,
		baris_userlogin.db_phone,
		baris_userlogin.db_email,
		baris_userlogin.db_username,
		baris_userlogin.userId
		FROM
		baris_survey
		INNER JOIN baris_userlogin ON baris_survey.db_surveyUserid = baris_userlogin.userId
		WHERE baris_survey.is_submit = 'Y' 
		AND LOWER(baris_userlogin.db_usertype) = 'auditor' ";

		$SQL .= " 
			AND baris_survey.created_date BETWEEN '".date("Y-m-d",strtotime($date))." 00:00:00' AND '".date("Y-m-d",strtotime($date))." 23:59:59' ";
		
		$SQL .= " 
		AND baris_survey.db_surveySubQuestionId = ".$subqueId ;

		$SQL .= " 
		AND baris_survey.db_surveyPageId != '15' " ;

		$SQL .= " 
		AND baris_survey.tokenid = '".$rsUserInfo_[$i]['tokenid']."' " ;
		
		//$SQL .= " ORDER BY baris_survey.created_date DESC";
		$SQL .= " GROUP BY baris_survey.db_surveyPageId ORDER BY baris_survey.db_surveyPageId ASC, baris_survey.created_date DESC";

		$surveyArr[] = $objDB->sql_query($SQL);

        }
        return $surveyArr;
	}







}






?>