I have a display object that needs to show the percentage of project's progress.
Given the multiple tables involved, I opted for a query setting temporary values and then a select that gives me the result as below:
SET @variable1 = (select SUM(totale) FROM
(
select (preventivo.pre_subtotale) AS totale from
preventivo
LEFT JOIN progetto ON progetto.progetto_id = preventivo.pre_progetto
LEFT JOIN cdc ON cdc.cdc_id = preventivo.pre_cdc
LEFT JOIN fasi_lav ON fasi_lav.fasi_lav_id = preventivo.pre_fase_lav
LEFT JOIN materiale ON materiale.materiale_id = preventivo.pre_materiale
left join consuntivo ON preventivo.preventivo_id = consuntivo.con_preventivo
left join fattura ON consuntivo.con_fattura = fattura.fattura_id
left join preventivo_n ON consuntivo.con_preventivo = preventivo_n.preventivo_n_id
group by preventivo.preventivo_id
)a);
SET @variable2 = (select SUM(totale1) FROM
(
select (preventivo.con_somma_subtotale) AS totale1 from
preventivo
LEFT JOIN progetto ON progetto.progetto_id = preventivo.pre_progetto
LEFT JOIN cdc ON cdc.cdc_id = preventivo.pre_cdc
LEFT JOIN fasi_lav ON fasi_lav.fasi_lav_id = preventivo.pre_fase_lav
LEFT JOIN materiale ON materiale.materiale_id = preventivo.pre_materiale
LEFT JOIN consuntivo ON preventivo.preventivo_id = consuntivo.con_preventivo
group by preventivo.preventivo_id
)b);
SELECT CONCAT(COALESCE(FORMAT (@variable2 / @variable1 *100, 2), "0"),' %') AS percento;
but, although it works perfectly in PHPMyAdmin, the display object is empty.
PDO does not support executing multiple queries/statements in a single `PDO::query()` call by default. This limitation is why you see no output when attempting to run multiple queries at once. Each query needs to be executed separately.
To address this, we've introduced a new option: **Run a Procedure**. By updating to the latest of nuBuilder, you will gain access to this feature.
display_procedure.png
Here is an example of how the Procedure would look (its output will be displayed in the Display object):
global $nuDB;
// Set variable1
$stmt = $nuDB->prepare("
SET @variable1 = (
SELECT SUM(totale) FROM (
SELECT (preventivo.pre_subtotale) AS totale FROM preventivo
LEFT JOIN progetto ON progetto.progetto_id = preventivo.pre_progetto
LEFT JOIN cdc ON cdc.cdc_id = preventivo.pre_cdc
LEFT JOIN fasi_lav ON fasi_lav.fasi_lav_id = preventivo.pre_fase_lav
LEFT JOIN materiale ON materiale.materiale_id = preventivo.pre_materiale
LEFT JOIN consuntivo ON preventivo.preventivo_id = consuntivo.con_preventivo
LEFT JOIN fattura ON consuntivo.con_fattura = fattura.fattura_id
LEFT JOIN preventivo_n ON consuntivo.con_preventivo = preventivo_n.preventivo_n_id
GROUP BY preventivo.preventivo_id
) a
)
");
$stmt->execute();
// Set variable2
$stmt = $nuDB->prepare("
SET @variable2 = (
SELECT SUM(totale1) FROM (
SELECT (preventivo.con_somma_subtotale) AS totale1 FROM preventivo
LEFT JOIN progetto ON progetto.progetto_id = preventivo.pre_progetto
LEFT JOIN cdc ON cdc.cdc_id = preventivo.pre_cdc
LEFT JOIN fasi_lav ON fasi_lav.fasi_lav_id = preventivo.pre_fase_lav
LEFT JOIN materiale ON materiale.materiale_id = preventivo.pre_materiale
LEFT JOIN consuntivo ON preventivo.preventivo_id = consuntivo.con_preventivo
GROUP BY preventivo.preventivo_id
) b
)
");
$stmt->execute();
// Select query
$stmt = $nuDB->prepare("
SELECT CONCAT(COALESCE(FORMAT(@variable2 / @variable1 * 100, 2), '0'), ' %') AS percento
");
$stmt->execute();
// Fetch the result
$result = $stmt->fetch(PDO::FETCH_NUM);
// Output the result
echo $result[0];
You do not have the required permissions to view the files attached to this post.
sorry for the late reply but I was travelling.
I had seen from the phone that at first you had given a solution but now that I came back and wanted to implement it, I saw that you modified the master code. Sorry... I didn't mean to give you more work!!
A small problem: I loaded the new version of NUBuilder (4.5.8.6) but in my browser (Chrome) the data fields in the input section are overlapped (see attachment) and I don't know if it's a problem with my browser (i also cancelled all cache) or a layout oversight and therefore I wasn't able to experience what you did also because the field for entering the procedure does not appear in the display mask
Anyway, thanks for all your support.
Costa
You do not have the required permissions to view the files attached to this post.
Last edited by Costa on Fri Jun 07, 2024 12:22 pm, edited 1 time in total.
Yes, i have done the update (using the database update procedure) and also delete all file/directory and copied the new release but the info are the followings:
just for information, I completely deleted the db and recreated it by uploading the backup I had made shortly before the update. I deleted all the files and directories and proceeded to update again but nothing changes. Info are still
Sorry to bother you but but Is there the possibility of refreshing the display object (working with procedure) following the application of filters in the sql?
The procedure
The nuRefreshDisplayObject() function has not yet been modified to update when a procedure is used instead of SQL. This still needs to be done (if that’s what you mean).
