'Nome       : StcSQL_Res.inc
'Tipo       : SQL
'Data       : 2003/11/20
'Versao     : 1.0
'Descricao  : Resumos por SQL a incluir em listagens

'==============================================================================
'#VER - Historia de modificacoes
'==============================================================================
'- Versao : 1.0   2003/??/?? --------------------------------------------------
'Versao base


'==============================================================================
'#INF - Informacao
'==============================================================================
'------------------------------------------------------------------------------
' codigos de movimento
'------------------------------------------------------------------------------
'Cod  Descricao            Tipo Notas
'---  -------------------- ---- -----------------------------------------------
' 1 - Compra               Ent
' 2 - Dev.Cliente          Ent
' 3 - Dev.Fornec           Sai
' 4 - Quebra               Sai
' 5 - Transferencia        Ent
' 6 - Custos               Ent So deve ser considerado o valor
' 7 - E.Diversas           Ent
' 8 - S.Diversas           Ent
' 9 - Venda                Sai
'10 - Enc.Forn.            --- Nao actualiza existencias
'11 - Act.Inv.Saida        Sai
'12 - Act.Inv.Entr.        Ent
'13 - R.Encom.Forn.        Ent Semelhante a compra
'14 - Saida Taras          --- Nao usado
'15 - Entrada Taras        --- Nao usado
'16 - N.Credito            Sai Semelhante a venda
'17 - Trf.Armazem          --- Apenas movimenta armazens
'18 - Inc.Custos           Ent So deve ser considerado o valor


'==============================================================================
#SQL - Comandos SQL
'==============================================================================
'Base de dados           [BD]=EmpFic$("EMP","Cemp,Emp_Dat.mdb")
Base de dados           [BD]=EmpFic$("EMP","Stc,Stc.mdb")


'- generico -------------------------------------------------------------------
Familias com valores a listar [Cmd]=CmpVnd=+
+SELECT left(Art$,2) as Fam, Sum(qu) as RQT, Sum(Vt) as RVT FROM StcMov{Ano} +
+WHERE RecVal<>255 +
+AND Cmv IN(1,-1,2,-2,3,-3,9,-9,16,-16) +
+AND [Art$] BETWEEN '{Opc_Art.Ini}' AND '{Opc_Art.Fim}' +
+AND Dtl BETWEEN {Opc_Dtl.Ini} AND {Opc_Dtl.Fim} +
+GROUP BY left(Art$,2) +
+ORDER BY left(Art$,2)

Familias com movimento        [Cmd]=FamMov=+
+SELECT left(Art$,2) as Fam FROM StcMov{Ano} +
+WHERE [Art$] BETWEEN '{Opc_Art.Ini}' AND '{Opc_Art.Fim}' +
+AND Dtl BETWEEN {Opc_Dtl.Ini} AND {Opc_Dtl.Fim} +
+GROUP BY left(Art$,2) +
+ORDER BY left(Art$,2)


'- vendas ---------------------------------------------------------------------
Familias com valores a listar [Cmd]=VndFam=+
+SELECT left(Art$,2) as Fam, Sum(qu) as RQT, Sum(Vt) as RVT FROM StcMov{Ano} +
+WHERE RecVal<>255 +
+AND Cmv IN(2,-2,9,-9,16,-16) +
+AND [Art$] BETWEEN '{Opc_Art.Ini}' AND '{Opc_Art.Fim}' +
+AND Dtl BETWEEN {Opc_Dtl.Ini} AND {Opc_Dtl.Fim} +
+GROUP BY left(Art$,2) +
+ORDER BY left(Art$,2)


Familia - Vendas - Valores a somar    [Cmd]=ResVndSom=+
+SELECT Sum(qu) as VQTS, Sum(Vt) as VVTS FROM StcMov{Ano} +
+WHERE RecVal<>255 +
+AND Cmv IN(9,-9) +
+AND [Art$] BETWEEN '{Fam.Ini}' AND '{Fam.Fim}' +
+AND Dtl BETWEEN {Opc_Dtl.Ini} AND {Opc_Dtl.Fim}


Familia - Vendas - Valores a subtrair [Cmd]=ResVndSub=+
+SELECT Sum(qu) as VQTR, Sum(Vt) as VVTR FROM StcMov{Ano} +
+WHERE RecVal<>255 +
+AND Cmv IN(2,-2,16,-16) +
+AND [Art$] BETWEEN '{Fam.Ini}' AND '{Fam.Fim}' +
+AND Dtl BETWEEN {Opc_Dtl.Ini} AND {Opc_Dtl.Fim}


