1: <?php
2:
3: 4: 5:
6: class Quform_Repository
7: {
8: 9: 10: 11: 12:
13: public function getFormsTableName()
14: {
15: global $wpdb;
16:
17: return $wpdb->prefix . 'quform_forms';
18: }
19:
20: 21: 22: 23: 24:
25: public function getEntriesTableName()
26: {
27: global $wpdb;
28:
29: return $wpdb->prefix . 'quform_entries';
30: }
31:
32: 33: 34: 35: 36:
37: public function getEntryDataTableName()
38: {
39: global $wpdb;
40:
41: return $wpdb->prefix . 'quform_entry_data';
42: }
43:
44: 45: 46: 47: 48:
49: public function getEntryLabelsTableName()
50: {
51: global $wpdb;
52:
53: return $wpdb->prefix . 'quform_entry_labels';
54: }
55:
56: 57: 58: 59: 60:
61: public function getEntryEntryLabelsTableName()
62: {
63: global $wpdb;
64:
65: return $wpdb->prefix . 'quform_entry_entry_labels';
66: }
67:
68: 69: 70: 71: 72: 73: 74: 75:
76: public function all($active = null, $orderBy = 'id', $order = 'ASC')
77: {
78: global $wpdb;
79:
80: $sql = "SELECT forms.*, COALESCE(e.cnt, 0) AS entries FROM " . $this->getFormsTableName() . " forms
81: LEFT JOIN ( SELECT form_id, COUNT(*) AS cnt FROM " . $this->getEntriesTableName() . " WHERE status = 'normal' GROUP BY form_id ) e
82: ON forms.id = e.form_id
83: WHERE forms.trashed = 0";
84:
85: if ($active !== null) {
86: $sql .= $wpdb->prepare(" AND active = %d", $active ? 1 : 0);
87: }
88:
89: $orderBy = in_array($orderBy, array('id', 'name', 'entries', 'active', 'created_at', 'updated_at')) ? $orderBy : 'updated_at';
90: $order = strtoupper($order);
91: $order = in_array($order, array('ASC', 'DESC')) ? $order : 'DESC';
92:
93: $sql .= " ORDER BY $orderBy $order";
94:
95: return $wpdb->get_results($sql, ARRAY_A);
96: }
97:
98: 99: 100: 101: 102: 103: 104:
105: public function getFormsForListTable(array $args = array())
106: {
107: _deprecated_function(__METHOD__, '2.1.0', 'Quform_Repository::getForms()');
108:
109: return $this->getForms($args);
110: }
111:
112: 113: 114: 115: 116: 117:
118: public function getForms(array $args = array())
119: {
120: global $wpdb;
121:
122: $args = wp_parse_args($args, array(
123: 'active' => null,
124: 'orderby' => 'updated_at',
125: 'order' => 'DESC',
126: 'trashed' => false,
127: 'offset' => 0,
128: 'limit' => 20,
129: 'search' => ''
130: ));
131:
132: $sql = "SELECT SQL_CALC_FOUND_ROWS f.id, f.name, f.active, f.trashed, f.updated_at,
133: COALESCE(e.cnt, 0) AS entries,
134: COALESCE(u.cnt, 0) AS unread
135: FROM " . $this->getFormsTableName() . " f
136: LEFT JOIN ( SELECT form_id, COUNT(*) AS cnt FROM " . $this->getEntriesTableName() . " WHERE status = 'normal' GROUP BY form_id ) e
137: ON f.id = e.form_id
138: LEFT JOIN ( SELECT form_id, COUNT(*) AS cnt FROM " . $this->getEntriesTableName() . " WHERE status = 'normal' AND unread = 1 GROUP BY form_id ) u
139: ON f.id = u.form_id";
140:
141: $where = array($wpdb->prepare('trashed = %d', $args['trashed'] ? 1 : 0));
142:
143: if ($args['active'] !== null) {
144: $where[] = $wpdb->prepare('active = %d', $args['active'] ? 1 : 0);
145: }
146:
147: if (Quform::isNonEmptyString($args['search'])) {
148: $searchColumns = array();
149:
150: if (is_numeric($args['search'])) {
151: $searchColumns[] = $wpdb->prepare("id = %d", $args['search']);
152: }
153:
154: $searchColumns[] = $wpdb->prepare("name LIKE '%s'", '%' . $wpdb->esc_like($args['search']) . '%');
155:
156: $where[] = '(' . join(' OR ', $searchColumns) . ')';
157: }
158:
159: $sql .= " WHERE " . join(' AND ', $where);
160:
161:
162: $args['orderby'] = in_array($args['orderby'], array('id', 'name', 'entries', 'active', 'created_at', 'updated_at')) ? $args['orderby'] : 'updated_at';
163: $args['order'] = strtoupper($args['order']);
164: $args['order'] = in_array($args['order'], array('ASC', 'DESC')) ? $args['order'] : 'DESC';
165: $args['limit'] = (int) $args['limit'];
166: $args['offset'] = (int) $args['offset'];
167:
168: $sql .= " ORDER BY `{$args['orderby']}` {$args['order']} LIMIT {$args['limit']} OFFSET {$args['offset']}";
169:
170: return $wpdb->get_results($sql, ARRAY_A);
171: }
172:
173: 174: 175: 176: 177: 178:
179: public function allForms($active = null)
180: {
181: $forms = array();
182: $rows = $this->all($active);
183:
184: foreach ($rows as $row) {
185: $config = maybe_unserialize(base64_decode($row['config']));
186:
187: if (is_array($config)) {
188: $config = $this->addRowDataToConfig($row, $config);
189: $forms[] = $config;
190: }
191: }
192:
193: return $forms;
194: }
195:
196: 197: 198: 199: 200: 201:
202: public function getFormsById(array $ids)
203: {
204: global $wpdb;
205: $forms = array();
206: $ids = $this->sanitizeIds($ids);
207:
208: if (empty($ids)) {
209: return $forms;
210: }
211:
212: $joinedIds = $this->joinIds($ids);
213:
214: $sql = "SELECT * FROM " . $this->getFormsTableName() . " WHERE id IN ($joinedIds)";
215:
216: $rows = $wpdb->get_results($sql, ARRAY_A);
217:
218: if (is_array($rows)) {
219: foreach ($rows as $row) {
220: $config = maybe_unserialize(base64_decode($row['config']));
221: $config = $this->addRowDataToConfig($row, $config);
222: $forms[] = $config;
223: }
224: }
225:
226: return $forms;
227: }
228:
229: 230: 231: 232: 233: 234: 235: 236:
237: public function formsToSelectArray($active = null, $orderBy = 'updated_at', $order = 'DESC')
238: {
239: $rows = $this->all($active, $orderBy, $order);
240: $forms = array();
241:
242: foreach ($rows as $row) {
243: if ($row['active']) {
244: $forms[$row['id']] = $row['name'];
245: } else {
246:
247: $forms[$row['id']] = sprintf(__('%s (inactive)', 'quform'), $row['name']);
248: }
249: }
250:
251: return $forms;
252: }
253:
254: 255: 256: 257: 258: 259: 260:
261: public function count($active = null, $trashed = false)
262: {
263: global $wpdb;
264:
265: $sql = "SELECT COUNT(*) FROM " . $this->getFormsTableName();
266:
267: $where = array($wpdb->prepare('trashed = %d', $trashed ? 1 : 0));
268:
269: if ($active !== null) {
270: $where[] = $wpdb->prepare('active = %d', $active ? 1 : 0);
271: }
272:
273: $sql .= " WHERE " . join(' AND ', $where);
274:
275: return (int) $wpdb->get_var($sql);
276: }
277:
278: 279: 280: 281: 282: 283:
284: public function exists($id)
285: {
286: global $wpdb;
287:
288: $sql = $wpdb->prepare("SELECT id FROM " . $this->getFormsTableName() . " WHERE id = %d", (int) $id);
289:
290: return $wpdb->get_var($sql) !== null;
291: }
292:
293: 294: 295: 296: 297: 298:
299: public function entryExists($id)
300: {
301: global $wpdb;
302:
303: $sql = $wpdb->prepare("SELECT id FROM " . $this->getEntriesTableName() . " WHERE id = %d", (int) $id);
304:
305: return $wpdb->get_var($sql) !== null;
306: }
307:
308: 309: 310: 311: 312: 313:
314: public function find($id)
315: {
316: global $wpdb;
317:
318: $sql = "SELECT * FROM " . $this->getFormsTableName() . " WHERE id = %d";
319:
320: return $wpdb->get_row($wpdb->prepare($sql, $id), ARRAY_A);
321: }
322:
323: 324: 325: 326: 327:
328: public function first()
329: {
330: global $wpdb;
331:
332: $sql = "SELECT * FROM " . $this->getFormsTableName() . " WHERE trashed = 0 LIMIT 1";
333:
334: return $wpdb->get_row($sql, ARRAY_A);
335: }
336:
337: 338: 339: 340: 341: 342:
343: public function getConfig($id)
344: {
345: $row = $this->find($id);
346:
347: if ($row === null) {
348: return null;
349: }
350:
351: $config = maybe_unserialize(base64_decode($row['config']));
352:
353: if (is_array($config)) {
354: $config = $this->addRowDataToConfig($row, $config);
355: } else {
356: $config = null;
357: }
358:
359: return $config;
360: }
361:
362: 363: 364: 365: 366:
367: public function firstConfig()
368: {
369: $row = $this->first();
370:
371: if ($row === null) {
372: return null;
373: }
374:
375: $config = maybe_unserialize(base64_decode($row['config']));
376: $config = $this->addRowDataToConfig($row, $config);
377:
378: return $config;
379: }
380:
381: 382: 383:
384: public function activate()
385: {
386: require_once ABSPATH . 'wp-admin/includes/upgrade.php';
387:
388: $this->createFormsTable();
389: $this->createEntriesTable();
390: $this->createEntryDataTable();
391: $this->createEntryLabelsTable();
392: $this->createEntryEntryLabelsTable();
393: }
394:
395: 396: 397:
398: protected function createFormsTable()
399: {
400: global $wpdb;
401:
402: $sql = "CREATE TABLE " . $this->getFormsTableName() . " (
403: id int UNSIGNED NOT NULL AUTO_INCREMENT,
404: name varchar(64) NOT NULL,
405: config longtext NOT NULL,
406: active boolean NOT NULL DEFAULT 1,
407: trashed boolean NOT NULL DEFAULT 0,
408: created_at datetime NOT NULL,
409: updated_at datetime NOT NULL,
410: PRIMARY KEY (id),
411: KEY active (active),
412: KEY trashed (trashed)
413: ) " . $wpdb->get_charset_collate() . ";";
414:
415: dbDelta($sql);
416: }
417:
418: 419: 420:
421: protected function createEntriesTable()
422: {
423: global $wpdb;
424:
425: $sql = "CREATE TABLE " . $this->getEntriesTableName() . " (
426: id int UNSIGNED NOT NULL AUTO_INCREMENT,
427: form_id int UNSIGNED NOT NULL,
428: unread tinyint (1) UNSIGNED NOT NULL DEFAULT 1,
429: ip varchar(45) NOT NULL,
430: form_url varchar(512) NOT NULL,
431: referring_url varchar(512) NOT NULL,
432: post_id bigint(20) UNSIGNED,
433: created_by bigint(20) UNSIGNED,
434: created_at datetime NOT NULL,
435: updated_at datetime NOT NULL,
436: status varchar(20) NOT NULL DEFAULT 'normal',
437: PRIMARY KEY (id),
438: KEY form_id (form_id),
439: KEY status (status)
440: ) " . $wpdb->get_charset_collate() . ";";
441:
442: dbDelta($sql);
443: }
444:
445: 446: 447:
448: protected function createEntryDataTable()
449: {
450: global $wpdb;
451:
452: $sql = "CREATE TABLE " . $this->getEntryDataTableName() . " (
453: entry_id int UNSIGNED NOT NULL,
454: element_id int UNSIGNED NOT NULL,
455: value mediumtext,
456: PRIMARY KEY (entry_id,element_id),
457: KEY element_id (element_id)
458: ) " . $wpdb->get_charset_collate() . ";";
459:
460: dbDelta($sql);
461: }
462:
463: 464: 465:
466: protected function createEntryLabelsTable()
467: {
468: global $wpdb;
469:
470: $sql = "CREATE TABLE " . $this->getEntryLabelsTableName() . " (
471: id int UNSIGNED NOT NULL AUTO_INCREMENT,
472: form_id int UNSIGNED NOT NULL,
473: name varchar(128) NOT NULL,
474: color varchar (32) NOT NULL,
475: PRIMARY KEY (id),
476: KEY form_id (form_id)
477: ) " . $wpdb->get_charset_collate() . ";";
478:
479: dbDelta($sql);
480: }
481:
482: 483: 484:
485: protected function createEntryEntryLabelsTable()
486: {
487: global $wpdb;
488:
489: $sql = "CREATE TABLE " . $this->getEntryEntryLabelsTableName() . " (
490: entry_id int UNSIGNED NOT NULL,
491: entry_label_id int UNSIGNED NOT NULL,
492: PRIMARY KEY (entry_id,entry_label_id),
493: KEY entry_label_id (entry_label_id)
494: ) " . $wpdb->get_charset_collate() . ";";
495:
496: dbDelta($sql);
497: }
498:
499: 500: 501:
502: public function getDbVersion()
503: {
504: global $wpdb;
505:
506: return $wpdb->db_version();
507: }
508:
509: 510: 511: 512: 513: 514:
515: public function add(array $config)
516: {
517: global $wpdb;
518:
519:
520: $name = $config['name'];
521: $active = $config['active'];
522: $trashed = $config['trashed'];
523: unset($config['id'], $config['name'], $config['active'], $config['trashed']);
524:
525: $time = Quform::date('Y-m-d H:i:s', null, new DateTimeZone('UTC'));
526:
527: $result = $wpdb->insert($this->getFormsTableName(), array(
528: 'config' => base64_encode(serialize($config)),
529: 'name' => Quform::substr($name, 0, 64),
530: 'active' => $active,
531: 'trashed' => $trashed,
532: 'created_at' => $time,
533: 'updated_at' => $time
534: ));
535:
536: if ($result === false) {
537: return false;
538: }
539:
540: $config['id'] = $wpdb->insert_id;
541:
542:
543: $config['name'] = $name;
544: $config['active'] = $active;
545: $config['trashed'] = $trashed;
546:
547: $defaultEntryLabels = array(
548: array(
549: 'name' => __('Starred', 'quform'),
550: 'color' => '#F2D600'
551: )
552: );
553:
554: $defaultEntryLabels = apply_filters('quform_default_entry_labels', $defaultEntryLabels, $config);
555:
556: if (is_array($defaultEntryLabels)) {
557: $this->setFormEntryLabels($config['id'], $defaultEntryLabels);
558: }
559:
560: do_action('quform_add_form', $config);
561:
562: return $config;
563: }
564:
565: 566: 567: 568: 569: 570: 571: 572: 573:
574: public function save(array $config)
575: {
576: global $wpdb;
577:
578:
579: $id = $config['id'];
580: $name = $config['name'];
581: $active = $config['active'];
582: $trashed = $config['trashed'];
583:
584: unset($config['id'], $config['name'], $config['active'], $config['trashed']);
585:
586: $updateValues = array(
587: 'config' => base64_encode(serialize($config)),
588: 'name' => Quform::substr($name, 0, 64),
589: 'active' => $active,
590: 'trashed' => $trashed,
591: 'updated_at' => Quform::date('Y-m-d H:i:s', null, new DateTimeZone('UTC'))
592: );
593:
594: $updateWhere = array(
595: 'id' => $id
596: );
597:
598: $wpdb->update($this->getFormsTableName(), $updateValues, $updateWhere);
599:
600:
601: $config['id'] = $id;
602: $config['name'] = $name;
603: $config['active'] = $active;
604: $config['trashed'] = $trashed;
605:
606: do_action('quform_save_form', $config);
607:
608: return $config;
609: }
610:
611: 612: 613: 614: 615: 616: 617:
618: public function saveEntry(array $config, $entryId = null)
619: {
620: global $wpdb;
621:
622: if (isset($config['data']) && is_array($config['data'])) {
623: $data = $config['data'];
624: unset($config['data']);
625: } else {
626: $data = array();
627: }
628:
629: if ($entryId === null || ! $this->entryExists($entryId)) {
630:
631: $wpdb->insert($this->getEntriesTableName(), $config);
632: $entryId = $wpdb->insert_id;
633: } else {
634: $wpdb->update($this->getEntriesTableName(), $config, array('id' => $entryId));
635: }
636:
637: if (count($data)) {
638: $this->saveEntryData($entryId, $data);
639: $config['data'] = $data;
640: }
641:
642: $config['id'] = $entryId;
643:
644: return $config;
645: }
646:
647: 648: 649: 650: 651: 652:
653: public function saveEntryData($entryId, array $data)
654: {
655: if ( ! count($data)) {
656: return;
657: }
658:
659: global $wpdb;
660:
661: $sql = "INSERT INTO " . $this->getEntryDataTableName() . " (entry_id, element_id, value) VALUES ";
662: $values = array();
663: $placeholders = array();
664:
665: foreach ($data as $elementId => $value) {
666: $placeholders[] = "(%d, %d, %s)";
667: array_push($values, $entryId, $elementId, $value);
668: }
669:
670: $sql .= $wpdb->prepare(implode(', ', $placeholders), $values);
671:
672: $sql .= " ON DUPLICATE KEY UPDATE value = VALUES(value);";
673:
674: $wpdb->query($sql);
675: }
676:
677: 678: 679: 680: 681: 682:
683: public function activateForms(array $ids)
684: {
685: global $wpdb;
686:
687: $ids = $this->sanitizeIds($ids);
688:
689: if (empty($ids)) {
690: return 0;
691: }
692:
693: $joinedIds = $this->joinIds($ids);
694:
695: $sql = "UPDATE " . $this->getFormsTableName() . " SET active = 1 WHERE id IN ($joinedIds)";
696:
697: $affectedRows = (int) $wpdb->query($sql);
698:
699: foreach($ids as $id) {
700: do_action('quform_form_activated', $id);
701: }
702:
703: return $affectedRows;
704: }
705:
706: 707: 708: 709: 710: 711:
712: public function deactivateForms(array $ids)
713: {
714: global $wpdb;
715:
716: $ids = $this->sanitizeIds($ids);
717:
718: if (empty($ids)) {
719: return 0;
720: }
721:
722: $joinedIds = $this->joinIds($ids);
723:
724: $sql = "UPDATE " . $this->getFormsTableName() . " SET active = 0 WHERE id IN ($joinedIds)";
725:
726: $affectedRows = (int) $wpdb->query($sql);
727:
728: foreach($ids as $id) {
729: do_action('quform_form_deactivated', $id);
730: }
731:
732: return $affectedRows;
733: }
734:
735: 736: 737: 738: 739: 740:
741: public function trashForms(array $ids)
742: {
743: global $wpdb;
744:
745: $ids = $this->sanitizeIds($ids);
746:
747: if (empty($ids)) {
748: return 0;
749: }
750:
751: $joinedIds = $this->joinIds($ids);
752:
753: $sql = "UPDATE " . $this->getFormsTableName() . " SET trashed = 1 WHERE id IN ($joinedIds)";
754:
755: $affectedRows = (int) $wpdb->query($sql);
756:
757: foreach($ids as $id) {
758: do_action('quform_form_trashed', $id);
759: }
760:
761: return $affectedRows;
762: }
763:
764: 765: 766: 767: 768: 769:
770: public function untrashForms(array $ids)
771: {
772: global $wpdb;
773:
774: $ids = $this->sanitizeIds($ids);
775:
776: if (empty($ids)) {
777: return 0;
778: }
779:
780: $joinedIds = $this->joinIds($ids);
781:
782: $sql = "UPDATE " . $this->getFormsTableName() . " SET trashed = 0 WHERE id IN ($joinedIds)";
783:
784: $affectedRows = (int) $wpdb->query($sql);
785:
786: foreach($ids as $id) {
787: do_action('quform_form_untrashed', $id);
788: }
789:
790: return $affectedRows;
791: }
792:
793: 794: 795: 796: 797: 798:
799: public function deleteForms(array $ids)
800: {
801: global $wpdb;
802:
803: $ids = $this->sanitizeIds($ids);
804:
805: if (empty($ids)) {
806: return 0;
807: }
808:
809: $joinedIds = $this->joinIds($ids);
810:
811:
812: $wpdb->query("DELETE FROM " . $this->getEntryEntryLabelsTableName() . " WHERE entry_id IN (SELECT id FROM " . $this->getEntriesTableName() . " WHERE form_id IN ($joinedIds))");
813:
814:
815: $wpdb->query("DELETE FROM " . $this->getEntryLabelsTableName() . " WHERE form_id IN ($joinedIds)");
816:
817:
818: $wpdb->query("DELETE FROM " . $this->getEntryDataTableName() . " WHERE entry_id IN (SELECT id FROM " . $this->getEntriesTableName() . " WHERE form_id IN ($joinedIds))");
819:
820:
821: $wpdb->query("DELETE FROM " . $this->getEntriesTableName() . " WHERE form_id IN ($joinedIds)");
822:
823:
824: $affectedRows = (int) $wpdb->query("DELETE FROM " . $this->getFormsTableName() . " WHERE id IN ($joinedIds)");
825:
826: foreach($ids as $id) {
827: do_action('quform_form_deleted', $id);
828: }
829:
830: return $affectedRows;
831: }
832:
833: 834: 835: 836: 837: 838:
839: public function duplicateForms(array $ids)
840: {
841: $ids = $this->sanitizeIds($ids);
842: $newIds = array();
843:
844: foreach ($ids as $id) {
845: $config = $this->getConfig($id);
846:
847: if ( ! is_array($config)) {
848: continue;
849: }
850:
851: $config['active'] = true;
852:
853: $config['name'] = sprintf(_x('%s duplicate', 'form name duplicate', 'quform'), $config['name']);
854:
855: $config = $this->add($config);
856:
857: if (is_array($config)) {
858: $newIds[] = $config['id'];
859:
860: if (apply_filters('quform_duplicate_form_entry_labels', true)) {
861: $entryLabels = $this->getFormEntryLabels($id);
862:
863: foreach ($entryLabels as $key => $entryLabel) {
864: unset($entryLabels[$key]['id'], $entryLabels[$key]['form_id']);
865: }
866:
867: $this->setFormEntryLabels($config['id'], $entryLabels);
868: }
869: }
870: }
871:
872: return $newIds;
873: }
874:
875: 876: 877: 878: 879: 880:
881: protected function joinIds(array $ids)
882: {
883: $ids = array_map('esc_sql', $ids);
884: $ids = join(',', $ids);
885:
886: return $ids;
887: }
888:
889: 890: 891: 892: 893: 894:
895: protected function prepareIds(array $ids)
896: {
897: return $this->joinIds($this->sanitizeIds($ids));
898: }
899:
900: 901: 902: 903: 904: 905:
906: protected function sanitizeIds(array $ids)
907: {
908: $sanitized = array();
909:
910: foreach ($ids as $id) {
911: if ( ! is_numeric($id)) {
912: continue;
913: }
914:
915: $id = (int) $id;
916:
917: if ($id > 0) {
918: $sanitized[] = $id;
919: }
920: }
921:
922: $sanitized = array_unique($sanitized);
923:
924: return $sanitized;
925: }
926:
927: 928: 929: 930: 931: 932: 933:
934: protected function sanitiseIds(array $ids)
935: {
936: _deprecated_function(__METHOD__, '2.4.0', 'Quform_Repository::sanitizeIds()');
937:
938: return $this->sanitizeIds($ids);
939: }
940:
941: 942: 943: 944: 945: 946: 947: 948: 949:
950: public function getEntries(Quform_Form $form, array $args = array())
951: {
952: global $wpdb;
953:
954: $args = wp_parse_args($args, array(
955: 'unread' => null,
956: 'orderby' => 'created_at',
957: 'order' => 'DESC',
958: 'status' => 'normal',
959: 'offset' => 0,
960: 'limit' => 20,
961: 'search' => '',
962: 'labels' => array(),
963: 'label_operator' => 'OR',
964: 'created_by' => null
965: ));
966:
967: $sql = "SELECT SQL_CALC_FOUND_ROWS `entries`.*";
968:
969: $searchColumns = array(
970: 'entries.ip',
971: 'entries.form_url',
972: 'entries.referring_url',
973: 'entries.post_id',
974: 'entries.created_by',
975: 'entries.created_at',
976: 'entries.updated_at'
977: );
978:
979: $validOrderBy = array(
980: 'id',
981: 'ip',
982: 'form_url',
983: 'referring_url',
984: 'post_id',
985: 'created_by',
986: 'created_at',
987: 'updated_at'
988: );
989:
990: foreach ($form->getRecursiveIterator() as $element) {
991: if ($element->config('saveToDatabase')) {
992: $sql .= $wpdb->prepare(", GROUP_CONCAT(DISTINCT IF (data.element_id = %d, data.value, NULL)) AS element_%d", $element->getId(), $element->getId());
993: $searchColumns[] = $wpdb->prepare("element_%d", $element->getId());
994: $validOrderBy[] = $wpdb->prepare("element_%d", $element->getId());
995: }
996: }
997:
998: $sql .= ", GROUP_CONCAT(DISTINCT eel.entry_label_id) AS labels";
999:
1000: $whereClause = array($wpdb->prepare("entries.form_id = %d", $form->getId()));
1001:
1002: if ($args['unread'] !== null) {
1003: $whereClause[] = $wpdb->prepare("entries.unread = %d", $args['unread'] ? 1 : 0);
1004: }
1005:
1006: if (Quform::isNonEmptyString($args['status'])) {
1007: $whereClause[] = $wpdb->prepare("entries.status = %s", $args['status']);
1008: }
1009:
1010: $sql .= " FROM " . $this->getEntriesTableName() . " entries
1011: LEFT JOIN " . $this->getEntryDataTableName() . " data ON data.entry_id = entries.id
1012: LEFT JOIN " . $this->getEntryEntryLabelsTableName() . " eel ON eel.entry_id = entries.id
1013: WHERE " . join(' AND ', $whereClause) . "
1014: GROUP BY entries.id";
1015:
1016:
1017: if (Quform::isNonEmptyString($args['search']) || count($args['labels']) || is_numeric($args['created_by'])) {
1018: $sql .= " HAVING ";
1019: $having = array();
1020:
1021: if (Quform::isNonEmptyString($args['search'])) {
1022: $filteredSearchColumns = array();
1023:
1024: if (is_numeric($args['search'])) {
1025: $filteredSearchColumns[] = $wpdb->prepare('entries.id = %d', $args['search']);
1026: }
1027:
1028: $search = $wpdb->esc_like($args['search']);
1029:
1030: foreach ($searchColumns as $searchColumn) {
1031:
1032: if (($searchColumn == 'entries.created_at' || $searchColumn == 'entries.updated_at') && preg_match('/[^\d\-: ]/', $search)) {
1033: continue;
1034: }
1035:
1036: $filteredSearchColumns[] = $wpdb->prepare("$searchColumn LIKE '%s'", '%' . $search . '%');
1037: }
1038:
1039: $having[] = '(' . join(' OR ', $filteredSearchColumns) . ')';
1040: }
1041:
1042: if (count($args['labels'])) {
1043: $labels = array();
1044:
1045: foreach ($args['labels'] as $label) {
1046: $label = (int) $label;
1047:
1048: $labels[] = $wpdb->prepare(
1049: "(labels LIKE '%s' OR labels LIKE '%s' OR labels LIKE '%s' OR labels LIKE '%s')",
1050: $label,
1051: '%,' . $label,
1052: $label . ',%',
1053: '%,' . $label . ',%'
1054: );
1055: }
1056:
1057: $args['label_operator'] = strtoupper($args['label_operator']) == 'AND' ? 'AND' : 'OR';
1058:
1059: $having[] = '(' . join(sprintf(' %s ', $args['label_operator']), $labels) . ')';
1060: }
1061:
1062: if (is_numeric($args['created_by'])) {
1063: $having[] = '(' . $wpdb->prepare("entries.created_by = %d", (int) $args['created_by']) . ')';
1064: }
1065:
1066: $sql .= join(' AND ', $having);
1067: }
1068:
1069:
1070: $args['orderby'] = in_array($args['orderby'], $validOrderBy) ? $args['orderby'] : 'created_at';
1071: $args['order'] = strtoupper($args['order']);
1072: $args['order'] = in_array($args['order'], array('ASC', 'DESC')) ? $args['order'] : 'DESC';
1073: $args['limit'] = (int) $args['limit'];
1074: $args['offset'] = (int) $args['offset'];
1075:
1076:
1077: $sql .= " ORDER BY `{$args['orderby']}` {$args['order']}";
1078:
1079:
1080: if ($args['limit'] > 0) {
1081: $sql .= " LIMIT {$args['limit']} OFFSET {$args['offset']}";
1082: }
1083:
1084:
1085: $wpdb->query('SET @@GROUP_CONCAT_MAX_LEN = 65535');
1086:
1087: return $wpdb->get_results($sql, ARRAY_A);
1088: }
1089:
1090: 1091: 1092: 1093: 1094: 1095: 1096: 1097:
1098: public function listEntries(Quform_Form $form, array $columns, array $args = array())
1099: {
1100: global $wpdb;
1101:
1102: $args = wp_parse_args($args, array(
1103: 'unread' => null,
1104: 'orderby' => 'created_at',
1105: 'order' => 'DESC',
1106: 'status' => 'normal',
1107: 'offset' => 0,
1108: 'limit' => 20,
1109: 'search' => '',
1110: 'labels' => array(),
1111: 'label_operator' => 'OR',
1112: 'created_by' => null
1113: ));
1114:
1115: $sql = "SELECT SQL_CALC_FOUND_ROWS `entries`.*";
1116:
1117: $searchColumns = array(
1118: 'entries.ip',
1119: 'entries.form_url',
1120: 'entries.referring_url',
1121: 'entries.post_id',
1122: 'entries.created_by',
1123: 'entries.created_at',
1124: 'entries.updated_at'
1125: );
1126:
1127: $validOrderBy = array(
1128: 'id',
1129: 'ip',
1130: 'form_url',
1131: 'referring_url',
1132: 'post_id',
1133: 'created_by',
1134: 'created_at',
1135: 'updated_at'
1136: );
1137:
1138: foreach($columns as $column) {
1139: if (preg_match("/^element_(\d+)$/", $column, $matches)) {
1140: $sql .= $wpdb->prepare(", GROUP_CONCAT(DISTINCT IF (data.element_id = %d, data.value, NULL)) AS element_%d", $matches[1], $matches[1]);
1141: $validOrderBy[] = $wpdb->prepare("element_%d", $matches[1]);
1142: }
1143: }
1144:
1145: $sql .= ", GROUP_CONCAT(DISTINCT eel.entry_label_id) AS labels";
1146:
1147: $whereClause = array($wpdb->prepare("entries.form_id = %d", $form->getId()));
1148:
1149: if ($args['unread'] !== null) {
1150: $whereClause[] = $wpdb->prepare("entries.unread = %d", $args['unread'] ? 1 : 0);
1151: }
1152:
1153: if (Quform::isNonEmptyString($args['status'])) {
1154: $whereClause[] = $wpdb->prepare("entries.status = %s", $args['status']);
1155: }
1156:
1157: if (is_numeric($args['created_by'])) {
1158: $whereClause[] = $wpdb->prepare("entries.created_by = %d", (int) $args['created_by']);
1159: }
1160:
1161: if (Quform::isNonEmptyString($args['search'])) {
1162: $searchWhereClause = array();
1163:
1164: if (is_numeric($args['search'])) {
1165: $searchWhereClause[] = $wpdb->prepare('entries.id = %d', $args['search']);
1166: }
1167:
1168: $search = $wpdb->esc_like($args['search']);
1169:
1170: foreach ($searchColumns as $searchColumn) {
1171:
1172: if (($searchColumn == 'entries.created_at' || $searchColumn == 'entries.updated_at') && preg_match('/[^\d\-: ]/', $search)) {
1173: continue;
1174: }
1175:
1176: $searchWhereClause[] = $wpdb->prepare("$searchColumn LIKE '%s'", '%' . $search . '%');
1177: }
1178:
1179: $searchWhereClause[] = $wpdb->prepare(
1180: "(entries.id IN (
1181: SELECT DISTINCT entry_id
1182: FROM " . $this->getEntryDataTableName() . " sed
1183: LEFT JOIN " . $this->getEntriesTableName() . " se
1184: ON sed.entry_id = se.id
1185: WHERE se.form_id = %d AND `value` LIKE '%s'
1186: ))",
1187: $form->getId(),
1188: '%' . $search . '%'
1189: );
1190:
1191: $whereClause[] = '(' . join(' OR ', $searchWhereClause) . ')';
1192: }
1193:
1194: $sql .= " FROM " . $this->getEntriesTableName() . " entries
1195: LEFT JOIN " . $this->getEntryDataTableName() . " data ON data.entry_id = entries.id
1196: LEFT JOIN " . $this->getEntryEntryLabelsTableName() . " eel ON eel.entry_id = entries.id
1197: WHERE " . join(' AND ', $whereClause) . "
1198: GROUP BY entries.id";
1199:
1200:
1201: if (count($args['labels'])) {
1202: $labels = array();
1203:
1204: foreach ($args['labels'] as $label) {
1205: $label = (int) $label;
1206:
1207: $labels[] = $wpdb->prepare(
1208: "(labels LIKE '%s' OR labels LIKE '%s' OR labels LIKE '%s' OR labels LIKE '%s')",
1209: $label,
1210: '%,' . $label,
1211: $label . ',%',
1212: '%,' . $label . ',%'
1213: );
1214: }
1215:
1216: $args['label_operator'] = strtoupper($args['label_operator']) == 'AND' ? 'AND' : 'OR';
1217:
1218: if (count($labels)) {
1219: $sql .= ' HAVING (' . join(sprintf(' %s ', $args['label_operator']), $labels) . ')';
1220: }
1221: }
1222:
1223:
1224: $args['orderby'] = in_array($args['orderby'], $validOrderBy) ? $args['orderby'] : 'created_at';
1225: $args['order'] = strtoupper($args['order']);
1226: $args['order'] = in_array($args['order'], array('ASC', 'DESC')) ? $args['order'] : 'DESC';
1227: $args['limit'] = (int) $args['limit'];
1228: $args['offset'] = (int) $args['offset'];
1229:
1230:
1231: $sql .= " ORDER BY `{$args['orderby']}` {$args['order']}";
1232:
1233:
1234: if ($args['limit'] > 0) {
1235: $sql .= " LIMIT {$args['limit']} OFFSET {$args['offset']}";
1236: }
1237:
1238:
1239: $wpdb->query('SET @@GROUP_CONCAT_MAX_LEN = 65535');
1240:
1241: return $wpdb->get_results($sql, ARRAY_A);
1242: }
1243:
1244: 1245: 1246: 1247: 1248:
1249: public function getFoundRows()
1250: {
1251: global $wpdb;
1252:
1253: return (int) $wpdb->get_var("SELECT FOUND_ROWS()");
1254: }
1255:
1256: 1257: 1258: 1259: 1260: 1261: 1262: 1263:
1264: public function getEntryCount($formId, $unread = null, $status = 'normal')
1265: {
1266: global $wpdb;
1267:
1268: $whereClause = array($wpdb->prepare("form_id = %d", (int) $formId));
1269:
1270: if (is_bool($unread)) {
1271: $whereClause[] = $wpdb->prepare("unread = %d", $unread ? 1 : 0);
1272: }
1273:
1274: if (Quform::isNonEmptyString($status)) {
1275: $whereClause[] = $wpdb->prepare("status = %s", $status);
1276: }
1277:
1278: $count = $wpdb->get_var("SELECT COUNT(*) FROM " . $this->getEntriesTableName() . " WHERE " . join(' AND ', $whereClause));
1279:
1280: return (int) $count;
1281: }
1282:
1283: 1284: 1285: 1286: 1287: 1288:
1289: public function getEntryLabels($entryId)
1290: {
1291: global $wpdb;
1292:
1293: $sql = $wpdb->prepare("SELECT * FROM " . $this->getEntryLabelsTableName() . " WHERE `id` IN (SELECT entry_label_id FROM " . $this->getEntryEntryLabelsTableName() . " WHERE entry_id = %d)", $entryId);
1294:
1295: $labels = $wpdb->get_results($sql, ARRAY_A);
1296:
1297: if ( ! is_array($labels)) {
1298: $labels = array();
1299: }
1300:
1301: return $labels;
1302: }
1303:
1304: 1305: 1306: 1307: 1308: 1309:
1310: public function getFormEntryLabels($formId)
1311: {
1312: global $wpdb;
1313:
1314: $sql = $wpdb->prepare("SELECT * FROM " . $this->getEntryLabelsTableName() . " WHERE `form_id` = %d", $formId);
1315:
1316: $labels = $wpdb->get_results($sql, ARRAY_A);
1317:
1318: if ( ! is_array($labels)) {
1319: $labels = array();
1320: }
1321:
1322: return $labels;
1323: }
1324:
1325: 1326: 1327: 1328: 1329: 1330:
1331: public function setFormEntryLabels($formId, array $labels)
1332: {
1333: global $wpdb;
1334:
1335: $ids = array();
1336: $values = array();
1337:
1338: foreach ($labels as $label) {
1339: if (isset($label['id'])) {
1340: $ids[] = $label['id'];
1341: }
1342:
1343: $values[] = $wpdb->prepare(
1344: '(%d, %d, %s, %s)',
1345: Quform::get($label, 'id'),
1346: $formId,
1347: $label['name'],
1348: $label['color']
1349: );
1350: }
1351:
1352: $ids = $this->sanitizeIds($ids);
1353:
1354: if (empty($ids)) {
1355:
1356: $wpdb->query($wpdb->prepare(
1357: "DELETE el, eel FROM `" . $this->getEntryLabelsTableName() . "` el LEFT JOIN `" . $this->getEntryEntryLabelsTableName() . "` eel ON el.id = eel.entry_label_id WHERE el.form_id = %d",
1358: $formId
1359: ));
1360: } else {
1361: $joinedIds = $this->joinIds($ids);
1362:
1363:
1364: $wpdb->query($wpdb->prepare(
1365: "DELETE el, eel FROM `" . $this->getEntryLabelsTableName() . "` el LEFT JOIN `" . $this->getEntryEntryLabelsTableName() . "` eel ON el.id = eel.entry_label_id WHERE el.form_id = %d AND el.id NOT IN ($joinedIds)",
1366: $formId
1367: ));
1368: }
1369:
1370: if (count($values)) {
1371: $sql = "INSERT INTO " . $this->getEntryLabelsTableName() . " (id, form_id, name, color) VALUES ";
1372: $sql .= join(', ', $values);
1373: $sql .= " ON DUPLICATE KEY UPDATE form_id = VALUES(form_id), name = VALUES(name), color = VALUES(color)";
1374:
1375: $wpdb->query($sql);
1376: }
1377: }
1378:
1379: 1380: 1381: 1382: 1383: 1384:
1385: public function deleteFormEntryLabels($formId)
1386: {
1387: _deprecated_function(__METHOD__, '2.2.0', 'Quform_Repository::setFormEntryLabels($formId, array())');
1388:
1389: $this->setFormEntryLabels($formId, array());
1390: }
1391:
1392: 1393: 1394: 1395: 1396: 1397:
1398: public function addEntryEntryLabel($entryId, $entryLabelId)
1399: {
1400: global $wpdb;
1401:
1402: $wpdb->query($wpdb->prepare("INSERT IGNORE INTO " . $this->getEntryEntryLabelsTableName() . " (entry_id, entry_label_id) VALUES (%d, %d)", $entryId, $entryLabelId));
1403: }
1404:
1405: 1406: 1407: 1408: 1409: 1410:
1411: public function deleteEntryEntryLabel($entryId, $entryLabelId)
1412: {
1413: global $wpdb;
1414:
1415: $wpdb->query($wpdb->prepare("DELETE FROM " . $this->getEntryEntryLabelsTableName() . " WHERE entry_id = %d AND entry_label_id = %d", $entryId, $entryLabelId));
1416: }
1417:
1418: 1419: 1420: 1421: 1422: 1423:
1424: public function getFormIdFromEntryId($entryId)
1425: {
1426: global $wpdb;
1427:
1428: $entryId = (int) $entryId;
1429:
1430: $formId = $wpdb->get_var($wpdb->prepare("SELECT form_id FROM " . $this->getEntriesTableName() . " WHERE `id` = %d", $entryId));
1431:
1432: return (int) $formId;
1433: }
1434:
1435: 1436: 1437: 1438: 1439: 1440:
1441: public function findEntry($entryId)
1442: {
1443: global $wpdb;
1444:
1445: $entry = $wpdb->get_row(
1446: $wpdb->prepare(
1447: "SELECT * FROM " . $this->getEntriesTableName() . " WHERE id = %d",
1448: $entryId
1449: ),
1450: ARRAY_A
1451: );
1452:
1453: if (is_array($entry)) {
1454: $data = $wpdb->get_results(
1455: $wpdb->prepare(
1456: "SELECT `element_id`, `value` FROM " . $this->getEntryDataTableName() . " WHERE `entry_id` = %d",
1457: $entryId
1458: ),
1459: ARRAY_A
1460: );
1461:
1462: if (is_array($data)) {
1463: foreach ($data as $datum) {
1464: $entry['element_' . $datum['element_id']] = $datum['value'];
1465: }
1466: }
1467:
1468: return $entry;
1469: }
1470:
1471: return null;
1472: }
1473:
1474: 1475: 1476: 1477: 1478: 1479:
1480: public function hasDuplicateEntry(Quform_Element_Field $element)
1481: {
1482: global $wpdb;
1483:
1484: $query = "SELECT `e`.`id` FROM `" . $this->getEntryDataTableName() . "` ed LEFT JOIN `" .
1485: $this->getEntriesTableName() . "` e ON `ed`.`entry_id` = `e`.`id`
1486: WHERE `e`.`form_id` = %d
1487: AND `ed`.`element_id` = %d
1488: AND `ed`.`value` = '%s'";
1489:
1490: $args = array(
1491: $element->getForm()->getId(),
1492: $element->getId(),
1493: $element->getValueForStorage()
1494: );
1495:
1496: $entryId = $element->getForm()->getEntryId();
1497:
1498: if (is_numeric($entryId) && $entryId > 0) {
1499: $query .= " AND `e`.`id` != %d";
1500: $args[] = $entryId;
1501: }
1502:
1503: $result = $wpdb->get_row($wpdb->prepare($query, $args));
1504:
1505: return $result !== null;
1506: }
1507:
1508: 1509: 1510: 1511: 1512: 1513:
1514: public function readEntries(array $ids)
1515: {
1516: global $wpdb;
1517:
1518: $ids = $this->sanitizeIds($ids);
1519:
1520: if (empty($ids)) {
1521: return 0;
1522: }
1523:
1524: $joinedIds = $this->joinIds($ids);
1525:
1526: $sql = "UPDATE " . $this->getEntriesTableName() . " SET unread = 0 WHERE id IN ($joinedIds)";
1527:
1528: $affectedRows = (int) $wpdb->query($sql);
1529:
1530: foreach($ids as $id) {
1531: do_action('quform_entry_read', $id);
1532: }
1533:
1534: return $affectedRows;
1535: }
1536:
1537: 1538: 1539: 1540: 1541: 1542:
1543: public function unreadEntries(array $ids)
1544: {
1545: global $wpdb;
1546:
1547: $ids = $this->sanitizeIds($ids);
1548:
1549: if (empty($ids)) {
1550: return 0;
1551: }
1552:
1553: $joinedIds = $this->joinIds($ids);
1554:
1555: $sql = "UPDATE " . $this->getEntriesTableName() . " SET unread = 1 WHERE id IN ($joinedIds)";
1556:
1557: $affectedRows = (int) $wpdb->query($sql);
1558:
1559: foreach($ids as $id) {
1560: do_action('quform_entry_unread', $id);
1561: }
1562:
1563: return $affectedRows;
1564: }
1565:
1566: 1567: 1568: 1569: 1570: 1571:
1572: public function trashEntries(array $ids)
1573: {
1574: global $wpdb;
1575:
1576: $ids = $this->sanitizeIds($ids);
1577:
1578: if (empty($ids)) {
1579: return 0;
1580: }
1581:
1582: $joinedIds = $this->joinIds($ids);
1583:
1584: $sql = "UPDATE " . $this->getEntriesTableName() . " SET status = 'trash' WHERE id IN ($joinedIds)";
1585:
1586: $affectedRows = (int) $wpdb->query($sql);
1587:
1588: foreach($ids as $id) {
1589: do_action('quform_entry_trashed', $id);
1590: }
1591:
1592: return $affectedRows;
1593: }
1594:
1595: 1596: 1597: 1598: 1599: 1600:
1601: public function untrashEntries(array $ids)
1602: {
1603: global $wpdb;
1604:
1605: $ids = $this->sanitizeIds($ids);
1606:
1607: if (empty($ids)) {
1608: return 0;
1609: }
1610:
1611: $joinedIds = $this->joinIds($ids);
1612:
1613: $sql = "UPDATE " . $this->getEntriesTableName() . " SET status = 'normal' WHERE id IN ($joinedIds)";
1614:
1615: $affectedRows = (int) $wpdb->query($sql);
1616:
1617: foreach($ids as $id) {
1618: do_action('quform_entry_untrashed', $id);
1619: }
1620:
1621: return $affectedRows;
1622: }
1623:
1624: 1625: 1626: 1627: 1628: 1629:
1630: public function deleteEntries(array $ids)
1631: {
1632: global $wpdb;
1633:
1634: $ids = $this->sanitizeIds($ids);
1635:
1636: if (empty($ids)) {
1637: return 0;
1638: }
1639:
1640: $joinedIds = $this->joinIds($ids);
1641:
1642: foreach($ids as $id) {
1643: do_action('quform_pre_delete_entry', $id);
1644: }
1645:
1646:
1647: $wpdb->query("DELETE FROM " . $this->getEntryEntryLabelsTableName() . " WHERE entry_id IN ($joinedIds)");
1648:
1649:
1650: $wpdb->query("DELETE FROM " . $this->getEntryDataTableName() . " WHERE entry_id IN ($joinedIds)");
1651:
1652:
1653: $affectedRows = (int) $wpdb->query("DELETE FROM " . $this->getEntriesTableName() . " WHERE id IN ($joinedIds)");
1654:
1655: foreach($ids as $id) {
1656: do_action('quform_entry_deleted', $id);
1657: }
1658:
1659: return $affectedRows;
1660: }
1661:
1662: 1663: 1664: 1665: 1666:
1667: public function getAllUnreadEntriesCount()
1668: {
1669: global $wpdb;
1670:
1671: $sql = "SELECT COUNT(*) FROM " . $this->getEntriesTableName() . " WHERE unread = 1 AND status = 'normal';";
1672:
1673: return $wpdb->get_var($sql);
1674: }
1675:
1676: 1677: 1678:
1679: public function getAllFormsWithUnreadEntries()
1680: {
1681: global $wpdb;
1682:
1683: $sql = "SELECT f.id, f.name, (SELECT COUNT(*) FROM " . $this->getEntriesTableName() . " WHERE form_id = f.id AND unread = 1 AND status = 'normal') AS entries FROM " . $this->getFormsTableName() . " f HAVING entries > 0;";
1684:
1685: return $wpdb->get_results($sql, ARRAY_A);
1686: }
1687:
1688: 1689: 1690: 1691: 1692: 1693:
1694: public function getRecentEntries($count = null)
1695: {
1696: global $wpdb;
1697:
1698: $sql = "SELECT f.name, e.* FROM " . $this->getEntriesTableName() . " e LEFT JOIN " . $this->getFormsTableName() . " f ON e.form_id = f.id WHERE status = 'normal' ORDER BY e.created_at DESC";
1699:
1700: if (is_numeric($count)) {
1701: $sql .= $wpdb->prepare(" LIMIT %d", $count);
1702: }
1703:
1704: return $wpdb->get_results($sql, ARRAY_A);
1705: }
1706:
1707: 1708: 1709: 1710: 1711:
1712: protected function addRowDataToConfig(array $row, array $config)
1713: {
1714: $config['id'] = (int) $row['id'];
1715: $config['name'] = $row['name'];
1716: $config['active'] = $row['active'] == 1;
1717: $config['trashed'] = $row['trashed'] == 1;
1718:
1719: return $config;
1720: }
1721:
1722: 1723: 1724: 1725: 1726: 1727: 1728: 1729: 1730:
1731: public function exportEntries(Quform_Form $form, $from = '', $to = '')
1732: {
1733: global $wpdb;
1734:
1735:
1736: $sql = "SELECT `entries`.*";
1737:
1738: foreach ($form->getRecursiveIterator() as $element) {
1739: if ($element->config('saveToDatabase')) {
1740: $sql .= ", GROUP_CONCAT(if (`data`.`element_id` = {$element->getId()}, value, NULL)) AS `element_{$element->getId()}`";
1741: }
1742: }
1743:
1744: $sql .= $wpdb->prepare(" FROM `" . $this->getEntriesTableName() . "` `entries`
1745: LEFT JOIN `" . $this->getEntryDataTableName() . "` `data` ON `data`.`entry_id` = `entries`.`id`
1746: WHERE `entries`.`form_id` = %d AND `entries`.`status` = 'normal'", $form->getId());
1747:
1748: $dateParts = array();
1749:
1750: if ($from) {
1751: $dateParts[] = $wpdb->prepare('`entries`.`created_at` >= %s', get_gmt_from_date($from . ' 00:00:00'));
1752: }
1753:
1754: if ($to) {
1755: $dateParts[] = $wpdb->prepare('`entries`.`created_at` <= %s', get_gmt_from_date($to . ' 23:59:59'));
1756: }
1757:
1758: if (count($dateParts)) {
1759: $sql .= ' AND (' . join(' AND ', $dateParts) . ')';
1760: }
1761:
1762: $sql .= " GROUP BY `entries`.`id`;";
1763:
1764: $sql = apply_filters('quform_export_entries_query_' . $form->getId(), $sql, $form, $from, $to);
1765:
1766: $wpdb->query('SET @@GROUP_CONCAT_MAX_LEN = 65535');
1767:
1768: return $wpdb->get_results($sql, ARRAY_A);
1769: }
1770:
1771: 1772: 1773: 1774: 1775:
1776: public function getFormsByUpdatedAt()
1777: {
1778: global $wpdb;
1779:
1780: $sql = "SELECT `id`, `name` FROM " . $this->getFormsTableName() . " WHERE `trashed` = 0 AND `active` = 1 ORDER BY `updated_at` DESC";
1781:
1782: return $wpdb->get_results($sql, ARRAY_A);
1783: }
1784:
1785: 1786: 1787: 1788: 1789: 1790: 1791:
1792: public function entryExistsByFormIdAndCreatedBy($formId, $createdBy)
1793: {
1794: global $wpdb;
1795:
1796: $sql = $wpdb->prepare(
1797: "SELECT id FROM " . $this->getEntriesTableName() . " WHERE form_id = %d AND created_by = %d AND status = 'normal'",
1798: $formId,
1799: $createdBy
1800: );
1801:
1802: return $wpdb->get_var($sql) !== null;
1803: }
1804:
1805: 1806: 1807: 1808: 1809: 1810: 1811:
1812: public function entryExistsByFormIdAndIpAddress($formId, $ipAddress)
1813: {
1814: global $wpdb;
1815:
1816: $sql = $wpdb->prepare(
1817: "SELECT id FROM " . $this->getEntriesTableName() . " WHERE form_id = %d AND ip = %s AND status = 'normal'",
1818: $formId,
1819: $ipAddress
1820: );
1821:
1822: return $wpdb->get_var($sql) !== null;
1823: }
1824:
1825: 1826: 1827:
1828: public function uninstall()
1829: {
1830: global $wpdb;
1831:
1832:
1833: foreach ($this->getTables() as $table) {
1834: $wpdb->query("DROP TABLE IF EXISTS `$table`");
1835: }
1836:
1837:
1838: delete_metadata('user', 0, 'quform_recent_forms', '', true);
1839: delete_metadata('user', 0, 'quform_forms_order_by', '', true);
1840: delete_metadata('user', 0, 'quform_forms_order', '', true);
1841: delete_metadata('user', 0, 'quform_forms_per_page', '', true);
1842: delete_metadata('user', 0, 'quform_entries_order_by', '', true);
1843: delete_metadata('user', 0, 'quform_entries_order', '', true);
1844: delete_metadata('user', 0, 'quform_entries_per_page', '', true);
1845: delete_metadata('user', 0, 'quform_export_entries_format_settings', '', true);
1846: }
1847:
1848: 1849: 1850: 1851: 1852:
1853: protected function getTables()
1854: {
1855: return array(
1856: $this->getFormsTableName(),
1857: $this->getEntriesTableName(),
1858: $this->getEntryDataTableName(),
1859: $this->getEntryLabelsTableName(),
1860: $this->getEntryEntryLabelsTableName()
1861: );
1862: }
1863:
1864: 1865: 1866: 1867: 1868: 1869:
1870: public function dropTablesOnSiteDeletion($tables)
1871: {
1872: return array_merge($tables, $this->getTables());
1873: }
1874: }
1875: