Family Pledge by Fiscal Year report

[rottinger]

If you have the ChurchCRM software running, please file an issue using the Report an issue in the help menu.

Sorry, but the link for "Report an Issue" only links back to the page you're on (linked to "#").

On what page in the application did you find this issue?

Under "Data Reports", "Query Menu", "Family Pledge by Fiscal Year"

On what type of server is this running? Dedicated / Shared hosting? Linux / Windows?

Server Platform: Linux us-phx-web740.main-hosting.eu 4.18.0-348.lve.el8.x86_64 #1 SMP Sun Nov 21 10:56:33 EST 2021 x86_64
Shared Hosting

What browser (and version) are you running?

Microsoft Edge, Version 109.0.1518.70 (Official build) (64-bit)

What version of PHP is the server running?

v. 7.4.33

What version of SQL Server are you running?

5.5.5-10.5.15-MariaDB-cll-lve

What version of ChurchCRM are you running?

v. 4.5.3

Details

I have what seems to be a bug in the reports (specifically, regarding the "Family Pledge by Fiscal Year" report). I will tell you that I've dealt with php and web pages for years

When I set up a deposit, the "Fiscal Year" dropdown in the "Payment Details" page allows you to choose from a date range between 1997 to 2024. In looking at the values assigned for each year, it assigns an option value from 1 to 28. So:

Select Fiscal Year 1997 1998 1999

and so on. For 2023, the value assigned is "27".

The original SQL setup has the following in the Install.sql (in queryparameteroptions_gpo):

(10, 27, '2012/2013', '17'),
(11, 27, '2013/2014', '18'),
(12, 27, '2014/2015', '19'),
(13, 27, '2015/2016', '20'),

I've modified it so that it will register from 2020 to 2023, as our fiscal year follows the calendar year, as follows:

(10, 27, '2020', '17'),
(11, 27, '2021', '18'),
(12, 27, '2022', '19'),
(13, 27, '2023', '20'),

When I run the report, I never get any information back (I currently have five deposits so far for the year, with several "Pledges" registered, and several people having pledges registered for 2023 in the family account. The SQL query for the report, when selecting FY 2023, is:

SELECT fam_Name, fam_Envelope, b.fun_Name as Fund_Name, a.plg_amount as Pledge from family_fam left join pledge_plg a on a.plg_famID = fam_ID and a.plg_FYID=20 and a.plg_PledgeOrPayment='Pledge' and a.plg_amount>0 join donationfund_fun b on b.fun_ID = a.plg_fundID order by fam_Name, a.plg_fundID

The query is looking for "a.plg_FYID=20". In looking at queryparameteroptions_gpo, it doesn't appear to be referring to gpo_grp_ID, and if it's looking at gpo_Value, it's not finding it.

If there's something I'm missing (I need to make changes elsewhere, or I've completely gone off the rails), please let me know.

Thank you.

[rottinger]

** Update **

First of all, I am not a coder. I have spent enough years looking at PHP and SQL to be able to sort through (mostly) what data is being looked for and where it's supposed to find it. With that caveat …

I went back and looked at the pledge_plg table, and compared it against the query in the query_qry table.

The query_qry table has the following SQL, in qry_SQL, regarding the "Family Pledge by Fiscal Year" report:

SELECT fam_Name, fam_Envelope, b.fun_Name as Fund_Name, a.plg_amount as Pledge from family_fam left join pledge_plg a on a.plg_famID = fam_ID and a.plg_FYID=fyid and a.plg_PledgeOrPayment='Pledge' and a.plg_amount>0 join donationfund_fun b on b.fun_ID = a.plg_fundID order by fam_Name, a.plg_fundID

The a.plg_FYID=fyid is pulling information from the pledge_plg table, in the plg_FYID field. If I set the "Fiscal Year" in the deposit slip to "2023", it should pull "27" from the gpo_grp_ID field, which it does. The a.plg_PledgeOrPayment field is looking for a value of "Pledge".

I went in and looked at my table information in "pledge_plg". In the deposits, I set the "Fiscal Year" for 2023, which had an option/ID value of "27". Fine so far. However, every entry in the "plg_PledgeOrPayment" field is listed as "Payment", even if the money was credited in the "Fund Split" section under "Pledges".

In looking at DepositSlipEditor.php, there doesn't appear to be a point where a deposit can be credited as "Pledge" versus "Payment", so I'm not sure what's missing. But that would explain why I don't get anything back in the report.

I may have this all wrong, and if so, and I need to make other changes, I'd appreciate getting pointed in the right direction.

[rottinger]

Well, maybe I'm not so smart after all. I manually changed the information in the "pledge_plg" table to reflect "Pledge" instead of "Payment". No joy on the report.

[github-actions]

This issue is stale because it has been open 30 days with no activity. Remove stale label or comment or this will be closed in 5 days.

[github-actions]

This issue is stale because it has been open 30 days with no activity. Remove stale label or comment or this will be closed in 5 days.

[github-actions]

This issue was closed because it has been stalled for 15 days with no activity.