Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.
Home » RADICORE development » Transaction Patterns » Output2 - SQL
Output2 - SQL [message #7334] Wed, 21 August 2019 17:11 Go to next message
htManager is currently offline  htManager
Messages: 438
Registered: May 2014
Senior Member
I have an output2 pattern and I want to list only records of the chosen value in field 'saison_id'. I searched the net to look for the right SQL solution but I failed. Can you say why my SQL code works with a 'hard' code (2018/2019) and not with the one (training_plan_kopf.saison_id) you see below?

Best regards,

Juergen

$table_id = 'training_plan_anwesenheit'; // table name
$report = 'training_plan_anwesenheit.gesamt.list.report.inc'; // file identifying report structure

// customise the SQL SELECT statement
$sql_select = "tplan_anwesenheit_kontakt_name, tplan_anwesenheit_kontakt_vorname, tplan_anwesenheit_status_grund, tplan_anwesenheit_saison_id, " .
"orga_vereine.vereine_name, orga_vereine.vereine_logo, orga_mannschaften_eigene.mannschaften_desc, " .

"SUM(CASE WHEN tplan_anwesenheit_status = 1 THEN tplan_anwesenheit_status ELSE 0 END) AS anzahl_anwesenheit, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND training_plan_kopf.training_datum > NOW() THEN 1 ELSE 0 END) AS anzahl_absagen_zukunft, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'URL' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_url, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'PRV' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_prv, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'BER' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_ber, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'VLZ' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_vlz, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'KRH' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_krh, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'SO' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_so, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'TAM' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_tam, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'SCH' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_sch, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'UNI' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_uni, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'NIK' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_nik, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'UE' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_ue";

$sql_from = 'training_plan_anwesenheit ' .
'LEFT JOIN orga_vereine ON (' .
'orga_vereine.verbaende_art_id = training_plan_anwesenheit.tplan_anwesenheit_verbaende_art_id AND ' .
'orga_vereine.verbaende_kuerzel = training_plan_anwesenheit.tplan_anwesenheit_verbaende_kuerze l AND ' .
'orga_vereine.vereine_kuerzel = training_plan_anwesenheit.tplan_anwesenheit_vereine_kuerzel) ' .

'LEFT JOIN orga_mannschaften_eigene ON (' .
'orga_mannschaften_eigene.verbaende_art_id = training_plan_anwesenheit.tplan_anwesenheit_verbaende_art_id AND ' .
'orga_mannschaften_eigene.verbaende_kuerzel = training_plan_anwesenheit.tplan_anwesenheit_verbaende_kuerze l AND ' .
'orga_mannschaften_eigene.vereine_kuerzel = training_plan_anwesenheit.tplan_anwesenheit_vereine_kuerzel AND ' .
'orga_mannschaften_eigene.saison_id = training_plan_anwesenheit.tplan_anwesenheit_saison_id AND ' .
'orga_mannschaften_eigene.mannschaften_id = training_plan_anwesenheit.tplan_anwesenheit_mannschaften_id) ' .

'LEFT JOIN training_plan_kopf ON (' .
'training_plan_kopf.verbaende_art_id = training_plan_anwesenheit.tplan_anwesenheit_verbaende_art_id AND ' .
'training_plan_kopf.verbaende_kuerzel = training_plan_anwesenheit.tplan_anwesenheit_verbaende_kuerze l AND ' .
'training_plan_kopf.vereine_kuerzel = training_plan_anwesenheit.tplan_anwesenheit_vereine_kuerzel AND ' .
'training_plan_kopf.saison_id = training_plan_anwesenheit.tplan_anwesenheit_saison_id AND ' .
'training_plan_kopf.mannschaften_id = training_plan_anwesenheit.tplan_anwesenheit_mannschaften_id AND '.
'training_plan_kopf.training_plan_kopf_no = training_plan_anwesenheit.tplan_anwesenheit_kopf_no)';

$sql_where = "training_plan_anwesenheit.tplan_anwesenheit_saison_id = training_plan_kopf.saison_id";
-> this code doesn't work.

$sql_where = "training_plan_anwesenheit.tplan_anwesenheit_saison_id = '2018/2019'";
-> this code works. Only records with value 2018/2019 are shown

$sql_groupby = 'tplan_anwesenheit_kontakt_name, tplan_anwesenheit_kontakt_vorname';
$sql_having = null;
$sql_orderby = null;

require 'std.output2.inc'; // activate page controller

Report message to a moderator

Re: Output2 - SQL [message #7335 is a reply to message #7334] Thu, 22 August 2019 04:07 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2370
Registered: April 2006
Location: Surrey, UK
Senior Member
Your query is basically 
SELECT ... FROM training_plan_anwesenheit LEFT JOIN training_plan_kopf ... 
WHERE training_plan_anwesenheit.tplan_anwesenheit_saison_id = 2018/2019'
If you replace the literal '2018/2019' with the column name training_plan_kopf.saison_id you are basically saying "use whatever value you find in column training_plan_kopf.saison_id", and as there is nothing in that query that limits that value to '2018/2019' it will use whatever values it finds.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Output2 - SQL [message #7337 is a reply to message #7335] Fri, 23 August 2019 05:24 Go to previous messageGo to next message
htManager is currently offline  htManager
Messages: 438
Registered: May 2014
Senior Member
But I generate the report from a list2 pattern. That's why I thought that the value 2018/2019 will be taken from the parent record where the value in this case is 2018/2019. Or do I have to consider something else?

Report message to a moderator

Re: Output2 - SQL [message #7338 is a reply to message #7337] Sat, 24 August 2019 04:27 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2370
Registered: April 2006
Location: Surrey, UK
Senior Member
What value from the LIST2 screen is being passed to the OUTPUT2 task? Unless you are passing the string
tplan_anwesenheit_saison_id = '2018/2019'
then the query will not limit itself to that selection.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Output2 - SQL [message #7339 is a reply to message #7338] Sun, 25 August 2019 13:30 Go to previous messageGo to next message
htManager is currently offline  htManager
Messages: 438
Registered: May 2014
Senior Member
Where can I see this best which value is being passed?

Report message to a moderator

Re: Output2 - SQL [message #7340 is a reply to message #7339] Mon, 26 August 2019 04:42 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2370
Registered: April 2006
Location: Surrey, UK
Senior Member
Your OUTPUT2 task is pointed to the training_plan_anwesenheit table, so I suggest you use your debugger and, when inside the _cm_initialise() method of that table's class, examine the contents of the $where and $selection variables.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Output2 - SQL [message #7341 is a reply to message #7340] Mon, 26 August 2019 09:36 Go to previous messageGo to next message
htManager is currently offline  htManager
Messages: 438
Registered: May 2014
Senior Member
If I set $sql_where = null; in the controller script, the content of $where in the _cm_initialise() method is exactly what I want, tplan_anwesenheit_saison_id has the value '2018/2019'. The value of $selection is ''.
But in the report are shown the records of '2018/2019' and of '2019/2020' .....

Report message to a moderator

Re: Output2 - SQL [message #7343 is a reply to message #7341] Tue, 27 August 2019 04:46 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2370
Registered: April 2006
Location: Surrey, UK
Senior Member
In that case you need to examine the contents of the $where variable in the _cm_pre_getData() method in case it has been changed. This is the method where the various parts of the sql query are defined.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Output2 - SQL [message #7344 is a reply to message #7343] Tue, 27 August 2019 09:13 Go to previous messageGo to next message
htManager is currently offline  htManager
Messages: 438
Registered: May 2014
Senior Member
In the _cm_pre_getData() method the $where is '', the $where_array is null and the $parent_data is false.

Report message to a moderator

Re: Output2 - SQL [message #7345 is a reply to message #7344] Wed, 28 August 2019 05:15 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2370
Registered: April 2006
Location: Surrey, UK
Senior Member
You will need to step through with your debugger to see what is happening between the call to _cm_initialise() and _cm_pre_getData().

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Output2 - SQL [message #7346 is a reply to message #7345] Wed, 28 August 2019 06:56 Go to previous messageGo to next message
htManager is currently offline  htManager
Messages: 438
Registered: May 2014
Senior Member
As far as I see is the $where set to '' in include.library.inc in method filterwhere(). The array3 contains the correct values but the array4 is null.

Report message to a moderator

Re: Output2 - SQL [message #7347 is a reply to message #7346] Thu, 29 August 2019 03:49 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2370
Registered: April 2006
Location: Surrey, UK
Senior Member
You need to investigate why the filterWhere() function is clearing out that data. It was designed to remove references to fields which do not exist in the structure of the current table.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Output2 - SQL [message #7350 is a reply to message #7347] Fri, 30 August 2019 10:00 Go to previous messageGo to next message
htManager is currently offline  htManager
Messages: 438
Registered: May 2014
Senior Member
If I understand you right, the clearing out happens because the fields in the $where string are different to those in the fieldlist. In the fieldlist are the fields of the $tablename table. In the $where string are the fields (with the right values) of the table from which the pattern is called. I hope I did understand you right. As you adviced in one of your articles I try to give unique field names in each table.
Do I have a wrong table name in one of my controller scripts?

Report message to a moderator

Re: Output2 - SQL [message #7351 is a reply to message #7350] Sat, 31 August 2019 04:41 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2370
Registered: April 2006
Location: Surrey, UK
Senior Member
When you have the OUTPUT2 (child) task as a navigation button on another (parent) task and you press that navigation button there are two values which are passed to the child task - the original WHERE string of the parent, and a SELECTION string which identifies any rows which you checked using the select box on the parent's screen. The field names used in both strings are the names as known to the parent. If the corresponding names in the child task are different then you have to adjust them manually as described in http://www.tonymarston.net/php-mysql/menuguide/appendixi.htm l

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Output2 - SQL [message #7354 is a reply to message #7351] Tue, 03 September 2019 06:15 Go to previous messageGo to next message
htManager is currently offline  htManager
Messages: 438
Registered: May 2014
Senior Member
Exactly what I was looking for. Thank you very much. Replacing the $where string with the right primary key fields led to the right solution. Everything seems to work fine now.

Two more questions:
1. As you can see I want to generate a report with a 'Cross' or 'Pivot' table. Do you have an example where you did deal with this? I have now hard coded the columns but each time I insert a new column I have to change my code.

2. Is there a possibility to output the 1 in a boolean field as a check mark?

Report message to a moderator

Re: Output2 - SQL [message #7355 is a reply to message #7354] Wed, 04 September 2019 04:41 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2370
Registered: April 2006
Location: Surrey, UK
Senior Member
If by a 'Cross' or 'Pivot' table you mean an intersection table which handles a Many-to-Many relationship, then take a look at https://www.tonymarston.net/php-mysql/dialog-types.html#link 1. There are several examples of this pattern in the MENU subsystem.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

[Updated on: Wed, 04 September 2019 04:42]

Report message to a moderator

Re: Output2 - SQL [message #7356 is a reply to message #7355] Wed, 04 September 2019 06:25 Go to previous messageGo to next message
htManager is currently offline  htManager
Messages: 438
Registered: May 2014
Senior Member
No, that's not what I am looking for. I want to sum how many times a person was out (wasn't available), grouped by the reason. But the reasons are listed in a different table and the number can change. For your understanding what I mean:

Person - reason 1 - reason 2 - reason 3 - ....
1 - 3 - 0 - 2 - ....
2 - 1 - 2 - 0 - ....
3 - 5 - 3 - 1 - ....

I hope I could explain what I mean.

Report message to a moderator

Re: Output2 - SQL [message #7357 is a reply to message #7356] Thu, 05 September 2019 04:14 Go to previous message
AJM is currently offline  AJM
Messages: 2370
Registered: April 2006
Location: Surrey, UK
Senior Member
I do not have a particular pattern for that. You need to identify the particular SQL query which does that, then modify one of your classes to issue that query.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Previous Topic: add5 - Data Area
Next Topic: Output2 - Options / 90° cell rotation
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ] [ PDF ]

Current Time: Tue Nov 26 14:03:49 EST 2024

Total time taken to generate the page: 0.01495 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.1.3.
Copyright ©2001-2023 FUDforum Bulletin Board Software