Gary-van-Breda
commented
1 year ago Occurrence and Sample Download Reports updated (location list parameter where clause updated). New EBMS specific versions of raw data, summary and section download reports created.
All deployed to the warehouse and checked - look OK to me.
Bouncing to @CrisSevilleja for review/closure.
CrisSevilleja
In the Annual Report https://butterfly-monitoring.net/annual-report data from the transects can be downloaded, I tried to test this function for a regional coordinator.
I masqueraded as a regional coordinator ID user 246336, for Spanish BMS, Region Galicia. I tried to download data from all the transects from the Region of Galicia, the samples file was downloaded but it is empty and the occurrences file gave the error:
{"error":"There was an SQL error: ERROR: syntax error at or near \"244316\"\nLINE 68: ...,303028,223579' = '(-1)' OR parent.location_id IN 244316,223...\n ^ - \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 ('244316,223571,282298,223572,223573,223570,223574,282304,286076,310631,223569,223575,303006,223576,320334,223577,303023,273638,223578,326264,303000,303028,223579' = '(-1)' OR parent.location_id IN 244316,223571,282298,223572,223573,223570,223574,282304,286076,310631,223569,223575,303006,223576,320334,223577,303023,273638,223578,326264,303000,303028,223579)\nAND l.location_type_id=777\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":"D:\websites\warehouse\system\libraries\drivers\Database\Pgsql.php","line":368,"trace":[]}