· 5 years ago · Dec 10, 2020, 10:16 AM
1<?php
2
3ini_set( 'display_errors', 1 );
4ini_set( 'display_startup_errors', 1 );
5error_reporting( E_ALL );
6
7//require_once('../helpers.php');
8require_once '/opt/data/git/backend/src/backend.referrizer.com/ws/helpers.php';
9require_once '/opt/data/git/backend/src/ws.referrizer.com/v2/services/AccountService.php';
10require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/services/TimezoneHelper.class.php';
11require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/log4php/LoggerSingleton.php';
12
13//libphonenumber library
14includePhpLibPhoneNumber();
15
16class CustomersViewService {
17 const MBO_CLASS_SERVICE_TYPE_ID = 490;
18 const MBO_APPOINTMENT_SERVICE_TYPE_ID = 491;
19 const MBO_SERVICE_PURCHASE_SERVICE_TYPE_ID = 495;
20 const TSK_PROGRAM_TYPE = 'program_type';
21 const TSK_AGE_GROUP = 'age_group';
22 const TSK_DISCOUNT_TYPE = 'discount_type';
23 const TSK_PAYMENT_PLAN = 'payment_plan';
24 const TSK_BUSINESS_HASH = 'a78c7f20d597a6de26b0c31c1098d87e';
25 private $_database;
26 private $_columnid;
27 private $_operatorid;
28 private $_customersviewid;
29 private $_statusid;
30
31 public function __construct( $database = null) {
32 if ( $database != null ) {
33 $this->_database = $database;
34 } else {
35 require_once $_SERVER['DOCUMENT_ROOT'] . '/pg_db_connect.php';
36 $this->_database = $database;
37 }
38
39 $id = 360;
40 $this->_columnid['Name'] = $id;
41 $this->_columnid['Full Name'] = $id ++;
42 $this->_columnid['Email'] = $id ++;
43 $this->_columnid['Phone'] = $id ++;
44 $this->_columnid['Loyalty Points'] = $id ++;
45 $this->_columnid['Balance'] = $id;
46 $this->_columnid['Credit'] = $id ++;
47 $this->_columnid['Source'] = $id ++;
48 $this->_columnid['Last Visit'] = $id ++;
49 $this->_columnid['Referred by'] = $id ++;
50 $this->_columnid['Service'] = $id ++;
51 $this->_columnid['Label'] = $id ++;
52 $this->_columnid['First Name'] = $id ++;
53 $this->_columnid['Last Name'] = $id ++;
54 $this->_columnid['Type'] = $id ++;
55 $this->_columnid['Visits'] = $id ++;
56 $this->_columnid['Registration Date'] = $id ++;
57 $this->_columnid['First Visit'] = $id ++;
58 $this->_columnid['Class'] = $id ++;
59 $this->_columnid['Appointment'] = $id ++;
60 $this->_columnid['Purchase'] = $id ++;
61 $this->_columnid['Age'] = $id ++;
62 $id = 570;
63 $this->_columnid['Birthday'] = $id ++;
64 $this->_columnid['Email Unsubscribed'] = $id ++;
65 $this->_columnid['Text Unsubscribed'] = $id ++;
66 $this->_columnid['Last Purchased Service/Product'] = $id ++;
67 $this->_columnid['Last Purchased Date'] = $id++;
68 $this->_columnid['Text Reply Received'] = $id++;
69 $this->_columnid['Contact Status'] = $id;
70 $this->_columnid['Contract Expiration Date'] = 577;
71 $this->_columnid['Contract Purchase Date'] = 578;
72 $this->_columnid['Contract Payment Due Date'] = 579;
73 $this->_columnid['Contract Program Type'] = 585;
74 $this->_columnid['Contract Payment Plan'] = 586;
75 $this->_columnid['Contract Discount Type'] = 587;
76 $this->_columnid['Contract Age Group'] = 588;
77 $this->_columnid['Last Modified Date'] = 589;
78 $this->_columnid['Last Attendance Status'] = 1040;
79 $this->_columnid['Mindbody Member'] = 1041;
80 $this->_columnid['Gender'] = 1053;
81 $this->_columnid['Claimed Offer'] = 1054;
82
83
84
85 $id = 380;
86 $this->_operatorid['contains'] = $id ++;
87 $this->_operatorid['equals'] = $id ++;
88 $this->_operatorid['greater than'] = $id ++;
89 $this->_operatorid['less than'] = $id ++;
90 $this->_operatorid['last 7 days'] = $id ++;
91 $this->_operatorid['last 30 days'] = $id ++;
92 $this->_operatorid['last 60 days'] = $id ++;
93 $this->_operatorid['last 90 days'] = $id ++;
94 $this->_operatorid['not equal to'] = $id ++;
95 $this->_operatorid['empty'] = $id ++;
96 $this->_operatorid['today'] = $id ++;
97 $this->_operatorid['nextweek'] = $id ++;
98 $this->_operatorid['Yes'] = $id ++;
99 $this->_operatorid['No'] = $id;
100 $this->_operatorid['yesterday'] = 954;
101 $this->_operatorid['does not contain'] = 1080;
102
103
104 $this->_statusid['active'] = 320;
105 $this->_statusid['deleted'] = 321;
106
107 }
108
109 public function createTrigger( $trigger ) {
110 if ( isset( $trigger['accountid'], $trigger['name'] ) ) {
111 // get real ID from md5h
112 $trigger['accountid'] = $this->getAccountID( $trigger['accountid'] );
113 if ( $trigger['accountid'] == - 1 ) {
114 $result['id'] = - 1;
115
116 return $result;
117 }
118
119 // check if trigger with that name already exists
120 $query = "SELECT customersviewid
121 FROM customersview
122 WHERE triggerview = TRUE AND accountid = " . $trigger['accountid'] . " AND name = '" . $trigger['name'] .
123 "' AND statusid = " . $this->_statusid['active'];
124 if ( $this->querySingle( $query ) != null ) {
125 $result['id'] = - 2;
126
127 return $result;
128 }
129
130 // check if trigger with that name already exists in multiaccounts
131 $query = "SELECT cv.customersviewid as id FROM customersview cv JOIN account mainA ON mainA.accountid = cv.accountid AND mainA.main <> TRUE
132 JOIN account a ON a.businessid = mainA.businessid AND a.main = TRUE JOIN business b ON b.businessid = a.businessid
133 WHERE b.has_main_account = TRUE AND UPPER(cv.name) LIKE UPPER('".$trigger['name']."') AND a.accountid = " . $trigger['accountid'] .
134 " AND cv.statusid = " . $this->_statusid['active'];
135 if ( $this->querySingle( $query ) != null ) {
136 $result['id'] = - 3;
137 return $result;
138 }
139
140 // insert trigger
141 if($trigger['triggerType'] == 'stopCampaignTrigger') {
142 $query = "INSERT INTO customersview (accountid, name, triggerview, statusid, stoptriggerview)
143 VALUES ({$trigger['accountid']}, '" . addslashes( $trigger['name'] ) . "', TRUE, " . $this->_statusid['active'] . ", TRUE)
144 RETURNING customersviewid as id";
145 $response = $this->querySingle( $query );
146
147 $this->_customersviewid = $response['id'];
148 } else {
149 $query = "INSERT INTO customersview (accountid, name, triggerview, statusid, stoptriggerview)
150 VALUES ({$trigger['accountid']}, '" . addslashes( $trigger['name'] ) . "', TRUE, " . $this->_statusid['active'] . ", FALSE)
151 RETURNING customersviewid as id";
152 $response = $this->querySingle( $query );
153
154 $this->_customersviewid = $response['id'];
155 }
156
157
158 // generate and add md5h
159 $triggerMd5h = md5( $response['id'] . 'ReferrizViewJMZBA' );
160 $query = "UPDATE customersview
161 SET md5h='{$triggerMd5h}'
162 WHERE customersviewid = $this->_customersviewid";
163 $this->querySingle( $query );
164
165 $result['id'] = $this->_customersviewid;
166 $result['md5h'] = $triggerMd5h;
167
168 return $result;
169 } else {
170 $result['id'] = - 1;
171
172 return $result;
173 }
174 }
175
176 public function getAccountID( $accountMd5h ) {
177 $query = "select accountid as id from account where md5h = '$accountMd5h'";
178 $result = $this->querySingle( $query );
179 if ( $result['id'] ) {
180 return $result['id'];
181 } else {
182 return - 1;
183 }
184 }
185
186 public function getMainAccountViews( $accountMd5h ) {
187 $views = array();
188 $query = "SELECT cv.customersviewid as id, cv.name as name, cv.md5h as md5h, cv.customer_count as customer_count FROM customersview cv JOIN account mainA ON mainA.accountid = cv.accountid JOIN account a ON a.businessid = mainA.businessid WHERE cv.statusid = 320 AND a.md5h = '$accountMd5h' AND a.accountid <> mainA.accountid AND mainA.main = TRUE AND cv.triggerview != TRUE ORDER BY cv.name";
189 $result = cache_aware_pg_query( $this->_database, $query );
190 if ( ( ! empty( $result ) ) && ( pg_numrows( $result ) > 0 ) ) {
191 while ( $row = pg_fetch_array( $result ) ) {
192 $view = null;
193 $view['id'] = $row['id'];
194 $view['name'] = $row['name'];
195 $view['md5h'] = $row['md5h'];
196 $view['customerCount'] = $row['customer_count'];
197 $view['fromMainAccount'] = true;
198
199 array_push( $views, $view );
200 }
201 }
202
203 return $views;
204 }
205
206 public function getMainAccountTriggers( $accountMd5h ) {
207 $views = array();
208 $query = "SELECT cv.customersviewid as id, cv.name as name, cv.md5h as md5h, cv.stoptriggerview as stoptrigger FROM customersview cv JOIN account mainA ON mainA.accountid = cv.accountid JOIN account a ON a.businessid = mainA.businessid WHERE cv.statusid = 320 AND a.md5h = '$accountMd5h' AND mainA.main = TRUE AND a.accountid <> mainA.accountID AND (cv.stoptriggerview = TRUE OR cv.triggerview = TRUE) ORDER BY cv.name";
209 $result = cache_aware_pg_query( $this->_database, $query );
210 if ( ( ! empty( $result ) ) && ( pg_numrows( $result ) > 0 ) ) {
211 while ( $row = pg_fetch_array( $result ) ) {
212 $view = null;
213 $view['id'] = $row['id'];
214 $view['name'] = $row['name'];
215 $view['md5h'] = $row['md5h'];
216 $view['stoptriggerview'] = $row['stoptrigger'];
217 $view['fromMainAccount'] = true;
218
219 array_push( $views, $view );
220 }
221 }
222
223 return $views;
224 }
225
226
227 public function hasMainAccount( $accountMd5h ) {
228 $query = "select b.has_main_account as has_main_account from business b join account a on a.businessid = b.businessid where a.md5h = '$accountMd5h'";
229 $result = $this->querySingle( $query );
230 if ( $result['has_main_account'] ) {
231 return $result['has_main_account'] == "t";
232 } else {
233 return false;
234 }
235 }
236
237 public function getMainAccountContactStatuses( $accountMd5h ) {
238 $views = array();
239 $query = "SELECT cst.contact_status_id as id, cst.name, cst.md5h as md5h FROM contact_status cst JOIN account mainA ON mainA.accountid = cst.accountid JOIN account a ON a.businessid = mainA.businessid WHERE cst.statusid = 661 AND a.md5h = '$accountMd5h' AND mainA.main = TRUE ORDER BY cst.name";
240 $result = cache_aware_pg_query( $this->_database, $query );
241 if ( ( ! empty( $result ) ) && ( pg_numrows( $result ) > 0 ) ) {
242 while ( $row = pg_fetch_array( $result ) ) {
243 $view = null;
244 $view['id'] = $row['id'];
245 $view['name'] = $row['name'];
246 $view['md5h'] = $row['md5h'];
247 $view['fromMainAccount'] = true;
248
249 array_push( $views, $view );
250 }
251 }
252
253 return $views;
254 }
255
256 public function getMainAccountLabels( $accountMd5h ) {
257 $views = array();
258 $query = "SELECT cg.customer_group_id as id, cg.name, cg.md5h as md5h FROM customer_group cg JOIN account mainA ON mainA.accountid = cg.accountid JOIN account a ON a.businessid = mainA.businessid WHERE cg.statusid = 270 AND a.md5h = '$accountMd5h' AND a.accountid <> mainA.accountid AND mainA.main = TRUE ORDER BY cg.name";
259 $result = cache_aware_pg_query( $this->_database, $query );
260 if ( ( ! empty( $result ) ) && ( pg_numrows( $result ) > 0 ) ) {
261 while ( $row = pg_fetch_array( $result ) ) {
262 $view = null;
263 $view['id'] = $row['id'];
264 $view['title'] = $row['name'];
265 $view['md5h'] = $row['md5h'];
266 $view['fromMainAccount'] = true;
267
268 array_push( $views, $view );
269 }
270 }
271
272 return $views;
273 }
274
275 private function querySingle( $query ) {
276 $result = cache_aware_pg_query( $this->_database, $query );
277 if ( ( ! empty( $result ) ) && ( pg_numrows( $result ) > 0 ) ) {
278 $result = pg_fetch_array( $result );
279
280 return $result;
281 } else {
282 return null;
283 }
284 }
285
286 public function createViewFromGroup( $groupid ) {
287 $viewId = $this->createViewFromGroupReturnId( $groupid );
288
289 return $viewId > 0;
290 }
291
292 public function createViewFromGroupReturnId( $groupid ) {
293 $query =
294 "SELECT cg.name, a.md5h
295 FROM customer_group cg
296 JOIN account a ON cg.accountid = a.accountid
297 WHERE cg.customer_group_id = $groupid";
298 $result = cache_aware_pg_query( $this->_database, $query );
299 if ( ! empty( $result ) && pg_numrows( $result ) > 0 ) {
300 $result = pg_fetch_array( $result );
301
302 $view['accountid'] = $result['md5h'];
303 $view['name'] = $result['name'];
304 $view['defaultview'] = 'false';
305
306 $result = $this->createView( $view );
307
308 if ( $result['id'] > 0 ) {
309 $column['customersviewid'] = $result['id'];
310 $defaultColumns = array( 'Name', 'Email', 'Phone', 'Loyalty Points', 'Balance', 'Source' );
311 for ( $i = 0; $i < 6; $i ++ ) {
312 $column['columnname'] = $defaultColumns[ $i ];
313 $column['columnorder'] = $i;
314 $this->createColumn( $column );
315 }
316
317 $filter['customersviewid'] = $result['id'];
318 $filter['columnname'] = 'Label';
319 $filter['operatorname'] = 'equals';
320 $filter['value'] = $groupid;
321 $filter['accountid'] = $view['accountid'];
322 $filter['conditiontype'] = 580;
323 $this->createFilter( $filter );
324
325 return $result['id'];
326 }
327 }
328
329 return 0;
330 }
331
332 public function createView( $view ) {
333 if ( isset( $view['accountid'], $view['name'], $view['defaultview'] ) ) {
334 // get real ID from md5h
335 $view['accountid'] = $this->getAccountID( $view['accountid'] );
336 if ( $view['accountid'] == - 1 ) {
337 $result['id'] = - 1;
338
339 return $result;
340 }
341
342 // check if view with that name already exists
343 $query = "SELECT customersviewid
344 FROM customersview
345 WHERE triggerview = FALSE AND accountid = " . $view['accountid'] . " AND name = '" . pg_escape_string( stripslashes( $view['name'] ) ) .
346 "' AND statusid = " . $this->_statusid['active'];
347 if ( $this->querySingle( $query ) != null ) {
348 $result['id'] = - 2;
349
350 return $result;
351 }
352
353 // only one view can be default
354 if ( $view['defaultview'] == 'true' ) {
355 $query = "UPDATE customersview
356 SET defaultview = false
357 WHERE triggerview = FALSE AND defaultview = true AND accountid = " . $view['accountid'];
358 $this->querySingle( $query );
359 }
360
361 // insert view
362 $query = "INSERT INTO customersview (accountid, name, defaultview, statusid)
363 VALUES ({$view['accountid']}, '" . pg_escape_string( stripslashes( $view['name'] ) ) . "', {$view['defaultview']}, " . $this->_statusid['active'] . ")
364 RETURNING customersviewid as id";
365 $response = $this->querySingle( $query );
366
367 $this->_customersviewid = $response['id'];
368
369 // generate and add md5h
370 $viewMd5h = md5( $response['id'] . 'ReferrizViewJMZBA' );
371 $query = "UPDATE customersview
372 SET md5h='{$viewMd5h}'
373 WHERE customersviewid = $this->_customersviewid";
374 $this->querySingle( $query );
375
376 $result['id'] = $this->_customersviewid;
377 $result['md5h'] = $viewMd5h;
378
379 return $result;
380 } else {
381 $result['id'] = - 1;
382
383 return $result;
384 }
385 }
386
387 public function createColumn( $column ) {
388 if ( isset( $column['columnname'], $column['columnorder'] ) ) {
389 if ( ! isset( $column['customersviewid'] ) ) {
390 $column['customersviewid'] = $this->_customersviewid;
391 }
392
393 if ( array_key_exists( $column['columnname'], $this->_columnid ) ) {
394 $query = "INSERT INTO customersview_column (customersviewid, columnid, columnorder)
395 VALUES (" . $column['customersviewid'] . ", "
396 . $this->_columnid[ $column['columnname'] ] . ", "
397 . $column['columnorder'] . ")
398 RETURNING customersview_columnid as id";
399 } elseif ( isset( $column['accountid'] ) ) { // Custom field
400 $column['accountid'] = $this->getAccountID( $column['accountid'] );
401 if ( $column['accountid'] == - 1 ) {
402 return - 1;
403 }
404
405 $idQuery =
406 "SELECT users_field_id
407 FROM users_field
408 WHERE title = '" . $column['columnname'] . "' AND accountid = " . $column['accountid'];
409 $response = $this->querySingle( $idQuery );
410 $query = "INSERT INTO customersview_column (customersviewid, users_field_id, columnorder)
411 VALUES (" . $column['customersviewid'] . ", "
412 . $response['users_field_id'] . ", "
413 . $column['columnorder'] . ")
414 RETURNING customersview_columnid as id";
415 } else {
416 return - 2;
417 }
418
419
420 $response = $this->querySingle( $query );
421
422 return $response['id'];
423 } else {
424 return - 1;
425 }
426 }
427
428 public function createFilter( $filter ) {
429 if ( isset( $filter['columnname'], $filter['operatorname'] ) ) {
430 if ( ! isset( $filter['customersviewid'] ) ) {
431 $filter['customersviewid'] = $this->_customersviewid;
432 }
433
434 $query;
435 if ( array_key_exists( $filter['columnname'], $this->_columnid ) ) {
436
437 if ( isset( $filter['value'] ) ) {
438 $query = "INSERT INTO customersview_filter (customersviewid, columnid, operatorid, value, conditiontype)
439 VALUES (" . $filter['customersviewid'] . ", "
440 . $this->_columnid[ $filter['columnname'] ] . ", "
441 . $this->_operatorid[ $filter['operatorname'] ] . ", "
442 . "'" . $filter['value'] . "', "
443 . "" . $filter['conditiontype'] . ")
444 RETURNING customersview_filterid as id";
445 } else {
446 $query = "INSERT INTO customersview_filter (customersviewid, columnid, operatorid, conditiontype)
447 VALUES (" . $filter['customersviewid'] . ", "
448 . $this->_columnid[ $filter['columnname'] ] . ", "
449 . $this->_operatorid[ $filter['operatorname'] ] . ", "
450 . "" . $filter['conditiontype'] . ")
451 RETURNING customersview_filterid as id";
452 }
453 } elseif ( isset( $filter['accountid'] ) ) { // Custom field
454 $filter['accountid'] = $this->getAccountID( $filter['accountid'] );
455 if ( $filter['accountid'] == - 1 ) {
456 return - 1;
457 }
458
459 $idQuery =
460 "SELECT users_field_id
461 FROM users_field
462 WHERE title = '" . $filter['columnname'] . "' AND accountid = " . $filter['accountid'];
463 $response = $this->querySingle( $idQuery );
464 if (!isset($response)) {
465 $idQuery =
466 "SELECT uf.users_field_id
467 FROM users_field uf
468 JOIN account main_a ON main_a.accountid = uf.accountid AND main_a.main = TRUE
469 JOIN account a ON a.businessid = main_a.businessid AND a.accountid != main_a.accountid
470 JOIN business b ON b.businessid = a.businessid AND b.has_main_account = TRUE
471 WHERE a.accountid = {$filter['accountid']} AND title = '{$filter['columnname']}'";
472 $response = $this->querySingle( $idQuery );
473 }
474
475 if ( isset( $response ) ) {
476 $query = "INSERT INTO customersview_filter (customersviewid, users_field_id, operatorid, value, conditiontype)
477 VALUES (" . $filter['customersviewid'] . ", "
478 . $response['users_field_id'] . ", "
479 . $this->_operatorid[ $filter['operatorname'] ] . ", "
480 . "'" . $filter['value'] . "', "
481 . "" . $filter['conditiontype'] . ")
482 RETURNING customersview_filterid as id";
483 } else {
484 return - 2;
485 }
486 } else {
487 return - 3;
488 }
489
490 $response = $this->querySingle( $query );
491
492 return $response['id'];
493 } else {
494 return - 1;
495 }
496 }
497
498 public function createViewFromService( $serviceid ) {
499 $viewId = $this->createViewFromServiceReturnId( $serviceid );
500
501 return $viewId > 0;
502 }
503
504 public function createViewFromServiceReturnId( $serviceid ) {
505 $query =
506 "SELECT aas.title, a.md5h, aas.service_type_id
507 FROM account_app_service aas
508 JOIN account_app ap ON aas.account_app_id = ap.account_app_id
509 JOIN account a ON ap.accountid = a.accountid
510 WHERE aas.account_app_service_id = $serviceid";
511 $result = cache_aware_pg_query( $this->_database, $query );
512 if ( ! empty( $result ) && pg_numrows( $result ) > 0 ) {
513 $result = pg_fetch_array( $result );
514
515 $view['accountid'] = $result['md5h'];
516 $view['name'] = $result['title'];
517 $view['defaultview'] = 'false';
518
519 $serviceTypeID = $result['service_type_id'];
520 switch ( $serviceTypeID ) {
521 case 490:
522 $serviceFilter = 'Class';
523 break;
524 case 491:
525 $serviceFilter = 'Appointment';
526 break;
527 case 495:
528 $serviceFilter = 'Purchase';
529 break;
530 default:
531 $serviceFilter = 'Class';
532 }
533
534 $result = $this->createView( $view );
535
536 if ( $result['id'] > 0 ) {
537 $column['customersviewid'] = $result['id'];
538 $defaultColumns = array( 'Name', 'Email', 'Phone', 'Loyalty Points', 'Balance', 'Source' );
539 for ( $i = 0; $i < 6; $i ++ ) {
540 $column['columnname'] = $defaultColumns[ $i ];
541 $column['columnorder'] = $i;
542 $this->createColumn( $column );
543 }
544
545 $filter['customersviewid'] = $result['id'];
546 $filter['columnname'] = $serviceFilter;
547 $filter['operatorname'] = 'equals';
548 $filter['value'] = $serviceid;
549 $filter['accountid'] = $view['accountid'];
550 $filter['conditiontype'] = 580;
551 $this->createFilter( $filter );
552
553 return $result['id'];
554 }
555 }
556
557 return 0;
558 }
559
560 public function editView( $view ) {
561 if ( isset( $view['accountid'], $view['id'], $view['name'], $view['defaultview'] ) ) {
562 // get real ID from md5h
563 $view['accountid'] = $this->getAccountID( $view['accountid'] );
564 if ( $view['accountid'] == - 1 ) {
565 return - 1;
566 }
567
568 // Check view
569 $query = "SELECT name
570 FROM customersview
571 WHERE accountid = " . $view['accountid'] . " AND
572 customersviewid = " . $view['id'];
573 if ( $this->querySingle( $query ) == null ) {
574 return - 2;
575 }
576
577 // only one view can be default
578 if ( $view['defaultview'] == 'true' ) {
579 $query = "UPDATE customersview
580 SET defaultview = false
581 WHERE defaultview = true AND accountid = " . $view['accountid'];
582 $this->querySingle( $query );
583 }
584
585 // delete old columns
586 $query = "DELETE FROM customersview_column
587 WHERE customersviewid = " . $view['id'];
588 $this->querySingle( $query );
589
590 // delete old filters
591 $query = "DELETE FROM customersview_filter
592 WHERE customersviewid = " . $view['id'];
593 $this->querySingle( $query );
594
595 // update name and defaultview
596 $query = "UPDATE customersview
597 SET name = '" . addslashes( $view['name'] ) . "',
598 defaultview = " . $view['defaultview'] . "
599 WHERE customersviewid = " . $view['id'];
600 $this->querySingle( $query );
601
602 $this->_customersviewid = $view['id'];
603
604 return $view['id'];
605 } else {
606 return - 3;
607 }
608 }
609
610 public function editTrigger( $trigger ) {
611 if ( isset( $trigger['accountid'], $trigger['id'], $trigger['name'] ) ) {
612 // get real ID from md5h
613 $trigger['accountid'] = $this->getAccountID( $trigger['accountid'] );
614 if ( $trigger['accountid'] == - 1 ) {
615 return - 1;
616 }
617
618 // check if trigger with that name already exists in multiaccounts
619 $query = "SELECT cv.customersviewid as id FROM customersview cv JOIN account mainA ON mainA.accountid = cv.accountid AND mainA.main <> TRUE
620 JOIN account a ON a.businessid = mainA.businessid AND a.main = TRUE JOIN business b ON b.businessid = a.businessid
621 WHERE b.has_main_account = TRUE AND UPPER(cv.name) LIKE UPPER('".$trigger['name']."') AND a.accountid = " . $trigger['accountid'] .
622 " AND cv.statusid = " . $this->_statusid['active'];
623 if ( $this->querySingle( $query ) != null ) {
624 return -4;
625 }
626
627 $query = "UPDATE emailcampaign
628 SET automated_cancelled = FALSE
629 WHERE triggerviewid = " . $trigger['id'];
630 $this->querySingle( $query );
631
632 // Check view
633 $query = "SELECT name
634 FROM customersview
635 WHERE accountid = " . $trigger['accountid'] . " AND
636 customersviewid = " . $trigger['id'];
637 if ( $this->querySingle( $query ) == null ) {
638 return - 2;
639 }
640
641 // delete old filters
642 $query = "DELETE FROM customersview_filter
643 WHERE customersviewid = " . $trigger['id'];
644 $this->querySingle( $query );
645
646 // update name
647 $query = "UPDATE customersview
648 SET name = '" . addslashes( $trigger['name'] ) . "'
649 WHERE customersviewid = " . $trigger['id'];
650 $this->querySingle( $query );
651
652 $this->_customersviewid = $trigger['id'];
653
654 return $trigger['id'];
655 } else {
656 return - 3;
657 }
658 }
659
660 public function deleteView( $view ) {
661 if ( isset( $view['accountid'], $view['id'] ) ) {
662 // get real ID from md5h
663 $view['accountid'] = $this->getAccountID( $view['accountid'] );
664 if ( $view['accountid'] == - 1 ) {
665 return - 1;
666 }
667
668
669 // Check view
670 $query = "SELECT name
671 FROM customersview
672 WHERE accountid = " . $view['accountid'] . " AND
673 customersviewid = " . $view['id'];
674 if ( $this->querySingle( $query ) == null ) {
675 return - 2;
676 }
677
678 // delete columns
679 $query = "DELETE FROM customersview_column
680 WHERE customersviewid = " . $view['id'];
681 $this->querySingle( $query );
682
683 // delete filters
684 $query = "DELETE FROM customersview_filter
685 WHERE customersviewid = " . $view['id'];
686 $this->querySingle( $query );
687
688 // delete view
689 $query = "UPDATE customersview
690 SET defaultview = FALSE
691 WHERE customersviewid = " . $view['id'];
692 $this->querySingle( $query );
693
694 $query = "UPDATE customersview
695 SET statusid = 321
696 WHERE customersviewid = " . $view['id'];
697 $this->querySingle( $query );
698
699 return $view['id'];
700 } else {
701 return - 3;
702 }
703 }
704
705 public function getDefaultViewID( $accountMd5h ) {
706 $accountID = $this->getAccountID( $accountMd5h );
707 if ( $accountID == - 1 ) {
708 return - 1;
709 }
710
711 $query = "SELECT customersviewid as id
712 FROM customersview
713 WHERE accountid = $accountID AND defaultview = TRUE";
714 $result = $this->querySingle( $query );
715 if ( $result != null ) {
716 return $result['id'];
717 } else {
718 return - 1;
719 }
720 }
721
722 public function getViews( $accountMd5h ) {
723 $accountID = $this->getAccountID( $accountMd5h );
724 if ( $accountID == - 1 ) {
725 return - 1;
726 }
727 $views = array();
728
729 if($this->hasMainAccount($accountMd5h)) {
730 $views = $this->getMainAccountViews($accountMd5h);
731 }
732
733
734 $query = "SELECT customersviewid as id, name, defaultview, md5h, customer_count
735 FROM customersview
736 WHERE triggerview = FALSE AND accountid = $accountID AND statusid = {$this->_statusid['active']}
737 ORDER BY name";
738 $result = $this->query( $query );
739 if ( $result == null ) {
740 return $views;
741 }
742 foreach ( $result as $row ) {
743 $view = null;
744 $view['id'] = $row['id'];
745 $view['name'] = $row['name'];
746 $view['md5h'] = $row['md5h'];
747 $view['customerCount'] = $row['customer_count'];
748 $view['fromMainAccount'] = false;
749
750 array_push( $views, $view );
751 }
752
753 return $views;
754 }
755
756 private function query( $query ) {
757 $resultArray = array();
758 $result = cache_aware_pg_query( $this->_database, $query );
759 if ( ( ! empty( $result ) ) && ( pg_numrows( $result ) > 0 ) ) {
760 while ( $row = pg_fetch_array( $result ) ) {
761 array_push( $resultArray, $row );
762 }
763
764 return $resultArray;
765 } else {
766 return null;
767 }
768 }
769
770 public function getTriggers( $accountMd5h ) {
771 $accountID = $this->getAccountID( $accountMd5h );
772 if ( $accountID == - 1 ) {
773 return - 1;
774 }
775
776 $triggers = array();
777
778 if($this->hasMainAccount($accountMd5h)) {
779 $triggers = $this->getMainAccountTriggers($accountMd5h);
780 }
781
782 $query = "SELECT customersviewid as id, name, md5h, stoptriggerview
783 FROM customersview
784 WHERE triggerview = TRUE AND accountid = $accountID AND statusid = {$this->_statusid['active']}
785 ORDER BY name";
786 $result = $this->query( $query );
787 if ( $result == null ) {
788 return $triggers;
789 }
790 foreach ( $result as $row ) {
791 $trigger = null;
792 $trigger['id'] = $row['id'];
793 $trigger['name'] = $row['name'];
794 $trigger['md5h'] = $row['md5h'];
795 $trigger['stoptriggerview'] = $row['stoptriggerview'];
796 $trigger['fromMainAccount'] = false;
797
798 array_push( $triggers, $trigger );
799 }
800
801 return $triggers;
802 }
803
804 public function isAccountTsk($accountMd5h){
805 $query = "select b.md5h from business b join account a on a.businessid = b.businessid where a.md5h = '$accountMd5h'";
806 $result = $this->querySingle( $query );
807 if ( $result['md5h'] == $this::TSK_BUSINESS_HASH) {
808 return true;
809 } else {
810 return false;
811 }
812
813 }
814
815 public function isAccountMbo($accountMd5h){
816 $query = "select * from account_app aa join account a on a.accountid=aa.accountid where aa.app_id = 300 and a.md5h = '$accountMd5h'";
817 $result = $this->querySingle( $query );
818 if ( $result['account_app_id']) {
819 return true;
820 } else {
821 return false;
822 }
823
824 }
825
826 public function getVouchers( $accountMd5h ) {
827 $accountID = $this->getAccountID( $accountMd5h );
828 if ( $accountID == - 1 ) {
829 return - 1;
830 }
831
832 $query = " SELECT distinct * FROM voucher v
833 LEFT JOIN VoucherAccount va ON v.voucherid = va.voucherid
834 LEFT JOIN Account a ON va.accountid = a.accountid
835 WHERE a.accountid = $accountID AND va.statusid = 52 AND (SELECT count(i) FROM Image i WHERE i.voucherid = va.voucherid) > 0 AND v.statusid IN (12, 13)";
836
837
838 $result = $this->query( $query );
839 $vouchers = array();
840 if ( $result == null ) {
841 return null;
842 }
843 foreach ( $result as $row ) {
844 $voucher = null;
845 $voucher['id'] = $row['voucherid'];
846 $voucher['title'] = $row['vouchertitle'];
847
848 array_push( $vouchers, $voucher );
849 }
850
851 return $vouchers;
852 }
853
854 public function getServices( $accountMd5h, $serviceTypeId = null ) {
855 $accountID = $this->getAccountID( $accountMd5h );
856 if ( $accountID == - 1 ) {
857 return - 1;
858 }
859
860 $query = "SELECT aps.account_app_service_id as id, aps.title
861 FROM account_app ap
862 JOIN account_app_service aps on ap.account_app_id = aps.account_app_id
863 WHERE ap.accountid = $accountID AND aps.statusid = 272";
864
865 if ( isset( $serviceTypeId ) ) {
866 $query .= " AND service_type_id = $serviceTypeId";
867 }
868
869 $result = $this->query( $query );
870 $services = array();
871 if ( $result == null ) {
872 return null;
873 }
874 foreach ( $result as $row ) {
875 $service = null;
876 $service['id'] = $row['id'];
877 $service['title'] = $row['title'];
878
879 array_push( $services, $service );
880 }
881
882 return $services;
883 }
884
885 public function getTskOptions($tskContractOption){
886 $query = "select id, value from tsk_contract_option where field_name = '$tskContractOption'";
887 $result = $this->query( $query );
888 $options = array();
889 if ( $result == null ) {
890 return null;
891 }
892 foreach ( $result as $row ) {
893 $option = null;
894 $option['id'] = $row['id'];
895 $option['title'] = $row['value'];
896
897 array_push( $options, $option );
898 }
899 return $options;
900 }
901
902 public function getContactStatuses($accountMd5h) {
903 $accountID = $this->getAccountID( $accountMd5h );
904 if ( $accountID == - 1 ) {
905 return - 1;
906 }
907
908 $contactStatuses = array();
909
910 if($this->hasMainAccount($accountMd5h)) {
911 $contactStatuses = $this->getMainAccountContactStatuses($accountMd5h);
912 $query = "SELECT cst.name, cst.contact_status_id
913 FROM contact_status cst
914 WHERE cst.accountid = $accountID and cst.statusid=661 and cst.is_default = false";
915 $result = $this->query( $query );
916 if ( $result == null ) {
917 return $contactStatuses;
918 }
919 }
920
921 $query = "SELECT cst.name, cst.contact_status_id
922 FROM contact_status cst
923 WHERE cst.accountid = $accountID and cst.statusid=661";
924
925 $result = $this->query( $query );
926 if ( $result == null ) {
927 return null;
928 }
929 foreach ( $result as $row ) {
930 $contactSt = null;
931 $contactSt['id'] = $row['contact_status_id'];
932 $contactSt['name'] = $row['name'];
933 $contactSt['fromMainAccount'] = false;
934 array_push( $contactStatuses, $contactSt );
935 }
936
937
938 return $contactStatuses;
939 }
940
941 public function getLabels( $accountMd5h ) {
942 $accountID = $this->getAccountID( $accountMd5h );
943 if ( $accountID == - 1 ) {
944 return - 1;
945 }
946
947 $labels = array();
948
949 if($this->hasMainAccount($accountMd5h)) {
950 $labels = $this->getMainAccountLabels($accountMd5h);
951 }
952
953 $query = "SELECT customer_group_id as id, name as title, color
954 FROM customer_group
955 WHERE accountid = $accountID AND statusid = 270";
956 $result = $this->query( $query );
957 if ( $result == null ) {
958 return null;
959 }
960 foreach ( $result as $row ) {
961 $label = null;
962 $label['id'] = $row['id'];
963 $label['title'] = $row['title'];
964 $label['color'] = $row['color'];
965 $label['fromMainAccount'] = false;
966 if ( empty( $label['color'] ) ) {
967 $label['color'] = $this->getLabelColorByName( $label['title'] );
968 }
969
970 array_push( $labels, $label );
971 }
972
973 return $labels;
974 }
975
976 public function getLabelColorByName( $name ) {
977 $colors = array(
978 'red',
979 'pink',
980 'purple',
981 'deep_purple',
982 'indigo',
983 'light_blue',
984 'cyan',
985 'teal',
986 'lime',
987 'amber',
988 'orange',
989 'deep_orange',
990 'brown',
991 'grey',
992 'blue_grey'
993 );
994 $colorNum = abs( hexdec( substr( sha1( $name ), 0, 15 ) ) % count( $colors ) );
995
996 return $colors[ $colorNum ];
997 }
998
999 public function getCustomFields( $accountMd5h ) {
1000 $accountID = $this->getAccountID( $accountMd5h );
1001 if ( $accountID == - 1 ) {
1002 return - 1;
1003 }
1004
1005 $fields = array();
1006 $query = "SELECT md5h as id, title, field_type
1007 FROM users_field
1008 WHERE accountid = $accountID AND statusid = 350 AND source != 902";
1009 $result = $this->query( $query );
1010 if ( $result == null ) {
1011 return null;
1012 }
1013 foreach ( $result as $row ) {
1014 $field = null;
1015 $field['id'] = $row['id'];
1016 $field['title'] = $row['title'];
1017 $field['fieldType'] = $row['field_type'];
1018
1019 array_push( $fields, $field );
1020 }
1021
1022 $query = "SELECT uf.md5h as id, uf.title, uf.field_type
1023 FROM users_field uf
1024 JOIN account main_a ON main_a.accountid = uf.accountid AND main_a.main = TRUE
1025 JOIN account a ON a.businessid = main_a.businessid AND a.accountid != main_a.accountid
1026 JOIN business b ON b.businessid = a.businessid AND b.has_main_account = TRUE
1027 WHERE a.accountid = $accountID AND uf.statusid = 350 AND uf.source != 902";
1028 $result = $this->query( $query );
1029 if ( $result == null ) {
1030 return $fields;
1031 }
1032 foreach ( $result as $row ) {
1033 $field = null;
1034 $field['id'] = $row['id'];
1035 $field['title'] = $row['title'];
1036 $field['fieldType'] = $row['field_type'];
1037
1038 array_push( $fields, $field );
1039 }
1040
1041 return $fields;
1042 }
1043
1044 public function isContactStatusDeleted ($value) {
1045
1046 $query = "SELECT cst.name
1047 FROM customersview_filter f
1048 JOIN contact_status cst ON f.value::integer = cst.contact_status_id
1049 WHERE f.columnid = 576 and f.value::integer = $value AND cst.statusid = 662;";
1050
1051 $result = $this->query( $query );
1052
1053 if ( $result == null ) {
1054 $isDeleted = false;
1055 } else {
1056 $isDeleted = true;
1057 }
1058
1059 return $isDeleted;
1060 }
1061
1062 public function getFilters( $viewid ) {
1063 $query = "SELECT f.customersview_filterid as id,
1064 CASE WHEN f.columnid IS NULL THEN uf.title ELSE oc.value END AS column,
1065 oo.value as operator,
1066 f.value, f.conditiontype
1067 FROM customersview_filter f
1068 LEFT JOIN option oc ON f.columnid = oc.optionid
1069 LEFT JOIN option oo ON f.operatorid = oo.optionid
1070 LEFT JOIN users_field uf ON uf.users_field_id = f.users_field_id
1071 WHERE f.customersviewid = $viewid";
1072
1073 $response = $this->query( $query );
1074 $filters = array();
1075 if ( is_array( $response ) || is_object( $response ) ) {
1076 foreach ( $response as $r ) {
1077 $filter = null;
1078 $filter['id'] = $r['id'];
1079 $filter['column'] = $r['column'];
1080 $filter['operator'] = $r['operator'];
1081 $filter['value'] = $r['value'];
1082 $filter['conditiontype'] = $r['conditiontype'];
1083 $filter['isDeleted'] = false;
1084
1085 if($r['column'] == 'Contact Status') {
1086 $isDeleted = $this->isContactStatusDeleted( $filter['value'] );
1087 $filter['isDeleted'] = $isDeleted;
1088 }
1089//
1090// if($r['column'] == 'Contact Status') {
1091// $isDeleted = $this->isContactStatusDeleted( $filter['value'] );
1092// $filter['isDeleted'] = $isDeleted;
1093// }
1094
1095
1096 array_push( $filters, $filter );
1097 }
1098 }
1099
1100 return $filters;
1101 }
1102
1103
1104 public function isDefault( $viewid ) {
1105 $query = "SELECT defaultview
1106 FROM customersview
1107 WHERE customersviewid = $viewid";
1108
1109 $response = $this->querySingle( $query );
1110
1111 return $response['defaultview'] == 't';
1112 }
1113
1114 public function createLabel( $accountId, $name, $color = null ) {
1115 // get real ID from md5h
1116 $accountId = $this->getAccountID( $accountId );
1117 if ( $accountId == - 1 ) {
1118 return - 1;
1119 }
1120
1121 // check if label with that name already exists
1122 $query = "SELECT customer_group_id
1123 FROM customer_group
1124 WHERE accountid = " . $accountId . " AND name = '" . $name .
1125 "' AND statusid = 270";
1126 if ( $this->querySingle( $query ) != null ) {
1127 return - 2;
1128 }
1129
1130 $sort = 1;
1131 $query = "SELECT MAX(sort) AS sort FROM customer_group WHERE statusid = 270 AND accountid = $accountId";
1132 $response = $this->querySingle( $query );
1133 if ( isset( $response ) && isset( $response['sort'] ) ) {
1134 $sort += (int) $response['sort'];
1135 }
1136
1137 if ( ! isset( $color ) ) {
1138 $color = $this->getLabelColorByName( $name );
1139 }
1140
1141 // insert label
1142 $query = "INSERT INTO customer_group (name, statusid, sort, accountid, color) VALUES ('$name', 270, $sort, $accountId, '$color') RETURNING customer_group_id as id";
1143 $response = $this->querySingle( $query );
1144
1145 $labelId = $response['id'];
1146
1147 // generate and add md5h
1148 $labelMd5h = md5( $labelId . 'ReferrizGroupJMZBA' );
1149 $query = "UPDATE customer_group
1150 SET md5h='{$labelMd5h}'
1151 WHERE customer_group_id = {$labelId}";
1152 $this->querySingle( $query );
1153
1154 return $labelId;
1155 }
1156
1157 public function addLabelToCustomers( $labelId, $customersMd5hs ) {
1158 $query = "SELECT customerid FROM customer WHERE md5h IN " . $this->to_pg_array( $customersMd5hs );
1159 $response = $this->query( $query );
1160 $customersIds = array();
1161 if ( is_array( $response ) || is_object( $response ) ) {
1162 foreach ( $response as $r ) {
1163 array_push( $customersIds, $r['customerid'] );
1164 }
1165 }
1166
1167 $query = "SELECT customerid FROM customer_customergroup WHERE customer_group_id = {$labelId} AND customerid IN " . $this->to_pg_array( $customersIds );
1168 $response = $this->query( $query );
1169 $customersIdsAlreadyHasLabel = array();
1170 if ( is_array( $response ) || is_object( $response ) ) {
1171 foreach ( $response as $r ) {
1172 array_push( $customersIdsAlreadyHasLabel, $r['customerid'] );
1173 }
1174 }
1175
1176 $customersIds = array_diff( $customersIds, $customersIdsAlreadyHasLabel );
1177
1178 if ( count( $customersIds ) > 0 ) {
1179 $query = "INSERT INTO customer_customergroup (customerid, customer_group_id) VALUES";
1180 foreach ( $customersIds as $customerId ) {
1181 $query .= "({$customerId}, {$labelId}),";
1182 }
1183 $query = rtrim( $query, "," );
1184
1185 $this->querySingle( $query );
1186 }
1187
1188 return count( $customersIds );
1189 }
1190
1191 public function to_pg_array( $set ) {
1192 if ( is_null( $set ) || ! is_array( $set ) ) {
1193 return 'NULL';
1194 }
1195
1196 // can be called with a scalar or array
1197 settype( $set, 'array' );
1198
1199 $result = array();
1200 foreach ( $set as $t ) {
1201 // Element is array : recursion
1202 if ( is_array( $t ) ) {
1203 $result[] = to_pg_array( $t );
1204 } else {
1205 // PHP NULL
1206 if ( is_null( $t ) ) {
1207 $result[] = 'NULL';
1208 } // PHP TRUE::boolean
1209 elseif ( is_bool( $t ) && $t == true ) {
1210 $result[] = 'TRUE';
1211 } // PHP FALSE::boolean
1212 elseif ( is_bool( $t ) && $t == false ) {
1213 $result[] = 'FALSE';
1214 } // Other scalar value
1215 else {
1216 // Escape
1217 $t = pg_escape_string( $t );
1218
1219 // quote only non-numeric values
1220 if ( ! is_numeric( $t ) ) {
1221 $t = '\'' . $t . '\'';
1222 }
1223 $result[] = $t;
1224 }
1225 }
1226 }
1227
1228 return '(' . implode( ",", $result ) . ')'; // format
1229 }
1230
1231 public function removeLabelFromCustomers( $labelId, $customersMd5hs ) {
1232 $query = "SELECT customerid FROM customer WHERE md5h IN " . $this->to_pg_array( $customersMd5hs );
1233 $response = $this->query( $query );
1234 $customersIds = array();
1235 if ( is_array( $response ) || is_object( $response ) ) {
1236 foreach ( $response as $r ) {
1237 array_push( $customersIds, $r['customerid'] );
1238 }
1239 }
1240
1241
1242 if ( count( $customersIds ) > 0 ) {
1243 $query = "DELETE FROM customer_customergroup WHERE customer_group_id = {$labelId} AND customerid IN " . $this->to_pg_array( $customersIds );
1244 $this->querySingle( $query );
1245 }
1246
1247 return count( $customersIds );
1248 }
1249
1250 public function getCustomersCount( $accountMd5h, $viewid ) {
1251 return $this->getCustomers2( $accountMd5h, $viewid, 'count' );
1252 }
1253
1254 public function export($businessId, $accountId, $customerIds) {
1255 if (empty($customerIds)) {
1256 return array();
1257 }
1258
1259 $customerIds = array_map(function($id) {
1260 return "'" . $id . "'";
1261 }, $customerIds);
1262 $customerIds = implode(", ", $customerIds);
1263 $query = "SELECT t.firstname as \"First Name\",
1264 t.lastname as \"Last Name\",
1265 t.md5h,
1266 t.email as \"Email Raw\",
1267 t.address as \"Address\",
1268 t.city as \"City\",
1269 t.zip as \"Zip\",
1270 t.phone as \"Phone Raw\",
1271 t.birthday as \"Date of Birth\",
1272 t.uniquemboid as \"MBO ID\",
1273 t.fromsystem as fromSystem,
1274 CASE
1275 WHEN t.emailunsubcribeid is NULL
1276 THEN false
1277 ELSE true
1278 END as \"Unsubscribed Email\",
1279 CASE
1280 WHEN t.emailunsubcribeid2 is NULL
1281 THEN false
1282 ELSE true
1283 END as \"Unsubscribed Phone\",
1284 t.loyaltypoints as \"Loyalty Points\",
1285 t.balance as \"Balance\",
1286 t.registrationtypeid as registrationTypeId,
1287 t.lastvisit as \"Last Visit\",
1288 t.source as \"Source\"
1289FROM (
1290 SELECT DISTINCT ON (c.customerid) u.firstname AS firstname,
1291 u.lastname AS lastname,
1292 e.email AS email,
1293 u.address AS address,
1294 u.city AS city,
1295 u.zip AS zip,
1296 u.phone AS phone,
1297 u.birthday AS birthday,
1298 um.unique_mbo_id AS uniquemboid,
1299 eu.fromsystem AS fromsystem,
1300 eu2.emailunsubcribeid AS emailunsubcribeid2,
1301 eu.emailunsubcribeid AS emailunsubcribeid,
1302 (COALESCE(count(DISTINCT cv.customervisitsid), 0) +
1303 COALESCE(loyaltypoints.loyaltypoints, 0) -
1304 COALESCE(usedloyalty.usedloyalty, 0)) AS
1305 loyaltypoints,
1306 (COALESCE(credit.store_credit_plus, 0) -
1307 COALESCE(credit.store_credit_minus, 0)) AS balance,
1308 c.registrationtypeid AS registrationtypeid,
1309 c.last_visit AS lastvisit,
1310 c.md5h,
1311 source.name AS source
1312 FROM customer c
1313 LEFT JOIN customervisits cv ON (c.customerid = cv.customerid AND cv.point_given = true)
1314 LEFT JOIN (SELECT DISTINCT ON (customerid) customerid,
1315 SUM(lrp.loyaltypoints) AS loyaltypoints
1316 FROM loyaltyrewardpoints lrp
1317 group by customerid) loyaltypoints ON (c.customerid = loyaltypoints.customerid)
1318 LEFT JOIN (SELECT DISTINCT ON (customerid) customerid,
1319 SUM(lrt.loyaltypoints) AS usedloyalty
1320 FROM loyaltyrewardtransaction lrt
1321 group by customerid) usedloyalty ON (c.customerid = usedloyalty.customerid)
1322 INNER JOIN email e ON (e.userid = c.userid)
1323 INNER JOIN users u ON (u.userid = c.userid)
1324 LEFT JOIN account_user_store_credit credit
1325 ON (u.userid = credit.userid AND credit.accountid = c.accountid)
1326 LEFT JOIN registrationtype source ON (c.registrationtypeid = source.registrationtypeid)
1327 LEFT JOIN emailunsubscribe eu ON (eu.userid = u.userid AND
1328 (eu.fromsystem = true OR eu.frombusinessid = $businessId AND eu.uns_email =
1329 true))
1330 LEFT JOIN emailunsubscribe eu2
1331 ON (eu2.userid = u.userid AND (eu2.fromsystem = true OR eu2.frombusinessid = $businessId AND
1332 eu2.uns_phone = true))
1333 LEFT JOIN user_mbo um
1334 ON (um.userid = c.userid AND um.accountid = c.accountid AND um.unique_mbo_id <> '')
1335 LEFT JOIN city ci ON (ci.cityid = u.cityid)
1336 WHERE c.accountid = $accountId
1337 AND c.md5h in ($customerIds)
1338 GROUP BY c.customerid, u.firstname, u.lastname, e.email, u.address, u.city, u.zip, u.phone, u.birthday,
1339 um.unique_mbo_id, eu.fromsystem,
1340 eu.emailunsubcribeid, eu2.emailunsubcribeid, loyaltypoints.loyaltypoints, usedloyalty.usedloyalty,
1341 credit.store_credit_plus, credit
1342 .store_credit_minus,
1343 c.registrationtypeid, c.last_visit, source.registrationtypeid
1344 ORDER BY c.customerid
1345 ) t
1346ORDER BY firstName, lastName, email";
1347
1348 $result = cache_aware_pg_query( $this->_database, $query );
1349 $data = array();
1350 if ( ( ! empty( $result ) ) && ( pg_numrows( $result ) > 0 ) ) {
1351 while ($row = pg_fetch_array($result)) {
1352 $row['Unsubscribed Email'] = $row['Unsubscribed Email'] === 'f' ? 'false' : 'true';
1353 $row['Unsubscribed Phone'] = $row['Unsubscribed Phone'] === 'f' ? 'false' : 'true';
1354 array_push($data, $row);
1355 }
1356 }
1357
1358 return $data;
1359 }
1360
1361 public function getCustomers2( $accountMd5h, $viewid, $fields = null, $dataTablesGET = null, $additionalConditions = null, $readOnly = null, $ifFreemium = null, $masterLevel = null, $jsonResponse = false, $customerId = null ) {
1362 $logger = LoggerSingleton::getInstance();
1363 $accountID = AccountService::getAccountIdByMd5h($accountMd5h);
1364
1365 $accountTimezone = AccountService::getAccountTimezone($accountID);
1366 $timezoneDeviationInSeconds = TimezoneHelper::getTimezoneOffsetInSeconds('America/New_York', $accountTimezone['timezone_id']);
1367
1368 $viewid = urldecode($viewid);
1369 //sleep(3);
1370 // constants
1371 $today = date( 'Y-m-d H:i:s', time() );
1372 $before7days = date( 'Y-m-d H:i:s', strtotime( "- 7 day" ) );
1373 $before30days = date( 'Y-m-d H:i:s', strtotime( "- 30 day" ) );
1374 $before60days = date( 'Y-m-d H:i:s', strtotime( "- 60 day" ) );
1375 $before90days = date( 'Y-m-d H:i:s', strtotime( "- 90 day" ) );
1376 $next7days = date( 'Y-m-d H:i:s', strtotime( "+ 7 day" ) );
1377
1378 $INVALID_EMAIL_TOOLTIP = ' <i class="icon-warning-sign questiontooltip" data-toggle="tooltip" data-placement="right" title="Invalid email address." style="color:#da4f49;"></i></p>';
1379
1380 // vars
1381 $whereQuery = '';
1382 $joinQuery = '';
1383 $selectQuery = '';
1384 $groupByQuery = '';
1385 $havingQuery = '';
1386 $orderByQuery = '';
1387 $limitQuery = '';
1388
1389 $customColumnCount = 0;
1390
1391 $defaultViews = array();
1392 $defaultViews['Current Customers'] = array(
1393 'Name',
1394 'Phone',
1395 'Email',
1396 'Source',
1397 'Balance',
1398 'Last Visit',
1399 'Loyalty Points'
1400 );
1401 $defaultViews['All'] = $defaultViews['Current Customers'];
1402 $defaultViews['Referred'] = array(
1403 'Registration Date',
1404 'Referred Customer',
1405 'Referred by',
1406 'Status'
1407 );
1408 $defaultViews['Referred to partner'] = array(
1409 'Registration Date',
1410 'Name',
1411 'Referred To',
1412 'Status'
1413 );
1414 $defaultViews['Past Customers'] = $defaultViews['Current Customers'];
1415 $defaultViews['Other'] = $defaultViews['Current Customers'];
1416 $defaultViews['Leads'] = array(
1417 'Name',
1418 'Phone',
1419 'Email',
1420 'Offers',
1421 'Source',
1422 'Balance',
1423 'Registration Date',
1424 'Loyalty Points'
1425 );
1426
1427 // Validate IDs
1428 if ( array_key_exists( $viewid, $defaultViews ) ) { // One of default views
1429 $query = "SELECT accountid
1430 FROM account
1431 WHERE md5h = '$accountMd5h'";
1432 } else { // Custom view
1433 $query = "SELECT a.accountid
1434 FROM customersview cv
1435 JOIN account a ON a.accountid = cv.accountid
1436 WHERE a.md5h = '$accountMd5h' AND cv.customersviewid = $viewid";
1437 }
1438 $result = cache_aware_pg_query( $this->_database, $query );
1439 if ( ( ! empty( $result ) ) && ( pg_numrows( $result ) > 0 ) ) {
1440 $result = pg_fetch_array( $result );
1441 $accountID = $result['accountid'];
1442 } else {
1443 $query = "SELECT a.accountid FROM customersview cv JOIN account mainA ON mainA.accountid = cv.accountid JOIN account a ON a.businessid = mainA.businessid WHERE cv.statusid = 320 AND a.md5h = '$accountMd5h' AND mainA.main = TRUE AND cv.customersviewid = $viewid";
1444 $result = cache_aware_pg_query( $this->_database, $query );
1445 if ( ( ! empty( $result ) ) && ( pg_numrows( $result ) > 0 ) ) {
1446 $result = pg_fetch_array( $result );
1447 $accountID = $result['accountid'];
1448 } else {
1449 return false;
1450 }
1451 }
1452
1453
1454 $include = array();
1455 $include['ID'] = false;
1456 $include['Md5h'] = false;
1457 $include['Name'] = false;
1458 $include['First Name'] = false;
1459 $include['Last Name'] = false;
1460 $include['Email'] = false;
1461 $include['Phone'] = false;
1462 $include['Source'] = false;
1463 $include['Balance'] = false;
1464 $include['Loyalty Points'] = false;
1465 $include['Last Visit'] = false;
1466 $include['Loyalty Points'] = false;
1467 $include['Offers'] = false;
1468 $include['Registration Date'] = false;
1469 $include['Type'] = false;
1470 $include['Label'] = false;
1471 $include['Visits'] = false;
1472 $include['First Visit'] = false;
1473 $include['Class'] = false;
1474 $include['Appointment'] = false;
1475 $include['Service'] = false;
1476 $include['Purchase'] = false;
1477 $include['Age'] = false;
1478 $include['Zip'] = false;
1479 $include['Birthday'] = false;
1480 $include['Email Unsubscribed'] = false;
1481 $include['Text Unsubscribed'] = false;
1482 $include['Last Purchased Service/Product'] = false;
1483 $include['Last Purchased Date'] = false;
1484 $include['Text Reply Received'] = false;
1485 $include['Contact Status'] = false;
1486 $include['Last Modified Date'] = false;
1487 $include['Contract Expiration Date'] = false;
1488 $include['Contract Purchase Date'] = false;
1489 $include['Contract Payment Due Date'] = false;
1490 $include['Contract Program Type'] = false;
1491 $include['Contract Payment Plan'] = false;
1492 $include['Contract Discount Type'] = false;
1493 $include['Contract Age Group'] = false;
1494 $include['Last Attendance Status'] = false;
1495 $include['Mindbody Member'] = false;
1496 $include['Gender'] = false;
1497 $include['Last Purchased Service/Product'] = false;
1498 $include['Claimed Offer'] = false;
1499
1500
1501
1502 if ( isset( $fields ) && is_array( $fields ) ) {
1503 foreach ( $fields as $field ) {
1504 $include[ $field ] = true;
1505 }
1506 }
1507
1508 $businessQuery = "SELECT a.businessid, bo.multi_location_loyalty::int
1509 FROM account a
1510 JOIN businessoption bo ON bo.businessid = a.businessid
1511 WHERE a.accountid = $accountID";
1512 $response = $this->querySingle( $businessQuery );
1513 $businessID = $response['businessid'];
1514 $multiLocationLoyalty = (bool) $response['multi_location_loyalty'];
1515
1516 // Pagination
1517 if ( isset( $dataTablesGET ) ) {
1518 if ( isset( $dataTablesGET['iDisplayStart'] ) && $dataTablesGET['iDisplayLength'] != '-1' ) {
1519 $limitQuery = " LIMIT " . intval( $dataTablesGET['iDisplayLength'] ) . " OFFSET " .
1520 intval( $dataTablesGET['iDisplayStart'] );
1521 }
1522 }
1523
1524 if ( array_key_exists( $viewid, $defaultViews ) ) {
1525 $columns = array();
1526 if ( $fields != 'count' ) {
1527 foreach ( $defaultViews[ $viewid ] as $column ) {
1528 if ( array_key_exists( $column, $include ) && $include[ $column ] === false ) {
1529 $include[ $column ] = true;
1530 $c['name'] = $column;
1531 array_push( $columns, $c );
1532 }
1533 }
1534 }
1535
1536 if ($viewid === 'Referred to partner') {
1537 foreach ( $defaultViews[ $viewid ] as $column ) {
1538 $c['name'] = $column;
1539 array_push( $columns, $c );
1540 }
1541 $orderByQuery .= "ORDER BY ";
1542 if ( isset( $dataTablesGET['iSortCol_0'] ) ) {
1543 $sortColumnIndex = $dataTablesGET['iSortCol_0'] > 0 ? $dataTablesGET['iSortCol_0'] - 1 : 0;
1544 switch ( $columns[ $sortColumnIndex ]['name'] ) {
1545 case 'Registration Date':
1546 $orderByQuery .= "date";
1547 break;
1548 case 'Name':
1549 $orderByQuery .= "full_name";
1550 break;
1551 case 'Referred To':
1552 $orderByQuery .= "referred_to";
1553 break;
1554 case 'Status':
1555 $orderByQuery .= "status";
1556 break;
1557 default:
1558 $orderByQuery .= "id DESC";
1559
1560 }
1561 }
1562 if ( $orderByQuery != "ORDER BY " ) {
1563 $orderByQuery .= " " . $dataTablesGET['sSortDir_0'] . " NULLS LAST ";
1564 } else {
1565 $orderByQuery = "";
1566 }
1567
1568 $searchQuery = "";
1569 if ( isset( $dataTablesGET['sSearch'] ) && $dataTablesGET['sSearch'] != "" ) {
1570 $searchString = strtolower(pg_escape_string(urldecode(trim($dataTablesGET['sSearch']))));
1571 $dontHaveLetters = strlen(preg_replace("/[^a-zA-Z]/", "", urldecode($dataTablesGET['sSearch']))) == 0;
1572 $searchQuery = " AND ";
1573 $searchQuery .= "(lower(concat_ws(' ', trim(u.firstname), trim(u.middlename), trim(u.lastname))) like ('%" . $searchString . "%') OR ";
1574 if($dontHaveLetters) { $searchQuery .= "lower(u.phone) like ('%" . preg_replace("/[^0-9]/", "", "$searchString") . "%') OR ";}
1575 $searchQuery .= "lower(a.shoptitle) like ('%" . $searchString . "%') ";
1576 $searchQuery .= " )";
1577 }
1578
1579
1580
1581 $_countQuery = "SELECT count(distinct i.friendid) as number
1582FROM invite i
1583left join account a on i.accountid = a.accountid
1584left join users u on i.friendid = u.userid
1585left join customer c on c.userid = u.userid and c.accountid = i.inviter_accountid
1586WHERE i.statusid IN (43, 44)
1587 AND i.inviter_accountid = $accountID
1588 AND i.claimSource = 404
1589 AND c.statusid = 1
1590 $searchQuery";
1591
1592 $result = cache_aware_pg_query( $this->_database, $_countQuery );
1593 $result = pg_fetch_array($result);
1594 $totalNumber = $result['number'];
1595
1596 $_mainQuery = "SELECT * FROM (SELECT distinct on (i.friendid) u.userid,
1597 c.md5h as id,
1598 a.shoptitle as referred_to,
1599 i.inviteddate as date,
1600 case
1601 when (u.firstname <> '' or u.middlename <> '' or u.lastname <> '')
1602 then concat_ws(' ', u.firstname, u.middlename, u.lastname)
1603 when (u.phone is not null and u.phone <> '') then u.phone
1604 end as full_name,
1605
1606 case
1607 when (i.statusid = 43) then 'Redeemed'
1608 when (i.statusid = 44 and mt.moneytransactionid is not null) then 'Purchased'
1609 when (i.statusid = 44 and mt.moneytransactionid is null) then 'Claimed'
1610 end as status
1611FROM invite i
1612 left join account a on i.accountid = a.accountid
1613 left join users u on i.friendid = u.userid
1614 left join customer c on u.userid = c.userid and i.inviter_accountid = c.accountid
1615 LEFT JOIN moneytransaction mt on (i.inviteid = mt.inviteid AND moneytransactiontypeid = 22)
1616WHERE i.statusid IN (43, 44)
1617 AND i.inviter_accountid = $accountID
1618 AND i.claimSource = 404
1619 AND c.statusid = 1
1620 $searchQuery) t
1621 $orderByQuery
1622 $limitQuery";
1623 $data = array();
1624 $response = cache_aware_pg_query( $this->_database, $_mainQuery );
1625 if ( ( ! empty( $response ) ) && ( pg_numrows( $response ) > 0 ) ) {
1626 while ($row = pg_fetch_array($response)) {
1627 $date = date('m/d/Y', strtotime($row['date']));
1628
1629 array_push($data, array(
1630 'id' => $row['id'],
1631 'Registration Date' => $date,
1632 'Referred To' => $row['referred_to'],
1633 'Name' => $row['full_name'],
1634 'Status' => $row['status'],
1635 ));
1636 }
1637 }
1638
1639 if (!empty($fields)) {
1640 $data = $this->export($businessID, $accountID, array_map(function ($customer) {
1641 return $customer['id'];
1642 }, $data));
1643 return $data;
1644 }
1645
1646 $jsonData = array();
1647 $jsonData['results'] = $data;
1648 $jsonData['meta'] = array();
1649 $jsonData['meta']['length'] = intval($totalNumber);
1650 $jsonData['meta']['limit'] = isset( $dataTablesGET['iDisplayLength'] ) ? intval( $dataTablesGET['iDisplayLength'] ) : - 1;
1651 $jsonData['meta']['page'] = isset( $dataTablesGET['page'] ) ? intval( $dataTablesGET['page'] ) : - 1;
1652 $jsonData['meta']['query'] = isset( $dataTablesGET['sSearch'] ) ? $dataTablesGET['sSearch'] : "";
1653 $jsonData['meta']['sortBy'] = isset( $dataTablesGET['iSortCol_0'] ) ? $dataTablesGET['iSortCol_0'] : "";
1654 $jsonData['meta']['sortOrder'] = isset( $dataTablesGET['sSortDir_0'] ) ? $dataTablesGET['sSortDir_0'] : "";
1655 return $jsonData;
1656 }
1657
1658 if ( $viewid === 'Referred' ) {
1659 GLOBAL $accountID, $businessID, $json_data_global, $totalNumber;
1660 $accountID = $result['accountid'];
1661 $businessID = $response['businessid'];
1662 $from = $dataTablesGET['iDisplayStart'];
1663 $countRows = $dataTablesGET['iDisplayLength'];
1664 foreach ( $defaultViews[ $viewid ] as $column ) {
1665 $c['name'] = $column;
1666 array_push( $columns, $c );
1667 }
1668 Dashboard_referred(strlen($from) === 0 ? "" : $from.'-'.$countRows, $dataTablesGET['sSearch'], $jsonResponse, $dataTablesGET['iSortCol_0'], $dataTablesGET['sSortDir_0'], $columns);
1669
1670 $jsonData = array();
1671 $jsonData['results'] = $json_data_global;
1672 $jsonData['meta'] = array();
1673 $jsonData['meta']['length'] = intval($totalNumber);
1674 $jsonData['meta']['limit'] = isset( $dataTablesGET['iDisplayLength'] ) ? intval( $dataTablesGET['iDisplayLength'] ) : - 1;
1675 $jsonData['meta']['page'] = isset( $dataTablesGET['page'] ) ? intval( $dataTablesGET['page'] ) : - 1;
1676 $jsonData['meta']['query'] = isset( $dataTablesGET['sSearch'] ) ? $dataTablesGET['sSearch'] : "";
1677 $jsonData['meta']['sortBy'] = isset( $dataTablesGET['iSortCol_0'] ) ? $dataTablesGET['iSortCol_0'] : "";
1678 $jsonData['meta']['sortOrder'] = isset( $dataTablesGET['sSortDir_0'] ) ? $dataTablesGET['sSortDir_0'] : "";
1679
1680 if (!empty($fields)) {
1681 $json_data_global = $this->export($businessID, $accountID, array_map(function ($customer) {
1682 return $customer['id'];
1683 }, $json_data_global));
1684 return $json_data_global;
1685 }
1686
1687 return $jsonData;
1688 }
1689
1690 // Include filters for default view
1691 switch ( $viewid ) {
1692 case 'Current Customers':
1693 $whereQuery .= ' AND c.customertypeid IN (1, 2, 3) AND c.affiliate = false ';
1694 break;
1695 case 'Past Customers':
1696 $whereQuery .= ' AND c.customertypeid IN (4, 5) AND c.affiliate = false ';
1697 break;
1698 case 'Leads':
1699 $whereQuery .= ' AND c.customertypeid = 6 AND c.affiliate = false ';
1700 break;
1701 case 'Other':
1702 $whereQuery .= ' AND c.affiliate = true ';
1703 default:
1704 $whereQuery .= ' AND c.customertypeid IN (1, 2, 3, 4, 5, 6, 7) ';
1705 }
1706 } else {
1707 if ( $fields != 'count' ) {
1708 $columns = $this->getColumns( $viewid );
1709 }
1710
1711 // Include filters for custom view
1712 $query = "SELECT o.value as operator, oc.value as columnname, f.value as filtervalue, f.users_field_id, uf.field_type, f.conditiontype
1713 FROM customersview_filter f
1714 LEFT JOIN option o ON o.optionid = f.operatorid
1715 LEFT JOIN option oc ON oc.optionid = f.columnid
1716 LEFT JOIN users_field uf ON uf.users_field_id = f.users_field_id
1717 WHERE f.customersviewid = $viewid";
1718 $filterResponse = $this->query( $query );
1719
1720 $servicesCount = 0;
1721 $labelsCount = 0;
1722 $customFieldsCount = 0;
1723 $allFilters = array();
1724 $anyFilters = array();
1725 if ( is_array( $filterResponse ) || is_object( $filterResponse ) ) {
1726 foreach ( $filterResponse as $filter ) {
1727 if ( $filter['conditiontype'] == "580" ) {
1728 array_push( $allFilters, $filter );
1729 } elseif ( $filter['conditiontype'] == "581" ) {
1730 array_push( $anyFilters, $filter );
1731 }
1732 }
1733 }
1734
1735 if ( is_array( $allFilters ) || is_object( $allFilters ) ) {
1736 $isAND = true;
1737 list( $selectQuery, $whereQuery, $havingQuery, $joinQuery, $include, $labelsCount ) = self::addQueries( $accountID, $businessID, $allFilters, $selectQuery, $whereQuery, $havingQuery, $joinQuery, $include, $isAND, $servicesCount, $labelsCount, $customFieldsCount, $multiLocationLoyalty );
1738 $customFieldsCount = sizeof($allFilters);
1739 }
1740
1741 if ( count( $anyFilters ) > 0 && ( is_array( $anyFilters ) || is_object( $anyFilters ) ) ) {
1742 $isAND = false;
1743 list( $selectQuery, $whereQuery, $havingQuery, $joinQuery, $include, $labelsCount ) = self::addQueries( $accountID, $businessID, $anyFilters, $selectQuery, $whereQuery, $havingQuery, $joinQuery, $include, $isAND, $servicesCount, $labelsCount, $customFieldsCount, $multiLocationLoyalty );
1744 }
1745
1746 if ( ! isset( $fields ) ) {
1747 // get custom fields columns
1748 $customColumns = array();
1749 $query = "SELECT c.users_field_id, uf.title, uf.field_type
1750 FROM customersview_column c
1751 JOIN users_field as uf ON uf.users_field_id = c.users_field_id
1752 WHERE c.customersviewid = $viewid AND c.users_field_id IS NOT NULL
1753 ORDER BY c.columnorder";
1754
1755 $response = cache_aware_pg_query( $this->_database, $query );
1756 if ( ( ! empty( $response ) ) && ( pg_numrows( $response ) > 0 ) ) {
1757 while ( $r = pg_fetch_array( $response ) ) {
1758
1759 //$whereQuery .= " AND ufuc$customColumnCount.users_field_id = " . $r['users_field_id'] . " ";
1760 $joinQuery .= " LEFT JOIN users_field_user AS ufuc$customColumnCount ON ufuc$customColumnCount.userid = c.userid AND ufuc$customColumnCount.users_field_id = " . $r['users_field_id'] . " ";
1761 $joinQuery .= " LEFT JOIN users_field AS ufc$customColumnCount ON ufuc$customColumnCount.users_field_id = ufc$customColumnCount.users_field_id";
1762 $selectQuery .= ", ufc$customColumnCount.title AS field_title$customColumnCount, ufc$customColumnCount.field_type AS field_type$customColumnCount, ufuc$customColumnCount.field AS field_value$customColumnCount ";
1763 $groupByQuery .= " , ufc$customColumnCount.title, ufc$customColumnCount.field_type, ufuc$customColumnCount.field ";
1764 $customColumns[$r['title']]['position'] = $customColumnCount;
1765 $customColumns[$r['title']]['type'] = $r['field_type'];
1766 $customColumnCount ++;
1767
1768 }
1769 }
1770 }
1771 }
1772
1773 if ( $jsonResponse ) {
1774 array_push( $columns, array( 'name' => 'Label' ) );
1775 array_push( $columns, array( 'name' => 'Email Unsubscribed' ) );
1776 array_push( $columns, array( 'name' => 'Text Unsubscribed' ) );
1777 }
1778
1779 if ( ! isset( $fields ) ) {
1780 foreach ( $columns as $column ) {
1781 if ( array_key_exists( $column['name'], $include ) && $include[ $column['name'] ] === false ) {
1782 $include[ $column['name'] ] = true;
1783 }
1784 }
1785 }
1786
1787 foreach ( $include as $columnName => $value ) {
1788
1789 if ( $value === true ) {
1790
1791 switch ( $columnName ) {
1792
1793 case 'Name':
1794 $selectQuery .= ", concat_ws(' ', u.firstname, u.middlename, u.lastname) as fullname";
1795 $groupByQuery .= ", u.firstname, u.middlename, u.lastname";
1796 break;
1797
1798 case 'First Name':
1799 $selectQuery .= ", u.firstname";
1800 if ( $include['Name'] === false ) {
1801 $groupByQuery .= ", u.firstname";
1802 }
1803 break;
1804
1805 case 'Last Name':
1806 $selectQuery .= ", u.lastname";
1807 if ( $include['Name'] === false ) {
1808 $groupByQuery .= ", u.lastname";
1809 }
1810 break;
1811
1812 case 'Email':
1813 $selectQuery .= ", e.email";
1814 $joinQuery .= " JOIN email e ON u.userid = e.userid ";
1815 $whereQuery .= " AND e.primaryemail = true ";
1816 $groupByQuery .= ", e.email";
1817 break;
1818
1819 case 'Phone':
1820 $selectQuery .= ", COALESCE(u.phone, ' ') as phone ";
1821 $groupByQuery .= ", u.phone";
1822 break;
1823
1824 case 'Zip':
1825 $selectQuery .= ", u.zip";
1826 $groupByQuery .= ", u.zip";
1827 break;
1828
1829 case 'Source':
1830 $selectQuery .= ", c.registrationtypeid, regt.tablename";
1831 $joinQuery .= " LEFT JOIN registrationtype regt ON regt.registrationtypeid = c.registrationtypeid ";
1832 $groupByQuery .= ", c.registrationtypeid, regt.tablename";
1833 break;
1834
1835 case 'Balance':
1836 $selectQuery .= ", credit.expdate, COALESCE( credit.store_credit_plus, 0 ) AS store_credit_plus, COALESCE( credit.store_credit_minus, 0 ) AS store_credit_minus, ( COALESCE( credit.store_credit_plus, 0 ) - COALESCE( credit.store_credit_minus, 0 ) ) as balans";
1837 $joinQuery .= " LEFT JOIN account_user_store_credit credit ON (u.userid = credit.userid and credit.accountid = $accountID) ";
1838 $groupByQuery .= ", credit.expdate, credit.store_credit_plus, credit.store_credit_minus";
1839 break;
1840
1841 case 'Loyalty Points':
1842 if ($multiLocationLoyalty) {
1843 $selectQuery .= ", sum(c_loyalty.loyalty_points_count) as loyalty_points";
1844 $joinQuery .= " JOIN customer c_loyalty ON c_loyalty.userid = u.userid ";
1845 } else {
1846 $selectQuery .= ", c.loyalty_points_count as loyalty_points";
1847 }
1848 break;
1849
1850 case 'Last Visit':
1851 $selectQuery .= ", c.last_visit as visitdate";
1852 break;
1853
1854 case 'First Visit':
1855 $selectQuery .= ", c.first_visit as first_visit_date";
1856 break;
1857
1858 case 'Visits':
1859 $selectQuery .= ", c.visits_count";
1860 break;
1861
1862 case 'Offers':
1863 $selectQuery .= ", count (distinct i_saved.voucherid) as invite_id_saved";
1864 $joinQuery .= " LEFT JOIN invite i_saved ON (i_saved.friendid = c.userid AND i_saved.statusid=44 AND i_saved.accountid=c.accountid) ";
1865 break;
1866
1867 case 'Registration Date':
1868 $selectQuery .= ", c.registrationdate";
1869 break;
1870
1871 case 'Age':
1872 $selectQuery .= ", EXTRACT(YEAR from AGE(u.birthday::date)) as age_customer";
1873 break;
1874 case 'Last Purchased Date':
1875 $selectQuery .= ", (select date from account_app_service_purchase where account_app_service_customer_id in (select account_app_service_customer_id from account_app_service_customer where customerid = c.customerid) order by date DESC LIMIT 1 ) as last_purchased_date ";
1876 break;
1877 case 'Contract Expiration Date':
1878 $selectQuery .= ", (select effective_expiration_date from tsk_contract where student_id in (select field::bigint as student_id from users_field_user ufu left join users_field uf on ufu.users_field_id = uf.users_field_id where ufu.userid = u.userid and ufu.users_field_id = 1772) order by contract_id DESC limit 1 ) as effective_expiration_date ";
1879 break;
1880 case 'Contract Purchase Date':
1881 $selectQuery .= ", (select member_purchase_date from tsk_contract where student_id in (select field::bigint as student_id from users_field_user ufu left join users_field uf on ufu.users_field_id = uf.users_field_id where ufu.userid = u.userid and ufu.users_field_id = 1772) order by contract_id DESC limit 1 ) as member_purchase_date ";
1882 break;
1883 case 'Contract Payment Due Date':
1884 $selectQuery .= ", (select payment_due_date from tsk_contract where student_id in (select field::bigint as student_id from users_field_user ufu left join users_field uf on ufu.users_field_id = uf.users_field_id where ufu.userid = u.userid and ufu.users_field_id = 1772) order by contract_id DESC limit 1 ) as payment_due_date ";
1885 break;
1886 case 'Last Purchased Service/Product':
1887 $selectQuery .= ", (select (select title from account_app_service where account_app_service_id = ac.account_app_service_id)
1888from account_app_service_purchase ap left join account_app_service_customer ac ON ap.account_app_service_customer_id = ac.account_app_service_customer_id
1889where ap.account_app_service_customer_id in (select account_app_service_customer_id from account_app_service_customer where customerid = c.customerid)
1890order by ap.date DESC LIMIT 1) as last_purchased_product";
1891 break;
1892 case 'Contact Status':
1893 $selectQuery .= ", cst.name";
1894 $joinQuery .= " LEFT JOIN contact_status cst on cst.contact_status_id = c.contact_status_id ";
1895 $groupByQuery .= ", cst.name";
1896 break;
1897 case 'Last Modified Date':
1898 $selectQuery .= ", u.modified_date ";
1899 break;
1900 case 'Last Attendance Status':
1901 $selectQuery .= ", (select statusid from customervisits_schedule where customerid = c.customerid and date <= current_date + interval '2 hours' order by date DESC limit 1) as last_attendance_status";
1902 break;
1903 case 'Email Unsubscribed':
1904 $selectQuery .= ", COALESCE(eu.uns_email, false) as uns_email ";
1905 $joinQuery .= " LEFT JOIN emailunsubscribe eu ON (eu.userid = u.userid AND (eu.fromsystem = true OR eu.frombusinessid = $businessID AND eu.uns_email = true))";
1906 $groupByQuery .= ", eu.uns_email";
1907 break;
1908 case 'Text Unsubscribed':
1909 $selectQuery .= ", COALESCE(eu2.uns_phone, false) as uns_phone ";
1910 $joinQuery .= " LEFT JOIN emailunsubscribe eu2 ON (eu2.userid = u.userid AND (eu2.fromsystem = true OR eu2.frombusinessid = $businessID AND eu2.uns_phone = true))";
1911 $groupByQuery .= ", eu2.uns_phone";
1912 break;
1913 case 'Mindbody Member':
1914 $selectQuery .= ", (select mbo_member from user_mbo where userid = u.userid and accountid = $accountID order by mbo_register_date DESC limit 1) as mbo_member ";
1915 break;
1916 case 'Gender':
1917 $selectQuery .= ", ( select value from option where optionid = (select genderid from users where userid = u.userid) ) as gender_value ";
1918 break;
1919 case 'Type':
1920 $selectQuery .= ", cust.name ";
1921 $joinQuery .= " LEFT JOIN customertype cust on cust.customertypeid = c.customertypeid ";
1922 $groupByQuery .= ", cust.name";
1923 break;
1924
1925 }
1926 }
1927 }
1928
1929 if ( isset( $dataTablesGET ) ) {
1930 // Sorting
1931 $orderByQuery .= "ORDER BY ";
1932 if ( isset( $dataTablesGET['iSortCol_0'] ) ) {
1933 $sortColumnIndex = $dataTablesGET['iSortCol_0'] > 0 ? $dataTablesGET['iSortCol_0'] - 1 : 0;
1934 switch ( $columns[ $sortColumnIndex ]['name'] ) {
1935 case 'Name':
1936 $orderByQuery .= "NULLIF(u.firstname , '') " . $dataTablesGET['sSortDir_0'] . " NULLS LAST, ";
1937 $orderByQuery .= "NULLIF(u.middlename , '') " . $dataTablesGET['sSortDir_0'] . " NULLS LAST, ";
1938 $orderByQuery .= "NULLIF(u.lastname, '')";
1939 break;
1940 case 'First Name':
1941 $orderByQuery .= "NULLIF(u.firstname , '')";
1942 break;
1943 case 'Last Name':
1944 $orderByQuery .= "NULLIF(u.lastname , '')";
1945 break;
1946 case 'Email':
1947 $orderByQuery .= "NULLIF(e.email , '')";
1948 break;
1949 case 'Phone':
1950 $orderByQuery .= "phone";
1951 break;
1952 case 'Zip':
1953 $orderByQuery .= "NULLIF(u.zip, '')";
1954 break;
1955 case 'Source':
1956 $orderByQuery .= "regt.tablename";
1957 break;
1958 case 'Balance':
1959 $orderByQuery .= "( COALESCE( credit.store_credit_plus, 0 ) - COALESCE( credit.store_credit_minus, 0 ) )";
1960 break;
1961 case 'Loyalty Points':
1962 if ($multiLocationLoyalty) {
1963 $orderByQuery .= "sum(c_loyalty.loyalty_points_count)";
1964 } else {
1965 $orderByQuery .= "c.loyalty_points_count";
1966 }
1967 break;
1968 case 'Last Visit':
1969 $orderByQuery .= "c.last_visit";
1970 break;
1971 case 'First Visit':
1972 $orderByQuery .= "c.first_visit";
1973 break;
1974 case 'Visits':
1975 $orderByQuery .= "c.visits_count";
1976 break;
1977 case 'Registration Date':
1978 $orderByQuery .= "c.registrationdate";
1979 break;
1980 case 'Age':
1981 $orderByQuery .= "u.birthday";
1982 break;
1983 case 'Last Purchased Date':
1984 $orderByQuery .= "last_purchased_date";
1985 break;
1986 case 'Contract Expiration Date':
1987 $orderByQuery .= "effective_expiration_date";
1988 break;
1989 case 'Contract Purchase Date':
1990 $orderByQuery .= "member_purchase_date";
1991 break;
1992 case 'Contract Payment Due Date':
1993 $orderByQuery .= "payment_due_date";
1994 break;
1995 case 'Contract Program Type':
1996 $orderByQuery .= "program_type";
1997 break;
1998 case 'Contract Payment Plan':
1999 $orderByQuery .= "payment_plan";
2000 break;
2001 case 'Contract Discount Type':
2002 $orderByQuery .= "discount_type";
2003 break;
2004 case 'Contract Age Group':
2005 $orderByQuery .= "age_group";
2006 break;
2007 case 'Last Attendance Status':
2008 $orderByQuery .= "last_attendance_status";
2009 break;
2010 case 'Email Unsubscribed':
2011 $orderByQuery .= "uns_email";
2012 break;
2013 case 'Text Unsubscribed':
2014 $orderByQuery .= "uns_phone";
2015 break;
2016 case 'Mindbody Member':
2017 $orderByQuery .= "mbo_member";
2018 break;
2019 case 'Gender':
2020 $orderByQuery .= "gender_value";
2021 break;
2022 case 'Type':
2023 $orderByQuery .= "cust.name";
2024 break;
2025 case 'Contact Status':
2026 $orderByQuery .= "cst.name";
2027 break;
2028 case 'Status':
2029 $orderByQuery .= "status";
2030 break;
2031 case 'Last Modified Date':
2032 $orderByQuery .= "u.modified_date";
2033 break;
2034 case 'Last Purchased Service/Product':
2035 $orderByQuery .= "last_purchased_product";
2036 break;
2037 default: // custom field
2038 // TODO: Sort by custom field.
2039
2040 if($customColumns[$columns[ $sortColumnIndex ]['name']]['type'] == 'number') {
2041 $orderByQuery .= "ufuc{$customColumns[$columns[ $sortColumnIndex ]['name']]['position']}.field::int";
2042 } else {
2043 $orderByQuery .= "ufuc{$customColumns[$columns[ $sortColumnIndex ]['name']]['position']}.field";
2044 }
2045
2046 }
2047 }
2048 if ( $orderByQuery != "ORDER BY " ) {
2049 $orderByQuery .= " " . $dataTablesGET['sSortDir_0'] . " NULLS LAST, c.customerid ";
2050 } else {
2051 $orderByQuery = "";
2052 }
2053
2054 // Search filtering
2055 if ( isset( $dataTablesGET['sSearch'] ) && $dataTablesGET['sSearch'] != "" ) {
2056
2057 $whereQuery .= " AND (";
2058 if ( $include['Name'] === true ) {
2059 $whereQuery .= "lower(concat_ws(' ', trim(u.firstname), trim(u.middlename), trim(u.lastname))) LIKE lower('%" . pg_escape_string(trim($dataTablesGET['sSearch'])) . "%') OR ";
2060 } else {
2061 if ( $include['First Name'] === true ) {
2062 $whereQuery .= "trim(u.firstname) LIKE '%" . pg_escape_string(trim($dataTablesGET['sSearch'])) . "%' OR ";
2063 }
2064 if ( $include['Last Name'] === true ) {
2065 $whereQuery .= "trim(u.lastname) LIKE '%" . pg_escape_string( trim($dataTablesGET['sSearch'])) . "%' OR ";
2066 }
2067 }
2068 if ( $include['Phone'] === true ) {
2069 $searchCriteria = preg_replace("/[^0-9]/", "", urldecode($dataTablesGET['sSearch']));
2070 $dontHaveLetters = strlen(preg_replace("/[^a-zA-Z]/", "", urldecode($dataTablesGET['sSearch']))) == 0;
2071 if($dontHaveLetters && strlen($searchCriteria) > 0) {
2072 $whereQuery .= " u.phone LIKE '%" . $searchCriteria . "%' OR ";
2073 try {
2074 if(strlen($searchCriteria) > 2) {
2075 $phoneUtil = \libphonenumber\PhoneNumberUtil::getInstance();
2076 $countryShort = $this->getCountryShortCodeFromAccountId($accountID);
2077 $phoneParsed = $phoneUtil->parse($searchCriteria, $countryShort);
2078
2079 $phoneWithCountryPrefix = $phoneParsed->getCountryCode() . "" . $phoneParsed->getNationalNumber();
2080 $whereQuery .= " u.phone LIKE '" . $phoneWithCountryPrefix . "%' OR ";
2081 }
2082 } catch (\libphonenumber\NumberParseException $npe) {
2083 } catch (Exception $e) { }
2084 }
2085 }
2086 if ( $include['Email'] === true ) {
2087 $toLowerSearchCriteria = strtolower(pg_escape_string($dataTablesGET['sSearch']));
2088 $whereQuery .= "LOWER(e.email) LIKE '%" . $toLowerSearchCriteria . "%' OR ";
2089 }
2090 if ($customColumnCount > 0) {
2091 for ($i = 0; $i < $customColumnCount; $i++) {
2092 $toLowerSearchCriteria = strtolower(pg_escape_string($dataTablesGET['sSearch']));
2093 $whereQuery .= "LOWER(ufuc$i.field) LIKE '%" . $toLowerSearchCriteria . "%' OR ";
2094 }
2095 }
2096 $whereQuery = substr_replace( $whereQuery, "", - 3 );
2097 $whereQuery .= ')';
2098 }
2099 }
2100
2101
2102 $havingQuery = str_replace(" AND AND ", " AND ", $havingQuery);
2103
2104 if ( strpos( $havingQuery, 'AND' ) !== false ) {
2105 $havingQuery = 'HAVING ' . substr( $havingQuery, 4 );
2106 }
2107
2108 $query = "SELECT cn.dialing_code, c.phone_format, c.phone_format2, c.non_zero_numbers
2109 FROM account a
2110 LEFT JOIN country cn ON (cn.countryid=a.countryid)
2111 LEFT JOIN country_phone_format c ON (c.countryid=a.countryid)
2112 WHERE a.md5h = '$accountMd5h'";
2113 $result = cache_aware_pg_query( $this->_database, $query );
2114 if ( ( ! empty( $result ) ) && ( pg_numrows( $result ) > 0 ) ) {
2115 $result = pg_fetch_array( $result );
2116 $dialing_code = $result['dialing_code'];
2117 $phone_format = $result['phone_format'];
2118 $phone_format2 = $result['phone_format2'];
2119 $non_zero_numbers = $result['non_zero_numbers'];
2120 }
2121
2122 if ( isset( $additionalConditions ) ) {
2123 $whereQuery .= ' AND ' . $additionalConditions;
2124 if ( strpos( $additionalConditions, 'eu.' ) !== false && strpos( $joinQuery, 'emailunsubscribe' ) === false ) {
2125 $query = "SELECT businessid
2126 FROM account
2127 WHERE accountid = $accountID";
2128 $response = $this->querySingle( $query );
2129 $businessID = $response['businessid'];
2130 $joinQuery .= " LEFT JOIN emailunsubscribe eu ON (u.userid = eu.userid AND (eu.fromsystem = TRUE OR eu.frombusinessid={$businessID} AND (eu.uns_email=true OR eu.uns_phone=true OR eu.uns_mobile=true))) ";
2131 }
2132 }
2133
2134 if(isset($customerId)) {
2135 $whereQuery.=" AND c.customerid = ".$customerId." ";
2136 }
2137
2138
2139 $whereQuery = str_replace("AND ( )", "", $whereQuery);
2140 $whereQuery = str_replace(" AND AND ", " AND ", $whereQuery);
2141 $whereQuery = str_replace(" AND OR ", " AND ", $whereQuery);
2142 // GET CUSTOMERS
2143 $query =
2144 "SELECT count(c.customerid) OVER() AS total_count, u.phone as user_phone,
2145 c.md5h, c.customerid, c.customertypeid, c.userid, u.birthday as user_birthday,
2146 c.affiliate::boolean::text $selectQuery
2147 FROM Customer c
2148 JOIN Users u ON c.userid = u.userid
2149 $joinQuery
2150 WHERE c.accountid = {$accountID} AND u.statusid IN (54,55,56,57) AND c.statusid = 1 $whereQuery
2151 GROUP BY c.customerid, u.invitedbyaccountid, u.usertypeid, c.affiliate, u.userid, u.md5h $groupByQuery
2152 $havingQuery
2153 $orderByQuery
2154 $limitQuery";
2155
2156 $query = preg_replace( "/\s+/", " ", preg_replace( "/[\t]|[\n]|[\r]/", " ", $query ) );
2157 $query = str_replace(" AND GROUP ", " GROUP ", $query);
2158 $query = str_replace(" OR GROUP ", " GROUP ", $query);
2159 $query = str_replace("AND ( ) AND", " AND ", $query);
2160 $query = str_replace("AND ( )", " ", $query);
2161 $logger->info(json_encode(array('view_query' => $query)));
2162// echo "******".$query; die;
2163 $output = array(
2164 "sEcho" => intval( $dataTablesGET['sEcho'] ),
2165 "iTotalRecords" => 0,
2166 "iTotalDisplayRecords" => 0,
2167 "aaData" => array()
2168 );
2169 $jsonData = array();
2170 $jsonData['results'] = array();
2171 $jsonData['meta'] = array();
2172 $jsonData['meta']['length'] = 0;
2173 $jsonData['meta']['limit'] = isset( $dataTablesGET['iDisplayLength'] ) ? intval( $dataTablesGET['iDisplayLength'] ) : - 1;
2174 $jsonData['meta']['page'] = isset( $dataTablesGET['page'] ) ? intval( $dataTablesGET['page'] ) : - 1;
2175 $jsonData['meta']['query'] = isset( $dataTablesGET['sSearch'] ) ? $dataTablesGET['sSearch'] : "";
2176 $jsonData['meta']['sortBy'] = isset( $dataTablesGET['iSortCol_0'] ) ? $dataTablesGET['iSortCol_0'] : "";
2177 $jsonData['meta']['sortOrder'] = isset( $dataTablesGET['sSortDir_0'] ) ? $dataTablesGET['sSortDir_0'] : "";
2178 $result = cache_aware_pg_query( $this->_database, $query );
2179 $result = pg_fetch_all($result);
2180
2181 if ( ( ! empty( $result ) ) && ( sizeof( $result ) > 0 ) ) {
2182
2183 if ( isset( $dataTablesGET ) || $include['Label'] === true ) {
2184 $customerIds = array_map(function($element) {
2185 return $element['customerid'];
2186 }, $result);
2187 $labels = cache_aware_pg_query( $this->_database, "
2188 SELECT ccg.customerid, cg.customer_group_id, cg.color, cg.name
2189 FROM customer_customergroup ccg
2190 JOIN customer_group cg ON cg.customer_group_id = ccg.customer_group_id AND cg.statusid = 270
2191 WHERE ccg.customerid IN {$this->to_pg_array($customerIds)}
2192 " );
2193 $labels = pg_fetch_all($labels);
2194 if(!is_array($labels)) {
2195 $labels = array();
2196 }
2197 }
2198
2199 if ( isset( $dataTablesGET ) || $include['Class'] === true ) {
2200 $customerIds = array_map(function($element) {
2201 return $element['customerid'];
2202 }, $result);
2203 $classes = cache_aware_pg_query($this->_database,
2204 "SELECT apsc.customerid, aps.account_app_service_id, aps.title
2205 FROM account_app_service aps
2206 JOIN account_app_service_customer apsc ON aps.account_app_service_id = apsc.account_app_service_id AND
2207 aps.statusid = 272 AND aps.service_type_id = 490
2208 WHERE apsc.customerid IN {$this->to_pg_array($customerIds)}
2209 ORDER BY apsc.date DESC");
2210 $classes = pg_fetch_all($classes);
2211 if(!is_array($classes)) {
2212 $classes = array();
2213 }
2214 }
2215 if ( isset( $dataTablesGET ) || $include['Appointment'] === true ) {
2216 $customerIds = array_map(function($element) {
2217 return $element['customerid'];
2218 }, $result);
2219 $appointments = cache_aware_pg_query($this->_database,
2220 "SELECT apsc.customerid, aps.account_app_service_id, aps.title
2221 FROM account_app_service aps
2222 JOIN account_app_service_customer apsc ON aps.account_app_service_id = apsc.account_app_service_id AND
2223 aps.statusid = 272 AND aps.service_type_id = 491
2224 WHERE apsc.customerid IN {$this->to_pg_array($customerIds)}
2225 ORDER BY apsc.date DESC");
2226 $appointments = pg_fetch_all($appointments);
2227 if(!is_array($appointments)) {
2228 $appointments = array();
2229 }
2230 }
2231
2232
2233 if ( isset( $dataTablesGET ) || $include['Claimed Offer'] === true ) {
2234 $customerIds = array_map(function($element) {
2235 return $element['customerid'];
2236 }, $result);
2237
2238
2239 $claimedOffers = cache_aware_pg_query($this->_database,
2240 "select distinct i.voucherid, c.customerid, i.inviteddate, (SELECT vouchertitle from voucher where voucherid = i.voucherid) as voucher_title
2241 from invite i
2242 left join customer c on c.userid = i.friendid
2243 where i.friendid in (select userid from customer where customerid in {$this->to_pg_array($customerIds)}) and i.statusid in (43, 44)
2244 order by i.inviteddate desc
2245 ");
2246 $claimedOffers = pg_fetch_all($claimedOffers);
2247 if(!is_array($claimedOffers)) {
2248 $claimedOffers = array();
2249 }
2250 }
2251
2252
2253 if ( isset( $dataTablesGET ) || $include['Service'] === true ) {
2254 $customerIds = array_map(function($element) {
2255 return $element['customerid'];
2256 }, $result);
2257 $services = cache_aware_pg_query($this->_database,
2258 "SELECT apsc.customerid, aps.account_app_service_id, aps.title
2259 FROM account_app_service aps
2260 JOIN account_app_service_customer apsc ON aps.account_app_service_id = apsc.account_app_service_id AND
2261 aps.statusid = 272 AND aps.service_type_id = 492
2262 WHERE apsc.customerid IN {$this->to_pg_array($customerIds)}
2263 ORDER BY apsc.date DESC");
2264 $services = pg_fetch_all($services);
2265 if(!is_array($services)) {
2266 $services = array();
2267 }
2268 }
2269 if ( isset( $dataTablesGET ) || $include['Purchase'] === true ) {
2270 $customerIds = array_map(function($element) {
2271 return $element['customerid'];
2272 }, $result);
2273 $purchases = cache_aware_pg_query($this->_database,
2274 "SELECT apsc.customerid, aps.account_app_service_id, aps.title
2275 FROM account_app_service aps
2276 JOIN account_app_service_customer apsc ON aps.account_app_service_id = apsc.account_app_service_id AND
2277 aps.statusid = 272 AND aps.service_type_id = 495
2278 WHERE apsc.customerid IN {$this->to_pg_array($customerIds)}
2279 ORDER BY apsc.date DESC");
2280 $purchases = pg_fetch_all($purchases);
2281 if(!is_array($purchases)) {
2282 $purchases = array();
2283 }
2284 }
2285 //tsk program type
2286 if ( isset( $dataTablesGET ) || $include['Contract Program Type'] === true ) {
2287 $tskProgramType = array();
2288 $customerIds = array_map(function($element) {
2289 return $element['customerid'];
2290 }, $result);
2291 foreach($customerIds as $cId){
2292 $sqlProgramType = cache_aware_pg_query($this->_database,
2293 "SELECT c.customerid, tco.id, tco.value as title
2294 from tsk_contract_option tco
2295 join tsk_contract tc on tc.program_type_optionid=tco.id and tco.field_name ilike 'program_type'
2296 join users_field_user ufu on tc.student_id=ufu.field::bigint and ufu.users_field_id = 1772
2297 join customer c on c.userid = ufu.userid
2298 where c.customerid = $cId
2299 order by tc.contract_id DESC limit 1;");
2300 $sqlProgramType = pg_fetch_all($sqlProgramType);
2301 if (sizeof($sqlProgramType) > 0){
2302 array_push($tskProgramType, $sqlProgramType[0]);
2303 }
2304 }
2305 if(!is_array($tskProgramType)) {
2306 $tskProgramType = array();
2307 }
2308 }
2309 //tsk payment plan
2310 if ( isset( $dataTablesGET ) || $include['Contract Payment Plan'] === true ) {
2311 $tskPaymentPlan = array();
2312 $customerIds = array_map(function($element) {
2313 return $element['customerid'];
2314 }, $result);
2315 foreach($customerIds as $cId){
2316 $sqlPaymentPlan = cache_aware_pg_query($this->_database,
2317 "SELECT c.customerid, tco.id, tco.value as title
2318 from tsk_contract_option tco
2319 join tsk_contract tc on tc.payment_plan_optionid=tco.id and tco.field_name ilike 'payment_plan'
2320 join users_field_user ufu on tc.student_id=ufu.field::bigint and ufu.users_field_id = 1772
2321 join customer c on c.userid = ufu.userid
2322 where c.customerid = $cId
2323 order by tc.contract_id DESC limit 1;");
2324 $sqlPaymentPlan = pg_fetch_all($sqlPaymentPlan);
2325 if (sizeof($sqlPaymentPlan) > 0){
2326 array_push($tskPaymentPlan, $sqlPaymentPlan[0]);
2327 }
2328 }
2329 if(!is_array($tskPaymentPlan)) {
2330 $tskPaymentPlan = array();
2331 }
2332 }
2333 //tsk age group
2334 if ( isset( $dataTablesGET ) || $include['Contract Age Group'] === true ) {
2335 $tskAgeGroup = array();
2336 $customerIds = array_map(function($element) {
2337 return $element['customerid'];
2338 }, $result);
2339 foreach($customerIds as $cId){
2340 $sqlAgeGroup = cache_aware_pg_query($this->_database,
2341 "SELECT c.customerid, tco.id, tco.value as title
2342 from tsk_contract_option tco
2343 join tsk_contract tc on tc.age_group_optionid=tco.id and tco.field_name ilike 'age_group'
2344 join users_field_user ufu on tc.student_id=ufu.field::bigint and ufu.users_field_id = 1772
2345 join customer c on c.userid = ufu.userid
2346 where c.customerid = $cId
2347 order by tc.contract_id DESC limit 1;");
2348 $sqlAgeGroup = pg_fetch_all($sqlAgeGroup);
2349 if (sizeof($sqlAgeGroup) > 0){
2350 array_push($tskAgeGroup, $sqlAgeGroup[0]);
2351 }
2352 }
2353 if(!is_array($tskAgeGroup)) {
2354 $tskAgeGroup = array();
2355 }
2356 }
2357 //tsk discount type
2358 if ( isset( $dataTablesGET ) || $include['Contract Discount Type'] === true ) {
2359 $tskDiscountType = array();
2360 $customerIds = array_map(function($element) {
2361 return $element['customerid'];
2362 }, $result);
2363 foreach($customerIds as $cId){
2364 $sqlDiscountType = cache_aware_pg_query($this->_database,
2365 "SELECT c.customerid, tco.id, tco.value as title
2366 from tsk_contract_option tco
2367 join tsk_contract tc on tc.discount_type_optionid=tco.id and tco.field_name ilike 'discount_type'
2368 join users_field_user ufu on tc.student_id=ufu.field::bigint and ufu.users_field_id = 1772
2369 join customer c on c.userid = ufu.userid
2370 where c.customerid = $cId
2371 order by tc.contract_id DESC limit 1;");
2372 $sqlDiscountType = pg_fetch_all($sqlDiscountType);
2373 if (sizeof($sqlDiscountType) > 0){
2374 array_push($tskDiscountType, $sqlDiscountType[0]);
2375 }
2376 }
2377 if(!is_array($tskDiscountType)) {
2378 $tskDiscountType = array();
2379 }
2380 }
2381 if ( isset( $dataTablesGET ) ) {
2382 foreach ( $result as $row ) {
2383 if ( $output['iTotalDisplayRecords'] == 0 ) {
2384 $output['iTotalRecords'] = $row['total_count'];
2385 $output['iTotalDisplayRecords'] = $row['total_count'];
2386 $jsonData['meta']['length'] = intval( $row['total_count'] );
2387 }
2388 $customer['ID'] = $row['md5h'];
2389
2390 $isUnsubscribedEmail = false;
2391 $isUnsubscribedPhone = false;
2392 $unsubscribedEmailSource = null;
2393 $unsubscribedEmailDate = null;
2394 $unsubscribedTextDate = null;
2395 $unsubscribedTextSource = null;
2396 if ( $include['Email'] === true || $include['Phone'] === true || $include['Email Unsubscribed'] === true || $include['Text Unsubscribed'] === true ) {
2397 $unsQuery = "SELECT uns_email, uns_phone, startdate, fromsystem, email_unsubscribe_date, phone_unsubscribe_date, (select value from option where optionid = sourceid) as emailSourceText, (select value from option where optionid = textsourceid) as sourcetext
2398FROM emailunsubscribe WHERE userid = {$row['userid']}
2399 AND (frombusinessid={$businessID} OR fromaccountid={$accountID}) order by startdate desc limit 1";
2400
2401 $resultUns = cache_aware_pg_query( $this->_database, $unsQuery );
2402 if ( ( ! empty( $resultUns ) ) && ( pg_numrows( $resultUns ) > 0 ) ) {
2403 while ( $unsRow = pg_fetch_assoc( $resultUns ) ) {
2404 if ( $unsRow['uns_email'] == 't' || $unsRow['fromsystem'] == 't' ) {
2405 $isUnsubscribedEmail = true;
2406 }
2407 if ( $unsRow['uns_phone'] == 't' || $unsRow['fromsystem'] == 't' ) {
2408 $isUnsubscribedPhone = true;
2409 }
2410 $unsubscribedTextSource = $unsRow['sourcetext'];
2411 $unsubscribedEmailSource = $unsRow['emailsourcetext'];
2412
2413 if($unsRow['email_unsubscribe_date'] == null && $isUnsubscribedEmail) {
2414 $unsubscribedEmailDate = new DateTime($unsRow['startdate']);
2415 $unsubscribedEmailDate = $unsubscribedEmailDate -> format(DateTime::ISO8601);
2416 } else {
2417 $unsubscribedEmailDate = new DateTime($unsRow['email_unsubscribe_date']);
2418 $unsubscribedEmailDate = $unsubscribedEmailDate -> format(DateTime::ISO8601);
2419 }
2420 if($unsRow['phone_unsubscribe_date'] == null && $isUnsubscribedPhone) {
2421 $unsubscribedTextDate = new DateTime($unsRow['startdate']);
2422 $unsubscribedTextDate = $unsubscribedTextDate -> format(DateTime::ISO8601);
2423 } else {
2424 $unsubscribedTextDate = new DateTime($unsRow['phone_unsubscribe_date']);
2425 $unsubscribedTextDate = $unsubscribedTextDate -> format(DateTime::ISO8601);
2426 }
2427
2428
2429 }
2430 }
2431 }
2432
2433 $dtRow = array();
2434 $data = array();
2435
2436 //$isSelected = in_array($customer['ID'], $selectedContactsIds) ? "checked" : "";
2437 $data['id'] = $customer['ID'];
2438 $dtRow[] = "<input type=\"checkbox\" class=\"big-checkbox select-checkbox\" data-cid=\"{$customer['ID']}\" data-labels-ids=\"{$row['labels_ids']}\">";
2439
2440
2441 foreach ( $columns as $column ) {
2442
2443 switch ( $column['name'] ) {
2444 case 'Name':
2445 $data['Name'] = $row['fullname'];
2446 $dtRow[] = $row['fullname'];
2447 break;
2448 case 'First Name':
2449 $data['First Name'] = $row['firstname'];
2450 $dtRow[] = $row['firstname'];
2451 break;
2452 case 'Last Name':
2453 $data['Last Name'] = $row['lastname'];
2454 $dtRow[] = $row['lastname'];
2455 break;
2456 case 'Zip':
2457 $data['Zip'] = $row['zip'];
2458 $dtRow[] = $row['zip'];
2459 break;
2460 case 'Email':
2461 $emailMapping = isset( $row['email'] ) ? $row['email'] : "";
2462 $data['Email raw'] = $emailMapping;
2463 if ( strpos( $row['email'], '@' ) !== false ) {
2464 $emailMapping = $isUnsubscribedEmail ? mask_email( $row['email'] ) : $row['email'];
2465 /*if(isset($row['valid'], $row['last_verified_email']) &&
2466 !(bool)$row['valid'] && $row['email'] == $row['last_verified_email']) {
2467 $rowTemp .= $INVALID_EMAIL_TOOLTIP;
2468 }*/
2469 }
2470 $data['Email'] = $emailMapping;
2471 $dtRow[] = $emailMapping;
2472 break;
2473 case 'Phone':
2474 $phone = "";
2475 $data['Phone'] = "";
2476 if ( ! empty( $row['phone'] ) ) {
2477 $phone = formatPhoneNumber( $row['phone'], $dialing_code, $phone_format, $phone_format2, $non_zero_numbers );
2478 $data['Phone'] = $phone;
2479 $data['Phone raw'] = $row['phone'];
2480 if ( $isUnsubscribedPhone ) {
2481 $phone = substr( $phone, 0, - 3 ) . "***";
2482 }
2483 }
2484 $dtRow[] = $phone;
2485 break;
2486 case 'Source':
2487 switch ( $row['registrationtypeid'] ) {
2488 case 11:
2489 $customer['source'] = 'Imported';
2490 break;
2491 case 10:
2492 $customer['source'] = 'Invited';
2493 break;
2494 case 9:
2495 $customer['source'] = 'Web App'; //Via Web-app
2496 break;
2497 case 17:
2498 case 35:
2499 $customer['source'] = 'Landing Page';
2500 break;
2501 case 21:
2502 $customer['source'] = 'Customers Page';
2503 break;
2504 case 22:
2505 $customer['source'] = 'Search Field';
2506 break;
2507 case 23:
2508 $customer['source'] = 'Master';
2509 break;
2510 case 24:
2511 $customer['source'] = 'MINDBODY';
2512 break;
2513 case 28:
2514 $customer['source'] = 'Call In';
2515 break;
2516 case 30:
2517 $customer['source'] = 'Backend Redeem';
2518 break;
2519 case 32:
2520 $customer['source'] = 'Pike13';
2521 break;
2522 case 34:
2523 $customer['source'] = 'Text Sent';
2524 break;
2525 case 35:
2526 $customer['source'] = 'Landing Page Claim';
2527 break;
2528 case 37:
2529 $customer['source'] = 'Zapier';
2530 break;
2531 case 39:
2532 $customer['source'] = 'Website Widget';
2533 break;
2534 case 41:
2535 $customer['source'] = 'VTiger';
2536 break;
2537 case 43:
2538 $customer['source'] = 'iConnect';
2539 break;
2540 case 45:
2541 $customer['source'] = 'WIFI';
2542 break;
2543 case 47:
2544 $customer['source'] = 'ClubReady';
2545 break;
2546 case 49:
2547 $customer['source'] = 'ABC Financial';
2548 break;
2549 case 51:
2550 $customer['source'] = 'Clover';
2551 break;
2552 case 53:
2553 $customer['source'] = 'Contact Form';
2554 break;
2555 case 55:
2556 $customer['source'] = 'CityGro';
2557 break;
2558 case 57:
2559 $customer['source'] = 'UniversalShareLink';
2560 break;
2561 case 60:
2562 $customer['source'] = 'AskForFeedback';
2563 break;
2564 case 58:
2565 $customer['source'] = 'PartnerUp';
2566 break;
2567 case 60:
2568 $customer['source'] = 'AskForFeedback';
2569 break;
2570 case 62:
2571 $customer['source'] = 'Poynt';
2572 break;
2573 case 64:
2574 $customer['source'] = 'Texting Extension';
2575 break;
2576 case 66:
2577 $customer['source'] = 'Subscription Form';
2578 break;
2579 case 68:
2580 $customer['source'] = 'ECommerce';
2581 break;
2582 case 70:
2583 $customer['source'] = 'Reviews Widget';
2584 break;
2585 case 72:
2586 $customer['source'] = 'Landing Page Subscribe';
2587 break;
2588 case 74:
2589 $customer['source'] = 'ASF Payment Solutions';
2590 break;
2591 case 76:
2592 $customer['source'] = 'API';
2593 break;
2594 case 81:
2595 $customer['source'] = 'Booker';
2596 break;
2597 case 83:
2598 $customer['source'] = 'Mailchimp';
2599 break;
2600 case 85:
2601 $customer['source'] = 'Gmail';
2602 break;
2603 case 87:
2604 $customer['source'] = 'Constant Contact';
2605 break;
2606 case 89:
2607 $customer['source'] = 'Quickbooks';
2608 break;
2609 case 91:
2610 $customer['source'] = 'Yahoo';
2611 break;
2612 case 93:
2613 $customer['source'] = 'Glofox';
2614 break;
2615 case 95:
2616 $customer['source'] = 'Loyalty Widget';
2617 break;
2618 case 97:
2619 $customer['source'] = 'Free Trial';
2620 break;
2621 case 99:
2622 $customer['source'] = 'Freemium';
2623 break;
2624 case 101:
2625 $customer['source'] = 'Premium';
2626 break;
2627 case 102:
2628 $customer['source'] = 'MINDBODY';
2629 break;
2630 case 104:
2631 $customer['source'] = 'Landing Page Contact Form';
2632 break;
2633 case 106:
2634 $customer['source'] = 'SunLync';
2635 break;
2636 case 108:
2637 $customer['source'] = 'Email';
2638 break;
2639 case 111:
2640 $customer['source'] = 'TSK';
2641 break;
2642 default:
2643 $customer['source'] = 'Imported';
2644 }
2645 //$customer['source'] = "<span class=\"label label-success\">{$customer['source']}</span> <span class=\"label label-success\">{$customer['source']}</span> ";
2646 $dtRow[] = $customer['source'];
2647 $data['Source'] = $customer['source'];
2648 break;
2649 case 'Balance':
2650 $expDateUnix = strtotime( $row['expdate'] );
2651 $todayUnix = strtotime( $today );
2652 $balance = $row['store_credit_plus'] - $row['store_credit_minus'];
2653
2654 if ( $balance > 0 and $expDateUnix > $todayUnix ) {
2655 $balanceDigits = $balance;
2656 $balance = '$' . number_format( (float) $balance, 2, '.', '' );
2657 $timeLeft = $expDateUnix - $todayUnix;
2658 if ( $timeLeft < 86400 ) {
2659 $whenExp = 'today';
2660 } else {
2661 $daysLeft = floor( $timeLeft / 86400 );
2662 $whenExp = 'in ' . $daysLeft . ' days';
2663 }
2664 $expDatePR = date( 'm/d/Y', $expDateUnix );
2665 $exrPR = ' - Expires ' . $whenExp . ' (' . $expDatePR . ')';
2666 } else {
2667 $balanceDigits = 0;
2668 $balance = '$0.00';
2669 }
2670 $dtRow[] = '<a class="fancybox fancybox.iframe" href="customers_purchase.php?customerID=' . $customer['ID'] . '&t=c">' . $balance . '</a>';
2671 $data['Balance'] = $balance;
2672 break;
2673 case 'Last Visit':
2674 $lastVisit = strtotime( $row['visitdate'] );
2675 $customer['lastVisit'] = date( "m/d/Y", $lastVisit );
2676 if ( ! isset( $row['visitdate'] ) || ( $customer['lastVisit'] == '01/01/1970' || $customer['lastVisit'] == '12/31/1969' ) ) {
2677 $customer['lastVisit'] = '';
2678 }
2679 $dtRow[] = $customer['lastVisit'];
2680 $data['Last Visit'] = $row['visitdate'];
2681 break;
2682 case 'Last Purchased Date':
2683 $lastPurchasedDate = strtotime( $row['last_purchased_date'] );
2684 $lastPurchasedDate = date( "m/d/Y", $lastPurchasedDate );
2685 if ( ! isset( $row['last_purchased_date'] ) || ( $lastPurchasedDate == '01/01/1970' || $lastPurchasedDate == '12/31/1969' ) ) {
2686 $lastPurchasedDate = '';
2687 }
2688 $dtRow[] = $lastPurchasedDate;
2689 $data['Last Purchased Date'] = $row['last_purchased_date'];
2690 break;
2691
2692 case 'Last Purchased Service/Product':
2693 $lastPurchasedProduct = $row['last_purchased_product'];
2694 if ( ! isset( $row['last_purchased_product'] ) ) {
2695 $lastPurchasedProduct = '';
2696 }
2697 $dtRow[] = $lastPurchasedProduct;
2698 $data['Last Purchased Service/Product'] = $lastPurchasedProduct;
2699 break;
2700 case 'Age':
2701 if ( ! isset( $row['user_birthday'] ) ) {
2702 $customer['Age'] = '';
2703 } else {
2704 $customer['Age'] = $row['age_customer'];
2705 }
2706
2707 $dtRow[] = $customer['Age'];
2708 $data['Age'] = $customer['Age'];
2709 break;
2710 case 'Text Reply Received':
2711 $data['Text Reply Received'] = $row['smstext'];
2712 $dtRow[] = $row['smstext'];
2713 break;
2714 case 'Birthday':
2715 if ( ! isset( $row['user_birthday'] ) ) {
2716 $customer['Birthday'] = '';
2717 } else {
2718 $birthday = strtotime( $row['user_birthday'] );
2719 $customer['Birthday'] = date( "m/d/Y", $birthday );
2720 }
2721 $dtRow[] = $customer['Birthday'];
2722 $data['Birthday'] = $row['user_birthday'];
2723 break;
2724 case 'First Visit':
2725 $firstVisit = strtotime( $row['first_visit_date'] );
2726 $customer['firstVisit'] = date( "m/d/Y", $firstVisit );
2727 if ( ! isset( $row['first_visit_date'] ) || ( $customer['firstVisit'] == '01/01/1970' || $customer['firstVisit'] == '12/31/1969' ) ) {
2728 $customer['firstVisit'] = '';
2729 }
2730 $dtRow[] = $customer['firstVisit'];
2731 $data['First Visit'] = $row['first_visit_date'];
2732 break;
2733 case 'Visits':
2734 $dtRow[] = intval( $row['visits_count'] );
2735 $data['Visits'] = intval( $row['visits_count'] );
2736 break;
2737 case 'Loyalty Points':
2738 $dtRow[] = '<a class="fancybox fancybox.iframe" href="customers_loyalty.php?customerID=' . $customer['ID'] . '&t=c">' . intval( $row['loyalty_points'] ) . '</a>';
2739 $data['Loyalty Points'] = intval( $row['loyalty_points'] );
2740 break;
2741 case 'Offers':
2742 $dtRow[] = $row['invite_id_saved'];
2743 $data['Offers'] = $row['invite_id_saved'];
2744 break;
2745 case 'Registration Date':
2746 $regDate = strtotime( $row['registrationdate'] );
2747 $customer['registrationdate'] = date( "m/d/Y", $regDate );
2748 if ( ! isset( $row['registrationdate'] ) || ( $customer['registrationdate'] == '01/01/1970' || $customer['registrationdate'] == '12/31/1969' ) ) {
2749 $customer['registrationdate'] = '';
2750 }
2751 $dtRow[] = $customer['registrationdate'];
2752 $data['Registration Date'] = $row['registrationdate'];
2753 break;
2754 case 'Contract Expiration Date':
2755 $effectiveRegistrationDate = strtotime( $row['effective_expiration_date'] );
2756 $customer['effective_expiration_date'] = date( "m/d/Y", $effectiveRegistrationDate );
2757 if ( ! isset( $row['effective_expiration_date'] ) || ( $customer['effective_expiration_date'] == '01/01/1970' || $customer['effective_expiration_date'] == '12/31/1969' ) ) {
2758 $customer['effective_expiration_date'] = '';
2759 }
2760 $dtRow[] = $customer['effective_expiration_date'];
2761 $data['Contract Expiration Date'] = $row['effective_expiration_date'];
2762 break;
2763 case 'Contract Purchase Date':
2764 $memberPurchaseDate = strtotime( $row['member_purchase_date'] );
2765 $customer['member_purchase_date'] = date( "m/d/Y", $memberPurchaseDate );
2766 if ( ! isset( $row['member_purchase_date'] ) || ( $customer['member_purchase_date'] == '01/01/1970' || $customer['member_purchase_date'] == '12/31/1969' ) ) {
2767 $customer['member_purchase_date'] = '';
2768 }
2769 $dtRow[] = $customer['member_purchase_date'];
2770 $data['Contract Purchase Date'] = $row['member_purchase_date'];
2771 break;
2772 case 'Contract Payment Due Date':
2773 $paymentDueDate = strtotime( $row['payment_due_date'] );
2774 $customer['payment_due_date'] = date( "m/d/Y", $paymentDueDate );
2775 if ( ! isset( $row['payment_due_date'] ) || ( $customer['payment_due_date'] == '01/01/1970' || $customer['payment_due_date'] == '12/31/1969' ) ) {
2776 $customer['payment_due_date'] = '';
2777 }
2778 $dtRow[] = $customer['payment_due_date'];
2779 $data['Contract Payment Due Date'] = $row['payment_due_date'];
2780 break;
2781 case 'Email Unsubscribed':
2782 $data['Email Unsubscribed'] = $isUnsubscribedEmail ? "Yes" : "No";
2783 if($data['Email Unsubscribed'] == "Yes") {
2784 $data['Email Unsubscribed Date'] = $unsubscribedEmailDate;
2785 $data['Email Unsubscribed Source'] = $unsubscribedEmailSource;
2786 }
2787 $dtRow[] = $isUnsubscribedEmail ? "Yes" : "No";
2788 break;
2789 case 'Text Unsubscribed':
2790 $data['Text Unsubscribed'] = $isUnsubscribedPhone ? "Yes" : "No";
2791 if($data['Text Unsubscribed'] == "Yes") {
2792 $data['Text Unsubscribed Date'] = $unsubscribedTextDate;
2793 $data['Text Unsubscribed Source'] = $unsubscribedTextSource;
2794 }
2795 $dtRow[] = $isUnsubscribedPhone ? "Yes" : "No";
2796 break;
2797 case 'Mindbody Member':
2798 $isMboMember = $row['mbo_member'] == 't';
2799 $data['Mindbody Member'] = $isMboMember ? "Yes" : "No";
2800 $dtRow[] = $isMboMember ? "Yes" : "No";
2801 break;
2802 case 'Gender':
2803 $gender = $row['gender_value'] ;
2804 $data['Gender'] = $gender;
2805 $dtRow[] = $gender;
2806 break;
2807 case 'Type':
2808 $customerType = "";
2809 if ( $row['affiliate'] == 'true' ) {
2810 $customerType = 'Other';
2811 } else {
2812 switch ( $row['customertypeid'] ) {
2813 case 3:
2814 $customerType = 'Current Customers (VIP)';
2815 break;
2816 case 1:
2817 case 2:
2818 $customerType = 'Current Customers';
2819 break;
2820 case 4:
2821 case 5:
2822 $customerType = 'Past Customers';
2823 break;
2824 case 6:
2825 $customerType = 'Leads';
2826 break;
2827 }
2828 }
2829 $dtRow[] = $customerType;
2830 $data['Type'] = intval( $row['customertypeid'] );
2831 $data['legacy_type_label'] = $customerType;
2832 $data['affiliate'] = $row['affiliate'] == 'true';
2833 break;
2834 case 'Label':
2835
2836 $customerLabels = array_filter($labels, function($element) use ($row) {
2837 return $element['customerid'] == $row['customerid'];
2838 });
2839 $data['Labels'] = array();
2840 $labelsHTML = "";
2841 foreach ($customerLabels as $customerLabel) {
2842 if ( empty( $customerLabel['color'] ) ) {
2843 $customerLabel['color'] = $this->getLabelColorByName( $customerLabel['name'] );
2844 }
2845 array_push( $data['Labels'], array(
2846 'id' => intval( $customerLabel['customer_group_id'] ),
2847 'color' => $customerLabel['color'],
2848 'name' => $customerLabel['name']
2849 ) );
2850 if ( strlen( $customerLabel['name'] ) <= 16 ) {
2851 $labelsHTML .= "<span data-lid=\"{$customerLabel['customer_group_id']}\" class=\"label md-bg-{$customerLabel['color']}\">{$customerLabel['name']}</span> ";
2852 } else {
2853 $labelShorter = substr( $customerLabel['name'], 0, 16 ) . "...";
2854 $labelsHTML .= "<span data-lid=\"{$customerLabel['customer_group_id']}\" class=\"label md-bg-{$customerLabel['color']}\" title=\"{$customerLabel['name']}\">{$labelShorter}</span> ";
2855 }
2856 }
2857 $data['Label'] = $data['Labels'];
2858
2859 $dtRow[] = $labelsHTML;
2860 break;
2861 case 'Class':
2862 $customerClasses = array_filter($classes, function($element) use ($row) {
2863 return $element['customerid'] == $row['customerid'];
2864 });
2865 $data['Class'] = array();
2866 $classHTML = "";
2867 foreach ($customerClasses as $customerClass) {
2868 array_push( $data['Class'], $customerClass['title']);
2869 $classHTML = ", " . $customerClass['title'];
2870 }
2871
2872 $dtRow[] = $classHTML;
2873 break;
2874 case 'Appointment':
2875 $customerAppointments = array_filter($appointments, function($element) use ($row) {
2876 return $element['customerid'] == $row['customerid'];
2877 });
2878 $data['Appointment'] = array();
2879 $appointmentHTML = "";
2880 foreach ($customerAppointments as $customerAppointment) {
2881 array_push( $data['Appointment'], $customerAppointment['title']);
2882 $appointmentHTML = ", " . $customerAppointment['title'];
2883 }
2884
2885 $dtRow[] = $appointmentHTML;
2886 break;
2887 case 'Service':
2888 $customerServices = array_filter($services, function($element) use ($row) {
2889 return $element['customerid'] == $row['customerid'];
2890 });
2891 $data['Service'] = array();
2892 $serviceHTML = "";
2893 foreach ($customerServices as $customerService) {
2894 array_push( $data['Service'], $customerService['title']);
2895 $serviceHTML = ", " . $customerService['title'];
2896 }
2897
2898 $dtRow[] = $serviceHTML;
2899 break;
2900 case 'Purchase':
2901 $customerPurchases = array_filter($purchases, function($element) use ($row) {
2902 return $element['customerid'] == $row['customerid'];
2903 });
2904 $data['Purchase'] = array();
2905 $purchaseHTML = "";
2906 foreach ($customerPurchases as $customerPurchase) {
2907 array_push( $data['Purchase'], $customerPurchase['title']);
2908 $purchaseHTML = ", " . $customerPurchase['title'];
2909 }
2910
2911 $dtRow[] = $purchaseHTML;
2912 break;
2913 case 'Claimed Offer':
2914 $customerClaims = array_filter($claimedOffers, function($element) use ($row) {
2915 return $element['customerid'] == $row['customerid'];
2916 });
2917
2918 $filteredClaims = array();
2919 foreach ($customerClaims as $customerClaim) {
2920 array_push($filteredClaims, $customerClaim["voucher_title"]);
2921 }
2922 $filteredClaims = array_unique($filteredClaims);
2923
2924 $data['Claimed Offer'] = array();
2925 $claimedOffersHTML = "";
2926 foreach ($filteredClaims as $customerClaim) {
2927 array_push( $data['Claimed Offer'], $customerClaim);
2928 $claimedOffersHTML = ", " . $customerClaim;
2929 }
2930
2931 $dtRow[] = $claimedOffersHTML;
2932 break;
2933 case 'Contact Status':
2934 $data['Contact Status'] = $row['name'];
2935 $dtRow[] = $row['name'];
2936 break;
2937 case 'Last Modified Date':
2938 $lastModifiedDate = strtotime( $row['modified_date'] );
2939 $customer['last_modified_date'] = date( "m/d/Y", $lastModifiedDate);
2940 $dtRow[] = $customer['last_modified_date'];
2941 $data['Last Modified Date'] = $row['modified_date'];
2942 break;
2943 //tsk program type
2944 case 'Contract Program Type':
2945 $customerTskProgramTypes = array_filter($tskProgramType, function($element) use ($row) {
2946 return $element['customerid'] == $row['customerid'];
2947 });
2948 $data['Contract Program Type'] = array();
2949 $programTypeHTML = "";
2950 foreach ($customerTskProgramTypes as $customerTskProgramType) {
2951 array_push( $data['Contract Program Type'], $customerTskProgramType['title']);
2952 $programTypeHTML = ", " . $customerTskProgramType['title'];
2953 }
2954
2955 $dtRow[] = $programTypeHTML;
2956 break;
2957 //tsk age group
2958 case 'Contract Age Group':
2959 $customerTskAgeGroups = array_filter($tskAgeGroup, function($element) use ($row) {
2960 return $element['customerid'] == $row['customerid'];
2961 });
2962 $data['Contract Age Group'] = array();
2963 $ageHTML = "";
2964 foreach ($customerTskAgeGroups as $customerTskAgeGroup) {
2965 array_push( $data['Contract Age Group'], $customerTskAgeGroup['title']);
2966 $ageHTML = ", " . $customerTskAgeGroup['title'];
2967 }
2968
2969 $dtRow[] = $ageHTML;
2970 break;
2971 //tsk payment plan
2972 case 'Contract Payment Plan':
2973 $customerTskPaymentPlans = array_filter($tskPaymentPlan, function($element) use ($row) {
2974 return $element['customerid'] == $row['customerid'];
2975 });
2976 $data['Contract Payment Plan'] = array();
2977 $paymentHTML = "";
2978 foreach ($customerTskPaymentPlans as $customerTskPaymentPlan) {
2979 array_push( $data['Contract Payment Plan'], $customerTskPaymentPlan['title']);
2980 $paymentHTML = ", " . $customerTskPaymentPlan['title'];
2981 }
2982
2983 $dtRow[] = $paymentHTML;
2984 break;
2985 //tsk discount type
2986 case 'Contract Discount Type':
2987 $customerTskDiscountTypes = array_filter($tskDiscountType, function($element) use ($row) {
2988 return $element['customerid'] == $row['customerid'];
2989 });
2990 $data['Contract Discount Type'] = array();
2991 $discountHTML = "";
2992 foreach ($customerTskDiscountTypes as $customerTskDiscountType) {
2993 array_push( $data['Contract Discount Type'], $customerTskDiscountType['title']);
2994 $discountHTML = ", " . $customerTskDiscountType['title'];
2995 }
2996
2997 $dtRow[] = $discountHTML;
2998 break;
2999 case 'Last Attendance Status':
3000 $lastAttendanceStatus = $this->getAttendanceLabelById($row['last_attendance_status']);
3001 $customer['last_attendance_status'] = $lastAttendanceStatus;
3002 $dtRow[] = $customer['last_attendance_status'];
3003 $data['Last Attendance Status'] = $lastAttendanceStatus;
3004 break;
3005 default:
3006 $isAdded = false;
3007 for ( $i = 0; $i < $customColumnCount; $i ++ ) {
3008 if ( isset( $row["field_title$i"] ) && $column['name'] == $row["field_title$i"] ) {
3009 $data[ $column['name'] ] = isset( $row["field_value$i"] ) ?
3010 $row["field_type$i"] == 'number' ? intval($row["field_value$i"]) : $row["field_value$i"] : "";
3011
3012 $dtRow[] = isset( $row["field_value$i"] ) ? $row["field_value$i"] : "";
3013 $isAdded = true;
3014 break;
3015 }
3016 }
3017
3018 if ( $isAdded === false ) {
3019 $dtRow[] = "";
3020 }
3021
3022 }
3023 }
3024
3025 $customerType = null;
3026 if ( $row['affiliate'] == 'true' ) {
3027 $customerType = 'affiliates';
3028 } else {
3029 switch ( $row['customertypeid'] ) {
3030 case 1:
3031 case 2:
3032 case 3:
3033 $customerType = 'active';
3034 break;
3035 case 4:
3036 case 5:
3037 $customerType = 'passive';
3038 break;
3039 case 6:
3040 $customerType = 'potential';
3041 break;
3042 default:
3043 continue 2;
3044 }
3045 }
3046 // ACTION button
3047 $actionBtn =
3048 '<td class="action-td">
3049 <div class="btn-group action-button invisible">
3050 <a class="btn dropdown-toggle table-action" data-toggle="dropdown" href="#">Action <span class="caret"></span></a>
3051 <ul class="dropdown-menu pull-right action-menu">
3052 <li><a class="check-in-action" href="contacts_checkin.php?customerID=' . $customer['ID'] . '">Check In</a></li>';
3053 if ( ! empty( $row['user_phone'] ) && $ifFreemium != 't' ) {
3054 $actionBtn .= '<li><a class="sms-action" data-cid="' . $customer['ID'] . '" data-phone="' . $row['user_phone'] . '">SMS Messages</a></li>';
3055 }
3056
3057 $actionBtn .= '<li><a href="customers_edit.php?customerID=' . $customer['ID'] . '&fr=act&goto=contacts">Edit</a></li>
3058 <li><a href="javascript:confirmDelete(\'contacts_delete.php?customerID=' . $customer['ID'] . '&fr=act\')">Delete</a></li>';
3059
3060 if ( $customerType != 'potential' ) {
3061 $actionBtn .=
3062 '<li><a class="fancybox fancybox.iframe" href="customers_assign.php?customerID=' . $customer['ID'] . '&fr=act">Assign Vouchers</a></li>
3063 <li><a class="fancybox fancybox.iframe" href="customers_invite.php?customerID=' . $customer['ID'] . '&fr=act">Send Invite</a></li>';
3064 }
3065 if ( $masterLevel == 'master' ) {
3066 $actionBtn .= '<li><a class="fancybox4 fancybox.iframe" href="logging_history.php?id=' . $customer['ID'] . '&tbl=3&if_bus=f&in_acc=f">History</a></li>';
3067 }
3068 //$actionBtn .=
3069 // '<li><a class="fancybox4 fancybox.iframe" href="contacts_trans.php?customerID=' . $customer['ID'] . '">Views</a></li>';
3070 $actionBtn .=
3071 '<li><a class="fancybox fancybox.iframe" href="customers_unsubscribe.php?customerID=' . $customer['ID'] . '&fr=act">Subscription</a></li>';
3072 $actionBtn .=
3073 '</ul>
3074 </div>
3075 </td>';
3076 if ( $readOnly == 't' ) {
3077 $actionBtn = '';
3078 }
3079
3080 $dtRow[] = $actionBtn;
3081 $output['aaData'][] = $dtRow;
3082 array_push( $jsonData['results'], $data );
3083 } // while
3084 } else {
3085 if ( $fields == 'count' ) {
3086 $row = $result;
3087
3088 return (int) $row['total_count'];
3089 }
3090
3091 $customers = array();
3092 foreach ( $result as $row ) {
3093 $isUnsubscribedEmail = false;
3094 $isUnsubscribedPhone = false;
3095 if ( $include['Email'] === true || $include['Phone'] === true || $include['Email Unsubscribed'] === true || $include['Text Unsubscribed'] === true ) {
3096 $unsQuery = "SELECT uns_email, uns_phone, fromsystem FROM emailunsubscribe WHERE userid = {$row['userid']} AND (fromsystem = TRUE OR frombusinessid={$businessID} AND (uns_email=true OR uns_phone=true OR uns_mobile=true))";
3097 $resultUns = cache_aware_pg_query( $this->_database, $unsQuery );
3098 if ( ( ! empty( $resultUns ) ) && ( pg_numrows( $resultUns ) > 0 ) ) {
3099 while ( $unsRow = pg_fetch_assoc( $resultUns ) ) {
3100 if ( $unsRow['uns_email'] == 't' || $unsRow['fromsystem'] == 't' ) {
3101 $isUnsubscribedEmail = true;
3102 }
3103 if ( $unsRow['uns_phone'] == 't' || $unsRow['fromsystem'] == 't' ) {
3104 $isUnsubscribedPhone = true;
3105 }
3106 }
3107 }
3108 }
3109
3110 foreach ( $fields as $column ) {
3111 switch ( $column ) {
3112 case 'ID':
3113 $customer[ $column ] = $row['customerid'];
3114 break;
3115 case 'Md5h':
3116 $customer[ $column ] = $row['md5h'];
3117 break;
3118 case 'Name':
3119 $customer[ $column ] = $row['fullname'];
3120 break;
3121 case 'First Name':
3122 $customer[ $column ] = $row['firstname'];
3123 break;
3124 case 'Zip':
3125 $customer[ $column ] = $row['zip'];
3126 break;
3127 case 'Last Name':
3128 $customer[ $column ] = $row['lastname'];
3129 break;
3130 case 'Email':
3131 $customer['Email raw'] = $row['email'];
3132 $customer['Unsubscribed Email'] = $isUnsubscribedEmail;
3133 $customer[ $column ] = $isUnsubscribedEmail ? 'unsubscribed' : $row['email'];
3134 break;
3135 case 'Phone':
3136 $customer['Phone raw'] = "";
3137 $customer['Unsubscribed Phone'] = $isUnsubscribedPhone;
3138 if ( ! empty( $row['phone'] ) ) {
3139 $phone = $row['phone'];
3140 $customer['Phone raw'] = $phone;
3141 if ( $isUnsubscribedPhone ) {
3142 $phone = 'unsubscribed';
3143 }
3144 $customer[ $column ] = $phone;
3145 } else {
3146 $customer[ $column ] = "";
3147 }
3148 break;
3149 case 'Source':
3150 switch ( $row['registrationtypeid'] ) {
3151 case 11:
3152 $customer['Source'] = 'Imported';
3153 break;
3154 case 10:
3155 $customer['Source'] = 'Invited';
3156 break;
3157 case 9:
3158 $customer['Source'] = 'Web App'; //Via Web-app
3159 break;
3160 case 17:
3161 case 35:
3162 $customer['Source'] = 'Landing Page';
3163 break;
3164 case 21:
3165 $customer['Source'] = 'Customers Page';
3166 break;
3167 case 22:
3168 $customer['Source'] = 'Search Field';
3169 break;
3170 case 23:
3171 $customer['Source'] = 'Master';
3172 break;
3173 case 24:
3174 $customer['Source'] = 'MINDBODY';
3175 break;
3176 case 28:
3177 $customer['Source'] = 'Call In';
3178 break;
3179 case 30:
3180 $customer['Source'] = 'Backend Redeem';
3181 break;
3182 case 32:
3183 $customer['Source'] = 'Pike13';
3184 break;
3185 case 34:
3186 $customer['Source'] = 'Text Sent';
3187 break;
3188 case 35:
3189 $customer['Source'] = 'Landing Page Claim';
3190 break;
3191 case 37:
3192 $customer['Source'] = 'Zapier';
3193 break;
3194 case 39:
3195 $customer['Source'] = 'Website Widget';
3196 break;
3197 case 70:
3198 $customer['Source'] = 'Reviews Widget';
3199 break;
3200 case 72:
3201 $customer['source'] = 'Landing Page Subscribe';
3202 break;
3203 case 74:
3204 $customer['source'] = 'ASF Payment Solutions';
3205 break;
3206 case 81:
3207 $customer['source'] = 'Booker';
3208 break;
3209 case 83:
3210 $customer['source'] = 'Mailchimp';
3211 break;
3212 case 85:
3213 $customer['source'] = 'Gmail';
3214 break;
3215 case 87:
3216 $customer['source'] = 'Constant Contact';
3217 break;
3218 case 89:
3219 $customer['source'] = 'Quickbooks';
3220 break;
3221 case 91:
3222 $customer['source'] = 'Yahoo';
3223 break;
3224 case 93:
3225 $customer['source'] = 'Glofox';
3226 break;
3227 case 95:
3228 $customer['source'] = 'Loyalty Widget';
3229 break;
3230 case 97:
3231 $customer['source'] = 'Free Trial';
3232 break;
3233 case 99:
3234 $customer['source'] = 'Freemium';
3235 break;
3236 case 101:
3237 $customer['source'] = 'Premium';
3238 break;
3239 case 102:
3240 $customer['source'] = 'MINDBODY';
3241 break;
3242 case 104:
3243 $customer['source'] = 'Landing Page Contact Form';
3244 break;
3245 case 106:
3246 $customer['source'] = 'SunLync';
3247 break;
3248 case 108:
3249 $customer['source'] = 'Email';
3250 break;
3251 default:
3252 $customer['Source'] = 'Imported';
3253 }
3254 break;
3255 case 'Balance':
3256 $expDateUnix = strtotime( $row['expdate'] );
3257 $todayUnix = strtotime( $today );
3258 $balance = $row['store_credit_plus'] - $row['store_credit_minus'];
3259
3260 if ( $balance > 0 and $expDateUnix > $todayUnix ) {
3261 $balanceDigits = $balance;
3262 $balance = '$' . number_format( (float) $balance, 2, '.', '' );
3263 $timeLeft = $expDateUnix - $todayUnix;
3264 if ( $timeLeft < 86400 ) {
3265 $whenExp = 'today';
3266 } else {
3267 $daysLeft = floor( $timeLeft / 86400 );
3268 $whenExp = 'in ' . $daysLeft . ' days';
3269 }
3270 $expDatePR = date( 'm/d/Y', $expDateUnix );
3271 $exrPR = ' - Expires ' . $whenExp . ' (' . $expDatePR . ')';
3272 } else {
3273 $balanceDigits = 0;
3274 $balance = '$0.00';
3275 }
3276 $customer[ $column ] = $balance;
3277 break;
3278 case 'Last Visit':
3279 $lastVisit = strtotime( $row['visitdate'] );
3280 $customer[ $column ] = date( "m/d/Y", $lastVisit );
3281 if ( ! isset( $row['visitdate'] ) || ( $customer[ $column ] == '01/01/1970' || $customer[ $column ] == '12/31/1969' ) ) {
3282 $customer[ $column ] = '';
3283 }
3284 break;
3285 case 'First Visit':
3286 $firstVisit = strtotime( $row['first_visit_date'] );
3287 $customer[ $column ] = date( "m/d/Y", $firstVisit );
3288 if ( ! isset( $row['first_visit_date'] ) || ( $customer[ $column ] == '01/01/1970' || $customer[ $column ] == '12/31/1969' ) ) {
3289 $customer[ $column ] = '';
3290 }
3291 break;
3292 case 'Visits':
3293 $customer[ $column ] = intval( $row['visits_count'] );
3294 break;
3295 case 'Loyalty Points':
3296 $customer[ $column ] = intval( $row['loyalty_points'] );
3297 break;
3298
3299
3300 }
3301 }
3302
3303 array_push( $customers, $customer );
3304 } // while
3305
3306 return $customers;
3307 }
3308 }
3309
3310
3311 if ( $jsonResponse ) {
3312 return $jsonData;
3313 }
3314
3315 if ( isset( $dataTablesGET ) ) {
3316 return $output;
3317 } else {
3318 if ( $fields == 'count' ) {
3319 return 0;
3320 } else {
3321 return array();
3322 }
3323 }
3324 }
3325
3326 public function getColumns( $viewid ) {
3327 $query = "SELECT CASE WHEN c.columnid IS NULL THEN uf.title ELSE o.value END AS name
3328 FROM customersview_column c
3329 LEFT JOIN option o ON c.columnid = o.optionid
3330 LEFT JOIN users_field as uf ON uf.users_field_id = c.users_field_id
3331 WHERE c.customersviewid = $viewid
3332 ORDER BY c.columnorder";
3333
3334 $response = $this->query( $query );
3335 $columns = array();
3336 if ( is_array( $response ) || is_object( $response ) ) {
3337 foreach ( $response as $r ) {
3338 $column = array(
3339 'name' => $r['name']
3340 );
3341 array_push( $columns, $column );
3342 }
3343 }
3344
3345
3346 return $columns;
3347 }
3348
3349
3350 public function addQueries( $accountID, $businessID, $allFilters, $selectQuery, $whereQuery, $havingQuery, $joinQuery, $include, $isAND, $servicesCount, $labelsCount, $customFieldsCount, $multiLocationLoyalty ) {
3351
3352
3353 $today = date( 'Y-m-d H:i:s', time());
3354 $yesterday = date( 'Y-m-d H:i:s', strtotime( " -1 day "));
3355 $before7days = date( 'Y-m-d H:i:s', strtotime( "- 7 day" ) );
3356 $before30days = date( 'Y-m-d H:i:s', strtotime( "- 30 day" ) );
3357 $before60days = date( 'Y-m-d H:i:s', strtotime( "- 60 day" ) );
3358 $before90days = date( 'Y-m-d H:i:s', strtotime( "- 90 day" ) );
3359 $next7days = date( 'Y-m-d H:i:s', strtotime( "+ 7 day" ) );
3360 $customerDoesNotBelongToService = false;
3361
3362 $dateStart = date( 'Y-m-d 00:00:00');
3363 $dateEnd = date( 'Y-m-d 23:59:59');
3364
3365 $todayStart = date( 'Y-m-d H:i:s', strtotime($dateStart));
3366 $todayEnd = date( 'Y-m-d H:i:s', strtotime($dateEnd));
3367 $yesterdayStart = date( 'Y-m-d H:i:s', strtotime( "-1 day ", strtotime($dateStart)));
3368 $yesterdayEnd = date( 'Y-m-d H:i:s', strtotime( "-1 day ", strtotime($dateEnd)));
3369
3370
3371 if ( ! $isAND ) {
3372 $whereQuery .= $this->endsWith($whereQuery, " AND ") ? "( " : " AND ( ";
3373 } else {
3374 $whereQuery .= $this->endsWith($whereQuery, " AND ") ? "" : " AND ";
3375 }
3376
3377 $services = array();
3378 $labels = array();
3379 $mainOpTemp = $mainOp = $isAND ? " AND " : " OR ";
3380 $isFirst = true;
3381 $tskOptions = array();
3382 $isFirstTsk = true;
3383 foreach ( $allFilters as $row ) {
3384 $columnname = $row['columnname'];
3385 $operator = $row['operator'];
3386 $value = $row['filtervalue'];
3387 $valueLower = strtolower( $value );
3388 if ( array_key_exists( $columnname, $include ) && $include[ $columnname ] === false ) {
3389 $include[ $columnname ] = true;
3390 }
3391
3392 $mainOp = $isFirst ? "" : $mainOpTemp;
3393 $isFirst = false;
3394 switch ( $columnname ) {
3395 case 'Name':
3396 if ( $operator == 'contains' ) {
3397 $whereQuery .= $mainOp . " lower(concat_ws(' ', u.firstname, u.middlename, u.lastname)) LIKE '%$valueLower%' ";
3398 } elseif ( $operator == 'equals' ) {
3399 $whereQuery .= $mainOp . " lower(concat_ws(' ', u.firstname, u.middlename, u.lastname)) = '$valueLower' ";
3400 }
3401 break;
3402 case 'First Name':
3403 if ( $operator == 'contains' ) {
3404 $whereQuery .= $mainOp . " lower(u.firstname) LIKE '%$valueLower%' ";
3405 } elseif ( $operator == 'equals' ) {
3406 $whereQuery .= $mainOp . " lower(u.firstname) = '$valueLower' ";
3407 }
3408
3409 if ( $include[ $columnname ] === false ) {
3410 $selectQuery .= ", u.firstname";
3411 $include[ $columnname ] = true;
3412 }
3413 break;
3414 case 'Last Name':
3415 if ( $operator == 'contains' ) {
3416 $whereQuery .= $mainOp . " lower(u.lastname) LIKE '%$valueLower%' ";
3417 } elseif ( $operator == 'equals' ) {
3418 $whereQuery .= $mainOp . " lower(u.lastname) = '$valueLower' ";
3419 }
3420
3421 if ( $include[ $columnname ] === false ) {
3422 $selectQuery .= ", u.lastname";
3423 $include[ $columnname ] = true;
3424 }
3425 break;
3426 case 'Text Reply Received':
3427 if ( $operator == 'equals' ) {
3428 $valueLower = trim($valueLower);
3429 $whereQuery .= $mainOp . " (select count(s) from sms s where s.smstypeid = 6 and s.userid = c.userid and s.accountid = c.accountid and lower((regexp_replace(s.smstext, E'[\\n\\r]+', ' ', 'g' ))) = '$valueLower' ) > 0 ";
3430 $selectQuery .= " , '$valueLower' as smsText ";
3431 }
3432 break;
3433 case 'Email':
3434 if ( $operator == 'contains' ) {
3435 $whereQuery .= $mainOp . " e.email LIKE '%$valueLower%' ";
3436 } elseif ( $operator == 'equals' ) {
3437 if ( $value == 'empty' ) {
3438 $whereQuery .= $mainOp . " (e.email IS NULL OR e.email = '') ";
3439 } else {
3440 $whereQuery .= $mainOp . " e.email = '$valueLower' ";
3441 }
3442 } elseif ( $operator == 'empty' ) {
3443 $whereQuery .= $mainOp . " (e.email IS NULL OR e.email = '') ";
3444 }
3445 break;
3446 case 'Phone':
3447 if ( $operator == 'contains' ) {
3448 $whereQuery .= $mainOp . " u.phone LIKE '%$value%' ";
3449 } elseif ( $operator == 'equals' ) {
3450 if ( $value == 'empty' ) {
3451 $whereQuery .= $mainOp . " (u.phone IS NULL OR u.phone = '') ";
3452 } else {
3453 $whereQuery .= $mainOp . " u.phone = '$value' ";
3454 }
3455 } elseif ( $operator == 'empty' ) {
3456 $whereQuery .= $mainOp . " (u.phone IS NULL OR u.phone = '') ";
3457 }
3458 break;
3459 case 'Source':
3460 if ( $operator == 'equals' ) {
3461 $whereQuery .= $mainOp . " c.registrationtypeid = $value";
3462 } elseif ( $operator == 'not equal to' ) {
3463 $whereQuery .= $mainOp . " c.registrationtypeid != $value";
3464 }
3465 break;
3466 case 'Balance':
3467 switch ( $operator ) {
3468 case 'equals':
3469 $whereQuery .= $mainOp . " '$today' < credit.expdate AND ( COALESCE( credit.store_credit_plus, 0 ) - COALESCE( credit.store_credit_minus, 0 ) ) = $value ";
3470 break;
3471 case 'greater than':
3472 $whereQuery .= $mainOp . " '$today' < credit.expdate AND ( COALESCE( credit.store_credit_plus, 0 ) - COALESCE( credit.store_credit_minus, 0 )) > $value ";
3473 break;
3474 case 'less than':
3475 $whereQuery .= $mainOp . " '$today' < credit.expdate AND ( COALESCE( credit.store_credit_plus, 0 ) - COALESCE( credit.store_credit_minus, 0 )) < $value ";
3476 }
3477 break;
3478 case 'Loyalty Points':
3479 if ($multiLocationLoyalty) {
3480 if (!$include['Loyalty Points']) {
3481 $joinQuery .= " JOIN customer c_loyalty ON c_loyalty.userid = u.userid ";
3482 }
3483 $havingQuery .= " AND " . $mainOp . " sum(c_loyalty.loyalty_points_count) ";
3484 } else {
3485 $havingQuery .= " AND " . $mainOp . " c.loyalty_points_count ";
3486 }
3487 switch ( $operator ) {
3488 case 'equals':
3489 $havingQuery .= "= $value ";
3490 break;
3491 case 'greater than':
3492 $havingQuery .= "> $value ";
3493 break;
3494 case 'less than':
3495 $havingQuery .= "< $value ";
3496 }
3497 //$isFirst = true;
3498 break;
3499 case 'First Visit':
3500 switch ( $operator ) {
3501 case 'last 7 days':
3502 $whereQuery .= $mainOp . " c.first_visit::date > '$before7days'";
3503 break;
3504 case 'last 30 days':
3505 $whereQuery .= $mainOp . " c.first_visit::date > '$before30days'";
3506 break;
3507 case 'last 60 days':
3508 $whereQuery .= $mainOp . " c.first_visit::date > '$before60days'";
3509 break;
3510 case 'last 90 days':
3511 $whereQuery .= $mainOp . " c.first_visit::date > '$before90days'";
3512 break;
3513 case 'today':
3514 $whereQuery .= $mainOp . " c.first_visit BETWEEN '$todayStart' AND '$todayEnd'";
3515 break;
3516 case 'yesterday':
3517 $whereQuery .= $mainOp . " c.first_visit BETWEEN '$yesterdayStart' AND '$yesterdayEnd'";
3518 break;
3519 default:
3520 $dateValue = date('Y-m-d H:i:s', strtotime("- $value day"));
3521 $op = "";
3522 switch ($operator) {
3523 case 'equals':
3524 $op = "=";
3525 break;
3526 case 'greater than':
3527 $op = "<";
3528 break;
3529 case 'less than':
3530 $op = ">";
3531 }
3532 $whereQuery .= $mainOp . " c.first_visit::date $op '$dateValue'";
3533
3534 }
3535 break;
3536 case 'Last Visit':
3537 switch ( $operator ) {
3538 case 'last 7 days':
3539 $whereQuery .= $mainOp . " c.last_visit::date > '$before7days'";
3540 break;
3541 case 'last 30 days':
3542 $whereQuery .= $mainOp . " c.last_visit::date > '$before30days'";
3543 break;
3544 case 'last 60 days':
3545 $whereQuery .= $mainOp . " c.last_visit::date > '$before60days'";
3546 break;
3547 case 'last 90 days':
3548 $whereQuery .= $mainOp . " c.last_visit::date > '$before90days'";
3549 break;
3550 case 'today':
3551 $whereQuery .= $mainOp . " c.last_visit BETWEEN '$todayStart' AND '$todayEnd'";
3552 break;
3553 case 'yesterday':
3554 $whereQuery .= $mainOp . " c.last_visit BETWEEN '$yesterdayStart' AND '$yesterdayEnd'";
3555 break;
3556 default:
3557 $dateValue = date( 'Y-m-d H:i:s', strtotime( "- $value day" ) );
3558 $op = "";
3559 switch ( $operator ) {
3560 case 'equals':
3561 $op = "=";
3562 break;
3563 case 'greater than':
3564 $op = "<";
3565 break;
3566 case 'less than':
3567 $op = ">";
3568 }
3569 $whereQuery .= $mainOp . " c.last_visit::date $op '$dateValue'";
3570 }
3571 $include[ $columnname ] = true;
3572 break;
3573 case 'Registration Date':
3574 switch ( $operator ) {
3575 case 'last 7 days':
3576 $whereQuery .= $mainOp . " c.registrationdate::date > '$before7days'";
3577 break;
3578 case 'last 30 days':
3579 $whereQuery .= $mainOp . " c.registrationdate::date > '$before30days'";
3580 break;
3581 case 'last 60 days':
3582 $whereQuery .= $mainOp . " c.registrationdate::date > '$before60days'";
3583 break;
3584 case 'last 90 days':
3585 $whereQuery .= $mainOp . " c.registrationdate::date > '$before90days'";
3586 break;
3587 case 'today':
3588 $whereQuery .= $mainOp . " c.registrationdate BETWEEN '$todayStart' AND '$todayEnd'";
3589 break;
3590 case 'yesterday':
3591 $whereQuery .= $mainOp . " c.registrationdate BETWEEN '$yesterdayStart' AND '$yesterdayEnd'";
3592 break;
3593 default:
3594 $dateValue = date( 'Y-m-d H:i:s', strtotime( "- $value day" ) );
3595 $op = "";
3596 switch ( $operator ) {
3597 case 'equals':
3598 $op = "=";
3599 break;
3600 case 'greater than':
3601 $op = "<";
3602 break;
3603 case 'less than':
3604 $op = ">";
3605 }
3606 $whereQuery .= $mainOp . " c.registrationdate::date $op '$dateValue'";
3607
3608 }
3609 $include[ $columnname ] = true;
3610 break;
3611 case 'Contract Expiration Date':
3612 $mainExpQuery = "(select effective_expiration_date from tsk_contract where student_id in (select field::bigint as student_id from users_field_user ufu left join users_field uf on ufu.users_field_id = uf.users_field_id where ufu.userid = u.userid and ufu.users_field_id = 1772) order by contract_id DESC limit 1 )::date ";
3613 switch ( $operator ) {
3614 case 'last 7 days':
3615 $whereQuery .= $mainOp . " $mainExpQuery > '$before7days' AND $mainExpQuery <= '$today' ";
3616 break;
3617 case 'last 30 days':
3618 $whereQuery .= $mainOp . " $mainExpQuery > '$before30days' AND $mainExpQuery <= '$today' ";
3619 break;
3620 case 'last 60 days':
3621 $whereQuery .= $mainOp . " $mainExpQuery > '$before60days' AND $mainExpQuery <= '$today' ";
3622 break;
3623 case 'last 90 days':
3624 $whereQuery .= $mainOp . " $mainExpQuery > '$before90days' AND $mainExpQuery <= '$today' ";
3625 break;
3626 case 'today':
3627 $whereQuery .= $mainOp . " (select effective_expiration_date from tsk_contract where student_id in (select field::bigint as student_id from users_field_user ufu left join users_field uf on ufu.users_field_id = uf.users_field_id where ufu.userid = u.userid and ufu.users_field_id = 1772) order by member_purchase_date DESC limit 1 ) BETWEEN '$todayStart' AND '$todayEnd'";
3628 break;
3629 case 'yesterday':
3630 $whereQuery .= $mainOp . " (select effective_expiration_date from tsk_contract where student_id in (select field::bigint as student_id from users_field_user ufu left join users_field uf on ufu.users_field_id = uf.users_field_id where ufu.userid = u.userid and ufu.users_field_id = 1772) order by member_purchase_date DESC limit 1 ) BETWEEN '$yesterdayStart' AND '$todayEnd'";
3631 break;
3632 default:
3633 $dateValue = date( 'Y-m-d H:i:s', strtotime( "- $value day" ) );
3634 $op = "";
3635 switch ( $operator ) {
3636 case 'equals':
3637 $op = "=";
3638 break;
3639 case 'greater than':
3640 $op = "<";
3641 break;
3642 case 'less than':
3643 $op = ">";
3644 }
3645 $whereQuery .= $mainOp . " (select effective_expiration_date from tsk_contract where student_id in (select field::bigint as student_id from users_field_user ufu left join users_field uf on ufu.users_field_id = uf.users_field_id where ufu.userid = u.userid and ufu.users_field_id = 1772) order by member_purchase_date DESC limit 1 )::date $op '$dateValue'";
3646
3647 }
3648 $include[ $columnname ] = true;
3649 break;
3650 case 'Contract Purchase Date':
3651 $mainPurchaseQuery = "(select member_purchase_date from tsk_contract where student_id in (select field::bigint as student_id from users_field_user ufu left join users_field uf on ufu.users_field_id = uf.users_field_id where ufu.userid = u.userid and ufu.users_field_id = 1772) order by member_purchase_date DESC limit 1)::date ";
3652 switch ( $operator ) {
3653 case 'last 7 days':
3654 $whereQuery .= $mainOp . " $mainPurchaseQuery > '$before7days' AND $mainPurchaseQuery <= '$today' ";
3655 break;
3656 case 'last 30 days':
3657 $whereQuery .= $mainOp . " $mainPurchaseQuery > '$before30days' AND $mainPurchaseQuery <= '$today' ";
3658 break;
3659 case 'last 60 days':
3660 $whereQuery .= $mainOp . " $mainPurchaseQuery > '$before60days' AND $mainPurchaseQuery <= '$today' ";
3661 break;
3662 case 'last 90 days':
3663 $whereQuery .= $mainOp . " $mainPurchaseQuery > '$before90days' AND $mainPurchaseQuery <= '$today' ";
3664 break;
3665 case 'today':
3666 $whereQuery .= $mainOp . " (select member_purchase_date from tsk_contract where student_id in (select field::bigint as student_id from users_field_user ufu left join users_field uf on ufu.users_field_id = uf.users_field_id where ufu.userid = u.userid and ufu.users_field_id = 1772) order by member_purchase_date DESC limit 1 ) BETWEEN '$todayStart' AND '$todayEnd'";
3667 break;
3668 case 'yesterday':
3669 $whereQuery .= $mainOp . " (select member_purchase_date from tsk_contract where student_id in (select field::bigint as student_id from users_field_user ufu left join users_field uf on ufu.users_field_id = uf.users_field_id where ufu.userid = u.userid and ufu.users_field_id = 1772) order by member_purchase_date DESC limit 1 ) BETWEEN '$yesterdayStart' AND '$todayEnd'";
3670 break;
3671 default:
3672 $dateValue = date( 'Y-m-d H:i:s', strtotime( "- $value day" ) );
3673 $op = "";
3674 switch ( $operator ) {
3675 case 'equals':
3676 $op = "=";
3677 break;
3678 case 'greater than':
3679 $op = "<";
3680 break;
3681 case 'less than':
3682 $op = ">";
3683 }
3684 $whereQuery .= $mainOp . " (select member_purchase_date from tsk_contract where student_id in (select field::bigint as student_id from users_field_user ufu left join users_field uf on ufu.users_field_id = uf.users_field_id where ufu.userid = u.userid and ufu.users_field_id = 1772) order by member_purchase_date DESC limit 1 )::date $op '$dateValue'";
3685
3686 }
3687 $include[ $columnname ] = true;
3688 break;
3689 case 'Contract Payment Due Date':
3690 $mainDueDateQuery = "(select payment_due_date from tsk_contract where student_id in (select field::bigint as student_id from users_field_user ufu left join users_field uf on ufu.users_field_id = uf.users_field_id where ufu.userid = u.userid and ufu.users_field_id = 1772) order by member_purchase_date DESC limit 1)::date ";
3691
3692 switch ( $operator ) {
3693 case 'last 7 days':
3694 $whereQuery .= $mainOp . " $mainDueDateQuery > '$before7days' AND $mainDueDateQuery <= '$today' ";
3695 break;
3696 case 'last 30 days':
3697 $whereQuery .= $mainOp . " $mainDueDateQuery > '$before30days' AND $mainDueDateQuery <= '$today' ";
3698 break;
3699 case 'last 60 days':
3700 $whereQuery .= $mainOp . " $mainDueDateQuery > '$before60days' AND $mainDueDateQuery <= '$today' ";
3701 break;
3702 case 'last 90 days':
3703 $whereQuery .= $mainOp . " $mainDueDateQuery > '$before90days' AND $mainDueDateQuery <= '$today' ";
3704 break;
3705 case 'today':
3706 $whereQuery .= $mainOp . " (select payment_due_date from tsk_contract where student_id in (select field::bigint as student_id from users_field_user ufu left join users_field uf on ufu.users_field_id = uf.users_field_id where ufu.userid = u.userid and ufu.users_field_id = 1772) order by member_purchase_date DESC limit 1 ) BETWEEN '$todayStart' AND '$todayEnd'";
3707 break;
3708 case 'yesterday':
3709 $whereQuery .= $mainOp . " (select payment_due_date from tsk_contract where student_id in (select field::bigint as student_id from users_field_user ufu left join users_field uf on ufu.users_field_id = uf.users_field_id where ufu.userid = u.userid and ufu.users_field_id = 1772) order by member_purchase_date DESC limit 1 ) BETWEEN '$yesterdayStart' AND '$todayEnd'";
3710 break;
3711 default:
3712 $dateValue = date( 'Y-m-d H:i:s', strtotime( "- $value day" ) );
3713 $op = "";
3714 switch ( $operator ) {
3715 case 'equals':
3716 $op = "=";
3717 break;
3718 case 'greater than':
3719 $op = "<";
3720 break;
3721 case 'less than':
3722 $op = ">";
3723 }
3724 $whereQuery .= $mainOp . " (select payment_due_date from tsk_contract where student_id in (select field::bigint as student_id from users_field_user ufu left join users_field uf on ufu.users_field_id = uf.users_field_id where ufu.userid = u.userid and ufu.users_field_id = 1772) order by member_purchase_date DESC limit 1 )::date $op '$dateValue'";
3725
3726 }
3727 $include[ $columnname ] = true;
3728 break;
3729 case 'Visits':
3730 switch ( $operator ) {
3731 case 'equals':
3732 $op = "=";
3733 break;
3734 case 'greater than':
3735 $op = ">";
3736 break;
3737 case 'less than':
3738 $op = "<";
3739 }
3740 $havingQuery .= " AND " . $mainOp . " c.visits_count $op $value";
3741 $include[ $columnname ] = true;
3742 //$isFirst = true;
3743 break;
3744 case 'Contact Status':
3745 if ( $operator == 'equals' ) {
3746 $whereQuery .= $mainOp . " cst.contact_status_id = $value";
3747 } elseif ( $operator == 'not equal to' ) {
3748 $whereQuery .= $mainOp . " cst.contact_status_id != $value";
3749 }
3750 break;
3751 case 'Claimed Offer':
3752 if ( $operator == 'equals' ) {
3753 $whereQuery .= $mainOp . " (select count(*) from invite where friendid = c.userid and statusid in (43, 44) and voucherid = $value) > 0 ";
3754 } elseif ( $operator == 'not equal to' ) {
3755 $whereQuery .= $mainOp . " (select count(*) from invite where friendid = c.userid and statusid in (43, 44) and voucherid = $value) < 1 ";
3756 }
3757 break;
3758 case 'Service':
3759 case 'Class':
3760 case 'Appointment':
3761 case 'Purchase':
3762 case 'Last Purchased Service/Product':
3763 array_push($services, $row);
3764 //$isFirst = true;
3765 if($row['operator'] !== "equals") { $customerDoesNotBelongToService = true;}
3766 break;
3767 case 'Contract Program Type':
3768 case 'Contract Age Group':
3769 case 'Contract Payment Plan':
3770 case 'Contract Discount Type':
3771 array_push($tskOptions, $row);
3772 //$isFirst = true;
3773 if($row['operator'] !== "equals") { $customerDoesNotBelongToService = true;}
3774 break;
3775 case 'Last Purchased Date':
3776 switch ( $operator ) {
3777 case 'last 7 days':
3778 $whereQuery .= $mainOp . " (select count(account_app_service_purchase_id) from account_app_service_purchase where account_app_service_customer_id in (select account_app_service_customer_id from account_app_service_customer where customerid = c.customerid) and date::date > '$before7days' ) > 0 ";
3779 break;
3780 case 'last 30 days':
3781 $whereQuery .= $mainOp . " (select count(account_app_service_purchase_id) from account_app_service_purchase where account_app_service_customer_id in (select account_app_service_customer_id from account_app_service_customer where customerid = c.customerid) and date::date > '$before30days' ) > 0 ";
3782 break;
3783 case 'last 60 days':
3784 $whereQuery .= $mainOp . " (select count(account_app_service_purchase_id) from account_app_service_purchase where account_app_service_customer_id in (select account_app_service_customer_id from account_app_service_customer where customerid = c.customerid) and date::date > '$before60days' ) > 0 ";
3785 break;
3786 case 'last 90 days':
3787 $whereQuery .= $mainOp . " (select count(account_app_service_purchase_id) from account_app_service_purchase where account_app_service_customer_id in (select account_app_service_customer_id from account_app_service_customer where customerid = c.customerid) and date::date > '$before90days' ) > 0 ";
3788 break;
3789 case 'today':
3790 $whereQuery .= $mainOp . " (select count(account_app_service_purchase_id) from account_app_service_purchase where account_app_service_customer_id in (select account_app_service_customer_id from account_app_service_customer where customerid = c.customerid) and date BETWEEN '$todayStart' AND '$todayEnd' ) > 0 ";
3791 break;
3792 case 'yesterday':
3793 $whereQuery .= $mainOp . " (select count(account_app_service_purchase_id) from account_app_service_purchase where account_app_service_customer_id in (select account_app_service_customer_id from account_app_service_customer where customerid = c.customerid) and date BETWEEN '$yesterdayStart' AND '$yesterdayEnd' ) > 0 ";
3794 break;
3795 default:
3796 $dateValue = date( 'Y-m-d H:i:s', strtotime( "- $value day" ) );
3797 $op = "";
3798 switch ( $operator ) {
3799 case 'equals':
3800 $op = "=";
3801 break;
3802 case 'greater than':
3803 $op = "<";
3804 break;
3805 case 'less than':
3806 $op = ">";
3807 }
3808 $whereQuery .= $mainOp . " (select count(account_app_service_purchase_id) from account_app_service_purchase where account_app_service_customer_id in (select account_app_service_customer_id from account_app_service_customer where customerid = c.customerid) and date::date $op '$dateValue' ) > 0 ";
3809 }
3810 $include[ $columnname ] = true;
3811 break;
3812 case 'Last Modified Date':
3813 switch ( $operator ) {
3814 case 'last 7 days':
3815 $whereQuery .= $mainOp . " u.modified_date::date > '$before7days'";
3816 break;
3817 case 'last 30 days':
3818 $whereQuery .= $mainOp . " u.modified_date::date > '$before30days'";
3819 break;
3820 case 'last 60 days':
3821 $whereQuery .= $mainOp . " u.modified_date::date > '$before60days'";
3822 break;
3823 case 'last 90 days':
3824 $whereQuery .= $mainOp . " u.modified_date::date > '$before90days'";
3825 break;
3826 case 'today':
3827 $whereQuery .= $mainOp . " u.modified_date BETWEEN '$todayStart' AND '$todayEnd'";
3828 break;
3829 case 'yesterday':
3830 $whereQuery .= $mainOp . " u.modified_date BETWEEN '$yesterdayStart' AND '$yesterdayEnd'";
3831 break;
3832 default:
3833 $dateValue = date('Y-m-d H:i:s', strtotime("- $value day"));
3834 $op = "";
3835 switch ($operator) {
3836 case 'equals':
3837 $op = "=";
3838 break;
3839 case 'greater than':
3840 $op = "<";
3841 break;
3842 case 'less than':
3843 $op = ">";
3844 }
3845 $whereQuery .= $mainOp . " u.modified_date::date $op '$dateValue'";
3846
3847 }
3848 break;
3849 case 'Label':
3850 array_push($labels, $row);
3851 //$isFirst = true;
3852 break;
3853 case 'Age':
3854 $dateValue = date( 'Y-m-d H:i:s', strtotime( "- $value years" ) );
3855 $op = "";
3856
3857 switch ( $operator ) {
3858 case 'equals':
3859 $op = "=";
3860 break;
3861 case 'greater than':
3862 $op = "<";
3863 break;
3864 case 'less than':
3865 $op = ">";
3866 }
3867 if ( $op == '=' ) {
3868 $whereQuery .= $mainOp . " EXTRACT(YEAR from AGE(u.birthday::date)) $op '$value'";
3869 } else {
3870 $whereQuery .= $mainOp . " u.birthday::date $op '$dateValue'";
3871 }
3872 $include[ $columnname ] = true;
3873
3874 break;
3875 case 'Birthday':
3876 $op = "";
3877 switch ( $operator ) {
3878 case 'today':
3879 $op = "=";
3880 break;
3881 case 'nextweek':
3882 $op = "<";
3883 break;
3884 }
3885 if ( $op == '=' ) {
3886 $whereQuery .= $mainOp . " DATE_PART('month', u.birthday) = date_part('month', CURRENT_DATE) AND DATE_PART('day', u.birthday) = date_part('day', CURRENT_DATE)";
3887 } else {
3888 $whereQuery .= $mainOp . " u.birthday::date + date_trunc('year', age(u.birthday::date)) + interval '1 year' <= current_date + interval '7 days'";
3889 }
3890
3891 $include[ $columnname ] = true;
3892 break;
3893 case 'Type':
3894 $customerType = $value;
3895 switch ( $value ) {
3896 case 3:
3897 $customerType = 'VIP';
3898 break;
3899 case 1:
3900 case 2:
3901 $customerType = 'Current Customers';
3902 break;
3903 case 4:
3904 case 5:
3905 $customerType = 'Past Customers';
3906 break;
3907 case 6:
3908 $customerType = 'Leads';
3909 break;
3910 case 7:
3911 $customerType = 'Other';
3912 break;
3913 }
3914 switch ( $customerType ) {
3915 case 'Current Customers':
3916 $operatorValue = $operator == "equals" ? " IN " : "NOT IN ";
3917 $operatorValue2 = $operator == "equals" ? " = " : " != ";
3918 $operatorValue3 = $operator == "equals" ? " AND " : " OR ";
3919 $whereQuery .= $mainOp . '(c.customertypeid '.$operatorValue.' (1, 2, 3)'. $operatorValue3. ' c.affiliate '.$operatorValue2.' false)';
3920 break;
3921 case 'Past Customers':
3922 $operatorValue = $operator == "equals" ? "IN" : "NOT IN ";
3923 $operatorValue2 = $operator == "equals" ? " = " : " != ";
3924 $operatorValue3 = $operator == "equals" ? " AND " : " OR ";
3925 $whereQuery .= $mainOp . ' (c.customertypeid '.$operatorValue.' (4, 5) ' . $operatorValue3. ' c.affiliate '.$operatorValue2.' false)';
3926 break;
3927 case 'Leads':
3928 $operatorValue = $operator == "equals" ? "=" : "!=";
3929 $operatorValue2 = $operator == "equals" ? " AND " : " OR ";
3930 $whereQuery .= $mainOp . ' (c.customertypeid '.$operatorValue.' 6 ' .$operatorValue2. ' c.affiliate '.$operatorValue.' false)';
3931 break;
3932 case 'Other':
3933 $operatorValue = $operator == "equals" ? "=" : "!=";
3934 $whereQuery .= $mainOp . ' c.affiliate '.$operatorValue.' true';
3935 break;
3936 case 'VIP':
3937 $operatorValue = $operator == "equals" ? "=" : "!=";
3938 $operatorValue2 = $operator == "equals" ? " AND " : " OR ";
3939 $whereQuery .= $mainOp . ' (c.customertypeid'.$operatorValue.' 3 ' .$operatorValue2. 'c.affiliate '.$operatorValue.' false) ';
3940 break;
3941 }
3942 break;
3943 case 'Last Attendance Status':
3944 if ( $operator == 'equals' ) {
3945 $whereQuery .= $mainOp . " (select statusid from customervisits_schedule where customerid = c.customerid and date <= current_date + interval '2 hours' order by date DESC limit 1) = $value";
3946 } elseif ( $operator == 'not equal to' ) {
3947 $whereQuery .= $mainOp . " (select statusid from customervisits_schedule where customerid = c.customerid and date <= current_date + interval '2 hours' order by date DESC limit 1) != $value";
3948 }
3949 break;
3950 case 'Email Unsubscribed':
3951 if ( $operator == 'Yes' ) {
3952 $whereQuery .= $mainOp . " (((eu.uns_email IS TRUE AND (eu.frombusinessid = $businessID OR eu.fromaccountid = $accountID)) OR eu.fromsystem IS TRUE) AND eu.emailunsubcribeid is NOT null) ";
3953 } else {
3954 $whereQuery .= $mainOp . " (eu.emailunsubcribeid is null OR (eu.emailunsubcribeid not in (select z.emailunsubcribeid
3955 from emailunsubscribe z
3956 where z.userid = eu.userid
3957 AND z.uns_email IS FALSE AND z.fromsystem IS FALSE)
3958 AND (eu.uns_email IS FALSE AND eu.fromsystem IS FALSE))) ";
3959 }
3960
3961 break;
3962 case 'Text Unsubscribed':
3963 if ( $operator == 'Yes' ) {
3964 $whereQuery .= $mainOp . " (((eu2.uns_phone IS TRUE AND (eu2.frombusinessid = $businessID OR eu2.fromaccountid = $accountID)) OR eu2.fromsystem IS TRUE) AND eu2.emailunsubcribeid is NOT null) ";
3965 } else {
3966 $whereQuery .= $mainOp . " (eu2.emailunsubcribeid is null OR (eu2.emailunsubcribeid not in (select z.emailunsubcribeid
3967 from emailunsubscribe z
3968 where z.userid = eu2.userid
3969 AND z.uns_phone IS FALSE AND z.fromsystem IS FALSE)
3970 AND (eu2.uns_phone IS FALSE AND eu2.fromsystem IS FALSE))) ";
3971 }
3972
3973 break;
3974 case 'Mindbody Member':
3975 if ( $operator == 'Yes' ) {
3976 $whereQuery .= $mainOp . " ( select mbo_member from user_mbo where userid = u.userid and accountid = $accountID order by mbo_register_date DESC limit 1) IS TRUE ";
3977 } else {
3978 $whereQuery .= $mainOp . " COALESCE((select mbo_member from user_mbo where userid = u.userid and accountid = $accountID order by mbo_register_date DESC limit 1), false) IS FALSE ";
3979 }
3980 break;
3981 case 'Gender':
3982 if($operator == 'equals') {
3983 $whereQuery .= $mainOp . " (select userid from users where userid = u.userid and genderid = $value) IS NOT NULL ";
3984 } else {
3985 $whereQuery .= $mainOp . " (select userid from users where userid = u.userid and genderid != $value) IS NOT NULL ";
3986 }
3987 break;
3988 default: // custom field
3989 if ( isset( $row['users_field_id'], $row['field_type'] ) ) {
3990 $whereQuery .= $this->endsWith($whereQuery, ") ") || !$this->endsWith($whereQuery, " $mainOp ")? $mainOp." ( " : " ( ";
3991 if ( $row['field_type'] == 'text' ) {
3992 if($operator == 'equals') {
3993 $operatorValue = "= '$value'";
3994 } elseif($operator == 'not equal to') {
3995 $operatorValue = "!= '$value'";
3996 } else {
3997 $operatorValue = "LIKE '%$value%'";
3998 }
3999
4000 if ($operator == 'not equal to') {
4001 $whereQuery .= " (ufu$customFieldsCount.field ".$operatorValue;
4002 } else {
4003 $whereQuery .= " ufu$customFieldsCount.field ".$operatorValue;
4004 }
4005
4006 } else {
4007 switch ( $operator ) {
4008 case 'equals':
4009 $whereQuery .= " CAST(ufu$customFieldsCount.field AS INTEGER) = $value ";
4010 break;
4011 case 'greater than':
4012 $whereQuery .= " CAST(ufu$customFieldsCount.field AS INTEGER) > $value ";
4013 break;
4014 case 'less than':
4015 $whereQuery .= " CAST(ufu$customFieldsCount.field AS INTEGER) < $value ";
4016 }
4017 }
4018 if ($operator == 'not equal to') {
4019 $whereQuery .= " AND ufu$customFieldsCount.users_field_id = " . $row['users_field_id'] . " ) OR ufu$customFieldsCount.users_field_id IS NULL ) ";
4020 $joinQuery .= " LEFT JOIN users_field_user AS ufu$customFieldsCount ON ufu$customFieldsCount.userid = c.userid AND ufu$customFieldsCount.users_field_id = " . $row['users_field_id'];
4021 $joinQuery .= " LEFT JOIN users_field AS uf$customFieldsCount ON ufu$customFieldsCount.users_field_id = uf$customFieldsCount.users_field_id ";
4022
4023 } else {
4024 $whereQuery .= " AND ufu$customFieldsCount.users_field_id = " . $row['users_field_id'] . " ) ";
4025 $joinQuery .= ($isAND ? "" : " LEFT") ." JOIN users_field_user AS ufu$customFieldsCount ON ufu$customFieldsCount.userid = c.userid ";
4026 $joinQuery .= ($isAND ? "" : " LEFT") ." JOIN users_field AS uf$customFieldsCount ON ufu$customFieldsCount.users_field_id = uf$customFieldsCount.users_field_id ";
4027
4028 }
4029
4030 $customFieldsCount ++;
4031 }
4032 }
4033 }
4034 if (sizeof($tskOptions) > 0){
4035 if((!$isFirst || !$this->endsWith($whereQuery, $mainOpTemp)) && !$this->endsWith($whereQuery, " AND ( ")) {
4036 $whereQuery .= $mainOpTemp;
4037 }
4038 $columnToSearch = "";
4039 if ($isAND) {
4040 foreach ($tskOptions as $tskOption) {
4041 $columnToSearch = $this->getTskField($tskOption['columnname']);
4042 $optionId = $tskOption['filtervalue'];
4043 if($tskOption['operator'] == 'equals') {
4044 $whereQuery .= !$this->endsWith($whereQuery, " AND ") ? " AND " : "";
4045 $whereQuery .= " (select {$columnToSearch} from tsk_contract where student_id in (select field::bigint as student_id
4046 from users_field_user ufu
4047 left join users_field uf on ufu.users_field_id = uf.users_field_id
4048 where ufu.userid = u.userid and ufu.users_field_id = 1772)
4049 order by contract_id DESC limit 1)::bigint = ".$optionId." ";
4050 } else {
4051 $whereQuery .= !$this->endsWith($whereQuery, " AND ") ? " AND " : "";
4052 $whereQuery .= " (select {$columnToSearch} from tsk_contract where student_id in (select field::bigint as student_id
4053 from users_field_user ufu
4054 left join users_field uf on ufu.users_field_id = uf.users_field_id
4055 where ufu.userid = u.userid and ufu.users_field_id = 1772)
4056 order by contract_id DESC limit 1)::bigint <> ".$optionId." "; }
4057 }
4058 } else {
4059 $tskFieldsArray = array();
4060 foreach ($tskOptions as $tskOption) {
4061 $field = $this->getTskField($tskOption['columnname']);
4062
4063 if ((!empty($tskFieldsArray)) && $this->isValueExistInArray($tskFieldsArray, 'name', $tskOption['columnname'] ) == true){
4064 for ($k= 0; $k<sizeof($tskFieldsArray); $k++){
4065 if ($tskFieldsArray[$k]['field'] == $field){
4066 if($tskOption['operator'] == 'equals') {
4067 array_push($tskFieldsArray[$k]['inTskOptions'], $tskOption['filtervalue']);
4068 } else {
4069 array_push($tskFieldsArray[$k]['notInTskOptions'], $tskOption['filtervalue']);
4070 }
4071 }
4072 }
4073 } else {
4074 $newField = array(
4075 'name' => $tskOption['columnname'],
4076 'field' => $this->getTskField($tskOption['columnname']),
4077 'inTskOptions' => array(),
4078 'notInTskOptions' => array()
4079 );
4080 if($tskOption['operator'] == 'equals') {
4081 array_push($newField['inTskOptions'], $tskOption['filtervalue']);
4082 } else {
4083 array_push($newField['notInTskOptions'], $tskOption['filtervalue']);
4084 }
4085 array_push($tskFieldsArray, $newField);
4086 }
4087 }
4088
4089 for ($n=0; $n<sizeof($tskFieldsArray); $n++){
4090 $inTskOptions = $tskFieldsArray[$n]['inTskOptions'];
4091 $notInTskOptions = $tskFieldsArray[$n]['notInTskOptions'];
4092 $columnToSearch = $tskFieldsArray[$n]['field'];
4093 $conditionSize = sizeof($tskFieldsArray)-1;
4094 if(sizeof($inTskOptions) > 0 || sizeof($notInTskOptions) > 0) {
4095 if ($n > 0 && (substr($whereQuery, -3) != 'OR ')){
4096 $whereQuery .= " OR ";
4097 }
4098
4099 $whereQuery .= sizeof($inTskOptions) > 0 ? " (select {$columnToSearch} from tsk_contract where student_id in (select field::bigint as student_id
4100 from users_field_user ufu
4101 left join users_field uf on ufu.users_field_id = uf.users_field_id
4102 where ufu.userid = u.userid and ufu.users_field_id = 1772)
4103 order by contract_id DESC limit 1)::bigint in ".$this->to_pg_array($inTskOptions)."" : " ";
4104 if (($n== 0 && sizeof($inTskOptions) == 0) || (substr($whereQuery, -3) == 'OR ') || ($n == $conditionSize && sizeof($notInTskOptions) == 0 )){
4105 $whereQuery .= "";
4106 } else {
4107 $whereQuery .= " OR ";
4108 }
4109 $whereQuery .= sizeof($notInTskOptions) > 0 ? " (select {$columnToSearch} from tsk_contract where student_id in (select field::bigint as student_id
4110 from users_field_user ufu
4111 left join users_field uf on ufu.users_field_id = uf.users_field_id
4112 where ufu.userid = u.userid and ufu.users_field_id = 1772)
4113 order by contract_id DESC limit 1)::bigint NOT IN ".$this->to_pg_array($notInTskOptions)."
4114 " : " ";
4115
4116 }
4117 }
4118 }
4119 }
4120
4121 if (sizeof($services) > 0) {
4122
4123 if((!$isFirst || !$this->endsWith($whereQuery, $mainOpTemp)) && !$this->endsWith($whereQuery, " AND ( ")) {
4124 $whereQuery .= $mainOpTemp;
4125 }
4126 $serviceAlias = 'apsc' . ($isAND ? '1' : '0');
4127 if ($isAND) {
4128 $i = 0;
4129 $notEqualToServices = array();
4130 foreach ($services as $service) {
4131 $serviceColumnId = $service['columnname'];
4132 $serviceId = $service['filtervalue'];
4133 switch ($serviceColumnId) {
4134 case 377: // Appointment
4135 $serviceTypeId = 491;
4136 break;
4137 case 376: // Class
4138 $serviceTypeId = 490;
4139 break;
4140 case 378: // Purchase
4141 $serviceTypeId = 495;
4142 break;
4143 default: // Service
4144 $serviceTypeId = 492;
4145
4146 }
4147 switch ($service['operator']) {
4148 case 'equals':
4149 $whereQuery .= !$this->endsWith($whereQuery, " AND ") ? " AND " : "";
4150 if($service['columnname'] == 'Last Purchased Service/Product'){
4151 $whereQuery .= " (select account_app_service_purchase_id from account_app_service_purchase where account_app_service_customer_id = (select account_app_service_customer_id from account_app_service_customer where customerid = c.customerid and account_app_service_id = ".$serviceId." limit 1) order by date DESC limit 1)
4152 = (select account_app_service_purchase_id from account_app_service_purchase where account_app_service_customer_id in (select account_app_service_customer_id from account_app_service_customer where customerid = c.customerid) order by date DESC limit 1)";
4153 }else{
4154 $joinQuery .= " JOIN account_app_service_customer as {$serviceAlias}a{$i} ON {$serviceAlias}a{$i}.customerid = c.customerid ";
4155 $whereQuery .= " {$serviceAlias}a{$i}.account_app_service_id = ".$serviceId." ";
4156 }
4157 break;
4158 case 'contains':
4159 $whereQuery .= !$this->endsWith($whereQuery, " AND ") ? " AND " : "";
4160 if($service['columnname'] == 'Last Purchased Service/Product'){
4161 $whereQuery .= " (select account_app_service_purchase_id from account_app_service_purchase where account_app_service_customer_id = (select {$serviceAlias}apsc{$i}.account_app_service_customer_id from account_app_service_customer {$serviceAlias}apsc{$i} join account_app_service {$serviceAlias}aps{$i} on {$serviceAlias}aps{$i}.account_app_service_id = {$serviceAlias}apsc{$i}.account_app_service_id where {$serviceAlias}apsc{$i}.customerid = c.customerid and lower({$serviceAlias}aps{$i}.title) LIKE lower('%".$serviceId."%') limit 1) order by date DESC limit 1)
4162 = (select account_app_service_purchase_id from account_app_service_purchase where account_app_service_customer_id in (select account_app_service_customer_id from account_app_service_customer where customerid = c.customerid) order by date DESC limit 1)";
4163 } else {
4164 $joinQuery .= " JOIN account_app_service_customer as {$serviceAlias}a{$i} ON {$serviceAlias}a{$i}.customerid = c.customerid ";
4165 $joinQuery .= " JOIN account_app_service as {$serviceAlias}as{$i} ON {$serviceAlias}as{$i}.account_app_service_id = {$serviceAlias}a{$i}.account_app_service_id AND {$serviceAlias}as{$i}.service_type_id = {$serviceTypeId} AND {$serviceAlias}as{$i}.statusid = 272 ";
4166 $whereQuery .= " lower({$serviceAlias}as{$i}.title) LIKE lower('%".$serviceId."%') ";
4167 }
4168 break;
4169 case 'does not contain':
4170 $whereQuery .= !$this->endsWith($whereQuery, " AND ") ? " AND " : "";
4171 if($service['columnname'] == 'Last Purchased Service/Product'){
4172 $whereQuery .= " COALESCE((select account_app_service_purchase_id from account_app_service_purchase where account_app_service_customer_id = (select {$serviceAlias}apsc{$i}.account_app_service_customer_id from account_app_service_customer {$serviceAlias}apsc{$i} join account_app_service {$serviceAlias}aps{$i} on {$serviceAlias}aps{$i}.account_app_service_id = {$serviceAlias}apsc{$i}.account_app_service_id where {$serviceAlias}apsc{$i}.customerid = c.customerid and lower({$serviceAlias}aps{$i}.title) LIKE lower('%".$serviceId."%') limit 1) order by date DESC limit 1)
4173 != (select account_app_service_purchase_id from account_app_service_purchase where account_app_service_customer_id in (select account_app_service_customer_id from account_app_service_customer where customerid = c.customerid) order by date DESC limit 1), TRUE) ";
4174 } else {
4175 $whereQuery .= " (c.customerid NOT IN (SELECT {$serviceAlias}apsc_c{$i}.customerid FROM account_app_service_customer {$serviceAlias}apsc_c{$i} JOIN account_app_service {$serviceAlias}aps_c{$i} ON {$serviceAlias}aps_c{$i}.account_app_service_id = {$serviceAlias}apsc_c{$i}.account_app_service_id AND {$serviceAlias}aps_c{$i}.service_type_id = {$serviceTypeId} AND {$serviceAlias}aps_c{$i}.statusid = 272 WHERE lower({$serviceAlias}aps_c{$i}.title) LIKE lower('%".$serviceId."%')))";
4176 }
4177 break;
4178 default: // not equals
4179 if($service['columnname'] == 'Last Purchased Service/Product'){
4180 $whereQuery .= !$this->endsWith($whereQuery, " AND ") ? " AND " : "";
4181 $whereQuery .= " COALESCE((select account_app_service_purchase_id from account_app_service_purchase where account_app_service_customer_id = (select account_app_service_customer_id from account_app_service_customer where customerid = c.customerid and account_app_service_id = ".$serviceId." limit 1) order by date DESC limit 1)
4182 != (select account_app_service_purchase_id from account_app_service_purchase where account_app_service_customer_id in (select account_app_service_customer_id from account_app_service_customer where customerid = c.customerid) order by date DESC limit 1), TRUE) ";
4183 }else{
4184 array_push($notEqualToServices, $serviceId);
4185 }
4186
4187 }
4188 $i++;
4189 }
4190 if (!empty($notEqualToServices)) {
4191 $whereQuery .= !$this->endsWith($whereQuery, " AND ") ? " AND " : "";
4192 $whereQuery .= " (NOT EXISTS (select 1 from account_app_service_customer where account_app_service_id IN " . $this->to_pg_array($notEqualToServices) . " and customerid = c.customerid))";
4193
4194 }
4195 } else {
4196 $i = 0;
4197 $joinQuery .= ($customerDoesNotBelongToService || !$isAND ? " LEFT " : "") ." JOIN account_app_service_customer as $serviceAlias ON $serviceAlias.customerid = c.customerid ";
4198 $inServices = array();
4199 $notInServices = array();
4200
4201 foreach ($services as $service) {
4202 $serviceId = $service['filtervalue'];
4203 switch ($service['operator']) {
4204 case 'equals':
4205 if($service['columnname'] == 'Last Purchased Service/Product'){
4206 $whereQuery .= !$this->endsWith($whereQuery, " OR ") && !$this->endsWith($whereQuery, " AND ( ") ? " OR " : "";
4207 $whereQuery .= " (select account_app_service_purchase_id from account_app_service_purchase where account_app_service_customer_id = (select account_app_service_customer_id from account_app_service_customer where customerid = c.customerid and account_app_service_id = ".$serviceId." limit 1) order by date DESC limit 1)
4208 = (select account_app_service_purchase_id from account_app_service_purchase where account_app_service_customer_id in (select account_app_service_customer_id from account_app_service_customer where customerid = c.customerid) order by date DESC limit 1)";
4209 }else{
4210 array_push($inServices, $service['filtervalue']);
4211 }
4212 break;
4213 case 'contains':
4214 $whereQuery .= !$this->endsWith($whereQuery, " OR ") && !$this->endsWith($whereQuery, " AND ( ") ? " OR " : "";
4215 if ($service['columnname'] == 'Last Purchased Service/Product') {
4216 $whereQuery .= " (select account_app_service_purchase_id from account_app_service_purchase where account_app_service_customer_id = (select {$serviceAlias}apsco{$i}.account_app_service_customer_id from account_app_service_customer {$serviceAlias}apsco{$i} join account_app_service {$serviceAlias}apso{$i} on {$serviceAlias}apso{$i}.account_app_service_id = {$serviceAlias}apsco{$i}.account_app_service_id where {$serviceAlias}apsco{$i}.customerid = c.customerid and lower({$serviceAlias}apso{$i}.title) LIKE lower('%".$serviceId."%') limit 1) order by date DESC limit 1)
4217 = (select account_app_service_purchase_id from account_app_service_purchase where account_app_service_customer_id in (select account_app_service_customer_id from account_app_service_customer where customerid = c.customerid) order by date DESC limit 1)";
4218 } else {
4219 $whereQuery .= " (c.customerid IN (SELECT {$serviceAlias}apsc_c{$i}.customerid FROM account_app_service_customer {$serviceAlias}apsc_c{$i} JOIN account_app_service {$serviceAlias}aps_c{$i} ON {$serviceAlias}aps_c{$i}.account_app_service_id = {$serviceAlias}apsc_c{$i}.account_app_service_id WHERE {$serviceAlias}aps_c{$i}.title LIKE '%".$serviceId."%'))";
4220 }
4221 break;
4222 case 'does not contain':
4223 $whereQuery .= !$this->endsWith($whereQuery, " OR ") && !$this->endsWith($whereQuery, " AND ( ") ? " OR " : "";
4224 if($service['columnname'] == 'Last Purchased Service/Product'){
4225 $whereQuery .= " COALESCE((select account_app_service_purchase_id from account_app_service_purchase where account_app_service_customer_id = (select {$serviceAlias}apsco{$i}.account_app_service_customer_id from account_app_service_customer {$serviceAlias}apsco{$i} join account_app_service {$serviceAlias}apso{$i} on {$serviceAlias}apso{$i}.account_app_service_id = {$serviceAlias}apsco{$i}.account_app_service_id where {$serviceAlias}apsco{$i}.customerid = c.customerid and lower({$serviceAlias}apso{$i}.title) LIKE lower('%".$serviceId."%') limit 1) order by date DESC limit 1)
4226 != (select account_app_service_purchase_id from account_app_service_purchase where account_app_service_customer_id in (select account_app_service_customer_id from account_app_service_customer where customerid = c.customerid) order by date DESC limit 1), TRUE) ";
4227 } else {
4228 $whereQuery .= " (c.customerid NOT IN (SELECT {$serviceAlias}apsc_c{$i}.customerid FROM account_app_service_customer {$serviceAlias}apsc_c{$i} JOIN account_app_service {$serviceAlias}aps_c{$i} ON {$serviceAlias}aps_c{$i}.account_app_service_id = {$serviceAlias}apsc_c{$i}.account_app_service_id WHERE {$serviceAlias}aps_c{$i}.title LIKE '%".$serviceId."%'))";
4229 }
4230 break;
4231 default: // not equals
4232 if($service['columnname'] == 'Last Purchased Service/Product'){
4233 $whereQuery .= !$this->endsWith($whereQuery, " OR ") && !$this->endsWith($whereQuery, " AND ( ") ? " OR " : "";
4234 $whereQuery .= " COALESCE((select account_app_service_purchase_id from account_app_service_purchase where account_app_service_customer_id = (select account_app_service_customer_id from account_app_service_customer where customerid = c.customerid and account_app_service_id = ".$serviceId." limit 1) order by date DESC limit 1)
4235 != (select account_app_service_purchase_id from account_app_service_purchase where account_app_service_customer_id in (select account_app_service_customer_id from account_app_service_customer where customerid = c.customerid) order by date DESC limit 1), TRUE) ";
4236 }else{
4237 array_push($notInServices, $service['filtervalue']);
4238 }
4239 }
4240 $i++;
4241 }
4242 if(sizeof($inServices) > 0 || sizeof($notInServices) > 0) {
4243 $whereQuery .= sizeof($inServices) > 0 ? " (c.customerid IN (SELECT customerid FROM account_app_service_customer WHERE account_app_service_id IN ".$this->to_pg_array($inServices)."))" : "";
4244 $whereQuery .= sizeof($inServices) > 0 && sizeof($notInServices) > 0 ? " OR " : "";
4245 $whereQuery .= sizeof($notInServices) > 0 ? " (c.customerid IN (SELECT customerid FROM account_app_service_customer WHERE account_app_service_id NOT IN ".$this->to_pg_array($notInServices).")) AND (c.customerid NOT IN (SELECT customerid FROM account_app_service_customer WHERE account_app_service_id IN ".$this->to_pg_array($notInServices).")) " : "";
4246 }
4247
4248 }
4249
4250 }
4251
4252 if (sizeof($labels) > 0) {
4253 if((!$isFirst || !$this->endsWith($whereQuery, $mainOpTemp)) && !$this->endsWith($whereQuery, " AND ( ")) {
4254 $whereQuery .= $mainOpTemp;
4255 }
4256 $labelAlias = 'ccg' . ($isAND ? '1' : '0');
4257 $joinQuery .= " LEFT JOIN customer_customergroup as $labelAlias ON $labelAlias.customerid = c.customerid ";
4258 if ($isAND) {
4259 foreach ($labels as $label) {
4260 $labelId = $label['filtervalue'];
4261 if($label['operator'] == 'equals') {
4262 $whereQuery .= !$this->endsWith($whereQuery, " AND ") ? " AND " : "";
4263 $whereQuery .= " $labelAlias.customer_group_id = " . $labelId . " ";
4264 } else {
4265 $whereQuery .= !$this->endsWith($whereQuery, " AND ") ? " AND " : "";
4266 $whereQuery .= "c.customerid NOT IN (SELECT customerid FROM customer_customergroup WHERE customer_group_id = $labelId)";
4267 }
4268 }
4269 } else {
4270 $inLabels = array();
4271 $notInLabels = array();
4272 foreach ($labels as $label) {
4273 if($label["operator"] == "equals") {
4274 array_push($inLabels, $label['filtervalue']);
4275 } else {
4276 array_push($notInLabels, $label['filtervalue']);
4277 }
4278 }
4279 if(sizeof($inLabels) > 0 || sizeof($notInLabels) > 0) {
4280 $whereQuery .= sizeof($inLabels) > 0 ? " (c.customerid IN (SELECT customerid FROM customer_customergroup WHERE customer_group_id IN ".$this->to_pg_array($inLabels).")) " : "";
4281 $whereQuery .= sizeof($inLabels) > 0 && sizeof($notInLabels) > 0 ? " OR " : "";
4282 $whereQuery .= sizeof($notInLabels) > 0 ? " (c.customerid IN (SELECT customerid FROM customer_customergroup WHERE customer_group_id NOT IN ".$this->to_pg_array($notInLabels).")) " : "";
4283 }
4284 }
4285 }
4286
4287
4288 if ( ! $isAND ) {
4289 $whereQuery .= " )";
4290 }
4291 return array( $selectQuery, $whereQuery, $havingQuery, $joinQuery, $include, $labelsCount );
4292
4293 }
4294
4295 private function getTskField($columnName){
4296 switch ($columnName){
4297 case 'Contract Program Type':
4298 return 'program_type_optionid';
4299 case 'Contract Age Group':
4300 return 'age_group_optionid';
4301 case 'Contract Payment Plan':
4302 return 'payment_plan_optionid';
4303 case 'Contract Discount Type':
4304 return 'discount_type_optionid';
4305 }
4306 }
4307
4308 private function getAttendanceLabelById($attendanceStatusId){
4309 switch ($attendanceStatusId){
4310 case 680:
4311 return 'Excused';
4312 case 681:
4313 return 'No-show';
4314 case 682:
4315 return 'Present';
4316 }
4317 }
4318
4319 private function getGenderIdByValue($genderValue){
4320 switch ($genderValue){
4321 case "Male":
4322 return 1050;
4323 case "Female":
4324 return 1051;
4325 default:
4326 return 1052;
4327 }
4328 }
4329
4330 private function isValueExistInArray($array, $key, $val) {
4331 foreach ($array as $item)
4332 if (isset($item[$key]) && $item[$key] == $val)
4333 return true;
4334 return false;
4335 }
4336
4337 private function endsWith($haystack, $needle)
4338 {
4339 $length = strlen($needle);
4340 if ($length == 0) {
4341 return true;
4342 }
4343
4344 return (substr($haystack, -$length) === $needle);
4345 }
4346
4347 public function getCustomersCountSubscribedToTexts( $accountMd5h, $viewid ) {
4348 return $this->getCustomersCountAdditionalConditions( $accountMd5h, $viewid, "(eu.uns_phone IS NULL OR eu.uns_phone = false) AND u.phone != ''" );
4349 }
4350
4351 private function getCustomersCountAdditionalConditions( $accountMd5h, $viewid, $additionalConditions ) {
4352 return $this->getCustomers2( $accountMd5h, $viewid, 'count', null, $additionalConditions );
4353 }
4354
4355 public function getAccounts() {
4356 $query = "select accountid as id from account";
4357 $resultArray = $this->query( $query );
4358
4359 return $resultArray;
4360 }
4361
4362 public function getTableDisplayLength( $accountMd5h ) {
4363 $query = "SELECT contacts_table_displaylength as table_displaylength
4364 FROM accountoption ao
4365 JOIN account a ON a.accountid = ao.accountid
4366 WHERE a.md5h = '$accountMd5h'";
4367 $result = $this->querySingle( $query );
4368 if ( $result != null ) {
4369 return $result['table_displaylength'];
4370 } else {
4371 return - 1;
4372 }
4373 }
4374
4375 public function getCustomers( $accountMd5h, $viewid, $allFields = false, $dataTablesGET = null ) {
4376 // constants
4377 $today = date( 'Y-m-d H:i:s', time() );
4378 $before7days = date( 'Y-m-d H:i:s', strtotime( "- 7 day" ) );
4379 $before30days = date( 'Y-m-d H:i:s', strtotime( "- 30 day" ) );
4380 $before60days = date( 'Y-m-d H:i:s', strtotime( "- 60 day" ) );
4381 $before90days = date( 'Y-m-d H:i:s', strtotime( "- 90 day" ) );
4382 $next7days = date( 'Y-m-d H:i:s', strtotime( "+ 7 day" ) );
4383
4384 // Check account
4385 $accountID = $this->getAccountID( $accountMd5h );
4386 if ( $accountID == - 1 ) {
4387 return - 1;
4388 }
4389
4390 // Check view
4391 $query = "SELECT name
4392 FROM customersview
4393 WHERE accountid = $accountID AND customersviewid = $viewid";
4394 if ( $this->querySingle( $query ) == null ) {
4395 return - 1;
4396 }
4397
4398 // Pagination
4399 $sLimit = "";
4400 if ( isset( $dataTablesGET ) ) {
4401 if ( isset( $dataTablesGET['iDisplayStart'] ) && $dataTablesGET['iDisplayLength'] != '-1' ) {
4402 $sLimit = " LIMIT " . intval( $dataTablesGET['iDisplayStart'] ) . " OFFSET " .
4403 intval( $dataTablesGET['iDisplayLength'] );
4404 }
4405 }
4406
4407
4408 // Include filters
4409 $query = "SELECT o.value as operator, oc.value as columnname, f.value as filtervalue, f.users_field_id, uf.field_type
4410 FROM customersview_filter f
4411 LEFT JOIN option o ON o.optionid = f.operatorid
4412 LEFT JOIN option oc ON oc.optionid = f.columnid
4413 LEFT JOIN users_field uf ON uf.users_field_id = f.users_field_id
4414 WHERE f.customersviewid = $viewid";
4415 $filterResponse = $this->query( $query );
4416 $filterQuery = '';
4417 $filterJoinQuery = '';
4418 $filterSelectQuery = '';
4419 $filterGroupByQuery = '';
4420 $filterHavingQuery = '';
4421 $servicesCount = 0;
4422 $labelsCount = 0;
4423 $customFieldsCount = 0;
4424 if ( is_array( $filterResponse ) || is_object( $filterResponse ) ) {
4425 foreach ( $filterResponse as $row ) {
4426 $columnname = $row['columnname'];
4427 $operator = $row['operator'];
4428 $value = $row['filtervalue'];
4429 $valueLower = strtolower( $value );
4430 switch ( $columnname ) {
4431 case 'Name':
4432 // Name
4433 if ( $operator == 'contains' ) {
4434 $filterQuery .= " AND lower(concat_ws(' ', u.firstname, u.middlename, u.lastname)) LIKE '%$valueLower%' ";
4435 } elseif ( $operator == 'equals' ) {
4436 $filterQuery .= " AND lower(concat_ws(' ', u.firstname, u.middlename, u.lastname)) = '$valueLower' ";
4437 }
4438 break;
4439 case 'First Name':
4440 if ( $operator == 'contains' ) {
4441 $filterQuery .= " AND lower(u.firstname) LIKE '%$valueLower%' ";
4442 } elseif ( $operator == 'equals' ) {
4443 $filterQuery .= " AND lower(u.firstname) = '$valueLower' ";
4444 }
4445 break;
4446 case 'Last Name':
4447 if ( $operator == 'contains' ) {
4448 $filterQuery .= " AND lower(u.lastname) LIKE '%$valueLower%' ";
4449 } elseif ( $operator == 'equals' ) {
4450 $filterQuery .= " AND lower(u.lastname) = '$valueLower' ";
4451 }
4452 break;
4453 case 'Email':
4454 // Email
4455 if ( $operator == 'contains' ) {
4456 $filterQuery .= " AND e.email LIKE '%$valueLower%' ";
4457 } elseif ( $operator == 'equals' ) {
4458 $filterQuery .= " AND e.email = '$valueLower' ";
4459 } elseif ( $operator == 'empty' ) {
4460 $filterQuery .= " AND (e.email IS NULL OR e.email = '') ";
4461 }
4462 break;
4463 case 'Phone':
4464 // Phone
4465 if ( $operator == 'contains' ) {
4466 $filterQuery .= " AND u.phone LIKE '%$value%' ";
4467 } elseif ( $operator == 'equals' ) {
4468 $filterQuery .= " AND u.phone = '$value' ";
4469 } elseif ( $operator == 'empty' ) {
4470 $filterQuery .= " AND (e.phone IS NULL OR u.phone = '') ";
4471 }
4472 break;
4473 case 'Source':
4474 if ( $operator == 'equals' ) {
4475 $filterQuery .= " AND c.registrationtypeid = $value";
4476 } elseif ( $operator == 'not equal to' ) {
4477 $filterQuery .= " AND c.registrationtypeid != $value";
4478 }
4479 // Source
4480 break;
4481 case 'Balance':
4482 switch ( $operator ) {
4483 case 'equals':
4484 $filterQuery .= " AND '$today' < credit.expdate AND ( COALESCE( credit.store_credit_plus, 0 ) - COALESCE( credit.store_credit_minus, 0 ) ) = $value ";
4485 break;
4486 case 'greater than':
4487 $filterQuery .= " AND '$today' < credit.expdate AND ( COALESCE( credit.store_credit_plus, 0 ) - COALESCE( credit.store_credit_minus, 0 ) ) > $value ";
4488 break;
4489 case 'less than':
4490 $filterQuery .= " AND '$today' < credit.expdate AND ( COALESCE( credit.store_credit_plus, 0 ) - COALESCE( credit.store_credit_minus, 0 ) ) < $value ";
4491 }
4492 break;
4493 case 'Loyalty Points':
4494 $filterHavingQuery .= " AND ( COALESCE( count (distinct cv.customervisitsid), 0) + COALESCE( loyaltypoints.loyaltypoints, 0) - COALESCE( usedloyalty.usedloyalty, 0) ) ";
4495 switch ( $operator ) {
4496 case 'equals':
4497 $filterHavingQuery .= "= $value ";
4498 break;
4499 case 'greater than':
4500 $filterHavingQuery .= "> $value ";
4501 break;
4502 case 'less than':
4503 $filterHavingQuery .= "< $value ";
4504 }
4505 break;
4506 case 'Last Visit':
4507 // Last visit
4508 switch ( $operator ) {
4509 case 'last 7 days':
4510 $filterQuery .= " AND customervisitsmax.visitdate > '$before7days'";
4511 break;
4512 case 'last 30 days':
4513 $filterQuery .= " AND customervisitsmax.visitdate > '$before30days'";
4514 break;
4515 case 'last 60 days':
4516 $filterQuery .= " AND customervisitsmax.visitdate > '$before60days'";
4517 break;
4518 case 'last 90 days':
4519 $filterQuery .= " AND customervisitsmax.visitdate > '$before90days'";
4520 break;
4521 default:
4522 $dateValue = date( 'Y-m-d H:i:s', strtotime( "- $value day" ) );
4523 $op = "";
4524 switch ( $operator ) {
4525 case 'equals':
4526 $op = "=";
4527 break;
4528 case 'greater than':
4529 $op = "<";
4530 break;
4531 case 'less than':
4532 $op = ">";
4533 }
4534 $filterQuery .= " AND customervisitsmax.visitdate $op '$dateValue'";
4535
4536 }
4537 break;
4538 case 'Service':
4539 if ( $operator == 'equals' ) {
4540 $filterQuery .= " AND apsc$servicesCount.account_app_service_id = $value";
4541 } elseif ( $operator == 'not equal to' ) {
4542 $filterQuery .= " AND apsc$servicesCount.account_app_service_id != $value";
4543 }
4544
4545 $filterJoinQuery .= " JOIN account_app_service_customer as apsc$servicesCount ON apsc$servicesCount.customerid = c.customerid ";
4546 $servicesCount ++;
4547 break;
4548 case 'Label':
4549 $filterQuery .= " AND ccg$labelsCount.customer_group_id = $value ";
4550 $filterJoinQuery .= " LEFT JOIN customer_customergroup as ccg$labelsCount ON ccg$labelsCount.customerid = c.customerid ";
4551 $labelsCount ++;
4552 break;
4553
4554 default: // custom field
4555 if ( isset( $row['users_field_id'], $row['field_type'] ) ) {
4556 if ( $row['field_type'] == 'text' ) {
4557 $filterQuery .= " AND ufu$customFieldsCount.field ";
4558 if($operator == 'equals') {
4559 $operatorValue = "= '$value'";
4560 } else if($operator == 'not equal to') {
4561 $operatorValue = "!= '$value'";
4562 } else {
4563 $operatorValue = "LIKE '%$value%'";
4564 }
4565 $filterQuery .= $operatorValue;
4566 } else {
4567 switch ( $operator ) {
4568 case 'equals':
4569 $filterQuery .= " AND ufu$customFieldsCount.field = $value ";
4570 break;
4571 case 'greater than':
4572 $filterQuery .= " AND ufu$customFieldsCount.field > $value ";
4573 break;
4574 case 'less than':
4575 $filterQuery .= " AND ufu$customFieldsCount.field < $value ";
4576 }
4577 }
4578 $filterQuery .= " AND ufu$customFieldsCount.users_field_id = " . $row['users_field_id'] . " ";
4579 $filterJoinQuery .= " JOIN users_field_user AS ufu$customFieldsCount ON ufu$customFieldsCount.userid = c.userid ";
4580 $filterJoinQuery .= " JOIN users_field AS uf$customFieldsCount ON ufu$customFieldsCount.users_field_id = uf$customFieldsCount.users_field_id";
4581 }
4582
4583
4584 }
4585 }
4586 }
4587
4588
4589 if ( strpos( $filterHavingQuery, 'AND' ) !== false ) {
4590 $filterHavingQuery = 'HAVING ' . substr( $filterHavingQuery, 4 );
4591 }
4592
4593
4594 // Get customers
4595 $query = "SELECT businessid
4596 FROM account
4597 WHERE accountid = $accountID";
4598 $response = $this->querySingle( $query );
4599 $businessID = $response['businessid'];
4600 $customersTypeQuery = '';
4601 $query = "SELECT count(*) OVER() AS total_count, c.md5h, c.customerid, c.customertypeid, c.registrationtypeid, c.affiliate::boolean::text,
4602 u.firstname, u.middlename, u.lastname, lower(concat_ws(' ', u.firstname, u.middlename, u.lastname)) as fullname, u.phone,
4603 credit.expdate, COALESCE( credit.store_credit_plus, 0 ) AS store_credit_plus, COALESCE( credit.store_credit_minus, 0 ) AS store_credit_minus, ( COALESCE( credit.store_credit_plus, 0 ) - COALESCE( credit.store_credit_minus, 0 ) ) as balans,
4604 customervisitsmax.visitdate,
4605 count (distinct i_saved.voucherid) as invite_id_saved,
4606 ( COALESCE( count (distinct cv.customervisitsid), 0) + COALESCE( loyaltypoints.loyaltypoints, 0) - COALESCE( usedloyalty.usedloyalty, 0) ) as loyalty_points,
4607 eu.emailunsubcribeid, eu.uns_email, eu.uns_phone, eu.uns_mobile, eu.fromsystem,
4608 e.email $filterSelectQuery
4609 FROM Customer c
4610 JOIN Users u ON c.userid = u.userid
4611 LEFT JOIN account_user_store_credit credit ON (u.userid = credit.userid and credit.accountid = {$accountID})
4612 LEFT JOIN (select cv.customerid, cv.visitdate from customervisits cv where cv.visitdate = (select max(visitdate) from customervisits cv2 where cv.customerid= cv2.customerid)) customervisitsmax on (c.customerid = customervisitsmax.customerid)
4613 LEFT JOIN invite i_saved ON (i_saved.friendid = c.userid AND i_saved.statusid=44 AND i_saved.accountid=c.accountid)
4614 LEFT JOIN customervisits cv ON (c.customerid = cv.customerid and cv.point_given=true)
4615 LEFT JOIN (SELECT distinct on (customerid) customerid, SUM(lrp.loyaltypoints) as loyaltypoints FROM loyaltyrewardpoints lrp group by customerid) loyaltypoints on (c.customerid = loyaltypoints.customerid)
4616 LEFT JOIN (SELECT distinct on (customerid) customerid, SUM(lrt.loyaltypoints) as usedloyalty FROM loyaltyrewardtransaction lrt group by customerid) usedloyalty on (c.customerid = usedloyalty.customerid)
4617 LEFT JOIN emailunsubscribe eu ON (u.userid = eu.userid AND (eu.fromsystem = TRUE OR eu.frombusinessid={$businessID} AND (eu.uns_email=true OR eu.uns_phone=true OR eu.uns_mobile=true)))
4618 $filterJoinQuery
4619 JOIN email e ON u.userid = e.userid
4620 WHERE c.accountid = {$accountID} AND u.statusid IN (54,55,56,57) AND c.statusid = 1 AND e.primaryemail = true $customersTypeQuery $filterQuery
4621 GROUP BY c.customerid, e.email, customervisitsmax.visitdate, u.invitedbyaccountid, u.usertypeid, c.affiliate, c.registrationtypeid, u.company, u.lastname, u.middlename, u.firstname, u.userid, u.md5h, u.phone, credit.expdate, credit.store_credit_plus, credit.store_credit_minus, usedloyalty.usedloyalty, loyaltypoints.loyaltypoints, eu.emailunsubcribeid, eu.uns_email, eu.uns_phone, eu.uns_mobile, eu.fromsystem $filterGroupByQuery
4622 $filterHavingQuery
4623 $sLimit
4624 ";
4625
4626 $customers = array();
4627 $result = cache_aware_pg_query( $this->_database, $query );
4628 if ( ( ! empty( $result ) ) && ( pg_numrows( $result ) > 0 ) ) {
4629 while ( $row = pg_fetch_assoc( $result ) ) {
4630 $customer = null;
4631
4632 $customer['ID'] = $row['md5h'];
4633 if ( $allFields ) {
4634 $customer['customerID'] = $row['customerid'];
4635 }
4636
4637 $columns = $allFields ? $this->getAllColumns() : $this->getColumns( $viewid );
4638
4639 foreach ( $columns as $column ) {
4640 switch ( $column['name'] ) {
4641 case 'Name':
4642 // Name
4643 $customer['name'] = $row['firstname'] . ' ' . $row['middlename'] . ' ' . $row['lastname'];
4644 break;
4645 case 'First Name':
4646 // Name
4647 $customer['firstName'] = $row['firstname'];
4648 break;
4649 case 'Last Name':
4650 // Name
4651 $customer['lastName'] = $row['lastname'];
4652 break;
4653 case 'Email':
4654 // Email
4655 $email['address'] = $row['email'];
4656 $email['unsubscribed'] = $row['uns_email'] == 't';
4657 $customer['email'] = $email;
4658 break;
4659 case 'Phone':
4660 // Phone
4661 $phone['number'] = $row['phone'];
4662 $phone['unsubscribed'] = $row['uns_phone'] == 't';
4663 $customer['phone'] = $phone;
4664 break;
4665 case 'Source':
4666 // Source
4667 switch ( $row['registrationtypeid'] ) {
4668 case 11:
4669 $customer['source'] = 'Imported';
4670 break;
4671 case 9:
4672 $customer['source'] = 'Web App'; //Via Web-app
4673 break;
4674 case 10:
4675 $customer['source'] = 'Invited';
4676 break;
4677 case 17:
4678 case 35:
4679 $customer['source'] = 'Landing Page';
4680 break;
4681 case 21:
4682 $customer['source'] = 'Customers Page';
4683 break;
4684 case 22:
4685 $customer['source'] = 'Search Field';
4686 break;
4687 case 23:
4688 $customer['source'] = 'Master';
4689 break;
4690 case 24:
4691 $customer['source'] = 'MINDBODY';
4692 break;
4693 case 28:
4694 $customer['source'] = 'Call In';
4695 break;
4696 case 30:
4697 $customer['source'] = 'Backend Redeem';
4698 break;
4699 case 32:
4700 $customer['source'] = 'Pike13';
4701 break;
4702 case 34:
4703 $customer['source'] = 'Text Sent';
4704 break;
4705 case 35:
4706 $customer['source'] = 'Landing Page Claim';
4707 break;
4708 case 37:
4709 $customer['source'] = 'Zapier';
4710 break;
4711 case 39:
4712 $customer['source'] = 'Website Widget';
4713 break;
4714 case 70:
4715 $customer['source'] = 'Reviews Widget';
4716 break;
4717 case 72:
4718 $customer['source'] = 'Landing Page Subscribe';
4719 break;
4720 case 74:
4721 $customer['source'] = 'ASF Payment Solutions';
4722 break;
4723 case 81:
4724 $customer['source'] = 'Booker';
4725 break;
4726 case 83:
4727 $customer['source'] = 'Mailchimp';
4728 break;
4729 case 85:
4730 $customer['source'] = 'Gmail';
4731 break;
4732 case 87:
4733 $customer['source'] = 'Constant Contact';
4734 break;
4735 case 89:
4736 $customer['source'] = 'Quickbooks';
4737 break;
4738 case 91:
4739 $customer['source'] = 'Yahoo';
4740 break;
4741 case 93:
4742 $customer['source'] = 'Glofox';
4743 break;
4744 case 95:
4745 $customer['source'] = 'Loyalty Widget';
4746 break;
4747 case 97:
4748 $customer['source'] = 'Free Trial';
4749 break;
4750 case 99:
4751 $customer['source'] = 'Freemium';
4752 break;
4753 case 101:
4754 $customer['source'] = 'Premium';
4755 break;
4756 case 102:
4757 $customer['source'] = 'MINDBODY';
4758 break;
4759 case 104:
4760 $customer['source'] = 'Landing Page Contact Form';
4761 break;
4762 case 106:
4763 $customer['source'] = 'SunLync';
4764 break;
4765 case 108:
4766 $customer['source'] = 'Email';
4767 break;
4768 case 109:
4769 $customer['source'] = 'MINDBODY';
4770 break;
4771 default:
4772 $customer['source'] = 'Imported';
4773 }
4774 break;
4775 case 'Balance':
4776 // Balance
4777 $expDateUnix = strtotime( $row['expdate'] );
4778 $todayUnix = strtotime( $today );
4779 $balance = $row['store_credit_plus'] - $row['store_credit_minus'];
4780
4781 if ( $balance > 0 and $expDateUnix > $todayUnix ) {
4782 $balanceDigits = $balance;
4783 $balance = '$' . number_format( (float) $balance, 2, '.', '' );
4784 $timeLeft = $expDateUnix - $todayUnix;
4785 if ( $timeLeft < 86400 ) {
4786 $whenExp = 'today';
4787 } else {
4788 $daysLeft = floor( $timeLeft / 86400 );
4789 $whenExp = 'in ' . $daysLeft . ' days';
4790 }
4791 $expDatePR = date( 'm/d/Y', $expDateUnix );
4792 $exrPR = ' - Expires ' . $whenExp . ' (' . $expDatePR . ')';
4793 } else {
4794 $balanceDigits = 0;
4795 $balance = '$0.00';
4796 }
4797 $customer['balance'] = $balance;
4798 break;
4799 case 'Last Visit':
4800 // Last visit
4801 $lastVisit = strtotime( $row['visitdate'] );
4802 $customer['lastVisit'] = date( "m/d/Y", $lastVisit );
4803 if ( ! isset( $row['visitdate'] ) || ( $customer['lastVisit'] == '01/01/1970' || $customer['lastVisit'] == '12/31/1969' ) ) {
4804 $customer['lastVisit'] = '';
4805 }
4806 break;
4807 case 'Loyalty Points':
4808 // Loyalty Pointes
4809 $customer['loyaltiesCount'] = intval( $row['loyalty_points'] );
4810 break;
4811 default:
4812 for ( $i = 0; $i < $customColumnCount; $i ++ ) {
4813 if ( isset( $row["field_title$i"] ) && $column['name'] == $row["field_title$i"] ) {
4814 $customer[ $column['name'] ] = isset( $row["field_value$i"] ) ? $row["field_value$i"] : "";
4815 break;
4816 }
4817 }
4818
4819
4820 }
4821 }
4822
4823
4824 if ( $row['affiliate'] == 'true' ) {
4825 //array_push($affiliates, $customer);
4826 $customer['type'] = 'affiliates';
4827 } else {
4828 switch ( $row['customertypeid'] ) {
4829 case 1:
4830 case 2:
4831 case 3:
4832 //array_push($activeCustomers, $customer);
4833 $customer['type'] = 'active';
4834 break;
4835 case 4:
4836 case 5:
4837 //array_push($passiveCustomers, $customer);
4838 $customer['type'] = 'passive';
4839 break;
4840 case 6:
4841 //array_push($potentialCustomers, $customer);
4842 $customer['type'] = 'potential';
4843 default:
4844 continue 2;
4845 }
4846 }
4847 array_push( $customers, $customer );
4848 } // while
4849 }
4850
4851 return $customers;
4852 }
4853
4854 public function getAllColumns() {
4855 $query = "SELECT o.value as name
4856 FROM option o
4857 WHERE fieldname = 'columnid'";
4858
4859 $response = $this->query( $query );
4860 $columns = array();
4861 if ( is_array( $response ) || is_object( $response ) ) {
4862 foreach ( $response as $r ) {
4863 $column = null;
4864 $column['name'] = $r['name'];
4865 array_push( $columns, $column );
4866 }
4867 }
4868
4869
4870 return $columns;
4871 }
4872
4873// Deprecated
4874
4875 private function printn( $field, $key ) {
4876 if ( isset( $field[ $key ] ) ) {
4877 return ", " . $key;
4878 } else {
4879 return "";
4880 }
4881 }
4882
4883 private function printv( $field, $key ) {
4884 if ( isset( $field[ $key ] ) ) {
4885 return ", '" . $field[ $key ] . "'";
4886 } else {
4887 return "";
4888 }
4889 }
4890
4891 private function getCountryShortCodeFromAccountId($accountID) {
4892 $query = "select c.short as countrycode from country c join account a on a.countryid = c.countryid where a.accountid = '$accountID'";
4893 $result = $this->querySingle( $query );
4894 if ( $result['countrycode'] ) {
4895 return $result['countrycode'];
4896 } else {
4897 return 'US';
4898 }
4899 }
4900
4901 private function getGenderValue($genderId) {
4902
4903
4904 $query = "select c.short as countrycode from country c join account a on a.countryid = c.countryid where a.accountid = '$accountID'";
4905 $result = $this->querySingle( $query );
4906 if ( $result['countrycode'] ) {
4907 return $result['countrycode'];
4908 } else {
4909 return 'US';
4910 }
4911 }
4912}
4913
4914function includePhpLibPhoneNumber() {
4915 require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/libphonenumber-for-php/src/PhoneNumberUtil.php';
4916 require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/libphonenumber-for-php/src/CountryCodeToRegionCodeMap.php';
4917 require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/libphonenumber-for-php/src/MatcherAPIInterface.php';
4918 require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/libphonenumber-for-php/src/MatcherAPIInterface.php';
4919 require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/libphonenumber-for-php/src/RegexBasedMatcher.php';
4920 require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/libphonenumber-for-php/src/MetadataSourceInterface.php';
4921 require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/libphonenumber-for-php/src/MultiFileMetadataSourceImpl.php';
4922 require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/libphonenumber-for-php/src/MetadataLoaderInterface.php';
4923 require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/libphonenumber-for-php/src/CountryCodeToRegionCodeMap.php';
4924 require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/libphonenumber-for-php/src/DefaultMetadataLoader.php';
4925 require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/libphonenumber-for-php/src/PhoneNumberUtil.php';
4926 require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/libphonenumber-for-php/src/PhoneNumberFormat.php';
4927 require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/libphonenumber-for-php/src/PhoneNumber.php';
4928 require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/libphonenumber-for-php/src/CountryCodeSource.php';
4929 require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/libphonenumber-for-php/src/Matcher.php';
4930 require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/libphonenumber-for-php/src/PhoneMetadata.php';
4931 require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/libphonenumber-for-php/src/PhoneNumberDesc.php';
4932 require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/libphonenumber-for-php/src/NumberFormat.php';
4933 require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/libphonenumber-for-php/src/PhoneNumberType.php';
4934 require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/libphonenumber-for-php/src/ValidationResult.php';
4935 require_once '/opt/data/git/backend/src/backend.referrizer.com/apps/libphonenumber-for-php/src/NumberParseException.php';
4936}
4937