PARAMETERS cReportType, cFecha, cBdd, cIdView

PUBLIC oReport, gdIni, gdFin, cATo, curJson
CLEAR 

STORE dtoc(DATE(),1) TO gdIni, gdFin

IF !EMPTY(cFecha)
	gdIni = SUBSTR(cFecha,1,8)
	gdFin = gdIni

	IF LEN(cFecha)>10
		gdFin = SUBSTR(cFecha,10,8)
	ENDIF
ENDIF

IF !EMPTY(cReportType)

	IF EMPTY(cBdd)
		cBdd='fss1'
	ENDIF

	*** Clases para el manejo de Web
	SET PATH TO ".\classes" ADDITIVE
	SET PROCEDURE TO wwHTTP ADDITIVE   
	SET PROCEDURE TO wwAPI ADDITIVE
	SET PROCEDURE TO wwFTP ADDITIVE
	SET PROCEDURE TO wwUtils ADDITIVE

	SET TALK off
	SET STATUS BAR OFF
	SET SYSMENU off
	SET DELETED on
	SET CONFIRM OFF
	SET NOTIFY off
	SET SAFETY off
	SET CENTURY ON
	SET DATE TO DMY
	SET FIXED Off
	SET DECIMALS TO 4
	_SCREEN.Visible=.T.

	DO prg\App.prg WITH cBdd
	
	oApp.sqlexec('select codigo, codigo as id from negocio limit 1','curNeg')
	cATo = ALLTRIM(curNeg.codigo)


	oReport = CREATEOBJECT ('ReportClass',cIdView)
	oReport.run(cReportType)

ENDIF


DEFINE CLASS ReportClass AS CUSTOM 


cMsgResult = ''
cIdView = ''
cTitulo = ''

idsupplier = ''
idbrand = ''
ccodigoproveedor = ''


periodo = " "
semana = " "
fecha = " "
cCondStockNE = " "

NL = CHR(13)+CHR(10)
cRuta = ''

FUNCTION init(cIdView)
	IF !EMPTY(cIdView)
		this.cIdView = cIdView
	ENDIF
ENDFUNC

FUNCTION run (cReportType)
	this.cTitulo = 'Ghost Rerport...'

	IF cReportType='01'  && familia
		this.cTitulo = 'Familia...'+CHR(13)
		
		this.idsupplier = oApp.setup('ghost_report_idsupp_01','C')
		this.idbrand = oApp.setup('ghost_report_idbrand_01','C')
		this.ccodigoproveedor = oApp.setup('ghost_report_codsupp_01','C')
		
		this.cRuta=GETDIR()
	
		*this.familia_inv ()
		this.familia_ventas ()
	ENDIF
	

	IF cReportType='02'  && colgate
		this.cTitulo = 'Colgate...'+CHR(13)
		
		this.idsupplier = oApp.setup('ghost_report_idsupp_02','C')
		this.idbrand = oApp.setup('ghost_report_idbrand_02','C')
		this.ccodigoproveedor = oApp.setup('ghost_report_codsupp_02','C')

		this.cRuta=GETDIR()
		this.colgate (gdIni, gdFin)
	ENDIF
	
	IF UPPER(cReportType)='NESTLE'  
		this.cTitulo = 'Nestle...'+CHR(13)
		this.idsupplier = oApp.setup('ghost_report_idsupp_nestle','C')
		this.ccodigoproveedor = oApp.setup('ghost_report_codsupp_nestle','C')
		this.nestle(gdIni, gdFin)
	ENDIF
	
	
	IF UPPER(cReportType)='GRUPO1'  
		this.cTitulo = 'Ventas Grupo dolares...'+CHR(13)
		this.getVentasGrupo (.T.,'ventasGrupo')
	ENDIF

	IF UPPER(cReportType)='GRUPO2'  
		this.cTitulo = 'Ventas Grupo cantidad...'+CHR(13)
		this.getVentasGrupo (.F.,'ventasGrupo')
	ENDIF


	IF UPPER(cReportType)='MARCA1'  
		this.cTitulo = 'Ventas Marca  Dolares...'+CHR(13)
		this.getVentasGrupo (.T.,'ventasMarca')
	ENDIF
	
	
	IF UPPER(cReportType)='MARCA2'  
		this.cTitulo = 'Ventas Marca Cantidad...'+CHR(13)
		this.getVentasGrupo (.F.,'ventasMarca')
	ENDIF

	*oApp.setView (this.cIdView,2)
	*oApp.setSetup('Ghost_lReportBuzy',.F.,'L')

ENDFUNC


FUNCTION nestle (gdIni, gdFin)

WAIT WINDOW 'Generando archivo de ventas, espere por favor' nowait  


TEXT TO ccad NOSHOW TEXTMERGE 

	SELECT
		"<<this.ccodigoproveedor>>" as cod_distribuidor,
		"<<oApp.cbComercio>>" as distribuidor,
		i.seriesec as cod_fac,
		i.date as fecha_fac,
		cu.codigo as cod_cliente,
		cu.name as cliente,
		cu.ruc,
		cu.address as domicilio,
		s.code as cod_vendedor, 
		s.name as nombre_vend,
		cu.city,
		it.partnumber as cod_producto,
		it.name as producto,
		d.quantity as cant_vend,
		if(!it.iva,(D.price-D.descunit)*d.quantity,0) as venta_tarifa0,
		if(it.iva,(D.price-D.descunit)*d.quantity,0) as venta_tar_grav,
		(SELECT SUM((ndcdet.cost-ndcdet.descunit)*quantity) from ndcdet inner join ndc on ndcdet.id=ndc.id where ndc.idinvoice=i.id and ndcdet.iditem=it.id) as devolucion
	    FROM InvoiceDet as D inner join invoice as I ON I.Id=D.Id 
		    inner join Item as It ON D.iditem=It.Id 
		    left join Customer as Cu ON I.IdCustomer = Cu.Id 
		    left join salesman as s on i.idsalesman = s.id 
	    WHERE !i.void and i.archivado and i.tipo<3 
      		and it.idsupplier="<<this.idsupplier>>"
	    	and I.date between <<gdIni>> and <<gdFin>>
ENDTEXT 	
*group by d.iditem,i.idcustomer 	

*STRTOFILE(CCAD,'\EDDY.TXT')

oApp.sqlexec(ccad,'cursorventas1')

cFileXLS = GETFILE('xls')

SELECT CURSORVENTAS1
COPY TO (cFileXLS) XLS 


ENDFUNC


