Important Tip #1: Make the Group name as generic as Possible so it can be used across as many products as possible:
Tip#2: If the same options are across multiple products be sure to name the group and options identically, (this helps with later reporting)
After PHP 7 upgrade
Warning: A non-numeric value encountered in /home/mecha4ma/public_html/wp-content/plugins/woocommerce-shipping-flat-rate-boxes/includes/class-wc-shipping-flat-rate-boxes.php on line 344
Line 344:
// add fee
$cost += $this->get_fee( $this->fee, $package->value );
Change to:
// add fee$cost += (float)$this->get_fee( $this->fee, $package->value );
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
join wp1_woocommerce_order_items oi on oi.order_item_id = oim.order_item_id
set meta_value = ‘0.00’
where oi.order_id = 3387
and oi.order_item_type = ‘shipping’
and meta_key = ‘cost’;
update wp1_woocommerce_order_itemmeta oim
join wp1_woocommerce_order_items oi on oi.order_item_id = oim.order_item_id
set meta_value = ‘ a:1:{i:1;s:6:”0.00″;}’
where oi.order_id = 3387
and oi.order_item_type = ‘shipping’
and meta_key = ‘taxes’;
xx
Potential dashboard / tabular reports for Woocommerce product to sales analysis
Top view = ‘Sales by product’
To consider: version of ‘Sales by Product’ but is ‘Sales by Length’ for products sold per custom lengths
Top view = ‘Sales by category‘:
View for creating a monthly ‘snapshot’ into a table
Note, used https://codebeautify.org/sqlformatter thank you very much
alter VIEW `vProductSales` AS
-- 20181110 LSR
-- Initial version
-- 20181111 Added sku, stock (on hand)
select
now() AS `snapped`,
`pr`.`ID` AS `ID`,
pm_sku,
`oim`.`order_item_type` AS `order_item_type`,
coalesce(
`pr`.`post_title`, `oim`.`order_item_name`
) AS `coalesce(pr.post_title, order_item_name)`,
pm_stock,
sum(
`oim`.`oim->line_item->_line_total`
) AS `net_forever`,
round(
sum(
coalesce(
(
(
`oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
) / 1000
),
`oim`.`oim->line_item->_qty`
)
),
2
) AS `qty_forever`,
sum(
(
case when (`om`.`monthdiff` = 0) then `oim`.`oim->line_item->_line_total` else 0 end
)
) AS `net_current_mth`,
sum(
(
case when (`om`.`monthdiff` = 1) then `oim`.`oim->line_item->_line_total` else 0 end
)
) AS `net_prev_m1`,
sum(
(
case when (`om`.`monthdiff` = 2) then `oim`.`oim->line_item->_line_total` else 0 end
)
) AS `net_prev_m2`,
sum(
(
case when (`om`.`monthdiff` = 3) then `oim`.`oim->line_item->_line_total` else 0 end
)
) AS `net_prev_m3`,
sum(
(
case when (`om`.`monthdiff` = 4) then `oim`.`oim->line_item->_line_total` else 0 end
)
) AS `net_prev_m4`,
sum(
(
case when (`om`.`monthdiff` = 5) then `oim`.`oim->line_item->_line_total` else 0 end
)
) AS `net_prev_m5`,
sum(
(
case when (`om`.`monthdiff` = 6) then `oim`.`oim->line_item->_line_total` else 0 end
)
) AS `net_prev_m6`,
sum(
(
case when (`om`.`monthdiff` = 7) then `oim`.`oim->line_item->_line_total` else 0 end
)
) AS `net_prev_m7`,
sum(
(
case when (`om`.`monthdiff` = 8) then `oim`.`oim->line_item->_line_total` else 0 end
)
) AS `net_prev_m8`,
sum(
(
case when (`om`.`monthdiff` = 9) then `oim`.`oim->line_item->_line_total` else 0 end
)
) AS `net_prev_m9`,
sum(
(
case when (`om`.`monthdiff` = 10) then `oim`.`oim->line_item->_line_total` else 0 end
)
) AS `net_prev_m10`,
sum(
(
case when (`om`.`monthdiff` = 11) then `oim`.`oim->line_item->_line_total` else 0 end
)
) AS `net_prev_m11`,
sum(
(
case when (`om`.`monthdiff` = 12) then `oim`.`oim->line_item->_line_total` else 0 end
)
) AS `net_prev_m12`,
sum(
(
case when (
`om`.`monthdiff` between 1
and 12
) then `oim`.`oim->line_item->_line_total` else 0 end
)
) AS `net_prev_Y1`,
sum(
(
case when (
`om`.`monthdiff` between 13
and 24
) then `oim`.`oim->line_item->_line_total` else 0 end
)
) AS `net_prev_Y2`,
sum(
(
case when (`om`.`monthdiff` > 24) then `oim`.`oim->line_item->_line_total` else 0 end
)
) AS `net_prev_Y3+`,
round(
sum(
(
case when (`om`.`monthdiff` = 0) then coalesce(
(
(
`oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
) / 1000
),
`oim`.`oim->line_item->_qty`
) else 0 end
)
),
2
) AS `qty_mnow`,
round(
sum(
(
case when (`om`.`monthdiff` = 1) then coalesce(
(
(
`oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
) / 1000
),
`oim`.`oim->line_item->_qty`
) else 0 end
)
),
2
) AS `qty_prev_m1`,
round(
sum(
(
case when (`om`.`monthdiff` = 2) then coalesce(
(
(
`oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
) / 1000
),
`oim`.`oim->line_item->_qty`
) else 0 end
)
),
2
) AS `qty_prev_m2`,
round(
sum(
(
case when (`om`.`monthdiff` = 3) then coalesce(
(
(
`oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
) / 1000
),
`oim`.`oim->line_item->_qty`
) else 0 end
)
),
2
) AS `qty_prev_m3`,
round(
sum(
(
case when (`om`.`monthdiff` = 4) then coalesce(
(
(
`oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
) / 1000
),
`oim`.`oim->line_item->_qty`
) else 0 end
)
),
2
) AS `qty_prev_m4`,
round(
sum(
(
case when (`om`.`monthdiff` = 5) then coalesce(
(
(
`oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
) / 1000
),
`oim`.`oim->line_item->_qty`
) else 0 end
)
),
2
) AS `qty_prev_m5`,
round(
sum(
(
case when (`om`.`monthdiff` = 6) then coalesce(
(
(
`oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
) / 1000
),
`oim`.`oim->line_item->_qty`
) else 0 end
)
),
2
) AS `qty_prev_m6`,
round(
sum(
(
case when (`om`.`monthdiff` = 7) then coalesce(
(
(
`oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
) / 1000
),
`oim`.`oim->line_item->_qty`
) else 0 end
)
),
2
) AS `qty_prev_m7`,
round(
sum(
(
case when (`om`.`monthdiff` = 8) then coalesce(
(
(
`oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
) / 1000
),
`oim`.`oim->line_item->_qty`
) else 0 end
)
),
2
) AS `qty_prev_m8`,
round(
sum(
(
case when (`om`.`monthdiff` = 9) then coalesce(
(
(
`oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
) / 1000
),
`oim`.`oim->line_item->_qty`
) else 0 end
)
),
2
) AS `qty_prev_m9`,
round(
sum(
(
case when (`om`.`monthdiff` = 10) then coalesce(
(
(
`oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
) / 1000
),
`oim`.`oim->line_item->_qty`
) else 0 end
)
),
2
) AS `qty_prev_m10`,
round(
sum(
(
case when (`om`.`monthdiff` = 11) then coalesce(
(
(
`oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
) / 1000
),
`oim`.`oim->line_item->_qty`
) else 0 end
)
),
2
) AS `qty_prev_m11`,
round(
sum(
(
case when (`om`.`monthdiff` = 12) then coalesce(
(
(
`oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
) / 1000
),
`oim`.`oim->line_item->_qty`
) else 0 end
)
),
2
) AS `qty_prev_m12`,
round(
sum(
(
case when (
`om`.`monthdiff` between 1
and 12
) then coalesce(
(
(
`oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
) / 1000
),
`oim`.`oim->line_item->_qty`
) else 0 end
)
),
2
) AS `qty_prev_Y1`,
round(
sum(
(
case when (
`om`.`monthdiff` between 13
and 24
) then coalesce(
(
(
`oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
) / 1000
),
`oim`.`oim->line_item->_qty`
) else 0 end
)
),
2
) AS `qty_prev_Y2`,
round(
sum(
(
case when (`om`.`monthdiff` > 24) then coalesce(
(
(
`oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
) / 1000
),
`oim`.`oim->line_item->_qty`
) else 0 end
)
),
2
) AS `qty_prev_Y3+`
from
(
(
`vProductMetaData` `pr`
left join `vOrderItemMetaDataV3` `oim` on(
(
`pr`.`ID` = `oim`.`oim->line_item->_product_id`
)
)
)
left join `vOrderMetaData` `om` on(
(`om`.`ID` = `oim`.`order_id`)
)
)
where
(
(1 = 1)
and (
`oim`.`order_item_type` = 'line_item'
)
and (
`om`.`post_status` like '%complete%'
)
)
group by
`oim`.`oim->line_item->_product_id`,
coalesce(
`pr`.`post_title`, `oim`.`order_item_name`
);
xx
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 trx.object_id = ID
and ttx.taxonomy = 'product_cat') as cats
from wp1_posts po
JOIN wp1_term_relationships tr ON po.ID = tr.object_id
join wp1_term_taxonomy tt on tr.term_taxonomy_id = tt.term_taxonomy_id
where po.post_type = 'product'
aND tt.taxonomy = 'product_cat'
group by ID;
xx
alter view vTaxonomyPaths
as
select tt_id
,concat(
case when tt3_id is not null then concat(te3_name, ' -> ') else '' end
,case when tt2_id is not null then concat(te2_name, ' -> ') else '' end
,case when tt1_id is not null then concat(te1_name, ' -> ') else '' end
,te_name) as cats
,concat(
case when tt3_id is not null then concat(te3_slug, '/') else '' end
,case when tt2_id is not null then concat(te2_slug, '/') else '' end
,case when tt1_id is not null then concat(te1_slug, '/') else '' end
,te_slug) as path
from vTaxonomyChainedUp
where tt_taxonomy = 'product_cat';
xx
create view vTaxonomyChainedUp
as
-- 20180808 LSR
-- product categories
-- daisychained up +3 levels via parents
select tt0.term_taxonomy_id as tt_id
,tt0.term_id as te_id
,tt0.taxonomy as tt_taxonomy
,tt0.description as tt_desc
,te0.name as te_name
,te0.slug as te_slug
--
,tt1.term_taxonomy_id as tt1_id
,tt1.term_id as te1_id
,tt1.taxonomy as tt1_taxonomy
,tt1.description as tt1_desc
,te1.name as te1_name
,te1.slug as te1_slug
--
,tt2.term_taxonomy_id as tt2_id
,tt2.term_id as te2_id
,tt2.taxonomy as tt2_taxonomy
,tt2.description as tt2_desc
,te2.name as te2_name
,te2.slug as te2_slug
--
,tt3.term_taxonomy_id as tt3_id
,tt3.term_id as te3_id
,tt3.taxonomy as tt3_taxonomy
,tt3.description as tt3_desc
,te3.name as te3_name
,te3.slug as te3_slug
--
from wp1_term_taxonomy tt0
JOIN wp1_terms te0 ON tt0.term_id = te0.term_id
--
left join wp1_term_taxonomy tt1 on tt1.term_taxonomy_id = tt0.parent
left JOIN wp1_terms te1 ON tt1.term_id = te1.term_id
--
left join wp1_term_taxonomy tt2 on tt2.term_taxonomy_id = tt1.parent
left JOIN wp1_terms te2 ON tt2.term_id = te2.term_id
--
left join wp1_term_taxonomy tt3 on tt3.term_taxonomy_id = tt2.parent
left JOIN wp1_terms te3 ON tt3.term_id = te3.term_id;
xx
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 / cancelled
-- Removed incomp_om_gross
select max(LAST_DAY(concat(year(post_date), '-', month(post_date), '-','1'))) as month
,vOIMS.trademeflag
-- counts
,sum(case when post_status like '%cancel%'
or post_status like '%refund%' then 1 else 0 end ) as fail
,sum(case when post_status like '%completed' then 1 else 0 end) as complete
,sum(case when post_status like '%hold%' then 1 else 0 end) as hold
,sum(case when post_status like '%process%' then 1 else 0 end) as processing
,sum(case when post_status not like '%cancel%'
and post_status not like '%refund%'
and post_status not like '%completed'
and post_status not like '%hold%'
and post_status not like '%process%' then 1 else 0 end) as UNKNOWN
-- $ from orderitemmeta
,sum(case when post_status like '%completed' then
`sum_oim->line_item->_line_total` else 0 end) as 'comp_oimnet'
,sum(case when post_status like '%completed' then
`sum_oim->shipping->cost` else 0 end) as 'comp_ship'
,sum(case when post_status like '%completed' then
`sum_oim->fee->_line_total` else 0 end) as 'comp_fee'
,sum(case when post_status like '%completed' then
`sum_oim->line_item->_line_tax` else 0 end) as 'comp_tax'
,sum(case when post_status like '%completed' then
`sum_oim->tax->shipping_tax_amount` else 0 end) as 'comp_shiptax'
,sum(case when post_status like '%completed' then
`sum_oim->fee->_line_tax` else 0 end) as 'comp_feetax'
-- $ from order meta & tax from orderitemmeta
,sum(case when post_status like '%completed' then
coalesce(order_total,0) else 0 end) as completed_om_gross
,sum(case when post_status like '%completed' then
coalesce(order_total,0)
- (coalesce(order_tax,0) + coalesce(order_shipping_tax,0)) else 0 end) as completed_om_netofgst
,sum(case when post_status not like '%completed'
and post_status not like '%cancelled'
and post_status not like '%refund%' then
`sum_oim->line_item->_line_total` else 0 end) as 'incomp_oimnet'
,sum(case when post_status like '%cancel%'
or post_status like '%refund%' then
`sum_oim->line_item->_line_total` else 0 end) as 'cancel_oimnet'
from vOrderMetaData om
join vOrderItemMetaSUM vOIMS on vOIMS.order_id = om.ID
group by concat(year(post_date), '-', lpad(month(post_date),2,'0'))
,vOIMS.trademeflag;
xx
xx
alter view vOrdersList as
-- 20180807 LSR
-- Added posthaste edit A link
-- 20180802 1019
select concat('<a href="invoice.php?order=',ID,'©invoice=N" target="_blank">OL', ID, '</a>') as NewInv
,concat('<a href="invoiceold.php?order=',ID,'" target="_blank">OL', ID, '</a>') as OldInv
,post_date
,order_total
,payment_method_title as pay_method
,post_status
,concat('<a href="mailto:'
,coalesce(billing_email, 'xxx@xxx.xxx')
,'?Subject=MMMMM FFFF MMMM Order #' -- Company name here
,ID
,'&body=Thank%20you%20very%20much%20for%20your%20order%20which%20is%20with%20Post%20Haste.'
,'%20%20An%20invoice%20is%20included%20in%20the%20package%0D%0A%0D%0A'
,coalesce(ph_httpurlencoded, 'xxxxx')
,'%0D%0A%0D%0A---%0D%0A%0D%0A'
,'We%20appreciate%20your%20business.'
,'%20If%20you%20like%20our%20products%20%26%20prices%20please%2C%20spread%20the%20word%0D%0A%0D%0A---'
,'%0D%0A%0D%0ARegards%20%26%20thanks%20again%20for%20shopping%20with%20us%0D%0A%0D%0A'
,'LLLLLLl' -- Sender first name here
,'%20'
,'RRRR' -- Sender last name here
,'%0D%0A%0D%0A'
,'MMMMM%20FFF%20MMMMM%20LLLLL' -- URL encoded company name here
,'%0D%0A'
,'www.xxxxx.co.nz' -- URL encoded www...nz domain name here
,'" target="_top">Send Mail</a>') as On_Ship
,ph_href
,concat('<a href="posthasteedit.php?order=',ID, '" target="_blank">', ID, '</a>') as ph_edit
,concat(billing_first_name, ' ', billing_last_name) as who
-- $$ summaries from order items meta
,soim.`sum_oim->line_item->_line_total` as oim_Net
,soim.`sum_oim->shipping->cost` as oim_Ship
,soim.`sum_oim->fee->_line_total` as oim_PPal
,soim.`sum_oim->line_item->_line_tax`
+ soim.`sum_oim->tax->shipping_tax_amount`
+ soim.`sum_oim->fee->_line_tax` as oim_GST
,soim.`sum_oim->line_item->_line_total`
+ soim.`sum_oim->shipping->cost`
+ soim.`sum_oim->fee->_line_total`
+ `sum_oim->line_item->_line_tax`
+ soim.`sum_oim->tax->shipping_tax_amount`
+ soim.`sum_oim->fee->_line_tax` as oim_Total
-- and finally a sanity check to help find some of those
-- annoying GST rounding descrepancies
,(soim.`sum_oim->line_item->_line_total`
+ soim.`sum_oim->shipping->cost`
+ soim.`sum_oim->fee->_line_total`
+ `sum_oim->line_item->_line_tax`
+ soim.`sum_oim->tax->shipping_tax_amount`
+ soim.`sum_oim->fee->_line_tax`) - cast(order_total as DECIMAL(12,4)) as oimT_less_omT
from vOrderMetaData omd
join vOrderItemMetaSUM soim on soim.order_id = omd.ID
order by ID desc;
xx
xx
Todo: change from br to p tags
alter view vOrderAddresses
as
-- 20180822 LSR
-- Added Post_excerpt first 20 chars(Customer comment added at checkout)
-- 20180807 LSR
-- Added maint <a> link
-- 20180805 LSR
-- Original
select ID
,concat('<a href="addressedit.php?order=',ID,'" target="_blank">OL', ID, '</a>') as edit_shipping_href
,case when concat(
coalesce(billing_first_name, '')
,coalesce(billing_last_name, '')
,coalesce(billing_company, '')
,coalesce(billing_address_1, '')
,coalesce(billing_address_2, '')
,coalesce(billing_city, '')
,coalesce(billing_postcode, '')
,coalesce(billing_state, '')
,coalesce(billing_country), '')
<> concat(
coalesce(shipping_first_name, '')
,coalesce(shipping_last_name, '')
,coalesce(shipping_company, '')
,coalesce(shipping_address_1, '')
,coalesce(shipping_address_2, '')
,coalesce(shipping_city, '')
,coalesce(shipping_postcode, '')
,coalesce(shipping_state, '')
,coalesce(shipping_country, '')) then 1 else 0 end as hasshiptoaddress
,concat(
case when coalesce(billing_first_name, '') <> '' then concat(billing_first_name, ' ') else '' end
,case when coalesce(billing_last_name, '') <> '' then concat(billing_last_name, '</br>') else '</br>' end
,case when coalesce(billing_company, '') <> '' then concat(billing_company, '</br>') else '' end
,case when coalesce(billing_address_1, '') <> '' then concat(billing_address_1, '</br>') else '' end
,case when coalesce(billing_address_2, '') <> '' then concat(billing_address_2, '</br>') else '' end
,case when coalesce(billing_city, '') <> '' then concat(billing_city, ' ') else '' end
,case when coalesce(shipping_postcode, '') <> '' then concat(shipping_postcode, '</br>') else '</br>' end
-- no state for now
,case when (coalesce(billing_country, '') <> ''
and billing_country not like 'nz'
and billing_country not like '%zealand') then billing_country else '' end) as billingaddresshtml
,concat(
case when coalesce(shipping_first_name, '') <> '' then concat(shipping_first_name, ' ') else '' end
,case when coalesce(shipping_last_name, '') <> '' then concat(shipping_last_name, '</br>') else '</br>' end
,case when coalesce(shipping_company, '') <> '' then concat(shipping_company, '</br>') else '' end
,case when coalesce(shipping_address_1, '') <> '' then concat(shipping_address_1, '</br>') else '' end
,case when coalesce(shipping_address_2, '') <> '' then concat(shipping_address_2, '</br>') else '' end
,case when coalesce(shipping_city, '') <> '' then concat(shipping_city, ' ') else '' end
,case when coalesce(shipping_postcode, '') <> '' then concat(shipping_postcode, '</br>') else '</br>' end
-- no state for now
,case when (coalesce(shipping_country, '') <> ''
and shipping_country not like 'nz'
and shipping_country not like '%zealand') then shipping_country else '' end) as shippingaddresshtml
,concat(left(post_excerpt, 20), case when char_length(post_excerpt) > 20 then '...' else '' end) as post_excerpt20
from vOrderMetaData;
xx
xx
alter view vProductMetaData as /* Author: LSR 20180402 Woocommerce products are stored as Posts Products have multiple attributes. - Each attribute has it's own record in table ..postmeta - so if a product has 5 attributes = at least 5 records in this table for that product - Custom attributes generated by custom product options/plugins also generate additional records in this table This View flatterns those attributes 1 one record per product: - returns 1 record per post_type = 'product' - each attribute is in its own column Assumes integrity in that an attribute as identified by meta_key exists only once per parent Product */ select po.ID, po.post_title ,max(case when meta_key = '_area' then meta_value else null end) as pm_area ,max(case when meta_key = '_backorders' then meta_value else null end) as pm_backorders ,max(case when meta_key = '_crosssell_ids' then meta_value else null end) as pm_crosssell_ids ,max(case when meta_key = '_default_attributes' then meta_value else null end) as pm_default_attributes ,max(case when meta_key = '_downloadable' then meta_value else null end) as pm_downloadable ,max(case when meta_key = '_edit_last' then meta_value else null end) as pm_edit_last ,max(case when meta_key = '_edit_lock' then meta_value else null end) as pm_edit_lock ,max(case when meta_key = '_featured' then meta_value else null end) as pm_featured ,max(case when meta_key = '_height' then meta_value else null end) as pm_height ,max(case when meta_key = '_length' then meta_value else null end) as pm_length ,max(case when meta_key = '_m4m_cost' then meta_value else null end) as pm_m4m_cost ,max(case when meta_key = '_m4m_ok' then meta_value else null end) as pm_m4m_ok ,max(case when meta_key = '_m4m_pnp_cost' then meta_value else null end) as pm_m4m_pnp_cost ,max(case when meta_key = '_m4m_purchase_note' then meta_value else null end) as pm_m4m_purchase_note ,max(case when meta_key = '_m4m_qty_bought' then meta_value else null end) as pm_m4m_qty_bought ,max(case when meta_key = '_m4m_qty_inv' then meta_value else null end) as pm_m4m_qty_inv ,max(case when meta_key = '_m4m_repl_cost' then meta_value else null end) as pm_m4m_repl_cost ,max(case when meta_key = '_m4m_shrinkage' then meta_value else null end) as pm_m4m_shrinkage ,max(case when meta_key = '_m4m_stocktake20180331' then meta_value else null end) as pm_m4m_stocktake20180331 ,max(case when meta_key = '_manage_stock' then meta_value else null end) as pm_manage_stock ,max(case when meta_key = '_max_price_variation_id' then meta_value else null end) as pm_max_price_variation_id ,max(case when meta_key = '_max_regular_price_variation_id' then meta_value else null end) as pm_max_regular_price_variation_id ,max(case when meta_key = '_max_sale_price_variation_id' then meta_value else null end) as pm_max_sale_price_variation_id ,max(case when meta_key = '_max_variation_price' then meta_value else null end) as pm_max_variation_price ,max(case when meta_key = '_max_variation_regular_price' then meta_value else null end) as pm_max_variation_regular_price ,max(case when meta_key = '_max_variation_sale_price' then meta_value else null end) as pm_max_variation_sale_price ,max(case when meta_key = '_min_price_variation_id' then meta_value else null end) as pm_min_price_variation_id ,max(case when meta_key = '_min_regular_price_variation_id' then meta_value else null end) as pm_min_regular_price_variation_id ,max(case when meta_key = '_min_sale_price_variation_id' then meta_value else null end) as pm_min_sale_price_variation_id ,max(case when meta_key = '_min_variation_price' then meta_value else null end) as pm_min_variation_price ,max(case when meta_key = '_min_variation_regular_price' then meta_value else null end) as pm_min_variation_regular_price ,max(case when meta_key = '_min_variation_sale_price' then meta_value else null end) as pm_min_variation_sale_price ,max(case when meta_key = '_price' then meta_value else null end) as pm_price ,max(case when meta_key = '_product_addons' then meta_value else null end) as pm_product_addons ,max(case when meta_key = '_product_addons_exclude_global' then meta_value else null end) as pm_product_addons_exclude_global ,max(case when meta_key = '_product_attributes' then meta_value else null end) as pm_product_attributes ,max(case when meta_key = '_product_image_gallery' then meta_value else null end) as pm_product_image_gallery ,max(case when meta_key = '_product_version' then meta_value else null end) as pm_product_version ,max(case when meta_key = '_psad_featured_order' then meta_value else null end) as pm_psad_featured_order ,max(case when meta_key = '_psad_onsale_order' then meta_value else null end) as pm_psad_onsale_order ,max(case when meta_key = '_purchase_note' then meta_value else null end) as pm_purchase_note ,max(case when meta_key = '_regular_price' then meta_value else null end) as pm_regular_price ,max(case when meta_key = '_sale_price' then meta_value else null end) as pm_sale_price ,max(case when meta_key = '_sale_price_dates_from' then meta_value else null end) as pm_sale_price_dates_from ,max(case when meta_key = '_sale_price_dates_to' then meta_value else null end) as pm_sale_price_dates_to ,max(case when meta_key = '_sku' then meta_value else null end) as pm_sku ,max(case when meta_key = '_sold_individually' then meta_value else null end) as pm_sold_individually ,max(case when meta_key = '_stock' then meta_value else null end) as pm_stock ,max(case when meta_key = '_stock_status' then meta_value else null end) as pm_stock_status ,max(case when meta_key = '_tax_class' then meta_value else null end) as pm_tax_class ,max(case when meta_key = '_tax_status' then meta_value else null end) as pm_tax_status ,max(case when meta_key = '_thumbnail_id' then meta_value else null end) as pm_thumbnail_id ,max(case when meta_key = '_upsell_ids' then meta_value else null end) as pm_upsell_ids ,max(case when meta_key = '_virtual' then meta_value else null end) as pm_virtual ,max(case when meta_key = '_visibility' then meta_value else null end) as pm_visibility ,max(case when meta_key = '_volume' then meta_value else null end) as pm_volume ,max(case when meta_key = '_wc_average_rating' then meta_value else null end) as pm_wc_average_rating ,max(case when meta_key = '_wc_measurement_price_calculator_min_price' then meta_value else null end) as pm_wc_measurement_price_calculator_min_price ,max(case when meta_key = '_wc_price_calculator' then meta_value else null end) as pm_wc_price_calculator ,max(case when meta_key = '_wc_price_calculator_pricing_rules' then meta_value else null end) as pm_wc_price_calculator_pricing_rules ,max(case when meta_key = '_wc_rating_count' then meta_value else null end) as pm_wc_rating_count ,max(case when meta_key = '_weight' then meta_value else null end) as pm_weight ,max(case when meta_key = '_width' then meta_value else null end) as pm_width ,max(case when meta_key = '_woocommerce_add_fees_product' then meta_value else null end) as pm_woocommerce_add_fees_product ,max(case when meta_key = '_wp_old_slug' then meta_value else null end) as pm_wp_old_slug ,max(case when meta_key = 'total_sales' then meta_value else null end) as pmtotal_sales from wp1_posts po left join wp1_postmeta pm on pm.post_id = po.ID where post_type = 'product' group by po.ID
xx
Todo
xx
xx