gistfile1.txt

SELECT commerce_line_item_field_data_commerce_line_items__field_data_field_user.field_user_target_id AS commerce_line_item_field_data_commerce_line_items__field_dat, users_field_data_field_user__field_data_field_firstname.field_firstname_value AS users_field_data_field_user__field_data_field_firstname_fiel, users_field_data_field_user__field_data_field_lastname.field_lastname_value AS users_field_data_field_user__field_data_field_lastname_field, users_commerce_order__field_data_field_firstname.field_firstname_value AS users_commerce_order__field_data_field_firstname_field_first, users_commerce_order__field_data_field_lastname.field_lastname_value AS users_commerce_order__field_data_field_lastname_field_lastna, MIN(commerce_line_item_field_data_commerce_line_items.line_item_id) AS commerce_line_item_field_data_commerce_line_items_line_item_, 'commerce_line_item' AS field_data_field_user_commerce_line_item_entity_type, MIN(users_field_data_field_user.uid) AS users_field_data_field_user_uid, 'user' AS field_data_field_firstname_user_entity_type, 'user' AS field_data_field_lastname_user_entity_type, MIN(users_commerce_order.uid) AS users_commerce_order_uid, MIN(commerce_order.order_id) AS order_id, 'commerce_order' AS field_data_group_audience_commerce_order_entity_type, 'commerce_line_item' AS field_data_commerce_total_commerce_line_item_entity_type, SUM(commerce_line_item_field_data_commerce_line_items__field_data_commerce_total.commerce_total_amount) AS commerce_line_item_field_data_commerce_line_items__field_dat_1, SUM(commerce_line_item_field_data_commerce_line_items__field_data_commerce_total.commerce_total_currency_code) AS commerce_line_item_field_data_commerce_line_items__field_dat_2, 'commerce_line_item' AS field_data_field_line_item_value_commerce_line_item_entity_t, SUM(commerce_line_item_field_data_commerce_line_items__field_data_field_line_item_value.field_line_item_value_amount) AS commerce_line_item_field_data_commerce_line_items__field_dat_3, SUM(commerce_line_item_field_data_commerce_line_items__field_data_field_line_item_value.field_line_item_value_currency_code) AS commerce_line_item_field_data_commerce_line_items__field_dat_4, commerce_line_item_field_data_commerce_line_items__field_data_commerce_total2.commerce_total_amount - commerce_line_item_field_data_commerce_line_items__field_data_field_line_item_amount_due.field_line_item_amount_due_amount - (SELECT COALESCE(SUM(amount), 0) AS payment_amount
FROM 
{commerce_payment_transaction} cpt
WHERE  (commerce_order.order_id = cpt.order_id) AND (cpt.payment_method = 'adhoc') AND (cpt.data NOT LIKE '%"online_payment";i:1%') ) AS amount_paid, SUM(CASE WHEN commerce_line_item_field_data_commerce_line_items__field_data_commerce_order_invoice_due_date.commerce_order_invoice_due_date_value >=1339563600 THEN commerce_line_item_field_data_commerce_line_items__field_data_field_line_item_amount_due.field_line_item_amount_due_amount ELSE 0 END) AS amount_past_due_amount, SUM(CASE WHEN commerce_line_item_field_data_commerce_line_items__field_data_commerce_order_invoice_due_date.commerce_order_invoice_due_date_value <1339563600 THEN commerce_line_item_field_data_commerce_line_items__field_data_field_line_item_amount_due.field_line_item_amount_due_amount ELSE 0 END) AS amount_past_due_amount_1, (SELECT COALESCE(SUM(amount), 0) AS payment_amount
FROM 
{commerce_payment_transaction} cpt
WHERE  (commerce_order.order_id = cpt.order_id) AND (cpt.payment_method = 'adhoc') AND (cpt.data NOT LIKE '%"online_payment";i:1%') ) AS amount_paid_offline
FROM 
{commerce_order} commerce_order
INNER JOIN {og_membership} og_membership_commerce_order ON commerce_order.order_id = og_membership_commerce_order.etid AND og_membership_commerce_order.entity_type = 'commerce_order'
LEFT JOIN {field_data_commerce_line_items} field_data_commerce_line_items ON commerce_order.order_id = field_data_commerce_line_items.entity_id AND (field_data_commerce_line_items.entity_type = 'commerce_order' AND field_data_commerce_line_items.deleted = '0')
INNER JOIN {commerce_line_item} commerce_line_item_field_data_commerce_line_items ON field_data_commerce_line_items.commerce_line_items_line_item_id = commerce_line_item_field_data_commerce_line_items.line_item_id
LEFT JOIN {field_data_field_user} commerce_line_item_field_data_commerce_line_items__field_data_field_user ON commerce_line_item_field_data_commerce_line_items.line_item_id = commerce_line_item_field_data_commerce_line_items__field_data_field_user.entity_id AND (commerce_line_item_field_data_commerce_line_items__field_data_field_user.entity_type = 'commerce_line_item' AND commerce_line_item_field_data_commerce_line_items__field_data_field_user.deleted = '0')
INNER JOIN {users} users_field_data_field_user ON commerce_line_item_field_data_commerce_line_items__field_data_field_user.field_user_target_id = users_field_data_field_user.uid
LEFT JOIN {field_data_commerce_product} commerce_line_item_field_data_commerce_line_items__field_data_commerce_product ON commerce_line_item_field_data_commerce_line_items.line_item_id = commerce_line_item_field_data_commerce_line_items__field_data_commerce_product.entity_id AND (commerce_line_item_field_data_commerce_line_items__field_data_commerce_product.entity_type = 'commerce_line_item' AND commerce_line_item_field_data_commerce_line_items__field_data_commerce_product.deleted = '0')
LEFT JOIN {commerce_product} commerce_product_field_data_commerce_product ON commerce_line_item_field_data_commerce_line_items__field_data_commerce_product.commerce_product_product_id = commerce_product_field_data_commerce_product.product_id
LEFT JOIN {users} users_commerce_order ON commerce_order.uid = users_commerce_order.uid
LEFT JOIN {field_data_field_firstname} users_field_data_field_user__field_data_field_firstname ON users_field_data_field_user.uid = users_field_data_field_user__field_data_field_firstname.entity_id AND (users_field_data_field_user__field_data_field_firstname.entity_type = 'user' AND users_field_data_field_user__field_data_field_firstname.deleted = '0')
LEFT JOIN {field_data_field_lastname} users_field_data_field_user__field_data_field_lastname ON users_field_data_field_user.uid = users_field_data_field_user__field_data_field_lastname.entity_id AND (users_field_data_field_user__field_data_field_lastname.entity_type = 'user' AND users_field_data_field_user__field_data_field_lastname.deleted = '0')
LEFT JOIN {field_data_field_firstname} users_commerce_order__field_data_field_firstname ON users_commerce_order.uid = users_commerce_order__field_data_field_firstname.entity_id AND (users_commerce_order__field_data_field_firstname.entity_type = 'user' AND users_commerce_order__field_data_field_firstname.deleted = '0')
LEFT JOIN {field_data_field_lastname} users_commerce_order__field_data_field_lastname ON users_commerce_order.uid = users_commerce_order__field_data_field_lastname.entity_id AND (users_commerce_order__field_data_field_lastname.entity_type = 'user' AND users_commerce_order__field_data_field_lastname.deleted = '0')
LEFT JOIN {field_data_group_audience} field_data_group_audience ON commerce_order.order_id = field_data_group_audience.entity_id AND (field_data_group_audience.entity_type = 'commerce_order' AND field_data_group_audience.deleted = '0')
LEFT JOIN {field_data_commerce_total} commerce_line_item_field_data_commerce_line_items__field_data_commerce_total ON commerce_line_item_field_data_commerce_line_items.line_item_id = commerce_line_item_field_data_commerce_line_items__field_data_commerce_total.entity_id AND (commerce_line_item_field_data_commerce_line_items__field_data_commerce_total.entity_type = 'commerce_line_item' AND commerce_line_item_field_data_commerce_line_items__field_data_commerce_total.deleted = '0')
LEFT JOIN {field_data_field_line_item_value} commerce_line_item_field_data_commerce_line_items__field_data_field_line_item_value ON commerce_line_item_field_data_commerce_line_items.line_item_id = commerce_line_item_field_data_commerce_line_items__field_data_field_line_item_value.entity_id AND (commerce_line_item_field_data_commerce_line_items__field_data_field_line_item_value.entity_type = 'commerce_line_item' AND commerce_line_item_field_data_commerce_line_items__field_data_field_line_item_value.deleted = '0')
LEFT JOIN {field_data_commerce_total} commerce_line_item_field_data_commerce_line_items__field_data_commerce_total2 ON commerce_line_item_field_data_commerce_line_items.line_item_id = commerce_line_item_field_data_commerce_line_items__field_data_commerce_total2.entity_id AND commerce_line_item_field_data_commerce_line_items__field_data_commerce_total2.entity_type = 'commerce_line_item'
LEFT JOIN {field_data_field_line_item_amount_due} commerce_line_item_field_data_commerce_line_items__field_data_field_line_item_amount_due ON commerce_line_item_field_data_commerce_line_items.line_item_id = commerce_line_item_field_data_commerce_line_items__field_data_field_line_item_amount_due.entity_id AND commerce_line_item_field_data_commerce_line_items__field_data_field_line_item_amount_due.entity_type = 'commerce_line_item'
LEFT JOIN {field_data_commerce_order_invoice_due_date} commerce_line_item_field_data_commerce_line_items__field_data_commerce_order_invoice_due_date ON commerce_line_item_field_data_commerce_line_items.order_id = commerce_line_item_field_data_commerce_line_items__field_data_commerce_order_invoice_due_date.entity_id AND commerce_line_item_field_data_commerce_line_items__field_data_commerce_order_invoice_due_date.entity_type = 'commerce_order'
WHERE (( (og_membership_commerce_order.gid = '' ) )AND(( (commerce_order.status NOT IN  ('canceled')) )))
GROUP BY commerce_line_item_field_data_commerce_line_items__field_dat, users_field_data_field_user__field_data_field_firstname_fiel, users_field_data_field_user__field_data_field_lastname_field, users_commerce_order__field_data_field_firstname_field_first, users_commerce_order__field_data_field_lastname_field_lastna
ORDER BY users_field_data_field_user__field_data_field_lastname_field ASC
LIMIT 50 OFFSET 0