DROP PROCEDURE IF EXISTS StockItem;
DELIMITER ;;
CREATE PROCEDURE StockItem (
IN _cIdItem VARCHAR(12),
IN _fcorte VARCHAR(8))

BEGIN 	
	DECLARE _isAll INT;
	DECLARE _cargarCompPend INT;
	DECLARE _lNEBajaStock INT;
  	DECLARE _ltransf_noinvent INT;
	
	SET _isAll 				= (TRIM(_cIdItem)="");

	SET _lNEBajaStock 		= (SELECT value=".T." FROM Setup WHERE Id = "lNEBajaStock");
	SET _ltransf_noinvent 	= (SELECT value=".T." FROM Setup WHERE Id = "ltransf_noinvent");
	SET _cargarCompPend 	= (SELECT value=".T." FROM Setup WHERE Id = "cargarCompPend");

	SET @sumac  = "SELECT B.iditem, SUM(B.quantityIn)  as q FROM ";
	SET @suma   = "SELECT B.iditem, SUM(B.quantity)    as q FROM ";
	SET @restac = "SELECT B.iditem, -SUM(B.quantityIn) as q FROM ";
	SET @resta  = "SELECT B.iditem, -SUM(B.quantity)   as q FROM ";
	
	SET @excl = " and B.iditem not in (select id from item where gasto and isservice) ";

	SET @excl = " ";

	SET @_where = " WHERE A.void=0 ";
	SET @citem  = concat(" and B.iditem = '",_cIdItem,"'");

	IF _isAll THEN
		SET @_where = concat(@_where, @excl );
	ELSE
		SET @_where = concat(@_where, @citem );
	END IF;
	
	
	SET @ccorte = " ";
	IF TRIM(_fcorte)!="" THEN
		SET @ccorte = concat(" and B.date<=",_fcorte," ");
		SET @_where = concat(@_where, " and A.date<=",_fcorte," ");
	END IF; 
	
	SET @condPo = " and a.transito=0 ";
	IF !_cargarCompPend THEN
		SET @condPo = concat(@condPo, "  and A.Archivado = 1  ");
	END IF; 

	SET @condI = " ";

	IF !_cargarCompPend THEN
		SET @condI = concat(@condI," and A.Archivado = 1 ");
	END IF;
	
	IF _lNEBajaStock THEN
		SET @condI = concat(@condI," and A.agrupar=0 ");
	ELSE 
		SET @condI = concat(@condI," and A.facturado=0 ");
	END IF;
	
	SET @condK = " and A.state = 1 " ;
	IF _ltransf_noinvent THEN
		SET @condK = concat(@condK," and !A.transferencia ");
	END IF;

	SET @gby  = " GROUP BY 1 ";

	SET @s_po   = concat(@sumac,  " Po A INNER JOIN PoDet B ON A.Id=B.Id ", 					@_where, @condPo,' and A.tipoComprobante!="04" ');
	SET @s_ponc = concat(@restac, " Po A INNER JOIN PoDet B ON A.Id=B.Id  ",					@_where, @condPo,' and A.tipoComprobante ="04" and A.tiponcd=1 ');
	SET @s_invo = concat(@resta,  " Invoice A INNER JOIN InvoiceDet B ON A.Id=B.Id ", 			@_where, @condI, ' and A.tipo != "06" ');
	SET @s_invd = concat(@suma,   " Invoice A INNER JOIN InvoiceDet B ON A.Id=B.Id ",			@_where, @condI, ' and A.tipo = "06" ');
	SET @s_kin  = concat(@suma,   " KardexIn A INNER JOIN KardexInDet B ON A.Id=B.Id ", 		@_where, @condK);
	SET @s_kout = concat(@resta,  " KardexOut A INNER JOIN KardexOutDet B ON A.Id=B.Id ", 		@_where, @condK);
	SET @s_ndc  = concat(@suma,   " ndc A INNER JOIN ndcDet B ON A.Id=B.Id ", 					@_where, " AND A.noinvent=0 AND A.type_doc=1  ");
	SET @s_cint = concat(@resta,  " ConsInterno A INNER JOIN ConsInternoDet B ON A.Id=B.Id ", 	@_where, " and A.state = 1" );
	
	SET @s_Hcmd = concat("SELECT B.iditem, SUM(-B.quantity) as q FROM Hcomanda AS B ",   "WHERE b.void=0 ",@citem,@ccorte," AND B.idrescomanda='' ");
	SET @s_Rcmd = concat("SELECT B.iditem, SUM(-B.quantity) as q FROM rescomanda AS B ", "WHERE b.void=0 ",@citem,@ccorte," AND B.idinvoice='' ");
	
	IF _isAll THEN
		truncate table stocktmp ;	
	ELSE
		DELETE FROM stocktmp WHERE iditem=_cIdItem;
	END IF;

	SET @sql := concat("INSERT INTO stocktmp SELECT iditem, SUM(q) FROM ( ");

	SET @sql := concat(@sql, @s_po, 					@gby);
	SET @sql := concat(@sql, " union all ", @s_ponc,	@gby);
	SET @sql := concat(@sql, " union all ", @s_invo,	@gby);
	SET @sql := concat(@sql, " union all ", @s_kin,		@gby);
	SET @sql := concat(@sql, " union all ", @s_kout,	@gby);
	SET @sql := concat(@sql, " union all ", @s_ndc,		@gby);
	SET @sql := concat(@sql, " union all ", @s_cint,	@gby);
	SET @sql := concat(@sql, " union all ", @s_invd,	@gby);
	SET @sql := concat(@sql, " union all ", @s_Hcmd,	@gby);
	SET @sql := concat(@sql, " union all ", @s_Rcmd,	@gby);
	SET @sql := concat(@sql, " ) AS Result ",       	@gby);

	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;

	IF _isAll THEN
		UPDATE stock SET stock = 0;
		UPDATE stock set stock = (select q FROM stocktmp WHERE stock.iditem=stocktmp.iditem) WHERE iditem in (select iditem FROM stocktmp);
		INSERT INTO stock (iditem,stock) SELECT iditem, q FROM stocktmp WHERE iditem NOT in (select iditem FROM stock);
	ELSE
		UPDATE stock SET stock = 0 WHERE iditem=_cIdItem;
		UPDATE stock set stock = (select q FROM stocktmp WHERE stock.iditem=stocktmp.iditem) WHERE iditem in (select iditem FROM stocktmp);
		INSERT INTO stock (iditem,stock) SELECT iditem, q FROM stocktmp WHERE iditem NOT in (select iditem FROM stock);

	END IF;