FUNCTION familia_inv
	
	WAIT WINDOW 'Generando archivo de inventarios, espere por favor'nowait  
	
	TEXT TO cCad NOSHOW TEXTMERGE 

		SELECT st.Stock,it.id,it.codigo4,it.name as item,it.noteunidad as unidadmedida,st.costavg+0.00 as costavg 
	          from item as it inner join 
	          stock as st on it.id=st.iditem 
	          WHERE 1 
	          		and it.idbrand="<<this.idbrand>>" 
	          		and it.codigo4!=""	          
	          
	ENDTEXT 
			
	oApp.sqlexec(ccad,'cursorinventarios')
	SELECT cursorinventarios

	COUNT TO nreg
	GO TOP 

	sOut = 'RUC|FechaInventario|CodigoProducto|DescripcionProducto|CodigoEnProveedor|Cantidad|Unidad|Costo|CodigoProveedor|' + (this.NL)

	IF nreg >0
	    SELECT cursorinventarios
		SCAN ALL
			cUnidad = UPPER(this.fix1(cursorinventarios.unidadmedida))
			IF cUnidad=='CAJA' OR cUnidad=='PAQUETE'
				cUnidad = 'CJ'
			ELSE
				cUnidad = 'UND'
			ENDIF

			_iditem = cursorinventarios.id

			_stock = cursorinventarios.stock
			_costo = cursorinventarios.costavg
			
			IF oApp.SQLExec("SELECT stock,costlast FROM stockhist where iditem='"+_iditem+"' AND fecha<'"+gdFin +;
			 					"' ORDER BY fecha DESC LIMIT 1",'curStock')>0
			 					
			 	_stock = curStock.stock
				_costo = curStock.costlast		
			ENDIF
			
						
			
			sOut = sOut + this.fix1(oApp.cbruc)+'|'  
	 		sOut = sOut + DTOC(DATE())+'|'
	 		sOut = sOut + cursorinventarios.id+'|'
	 		sOut = sOut + this.fix1(cursorinventarios.item)+'|'
	 		sOut = sOut + this.fix1(cursorinventarios.codigo4)+'|'
	 		sOut = sOut + trans(ROUND(_stock,0),'9999999999')+'|'
	 		sOut = sOut + cUnidad+'|'
	 		sOut = sOut + trans(ROUND(_costo,2),'999,999.99')+'|'
	 		sOut = sOut + this.ccodigoproveedor
	 		
	 		sOut = sOut + (this.NL)
		ENDSCAN 
	ENDIF

	STRTOFILE(sOut ,this.cruta + 'f'+this.ccodigoproveedor+'i'+'.dat')

ENDFUNC


FUNCTION familia_ventas

WAIT WINDOW 'Generando archivo de ventas, espere por favor' nowait  


TEXT TO ccad NOSHOW TEXTMERGE 

	SELECT 
		i.seriesec as numero,
		i.date,
		d.iditem,
		it.name as producto,
		it.partnumber,
		d.quantity,
		it.noteunidad as unidadmedida,
		d.price,
		(D.price-D.descunit)*d.quantity as total,
		d.cost as cost,
		D.descunit*d.quantity as descunit,
		SPACE(10) as codtrans,
		SPACE(10) as DescTrans,
		s.ruc as rucsalesman, 
		s.name as nombresalesman,
		s.phone as phonesalesman,
		s.address as addresssalesman,
		sv.ruc as srucsalesman, 
		sv.name as snombresalesman,
		sv.phone as sphonesalesman,
		sv.address as saddresssalesman,
		cu.ruc,cu.name as customer,
		cu.address,cu.phone,
		cu.fechareg,
		ne.codigo as codCanal, 
		ne.name as nombCanal,
		it.codigo4, 
		Dep.codigo as codDepar, 
		Dep.name as nombDepar, 
		Cu.sector as codSector, 
		Cu.sector as nombSector, 
		z.name as zona, 
		Z.GRUPO as codzona, 
		cu.city, 
		cu.zip, 
		i.void, 
		"VT" AS tipotrans, 
		SPACE(20) as numfact_nc
	    FROM InvoiceDet as D inner join invoice as I ON I.Id=D.Id 
		    inner join Item as It ON D.iditem=It.Id 
		    left join Customer as Cu ON I.IdCustomer = Cu.Id 
		    left join Negocio as Ne ON Ne.id = Cu.IdNegocio 
		    left join Departament as Dep ON Dep.id = Cu.IdDepart 
		    left join salesman as s on i.idsalesman = s.id 
		    left join salesman as sv on s.idsupervisor = sv.id 
		    left join zonas as z on Cu.idzona = z.id 
	    WHERE i.archivado = 1 and i.tipo<3 
	        and !i.void 
      		and it.idbrand="<<this.idbrand>>" 
      		and it.codigo4!=""
	    	and I.date between <<gdIni>> and <<gdFin>>
ENDTEXT 	
*group by d.iditem,i.idcustomer 	

*STRTOFILE(CCAD,'\EDDY.TXT')

oApp.sqlexec(ccad,'cursorventas1')

TEXT TO ccad NOSHOW TEXTMERGE 
	SELECT 
		i.seriesec as numero,
		i.date,
		d.iditem,
		it.name as producto,
		it.partnumber,
		d.quantity,
		it.noteunidad as unidadmedida,
		d.cost as price,
		(D.cost-D.descunit)*d.quantity as total,
		d.cost,D.descunit,
		SPACE(10) as codtrans,
		SPACE(10) as DescTrans,
		s.ruc as rucsalesman, 
		s.name as nombresalesman,
		s.phone as phonesalesman,
		s.address as addresssalesman,
		sv.ruc as srucsalesman, 
		sv.name as snombresalesman,
		sv.phone as sphonesalesman,
		sv.address as saddresssalesman,
		cu.ruc,cu.name as customer,
		cu.address,cu.phone,
		cu.fechareg,
		ne.codigo as codCanal, 
		ne.name as nombCanal,
		it.codigo4, 
		Dep.codigo as codDepar, 
		Dep.name as nombDepar, 
		Cu.sector as codSector, 
		Cu.sector as nombSector, 
		z.name as zona, 
		Z.GRUPO as codzona, 
		cu.city, 
		cu.zip, 
		i.void, 
		"NC" AS tipotrans,
		Invoice.seriesec as numfact_nc
	    FROM NDCDet as D inner join NDC as I ON I.Id=D.Id 
		    inner join Item as It ON D.iditem=It.Id 
		    left join Customer as Cu ON I.IdCustomer = Cu.Id 
		    left join Negocio as Ne ON Ne.id = Cu.IdNegocio 
		    left join Departament as Dep ON Dep.id = Cu.IdDepart 
		    left join salesman as s on i.idsalesman = s.id 
		    left join salesman as sv on s.idsupervisor = sv.id 
		    left join zonas as z on Cu.idzona = z.id 
		    left join Invoice  ON I.idinvoice = Invoice.id
	    WHERE i.type_doc = 1  
      		and it.idbrand="<<this.idbrand>>" 
      		and it.codigo4!=""
	    	and I.date between <<gdIni>> and <<gdFin>>
