File: /home/barbeatleanalyti/public_html/barisnew.beatleanalytics.com/site/backup/include/low_rating.php
<?php
/* <summary>
Class contains methods related to state module
<author>
Beatle Buddy 2017.
</author>
Version 1.0
</summary>
*/
class include_low_rating
{
/*
<summary>
Default constructor
</summary>
*/
function __CONSTRUCT(){
}
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 tokenid, ROUND(AVG(db_surveyValue),2) AS AvgValue from beatle_survey WHERE db_surveyValue IN (1,2,3,4) AND is_submit = 'Y' ";
$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($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";
$rsAvg = $objDB->sql_query($SQL);
//echo "<pre>";
//print_r(count($rsAvg));
//echo $rsAvg[10]['tokenid'];
$SQL = "";
$rsUserInfo_ = "";
$cTokenid = array();
foreach($rsAvg as $key => $val){
//echo $val['tokenid'];
if($val['AvgValue'] <= 2){
/*echo $val['tokenid'];
echo "</br>";*/
//echo $this->FetchLowAvgCustomerData($val['tokenid']);
$cTokenid[] = $val['tokenid'];
}
}
$userStr = implode("', '", $cTokenid);
$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,
beatle_organization.OrgId,
beatle_organization.db_Orgname,
beatle_branch.branchId,
beatle_branch.db_branchName,
beatle_branch.db_branchManager,
beatle_industry.IndId,
beatle_industry.db_ind_type_id,
beatle_industry.db_userid,
beatle_industry_type.indtypeid,
beatle_industry_type.name AS ind_type_name
FROM
beatle_survey
INNER JOIN beatle_userlogin ON beatle_survey.db_surveyUserid = beatle_userlogin.userId
INNER JOIN beatle_industry ON beatle_survey.db_surveyIndId = beatle_industry.IndId
INNER JOIN beatle_branch ON beatle_survey.db_surveyBranchid = beatle_branch.branchId
INNER JOIN beatle_organization ON beatle_survey.orgid = beatle_organization.OrgId
INNER JOIN beatle_industry_type ON beatle_industry.db_ind_type_id = beatle_industry_type.indtypeid
WHERE beatle_survey.is_submit = 'Y'
AND LOWER(beatle_userlogin.db_usertype) = 'end_user' ";
$SQL .= "
AND beatle_survey.tokenid in ('$userStr') " ;
$SQL .= " GROUP BY beatle_survey.tokenid ORDER BY beatle_survey.created_date DESC";
//print_r($objDB->sql_query($SQL));die;
return $rsUserInfo_ = $objDB->sql_query($SQL);
}
public function FetchUserName($userid){
global $objDB;
$SQL = "SELECT db_username
FROM
beatle_userlogin
WHERE userId = ".$userid;
$rsUserInfo = $objDB->sql_query($SQL);
return $rsUserInfo[0]['db_username'];
}
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) 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) 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,"low_rating.html?v=list","Email sent successfuly to the customer!",0);
exit;
}else{
$insertArr['status'] = 'no';
addEmailSendInformation($insertArr);
ReturnPostFormData($returnArr,"low_rating.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,"low_rating.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'];
}
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) 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[3];
}else if($avgValue > 1 and $avgValue <= 2){
$test = $mark[2];
}else if($avgValue > 2 and $avgValue <= 3){
$test = $mark[1];
}else if($avgValue > 3 and $avgValue <= 4){
$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) 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[4-$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) 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." ";
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'];
}
}
?>