← All changes | database/Migrations/TaxRatesMigrator.php +112 -2 1.3.21 → 1.6.5 View file →

		@@ -1,8 +1,10 @@
1	1	<?php
2	2
3	3	namespace FluentCart\Database\Migrations;
4	4
	5	+use FluentCart\Framework\Database\Schema;
	6	+
5	7	class TaxRatesMigrator extends Migrator
6	8	{
7	9
8	10	public static string $tableName = 'fct_tax_rates';
		@@ -11,9 +13,9 @@
11	13	{
12	14	$prefix = static::getDbPrefix();
13	15	$indexPrefix = $prefix . 'fct_txr_';
14	16
15		- // postcode is text to allow multiple postcodes like: 12345, 23456, 34567 or ranges like: 12345...12350
	17	+ // postcode is text to allow multiple postcodes like: 12345, 23456, 34567 or ranges like: 12345-12350
16	18	return "`id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
17	19	`class_id` BIGINT UNSIGNED NOT NULL,
18	20	`country` VARCHAR(45) NULL,
19	21	`state` VARCHAR(45) NULL,
		@@ -23,9 +25,9 @@
23	25	`name` VARCHAR(45) NULL,
24	26	`group` VARCHAR(45) NULL,
25	27	`priority` INT UNSIGNED NULL DEFAULT 1,
26	28	`is_compound` TINYINT UNSIGNED NULL DEFAULT 0,
27		- `for_shipping` TINYINT UNSIGNED NULL DEFAULT NULL,
	29	+ `for_shipping` DECIMAL(10, 2) NULL DEFAULT NULL,
28	30	`for_order` TINYINT UNSIGNED NULL DEFAULT 0,
29	31
30	32	INDEX `{$indexPrefix}_txr_class_idx` (`class_id` ASC),
31	33	INDEX `{$indexPrefix}_priority_idx` (`priority` ASC)";
		@@ -33,8 +35,10 @@
33	35
34	36	public static function migrated()
35	37	{
36	38	static::addGroupColumn();
	39	+ static::upgradeShippingOverridePrecision();
	40	+ static::fixPostcodeRangeSeparator();
37	41	}
38	42
39	43	public static function addGroupColumn()
40	44	{
		@@ -39,6 +43,112 @@
39	43	public static function addGroupColumn()
40	44	{
41	45	// "ALTER TABLE %i ADD COLUMN `group` VARCHAR(45) NULL AFTER `name`"
42	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);
43	153	}
44	154	}