LOCAL cYear,cCmd,cTabla

cYear = TRANSFORM(YEAR(DATE()))
IF !EMPTY(oJs.cParam)
	cYear = ALLTRIM(TRANSFORM(oJs.cParam))
ENDIF

cTabla = 'sales'

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,'tabla0')
cCampo = 'Total'+cYear
SELECT * FROM tabla0 WHERE &cCampo>0 ORDER BY vendedor INTO TABLE &cTabla

oJs.json_up(cTabla)
