我试图在MySQL中创建一个报告(从Wordpress/Woocommerce安装的表中,但是这个商店的纳税方式不同于Woocommerce的做事方式,所以我不能使用Woocommerce报告。
计算需要从总数中计算出20%的税率(英国增值税)-但仅当客户位于特定位置时。因此,如果客户在"世界"中,税率为0%。如果客户在英国,税率是20%。但总收费保持不变(所以出口的利润比本地销售的利润多!)
开头:
SELECT TRIM(LEADING 'Order –' FROM post_title) AS Date,
m1.meta_value AS _order_number,
m2.meta_value AS _Location,
m3.meta_value AS _order_total
FROM wp_posts
LEFT JOIN wp_postmeta AS m1 ON m1.post_id = wp_posts.ID AND m1.meta_key = '_order_number'
LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_shipping_method_title'
LEFT JOIN wp_postmeta AS m3 ON m3.post_id = wp_posts.ID AND m3.meta_key = '_order_total'
WHERE wp_posts.post_type = 'shop_order'
这给了我一个这样的表:
Date | Order no | Location | Order Total
May 2013 | 123 |World |1124.00
Jan 2013 | 124 |UK |163.00
到目前为止一切顺利。所以我试着加一些sum来算出增值税的金额。
SELECT TRIM(LEADING 'Order –' FROM post_title) AS Date,
m1.meta_value AS _order_number,
m2.meta_value AS _Location,
m3.meta_value AS _order_total,
SUM(m3.meta_value*20/120) AS _VAT,
SUM(m3.meta_value*100/120) AS _NET
FROM wp_posts
LEFT JOIN wp_postmeta AS m1 ON m1.post_id = wp_posts.ID AND m1.meta_key = '_order_number'
LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_shipping_method_title'
LEFT JOIN wp_postmeta AS m3 ON m3.post_id = wp_posts.ID AND m3.meta_key = '_order_total'
WHERE wp_posts.post_type = 'shop_order'
我想这会给我:
Date | Order no | Location | Order Total | VAT | NET
May 2013 | 123| World | 1124.00 | 187.33 | 1311.33
Jan 2013 | 124 | UK | 163.00 | 27.17 | 190.17
然后我可以继续尝试计算如何根据位置改变速率。不幸的是,它似乎求和一切,而不是给我一行一行的结果。
1)我搞砸了什么,它把所有的东西加在一起?
2)当_shipping_method_title=UK时,如何最好地应用增值税计算?
3)显然,这只在税率改变之前有效。我最好的办法是在税率下一次修订时,按日期限制这份报告,并为适用新税率的未来订单制作一份新报告吗?还是有更聪明的方法?
我明白向客户收取固定费率并支付可变税是一种有点奇怪的方法,但我不能改变这一点,我只是负责提供报告。: - (
编辑感谢Gordon,我已经整理了SUM问题,现在我的查询看起来像这样,并产生了我期望的表布局,如上面所示:
SELECT TRIM(LEADING 'Order –' FROM post_title) AS Date,
m1.meta_value AS _order_number,
m2.meta_value AS _Location,
m3.meta_value AS _order_total,
SUM(m3.meta_value*20/120) AS _VAT,
SUM(m3.meta_value*100/120) AS _NET
FROM wp_posts
LEFT JOIN wp_postmeta AS m1 ON m1.post_id = wp_posts.ID AND m1.meta_key = '_order_number'
LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_shipping_method_title'
LEFT JOIN wp_postmeta AS m3 ON m3.post_id = wp_posts.ID AND m3.meta_key = '_order_total'
WHERE wp_posts.post_type = 'shop_order'
GROUP BY wp_posts.ID
但我仍然不确定如何根据位置字段中的值更改乘数。这有可能吗,还是我说错话了?
编辑2
我已经制定了一个部分解决方案-而不是SUM行,我使用CASE给我一个计算值的增值税。
CASE
when m2.meta_value = 'World Delivery' then 0
when m2.meta_value = 'UK Delivery' then (m3.meta_value*20/120)
when m2.meta_value = 'European Delivery' then (m3.meta_value*20/120)
END AS _VAT
不幸的是,我不能只是重复_NET值(price - VAT)的CASE语句,因为它与VAT是相同的CASE,所以会产生错误。
我想做一些像
SUM(_order_total-_VAT) AS _NET
但是*_order_total*和*_VAT*不被识别为可以计算的列。
最后编辑再次感谢Gordon,我的最后一个问题是这样的,我只是添加它,以防其他人可能会发现它有帮助。这个查询与Wordpress Exports和Reports Wordpress插件一起工作,因此可以快速集成到Wordpress管理中并导出到Excel,这很漂亮。
SELECT TRIM(LEADING 'Order –' FROM post_title) AS Date,
m1.meta_value AS _order_number,
m2.meta_value AS _Customer_Location,
m3.meta_value AS _order_total,
ROUND(SUM(case when m2.meta_value = 'World Delivery' then 0 else m3.meta_value*20/120 end),2) AS _VAT,
ROUND(SUM(case when m2.meta_value = 'World Delivery' then m3.meta_value else m3.meta_value*100/120 end),2) AS _NET
FROM wp_posts
LEFT JOIN wp_postmeta AS m1 ON m1.post_id = wp_posts.ID AND m1.meta_key = '_order_number'
LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_shipping_method_title'
LEFT JOIN wp_postmeta AS m3 ON m3.post_id = wp_posts.ID AND m3.meta_key = '_order_total'
WHERE wp_posts.post_type = 'shop_order' AND wp_comments.comment_post_id = wp_posts.ID AND wp_comments.comment_content="Order status changed from processing to completed."
GROUP BY wp_posts.ID
您只获得一行的原因是因为sum()
函数使其成为聚合查询。但是没有group by
。我认为按postid分组修复了这个问题,以及对select
的一些其他调整:
SELECT TRIM(LEADING 'Order –' FROM post_title) AS Date,
max(m1.meta_value) AS _order_number,
max(m2.meta_value) AS _Customer_Location,
max(m3.meta_value) AS _order_total,
SUM(m3.meta_value*20/120) AS _VAT,
SUM(m3.meta_value*100/120) AS _NET
FROM wp_posts
LEFT JOIN wp_postmeta AS m1 ON m1.post_id = wp_posts.ID AND m1.meta_key = '_order_number'
LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_shipping_method_title'
LEFT JOIN wp_postmeta AS m3 ON m3.post_id = wp_posts.ID AND m3.meta_key = '_order_total'
WHERE wp_posts.post_type = 'shop_order'
这种类型的数据很难习惯。单个记录的所有数据都被拆分为多个行。
要根据位置改变乘数,使用where
子句。下面是一个例子:
SUM(case when location = 'UK' then 0 else m3.meta_value*20/120 end) AS _VAT,
SUM(case when location = 'UK' then m3.meta_value else m3.meta_value*100/120 end) AS _NET