-
Notifications
You must be signed in to change notification settings - Fork 1
/
payment.sql
43 lines (36 loc) · 1.28 KB
/
payment.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
SELECT
wp_woocommerce_order_items.order_id AS invoice_number,
'Check' AS payment_method,
DATE_FORMAT(p.post_date, "%Y-%m-%d") AS payment_date ,
SUM(wp_woocommerce_order_itemmeta.meta_value) AS payment_amount,
'Imported' AS payment_note
FROM
wp_woocommerce_order_items
JOIN
wp_woocommerce_order_itemmeta
ON wp_woocommerce_order_itemmeta.order_item_id = wp_woocommerce_order_items.order_item_id
JOIN
wp_posts p
ON p.ID = wp_woocommerce_order_items.order_id
JOIN
(
SELECT
wp_woocommerce_order_items.order_id ,
meta_value,
wp_woocommerce_order_itemmeta.order_item_id
FROM
wp_woocommerce_order_items
JOIN
wp_woocommerce_order_itemmeta
ON wp_woocommerce_order_itemmeta.order_item_id = wp_woocommerce_order_items.order_item_id
JOIN
wp_posts p
ON p.ID = wp_woocommerce_order_items.order_id
AND meta_key = '_qty'
) Kwantiti
ON Kwantiti.order_id = wp_woocommerce_order_items.order_id AND Kwantiti.order_item_id = wp_woocommerce_order_itemmeta.order_item_id
WHERE wp_woocommerce_order_itemmeta.meta_key = '_line_total'
-- WHERE wp_woocommerce_order_items.order_id = 1325
GROUP BY 1
ORDER BY 1 DESC
-- AND wp_woocommerce_order_itemmeta.meta_key = '_line_subtotal'