SELECT
 '272' account_id
 ,'ab' account_name
 ,'' brand_name
 ,rep.sales_no
 ,rep.sales_category_id
 ,rep.sales_category_name
 ,rep.customer_name
 ,rep.device_id register_name,
 pg.product_group_id                   group_id,
 pg.product_group_name                 group_name,
 dept.department_id                    department_id,
 dept.department_name                  department_name,
 subDept.department_id                 sub_department_id,
 subDept.department_name               sub_department_name,
 parentDept.department_id              parent_department_id,
 parentDept.department_name            parent_department_name,
 parentPg.product_group_id             parent_product_group_id,
 parentPg.product_group_name           parent_product_group_name,
 rep.product_id                             product_id,
 p.product_name,
 members_id                            member_id,
 m.member_type,
 Concat(m.first_name, IF(m.last_name != '', Concat(' ', m.last_name), '')) member_name
 ,IF(mc.name = ''
   OR mc.name IS NULL, '-', mc.name) AS class_name,
 u.full_name                           user_name
 ,rep.transaction_type
 ,rep.revenue sub_total
 ,rep.discount
 ,rep.total_tax
 ,rep.total_qty
 ,rep.created_date
 ,rep.created_by
FROM
(SELECT
 src.sales_no
 ,sc.sales_category_id
 ,sc.sales_category_name
 ,src.customer_name
 ,src.created_by
 ,src.created_date
 ,IF(src.transaction_type = 4
	,si.subtotal,
        ( si.subtotal - si.discount ) - Ifnull((SELECT Sum(subtotal)
                                                     FROM   sales_item
                                                     WHERE  item_type = 4
                                                            AND parent_id =
						si.sales_item_id), 0))
 revenue
 ,src.transaction_type
 ,si.members_id
 ,si.device_id
 ,si.product_id
 ,si.discount
 ,si.total_tax
 ,si.total_qty
FROM
(
SELECT
 sc.sales_category_id
 ,sc.sales_category_name
FROM
 sales_category sc
)sc
JOIN
(
SELECT
 si.sales_item_id
 ,si.receipt_no

 ,si.sales_no
 ,si.item_name
 ,si.item_type

 ,si.subtotal
 ,si.sales_category_id
 /*
 ,si.sales_item_id
 ,si.parent_id
 */
 ,si.members_id
 ,si.device_id
 ,Ifnull((SELECT Sum(subtotal)
       FROM   sales_item
       WHERE  item_type IN ( 4, 5 )
	      AND parent_id = si.sales_item_id), 0)
 total_tax
 /*
 ,si.subtotal
 ,si.item_name
 ,si.item_type
 ,si.is_deleted
 */
 ,si.product_id
 ,si.discount
 ,si.item_quantity total_qty
FROM
 sales_item si
WHERE
 /*
 si.item_type IN (1,4,5)
 AND
 */
 si.is_deleted=0
 AND
 si.item_seq_number<801
 AND
 (si.item_sub_type=0
	OR
  si.item_sub_type=8
 )
ORDER BY
 si.sales_no
 ,si.receipt_no
 /*,si.parent_id*/
 ,si.item_type
)si
ON
 sc.sales_category_id=si.sales_category_id
RIGHT OUTER JOIN
(
SELECT
 src.sales_no
 ,src.receipt_no
 ,src.sales_category_id
 ,src.transaction_sub_type
 ,src.customer_name
 ,src.created_by
 ,src.transaction_type
 ,src.created_date
FROM
 sales_receipt src
WHERE
 src.transaction_type IN (1,2,3,4)
 AND
 src.is_deleted=0
 AND
 (
  (src.is_closed=1 AND src.transaction_sub_type=10)
  OR
  (src.is_closed=0 AND src.transaction_sub_type=11)
 )
ORDER BY
 src.sales_no
 ,src.receipt_no
)src
ON
 src.receipt_no = si.receipt_no
 AND
 src.sales_no = si.sales_no
WHERE
 si.item_type=1
)rep
LEFT JOIN user u
      ON rep.created_by = u.user_id
LEFT JOIN product p
      ON rep.product_id = p.product_id
LEFT JOIN product_department dept
      ON dept.department_id = p.department_id
	 AND dept.departement_level = 1
LEFT JOIN product_department subDept
      ON p.department_id = subDept.department_id
	 AND subDept.departement_level = 2
LEFT JOIN product_department parentDept
      ON subDept.parent_department_id = parentDept.department_id
	 AND parentDept.departement_level = 1
LEFT JOIN product_group pg
      ON dept.product_group_id = pg.product_group_id
LEFT JOIN product_group parentPg
      ON parentDept.product_group_id = parentPg.product_group_id
LEFT JOIN member m
      ON rep.members_id = m.member_id
LEFT JOIN member_class mc
      ON mc.class_number = m.class_number;