Jump to content
Fórum Script Brasil
  • 0

(Resolvido) Lazarus 1.6 - calculo em SQL via ZQuery


Question

Olá !

   alguém por gentileza poderia me dar um help :

 

   Estou num dilema a dias com o seguinte senário: Com Lazarus 1.6 com ZQuery    e    SQLite 3.31.1
      Tenho uma tabela detalhes ,de poucos registros (100)
   que com o seguinte select eu tento fazer um  cálculo que é baseado em uma soma de uma coluna de um lote de registros (uso GROUPBY).

Uso uma Query master e uma Query detalhes que estao ligadas por Master / detalhes id X idNC. Puxei um total, campos de data e numero de

Nota  da tabela master pelo metodo LEFT OUTER JOIN que me traz certinho e ainda traz valores que somo no SQL.
  Antes tinha feito uma miscelânea que alguns calculos foi direto no SELECT outros usei por metodo CalcField na tabela detalhe.
  Como modulo somente para digitação está perfeito , monta toda a massa de dados que preciso, mas agora nescessito de
  trabalhar com datas nesta base e o agravante é que existe uma dependência nesta forma que  me obriga a ter a tabela master
  ligada, porque ela que dá alguns valores totalizados (tot_custos) e liga a chave primaria quando na seleção .
  Estou tentando agora eliminar a relação da tabela master para que o SELECT na detalhes faça tudo com autonomia.


    SELECT  A.id,
            A.idNC,
            B.data,         AS data_FK,
            B.num_NC        AS num_nota_FK,
            nome_do_item,  
            qde,preco_un,
            tot_item,
            IFNULL(B.val1,0) + IFNULL(B.val2,0) + IFNULL(B.val3,0)    AS   tot_custos ,
            IFNULL((qde * preco_un),0)     AS   tot_item,   
            IFNULL((SUM(tot_item)),0)      AS   tot_colunaItem,
            (((IFNULL(tot_custos,0) / IFNULL(tot_colunaItem,0)) * 100)   *    (tot_item / 100))   AS  custo_item

            FROM tboperacoes    AS   A
            LEFT OUTER JOIN  tbnotacorret AS B ON (B.id  = A.idNC)
            GROUP BY A.idNC
 

       Obs. Usei IFNULL para precaver erro de entrada ainda nula ou divisao por zero.

             

       Onde id : Chave primaria Identificadora da tabela master que seve de cabeçalho e numeração e data da nota.
            idNC : Referencial na tabela detalhes.
            Alias A : Refere-se a tabela detalhes.
            Alias B : Refere-se a tabela master.

            A expressão do calculo já esta ok tambem, mas não apresenta todos os registros, só o primeiro de cada nota. Eu queria de todos.

   O PROBLEMA: quando retiro a ligação master/detale ela não varre todos os registros apresentando somente o primeiro de cada nota.
   No grid da tabela master rodo este select que esta em procedure Calculquery para cada registro selecionado na query master via um grid de edição.

  para poder prosseguir em pesquisas nestes resultados já conseguidos.
   já tentei com ROOLUP mas SQLite não tem isso só emulado assim mesmo não sei se é isso que faria efeito.
   Preciso eliminar o método master/detalhes das querys que coloquei via propriedades master fild/master source etc..  e utilizar tudo via SQL.

Agradeço muito a quem se envolver neste help, que poderia até servir de estudo de caso para os demais.

 

Link to post
Share on other sites

25 answers to this question

Recommended Posts

  • 0

Se existe a necessidade de fazer referencia entre as tabelas, não é necessario usar IFNULL, pois por padrão, campos nulos não são calculados

FROM tboperacoes    AS   A
LEFT OUTER JOIN  tbnotacorret AS B ON (B.id  = A.idNC)
GROUP BY A.idNC

OBs: Reveja essa parte

https://www.devmedia.com.br/clausulas-inner-join-left-join-e-right-join-no-sql-server/18930

https://www.devmedia.com.br/sql-join-entenda-como-funciona-o-retorno-dos-dados/31006

abraço

Link to post
Share on other sites
  • 0
Posted (edited)

Obrigado pela ajuda e o material.  Estou estudando melhor em meus testes que  não surtiram efeito com os demais JOINS possíveis , ainda mais que os tipos FULL JOINs  não são suportados no SQLite nem no mais novo , mas vou renovar o select acima para  um exemplo mais simplificado e assim ficara mais fácil rodar em um executor de SQL. Então quem mais for me ajudar ficará mais facil.

Quanto aos IFFNULs  eu já retirei os que não são nescessários execeto o de divisor por zero que

tem que haver , pois a tabela pode iniciar zerada e sem isso da erro de div. por 0.

Edited by Lazaruz
Não terminei. Não obtive ainda solução. Vide acima.
Link to post
Share on other sites
  • 0
Posted (edited)

Continuando:

Segue as duas tabelas para melhor entendimento,

                                         TBNOTA  (master)

id    num_nc             data             val1      val2     val3

1       0001           2020-05-17    10.0       1.0      3.0
2       9619           2020-04-01     0.09      0.57    0.0
3       0003           2020-03-15     7.36      1.13    0.0
AUTO                   

          

                                          TBOPERACAO   (detalhes)

 id                nome_item_             qde         preco_un         idNC

1                 ITEM-X1                      1.0            525.3             2
2                 ITEM-B2                      15.0         103.87           2
3                 ITEM-B2                      10.0         104.0             2
4                 ITEM-C3                       41.0        452.0             1
5                 ITEM-C3B                    15.0           98.8             1
Auto         

