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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


/****** Objeto:  procedimiento almacenado ºdbo.REG_VENTAS_RESUMIDO_PROC    fecha de la secuencia de comandos: 27/03/2012 11:48:51 a.m. ******/


/****** Objeto:  procedimiento almacenado dbo.REG_VENTAS_RESUMIDO_PROC    fecha de la secuencia de comandos: 06/01/2011 02:55:24 p.m. ******/


CREATE PROC REG_VENTAS_RESUMIDO_PROC
(
 @CORRNBR	INT=55555,
 @FECHAINI	CHAR(15)='20110901',
 @FECHAFIN	CHAR(15)='20110930',
 @MONEDA	UD_CURYID='PEN',
 @TODOS_DOC	TINYINT=1,	-- (1) TODOS LOD DOC EN @MONEDA, (0) SOLO LOS DOC EN @MONEDA
 @ALMACENES	VARCHAR(255)='',-- CADENA DE ALMACENES
 @TODOS_ALM	TINYINT=1,--TODOS LOS ALMACENES
 @CRITERIOS	VARCHAR(255)='', --solo los elementos que cumplan la condicion
 @TODOS_CON	TINYINT=1 --TODOS LOS elementos del tipo de condicion
)
AS
BEGIN


   DECLARE  @DATFECHAINI SMALLDATETIME
   DECLARE  @DATFECHAFIN SMALLDATETIME
      
   DECLARE  @CODTICKET  CHAR(2)   
   SET @CODTICKET=(SELECT DOCTYPEIDTICKET FROM OP_SETUP)

--  SET @FECHAINI='20021201'
--  SET @FECHAFIN='20021218'

