ERP SQL Server 5 -6

5、派工：根据入库扫描条码进行派工（派工到人）
根据已经入库的条码数据进行派工，在任务派工界面必须显示订单单号和入库编号；
派工至职员，同一个职员只允许有一条未完工数据
派工功能维护（按物料匹配）
数据源sql

select '' 派工,'' 派工单号,'' 职员,
CAST(GETDATE() AS DATETIME) 开始时间,CAST(GETDATE() AS DATETIME) 结束时间,0 派工数量,0 完工数,
b.FCode 工序计划单号,a.FentryID 工序号,b.FSourceBillNo 订单单号,b.FSN 入库编号,
c.FNumber 产品代码,c.FName 产品名称,c.fmodel 产品规格,cast(a.FLabelQty as float) 任务单数量,
cast(a.FLabelQty-isnull(t2.fsqty,0) as float) 未派工数量,'未派工' 状态,
NULL 实际开工时间,NULL 实际完工时间,0 完工数量,
c.fitemid 产品内码,a.FinterID 任务单内码,NULL 派工时间,
0 时间戳
from br_codeinfo a
inner join BR_InsertStock b on FProcFlag='S' and a.FCode=b.FCode
inner join ERP1_t_icitem c on a.fitemid=c.fitemid  
left join (select T1.fpkvalue,fitemid,sum(fpqty) fsqty
		   from M_Task_111 t1 (nolock) WHERE isnull(fstatus,0)<>9 
		   group by T1.fpkvalue,fitemid) t2 on b.FCode=t2.fpkvalue and t2.fitemid=b.FItemID
where FISIN=1  and isnull(a.FQTY,0)-isnull(t2.fsqty,0)>0
and b.FFuncNumber='01.04.10'
union all
select '√' 派工,a.fbillno 派工单号, v1.FName 职员,
CAST(FDATEFrom AS DATETIME) 开始时间,CAST(FDateTo AS DATETIME) 结束时间,cast(a.FpQTY as float) 派工数量,isnull(a.FRQTY,0) 完工数,
isnull(a.fpkvalue,'') 工序计划单号,a.FentryID 工序号,b.FSourceBillNo 订单单号,b.FSN 入库编号,
d.FNumber 产品代码,d.FName 产品名称,d.fmodel 产品规格,cast(c.FLabelQty as float) 任务单数量,
0 未派工数量,case isnull(a.FStatus,0) when 0 then '未关闭' else '关闭' end 状态,
a.FDateS 实际开工时间,a.FDateE 实际完工时间,cast(FRQty as float) 完工数量,
a.fitemid 产品内码,a.FinterID 任务单内码,FTaskDate 派工时间,
a.FTimestamp 时间戳
from M_Task_111 a
inner join BR_InsertStock b  on a.FPKValue=b.FCode and a.fitemid=b.FItemID and b.FProcFlag='S'
inner join BR_CodeInfo c on b.FCode=c.FCode
inner join ERP1_t_icitem d on c.fitemid=d.fitemid and d.FDeleted=0 and d.FBatchManager=1 and d.FISKFPeriod=1
inner join ERP1_T_Emp v1 on v1.FNumber=a.FNAME
left join (select T1.fpkvalue,fitemid,sum(fpqty) fsqty
		   from M_Task_111 t1 (nolock) WHERE isnull(fstatus,0)<>9 
		   group by T1.fpkvalue,fitemid) t2 on b.FSourceBillNo=t2.fpkvalue and t2.fitemid=b.FItemID
where ISNULL(a.FSTATUS,0)<>9

派工校验sql

if (select count(1) from M_Task_111 where FNAME='#变量_职员#' and fstatus=0)>0
begin select '#变量_职员#'+'机台产线未关闭派工任务超过1条!' end
if ('#变量_派工数量#'<=0)
begin select '派工数量异常' end
if ('#变量_开始时间#'>'#变量_结束时间#')
begin select '派工时间异常' end
取消派工校验sql
declare @FTime as varchar(50) select @FTime=CAST(FTimestamp AS int) FROM M_Task_001 WHERE FBILLNO='#变量_派工单号#' if(@FTime<>'#变量_时间戳#')
begin	
select '数据已变更，请重新查询后再操作！' 
end
if exists(select 1 FROM M_Task_001 WHERE FBILLNO='#变量_派工单号#' and (isnull(FRQTY,0)+isnull(FRLQTY,0))>0 )
begin	
select '已有汇报数据不允许取消！' 
end

