<?php
date_default_timezone_set('Asia/Kolkata');
defined('BASEPATH') or exit('No direct script access allowed');
class Fx
{
	public $dbPrventWords = "drop |delete |update |truncate |insert |create ";
	public $userId;
	public $roleId;
	public $userDetails;
	public $userChilds;
	public $userChildsWithParent;
	public $isMasterRole = false;
	private $key = 'sansoftwares1806';

	public $templateTypeArray = array(
		'sms_for_otp'							=> 'OTP SMS',
		'email_for_otp' 						=> 'OTP Email',
		'email_for_new_lead' 					=> 'Acknowledgement Email for New Lead',
		'email_for_new_client_registration' 	=> 'Acknowledgement Email for New Client Registration',
		'email_for_plan_renewable' 				=> 'Acknowledgement Email for Plan Renewable',
		'email_for_plan_change' 				=> 'Acknowledgement Email for Plan Change',
		'email_before_due_date_for_plan_renewable' => 'Reminder Email for Plan Renewal Before Due Date',
		'email_on_due_date_for_plan_renewable' 	=> 'Reminder Email for Plan Renewal on Due Date'
	);

	public $templateArray = array(
		'Email' => array(
			array(
				'##ticket_id##' => 'ticket_id',
				'##new_ticket_id##' => 'new_ticket_id',
				'##ticket_unique_id##' => 'ticket_unique_id',
				'##email_message##' => 'Email_Message',
				'##agent_name##' => 'agent_name',
				'##agent_mobile_number##' => 'agent_mobile_number',
				'##cc_email##' => 'cc_email',
				'##remarks##' => 'remarks',
				'##mobile_no##' => 'mobile_no',
				'##signature##' => 'signature',
				'##last_updated_on##' => 'last_updated_on',
				'##person_name##' => 'person_name',
				'##subject##' => 'subject',
				'##to_email##' => 'to_email',
				'##from_email##' => 'from_email',
				'##happy_code##' => 'happy_code',
				'##duration##' => 'duration',
				'##ticket_link##' => 'ticket_link'
			)
		),
		'UserEmail' => array(
			array(
				'##ticket_id##' => 'ticket_id',
				'##ticket_unique_id##' => 'ticket_unique_id',
				'##Email_Message##' => 'Email_Message',
				'##Agent_Name##' => 'Agent_Name',
				'##Agent_mobile_number##' => 'Agent_mobile_number',
				'##cc_email##' => 'cc_email',
				'##remarks##' => 'remarks',
				'##mobile_no##' => 'mobile_no',
				'##signature##' => 'signature',
				'##footer##' => 'footer',
				'##last_updated_on##' => 'last_updated_on',
				'##person_name##' => 'person_name',
				'##subject##' => 'subject',
				'##to_email##' => 'to_email',
				'##from_email##' => 'from_email',
				'##happy_code##' => 'happy_code',
				'##duration##' => 'duration',
				'##ticket_link##' => 'ticket_link'
			)
		),
		'SMS' => array(
			array(
				'##ticket_id##' => 'ticket_id',
				'##new_ticket_id##' => 'new_ticket_id',
				'##ticket_unique_id##' => 'ticket_unique_id',
				'##email_message##' => 'email_message',
				'##agent_name##' => 'agent_name',
				'##agent_mobile_number##' => 'agent_mobile_number',
				'##cc_email##' => 'cc_email',
				'##remarks##' => 'remarks',
				'##mobile_no##' => 'mobile_no',
				'##signature##' => 'signature',
				'##last_updated_on##' => 'last_updated_on',
				'##person_name##' => 'person_name',
				'##subject##' => 'subject',
				'##to_email##' => 'to_email',
				'##from_email##' => 'from_email',
				'##happy_code##' => 'happy_code',
				'##duration##' => 'duration',
				'##ticket_link##' => 'ticket_link'
			)
		),
		'Whatsapp' => array(
			array(
				'##ticket_id##' => 'ticket_id',
				'##new_ticket_id##' => 'new_ticket_id',
				'##ticket_unique_id##' => 'ticket_unique_id',
				'##email_message##' => 'email_message',
				'##agent_name##' => 'agent_name',
				'##agent_mobile_number##' => 'agent_mobile_number',
				'##cc_email##' => 'cc_email',
				'##remarks##' => 'remarks',
				'##mobile_no##' => 'mobile_no',
				'##signature##' => 'signature',
				'##last_updated_on##' => 'last_updated_on',
				'##person_name##' => 'person_name',
				'##subject##' => 'subject',
				'##to_email##' => 'to_email',
				'##from_email##' => 'from_email',
				'##happy_code##' => 'happy_code',
				'##duration##' => 'duration',
				'##ticket_link##' => 'ticket_link'
			)
		)
	);

