select

${if(车间 == '制卷车间',"prod_line_name,","prod_line_code as prod_line_name,")}

sum(output_qty) as output_qty1 ,

        max(plan_qty)*24 as plan_qty1,

        case 

        when max(plan_qty) = 0 then 1  

        else toFloat32(sum(output_qty)) / toFloat32((max(plan_qty)*24)) end as completion_rate

from

(select

  prod_category_name2,

department_name ,

workshop_name,

data_date,

case when operation_name = '负极分切' then '负极二次分条'

when operation_name = '正极分切' then '正极二次分条'

when operation_name = '侧缝焊外观判定及关键尺寸测量' then '侧缝焊判定测量'

else operation_name  end as operation_name,

base_code ,

factory_stage ,

product_model ,

toFloat32(case when operation_name in ('正极涂布','负极涂布','正极一次分条','正极辊压','正极二次分条','负极一次分条','负极辊压','负极二次分条','切叠','热压','极耳预焊','正极预分','正极分切','负极预分','负极分切')

  then toFloat32(output_qty)*0.5

  when operation_name in ('正极合浆','负极合浆') 

  then toFloat32(output_qty) else toFloat32(output_qty) end) as output_qty,

plan_qty,

prod_line_code,

prod_line_name,

work_last_oper_flag,

prod_last_oper_flag

from

zh_dws.dws_mfg_equip_production_h 

where 1=1

and prod_category_name2 = '电芯'

AND workshop_name = '${车间}'

AND factory_stage = '${工厂}'

${if(车间 == '制卷车间',"AND prod_last_oper_flag = 'Y'","AND work_last_oper_flag = 'Y'")}

${if(len(日期) == 0,"and toString(data_date) = formatDateTime(subtractHours(now(),9),'%Y-%m-%d')","and toString(data_date) = ('" + 日期 + "')")} 

group by

data_date,

product_model,

prod_category_name2,

base_code,

factory_stage ,

workshop_name ,

department_name ,

operation_name ,

prod_line_code,

prod_line_name,

work_last_oper_flag,

prod_last_oper_flag

) 

group by prod_line_name,prod_line_code

order by prod_line_code