if exists(select * from sysobjects where name='sp_AddObjectsLicense')
  drop procedure sp_AddObjectsLicense
Go

Create procedure sp_AddObjectsLicense 
@License_Code varchar(20),
@Object_Id integer,
@Object_Code varchar(50),
@Object_Name varchar(50),
@Method_Code varchar(20)
As
declare @Method_Id integer
declare @Group_Id integer
declare @User_Id integer
begin  
  /*objects*/  
  if not exists(select * from objects where object_id=@object_id)
    insert into objects(object_id,object_code,object_name_p,object_name,object_order)
    values (@Object_Id,@Object_Code,@Object_Name,@Object_Name,@Object_Id)
  /*objectsLicense*/
  if not exists(select * from objectslicense where object_id=@Object_Id and model_action_name=@License_Code)
    insert into objectslicense(object_id,model_action_name) 
    values (@Object_Id,@License_Code)
  select @Method_Id=method_id from methods where method_code=@method_code
  if @method_code<>''
  begin    
    /*objectmethod*/
    if not exists(select * from objectmethod where object_id=@object_id and method_id=@Method_Id)
      insert into objectmethod(object_id,method_id) 
      values (@Object_Id,@Method_Id)            
    select @Group_Id=group_id from groups where group_code='Administrators'
    /*groupobject*/
    if not exists (select * from groupobject where group_id=@group_id and object_id=@object_id)
      insert into groupobject(group_id,object_id)
      values (@group_id,@object_id) 
    /*groupprivilege*/
    if not exists (select * from groupprivilege where group_id=@group_id and object_id=@object_id and method_id=@method_id)
      insert into groupprivilege(group_id,object_id,method_id)
      values (@group_id,@object_id,@method_id) 
    /*userprivilege*/
    select @User_Id=user_id from users where user_code='Admin'
    if not exists (select * from userprivilege where user_id=@user_id and object_id=@object_id and method_id=@method_id)
      insert into userprivilege(user_id,object_id,method_id)
      values (@user_id,@object_id,@method_id)
  end 
end
Go

--工序基本資料9809增加单价权限
if not exists(select * from objectmethod where object_id=9809 and method_id=8)
  insert into objectmethod(object_id,method_id)
  values (9809,8)
Go
--模具物料结构9807增加单价权限
if not exists(select * from objectmethod where object_id=9807 and method_id=8)
  insert into objectmethod(object_id,method_id)
  values (9807,8)
Go
--調整材料單價9822增加单价权限
if not exists(select * from objectmethod where object_id=9822 and method_id=8)
  insert into objectmethod(object_id,method_id)
  values (9822,8)
Go
--模具成本?算9813增加成本权限
if not exists(select * from objectmethod where object_id=9813 and method_id=8)
  insert into objectmethod(object_id,method_id)
  values (9813,8)
Go
--模具生产计划单9811增加成本权限
if not exists(select * from objectmethod where object_id=9811 and method_id=8)
  insert into objectmethod(object_id,method_id)
  values (9811,8)
Go

--系统管理组分别具有以上权限
if not exists(select * from groupprivilege where group_id=(select group_id from groups where group_code='Administrators') and object_id=9809 and method_id=8)
  insert into groupprivilege(group_id,object_id,method_id)
  select group_id,9809,8 from groups where group_code='Administrators'
Go
if not exists(select * from groupprivilege where group_id=(select group_id from groups where group_code='Administrators') and object_id=9807 and method_id=8)
  insert into groupprivilege(group_id,object_id,method_id)
  select group_id,9807,8 from groups where group_code='Administrators'
Go
if not exists(select * from groupprivilege where group_id=(select group_id from groups where group_code='Administrators') and object_id=9822 and method_id=8)
  insert into groupprivilege(group_id,object_id,method_id)
  select group_id,9822,8 from groups where group_code='Administrators'
Go
if not exists(select * from groupprivilege where group_id=(select group_id from groups where group_code='Administrators') and object_id=9813 and method_id=8)
  insert into groupprivilege(group_id,object_id,method_id)
  select group_id,9813,8 from groups where group_code='Administrators'
Go
if not exists(select * from groupprivilege where group_id=(select group_id from groups where group_code='Administrators') and object_id=9811 and method_id=8)
  insert into groupprivilege(group_id,object_id,method_id)
  select group_id,9811,8 from groups where group_code='Administrators'
Go

--系统管理员分别具有以上权限
if not exists(select * from userprivilege where user_id=(select user_id from users where user_code='Admin') and object_id=9809 and method_id=8)
  insert into userprivilege(user_id,object_id,method_id)
  select user_id,9809,8 from users where user_code='Admin'
Go
if not exists(select * from userprivilege where user_id=(select user_id from users where user_code='Admin') and object_id=9807 and method_id=8)
  insert into userprivilege(user_id,object_id,method_id)
  select user_id,9807,8 from users where user_code='Admin'
Go
if not exists(select * from userprivilege where user_id=(select user_id from users where user_code='Admin') and object_id=9822 and method_id=8)
  insert into userprivilege(user_id,object_id,method_id)
  select user_id,9822,8 from users where user_code='Admin'
Go
if not exists(select * from userprivilege where user_id=(select user_id from users where user_code='Admin') and object_id=9813 and method_id=8)
  insert into userprivilege(user_id,object_id,method_id)
  select user_id,9813,8 from users where user_code='Admin'
Go
if not exists(select * from userprivilege where user_id=(select user_id from users where user_code='Admin') and object_id=9811 and method_id=8)
  insert into userprivilege(user_id,object_id,method_id)
  select user_id,9811,8 from users where user_code='Admin'
Go

if not exists(select * from sysobjects where name='BomImport')
Create table BomImport
(RecNo  nvarchar(10),
 Prodnr nvarchar(20),
 ProdnrUnit nvarchar(10),
 Masternr nvarchar(20),
 Descript nvarchar(40),
 Spec nvarchar(50) ,
 MasternrUnit nvarchar(10),
 UnitCost nvarchar(10),
 Provider nvarchar(50),
 MainQty nvarchar(10),
 LossRate nvarchar(10) 
)
Go

if not exists(select * from sysobjects where name='PK_BomBase' and xtype='PK')
begin
ALTER TABLE [dbo].[BOMBase] ALTER COLUMN [ProdNr] nvarchar(30) NOT NULL
ALTER TABLE [dbo].[BOMBase] ALTER COLUMN [Version] Float NOT NULL
end
GO

if not exists(select * from sysobjects where name='PK_BomBase' and xtype='PK')
begin
ALTER TABLE [dbo].[BOMBase] ADD CONSTRAINT [PK_BOMBase] 
PRIMARY KEY CLUSTERED ([prodnr], [Version])ON [PRIMARY] 
end
Go

sp_AddObjectsLicense 'actmjwh',9814,'','','Print'
Go
 
sp_AddObjectsLicense 'actmjwh',9815,'','','Print'
Go

Update MouldProcessProduct
Set ProdNr=B.ProdNr
From MouldProcessProduct P Join MouldProcessBase B On P.InvSeriesNr=B.InvSeriesNr
Go

Update MouldStdProcessProduct
Set ProdNr=B.ProdNr
From MouldStdProcessProduct P Join MouldStdProcessBase B On P.InvSeriesNr=B.InvSeriesNr
Go

ALTER TABLE [dbo].[MouldProduct]
ALTER COLUMN [StartModifyDate] DateTime
GO

ALTER TABLE [dbo].[MouldProduct]
ALTER COLUMN [EndModifyDate] DateTime
GO

ALTER TABLE [dbo].[MouldMaintenance]
ALTER COLUMN [StartModifyDate] DateTime
GO

ALTER TABLE [dbo].[MouldMaintenance]
ALTER COLUMN [EndModifyDate] DateTime
GO

Update MouldScheduleDetailNew
Set SchedulePercent=100
From MouldScheduleDetailNew
Where (not (InstStartTime is Null))
and (not (InstFinishTime is Null))
and IsNull(SchedulePercent,0)<100
Go

Update MouldScheduleDetailNew
Set InstStartTime=SubString(InvSeriesNr,3,4)+'/'+SubString(InvSeriesNr,7,2)+'/'+SubString(InvSeriesNr,9,2)
From MouldScheduleDetailNew
Where SchedulePercent=100
and (InstStartTime is Null)
Go

Update MouldScheduleDetailNew
Set InstFinishTime=SubString(InvSeriesNr,3,4)+'/'+SubString(InvSeriesNr,7,2)+'/'+SubString(InvSeriesNr,9,2)
From MouldScheduleDetailNew
Where SchedulePercent=100
and (InstFinishTime is Null)
Go

Update MouldScheduleDetailNew
Set InstStartTime=InstFinishTime
From MouldScheduleDetailNew
Where InstStartTime>InstFinishTime
Go

Update MouldScheduleDetailNew
Set WorkDate=SubString(InvSeriesNr,3,4)+'/'+SubString(InvSeriesNr,7,2)+'/'+SubString(InvSeriesNr,9,2)
From MouldScheduleDetailNew
Where (not InstStartTime is Null)
and (WorkDate is Null)
Go

Update MouldScheduleDetailNew
Set WorkDate=InstFinishTime
From MouldScheduleDetailNew
Where WorkDate>InstFinishTime
Go

Update MouldProcessProduct
Set WorkDate=b.WorkDate
From MouldProcessProduct a Join 
(Select Max(SubString(InvSeriesNr,3,4)+'/'+SubString(InvSeriesNr,7,2)+'/'+SubString(InvSeriesNr,9,2)) as WorkDate,soProcessID
From MouldScheduleDetailNew 
Group By soProcessID) b On a.ProcessID=b.soProcessID and a.WorkDate<>b.WorkDate
Go

Update MouldProcessBase
Set Descript=Substring(B.Descript,1,40)
From MouldProcessBase A Join MouldBase B On A.ProdNr=B.ProdNr and A.Descript<>B.Descript
Go

Update MouldProcessProduct
Set SchedulePercent= Case When StdHour=0 Then 0 When InstHour>=StdHour Then 99.0 Else Round(100*InstHour/StdHour,1) End 
Where AutoCalc=1 and SchedulePercent>100
Go

Update MouldProcessProduct
Set WorkDate=InstFinishTime
Where (not InstFinishTime is null)
and (WorkDate>InstFinishTime)
Go

Insert Into GroupObject(Group_ID,Object_ID)
Select G.Group_ID,O.Object_ID
From Groups G , Objects O 
Where Convert(nvarchar,G.Group_ID)+Convert(nvarchar,O.Object_ID)
not in (Select Convert(nvarchar,Group_ID)+Convert(nvarchar,Object_ID)
From GroupObject)
Order By G.Group_ID,O.Object_ID
Go

Insert into GroupPrivilege(Group_ID,Object_ID,Method_ID)
Select G.Group_ID,O.Object_ID,M.Method_ID
From Groups G,Objects O,Methods M
Where Convert(nvarchar,G.Group_ID)+Convert(nvarchar,O.Object_ID)+Convert(nvarchar,M.Method_ID)
Not in (Select Convert(nvarchar,Group_ID)+Convert(nvarchar,Object_ID)+Convert(nvarchar,Method_ID)
From GroupPrivilege)
and G.Group_ID=1
Order By G.Group_ID,O.Object_ID,M.Method_ID
Go

Insert into UserPrivilege(User_ID,Object_ID,Method_ID)
Select U.User_ID,O.Object_ID,M.Method_ID
From Users U,Objects O,Methods M
Where Convert(nvarchar,U.User_ID)+Convert(nvarchar,O.Object_ID)+Convert(nvarchar,M.Method_ID)
Not in (Select Convert(nvarchar,User_ID)+Convert(nvarchar,Object_ID)+Convert(nvarchar,Method_ID)
From UserPrivilege)
and U.User_ID=1
Order By U.User_ID,O.Object_ID,M.Method_ID
Go

Update MouldProcessProduct
Set SchedulePercent=100
Where isnull(SchedulePercent,0)<>100
and (not InstFinishTime is null)
Go


Update WorkCenter
	Set TotalCapacity=Case When IsMachine=0 Then isnull(UnitCapacity,0)*isnull(PersonNum,0) Else isnull(UnitCapacity,0)*isnull(MachinNum,0) End
Go

Delete MouldProvide
Where isnull(InvSeriesNr,'')=''
Go

Update MouldProcessProduct
Set Cost=B.Amt
From MouldProcessProduct a Join (
Select SoProcessID,Sum(Amt) as Amt
From MouldScheduleDetailNew
Where InvSeriesNr in
(Select InvSeriesNr
From MouldScheduleMasterNew 
Where ProcType=2) 
Group By SoProcessID ) B On a.ProcessID=SoProcessID
and IsNull(Cost,0)<>B.Amt
Go

Update MouldCostBase
Set SumAmount=0
Where Isnull(MakeCost,0)+Isnull(RMakeCost,0)+Isnull(MasterCost,0)+Isnull(OtherCost,0)+Isnull(ManageCost,0)=Isnull(SumAmount,0)
Go

if not exists(select * from syscolumns where name='Orders' and id=(select id from sysobjects where name='MouldTechnics'))
  alter table MouldTechnics 
  add Orders SmallInt Default 0
Go


if EXISTS(select * from sysobjects where name='CalcMouldCost')
  drop procedure CalcMouldCost
Go

CREATE PROCEDURE CalcMouldCost
	@ProdNr nVarChar(30),
	@InvSeriesNr nVarChar(13),
	@User_Code nVarChar(20)
AS
	Declare @Descript nVarChar(50)
	Select @Descript=Descript
		From MouldBase
		Where ProdNr=@ProdNr

	Delete MouldCostProduct Where InvSeriesNr=@InvSeriesNr
	SELECT Distinct @InvSeriesNr as InvSeriesNr,' ' as InvNr, Identity(smallint,1,1) as SeriesNr,B.Masternr as ProdNr, 
		isnull(B.Descript,'') Descript,3 as CostType,Sum(isnull(B.PhysicalQty,0)*isnull(B.unicost,0)*Isnull(Weight,1)) as Cost,D.TypeNr,D.ChargeType,Isnull(B.Spec,'') Spec,
		Sum(isnull(B.PhysicalQty,0)) as PhysicalQty,isnull(B.unicost,0) as unicost,isnull(B.MaterialCase,'') as MaterialCase,IsNull(b.ProdClass,'') as ProdClass
		Into #MaterTmpDB
		FROM MouldProcessBOM B,MouldProcessbomBase C,mouldprocessbase D 
		Where B.bominvseriesnr = C.bominvseriesnr and D.invseriesnr=C.invseriesnr and C.prodnr=@ProdNr
		Group By B.Masternr ,isnull(B.Descript,''), D.TypeNr,D.ChargeType,Isnull(B.Spec,''),isnull(B.unicost,0) ,isnull(B.MaterialCase,'') ,IsNull(b.ProdClass,'')
		ORDER BY B.Masternr ,isnull(B.Descript,''),Isnull(B.Spec,'') , D.TypeNr,D.ChargeType
	
	Insert Into MouldCostProduct
		Select NewID(),InvSeriesNr,InvNr,Right('000'+RTrim(LTrim(Str(SeriesNr))),3) as SeriesNr,ProdNr,Descript,CostType,Cost,TypeNr,ChargeType,Spec,PhysicalQty,UniCost,MaterialCase,ProdClass
		From #MaterTmpDB
		Order By SeriesNr

	Drop Table #MaterTmpDB
	
	SELECT @InvSeriesNr as InvSeriesNr,' ' as InvNr, Identity(smallint,1,1) as SeriesNr,b.TechnicsNr as ProdNr,
		C.TechnicsName as Descript,1 as CostType,b.stdprice*(b.insthour+b.OverInstHour) as Cost,a.TypeNr,a.ChargeType,b.Descript as Spec,
		b.insthour+b.OverInstHour as PhysicalQty,b.stdprice as UniCost,' ' as MaterialCase,IsNull(C.WTypeC,'') as ProdClass
		Into #ProductDB1
		FROM MouldProcessBase a inner join MouldProcessProduct b 
		On a.InvseriesNr = b.InvseriesNr Join MouldTechnics C On B.TechnicsNr=C.TechnicsNr
		Where B.MakeType = 1 and a.Prodnr =@ProdNr
		ORDER BY a.InvSeriesNr,b.TechnicsNr	

	Insert Into MouldCostProduct
		Select NewID(),InvSeriesNr,InvNr,Right('000'+RTrim(LTrim(Str(SeriesNr))),3) as SeriesNr,ProdNr,Descript,CostType,Cost,TypeNr,ChargeType,Spec,PhysicalQty,UniCost,MaterialCase,ProdClass
		From #ProductDB1
		Order By SeriesNr

	Drop Table #ProductDB1
	
	SELECT @InvSeriesNr as InvSeriesNr,' ' as InvNr, Identity(smallint,1,1) as SeriesNr,b.TechnicsNr as ProdNr,
		C.TechnicsName as Descript,2 as CostType,b.Cost as Cost,a.TypeNr,a.ChargeType,b.Descript as Spec,
		b.insthour as PhysicalQty,b.stdprice as UniCost,' ' as MaterialCase,'         ' as ProdClass
		Into #ProductDB2
		FROM MouldProcessBase a inner join MouldProcessProduct b 
		On a.InvseriesNr = b.InvseriesNr Join MouldTechnics C On B.TechnicsNr=C.TechnicsNr
		Where B.MakeType = 2 and a.Prodnr =@ProdNr
		ORDER BY a.InvSeriesNr,b.TechnicsNr	

	Insert Into MouldCostProduct
		Select NewID(),InvSeriesNr,InvNr,Right('000'+RTrim(LTrim(Str(SeriesNr))),3) as SeriesNr,ProdNr,Descript,CostType,Cost,TypeNr,ChargeType,Spec,PhysicalQty,UniCost,MaterialCase,ProdClass
		From #ProductDB2
		Order By SeriesNr

	Drop Table #ProductDB2

	IF not EXISTS(Select InvSeriesNr From MouldCostBase Where InvSeriesNr=@InvSeriesNr)
	Begin
		Insert Into MouldCostBase(InvSeriesNr,InvNr,ProdNr,Descript,MakeCost,RMakeCost,MasterCost,ManageCost,OtherCost,Make,MakeDate,UpdateBy, UpdateTime,SumAmount)
			Values(@InvSeriesNr ,' ' ,@ProdNr ,@Descript,0.0 ,0.0 ,0.0 ,0.0 ,0.0,@User_Code ,Convert(nVarChar,GetDate(),111),@User_Code ,GetDate() ,0.0 )
	End


	SELECT @InvSeriesNr as InvSeriesNr,' ' as InvNr, Identity(smallint,1,1) as SeriesNr,b.TechnicsNr as ProdNr,
		C.TechnicsName as Descript,4 as CostType,(IsNull(B.RepeatHour,0)+IsNull(B.RepeatOverHour,0))*B.StdPrice as Cost,a.TypeNr,a.ChargeType,b.Descript as Spec,
		IsNull(B.RepeatHour,0)+IsNull(B.RepeatOverHour,0) as PhysicalQty,b.stdprice as UniCost,' ' as MaterialCase,'         ' as ProdClass
		Into #ProductDB4
		FROM MouldProcessBase a inner join MouldProcessProduct b 
		On a.InvseriesNr = b.InvseriesNr Join MouldTechnics C On B.TechnicsNr=C.TechnicsNr
		Where B.MakeType = 1 and a.Prodnr =@ProdNr and IsNull(B.RepeatHour,0)+IsNull(B.RepeatOverHour,0)>0
		ORDER BY a.InvSeriesNr,b.TechnicsNr	

	Insert Into MouldCostProduct
		Select NewID(),InvSeriesNr,InvNr,Right('000'+RTrim(LTrim(Str(SeriesNr))),3) as SeriesNr,ProdNr,Descript,CostType,Cost,TypeNr,ChargeType,Spec,PhysicalQty,UniCost,MaterialCase,ProdClass
		From #ProductDB4
		Order By SeriesNr

	Drop Table #ProductDB4

	Update MouldCostBase
		Set MakeCost=(Select Sum(Cost) as Cost From MouldCostProduct Where CostType=1 and InvSeriesNr=@InvSeriesNr),
			RMakeCost=(Select Sum(Cost) as Cost From MouldCostProduct Where CostType=2 and InvSeriesNr=@InvSeriesNr),
			MasterCost=(Select Sum(Cost) as Cost From MouldCostProduct Where CostType=3 and InvSeriesNr=@InvSeriesNr),
			RepeatJobCost=(Select Sum(Cost) as Cost From MouldCostProduct Where CostType=4 and InvSeriesNr=@InvSeriesNr)
		Where InvSeriesNr=@InvSeriesNr

GO


if exists(select * from sysobjects where name='sp_WriteMouldMakeProduct')
  drop procedure dbo.sp_WriteMouldMakeProduct
Go

Create procedure dbo.sp_WriteMouldMakeProduct
@State1 nvarchar(50), --开始生产state_id=4
@State2 nvarchar(50)  --生产完成state_id=2
As
begin
  --开始生产
  delete from mouldmakeproduct where state_id=4
  insert into mouldmakeproduct(soinvseriesnr,prodnr,state_flag,state_date,type_nr,state_id)
  select (select top 1 invseriesnr from mouldscheduledetailnew where processinvseriesnr=b.processinvseriesnr
            and workdate=b.workdate),a.prodnr,@state1,b.workdate,a.typenr,4
    from mouldprocessbase a,
    (select distinct processinvseriesnr,min(workdate) as workdate from mouldscheduledetailnew 
       where invseriesnr in (select invseriesnr from mouldschedulemasternew where proctype=1) and workdate is not null
       group by processinvseriesnr) b
  where a.invseriesnr=b.processinvseriesnr
  --生产完成
  delete from mouldmakeproduct where state_id=2 and ltrim(rtrim(prodnr))+'!@#'+ltrim(rtrim(type_nr)) in 
    (select ltrim(rtrim(a.prodnr))+'!@#'+ltrim(rtrim(a.typenr)) 
     from (select c.prodnr,c.typenr
           from mouldprocessproduct a,mouldscheduledetailnew b,mouldprocessbase c 
           where a.islast=1 and a.processid=b.soprocessid and isnull(b.schedulepercent,0)=100
           and b.invseriesnr in (select invseriesnr from mouldschedulemasternew where proctype=1)
           and b.workdate is not null and a.invseriesnr=c.invseriesnr) a)
  insert into mouldmakeproduct(soinvseriesnr,prodnr,state_flag,state_date,type_nr,state_id)
    select b.invseriesnr,c.prodnr,@State2,b.workdate,c.typenr,2 
    from mouldprocessproduct a,mouldscheduledetailnew b,mouldprocessbase c 
    where a.islast=1 and a.processid=b.soprocessid and isnull(b.schedulepercent,0)=100
     and b.invseriesnr in (select invseriesnr from mouldschedulemasternew where proctype=1)
     and b.workdate is not null and a.invseriesnr=c.invseriesnr 
end
Go

/*edit by lsq at 2008-10-30*/
/*由于前端增加了搜索条件，从而将满足条件的模具编号写入中间表NewMouldFollow*/
/*过程处理中需要从NewMouldFollow表筛选数据，模具完工是以生产单statusno决定*/

if not exists(select * from sysobjects where name='NewMouldFollow')
  create table dbo.NewMouldFollow(prodnr nvarchar(30))
Go

if exists(select * from sysobjects where name='sp_NewMouldFollow')
  drop procedure dbo.sp_NewMouldFollow
Go

Create procedure dbo.sp_NewMouldFollow
@StartMouldNr nvarchar(30),
@EndMouldNr nvarchar(30),
@WorkPieceList nvarchar(500)
As
begin
	declare @smouldnr nvarchar(30)
	declare @emouldnr nvarchar(30)
	declare @wp nvarchar(30)
	declare @wps nvarchar(500)
	declare @id int
	declare @maxid int
	/*delete*/
	/*select @smouldnr=min(prodnr) from mouldprocessbase where isnull(schedulepercent,0)<100
	select @smouldnr=max(prodnr) from mouldprocessbase where isnull(schedulepercent,0)<100
	if @StartMouldNr='' select @StartMouldNr=@smouldnr
	if @EndMouldNr='' select @EndMouldNr=@emouldnr*/
	/*end*/
	create table #temp1(id int identity(1,1),
	                    seriesnr int,
	                    prodnr nvarchar(30),
	                    workpiece nvarchar(30),
	                    orderid int,
	                    workcnnr nvarchar(30),
	                    technicsnr nvarchar(30))
	create table #temp2(soprocessid nvarchar(50),	                   
	                    prodnr nvarchar(30),
	                    workpiece nvarchar(30),
	                    workcnnr nvarchar(30),
	                    orderid int,
	                    workdate datetime,
	                    technicsnr nvarchar(30))
	insert into #temp2(soprocessid,workcnnr,workdate,technicsnr)
	  select soprocessid,workcnnr,max(workdate) workdate,technicsnr
      from mouldscheduledetailnew
      where isnull(schedulepercent,0)<100
      /*add*/
      and soprocessid in (select processid from mouldprocessproduct where prodnr in (select prodnr from NewMouldFollow))
      /*end*/
      group by soprocessid,workcnnr,technicsnr                    
	/*只跟踪模具，不跟踪工件*/
	if @WorkPieceList=''
	begin	  
    update #temp2 set prodnr=a.prodnr 
      from mouldprocessproduct a 
      where #temp2.soprocessid=a.processid      
    update #temp2 set orderid=a.orderid
      from workcenter a
      where #temp2.workcnnr=a.workcnnr      
    insert into #temp1(prodnr,orderid) 
      select prodnr,max(orderid) orderid
      from #temp2 
      group by prodnr
      order by orderid
    update #temp1 set workcnnr=a.workcnnr
      from workcenter a
      where #temp1.orderid=a.orderid  
    update #temp1 set technicsnr=a.technicsnr
      from #temp2 a
      where #temp1.orderid=a.orderid
      and #temp1.prodnr=a.prodnr
    /*按工作中心生成流水号*/ 
	  declare curid cursor for select distinct orderid from #temp1 order by orderid
	  open curid
	  fetch next from curid into @id
	  select @maxid=0
	  while @@fetch_status=0
	  begin	  
	    update #temp1 set seriesnr=id-@maxid where orderid=@id
	    select @maxid=max(id) from #temp1 where orderid=@id
	    fetch next from curid into @id	  	  
	  end
	  close curid
	  deallocate curid 	   
    select orderid,seriesnr,prodnr,workpiece,workcnnr,technicsnr,
      (select distinct owner_code from mouldbase where prodnr=a.prodnr) owner_code,
      (select distinct descript from mouldbase where prodnr=a.prodnr) mould_descript,
      (select distinct unitqty from mouldbase where prodnr=a.prodnr) unit_qty,
      (select distinct smodate from mouldbase where prodnr=a.prodnr) order_date,
      (select distinct workcnname from workcenter where workcnnr=a.workcnnr) workcnname,
      (select max(planstartdate) from mouldprocessbase where prodnr=a.prodnr) planfinish_date,
      (select distinct technicsname from mouldtechnics where technicsnr=a.technicsnr) technics_descript, 
      (select max(descript) from mouldprocessproduct where prodnr=a.prodnr and technicsnr=a.technicsnr) technics_memo
      from #temp1 a
      /*edit*/
      /*where prodnr>=@StartMouldNr and prodnr<=@EndMouldNr*/
      where prodnr in (select prodnr from NewMouldFollow)
      /*end*/
      order by orderid,seriesnr,prodnr               
	end
	else
	/*跟踪模具和工件*/
	begin
	  create table #tmpwp(workpiece nvarchar(30))
	  select @wps=@WorkPieceList
	  while charindex(',',@wps)>0
	  begin
	  	select @wp=substring(@wps,1,charindex(',',@wps)-1)
	  	insert into #tmpwp(workpiece) values (@wp)
	  	select @wps=substring(@wps,charindex(',',@wps)+1,500)
	  end
	  insert into #tmpwp(workpiece) values (@wps)	
	    
    update #temp2 set prodnr=a.prodnr,workpiece=a.workpiece 
      from mouldprocessproduct a 
      where #temp2.soprocessid=a.processid      
    update #temp2 set orderid=a.orderid
      from workcenter a
      where #temp2.workcnnr=a.workcnnr      
    insert into #temp1(prodnr,workpiece,orderid) 
      select prodnr,workpiece,max(orderid) orderid
      from #temp2 
      group by prodnr,workpiece
      order by orderid,workpiece     
    update #temp1 set workcnnr=a.workcnnr
      from workcenter a
      where #temp1.orderid=a.orderid  
    update #temp1 set technicsnr=a.technicsnr
      from #temp2 a
      where #temp1.orderid=a.orderid
      and #temp1.prodnr=a.prodnr 
    /*按工作中心和工件生成流水号*/ 
	  declare curid cursor for select distinct orderid,workpiece from #temp1 order by orderid,workpiece
	  open curid
	  fetch next from curid into @id,@wp
	  select @maxid=0
	  while @@fetch_status=0
	  begin	  
	    update #temp1 set seriesnr=id-@maxid where orderid=@id and workpiece=@wp
	    select @maxid=max(id) from #temp1 where orderid=@id and workpiece=@wp
	    fetch next from curid into @id,@wp	  	  
	  end
	  close curid
	  deallocate curid	  
    select orderid,seriesnr,prodnr,workpiece,workcnnr,technicsnr,
      (select distinct owner_code from mouldbase where prodnr=a.prodnr) owner_code,
      (select distinct descript from mouldbase where prodnr=a.prodnr) mould_descript,
      (select distinct unitqty from mouldbase where prodnr=a.prodnr) unit_qty,
      (select distinct smodate from mouldbase where prodnr=a.prodnr) order_date,
      (select distinct workcnname from workcenter where workcnnr=a.workcnnr) workcnname,
      (select max(planstartdate) from mouldprocessbase where prodnr=a.prodnr) planfinish_date,
      (select distinct technicsname from mouldtechnics where technicsnr=a.technicsnr) technics_descript, 
      (select max(descript) from mouldprocessproduct where prodnr=a.prodnr and technicsnr=a.technicsnr) technics_memo
      from #temp1 a
      /*edit*/
      /*where prodnr>=@StartMouldNr and prodnr<=@EndMouldNr*/
      where prodnr in (select prodnr from NewMouldFollow)
      /*end*/
      and workpiece in (select workpiece from #tmpwp)
      order by orderid,workpiece,seriesnr,prodnr        
	end		
end
Go  

if not exists(select * from syscolumns where name='JitId' and id=(select id from sysobjects where name='mouldmakejit'))
  alter table mouldmakejit
  add JitId nvarchar(50)
Go

if not exists(select * from syscolumns where name='ScheduleInvseriesnr' and id=(select id from sysobjects where name='mouldmakejit'))
  alter table mouldmakejit
  add ScheduleInvseriesnr nvarchar(15)
Go

if not exists(select * from sysobjects where name='MouldSchedulePart')
  create table MouldSchedulePart(ProcessId nvarchar(50),P_NO nvarchar(20),
    MachineNr nvarchar(20),WorkPieceNo nvarchar(30),JitId nvarchar(50),
    StartDate datetime,WorkDate datetime,StateKind int,InstHours float)
Go

alter procedure dbo.sp_NewMouldFollow
@StartMouldNr nvarchar(30),
@EndMouldNr nvarchar(30),
@WorkPieceList nvarchar(500)
As
begin
  declare @smouldnr nvarchar(30)
  declare @emouldnr nvarchar(30)
  declare @wp nvarchar(30)
  declare @wps nvarchar(500)
  declare @id int
  declare @maxid int
  /*delete*/
    /*select @smouldnr=min(prodnr) from mouldprocessbase where isnull(schedulepercent,0)<100
    select @smouldnr=max(prodnr) from mouldprocessbase where isnull(schedulepercent,0)<100
    if @StartMouldNr='' select @StartMouldNr=@smouldnr
    if @EndMouldNr='' select @EndMouldNr=@emouldnr*/
  /*end*/
  create table #temp1(id int identity(1,1),
                      seriesnr int,
	              prodnr nvarchar(30) ,
	              workpiece nvarchar(30) default '' ,
	              orderid int,
	              workcnnr nvarchar(30) ,
	              technicsnr nvarchar(30) ,
                      flag int) 
  create table #temp2(soprocessid nvarchar(50) ,	                   
	              prodnr nvarchar(30) ,
	              workpiece nvarchar(30) ,
	              workcnnr nvarchar(30) ,
	              orderid int,
	              workdate datetime,
	              technicsnr nvarchar(30) )
  create table #temp3(id int identity(1,1),
                      seriesnr int,
	              prodnr nvarchar(30) ,
	              workpiece nvarchar(30) default '' ,
	              orderid int,
	              workcnnr nvarchar(30) ,
	              technicsnr nvarchar(30) ,
                      flag int) 
  insert into #temp2(soprocessid,workcnnr,workdate,technicsnr)
    select soprocessid,workcnnr,max(workdate) workdate,technicsnr
    from mouldscheduledetailnew
    where isnull(schedulepercent,0)<100
    /*add*/
    and soprocessid in (select processid from mouldprocessproduct where prodnr in (select prodnr from NewMouldFollow))
    /*end*/
    group by soprocessid,workcnnr,technicsnr       
  /*只跟踪模具，不跟踪工件*/
  if @WorkPieceList=''
  begin	  
    update #temp2 set prodnr=a.prodnr 
      from mouldprocessproduct a 
      where #temp2.soprocessid=a.processid      
    update #temp2 set orderid=a.orderid
      from workcenter a
      where #temp2.workcnnr=a.workcnnr      
    insert into #temp1(prodnr,orderid,flag) 
      select prodnr,max(orderid) orderid,1
      from #temp2 
      group by prodnr
      order by orderid
    /*add by lsq at 2008-11-10 如果在日报表中进度都为100，就取计划单中的下一个工作中心*/
    create table #temp4(prodnr nvarchar(30) ,	              
	                orderid int,	              
	                technicsnr nvarchar(30) )
    insert into #temp4(prodnr,orderid,technicsnr)
      select a.prodnr,isnull(b.orderid,0) orderid,a.technicsnr from mouldprocessproduct a,workcenter b
      where a.workcnnr=b.workcnnr and isnull(a.schedulepercent,0)=0 and prodnr in (select prodnr from NewMouldFollow)
      and a.prodnr not in (select prodnr from (select prodnr,isnull(schedulepercent,0) schedulepercent from mouldprocessproduct) c 
                             where schedulepercent<>100 and schedulepercent>0)
    insert into #temp1(prodnr,orderid,flag)        
    select prodnr,min(orderid),0 from #temp4 group by prodnr 
    update #temp1 set technicsnr=(select distinct technicsnr from #temp4 where prodnr=#temp1.prodnr and orderid=#temp1.orderid)      
      where flag=0     
    /*end*/                   
    update #temp1 set workcnnr=a.workcnnr
      from workcenter a
      where #temp1.orderid=a.orderid  
    update #temp1 set technicsnr=a.technicsnr
      from #temp2 a
      where #temp1.orderid=a.orderid
      and #temp1.prodnr=a.prodnr and flag=1     
    insert into #temp3(prodnr,workpiece,orderid,workcnnr,technicsnr,flag)
      select prodnr,workpiece,orderid,workcnnr,technicsnr,flag from #temp1 order by orderid,prodnr
    /*按工作中心生成流水号*/ 
    declare curid cursor for select distinct orderid from #temp3 order by orderid
    open curid
    fetch next from curid into @id
    select @maxid=0
    while @@fetch_status=0
    begin	  
      update #temp3 set seriesnr=id-@maxid where orderid=@id
      select @maxid=max(id) from #temp3 where orderid=@id
      fetch next from curid into @id	  	  
    end
    close curid
    deallocate curid 	   
    select orderid,seriesnr,prodnr,workpiece,workcnnr,technicsnr,flag,
      (select distinct owner_code from mouldbase where prodnr=a.prodnr) owner_code,
      (select distinct descript from mouldbase where prodnr=a.prodnr) mould_descript,
      (select distinct unitqty from mouldbase where prodnr=a.prodnr) unit_qty,
      (select distinct smodate from mouldbase where prodnr=a.prodnr) order_date,
      (select distinct workcnname from workcenter where workcnnr=a.workcnnr) workcnname,
      (select max(planstartdate) from mouldprocessbase where prodnr=a.prodnr) planfinish_date,
      (select distinct technicsname from mouldtechnics where technicsnr=a.technicsnr) technics_descript, 
      (select max(descript) from mouldprocessproduct where prodnr=a.prodnr and technicsnr=a.technicsnr) technics_memo
    from #temp3 a
    /*edit*/
    /*where prodnr>=@StartMouldNr and prodnr<=@EndMouldNr*/
    where prodnr in (select prodnr from NewMouldFollow)
    /*end*/
    order by orderid,seriesnr,prodnr               
  end
  else
  /*跟踪模具和工件*/
  begin
    create table #tmpwp(workpiece nvarchar(30) )
    select @wps=@WorkPieceList
    while charindex(',',@wps)>0
    begin
      select @wp=substring(@wps,1,charindex(',',@wps)-1)
      insert into #tmpwp(workpiece) values (@wp)
        select @wps=substring(@wps,charindex(',',@wps)+1,500)
    end
    insert into #tmpwp(workpiece) values (@wps)		    
    update #temp2 set prodnr=a.prodnr,workpiece=a.workpiece 
      from mouldprocessproduct a 
      where #temp2.soprocessid=a.processid      
    update #temp2 set orderid=a.orderid
      from workcenter a
      where #temp2.workcnnr=a.workcnnr      
    insert into #temp1(prodnr,workpiece,orderid,flag) 
      select prodnr,workpiece,max(orderid) orderid,1
      from #temp2 
      group by prodnr,workpiece
      order by orderid,workpiece  
    /*add by lsq at 2008-11-12 如果在日报表中进度都为100，就取计划单中的下一个工作中心*/
    create table #temp5(prodnr nvarchar(30) ,
                        workpiece nvarchar(30) ,	              
	                orderid int,	              
	                technicsnr nvarchar(30) )
    insert into #temp5(prodnr,workpiece,orderid,technicsnr)
      select a.prodnr,isnull(a.workpiece,''),isnull(b.orderid,0) orderid,a.technicsnr from mouldprocessproduct a,workcenter b
      where a.workcnnr=b.workcnnr and isnull(a.schedulepercent,0)=0 and prodnr in (select prodnr from NewMouldFollow)
      and isnull(workpiece,'') in (select workpiece from #tmpwp)
      and a.prodnr+isnull(a.workpiece,'') not in (select pw from (select prodnr+isnull(workpiece,'') as pw,isnull(schedulepercent,0) schedulepercent                                                  
                                                  from mouldprocessproduct) c where schedulepercent<>100 and schedulepercent>0)    
    insert into #temp1(prodnr,workpiece,orderid,flag)        
    select prodnr,workpiece,min(orderid),0 from #temp5 group by prodnr,workpiece 
    update #temp1 set technicsnr=(select distinct technicsnr COLLATE Chinese_Taiwan_Stroke_CI_AS from #temp5 where prodnr=#temp1.prodnr COLLATE Chinese_Taiwan_Stroke_CI_AS and workpiece=#temp1.workpiece COLLATE Chinese_Taiwan_Stroke_CI_AS
      and orderid=#temp1.orderid) where flag=0     
    /*end*/                      
    update #temp1 set workcnnr=a.workcnnr
      from workcenter a
      where #temp1.orderid=a.orderid  
    update #temp1 set technicsnr=a.technicsnr
      from #temp2 a
      where #temp1.orderid=a.orderid
      and #temp1.prodnr=a.prodnr 
    insert into #temp3(prodnr,workpiece,orderid,workcnnr,technicsnr,flag)
      select prodnr,workpiece,orderid,workcnnr,technicsnr,flag from #temp1 order by orderid,workpiece    
    /*按工作中心和工件生成流水号*/ 
    declare curid cursor for select distinct orderid,workpiece from #temp3 order by orderid,workpiece
    open curid
    fetch next from curid into @id,@wp
    select @maxid=0
    while @@fetch_status=0
    begin	  
      update #temp3 set seriesnr=id-@maxid where orderid=@id and workpiece=@wp
      select @maxid=max(id) from #temp3 where orderid=@id and workpiece=@wp
      fetch next from curid into @id,@wp	  	  
    end
    close curid
    deallocate curid	  
    select orderid,seriesnr,prodnr,workpiece,workcnnr,technicsnr,flag,
      (select distinct owner_code from mouldbase where prodnr=a.prodnr COLLATE Chinese_Taiwan_Stroke_CI_AS) owner_code,
      (select distinct descript from mouldbase where prodnr=a.prodnr COLLATE Chinese_Taiwan_Stroke_CI_AS) mould_descript,
      (select distinct unitqty from mouldbase where prodnr=a.prodnr COLLATE Chinese_Taiwan_Stroke_CI_AS) unit_qty,
      (select distinct smodate from mouldbase where prodnr=a.prodnr COLLATE Chinese_Taiwan_Stroke_CI_AS) order_date,
      (select distinct workcnname from workcenter where workcnnr=a.workcnnr COLLATE Chinese_Taiwan_Stroke_CI_AS) workcnname,
      (select max(planstartdate) from mouldprocessbase where prodnr=a.prodnr COLLATE Chinese_Taiwan_Stroke_CI_AS) planfinish_date,
      (select distinct technicsname from mouldtechnics where technicsnr=a.technicsnr COLLATE Chinese_Taiwan_Stroke_CI_AS) technics_descript, 
      (select max(descript) from mouldprocessproduct where prodnr=a.prodnr and technicsnr=a.technicsnr COLLATE Chinese_Taiwan_Stroke_CI_AS) technics_memo
    from #temp3 a
    /*edit*/
    /*where prodnr>=@StartMouldNr and prodnr<=@EndMouldNr*/
    where prodnr in (select prodnr COLLATE Chinese_Taiwan_Stroke_CI_AS from NewMouldFollow)
    /*end*/
    and workpiece in (select workpiece COLLATE Chinese_Taiwan_Stroke_CI_AS from #tmpwp)
    order by orderid,workpiece,seriesnr,prodnr        
  end		
end
Go  

if exists(select * from sysobjects where name='sp_WriteProcessHeadSchedule')
  drop procedure sp_WriteProcessHeadSchedule
Go
 
Create procedure sp_WriteProcessHeadSchedule
@invseriesnr nvarchar(13)
As
declare @PlanTotalHour numeric(15,2)
declare @TotalRate numeric(15,2)
declare @TotalRec int
declare @Total100Rec int
begin  
  select @PlanTotalHour=sum(isnull(stdhour,0)) from mouldprocessproduct where invseriesnr=@invseriesnr
  --更新模具生产计划单总进度
  Select @TotalRate=isnull(Sum(FactRate),0) From
    (Select *,FactRate=Case When isnull(SchedulePercent,0)>=100 Then StdHourRate 
                            Else HourRate*StdHourRate End  From
      (Select ProcessID,isnull(InstHour,0)+isnull(OverInstHour,0) as InstHour,isnull(StdHour,0) as StdHour,
       isnull(SchedulePercent,0) as SchedulePercent,b.TotalStdHour,
       HourRate=Case When isnull(StdHour,0)<=0 Then 0 
         When isnull(InstHour,0)+isnull(OverInstHour,0)>isnull(StdHour,0) Then 0.99
         Else (isnull(InstHour,0)+isnull(OverInstHour,0))/isnull(StdHour,0) End,
       StdHourRate=Case When b.TotalStdHour<=0 Then 0 Else isnull(StdHour,0)*100/b.TotalStdHour End 
     From MouldProcessProduct Join 
     (Select Sum(isnull(StdHour,0)) as TotalStdHour From MouldProcessProduct Where InvSeriesnr=@invseriesnr) B 
     On InvSeriesnr=@invseriesnr) C ) D 
  if @TotalRate>100 select @TotalRate=100  
  Update MouldProcessBase Set SchedulePercent=@TotalRate Where Invseriesnr=@invseriesnr
  --更新模具生产计划单状态
  Select @TotalRec=Count(*) From MouldProcessProduct Where Invseriesnr=@invseriesnr
  Select @Total100Rec=Count(*) From MouldProcessProduct Where isnull(SchedulePercent,0)=100 and Invseriesnr=@invseriesnr
  if @TotalRec=@Total100Rec
  begin
    Update MouldProcessBase Set StatusNo=2,SchedulePercent=100,signDate=GetDate() Where Invseriesnr=@invseriesnr
    update mouldbase set laststatedate=convert(varchar(12),getdate(),112),state='1' 
      where prodnr=(select prodnr from mouldprocessbase where invseriesnr=@invseriesnr)
  end
  else
  begin
    if @TotalRate>=100 select @TotalRate=99
    Update MouldProcessBase Set StatusNo=1,SchedulePercent=@TotalRate Where Invseriesnr=@invseriesnr
  end
end
Go

if exists(select * from sysobjects where name='sp_WriteJitToSchedule')
  drop procedure sp_WriteJitToSchedule
Go
 
Create procedure sp_WriteJitToSchedule
@Id integer --终端号
As
declare @jitguid nvarchar(50)--即时表ID(回滚需要)
declare @scheduleinvseriesnr nvarchar(13)--日报单序号(回滚需要)
declare @jitid integer
declare @processid nvarchar(50)
declare @detailid nvarchar(50)
declare @workdatetime datetime
declare @workdate datetime
declare @i int
declare @PartCnt int
declare @PartOverCnt int
declare @s nvarchar(3)
declare @maker nvarchar(20)
declare @p_no nvarchar(20)
declare @machinenr nvarchar(20)
declare @prodnr nvarchar(30)--模具编号
declare @processinvseriesnr nvarchar(13)--计划单序号
declare @invseriesnr nvarchar(13)--日报表单据序号
declare @processworkpieceno nvarchar(50)--计划单表体工件
declare @workpieceno nvarchar(50)--采集表工件
declare @schedulepercent numeric(15,2)--日报进度比例
declare @inststarttime datetime--日报实际开始时间
declare @insthours numeric(15,2)--日报实际工时
declare @instfinishtime datetime--日报实际完成时间
declare @planTtlhours numeric(15,2)--计划单表体计划工时
declare @instTtlhours numeric(15,2)--计划单表体实际工时
declare @IsMachine int--工作中心是否以机台统计工时
declare @IsOver int--是否完工
declare @IsWrite int--是否写生产日报
declare @statekind int--状态分类
declare @ProcType int--日报种类（1：正常日报 3：重工日报）
declare @InstHour numeric(15,2)--日报正班工时
declare @OverHour numeric(15,2)--日报加班工时
declare @ProcessIdInstHour numeric(15,2)--计划单表体正班工时
declare @ProcessIdOverHour numeric(15,2)--计划单表体加班工时
declare @ProcessIdRepeatInstHour numeric(15,2)--计划单表体重工正班工时
declare @ProcessIdRepeatOverHour numeric(15,2)--计划单表体重工加班工时
declare @vWorkPiece nvarchar(30)
begin      
  select @jitguid=newid()
  select @jitid=max(id) from mouldmakejit where haveconvert=0 and pcid=@id
  /*add by lsq at 2009-04-04 处理虚拟工件问题*/
  select @vWorkPiece='virtual'
  update MouldMakeJit set workpieceno='' where workpieceno=@vWorkPiece 
  update MouldMakeJit set workpiecenonew='' where workpiecenonew=@vWorkPiece
  select @IsWrite=IsWrite,@IsMachine=IsMachine,@workdatetime=workdate,@p_no=p_no,@machinenr=machinenr,@processid=processid, 
    @IsOver=isover,@StateKind=statekind,@workpieceno=workpieceno,@prodnr=prodnr from MouldMakeJit where id=@jitid 
  if @StateKind in (1,2) select @ProcType=1 else select @ProcType=3
  select @processworkpieceno=workpieceno from mouldprocessproduct where processid=@processid
  if (@IsWrite=1) and (@processid<>'')
  begin    
    --生成日报表单据序号(重工单单据序号)
    select @workdate=convert(datetime,convert(nvarchar(10),@workdatetime,112))    
    if not exists(select * from mouldschedulemasternew where makedate=@workdate)
      select @invseriesnr='SM'+convert(nvarchar(10),@workdate,112)+'001'
    else
    begin 
      select @invseriesnr=isnull(max(invseriesnr),'') from mouldschedulemasternew where makedate=@workdate
      select @i=convert(int,substring(@invseriesnr,11,3))+1
      select @s=convert(nvarchar(3),@i)
      if len(ltrim(rtrim(@s)))=1 select @s='00'+ltrim(rtrim(@s))
      if len(ltrim(rtrim(@s)))=2 select @s='0'+ltrim(rtrim(@s))
      select @invseriesnr='SM'+convert(nvarchar(10),@workdate,112)+@s
    end  
    select @scheduleinvseriesnr=@invseriesnr       
    --制单人
    select @maker=@p_no                                                                          
    --向日报主表插入一条记录
    insert into mouldschedulemasternew(invseriesnr,maker,makedate,updateby,updatetime,isworkcenter,proctype)
      values (@invseriesnr,@maker,@workdate,@maker,@workdate,0,@ProcType)
    select @detailid=newid()
    if @IsMachine=0
      select @inststarttime=max(workdate) from MouldMakeJit where processid=@processid 
        and haveconvert=0 and p_no=@p_no and iswrite=0 
    else
      select @inststarttime=max(workdate) from MouldMakeJit where processid=@processid 
        and haveconvert=0 and machinenr=@machinenr and iswrite=0    
    --日报实际工时
    select @insthours=convert(numeric(15,2),datediff(minute,@inststarttime,@workdatetime)/60.00) 
    --add by lsq at 2008-12-24 向零件工时表插入一条记录
    if @workpieceno<>@processworkpieceno
    begin
      insert into MouldSchedulePart(ProcessId,P_NO,MachineNr,WorkPieceNo,JitId,StartDate,WorkDate,StateKind,InstHours)    
      values (@processid,@p_no,@machinenr,@workpieceno,@jitguid,@inststarttime,@workdatetime,@StateKind,@insthours)
    end
    --日报实际开始时间(如果计划单表体有对应的实际开始时间就取计划单的，如果没有就取采集表中IsWrite=0的workdate)
    if (@ProcType=1) and exists(select * from mouldprocessproduct where (inststarttime is not null) and processid=@processid)    
      select @inststarttime=inststarttime from mouldprocessproduct where processid=@processid       
    --日报实际完成时间    
    if (@IsOver=1) and (@workpieceno=@processworkpieceno)
      select @instfinishtime=@workdatetime
    --日报进度比例
    select @planTtlhours=isnull(stdhour,0.00) from mouldprocessproduct where processid=@processid    
    select @instTtlhours=isnull(insthour,0.00)+isnull(overinsthour,0.00)+@insthours 
      from mouldprocessproduct where processid=@processid --计划单表体实际工时
    if @planTtlhours=0 select @schedulepercent=0 else
      select @schedulepercent=convert(numeric(15,2),@instTtlhours/@planTtlhours)*100.00
    if (@IsOver=1) and (@workpieceno=@processworkpieceno)
      select @schedulepercent=100
    --add by lsq at 2008-12-05 如果是粗排细跟，需要考虑散件的实际完成时间和进度比例
    if (@IsOver=1) and (@ProcType=1) and (@workpieceno<>@processworkpieceno)
    begin
      select @partcnt=count(distinct workpieceno) from MouldPartDetail where prodnr=@prodnr and 
        productid=(select distinct productid from MouldPartProduct where prodnr=@prodnr and workpieceno=@processworkpieceno)
      select @partovercnt=count(*) from mouldmakejit where processid=@processid and prodnr=@prodnr
        and isover=1 and statekind in (1,2)
      if @partcnt=@partovercnt 
      begin
        select @schedulepercent=100
        select @instfinishtime=@workdatetime
      end
    end     
    if @ProcType=3 select @schedulepercent=100.00
    --日报正班工时
    if @statekind in (1,3) select @insthour=@insthours,@overhour=0.00     
    --日报加班工时
    if @statekind in (2,4) select @insthour=0.00,@overhour=@insthours
    --向日报从表插入一条记录  
    insert into mouldscheduledetailnew(invseriesnr,seriesnr,technicsnr,schedulepercent,scheduleinfo2,
      planstarttime,planfinishtime,plandays,instfinishtime,inststarttime,instdays,processinvseriesnr,
      schedulepercentm,soprocessid,workcnnr,workdate,finishqty,badqty,amt,overhour,detailid,workpiece,prodnr)
    select @invseriesnr,'001',technicsnr,@schedulepercent,1,
      (select distinct planstarttime from mouldprocessproduct where processid=@processid),
      (select distinct planfinishtime from mouldprocessproduct where processid=@processid),
      (select distinct stdhour from mouldprocessproduct where processid=@processid),
      @instfinishtime,@inststarttime,@insthour,processinvseriesnr,0,processid,workcnnr,
      @workdatetime,1,0,1,@overhour,@detailid,(select distinct workpiece from workpiece where workpieceno=MouldMakeJit.workpiecenonew),
      prodnr from MouldMakeJit where id=@jitid 
    if @IsMachine=0
      --向日报人员表插入一条记录     
      insert into mouldscheduleitem(itemid,invseriesnr,seriesnr,itemnr,workcenternr,p_no,instdays,
        soprocessid,overinsthour,detailid)  
      select newid(),@invseriesnr,'001','001',
        (select workcnnr from MouldMakeJit where id=@jitid),@p_no,@insthour,@processid,@overhour,@detailid
    else
      --向日报机台表插入一条记录     
      insert into mouldschedulemachine(itemid,invseriesnr,seriesnr,itemnr,workcenternr,machinnr,instdays,
        soprocessid,overinsthour,detailid)  
      select newid(),@invseriesnr,'001','001',
        (select workcnnr from MouldMakeJit where id=@jitid),@machinenr,@insthour,@processid,@overhour,@detailid
    --计划单表体正班工时
    if @statekind=1
      select @ProcessIdInstHour=isnull(insthour,0.00)+@insthours from mouldprocessproduct where processid=@processid
    else
      select @ProcessIdInstHour=isnull(insthour,0.00) from mouldprocessproduct where processid=@processid
    --计划单表体加班工时
    if @statekind=2
      select @ProcessIdOverHour=isnull(overinsthour,0.00)+@insthours from mouldprocessproduct where processid=@processid
    else
      select @ProcessIdOverHour=isnull(overinsthour,0.00) from mouldprocessproduct where processid=@processid    
    --计划单表体重工正班工时    
    if @statekind=3
      select @ProcessIdRepeatInstHour=isnull(repeathour,0.00)+@insthours from mouldprocessproduct where processid=@processid
    else
      select @ProcessIdRepeatInstHour=isnull(repeathour,0.00) from mouldprocessproduct where processid=@processid  
    --计划单表体重工加班工时
    if @statekind=4
      select @ProcessIdRepeatOverHour=isnull(repeatoverhour,0.00)+@insthours from mouldprocessproduct where processid=@processid
    else
      select @ProcessIdRepeatOverHour=isnull(repeatoverhour,0.00) from mouldprocessproduct where processid=@processid    
    --更新mouldprocessproduct表  
    update mouldprocessproduct set inststarttime=@inststarttime,instfinishtime=@instfinishtime,
      schedulepercent=@schedulepercent,insthour=@ProcessIdInstHour,overinsthour=@ProcessIdOverHour,
      repeathour=@ProcessIdRepeatInstHour,repeatoverhour=@ProcessIdRepeatOverHour,scheduleinfo2=1
      where processid=@processid
    --更新MouldMakeJit表
    if @IsMachine=0 
      update MouldMakeJit set haveconvert=1,jitid=@jitguid,scheduleinvseriesnr=@scheduleinvseriesnr
        where processid=@processid and p_no=@p_no and haveconvert=0 and pcid=@id             
    else
      update MouldMakeJit set haveconvert=1,jitid=@jitguid,scheduleinvseriesnr=@scheduleinvseriesnr 
        where processid=@processid and machinenr=@machinenr and haveconvert=0 and pcid=@id
    --更新重工表头
    if @statekind in (3,4) exec sp_WriteMakeRepeat
    --更新计划单表头进度
    if @statekind in (1,2)
    begin
      select @processinvseriesnr=invseriesnr from mouldprocessproduct where processid=@processid
      exec sp_WriteProcessHeadSchedule @processinvseriesnr
    end
  end  
end
Go  


update mouldprocessproduct
set stdprice=t.price
from mouldprocessproduct a join Mouldtechnics t on a.TechnicsNr=T.TechnicsNr
Where isnull(stdprice,0)=0
GO

Update MouldVindicate
Set WeihuTTL=B.WeihuTTL
From MouldVindicate a Join
(Select a.ProdNr,b.RepairItem,Count(*) as WeihuTTL
From MouldMaintenance a Join MouldReceiveRepair B On a.invseriesnr=B.invseriesnr and a.SeriesNr=b.SeriesNr
Where Type=2
Group By a.ProdNr,b.RepairItem) B
On a.ProdNr=B.ProdNr
and a.ProjectNr=B.RepairItem
and a.WeihuTTL<>B.WeihuTTL
Go

Update MouldRegular
Set Times=B.WeihuTTL
From MouldRegular a Join
(Select a.ProdNr,b.RepairItem,Count(*) as WeihuTTL
From MouldMaintenance a Join MouldReceiveRepair B On a.invseriesnr=B.invseriesnr and a.SeriesNr=b.SeriesNr
Where Type=3
Group By a.ProdNr,b.RepairItem) B
On a.ProdNr=B.ProdNr
and a.RepairItem=B.RepairItem
and a.Times<>B.WeihuTTL
Go

if exists(select * from sysobjects where name='sp_WriteMouldProcess')
  drop procedure sp_WriteMouldProcess
Go
 
Create procedure sp_WriteMouldProcess
As

Update MouldProcessProduct
Set InstHour=d.InstDays,
OverInstHour=d.OverHour
From MouldProcessProduct a Join
(Select a.SoProcessID,Sum(Isnull(InstDays,0)) as InstDays,Sum(Isnull(OverHour,0)) as OverHour
From mouldscheduledetailnew a Join MouldScheduleMasterNew b On a.InvSeriesNr=b.InvSeriesNr
Where b.ProcType=1
Group By SoProcessID
) d On a.ProcessID=d.SoProcessID
Where (a.InstHour<>d.InstDays) or (a.OverInstHour<>d.OverHour)

Update MouldProcessProduct
Set RepeatHour=d.InstDays,
RepeatOverHour=d.OverHour
From MouldProcessProduct a Join
(Select a.SoProcessID,Sum(Isnull(InstDays,0)) as InstDays,Sum(Isnull(OverHour,0)) as OverHour
From mouldscheduledetailnew a Join MouldScheduleMasterNew b On a.InvSeriesNr=b.InvSeriesNr
Where b.ProcType=3
Group By SoProcessID
) d On a.ProcessID=d.SoProcessID
Where (a.RepeatHour<>d.InstDays) or (a.RepeatOverHour<>d.OverHour)

Update MouldProcessProduct
Set Cost=StdPrice*(InstHour+OverInstHour+RepeatHour+RepeatOverHour)
Where (Cost<>StdPrice*(InstHour+OverInstHour))
and ((InstHour+OverInstHour)>0)

Update MouldProcessProduct
Set InstStartTime=b.InstStartTime
From MouldProcessProduct a Join
(Select SoProcessID,Min(Isnull(InstStartTime,GetDate())) as InstStartTime
From MouldScheduleDetailNew
Group By SoProcessID) b On a.ProcessID=b.SoProcessID
Where a.InstStartTime<>b.InstStartTime

Update MouldProcessProduct
Set InstFinishTime=b.InstFinishTime
From MouldProcessProduct a Join
(Select SoProcessID,Max(Isnull(InstFinishTime,'2009-01-01')) as InstFinishTime
From MouldScheduleDetailNew
Group By SoProcessID) b On a.ProcessID=b.SoProcessID
Where (a.InstFinishTime<>b.InstFinishTime)
and (b.InstFinishTime<>'2009-01-01')

Update MouldProcessProduct
Set WorkDate=b.WorkDate
From MouldProcessProduct a Join
(Select SoProcessID,Max(Isnull(WorkDate,'2009-01-01')) as WorkDate
From MouldScheduleDetailNew
Group By SoProcessID) b On a.ProcessID=b.SoProcessID
Where (a.WorkDate<>b.WorkDate)
and (b.WorkDate<>'2009-01-01')

Update MouldProcessProduct
Set SchedulePercent=b.SchedulePercent
From MouldProcessProduct a Join
(Select SoProcessID,Max(Isnull(SchedulePercent,0)) as SchedulePercent
From MouldScheduleDetailNew
Group By SoProcessID) b On a.ProcessID=b.SoProcessID
Where (a.SchedulePercent<>b.SchedulePercent)
GO

sp_WriteMouldProcess
Go

if exists(select * from sysobjects where name='sp_MmsDataReport')
  drop procedure sp_MmsDataReport
Go

Create procedure sp_MmsDataReport
@s_date datetime,
@e_date datetime
As
begin
  declare @MouldBaseCnt int--模具资料
  select @MouldBaseCnt=count(*) from mouldbase 
    where makedate>=@s_date and makedate<=@e_date 
  declare @StdProcessCnt int--样版工艺单
  select @StdProcessCnt=count(*) from mouldstdprocessbase
    where makedate>=@s_date and makedate<=@e_date 
  declare @ProcessCnt int--生产计划单
  select @ProcessCnt=count(*) from mouldprocessbase
    where makedate>=@s_date and makedate<=@e_date 
  declare @ScheduleCnt int--生产日报表
  select @ScheduleCnt=count(*) from mouldschedulemasternew
    where makedate>=@s_date and makedate<=@e_date 
  declare @NoramlCnt int--正常生产
  select @NoramlCnt=count(*) from mouldschedulemasternew
    where makedate>=@s_date and makedate<=@e_date and proctype=1
  declare @RepeatJobCnt int--重工生产
  select @RepeatJobCnt=count(*) from mouldschedulemasternew
    where makedate>=@s_date and makedate<=@e_date and proctype=3
  declare @CommitOverCnt int--委外完工
  select @CommitOverCnt=count(*) from mouldschedulemasternew
    where makedate>=@s_date and makedate<=@e_date and proctype=2
  declare @AberrentCnt int--生产异常
  select @AberrentCnt=count(*) from mouldschedulemasternew
    where makedate>=@s_date and makedate<=@e_date and proctype=4
  declare @TechnicsOverCnt int--工艺完工
  select @TechnicsOverCnt=count(*) from mouldschedulemasternew
    where makedate>=@s_date and makedate<=@e_date and proctype=5
  declare @TestNoticeCnt int--试模通知单
  select @TestNoticeCnt=count(*) from mouldtestnoticebase
    where makedate>=@s_date and makedate<=@e_date
  declare @TestReportCnt int--试模报告单
  select @TestReportCnt=count(*) from mouldtestreportbase
    where makedate>=@s_date and makedate<=@e_date
  declare @BomCnt int--模具物料结构
  select @BomCnt=count(*) from bombase
    where makedate>=@s_date and makedate<=@e_date
  declare @ProcessBomCnt int--模具生产材料单
  select @ProcessBomCnt=count(*) from MouldProcessBomBase
    where makedate>=@s_date and makedate<=@e_date
  declare @CostCnt int--成本分析单
  select @CostCnt=count(*) from mouldcostbase
    where makedate>=@s_date and makedate<=@e_date
  declare @IsCurrDayReport int--是否填写当天生产日报表
  select @IsCurrDayReport=IsCurrDayReport from companyinfo
  declare @MouldStartOverCnt int--模具应该起工未起工
  declare @TaskStartOverCnt int--任务应该起工未起工
  declare @MouldFinishOverCnt int--模具应该完工未完工
  declare @TaskFinishOverCnt int--任务应该完工未完工
  if @IsCurrDayReport=1
  begin
    select @MouldStartOverCnt=count(distinct prodnr) from mouldprocessproduct 
      where Isnull(inststarttime,GetDate())>planstarttime and isnull(SchedulePercent,0)<100 and maketype='1'
      and prodnr in (select prodnr from mouldprocessbase where isnull(SchedulePercent,0)<100 and makedate>=@s_date and makedate<=@e_date)
    select @TaskStartOverCnt=count(*) from mouldprocessproduct 
      where Isnull(inststarttime,GetDate())>planstarttime and isnull(SchedulePercent,0)<100 and maketype='1'
      and prodnr in (select prodnr from mouldprocessbase where isnull(SchedulePercent,0)<100 and makedate>=@s_date and makedate<=@e_date)                                                  
    select @MouldFinishOverCnt=count(distinct prodnr) from mouldprocessproduct 
      where Isnull(instfinishtime,GetDate())>planfinishtime and isnull(SchedulePercent,0)<100 and maketype='1'
      and prodnr in (select prodnr from mouldprocessbase where isnull(SchedulePercent,0)<100 and makedate>=@s_date and makedate<=@e_date)
    select @TaskFinishOverCnt=count(*) from mouldprocessproduct 
      where Isnull(instfinishtime,GetDate())>planfinishtime and isnull(SchedulePercent,0)<100 and maketype='1'
      and prodnr in (select prodnr from mouldprocessbase where isnull(SchedulePercent,0)<100 and makedate>=@s_date and makedate<=@e_date)                                                    
  end
  else
  begin
    select @MouldStartOverCnt=count(distinct prodnr) from mouldprocessproduct 
      where Isnull(inststarttime,dateadd(day,-1,getdate()))>planstarttime and isnull(SchedulePercent,0)<100 and maketype='1'
      and prodnr in (select prodnr from mouldprocessbase where isnull(SchedulePercent,0)<100 and makedate>=@s_date and makedate<=@e_date)
    select @TaskStartOverCnt=count(*) from mouldprocessproduct 
      where Isnull(inststarttime,dateadd(day,-1,getdate()))>planstarttime and isnull(SchedulePercent,0)<100 and maketype='1'
      and prodnr in (select prodnr from mouldprocessbase where isnull(SchedulePercent,0)<100 and makedate>=@s_date and makedate<=@e_date)
    select @MouldFinishOverCnt=count(distinct prodnr) from mouldprocessproduct 
      where Isnull(instfinishtime,dateadd(day,-1,getdate()))>planfinishtime and isnull(SchedulePercent,0)<100 and maketype='1'
      and prodnr in (select prodnr from mouldprocessbase where isnull(SchedulePercent,0)<100 and makedate>=@s_date and makedate<=@e_date)
    select @TaskFinishOverCnt=count(*) from mouldprocessproduct 
      where Isnull(instfinishtime,dateadd(day,-1,getdate()))>planfinishtime and isnull(SchedulePercent,0)<100 and maketype='1'
      and prodnr in (select prodnr from mouldprocessbase where isnull(SchedulePercent,0)<100 and makedate>=@s_date and makedate<=@e_date)
  end                                                            
  declare @NotWorkDays numeric(15,2)--休息天数
  select @NotWorkDays=count(*) from FactoryCalendar
    where isbreak=1 and [date]>=@s_date and [date]<=@e_date
  declare @WorkHours numeric(15,2)--生产总工时
  select @WorkHours=sum(isnull(instdays,0))+sum(isnull(overhour,0)) from mouldscheduledetailnew
    where invseriesnr in (select invseriesnr from mouldschedulemasternew where proctype in (1,3) and makedate>=@s_date and makedate<=@e_date) 
  select @MouldBaseCnt MouldBaseCnt,@StdProcessCnt StdProcessCnt, @ProcessCnt ProcessCnt,
    @ScheduleCnt ScheduleCnt,@NoramlCnt NoramlCnt,@RepeatJobCnt RepeatJobCnt,@CommitOverCnt CommitOverCnt, 
    @AberrentCnt AberrentCnt,@TechnicsOverCnt TechnicsOverCnt,@TestNoticeCnt TestNoticeCnt,@TestReportCnt TestReportCnt, 
    @BomCnt BomCnt,@ProcessBomCnt ProcessBomCnt,@CostCnt CostCnt,@MouldStartOverCnt MouldStartOverCnt,@TaskStartOverCnt TaskStartOverCnt,
    @MouldFinishOverCnt MouldFinishOverCnt,@TaskFinishOverCnt TaskFinishOverCnt,@NotWorkDays NotWorkDays,@WorkHours WorkHours,@s_date s_date,@e_date e_date
  --按工作中心class_flag=1 --按模具编号class_flag=2
  if not exists(select * from sysobjects where name='MmsDataReport')
    create table MmsDataReport(class_flag int,prodnr nvarchar(30),instdays numeric(15,2),overhour numeric(15,2),
      repeatinstdays numeric(15,2),repeatoverhour numeric(15,2),MouldStartOverCnt int,TaskStartOverCnt int,
      MouldFinishOverCnt int,TaskFinishOverCnt int)
  else
    delete from MmsDataReport 
  --按工作中心
  --1正常生产
  select workcnnr,sum(isnull(instdays,0)) instdays,sum(isnull(overhour,0)) overhour
    into #wcnormal from mouldscheduledetailnew 
    where invseriesnr in (select invseriesnr from mouldschedulemasternew where proctype=1 and makedate>=@s_date and makedate<=@e_date)
    group by workcnnr
  insert into MmsDataReport(class_flag,prodnr,instdays,overhour)
    select 1,workcnnr,instdays,overhour from #wcnormal
  insert into MmsDataReport(class_flag,prodnr)
    select 1,workcnnr from workcenter where workcnnr not in (select prodnr from MmsDataReport  where class_flag=1)
  --2重工生产
  select workcnnr,sum(isnull(instdays,0)) instdays,sum(isnull(overhour,0)) overhour
    into #wcrepeatjob from mouldscheduledetailnew 
    where invseriesnr in (select invseriesnr from mouldschedulemasternew where proctype=3 and makedate>=@s_date and makedate<=@e_date)
    group by workcnnr
  update MmsDataReport set repeatinstdays=a.instdays,repeatoverhour=a.overhour 
    from #wcrepeatjob a where class_flag=1 and MmsDataReport.prodnr=a.workcnnr
  --3.1应起工而未起工模具
  --3.2应起工而未起工任务
  --4.1应完工而未完工模具
  --4.2应完工而未完工任务
  if @IsCurrDayReport=1
  begin
    select workcnnr,count(distinct prodnr) cnt into #wcsomould from mouldprocessproduct 
      where Isnull(inststarttime,GetDate())>planstarttime and isnull(SchedulePercent,0)<100 and maketype='1'
      and prodnr in (select prodnr from mouldprocessbase where isnull(SchedulePercent,0)<100 and makedate>=@s_date and makedate<=@e_date)
      group by workcnnr
    update MmsDataReport set MouldStartOverCnt=a.cnt from #wcsomould a where class_flag=1 and MmsDataReport.prodnr=a.workcnnr
    select workcnnr,count(*) cnt into #wcsotask from mouldprocessproduct 
      where Isnull(inststarttime,GetDate())>planstarttime and isnull(SchedulePercent,0)<100 and maketype='1'
      and prodnr in (select prodnr from mouldprocessbase where isnull(SchedulePercent,0)<100 and makedate>=@s_date and makedate<=@e_date)
      group by workcnnr
    update MmsDataReport set TaskStartOverCnt=a.cnt from #wcsotask a where class_flag=1 and MmsDataReport.prodnr=a.workcnnr
    select workcnnr,count(distinct prodnr) cnt into #wcfomould from mouldprocessproduct 
      where Isnull(instfinishtime,GetDate())>planfinishtime and isnull(SchedulePercent,0)<100 and maketype='1'
      and prodnr in (select prodnr from mouldprocessbase where isnull(SchedulePercent,0)<100 and makedate>=@s_date and makedate<=@e_date)
      group by workcnnr 
    update MmsDataReport set MouldFinishOverCnt=a.cnt from #wcfomould a where class_flag=1 and MmsDataReport.prodnr=a.workcnnr
    select workcnnr,count(*) cnt into #wcfotask from mouldprocessproduct 
      where Isnull(instfinishtime,GetDate())>planfinishtime and isnull(SchedulePercent,0)<100 and maketype='1'
      and prodnr in (select prodnr from mouldprocessbase where isnull(SchedulePercent,0)<100 and makedate>=@s_date and makedate<=@e_date)
      group by workcnnr
    update MmsDataReport set TaskFinishOverCnt=a.cnt from #wcfotask a where class_flag=1 and MmsDataReport.prodnr=a.workcnnr
  end
  else
  begin
    select workcnnr,count(distinct prodnr) cnt into #wcsomould0 from mouldprocessproduct 
      where Isnull(inststarttime,dateadd(day,-1,getdate()))>planstarttime and isnull(SchedulePercent,0)<100 and maketype='1'
      and prodnr in (select prodnr from mouldprocessbase where isnull(SchedulePercent,0)<100 and makedate>=@s_date and makedate<=@e_date)
      group by workcnnr 
    update MmsDataReport set MouldStartOverCnt=a.cnt from #wcsomould0 a where class_flag=1 and MmsDataReport.prodnr=a.workcnnr
    select workcnnr,count(*) cnt into #wcsotask0 from mouldprocessproduct 
      where Isnull(inststarttime,dateadd(day,-1,getdate()))>planstarttime and isnull(SchedulePercent,0)<100 and maketype='1'
      and prodnr in (select prodnr from mouldprocessbase where isnull(SchedulePercent,0)<100 and makedate>=@s_date and makedate<=@e_date)
      group by workcnnr
    update MmsDataReport set TaskStartOverCnt=a.cnt from #wcsotask0 a where class_flag=1 and MmsDataReport.prodnr=a.workcnnr
    select workcnnr,count(distinct prodnr) cnt into #wcfomould0 from mouldprocessproduct 
      where Isnull(instfinishtime,dateadd(day,-1,getdate()))>planfinishtime and isnull(SchedulePercent,0)<100 and maketype='1'
      and prodnr in (select prodnr from mouldprocessbase where isnull(SchedulePercent,0)<100 and makedate>=@s_date and makedate<=@e_date)
      group by workcnnr 
    update MmsDataReport set MouldFinishOverCnt=a.cnt from #wcfomould0 a where class_flag=1 and MmsDataReport.prodnr=a.workcnnr
    select workcnnr,count(*) cnt into #wcfotask0 from mouldprocessproduct 
      where Isnull(instfinishtime,dateadd(day,-1,getdate()))>planfinishtime and isnull(SchedulePercent,0)<100 and maketype='1'
      and prodnr in (select prodnr from mouldprocessbase where isnull(SchedulePercent,0)<100 and makedate>=@s_date and makedate<=@e_date)
      group by workcnnr
    update MmsDataReport set TaskFinishOverCnt=a.cnt from #wcfotask0 a where class_flag=1 and MmsDataReport.prodnr=a.workcnnr
  end      
  --按模具编号
  --1正常生产
  select prodnr,sum(isnull(instdays,0)) instdays,sum(isnull(overhour,0)) overhour
    into #mbnormal from (select instdays,overhour,
      (select prodnr from mouldprocessproduct where processid=mouldscheduledetailnew.soprocessid) prodnr
       from mouldscheduledetailnew 
       where invseriesnr in (select invseriesnr from mouldschedulemasternew where proctype=1 and makedate>=@s_date and makedate<=@e_date)) a
    group by prodnr
  insert into MmsDataReport(class_flag,prodnr,instdays,overhour)
    select 2,prodnr,instdays,overhour from #mbnormal
  insert into MmsDataReport(class_flag,prodnr)
    select 2,prodnr from mouldbase where prodnr not in (select prodnr from MmsDataReport where class_flag=2)
      and makedate<=@e_date
  --2重工生产
  select prodnr,sum(isnull(instdays,0)) instdays,sum(isnull(overhour,0)) overhour
    into #mbrepeatjob from (select instdays,overhour,
      (select prodnr from mouldprocessproduct where processid=mouldscheduledetailnew.soprocessid) prodnr
       from mouldscheduledetailnew 
       where invseriesnr in (select invseriesnr from mouldschedulemasternew where proctype=3 and makedate>=@s_date and makedate<=@e_date)) a
    group by prodnr 
  update MmsDataReport set repeatinstdays=a.instdays,repeatoverhour=a.overhour 
    from #mbrepeatjob a where class_flag=2 and MmsDataReport.prodnr=a.prodnr
  --3应起工而未起工任务
  --4应完工而未完工任务
  if @IsCurrDayReport=1
  begin
    select prodnr,count(*) cnt into #mbsotask from mouldprocessproduct 
      where Isnull(inststarttime,GetDate())>planstarttime and isnull(SchedulePercent,0)<100 and maketype='1'
      and prodnr in (select prodnr from mouldprocessbase where isnull(SchedulePercent,0)<100 and makedate>=@s_date and makedate<=@e_date)
      group by prodnr
    update MmsDataReport set TaskStartOverCnt=a.cnt from #mbsotask a where class_flag=2 and MmsDataReport.prodnr=a.prodnr
    select prodnr,count(*) cnt into #mbfotask from mouldprocessproduct 
      where Isnull(instfinishtime,GetDate())>planfinishtime and isnull(SchedulePercent,0)<100 and maketype='1'
      and prodnr in (select prodnr from mouldprocessbase where isnull(SchedulePercent,0)<100 and makedate>=@s_date and makedate<=@e_date)
      group by prodnr
    update MmsDataReport set TaskFinishOverCnt=a.cnt from #mbfotask a where class_flag=2 and MmsDataReport.prodnr=a.prodnr
  end
  else
  begin
    select prodnr,count(*) cnt into #mbsotask0 from mouldprocessproduct 
      where Isnull(inststarttime,dateadd(day,-1,getdate()))>planstarttime and isnull(SchedulePercent,0)<100 and maketype='1'
      and prodnr in (select prodnr from mouldprocessbase where isnull(SchedulePercent,0)<100 and makedate>=@s_date and makedate<=@e_date)
      group by prodnr
    update MmsDataReport set TaskStartOverCnt=a.cnt from #mbsotask0 a where class_flag=2 and MmsDataReport.prodnr=a.prodnr
    select prodnr,count(*) cnt into #mbfotask0 from mouldprocessproduct 
      where Isnull(instfinishtime,dateadd(day,-1,getdate()))>planfinishtime and isnull(SchedulePercent,0)<100 and maketype='1'
      and prodnr in (select prodnr from mouldprocessbase where isnull(SchedulePercent,0)<100 and makedate>=@s_date and makedate<=@e_date)
      group by prodnr
    update MmsDataReport set TaskFinishOverCnt=a.cnt from #mbfotask0 a where class_flag=2 and MmsDataReport.prodnr=a.prodnr
  end  
  update MmsDataReport set instdays=0 where instdays is null   
  update MmsDataReport set overhour=0 where overhour is null
  update MmsDataReport set repeatinstdays=0 where repeatinstdays is null
  update MmsDataReport set repeatoverhour=0 where repeatoverhour is null
  update MmsDataReport set MouldStartOverCnt=0 where MouldStartOverCnt is null 
  update MmsDataReport set TaskStartOverCnt=0 where TaskStartOverCnt is null
  update MmsDataReport set MouldFinishOverCnt=0 where MouldFinishOverCnt is null
  update MmsDataReport set TaskFinishOverCnt=0 where TaskFinishOverCnt is null
