Annual reports error message

[xaviermestdagh]

hi @JimBacon

trying to export annual reports from here https://butterfly-monitoring.net/annual-report?locationID=scheme:81&dataSet=scheme:81&year=2022

I have the following error message

error | "There was an SQL error: ERROR: operator does not exist: integer = record\nLINE 52: ...6646,216645,267526)' = '-1' OR parent.location_id IN ((21663...\n ^\nHINT: No operator matches the given name and argument types. You might need to add explicit type casts. - \n select \n parent.id as sample_id,\n ctt.term as site_type,\n l.code as site_code,\n l.name as site_name,\n l.centroid_sref as centroid_sref,\n '' as date, --- allows proper positioning of date column in report\n parent.date_start,\n parent.date_end,\n parent.date_type,\n parent.comment,\n parent.entered_sref,\n ( select AVG(savs.int_value)\n from samples ss\n JOIN sample_attribute_values savs ON savs.sample_id = ss.id AND savs.deleted = FALSE \n JOIN sample_attributes sas ON savs.sample_attribute_id = sas.id AND sas.deleted = FALSE AND sas.caption = '% Sun'\n WHERE parent.id = ss.parent_id AND ss.deleted = FALSE) as mean_sun\n ,\nsample1384.id as attr_id_sample_1384,\nsample1384.text_value as attr_sample_1384,\nsample1385.id as attr_id_sample_1385,\nsample1385.text_value as attr_sample_1385,\nsample1386.id as attr_id_sample_1386,\nsample1386.text_value as attr_sample_1386,\nsample1660.id as attr_id_sample_1660,\nsample1660.int_value as attr_sample_1660,\nltt1660.term as attr_sample_term_1660,\nsample1389.id as attr_id_sample_1389,\nsample1389.int_value as attr_sample_1389,\nltt1389.term as attr_sample_term_1389,\nsample1390.id as attr_id_sample_1390,\nsample1390.int_value as attr_sample_1390,\nltt1390.term as attr_sample_term_1390\n from samples parent\n JOIN locations l ON parent.location_id=l.id\n JOIN cache_termlists_terms ctt ON ctt.id=l.location_type_id\n JOIN surveys su ON parent.survey_id = su.id\n LEFT JOIN sample_attribute_values sample1384 ON sample1384.sample_id=parent.id AND sample1384.sample_attribute_id=1384 AND sample1384.deleted=false\n LEFT JOIN sample_attribute_values sample1385 ON sample1385.sample_id=parent.id AND sample1385.sample_attribute_id=1385 AND sample1385.deleted=false\n LEFT JOIN sample_attribute_values sample1386 ON sample1386.sample_id=parent.id AND sample1386.sample_attribute_id=1386 AND sample1386.deleted=false\n LEFT JOIN sample_attribute_values sample1660 ON sample1660.sample_id=parent.id AND sample1660.sample_attribute_id=1660 AND sample1660.deleted=false\nLEFT JOIN cache_termlists_terms ltt1660 ON ltt1660.id=sample1660.int_value\n LEFT JOIN sample_attribute_values sample1389 ON sample1389.sample_id=parent.id AND sample1389.sample_attribute_id=1389 AND sample1389.deleted=false\nLEFT JOIN cache_termlists_terms ltt1389 ON ltt1389.id=sample1389.int_value\n LEFT JOIN sample_attribute_values sample1390 ON sample1390.sample_id=parent.id AND sample1390.sample_attribute_id=1390 AND sample1390.deleted=false\nLEFT JOIN cache_termlists_terms ltt1390 ON ltt1390.id=sample1390.int_value\n \n WHERE parent.deleted = false\n AND parent.parent_id is null\n AND (su.website_id in (118) or su.website_id is null)\n AND ('(216635,261077,216684,216683,274816,216665,261024,218817,258523,216688,216626,227494,216637,227522,216680,265669,265693,216708,219055,258540,216633,216618,216701,273787,216663,261088,216628,271684,216687,216674,261052,216625,261041,216662,261099,216623,216673,219124,216682,216710,265093,265117,216681,274584,216676,260520,318088,318103,216649,216706,273665,323396,305592,216672,260536,322160,322181,216659,260425,216661,261064,216667,260405,216664,260636,216624,260465,216666,260603,216675,260448,216627,274556,216620,260483,216660,216709,274571,274596,274663,274644,274620,216621,216669,216653,216657,216697,216696,216639,216652,216611,216694,216700,216699,328865,302325,302338,301168,216644,216617,216703,216678,274507,216693,272061,272177,216725,216716,216724,216723,216722,216721,216720,216719,216718,216717,216691,216654,272040,272158,273066,273084,246946,216616,216679,221024,271753,272078,216640,216632,216651,216705,216655,216690,314478,216671,265650,216643,216650,216634,216619,271702,216631,216647,216695,216658,268710,216677,216715,216713,274472,274490,272001,275428,274517,216698,216692,216610,216642,216656,216615,216702,216668,216685,216689,216670,216641,216686,216707,218086,302357,328890,302377,328904,216648,227476,216636,216622,216646,216645,267526)' = '-1' OR parent.location_id IN ((216635,261077,216684,216683,274816,216665,261024,218817,258523,216688,216626,227494,216637,227522,216680,265669,265693,216708,219055,258540,216633,216618,216701,273787,216663,261088,216628,271684,216687,216674,261052,216625,261041,216662,261099,216623,216673,219124,216682,216710,265093,265117,216681,274584,216676,260520,318088,318103,216649,216706,273665,323396,305592,216672,260536,322160,322181,216659,260425,216661,261064,216667,260405,216664,260636,216624,260465,216666,260603,216675,260448,216627,274556,216620,260483,216660,216709,274571,274596,274663,274644,274620,216621,216669,216653,216657,216697,216696,216639,216652,216611,216694,216700,216699,328865,302325,302338,301168,216644,216617,216703,216678,274507,216693,272061,272177,216725,216716,216724,216723,216722,216721,216720,216719,216718,216717,216691,216654,272040,272158,273066,273084,246946,216616,216679,221024,271753,272078,216640,216632,216651,216705,216655,216690,314478,216671,265650,216643,216650,216634,216619,271702,216631,216647,216695,216658,268710,216677,216715,216713,274472,274490,272001,275428,274517,216698,216692,216610,216642,216656,216615,216702,216668,216685,216689,216670,216641,216686,216707,218086,302357,328890,302377,328904,216648,227476,216636,216622,216646,216645,267526)))\nAND parent.date_end >= CAST(COALESCE('2022-01-01','1500-01-01') as date)\nAND parent.date_start <= CAST(COALESCE('2022-12-31','1500-01-01') as date)\nAND parent.survey_id=562\nAND l.location_type_id=777\nAND parent.created_by_id=227052\n\n ORDER BY l.code, parent.date_start\n " -- | -- code | 44 file | "/srv/sites/warehouse1.indicia.org.uk/system/libraries/drivers/Database/Pgsql.php" line | 368 trace | []