END
;;
DELIMITER ;


DELIMITER ;;
DROP PROCEDURE IF EXISTS costoItem ;
CREATE PROCEDURE costoItem (
IN _cIdItem VARCHAR(12),
IN esHist BOOL)

BEGIN
  CALL costoItem_matriz (_cIdItem);
  IF (TRIM(_cIdItem)="") THEN
  	CALL costoItem_recorrer(esHist);
  ELSE
  	CALL costoItem_calcular(_cIdItem,esHist);
  END IF;
END
;;
DELIMITER ;


DROP PROCEDURE IF EXISTS costoItem_recorrer;
DELIMITER ;;
CREATE PROCEDURE costoItem_recorrer(
IN esHist BOOL)

BEGIN
  DECLARE cIdItem VARCHAR(12);
  DECLARE done INT DEFAULT 0;
  DECLARE cur1 CURSOR FOR SELECT distinct iditem FROM costoitem_matriz;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN cur1;

  REPEAT
    FETCH cur1 INTO cIdItem ;
    IF NOT done THEN
	   CALL costoItem_calcular(cIdItem,esHist);	
	END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;

END
;;
DELIMITER ;



DROP PROCEDURE IF EXISTS costoItem_calcular;
DELIMITER ;;
CREATE PROCEDURE costoItem_calcular(
IN _cIdItem VARCHAR(12),
IN esHist BOOL)
BEGIN
  DECLARE nQuantity DOUBLE DEFAULT 0.0;
  DECLARE nCosto DOUBLE DEFAULT 0.0;
  DECLARE nType int DEFAULT 0.0;
  DECLARE dFecha date ;

  DECLARE nCostAVG DOUBLE DEFAULT 0;
  DECLARE nCostHi DOUBLE DEFAULT 0;
  DECLARE nCostLast DOUBLE DEFAULT 0;
  DECLARE nStock DOUBLE DEFAULT 0;
  
  DECLARE done INT DEFAULT 0;
  DECLARE cur1 CURSOR FOR SELECT q, costo, t, date FROM costoitem_matriz WHERE iditem=_cIdItem ORDER BY date,t,id;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN cur1;

  SET nStock = 0;


  REPEAT
    FETCH cur1 INTO nQuantity, nCosto, nType, dFecha;
    IF NOT done THEN
		IF nType = 0 THEN		
			IF nStock>0 AND nQuantity>0 THEN
				SET nCostAVG = (nCostAVG*nStock + nQuantity*nCosto)/(nStock+nQuantity);
			ELSE
				SET nCostAVG = nCosto;
			END IF;
			IF nCosto>nCostHi THEN
				SET nCostHi = nCosto;
			END IF;
			SET nCostLast = nCosto;
		
			IF esHist THEN
				DELETE FROM CostoHist WHERE iditem=_cIdItem AND fecha=dFecha;
				INSERT INTO CostoHist (iditem, fecha, costavg, costhi, costlast) VALUES (_cIdItem,dFecha,nCostAVG,nCostHi,nCostLast);
			END IF;
		END IF;	
		SET nStock = nStock + nQuantity;
	END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
    

  UPDATE stock SET costavg=nCostAVG, costhi=nCostHi, costlast=nCostLast where iditem=_cIdItem;

  
END
;;
DELIMITER ;


DROP PROCEDURE IF EXISTS costoItem_matriz ;
DELIMITER ;;

