存储过程实例(二)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BOM_COUNT_PRICE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BOM_COUNT_PRICE]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Bom_Find_BomParts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Bom_Find_BomParts]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Bom_Find_BomStruc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Bom_Find_BomStruc]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Bom_Get_Bom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Bom_Get_Bom]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Bom_Get_BomDetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Bom_Get_BomDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Bom_Get_BomPrice]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Bom_Get_BomPrice]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ExPort_EF]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ExPort_EF]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Get_Bom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Get_Bom]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_EF]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_EF]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_App_Type]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_App_Type]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Copy_Bom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Copy_Bom]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Copy_JobHead_Detail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Copy_JobHead_Detail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Delete_JobHead]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Delete_JobHead]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Edit_Bom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Edit_Bom]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Edit_Colorant]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Edit_Colorant]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Edit_JobDetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Edit_JobDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Edit_JobHead]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Edit_JobHead]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Edit_M_Serial]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Edit_M_Serial]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Edit_Mould]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Edit_Mould]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Edit_P_Bom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Edit_P_Bom]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Edit_Part]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Edit_Part]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Edit_Pla_Col]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Edit_Pla_Col]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Edit_Plastic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Edit_Plastic]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Edit_Pr_Bom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Edit_Pr_Bom]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Edit_Prod_ORA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Edit_Prod_ORA]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Edit_Prod_ORC]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Edit_Prod_ORC]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Find_Bom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Find_Bom]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Find_Colorant]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Find_Colorant]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Find_Data]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Find_Data]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Find_JobDetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Find_JobDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Find_JobHead]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Find_JobHead]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Find_M_Serial]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Find_M_Serial]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Find_Mould]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Find_Mould]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Find_Mould_Serial]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Find_Mould_Serial]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Find_P_Bom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Find_P_Bom]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Find_Part]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Find_Part]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Find_Plastic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Find_Plastic]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Find_Pr_Bom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Find_Pr_Bom]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Find_Prod_ORA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Find_Prod_ORA]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Find_Prod_ORB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Find_Prod_ORB]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Find_Prod_ORC]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Find_Prod_ORC]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Find_Rep_SKZ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Find_Rep_SKZ]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Find_Rpt_Plastic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Find_Rpt_Plastic]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Find_Temp_Prod_ORC]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Find_Temp_Prod_ORC]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Find_tabBzgzd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Find_tabBzgzd]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Find_tabBzxqb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Find_tabBzxqb]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_Bom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_Bom]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_Colorant]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_Colorant]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_JobDetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_JobDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_JobHead]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_JobHead]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_MRP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_MRP]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_M_Serial]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_M_Serial]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_Mould]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_Mould]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_P_Bom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_P_Bom]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_Part]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_Part]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_Plastic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_Plastic]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_Pr_Bom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_Pr_Bom]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_Prod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_Prod]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_Prod_ORA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_Prod_ORA]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_Prod_ORB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_Prod_ORB]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_Prod_ORC]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_Prod_ORC]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_Rep_Plastic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_Rep_Plastic]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_Rep_SKZ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_Rep_SKZ]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_Temp_ORA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_Temp_ORA]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_Temp_ORB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_Temp_ORB]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_Temp_ORC]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_Temp_ORC]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_tabBzgzd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_tabBzgzd]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_tabBzxqb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_tabBzxqb]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_New_tabJjGzdHh]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_New_tabJjGzdHh]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Rep_Pla_Col]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Rep_Pla_Col]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Rep_Plastic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Rep_Plastic]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Rep_Prod_ORC]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Rep_Prod_ORC]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Truncate_Table]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Truncate_Table]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Truncate_tabJjGzdHh]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Truncate_tabJjGzdHh]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_UpDate_tabBzxqb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_UpDate_tabBzxqb]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_UpFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_UpFile]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Update_Temp_ORC]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Update_Temp_ORC]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_Update_tabBzgzd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_Update_tabBzgzd]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_UserLogin]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_UserLogin]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Get_truncate_tabBzxqb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Get_truncate_tabBzxqb]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Mis_Part]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Mis_Part]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Month_PartPrice]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Month_PartPrice]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Month_PartPrice1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Month_PartPrice1]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_New_UpFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_New_UpFile]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Out_EF]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Out_EF]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Part_Price]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Part_Price]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Part_Serial]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Part_Serial]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Print_PartWeight]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Print_PartWeight]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Print_tabBzgzd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Print_tabBzgzd]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Read_Part]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Read_Part]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Read_PartWeight]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Read_PartWeight]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_Week_PartPrice]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_Week_PartPrice]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_tabBzgzd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_tabBzgzd]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pageTest]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[pageTest]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BomDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BomDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BomOldCost]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BomOldCost]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BomPart]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BomPart]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BomPrice]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BomPrice]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BomStruc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BomStruc]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Count_PartWeight]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Count_PartWeight]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DelPart]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DelPart]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EF]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EF]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[JobOrder]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[JobOrder]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Part]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Part]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PartCost]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PartCost]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PartWeight]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PartWeight]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prod_ORA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Prod_ORA]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpFile]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[UpFile]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tabBzgzd]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tabBzgzd]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tabBzxqb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tabBzxqb]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tabJjGzdHh]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tabJjGzdHh]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tabMrp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tabMrp]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tb]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblAppFile]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblAppFile]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblColorant]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblColorant]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCompany]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCompany]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblItem_Mas]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblItem_Mas]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblJobDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblJobDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblJobHead]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblJobHead]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblM_Serial]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblM_Serial]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblMould]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblMould]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPDaily]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPDaily]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblP_Bom]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblP_Bom]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPart]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPart]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPassword]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPassword]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPlastic]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPlastic]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPr_Bom]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPr_Bom]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblProd_ORA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblProd_ORA]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblProd_ORB]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblProd_ORB]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblProd_ORC]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblProd_ORC]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblRep_Colorant]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblRep_Colorant]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblRep_Part]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblRep_Part]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblRep_Pla_Col]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblRep_Pla_Col]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblRep_Plastic]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblRep_Plastic]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblRep_SKZ]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblRep_SKZ]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTemp_Prod_ORA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblTemp_Prod_ORA]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTemp_Prod_ORB]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblTemp_Prod_ORB]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTemp_Prod_ORC]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblTemp_Prod_ORC]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTemp_Prod_ORD]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblTemp_Prod_ORD]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblUserLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblUserLog]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblUserRight]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblUserRight]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_Rpt_Platic]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_Rpt_Platic]
GO