	public $notInSessionApi = array('api', 'login', 'logout');
	function __construct()
	{
		$this->CI = &get_instance();
	}


	public function validateCORS()
	{

		$originHttps = $_SERVER['HTTP_ORIGIN'];
		$origin = isset($originHttps) ? $originHttps : '';
		// fx::pr($origin,1);
		// if (in_array($origin, $this->CI->config->item('allowed_origins'))) {
		// header("Access-Control-Allow-Origin: $origin");
		header("Access-Control-Allow-Origin:*");
		header('Access-Control-Allow-Methods: GET, POST');
		header('Access-Control-Allow-Headers: Origin, Content-Type, X-Auth-Token, token, loginsession, isloginwithmaster,accesstoken');

		// } else {
		// 	header('HTTP/1.1 403 Access Forbidden');
		// 	exit("Your origin is not allowed to access this resource.");
		// }
	}

	static function pr($ar, $ex = 0)
	{
		echo '<pre>';
		print_r($ar);
		echo '</pre>';
		if ($ex == 1) {
			exit;
		}
	}

	public function validate()
	{
		$this->CI = &get_instance();
		$data_1 = json_decode(file_get_contents("php://input"));
		$data_1 = (array)$data_1;
		$data_2 = $this->CI->input->post();

		if (!is_array($data_2))
			$data_2 = (array) $data_2;
		$post = array_merge($data_1, $data_2);

		$this->apiName			= $this->CI->uri->segment(1);
		$this->apiMethodName	= $this->CI->uri->segment(2);

		if (empty($post['token']) && (strtolower($this->apiName) == 'api' && !in_array($this->apiMethodName, $this->notInSessionApi))) {
			return false;
		} else if (!in_array($this->apiMethodName, $this->notInSessionApi)) {
			$this->CI->load->model('Users_Model');
			$userData = $this->CI->Users_Model->getUserByToken($post['token']);

			if (!empty($userData['user_role_id'])) {
				$this->userId = $userData['user_id'];
				$this->roleId = $userData['user_role_id'];
				$this->isMasterRole = empty($userData['parent_user_id']) ? true : false;
				if ($this->isMasterRole) {
					$childData = $this->CI->Users_Model->getAllUsers();
					$this->userChilds = $childData['all_users'];
					$this->userChildsWithParent = $this->userChilds;
				} else {
					$childData = $this->CI->Users_Model->getChildUsers($this->userId);
					if (!empty($childData)) {
						$this->userChilds = $childData[0]['@chuser'];
						$this->userChildsWithParent = $this->userChilds . ',' . $this->userId;
					}
				}
				return true;
			} else {
				return false;
			}
		}
	}

	public function encrypt_decrypt($action, $string)
	{
		$output = false;
		$encrypt_method = "AES-128-CBC";
		$secret_key = 'sansoftwares1806';
		$secret_iv = 'sansoftwares1806';
		$key = "";
		// iv - encrypt method AES-256-CBC expects 16 bytes - else you will get a warning
		$iv = substr(hash('sha256', $secret_iv), 0, 16);
		if ($action == 'encrypt') {
			$output = openssl_encrypt($string, $encrypt_method, $key, 0, $iv);
			$output = base64_encode($output);
		} elseif ($action == 'decrypt') {
			$output = openssl_decrypt(base64_decode($string), $encrypt_method, $key, 0, $iv);
		}
		return $output;
	}



