#*Var,PVend,Primero Vendedor,4,
#*Var,UVend,Ultimo Vendedor ,4,
*Var,PCli,Primero Cliente ,4,
*Var,UCli,Ultimo  Cliente ,4,
*Var,PData,Primero Data    ,6,
*Var,UData,Ultima  Data    ,6,

*Landscape,0
*LinhaPorPagina,61
### *Titulo,aaaaa
*MTitulo,Listagem de IRS retido a clientes/Fornecedores. 

#int x,int cx,int cy,int tot,int just,int y)
#Grid  x , cx,cy,tot,just, y
*Grid, 50,1800,0,0,0,0
*Grid, 50,140,0,0,0,0
*Grid,  0,150,0,0,0,0
*Grid,  0,110,0,0,0,0
*Grid,  0,300,0,0,0,0
*Grid,  0,150,0,0,0,0
*Grid,  0,300,0,0,0,0
*Grid,  0,100,0,0,1,0
*Grid,  0,100,0,0,1,0
*Grid,  0,100,0,0,1,0
*Grid,  0, 50,0,0,0,0
*Grid,  0,100,0,0,1,0
*Grid,  0,100,0,0,1,0
*Grid,  0,100,0,0,1,0
*Grid,  0,100,0,0,1,0
*Grid,  0,120,0,1,1,0


*Totaliza,1,
*Destaca,1,

DROP TABLE IF EXISTS Mapa1;

CREATE TABLE Mapa1 (
#       Vendedor  char(60),
       TipoDoc   char(20),
       NumDoc    int ,
       DataDoc   date,
       NumCli    int,
       NomeCli   char(60),
       Codigo     char(20),
       Designacao char(60),
       Qtd        double,
       PrecoUnit   double,
       Desconto    double,
       Iva     double,
       Valor   double,
       Custo   double,
       Familia char(20)
#       Comiss  double,
#       ValorComiss double
        );


INSERT into Mapa1
SELECT
# Concat( c.no_vended,'-',vd.nome),
 'Factura',
 a.num_doc,
 c.d_doc,
 c.no_CF,
 c.nome_CF,
 a.codigo,
 a.designacao,
 a.quantidade,
 a.preco_unit,
 a.desc1,
# a.desc_1,
# a.desc2,
# a.desc_2,
 a.iva,
 a.valor,
 a.ult_custo,
 if(art.familia is null,' ',art.familia) as familia
# v.des_art,
#if( v.comissao is null,0,v.comissao) as Comiss,
#if( v.comissao is null,0,a.valor*v.comissao/100 ) as 'Valor Comis.'

 FROM documento_linhas_d2 as a 
 LEFT join documento_d2 as c on c.no_doc=a.num_doc
# LEFT join Vendedores as vd on vd.numero=c.no_vended 
 LEFT join artigos  as art on art.codigo=a.codigo
# LEFT join vendedores_comissoes_familia as v on c.no_vended=v.no_vend and v.cod_art=art.familia
WHERE
  d_doc>=PData and d_doc<=UData
  and no_cf>=PCli and no_cf<=UCli
#  and no_vended>=PVend and no_vended<=UVend
  and length(a.codigo)>0
ORDER BY 
  num_doc
; 
 
INSERT into Mapa1
SELECT
# Concat( c.no_vended,'-',vd.nome),
 'V.Dinheiro',
 a.num_doc,
 c.d_doc,
 c.no_CF,
 c.nome_CF,
 a.codigo,
 a.designacao,
 a.quantidade,
 a.preco_unit,
 a.desc1,
 a.iva,
 a.valor,
 a.ult_custo,
 if(art.familia is null,' ',art.familia) as familia,
# if( v.comissao is null,0,v.comissao) as Comiss
# if( v.comissao is null,0,a.valor*v.comissao/100 ) as 'Valor Comis.'
 FROM documento_linhas_d1 as a 
 LEFT join documento_d1 as c on c.no_doc=a.num_doc
