package mysql
-------医疗消费分析------------
-- 饼状图 默认值 1：商品销售 2：挂号 3：住院每日消费 4.住院门诊
-- 41：住院门诊化验 42：住院门诊影像 43：住院门诊处方 44：住院门诊手术
-- 45：住院门诊处置 5：住院商品销售 6：普通门诊 61：普通门诊化验
-- 62：普通门诊影像 63：普通门诊处方 64：普通门诊手术 65：普通门诊处置
-- 7：美容 8：寄养每日消费 9：寄养商品销售 10：疫苗 11:驱虫 12：订单结算商品销售',
SELECT
consumption.top_category_id,
con._name,
convert(ifnull(sum(consumption.quantity*consumption.payprice), 0) / 1000000, decimal(11,3))
FROM
his_consumption consumption
left JOIN his_bill bill ON consumption.his_bill_id = bill.id
LEFT JOIN con_category con ON consumption.top_category_id = con.id
WHERE
consumption.delflag = 0
AND consumption.sys_hospital_id IN ( 0, 1 )
AND bill.billtype IN (1,4,6,7)
and consumption.his_retreatbill_id = 0
and consumption.commodity_ismeal = 0
and consumption.meter_num = 0
AND consumption.top_category_id IN ( 1,2,3,4,5,6,7,8,9,10)
GROUP BY
consumption.top_category_id
-- 下方数据
SELECT
consumption.top_category_id as top_id ,
con._name as _name,
convert(ifnull(sum(consumption.quantity*consumption.payprice), 0) / 1000000, decimal(11,3)) AS money,
DATE_FORMAT(consumption.createtime,'%Y-%m-%d') AS _time
FROM
his_consumption consumption
left JOIN his_bill bill ON consumption.his_bill_id = bill.id
LEFT JOIN con_category con ON consumption.top_category_id = con.id
WHERE
consumption.delflag = 0
AND consumption.sys_hospital_id IN ( 0, 1 )
AND bill.billtype IN (1,4,6,7)
and consumption.his_retreatbill_id = 0
and consumption.commodity_ismeal = 0
and consumption.meter_num = 0
AND consumption.top_category_id IN ( 1,2,3,4,5,6,7,9,10)
AND DATE_FORMAT(consumption.createtime,'%Y-%m-%d') BETWEEN "2021-01-01" AND "2022-01-01"
GROUP BY
consumption.top_category_id,DATE_FORMAT(consumption.createtime,'%Y-%m-%d')
---- 商品分析
---商品数量以及分类数量
-- 商品库存总量
select
COUNT(quantity)
from
g_stock
where
delflag = 0
and sys_hospital_id = 1
-- 服务商品总量
--商品总种类
select
count( 0 ) as counts
FROM
(
SELECT
commodity.id
FROM
g_stock stock
LEFT JOIN con_commodity commodity ON stock.con_commodity_id = commodity.id
LEFT JOIN con_category category ON category.id = commodity.con_category_id
WHERE
stock.delflag = 0
AND stock.sys_hospital_id = 1
AND commodity.top_category_id IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,11,12)
GROUP BY
commodity.id
) a
-- 普通商品
select
count( 0 ) as counts
FROM
(
SELECT
commodity.id
FROM
g_stock stock
LEFT JOIN con_commodity commodity ON stock.con_commodity_id = commodity.id
LEFT JOIN con_category category ON category.id = commodity.con_category_id
WHERE
stock.delflag = 0
AND stock.sys_hospital_id = 1
AND commodity.top_category_id IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
GROUP BY
commodity.id
) a
-- 商品其他种类 在in中填写 种类id
select
count( 0 ) as counts
FROM
(
SELECT
commodity.id
FROM
g_stock stock
LEFT JOIN con_commodity commodity ON stock.con_commodity_id = commodity.id
LEFT JOIN con_category category ON category.id = commodity.con_category_id
WHERE
stock.delflag = 0
AND stock.sys_hospital_id = 1
AND commodity.top_category_id IN ( 11)
GROUP BY
commodity.id
) a
--
select
count( 0 ) as counts
FROM
(
SELECT
commodity.id
FROM
g_stock stock
LEFT JOIN con_commodity commodity ON stock.con_commodity_id = commodity.id
LEFT JOIN con_category category ON category.id = commodity.con_category_id
WHERE
stock.delflag = 0
AND stock.sys_hospital_id = 1
AND commodity.top_category_id IN ( 12)
GROUP BY
commodity.id
) a
--商品警惕
SELECT
c.con_commodity_id,
c.commodity_name,
COUNT(0)
FROM
his_consumption AS c
LEFT JOIN his_bill AS b ON b.id = c.his_bill_id
LEFT JOIN g_stock AS g ON g.con_commodity_id = c.con_commodity_id
WHERE
c.delflag = 0
AND c.sys_hospital_id = 1
AND c.his_retreatbill_id = 0
AND c.commodity_ismeal = 0
AND c.meter_num = 0
AND b.billtype IN ( 1, 4, 6, 7 )
and DATE_FORMAT(b.paytime, "%Y-%m-%d") BETWEEN "2021-01-01" AND "2022-01-01"
GROUP BY
c.con_commodity_id
--商品销售分析
SELECT
c.con_commodity_id,
c.commodity_name,
c.top_category_id,
SUM(c.quantity) ,
CONVERT (
ifnull( sum( c.quantity * c.payprice ), 0 ) / 1000000,
DECIMAL ( 11, 3 )),
CONVERT (
ifnull( sum( c.quantity * g.costmoney ), 0 ) / 1000000,
DECIMAL ( 11, 3 ))
FROM
his_consumption AS c
LEFT JOIN his_bill AS b ON b.id = c.his_bill_id
LEFT JOIN g_stock AS g ON g.con_commodity_id = c.con_commodity_id
WHERE
c.delflag = 0
AND c.sys_hospital_id = 1
AND c.his_retreatbill_id = 0
AND c.commodity_ismeal = 0
AND c.meter_num = 0
AND b.billtype IN ( 1, 4, 6, 7 )
GROUP BY
c.con_commodity_id
---员工分析
-- 首先查出所有的已结订单
--B端小程序
--- 数据概览
-- 新增用户
select count(*) from his_consumer where delflag = 0 and DATE_FORMAT(addtime, ?) = ?
--新增会员(新增会员卡与次卡去重后就是新增会员)
-- 新增的会员卡用户
SELECT
c.id
FROM
his_consumer c
INNER JOIN his_card card ON c.id = card.his_consumer_id
AND c.id not IN (select his_consumer_id FROM his_card WHERE delflag = 0 AND sys_hospital_id = 1 AND createtime < "2021-01-01")
AND c.id not IN (select his_consumer_id FROM his_metercard WHERE delflag = 0 AND sys_hospital_id = 1 AND createtime < "2021-01-01")
-- 新增的次卡用户
SELECT
c.id
FROM
his_consumer c
INNER JOIN his_metercard card ON c.id = card.his_consumer_id
AND c.id not IN (select his_consumer_id FROM his_card WHERE delflag = 0 AND sys_hospital_id = 1 AND createtime < "2021-01-01")
AND c.id not IN (select his_consumer_id FROM his_metercard WHERE delflag = 0 AND sys_hospital_id = 1 AND createtime < "2021-01-01")
--- 新客消费 老客消费 到店消费
-- 新客
SELECT
his_consumer_id,
SUM( paymoney / 1000 ) price
FROM
his_bill
WHERE
delflag = 0
AND his_consumer_id > 0
AND billtype != 5
AND billtype != 7
AND sys_hospital_id = 1
AND DATE_FORMAT( paytime, '%Y-%m-%d' ) = "2021-04-06"
AND his_consumer_id NOT IN (
SELECT
his_consumer_id
FROM
his_bill
WHERE
delflag = 0
AND his_consumer_id > 0
AND billtype != 5
AND billtype != 7
AND sys_hospital_id = 1
AND DATE_FORMAT( paytime, '%Y-%m-%d' ) < "2021-04-06"
GROUP BY
his_consumer_id
)
GROUP BY
DATE_FORMAT( paytime, '%Y-%m-%d' ),
his_consumer_id
-- 老客
SELECT
his_consumer_id,
SUM( paymoney / 1000 ) price
FROM
his_bill
WHERE
delflag = 0
AND his_consumer_id > 0
AND billtype != 5
AND billtype != 7
AND sys_hospital_id = 1
AND date_format( paytime, '%Y-%m-%d' ) = "2021-04-06"
AND his_consumer_id IN (
SELECT
his_consumer_id
FROM
his_bill
WHERE
delflag = 0
AND his_consumer_id > 0
AND billtype != 5
AND billtype != 7
AND sys_hospital_id = 1
AND DATE_FORMAT( paytime, '%Y-%m-%d' ) < "2021-04-06"
GROUP BY
his_consumer_id
)
GROUP BY
DATE_FORMAT( paytime, '%Y-%m-%d' ),
his_consumer_id
-- 到店 为 新客加老客
-- 次卡分析
-- 次卡分析按时间与次卡id
-- 次卡充值的可以使用的次数SUM( meter ),与新开次卡张数 COUNT( 0 )
SELECT
DATE_FORMAT( card.createtime, '%Y-%m-%d' ) ,
card.con_metercardtype_id,
type._name,
ifnull( SUM( meter ), 0 ),
COUNT( 0 )
FROM
dbc_hueqzfp2kn.his_metercard card
LEFT JOIN dbc_hueqzfp2kn.con_metercardtype type ON type.id = card.con_metercardtype_id
WHERE
card.delflag = 0
AND card.sys_hospital_id = 1
AND DATE_FORMAT( card.createtime, '%Y-%m-%d' ) BETWEEN "2021-01-01"
AND "2022-01-01"
AND card.con_metercardtype_id = 3
GROUP BY
DATE_FORMAT( card.createtime, '%Y-%m-%d' )
-- 次卡分析之详细数据
SELECT
DATE_FORMAT( meter_detail.eventtime, '%Y-%m-%d' ),
SUM( CONVERT ( ifnull( meter_detail.money, 0 ), DECIMAL ( 11, 3 )) ) / 1000 AS money,
meter_type.id
FROM
dbc_hueqzfp2kn.his_metercard_detail AS meter_detail
LEFT JOIN dbc_hueqzfp2kn.his_metercard AS meter ON meter_detail.his_metercard_id = meter.id
LEFT JOIN dbc_hueqzfp2kn.con_metercardtype AS meter_type ON meter.con_metercardtype_id = meter_type.id
WHERE
meter_detail.sys_hospital_id = 1
AND meter_detail.eventtype = 2
AND meter_detail.delflag = 0
AND DATE_FORMAT( meter_detail.eventtime, '%Y-%m-%d' ) BETWEEN "2021-01-01"
AND "2022-01-01"
AND meter_type.id = 2
GROUP BY
DATE_FORMAT( meter_detail.eventtime, '%Y-%m-%d' )
-- 数据概览
-- 开卡 充值 排行
-- 会员卡
SELECT
card_type._name,
ifnull(SUM(card_pay.money) / 1000,0)
FROM
his_card card
LEFT JOIN con_cardtype card_type ON card_type.id = card.con_cardtype_id
LEFT JOIN his_card_detail card_detail ON. card_detail.his_card_id = card.id
left JOIN his_card_detail_pay card_pay ON card_pay.his_card_detail_id = card_detail.id
AND card.sys_hospital_id = 1
GROUP BY card.con_cardtype_id
-- 次卡
SELECT
card_type._name,
ifnull(SUM(card_pay.money),0) / 1000
FROM
his_metercard card
LEFT JOIN con_metercardtype card_type ON card_type.id = card.con_metercardtype_id
LEFT JOIN his_metercard_detail card_detail ON. card_detail.his_metercard_id = card.id
left JOIN his_metercard_detail_pay card_pay ON card_pay.his_metercard_detail_id = card_detail.id
AND card.sys_hospital_id = 1
GROUP BY card.con_metercardtype_id
-- 押金
SELECT
SUM(p.money) / 1000
FROM
his_deposit_detail d
LEFT JOIN his_deposit_detail_pay p ON d.id = p.his_deposit_detail_id
WHERE
d.sys_hospital_id = 1
-- 新客户初诊
SELECT
COUNT(0)
FROM
(SELECT
id,
DATE_FORMAT( eventtime, '%Y-%m-%d' ) AS _time
FROM
his_clinic
WHERE
delflag = 0
AND sys_hospital_id = 1
AND DATE_FORMAT( eventtime, '%Y-%m-%d' ) BETWEEN "2021-09-01"
AND "2021-10-10"
AND group_id = 0
AND his_consumer_id NOT IN (
SELECT
his_consumer_id
FROM
his_clinic
WHERE
delflag = 0
AND sys_hospital_id = 1
AND DATE_FORMAT( eventtime, '%Y-%m-%d' ) < "2021-09-10"
)
GROUP BY his_consumer_id) as a