<?php
/**
* Annual membership fee renewals (per calendar year).
*
* @package TonItaliaRegistration
*/
defined( 'ABSPATH' ) || exit;
/**
* Class TON_Reg_Membership_Renewals
*/
class TON_Reg_Membership_Renewals {
const STATUS_PENDING = 'pending';
const STATUS_PAID = 'paid';
/**
* @return string
*/
public static function table_name() {
global $wpdb;
return $wpdb->prefix . 'ton_reg_membership_renewals';
}
/**
* Create renewals 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,
registration_id bigint(20) unsigned NOT NULL,
year smallint(5) unsigned NOT NULL,
status varchar(20) NOT NULL DEFAULT 'pending',
paid_at datetime DEFAULT NULL,
paid_by bigint(20) unsigned DEFAULT NULL,
reminder_first_jan_sent_at datetime DEFAULT NULL,
reminder_last_jan_sent_at datetime DEFAULT NULL,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY registration_year (registration_id, year),
KEY year (year),
KEY status (status)
) {$charset};";
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
dbDelta( $sql );
}
/**
* Current calendar year (site timezone).
*
* @return int
*/
public static function current_year() {
return (int) wp_date( 'Y' );
}
/**
* @param object $registration Registration row.
* @return int
*/
public static function admission_year( $registration ) {
if ( ! empty( $registration->libro_associati_date ) ) {
$timestamp = strtotime( (string) $registration->libro_associati_date );
if ( $timestamp ) {
return (int) wp_date( 'Y', $timestamp );
}
}
if ( ! empty( $registration->created_at ) ) {
$timestamp = strtotime( (string) $registration->created_at );
if ( $timestamp ) {
return (int) wp_date( 'Y', $timestamp );
}
}
return self::current_year();
}
/**
* Years shown in admin checkboxes: from admission through current calendar year only.
*
* @param object $registration Registration row.
* @return array{start:int,end:int}
*/
public static function get_checkbox_year_range( $registration ) {
$start = self::admission_year( $registration );
$end = self::current_year();
if ( $start > $end ) {
$start = $end;
}
return array(
'start' => $start,
'end' => $end,
);
}
/**
* @param int $registration_id Registration ID.
* @param int $year Calendar year.
* @return string paid|pending
*/
public static function get_status( $registration_id, $year ) {
$row = self::get_by_registration_year( $registration_id, $year );
if ( ! $row || self::STATUS_PAID !== (string) $row->status ) {
return self::STATUS_PENDING;
}
return self::STATUS_PAID;
}
/**
* @param int $registration_id Registration ID.
* @param int $year Calendar year.
* @return object|null
*/
public static function get_by_registration_year( $registration_id, $year ) {
global $wpdb;
return $wpdb->get_row(
$wpdb->prepare(
'SELECT * FROM ' . self::table_name() . ' WHERE registration_id = %d AND year = %d',
$registration_id,
$year
)
);
}
/**
* @param int $registration_id Registration ID.
* @return array<int,object>
*/
public static function get_by_registration( $registration_id ) {
global $wpdb;
return $wpdb->get_results(
$wpdb->prepare(
'SELECT * FROM ' . self::table_name() . ' WHERE registration_id = %d ORDER BY year ASC',
$registration_id
)
);
}
/**
* @param array<int> $registration_ids Registration IDs.
* @param int $year Calendar year.
* @return array<int,string> Map registration_id => status.
*/
public static function get_statuses_for_registrations( array $registration_ids, $year ) {
global $wpdb;
$registration_ids = array_values( array_filter( array_map( 'intval', $registration_ids ) ) );
if ( empty( $registration_ids ) ) {
return array();
}
$placeholders = implode( ',', array_fill( 0, count( $registration_ids ), '%d' ) );
$params = array_merge( array( (int) $year ), $registration_ids );
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
$sql = $wpdb->prepare(
'SELECT registration_id, status FROM ' . self::table_name() . " WHERE year = %d AND registration_id IN ({$placeholders})",
$params
);
$rows = $wpdb->get_results( $sql );
$map = array();
foreach ( $registration_ids as $id ) {
$map[ $id ] = self::STATUS_PENDING;
}
foreach ( $rows as $row ) {
$map[ (int) $row->registration_id ] = (string) $row->status;
}
return $map;
}
/**
* @param int $registration_id Registration ID.
* @param int $year Calendar year.
* @param bool $paid Whether fee is paid.
* @param int $user_id Admin user marking payment.
* @return bool
*/
public static function set_paid( $registration_id, $year, $paid, $user_id = 0 ) {
global $wpdb;
$registration_id = (int) $registration_id;
$year = (int) $year;
$user_id = (int) $user_id;
$now = current_time( 'mysql', true );
$existing = self::get_by_registration_year( $registration_id, $year );
$data = array(
'status' => $paid ? self::STATUS_PAID : self::STATUS_PENDING,
'paid_at' => $paid ? $now : null,
'paid_by' => $paid ? ( $user_id ?: null ) : null,
'updated_at' => $now,
);
if ( $existing ) {
return false !== $wpdb->update(
self::table_name(),
$data,
array( 'id' => (int) $existing->id ),
null,
array( '%d' )
);
}
$data['registration_id'] = $registration_id;
$data['year'] = $year;
$data['created_at'] = $now;
return false !== $wpdb->insert( self::table_name(), $data );
}
/**
* Ensure a pending row exists for reminder tracking.
*
* @param int $registration_id Registration ID.
* @param int $year Calendar year.
* @return object|null
*/
public static function ensure_pending_row( $registration_id, $year ) {
$existing = self::get_by_registration_year( $registration_id, $year );
if ( $existing ) {
return $existing;
}
$now = current_time( 'mysql', true );
global $wpdb;
$wpdb->insert(
self::table_name(),
array(
'registration_id' => (int) $registration_id,
'year' => (int) $year,
'status' => self::STATUS_PENDING,
'created_at' => $now,
'updated_at' => $now,
)
);
return self::get_by_registration_year( $registration_id, $year );
}
/**
* @param int $registration_id Registration ID.
* @param int $year Calendar year.
* @param string $reminder_type first|last.
* @return bool
*/
public static function mark_reminder_sent( $registration_id, $year, $reminder_type ) {
global $wpdb;
$row = self::ensure_pending_row( $registration_id, $year );
if ( ! $row ) {
return false;
}
$column = 'first' === $reminder_type ? 'reminder_first_jan_sent_at' : 'reminder_last_jan_sent_at';
$now = current_time( 'mysql', true );
return false !== $wpdb->update(
self::table_name(),
array(
$column => $now,
'updated_at' => $now,
),
array( 'id' => (int) $row->id ),
null,
array( '%d' )
);
}
/**
* Admitted members without paid renewal for a given year.
*
* @param int $year Calendar year.
* @param string $reminder_type first|last — skip if that reminder was already sent.
* @return array<int,object> Registration rows.
*/
public static function get_unpaid_admitted( $year, $reminder_type = '' ) {
global $wpdb;
$reg_table = TON_Reg_Database::table_name();
$ren_table = self::table_name();
$year = (int) $year;
$sql = "SELECT r.* FROM {$reg_table} r
LEFT JOIN {$ren_table} ren ON ren.registration_id = r.id AND ren.year = %d
WHERE r.status = 'admitted'
AND r.anonymized_at IS NULL
AND r.email <> ''
AND ( ren.id IS NULL OR ren.status <> %s )";
$params = array( $year, self::STATUS_PAID );
if ( 'first' === $reminder_type ) {
$sql .= ' AND ( ren.reminder_first_jan_sent_at IS NULL )';
} elseif ( 'last' === $reminder_type ) {
$sql .= ' AND ( ren.reminder_last_jan_sent_at IS NULL )';
}
$sql .= ' ORDER BY r.cognome ASC, r.nome ASC, r.id ASC';
return $wpdb->get_results( $wpdb->prepare( $sql, $params ) );
}
/**
* Registration IDs with pending renewal for a year.
*
* @param int $year Calendar year.
* @return array<int>
*/
public static function get_unpaid_registration_ids( $year ) {
$rows = self::get_unpaid_admitted( $year );
return array_map(
static function ( $row ) {
return (int) $row->id;
},
$rows
);
}
/**
* Display label and CSS class for list table badge.
*
* @param string $status Renewal status (paid|pending).
* @param int $year Calendar year shown.
* @return array{label:string,class:string}
*/
public static function display_badge( $status, $year ) {
$year = (int) $year;
if ( self::STATUS_PAID === $status ) {
return array(
'label' => sprintf(
/* translators: %d: calendar year */
__( 'Pagato %d', 'ton-italia-registration' ),
$year
),
'class' => 'ton-reg-renewal--paid',
);
}
$month = (int) wp_date( 'n' );
$day = (int) wp_date( 'j' );
if ( 1 === $month && $day >= 25 ) {
return array(
'label' => sprintf(
/* translators: %d: calendar year */
__( 'Sollecito %d', 'ton-italia-registration' ),
$year
),
'class' => 'ton-reg-renewal--reminder',
);
}
if ( $month >= 2 ) {
return array(
'label' => sprintf(
/* translators: %d: calendar year */
__( 'Non pagato %d', 'ton-italia-registration' ),
$year
),
'class' => 'ton-reg-renewal--overdue',
);
}
return array(
'label' => sprintf(
/* translators: %d: calendar year */
__( 'Da pagare %d', 'ton-italia-registration' ),
$year
),
'class' => 'ton-reg-renewal--pending',
);
}
}