O seguinte select baseado nas tabelas acima produz o resultado abaixo:

  SELECT   A.id, idNC, B.DATA AS data_FK, B.num_nc AS nota_FK, nome_do_item, qde, preco_un,  
                   (qde * preco_un)                 AS tot_item,
                   B.val1 + B.val2 + B.val3         AS  tot_custos,              
                   SUM(qde * preco_un )             AS tot_coluna_item,
                         -->CALCULO DO CUSTO DE CADA OPERACAO
                   (((B.val1 + B.val2 + B.val3)    /    IFNULL((SUM(qde * preco_un )),0) * 100  
                    *  ((qde * preco_un) / 100)))   AS   CUSTO_ITEM     
             
                     FROM tboperacao                  AS  A            
                     LEFT OUTER JOIN  tbnota AS B ON (B.id  = A.idNC)
    
                     GROUP BY idNC

   RESULTADO:

id   idNC    data_FK     nota_FK     nome_do_item     qde   preco_un     tot_item   tot_coluna_item        CUSTO_ITEM

4       1    2020-05-17    0001         ITEM-C3              41.0      452.0        18532.0         14.0    20014.0               12.96


1       2    2020-04-01    9619          ITEM-X1               1.0       525.3            525.3         0.66     3123.35               0.11
       

          O objetivo desta é apurar o custo proporcional de cada ítem  (CUSTO_ITEM)  que traz corretamente mas,     
         observe que  no resultado não aparece os outros ítens de detalhes de cada nota. Somente o primeiro de cada nota.

         Quando eu monto tudo isso no programa eu  consigo ver os outros detalhes se eu selecionar manualmente um  ítem da tabela master, então aparece todos os detalhes da nota escolhida e todos os cálculos corretos, lembrando que no programa eu utilizo uma parte do cálculo como por ex. -->CALCULO DO CUSTO DE CADA OPERACAO  por  método CalcField da query QRYOPERACAO. O que eu precisava é que isso funcionasse apenas em um select , sem nenhuma linha de metodo CalcField.  Porque?  Porque agora com cada operacao de todas as notas eu terei que ter uma base de dados única para fazer select por datas ou mês.    Acho que agora ficou mehor .     Mais uma vez agradeço muito  a quem se propuser me ajudar . Obrigado e aguardo.

        

Só para lembrar , eu utilizei GROUP BY neste selct somente para ilustrar e poder fazer os calculos,    no programa eu não usei isto , usei método de propriedade master / detalhes  que mostra somente uma nota por vêz quando seleciona ela.

Edited by Lazaruz
Link to post
Share on other sites
  • 0

Voce não acha que seria muito mais simples, selecionar os registros que voce quer das tabelas, jogar em um dbgrid e depois proceder aos calculos dos registros ?

A vantagem disso é que a performance fica mais rapida. Se voce acha que fazer os calculos ficaria lento, então voce pode optar em usar ( Threads  -  para efetuar os calculos em segundo plano )  ...  o usuario nem vai perceber.

Quando eu monto tudo isso no programa eu  consigo ver os outros detalhes se eu selecionar manualmente um  ítem da tabela master, então aparece todos os detalhes da nota escolhida e todos os cálculos corretos

Se voce quer ver todos os campos de um registro, os mesmos deverão aparecer na Select.

Sugestão: use o sqlite expert  ( Gerenciador de Banco de Dados)  ... com ele voce pode construir Selects utilizando as suas tabelas e testá-las em tempo real.

Uma vez criada e testada a Select, voce pode usá-la no seu programa.

http://www.sqliteexpert.com/download.html

ou o SQLite Studio

https://sqlitestudio.pl/index.rvt

abraço

Link to post
Share on other sites
  • 0

Quando eu disse ver os detalhes, quis dizer  registros de detalhes. Note que nas tabelas que postei , por ex. os  registros de detalhes

de uma nota são mais que um.    Quanto a sugestao do sqlstudio agradeço vou estudar o caso , mas já uso o DBManager e já testei o select inclusive  os INNER JOINS sugerido por voce .

 

...selecionar os registros que voce quer das tabelas, jogar em um dbgrid e depois proceder aos calculos dos registros ? A ideia que voce colocou para que ...

  Foi o que fiz, o grid já traz tudo que preciso, como já tinha dito antes estou amarrado em um grid temporário de um conjunto de registros que pertence somente a uma nota selecionada. Na verdade utilizo dois grids um de selecao da nota (master) e outro grid onde apresenta os registros de detalhes pertencentes ao registro master na tabela superior. Até aí tudo bem , o usuario já consegue preencher incluindo as notas e em cada uma ele já acrecenta os registros de operação no grid de operações (detalhes).

   O Lazarus trabalha identico ao Delphi no conceito Master / Detalhes, e achei no inicio do projeto ótimo para pelo menos construir a tela de entrada de dados, o que já está funcional .  Bom...  feito isso eu preciso evoluir  agora construindo uma base desses dados de maneira que eu possa fazer seleção em cima de datas. O problema é que não consigo fazer esta base única com esses calculos , e nem consegui montar uma tabela temporária porque não consegui levar para ela o resultado CUSTO_CUSTO item obtido na forma da fase  que já descrevi como tela de entrada de dados do usuário.

  .....Threads  -  para efetuar os calculos em segundo plano..

  Como não sou nenhum programador avançado não entendo de Thereads.

  Mas estou aberto a qualquer pergunta poder dar entendimento e evoluir.

