<?php
/*
 * Following code will get single department details
 * A industry is identified by dept_id
 */
 
// 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["orgid"],$_GET["branchid"],$_GET["indid"],$_GET["fromdate"],$_GET["todate"])){

    $orgID = $_GET["orgid"];
	$utype = $_GET["utype"];
	$userId = $_GET["userId"];
    $BranchID = $_GET["branchid"];
    $IndID = $_GET["indid"];
	$FromDate = $_GET["fromdate"];
	$ToDate = $_GET["todate"];
	$pg = "dashboardma";
	$SQL_B = "";
	// if($utype == 'manager' or $utype == 'line_manager'){
		// $SQL_B = " branchId = ".$BranchID." AND ";	
	// }

    //$result = mysql_query("SELECT * FROM beatle_branch WHERE {$SQL_B} db_branchOrg = '$orgID'")or die(mysql_error());
    $result1 = mysql_query("SELECT GROUP_CONCAT(db_pagesId) AS PageIDs FROM  beatle_industry WHERE db_bracnchid = ".$BranchID." AND IndId = ".$IndID) or die(mysql_error());
	$rsPageID = mysql_fetch_array($result1);

	$result = mysql_query("SELECT * FROM  beatle_page WHERE pageId IN (".$rsPageID['PageIDs'].") AND db_pagetype = 'Content'") or die(mysql_error());
	//$rsPages = mysql_fetch_array($SQL);
	//echo $rsPages['db_pagename'];
			
	
   if (!empty($result)) {
	   
		$response["pagesDetail"] = array();
	   
	  while($row =  mysql_fetch_array($result)) {
		 // echo $row['db_pagename'];
	   
			$pagesDetail = array();
			$pagesDetail["pageid"] = $row["pageId"];
			$pagesDetail["pageName"] = $row["db_pagename"];
			
			$averageLowRatingUS = fetchIndustryWiseAverage($userId,$orgID,$BranchID,$IndID,'us',$row["pageId"],$FromDate,$ToDate,'');
			$pagesDetail["pageScore"] = $averageLowRatingUS;
			
			$resultpc = mysql_query("SELECT * FROM beatle_pagecontent WHERE db_pageid = ".$row["pageId"]) or die(mysql_error());
			if(!empty($resultpc)){
				$pagesDetail["pageContentDetail"] = array();
				 while($rowpc =  mysql_fetch_array($resultpc)) {
					 $pageContentDetail = array();
					 $pageContentDetail["pageConId"] = $rowpc["pageConId"];
					 $pageContentDetail["pageConTitle"] = $rowpc["db_pageConTitle"];
					 $averageLowRatingUS = fetchIndustryWiseAverage($userId,$orgID,$BranchID,$IndID,'us',$row["pageId"],$FromDate,$ToDate, $rowpc["pageConId"]);
					 $pageContentDetail["pageConScore"] = $averageLowRatingUS;
					 array_push($pagesDetail["pageContentDetail"],$pageContentDetail);
				 }
			}
			array_push($response["pagesDetail"], $pagesDetail);
			
	   }
	    // success
            $response["success"] = 1;
            $response["pcount"] = mysql_num_rows($result);
            // echoing JSON response
            echo json_encode($response);
		
   }  else {

        $response["success"] = 0;
        $response["message"] = "No branch found.";
 
        // echo no users JSON
        echo json_encode($response);
    }

}else{
            
    $response["success"] = 0;
    $response["message"] = "Required field(s) is missing";
 
    echo json_encode($response);
}

