ÿþUSE [WinccSQL] GO /****** Object: User [SIMATIC HMI User] Script Date: 2023/12/11 8:44:59 ******/ CREATE USER [SIMATIC HMI User] FOR LOGIN [DESKTOP-OAB5EAB\SIMATIC HMI] GO /****** Object: User [SIMATIC HMI VIEWER User] Script Date: 2023/12/11 8:44:59 ******/ CREATE USER [SIMATIC HMI VIEWER User] FOR LOGIN [DESKTOP-OAB5EAB\SIMATIC HMI VIEWER] GO /****** Object: DatabaseRole [SIMATIC HMI role] Script Date: 2023/12/11 8:44:59 ******/ CREATE ROLE [SIMATIC HMI role] GO /****** Object: DatabaseRole [SIMATIC HMI VIEWER role] Script Date: 2023/12/11 8:44:59 ******/ CREATE ROLE [SIMATIC HMI VIEWER role] GO ALTER ROLE [SIMATIC HMI role] ADD MEMBER [SIMATIC HMI User] GO ALTER ROLE [db_ddladmin] ADD MEMBER [SIMATIC HMI User] GO ALTER ROLE [db_datareader] ADD MEMBER [SIMATIC HMI User] GO ALTER ROLE [db_datawriter] ADD MEMBER [SIMATIC HMI User] GO ALTER ROLE [SIMATIC HMI VIEWER role] ADD MEMBER [SIMATIC HMI VIEWER User] GO ALTER ROLE [db_datareader] ADD MEMBER [SIMATIC HMI VIEWER User] GO ALTER ROLE [db_ddladmin] ADD MEMBER [SIMATIC HMI role] GO ALTER ROLE [db_datareader] ADD MEMBER [SIMATIC HMI role] GO ALTER ROLE [db_datawriter] ADD MEMBER [SIMATIC HMI role] GO ALTER ROLE [db_datareader] ADD MEMBER [SIMATIC HMI VIEWER role] GO /****** Object: Table [dbo].[GlassIDTemp] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[GlassIDTemp]( [GlassIDTemp] [nvarchar](max) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[glassIn] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[glassIn]( [¢[7bãN÷S] [nvarchar](max) NULL, [¤N'åeg] [datetime] NULL, [¢‹US÷S] [nvarchar](max) NULL, [ň±{÷Sÿ¶g÷S ÿ] [nvarchar](max) NULL, [±{…Q»sƒt;`pe] [float] NULL, [WY÷S] [nvarchar](max) NULL, [WY…Q»sƒt;`pe] [float] NULL, [»sƒtID] [nvarchar](max) NULL, [»sƒt•¦^ÿmm ÿ] [float] NULL, [»sƒt½[¦^ÿmm ÿ] [float] NULL, [»sƒtšS¦^ÿmm ÿ] [float] NULL, [WY…Q»sƒt^÷S] [float] NULL, [±{…Q»sƒt^÷S] [float] NULL, [¢‹US»sƒt;`pe] [float] NULL, [item_quantity] [int] NULL, [RackTypeID] [nvarchar](max) NULL, [P[±{÷S] [nvarchar](max) NULL, [RackID] [float] NULL, [±{pe] [int] NULL, [»sƒt/UNID] [nvarchar](max) NULL, [ŸSň±{÷S] [nvarchar](max) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[glassOut] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[glassOut]( [^÷S] [float] NULL, [¢[7bãN÷S] [nvarchar](max) NULL, [¤N'åeg] [datetime] NULL, [¢‹US÷S] [nvarchar](max) NULL, [ň±{÷Sÿ¶g÷S ÿ] [nvarchar](max) NULL, [±{…Q»sƒt;`pe] [float] NULL, [WY÷S] [nvarchar](max) NULL, [WY…Q»sƒt;`pe] [float] NULL, [»sƒtID] [nvarchar](max) NULL, [»sƒt•¦^ÿmm ÿ] [float] NULL, [»sƒt½[¦^ÿmm ÿ] [float] NULL, [»sƒtšS¦^ÿmm ÿ] [float] NULL, [RackTypeID] [float] NULL, [rackID] [float] NULL, [™e¶g N„všS¦^] [int] NULL, [™e¶g N„v^÷S] [int] NULL, [±{…Q»sƒt^÷S] [int] NULL, [ö€«WšS¦^] [float] NULL, [P[±{÷S] [nvarchar](max) NULL, [±{pe] [int] NULL, [»sƒt/UNID] [nvarchar](max) NULL, [ŸSň±{÷S] [nvarchar](max) NULL, [ŸS±{…Q^÷S] [int] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[glassOut2] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[glassOut2]( [^÷S] [float] NULL, [¢[7bãN÷S] [nvarchar](max) NULL, [¤N'åeg] [datetime] NULL, [¢‹US÷S] [nvarchar](max) NULL, [ň±{÷Sÿ¶g÷S ÿ] [nvarchar](max) NULL, [±{…Q»sƒt;`pe] [float] NULL, [WY÷S] [nvarchar](max) NULL, [WY…Q»sƒt;`pe] [float] NULL, [»sƒtID] [nvarchar](max) NULL, [»sƒt•¦^ÿmm ÿ] [float] NULL, [»sƒt½[¦^ÿmm ÿ] [float] NULL, [»sƒtšS¦^ÿmm ÿ] [float] NULL, [RackTypeID] [float] NULL, [rackID] [float] NULL, [™e¶g N„všS¦^] [int] NULL, [™e¶g N„v^÷S] [int] NULL, [±{…Q»sƒt^÷S] [int] NULL, [ö€«WšS¦^] [float] NULL, [P[±{÷S] [nvarchar](max) NULL, [±{pe] [int] NULL, [»sƒt/UNID] [nvarchar](max) NULL, [ŸSň±{÷S] [nvarchar](max) NULL, [ŸS±{…Q^÷S] [int] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[glassOut3] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[glassOut3]( [^÷S] [float] NULL, [¢[7bãN÷S] [nvarchar](max) NULL, [¤N'åeg] [datetime] NULL, [¢‹US÷S] [nvarchar](max) NULL, [ň±{÷Sÿ¶g÷S ÿ] [nvarchar](max) NULL, [±{…Q»sƒt;`pe] [float] NULL, [WY÷S] [nvarchar](max) NULL, [WY…Q»sƒt;`pe] [float] NULL, [»sƒtID] [nvarchar](max) NULL, [»sƒt•¦^ÿmm ÿ] [float] NULL, [»sƒt½[¦^ÿmm ÿ] [float] NULL, [»sƒtšS¦^ÿmm ÿ] [float] NULL, [RackTypeID] [float] NULL, [rackID] [float] NULL, [™e¶g N„všS¦^] [int] NULL, [™e¶g N„v^÷S] [int] NULL, [±{…Q»sƒt^÷S] [int] NULL, [ö€«WšS¦^] [float] NULL, [P[±{÷S] [nvarchar](max) NULL, [±{pe] [int] NULL, [»sƒt/UNID] [nvarchar](max) NULL, [ŸSň±{÷S] [nvarchar](max) NULL, [ŸS±{…Q^÷S] [int] NULL, [úQGröeô•] [datetime] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[modeSwitch] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[modeSwitch]( [mode] [bit] NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[newtesttable] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[newtesttable]( [¢[7bãN÷S] [nvarchar](max) NULL, [¤N'åeg] [datetime] NULL, [¢‹US÷S] [nvarchar](max) NULL, [ň±{÷Sÿ¶g÷S ÿ] [nvarchar](max) NULL, [±{…Q»sƒt;`pe] [float] NULL, [WY÷S] [nvarchar](max) NULL, [WY…Q»sƒt;`pe] [float] NULL, [»sƒtID] [nvarchar](max) NULL, [»sƒt•¦^ÿmm ÿ] [float] NULL, [»sƒt½[¦^ÿmm ÿ] [float] NULL, [»sƒtšS¦^ÿmm ÿ] [float] NULL, [WY…Q»sƒt^÷S] [float] NULL, [±{…Q»sƒt^÷S] [float] NULL, [item_quantity] [int] NULL, [RackTypeID] [float] NULL, [RackID] [float] NULL, [ۏGrz˜^] [float] NULL, [ۏGr] [float] NULL, [úQGr] [float] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[Order$] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Order$]( [¢[7bãN÷S] [nvarchar](max) NULL, [¤N'åeg] [datetime] NULL, [¢‹US÷S] [nvarchar](max) NULL, [ň±{÷Sÿ¶g÷S ÿ] [nvarchar](max) NULL, [±{…Q»sƒt;`pe] [float] NULL, [WY÷S] [nvarchar](max) NULL, [WY…Q»sƒt;`pe] [float] NULL, [»sƒtID] [nvarchar](max) NULL, [»sƒt•¦^ÿmm ÿ] [float] NULL, [»sƒt½[¦^ÿmm ÿ] [float] NULL, [»sƒtšS¦^ÿmm ÿ] [float] NULL, [WY…Q»sƒt^÷S] [float] NULL, [±{…Q»sƒt^÷S] [float] NULL, [¢‹US»sƒt;`pe] [float] NULL, [item_quantity] [int] NULL, [RackTypeID] [nvarchar](max) NULL, [P[±{÷S] [nvarchar](max) NULL, [RackID] [float] NULL, [±{pe] [int] NULL, [»sƒt/UNID] [nvarchar](max) NULL, [ŸSň±{÷S] [nvarchar](max) NULL, [last_update_time] [datetime] NULL, [number] [int] NULL, [úQP[±{÷S] [nvarchar](max) NULL, [ŸS±{…Q^÷S] [float] NULL, [¢‹US÷S÷S] [nvarchar](max) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[QuestionsTB] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[QuestionsTB]( [customer_order_number] [nvarchar](max) NULL, [last_update_date] [datetime] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[Rack] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Rack]( [^÷S] [int] IDENTITY(1,1) NOT NULL, [RACK] [float] NULL, [½[¦^] [float] NULL, [ň±{÷S] [nvarchar](max) NULL, [»sƒt;`šS¦^] [float] NULL, [{|‹W] [nvarchar](max) NULL, [¢‹US÷S] [nvarchar](max) NULL, [WY÷S] [nvarchar](max) NULL, [¢[7bãN÷S] [nvarchar](max) NULL, CONSTRAINT [PK_Rack] PRIMARY KEY CLUSTERED ( [^÷S] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[Report] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Report]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [markDate] [date] NULL, [GlassID] [nvarchar](max) NULL, [TheOnlyGlassID] [nvarchar](max) NULL, [OrderNum] [nvarchar](max) NULL, [CustomNum] [nvarchar](max) NULL, CONSTRAINT [PK_Report] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[storage] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[storage]( [^÷S] [bigint] IDENTITY(1,1) NOT NULL, [¢[7bãN÷S] [nvarchar](max) NULL, [¤N'åeg] [datetime] NULL, [¢‹US÷S] [nvarchar](max) NULL, [ň±{÷Sÿ¶g÷S ÿ] [nvarchar](max) NULL, [±{…Q»sƒt;`pe] [float] NULL, [WY÷S] [nvarchar](max) NULL, [WY…Q»sƒt;`pe] [float] NULL, [»sƒtID] [nvarchar](max) NULL, [»sƒt•¦^ÿmm ÿ] [float] NULL, [»sƒt½[¦^ÿmm ÿ] [float] NULL, [»sƒtšS¦^ÿmm ÿ] [float] NULL, [RackTypeID] [float] NULL, [rackID] [float] NULL, [™e¶g N„všS¦^] [int] NULL, [™e¶g N„v^÷S] [int] NULL, [±{…Q»sƒt^÷S] [int] NULL, [ö€«WšS¦^] [float] NULL, [item_quantity] [int] NULL, [P[±{÷S] [nvarchar](max) NULL, [±{pe] [int] NULL, [»sƒt/UNID] [nvarchar](max) NULL, [ŸSň±{÷S] [nvarchar](max) NULL, [úQP[±{÷S] [nvarchar](max) NULL, [ŸS±{…Q^÷S] [float] NULL, CONSTRAINT [PK_storage] PRIMARY KEY CLUSTERED ( [^÷S] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[t_report_processing_order] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[t_report_processing_order]( [id] [nvarchar](255) NULL, [customer_number] [nvarchar](255) NULL, [shipping_date] [datetime] NULL, [customer_order_number] [nvarchar](255) NULL, [customer_order_number_number] [nvarchar](255) NULL, [item_memo_one] [nvarchar](255) NULL, [processing_order_number] [nvarchar](255) NULL, [processing_order_item_number] [nvarchar](255) NULL, [item_height] [nvarchar](255) NULL, [item_width] [nvarchar](255) NULL, [thickness] [nvarchar](255) NULL, [item_memo_one_last_no] [nvarchar](255) NULL, [last_update_time] [datetime] NULL, [used] [nvarchar](255) NULL, [used_time] [nvarchar](255) NULL, [remark] [nvarchar](255) NULL, [customer_product_desc] [nvarchar](255) NULL, [serial_number] [nvarchar](255) NULL, [item_quantity] [nvarchar](255) NULL, [total_quantity] [nvarchar](255) NULL, [customer_product_desc_number] [nvarchar](255) NULL, [serial_number_number] [nvarchar](255) NULL, [work_shop] [nvarchar](255) NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[Temp] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Temp]( [¢[7bãN÷S] [nvarchar](255) NULL, [¤N'åeg] [datetime] NULL, [¢‹US÷S] [nvarchar](255) NULL, [ň±{÷S_¶g÷S] [nvarchar](255) NULL, [±{…Q»sƒt;`pe] [float] NULL, [WY÷S] [nvarchar](255) NULL, [WY…Q»sƒt;`pe] [float] NULL, [»sƒtID] [nvarchar](255) NULL, [»sƒt•¦^_mm] [float] NULL, [»sƒt½[¦^_mm] [float] NULL, [»sƒtšS¦^_mm] [float] NULL, [WY…Q»sƒt^÷S] [float] NULL, [±{…Q»sƒt^÷S] [float] NULL, [¢‹US»sƒt;`pe] [float] NULL, [item_quantity] [float] NULL, [RackTypeID] [nvarchar](255) NULL, [P[±{÷S] [nvarchar](255) NULL, [RackID] [float] NULL, [±{pe] [int] NULL, [»sƒt/UNID] [nvarchar](255) NULL, [ŸSň±{÷S] [nvarchar](255) NULL, [last_update_time] [datetime] NULL, [number] [int] NULL, [úQP[±{÷S] [nvarchar](max) NULL, [ŸS±{…Q^÷S] [float] NULL, [¢‹US÷S÷S] [nvarchar](max) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[weiyiID] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[weiyiID]( [»sƒt/UNID] [nvarchar](max) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[Temp] ADD CONSTRAINT [DF_Temp_number] DEFAULT ((0)) FOR [number] GO /****** Object: StoredProcedure [dbo].[insert_chai] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --4Nöehˆ ÒceQpenc CREATE proc [dbo].[insert_chai] @days int as begin declare @¢[7bãN÷S nvarchar(50) declare @¤N'åeg nvarchar(50) declare @¢‹US÷S nvarchar(50) declare @ň±{÷S_¶g÷S nvarchar(50) --declare @ŸSň±{÷S nvarchar(50) declare @»sƒtID nvarchar(50) declare @»sƒt•¦^_mm nvarchar(50) declare @»sƒt½[¦^_mm nvarchar(50) declare @»sƒtšS¦^_mm nvarchar(50) declare @±{…Q»sƒt^÷S nvarchar(50) declare @¢‹US»sƒt;`pe nvarchar(50) declare @item_quantity nvarchar(50) declare @ûm Råeg nvarchar(50) declare cursor1 cursor for select customer_number,shipping_date,customer_order_number,customer_product_desc_number,processing_order_number,item_height,item_width,thickness,serial_number_number,total_quantity,item_quantity,last_update_time from [192.168.3.16].shebei.dbo.t_report_processing_order where work_shop=2000 and last_update_time>getdate()-@days --and processing_order_number not in(select »sƒtID from Order$ group by »sƒtID) --ÿb0R@b g¡l gûm R„v»sƒtID open cursor1 fetch next from cursor1 into @¢[7bãN÷S,@¤N'åeg,@¢‹US÷S,@ň±{÷S_¶g÷S,@»sƒtID,@»sƒt•¦^_mm,@»sƒt½[¦^_mm,@»sƒtšS¦^_mm,@±{…Q»sƒt^÷S,@¢‹US»sƒt;`pe,@item_quantity,@ûm Råeg while @@fetch_status=0 begin declare @prices int declare @ò]0Rpe int set @ò]0Rpe=0 set @prices=@item_quantity-@ò]0Rpe --;`pe-ò]Ï~ûm R„vpeϑ while @prices>0 begin declare @»sƒt/UNID nvarchar(50) set @ò]0Rpe=@ò]0Rpe+1 set @»sƒt/UNID=@»sƒtID+'-'+CONVERT(nvarchar,@ò]0Rpe) --set @ŸSň±{÷S=@ň±{÷S_¶g÷S insert into Temp(¢[7bãN÷S,¤N'åeg,¢‹US÷S,ň±{÷S_¶g÷S,»sƒtID,»sƒt•¦^_mm,»sƒt½[¦^_mm,»sƒtšS¦^_mm,±{…Q»sƒt^÷S,¢‹US»sƒt;`pe,item_quantity,±{pe,»sƒt/UNID,ŸSň±{÷S,last_update_time,ŸS±{…Q^÷S,¢‹US÷S÷S) --ûm RW[µk ±{pe ؞¤‹0 -- g^÷S„v begin exec Pro_ChildBox_Rule1 @ň±{÷S_¶g÷S end else begin if((select top(1)mode from modeSwitch)='false') or (select PATINDEX('%JYC-%',@ň±{÷S_¶g÷S))>0 --!j_1 b€/fÖNìN„vň±{÷S begin update Temp set P[±{÷S=@ň±{÷S_¶g÷S where ň±{÷S_¶g÷S=@ň±{÷S_¶g÷S end else begin exec Pro_ChildBox_Rule2 @ň±{÷S_¶g÷S end end end GO /****** Object: StoredProcedure [dbo].[Pro_ChildBox_Rule1] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --P[±{ĉR1 50N¶g CREATE proc [dbo].[Pro_ChildBox_Rule1] @ň±{÷S_¶g÷S nvarchar(100) as begin declare @Box_childID nvarchar(50) --°eP[±{÷S declare @Count int --hˆ:y,{àQ*N±{ set @Count=1 while(select count(*) from Temp where ň±{÷S_¶g÷S=@ň±{÷S_¶g÷S and P[±{÷S is null)>0 begin set @Box_childID=@ň±{÷S_¶g÷S+'-'+CONVERT(nvarchar,@Count) update Temp set P[±{÷S=@Box_childID where ň±{÷S_¶g÷S=@ň±{÷S_¶g÷S and »sƒt/UNID in (select top(50)»sƒt/UNID from Temp where ň±{÷S_¶g÷S=@ň±{÷S_¶g÷S and P[±{÷S is null order by ±{…Q»sƒt^÷S,»sƒt½[¦^_mm desc,»sƒtID) set @Count=@Count+1 end end GO /****** Object: StoredProcedure [dbo].[Pro_ChildBox_Rule2] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE proc [dbo].[Pro_ChildBox_Rule2] @ň±{÷S_¶g÷S nvarchar(100) as begin declare @Box_childID nvarchar(50) --°eP[±{÷S declare @Count int --hˆ:y,{àQ*N±{ declare @g'Y±{…Qpeϑ int --g'Y±{…Qpeϑ declare @±{…Qg'YšS¦^ int --±{…Qg'YšS¦^ set @±{…Qg'YšS¦^=(select MAX(CONVERT(int,»sƒtšS¦^_mm)) from Temp where ň±{÷S_¶g÷S=@ň±{÷S_¶g÷S) if(@±{…Qg'YšS¦^=10) begin set @g'Y±{…Qpeϑ=45 end else if(@±{…Qg'YšS¦^=6) begin set @g'Y±{…Qpeϑ=60 end else if(@±{…Qg'YšS¦^=5) begin set @g'Y±{…Qpeϑ=70 end else begin set @g'Y±{…Qpeϑ=50 end set @Count=1 if(@g'Y±{…Qpeϑ<=50) begin while(select count(*) from Temp where ň±{÷S_¶g÷S=@ň±{÷S_¶g÷S and P[±{÷S is null)>0 begin set @Box_childID=@ň±{÷S_¶g÷S+'-'+CONVERT(nvarchar,@Count) update Temp set P[±{÷S=@Box_childID,úQP[±{÷S=@Box_childID where ň±{÷S_¶g÷S=@ň±{÷S_¶g÷S and »sƒt/UNID in (select top(@g'Y±{…Qpeϑ)»sƒt/UNID from Temp where ň±{÷S_¶g÷S=@ň±{÷S_¶g÷S and P[±{÷S is null order by ±{…Q»sƒt^÷S,»sƒt½[¦^_mm desc,»sƒtID) set @Count=@Count+1 end end else begin while(select count(*) from Temp where ň±{÷S_¶g÷S=@ň±{÷S_¶g÷S and P[±{÷S is null)>0 begin set @Box_childID=@ň±{÷S_¶g÷S+'-'+CONVERT(nvarchar,@Count) insert into weiyiID(»sƒt/UNID) --ûm RW[µk ±{pe ؞¤‹getdate()-@day) if (@X[(W>0) begin --print 1 exec insert_chai @day declare @counnt int declare @Dateing datetime --ågâ‹g'Y„vnumber set @counnt=(select max(number) from Order$) if(@counnt is null) begin set @counnt=0 end set @counnt=@counnt+1 --¡‹—{ÿň±{÷S:N NG+peW[ x0) commit tran end GO /****** Object: StoredProcedure [dbo].[Pro_Delete] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- Rd–orders$hˆ 15)YKNMR„v CREATE proc [dbo].[Pro_Delete] as begin delete Order$ where last_update_time<getdate()-40 delete QuestionsTB where last_update_date<getdate()-20 end GO /****** Object: StoredProcedure [dbo].[Pro_InsertXuhao] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE proc [dbo].[Pro_InsertXuhao] @ň±{÷S_¶g÷S nvarchar(100) as begin declare @»sƒt/UNID nvarchar(255) --hˆ:y»sƒt/UNID declare @±{…Q»sƒt^÷S int --hˆ:y±{…Q»sƒt^÷S set @±{…Q»sƒt^÷S=1 declare cursor_Xuhao cursor for select »sƒt/UNID from Temp where ň±{÷S_¶g÷S=@ň±{÷S_¶g÷S order by ŸS±{…Q^÷S asc,CONVERT(int,»sƒt½[¦^_mm) desc,»sƒtID open cursor_Xuhao fetch next from cursor_Xuhao into @»sƒt/UNID while @@fetch_status=0 begin update Temp set ±{…Q»sƒt^÷S=@±{…Q»sƒt^÷S where ň±{÷S_¶g÷S=@ň±{÷S_¶g÷S and »sƒt/UNID=@»sƒt/UNID set @±{…Q»sƒt^÷S=@±{…Q»sƒt^÷S+1 fetch next from cursor_Xuhao into @»sƒt/UNID end close cursor_Xuhao deallocate cursor_Xuhao end GO /****** Object: StoredProcedure [dbo].[Pro_Sum] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --¡‹—{P[±{…Q;`pe create proc [dbo].[Pro_Sum] as begin --declare @ň±{÷S_¶g÷S nvarchar(100) declare @P[±{÷S nvarchar(100) declare cursor1 cursor for --select distinct P[±{÷S from ##Temp select distinct P[±{÷S from Temp open cursor1 fetch next from cursor1 into @P[±{÷S while @@fetch_status=0 begin --select sum(item_quantity) from Table_Name1 where ň±{÷S_¶g÷S='' update Temp set ±{…Q»sƒt;`pe=(select COUNT(*) from Temp where P[±{÷S=@P[±{÷S) where P[±{÷S=@P[±{÷S fetch next from cursor1 into @P[±{÷S end close cursor1 deallocate cursor1 end GO /****** Object: StoredProcedure [dbo].[Pro_SumOut] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE proc [dbo].[Pro_SumOut] as begin declare @P[±{÷S nvarchar(100) declare cursor6 cursor for select distinct P[±{÷S from glassOut2 open cursor6 fetch next from cursor6 into @P[±{÷S while @@fetch_status=0 begin if (select MAX(CONVERT(int,ŸS±{…Q^÷S)) from glassOut2 where P[±{÷S=@P[±{÷S)>0 -- g^÷S„v begin if(select top(1) count(*) from glassOut2 where P[±{÷S=@P[±{÷S group by ŸS±{…Q^÷S having count(*)>1 order by count(*) desc)>0 begin --RWY update glassOut2 set ±{…Q»sƒt^÷S=0 where P[±{÷S=@P[±{÷S exec proc_Xuhao @P[±{÷S end end else --àe^÷S„v begin exec proc_Xuhao @P[±{÷S end update glassOut2 set ±{…Q»sƒt;`pe=(select COUNT(*) from glassOut2 where P[±{÷S=@P[±{÷S) where P[±{÷S=@P[±{÷S --select sum(item_quantity) from Table_Name1 where ň±{÷S_¶g÷S='' --update glassOut set ±{…Q»sƒt;`pe=(select COUNT(*) from glassOut where P[±{÷S=@P[±{÷S) where P[±{÷S=@P[±{÷S fetch next from cursor6 into @P[±{÷S end close cursor6 deallocate cursor6 --YtŒ[b ûm RۏúQGrhˆ insert into glassOut(^÷S,¢[7bãN÷S,¤N'åeg,¢‹US÷S,[ň±{÷Sÿ¶g÷S ÿ],±{…Q»sƒt;`pe,WY÷S,WY…Q»sƒt;`pe,»sƒtID,[»sƒt•¦^ÿmm ÿ],[»sƒt½[¦^ÿmm ÿ],[»sƒtšS¦^ÿmm ÿ],RackTypeID,rackID,™e¶g N„všS¦^,™e¶g N„v^÷S,±{…Q»sƒt^÷S,ö€«WšS¦^,P[±{÷S,»sƒt/UNID,ŸSň±{÷S,ŸS±{…Q^÷S) select ^÷S,¢[7bãN÷S,¤N'åeg,¢‹US÷S,[ň±{÷Sÿ¶g÷S ÿ],±{…Q»sƒt;`pe,WY÷S,WY…Q»sƒt;`pe,»sƒtID,[»sƒt•¦^ÿmm ÿ],[»sƒt½[¦^ÿmm ÿ],[»sƒtšS¦^ÿmm ÿ],RackTypeID,rackID,™e¶g N„všS¦^,™e¶g N„v^÷S,±{…Q»sƒt^÷S,ö€«WšS¦^,P[±{÷S,»sƒt/UNID,ŸSň±{÷S,ŸS±{…Q^÷S from glassOut2 insert into glassOut3(^÷S,¢[7bãN÷S,¤N'åeg,¢‹US÷S,[ň±{÷Sÿ¶g÷S ÿ],±{…Q»sƒt;`pe,WY÷S,WY…Q»sƒt;`pe,»sƒtID,[»sƒt•¦^ÿmm ÿ],[»sƒt½[¦^ÿmm ÿ],[»sƒtšS¦^ÿmm ÿ],RackTypeID,rackID,™e¶g N„všS¦^,™e¶g N„v^÷S,±{…Q»sƒt^÷S,ö€«WšS¦^,P[±{÷S,»sƒt/UNID,ŸSň±{÷S,ŸS±{…Q^÷S,úQGröeô•) select ^÷S,¢[7bãN÷S,¤N'åeg,¢‹US÷S,[ň±{÷Sÿ¶g÷S ÿ],±{…Q»sƒt;`pe,WY÷S,WY…Q»sƒt;`pe,»sƒtID,[»sƒt•¦^ÿmm ÿ],[»sƒt½[¦^ÿmm ÿ],[»sƒtšS¦^ÿmm ÿ],RackTypeID,rackID,™e¶g N„všS¦^,™e¶g N„v^÷S,±{…Q»sƒt^÷S,ö€«WšS¦^,P[±{÷S,»sƒt/UNID,ŸSň±{÷S,ŸS±{…Q^÷S,getdate() from glassOut2 delete glassOut2 end GO /****** Object: StoredProcedure [dbo].[Pro_Type] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --¡‹—{™e¶g{|‹W CREATE proc [dbo].[Pro_Type] as begin --select * from ##Temp where item_width>@Str_width -- c½[¦^š[INÏköN»sƒt„v{|‹W\ŽN400:N1,400-600:N2,600-800:N3,800åN N:N5 update Temp set RackTypeID=1 where »sƒt½[¦^_mm<400.00 update Temp set RackTypeID=2 where »sƒt½[¦^_mm between 400 and 651 update Temp set RackTypeID=3 where »sƒt½[¦^_mm between 652 and 850 update Temp set RackTypeID=5 where »sƒt½[¦^_mm>850 end GO /****** Object: StoredProcedure [dbo].[Pro_Update_BoxID] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --¡‹—{ň±{÷S x9999999 or @BoxNum=0) begin set @BoxNum=1 end if ((@BoxID is not null) and (@BoxID!='') and (@BoxID!=0)) begin set @BoxBOXX='JYC-'+CONVERT(nvarchar,@BoxNum) end else begin set @BoxBOXX='JYC\'+CONVERT(nvarchar,@BoxNum) end --îO9e4Nöehˆ-N TN¢‹US TNň±{÷S Šbň±{÷S 9e:NNG+@BoxNum update Temp set ň±{÷S_¶g÷S=@BoxBOXX,number=@BoxNum where ¢‹US÷S=@OrdersID and ň±{÷S_¶g÷S=@BoxID --exec Pro_InsertXuhao @BoxBOXX --R^÷S exec Pro_ChildBox @BoxBOXX --RP[±{÷S set @BoxNum=@BoxNum+1; fetch next from cursor1 into @OrdersID,@BoxID end close cursor1 deallocate cursor1 end GO /****** Object: StoredProcedure [dbo].[proc_aa] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create proc [dbo].[proc_aa] as begin declare @g'Y±{…Qpeϑ int declare @ň±{÷S nvarchar(50) declare @P[±{÷S nvarchar(50) declare @ò]0RGrpe int declare @±{…Q»sƒt;`pe int select [ň±{÷Sÿ¶g÷S ÿ],P[±{÷S,count(*),±{…Q»sƒt;`pe,[»sƒtšS¦^ÿmm ÿ] from storage group by P[±{÷S,[ň±{÷Sÿ¶g÷S ÿ],±{…Q»sƒt;`pe,[»sƒtšS¦^ÿmm ÿ] open cursor2 fetch next from cursor2 into @ň±{÷S,@P[±{÷S,@ò]0RGrpe,@±{…Q»sƒt;`pe while @@fetch_status=0 begin --print(@šS¦^) fetch next from cursor2 into @ň±{÷S,@P[±{÷S,@ò]0RGrpe,@±{…Q»sƒt;`pe end close cursor2 --sQí•8nh deallocate cursor2 end GO /****** Object: StoredProcedure [dbo].[proc_ChuPian] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE proc [dbo].[proc_ChuPian] @P[±{÷SúQ nvarchar(100) as begin ------_ËYM†SP[±{ hQèSbS declare @»sƒt/UNID nvarchar(255) --hˆ:y»sƒt/UNID declare @Number int --hˆ:y,{àQGr declare @Count int --hˆ:y,{àQ*N±{ declare @Box_childID nvarchar(50) --°eP[±{÷S set @Number=1 set @Count=(select MAX(±{pe) from Order$ where P[±{÷S=@P[±{÷SúQ)+1 declare cursor_TaGlassId cursor for select »sƒt/UNID from storage where P[±{÷S=@P[±{÷SúQ order by CONVERT(int,rackID) desc,™e¶g N„v^÷S desc open cursor_TaGlassId fetch next from cursor_TaGlassId into @»sƒt/UNID while @@fetch_status=0 begin if(@Number<=50) begin set @Box_childID=@P[±{÷SúQ+'-'+CONVERT(nvarchar,@Count) set @Number=@Number+1 insert into glassOut2(^÷S,¢[7bãN÷S,¤N'åeg,¢‹US÷S,[ň±{÷Sÿ¶g÷S ÿ],±{…Q»sƒt;`pe,WY÷S,WY…Q»sƒt;`pe,»sƒtID,[»sƒt•¦^ÿmm ÿ],[»sƒt½[¦^ÿmm ÿ],[»sƒtšS¦^ÿmm ÿ],RackTypeID,rackID,™e¶g N„všS¦^,™e¶g N„v^÷S,±{…Q»sƒt^÷S,ö€«WšS¦^,P[±{÷S,»sƒt/UNID,ŸSň±{÷S,ŸS±{…Q^÷S) select ^÷S,¢[7bãN÷S,¤N'åeg,¢‹US÷S,[ň±{÷Sÿ¶g÷S ÿ],±{…Q»sƒt;`pe,WY÷S,WY…Q»sƒt;`pe,»sƒtID,[»sƒt•¦^ÿmm ÿ],[»sƒt½[¦^ÿmm ÿ],[»sƒtšS¦^ÿmm ÿ],RackTypeID,rackID,™e¶g N„všS¦^,™e¶g N„v^÷S,±{…Q»sƒt^÷S,ö€«WšS¦^,P[±{÷S,»sƒt/UNID,ŸSň±{÷S,ŸS±{…Q^÷S from storage where P[±{÷S=@P[±{÷SúQ and »sƒt/UNID=@»sƒt/UNID update Order$ set ±{pe=@Count where P[±{÷S=@P[±{÷SúQ and »sƒt/UNID=@»sƒt/UNID update glassOut2 set P[±{÷S=@Box_childID where P[±{÷S=@P[±{÷SúQ and »sƒt/UNID=@»sƒt/UNID end else begin set @Count=@Count+1 set @Number=2 set @Box_childID=@P[±{÷SúQ+'-'+CONVERT(nvarchar,@Count) insert into glassOut2(^÷S,¢[7bãN÷S,¤N'åeg,¢‹US÷S,[ň±{÷Sÿ¶g÷S ÿ],±{…Q»sƒt;`pe,WY÷S,WY…Q»sƒt;`pe,»sƒtID,[»sƒt•¦^ÿmm ÿ],[»sƒt½[¦^ÿmm ÿ],[»sƒtšS¦^ÿmm ÿ],RackTypeID,rackID,™e¶g N„všS¦^,™e¶g N„v^÷S,±{…Q»sƒt^÷S,ö€«WšS¦^,P[±{÷S,»sƒt/UNID,ŸSň±{÷S,ŸS±{…Q^÷S) select ^÷S,¢[7bãN÷S,¤N'åeg,¢‹US÷S,[ň±{÷Sÿ¶g÷S ÿ],±{…Q»sƒt;`pe,WY÷S,WY…Q»sƒt;`pe,»sƒtID,[»sƒt•¦^ÿmm ÿ],[»sƒt½[¦^ÿmm ÿ],[»sƒtšS¦^ÿmm ÿ],RackTypeID,rackID,™e¶g N„všS¦^,™e¶g N„v^÷S,±{…Q»sƒt^÷S,ö€«WšS¦^,P[±{÷S,»sƒt/UNID,ŸSň±{÷S,ŸS±{…Q^÷S from storage where P[±{÷S=@P[±{÷SúQ and »sƒt/UNID=@»sƒt/UNID update Order$ set ±{pe=@Count where P[±{÷S=@P[±{÷SúQ and »sƒt/UNID=@»sƒt/UNID update glassOut2 set P[±{÷S=@Box_childID where P[±{÷S=@P[±{÷SúQ and »sƒt/UNID=@»sƒt/UNID end fetch next from cursor_TaGlassId into @»sƒt/UNID end close cursor_TaGlassId --sQí•8nhcursor_TaGlassId deallocate cursor_TaGlassId end GO /****** Object: StoredProcedure [dbo].[proc_ChupianType1] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE proc [dbo].[proc_ChupianType1] @ň±{÷S nvarchar(50) as begin declare @P[±{÷S nvarchar(50) declare @±{…Q»sƒt;`pe int declare @P[±{…Qg'YšS¦^ nvarchar(50) declare @ò]0RGrpe int declare @g'Y±{…Qpeϑ int declare cursor_MyBox cursor for select P[±{÷S,±{…Q»sƒt;`pe,MAX([»sƒtšS¦^ÿmm ÿ]),count(*) from storage where [ň±{÷Sÿ¶g÷S ÿ]=@ň±{÷S group by P[±{÷S,±{…Q»sƒt;`pe open cursor_MyBox fetch next from cursor_MyBox into @P[±{÷S,@±{…Q»sƒt;`pe,@P[±{…Qg'YšS¦^,@ò]0RGrpe --M†SP[±{÷S Yt while @@fetch_status=0 begin declare @»sƒt/UNID2 nvarchar(255) --hˆ:y»sƒt/UNID declare @Grpe int --hˆ:y,{àQGr declare @±{pe int --hˆ:y,{àQ*N±{ declare @°eP[±{÷S nvarchar(50) --°eP[±{÷S if(@P[±{…Qg'YšS¦^='10') begin set @g'Y±{…Qpeϑ=45 end else if(@P[±{…Qg'YšS¦^='6') begin set @g'Y±{…Qpeϑ=60 end else if(@P[±{…Qg'YšS¦^='5') begin set @g'Y±{…Qpeϑ=70 end else begin set @g'Y±{…Qpeϑ=50 end --án³N±{agöN if(@ò]0RGrpe>=@g'Y±{…Qpeϑ) begin set @Grpe=0 set @±{pe=(select MAX(±{pe) from Order$ where P[±{÷S=@P[±{÷S)+1 declare cursor_MyGlass_id cursor for select »sƒt/UNID from storage where P[±{÷S=@P[±{÷S and »sƒt/UNID not in (select »sƒt/UNID from glassOut2 group by »sƒt/UNID) order by CONVERT(int,rackID) desc,™e¶g N„v^÷S desc open cursor_MyGlass_id fetch next from cursor_MyGlass_id into @»sƒt/UNID2 while @@fetch_status=0 begin if(@Grpe=@g'Y±{…Qpeϑ) begin break end else if(@Grpe<50) begin set @°eP[±{÷S=@P[±{÷S+'-'+CONVERT(nvarchar,@±{pe) set @Grpe=@Grpe+1 insert into glassOut2(^÷S,¢[7bãN÷S,¤N'åeg,¢‹US÷S,[ň±{÷Sÿ¶g÷S ÿ],±{…Q»sƒt;`pe,WY÷S,WY…Q»sƒt;`pe,»sƒtID,[»sƒt•¦^ÿmm ÿ],[»sƒt½[¦^ÿmm ÿ],[»sƒtšS¦^ÿmm ÿ],RackTypeID,rackID,™e¶g N„všS¦^,™e¶g N„v^÷S,±{…Q»sƒt^÷S,ö€«WšS¦^,P[±{÷S,»sƒt/UNID,ŸSň±{÷S,ŸS±{…Q^÷S) select ^÷S,¢[7bãN÷S,¤N'åeg,¢‹US÷S,[ň±{÷Sÿ¶g÷S ÿ],±{…Q»sƒt;`pe,WY÷S,WY…Q»sƒt;`pe,»sƒtID,[»sƒt•¦^ÿmm ÿ],[»sƒt½[¦^ÿmm ÿ],[»sƒtšS¦^ÿmm ÿ],RackTypeID,rackID,™e¶g N„všS¦^,™e¶g N„v^÷S,±{…Q»sƒt^÷S,ö€«WšS¦^,P[±{÷S,»sƒt/UNID,ŸSň±{÷S,ŸS±{…Q^÷S from storage where P[±{÷S=@P[±{÷S and »sƒt/UNID=@»sƒt/UNID2 update glassOut2 set P[±{÷S=@°eP[±{÷S where »sƒt/UNID=@»sƒt/UNID2 update Order$ set ±{pe=@±{pe where P[±{÷S=@P[±{÷S and »sƒt/UNID=@»sƒt/UNID2 end else begin if (@Grpe=50) begin set @±{pe=@±{pe+1 end set @°eP[±{÷S=@P[±{÷S+'-'+CONVERT(nvarchar,@±{pe) set @Grpe=@Grpe+1 insert into glassOut2(^÷S,¢[7bãN÷S,¤N'åeg,¢‹US÷S,[ň±{÷Sÿ¶g÷S ÿ],±{…Q»sƒt;`pe,WY÷S,WY…Q»sƒt;`pe,»sƒtID,[»sƒt•¦^ÿmm ÿ],[»sƒt½[¦^ÿmm ÿ],[»sƒtšS¦^ÿmm ÿ],RackTypeID,rackID,™e¶g N„všS¦^,™e¶g N„v^÷S,±{…Q»sƒt^÷S,ö€«WšS¦^,P[±{÷S,»sƒt/UNID,ŸSň±{÷S,ŸS±{…Q^÷S) select ^÷S,¢[7bãN÷S,¤N'åeg,¢‹US÷S,[ň±{÷Sÿ¶g÷S ÿ],±{…Q»sƒt;`pe,WY÷S,WY…Q»sƒt;`pe,»sƒtID,[»sƒt•¦^ÿmm ÿ],[»sƒt½[¦^ÿmm ÿ],[»sƒtšS¦^ÿmm ÿ],RackTypeID,rackID,™e¶g N„všS¦^,™e¶g N„v^÷S,±{…Q»sƒt^÷S,ö€«WšS¦^,P[±{÷S,»sƒt/UNID,ŸSň±{÷S,ŸS±{…Q^÷S from storage where P[±{÷S=@P[±{÷S and »sƒt/UNID=@»sƒt/UNID2 update glassOut2 set P[±{÷S=@°eP[±{÷S where »sƒt/UNID=@»sƒt/UNID2 update Order$ set ±{pe=@±{pe where P[±{÷S=@P[±{÷S and »sƒt/UNID=@»sƒt/UNID2 end fetch next from cursor_MyGlass_id into @»sƒt/UNID2 end close cursor_MyGlass_id --sQí•8nhcursor_MyGlass_id deallocate cursor_MyGlass_id break end else if((@±{…Q»sƒt;`pe-(select count(*) from Order$ where P[±{÷S=@P[±{÷S and ±{pe>0))=@ò]0RGrpe) --àe±{÷S ÿ±{…Q;`pe\ŽN50 Töeò]Ï~0R¾„v begin if(@ò]0RGrpe<=50) --àe±{÷S g^÷S ÿ„˜YtŒ[b„vÿpeϑ<50 ÿ gTNyb„vöeP begin set @±{pe=(select MAX(±{pe) from Order$ where P[±{÷S=@P[±{÷S)+1 set @°eP[±{÷S=@P[±{÷S+'-'+CONVERT(nvarchar,@±{pe) insert into glassOut2(^÷S,¢[7bãN÷S,¤N'åeg,¢‹US÷S,[ň±{÷Sÿ¶g÷S ÿ],±{…Q»sƒt;`pe,WY÷S,WY…Q»sƒt;`pe,»sƒtID,[»sƒt•¦^ÿmm ÿ],[»sƒt½[¦^ÿmm ÿ],[»sƒtšS¦^ÿmm ÿ],RackTypeID,rackID,™e¶g N„všS¦^,™e¶g N„v^÷S,±{…Q»sƒt^÷S,ö€«WšS¦^,P[±{÷S,»sƒt/UNID,ŸSň±{÷S,ŸS±{…Q^÷S) select ^÷S,¢[7bãN÷S,¤N'åeg,¢‹US÷S,[ň±{÷Sÿ¶g÷S ÿ],±{…Q»sƒt;`pe,WY÷S,WY…Q»sƒt;`pe,»sƒtID,[»sƒt•¦^ÿmm ÿ],[»sƒt½[¦^ÿmm ÿ],[»sƒtšS¦^ÿmm ÿ],RackTypeID,rackID,™e¶g N„všS¦^,™e¶g N„v^÷S,±{…Q»sƒt^÷S,ö€«WšS¦^,P[±{÷S,»sƒt/UNID,ŸSň±{÷S,ŸS±{…Q^÷S from storage where P[±{÷S=@P[±{÷S update glassOut2 set P[±{÷S=@°eP[±{÷S where P[±{÷S=@P[±{÷S --update Order$ set ±{pe=@±{pe where P[±{÷S=@P[±{÷S end else begin set @Grpe=0 set @±{pe=(select MAX(±{pe) from Order$ where P[±{÷S=@P[±{÷S)+1 declare cursor_MyGlass_id2 cursor for select »sƒt/UNID from storage where P[±{÷S=@P[±{÷S and »sƒt/UNID not in (select »sƒt/UNID from glassOut2 group by »sƒt/UNID) order by CONVERT(int,rackID) desc,™e¶g N„v^÷S desc open cursor_MyGlass_id2 fetch next from cursor_MyGlass_id2 into @»sƒt/UNID2 while @@fetch_status=0 begin if(@Grpe<50) begin set @°eP[±{÷S=@P[±{÷S+'-'+CONVERT(nvarchar,@±{pe) set @Grpe=@Grpe+1 insert into glassOut2(^÷S,¢[7bãN÷S,¤N'åeg,¢‹US÷S,[ň±{÷Sÿ¶g÷S ÿ],±{…Q»sƒt;`pe,WY÷S,WY…Q»sƒt;`pe,»sƒtID,[»sƒt•¦^ÿmm ÿ],[»sƒt½[¦^ÿmm ÿ],[»sƒtšS¦^ÿmm ÿ],RackTypeID,rackID,™e¶g N„všS¦^,™e¶g N„v^÷S,±{…Q»sƒt^÷S,ö€«WšS¦^,P[±{÷S,»sƒt/UNID,ŸSň±{÷S,ŸS±{…Q^÷S) select ^÷S,¢[7bãN÷S,¤N'åeg,¢‹US÷S,[ň±{÷Sÿ¶g÷S ÿ],±{…Q»sƒt;`pe,WY÷S,WY…Q»sƒt;`pe,»sƒtID,[»sƒt•¦^ÿmm ÿ],[»sƒt½[¦^ÿmm ÿ],[»sƒtšS¦^ÿmm ÿ],RackTypeID,rackID,™e¶g N„všS¦^,™e¶g N„v^÷S,±{…Q»sƒt^÷S,ö€«WšS¦^,P[±{÷S,»sƒt/UNID,ŸSň±{÷S,ŸS±{…Q^÷S from storage where P[±{÷S=@P[±{÷S and »sƒt/UNID=@»sƒt/UNID2 update glassOut2 set P[±{÷S=@°eP[±{÷S where »sƒt/UNID=@»sƒt/UNID2 update Order$ set ±{pe=@±{pe where P[±{÷S=@P[±{÷S and »sƒt/UNID=@»sƒt/UNID2 end else begin set @±{pe=@±{pe+1 set @°eP[±{÷S=@P[±{÷S+'-'+CONVERT(nvarchar,@±{pe) set @Grpe=@Grpe+1 insert into glassOut2(^÷S,¢[7bãN÷S,¤N'åeg,¢‹US÷S,[ň±{÷Sÿ¶g÷S ÿ],±{…Q»sƒt;`pe,WY÷S,WY…Q»sƒt;`pe,»sƒtID,[»sƒt•¦^ÿmm ÿ],[»sƒt½[¦^ÿmm ÿ],[»sƒtšS¦^ÿmm ÿ],RackTypeID,rackID,™e¶g N„všS¦^,™e¶g N„v^÷S,±{…Q»sƒt^÷S,ö€«WšS¦^,P[±{÷S,»sƒt/UNID,ŸSň±{÷S,ŸS±{…Q^÷S) select ^÷S,¢[7bãN÷S,¤N'åeg,¢‹US÷S,[ň±{÷Sÿ¶g÷S ÿ],±{…Q»sƒt;`pe,WY÷S,WY…Q»sƒt;`pe,»sƒtID,[»sƒt•¦^ÿmm ÿ],[»sƒt½[¦^ÿmm ÿ],[»sƒtšS¦^ÿmm ÿ],RackTypeID,rackID,™e¶g N„všS¦^,™e¶g N„v^÷S,±{…Q»sƒt^÷S,ö€«WšS¦^,P[±{÷S,»sƒt/UNID,ŸSň±{÷S,ŸS±{…Q^÷S from storage where P[±{÷S=@P[±{÷S and »sƒt/UNID=@»sƒt/UNID2 update glassOut2 set P[±{÷S=@°eP[±{÷S where »sƒt/UNID=@»sƒt/UNID2 update Order$ set ±{pe=@±{pe where P[±{÷S=@P[±{÷S and »sƒt/UNID=@»sƒt/UNID2 end fetch next from cursor_MyGlass_id2 into @»sƒt/UNID2 end close cursor_MyGlass_id2 --sQí•8nhcursor_MyGlass_id2 deallocate cursor_MyGlass_id2 end break end fetch next from cursor_MyBox into @P[±{÷S,@±{…Q»sƒt;`pe,@P[±{…Qg'YšS¦^,@ò]0RGrpe end close cursor_MyBox deallocate cursor_MyBox --sQí•8nhcursor_MyBox end GO /****** Object: StoredProcedure [dbo].[proc_ChupianType2] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE proc [dbo].[proc_ChupianType2] @ň±{÷S nvarchar(100) as begin declare @úQP[±{÷S nvarchar(50) declare @ò]0RGrpe2 int declare cursor_TaBox2 cursor for select úQP[±{÷S,count(*) from storage where [ň±{÷Sÿ¶g÷S ÿ]=@ň±{÷S group by úQP[±{÷S open cursor_TaBox2 fetch next from cursor_TaBox2 into @úQP[±{÷S,@ò]0RGrpe2 while @@fetch_status=0 begin --ò]0Rpeϑ==±{…Q»sƒt;`pe if((select count(*) from Order$ where úQP[±{÷S=@úQP[±{÷S)=@ò]0RGrpe2) begin insert into glassOut2(^÷S,¢[7bãN÷S,¤N'åeg,¢‹US÷S,[ň±{÷Sÿ¶g÷S ÿ],±{…Q»sƒt;`pe,WY÷S,WY…Q»sƒt;`pe,»sƒtID,[»sƒt•¦^ÿmm ÿ],[»sƒt½[¦^ÿmm ÿ],[»sƒtšS¦^ÿmm ÿ],RackTypeID,rackID,™e¶g N„všS¦^,™e¶g N„v^÷S,±{…Q»sƒt^÷S,ö€«WšS¦^,P[±{÷S,»sƒt/UNID,ŸSň±{÷S,ŸS±{…Q^÷S) select ^÷S,¢[7bãN÷S,¤N'åeg,¢‹US÷S,[ň±{÷Sÿ¶g÷S ÿ],±{…Q»sƒt;`pe,WY÷S,WY…Q»sƒt;`pe,»sƒtID,[»sƒt•¦^ÿmm ÿ],[»sƒt½[¦^ÿmm ÿ],[»sƒtšS¦^ÿmm ÿ],RackTypeID,rackID,™e¶g N„všS¦^,™e¶g N„v^÷S,±{…Q»sƒt^÷S,ö€«WšS¦^,P[±{÷S,»sƒt/UNID,ŸSň±{÷S,ŸS±{…Q^÷S from storage where úQP[±{÷S=@úQP[±{÷S end ------hQèSbSŒ[b fetch next from cursor_TaBox2 into @úQP[±{÷S,@ò]0RGrpe2 end close cursor_TaBox2 deallocate cursor_TaBox2 --sQí•8nhcursor_TaBox2 end GO /****** Object: StoredProcedure [dbo].[proc_Xuhao] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE proc [dbo].[proc_Xuhao] @P[±{÷S nvarchar(100) as begin --ûm R^÷S declare @»sƒt/UNID nvarchar(100) declare @^÷S nvarchar(100) set @^÷S=1 declare cursor_GlassOutXu cursor for select »sƒt/UNID from glassOut2 where P[±{÷S=@P[±{÷S order by ŸS±{…Q^÷S asc,[»sƒt½[¦^ÿmm ÿ] desc,»sƒtID open cursor_GlassOutXu fetch next from cursor_GlassOutXu into @»sƒt/UNID while @@fetch_status=0 begin update glassOut2 set ±{…Q»sƒt^÷S=@^÷S where P[±{÷S=@P[±{÷S and »sƒt/UNID=@»sƒt/UNID set @^÷S=@^÷S+1 fetch next from cursor_GlassOutXu into @»sƒt/UNID end close cursor_GlassOutXu deallocate cursor_GlassOutXu end GO /****** Object: StoredProcedure [dbo].[proc_Zong] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE proc [dbo].[proc_Zong] as begin if ((select count(*) from glassOut)=0)--åg wúQGrhˆ/f&Tò] gpenc begin declare @ň±{÷S nvarchar(50) declare cursor_All cursor for select [ň±{÷Sÿ¶g÷S ÿ] from storage group by [ň±{÷Sÿ¶g÷S ÿ] open cursor_All fetch next from cursor_All into @ň±{÷S --_ËYM†S@b g„vň±{÷S while @@fetch_status=0 begin --ÖNìN„vň±{÷S án†NúQ if((select PATINDEX('%JYC-%',@ň±{÷S))>0 and (select count(*) from Order$ where [ň±{÷Sÿ¶g÷S ÿ]=@ň±{÷S)=(select count(*) from storage where [ň±{÷Sÿ¶g÷S ÿ]=@ň±{÷S)) begin declare @P[±{÷S nvarchar(50) declare @±{…Q»sƒt;`pe int declare @ò]0RGrpe int declare cursor_TaBox cursor for select P[±{÷S,±{…Q»sƒt;`pe,count(*) from storage where [ň±{÷Sÿ¶g÷S ÿ]=@ň±{÷S group by P[±{÷S,±{…Q»sƒt;`pe open cursor_TaBox fetch next from cursor_TaBox into @P[±{÷S,@±{…Q»sƒt;`pe,@ò]0RGrpe while @@fetch_status=0 begin exec proc_ChuPian @P[±{÷S ------hQèSbSŒ[b fetch next from cursor_TaBox into @P[±{÷S,@±{…Q»sƒt;`pe,@ò]0RGrpe end close cursor_TaBox deallocate cursor_TaBox --sQí•8nhcursor_MyBox break --úQGr end --bìN„vň±{÷S else if((select PATINDEX('%JYC\%',@ň±{÷S))>0) begin if (select count(*) from Order$ where [ň±{÷Sÿ¶g÷S ÿ]=@ň±{÷S and úQP[±{÷S is not null)>0 begin exec proc_ChupianType2 @ň±{÷S --!j_2 end else begin exec proc_ChupianType1 @ň±{÷S --!j_1 end end if ((select count(*) from glassOut2)!=0)--åg wúQGrhˆ/f&Tò] gpenc begin break end fetch next from cursor_All into @ň±{÷S end close cursor_All deallocate cursor_All --sQí•8nhcursor_All exec Pro_SumOut --¡‹—{úQGrhˆP[±{…Q;`pe v^ûm RۏúQGrhˆ end end GO /****** Object: DdlTrigger [OnTriggerDboSchema] Script Date: 2023/12/11 8:45:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE trigger [OnTriggerDboSchema] ON database FOR create_table, create_view AS BEGIN DECLARE @xmlEventData xml SELECT @xmlEventData = eventdata() DECLARE @schemaName nvarchar(max) DECLARE @objectName nvarchar(max) DECLARE @DynSql nvarchar(max) SET @schemaName = convert(nvarchar(max), @xmlEventData.query('/EVENT_INSTANCE/SchemaName/text()')) SET @objectName = convert(nvarchar(max), @xmlEventData.query('/EVENT_INSTANCE/ObjectName/text()')) IF(@schemaName='') BEGIN SET @DynSql = N'alter schema [dbo] transfer [' + @schemaName + N'].[' + @objectName + N']' EXEC sp_executesql @statement=@DynSql END END SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO ENABLE TRIGGER [OnTriggerDboSchema] ON DATABASE GO