Complementando entendimentos, utilizo estes calculos na tela de entradas pois ao final da inclusão das notas e os detalhes dela o usuário bate o valor total final da nota com a nota oficial, assim ele sabe que não digitou nada errado. É aí que os cálculos não podem sair dai,   mas por outro lado eu poderia até repetir eles em uma outra tabela mas o valor de custo de item deverá estar nela.

Link to post
Share on other sites
  • 0

ok... vamos ver se chegamos a uma solução

quantos campos voce tem na tabela Master e na tabela Detalhes ?

a relação entre as 2 tabelas é pelo campo id ?

os calculos são efetuados depois da seleção dos registros ?

quantos campos da tabela são resultado de calculos ( ou seja vão receber um valor de calculo ) ?

qual a quantidade aproximada de registros, depois do select ?

Link to post
Share on other sites
  • 0
Posted (edited)

ok, 

Campos da tabela se voce for precisar de montar no seu ambiente é só copiar como as tabelas acima que são exatamente estes:

MATER : são 6 campos como vê acima.

DETALHES: 10 campos como acima descrito tambem.

RELAÇÃO: id da tabela MASTER (tbnotas) =  idNC da tabela DETALHES (tboperacao).

CÁLCULOS efetuados após seleção de apenas um registro da tabela masterTBNOTAS.

