1
Well, I’m currently working on a project that we use Clientdataset’s to communicate with the database. My problem is Next, I have a Receiving Report in the Export Sector, in which there are several records in Several Different Currencies. I need to Sum the Value of Receiving Installments, based on Your Currency, researched some methods of doing it with a repeat that passed a parameter and summed the value_a_receive based on a specific currency, but in this case I would need to specify each of the coins, are more than 150.
while not CDSGrid.Eof do
begin
if cdsgrid.fielbyname('ID_MOEDA').AsInteger = 155 Then
begin
ValorReceber := cdsgrid.fielbyname('valor_a_receber').AsExtended +
ValorReceber
CDSGrid.Next;
end;
I’m Using Mysql Server 8.0, I can distinguish coins by a field called ID_MOEDA containing only 1 specific id for each currency (Ex: Euro ID_MOEDA = 155);
Looking a little in the framework, I was able to identify what is done to bring the data from the report table (I will show here abbreviated, because some information is not relevant to the issue)
SELECT
CAST(CONCAT(`lr`.`ID`,`pr`.`ID`) AS UNSIGNED) AS `ID`,
CONCAT(`lr`.`NUMERO_DOCUMENTO`,'-',`pr`.`NUMERO_PARCELA`,'/',`lr`.`QUANTIDADE_PARCELA`) AS `DOCUMENTO`,
`lr`.`ID` AS `ID_LANCAMENTO_RECEBER`,
`lr`.`QUANTIDADE_PARCELA` AS `QUANTIDADE_PARCELA`,
`lr`.`VALOR_A_RECEBER` AS `VALOR_LANCAMENTO`,
`lr`.`NUMERO_DOCUMENTO` AS `NUMERO_DOCUMENTO`,
`lr`.`ID_PROCESSO_EXPORTACAO` AS `ID_PROCESSO_EXPORTACAO`,
`exportacao_processo`.`SITUACAO` AS `SITUACAO`,
`pr`.`VALOR_RECEBIDO` AS `VALOR_RECEBIDO`,
`pr`.`VALOR_RECEBER` AS `VALOR_RECEBER`,
`lr`.`ID_MOEDA` AS `ID_MOEDA`,
`moeda`.`NOME` AS `NOME_MOEDA`
FROM (((((((`fin_lancamento_receber` `lr`
JOIN `fin_parcela_receber` `pr`)
JOIN `pessoa` `p`)
JOIN `cliente` `c`)
JOIN `fin_status_parcela` `s`)
JOIN `conta_caixa` `cc`)
JOIN `moeda`
ON ((`lr`.`ID_MOEDA` = `moeda`.`ID`)))
LEFT JOIN `exportacao_processo`
ON ((`exportacao_processo`.`ID` = `lr`.`ID_PROCESSO_EXPORTACAO`)))
WHERE ((`pr`.`ID_FIN_LANCAMENTO_RECEBER` = `lr`.`ID`)
AND (`pr`.`ID_FIN_STATUS_PARCELA` = `s`.`ID`)
AND (`lr`.`ID_CLIENTE` = `c`.`ID`)
AND (`c`.`ID_PESSOA` = `p`.`ID`)
AND (`pr`.`ID_CONTA_CAIXA` = `cc`.`ID`))
My idea is Calculate the Total of "Received Value" in Dollar, Euro, Pound, etc.. A Dynamic Way that scans all records, identifies Coins, and makes a totalizer for each Currency.
Type :
|===========================================|
|id_moeda |nome_moeda|valor_total_a_receber |
|===========================================|
|155 | EURO |155.251,65 |
|84 |LIBRA |25.486,98 |
|===========================================|
Do you need to do this directly on the CDS? Because you could do by SQL this treatment...
– Matheus Ribeiro
I don’t have to necessarily do it for the CDS, I’ve tried, but I can’t think of other ways to do it even though I’m wondering about SQL, I have no idea where to start the code.
– Marcus Melgaço
Show us your SQL, edit your question and inform the SQL you are using to bring the data! And also tell how you do to distinguish between the types of coins.
– Matheus Ribeiro
Okay, I’ve put the required details
– Marcus Melgaço
Sorry, I expressed myself badly, the information you put is useful too, but I say to show us your Query (The SELECT you do to bring the database data) that you have tried so far. This way, it will also help to get your question released again.
– Matheus Ribeiro
I didn’t use a select, because behind the system is a whole framework that already does this, I presented the code that I used to add up the value of a specific currency, but that in the case would not work out for what I was needing, because I need something dynamic that does not need to be informed the currency, but rather identify which coins exist in the table and make a value totalizer (value_a_receive) for currency
– Marcus Melgaço
SELECT ID_MOEDA, SUM( VALOR_A_RECEBE), SUM( VALOR_TOTAL ) FROM TABLE GROUP BY ID_MOEDA
– Reginaldo Rigo
Dude that’s exactly it, I got the records added up correctly, now the part about integrating it into the system is up to me! Is there any way you can post as a response? or only when the post is released?
– Marcus Melgaço