CREATE PROCEDURE costoItem_matriz (IN _cIdItem VARCHAR(12)) 
BEGIN
	DECLARE _isAll INT;
	DECLARE _cargarCompPend INT;
	DECLARE _lNEBajaStock INT;
 	DECLARE _ltransf_noinvent INT;
 	DECLARE _lcostoSoloCompras INT;

	
	SET _isAll 				= (TRIM(_cIdItem)="");

	SET _lNEBajaStock 		= (SELECT value=".T." FROM Setup WHERE Id = "lNEBajaStock");
	SET _ltransf_noinvent 	= (SELECT value=".T." FROM Setup WHERE Id = "ltransf_noinvent");
	SET _cargarCompPend 	= (SELECT value=".T." FROM Setup WHERE Id = "cargarCompPend");
	SET _lcostoSoloCompras 	= (SELECT value=".T." FROM Setup WHERE Id = "lcostoSoloCompras");

	
	SET @_where = " WHERE C.void=0 ";
	SET @citem  = concat(" and D.iditem = '",_cIdItem,"'");
	IF !_isAll THEN
		SET @_where = concat(@_where, @citem );
	END IF;

	
	SET @condPo = " and C.transito=0 ";
	IF !_cargarCompPend THEN
		SET @condPo = concat(@condPo, "  and C.Archivado = 1  ");
	END IF; 

	SET @condI = " ";

	IF !_cargarCompPend THEN
		SET @condI = concat(@condI," and C.Archivado = 1 ");
	END IF;
	
	IF _lNEBajaStock THEN
		SET @condI = concat(@condI," and C.agrupar=0 ");
	ELSE 
		SET @condI = concat(@condI," and C.facturado=0 ");
	END IF;
	
	SET @condK = " and C.state = 1 " ;
	IF _ltransf_noinvent THEN
		SET @condI = concat(@condK," and !C.transferencia ");
	END IF;

	SET @_mdate    = "SELECT MAX(C.date) AS DATE, D.iditem, ";
	SET @_date     = "SELECT C.date, D.iditem, ";
	SET @_cdate    = "SELECT D.date, D.iditem, -sum(D.quantity) as  quantity, " ;

	SET @_mid      = "max(C.id) as id,";
	SET @_id       = "C.id,";
	SET @_cid      = "max(D.id) as id,";


	-- SET @_Costo_po = "sum(((D.price-D.DESCUNIT)*D.quantityIn))/SUM(D.quantityIn)*(1.0 + (C.recargo+C.overcharge-C.discount)/(C.subtotal-(C.recargo+C.overcharge-C.discount))) as costo,";
	

	
	SET @_Costo_po = "sum(((D.price-D.DESCUNIT)*D.quantityIn))/SUM(D.quantityIn) as costo,";
	SET @_Costo_in = "max(D.price-D.DESCUNIT) as costo,";
	SET @_Costo_nc = "max(D.costoventa) as costo,";
	SET @_Costo_ka = "max(D.price) as costo,";

	SET @g1  = " GROUP BY C.id,D.IdItem ";
	SET @g2  = " GROUP BY C.date,D.IdItem ";
	SET @g3  = " GROUP BY D.date,D.IdItem ";
	

	SET @cur00 = concat(@_mdate, "SUM(D.quantityIn) as q,", @_Costo_po, @_id,  '0 as t ', "FROM Po C INNER JOIN PoDet D ON C.Id=D.Id ", 					@_where, @condPo,' and C.tipoComprobante!="04" ',		@g1);
	SET @cur01 = concat(@_date,  "-SUM(D.quantity) as q,"  , @_Costo_in, @_mid, '1 as t ', "FROM Invoice C INNER JOIN InvoiceDet D ON C.Id=D.Id ", 		@_where, @condI, ' and C.tipo != "06" ',				@g2);
	SET @cur02 = concat(@_mdate, "SUM(D.quantity) as q,"  , @_Costo_ka, @_id,  '0 as t ', "FROM KardexIn C INNER JOIN KardexInDet D ON C.Id=D.Id ", 		@_where, " and C.importacion ",@condK,				@g1);
	SET @cu202 = concat(@_mdate, "SUM(D.quantity) as q,"  , @_Costo_ka, @_id,  '0 as t ', "FROM KardexIn C INNER JOIN KardexInDet D ON C.Id=D.Id ", 		@_where, " and !C.importacion ",@condK,				@g1);
	SET @cur03 = concat(@_date,  "-SUM(D.quantity) as q,"  , @_Costo_ka, @_mid, '1 as t ', "FROM KardexOut C INNER JOIN KardexOutDet D ON C.Id=D.Id ",	@_where, @condK,										@g2);
	SET @cur07 = concat(@_date,  "SUM(D.quantity) as q,"  , @_Costo_nc, @_mid, '1 as t ', "FROM ndc C INNER JOIN ndcDet D ON C.Id=D.Id ", 				@_where, " AND C.noinvent=0 AND C.type_doc=1  ",		@g2);
	SET @cur11 = concat(@_date,  "-SUM(D.quantityIn) as q,", @_Costo_po, @_mid, '1 as t ', "FROM Po C INNER JOIN PoDet D ON C.Id=D.Id  ", 				@_where, @condPo,' and C.tipoComprobante ="04" and C.tiponcd=1 ',@g2);	
	SET @cur13 = concat(@_date,  "-SUM(D.quantity) as q,"  , @_Costo_ka, @_mid, '1 as t ', "FROM ConsInterno C INNER JOIN ConsInternoDet D ON C.Id=D.Id ",@_where, " and C.state = 1",							@g2);
	SET @cur15 = concat(@_date,  "-SUM(D.quantity) as q,"  , @_Costo_in, @_mid, '1 as t ', "FROM Invoice C INNER JOIN InvoiceDet D ON C.Id=D.Id ",		@_where, @condI, ' and C.tipo = "06" ',				@g2);
	SET @cur17 = concat(@_cdate,  		  @_Costo_ka, @_cid, '1 as t ', "FROM Hcomanda AS D ",   										"WHERE D.void=0 ",@citem," AND D.idrescomanda='' ",	@g3);
	SET @cur19 = concat(@_cdate,          @_Costo_ka, @_cid, '1 as t ', "FROM rescomanda AS D ", 										"WHERE D.void=0 ",@citem," AND D.idinvoice='' ",		@g3);

 	IF _lcostoSoloCompras THEN
		SET @cu202 = concat(@_date,"SUM(D.quantity) as q,", @_Costo_ka, @_mid, '1 as t ', "FROM KardexIn C INNER JOIN KardexInDet D ON C.Id=D.Id ", 		@_where, " and !C.importacion ",@condK,					@g2);
	END IF;
	
	IF _isAll THEN
		truncate table costoitem_matriz ;
	ELSE
		DELETE from costoitem_matriz WHERE iditem=_cIdItem;
	END IF;

	SET @sql := concat("INSERT INTO costoitem_matriz (iditem, date,  q, costo,  t, id ) SELECT iditem, date,  q, costo,  t, id  FROM ( ");
	SET @sql := concat(@sql, @cur00);

	SET @sql := concat(@sql, " union all ", @cur01);
	SET @sql := concat(@sql, " union all ", @cur02);
	SET @sql := concat(@sql, " union all ", @cu202);
	SET @sql := concat(@sql, " union all ", @cur03);
	SET @sql := concat(@sql, " union all ", @cur07);
	SET @sql := concat(@sql, " union all ", @cur11);
	SET @sql := concat(@sql, " union all ", @cur13);
	SET @sql := concat(@sql, " union all ", @cur15);
	SET @sql := concat(@sql, " union all ", @cur17);
	SET @sql := concat(@sql, " union all ", @cur19);
	SET @sql := concat(@sql, " ) AS Result ORDER BY iditem,date,t,id ");

	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;

END
;;
DELIMITER ;

DROP PROCEDURE IF EXISTS UpdateCosto ;
DELIMITER ;;
CREATE PROCEDURE UpdateCosto (
IN _desde VARCHAR(8),
IN _hasta VARCHAR(8))

BEGIN
  update invoicedet set cost = (select costavg from costohist where iditem=invoicedet.iditem and (select fecha from invoice where id=invoicedet.id)<=fecha limit 1);
  UPDATE invoice SET cost0=(select sum(cost)*quantity from invoicedet where id=invoice.id and liva=0) where date between _desde and _hasta ;
  UPDATE invoice SET costx=(select sum(cost)*quantity from invoicedet where id=invoice.id and liva=1) where date between _desde and _hasta ;
  update invoice set cost=cost0+costx where date between _desde and _hasta ;
  
  update ndcdet set costoventa= (select costavg from costohist where iditem=ndcdet.iditem and (select fecha from ndc where id=ndcdet.id)<=fecha limit 1);
  UPDATE ndc SET cost0=(select sum(costoventa)*quantity from ndcdet where id=ndc.id and liva=0) where date between _desde and _hasta ;
  UPDATE ndc SET costx=(select sum(costoventa)*quantity from ndcdet where id=ndc.id and liva=1) where date between _desde and _hasta ;
 
  update hcomanda set cost = (select costavg from costohist where iditem=hcomanda.iditem and hcomanda.date<=fecha limit 1);
 
