Bolt sizes: select reverse(substring_index(reverse(pm_sku), ‘.’, 1)) from vProductMetaData where (pm_sku like ‘BO.%’ or pm_sku like ‘SCW.%’);
Category: Database
Musings on how to calculate close to actual cost of sales in Woocommerce 2.6.14 using FIFO (If I manage to achieve it per order line then I can aggregate up for reporting totals etc, so initially focus on per order item) I have a custom inwards goods table, for initial thoughts I will ignore credits/returns, […]
SELECT table_schema as Database, table_name AS Table, round(((data_length + index_length) / 1024 / 1024), 2) Size in MB FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC;
select * from vOrderItemMetaDataV3 where order_id = 7509; insert into wp1_woocommerce_order_itemmeta (order_item_id, meta_key, meta_value) values (29897, ‘length’, ‘1000’) ; select * from wp1_woocommerce_order_itemmeta where order_item_id = 29897;
20200514 I have been being to create a lookup table for woocommerce orders states. So I decided to implement it as a view: Before adding in ‘collate utf8_general_ci’ to each record in the view below I was getting an error when consuming the view: Error Code: 1267. Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) create […]
20181220 Product variations (attribute = length only currently) alter view vVariationMetaData as — 20181220 LSR — Product Variations meta data — Note is selected metadata only (not quite all) select po.post_parent, pm.post_id ,max(case when meta_key = ‘attribute_length’ then meta_value else null end) as pvm_attribute_length ,max(case when meta_key = ‘_sku’ then meta_value else null end) as […]
Initial versions 20181117 — Keep track of purchases ‘ Snapshot in case ever lost CREATE TABLE `m4m_wpProducts` (`wp_post_id` INT ,`wp_post_title` TEXT ,`wp_sku` TEXT , KEY `iPostID` (`wp_post_id`) USING BTREE ); insert into m4m_wpProducts (`wp_post_id`, `wp_post_title`, `wp_sku`) select ID, post_title, pm_sku from vProductMetaData; Purchases CREATE TABLE `m4m_purchases` (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,`wp_post_id` INT […]
Paypal orders are sorta locked xx — wp1_postmeta update wp1_postmeta set meta_value = ‘0.00’ where post_id = 3387 and meta_key = ‘_order_shipping’; update wp1_postmeta set meta_value = ‘0.00’ where post_id = 3387 and meta_key = ‘_order_shipping_tax’; update wp1_postmeta set meta_value = ‘10.36’ where post_id = 3387 and meta_key = ‘_order_total’; — wp1_woocommerce_order_itemmeta update wp1_woocommerce_order_itemmeta oim […]
Product categories have parents… Products can be in more than 1 category xx create view vProductCatsSummary as select ID ,count(*) as cat_cnt ,min(tt.term_taxonomy_id) as cat_min ,max(tt.term_taxonomy_id) as cat_max ,( select group_concat( tex.name separator ‘ | ‘ ) from wp1_term_relationships trx JOIN wp1_term_taxonomy ttx ON trx.term_taxonomy_id = ttx.term_taxonomy_id JOIN wp1_terms tex ON ttx.term_id = tex.term_id where […]
xx alter view vOrderSummaryByMonth as — 20190217 LSR — Add refund into cancelled total — Exclude refund from incompleted — 20180829 LSR — Split by Trademe not trademe — 20180803 0907 LSR — Order counts & sums by month — 20181128 1226 LSR — Added cancel_oimnet — Updated incomp_oimnet to be all except completed / […]