	function xor_encrypt($data)
	{
		$iv = openssl_random_pseudo_bytes(16);
		$encryptedData = openssl_encrypt($data, 'AES-128-CBC', $this->key, OPENSSL_RAW_DATA, $iv);
		$encryptedDataWithIV = $iv . $encryptedData;
		$base64Encoded = base64_encode($encryptedDataWithIV);
		return $base64Encoded;
	}
	function xor_decrypt($encryptedData)
	{

		$encryptedData = base64_decode($encryptedData);
		$iv = substr($encryptedData, 0, 16);
		$encryptedBytes = substr($encryptedData, 16);
		$decryptedData = openssl_decrypt($encryptedBytes, 'AES-128-CBC', $this->key, OPENSSL_RAW_DATA, $iv);
		return $decryptedData;
	}



	function generateTokenForPayment($ref_type = '', $ref_id = '', $order_id = '')
	{
		$str	= "$ref_type-$ref_id-$order_id";
		return $this->encrypt_decrypt('encrypt', $str);
	}

	public function setEailSMSTemplate($type = 'common', $templatedbArray, $dataArray, $dynamic_master_name)
	{
		$this->CI->load->model('Api_Model');
		// replace first string and replaced string array ************ Start
		$baseStringKeys = array_keys($this->templateArray[$type][0]);

		$dynamicFormData = $this->CI->Api_Model->getDynamicFormField($dynamic_master_name);

		$dynamicFieldNames = array();
		foreach ($dynamicFormData as $data) {
			$dynamicFieldNames[] = "##" . $data['field_name'] . "##";
		}

		$baseStringKeys = array_merge($baseStringKeys, $dynamicFieldNames);

		foreach ($baseStringKeys as $baseKey) {
			$key = trim(str_replace(array('##'), array(''), $baseKey));
			$replacvalue[] = (isset($dataArray["$key"])) ? $dataArray["$key"] : '';
		}

		// replace first string and replaced string array ************* ENDS
		$emilSubject = $emilBody = $smsBody =  $whatsappBody = '';
		foreach ($templatedbArray as $temp) {
			if ($type == TEMPLATE_TYPE_EMAIL) {
				$emilSubject = str_replace($baseStringKeys, $replacvalue, $temp->customer_email_subject);
				$emilBody = str_replace($baseStringKeys, $replacvalue, $temp->customer_email_template);
			} elseif ($type == TEMPLATE_TYPE_USER_EMAIL) {
				$emilSubject = str_replace($baseStringKeys, $replacvalue, $temp->user_email_subject);
				$emilBody = str_replace($baseStringKeys, $replacvalue, $temp->user_email_template);
			} else if ($type == TEMPLATE_TYPE_SMS) {
				$smsBody = str_replace($baseStringKeys, $replacvalue, $temp->customer_sms_template);
			} else if ($type == TEMPLATE_TYPE_WHATSAPP) {
				$whatsappBody = str_replace($baseStringKeys, $replacvalue, $temp->customer_wp_template);
			}
		}
		return array('emilSubject' => $emilSubject, 'emilBody' => $emilBody, 'smsBody' => $smsBody, 'whatsappBody' => $whatsappBody);
	}