END
;;
DELIMITER ;



DROP PROCEDURE IF EXISTS StockBodega;
DELIMITER ;;
CREATE PROCEDURE StockBodega (
IN _cIdItem VARCHAR(12),
IN _fcorte VARCHAR(8),
IN _idbodega VARCHAR(8)
)

BEGIN 	
	DECLARE _isAll INT;
	DECLARE _lbod INT;
	DECLARE _cargarCompPend INT;
	DECLARE _lNEBajaStock INT;
  	DECLARE _ltransf_noinvent INT;
	
	SET _isAll 				= (TRIM(_cIdItem)="");
	
	SET _lbod 				= (TRIM(_idbodega)!="");
	
	

	SET _lNEBajaStock 		= (SELECT value=".T." FROM Setup WHERE Id = "lNEBajaStock");
	SET _ltransf_noinvent 	= (SELECT value=".T." FROM Setup WHERE Id = "ltransf_noinvent");
	SET _cargarCompPend 	= (SELECT value=".T." FROM Setup WHERE Id = "cargarCompPend");

	SET @sumac  = "SELECT B.iditem, A.idBodega, SUM(B.quantityIn)  as q FROM ";
	SET @suma   = "SELECT B.iditem, A.idBodega, SUM(B.quantity)    as q FROM ";
	SET @restac = "SELECT B.iditem, A.idBodega, -SUM(B.quantityIn) as q FROM ";
	SET @resta  = "SELECT B.iditem, A.idBodega, -SUM(B.quantity)   as q FROM ";


	SET @_where = " WHERE A.void=0 ";
	
	IF _lbod THEN
		SET @_where = concat(@_where, " and A.idbodega='",_idbodega,"' ");
	
	END IF;	
	
	
	SET @citem  = concat(" and B.iditem = '",_cIdItem,"'");
	IF !_isAll THEN
		SET @_where = concat(@_where, @citem );
	END IF;
	
	
	
	SET @ccorte = " ";
	IF TRIM(_fcorte)!="" THEN
		SET @ccorte = concat(" and B.date<='",_fcorte,"' ");
		SET @_where = concat(@_where, " and A.date<='",_fcorte,"' ");
	END IF;

	SET @condPo = " and a.transito=0 ";
	IF !_cargarCompPend THEN
		SET @condPo = concat(@condPo, "  and A.Archivado = 1  ");
	END IF; 

	SET @condI = " ";

	IF !_cargarCompPend THEN
		SET @condI = concat(@condI," and A.Archivado = 1 ");
	END IF;
	
	IF _lNEBajaStock THEN
		SET @condI = concat(@condI," and A.agrupar=0 ");
	ELSE 
		SET @condI = concat(@condI," and A.facturado=0 ");
	END IF;
	
	SET @condK = " and A.state = 1 " ;
	IF _ltransf_noinvent THEN
		SET @condK = concat(@condK," and !A.transferencia ");
	END IF;

	SET @gby  = " GROUP BY 1,2 ";
	
	SET @condBodega = ' ';
	
	SET @bodTo := " SELECT B.iditem, A.idBodTo as idbodega, SUM(B.quantity) as q FROM Transfer A 
	inner join TransferDet B ON A.id=B.id ";

	SET @bodFrom := " SELECT B.iditem, A.idBodFrom as idbodega, SUM(-B.quantity) as q FROM Transfer A 
	inner join TransferDet B ON A.id=B.id ";


	IF _lbod THEN
		SET @bodTo := concat(@bodTo ," where A.idbodto='",_idbodega,"' ", @citem );
		SET @bodFrom := concat(@bodFrom ," where A.idBodFrom='",_idbodega,"' ", @citem );

	END IF;


	SET @bodTo := concat(@bodTo ," and !A.void AND A.state and !A.transito ");
	SET @bodFrom := concat(@bodFrom ," and !A.void AND A.state and !A.transito ");



	SET @s_po   = concat(@sumac,  " Po A INNER JOIN PoDet B ON A.Id=B.Id ", 					@_where, @condPo,' and A.tipoComprobante!="04" ');
	SET @s_ponc = concat(@restac, " Po A INNER JOIN PoDet B ON A.Id=B.Id  ",					@_where, @condPo,' and A.tipoComprobante ="04" and A.tiponcd=1 ');
	SET @s_invo = concat(@resta,  " Invoice A INNER JOIN InvoiceDet B ON A.Id=B.Id ", 			@_where, @condI, ' and A.tipo != "06" ');
	SET @s_invd = concat(@suma,   " Invoice A INNER JOIN InvoiceDet B ON A.Id=B.Id ",			@_where, @condI, ' and A.tipo = "06" ');
	SET @s_kin  = concat(@suma,   " KardexIn A INNER JOIN KardexInDet B ON A.Id=B.Id ", 		@_where, @condK);
	SET @s_kout = concat(@resta,  " KardexOut A INNER JOIN KardexOutDet B ON A.Id=B.Id ", 		@_where, @condK);
	SET @s_ndc  = concat(@suma,   " ndc A INNER JOIN ndcDet B ON A.Id=B.Id ", 					@_where, " AND A.noinvent=0 AND A.type_doc=1  ");
	SET @s_cint = concat(@resta,  " ConsInterno A INNER JOIN ConsInternoDet B ON A.Id=B.Id ", 	@_where, " and A.state = 1" );
	
	SET @s_Hcmd = concat("SELECT B.iditem, B.idbodega, SUM(-B.quantity) as q FROM Hcomanda AS B ",   "WHERE b.void=0 "," and B.idbodega='",_idbodega,"' ",@citem,@ccorte," AND B.idrescomanda='' ");
	SET @s_Rcmd = concat("SELECT B.iditem, B.idbodega, SUM(-B.quantity) as q FROM rescomanda AS B ", "WHERE b.void=0 "," and B.idbodega='",_idbodega,"' ",@citem,@ccorte," AND B.idinvoice='' ");
	
				
	IF TRIM(_fcorte)!="" THEN
		SET @sql1= concat(@sql1," and A.date<='",_fcorte,"' ") ;
	END IF;


	SET @sql := concat("INSERT INTO stock_bodtmp (iditem,idbodega,q) SELECT iditem, idbodega, @q:=SUM(q) FROM ( ");
	SET @sql := concat(@sql, @s_po, 					@gby);
	SET @sql := concat(@sql, " union all ", @s_ponc,	@gby);
	SET @sql := concat(@sql, " union all ", @s_invo,	@gby);
	SET @sql := concat(@sql, " union all ", @s_kin,		@gby);
	SET @sql := concat(@sql, " union all ", @s_kout,	@gby);
	SET @sql := concat(@sql, " union all ", @s_ndc,		@gby);
	SET @sql := concat(@sql, " union all ", @s_cint,	@gby);
	SET @sql := concat(@sql, " union all ", @s_invd,	@gby);
	SET @sql := concat(@sql, " union all ", @s_Hcmd,	@gby);
	SET @sql := concat(@sql, " union all ", @s_Rcmd,	@gby);
	SET @sql := concat(@sql, " union all ", @bodTo,		@gby);
	SET @sql := concat(@sql, " union all ", @bodFrom ,	@gby);
	SET @sql := concat(@sql, " ) AS Result ",       	@gby);
	
	IF _isAll THEN
		truncate table stock_bodtmp ;
	ELSE
		IF _lbod THEN
			DELETE FROM stock_bodtmp WHERE iditem=_cIdItem AND idBodega=_idbodega ;
		ELSE
			DELETE FROM stock_bodtmp WHERE iditem=_cIdItem ;
		END IF;
	END IF;

	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;

	IF _isAll THEN
		IF _lbod THEN
			DELETE FROM stockbod WHERE idBodega=_idbodega;
			insert into stockbod (iditem,idbodega,cantidad) select iditem,idbodega,q from stock_bodtmp WHERE idBodega=_idbodega;
		ELSE
			truncate table stockbod ;	
			insert into stockbod (iditem,idbodega,cantidad) select iditem,idbodega,q from stock_bodtmp ;
		end if;
	ELSE 

		IF _lbod THEN
		
			SELECT @count:=COUNT(*) FROM stock_bodtmp WHERE iditem=_cIdItem AND idBodega=_idbodega ;
			
			IF @count>0 THEN
				SELECT @count:=COUNT(*) FROM stockbod WHERE iditem=_cIdItem AND idBodega=_idbodega ;
				IF @count>0 THEN
					 UPDATE stockbod SET cantidad=@q where iditem=_cIdItem AND idBodega=_idbodega ;
				ELSE
					 insert into stockbod (iditem,idbodega,cantidad) select iditem,idbodega,q from stock_bodtmp  WHERE iditem=_cIdItem AND idBodega=_idbodega  ;
				END IF;
			END IF;
			
			
		else
			DELETE FROM stockbod WHERE iditem=_cIdItem ;
			insert into stockbod (iditem,idbodega,cantidad) select iditem,idbodega,q from stock_bodtmp  WHERE iditem=_cIdItem ;
		end if;
		
		
	END IF;