CREATE TABLE [dbo].[BomDetail] (
 [ItemNo] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [PartNo] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [Qty_Per] [float] NULL ,
 [LEVEL] [int] NULL ,
 [Last_Date] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[BomOldCost] (
 [ItemNo] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [OldCost] [float] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[BomPart] (
 [PartNo] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [Type] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
 [Category] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [StdCost] [float] NULL ,
 [Last_Date] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[BomPrice] (
 [ItemNo] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [NewCost] [float] NULL ,
 [OldCost] [float] NULL ,
 [Change] [float] NULL ,
 [PercentAge] [float] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[BomStruc] (
 [ItemNo] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [PartNo] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [Qty_Per] [float] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Count_PartWeight] (
 [Part_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [Qty] [decimal](18, 0) NULL ,
 [Part_NW] [decimal](18, 2) NULL ,
 [Sum_Qty] [decimal](18, 2) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DelPart] (
 [Part_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[EF] (
 [UDate] [smalldatetime] NULL ,
 [Item_No] [nvarchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
 [Part_No] [nvarchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [Part_Desc] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
 [Qty] [decimal](9, 0) NULL ,
 [M_1] [nvarchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
 [M1_Price] [decimal](9, 4) NULL ,
 [P_1] [decimal](9, 0) NULL ,
 [M1_Amt] [decimal](9, 4) NULL ,
 [M_2] [nvarchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
 [M2_Price] [decimal](9, 4) NULL ,
 [M2_Amt] [decimal](9, 4) NULL ,
 [P_2] [decimal](9, 0) NULL ,
 [C_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [C_Rate] [decimal](9, 1) NULL ,
 [Part_GW] [decimal](9, 2) NULL ,
 [Add_GW] [decimal](9, 2) NULL ,
 [Part_NW] [decimal](9, 2) NULL ,
 [Add_NW] [decimal](9, 2) NULL ,
 [Add_Per] [decimal](9, 0) NULL ,
 [Plt_Cost] [decimal](9, 4) NULL ,
 [Plt_C2] [decimal](9, 4) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[JobOrder] (
 [fldJorder_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [fldSorder_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldItem_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldItem_Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldProd_Qty] [numeric](18, 2) NULL ,
 [fldC_Qty] [numeric](18, 2) NULL ,
 [fldN_Qty] [numeric](18, 2) NULL ,
 [fldS_Date] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldC_Date] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldRemark] [text] COLLATE Chinese_PRC_CI_AS NULL ,
 [fldProd_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldDate] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [part_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [part_name] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [per_Qty] [numeric](18, 2) NULL ,
 [qty] [numeric](18, 2) NULL ,
 [P_1] [numeric](18, 2) NULL ,
 [M_1] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [P_2] [numeric](18, 2) NULL ,
 [M_2] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [Part_Color] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [C_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [C_Rate] [numeric](18, 2) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Part] (
 [Part_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [Part_Name] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [StdCost] [numeric](18, 4) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PartCost] (
 [PartNo] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [Cost] [decimal](18, 4) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PartWeight] (
 [Part_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [Qty] [decimal](18, 0) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Prod_ORA] (
 [Prod_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [Item_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [Item_Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [Prod_Qty] [numeric](18, 2) NULL ,
 [S_Date] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [C_Date] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [Jorder_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [Sorder_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [Prod_Qty1] [numeric](18, 2) NULL ,
 [SDate] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [CDate] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UpFile] (
 [FileName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [FileType] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [FileLength] [int] NULL ,
 [UserId] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [FileDate] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tabBzgzd] (
 [F_JOB_NO] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
 [MOULD_NO] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [PART_NO] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_ITEM] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_MCYS] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_BZXQSL] [numeric](30, 0) NULL ,
 [F_ZS] [numeric](18, 0) NULL ,
 [F_DQCCSL] [numeric](18, 0) NULL ,
 [F_BZRQ] [smalldatetime] NULL ,
 [F_WCRQ] [smalldatetime] NULL ,
 [F_MJMZ] [numeric](18, 2) NULL ,
 [F_MJJZ] [numeric](18, 2) NULL ,
 [F_ZMZ] [numeric](18, 1) NULL ,
 [F_ZJZ] [numeric](18, 1) NULL ,
 [F_P_NO1] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_M1] [numeric](18, 0) NULL ,
 [F_YJYL1] [numeric](18, 1) NULL ,
 [F_P_NO2] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_M2] [numeric](18, 0) NULL ,
 [F_YJYL2] [numeric](18, 1) NULL ,
 [F_CNO] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_CM] [numeric](18, 0) NULL ,
 [F_YJSFYL] [numeric](18, 1) NULL ,
 [F_LCF] [bit] NOT NULL ,
 [F_MDNM] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_NOP] [numeric](18, 0) NULL ,
 [F_MBZQ] [numeric](18, 2) NULL ,
 [F_YJZSS] [numeric](18, 2) NULL ,
 [F_BZJT] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_24XSCL] [numeric](18, 0) NULL ,
 [F_24XSYL] [numeric](18, 0) NULL ,
 [F_InUser] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_InDate] [smalldatetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tabBzxqb] (
 [F_HHYXQ] [nvarchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_GZDYXQ] [nvarchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_JJYXQ] [nvarchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_JOB_NO] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [PART_NO] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [F_TF] [nvarchar] (1) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_ITEM] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_MCYS] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_DQCCSL] [numeric](18, 0) NULL ,
 [F_DATE1] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_RES1] [numeric](18, 0) NULL ,
 [F_REQ1] [numeric](18, 0) NULL ,
 [F_DATE2] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_RES2] [numeric](18, 0) NULL ,
 [F_REQ2] [numeric](18, 0) NULL ,
 [F_DATE3] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_RES3] [numeric](18, 0) NULL ,
 [F_REQ3] [numeric](18, 0) NULL ,
 [F_DATE4] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_RES4] [numeric](18, 0) NULL ,
 [F_REQ4] [numeric](18, 0) NULL ,
 [F_DATE5] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_RES5] [numeric](18, 0) NULL ,
 [F_REQ5] [numeric](18, 0) NULL ,
 [F_DATE6] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_RES6] [numeric](18, 0) NULL ,
 [F_REQ6] [numeric](18, 0) NULL ,
 [F_RESS] [numeric](18, 0) NULL ,
 [F_REST] [numeric](18, 0) NULL ,
 [F_REQS] [numeric](18, 0) NULL ,
 [F_REQT] [numeric](18, 0) NULL ,
 [F_XQRQ] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_SHL] [numeric](18, 0) NULL ,
 [F_BZXQSL] [numeric](18, 0) NULL ,
 [F_QZRQ] [numeric](18, 0) NULL ,
 [F_BZRQ] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_MJMZ] [numeric](18, 2) NULL ,
 [F_ZMZ] [numeric](18, 2) NULL ,
 [F_MJJZ] [numeric](18, 2) NULL ,
 [F_P_NO1] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_M1] [numeric](18, 0) NULL ,
 [F_YJYL1] [numeric](18, 1) NULL ,
 [F_P_NO2] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_M2] [numeric](18, 0) NULL ,
 [F_YJYL2] [numeric](18, 1) NULL ,
 [F_CNO] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_CM] [numeric](18, 0) NULL ,
 [F_YJSFYL] [numeric](18, 1) NULL ,
 [F_LCF] [bit] NULL ,
 [MOULD_NO] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_MDNM] [nvarchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_NOP] [numeric](18, 0) NULL ,
 [F_MBZQ] [numeric](18, 2) NULL ,
 [F_YJZSS] [numeric](18, 1) NULL ,
 [MOULD_NO2] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_MDNM2] [nvarchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_NOP2] [numeric](18, 0) NULL ,
 [F_MBZQ2] [numeric](18, 2) NULL ,
 [F_YJZSS2] [numeric](18, 1) NULL ,
 [F_MJMZ2] [numeric](18, 2) NULL ,
 [F_ZMZ2] [numeric](18, 2) NULL ,
 [F_MJJZ2] [numeric](18, 2) NULL ,
 [F_YJYL12] [numeric](18, 1) NULL ,
 [F_YJYL22] [numeric](18, 1) NULL ,
 [F_YJSFYL2] [numeric](18, 1) NULL ,
 [F_InUSER] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_InDate] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tabJjGzdHh] (
 [JOB_NO] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [ITEM_NO] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [PART_NO] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_RES_QTY] [numeric](18, 0) NULL ,
 [F_HHYXQ] [nvarchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_GZDYXQ] [nvarchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
 [F_JJYXQ] [nvarchar] (4) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tabMrp] (
 [MRP_ID] [bigint] NOT NULL ,
 [Line] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tb] (
 [BH] [char] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [col] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblAppFile] (
 [fldApp_Id] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [fldApp_Desc] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldApp_Name] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldApp_Type] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldGroup] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldNumber] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblColorant] (
 [fldC_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [fldCc_Name] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldC_Name] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldC_Color] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldInUser] [nvarchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldInDate] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldTransFlag] [nvarchar] (1) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblCompany] (
 [fldCo_Id] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [fldCo_Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblItem_Mas] (
 [fldItem_No] [nvarchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [fldItem_Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldRemark] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
 [fldInUser] [nvarchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldInDate] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldTransFlag] [nvarchar] (1) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblJobDetail] (
 [fldJorder_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [fldSorder_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldItem_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldPart_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldPart_Name] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldPer_Qty] [numeric](18, 2) NULL ,
 [fldQty] [numeric](18, 2) NULL ,
 [fldP_1] [numeric](18, 2) NULL ,
 [fldM_1] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldP_2] [numeric](18, 2) NULL ,
 [fldM_2] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldPart_Color] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldC_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldC_Rate] [numeric](18, 2) NULL ,
 [fldDate] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblJobHead] (
 [fldJorder_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [fldSorder_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldItem_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldItem_Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldProd_Qty] [numeric](18, 2) NULL ,
 [fldC_Qty] [numeric](18, 2) NULL ,
 [fldN_Qty] [numeric](18, 2) NULL ,
 [fldS_Date] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldC_Date] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldRemark] [text] COLLATE Chinese_PRC_CI_AS NULL ,
 [fldProd_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldDate] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblM_Serial] (
 [fldMould_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [fldSerial_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldPart_GW] [numeric](18, 2) NULL ,
 [fldPart_NW] [numeric](18, 2) NULL ,
 [fldNop] [int] NULL ,
 [fldInUser] [nvarchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldInDate] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldTransFlag] [nvarchar] (1) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblMould] (
 [fldMould_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [fldMould_Name] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldMachineType] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldMould_GW] [numeric](18, 2) NULL ,
 [fldMould_NW] [numeric](18, 2) NULL ,
 [fldCycle_Time] [numeric](18, 2) NULL ,
 [fldNoc] [int] NULL ,
 [fldPhoto_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldM_Date] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldT_Date] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldLocation] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldRemark] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
 [fldInUser] [nvarchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldInDate] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldTransFlag] [nvarchar] (1) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblPDaily] (
 [PROD_DATE] [smalldatetime] NULL ,
 [IMM_NO] [nvarchar] (3) COLLATE Chinese_PRC_CI_AS NULL ,
 [TYPE] [nvarchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
 [PART_NO] [nvarchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
 [PART_DESC] [nvarchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
 [ITEM_NO] [nvarchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
 [MOULD_NO] [nvarchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
 [PER_QTY] [float] NULL ,
 [CYCLE_TIME] [float] NULL ,
 [M_IQTY] [float] NULL ,
 [A_IQTY] [float] NULL ,
 [N_IQTY] [float] NULL ,
 [TOTL_IQTY] [float] NULL ,
 [BAL_IQTY] [float] NULL ,
 [NEED_QTY] [float] NULL ,
 [DEBT_QTY] [float] NULL ,
 [SCRP_TIME] [float] NULL ,
 [POWER] [float] NULL ,
 [REMARK] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblP_Bom] (
 [fldItem_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldPart_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldPer_Qty] [numeric](18, 0) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblPart] (
 [fldPart_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [fldPart_Name] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldP_1] [numeric](18, 0) NULL ,
 [fldM_1] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldP_2] [numeric](18, 0) NULL ,
 [fldM_2] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldPart_Color] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldC_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldC_Rate] [numeric](18, 0) NULL ,
 [fldRemark] [text] COLLATE Chinese_PRC_CI_AS NULL ,
 [fldInUser] [nvarchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldInDate] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldTransFlag] [nvarchar] (1) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblPassword] (
 [fldUserID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [fldUserName] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldUserPswd] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldDepartment] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldCTD] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldRemark] [text] COLLATE Chinese_PRC_CI_AS NULL ,
 [fldInUser] [nvarchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldInDate] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblPlastic] (
 [fldP_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [fldGrade_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldP_Name] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldPrice] [decimal](18, 4) NULL ,
 [fldInUser] [nvarchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldInDate] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldTransFlag] [nvarchar] (1) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblPr_Bom] (
 [fldItem_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldPart_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldRPart_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblProd_ORA] (
 [fldProd_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldItem_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldItem_Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldProd_Qty] [numeric](18, 2) NULL ,
 [fldS_Date] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldC_Date] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldRep] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldDate] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblProd_ORB] (
 [fldProd_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldJorder_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldSorder_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldItem_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldProd_Qty] [numeric](18, 2) NULL ,
 [fldS_Date] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldC_Date] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblProd_ORC] (
 [fldProd_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [fldPart_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldPart_Name] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldPer_Qty] [numeric](18, 0) NULL ,
 [fldQty] [numeric](18, 2) NULL ,
 [fldP_1] [numeric](18, 0) NULL ,
 [fldM_1] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldP_2] [numeric](18, 0) NULL ,
 [fldM_2] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldPart_Color] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldC_NO] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldC_Rate] [numeric](18, 0) NULL ,
 [fldMould_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldMould_Qty] [numeric](18, 0) NULL ,
 [fldPart_GW] [numeric](18, 2) NULL ,
 [fldPart_NW] [numeric](18, 2) NULL ,
 [fldImm_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblRep_Colorant] (
 [fldC_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [fldCC_Name] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldC_Name] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldC_Color] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldRate] [numeric](18, 1) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblRep_Part] (
 [fldID] [bigint] NOT NULL ,
 [fldPart_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldPart_Name] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldJorder_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldItem_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldQTY1] [float] NULL ,
 [fldQTY2] [float] NULL ,
 [fldQTY3] [float] NULL ,
 [fldQTY4] [float] NULL ,
 [fldQTY5] [float] NULL ,
 [fldQTY] [float] NULL ,
 [fldJEF_QTY] [float] NULL ,
 [fldQ_QTY] [float] NULL ,
 [fldMould_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldDay] [float] NULL ,
 [fldS_Date] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldC_Date] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblRep_Pla_Col] (
 [fldPart_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [fldPart_Name] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldQty] [numeric](18, 0) NULL ,
 [fldMould_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldPart_GW] [numeric](18, 2) NULL ,
 [fldPart_NW] [numeric](18, 2) NULL ,
 [fldP_1] [numeric](18, 0) NULL ,
 [fldM_1] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldM1_WT] [numeric](18, 2) NULL ,
 [fldP_2] [numeric](18, 0) NULL ,
 [fldM_2] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldM2_WT] [numeric](18, 2) NULL ,
 [fldBWT] [numeric](18, 2) NULL ,
 [fldPart_Color] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldC_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldC_Rate] [numeric](18, 1) NULL ,
 [fldProd_Time] [numeric](18, 2) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblRep_Plastic] (
 [fldP_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [fldGrade_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldP_Name] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldTwt] [numeric](18, 3) NULL ,
 [fldPack] [numeric](18, 0) NULL ,
 [fldBwt] [numeric](18, 3) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblRep_SKZ] (
 [fldM_1] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [fldGrade_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldP_Name] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldTWT] [numeric](18, 3) NULL ,
 [fldPack] [numeric](18, 0) NULL ,
 [fldBWT] [numeric](18, 3) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTemp_Prod_ORA] (
 [fldProd_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldItem_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldItem_Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldProd_Qty] [numeric](18, 2) NULL ,
 [fldS_Date] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldC_Date] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldRep] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldDate] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTemp_Prod_ORB] (
 [fldProd_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldJorder_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldSorder_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldItem_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldProd_Qty] [numeric](18, 2) NULL ,
 [fldS_Date] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldC_Date] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTemp_Prod_ORC] (
 [fldProd_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldPart_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldPart_Name] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldPer_Qty] [numeric](18, 0) NULL ,
 [fldQty] [numeric](18, 2) NULL ,
 [fldP_1] [numeric](18, 0) NULL ,
 [fldM_1] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldP_2] [numeric](18, 0) NULL ,
 [fldM_2] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldPart_Color] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldC_NO] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldC_Rate] [numeric](18, 0) NULL ,
 [fldMould_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldMould_Qty] [numeric](18, 0) NULL ,
 [fldPart_GW] [numeric](18, 2) NULL ,
 [fldPart_NW] [numeric](18, 2) NULL ,
 [fldImm_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTemp_Prod_ORD] (
 [fldPart_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldQty] [numeric](18, 0) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblUserLog] (
 [fldUserID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [fldIpaddress] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [fldsessionout] [datetime] NULL ,
 [fldlogindate] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblUserRight] (
 [fldUserId] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [fldApp_Id] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_Rpt_Platic] (
 [Item_No] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [P_No] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [P_M] [numeric](18, 3) NULL
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE BOM_COUNT_PRICE

AS
 SET NOCOUNT ON
--UPDATE LATEST PRICE
UPDATE BOMPART
SET STDCOST=B.COST
FROM BOMPART A,PARTCOST B
WHERE A.TYPE='P' AND A.PARTNO=B.PARTNO

--COMPARE COST
TRUNCATE TABLE BOMPRICE
INSERT INTO BOMPRICE
SELECT N.ITEMNO,CONVERT(DECIMAL(12,4),N.NEWCOST) AS NEWCOST,CONVERT(DECIMAL(12,4),O.OLDCOST) AS OLDCOST,
       CONVERT(DECIMAL(12,4),(N.NEWCOST-O.OLDCOST)) AS CHANGE,
       CASE WHEN O.OLDCOST=0 THEN NULL
            WHEN O.OLDCOST<>0 THEN CONVERT(DECIMAL(12,2),(N.NEWCOST-O.OLDCOST)/O.OLDCOST*100)
       END AS PercentAge
FROM (SELECT A.ItemNo,SUM(A.QTY_PER*B.STDCOST) AS NEWCOST
FROM BOMDETAIL A INNER JOIN BOMPART B ON A.PARTNO=B.PARTNO
GROUP BY A.ITEMNO) N,BOMOLDCOST O
WHERE N.ITEMNO=O.ITEMNO
ORDER BY N.ITEMNO

    SET NOCOUNT OFF
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.Bom_Find_BomParts
AS
   SET NOCOUNT OFF
TRUNCATE TABLE BOMPART
SELECT * FROM BOMPART
 SET NOCOUNT ON
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.Bom_Find_BomStruc
AS
   SET NOCOUNT OFF
TRUNCATE TABLE BOMSTRUC
SELECT * FROM BOMSTRUC
 SET NOCOUNT ON
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE Procedure Bom_Get_Bom
AS
-- 计算处理
SET NOCOUNT ON
CREATE TABLE #BOM(ItemNo nvarchar(20) COLLATE Chinese_PRC_CI_AS,
                  PartNo nvarchar(20) COLLATE Chinese_PRC_CI_AS,
                  Sum_PerQty Decimal(12,4),
                  Level int)
CREATE TABLE #BOM_RESULT(ItemNo nvarchar(20) COLLATE Chinese_PRC_CI_AS,
                         PartNo nvarchar(20) COLLATE Chinese_PRC_CI_AS,
                         Sum_PerQty Decimal(12,4),
                         Level int)
DECLARE @l INT
SET @l = 1  ---BOM第一层

INSERT #BOM
SELECT ItemNo, PartNo, Sum_PerQty = Qty_Per,Level = @l
FROM BomStruc A
 
WHILE @@ROWCOUNT > 0
BEGIN
 SET @l = @l + 1
 INSERT INTO #BOM
    SELECT B.ItemNo, A.PartNo,Sum_PerQty = B.Sum_PerQty * A.Qty_Per,@l
 FROM BomStruc  A, #BOM B
 WHERE A.ItemNo = B.PartNo
  AND B.Level = @l - 1
END

--自顶向下的往下乘, 最后过滤掉非底层的结点
TRUNCATE TABLE #BOM_RESULT
INSERT #BOM_RESULT
SELECT ItemNo,PartNo,SUM(Sum_PerQty),Level
FROM #BOM A
WHERE NOT EXISTS(
  SELECT * FROM BomStruc
  WHERE A.PartNo = ItemNo)
GROUP BY ItemNo,PartNo,Level
ORDER BY ItemNo,LEVEL,PARTNO

TRUNCATE TABLE BOMDETAIL
INSERT BOMDETAIL
SELECT A.ITEMNO,A.PARTNO,A.SUM_PERQTY,A.LEVEL,B.LAST_DATE
FROM #BOM_RESULT A LEFT JOIN BOMPART B ON A.PARTNO=B.PARTNO
WHERE A.ITEMNO IN (SELECT PARTNO FROM BOMPART WHERE TYPE='F')
ORDER BY A.ITEMNO,A.LEVEL,A.PARTNO

TRUNCATE TABLE #BOM
DROP TABLE #BOM
TRUNCATE TABLE #BOM_RESULT
DROP TABLE #BOM_RESULT

PRINT 'EXECUTE SUCCESSFUL!'
SET NOCOUNT OFF

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Bom_Get_BomDetail
(
 @Start_Bom nvarchar(20),
 @End_Bom nvarchar(20),
 @Start_Date nvarchar(10),
 @End_Date nvarchar(10)
)
AS
    BEGIN
     SET NOCOUNT OFF
     DECLARE @Y NVARCHAR(4)
     DECLARE @M NVARCHAR(2)
     DECLARE @D NVARCHAR(2)
    
     IF @START_DATE='' OR @START_DATE IS NULL
        BEGIN
           SET @Y=YEAR(DATEADD(mm,-3,GETDATE()))
           SET @M=MONTH(DATEADD(mm,-3,GETDATE()))
           SET @D=DAY(DATEADD(mm,-3,GETDATE()))
    
           IF LEN(@M)=1
              SET @M='0'+@M
           ELSE
              SET @M=@M
       
           IF LEN(@D)=1
              SET @D='0'+@D
           ELSE
              SET @D=@D
           SET @START_DATE=@Y+'/'+@M+'/'+@D
        END
     
     IF @START_BOM='' OR @START_BOM IS NULL
        SET @START_BOM='1000000'
       
     IF @END_BOM='' OR @END_BOM IS NULL
        SET @END_BOM='9999999999'
       
     SELECT ItemNo,PartNo,Qty_Per,Last_Date
     FROM BOMDETAIL
     WHERE ITEMNO>=@START_BOM AND ITEMNO<=@END_BOM AND LAST_DATE<@START_DATE
     ORDER BY ITEMNO,PARTNO,LAST_DATE
    
  SET NOCOUNT ON
 END
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Bom_Get_BomPrice

AS
     SET NOCOUNT OFF
     SELECT * FROM BOMPRICE
  SET NOCOUNT ON
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE ExPort_EF

AS

  begin
     truncate table EF
 
     insert into EF
     select tblPart.fldInDate,'',tblPart.fldPart_No,fldPart_Name,0,fldM_1,0,fldP_1,0,fldM_2,0,0,fldP_2,fldC_No,fldC_Rate,tblM_Serial.fldPart_GW,0,tblM_Serial.fldPart_NW,0,0,0,0
     from tblPart,tblM_Serial
     where tblPart.fldM_1<>''and substring(tblPart.fldPart_No,5,4)=tblM_Serial.fldSerial_No
     order by tblPart.fldPart_No
   
    end
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE procedure Get_Bom
(@Bom_No nvarchar(20))
as
 
  begin
select * from ViewBom(@Bom_No)

truncate table EF_Database..tbl_Rpt_Platic
  end
 
