1

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":[]}

Dave Slade
Senior IT & Records Officer,
15 Talbot Road, Talbot Green, Pontyclun, CF72 8AD
www.sewbrec.org.uk, www.sewbrecord.org.uk

2

Re: Broken download page

Hi

Let's see now.
I can observe that the problem is with the date filtering where we have

AND ('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)))

The token #input_date_to# has not been replaced by a date. This is one of the standard parameters that is available from the standard report parameters. Ref http://indicia-docs.readthedocs.io/en/l … ard-params

Now let's look at some source code for the standard parameters at https://github.com/Indicia-Team/warehou … rences.php
Actually, that's not such a great help. The lines of code are too long to be legible on GitHub so I'll copy a couple here that come to my attention.

      'input_date_to' => array('datatype'=>'date', 'default'=>'', 'display'=>'Input date to',
        'description'=>'Input date of last record to include in the output',
        'wheres' => array(
          array('value'=>'', 'operator'=>'', 
            'sql'=>"('#input_date_to#'='Click here' OR (o.created_on <= '#input_date_to#'::timestamp OR (length('#input_date_to#')<=10 AND o.created_on < cast('#input_date_to#' as date) + '1 day'::interval)))")
        )
      'edited_date_to' => array('datatype'=>'date', 'default'=>'', 'display'=>'Last update date to',
        'description'=>'Last update date of last record to include in the output',
        'wheres' => array(
          array('value'=>'', 'operator'=>'', 
            'sql'=>"('#edited_date_to#'='Click here' OR (o.updated_on <= '#edited_date_to#'::timestamp OR (length('#edited_date_to#')<=10 AND o.updated_on < cast('#input_date_to#' as date) + '1 day'::interval)))")
        )

This code defines a couple of standard parameters and the SQL filters associated with them. Comparing the two with the query you were executing shows that you were using the edited_date_to parameter. I can confirm that by looking at the parameters in the error url which includes 'edited_date_from=10/07/2016&edited_date_to=17/07/2016'

I can see a bug lurking in our code because the edited_date_to parameter should only use the #edited_date_to# token. This was introduced in the commit https://github.com/Indicia-Team/warehou … 87ba18b3cf made on 12 February 2016.

That you have encountered this now suggests to me that either,
- you have not done this for quite a few months or
- you are using different date parameters to those used last time.
Either way, it is a fault in the Indicia code which I shall attempt to patch forthwith. Thanks for letting us know.

Jim Bacon.

3

Re: Broken download page

Thanks Jim.
I don't think I'd downloaded from this page since I updated all my modules!
Dave

Dave Slade
Senior IT & Records Officer,
15 Talbot Road, Talbot Green, Pontyclun, CF72 8AD
www.sewbrec.org.uk, www.sewbrecord.org.uk

4

Re: Broken download page

The fault is at the warehouse end so you won't need to change anything when I have fixed it.
I reckon it will have been present on our server since March/April

Jim Bacon.

5

Re: Broken download page

Interesting, my last successful download was on 11th July.

Dave Slade
Senior IT & Records Officer,
15 Talbot Road, Talbot Green, Pontyclun, CF72 8AD
www.sewbrec.org.uk, www.sewbrecord.org.uk

6

Re: Broken download page

Well either I'm wrong, which is always possible, or you used a slightly different date filter on that occasion!
Jim Bacon.

7

Re: Broken download page

I've committed a fix and updated the server so hopefully your download should now work.
https://github.com/Indicia-Team/warehou … bcc18e482e

Jim Bacon.

8

Re: Broken download page

Thanks Jim, that has worked.

Dave Slade
Senior IT & Records Officer,
15 Talbot Road, Talbot Green, Pontyclun, CF72 8AD
www.sewbrec.org.uk, www.sewbrecord.org.uk