END
;;
DELIMITER ;




DROP PROCEDURE IF EXISTS fixRef ;
DELIMITER ;;
CREATE PROCEDURE fixRef ()
BEGIN
	UPDATE entry SET auxiliar = 1 where idind in (select identry from auxiliar where identry=entry.idind) ;
	UPDATE entry SET auxiliar = 0 where idind not in (select identry from auxiliar where identry=entry.idind) ;
	UPDATE entry SET idreferente = (select idreferente from auxiliar as a where a.identry=entry.idInd limit 1) where auxiliar=1;
	UPDATE bookentry SET idreferente = (select idreferente from entry where entry.id=bookentry.id limit 1) ;
END
;;
DELIMITER ;



DROP PROCEDURE IF EXISTS mov_compras ;
DELIMITER ;;
CREATE PROCEDURE mov_compras (
IN _cIdItem VARCHAR(12),
IN _fcorte VARCHAR(8),
IN _idbodega VARCHAR(8))
BEGIN
	DECLARE _cargarCompPend INT;
	SET _cargarCompPend 	= (SELECT value=".T." FROM Setup WHERE Id = "cargarCompPend");
	
	SET @sql   = '
	SELECT B.iditem, SUM(B.quantityIn) as q 
		,sum(B.quantityIn*(B.price-B.descunit)) as c
	FROM Po A INNER JOIN PoDet B ON A.Id=B.Id 
	WHERE A.void=0 and a.transito=0 and A.tipoComprobante!="04" 
	';
	
	IF !_cargarCompPend THEN
		SET @sql = concat(@sql, " and A.Archivado = 1  ");
	END IF;
	
	IF (TRIM(_idbodega)!="") THEN
		SET @sql = concat(@sql, " and A.idbodega = '",_idbodega,"'");
	END IF;
	
	SET @sql = concat(@sql, " and B.iditem = '",_cIdItem,"'");
	SET @sql = concat(@sql, " and A.date<=",_fcorte," ");
	SET @sql = concat(@sql, " GROUP BY 1 ");
	
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END
;;
DELIMITER ;

DROP PROCEDURE IF EXISTS mov_compras_dev ;
DELIMITER ;;
CREATE PROCEDURE mov_compras_dev (
IN _cIdItem VARCHAR(12),
IN _fcorte VARCHAR(8),
IN _idbodega VARCHAR(8))
BEGIN
	DECLARE _cargarCompPend INT;
	SET _cargarCompPend 	= (SELECT value=".T." FROM Setup WHERE Id = "cargarCompPend");
	
	SET @sql   = '
	SELECT B.iditem, SUM(B.quantityIn) as q
	,sum(B.quantityIn*(B.price-B.descunit)) as c
	FROM Po A INNER JOIN PoDet B ON A.Id=B.Id 
	WHERE A.void=0 and a.transito=0 and A.tipoComprobante="04" 
	';
	
	IF !_cargarCompPend THEN
		SET @sql = concat(@sql, " and A.Archivado = 1  ");
	END IF;
	
	IF (TRIM(_idbodega)!="") THEN
		SET @sql = concat(@sql, " and A.idbodega = '",_idbodega,"'");
	END IF;
	
	SET @sql = concat(@sql, " and B.iditem = '",_cIdItem,"'");
	SET @sql = concat(@sql, " and A.date<=",_fcorte," ");
	SET @sql = concat(@sql, " GROUP BY 1 ");
	
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END
;;
DELIMITER ;

