hcjk_SQL_FinReport_ismealFlag_Version

 select * from  
  (
  select
    t.itemName,
    t.patient_name,
    t.price,
    t.fatherItemClassName,
    t.fatherItemClass,
    t.createBy,
    t.createTime,
    sum(t.quantity)  as quantity,
    sum(t.totalMoney) AS totalMoney
  from(
            select
                b.itemID,
                b.itemName,
                d.`name` AS patient_name,
                e.name AS fatherItemClassName,
                g.clinic_receipt_type AS fatherItemClass,
                k.docname AS createBy,
                DATE_FORMAT(a.createtime,'%Y-%m-%d') AS createtime,
                round(b.quantity) AS quantity,
                round(b.price,2) AS price,
                b.mealFlag,
                TRUNCATE(b.preFee - b.discountFee - b.promotionBenefitFee - b.couponFee - b.itemBenefitFee - b.memberCardBenefitFee - b.itemComInvBenefitFee,2) AS totalMoney,
                c.AccountBillId,
                b.settlementID
            FROM  `thc_rcm`.`Cs_Settlement` a
            JOIN `thc_rcm`.`Cs_SettlementDetail` b on a.id = b.settlementID
            JOIN `thc_rcm`.`Cs_AccountBillDetail` c ON b.accountBillDetailID = c.id
            JOIN (
                SELECT u.clinic_id AS clinicid, u.id AS docid,u.name AS docname
                FROM thc_warehouse.staff_record u
                LEFT JOIN thc_warehouse.staff_record_property u1 ON u1.property_code = 'SXX000083' AND u.id = u1.staff_record_id
            ) k     ON a.createBy = k.docid
            JOIN `thc_c_union`.`patient` d ON a.patientID = d.id
            JOIN `thc_sob`.`bpm_health_service` g ON g.id = b.itemID
            JOIN `thc_thc_platform_core`.`tm_value_set_item`  e ON e.code =  g.clinic_receipt_type
             JOIN `thc_thc_platform_core`.`tm_value_set` f ON e.set_code = f.set_code
               WHERE a.isDelete= 0 AND a.returnFlag = 0   AND f.set_code = 'THC_CPOE_OutpatientReceipt'   AND a.id="${id}"
                AND b.mealFlag = 0
  ) t
  group by
    t.itemName,
    t.patient_name,
    t.fatherItemClass,
    t.fatherItemClassName
    
  union
  
  
  select
    t.itemName,
    t.patient_name,
    t.price,
    t.fatherItemClassName,
    t.fatherItemClass,
    t.createBy,
    t.createTime,
    sum(t.quantity)  as quantity,
    0 AS totalMoney
  from(
            select
                b.itemID,
                b.itemName,
                d.name AS patient_name,
                e.name AS fatherItemClassName,
                g.clinic_receipt_type AS fatherItemClass,
                k.docname AS createBy,
                DATE_FORMAT(a.createtime,'%Y-%m-%d') AS createtime,
                round(b.quantity) AS quantity,
                round(b.price,2) AS price,
                TRUNCATE(b.preFee - b.discountFee - b.promotionBenefitFee - b.couponFee - b.itemBenefitFee - b.memberCardBenefitFee - b.itemComInvBenefitFee,2) AS totalMoney,
                c.AccountBillId,
                b.settlementID
            FROM  `thc_rcm`.`Cs_Settlement` a
            JOIN `thc_rcm`.`Cs_SettlementDetail` b on a.id = b.settlementID
            JOIN `thc_rcm`.`Cs_AccountBillDetail` c ON b.accountBillDetailID = c.id
            JOIN (
                SELECT u.clinic_id AS clinicid, u.id AS docid,u.name AS docname
                FROM thc_warehouse.staff_record u
                LEFT JOIN thc_warehouse.staff_record_property u1 ON u1.property_code = 'SXX000083' AND u.id = u1.staff_record_id
            ) k     ON a.createBy = k.docid
            JOIN `thc_c_union`.`patient` d ON a.patientID = d.id
            JOIN `thc_sob`.`bpm_health_service` g on g.id = b.itemID
            JOIN `thc_thc_platform_core`.`tm_value_set_item`  e on e.code =  g.clinic_receipt_type
             JOIN `thc_thc_platform_core`.`tm_value_set` f on e.set_code = f.set_code
               WHERE a.isDelete= 0  AND f.set_code = 'THC_CPOE_OutpatientReceipt'   AND a.id="${id}" 
                AND b.mealFlag = 1
  ) t
  group by
    t.itemName,
    t.patient_name,
    t.fatherItemClass,
    t.fatherItemClassName
 ) tt