派工值更新sql

update M_Task_001 set FQTQty='#变量_齐套数#' where FBillNo='#变量_派工单号#'
关闭校验sql
declare @FTime as varchar(50) select @FTime=CAST(FTimestamp AS int) FROM M_Task_001 WHERE FBILLNO='#变量_派工单号#' 
if(@FTime<>'#变量_时间戳#')
begin	select '数据已变更，请重新查询后再操作！' 
end
else
begin	
update M_Task_001 set FDateE=convert(varchar(20),getdate(),20),FPQTY=isnull(FRQTY,0)+isnull(FRLQTY,0)
WHERE FBILLNO='#变量_派工单号#'  
end

派工对象维护
职员

select FNumber 代码,FName 名称 from ERP1_t_Emp
派工功能维护（按行匹配）
数据源sql
select '' 派工,'' 派工单号,'' 职员,
CAST(GETDATE() AS DATETIME) 开始时间,CAST(GETDATE() AS DATETIME) 结束时间,cast(isnull(a.FQTY,0)-isnull(t2.fsqty,0) as float) 派工数量,0 完工数,
b.FSourceBillNo 工序计划单号,a.FentryID 工序号,b.FSourceBillNo 订单单号,b.FSN 入库编号,
c.FNumber 产品代码,c.FName 产品名称,c.fmodel 产品规格,cast(a.fqty as float) 任务单数量,
b.FQty 未派工数量,'未派工' 状态,
NULL 实际开工时间,NULL 实际完工时间,0 完工数量,
c.fitemid 产品内码,a.FinterID 任务单内码,NULL 派工时间,
0 时间戳
from br_codeinfo a
inner join AIS20200722111428..t_BOS200000001 b1 on a.FInterID=b1.fid
inner join BR_InsertStock b on FProcFlag='S' and a.FCode=b.FCode
inner join ERP1_t_icitem c  on a.fitemid=c.fitemid and c.FDeleted=0 and c.FBatchManager=1 and c.FISKFPeriod=1
left join (select T1.fpkvalue,sum(fpqty) fsqty
		   from M_Task_111 t1 (nolock) WHERE isnull(fstatus,0)<>9 
		   group by T1.fpkvalue) t2 on b.FSourceBillNo=T2.fpkvalue
where FISIN=1  and FSourceBillNo <> '' and isnull(a.FQTY,0)-isnull(t2.fsqty,0)>0
union all
select '√' 派工,a.fbillno 派工单号, v1.FName 职员,
CAST(FDATEFrom AS DATETIME) 开始时间,CAST(FDateTo AS DATETIME) 结束时间,cast(isnull(a.FpQTY,0) as float) 派工数量,isnull(a.FRQTY,0) 完工数,
isnull(a.fpkvalue,'') 工序计划单号,a.FentryID 工序号,b2.FSourceBillNo 订单单号,b2.FSN 入库编号,
c1.FNumber 产品代码,c1.FName 产品名称,c1.fmodel 产品规格,cast(b1.fqty as float) 任务单数量,
0 未派工数量,case isnull(a.FStatus,0) when 0 then '未关闭' else '关闭' end 状态,
a.FDateS 实际开工时间,a.FDateE 实际完工时间,cast(FRQty as float) 完工数量,
a.fitemid 产品内码,a.FinterID 任务单内码,FTaskDate 派工时间,
a.FTimestamp 时间戳
from M_Task_111 a
inner join AIS20200722111428..t_BOS200000001 b on a.FPKValue=b.FBillNo
inner join AIS20200722111428..t_BOS200000001Entry2 c on a.FentryID=c.FIndex and c.FID=b.FID
inner join BR_CodeInfo b1  on a.fitemid=b1.FItemID and b.fid=b1.FInterID and b1.FEntryID=a.FentryID
inner join BR_InsertStock b2 on FProcFlag='S' and b1.FCode=b2.FCode
inner join ERP1_t_icitem c1 on b1.fitemid=c1.fitemid and c1.FDeleted=0 and c1.FBatchManager=1 and c1.FISKFPeriod=1
inner join ERP1_T_Emp v1 on v1.FNumber=a.FNAME
where ISNULL(a.FSTATUS,0)<>9