# LEFT join Vendedores as vd on vd.numero=c.no_vended 
 LEFT join artigos  as art on art.codigo=a.codigo
# LEFT join vendedores_comissoes_familia as v on c.no_vended=v.no_vend and v.cod_art=art.familia
WHERE
  d_doc>=PData and d_doc<=UData
  and no_cf>=PCli and no_cf<=UCli
#  and no_vended>=PVend and no_vended<=UVend
  and length(a.codigo)>0
ORDER BY 
#  no_vended,
   num_doc
; 

SELECT 
  Concat('Desde ',Min(DataDoc),' Ate ',Max(dataDoc)) as ' '
FROM Mapa1
;


*Titulo,0
*Totaliza,1
*LinhaBranco,0
*Destaca,0,
*ResetGrid,


#int x,int cx,int cy,int tot,int just,int y)
#Grid  x , cx,cy,tot,just, y
*Grid, 50,1800,0,0,0,0
*Grid, 50,140,0,0,0,0
*Grid,  0,150,0,0,0,0
*Grid,  0,110,0,0,0,0
*Grid,  0,300,0,0,0,0
*Grid,  0,150,0,0,0,0
*Grid,  0,300,0,0,0,0
*Grid,  0,100,0,0,1,0
*Grid,  0,100,0,0,1,0
*Grid,  0,100,0,0,1,0
*Grid,  0, 50,0,0,0,0
*Grid,  0,100,0,0,1,0
*Grid,  0,100,0,0,1,0
*Grid,  0,100,0,0,1,0
*Grid,  0,100,0,0,1,0
*Grid,  0,120,0,1,1,0


*Totaliza,1,
*Destaca,1,

  
SELECT 
#       Vendedor,
       Familia,
       TipoDoc,
       NumDoc,
       NumCli ,
       NomeCli ,
       Codigo ,
       Designacao,
       Qtd ,
       PrecoUnit ,
       Desconto,
       Iva  ,
       Valor ,
       Custo 
#       Familia,
#       Comiss,
#       Round(ValorComiss,2) as 'ValorComiss'
FROM Mapa1
ORDER BY 
  familia,numdoc
;

*Titulo,0
*Totaliza,1
*LinhaBranco,0
*Destaca,0,
*ResetGrid,

#Grid  x , cx,cy,tot,just, y
*Grid,450,450,0,0,0,0
*Grid,  0,600,0,0,0,0
*Grid,  0,350,0,0,0,0
*Grid,  0,220,0,0,1,0

SELECT 
'Sub-Total por famila' ,
Familia,       
#Vendedor ,    
 Round(Sum(ValorComiss),2)
FROM Mapa1
GROUP BY 
#  Vendedor,
  Familia 
ORDER BY 
#  Vendedor,
  Familia
;


*Titulo,0
*Totaliza,1
*LinhaBranco,0
*Destaca,0,
*ResetGrid,

#Grid  x , cx,cy,tot,just, y
*Grid,450,450,0,0,0,0
*Grid,  0,600,0,0,0,0
*Grid,  0,350,0,0,0,0
*Grid,  0,220,0,0,1,0

SELECT 
'Sub-Total por Cliente' ,
Concat(NumCli,'-',NomeCli) ,       
Vendedor ,    
 Round(Sum(ValorComiss),2)
FROM Mapa1
GROUP BY 
  Vendedor,NumCli
ORDER BY 
  Vendedor,NumCli
;


*Titulo,0
*Totaliza,1
*LinhaBranco,0
*Destaca,0,
*ResetGrid,

#Grid  x , cx,cy,tot,just, y
#*Grid,490,450,0,0,0,0
*Grid,900,600,0,0,0,0
*Grid,  0,350,0,0,0,0
*Grid,  0,220,0,0,1,0

SELECT 
 'Total' ,
Vendedor ,    
 Round(Sum(ValorComiss),2)
FROM Mapa1
GROUP BY 
  Vendedor
ORDER BY 
  Vendedor
;

