supportcandy / includes / functions / get_sql_query.php

get_sql_query.php in SupportCandy – AI Customer Support Ticket System & Live Chatbot Agent 2.3.1, at includes/functions/get_sql_query.php

199 lines 5.8 KB

Raw Download Zip

1	<?php
2	if ( ! defined( 'ABSPATH' ) ) {
3	exit; // Exit if accessed directly
4	}
5
6	global $wpdb;
7
8	$get_all_meta_keys = $this->get_all_meta_keys();
9
10	$status = $this->get_statuses();
11	$priority = $this->get_priorities();
12	$category = $this->get_categories();
13	$join = array();
14
15	$sql = "SELECT ". $select_str ." FROM ".$wpdb->prefix."wpsc_ticket t";
16
17	$layer1_where = array();
18	$layer1_relation = isset($meta_query['relation']) ? $meta_query['relation'] : 'AND';
19
20	foreach ($meta_query as $outer_key => $inner_query) {
21
22	if( $outer_key !== 'relation' ){
23
24	$layer2_where = array();
25	$layer2_relation = isset($inner_query['relation']) ? $inner_query['relation'] : '';
26
27	if( $layer2_relation ) {
28
29	foreach ($inner_query as $innner_key => $query) {
30
31	if( $innner_key !== 'relation' ){
32
33	$alice = 't';
34	$field = $query['key'];
35
36	if( in_array($query['key'],$get_all_meta_keys) ){
37
38	$join[] = $query['key'];
39	$alice = str_replace('-','_',$query['key']);
40	$field = 'meta_value';
41
42	}
43
44	if (isset($query['type'])) {
45
46	if (is_array($query['value'])) {
47
48	foreach ($query['value'] as $k => $v) {
49	$date_data[] = "'$v'";
50	}
51	}
52
53	$value = is_array($query['value']) ? '(' . implode('AND',$date_data) . ')' : "'" . $query['value'] . "'";
54
55	} else {
56
57	if(is_array($query['value'])){
58	foreach ($query['value'] as $val_key => $val) {
59	$query['value'][$val_key] = "'".$val."'";
60	}
61	}
62	$value = is_array($query['value']) ? '(' . implode(',',$query['value']) . ')' : "'" . $query['value'] . "'";
63
64	}
65
66	$layer2_where[] = $alice . "." . $field. " " . $query['compare']. " " . $value;
67
68	}
69
70	}
71
72	} else {
73
74	$alice = 't';
75	$field = $inner_query['key'];
76
77	if( in_array($inner_query['key'],$get_all_meta_keys) ){
78	$join[] = $inner_query['key'];
79	$alice = str_replace('-','_',$inner_query['key']);
80	$field = 'meta_value';
81	}
82	/**
83	* If type is DATE, need to implode it.
84	*/
85	if(isset($inner_query['type'])){
86
87	foreach ($inner_query['value'] as $k => $v) {
88	$date_data[] = "'$v'";
89	}
90	$value = implode(' AND ',$date_data) ;
91
92	} else {
93
94	if(is_array($inner_query['value'])){
95	foreach ($inner_query['value'] as $val_key => $val) {
96	$inner_query['value'][$val_key] = "'".$val."'";
97	}
98	}
99	$value = is_array($inner_query['value']) ? '(' . implode(',',$inner_query['value']) . ')' : "'" . $inner_query['value'] . "'";
100
101	}
102	$layer2_where[] = $alice . "." . $field. " " . $inner_query['compare']. " " . $value;
103
104	}
105
106	if($layer2_where){
107
108	$layer1_where[] = count($layer2_where) > 1 ? "(" . implode(" ".$layer2_relation." ",$layer2_where) .")" : $layer2_where[0];
109
110	}
111
112	}
113
114	}
115
116	if($search){
117	$search = addslashes($search);
118	$term = '%'.$search.'%';
119	$layer1_where[] = "( "
120	."t.id LIKE '$term' OR "
121	."t.customer_name LIKE '$term' OR "
122	."t.customer_email LIKE '$term' OR "
123	."t.ticket_subject LIKE '$term' OR "
124	."tm.meta_value LIKE '$term' "
125	.")" ;
126	}
127
128	$where_str = implode(' '.$layer1_relation.' ', $layer1_where).' ';
129
130	$join = array_unique($join);
131	$join_str = '';
132
133	if (($orderkey = array_search($orderby, $join)) !== false) {
134	unset($join[$orderkey]);
135	}
136
137	foreach ( $join as $slug ) {
138
139	$alice = str_replace('-','_',$slug);
140	$join_str .= "JOIN {$wpdb->prefix}wpsc_ticketmeta ".$alice." ON t.id = ".$alice.".ticket_id AND ".$alice.".meta_key = '".$slug."' ";
141
142	}
143
144	if($search){
145	$join_str .= "JOIN {$wpdb->prefix}wpsc_ticketmeta tm ON t.id = tm.ticket_id ";
146	}
147
148	if ( $orderby && in_array( $orderby, $get_all_meta_keys ) ) {
149
150	$alice = str_replace('-','_',$orderby);
151	$join_str .= " LEFT JOIN {$wpdb->prefix}wpsc_ticketmeta ".$alice." ON t.id = ".$alice.".ticket_id AND ".$alice.".meta_key = '".$orderby."' ";
152	$orderby_str = " ORDER BY " .$alice.".meta_value";
153
154	} else if( $orderby ) {
155
156	if ( $orderby == 'ticket_status' ) {
157
158	$orderby_str = " ORDER BY FIELD( t.ticket_status, ". implode(',',$status)." )";
159
160	} elseif ( $orderby == 'ticket_priority' ) {
161
162	$orderby_str = " ORDER BY FIELD( t.ticket_priority, ". implode(',',$priority) ." )";
163
164	} elseif ( $orderby == 'ticket_category' ) {
165
166	$orderby_str = " ORDER BY FIELD( t.ticket_category, ".implode(',',$category)." )";
167
168	} else {
169
170	$orderby_str = " ORDER BY t.".$orderby;
171
172	}
173
174	} else {
175
176	$orderby_str = '';
177
178	}
179
180	if( $current_page && $no_of_tickets ) {
181	$offset = ($current_page-1)*$no_of_tickets;
182	$limit = ' LIMIT ' . $no_of_tickets . " OFFSET " . $offset;
183	} else {
184	$limit = '';
185	}
186
187	if($order){
188	$order = ' '.$order;
189	} else {
190	$order = '';
191	}
192
193	$where = '';
194	if(strlen( trim($where_str)) ){
195	$where = ' WHERE ';
196	}
197
198	$sql .= ' ' . $join_str . $where . $where_str . $orderby_str . $order . $limit;
199