DROP PROCEDURE IF EXISTS mov_ventas ;
DELIMITER ;;
CREATE PROCEDURE mov_ventas (
IN _cIdItem VARCHAR(12),
IN _fcorte VARCHAR(8),
IN _idbodega VARCHAR(8))
BEGIN
	DECLARE _cargarCompPend INT;
	DECLARE _lNEBajaStock INT;
	SET _cargarCompPend 	= (SELECT value=".T." FROM Setup WHERE Id = "cargarCompPend");
	SET _lNEBajaStock 		= (SELECT value=".T." FROM Setup WHERE Id = "lNEBajaStock");
	
	SET @sql   = '
	SELECT B.iditem, SUM(B.quantity) as q
	,sum(B.quantity*B.cost) as c
	FROM Invoice A INNER JOIN InvoiceDet B ON A.Id=B.Id 
	WHERE A.void=0 and A.tipo!="06" 
	';
	
	IF !_cargarCompPend THEN
		SET @sql = concat(@sql, " and A.Archivado = 1  ");
	END IF; 
	
	IF _lNEBajaStock THEN
		SET @sql = concat(@sql," and A.agrupar=0 ");
	ELSE 
		SET @sql = concat(@sql," and A.facturado=0 ");
	END IF;
	
	IF (TRIM(_idbodega)!="") THEN
		SET @sql = concat(@sql, " and A.idbodega = '",_idbodega,"'");
	END IF;
	
	SET @sql = concat(@sql, " and B.iditem = '",_cIdItem,"'");
	SET @sql = concat(@sql, " and A.date<=",_fcorte," ");
	SET @sql = concat(@sql, " GROUP BY 1 ");
	
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END
;;
DELIMITER ;

DROP PROCEDURE IF EXISTS mov_consig_dev ;
DELIMITER ;;
CREATE PROCEDURE mov_consig_dev (
IN _cIdItem VARCHAR(12),
IN _fcorte VARCHAR(8),
IN _idbodega VARCHAR(8))
BEGIN
	DECLARE _cargarCompPend INT;
	DECLARE _lNEBajaStock INT;
	SET _cargarCompPend 	= (SELECT value=".T." FROM Setup WHERE Id = "cargarCompPend");
	SET _lNEBajaStock 		= (SELECT value=".T." FROM Setup WHERE Id = "lNEBajaStock");
	
	SET @sql   = '
	SELECT B.iditem, SUM(B.quantity) as q
	,sum(B.quantity*B.cost) as c	
	FROM Invoice A INNER JOIN InvoiceDet B ON A.Id=B.Id 
	WHERE A.void=0 and A.tipo="06" 
	';
	
	IF !_cargarCompPend THEN
		SET @sql = concat(@sql, " and A.Archivado = 1  ");
	END IF; 
	
	IF _lNEBajaStock THEN
		SET @sql = concat(@sql," and A.agrupar=0 ");
	ELSE 
		SET @sql = concat(@sql," and A.facturado=0 ");
	END IF;
	
	IF (TRIM(_idbodega)!="") THEN
		SET @sql = concat(@sql, " and A.idbodega = '",_idbodega,"'");
	END IF;
	
	SET @sql = concat(@sql, " and B.iditem = '",_cIdItem,"'");
	SET @sql = concat(@sql, " and A.date<=",_fcorte," ");
	SET @sql = concat(@sql, " GROUP BY 1 ");
	
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END
;;
DELIMITER ;


DROP PROCEDURE IF EXISTS mov_ventas_dev ;
DELIMITER ;;
CREATE PROCEDURE mov_ventas_dev (
IN _cIdItem VARCHAR(12),
IN _fcorte VARCHAR(8),
IN _idbodega VARCHAR(8))
BEGIN	
	SET @sql   = '
	SELECT B.iditem, SUM(B.quantity) as q
	,sum(B.quantity*B.costoventa) as c	
	FROM ndc A INNER JOIN ndcDet B ON A.Id=B.Id 
	WHERE A.void=0 AND A.noinvent=0 AND A.type_doc=1 
	';

	IF (TRIM(_idbodega)!="") THEN
		SET @sql = concat(@sql, " and A.idbodega = '",_idbodega,"'");
	END IF;
	
	SET @sql = concat(@sql, " and B.iditem = '",_cIdItem,"'");
	SET @sql = concat(@sql, " and A.date<=",_fcorte," ");
	SET @sql = concat(@sql, " GROUP BY 1 ");
	
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END
;;
DELIMITER ;

DROP PROCEDURE IF EXISTS mov_entradas ;
DELIMITER ;;
CREATE PROCEDURE mov_entradas (
IN _cIdItem VARCHAR(12),
IN _fcorte VARCHAR(8),
IN _idbodega VARCHAR(8))
BEGIN
	DECLARE _ltransf_noinvent INT;
	SET _ltransf_noinvent 	= (SELECT value=".T." FROM Setup WHERE Id = "ltransf_noinvent");

	SET @sql   = '
	SELECT B.iditem, SUM(B.quantity) as q 
	,sum(B.quantity*B.price) as c
	FROM KardexIn A INNER JOIN KardexInDet B ON A.Id=B.Id  
	WHERE A.void=0 AND A.state = 1 
	';
	
	IF (TRIM(_idbodega)!="") THEN
		SET @sql = concat(@sql, " and A.idbodega = '",_idbodega,"'");
	END IF;
	
	IF _ltransf_noinvent THEN
		SET @sql = concat(@sql," and !A.transferencia ");
	END IF;

	SET @sql = concat(@sql, " and B.iditem = '",_cIdItem,"'");
	SET @sql = concat(@sql, " and A.date<=",_fcorte," ");
	SET @sql = concat(@sql, " GROUP BY 1 ");
	
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END
;;
DELIMITER ;

DROP PROCEDURE IF EXISTS mov_salidas ;
DELIMITER ;;
CREATE PROCEDURE mov_salidas (
IN _cIdItem VARCHAR(12),
IN _fcorte VARCHAR(8),
IN _idbodega VARCHAR(8))
BEGIN
	DECLARE _ltransf_noinvent INT;
	SET _ltransf_noinvent 	= (SELECT value=".T." FROM Setup WHERE Id = "ltransf_noinvent");

	SET @sql   = '
	SELECT B.iditem, SUM(B.quantity) as q
	,sum(B.quantity*B.price) as c
	FROM KardexOut A INNER JOIN KardexOutDet B ON A.Id=B.Id 
	WHERE A.void=0 AND A.state = 1 
	';
	
	IF _ltransf_noinvent THEN
		SET @sql = concat(@sql," and !A.transferencia ");
	END IF;
	
	IF (TRIM(_idbodega)!="") THEN
		SET @sql = concat(@sql, " and A.idbodega = '",_idbodega,"'");
	END IF;

	SET @sql = concat(@sql, " and B.iditem = '",_cIdItem,"'");
	SET @sql = concat(@sql, " and A.date<=",_fcorte," ");
	SET @sql = concat(@sql, " GROUP BY 1 ");
	
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END
;;
DELIMITER ;

