File: /home/barbeatleanalyti/public_html/barisnew.beatleanalytics.com/site/include/dasperformancelog.php
<?php
/* <summary>
Class contains methods related to state module
<author>
Beatle Buddy 2017.
</author>
Version 1.0
</summary>
*/
class include_dasperformancelog
{
/*
<summary>
Default constructor
</summary>
*/
function __CONSTRUCT(){
}
public function fetchRatingForNewReportWeekly(){
global $objDB;
$endDate = date("Y-m-d");
$startDate = date("Y-m-d",strtotime("-6 days"));
$returnArr = array();
while($startDate <= $endDate){
$returnArr['days'][] = strtoupper(substr(date("D",strtotime($startDate)),0,2));
$SQL = "SELECT tokenid FROM
baris_survey WHERE is_submit = 'Y' ";
$SQL .= "
AND OrgID = ".$_SESSION['UserInfo']['OrgID']." ";
$SQL .= " AND db_surveyQuestionId = '13' ";
$SQL .= " AND created_date BETWEEN '".date("Y-m-d",strtotime($startDate))." 00:00:00' AND '".date("Y-m-d",strtotime($startDate))." 23:59:59' ";
$SQL .= " GROUP BY tokenid";
$rsUserInfo_ = $objDB->sql_query($SQL);
$returnArr['weekly_feedback_count'][] = count($rsUserInfo_);
$weeklyScore = 0;
for ($j=0;$j< count($rsUserInfo_);$j++)
{
$rsCoachList = FetchCoachList($rsUserInfo_[$j]['tokenid']);
$totalScore = 0;
$totalCount = 0;
for ($c=0;$c < count($rsCoachList);$c++)
{
$SQL = "SELECT db_surveyValue AS AvgValue FROM baris_survey WHERE is_submit = 'Y' ";
if(!empty($rsCoachList[$c][0]))
$SQL .= " AND db_coachNo = '".$rsCoachList[$c][0]. "' ";
if(!empty($rsUserInfo_[$j]['tokenid']))
$SQL .= " AND tokenid = '".$rsUserInfo_[$j]['tokenid']. "' ";
$rsCountSurvey = $objDB->sql_query($SQL);
// echo $rsCountSurvey[0]['AvgValue'];
for ($i=0; $i < count($rsCountSurvey); $i++) {
if($rsCountSurvey[$i]['AvgValue'] == "1"){
$totalScore++;
}
if($rsCountSurvey[$i]['AvgValue'] == "1" || $rsCountSurvey[$i]['AvgValue'] == "0"){
$totalCount++;
}
}
}
$weeklyScore += round($totalScore*100/$totalCount,2);
}
$returnArr['weekly_feedback_total'][] = round($weeklyScore/count($rsUserInfo_),2);
$startDate = date("Y-m-d",strtotime($startDate." +1 days"));
}
return $returnArr;
}
public function FetchStationData()
{
global $objDB;
$SQL = "SELECT * FROM baris_station where OrgId = ".$_SESSION['UserInfo']['OrgID'];
$rsUserInfo = $objDB->sql_query($SQL);
return $rsUserInfo;
}
public function FetchAuditorsData()
{
global $objDB;
$SQL = "SELECT * FROM baris_userlogin where OrgId = ".$_SESSION['UserInfo']['OrgID']." and db_usertype = 'auditor' and reportType like '%PMC%' ";
$rsUserInfo = $objDB->sql_query($SQL);
return $rsUserInfo;
}
public function FetchOrganizationName(){
global $objDB;
$SQL = "SELECT
baris_organization.db_Orgname,
baris_organization.OrgId
FROM
baris_organization
RIGHT JOIN baris_userlogin ON baris_organization.OrgId = baris_userlogin.OrgID WHERE baris_userlogin.userId = ".$_SESSION['UserInfo']['UserID']." ";
$rsUserInfo = $objDB->sql_query($SQL);
return $rsUserInfo;
}
function fetchTrainsAttendedList()
{
global $objDB;
$SQL = "SELECT DISTINCT db_trainno, tokenid, created_date FROM
baris_survey WHERE is_submit = 'Y' ";
//if(!empty($this->AuditorId))
$SQL .= "
AND OrgID = ".$_SESSION['UserInfo']['OrgID']." ";
$SQL .= " AND db_surveyQuestionId = '13' ";
//$SQL .= " ORDER BY baris_survey.created_date DESC";
//$SQL .= " GROUP BY baris_survey.db_surveyPageId ORDER BY baris_survey.created_date DESC,baris_survey.db_surveyPageId ASC";
$SQL .= " GROUP BY tokenid ORDER BY created_date DESC LIMIT 10";
$rsUserInfo_ = $objDB->sql_query($SQL);
$dataArry = array();
for ($j=0;$j< count($rsUserInfo_);$j++)
{
$rsCoachList = FetchCoachList($rsUserInfo_[$j]['tokenid']);
// echo "<pre>";
// print_r($rsCoachList);
$totalScore = 0;
$totalCount = 0;
for ($c=0;$c < count($rsCoachList);$c++)
{
$SQL = "SELECT db_surveyValue AS AvgValue FROM baris_survey WHERE is_submit = 'Y' ";
if(!empty($rsCoachList[$c][0]))
$SQL .= " AND db_coachNo = '".$rsCoachList[$c][0]. "' ";
if(!empty($rsUserInfo_[$j]['tokenid']))
$SQL .= " AND tokenid = '".$rsUserInfo_[$j]['tokenid']. "' ";
$rsCountSurvey = $objDB->sql_query($SQL);
// echo $rsCountSurvey[0]['AvgValue'];
for ($i=0; $i < count($rsCountSurvey); $i++) {
if($rsCountSurvey[$i]['AvgValue'] == "1"){
$totalScore++;
}
if($rsCountSurvey[$i]['AvgValue'] == "1" || $rsCountSurvey[$i]['AvgValue'] == "0"){
$totalCount++;
}
}
}
$dataArry[$j]['trainno'] = $rsUserInfo_[$j]['db_trainno'];
$dataArry[$j]['created_date'] = $rsUserInfo_[$j]['created_date'];
$dataArry[$j]['score'] = round($totalScore*100/$totalCount,2);
}
return $dataArry;
}
/* 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);
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'])) {
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' ";
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"));
}
}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 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 fetchIndustryWiseAverageName($avgValue,$PageContentID){
global $objDB;
$SQL ="SELECT db_pageChoice FROM beatle_pagecontent where pageConId = ".$PageContentID."";
$result = $objDB->sql_query($SQL);
$test = '';
$mark=explode(',', $result[0]['db_pageChoice']);
if ($avgValue > 0 and $avgValue <= 1){
$test = $mark[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;
}
} ///// class include_login completed /////
?>