prefix . 'devisqr_quotes';
}
/**
* Items table name.
*
* @return string
*/
public static function get_items_table_name() {
global $wpdb;
return $wpdb->prefix . 'devisqr_quote_items';
}
/**
* Create / upgrade tables (dbDelta).
*/
public static function create_tables() {
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
$quotes_table = self::get_table_name();
$items_table = self::get_items_table_name();
$sql_quotes = "CREATE TABLE {$quotes_table} (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
quote_number varchar(50) NOT NULL,
user_id bigint(20) unsigned NOT NULL,
status varchar(20) NOT NULL DEFAULT 'pending',
total_ht decimal(12,2) NOT NULL DEFAULT 0,
total_tax decimal(12,2) NOT NULL DEFAULT 0,
total_ttc decimal(12,2) NOT NULL DEFAULT 0,
discount_percent decimal(5,2) NOT NULL DEFAULT 0,
pdf_path varchar(500) DEFAULT NULL,
attachment_path varchar(500) DEFAULT NULL,
wc_order_id bigint(20) unsigned DEFAULT NULL,
customer_note text DEFAULT NULL,
admin_note text DEFAULT NULL,
expires_at datetime DEFAULT NULL,
created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY quote_number (quote_number),
KEY user_id (user_id),
KEY status (status),
KEY wc_order_id (wc_order_id),
KEY created_at (created_at)
) {$charset_collate};";
$sql_items = "CREATE TABLE {$items_table} (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
quote_id bigint(20) unsigned NOT NULL,
product_id bigint(20) unsigned NOT NULL,
variation_id bigint(20) unsigned DEFAULT 0,
product_name varchar(500) NOT NULL,
product_sku varchar(100) DEFAULT NULL,
quantity decimal(10,2) NOT NULL DEFAULT 1,
unit_price_ht decimal(14,4) NOT NULL DEFAULT 0,
unit_price_ttc decimal(14,4) NOT NULL DEFAULT 0,
tax_rate decimal(6,3) NOT NULL DEFAULT 0,
line_total_ht decimal(14,4) NOT NULL DEFAULT 0,
line_total_ttc decimal(14,4) NOT NULL DEFAULT 0,
discount_percent decimal(5,2) NOT NULL DEFAULT 0,
meta_data longtext DEFAULT NULL,
PRIMARY KEY (id),
KEY quote_id (quote_id),
KEY product_id (product_id)
) {$charset_collate};";
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
dbDelta( $sql_quotes );
dbDelta( $sql_items );
}
/* ------------------------------------------------------------------ */
/* File storage */
/* ------------------------------------------------------------------ */
/**
* Absolute storage base dir (uploads/devisqr-quotes).
*
* @param string $sub '' | 'attachments'.
* @return string
*/
public static function storage_dir( $sub = '' ) {
$upload_dir = wp_upload_dir();
$dir = trailingslashit( $upload_dir['basedir'] ) . self::STORAGE_DIR;
if ( $sub ) {
$dir .= '/' . $sub;
}
return $dir;
}
/**
* Create storage dirs with .htaccess + index.php protection.
*/
public static function ensure_storage() {
foreach ( array( self::storage_dir(), self::storage_dir( 'attachments' ) ) as $dir ) {
if ( ! file_exists( $dir ) ) {
wp_mkdir_p( $dir );
}
if ( ! file_exists( $dir . '/.htaccess' ) ) {
$rules = "\nRequire all denied\n\n\nOrder deny,allow\nDeny from all\n\n";
file_put_contents( $dir . '/.htaccess', $rules ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_file_put_contents
}
if ( ! file_exists( $dir . '/index.php' ) ) {
file_put_contents( $dir . '/index.php', 'get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
$wpdb->prepare(
'SELECT quote_number FROM %i WHERE quote_number LIKE %s ORDER BY id DESC LIMIT 1',
$table,
$prefix . '-' . $year . $month . '-%'
)
);
$seq = 1;
if ( $last ) {
$parts = explode( '-', $last );
$seq = intval( end( $parts ) ) + 1;
}
return sprintf( '%s-%s%s-%04d', $prefix, $year, $month, $seq );
}
/**
* Format map for wpdb insert/update.
*
* @return array
*/
private static function quote_formats() {
return array(
'quote_number' => '%s',
'user_id' => '%d',
'status' => '%s',
'total_ht' => '%f',
'total_tax' => '%f',
'total_ttc' => '%f',
'discount_percent' => '%f',
'pdf_path' => '%s',
'attachment_path' => '%s',
'wc_order_id' => '%d',
'customer_note' => '%s',
'admin_note' => '%s',
'expires_at' => '%s',
'created_at' => '%s',
);
}
/**
* Item format map.
*
* @return array
*/
private static function item_formats() {
return array(
'quote_id' => '%d',
'product_id' => '%d',
'variation_id' => '%d',
'product_name' => '%s',
'product_sku' => '%s',
'quantity' => '%f',
'unit_price_ht' => '%f',
'unit_price_ttc' => '%f',
'tax_rate' => '%f',
'line_total_ht' => '%f',
'line_total_ttc' => '%f',
'discount_percent' => '%f',
'meta_data' => '%s',
);
}
/**
* Build the formats array for a data array.
*
* @param array $data Data.
* @param array $map Format map.
* @return array
*/
private static function formats_for( $data, $map ) {
$formats = array();
foreach ( array_keys( $data ) as $key ) {
$formats[] = isset( $map[ $key ] ) ? $map[ $key ] : '%s';
}
return $formats;
}
/**
* Insert a quote.
*
* @param array $data Columns.
* @return int|false
*/
public static function insert_quote( $data ) {
global $wpdb;
$result = $wpdb->insert( self::get_table_name(), $data, self::formats_for( $data, self::quote_formats() ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
return $result ? $wpdb->insert_id : false;
}
/**
* Insert an item.
*
* @param array $data Columns.
* @return int|false
*/
public static function insert_quote_item( $data ) {
global $wpdb;
return $wpdb->insert( self::get_items_table_name(), $data, self::formats_for( $data, self::item_formats() ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
}
/**
* Get a quote by ID.
*
* @param int $quote_id Quote ID.
* @return object|null
*/
public static function get_quote( $quote_id ) {
global $wpdb;
$table = self::get_table_name();
return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $table, (int) $quote_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
}
/**
* Get a quote by number.
*
* @param string $quote_number Number.
* @return object|null
*/
public static function get_quote_by_number( $quote_number ) {
global $wpdb;
$table = self::get_table_name();
return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE quote_number = %s', $table, $quote_number ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
}
/**
* Items of a quote.
*
* @param int $quote_id Quote ID.
* @return array
*/
public static function get_quote_items( $quote_id ) {
global $wpdb;
$table = self::get_items_table_name();
return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE quote_id = %d ORDER BY id ASC', $table, (int) $quote_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
}
/**
* Quotes of a user (paged).
*
* @param int $user_id User.
* @param int $limit Limit.
* @param int $offset Offset.
* @return array
*/
public static function get_user_quotes( $user_id, $limit = 20, $offset = 0 ) {
global $wpdb;
$table = self::get_table_name();
return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
$wpdb->prepare(
'SELECT * FROM %i WHERE user_id = %d ORDER BY created_at DESC LIMIT %d OFFSET %d',
$table,
(int) $user_id,
(int) $limit,
(int) $offset
)
);
}
/**
* Count quotes of a user.
*
* @param int $user_id User.
* @return int
*/
public static function count_user_quotes( $user_id ) {
global $wpdb;
$table = self::get_table_name();
return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE user_id = %d', $table, (int) $user_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
}
/**
* All quotes (admin list).
*
* @param array $args Filters.
* @return array
*/
public static function get_all_quotes( $args = array() ) {
global $wpdb;
$table = self::get_table_name();
$args = wp_parse_args(
$args,
array(
'status' => '',
'search' => '',
'orderby' => 'created_at',
'order' => 'DESC',
'limit' => 20,
'offset' => 0,
)
);
$status = (string) $args['status'];
$search = '' !== (string) $args['search'] ? '%' . $wpdb->esc_like( $args['search'] ) . '%' : '';
$allowed_orderby = array( 'id', 'quote_number', 'total_ttc', 'status', 'created_at' );
$orderby = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'created_at';
$asc = 'ASC' === strtoupper( $args['order'] );
// Static SQL: optional filters are expressed as "( %s = '' OR column = %s )", so the query never changes shape.
if ( $asc ) {
$rows = $wpdb->get_results( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
"SELECT * FROM %i WHERE ( %s = '' OR status = %s ) AND ( %s = '' OR quote_number LIKE %s ) ORDER BY %i ASC LIMIT %d OFFSET %d",
$table, $status, $status, $search, $search, $orderby, (int) $args['limit'], (int) $args['offset']
) );
} else {
$rows = $wpdb->get_results( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
"SELECT * FROM %i WHERE ( %s = '' OR status = %s ) AND ( %s = '' OR quote_number LIKE %s ) ORDER BY %i DESC LIMIT %d OFFSET %d",
$table, $status, $status, $search, $search, $orderby, (int) $args['limit'], (int) $args['offset']
) );
}
return $rows;
}
/**
* Count all quotes (optionally by status).
*
* @param string $status Status.
* @return int
*/
public static function count_all_quotes( $status = '', $search = '' ) {
global $wpdb;
$table = self::get_table_name();
$status = (string) $status;
$search = '' !== (string) $search ? '%' . $wpdb->esc_like( $search ) . '%' : '';
return (int) $wpdb->get_var( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
"SELECT COUNT(*) FROM %i WHERE ( %s = '' OR status = %s ) AND ( %s = '' OR quote_number LIKE %s )",
$table, $status, $status, $search, $search
) );
}
/**
* Counts per status.
*
* @return array
*/
public static function get_status_counts() {
global $wpdb;
$table = self::get_table_name();
$results = $wpdb->get_results( $wpdb->prepare( 'SELECT status, COUNT(*) AS cnt FROM %i GROUP BY status', $table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
$counts = array(
'total' => 0,
'pending' => 0,
'quoted' => 0,
'accepted' => 0,
'rejected' => 0,
'expired' => 0,
'converted' => 0,
);
foreach ( (array) $results as $row ) {
if ( isset( $counts[ $row->status ] ) ) {
$counts[ $row->status ] = (int) $row->cnt;
}
$counts['total'] += (int) $row->cnt;
}
return $counts;
}
/**
* Quoted/accepted quotes whose expiry date has passed.
*
* @return array
*/
public static function get_expired_candidates() {
global $wpdb;
$table = self::get_table_name();
return (array) $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
$wpdb->prepare(
"SELECT id, status FROM %i WHERE status IN ('pending','quoted','accepted') AND expires_at IS NOT NULL AND expires_at < %s LIMIT 200",
$table,
current_time( 'mysql' )
)
);
}
/**
* Update a quote.
*
* @param int $quote_id Quote ID.
* @param array $data Columns.
* @return int|false
*/
public static function update_quote( $quote_id, $data ) {
global $wpdb;
return $wpdb->update( self::get_table_name(), $data, array( 'id' => (int) $quote_id ), self::formats_for( $data, self::quote_formats() ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
}
/**
* Delete a quote, its items and its files.
*
* @param int $quote_id Quote ID.
* @return int|false
*/
public static function delete_quote( $quote_id ) {
global $wpdb;
$quote = self::get_quote( $quote_id );
if ( $quote ) {
/**
* Fires before a quote and its files are deleted.
*
* @param object $quote Quote row.
*/
do_action( 'devisqr_before_delete_quote', $quote );
self::delete_storage_file( $quote->pdf_path, 'pdf' );
self::delete_storage_file( $quote->attachment_path, 'attachments' );
}
$wpdb->delete( self::get_items_table_name(), array( 'quote_id' => (int) $quote_id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
return $wpdb->delete( self::get_table_name(), array( 'id' => (int) $quote_id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
}
/**
* Update one item.
*
* @param int $item_id Item ID.
* @param array $data Columns.
* @return int|false
*/
public static function update_quote_item( $item_id, $data ) {
global $wpdb;
return $wpdb->update( self::get_items_table_name(), $data, array( 'id' => (int) $item_id ), self::formats_for( $data, self::item_formats() ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
}
/**
* Recompute every line from its original unit price (excl. tax):
* line excl. = unit excl. × qty × (1 − line discount) × (1 − global discount) [no intermediate rounding]
* line tax = line excl. × real product tax rate
* Quote totals are the sum of the lines, rounded ONCE to 2 decimals. The global
* discount is thus folded into the lines: cart, PDF, ERP mirror and My Account agree.
*
* @param int $quote_id Quote ID.
* @param float|null $global_discount New global discount or null to keep.
*/
public static function recompute_items( $quote_id, $global_discount = null ) {
$quote = self::get_quote( $quote_id );
if ( ! $quote ) {
return;
}
if ( null === $global_discount ) {
$global_discount = isset( $quote->discount_percent ) ? (float) $quote->discount_percent : 0;
}
$global_discount = max( 0, min( 100, (float) $global_discount ) );
$gf = 1 - $global_discount / 100;
foreach ( self::get_quote_items( $quote_id ) as $item ) {
$disc = isset( $item->discount_percent ) ? (float) $item->discount_percent : 0;
$qty = (float) $item->quantity;
$rate = (float) $item->tax_rate;
// Re-read the real rate from the product's tax class.
$pid = (int) ( $item->variation_id ? $item->variation_id : $item->product_id );
$product = $pid ? wc_get_product( $pid ) : null;
if ( $product && class_exists( 'WC_Tax' ) ) {
$real = 0.0;
$customer = $quote->user_id ? new WC_Customer( (int) $quote->user_id ) : null;
foreach ( (array) WC_Tax::get_rates( $product->get_tax_class(), $customer ) as $r ) {
$real += (float) $r['rate'];
}
if ( $real > 0 ) {
$rate = $real;
}
}
$unit_ht = (float) $item->unit_price_ht;
$line_ht = $unit_ht * $qty * ( 1 - $disc / 100 ) * $gf;
$line_ttc = $line_ht * ( 1 + $rate / 100 );
self::update_quote_item(
(int) $item->id,
array(
'tax_rate' => round( $rate, 3 ),
'unit_price_ttc' => round( $unit_ht * ( 1 + $rate / 100 ), 4 ),
'line_total_ht' => round( $line_ht, 4 ),
'line_total_ttc' => round( $line_ttc, 4 ),
)
);
}
self::update_quote( $quote_id, array( 'discount_percent' => $global_discount ) );
}
/**
* Recalculate totals from lines (single rounding).
*
* @param int $quote_id Quote ID.
* @return array
*/
public static function recalculate_quote_totals( $quote_id ) {
$total_ht = 0;
$total_tax = 0;
foreach ( self::get_quote_items( $quote_id ) as $item ) {
$line_ht = (float) $item->line_total_ht;
$total_ht += $line_ht;
$total_tax += $line_ht * (float) $item->tax_rate / 100;
}
$totals = array(
'total_ht' => round( $total_ht, 2 ),
'total_tax' => round( $total_tax, 2 ),
'total_ttc' => round( $total_ht + $total_tax, 2 ),
);
self::update_quote( $quote_id, $totals );
return $totals;
}
}