CAMPOS QUE RESEBEM VALOR:    3    

   tot_item(qde * preco_un)    

   tot_coluna_item(sum(qde * preco_un)    

   CUSTO_ITEM (ver expressao no select:           -->CALCULO DO CUSTO DE CADA OPERACAO
                                  (((B.val1 + B.val2 + B.val3)    /    IFNULL((SUM(qde * preco_un )),0) * 100  
                                                      *  ((qde * preco_un) / 100)))   AS   CUSTO_ITEM     
 

QDE DE REGISTROS APOS SELECT:  Variável conforme número de registros de operações lançadas na TBOPERACAO, mas na méida não pssa de 15 diário.

 

Edited by Lazaruz
ajuste tabulação
Link to post
Share on other sites
  • 0

ok... agora quero que voce me passe  suas tabelas com todos os campos e com alguns registros, da maneira como voce passou

                                       TBNOTA  (master)

id    num_nc             data             val1      val2     val3

1       0001           2020-05-17    10.0       1.0      3.0
2       9619           2020-04-01     0.09      0.57    0.0
3       0003           2020-03-15     7.36      1.13    0.0
AUTO                   

          

                                          TBOPERACAO   (detalhes)

 id                nome_item_             qde         preco_un         idNC

1                 ITEM-X1                      1.0            525.3             2
2                 ITEM-B2                      15.0         103.87           2
3                 ITEM-B2                      10.0         104.0             2
4                 ITEM-C3                       41.0        452.0             1
5                 ITEM-C3B                    15.0           98.8             1
Auto         

Coloque com valores para que eu possa montar essas tabelas aqui e testar

Link to post
Share on other sites
  • 0

 Como  assim passar as tabelas, são estas mesmas e os valores tambem. Voce quer o select creator delas em SQL? É  isso?

Se for isso então la vai>>

 

- Table: tbnota
--
CREATE TABLE [tbnota]
(
    [id] integer NOT NULL  PRIMARY KEY AUTOINCREMENT ,
    [num_nc] varchar (12),
    [data] datetime,
    [val1] float,
    [val2] float,
    [val3] float
);


--
-- Dumping Table Data: tbnota
--
BEGIN;
-- GO
INSERT INTO [tbnota] ([id], [num_nc], [data], [val1], [val2], [val3]) VALUES

('1', '0001', '2020-05-17 00:00:00.000', '10.0', '1.0', '3.0');
-- GO
INSERT INTO [tbnota] ([id], [num_nc], [data], [val1], [val2], [val3]) VALUES

('2', '9619', '2020-04-01 00:00:00.000', '0.09', '0.57', '0.0');
-- GO
INSERT INTO [tbnota] ([id], [num_nc], [data], [val1], [val2], [val3]) VALUES

('3', '0003', '2020-03-15 00:00:00.000', '7.36', '1.13', '0.0');
-- GO
COMMIT;

-- Table: tboperacao
--
CREATE TABLE [tboperacao]
(
    [id] integer NOT NULL  PRIMARY KEY AUTOINCREMENT ,
    [nome_do_item] varchar (14),
    [qde] float,
    [preco_un] float,
    [idNC] integer
);

 

--
-- Dumping Table Data: tboperacao
--
BEGIN;
-- GO
INSERT INTO [tboperacao] ([id], [nome_do_item], [qde], [preco_un], [idNC])

VALUES('1', 'ITEM-X1', '1.0', '525.3', '2');
-- GO
INSERT INTO [tboperacao] ([id], [nome_do_item], [qde], [preco_un], [idNC])

VALUES('2', 'ITEM-B2', '15.0', '103.87', '2');
-- GO
INSERT INTO [tboperacao] ([id], [nome_do_item], [qde], [preco_un], [idNC])

VALUES('3', 'ITEM-B2', '10.0', '104.0', '1');
-- GO
INSERT INTO [tboperacao] ([id], [nome_do_item], [qde], [preco_un], [idNC])

VALUES('4', 'ITEM-C3', '41.0', '452.0', '1');
-- GO
INSERT INTO [tboperacao] ([id], [nome_do_item], [qde], [preco_un], [idNC])

VALUES('5', 'ITEM-C3B', '15.0', '98.8', '1');
-- GO
COMMIT;


-- Isso deve facilitar néhh?!

Link to post
Share on other sites
  • 0

ok... facilitou

SELECT  A.id, idNC, B.data AS data_FK, B.num_nc AS nota_FK, nome_item, qde, preco_un,  
(qde * preco_un) AS tot_item,
 B.val1 + B.val2 + B.val3 AS  tot_custos,              
SUM(qde * preco_un ) AS tot_coluna_item,
(((B.val1 + B.val2 + B.val3)/IFNULL((SUM(qde * preco_un )),0) * 100  
*  ((qde * preco_un) / 100)))   AS   CUSTO_ITEM     
FROM tboperacao AS A
LEFT OUTER JOIN  tbnota AS B ON (B.id = A.idNC)
GROUP BY id

o Resultado foi esse

"id","idNC","data_FK","nota_FK","nome_item","qde","preco_un","tot_item","tot_custos","tot_coluna_item","CUSTO_ITEM"
1,2,2020-04-01,2,"ITEM-X1",1,525.30,525.30,,525.30,
2,2,2020-04-01,2," ITEM-B2 ",15,103.87,1558.05,,1558.05,
3,2,2020-04-01,2," ITEM-B2 ",10,104.00,1040.00,,1040.00,
4,1,2020-05-17,1,"ITEM-C3",41,452.00,18532.00,14.00,18532.00,14.000000
5,1,2020-05-17,1,"ITEM-C3B",15,98.80,1482.00,14.00,1482.00,14.000000

Obs: Era isso que voce queria ?

Veja que coloquei no GROUP BY  id

se voce colocar idNC só vão aparecer 2 registros :

 ITEM-C3  ( 1 ) 

ITEM-X1 ( 2 )

os demais registros não aparecem porque estão dentro do grupo e tem o mesmo valor de idNC.

Link to post
Share on other sites
  • 0

Vamos lá, agora vai ficar melhor para voce entender onde quero chegar:

observe o resultado que encontro:

id   idNC    data_FK     nota_FK     nome_do_item     qde   preco_un     tot_item   tot_coluna_item        CUSTO_ITEM

1       2    2020-04-01    9619          ITEM-X1               1.0       525.3            525.3         0.66     3123.35               0.11

 

Nossa referencia  por enquanto agora sera este item ok?!     O custo deste item é    0,11 porque ouve um rateio

nos itens da nota num. 2  como demonstra abaixo que destaquei da base de notas (obs id 2 é o num da nota), pois

a soma dos custos de totos os registros desta nota 2 é val1 + val2 + val3 que é 0,66 ,  conforme o bloco  de calculo no select prevê e apura o CUSTO_ITEM.   Isto quer dizer que proporcionalmente nos outros ítens de nota 2 (que só falta 2) a soma dos custos proporcionais tem que dar 0,66. Portanto o custo do item de nome ITEM-X1 é 0,11 e quando voce alterou o GROUP para id  o calculo saiu do objetivo que é calcular o custo exato de cada item  em uma nota baseado no custo total dela (val1+val2+val3).

TBNOTA  (master)

id    num_nc             data             val1      val2     val3
2       9619           2020-04-01     0.09      0.57    0.0               = custo das operacaoes desta nota = 0,66

Agora sim voce pode ter uma visão mais clara do problema, que parece não ser tão fácil resolver a não ser que se tenha uma visão matemática agregado a um conhecimento profundo de SQL.  E acho que voce deve ter porque de cara me ajudou muito na simplificação do select retirando os IFNUL . Eu não tenho tanto conhecimento assim porque o meu é baseado em tabelas diretas que utilizam indices sempre, e são as PARADOX. Estou aos poucos migrando coisas para SQL e há muitos comandos que eu ainda não utilizei.

   Não sei como fazer isto neste caso que aliaz vai dar um bom posta demonstrativo e instrutivo.

Agradeço muito a tua ajuda, e vamos prosseguindo que agente chega lá, é muito importante para mim.


 

 

 Só relembrando , quando quando o GROUP BY era idNC os reg. de detalhes sumiram da view exceto a primeira nota que aliaz mostrou valor correto do rateio, mas os outros parecem ter tido valor de rateio correto mas não aparece na view do reusltado.

Link to post
Share on other sites
  • 0

Cheguei aos valores que voce mencionou

"Id","num_nc","data","val1","val2","val3","Id","nome_item","qde","preco_un","idNC","tot_coluna_item","tot_item","tot_custos","CUSTO_ITEM"
2,961,2020-04-01,0.09,0.57,0.00,1,"ITEM-X1",1,525.30,2,3123.35,525.30,0.66,0.111002

Me lembrei que quando voce usa a função SUM dentro de um JOIN , ele agrega os registros, mostrando apenas um registro na saida.

Para que mostre os demais registros, vai ter que ser diferente

SELECT * ,  
(qde * preco_un) AS tot_item,
 B.val1 + B.val2 + B.val3 AS  tot_custos              
FROM tboperacao AS A
JOIN  tbnota AS B ON (B.id = A.idNC)

Resultado

"Id","nome_item","qde","preco_un","idNC","Id","num_nc","data","val1","val2","val3","tot_item","tot_custos"
1,"ITEM-X1 ", 1,525.30,2,2,961,2020-04-01, 0.09,0.57,0.00,  525.30, 0.66
2,"ITEM-B2 ",15,103.87,2,2,961,2020-04-01, 0.09,0.57,0.00, 1558.05, 0.66
3,"ITEM-B2 ",10,104.00,2,2,961,2020-04-01, 0.09,0.57,0.00, 1040.00, 0.66
4,"ITEM-C3 ",41,452.00,1,1,  1,2020-05-17,10.00,1.00,3.00,18532.00,14.00
5,"ITEM-C3B",15, 98.80,1,1,  1,2020-05-17,10.00,1.00,3.00, 1482.00,14.00

A partir dai, voce monta um código para calcular o  tot_coluna_item e o custo_item

ou usa campo calculado no dbgrid

 

 

Link to post
Share on other sites
  • 0
Posted (edited)

 Entendo o que quer dizer , eu já tinha passado por este teste tambem, o negócio é que dessa forma eu nunca iria chegar ao custo de cada item em uma nota sem a soma da coluna tot_item   que da-se nome de     tot_coluna_item   (que é a soma do valor do campo criado tot_item onde foi multiplicado qde * preco_unitario).

Resumindo ,se desta ultima forma que apresentou tivesse uma coluna que mencionasse o valor desta soma repetidamente nos registros eu teria como calcular facilmente em uma dataset ou outrea tabela criada no sql. Uma outra duvida que veremos mais a frente (criar outra tabela com este resultado gerado).  Se conseguir-mos chegar a isso então eu iria conseguir o obejetivo final que é trabalhar filtro de   datas com esses dados apurados, e sem perder nenhum deles como por ex. o valor medio final que é uma outra coisa que terei que fazer somando o custo de cada item ao valor de qde * preco_un , sacou?  Isso me dará o produto final que é o valor bruto da nota.

   Em resumo para entendimento ,  cada item de operação tem um custo de peso dentro de uma nota e a soma de cada custo ao valor liquido que é qde X preco_un .

 Quando se tem esse campo de total em todos os registro pertinentes apenas uma nota é possível sem nenhum where ou group by calcular ate por calcfield os valores médios.

Mas vamos tentando , e mais , se alguém estiver passeando por este post poderia ate unir-se a nós,  lógico sem desprezar o grande impulso que tem me dado.

Edited by Lazaruz
complementacao
Link to post
Share on other sites
  • 0

Uma outra duvida que veremos mais a frente (criar outra tabela com este resultado gerado). 

DROP TABLE IF EXISTS tbresult;
CREATE TABLE tbresult 
SELECT A.id, num_nc, data, val1, val2, val3, 
nome_item, qde, preco_un, idNC, 
(qde * preco_un) AS tot_item,
B.val1 + B.val2 + B.val3 AS  tot_custos
FROM tboperacao AS A
JOIN  tbnota AS B ON (B.id = A.idNC);

ALTER TABLE tbresult ADD tot_coluna_item double(19,2) DEFAULT NULL; 
ALTER TABLE tbresult ADD custo_item double(19,2) DEFAULT NULL; 

RESULTADO:  ( TBRESULT )

"id"	"num_nc"	"data"	"val1"	"val2"	"val3"	"nome_item"	"qde"	"preco_un"	"idNC"	"tot_item"	"tot_custos"	"tot_coluna_item"	"custo_item"
1	961	     2020-04-01	0.09	0.57	0.00	  "ITEM-X1 "	 1	   525.30	      2	525.30	         0.66		
2	961	     2020-04-01	0.09	0.57	0.00	  "ITEM-B2 "	15	   103.87	      2	1558.05	         0.66		
3	961	     2020-04-01	0.09	0.57	0.00	  "ITEM-B2 "	10         104.00	      2	1040.00	         0.66		
4	1	     2020-05-17	10.00	1.00	3.00	  "ITEM-C3 "	41	   452.00	      1	18532.00        14.00		
5	1	     2020-05-17	10.00	1.00	3.00	  "ITEM-C3B"	15	    98.80	      1	1482.00	        14.00		

Obs: Isso vai criar uma tabela no seu banco de dados com todos os campos que voce precisa e com os resultados parciais.

os ultimos 2 campos, basta voce criar uma procedure para calcular e pronto.

Voce só não resolve agora se não quiser ( está mamão com açucar ..rs )

Link to post
Share on other sites
  • 0

Estou tentando remontar aqui tua  ideia brilhante (o mamão tá saindo da geladeira, e tenho que descascar primeiro)  que à muito tempo eu não implementei ,  não consegui realizar porque  o problema é eu entender a sintaxe e regras de funcionamento que não consegui. Vamos lá: 

Estou pegando um erro no segundo select e não diz o que mas diz que é lá no segundo porque o primeiro isoladamente funcionou.

1)não entendo da onde tirou o campo DATA. Sera alguma palavra reservada porque ele transforma a palavra data e maiuscula.

2)Tenho que  encadear esses dois blocos de selects seguidos, tudo como se fosse uma linha só ?