	public function createMailJsonFile($data)
	{
		if ($data['is_replied_mail'] == "" && $data['is_forward_mail'] == "") {
			if ($data['footer_url'] != "" && $data['footer_url'] != null) {
				$data['conversation'] .= '<br><br><br><br><img src="cid:footer" width="' . $data['footer_width'] . '">';
			}
		}

		if (count($data) > 0) {

			$toEmailAddresses = explode(",", $data['to_email']);
			$toValue = array();

			foreach ($toEmailAddresses as $email) {
				$toValue[] = array(
					"address" => trim($email),
					"name" => ""
				);
			}
			$currentDate = new DateTime();
			$formattedDate = $currentDate->format('Y-m-d\TH:i:s.v\Z');

			$jsonArray = array(
				"headerData" => array(
					"message-id" => $data['uuid'],
					"date" => $formattedDate,
					"to" => array(
						"value" => $toValue,
					),

					"from" => array(
						"value" => array(
							array(
								"address" => $data['from_email'],
							)
						),
					),

					"subject" => $data['subject']
				),
				"bodyData" => base64_encode($data['conversation']),
				"attachmentData" => array()
			);

			if (!empty($data['attachmentArray'])) {
				foreach ($data['attachmentArray'] as $attachment) {
					$jsonArray['attachmentData'][] = array(
						"filename" => $attachment['name'],
						"contentType" =>  stripslashes($attachment['type'])
					);
				}
			}
			if (!empty($data['cc_email'])) {
				$jsonArray['headerData']['cc'] = array(
					"value" => $this->generateEmailAddresses($data['cc_email'])
				);
			}

			if (!empty($data['bcc_email'])) {
				$jsonArray['headerData']['bcc'] = array(
					"value" => $this->generateEmailAddresses($data['bcc_email'])
				);
			}
			$json = json_encode($jsonArray);


			// $path = $_SERVER["DOCUMENT_ROOT"] . '/mailread/mail_json_file/';
			$todayfolderPath = NODE_APP_PATH . date("Y-m-d");
			if (!is_dir($todayfolderPath)) {
				mkdir($todayfolderPath, 0777, true);
			}
			chmod($todayfolderPath, 0777);
			$path = $todayfolderPath . '/' . $data['msg_id'];
			if (!is_dir($path)) {
				mkdir($path, 0777, true);
			}
			chmod($path, 0777);
			// $path = NODE_APP_PATH;
			$filename = $data['msg_id'] . '.json';
			$filePath = $path . '/' . $filename;

			$json = json_encode($jsonArray, JSON_UNESCAPED_UNICODE);
			if (file_put_contents($filePath, $json) !== false) {
				chmod($filePath, 0777);
				return true;
			} else {
				return false;
			}
		} else {
			return false;
		}
	}

	function generateEmailAddresses($ccEmails)
	{
		$ccEmailAddresses = array();
		$emails = explode(',', $ccEmails);
		foreach ($emails as $email) {
			$ccEmailAddresses[] = array(
				"address" => trim($email),
			);
		}
		return $ccEmailAddresses;
	}

	public function generateEmailUnqMsgId($fromMailId)
	{
		$this->CI->load->model('Master_Model');
		$outgoing_mail_server = $this->CI->Master_Model->getScalerCol("outgoing_mail_server", "tms_email_account t1", "1=1 and t1.email = '$fromMailId'")->outgoing_mail_server;

		$uuid = md5(uniqid('', true));

		$uuid = "<" . substr($uuid, 0, 8) . '-' . substr($uuid, 8, 4) . '-' .  substr($uuid, 12, 4) . '-' .  substr($uuid, 16, 4) . '-' . substr($uuid, 20) . '@' . $outgoing_mail_server . ">";
		return $uuid;
	}

	public function generateMsgId($uuid)
	{
		$attachGolderName = explode('@', preg_replace("/[<>\-.]/", '', $uuid));
		$msgId = $attachGolderName[0];
		return $msgId;
	}

	function runPhpCommand($command)
	{
		try {
			if (strtoupper(substr(PHP_OS, 0, 3)) == 'WIN') {
				exec('php ' . $command);
			} else {
				exec('php ' . $command . ' >  /dev/null 2>&1 &');
			}
		} catch (Exception $e) {
			echo $e->getMessage();
			return false;
		}
		return true;
	}

	public function generateUserLogs($type = 1, $name, $userName, $userId, $data, $id = '', $msg = '')
	{
		switch ($type) {
			case '1':
				// For Add
				$message = "A New $name Added by $userName";
				break;
			case '2':
				// For Edit
				$message = "$name (ID : $id) Updated by $userName";
				break;
			case '3':
				// For delete
				$message = "$name (ID : $id) Deleted by $userName";
				break;
			case '4':
				// For Search
				$message = "$name List Search by $userName";
				break;
			case '5':
				// For unread mail on ticket search page
				$message = $msg;
				break;
			default:
				break;
		}
		if ($msg != '')
			$message = $msg;

		if ($message == '')
			return;
		$pageData = json_encode($data);
		$logData = array(
			'type' => $type,
			'message' => $message,
			'page_name' => $name,
			'method' => $this->CI->router->fetch_method(),
			'user_id' => $userId,
			'remote_ip' => $_SERVER['REMOTE_ADDR'],
			'data' => $pageData
		);

		return $this->CI->Api_Model->enterUserLog($logData);
	}

	function generate_session_id()
	{
		return md5(uniqid(rand(), true));
	}