function fetchIndustryWiseAverage($userId,$orgID,$BranchID,$IndustryID,$type,$PageID,$FromDate,$ToDate,$PageContentID){
			

			$datetime1 = new DateTime($FromDate);
			$datetime2 = new DateTime($ToDate);
			$interval = $datetime1->diff($datetime2);
			$diff = $interval->format('%a');
			$diff = $diff + 1;

			$SQL = "SELECT * FROM beatle_industry WHERE IndId = ".$IndustryID;
			$rsIndustry = mysql_fetch_array(mysql_query($SQL));

			$SQL = "SELECT GROUP_CONCAT(IndId) AS IndustryIDs FROM beatle_industry INNER JOIN beatle_branch ON beatle_industry.db_bracnchid =  beatle_branch.branchId

			WHERE beatle_industry.db_ind_type_id = ".$rsIndustry['db_ind_type_id']." ";


			if($type == 'us')
				$SQL .= " AND  beatle_industry.IndId = ".$IndustryID;
			else if($type == 'ind')
				$SQL .= " AND  beatle_industry.IndId != ".$IndustryID;


		//echo "<br />".$SQL."<br />";
			$rsIndustryIDs = mysql_fetch_array(mysql_query($SQL));

		//print_r($rsIndustryIDs);
		//echo $rsIndustryIDs;


			if(empty($rsIndustryIDs['IndustryIDs'])){
				return "0.00";
			}


				$fdate = $FromDate;
				$tdate = $ToDate;
				$avg = 0;
				$dtcnt = 0;
				while(strtotime($fdate) <= strtotime($tdate)) {
					$dtcnt++;
					$SQL = "SELECT  AVG(db_surveyValue) AS AvgValue FROM beatle_survey WHERE db_surveyPageid IN (".$PageID.") AND db_surveyValue IN (1,2,3,4) AND is_submit = 'Y'";

					if(!empty($IndustryID)){
						if($type == 'us')
							$SQL .= " AND db_surveyIndId = ".$IndustryID;
						else if($type == 'ind')
							$SQL .= " AND db_surveyIndId IN (".$rsIndustryIDs['IndustryIDs'].") ";
					}else{
						$SQL .= " AND db_surveyIndId IN (".$rsIndustryIDs['IndustryIDs'].") ";					
					}

					if(!empty($PageContentID))
						$SQL .= " AND db_surveyContentId = ".$PageContentID;

					if($type == 'us'){
						$SQL .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$BranchID ;
					}elseif($type == 'ind'){
						if(empty($BranchID) and empty($IndustryID)){
							$SQL .= " AND orgid != ".$orgID;
						}elseif(!empty($BranchID) and empty($IndustryID)){
							$SQL .= " AND db_surveyBranchid != ".$BranchID;
						}
					}

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

					$rsCountSurvey = mysql_fetch_array(mysql_query($SQL));

					$avg = $avg + $rsCountSurvey['AvgValue'];
					$fdate = date("Y-m-d",strtotime($fdate." +1 days"));

				}
				
				if($type == 'us'){
				if(fetchActiveScoreCheckedData($userId) == "checked"){
					if($avg > 0){
						$avg = number_format(($avg / fetchZeroFeedbackDays($orgID,$BranchID,$IndustryID,$FromDate,$ToDate)),2);
					}else{
						$avg = "0.00";
					}
				}else{
					if($avg > 0){
						$avg = number_format(($avg / $dtcnt),2);
					}else{
						$avg = "0.00";
					}
				}
				}else{
					if($avg > 0){
						$avg = number_format(($avg / $dtcnt),2);
					}else{
						$avg = "0.00";
					}
				}

				return $avg;	
	}

function fetchActiveScoreCheckedData($userId){
		$SQL = "select db_pagepermistion from beatle_userlogin where userId = ".$userId;
		$rsData = mysql_fetch_array(mysql_query($SQL));
		if(strpos($rsData['db_pagepermistion'],"ason")){
			return "checked";
		}else{
			if($rsData['db_pagepermistion'] == "ason")
				return "checked";
			else
				return "";
		}
	}
	
function fetchZeroFeedbackDays($orgID,$BranchID,$IndustryID,$FromDate,$ToDate){
		
		$SQL = "SELECT * FROM beatle_survey WHERE is_submit = 'Y' ";
		

		if(empty($BranchID) and empty($IndustryID)){
			$SQL .= " AND orgid = ".$orgID;
		}elseif(!empty($BranchID) and empty($IndustryID)){
			$SQL .= " AND orgid = ".$orgID." AND db_surveyBranchid = ".$BranchID;
		}elseif(!empty($BranchID) && !empty($IndustryID)){
			$SQL .= " AND orgid = ".$orgID." AND db_surveyBranchid = ".$BranchID." AND db_surveyIndId  = ".$IndustryID;
		}
		

		$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 DATE(created_date)";

		$rsCountDay = mysql_query($SQL);;

		return mysql_num_rows($rsCountDay);				
	}
?>