DROP PROCEDURE IF EXISTS mov_consumos ;
DELIMITER ;;
CREATE PROCEDURE mov_consumos (
IN _cIdItem VARCHAR(12),
IN _fcorte VARCHAR(8),
IN _idbodega VARCHAR(8))
BEGIN

	SET @sql   = '
	SELECT B.iditem, SUM(B.quantity) as q
	,sum(B.quantity*B.price) as c
	FROM ConsInterno A INNER JOIN ConsInternoDet B ON A.Id=B.Id
	WHERE A.void=0 AND A.state = 1 
	';
	
	IF (TRIM(_idbodega)!="") THEN
		SET @sql = concat(@sql, " and A.idbodega = '",_idbodega,"'");
	END IF;
	
	SET @sql = concat(@sql, " and B.iditem = '",_cIdItem,"'");
	SET @sql = concat(@sql, " and A.date<=",_fcorte," ");
	SET @sql = concat(@sql, " GROUP BY 1 ");
	
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END
;;
DELIMITER ;


DROP PROCEDURE IF EXISTS mov_comandas ;
DELIMITER ;;
CREATE PROCEDURE mov_comandas (
IN _cIdItem VARCHAR(12),
IN _fcorte VARCHAR(8),
IN _idbodega VARCHAR(8))
BEGIN

	SET @sql   = '
	SELECT B.iditem, SUM(B.quantity) as q 
	,sum(B.quantity*B.price) as c
	FROM Hcomanda AS B
	WHERE B.void=0 AND B.idrescomanda=""
	';
	
	IF (TRIM(_idbodega)!="") THEN
		SET @sql = concat(@sql, " and B.idbodega = '",_idbodega,"'");
	END IF;
	
	SET @sql = concat(@sql, " and B.iditem = '",_cIdItem,"'");
	SET @sql = concat(@sql, " and B.date<=",_fcorte," ");
	SET @sql = concat(@sql, " GROUP BY 1 ");
	
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END
;;
DELIMITER ;

DROP PROCEDURE IF EXISTS mov_restcomandas ;
DELIMITER ;;
CREATE PROCEDURE mov_restcomandas (
IN _cIdItem VARCHAR(12),
IN _fcorte VARCHAR(8),
IN _idbodega VARCHAR(8))
BEGIN

	SET @sql   = '
	SELECT B.iditem, SUM(B.quantity) as q 
	,sum(B.quantity*B.price) as c
	FROM rescomanda AS B
	WHERE B.void=0 AND B.idinvoice=""
	';
	
	IF (TRIM(_idbodega)!="") THEN
		SET @sql = concat(@sql, " and B.idbodega = '",_idbodega,"'");
	END IF;
	
	SET @sql = concat(@sql, " and B.iditem = '",_cIdItem,"'");
	SET @sql = concat(@sql, " and B.date<=",_fcorte," ");
	SET @sql = concat(@sql, " GROUP BY 1 ");
	
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END
;;
DELIMITER ;

DROP PROCEDURE IF EXISTS mov_transf_ent ;
DELIMITER ;;
CREATE PROCEDURE mov_transf_ent (
IN _cIdItem VARCHAR(12),
IN _fcorte VARCHAR(8),
IN _idbodega VARCHAR(8))
BEGIN

	SET @sql   = '
	SELECT B.iditem, SUM(B.quantity) as q
	,sum(B.quantity*B.cost) as c
	FROM transfer A INNER JOIN transferDet B ON A.Id=B.Id
	WHERE A.void=0 AND A.state = 1 
	';
	
	IF (TRIM(_idbodega)!="") THEN
		SET @sql = concat(@sql, " and A.idbodto = '",_idbodega,"'");
	END IF;
	
	SET @sql = concat(@sql, " and B.iditem = '",_cIdItem,"'");
	SET @sql = concat(@sql, " and A.date<=",_fcorte," ");
	SET @sql = concat(@sql, " GROUP BY 1 ");
	
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
	
END
;;
DELIMITER ;

DROP PROCEDURE IF EXISTS mov_transf_sal ;
DELIMITER ;;
CREATE PROCEDURE mov_transf_sal (
IN _cIdItem VARCHAR(12),
IN _fcorte VARCHAR(8),
IN _idbodega VARCHAR(8))
BEGIN

	SET @sql   = '
	SELECT B.iditem, SUM(B.quantity) as q
	,sum(B.quantity*B.cost) as c
	FROM transfer A INNER JOIN transferDet B ON A.Id=B.Id
	WHERE A.void=0 AND A.state = 1 
	';
	
	IF (TRIM(_idbodega)!="") THEN
		SET @sql = concat(@sql, " and A.idbodFrom = '",_idbodega,"'");
	END IF;
	
	SET @sql = concat(@sql, " and B.iditem = '",_cIdItem,"'");
	SET @sql = concat(@sql, " and A.date<=",_fcorte," ");
	SET @sql = concat(@sql, " GROUP BY 1 ");
	
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
	
END
;;
DELIMITER ;


DROP PROCEDURE IF EXISTS StockItemBodega;
DELIMITER ;;
CREATE PROCEDURE StockItemBodega(
IN _cIdItem VARCHAR(12),
IN _fcorte VARCHAR(8),
IN _idbodega VARCHAR(8)
)

BEGIN 	

	DECLARE _isAll INT;
	DECLARE _lbod INT;
	DECLARE _cargarCompPend INT;
	DECLARE _lNEBajaStock INT;
  	DECLARE _ltransf_noinvent INT;
	
	SET _isAll 				= (TRIM(_cIdItem)="");
	
	SET _lbod 				= (TRIM(_idbodega)!="");
	
	

	SET _lNEBajaStock 		= (SELECT value=".T." FROM Setup WHERE Id = "lNEBajaStock");
	SET _ltransf_noinvent 	= (SELECT value=".T." FROM Setup WHERE Id = "ltransf_noinvent");
	SET _cargarCompPend 	= (SELECT value=".T." FROM Setup WHERE Id = "cargarCompPend");

	SET @sumac  = "SELECT B.iditem, A.idBodega, SUM(B.quantityIn)  as q FROM ";
	SET @suma   = "SELECT B.iditem, A.idBodega, SUM(B.quantity)    as q FROM ";
	SET @restac = "SELECT B.iditem, A.idBodega, -SUM(B.quantityIn) as q FROM ";
	SET @resta  = "SELECT B.iditem, A.idBodega, -SUM(B.quantity)   as q FROM ";


	SET @_where = " WHERE A.void=0 ";
	
	IF _lbod THEN
		SET @_where = concat(@_where, " and A.idbodega='",_idbodega,"' ");
	
	END IF;	
	
	
	SET @citem  = concat(" and B.iditem = '",_cIdItem,"'");
	IF !_isAll THEN
		SET @_where = concat(@_where, @citem );
	END IF;
	
	
	
	SET @ccorte = " ";
	IF TRIM(_fcorte)!="" THEN
		SET @ccorte = concat(" and B.date<='",_fcorte,"' ");
		SET @_where = concat(@_where, " and A.date<='",_fcorte,"' ");
	END IF;

	SET @condPo = " and a.transito=0 ";
	IF !_cargarCompPend THEN
		SET @condPo = concat(@condPo, "  and A.Archivado = 1  ");
	END IF; 

	SET @condI = " ";

	IF !_cargarCompPend THEN
		SET @condI = concat(@condI," and A.Archivado = 1 ");
	END IF;
	
	IF _lNEBajaStock THEN
		SET @condI = concat(@condI," and A.agrupar=0 ");
	ELSE 
		SET @condI = concat(@condI," and A.facturado=0 ");
	END IF;
	
	SET @condK = " and A.state = 1 " ;
	IF _ltransf_noinvent THEN
		SET @condK = concat(@condK," and !A.transferencia ");
	END IF;

	SET @gby  = " GROUP BY 1,2 ";
	
	SET @condBodega = ' ';
	
	SET @bodTo := " SELECT B.iditem, A.idBodTo as idbodega, SUM(B.quantity) as q FROM Transfer A 
	inner join TransferDet B ON A.id=B.id ";

	SET @bodFrom := " SELECT B.iditem, A.idBodFrom as idbodega, SUM(-B.quantity) as q FROM Transfer A 
	inner join TransferDet B ON A.id=B.id ";


	IF _lbod THEN
		SET @bodTo := concat(@bodTo ," where A.idbodto='",_idbodega,"' ", @citem );
		SET @bodFrom := concat(@bodFrom ," where A.idBodFrom='",_idbodega,"' ", @citem );

	END IF;


	SET @bodTo := concat(@bodTo ," and !A.void AND A.state and !A.transito ");
	SET @bodFrom := concat(@bodFrom ," and !A.void AND A.state and !A.transito ");



	SET @s_po   = concat(@sumac,  " Po A INNER JOIN PoDet B ON A.Id=B.Id ", 					@_where, @condPo,' and A.tipoComprobante!="04" ');
	SET @s_ponc = concat(@restac, " Po A INNER JOIN PoDet B ON A.Id=B.Id  ",					@_where, @condPo,' and A.tipoComprobante ="04" and A.tiponcd=1 ');
	SET @s_invo = concat(@resta,  " Invoice A INNER JOIN InvoiceDet B ON A.Id=B.Id ", 			@_where, @condI, ' and A.tipo != "06" ');
	SET @s_invd = concat(@suma,   " Invoice A INNER JOIN InvoiceDet B ON A.Id=B.Id ",			@_where, @condI, ' and A.tipo = "06" ');
	SET @s_kin  = concat(@suma,   " KardexIn A INNER JOIN KardexInDet B ON A.Id=B.Id ", 		@_where, @condK);
	SET @s_kout = concat(@resta,  " KardexOut A INNER JOIN KardexOutDet B ON A.Id=B.Id ", 		@_where, @condK);
	SET @s_ndc  = concat(@suma,   " ndc A INNER JOIN ndcDet B ON A.Id=B.Id ", 					@_where, " AND A.noinvent=0 AND A.type_doc=1  ");
	SET @s_cint = concat(@resta,  " ConsInterno A INNER JOIN ConsInternoDet B ON A.Id=B.Id ", 	@_where, " and A.state = 1" );
	
	SET @s_Hcmd = concat("SELECT B.iditem, B.idbodega, SUM(-B.quantity) as q FROM Hcomanda AS B ",   "WHERE b.void=0 "," and B.idbodega='",_idbodega,"' ",@citem,@ccorte," AND B.idrescomanda='' ");
	SET @s_Rcmd = concat("SELECT B.iditem, B.idbodega, SUM(-B.quantity) as q FROM rescomanda AS B ", "WHERE b.void=0 "," and B.idbodega='",_idbodega,"' ",@citem,@ccorte," AND B.idinvoice='' ");
	
				
	IF TRIM(_fcorte)!="" THEN
		SET @sql1= concat(@sql1," and A.date<='",_fcorte,"' ") ;
	END IF;


	SET @sql := concat("SELECT iditem, idbodega, SUM(q) as q FROM ( ");
	SET @sql := concat(@sql, @s_po, 					@gby);
	SET @sql := concat(@sql, " union all ", @s_ponc,	@gby);
	SET @sql := concat(@sql, " union all ", @s_invo,	@gby);
	SET @sql := concat(@sql, " union all ", @s_kin,		@gby);
	SET @sql := concat(@sql, " union all ", @s_kout,	@gby);
	SET @sql := concat(@sql, " union all ", @s_ndc,		@gby);
	SET @sql := concat(@sql, " union all ", @s_cint,	@gby);
	SET @sql := concat(@sql, " union all ", @s_invd,	@gby);
	SET @sql := concat(@sql, " union all ", @s_Hcmd,	@gby);
	SET @sql := concat(@sql, " union all ", @s_Rcmd,	@gby);
	SET @sql := concat(@sql, " union all ", @bodTo,		@gby);
	SET @sql := concat(@sql, " union all ", @bodFrom ,	@gby);
	SET @sql := concat(@sql, " ) AS Result ",       	@gby);
	
	
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;

	

END
;;
DELIMITER ;