派工校验sql

if (select count(1) from M_Task_111 where FNAME='#变量_职员#' and fstatus=0)>0
begin select '#变量_职员#'+'机台产线未关闭派工任务超过1条!' end
if ('#变量_派工数量#'<=0)
begin select '派工数量异常' end
if ('#变量_开始时间#'>'#变量_结束时间#')
begin select '派工时间异常' end

取消派工校验sql

declare @FTime as varchar(50) select @FTime=CAST(FTimestamp AS int) FROM M_Task_001 WHERE FBILLNO='#变量_派工单号#' if(@FTime<>'#变量_时间戳#')
begin	
select '数据已变更，请重新查询后再操作！' 
end
if exists(select 1 FROM M_Task_001 WHERE FBILLNO='#变量_派工单号#' and (isnull(FRQTY,0)+isnull(FRLQTY,0))>0 )
begin	
select '已有汇报数据不允许取消！' 
end

派工值更新sql

update M_Task_001 set FQTQty='#变量_齐套数#' where FBillNo='#变量_派工单号#'
关闭校验sql
declare @FTime as varchar(50) select @FTime=CAST(FTimestamp AS int) FROM M_Task_001 WHERE FBILLNO='#变量_派工单号#' 
if(@FTime<>'#变量_时间戳#')
begin	select '数据已变更，请重新查询后再操作！' 
end
else
begin	
update M_Task_001 set FDateE=convert(varchar(20),getdate(),20),FPQTY=isnull(FRQTY,0)+isnull(FRLQTY,0)
WHERE FBILLNO='#变量_派工单号#'  
end

派工对象维护
职员

select FNumber 代码,FName 名称 from ERP1_t_Emp

6、 质量汇报：扫描人员条码获取该人员任务进行汇报
按员工获取，当前员工的任务信息数据，任务列表至少显示条码信息，物料代码,名称,批次,标签数量,派工数量,完工数量（不良+合格） 不良原因随意 完工数量不允许超过派工数量，完工数量等于派工数量后，自动关闭派工单
任务列表sql

select v1.FName 职员,a.FBillNo 派工单号,c.FCode 条码信息,d.FNumber 物料代码,d.FName 物料名称,c.FBatchNo 批次号,
c.FQty 标签数量,a.FPQty 派工数量,cast(FRQty as float) 完工数量,
FTaskDate 派工时间,'#变量_员工信息#' '操作工'
from M_Task_111 a
inner join BR_InsertStock b  on a.FPKValue=b.fcode  and a.fitemid=b.FItemID and b.FProcFlag='S'
inner join BR_CodeInfo c on b.FCode=c.FCode
inner join ERP1_t_icitem d on c.fitemid=d.fitemid  
inner join ERP1_T_Emp v1 on v1.FNumber=a.FNAME and v1.FNumber='#变量_员工信息#'
where ISNULL(a.FSTATUS,0)<>9 and a.FStatus<1

反写完工数量，超出报错
–完工数量,超出报错

update m_task_111 set frqty=isnull(FRQty,0)+@FQTY+@FLQTY,
@FError=case when isnull(FRQty,0)+@FQTY+@FLQTY>FPQTY then '超出派工数量' else @FError END,
@FNum=case when isnull(FRQty,0)+@FQTY+@FLQTY>FPQTY then '1' else @FNum END
where FBillNo=@FBillNO

反写判断是否完工
–判断是否完工

declare @FPnum as int  --获取派工数量
declare @FRnum as int  --获取完工数量
select @FPnum=cast(cast(FPQty as float) as int) from M_Task_111 where FBILLNO=@FBillNO
select @FRnum=FRQty from M_Task_111 where FBILLNO=@FBillNO
if(@FPnum=@FRnum)
begin
update M_Task_111 set FStatus=1
WHERE FBILLNO=@FBillNO
end