Line totals
alter view vOrderItemMetaSUM
as
— 20180829 LSR
— Need a flag to indictae if an order is Trademe or not
— Should be sufficient that if an order line has ‘trademe’ then yes
— 20180817 LSR
— Realised differences between the fields below where:
— For type = line_item: line totals (net of tax):
— oim->line_item->_line_subtotal is the original value on order confirmation by the customer
— oim->line_item->_line_total is the current value (ie includes edits made by the shop owner)
— For type = line_item: line totals (net of tax):
— oim->line_item->_line_subtotal_tax is the original tax value on order confirmation by the customer
— oim->line_item->_line_tax is the current value (ie includes edits made by the shop owner)
— So added in missing columns
— (Re)named these columns to force fail of dependant queries:
— sum_oim->line_item->_line_subtotal to sum_oim->line_item->_line_subtotalx
— sum_oim->line_item->_line_subtotal_tax to sum_oim->line_item->_line_subtotal_taxz
—
— 20180802 LSR
— Summarise per order totals from oim
— – Product total
— – Shipping
— – Paypal charges
— – GST
—
— Used for:
— 1. For invoice shipping line items:
— Because GST on shipping charges from Flat rate Box plugin isn’t
— directly available on the shipping order item+meta
— 2. For reporting across orders
—
— Note: cast to decimal 4dp used as:
— 1. I am seeing 4 dp precison in $ values stored in oim
— 2. Aggregate functions seem to be taking $ values from oim that
— and treating them as ‘float’ so we are getting errors:
— e.g. 26.005 displays 26.0049999999999954525
— round worked on some columns not others, cast seems good enough
— Note: also cast to 2dp for use in invoices
select oi.order_id
— line_subtotal is amount before any order manual edits
,sum(cast(case when oi.order_item_type = ‘line_item’
and oim.meta_key = ‘_line_subtotal’
then meta_value else 0 end as DECIMAL(12,4))) as ‘sum_oim->line_item->_line_subtotalx’
— line_total is amount after any order manual edits – so always use line_total
,sum(cast(case when oi.order_item_type = ‘line_item’
and oim.meta_key = ‘_line_total’
then meta_value else 0 end as DECIMAL(12,4))) as ‘sum_oim->line_item->_line_total’
— line_subtotal_tax is amount before any order manual edits
,sum(cast(case when oi.order_item_type = ‘line_item’
and oim.meta_key = ‘_line_subtotal_tax’
then meta_value else 0 end as DECIMAL(12,4))) as ‘sum_oim->line_item->_line_subtotal_taxz’
— _line_tax is tax amount after any order manual edits – so always use _line_tax
,sum(cast(case when oi.order_item_type = ‘line_item’
and oim.meta_key = ‘_line_tax’
then meta_value else 0 end as DECIMAL(12,4))) as ‘sum_oim->line_item->_line_tax’
,sum(cast(case when oi.order_item_type = ‘tax’
and oim.meta_key = ‘shipping_tax_amount’
then meta_value else 0 end as DECIMAL(12,4))) as ‘sum_oim->tax->shipping_tax_amount’
,sum(cast(case when oi.order_item_type = ‘shipping’
and oim.meta_key = ‘cost’
then meta_value else 0 end as DECIMAL(12,4))) ‘sum_oim->shipping->cost’
,sum(cast(case when oi.order_item_type = ‘fee’
and oim.meta_key = ‘_line_total’
then meta_value else 0 end as DECIMAL(12,4))) ‘sum_oim->fee->_line_total’
,sum(cast(case when oi.order_item_type = ‘fee’
and oim.meta_key = ‘_line_tax’
then meta_value else 0 end as DECIMAL(12,4))) as ‘sum_oim->fee->_line_tax’
,max(case when oi.order_item_name like ‘%trademe%’ then 1 else 0 end) as trademeflag
from wp1_woocommerce_order_items oi
join wp1_woocommerce_order_itemmeta oim
on oim.order_item_id = oi.order_item_id
group by order_id;
Invoice lineitems
alter view vOrderLineItemsForInvoice
as
— 20180904 LSR
— Had missed substituting line_total for line_subtotal in ouput column lineprice
— 20180817 LSR
— Realised differences between the fields below where:
— For type = line_item: line totals (net of tax):
— oim->line_item->_line_subtotal is the original value on order confirmation by the customer
— oim->line_item->_line_total is the current value (ie includes edits made by the shop owner)
— For type = line_item: line totals (net of tax):
— oim->line_item->_line_subtotal_tax is the original tax value on order confirmation by the customer
— oim->line_item->_line_tax is the current value (ie includes edits made by the shop owner)
— So made sure to be using _line_total & _line_tax_total
— 20180808 0702 LSR
— Removed wp1_woocommerce_order_items as all fields are available in oim view
— 20180805 LSR
— Added product options as text ‘label: value’
— 20180804 LSR
— For itemline description we will take product description instead of the
— populated lineitem name, as for grouped products the item name prefixes the
— parent produt group, which is nice but dosn’t work for our product setup
— (As we put full description in child product in case they find it through search)
—
— 20180803 LSR
— All Order lines for use in invoices/packing slips
—
— Will leave GST lineitem in here and excluded in the PHP query
— as is best to see all for trouble shooting when issues arise
select oim.order_id, oim.order_item_id
,pm_sku as sku
,coalesce(pm.post_title, order_item_name) as description
,’Calced pricing columns->’
,`oim->line_item->length`
,coalesce(`oim->line_item->_qty`,1) as qty
,cast(coalesce(`oim->shipping->cost`
,`oim->fee->_line_total`
— Need line_total was line_subtotal
,round(`oim->line_item->_line_total`/`oim->line_item->_qty`,2)
) as DECIMAL(12,2)) as unitprice
,cast(coalesce(`oim->shipping->cost`
,`oim->fee->_line_total`
,`oim->line_item->_line_total`
) as DECIMAL(12,2)) as lineprice
,case when order_item_name not like ‘%GST%’ then
coalesce(`oim->line_item->_line_tax`
,`oim->fee->_line_tax`
,oims.`sum_oim->tax->shipping_tax_amount`
)
else coalesce(`oim->tax->shipping_tax_amount`,0) + coalesce(`oim->tax->tax_amount`, 0)
end as linegst
,’Raw Pricing columns->’
— These are here just so we can eyeball check source pricing columns if any issues
,`oim->fee->_line_subtotal_tax`
,`oim->fee->_line_tax`
,`oim->fee->_line_tax_data`
,`oim->fee->_line_total`
,`oim->line_item->_line_subtotal`
,`oim->line_item->_line_subtotal_tax`
,`oim->line_item->_line_tax`
,`oim->line_item->_line_tax_data`
,`oim->line_item->_line_total`
,`oim->line_item->_qty`
,`oim->line_item->Required Length (mm)`
,`oim->shipping->_line_tax`
,`oim->shipping->_line_tax_data`
,`oim->shipping->cost`
,`oim->shipping->Items`
,`oim->shipping->method_id`
,`oim->shipping->taxes`
,`oim->tax->shipping_tax_amount`
,`oim->tax->tax_amount`
,’Product addons->’
,`oim->line_item->Cutting options`
,`oim->line_item->End threading options M6`
,`oim->line_item->Milling for M6 Cap Screw`
,`oim->line_item->Drill tool hole`
— Product addons if selected formatted text else null
,case when coalesce(`oim->line_item->Cutting options`, ‘None’) <> ‘None’ then
concat(‘<p>Cutting options: ‘, `oim->line_item->Cutting options`,'</p>’) end as cuttingoptionstext
,case when coalesce(`oim->line_item->End threading options M6`, ‘None’) <> ‘None’ then
concat(‘<p>End threading M6: ‘, `oim->line_item->End threading options M6`,'</p>’) end as endthreadingm6
,case when coalesce(`oim->line_item->Milling for M6 Cap Screw`, ‘None’) <> ‘None’ then
concat(‘<p>Milling for M6 Cap Screw: ‘, `oim->line_item->Milling for M6 Cap Screw`,'</p>’) end as millingm6capscrewtext
,case when coalesce(`oim->line_item->Drill tool hole`, ‘None’) <> ‘None’ then
concat(‘<p>Drill tool hole: ‘, `oim->line_item->Drill tool hole`,'</p>’) end as drilltoolholetext
from vOrderItemMetaDataV3 oim
— Get OrderItem record for GST on shipping, **Ugly**
— And we will left join as we might do sales to Oz soonish..
— so possibly no GST on some orders in future
left join vOrderItemMetaSUM oims on oims.order_id = oim.order_id
— left join to products as order items for shipping & paypal fees don’t exist in there
left join vProductMetaData pm on pm.ID = oim.`oim->line_item->_product_id`
order by oim.order_id, oim.order_item_id;
20180801 Woocommerce 2.6.14
Building towards generating invoices directly off the DB
Woocommerce_order_items has only 4 columns with all the other attributes data shotgunned into woocommerce_order_itemmeta
And I say shotgunned because there is no consistency across standard woocommerce let alone once you add some plugins.
So here I am looking at how to get order lines with these attributes:
- quantity
- per unit price
- line total net of tax
- line tax
- line total (perhaps I want this, not sure yet)
Items include:
- standard products
variable products(I don’t have any)- paypal fees (FROM Standard Woocommerce Paypal payment option)
- shipping charges (FROM Woocommerce Flat Rate Box Shipping plugin)
Re Product Addons (from Product Addons plugin) I observe:
- When a product with addon’s is purchased:
- There is a single orderItem record for that product
- The product price + Add On pricing is used to populate the orderItem record
- The Add On options selected are stored in OrderItemMeta
Observations:
| Qty | per unit price | line tax | line total | ||
| standard products | PASS:
oim->line_item->_qty |
PASS:
Not available directly so we derive: round(`oim->line_item->_line_subtotal`/`oim->line_item->_qty`,2) |
PASS:
same value in both: oim->line_item->_line_subtotal_tax 20180817 IMPORTANT: If you edit the line item value, the original line taxremains in _line_subtotal_tax The new line tax value is updated into _line_tax So we need to use: oim->line_item->_line_tax |
PASS:
same value in both: oim->line_item->_line_subtotal 20180817 IMPORTANT: If you edit the line item value, the original line total remains in _line_subtotal The new value is updated into _line_total So we need to use: oim->line_item->_line_total |
|
| paypal fees | PASS:
We set to 1 |
PASS:
oim->fee->_line_total |
PASS:
oim->fee->_line_tax |
Haven’t looked yet | |
| shipping charges | PASS:
We set to 1 |
PASS:
oim->shipping->cost |
FAIL:
Not available directly, is buried as serialised data in: oim->shipping->taxes ALSO: Is on the TAX order line record in: oim->tax->shipping_tax_amount |
Haven’t looked yet | |
| Variable products | NA |
So the only one that is annoying is Tax in Shipping charges
So I created view vOrderItemMetaSUM etc
xx