海南医院帆软报表 最终版本SQL
1. 挂号微信收入明细
-- WANTED-1283 门诊查询统计报表-挂号微信收入明细 select p.name as patientName, -- 患者姓名 p.birthday, -- 出生日期 bl.outpatient_number as blNumber, -- 病历号 case p.sex WHEN 1 THEN '男' WHEN 2 THEN '女' END as sex, -- 性别 round((f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount,2) AS guaHaoMoney, h.type, h.pay_method, i.name as payMethod, h.flow_fee as weiXinMoney, -- 微信到账 h.charge as weiXinCharge, -- 微信手续 h.pay_time as payTime, -- 缴费时间 a.create_time, -- 挂号时间 a.dept_name, -- 挂号科室名称 a.appointment_doctor_name as doctorName, -- 挂号医生 (select t.docname from ( SELECT u.id AS docid,u1.property_value 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 ) t where t.docid = a.creator) AS creater, CASE a.subsequent_visit WHEN 0 THEN '初诊' WHEN 1 THEN '复诊' WHEN 2 THEN '转诊' WHEN 3 THEN '急诊' WHEN 4 THEN '体检' WHEN 5 THEN '简易' WHEN 6 THEN '疫苗' WHEN 7 THEN '团队体检' END AS isReVisit -- 初复诊 from `thc_arrange`.`bpm_appointment` a inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id INNER JOIN `thc_passport`.`patient` p on a.patient_id = p.id left join `thc_passport`.`patient_org` bl on a.patient_id = bl.patient_id left JOIN `thc_rcm`.`pay_payment` i on h.pay_method = i.value and g.clinicID = i.clinicid where 1=1 and a.del_flag = 0 and d.isDelete = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 and e.itemClass = 1 and e.returnFlag is NULL and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 and h.type = 1 and h.pay_method = 4 -- 微信支付 ${if(len(creator) == 0,"","and a.creator = '" + creator + "'")} ${if(len(dept) == 0,"","and a.dept_id = '" + dept + "'")} ${if(len(startTime) == 0,"","and h.pay_time >= '" + startTime + "'")} ${if(len(endTime) == 0,"","and h.pay_time <= '" + endTime + "'")} ORDER BY g.createtime desc,a.create_time desc
2.挂号患者信息查询
-- ----------------------- -- 患者信息查询 -- ----------------------- select DISTINCT CASE a.appointment_state WHEN -1 THEN '是' ELSE '否' END AS returnFlag, -- 退号标志 CASE a.is_appoint_resource WHEN 0 THEN '否' WHEN 1 THEN '是' END as isAppointResource, -- 指定标志 a.create_time as guaHaoTime, -- 挂号时间 p.create_time as createDocTime, -- 建档时间 -- 挂号实收金额 round((f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount,2) AS realFee, round(t1.realFee,2) as menZenRealMoney, -- 门诊实收金额 j.outpatient_number as blNumber, -- 病历号 p.name as patientName, -- 患者姓名 a.dept_name, -- 科室名称 a.appointment_doctor_name as doctorName, -- 医生 CASE a.subsequent_visit -- 初复诊 WHEN 0 THEN '初诊' WHEN 1 THEN '复诊' WHEN 2 THEN '转诊' WHEN 3 THEN '急诊' WHEN 4 THEN '体检' WHEN 5 THEN '简易' WHEN 6 THEN '疫苗' WHEN 7 THEN '团队体检' END AS isReVisit, -- 初复诊 channel.name as sourceName, -- 信息(渠道)来源 p.birthday, -- 出生日期 if(i.id_no is null, if(i.other_type is null, null, (select e.name from `thc_warehouse`.`sys_type_info` e JOIN `thc_warehouse`.`sys_type` f ON e.sys_type_id = f.id WHERE f.`code` = 'THC_WH_PERSON_CARD' and e.value = i.other_type) ), '身份证') as cardType, -- 证件类型 if(i.id_no is null,i.other_no,i.id_no) as cardNo, -- 证件号码 CONCAT(t2.province,t2.city,t2.county,t2.addressDetail) as liveAddr, -- 住址 CONCAT(t2.province2,t2.city2,t2.county2,t2.addressDetail2) as bornAddr, -- 户籍 if(locate('区',t2.county2) > 0,t2.county2,'') as qu, -- 区 a.patient_phone, (select t.docname from ( SELECT u.id AS docid,u1.property_value 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 ) t where t.docid = a.creator) AS creater, -- a.appointment_starttime, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, -- 预约时间 -- CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, a.referrer_dept_name, -- 爱院科室 a.referrer_name, -- 爱院人 a.description -- 备注 FROM `thc_arrange`.`bpm_appointment` a INNER JOIN `thc_sob`.`bpm_service_order` b on a.orderId = b.id INNER JOIN `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id AND c.id = a.order_item_id INNER JOIN `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id INNER JOIN `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId INNER JOIN `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id AND f.accountBillDetailID = e.id INNER JOIN `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID INNER JOIN `thc_passport`.`patient` p on a.patient_id = p.id INNER JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id LEFT JOIN `thc_c_union`.`member_channel` channel on a.channel_id = channel.id LEFT JOIN `thc_passport`.`contact` i on a.patient_id = i.patient_id LEFT JOIN `thc_passport`.`patient_org` j on a.patient_id = j.patient_id left join ( select aa.id, round((ff.preFee - ff.discountFee - ff.promotionBenefitFee - ff.couponFee - ff.itemBenefitFee - ff.memberCardBenefitFee - ff.itemComInvBenefitFee) * ff.discount,2) AS realFee from `thc_arrange`.`bpm_appointment` aa inner join `thc_sob`.`bpm_service_order` bb on aa.orderId = bb.id inner join `thc_sob`.`bpm_service_order_item` cc on cc.service_order_id = bb.id and cc.id = aa.`order_item_id` inner join `thc_rcm`.`Cs_AccountBill` dd on dd.orderID = bb.id and dd.`isDelete` = 0 and dd.orderSource = 1 and dd.orderType = 3 and dd.returnFlag = 0 inner join `thc_rcm`.`Cs_AccountBillDetail` ee on dd.id = ee.AccountBillId and ee.itemClass = 1 and ee.returnFlag is NULL inner join `thc_rcm`.`Cs_SettlementDetail` ff on ff.accountBillID = dd.id and ff.accountBillDetailID = ee.id inner join `thc_rcm`.`Cs_Settlement` gg on gg.id = ff.settlementID and gg.`isDelete` = 0 and gg.returnFlag = 0 and gg.settlementType=1 -- 门诊收费 where 1=1 and aa.del_flag = 0 ) t1 on a.id = t1.id left join ( select aa.id, -- 住址 (select city.name from `thc_passport`.`city` where city.id = (if(p.address is null,null, TRIM(BOTH '"' FROM JSON_EXTRACT(p.address,'$.province'))))) as province, (select city.name from `thc_passport`.`city` where city.id = (if(p.address is null,null, TRIM(BOTH '"' FROM JSON_EXTRACT(p.address,'$.city'))))) as city, (select city.name from `thc_passport`.`city` where city.id = (if(p.address is null,null, TRIM(BOTH '"' FROM JSON_EXTRACT(p.address,'$.county'))))) as county, if(p.address is null,null, TRIM(BOTH '"' FROM JSON_EXTRACT(p.address,'$.addressDetail'))) as addressDetail, -- 户籍 (select city.name from `thc_passport`.`city` where city.id = (if(p.household is null,null, TRIM(BOTH '"' FROM JSON_EXTRACT(p.household,'$.province'))))) as province2, (select city.name from `thc_passport`.`city` where city.id = (if(p.household is null,null, TRIM(BOTH '"' FROM JSON_EXTRACT(p.household,'$.city'))))) as city2, (select city.name from `thc_passport`.`city` where city.id = (if(p.household is null,null, TRIM(BOTH '"' FROM JSON_EXTRACT(p.household,'$.county'))))) as county2, if(p.household is null,null, TRIM(BOTH '"' FROM JSON_EXTRACT(p.household,'$.addressDetail'))) as addressDetail2, round((ff.preFee - ff.discountFee - ff.promotionBenefitFee - ff.couponFee - ff.itemBenefitFee - ff.memberCardBenefitFee - ff.itemComInvBenefitFee) * ff.discount,2) AS realFee from `thc_arrange`.`bpm_appointment` aa inner join `thc_sob`.`bpm_service_order` bb on aa.orderId = bb.id inner join `thc_sob`.`bpm_service_order_item` cc on cc.service_order_id = bb.id and cc.id = aa.`order_item_id` inner join `thc_rcm`.`Cs_AccountBill` dd on dd.orderID = bb.id and dd.`isDelete` = 0 and dd.orderSource = 1 and dd.orderType = 3 and dd.returnFlag = 0 inner join `thc_rcm`.`Cs_AccountBillDetail` ee on dd.id = ee.AccountBillId and ee.itemClass = 1 and ee.returnFlag is NULL inner join `thc_rcm`.`Cs_SettlementDetail` ff on ff.accountBillID = dd.id and ff.accountBillDetailID = ee.id inner join `thc_rcm`.`Cs_Settlement` gg on gg.id = ff.settlementID and gg.`isDelete` = 0 and gg.returnFlag = 0 -- 门诊挂号 and gg.settlementType=2 -- 非挂号消费类型 inner join `thc_passport`.`patient` p on aa.patient_id = p.id where 1=1 and aa.del_flag = 0 ) t2 on a.id = t2.id WHERE 1=1 AND a.del_flag = 0 AND d.isDelete = 0 AND d.orderSource = 1 AND d.orderType = 3 AND d.returnFlag = 0 AND e.itemClass = 1 AND e.returnFlag is NULL AND g.settlementType=2 AND g.isDelete = 0 AND g.returnFlag = 0 -- 门诊挂号 ${if(len(startTime) == 0,"","AND a.create_time >= '" + startTime + "'")} ${if(len(endTime) == 0,"","AND a.create_time <= '" + endTime + "'")} -- 爱院标志 ${if(loveYuan == 0,"and a.appointment_state is null","")} ${if(loveYuan == 1,"and a.appointment_state is not null","")} ${if(len(deptId) == 0,"","AND a.dept_id = '" + deptId + "'")} -- 部门 ${if(len(doctor) == 0,"","AND a.appointment_doctor_id = '" + doctor + "'")} -- 医生 ${if(len(channelId) == 0,"","AND a.channel_id = '" + channelId + "'")} -- 信息来源 ${if(len(patientName) == 0,"","AND p.name like '%" + patientName + "%'")} -- 姓名 ${if(len(blNumber) == 0,"","AND j.outpatient_number like '%" + blNumber + "%'")} -- 病历号 ${if(len(phone) == 0,"","AND a.patient_phone like '%" + phone + "%'")} -- 电话号 ${if(len(subVisit) == 0,"","AND a.subsequent_visit = '" + subVisit + "'")} -- 初复诊 ${if(len(doctor) == 0,"","and a.appointment_doctor_id = '" + doctor + "'")} -- 接诊员 ${if(isReturn == -1,"and a.appointment_state = -1","")} -- 退号标志 ${if(isReturn == 0,"and a.appointment_state != -1","")} -- 退号标志
3.挂号按来源统计
select channel.name as name, DATE_FORMAT(a.appointment_date,${if(dateType == 1,"'%Y-%m'","'%Y-%m-%d'")}) as date, -- 按日期还是月份展示 IFNULL(count(1),0) as num from `thc_arrange`.`bpm_appointment` a inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID left join `thc_c_union`.`member_channel` channel on a.channel_id = channel.id where 1=1 and a.del_flag = 0 and d.isDelete = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 and e.itemClass = 1 and e.returnFlag is NULL and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 and g.id in ( SELECT DISTINCT g.id from `thc_arrange`.`bpm_appointment` a inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id where 1=1 and a.del_flag = 0 and d.isDelete = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 and e.itemClass = 1 and e.returnFlag is NULL and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 and h.type = 1 and h.pay_state = 1 ) and DATE_FORMAT(a.appointment_date,'%Y-%m-%d') >= DATE_FORMAT(${if(len(startTime) == 0, "'0000-01-01'" , "'" + startTime + "'" )},'%Y-%m-%d') and DATE_FORMAT(a.appointment_date,'%Y-%m-%d') <= DATE_FORMAT(${if(len(endTime) == 0,"'9999-01-01'","'" + endTime + "'")},'%Y-%m-%d') ${if(len(subVisit) == 0,"","and a.subsequent_visit = '" + subVisit + "'")} -- and channel.name is not null group by date_format(a.appointment_date, ${if(dateType == 1,"'%Y-%m'","'%Y-%m-%d'")}), channel.name -- 按日期还是月份分组查询 order by a.appointment_date DESC, data_source asc
3.1有交易流水的条件
AND g.id IN ( SELECT DISTINCT g.id FROM `thc_arrange`.`bpm_appointment` a INNER JOIN `thc_sob`.`bpm_service_order` b on a.orderId = b.id INNER JOIN `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id AND c.id = a.order_item_id INNER JOIN `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id INNER JOIN `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId INNER JOIN `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id AND f.accountBillDetailID = e.id INNER JOIN `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID INNER JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id WHERE 1=1 AND a.del_flag = 0 AND d.isDelete = 0 AND d.orderSource = 1 AND d.orderType = 3 AND d.returnFlag = 0 AND e.itemClass = 1 AND e.returnFlag is NULL AND g.settlementType=2 AND g.`isDelete` = 0 AND g.returnFlag = 0 -- 门诊挂号 AND h.type = 1 )
4.挂号按医生统计
1 select 2 t0.deptName, 3 t0.doctorName, 4 t0.deptId, 5 t0.doctorId, 6 t0.startTime, 7 t0.endTime, 8 IFNULL(t0.num,0) as num0, 9 IFNULL(t1.num,0) as num1, 10 IFNULL(t2.num,0) as num2, 11 IFNULL(t3.num,0) as num3, 12 IFNULL(t4.num,0) as num4, 13 IFNULL(t5.num,0) as num5, 14 IFNULL(t6.num,0) as num6, 15 IFNULL(t7.num,0) as num7, 16 IFNULL(t8.num,0) as num8, 17 IFNULL(t9.num,0) as num9, 18 IFNULL(t10.num,0) as num10, 19 IFNULL(t11.num,0) as num11, 20 IFNULL(t12.num,0) as num12, 21 IFNULL(t13.num,0) as num13, 22 IFNULL(t14.num,0) as num14 23 from 24 -- ------------ 25 -- 总挂号数 26 -- ------------ 27 ( 28 select t.startTime, t.endTime, count(1) as num,t.deptId, doctorId,t.deptName,t.create_time,t.doctorName from 29 ( 30 select 31 a.data_source, 32 a.is_appoint_resource, 33 a.subsequent_visit, 34 a.appointment_state, 35 a.dept_id as deptId, 36 a.appointment_doctor_id as doctorId, 37 a.dept_name as deptName, 38 a.create_time, 39 a.appointment_doctor_name as doctorName, 40 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 41 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 42 f.preFee, -- 原价 43 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 44 f.discount 45 from `thc_arrange`.`bpm_appointment` a 46 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 47 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` 48 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 49 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 50 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 51 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 52 where a.del_flag = 0 53 AND g.id IN ( 54 SELECT DISTINCT g.id 55 FROM `thc_arrange`.`bpm_appointment` a 56 INNER JOIN `thc_sob`.`bpm_service_order` b on a.orderId = b.id 57 INNER JOIN `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id AND c.id = a.order_item_id 58 INNER JOIN `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 59 INNER JOIN `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 60 INNER JOIN `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id AND f.accountBillDetailID = e.id 61 INNER JOIN `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 62 INNER JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 63 WHERE 1=1 64 AND a.del_flag = 0 65 AND d.isDelete = 0 AND d.orderSource = 1 AND d.orderType = 3 AND d.returnFlag = 0 66 AND e.itemClass = 1 AND e.returnFlag is NULL 67 AND g.settlementType=2 AND g.`isDelete` = 0 AND g.returnFlag = 0 -- 门诊挂号 68 AND h.type = 1 and h.pay_state = 1 -- 支付成功 69 ) 70 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 71 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 72 ) t 73 where 1=1 74 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 75 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 76 group by t.deptId,t.doctorId 77 ) t0 78 79 -- ------------ 80 -- 退号 81 -- ------------ 82 left join 83 ( 84 select count(1) as num,t.deptId, doctorId,t.deptName,t.create_time,t.doctorName from 85 ( 86 select 87 a.data_source, 88 a.is_appoint_resource, 89 a.subsequent_visit, 90 a.dept_id as deptId, 91 a.appointment_doctor_id as doctorId, 92 a.appointment_state, 93 a.dept_name as deptName, 94 a.create_time, 95 a.appointment_doctor_name as doctorName, 96 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 97 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 98 f.preFee, -- 原价 99 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 100 f.discount 101 from `thc_arrange`.`bpm_appointment` a 102 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 103 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` 104 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 105 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 106 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 107 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 108 where 1=1 and a.del_flag = 0 109 AND g.id IN ( 110 SELECT DISTINCT g.id 111 FROM `thc_arrange`.`bpm_appointment` a 112 INNER JOIN `thc_sob`.`bpm_service_order` b on a.orderId = b.id 113 INNER JOIN `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id AND c.id = a.order_item_id 114 INNER JOIN `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 115 INNER JOIN `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 116 INNER JOIN `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id AND f.accountBillDetailID = e.id 117 INNER JOIN `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 118 INNER JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 119 WHERE 1=1 120 AND a.del_flag = 0 121 AND d.isDelete = 0 AND d.orderSource = 1 AND d.orderType = 3 AND d.returnFlag = 0 122 AND e.itemClass = 1 AND e.returnFlag is NULL 123 AND g.settlementType=2 AND g.`isDelete` = 0 AND g.returnFlag = 0 -- 门诊挂号 124 AND h.type = 1 and h.pay_state = 1 -- 支付成功 125 ) 126 and a.appointment_state = -1 127 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 128 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 129 ) t 130 where 1=1 131 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 132 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 133 group by t.deptId,t.doctorId 134 ) t1 135 on t0.deptId = t1.deptId and t0.doctorId = t1.doctorId 136 137 -- ------------ 138 -- 实际挂号数 = 初诊数 + 复诊数 + 转诊数 139 -- ------------ 140 left join 141 ( 142 select count(1) as num,t.deptId, doctorId,t.deptName,t.create_time,t.doctorName from 143 ( 144 select 145 a.data_source, 146 a.is_appoint_resource, 147 a.subsequent_visit, 148 a.dept_id as deptId, 149 a.appointment_doctor_id as doctorId, 150 a.appointment_state, 151 a.dept_name as deptName, 152 a.create_time, 153 a.appointment_doctor_name as doctorName, 154 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 155 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 156 f.preFee, -- 原价 157 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 158 f.discount 159 from `thc_arrange`.`bpm_appointment` a 160 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 161 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` 162 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 163 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 164 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 165 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 166 where a.del_flag = 0 and a.subsequent_visit in (0,1,2) 167 AND g.id IN ( 168 SELECT DISTINCT g.id 169 FROM `thc_arrange`.`bpm_appointment` a 170 INNER JOIN `thc_sob`.`bpm_service_order` b on a.orderId = b.id 171 INNER JOIN `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id AND c.id = a.order_item_id 172 INNER JOIN `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 173 INNER JOIN `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 174 INNER JOIN `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id AND f.accountBillDetailID = e.id 175 INNER JOIN `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 176 INNER JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 177 WHERE 1=1 178 AND a.del_flag = 0 179 AND d.isDelete = 0 AND d.orderSource = 1 AND d.orderType = 3 AND d.returnFlag = 0 180 AND e.itemClass = 1 AND e.returnFlag is NULL 181 AND g.settlementType=2 AND g.`isDelete` = 0 AND g.returnFlag = 0 -- 门诊挂号 182 AND h.type = 1 and h.pay_state = 1 -- 支付成功 183 ) 184 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 185 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 186 ) t 187 where 1=1 188 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 189 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 190 group by t.deptId,t.doctorId 191 ) t2 192 on t0.deptId = t2.deptId and t0.doctorId = t2.doctorId 193 194 -- ------------ 195 -- 指定 196 -- ------------ 197 left join 198 ( 199 select count(1) as num,t.deptId, doctorId,t.deptName,t.create_time,t.doctorName from 200 ( 201 select 202 a.data_source, 203 a.is_appoint_resource, 204 a.subsequent_visit, 205 a.dept_id as deptId, 206 a.appointment_doctor_id as doctorId, 207 a.appointment_state, 208 a.dept_name as deptName, 209 a.create_time, 210 a.appointment_doctor_name as doctorName, 211 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 212 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 213 f.preFee, -- 原价 214 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 215 f.discount 216 from `thc_arrange`.`bpm_appointment` a 217 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 218 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` 219 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 220 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 221 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 222 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 223 where a.del_flag = 0 and a.is_appoint_resource = 1 224 AND g.id IN ( 225 SELECT DISTINCT g.id 226 FROM `thc_arrange`.`bpm_appointment` a 227 INNER JOIN `thc_sob`.`bpm_service_order` b on a.orderId = b.id 228 INNER JOIN `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id AND c.id = a.order_item_id 229 INNER JOIN `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 230 INNER JOIN `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 231 INNER JOIN `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id AND f.accountBillDetailID = e.id 232 INNER JOIN `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 233 INNER JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 234 WHERE 1=1 235 AND a.del_flag = 0 236 AND d.isDelete = 0 AND d.orderSource = 1 AND d.orderType = 3 AND d.returnFlag = 0 237 AND e.itemClass = 1 AND e.returnFlag is NULL 238 AND g.settlementType=2 AND g.`isDelete` = 0 AND g.returnFlag = 0 -- 门诊挂号 239 AND h.type = 1 and h.pay_state = 1 -- 支付成功 240 ) 241 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 242 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 243 ) t 244 where 1=1 245 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 246 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 247 group by t.deptId,t.doctorId 248 ) t3 249 on t0.deptId = t3.deptId and t0.doctorId = t3.doctorId 250 251 252 -- ------------ 253 -- 初诊 254 -- ------------ 255 left join 256 ( 257 select count(1) as num,t.deptId, doctorId,t.deptName,t.create_time,t.doctorName from 258 ( 259 select 260 a.data_source, 261 a.is_appoint_resource, 262 a.subsequent_visit, 263 a.dept_id as deptId, 264 a.appointment_doctor_id as doctorId, 265 a.appointment_state, 266 a.dept_name as deptName, 267 a.create_time, 268 a.appointment_doctor_name as doctorName, 269 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 270 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 271 f.preFee, -- 原价 272 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 273 f.discount 274 from `thc_arrange`.`bpm_appointment` a 275 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 276 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` 277 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 278 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 279 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 280 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 281 where a.del_flag = 0 and a.subsequent_visit = 0 282 AND g.id IN ( 283 SELECT DISTINCT g.id 284 FROM `thc_arrange`.`bpm_appointment` a 285 INNER JOIN `thc_sob`.`bpm_service_order` b on a.orderId = b.id 286 INNER JOIN `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id AND c.id = a.order_item_id 287 INNER JOIN `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 288 INNER JOIN `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 289 INNER JOIN `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id AND f.accountBillDetailID = e.id 290 INNER JOIN `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 291 INNER JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 292 WHERE 1=1 293 AND a.del_flag = 0 294 AND d.isDelete = 0 AND d.orderSource = 1 AND d.orderType = 3 AND d.returnFlag = 0 295 AND e.itemClass = 1 AND e.returnFlag is NULL 296 AND g.settlementType=2 AND g.`isDelete` = 0 AND g.returnFlag = 0 -- 门诊挂号 297 AND h.type = 1 and h.pay_state = 1 -- 支付成功 298 ) 299 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 300 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 301 ) t 302 where 1=1 303 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 304 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 305 group by t.deptId,t.doctorId 306 ) t4 307 on t0.deptId = t4.deptId and t0.doctorId = t4.doctorId 308 309 -- ------------ 310 -- 复诊 311 -- ------------ 312 left join 313 ( 314 select count(1) as num,t.deptId, doctorId,t.deptName,t.create_time,t.doctorName from 315 ( 316 select 317 a.data_source, 318 a.is_appoint_resource, 319 a.subsequent_visit, 320 a.dept_id as deptId, 321 a.appointment_doctor_id as doctorId, 322 a.appointment_state, 323 a.dept_name as deptName, 324 a.create_time, 325 a.appointment_doctor_name as doctorName, 326 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 327 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 328 f.preFee, -- 原价 329 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 330 f.discount 331 from `thc_arrange`.`bpm_appointment` a 332 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 333 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` 334 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 335 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 336 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 337 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 338 where a.del_flag = 0 and a.subsequent_visit = 1 339 AND g.id IN ( 340 SELECT DISTINCT g.id 341 FROM `thc_arrange`.`bpm_appointment` a 342 INNER JOIN `thc_sob`.`bpm_service_order` b on a.orderId = b.id 343 INNER JOIN `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id AND c.id = a.order_item_id 344 INNER JOIN `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 345 INNER JOIN `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 346 INNER JOIN `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id AND f.accountBillDetailID = e.id 347 INNER JOIN `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 348 INNER JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 349 WHERE 1=1 350 AND a.del_flag = 0 351 AND d.isDelete = 0 AND d.orderSource = 1 AND d.orderType = 3 AND d.returnFlag = 0 352 AND e.itemClass = 1 AND e.returnFlag is NULL 353 AND g.settlementType=2 AND g.`isDelete` = 0 AND g.returnFlag = 0 -- 门诊挂号 354 AND h.type = 1 and h.pay_state = 1 -- 支付成功 355 ) 356 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 357 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 358 ) t 359 where 1=1 360 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 361 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 362 group by t.deptId,t.doctorId 363 ) t5 364 on t0.deptId = t5.deptId and t0.doctorId = t5.doctorId 365 366 367 368 -- ------------ 369 -- 体检 370 -- ------------ 371 left join 372 ( 373 select count(1) as num,t.deptId, doctorId,t.deptName,t.create_time,t.doctorName from 374 ( 375 select 376 a.data_source, 377 a.is_appoint_resource, 378 a.subsequent_visit, 379 a.dept_id as deptId, 380 a.appointment_doctor_id as doctorId, 381 a.appointment_state, 382 a.dept_name as deptName, 383 a.create_time, 384 a.appointment_doctor_name as doctorName, 385 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 386 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 387 f.preFee, -- 原价 388 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 389 f.discount 390 from `thc_arrange`.`bpm_appointment` a 391 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 392 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` 393 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 394 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 395 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 396 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 397 where a.del_flag = 0 and a.subsequent_visit = 4 398 AND g.id IN ( 399 SELECT DISTINCT g.id 400 FROM `thc_arrange`.`bpm_appointment` a 401 INNER JOIN `thc_sob`.`bpm_service_order` b on a.orderId = b.id 402 INNER JOIN `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id AND c.id = a.order_item_id 403 INNER JOIN `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 404 INNER JOIN `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 405 INNER JOIN `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id AND f.accountBillDetailID = e.id 406 INNER JOIN `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 407 INNER JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 408 WHERE 1=1 409 AND a.del_flag = 0 410 AND d.isDelete = 0 AND d.orderSource = 1 AND d.orderType = 3 AND d.returnFlag = 0 411 AND e.itemClass = 1 AND e.returnFlag is NULL 412 AND g.settlementType=2 AND g.`isDelete` = 0 AND g.returnFlag = 0 -- 门诊挂号 413 AND h.type = 1 and h.pay_state = 1 -- 支付成功 414 ) 415 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 416 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 417 ) t 418 where 1=1 419 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 420 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 421 group by t.deptId,t.doctorId 422 ) t6 423 on t0.deptId = t6.deptId and t0.doctorId = t6.doctorId 424 425 -- ------------ 426 -- 团队 427 -- ------------ 428 left join 429 ( 430 select count(1) as num,t.deptId, doctorId,t.deptName,t.create_time,t.doctorName from 431 ( 432 select 433 a.data_source, 434 a.is_appoint_resource, 435 a.subsequent_visit, 436 a.dept_id as deptId, 437 a.appointment_doctor_id as doctorId, 438 a.appointment_state, 439 a.dept_name as deptName, 440 a.create_time, 441 a.appointment_doctor_name as doctorName, 442 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 443 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 444 f.preFee, -- 原价 445 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 446 f.discount 447 from `thc_arrange`.`bpm_appointment` a 448 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 449 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` 450 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 451 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 452 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 453 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 454 where a.del_flag = 0 and a.subsequent_visit = 7 455 AND g.id IN ( 456 SELECT DISTINCT g.id 457 FROM `thc_arrange`.`bpm_appointment` a 458 INNER JOIN `thc_sob`.`bpm_service_order` b on a.orderId = b.id 459 INNER JOIN `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id AND c.id = a.order_item_id 460 INNER JOIN `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 461 INNER JOIN `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 462 INNER JOIN `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id AND f.accountBillDetailID = e.id 463 INNER JOIN `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 464 INNER JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 465 WHERE 1=1 466 AND a.del_flag = 0 467 AND d.isDelete = 0 AND d.orderSource = 1 AND d.orderType = 3 AND d.returnFlag = 0 468 AND e.itemClass = 1 AND e.returnFlag is NULL 469 AND g.settlementType=2 AND g.`isDelete` = 0 AND g.returnFlag = 0 -- 门诊挂号 470 AND h.type = 1 and h.pay_state = 1 -- 支付成功 471 ) 472 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 473 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 474 ) t 475 where 1=1 476 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 477 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 478 group by t.deptId,t.doctorId 479 ) t7 480 on t0.deptId = t7.deptId and t0.doctorId = t7.doctorId 481 482 483 -- ------------ 484 -- 简易 485 -- ------------ 486 left join 487 ( 488 select count(1) as num,t.deptId, doctorId,t.deptName,t.create_time,t.doctorName from 489 ( 490 select 491 a.data_source, 492 a.is_appoint_resource, 493 a.subsequent_visit, 494 a.dept_id as deptId, 495 a.appointment_doctor_id as doctorId, 496 a.appointment_state, 497 a.dept_name as deptName, 498 a.create_time, 499 a.appointment_doctor_name as doctorName, 500 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 501 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 502 f.preFee, -- 原价 503 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 504 f.discount 505 from `thc_arrange`.`bpm_appointment` a 506 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 507 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` 508 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 509 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 510 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 511 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 512 where a.del_flag = 0 and a.subsequent_visit = 5 513 AND g.id IN ( 514 SELECT DISTINCT g.id 515 FROM `thc_arrange`.`bpm_appointment` a 516 INNER JOIN `thc_sob`.`bpm_service_order` b on a.orderId = b.id 517 INNER JOIN `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id AND c.id = a.order_item_id 518 INNER JOIN `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 519 INNER JOIN `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 520 INNER JOIN `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id AND f.accountBillDetailID = e.id 521 INNER JOIN `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 522 INNER JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 523 WHERE 1=1 524 AND a.del_flag = 0 525 AND d.isDelete = 0 AND d.orderSource = 1 AND d.orderType = 3 AND d.returnFlag = 0 526 AND e.itemClass = 1 AND e.returnFlag is NULL 527 AND g.settlementType=2 AND g.`isDelete` = 0 AND g.returnFlag = 0 -- 门诊挂号 528 AND h.type = 1 and h.pay_state = 1 -- 支付成功 529 ) 530 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 531 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 532 ) t 533 where 1=1 534 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 535 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 536 group by t.deptId,t.doctorId 537 ) t8 538 on t0.deptId = t8.deptId and t0.doctorId = t8.doctorId 539 540 541 542 -- ------------ 543 -- 转诊 544 -- ------------ 545 left join 546 ( 547 select count(1) as num,t.deptId, doctorId,t.deptName,t.create_time,t.doctorName from 548 ( 549 select 550 a.data_source, 551 a.is_appoint_resource, 552 a.subsequent_visit, 553 a.dept_id as deptId, 554 a.appointment_doctor_id as doctorId, 555 a.appointment_state, 556 a.dept_name as deptName, 557 a.create_time, 558 a.appointment_doctor_name as doctorName, 559 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 560 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 561 f.preFee, -- 原价 562 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 563 f.discount 564 from `thc_arrange`.`bpm_appointment` a 565 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 566 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` 567 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 568 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 569 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 570 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 571 where a.del_flag = 0 and a.subsequent_visit = 2 572 AND g.id IN ( 573 SELECT DISTINCT g.id 574 FROM `thc_arrange`.`bpm_appointment` a 575 INNER JOIN `thc_sob`.`bpm_service_order` b on a.orderId = b.id 576 INNER JOIN `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id AND c.id = a.order_item_id 577 INNER JOIN `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 578 INNER JOIN `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 579 INNER JOIN `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id AND f.accountBillDetailID = e.id 580 INNER JOIN `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 581 INNER JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 582 WHERE 1=1 583 AND a.del_flag = 0 584 AND d.isDelete = 0 AND d.orderSource = 1 AND d.orderType = 3 AND d.returnFlag = 0 585 AND e.itemClass = 1 AND e.returnFlag is NULL 586 AND g.settlementType=2 AND g.`isDelete` = 0 AND g.returnFlag = 0 -- 门诊挂号 587 AND h.type = 1 and h.pay_state = 1 -- 支付成功 588 ) 589 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 590 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 591 ) t 592 where 1=1 593 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 594 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 595 group by t.deptId,t.doctorId 596 ) t9 597 on t0.deptId = t9.deptId and t0.doctorId = t9.doctorId 598 599 -- ------------ 600 -- 疫苗 601 -- ------------ 602 left join 603 ( 604 select count(1) as num,t.deptId, doctorId,t.deptName,t.create_time,t.doctorName from 605 ( 606 select 607 a.data_source, 608 a.is_appoint_resource, 609 a.subsequent_visit, 610 a.dept_id as deptId, 611 a.appointment_doctor_id as doctorId, 612 a.appointment_state, 613 a.dept_name as deptName, 614 a.create_time, 615 a.appointment_doctor_name as doctorName, 616 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 617 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 618 f.preFee, -- 原价 619 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 620 f.discount 621 from `thc_arrange`.`bpm_appointment` a 622 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 623 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` 624 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 625 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 626 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 627 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 628 where a.del_flag = 0 and a.subsequent_visit = 2 629 AND g.id IN ( 630 SELECT DISTINCT g.id 631 FROM `thc_arrange`.`bpm_appointment` a 632 INNER JOIN `thc_sob`.`bpm_service_order` b on a.orderId = b.id 633 INNER JOIN `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id AND c.id = a.order_item_id 634 INNER JOIN `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 635 INNER JOIN `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 636 INNER JOIN `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id AND f.accountBillDetailID = e.id 637 INNER JOIN `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 638 INNER JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 639 WHERE 1=1 640 AND a.del_flag = 0 641 AND d.isDelete = 0 AND d.orderSource = 1 AND d.orderType = 3 AND d.returnFlag = 0 642 AND e.itemClass = 1 AND e.returnFlag is NULL 643 AND g.settlementType=2 AND g.`isDelete` = 0 AND g.returnFlag = 0 -- 门诊挂号 644 AND h.type = 1 and h.pay_state = 1 -- 支付成功 645 ) 646 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 647 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 648 ) t 649 where 1=1 650 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 651 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 652 group by t.deptId,t.doctorId 653 ) t10 654 on t0.deptId = t10.deptId and t0.doctorId = t10.doctorId 655 656 -- ------------ 657 -- 微信数 658 -- ------------ 659 left join 660 ( 661 select count(1) as num,t.deptId, doctorId,t.deptName,t.create_time,t.doctorName from 662 ( 663 select 664 a.data_source, 665 a.is_appoint_resource, 666 a.subsequent_visit, 667 a.dept_id as deptId, 668 a.appointment_doctor_id as doctorId, 669 a.appointment_state, 670 a.dept_name as deptName, 671 a.create_time, 672 a.appointment_doctor_name as doctorName, 673 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 674 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 675 f.preFee, -- 原价 676 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 677 f.discount 678 from `thc_arrange`.`bpm_appointment` a 679 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 680 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` 681 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 682 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 683 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 684 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 685 where a.del_flag = 0 and a.data_source = 2 686 AND g.id IN ( 687 SELECT DISTINCT g.id 688 FROM `thc_arrange`.`bpm_appointment` a 689 INNER JOIN `thc_sob`.`bpm_service_order` b on a.orderId = b.id 690 INNER JOIN `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id AND c.id = a.order_item_id 691 INNER JOIN `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 692 INNER JOIN `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 693 INNER JOIN `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id AND f.accountBillDetailID = e.id 694 INNER JOIN `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 695 INNER JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 696 WHERE 1=1 697 AND a.del_flag = 0 698 AND d.isDelete = 0 AND d.orderSource = 1 AND d.orderType = 3 AND d.returnFlag = 0 699 AND e.itemClass = 1 AND e.returnFlag is NULL 700 AND g.settlementType=2 AND g.`isDelete` = 0 AND g.returnFlag = 0 -- 门诊挂号 701 AND h.type = 1 and h.pay_state = 1 -- 支付成功 702 ) 703 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 704 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 705 ) t 706 where 1=1 707 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 708 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 709 group by t.deptId,t.doctorId 710 ) t11 711 on t0.deptId = t11.deptId and t0.doctorId = t11.doctorId 712 713 714 -- ------------ 715 -- 微信金额 716 -- ------------ 717 left join 718 ( 719 select round(sum(t.preFee),2) as num,t.deptId, doctorId,t.deptName,t.create_time,t.doctorName from 720 ( 721 select 722 a.data_source, 723 a.is_appoint_resource, 724 a.subsequent_visit, 725 a.dept_id as deptId, 726 a.appointment_doctor_id as doctorId, 727 a.appointment_state, 728 a.dept_name as deptName, 729 a.create_time, 730 a.appointment_doctor_name as doctorName, 731 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 732 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 733 f.preFee, -- 原价 734 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 735 f.discount 736 from `thc_arrange`.`bpm_appointment` a 737 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 738 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` 739 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 740 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 741 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 742 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 743 where a.del_flag = 0 and a.data_source = 2 744 AND g.id IN ( 745 SELECT DISTINCT g.id 746 FROM `thc_arrange`.`bpm_appointment` a 747 INNER JOIN `thc_sob`.`bpm_service_order` b on a.orderId = b.id 748 INNER JOIN `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id AND c.id = a.order_item_id 749 INNER JOIN `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 750 INNER JOIN `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 751 INNER JOIN `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id AND f.accountBillDetailID = e.id 752 INNER JOIN `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 753 INNER JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 754 WHERE 1=1 755 AND a.del_flag = 0 756 AND d.isDelete = 0 AND d.orderSource = 1 AND d.orderType = 3 AND d.returnFlag = 0 757 AND e.itemClass = 1 AND e.returnFlag is NULL 758 AND g.settlementType=2 AND g.`isDelete` = 0 AND g.returnFlag = 0 -- 门诊挂号 759 AND h.type = 1 and h.pay_state = 1 AND h.pay_method = 4 -- 微信支付成功 760 ) 761 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 762 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 763 ) t 764 where 1=1 765 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 766 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 767 group by t.deptId,t.doctorId 768 ) t12 769 on t0.deptId = t12.deptId and t0.doctorId = t12.doctorId 770 771 772 -- ------------ 773 -- 应收金额 t12 774 -- ------------ 775 left join 776 ( 777 select round(sum(t.preFee),2) as num,t.deptId, doctorId,t.deptName,t.create_time,t.doctorName from 778 ( 779 select 780 a.data_source, 781 a.is_appoint_resource, 782 a.subsequent_visit, 783 a.dept_id as deptId, 784 a.appointment_doctor_id as doctorId, 785 a.appointment_state, 786 a.dept_name as deptName, 787 a.create_time, 788 a.appointment_doctor_name as doctorName, 789 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 790 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 791 f.preFee, -- 原价 792 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 793 f.discount 794 from `thc_arrange`.`bpm_appointment` a 795 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 796 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` 797 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 798 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 799 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 800 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 801 where a.del_flag = 0 802 AND g.id IN ( 803 SELECT DISTINCT g.id 804 FROM `thc_arrange`.`bpm_appointment` a 805 INNER JOIN `thc_sob`.`bpm_service_order` b on a.orderId = b.id 806 INNER JOIN `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id AND c.id = a.order_item_id 807 INNER JOIN `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 808 INNER JOIN `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 809 INNER JOIN `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id AND f.accountBillDetailID = e.id 810 INNER JOIN `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 811 INNER JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 812 WHERE 1=1 813 AND a.del_flag = 0 814 AND d.isDelete = 0 AND d.orderSource = 1 AND d.orderType = 3 AND d.returnFlag = 0 815 AND e.itemClass = 1 AND e.returnFlag is NULL 816 AND g.settlementType=2 AND g.`isDelete` = 0 AND g.returnFlag = 0 -- 门诊挂号 817 AND h.type = 1 and h.pay_state = 1 -- 支付成功 818 ) 819 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 820 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 821 ) t 822 where 1=1 823 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 824 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 825 group by t.deptId,t.doctorId 826 ) t13 827 on t0.deptId = t13.deptId and t0.doctorId = t13.doctorId 828 829 830 831 832 833 -- ------------ 834 -- 实收金额 t13 835 -- ------------ 836 left join 837 ( 838 select round(sum(t.realFee),2) as num,t.deptId, doctorId,t.deptName,t.create_time,t.doctorName from 839 ( 840 select 841 a.data_source, 842 a.is_appoint_resource, 843 a.subsequent_visit, 844 a.dept_id as deptId, 845 a.appointment_doctor_id as doctorId, 846 a.appointment_state, 847 a.dept_name as deptName, 848 a.create_time, 849 a.appointment_doctor_name as doctorName, 850 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 851 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 852 f.preFee, 853 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 854 f.discount 855 from `thc_arrange`.`bpm_appointment` a 856 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 857 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` 858 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 859 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 860 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 861 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 862 where a.del_flag = 0 863 AND g.id IN ( 864 SELECT DISTINCT g.id 865 FROM `thc_arrange`.`bpm_appointment` a 866 INNER JOIN `thc_sob`.`bpm_service_order` b on a.orderId = b.id 867 INNER JOIN `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id AND c.id = a.order_item_id 868 INNER JOIN `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 869 INNER JOIN `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 870 INNER JOIN `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id AND f.accountBillDetailID = e.id 871 INNER JOIN `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 872 INNER JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 873 WHERE 1=1 874 AND a.del_flag = 0 875 AND d.isDelete = 0 AND d.orderSource = 1 AND d.orderType = 3 AND d.returnFlag = 0 876 AND e.itemClass = 1 AND e.returnFlag is NULL 877 AND g.settlementType=2 AND g.`isDelete` = 0 AND g.returnFlag = 0 -- 门诊挂号 878 AND h.type = 1 and h.pay_state = 1 -- 支付成功 879 ) 880 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 881 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 882 ) t 883 where 1=1 884 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 885 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 886 group by t.deptId,t.doctorId 887 ) t14 888 on t0.deptId = t14.deptId and t0.doctorId = t14.doctorId 889 890 891 order by t0.startTime asc
5.挂号按科室统计
1 select 2 t0.deptName, 3 t0.deptId, 4 t0.startTime, 5 t0.endTime, 6 IFNULL(t0.num,0) as num0, 7 IFNULL(t1.num,0) as num1, 8 IFNULL(t2.num,0) as num2, 9 IFNULL(t4.num,0) as num4, 10 IFNULL(t5.num,0) as num5, 11 IFNULL(t6.num,0) as num6, 12 IFNULL(t7.num,0) as num7, 13 IFNULL(t8.num,0) as num8, 14 IFNULL(t9.num,0) as num9, 15 IFNULL(t10.num,0) as num10, 16 IFNULL(t11.num,0) as num11, 17 IFNULL(t13.num,0) as num13, 18 IFNULL(t14.num,0) as num14, 19 IFNULL(t15.num,0) as num15, 20 IFNULL(t16.num,0) as num16, 21 IFNULL(t17.num,0) as num17, 22 IFNULL(t18.num,0) as num18, 23 IFNULL(t19.num,0) as num19, 24 IFNULL(t20.num,0) as num20, 25 IFNULL(t21.num,0) as num21, 26 IFNULL(t22.num,0) as num22 27 from 28 -- ------------ 29 -- 总挂号数 30 -- ------------ 31 ( 32 select t.startTime, t.endTime, count(1) as num,t.deptId, t.deptName,t.create_time from 33 ( 34 select 35 a.data_source, 36 a.is_appoint_resource, 37 a.subsequent_visit, 38 a.appointment_state, 39 a.dept_id as deptId, 40 41 a.dept_name as deptName, 42 a.create_time, 43 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 44 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 45 f.preFee, -- 原价 46 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 47 f.discount 48 from `thc_arrange`.`bpm_appointment` a 49 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 50 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 51 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 52 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 53 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 54 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 55 where 1=1 and a.del_flag = 0 56 and g.id in ( 57 SELECT DISTINCT g.id 58 from `thc_arrange`.`bpm_appointment` a 59 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 60 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 61 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 62 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 63 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 64 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 65 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 66 where 1=1 67 and a.del_flag = 0 68 and d.isDelete = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 69 and e.itemClass = 1 and e.returnFlag is NULL 70 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 71 and h.type = 1 and h.pay_state = 1 72 ) 73 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 74 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 75 ) t 76 where 1=1 77 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 78 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 79 group by t.deptId 80 ) t0 81 82 -- ------------ 83 -- 退号 84 -- ------------ 85 left join 86 ( 87 select count(1) as num,t.deptId, t.deptName,t.create_time from 88 ( 89 select 90 a.data_source, 91 a.is_appoint_resource, 92 a.subsequent_visit, 93 a.dept_id as deptId, 94 a.appointment_state, 95 a.dept_name as deptName, 96 a.create_time, 97 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 98 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 99 f.preFee, -- 原价 100 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 101 f.discount 102 from `thc_arrange`.`bpm_appointment` a 103 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 104 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 105 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 106 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 107 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 108 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 109 where 1=1 and a.del_flag = 0 AND a.appointment_state = -1 110 and g.id in ( 111 SELECT DISTINCT g.id 112 from `thc_arrange`.`bpm_appointment` a 113 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 114 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 115 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 116 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 117 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 118 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 119 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 120 where 1=1 121 and a.del_flag = 0 122 and d.isDelete = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 123 and e.itemClass = 1 and e.returnFlag is NULL 124 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 125 and h.type = 1 and h.pay_state = 1 126 ) 127 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 128 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 129 ) t 130 where 1=1 131 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 132 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 133 group by t.deptId 134 ) t1 135 on t0.deptId = t1.deptId 136 137 -- ------------ 138 -- 实际挂号数 = 初诊数 + 复诊数 + 转诊数 139 -- ------------ 140 left join 141 ( 142 select count(1) as num,t.deptId, t.deptName,t.create_time from 143 ( 144 select 145 a.data_source, 146 a.is_appoint_resource, 147 a.subsequent_visit, 148 a.dept_id as deptId, 149 150 a.appointment_state, 151 a.dept_name as deptName, 152 a.create_time, 153 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 154 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 155 f.preFee, -- 原价 156 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 157 f.discount 158 from `thc_arrange`.`bpm_appointment` a 159 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 160 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 161 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 162 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 163 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 164 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 165 where 1=1 and a.del_flag = 0 AND a.subsequent_visit in (0,1,2) 166 and g.id in ( 167 SELECT DISTINCT g.id 168 from `thc_arrange`.`bpm_appointment` a 169 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 170 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 171 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 172 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 173 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 174 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 175 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 176 where 1=1 177 and a.del_flag = 0 178 and d.isDelete = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 179 and e.itemClass = 1 and e.returnFlag is NULL 180 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 181 and h.type = 1 and h.pay_state = 1 182 ) 183 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 184 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 185 ) t 186 where 1=1 187 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 188 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 189 group by t.deptId 190 ) t2 191 on t0.deptId = t2.deptId 192 193 194 -- ------------ 195 -- 初诊 196 -- ------------ 197 left join 198 ( 199 select count(1) as num,t.deptId, t.deptName,t.create_time from 200 ( 201 select 202 a.data_source, 203 a.is_appoint_resource, 204 a.subsequent_visit, 205 a.dept_id as deptId, 206 207 a.appointment_state, 208 a.dept_name as deptName, 209 a.create_time, 210 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 211 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 212 f.preFee, -- 原价 213 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 214 f.discount 215 from `thc_arrange`.`bpm_appointment` a 216 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 217 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 218 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 219 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 220 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 221 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 222 where 1=1 and a.del_flag = 0 AND a.subsequent_visit = 0 223 and g.id in ( 224 SELECT DISTINCT g.id 225 from `thc_arrange`.`bpm_appointment` a 226 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 227 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 228 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 229 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 230 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 231 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 232 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 233 where 1=1 234 and a.del_flag = 0 235 and d.isDelete = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 236 and e.itemClass = 1 and e.returnFlag is NULL 237 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 238 and h.type = 1 and h.pay_state = 1 239 ) 240 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 241 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 242 ) t 243 where 1=1 244 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 245 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 246 group by t.deptId 247 ) t4 248 on t0.deptId = t4.deptId 249 250 -- ------------ 251 -- 复诊 252 -- ------------ 253 left join 254 ( 255 select count(1) as num,t.deptId, t.deptName,t.create_time from 256 ( 257 select 258 a.data_source, 259 a.is_appoint_resource, 260 a.subsequent_visit, 261 a.dept_id as deptId, 262 263 a.appointment_state, 264 a.dept_name as deptName, 265 a.create_time, 266 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 267 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 268 f.preFee, -- 原价 269 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 270 f.discount 271 from `thc_arrange`.`bpm_appointment` a 272 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 273 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 274 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 275 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 276 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 277 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 278 where 1=1 and a.del_flag = 0 AND a.subsequent_visit = 1 279 and g.id in ( 280 SELECT DISTINCT g.id 281 from `thc_arrange`.`bpm_appointment` a 282 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 283 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 284 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 285 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 286 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 287 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 288 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 289 where 1=1 290 and a.del_flag = 0 291 and d.isDelete = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 292 and e.itemClass = 1 and e.returnFlag is NULL 293 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 294 and h.type = 1 and h.pay_state = 1 295 ) 296 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 297 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 298 ) t 299 where 1=1 300 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 301 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 302 group by t.deptId 303 ) t5 304 on t0.deptId = t5.deptId 305 306 307 308 -- ------------ 309 -- 体检 310 -- ------------ 311 left join 312 ( 313 select count(1) as num,t.deptId, t.deptName,t.create_time from 314 ( 315 select 316 a.data_source, 317 a.is_appoint_resource, 318 a.subsequent_visit, 319 a.dept_id as deptId, 320 321 a.appointment_state, 322 a.dept_name as deptName, 323 a.create_time, 324 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 325 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 326 f.preFee, -- 原价 327 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 328 f.discount 329 from `thc_arrange`.`bpm_appointment` a 330 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 331 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 332 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 333 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 334 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 335 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 336 where 1=1 and a.del_flag = 0 AND a.subsequent_visit = 4 337 and g.id in ( 338 SELECT DISTINCT g.id 339 from `thc_arrange`.`bpm_appointment` a 340 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 341 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 342 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 343 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 344 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 345 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 346 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 347 where 1=1 348 and a.del_flag = 0 349 and d.isDelete = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 350 and e.itemClass = 1 and e.returnFlag is NULL 351 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 352 and h.type = 1 and h.pay_state = 1 353 ) 354 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 355 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 356 ) t 357 where 1=1 358 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 359 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 360 group by t.deptId 361 ) t6 362 on t0.deptId = t6.deptId 363 364 -- ------------ 365 -- 团队 366 -- ------------ 367 left join 368 ( 369 select count(1) as num,t.deptId, t.deptName,t.create_time from 370 ( 371 select 372 a.data_source, 373 a.is_appoint_resource, 374 a.subsequent_visit, 375 a.dept_id as deptId, 376 377 a.appointment_state, 378 a.dept_name as deptName, 379 a.create_time, 380 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 381 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 382 f.preFee, -- 原价 383 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 384 f.discount 385 from `thc_arrange`.`bpm_appointment` a 386 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 387 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 388 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 389 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 390 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 391 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 392 where 1=1 and a.del_flag = 0 AND a.subsequent_visit = 7 393 and g.id in ( 394 SELECT DISTINCT g.id 395 from `thc_arrange`.`bpm_appointment` a 396 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 397 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 398 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 399 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 400 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 401 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 402 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 403 where 1=1 404 and a.del_flag = 0 405 and d.isDelete = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 406 and e.itemClass = 1 and e.returnFlag is NULL 407 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 408 and h.type = 1 and h.pay_state = 1 409 ) 410 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 411 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 412 ) t 413 where 1=1 414 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 415 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 416 group by t.deptId 417 ) t7 418 on t0.deptId = t7.deptId 419 420 421 -- ------------ 422 -- 简易 423 -- ------------ 424 left join 425 ( 426 select count(1) as num,t.deptId, t.deptName,t.create_time from 427 ( 428 select 429 a.data_source, 430 a.is_appoint_resource, 431 a.subsequent_visit, 432 a.dept_id as deptId, 433 434 a.appointment_state, 435 a.dept_name as deptName, 436 a.create_time, 437 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 438 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 439 f.preFee, -- 原价 440 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 441 f.discount 442 from `thc_arrange`.`bpm_appointment` a 443 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 444 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 445 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 446 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 447 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 448 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 449 where 1=1 and a.del_flag = 0 AND a.subsequent_visit = 5 450 and g.id in ( 451 SELECT DISTINCT g.id 452 from `thc_arrange`.`bpm_appointment` a 453 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 454 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 455 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 456 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 457 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 458 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 459 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 460 where 1=1 461 and a.del_flag = 0 462 and d.isDelete = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 463 and e.itemClass = 1 and e.returnFlag is NULL 464 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 465 and h.type = 1 and h.pay_state = 1 466 ) 467 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 468 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 469 ) t 470 where 1=1 471 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 472 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 473 group by t.deptId 474 ) t8 475 on t0.deptId = t8.deptId 476 477 478 479 -- ------------ 480 -- 转诊 481 -- ------------ 482 left join 483 ( 484 select count(1) as num,t.deptId, t.deptName,t.create_time from 485 ( 486 select 487 a.data_source, 488 a.is_appoint_resource, 489 a.subsequent_visit, 490 a.dept_id as deptId, 491 492 a.appointment_state, 493 a.dept_name as deptName, 494 a.create_time, 495 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 496 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 497 f.preFee, -- 原价 498 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 499 f.discount 500 from `thc_arrange`.`bpm_appointment` a 501 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 502 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 503 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 504 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 505 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 506 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 507 where 1=1 and a.del_flag = 0 AND a.subsequent_visit = 2 508 and g.id in ( 509 SELECT DISTINCT g.id 510 from `thc_arrange`.`bpm_appointment` a 511 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 512 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 513 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 514 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 515 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 516 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 517 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 518 where 1=1 519 and a.del_flag = 0 520 and d.isDelete = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 521 and e.itemClass = 1 and e.returnFlag is NULL 522 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 523 and h.type = 1 and h.pay_state = 1 524 ) 525 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 526 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 527 ) t 528 where 1=1 529 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 530 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 531 group by t.deptId 532 ) t9 533 on t0.deptId = t9.deptId 534 535 -- ------------ 536 -- 疫苗 537 -- ------------ 538 left join 539 ( 540 select count(1) as num,t.deptId, t.deptName,t.create_time from 541 ( 542 select 543 a.data_source, 544 a.is_appoint_resource, 545 a.subsequent_visit, 546 a.dept_id as deptId, 547 548 a.appointment_state, 549 a.dept_name as deptName, 550 a.create_time, 551 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 552 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 553 f.preFee, -- 原价 554 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 555 f.discount 556 from `thc_arrange`.`bpm_appointment` a 557 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 558 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 559 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 560 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 561 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 562 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 563 where 1=1 and a.del_flag = 0 AND a.subsequent_visit = 2 564 and g.id in ( 565 SELECT DISTINCT g.id 566 from `thc_arrange`.`bpm_appointment` a 567 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 568 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 569 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 570 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 571 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 572 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 573 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 574 where 1=1 575 and a.del_flag = 0 576 and d.isDelete = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 577 and e.itemClass = 1 and e.returnFlag is NULL 578 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 579 and h.type = 1 and h.pay_state = 1 580 ) 581 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 582 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 583 ) t 584 where 1=1 585 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 586 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 587 group by t.deptId 588 ) t10 589 on t0.deptId = t10.deptId 590 591 -- ------------ 592 -- t11 微信数 593 -- ------------ 594 left join 595 ( 596 select count(1) as num,t.deptId, t.deptName,t.create_time from 597 ( 598 select 599 a.data_source, 600 a.is_appoint_resource, 601 a.subsequent_visit, 602 a.dept_id as deptId, 603 604 a.appointment_state, 605 a.dept_name as deptName, 606 a.create_time, 607 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 608 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 609 f.preFee, -- 原价 610 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 611 f.discount 612 from `thc_arrange`.`bpm_appointment` a 613 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 614 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 615 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 616 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 617 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 618 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 619 where 1=1 and a.del_flag = 0 AND a.data_source = 2 620 and g.id in ( 621 SELECT DISTINCT g.id 622 from `thc_arrange`.`bpm_appointment` a 623 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 624 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 625 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 626 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 627 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 628 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 629 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 630 where 1=1 631 and a.del_flag = 0 632 and d.isDelete = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 633 and e.itemClass = 1 and e.returnFlag is NULL 634 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 635 and h.type = 1 and h.pay_state = 1 636 ) 637 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 638 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 639 ) t 640 where 1=1 641 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 642 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 643 group by t.deptId 644 ) t11 645 on t0.deptId = t11.deptId 646 647 648 649 650 -- ------------ 651 -- 挂号费 t13 652 -- ------------ 653 left join 654 ( 655 select round(sum(t.preFee),2) as num,t.deptId, t.deptName,t.create_time from 656 ( 657 select 658 a.data_source, 659 a.is_appoint_resource, 660 a.subsequent_visit, 661 a.dept_id as deptId, 662 663 a.appointment_state, 664 a.dept_name as deptName, 665 a.create_time, 666 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 667 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 668 f.preFee, -- 原价 669 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 670 f.discount 671 from `thc_arrange`.`bpm_appointment` a 672 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 673 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 674 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 675 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 676 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 677 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 678 where 1=1 and a.del_flag = 0 679 and g.id in ( 680 SELECT DISTINCT g.id 681 from `thc_arrange`.`bpm_appointment` a 682 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 683 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 684 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 685 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 686 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 687 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 688 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 689 where 1=1 690 and a.del_flag = 0 691 and d.isDelete = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 692 and e.itemClass = 1 and e.returnFlag is NULL 693 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 694 and h.type = 1 and h.pay_state = 1 695 ) 696 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 697 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 698 ) t 699 where 1=1 700 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 701 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 702 group by t.deptId 703 ) t13 704 on t0.deptId = t13.deptId 705 706 707 708 -- ------------ 709 -- 急诊费 t14 710 -- ------------ 711 left join 712 ( 713 select round(sum(t.preFee),2) as num,t.deptId, t.deptName,t.create_time from 714 ( 715 select 716 a.data_source, 717 a.is_appoint_resource, 718 a.subsequent_visit, 719 a.dept_id as deptId, 720 721 a.appointment_state, 722 a.dept_name as deptName, 723 a.create_time, 724 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 725 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 726 f.preFee, -- 原价 727 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 728 f.discount 729 from `thc_arrange`.`bpm_appointment` a 730 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id and b.del_flag = 0 731 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` AND c.del_flag = 0 732 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 733 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.isDelete = 0 734 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0 735 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号 736 where 1=1 and a.del_flag = 0 AND e.itemCode = 'jiajifei' -- 加急费：急诊费 737 and g.id in ( 738 SELECT DISTINCT g.id 739 from `thc_arrange`.`bpm_appointment` a 740 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 741 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 742 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 743 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 744 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 745 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 746 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 747 where 1=1 748 and a.del_flag = 0 749 and d.isDelete = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 750 and e.itemClass = 1 and e.returnFlag is NULL 751 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 752 and h.type = 1 and h.pay_state = 1 753 ) 754 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 755 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 756 ) t 757 where 1=1 758 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 759 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 760 group by t.deptId 761 ) t14 762 on t0.deptId = t14.deptId 763 764 765 766 767 -- ------------ 768 -- 工本费 t15 769 -- ------------ 770 left join 771 ( 772 select round(sum(t.preFee),2) as num,t.deptId, t.deptName,t.create_time from 773 ( 774 select 775 a.data_source, 776 a.is_appoint_resource, 777 a.subsequent_visit, 778 a.dept_id as deptId, 779 780 a.appointment_state, 781 a.dept_name as deptName, 782 a.create_time, 783 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 784 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 785 f.preFee, -- 原价 786 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 787 f.discount 788 from `thc_arrange`.`bpm_appointment` a 789 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 790 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 791 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 792 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 793 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 794 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 795 where 1=1 and a.del_flag = 0 AND e.itemCode = 'bingliben' -- 病历本：工本费 796 and g.id in ( 797 SELECT DISTINCT g.id 798 from `thc_arrange`.`bpm_appointment` a 799 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 800 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 801 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 802 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 803 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 804 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 805 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 806 where 1=1 807 and a.del_flag = 0 808 and d.isDelete = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 809 and e.itemClass = 1 and e.returnFlag is NULL 810 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 811 and h.type = 1 and h.pay_state = 1 812 ) 813 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 814 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 815 ) t 816 where 1=1 817 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 818 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 819 ) t15 820 on t0.deptId = t15.deptId 821 822 -- ------------ 823 -- 卡费 t16 824 -- ------------ 825 left join 826 ( 827 select round(sum(t.preFee),2) as num,t.deptId, t.deptName,t.create_time from 828 ( 829 select 830 a.data_source, 831 a.is_appoint_resource, 832 a.subsequent_visit, 833 a.dept_id as deptId, 834 835 a.appointment_state, 836 a.dept_name as deptName, 837 a.create_time, 838 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 839 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 840 f.preFee, -- 原价 841 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 842 f.discount 843 from `thc_arrange`.`bpm_appointment` a 844 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 845 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 846 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 847 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 848 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 849 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 850 where 1=1 and a.del_flag = 0 AND e.itemCode = 'jiuzhenka' -- 就诊卡：卡费 851 and g.id in ( 852 SELECT DISTINCT g.id 853 from `thc_arrange`.`bpm_appointment` a 854 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 855 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 856 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 857 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 858 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 859 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 860 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 861 where 1=1 862 and a.del_flag = 0 863 and d.isDelete = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 864 and e.itemClass = 1 and e.returnFlag is NULL 865 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 866 and h.type = 1 and h.pay_state = 1 867 ) 868 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 869 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 870 ) t 871 where 1=1 872 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 873 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 874 group by t.deptId 875 ) t16 876 on t0.deptId = t16.deptId 877 878 879 880 881 -- ------------ 882 -- 现金 t17 883 -- ------------ 884 left join 885 ( 886 select round(sum(t.preFee),2) as num,t.deptId, t.deptName,t.create_time from 887 ( 888 select 889 a.data_source, 890 a.is_appoint_resource, 891 a.subsequent_visit, 892 a.dept_id as deptId, 893 894 a.appointment_state, 895 a.dept_name as deptName, 896 a.create_time, 897 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 898 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 899 f.preFee, -- 原价 900 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 901 f.discount, 902 h.flow_fee, 903 h.`charge`, 904 h.`pay_method` 905 from `thc_arrange`.`bpm_appointment` a 906 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 907 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 908 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 909 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 910 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 911 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 912 inner join `thc_rcm`.`pay_trade_log` h on h.`settlement_id` = g.id and h.type = 1 913 where 1=1 and a.del_flag = 0 -- 门诊 914 and h.type = 1 and h.pay_state = 1 and h.pay_method = 1 -- 银行卡 915 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 916 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 917 ) t 918 where 1=1 919 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 920 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 921 group by t.deptId 922 ) t17 923 on t0.deptId = t17.deptId 924 925 926 -- ------------ 927 -- 银行卡 t18 928 -- ------------ 929 left join 930 ( 931 select round(sum(t.preFee),2) as num,t.deptId, t.deptName,t.create_time from 932 ( 933 select 934 a.data_source, 935 a.is_appoint_resource, 936 a.subsequent_visit, 937 a.dept_id as deptId, 938 939 a.appointment_state, 940 a.dept_name as deptName, 941 a.create_time, 942 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 943 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 944 f.preFee, -- 原价 945 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 946 f.discount, 947 h.flow_fee, 948 h.`charge`, 949 h.`pay_method` 950 from `thc_arrange`.`bpm_appointment` a 951 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 952 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 953 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 954 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 955 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 956 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 957 inner join `thc_rcm`.`pay_trade_log` h on h.`settlement_id` = g.id and h.type = 1 958 where 1=1 and a.del_flag = 0 959 and h.type = 1 and h.pay_state = 1 and h.pay_method = 2 -- 银行卡 960 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 961 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 962 ) t 963 where 1=1 964 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 965 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 966 group by t.deptId 967 ) t18 968 on t0.deptId = t18.deptId 969 970 971 972 -- ------------ 973 -- 预检 t19 974 -- ------------ 975 left join 976 ( 977 select round(sum(t.preFee),2) as num,t.deptId, t.deptName,t.create_time from 978 ( 979 select 980 a.data_source, 981 a.is_appoint_resource, 982 a.subsequent_visit, 983 a.dept_id as deptId, 984 985 a.appointment_state, 986 a.dept_name as deptName, 987 a.create_time, 988 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 989 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 990 f.preFee, -- 原价 991 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 992 f.discount 993 from `thc_arrange`.`bpm_appointment` a 994 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 995 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 996 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 997 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 998 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 999 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 1000 1001 where 1=1 and a.del_flag = 0 and e.itemCode = 'yujian' -- 预检：预存款 1002 and g.id in ( 1003 SELECT DISTINCT g.id 1004 from `thc_arrange`.`bpm_appointment` a 1005 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 1006 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 1007 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 1008 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 1009 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 1010 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 1011 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 1012 where 1=1 1013 and a.del_flag = 0 1014 and d.isDelete = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 1015 and e.itemClass = 1 and e.returnFlag is NULL 1016 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 1017 and h.type = 1 and h.pay_state = 1 1018 ) 1019 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 1020 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 1021 ) t 1022 where 1=1 1023 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 1024 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 1025 group by t.deptId 1026 ) t19 1027 on t0.deptId = t19.deptId 1028 1029 1030 1031 -- ------------ 1032 -- 微信金额t20 1033 -- ------------ 1034 left join 1035 ( 1036 select round(sum(t.preFee),2) as num,t.deptId, t.deptName,t.create_time from 1037 ( 1038 select 1039 a.data_source, 1040 a.is_appoint_resource, 1041 a.subsequent_visit, 1042 a.dept_id as deptId, 1043 1044 a.appointment_state, 1045 a.dept_name as deptName, 1046 a.create_time, 1047 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 1048 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 1049 f.preFee, -- 原价 1050 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 1051 f.discount 1052 from `thc_arrange`.`bpm_appointment` a 1053 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 1054 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 1055 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 1056 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 1057 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 1058 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 1059 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 1060 where 1=1 and a.del_flag = 0 and a.data_source = 2 1061 and h.type = 1 and h.pay_state = 1 and h.pay_method=4 1062 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 1063 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 1064 ) t 1065 where 1=1 1066 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 1067 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 1068 group by t.deptId 1069 ) t20 1070 on t0.deptId = t20.deptId 1071 1072 1073 -- ------------ 1074 -- 应收金额 t21 1075 -- ------------ 1076 left join 1077 ( 1078 select round(sum(t.preFee),2) as num,t.deptId, t.deptName,t.create_time from 1079 ( 1080 select 1081 a.data_source, 1082 a.is_appoint_resource, 1083 a.subsequent_visit, 1084 a.dept_id as deptId, 1085 1086 a.appointment_state, 1087 a.dept_name as deptName, 1088 a.create_time, 1089 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 1090 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 1091 f.preFee, -- 原价 1092 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 1093 f.discount 1094 from `thc_arrange`.`bpm_appointment` a 1095 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 1096 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 1097 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 1098 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 1099 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 1100 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 1101 where 1=1 and a.del_flag = 0 1102 and g.id in ( 1103 SELECT DISTINCT g.id 1104 from `thc_arrange`.`bpm_appointment` a 1105 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 1106 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 1107 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 1108 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 1109 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 1110 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 1111 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 1112 where 1=1 1113 and a.del_flag = 0 1114 and d.isDelete = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 1115 and e.itemClass = 1 and e.returnFlag is NULL 1116 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 1117 and h.type = 1 and h.pay_state = 1 1118 ) 1119 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 1120 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 1121 ) t 1122 where 1=1 1123 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 1124 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 1125 group by t.deptId 1126 ) t21 1127 on t0.deptId = t21.deptId 1128 1129 1130 1131 1132 1133 -- ------------ 1134 -- 实收金额 t22 1135 -- ------------ 1136 left join 1137 ( 1138 select round(sum(t.realFee),2) as num,t.deptId,t.deptName,t.create_time from 1139 ( 1140 select 1141 a.data_source, 1142 a.is_appoint_resource, 1143 a.subsequent_visit, 1144 a.dept_id as deptId, 1145 1146 a.appointment_state, 1147 a.dept_name as deptName, 1148 a.create_time, 1149 CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, 1150 CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime, 1151 f.preFee, 1152 (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee, 1153 f.discount 1154 from `thc_arrange`.`bpm_appointment` a 1155 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 1156 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` 1157 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 1158 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 1159 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 1160 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 1161 where 1=1 and a.del_flag = 0 1162 and g.id in ( 1163 SELECT DISTINCT g.id 1164 from `thc_arrange`.`bpm_appointment` a 1165 inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id 1166 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.order_item_id 1167 inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id 1168 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId 1169 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id 1170 inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID 1171 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id 1172 where 1=1 1173 and a.del_flag = 0 1174 and d.isDelete = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 1175 and e.itemClass = 1 and e.returnFlag is NULL 1176 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 1177 and h.type = 1 and h.pay_state = 1 1178 ) 1179 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} 1180 ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} 1181 ) t 1182 where 1=1 1183 ${if(len(startTime) == 0,"","AND t.create_time >= '" + startTime + "'")} 1184 ${if(len(endTime) == 0,"","AND t.create_time <= '" + endTime + "'")} 1185 group by t.deptId 1186 ) t22 1187 on t0.deptId = t22.deptId 1188 1189 1190 order by t0.startTime asc
