use bd_spdv
drop procedure ApiBuscarTest
drop procedure ApiBuscarTomaFisica
drop procedure ApiBuscarTomaFisicaDetalle
drop procedure ApiBuscarTomaFisicaMae
drop procedure ApiBuscarTomaFisicaPost
drop procedure ApiBuscarTomaFisicaTotal
drop procedure ApiProcesarToma
go
--**********************************************************************
CREATE PROCEDURE [dbo].[ApiBuscarTest]

AS
	SELECT [Nombre]
	FROM [dbo].[PARAMETROS]

GO

--**********************************************************************

CREATE PROCEDURE [dbo].[ApiBuscarTomaFisica]
(
    @CodigoBarra  VARCHAR(20),
	@NumeroPagina INT,
	@Observacion  VARCHAR(500))

AS

DECLARE @ExisteArticulo           INT
DECLARE @ExisteArticuloTomaFisica INT
DECLARE @ExisteToma				  INT

	--*************************************
	--***Busca si existe codigo de barra en inventario
	--*************************************
	 SELECT @ExisteArticulo = ISNULL(COUNT(*),0) 
	 FROM INVENTARIO
	 WHERE CodigoBarra = @CodigoBarra


	IF (@ExisteArticulo > 0)
		BEGIN

	--************************************
	--***Busca si existe Pagina en toma
	--************************************
		SELECT @ExisteToma	= ISNULL(COUNT(NumeroPagina) ,0)
		FROM TOMAFISICA1
		WHERE NumeroPagina = @NumeroPagina

		IF (@ExisteToma > 0)
			BEGIN
	--************************************
	--***Actualiza o inserta maestro toma fisica
	--************************************
				UPDATE TOMAFISICA1
				SET Observacion = @Observacion
				WHERE NumeroPagina = @NumeroPagina

			END
		ELSE
			BEGIN
				INSERT INTO TOMAFISICA1
				VALUES(@NumeroPagina,GETDATE(),@Observacion,'INVE',1)
			END
	--*************************************
	--***Busca si existe art. en tomafisica
	--*************************************
			SELECT @ExisteArticuloTomaFisica = ISNULL((NumeroPagina),0) 
			FROM MOVTOMAFISICA1
			WHERE CodigoBarra = @CodigoBarra 

	--*************************************
	--***Busca si existe art. en tomafisica
	--*************************************
				IF (@ExisteArticuloTomaFisica > 0)
					BEGIN
						UPDATE MOVTOMAFISICA1
						SET Cantidad = Cantidad + 1
						WHERE CodigoBarra = @CodigoBarra AND NumeroPagina = @NumeroPagina
					END
				ELSE
					BEGIN
					    DECLARE @Id INT

					    SELECT @Id = ISNULL(MAX(Item),0) + 1 
					    FROM MOVTOMAFISICA1 
						WHERE NumeroPagina = @NumeroPagina

						INSERT INTO MOVTOMAFISICA1
						VALUES (@NumeroPagina,@Id, @CodigoBarra,1,CAST(GETDATE() AS DATETIME) )
					END
					 
	END
	
	GO
--**********************************************************************
	
	
CREATE PROCEDURE [dbo].[ApiBuscarTomaFisicaDetalle]
(
 @NumeroPagina  INT
)
AS
SELECT        MOVTOMAFISICA1.NumeroPagina,  INVENTARIO.Nombre, MOVTOMAFISICA1.Item, MOVTOMAFISICA1.CodigoBarra, MOVTOMAFISICA1.Cantidad
FROM            MOVTOMAFISICA1 INNER JOIN
                         INVENTARIO ON MOVTOMAFISICA1.CodigoBarra = INVENTARIO.CodigoBarra
WHERE        (MOVTOMAFISICA1.NumeroPagina =@NumeroPagina)
ORDER BY Item DESC

GO

--**********************************************************************

CREATE PROCEDURE [dbo].[ApiBuscarTomaFisicaMae]

