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