BEGIN --/1-- 统计胶粒用量并保存到tbl_Rpt_Platic资料表中

   declare @GW numeric(9,2),@NW numeric(9,2),@Serial_No nvarchar(10),@Part_No nvarchar(20)
   declare @P1 numeric(9),@P2 numeric(9),@M1 nvarchar(10),@M2 nvarchar(10),@P_GW numeric(9,2)
  
   declare @tb_bom table(Item_No nvarchar(16),Part_No nvarchar(20),
                P_1 numeric(9),M_1 nvarchar(10),P_2 numeric(9),M_2 nvarchar(10),
                Part_GW numeric(9,2),Part_NW numeric(9,2))
   declare @tb_Serial table(Serial_No nvarchar(10),Part_GW0 numeric(9,3),Part_NW0 numeric(9,2))
   declare @tb_Pla1 table(P_No1 nvarchar(10),Part_GW1 numeric(9,3))
   declare @tb_Pla2 table(P_No2 nvarchar(10),Part_GW2 numeric(9,3))
 
 
   begin--/2--
  insert into @tb_bom
  select tblItem_Mas.fldItem_No,tblPart.fldPart_No,tblPart.fldP_1,tblPart.fldM_1,tblPart.fldP_2,tblPart.fldM_2,0,0
  from tblItem_Mas,tblPart,tblP_Bom
  where tblItem_Mas.fldItem_No=tblP_Bom.fldItem_No and tblP_Bom.fldPart_No=tblPart.fldPart_No and tblItem_Mas.fldItem_No=@Bom_No
   end ---/2---
  
   begin --/3--
  declare Cur_Value cursor for
  select Part_No from @tb_bom
  open Cur_Value
  fetch next from Cur_Value into @Part_No
  while @@fetch_status=0
        begin
             insert into @tb_Serial
             select top 1 tblM_Serial.fldSerial_No,tblM_Serial.fldPart_GW,tblM_Serial.fldPart_NW
             from tblM_Serial
             where tblM_Serial.fldSerial_No =substring(@Part_No,5,4)
             fetch next from Cur_Value into @Part_No
        end
  close Cur_Value
  deallocate Cur_Value
   end --/3--
   
   begin --/4--
  declare Cur_Value cursor for
  select Serial_No,Part_GW0,Part_NW0 from @tb_Serial
  open Cur_Value
  fetch next from Cur_Value into @Serial_No,@GW,@NW
  while @@fetch_status=0
        begin
            
             update @tb_bom set Part_GW=@GW,Part_NW=@NW
             where substring(Part_No,5,4)=@Serial_No
             fetch next from Cur_Value into @Serial_No,@GW,@NW
        end
  close Cur_Value
  deallocate Cur_Value
  end --/4--
 
  begin--/5--   统计胶粒用量
  declare Cur_Value cursor for
  select P_1,M_1,P_2,M_2,Part_GW from @tb_bom
  open Cur_Value
  fetch next from Cur_Value into @P1,@M1,@P2,@M2,@P_GW
  while @@fetch_status=0
         begin
         if @M1 + 'R'=@M2 or (@M1<>'' and @M2='')
            begin
            insert into @tb_pla1 values(@M1,@P_GW)
            end
         else
            begin
            insert into @tb_pla1 values(@M1,@P_GW*@P1/100)
            insert into @tb_pla1 values(@M2,@P_GW*@P2/100)
            end
            fetch next from Cur_Value into @P1,@M1,@P2,@M2,@P_GW
         end
  close Cur_Value
  deallocate Cur_Value
 
  end--/5--
 
  begin --/6--
  insert into @tb_Pla2
  select case when(grouping(P_No1)=1) then  'all'
              else isnull(P_No1,'unknown')
         end as P_No1,
         sum(Part_GW1) as Part_GW1
  from @tb_Pla1
  group by P_No1 with rollup
 
  insert into EF_Database..tbl_Rpt_Platic
  select @Bom_No,P_No2,Part_GW2 from @tb_Pla2 where Part_GW2<>0 and P_No2<>'all'
  end--/6--
 
  delete from @tb_Serial
  delete from @tb_Pla1
  delete from @tb_Pla2
  delete from @tb_bom
 
 RETURN
 END--/1--

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_EF

AS

  begin
     truncate table EF
 
     insert into EF
     select tblPart.fldInDate,'',tblPart.fldPart_No,fldPart_Name,0,fldM_1,0,fldP_1,0,fldM_2,0,0,fldP_2,fldC_No,fldC_Rate,tblM_Serial.fldPart_GW,0,tblM_Serial.fldPart_NW,0,0,0,0
     from Part,tblPart,tblM_Serial
     where Part.Part_No=tblPart.fldPart_No and (tblPart.fldM_1<>'') and Part.StdCost=0
          and substring(Part.Part_No,5,4)=tblM_Serial.fldSerial_No
     order by tblPart.fldPart_No
   
    end
 RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_App_Type
  @App_Type Nvarchar(10),
  @UserId nvarchar(20)
AS
   if @UserId='ADMIN'
      begin
   select fldApp_Id,fldApp_Desc,fldApp_Name,fldGroup from tblAppFile
  where fldApp_Type=@App_Type order by fldNumber,fldGroup
   end
 else
        begin
 select tblAppFile.fldApp_Id,tblAppFile.fldApp_Desc,tblAppFile.fldApp_Name,tblAppFile.fldGroup from tblAppFile,tblUserRight
  where fldApp_Type=@App_Type and tblAppFile.fldApp_Id=tblUserRight.fldApp_Id and tblUserRight.fldUserId=@UserId
   order by fldNumber,fldGroup
     end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Copy_Bom
 @Item_No nvarchar(20),
 @New_Item_No nvarchar(20),
 @Item_Name nvarchar(50),
 @Remark ntext,
 @InUser nvarchar(8),
 @InDate nvarchar(10),
 @TransFlag nvarchar(1)
 
AS
     begin
 
insert into tblItem_Mas values(@New_Item_No,@Item_Name,@Remark,@InUser,@InDate,@TransFlag)
                             
insert into tblP_Bom select @New_Item_No,fldPart_No,fldPer_Qty from tblP_Bom where fldItem_No=@Item_No
    
insert into tblPr_Bom select @New_Item_No,fldPart_No,fldRPart_No from tblPr_Bom where fldItem_No=@Item_No

     end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Copy_JobHead_Detail
  @Jorder_No nvarchar(10),
  @New_Jorder_No nvarchar(10),
  @Sorder_No nvarchar(10),
  @Item_No nvarchar(20),
  @Item_Name Nvarchar(50), 
  @Prod_Qty numeric(12,2),
  @C_Qty numeric(12,2),
  @N_Qty numeric(12,2),
  @S_Date nvarchar(10),
  @C_Date nvarchar(10),
  @Remark text,
  @Prod_No nvarchar(10),
  @Date nvarchar(10)
 
AS
     begin
 
insert into tblJobHead values(@New_Jorder_No,@Sorder_No,@Item_No,@Item_Name,@Prod_Qty,@C_Qty,@N_Qty,@S_Date,@C_Date,
                              @Remark,@Prod_No,@Date)
                             
insert into tblJobDetail
select @New_Jorder_No,@Sorder_No,fldItem_No,fldPart_No,fldPart_Name,fldPer_Qty,fldPer_Qty*@Prod_Qty,fldP_1,fldM_1,fldP_2,fldM_2,fldPart_Color,fldC_No,fldC_Rate,@Date from tblJobDetail
     where fldJorder_No=@Jorder_No
    
     end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Delete_JobHead
  @Jorder_No nvarchar(4000)
 
AS
    begin
 exec('delete from tblJobHead where fldJorder_No in (' + @Jorder_No + ')')
 exec('delete from tblJobDetail where fldJorder_No in (' + @Jorder_No + ')')
    end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Edit_Bom
  @Item_No Nvarchar(20),
  @Item_Name nvarchar(50),
  @Remark ntext,
  @InUser nvarchar(8),
  @InDate nvarchar(10),
  @Id int
AS
   if @Id=0
      begin
  delete from tblItem_Mas where fldItem_No=@Item_No
  delete from tblP_Bom where fldItem_No=@Item_No
  delete from tblPr_Bom where fldItem_No=@Item_No
      end
   else if @Id=1
      begin
   update tblItem_Mas set fldItem_Name=@Item_Name,fldRemark=@Remark,fldInUser=@InUser,fldInDate=@InDate where fldItem_No=@Item_No
      end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Edit_Colorant
  @C_No Nvarchar(20),
  @CC_Name nvarchar(20),
  @C_Name nvarchar(20),
  @C_Color nvarchar(20),
  @InUser nvarchar(8),
  @InDate nvarchar(10),
  @Id int
AS
   if @Id=0
      begin
  delete from tblColorant where fldC_No=@C_No
      end
   else
      begin
   update tblColorant set fldC_Name=@C_Name,fldCC_Name=@CC_Name,fldC_Color=@C_Color,fldInUser=@InUser,fldInDate=@InDate where fldC_No=@C_No
      end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Edit_JobDetail
  @Jorder_No nvarchar(10),
  @Item_No nvarchar(20),
  @Part_No nvarchar(20),
  @RPart_No nvarchar(20),
  @Per_Qty numeric(12,2),
  @Qty numeric(12,2),
  @Id int
 
AS
       
   if @Id=0
      begin
   delete tblJobDetail where  fldJorder_No=@Jorder_No and fldItem_No=@Item_No and fldPart_No=@Part_No
      end
   else if @id=1
      begin
   update tblJobDetail set fldPer_Qty=@Per_Qty,fldQty=@Per_Qty*@Qty where fldJorder_No=@Jorder_No and fldItem_No=@Item_No and fldPart_No=@Part_No
      end
   else if @id=2
      begin
   update tblJobDetail set fldPart_No=@RPart_No,fldPer_Qty=@Per_Qty,fldQty=@Per_Qty*@Qty where fldJorder_No=@Jorder_No and fldItem_No=@Item_No and fldPart_No=@Part_No
      end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Edit_JobHead
  @Jorder_No nvarchar(10),
  @Sorder_No nvarchar(10),
  @Item_No nvarchar(20),
  @Prod_Qty numeric(9,2),
  @C_Qty numeric(9,2),
  @N_Qty numeric(9,2),
  @S_Date nvarchar(10),
  @C_Date nvarchar(10),
  @Remark text,
  @Id int
AS
       
   if @Id=1
      begin
   delete tblJobHead where  fldJorder_No=@Jorder_No
   delete tblJobDetail where fldJorder_No=@Jorder_No
      end
   else if @id=2
      begin
   update tblJobHead set fldSorder_No=@Sorder_No,fldProd_Qty=@Prod_Qty,fldC_Qty=@C_Qty,fldN_Qty=@N_Qty,fldS_Date=@S_Date,
                         fldC_Date=@C_Date,fldRemark=@Remark where fldJorder_No=@Jorder_No and fldItem_No=@Item_No
  
   update tblJobDetail set fldSorder_No=@Sorder_No,fldQty=@Prod_Qty*fldPer_Qty  where fldJorder_No=@Jorder_No and fldItem_No=@Item_No
      end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Edit_M_Serial
  @Mould_No Nvarchar(10),
  @Serial_No nvarchar(10),
  @Part_GW numeric(9,2),
  @Part_NW numeric(9,2),
  @Nop int,
  @InUser nvarchar(8),
  @InDate nvarchar(10),
  @Id int
AS
   if @Id=0
      begin
  delete from tblM_Serial where fldMould_No=@Mould_No and fldSerial_No=@Serial_No
       end
   else if @Id=1
      begin
   update tblM_Serial set fldPart_GW=@Part_GW,fldPart_NW=@Part_NW,fldNop=@Nop,fldInUser=@InUser,
                          fldInDate=@InDate where fldMould_No=@Mould_No and fldSerial_No=@Serial_No
      end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Edit_Mould
@Mould_No nvarchar(10),
@Mould_Name nvarchar(30),
@MachineType nvarchar(2),
@Mould_GW numeric(9,2),
@Mould_NW numeric(9,2),
@Cycle_Time numeric(9,2),
@Noc int,
@Photo_No nvarchar(10),
@M_Date nvarchar(10),
@T_Date nvarchar(10),
@Location nvarchar(20),
@Remark text,
@InUser nvarchar(8),
@InDate nvarchar(10),
@Id int

AS
    if @Id=0
       begin
 delete from tblMould where fldMould_No=@Mould_No
    end
   else if @Id=1
      begin
 update tblMould set fldMould_Name=@Mould_Name,fldMachineType=@MachineType,fldMould_GW=@Mould_GW,fldMould_NW=@Mould_NW,
                     fldCycle_Time=@Cycle_Time,fldNoc=@Noc,fldPhoto_No=@Photo_No,fldM_Date=@M_Date,fldT_Date=@T_Date,
                     fldLocation=@Location,fldRemark=@Remark,fldInUser=@InUser,fldInDate=@InDate where fldMould_No=@Mould_No
      end
     
 RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Edit_P_Bom
  @Item_No Nvarchar(20),
  @Part_No nvarchar(20),
  @Per_Qty numeric(9,2),
  @Id int
AS
   if @Id=0
      begin
  delete from tblP_Bom where fldItem_No=@Item_No and fldPart_No=@Part_No
  delete from tblPr_Bom where fldItem_No=@Item_No and fldPart_No=@Part_No
      end
   else if @Id=1
      begin
   update tblP_Bom set fldPer_Qty=@Per_Qty where fldItem_No=@Item_No and fldPart_No=@Part_No
      end
   else if @Id=2
      begin
  delete from tblP_Bom where fldItem_No=@Item_No and fldPart_No=@Part_No
      end
   
  return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Edit_Part
  @Part_No Nvarchar(20),
  @Part_Name nvarchar(20),
  @P_1 numeric(9,2),
  @M_1 nvarchar(10),
  @P_2 numeric(9,2),
  @M_2 nvarchar(10),
  @Part_Color nvarchar(20),
  @C_No nvarchar(20),
  @C_Rate numeric(9,2),
  @Remark text,
  @InUser nvarchar(8),
  @InDate nvarchar(10),
  @Id int
AS
   if @Id=0
      begin
  delete from tblPart where fldPart_No=@Part_No
      end
   else
      begin
   update tblPart set fldPart_Name=@Part_Name,fldP_1=@P_1,fldM_1=@M_1,fldP_2=@P_2,fldM_2=@M_2,fldPart_Color=@Part_Color,
          fldC_No=@C_No,fldC_Rate=@C_Rate,fldRemark=@Remark,fldInUser=@InUser,fldInDate=@InDate where fldPart_No=@Part_No
      end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Edit_Pla_Col
@Part_No nvarchar(20),
@Part_Name nvarchar(20),
@Qty numeric(18),
@Mould_No nvarchar(10),
@Part_GW numeric(18,2),
@Part_NW numeric(18,2),
@P_1 numeric(9),
@M_1 nvarchar(10),
@M1_WT numeric(18,2),
@P_2 numeric(9),
@M_2 nvarchar(10),
@M2_WT numeric(18,2),
@BWT numeric(18,2),
@Part_Color nvarchar(20),
@C_No nvarchar(10),
@C_Rate numeric(18,1),
@Prod_Time numeric(18,2)

AS
 
     begin
   update tblRep_Pla_Col set fldPart_Name=@Part_Name,fldQty=@Qty,fldMould_No=@Mould_No,fldPart_GW=@Part_GW,fldPart_NW=@Part_NW,
                             fldP_1=@P_1,fldM_1=@M_1,fldM1_WT=@M1_WT,fldP_2=@P_2,fldM_2=@M_2,fldM2_WT=@M2_WT,
                             fldBWT=@BWT,fldPart_Color=@Part_Color,fldC_No=@C_No,fldC_Rate=@C_Rate,fldProd_Time=@Prod_Time
                              where fldPart_No=@Part_No
     end
  
    RETURN
    

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Edit_Plastic
  @P_No Nvarchar(10),
  @Grade_No nvarchar(10),
  @P_Name nvarchar(40),
  @Price  decimal(12,4),
  @InUser nvarchar(8),
  @InDate nvarchar(10),
  @Id int
