Topic: Broken download page
I've just tried to do a data download from SEWBReCORD (which I regularly do prior to running John's Indicia2Recorder R6 addin to run some simple validation checks) and have received an error (reproduced below). As far as I can see I have done nothing differently to usual.
The report used is 'Utilities -> Easy Download 2'
I selected 'All Records for Reporting', filters set to <All available records> and Survey set to <All>, date field 'Last Changed Date', and then selected two dates (10/07/2016 to 31/07/2016).
Is this a bug introduced by my recent updates, or something new?
Dave
Error URL:
http://warehouse1.indicia.org.uk/index. … =reporting
The Error:
{"error":"general_errors.There was an SQL error: ERROR: invalid input syntax for type date: \"#input_date_to#\"\nLINE 59: ...(length('2016-07-31')<=10 AND o.updated_on < cast('#input_da...\n ^ - \n SELECT distinct on (o.id) o.id as \"occurrence_id\",\n'iBRC' || o.id as \"recordkey\",\nsnf.website_title || ' | ' || snf.survey_title as \"source\",\ncase when onf.sensitivity_precision is null then o.sample_id else null end as \"sample_id\",\ncttl.preferred_taxon as \"taxon\",\ncttl.default_common_name as \"common\",\ncttl.taxon_group as \"taxon_group\",\ncttl.kingdom_taxon as \"kingdom\",\ncttl.order_taxon as \"order\",\ncttl.family_taxon as \"family\",\no.taxa_taxon_list_external_key as \"taxonversionkey\",\no.taxa_taxon_list_id as \"taxa_taxon_list_id\",\nonf.comment as \"record_comment\",\ncase when onf.sensitivity_precision is null and onf.privacy_precision is null then o.location_name else 'Sensitive. Lat long is approximate.' end as \"location_name\",\ncase snf.entered_sref_system when '4326' then snf.public_entered_sref else replace(snf.public_entered_sref, ' ', '') end as \"entered_sref\",\nsubstring(st_astext(st_transform(st_centroid(o.public_geom), 4326)) from E'POINT\\\\(.+ (.+)\\\\)') as \"lat\",\nsubstring(st_astext(st_transform(st_centroid(o.public_geom), 4326)) from E'POINT\\\\((.+) ') as \"long\",\ncase snf.entered_sref_system when '4326' then 'WGS84' when '27700' then 'OSGB36' else upper(snf.entered_sref_system) end as \"projection\",\ncase snf.entered_sref_system when '4326' then 50 when '27700' then 1 else case length(replace(snf.public_entered_sref, ' ', '')) when 5 then 2000 else pow(10, (12-length(replace(snf.public_entered_sref, ' ', '')))\/2) end end as \"precision\",\nonf.output_sref as \"output_sref\",\nvc.name as \"vicecounty\",\no.date_start as \"date_start\",\no.date_end as \"date_end\",\no.date_type as \"date_type\",\no.created_on as \"input_date\",\no.updated_on as \"last_edit_date\",\nrtrim(snf.recorders, ', ') as \"recorder\",\nCASE WHEN onf.attr_det_full_name IS NULL THEN CASE WHEN onf.attr_det_last_name IS NULL THEN NULL ELSE onf.attr_det_last_name || COALESCE(', ' || onf.attr_det_first_name, '') END ELSE onf.attr_det_full_name END as \"determiner\",\nonf.verifier as \"verifier\",\no.verified_on as \"verified_on\",\nsnf.comment as \"sample_comment\",\nupper(cast (o.zero_abundance as character)) as \"zeroabundance\",\nCASE o.record_status WHEN 'V' THEN 'Verified' WHEN 'D' THEN 'Queried' WHEN 'R' THEN 'Rejected' WHEN 'I' THEN 'Input still in progress' WHEN 'C' THEN 'Record Submitted' WHEN 'S' THEN 'Awaiting check' ELSE o.record_status END as \"record_status\",\nCASE CAST(o.created_by_id AS character varying) WHEN '#currentUser#' THEN true ELSE false END as \"belongs_to_user\",\nCASE o.certainty WHEN 'C' THEN 'Certain' WHEN 'L' THEN 'Likely' WHEN 'U' THEN 'Uncertain' END as \"certainty\",\ncase when snf.recorders = p.surname || ', ' || p.first_name or snf.recorders = p.first_name || ' ' || p.surname then cast(p.id as varchar(20)) else 'unknown' end as \"recorder_person_id\",\ncase when snf.recorders = onf.attr_det_last_name || ', ' || onf.attr_det_first_name or snf.recorders = onf.attr_det_first_name || ' ' || onf.attr_det_last_name or snf.recorders = onf.attr_det_full_name then cast(p.id as varchar(20)) else 'unknown' end as \"determiner_person_id\",\ncase when onf.media is null then null else '' || replace(onf.media, ',', ', ') end as \"images\",\nonf.licence_code as \"licence_code\", \nattr_det_full_name, \nattr_sex, \nattr_stage, \nattr_sex_stage_count\n FROM cache_occurrences_functional o\n JOIN cache_occurrences_nonfunctional onf on onf.id=o.id\n JOIN cache_samples_nonfunctional snf on snf.id=o.sample_id\n JOIN cache_taxa_taxon_lists cttl on cttl.id=o.taxa_taxon_list_id\n LEFT JOIN locations vc on vc.id=o.location_id_vice_county and vc.deleted=false\n JOIN users u ON u.id=o.created_by_id\n JOIN people p ON p.id=u.person_id\n JOIN users privacyusers ON privacyusers.id=o.created_by_id\n \n WHERE (o.training=false OR o.training IS NULL) AND (o.website_id in (41) OR privacyusers.id=1 OR privacyusers.allow_share_for_reporting=true OR privacyusers.allow_share_for_reporting IS NULL)\n AND o.website_id in (41)\n \n AND (o.release_status='R' or o.release_status is null)\nAND ('2016-07-10'='Click here' OR o.updated_on >= '2016-07-10'::timestamp)\nAND ('2016-07-31'='Click here' OR (o.updated_on <= '2016-07-31'::timestamp OR (length('2016-07-31')<=10 AND o.updated_on < cast('#input_date_to#' as date) + '1 day'::interval)))\n ORDER BY o.id DESC LIMIT 20001","code":44,"file":"D:\\web sites\\warehouse1\\system\\libraries\\drivers\\Database\\Pgsql.php","line":342,"trace":[]}
Senior IT & Records Officer,
15 Talbot Road, Talbot Green, Pontyclun, CF72 8AD
www.sewbrec.org.uk, www.sewbrecord.org.uk