File: /home/barbeatleanalyti/www/baris.beatleanalytics.com/site/include/pmcbillinvoice.php
<?php
/* <summary>
Class contains methods related to state module
<author>
Beatle Buddy 2017.
</author>
Version 1.0
</summary>
*/
class include_pmcbillinvoice
{
/*
<summary>
Default constructor
</summary>
*/
function __CONSTRUCT(){
}
public function fetchBillRate(){
global $objDB;
$SQL = "SELECT * FROM baris_bill_rate where OrgId = ".$_SESSION['UserInfo']['OrgID']." and report_type = 'PMC' ";
$rsBillRateInfo = $objDB->sql_query($SQL);
return $rsBillRateInfo;
}
public function getPenaltyRate($value)
{
if($value >= 75 && $value < 85){
return 5;
}elseif ($value >= 65 && $value < 75) {
return 10;
}elseif ($value >= 50 && $value < 65) {
return 15;
}elseif($value < 50){
return 20;
}
}
public function getPenaltyRate1($value)
{
if($value >= 75 && $value < 85){
return 5;
}elseif ($value >= 65 && $value < 75) {
return 10;
}elseif ($value >= 50 && $value < 65) {
return 15;
}elseif($value < 50){
return 30;
}
}
public function getImposePenalty($FromDate,$ToDate,$reportType,$penaltyType)
{
global $objDB;
$SQL = "SELECT SUM(penalty_amount) AS AvgValue FROM baris_penalty WHERE ";
$SQL .= " OrgId = ".$_SESSION['UserInfo']['OrgID'];
$SQL .= " AND report_type = '".$reportType."' ";
$SQL .= " AND penalty_type = '".$penaltyType."' ";
$SQL .= " AND penalty_date BETWEEN '".date("Y-m-d",strtotime($FromDate))."' AND '".date("Y-m-d",strtotime($ToDate))."' ";
// echo $SQL;die;
$rsData = $objDB->sql_query($SQL);
if($rsData[0]['AvgValue'] == NULL) return '0';
else
return $rsData[0]['AvgValue'];
}
/////////////new report start///////////////
public function fetchStation($stationId = 0){
global $objDB;
if($stationId != 0)
$SQL = "SELECT * FROM baris_station WHERE stationId = ".$stationId;
else
$SQL = "SELECT * FROM baris_station where OrgId = ".$_SESSION['UserInfo']['OrgID'];
$rsStaInfo = $objDB->sql_query($SQL);
return $rsStaInfo;
}
public function FetchQuestion($queId = 0){
global $objDB;
if($queId != 0)
$SQL = "SELECT * FROM baris_question WHERE queId = ".$queId;
else
$SQL = "SELECT * FROM baris_question";
$rsQueInfo = $objDB->sql_query($SQL);
return $rsQueInfo;
}
public function FetchAuditor($userId = 0){
global $objDB;
if($userId != 0)
$SQL = "SELECT * FROM baris_userlogin WHERE db_usertype = 'auditor' and userId = ".$userId;
else
$SQL = "SELECT * FROM baris_userlogin where db_usertype = 'auditor' and reportType like '%WAT%' and OrgId = ".$_SESSION['UserInfo']['OrgID'];
$rsAudInfo = $objDB->sql_query($SQL);
return $rsAudInfo;
}
public function SelectBarisParamName($paramID){
global $objDB;
$SQL = "SELECT paramName FROM baris_param WHERE paramId = ".$paramID;
$rsParamInfo = $objDB->sql_query($SQL);
return $rsParamInfo[0]['paramName'];
}
public function SelectBarisPageName($pageId){
global $objDB;
$SQL = "SELECT db_pagename FROM baris_page WHERE pageId = ".$pageId;
$rsPageInfo = $objDB->sql_query($SQL);
return $rsPageInfo[0]['db_pagename'];
}
public function getcommondata($table,$data,$fieldname,$fieldid){
global $objDB;
$SQL = "SELECT ".$data." FROM ".$table." WHERE ".$fieldname." = ".$fieldid;
$rsParamInfo = $objDB->sql_query($SQL);
return $rsParamInfo[0][$data];
}
public function FetchUsersData(){
global $objDB;
$SQL = "SELECT DISTINCT db_surveyUserid AS db_surveyUserid FROM baris_survey WHERE ";
if(!empty($this->StationId))
$SQL .= "
db_surveyStationId = ".$this->StationId."";
if(!empty($this->AuditorId))
$SQL .= "
AND db_surveyUserid = ".$this->AuditorId." " ;
if(!empty($this->QuestionId))
$SQL .= "
AND db_surveyQuestionId = ".$this->QuestionId." " ;
$SQL .= "
AND is_submit = 'Y' GROUP BY created_date";
$rsUserIDS = $objDB->sql_query($SQL);
//echo $rsUserIDS[0]['db_surveyUserid'];
//print_r($rsUserIDS);
if(count($rsUserIDS) <= 0)
return array();
$idArr = array();
for($i=0; $i<count($rsUserIDS); $i++){
if(!in_array($rsUserIDS[$i]['db_surveyUserid'],$idArr)) {
$idArr[] = $rsUserIDS[$i]['db_surveyUserid'];
}
}
return $idArr;
}
public function FetchQuestionsData($QuestionId,$subqueId,$date){
global $objDB;
$SQL = "SELECT tokenid FROM
baris_survey WHERE is_submit = 'Y' ";
$SQL .= "
AND created_date BETWEEN '".date("Y-m-d",strtotime($date))." 00:00:00' AND '".date("Y-m-d",strtotime($date))." 23:59:59' ";
$SQL .= " AND OrgID = ".$_SESSION['UserInfo']['OrgID']." " ;
$SQL .= "
AND db_surveyQuestionId = ".$QuestionId ;
$SQL .= "
AND db_surveySubQuestionId = ".$subqueId ;
$SQL .= " GROUP BY tokenid ORDER BY created_date DESC";
$rsUserInfo_ = $objDB->sql_query($SQL);
/*echo "<pre>";
print_r($rsUserInfo_);
echo "</pre>";
die();*/
$surveyArr = array();
for ($i=0;$i< count($rsUserInfo_);$i++)
{
//echo $rsUserInfo_[$i]['tokenid'];
$SQL = "SELECT
baris_survey.surveyId,
baris_survey.db_surveyStationId,
baris_survey.db_surveyUserid,
baris_survey.db_surveyQuestionId,
baris_survey.db_surveyParamId,
baris_survey.db_coachNo,
baris_survey.db_coachName,
baris_survey.db_surveyPageId,
baris_survey.db_surveyValue,
baris_survey.OrgID,
baris_survey.DivisionId,
baris_survey.created_date,
baris_survey.updated_date,
baris_survey.tokenid,
baris_survey.is_submit,
baris_survey.db_trainno,
baris_survey.db_depart_time,
baris_survey.db_totalCoachCount,
baris_userlogin.db_phone,
baris_userlogin.db_email,
baris_userlogin.db_username,
baris_userlogin.userId
FROM
baris_survey
INNER JOIN baris_userlogin ON baris_survey.db_surveyUserid = baris_userlogin.userId
WHERE baris_survey.is_submit = 'Y'
AND LOWER(baris_userlogin.db_usertype) = 'auditor' ";
$SQL .= "
AND baris_survey.created_date BETWEEN '".date("Y-m-d",strtotime($date))." 00:00:00' AND '".date("Y-m-d",strtotime($date))." 23:59:59' ";
$SQL .= "
AND baris_survey.db_surveySubQuestionId = ".$subqueId ;
$SQL .= "
AND baris_survey.db_surveyPageId != '15' " ;
$SQL .= "
AND baris_survey.tokenid = '".$rsUserInfo_[$i]['tokenid']."' " ;
//$SQL .= " ORDER BY baris_survey.created_date DESC";
$SQL .= " GROUP BY baris_survey.db_surveyPageId ORDER BY baris_survey.db_surveyPageId ASC, baris_survey.created_date DESC";
$surveyArr[] = $objDB->sql_query($SQL);
}
return $surveyArr;
}
public function FetchQuestionsDataMachine($FromDate,$ToDate,$QuestionId,$subqueId){
global $objDB;
$SQL = "SELECT tokenid FROM
baris_survey WHERE is_submit = 'Y' ";
$SQL .= "
AND created_date BETWEEN '".date("Y-m-d",strtotime($FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($ToDate))." 23:59:59' ";
$SQL .= " AND OrgID = ".$_SESSION['UserInfo']['OrgID']." " ;
$SQL .= "
AND db_surveyQuestionId = ".$QuestionId ;
$SQL .= "
AND db_surveySubQuestionId = ".$subqueId ;
$SQL .= " GROUP BY tokenid ORDER BY created_date DESC";
$rsUserInfo_ = $objDB->sql_query($SQL);
/*echo "<pre>";
print_r($rsUserInfo_);
echo "</pre>";
die();*/
$surveyArr = array();
for ($i=0;$i< count($rsUserInfo_);$i++)
{
//echo $rsUserInfo_[$i]['tokenid'];
$SQL = "SELECT
baris_survey.surveyId,
baris_survey.db_surveyStationId,
baris_survey.db_surveyUserid,
baris_survey.db_surveyQuestionId,
baris_survey.db_surveyParamId,
baris_survey.db_coachNo,
baris_survey.db_coachName,
baris_survey.db_surveyPageId,
baris_survey.db_surveyValue,
baris_survey.OrgID,
baris_survey.DivisionId,
baris_survey.created_date,
baris_survey.updated_date,
baris_survey.tokenid,
baris_survey.is_submit,
baris_survey.db_trainno,
baris_survey.db_depart_time,
baris_survey.db_totalCoachCount,
baris_userlogin.db_phone,
baris_userlogin.db_email,
baris_userlogin.db_username,
baris_userlogin.userId
FROM
baris_survey
INNER JOIN baris_userlogin ON baris_survey.db_surveyUserid = baris_userlogin.userId
WHERE baris_survey.is_submit = 'Y'
AND LOWER(baris_userlogin.db_usertype) = 'auditor' ";
$SQL .= "
AND baris_survey.created_date BETWEEN '".date("Y-m-d",strtotime($FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($ToDate))." 23:59:59' ";
$SQL .= "
AND baris_survey.db_surveySubQuestionId = ".$subqueId ;
$SQL .= "
AND baris_survey.db_surveyPageId != '15' " ;
$SQL .= "
AND baris_survey.tokenid = '".$rsUserInfo_[$i]['tokenid']."' " ;
//$SQL .= " ORDER BY baris_survey.created_date DESC";
$SQL .= " GROUP BY baris_survey.db_surveyPageId ORDER BY baris_survey.db_surveyPageId ASC, baris_survey.created_date DESC";
$surveyArr[] = $objDB->sql_query($SQL);
}
return $surveyArr;
}
public function FetchValueScoreMachine($queId,$subqueId,$PageParamID,$PageID="",$tokenID=""){
global $objDB;
//$pageIDArr = IndustryPageIDS($pType,$BranchID,$IndustryID);
$SQL = "SELECT db_surveyValue AS AvgValue FROM baris_survey WHERE is_submit = 'Y' ";
if(!empty($queId))
$SQL .= " AND db_surveyQuestionId = ".$queId." " ;
if(!empty($subqueId))
$SQL .= " AND db_surveySubQuestionId = ".$subqueId." " ;
if(!empty($PageParamID))
$SQL .= " AND db_surveyParamId = ".$PageParamID. " ";
if(!empty($PageID))
$SQL .= " AND db_surveyPageId = '".$PageID. "' ";
if(!empty($tokenID))
$SQL .= " AND tokenid = '".$tokenID. "' ";
//$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' ";
$rsCountSurvey = $objDB->sql_query($SQL);
// if($rsCountSurvey[0]['AvgValue'] == "0"){
// $rsCountSurvey[0]['AvgValue'] == "0";
// }elseif ($rsCountSurvey[0]['AvgValue'] == "1") {
// $rsCountSurvey[0]['AvgValue'] == "1";
// }else{
// $rsCountSurvey[0]['AvgValue'] = "X";
// }
return $rsCountSurvey[0]['AvgValue'];
}
public function FetchValueScore($queId,$subqueId,$PageParamID,$PageID="",$tokenID=""){
global $objDB;
//$pageIDArr = IndustryPageIDS($pType,$BranchID,$IndustryID);
$SQL = "SELECT db_surveyValue AS AvgValue FROM baris_survey WHERE is_submit = 'Y' ";
if(!empty($queId))
$SQL .= " AND db_surveyQuestionId = ".$queId." " ;
if(!empty($subqueId))
$SQL .= " AND db_surveySubQuestionId = ".$subqueId." " ;
if(!empty($PageParamID))
$SQL .= " AND db_surveyParamId = ".$PageParamID. " ";
if(!empty($PageID))
$SQL .= " AND db_surveyPageId = '".$PageID. "' ";
if(!empty($tokenID))
$SQL .= " AND tokenid = '".$tokenID. "' ";
$rsCountSurvey = $objDB->sql_query($SQL);
return $rsCountSurvey[0]['AvgValue'];
}
public function FetchValueScoreConsumable($queId,$subqueId,$PageParamID,$PageID="",$month="",$year=""){
global $objDB;
//$pageIDArr = IndustryPageIDS($pType,$BranchID,$IndustryID);
$SQL = "SELECT SUM(db_surveyValue) AS AvgValue FROM baris_survey WHERE db_surveyPageid = ".$PageID." AND db_surveyValue AND is_submit = 'Y' AND OrgId = ".$_SESSION['UserInfo']['OrgID'];
if(!empty($queId))
$SQL .= " AND db_surveyQuestionId = ".$queId." " ;
if(!empty($subqueId))
$SQL .= " AND db_surveySubQuestionId = ".$subqueId." " ;
if(!empty($this->StationId))
$SQL .= "
AND db_surveyStationId = ".$this->StationId." " ;
if(!empty($PageParamID))
$SQL .= " AND db_surveyParamId = ".$PageParamID. " ";
$SQL .= " AND year(created_date) between ".$year." and ".$year." and month(created_date) between ".$month." and ".$month." ";
$rsCountSurvey = $objDB->sql_query($SQL);
return $rsCountSurvey[0]['AvgValue'];
}
public function FetchQuestionsDataManpower($QuestionId,$subqueId,$date){
global $objDB;
//$rsPagesIDS = QuestionGroupIDS('pages',$this->QuestionId);
//$rsParamIDS = QuestionGroupIDS('params',$this->QuestionId);
//$SQL = "SELECT * FROM beatle_survey WHERE is_submit = 'Y' AND db_surveyUserid IN (".implode(",",$idArr).") AND orgid = ".$_SESSION['OtherInfo']['OrgID']." GROUP BY db_surveyUserid ORDER BY created_date DESC";
//if(count($idArr) > 0) {
$SQL = "SELECT tokenid FROM
baris_survey WHERE is_submit = 'Y' ";
$SQL .= "
AND created_date BETWEEN '".date("Y-m-d",strtotime($date))." 00:00:00' AND '".date("Y-m-d",strtotime($date))." 23:59:59' ";
$SQL .= " AND OrgID = ".$_SESSION['UserInfo']['OrgID']." " ;
$SQL .= "
AND db_surveyQuestionId = ".$QuestionId;
$SQL .= "
AND db_surveySubQuestionId = ".$subqueId;
$SQL .= " GROUP BY tokenid ORDER BY created_date DESC";
$rsUserInfo_ = $objDB->sql_query($SQL);
/*echo "<pre>";
print_r($rsUserInfo_);
echo "</pre>";
die();*/
$surveyArr = array();
for ($i=0;$i< count($rsUserInfo_);$i++)
{
//echo $rsUserInfo_[$i]['tokenid'];
$SQL = "SELECT
baris_survey.surveyId,
baris_survey.db_surveyStationId,
baris_survey.db_surveyUserid,
baris_survey.db_surveyQuestionId,
baris_survey.db_surveyParamId,
baris_survey.db_coachNo,
baris_survey.db_coachName,
baris_survey.db_surveyPageId,
baris_survey.db_surveyValue,
baris_survey.OrgID,
baris_survey.DivisionId,
baris_survey.created_date,
baris_survey.updated_date,
baris_survey.tokenid,
baris_survey.is_submit,
baris_survey.db_trainno,
baris_survey.db_depart_time,
baris_survey.db_totalCoachCount,
baris_survey.extra_manpower,
baris_userlogin.db_phone,
baris_userlogin.db_email,
baris_userlogin.db_username,
baris_userlogin.userId
FROM
baris_survey
INNER JOIN baris_userlogin ON baris_survey.db_surveyUserid = baris_userlogin.userId
WHERE baris_survey.is_submit = 'Y'
AND LOWER(baris_userlogin.db_usertype) = 'auditor' ";
$SQL .= "
AND baris_survey.created_date BETWEEN '".date("Y-m-d",strtotime($date))." 00:00:00' AND '".date("Y-m-d",strtotime($date))." 23:59:59' ";
$SQL .= "
AND baris_survey.db_surveySubQuestionId = ".$subqueId ;
$SQL .= "
AND baris_survey.db_surveyPageId != '15' " ;
$SQL .= "
AND baris_survey.tokenid = '".$rsUserInfo_[$i]['tokenid']."' " ;
//$SQL .= " ORDER BY baris_survey.created_date DESC";
$SQL .= " GROUP BY baris_survey.db_surveyPageId ORDER BY baris_survey.db_surveyPageId ASC, baris_survey.created_date DESC";
$surveyArr[] = $objDB->sql_query($SQL);
}
return $surveyArr;
}
public function FetchValueScoreManpower($queId,$subqueId,$PageParamID,$PageID="",$value,$tokenID=""){
global $objDB;
//$pageIDArr = IndustryPageIDS($pType,$BranchID,$IndustryID);
$SQL = "SELECT db_surveyValue AS AvgValue FROM baris_survey WHERE is_submit = 'Y' ";
if(!empty($queId))
$SQL .= " AND db_surveyQuestionId = ".$queId." " ;
if(!empty($subqueId))
$SQL .= " AND db_surveySubQuestionId = ".$subqueId." " ;
if(!empty($PageParamID))
$SQL .= " AND db_surveyParamId = ".$PageParamID. " ";
if(!empty($PageID))
$SQL .= " AND db_surveyPageId = '".$PageID. "' ";
if(!empty($value))
$SQL .= " AND extra_manpower = '".$value. "' ";
if(!empty($tokenID))
$SQL .= " AND tokenid = '".$tokenID. "' ";
//$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' ";
$rsCountSurvey = $objDB->sql_query($SQL);
return $rsCountSurvey[0]['AvgValue'];
}
public function getParamsData($id){
global $objDB;
$SQL = "SELECT * FROM baris_param WHERE paramId = ".$id;
$rsOwnerInfo = $objDB->sql_query($SQL);
return $rsOwnerInfo;
}
public function SelectParamName($id){
global $objDB;
$SQL = "SELECT paramName FROM baris_param WHERE paramId = ".$id;
$rsOwnerInfo = $objDB->sql_query($SQL);
return $rsOwnerInfo[0]['paramName'];
}
public function SelectPageName($id){
global $objDB;
$SQL = "SELECT db_pagename FROM baris_page WHERE pageId = ".$id;
$rsOwnerInfo = $objDB->sql_query($SQL);
return $rsOwnerInfo[0]['db_pagename'];
}
public function getPagesData($id){
global $objDB;
$SQL = "SELECT * FROM baris_page WHERE pageId = ".$id;
$rsOwnerInfo = $objDB->sql_query($SQL);
return $rsOwnerInfo;
}
public function getMonthlyTarget($queId,$subqueId,$pageId,$month,$year){
global $objDB;
$SQL = "SELECT * FROM baris_target WHERE OrgID = ".$_SESSION['UserInfo']['OrgID']." AND queId = ".$queId." AND subqueId = ".$subqueId." AND pageId = ".$pageId." AND month = ".$month." AND year = ".$year;
$rsOwnerInfo = $objDB->sql_query($SQL);
return $rsOwnerInfo;
}
public function getTarget($queId,$subqueId,$paramId){
global $objDB;
$SQL = "SELECT * FROM baris_target WHERE OrgID = ".$_SESSION['UserInfo']['OrgID']." AND queId = ".$queId." AND subqueId = ".$subqueId." AND pageId = ".$paramId;
$rsOwnerInfo = $objDB->sql_query($SQL);
return $rsOwnerInfo;
}
//SELECT * FROM `baris_survey` WHERE year( `created_date` ) between 2020 and 2020 and month( `created_date` ) between 06 and 06
/////////////new report end/////////////////
protected function EditReligionData(){}
protected function InsertUserInfo(){} ///// InsertUserInfo completed /////
public function setDefineValue(){
global $objDB;
$rsINDID = FetchIndustryIDS($this->IndustryID);
//print_r($rsINDID[0]['db_pagesId']);
$SQL = "SELECT beatle_pagecontent.db_pageConTitle, beatle_pagecontent.db_pageChoice, beatle_pagecontent.pageConId,beatle_page.pageId FROM beatle_page INNER JOIN beatle_pagecontent ON beatle_page.pageId = beatle_pagecontent.db_pageid WHERE beatle_page.pageId IN (".$rsINDID[0]['db_pagesId'].") AND beatle_pagecontent.db_pageselection = 'multiline' ";
$rsPageContent = $objDB->sql_query($SQL);
if(count($rsPageContent) > 0){
define("QUESTION_DEFAULT_SENTENCE_FILTER",$rsPageContent[0]['db_pageConTitle']);
}
}
public function FetchCustomerData($Val=""){
global $objDB;
$SQL = "SELECT GROUP_CONCAT(DISTINCT db_surveyUserid SEPARATOR ',') AS db_surveyUserid FROM beatle_survey WHERE orgid = ".$_SESSION['OtherInfo']['OrgID']." AND is_submit = 'Y' GROUP BY created_date";
$rsUserIDS = $objDB->sql_query($SQL);
if(count($rsUserIDS) <= 0)
return array();
$idArr = array();
for($i=0; $i<count($rsUserIDS); $i++){
if(!in_array($rsUserIDS[$i]['db_surveyUserid'],$idArr)) {
$idArr[] = $rsUserIDS[$i]['db_surveyUserid'];
}
}
$SQL = "SELECT userId FROM beatle_userlogin WHERE userId IN (".implode(',',$idArr).") AND lower(db_usertype) = 'end_user'";
if(!empty($this->CustomerID)){
$SQL .= " AND `userId` = ".$this->CustomerID;
}
$rsUserInfo = $objDB->sql_query($SQL);
$idArr = array();
for($i=0; $i<count($rsUserInfo); $i++){
if(!in_array($rsUserInfo[$i]['userId'],$idArr)) {
$idArr[] = $rsUserInfo[$i]['userId'];
}
}
//$SQL = "SELECT * FROM beatle_survey WHERE is_submit = 'Y' AND db_surveyUserid IN (".implode(",",$idArr).") AND orgid = ".$_SESSION['OtherInfo']['OrgID']." GROUP BY db_surveyUserid ORDER BY created_date DESC";
//if(count($idArr) > 0) {
$SQL = "SELECT
beatle_survey.surveyId,
beatle_survey.db_surveyBranchid,
beatle_survey.db_surveyUserid,
beatle_survey.db_surveyIndId,
beatle_survey.db_surveyPageid,
beatle_survey.db_surveyContentId,
beatle_survey.db_surveyValue,
beatle_survey.created_date,
beatle_survey.updated_date,
beatle_survey.tokenid,
beatle_survey.orgid,
beatle_survey.keyword,
beatle_survey.paxNo,
beatle_survey.totalBill,
beatle_survey.servername,
beatle_survey.is_submit,
beatle_survey.db_trainno,
beatle_survey.db_depart_time,
beatle_userlogin.db_phone,
beatle_userlogin.db_userLoginName,
beatle_userlogin.db_email,
beatle_userlogin.db_username,
beatle_userlogin.userId
FROM
beatle_survey
INNER JOIN beatle_userlogin ON beatle_survey.db_surveyUserid = beatle_userlogin.userId
WHERE beatle_survey.is_submit = 'Y'
AND LOWER(beatle_userlogin.db_usertype) = 'end_user' ";
//AND beatle_survey.db_surveyUserid IN (".implode(",",$idArr).") ";
//if(!empty($this->FromDate) && !empty($this->ToDate)){
$SQL .= "
AND beatle_survey.created_date BETWEEN '".date("Y-m-d",strtotime($this->FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($this->ToDate))." 23:59:59' ";
//}
if(!empty($this->OrgID))
$SQL .= "
AND beatle_survey.orgid = ".$this->OrgID." " ;
else
$SQL .= "
AND beatle_survey.orgid = ".$_SESSION['OtherInfo']['OrgID']." " ;
if(!empty($this->BranchID))
$SQL .= "
AND beatle_survey.db_surveyBranchid = ".$this->BranchID." " ;
if(!empty($this->IndustryID))
$SQL .= "
AND beatle_survey.db_surveyIndId = ".$this->IndustryID." " ;
if(!empty($this->TokenID))
$SQL .= "
AND beatle_survey.tokenid = '".$this->TokenID."' " ;
$SQL_Main = $SQL;
$surveyIDArr = array();
$cntArr = array();
if(isset($_POST['pageid']) && $_POST['pageid'] != ''){
if(isset($_POST['pg_ids_'])){
foreach($_POST['pg_ids_'] as $val){
if($val != 0 && !empty($val) && !empty($_POST['name_'.$val])){
$SQL = $SQL_Main."
AND beatle_survey.db_surveyContentId = '".$val."' AND beatle_survey.db_surveyValue = '".$_POST['name_'.$val]."' GROUP BY beatle_survey.tokenid ORDER BY beatle_survey.created_date DESC" ;
//$SQL .= " GROUP BY beatle_survey.tokenid ORDER BY beatle_survey.created_date ASC";
$rsDataIDS = $objDB->sql_query($SQL);
$newArr = array();
for($i=0; $i<count($rsDataIDS); $i++){
//if(!in_array($rsDataIDS[$i]['surveyId'],$surveyIDArr))
//$surveyIDArr[] = $rsDataIDS[$i]['surveyId'];
$newArr[] = $rsDataIDS[$i]['tokenid'];
}
//echo '############<br />';
//print_r($newArr);
//echo '############<br />';
if(count($surveyIDArr) <= 0){
$surveyIDArr = $newArr;
}else{
$surveyIDArr = array_intersect($surveyIDArr,$newArr);
}
}else{
$cntArr[] = $val;
}
}
}else{
}
}else{
$SQL .= " GROUP BY beatle_survey.tokenid ORDER BY beatle_survey.created_date DESC";
return $rsUserInfo_ = $objDB->sql_query($SQL);
}
if(count($cntArr) == count($_POST['pg_ids_'])){
$SQL .= " GROUP BY beatle_survey.tokenid ORDER BY beatle_survey.created_date DESC";
return $rsUserInfo_ = $objDB->sql_query($SQL);
}
$imp = "'" . implode( "','", $surveyIDArr) . "'";
//$SQL = "SELECT * FROM beatle_survey WHERE tokenid IN (".$imp.") GROUP BY beatle_survey.tokenid ORDER BY beatle_survey.created_date DESC";
//$SQL = "SELECT beatle_survey.*, beatle_userlogin.db_phone FROM beatle_survey INNER JOIN beatle_userlogin ON
//beatle_survey.db_surveyUserid = beatle_userlogin.userId WHERE beatle_survey.tokenid IN (".$imp.") GROUP BY beatle_survey.tokenid ORDER BY beatle_survey.created_date DESC";
return $rsUserInfo_ = $objDB->sql_query($SQL);
}
public function FetchTrendCustomerData($FromDate,$ToDate){
global $objDB;
$SQL = "SELECT DISTINCT beatle_survey.db_surveyUserid,
beatle_userlogin.db_username,
beatle_userlogin.db_phone
FROM
beatle_survey
INNER JOIN beatle_userlogin ON beatle_survey.db_surveyUserid = beatle_userlogin.userId
WHERE beatle_survey.is_submit = 'Y' ";
if(!empty($this->OrgID))
$SQL .= "
AND beatle_survey.orgid = ".$this->OrgID." " ;
else
$SQL .= "
AND beatle_survey.orgid = ".$_SESSION['OtherInfo']['OrgID']." " ;
if(!empty($this->BranchID))
$SQL .= "
AND beatle_survey.db_surveyBranchid = ".$this->BranchID." " ;
if(!empty($this->IndustryID))
$SQL .= "
AND beatle_survey.db_surveyIndId = ".$this->IndustryID." " ;
$SQL .= "
AND beatle_survey.created_date BETWEEN '".date("Y-m-d",strtotime($FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($ToDate))." 23:59:59' GROUP BY beatle_survey.tokenid";
// $SQL .= "
// GROUP BY beatle_survey.tokenid " ;
$rsUserVisit_ = $objDB->sql_query($SQL);
return $rsUserVisit_;
}
public function FetchTrendTotalBill($uid,$FromDate,$ToDate){
global $objDB;
$SUMB = 0;
$SUMP = 0;
//$SUMV = 0;
$returnArr = array();
$SQL = "SELECT totalBill, paxNo FROM beatle_survey WHERE db_surveyUserid = ".$uid." AND is_submit = 'Y'";
if(!empty($this->OrgID))
$SQL .= "
AND orgid = ".$this->OrgID." " ;
else
$SQL .= "
AND orgid = ".$_SESSION['OtherInfo']['OrgID']." " ;
if(!empty($this->BranchID))
$SQL .= "
AND db_surveyBranchid = ".$this->BranchID." " ;
if(!empty($this->IndustryID))
$SQL .= "
AND db_surveyIndId = ".$this->IndustryID." " ;
$SQL .= "
AND created_date BETWEEN '".date("Y-m-d",strtotime($FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($ToDate))." 23:59:59' GROUP BY tokenid";
$rsUserBill = $objDB->sql_query($SQL);
if(count($rsUserBill) > 0){
for($i=0;$i<count($rsUserBill);$i++){
$SUMB = $SUMB + $rsUserBill[$i]['totalBill'];
$SUMP = $SUMP + $rsUserBill[$i]['paxNo'];
//$SUMV = $SUMV + $rsUserBill[$i]['visit'];
}
}
$returnArr['totalBill'] = $SUMB;
$returnArr['totalPax'] = $SUMP;
$returnArr['visit'] = count($rsUserBill);
return $returnArr;
}
public function FetchTotalVisits($uid,$FromDate,$ToDate){
global $objDB;
// $SQL_ = " SELECT count(db_surveyUserid) , created_date FROM beatle_survey WHERE
// db_surveyPageid IN (3,4,6,8,9) AND
// db_surveyValue IN (1,2,3,4) AND is_submit = 'Y' AND db_surveyUserid = ".$uid."
// AND db_surveyIndId IN (18)
// AND orgid = '9' AND db_surveyBranchid ='13'
// GROUP BY tokenid";
$SQL = "SELECT count(*) FROM beatle_survey WHERE db_surveyUserid = ".$uid." AND db_surveyValue IN (1,2,3,4,5) AND is_submit = 'Y'
AND orgid = ".$this->OrgID."
AND db_surveyBranchid = ".$this->BranchID."
AND db_surveyIndId = ".$this->IndustryID."
AND created_date BETWEEN '".date("Y-m-d",strtotime($FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($ToDate))." 23:59:59' GROUP BY tokenid";
// if(!empty($this->OrgID))
// $SQL .= "
// AND orgid = ".$this->OrgID." " ;
// else
// $SQL .= "
// AND orgid = ".$_SESSION['OtherInfo']['OrgID']." " ;
// if(!empty($this->BranchID))
// $SQL .= "
// AND db_surveyBranchid = ".$this->BranchID." " ;
// if(!empty($this->IndustryID))
// $SQL .= "
// AND db_surveyIndId = ".$this->IndustryID." " ;
// $SQL .= "
// GROUP BY tokenid " ;
$rsUserVisit = $objDB->sql_query($SQL);
return count($rsUserVisit);
}
public function FetchTotalRows($FromDate,$ToDate){
global $objDB;
$SQL = "SELECT count(*) FROM beatle_survey WHERE db_surveyValue IN (1,2,3,4,5) AND is_submit = 'Y'
AND orgid = ".$this->OrgID."
AND db_surveyBranchid = ".$this->BranchID."
AND db_surveyIndId = ".$this->IndustryID."
AND created_date BETWEEN '".date("Y-m-d",strtotime($FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($ToDate))." 23:59:59' GROUP BY tokenid";
$rsUserVisit = $objDB->sql_query($SQL);
return count($rsUserVisit);
}
public function fetchOrganization($orgid = 0){
global $objDB;
if($orgid != 0)
$SQL = "SELECT OrgId,db_Orgname FROM beatle_organization WHERE OrgId = ".$orgid;
else
$SQL = "SELECT OrgId,db_Orgname FROM beatle_organization WHERE OrgId = ".$_SESSION['OtherInfo']['OrgID'];
$rsOrgInfo = $objDB->sql_query($SQL);
return $rsOrgInfo;
}
public function fetchBranches(){
global $objDB;
$SQL = '';
if(strtolower($_SESSION['UserInfo']['UType']) == 'owner') {
$SQL = "SELECT db_branchName,branchId FROM beatle_branch WHERE db_branchOrg = ".$_SESSION['OtherInfo']['OrgID'];
}else if(strtolower($_SESSION['UserInfo']['UType']) == 'manager'){
$SQL = "SELECT db_branchName,branchId FROM beatle_branch WHERE db_branchOrg = ".$_SESSION['OtherInfo']['OrgID']." and branchId = (SELECT BranchID from beatle_userlogin where userId = ".$_SESSION['UserInfo']['UserID'].")";
}else if(strtolower($_SESSION['UserInfo']['UType']) == 'line_manager'){
$SQL = "SELECT db_branchName,branchId FROM beatle_branch WHERE db_branchOrg = ".$_SESSION['OtherInfo']['OrgID']." and branchId = (SELECT BranchID from beatle_userlogin where userId = ".$_SESSION['UserInfo']['UserID'].")";
}
//$_SESSION['UserInfo']['UserID']
//$SQL = "SELECT db_branchName,branchId FROM beatle_branch WHERE db_branchOrg = ".$_SESSION['OtherInfo']['OrgID'];
$rsBranches = $objDB->sql_query($SQL);
return $rsBranches;
}
public function fetchIndustrys($OrgID,$BranchID,$IndustryID){
global $objDB;
$SQL = '';
if(strtolower($_SESSION['UserInfo']['UType']) == 'owner') {
if(!empty($OrgID)) {
$SQL = "SELECT * FROM beatle_industry WHERE db_bracnchid IN (SELECT branchId FROM beatle_branch WHERE db_branchOrg = ".$OrgID.")";
$rsBranches = $objDB->sql_query($SQL);
}else {
$SQL = "SELECT * FROM beatle_industry WHERE db_bracnchid IN (SELECT branchId FROM beatle_branch WHERE db_branchOrg = ".$_SESSION['OtherInfo']['OrgID'].")";
$rsBranches = $objDB->sql_query($SQL);
}
}else if(strtolower($_SESSION['UserInfo']['UType']) == 'manager'){
if(!empty($OrgID)) {
$SQL = "SELECT * FROM beatle_industry WHERE db_bracnchid = (SELECT branchId FROM beatle_branch WHERE db_branchOrg = ".$OrgID." and db_branchManager = ".$_SESSION['UserInfo']['UserID']." )";
$rsBranches = $objDB->sql_query($SQL);
}else {
$SQL = "SELECT * FROM beatle_industry WHERE db_bracnchid = (SELECT branchId FROM beatle_branch WHERE db_branchOrg = ".$_SESSION['OtherInfo']['OrgID']." and db_branchManager = ".$_SESSION['UserInfo']['UserID'].")";
$rsBranches = $objDB->sql_query($SQL);
}
}else if(strtolower($_SESSION['UserInfo']['UType']) == 'line_manager'){
$SQL = "SELECT * FROM beatle_industry WHERE db_userid = ".$_SESSION['UserInfo']['UserID']." ";
$rsBranches = $objDB->sql_query($SQL);
}
return $rsBranches;
}
public function FetchOrganizationName($id){
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 = ".$id." ";
$rsUserInfo = $objDB->sql_query($SQL);
return $rsUserInfo;
}
public function fetchBranchesData($BranchID,$userId){
global $objDB;
$SQL_B = "";
if(!empty($BranchID)){
$SQL_B = " branchId = ".$BranchID." AND ";
}
$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 = ".$userId." )";
$rsBranches = $objDB->sql_query($SQL);
return $rsBranches;
}
public function fetchIndustryData($branchID){
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;
$rsIndustry = $objDB->sql_query($SQL);
return $rsIndustry;
}
public function FetchBranchName($id){
global $objDB;
$SQL = "SELECT db_branchName FROM beatle_branch WHERE branchId = ".$id;
$rsOwnerInfo = $objDB->sql_query($SQL);
return $rsOwnerInfo[0]['db_branchName'];
}
public function FetchIndustryName($id){
global $objDB;
$SQL = "SELECT db_industry FROM beatle_industry WHERE IndId = ".$id;
$rsOwnerInfo = $objDB->sql_query($SQL);
return $rsOwnerInfo[0]['db_industry'];
}
public function SelectPageContentName($id){
global $objDB;
$SQL = "SELECT db_pageConTitle FROM beatle_pagecontent WHERE pageConId = ".$id;
$rsOwnerInfo = $objDB->sql_query($SQL);
return $rsOwnerInfo[0]['db_pageConTitle'];
}
public function SelectPageNameFilter($rsPagesIDS,$filterTitle){
global $objDB;
if(count($rsPagesIDS) > 0 && $rsPagesIDS[0] != "") {
$SQL = "SELECT beatle_pagecontent.db_pageConTitle, beatle_pagecontent.db_pageChoice, beatle_pagecontent.pageConId,beatle_page.pageId FROM beatle_page INNER JOIN beatle_pagecontent ON beatle_page.pageId = beatle_pagecontent.db_pageid WHERE beatle_page.pageId IN (".implode(",",$rsPagesIDS).") AND (beatle_pagecontent.db_pageselection = '' || beatle_pagecontent.db_pageselection = 'radio' )";
$rsPageContent = $objDB->sql_query($SQL);
$html .= '' ;
$html .= '<input type="hidden" name="pageid" value="'.$rsPageContent[0]['pageId'].'" >' ;
$pgConIDs = '';
for($i=0; $i<count($rsPageContent); $i++) {
$choiceArr = explode(',',$rsPageContent[$i]['db_pageChoice']);
$html .= '<input type="hidden" name="pg_ids_[]" value="'.$rsPageContent[$i]['pageConId'].'" >' ;
$html .= '<div class="col-md-2 col-xs-12 form-group">';
$html .= '<select name="name_'.$rsPageContent[$i]['pageConId'].'" class="form-control input-md">';
$html .= '<option value="0">'.$rsPageContent[$i]['db_pageConTitle'].'</option>';
foreach($choiceArr as $key => $val){
$optVal = '';
if($_POST['name_'.$rsPageContent[$i]['pageConId']] == $val){
$optVal = 'selected="selected"';
}
$html .= '<option '.$optVal.' value="'.$val.'">'.$val.'</option>';
}
$html .= '</select>';
$html .= '</div>';
}
echo $html;
}else{
return array();
}
}
//horizontal average survey page wise
public function FetchContentAvg($QuestionId,$StationId,$AuditorId,$PageParamID,$coachNo="",$tokenid){
global $objDB;
//$SQL = "SELECT ROUND(AVG(db_surveyValue),2) AS AvgValue FROM beatle_survey WHERE db_surveyPageid IN(".implode(',',$pageIDArr).") 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_surveyContentId = ".$pageId." AND db_surveyValue IN (1,2,3,4,5) AND is_submit = 'Y' 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 = "SELECT db_surveyValue AS AvgValue FROM baris_survey WHERE db_surveyValue IN ('0','1') AND is_submit = 'Y' ";
if(!empty($QuestionId))
$SQL .= " AND db_surveyQuestionId = ".$QuestionId." " ;
if(!empty($StationId))
$SQL .= " AND db_surveyStationId = ".$StationId." " ;
if(!empty($AuditorId))
$SQL .= " AND db_surveyUserid = ".$AuditorId." " ;
if(!empty($PageParamID))
$SQL .= " AND db_surveyParamId = ".$PageParamID. " ";
if(!empty($coachNo))
$SQL .= " AND db_coachNo = '".$coachNo."' ";
if(!empty($tokenid))
$SQL .= " AND tokenid = '".$tokenid."' ";
$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' ";
$rsCountSurvey = $objDB->sql_query($SQL);
// if(empty($rsCountSurvey[0]['AvgValue']))
// $rsCountSurvey[0]['AvgValue'] = "0";
return $rsCountSurvey;
}
public function FetchContentAvgName($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;
}
public function FetchOverAllAvg($CustomerID,$OrgID,$BranchID,$IndustryID,$pType,$PageContentID="",$tokenID=""){
global $objDB;
$pageIDArr = IndustryPageIDS($pType,$BranchID,$IndustryID);
//echo "<pre>";
//print_r($pageIDArr);
//echo "</pre>";
$SQL = "SELECT ROUND(AVG(db_surveyValue),2) AS AvgValue FROM beatle_survey WHERE db_surveyPageid IN(".implode(',',$pageIDArr).") AND db_surveyValue IN (1,2,3,4,5) AND is_submit = 'Y' ";
/*
if(!empty($IndustryID))
$SQL .= " AND db_surveyIndId = ".$IndustryID." ";
if(!empty($BranchID))
$SQL .= " AND db_surveyBranchid = ".$BranchID;
//if($type == 'us' && !empty($OrgID))
//$SQL .= " AND orgid = ".$OrgID. " ";
//else if($type == 'ind')
//$SQL .= " AND orgid != ".$OrgID;
if(empty($OrgID))
$SQL .= " AND orgid = ".$_SESSION['OtherInfo']['OrgID']. " ";
else
$SQL .= " AND orgid = ".$OrgID. " ";
if(!empty($PageContentID))
$SQL .= " AND db_surveyContentId = ".$PageContentID. " ";
if(!empty($CustomerID))
$SQL .= " AND db_surveyUserid = ".$CustomerID. " ";
*/
if(!empty($PageContentID))
$SQL .= " AND db_surveyContentId = ".$PageContentID. " ";
if(!empty($tokenID))
$SQL .= " AND tokenid = '".$tokenID. "' ";
$SQL .= " AND created_date BETWEEN '".date("Y-m-d",strtotime($this->FromDate))." 00:00:00' AND '".date("Y-m-d",strtotime($this->ToDate))." 23:59:59' ";
//echo $SQL;
//die;
$rsCountSurvey = $objDB->sql_query($SQL);
if(empty($rsCountSurvey[0]['AvgValue']))
$rsCountSurvey[0]['AvgValue'] = "0.00";
return $rsCountSurvey[0]['AvgValue'];
}
public function FetchContentName($avgVal,$PageContentID){
global $objDB;
$SQL ="SELECT db_pageChoice FROM beatle_pagecontent where pageConId = ".$PageContentID."";
$result = $objDB->sql_query($SQL);
$mark=explode(',', $result[0]['db_pageChoice']);
$test = $mark[5-$avgVal];
return $test;
}
public function fetchCustomersQA($CustomerID="",$sort,$PageIDS){
global $objDB;
//print_r($_SESSION);
//$SQL = "SELECT pageConId FROM beatle_pagecontent WHERE LOWER(db_pageConTitle) = '".strtolower(QUESTION_DEFAULT_SENTENCE)."'";
$SQL = "SELECT pageConId,db_pageConTitle FROM beatle_pagecontent WHERE LOWER(db_pageselection) = 'multiline'";
$AboutUSayIArr = $objDB->sql_query($SQL);
$AboutUSayI_ID = $AboutUSayIArr[0]['pageConId'];
//print_r($PageIDS);
$SQL = "SELECT beatle_organization.OrgId FROM beatle_organization RIGHT JOIN beatle_userlogin ON beatle_organization.OrgId = beatle_userlogin.OrgID WHERE beatle_userlogin.userId = ".$_SESSION['UserInfo']['UserID']." ";
$rsOrgID = $objDB->sql_query($SQL);
$SQL = "SELECT * FROM beatle_survey WHERE is_submit = 'Y' AND db_surveyPageid IN (".$PageIDS.") ";
if(!empty($this->TokenID))
$SQL .= " AND beatle_survey.tokenid = '".$this->TokenID."' " ;
if(!empty($this->FromDate) && !empty($this->ToDate)){
$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' ";
}
if(!empty($AboutUSayI_ID))
$SQL .= " AND db_surveyContentId != ".$AboutUSayI_ID;
//echo "<br /><br />";
//echo $SQL;
$rsData = $objDB->sql_query($SQL);
return $rsData;
}
public function fetchIndustryPagesData(){
global $objDB;
$SQL_B = "";
if(strtolower($_SESSION['UserInfo']['UType']) == 'owner') {
$SQL = "SELECT * FROM beatle_branch WHERE 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 db_branchManager = ".$_SESSION['UserInfo']['UserID']." ";
}else if(strtolower($_SESSION['UserInfo']['UType']) == 'line_manager'){
$SQL = "SELECT * FROM beatle_branch WHERE branchId = (SELECT BranchID FROM beatle_userlogin WHERE userId = ".$_SESSION['UserInfo']['UserID'].") ";
}
$rsBranches = $objDB->sql_query($SQL);
return $rsBranches;
}
// 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;
}
}
?>