select t0.deptName, t0.doctorName, IFNULL(t0.num,0) as num0, IFNULL(t1.num,0) as num1, -- IFNULL(t2.num,0) as num2, (IFNULL(t4.num,0) + IFNULL(t5.num,0) + IFNULL(t9.num,0)) as num2, -- 实际挂号数 = 初诊数 + 复诊数 + 转诊数 IFNULL(t3.num,0) as num3, IFNULL(t4.num,0) as num4, IFNULL(t5.num,0) as num5, IFNULL(t6.num,0) as num6, IFNULL(t8.num,0) as num8, IFNULL(t9.num,0) as num9, IFNULL(t10.num,0) as num10, IFNULL(t11.num,0) as num11, IFNULL(t12.num,0) as num12, IFNULL(t12.num,0) as num121, IFNULL(t12.num,0) as num122, IFNULL(t12.num,0) as num123, IFNULL(t13.num,0) as num13, IFNULL(t14.num,0) as num14 from -- ------------ -- 总挂号数 -- ------------ ( select t.startTime, count(1) as num,t.deptId, doctorId,t.deptName,t.doctorName from ( select a.dept_id as deptId, IFNULL(a.appointment_doctor_id,'无') as doctorId, h.name as deptName, a.appointment_doctor_name as doctorName, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime 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 and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 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 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 left join `thc_sob`.`bpm_serv_provider` h on a.dept_id = h.id where a.del_flag = 0 and g.payStatus = 2 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} ) t where 1=1 ${if(len(startTime) == 0,"","AND t.startTime >= '" + startTime + "'")} ${if(len(endTime) == 0,"","AND t.startTime <= '" + endTime + "'")} group by t.deptId,t.doctorId ) t0 -- ------------ -- 退号 -- ------------ left join ( select count(1) as num,t.deptId, t.doctorId from ( select a.dept_id as deptId, IFNULL(a.appointment_doctor_id,'无') as doctorId, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime 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 and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 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 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 where 1=1 and a.del_flag = 0 and g.payStatus = 2 and a.appointment_state = -1 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} ) t where 1=1 ${if(len(startTime) == 0,"","AND t.startTime >= '" + startTime + "'")} ${if(len(endTime) == 0,"","AND t.startTime <= '" + endTime + "'")} group by t.deptId,t.doctorId ) t1 on t0.deptId = t1.deptId and t0.doctorId = t1.doctorId -- ------------ -- 实际挂号数 = 初诊数 + 复诊数 + 转诊数 -- ------------ left join ( select count(1) as num,t.deptId, t.doctorId from ( select a.dept_id as deptId, IFNULL(a.appointment_doctor_id,'无') as doctorId, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime 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 and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 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 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 where a.del_flag = 0 and a.subsequent_visit in (0,1,2) and g.payStatus = 2 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} ) t where 1=1 ${if(len(startTime) == 0,"","AND t.startTime >= '" + startTime + "'")} ${if(len(endTime) == 0,"","AND t.startTime <= '" + endTime + "'")} group by t.deptId,t.doctorId ) t2 on t0.deptId = t2.deptId and t0.doctorId = t2.doctorId -- ------------ -- 指定 -- ------------ left join ( select count(1) as num,t.deptId, t.doctorId from ( select a.dept_id as deptId, IFNULL(a.appointment_doctor_id,'无') as doctorId, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime 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 and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 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 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 where a.del_flag = 0 and a.is_appoint_resource = 1 and g.payStatus = 2 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} ) t where 1=1 ${if(len(startTime) == 0,"","AND t.startTime >= '" + startTime + "'")} ${if(len(endTime) == 0,"","AND t.startTime <= '" + endTime + "'")} group by t.deptId,t.doctorId ) t3 on t0.deptId = t3.deptId and t0.doctorId = t3.doctorId -- ------------ -- 初诊 -- ------------ left join ( select count(1) as num,t.deptId, doctorId from ( select a.dept_id as deptId, IFNULL(a.appointment_doctor_id,'无') as doctorId, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime 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 and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 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 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 where a.del_flag = 0 and a.subsequent_visit = 0 and g.payStatus = 2 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} ) t where 1=1 ${if(len(startTime) == 0,"","AND t.startTime >= '" + startTime + "'")} ${if(len(endTime) == 0,"","AND t.startTime <= '" + endTime + "'")} group by t.deptId,t.doctorId ) t4 on t0.deptId = t4.deptId and t0.doctorId = t4.doctorId -- ------------ -- 复诊 -- ------------ left join ( select count(1) as num,t.deptId, t.doctorId from ( select a.dept_id as deptId, IFNULL(a.appointment_doctor_id,'无') as doctorId, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime 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 and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 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 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 where a.del_flag = 0 and a.subsequent_visit = 1 and g.payStatus = 2 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} ) t where 1=1 ${if(len(startTime) == 0,"","AND t.startTime >= '" + startTime + "'")} ${if(len(endTime) == 0,"","AND t.startTime <= '" + endTime + "'")} group by t.deptId,t.doctorId ) t5 on t0.deptId = t5.deptId and t0.doctorId = t5.doctorId -- ------------ -- 体检 -- ------------ left join ( select count(1) as num,t.deptId, t.doctorId from ( select a.dept_id as deptId, IFNULL(a.appointment_doctor_id,'无') as doctorId, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime 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 and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 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 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 where a.del_flag = 0 and a.subsequent_visit = 4 and g.payStatus = 2 ) t where 1=1 ${if(len(startTime) == 0,"","AND t.startTime >= '" + startTime + "'")} ${if(len(endTime) == 0,"","AND t.startTime <= '" + endTime + "'")} group by t.deptId,t.doctorId ) t6 on t0.deptId = t6.deptId and t0.doctorId = t6.doctorId -- ------------ -- 简易 -- ------------ left join ( select count(1) as num,t.deptId, doctorId from ( select a.dept_id as deptId, IFNULL(a.appointment_doctor_id,'无') as doctorId, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime 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 and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 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 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 where a.del_flag = 0 and a.subsequent_visit = 5 and g.payStatus = 2 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} ) t where 1=1 ${if(len(startTime) == 0,"","AND t.startTime >= '" + startTime + "'")} ${if(len(endTime) == 0,"","AND t.startTime <= '" + endTime + "'")} group by t.deptId,t.doctorId ) t8 on t0.deptId = t8.deptId and t0.doctorId = t8.doctorId -- ------------ -- 转诊 -- ------------ left join ( select count(1) as num,t.deptId, doctorId from ( select a.dept_id as deptId, IFNULL(a.appointment_doctor_id,'无') as doctorId, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime 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 and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 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 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 where a.del_flag = 0 and a.subsequent_visit = 2 and g.payStatus = 2 ) t where 1=1 ${if(len(startTime) == 0,"","AND t.startTime >= '" + startTime + "'")} ${if(len(endTime) == 0,"","AND t.startTime <= '" + endTime + "'")} group by t.deptId,t.doctorId ) t9 on t0.deptId = t9.deptId and t0.doctorId = t9.doctorId -- ------------ -- 疫苗 -- ------------ left join ( select count(1) as num,t.deptId, doctorId from ( select a.dept_id as deptId, IFNULL(a.appointment_doctor_id,'无') as doctorId, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime 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 and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 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 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 where a.del_flag = 0 and a.subsequent_visit = 2 and g.payStatus = 2 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} ) t where 1=1 ${if(len(startTime) == 0,"","AND t.startTime >= '" + startTime + "'")} ${if(len(endTime) == 0,"","AND t.startTime <= '" + endTime + "'")} group by t.deptId,t.doctorId ) t10 on t0.deptId = t10.deptId and t0.doctorId = t10.doctorId -- ------------ -- 微信数 -- ------------ left join ( select count(1) as num,t.deptId, doctorId from ( select a.dept_id as deptId, IFNULL(a.appointment_doctor_id,'无') as doctorId, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime 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 and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL 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 and g.settlementType=2 and g.`isDelete` = 0 and g.returnFlag = 0 -- 门诊挂号 where a.del_flag = 0 and a.data_source = 2 and g.payStatus = 2 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} ) t where 1=1 ${if(len(startTime) == 0,"","AND t.startTime >= '" + startTime + "'")} ${if(len(endTime) == 0,"","AND t.startTime <= '" + endTime + "'")} group by t.deptId,t.doctorId ) t11 on t0.deptId = t11.deptId and t0.doctorId = t11.doctorId -- ------------ -- 现金 -- ------------ left join ( select round(sum(t.realFee),2) as num,t.deptId, doctorId from ( select a.dept_id as deptId, IFNULL(a.appointment_doctor_id,'无') as doctorId, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee 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 and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 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 and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id where 1=1 and a.del_flag = 0 and a.data_source = 2 and h.type = 1 and h.pay_state = 1 and h.pay_method=1 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} ) t where 1=1 ${if(len(startTime) == 0,"","AND t.startTime >= '" + startTime + "'")} ${if(len(endTime) == 0,"","AND t.startTime <= '" + endTime + "'")} group by t.deptId,t.doctorId ) t121 on t0.deptId = t121.deptId and t0.doctorId = t121.doctorId -- ------------ -- 银行卡 -- ------------ left join ( select round(sum(t.realFee),2) as num,t.deptId, doctorId from ( select a.dept_id as deptId, IFNULL(a.appointment_doctor_id,'无') as doctorId, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee 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 and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 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 and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id where 1=1 and a.del_flag = 0 and a.data_source = 2 and h.type = 1 and h.pay_state = 1 and h.pay_method=2 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} ) t where 1=1 ${if(len(startTime) == 0,"","AND t.startTime >= '" + startTime + "'")} ${if(len(endTime) == 0,"","AND t.startTime <= '" + endTime + "'")} group by t.deptId,t.doctorId ) t122 on t0.deptId = t122.deptId and t0.doctorId = t122.doctorId -- ------------ -- 会员卡 -- ------------ left join ( select round(sum(t.realFee),2) as num,t.deptId, doctorId from ( select a.dept_id as deptId, IFNULL(a.appointment_doctor_id,'无') as doctorId, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee 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 and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 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 and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id where 1=1 and a.del_flag = 0 and a.data_source = 2 and h.type = 1 and h.pay_state = 1 and h.pay_method=0 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} ) t where 1=1 ${if(len(startTime) == 0,"","AND t.startTime >= '" + startTime + "'")} ${if(len(endTime) == 0,"","AND t.startTime <= '" + endTime + "'")} group by t.deptId,t.doctorId ) t123 on t0.deptId = t123.deptId and t0.doctorId = t123.doctorId -- ------------ -- 微信金额 -- ------------ left join ( select round(sum(t.realFee),2) as num,t.deptId, doctorId from ( select a.dept_id as deptId, IFNULL(a.appointment_doctor_id,'无') as doctorId, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee 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 and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 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 and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号 inner JOIN `thc_rcm`.`pay_trade_log` h on h.settlement_id = g.id where 1=1 and a.del_flag = 0 and a.data_source = 2 and h.type = 1 and h.pay_state = 1 and h.pay_method=4 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} ) t where 1=1 ${if(len(startTime) == 0,"","AND t.startTime >= '" + startTime + "'")} ${if(len(endTime) == 0,"","AND t.startTime <= '" + endTime + "'")} group by t.deptId,t.doctorId ) t12 on t0.deptId = t12.deptId and t0.doctorId = t12.doctorId -- ------------ -- 应收金额 t12 -- ------------ left join ( select round(sum(t.preFee),2) as num,t.deptId, doctorId from ( select a.dept_id as deptId, IFNULL(a.appointment_doctor_id,'无') as doctorId, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, f.preFee -- 原价 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 and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 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 and g.settlementType=2 and g.`isDelete` = 0-- 门诊挂号 where 1=1 and a.del_flag = 0 and g.payStatus = 2 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} ) t where 1=1 ${if(len(startTime) == 0,"","AND t.startTime >= '" + startTime + "'")} ${if(len(endTime) == 0,"","AND t.startTime <= '" + endTime + "'")} group by t.deptId,t.doctorId ) t13 on t0.deptId = t13.deptId and t0.doctorId = t13.doctorId -- ------------ -- 实收金额 t13 -- ------------ left join ( select round(sum(t.realFee),2) as num,t.deptId, doctorId from ( select a.dept_id as deptId, IFNULL(a.appointment_doctor_id,'无') as doctorId, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime, (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) AS realFee 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 and d.`isDelete` = 0 and d.orderSource = 1 and d.orderType = 3 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 and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号 where 1=1 and a.del_flag = 0 and g.payStatus = 2 ${if(len(deptId) == 0,"","and a.dept_id = '" + deptId + "'")} ${if(len(doctorId) == 0,"","and a.appointment_doctor_id = '" + doctorId + "'")} ) t where 1=1 ${if(len(startTime) == 0,"","AND t.startTime >= '" + startTime + "'")} ${if(len(endTime) == 0,"","AND t.startTime <= '" + endTime + "'")} group by t.deptId,t.doctorId ) t14 on t0.deptId = t14.deptId and t0.doctorId = t14.doctorId order by t0.startTime desc
新退号SQL编辑：
-- ------------ -- 退号 -- ------------ select * from ( select g.settleCode, g.createtime, a.dept_id as deptId, IFNULL(a.appointment_doctor_id,'无') as doctorId, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime 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 and d.orderSource = 1 and d.orderType = 3 inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is not NULL 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 and g.settlementType=2 -- 门诊挂号 where 1=1 and g.`returnFlag` = 1 order by g.`createtime` desc ) t where t.createtime > '2019-01-03 00:00:00' select * from thc_rcm.`Cs_Settlement` where settleCode = 'SM2019010300019B5O7t'