
DROP PROC RESU_PLAYA_CIERREX_PROC
GO
CREATE PROC RESU_PLAYA_CIERREX_PROC
( @NBRTURN	INT,
  @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 D.NBRTURN=@NBRTURN 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 D.NBRTURN=@NBRTURN 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 D.NBRTURN=@NBRTURN AND C.SALESPOINT=@PTOVTA AND C.DOCTYPEID=@DOCTYPEID AND D.DOCSTATUSID<>'AN'
		GROUP BY C.SALESPERID,V.SALESPERNAME
		ORDER BY C.SALESPERID
	END 
 
	IF (@RESUMEN=4) -- POR CARA
	BEGIN 
		SELECT D.NBRSIDE AS CARA,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 D.NBRTURN=@NBRTURN AND C.SALESPOINT=@PTOVTA AND LEN(RTRIM(LTRIM(D.NBRSIDE)))=2 AND C.DOCTYPEID=@DOCTYPEID AND D.DOCSTATUSID<>'AN'
		GROUP BY D.NBRSIDE,D.INVTIDSKU,D.DESCRINVENTORY
		ORDER BY D.NBRSIDE,D.INVTIDSKU
	END

END 


RESU_PLAYA_CIERREX_PROC 1,'20030303','PTOVTA01',1
GO
RESU_PLAYA_CIERREX_PROC 1,'20030303','PTOVTA01',2
GO
RESU_PLAYA_CIERREX_PROC 1,'20030303','PTOVTA01',3
GO
RESU_PLAYA_CIERREX_PROC 1,'20030303','PTOVTA01',4

SELECT * FROM OP_SALESDET WHERE DATEPROCESALES='20030303' 

DROP PROC CABECERA_PLAYA_CIERREX_PROC
GO
CREATE PROC CABECERA_PLAYA_CIERREX_PROC
( @NBRTURN	INT,
  @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_CIERREX 
            WHERE NBRTURN=@NBRTURN AND 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,
                   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 @TOTALTARJETABASE UD_MONTO
   DECLARE @TOTALTARJETAEXT UD_MONTO
   DECLARE @TOTALCREDITOBASE UD_MONTO
   DECLARE @TOTALCREDITOEXT UD_MONTO
   
 
      
   SELECT @TOTALEFECTIVOBASE=SUM(TOTCASHPEN),
          @TOTALEFECTIVOEXT=SUM(TOTCASHUSD) 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)

   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,
	  @TOTALTICKETBOLETASOL AS TOTALTICKETBOLETASOL,@TOTALTICKETFACTURASOL AS TOTALTICKETFACTURASOL,
	  @TOTALBOLFACTBASE AS TOTALBOLFACTBASE,@TOTALEFECTIVOBASE AS TOTALEFECTIVOBASE,@TOTALTARJETABASE AS TOTALTARJETABASE,
	  @TOTALCREDITOBASE AS TOTALCREDITOBASE

END
 


CABECERA_CIERRE_PROC 2,'20030304','PTOVTA02'

     
    