TRUNCATE TABLE TMP_REGVENTAS1
TRUNCATE TABLE TMP_REGVENTAS2
       
        SET @DATFECHAINI=CAST(RTRIM(LTRIM(@FECHAINI)) AS SMALLDATETIME)
        SET @DATFECHAFIN=CAST(RTRIM(LTRIM(@FECHAFIN)) AS SMALLDATETIME)
         
        

	DECLARE @CADENA	VARCHAR(8000)
	DECLARE @MONEDABASE UD_CURYID	
	
	SET @MONEDABASE=(SELECT CURYORIG FROM SETUP)        --OBTENGO EL CODIGO DE LA MONEDA BASE
  

	IF (@MONEDA=@MONEDABASE) 
		BEGIN
			SET @CADENA="SELECT " + CAST(@CORRNBR AS CHAR(10)) + ",DOCTYPEID,DESCR,NBRDOCUMENT,SITEID,NAME,CAST(CONVERT(CHAR(10),DATEDOC,103) AS SMALLDATETIME) AS DATEDOC,DATEPROCESALES,TAXREGNBR,CUSTNAME,VAL_AFEC,VAL_NAFEC,VAL_IGV,TOTAL,SALESPOINT,SERIALNUMBER,DOCSTATUSID " +
			    	    "FROM VW_REGISTRO_VENTAS_PEN " +
	        	            "WHERE DATEPROCESALES BETWEEN '" + RTRIM(LTRIM(CAST(@DATFECHAINI AS CHAR(100)))) + "' AND '" + RTRIM(LTRIM(CAST(@DATFECHAFIN AS CHAR(100)))) + "'"
		END
	ELSE
		BEGIN
			SET @CADENA="SELECT " + CAST(@CORRNBR AS CHAR(10)) + ",DOCTYPEID,DESCR,NBRDOCUMENT,SITEID,NAME,CAST(CONVERT(CHAR(10),DATEDOC,103) AS SMALLDATETIME) AS DATEDOC,DATEPROCESALES,TAXREGNBR,CUSTNAME,VAL_AFEC,VAL_NAFEC,VAL_IGV,TOTAL,SALESPOINT,SERIALNUMBER,DOCSTATUSID " +
			    	    "FROM VW_REGISTRO_VENTAS_USD " +
	        	            "WHERE DATEPROCESALES BETWEEN '" + RTRIM(LTRIM(CAST(@DATFECHAINI AS CHAR(100)))) + "' AND '" + RTRIM(LTRIM(CAST(@DATFECHAFIN AS CHAR(100)))) + "'"
		END

	
	SET @CADENA=@CADENA + " AND (LEFT(NBRDOCUMENT,LEN(NBRDOCUMENT)-7) != 761)"

	IF (@TODOS_ALM=0)
              	SET @CADENA=@CADENA + " AND SITEID IN (" + @ALMACENES + ") " 


	IF (@TODOS_CON=0)
		SET @CADENA=@CADENA + " AND DOCTYPEID IN (" + @CRITERIOS + ") "

	SET @CADENA=@CADENA + " ORDER BY SITEID,SERIALNUMBER,DOCTYPEID,DATEDOC,NBRDOCUMENT"
	
	INSERT INTO TMP_REGVENTAS1 EXEC (@CADENA)

	-- VARIABLES PARA EL CURSOR
	DECLARE	@DOCTYPEID	UD_DOCTYPEID
	DECLARE	@DESCR		UD_DESCRIPCION
	DECLARE @NBRDOCUMENT	UD_CORRELATIVOS
	DECLARE @SITEID		UD_IDSITE
	DECLARE	@NAME		UD_DESCRIPCION
	DECLARE @DATEDOC	UD_FECHA
	DECLARE	@DATEPROCESALES	UD_FECHA
	DEClARE	@TAXREGNBR	UD_CODCLIENT
	DECLARE	@CUSTNAME	UD_NOMBRE
	DECLARE	@VAL_AFEC	UD_MONTO
	DECLARE	@VAL_NAFEC	UD_MONTO
	DECLARE	@VAL_IGV	UD_MONTO
	DECLARE	@TOTAL		UD_MONTO
	DECLARE	@SALESPOINT	UD_PTOVTA
	DECLARE	@SERIALNUMBER	CHAR(16)
	DECLARE @DOCSTATUSID	CHAR(2)

	-- VARIABLES PARA EL REPORTE
	DECLARE	@DOCTYPEID_T	UD_DOCTYPEID
	DECLARE	@DESCR_T	UD_DESCRIPCION
	DECLARE @NBRDOCUMENT_INI	UD_CORRELATIVOS
	DECLARE @NBRDOCUMENT_FIN	UD_CORRELATIVOS
	DECLARE @SITEID_T	UD_IDSITE
	DECLARE	@NAME_T		UD_DESCRIPCION
	DECLARE @DATEDOC_T	UD_FECHA
	DECLARE	@DATEPROCESALES_T	UD_FECHA
	DEClARE	@TAXREGNBR_T	UD_CODCLIENT
	DECLARE	@CUSTNAME_T	UD_NOMBRE
	DECLARE	@VAL_AFEC_T	UD_MONTO
	DECLARE	@VAL_NAFEC_T	UD_MONTO
	DECLARE	@VAL_IGV_T	UD_MONTO
	DECLARE	@TOTAL_T	UD_MONTO
	DECLARE	@SALESPOINT_T	UD_PTOVTA
	DECLARE	@SERIALNUMBER_T	CHAR(16)
	DECLARE @DOCSTATUSID_T	CHAR(2)

	DECLARE REG_VENTAS CURSOR

	FOR SELECT 	DOCTYPEID,DESCR,NBRDOCUMENT,SITEID,NAME,DATEDOC,DATEPROCESALES,TAXREGNBR,
            		CUSTNAME,VAL_AFEC,VAL_NAFEC,VAL_IGV,TOTAL,SALESPOINT,SERIALNUMBER,DOCSTATUSID FROM TMP_REGVENTAS1 
	    WHERE 	CORRNBR=@CORRNBR AND DOCTYPEID=@CODTICKET 
            --ORDER BY    SITEID,SERIALNUMBER,DOCTYPEID,DATEDOC,NBRDOCUMENT
	PRINT @@ROWCOUNT
	OPEN REG_VENTAS
	FETCH NEXT FROM REG_VENTAS INTO @DOCTYPEID,@DESCR,@NBRDOCUMENT,@SITEID,@NAME,@DATEDOC,@DATEPROCESALES,@TAXREGNBR,
		            		@CUSTNAME,@VAL_AFEC,@VAL_NAFEC,@VAL_IGV,@TOTAL,@SALESPOINT,@SERIALNUMBER,@DOCSTATUSID

	SET @DOCTYPEID_T=@DOCTYPEID
	SET @DESCR_T=@DESCR
	SET @NBRDOCUMENT_INI=@NBRDOCUMENT
	SET @NBRDOCUMENT_FIN=@NBRDOCUMENT
	SET @SITEID_T=@SITEID
	SET @NAME_T=@NAME
	SET @DATEDOC_T=@DATEDOC
	SET @DATEPROCESALES_T=@DATEPROCESALES
	SET @TAXREGNBR_T=@TAXREGNBR
	SET @CUSTNAME_T=@CUSTNAME
	SET @VAL_AFEC_T=0
	SET @VAL_NAFEC_T=0
	SET @VAL_IGV_T=0
	SET @TOTAL_T=0
	SET @SALESPOINT_T=@SALESPOINT
	SET @SERIALNUMBER_T=@SERIALNUMBER
	--SET @DOCSTATUSID_T=@DOCSTATUSID

	DECLARE @CONT	INTEGER
	DECLARE @G	INTEGER
	SET @G=0
	SET @CONT=0

	
	--SOLO PARA LOS TICKET (BOLETA O FACTURA)
	WHILE @@FETCH_STATUS=0
        BEGIN   
	
	  	IF(@SITEID<>@SITEID_T OR @SALESPOINT<>@SALESPOINT_T OR @DATEDOC<>@DATEDOC_T)
	  	BEGIN
			IF(@G=1) -- SI HAY UN GRUPO
			BEGIN
				INSERT 	INTO TMP_REGVENTAS2(CORRNBR,DOCTYPEID,DESCR,NBRDOCUMENTINI,NBRDOCUMENTFIN,SITEID,NAME,DATEDOC,
						DATEPROCESALES,TAXREGNBR,CUSTNAME,VAL_AFEC,VAL_NAFEC,VAL_IGV,TOTAL,SALESPOINT,SERIALNUMBER)
				VALUES(@CORRNBR,@DOCTYPEID_T,@DESCR_T,@NBRDOCUMENT_INI,@NBRDOCUMENT_FIN,@SITEID_T,@NAME_T,@DATEDOC_T,
				       		@DATEPROCESALES_T,@TAXREGNBR_T,@CUSTNAME_T,@VAL_AFEC_T,@VAL_NAFEC_T,@VAL_IGV_T,@TOTAL_T,@SALESPOINT_T,@SERIALNUMBER_T)

						SET @G=0

			END 

			SET @DOCTYPEID_T=@DOCTYPEID
			SET @DESCR_T=@DESCR
			SET @NBRDOCUMENT_INI=@NBRDOCUMENT
			SET @NBRDOCUMENT_FIN=@NBRDOCUMENT
			SET @SITEID_T=@SITEID
			SET @NAME_T=@NAME
			SET @DATEDOC_T=@DATEDOC
			SET @DATEPROCESALES_T=@DATEPROCESALES
			SET @TAXREGNBR_T=@TAXREGNBR
			SET @CUSTNAME_T=@CUSTNAME
			SET @VAL_AFEC_T=0
			SET @VAL_NAFEC_T=0
			SET @VAL_IGV_T=0
			SET @TOTAL_T=0
			SET @SALESPOINT_T=@SALESPOINT
			SET @SERIALNUMBER_T=@SERIALNUMBER

		END


		IF(LEN(LTRIM(RTRIM(@TAXREGNBR)))>0 OR @DOCSTATUSID='AN')
		BEGIN		
		IF(@G=1) -- SI HAY UN GRUPO
			BEGIN
				INSERT 	INTO TMP_REGVENTAS2(CORRNBR,DOCTYPEID,DESCR,NBRDOCUMENTINI,NBRDOCUMENTFIN,SITEID,NAME,DATEDOC,
						DATEPROCESALES,TAXREGNBR,CUSTNAME,VAL_AFEC,VAL_NAFEC,VAL_IGV,TOTAL,SALESPOINT,SERIALNUMBER)
				VALUES(@CORRNBR,@DOCTYPEID_T,@DESCR_T,@NBRDOCUMENT_INI,@NBRDOCUMENT_FIN,@SITEID_T,@NAME_T,@DATEDOC_T,
				       		@DATEPROCESALES_T,@TAXREGNBR_T,@CUSTNAME_T,@VAL_AFEC_T,@VAL_NAFEC_T,@VAL_IGV_T,@TOTAL_T,@SALESPOINT_T,@SERIALNUMBER_T)

						SET @G=0

			END 

			INSERT INTO TMP_REGVENTAS2(CORRNBR,DOCTYPEID,DESCR,NBRDOCUMENTINI,NBRDOCUMENTFIN,SITEID,NAME,DATEDOC,
						   DATEPROCESALES,TAXREGNBR,CUSTNAME,VAL_AFEC,VAL_NAFEC,VAL_IGV,TOTAL,SALESPOINT,SERIALNUMBER)
			VALUES(@CORRNBR,@DOCTYPEID,@DESCR,@NBRDOCUMENT,NULL,@SITEID,@NAME,@DATEDOC,
			       @DATEPROCESALES,@TAXREGNBR,@CUSTNAME,@VAL_AFEC,@VAL_NAFEC,@VAL_IGV,@TOTAL,@SALESPOINT,
			       @SERIALNUMBER)
			
			SET @CONT=0

			SET @VAL_AFEC_T=0
			SET @VAL_NAFEC_T=0
			SET @VAL_IGV_T=0
			SET @TOTAL_T=0
		END
		ELSE
		BEGIN 
			
			

			SET @DOCTYPEID_T=@DOCTYPEID
			SET @DESCR_T=@DESCR
			IF (@CONT=0)
				SET @NBRDOCUMENT_INI=@NBRDOCUMENT

			SET @NBRDOCUMENT_FIN=@NBRDOCUMENT
			SET @SITEID_T=@SITEID
			SET @NAME_T=@NAME
			SET @DATEDOC_T=@DATEDOC
			SET @DATEPROCESALES_T=@DATEPROCESALES
			SET @TAXREGNBR_T=@TAXREGNBR
			SET @CUSTNAME_T=@CUSTNAME
			SET @VAL_AFEC_T=@VAL_AFEC_T + @VAL_AFEC
			SET @VAL_NAFEC_T=@VAL_NAFEC_T + @VAL_NAFEC 
			SET @VAL_IGV_T=@VAL_IGV_T + @VAL_IGV
			SET @TOTAL_T=@TOTAL_T + @TOTAL
			SET @SALESPOINT_T=@SALESPOINT

			SET @SERIALNUMBER_T=@SERIALNUMBER

			SET @G=1
			SET @CONT=1
		END 



	FETCH NEXT FROM REG_VENTAS INTO @DOCTYPEID,@DESCR,@NBRDOCUMENT,@SITEID,@NAME,@DATEDOC,@DATEPROCESALES,@TAXREGNBR,
		            		@CUSTNAME,@VAL_AFEC,@VAL_NAFEC,@VAL_IGV,@TOTAL,@SALESPOINT,@SERIALNUMBER,@DOCSTATUSID
        
        END /* Termina while */

		IF(@G=1) -- SI HAY UN GRUPO
			BEGIN
			INSERT 	INTO TMP_REGVENTAS2(CORRNBR,DOCTYPEID,DESCR,NBRDOCUMENTINI,NBRDOCUMENTFIN,SITEID,NAME,DATEDOC,
						DATEPROCESALES,TAXREGNBR,CUSTNAME,VAL_AFEC,VAL_NAFEC,VAL_IGV,TOTAL,SALESPOINT,SERIALNUMBER)
				VALUES(@CORRNBR,@DOCTYPEID_T,@DESCR_T,@NBRDOCUMENT_INI,@NBRDOCUMENT_FIN,@SITEID_T,@NAME_T,@DATEDOC_T,
				     		@DATEPROCESALES_T,@TAXREGNBR_T,@CUSTNAME_T,@VAL_AFEC_T,@VAL_NAFEC_T,@VAL_IGV_T,@TOTAL_T,@SALESPOINT_T,@SERIALNUMBER_T)
			END
	--SELECT @DOCTYPEID_T
	CLOSE REG_VENTAS
	DEALLOCATE REG_VENTAS

	--INSERTAMOS TODOS LOS DEMAS DOCUMENTOS

	INSERT 	INTO 	TMP_REGVENTAS2(CORRNBR,DOCTYPEID,DESCR,NBRDOCUMENTINI,NBRDOCUMENTFIN,SITEID,NAME,DATEDOC,DATEPROCESALES,TAXREGNBR,CUSTNAME,VAL_AFEC,VAL_NAFEC,
   		VAL_IGV,TOTAL,SALESPOINT,SERIALNUMBER)
	SELECT 	CORRNBR,DOCTYPEID,DESCR,NBRDOCUMENT,NULL,SITEID,NAME,DATEDOC,DATEPROCESALES,TAXREGNBR,CUSTNAME,VAL_AFEC,VAL_NAFEC,
   		VAL_IGV,TOTAL,SALESPOINT,SERIALNUMBER
		FROM TMP_REGVENTAS1
		WHERE CORRNBR=@CORRNBR AND DOCTYPEID<>@CODTICKET

	-- SELECT PARA EL REPORTE 
	SELECT 	CORRNBR,DOCTYPEID,DESCR,CONVERT(VARCHAR(10),NBRDOCUMENTINI) AS NBRDOCUMENTINI,CASE (NBRDOCUMENTFIN) WHEN NBRDOCUMENTINI THEN NULL ELSE CONVERT(VARCHAR(10),NBRDOCUMENTFIN) END AS NBRDOCUMENTFIN,SITEID,NAME,DATEDOC,DATEPROCESALES,TAXREGNBR,CUSTNAME,
		VAL_AFEC,VAL_NAFEC,VAL_IGV,TOTAL,SALESPOINT,SERIALNUMBER
	FROM TMP_REGVENTAS2 
	WHERE CORRNBR=@CORRNBR

	RETURN(0)
