按科室统计 2.181222版本 关联查询join 不对
2024-10-10 22:10:06
SQL：
select
t0.deptName,
t0.deptId,
t0.startTime,
t0.endTime,
IFNULL(t0.num,0) as num0,
IFNULL(t1.num,0) as num1,
IFNULL(t2.num,0) as num2,
IFNULL(t4.num,0) as num4,
IFNULL(t5.num,0) as num5,
IFNULL(t6.num,0) as num6,
IFNULL(t7.num,0) as num7,
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(t13.num,0) as num13,
IFNULL(t14.num,0) as num14,
IFNULL(t15.num,0) as num15,
IFNULL(t16.num,0) as num16,
IFNULL(t17.num,0) as num17,
IFNULL(t18.num,0) as num18,
IFNULL(t19.num,0) as num19,
IFNULL(t20.num,0) as num20,
IFNULL(t21.num,0) as num21,
IFNULL(t22.num,0) as num22
from
-- ------------
-- 总挂号数
-- ------------
(
select t.startTime, t.endTime, count(1) as num,t.deptId, t.deptName from
(
select
a.data_source,
a.is_appoint_resource,
a.subsequent_visit,
a.appointment_state,
a.dept_id as deptId, a.dept_name as deptName, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
f.preFee, -- 原价
(f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
f.discount,
h.flow_fee,
h.`charge`,
h.`pay_method`
from `thc_arrange`.`bpm_appointment` a
inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id and b.del_flag = 0
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
inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.isDelete = 0
inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and f.isDelete = 0
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 and h.type = 1
where 1=1 and a.del_flag = 0
) t group by t.deptId
) t0 -- ------------
-- 退号
-- ------------
left join
(
select count(1) as num,t.deptId, t.deptName from
(
select
a.data_source,
a.is_appoint_resource,
a.subsequent_visit,
a.dept_id as deptId,
a.appointment_state,
a.dept_name as deptName,
CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
f.preFee, -- 原价
(f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
f.discount,
h.flow_fee,
h.`charge`,
h.`pay_method`
from `thc_arrange`.`bpm_appointment` a
inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id and b.del_flag = 0
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
inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.isDelete =0
inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and f.isDelete = 0
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 and h.type = 1
where 1=1 and a.del_flag = 0 AND a.appointment_state = -1 ) t
group by t.deptId
) t1
on t0.deptId = t1.deptId -- ------------
-- 实际挂号数 = 初诊数 + 复诊数 + 转诊数
-- ------------
left join
(
select count(1) as num,t.deptId, t.deptName from
(
select
a.data_source,
a.is_appoint_resource,
a.subsequent_visit,
a.dept_id as deptId, a.appointment_state,
a.dept_name as deptName, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
f.preFee, -- 原价
(f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
f.discount,
h.flow_fee,
h.`charge`,
h.`pay_method`
from `thc_arrange`.`bpm_appointment` a
inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id and b.del_flag = 0
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
inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.isDelete = 0
inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0
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 and h.type = 1
where 1=1 and a.del_flag = 0 AND a.subsequent_visit in (0,1,2) ) t group by t.deptId
) t2
on t0.deptId = t2.deptId -- ------------
-- 初诊
-- ------------
left join
(
select count(1) as num,t.deptId, t.deptName from
(
select
a.data_source,
a.is_appoint_resource,
a.subsequent_visit,
a.dept_id as deptId, a.appointment_state,
a.dept_name as deptName, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
f.preFee, -- 原价
(f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
f.discount,
h.flow_fee,
h.`charge`,
h.`pay_method`
from `thc_arrange`.`bpm_appointment` a
inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id and b.del_flag = 0
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
inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.isDelete = 0
inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0
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 and h.type = 1
where 1=1 and a.del_flag = 0 AND a.subsequent_visit = 0 ) t group by t.deptId
) t4
on t0.deptId = t4.deptId -- ------------
-- 复诊
-- ------------
left join
(
select count(1) as num,t.deptId, t.deptName from
(
select
a.data_source,
a.is_appoint_resource,
a.subsequent_visit,
a.dept_id as deptId, a.appointment_state,
a.dept_name as deptName, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
f.preFee, -- 原价
(f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
f.discount,
h.flow_fee,
h.`charge`,
h.`pay_method`
from `thc_arrange`.`bpm_appointment` a
inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id and b.del_flag = 0
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
inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.isDelete = 0
inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0
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 and h.type = 1
where 1=1 and a.del_flag = 0 AND a.subsequent_visit = 1 ) t group by t.deptId
) t5
on t0.deptId = t5.deptId -- ------------
-- 体检
-- ------------
left join
(
select count(1) as num,t.deptId, t.deptName from
(
select
a.data_source,
a.is_appoint_resource,
a.subsequent_visit,
a.dept_id as deptId, a.appointment_state,
a.dept_name as deptName, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
f.preFee, -- 原价
(f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
f.discount,
h.flow_fee,
h.`charge`,
h.`pay_method`
from `thc_arrange`.`bpm_appointment` a
inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id and b.del_flag = 0
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
inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.isDelete = 0
inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0
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 and h.type = 1
where 1=1 and a.del_flag = 0 AND a.subsequent_visit = 4 ) t group by t.deptId
) t6
on t0.deptId = t6.deptId -- ------------
-- 团队
-- ------------
left join
(
select count(1) as num,t.deptId, t.deptName from
(
select
a.data_source,
a.is_appoint_resource,
a.subsequent_visit,
a.dept_id as deptId, a.appointment_state,
a.dept_name as deptName, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
f.preFee, -- 原价
(f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
f.discount,
h.flow_fee,
h.`charge`,
h.`pay_method`
from `thc_arrange`.`bpm_appointment` a
inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id and b.del_flag = 0
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
inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.isDelete = 0
inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0
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 and h.type = 1
where 1=1 and a.del_flag = 0 AND a.subsequent_visit = 7 ) t group by t.deptId
) t7
on t0.deptId = t7.deptId -- ------------
-- 简易
-- ------------
left join
(
select count(1) as num,t.deptId, t.deptName from
(
select
a.data_source,
a.is_appoint_resource,
a.subsequent_visit,
a.dept_id as deptId, a.appointment_state,
a.dept_name as deptName, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
f.preFee, -- 原价
(f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
f.discount,
h.flow_fee,
h.`charge`,
h.`pay_method`
from `thc_arrange`.`bpm_appointment` a
inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id and b.del_flag = 0
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
inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.isDelete = 0
inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0
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 and h.type = 1
where 1=1 and a.del_flag = 0 AND a.subsequent_visit = 5 ) t group by t.deptId
) t8
on t0.deptId = t8.deptId -- ------------
-- 转诊
-- ------------
left join
(
select count(1) as num,t.deptId, t.deptName from
(
select
a.data_source,
a.is_appoint_resource,
a.subsequent_visit,
a.dept_id as deptId, a.appointment_state,
a.dept_name as deptName, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
f.preFee, -- 原价
(f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
f.discount,
h.flow_fee,
h.`charge`,
h.`pay_method`
from `thc_arrange`.`bpm_appointment` a
inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id and b.del_flag = 0
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
inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.isDelete = 0
inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0
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 and h.type = 1
where 1=1 and a.del_flag = 0 AND a.subsequent_visit = 2 ) t group by t.deptId
) t9
on t0.deptId = t9.deptId -- ------------
-- 疫苗
-- ------------
left join
(
select count(1) as num,t.deptId, t.deptName from
(
select
a.data_source,
a.is_appoint_resource,
a.subsequent_visit,
a.dept_id as deptId, a.appointment_state,
a.dept_name as deptName, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
f.preFee, -- 原价
(f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
f.discount,
h.flow_fee,
h.`charge`,
h.`pay_method`
from `thc_arrange`.`bpm_appointment` a
inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id and b.del_flag = 0
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
inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.isDelete = 0
inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0
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 and h.type = 1
where 1=1 and a.del_flag = 0 AND a.subsequent_visit = 2
) t group by t.deptId
) t10
on t0.deptId = t10.deptId -- ------------
-- t11 微信数
-- ------------
left join
(
select count(1) as num,t.deptId, t.deptName from
(
select
a.data_source,
a.is_appoint_resource,
a.subsequent_visit,
a.dept_id as deptId, a.appointment_state,
a.dept_name as deptName, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
f.preFee, -- 原价
(f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
f.discount,
h.flow_fee,
h.`charge`,
h.`pay_method`
from `thc_arrange`.`bpm_appointment` a
inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id and b.del_flag = 0
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
inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.isDelete = 0
inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0
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 and h.type = 1
where 1=1 and a.del_flag = 0 AND a.data_source = 2 ) t group by t.deptId
) t11
on t0.deptId = t11.deptId -- ------------
-- 挂号费 t13
-- ------------
left join
(
select round(sum(t.preFee),2) as num,t.deptId, t.deptName from
(
select
a.data_source,
a.is_appoint_resource,
a.subsequent_visit,
a.dept_id as deptId, a.appointment_state,
a.dept_name as deptName, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
f.preFee, -- 原价
(f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
f.discount,
h.flow_fee,
h.`charge`,
h.`pay_method`
from `thc_arrange`.`bpm_appointment` a
inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id and b.del_flag = 0
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
inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.isDelete = 0
inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0
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 and h.type = 1
where 1=1 and a.del_flag = 0
) t group by t.deptId
) t13
on t0.deptId = t13.deptId -- ------------
-- 急诊费 t14
-- ------------
left join
(
select round(sum(t.preFee),2) as num,t.deptId, t.deptName from
(
select
a.data_source,
a.is_appoint_resource,
a.subsequent_visit,
a.dept_id as deptId, a.appointment_state,
a.dept_name as deptName, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
f.preFee, -- 原价
(f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
f.discount,
h.flow_fee,
h.`charge`,
h.`pay_method`
from `thc_arrange`.`bpm_appointment` a
inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id and b.del_flag = 0
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
inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.isDelete = 0
inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0
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 and h.type = 1
where 1=1 and a.del_flag = 0 AND e.itemCode = 'jiajifei' -- 加急费：急诊费
) t group by t.deptId
) t14
on t0.deptId = t14.deptId -- ------------
-- 工本费 t15
-- ------------
left join
(
select round(sum(t.preFee),2) as num,t.deptId, t.deptName from
(
select
a.data_source,
a.is_appoint_resource,
a.subsequent_visit,
a.dept_id as deptId, a.appointment_state,
a.dept_name as deptName, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
f.preFee, -- 原价
(f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
f.discount,
h.flow_fee,
h.`charge`,
h.`pay_method`
from `thc_arrange`.`bpm_appointment` a
inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id and b.del_flag = 0
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
inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.isDelete = 0
inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0
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 and h.type = 1
where 1=1 and a.del_flag = 0 AND e.itemCode = 'bingliben' -- 病历本：工本费
) t group by t.deptId
) t15
on t0.deptId = t15.deptId -- ------------
-- 卡费 t16
-- ------------
left join
(
select round(sum(t.preFee),2) as num,t.deptId, t.deptName from
(
select
a.data_source,
a.is_appoint_resource,
a.subsequent_visit,
a.dept_id as deptId, a.appointment_state,
a.dept_name as deptName, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
f.preFee, -- 原价
(f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
f.discount,
h.flow_fee,
h.`charge`,
h.`pay_method`
from `thc_arrange`.`bpm_appointment` a
inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id and b.del_flag = 0
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
inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.isDelete = 0
inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0
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 and h.type = 1
where 1=1 and a.del_flag = 0 AND e.itemCode = 'jiuzhenka' -- 就诊卡：卡费
) t group by t.deptId
) t16
on t0.deptId = t16.deptId -- ------------
-- 现金 t17
-- ------------
left join
(
select round(sum(t.preFee),2) as num,t.deptId, t.deptName from
(
select
a.data_source,
a.is_appoint_resource,
a.subsequent_visit,
a.dept_id as deptId, a.appointment_state,
a.dept_name as deptName, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
f.preFee, -- 原价
(f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
f.discount,
h.flow_fee,
h.`charge`,
h.`pay_method`
from `thc_arrange`.`bpm_appointment` a
inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id and b.del_flag = 0
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
inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.isDelete = 0
inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and f.isDelete=0
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 and h.type = 1
where 1=1 and a.del_flag = 0 -- 门诊
and h.pay_method = 1 -- 银行卡
) t group by t.deptId
) t17
on t0.deptId = t17.deptId -- ------------
-- 银行卡 t18
-- ------------
left join
(
select round(sum(t.preFee),2) as num,t.deptId, t.deptName from
(
select
a.data_source,
a.is_appoint_resource,
a.subsequent_visit,
a.dept_id as deptId, a.appointment_state,
a.dept_name as deptName, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
f.preFee, -- 原价
(f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
f.discount,
h.flow_fee,
h.`charge`,
h.`pay_method`
from `thc_arrange`.`bpm_appointment` a
inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id and b.del_flag = 0
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
inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.isDelete = 0
inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0
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 and h.type = 1
where 1=1 and a.del_flag = 0
and h.pay_method = 2 -- 银行卡
) t group by t.deptId
) t18
on t0.deptId = t18.deptId -- ------------
-- 预检 t19
-- ------------
left join
(
select round(sum(t.preFee),2) as num,t.deptId, t.deptName from
(
select
a.data_source,
a.is_appoint_resource,
a.subsequent_visit,
a.dept_id as deptId, a.appointment_state,
a.dept_name as deptName, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
f.preFee, -- 原价
(f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
f.discount,
h.flow_fee,
h.`charge`,
h.`pay_method`
from `thc_arrange`.`bpm_appointment` a
inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id and b.del_flag = 0
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
inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.isDelete = 0
inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0
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 and h.type = 1
where 1=1 and a.del_flag = 0 and e.itemCode = 'yujian' -- 预检：预存款
) t
group by t.deptId
) t19
on t0.deptId = t19.deptId -- ------------
-- 微信金额t20
-- ------------
left join
(
select round(sum(t.preFee),2) as num,t.deptId, t.deptName from
(
select
a.data_source,
a.is_appoint_resource,
a.subsequent_visit,
a.dept_id as deptId, a.appointment_state,
a.dept_name as deptName, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
f.preFee, -- 原价
(f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
f.discount,
h.flow_fee,
h.`charge`,
h.`pay_method`
from `thc_arrange`.`bpm_appointment` a
inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id and b.del_flag = 0
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
inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.isDelete = 0
inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0
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 and h.type = 1
where 1=1 and a.del_flag = 0 and a.data_source = 2 ) t group by t.deptId
) t20
on t0.deptId = t20.deptId -- ------------
-- 应收金额 t21
-- ------------
left join
(
select round(sum(t.preFee),2) as num,t.deptId, t.deptName from
(
select
a.data_source,
a.is_appoint_resource,
a.subsequent_visit,
a.dept_id as deptId, a.appointment_state,
a.dept_name as deptName, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
f.preFee, -- 原价
(f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
f.discount,
h.flow_fee,
h.`charge`,
h.`pay_method`
from `thc_arrange`.`bpm_appointment` a
inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id and b.del_flag = 0
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
inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.isDelete = 0
inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0
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 and h.type = 1
where 1=1 and a.del_flag = 0 ) t group by t.deptId
) t21
on t0.deptId = t21.deptId -- ------------
-- 实收金额 t22
-- ------------
left join
(
select round(sum(t.realFee),2) as num,t.deptId,t.deptName from
(
select
a.data_source,
a.is_appoint_resource,
a.subsequent_visit,
a.dept_id as deptId, a.appointment_state,
a.dept_name as deptName, CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
f.preFee,
(f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
f.discount,
h.flow_fee,
h.`charge`,
h.`pay_method`
from `thc_arrange`.`bpm_appointment` a
inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id and b.del_flag = 0
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
inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.isDelete = 0
inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0
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 and h.type = 1
where 1=1 and a.del_flag = 0 ) t
group by t.deptId
) t22
on t0.deptId = t22.deptId order by t0.startTime asc
最新文章
- [转]GC简介
- Android -- Adapter
- Nginx+Varnish 实现动静分离，为服务器分流，降低服务器负载
- CSS 兼容 总结
- Awk使用一例：获取ASCII可见字符
- 使用git管理github上的项目
- UIToolbar swift
- UITableView 的使用总结
- 类似java.lang.NoClassDefFoundError: org/jaxen/JaxenException解决方法
- vue学习笔记 模板语法（三）
- Loadrunner 读取文件
- 【手记】解决“未能创建 SSL/TLS 安全通道”异常
- Oracle总结一
- 跟踪OceanLotus的新下载程序KerrDown
- SpringMVC.入门篇《二》form表单
- linux通配符含义
- mamcached+magent构建memcached集群
- 洛谷P3385负环
- Hibernate学习笔记四
- flask配置加载几种方式