ÿþ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»st;`pe] [float] NULL,

	[WY÷S] [nvarchar](max) NULL,

	[WYQ»st;`pe] [float] NULL,

	[»stID] [nvarchar](max) NULL,

	[»st¦^ÿmm	ÿ] [float] NULL,

	[»st½[¦^ÿmm	ÿ] [float] NULL,

	[»stS¦^ÿmm	ÿ] [float] NULL,

	[WYQ»st^÷S] [float] NULL,

	[±{Q»st^÷S] [float] NULL,

	[¢US»st;`pe] [float] NULL,

	[item_quantity] [int] NULL,

	[RackTypeID] [nvarchar](max) NULL,

	[P[±{÷S] [nvarchar](max) NULL,

	[RackID] [float] NULL,

	[±{pe] [int] NULL,

	[»st/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»st;`pe] [float] NULL,

	[WY÷S] [nvarchar](max) NULL,

	[WYQ»st;`pe] [float] NULL,

	[»stID] [nvarchar](max) NULL,

	[»st¦^ÿmm	ÿ] [float] NULL,

	[»st½[¦^ÿmm	ÿ] [float] NULL,

	[»stS¦^ÿmm	ÿ] [float] NULL,

	[RackTypeID] [float] NULL,

	[rackID] [float] NULL,

	[e¶g
NvS¦^] [int] NULL,

	[e¶g
Nv^÷S] [int] NULL,

	[±{Q»st^÷S] [int] NULL,

	[ö«WS¦^] [float] NULL,

	[P[±{÷S] [nvarchar](max) NULL,

	[±{pe] [int] NULL,

	[»st/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»st;`pe] [float] NULL,

	[WY÷S] [nvarchar](max) NULL,

	[WYQ»st;`pe] [float] NULL,

	[»stID] [nvarchar](max) NULL,

	[»st¦^ÿmm	ÿ] [float] NULL,

	[»st½[¦^ÿmm	ÿ] [float] NULL,

	[»stS¦^ÿmm	ÿ] [float] NULL,

	[RackTypeID] [float] NULL,

	[rackID] [float] NULL,

	[e¶g
NvS¦^] [int] NULL,

	[e¶g
Nv^÷S] [int] NULL,

	[±{Q»st^÷S] [int] NULL,

	[ö«WS¦^] [float] NULL,

	[P[±{÷S] [nvarchar](max) NULL,

	[±{pe] [int] NULL,

	[»st/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»st;`pe] [float] NULL,

	[WY÷S] [nvarchar](max) NULL,

	[WYQ»st;`pe] [float] NULL,

	[»stID] [nvarchar](max) NULL,

	[»st¦^ÿmm	ÿ] [float] NULL,

	[»st½[¦^ÿmm	ÿ] [float] NULL,

	[»stS¦^ÿmm	ÿ] [float] NULL,

	[RackTypeID] [float] NULL,

	[rackID] [float] NULL,

	[e¶g
NvS¦^] [int] NULL,

	[e¶g
Nv^÷S] [int] NULL,

	[±{Q»st^÷S] [int] NULL,

	[ö«WS¦^] [float] NULL,

	[P[±{÷S] [nvarchar](max) NULL,

	[±{pe] [int] NULL,

	[»st/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»st;`pe] [float] NULL,

	[WY÷S] [nvarchar](max) NULL,

	[WYQ»st;`pe] [float] NULL,

	[»stID] [nvarchar](max) NULL,

	[»st¦^ÿmm	ÿ] [float] NULL,

	[»st½[¦^ÿmm	ÿ] [float] NULL,

	[»stS¦^ÿmm	ÿ] [float] NULL,

	[WYQ»st^÷S] [float] NULL,

	[±{Q»st^÷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»st;`pe] [float] NULL,

	[WY÷S] [nvarchar](max) NULL,

	[WYQ»st;`pe] [float] NULL,

	[»stID] [nvarchar](max) NULL,

	[»st¦^ÿmm	ÿ] [float] NULL,

	[»st½[¦^ÿmm	ÿ] [float] NULL,

	[»stS¦^ÿmm	ÿ] [float] NULL,

	[WYQ»st^÷S] [float] NULL,

	[±{Q»st^÷S] [float] NULL,

	[¢US»st;`pe] [float] NULL,

	[item_quantity] [int] NULL,

	[RackTypeID] [nvarchar](max) NULL,

	[P[±{÷S] [nvarchar](max) NULL,

	[RackID] [float] NULL,

	[±{pe] [int] NULL,

	[»st/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,

	[»st;`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»st;`pe] [float] NULL,

	[WY÷S] [nvarchar](max) NULL,

	[WYQ»st;`pe] [float] NULL,

	[»stID] [nvarchar](max) NULL,

	[»st¦^ÿmm	ÿ] [float] NULL,

	[»st½[¦^ÿmm	ÿ] [float] NULL,

	[»stS¦^ÿmm	ÿ] [float] NULL,

	[RackTypeID] [float] NULL,

	[rackID] [float] NULL,

	[e¶g
NvS¦^] [int] NULL,

	[e¶g
Nv^÷S] [int] NULL,

	[±{Q»st^÷S] [int] NULL,

	[ö«WS¦^] [float] NULL,

	[item_quantity] [int] NULL,

	[P[±{÷S] [nvarchar](max) NULL,

	[±{pe] [int] NULL,

	[»st/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»st;`pe] [float] NULL,

	[WY÷S] [nvarchar](255) NULL,

	[WYQ»st;`pe] [float] NULL,

	[»stID] [nvarchar](255) NULL,

	[»st¦^_mm] [float] NULL,

	[»st½[¦^_mm] [float] NULL,

	[»stS¦^_mm] [float] NULL,

	[WYQ»st^÷S] [float] NULL,

	[±{Q»st^÷S] [float] NULL,

	[¢US»st;`pe] [float] NULL,

	[item_quantity] [float] NULL,

	[RackTypeID] [nvarchar](255) NULL,

	[P[±{÷S] [nvarchar](255) NULL,

	[RackID] [float] NULL,

	[±{pe] [int] NULL,

	[»st/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](

	[»st/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 @»stID nvarchar(50)

	declare @»st¦^_mm nvarchar(50)

	declare @»st½[¦^_mm nvarchar(50)

	declare @»stS¦^_mm nvarchar(50)

	declare @±{Q»st^÷S nvarchar(50)

	declare @¢US»st;`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 »stID from Order$ group by »stID)     --ÿb0R@b	g¡l	gûm Rv»stID

	open cursor1

	fetch next from cursor1 into @¢[7bãN÷S,@¤N'åeg,@¢US÷S,@Å±{÷S_¶g÷S,@»stID,@»st¦^_mm,@»st½[¦^_mm,@»stS¦^_mm,@±{Q»st^÷S,@¢US»st;`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 RvpeÏ

			while @prices>0

				begin

					declare @»st/UNID nvarchar(50)

					set @ò]0Rpe=@ò]0Rpe+1

					set @»st/UNID=@»stID+'-'+CONVERT(nvarchar,@ò]0Rpe)

					--set @SÅ±{÷S=@Å±{÷S_¶g÷S

					insert into Temp(¢[7bãN÷S,¤N'åeg,¢US÷S,Å±{÷S_¶g÷S,»stID,»st¦^_mm,»st½[¦^_mm,»stS¦^_mm,±{Q»st^÷S,¢US»st;`pe,item_quantity,±{pe,»st/UNID,SÅ±{÷S,last_update_time,S±{Q^÷S,¢US÷S÷S)    --ûm RW[µk  ±{pe Ø¤<P:N1

					select @¢[7bãN÷S,@¤N'åeg,@¢US÷S,@Å±{÷S_¶g÷S,@»stID,@»st¦^_mm,@»st½[¦^_mm,@»stS¦^_mm,@±{Q»st^÷S,@¢US»st;`pe,@item_quantity,0,@»st/UNID,@Å±{÷S_¶g÷S,@ûm Råeg,@±{Q»st^÷S,LEFT(@»stID,13)

					set @prices=@prices-1

				end

			fetch next from cursor1 into @¢[7bãN÷S,@¤N'åeg,@¢US÷S,@Å±{÷S_¶g÷S,@»stID,@»st¦^_mm,@»st½[¦^_mm,@»stS¦^_mm,@±{Q»st^÷S,@¢US»st;`pe,@item_quantity,@ûm Råeg

		end

	close cursor1                   --sQí8nh

	deallocate cursor1

end

GO

/****** Object:  StoredProcedure [dbo].[Pro_ChildBox]    Script Date: 2023/12/11 8:45:00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE proc [dbo].[Pro_ChildBox]

@Å±{÷S_¶g÷S nvarchar(100)

as

begin

	if (select MAX(CONVERT(int,S±{Q^÷S)) from Temp where Å±{÷S_¶g÷S=@Å±{÷S_¶g÷S)>0  --	g^÷Sv   

		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ìNvÅ±{÷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 »st/UNID in (select top(50)»st/UNID from Temp where Å±{÷S_¶g÷S=@Å±{÷S_¶g÷S and P[±{÷S is null order by ±{Q»st^÷S,»st½[¦^_mm desc,»stID) 

			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'YS¦^ int                  --±{Qg'YS¦^

	set @±{Qg'YS¦^=(select MAX(CONVERT(int,»stS¦^_mm)) from Temp where Å±{÷S_¶g÷S=@Å±{÷S_¶g÷S)

	if(@±{Qg'YS¦^=10)

		begin

			set @g'Y±{QpeÏ=45

		end

	else if(@±{Qg'YS¦^=6)

		begin

			set @g'Y±{QpeÏ=60

		end

	else if(@±{Qg'YS¦^=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 »st/UNID in (select top(@g'Y±{QpeÏ)»st/UNID from Temp where Å±{÷S_¶g÷S=@Å±{÷S_¶g÷S and P[±{÷S is null order by ±{Q»st^÷S,»st½[¦^_mm desc,»stID) 

					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(»st/UNID)    --ûm RW[µk  ±{pe Ø¤<P:N1

					select top(@g'Y±{QpeÏ)»st/UNID from Temp where Å±{÷S_¶g÷S=@Å±{÷S_¶g÷S and P[±{÷S is null order by ±{Q»st^÷S,»st½[¦^_mm desc,»stID

					update Temp set úQP[±{÷S=@Box_childID where Å±{÷S_¶g÷S=@Å±{÷S_¶g÷S and »st/UNID in (select »st/UNID from weiyiID)

					update Temp set P[±{÷S=@Box_childID where Å±{÷S_¶g÷S=@Å±{÷S_¶g÷S and »st/UNID in (select top(50)»st/UNID from weiyiID)

					delete top(50) weiyiID

					set @Count=@Count+1

					set @Box_childID=@Å±{÷S_¶g÷S+'-'+CONVERT(nvarchar,@Count)

					update Temp set P[±{÷S=@Box_childID where Å±{÷S_¶g÷S=@Å±{÷S_¶g÷S and »st/UNID in (select »st/UNID from weiyiID)

					delete weiyiID

					set @Count=@Count+1

				end

		end

	

end

GO

/****** Object:  StoredProcedure [dbo].[Pro_Copys]    Script Date: 2023/12/11 8:45:00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE proc [dbo].[Pro_Copys]

@day int

as

begin

	begin tran

		begin try

			exec Pro_Delete

			declare @X[(W int

			set @X[(W=(select count(*) from [192.168.3.16].shebei.dbo.t_report_processing_order where work_shop=2000 and last_update_time>getdate()-@day)

			if (@X[(W>0)

				begin

					--print 1

					exec insert_chai @day

					declare @counnt int

					declare @Dateing datetime

					--ågâg'Yvnumber

					set @counnt=(select max(number) from Order$)

					if(@counnt is null)

						begin

							set @counnt=0

						end

					set	@counnt=@counnt+1

					--¡{ÿÅ±{÷S:N  NG+peW[   x<h_   RúQP[±{÷S

					exec Pro_Update_BoxID @counnt

					--¡{ÿ±{Q»st;`pe

					exec Pro_Sum

					--¡{e¶g{|W

					exec Pro_Type

					--bYt[v4Nöehpenc ReQ0ROrdersh-N				

					set @Dateing=GETDATE()

					--úQÇv

					insert into QuestionsTB(customer_order_number,last_update_date) 

					select ¢US÷S,@Dateing from Order$ where 

					¢US÷S in(select ¢US÷S from Order$ where »stID in(select »stID from Temp group by »stID) group by ¢[7bãN÷S,¢US÷S) and

					¢US÷S not in(select customer_order_number from QuestionsTB group by customer_order_number)					

					group by ¢[7bãN÷S,¢US÷S having max(±{pe)!=0

					--(W^Qv

					insert into QuestionsTB(customer_order_number,last_update_date) 

					select ¢US÷S,@Dateing from Order$ where 

					¢US÷S in(select ¢US÷S from Order$ where »stID in(select »stID from Temp group by »stID) group by ¢[7bãN÷S,¢US÷S) and

					¢US÷S not in(select customer_order_number from QuestionsTB group by customer_order_number) and

					¢US÷S in(select ¢US÷S from storage group by ¢[7bãN÷S,¢US÷S) 					

					group by ¢[7bãN÷S,¢US÷S



					delete Order$  where 

					¢US÷S in( 

					select ¢US÷S from Order$ where 

					¢US÷S in(select ¢US÷S from Order$ where »stID in(select »stID from Temp group by »stID) group by ¢[7bãN÷S,¢US÷S) and

					¢US÷S not in(select ¢US÷S from storage group by ¢[7bãN÷S,¢US÷S) 

					group by ¢[7bãN÷S,¢US÷S having max(±{pe)=0)

					 

					insert into Order$ select * from Temp where »stID not in(select »stID from Order$ group by »stID)     --ÿb0R@b	g¡l	gûm Rv»stID

					-- RdTemph

					delete Temp

				end

		end try  

		begin catch

			select Error_number() as ErrorNumber,  --ïãNx

			Error_severity() as ErrorSeverity,     --ï%NÍ§~+Rÿ§~+R\N10 try catch Uc·
N0R

			Error_state() as ErrorState ,          --ï¶r`x

			Error_Procedure() as ErrorProcedure ,  --úQ°sïvX[¨PÇzbæÑShVv
Tðy0

			Error_line() as ErrorLine,             --ÑSuïvL÷S

			Error_message() as ErrorMessage        --ïvwQSOáOo`

			rollback tran                          ---1uNúQÿÙÌÞVÚn0R_ËYÿ,{NagíåS_N¡l	gÒceQbR0

		end catch

	if(@@trancount>0)

	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

-- Rdorders$h  15)YKNMRv

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 @»st/UNID nvarchar(255)	       --h:y»st/UNID

	declare @±{Q»st^÷S int	               --h:y±{Q»st^÷S

	set @±{Q»st^÷S=1

	declare cursor_Xuhao cursor for

	select »st/UNID from Temp where Å±{÷S_¶g÷S=@Å±{÷S_¶g÷S order by S±{Q^÷S asc,CONVERT(int,»st½[¦^_mm) desc,»stID

	open cursor_Xuhao

	fetch next from cursor_Xuhao into @»st/UNID

	while @@fetch_status=0

		begin

			update Temp set ±{Q»st^÷S=@±{Q»st^÷S where Å±{÷S_¶g÷S=@Å±{÷S_¶g÷S and »st/UNID=@»st/UNID

			set @±{Q»st^÷S=@±{Q»st^÷S+1

			fetch next from cursor_Xuhao into @»st/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»st;`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^÷Sv

				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»st^÷S=0 where P[±{÷S=@P[±{÷S							

							exec proc_Xuhao @P[±{÷S

						end

				end

			else  --àe^÷Sv

				begin

					exec proc_Xuhao @P[±{÷S

				end

			update glassOut2 set ±{Q»st;`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»st;`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»st;`pe,WY÷S,WYQ»st;`pe,»stID,[»st¦^ÿmm	ÿ],[»st½[¦^ÿmm	ÿ],[»stS¦^ÿmm	ÿ],RackTypeID,rackID,e¶g
NvS¦^,e¶g
Nv^÷S,±{Q»st^÷S,ö«WS¦^,P[±{÷S,»st/UNID,SÅ±{÷S,S±{Q^÷S) 

	select ^÷S,¢[7bãN÷S,¤N'åeg,¢US÷S,[Å±{÷Sÿ¶g÷S	ÿ],±{Q»st;`pe,WY÷S,WYQ»st;`pe,»stID,[»st¦^ÿmm	ÿ],[»st½[¦^ÿmm	ÿ],[»stS¦^ÿmm	ÿ],RackTypeID,rackID,e¶g
NvS¦^,e¶g
Nv^÷S,±{Q»st^÷S,ö«WS¦^,P[±{÷S,»st/UNID,SÅ±{÷S,S±{Q^÷S from glassOut2 

	

	insert into glassOut3(^÷S,¢[7bãN÷S,¤N'åeg,¢US÷S,[Å±{÷Sÿ¶g÷S	ÿ],±{Q»st;`pe,WY÷S,WYQ»st;`pe,»stID,[»st¦^ÿmm	ÿ],[»st½[¦^ÿmm	ÿ],[»stS¦^ÿmm	ÿ],RackTypeID,rackID,e¶g
NvS¦^,e¶g
Nv^÷S,±{Q»st^÷S,ö«WS¦^,P[±{÷S,»st/UNID,SÅ±{÷S,S±{Q^÷S,úQGröeô) 

	select ^÷S,¢[7bãN÷S,¤N'åeg,¢US÷S,[Å±{÷Sÿ¶g÷S	ÿ],±{Q»st;`pe,WY÷S,WYQ»st;`pe,»stID,[»st¦^ÿmm	ÿ],[»st½[¦^ÿmm	ÿ],[»stS¦^ÿmm	ÿ],RackTypeID,rackID,e¶g
NvS¦^,e¶g
Nv^÷S,±{Q»st^÷S,ö«WS¦^,P[±{÷S,»st/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»stv{|W\N400:N1,400-600:N2,600-800:N3,800åN
N:N5

	update Temp set RackTypeID=1 where »st½[¦^_mm<400.00 

	update Temp set RackTypeID=2 where »st½[¦^_mm between 400 and 651

	update Temp set RackTypeID=3 where »st½[¦^_mm between 652 and 850

	update Temp set RackTypeID=5 where »st½[¦^_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   x<h_ JYC-

CREATE proc [dbo].[Pro_Update_BoxID]

@BoxNum int   -- OÂSpe Å±{÷S;`pe

as

begin

--@OrdersID @b	g¢US÷S  @BoxID@b	gvÅ±{÷S

	declare @OrdersID nvarchar(100)

	declare @BoxID nvarchar(100)

	declare @BoxBOXX nvarchar(100)

	declare cursor1 cursor for 

	select ¢US÷S,Å±{÷S_¶g÷S from Temp group by Å±{÷S_¶g÷S,¢US÷S

	open cursor1

	fetch next from cursor1 into @OrdersID,@BoxID;

	--set @BoxNum=1;

	while @@fetch_status=0

		begin 

			--YgÅ±{÷Sò]Ï~ 0R¾ 999999RÍ°e1_ËY

			if(@BoxNum>9999999 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»st;`pe int

	select [Å±{÷Sÿ¶g÷S	ÿ],P[±{÷S,count(*),±{Q»st;`pe,[»stS¦^ÿmm	ÿ] from storage group by P[±{÷S,[Å±{÷Sÿ¶g÷S	ÿ],±{Q»st;`pe,[»stS¦^ÿmm	ÿ]

	open cursor2

	fetch next from cursor2 into @Å±{÷S,@P[±{÷S,@ò]0RGrpe,@±{Q»st;`pe

	while @@fetch_status=0

		begin

			--print(@S¦^)		

			fetch next from cursor2 into @Å±{÷S,@P[±{÷S,@ò]0RGrpe,@±{Q»st;`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

	------_ËYMSP[±{ hQèSbS

	declare @»st/UNID nvarchar(255)	        --h:y»st/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 »st/UNID from storage where P[±{÷S=@P[±{÷SúQ order by CONVERT(int,rackID) desc,e¶g
Nv^÷S desc

	open cursor_TaGlassId

	fetch next from cursor_TaGlassId into @»st/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»st;`pe,WY÷S,WYQ»st;`pe,»stID,[»st¦^ÿmm	ÿ],[»st½[¦^ÿmm	ÿ],[»stS¦^ÿmm	ÿ],RackTypeID,rackID,e¶g
NvS¦^,e¶g
Nv^÷S,±{Q»st^÷S,ö«WS¦^,P[±{÷S,»st/UNID,SÅ±{÷S,S±{Q^÷S) 

							select ^÷S,¢[7bãN÷S,¤N'åeg,¢US÷S,[Å±{÷Sÿ¶g÷S	ÿ],±{Q»st;`pe,WY÷S,WYQ»st;`pe,»stID,[»st¦^ÿmm	ÿ],[»st½[¦^ÿmm	ÿ],[»stS¦^ÿmm	ÿ],RackTypeID,rackID,e¶g
NvS¦^,e¶g
Nv^÷S,±{Q»st^÷S,ö«WS¦^,P[±{÷S,»st/UNID,SÅ±{÷S,S±{Q^÷S from storage 

							where P[±{÷S=@P[±{÷SúQ and »st/UNID=@»st/UNID

							update Order$ set ±{pe=@Count where P[±{÷S=@P[±{÷SúQ and »st/UNID=@»st/UNID

							update glassOut2 set P[±{÷S=@Box_childID where P[±{÷S=@P[±{÷SúQ and »st/UNID=@»st/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»st;`pe,WY÷S,WYQ»st;`pe,»stID,[»st¦^ÿmm	ÿ],[»st½[¦^ÿmm	ÿ],[»stS¦^ÿmm	ÿ],RackTypeID,rackID,e¶g
NvS¦^,e¶g
Nv^÷S,±{Q»st^÷S,ö«WS¦^,P[±{÷S,»st/UNID,SÅ±{÷S,S±{Q^÷S) 

							select ^÷S,¢[7bãN÷S,¤N'åeg,¢US÷S,[Å±{÷Sÿ¶g÷S	ÿ],±{Q»st;`pe,WY÷S,WYQ»st;`pe,»stID,[»st¦^ÿmm	ÿ],[»st½[¦^ÿmm	ÿ],[»stS¦^ÿmm	ÿ],RackTypeID,rackID,e¶g
NvS¦^,e¶g
Nv^÷S,±{Q»st^÷S,ö«WS¦^,P[±{÷S,»st/UNID,SÅ±{÷S,S±{Q^÷S from storage 

							where P[±{÷S=@P[±{÷SúQ and »st/UNID=@»st/UNID

							update Order$ set ±{pe=@Count where P[±{÷S=@P[±{÷SúQ and »st/UNID=@»st/UNID

							update glassOut2 set P[±{÷S=@Box_childID where P[±{÷S=@P[±{÷SúQ and »st/UNID=@»st/UNID	

						end

				fetch next from cursor_TaGlassId into @»st/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»st;`pe int

	declare @P[±{Qg'YS¦^ nvarchar(50)

	declare @ò]0RGrpe int

	declare @g'Y±{QpeÏ int

	declare cursor_MyBox cursor for

	select P[±{÷S,±{Q»st;`pe,MAX([»stS¦^ÿmm	ÿ]),count(*) from storage where [Å±{÷Sÿ¶g÷S	ÿ]=@Å±{÷S group by P[±{÷S,±{Q»st;`pe

	open cursor_MyBox

	fetch next from cursor_MyBox into @P[±{÷S,@±{Q»st;`pe,@P[±{Qg'YS¦^,@ò]0RGrpe

	--MSP[±{÷S Yt

	while @@fetch_status=0

		begin

			declare @»st/UNID2 nvarchar(255)	        --h:y»st/UNID

			declare @Grpe int	--h:y,{àQGr

			declare @±{pe int	--h:y,{àQ*N±{

			declare @°eP[±{÷S nvarchar(50)              --°eP[±{÷S

			if(@P[±{Qg'YS¦^='10')

				begin

					set @g'Y±{QpeÏ=45

				end

			else if(@P[±{Qg'YS¦^='6')

				begin

					set @g'Y±{QpeÏ=60

				end

			else if(@P[±{Qg'YS¦^='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 »st/UNID from storage 

					where P[±{÷S=@P[±{÷S and »st/UNID not in (select »st/UNID from glassOut2 group by »st/UNID) order by CONVERT(int,rackID) desc,e¶g
Nv^÷S desc

					open cursor_MyGlass_id

					fetch next from cursor_MyGlass_id into @»st/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»st;`pe,WY÷S,WYQ»st;`pe,»stID,[»st¦^ÿmm	ÿ],[»st½[¦^ÿmm	ÿ],[»stS¦^ÿmm	ÿ],RackTypeID,rackID,e¶g
NvS¦^,e¶g
Nv^÷S,±{Q»st^÷S,ö«WS¦^,P[±{÷S,»st/UNID,SÅ±{÷S,S±{Q^÷S) 

									select ^÷S,¢[7bãN÷S,¤N'åeg,¢US÷S,[Å±{÷Sÿ¶g÷S	ÿ],±{Q»st;`pe,WY÷S,WYQ»st;`pe,»stID,[»st¦^ÿmm	ÿ],[»st½[¦^ÿmm	ÿ],[»stS¦^ÿmm	ÿ],RackTypeID,rackID,e¶g
NvS¦^,e¶g
Nv^÷S,±{Q»st^÷S,ö«WS¦^,P[±{÷S,»st/UNID,SÅ±{÷S,S±{Q^÷S from storage 

									where P[±{÷S=@P[±{÷S and »st/UNID=@»st/UNID2

									update glassOut2 set P[±{÷S=@°eP[±{÷S where »st/UNID=@»st/UNID2

									update Order$ set ±{pe=@±{pe where P[±{÷S=@P[±{÷S and »st/UNID=@»st/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»st;`pe,WY÷S,WYQ»st;`pe,»stID,[»st¦^ÿmm	ÿ],[»st½[¦^ÿmm	ÿ],[»stS¦^ÿmm	ÿ],RackTypeID,rackID,e¶g
NvS¦^,e¶g
Nv^÷S,±{Q»st^÷S,ö«WS¦^,P[±{÷S,»st/UNID,SÅ±{÷S,S±{Q^÷S) 

									select ^÷S,¢[7bãN÷S,¤N'åeg,¢US÷S,[Å±{÷Sÿ¶g÷S	ÿ],±{Q»st;`pe,WY÷S,WYQ»st;`pe,»stID,[»st¦^ÿmm	ÿ],[»st½[¦^ÿmm	ÿ],[»stS¦^ÿmm	ÿ],RackTypeID,rackID,e¶g
NvS¦^,e¶g
Nv^÷S,±{Q»st^÷S,ö«WS¦^,P[±{÷S,»st/UNID,SÅ±{÷S,S±{Q^÷S from storage 

									where P[±{÷S=@P[±{÷S and »st/UNID=@»st/UNID2

									update glassOut2 set P[±{÷S=@°eP[±{÷S where »st/UNID=@»st/UNID2

									update Order$ set ±{pe=@±{pe where P[±{÷S=@P[±{÷S and »st/UNID=@»st/UNID2

								end

							fetch next from cursor_MyGlass_id into @»st/UNID2

						end

					close cursor_MyGlass_id                   --sQí8nhcursor_MyGlass_id

					deallocate cursor_MyGlass_id

					break

				end

			else if((@±{Q»st;`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[bvÿpeÏ<50	ÿ  gTNybvö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»st;`pe,WY÷S,WYQ»st;`pe,»stID,[»st¦^ÿmm	ÿ],[»st½[¦^ÿmm	ÿ],[»stS¦^ÿmm	ÿ],RackTypeID,rackID,e¶g
NvS¦^,e¶g
Nv^÷S,±{Q»st^÷S,ö«WS¦^,P[±{÷S,»st/UNID,SÅ±{÷S,S±{Q^÷S) 

						select ^÷S,¢[7bãN÷S,¤N'åeg,¢US÷S,[Å±{÷Sÿ¶g÷S	ÿ],±{Q»st;`pe,WY÷S,WYQ»st;`pe,»stID,[»st¦^ÿmm	ÿ],[»st½[¦^ÿmm	ÿ],[»stS¦^ÿmm	ÿ],RackTypeID,rackID,e¶g
NvS¦^,e¶g
Nv^÷S,±{Q»st^÷S,ö«WS¦^,P[±{÷S,»st/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 »st/UNID from storage 

							where P[±{÷S=@P[±{÷S and »st/UNID not in (select »st/UNID from glassOut2 group by »st/UNID) order by CONVERT(int,rackID) desc,e¶g
Nv^÷S desc

							open cursor_MyGlass_id2

							fetch next from cursor_MyGlass_id2 into @»st/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»st;`pe,WY÷S,WYQ»st;`pe,»stID,[»st¦^ÿmm	ÿ],[»st½[¦^ÿmm	ÿ],[»stS¦^ÿmm	ÿ],RackTypeID,rackID,e¶g
NvS¦^,e¶g
Nv^÷S,±{Q»st^÷S,ö«WS¦^,P[±{÷S,»st/UNID,SÅ±{÷S,S±{Q^÷S) 

											select ^÷S,¢[7bãN÷S,¤N'åeg,¢US÷S,[Å±{÷Sÿ¶g÷S	ÿ],±{Q»st;`pe,WY÷S,WYQ»st;`pe,»stID,[»st¦^ÿmm	ÿ],[»st½[¦^ÿmm	ÿ],[»stS¦^ÿmm	ÿ],RackTypeID,rackID,e¶g
NvS¦^,e¶g
Nv^÷S,±{Q»st^÷S,ö«WS¦^,P[±{÷S,»st/UNID,SÅ±{÷S,S±{Q^÷S from storage 

											where P[±{÷S=@P[±{÷S and »st/UNID=@»st/UNID2

											update glassOut2 set P[±{÷S=@°eP[±{÷S where »st/UNID=@»st/UNID2

											update Order$ set ±{pe=@±{pe where P[±{÷S=@P[±{÷S and »st/UNID=@»st/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»st;`pe,WY÷S,WYQ»st;`pe,»stID,[»st¦^ÿmm	ÿ],[»st½[¦^ÿmm	ÿ],[»stS¦^ÿmm	ÿ],RackTypeID,rackID,e¶g
NvS¦^,e¶g
Nv^÷S,±{Q»st^÷S,ö«WS¦^,P[±{÷S,»st/UNID,SÅ±{÷S,S±{Q^÷S) 

											select ^÷S,¢[7bãN÷S,¤N'åeg,¢US÷S,[Å±{÷Sÿ¶g÷S	ÿ],±{Q»st;`pe,WY÷S,WYQ»st;`pe,»stID,[»st¦^ÿmm	ÿ],[»st½[¦^ÿmm	ÿ],[»stS¦^ÿmm	ÿ],RackTypeID,rackID,e¶g
NvS¦^,e¶g
Nv^÷S,±{Q»st^÷S,ö«WS¦^,P[±{÷S,»st/UNID,SÅ±{÷S,S±{Q^÷S from storage 

											where P[±{÷S=@P[±{÷S and »st/UNID=@»st/UNID2

											update glassOut2 set P[±{÷S=@°eP[±{÷S where »st/UNID=@»st/UNID2

											update Order$ set ±{pe=@±{pe where P[±{÷S=@P[±{÷S and »st/UNID=@»st/UNID2

										end

								fetch next from cursor_MyGlass_id2 into @»st/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»st;`pe,@P[±{Qg'YS¦^,@ò]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»st;`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»st;`pe,WY÷S,WYQ»st;`pe,»stID,[»st¦^ÿmm	ÿ],[»st½[¦^ÿmm	ÿ],[»stS¦^ÿmm	ÿ],RackTypeID,rackID,e¶g
NvS¦^,e¶g
Nv^÷S,±{Q»st^÷S,ö«WS¦^,P[±{÷S,»st/UNID,SÅ±{÷S,S±{Q^÷S) 

					select ^÷S,¢[7bãN÷S,¤N'åeg,¢US÷S,[Å±{÷Sÿ¶g÷S	ÿ],±{Q»st;`pe,WY÷S,WYQ»st;`pe,»stID,[»st¦^ÿmm	ÿ],[»st½[¦^ÿmm	ÿ],[»stS¦^ÿmm	ÿ],RackTypeID,rackID,e¶g
NvS¦^,e¶g
Nv^÷S,±{Q»st^÷S,ö«WS¦^,P[±{÷S,»st/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 @»st/UNID nvarchar(100)

	declare @^÷S nvarchar(100)

	set @^÷S=1

	declare cursor_GlassOutXu cursor for

	select »st/UNID from glassOut2 where P[±{÷S=@P[±{÷S order by S±{Q^÷S asc,[»st½[¦^ÿmm	ÿ] desc,»stID 

	open cursor_GlassOutXu

	fetch next from cursor_GlassOutXu into @»st/UNID

	while @@fetch_status=0

		begin						

			update glassOut2 set ±{Q»st^÷S=@^÷S where P[±{÷S=@P[±{÷S and »st/UNID=@»st/UNID

			set @^÷S=@^÷S+1

			fetch next from cursor_GlassOutXu into @»st/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)--ågwú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

			--_ËYMS@b	gvÅ±{÷S

			while @@fetch_status=0             

				begin

					--ÖNìNvÅ±{÷S ánNú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»st;`pe int

							declare @ò]0RGrpe int

							declare cursor_TaBox cursor for

							select P[±{÷S,±{Q»st;`pe,count(*) from storage where [Å±{÷Sÿ¶g÷S	ÿ]=@Å±{÷S group by P[±{÷S,±{Q»st;`pe

							open cursor_TaBox

							fetch next from cursor_TaBox into @P[±{÷S,@±{Q»st;`pe,@ò]0RGrpe

							while @@fetch_status=0

								begin

									exec proc_ChuPian @P[±{÷S

									------hQèSbS[b

									fetch next from cursor_TaBox into @P[±{÷S,@±{Q»st;`pe,@ò]0RGrpe

								end

							close cursor_TaBox                   

							deallocate cursor_TaBox	       --sQí8nhcursor_MyBox

							break   --úQGr

						end

					--bìNvÅ±{÷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)--ågwú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           --¡{úQGrhP[±{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

