DROP PROCEDURE ReporteVentasProc
GO
CREATE PROCEDURE ReporteVentasProc
(
 @FECHAINI	CHAR(8),
 @FECHAFIN	CHAR(8),
 @MONEDA	UD_CURYID,
 @ALMACENES	VARCHAR(255)='',-- CADENA DE ALMACENES
 @TODOS_ALM	TINYINT=1,--TODOS LOS ALMACENES
 @TCONDICION	CHAR(3)='',--TIPO CONDICION CATegoria, SCA SUB CATERIGORIA
 @TODOS_CON	TINYINT=1,--TODOS LOS elementos del tipo de condicion
 @CRITERIOS	VARCHAR(255)=''	--solo los elementos que cumplan la condicion
)
AS
BEGIN

	DECLARE @CADENA	VARCHAR(8000)
	DECLARE @MONEDABASE AS UD_CURYID	
	
	SET @MONEDABASE=(SELECT CURYORIG FROM SETUP)        --OBTENGO EL CODIGO DE LA MONEDA BASE
  
        CREATE TABLE #TEMPORAL
            (SITEID   	CHAR(5),
	     INVTIDSKU 	CHAR(20),
	     Cantidad	NUMERIC(10,3),
	     Total	NUMERIC(9,2) 
	     )            

	IF (@MONEDA=@MONEDABASE) 
		BEGIN
			SET @CADENA="SELECT SITEID,INVTIDSKU,CASE (DOCSTATUSID) WHEN 'AN' THEN 0 ELSE CASE(DOCTYPEID) WHEN (SELECT DOCTYPEIDNC FROM OP_SETUP) THEN (-1)*QTY ELSE QTY END END AS Cantidad," +
				    "CASE (DOCSTATUSID) WHEN 'AN' THEN 0 ELSE CASE(DOCTYPEID) WHEN (SELECT DOCTYPEIDNC FROM OP_SETUP) THEN (-1)*TOTALPEN ELSE TOTALPEN END END AS Total " + 
			    	    "FROM OP_SALESDET " +
	        	            "WHERE DATEPROCESALES BETWEEN '"+ @FECHAINI +"' AND '"+ @FECHAFIN + "'" 
				    
		END
	ELSE
		BEGIN
			SET @CADENA="SELECT SITEID,INVTIDSKU,CASE (DOCSTATUSID) WHEN 'AN' THEN 0 ELSE CASE(DOCTYPEID) WHEN (SELECT DOCTYPEIDNC FROM OP_SETUP) THEN (-1)*QTY ELSE QTY END END AS Cantidad," +
				    "CASE (DOCSTATUSID) WHEN 'AN' THEN 0 ELSE CASE(DOCTYPEID) WHEN (SELECT DOCTYPEIDNC FROM OP_SETUP) THEN (-1)*TOTALUSD ELSE TOTALUSD END END AS Total " + 
			    	    "FROM OP_SALESDET " +
	        	            "WHERE DATEPROCESALES BETWEEN '"+ @FECHAINI +"' AND '"+ @FECHAFIN + "'"
		END


	IF (@TODOS_ALM=0)
              	SET @CADENA=@CADENA + " AND SITEID IN (" + @ALMACENES + ") " --+ "GROUP BY SITEID,INVTIDSKU"
	--ELSE
		--SET @CADENA=@CADENA + " GROUP BY SITEID,INVTIDSKU"



	-- INSERTANDO EN EL TEMPORAL LOS DATOS DE LA CONSULTA
        INSERT INTO #TEMPORAL EXEC (@CADENA)

	SET @CADENA = "SELECT #TEMPORAL.SITEID,SIT.NAME,INV.CLASSID,CAT.DESCR ,INV.CLASSUBID,SCAT.DESCR AS DESCR_SC,#TEMPORAL.INVTIDSKU,INV.DESCR AS DESCR_ART,Cantidad,Total,IDAT.QTYAVAIL " +
		      "FROM   #TEMPORAL " +
	              "JOIN IN_INVENTORY INV " +  	
		      "ON #TEMPORAL.INVTIDSKU=INV.INVTIDSKU " +
		      "JOIN IN_CATEGORY CAT " + 			
		      "ON INV.CLASSID=CAT.CLASSID " +
		      "JOIN IN_SUBCATEGORY SCAT " + 		
		      "ON INV.CLASSUBID=SCAT.CLASSUBID " +
		      "JOIN SITE SIT " + 		
		      "ON #TEMPORAL.SITEID=SIT.SITEID " +
	              "JOIN IN_INVENTORYDAT IDAT " + 		
		      "ON INV.INVTIDSKU=IDAT.INVTIDSKU AND #TEMPORAL.SITEID=IDAT.SITEID "

	IF (@TODOS_CON=0 AND @TCONDICION='CAT')
		SET @CADENA=@CADENA + " WHERE INV.CLASSID IN (" + @CRITERIOS + ") "

	IF (@TODOS_CON=0 AND @TCONDICION='SCA')
		SET @CADENA=@CADENA + " WHERE INV.CLASSUBID IN (" + @CRITERIOS + ") "
			
		
	EXEC (@CADENA)	

	DROP TABLE #TEMPORAL
END 