END 


/*SELECT 	CORRNBR,DOCTYPEID,DESCR,NBRDOCUMENTINI,CASE (NBRDOCUMENTFIN) WHEN NBRDOCUMENTINI THEN NULL ELSE NBRDOCUMENTFIN END AS NBRDOCUMENTFIN,SITEID,NAME,DATEDOC,DATEPROCESALES,TAXREGNBR,CUSTNAME,
	VAL_AFEC,VAL_NAFEC,VAL_IGV,TOTAL,SALESPOINT,SERIALNUMBER
FROM TMP_REGVENTAS2

SELECT 	CORRNBR,DOCTYPEID,DESCR,NBRDOCUMENTINI,NBRDOCUMENTFIN,SITEID,NAME,DATEDOC,DATEPROCESALES,TAXREGNBR,CUSTNAME,
	VAL_AFEC,VAL_NAFEC,VAL_IGV,TOTAL,SALESPOINT,SERIALNUMBER
FROM TMP_REGVENTAS2

DELETE FROM TMP_REGVENTAS1
DELETE FROM TMP_REGVENTAS2

SELECT * FROM TMP_REGVENTAS1
SELECT * FROM OP_SALES 
SELECT * FROM TMP_REGVENTAS2 ORDER BY SITEID,SALESPOINT,NBRDOCUMENTINI,DATEDOC

DATOS_REG_VENTAS_RESUMIDO_PROC 2,'20030101','20030101','PEN'


SELECT * FROM TMP_REGVENTAS2
SELECT SUM(VAL_AFEC) AS VAL_AFEC  FROM TMP_REGVENTAS1 WHERE DOCTYPEID='12'

SELECT DISTINCT SITEID,SALESPOINT,CAST(CONVERT(CHAR(10),DATEDOC,103) AS SMALLDATETIME) AS DATEDOC FROM OP_SALES




SELECT * FROM TMP_REGVENTAS1 WHERE 
SITEID='57' AND SALESPOINT='PTOVTA02' AND DATEDOC='20030106' AND (DOCSTATUSID='AN' OR LEN(LTRIM(RTRIM(TAXREGNBR)))>0)


SELECT SUM(NBRDOCUMENT AS MROINI FROM TMP_REGVENTAS1 WHE


SELECT * FROM OP_SALES
DELETE FROM OP_SALESDET WHERE NBRDOCUMENT=0
DELETE FROM OP_SALES WHERE NBRDOCUMENT=0*/


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

