PARAMETERS idConsulta

IF EMPTY(idConsulta)
	idConsulta = 'sales'
ENDIF

cYear = TRANSFORM(YEAR(DATE()))
cMes = "01" 
cFiltro = ""

f_desde = "20160101"
f_hasta = "20160101"
c_tabla = '1' && proveedor
c_tipo = '1' && valores

IF !EMPTY(oJs.jParam)
	* {"c_year":"2016","c_month":"enero","filtro":"15"}
	
	oApp.init_aJson(oJs.jParam)
	cYear   = oApp.get_aJson('c_year')
	cMes    = oApp.get_cmes(oApp.get_aJson('c_month'))	
	cFiltro = oApp.get_aJson('filtro')
	c_tabla = oApp.get_aJson('c_tabla')
	c_tipo  = oApp.get_aJson('c_tipo') && 2 dolares
	
	f_desde = oApp.getcdate(oApp.get_aJson('f_desde')) && 05/31/2016 
	f_hasta = oApp.getcdate(oApp.get_aJson('f_hasta')) && 05/31/2016 

	*STRTOFILE(oJs.jParam,'\jparam.txt')
ENDIF

curJson = idConsulta

DO CASE
CASE idConsulta='ventasMarcaUsd' OR idConsulta='ventasGrupoUsd'
	getVentasGrupo(.T.)
CASE idConsulta='ventasMarca' OR idConsulta='ventasGrupo'
	getVentasGrupo()
OTHERWISE 
	DO &IdConsulta
ENDCASE

namexml = '\'+curJson

COPY TO (namexml) xls

oJs.json_up(curJson)


FUNCTION resumenPedidos
TEXT TO cCmd NOSHOW TEXTMERGE 
select 
	s.name as vendedor, 
	round(sum(total),2) as USD, 
	sum(1) as Pedidos, 
	sum(if(b.facturadopda,1,0)) as Fact,
	sum(if(b.facturadopda,0,1)) as Pend,
	sum(if(b.void,1,0)) as Anul
	from basket as b inner join salesman as s on b.idsalesman=s.id
	where b.date between <<f_desde>> and <<f_hasta>>
	group by 1
union all
select 
	'TOTALES' as vendedor, 
	round(sum(total),2) as USD, 
	sum(1) as Nro_Ped, 
	sum(if(b.facturadopda,1,0)) as Fact,
	sum(if(b.facturadopda,0,1)) as Pend,
	sum(if(b.void,1,0)) as Anul
	from basket as b inner join salesman as s on b.idsalesman=s.id
	where b.date between <<f_desde>> and <<f_hasta>>
ENDTEXT

oApp.sqlexec(cCmd,curJson)

ENDFUNC


FUNCTION resumenCobros
TEXT TO cCmd NOSHOW TEXTMERGE 
select 
	s.name as vendedor, 
	round(sum(monto),2) as USD, 
	sum(1) as Cobros, 
	sum(if(b.despachado,1,0)) as Reg,
	sum(if(b.despachado,0,1)) as Pend,
	sum(if(b.void,1,0)) as Anul
	from Recaudacion as b inner join salesman as s on b.idsalesman=s.id
	where b.fecha between <<f_desde>> and <<f_hasta>>
	group by 1
union all
select 
	'TOTALES' as vendedor, 
	round(sum(monto),2) as USD, 
	sum(1) as Cobros, 
	sum(if(b.despachado,1,0)) as Reg,
	sum(if(b.despachado,0,1)) as Pend,
	sum(if(b.void,1,0)) as Anul
	from Recaudacion as b inner join salesman as s on b.idsalesman=s.id
	where b.fecha between <<f_desde>> and <<f_hasta>>
ENDTEXT

oApp.sqlexec(cCmd,curJson)

ENDFUNC

