File: /home/barbeatleanalyti/public_html/api.beatleanalytics.com/live/feedback_auto.php
<?php
/*
* Following code will get single department details
* create ticket
*/
// array for JSON response
$response = array();
// include db connect class
require_once 'db_connect.php';
date_default_timezone_set('Asia/Kolkata');
// connecting to db
$db = new DB_CONNECT();
$test = '';
if(isset($_GET["userid"],$_GET["branchId"],$_GET["indId"],$_GET["orgID"],$_GET["keyword"],$_GET["noOfPax"],$_GET["totalBill"],$_GET["servername"],$_GET["exType"],$_GET['values'])){
$userid = $_GET['userid'];
$orgID = $_GET['orgID'];
$branchId = $_GET['branchId'];
$indId = $_GET['indId'];
$keyword = $_GET["keyword"];
$noOfPax = '';
$totalBill = '';
$tokenid = random_string(32);
$is_submit = 'Y';
$date = date('Y-m-d H:i:s');
if($_GET["noOfPax"] == ""){
$noOfPax = "0";
}else{
$noOfPax = $_GET["noOfPax"];
}
if($_GET["totalBill"] == ""){
$totalBill = "0";
}else{
$totalBill = $_GET["totalBill"];
}
if($_GET["servername"] == ""){
$servername = "NA";
}else{
$servername = $_GET["servername"];
}
$exType = $_GET["exType"];
//array of content values
$rate1 = "";
$avgsum = 0;
$pageConIdCount = 0;
$pageConValue = "";
$pageArr = IndustryPageIDS('content',$orgID,$branchId,$indId);
$qPageConId = getQuestionPageConId(IndustryPageIDS('question',$orgID,$branchId,$indId));
mysql_query("SET NAMES 'utf8' COLLATE 'utf8_general_ci'");
for ($r=0; $r <= count($_GET['values'])-1; $r++){
//$rate1 .= $_GET['values'][$r]."\n";
$myArray = explode('@!#!@', $_GET['values'][$r]);
/*foreach ($myArray as $value) {
$rate1 .= $value." ";
}
$rate1 .= "\n";*/
$value = mysql_real_escape_string($myArray[2]);
$result = mysql_query("INSERT INTO beatle_survey(db_surveyBranchid,db_surveyUserid,db_surveyIndId,db_surveyPageid,db_surveyContentId,db_surveyValue, created_date, updated_date, tokenid,orgid,is_submit,keyword,paxNo,totalBill,servername) VALUES
('$branchId','$userid', '$indId', '$myArray[0]', '$myArray[1]', '$value', '$date', '0000-00-00 00:00:00', '$tokenid','$orgID','$is_submit', '$keyword', '$noOfPax', '$totalBill', '$servername')")or die(mysql_error());
if(in_array($myArray[0], $pageArr)){
$avgsum = $avgsum + $myArray[2];
$pageConIdCount++;
}
if($qPageConId == $myArray[1]){
$pageConValue = mysql_real_escape_string($myArray[2]);
}
}
$avgrat = round($avgsum / $pageConIdCount,2);
if($avgrat >= 3 && $pageConValue != "No Comments"){
$reviewidOrg = fetchReviewIdOfOrg($orgID);
$reviewidInd = fetchReviewIdOfInd($orgID,$branchId,$indId);
if(!empty($reviewidOrg)){
$username = fetchUsernamebyUserid($userid);
$result = mysql_query("INSERT INTO beatle_review_feedback (reviewId,userId,username,avgRating,feedbackDetail,tokenid,feedbackDate,created_date) VALUES(".$reviewidOrg.",".$userid.",'".$username."',".$avgrat.",'".$pageConValue."','".$tokenid."','".date('Y-m-d H:i:s')."','".date('Y-m-d H:i:s')."')")or die(mysql_error());
}
if(!empty($reviewidInd)){
$result = mysql_query("INSERT INTO beatle_review_feedback (reviewId,userId,username,avgRating,feedbackDetail,tokenid,feedbackDate,created_date) VALUES(".$reviewidInd.",".$userid.",'".$username."',".$avgrat.",'".$pageConValue."','".$tokenid."','".date('Y-m-d H:i:s')."','".date('Y-m-d H:i:s')."')")or die(mysql_error());
}
}
$totalAvg = 0;
$noZeroPageCount = 0;
foreach($pageArr as $val) {
$avgSum = 0;
$noZeroPageConCount = 0;
$rsPagesContent = getPageContentByPageid($val);
while ($pcVal = mysql_fetch_array($rsPagesContent)){
$avgVal = FetchOverAllAvg($pageArr,$userid,$orgID,$branchId,$indId,$pcVal['pageConId'],$tokenid);
if($avgVal != 0){
$noZeroPageConCount++;
}
$avgSum = $avgSum + $avgVal;
}
if($avgSum != 0){
$noZeroPageCount++;
}
$totalAvg += round($avgSum/$noZeroPageConCount,2);
}
$totalAvg = round($totalAvg/$noZeroPageCount,2);
if($totalAvg <= 2){
$title = 'lowrating feedback';
$result = mysql_query("INSERT INTO app_notification (title, description, orgid,branchid,indid,IsActive,type,created_date)VALUES('$title','$pageConValue','$orgID','$branchId','$indId','Y','lowfeedback','$date')");
}
$pointValue = '';
$userType = '';
$resultConfig = mysql_query("SELECT ConfigValue FROM appconfig WHERE ConfigName LIKE 'RECORDS_PER_PAGE'")or die(mysql_error());
if (!empty($resultConfig)) {
$row = mysql_fetch_row($resultConfig);
$test = $row[0];
$pointValue = $test;
}
$resultUserType = mysql_query("SELECT db_usertype FROM beatle_userlogin WHERE userId LIKE ".$userid." ")or die(mysql_error());
if (!empty($resultUserType)) {
$row = mysql_fetch_row($resultUserType);
$test = $row[0];
$userType = $test;
}
if($exType != 'Guest'){
$result = mysql_query("INSERT INTO beatle_points (UserID,OrgID,BranchID,IndustryID,UserType,tokenid,Amount,AddedDate,IsRedeem)VALUES('$userid','$orgID','$branchId','$indId','$userType','$tokenid','$pointValue','$date','N')");
// check if row inserted or not
if ($result) {
// successfully inserted into database
$response["success"] = 1;
$response["message"] = "feedback successfully submitted.";
// echoing JSON response
echo json_encode($response);
} else {
// failed to insert row
$response["success"] = 0;
$response["message"] = "feedback has not submitted.";
// echoing JSON response
echo json_encode($response);
}
}else{
$response["success"] = 1;
$response["message"] = "feedback successfully submitted.";
// echoing JSON response
echo json_encode($response);
}
/*$response["success"] = 1;
// $response["message"] = "feedback successfully submitted.";
$response["message"] = $rate1;
$response["userData"] = $userid."\n".$orgID."\n".$branchId."\n".$indId."\n".$keyword."\n".$noOfPax."\n".$totalBill."\n".$servername;*/
//$response["value"] = $all_arraylist;
// echo json_encode($response);
}else{
$response["success"] = 0;
$response["message"] = "Oops! An error occurred.";
echo json_encode($response);
}
/*echo "<pre>";
print_r(IndustryPageIDS('content',13,18));
print_r(IndustryPageIDS('question',13,18));
echo getQuestionPageConId(IndustryPageIDS('question',13,18));
*/
function FetchOverAllAvg($pageIDArr,$CustomerID,$OrgID,$BranchID,$IndustryID,$PageContentID="",$tokenID=""){
$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. "' ";
$rsCountSurvey = mysql_fetch_array(mysql_query($SQL));
if(empty($rsCountSurvey['AvgValue']))
$rsCountSurvey['AvgValue'] = "0.00";
return $rsCountSurvey['AvgValue'];
}
function IndustryPageIDS($PageType="",$orgID="",$BranchID="",$IndustryID=""){
$SQL = "SELECT GROUP_CONCAT(beatle_industry.db_pagesId) AS PageID FROM beatle_branch INNER JOIN beatle_industry ON
beatle_branch.branchId = beatle_industry.db_bracnchid WHERE
beatle_branch.db_branchOrg = ".$orgID;
if(!empty($BranchID))
$SQL .= " AND beatle_branch.branchId = ".$BranchID;
if(!empty($IndustryID))
$SQL .= " AND beatle_industry.IndId = ".$IndustryID;
$rsPages = mysql_fetch_row(mysql_query($SQL));
//echo $rsPages[0];
if($PageType != "" && !empty($rsPages[0])) {
$SQL = "SELECT GROUP_CONCAT(pageId) AS PageID FROM beatle_page WHERE pageId IN (".$rsPages[0].") AND LOWER(db_pagetype) = '".strtolower($PageType)."'";
$rsPages = mysql_fetch_row(mysql_query($SQL));
if(!empty($rsPages[0])) {
$rsArr = explode(",",$rsPages[0]);
$returnArr = array();
foreach($rsArr as $key => $val){
if(!in_array($val,$returnArr))
$returnArr[] = $val;
}
return $returnArr;
}else { return array(); }
}else{ return array(); }
}
function getQuestionPageConId($pageId){
$SQL = "SELECT pageConId FROM beatle_pagecontent WHERE db_pageid IN (".implode(',',$pageId).") AND LOWER(db_pageselection) = '".strtolower("multiline")."'";
$rsPages = mysql_fetch_row(mysql_query($SQL));
return $rsPages[0];
}
function getPageContentByPageid($PageID){
$SQL ="SELECT pageConId FROM beatle_pagecontent WHERE db_pageid = ".$PageID."";
$rsCountSurvey = mysql_query($SQL);
return $rsCountSurvey;
}
function fetchReviewIdOfOrg($orgID){
$SQL = "SELECT reviewId FROM beatle_review WHERE orgId = ".$orgID." AND type = 'org' ";
$rsReviewid = mysql_fetch_row(mysql_query($SQL));
return $rsReviewid[0];
}
function fetchReviewIdOfInd($orgID,$branchId,$indId){
$SQL = "SELECT reviewId FROM beatle_review WHERE orgId = ".$orgID." AND type = 'ind' AND branchId = ".$branchId." AND indId = ".$indId."";
$rsReviewid = mysql_fetch_row(mysql_query($SQL));
return $rsReviewid[0];
}
function fetchUsernamebyUserid($userid){
$SQL = "SELECT db_username FROM beatle_userlogin WHERE userId = ".$userid;
$rsUname = mysql_fetch_row(mysql_query($SQL));
return $rsUname[0];
}
//tokenid genarator
function random_string($length) {
$key = '';
$keys = array_merge(range(0, 9), range('a', 'z'));
for ($i = 0; $i < $length; $i++) {
$key .= $keys[array_rand($keys)];
}
return $key;
}
?>