<?php
/**
* Database operations.
*
* @package TonItaliaRegistration
*/
defined( 'ABSPATH' ) || exit;
/**
* Class TON_Reg_Database
*/
class TON_Reg_Database {
/**
* @return string
*/
public static function table_name() {
global $wpdb;
return $wpdb->prefix . TON_REG_TABLE;
}
/**
* Create registrations table.
*/
public static function create_table() {
global $wpdb;
$table = self::table_name();
$charset = $wpdb->get_charset_collate();
$sql = "CREATE TABLE {$table} (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
user_id bigint(20) unsigned DEFAULT NULL,
cognome varchar(100) NOT NULL DEFAULT '',
nome varchar(100) NOT NULL DEFAULT '',
luogo_nascita varchar(120) NOT NULL DEFAULT '',
provincia_nascita char(2) NOT NULL DEFAULT '',
data_nascita date DEFAULT NULL,
codice_fiscale char(16) NOT NULL DEFAULT '',
comune_residenza varchar(120) NOT NULL DEFAULT '',
provincia_residenza char(2) NOT NULL DEFAULT '',
indirizzo varchar(200) NOT NULL DEFAULT '',
numero_civico varchar(20) NOT NULL DEFAULT '',
cap char(10) NOT NULL DEFAULT '',
telefono varchar(40) NOT NULL DEFAULT '',
email varchar(190) NOT NULL DEFAULT '',
luogo_dichiarazione varchar(120) NOT NULL DEFAULT '',
data_dichiarazione date DEFAULT NULL,
bonifico_effettuato tinyint(1) NOT NULL DEFAULT 0,
consenso_statuto tinyint(1) NOT NULL DEFAULT 0,
consenso_privacy tinyint(1) NOT NULL DEFAULT 0,
consenso_newsletter tinyint(1) NOT NULL DEFAULT 0,
registration_ip varchar(45) NOT NULL DEFAULT '',
user_agent varchar(255) NOT NULL DEFAULT '',
privacy_text_version varchar(32) NOT NULL DEFAULT '',
consent_statuto_at datetime DEFAULT NULL,
consent_privacy_at datetime DEFAULT NULL,
status varchar(20) NOT NULL DEFAULT 'pending',
admin_notes text NULL,
libro_associati_date date DEFAULT NULL,
doc_identita_id bigint(20) unsigned DEFAULT NULL,
doc_bonifico_id bigint(20) unsigned DEFAULT NULL,
doc_pagamento_id bigint(20) unsigned DEFAULT NULL,
gdpr_log longtext NULL,
mailchimp_status varchar(20) DEFAULT NULL,
mailchimp_synced_at datetime DEFAULT NULL,
mailchimp_error varchar(255) DEFAULT NULL,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
anonymized_at datetime DEFAULT NULL,
PRIMARY KEY (id),
KEY email (email),
KEY codice_fiscale (codice_fiscale),
KEY status (status),
KEY created_at (created_at),
KEY user_id (user_id)
) {$charset};";
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
dbDelta( $sql );
}
/**
* @param array<string, mixed> $data Row data.
* @return int|false Insert ID or false.
*/
public static function insert( $data ) {
global $wpdb;
$now = current_time( 'mysql', true );
$data['created_at'] = $now;
$data['updated_at'] = $now;
$result = $wpdb->insert( self::table_name(), $data );
return false === $result ? false : (int) $wpdb->insert_id;
}
/**
* @param int $id Registration ID.
* @param array<string,mixed> $data Data.
* @return bool
*/
public static function update( $id, $data ) {
global $wpdb;
$data['updated_at'] = current_time( 'mysql', true );
return false !== $wpdb->update(
self::table_name(),
$data,
array( 'id' => $id ),
null,
array( '%d' )
);
}
/**
* @param int $id Registration ID.
* @return object|null
*/
public static function get( $id ) {
global $wpdb;
return $wpdb->get_row(
$wpdb->prepare(
'SELECT * FROM ' . self::table_name() . ' WHERE id = %d',
$id
)
);
}
/**
* @param int $id Registration ID.
* @return bool
*/
public static function delete( $id ) {
global $wpdb;
return false !== $wpdb->delete(
self::table_name(),
array( 'id' => $id ),
array( '%d' )
);
}
/**
* @param string $email Email.
* @param string $cf Codice fiscale.
* @return object|null
*/
public static function find_active_by_email_or_cf( $email, $cf ) {
global $wpdb;
return $wpdb->get_row(
$wpdb->prepare(
'SELECT * FROM ' . self::table_name() . " WHERE ( email = %s OR codice_fiscale = %s ) AND status IN ('pending','admitted') AND anonymized_at IS NULL LIMIT 1",
$email,
$cf
)
);
}
/**
* @param array<string,mixed> $args Query args.
* @return array<int,object>
*/
public static function query( $args = array() ) {
global $wpdb;
$defaults = array(
'status' => '',
'search' => '',
'limit' => 20,
'offset' => 0,
'orderby' => 'created_at',
'order' => 'DESC',
);
$args = wp_parse_args( $args, $defaults );
$where = array( '1=1' );
$params = array();
if ( ! empty( $args['status'] ) ) {
$where[] = 'status = %s';
$params[] = $args['status'];
}
if ( ! empty( $args['search'] ) ) {
$like = '%' . $wpdb->esc_like( $args['search'] ) . '%';
$where[] = '( cognome LIKE %s OR nome LIKE %s OR email LIKE %s OR codice_fiscale LIKE %s )';
$params = array_merge( $params, array( $like, $like, $like, $like ) );
}
$allowed_orderby = array( 'created_at', 'cognome', 'nome', 'email', 'status' );
$orderby = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'created_at';
$order = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';
$sql = 'SELECT * FROM ' . self::table_name() . ' WHERE ' . implode( ' AND ', $where )
. " ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";
$params[] = (int) $args['limit'];
$params[] = (int) $args['offset'];
if ( ! empty( $params ) ) {
$sql = $wpdb->prepare( $sql, $params );
}
return $wpdb->get_results( $sql );
}
/**
* @param array<string,mixed> $args Args.
* @return int
*/
public static function count( $args = array() ) {
global $wpdb;
$where = array( '1=1' );
$params = array();
if ( ! empty( $args['status'] ) ) {
$where[] = 'status = %s';
$params[] = $args['status'];
}
if ( ! empty( $args['search'] ) ) {
$like = '%' . $wpdb->esc_like( $args['search'] ) . '%';
$where[] = '( cognome LIKE %s OR nome LIKE %s OR email LIKE %s OR codice_fiscale LIKE %s )';
$params = array_merge( $params, array( $like, $like, $like, $like ) );
}
$sql = 'SELECT COUNT(*) FROM ' . self::table_name() . ' WHERE ' . implode( ' AND ', $where );
if ( ! empty( $params ) ) {
$sql = $wpdb->prepare( $sql, $params );
}
return (int) $wpdb->get_var( $sql );
}
}