FUNCTION ventasAnuales
	TEXT TO cCmd NOSHOW TEXTMERGE 
	SELECT
	s.name as vendedor, 
	(select round(sum(total),2) from invoice where idsalesman=s.id and tipo<3 and !void and (substr(date,1,7) = "<<cYear>>-01") ) as Ene,
	(select round(sum(total),2) from invoice where idsalesman=s.id and tipo<3 and !void and (substr(date,1,7) = "<<cYear>>-02") ) as Feb,
	(select round(sum(total),2) from invoice where idsalesman=s.id and tipo<3 and !void and (substr(date,1,7) = "<<cYear>>-03") ) as Mar,
	(select round(sum(total),2) from invoice where idsalesman=s.id and tipo<3 and !void and (substr(date,1,7) = "<<cYear>>-04") ) as Abr,
	(select round(sum(total),2) from invoice where idsalesman=s.id and tipo<3 and !void and (substr(date,1,7) = "<<cYear>>-05") ) as May,
	(select round(sum(total),2) from invoice where idsalesman=s.id and tipo<3 and !void and (substr(date,1,7) = "<<cYear>>-06") ) as Jun,
	(select round(sum(total),2) from invoice where idsalesman=s.id and tipo<3 and !void and (substr(date,1,7) = "<<cYear>>-07") ) as Jul,
	(select round(sum(total),2) from invoice where idsalesman=s.id and tipo<3 and !void and (substr(date,1,7) = "<<cYear>>-08") ) as Ago,
	(select round(sum(total),2) from invoice where idsalesman=s.id and tipo<3 and !void and (substr(date,1,7) = "<<cYear>>-09") ) as Sep,
	(select round(sum(total),2) from invoice where idsalesman=s.id and tipo<3 and !void and (substr(date,1,7) = "<<cYear>>-10") ) as Oct,
	(select round(sum(total),2) from invoice where idsalesman=s.id and tipo<3 and !void and (substr(date,1,7) = "<<cYear>>-11") ) as Nov,
	(select round(sum(total),2) from invoice where idsalesman=s.id and tipo<3 and !void and (substr(date,1,7) = "<<cYear>>-12") ) as Dic,
	(select round(sum(total),2) from invoice where idsalesman=s.id and tipo<3 and !void and (substr(date,1,4) = "<<cYear>>") ) as Total_<<cYear>>
	from salesman as s
	ENDTEXT

	oApp.sqlexec(cCmd,'sales')
	cCampo = 'Total_'+cYear
	SELECT * FROM sales WHERE &cCampo>0 ORDER BY vendedor INTO CURSOR &curJson

ENDFUNC


FUNCTION resumenVentas  && ventas por vendedor y por proveedor
	
	cTabla = 'supplier'
	cCampoId = 'idsupplier'
	
	IF c_tabla='2'		
		cTabla = 'brand'
		cCampoId = 'idbrand'
	ENDIF
	
	IF c_tabla='3'
		cTabla = 'grupo'
		cCampoId = 'idgrupo' 
	ENDIF
	
	venta_nc ('c0',f_desde,f_hasta,cTabla,cCampoId)
	
	CREATE CURSOR &curJson (nombre c(30), venta n(12,2), devol n(12,2), neto n(12,2))

	SELECT vendedor, grupo, SUM(venta) as venta, SUM(devol) as devol FROM  c0 INTO CURSOR c1 GROUP BY 1,2
	cVend = '.x.x.x'
	STORE 0 TO _venta, _devol, _neto
	SELECT c1
	SCAN ALL 
		IF cVend != vendedor
			IF cVend != '.x.x.x'
				INSERT INTO &curJson (nombre, venta, devol, neto) VALUES ('TOTALES', _venta, _devol, _neto)
				INSERT INTO &curJson (nombre, venta, devol, neto) VALUES ('', 0,0,0)
			ENDIF
			STORE 0 TO _venta, _devol, _neto
			cVend = vendedor
			INSERT INTO &curJson (nombre) VALUES (cVend)
			
		ENDIF
		SELECT c1
		_venta = _venta + venta
		_devol = _devol + devol
		_neto = _venta - _devol
		INSERT INTO &curJson (nombre, venta, devol, neto) VALUES (c1.grupo, c1.venta, c1.devol, c1.venta-c1.devol)
		SELECT c1
		cVend = vendedor
	ENDSCAN
	INSERT INTO &curJson (nombre, venta, devol, neto) VALUES ('TOTALES', _venta, _devol, _neto)
	
	SELECT &curJson
	
ENDFUNC

FUNCTION totalVentas  && ventas por vendedor y por proveedor
	
	cTabla = 'supplier'
	cCampoId = 'idsupplier'
	cTitulo = ' POR PROVEEDOR'
	
	IF c_tabla='2'		
		cTabla = 'brand'
		cCampoId = 'idbrand'
		cTitulo = ' POR MARCA'
	ENDIF
	
	IF c_tabla='3'
		cTabla = 'grupo'
		cCampoId = 'idgrupo'
		cTitulo = ' POR GRUPO'
	ENDIF
	
	venta_nc ('c0',f_desde,f_hasta,cTabla,cCampoId)
	
	CREATE CURSOR &curJson (nombre c(30), venta n(12,2), devol n(12,2), neto n(12,2))

	
	SELECT grupo, SUM(venta) as venta, SUM(devol) as devol FROM  c0 INTO CURSOR c1 GROUP BY 1
	STORE 0 TO _venta, _devol, _neto
	INSERT INTO &curJson (nombre, venta, devol, neto) VALUES ('VENTAS GENERAES'+cTitulo, _venta, _devol, _neto)

	SELECT c1
	SCAN ALL 
		SELECT c1
		_venta = _venta + venta
		_devol = _devol + devol
		_neto = _venta - _devol
		INSERT INTO &curJson (nombre, venta, devol, neto) VALUES (c1.grupo, c1.venta, c1.devol, c1.venta-c1.devol)
		SELECT c1
	ENDSCAN
	
	INSERT INTO &curJson (nombre, venta, devol, neto) VALUES ('TOTALES'+cTitulo, _venta, _devol, _neto)


	
	SELECT &curJson

