File: /home/barbeatleanalyti/www/manage.beatleanalytics.com/site/include 19-3-2018 1_10/dashboardma.php
<?php
/* <summary>
Class contains methods related to state module
<author>
Beatle Buddy 2017.
</author>
Version 1.0
</summary>
*/
class include_dashboardma
{
/*
<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 />";
//die;
$rsBranches = $objDB->sql_query($SQL);
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 fetchRatingForOhterIndustry($orgID,$orgType,$type,$FromDate,$ToDate){
global $objDB;
$datetime1 = new DateTime($FromDate);
$datetime2 = new DateTime($ToDate);
$interval = $datetime1->diff($datetime2);
$diff = $interval->format('%a');
$diff = $diff + 1;
//$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'])) {
while(strtotime($FromDate) <= strtotime($ToDate)){
//echo " # ".$FromDate;
$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' ";
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($FromDate))." 23:59:59' ";
$rsCountSurvey = $objDB->sql_query($SQL);
$avgValue = $avgValue + $rsCountSurvey[0]['AvgValue'];
$FromDate = date("d-m-Y", strtotime($FromDate." +1 day"));
}
}else{
//echo "NO PAGES FOUND";
}
}
}
}
}
if($avgValue > 0){
$avgValue = number_format(($avgValue / $diff),2);
}else{
$avgValue = "0.00";
}
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(count($rsIndustry));
$cnt = count($rsIndustry);
//echo "</pre>";
for($indst=0; $indst < $cnt; $indst++){
//echo "|| ".$indst." || <br />";
$SQL = "SELECT count(pageId) AS pageId FROM beatle_page WHERE pageId IN (".$rsIndustry[$indst]['db_pagesId'].") AND db_pagetype = 'Content'";
$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) AND is_submit = 'Y' ";
$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";
$rsCountSurvey = $objDB->sql_query($SQL);
$avg = count($rsCountSurvey) / $diff ;
$returnArr[] = $avg;
}
else{
$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 fetchAvgLowRating($type,$BranchID,$IndustryID,$orgID){
global $objDB;
$FromDate = $this->FromDate;
$ToDate = $this->ToDate;
$datetime1 = new DateTime($FromDate);
$datetime2 = new DateTime($ToDate);
$interval = $datetime1->diff($datetime2);
$diff = $interval->format('%a');
$diff = $diff + 1;
/*$SQL = "SELECT * FROM beatle_industry WHERE IndId = ".$IndustryID;
$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']."";
if($type == 'ind')
$SQL .= " AND IndId != ".$IndustryID."";
$rsIndustryIDs = $objDB->sql_query($SQL);
*/
//echo "<pre>";
//print_r($rsIndustryIDs);
//echo "</pre>";
//$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";
//echo "<pre>";
$rsBranches = $this->fetchBranchesData($BranchID);
//print_r($rsBranches);
$returnArr = array();
$avgUSTotal = 0;
for($i=0; $i<count($rsBranches); $i++) {
$rsIndustry = $this->fetchIndustryData($rsBranches[$i]['branchId'],$IndustryID);
//echo "<pre>";
//print_r($rsIndustry);
//echo "</pre>";
if($type == 'ind' && !empty($BranchID)) {
$SQL = "SELECT GROUP_CONCAT(IndId) AS IndustryIDs FROM beatle_industry WHERE db_ind_type_id = ".$rsIndustry[0]['db_ind_type_id']."";
$SQL .= " AND IndId != ".$rsIndustry[0]['IndId']."";
$rsIndustryIDs = $objDB->sql_query($SQL);
}else{
$SQL = "SELECT GROUP_CONCAT(IndId) AS IndustryIDs FROM beatle_industry WHERE db_ind_type_id = ".$rsIndustry[0]['db_ind_type_id']."";
$SQL .= " AND IndId = ".$rsIndustry[0]['IndId']."";
$rsIndustryIDs = $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) AND is_submit = 'Y' ";
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($FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($ToDate))." 23:59:59' ";
echo $SQL;
*/
while(strtotime($FromDate) <= strtotime($ToDate)){
$SQL_ = "SELECT AVG(db_surveyValue) AS AvgValue from beatle_survey WHERE db_surveyPageid IN (".$rsIndustry[$indst]['db_pagesId'].") AND db_surveyValue IN (1,2) AND is_submit = 'Y' ";
// AND db_surveyIndId IN (".$rsIndustry[$indst]['IndId'].")
// $SQL = "SELECT COUNT(db_surveyValue) AS AvgValue FROM beatle_industry WHERE db_surveyPageid IN (".$rsIndustry[$indst]['db_pagesId'].") AND db_ind_type_id = ".$rsIndustry[$indst]['db_ind_type_id']."";
if($type == 'us')
$SQL_ .= " AND db_surveyIndId = ".$rsIndustry[0]['IndId']." ";
else if($type == 'ind')
$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;
#######################################################
// ADDITIONAL ADDED IF NOT WORKING REMOVE BELOW LINE
########################################################
$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 = $objDB->sql_query($SQL_);
$SUM = 0;
if(count($rsCountSurvey) > 0){
foreach($rsCountSurvey as $key => $val) {
$SUM = $SUM + $val['AvgValue'];
}
$SUM = $SUM / count($rsCountSurvey);
}
//if($rsCountSurvey[0]['AvgValue'] == 0 || empty($rsCountSurvey[0]['AvgValue']))
//$rsCountSurvey[0]['AvgValue'] = "0.00";
//$avg = $rsCountSurvey[0]['TotalCount'] / $rsCountSurvey[0]['TotalDays'];
//$avg = $rsCountSurvey[]['']/
//$returnArr[] = $rsCountSurvey[0]['AvgValue'];
$avgUSTotal = $avgUSTotal + $SUM;
/*if($type == 'us') {
echo "<pre>";
echo $type."<br />";
echo $SQL_;
echo "<br />";
print_r($rsCountSurvey);
echo $SUM. "## ".$avgUSTotal;
//echo $type."<br />";
//echo $SQL_;
echo "<br />";
}*/
//echo $avgUSTotal." # ".$rsCountSurvey[0]['AvgValue'];
//echo "<br /><br /><br />";
$FromDate = date("d-m-Y",strtotime($FromDate." +1 day"));
//echo "</pre>";
}
// $avgValue = number_format($rsCountSurvey[0]['AvgValue'],2);
}else{
//$returnArr[] = "0.00";
//echo "NO PAGES FOUND";
}
}
}
//print_r($returnArr);
if($avgUSTotal > 0){
$avgUSTotal = number_format(($avgUSTotal / $diff),2);
}else{
$avgUSTotal = "0.00";
}
return $avgUSTotal;
//return $avg = number_format(array_sum($returnArr)/count($returnArr),2);
}
public function fetchGraphDetailsGeneral($atype,$orgID,$BranchID,$IndustryID,$PageID,$PageConID,$FromDate,$ToDate){
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 = $FromDate;
$FromDate = date("Y-m-d",strtotime("-1 day ".$FromDate));
$ToDate = $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;
$avgIndArr = array();
$avgUsArr = array();
for($indd=0; $indd<count($rsIndustryData); $indd++) {
$frmDt = $FromDate;
//for($dt=0; $dt<=$diff; $dt++){
while(strtotime($frmDt) < strtotime($ToDate)) {
//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);
//echo "US = ".$atype." # ".$frmDt." # ".$avgUs." <br />";
$avgInd = $this->fetchRatingForOhterIndustry_Graph($atype,$rsIndustryData[$indd]['IndId'],$rsIndustryData[$indd]['db_ind_type_id'],$orgID,$orgType,'ind',$frmDt,$frmDt,$PageID,$PageConID,$PageConID,$BranchID);
// echo "IND = ".$indd." > ".$atype." # ".$frmDt." # ".$avgInd." <br /><br />";
$avgIndArr[$indd][] = $avgInd;
$avgUsArr[$indd][] = $avgUs;
//echo "<pre>";
//print_r($avgUsArr);
//echo "</pre>";
}
//echo "<br /><br />";
//}
// }
}
}
$returnArr['avg_ind'][] = $avgIndArr;
$returnArr['avg_us'][] = $avgUsArr;
}
//$returnArr['avg_ind'] = $avgIndArr;
//$returnArr['avg_us'] = $avgUsArr;
//echo "<pre>";
//print_r($returnArr);
$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][0][$dt];
$avg2 += $returnArr['avg_us'][$cnt][0][$dt];
}
//echo $avg;
// ADDITIONAL ADDED
$avg1 = $avg1/count($rsIndustryData);
$avg2 = $avg2/count($rsIndustryData);
$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];
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 * 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') {
$fdate = $this->FromDate;
$tdate = $this->ToDate;
$avg = 0;
$dtcnt = 0;
while(strtotime($fdate) <= strtotime($tdate)) {
$dtcnt++;
$SQL = "SELECT AVG(db_surveyValue) AS AvgValue FROM beatle_survey WHERE db_surveyPageid IN (".$PageID.") AND db_surveyValue IN (1,2,3,4) 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($fdate))." 00:00:00' AND '".date("Y-m-d",strtotime($fdate))." 23:59:59' ";
//echo $SQL;
//echo "<br />";
$rsCountSurvey = $objDB->sql_query($SQL);
$avg = $avg + $rsCountSurvey[0]['AvgValue'];
$fdate = date("Y-m-d",strtotime($fdate." +1 days"));
}
if($avg > 0){
$avg = number_format(($avg / $dtcnt),2);
}else{
$avg = "0.00";
}
return $avg;
}
else if($atype == 'af'){
$fdate = $this->FromDate;
$tdate = $this->ToDate;
$avg = 0;
$dtcnt = 0;
while(strtotime($fdate) <= strtotime($tdate)) {
$dtcnt++;
$SQL = "SELECT COUNT(db_surveyValue) AS TotalCount FROM beatle_survey WHERE db_surveyPageid IN (".$PageID.") AND db_surveyValue IN (1,2,3,4) 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($fdate))." 00:00:00' AND '".date("Y-m-d",strtotime($fdate))." 23:59:59' ";
$SQL .= " GROUP BY tokenid";
$rsCountSurvey = $objDB->sql_query($SQL);
//$avg = $avg + $rsCountSurvey[0]['AvgValue'];
$avg = $avg + count($rsCountSurvey);
$fdate = date("Y-m-d",strtotime($fdate." +1 days"));
}
if($avg > 0){
$avg = number_format(($avg / $dtcnt),2);
}else{
$avg = "0.00";
}
return $avg;
}
else if($atype == 'alr'){
$fdate = $this->FromDate;
$tdate = $this->ToDate;
$avg = 0;
$dtcnt = 0;
while(strtotime($fdate) <= strtotime($tdate)) {
$dtcnt++;
$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($fdate))." 00:00:00' AND '".date("Y-m-d",strtotime($fdate))." 23:59:59' GROUP BY tokenid";
$rsCountSurvey = $objDB->sql_query($SQL);
$SUM = 0;
if(count($rsCountSurvey) > 0){
foreach($rsCountSurvey as $key => $val) {
$SUM = $SUM + $val['AvgValue'];
}
$SUM = $SUM / count($rsCountSurvey);
//echo "<br /> #### ".$SUM." #### <br />";
}
$avg = $avg + $SUM;
/*
if($type == 'us') {
echo $type."<br />";
echo $SQL;
echo "<br />";
print_r($rsCountSurvey);
echo $SUM. "## ".$avg;
//echo $type."<br />";
//echo $SQL_;
echo "<br />";
} */
//echo $rsCountSurvey[0]['AvgValue']." # ".$avg;
//echo "<br /><br />";
$fdate = date("Y-m-d",strtotime($fdate." +1 days"));
}
if($avg > 0){
$avg = number_format(($avg / $dtcnt),2);
}else{
$avg = "0.00";
}
//if($type == 'us' && !empty($PageContentID))
//echo " == ".$avg."<br />";
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 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;
$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) 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 /><br />";
/*
if(empty($rsPages[0]['pageId'])){
echo $rsIndustry[$indst]['db_pagesId'];
echo $SQL;
echo "<br /><br />";
die;
}
*/
$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) 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 * 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) 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);
//if($type == 'us')
//echo $SQL." # ".$avgValue;
//echo "<br />";
}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) 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";
}
}
//echo "<pre>";
//print_r($totalArr);
//echo "</pre>";
/*$T1 = ($totalArr[1] * 1) + ($totalArr[2]*2) + ($totalArr[3]*3) +($totalArr[4]*4);
$T2 = array_sum($totalArr);
$Avrg = number_format((($T1 )/($T2)),2);
if($Avrg <= 0)
$Avrg = "0.00";
//return $Avrg;
*/
return $avgValue;
}
} ///// class include_login completed /////
?>