end
Go

if exists(select * from sysobjects where name='sp_Bom2Process')
  drop procedure sp_Bom2Process
Go

Create procedure sp_Bom2Process
(@prodnr nvarchar(30),
 @flag int,
 @currentuser nvarchar(15),
 @one2one int)
As
begin
  if @flag in (1,2) 
  begin
    delete from mouldprocessproduct where invseriesnr in (select invseriesnr from mouldprocessbase
      where prodnr=@prodnr and typenr='01')
    delete from mouldprocessbase where prodnr=@prodnr and typenr='01' 	
  end	
  declare @workpieceno nvarchar(20)
  declare @workpiece nvarchar(40)
  declare @invseriesnr nvarchar(13)
  declare @makedate datetime
  declare @descript nvarchar(60)
  declare @ProjectName nvarchar(50)
  declare @SMoInvseriesnr nvarchar(13)
  declare @smakedate nvarchar(8)
  declare @s nvarchar(13)
  declare @i int
  select @makedate=convert(datetime,convert(nvarchar(10),getdate(),112))
  select @smakedate=convert(nvarchar(8),@makedate,112)
  if @flag in (1,0)
  begin 
    if @one2one=1
    begin	               
      declare bom2 cursor for select masternr,descript from bomproduct where prodnr=@prodnr and version=1.0 
        and masternr in (select masternr from Bom2ProcessMasterNr where prodnr=@prodnr and isconvert=1)	
      open bom2
      fetch next from bom2 into @workpieceno,@workpiece
      while @@fetch_status=0
      begin
        if exists(select * from Bom2ProcessTechnicsNr where prodnr=@prodnr and masternr=@workpieceno and isconvert=1)
        begin	
	
          select @s=isnull(max(invseriesnr),'') from mouldprocessbase where substring(invseriesnr,3,8)=@smakedate	
          if @s='' select @s='DP'+@smakedate+'001' 
          else
          begin
            select @i=convert(int,substring(@s,11,3))
            select @i=@i+1
            if @i<10 select @s='DP'+@smakedate+'00'+convert(nvarchar(1),@i)	
            if (@i>=10) and (@i<100) select @s='DP'+@smakedate+'0'+convert(nvarchar(2),@i)
            if @i>=100 select @s='DP'+@smakedate+convert(nvarchar(3),@i)
          end
          select @invseriesnr=@s        
          select distinct @descript=descript,@projectname=projectname,@SMoInvseriesnr=SMoInvseriesnr 
            from mouldbase where prodnr=@prodnr      

          insert into mouldprocessbase(invseriesnr,prodnr,descript,prodqty,make,makedate,statusno,updateby,
            updatetime,schedulepercent,typenr,chargetype,statusid,ProjectName,SMoInvseriesnr)
          select @invseriesnr,@prodnr,(select distinct descript from mouldbase where prodnr=@prodnr),1,
            @currentuser,@makedate,1,@currentuser,@makedate,0,'01','01',0,@ProjectName,@SMoInvseriesnr 

          Create table #tmpTechnics(id int identity(1,1),TechnicsNr nvarchar(20),Seriesnr nvarchar(3),
            descript nvarchar(50),stdhour float,stdprice float,workcnnr nvarchar(13),maketype tinyint,
            autocalc bit,worktimeallot nvarchar(20),islast bit)         
          insert into #tmpTechnics(TechnicsNr,descript,stdhour,stdprice,workcnnr,maketype,autocalc,worktimeallot,islast) 
          select technicsnr,descript,workhr,price,workcnnr,proctype,autocalc,
            (select distinct worktimeallot from workcenter where workcnnr=mouldtechnics.workcnnr),islast 
          from mouldtechnics
          where technicsnr in 
            (select technicsnr from Bom2ProcessTechnicsNr where prodnr=@prodnr and masternr=@workpieceno and isconvert=1)
          Update #tmpTechnics set Seriesnr='00'+convert(nvarchar(1),id) where id<10
          Update #tmpTechnics set Seriesnr='0'+convert(nvarchar(2),id) where id>=10 and id<100
          Update #tmpTechnics set Seriesnr=convert(nvarchar(3),id) where id>=100  
     
          insert into mouldprocessproduct(invseriesnr,seriesnr,prodnr,technicsnr,descript,stdhour,stdprice,
            qty,workcnnr,maketype,processid,workpiece,tmpseriesnr,autocalc,worktimeallot,repeatprice,insthour,
            workpieceno,islast,qtyunit)
          select @invseriesnr,seriesnr,@prodnr,technicsnr,descript,stdhour,stdprice,1,workcnnr,maketype,
            newid(),@workpiece,seriesnr,autocalc,worktimeallot,stdprice,0,@workpieceno,islast,1 
          from #tmpTechnics
          drop table #tmpTechnics 
        end 
        fetch next from bom2 into @workpieceno,@workpiece	
      end                          
      close bom2
      deallocate bom2
    end
    if (@one2one=0) and exists(select * from Bom2ProcessTechnicsNr where prodnr=@prodnr and isconvert=1 and 
      masternr in (select masternr from Bom2ProcessMasterNr where prodnr=@prodnr and isconvert=1)) 
    begin

      select @s=isnull(max(invseriesnr),'') from mouldprocessbase where substring(invseriesnr,3,8)=@smakedate	
      if @s='' select @s='DP'+@smakedate+'001' 
      else
      begin
        select @i=convert(int,substring(@s,11,3))
        select @i=@i+1
        if @i<10 select @s='DP'+@smakedate+'00'+convert(nvarchar(1),@i)	
        if (@i>=10) and (@i<100) select @s='DP'+@smakedate+'0'+convert(nvarchar(2),@i)
        if @i>=100 select @s='DP'+@smakedate+convert(nvarchar(3),@i)
      end
      select @invseriesnr=@s        
      select distinct @descript=descript,@projectname=projectname,@SMoInvseriesnr=SMoInvseriesnr 
        from mouldbase where prodnr=@prodnr      

      insert into mouldprocessbase(invseriesnr,prodnr,descript,prodqty,make,makedate,statusno,updateby,
        updatetime,schedulepercent,typenr,chargetype,statusid,ProjectName,SMoInvseriesnr)
      select @invseriesnr,@prodnr,(select distinct descript from mouldbase where prodnr=@prodnr),1,
        @currentuser,@makedate,1,@currentuser,@makedate,0,'01','01',0,@ProjectName,@SMoInvseriesnr       

      Create table #tmpTechnics0(id int identity(1,1),TechnicsNr nvarchar(20),Seriesnr nvarchar(3),
        descript nvarchar(50),stdhour float,stdprice float,workcnnr nvarchar(13),maketype tinyint,
        autocalc bit,worktimeallot nvarchar(20),islast bit,workpieceno nvarchar(20),workpiece nvarchar(40))         
      insert into #tmpTechnics0(TechnicsNr,descript,stdhour,stdprice,workcnnr,maketype,autocalc,
        worktimeallot,islast,workpieceno,workpiece) 
      select a.technicsnr,a.descript,a.workhr,a.price,a.workcnnr,a.proctype,a.autocalc,
        (select distinct worktimeallot from workcenter where workcnnr=a.workcnnr),islast,b.masternr,
        (select distinct descript from bomproduct where prodnr=b.prodnr and masternr=b.masternr) 
      from mouldtechnics a,Bom2ProcessTechnicsNr b
      where b.prodnr=@prodnr and b.isconvert=1 and b.technicsnr=a.technicsnr
        and b.masternr in (select masternr from Bom2ProcessMasterNr where prodnr=@prodnr and isconvert=1)
      order by b.masternr  
      Update #tmpTechnics0 set Seriesnr='00'+convert(nvarchar(1),id) where id<10
      Update #tmpTechnics0 set Seriesnr='0'+convert(nvarchar(2),id) where id>=10 and id<100
      Update #tmpTechnics0 set Seriesnr=convert(nvarchar(3),id) where id>=100  
  
      insert into mouldprocessproduct(invseriesnr,seriesnr,prodnr,technicsnr,descript,stdhour,stdprice,
        qty,workcnnr,maketype,processid,workpiece,tmpseriesnr,autocalc,worktimeallot,repeatprice,insthour,
        workpieceno,islast,qtyunit)
      select @invseriesnr,seriesnr,@prodnr,technicsnr,descript,stdhour,stdprice,1,workcnnr,maketype,
        newid(),workpiece,seriesnr,autocalc,worktimeallot,stdprice,0,workpieceno,islast,1 
      from #tmpTechnics0
      drop table #tmpTechnics0 	
    end
    delete from Bom2ProcessMasterNr where prodnr=@prodnr    
    delete from Bom2ProcessTechnicsNr where prodnr=@prodnr
  end
