File: /home/barbeatleanalyti/www/api.beatleanalytics.com/webapi/report.php
<?php
header('Access-Control-Allow-Origin: https://jodhpurstation.in');
// 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($_GET["pg"],$_GET["userId"],$_GET["utype"],$_GET["orgId"],$_GET["fromdate"],$_GET["todate"])) {
$pg = $_GET["pg"];
$userId = $_GET["userId"];
$utype = $_GET["utype"];
$orgId = $_GET["orgId"];
$branchId = "";
$indid = "";
$FromDate = date("Y-m-d",strtotime($_GET["fromdate"]));
$ToDate = date("Y-m-d",strtotime($_GET["todate"]));
$result = mysql_query("
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." ")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;
$totalFeedback['countAll'] = 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['countAll'] = $totalFeedback['countAll'] + $feddbackArr['countAll'];//countMonthly
$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;
if($utype == 'owner') {
$SQL = "SELECT * FROM beatle_branch WHERE db_branchOrg = (SELECT OrgId FROM beatle_organization WHERE db_ownerId = ".$userId." )";
}else if($utype == 'manager'){
$SQL = "SELECT * FROM beatle_branch WHERE db_branchManager = ".$userId." ";
}else if($utype == 'line_manager'){
$SQL = "SELECT * FROM beatle_branch WHERE branchId = (SELECT BranchID FROM beatle_userlogin WHERE userId = ".$userId.") ";
}
$rsBranches = mysql_query($SQL);
$returnArr = array();
$weeklyPerformanceFinalArr = 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);
$indScoreGraph = array();
$iKey=0;
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'];
$weeklyPerformanceFinalArr['weekly_feedback_total'][] = $performanceArr['weekly_feedback_total'];
//$scoreWeekly = $scoreWeekly + $performanceArr['scoreWeekly'];
//echo $performanceArr['scoreWeekly'];
//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'];
$indScoreGraph[] = $performanceArr['scoreWeekly'];
}
$returnArr['indScoreGraph'][] = $indScoreGraph;
$score = number_format(($score / mysql_num_rows($rsIndustry)),2);
$performance_ove = number_format(($performance_ove / 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);
}
// echo "<pre>";
// print_r($returnArr['indScoreGraph']);
// echo "<pre>";
$score = number_format(($score/mysql_num_rows($rsBranches)),2);
$performance_ove = number_format(($performance_ove/mysql_num_rows($rsBranches)),2);
$performanceM = number_format(($performanceM/mysql_num_rows($rsBranches)),2);
//$performanceINDM = number_format(($performanceINDM/mysql_num_rows($rsBranches)),2);
$perforArr = array();
$perforArr['wfbtotal'] = array(0,0,0,0,0,0,0);
for($wpday = 0; $wpday < 7; $wpday++) {
$perforArr['wfbtotal'][$wpday];
for($wp = 0; $wp<count($weeklyPerformanceFinalArr['weekly_feedback_total']); $wp++ ){
$perforArr['wfbtotal'][$wpday] = (float)number_format(($perforArr['wfbtotal'][$wpday] + $weeklyPerformanceFinalArr['weekly_feedback_total'][$wp][$wpday]),2);
}
}
$FromDate = date("Y-m-d",strtotime("-1 day ".$FromDate));
$datetime1 = new DateTime($FromDate);
$datetime2 = new DateTime($ToDate);
$interval = $datetime1->diff($datetime2);
$diff = $interval->format('%a');
$ArrArr = array();
for($dt=0; $dt<$diff; $dt++){
$avg2 = 0;
for($cnt=0; $cnt<count($returnArr['indScoreGraph']); $cnt++){
for($c=count($returnArr['indScoreGraph'][$cnt]);$c>=0 ; $c--){
@$avg2 += $returnArr['indScoreGraph'][$cnt][$c][$dt];
}
$avg2 = $avg2/count($returnArr['indScoreGraph'][$cnt]);
}
$ArrArr[0][] = number_format($avg2/count($returnArr['indScoreGraph']),2);
}
$finalAvgArr[0] = $ArrArr[0];//us
// echo "<pre>";
// print_r($finalAvgArr[0]);
// echo "<pre>";
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["feedbackAll"] = $totalFeedback['countAll'];
$reportDetail["feedbackTotal"] = $totalFeedback['guest_total'];
$reportDetail["weeklyFeedbackCount1"] = $perforArr['wfbtotal'][0];
$reportDetail["weeklyFeedbackCount2"] = $perforArr['wfbtotal'][1];
$reportDetail["weeklyFeedbackCount3"] = $perforArr['wfbtotal'][2];
$reportDetail["weeklyFeedbackCount4"] = $perforArr['wfbtotal'][3];
$reportDetail["weeklyFeedbackCount5"] = $perforArr['wfbtotal'][4];
$reportDetail["weeklyFeedbackCount6"] = $perforArr['wfbtotal'][5];
$reportDetail["weeklyFeedbackCount7"] = $perforArr['wfbtotal'][6];
//$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($_GET["orgId"],$_GET["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["indScoreGraph"] = $finalAvgArr[0];
// $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;
$response["success"] = 1;
$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($branchid) && $branchid != 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($_GET["utype"] == 'line_manager') {
$SQL = "SELECT * FROM beatle_ticket WHERE
assignto = ".$_GET['userId']." AND
assignto_type = 'line_manager' AND
parentid = 0 ";
}
else if($_GET["utype"] == 'manager') {
$SQL = "SELECT * FROM beatle_ticket WHERE 1 = 1 AND
(assignto_type = 'line_manager' OR assignto_type = 'manager') ";
}else if($_GET["utype"] == 'owner'){
$SQL = "SELECT * FROM beatle_ticket WHERE 1 = 1 ";
}
// $SQL = "";
$SQL .= " AND orgid = ".$_GET["orgId"]." ";
if($_GET["utype"] != 'owner')
$SQL .= " AND branchid = ".$_GET["branchId"]." ";
if($_GET["utype"] == 'manager')
{
$SQL .= " AND indid IN (SELECT IndId FROM beatle_industry WHERE db_bracnchid = ".$_GET["branchId"].")";
}
else if($_GET["utype"] == 'line_manager'){
$SQL .= " AND indid = ".$_GET['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){
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){
if($utype == 'owner') {
$SQL = "SELECT * FROM beatle_branch WHERE db_branchOrg = (SELECT OrgId FROM beatle_organization WHERE db_ownerId = ".$userId." )";
}else if($utype == 'manager'){
$SQL = "SELECT * FROM beatle_branch WHERE db_branchManager = ".$userId." ";
}else if($utype == 'line_manager'){
$SQL = "SELECT * FROM beatle_branch WHERE 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,5) 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,5) 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);
if($utype == 'owner') {
$SQL = "SELECT * FROM beatle_branch WHERE db_branchOrg = (SELECT OrgId FROM beatle_organization WHERE db_ownerId = ".$userId." )";
}else if($utype == 'manager'){
$SQL = "SELECT * FROM beatle_branch WHERE db_branchManager = ".$userId." ";
}else if($utype == 'line_manager'){
$SQL = "SELECT * FROM beatle_branch WHERE 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,5) 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>";
$startDate= date("d-m-Y",strtotime("-30 days"));
//$alldata['ToDate'] = date("d-m-Y");
$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,5) AND is_submit = 'Y' ";
$SQL .= " AND db_surveyIndId IN (".$rsIndustryIDs['IndustryIDs'].") ";
$SQL .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$row['branchId'];
$SQL .= " GROUP BY tokenid";
$rsCountSurvey = mysql_query($SQL);
$returnArr['countAll'] = mysql_num_rows($rsCountSurvey);
// 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,5) 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,5) 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;
$returnArr['countMonthly'] = 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 = array();
$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,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;
$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,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))
$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,5) 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);
$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;
$SQL .= " AND created_date BETWEEN '".date("Y-m-d",strtotime($startDate))." 00:00:00' AND '".date("Y-m-d",strtotime($startDate))." 23:59:59' ";
$SQL .= " GROUP BY tokenid";
//$rsTotalCount = $objDB->sql_query($SQL);
//$returnArr['weekly_feedback_total'][] = count($rsTotalCount);
$rsGuestCountSurvey = mysql_query($SQL);
$returnArr['weekly_feedback_total'][] = mysql_num_rows($rsGuestCountSurvey);
//echo mysql_num_rows($rsGuestCountSurvey);
$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,5) 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']." # ";
//echo $rsCountSurvey['AvgValue'];echo "<br>";
$avgValueDB = $avgValueDB + $rsCountSurvey['AvgValue'];
$scoreWeekly[] = round($rsCountSurvey['AvgValue'],2);
//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;
$returnArr['scoreWeekly'] = $scoreWeekly;
//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);
}
?>