File: /home/barbeatleanalyti/public_html/manage.beatleanalytics.com/site/include/shift.php
<?php
/* <summary>
Class contains methods related to state module
<author>
Beatle Buddy 2018.
</author>
Version 1.0
</summary>
*/
class include_shift
{
/*
<summary>
Default constructor
</summary>
*/
function __CONSTRUCT(){
}
protected function EditReligionData(){}
protected function InsertUserInfo(){} ///// InsertUserInfo completed /////
public function fetchShiftDataById(){
global $objDB;
$SQL = "SELECT * FROM beatle_shift WHERE ";
if(empty($this->IndustryID)){
$SQL .= "BranchID = ".$this->BranchID;
}else{
$SQL .= "IndustryID = ".$this->IndustryID;
}
$rsShift = $objDB->sql_query($SQL);
return $rsShift;
/* echo "<pre>";
print_r($rsShift);die();*/
}
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_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 sendemailtocustomer(){
global $objDB;
//echo "<pre>";
// print_r($_REQUEST);
//print_r($_SESSION['OtherInfo']['OrgID']);
//echo '<div style="text-align:center;"><h1> WAITING FOR EMAIL FORMAT APPROVAL </h1></div>';
$orgid = $_REQUEST['orgid_'];
$rsOrg = $this->fetchOrganization($orgid);
$orgDetails = 'Org. Name : <strong>'.$rsOrg[0]['db_Orgname'].'</strong><br />';
$SQL = "SELECT
beatle_organization.OrgId,
beatle_organization.db_Orgname,
beatle_userlogin.webprofileimage
FROM
beatle_userlogin
INNER JOIN beatle_organization ON beatle_organization.db_ownerId = beatle_userlogin.userId AND beatle_organization.OrgId = ".$_SESSION['OtherInfo']['OrgID'];
$rsImage = $objDB->sql_query($SQL);
$SQL = "SELECT * FROM beatle_userlogin WHERE userId = ".$_REQUEST['customerid'];
$rsCustomerData = $objDB->sql_query($SQL);
$customerDetails = ' Name : <strong>'.$rsCustomerData[0]['db_username'].'</strong><br />
User ID : <strong>'.$rsCustomerData[0]['db_userLoginName'].'</strong><br />
Email : <strong>'.$rsCustomerData[0]['db_email'].'</strong><br />
Phone : <strong>'.$rsCustomerData[0]['db_phone'].'</strong><br />';
//echo '<pre>';
//print_r($rsCustomerData);
//echo '</pre>';
$sourceProfilePath = 'http://'.$_SERVER['HTTP_HOST'].'/newdemo.beatleanalytics.com/assets/sourceProfilePath/'.$rsImage[0]['webprofileimage'];
if($_SERVER['HTTP_HOST'] != '192.168.2.103' && $_SERVER['HTTP_HOST'] != '192.168.2.105' && $_SERVER['HTTP_HOST'] != 'localhost' && $_SERVER['HTTP_HOST'] != 'kalpeshpc'){
if( preg_match('/manage.beatle/', $_SERVER['HTTP_HOST'])){
$sourceProfilePath = 'http://admin.beatleanalytics.com/assets/OrganizationPics/'.$rsImage[0]['webprofileimage'];
}else if(preg_match('/manage.beatle/', $_SERVER['HTTP_HOST'])){
$sourceProfilePath = 'http://admin.beatleanalytics.com/assets/OrganizationPics/'.$rsImage[0]['webprofileimage'];
}else if(preg_match('/owner.beatle/', $_SERVER['HTTP_HOST'])){
$sourceProfilePath = 'http://newdemo.beatleanalytics.com/assets/OrganizationPics/'.$rsImage[0]['webprofileimage'];
}
}
if(!fopen($sourceProfilePath,'r')){
$sourceProfilePath = 'http://newdemo.beatleanalytics.com/assets/OrganizationPics/no-image-found.png';
}
$eMailBody = file_get_contents("email-template/service-request.html");
$msgBody = $_REQUEST['message'];
$msgBody = str_replace("{{CUSTOMER_USER_ID}}",$rsCustomerData[0]['db_userLoginName'],$msgBody);
$msgBody = str_replace("{{CUSTOMER_EMAIL_ID}}",$rsCustomerData[0]['db_email'],$msgBody);
$msgBody = str_replace("{{CUSTOMER_NAME}}",$rsCustomerData[0]['db_username'],$msgBody);
$msgBody = str_replace("{{CUSTOMER_PHONE}}",$rsCustomerData[0]['db_phone'],$msgBody);
$EmailBodyContent = str_replace("{{DATE}}",date("d/m/Y"),$eMailBody);
$EmailBodyContent = str_replace("{{CUSTOMER_DETAILS}}",$customerDetails,$EmailBodyContent);
$EmailBodyContent = str_replace("{{ORG_DETAILS}}",$orgDetails,$EmailBodyContent);
$EmailBodyContent = str_replace("{{MESSAGE_BODY}}",$msgBody,$EmailBodyContent);
$EmailBodyContent = str_replace("{{IMAGE_PATH}}",$sourceProfilePath,$EmailBodyContent);
$EmailBodyContent = str_replace("{{ORG_NAME}}",$rsOrg[0]['db_Orgname'],$EmailBodyContent);
//echo $EmailBodyContent;
// Set email content variables value.
$objDB->SetFromName = $rsOrg[0]['db_Orgname'];
$objDB->ToEmail = $rsCustomerData[0]['db_email'];
$objDB->Name = $rsCustomerData[0]['db_username'];
$objDB->EmailSubject = $_REQUEST['subject'];
$objDB->EmailBody = $EmailBodyContent;
$insertArr = array();
$insertArr['userid'] = $_REQUEST['customerid'];
$insertArr['orgid'] = $orgid;
$insertArr['indid'] = $_REQUEST['indid_'];
$insertArr['branchid'] = $_REQUEST['branchic_'];
$insertArr['emailid'] = $rsCustomerData[0]['db_email'];
$insertArr['customername'] = $rsCustomerData[0]['db_username'];
$insertArr['send_by_id'] = $_SESSION['UserInfo']['UserID'];
$insertArr['emailbody'] = $EmailBodyContent;
$insertArr['created_date'] = date("Y-m-d H:i:s");
$insertArr['sent_date'] = date("Y-m-d H:i:s");
$insertArr['type'] = 'email';
$insertArr['response'] = $objDB->mailResponse;
$insertArr['email_subject'] = $_REQUEST['subject'];
$returnArr = array();
$returnArr['Organization'] = $orgid;
$returnArr['Branches'] = $_REQUEST['branchic_'];
$returnArr['Industrys'] = $_REQUEST['indid_'];
$returnArr['acn'] = 'FetchCustomerData';
//$returnArr['acn'] = 'FetchTotalVisits';
// Send email
if ($objDB->SendEMail()){
$insertArr['status'] = 'yes';
deductPointFromDB();
addEmailSendInformation($insertArr);
ReturnPostFormData($returnArr,"shift.html?v=list","Email sent successfuly to the customer!",0);
exit;
}else{
$insertArr['status'] = 'no';
addEmailSendInformation($insertArr);
ReturnPostFormData($returnArr,"shift.html?v=list","There is something wrong to send email, please try again!",1);
exit;
//echo 'CUSTOMER MAIL = N<br />';
}
}
public function sendemultiplemailtocustomer(){
global $objDB;
$rsUserID = $_REQUEST['sendemail'];
$orgid = $_REQUEST['orgid_'];
$branchid = $_REQUEST['branchic_'];
$indid = $_REQUEST['indid_'];
$subject = $_REQUEST['subject'];
$message = $_REQUEST['message'];
$idArr = $rsUserID;
$rsUserID = array();
$newArr = array();
$cnt = 0;
foreach($idArr as $key => $val){
if(!in_array($val,$newArr)){
$newArr[] = $val;
$cnt++;
}
}
$rsUserID = $newArr;
for($i=0; $i<count($rsUserID); $i++) {
$userid = $rsUserID[$i];
$SQL = "SELECT * FROM beatle_userlogin WHERE userId = ".$userid;
$rsCustomerData = $objDB->sql_query($SQL);
$SQL = "SELECT
beatle_organization.OrgId,
beatle_organization.db_Orgname,
beatle_userlogin.webprofileimage
FROM
beatle_userlogin
INNER JOIN beatle_organization ON beatle_organization.db_ownerId = beatle_userlogin.userId AND beatle_organization.OrgId = ".$_SESSION['OtherInfo']['OrgID'];
$rsImage = $objDB->sql_query($SQL);
$sourceProfilePath = 'http://'.$_SERVER['HTTP_HOST'].'/newdemo.beatleanalytics.com/assets/sourceProfilePath/'.$rsImage[0]['webprofileimage'];
if($_SERVER['HTTP_HOST'] != '192.168.2.103' && $_SERVER['HTTP_HOST'] != '192.168.2.105' && $_SERVER['HTTP_HOST'] != 'localhost' && $_SERVER['HTTP_HOST'] != 'kalpeshpc'){
if( preg_match('/manage.beatle/', $_SERVER['HTTP_HOST'])){
$sourceProfilePath = 'http://admin.beatleanalytics.com/assets/OrganizationPics/'.$rsImage[0]['webprofileimage'];
}else if(preg_match('/manage.beatle/', $_SERVER['HTTP_HOST'])){
$sourceProfilePath = 'http://admin.beatleanalytics.com/assets/OrganizationPics/'.$rsImage[0]['webprofileimage'];
}else if(preg_match('/owner.beatle/', $_SERVER['HTTP_HOST'])){
$sourceProfilePath = 'http://newdemo.beatleanalytics.com/assets/OrganizationPics/'.$rsImage[0]['webprofileimage'];
}
}
if(!fopen($sourceProfilePath,'r')){
$sourceProfilePath = 'http://newdemo.beatleanalytics.com/assets/OrganizationPics/no-image-found.png';
}
$customerDetails = ' Name : <strong>'.$rsCustomerData[0]['db_username'].'</strong><br />
User ID : <strong>'.$rsCustomerData[0]['db_userLoginName'].'</strong><br />
Email : <strong>'.$rsCustomerData[0]['db_email'].'</strong><br />
Phone : <strong>'.$rsCustomerData[0]['db_phone'].'</strong><br />';
$rsOrg = $this->fetchOrganization($orgid);
$orgDetails = '<strong>FROM : '.$rsOrg[0]['db_Orgname'].' - '.$this->FetchIndustryName($indid).' - '.$this->FetchBranchName($branchid).'</strong><br />';
$eMailBody = file_get_contents("email-template/service-request.html");
$msgBody = $_REQUEST['message'];
$msgBody = str_replace("{{CUSTOMER_USER_ID}}",$rsCustomerData[0]['db_userLoginName'],$msgBody);
$msgBody = str_replace("{{CUSTOMER_EMAIL_ID}}",$rsCustomerData[0]['db_email'],$msgBody);
$msgBody = str_replace("{{CUSTOMER_NAME}}",$rsCustomerData[0]['db_username'],$msgBody);
$msgBody = str_replace("{{CUSTOMER_PHONE}}",$rsCustomerData[0]['db_phone'],$msgBody);
$EmailBodyContent = str_replace("{{DATE}}",date("d/m/Y"),$eMailBody);
$EmailBodyContent = str_replace("{{CUSTOMER_DETAILS}}",$customerDetails,$EmailBodyContent);
$EmailBodyContent = str_replace("{{ORG_DETAILS}}",$orgDetails,$EmailBodyContent);
$EmailBodyContent = str_replace("{{MESSAGE_BODY}}",$msgBody,$EmailBodyContent);
$EmailBodyContent = str_replace("{{IMAGE_PATH}}",$sourceProfilePath,$EmailBodyContent);
$EmailBodyContent = str_replace("{{ORG_NAME}}",$rsOrg[0]['db_Orgname'],$EmailBodyContent);
$SQL = "INSERT INTO beatle_crone SET
userid = '".$userid."',
orgid = '".$orgid."',
indid = '".$indid."',
branchid = '".$branchid."',
emailid = '".$rsCustomerData[0]['db_email']."',
customername = '".$rsCustomerData[0]['db_username']."',
send_by_id = '".$_SESSION['UserInfo']['UserID']."',
emailbody = '".AddFieldAddslashes($EmailBodyContent)."',
status = 'queue',
created_date = '".date("Y/m/d H:i:s")."',
type = 'email',
email_subject = '".AddFieldAddslashes($subject)."' ";
//echo $SQL;
$objDB->sql_query($SQL);
}
$returnArr = array();
$returnArr['Organization'] = $orgid;
$returnArr['Branches'] = $branchid;
$returnArr['Industrys'] = $indid;
$returnArr['acn'] = 'FetchCustomerData';
//$returnArr['acn'] ='FetchTotalVisits';
// Send email
ReturnPostFormData($returnArr,"shift.html?v=list","Customer data successfuly added into queue to send email !",0);
exit;
}
/*
public function FetchOrganizationName($id){
}
public function FetchBranchName($id){}
public function FetchIndustryName($id){} */
public function FetchOrganizationName($id){
global $objDB;
$SQL = "SELECT db_Orgname FROM beatle_organization WHERE OrgId = ".$id;
$rsOwnerInfo = $objDB->sql_query($SQL);
return $rsOwnerInfo[0]['db_Orgname'];
}
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 SelectPageName($id){
global $objDB;
/*$SQL = "SELECT db_pagename FROM beatle_page WHERE pageId = ".$id;
$rsOwnerInfo = $objDB->sql_query($SQL);
return $rsOwnerInfo[0]['db_pagename'];*/
$SQL = "SELECT
beatle_page.db_pagename
FROM
beatle_page INNER JOIN
beatle_pagecontent ON beatle_page.pageId = beatle_pagecontent.db_pageid
WHERE
beatle_page.pageId = ".$id."
AND beatle_pagecontent.db_pageselection = 'multiline' ";
$rsOwnerInfo = $objDB->sql_query($SQL);
return $rsOwnerInfo[0]['db_pagename'];
}
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($OrgID,$BranchID,$IndustryID,$pageId){
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' ";
$rsCountSurvey = $objDB->sql_query($SQL);
if(empty($rsCountSurvey[0]['AvgValue']))
$rsCountSurvey[0]['AvgValue'] = "0.00";
return $rsCountSurvey[0]['AvgValue'];
}
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="",$FromDate,$shiftFrom,$shiftTo){
global $objDB;
$pageIDArr = IndustryPageIDS($pType,$BranchID,$IndustryID);
//echo "<pre>";
//print_r($pageIDArr);
//echo "</pre>";
//$FromDate = $this->FromDate;
//$ToDate = $this->ToDate;
$avgValue = 0;
//while(strtotime($FromDate) <= strtotime($ToDate)){
//echo $FromDate.'<br>';
$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($PageContentID))
$SQL .= " AND db_surveyContentId = ".$PageContentID. " ";
if(!empty($tokenID))
$SQL .= " AND tokenid = '".$tokenID. "' ";
$SQL .= " AND created_date BETWEEN '".date("Y-m-d",strtotime($FromDate))." ".$shiftFrom."' AND '".date("Y-m-d",strtotime($FromDate))." ".$shiftTo."' ";
$rsCountSurvey = $objDB->sql_query($SQL);
if(empty($rsCountSurvey[0]['AvgValue']))
$rsCountSurvey[0]['AvgValue'] = "0.00";
$avgValue = $avgValue + $rsCountSurvey[0]['AvgValue'];
//$FromDate = date("d-m-Y",strtotime($FromDate." +1 day"));
//}
return $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;
}
public function fetchContentAboutISay($CustomerID){
global $objDB;
//$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'];
$SQL = "SELECT * FROM beatle_survey WHERE is_submit = 'Y' AND tokenid = (SELECT tokenid FROM beatle_survey WHERE db_surveyUserid = ".$CustomerID." AND orgid = ".$_SESSION['OtherInfo']['OrgID']." ORDER BY created_date DESC LIMIT 1)";
if(!empty($AboutUSayI_ID))
$SQL .= " AND db_surveyContentId = ".$AboutUSayI_ID;
//echo $SQL;
//die;
$rsData = $objDB->sql_query($SQL);
if(count($rsData) > 0){
return $rsData[0]['db_surveyValue'];
}else{
return "No any data found";
}
}
public function fetchContentDetails($CustomerID,$type){
global $objDB;
//$rsData = $this->FetchCustomerData();
$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.is_submit,
beatle_userlogin.db_phone,
beatle_userlogin.db_userLoginName,
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 beatle_survey.db_surveyUserid = ".$this->CustomerID." AND LOWER(beatle_userlogin.db_usertype) = 'end_user' ";
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."' " ;
//if(empty($IN))
//$SQL .= " GROUP BY beatle_survey.tokenid";
$SQL .= " ORDER BY beatle_survey.created_date DESC";
//echo $SQL;
$rsData = $objDB->sql_query($SQL);
if(count($rsData) <= 0)
return array();
if($type == 'question') {
//$rsBranches = $this->fetchIndustryPagesData($CustomerID,$type);
//$BranchIDArr = array();
//foreach($rsBranches as $key => $val){
// $BranchIDArr[] = $val['branchId'];
//}
}
$SQL = "SELECT db_pagesId AS PageIDs FROM beatle_industry WHERE IndId = ".$this->IndustryID;
$rsPageID = $objDB->sql_query($SQL);
//echo $rsPageID[0]['PageIDs'];
echo $SQL = "SELECT GROUP_CONCAT(pageId) AS PageIDS FROM beatle_page WHERE pageId IN (".$rsPageID[0]['PageIDs'].") AND LOWER(db_pagetype) = '".$type."'";
$rsPages = $objDB->sql_query($SQL);
//print_r($rsPages);
//die;
if($type == 'content') {
return $rsPages;
}
if(!empty($rsPages[0]['PageIDS']))
$rsQuestionData = $this->fetchCustomersQA($CustomerID,$type,$rsPages[0]['PageIDS']);
else
$rsQuestionData = array();
return $rsQuestionData;
}
/////////////////////////////////////
public function getAverage($type,$pageID,$OrgID,$BranchID,$IndID,$PageContentID){
global $objDB;
$SQL = "SELECT ROUND(AVG(db_surveyValue),2) AS AvgValue FROM beatle_survey WHERE db_surveyPageid = ".$pageID." AND db_surveyValue IN (1,2,3,4,5) AND is_submit = 'Y' ";
if(!empty($IndID))
$SQL .= " AND db_surveyIndId = ".$IndID." ";
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']. " ";
if(!empty($PageContentID))
$SQL .= " AND db_surveyContentId = ".$PageContentID. " ";
$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;
$rsCountSurvey = $objDB->sql_query($SQL);
if(empty($rsCountSurvey[0]['AvgValue']))
$rsCountSurvey[0]['AvgValue'] = "0.00";
return $rsCountSurvey[0]['AvgValue'];
}
public function fetchPageList($pageID){
global $objDB;
$SQL = "SELECT pageConId,db_pageConTitle FROM beatle_pagecontent WHERE db_pageid = ".$pageID." and db_pageselection = 'multiline'";
return $rsPageContent = $objDB->sql_query($SQL);
//echo "<pre>";
//print_r($rsPageContent);
//echo "</pre>";
}
public function fetchPageListAll($pageID){
global $objDB;
$SQL = "SELECT pageConId,db_pageConTitle FROM beatle_pagecontent WHERE db_pageid = ".$pageID."";
return $rsPageContent = $objDB->sql_query($SQL);
//echo "<pre>";
//print_r($rsPageContent);
//echo "</pre>";
}
public function industryKeywordName($IndustryID){
global $objDB;
$SQL = "SELECT keywordName,keywordServer,keywordPax FROM beatle_industry WHERE IndId = ".$IndustryID." ";
return $rsPageContent = $objDB->sql_query($SQL);
//echo "<pre>";
//print_r($rsPageContent);
//echo "</pre>";
}
public function FetchQuestionValue($CustomerID,$OrgID,$BranchID,$IndustryID,$pType,$PageContentID="",$tokenID=""){
global $objDB;
$SQL = "SELECT db_surveyValue AS AvgValue FROM beatle_survey WHERE is_submit = 'Y' ";
if(!empty($PageContentID))
$SQL .= " AND db_surveyContentId = ".$PageContentID. " ";
if(!empty($CustomerID))
$SQL .= " AND db_surveyUserid = ".$CustomerID. " ";
if(!empty($tokenID))
$SQL .= " AND tokenid = '".$tokenID. "' ";
$rsCountSurvey = $objDB->sql_query($SQL);
return $rsCountSurvey[0]['AvgValue'];
}
}
?>