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