'- compras --------------------------------------------------------------------
Familias com valores a listar [Cmd]=CmpFam=+
+SELECT left(Art$,2) as Fam, Sum(qu) as RQT, Sum(Vt) as RVT FROM StcMov{Ano} +
+WHERE RecVal<>255 +
+AND Cmv IN(1,-1,3,-3) +
+AND [Art$] BETWEEN '{Opc_Art.Ini}' AND '{Opc_Art.Fim}' +
+AND Dtl BETWEEN {Opc_Dtl.Ini} AND {Opc_Dtl.Fim} +
+GROUP BY left(Art$,2) +
+ORDER BY left(Art$,2)


Familia - Compras - Valores a somar    [Cmd]=ResCmpSom=+
+SELECT Sum(qu) as CQTS, Sum(Vt) as CVTS FROM StcMov{Ano} +
+WHERE RecVal<>255 +
+AND Cmv IN(1,-1) +
+AND [Art$] BETWEEN '{Fam.Ini}' AND '{Fam.Fim}' +
+AND Dtl BETWEEN {Opc_Dtl.Ini} AND {Opc_Dtl.Fim}


Familia - Compras - Valores a subtrair [Cmd]=ResCmpSub=+
+SELECT Sum(qu) as CQTR, Sum(Vt) as CVTR FROM StcMov{Ano} +
+WHERE RecVal<>255 +
+AND Cmv IN(3,-3) +
+AND [Art$] BETWEEN '{Fam.Ini}' AND '{Fam.Fim}' +
+AND Dtl BETWEEN {Opc_Dtl.Ini} AND {Opc_Dtl.Fim}


'- entradas -------------------------------------------------------------------
Resumo por Familia - Valores a somar            [Cmd]=EntRqvSom=+
+SELECT Sum(qu) as ERQTS, Sum(Vt) as ERVTS FROM StcMov{Ano} +
+WHERE RecVal<>255 +
+AND Cmv IN(1,-1,2,-2,5,-5,7,-7,12,-12,13,-13) +
+AND [Art$] BETWEEN '{Fam.Ini}' AND '{Fam.Fim}' +
+AND Dtl <= {Opc_Dtl.Fim}


Resumo por Familia - Valores simples a somar    [Cmd]=EntRvsSom=+
+SELECT Sum(Vt) as ERVSS FROM StcMov{Ano} +
+WHERE RecVal<>255 +
+AND Cmv IN(6,-6,18,-18) +
+AND [Art$] BETWEEN '{Fam.Ini}' AND '{Fam.Fim}' +
+AND Dtl <= {Opc_Dtl.Fim}


'- saidas ---------------------------------------------------------------------
Resumo por Familia - Valores a somar            [Cmd]=SaiRqvSom=+
+SELECT Sum(qu) as SRQTS, Sum(Vt) as SRVTS FROM StcMov{Ano} +
+WHERE RecVal<>255 +
+AND Cmv IN(3,-3,4,-4,9,-9,11,-11,16,-16) +
+AND [Art$] BETWEEN '{Fam.Ini}' AND '{Fam.Fim}' +
+AND Dtl <= {Opc_Dtl.Fim}


'- existencia -----------------------------------------------------------------
Resumo por Familia - Valores a somar            [Cmd]=ExiRqSom=+
+SELECT Sum(qu) as EXIRQS FROM StcMov{Ano} +
+WHERE RecVal<>255 +
+AND Cmv IN(1,-1,2,-2,5,-5,7,-7,12,-12,13,-13) +
+AND [Art$] BETWEEN '{Fam.Ini}' AND '{Fam.Fim}' +
+AND Dtl <= {Opc_Dtl.Fim}


Resumo por Familia - Valores a subtrair         [Cmd]=ExiRqSub=+
+SELECT Sum(qu) as EXIRQR FROM StcMov{Ano} +
+WHERE RecVal<>255 +
+AND Cmv IN(3,-3,4,-4,9,-9,11,-11,16,-16) +
+AND [Art$] BETWEEN '{Fam.Ini}' AND '{Fam.Fim}' +
+AND Dtl <= {Opc_Dtl.Fim}

##


'==============================================================================
#VAR - Variaveis
'==============================================================================
'---------------------------------------------------------->'Comentario (61)
'------------------------------------------------------->'Comentario (58)
'---------------------------------------------------->'Comentario (55)
'------------------------------------------------->'Comentario (52)
'------------------------------------->'Comentario (40)

'Campos
famCod   = SqlCmp$("Fam")                             'codigo da familia

vPerQuX  = FrmNum$( 10.2,SqlCmp("RQT"))               'quantidade (total)
vPerVtX  = FrmNum$( 14.2,SqlCmp("RVT"))               'valor      (total)

