File: /home/barbeatleanalyti/public_html/barisnew.beatleanalytics.com/site/include/trainslist.php
<?php
/* <summary>
Class contains methods related to state module
<author>
Beatle Buddy 2017.
</author>
Version 1.0
</summary>
*/
class include_trainslist
{
/*
<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 * FROM baris_station";
$rsStaInfo = $objDB->sql_query($SQL);
$paramCount = 0;
for($i=0; $i<count($rsStaInfo); $i++){
//echo $value[0]['stationId'];
$SQL = "SELECT * FROM baris_userlogin";
$rsAudInfo = $objDB->sql_query($SQL);
for($a=0; $a<count($rsAudInfo); $a++){
$SQL = "SELECT * FROM baris_question";
$rsQueInfo = $objDB->sql_query($SQL);
for($q=0; $q<count($rsQueInfo); $q++){
$queStatus = getQuestionFeedbackStatus($rsStaInfo[$i]['stationId'],$rsQueInfo[$q]['queId'],$rsAudInfo[$a]['userId'],$startDate);
$pageIds = QuestionGroupIDS('pages',$rsQueInfo[$q]['queId']);
//echo count($questatus);
//echo ",";
for($qs=0; $qs<count($queStatus); $qs++){
$paraStatus = getParamFeedbackStatus($rsStaInfo[$i]['stationId'],$rsAudInfo[$a]['userId'],$rsQueInfo[$q]['queId'],$queStatus[$qs]['db_surveyParamId'],$startDate);
if(count($paraStatus) == (count($pageIds)+1)){
$paramCount++;
}
}
}
}
}
$returnArr['weekly_feedback_total'][] = $paramCount;
$startDate = date("Y-m-d",strtotime($startDate." +1 days"));
}
return $returnArr;
}
public function FetchUserData(){
global $objDB;
$SQL = "SELECT
baris_userlogin.db_username,
baris_userlogin.db_userLoginName,
baris_userlogin.db_phone,
baris_userlogin.db_email,
baris_userlogin.db_usertype,
baris_organization.db_Orgname,
baris_organization.OrgID,
baris_division.DivisionId,
baris_division.DivisionName,
baris_station.stationId,
baris_station.stationName,
baris_station.db_stLoginId
FROM
baris_userlogin
INNER JOIN baris_organization ON baris_userlogin.OrgID = baris_organization.OrgID
INNER JOIN baris_division ON baris_userlogin.DivisionId = baris_division.DivisionId
INNER JOIN baris_station ON baris_userlogin.StationId = baris_station.stationId
WHERE baris_userlogin.userId = ".$_SESSION['UserInfo']['UserID']." ";
$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'";
$rsUserInfo = $objDB->sql_query($SQL);
return $rsUserInfo;
}
public function FetchDivisionById($DivisionId)
{
global $objDB;
$SQL = "SELECT * FROM baris_division where DivisionId = ".$DivisionId;
$rsUserInfo = $objDB->sql_query($SQL);
return $rsUserInfo;
}
public function FetchStationIdById($stationId)
{
global $objDB;
$SQL = "SELECT * FROM baris_station where stationId = ".$stationId;
$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);
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 /////
?>