File: /home/barbeatleanalyti/public_html/api.beatleanalytics.com/corporate/live/get_lowratings.php
<?php
// 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["fromdate"],$_GET["todate"])) {
$userId = $_GET["userId"];
$utype = $_GET["utype"];
//$orgId = $_GET["orgId"];
//$branchId = "";
//$indid = "";
$FromDate = $_GET["fromdate"];
$ToDate = $_GET["todate"];
/*if($_GET["branchId"] == "0"){
$branchId="";
}else{
$branchId = $_GET["branchId"];
}
if($_GET["indId"] == "0"){
$indid="";
}else{
$indid = $_GET["indId"];
}*/
if($utype == 'owner') {
//$SQL = "SELECT * FROM beatle_branch WHERE {$SQL_B} db_branchOrg = (SELECT OrgId FROM beatle_organization WHERE db_ownerId = ".$userId." )";
$result = mysql_query("SET NAMES utf8"); //the main trick form hindi languages
$result = mysql_query("
SELECT
beatle_userlogin.userId AS userid,
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_userlogin
INNER JOIN beatle_organization ON beatle_userlogin.OrgID = beatle_organization.OrgId
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_userlogin.userId = ".$userId." ")or die(mysql_error());
}else if($utype == 'manager'){
$result = mysql_query("
SELECT
beatle_userlogin.userId AS userid,
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_userlogin
INNER JOIN beatle_branch ON beatle_userlogin.BranchID = beatle_branch.branchId
INNER JOIN beatle_industry ON beatle_branch.branchId = beatle_industry.db_bracnchid
INNER JOIN beatle_organization ON beatle_branch.db_branchOrg = beatle_organization.OrgId
WHERE beatle_userlogin.userId = ".$userId." ")or die(mysql_error());
}else if($utype == 'line_manager'){
$result = mysql_query("
SELECT
beatle_userlogin.userId AS userid,
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_userlogin
INNER JOIN beatle_industry ON beatle_userlogin.IndustryID = beatle_industry.IndId
INNER JOIN beatle_branch ON beatle_industry.db_bracnchid = beatle_branch.branchId
INNER JOIN beatle_organization ON beatle_branch.db_branchOrg = beatle_organization.OrgId
WHERE beatle_userlogin.userId = ".$userId." ")or die(mysql_error());
}
$mainAvgLowRatingArr = array();
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_array($result)) {
//$avgLowRatingArr = fetchAvgLowRating("us",$row['branchId'],$row['IndId'],$row['OrgIDS'],$userId,$utype,$FromDate,$ToDate);
//echo $row['db_pagesId'];
/*echo "<pre>";
print_r(getPageDetails($row['db_pagesId'],'content'));
echo "</pre>";*/
$rsPagesIDS = getPageDetails($row['db_pagesId'],'content');
$avgLowRatingArr = fetchAvgLowRating($rsPagesIDS,$row['branchId'],$row['IndId'],$row['OrgIDS'],$userId,$utype,$FromDate,$ToDate);
/*echo count($avgTotal);
echo "<br>";*/
/*eho c"<pre>";
print_r($avgLowRatingArr['today_lowrating']);
echo "</pre>";*/
$mainAvgLowRatingArr[] = $avgLowRatingArr['today_lowrating'];
//echo count($avgLowRatingArr['today_lowrating']);
//print_r($avgLowRatingArr['today_lowrating'][5]['userid']);
/*for($i=0; $i<count($avgLowRatingArr); $i++) {
echo($avgLowRatingArr[$i]['score']);
echo "<br>";
}*/
}
// echo "<pre>";
// print_r($mainAvgLowRatingArr);
// echo "</pre>";
$mainArr = array();
$new_array1 = array();
$new_array2 = array();
for ($i=0; $i < count($mainAvgLowRatingArr); $i++) {
if(!empty($mainAvgLowRatingArr[$i])){
//$new_array1[] = $mainAvgLowRatingArr[$i];
for ($s=0; $s < count($mainAvgLowRatingArr[$i]); $s++) {
$mainArr[] = $mainAvgLowRatingArr[$i][$s];
}
}
}
// foreach ($mainAvgLowRatingArr as $key => $value) {
// if(count($value) == 1){
// $new_array1[] = $value;
// }else{
// $new_array2[] = $value;
// }
// }
//$mainArr = array_merge($new_array1,$new_array2);
/*echo "<pre>";
print_r($mainArr);
echo "</pre>";*/
$response["success"] = 1;
$response["survey"] = array();
$response['count'] = count($mainArr);
for($i=0; $i<count($mainArr); $i++) {
$sValue = array();
$sValue['userId'] = $mainArr[$i]['userid'];
$sValue['username'] = $mainArr[$i]['username'];
$sValue['userProfile'] = $mainArr[$i]['userProfile'];
$sValue['userLoginName'] = $mainArr[$i]['userLoginName'];
$sValue['userPhone'] = $mainArr[$i]['userPhone'];
$sValue['userEmail'] = $mainArr[$i]['userEmail'];
$sValue['userAddress'] = $mainArr[$i]['userAddress'];
$sValue['userCity'] = $mainArr[$i]['userCity'];
$sValue['userState'] = $mainArr[$i]['userState'];
$sValue['created_date'] = $mainArr[$i]['created_date'];
$sValue['keyword1_title'] = $mainArr[$i]['keyword1_title'];
$sValue['keyword2_title'] = $mainArr[$i]['keyword2_title'];
$sValue['keyword3_title'] = $mainArr[$i]['keyword3_title'];
$sValue['keyword4_title'] = $mainArr[$i]['keyword4_title'];
$sValue['keyword'] = $mainArr[$i]['keyword'];//keyword1
$sValue['servername'] = $mainArr[$i]['servername'];//keyword2
$sValue['paxNo'] = $mainArr[$i]['paxNo'];//keyword3
$sValue['totalBill'] = $mainArr[$i]['totalBill'];//keyword4
$sValue['avgScore'] = $mainArr[$i]['score'];
$sValue['lowScore'] = $mainArr[$i]['lowScore'];
$sValue['aboutUsValue'] = $mainArr[$i]['aboutUsValue'];
$sValue['tokenid'] = $mainArr[$i]['tokenid'];
$sValue['sno'] = $i+1;
$sValue['survey1'] =array();
for($j=0; $j<count($mainArr[$i])-18; $j++) {
$sValue1 = array();
$sValue1['sno'] = $j+1;
$sValue1['pageId'] = $mainArr[$i][$j]['pageId'];
$sValue1['pageName'] = $mainArr[$i][$j]['pageName'];
$sValue1['pageType'] = $mainArr[$i][$j]['pageType'];
$sValue1['pcId'] = $mainArr[$i][$j]['pcId'];
$sValue1['pageConName'] = $mainArr[$i][$j]['pageConName'];
$sValue1['pageConType'] = $mainArr[$i][$j]['pageConType'];
$sValue1['value'] = $mainArr[$i][$j]['value'];
array_push($sValue['survey1'], $sValue1);
}
array_push($response["survey"], $sValue);
}
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 FetchOverAllAvg($pageIDArr,$CustomerID,$OrgID,$BranchID,$IndustryID,$PageContentID="",$tokenID=""){
//$pageIDArr = IndustryPageIDS($pType,$BranchID,$IndustryID);
/*echo "<pre>";
print_r($pageIDArr);
echo "</pre>";*/
$SQL = "SELECT ROUND(AVG(db_surveyValue),2) AS AvgValue FROM beatle_survey WHERE db_surveyPageid IN(".implode(',',$pageIDArr).") AND db_surveyValue IN (1,2,3,4,5) AND is_submit = 'Y' ";
if(!empty($PageContentID))
$SQL .= " AND db_surveyContentId = ".$PageContentID. " ";
if(!empty($tokenID))
$SQL .= " AND tokenid = '".$tokenID. "' ";
/*$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;
//die;
$rsCountSurvey = mysql_fetch_array(mysql_query($SQL));
if(empty($rsCountSurvey['AvgValue']))
$rsCountSurvey['AvgValue'] = "0.00";
return $rsCountSurvey['AvgValue'];
}
function fetchAvgLowRating($rsPagesIDS,$BranchID,$IndustryID,$orgID,$userId,$utype,$FromDate,$ToDate){
$returnArr = array();
$mValue = array();
$totalCustomers = FetchCustomerData($BranchID,$IndustryID,$orgID,$userId,$utype,$FromDate,$ToDate);
while ($row = mysql_fetch_array($totalCustomers)){
$totalAvg = 0;
$noZeroPageCount = 0;
foreach($rsPagesIDS as $val) {
$avgSum = 0;
$noZeroPageConCount = 0;
$rsPagesContent = getPageContentByPageid($val);
while ($pcVal = mysql_fetch_array($rsPagesContent)){
$avgVal = FetchOverAllAvg($rsPagesIDS,$row['db_surveyUserid'],$orgID,$BranchID,$IndustryID,$pcVal['pageConId'],$row['tokenid']);
/*echo $avgVal;
echo "<br>";*/
if($avgVal != 0){
$noZeroPageConCount++;
}
//echo "<br />";
$avgSum = $avgSum + $avgVal;
}
if($avgSum != 0){
$noZeroPageCount++;
}
$totalAvg += round($avgSum/$noZeroPageConCount,2);
}
$totalAvg = round($totalAvg/$noZeroPageCount,2);
$temp = array();
if($totalAvg <= 2){
$temp['userid'] = $row['db_surveyUserid'];
$temp['username'] = getUserDetails($row['db_surveyUserid'])['db_username'];
$temp['userProfile'] = getUserDetails($row['db_surveyUserid'])['webprofileimage'];
$temp['userLoginName'] = getUserDetails($row['db_surveyUserid'])['db_userLoginName'];
$temp['userPhone'] = getUserDetails($row['db_surveyUserid'])['db_phone'];
$temp['userEmail'] = getUserDetails($row['db_surveyUserid'])['db_email'];
$temp['userAddress'] = getUserDetails($row['db_surveyUserid'])['db_address'];
$temp['userCity'] = getLocation(getUserDetails($row['db_surveyUserid'])['db_city'])['cityName'];
$temp['userState'] = getLocation(getUserDetails($row['db_surveyUserid'])['db_state'])['stateName'];
$temp['created_date'] = $row['created_date'];
$temp['keyword1_title'] = getKeywordTitle($IndustryID)['keywordName'];
$temp['keyword2_title'] = getKeywordTitle($IndustryID)['keywordServer'];
$temp['keyword3_title'] = getKeywordTitle($IndustryID)['keywordPax'];
$temp['keyword4_title'] = getKeywordTitle($IndustryID)['keyword4'];
$temp['keyword'] = $row['keyword'];
$temp['paxNo'] = $row['paxNo'];
$temp['totalBill'] = $row['totalBill'];
$temp['servername'] = $row['servername'];
$temp['aboutUsValue'] = "";
$temp['score'] = round($totalAvg,2);
$temp['tokenid'] = $row['tokenid'];
$rsGetSurveyValues = mysql_query("SELECT count(db_surveyValue) AS AvgLowValue FROM beatle_survey WHERE db_surveyValue IN (1,2) AND tokenid = '".$row['tokenid']."'")or die(mysql_error());
if(mysql_num_rows($rsGetSurveyValues) > 0){
while($row1 = mysql_fetch_array($rsGetSurveyValues)) {
$temp['lowScore'] = $row1['AvgLowValue'];
}
}
$rsGetSurvey = mysql_query("SELECT db_surveyPageid, db_surveyContentId, db_surveyValue AS AvgValue FROM beatle_survey WHERE tokenid = '".$row['tokenid']."'")or die(mysql_error());
if(mysql_num_rows($rsGetSurvey) > 0){
$value = array();
while($row2 = mysql_fetch_array($rsGetSurvey)) {
$value['pageId'] = $row2['db_surveyPageid'];
$value['pageName'] = getPages($row2['db_surveyPageid'])['db_pagename'];
$value['pageType'] = getPages($row2['db_surveyPageid'])['db_pagetype'];
$value['pcId'] = $row2['db_surveyContentId'];
$value['pcId'] = $row2['db_surveyContentId'];
$value['pageConName'] = getPageContentName($row2['db_surveyContentId'])['db_pageConTitle'];
$value['pageConType'] = getPageContentName($row2['db_surveyContentId'])['db_pageselection'];
$value['value'] = $row2['AvgValue'];
if($value['pageConType'] == 'multiline'){
$temp['aboutUsValue'] = $row2['AvgValue'];
}
$temp[] = $value;
}
}
$mValue[] = $temp;
}
}
/*echo "<pre>";
print_r($mValue);*/
$returnArr['today_lowrating'] = $mValue ;
//$returnArr['count'] = $SUM ;
return $returnArr;
}
function FetchCustomerData($branchid,$indid,$orgID,$userId,$utype,$FromDate,$ToDate){
$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);
if(mysql_num_rows($rsUserIDS) <= 0)
return array();
$idArr = array();
while ($row = mysql_fetch_array($rsUserIDS)) {
if(!in_array($row['db_surveyUserid'],$idArr)) {
$idArr[] = $row['db_surveyUserid'];
}
}
$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 * FROM beatle_survey WHERE is_submit = 'Y' AND db_surveyUserid IN (".implode(",",$idArr).") AND orgid = ".$_SESSION['OtherInfo']['OrgID']." GROUP BY db_surveyUserid ORDER BY created_date DESC";
//if(count($idArr) > 0) {
$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.keyword,
beatle_survey.paxNo,
beatle_survey.totalBill,
beatle_survey.servername,
beatle_survey.is_submit,
beatle_userlogin.db_phone,
beatle_userlogin.db_userLoginName,
beatle_userlogin.db_email,
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 LOWER(beatle_userlogin.db_usertype) = 'end_user' ";
//AND beatle_survey.db_surveyUserid IN (".implode(",",$idArr).") ";
//if(!empty($this->FromDate) && !empty($this->ToDate)){
$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' ";
//}
$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 .= " GROUP BY beatle_survey.tokenid ORDER BY beatle_survey.created_date DESC";
//echo $SQL;
$rsUserInfo_ = mysql_query($SQL);
return $rsUserInfo_;
/*echo "<pre>";
print_r($rsUserInfo_);*/
}
function getPageContentName($PageContentID){
$SQL ="SELECT db_pageConTitle,db_pageselection FROM beatle_pagecontent where pageConId = ".$PageContentID."";
$rsCountSurvey = mysql_fetch_array(mysql_query($SQL));
/*if ($type == 'title') {
return $rsCountSurvey['db_pageConTitle'];
}else{
return $rsCountSurvey['db_pageselection'];
}*/
return $rsCountSurvey;
}
function getPageContentByPageid($PageID){
$SQL ="SELECT pageConId,db_pageConTitle FROM beatle_pagecontent WHERE db_pageid = ".$PageID."";
$rsCountSurvey = mysql_query($SQL);
/*if ($type == 'title') {
return $rsCountSurvey['db_pageConTitle'];
}else{
return $rsCountSurvey['db_pageselection'];
}*/
return $rsCountSurvey;
}
function getUserDetails($userid){
$SQL = "SELECT db_username,db_userLoginName,db_phone,db_email,webprofileimage,db_address,db_city,db_state FROM beatle_userlogin WHERE db_usertype = 'End_user' AND userId = ".$userid;
$result = mysql_fetch_array(mysql_query($SQL));
return $result;
}
function getPages($PageID){
$SQL ="SELECT db_pagename, db_pagetype FROM beatle_page where pageId = ".$PageID."";
$result = mysql_fetch_array(mysql_query($SQL));
return $result;
}
function getKeywordTitle($IndustryID){
$SQL = "SELECT keywordName,keywordServer,keywordPax,keywordPax FROM beatle_industry WHERE IndId = ".$IndustryID;
$result = mysql_fetch_array(mysql_query($SQL));
return $result;
}
function getPageDetails($PageID,$PageType){
$SQL ="SELECT GROUP_CONCAT(pageId) AS PageID FROM beatle_page where pageId IN (".$PageID.") AND LOWER(db_pagetype) = '".strtolower($PageType)."'";
$rsPages = mysql_fetch_array(mysql_query($SQL));
if(!empty($rsPages['PageID'])) {
$rsArr = explode(",",$rsPages['PageID']);
$returnArr = array();
foreach($rsArr as $key => $val){
if(!in_array($val,$returnArr))
$returnArr[] = $val;
}
return $returnArr;
}else {
return array();
}
//$result = mysql_fetch_array(mysql_query($SQL));
//return $result;
}
function getLocation($cityID){
// $SQL = "SELECT name FROM cities WHERE id = ".$cityID;
// $result = mysql_fetch_array(mysql_query($SQL));
// return $result;
$SQL = "
SELECT
cities.id AS cityID,
cities.name AS cityName,
states.id AS stateID,
states.name AS stateName,
countries.id AS countryID,
countries.sortname AS countryShortname,
countries.name AS countryName
FROM
cities
INNER JOIN states ON cities.state_id = states.id
INNER JOIN countries ON states.id = countries.id
where cities.id = ".$cityID;
$result = mysql_fetch_array(mysql_query($SQL));
return $result;
}
function getStateName($stateID){
$SQL = "SELECT name FROM states WHERE id = ".$stateID;
$result = mysql_fetch_array(mysql_query($SQL));
return $result;
}
function fetchIndustryWiseAverageName($avgValue,$PageContentID){
global $objDB;
$SQL ="SELECT db_pageChoice FROM beatle_pagecontent where pageConId = ".$PageContentID."";
$result = $objDB->sql_query($SQL);
$test = '';
$mark=explode(',', $result[0]['db_pageChoice']);
if ($avgValue > 0 and $avgValue <= 1){
$test = $mark[3];
}else if($avgValue > 1 and $avgValue <= 2){
$test = $mark[2];
}else if($avgValue > 2 and $avgValue <= 3){
$test = $mark[1];
}else if($avgValue > 3 and $avgValue <= 4){
$test = $mark[0];
}
return $test;
}
?>
<!-- SELECT
beatle_userlogin.userId AS userid,
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_userlogin
INNER JOIN beatle_organization ON beatle_userlogin.OrgID = beatle_organization.OrgId
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_userlogin.userId = 164
line_manager
SELECT
beatle_userlogin.userId AS userid,
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_userlogin
INNER JOIN beatle_industry ON beatle_userlogin.IndustryID = beatle_industry.IndId
INNER JOIN beatle_branch ON beatle_industry.db_bracnchid = beatle_branch.branchId
INNER JOIN beatle_organization ON beatle_branch.db_branchOrg = beatle_organization.OrgId
WHERE beatle_userlogin.userId = 151
manager
SELECT
beatle_userlogin.userId AS userid,
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_userlogin
INNER JOIN beatle_branch ON beatle_userlogin.BranchID = beatle_branch.branchId
INNER JOIN beatle_industry ON beatle_branch.branchId = beatle_industry.db_bracnchid
INNER JOIN beatle_organization ON beatle_branch.db_branchOrg = beatle_organization.OrgId
WHERE beatle_userlogin.userId = 154 -->