File: /home/barbeatleanalyti/public_html/api.beatleanalytics.com/corporate/report.php
<?php
// array for JSON response
$response = array();
// include db connect class
require_once 'db_connect.php';
// connecting to db
$db = new DB_CONNECT();
// array for JSON response
$response = array();
if (isset($_POST["pg"],$_POST["userId"],$_POST["utype"],$_POST["orgId"],$_POST["branchId"],$_POST['indId'],$_POST["fromdate"],$_POST["todate"])) {
$pg = $_POST["pg"];
$userId = $_POST["userId"];
$utype = $_POST["utype"];
$orgId = $_POST["orgId"];
$branchId = "";
$indid = "";
$FromDate = $_POST["fromdate"];
$ToDate = $_POST["todate"];
if($_POST["branchId"] == "0"){
$branchId="";
}else{
$branchId = $_POST["branchId"];
}
if($_POST["indId"] == "0"){
$indid="";
}else{
$indid = $_POST["indId"];
}
$SQL = "
SELECT
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_organization
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_organization.OrgId = ".$orgId." ";
$result = mysql_query($SQL)or die(mysql_error());
// $s = mysql_num_rows($result);
// echo "count ".$s;
// for($j=0; $j<mysql_num_rows($result); $j++) {
// $performanceArr = fetchRatingForCurrentIndustry($rsIndustryData[$j]['IndId'],$rsBranches[$i]['branchId'],$rsOrgDetails[0]['OrgId'],'us',$FromDate,$ToDate);
// $row = mysql_fetch_array($j);
// echo $row['db_industry'];
// }
$score = 0;
$performance_ove = 0;
$performance = 0;
$performanceM = 0;
$performanceINDM = 0;
$totalFeedback = array();
$totalFeedback['count'] = 0;
$totalFeedback['guest_total'] = 0;
$totalFeedback['guest_count'] = 0;
$today_lowrating = 0;
$ove_lowrating = 0;
$totalCustomers = 0;
$todaysCustomer = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_array($result)) {
//$performanceArr = fetchRatingForCurrentIndustry($pg,$row['IndId'],$row['branchId'],$row['OrgIDS'],'us',$FromDate,$ToDate);
//$performanceINDArr = fetchRatingForCurrentIndustry($pg,$row['IndId'],$row['branchId'],$row['OrgIDS'],'ind',$FromDate,$ToDate);
// $score = $score + $performanceArr['score'];
//$performance_ove = (float)$performance_ove + (float)$performanceArr['performance'];
$feddbackArr = fetchTotalFeedback($pg,"us",$row['branchId'],$row['IndId'],$row['OrgIDS'],$userId,$utype,$FromDate,$ToDate);
$totalFeedback['count'] = $totalFeedback['count'] + $feddbackArr['count'];
$totalFeedback['guest_total'] = $totalFeedback['guest_total'] + $feddbackArr['guest_total'];
$avgLowRatingArr = fetchAvgLowRating($pg,"us",$row['branchId'],$row['IndId'],$row['OrgIDS'],$userId,$utype,$FromDate,$ToDate);
$today_lowrating = $today_lowrating + $avgLowRatingArr['today_lowrating'];
$ove_lowrating = $ove_lowrating + $avgLowRatingArr['ove_lowrating'];
// $todaysCustomer = $todaysCustomer + FetchCustomerData($pg,$row['branchId'],$row['IndId'],$row['OrgIDS'],$userId,$utype,'Y',$FromDate,$ToDate);
// $totalCustomers = $totalCustomers + FetchCustomerData($pg,$row['branchId'],$row['IndId'],$row['OrgIDS'],$userId,$utype,'N',$FromDate,$ToDate);
}
//monthly report dashboard
$totalIndustry = 0;
$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;
while($row = mysql_fetch_array($rsBranches)) {
$SQL = "SELECT * FROM beatle_industry WHERE db_bracnchid = ".$row['branchId'];
if($indid != "" && !empty($indid)){
$SQL .= " AND IndId = ".$indid." ";
}
if($utype == 'line_manager'){
$SQL .= " AND db_userid = ".$userId." ";
}
$rsIndustry = mysql_query($SQL);
while($row1 = mysql_fetch_array($rsIndustry)) {
$performanceArr = fetchRatingForCurrentIndustry($pg,$userId,$row1['IndId'],$row['branchId'],$orgId,'us',$FromDate,$ToDate);
$performanceINDArr = fetchRatingForCurrentIndustry($pg,$userId,$row1['IndId'],$row['branchId'],$orgId,'ind',$FromDate,$ToDate);
//score
$score = $score + $performanceArr['score'];
$performance_ove = (float)$performance_ove + (float)$performanceArr['performance'];
//customer count
$todaysCustomer = $todaysCustomer + FetchCustomerData($pg,$row['branchId'],$row1['IndId'],$orgId,$userId,$utype,'Y',$FromDate,$ToDate);
$totalCustomers = $totalCustomers + FetchCustomerData($pg,$row['branchId'],$row1['IndId'],$orgId,$userId,$utype,'N',$FromDate,$ToDate);
//monthly reports
$performanceM = $performanceM + $performanceArr['performance'];
$performanceINDM = $performanceINDM + $performanceINDArr['performance'];
}
$score = number_format(($score / mysql_num_rows($rsIndustry)),2);
$performance_ove = number_format(($performance_ove / mysql_num_rows($rsIndustry)),2);
//$performanceINDM = number_format(($performanceINDM / mysql_num_rows($rsIndustry)),2);
$performanceM = $performanceM / mysql_num_rows($rsIndustry);
$performanceINDM = $performanceINDM / mysql_num_rows($rsIndustry);
// count of industry
$totalIndustry = $totalIndustry + mysql_num_rows($rsIndustry);
}
$score = number_format(($score/mysql_num_rows($rsBranches)),2);
$performance_ove = number_format(($performance_ove/mysql_num_rows($rsBranches)),2);
//$performanceINDM = number_format(($performanceINDM/mysql_num_rows($rsBranches)),2);
$performanceM = number_format(($performanceM/mysql_num_rows($rsBranches)),2);
$performanceINDM = number_format(($performanceINDM/mysql_num_rows($rsBranches)),2);
if($pg == 'summery') {
//$finalScore = number_format(($score/mysql_num_rows($result)),2);
//$finalOve = $performance_ove;
$reportDetail = array();
$reportDetail["scoreUs"] = $score;
$reportDetail["scoreOverall"] = $performance_ove;
$reportDetail["feedbackToday"] = $totalFeedback['count'];
$reportDetail["feedbackTotal"] = $totalFeedback['guest_total'];
$reportDetail["lowRatingNew"] = $today_lowrating;
$reportDetail["lowRatingTotal"] = $ove_lowrating;
$reportDetail["customerNew"] = $todaysCustomer;
$reportDetail["customerTotal"] = $totalCustomers;
$reportDetail["complaintToday"] = fetch_ticket_for_tabbing('new','today',$FromDate);
$reportDetail["complaintTotal"] = fetch_ticket_for_tabbing('new','',$FromDate);
$reportDetail["users"] = fetchTotalUser($_POST["orgId"],$_POST["userId"]);
// success
$response["success"] = 1;
// user node
$response["reportDetail"] = array();
array_push($response["reportDetail"], $reportDetail);
}else{
$finalScore = $performanceM;
$finalOve = $performanceINDM;
$reportMDetail = array();
$reportMDetail["scoreUs"] = $finalScore;
$reportMDetail["scoreOthers"] = $finalOve;;
$reportMDetail["feedback"] = $totalFeedback['guest_total'];
$reportMDetail["lowRatingToday"] = $today_lowrating;
$reportMDetail["lowRatingOverall"] = $ove_lowrating;
$reportMDetail["customerNew"] = $todaysCustomer;
$reportMDetail["customerTotal"] = $totalCustomers;
$reportMDetail["complaintTotal"] = fetch_ticket_for_tabbing('','',$FromDate);
$reportMDetail["totalBranch"] = mysql_num_rows($rsBranches);
$reportMDetail["totalIndustry"] = $totalIndustry;
// success
$response["success"] = 1;
// user node
$response["reportMDetail"] = array();
array_push($response["reportMDetail"], $reportMDetail);
}
// echoing JSON response
echo json_encode($response);
}else{
// no industry found
$response["success"] = 0;
$response["message"] = "No data found....";
// echo no users JSON
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 fetchTotalUser($orgID,$userId){
$SQL = "SELECT * FROM beatle_userlogin WHERE userId = ".$userId;
$rsData = mysql_fetch_array(mysql_query($SQL));
$branchid = $rsData['BranchID'];
$indid = $rsData['IndustryID'];
$SQL = "SELECT count(userId) AS TotalUser FROM beatle_userlogin WHERE OrgID = ".$orgID." AND db_usertype != 'End_user' ";
if(!empty($branch) && $branch != 0){
$SQL .= " AND BranchID = ".$branchid." " ;
}
if(!empty($indid) && $indid != 0){
$SQL .= " AND IndustryID = ".$indid." " ;
}
$rsData = mysql_fetch_array(mysql_query($SQL));
$user = $rsData['TotalUser'] - 1;
if($user <= 0)
$user = 0;
return $user;
}
function fetch_ticket_for_tabbing($type,$today='',$FromDate){
if($_POST["utype"] == 'line_manager') {
$SQL = "SELECT * FROM beatle_ticket WHERE
assignto = ".$_POST['userId']." AND
assignto_type = 'line_manager' AND
parentid = 0 ";
}
else if($_POST["utype"] == 'manager') {
$SQL = "SELECT * FROM beatle_ticket WHERE 1 = 1 AND
(assignto_type = 'line_manager' OR assignto_type = 'manager') ";
}else if($_POST["utype"] == 'owner'){
$SQL = "SELECT * FROM beatle_ticket WHERE 1 = 1 ";
}
// $SQL = "";
$SQL .= " AND orgid = ".$_POST["orgId"]." ";
if($_POST["utype"] != 'owner')
$SQL .= " AND branchid = ".$_POST["branchId"]." ";
if($_POST["utype"] == 'manager')
{
$SQL .= " AND indid IN (SELECT IndId FROM beatle_industry WHERE db_bracnchid = ".$_POST["branchId"].")";
}
else if($_POST["utype"] == 'line_manager'){
$SQL .= " AND indid = ".$_POST['indId'];
}
if($type != ''){
if($type == 'new'){
$SQL .= " AND (status = 'new' OR status = 'forward') ";
}else
$SQL .= " AND status = '".$type."' ";
}
if($today == 'today'){
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' ";
}else if($today == 'monthly'){
$prevDate = date("Y-m-d",strtotime("-1 month"));
$curDate = date("Y-m-d");
$SQL .= " AND created_date BETWEEN '".$prevDate." 00:00:00' AND '".$curDate." 23:59:59' ";
}
//$SQL .= " AND active = 'Y' ";
$SQL .= " GROUP BY ticketuid ORDER BY created_date DESC";
//echo $SQL;
$rsUserInfo = mysql_query($SQL);
$cntRtn = mysql_num_rows($rsUserInfo);
if(strlen(mysql_num_rows($rsUserInfo)) == 1)
$cntRtn = "0".mysql_num_rows($rsUserInfo);
return $cntRtn;
}
function FetchCustomerData($pg,$branchid,$indid,$orgID,$userId,$utype,$today = 'N',$FromDate,$ToDate){
//include_once("summery.php");
///$bojSummery = new include_summery();
if($today == 'N') {
if($pg == 'summery') {
$SQL = "SELECT GROUP_CONCAT(DISTINCT db_surveyUserid SEPARATOR ',') AS db_surveyUserid FROM beatle_survey WHERE orgid = ".$orgID." AND is_submit = 'Y' GROUP BY created_date";
$rsUserIDS = mysql_query($SQL);
}else if($pg == 'dashboardma'){
$SQL = "SELECT GROUP_CONCAT(DISTINCT db_surveyUserid SEPARATOR ',') AS db_surveyUserid FROM beatle_survey WHERE orgid = ".$orgID." AND is_submit = 'Y' AND created_date BETWEEN '".date("Y-m-d",strtotime($FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($ToDate))." 23:59:59' GROUP BY created_date";
$rsUserIDS = mysql_query($SQL);
}
}else{
$SQL = "SELECT GROUP_CONCAT(DISTINCT db_surveyUserid SEPARATOR ',') AS db_surveyUserid FROM beatle_survey WHERE orgid = ".$orgID." AND is_submit = 'Y' ";
if($pg == 'summery') {
if(empty($FromDate))
$SQL .= "AND created_date BETWEEN '".date("Y-m-d")." 00:00:00' AND '".date("Y-m-d")." 23:59:59' GROUP BY created_date";
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' GROUP BY created_date";
}else{
$SQL .= "AND created_date BETWEEN '".date("Y-m-d")." 00:00:00' AND '".date("Y-m-d")." 23:59:59' GROUP BY created_date";
}
$rsUserIDS = mysql_query($SQL);
}
if(mysql_num_rows($rsUserIDS) <= 0)
return 0;
$idArr = array();
while ($row = mysql_fetch_array($rsUserIDS)) {
if(!in_array($row['db_surveyUserid'],$idArr)) {
$idArr[] = $row['db_surveyUserid'];
}
}
if(count($idArr) <= 0){
return 0;
}
$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 = 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($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($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." " ;
if($pg == 'summery') {
if($today == 'Y') {
if(empty($FromDate))
$SQL .= " AND beatle_survey.created_date BETWEEN '".date("Y-m-d")." 00:00:00' AND '".date("Y-m-d")." 23:59:59' ";
else
$SQL .= " AND beatle_survey.created_date BETWEEN '".date("Y-m-d",strtotime($FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($FromDate))." 23:59:59' ";
}
}else if($pg == 'dashboardma'){
if($today == 'Y') {
$SQL .= " AND beatle_survey.created_date BETWEEN '".date("Y-m-d")." 00:00:00' AND '".date("Y-m-d")." 23:59:59' ";
}
else if($today == 'N') {
$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_);
}
function fetchAvgLowRating($pg,$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;
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'])) {
if($pg == 'summery') {
$SQL_ = "SELECT count(db_surveyValue) AS AvgValue from beatle_survey WHERE db_surveyPageid IN (".$row1['db_pagesId'].") AND db_surveyIndId IN (".$row1['IndId'].") AND db_surveyValue IN (1,2) AND is_submit = 'Y' ";
$SQL_ .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$row['branchId'];
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' ";
$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'];
}
}
$SQL_ = "";
$SQL_ = "SELECT count(db_surveyValue) 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")." 00:00:00' AND '".date("Y-m-d")." 23:59:59' ";
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' ";
$SQL_ .= " GROUP BY tokenid";
$rsCountSurveyOver = mysql_query($SQL_);
if(mysql_num_rows($rsCountSurveyOver) > 0){
//foreach($rsCountSurveyOver as $key => $val) {
while($row2 = mysql_fetch_array($rsCountSurveyOver)) {
$SUMOve = $SUMOve + $row2['AvgValue'];
}
}
}else if($pg == 'dashboardma'){
$SQL_ = "SELECT count(db_surveyValue) AS AvgValue from beatle_survey WHERE db_surveyPageid IN (".$row1['db_pagesId'].") AND db_surveyIndId IN (".$row1['IndId'].") AND db_surveyValue IN (1,2) 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)) {
$SUM = $SUM + $row2['AvgValue'];
}
}
$SQL_ = "";
$SQL_ = "SELECT count(db_surveyValue) 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";
$rsCountSurveyOver = mysql_query($SQL_);
if(mysql_num_rows($rsCountSurveyOver) > 0){
while($row2 = mysql_fetch_array($rsCountSurveyOver)) {
$SUMOve = $SUMOve + $row2['AvgValue'];
}
}
}
}else{
//$returnArr[] = "0.00";
//echo "NO PAGES FOUND";
}
}
}
$returnArr['today_lowrating'] = $SUM;
$returnArr['ove_lowrating'] = $SUMOve;
return $returnArr;
}
function fetchTotalFeedback($pg,$type,$BranchID,$IndustryID,$orgID,$userId,$utype,$FromDate,$ToDate){
//include_once("summery.php");
//$bojSummery = new include_summery();
$SQL = "SELECT userId FROM beatle_userlogin WHERE db_username = 'Guest' AND db_userLoginName = 'Guest' AND db_usertype = 'End_user' ";
$rsGuest = mysql_fetch_array(mysql_query($SQL));
$guestid = $rsGuest['userId'];
$datetime1 = new DateTime($FromDate);
$datetime2 = new DateTime($ToDate);
$interval = $datetime1->diff($datetime2);
$diff = $interval->format('%a');
$diff = $diff + 1;
//$rsBranches = $bojSummery->fetchBranchesData($BranchID);
$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;
//for($i=0; $i<count($rsBranches); $i++) {
while($row = mysql_fetch_array($rsBranches)) {
//$rsIndustry = $bojSummery->fetchIndustryData($rsBranches[$i]['branchId'],$IndustryID);
$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);
//$cnt = count($rsIndustry);
//for($indst=0; $indst < $cnt; $indst++){
while($row1 = mysql_fetch_array($rsIndustry)) {
//$SQL = "SELECT count(pageId) AS pageId FROM beatle_page WHERE pageId IN (".$row1['db_pagesId'].") AND db_pagetype = 'Content'";
//$rsCountContnentPages = $objDB->sql_query($SQL);
$rsPages = explode(",",$row1['db_pagesId']);
if(count($rsPages) > 0 && !empty($row1['db_pagesId'])) {
#################################
$SQL = "SELECT GROUP_CONCAT(IndId) AS IndustryIDs FROM beatle_industry WHERE db_ind_type_id = ".$row1['db_ind_type_id']."";
$SQL .= " AND IndId = ".$row1['IndId'];
$rsIndustryIDs = mysql_fetch_array(mysql_query($SQL));
#################################
if(!empty($rsIndustryIDs['IndustryIDs'])){
$SQL = "SELECT count(db_surveyValue) AS TotalCount FROM beatle_survey WHERE
db_surveyPageid IN (".$row1['db_pagesId'].") AND
db_surveyValue IN (1,2,3,4) AND is_submit = 'Y' ";
$SQL .= " AND db_surveyIndId IN (".$rsIndustryIDs['IndustryIDs'].") ";
$SQL .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$row['branchId'];
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' ";
$SQL .= " GROUP BY tokenid";
$rsCountSurvey = mysql_query($SQL);
$returnArr['count'] = mysql_num_rows($rsCountSurvey);
//echo "<pre>";
//print_r($rsCountSurvey);
//echo "</pre>";
// TOTAL FEEDBACK
$SQL = "SELECT COUNT(db_surveyValue) AS TotalCount FROM beatle_survey WHERE
db_surveyPageid IN (".$row1['db_pagesId'].") AND
db_surveyValue IN (1,2,3,4) AND is_submit = 'Y' ";
$SQL .= " AND db_surveyIndId IN (".$rsIndustryIDs['IndustryIDs'].") ";
$SQL .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$row['branchId'];
if($pg == 'summery')
$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";
$rsTotalCountSurvey = mysql_query($SQL);
$returnArr['guest_total'] = mysql_num_rows($rsTotalCountSurvey);
// GUEST FEEDBACK
$SQL = "SELECT COUNT(db_surveyValue) AS TotalCount FROM beatle_survey WHERE
db_surveyPageid IN (".$row1['db_pagesId'].") AND
db_surveyValue IN (1,2,3,4) AND is_submit = 'Y' AND db_surveyUserid = ".$guestid;
$SQL .= " AND db_surveyIndId IN (".$rsIndustryIDs['IndustryIDs'].") ";
$SQL .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$row['branchId'];
if($pg == 'summery')
$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";
$rsGuestCountSurvey = mysql_query($SQL);
$returnArr['guest_count'] = mysql_num_rows($rsGuestCountSurvey);
}
else{
//echo "<br /> ELSE HERE <br />";
$returnArr['count'] = 0;
$returnArr['guest_total'] = 0;
$returnArr['guest_count'] = 0;
}
// $avgValue = number_format($rsCountSurvey[0]['AvgValue'],2);
}else{
//echo "NO PAGES FOUND";
}
}
}
//echo "EXIT HERE";
//echo "<pre>";
//print_r($returnArr);
//echo "</pre>";
return $returnArr;
}
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);
}
?>