File: /home/barbeatleanalyti/public_html/manage.beatleanalytics.com/site/include/summery.php 14-6-2019
<?php
/* <summary>
Class contains methods related to state module
<author>
Beatle Buddy 2017.
</author>
Version 1.0
</summary>
*/
class include_summery
{
/*
<summary>
Default constructor
</summary>
*/
function __CONSTRUCT(){
}
public function FetchOrganizationName(){
global $objDB;
$SQL = "SELECT
beatle_organization.db_Orgname,
beatle_organization.OrgId,
beatle_organization.db_orgtype
FROM
beatle_organization
RIGHT JOIN beatle_userlogin ON beatle_organization.OrgId = beatle_userlogin.OrgID WHERE beatle_userlogin.userId = ".$_SESSION['UserInfo']['UserID']." ";
$rsUserInfo = $objDB->sql_query($SQL);
return $rsUserInfo;
}
public function fetchBranchesData($BranchID,$IndustryID){
global $objDB;
$SQL_B = "";
if(!empty($BranchID)){
$SQL_B = " branchId = ".$BranchID." AND ";
}
if(strtolower($_SESSION['UserInfo']['UType']) == 'owner') {
$SQL = "SELECT * FROM beatle_branch WHERE {$SQL_B} db_branchOrg = (SELECT OrgId FROM beatle_organization WHERE db_ownerId = ".$_SESSION['UserInfo']['UserID']." )";
}else if(strtolower($_SESSION['UserInfo']['UType']) == 'manager'){
$SQL = "SELECT * FROM beatle_branch WHERE {$SQL_B} db_branchManager = ".$_SESSION['UserInfo']['UserID']." ";
}else if(strtolower($_SESSION['UserInfo']['UType']) == 'line_manager'){
$SQL = "SELECT * FROM beatle_branch WHERE {$SQL_B} branchId = (SELECT BranchID FROM beatle_userlogin WHERE userId = ".$_SESSION['UserInfo']['UserID'].") ";
}
//echo $SQL;
//echo "<br /> <pre>";
$rsBranches = $objDB->sql_query($SQL);
//print_r($rsBranches);
//die;
return $rsBranches;
}
public function fetchIndustryData($branchID,$IndustryID){
global $objDB;
$SQL = "SELECT * FROM beatle_industry WHERE db_bracnchid = ".$branchID;
if($IndustryID != "" && !empty($IndustryID)){
$SQL .= " AND IndId = ".$IndustryID." ";
}
if(strtolower($_SESSION['UserInfo']['UType']) == 'line_manager'){
$SQL .= " AND db_userid = ".$_SESSION['UserInfo']['UserID']." ";
}
//echo $SQL;
$rsIndustry = $objDB->sql_query($SQL);
return $rsIndustry;
}
// Method to fetch all user info.
public function FetchAllUserInfo(){
global $objDB;
$SQL = "SELECT * FROM admin_master WHERE IsActive='Y' AND IsDeleted='N' ORDER BY AdminID DESC LIMIT 5";
$rsUserInfo = $objDB->sql_query($SQL);
return $rsUserInfo;
}
public function fetchRatingForCurrentBranch($branchID){
global $objDB;
$SQL = "SELECT * FROM beatle_survey WHERE db_surveyBranchid = ".$branchID;
$rsSurveys = $objDB->sql_query($SQL);
return $rsSurveys;
}
public function fetchFurtherRecommendation($IndID,$BranchID,$orgID,$type,$FromDate,$ToDate){
global $objDB;
//echo "<pre>";
$SQL = "SELECT db_ind_type_id, db_pagesId FROM beatle_industry WHERE IndId = ".$IndID;
$rsIndustry = $objDB->sql_query($SQL);
//$indType = $rsIndustry[0]['db_industry'];
//echo "<br />".$type."#".$indType."<br />";
$SQL = "SELECT GROUP_CONCAT(IndId) AS IndustryIDs FROM beatle_industry WHERE db_ind_type_id = ".$rsIndustry[0]['db_ind_type_id']."";
$rsIndustryIDs = $objDB->sql_query($SQL);
//echo '<pre>';
//print_r($rsIndustry[0]['db_pagesId']);
//echo '</pre>';
//echo "<br /><br />";
$SQL_ = "SELECT
beatle_page.pageId,
beatle_pagecontent.pageConId,
beatle_pagecontent.db_pageConTitle,
beatle_page.db_pagename,
beatle_page.db_pagetype
FROM
beatle_page
INNER JOIN beatle_pagecontent ON beatle_page.pageId = beatle_pagecontent.db_pageid
WHERE
beatle_page.pageId IN (".$rsIndustry[0]['db_pagesId'].") AND
beatle_page.db_pagetype = 'Question' AND
beatle_pagecontent.db_pageConTitle IN ('Will You Recommend Us','Will You Visit Again?','Would you like to Visit this Station Again? /क्या आप इस रेलवे स्टेशन फिर से आना चाहेंगे ?') ";
//echo $SQL_."<br /><br />";die();
$rapageArrid = $objDB->sql_query($SQL_);
if(count($rapageArrid) <= 0)
return 0;
$pageContentId_ = $rapageArrid[0]['pageConId'];
$pageid_ = $rapageArrid[0]['pageId'];
$returnArr = array();
$returnArr['total'] = 0;
$returnArr['count'] = 0;
$avgValue = "0.00";
if(count($rsIndustry) > 0 && count($rsIndustryIDs) > 0){
//echo "<br />";
//echo $rsIndustry[0]['db_pagesId'];
//echo $rsIndustryIDs[0]['IndustryIDs'];
//echo "<br /><br />";
$rsPages = explode(",",$rsIndustry[0]['db_pagesId']);
//print_r($rsPages);
if(!empty($rsIndustry[0]['db_pagesId']) && !empty($rsIndustryIDs[0]['IndustryIDs'])) {
$SQL = "SELECT db_surveyValue AS AvgValue FROM beatle_survey WHERE db_surveyPageid = ".$pageid_." AND db_surveyContentId = ".$pageContentId_;
$SQL .= " AND db_surveyIndId IN (".$IndID.")";
$SQL .= " AND db_surveyValue IN ('Yes','Yes/ हाँ') AND is_submit = 'Y'";
$SQL .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$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 .= " GROUP BY tokenid";
//echo $SQL;
//echo "<br />";
$rsCountSurvey = $objDB->sql_query($SQL);
$returnArr['yes'] = count($rsCountSurvey);
$SQL = "SELECT db_surveyValue AS AvgValue FROM beatle_survey WHERE db_surveyPageid = ".$pageid_." AND db_surveyContentId = ".$pageContentId_;
$SQL .= " AND db_surveyIndId IN (".$IndID.")";
$SQL .= " AND db_surveyValue IN ('No','No/ ना') AND is_submit = 'Y'";
$SQL .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$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 .= " GROUP BY tokenid";
$rsCountSurvey = $objDB->sql_query($SQL);
$returnArr['no'] = count($rsCountSurvey);
//echo $SQL;
//echo "<br /><br /><br />";
}else{
$avgValue = "0";
}
}
return $returnArr;
}
public function fetchRatingForOhterIndustry($orgID,$orgType,$type,$FromDate,$ToDate){
global $objDB;
//$SQL = "SELECT OrgId AS OrgIDS FROM beatle_organization WHERE LOWER(db_orgtype) = '".strtolower($orgType)."' AND OrgId != ".$orgID;
$SQL = "SELECT OrgId AS OrgIDS FROM beatle_organization WHERE OrgId != ".$orgID;
//echo "<br />";
$rsOrganizations = $objDB->sql_query($SQL);
$avgValue = "0.00";
if(count($rsOrganizations) > 0) {
$averageArr = array();
foreach($rsOrganizations as $orgKey => $orgVal){
$OrgIDS_ = $orgVal['OrgIDS'];
$SQL = "SELECT branchId AS BranchID FROM beatle_branch WHERE db_branchOrg = ".$OrgIDS_;
//echo "<br />";
$rsBranches = $objDB->sql_query($SQL);
for($b=0; $b<count($rsBranches); $b++){
$SQL = "SELECT IndId,db_pagesId FROM beatle_industry WHERE db_bracnchid =".($rsBranches[$b]['BranchID'])." ";
//echo "<br />";
$rsIndustry = $objDB->sql_query($SQL);
for($indst=0; $indst<count($rsIndustry); $indst++){
$rsPages = explode(",",$rsIndustry[$indst]['db_pagesId']);
if(count($rsPages) > 0 && !empty($rsIndustry[0]['db_pagesId'])) {
$SQL = "SELECT AVG(db_surveyValue) AS AvgValue FROM beatle_survey WHERE db_surveyPageid IN (".$rsIndustry[$indst]['db_pagesId'].") AND db_surveyIndId IN (".$rsIndustry[$indst]['IndId'].") AND db_surveyValue IN (1,2,3,4,5) AND is_submit = 'Y' ";
if($type == 'us')
$SQL .= " AND orgid = ".$OrgIDS_. " AND db_surveyBranchid = ".$BranchID ;
else if($type == 'ind')
$SQL .= " AND orgid != ".$OrgIDS_;
$SQL .= " AND created_date BETWEEN '".date("Y-m-d",strtotime($FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($ToDate))." 23:59:59' ";
$rsCountSurvey = $objDB->sql_query($SQL);
$avgValue = number_format($rsCountSurvey[0]['AvgValue'],2);
}else{
//echo "NO PAGES FOUND";
}
}
}
}
}
return $avgValue;
}
public function fetchAvgFeedback($type,$BranchID,$IndustryID,$orgID){
global $objDB;
//echo $this->FromDate;
//echo "<br />";
//echo $this->ToDate;
$datetime1 = new DateTime($this->FromDate);
$datetime2 = new DateTime($this->ToDate);
$interval = $datetime1->diff($datetime2);
$diff = $interval->format('%a');
$diff = $diff + 1;
//$SQL = "SELECT MIN(created_date) AS FromDate, MAX(created_date) AS ToDate, DATEDIFF(MAX(created_date),MIN(created_date)) AS TotalDays, COUNT(db_surveyValue) AS Count_ from beatle_survey";
$rsBranches = $this->fetchBranchesData($BranchID);
/*
echo "<pre>";
print_r($rsBranches);
echo "</pre>";
*/
$returnArr = array();
$avgUSTotal = 0;
for($i=0; $i<count($rsBranches); $i++) {
$rsIndustry = $this->fetchIndustryData($rsBranches[$i]['branchId'],$IndustryID);
// echo "<pre>";
// print_r($rsIndustry);
// echo $rsIndustry[0]['db_pagesId']." 0 @@ <br />";
// echo $rsIndustry[1]['db_pagesId']." 1 @@ <br />";
$cnt = count($rsIndustry);
//echo "</pre>";
for($indst=0; $indst < $cnt; $indst++){
//echo "||".$indst."|| ";
//echo $rsIndustry[1]['db_IndUnqId']." @@ <br />";
//echo $rsIndustry[1]['db_pagesId']." @@ <br />";
$SQL = "SELECT count(pageId) AS pageId FROM beatle_page WHERE pageId IN (".$rsIndustry[$indst]['db_pagesId'].") AND db_pagetype = 'Content'";
//if(empty($rsIndustry[$indst]['db_pagesId'])){
// echo $SQL." > ".$indst." # ".$rsIndustry[1]['db_pagesId']." > ".$indst;
// die;
//}
$rsCountContnentPages = $objDB->sql_query($SQL);
$rsPages = explode(",",$rsIndustry[$indst]['db_pagesId']);
if(count($rsPages) > 0 && !empty($rsIndustry[$indst]['db_pagesId'])) {
#################################
//$SQL = "SELECT * FROM beatle_industry WHERE IndId = ".$rsIndustry[$indst]['IndId'];
//$rsIndustry = $objDB->sql_query($SQL);
//echo $rsIndustry[$indst]['db_industry']."<br />";
$SQL = "SELECT GROUP_CONCAT(IndId) AS IndustryIDs FROM beatle_industry WHERE db_ind_type_id = ".$rsIndustry[$indst]['db_ind_type_id']."";
if($type == 'ind')
$SQL .= " AND IndId != ".$rsIndustry[$indst]['IndId'];
else
$SQL .= " AND IndId = ".$rsIndustry[$indst]['IndId'];
//echo $SQL." ## <br />";
$rsIndustryIDs = $objDB->sql_query($SQL);
//print_r($rsIndustryIDs[0]['IndustryIDs']);
#################################
if(!empty($rsIndustryIDs[0]['IndustryIDs'])){
$SQL = "SELECT COUNT(db_surveyValue) AS TotalCount FROM beatle_survey WHERE db_surveyPageid IN (".$rsIndustry[$indst]['db_pagesId'].") AND db_surveyValue IN (1,2,3,4,5) AND is_submit = 'Y' ";
//if(!empty($IndustryID)){
// echo " >> ";
// $SQL .= " AND db_surveyIndId = ".$IndustryID;
//}else{
$SQL .= " AND db_surveyIndId IN (".$rsIndustryIDs[0]['IndustryIDs'].") ";
//}
if($type == 'us')
$SQL .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$rsBranches[$i]['branchId'];
else if($type == 'ind')
$SQL .= " AND orgid != ".$orgID;
$SQL .= " AND created_date BETWEEN '".date("Y-m-d",strtotime($this->FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($this->ToDate))." 23:59:59' ";
$SQL .= " GROUP BY tokenid";
//echo $SQL;
$rsCountSurvey = $objDB->sql_query($SQL);
// if($rsCountSurvey[0]['TotalDays'] == 0)
// $rsCountSurvey[0]['TotalDays'] = 1;
//print_r($rsCountSurvey);
//echo $rsCountSurvey[0]['TotalCount']." # ".$diff;
$avg = count($rsCountSurvey) / $diff ;
//echo $SQL;
//$avg = $rsCountSurvey[]['']/
$returnArr[] = $avg;
}
else{
//echo "<br /> ELSE HERE <br />";
$returnArr[] = 0;
}
// $avgValue = number_format($rsCountSurvey[0]['AvgValue'],2);
}else{
//echo "NO PAGES FOUND";
}
}
}
//echo "EXIT HERE";
//print_r($returnArr);
return $avg = number_format(array_sum($returnArr)/count($returnArr),2);
}
public function fetchLetestComments(){
global $objDB;
$SQL = "SELECT GROUP_CONCAT(pageConId) AS pageConIds FROM beatle_pagecontent WHERE db_pageselection = 'multiline' ";
$rsCID = $objDB->sql_query($SQL);
//$pagecontentid = $rsCID[0]['pageConId'];
$orgid = 0;
$indid = 0;
$brnid = 0;
if(strtolower($_SESSION['UserInfo']['UType']) == 'owner') {
$orgid = $_SESSION['OtherInfo']['OrgID'];
}else if(strtolower($_SESSION['UserInfo']['UType']) == 'manager'){
$orgid = $_SESSION['OtherInfo']['OrgID'];
$brnid = $_SESSION['OtherInfo']['BranchID'];
}else if(strtolower($_SESSION['UserInfo']['UType']) == 'line_manager'){
$orgid = $_SESSION['OtherInfo']['OrgID'];
$brnid = $_SESSION['OtherInfo']['BranchID'];
$indid = $_SESSION['OtherInfo']['IndustryID'];
}
$SQL = "SELECT GROUP_CONCAT(DISTINCT db_surveyUserid SEPARATOR ',') AS db_surveyUserid FROM beatle_survey WHERE orgid = ".$_SESSION['OtherInfo']['OrgID']." AND is_submit = 'Y' GROUP BY tokenid";
$rsUserIDS = $objDB->sql_query($SQL);
if(count($rsUserIDS) <= 0)
return array();
$idArr = array();
for($i=0; $i<count($rsUserIDS); $i++){
if(!in_array($rsUserIDS[$i]['db_surveyUserid'],$idArr)) {
$idArr[] = $rsUserIDS[$i]['db_surveyUserid'];
}
}
if(count($idArr) <= 0){
return array();
}
$SQL = "SELECT userId FROM beatle_userlogin WHERE userId IN (".implode(',',$idArr).") AND lower(db_usertype) = 'end_user'";
$rsUserInfo = $objDB->sql_query($SQL);
$idArr = array();
for($i=0; $i<count($rsUserInfo); $i++){
if(!in_array($rsUserInfo[$i]['userId'],$idArr)) {
$idArr[] = $rsUserInfo[$i]['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.created_date,
beatle_survey.tokenid,
beatle_survey.db_surveyIndId,
beatle_survey.db_surveyBranchid,
beatle_survey.db_surveyContentId,
beatle_survey.db_surveyValue,
beatle_survey.tokenid,
beatle_userlogin.webprofileimage,
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).") AND beatle_survey.db_surveyContentId IN (".$rsCID[0]['pageConIds'].") ";
//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) && $orgid != 0)
$SQL .= " AND beatle_survey.orgid = ".$orgid." " ;
else
$SQL .= " AND beatle_survey.orgid = ".$_SESSION['OtherInfo']['OrgID']." " ;
if(!empty($brnid) && $brnid != 0)
$SQL .= " AND beatle_survey.db_surveyBranchid = ".$brnid." " ;
if(!empty($indid))
$SQL .= " AND beatle_survey.db_surveyIndId = ".$indid." " ;
//if(!empty($this->TokenID))
//$SQL .= " AND beatle_survey.tokenid = '".$this->TokenID."' " ;
$SQL .= " GROUP BY beatle_survey.tokenid ORDER BY beatle_survey.created_date DESC LIMIT 10";
$rsUserInfo_ = $objDB->sql_query($SQL);
return $rsUserInfo_;
}
public function fetchLowratingforTodayComments($type,$BranchID,$IndustryID,$tokenid, $orgID){
global $objDB;
// $rsBranches = $this->fetchBranchesData($BranchID);
$returnArr = array();
$returnArr['avg_lowrating'] = "0.00";
$returnArr['lowrating'] = 0;
$avgUSTotal = 0;
$SUM = 0;
$SUMOve = 0;
//for($i=0; $i<count($rsBranches); $i++) {
$rsIndustry = $this->fetchIndustryData($BranchID,$IndustryID);
for($indst=0; $indst<count($rsIndustry); $indst++){
$rsPages = explode(",",$rsIndustry[$indst]['db_pagesId']);
if(count($rsPages) > 0 && !empty($rsIndustry[0]['db_pagesId'])) {
$SQL_ = "SELECT AVG(db_surveyValue) AS AvgValue from beatle_survey WHERE db_surveyPageid IN (".$rsIndustry[$indst]['db_pagesId'].") AND db_surveyIndId = ".$IndustryID." AND db_surveyValue IN (1,2,3,4,5) AND is_submit = 'Y' ";
$SQL_ .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$BranchID;
//$SQL_ .= " AND created_date BETWEEN '".date("Y-m-d")." 00:00:00' AND '".date("Y-m-d")." 23:59:59' ";
$SQL_ .= " AND tokenid = '".$tokenid."'";
$SQL_ .= " GROUP BY tokenid";
$rsCountSurvey = $objDB->sql_query($SQL_);
if(count($rsCountSurvey) > 0){
foreach($rsCountSurvey as $key => $val) {
$SUM = $SUM + $val['AvgValue'];
}
}
$SQL_ = "";
$SQL_ = "SELECT count(db_surveyValue) AS AvgValue from beatle_survey WHERE db_surveyPageid IN (".$rsIndustry[$indst]['db_pagesId'].") AND db_surveyIndId = ".$IndustryID." AND db_surveyValue IN (1,2) AND is_submit = 'Y' ";
$SQL_ .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$BranchID;
//$SQL_ .= " AND created_date BETWEEN '".date("Y-m-d")." 00:00:00' AND '".date("Y-m-d")." 23:59:59' ";
$SQL_ .= " AND tokenid = '".$tokenid."'";
$SQL_ .= " GROUP BY tokenid";
$rsCountSurveyOver = $objDB->sql_query($SQL_);
if(count($rsCountSurveyOver) > 0){
foreach($rsCountSurveyOver as $key => $val) {
$SUMOve = $SUMOve + $val['AvgValue'];
}
}
}else{
//$returnArr[] = "0.00";
//echo "NO PAGES FOUND";
}
}
//}
$returnArr['avg_lowrating'] = $SUM;
$returnArr['lowrating'] = $SUMOve;
return $returnArr;
}
public function fetchHightRatingAndCount($type,$BranchID,$IndustryID,$orgID){
global $objDB;
$rsBranches = $this->fetchBranchesData($BranchID);
$returnArr = array();
$avgUSTotal = 0;
$count = 0;
$total = 0;
for($i=0; $i<count($rsBranches); $i++) {
$rsIndustry = $this->fetchIndustryData($rsBranches[$i]['branchId'],$IndustryID);
for($indst=0; $indst<count($rsIndustry); $indst++){
$rsPages = explode(",",$rsIndustry[$indst]['db_pagesId']);
if(count($rsPages) > 0 && !empty($rsIndustry[0]['db_pagesId'])) {
$SQL_ = "SELECT count(db_surveyValue) AS AvgValue from beatle_survey WHERE db_surveyPageid IN (".$rsIndustry[$indst]['db_pagesId'].") AND db_surveyIndId IN (".$rsIndustry[$indst]['IndId'].") AND db_surveyValue IN (3,4,5) AND is_submit = 'Y' ";
$SQL_ .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$rsBranches[$i]['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_ .= " GROUP BY tokenid";
$rsCountSurvey = $objDB->sql_query($SQL_);
if(count($rsCountSurvey) > 0){
foreach($rsCountSurvey as $key => $val) {
$count = $count + $val['AvgValue'];
}
}
$SQL_ = "";
$SQL_ = "SELECT count(db_surveyValue) AS AvgValue from beatle_survey WHERE db_surveyPageid IN (".$rsIndustry[$indst]['db_pagesId'].") AND db_surveyIndId IN (".$rsIndustry[$indst]['IndId'].") AND db_surveyValue IN (1,2,3,4,5) AND is_submit = 'Y' ";
$SQL_ .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$rsBranches[$i]['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_ .= " GROUP BY tokenid";
$rsCountSurveyTotal = $objDB->sql_query($SQL_);
//echo "<pre>";
//print_r($rsCountSurveyTotal);
//echo "</pre>";
if(count($rsCountSurveyTotal) > 0){
foreach($rsCountSurveyTotal as $key => $val) {
$total = $total + $val['AvgValue'];
}
}
//echo $count = $count + $rsCountSurveyTotal[0]['AvgValue'];
}else{
//$returnArr[] = "0.00";
//echo "NO PAGES FOUND";
}
}
}
$returnArr['total'] = $total;
$returnArr['count'] = $count;
return $returnArr;
}
public function fetchGraphDetailsGeneral($atype,$orgID,$BranchID,$IndustryID,$PageID,$PageConID){
global $objDB;
$SQL = "SELECT db_orgtype FROM beatle_organization WHERE OrgId = ".$orgID;
$rsOrganizations_ = $objDB->sql_query($SQL);
$orgType = $rsOrganizations_[0]['db_orgtype'];
// equal, notequal
$rsIndustryIDS = FetchIndustryIDS($IndustryID,$orgID,'equal');
//echo "<per>";
//print_r($rsIndustryIDS);
//echo "</per>";
$FromDate = $this->FromDate;
$FromDate = date("Y-m-d",strtotime("-1 day ".$FromDate));
$ToDate = $this->ToDate;
$datetime1 = new DateTime($FromDate);
$datetime2 = new DateTime($ToDate);
$interval = $datetime1->diff($datetime2);
$diff = $interval->format('%a');
$returnArr = array();
$avgIndArr = array();
$avgUsArr = array();
$rsBranches = $this->fetchBranchesData($BranchID,$IndustryID);
//echo "<pre>";
//print_r($rsBranches);
//echo "</pre>";
$avgUSTotal = 0;
for($br=0; $br<count($rsBranches); $br++) {
$frmDt = $FromDate;
if(!empty($BranchID))
$rsIndustryData = $this->fetchIndustryData($BranchID,$IndustryID);
else
$rsIndustryData = $this->fetchIndustryData($rsBranches[$br]['branchId'],$IndustryID);
//echo "<pre>";
//print_r($rsIndustryData);
//echo "</pre>";
if(count($rsIndustryData) > 0) {
$avgUS = 0;
$avgInd = 0;
for($indd=0; $indd<count($rsIndustryData); $indd++) {
$frmDt = $FromDate;
for($dt=0; $dt<=$diff; $dt++){
if($dt != $diff ) {
$frmDt = date("Y-m-d",strtotime("+1 day".$frmDt));
//echo $dt." || ".$returnArr['date']." || ";
//echo "<br />";
if(!in_array($frmDt,$returnArr['date'])) {
$returnArr['date'][] = date("Y-m-d",strtotime($frmDt));
$returnArr['date_format'][] = date("'d/M'",strtotime($frmDt));
}
$avgUs = $this->fetchRatingForCurrentIndustry_Graph($atype,$rsIndustryData[$indd]['IndId'],$rsBranches[$br]['branchId'],$orgID,'us',$frmDt,$frmDt,$PageID,$PageConID);
$avgInd = $this->fetchRatingForOhterIndustry_Graph($atype,$rsIndustryData[$indd]['IndId'],$rsIndustryData[$indd]['db_ind_type_id'],$orgID,$orgType,'ind',$frmDt,$frmDt,$PageID,$PageConID,$PageConID,$BranchID);
$avgIndArr[$indd][] = $avgInd;
$avgUsArr[$indd][] = $avgUs;
}
}
$returnArr['avg_ind'] = $avgIndArr;
$returnArr['avg_us'] = $avgUsArr;
}
}
}
//print_r($returnArr);
//die;
$finalAvgArr = array();
$ArrArr = array();
$finalAvgArr[0] = $returnArr['date_format'];
for($dt=0; $dt<=$diff; $dt++){
$avg1 = 0;
$avg2 = 0;
for($cnt=0; $cnt<count($returnArr['avg_ind']); $cnt++){
$avg1 += $returnArr['avg_ind'][$cnt][$dt];
$avg2 += $returnArr['avg_us'][$cnt][$dt];
}
//echo $avg;
$ArrArr[1][] = number_format($avg1/count($returnArr['avg_ind']),2);
$ArrArr[2][] = number_format($avg2/count($returnArr['avg_us']),2);
//echo "<br />";
}
unset($ArrArr[1][count($ArrArr[1])-1]);
unset($ArrArr[2][count($ArrArr[2])-1]);
$finalAvgArr[1] = $ArrArr[1];
$finalAvgArr[2] = $ArrArr[2];
/*echo "<pre>";
print_r($finalAvgArr);
echo "</pre>";
*/
return $finalAvgArr;
}
public function fetchContentDetails($BranchID,$IndustryID){
global $objDB;
$rsBranches = $this->fetchBranchesData($BranchID,$IndustryID);
$BranchIDArr = array();
foreach($rsBranches as $key => $val){
$BranchIDArr[] = $val['branchId'];
}
$SQL = "SELECT GROUP_CONCAT(db_pagesId) AS PageIDs FROM beatle_industry WHERE db_bracnchid IN (".implode(',',$BranchIDArr).")";
if(!empty($IndustryID))
$SQL .= " AND IndId = ".$IndustryID;
$rsPageID = $objDB->sql_query($SQL);
//print_r($rsPageID[0]['PageIDs']);
$SQL = "SELECT * FROM beatle_page WHERE pageId IN (".$rsPageID[0]['PageIDs'].") AND db_pagetype = 'Content'";
$rsPages = $objDB->sql_query($SQL);
return $rsPages;
}
public function fetchIndustryWiseAverage($atype,$orgID,$BranchID,$IndustryID,$type,$PageID,$PageContentID){
global $objDB;
$datetime1 = new DateTime($this->FromDate);
$datetime2 = new DateTime($this->ToDate);
$interval = $datetime1->diff($datetime2);
$diff = $interval->format('%a');
$diff = $diff + 1;
$SQL = "SELECT db_ind_type_id FROM beatle_industry WHERE IndId = ".$IndustryID;
$rsIndustry = $objDB->sql_query($SQL);
$SQL = "SELECT GROUP_CONCAT(IndId) AS IndustryIDs FROM beatle_industry INNER JOIN beatle_branch ON beatle_industry.db_bracnchid = beatle_branch.branchId
WHERE beatle_industry.db_ind_type_id = ".$rsIndustry[0]['db_ind_type_id']."";
if($type == 'us')
$SQL .= " AND beatle_industry.IndId = ".$IndustryID;
else if($type == 'ind')
$SQL .= " AND beatle_industry.IndId != ".$IndustryID." AND beatle_branch.db_branchOrg != ".$orgID;
//echo "<br />".$SQL."<br />";
$rsIndustryIDs = $objDB->sql_query($SQL);
//print_r($rsIndustryIDs);
//echo $rsIndustryIDs;
if(empty($rsIndustryIDs[0]['IndustryIDs'])){
return "0.00";
}
if($atype == 'oa') {
$SQL = "SELECT AVG(db_surveyValue) AS AvgValue FROM beatle_survey WHERE db_surveyPageid IN (".$PageID.") AND db_surveyValue IN (1,2,3,4,5) AND is_submit = 'Y'";
if(!empty($IndustryID)){
if($type == 'us')
$SQL .= " AND db_surveyIndId = ".$IndustryID;
else if($type == 'ind')
$SQL .= " AND db_surveyIndId IN (".$rsIndustryIDs[0]['IndustryIDs'].") ";
}else{
$SQL .= " AND db_surveyIndId IN (".$rsIndustryIDs[0]['IndustryIDs'].") ";
}
if(!empty($PageContentID))
$SQL .= " AND db_surveyContentId = ".$PageContentID;
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($this->FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($this->ToDate))." 23:59:59' ";
//echo $SQL;
$rsCountSurvey = $objDB->sql_query($SQL);
$avg = $rsCountSurvey[0]['AvgValue'];
if($avg <= 0){
$avg = "0.00";
}else{
$avg = number_format($avg,2);
}
return $avg;
}else if($atype == 'af'){
$SQL = "SELECT COUNT(db_surveyValue) AS TotalCount FROM beatle_survey WHERE db_surveyPageid IN (".$PageID.") AND db_surveyValue IN (1,2,3,4,5) AND is_submit = 'Y' ";
if(!empty($IndustryID)){
if($type == 'us')
$SQL .= " AND db_surveyIndId = ".$IndustryID;
else if($type == 'ind')
$SQL .= " AND db_surveyIndId IN (".$rsIndustryIDs[0]['IndustryIDs'].") ";
}else{
$SQL .= " AND db_surveyIndId IN (".$rsIndustryIDs[0]['IndustryIDs'].") ";
}
if(!empty($PageContentID))
$SQL .= " AND db_surveyContentId = ".$PageContentID;
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($this->FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($this->ToDate))." 23:59:59' ";
$SQL .= " GROUP BY tokenid";
$rsCountSurvey = $objDB->sql_query($SQL);
$avg = count($rsCountSurvey) / $diff;
if($avg <= 0){
$avg = "0.00";
}else{
$avg = number_format($avg,2);
}
return $avg;
}else if($atype == 'alr'){
$SQL = "SELECT AVG(db_surveyValue) AS AvgValue FROM beatle_survey WHERE db_surveyPageid IN (".$PageID.") AND db_surveyValue IN (1,2) AND is_submit = 'Y'";
if(!empty($IndustryID)){
if($type == 'us')
$SQL .= " AND db_surveyIndId = ".$IndustryID;
else if($type == 'ind')
$SQL .= " AND db_surveyIndId IN (".$rsIndustryIDs[0]['IndustryIDs'].") ";
}else{
$SQL .= " AND db_surveyIndId IN (".$rsIndustryIDs[0]['IndustryIDs'].") ";
}
if(!empty($PageContentID))
$SQL .= " AND db_surveyContentId = ".$PageContentID;
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($this->FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($this->ToDate))." 23:59:59' ";
//echo $SQL;
$rsCountSurvey = $objDB->sql_query($SQL);
$avg = $rsCountSurvey[0]['AvgValue'];
if($avg <= 0){
$avg = "0.00";
}else{
$avg = number_format($avg,2);
}
return $avg;
}
}
public function fetchRatingForOhterIndustry_Graph($atype,$indID,$indType,$orgID,$orgType,$type,$FromDate,$ToDate,$PageID,$PageConID,$BranchID){
global $objDB;
$datetime1 = new DateTime($this->FromDate);
$datetime2 = new DateTime($this->ToDate);
$interval = $datetime1->diff($datetime2);
$diff = $interval->format('%a');
$SQL = "SELECT
beatle_organization.OrgId AS OrgIDS,
beatle_organization.db_Orgname,
beatle_organization.db_orgtype,
beatle_branch.branchId,
beatle_branch.db_branchOrg,
beatle_industry.db_industry,
beatle_industry.db_ind_type_id
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." AND
beatle_industry.db_ind_type_id = ".$indType." ";
//echo "<pre>".$type."<br />";
//echo $SQL;
//echo "</pre>";
//echo "#".$indID."#";
$rsOrganizations = $objDB->sql_query($SQL);
//print_r($rsOrganizations);
$avgValue = "0.00";
if(count($rsOrganizations) > 0) {
$averageArr = array();
foreach($rsOrganizations as $orgKey => $orgVal){
$OrgIDS_ = $orgVal['OrgIDS'];
$SQL = "SELECT branchId AS BranchID FROM beatle_branch WHERE db_branchOrg = ".$OrgIDS_;
if(!empty($BranchID))
$SQL .= " AND branchId = ".$BranchID;
//echo "<br />";
$rsBranches = $objDB->sql_query($SQL);
//echo "<pre>";
//print_r($rsBranches);
//echo "</pre>";
for($b=0; $b<count($rsBranches); $b++){
$SQL = "SELECT IndId,db_pagesId FROM beatle_industry WHERE db_bracnchid =".($rsBranches[$b]['BranchID'])." AND db_ind_type_id = ".$indType."";
//echo "<br />";
$rsIndustry = $objDB->sql_query($SQL);
//echo "<pre>".$indType;
//print_r($rsIndustry);
//echo "</pre>";
for($indst=0; $indst<count($rsIndustry); $indst++){
//echo $rsIndustry[$indst]['IndId']." # ".$indID."<br />";
$SQL_ = "SELECT GROUP_CONCAT(pageId) AS pageId FROM beatle_page WHERE pageId IN (".$rsIndustry[$indst]['db_pagesId'].") AND LOWER(db_pagetype) = 'content' ";
$rsPages = $objDB->sql_query($SQL_);
//echo "<pre>";
//print_r($rsPages);
//echo "</pre>";
//$rsPages = explode(",",$rsIndustry[$indst]['db_pagesId']);
if(count($rsPages) > 0 && !empty($rsIndustry[0]['db_pagesId'])) {
if($atype == 'oa') {
//$SQL = "SELECT AVG(db_surveyValue) AS AvgValue FROM beatle_survey WHERE db_surveyPageid IN (".$rsIndustry[$indst]['db_pagesId'].") AND db_surveyIndId IN (".$rsIndustry[$indst]['IndId'].") AND db_surveyValue IN (1,2,3,4) AND is_submit = 'Y' ";
$SQL = "SELECT ROUND(AVG(db_surveyValue),2) AS AvgValue FROM beatle_survey WHERE db_surveyPageid IN (".$rsPages[0]['pageId'].") AND db_surveyIndId IN (".$rsIndustry[$indst]['IndId'].") AND db_surveyValue IN (1,2,3,4,5) AND is_submit = 'Y' ";
if(!empty($PageID)){
$SQL .= " AND db_surveyPageid = ".$PageID;
}
if(!empty($PageConID)){
$SQL .= " AND db_surveyContentId = ".$PageConID;
}
$SQL .= " AND orgid = ".$OrgIDS_;
$SQL .= " AND created_date BETWEEN '".date("Y-m-d",strtotime($FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($ToDate))." 23:59:59' ";
$rsCountSurvey = $objDB->sql_query($SQL);
$avgValue = $avgValue + $rsCountSurvey[0]['AvgValue'];
//echo "OA = #".$avgValue."#<br />";
}
else if($atype == 'af'){
$SQL = "SELECT COUNT(db_surveyValue) AS TotalCount FROM beatle_survey WHERE db_surveyPageid IN (".$rsPages[0]['pageId'].") AND db_surveyIndId IN (".$rsIndustry[$indst]['IndId'].") AND db_surveyValue IN (1,2,3,4,5) AND is_submit = 'Y' ";
if(!empty($PageID)){
$SQL .= " AND db_surveyPageid = ".$PageID;
}
if(!empty($PageConID)){
$SQL .= " AND db_surveyContentId = ".$PageConID;
}
$SQL .= " AND orgid = ".$OrgIDS_;
$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 />";
$rsCountSurvey = $objDB->sql_query($SQL);
//echo "|| ".$rsCountSurvey[0]['TotalCount']." || ".$diff;
$avgValue = $avgValue + ($rsCountSurvey[0]['TotalCount']/$diff)/count($rsOrganizations);
//$avgValue = $avgValue + $rsCountSurvey[0]['AvgValue'];
//echo "<br />";
if((float)$avgValue <= 0){
$avgValue = "0.00";
}else{
$avgValue = number_format($avgValue,2);
}
//echo "AF = #".$avgValue."#<br />";
}
else if($atype == 'alr'){
$SQL = "SELECT ROUND(AVG(db_surveyValue),2) AS AvgValue FROM beatle_survey WHERE db_surveyPageid IN (".$rsPages[0]['pageId'].") AND db_surveyIndId IN (".$rsIndustry[$indst]['IndId'].") AND db_surveyValue IN (1,2) AND is_submit = 'Y' ";
if(!empty($PageID)){
$SQL .= " AND db_surveyPageid = ".$PageID;
}
if(!empty($PageConID)){
$SQL .= " AND db_surveyContentId = ".$PageConID;
}
$SQL .= " AND orgid = ".$OrgIDS_;
$SQL .= " AND created_date BETWEEN '".date("Y-m-d",strtotime($FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($ToDate))." 23:59:59' ";
$rsCountSurvey = $objDB->sql_query($SQL);
$avgValue = $avgValue + $rsCountSurvey[0]['AvgValue'];
//echo "ALR = #".$avgValue."#<br />";
}
}else{
//echo "NO PAGES FOUND";
}
}
}
}
}
//echo "<br />RETURN #".$avgValue."#";
return $avgValue;
}
public function fetchRatingForCurrentIndustry_Graph($atype,$IndID,$BranchID,$orgID,$type,$FromDate,$ToDate,$PageID,$PageConID){
global $objDB;
$datetime1 = new DateTime($this->FromDate);
$datetime2 = new DateTime($this->ToDate);
$interval = $datetime1->diff($datetime2);
$diff = $interval->format('%a');
//echo $atype;
//echo "<br />";
//echo "<pre>";
$SQL = "SELECT db_ind_type_id, db_pagesId FROM beatle_industry WHERE IndId = ".$IndID;
$rsIndustry = $objDB->sql_query($SQL);
$SQL = "SELECT GROUP_CONCAT(IndId) AS IndustryIDs FROM beatle_industry WHERE db_ind_type_id = ".$rsIndustry[0]['db_ind_type_id']."";
//echo "<br />";
$rsIndustryIDs = $objDB->sql_query($SQL);
$avgValue = "0.00";
if(count($rsIndustry) > 0 && count($rsIndustryIDs) > 0){
//echo "<br />";
//echo $rsIndustry[0]['db_pagesId'];
//echo "<br />";
$rsPages = explode(",",$rsIndustry[0]['db_pagesId']);
if(count($rsPages) > 0 && !empty($rsIndustry[0]['db_pagesId'])) {
//$SQL = "SELECT AVG(db_surveyValue) AS AvgValue FROM beatle_survey WHERE db_surveyPageid IN(".$rsIndustry[0]['db_pagesId'].") AND db_surveyIndId IN (".$rsIndustryIDs[0]['IndustryIDs'].") AND db_surveyValue IN (1,2,3,4) AND is_submit = 'Y'";
if($atype == 'oa') {
$SQL = "SELECT AVG(db_surveyValue) AS AvgValue FROM beatle_survey WHERE db_surveyPageid IN(".$rsIndustry[0]['db_pagesId'].") AND db_surveyIndId IN (".$IndID.") AND db_surveyValue IN (1,2,3,4,5) AND is_submit = 'Y'";
if(!empty($PageID)){
$SQL .= " AND db_surveyPageid = ".$PageID;
}
if(!empty($PageConID)){
$SQL .= " AND db_surveyContentId = ".$PageConID;
}
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($FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($ToDate))." 23:59:59' ";
$rsCountSurvey = $objDB->sql_query($SQL);
$avgValue = number_format($rsCountSurvey[0]['AvgValue'],2);
}else if($atype == 'af'){
//$SQL = "SELECT AVG(db_surveyValue) AS AvgValue FROM beatle_survey WHERE db_surveyPageid IN(".$rsIndustry[0]['db_pagesId'].") AND db_surveyIndId IN (".$IndID.") AND db_surveyValue IN (1,2,3,4) AND is_submit = 'Y'";
$SQL = "SELECT COUNT(db_surveyValue) AS TotalCount FROM beatle_survey WHERE db_surveyPageid IN(".$rsIndustry[0]['db_pagesId'].") AND db_surveyIndId IN (".$IndID.") AND db_surveyValue IN (1,2,3,4,5) AND is_submit = 'Y'";
if(!empty($PageID)){
$SQL .= " AND db_surveyPageid = ".$PageID;
}
if(!empty($PageConID)){
$SQL .= " AND db_surveyContentId = ".$PageConID;
}
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($FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($ToDate))." 23:59:59' ";
$rsCountSurvey = $objDB->sql_query($SQL);
//echo "|| ".$rsCountSurvey[0]['TotalCount']." || ".$diff;
$avgValue = $rsCountSurvey[0]['TotalCount'] / $diff;
//echo "<br />";
if($avgValue <= 0){
$avgValue = "0.00";
}else{
$avgValue = number_format($avgValue,2);
}
}else if($atype == 'alr'){
$SQL = "SELECT AVG(db_surveyValue) AS AvgValue FROM beatle_survey WHERE db_surveyPageid IN(".$rsIndustry[0]['db_pagesId'].") AND db_surveyIndId IN (".$IndID.") AND db_surveyValue IN (1,2) AND is_submit = 'Y'";
if(!empty($PageID)){
$SQL .= " AND db_surveyPageid = ".$PageID;
}
if(!empty($PageConID)){
$SQL .= " AND db_surveyContentId = ".$PageConID;
}
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($FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($ToDate))." 23:59:59' ";
$rsCountSurvey = $objDB->sql_query($SQL);
$avgValue = number_format($rsCountSurvey[0]['AvgValue'],2);
}
}else{
//echo "NO PAGES FOUND";
}
}
return $avgValue;
}
public function fetchNewsData(){
global $objDB;
$SQL = "SELECT * FROM beatle_news WHERE startdate <= '".date("Y-m-d H:i:s")."' AND enddate >= '".date("Y-m-d H:i:s")."' AND active = 'Y' ";
return $rsData = $objDB->sql_query($SQL);
}
public function fetchSubscriptionDate(){
global $objDB;
$SQL = "SELECT beatle_userlogin.created_date, beatle_userlogin.db_valid, beatle_userlogin.db_valid_from FROM beatle_userlogin
WHERE OrgID = ". $_SESSION['OtherInfo']['OrgID']." AND beatle_userlogin.userId = (SELECT db_ownerId FROM beatle_organization WHERE OrgId = ". $_SESSION['OtherInfo']['OrgID'].")" ;
$rsData = $objDB->sql_query($SQL);
$datetime1 = new DateTime($rsData[0]['db_valid_from']);
$datetime2 = new DateTime($rsData[0]['db_valid']);
$datetime3 = new DateTime(date("Y-m-d H:i:s"));
$interval = $datetime1->diff($datetime2);
$diff = $interval->format('%a');
$interval1 = $datetime1->diff($datetime3);
$diff1 = $interval1->format('%a');
$returnArr['totalday'] = $diff;
$returnArr['used'] = $diff1;
return $returnArr;
}
} ///// class include_login completed /////
?>