
DROP PROC RESU_TIENDA_CIERREZ_PROC
GO
CREATE PROC RESU_TIENDA_CIERREZ_PROC
( 
  @DATEPROCE	UD_FECHA,
  @PTOVTA	UD_PTOVTA,
  @RESUMEN	INT
)
AS
BEGIN

	DECLARE @DOCTYPEID    UD_DOCTYPEID
	SELECT @DOCTYPEID=DOCTYPEIDTICKET FROM OP_SETUP

	IF (@RESUMEN=1) -- POR CATEGORIA
	BEGIN 
		SELECT I.CLASSID,CA.DESCR,SUM(D.TOTALPEN) AS TOTALPEN  
		FROM  ((OP_SALES AS C JOIN OP_SALESDET AS D
		ON C.DOCTYPEID=D.DOCTYPEID AND C.NBRDOCUMENT=D.NBRDOCUMENT AND C.SITEID=D.SITEID) JOIN IN_INVENTORY AS I
		ON D.INVTIDSKU=I.INVTIDSKU) JOIN IN_CATEGORY AS CA
		ON I.CLASSID=CA.CLASSID
        	WHERE C.DATEPROCESALES=@DATEPROCE AND C.SALESPOINT=@PTOVTA AND C.DOCTYPEID=@DOCTYPEID AND D.DOCSTATUSID<>'AN'
		GROUP BY I.CLASSID,CA.DESCR
		ORDER BY I.CLASSID
	END 

	IF (@RESUMEN=2) -- POR PRODUCTO
	BEGIN 
		SELECT D.INVTIDSKU,D.DESCRINVENTORY,SUM(D.TOTALPEN) AS TOTALPEN  
		FROM  OP_SALES AS C JOIN OP_SALESDET AS D
		ON C.DOCTYPEID=D.DOCTYPEID AND C.NBRDOCUMENT=D.NBRDOCUMENT AND C.SITEID=D.SITEID
        	WHERE C.DATEPROCESALES=@DATEPROCE AND C.SALESPOINT=@PTOVTA AND C.DOCTYPEID=@DOCTYPEID AND D.DOCSTATUSID<>'AN'
		GROUP BY INVTIDSKU,DESCRINVENTORY
		ORDER BY INVTIDSKU
	END

	IF (@RESUMEN=3) -- POR VENDEDOR
	BEGIN 
		SELECT C.SALESPERID,V.SALESPERNAME,SUM(D.TOTALPEN) AS TOTALPEN  
		FROM  (OP_SALES AS C JOIN OP_SALESDET AS D
		ON C.DOCTYPEID=D.DOCTYPEID AND C.NBRDOCUMENT=D.NBRDOCUMENT AND C.SITEID=D.SITEID) JOIN OP_SALESPERSON AS V
		ON C.SALESPERID=V.SALESPERID 
        	WHERE C.DATEPROCESALES=@DATEPROCE AND C.SALESPOINT=@PTOVTA AND C.DOCTYPEID=@DOCTYPEID AND D.DOCSTATUSID<>'AN'
		GROUP BY C.SALESPERID,V.SALESPERNAME
		ORDER BY C.SALESPERID
	END 

END 


RESU_TIENDA_CIERREZ_PROC '40100127','20030305','PTOVTA03',1
GO
RESU_TIENDA_CIERREZ_PROC '40100127','20030305','PTOVTA03',2

SELECT * FROM OP_SALESDET WHERE DATEPROCESALES='20030303' 