end
Go  

ALTER TABLE [dbo].[MouldProcessProduct]
ALTER COLUMN [StdHour] Float Null
GO

declare @constraint_name varchar(50) 
select @constraint_name =b.name from syscolumns a,sysobjects b where a.id=object_id('BomProduct') and b.id=a.cdefault 
and a.name='Weight' and b.name like 'DF%' 
declare @cmd varchar(100) 
select @cmd='alter table BomProduct drop constraint '+ @constraint_name 
exec(@cmd) 
alter table BomProduct 
ADD CONSTRAINT   DF__bomproduc__Weigh__11207638    DEFAULT   ( 1)   FOR   Weight 
GO 

declare @constraint_name varchar(50) 
select @constraint_name =b.name from syscolumns a,sysobjects b where a.id=object_id('mouldprocessbom') and b.id=a.cdefault 
and a.name='Weight' and b.name like 'DF%' 
declare @cmd varchar(100) 
select @cmd='alter table mouldprocessbom drop constraint '+ @constraint_name 
exec(@cmd) 
alter table mouldprocessbom 
ADD CONSTRAINT   DF__mouldproc__Weigh__12149A71    DEFAULT   ( 1)   FOR   Weight 
GO 

Update BomProduct
Set Weight=1
Where (IsNull(Weight,0))=0
Go

Update mouldprocessbom
Set Weight=1
Where (IsNull(Weight,0))=0
Go
