<?php 
// array for JSON response
$response = array();

// include db connect class
require_once 'db_connect.php';

// connecting to db
$db = new DB_CONNECT();

if (isset($_GET["userId"],$_GET["utype"],$_GET["fromdate"],$_GET["todate"])) {

	$userId = $_GET["userId"];
	$utype = $_GET["utype"];
	//$orgId = $_GET["orgId"];
	//$branchId = "";
	//$indid = "";
	$FromDate = $_GET["fromdate"];
	$ToDate = $_GET["todate"];
	
	/*if($_GET["branchId"] == "0"){
		$branchId="";
	}else{
		$branchId = $_GET["branchId"];
	}
	if($_GET["indId"] == "0"){
		$indid="";
	}else{
		$indid = $_GET["indId"];
	}*/
	if($utype == 'owner') {
		//$SQL = "SELECT * FROM beatle_branch WHERE {$SQL_B} db_branchOrg = (SELECT OrgId FROM beatle_organization WHERE db_ownerId = ".$userId." )";
	$result = mysql_query("SET NAMES utf8"); //the main trick form hindi languages
		
		$result = mysql_query("
			SELECT
			beatle_userlogin.userId AS userid,
			beatle_organization.OrgId AS OrgIDS,
			beatle_organization.db_Orgname,
			beatle_organization.db_orgtype,
			beatle_branch.branchId,
			beatle_branch.db_branchOrg,
			beatle_branch.db_branch_type,
			beatle_industry.IndId,
			beatle_industry.db_industry,
			beatle_industry.db_ind_type_id,
			beatle_industry.db_pagesId
			FROM
			beatle_userlogin
			INNER JOIN beatle_organization ON beatle_userlogin.OrgID = beatle_organization.OrgId
			INNER JOIN beatle_branch ON beatle_organization.OrgId = beatle_branch.db_branchOrg
			INNER JOIN beatle_industry ON beatle_branch.branchId = beatle_industry.db_bracnchid
			WHERE beatle_userlogin.userId = ".$userId." ")or die(mysql_error());

	}else if($utype == 'manager'){
		$result = mysql_query("
			SELECT
			beatle_userlogin.userId AS userid,
			beatle_organization.OrgId AS OrgIDS,
			beatle_organization.db_Orgname,
			beatle_organization.db_orgtype,
			beatle_branch.branchId,
			beatle_branch.db_branchOrg,
			beatle_branch.db_branch_type,
			beatle_industry.IndId,
			beatle_industry.db_industry,
			beatle_industry.db_ind_type_id,
			beatle_industry.db_pagesId
			FROM
			beatle_userlogin
			INNER JOIN beatle_branch ON beatle_userlogin.BranchID = beatle_branch.branchId
			INNER JOIN beatle_industry ON beatle_branch.branchId = beatle_industry.db_bracnchid
			INNER JOIN beatle_organization ON beatle_branch.db_branchOrg = beatle_organization.OrgId
			WHERE beatle_userlogin.userId = ".$userId." ")or die(mysql_error());

	}else if($utype == 'line_manager'){
		$result = mysql_query("
			SELECT
			beatle_userlogin.userId AS userid,
			beatle_organization.OrgId AS OrgIDS,
			beatle_organization.db_Orgname,
			beatle_organization.db_orgtype,
			beatle_branch.branchId,
			beatle_branch.db_branchOrg,
			beatle_branch.db_branch_type,
			beatle_industry.IndId,
			beatle_industry.db_industry,
			beatle_industry.db_ind_type_id,
			beatle_industry.db_pagesId
			FROM
			beatle_userlogin
			INNER JOIN beatle_industry ON beatle_userlogin.IndustryID = beatle_industry.IndId
			INNER JOIN beatle_branch ON beatle_industry.db_bracnchid = beatle_branch.branchId
			INNER JOIN beatle_organization ON beatle_branch.db_branchOrg = beatle_organization.OrgId
			WHERE beatle_userlogin.userId = ".$userId." ")or die(mysql_error());
	}


	$mainAvgLowRatingArr = array();
	if (mysql_num_rows($result) > 0) {
		while ($row = mysql_fetch_array($result)) {
			
			$avgLowRatingArr = fetchAvgLowRating("us",$row['branchId'],$row['IndId'],$row['OrgIDS'],$userId,$utype,$FromDate,$ToDate);	
			/*echo "<pre>";
			print_r($avgLowRatingArr['today_lowrating']);					
			echo "</pre>";*/
			$mainAvgLowRatingArr[] = $avgLowRatingArr['today_lowrating'];
			//echo count($avgLowRatingArr['today_lowrating']);
			//print_r($avgLowRatingArr['today_lowrating'][5]['userid']);
						
		}
		$new_array1 = array();
		$new_array2 = array();
		foreach ($mainAvgLowRatingArr as $key => $value) {
			if(count($value) == 1){
				$new_array1 = $value;
			}else{
				$new_array2 = $value;
			}
		}
		$mainArr = array_merge($new_array1,$new_array2);
		/*echo "<pre>";
		print_r($mainArr);					
		echo "</pre>";*/
		$response["success"] = 1;
		$response["survey"] = array();
		$response['count'] = count($mainArr);
		for($i=0; $i<count($mainArr); $i++) {

			$sValue = array();
			$sValue['userId'] = $mainArr[$i]['userid'];
			$sValue['username'] = $mainArr[$i]['username'];
			$sValue['userProfile'] = $mainArr[$i]['userProfile'];
			$sValue['userLoginName'] = $mainArr[$i]['userLoginName'];
			$sValue['userPhone'] = $mainArr[$i]['userPhone'];
			$sValue['userEmail'] = $mainArr[$i]['userEmail'];
			$sValue['userAddress'] = $mainArr[$i]['userAddress'];
			$sValue['userCity'] = $mainArr[$i]['userCity'];			
			$sValue['userState'] = $mainArr[$i]['userState'];
			$sValue['created_date'] = $mainArr[$i]['created_date'];
			$sValue['keyword'] = $mainArr[$i]['keyword'];
			$sValue['paxNo'] = $mainArr[$i]['paxNo'];
			$sValue['totalBill'] = $mainArr[$i]['totalBill'];
			$sValue['servername'] = $mainArr[$i]['servername'];
			$sValue['avgScore'] = $mainArr[$i]['score'];
			$sValue['lowScore'] = $mainArr[$i]['lowScore'];
			$sValue['aboutUsValue'] = $mainArr[$i]['aboutUsValue'];
			$sValue['tokenid'] = $mainArr[$i]['tokenid'];
			$sValue['sno'] = $i+1;
			$sValue['survey1'] =array();
			for($j=0; $j<count($mainArr[$i])-18; $j++) {
				$sValue1 = array();
				$sValue1['sno'] = $j+1; 
				$sValue1['pageId'] = $mainArr[$i][$j]['pageId'];
				$sValue1['pageName'] = $mainArr[$i][$j]['pageName'];
				$sValue1['pageType'] = $mainArr[$i][$j]['pageType'];
				$sValue1['pcId'] = $mainArr[$i][$j]['pcId'];
				$sValue1['pageConName'] = $mainArr[$i][$j]['pageConName'];
				$sValue1['pageConType'] = $mainArr[$i][$j]['pageConType'];
				$sValue1['value'] = $mainArr[$i][$j]['value'];
				array_push($sValue['survey1'], $sValue1);
			}
			array_push($response["survey"], $sValue);
		}
		echo json_encode($response);
	}

}else{
	//no industry found
	$response["success"] = 0;
	$response["message"] = "Required field(s) is missing";

	//echo no users JSON
	echo json_encode($response); 
}

	function fetchAvgLowRating($type,$BranchID,$IndustryID,$orgID,$userId,$utype,$FromDate,$ToDate){

		//$rsPages = explode(",",$row1['db_pagesId']);
		$totalCustomers = FetchCustomerData($BranchID,$IndustryID,$orgID,$userId,$utype,$FromDate,$ToDate);
		/*echo "<pre>";
		print_r($totalCustomers);*/
				
			
		$returnArr['today_lowrating'] = $mValue ;
	//$returnArr['count'] = $SUM ;
		return $returnArr;
	}

	function FetchCustomerData($branchid,$indid,$orgID,$userId,$utype,$FromDate,$ToDate){
	
		$SQL = "SELECT GROUP_CONCAT(DISTINCT db_surveyUserid SEPARATOR ',') AS db_surveyUserid FROM beatle_survey WHERE orgid = ".$orgID." AND is_submit = 'Y' GROUP BY created_date";
		$rsUserIDS = mysql_query($SQL);
		
		if(mysql_num_rows($rsUserIDS) <= 0)
		return array();
		
		$idArr = array();
		while ($row = mysql_fetch_array($rsUserIDS)) {
			if(!in_array($row['db_surveyUserid'],$idArr))	{
				$idArr[] = $row['db_surveyUserid'];
			}
		}
		
		
		$SQL = "SELECT userId FROM beatle_userlogin WHERE userId IN (".implode(',',$idArr).") AND lower(db_usertype) = 'end_user'";
		$rsUserInfo = mysql_query($SQL);

		$idArr = array();
		while ($row = mysql_fetch_array($rsUserInfo)) {
			if(!in_array($row['userId'],$idArr))	{
				$idArr[] = $row['userId'];
			}
		}		


		//$SQL = "SELECT * FROM beatle_survey WHERE is_submit = 'Y' AND db_surveyUserid IN (".implode(",",$idArr).") AND orgid = ".$_SESSION['OtherInfo']['OrgID']." GROUP BY db_surveyUserid ORDER BY created_date DESC";
		
		//if(count($idArr) > 0) {
		$SQL = "SELECT
		beatle_survey.surveyId,
		beatle_survey.db_surveyBranchid,
		beatle_survey.db_surveyUserid,
		beatle_survey.db_surveyIndId,
		beatle_survey.db_surveyPageid,
		beatle_survey.db_surveyContentId,
		beatle_survey.db_surveyValue,
		beatle_survey.created_date,
		beatle_survey.updated_date,
		beatle_survey.tokenid,
		beatle_survey.orgid,
		beatle_survey.keyword,
		beatle_survey.paxNo,
		beatle_survey.totalBill,
		beatle_survey.servername,
		beatle_survey.is_submit,
		beatle_userlogin.db_phone,
		beatle_userlogin.db_userLoginName,
		beatle_userlogin.db_email,
		beatle_userlogin.db_username,
		beatle_userlogin.userId
		FROM
		beatle_survey
		INNER JOIN beatle_userlogin ON beatle_survey.db_surveyUserid = beatle_userlogin.userId
		WHERE beatle_survey.is_submit = 'Y' 
		AND LOWER(beatle_userlogin.db_usertype) = 'end_user'  ";
		
		//AND beatle_survey.db_surveyUserid IN (".implode(",",$idArr).") ";
		
		//if(!empty($this->FromDate) && !empty($this->ToDate)){
			$SQL .= " 
			AND beatle_survey.created_date BETWEEN '".date("Y-m-d",strtotime($FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($ToDate))." 23:59:59' ";
		//}
		
		$SQL .= " AND beatle_survey.orgid = ".$orgID." " ;

	if(!empty($branchid))
		$SQL .= " AND beatle_survey.db_surveyBranchid = ".$branchid." " ;

	if(!empty($indid))
		$SQL .= " AND beatle_survey.db_surveyIndId = ".$indid." " ;
		

		
		$SQL .= " GROUP BY beatle_survey.tokenid ORDER BY beatle_survey.created_date DESC";
		$rsUserInfo_ = mysql_fetch_array(mysql_query($SQL))
		//return $rsUserInfo_;
		echo "<pre>";
		print_r($rsUserInfo_);

}

	function getPageContentName($PageContentID){
		$SQL ="SELECT db_pageConTitle,db_pageselection FROM beatle_pagecontent where pageConId = ".$PageContentID."";
		$rsCountSurvey = mysql_fetch_array(mysql_query($SQL));
		/*if ($type == 'title') {
			return $rsCountSurvey['db_pageConTitle'];
		}else{
			return $rsCountSurvey['db_pageselection'];
		}*/
		return $rsCountSurvey;
	}
	function getUserDetails($userid){
		$SQL = "SELECT db_username,db_userLoginName,db_phone,db_email,webprofileimage,db_address,db_city,db_state FROM beatle_userlogin WHERE db_usertype = 'End_user' AND userId = ".$userid;
		$result = mysql_fetch_array(mysql_query($SQL));
		return $result;
	}
	function getPageDetails($PageID){
		$SQL ="SELECT db_pagename, db_pagetype FROM beatle_page where pageId = ".$PageID."";
		$result = mysql_fetch_array(mysql_query($SQL));
		return $result;
	}
	function getLocation($cityID){
//		$SQL = "SELECT name FROM cities WHERE id = ".$cityID;
//		$result = mysql_fetch_array(mysql_query($SQL));
//		return $result;


		$SQL = "
		SELECT
		cities.id AS cityID,
		cities.name AS cityName,
		states.id AS stateID,
		states.name AS stateName,
		countries.id AS countryID,
		countries.sortname AS countryShortname,
		countries.name AS countryName 
		FROM
		cities
		INNER JOIN states ON cities.state_id = states.id
		INNER JOIN countries ON states.id = countries.id
		where cities.id = ".$cityID;
		$result = mysql_fetch_array(mysql_query($SQL));
		return $result;
	}
	function getStateName($stateID){
		$SQL = "SELECT name FROM states WHERE id = ".$stateID;
		$result = mysql_fetch_array(mysql_query($SQL));
		return $result;
	}
	function fetchIndustryWiseAverageName($avgValue,$PageContentID){
		global $objDB;
		$SQL ="SELECT db_pageChoice FROM beatle_pagecontent where pageConId = ".$PageContentID."";
		$result = $objDB->sql_query($SQL);
		$test = '';
		$mark=explode(',', $result[0]['db_pageChoice']);
		if ($avgValue > 0 and $avgValue <= 1){
			$test = $mark[3];
		}else if($avgValue > 1 and $avgValue <= 2){
			$test = $mark[2];
		}else if($avgValue > 2 and $avgValue <= 3){
			$test = $mark[1];
		}else if($avgValue > 3 and $avgValue <= 4){
			$test = $mark[0];
		}
		return $test; 
	}
	?>

<!-- SELECT
		beatle_userlogin.userId AS userid,
		beatle_organization.OrgId AS OrgIDS,
		beatle_organization.db_Orgname,
		beatle_organization.db_orgtype,
		beatle_branch.branchId,
		beatle_branch.db_branchOrg,
		beatle_branch.db_branch_type,
		beatle_industry.IndId,
		beatle_industry.db_industry,
		beatle_industry.db_ind_type_id,
		beatle_industry.db_pagesId
		FROM
		beatle_userlogin
		INNER JOIN beatle_organization ON beatle_userlogin.OrgID = beatle_organization.OrgId
		INNER JOIN beatle_branch ON beatle_organization.OrgId = beatle_branch.db_branchOrg
		INNER JOIN beatle_industry ON beatle_branch.branchId = beatle_industry.db_bracnchid
		WHERE beatle_userlogin.userId = 164

line_manager
		SELECT
		beatle_userlogin.userId AS userid,
		beatle_organization.OrgId AS OrgIDS,
		beatle_organization.db_Orgname,
		beatle_organization.db_orgtype,
		beatle_branch.branchId,
		beatle_branch.db_branchOrg,
		beatle_branch.db_branch_type,
		beatle_industry.IndId,
		beatle_industry.db_industry,
		beatle_industry.db_ind_type_id,
		beatle_industry.db_pagesId
		FROM
		beatle_userlogin
		INNER JOIN beatle_industry ON beatle_userlogin.IndustryID = beatle_industry.IndId
		INNER JOIN beatle_branch ON beatle_industry.db_bracnchid = beatle_branch.branchId
		INNER JOIN beatle_organization ON beatle_branch.db_branchOrg = beatle_organization.OrgId
		WHERE beatle_userlogin.userId = 151

		manager
SELECT
		beatle_userlogin.userId AS userid,
		beatle_organization.OrgId AS OrgIDS,
		beatle_organization.db_Orgname,
		beatle_organization.db_orgtype,
		beatle_branch.branchId,
		beatle_branch.db_branchOrg,
		beatle_branch.db_branch_type,
		beatle_industry.IndId,
		beatle_industry.db_industry,
		beatle_industry.db_ind_type_id,
		beatle_industry.db_pagesId
		FROM
		beatle_userlogin
		INNER JOIN beatle_branch ON beatle_userlogin.BranchID = beatle_branch.branchId
		INNER JOIN beatle_industry ON beatle_branch.branchId = beatle_industry.db_bracnchid
		INNER JOIN beatle_organization ON beatle_branch.db_branchOrg = beatle_organization.OrgId
		WHERE beatle_userlogin.userId = 154 -->