ENDFUNC

FUNCTION efectividad
	
	cTabla = 'supplier'
	cCampoId = 'idsupplier'
	
	IF c_tabla='2'		
		cTabla = 'brand'
		cCampoId = 'idbrand'
	ENDIF
	
	IF c_tabla='3'
		cTabla = 'grupo'
		cCampoId = 'idgrupo' 
	ENDIF
	
	
	cYear = left(f_desde,4)
	
	TEXT TO cCmd NOSHOW TEXTMERGE
SELECT 
s.id,
s.name as Vendedor,
00000000.00 as Clientes_Rutero,
count(i.id)+00000000.00 as Visitas_Realiz,
00000000.00 as Pedidos,
00000000.00 as No_Compras,
00000000.00 as Venta_Diaria,
round(sum(i.subtotal),2)+0.00 as Ventas,
00000000.00 as Presupuesto,
000.00 as Cumpl_Ventas,
000.00 as Efect_Rutero,
000.00 as Efect_Ventas,
000.00 as dropsize
from invoice as i
inner join salesman as s on i.idsalesman=s.id
where !void and archivado and (date between <<f_desde>> and <<f_hasta>>) and s.supervisor=0
group by 1
order by 2
	ENDTEXT 
	
	
	IF USED('c0')
		SELECT c0
		use
	ENDIF
	
	
	oApp.sqlexec(cCmd,'c0')
	
	SELECT c0
	SCAN ALL
		cIdS = c0.id
		
		nFacturas = c0.Visitas_Realiz
		
		TEXT TO cCmd NOSHOW TEXTMERGE 
SELECT distinct idcustomer FROM fss1.visitascustomer where idsalesman=<<cIdS>>
		ENDTEXT
		nClientes_Rutero = oApp.sqlexec(cCmd,'cCR')
		
		TEXT TO cCmd NOSHOW TEXTMERGE 
SELECT id FROM basket where idsalesman=<<cIdS>> and !void and !facturadopda and date between <<f_desde>> and <<f_hasta>>
		ENDTEXT
		nNo_Compra = oApp.sqlexec(cCmd,'cPed')

		TEXT TO cCmd NOSHOW TEXTMERGE 
SELECT sum(p9.mes08) as n FROM ghost_09accion as p9
 inner join ghost_09 as c9 on c9.id=p9.id_ghost_09 
 inner join ghost_09sales as s9 on s9.id_ghost_09=c9.id 
where s9.idsalesman=<<cIdS>> and c9.year=<<cYear>>
		ENDTEXT
		oApp.sqlexec(cCmd,'cPresup')
		 
		nPresup =  IIF(ISNULL(cPresup.n),0.0,cPresup.n)
		
		nVisitas_Realiz = nFacturas + nNo_Compra 
		nCumpl_Ventas = ROUND(IIF(nPresup>0,c0.Ventas/nPresup,0)*100,2)
		nEfect_Rutero  = ROUND(IIF(nClientes_Rutero>0,nVisitas_Realiz/nClientes_Rutero,0)*100,2)
		nEfect_Ventas  = ROUND(IIF(nClientes_Rutero>0,nFacturas/nClientes_Rutero,0)*100,2)
		nDropSize 	   = 0
		
		SELECT c0
		replace Clientes_Rutero WITH nClientes_Rutero
		replace Visitas_Realiz WITH nVisitas_Realiz 
		replace Pedidos      WITH nFacturas 
		replace No_Compras   WITH nNo_Compra
		replace Venta_Diaria WITH c0.Ventas/30
		replace Presupuesto  WITH nPresup 
		replace Cumpl_Ventas WITH nCumpl_Ventas
		replace Efect_Rutero WITH nEfect_Rutero
		replace Efect_Ventas WITH nEfect_Ventas
		replace dropsize WITH nDropSize
		
	ENDSCAN
	*INSERT INTO c0 (nombre, venta, devol, neto) VALUES ('TOTALES', _venta, _devol, _neto)
	
	SELECT ;
	SUM(Clientes_Rutero ) as Clientes_Rutero ,;
	SUM(Visitas_Realiz) as Visitas_Realiz ,;
	SUM(Pedidos) as Pedidos,;
	SUM(No_Compras) as No_Compras,;
	avg(Venta_Diaria) as Venta_Diaria,;
	SUM(Ventas) as Ventas,;
	SUM(Presupuesto) as Presupuesto FROM c0 INTO CURSOR c8

	SELECT * FROM c0 INTO cursor &curJson readwrite
	
	INSERT INTO &curJson (vendedor,Clientes_Rutero,Visitas_Realiz,Pedidos,No_Compras,Venta_Diaria,Ventas,Presupuesto) values;
	("TOTALES",c8.Clientes_Rutero,c8.Visitas_Realiz,c8.Pedidos,c8.No_Compras,c8.Venta_Diaria,c8.Ventas,c8.Presupuesto)
	FLUSH
	


