File: /home/barbeatleanalyti/public_html/api.beatleanalytics.com/corporate/branchIndDetails.php
<?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());
// $result = mysql_query("SELECT * FROM app_notification WHERE IsActive LIKE 'Y' ")or die(mysql_error());
$avgValueDB = "";
if (!empty($result)) {
if (mysql_num_rows($result) > 0) {
$response["branchDetail"] = array();
while ($row = mysql_fetch_array($result)) {
$branchDetail = array();
$branchDetail["branchid"] = $row["branchId"];
$branchDetail["branchName"] = $row["db_branchName"];
$branchDetail["branchType"] = $row["db_branch_type"];
$SQL_I = "";
if($utype == 'line_manager'){
$SQL_I = " IndId = ".$IndID." AND ";
}
$resulti = mysql_query("SELECT * FROM beatle_industry WHERE {$SQL_I} db_bracnchid = ".$row["branchId"]."")or die(mysql_error());
if (!empty($resulti)) {
//$response["industry"] = array();
$branchDetail["industries"] = array();
while ($row1 = mysql_fetch_array($resulti)) {
$indDetail = array();
$indDetail["indid"] = $row1["IndId"];
$indDetail["indName"] = $row1["db_industry"];
$performanceArr = fetchRatingForCurrentIndustry($pg,$userId,$row1["IndId"],$row["branchId"],$orgID,'us',$FromDate,$ToDate);
$avgValueDB = $avgValueDB + $performanceArr['avgValueDB'];
$indDetail["indScore"] = $performanceArr['avgValueDB'];
array_push($branchDetail["industries"], $indDetail);
}
}
$branchDetail["branchScore"] = number_format($avgValueDB / mysql_num_rows($resulti),2);
$branchDetail["icount"] = mysql_num_rows($resulti);
array_push($response["branchDetail"], $branchDetail);
$avgValueDB = "";
}
// success
$response["success"] = 1;
$response["bcount"] = mysql_num_rows($result);
// echoing JSON response
echo json_encode($response);
} else {
// no product found
$response["success"] = 0;
$response["message"] = "No offers found";
// echo no users JSON
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 fetchRatingForCurrentIndustry($pg,$userId,$IndID,$BranchID,$orgID,$type,$FromDate,$ToDate){
//echo "<pre>";
$SQL = "SELECT * FROM beatle_industry WHERE IndId = ".$IndID;
$rsIndustry1 =mysql_query($SQL);
$rsIndustry = mysql_fetch_array($rsIndustry1);
$returnArr = array();
$returnArr['performance'] = 0;
$returnArr['score'] = 0;
$performance = 0;
$score = 0;
$performanceWeekly = 0;
$scoreWeekly = 0;
$avgValueDB = 0;
//$indName = $rsIndustry[0]['db_industry'];
//echo "<br />".$type."#".$indName."<br />";
$SQL = "SELECT GROUP_CONCAT(IndId) AS IndustryIDs FROM beatle_industry WHERE db_ind_type_id = ".$rsIndustry['db_ind_type_id']."";
if($type == 'ind')
$SQL .= " AND IndId != ".$IndID."";
$rsIndustryIDs1 = mysql_query($SQL);
$rsIndustryIDs = mysql_fetch_array($rsIndustryIDs1);
// echo '<pre>';
// print_r($rsIndustryIDs);
// echo '</pre>';
// echo "<br /><br />";
$avgValue = "0.00";
if(mysql_num_rows($rsIndustry1) > 0 && mysql_num_rows($rsIndustryIDs1) > 0){
//echo "<br />";
//echo $rsIndustry[0]['db_pagesId'];
//echo $rsIndustryIDs[0]['IndustryIDs'];
//echo "<br /><br />";
$rsPages = explode(",",$rsIndustry['db_pagesId']);
if(count($rsPages) > 0 && !empty($rsIndustry['db_pagesId']) && !empty($rsIndustryIDs['IndustryIDs'])) {
//if($pg == 'summery') {
// if($type == 'us') {
/////////// CODE ONLY FOR PERFORMANCE MONTHLY //////////////////
if($pg == 'summery') {
$fdate = date("Y-m-d",strtotime("-30 days"));
$tdate = date("Y-m-d");
}else{
$fdate = date("Y-m-d",strtotime($FromDate));
$tdate = date("Y-m-d",strtotime($ToDate));
}
$dtcnt = 0;
while(strtotime($fdate) <= strtotime($tdate)) {
$dtcnt++;
$SQL = "SELECT AVG(db_surveyValue) AS AvgValue FROM beatle_survey WHERE db_surveyPageid IN(".$rsIndustry['db_pagesId'].") ";
//$SQL .= " AND db_surveyIndId IN (".$IndID.")";
if($type == 'us')
$SQL .= " AND db_surveyIndId IN (".$IndID.")";
else if($type == 'ind')
$SQL .= " AND db_surveyIndId IN (".$rsIndustryIDs['IndustryIDs'].") ";
$SQL .= " AND db_surveyValue IN (1,2,3,4) AND is_submit = 'Y'";
//$SQL .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$BranchID ;
if($type == 'us')
$SQL .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$BranchID ;
else if($type == 'ind')
$SQL .= " AND orgid != ".$orgID;
$SQL .= " AND created_date BETWEEN '".$fdate." 00:00:00' AND '".$fdate." 23:59:59' ";
//echo "<pre>";
$rsCountSurvey = mysql_fetch_array(mysql_query($SQL));
//echo $fdate." ".$rsCountSurvey['AvgValue'];
//echo "</br>";
//print_r($rsCountSurvey);
//echo " # ";
$performance = $performance + $rsCountSurvey['AvgValue'];
//echo " <br />";
$fdate = date("Y-m-d",strtotime($fdate." + 1day"));
} // WHILE COMPLETED
if($type == 'us'){
if(fetchActiveScoreCheckedData($userId) == "checked"){
if($pg == 'summery'){
if($performance > 0){
$performance = number_format(($performance / fetchZeroFeedbackDays($orgID,$BranchID,$IndID,date("Y-m-d",strtotime("-30 days")),date("Y-m-d"))),2);
}else{
$performance = "0.00";
}
}else{
if($performance > 0){
$performance = number_format(($performance / fetchZeroFeedbackDays($orgID,$BranchID,$IndID,$FromDate,$ToDate)),2);
}else{
$performance = "0.00";
}
}
}else{
if($performance > 0){
$performance = number_format(($performance / $dtcnt),2);
}else{
$performance = "0.00";
}
}
}elseif($type == 'ind'){
if($performance > 0){
$performance = number_format(($performance / $dtcnt),2);
}else{
$performance = "0.00";
}
}
$SQL = "";
$SQL = "SELECT AVG(db_surveyValue) AS AvgValue FROM beatle_survey WHERE db_surveyPageid IN(".$rsIndustry['db_pagesId'].") ";
//$SQL .= " AND db_surveyIndId IN (".$IndID.")";
if($type == 'us')
$SQL .= " AND db_surveyIndId IN (".$IndID.")";
else if($type == 'ind')
$SQL .= " AND db_surveyIndId IN (".$rsIndustryIDs['IndustryIDs'].") ";
$SQL .= " AND db_surveyValue IN (1,2,3,4) AND is_submit = 'Y'";
//$SQL .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$BranchID ;
if($type == 'us')
$SQL .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$BranchID ;
else if($type == 'ind')
$SQL .= " AND orgid != ".$orgID;
if(empty($FromDate))
$SQL .= " AND created_date BETWEEN '".date("Y-m-d")." 00:00:00' AND '".date("Y-m-d")." 23:59:59' ";
else
$SQL .= " AND created_date BETWEEN '".date("Y-m-d",strtotime($FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($FromDate))." 23:59:59' ";
//echo $SQL;
//echo "<br /><br />";
$rsCountSurveyToday = mysql_fetch_array(mysql_query($SQL));
// echo $fdate." ".$rsCountSurveyToday['AvgValue'];
// echo "</br>";
$score = $score + number_format($rsCountSurveyToday['AvgValue'],2);
/////////// CODE ONLY FOR PERFORMANCE MONTHLY COMPLETED //////////////////
//}
/////////// CODE ONLY FOR PERFORMANCE WEEKLY FRO US VS. IND. IN SUMMERY PAGE START //////////////////
$endDate = date("Y-m-d");
$startDate = date("Y-m-d",strtotime("-6 days"));
while($startDate <= $endDate){
$returnArr['days'][] = strtoupper(substr(date("D",strtotime($startDate)),0,2));
$SQL = "";
$SQL = "SELECT AVG(db_surveyValue) AS AvgValue FROM beatle_survey WHERE db_surveyPageid IN(".$rsIndustry['db_pagesId'].") ";
if($type == 'us')
$SQL .= " AND db_surveyIndId IN (".$IndID.")";
else if($type == 'ind')
$SQL .= " AND db_surveyIndId IN (".$rsIndustryIDs['IndustryIDs'].") ";
$SQL .= " AND db_surveyValue IN (1,2,3,4) AND is_submit = 'Y'";
if($type == 'us')
$SQL .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$BranchID ;
else if($type == 'ind')
$SQL .= " AND orgid != ".$orgID;
$SQL .= " AND created_date BETWEEN '".date("Y-m-d",strtotime($startDate))." 00:00:00' AND '".date("Y-m-d",strtotime($startDate))." 23:59:59' ";
$rsCountSurveyWeekly = mysql_fetch_array(mysql_query($SQL));
$performanceWeekly = $rsCountSurveyWeekly['AvgValue'];
if(!empty($performanceWeekly))
$returnArr['weekly'][] = $performanceWeekly;
else
$returnArr['weekly'][] = 0;
//$returnArr['weekly'][] = rand(0,4);
$startDate = date("Y-m-d",strtotime($startDate." +1 days"));
} //// CODE FOR WEEKLY PERFORMANCE COMPLETED
//} // IF COMPLETED FOR SUMMEY PAGE
// CODE FOR DASHBARD PAGE AVERAGE VALUE
if($pg == 'dashboardma') {
$fdateDash = $FromDate;
$tdateDash = $ToDate;
$dtcnt = 0;
while(strtotime($fdateDash) <= strtotime($tdateDash)) {
$dtcnt++;
$SQL = "SELECT AVG(db_surveyValue) AS AvgValue FROM beatle_survey WHERE db_surveyPageid IN(".$rsIndustry['db_pagesId'].") ";
if($type == 'us')
$SQL .= " AND db_surveyIndId IN (".$IndID.")";
else if($type == 'ind')
$SQL .= " AND db_surveyIndId IN (".$rsIndustryIDs['IndustryIDs'].") ";
$SQL .= " AND db_surveyValue IN (1,2,3,4) AND is_submit = 'Y'";
if($type == 'us')
$SQL .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$BranchID ;
else if($type == 'ind')
$SQL .= " AND orgid != ".$orgID;
$SQL .= " AND created_date BETWEEN '".date("Y-m-d",strtotime($fdateDash))." 00:00:00' AND '".date("Y-m-d",strtotime($fdateDash))." 23:59:59' ";
//if($type == 'us' && $IndID == 12)
//echo $SQL;
$rsCountSurvey = mysql_fetch_array(mysql_query($SQL));
//echo $FromDate. " # ".$rsCountSurvey[0]['AvgValue']." # ";
$avgValueDB = $avgValueDB + $rsCountSurvey['AvgValue'];
//if($type == 'us' && $IndID == 12)
//echo "<br />".$rsCountSurvey[0]['AvgValue']."<br /><br />";
$fdateDash = date("Y-m-d",strtotime($fdateDash." +1 days"));
}
if($type == 'us'){
if(fetchActiveScoreCheckedData($userId) == "checked"){
//echo "checked";
if($avgValueDB > 0){
$avgValueDB = number_format(($avgValueDB / fetchZeroFeedbackDays($orgID,$BranchID,$IndID,$FromDate,$ToDate)),2);
}else{
$avgValueDB = "0.00";
}
}else{
//echo "not checked";
if($avgValueDB > 0){
$avgValueDB = number_format(($avgValueDB / $dtcnt),2);
}else{
$avgValueDB = "0.00";
}
}
}else{
if($avgValueDB > 0){
$avgValueDB = number_format(($avgValueDB / $dtcnt),2);
}else{
$avgValueDB = "0.00";
}
}
}else{
//echo "XXXX<br />";
$avgValueDB = "0.00";
}
//if($type == 'us') {
//echo $FromDate;
//echo " ## ";
//}
}else{
$returnArr['weekly'] = array(0=>0,1=>0,2=>0,3=>0,4=>0,5=>0,6=>0);
//$returnArr['weekly'] = array(0=>2,1=>2,2=>3,3=>4,4=>4,5=>1,6=>1);
$performance = 0;
$score = 0;
$avgValueDB = "0.00";
}
}
$returnArr['performance'] = $performance;
$returnArr['score'] = $score;
$returnArr['avgValueDB'] = $avgValueDB;
//echo "<pre>";
//print_r($returnArr);
//echo "</pre>";
//$returnArr['performanceWeekly'] = $performanceWeekly;
return $returnArr;
}
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);
}
?>