

PUBLIC oStock

oStock = CREATEOBJECT ('StockClass')



DEFINE CLASS StockClass AS CUSTOM 

l_incluye_pedido = .F.

FUNCTION computeBodega 
PARAMETERS __cIdItem, __Fecha, __NocalcStock,cidbodcalcular, __NoGraba
	note: viene con valor .t. si es que se quiere reconstruir solo de una bodega especifica 
	
	&&oBodega.computeBodega(,,,.f.) 
	&&oBodega.computeBodega(__cIdItem,__Fecha,__NocalcStock,.f.) 
	
	cCondItem = ' '
	
	ccondbod = ' '
	ccondbodfrom = ''
	ccondbodto = ''

	lAll = .T.
	__lFecha = .f.
	
	lGraba = .t.
	
	
	IF !EMPTY(__NoGraba)
		lGraba=.f.
	ENDIF
	
	
	IF EMPTY(__NocalcStock)
		__NocalcStock = .f.
	ENDIF
	
	IF !EMPTY(__cIdItem)
		lAll = .F.
		cCondItem = ' and D.iditem="?__cIdItem" '
		cCmd = 'UPDATE StockBod SET salida=0.0,entrada=0.0, cantidad=0.0,costo=0.0, total=0.0 WHERE iditem=?__cIdItem '
		IF lGraba
			oApp.sqlexec(cCmd)
		ENDIF
		
	ELSE
		
		cCmd = 'UPDATE StockBod SET salida=0.0,entrada=0.0, cantidad=0.0,costo=0.0, total=0.0'
		oApp.sqlexec(cCmd)
		this.MIB_cursorStock()
		
	ENDIF
	
	IF !EMPTY(__Fecha)
		cCondItem = cCondItem +  ' and date<=?__Fecha '
		__lFecha = .t.
	ENDIF
	IF !EMPTY(cidbodcalcular)
		ccondbod = ' and i.idbodega=?cidbodcalcular '
		ccondbodfrom = ' and i.idbodfrom=?cidbodcalcular '
		ccondbodto = ' and i.idbodto=?cidbodcalcular '
	ENDIF 

	IF lAll
		*oApp.crono(0)	
		*WAIT WINDOW 'Calculando inventario...' nowait
	ENDIF
	

	
	cCondPendiente = ' and I.archivado = 1 '
	
	IF .t. &&oApp.cargarCompPend
		cCondPendiente = '  '
	ENDIF
	lNEBajaStock = oApp.Setup('lNEBajaStock','L')
	
	cCondStockNE = ' and i.facturado = 0 '
	IF lNEBajaStock
		cCondStockNE = ' and i.agrupar=0 '
	ENDIF

	oApp.sqlexec('SELECT I.idBodega, D.idItem, SUM(D.quantity) as n  '+;
					'FROM PO as I inner join PoDet as D ON I.id=D.id '+;
					'WHERE !I.void AND I.tipoComprobante!="04" '+cCondPendiente+cCondItem+ccondbod+' GROUP BY 1,2 ', 		'curCompra')	

	oApp.sqlexec('SELECT I.idBodTo as IdBodega, D.idItem, SUM(D.quantity) as n '+;
					'FROM Transfer as I inner join TransferDet as D ON I.id=D.id '+;
					'WHERE !I.void AND I.state '+cCondItem+ccondbodto+'GROUP BY 1,2 ','curCompra2')	

	oApp.sqlexec('SELECT I.IdBodega, D.idItem, SUM(D.quantity) as n '+;
					'FROM KardexIn as I inner join KardexInDet as D ON I.id=D.id '+;
					'WHERE !I.void AND I.state '+cCondItem+ccondbod+'GROUP BY 1,2 ',									'curCompra3')

	oApp.sqlexec('SELECT I.idBodega, D.idItem, SUM(D.quantity) as n '+;
					'FROM NDC as I inner join NDCDet as D ON I.id=D.id '+;
					'WHERE I.type_doc = 1 and I.void=0 AND I.noinvent=0 '+cCondItem+ccondbod+' GROUP BY 1,2 ',		'curCompra4')

	oApp.sqlexec('SELECT I.IdBodega, D.idItem, SUM(D.quantity) as n '+;
					'FROM INVOICE as I inner join InvoiceDet as D ON I.id=D.id '+;
					'WHERE !I.void and I.tipo = "06" '+cCondItem+ccondbod+cCondStockNE+' GROUP BY 1,2 ',		'curCompra5')
	
	IF lAll
		*WAIT WINDOW 'Calculando salidas...' nowait
	ENDIF 	
	IF oApp.setup('lbodegasdetalleventas','L')						
		oApp.sqlexec('SELECT D.IdBodega, D.idItem,  -SUM(D.quantity) as n '+;
						'FROM INVOICE as I inner join InvoiceDet as D ON I.id=D.id '+;
						'WHERE !I.void and I.tipo != "06" '+cCondItem+ccondbod+cCondStockNE+cCondPendiente+'GROUP BY 1,2 ',		'curVenta')
	ELSE
		oApp.sqlexec('SELECT I.IdBodega, D.idItem,  -SUM(D.quantity) as n '+;
						'FROM INVOICE as I inner join InvoiceDet as D ON I.id=D.id '+;
						'WHERE !I.void and I.tipo != "06" '+cCondItem+ccondbod+cCondStockNE+cCondPendiente+'GROUP BY 1,2 ',		'curVenta')
	ENDIF 	

	oApp.sqlexec('SELECT I.idBodFrom as IdBodega, D.idItem, -SUM(D.quantity) as n '+;
					'FROM Transfer as I inner join TransferDet as D ON I.id=D.id '+;
					'WHERE !I.void AND I.state '+cCondItem+ccondbodfrom+'GROUP BY 1,2 ',									'curVenta2')

	oApp.sqlexec('SELECT I.idBodega , D.idItem, -SUM(D.quantity) as n '+;
					'FROM KardexOut as I inner join KardexOutDet as D ON I.id=D.id '+;
					'WHERE !I.void AND I.state '+cCondItem+ccondbod+'GROUP BY 1,2 ',									'curVenta3')
			
	oApp.sqlexec('SELECT I.idBodega, D.idItem, -SUM(D.quantity) as n '+;
					'FROM po as I inner join PoDet as D ON I.id=D.id '+;
					'WHERE !I.void and I.tipoComprobante="04" '+cCondPendiente+cCondItem+ccondbod+'  GROUP BY 1,2 ',	'curVenta4')			
			
	oApp.sqlexec('SELECT I.idBodega, D.idItem, -SUM(D.quantity) as n '+;
					'FROM ConsInterno as I inner join ConsInternoDet as D ON I.id=D.id '+;
					'WHERE !I.void AND I.state '+cCondItem+ccondbod+' GROUP BY 1,2 ',								'curVenta5')

	oApp.sqlexec('SELECT IdBodega, idItem, -SUM(quantity) as n '+;
					'FROM hcomanda  as d '+;
					'WHERE !void and d.idrescomanda = "" '+cCondItem+' GROUP BY 1,2 ',												'curVenta6')
	
	IF oApp.setup('linventariorecetas','L')
		oApp.sqlexec('SELECT IdBodega, idItem, -SUM(quantity) as n '+;
						'FROM rescomanda  as d '+;
						'WHERE !void '+cCondItem+' GROUP BY 1,2 ',											'curVenta7')
	ELSE
		oApp.sqlexec('SELECT IdBodega, idItem, -SUM(quantity) as n '+;
					'FROM rescomanda  as d '+;
					'WHERE !void and iditem ="xxxxxxxxxxxx"'+' GROUP BY 1,2 ',								'curVenta7')
	ENDIF 			



	IF this.l_incluye_pedido
	
		oApp.sqlexec('SELECT d.IdBodega, d.idItem, -SUM(d.quantity) as n '+;
						'FROM basketdet as d inner join basket as b on b.id=d.id '+;
						'WHERE !b.void and !b.facturadopda '+cCondItem+' GROUP BY 1,2 ',											'curVenta8')
	ELSE
		oApp.sqlexec('SELECT IdBodega, idItem, -SUM(quantity) as n '+;
					'FROM basketdet as d '+;
					'WHERE iditem ="xxxxxxxxxxxx"'+' GROUP BY 1,2 ',								'curVenta8')
	ENDIF 	

	SELECT 				 ALL * FROM curCompra;
		UNION ALL SELECT all * FROM curCompra2 ;
		UNION ALL SELECT all * FROM curCompra3 ;
		UNION ALL SELECT all * FROM curCompra4 ;
		UNION ALL SELECT all * FROM curCompra5 ;
		UNION ALL SELECT all * FROM curVenta ;
		UNION ALL SELECT all * FROM curVenta2 ;
		UNION ALL SELECT all * FROM curVenta3 ;
		UNION ALL SELECT all * FROM curVenta4 ;
		UNION ALL SELECT all * FROM curVenta5 ;
		UNION ALL SELECT all * FROM curVenta6 ;
		UNION ALL SELECT all * FROM curVenta7 ;
		UNION ALL SELECT all * FROM curVenta8 ;
		INTO CURSOR curEntSal0
	

	
	IF _tally=0
		RETURN 0
	ENDIF

	
	SELECT idbodega, iditem, SUM(IIF(n>0,n,0))+00000000.00 as ent, -SUM(iif(n<0,n,0))+00000000.00 as sal ;
		FROM curEntSal0 INTO CURSOR curEntSal GROUP BY 1,2

	nTotalRec = _tally
	nEnt = 0	
	nSal = 0								
	
	IF nTotalRec>0
		IF lAll
			*WAIT WINDOW 'Actualizando inventario...' nowait
		ENDIF
		

		nBodCount = 0		
		SELECT curEntSal
		GO TOP

		SCAN all
			IF lAll
				*WAIT WINDOW trans(ROUND(RECNO()/nTotalRec*100,0))+'%' NOWAIT
				IF INKEY() #0
					IF INKEY(0)=27
						IF messagebox('Terminar proceso',32+4,oApp.cModule)=6
							return
						ENDIF
					ENDIF
				ENDIF
			ENDIF	
			cIdBodega = curEntSal.idBodega
			cIdItem   = curEntSal.idItem
			
			nCos = this.MIB_getCost (cIdItem)
			nEnt = curEntSal.Ent
			nSal = curEntSal.Sal		
			
			nBodCount = nBodCount + 1
			
		
			IF lGraba
				this.MIB_set(cIdBodega, cIdItem, nEnt, nSal, nCos)
			ENDIF
			
			IF !__NocalcStock					
				*this.stockItem(cidItem)
			ENDIF
			SELECT curEntSal
		ENDSCAN		
	ENDIF
	IF lAll
		*oApp.crono(9)
	ENDIF
	RETURN nEnt-nSal