'vendas
vVndQu   = FrmNum$( 10.2,SqlOpcCon("VQTS")-SqlOpcCon("VQTR"))                                             'quantidade
vVndVt   = FrmNum$( 14.2,SqlOpcCon("VVTS")-SqlOpcCon("VVTR"))                                             'valor
vVpmc    = FrmNum$( 14.2,0+(SqlOpcCon("ERVTS")+SqlOpcCon("ERVSS"))/SqlOpcCon("ERQTS")*(SqlOpcCon("VQTS")-SqlOpcCon("VQTR")))  'valor a PMC

'vendas - totais
tVndN    = FrmNum$(  5.0,TotCnt("vVndQu",0))          'numero de registos 
tVndQu   = FrmNum$( 10.2,total(vVndQu,0))             'quantidade
tVndVt   = FrmNum$( 14.2,total(vVndVt,0))             'valor
tVpmc    = FrmNum$( 14.2,total(vVpmc,0))              'valor a PMC

'vendas - subtotais
sVndN    = FrmNum$(  5.0,TotCnt("vVndQu",1))          'numero de registos
sVndQu   = FrmNum$( 10.2,total(vVndQu,1))             'quantidade
sVndVt   = FrmNum$( 14.2,total(vVndVt,1))             'valor
sVpmc    = FrmNum$( 14.2,total(vVpmc,1))              'valor a PMC

'compras
vCmpQu   = FrmNum$( 10.2,SqlOpcCon("CQTS")-SqlOpcCon("CQTR"))     'quantidade
vCmpVt   = FrmNum$( 14.2,SqlOpcCon("CVTS")-SqlOpcCon("CVTR"))     'valor

'compras - totais
tCmpN    = FrmNum$(  5.0,TotCnt("vCmpQu",0))          'numero de registos
tCmpQu   = FrmNum$( 10.2,total(vCmpQu,0))             'quantidade
tCmpVt   = FrmNum$( 14.2,total(vCmpVt,0))             'valor

'compras - subtotais
sCmpN    = FrmNum$(  5.0,TotCnt("vCmpQu",1))          'numero de registos
sCmpQu   = FrmNum$( 10.2,total(vCmpQu,1))             'quantidade
sCmpVt   = FrmNum$( 14.2,total(vCmpVt,1))             'valor

'entradas
vEntQe   = FrmNum$( 10.2,SqlOpcCon("ERQTS"))          'quantidade
vEntVe   = FrmNum$( 14.2,SqlOpcCon("ERVTS"))          'valor
vEntVs   = FrmNum$( 14.2,SqlOpcCon("ERVSS"))          'valor simples (custos)
vEntPmc  = FrmNum$( 14.2,0+(SqlOpcCon("ERVTS")+SqlOpcCon("ERVSS"))/SqlOpcCon("ERQTS"))   'valor a PMC

'entradas - totais
tEntN    = FrmNum$(  5.0,TotCnt("vEntQe",0))          'numero de registos
tEntQe   = FrmNum$( 10.2,total(vEntQe,0))             'quantidade
tEntVe   = FrmNum$( 14.2,total(vEntVe,0))             'valor
tEntVs   = FrmNum$( 14.2,total(vEntVs,0))             'valor simples (custos)

'entradas - subtotais
sEntN    = FrmNum$(  5.0,TotCnt("vEntQe",1))          'numero de registos
sEntQe   = FrmNum$( 10.2,total(vEntQe,1))             'quantidade
sEntVe   = FrmNum$( 14.2,total(vEntVe,1))             'valor
sEntVs   = FrmNum$( 14.2,total(vEntVs,1))             'valor simples (custos)


'existencia
vExiQu   = FrmNum$( 10.2,SqlOpcCon("EXIRQS")-SqlOpcCon("EXIRQR"))
vExiVt   = FrmNum$( 14.2,0+(SqlOpcCon("EXIRQS")-SqlOpcCon("EXIRQR"))*(SqlOpcCon("ERVTS")+SqlOpcCon("ERVSS"))/SqlOpcCon("ERQTS"))
vExiQe   = FrmNum$( 10.2,SqlOpcCon("EXIRQS"))
vExiQs   = FrmNum$( 10.2,SqlOpcCon("EXIRQR"))

'existencias - totais
tExiQu   = FrmNum$( 10.2,total(vExiQu,0))
tExiVt   = FrmNum$( 14.2,total(vExiVt,0))

'existencias - subtotais
sExiQu   = FrmNum$( 10.2,total(vExiQu,1))
sExiVt   = FrmNum$( 14.2,total(vExiVt,1))

##

