File: /home/barbeatleanalyti/public_html/api.beatleanalytics.com/corporate/live/monthlydetailreport.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());
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();
$avgValueDB = "";
$totalfeedbackCount ="";
$totalowRating = "";
$totalCustomerCount = "";
$totalComplaint = "";
$branchDetail["industries"] = array();
while ($row1 = mysql_fetch_array($resulti)) {
$indDetail = array();
$indDetail["indid"] = $row1["IndId"];
$indDetail["indName"] = $row1["db_industry"];
$performanceArr = fetchRatingForCurrentIndustry($pg,$utype,$userId,$row1["IndId"],$row["branchId"],$orgID,'us',$FromDate,$ToDate);
/*echo $performanceArr["count"];
echo "<br>";*/
//$avgValueDB = $avgValueDB + $performanceArr['avgValueDB'];
//$indDetail["indScore"] = (($performanceArr['avgValueDB']/5)*100)."%";
$avgValueDB = $avgValueDB + $performanceArr['score'];
$totalfeedbackCount = $totalfeedbackCount + $performanceArr["count"];
$totalowRating = $totalowRating + $performanceArr["lowrating"];
$totalCustomerCount = $totalCustomerCount + $performanceArr["customerCount"];
$totalComplaint = $totalComplaint + $performanceArr["complaint"];
$indDetail["indScore"] = (($performanceArr['score']/5)*100)."%";
$indDetail["indFeedbackCount"] = $performanceArr["count"];
$indDetail["indLowRating"] = $performanceArr["lowrating"];
$indDetail["indCustomerCount"] = $performanceArr["customerCount"];
$indDetail["indComplaint"] = $performanceArr["complaint"];
array_push($branchDetail["industries"], $indDetail);
}
}
$branchDetail["branchScore"] = ((number_format($avgValueDB / mysql_num_rows($resulti),2)/5)*100)."%";
$branchDetail["branchfeedbackCount"] = $totalfeedbackCount;
$branchDetail["branchlowrating"] = $totalowRating;
$branchDetail["branchCustomerCount"] = $totalCustomerCount;
$branchDetail["branchComplaint"] = $totalComplaint;
$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 data 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,$utype,$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;
$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'])) {
/////////// 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));
}
$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,5) 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) && empty($ToDate))
$SQL .= " AND created_date BETWEEN '".date("Y-m-d",strtotime("-30 days"))." 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($ToDate))." 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 //////////////////
///////////feedback count//////////////
$SQL = "SELECT count(db_surveyValue) AS TotalCount FROM beatle_survey WHERE
db_surveyPageid IN (".$rsIndustry['db_pagesId'].") AND
db_surveyValue IN (1,2,3,4,5) AND is_submit = 'Y' ";
$SQL .= " AND db_surveyIndId IN (".$IndID.") ";
$SQL .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$BranchID;
if(empty($FromDate) && empty($ToDate))
$SQL .= " AND created_date BETWEEN '".date("Y-m-d",strtotime("-30 days"))." 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($ToDate))." 23:59:59' ";
$SQL .= " GROUP BY tokenid";
//echo $SQL;
//echo "<br>";
$rsCountSurvey = mysql_query($SQL);
$returnArr['count'] = mysql_num_rows($rsCountSurvey);
//////feedback count completed/////////
//////////////////low rating////////////////////
$SUM = 0;
$SQL = "SELECT count(db_surveyValue) AS AvgValue from beatle_survey WHERE db_surveyPageid IN (".$rsIndustry['db_pagesId'].") AND db_surveyIndId IN (".$IndID.") AND db_surveyValue IN (1,2) AND is_submit = 'Y' ";
$SQL .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$BranchID;
if(empty($FromDate) && empty($ToDate))
$SQL .= " AND created_date BETWEEN '".date("Y-m-d",strtotime("-30 days"))." 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($ToDate))." 23:59:59' ";
$SQL .= " GROUP BY tokenid";
$rsCountSurvey = mysql_query($SQL);
if(mysql_num_rows($rsCountSurvey) > 0){
//foreach($rsCountSurvey as $key => $val) {
while($row2 = mysql_fetch_array($rsCountSurvey)) {
$SUM = $SUM + $row2['AvgValue'];
}
}
$returnArr['lowrating'] = $SUM;
////////////////low rating complete/////////////
///////////////new customer/////////////////////
$SQL = "SELECT GROUP_CONCAT(DISTINCT db_surveyUserid SEPARATOR ',') AS db_surveyUserid FROM beatle_survey WHERE orgid = ".$orgID." AND is_submit = 'Y' ";
if(empty($FromDate) && empty($ToDate))
$SQL .= " AND created_date BETWEEN '".date("Y-m-d",strtotime("-30 days"))." 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($ToDate))." 23:59:59' ";
$rsUserIDS = mysql_query($SQL);
if(mysql_num_rows($rsUserIDS) <= 0){
$returnArr['customerCount'] = 0;
}else{
$idArr = array();
while ($row = mysql_fetch_array($rsUserIDS)) {
if(!in_array($row['db_surveyUserid'],$idArr)) {
$idArr[] = $row['db_surveyUserid'];
}
}
//print_r($idArr);
if(count($idArr) <= 0){
$returnArr['customerCount'] = 0;
}else{
$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
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 IN (".implode(",",$idArr).") ";
$SQL .= " AND beatle_userlogin.db_username <> 'Guest' AND beatle_userlogin.db_userLoginName <> 'Guest' " ;
if(!empty($orgID))
$SQL .= " AND beatle_survey.orgid = ".$orgID." " ;
else
$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 .= " 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' ";
//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_ = mysql_query($SQL);
//return mysql_num_rows($rsUserInfo_);
$returnArr['customerCount'] = mysql_num_rows($rsUserInfo_);
}
}
///////////////new customer complete////////////
////////////////complaint///////////////////////
if($utype == 'line_manager') {
$SQL = "SELECT * FROM beatle_ticket WHERE
assignto = ".$userId." AND
assignto_type = 'line_manager' AND
parentid = 0 ";
}
else if($utype == 'manager') {
$SQL = "SELECT * FROM beatle_ticket WHERE 1 = 1 AND
(assignto_type = 'line_manager' OR assignto_type = 'manager') ";
}else if($utype == 'owner'){
$SQL = "SELECT * FROM beatle_ticket WHERE 1 = 1 ";
}
$SQL .= " AND orgid = ".$orgID." ";
$SQL .= " AND branchid = ".$BranchID." ";
$SQL .= " AND indid = ".$IndID;
$SQL .= " AND (status = 'new' OR status = 'forward') ";
/*if(empty($FromDate))
$curDate = date("Y-m-d");
else
$curDate = date("Y-m-d",strtotime($FromDate));
$SQL .= " AND created_date BETWEEN '".$curDate." 00:00:00' AND '".$curDate." 23:59:59' ";*/
if(empty($FromDate) && empty($ToDate))
$SQL .= " AND created_date BETWEEN '".date("Y-m-d",strtotime("-30 days"))." 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($ToDate))." 23:59:59' ";
$SQL .= " GROUP BY ticketuid ORDER BY created_date DESC";
$rsUserInfo = mysql_query($SQL);
$cntRtn = mysql_num_rows($rsUserInfo);
if(strlen(mysql_num_rows($rsUserInfo)) == 1)
$cntRtn = "0".mysql_num_rows($rsUserInfo);
$returnArr['complaint'] = $cntRtn;
////////////////complaint complete//////////////
}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);
}
?>