ENDFUNC


****
**
*  	MANEJO DE INVENTARIOS DE BODEGAS.
*

FUNCTION MIB_cursorStock(cCosto)

	IF EMPTY(cCosto)
		cCosto = 'CostAVG'
	ENDIF
	

	oApp.sqlexec('SELECT iditem, '+cCosto+' as costo FROM Stock ','_curStockBodega_')
	SELECT _curStockBodega_
	INDEX on IdItem TAG uno
	SET ORDER TO uno
	
ENDFUNC

FUNCTION MIB_exist 
	PARAMETERS cIdBodega,cIdItem
	RETURN oApp.sqlexec('SELECT entrada,salida,cantidad,costo '+;
					    'FROM StockBod WHERE idBodega=?cIdbodega and idItem="?cIdItem"','curExistItemBod')>0
ENDFUNC

FUNCTION MIB_getCost (cIdItem)
LOCAL nCos
	IF !USED('_curStockBodega_')
		this.MIB_cursorStock()
	ENDIF

	nCos = 0
	SELECT _curStockBodega_
	SEEK cIdItem   
	IF !EOF()
		nCos = _curStockBodega_.costo
	ENDIF
	RETURN nCos
ENDFUNC

FUNCTION MIB_update (cIdBodega,cIdItem,nEnt,nSal,nCos,nDif,nTot)
	oApp.t_clear()
	oApp.t_set('entrada',nEnt)
	oApp.t_set('salida',nSal)
	oApp.t_set('cantidad',nDif)
	oApp.t_set('costo',nCos)
	oApp.t_set('Total',nTot )
	&&oApp.t_set('ya',0)
	oApp.t_update('StockBod',,'idBodega="'+cIdbodega+'" and idItem="'+cIdItem+'"')
	
