daily pastebin goal
42%
SHARE
TWEET

Untitled

a guest Feb 13th, 2018 57 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT
  2.  '272' account_id
  3.  ,'ab' account_name
  4.  ,'' brand_name
  5.  ,rep.sales_no
  6.  ,rep.sales_category_id
  7.  ,rep.sales_category_name
  8.  ,rep.customer_name
  9.  ,rep.device_id register_name,
  10.  pg.product_group_id                   group_id,
  11.  pg.product_group_name                 group_name,
  12.  dept.department_id                    department_id,
  13.  dept.department_name                  department_name,
  14.  subDept.department_id                 sub_department_id,
  15.  subDept.department_name               sub_department_name,
  16.  parentDept.department_id              parent_department_id,
  17.  parentDept.department_name            parent_department_name,
  18.  parentPg.product_group_id             parent_product_group_id,
  19.  parentPg.product_group_name           parent_product_group_name,
  20.  rep.product_id                             product_id,
  21.  p.product_name,  
  22.  members_id                            member_id,
  23.  m.member_type,
  24.  Concat(m.first_name, IF(m.last_name != '', Concat(' ', m.last_name), '')) member_name
  25.  ,IF(mc.name = ''
  26.    OR mc.name IS NULL, '-', mc.name) AS class_name,
  27.  u.full_name                           user_name
  28.  ,rep.transaction_type
  29.  ,rep.revenue sub_total
  30.  ,rep.discount
  31.  ,rep.total_tax
  32.  ,rep.total_qty
  33.  ,rep.created_date
  34.  ,rep.created_by
  35. FROM
  36. (SELECT
  37.  src.sales_no
  38.  ,sc.sales_category_id
  39.  ,sc.sales_category_name
  40.  ,src.customer_name
  41.  ,src.created_by
  42.  ,src.created_date
  43.  ,IF(src.transaction_type = 4
  44.     ,si.subtotal,
  45.         ( si.subtotal - si.discount ) - Ifnull((SELECT Sum(subtotal)
  46.                                                      FROM   sales_item
  47.                                                      WHERE  item_type = 4
  48.                                                             AND parent_id =
  49.                         si.sales_item_id), 0))
  50.  revenue
  51.  ,src.transaction_type
  52.  ,si.members_id
  53.  ,si.device_id
  54.  ,si.product_id
  55.  ,si.discount
  56.  ,si.total_tax
  57.  ,si.total_qty
  58. FROM
  59. (
  60. SELECT
  61.  sc.sales_category_id
  62.  ,sc.sales_category_name
  63. FROM
  64.  sales_category sc
  65. )sc
  66. JOIN
  67. (
  68. SELECT
  69.  si.sales_item_id
  70.  ,si.receipt_no
  71.  
  72.  ,si.sales_no
  73.  ,si.item_name
  74.  ,si.item_type
  75.  
  76.  ,si.subtotal
  77.  ,si.sales_category_id
  78.  /*
  79.  ,si.sales_item_id
  80.  ,si.parent_id
  81.  */
  82.  ,si.members_id
  83.  ,si.device_id
  84.  ,Ifnull((SELECT Sum(subtotal)
  85.        FROM   sales_item
  86.        WHERE  item_type IN ( 4, 5 )
  87.           AND parent_id = si.sales_item_id), 0)
  88.  total_tax
  89.  /*
  90.  ,si.subtotal
  91.  ,si.item_name
  92.  ,si.item_type
  93.  ,si.is_deleted
  94.  */
  95.  ,si.product_id
  96.  ,si.discount
  97.  ,si.item_quantity total_qty
  98. FROM
  99.  sales_item si
  100. WHERE
  101.  /*
  102.  si.item_type IN (1,4,5)
  103.  AND
  104.  */
  105.  si.is_deleted=0
  106.  AND
  107.  si.item_seq_number<801
  108.  AND
  109.  (si.item_sub_type=0
  110.     OR
  111.   si.item_sub_type=8
  112.  )
  113. ORDER BY
  114.  si.sales_no
  115.  ,si.receipt_no
  116.  /*,si.parent_id*/
  117.  ,si.item_type
  118. )si
  119. ON
  120.  sc.sales_category_id=si.sales_category_id
  121. RIGHT OUTER JOIN
  122. (
  123. SELECT
  124.  src.sales_no
  125.  ,src.receipt_no
  126.  ,src.sales_category_id
  127.  ,src.transaction_sub_type
  128.  ,src.customer_name
  129.  ,src.created_by
  130.  ,src.transaction_type
  131.  ,src.created_date
  132. FROM
  133.  sales_receipt src
  134. WHERE
  135.  src.transaction_type IN (1,2,3,4)
  136.  AND
  137.  src.is_deleted=0
  138.  AND
  139.  (
  140.   (src.is_closed=1 AND src.transaction_sub_type=10)
  141.   OR
  142.   (src.is_closed=0 AND src.transaction_sub_type=11)
  143.  )
  144. ORDER BY
  145.  src.sales_no
  146.  ,src.receipt_no
  147. )src
  148. ON
  149.  src.receipt_no = si.receipt_no
  150.  AND
  151.  src.sales_no = si.sales_no
  152. WHERE
  153.  si.item_type=1
  154. )rep
  155. LEFT JOIN user u
  156.       ON rep.created_by = u.user_id
  157. LEFT JOIN product p
  158.       ON rep.product_id = p.product_id
  159. LEFT JOIN product_department dept
  160.       ON dept.department_id = p.department_id
  161.      AND dept.departement_level = 1
  162. LEFT JOIN product_department subDept
  163.       ON p.department_id = subDept.department_id
  164.      AND subDept.departement_level = 2
  165. LEFT JOIN product_department parentDept
  166.       ON subDept.parent_department_id = parentDept.department_id
  167.      AND parentDept.departement_level = 1
  168. LEFT JOIN product_group pg
  169.       ON dept.product_group_id = pg.product_group_id
  170. LEFT JOIN product_group parentPg
  171.       ON parentDept.product_group_id = parentPg.product_group_id
  172. LEFT JOIN member m
  173.       ON rep.members_id = m.member_id
  174. LEFT JOIN member_class mc
  175.       ON mc.class_number = m.class_number;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top