DROP PROCEDURE Reporte_Deposito_Tarjetas
GO
CREATE PROCEDURE Reporte_Deposito_Tarjetas
(
@SITES			 VARCHAR(255),			 			 
@FECHAHOY	 	 VARCHAR(15),
@FECHAINI		 VARCHAR(15),
@FECHAFIN		 VARCHAR(15),
@MONEDA		 	 UD_CURYID
)
AS
BEGIN

--SET @FECHA='20030109'
--SET @MONEDA='USD'

DECLARE @CADENA 		VARCHAR(8000)

--DECLARE  @DATFECHAINI SMALLDATETIME 
--DECLARE  @DATFECHAFIN SMALLDATETIME
DECLARE  @DATFECHAHOY SMALLDATETIME

	DECLARE  @DATFECHAINI SMALLDATETIME 
	DECLARE  @DATFECHAFIN SMALLDATETIME
      
	SET @DATFECHAINI=CAST(RTRIM(LTRIM(@FECHAINI)) AS SMALLDATETIME)
	SET @DATFECHAFIN=CAST(RTRIM(LTRIM(@FECHAFIN)) AS SMALLDATETIME)


--SET @DATFECHAINI=CAST(RTRIM(LTRIM(@FECHAINI)) AS SMALLDATETIME)
--SET @DATFECHAFIN=CAST(RTRIM(LTRIM(@FECHAFIN)) AS SMALLDATETIME)
SET @DATFECHAHOY=CAST(RTRIM(LTRIM(@FECHAHOY)) AS SMALLDATETIME)

DECLARE  @DATFECHAINI2 VARCHAR(15)
DECLARE  @DATFECHAFIN2 VARCHAR(15)
DECLARE  @DATFECHAHOY2 SMALLDATETIME

SET @DATFECHAINI2=RTRIM(LTRIM(CAST(@DATFECHAINI AS CHAR(100))))
SET @DATFECHAFIN2=RTRIM(LTRIM(CAST(@DATFECHAFIN AS CHAR(100))))
SET @DATFECHAHOY2=RTRIM(LTRIM(CAST(@DATFECHAHOY AS CHAR(100))))

--SELECT @DATFECHAINI,@DATFECHAFIN,@DATFECHAHOY

DECLARE @MONEDAINVERSA	UD_CURYID
SELECT @MONEDAINVERSA=CURYID FROM CURRENCY WHERE CURYID NOT IN (SELECT @MONEDA)

-- SELCCIONA EL TIPO DE CAMBIO ACTUAL (TCONV)
DECLARE @TIPOCAMBIO 	FLOAT
DECLARE @OPERADOR 	CHAR(2)


SELECT @TIPOCAMBIO=RATE,@OPERADOR=OPERADOR FROM CURYRATE C,SETUP S
WHERE 
C.FROMCURYID=@MONEDAINVERSA AND
C.TOCURYID=@MONEDA AND
C.CURYTYPEID=S.CURYTYPEIDSALES AND
C.CURYDATE=(SELECT MAX(CURYDATE)FROM CURYRATE WHERE CURYDATE<=@DATFECHAHOY AND CURYTYPEID=S.CURYTYPEIDSALES )


IF @OPERADOR='*'
BEGIN
SET @CADENA="SELECT OP_CARD_MONT.SITEID, SITE.NAME, " +
	    "OP_CARD_MONT.CARDID, OP_CARD.DESCR,  " +
	    "OP_CARD.ACCOUNT, OP_CARD_MONT.DATEPOS,  " +
	    "OP_CARD_MONT.DATEPROCE,  " +
	    "CASE WHEN OP_CARD_MONT.CURYID='" + LTRIM(RTRIM(CAST(@MONEDA AS CHAR(100))))  + "'" +
		" THEN OP_CARD_MONT.TOTALCARD ELSE OP_CARD_MONT.TOTALCARD/" + CAST(@TIPOCAMBIO AS CHAR(100)) + " END AS TOTAL, " +
	    "OP_CARD_MONT.CURYID,  " +
	    "CURRENCY.DESCR AS MONEDA,  " +
	    "OP_CARD_MONT.STATUS,  " +
	    "OP_CARD_MONT.USERID, OP_CARD_MONT.stksend,  " +
	    "OP_CARD_MONT.stkreceive," + LTRIM(RTRIM(CAST(@TIPOCAMBIO AS CHAR(100)))) + " AS CAMBIO " +
	"FROM OP_CARD INNER JOIN " +
	    "OP_CARD_MONT ON  " +
	    "OP_CARD.CARDID = OP_CARD_MONT.CARDID INNER JOIN " +
	    "SITE ON OP_CARD_MONT.SITEID = SITE.SITEID INNER JOIN " +
	    "CURRENCY ON  " +
	    "OP_CARD_MONT.CURYID = CURRENCY.CURYID " +
	"WHERE OP_CARD_MONT.SITEID IN (" + @SITES + ") AND  " +
	    "OP_CARD_MONT.DATEPROCE BETWEEN '" +  RTRIM(LTRIM(CAST(@DATFECHAINI AS CHAR(100)))) + "' AND '" +  RTRIM(LTRIM(CAST(@DATFECHAFIN AS CHAR(100)))) + "'"
END
ELSE
BEGIN
	IF @OPERADOR='/'
	begin
SET @CADENA="SELECT OP_CARD_MONT.SITEID, SITE.NAME, " +
	    "OP_CARD_MONT.CARDID, OP_CARD.DESCR,  " +
	    "OP_CARD.ACCOUNT, OP_CARD_MONT.DATEPOS,  " +
	    "OP_CARD_MONT.DATEPROCE,  " +
	    "CASE WHEN OP_CARD_MONT.CURYID='" + LTRIM(RTRIM(CAST(@MONEDA AS CHAR(10)))) + "'" +
		" THEN OP_CARD_MONT.TOTALCARD ELSE OP_CARD_MONT.TOTALCARD*" + LTRIM(RTRIM(CAST(@TIPOCAMBIO AS CHAR(100)))) + " END AS TOTAL, " +
	    "OP_CARD_MONT.CURYID,  " +
	    "CURRENCY.DESCR AS MONEDA,  " +
	    "OP_CARD_MONT.STATUS,  " +
	    "OP_CARD_MONT.USERID, OP_CARD_MONT.stksend,  " +
	    "OP_CARD_MONT.stkreceive," + LTRIM(RTRIM(CAST(@TIPOCAMBIO AS CHAR(100)))) + " AS CAMBIO " +
	"FROM OP_CARD INNER JOIN " +
	    "OP_CARD_MONT ON  " +
	    "OP_CARD.CARDID = OP_CARD_MONT.CARDID INNER JOIN " +
	    "SITE ON OP_CARD_MONT.SITEID = SITE.SITEID INNER JOIN " +
	    "CURRENCY ON  " +
	    "OP_CARD_MONT.CURYID = CURRENCY.CURYID " +
	"WHERE OP_CARD_MONT.SITEID IN (" + @SITES + ") AND  " +
	    "OP_CARD_MONT.DATEPROCE BETWEEN '" +  RTRIM(LTRIM(CAST(@DATFECHAINI AS CHAR(100)))) + "' AND '" +  RTRIM(LTRIM(CAST(@DATFECHAFIN AS CHAR(100)))) + "'"
	END
END

--SELECT @CADENA

EXEC(@CADENA)

END











