<?php

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

	protected function InsertUserInfo(){}  ///// InsertUserInfo completed  /////

	public function FetchCustomerData($Val=""){
	
		global $objDB;
		
		
		$SQL = "SELECT GROUP_CONCAT(DISTINCT db_surveyUserid SEPARATOR ',') AS db_surveyUserid FROM beatle_survey WHERE orgid = ".$_SESSION['OtherInfo']['OrgID']." AND is_submit = 'Y' GROUP BY created_date";
		$rsUserIDS = $objDB->sql_query($SQL);
		
		if(count($rsUserIDS) <= 0)
		return array();
		
		$idArr = array();
		for($i=0; $i<count($rsUserIDS); $i++){
			if(!in_array($rsUserIDS[$i]['db_surveyUserid'],$idArr))	{
				$idArr[] = $rsUserIDS[$i]['db_surveyUserid'];
			}
		}
		
		if(count($idArr) <= 0){
			return array();	
		}
		
		
		$SQL = "SELECT userId FROM beatle_userlogin WHERE userId IN (".implode(',',$idArr).") AND lower(db_usertype) = 'end_user'";
		if(!empty($this->CustomerID)){
			$SQL .= " AND `userId` = ".$this->CustomerID;
		}
		$rsUserInfo = $objDB->sql_query($SQL);


		$idArr = array();
		for($i=0; $i<count($rsUserInfo); $i++){
			if(!in_array($rsUserInfo[$i]['userId'],$idArr))	{
				$idArr[] = $rsUserInfo[$i]['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_email,
		beatle_userlogin.db_address,
		beatle_userlogin.db_state,
		beatle_userlogin.db_city,
		beatle_userlogin.db_userLoginName,
		beatle_userlogin.db_username,
		beatle_userlogin.webprofileimage,
		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 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($this->FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($this->ToDate))." 23:59:59' ";
		}
		
		
		if(!empty($this->OrgID))
		$SQL .= " AND beatle_survey.orgid = ".$this->OrgID." " ;
		else
		$SQL .= " AND beatle_survey.orgid = ".$_SESSION['OtherInfo']['OrgID']." " ;
		
		if(!empty($this->BranchID))
		$SQL .= " AND beatle_survey.db_surveyBranchid = ".$this->BranchID." " ;
		
		if(!empty($this->IndustryID))
		$SQL .= " AND beatle_survey.db_surveyIndId = ".$this->IndustryID." " ;

		if(!empty($this->TokenID))
		$SQL .= " AND beatle_survey.tokenid = '".$this->TokenID."' " ;
		
		$SQL .= " GROUP BY beatle_survey.tokenid ORDER BY beatle_survey.created_date DESC";
		
		
		$rsUserInfo_ = $objDB->sql_query($SQL);
		
		return $rsUserInfo_;
	}
	
	public function fetchOrganization(){
		global $objDB;
		
		//$SQL = "SELECT OrgID,BranchID,IndustryID FROM beatle_userlogin WHERE 1 = 1 AND lower(db_usertype) = '".strtolower($_SESSION['UserInfo']['UType'])."' AND userId = ".$_SESSION['UserInfo']['UserID'];
		//$rsOrg = $objDB->sql_query($SQL);

		$SQL = "SELECT OrgId,db_Orgname FROM beatle_organization WHERE OrgId = ".$_SESSION['OtherInfo']['OrgID'];
		$rsOrgInfo = $objDB->sql_query($SQL);
		return $rsOrgInfo;
	}
	
	public function fetchBranches(){
		global $objDB;
		$SQL = "SELECT db_branchName,branchId FROM beatle_branch WHERE db_branchOrg = ".$_SESSION['OtherInfo']['OrgID'];
		$rsBranches = $objDB->sql_query($SQL);
		return $rsBranches;
	}
	
	public function fetchIndustrys($OrgID,$BranchID,$IndustryID){
		global $objDB;
		if(!empty($OrgID)) {
			$SQL = "SELECT * FROM beatle_industry WHERE db_bracnchid IN (SELECT GROUP_CONCAT(branchId) FROM beatle_branch WHERE db_branchOrg = ".$OrgID.")";
			$rsBranches = $objDB->sql_query($SQL);
		}else {
			$SQL = "SELECT * FROM beatle_industry WHERE db_bracnchid IN (SELECT GROUP_CONCAT(branchId) FROM beatle_branch WHERE db_branchOrg = ".$_SESSION['OtherInfo']['OrgID'].")";
			$rsBranches = $objDB->sql_query($SQL);
		}
		return $rsBranches;
	}
	
	public function FetchOrganizationName($id){
		global $objDB;
		$SQL = "SELECT db_Orgname FROM beatle_organization WHERE OrgId = ".$id;
		$rsOwnerInfo = $objDB->sql_query($SQL);
		return $rsOwnerInfo[0]['db_Orgname'];
	}
	
	public function FetchBranchName($id){
		global $objDB;
		$SQL = "SELECT db_branchName FROM beatle_branch WHERE branchId = ".$id;
		$rsOwnerInfo = $objDB->sql_query($SQL);
		return $rsOwnerInfo[0]['db_branchName'];
	}
	
	public function FetchIndustryName($id){
		global $objDB;
		$SQL = "SELECT db_industry FROM beatle_industry WHERE IndId = ".$id;
		$rsOwnerInfo = $objDB->sql_query($SQL);
		return $rsOwnerInfo[0]['db_industry'];
	}
	
	
	public function SelectPageContentName($id){
		global $objDB;
		$SQL = "SELECT db_pageConTitle FROM beatle_pagecontent WHERE pageConId = ".$id;
		$rsOwnerInfo = $objDB->sql_query($SQL);
		return $rsOwnerInfo[0]['db_pageConTitle'];
	}
	
	public function SelectPageName($id){
		global $objDB;
		$SQL = "SELECT db_pagename FROM beatle_page WHERE pageId = ".$id;
		$rsOwnerInfo = $objDB->sql_query($SQL);
		return $rsOwnerInfo[0]['db_pagename'];
	}
	
	
	public function fetchCustomersQA($CustomerID,$sort,$PageIDS){
		global $objDB;
		
		//print_r($_SESSION);
		
		//$SQL = "SELECT pageConId FROM beatle_pagecontent WHERE LOWER(db_pageConTitle) = '".strtolower(QUESTION_DEFAULT_SENTENCE)."'";
		$SQL = "SELECT GROUP_CONCAT(pageConId) AS pageConIds FROM beatle_pagecontent WHERE LOWER(db_pageselection) = 'multiline'";
		$AboutUSayIArr = $objDB->sql_query($SQL);
		
		$AboutUSayI_ID = $AboutUSayIArr[0]['pageConIds'];
		
		//print_r($PageIDS);
		
		
		$SQL = "SELECT beatle_organization.OrgId FROM beatle_organization RIGHT JOIN beatle_userlogin ON beatle_organization.OrgId = beatle_userlogin.OrgID WHERE beatle_userlogin.userId = ".$_SESSION['UserInfo']['UserID']." ";
		$rsOrgID = $objDB->sql_query($SQL);
		
		$SQL = "SELECT * FROM beatle_survey WHERE is_submit = 'Y' AND  db_surveyPageid IN (".$PageIDS.") ";
		
		if(!empty($this->TokenID))
		$SQL .= " AND beatle_survey.tokenid = '".$this->TokenID."' " ;
		
		
		if(!empty($this->FromDate) && !empty($this->ToDate)){
			$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' ";	
		}
		
		
		if(!empty($AboutUSayI_ID))
		$SQL .= "	AND db_surveyContentId NOT IN (".$AboutUSayIArr[0]['pageConIds'].") ";
		
		//echo "<br /><br />";
		//echo $SQL;
		
		$rsData = $objDB->sql_query($SQL);
		
		
		return $rsData;
		
	}
	
	public function fetchIndustryPagesData(){
		global $objDB;
		$SQL_B = "";
		
		if(strtolower($_SESSION['UserInfo']['UType']) == 'owner') {
			$SQL = "SELECT * FROM beatle_branch WHERE db_branchOrg = (SELECT OrgId FROM beatle_organization WHERE db_ownerId = ".$_SESSION['UserInfo']['UserID']." )";
		}else if(strtolower($_SESSION['UserInfo']['UType']) == 'manager'){
			$SQL = "SELECT * FROM beatle_branch WHERE db_branchManager = ".$_SESSION['UserInfo']['UserID']." ";
		}else if(strtolower($_SESSION['UserInfo']['UType']) == 'line_manager'){
			$SQL = "SELECT * FROM beatle_branch WHERE branchId = (SELECT BranchID FROM beatle_userlogin WHERE userId = ".$_SESSION['UserInfo']['UserID'].") ";
		}
		$rsBranches = $objDB->sql_query($SQL);
		
		return $rsBranches;
	}
	
	public function fetchContentAboutISay($CustomerID){
		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);
		$returnArr = array();
		
		for($i=0;$i < count($AboutUSayIArr); $i++){
			
			$AboutUSayIArr = $objDB->sql_query($SQL);
		
			$AboutUSayI_ID = $AboutUSayIArr[$i]['pageConId'];

			$returnArr = array();
			$returnArr[0] =  $AboutUSayIArr[$i]['db_pageConTitle'];
			
			$SQL1 = "SELECT db_surveyValue FROM beatle_survey WHERE is_submit = 'Y' AND tokenid = '".$this->TokenID."'";
			
			if(!empty($AboutUSayI_ID))
			$SQL1 .= "	AND db_surveyContentId = ".$AboutUSayI_ID;
			$rsData = $objDB->sql_query($SQL1);
			
			if(count($rsData) > 0){
				$returnArr[1] = $rsData[0]['db_surveyValue'];	
				break;
			}else{
				$returnArr[1] = "No any data found";	
				
			}
			
			// $checkvalue = $rsData[0]['db_surveyValue'];
			// if(empty($checkvalue)){
				// break;
			// }
		}
		
		// $AboutUSayIArr = $objDB->sql_query($SQL);
		
		// $AboutUSayI_ID = $AboutUSayIArr[0]['pageConId'];

		// $returnArr = array();
		// $returnArr[0] =  $AboutUSayIArr[0]['db_pageConTitle'];

		
		
		// $SQL = "SELECT db_surveyValue FROM beatle_survey WHERE is_submit = 'Y' AND tokenid = '".$this->TokenID."'";
		
		// if(!empty($AboutUSayI_ID))
		// $SQL .= "	AND db_surveyContentId = ".$AboutUSayI_ID;
		// $rsData = $objDB->sql_query($SQL);
		
		
		// if(count($rsData) > 0){
			// $returnArr[1] = $rsData[0]['db_surveyValue'];	
		// }else{
			// $returnArr[1] = "No any data found";	
		// }
		
		return $returnArr;
		
	}
	
	
	public function fetchContentDetails($CustomerID,$type){
		global $objDB; 
		
		//$rsData =  $this->FetchCustomerData();
		
		$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.is_submit,
		beatle_userlogin.db_phone,
		beatle_userlogin.db_userLoginName,
		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 beatle_survey.db_surveyUserid = ".$this->CustomerID." ";
		
		if(!empty($this->FromDate) && !empty($this->ToDate)){
			$SQL .= " AND beatle_survey.created_date BETWEEN '".date("Y-m-d",strtotime($this->FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($this->ToDate))." 23:59:59' ";
		}
		
		
		if(!empty($this->OrgID))
		$SQL .= " AND beatle_survey.orgid = ".$this->OrgID." " ;
		else
		$SQL .= " AND beatle_survey.orgid = ".$_SESSION['OtherInfo']['OrgID']." " ;
		
		if(!empty($this->BranchID))
		$SQL .= " AND beatle_survey.db_surveyBranchid = ".$this->BranchID." " ;
		
		if(!empty($this->IndustryID))
		$SQL .= " AND beatle_survey.db_surveyIndId = ".$this->IndustryID." " ;

		if(!empty($this->TokenID))
		$SQL .= " AND beatle_survey.tokenid = '".$this->TokenID."' " ;
		
		
		//if(empty($IN))
		//$SQL .= " GROUP BY beatle_survey.tokenid";
		
		$SQL .= " ORDER BY beatle_survey.created_date DESC";
		
		//echo $SQL;
		
		$rsData = $objDB->sql_query($SQL);
		
		
		
		if(count($rsData) <= 0)
		return array();
		
		if($type == 'question') {
			//$rsBranches = $this->fetchIndustryPagesData($CustomerID,$type);
			
			//$BranchIDArr = array();
			
			//foreach($rsBranches as $key => $val){
			//	$BranchIDArr[] = $val['branchId'];
			//}
		}
		
		$SQL = "SELECT db_pagesId AS PageIDs FROM beatle_industry WHERE IndId = ".$this->IndustryID;
		$rsPageID = $objDB->sql_query($SQL);
		
		//echo $rsPageID[0]['PageIDs'];
		
		$SQL = "SELECT GROUP_CONCAT(pageId) AS PageIDS FROM  beatle_page WHERE pageId IN (".$rsPageID[0]['PageIDs'].") AND LOWER(db_pagetype) = '".$type."'";
		$rsPages = $objDB->sql_query($SQL);
		
		//print_r($rsPages);
		//die;
		
		if($type == 'content') {
			return $rsPages;
		}
		
		
		if(!empty($rsPages[0]['PageIDS']))
		$rsQuestionData = $this->fetchCustomersQA($CustomerID,$type,$rsPages[0]['PageIDS']);
		else
		$rsQuestionData = array();
		
		return $rsQuestionData;
	}
	
	public function fetchStateName($sid){
		global $objDB; 
		$SQL = "SELECT name from states WHERE id = ".$sid;
		$rsData = $objDB->sql_query($SQL);;
		return $rsData[0]['name'];
		
	}
	public function fetchCityName($sid){
		global $objDB; 
		$SQL = "SELECT name from cities WHERE id = ".$sid;
		$rsData = $objDB->sql_query($SQL);;
		return $rsData[0]['name'];
		
	}
	

}


?>