DROP PROC CABECERA_TIENDA_CIERREZ_PROC
GO
CREATE PROC CABECERA_TIENDA_CIERREZ_PROC
( 
  @DATEPROCE	UD_FECHA,
  @PTOVTA	UD_PTOVTA
)
AS
BEGIN
   -- Declaracion de Variables 
   DECLARE @DATEPROCESALES     SMALLDATETIME
   DECLARE @DOCTYPEIDTICKET    UD_DOCTYPEID   
   DECLARE @SERIALNUMBER       CHAR(16) 
   DECLARE @FECHASERVIDOR      SMALLDATETIME
   DECLARE @TIPODETIPODECAMBIO UD_CURYTYPEID
   DECLARE @MONEDACAJA         UD_CURYID
   DECLARE @MONEDAINVERSA      UD_CURYID
   DECLARE @MONTOTIPOCAMBIO    UD_CURYRATE
   -- *************************************************************************************
   SELECT @DOCTYPEIDTICKET=DOCTYPEIDTICKET  from OP_SETUP
   SELECT @DATEPROCESALES=DATEPROCESALES FROM OP_SETUP  
   SELECT @SERIALNUMBER=SERIALNUMBER,@MONEDACAJA=CURYID FROM OP_salespointsetting  WHERE SALESPOINT=@PTOVTA
   SELECT @FECHASERVIDOR=GETDATE()
   SELECT @MONEDAINVERSA=CURYID FROM CURRENCY WHERE CURYID NOT IN (SELECT @MONEDACAJA)

   --  Obtener el Monto del Tipo de Cambio de la Moneda de la Caja a la Moneda Extranjera   
   
   SELECT @TIPODETIPODECAMBIO=CURYTYPEIDSALES FROM SETUP 
   SELECT @MONTOTIPOCAMBIO=RATE FROM CURYRATE WHERE CURYTYPEID=@TIPODETIPODECAMBIO AND fromcuryid=@MONEDACAJA
                                                    AND TOCURYID=@MONEDAINVERSA AND CURYDATE= 
                                                    (SELECT MAX(CURYDATE)FROM CURYRATE WHERE CURYDATE<=@DATEPROCESALES AND CURYTYPEID=@TIPODETIPODECAMBIO)      
                                
   --  Parte de la Cabecera     
   --  Variables para el Manejo de la Cabecera 
   
   DECLARE @NROTRANSAC               INTEGER
   DECLARE @TICKECTBOLEINI           UD_CORRELATIVOS
   DECLARE @TICKECTFACTINI           UD_CORRELATIVOS
   DECLARE @TICKECTBOLEFIN           UD_CORRELATIVOS
   DECLARE @TICKECTFACTFIN           UD_CORRELATIVOS
   DECLARE @TOTALTICKETBOLETASOL     UD_MONTO  
   DECLARE @TOTALTICKETBOLETADOL     UD_MONTO  
   DECLARE @TOTALTICKETFACTURASOL    UD_MONTO       
   DECLARE @TOTALTICKETFACTURADOL    UD_MONTO              
   

   DECLARE @VALORVENTASOLES     UD_MONTO  
   DECLARE @VALORVENTADOLARES   UD_MONTO       
   DECLARE @NOAFECTOSOLES       UD_MONTO          
   DECLARE @NOAFECTODOLARES     UD_MONTO          
   DECLARE @IGVSOLES            UD_MONTO          
   DECLARE @IGVDOLARES          UD_MONTO                
   DECLARE @TOTALSOLES          UD_MONTO 
   DECLARE @TOTALDOLARES        UD_MONTO 
   DECLARE @TICKESTANUL         INTEGER    
   DECLARE @MONTOTICKESTANULSOL UD_MONTO 
   DECLARE @MONTOTICKESTANULDOL UD_MONTO 
   
   -- Esta Tabla Temporal Contiene el Resultado General (OP_sales y OP_salesdet)         

   SELECT * INTO #TMP_CIERREXGENERAL FROM VW_CIERREZZ 
            WHERE SALESPOINT=@PTOVTA AND DATEPROCESALESDET=@DATEPROCE
   
      
   -- Se realiza un Select DISTINCT para Generar la Cabecera (Queda Pendiente si se Hace un Vista a OP_SALES sin
   -- op_salesdet
   -- o se Utiliza esta forma con el DISTINCT
  
   SELECT DISTINCT DOCTYPEID,NBRDOCUMENT,SITEID,TYPEPAYMENTID,CURYID,DOCSTATUSID,SALESPERID,SALESPERNAME,
                   USERID,DATEDOC,
                   DATEPROCESALES,PERPOST,TOTALPEN,TOTALUSD,SUBTOTALPEN,SUBTOTALUSD,TAXIGVPEN,
                   TAXIGVUSD,TAXISCPEN,TAXISCUSD,TOTALNOTAFFECTPEN,TOTALNOTAFFECTUSD,TOTCASHPEN,TOTRETURNEDPEN,TOTRETURNEDUSD,
                   TOTCASHUSD,custidss,taxregnbr,CUSTNAME,PLACA,NBRBONUS,CURYTYPEID,STKCLOSEDX,
                   CURYRATE,SALESPOINT INTO #TMP_CIERREX from #TMP_CIERREXGENERAL
     
   SELECT @NROTRANSAC=COUNT(*) FROM #TMP_CIERREX     
  
   SELECT @TICKECTBOLEINI=MIN(NBRDOCUMENT),@TICKECTBOLEFIN=MAX(NBRDOCUMENT),@TOTALTICKETBOLETASOL=SUM(TOTALPEN),@TOTALTICKETBOLETADOL=SUM(TOTALUSD) 
                                           FROM #TMP_CIERREX 
                                           WHERE (taxregnbr IS NULL OR taxregnbr='') AND DOCSTATUSID <> 'AN'
   
   SELECT @TICKECTFACTINI=MIN(NBRDOCUMENT),@TICKECTFACTFIN=MAX(NBRDOCUMENT),@TOTALTICKETFACTURASOL=SUM(TOTALPEN),@TOTALTICKETFACTURADOL=SUM(TOTALUSD)
                                           FROM #TMP_CIERREX 
                                           WHERE (NOT taxregnbr IS NULL AND taxregnbr <> '') AND DOCSTATUSID <> 'AN'
  
   SELECT @TOTALSOLES=SUM(TOTALPEN),@TOTALDOLARES=SUM(TOTALUSD),@VALORVENTASOLES=SUM(SUBTOTALPEN),
          @VALORVENTADOLARES=SUM(SUBTOTALUSD),@IGVSOLES=SUM(TAXIGVPEN),
          @IGVDOLARES=SUM(TAXIGVUSD),@NOAFECTOSOLES=SUM(TOTALNOTAFFECTPEN),
          @NOAFECTODOLARES=SUM(TOTALNOTAFFECTUSD) FROM #TMP_CIERREX WHERE DOCSTATUSID <> 'AN'
   
   SELECT @TICKESTANUL=COUNT(*),@MONTOTICKESTANULSOL=ISNULL(SUM(TOTALPEN),0),@MONTOTICKESTANULDOL=ISNULL(SUM(TOTALUSD),0) 
                                           FROM #TMP_CIERREX WHERE DOCSTATUSID = 'AN'    

 
   --   Parte de Resumen de Venta
   DECLARE @TOTALBOLFACTBASE UD_MONTO
   DECLARE @TOTALBOLFACTEXT UD_MONTO       

   SELECT @TOTALBOLFACTBASE=ISNULL(@TOTALTICKETBOLETASOL,0)+ISNULL(@TOTALTICKETFACTURASOL,0),
          @TOTALBOLFACTEXT=ISNULL(@TOTALTICKETBOLETADOL,0)+ISNULL(@TOTALTICKETFACTURADOL,0)
       
   --   Resumen por Tipos de Pagos
   --   Parte de Efectivo 
   
   DECLARE @TOTALEFECTIVOBASE UD_MONTO
   DECLARE @TOTALEFECTIVOEXT UD_MONTO
   DECLARE @TOTALVUELTOBASE UD_MONTO
   DECLARE @TOTALVUELTOEXT UD_MONTO
   DECLARE @TOTALNETOBASE UD_MONTO
   DECLARE @TOTALNETOEXT UD_MONTO
   DECLARE @TOTALTARJETABASE UD_MONTO
   DECLARE @TOTALTARJETAEXT UD_MONTO
   DECLARE @TOTALCREDITOBASE UD_MONTO
   DECLARE @TOTALCREDITOEXT UD_MONTO
   
 
      
   SELECT @TOTALEFECTIVOBASE=SUM(TOTCASHPEN),
          @TOTALEFECTIVOEXT=SUM(TOTCASHUSD),
	  @TOTALVUELTOBASE=SUM(TOTRETURNEDPEN),
	  @TOTALVUELTOEXT=SUM(TOTRETURNEDUSD),
          @TOTALNETOBASE=SUM(TOTALPEN),
          @TOTALNETOEXT=SUM(TOTALUSD) FROM #TMP_CIERREX WHERE DOCSTATUSID <> 'AN' AND TYPEPAYMENTID = '01'
                                                                                             
   --   Parte de Tarjeta de Credito  
   

   SELECT @TOTALTARJETABASE=SUM(TOTALPEN),
          @TOTALTARJETAEXT=SUM(TOTALUSD) FROM #TMP_CIERREX WHERE DOCSTATUSID <> 'AN' AND TYPEPAYMENTID = '02' 

   --   Parte de vales de Crédito

   SELECT @TOTALCREDITOBASE=SUM(TOTALPEN),
          @TOTALCREDITOEXT=SUM(TOTALUSD) FROM #TMP_CIERREX WHERE DOCSTATUSID <> 'AN' AND TYPEPAYMENTID = '06'   
      
   DECLARE @HEAD1 VARCHAR(40)
   DECLARE @HEAD2 VARCHAR(40)
   DECLARE @HEAD3 VARCHAR(40)
   DECLARE @HEAD4 VARCHAR(40)
   DECLARE @HEAD5 VARCHAR(40)
   DECLARE @HEAD6 VARCHAR(40)

   DECLARE @TOTZETA UD_MONTO
   DECLARE @NBRZETA SMALLINT

   SELECT @TOTZETA=TOTZETA,@NBRZETA=NBRZETA FROM OP_ZETA WHERE NBRZETA=(SELECT MAX(NBRZETA) FROM OP_ZETA WHERE SALESPOINT=@PTOVTA)
   SET @TOTZETA=ISNULL(@TOTZETA,0)+ISNULL(@TOTALSOLES,0)	
   SET @NBRZETA=ISNULL(@NBRZETA,0)+1

   SELECT @HEAD1=HEAD1,@HEAD2=HEAD2,@HEAD3=HEAD3,@HEAD4=HEAD4,@HEAD5=HEAD5,@HEAD6=HEAD6 FROM OP_TICKETFORM

   SELECT @HEAD1 AS HEAD1,@HEAD2 AS HEAD2,@HEAD3 AS HEAD3,@HEAD4 AS HEAD4,@HEAD5 AS HEAD5,@HEAD6 AS HEAD6,@SERIALNUMBER AS SERIALNUMBER,@MONTOTIPOCAMBIO AS MONTOTIPOCAMBIO,@NROTRANSAC AS NROTRANSAC,@TICKECTBOLEINI AS TICKECTBOLEINI,@TICKECTBOLEFIN AS TICKECTBOLEFIN,
	  @TICKECTFACTINI AS TICKECTFACTINI,@TICKECTFACTFIN AS TICKECTFACTFIN,
	  @VALORVENTASOLES AS VALORVENTASOLES,@NOAFECTOSOLES AS NOAFECTOSOLES,@IGVSOLES AS IGVSOLES,
	  @TOTALSOLES AS TOTALSOLES,@TICKESTANUL AS TICKESTANUL,@MONTOTICKESTANULSOL AS MONTOTICKESTANULSOL,@TOTZETA AS TOTZETA,@NBRZETA AS NBRZETA,
	  @TOTALTICKETBOLETASOL AS TOTALTICKETBOLETASOL,@TOTALTICKETFACTURASOL AS TOTALTICKETFACTURASOL,
	  @TOTALBOLFACTBASE AS TOTALBOLFACTBASE,@TOTALEFECTIVOBASE AS TOTALEFECTIVOBASE,@TOTALEFECTIVOEXT AS TOTALEFECTIVOEXT,
	  @TOTALVUELTOBASE AS TOTALVUELTOBASE,@TOTALVUELTOEXT AS TOTALVUELTOEXT,@TOTALNETOBASE AS TOTALNETOBASE,
          @TOTALNETOEXT AS TOTALNETOEXT,@TOTALTARJETABASE AS TOTALTARJETABASE,@TOTALTARJETAEXT AS TOTALTARJETAEXT,
	  @TOTALCREDITOBASE AS TOTALCREDITOBASE,@TOTALCREDITOEXT AS TOTALCREDITOEXT


END
 


CABECERA_TIENDA_CIERREZ_PROC '20030307','PTOVTA03'
GO
CABECERA_TIENDA_CIERREZ_PROC '06/03/2003','PTOVTA03'
go
RESU_TIENDA_CIERREZ_PROC '20030305','PTOVTA03',1
GO
CABECERA_PLAYA_CIERREZ_PROC '20030306','PTOVTA01'
go
RESU_PLAYA_CIERREZ_PROC '20030306','PTOVTA01',1

SELECT count(*) FROM OP_SALES WHERE DATEPROCESALES='20030306' AND SALESPOINT='PTOVTA03'
GO
SELECT COUNT(*),MIN(NBRDOCUMENT),MAX(NBRDOCUMENT),SUM(TOTALPEN) FROM OP_SALES WHERE DATEPROCESALES='20030306' AND SALESPOINT='PTOVTA03'
GO
SELECT SUM(TOTALPEN) FROM OP_SALES WHERE DATEPROCESALES='20030306' AND SALESPOINT='PTOVTA03' AND NBRDOCUMENT BETWEEN '5742043027' AND '5742043035'
go
SELECT SUM(TOTALPEN) FROM OP_SALES WHERE DATEPROCESALES='20030306' AND SALESPOINT='PTOVTA03' AND NBRDOCUMENT BETWEEN '5742043036' AND '5742043130'

SELECT sum(totalpen) FROM OP_SALES WHERE DATEPROCESALES='20030306' AND SALESPOINT='PTOVTA03' 


SELECT * FROM VW_CIERREZZ  WHERE SALESPOINT='PTOVTA03' 
go
CABECERA_TIENDA_CIERREZ_PROC '20030307','PTOVTA03'
go
RESU_TIENDA_CIERREZ_PROC '20030306','PTOVTA03',1
go
RESU_TIENDA_CIERREZ_PROC '20030306','PTOVTA03',2
go

select * from  intramov where BATNBR BETWEEN '5742043027' AND '5742043035'
RESU_TIENDA_CIERREZ_PROC '20030306','PTOVTA03',3

a
select * from op_sales where SALESPOINT='PTOVTA03' and dateprocesales ='20030306'



SELECT * FROM OP_SALESdet WHERE DATEPROCESALES='20030306' order by nbrdocument--AND NBRDOCUMENT BETWEEN '5742043027' AND '5742043035'
SELECT * FROM OP_RESERVATIONCASH



