fluent-cart / database / Migrations / TaxRatesMigrator.php

TaxRatesMigrator.php in FluentCart A New Era of eCommerce – Faster, Lighter, and Simpler 1.6.4, at database/Migrations/TaxRatesMigrator.php

155 lines 6.2 KB

Raw Download Zip

1	<?php
2
3	namespace FluentCart\Database\Migrations;
4
5	use FluentCart\Framework\Database\Schema;
6
7	class TaxRatesMigrator extends Migrator
8	{
9
10	public static string $tableName = 'fct_tax_rates';
11
12	public static function getSqlSchema(): string
13	{
14	$prefix = static::getDbPrefix();
15	$indexPrefix = $prefix . 'fct_txr_';
16
17	// postcode is text to allow multiple postcodes like: 12345, 23456, 34567 or ranges like: 12345-12350
18	return "`id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
19	`class_id` BIGINT UNSIGNED NOT NULL,
20	`country` VARCHAR(45) NULL,
21	`state` VARCHAR(45) NULL,
22	`postcode` TEXT NULL,
23	`city` VARCHAR(45) NULL,
24	`rate` VARCHAR(45) NULL,
25	`name` VARCHAR(45) NULL,
26	`group` VARCHAR(45) NULL,
27	`priority` INT UNSIGNED NULL DEFAULT 1,
28	`is_compound` TINYINT UNSIGNED NULL DEFAULT 0,
29	`for_shipping` DECIMAL(10, 2) NULL DEFAULT NULL,
30	`for_order` TINYINT UNSIGNED NULL DEFAULT 0,
31
32	INDEX `{$indexPrefix}_txr_class_idx` (`class_id` ASC),
33	INDEX `{$indexPrefix}_priority_idx` (`priority` ASC)";
34	}
35
36	public static function migrated()
37	{
38	static::addGroupColumn();
39	static::upgradeShippingOverridePrecision();
40	static::fixPostcodeRangeSeparator();
41	}
42
43	public static function addGroupColumn()
44	{
45	// "ALTER TABLE %i ADD COLUMN `group` VARCHAR(45) NULL AFTER `name`"
46	static::addColumnIfNotExists('group', 'VARCHAR(45) NULL', 'name');
47	}
48
49	public static function upgradeShippingOverridePrecision()
50	{
51	// Before changing the column type, convert the old TINYINT boolean sentinel (1 = "yes,
52	// apply the product rate to shipping") to NULL, which carries the same meaning in the
53	// new DECIMAL schema ("inherit rate from the rate column"). This guard runs only while
54	// the column is still TINYINT — once it is DECIMAL the query is a no-op because no
55	// legitimate UI-entered percentage would equal exactly 1.00 on an upgraded install.
56	// for_shipping = 0 (no shipping tax) and NULL (inherit) are left untouched.
57	$wpdb = Schema::db();
58	$tableName = static::getTableName();
59
60	$colType = '';
61	foreach (Schema::getColumnsWithTypes(static::$tableName) as $col) {
62	if (isset($col['column_name']) && $col['column_name'] === 'for_shipping') {
63	$colType = isset($col['data_type']) ? $col['data_type'] : '';
64	break;
65	}
66	}
67
68	if (strpos($colType, 'tinyint') !== false) {
69	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
70	$wpdb->query($wpdb->prepare(
71	"UPDATE %i SET `for_shipping` = NULL WHERE `for_shipping` = 1",
72	$tableName
73	));
74	}
75
76	// Shipping tax overrides are edited as percentages in the admin UI and must
77	// preserve decimal precision across both new installs and upgrades.
78	static::modifyColumnIfExists('for_shipping', 'DECIMAL(10, 2) NULL DEFAULT NULL');
79	}
80
81	public static function fixPostcodeRangeSeparator()
82	{
83	// The postcode range separator changed from "..." (legacy) and "::" (intermediate) to
84	// "-" in the current version. Rows still using the old separators will not match ranges
85	// at all; this one-time replacement fixes them silently on upgrade.
86	$wpdb = Schema::db();
87	$tableName = static::getTableName();
88
89	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
90	$wpdb->query($wpdb->prepare(
91	"UPDATE %i SET `postcode` = REPLACE(`postcode`, '...', '-') WHERE `postcode` LIKE '%%...%%'",
92	$tableName
93	));
94	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
95	$wpdb->query($wpdb->prepare(
96	"UPDATE %i SET `postcode` = REPLACE(`postcode`, '::', '-') WHERE `postcode` LIKE '%%::%%'",
97	$tableName
98	));
99
100	// Product category tax overrides in fct_meta carry the same postcode value inside
101	// their JSON meta_value — normalize those rows the same way.
102	$metaTable = static::getDbPrefix() . 'fct_meta';
103	$lastId = 0;
104	$batchSize = 200;
105
106	do {
107	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
108	$rows = $wpdb->get_results($wpdb->prepare(
109	"SELECT `id`, `meta_value` FROM %i
110	WHERE `object_type` = 'tax_override'
111	AND `meta_key` = 'product_category_override'
112	AND (`meta_value` LIKE '%%...%%' OR `meta_value` LIKE '%%::%%')
113	AND `id` > %d
114	ORDER BY `id` ASC
115	LIMIT %d",
116	$metaTable,
117	$lastId,
118	$batchSize
119	), ARRAY_A);
120
121	if (!is_array($rows)) {
122	break;
123	}
124
125	foreach ($rows as $row) {
126	$lastId = (int) $row['id'];
127
128	$data = json_decode($row['meta_value'], true);
129	if (!is_array($data) \|\| !isset($data['postcode'])) {
130	continue;
131	}
132
133	$normalized = str_replace(['...', '::'], '-', $data['postcode']);
134	if ($normalized === $data['postcode']) {
135	continue;
136	}
137
138	$data['postcode'] = $normalized;
139	$encoded = wp_json_encode($data);
140	if ($encoded === false) {
141	continue;
142	}
143	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
144	$wpdb->update(
145	$metaTable,
146	['meta_value' => $encoded],
147	['id' => $row['id']],
148	['%s'],
149	['%d']
150	);
151	}
152	} while (count($rows) === $batchSize);
153	}
154	}
155