set nocount on;
declare @intYear int=${查询年份}
declare @intMonth int=${查询月份}
declare @location_id int=15
IF OBJECT_ID(N'tempdb.dbo.#C') IS NOT NULL
BEGIN
    DROP TABLE #C
END
create table #C
(
FID int identity(1,1) not null,
model_id varchar(100),
report_wo_id varchar(50), 
da_num int,
shift_date datetime ,
material_id int,
reject_num int ,
shift_id int ,
eqpt_id int,
shift_manu_code varchar(50),--班组编号
change_mould int , --换模
other   int, ---其他
fix_mould int ,---模修 
Fstate   varchar(50), --审核状态
fault int ,--异常 
sub_company  varchar(50),
check_type varchar(50),
shift_group_name  varchar(50), --班组
mould_wms_code varchar(50),  --模具编号
eqpt_name  varchar(50) ,--设备
material_name varchar(50), --产品
user_cn_name varchar(50), --上报人
process_name varchar(50),--工序
cav_no varchar(50) ,---型腔号
last_process varchar(50),---是否最后一道工序 
check_name varchar(50), ---审核人
check_time datetime ,
select_shift_user varchar(50), --签入班长
fprice decimal(18,4),
user_id int
)
  insert into #C
  select
  b.model_id,a.report_wo_id 主表ID,i.da_num+isnull(i.user_num1,0) 合格数,a.shift_date 上报时间,a.material_id 产品ID,
             i.reject_num 报废数量,a.shift_id 班组ID,a.eqpt_id 设备ID,--a.report_type 上报类型,,a.site_id 站点
                a.shift_manu_code 班组编号,a.change_mould ,a.other,a.fix_mould,case when a.check_type='1' then '已审核' else '未审核' end as state,
                a.fault,a.sub_company,a.check_type,e.shift_group_name,g.mould_wms_code
                ,b.eqpt_name,c.material_name,d.user_cn_name,f.process_name,i.cav_no,----g.is_last_process,
    case when g.is_last_process='1' then '是' when g.is_last_process='0' then '否' else '' end as last_process,
    h.user_cn_name as check_name,a.check_time,k.user_cn_name as shift_user,0,d.USER_ID
                from  MSSQLSERVER.tn_iot.dbo.d_report_work a with(nolock)
                left join MSSQLSERVER.tn_iot.dbo.bs_eqpt b with(nolock) on a.eqpt_id=b.eqpt_id
                left join MSSQLSERVER.tn_iot.dbo.bs_material c with(nolock) on a.material_id=c.material_id
                left join MSSQLSERVER.tn_iot.dbo.uums_user d with(nolock) on a.user_id=d.user_id
                left join MSSQLSERVER.tn_iot.dbo.bs_shift_group e with(nolock) on a.shift_manu_code=e.shift_group_id
                left join MSSQLSERVER.tn_iot.dbo.bs_process f with(nolock) on a.process_id=f.process_id
                  left join MSSQLSERVER.tn_iot.dbo.uums_dept l with(nolock) on a.shift_manu_code=l.dept_id
                left join MSSQLSERVER.tn_iot.dbo.plan_wo g with(nolock) on a.wo_code=g.wo_code
                left join MSSQLSERVER.tn_iot.dbo.uums_user h with(nolock) on a.check_user=h.user_id
                left join MSSQLSERVER.tn_iot.dbo.uums_user k with(nolock) on a.select_shift_user=k.user_id
                left join MSSQLSERVER.tn_iot.dbo.d_report_work_dtl i with(nolock) on a.report_wo_id=i.report_wo_id
                where 1=1
    and YEAR(a.shift_date)=@intYear
    and Month(a.shift_date)=@intMonth
               and a.location_id=@location_id 
                and a.check_type  =1
                and i.is_delete <> 1 --and a.process_id in (0,90000)
update #C set fprice=0.22 where material_id=2829 and eqpt_id=366
;WITH tb AS (--#C 
SELECT a.material_id,a.shift_date,a.model_id,a.process_name,a.da_num,a.reject_num,a.user_cn_name,a.eqpt_id,a.material_name,a.FID,a.user_id  FROM #C  a
),
tb1 AS (--形成价格表
SELECT DISTINCT  Mf_BaseDe.fprice,Mf_Base.FProductsID,Mf_BaseDe.FQYDate,Pm_Base.FName,Mf_WorkProcedure.FWPName,mf_basede.FBaseDeID FROM
Mf_BaseDe  with(nolock)
 inner  join  Mf_Base with(nolock) on Mf_BaseDe.FBaseID=Mf_Base.FBaseID
 inner join  Pm_Base with(nolock) on Pm_Base.FBaseID=Mf_BaseDe.FEqptClassID and Pm_Base.FType in ('SBFL','CXDY')
 inner join  Mf_WorkProcedure with(nolock) on Mf_WorkProcedure.FWPCode =Mf_BaseDe.FWPCode and Mf_WorkProcedure.FEnable=1
 where Mf_Base.FType='GYGJ'  and isnull(Mf_Base.FWorkShipID,0)=67  
),
tb2 AS (--员工计件数量表和价格表组合，对有价格的产品有效-------------------
SELECT tb.*,tb1.Fprice,tb1.FQYDate,tb1.FBaseDeID FROM tb LEFT JOIN tb1 ON tb.material_id=tb1.FProductsID AND tb.model_id=tb1.FName AND tb.process_name=tb1.FWPName
WHERE 1=1 AND  datediff(day,tb1.FQYDate,tb.shift_date)>=0
),
tb3 AS (--会有同一个报告日期但有多个价格的情况(因为后面有两次启用日期的调整，需要取出多个启用日期里最大的那个,FBaseDeID最大，因为启用日期可能也会一样的情况
--这一步取出唯一的价格表
SELECT tb2.material_id,tb2.shift_date,tb2.model_id,tb2.process_name,tb2.da_num,tb2.reject_num,tb2.user_cn_name,tb2.material_name,MAX(tb2.FBaseDeID)FBaseDeID,tb2.FID FROM tb2
 WHERE 1=1
GROUP BY tb2.material_id,tb2.shift_date,tb2.model_id,tb2.process_name,tb2.da_num,tb2.reject_num,tb2.user_cn_name,tb2.material_name,tb2.FID
),
tb4 AS (--把tb3的FBaseDeID关联价格表的FBaseDeID,获取价格,只能获取到右边这有价格的数据
SELECT tb3.*,tb1.Fprice FROM tb3 LEFT JOIN tb1 ON tb3.FBaseDeID=tb1.FBaseDeID
)
SELECT tb.user_id, hr.FEmplName,SUM(tb.da_num*tb4.Fprice) 合格金额 ,SUM(tb.reject_num*tb4.Fprice) 报废金额,SUM((tb.da_num+tb.reject_num)*tb4.Fprice) 合计金额,SUM(tb.da_num)合格数,SUM(tb.reject_num)报废数
 FROM tb LEFT JOIN tb4 ON tb4.FID=tb.FID
LEFT JOIN HR_Employee hr ON hr.FEmplID=tb.user_id
GROUP BY  hr.FEmplName,tb.user_id

drop table #C