[DavidRoy]

@andrewvanbreda @Gary-van-Breda can you pick this up. A post-migration bug?

[Irmawyn]

I tried to enter this page, too, using this link: https://butterfly-monitoring.net/annual-report

But the reply is that access is denied because I am not authorized.

Bests, Irma

---

Dit bericht is verzonden door / This message was sent by

Dr. Irma Wynhoff

De Vlinderstichting / Dutch Butterfly Conservation

Butterfly Conservation Europe

Postbus 506 / P.O. Box 506

NL - 6700 AM Wageningen

Mennonietenweg 10, Wageningen

[T] (+31) 0317 467346 / direct (+31) 0317 467320 / mob (+31) 06 44 67 46 49

[E}: @.**@.>,

[I]: vlinderstichting.nlhttp://www.vlinderstichting.nl/ bc-europe.ehttp://www.bc-europe.org/u facebook.com/bluesinthemarshes/http://www.facebook.com/bluesinthemarshes/

---

Aan de inhoud van dit e-mailbericht kunnen geen rechten worden ontleend. De informatie is uitsluitend bestemd voor de geadresseerde. De Vlinderstichting staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht noch voor tijdige aankomst daarvan.

No rights may be claimed from the contents of this e-mail. The information is only intended for the addressee. Dutch Butterfly Conservation cannot be held responsible for the correctness and completeness of the contents of an e-mail, nor for its arrival on time.

