-----------------------------------------------------------------------
-- ATENCIÓN: ESTOS SCRIPTS ESTAN COPIADOS DE:						 --
-- create_tables.sql , create_triggers.sql							 --
-- Cualquiera de ellos que cambie, se debe actualizar este archivo	 --
-----------------------------------------------------------------------

PRINT '*** POST SNAPSHOT SCRIPT.' 
--PRINT  'HOST:' + HOST_NAME(). Esto no anda.
PRINT '*** Adding USER-DEFINED TYPES'
IF  NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'FILTER_ID' AND ss.name = N'dbo')
BEGIN
CREATE TYPE [dbo].[FILTER_ID] AS TABLE(
	[ID] [bigint] NOT NULL,
	PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
END

PRINT '*** Adding tables not included in Replication'

IF  NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SKU_Empresa_Ubicacion]') AND type in (N'U'))
BEGIN
	CREATE TABLE SKU_Empresa_Ubicacion 
		( 
		 ID_SKU_Empresa_Ubicacion BIGINT NOT NULL IDENTITY(1,1), 
		 ID_Deposito_Ubicacion BIGINT NOT NULL , 
		 Cantidad FLOAT  NOT NULL , 
		 ID_SKU_Empresa BIGINT NOT NULL , 
		 Usr_Creacion BIGINT NOT NULL DEFAULT 1, 
		 Fecha_Creacion DATETIME  NOT NULL DEFAULT GETDATE(), 
		 Usr_Modificacion BIGINT, 
		 Fecha_Modificacion DATETIME , 
		 Dato1 FLOAT , 
		 Dato2 FLOAT , 
		 Dato3 FLOAT , 
		 Dato4 NVARCHAR (500) , 
		 Dato5 NVARCHAR (500) , 
		 Dato6 NVARCHAR (500) , 
		 Row_Version ROWVERSION 
		) 
	;
	ALTER TABLE SKU_Empresa_Ubicacion 
		ADD CONSTRAINT SKU_Empresa_Ubicacion_PK PRIMARY KEY ( ID_SKU_Empresa_Ubicacion ) ;
	ALTER TABLE SKU_Empresa_Ubicacion 
		ADD CONSTRAINT Dep_Ubic_Cod_Barr_Dep_Ubic_FK FOREIGN KEY 
		( 
			ID_Deposito_Ubicacion
		) 
		REFERENCES Deposito_Ubicacion 
		( 
			ID_Deposito_Ubicacion
		) 
		;
	ALTER TABLE SKU_Empresa_Ubicacion 
		ADD CONSTRAINT SKU_Emp_Ubic_SKU_Emp_FK FOREIGN KEY 
		( 
		 ID_SKU_Empresa
		) 
		REFERENCES SKU_Empresa 
		( 
		 ID_SKU_Empresa
		) 
	;
END
GO


PRINT '*** END Adding tables not included in Replication'
GO

PRINT '*** CREATING Triggers not included in Replication'
GO

IF  EXISTS (SELECT 1 FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[UPDATE_Resumen_Stock_ACTUAL]'))
DROP TRIGGER [dbo].[UPDATE_Resumen_Stock_ACTUAL]
GO


-- =============================================
-- Author:		<Author,,Daniel Kacowicz>
-- Create date: <Create Date,,02/02/2010>
-- Description:	<Description,,Actualiza el Resumen de Stock por sucursal al modificar el Sku Empresa Ubicacion>
-- =============================================

	CREATE TRIGGER [dbo].[UPDATE_Resumen_Stock_ACTUAL] 
	   ON  [dbo].[SKU_Empresa_Ubicacion]
	   AFTER INSERT,UPDATE
	AS 
	BEGIN
		-- SET NOCOUNT ON added to prevent extra result sets from
		-- interfering with SELECT statements.
		SET NOCOUNT ON;
	if dbo.IS_BRANCH_DB()=1  return 

	DECLARE @ICURSOR CURSOR; --Imita el FOR EACH ROW  
	Declare @ID_SKU_EMP numeric
	Declare @ID_SUCURSAL numeric
	Declare @CANT_NUEVA numeric
	Declare @ID_UBICACION numeric
	declare @CANT_ACTUAL numeric 

	SET @ICURSOR =CURSOR FOR Select ID_SKU_EMPRESA,ID_Deposito_Ubicacion,CANTIDAD From Inserted ;   
	OPEN @ICURSOR  
   
		 FETCH NEXT FROM @ICURSOR INTO @ID_SKU_EMP ,@ID_UBICACION ,@CANT_NUEVA ;
		 WHILE (@@FETCH_STATUS = 0)   
		 BEGIN  
			Select @ID_SUCURSAL =  (select  Deposito.ID_Sucursal 
							FROM	Deposito_Ubicacion 
							INNER JOIN	Deposito ON  Deposito_Ubicacion.ID_Deposito = Deposito.ID_Deposito
							where ID_Deposito_Ubicacion=@ID_UBICACION)
			select @CANT_ACTUAL = (select CANT_ACTUAL from Resumen_Stock_ACTUAL 
							where ID_SKU_EMPRESA=@ID_SKU_EMP AND ID_SUCURSAL=@ID_SUCURSAL);
		
			if (@CANT_ACTUAL IS NULL) 
			 BEGIN
				insert into Resumen_Stock_ACTUAL (ID_SKU_EMPRESA,CANT_ACTUAL,ID_SUCURSAL)
										  values (@ID_SKU_EMP,	@CANT_NUEVA ,@ID_SUCURSAL);
			 END
			ELSE
			BEGIN
				Declare @SUMA numeric
				SELECT     @SUMA =SUM(SKU_Empresa_Ubicacion.Cantidad) 
				FROM         SKU_Empresa_Ubicacion INNER JOIN
									  Deposito_Ubicacion ON SKU_Empresa_Ubicacion.ID_Deposito_Ubicacion = Deposito_Ubicacion.ID_Deposito_Ubicacion INNER JOIN
									  Deposito ON Deposito_Ubicacion.ID_Deposito = Deposito.ID_Deposito
				WHERE (ID_SKU_EMPRESA=@ID_SKU_EMP AND ID_SUCURSAL=@ID_SUCURSAL);
			
				update Resumen_Stock_ACTUAL set CANT_ACTUAL= @SUMA
				WHERE (ID_SKU_EMPRESA=@ID_SKU_EMP AND ID_SUCURSAL=@ID_SUCURSAL);
			END
		
		   FETCH NEXT FROM @ICURSOR INTO @ID_SKU_EMP ,@ID_UBICACION ,@CANT_NUEVA ;  

		END --End While  
	 CLOSE @ICURSOR  
	 DEALLOCATE @ICURSOR;  

	END
GO
PRINT '*** END CREATING Triggers not included in Replication'
GO

PRINT '*** Creating CATEGORIAS TABLES '
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Create_SKU_Categorias_Table]') AND type in (N'P', N'PC'))
begin
	exec Create_SKU_Categorias_Table;
	exec Create_Producto_Categorias_Table;
end
PRINT '*** DONE Creating CATEGORIAS TABLES '
GO

PRINT '*** Regenerating Stock'
exec Regenerate_Stock
PRINT '*** Done Regenerating Stock'