ENDTEXT
**group by d.iditem,i.idcustomer 		
oApp.sqlexec(ccad,'cursorventas2')

SELECT ALL * FROM cursorventas1 UNION ALL SELECT ALL * FROM cursorventas2 INTO CURSOR cursorventas

SELECT cursorventas
COUNT TO nreg
GO TOP 

sOut =        'RUC|NumeroFactura|FechaFactura|CodProducto|DescrProducto|'      	&&  A
sOut = sOut + 'CodProveedor|Cantidad|UnidadVenta|PrecioUnitario|PrecioTotal|'  	&&  B
sOut = sOut + 'Costo|Descuento|CodTrans|DescTrans|CodCausTrans|'				&&  C
sOut = sOut + 'DescCausTrans|CedulaVend|NombreVend|TelfVend|DireccionVend|'    	&&  D
sOut = sOut + 'RucCliente|RazonSocial|DirecCliente|TelfCliente|FechaCreacion|' 	&&  E
sOut = sOut + 'FechaDesac|VisiClie|CodZona|DescZona|CodigoCiudad|'       		&&  F
sOut = sOut + 'NombreCiudad|CedulaSuperv|NombSuperv|DirecSuperv|TeleSuperv|'    &&  G
sOut = sOut + 'CodCanal|NombCanal|Depa|NombDepa|CodProveedor|'					&&  H
sOut = sOut + 'NombreProv|CodSector|DescrSector|EstaFact'						&&  I  

sOut = sOut + (this.NL)


sup_ced = oApp.setup('ghost_sup_ced','C',,,'1102667894')
sup_nom = oApp.setup('ghost_sup_nom','C',,,'SANDOYA GERRERO TRUDY')
sup_dir = oApp.setup('ghost_sup_dir','C',,,'AV. CIRCUNVALACION Y OLMEDO')
sup_fon = oApp.setup('ghost_sup_fon','C',,,'0998237429')

IF nreg >0

    SELECT cursorventas
        
	SCAN ALL
	
		cUnidad = UPPER(this.fix1(cursorventas.unidadmedida))
		IF cUnidad=='CAJA' OR cUnidad=='PAQUETE'
			cUnidad = 'CJ'
		ELSE
			cUnidad = 'UND'
		ENDIF
	
		&& A  'RUC|NumeroFactura|FechaFactura|CodProducto|DescrProducto|'
		sOut = sOut + this.fix1(oApp.cbruc)+'|' && 1 nit
		sOut = sOut + this.fix1(cursorventas.numero)+'|' &&2 numfact
		sOut = sOut + DTOC(cursorventas.date)+'|' &&3 fechafac
		sOut = sOut + this.fix1(cursorventas.partnumber)+'|' &&4 cod del producto interno
		sOut = sOut + this.fix1(cursorventas.producto)+'|' &&5 descprod
		
		
		&& B  'CodProveedor|Cantidad|UnidadVenta|PrecioUnitario|PrecioTotal|'
		
		sOut = sOut + this.fix1(cursorventas.codigo4)+'|' &&6 codprod. de familia
		sOut = sOut + TRANSFORM(cursorventas.quantity,'99999999')+'|' &&7 cant
		sOut = sOut + cUnidad +'|' &&8 unidad
		sOut = sOut + TRANSFORM(cursorventas.price,'999,999.9999')+'|' &&9 precio venta
		sOut = sOut + TRANSFORM(cursorventas.total,'999,999.9999')+'|' &&10 total
		
		&& C  'Costo|Descuento|CodTrans|DescTrans|CodCausTrans|'
		sOut = sOut + TRANSFORM(cursorventas.cost,'999,999.9999')+'|' &&11 costo
		sOut = sOut + TRANSFORM(cursorventas.descunit,'999,999.9999')+'|' &&12 desc
		sOut = sOut + cursorventas.tipoTrans+'|' &&13 codtrans
		sOut = sOut + IIF("VT" = cursorventas.tipotrans, 'VENTA FACTURA|','NOTA CREDITO|') &&14 desctrans
		sOut = sOut + IIF("NC" = cursorventas.tipotrans,cursorventas.numfact_nc, '')+'|' &&15 factura afectada por la NC
		
		&& D  'DescCausTrans|CedulaVend|NombreVend|TelfVend|DireccionVend|
		sOut = sOut + IIF("NC" = cursorventas.tipotrans,'DEVOLUCION','')+'|'  &&16 motivo
		sOut = sOut + this.fix1(cursorventas.rucsalesman)+'|' && 17 cedula ven
		sOut = sOut + this.fix1(cursorventas.nombresalesman)+'|' && 18 nombre vendedor
		sOut = sOut + this.fix1(cursorventas.phonesalesman)+'|'  && 19 telefono
		sOut = sOut + this.fix1(cursorventas.addresssalesman)+'|'&& 20 direccion
		
		&& E  'RucCliente|RazonSocial|DirecCliente|TelfCliente|FechaCreacion|'
		sOut = sOut + this.fix1(cursorventas.ruc)+'|' && 21 cedula cliente
		sOut = sOut + this.fix1(cursorventas.customer)+'|' && 22 nombre
		sOut = sOut + this.fix1(cursorventas.address)+'|' && 23 direccion
		sOut = sOut + this.fix1(cursorventas.phone)+'|'  && 24 telefono
		sOut = sOut + DTOC(cursorventas.fechareg)+'|'  && 25 fecha de creacion
		
		&& F  'FechaDesac|VisiClie|CodZona|DescZona|CodigoCiudad|'
		sOut = sOut + '0|'  && 26 fecha de desactivacion cliente
		sOut = sOut + '1|'  && 27 visitas por mes
		sOut = sOut + this.fix1(cursorventas.codzona)+'|' && 28 codZona
		sOut = sOut + this.fix1(cursorventas.zona)+'|' && 29 DescZona
		sOut = sOut + this.fix1(cursorventas.zip)+'|' && 30 codigo ciudad cliente
		
		&& G  'NombreCiudad|CedulaSuperv|NombSuperv|DirecSuperv|TeleSuperv|
		sOut = sOut + this.fix1(cursorventas.city)+'|'  && 31 nombre ciudad
		
		*sOut = sOut + this.fix1(cursorventas.srucsalesman)+'|'  && 32 cedula supervisor
		*sOut = sOut + this.fix1(cursorventas.snombresalesman)+'|'  && 33 nombre
		*sOut = sOut + this.fix1(cursorventas.saddresssalesman)+'|'	&& 34 direcc
		*sOut = sOut + this.fix1(cursorventas.sphonesalesman)+'|'  && 35 telefono
		
		
		sOut = sOut + this.fix1(sup_ced)+'|'  && 32 cedula supervisor
		sOut = sOut + this.fix1(sup_nom)+'|'  && 33 nombre
		sOut = sOut + this.fix1(sup_dir)+'|'	&& 34 direcc
		sOut = sOut + this.fix1(sup_fon)+'|'  && 35 
		
		&& H  'CodCanal|NombCanal|Depa|NombDepa|CodProveedor|'	
		
		cod_neg = 'TN'
		nom_neg = 'TIENDA NUMERICA'
		IF !EMPTY(cursorventas.CodCanal) AND !ISNULL(cursorventas.CodCanal)
			cod_neg = ALLTRIM(cursorventas.CodCanal)
			nom_neg = ALLTRIM(cursorventas.nombCanal)
		ENDIF
		
		
		
		sOut = sOut + this.fix1(cod_neg)+'|'  && 36 codigo de Canal (negocio)
		sOut = sOut + this.fix1(nom_neg)+'|'  && 37 nombre
		sOut = sOut + this.fix1(cursorventas.CodDepar)+'|'  && 38 codigo provincia
		sOut = sOut + this.fix1(cursorventas.NombDepar)+'|'  && 39 nombre
		sOut = sOut + '1791314379001|'  && 40 Codigo del proveedor
		
		&& I  'NombreProv|CodSector|DescrSector|EstaFac'
		
		sOut = sOut + this.fix1('FAMILIA SANCELA')+'|'  && 41 nombre
		sOut = sOut + this.fix1(cursorventas.codzona)+'|'  && 42 codigo del sector
		sOut = sOut + this.fix1(cursorventas.zona)+'|' && 43 nombre sector		
		sOut = sOut + transform(cursorventas.void,'9') && 44 estado factura
		
		
		sOut = sOut + (this.NL)

	ENDSCAN 