AS
   if @Id=0
      begin
  delete from tblPlastic where fldP_No=@P_No
      end
   else
      begin
   update tblPlastic set fldGrade_No=@Grade_No,fldP_Name=@P_Name,fldPrice=@Price,fldInUser=@InUser,fldInDate=@InDate where fldP_No=@P_No
      end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Edit_Pr_Bom
  @Item_No Nvarchar(20),
  @Part_No nvarchar(20),
  @RPart_No nvarchar(20),
  @Id int
AS
   if @Id=0
        begin
  delete from tblPr_Bom where fldItem_No=@Item_No and fldPart_No=@Part_No and fldRPart_No=@RPart_No
     end
   else if @Id=1
        begin
    update tblPr_Bom set fldPart_No=@Part_No where fldItem_No=@Item_No
        end
       
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Edit_Prod_ORA

 @Prod_No nvarchar(1000),
 @Prod_Qty numeric(12,2),
 @S_Date nvarchar(10),
 @C_Date nvarchar(10),
 @Id int
 
AS
 if @Id=0
    begin
 delete from tblProd_ORA where fldProd_No=@Prod_No
 delete from tblProd_ORB where fldProd_No=@Prod_No
 delete from tblProd_ORC where fldProd_No=@Prod_No
    end
 else if @Id=1
    begin
 update tblProd_ORA set fldProd_Qty=@Prod_Qty,fldS_Date=@S_Date,fldC_Date=@C_Date where fldProd_No=@Prod_No
 update tblProd_ORC set fldQty=@Prod_Qty*fldPer_Qty where fldProd_No=@Prod_No
    end
 else if @Id=2
    begin
 exec('delete from tblProd_ORA where fldProd_No in (' + @Prod_No + ')')
 exec('delete from tblProd_ORB where fldProd_No in (' + @Prod_No + ')')
 exec('delete from tblProd_ORC where fldProd_No in (' + @Prod_No + ')')
    end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Edit_Prod_ORC
@Prod_No nvarchar(10),
@Part_No nvarchar(20),
@Mould_No nvarchar(10),
@IMM_No nvarchar(10)

as

   update tblProd_ORC set fldMould_No=@Mould_No,fldIMM_No=@IMM_No where fldProd_No=@Prod_No and fldPart_No=@Part_No
 
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Find_Bom
  @Item_No Nvarchar(20),
  @Id int
AS
   if @Id=0
      begin
  select fldItem_No from tblItem_Mas where fldItem_No=@Item_No order by fldItem_No
      end
   else if @Id=1
      begin
   select * from tblItem_Mas where fldItem_No like @Item_No order by fldItem_No desc
      end
   else if @Id=2
       begin
   select * from tblItem_Mas where fldItem_No=@Item_No order by fldItem_No
       end
   else if @Id=3
       begin
   select distinct fldItem_No from tblItem_Mas order by fldItem_No desc
       end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Find_Colorant
  @C_No Nvarchar(20),
  @Id int
AS
   if @Id=0
      begin
  select fldC_No from tblColorant where fldC_No=@C_No order by fldC_No
      end
   else
      begin
   select * from tblColorant where fldC_No like @C_No order by fldC_No desc
      end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Find_Data
  @Id int
AS
   if @Id=0
      begin
  select fldP_No from tblPlastic order by fldP_No
      end
   else
      begin
   select fldC_No from tblColorant order by fldC_No
      end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Find_JobDetail
  @Jorder_No Nvarchar(1000),
  @Item_No nvarchar(20),
  @Id int
