File: /home/barbeatleanalyti/public_html/manage.beatleanalytics.com/site/include/dashboardma.php 2-2-2019
<?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 branchId,db_branchName,db_branch_type,db_branchOrg 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 branchId,db_branchName,db_branch_type,db_branchOrg FROM beatle_branch WHERE {$SQL_B} db_branchManager = ".$_SESSION['UserInfo']['UserID']." ";
}else if(strtolower($_SESSION['UserInfo']['UType']) == 'line_manager'){
$SQL = "SELECT branchId,db_branchName,db_branch_type,db_branchOrg FROM beatle_branch WHERE {$SQL_B} branchId = (SELECT BranchID FROM beatle_userlogin WHERE userId = ".$_SESSION['UserInfo']['UserID'].") ";
}
$rsBranches = $objDB->sql_query($SQL);
return $rsBranches;
}
public function fetchIndustryData($branchID,$IndustryID){
global $objDB;
$SQL = "SELECT IndId,db_industry,db_ind_type_id,db_IndLoginId,db_bracnchid,db_userid,db_pagesId 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 fetchZeroFeedbackDays($orgID,$BranchID,$IndustryID){
global $objDB;
$SQL = "SELECT count(*) 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($this->FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($this->ToDate))." 23:59:59' ";
$SQL .= " GROUP BY DATE(created_date)";
$rsCountDay = $objDB->sql_query($SQL);
return $rsCountDay;
}
public function fetchAvgFeedback($type,$BranchID,$IndustryID,$orgID){
global $objDB;
$datetime1 = new DateTime($this->FromDate);
$datetime2 = new DateTime($this->ToDate);
$interval = $datetime1->diff($datetime2);
$diff = $interval->format('%a');
$diff = $diff + 1;
$rsBranches = $this->fetchBranchesData($BranchID);
$returnArr = array();
$avgUSTotal = 0;
for($i=0; $i<count($rsBranches); $i++) {
$rsIndustry = $this->fetchIndustryData($rsBranches[$i]['branchId'],$IndustryID);
for($indst=0; $indst < count($rsIndustry); $indst++){
$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 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'];
$rsIndustryIDs = $objDB->sql_query($SQL);
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' ";
$SQL .= " AND db_surveyIndId IN (".$rsIndustryIDs[0]['IndustryIDs'].") ";
if($type == 'us'){
$SQL .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$rsBranches[$i]['branchId'];
}elseif($type == 'ind'){
if(empty($BranchID) and empty($IndustryID)){
$SQL .= " AND orgid != ".$orgID;
}elseif(!empty($BranchID) and empty($IndustryID)){
$SQL .= " AND db_surveyBranchid != ".$BranchID;
}
}
$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);
if($type == 'us'){
if(fetchActiveScoreCheckedData() == "checked"){
$avg = count($rsCountSurvey) / fetchZeroFeedbackDays($orgID,$BranchID,$IndustryID,$this->FromDate,$this->ToDate) ;
}else{
$avg = count($rsCountSurvey) / $diff ;
}
}else{
$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;
$rsBranches = $this->fetchBranchesData($BranchID);
//print_r($rsBranches);
// ECHO $rsBranches[0]['db_branch_type'];
$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);
// if(!empty($BranchID) and empty($IndustryID))
// $SQL = "SELECT GROUP_CONCAT(branchId) AS BranchIDs FROM beatle_branch WHERE db_branch_type = ".$rsBranches[0]['db_branch_type']." ";
// $SQL .= " AND branchId != ".$rsBranches[0]['branchId']."";
// $rsBranchIDs = $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')
if(!empty($rsIndustryIDs[0]['IndustryIDs']))
$SQL_ .= " AND db_surveyIndId IN (".$rsIndustryIDs[0]['IndustryIDs'].") ";
else
$SQL_ .= " AND db_surveyIndId IN ('0') ";
if($type == 'us')
$SQL_ .= " AND orgid = ".$orgID. " AND db_surveyBranchid = ".$rsBranches[$i]['branchId'];
elseif($type == 'ind')
if(empty($BranchID) and empty($IndustryID))
$SQL .= " AND orgid != ".$orgID;
elseif(!empty($BranchID) and empty($IndustryID))
$SQL .= " AND db_surveyBranchid = ".$rsBranchIDs[$i]['BranchIDs'];
#######################################################
// 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);
}
$avgUSTotal = $avgUSTotal + $SUM;
$FromDate = date("d-m-Y",strtotime($FromDate." +1 day"));
//echo "</pre>";
}
}else{
//$returnArr[] = "0.00";
//echo "NO PAGES FOUND";
}
}
}
//print_r($returnArr);
if($type == 'us'){
if(fetchActiveScoreCheckedData() == "checked"){
if($avgUSTotal > 0){
$avgUSTotal = number_format(($avgUSTotal / fetchZeroFeedbackDays($orgID,$BranchID,$IndustryID,$this->FromDate,$this->ToDate)),2);
}else{
$avgUSTotal = "0.00";
}
}else{
if($avgUSTotal > 0){
$avgUSTotal = number_format(($avgUSTotal / $diff),2);
}else{
$avgUSTotal = "0.00";
}
}
}else{
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;
//echo "ind = ".$IndustryID." # ".$BranchID." # ".$orgID." <br />";
$SQL = "SELECT db_orgtype FROM beatle_organization WHERE OrgId = ".$orgID;
$rsOrganizations_ = $objDB->sql_query($SQL);
$orgType = $rsOrganizations_[0]['db_orgtype'];
$branchType ="";
if(!empty($BranchID))
$SQL = "SELECT db_branch_type FROM beatle_branch WHERE branchId = ".$BranchID;
$rsBranchDetails = $objDB->sql_query($SQL);
$branchType = $rsBranchDetails[0]['db_branch_type'];
$indTypeId ="";
if(!empty($IndustryID))
$SQL = "SELECT db_ind_type_id FROM beatle_industry WHERE IndId = ".$IndustryID;
$rsIndDetails = $objDB->sql_query($SQL);
$indTypeId = $rsIndDetails[0]['db_ind_type_id'];
// 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>";
$ustest = 0;
$avgUSTotal = 0;
$indc = 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>";
$indc = $indc+ count($rsIndustryData);
if(count($rsIndustryData) > 0) {
$avgUS = 0;
$avgInd = 0;
$avgIndArr = array();
$avgUsArr = array();
$avgUsArrt = array();
for($indd=0; $indd<count($rsIndustryData); $indd++) {
$frmDt = $FromDate;
while(strtotime($frmDt) < strtotime($ToDate)) {
$frmDt = date("Y-m-d",strtotime("+1 day".$frmDt));
if(!in_array($frmDt,$returnArr['date'])) {
$returnArr['date'][] = date("Y-m-d",strtotime($frmDt));
$returnArr['date_format'][] = date("'d/M'",strtotime($frmDt));
}
//echo "ind = ".$IndustryID." # ".$BranchID." # ".$orgID." <br />";
$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);
//$avgInd = $this->fetchRatingForOhterIndustry_Graph($atype,$rsIndustryData[$indd]['db_ind_type_id'],$orgID,$orgType,$BranchID,$branchType,$IndustryID,'ind',$frmDt,$frmDt,$PageID,$PageConID);
//echo "IND = ".$indd." > ".$atype." # ".$frmDt." # ".$avgInd." <br />";
if(empty($BranchID))
$avgUsArr[$indd][] = $avgUs / count($rsIndustryData);
else
$avgUsArr[$indd][] = $avgUs;
}
}
$frmDt = $FromDate;
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));
}
//$avgInd = $this->fetchRatingForOhterIndustry_Graph($atype,$rsIndustryData[$indd]['IndId'],$rsIndustryData[$indd]['db_ind_type_id'],$orgID,$orgType,'ind',$frmDt,$frmDt,$PageID,$PageConID,$PageConID,$BranchID);
$avgInd = $this->fetchRatingForOhterIndustry_Graph($atype,$indTypeId,$orgID,$orgType,$BranchID,$branchType,$IndustryID,'ind',$frmDt,$frmDt,$PageID,$PageConID);
//echo "IND = ".$indd." > ".$atype." # ".$frmDt." # ".$avgInd." <br />";
$avgIndArr[] = $avgInd;
//$avgUsArr[$indd][] = $avgUs;
}
}
$returnArr['avg_ind'][] = $avgIndArr;
$returnArr['avg_us'][] = $avgUsArr;
}
$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];
}
$usSum = array();
for($cnt=0; $cnt<count($returnArr['avg_us']); $cnt++){
for($c=count($returnArr['avg_us'][$cnt]);$c>=0 ; $c--){
$avg2 += $returnArr['avg_us'][$cnt][$c][$dt];
}
}
// ADDITIONAL ADDED
//$avg1 = $avg1/count($rsIndustryData);
$avg2 = $avg2/count($rsIndustryData);
//echo "count: ".count($returnArr['avg_us']);
$ArrArr[1][] = number_format($avg1,2);
//$ArrArr[1][] = number_format($avg1/count($returnArr['avg_ind']),2);
//$ArrArr[2][] = number_format($avg2/count($returnArr['avg_us']),2);
$ArrArr[2][] = number_format($avg2/count($returnArr['avg_us']),2);
}
$finalAvgArr[1] = $ArrArr[1];//ind
$finalAvgArr[2] = $ArrArr[2];//us
return $finalAvgArr;
}
public function fetchContentDetails($BranchID,$IndustryID){
global $objDB;
$rsBranches = $this->fetchBranchesData($BranchID,$IndustryID);
$BranchIDArr = array();
foreach($rsBranches as $key => $val){
$BranchIDArr[] = $val['branchId'];
}
//print_r($BranchIDArr);
//echo $IndustryID;
$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 pageId,db_pagename 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;
if($type == 'us')
$SQL .= " AND beatle_industry.IndId = ".$IndustryID;
else if($type == 'ind')
$SQL .= " AND beatle_industry.IndId != ".$IndustryID;
//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,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 ;
}elseif($type == 'ind'){
if(empty($BranchID) and empty($IndustryID)){
$SQL .= " AND orgid != ".$orgID;
}elseif(!empty($BranchID) and empty($IndustryID)){
$SQL .= " AND db_surveyBranchid != ".$BranchID;
}
}
$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($type == 'us'){
if(fetchActiveScoreCheckedData() == "checked"){
if($avg > 0){
$avg = number_format(($avg / fetchZeroFeedbackDays($orgID,$BranchID,$IndustryID,$this->FromDate,$this->ToDate)),2);
}else{
$avg = "0.00";
}
}else{
if($avg > 0){
$avg = number_format(($avg / $dtcnt),2);
}else{
$avg = "0.00";
}
}
}else{
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,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;
}elseif($type == 'ind'){
if(empty($BranchID) and empty($IndustryID)){
$SQL .= " AND orgid != ".$orgID;
}elseif(!empty($BranchID) and empty($IndustryID)){
$SQL .= " AND db_surveyBranchid != ".$BranchID;
}
}
$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(fetchActiveScoreCheckedData() == "checked"){
if($avg > 0){
$avg = number_format(($avg / fetchZeroFeedbackDays($orgID,$BranchID,$IndustryID,$this->FromDate,$this->ToDate)),2);
}else{
$avg = "0.00";
}
}else{
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 ;
}elseif($type == 'ind'){
if(empty($BranchID) and empty($IndustryID)){
$SQL .= " AND orgid != ".$orgID;
}elseif(!empty($BranchID) and empty($IndustryID)){
$SQL .= " AND db_surveyBranchid != ".$BranchID;
}
}
$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;
$fdate = date("Y-m-d",strtotime($fdate." +1 days"));
}
if(fetchActiveScoreCheckedData() == "checked"){
if($avg > 0){
$avg = number_format(($avg / fetchZeroFeedbackDays($orgID,$BranchID,$IndustryID,$this->FromDate,$this->ToDate)),2);
}else{
$avg = "0.00";
}
}else{
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 fetchIndustryWiseAverageName($avgValue,$PageContentID){
global $objDB;
$SQL ="SELECT db_pageChoice FROM beatle_pagecontent where pageConId = ".$PageContentID."";
$result = $objDB->sql_query($SQL);
$test = '';
//for($i=0; $i<count($result); $i++) {
$mark=explode(',', $result[0]['db_pageChoice']);
if ($avgValue > 0 and $avgValue <= 1){
$test = $mark[4];
}else if($avgValue > 1 and $avgValue <= 2){
$test = $mark[3];
}else if($avgValue > 2 and $avgValue <= 3){
$test = $mark[2];
}else if($avgValue > 3 and $avgValue <= 4){
$test = $mark[1];
}else if($avgValue > 4 and $avgValue <= 5){
$test = $mark[0];
}
return $test;
}
public function fetchRatingForOhterIndustry_Graph($atype,$indType,$orgID,$orgType,$BranchID,$branchType,$IndustryID,$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 $orgID."#".$orgType."#".$BranchID."#".$branchType."#".$IndustryID."#".$indType;
//echo $atype;
//echo "<br />";
//echo "<pre>";
//beatle_industry.db_ind_type_id = ".$indType." ";
$SQL ="";
if(empty($BranchID) && empty($IndustryID)){
$SQL = "SELECT
beatle_organization.OrgId AS OrgIDS,
beatle_organization.db_Orgname,
beatle_organization.db_orgtype,
beatle_branch.branchId,
beatle_branch.db_branchOrg,
beatle_branch.db_branch_type,
beatle_industry.IndId,
beatle_industry.db_industry,
beatle_industry.db_ind_type_id,
beatle_industry.db_pagesId
FROM
beatle_organization
INNER JOIN beatle_branch ON beatle_organization.OrgId = beatle_branch.db_branchOrg
INNER JOIN beatle_industry ON beatle_branch.branchId = beatle_industry.db_bracnchid
WHERE beatle_organization.OrgId <> ".$orgID." AND beatle_organization.db_orgtype = '".$orgType."' " ;
}else if(!empty($BranchID) && empty($IndustryID)){
$SQL = "SELECT
beatle_organization.OrgId AS OrgIDS,
beatle_organization.db_Orgname,
beatle_organization.db_orgtype,
beatle_branch.branchId,
beatle_branch.db_branchOrg,
beatle_branch.db_branch_type,
beatle_industry.IndId,
beatle_industry.db_industry,
beatle_industry.db_ind_type_id,
beatle_industry.db_pagesId
FROM
beatle_organization
INNER JOIN beatle_branch ON beatle_organization.OrgId = beatle_branch.db_branchOrg
INNER JOIN beatle_industry ON beatle_branch.branchId = beatle_industry.db_bracnchid
WHERE beatle_branch.branchId <> ".$BranchID." AND
beatle_branch.db_branch_type = '".$branchType."' " ;
//$rsIndustry = $objDB->sql_query($SQL);
}else if(!empty($BranchID) && !empty($IndustryID)){
$SQL = "SELECT
beatle_organization.OrgId AS OrgIDS,
beatle_organization.db_Orgname,
beatle_organization.db_orgtype,
beatle_branch.branchId,
beatle_branch.db_branchOrg,
beatle_branch.db_branch_type,
beatle_industry.IndId,
beatle_industry.db_industry,
beatle_industry.db_ind_type_id,
beatle_industry.db_pagesId
FROM
beatle_organization
INNER JOIN beatle_branch ON beatle_organization.OrgId = beatle_branch.db_branchOrg
INNER JOIN beatle_industry ON beatle_branch.branchId = beatle_industry.db_bracnchid
WHERE beatle_industry.IndId <> ".$IndustryID." AND
beatle_industry.db_ind_type_id = ".$indType." " ;
//$rsIndustry = $objDB->sql_query($SQL);
}
//$SQL = "SELECT * FROM beatle_industry WHERE IndId = ".$IndID;
$rsIndustry = $objDB->sql_query($SQL);
if (empty($rsIndustry)) {
return false;
}
//$SQL = "SELECT GROUP_CONCAT(IndId) AS IndustryIDs FROM beatle_industry WHERE db_ind_type_id = ".$rsIndustry[0]['db_ind_type_id']." ";
//echo "<br />";
//echo $rsIndustry[0]['db_ind_type_id'];
//$rsIndustryIDs = $objDB->sql_query($SQL);
$returnArr = array();
$avgIndArr = array();
$avgValue = "0.00";
if(count($rsIndustry) > 0){
//echo "<br />";
//echo $rsIndustry[0]['db_pagesId'];
//echo "<br />";
for($indst=0; $indst<count($rsIndustry); $indst++){
$OrgIDS_ = $rsIndustry[$indst]['OrgIDS'];
$rsPages = explode(",",$rsIndustry[$indst]['db_pagesId']);
if(count($rsPages) > 0 && !empty($rsIndustry[$indst]['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 ROUND(AVG(db_surveyValue),2) 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(!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 = number_format($rsCountSurvey[0]['AvgValue'],2);
//$avg1 += $returnArr['avg_ind'][$cnt][0][$dt];
$avgValue = $avgValue + $rsCountSurvey[0]['AvgValue'];
//$avgIndArr[$indst][] = $avgValue;
//echo $rsIndustry[$indst]['IndId']." # ".$avgValue;
//echo $rsIndustry[$indst]['branchId']." # ".$rsIndustry[$indst]['IndId'];
//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[$indst]['db_pagesId'].") 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);
//echo "|| ".$rsCountSurvey[0]['TotalCount']." || ".$diff;
//$avgValue = $rsCountSurvey[0]['TotalCount'] / $diff;
$avgValue = $avgValue + ($rsCountSurvey[0]['TotalCount']/$diff)/count($rsIndustry);
//echo "<br />";
if($avgValue <= 0){
$avgValue = "0.00";
}else{
$avgValue = number_format($avgValue,2);
}
}else if($atype == 'alr'){
$SQL = "SELECT ROUND(AVG(db_surveyValue),2) 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) 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 = number_format($rsCountSurvey[0]['AvgValue'],2);
$avgValue = $avgValue + $rsCountSurvey[0]['AvgValue'];
}
}else{
//echo "NO PAGES FOUND";
}
}
$returnArr['avg_ind'][] = $avgValue;
}
$avgTotal = "0.00";
$avg1 = 0;
for($cnt=0; $cnt<count($returnArr['avg_ind']); $cnt++){
$avg1 = $returnArr['avg_ind'][$cnt];
$avg1 = $avg1/count($rsIndustry);
$returnArr['avg_ind_final'][] = $avg1;
}
// echo "<pre>";
// print_r($returnArr['avg_ind_final']);
// echo "</pre>";
//echo $avgValue.", ";
//return $avgValue;
return $returnArr['avg_ind_final'][0];
}
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;
}
} ///// class include_login completed /////
?>