AS

	SELECT [NumeroPagina],[Fecha],[Observacion]
	FROM [dbo].[TOMAFISICA1]
	WHERE [Usuario] = 'INVE' AND [Status] = 0 
	ORDER BY NumeroPagina DESC,Fecha DESC

	
	GO

--**********************************************************************



CREATE PROCEDURE [dbo].[ApiBuscarTomaFisicaPost]
(
@NumeroPagina INT ,
 @CodigoBarra  VARCHAR(20)

)
AS

	SELECT        MOVTOMAFISICA1.Cantidad, INVENTARIO.Nombre
    FROM          TOMAFISICA1 INNER JOIN
                  MOVTOMAFISICA1 ON TOMAFISICA1.NumeroPagina = MOVTOMAFISICA1.NumeroPagina INNER JOIN
                  INVENTARIO ON MOVTOMAFISICA1.CodigoBarra = INVENTARIO.CodigoBarra
	WHERE         MOVTOMAFISICA1.NumeroPagina  = @NumeroPagina and MOVTOMAFISICA1.CodigoBarra = @CodigoBarra
	
	GO
	
--**********************************************************************

CREATE PROCEDURE [dbo].[ApiProcesarToma]
(
    @CodigoBarra  VARCHAR(20),
	@NumeroPagina INT,
	@Observacion  VARCHAR(500)
)

AS

DECLARE @ExisteArticulo           INT
DECLARE @ExisteArticuloTomaFisica INT
DECLARE @ExisteToma				  INT

	--*************************************
	--***Busca si existe codigo de barra en inventario
	--*************************************
	 SELECT @ExisteArticulo = ISNULL(COUNT(CodigoBarra),0) 
	 FROM INVENTARIO
	 WHERE CodigoBarra = @CodigoBarra


		IF (@ExisteArticulo > 0)
			BEGIN

			--************************************
			--***Busca si existe Pagina en toma
			--************************************
			SELECT @ExisteToma	= ISNULL(COUNT(NumeroPagina) ,0)
			FROM TOMAFISICA1
			WHERE NumeroPagina = @NumeroPagina

					IF (@ExisteToma > 0)
						BEGIN
							--************************************
							--***Actualiza o inserta maestro toma fisica
							--************************************
							UPDATE TOMAFISICA1
							SET Observacion = @Observacion
							WHERE NumeroPagina = @NumeroPagina

						END
					ELSE
						BEGIN
							INSERT INTO TOMAFISICA1
							VALUES(@NumeroPagina,GETDATE(),@Observacion,'INVE',0)
						END

			--*************************************
			--***Busca si existe art. en tomafisica
			--*************************************
			SELECT @ExisteArticuloTomaFisica = ISNULL(COUNT(NumeroPagina),0) 
			FROM MOVTOMAFISICA1
			WHERE CodigoBarra = @CodigoBarra AND NumeroPagina = @NumeroPagina


				--*************************************
				--***Busca si existe art. en tomafisica
				--*************************************
						IF (@ExisteArticuloTomaFisica > 0)
							BEGIN
								UPDATE MOVTOMAFISICA1
								SET Cantidad = Cantidad + 1
								WHERE CodigoBarra = @CodigoBarra AND NumeroPagina = @NumeroPagina
							END
						ELSE
							BEGIN
								DECLARE @Id INT

								SELECT @Id = ISNULL(MAX(Item),0) + 1 
								FROM MOVTOMAFISICA1 
								WHERE NumeroPagina = @NumeroPagina

								INSERT INTO MOVTOMAFISICA1
								VALUES (@NumeroPagina,@Id, @CodigoBarra,1,CAST(GETDATE() AS DATETIME) )
							END
		END

GO

--**********************************************************************

CREATE PROCEDURE [dbo].[ApiBuscarTomaFisicaTotal]
(
@NumPagina    INT 
)
AS
	SELECT SUM(cantidad) AS Cantidad
	FROM [dbo].[MOVTOMAFISICA1]
	WHERE [NumeroPagina]  = @NumPagina