ENDFUNC

FUNCTION MIB_insert (cIdBodega,cIdItem,nEnt,nSal,nCos,nDif,nTot)
	oApp.t_clear()
	oApp.t_set('idBodega',cIdbodega)
	oApp.t_set('idItem',cIdItem)
	oApp.t_set('entrada',nEnt)
	oApp.t_set('salida',nSal)
	oApp.t_set('cantidad',nDif)
	oApp.t_set('costo',nCos)
	oApp.t_set('Total',nTot )
	&&oApp.t_set('ya',0)
	oApp.t_insert('StockBod')
ENDFUNC


FUNCTION MIB_set(cIdBodega, cIdItem, nEnt, nSal, nCos)
	IF this.MIB_exist (cIdBodega,cIdItem)
		this.MIB_update (cIdBodega,cIdItem,nEnt,nSal,nCos,nEnt-nSal,(nEnt-nSal)*nCos)
	ELSE
		this.MIB_insert (cIdBodega,cIdItem,nEnt,nSal,nCos,nEnt-nSal,(nEnt-nSal)*nCos)		
	ENDIF
ENDFUNC


FUNCTION MIB_getStock (cIdBodega, cIdItem)
	IF this.MIB_exist (cIdBodega,cIdItem)

		RETURN curExistItemBod.cantidad
	ENDIF
	RETURN 0
ENDFUNC


FUNCTION actualizarStock(curNameBod)

	IF oApp.lGhost_lGhost_upstock

		SELECT &curNameBod
		GO top
		SCAN all
	*		WAIT WINDOW &curNameBod..iditem nowait
			cIdItem = &curNameBod..iditem
			this.computeBodega (cIdItem)
			oApp.sqlexec('UPDATE stock SET ya=3 WHERE idItem="?cIdItem"')
			SELECT &curNameBod
		ENDSCAN
	ENDIF
	