3)não entendo a  (;) apos 'tbresult' e não apos   ...A.idNC) anterior.

 

SELECT * ,  
(qde * preco_un) AS tot_item,
 B.val1 + B.val2 + B.val3 AS  tot_custos              
FROM tboperacao AS A
JOIN  tbnota AS B ON (B.id = A.idNC)

 

ROP TABLE IF EXISTS tbresult;
CREATE TABLE tbresult 
SELECT A.id, num_nc, data, val1, val2, val3, 
nome_item, qde, preco_un, idNC, 
(qde * preco_un) AS tot_item,
B.val1 + B.val2 + B.val3 AS  tot_custos
FROM tboperacao AS A
JOIN  tbnota AS B ON (B.id = A.idNC);

ALTER TABLE tbresult ADD tot_coluna_item double(19,2) DEFAULT NULL; 
ALTER TABLE tbresult ADD custo_item double(19,2) DEFAULT NULL; 

Aos poucos,  posso analisar com calma as possíveis incompatibilidades por que eu tenho que usar SQLite, não pode ser Mysql. Mas o DROP tem, o restante é que estou vendo onde agarra.

Link to post
Share on other sites
  • 0
Posted (edited)

Achei , é que eu tinha colocado nome _do_item por infelicedade minha e voce simplificou. Não tem prob. . Agora me deu seguinte resposta:

Executando Consulta. Aguarde...
Estatísticas da Consulta 'solucaoTBresult' (Tempo: 00:00:00, Linhas: 5, Colunas: 13)
Query Successfull. Returned 5 records.
Estatísticas da Consulta 'solucaoTBresult' (Tempo: 00:00:00, Linhas: 0, Colunas: 0)
Affected Rows: 0
Estatísticas da Consulta 'solucaoTBresult' (Tempo: 00:00:00, Linhas: 0, Colunas: 0)
Affected Rows: 0
Estatísticas da Consulta 'solucaoTBresult' (Tempo: 00:00:00, Linhas: 0, Colunas: 0)
Affected Rows: 0
Estatísticas da Consulta 'solucaoTBresult' (Tempo: 00:00:00, Linhas: 0, Colunas: 0)
Affected Rows: 0

 

Mas não trouxe os dados como o seu. Vou persistir nisso aqui ainda  enquanto isso.

Edited by Lazaruz
sintaxe
Link to post
Share on other sites
  • 0

Acho que voce por não ter experiencia, ficaria dificil para voce montar a procedure para calcular os demais campos

então segue o código completo

DROP TABLE IF EXISTS tbresult;
CREATE TABLE tbresult 
SELECT A.id, num_nc, data, val1, val2, val3, 
nome_item, qde, preco_un, idNC, 
(qde * preco_un) AS tot_item,
B.val1 + B.val2 + B.val3 AS  tot_custos
FROM tboperacao AS A
JOIN  tbnota AS B ON (B.id = A.idNC);

ALTER TABLE tbresult ADD tot_coluna_item double(19,2) DEFAULT NULL; 
ALTER TABLE tbresult ADD custo_item double(19,2) DEFAULT NULL; 
ALTER TABLE tbresult ADD val_med_fin double(19,2) DEFAULT NULL; 

DROP PROCEDURE IF EXISTS P;
CREATE PROCEDURE p(increment INT)
BEGIN
  DECLARE reg INT DEFAULT 0; 
  DECLARE cc INT DEFAULT 1; 
  DECLARE counter INT DEFAULT 1;
  
  SET @nreg = (SELECT MAX(ID) AS nreg FROM tbresult);
  SET reg = @nreg ;
  
  WHILE counter < reg DO
    -- ... do work ...
    
    SET @TOT := (SELECT SUM(QDE*PRECO_UN) FROM tbresult WHERE idnc = cc);
    
    SET @CUSTO := (SELECT (((val1 + val2 + val3) / ((SUM(qde * preco_un ))) * 100  
*  ((qde * preco_un) / 100))) FROM tbresult WHERE idnc = cc);

    UPDATE tbresult
    SET tot_coluna_item = @TOT, custo_item = @CUSTO
    WHERE idNC = cc;

    SET @VALMED = (SELECT SUM(QDE*PRECO_UN) + custo_item FROM tbresult WHERE idnc = cc);
    
    UPDATE tbresult
    SET val_med_fin = @VALMED
    WHERE idNC = cc;
   
    SET counter = counter + increment;
    SET cc = cc + 1;
  
  END WHILE;
END;


CALL P(1);

resultado

"id"	"num_nc"	"data"	"val1"	"val2"	"val3"	"nome_item"	"qde"	"preco_un"	"idNC"	"tot_item"	"tot_custos"	"tot_coluna_item"	"custo_item"	"val_med_fin"
1	961	2020-04-01	0.09	0.57	0.00	"ITEM-X1"	1	525.30	       2	525.30	            0.66	3123.35	                    0.11	3123.46
2	961	2020-04-01	0.09	0.57	0.00	" ITEM-B2 "	15	103.87	       2	1558.05             0.66	3123.35	                    0.11	3123.46
3	961	2020-04-01	0.09	0.57	0.00	" ITEM-B2 "	10	104.00	       2	1040.00	            0.66	3123.35	                    0.11	3123.46
4	1	2020-05-17	10.00	1.00	3.00	"ITEM-C3"	41	452.00	       1	18532.00           14.00	20014.00	           12.96	20026.96
5	1	2020-05-17	10.00	1.00	3.00	"ITEM-C3B"	15	98.80	       1	1482.00	           14.00	20014.00	           12.96	20026.96

agora o resto é com voce

abraço

Link to post
Share on other sites
  • 0