limit 10 offset ${startIndex}

select 
    sum(totalMoney) as totalMoney, 
    sum(quantity) as quantity, 
    fatherItemClass,
    fatherItemClassName,
    CASE fatherItemClass                                                 -- 初复诊
        WHEN 21 THEN 1     -- 西药费
        WHEN 22 THEN 2     -- 中成药费
        WHEN 14 THEN 3        -- 材料费 
        WHEN 12 THEN 4        -- 其它费  
    END AS itemClassOrder     
from (
        
 select * from  
  (
  select
    t.itemName,
    t.patient_name,
    t.price,
    t.fatherItemClassName,
    t.fatherItemClass,
    t.createBy,
    t.createTime,
    sum(t.quantity)  as quantity,
    sum(t.totalMoney) AS totalMoney
  from(
            select
                b.itemID,
                b.itemName,
                d.`name` AS patient_name,
                e.name AS fatherItemClassName,
                g.clinic_receipt_type AS fatherItemClass,
                k.docname AS createBy,
                DATE_FORMAT(a.createtime,'%Y-%m-%d') AS createtime,
                round(b.quantity) AS quantity,
                round(b.price,2) AS price,
                b.mealFlag,
                TRUNCATE(b.preFee - b.discountFee - b.promotionBenefitFee - b.couponFee - b.itemBenefitFee - b.memberCardBenefitFee - b.itemComInvBenefitFee,2) AS totalMoney,
                c.AccountBillId,
                b.settlementID
            FROM  `thc_rcm`.`Cs_Settlement` a
            JOIN `thc_rcm`.`Cs_SettlementDetail` b on a.id = b.settlementID
            JOIN `thc_rcm`.`Cs_AccountBillDetail` c ON b.accountBillDetailID = c.id
            JOIN (
                SELECT u.clinic_id AS clinicid, u.id AS docid,u.name AS docname
                FROM thc_warehouse.staff_record u
                LEFT JOIN thc_warehouse.staff_record_property u1 ON u1.property_code = 'SXX000083' AND u.id = u1.staff_record_id
            ) k     ON a.createBy = k.docid
            JOIN `thc_c_union`.`patient` d ON a.patientID = d.id
            JOIN `thc_sob`.`bpm_health_service` g ON g.id = b.itemID
            JOIN `thc_thc_platform_core`.`tm_value_set_item`  e ON e.code =  g.clinic_receipt_type
             JOIN `thc_thc_platform_core`.`tm_value_set` f ON e.set_code = f.set_code
               WHERE a.isDelete= 0 AND a.returnFlag = 0   AND f.set_code = 'THC_CPOE_OutpatientReceipt'   AND a.id="${id}"
                AND b.mealFlag = 0
  ) t
  group by
    t.itemName,
    t.patient_name,
    t.fatherItemClass,
    t.fatherItemClassName
    
  union
  
  
  select
    t.itemName,
    t.patient_name,
    t.price,
    t.fatherItemClassName,
    t.fatherItemClass,
    t.createBy,
    t.createTime,
    sum(t.quantity)  as quantity,
    0 AS totalMoney
  from(
            select
                b.itemID,
                b.itemName,
                d.name AS patient_name,
                e.name AS fatherItemClassName,
                g.clinic_receipt_type AS fatherItemClass,
                k.docname AS createBy,
                DATE_FORMAT(a.createtime,'%Y-%m-%d') AS createtime,
                round(b.quantity) AS quantity,
                round(b.price,2) AS price,
                TRUNCATE(b.preFee - b.discountFee - b.promotionBenefitFee - b.couponFee - b.itemBenefitFee - b.memberCardBenefitFee - b.itemComInvBenefitFee,2) AS totalMoney,
                c.AccountBillId,
                b.settlementID
            FROM  `thc_rcm`.`Cs_Settlement` a
            JOIN `thc_rcm`.`Cs_SettlementDetail` b on a.id = b.settlementID
            JOIN `thc_rcm`.`Cs_AccountBillDetail` c ON b.accountBillDetailID = c.id
            JOIN (
                SELECT u.clinic_id AS clinicid, u.id AS docid,u.name AS docname
                FROM thc_warehouse.staff_record u
                LEFT JOIN thc_warehouse.staff_record_property u1 ON u1.property_code = 'SXX000083' AND u.id = u1.staff_record_id
            ) k     ON a.createBy = k.docid
            JOIN `thc_c_union`.`patient` d ON a.patientID = d.id
            JOIN `thc_sob`.`bpm_health_service` g on g.id = b.itemID
            JOIN `thc_thc_platform_core`.`tm_value_set_item`  e on e.code =  g.clinic_receipt_type
             JOIN `thc_thc_platform_core`.`tm_value_set` f on e.set_code = f.set_code
               WHERE a.isDelete= 0  AND f.set_code = 'THC_CPOE_OutpatientReceipt'   AND a.id="${id}" 
                AND b.mealFlag = 1
  ) t
  group by
    t.itemName,
    t.patient_name,
    t.fatherItemClass,
    t.fatherItemClassName
    
 ) kk
limit 10 offset 0
) tt
where 1=1 and  tt.fatherItemClass in (21,22,14,12)
group by fatherItemClass,fatherItemClassName
order by itemClassOrder asc