ENDIF 	

*F14010_1M_31012016.dat

STRTOFILE(sOut ,this.cRuta + 'F'+this.ccodigoproveedor+'_1M_'+SUBSTR(gdFin,7,2)+SUBSTR(gdFin,5,2)+SUBSTR(gdFin,1,4)+'.dat'	)

ENDFUNC


FUNCTION fix1
	PARAMETERS sCadTemp
	enter     	= CHR(13)
	ctab 		= CHR(10)


	IF ISNULL(sCadTemp)
		sCadTemp = ''
	ENDIF

	sCadTemp= strtran(sCadTemp,'"','')
	sCadTemp= strtran(sCadTemp,"'",'')
	sCadTemp= STRTRAN(sCadTemp,enter,' ')
	sCadTemp= STRTRAN(sCadTemp,ctab,' ')

	RETURN ALLTRIM(sCadTemp)
ENDFUNC



FUNCTION msg_add (cMsg)
	IF EMPTY(this.cIdView)
		WAIT WINDOW cMsg nowait
	ELSE
		cMsg = this.cTitulo + CHR(13)+ cMsg
		oApp.msg_add(this.cIdView, cMsg)
	ENDIF
	
ENDFUNC


FUNCTION colgate (ndIni, ndFin) 

		ndIni = INT(VAL(TRANSFORM(ndIni)))
		ndFin = INT(VAL(TRANSFORM(ndFin)))

		this.cCondStockNE = IIF(oApp.setup('lNEBajaStock','L'),' and C.facturado = 0 ',' and C.agrupar=0 ')
		
		s_Sales = ""
		s_inventry = ""
		s_disteffe = ""

		FOR iCount = ndIni TO ndFin

			this.fecha = TRANSFORM(iCount)
			
			? this.fecha
			c = this.fecha
	
			this.periodo = SUBSTR(this.fecha,1,6)
			this.semana = ALLTRIM(TRANSFORM(WEEK(CTOD(SUBSTR(c,7,2)+'/'+SUBSTR(c,5,2)+'/'+SUBSTR(c,1,4)))))
			? 'Semana: '+this.semana
			

			? "Cargando ventas "
			s_Sales = s_Sales + this.colgate_sales()

		
		ENDFOR  

		? "Cargando inventario "
		s_inventry = s_inventry + this.colgate_inventry()



		? "Cargando estadisticas "
			s_disteffe = s_disteffe + this.colgate_disteffe()
		
		? "Cargando clientes "
		s_custmast = this.colgate_custmast(ndIni, ndFin)
		
		? "Cargando rutas "
		s_routmast = this.colgate_routmast()

		? "Cargando vendedores "
		s_saleteam = this.colgate_saleteam()
				
		? "Cargando canales "
		s_chain = this.colgate_chain()
		
		? "Cargando ciudad "
		s_city = this.colgate_city()
		
		? "Cargando cprovincia "
		s_prov = this.colgate_prov()

		
		STRTOFILE(s_Sales,    this.cRuta + 'sales4.txt')
		STRTOFILE(s_custmast, this.cRuta + 'custmast4.txt')
		STRTOFILE(s_inventry, this.cRuta + 'inventry4.txt')
		STRTOFILE(s_disteffe, this.cRuta + 'disteffe4.txt')

		STRTOFILE(s_routmast, this.cRuta + 'routmast4.txt')
		STRTOFILE(s_saleteam, this.cRuta + 'saleteam4.txt')
		STRTOFILE(s_chain,    this.cRuta + 'chain4.txt')
		
		STRTOFILE(s_city,     this.cRuta + 'city4.txt')
		STRTOFILE(s_prov,     this.cRuta + 'StateProvince4.txt')
*!*					

	ENDFUNC