	function addslashes_custom($str)
	{
		return preg_replace('/([\\\'"])/', "\\\\$1", $str);
	}

	public function whereString($search, $tableAliasAr = array(), $operator = array(), $colmnName = array(), $removeName = array())
	{

		$whereAr[] = " 1=1";
		foreach ($search as $key => $fld) {
			$fld['name'] = trim($fld['name']);
			if ($removeName[$fld['name']]) {
				continue;
			}

			$fld['value'] = trim($fld['value']);

			if (isset($tableAliasAr[$fld['name']]) && $tableAliasAr[$fld['name']] != '')
				$tableAlias = $tableAliasAr[$fld['name']];
			elseif (isset($tableAliasAr['*']) && $tableAliasAr['*'] != '')
				$tableAlias = $tableAliasAr['*'];
			else
				$tableAlias = '';
			if ($fld['value'] == '') {
				unset($_data['search'][$key]);
			} else {
				$fld['name'] = $this->CI->security->xss_clean($fld['name']);
				$fld['value'] = $this->CI->security->xss_clean($fld['value']);
				if (isset($colmnName[$fld['name']]) && $colmnName[$fld['name']] != '')
					$fldName = $colmnName[$fld['name']];
				else
					$fldName = $fld['name'];

				if (count($operator) > 0 && isset($operator[$fld['name']])) {
					if ($operator[$fld['name']] == 'LIKE') {
						$whereAr[] = $tableAlias . $fldName . " LIKE '%" . $fld['value'] . "%'";
					} elseif ($operator[$fld['name']] == 'LIKELEFT') {
						$whereAr[] = $tableAlias . $fldName . " LIKE '%" . $fld['value'] . "'";
					} elseif ($operator[$fld['name']] == 'LIKERIGHT') {
						$whereAr[] = $tableAlias . $fldName . " LIKE '" . $fld['value'] . "%'";
					} elseif ($operator[$fld['name']] == 'GT') {
						$whereAr[] = $tableAlias . $fldName . " > '" . $fld['value'] . "'";
					} elseif ($operator[$fld['name']] == 'LT') {
						$whereAr[] = $tableAlias . $fldName . " < '" . $fld['value'] . "'";
					} elseif ($operator[$fld['name']] == 'GTE') {
						$whereAr[] = $tableAlias . $fldName . " >= '" . $fld['value'] . "'";
					} elseif ($operator[$fld['name']] == 'LTE') {
						$whereAr[] = $tableAlias . $fldName . " <= '" . $fld['value'] . "'";
					} elseif ($operator[$fld['name']] == 'IN') {
						$fldVal = str_replace(",", "','", $fld['value']);
						$whereAr[] = $tableAlias . $fldName . " IN ('" . $fldVal . "')";
					} elseif ($operator[$fld['name']] == 'NOTIN') {
						$fldVal = str_replace(",", "','", $fld['value']);
						$whereAr[] = $tableAlias . $fldName . " NOT IN ('" . $fldVal . "')";
					} elseif ($operator[$fld['name']] == 'NOT') {
						$whereAr[] = $tableAlias . $fldName . " != '" . $fld['value'] . "'";
					} elseif ($operator[$fld['name']] == 'DLIKE') {
						$whereAr[] = "date(" . $tableAlias . $fldName . ") LIKE '%" . $fld['value'] . "%'";
					} elseif ($operator[$fld['name']] == 'DLIKELEFT') {
						$whereAr[] = "date(" . $tableAlias . $fldName . ") LIKE '%" . $fld['value'] . "'";
					} elseif ($operator[$fld['name']] == 'DLIKERIGHT') {
						$whereAr[] = "date(" . $tableAlias . $fldName . ") LIKE '" . $fld['value'] . "%'";
					} elseif ($operator[$fld['name']] == 'DGT') {
						$whereAr[] = "date(" . $tableAlias . $fldName . ") > '" . $fld['value'] . "'";
					} elseif ($operator[$fld['name']] == 'DLT') {
						$whereAr[] = "date(" . $tableAlias . $fldName . ") < '" . $fld['value'] . "'";
					} elseif ($operator[$fld['name']] == 'DGTE') {
						$whereAr[] = "date(" . $tableAlias . $fldName . ") >= '" . $fld['value'] . "'";
					} elseif ($operator[$fld['name']] == 'DLTE') {
						$whereAr[] = "date(" . $tableAlias . $fldName . ") <= '" . $fld['value'] . "'";
					} elseif ($operator[$fld['name']] == 'DIN') {
						$fldVal = str_replace(",", "','", $fld['value']);
						$whereAr[] = "date(" . $tableAlias . $fldName . ") IN ('" . $fldVal . "')";
					} elseif ($operator[$fld['name']] == 'DNOTIN') {
						$fldVal = str_replace(",", "','", $fld['value']);
						$whereAr[] = "date(" . $tableAlias . $fldName . ") NOT IN ('" . $fldVal . "')";
					} elseif ($operator[$fld['name']] == 'DNOT') {
						$whereAr[] = "date(" . $tableAlias . $fldName . ") != '" . $fld['value'] . "'";
					} elseif ($operator[$fld['name']] == 'DGTE_F') {
						$date = date('Y-m-d', strtotime($fld['value']));
						$whereAr[] = "date(" . $tableAlias . $fldName . ") >= '" . $date . "'";
					} elseif ($operator[$fld['name']] == 'DLTE_L') {
						$date = date('Y-m-d', strtotime($fld['value']));
						$whereAr[] = "date(" . $tableAlias . $fldName . ") <= '" . $date . "'";
					} elseif ($operator[$fld['name']] == 'DTGTE_F') {
						$date = date('Y-m-d H:i:s', strtotime($fld['value']));
						$whereAr[] = "date_format(" . $tableAlias . $fldName . ",'%Y-%m-%d %H:%i:%s') >= '" . $date . "'";
					} elseif ($operator[$fld['name']] == 'DTLTE_L') {
						$date = date('Y-m-d H:i:s', strtotime($fld['value']));
						$whereAr[] = "date_format(" . $tableAlias . $fldName . ",'%Y-%m-%d %H:%i:%s') <='" . $date . "'";
					} elseif ($operator[$fld['name']] == 'DET_C') {
						$date = date('Y-m-d', strtotime($fld['value']));
						$whereAr[] = "date(" . $tableAlias . $fldName . ") = '" . $date . "'";
					} else {
						$whereAr[] = $tableAlias . $fldName . "='" . $fld['value'] . "'";
					}
				} else {
					if ($fld['name'] == 'company') {
						$whereAr[] = '(' . $tableAlias . 'client_id' . "='" . $fld['value'] . "' or " . $tableAlias . 'parent_id' . "='" . $fld['value'] . "')";
						// fx::pr($whereAr,1);
					} else {
						$whereAr[] = $tableAlias . $fldName . "='" . $fld['value'] . "'";
					}
				}
			}
		}
		return implode(' and ', $whereAr);
	}
	//FOR CUSTOM REPORT  START 
	function dbError($db, $exit = false)
	{
		$message = @$db->error()['message'];
		if ($exit == true && $message != '')
			$this->api_response(['success' => false, 'message' => $message], 400);
		return $message;
	}
	function api_response($response, $status_code = 200)
	{
		$CI = &get_instance();
		$CI->output
			->set_status_header($status_code)
			->set_content_type('application/json', 'utf-8')
			->set_output(json_encode($response, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES))
			->_display();
		exit;
	}

