<?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 WP_User|int|null $user WordPress user or user ID.
* @return object|null
*/
public static function get_for_wp_user( $user ) {
if ( is_numeric( $user ) ) {
$user = get_user_by( 'id', (int) $user );
}
if ( ! $user instanceof WP_User ) {
return null;
}
$row = self::get_by_user_id( (int) $user->ID );
if ( $row ) {
return $row;
}
if ( empty( $user->user_email ) ) {
return null;
}
global $wpdb;
return $wpdb->get_row(
$wpdb->prepare(
'SELECT * FROM ' . self::table_name() . ' WHERE email = %s AND anonymized_at IS NULL ORDER BY id DESC LIMIT 1',
$user->user_email
)
);
}
/**
* @param int $user_id WordPress user ID.
* @return object|null
*/
public static function get_by_user_id( $user_id ) {
global $wpdb;
if ( ! $user_id ) {
return null;
}
return $wpdb->get_row(
$wpdb->prepare(
'SELECT * FROM ' . self::table_name() . ' WHERE user_id = %d AND anonymized_at IS NULL ORDER BY id DESC LIMIT 1',
$user_id
)
);
}
/**
* @param array<string,mixed> $args Query args.
* @return array<int,object>
*/
public static function query( $args = array() ) {
global $wpdb;
$defaults = array(
'status' => '',
'search' => '',
'exclude_anonymized' => false,
'renewal_unpaid' => false,
'renewal_year' => 0,
'limit' => 20,
'offset' => 0,
'orderby' => 'created_at',
'order' => 'DESC',
);
$args = wp_parse_args( $args, $defaults );
$where = array( '1=1' );
$params = array();
if ( ! empty( $args['exclude_anonymized'] ) ) {
$where[] = 'anonymized_at IS NULL';
}
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 ) );
}
if ( ! empty( $args['renewal_unpaid'] ) ) {
$renewal_year = ! empty( $args['renewal_year'] ) ? (int) $args['renewal_year'] : TON_Reg_Membership_Renewals::current_year();
$ren_table = TON_Reg_Membership_Renewals::table_name();
$reg_table = self::table_name();
$where[] = "status = 'admitted'";
$where[] = 'anonymized_at IS NULL';
$where[] = "NOT EXISTS (
SELECT 1 FROM {$ren_table} ren
WHERE ren.registration_id = {$reg_table}.id
AND ren.year = {$renewal_year}
AND ren.status = '" . esc_sql( TON_Reg_Membership_Renewals::STATUS_PAID ) . "'
)";
}
$allowed_orderby = array( 'id', '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;
$defaults = array(
'status' => '',
'search' => '',
'exclude_anonymized' => false,
'renewal_unpaid' => false,
'renewal_year' => 0,
);
$args = wp_parse_args( $args, $defaults );
$where = array( '1=1' );
$params = array();
if ( ! empty( $args['exclude_anonymized'] ) ) {
$where[] = 'anonymized_at IS NULL';
}
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 ) );
}
if ( ! empty( $args['renewal_unpaid'] ) ) {
$renewal_year = ! empty( $args['renewal_year'] ) ? (int) $args['renewal_year'] : TON_Reg_Membership_Renewals::current_year();
$ren_table = TON_Reg_Membership_Renewals::table_name();
$reg_table = self::table_name();
$where[] = "status = 'admitted'";
$where[] = 'anonymized_at IS NULL';
$where[] = "NOT EXISTS (
SELECT 1 FROM {$ren_table} ren
WHERE ren.registration_id = {$reg_table}.id
AND ren.year = {$renewal_year}
AND ren.status = '" . esc_sql( TON_Reg_Membership_Renewals::STATUS_PAID ) . "'
)";
}
$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 );
}
/**
* Query registrations for CSV export (no pagination by default).
*
* @param array<string,mixed> $args Query args.
* @return array<int,object>
*/
public static function query_for_export( $args = array() ) {
global $wpdb;
$defaults = array(
'status' => 'admitted',
'search' => '',
'exclude_anonymized' => true,
'limit' => 0,
'orderby' => 'cognome',
'order' => 'ASC',
);
$args = wp_parse_args( $args, $defaults );
$where = array( '1=1' );
$params = array();
if ( ! empty( $args['exclude_anonymized'] ) ) {
$where[] = 'anonymized_at IS NULL';
}
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( 'id', 'created_at', 'cognome', 'nome', 'email', 'status', 'libro_associati_date' );
$orderby = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'cognome';
$order = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';
$sql = 'SELECT * FROM ' . self::table_name() . ' WHERE ' . implode( ' AND ', $where )
. " ORDER BY {$orderby} {$order}, id ASC";
if ( ! empty( $args['limit'] ) ) {
$sql .= ' LIMIT %d';
$params[] = (int) $args['limit'];
}
if ( ! empty( $params ) ) {
$sql = $wpdb->prepare( $sql, $params );
}
return $wpdb->get_results( $sql );
}
/**
* Ordered registration IDs for admin prev/next navigation (same filters as query()).
*
* @param array<string,mixed> $args Query args.
* @return array<int,int>
*/
public static function query_ids( $args = array() ) {
global $wpdb;
$defaults = array(
'status' => '',
'search' => '',
'exclude_anonymized' => false,
'renewal_unpaid' => false,
'renewal_year' => 0,
'orderby' => 'created_at',
'order' => 'DESC',
);
$args = wp_parse_args( $args, $defaults );
$where = array( '1=1' );
$params = array();
if ( ! empty( $args['exclude_anonymized'] ) ) {
$where[] = 'anonymized_at IS NULL';
}
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 ) );
}
if ( ! empty( $args['renewal_unpaid'] ) ) {
$renewal_year = ! empty( $args['renewal_year'] ) ? (int) $args['renewal_year'] : TON_Reg_Membership_Renewals::current_year();
$ren_table = TON_Reg_Membership_Renewals::table_name();
$reg_table = self::table_name();
$where[] = "status = 'admitted'";
$where[] = 'anonymized_at IS NULL';
$where[] = "NOT EXISTS (
SELECT 1 FROM {$ren_table} ren
WHERE ren.registration_id = {$reg_table}.id
AND ren.year = {$renewal_year}
AND ren.status = '" . esc_sql( TON_Reg_Membership_Renewals::STATUS_PAID ) . "'
)";
}
$allowed_orderby = array( 'id', '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 id FROM ' . self::table_name() . ' WHERE ' . implode( ' AND ', $where )
. " ORDER BY {$orderby} {$order}, id ASC";
if ( ! empty( $params ) ) {
$sql = $wpdb->prepare( $sql, $params );
}
$ids = $wpdb->get_col( $sql );
return array_map( 'intval', is_array( $ids ) ? $ids : array() );
}
/**
* @param int $current_id Current registration ID.
* @param array<string,mixed> $args Same filters/order as list view.
* @return array{prev:int|null,next:int|null}
*/
public static function get_adjacent_ids( $current_id, $args = array() ) {
$current_id = (int) $current_id;
$ids = self::query_ids( $args );
$index = array_search( $current_id, $ids, true );
if ( false === $index ) {
return array(
'prev' => null,
'next' => null,
);
}
return array(
'prev' => $index > 0 ? (int) $ids[ $index - 1 ] : null,
'next' => $index < count( $ids ) - 1 ? (int) $ids[ $index + 1 ] : null,
);
}
}