FUNCTION colgate_sales() 
		
		
		TEXT TO cCmd NOSHOW TEXTMERGE 
			SELECT i.idsalesman, 
				  i.idcustomer, 
				  d.iditem,
				  count(i.id) as orders,
				  sum(if((d.price-d.descunit)=0,d.quantity,00000000.00)) as q0, 
				  sum(d.quantity) as q, 
				  round(sum(d.quantity*(d.price-d.descunit)),4) as totalventa,
				  max(d.iditem) as iditem,
				  max(it.codigo4) as producto,
				  max(it.unidadescaja) as uc
			FROM invoice as i inner join invoicedet as d ON i.id=d.id 
								inner join item as it ON it.id=d.iditem
			WHERE !i.void and i.archivado 
		      		and it.idbrand="<<this.idbrand>>" 
		      		and it.codigo4!=""
					and i.date=<<this.fecha>>
			GROUP BY 1,2,3
		ENDTEXT 
		
		oApp.SQLExec(cCmd, 'cur')
		
		
		TEXT TO cCmd NOSHOW TEXTMERGE 
			SELECT it.id, sum(ndcdet.quantity) as q
			FROM ndcdet inner join ndc on ndcdet.id=ndc.id 
			            inner join item as it ON it.id=ndcdet.iditem
			WHERE !ndc.void 
		      		and it.idbrand="<<this.idbrand>>" 
		      		and it.codigo4!=""
					and ndc.date="<<this.fecha>>" 
			GROUP BY 1
		ENDTEXT 
		
		oApp.SQLExec(cCmd, 'curNC')	
		
		sOut = ''
				
		SELECT cur
		
		SCAN ALL 
				
			_iditem 	= cur.iditem

			SELECT curNC
			LOCATE FOR id=_iditem
			qdev = 0
			
			IF FOUND()
				qdev = curNC.q
			ENDIF

			precompra = 0

			IF oApp.SQLExec("SELECT stock,costlast FROM stockhist where iditem='"+_iditem+"' AND fecha<'"+this.fecha+;
			 					"' ORDER BY fecha DESC LIMIT 1",'curStock')>0
				precompra = curStock.costlast	
			ENDIF

			SELECT cur
		
			q 			= ROUND(cur.q,2)
			_pvp 		= cur.totalventa / q
			
			q 			= cur.q - qdev 
			
			nTotalVen 	= round(_pvp * q,2)
			
	
			sOut = sOut + this.fix2(this.ccodigoproveedor)
			sOut = sOut + this.fix2(this.periodo) && yyyymm
			sOut = sOut + this.fix_a(cur.producto)
			sOut = sOut + this.fix2(cur.idcustomer)	
			sOut = sOut + this.fix2(cur.idsalesman)

			sOut = sOut + this.fixn(q) && Sales Qty
			*sOut = sOut + this.fix2(q/iif(cur.uc>0,cur.uc,1)) && cantidad_vta_caja
			sOut = sOut + this.fixn(nTotalVen)

			sOut = sOut + this.fixn(cur.q0) &&cant_gratis
			sOut = sOut + this.fixn(precompra)
			
			
			SELECT COUNT(iditem) as n FROM cur WHERE iditem=_iditem INTO CURSOR curOrders
			
			
			SELECT cur
			sOut = sOut + this.fixn(curOrders.n)
			sOut = sOut + this.fixn(nTotalVen)
			sOut = sOut + this.fix2(this.periodo+this.semana) && yyyymmww
			sOut = sOut + this.fix2(this.fecha,.t.) && yyyymmdd

			sOut = sOut + this.NL
			SELECT cur
		ENDSCAN
		RETURN sOut
	ENDFUNC


	FUNCTION colgate_custmast(ndIni, ndFin) 
	
*!*			TEXT TO cCmd NOSHOW TEXTMERGE
*!*				SELECT c.id, 
*!*					MAX(c.name) as name,
*!*					MAX(c.codigo) as codigo,
*!*					MAX(c.ruc) as ruc,
*!*					MAX(c.idzona) as idzona,
*!*					MAX(n.id) as idchain,
*!*					MAX(n.codigo) as ac_type,
*!*					MAX(c.city) as city, 
*!*					MAX(dep.name) as provincia, 
*!*					COUNT(i.id) as n
*!*				FROM customer as c 
*!*					inner join invoice as i on  c.id=i.idcustomer 
*!*					left join negocio as n on  c.idnegocio=n.id
*!*					left join departament as dep on  c.iddepart=dep.id
*!*				WHERE !i.void and i.date between <<ndIni>> and <<ndFin>>
*!*				GROUP BY 1
*!*			ENDTEXT
		
		
		
		TEXT TO cCmd NOSHOW TEXTMERGE
			SELECT c.id, 
				c.name,
				c.codigo,
				c.ruc,
				c.idzona,
				MAX(n.codigo) as idchain,
				MAX(n.cod_col) as ac_type,
				MAX(c.city) as city, 
				MAX(dep.name) as provincia, 
				COUNT(i.id) as n
			FROM customer as c 
				inner join invoice as i on  c.id=i.idcustomer 
				left join negocio as n on  c.idnegocio=n.id
				left join departament as dep on  c.iddepart=dep.id
			WHERE !i.void and i.date between <<ndIni>> and <<ndFin>>
			GROUP BY 1
		ENDTEXT

		oApp.SQLExec(cCmd,'cur')
		
		sOut = ''	
		
		SELECT cur
		SCAN all
			idcust = cur.id
			sOut = sOut + this.fix2(this.ccodigoproveedor)
			sOut = sOut + this.fix2(cur.id)			
			sOut = sOut + this.fix2(substr(cur.name,1,40))			
			sOut = sOut + this.fix2(cur.idchain) && chain code	
			
			*sOut = sOut + this.fix2(this.periodo)
			*sOut = sOut + this.fix2(this.semana)
			
			sOut = sOut + this.fix2(cur.idzona)
			sOut = sOut + this.fix2(IIF(ISNULL(cur.ac_type),cATo,ALLTRIM(cur.ac_type))) && Account code	
			
			sOut = sOut + this.fix2("N") && d2_track_job
			sOut = sOut + this.fix2("Y") && d2_track_cp
			sOut = sOut + this.fix2(substr(cur.ruc,1,10)) && census_number
			sOut = sOut + SPACE(2) + CHR(9) && customer_class
			sOut = sOut + SPACE(16) + CHR(9) && postal code
			
			sOut = sOut + this.fix2(SUBSTR(ALLTRIM(cur.city),1,18)) && City
			sOut = sOut + this.fix2(SUBSTR(ALLTRIM(cur.provincia),1,20)) && State
			
			