	function getFormattedSQL($sql_raw)
	{
		if (empty($sql_raw) || !is_string($sql_raw)) {
			return false;
		}

		$sql_reserved_all = array(
			'ACCESSIBLE',
			'ACTION',
			'ADD',
			'AFTER',
			'AGAINST',
			'AGGREGATE',
			'ALGORITHM',
			'ALL',
			'ALTER',
			'ANALYSE',
			'ANALYZE',
			'AND',
			'AS',
			'ASC',
			'AUTOCOMMIT',
			'AUTO_INCREMENT',
			'AVG_ROW_LENGTH',
			'BACKUP',
			'BEGIN',
			'BETWEEN',
			'BINLOG',
			'BOTH',
			'BY',
			'CASCADE',
			'CASE',
			'CHANGE',
			'CHANGED',
			'CHARSET',
			'CHECK',
			'CHECKSUM',
			'COLLATE',
			'COLLATION',
			'COLUMN',
			'COLUMNS',
			'COMMENT',
			'COMMIT',
			'COMMITTED',
			'COMPRESSED',
			'CONCURRENT',
			'CONSTRAINT',
			'CONTAINS',
			'CONVERT',
			'CREATE',
			'CROSS',
			'CURRENT_TIMESTAMP',
			'DATABASE',
			'DATABASES',
			'DAY',
			'DAY_HOUR',
			'DAY_MINUTE',
			'DAY_SECOND',
			'DEFINER',
			'DELAYED',
			'DELAY_KEY_WRITE',
			'DELETE',
			'DESC',
			'DESCRIBE',
			'DETERMINISTIC',
			'DISTINCT',
			'DISTINCTROW',
			'DIV',
			'DO',
			'DROP',
			'DUMPFILE',
			'DUPLICATE',
			'DYNAMIC',
			'ELSE',
			'ENCLOSED',
			'END',
			'ENGINE',
			'ENGINES',
			'ESCAPE',
			'ESCAPED',
			'EVENTS',
			'EXECUTE',
			'EXISTS',
			'EXPLAIN',
			'EXTENDED',
			'FAST',
			'FIELDS',
			'FILE',
			'FIRST',
			'FIXED',
			'FLUSH',
			'FOR',
			'FORCE',
			'FOREIGN',
			'FROM',
			'FULL',
			'FULLTEXT',
			'FUNCTION',
			'GEMINI',
			'GEMINI_SPIN_RETRIES',
			'GLOBAL',
			'GRANT',
			'GRANTS',
			'GROUP',
			'HAVING',
			'HEAP',
			'HIGH_PRIORITY',
			'HOSTS',
			'HOUR',
			'HOUR_MINUTE',
			'HOUR_SECOND',
			'IDENTIFIED',
			'IF',
			'IGNORE',
			'IN',
			'INDEX',
			'INDEXES',
			'INFILE',
			'INNER',
			'INSERT',
			'INSERT_ID',
			'INSERT_METHOD',
			'INTERVAL',
			'INTO',
			'INVOKER',
			'IS',
			'ISOLATION',
			'JOIN',
			'KEY',
			'KEYS',
			'KILL',
			'LAST_INSERT_ID',
			'LEADING',
			'LEFT',
			'LEVEL',
			'LIKE',
			'LIMIT',
			'LINEAR',
			'LINES',
			'LOAD',
			'LOCAL',
			'LOCK',
			'LOCKS',
			'LOGS',
			'LOW_PRIORITY',
			'MARIA',
			'MASTER',
			'MASTER_CONNECT_RETRY',
			'MASTER_HOST',
			'MASTER_LOG_FILE',
			'MASTER_LOG_POS',
			'MASTER_PASSWORD',
			'MASTER_PORT',
			'MASTER_USER',
			'MATCH',
			'MAX_CONNECTIONS_PER_HOUR',
			'MAX_QUERIES_PER_HOUR',
			'MAX_ROWS',
			'MAX_UPDATES_PER_HOUR',
			'MAX_USER_CONNECTIONS',
			'MEDIUM',
			'MERGE',
			'MINUTE',
			'MINUTE_SECOND',
			'MIN_ROWS',
			'MODE',
			'MODIFY',
			'MONTH',
			'MRG_MYISAM',
			'MYISAM',
			'NAMES',
			'NATURAL',
			'NOT',
			'NULL',
			'OFFSET',
			'ON',
			'OPEN',
			'OPTIMIZE',
			'OPTION',
			'OPTIONALLY',
			'OR',
			'ORDER',
			'OUTER',
			'OUTFILE',
			'PACK_KEYS',
			'PAGE',
			'PARTIAL',
			'PARTITION',
			'PARTITIONS',
			'PASSWORD',
			'PRIMARY',
			'PRIVILEGES',
			'PROCEDURE',
			'PROCESS',
			'PROCESSLIST',
			'PURGE',
			'QUICK',
			'RAID0',
			'RAID_CHUNKS',
			'RAID_CHUNKSIZE',
			'RAID_TYPE',
			'RANGE',
			'READ',
			'READ_ONLY',
			'READ_WRITE',
			'REFERENCES',
			'REGEXP',
			'RELOAD',
			'RENAME',
			'REPAIR',
			'REPEATABLE',
			'REPLACE',
			'REPLICATION',
			'RESET',
			'RESTORE',
			'RESTRICT',
			'RETURN',
			'RETURNS',
			'REVOKE',
			'RIGHT',
			'RLIKE',
			'ROLLBACK',
			'ROW',
			'ROWS',
			'ROW_FORMAT',
			'SECOND',
			'SECURITY',
			'SELECT',
			'SEPARATOR',
			'SERIALIZABLE',
			'SESSION',
			'SET',
			'SHARE',
			'SHOW',
			'SHUTDOWN',
			'SLAVE',
			'SONAME',
			'SOUNDS',
			'SQL',
			'SQL_AUTO_IS_NULL',
			'SQL_BIG_RESULT',
			'SQL_BIG_SELECTS',
			'SQL_BIG_TABLES',
			'SQL_BUFFER_RESULT',
			'SQL_CACHE',
			'SQL_CALC_FOUND_ROWS',
			'SQL_LOG_BIN',
			'SQL_LOG_OFF',
			'SQL_LOG_UPDATE',
			'SQL_LOW_PRIORITY_UPDATES',
			'SQL_MAX_JOIN_SIZE',
			'SQL_NO_CACHE',
			'SQL_QUOTE_SHOW_CREATE',
			'SQL_SAFE_UPDATES',
			'SQL_SELECT_LIMIT',
			'SQL_SLAVE_SKIP_COUNTER',
			'SQL_SMALL_RESULT',
			'SQL_WARNINGS',
			'START',
			'STARTING',
			'STATUS',
			'STOP',
			'STORAGE',
			'STRAIGHT_JOIN',
			'STRING',
			'STRIPED',
			'SUPER',
			'TABLE',
			'TABLES',
			'TEMPORARY',
			'TERMINATED',
			'THEN',
			'TO',
			'TRAILING',
			'TRANSACTIONAL',
			'TRUNCATE',
			'TYPE',
			'TYPES',
			'UNCOMMITTED',
			'UNION',
			'UNIQUE',
			'UNLOCK',
			'UPDATE',
			'USAGE',
			'USE',
			'USING',
			'VALUES',
			'VARIABLES',
			'VIEW',
			'WHEN',
			'WHERE',
			'WITH',
			'WORK',
			'WRITE',
			'XOR',
			'YEAR_MONTH'
		);

		$sql_skip_reserved_words = array('AS', 'ON', 'USING');
		$sql_special_reserved_words = array('(', ')');

		$sql_raw = str_replace("\n", " ", $sql_raw);

		$sql_formatted = "";

		$prev_word = "";
		$word = "";

		for ($i = 0, $j = strlen($sql_raw); $i < $j; $i++) {
			$word .= $sql_raw[$i];

			$word_trimmed = trim($word);

			if ($sql_raw[$i] == " " || in_array($sql_raw[$i], $sql_special_reserved_words)) {
				$word_trimmed = trim($word);

				$trimmed_special = false;

				if (in_array($sql_raw[$i], $sql_special_reserved_words)) {
					$word_trimmed = substr($word_trimmed, 0, -1);
					$trimmed_special = true;
				}

				$word_trimmed = strtoupper($word_trimmed);

				if (in_array($word_trimmed, $sql_reserved_all) && !in_array($word_trimmed, $sql_skip_reserved_words)) {
					if (in_array($prev_word, $sql_reserved_all)) {
						$sql_formatted .= '<b>' . strtoupper(trim($word)) . '</b>' . '&nbsp;';
					} else {
						$sql_formatted .= '<br/>&nbsp;';
						$sql_formatted .= '<b>' . strtoupper(trim($word)) . '</b>' . '&nbsp;';
					}

					$prev_word = $word_trimmed;
					$word = "";
				} else {
					$sql_formatted .= trim($word) . '&nbsp;';

					$prev_word = $word_trimmed;
					$word = "";
				}
			}
		}

		$sql_formatted .= trim($word);

		return $sql_formatted;
	}
	//FOR CUSTOM REPORT  END


}