Ola, Jhonas, agradeço muito o modelo de procedure, realmente eu não conseguiria sozinho. No SQLite que não existe stored procedure, pelo que já procurei na web e site oficial.  Ainda estou estudando o modelo da tua procedure para que eu possa concluir uma adaptação para somente estudos no MySql ,até uma forma de implementar.Como eu tinha dito só poderei utilizar SQLite  no projeto.

  Por gentileza, favor deixar o post aberto para debates.

abr.

Link to post
Share on other sites
  • 0

Esse código foi gerado usando o  MYSQL_Front e esse código pode ser colocado dentro de um componente ZQuery ( Zeos ) com acesso a tabela gerada no MYSQL

-----------------------------------------------------------------

ok ...no seu caso, se voce conseguiu fazer essa parte...

DROP TABLE IF EXISTS tbresult;
CREATE TABLE tbresult 
SELECT A.id, num_nc, data, val1, val2, val3, 
nome_item, qde, preco_un, idNC, 
(qde * preco_un) AS tot_item,
B.val1 + B.val2 + B.val3 AS  tot_custos
FROM tboperacao AS A
JOIN  tbnota AS B ON (B.id = A.idNC);

ALTER TABLE tbresult ADD tot_coluna_item double(19,2) DEFAULT NULL; 
ALTER TABLE tbresult ADD custo_item double(19,2) DEFAULT NULL; 
ALTER TABLE tbresult ADD val_med_fin double(19,2) DEFAULT NULL; 

... não precisa necessariamente fazer a outra via SQL

voce pode construir uma procedure no Lazarus e fazer a mesma coisa usando os comandos do Lazarus/Delphi

Exemplo:

procedure Calcular_Valores(Sender: TObject);
Var i, reg, cc, counter : integer;
begin

   TbResult_IB.Active := false;
   CDS_TbResult.Active := false;
   TbResult_IB.Active := true;
   CDS_TbResult.Active := true;
   CDS_TbResult.Refresh;

   For i := 1 to CDS_TbResult.RecordCount do
     begin
       ///  outros comandos

     end;
end;

abraço

Link to post
Share on other sites
  • 0
Posted (edited)

Jhonas,  valeu novamente. Eu consegui sim executar esta ultimo SQL, tranquilamente no SQLite. Tenho agora nele custo total de cada nota em cada registro e totais no mesmo registro. Só faltará soma total dos valores de coluna (qde*preco_un), que como voce sugeriu farei via procedure Pascal Lazarus/Delphi. Só esclarecendo: tenho que interfacear esta TBRESULT criando uma novo DataSet (Zquery) que apesar de tabela ser temporaria a ZQuery vai entender? Colocarer este último select na query que já utilizo e criar outra query para a TBRESULT ser tratada via PROCEDURE  D.pascal.

 Obrigado.

Edited by Lazaruz
retirar MP
Link to post
Share on other sites
  • 0

tenho que interfacear esta TBRESULT criando uma novo DataSet (Zquery) que apesar de tabela ser temporaria a ZQuery vai entender?

Na verdade a tabela é temporaria até que voce execute novamente 

DROP TABLE IF EXISTS tbresult;
CREATE TABLE tbresult 

Trate a tabela como se fosse outra tabela do seu banco de dados. voce pode fazer com ela tudo o que precisar.

use normalmente os componentes de acesso à tabelas, não vai ter problema nenhum

abraço

Link to post
Share on other sites
  • 0

 Depois de alguns problemas para que o SQL sugerido rodasse redondo na ZQuery do Lazarus   carregando a tb  temporária,

consegui obter apresentação no grid desta tbResult porém todos os valores nos campos que são inerentes a tabela original física tb operações aparecem assim: (MEMO)

                                                          (MEMO)

                                                          (MEMO) 

                                                            etc. ate o final dos regs.

        Depois de pesquisar bastante e não achar nada tentei mudar o campo para os modos na propriedade FielddKind , BlobType etc..

        e nada. que será isto?  Inclusive quando eu abro o próprio gerenciador com o mesmo SELECT aparece assim também.  O único lugar que aparece os valores normais (que foram declarados originalmente como VARCHAR , FLOAT etc.. ) é na barra de STATUS  inferior de apresentação do resultado do SQL.   ESTRANHO ?!?! Obs. Ainda estou persistindo no SQLite, e estou quase chegando lá. Falta pouco , é só escrever a rotina de procedure de cálculos.

Link to post
Share on other sites
  • 0

O único lugar que aparece os valores normais (que foram declarados originalmente como VARCHAR , FLOAT etc.. ) é na barra de STATUS  inferior de apresentação do resultado do SQL.   ESTRANHO ?!?!

A tabela  tbResult quando é criada tem os  nomes dos campos e tipos relacionados   dessa maneira

id          int(11)
num_nc      int(11)
data        date
val1        double(11,2)
val2        double(11,2)
val3        double(11,2)
nome_item   varchar(50)
qde         int(5)
preco_un    double(11,2)
idNC        int(5)
tot_item    double(19,2)
tot_custos  double(19,2)
tot_coluna_item    double(19,2)
custo_item  double(19,2)
val_med_fin double(19,2)

voce não precisa mudar nada...  então veja o que voce está fazendo de errado

Dica: para que não ocorra erros,  carregue os campos dessa tabela ou na Zquery ou num ClientDataSet

No Select da Query voce coloca: Select * from tbresult ... não tem erro

abraço

 

Link to post
Share on other sites
  • 0