*!*				sOut = sOut + this.fix2(cur.fechareg) && New_Customer_Date
*!*				sOut = sOut + this.fix2(rowOrders["n) && Number_Orders
*!*				sOut = sOut + this.fix2(IIF(rowOrders.n>0,nn/rowOrders.n,0) && Average_Orders
			sOut = sOut + this.NL
		ENDSCAN
		RETURN sOut
		
	ENDFUNC
	
	FUNCTION colgate_inventry()

		_cIdbrand = this.idbrand
		
		oApp.SQLExec("SELECT id as iditem, codigo4 as producto, if(unidadescaja<1,1,unidadescaja)+000000.0 as uc FROM item WHERE trim(codigo4)!='' and idbrand=?_cIdBrand",'cur')
		
		bodega = 'MATRIZ'
		
		sOut = ''
		SELECT cur
		SCAN ALL
				
			_iditem 	 = cur.iditem

			nIni = this.calmov ('PO',			_iditem, gdIni)
			nIni = nIni - this.calmov ('NDCPo',			_iditem, gdIni)
			nIni = nIni - this.calmov ('INVOICE',		_iditem, gdIni)
			nIni = nIni + this.calmov ('NDC',			_iditem, gdIni)
			nIni = nIni + this.calmov ('KARDEXIN',		_iditem, gdIni)
			nIni = nIni - this.calmov ('KARDEXOUT',		_iditem, gdIni)
			nIni = nIni - this.calmov ('CONSINTERNO',	_iditem, gdIni)
							
			nPo 		= this.calmov ('PO',			_iditem, gdIni, gdFin)
			nNDCPo 	 	= this.calmov ('NDCPo',		_iditem, gdIni, gdFin)
			nINVOICE 	= this.calmov ('INVOICE',		_iditem, gdIni, gdFin)
			nNDC 		= this.calmov ('NDC',			_iditem, gdIni, gdFin)
			nKARDEXIN   = this.calmov ('KARDEXIN',		_iditem, gdIni, gdFin)
			nKARDEXOUT	= this.calmov ('KARDEXOUT',	_iditem, gdIni, gdFin)
			nCONSINT	= this.calmov ('CONSINTERNO',	_iditem, gdIni, gdFin)
			nGratis 	= this.calmov ('GRATIS',		_iditem, gdIni, gdFin)

			val_rec_cp	= 0
			_costo 		= 0
			IF oApp.SQLExec("SELECT sum((podet.price-podet.descunit)*quantity)+0.0 as val_rec_cp "+;
				 "FROM podet "+;
				 "inner join po as C on podet.id=C.id "+;
				 "where podet.iditem='"+_iditem+"' AND C.date between ?gdIni and ?gdFin "+;
				 " and C.void=0 and C.tipoComprobante<>4 and C.transito=0",'rowCompra')>0

				val_rec_cp	= rowcompra.val_rec_cp+0.0
				
				
				IF oApp.SQLExec("SELECT (podet.price-podet.descunit) as costo "+;
					 "FROM podet "+;
					 "inner join po as C on podet.id=C.id "+;
					 "where podet.iditem='"+_iditem+"' AND C.date <= ?gdFin  "+;
					 " and C.void=0 and C.tipoComprobante<>4 and C.transito=0 AND podet.price>0 "+;
					 " ORDER BY C.DATE DESC LIMIT 1 " ,'rowCompraCosto')>0
					
					_costo 		= rowCompraCosto.costo+0.0
				ENDIF
				
			ENDIF
			
			 _costo = ROUND(_costo,4)
			
			tipo_inventario		= "S"
			producto			= cur.producto
			cant_inv_inicial	= nIni
			val_inv_inicial		= ROUND(cant_inv_inicial * _costo+0.0,2)
			cant_rec_cp			= nPo-nNDCPo+0.0
			&&val_rec_cp			= cant_rec_cp * costo
			cant_gratis_cp		= nGratis+0.0
			val_gratis_cp		= ROUND(nGratis * _costo+0.0,2)
			cant_dev_cli		= nNDC+0.0

			val_dev_cli			= ROUND(cant_dev_cli * (_costo+0.0)+0.0,2)
			

			cant_ajust			= nKARDEXIN-nKARDEXOUT-nCONSINT-nNDCPo+0.0
			
			val_ajust			= cant_ajust * _costo+0.0
			cant_asig_vta		= nINVOICE-nGratis+0.0
			val_asig_vta		= ROUND(cant_asig_vta * _costo+0.0,2)
			cant_inv_final		= nIni + (cant_rec_cp+cant_dev_cli+cant_ajust-nINVOICE)+0.0
			val_inv_final		= ROUND(cant_inv_final * _costo +0.0,2)
			
            if (isnull(val_inv_final)) 
            	val_inv_final=0.0
            ENDIF
            
           
             &&Op Balance Qty + Receipt Qty + Free Receipt Qty + Return Qty  +   (+)or(-)Adjustment Qty  - Allocated Qty - Closing Qty = 0
             
             && F + H + J + L + N - P - R = 0
           
			
			sOut = sOut + this.fix2(this.ccodigoproveedor)  && A 1
			sOut = sOut + this.fix2(bodega)					&& B 2
			sOut = sOut + this.fix2(this.periodo)			&& C 3
			*sOut = sOut + this.fix2(this.semana)			
			sOut = sOut + this.fix2(tipo_inventario)		&& D 4
			sOut = sOut + this.fix_a(producto)				&& E 5
			sOut = sOut + this.fixn(cant_inv_inicial) 		&& F 6 opening balance qty			
			sOut = sOut + this.fixn(val_inv_inicial) 		&& G 7 valor
			sOut = sOut + this.fixn(cant_rec_cp)      		&& H 8 cant recibida CP
			sOut = sOut + this.fixn(val_rec_cp)       		&& I 9 valor
			sOut = sOut + this.fixn(cant_gratis_cp)    		&& J 10 cant gratis
			sOut = sOut + this.fixn(val_gratis_cp)			&& K 11 valor gratis
			sOut = sOut + this.fixn(cant_dev_cli)			&& L 12 cant dev.
			sOut = sOut + this.fixn(val_dev_cli)			&& M 13 valor dev.
			sOut = sOut + this.fixn(cant_ajust)       		&& N 14 cant ajustada
			sOut = sOut + this.fixn(val_ajust)				&& O 15 val ajustada
			sOut = sOut + this.fixn(cant_asig_vta)    		&& P 16 cant vendida
			sOut = sOut + this.fixn(val_asig_vta)		    && Q 17 valor venta
			sOut = sOut + this.fixn(cant_inv_final)         && R 18 stock final
			sOut = sOut + this.fixn(val_inv_final,.t.)		&& S 19 valor final
			sOut = sOut + this.NL
		ENDSCAN
		RETURN sOut

	ENDFUNC

	
FUNCTION colgate_disteffe() 

		TEXT TO cCmd NOSHOW TEXTMERGE 
			SELECT SUBSTR(i.date,1,7) as periodo, c.idzona, c.idnegocio,
				count(i.id)+0.0 as orders,
				count(i.idcustomer)+0.0 as num_cli,
				sum(d.quantity)+0.0 as qt, 
				count(i.id)+0.0 as nf,
				sum(d.quantity/if(it.unidadescaja<1,1,it.unidadescaja)) as q,
				sum(d.quantity*(d.price-d.descunit)) as totalventa,
				MAX(c.idsalesman) as idsalesman,
				MAX(c.idnegocio) as idnegocio,
				MAX(c.city) as city,
				MAX(c.provincia) as provincia
			FROM invoice as i inner join invoicedet as d ON i.id=d.id 
								inner join item as it ON it.id=d.iditem
								inner join Customer as c ON c.id=i.idcustomer
			WHERE !i.void and i.archivado 
	      		and it.idbrand="<<this.idbrand>>" 
	      		and it.codigo4!=""
				and (i.date between <<gdIni>> and  <<gdFin>>) 
			GROUP BY 1,2,3
		ENDTEXT

		oApp.SQLExec(cCmd,'cur')
		
		

		TEXT TO cCmd NOSHOW TEXTMERGE
			SELECT SUBSTR(i.date,1,7) as periodo, c.idzona, c.idnegocio, c.id, MIN(SUBSTR(c.fechareg,1,7)) as fechareg
			FROM customer as c 
				inner join invoice as i on  c.id=i.idcustomer 
				left join negocio as n on  c.idnegocio=n.id
				left join departament as dep on  c.iddepart=dep.id
			WHERE !i.void and i.date between <<gdIni>> and  <<gdFin>>
			GROUP BY 1,2,3,4
		ENDTEXT
		oApp.SQLExec(cCmd,'curCli')
		
		TEXT TO cCmd NOSHOW TEXTMERGE 
			SELECT SUBSTR(i.date,1,7) as periodo, c.idzona, c.idnegocio, c.id
			FROM invoice as i inner join invoicedet as d ON i.id=d.id 
								inner join item as it ON it.id=d.iditem
								inner join Customer as c ON c.id=i.idcustomer
			WHERE !i.void and i.archivado 
	      		and it.idbrand="<<this.idbrand>>" 
	      		and it.codigo4!=""
				and (i.date between <<gdIni>> and  <<gdFin>>) 
			GROUP BY 1,2,3,4
		ENDTEXT

		oApp.SQLExec(cCmd,'curCliNew')
						
		SELECT cur

		sOut = ''
		
		SCAN all
			
			cidzona = cur.idzona
			cIdNegocio = cur.idnegocio
			cPeri = cur.periodo
						
			frecuencia = 4
			cperiodo = ALLTRIM(STRTRAN(cur.periodo,'-','')) && yyyymm
			
			SELECT curCli
			GO top
			COUNT TO cant_cli_acti for periodo=cPeri AND idzona=cIdZona AND idnegocio=cIdNegocio 

			SELECT curCli
			GO top
			COUNT TO cant_cli_new for  periodo=cPeri AND idzona=cIdZona AND idnegocio=cIdNegocio and fechareg=cPeri
			
			SELECT curCliNew
			GO top
			COUNT TO cant_cli_uni FOR  periodo=cPeri AND idzona=cIdZona AND idnegocio=cIdNegocio 

			cant_orden = round(cur.orders,0)
			valor_orden = round(cur.totalventa,2)
	
			promedio = round(cur.qt/iif(cur.nf>0,cur.nf,1),0)
			cant_uni_ord = round(cur.q,2)
			cant_uni_ent = round(cur.q,2)


			sOut = sOut + this.fix2(this.ccodigoproveedor)  && 1
			sOut = sOut + this.fix2(SUBSTR(cperiodo,1,6))  && 2
			*sOut = sOut + this.fix2(this.semana)
			sOut = sOut + this.fix2(cIdzona)  &&3
		
		
			cAT = cATo
			
			cCodChain = '15'
			
			IF oApp.sqlexec('select codigo, cod_col from negocio where id="'+cur.idnegocio+'"')>0
				cAT = ALLTRIM(sqlresult.codigo)
				cCodChain = ALLTRIM(sqlresult.cod_col)
			ENDIF
			
			
			sOut = sOut + this.fix2(cCodChain )		&&4	

			sOut = sOut + this.fixn(frecuencia)	&& 5
			sOut = sOut + this.fixn(cant_cli_acti) && 6
			sOut = sOut + this.fixn(cant_cli_new) && 7

			sOut = sOut + this.fixn(cant_cli_uni) && 8 clientes unicos
			sOut = sOut + this.fixn(cant_orden)   && 9
			sOut = sOut + this.fixn(valor_orden)  && 10
			sOut = sOut + this.fixn(promedio)     && prom.prod. fact
			sOut = sOut + this.fixn(cant_uni_ord) 
			sOut = sOut + this.fixn(cant_uni_ent)
			
			sOut = sOut + this.fix2(cur.idsalesman)
			sOut = sOut + this.fix2(cur.city)
			sOut = sOut + this.fix2(cur.provincia,.t.)
	
			sOut = sOut + this.NL
		ENDSCAN
		RETURN sOut
	ENDFUNC


	
	FUNCTION colgate_routmast() 

		sOut = ''		
		oApp.SQLExec("SELECT id, name FROM zonas ",'cur')

		SELECT cur
		SCAN all
			sOut = sOut + this.fix2(this.ccodigoproveedor)
			sOut = sOut + this.fix2(cur.id)
			sOut = sOut + this.fix2("D")
			sOut = sOut + this.fix2(4)
			sOut = sOut + this.fix2(cur.name,.t.)
			sOut = sOut + this.NL
		ENDSCAN
		RETURN sOut
	ENDFUNC
	
	
	function colgate_saleteam() 
		
		sOut = ''
		oApp.SQLExec("SELECT id, name FROM salesman ",'cur')

		SELECT cur
		SCAN all
		
			sOut = sOut + this.fix2(this.ccodigoproveedor)
			sOut = sOut + this.fix2(cur.id)
			sOut = sOut + this.fix2(cur.name)
			sOut = sOut + this.fix2("D",.t.)
			sOut = sOut + this.NL
		ENDSCAN
		RETURN sOut
	ENDFUNC
	
	FUNCTION colgate_chain()
		
		sOut = ''
		oApp.SQLExec("SELECT cod_col as cod1, codigo as cod2, name FROM negocio ",'cur')

		SELECT cur
		SCAN all
			sOut = sOut + this.fix2(cur.cod1)
			sOut = sOut + this.fix2(cur.cod2)
			sOut = sOut + this.fix2(cur.name,.t.)
			sOut = sOut + this.NL
		ENDSCAN
		RETURN sOut
	ENDFUNC
	
	FUNCTION colgate_city()

		sOut = ''
		oApp.SQLExec("SELECT distinct c.city, d.name as provincia FROM customer as c left join departament as d on c.iddepart=d.id ",'cur')

		SELECT cur
		SCAN all
			sOut = sOut + this.fix2(SUBSTR(cur.provincia,1,4))
			sOut = sOut + this.fix2(SUBSTR(cur.city,1,4))
			sOut = sOut + this.fix2(cur.city,.t.)
			sOut = sOut + this.NL
		ENDSCAN
		RETURN sOut
	ENDFUNC

	FUNCTION colgate_prov()

		sOut = ''

		oApp.SQLExec("SELECT name as provincia FROM departament ",'cur')

		SELECT cur
		SCAN all
			sOut = sOut + this.fix2(SUBSTR(cur.provincia,1,4))
			sOut = sOut + this.fix2(cur.provincia,.t.)
			sOut = sOut + this.NL
		ENDSCAN
		RETURN sOut
	ENDFUNC

	
	FUNCTION calmov
	PARAMETERS cTable, cIdItem, dFechaIni, dFechaFin

		cTable = trim(upper(cTable))
		cCabeza = cTable
		cDetalle = cTable + 'Det'

		nRetVal = 0.0
		
		cPend = ' and C.archivado = 1 '
		condDate = ' and C.date between ?dFechaIni and ?dFechaFin '
		
		IF EMPTY(dFechaFin)
			condDate = ' and C.date < ?dFechaIni '
		ENDIF
		
		
		cWhere = ' '
		
		DO CASE  
			case cTable == 'PO'
				cWhere = ' D.IdItem="'+cIdItem+'" and C.void=0 and C.tipoComprobante<>4 and c.transito=0 '+cPend

			case cTable == 'NDCPO'
				cCabeza = 'PO'
				cDetalle = 'PODet'
				cWhere = ' D.IdItem="'+cIdItem+'" and C.void=0 and C.tipoComprobante=4 and c.transito=0 '+cPend

			case cTable == 'INVOICE'
				cWhere = ' D.IdItem="'+cIdItem+'" and C.void=0 AND C.tipo != "06" '+this.cCondStockNE 

			case cTable == 'GRATIS'
				
				cCabeza = 'Invoice'
				cDetalle = 'InvoiceDet'
				cWhere = ' D.IdItem="'+cIdItem+'" and C.void=0 AND C.tipo != "06" '+this.cCondStockNE+ ' and (d.price-d.descunit)=0 '

			case cTable == 'KARDEXIN' OR cTable == 'KARDEXOUT' or cTable == 'CONSINTERNO'
				cWhere = ' D.IdItem="'+cIdItem+'" and C.state=1 and C.void=0 '
		
			case cTable == 'NDC'
				cWhere = ' D.IdItem="'+cIdItem+'" and C.type_doc = 1 and C.void=0 AND C.noinvent=0 '
		ENDcase
	
		cCad = 'SELECT sum(D.quantity) as n FROM '+cCabeza+' AS C INNER JOIN '+cDetalle+' as D ON D.id=C.id WHERE '+cWhere+condDate
		
		&&oApp.msg(cCad)
		
		IF oApp.SQLExec(cCad,'rowmov')>0
			nRetVal = rowmov.n
			IF ISNULL(nRetVal)
				nRetVal = 0
			ENDIF
			
		ENDIF 

		return ROUND(nRetVal,2)
	ENDFUNC
	
	FUNCTION fix2 
		PARAMETERS sCadTemp, lVacia
		enter     	= CHR(13)
		ctab 		= CHR(9)
		cnl 		= CHR(10)

		IF ISNULL(sCadTemp)
			sCadTemp = ''
		ENDIF
		sCadTemp = TRANSFORM(sCadTemp)

		sCadTemp = strtran(sCadTemp,'"','')
		sCadTemp = strtran(sCadTemp,"'",'')
		sCadTemp = STRTRAN(sCadTemp,enter,'')
		sCadTemp = STRTRAN(sCadTemp,cnl,'')
		sCadTemp = STRTRAN(sCadTemp,ctab,' ')

		RETURN ALLTRIM(sCadTemp) + IIF(EMPTY(lVacia),ctab,'')
		
	ENDFUNC
	
	FUNCTION fixn
		PARAMETERS sCadTemp, lVacia
		enter     	= CHR(13)
		ctab 		= CHR(9)
		cnl 		= CHR(10)

		IF ISNULL(sCadTemp)
			sCadTemp = 0
		ENDIF
		sCadTemp = TRANSFORM(sCadTemp)

		sCadTemp = strtran(sCadTemp,'"','')
		sCadTemp = strtran(sCadTemp,"'",'')
		sCadTemp = STRTRAN(sCadTemp,enter,'')
		sCadTemp = STRTRAN(sCadTemp,cnl,'')
		sCadTemp = STRTRAN(sCadTemp,ctab,' ')

		RETURN ALLTRIM(sCadTemp) + IIF(EMPTY(lVacia),ctab,'')
		
	ENDFUNC
	
	
	FUNCTION fix_a (cCod, lVacia)
		ctab 		= CHR(9)
		RETURN ALLTRIM(STRTRAN(cCod,'@','')) + IIF(EMPTY(lVacia),ctab,'')
	ENDFUNC
	


	FUNCTION getVentasGrupo (lUsd,idConsulta)
	
		curJson = idConsulta

		IF EMPTY(lUsd)
			lUsd = .f.
		ENDIF
		

		cTabla = 'grupo'
		cColGrp ='GRUPO'
		cCampoId = 'idgrupo' 
		
		cCampoRet = 'd.quantity/it.unidadescaja'
		
		IF lUsd
			cCampoRet = 'd.quantity*(d.price-d.descunit)'
		ENDIF
		
		IF idConsulta='ventasMarca'
			
			cTabla = 'brand'
			cCampoId = 'idbrand'
			cColGrp ='MARCA'
		ENDIF
		
		
		TEXT TO cCmd NOSHOW TEXTMERGE 
		select 
		i.idsalesman,
		g.id as idgrupo,
		s.name as vendedor,
		g.name as grupo,
		<<cCampoRet>> as ca
		from invoice as i 
		inner join invoicedet as d on i.id=d.id
		inner join item as it on it.id=d.iditem
		inner join <<cTabla>> as g on it.<<cCampoId>>=g.id
		inner join salesman as s on s.id=i.idsalesman
		where  i.tipo<3 and !i.void and i.date between <<gdIni>> and  <<gdFin>>
		ENDTEXT

		* (substr(i.date,1,7) ="<<cYear>>-<<cMes>>") 
		*STRTOFILE(cCmd,'\ventas_grupo.txt')

		oApp.sqlexec(cCmd,'curJson0')
		SELECT distinc vendedor, idsalesman FROM curJson0 INTO CURSOR curSales ORDER BY 1

		cCurSql = 'create cursor '+curJson+'('+cColGrp+' C(20) ' 
		SELECT curSales
		SCAN all
			cCurSql = cCurSql + ", " + oApp.fixn(vendedor) + " n(12,2)"
		ENDSCAN
		cCurSql = cCurSql + ")" 

		&cCurSql 

		SELECT distinc grupo, idgrupo FROM curJson0 INTO CURSOR curGrupo ORDER BY 1
		SELECT curGrupo
		SCAN all
			INSERT INTO &curJson (&cColGrp) VALUES (curGrupo.grupo)	
			SELECT curSales
			GO TOP 
			SCAN all
				cCampoSales = oApp.fixn(vendedor)
				select SUM(ca) as ca FROM curJson0 WHERE idgrupo=curGrupo.idgrupo AND idsalesman=curSales.idsalesman INTO CURSOR curTotal
				IF !ISNULL(curTotal.ca) 
					SELECT &curJson
					replace &cCampoSales WITH ROUND(curTotal.ca,0)
					flush
				ENDIF
				SELECT curSales
			ENDSCAN
			SELECT curGrupo
		ENDSCAN

		SELECT &curJson
		
		cFileXls = GETFILE('xls')
		
		COPY TO &cFileXls XLS
		

	ENDFUNC

	
ENDDEFINE