ENDFUNC



FUNCTION StockItem
	PARAMETERS cIdItem, cIdBodega, dCorte
	
	LOCAL lBodegas
	
	cCondCorte = ' '
	IF !EMPTY(dCorte)
		cCondCorte = ' and date<=?dCorte '
	ENDIF
	
	
	lBodegas = .F.
	
	cCondBodFrom = ' and 0 '
	cCondBodTo = ' and 0 '
	
	IF !EMPTY(cIdBodega)
	
		cCondBodFrom = cCondCorte + ' and idbodFrom= '+TRANSFORM(cIdBodega)+ ' '
		cCondBodTo   = cCondCorte + ' and idbodTo= '+TRANSFORM(cIdBodega)+ ' '
	
		cCondCorte = cCondCorte +  ' and idbodega= '+TRANSFORM(cIdBodega)+ ' '
		lBodegas = .T.
		
	ENDIF

	
	LOCAL nStock

	nStock = 0
	nalias = SELECT()

	cCondPendiente = ' and Archivado = 1 '
	
	IF .t. &&  oApp.cargarCompPend
		cCondPendiente = '  '
	ENDIF
	
	
	lNEBajaStock = oApp.Setup('lNEBajaStock','L')
	
	cCondStockNE = ' and facturado = 0 '
	IF lNEBajaStock
		cCondStockNE = ' and agrupar=0 '
	ENDIF
	
	*MESSAGEBOX(cIdItem+'|'+cIdBodega+CHR(13)+cCondCorte)
	
	cJoinBasket = ' '
	IF this.l_incluye_pedido
		cJoinBasket = ' union all SELECT SUM(-quantity) as n FROM BasketDet WHERE IdItem="'+cIdItem+'" and id in (select id from Basket where !void and !facturadopda  '+cCondCorte+') '		
	ENDIF
	
	TEXT TO cCmd NOSHOW TEXTMERGE 
SELECT SUM(quantity) as n FROM PoDet WHERE IdItem="<<cIdItem>>" and id in (select id from Po where  !void and tipoComprobante!="04" and transito=0 <<cCondPendiente>> <<cCondCorte>>)
union all
SELECT SUM(-quantity) as n FROM PoDet WHERE IdItem="<<cIdItem>>" and id in (select id from Po where  !void and tipoComprobante="04" and transito=0 and tiponcd=1 <<cCondPendiente>> <<cCondCorte>>)
union all 
SELECT SUM(-quantity) as n FROM InvoiceDet     WHERE IdItem="<<cIdItem>>" and id in (select id from invoice where  !void and tipo != "06"  and !facturado <<cCondCorte>>)
union all
SELECT SUM(quantity) as n FROM KardexInDet WHERE IdItem="<<cIdItem>>" and id in (select id from KardexIn where  !void and state = 1 <<cCondCorte>>)
union all
SELECT SUM(-quantity) as n FROM KardexOutDet WHERE IdItem="<<cIdItem>>" and id in (select id from KardexOut where  !void and state = 1 and !ncafectada <<cCondCorte>>)
union all
SELECT SUM(quantity) as n FROM ndcDet WHERE IdItem="<<cIdItem>>" and id in (select id from ndc where  !void and noinvent=0 AND type_doc=1 <<cCondCorte>>)
union all	
SELECT SUM(-quantity) as n FROM ConsInternoDet WHERE IdItem="<<cIdItem>>" and id in (select id from ConsInterno where  !void and state = 1 <<cCondCorte>>)
union all
SELECT SUM(quantity) as n FROM InvoiceDet     WHERE IdItem="<<cIdItem>>" and id in (select id from invoice where !void and tipo = "06"  <<cCondStockNE>>)
union all	
SELECT SUM(-quantity) as n FROM transferDet WHERE IdItem="<<cIdItem>>" and id in (select id from transfer where  !void and state = 1 <<cCondBodFrom>> )
union all	
SELECT SUM(quantity) as n FROM transferDet WHERE IdItem="<<cIdItem>>" and id in (select id from transfer where !void and state = 1 <<cCondBodTo>>)
<<cJoinBasket>>
	ENDTEXT
	
	
	*STRTOFILE(cCmd,'\eddy_stock.txt')
	
	oApp.sqlexec(cCmd,'curAll1')
	
	
	*SELECT curAll1
	*brow
	SELECT SUM(n) as n FROM curAll1 INTO CURSOR curAll

	nStock = curAll.n
		
	n_stockreal = IIF(ISNULL(nStock),00.0,nStock)

	
	RETURN n_stockreal 
ENDFUNC

ENDDEFINE