---

Van: David Roy (CEH) @.> Verzonden: donderdag 23 november 2023 11:13 Aan: BiologicalRecordsCentre/ABLE @.> CC: Subscribed @.***> Onderwerp: Re: [BiologicalRecordsCentre/ABLE] Annual reports error message (Issue #643)

@andrewvanbredahttps://github.com/andrewvanbreda @Gary-van-Bredahttps://github.com/Gary-van-Breda can you pick this up. A post-migration bug?

— Reply to this email directly, view it on GitHubhttps://github.com/BiologicalRecordsCentre/ABLE/issues/643#issuecomment-1824122581, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AK6ABCW4M67HPAHDZ3PGNXLYF4OWDAVCNFSM6AAAAAA7XQIJFCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMRUGEZDENJYGE. You are receiving this because you are subscribed to this thread.Message ID: @.***>

BELANGRIJK: Deze e-mail is afkomstig van buiten de organisatie. Klik niet op links of open geen bijlagen tenzij u de afzender herkent en zeker weet dat de inhoud veilig is.

[andrewvanbreda]

@DavidRoy OK I will look now

[andrewvanbreda]

H @Irmawyn I am afraid I cannot reproduce the accessed denied message. The website would return that message if you hadn't logged in to the website on the browser before clicking on the link. If you do not think that is the problem, could you confirm what username you use on the websitie site so I can investigate. Thanks Andy

[CrisSevilleja]

Hello @andrewvanbreda I also saw the annual Report page is not working properly. I cannot see the real visits of any transect or complete scheme. I always get the same numbers, I put you the screenshot for the Spanish data, but I got the same when I selected other schemes:

When I try to download that data, a new window opens showing this error message: {"error":"There was an SQL error: ERROR: operator does not exist: integer = record\nLINE 68: ...3835,223579,223333)' = '-1' OR parent.location_id IN ((22384...\n ^\nHINT: No operator matches the given name and argument types. You might need to add explicit type casts. - \n select \n su.title as survey,\n parent.id as sample_id,\n o.id as occurrence_id,\n ctt.term as site_type,\n l.code as site_code,\n '' as date, --- allows proper positioning of date column in report\n parent.date_start,\n parent.date_end,\n parent.date_type,\n l2.code as section_number,\n coalesce(l.code,l.name)||'.'||substring(l2.code from 2) as section_code,\n coalesce(cttl.default_common_name, cttl.preferred_taxon) as common,\n cttl.preferred_taxon as preferred\n ,\nsample1384.id as attr_id_sample_1384,\nsample1384.text_value as attr_sample_1384,\nsample1385.id as attr_id_sample_1385,\nsample1385.text_value as attr_sample_1385,\nsample1386.id as attr_id_sample_1386,\nsample1386.text_value as attr_sample_1386,\nsample1660.id as attr_id_sample_1660,\nsample1660.int_value as attr_sample_1660,\nltt1660.term as attr_sample_term_1660,\nsample1389.id as attr_id_sample_1389,\nsample1389.int_value as attr_sample_1389,\nltt1389.term as attr_sample_term_1389,\nsample1390.id as attr_id_sample_1390,\nsample1390.int_value as attr_sample_1390,\nltt1390.term as attr_sample_term_1390,\nsample2_1387.id as attr_id_sample2_1387,\nsample2_1387.int_value as attr_sample2_1387,\nsample2_1457.id as attr_id_sample2_1457,\nsample2_1457.int_value as attr_sample2_1457,\nsample2_1393.id as attr_id_sample2_1393,\nsample2_1393.int_value as attr_sample2_1393,\nltt2_1393.term as attr_sample2_term_1393,\noccurrence780.id as attr_id_occurrence_abundance_count,\noccurrence780.int_value as attr_occurrence_abundance_count\n from samples parent\n JOIN surveys su ON su.id = parent.survey_id\n JOIN samples s ON s.parent_id=parent.id AND s.deleted=false\n JOIN locations l ON parent.location_id=l.id\n JOIN cache_termlists_terms ctt ON ctt.id=l.location_type_id\n JOIN locations l2 ON s.location_id=l2.id\n JOIN occurrences o ON o.sample_id=s.id AND o.deleted=false\n JOIN cache_taxa_taxon_lists cttl ON cttl.id=o.taxa_taxon_list_id\n AND (CAST(cttl.taxon_list_id as character varying)='' OR '' = '')\n LEFT JOIN sample_attribute_values sample1384 ON sample1384.sample_id=parent.id AND sample1384.sample_attribute_id=1384 AND sample1384.deleted=false\n LEFT JOIN sample_attribute_values sample1385 ON sample1385.sample_id=parent.id AND sample1385.sample_attribute_id=1385 AND sample1385.deleted=false\n LEFT JOIN sample_attribute_values sample1386 ON sample1386.sample_id=parent.id AND sample1386.sample_attribute_id=1386 AND sample1386.deleted=false\n LEFT JOIN sample_attribute_values sample1660 ON sample1660.sample_id=parent.id AND sample1660.sample_attribute_id=1660 AND sample1660.deleted=false\nLEFT JOIN cache_termlists_terms ltt1660 ON ltt1660.id=sample1660.int_value\n LEFT JOIN sample_attribute_values sample1389 ON sample1389.sample_id=parent.id AND sample1389.sample_attribute_id=1389 AND sample1389.deleted=false\nLEFT JOIN cache_termlists_terms ltt1389 ON ltt1389.id=sample1389.int_value\n LEFT JOIN sample_attribute_values sample1390 ON sample1390.sample_id=parent.id AND sample1390.sample_attribute_id=1390 AND sample1390.deleted=false\nLEFT JOIN cache_termlists_terms ltt1390 ON ltt1390.id=sample1390.int_value\n LEFT JOIN sample_attribute_values sample2_1387 ON sample2_1387.sample_id=s.id AND sample2_1387.sample_attribute_id=1387 AND sample2_1387.deleted=false\n LEFT JOIN sample_attribute_values sample2_1457 ON sample2_1457.sample_id=s.id AND sample2_1457.sample_attribute_id=1457 AND sample2_1457.deleted=false\n LEFT JOIN sample_attribute_values sample2_1393 ON sample2_1393.sample_id=s.id AND sample2_1393.sample_attribute_id=1393 AND sample2_1393.deleted=false\nLEFT JOIN cache_termlists_terms ltt2_1393 ON ltt2_1393.id=sample2_1393.int_value\n LEFT JOIN occurrence_attribute_values occurrence780 ON occurrence780.occurrence_id=o.id AND occurrence780.occurrence_attribute_id=780 AND occurrence780.deleted=false\n \n WHERE parent.deleted = false\n AND (o.website_id in (118) or o.website_id is null)\n AND s.survey_id=562\nAND ('(223845,223838,223867,223334,282298,301011,223355,223824,246173,228494,223858,244316,223571,223850,314164,223874,240151,282652,278383,223580,225663,223852,251632,322200,223335,223572,223884,223581,308776,277976,223336,320024,223330,223839,223840,223573,244278,265052,223337,223876,223356,253575,303241,279205,223875,223338,250806,223831,242652,223339,223340,223877,264789,278423,225661,225662,223357,223851,223582,308880,223583,223584,223585,303224,223878,293398,223586,225664,225665,282799,223570,223574,322207,282304,223587,279631,223825,246162,273548,264626,273577,275790,223879,223354,223341,223328,303583,245352,306374,227721,223362,223880,223841,223871,308558,286076,303614,223836,223881,223564,223358,268249,310631,223883,256449,223588,223856,267434,223870,256818,225672,223859,256689,266196,223837,228053,239789,330019,327426,298523,286084,240075,223344,223569,223590,223342,315323,298284,250802,223343,240245,254406,223826,246165,268910,328374,322434,225666,225667,250035,257218,291734,223591,223860,223592,223593,223832,223609,323724,223833,273447,223589,223872,223873,223329,223345,295917,295928,305527,223594,223846,223842,223363,223575,255100,228057,223834,223610,303006,278417,223843,223868,223595,223576,320334,223861,261326,223596,223862,223347,223346,227705,225668,257172,223885,223577,283456,223597,322448,223857,223866,223827,246167,223348,251113,223853,223359,291725,223863,307222,223844,223349,256681,223600,322456,223599,223598,223601,223331,223350,223332,309020,250875,332766,223828,246169,228541,306500,246029,284235,322596,332755,223351,225669,223352,303023,223602,273638,223578,223854,223864,225670,225671,326711,223865,318711,322602,322610,223829,246171,279833,223603,223882,223360,223604,301726,223361,329031,303000,243817,223353,300311,223611,265526,225673,223606,223847,223869,269391,284760,303028,223607,223855,223848,223830,256051,223849,223608,223835,223579,223333)' = '-1' OR parent.location_id IN ((223845,223838,223867,223334,282298,301011,223355,223824,246173,228494,223858,244316,223571,223850,314164,223874,240151,282652,278383,223580,225663,223852,251632,322200,223335,223572,223884,223581,308776,277976,223336,320024,223330,223839,223840,223573,244278,265052,223337,223876,223356,253575,303241,279205,223875,223338,250806,223831,242652,223339,223340,223877,264789,278423,225661,225662,223357,223851,223582,308880,223583,223584,223585,303224,223878,293398,223586,225664,225665,282799,223570,223574,322207,282304,223587,279631,223825,246162,273548,264626,273577,275790,223879,223354,223341,223328,303583,245352,306374,227721,223362,223880,223841,223871,308558,286076,303614,223836,223881,223564,223358,268249,310631,223883,256449,223588,223856,267434,223870,256818,225672,223859,256689,266196,223837,228053,239789,330019,327426,298523,286084,240075,223344,223569,223590,223342,315323,298284,250802,223343,240245,254406,223826,246165,268910,328374,322434,225666,225667,250035,257218,291734,223591,223860,223592,223593,223832,223609,323724,223833,273447,223589,223872,223873,223329,223345,295917,295928,305527,223594,223846,223842,223363,223575,255100,228057,223834,223610,303006,278417,223843,223868,223595,223576,320334,223861,261326,223596,223862,223347,223346,227705,225668,257172,223885,223577,283456,223597,322448,223857,223866,223827,246167,223348,251113,223853,223359,291725,223863,307222,223844,223349,256681,223600,322456,223599,223598,223601,223331,223350,223332,309020,250875,332766,223828,246169,228541,306500,246029,284235,322596,332755,223351,225669,223352,303023,223602,273638,223578,223854,223864,225670,225671,326711,223865,318711,322602,322610,223829,246171,279833,223603,223882,223360,223604,301726,223361,329031,303000,243817,223353,300311,223611,265526,225673,223606,223847,223869,269391,284760,303028,223607,223855,223848,223830,256051,223849,223608,223835,223579,223333)))\nAND l.location_type_id=777\nAND parent.created_by_id=201126\n\n AND (trim('2023-01-01')='' OR s.date_end >= CAST(COALESCE('2023-01-01','1500-01-01') as date))\n AND (trim('2023-12-31')='' OR s.date_start <= CAST(COALESCE('2023-12-31','1500-01-01') as date))\n ORDER BY l.code, parent.date_start, section_number, cttl.taxonomic_sort_order, o.id DESC\n ","code":44,"file":"\/srv\/sites\/warehouse1.indicia.org.uk\/system\/libraries\/drivers\/Database\/Pgsql.php","line":368,"trace":[]}

[CrisSevilleja]

I think the problem of Irma could be that she was not logged in on her eBMS account. I don't have problems entering that eBMS page

[andrewvanbreda]

Hi @CrisSevilleja Thanks for the information, I have removed the report from the site menu until it is fixed. Andy

[Gary-van-Breda]

This is a reports/form issue (an incompatability between parameters passed from the form to the download reports), and may be related to the move. I'll have to investigate to find out which is at fault.

[andrewvanbreda]

@Gary-van-Breda At least one of the problems us the #location_list# parameter is having an extra set of brackets being put around it by something. In ebms_summary_download I see this AND ('(308971,315200,308977,308699,316488)' = '-1' OR parent.location_id IN ((308971,315200,308977,308699,316488)))

when I think this works AND ('(308971,315200,308977,308699,316488)' = '-1' OR parent.location_id IN (308971,315200,308977,308699,316488))

From the SQL

('#location_list#' = '-1' OR parent.location_id IN (#location_list#))

Could this be server move to Linux?

[andrewvanbreda]

@Gary-van-Breda Also note I had to adjust iForm's current Master report_helper code to include some code from EBMS's old report_helper for the Drupal 10 move. It relates to getting the user_id in the report_calendar_summary section. Not sure, that might have broken what Cristina is mentioning. Before the adjustment there was a validation error.

[xaviermestdagh]

thanks for you effort in making the annual export works, do you think there is any hope to have this annual export mean working again? This is what I use for exporting records into my local database, so would be great to know if I need to change it...

[DavidRoy]

@johnvanbreda @JimBacon same issue being addressed for the ukbms?

@xaviermestdagh I think the downloads page should give you access to the same files? https://butterfly-monitoring.net/downloads

[johnvanbreda]

@DavidRoy no, I think this is an error in the report rather than a change to the server configuration.

[DavidRoy]

@Gary-van-Breda @andrewvanbreda any advice on resolving this in that case?

[andrewvanbreda]

Hi @Gary-van-Breda, Are you progressing this or shall I try to fix it?

[andrewvanbreda]

I will look at the issue

[andrewvanbreda]

Hi @xaviermestdagh @CrisSevilleja @DavidRoy I have applied fixes which are now live. Will discuss with Gary about how to deal with the fixes in an elegant way going forward, so the same issues do not reoccur. I will put details of the fixes in this thread once have discussed with Gary.

[CrisSevilleja]

Great @andrewvanbreda I did a quick check and the site looks to be working well. I will make a deep check. Thanks for fixing it.

[andrewvanbreda]

Fix notes (for future developer reference).

The following functions in report_helper were reverted back to the versions from the old Drupal 9 site report_calendar_summary2 report_calendar_summary_sort1 report_calendar_grid get_report_calendar_grid_options get_report_calendar_summary_options

In particular the report_calendar_summary2 function contains extra code in the latest indicia-team Master branch that causes the extra bracket issue I mentioned above

if ($downloadOptions['extraParams']['location_list'] == "") $downloadOptions['extraParams']['location_list'] = "(-1)"; else $downloadOptions['extraParams']['location_list'] = '(' . $downloadOptions['extraParams']['location_list'] . ')';

I will note this for investigation.

[xaviermestdagh]

@johnvanbreda @JimBacon same issue being addressed for the ukbms?

@xaviermestdagh I think the downloads page should give you access to the same files? https://butterfly-monitoring.net/downloads

the downloads page was still under development and format changes so that I kept used to the annual reports... but having both possibilities to get exports is a great way to mitigate issues ;-)

[Gary-van-Breda]

Some work has been done on this page (and its underlying functionality in report_helper) to make sure the EBMS, UKBMS and client_helpers are now all in sync. This will prevent reoccurence of this issue.

@xaviermestdagh : it looks like you can now download from the Annual Report - if you are happy, this issue can now be closed.

FYI @CrisSevilleja

[xaviermestdagh]

thanks @Gary-van-Breda , I confirm being able to download annual report. Thanks!