<?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("
			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());
	}


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

			//echo count($avgLowRatingArr['today_lowrating']);
			//print_r($avgLowRatingArr['today_lowrating'][5]['userid']);
				if(count($avgLowRatingArr['today_lowrating']) > 0){
					$response["success"] = 1;
					$response["survey"] = array();
					$response['count'] = count($avgLowRatingArr['today_lowrating']);
					for($i=0; $i<count($avgLowRatingArr['today_lowrating']); $i++) {

						$sValue = array();
						$sValue['userId'] = $avgLowRatingArr['today_lowrating'][$i]['userid'];
						$sValue['username'] = $avgLowRatingArr['today_lowrating'][$i]['username'];
						$sValue['userProfile'] = $avgLowRatingArr['today_lowrating'][$i]['userProfile'];
						$sValue['userLoginName'] = $avgLowRatingArr['today_lowrating'][$i]['userLoginName'];
						$sValue['userPhone'] = $avgLowRatingArr['today_lowrating'][$i]['userPhone'];
						$sValue['userEmail'] = $avgLowRatingArr['today_lowrating'][$i]['userEmail'];
						$sValue['userAddress'] = $avgLowRatingArr['today_lowrating'][$i]['userAddress'];
						$sValue['userCity'] = $avgLowRatingArr['today_lowrating'][$i]['userCity'];						
						$sValue['userState'] = $avgLowRatingArr['today_lowrating'][$i]['userState'];
						$sValue['created_date'] = $avgLowRatingArr['today_lowrating'][$i]['created_date'];
						$sValue['keyword'] = $avgLowRatingArr['today_lowrating'][$i]['keyword'];
						$sValue['paxNo'] = $avgLowRatingArr['today_lowrating'][$i]['paxNo'];
						$sValue['totalBill'] = $avgLowRatingArr['today_lowrating'][$i]['totalBill'];
						$sValue['servername'] = $avgLowRatingArr['today_lowrating'][$i]['servername'];
						$sValue['avgScore'] = $avgLowRatingArr['today_lowrating'][$i]['score'];
						$sValue['lowScore'] = $avgLowRatingArr['today_lowrating'][$i]['lowScore'];
						$sValue['aboutUsValue'] = $avgLowRatingArr['today_lowrating'][$i]['aboutUsValue'];
						$sValue['tokenid'] = $avgLowRatingArr['today_lowrating'][$i]['tokenid'];
						$sValue['sno'] = $i+1;
						$sValue['survey1'] =array();
						for($j=0; $j<count($avgLowRatingArr['today_lowrating'][$i])-18; $j++) {
							$sValue1 = array();
							$sValue1['sno'] = $j+1; 
							$sValue1['pageId'] = $avgLowRatingArr['today_lowrating'][$i][$j]['pageId'];
							$sValue1['pageName'] = $avgLowRatingArr['today_lowrating'][$i][$j]['pageName'];
							$sValue1['pageType'] = $avgLowRatingArr['today_lowrating'][$i][$j]['pageType'];
							$sValue1['pcId'] = $avgLowRatingArr['today_lowrating'][$i][$j]['pcId'];
							$sValue1['pageConName'] = $avgLowRatingArr['today_lowrating'][$i][$j]['pageConName'];
							$sValue1['pageConType'] = $avgLowRatingArr['today_lowrating'][$i][$j]['pageConType'];
							$sValue1['value'] = $avgLowRatingArr['today_lowrating'][$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){

		$SQL_B = "";
		if(!empty($BranchID)){
			$SQL_B = " branchId = ".$BranchID." AND ";	
		}

		if($utype == 'owner') {
			$SQL = "SELECT * FROM beatle_branch WHERE {$SQL_B} db_branchOrg = (SELECT OrgId FROM beatle_organization WHERE db_ownerId = ".$userId." )";
		}else if($utype == 'manager'){
			$SQL = "SELECT * FROM beatle_branch WHERE {$SQL_B}  db_branchManager = ".$userId." ";
		}else if($utype == 'line_manager'){
			$SQL = "SELECT * FROM beatle_branch WHERE {$SQL_B}  branchId = (SELECT BranchID FROM beatle_userlogin WHERE userId = ".$userId.") ";
		}
		$rsBranches = mysql_query($SQL);

		$returnArr = array();
		$avgUSTotal = 0;
		$SUM = 0;
		$SUMOve = 0;
		$value = array();
		$mValue = array();
		$vCount = array();
		while($row = mysql_fetch_array($rsBranches)) {	

			$SQL = "SELECT * FROM beatle_industry WHERE db_bracnchid = ".$row['branchId'];

			if($IndustryID != "" && !empty($IndustryID)){
				$SQL .= " AND IndId = ".$IndustryID." ";	
			}

			if($utype == 'line_manager'){
				$SQL .= " AND db_userid = ".$userId."  ";
			}

			$rsIndustry = mysql_query($SQL);

			while($row1 = mysql_fetch_array($rsIndustry)) {	

				$rsPages = explode(",",$row1['db_pagesId']);

				if(count($rsPages) > 0 && !empty($row1['db_pagesId'])) {

					$SQL_ = "SELECT tokenid,db_surveyUserid,created_date,keyword,paxNo,totalBill,servername,ROUND(AVG(db_surveyValue),2) AS AvgValue from beatle_survey WHERE db_surveyPageid IN (".$row1['db_pagesId'].") AND db_surveyIndId IN (".$row1['IndId'].") AND db_surveyValue IN (1,2,3,4) AND is_submit = 'Y' ";
					$SQL_ .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$row['branchId'];
									//$SQL_ .= " AND created_date BETWEEN '".date("Y-m-d",strtotime("-30 days"))." 00:00:00' AND '".date("Y-m-d")." 23:59:59' ";
					$SQL_ .= " AND created_date BETWEEN '".date("Y-m-d",strtotime($FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($ToDate))." 23:59:59' ";
					$SQL_ .= " GROUP BY tokenid";

					$rsCountSurvey_ = mysql_query($SQL_);

					if(mysql_num_rows($rsCountSurvey_) > 0){
						while($row2 = mysql_fetch_array($rsCountSurvey_)) {	
							$temp = array();
							if(round($row2['AvgValue'],2) <= 2){
								$temp['userid'] = $row2['db_surveyUserid'];
								$temp['username'] = getUserDetails($row2['db_surveyUserid'])['db_username'];
								$temp['userProfile'] = getUserDetails($row2['db_surveyUserid'])['webprofileimage'];
								$temp['userLoginName'] = getUserDetails($row2['db_surveyUserid'])['db_userLoginName'];
								$temp['userPhone'] = getUserDetails($row2['db_surveyUserid'])['db_phone'];
								$temp['userEmail'] = getUserDetails($row2['db_surveyUserid'])['db_email'];
								$temp['userAddress'] = getUserDetails($row2['db_surveyUserid'])['db_address'];
								$temp['userCity'] = getLocation(getUserDetails($row2['db_surveyUserid'])['db_city'])['cityName'];
								$temp['userState'] =  getLocation(getUserDetails($row2['db_surveyUserid'])['db_state'])['stateName'];
								$temp['created_date'] = $row2['created_date'];
								$temp['keyword'] = $row2['keyword'];
								$temp['paxNo'] = $row2['paxNo'];
								$temp['totalBill'] = $row2['totalBill'];
								$temp['servername'] = $row2['servername'];
								$temp['aboutUsValue'] = "";
								$temp['score'] = round($row2['AvgValue'],2);
								$temp['tokenid'] = $row2['tokenid'];

								$rsGetSurveyValues = mysql_query("SELECT count(db_surveyValue) AS AvgLowValue FROM beatle_survey WHERE db_surveyValue IN (1,2) AND tokenid = '".$row2['tokenid']."'")or die(mysql_error());
								if(mysql_num_rows($rsGetSurveyValues) > 0){
									while($row4 = mysql_fetch_array($rsGetSurveyValues)) {	

										$temp['lowScore'] = $row4['AvgLowValue'];
									}
								}

								$rsGetSurvey = mysql_query("SELECT db_surveyPageid, db_surveyContentId, db_surveyValue AS AvgValue FROM beatle_survey WHERE tokenid = '".$row2['tokenid']."'")or die(mysql_error());

								if(mysql_num_rows($rsGetSurvey) > 0){
									while($row3 = mysql_fetch_array($rsGetSurvey)) {
										$value['pageId'] = $row3['db_surveyPageid'];
										$value['pageName'] = getPageDetails($row3['db_surveyPageid'])['db_pagename'];
										$value['pageType'] = getPageDetails($row3['db_surveyPageid'])['db_pagetype'];
										$value['pcId'] = $row3['db_surveyContentId'];
										$value['pcId'] = $row3['db_surveyContentId'];
										$value['pageConName'] = getPageContentName($row3['db_surveyContentId'])['db_pageConTitle'];
										$value['pageConType'] = getPageContentName($row3['db_surveyContentId'])['db_pageselection'];
										$value['value'] = $row3['AvgValue'];
										if($value['pageConType'] == 'multiline'){
											$temp['aboutUsValue'] = $row3['AvgValue'];
										}
										$temp[] = $value;

									}
								}
								$mValue[] = $temp;
							}
						}
					}

				}else{
								//$returnArr[] = "0.00";
								//echo "NO PAGES FOUND";	
				}
			}
		}
		$returnArr['today_lowrating'] = $mValue ;
	//$returnArr['count'] = $SUM ;
		return $returnArr;
	}

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