一.项目背景
质量部门对实验室检测业务关注度较高，希望开发一个可视化看板，达到以下目的：
- ①质检流程的监控：支持方便查询任意样本在取样→样本接收→检测→结果录入→结果互审→报告编制→报告审核→放行质检流程状态，以及各个已完成流程耗时；
- ②质检/放行周期：从物料、时间维度，对样本质检周期、放行周期进行监控；
- ③设备预警：监控实验室设备运行状况，对长期没有维护的设备进行预警；
二.数据准备
主要数据为“质检流程时间节点表”，以样本编号为主键，串联其各个检测流程的时间节点。表字段信息如下。
with comno_Maxtime as (
-- 最大时间表
select distinct
sample_code,
max(sampling_time) as 'sampling_time',
max(predict_delivery_time) as 'predict_delivery_time',
max(packing_material_receipt_time) as 'packing_material_receipt_time',
max(microorganism_receipt_time) as 'microorganism_receipt_time',
max(physicochemical_receipt_time) as 'physicochemical_receipt_time',
max(packing_material_result_entry_time) as 'packing_material_result_entry_time',
max(microorganism_result_entry_time) as 'microorganism_result_entry_time',
max(physicochemical_result_entry_time) as 'physicochemical_result_entry_time',
max(packing_material_mutual_review_time) as 'packing_material_mutual_review_time',
max(microorganism_mutual_review_time) as 'microorganism_mutual_review_time',
max(physicochemical_mutual_review_time) as 'physicochemical_mutual_review_time',
max(leader_review_time) as 'leader_review_time',
max(report_time) as 'report_time',
max(create_time) as 'create_time',
max(report_confirm_time) as 'report_confirm_time',
max(report_review_time) as 'report_review_time',
max(pass_time) as 'pass_time',
max(review_time) as 'review_time',
max(process_validation_time) as 'process_validation_time'
from cdm_gyl.dbo.dwd_lims_btn_v_lims_pass_report_query
where registered_sample_time>='2024-05-01'
group by sample_code
),
commo_Yujing as (
-- 预警状态表
select
a.sample_code,
count(a.packing_material_receipt_time) as 'bc_js_state',-- 包材接收
count(a.microorganism_receipt_time) as 'wsw_js_state',-- 微生物接收
count(a.physicochemical_receipt_time) as 'lh_js_state',-- 理化接收
count(a.packing_material_result_entry_time) as 'bc_lr_state',-- 包材录入
count(a.microorganism_result_entry_time) as 'wsw_lr_state',-- 微生物录入
count(a.physicochemical_result_entry_time) as 'lh_lr_state', -- 理化录入
count(a.packing_material_mutual_review_time) as 'bc_hushen',-- 包材互审
count(a.microorganism_mutual_review_time) as 'wsw_hushen',-- 微生物互审
count(a.physicochemical_mutual_review_time) as 'lh_hushen',-- 理化互审
case when count(packing_material_receipt_time)+count(microorganism_receipt_time)+count(physicochemical_receipt_time)>0 then '有材料接收'
else '无' end as 'jiehsou_state',-- 包材接收状态
case when count(packing_material_result_entry_time)+count(microorganism_result_entry_time)+count(physicochemical_result_entry_time)>0
then '有结果录入' else '无' end as 'Result_state', -- 检测结果录入状态
case when count(packing_material_result_entry_time)+count(microorganism_result_entry_time)+count(physicochemical_result_entry_time)>0
and count(packing_material_result_entry_time)=count(packing_material_receipt_time) and count(microorganism_receipt_time)=count(microorganism_result_entry_time) and count(physicochemical_receipt_time)=count(physicochemical_result_entry_time)
then '结果录入-完成' else '结果录入-未完成' end as 'yujing_Result_state', -- 结果状态预警
case when count(packing_material_mutual_review_time)+count(microorganism_mutual_review_time)+count(physicochemical_mutual_review_time)>0
then '有互审' else '无' end as 'hushen_state', -- 结果互审状态
case when count(packing_material_mutual_review_time)+count(microorganism_mutual_review_time)+count(physicochemical_mutual_review_time)>0
and count(packing_material_receipt_time)=count(packing_material_mutual_review_time) and count(microorganism_receipt_time)=count(microorganism_mutual_review_time) and count(physicochemical_receipt_time)=count(physicochemical_mutual_review_time)
then '互审-完成' else '互审-未完成' end as 'yujing_hushen_state' -- 互审状态预警
from comno_Maxtime a
group by a.sample_code
)
select distinct
cdd_qp.sample_code as 'report_id',
cdd_qp.sample_code as '样品编号',
case cdd_qp.material_type
when '外包材' then '外包材'
when '成品' then '成品'
when '非化妆品外包材' then '外包材'
when '非化妆品原料' then '原料'
when '半成品' then '半成品'
when '非化妆品成品' then '成品'
when '非化妆品内包材' then '内包材'
when '内包材' then '内包材'
when '原料' then '原料'
else '其它' end as '物料类别',
cdd_qp.material_code as '物料编码',
cdd_qp.material_name as '物料名称',
cdd_qp.produced_date as '生产日期',
cdd_qp.validity_date as '有效期至',
cdd_qp.arrival_time as '到货日期',
cdd_qp.filling_date as '灌装日期',
cdd_qp.manufacturer as '生产商',
cdd_qp.vendor_name as '供应商',
case when cdd_qp.material_type in ('成品','非化妆品成品','半成品') then cdd_qp.batch_qty
else cdd_qp.arrival_qty end as '数量', -- 成品半成品取batch_qty，原料包材arrival_qty
cdd_qp.unit as '单位',
cdd_qp.arrival_batch as '到货批号',
cdd_qp.factory_batch as '工厂批号',
cdd_qp.versions_outsourcing as '外包版本',
cdd_qp.versions_insourcing as '内包版本',
cdd_qp.experiment_type as '实验类型',
cdd_qp.registered_sample_time as '下样时间',
cdd_qp.inspection_audit_time as '报检审核通过时间',
cdd_qp.sample_state as '检验状态',
-- cdd_qp.report_code as '报告编号', -- 导致重复
-- cdd_qp.report_level as '报告等级',-- 导致重复
comno_Maxtime.sampling_time as '待采样任务提交时间',
comno_Maxtime.predict_delivery_time as '预计出库时间',
case when DATEDIFF(d, comno_Maxtime.sampling_time, comno_Maxtime.predict_delivery_time)<=6 then 1
else 0 end as '是否紧急任务',
case when DATEDIFF(d, comno_Maxtime.sampling_time, comno_Maxtime.predict_delivery_time)<=6 then '紧急'
else '非紧急' end as '紧急',
comno_Maxtime.packing_material_receipt_time as '包材接收时间',
comno_Maxtime.microorganism_receipt_time as '微生物接收时间',
comno_Maxtime.physicochemical_receipt_time as '理化接收时间',
comno_Maxtime.packing_material_result_entry_time as '包材结果录入时间',
comno_Maxtime.microorganism_result_entry_time as '微生物结果录入时间',
comno_Maxtime.physicochemical_result_entry_time as '理化结果录入时间',
comno_Maxtime.packing_material_mutual_review_time as '包材同组互审时间',
comno_Maxtime.microorganism_mutual_review_time as '微生物同组互审时间',
comno_Maxtime.physicochemical_mutual_review_time as '理化同组互审时间',
comno_Maxtime.leader_review_time as '组长审核时间',
COALESCE(comno_Maxtime.packing_material_receipt_time,comno_Maxtime.microorganism_receipt_time,comno_Maxtime.physicochemical_receipt_time) as '材料接收时间',
COALESCE(comno_Maxtime.packing_material_result_entry_time,comno_Maxtime.microorganism_result_entry_time,comno_Maxtime.physicochemical_result_entry_time) as '结果录入时间',
COALESCE(comno_Maxtime.packing_material_mutual_review_time,comno_Maxtime.microorganism_mutual_review_time,comno_Maxtime.physicochemical_mutual_review_time) as '同组互审时间',
-- +预警状态
commo_Yujing.jiehsou_state as '材料接收状态',
commo_Yujing.Result_state as '检测结果录入状态',
commo_Yujing.yujing_Result_state as '结果状态预警',
commo_Yujing.hushen_state as '结果互审状态',
commo_Yujing.yujing_hushen_state as '互审状态预警',
comno_Maxtime.report_time as '报告生成时间',
comno_Maxtime.create_time as '报告编制时间',
comno_Maxtime.report_confirm_time as '报告批准时间',-- 全空
comno_Maxtime.report_review_time as '报告审核时间',
comno_Maxtime.pass_time as '放行时间',
case when comno_Maxtime.pass_time is null then '未结束' else '已结束' end as '质检完成/否',
case when cdd_qp.material_type='半成品' then comno_Maxtime.process_validation_time
else comno_Maxtime.review_time end as '审核/过程确认时间' -- 原料，包材，成品取review_time；半成品取process_validation_time
from cdm_gyl.dbo.dwd_lims_btn_v_lims_pass_report_query cdd_qp
left join comno_Maxtime on comno_Maxtime.sample_code=cdd_qp.sample_code
left join commo_Yujing on commo_Yujing.sample_code=cdd_qp.sample_code
where registered_sample_time>='2024-05-01'
最后的数据表预览如下
三.FineBI数据可视化
1.按需添加公式列
2.按需添加组件公式
这块组件要实现，后一个节点如果没完成，那就用蓝色标注出来。
四.最终看板
1.看板链接：
pcdemo.finebi.com/webroot/dec…
（1）点击某个样品，知道其哪个流程节点没完成。
（2）看各个环节的完成未完成情况；及质量放行周期；还有各个检测组任务量情况。