Posted (edited)

 Correto,  minha tbresult esta perfeita. O percebi é que ela é criada com campos  como tipo TEXT e não VARCHAR(x),  e ai que esta o estranho. Mas depois de testar de toda forma resolvi simplesmente seguir as orientações do site oficial SQLite, onde diz que  não  há  como deletar colunas ou alterar nomes ou tipagem nas colunas, só aceitando ALTER  TABLE tbresult ADD ....,   portanto até que alguém apresente melhor devemos  fazer tudo na mão  grande mesmo:

  Obs.: Não utilizo componente clienteDataSet ,  uso Dsource e Zquery .

     1- Criar tbresul_OLD baseada nos dados da tb operacao:

 

procedure TformLISTA.calculaMED(Sender: TObject);
 Var i, reg, cc, counter : integer;
begin
   with DTM do
  begin
          with ZQryResult do
           begin
          Active := false;

           sql.clear;
           sql.add('CREATE TABLE tbresult_OLD  AS '+
           'SELECT . . . . . . . . . . . .'+
           'FROM  tboperacao  AS  A '+
          'JOIN  tbnota AS B ON (B.id = A.idNC) ;');


           ExecSQL;

 

 {    2-Criar tbresult_NEW no modo normal de criacao declarando todos os campos com nome e tipagens originais:}

sql.clear;
 sql.add('CREATE TABLE tbresult_NEW ('+
         'id           INTEGER   PRIMAY KEY,   '+
         'idNC         INTEGER, '+
         'data  DATE,  '+

   {      Val1,Val2,Va3  nÃo precisamos mais porque já tenho até aqui a soma deles  TOT_CUSTO que servirá para o cálculo final.}


         'num_nc       VARCHAR(12),  '+
         'nome_item    VARCHAR(14),  '+
         'tot_item     FLOAT,  '+
         'tot_custos   FLOAT,  '+
         'tot_coluna_item FLOAT,  '+
         'custo_item     FLOAT,  '+
         'val_med_fin  FLOAT);  ');


           ExecSQL;

  { Seguindo com popular a tbresult_NEW.

    Deletar tbresult_OLD.

    Renomear tbresult_NEW para tbresult.}

. . . . . . . .end    // Query

              end;    //Dtm (datamodule)

           end;    //PROCEDURE

    Agora continuarei com os cálculos via PROCEDURE pascal como sugeriu e estruturou o laço para mim.

  Vou postando os resultados como ex para os demais interessados no post.

       Parece fácil , mas para mim não que  estou saindo de outro tipo de bases de dados e estou engatinhando no SQL .

 

Edited by Lazaruz
complemento.
Link to post
Share on other sites
  • 0
Posted (edited)

                     Com  enorme agradecimento ao companheiro Jhonas, que me acompanhou me me ajudou a estruturar a ideia, segue o resultado final, que consegui que fosse bem enxuto e operacional com uma procedure de calculos simples, e como sugerido sem tratar nada em stored procedures de banco.

     Após a criação das tabelas conforme posts anteriores:

 

procedure Tform.calculaMED(Sender: TObject);
begin
 With Dtm do
 begin


 ZQryResultAux.Open;       //Criei uma Query igual a TBresult para o  laço interno
 VTabNota.Open ;      // Trabalhar no laço externo para agregar as notas nos calulos.

//ANALISE: O laco interno é baseado na tabela auxiliar com os mesmos campos da tabela Result.


     //Tabela RESULT é gravada dado os parametros do laco executado na tabela AUXILIAR.
                   // O laco externo serve para fazer o update nos casos de somas ou calculos de registros agregados.

  WHILE  NOT  ZQryNota.EOF   do            //dece selecionando cada NOTA
  begin

       ZQryResultAux.First;
       with ZQryResultAux do
       begin;


      //Posta os valores direto na tab fisica de resultado utilizando sub selects

      WHILE not EOF do
       begin


              //SOMA  COLUNA TOT_ITEM E POSTA EM COLUNA TOTCOLUNA

              ZQryResult.sql.clear;
              ZQryResult.sql.add('UPDATE tbresult SET totcoluna = (SELECT SUM(tot_item)
               FROM tbresult WHERE idNC = :Param_numID)  WHERE idNC= :Param_numID ; ');
             ZQryResult.ParamByName('Param_numID').AsString=:ZQryNota.FieldByName('id').asstring;
                  ZQryResult.ExecSQL ;

                                     //CALCULA, POSTA CUSTO DE CADA ITEM
                ZQryResult.sql.clear;
                ZQryResult.sql.add('UPDATE tbresult SET custo_item = '+
                '(SELECT ((((tot_custos) / tot_opers_liq * 100) * '+
                '(tot_item / 100))) '+
             'FROM tbresult WHERE  id_result = :ParamIDresult) WHERE id_result  = :ParamIDresult; ');
 ZQryResult.ParamByName('ParamIDresult').AsString  := ZQryResultAux.FieldByName('id_result').asstring;
                ZQryResult.ExecSQL ;

               ZQryResultAux.Next ;
              end; //EOF tabela auxiliar

 

       end;    //Qry auxiliar

    VZQryNota.Next;

 
 end;//EOF notas
 end;//Dtm
end;//Proced
                           CALCULO DE CUSTO PROPORCIONAL: Fica ai para quem precisar. Tem várias utilidades.

Obrigado ao site. Suma importância para auxílio  ao desenvolvimento.

 

 

Edited by Lazaruz
faltou letra
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.



  • Forum Statistics

    • Total Topics
      148680
    • Total Posts
      644502
×
×
  • Create New...