File: /home/barbeatleanalyti/public_html/manage.beatleanalytics.com/site/include/tickets.php 18-3-2019
<?php
/* <summary>
Class contains methods related to state module
<author>
Beatle Buddy 2017.
</author>
Version 1.0
</summary>
*/
class include_tickets
{
/*
<summary>
Default constructor
</summary>
*/
function __CONSTRUCT(){
}
protected function updateTicket(){
global $objDB;
$SQL = "SELECT * FROM beatle_ticket WHERE ticketid = ".$_POST['tid'];
$rsTInfo = $objDB->sql_query($SQL);
if($rsTInfo[0]['parentid'] == 0){
$parentid = $rsTInfo[0]['ticketid'];
}else{
$parentid = $rsTInfo[0]['parentid'];
}
if($rsTInfo[0]['parentid'] != 0){
$_POST['tid'] = $rsTInfo[0]['parentid'];
}
/*
$SQL = "SELECT assignto,assignto_type FROM beatle_ticket WHERE parentid = ".$_POST['tid']." AND active = 'Y'";
$rsassign = $objDB->sql_query($SQL);
if(count($rsassign) == 1){
$assitnTo = $rsassign[0]['assignto'];
$assitnToType = $rsassign[0]['assignto_type'];
}else if(count($rsassign) == 0){
$SQL = "SELECT assignto,assignto_type FROM beatle_ticket WHERE ticketid = ".$_POST['tid']." AND parentid = 0 AND active = 'Y'";
$rsassign = $objDB->sql_query($SQL);
$assitnTo = $rsassign[0]['assignto'];
$assitnToType = $rsassign[0]['assignto_type'];
}*/
//echo "<pre>";
//print_r($rsTInfo);
//die;
//print_r($_REQUEST);
//print_r($_SESSION);
if(strtolower($_POST['status']) == 'forward') {
$SQL = "UPDATE beatle_ticket SET
status = '".$_POST['status']."',
forward = 'Y',
active = 'N'
WHERE ticketid = ".$_POST['tid'];
$Result = $objDB->sql_query($SQL);
if(strtolower($_SESSION['UserInfo']['UType']) == 'line_manager') {
$SQL = "SELECT db_branchManager FROM beatle_branch WHERE branchId = ".$rsTInfo[0]['branchid'];
$rsFTo = $objDB->sql_query($SQL);
$assign_usrid = $rsFTo[0]['db_branchManager'];
$assignto_type = "manager";
}else if(strtolower($_SESSION['UserInfo']['UType']) == 'manager') {
$SQL = "SELECT db_ownerId FROM beatle_organization WHERE OrgId = ".$rsTInfo[0]['orgid'];
$rsFTo = $objDB->sql_query($SQL);
$assign_usrid = $rsFTo[0]['db_ownerId'];
$assignto_type = "owner";
}
$forward = 'Y';
}else{
$SQL = "UPDATE beatle_ticket SET
status = '".$_POST['status']."',
forward = 'N',
active = 'N'
WHERE ticketid = ".$_POST['tid'];
$Result = $objDB->sql_query($SQL);
$assign_usrid = $_SESSION['UserInfo']['UserID'];
$assignto_type = $_SESSION['UserInfo']['UType'];
$forward = 'N';
}
$SQL = "UPDATE beatle_ticket SET active = 'N' WHERE parentid = ".$_POST['tid'];
$Result = $objDB->sql_query($SQL);
$SQL = "SELECT * FROM `beatle_ticket` WHERE parentid = ".$rsTInfo[0]['ticketid']." OR ticketid = ".$rsTInfo[0]['ticketid']." ORDER BY level DESC LIMIT 1";
$rsLevelCount = $objDB->sql_query($SQL);
$level = (int)$rsLevelCount[0]['level'] + 1;
$SQL = "INSERT INTO beatle_ticket SET
userid = ".$_SESSION['UserInfo']['UserID'].",
usertype = '".$_SESSION['UserInfo']['UType']."',
orgid = ".$rsTInfo[0]['orgid'].",
branchid = ".$rsTInfo[0]['branchid'].",
indid = ".$rsTInfo[0]['indid'].",
assignto = ".$assign_usrid.",
assignto_type = '".$assignto_type."',
message = '".AddFieldAddslashes($_POST['message'])."',
forward = '".$forward."',
status = '".strtolower($_POST['status'])."',
tokenid = '".$rsTInfo[0]['tokenid']."',
created_date = '".date("Y-m-d H:i:s")."' ,
level = ".$level.",
contentselection = '".$rsTInfo[0]['contentselection']."',
parentid = ".$parentid.",
id = 0,
ticketuid = '".$rsTInfo[0]['ticketuid']."',
active = 'Y'";
$rsData = $objDB->sql_query($SQL);
if($rsData) {
ReturnPostFormData(array(),"tickets.html?v=list","Ticket data updated successfuly.",0);
exit;
}
else {
ReturnPostFormData(array(),"tickets.html?v=list","There is something wrong to update ticket data.",1);
exit;
}
}
protected function InsertUserInfo(){
global $objDB;
$str = $this->pg.".html?v=".$this->v;
if ($this->CheckReligionIsExistOrNot($this->ReligionName) ){
// return error error value
ReturnPostFormData($this->DataArr,$str,"Religion is alreay taken, please try again with different Religion !",1);
exit;
}else{
// Insert data into user_info table
$SQL = "INSERT INTO religion SET
Religion_Name = '".AddFieldAddslashes($this->ReligionName)."' ,
IsActive = '".AddFieldAddslashes($this->IsActive)."',
IsDeleted = 'N',
AddedBy = '".$_SESSION['UserInfo']['UserID']."',
AddedDate = '".date('Y-m-d H:i:s')."'";
$Result = $objDB->sql_query($SQL);
if($Result)
{
return true;
}
else
{
return false;
}
}
} ///// InsertUserInfo completed /////
public function fetchOrganization(){
global $objDB;
//$SQL = "SELECT OrgID,BranchID,IndustryID FROM beatle_userlogin WHERE 1 = 1 AND lower(db_usertype) = '".strtolower($_SESSION['UserInfo']['UType'])."' AND userId = ".$_SESSION['UserInfo']['UserID'];
//$rsOrg = $objDB->sql_query($SQL);
$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 = "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;
if(!empty($OrgID)) {
$SQL = "SELECT * FROM beatle_industry WHERE db_bracnchid IN (SELECT GROUP_CONCAT(branchId) FROM beatle_branch WHERE db_branchOrg = ".$OrgID.")";
$rsBranches = $objDB->sql_query($SQL);
}else {
$SQL = "SELECT * FROM beatle_industry WHERE db_bracnchid IN (SELECT GROUP_CONCAT(branchId) FROM beatle_branch WHERE db_branchOrg = ".$_SESSION['OtherInfo']['OrgID'].")";
$rsBranches = $objDB->sql_query($SQL);
}
return $rsBranches;
}
public function FetchAllTicketsData($Val=""){
global $objDB;
/*
if($this->status_search == 'All') {
if($_SESSION['UserInfo']['UType'] == 'line_manager')
$SQL = "SELECT * FROM beatle_ticket WHERE assignto = ".$_SESSION['UserInfo']['UserID']." AND parentid = 0 ";
else if($_SESSION['UserInfo']['UType'] == 'manager' || $_SESSION['UserInfo']['UType'] == 'owner')
$SQL = "SELECT * FROM beatle_ticket WHERE assignto = ".$_SESSION['UserInfo']['UserID']." AND parentid <> 0 ";
}else if(strtolower($this->status_search) == 'forward'){
if($_SESSION['UserInfo']['UType'] == 'line_manager') {
$SQL = "SELECT * FROM beatle_ticket WHERE assignto = ".$_SESSION['UserInfo']['UserID']." AND forward = 'Y' AND LOWER(status) = '".strtolower($this->status_search)."' ";
}
else if($_SESSION['UserInfo']['UType'] == 'manager') {
$SQL = "SELECT * FROM beatle_ticket WHERE assignto = ".$_SESSION['UserInfo']['UserID']." AND forward = 'Y' AND LOWER(status) = '".strtolower($this->status_search)."' ";
}
else {
$SQL = "SELECT * FROM beatle_ticket WHERE assignto = ".$_SESSION['UserInfo']['UserID']." AND forward = 'Y' AND LOWER(status) = '".strtolower($this->status_search)."' ";
}
}else {
$SQL = "SELECT * FROM beatle_ticket WHERE assignto = ".$_SESSION['UserInfo']['UserID']." AND LOWER(status) = '".strtolower($this->status_search)."' ";
if($_SESSION['UserInfo']['UType'] == 'line_manager')
$SQL .= " AND parentid = 0";
} */
if($_SESSION['UserInfo']['UType'] == 'line_manager') {
$SQL = "SELECT * FROM beatle_ticket WHERE
assignto = ".$_SESSION['UserInfo']['UserID']." AND
assignto_type = 'line_manager' AND
parentid = 0 ";
}
else if($_SESSION['UserInfo']['UType'] == 'manager') {
$SQL = "SELECT * FROM beatle_ticket WHERE
assignto_type = 'line_manager' OR assignto_type = 'manager' ";
}else if($_SESSION['UserInfo']['UType'] == 'owner'){
$SQL = "SELECT * FROM beatle_ticket WHERE 1 = 1";
}
if($this->status_search != 'All') {
$SQL .= " AND status = '".$this->status_search."' ";
}
// $SQL = "";
if(!empty($this->OrgID))
$SQL .= " AND orgid = ".$this->OrgID;
else{
$SQL .= " AND orgid = ".$_SESSION['OtherInfo']['OrgID']." ";
}
if(!empty($this->BranchID))
$SQL .= " AND branchid = ".$this->BranchID;
else{
if($_SESSION['UserInfo']['UType'] != 'owner')
$SQL .= " AND branchid = ".$_SESSION['OtherInfo']['BranchID']." ";
}
if(!empty($this->IndustryID))
$SQL .= " AND indid = ".$this->IndustryID;
else{
if($_SESSION['UserInfo']['UType'] != 'owner')
$SQL .= " AND indid = ".$_SESSION['OtherInfo']['IndustryID']." ";
}
//$SQL .= " ORDER BY level DESC LIMIT 1";
$SQL .= " GROUP BY ticketuid ORDER BY created_date DESC";
//echo $SQL;
//echo "<br />";
//die;
$rsUserInfo = $objDB->sql_query($SQL);
//echo "<pre>";
//print_r($rsUserInfo);
//echo "</pre>";
return $rsUserInfo;
}
public function fetchParentData($tktid,$level){
global $objDB;
$level = ($level-1);
if($level == 1){
$SQL = "SELECT db_username,db_usertype FROM beatle_userlogin WHERE userId = (SELECT assignto FROM beatle_ticket WHERE ticketid = ".$tktid." AND level = 1)";
$rsData = $objDB->sql_query($SQL);
return $rsData[0]['db_username']." (".$rsData[0]['db_usertype'].")";
}else if($level > 1){
$SQL = "SELECT db_username,db_usertype FROM beatle_userlogin WHERE userId = (SELECT assignto FROM beatle_ticket WHERE parentid = ".$tktid." AND level = ".$level.")";
$rsData = $objDB->sql_query($SQL);
return $rsData[0]['db_username']." (".$rsData[0]['db_usertype'].")";
}
}
public function fetchOtherMessages($tktid){
global $objDB;
if($_SESSION['UserInfo']['UType'] == 'line_manager'){
$SQL = "SELECT * FROM beatle_ticket WHERE userid = ".$_SESSION['UserInfo']['UserID']." AND parentid = ".$tktid;
$rsData = $objDB->sql_query($SQL);
}else if($_SESSION['UserInfo']['UType'] == 'manager' || $_SESSION['UserInfo']['UType'] == 'owner'){
$SQL = "SELECT * FROM beatle_ticket WHERE ticketid = ".$tktid." OR parentid = ".$tktid." ORDER BY level ASC";
// ticketid = ".$tktid." OR
$rsData = $objDB->sql_query($SQL);
//unset($rsData[count($rsData)-1]);
//echo "<pre>";
//print_r($rsData);
//echo "</pre>";
}
return $rsData;
}
public function FetchUserDetailsAll($UID){
global $objDB;
$SQL = "SELECT db_username,db_usertype FROM beatle_userlogin WHERE userId = ".$UID;
$rsData = $objDB->sql_query($SQL);
return $rsData[0]['db_username']." [".$rsData[0]['db_usertype']."]";
exit;
}
public function fetchticketUid($tktid){
global $objDB;
$SQL = "SELECT ticketuid FROM beatle_ticket WHERE ticketid = ".$tktid;
$rsData = $objDB->sql_query($SQL);
return $rsData[0]['ticketuid'];
}
public function fetchUserPhone($id){
global $objDB;
$SQL = "SELECT db_phone FROM beatle_userlogin WHERE userId = ".$id;
$rsOwnerInfo = $objDB->sql_query($SQL);
return $rsOwnerInfo[0]['db_phone'];
}
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 DeletedData($ReligionID){}
public function ChangeReligionStatus(){}
public function CheckReligionIsExistOrNot($ReligionName){}
public function fetchTicketConversationData($tktid){
global $objDB;
$tuid = $this->fetchticketUid($tktid);
$mainTicketID = $this->fetchMainTicketID($tktid);
/*
if($_SESSION['UserInfo']['UType'] == 'line_manager'){
$SQL = "SELECT * FROM beatle_ticket WHERE parentid = ".$mainTicketID." AND ticketuid = '".$tuid."' ORDER BY created_date DESC";
$rsData = $objDB->sql_query($SQL);
}else if($_SESSION['UserInfo']['UType'] == 'manager'){
$SQL = "SELECT * FROM beatle_ticket WHERE (assignto_type = 'line_manager' OR assignto_type = 'manager' ) AND parentid = ".$mainTicketID." AND ticketuid = '".$tuid."' ORDER BY created_date DESC";
$rsData = $objDB->sql_query($SQL);
}else if($_SESSION['UserInfo']['UType'] == 'owner'){
$SQL = "SELECT * FROM beatle_ticket WHERE parentid = ".$mainTicketID." AND ticketuid = '".$tuid."' ORDER BY created_date DESC";
$rsData = $objDB->sql_query($SQL);
} */
$SQL = "SELECT * FROM beatle_ticket WHERE parentid = ".$mainTicketID." AND ticketuid = '".$tuid."' ORDER BY created_date DESC";
$rsData = $objDB->sql_query($SQL);
return $rsData;
}
public function checkEnableDisabled($tktid){
global $objDB;
$mainTicketID = $this->fetchMainTicketID($tktid);
if($_SESSION['UserInfo']['UType'] == 'line_manager'){
$SQL = "SELECT * FROM beatle_ticket WHERE
assignto = ".$_SESSION['UserInfo']['UserID']." AND
assignto_type = '".strtolower($_SESSION['UserInfo']['UType'])."' AND
(parentid = ".$mainTicketID." OR parentid = 0) AND
active = 'Y' AND
status IN('hold','new','forward') AND
ticketuid = (SELECT ticketuid FROM beatle_ticket WHERE ticketid = ".$mainTicketID.")";
}else if($_SESSION['UserInfo']['UType'] == 'manager'){
$SQL = "SELECT * FROM beatle_ticket WHERE
(assignto_type = 'line_manager' OR assignto_type = 'manager') AND
(parentid = ".$mainTicketID." OR parentid = 0) AND
active = 'Y' AND
status IN('hold','new','forward') AND
ticketuid = (SELECT ticketuid FROM beatle_ticket WHERE ticketid = ".$mainTicketID.")";
}else if($_SESSION['UserInfo']['UType'] == 'owner'){
$SQL = "SELECT * FROM beatle_ticket WHERE
(parentid = ".$mainTicketID." OR parentid = 0) AND
active = 'Y' AND
status IN('hold','new','forward') AND
ticketuid = (SELECT ticketuid FROM beatle_ticket WHERE ticketid = ".$mainTicketID.")";
}
return $rsData = $objDB->sql_query($SQL);
}
public function fetchTicketForData($tktid){
global $objDB;
$mainTicketID = $this->fetchMainTicketID($tktid);
$sql = "SELECT contentselection FROM beatle_ticket WHERE ticketid = ".$tktid;
$rsData = $objDB->sql_query($sql);
$rsC = unserialize($rsData[0]['contentselection']);
$html = '';
if(count($rsC) > 0) {
foreach($rsC as $key => $val){
$sql = "SELECT * FROM beatle_pagecontent WHERE pageConId = ".$val['pagecid'];
$rsPapgeContner = $objDB->sql_query($sql);
$html .= $rsPapgeContner[0]["db_pageConTitle"].", ";
}
}
$html = substr(trim($html),0,-1);
echo $html;
}
public function fetchMainTicketID($tktid){
global $objDB;
$sql = "SELECT ticketid, parentid FROM beatle_ticket WHERE ticketid = ".$tktid;
$rsTicket = $objDB->sql_query($sql);
if($rsTicket[0]['parentoid'] == 0){
$mainTicketID = $rsTicket[0]['ticketid'];
return $mainTicketID;
exit;
}else{
return $mainTicketID = $rsTicket[0]['parentoid'];
exit;
}
/*
$sql = "SELECT ticketid FROM beatle_ticket WHERE parentid = 0 AND ticketid = ".$rsTicket[0]['parentoid'];
$rsTicket = $objDB->sql_query($sql);
$mainTicketID = $rsTicket[0]['ticketid'];
return $mainTicketID;
exit;
*/
}
public function fetchTicketCreatedByData($tktid){
global $objDB;
$SQL = "SELECT
beatle_ticket.ticketuid,
beatle_ticket.message,
beatle_ticket.status,
beatle_ticket.created_date,
beatle_userlogin.db_phone
FROM beatle_ticket LEFT JOIN beatle_userlogin ON beatle_ticket.userid = beatle_userlogin.userId WHERE beatle_ticket.ticketid = ".$tktid." AND beatle_ticket.parentid = 0";
$rsData = $objDB->sql_query($SQL);
//echo "<pre>";
//print_r($rsData);
//echo "</pre>";
return $rsData;
}
public function fetchCurrentStatus($tktid){
global $objDB;
$SQL = "SELECT
beatle_ticket.status
FROM beatle_ticket WHERE beatle_ticket.ticketid = ".$tktid." AND beatle_ticket.parentid = 0";
$rsData = $objDB->sql_query($SQL);
//echo "<pre>";
//print_r($rsData);
//echo "</pre>";
return $rsData[0]['status'];
}
public function findRootTicketID($tktid){
global $objDB;
$SQL = "SELECT ticketid,parentid FROM beatle_ticket WHERE ticketid = ".$tktid;
$rsData = $objDB->sql_query($SQL);
if($rsData[0]['parentid'] == 0){
return $rsData[0]['ticketid'];
}else{
$SQL = "SELECT ticketid,parentid FROM beatle_ticket WHERE ticketid = ".$rsData[0]['parentid']." AND parentid = 0" ;
$rsData = $objDB->sql_query($SQL);
return $rsData[0]['ticketid'];
}
}
}
?>