AS
   if @Id=0
      begin
  select * from tblJobDetail where fldJorder_No=@Jorder_No and fldItem_No=@Item_No order by fldJorder_No
      end
   else if @Id=1
  
      begin
     exec('insert into tblTemp_Prod_ORD select fldPart_No,sum(fldQty) as Qty
           from tblJobDetail
           where fldJorder_No in (' + @Jorder_No + ')
           group by fldPart_No')
      end
else if @Id=2
      begin
  select fldPart_No,fldQty from tblTemp_Prod_ORD order by fldPart_No
      end
 
 else if @Id=3
      begin
  exec('select fldPart_No,fldPart_Name,fldPer_Qty,fldQty,fldP_1,fldM_1,fldP_2,fldM_2,fldPart_Color,fldC_No,fldC_Rate
        from tblJobDetail where fldJorder_No in (' + @Jorder_No + ')')
         
      end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Find_JobHead
  @Jorder_No Nvarchar(20),
  @Id int
AS
   if @Id=0
      begin
  select * from tblJobHead where fldJorder_No like @Jorder_No order by fldJorder_No
      end
   else if @Id=1
      begin
  select fldJorder_No from tblJobHead where fldJorder_No=@Jorder_No order by fldJorder_No
      end
   else if @Id=2
      begin
  select fldJorder_No from tblJobHead  order by fldJorder_No
      end
  else if @Id=3
      begin
  select * from tblJobHead where fldJorder_No=@Jorder_No order by fldJorder_No
      end
  else if @Id=4
      begin
  select * from tblJobHead where fldItem_No=@Jorder_No order by fldJorder_No
      end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Find_M_Serial
@Mould_No nvarchar(10),
@Serial_No nvarchar(10),
@Id int

AS
    if @Id=0
       begin
 select * from tblM_Serial where fldMould_No=@Mould_No and fldSerial_No=@Serial_No order by fldMould_No,fldSerial_No
    end
 else if @Id=1
    begin
 select fldSerial_No from tblM_Serial where fldMould_No=@Mould_No and fldSerial_No=@Serial_No order by fldMould_No,fldSerial_No
    end
    else if @Id=2
       begin
 select * from tblM_Serial where fldMould_No=@Mould_No order by fldMould_No,fldSerial_No
    end
   else if @Id=3
       begin
    select top 1 fldMould_No,fldNop,fldPart_GW,fldPart_NW from tblM_Serial where fldSerial_No=@Serial_No order by fldMould_No
       end
   else if @Id=4
       begin
    select fldMould_No,fldNop,fldPart_GW,fldPart_NW from tblM_Serial where fldSerial_No=@Serial_No order by fldMould_No
       end
   else if @Id=5
       begin
    select tblMould.fldMould_No,isnull(tblMould.fldMould_Name,''),isnull(tblMould.fldCycle_Time,0),isnull(tblMould.fldNoc,0),isnull(tblM_Serial.fldPart_GW,0),isnull(tblM_Serial.fldPart_NW,0),isnull(tblM_Serial.fldNop,0)
           from tblM_Serial,tblMould where tblMould.fldMould_No=tblM_Serial.fldMould_No and tblM_Serial.fldSerial_No=@Serial_No order by tblMould.fldMould_No
       end
      
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Find_Mould
@Mould_No nvarchar(10),
@Id int
AS
    if @Id=0
       begin
 select * from tblMould where fldMould_No like @Mould_No order by fldMould_No
    end
 else if @Id=1
    begin
   select fldMould_No,fldCycle_Time from tblMould where fldMould_No=@Mould_No order by fldMould_No
    end
   else if @Id=2
       begin
   select fldMould_No from tblMould  order by fldMould_No
        end
   else if @Id=3  --输入调整胶件啤制工作单
        begin
   select tblMould.fldMould_No from tblMould,tblM_Serial
   where tblMould.fldMould_No=tblM_Serial.fldMould_No and tblM_Serial.fldSerial_No=@Mould_No order by tblM_Serial.fldMould_No
        end
   else if @Id=4  --输入调整胶件啤制工作单
        begin
   select top 1 tblMould.fldMould_Name,tblMould.fldCycle_Time,tblM_Serial.fldPart_GW,tblM_Serial.fldPart_NW,tblM_Serial.fldNop from tblMould,tblM_Serial
   where tblMould.fldMould_No=tblM_Serial.fldMould_No and tblM_Serial.fldMould_No=@Mould_No
   order by tblM_Serial.fldMould_No
        end
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Find_Mould_Serial

@From_Mould_No nvarchar(10),
@To_Mould_No nvarchar(10)

AS
   
      begin
    select tblMould.*,tblM_Serial.* from tblMould,tblM_Serial where tblMould.fldMould_No=tblM_Serial.fldMould_No
          and tblMould.fldMould_No>=@From_Mould_No and tblMould.fldMould_No<=@To_Mould_No order by tblMould.fldMould_No
      end
     
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE  Sp_Get_Find_P_Bom
 @Item_No nvarchar(20),
 @Part_No nvarchar(20),
 @Id int
AS
     declare @temp1 table(Item_No nvarchar(20),Part_No nvarchar(20),Part_Name nvarchar(20),
                         Per_Qty decimal(9),P_1 decimal(9),M_1 nvarchar(10),P_2 decimal(9),M_2 nvarchar(10),
                         C_No nvarchar(10),C_Rate decimal(9),Mould_No nvarchar(10),Mould_Name nvarchar(30),
                         Noc int,Nop int,Cycle_Time decimal(12,2),Part_GW decimal(12,2),Part_NW decimal(12,2))
     declare @temp2 table(Item_No nvarchar(20),Part_No nvarchar(20),RPart_No nvarchar(20),Part_Name nvarchar(20),
                         Per_Qty decimal(9),P_1 decimal(9),M_1 nvarchar(10),P_2 decimal(9),M_2 nvarchar(10),
                         C_No nvarchar(10),C_Rate decimal(9),Mould_No nvarchar(10),Mould_Name nvarchar(30),
                         Noc int,Nop int,Cycle_Time decimal(12,2),Part_GW decimal(12,2),Part_NW decimal(12,2))
     declare @PartNo nvarchar(20),@Per_Qty decimal(9)
     if @Id=0
        begin
  select * from tblP_Bom where fldItem_No=@Item_No and fldPart_No like @Part_No order by fldItem_No
     end
  else if @Id=1
     begin
     select fldPer_Qty from tblP_Bom where fldItem_No=@Item_No and fldPart_No=@Part_No order by fldItem_No
        end
     else if @Id=2
     begin
     select * from tblP_Bom where fldItem_No=@Item_No and fldPart_No=@Part_No order by fldItem_No
        end
     else if @Id=3
     begin
     select fldItem_No,fldPart_No,fldPer_Qty from tblP_Bom where fldItem_No=@Item_No order by fldItem_No,fldPart_No
        end
     else if @Id=4
     begin
        begin
    insert into @temp1
    SELECT dbo.tblItem_Mas.fldItem_No AS Item_No, dbo.tblP_Bom.fldPart_No AS Part_No,
      dbo.tblPart.fldPart_Name AS Part_Name,dbo.tblP_Bom.fldPer_Qty AS Per_Qty,
      dbo.tblPart.fldP_1 AS P_1, dbo.tblPart.fldM_1 AS M_1, dbo.tblPart.fldP_2 AS P_2,
      dbo.tblPart.fldM_2 AS M_2, dbo.tblPart.fldC_No AS C_No,
      dbo.tblPart.fldC_Rate AS C_Rate, dbo.tblM_Serial.fldMould_No AS Mould_No,
      dbo.tblMould.fldMould_Name AS Mould_Name, dbo.tblMould.fldNoc AS Noc,
      dbo.tblM_Serial.fldNop AS Nop, dbo.tblMould.fldCycle_Time AS Cycle_Time,
      dbo.tblM_Serial.fldPart_GW AS Part_GW,
      dbo.tblM_Serial.fldPart_NW AS Part_NW
  FROM dbo.tblItem_Mas INNER JOIN
      dbo.tblP_Bom ON
      dbo.tblItem_Mas.fldItem_No = dbo.tblP_Bom.fldItem_No INNER JOIN
      dbo.tblPart ON dbo.tblP_Bom.fldPart_No = dbo.tblPart.fldPart_No INNER JOIN
      dbo.tblM_Serial ON SUBSTRING(dbo.tblP_Bom.fldPart_No, 5, 4)
      = dbo.tblM_Serial.fldSerial_No INNER JOIN
      dbo.tblMould ON dbo.tblM_Serial.fldMould_No = dbo.tblMould.fldMould_No
   where dbo.tblItem_Mas.fldItem_No=@Item_No order by dbo.tblItem_Mas.fldItem_No,dbo.tblPart.fldPart_No
        end
       
     declare Cur_Value cursor
     for select Part_No from @temp1 order by Item_No,Part_No
     open Cur_Value
     fetch next from Cur_Value
     into @PartNo
     while @@fetch_status=0
          begin
             insert into @temp2
             select Item_No,Part_No,'',Part_Name,Per_Qty,P_1,M_1,P_2,M_2,C_No,C_Rate,Mould_No,Mould_Name,
                    Noc,Nop,Cycle_Time,Part_GW,Part_NW
             from @temp1 where Item_No=@Item_No and Part_No=@PartNo order by Item_No,Part_No
             select @Per_Qty=fldPer_Qty from tblP_Bom where fldItem_No=@Item_No and fldPart_No=@PartNo order by fldItem_No,fldPart_No
             insert into @temp2
             select tblPr_Bom.fldItem_No,'代用胶件',rtrim(tblPr_Bom.fldRPart_No),tblPart.fldPart_Name,@Per_Qty,tblPart.fldP_1,tblPart.fldM_1,
                    tblPart.fldP_2,tblPart.fldM_2,tblPart.fldC_No,tblPart.fldC_Rate,tblMould.fldMould_No,tblMould.fldMould_Name,
                    tblMould.fldNoc,tblM_Serial.fldNop,tblMould.fldCycle_Time,tblM_Serial.fldPart_GW,tblM_Serial.fldPart_NW
             from tblPr_Bom,tblPart,tblM_Serial,tblMould
        where tblPr_Bom.fldItem_No=@Item_No and tblPr_Bom.fldPart_No=@PartNo and tblPr_Bom.fldRPart_No=tblPart.fldPart_No
                   and substring(tblPart.fldPart_No,5,4)=tblM_Serial.fldSerial_No and tblM_Serial.fldMould_No=tblMould.fldMould_No
             fetch next from Cur_Value into @PartNo
           end
    close Cur_Value
    deallocate Cur_Value
   
    select * from @temp2 order by Item_No
    delete from @temp1
    delete from @temp2
   
  end
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Find_Part
  @Part_No Nvarchar(20),
  @Id int
AS
   if @Id=0
      begin
  select fldPart_No from tblPart where fldPart_No=@Part_No order by fldPart_No
      end
   else if @Id=1
      begin
   select * from tblPart where fldPart_No like @Part_No order by fldPart_No desc
      end
   else if @Id=2
      begin
   select fldPart_No from tblPart order by fldPart_No
      end
   else if @Id=3
      begin
   select fldPart_No,isnull(fldPart_Name,'') as fldPart_Name,isnull(fldP_1,0) as fldP_1,isnull(fldM_1,'') as fldM_1,isnull(fldP_2,0) as fldP_2,isnull(fldM_2,'') as fldM_2,
          isnull(fldPart_Color,'') as fldPart_Color,isnull(fldC_No,'') as fldC_No,isnull(fldC_Rate,0) as fldC_Rate from tblPart where fldPart_No=@Part_No order by fldPart_No
      end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Find_Plastic
  @P_No Nvarchar(10),
  @Id int
AS
   if @Id=0
      begin
  select fldP_No from tblPlastic where fldP_No=@P_No order by fldP_No
      end
   else
      begin
   select * from tblPlastic where fldP_No like @P_No order by fldP_No desc
      end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Find_Pr_Bom
  @Item_No Nvarchar(20),
  @Part_No nvarchar(20),
  @RPart_No nvarchar(20),
  @Id int
AS
   if @Id=0
        begin
  select * from tblPr_Bom where fldItem_No=@Item_No and fldPart_No=@Part_No and fldRPart_No like @RPart_No order by fldItem_No,fldPart_No,fldRPart_No
     end
     else if @Id=1
     begin
     select * from tblPr_Bom where fldItem_No=@Item_No and fldPart_No=@Part_No and fldRPart_No=@RPart_No order by fldItem_No,fldPart_No,fldRPart_NO
        end
     else if @Id=2
     begin
     select fldRPart_No from tblPr_Bom where fldItem_No=@Item_No and fldPart_No=@Part_No and fldRPart_No=@RPart_No order by fldItem_No,fldPart_No,fldRPart_NO
        end
     else if @Id=3
     begin
     select fldItem_No,fldPart_No,fldRPart_No from tblPr_Bom where fldItem_No=@Item_No order by fldItem_No
        end
     else if @Id=4
     begin
     select fldRPart_No from tblPr_Bom where fldItem_No=@Item_No and fldPart_No=@Part_No order by fldItem_No,fldPart_No
        end
     else if @Id=5
        begin
     select fldItem_No,fldPart_No,fldRPart_No from tblPr_Bom where fldItem_No=@Item_No and fldPart_No=@Part_No order by fldItem_No,fldPart_No
        end
       
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Find_Prod_ORA

 @Prod_No nvarchar(10),
 @Id int
 
AS
 if @Id=0
    begin
 select * from tblTemp_Prod_ORA where fldProd_No=@Prod_No order by fldProd_No
    end
 else if @Id=1
    begin
 select fldProd_No from tblProd_ORA where fldProd_No=@Prod_No order by fldProd_No
    end
    else if @Id=2
    begin
 select * from tblProd_ORA where fldProd_No like @Prod_No order by fldProd_No
    end
   
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Find_Prod_ORB

 @Prod_No nvarchar(10),
 @Id int
 
AS
 if @Id=0
    begin
 select * from tblProd_ORB where fldProd_No=@Prod_No order by fldProd_No
    end
 
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Find_Prod_ORC

 @Prod_No nvarchar(10),
 @Id int
 
AS
 if @Id=0
    begin
 select * from tblProd_ORC where fldProd_No=@Prod_No order by fldProd_No,fldPart_No
    end
    
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Find_Rep_SKZ

@Id int

AS
    if @Id=0
      begin
   select distinct fldM_1 from tblRep_SKZ order by fldM_1
      end
   
    RETURN
    

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Find_Rpt_Plastic

 @P_From_No nvarchar(10),
 @P_To_No nvarchar(10)
 
AS
     begin
   select fldP_No,fldGrade_No,fldP_Name from tblPlastic where fldP_No>=@P_From_No and fldP_No<=@P_To_No order by fldP_No
     end
    
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Find_Temp_Prod_ORC

 @Prod_No nvarchar(10),
 @Id int
 
AS
 if @Id=0
    begin
 select * from tblTemp_Prod_ORC where fldProd_No=@Prod_No order by fldProd_No,fldPart_No
    end
    
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Find_tabBzgzd
  @Job_No Nvarchar(15),
  @Id int
AS

    if @Id=1
     begin
   select top 1 F_Job_No from tabBzgzd  order by F_Job_No desc
     end
    else if @Id=2
     begin
   select * from tabBzgzd where Part_No like @Job_No order by F_Job_No,Part_No
     end
   else if @Id=3
     begin
   select * from tabBzgzd where F_Job_No=@Job_No order by F_Job_No
     end
   return

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Find_tabBzxqb
@Part_No nvarchar(20),
@Id int
AS
    if @Id=1
    begin
  select * from tabBzxqb where F_REQS>0 and Part_No like @Part_No order by Part_No
     end
 else if @Id=2
     begin
  select JOB_No,ITEM_NO,F_RES_QTY from tabJjGzdHh where Part_No=@Part_No order by Part_No
     end
 else if @Id=3
     begin
   select * from tabBzxqb where F_REQS>0 and Part_No=@Part_No order by Part_No
     end
 else if @Id=4
     begin
  truncate table tabBzgzd
  select * from tabBzxqb where F_REQS>0 order by Part_No
     end
  RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_New_Bom
 @Item_No nvarchar(20),
 @Item_Name nvarchar(50),
 @Remark ntext,
 @InUser nvarchar(8),
 @InDate nvarchar(10),
 @TransFlag nvarchar(1)
AS
    begin
 Insert into tblItem_Mas values(@Item_No,@Item_Name,@Remark,@InUser,@InDate,@TransFlag)
    end
   
 RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_New_Colorant
  @C_No Nvarchar(20),
  @C_Name nvarchar(20),
  @CC_Name nvarchar(20),
  @C_Color nvarchar(20),
  @InUser nvarchar(8),
  @InDate nvarchar(10),
  @TransFlag nvarchar(1)
AS
  insert into tblColorant values(@C_No,@C_Name,@CC_Name,@C_Color,@InUser,@InDate,@TransFlag)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_New_JobDetail
  @Jorder_No nvarchar(10),
  @Sorder_No nvarchar(10),
  @Item_No nvarchar(20),
  @Part_No nvarchar(20),
  @Part_Name nvarchar(20),
  @Per_Qty numeric(12,2),
  @Qty numeric(12,2),
  @P_1 numeric(12,2),
  @M_1 nvarchar(10),
  @P_2 numeric(12,2),
  @M_2 nvarchar(10),
  @Part_Color nvarchar(20),
  @C_No nvarchar(10),
  @C_Rate numeric(12,2),
  @Date nvarchar(10)
 
AS
     begin
  insert into tblJobDetail values(@Jorder_No,@Sorder_No,@Item_No,@Part_No,@Part_Name,@Per_Qty,@Qty,@P_1,@M_1,@P_2,@M_2,
                                @Part_Color,@C_No,@C_Rate,@Date)
     end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_New_JobHead
  @Jorder_No nvarchar(10),
  @Sorder_No nvarchar(10),
  @Item_No nvarchar(20),
  @Item_Name Nvarchar(50), 
  @Prod_Qty numeric(12,2),
  @C_Qty numeric(12,2),
  @N_Qty numeric(12,2),
  @S_Date nvarchar(10),
  @C_Date nvarchar(10),
  @Remark text,
  @Prod_No nvarchar(10),
  @Date nvarchar(10),
  @TransFlag nvarchar(1)
 
AS
     begin
  insert into tblJobHead values(@Jorder_No,@Sorder_No,@Item_No,@Item_Name,@Prod_Qty,@C_Qty,@N_Qty,@S_Date,@C_Date,
                                @Remark,@Prod_No,@Date)
  update tblItem_Mas set fldTransFlag=@TransFlag where fldItem_No=@Item_No

     end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_New_MRP
@Line nvarchar(200),
@MRP_ID bigint,
@Id int

AS
    if @Id=1
      begin
  truncate table tabMRp
      end
    else if @Id=2
      begin
   insert into tabMRP values(@MRP_ID,@Line)
      end
    else if @Id=3
      begin
   select Line from tabMRP order by MRP_ID
      end
      
    RETURN
    

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_New_M_Serial
@Mould_No nvarchar(10),
@Serial_No nvarchar(10),
@Part_GW numeric(12,2),
@Part_NW numeric(12,2),
@Nop int,
@InUser nvarchar(8),
@InDate nvarchar(10),
@TransFlag nvarchar(1)

AS
 insert into tblM_Serial values(@Mould_No,@Serial_No,@Part_GW,@Part_NW,@Nop,@InUser,@InDate,@TransFlag)
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_New_Mould
@Mould_No nvarchar(10),
@Mould_Name nvarchar(30),
@MachineType nvarchar(2),
@Mould_GW numeric(9,2),
@Mould_NW numeric(9,2),
@Cycle_Time numeric(9,2),
@Noc int,
@Photo_No nvarchar(10),
@M_Date nvarchar(10),
@T_Date nvarchar(10),
@Location nvarchar(20),
@Remark text,
@InUser nvarchar(8),
@InDate nvarchar(10),
@TransFlag nvarchar(1)

AS
 insert into tblMould values(@Mould_No,@Mould_Name,@MachineType,@Mould_GW,@Mould_NW,@Cycle_Time,@Noc,@Photo_No,@M_Date,@T_Date,
                             @Location,@Remark,@InUser,@InDate,@TransFlag)
 RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE  Sp_Get_New_P_Bom
    @Item_No nvarchar(20),
    @Part_No nvarchar(20),
    @Per_Qty numeric(9,2),
    @TransFlag1 nvarchar(1)
AS
    insert into tblP_Bom values(@Item_No,@Part_No,@Per_Qty)
 update tblPart set fldTransFlag=@TransFlag1 where fldPart_No=@Part_No
 
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_New_Part
  @Part_No Nvarchar(20),
  @Part_Name nvarchar(20),
  @P_1 numeric(9,2),
  @M_1 nvarchar(10),
  @P_2 numeric(9,2),
  @M_2 nvarchar(10),
  @Part_Color nvarchar(20),
  @C_No nvarchar(20),
  @C_Rate numeric(9,2),
  @Remark text,
  @InUser nvarchar(8),
  @InDate nvarchar(10),
  @TransFlag nvarchar(1),
  @TransFlag1 nvarchar(1),
  @TransFlag2 nvarchar(1)
AS
   begin
   insert into tblPart values (@Part_No,@Part_Name,@P_1,@M_1,@P_2,@M_2,@Part_Color,@C_No,@C_Rate,@Remark,@InUser,@InDate,@TransFlag)
   update tblPlastic set fldTransFlag=@TransFlag1 where fldP_No=@M_1
   update tblPlastic set fldTransFlag=@TransFlag1 where fldP_No=@M_2
   update tblColorant set fldTransFlag=@TransFlag2 where fldC_No=@C_No
   end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_New_Plastic
  @P_No Nvarchar(10),
  @Grade_No nvarchar(10),
  @P_Name nvarchar(40),
  @InUser nvarchar(8),
  @InDate nvarchar(10),
  @TransFlag nvarchar(1)
AS
  insert into tblPlastic(fldP_No,fldGrade_No,fldP_Name,fldPrice,fldInUser,fldInDate,fldTransFlag)
         values(@P_No,@Grade_No,@P_Name,0,@InUser,@InDate,@TransFlag)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_New_Pr_Bom
 @Item_No nvarchar(20),
 @Part_No nvarchar(20),
 @RPart_No nvarchar(20),
 @TransFlag1 nvarchar(1)
AS
 Insert into tblPr_Bom values(@Item_No,@Part_No,@RPart_No)
 update tblPart set fldTransFlag=@TransFlag1 where fldPart_No=@RPart_No
 
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE  Sp_Get_New_Prod

  @Prod_No nvarchar(10)
 
AS
 
 insert into tblProd_ORA select * from tblTemp_Prod_ORA
 
 insert into tblProd_ORB select * from tblTemp_Prod_ORB
 
 insert into tblProd_ORC select * from tblTemp_Prod_ORC
 
 update tblJobHead set fldProd_No=@Prod_No where fldJorder_No in (select fldJorder_No from tblTemp_Prod_ORB)
 
 update tblM_Serial set fldTransFlag='T' where fldMould_No in (select fldMould_No from tblTemp_Prod_ORC)
 
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_New_Prod_ORA
@Prod_No nvarchar(10),
@Item_No nvarchar(20),
@Item_Name nvarchar(50),
@Prod_Qty numeric(12,2),
@S_Date nvarchar(10),
@C_Date nvarchar(10),
@Rep nvarchar(2),
@Date nvarchar(10)

as

   insert into tblProd_ORA values(@Prod_No,@Item_No,@Item_Name,@Prod_Qty,@S_Date,@C_Date,@Rep,@Date)

 
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_New_Prod_ORB
@Prod_No nvarchar(10),
@Jorder_No nvarchar(10),
@Sorder_No nvarchar(10),
@Item_No nvarchar(10),
@Prod_Qty numeric(12,2),
@S_Date nvarchar(10),
@C_Date nvarchar(10)

as

   insert into tblProd_ORB values(@Prod_No,@Jorder_No,@Sorder_No,@Item_No,@Prod_Qty,@S_Date,@C_Date)

 
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_New_Prod_ORC
@Prod_No nvarchar(10),
@Part_No nvarchar(20),
@Part_Name nvarchar(20),
@Per_Qty numeric(9),
@Qty numeric(12,2),
@P_1 numeric(9),
@M_1 nvarchar(10),
@P_2 numeric(9),
@M_2 nvarchar(10),
@Part_Color nvarchar(20),
@C_No nvarchar(10),
@C_Rate numeric(9),
@Mould_No nvarchar(10),
@Mould_Qty numeric(9),
@Part_GW numeric(12,2),
@Part_NW numeric(12,2),
@IMM_No nvarchar(10)

as

   insert into tblProd_ORC values(@Prod_No,@Part_No,@Part_Name,@Per_Qty,@Qty,@P_1,@M_1,@P_2,@M_2,@Part_Color,
                                  @C_No,@C_Rate,@Mould_No,@Mould_Qty,@Part_GW,@Part_NW,@IMM_No)

 
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_New_Rep_Plastic
@P_No nvarchar(10),
@TWT decimal(18,3),
@Pack decimal(9,0),
@BWT decimal(18,3)

AS
 
   update tblRep_Plastic set fldTWT=@TWT,fldPack=@Pack,fldBWT=@BWT where fldP_No=@P_No
  
  
   
    RETURN
    

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_New_Rep_SKZ
@M_1 nvarchar(10),
@TWT decimal(18,3),
@Pack decimal(9,0),
@BWT decimal(18,3)

AS
  
      begin
   update tblRep_SKZ set fldTWT=@TWT,fldPack=@Pack,fldBWT=@BWT
      where fldM_1=@M_1
      end
 
      
    RETURN
    

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_New_Temp_ORA
@Prod_No nvarchar(10),
@Item_No nvarchar(20),
@Item_Name nvarchar(50),
@Prod_Qty numeric(12,2),
@S_Date nvarchar(10),
@C_Date nvarchar(10),
@Rep nvarchar(2),
@Date nvarchar(10)

as

   insert into tblTemp_Prod_ORA values(@Prod_No,@Item_No,@Item_Name,@Prod_Qty,@S_Date,@C_Date,@Rep,@Date)

 
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_New_Temp_ORB
@Prod_No nvarchar(10),
@Jorder_No nvarchar(1000)

AS

   insert into tblTemp_Prod_ORB
   exec('select '+@Prod_No+',fldJorder_No,fldSorder_No,fldItem_No,fldProd_Qty,fldS_Date,fldC_Date from tblJobHead
          where fldJorder_No in ('+ @Jorder_No +')')
 
    RETURN
    

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_New_Temp_ORC
@Prod_No nvarchar(10),
@Part_No nvarchar(20),
@Part_Name nvarchar(20),
@Per_Qty numeric(9),
@Qty numeric(12,2),
@P_1 numeric(9),
@M_1 nvarchar(10),
@P_2 numeric(9),
@M_2 nvarchar(10),
@Part_Color nvarchar(20),
@C_No nvarchar(10),
@C_Rate numeric(9),
@Mould_No nvarchar(10),
@Mould_Qty numeric(9),
@Part_GW numeric(12,2),
@Part_NW numeric(12,2),
@IMM_No nvarchar(10)

as

   insert into tblTemp_Prod_ORC values(@Prod_No,@Part_No,@Part_Name,@Per_Qty,@Qty,@P_1,@M_1,@P_2,@M_2,@Part_Color,
                                       @C_No,@C_Rate,@Mould_No,@Mould_Qty,@Part_GW,@Part_NW,@IMM_No)

 
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_New_tabBzgzd

@Job_No nvarchar(15),
@Mould_No nvarchar(10),
@Part_No nvarchar(20),
@F_Item nvarchar(40),
@F_MCYS nvarchar(50),
@F_Bzxqsl numeric(20),
@F_ZS numeric(12),
@F_Dqccsl numeric(12),
@F_Bzrq smalldatetime,
@F_Wcrq smalldatetime,
@F_MJMZ numeric(12,2),
@F_MJJZ numeric(12,2),
@F_ZMZ numeric(12,1),
@F_ZJZ numeric(12,1),
@F_P_No1 nvarchar(15),
@F_M1 numeric(12),
@F_YJYL1 numeric(12,1),
@F_P_No2 nvarchar(15),
@F_M2 numeric(12),
@F_YJYL2 numeric(12,1),
@F_CNO nvarchar(15),
@F_CM numeric(12,2),
@F_YJSFYL numeric(12,1),
@F_LCF bit,
@F_MDNM nvarchar(20),
@F_Nop numeric(9),
@F_MBZQ numeric(12,2),
@F_YJZSS numeric(12,2),
@F_BZJT nvarchar(30),
@F_24XSCL numeric(12),
@F_24XSYL numeric(12),
@F_InUser nvarchar(10),
@F_InDate smalldatetime

AS
   
    begin
    set @F_Wcrq=DateAdd(day,round(@F_YJZSS/24,0),@F_Bzrq)
  insert into tabBzgzd values(@Job_No,@Mould_No,@Part_No,@F_Item,@F_MCYS,@F_Bzxqsl,@F_ZS,@F_Dqccsl,@F_Bzrq,@F_Wcrq,
                              @F_MJMZ,@F_MJJZ,@F_ZMZ,@F_ZJZ,@F_P_No1,@F_M1,@F_YJYL1,@F_P_No2,@F_M2,@F_YJYL2,@F_CNO,@F_CM,@F_YJSFYL,
                              @F_LCF,@F_MDNM,@F_Nop,@F_MBZQ,@F_YJZSS,@F_BZJT,@F_24XSCL,@F_24XSYL,@F_InUser,@F_InDate)
    end
  
 
    RETURN
    
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE  Sp_Get_New_tabBzxqb
  @F_HHYXQ nvarchar(4),@F_GZDYXQ nvarchar(4),@F_JJYXQ NVARCHAR(4),@F_Job_No nvarchar(10),
  @Part_No nvarchar(20),@F_TF nvarchar(1),@F_Item nvarchar(40),@F_MCYS nvarchar(50),@F_DQCCSL numeric(12),
  @F_Date1 nvarchar(10),@F_RES1 numeric(12),@F_REQ1 numeric(12),
  @F_Date2 nvarchar(10),@F_RES2 numeric(12),@F_REQ2 numeric(12),
  @F_Date3 nvarchar(10),@F_RES3 numeric(12),@F_REQ3 numeric(12),
  @F_Date4 nvarchar(10),@F_RES4 numeric(12),@F_REQ4 numeric(12),
  @F_Date5 nvarchar(10),@F_RES5 numeric(12),@F_REQ5 numeric(12),
  @F_Date6 nvarchar(10),@F_RES6 numeric(12),@F_REQ6 numeric(12),
  @F_RESS numeric(12),@F_REST numeric(12),@F_REQS numeric(12),@F_REQT numeric(12),@F_XQRQ nvarchar(10),
  @F_SHL numeric(12),@F_BZXQSL numeric(12),@F_QZRQ numeric(12),@F_BZRQ nvarchar(10),
  @F_MJMZ numeric(12,2),@F_ZMZ numeric(12,2),@F_MJJZ numeric(12,2),
  @F_P_NO1 nvarchar(15),@F_M1 numeric(12),@F_YJYL1 numeric(12,1),
  @F_P_NO2 nvarchar(15),@F_M2 numeric(12),@F_YJYL2 numeric(12,1),
  @F_CNO nvarchar(15),@F_CM numeric(12),@F_YJSFYL numeric(12,1),@F_LCF bit,
  @Mould_No nvarchar(10),@F_MDNM nvarchar(16),@F_NOP numeric(12),@F_MBZQ numeric(12,2),@F_YJZSS numeric(12,1),
  @Mould_No2 nvarchar(10),@F_MDNM2 nvarchar(16),@F_NOP2 numeric(12),@F_MBZQ2 numeric(12,2),@F_YJZSS2 numeric(12,1),
  @F_MJMZ2 numeric(12,2),@F_ZMZ2 numeric(12,2),@F_MJJZ2 numeric(12,2),@F_YJYL12 numeric(12,1),@F_YJYL22 numeric(12,1),@F_YJSFYL2 numeric(12,1),
  @F_InUser nvarchar(10),@F_InDate smalldatetime
 
 
  AS
  
    begin
 insert into tabBzxqb values(@F_HHYXQ,@F_GZDYXQ,@F_JJYXQ,@F_Job_No,@Part_No,@F_TF,@F_Item,@F_MCYS,@F_DQCCSL,
             @F_Date1,@F_RES1,@F_REQ1,@F_Date2,@F_RES2,@F_REQ2,@F_Date3,@F_RES3,@F_REQ3,@F_Date4,@F_RES4,@F_REQ4,
             @F_Date5,@F_RES5,@F_REQ5,@F_Date6,@F_RES6,@F_REQ6,@F_RESS,@F_REST,@F_REQS,@F_REQT,@F_XQRQ,
             @F_SHL,@F_BZXQSL,@F_QZRQ,@F_BZRQ,@F_MJMZ,@F_ZMZ,@F_MJJZ,@F_P_NO1,@F_M1,@F_YJYL1,@F_P_NO2,@F_M2,
             @F_YJYL2,@F_CNO,@F_CM,@F_YJSFYL,@F_LCF,@Mould_No,@F_MDNM,@F_NOP,@F_MBZQ,@F_YJZSS,
             @Mould_No2,@F_MDNM2,@F_NOP2,@F_MBZQ2,@F_YJZSS2,@F_MJMZ2,@F_ZMZ2,@F_MJJZ2,@F_YJYL12,@F_YJYL22,
             @F_YJSFYL2,@F_InUser,@F_InDate)
    end
   
 
  
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_New_tabJjGzdHh
  @Job_No nvarchar(10),
  @Item_No nvarchar(20),
  @Part_No nvarchar(20),
  @RES_QTY numeric(12),
  @HHYXQ nvarchar(4),
  @GZDYXQ nvarchar(4),
  @JJYXQ nvarchar(4)
 
AS
    begin
   
  insert into tabJjGzdHh values(@Job_No,@Item_No,@Part_No,@RES_QTY,@HHYXQ,@GZDYXQ,@JJYXQ)
 
    end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Rep_Pla_Col
@Prod_No nvarchar(1000),
@Id int

AS
 
    if @Id=1  
      begin
   delete from tblRep_Plastic
   delete from tblRep_Colorant
   delete from tblRep_Pla_Col
   delete from tblRep_SKZ
  
     /* 产生胶粒色粉汇总表*/
   insert into tblRep_Pla_Col(fldPart_No)
   exec('select distinct fldPart_No from tblProd_ORC  where fldProd_No in ('+ @Prod_No +')')
  
      end
  else if @Id=2
      begin
  select fldPart_No from tblRep_Pla_Col
      end
 
    RETURN
    

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Rep_Plastic
(@Id int,@P_No nvarchar(10))

AS
  
   if @Id=1
       begin
    /* 产生胶粒汇总表 */
   insert into tblRep_Plastic(fldP_No,fldGrade_No,fldP_Name)
   select fldP_No,fldGrade_No,fldP_Name from tblPlastic
          where fldP_No in (select distinct fldM_1 from tblRep_Pla_Col)
         
            
   insert into tblRep_Plastic(fldP_No,fldGrade_No,fldP_Name)
    select fldP_No,fldGrade_No,fldP_Name from tblPlastic
          where fldP_No in (select distinct fldM_2 from tblRep_Pla_Col) and fldP_No not in (select fldP_No from tblRep_Plastic)
  
   update tblRep_Plastic set fldTWT=0,fldPack=0,fldBWT=0
  
   /*生产色粉汇总表*/
 insert into tblRep_Colorant(fldC_No,fldCC_Name,fldC_Name,fldC_Color,fldRate)
 SELECT tblrep_pla_Col.fldC_No,tblcolorant.fldC_Name,tblcolorant.fldCC_Name,tblcolorant.fldC_Color,sum(tblrep_pla_Col.fldC_Rate) as C_Rate
        from tblColorant,tblRep_Pla_Col
        where tblColorant.fldC_No=tblrep_pla_Col.fldC_No and tblcolorant.fldC_no
        in (select distinct tblRep_Pla_Col.fldC_No from tblRep_Pla_Col)
        group by tblRep_pla_Col.fldc_No,tblcolorant.fldC_Name,tblcolorant.fldCC_Name,tblcolorant.fldC_color
 
   /*生产水口枝汇总表*/
   insert into tblRep_SKZ(fldM_1,fldGrade_No,fldP_Name)
   select tblRep_Pla_Col.fldM_1,tblPlastic.fldGrade_No,fldP_Name
        from tblPlastic,tblRep_Pla_Col
        where tblPlastic.fldP_No=tblRep_Pla_Col.fldM_1
        group by tblRep_Pla_Col.fldM_1,tblPlastic.fldGrade_No,tblPlastic.fldP_Name
       
   insert into tblRep_SKZ values('I15R','','',0,0,0)
   update tblRep_SKZ set fldM_1=fldM_1+'R',fldTWT=0,fldPack=0,fldBWT=0 where right(fldM_1,1)<>'R'
  
        end
    else if @Id=2
        begin
     select fldP_No from tblRep_Plastic order by fldP_No  
        end
    else if @Id=3
        begin
 
    select sum(fldM1_WT)  as TWT1 from tblRep_Pla_Col where fldM_1=@P_No Group by fldM_1
   
        end
    else if @Id=4
         begin
    
    select sum(fldM2_WT) as TWT2 from tblRep_Pla_Col where fldM_2=@P_No Group by fldM_2
     
        end
    RETURN
    

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Rep_Prod_ORC
   
    @Prod_No nvarchar(1000),
 @Part_No nvarchar(20),
 @Id int
 
AS
 
   if @Id=1 
    begin
   
   declare @sql nvarchar(4000)
      set @sql='select top 1 fldPart_Name,fldMould_No,fldPart_GW,fldPart_NW,fldP_1,fldM_1,fldP_2,fldM_2,fldPart_Color,fldC_No
            from tblProd_ORC where fldPart_No='''+@Part_No+''' and fldProd_No in ('+ @Prod_No + ') order by fldProd_No,fldPart_No'
   exec(@sql)
    end
   else if @Id=2
    begin
    declare @sqlstr nvarchar(4000)
    set @sqlstr='select Sum(fldQty) as Qty,Sum(fldMould_Qty) as Mould_Qty from tblProd_ORC where fldPart_No='''+@Part_No+''' and fldProd_No in (' + @Prod_No + ') group by fldPart_No'
   exec(@sqlstr)
    end
    
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Truncate_Table

AS
 truncate table tblTemp_Prod_ORA
 truncate table tblTemp_Prod_ORB
 truncate table tblTemp_Prod_ORC
 truncate table tblTemp_Prod_ORD
 
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Truncate_tabJjGzdHh
 
 AS
  
     begin
  truncate table tabJjGzdHh
     end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE  Sp_Get_UpDate_tabBzxqb
  @Part_No nvarchar(20),
  @F_TF nvarchar(1),
  @F_RESS numeric(12),
  @F_REQS numeric(12),
  @F_SHL numeric(9),
  @F_BZXQSL numeric(12),
  @F_QZRQ numeric(9),
  @F_BZRQ nvarchar(10),
  @Mould_No nvarchar(10),
  @F_MDNM nvarchar(16),
  @F_YJZSS  numeric(12,1),
  @F_Nop numeric(9),
  @F_MBZQ numeric(12,2),
  @Mould_No2 nvarchar(10),
  @F_MDNM2 nvarchar(16),
  @F_YJZSS2  numeric(12,1),
  @F_Nop2 numeric(9),
  @F_MBZQ2 numeric(12,2)
   
  AS
 
 begin
 update tabBzxqb set F_TF=@F_TF,F_RESS=@F_RESS,F_REQS=@F_REQS,F_SHL=@F_SHL,F_BZXQSL=@F_BZXQSL,F_QZRQ=@F_QZRQ,
                     F_BZRQ=@F_BZRQ,MOULD_NO=@MOULD_NO,F_MDNM=@F_MDNM,F_YJZSS=@F_YJZSS,F_Nop=@F_Nop,F_MBZQ=@F_MBZQ,
                     MOULD_NO2=@MOULD_NO2,F_MDNM2=@F_MDNM2,F_YJZSS2=@F_YJZSS2,F_Nop2=@F_Nop2,F_MBZQ2=@F_MBZQ2
                     where Part_No=@Part_No
 end
  
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_UpFile
@FileName nvarchar(50),
@Id int
AS
   if @id=1
 begin
 select * from UpFile order by filedate
 end
   else if @id=2
    begin
    delete from UpFile where filename=@filename
    end
 
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Update_Temp_ORC
@Prod_No nvarchar(10),
@Part_No nvarchar(20),
@M_1 nvarchar(10),
@M_2 nvarchar(10),
@Mould_No nvarchar(10),
@IMM_No nvarchar(10)

as

   update tblTemp_Prod_ORC set fldM_1=@M_1,fldM_2=@M_2,fldMould_No=@Mould_No,fldIMM_No=@IMM_No where fldProd_No=@Prod_No and fldPart_No=@Part_No
 
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_Update_tabBzgzd

@Job_No nvarchar(15),
@F_Bzxqsl numeric(12),
@F_Bzrq smalldatetime,
@F_Wcrq smalldatetime,
@F_P_No1 nvarchar(15),
@F_YJYL1 numeric(12,1),
@F_P_No2 nvarchar(15),
@F_YJYL2 numeric(12,1),
@F_CNO nvarchar(15),
@F_CM numeric(9),
@F_YJSFYL numeric(12,1),
@F_YJZSS numeric(9,2),
@F_BZJT nvarchar(30),
@F_ZMZ numeric(9,1),
@F_ZJZ numeric(9,1),
@F_24XSCL numeric(9),
@F_24XSYL numeric(9)

AS
   
    begin
    set @F_Wcrq=DateAdd(day,round(@F_YJZSS/24,0),@F_Bzrq)
  update tabBzgzd set F_Bzxqsl=@F_Bzxqsl,F_Bzrq=@F_Bzrq,F_Wcrq=@F_Wcrq,F_P_No1=@F_P_No1,F_P_No2=@F_P_No2,
                      F_YJYL1=@F_YJYL1,F_YJYL2=@F_YJYL2,F_CNO=@F_CNO,F_CM=@F_CM,F_YJSFYL=@F_YJSFYL,F_YJZSS=@F_YJZSS,
                      F_BZJT=@F_BZJT,F_ZMZ=@F_ZMZ,F_ZJZ=@F_ZJZ,F_24XSCL=@F_24XSCL,F_24XSYL=@F_24XSYL
                      where F_Job_No=@Job_No
    end  
 
    RETURN
    

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_UserLogin
 
 @UserId nvarchar(20),
    @UserPswd nvarchar(50),
    @CTD Nvarchar(10)
AS
  select fldUserID,fldUserPswd,fldCTD from tblPassword where fldUserId=@UserId and fldUserPswd=@UserPswd and fldCTD=@CTD order by fldUserId

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Get_truncate_tabBzxqb
 
 AS
 
     begin
  truncate table tabBzxqb
     end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Mis_Part

AS
 
 begin
    --delete from Mis_Part
   
    --insert into Mis_Part
    select Part_No,Part_Name from Part
    where Part_No not in (select fldPart_No from tblPart)
    order by Part_No
   
    --select Part_No from Mis_Part order by Part_No
 end
 return
 
 
 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--计算每月胶件更新价格
CREATE PROCEDURE  Sp_Month_PartPrice
AS
      begin
      set nocount on
     
      declare @PartPrice table(PartNo nvarchar(20),M1 nvarchar(10),P1 decimal(9),M2 nvarchar(10),P2 decimal(9),Part_GW decimal(9,2),Part_NW decimal(9,2),MachineType nvarchar(2),Cycle_Time decimal(12,0),Noc int)

     insert into  @PartPrice
     select tblPart.fldPart_No,tblPart.fldM_1,tblPart.fldP_1,tblPart.fldM_2,tblPart.fldP_2,tblM_Serial.fldPart_GW,tblM_Serial.fldPart_NW,tblMould.fldMachineType,tblMould.fldCycle_Time,tblMould.fldNoc
     from Part,tblPart,tblM_Serial,tblMould
     where Part.Part_No=tblPart.fldPart_No and (tblPart.fldM_1<>'')
          and substring(Part.Part_No,5,4)=tblM_Serial.fldSerial_No and tblM_Serial.fldMould_No=tblMould.fldMould_No
     order by tblPart.fldPart_No
    end

    declare @PartNo nvarchar(20),@M1 nvarchar(10),@M2 nvarchar(10),@MachineType nvarchar(2)
    declare @P1 decimal(9),@P2 decimal(9),@Part_GW decimal(9,2)
    declare @M1_Price decimal(12,5),@M2_Price decimal(12,5),@M1Price decimal(12,5),@M2Price decimal(12,5),@Cycle_Time decimal(12,0),@Noc int
    declare @ColorCost decimal(12,1),@MachineCost decimal(12,0),@MachinePrice decimal(12,5),@TotalPrice decimal(12,5)
    declare @Price table(PartNo nvarchar(20),Cost decimal(12,4))
   
    declare Cur_Value cursor for
    select PartNo,M1,M2,P1,P2,Part_GW,MachineType,Cycle_Time,Noc from @PartPrice where Part_GW>=Part_NW order by PartNo
   
    open Cur_Value
    fetch next from Cur_Value into @PartNo,@M1,@M2,@P1,@P2,@Part_GW,@MachineType,@Cycle_Time,@Noc
    while @@fetch_status=0
    begin
        --计算机种类型
        if @MachineType='B'
           set @MachineCost=3000
        else if @MachineType='M'
           set @MachineCost=2000
        else if @MachineType='S'
           set @MachineCost=1000
        if right(@PartNo,2)='TA' or right(@PartNo,2)='NA' --以TA/NA结尾的胶件不需加色粉
           set @ColorCost=0
        else
           set @ColorCost=0.8 --色粉单价：以￥20每份配一包25kg原料计
        set @MachinePrice=@MachineCost/((86400/@Cycle_Time)*@Noc) --啤工成本(机器成本)
          
        if @M1<>''
           begin
               if @P1=100
                   if right(@M1,1)='R'
                      begin
                        select @M1Price=fldPrice from tblPlastic where fldP_No=@M1 order by fldP_No
                        if (@M1Price=0 or @M1Price is null)
                           select @M1Price=fldPrice from tblPlastic where fldP_No=substring(@M1,1,len(@M1)-1) order by fldP_No
                       
                        set @M1Price=@M1Price+@ColorCost
                        set @M1_Price=@M1Price*@Part_GW*@P1/100/1000
      
                        set @TotalPrice=@MachinePrice+@M1_Price
                        insert into @Price
                        select @PartNo,@TotalPrice
                      end
                   else
                      begin
                        select @M1Price=fldPrice from tblPlastic where fldP_No=@M1 order by fldP_No
                        set @M1Price=@M1Price+@ColorCost
                        set @M1_Price=@M1Price*@Part_GW*@P1/100/1000
                    
                        set @TotalPrice=@MachinePrice+@M1_Price
                        insert into @Price
                        select @PartNo,@TotalPrice
                      end
               else if @P1<>100
                    begin
                        if @M1+'R'<> @M2
                            begin
                                select @M1Price=fldPrice from tblPlastic where fldP_No=@M1 order by fldP_No
                                select @M2Price=fldPrice from tblPlastic where fldP_No=@M2 order by fldP_No
                                set @M1Price=@M1Price+@ColorCost
                                set @M2Price=@M2Price+@ColorCost
                                set @M1_Price=@M1Price*@Part_GW*@P1/100/1000
                                set @M2_Price=@M2Price*@Part_GW*@P2/100/1000
                                set @TotalPrice=@M1_Price+@M2_Price+@MachinePrice
    
                              insert into @Price
                                select @PartNo,@TotalPrice
                            end
                        else if @M1+'R'=@M2
                            begin
                                select @M1Price=fldPrice from tblPlastic where fldP_No=@M1 order by fldP_No
                                select @M2Price=fldPrice from tblPlastic where fldP_No=@M2 order by fldP_No
                                set @M1Price=@M1Price+@ColorCost
                                set @M2Price=@M2Price+@ColorCost
                                   if (@M2Price=0 or @M2Price is Null)
                                   begin
                                       set @M2Price=@M1Price
                                   end
      
                                set @M1_Price=@M1Price*@Part_GW*@P1/100/1000
                                set @M2_Price=@M2Price*@Part_GW*@P2/100/1000
                                set @TotalPrice=@M1_Price+@M2_Price+@MachinePrice
                                insert into @Price
                                select @PartNo,@TotalPrice
                            end
                        else if right(@M1,1)='R'
                             begin
                               select @M1Price=fldPrice from tblPlastic where fldP_No=@M1 order by fldP_No
                               if (@M1Price=0 or @M1Price is null)
                                   select @M1Price=fldPrice from tblPlastic where fldP_No=substring(@M1,1,len(@M1)-1) order by fldP_No
                               select @M2Price=fldPrice from tblPlastic where fldP_No=@M2 order by fldP_No
                               if (@M2Price=0 or @M2Price is null)
                                   select @M2Price=fldPrice from tblPlastic where fldP_No=substring(@M2,1,len(@M2)-1) order by fldP_No

                                set @M1Price=@M1Price+@ColorCost
                                set @M2Price=@M2Price+@ColorCost
                                set @M1_Price=@M1Price*@Part_GW*@P1/100/1000
                                set @M2_Price=@M2Price*@Part_GW*@P2/100/1000
                                set @TotalPrice=@M1_Price+@M2_Price+@MachinePrice
                               
                                insert into @Price
                                select @PartNo,@TotalPrice
                             end
                        
                    end
           end
           fetch next from Cur_Value into @PartNo,@M1,@M2,@P1,@P2,@Part_GW,@MachineType,@Cycle_Time,@Noc
    end
   
    Close Cur_Value
    Deallocate Cur_Value
   
    truncate table PartCost
    insert into PartCost(PartNo,Cost)
    select *
    from @Price
    order by PartNo
  
    delete from @PartPrice
    delete from @Price
    

    RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE  Sp_Month_PartPrice1
AS
      begin
      set nocount on
      select distinct a.PartNo,a.Cost
      from PartCost a
      where not exists(select 0 from PartCost b where a.PartNo=b.PartNo and a.Cost<b.Cost)
      order by PartNo
      end
    RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_New_UpFile
@FileName nvarchar(50),
@FileType nvarchar(50),
@FileLength int,
@UserId nvarchar(10),
@FileDate DateTime
AS
 begin
 delete from UpFile where filename=@FileName 
 insert into UpFile values(@FileName,@FileType,@FileLength,@UserId,@FileDate)
 end
 
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/*--调用示例
 --导出dBase
 p_exporttb @sqlstr='select * from 地区资料',@path='c:/',@over=1
--*/
--if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
--drop procedure [dbo].[p_exporttb]
--GO
--p_exporttb @tbname='tmp_allmedia',@path='e:/',@fname='allmedia.dbf',@over=1

 

CREATE proc Sp_Out_EF
@sqlstr varchar(100),   --要导出的查询名
@path nvarchar(100),   --文件存放目录
@fname nvarchar(50)='EF.dbf',--文件名,默认为temp
@over bit=0      --是否覆盖已经存在的文件,如果不覆盖,则直接追加
as

declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)

--先清空之前数据并生成新的数据
   begin
  
     truncate table EF
  
     insert into EF
     select tblPart.fldInDate,'',tblPart.fldPart_No,fldPart_Name,0,fldM_1,0,fldP_1,0,fldM_2,0,0,fldP_2,fldC_No,fldC_Rate,tblM_Serial.fldPart_GW,0,tblM_Serial.fldPart_NW,0,0,0,0
     from Part,tblPart,tblM_Serial
     where Part.Part_No=tblPart.fldPart_No and (tblPart.fldM_1<>'') and Part.StdCost=0
          and substring(Part.Part_No,5,4)=tblM_Serial.fldSerial_No
     order by tblPart.fldPart_No
   
    end

--参数检测
if isnull(@fname,'')='' set @fname='EF.dbf'

--检查文件是否已经存在
if right(@path,1)<>'/' set @path=@path+'/'
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql
if exists(select 1 from #tb where a=1)
 if @over=1
 begin
  set @sql='del '+@sql
  exec master..xp_cmdshell @sql,no_output
 end
 else
  set @over=0
else
 set @over=1

--数据库创建语句
set @sql=@path+@fname
set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="dBASE 5.0;'
 +';HDR=NO;DATABASE='+@path+'"'

--创建表的SQL
declare @tbname sysname
set @tbname='##tmp_'+convert(varchar(38),newid())
set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a'
exec(@sql)

--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
--exec @err=sp_oacreate 'SQLDMO.SQLServer',@obj out
if @err<>0 goto lberr

exec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr

--创建表的SQL
select @sql='',@fdlist=''
select @fdlist=@fdlist+','+a.name
 ,@sql=@sql+',['+a.name+'] '
  +case when b.name in('char','nchar','varchar','nvarchar') then
     'text('+cast(case when a.length>250 then 250 else a.length end as varchar)+')'
   when b.name in('tynyint','int','bigint','tinyint') then 'int'
   when b.name in('smalldatetime','datetime') then 'datetime'
   when b.name in('money','smallmoney') then 'money'
   else b.name end
FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype
where b.name not in('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
 and a.id=(select id from tempdb..sysobjects where name=@tbname)
select @sql='create table ['+@fname
 +']('+substring(@sql,2,8000)+')'
 ,@fdlist=substring(@fdlist,2,8000)
 

if @over=1
begin
 exec @err=sp_oamethod @obj,'execute',@out out,@sql
 if @err<>0 goto lberr
end

exec @err=sp_oadestroy @obj

set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''dBase 5.0;DATABASE='
 +@path+''',''select * from ['+@fname+']'')'

--导入数据
exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')

set @sql='drop table ['+@tbname+']'
exec(@sql)

return

lberr:
 exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
 select cast(@err as varbinary(4)) as 错误号
  ,@src as 错误源,@desc as 错误描述
 select @sql,@constr,@fdlist

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Part_Price
 
AS
   begin
  
     truncate table EF
  
     insert into EF
     select tblPart.fldInDate,'',tblPart.fldPart_No,fldPart_Name,0,fldM_1,0,fldP_1,0,fldM_2,0,0,fldP_2,fldC_No,fldC_Rate,tblM_Serial.fldPart_GW,0,tblM_Serial.fldPart_NW,0,0,0,0
    from Part,tblPart,tblM_Serial
    where Part.Part_No=tblPart.fldPart_No and (tblPart.fldM_1<>'') and Part.StdCost=0
          and substring(Part.Part_No,5,4)=tblM_Serial.fldSerial_No
    order by tblPart.fldPart_No
   
    end
 
  return
 
 
 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Part_Serial
 @Part_No nvarchar(20),
 @Id int
 
AS
    if @Id=1
        begin
          select tblPart.fldPart_No as Part_No,tblPart.fldPart_Name as Part_Name,tblM_Serial.fldSerial_No as Serial_No,tblM_Serial.fldMould_No as Mould_No
          from tblPart left outer join tblM_Serial
          on substring(tblPart.fldPart_No,5,4)=tblM_Serial.fldSerial_No
          where tblPart.fldPart_No like @Part_No
          order by tblPart.fldPart_No
        end
    else if @Id=2
        begin
          --delete from Part_Serial
         
          --insert into Part_Serial
       select fldPart_No,fldPart_Name from tblPart
          where substring(fldPart_No,5,4) not in (select fldSerial_No from tblM_Serial)
          order by fldPart_No
         
          --select Part_No,Part_Name from Part_Serial order by Part_No
     end
 
  return
 
 
 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Print_PartWeight

AS
 
   declare @temp_abc table(Part_No nvarchar(20),Qty Decimal(12),Part_NW decimal(9,2),Total_Qty decimal(12,2))
   declare @temp table(Part_No nvarchar(20),Qty Decimal(12),Part_NW decimal(9,2),Total_Qty decimal(12,2))
   declare @Part table(Part_No nvarchar(20))
   declare @Part_No nvarchar(20)
  
   begin
   delete from Count_PartWeight
  
   insert into @temp_abc
   select PartWeight.part_No,PartWeight.Qty,tblM_Serial.fldPart_NW,PartWeight.Qty*tblM_Serial.fldPart_NW as total_Qty
   from PartWeight,tblM_serial
   where PartWeight.Part_No in (select fldPart_No from tblPart) and substring(PartWeight.Part_No,5,4)=tblM_Serial.fldSerial_No
   order by PartWeight.Part_No
  
   insert into @part
   select distinct Part_No from @temp_abc order by Part_No
  
   declare Cur_Value cursor for select * from @Part order by Part_No
   open Cur_Value
   fetch next from Cur_Value into @Part_No
   while @@fetch_status=0
         begin
         insert into @temp
         select top 1 * from @temp_abc where part_no=@part_No order by total_qty desc
         fetch next from Cur_Value into @Part_No
         end
        
         insert into Count_PartWeight
         --select Part_No,Qty,Part_NW,round(Total_Qty/1000,2) from @temp order by Part_No
         select * from @temp order by Part_No
        
         delete from @part
         delete from @temp
         delete from @temp_abc
        
         select * from Count_PartWeight order by Part_No --输出计算结果
        
   end
  
  
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Print_tabBzgzd
@Option nvarchar(1),
@Start_No nvarchar(20),
@End_No nvarchar(20)

AS

 if @Option='0'
    begin
    select * from tabBzgzd
    where F_Job_No in (select F_Job_No from tabBzgzd where F_Job_No>=@Start_No and F_Job_No<=@End_No)
    order by F_Job_No,Part_No
    end
 else if @Option='1'
    begin
    select * from tabBzgzd
    where F_Job_No in (select F_Job_No from tabBzgzd where Part_No>=@Start_No and Part_No<=@End_No)
    order by F_Job_No,Part_No
    end
 else if @Option='2'
    begin
    select * from tabBzgzd where F_Job_No in (select F_Job_No from tabBzgzd where F_Item like '%'+ltrim(rtrim(@Start_No))+'%')
    order by F_Job_No,Part_No
   
    end
   
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Read_Part
 @Part_No nvarchar(20),
 @Part_Name nvarchar(100),
 @StdCost numeric(18,4),
 @Id int

AS
    if @Id=1
        begin
          truncate table Part
        end
    else if @Id=2
        begin
       insert into Part values(@Part_No,@Part_Name,@StdCost)
    end
 
  return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_Read_PartWeight
 @Part_No nvarchar(20),
 @Qty  decimal(10),
 @Id int

AS
    if @Id=1
        begin
          truncate table PartWeight
        end
    else if @Id=2
        begin
       insert into PartWeight values(@Part_No,@Qty)
    end
 
  return
 
 
 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--计算每周胶件更新价格
CREATE PROCEDURE  Sp_Week_PartPrice
AS
      begin
      set nocount on
     
      declare @PartPrice table(PartNo nvarchar(20),M1 nvarchar(10),P1 decimal(9),M2 nvarchar(10),P2 decimal(9),Part_GW decimal(9,2),Part_NW decimal(9,2),MachineType nvarchar(2),Cycle_Time decimal(12,0),Noc int)

     insert into  @PartPrice
     select tblPart.fldPart_No,tblPart.fldM_1,tblPart.fldP_1,tblPart.fldM_2,tblPart.fldP_2,tblM_Serial.fldPart_GW,tblM_Serial.fldPart_NW,tblMould.fldMachineType,tblMould.fldCycle_Time,tblMould.fldNoc
     from Part,tblPart,tblM_Serial,tblMould
     where Part.Part_No=tblPart.fldPart_No and (tblPart.fldM_1<>'') and Part.StdCost=0
          and substring(Part.Part_No,5,4)=tblM_Serial.fldSerial_No and tblM_Serial.fldMould_No=tblMould.fldMould_No
     order by tblPart.fldPart_No
    end
   
     declare @PartNo nvarchar(20),@M1 nvarchar(10),@M2 nvarchar(10),@MachineType nvarchar(2)
    declare @P1 decimal(9),@P2 decimal(9),@Part_GW decimal(9,2)
    declare @M1_Price decimal(12,5),@M2_Price decimal(12,5),@M1Price decimal(12,5),@M2Price decimal(12,5),@Cycle_Time decimal(12,0),@Noc int
    declare @ColorCost decimal(12,1),@MachineCost decimal(12,0),@MachinePrice decimal(12,5),@TotalPrice decimal(12,5)
    declare @Price table(PartNo nvarchar(20),Cost decimal(12,4))
   
    declare Cur_Value cursor for
    select PartNo,M1,M2,P1,P2,Part_GW,MachineType,Cycle_Time,Noc from @PartPrice where Part_GW>=Part_NW order by PartNo
   
    open Cur_Value
    fetch next from Cur_Value into @PartNo,@M1,@M2,@P1,@P2,@Part_GW,@MachineType,@Cycle_Time,@Noc
    while @@fetch_status=0
    begin
        --计算机种类型
        if @MachineType='B'
           set @MachineCost=3000
        else if @MachineType='M'
           set @MachineCost=2000
        else if @MachineType='S'
           set @MachineCost=1000
        if right(@PartNo,2)='TA' or right(@PartNo,2)='NA' --以TA/NA结尾的胶件不需加色粉
           set @ColorCost=0
        else
           set @ColorCost=0.8 --色粉单价：以￥20每份配一包25kg原料计
        set @MachinePrice=@MachineCost/((86400/@Cycle_Time)*@Noc) --啤工成本(机器成本)
          
        if @M1<>''
           begin
               if @P1=100
                   if right(@M1,1)='R'
                      begin
                        select @M1Price=fldPrice from tblPlastic where fldP_No=@M1 order by fldP_No
                        if (@M1Price=0 or @M1Price is null)
                           select @M1Price=fldPrice from tblPlastic where fldP_No=substring(@M1,1,len(@M1)-1) order by fldP_No
                       
                        set @M1Price=@M1Price+@ColorCost
                        set @M1_Price=@M1Price*@Part_GW*@P1/100/1000
      
                        set @TotalPrice=@MachinePrice+@M1_Price
                        insert into @Price
                        select @PartNo,@TotalPrice
                      end
                   else
                      begin
                        select @M1Price=fldPrice from tblPlastic where fldP_No=@M1 order by fldP_No
                        set @M1Price=@M1Price+@ColorCost
                        set @M1_Price=@M1Price*@Part_GW*@P1/100/1000
                    
                        set @TotalPrice=@MachinePrice+@M1_Price
                        insert into @Price
                        select @PartNo,@TotalPrice
                      end
               else if @P1<>100
                    begin
                        if @M1+'R'<> @M2
                            begin
                                select @M1Price=fldPrice from tblPlastic where fldP_No=@M1 order by fldP_No
                                select @M2Price=fldPrice from tblPlastic where fldP_No=@M2 order by fldP_No
                                set @M1Price=@M1Price+@ColorCost
                                set @M2Price=@M2Price+@ColorCost
                                set @M1_Price=@M1Price*@Part_GW*@P1/100/1000
          set @M2_Price=@M2Price*@Part_GW*@P2/100/1000
                                set @TotalPrice=@M1_Price+@M2_Price+@MachinePrice
    
                                 insert into @Price
                                select @PartNo,@TotalPrice
                            end
                        else if @M1+'R'=@M2
                            begin
                                select @M1Price=fldPrice from tblPlastic where fldP_No=@M1 order by fldP_No
                                select @M2Price=fldPrice from tblPlastic where fldP_No=@M2 order by fldP_No
                                set @M1Price=@M1Price+@ColorCost
                                set @M2Price=@M2Price+@ColorCost
                                   if (@M2Price=0 or @M2Price is Null)
                                   begin
                                       set @M2Price=@M1Price
                                   end
      
                                set @M1_Price=@M1Price*@Part_GW*@P1/100/1000
                                set @M2_Price=@M2Price*@Part_GW*@P2/100/1000
                                set @TotalPrice=@M1_Price+@M2_Price+@MachinePrice
                                insert into @Price
                                select @PartNo,@TotalPrice
                            end
                        else if right(@M1,1)='R'
                             begin
                               select @M1Price=fldPrice from tblPlastic where fldP_No=@M1 order by fldP_No
                               if (@M1Price=0 or @M1Price is null)
                                   select @M1Price=fldPrice from tblPlastic where fldP_No=substring(@M1,1,len(@M1)-1) order by fldP_No
                               select @M2Price=fldPrice from tblPlastic where fldP_No=@M2 order by fldP_No
                               if (@M2Price=0 or @M2Price is null)
                                   select @M2Price=fldPrice from tblPlastic where fldP_No=substring(@M2,1,len(@M2)-1) order by fldP_No

                                set @M1Price=@M1Price+@ColorCost
                                set @M2Price=@M2Price+@ColorCost
                                set @M1_Price=@M1Price*@Part_GW*@P1/100/1000
                                set @M2_Price=@M2Price*@Part_GW*@P2/100/1000
                                set @TotalPrice=@M1_Price+@M2_Price+@MachinePrice
                               
                                insert into @Price
                                select @PartNo,@TotalPrice
                             end
                        
                    end
           end
           fetch next from Cur_Value into @PartNo,@M1,@M2,@P1,@P2,@Part_GW,@MachineType,@Cycle_Time,@Noc
    end
   
    Close Cur_Value
    Deallocate Cur_Value
   
    select distinct * from @Price order by PartNo
  
    delete from @PartPrice
    delete from @Price
    
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Sp_tabBzgzd
  @F_InUser nvarchar(10),
  @F_InDate smalldatetime  
 
AS
 
    declare @JobNo as nvarchar(15),@Part nvarchar(10)
    declare @Mould_No1 nvarchar(10)
    declare @Mould table(Mould_No nvarchar(10))
    declare @temp table(partno nvarchar(20))
    declare @Temp_Bzxqb table(Mould_No nvarchar(10),Part_No nvarchar(20),F_Item nvarchar(40),F_MCYS nvarchar(50),F_Bzxqsl numeric(12),
                              F_Dqccsl numeric(12),F_Bzrq smalldatetime,F_MJMZ numeric(12,2), F_ZMZ numeric(12,1),
                              F_MJJZ numeric(12,2),F_P_No1 nvarchar(15),F_M1 numeric(12),
                              F_YJYL1 numeric(12,1),F_P_No2 nvarchar(15),F_M2 numeric(12),F_YJYL2 numeric(12,1),
                              F_CNO nvarchar(15),F_CM numeric(12,2),F_YJSFYL numeric(12,1),F_MDNM nvarchar(20),
                              F_Nop numeric(9),F_MBZQ numeric(12,2),F_YJZSS numeric(12,2))
    declare @Job_No nvarchar(15),@Mould_No nvarchar(10),@Part_No nvarchar(20),@F_Item nvarchar(40),@F_MCYS nvarchar(50),
            @F_Bzxqsl numeric(12),@F_ZS numeric(12),@F_Dqccsl numeric(12),@F_Bzrq smalldatetime,@F_Wcrq smalldatetime,
            @F_MJMZ numeric(12,2),@F_MJJZ numeric(12,2),@F_ZMZ numeric(12,1),@F_ZJZ numeric(12,1),@F_P_No1 nvarchar(15),
            @F_M1 numeric(12),@F_YJYL1 numeric(12,1),@F_P_No2 nvarchar(15),@F_M2 numeric(12),@F_YJYL2 numeric(12,1),
            @F_CNO nvarchar(15),@F_CM numeric(12,2),@F_YJSFYL numeric(12,1),@F_MDNM nvarchar(20),@F_Nop numeric(9),
            @F_MBZQ numeric(12,2),@F_YJZSS numeric(12,2),@F_BZJT nvarchar(30),@F_24XSCL numeric(12),@F_24XSYL numeric(12)
       
         
    --先清空资料
    truncate table tabBzgzd
    --把所有相同公模编号放入一个临时表中
    insert into @Mould
    select distinct Mould_No
    from tabBzxqb where Mould_No<>'' order by Mould_No
   
    --/1
    declare Cur_Mould cursor for select Mould_No from @Mould
    open Cur_Mould
    fetch next from Cur_Mould into @Mould_No1
    while @@fetch_status=0
          begin
           --对同一公模号的胶件进行分类
          insert into @Temp
          select Part_No from tabBzxqb
          where Mould_No=@Mould_No1 and F_REQS>0 order by Part_No
        
         --/2
          declare Cur_Part cursor for SELECT distinct right(partno,2) from @Temp
          open Cur_Part
          fetch next from Cur_Part into @Part
          while @@fetch_status=0
          --/3
          begin
          insert into @Temp_Bzxqb
          select Mould_No,Part_No,F_Item,F_MCYS,F_Bzxqsl,F_Dqccsl,F_Bzrq,F_MJMZ,F_ZMZ,F_MJJZ,F_P_No1,F_M1,F_YJYL1,
                 F_P_No2,F_M2,F_YJYL2,F_CNo,F_CM,F_YJSFYL,F_MDNM,F_NOP,F_MBZQ,F_YJZSS
          from tabBzxqb
          where Mould_No=@Mould_No1 and Part_No like '%'+@Part and F_REQS>0 order by Part_No
         
          --得到工作单号
          select @JobNo=dbo.Sp_Get_Id()
         
          --运算并产生工作单
          --/4
          declare Cur_Gzd cursor for select * from @Temp_Bzxqb
          open Cur_Gzd
          fetch next from Cur_Gzd into  @Mould_No,@Part_No,@F_Item,@F_MCYS,@F_Bzxqsl,@F_Dqccsl,@F_Bzrq,@F_MJMZ,@F_ZMZ,@F_MJJZ,
                                        @F_P_No1,@F_M1,@F_YJYL1,@F_P_No2,@F_M2,@F_YJYL2,@F_CNo,@F_CM,@F_YJSFYL,@F_MDNM,
                                        @F_NOP,@F_MBZQ,@F_YJZSS
          while @@fetch_status=0
                begin
                 set @F_ZS=round(@F_Bzxqsl/@F_Nop,0)
                 set @F_ZJZ=round(@F_Bzxqsl*@F_MJJZ/1000,1)
                 set @F_BZJT=''
                 if @F_MBZQ=0
                    set @F_24XSCL=0
                 else
                    set @F_24XSCL=round((3600/@F_MBZQ)*@F_NOP*24,0)
   set @F_24XSYL=round(@F_24XSCL*@F_MJMZ/1000,0)
                    set @F_Wcrq=DateAdd(day,round(@F_YJZSS/24,0),@F_Bzrq)
                 insert into tabBzgzd values(@JobNo,@Mould_No,@Part_No,@F_Item,@F_MCYS,@F_Bzxqsl,@F_ZS,@F_Dqccsl,@F_Bzrq,@F_Wcrq,
                              @F_MJMZ,@F_MJJZ,@F_ZMZ,@F_ZJZ,@F_P_No1,@F_M1,@F_YJYL1,@F_P_No2,@F_M2,@F_YJYL2,@F_CNO,@F_CM,@F_YJSFYL,
                              0,@F_MDNM,@F_Nop,@F_MBZQ,@F_YJZSS,@F_BZJT,@F_24XSCL,@F_24XSYL,@F_InUser,@F_InDate)

                 fetch next from Cur_Gzd into  @Mould_No,@Part_No,@F_Item,@F_MCYS,@F_Bzxqsl,@F_Dqccsl,@F_Bzrq,@F_MJMZ,@F_ZMZ,@F_MJJZ,
   @F_P_No1,@F_M1,@F_YJYL1,@F_P_No2,@F_M2,@F_YJYL2,@F_CNo,@F_CM,@F_YJSFYL,@F_MDNM,
                                                @F_Nop,@F_MBZQ,@F_YJZSS
               end
           close Cur_Gzd
           deallocate Cur_Gzd
           --/4
          
             --完成一个循环,清空上次的插入的资料
            delete from @Temp_Bzxqb
            --/3
           fetch next from Cur_Part into @Part
           end
           Close Cur_Part
           deallocate Cur_Part
            --/2
          fetch next from Cur_Mould into @Mould_No1
          end
    close Cur_Mould
    deallocate Cur_Mould
    --/1
   
    delete from @temp
    delete from @Temp_Bzxqb
    delete from @Mould
   
    RETURN
    

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE pageTest  --用于翻页的测试
--需要把排序字段放在第一列

 (
  @FirstID nvarchar(20)=null,  --当前页面里的第一条记录的排序字段的值
  @LastID nvarchar(20)=null,   --当前页面里的最后一条记录的排序字段的值
  @isNext bit=null,            --true 1 :下一页；false 0:上一页
  @allCount int output,        --返回总记录数
  @pageSize int output,        --返回一页的记录数
  @CurPage int                 --页号（第几页）0：第一页；-1最后一页。
  )

AS

if @CurPage=0
 begin
  --统计总记录数
  select @allCount=count(FLDMould_No) from tblMould
 
  set @pageSize=10
  --返回第一页的数据
  select top 10 fldMould_No,fldMould_Name  
        from tblMould
          order by fldMould_No
 end

else if @CurPage=-1
  BEGIN
    select * from
      (select top 10 fldMould_No,fldMould_Name
          from tblMould
              order by fldMould_No desc ) as aa 
                        order by fldMould_No
  END
else

 begin
  if @isNext=1
   --翻到下一页
   select top 10 fldMould_No,fldMould_Name
        from tblMould
          where fldMould_No > @LastID
            order by fldMould_No
  else
   --翻到上一页
   select * from
    (select top 10 fldMould_No,fldMould_Name
       from tblMould
          where fldMould_No < @FirstID 
              order by fldMould_No desc) as bb
                  order by fldMould_No
 end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO