SELECT website_id id AS website_id SELECT SUM bd total FROM billing_da

 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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
SELECT
@website_id:=id AS website_id,
(SELECT SUM(bd.total) FROM billing_data_paypal AS bd WHERE bd.website_id <> '0' AND bd.when_it >= '1228082400' AND bd.when_it <= '1228255199' AND bd.order_id <> '0' AND bd.website_id=@website_id AND bd.business NOT LIKE '%biz@gmail.com%' ) AS total_instant_payments,
(
SELECT
SUM(bd.total)
FROM
billing_data_paypal AS bd
LEFT JOIN customers_balance_info AS cbi ON bd.order_id = cbi.order_id
WHERE
bd.website_id <> '0' AND bd.when_it >= '1228082400' AND bd.when_it <= '1228255199' AND bd.order_id <> '0' AND bd.website_id=@website_id AND bd.business NOT LIKE '%biz@gmail.com%'
AND cbi.transaction_type='7' AND bd.order_id='0'
) AS total_balance,
(
SELECT
(SUM(o.total_cost) - SUM(o.total_charge))
FROM
orders AS o
INNER JOIN billing_data_paypal AS bd
ON o.id=bd.order_id
INNER JOIN customers_info AS ci
ON ci.user_id = o.customer_id
INNER JOIN ci_users AS u
ON ci.user_id = u.userid
INNER JOIN websites AS w
ON u.site_id = w.id
INNER JOIN orders_used_coupon AS oc
ON oc.order_id = o.id
INNER JOIN coupons AS co
ON oc.coupon_code = co.coupon_code
INNER JOIN coupons_types AS ct
ON co.type_id = ct.id
WHERE
bd.website_id=@website_id AND bd.when_it >= '1228082400' AND
co.type_id = '4' ) AS one_page_total_discounts,
(
SELECT
(SUM(o.total_cost) - SUM(o.total_charge))
FROM
orders AS o
INNER JOIN billing_data_paypal AS bd
ON o.id=bd.order_id
INNER JOIN customers_info AS ci
ON ci.user_id = o.customer_id
INNER JOIN ci_users AS u
ON ci.user_id = u.userid
INNER JOIN websites AS w
ON u.site_id = w.id
INNER JOIN orders_used_coupon AS oc
ON oc.order_id = o.id
INNER JOIN coupons AS co
ON oc.coupon_code = co.coupon_code
INNER JOIN coupons_types AS ct
ON co.type_id = ct.id
WHERE
bd.website_id=@website_id AND bd.when_it >= '1228082400' AND
co.type_id <> '4' ) AS percentage_total_discounts,
(SELECT SUM(bd.total) FROM billing_data_paypal AS bd WHERE bd.website_id <> '0' AND bd.when_it >= '1228082400' AND bd.when_it <= '1228255199' AND bd.order_id = '0' AND bd.website_id=@website_id AND bd.business NOT LIKE '%biz@gmail.com%') AS total_user_balance,
(SELECT SUM(crr.request_sum) FROM customers_refund_requests AS crr INNER JOIN orders AS o ON o.id=crr.order_id INNER JOIN billing_data_paypal as bd ON o.id=bd.order_id WHERE bd.website_id <> '0' AND crr.payment_date >= '1228082400' AND crr.payment_date <= '1228255199' AND crr.request_sum < o.total_paid AND bd.website_id=@website_id AND crr.transaction_id <>'0' AND crr.payment_date <>'0' AND bd.business NOT LIKE '%biz@gmail.com%') AS partial_refunds,
(SELECT SUM(crr.request_sum) FROM customers_refund_requests AS crr INNER JOIN orders AS o ON o.id=crr.order_id INNER JOIN billing_data_paypal as bd ON o.id=bd.order_id WHERE bd.website_id <> '0' AND crr.payment_date >= '1228082400' AND crr.payment_date <= '1228255199' AND crr.request_sum = o.total_paid AND bd.website_id=@website_id AND crr.transaction_id <>'0' AND crr.payment_date <>'0' AND bd.business NOT LIKE '%biz@gmail.com%') AS full_refunds
FROM
websites
WHERE
id <> '0'
AND `group` = '6'
GROUP BY id