Php procedure for a report table Topic is solved

[capstefano]

Hi there, I'm building a query to retrieve aggregate values on a row, calculated on the value of two objects in the form ('#data_inizio#' and '#data_fine#'). the query alone runs fine in phpmyadmin (using arbitrary data instead of hash cookies) so I've tried to build a php procedure to be used as the table for a report. I can't understand what I'm doing wrong, but the procedure doesn't appear in the lookup when I try to build a report. Is miscoding or am I missing anything fundamental?
the procedure code is:

Code: Select all

$s  = "
  -- selezione per valori di performance dello studio nel periodo di tempo

SELECT

	-- importo netto preventivato nel periodo
		(SELECT SUM(listino.listino_prezzo)

			FROM
    		listino
        		JOIN terapia ON listino.listino_id = terapia.terapia_listino_id
        		JOIN preventivo ON terapia.terapia_preventivo_id = preventivo.preventivo_id
			WHERE
    		((preventivo.preventivo_data BETWEEN '#data_inizio#' AND '#data_fine#') AND 
    			(terapia.terapia_non_eseguito ='0'))
 							) AS 
 								'Importo preventivi',
		
	-- importo terapie eseguite nel periodo
		(SELECT SUM(listino.listino_prezzo)
			FROM
   		 listino
     		   JOIN terapia ON listino.listino_id = terapia.terapia_listino_id
    	    JOIN diario ON terapia.terapia_diario_id = diario.diario_id
			WHERE
 		   ((diario.diario_data BETWEEN '#data_inizio#' AND '#data_fine#') AND 
 			   (terapia.terapia_non_eseguito ='0'))
					) AS 
							'Importo terapie eseguite',
			
			
			
	-- importo fatturato
		(SELECT SUM(listino.listino_prezzo)
			FROM
    		listino
        	JOIN terapia ON listino.listino_id = terapia.terapia_listino_id
       		 LEFT JOIN fattura ON terapia.terapia_fattura_id = fattura.fattura_id
			WHERE
  			  ((fattura.fattura_data BETWEEN '#data_inizio#' AND '#data_fine#')))
  			  	AS 
							'Importo fatturato',
		
		
		
	-- importo incassato
		(SELECT SUM(listino.listino_prezzo)

FROM
    listino
        JOIN terapia ON listino.listino_id = terapia.terapia_listino_id
        LEFT JOIN fattura ON terapia.terapia_fattura_id = fattura.fattura_id

WHERE
    ((fattura.fattura_data BETWEEN '#data_inizio#' AND '#data_fine#') AND 
    (fattura.fattura_data_pagamento IS NOT NULL)))

		AS 'Importo incassato';

  ";
  
nuRunQuery("CREATE TABLE #TABLE_ID# $s");

thank you in advance for your help

EDIT: I thought it could be the query, designed in a different way but had no positive outcome

Code: Select all

$s  = "
  -- selezione per valori di performance dello studio nel periodo di tempo

-- Dichiarazione delle variabili per i criteri di selezione
SET @data_inizio = '#data_inizio#';
SET @data_fine = '#data_fine#';

-- Dichiarazione delle variabili per ciascuna subquery
SET @importo_preventivi = (
    SELECT SUM(listino.listino_prezzo)
    FROM listino
    JOIN terapia ON listino.listino_id = terapia.terapia_listino_id
    JOIN preventivo ON terapia.terapia_preventivo_id = preventivo.preventivo_id
    WHERE ((preventivo.preventivo_data BETWEEN @data_inizio AND @data_fine) 
           AND (terapia.terapia_non_eseguito = '0'))
);

SET @importo_terapie_eseguite = (
    SELECT SUM(listino.listino_prezzo)
    FROM listino
    JOIN terapia ON listino.listino_id = terapia.terapia_listino_id
    JOIN diario ON terapia.terapia_diario_id = diario.diario_id
    WHERE ((diario.diario_data BETWEEN @data_inizio AND @data_fine) 
           AND (terapia.terapia_non_eseguito = '0'))
);

SET @importo_fatturato = (
    SELECT SUM(listino.listino_prezzo)
    FROM listino
    JOIN terapia ON listino.listino_id = terapia.terapia_listino_id
    LEFT JOIN fattura ON terapia.terapia_fattura_id = fattura.fattura_id
    WHERE (fattura.fattura_data BETWEEN @data_inizio AND @data_fine)
);

SET @importo_incassato = (
    SELECT SUM(listino.listino_prezzo)
    FROM listino
    JOIN terapia ON listino.listino_id = terapia.terapia_listino_id
    LEFT JOIN fattura ON terapia.terapia_fattura_id = fattura.fattura_id
    WHERE ((fattura.fattura_data BETWEEN @data_inizio AND @data_fine) 
           AND (fattura.fattura_data_pagamento IS NOT NULL))
);

-- Utilizzo delle variabili nella SELECT finale
SELECT 
    @importo_preventivi AS 'Importo preventivi',
    @importo_terapie_eseguite AS 'Importo terapie eseguite',
    @importo_fatturato AS 'Importo fatturato',
    @importo_incassato AS 'Importo incassato';
  ";
  
nuRunQuery("CREATE TABLE #TABLE_ID# $s");

[steven]

Hi capstefano,

I created a Procedure from your first Procedure and I was able to see it in my list of tables...

procedure.png

lookup.png

Can you create a Procedure that appears if you just do something like this?

Code: Select all

nuRunQuery("CREATE TABLE #TABLE_ID# SELECT * FROM listino");

Steven

[capstefano]

Thanks, Steven, for testing it.
I've tried to create the simple procedure you proposed, with no success. I'm happy on a side, because it means that I've understood, more or less, how that procedure thing works. On the other side I'm wondering what's happened: tried to update my applicarion, but the process got stuck and left several tables with altered name (like sys_zzzsys...). After correcting the table names the framework works (apparently) correctly, but I suspect that my issue could be related to some malfunctions (probably I've messed up something, but no idea of what)
What's going on, in your opinion? How could I fix this?

[steven]

capstefano ,

Sorry, I couldn't say what your problem is but if you want to zip a copy up and upload it, I can take a look.


Steven

[capstefano]

You're really kind Steven, I've attached a zipped backup of my database, would be great if you can check it

capstefano_gestalba.sql.zip

[capstefano]

Maybe I've found the issue... table zzsys_report_data was missing. After recreating it I'm able to see the procedure as a valid table

said that, no fields appear after selecting the procedure in the report creator form, but I guess I will have to retrieve them after creating objects in the report builder (calling them with their aliases, I suppose)

I'm sorry for wasting your time, Steven

BTW, the update process can't be accomplished, and when stucks there's no report_data table in zzzsys nor sys_zzzsys tables

[steven]

capstefano,

Like you said.

The database you attached is missing at least one table.

loo.png

This is what is happening when you try to open this Lookup...

miss.png

Steven

[capstefano]

thanks for checking it out Steven.
the weird thing is that I can't figure out when the table was missed.
I've re-created the table using a piece of the nubuilder4.sql file in the server folder, was it appropriate?
Still have the updating issue, but I'll wait and try the next release before going drastic (like copy all relevant records from here and start from scratch with a new, already-updated framework).
again, thanks a lot