ENDFUNC


FUNCTION venta_nc (curRet,fIni,fFin,cTabla,cCampoId)
	cCampoRet = 'd.quantity/it.unidadescaja'
	cCampoRet_nc = 'd.quantity/it.unidadescaja'
	IF c_tipo='1'
		cCampoRet = 'd.quantity*(d.price-d.descunit)'
		cCampoRet_nc = 'd.quantity*(d.cost-d.descunit)'
	ENDIF

	TEXT TO cCmd NOSHOW TEXTMERGE 
	SELECT
		s.name as vendedor,
		g.name as grupo,
		SUM(<<cCampoRet>>) as venta,
		0000000000.00 as devol
		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 <<f_desde>> and <<f_hasta>>
		group by 1,2
	union all
	SELECT 
		s.name as vendedor,
		g.name as grupo,
		0000000000.00 as venta,
		SUM(<<cCampoRet_nc>>) as devol
		from ndc as i 
		inner join ndcdet as d on i.id=d.id
		inner join item as it on it.id=d.iditem
		left join <<cTabla>> as g on it.<<cCampoId>>=g.id
		left join salesman as s on s.id=i.idsalesman
		where  !i.void AND i.tipo=1 and i.date between <<f_desde>> and <<f_hasta>>
		group by 1,2
	ENDTEXT
	
	*STRTOFILE(cCmd,'\eddy_ventas.txt')

	oApp.sqlexec(cCmd,curRet)	
ENDFUNC




FUNCTION cobrosAnuales
	TEXT TO cCmd NOSHOW TEXTMERGE 
	SELECT
	t.name as tipo_pago, 
	round(sum(if(MONTH(r.date)= 1,d.monto,0)),2) as Ene,
	round(sum(if(MONTH(r.date)= 2,d.monto,0)),2) as Feb,
	round(sum(if(MONTH(r.date)= 3,d.monto,0)),2) as Mar,
	round(sum(if(MONTH(r.date)= 4,d.monto,0)),2) as Abr,
	round(sum(if(MONTH(r.date)= 5,d.monto,0)),2) as May,
	round(sum(if(MONTH(r.date)= 6,d.monto,0)),2) as Jun,
	round(sum(if(MONTH(r.date)= 7,d.monto,0)),2) as Jul,
	round(sum(if(MONTH(r.date)= 8,d.monto,0)),2) as Ago,
	round(sum(if(MONTH(r.date)= 9,d.monto,0)),2) as Sep,
	round(sum(if(MONTH(r.date)=10,d.monto,0)),2) as Oct,
	round(sum(if(MONTH(r.date)=11,d.monto,0)),2) as Nov,
	round(sum(if(MONTH(r.date)=12,d.monto,0)),2) as Dic,
	round(sum(d.monto),2) as Total_<<cYear>>
	FROM DetaReceiv as d 
	INNER JOIN Receiv as R ON d.Id=R.id 
	INNER JOIN Accountype as t on t.id=d.idaccountype
	WHERE !R.void and d.idAccounType=T.Id and (substr(R.date,1,4) = "<<cYear>>")
	group by 1
	ENDTEXT
	
	*STRTOFILE(cCmd,'\eddycobro.txt')

	oApp.sqlexec(cCmd,'cobros')
	cCampo = 'Total_'+cYear
	SELECT * FROM cobros WHERE &cCampo>0 ORDER BY tipo_pago INTO CURSOR &curJson

ENDFUNC


FUNCTION getVentasGrupo (lUsd)

	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 <<f_desde>> and <<f_hasta>>
	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 + ", " + 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 = 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

ENDFUNC



FUNCTION fixn (cCampo)
	cCampo = ALLTRIM(cCampo)
RETURN STRTRAN(cCampo,' ','_')