新版本，去掉了套餐

===================================================

select
    t.itemName,
    t.patient_name as patientName,
    t.price,
      t.fatherItemClassName,
      t.fatherItemClass,
    t.createBy,
    t.createTime,
    sum(t.quantity)  as quantity,
      sum(t.totalMoney) AS totalMoney
  from(
            select
                b.itemID,
                b.itemName,
                d.`name` AS patient_name,
                e.name AS fatherItemClassName,
                g.clinic_receipt_type AS fatherItemClass,
                k.docname AS createBy,
                DATE_FORMAT(a.createtime,'%Y-%m-%d') AS createtime,
                round(b.quantity) AS quantity,
                round(b.price,2) AS price,
                b.mealFlag,
                TRUNCATE(b.preFee - b.discountFee - b.promotionBenefitFee - b.couponFee - b.itemBenefitFee - b.memberCardBenefitFee - b.itemComInvBenefitFee,2) AS totalMoney,
                c.AccountBillId,
                b.settlementID
            FROM  `thc_rcm`.`Cs_Settlement` a
            JOIN `thc_rcm`.`Cs_SettlementDetail` b on a.id = b.settlementID
            JOIN `thc_rcm`.`Cs_AccountBillDetail` c ON b.accountBillDetailID = c.id
            JOIN (
                SELECT u.clinic_id AS clinicid, u.id AS docid,u.name AS docname
                FROM thc_warehouse.staff_record u
                LEFT JOIN thc_warehouse.staff_record_property u1 ON u1.property_code = 'SXX000083' AND u.id = u1.staff_record_id
            ) k     ON a.createBy = k.docid
            JOIN `thc_c_union`.`patient` d ON a.patientID = d.id
            JOIN `thc_sob`.`bpm_health_service` g ON g.id = b.itemID
            JOIN `thc_thc_platform_core`.`tm_value_set_item`  e ON e.code =  g.clinic_receipt_type
             JOIN `thc_thc_platform_core`.`tm_value_set` f ON e.set_code = f.set_code
               WHERE a.isDelete= 0 AND a.returnFlag = 0   AND f.set_code = 'THC_CPOE_OutpatientReceipt'   AND a.id="${id}"
                AND b.mealFlag = 0
  ) t
  group by
    t.itemID,
    t.fatherItemClass
 order by t.itemID
limit 10 offset ${startIndex}