.csv file reports

[xaviermestdagh]

As national coordinator I need

• an access to national data report (.csv), which is currently not the case.
• additional fields in the .csv data report: Site_id, Section_id (in relation with the shapefile), Start_time, End_time, Sun, Wind_speed,... Species_name, Abundance, Reliability

[xaviermestdagh]

Sorry it seems I didn't explored all the download possibilities. I have indeed access to csv data, at country level which great, although some information are missing:

• Start_time, End-time, Sun, Wind_speed,...
• Reliability (section-level)

Also, I only found reports for single years, would it be possible to have one single report for all the country including all years?

[Gary-van-Breda]

There are 4 download reports - which of the reports do you want the data to appear in?

[Gary-van-Breda]

We had problems on UKBMS with the warehouse running out of memory when the full data set was downloaded, hence the year restriction.

[xaviermestdagh]

I have an error while creating the report "Samples" {"error":"general_errors.There was an SQL error: - SELECT vt.sample_id as main_id,\r\n vt.text_value, vt.float_value, vt.int_value, vt.date_start_value, vt.date_end_value, vt.date_type_value,\r\n at.id, at.caption, at.data_type, at.termlist_id, at.multi_value FROM samples lt0 INNER JOIN sample_attribute_values vt ON (vt.sample_id = lt0.id and vt.deleted = FALSE) INNER JOIN sample_attributes at ON (vt.sample_attribute_id = at.id and at.deleted = FALSE) INNER JOIN sample_attributes_websites rt ON (rt.sample_attribute_id = at.id and rt.deleted = FALSE and (rt.restrict_to_survey_id = 2 or rt.restrict_to_survey_id is null)) WHERE lt0.deleted = FALSE and lt0.parent_id IS NULL and lt0.survey_id = 2 AND ('7'='' OR CAST(lt0.created_by_id AS character varying)='7') AND lt0.date_end >= CAST(COALESCE('01\/01\/2016','1500-01-01') as date) AND lt0.date_start <= CAST(COALESCE('31\/12\/2016','1500-01-01') as date) ORDER BY rt.form_structure_block_id, rt.weight, at.id, lt0.id ","code":44,"file":"D:\web sites\warehouse2\system\libraries\drivers\Database\Pgsql.php","line":342,"trace":[]}

[BirenRathod]

@xaviermestdagh Could you send me the page link please?

[xaviermestdagh]

http://warehouse2.indicia.org.uk/index.php/services/report/requestReport?report=reports_for_prebuilt_forms/UKBMS/ukbms_sample_download.xml&reportSource=local&filename=2016_XavierMestdagh_Allmysites_Samples_20170428134111&mode=csv&nonce=e502db560e99d52647473d9988cb28107f60ab5c&auth_token=6d3aef30c7374a4fc79973b6d574faf9d8ca6123&&date_from=01%2F01%2F2016&date_to=31%2F12%2F2016&occattrs=Abundance+count&taxon_list_id=1&smpattrs=&survey_id=2&user_id=7&location_id=&location_list=%28-1%29&location_type_id=107&cms_user_id=6

[BirenRathod]

and also the link from EBNS website please. if it is from ukbms please pass that too.

[xaviermestdagh]

Not sure I understand well your request, here the link from which I push the button "Download" beside "Samples"... http://www.butterfly-monitoring.net/annual-summary?userID=6&year=2016

[xaviermestdagh]

@Gary-van-Breda I imagine that fields from the "Site details" (first part of the Walk Data Entry) might go to the Samples report. Fields from the second part of the Walk Data Entry might go to the Occurences report (currently missing "Reliability")

[xaviermestdagh]

@BirenRathod here the link from ebms when the error message is displayed http://warehouse2.indicia.org.uk/index.php/services/report/requestReport?report=reports_for_prebuilt_forms/UKBMS/ukbms_sample_download.xml&reportSource=local&filename=2016_XavierMestdagh_WanterfeldRumelange_Samples_20170616104206&mode=csv&nonce=b0af9253179bf6bcd7df93baf435654ea9b5dfda&auth_token=e29dc5bd1b641370fb834c7bb0f7b881943d9fb4&&date_from=01%2F01%2F2016&date_to=31%2F12%2F2016&occattrs=Abundance+count&taxon_list_id=1&smpattrs=&survey_id=2&user_id=7&location_id=394&location_list=%28-1%29&location_type_id=107&cms_user_id=6

[Gary-van-Breda]

I can't see where there is an SQL error, unless it is reading in the date 31/12/2017 in MM/DD/YYYY format, instead of DD/MM/YYYY: can we confirm the date setup in the database?

[BirenRathod]

@Gary-van-Breda & @xaviermestdagh , I have changed the date format to dd/mm/yyyy in the database setting.

[xaviermestdagh]

Thanks, Sample report is well created. Remaing points from this issue:

• Missing fields in Occurrences report, related to section habitat and management (waiting for improvement of issue #5 and #6)

[Gary-van-Breda]

At the moment the download reports available on the Annual summary pages have the following restrictions: 1) Summary Level: fixed content, no ability to specify location or sample attributes to be included. 2) Section Level: fixed content, no ability to specify location, section, sample or subsample attributes to be included. 3) Sample Level: content extension restricted, no ability to specify location, section or subsample attributes to be included. I have added the sample level attibutes to this (Recorder Name, Start Time, End Time, % Sun, Temp (Deg C), Wind Direction, Wind Speed). 4) Occurrence Level: fixed content, no ability to specify location, section, sample or subsample attributes to be included.

These reports have been inherited from UKBMS, and may need alteration to meet the requirements of EBMS (e.g. add ability to include the attributes mentioned above). I believe this should be viewed as a functionality change rather than a bugfix.

[DavidRoy]

@xaviermestdagh to define additional requirements for Luxembourg scheme. We do not currently have any funding to address these extra requirements

[Gary-van-Breda]

@xaviermestdagh : can you confirm which reports you wish to have the extra data added to?

As I read it above, these are to be added to the occurrence download report only, which is the last option on the Downloads tab on the Annual Summary page. The other reports are to be left alone. If possible you would also like to run this report to download all the data in one.

My current plan is to create an EBMS specific occurrence download report to include attibutes for location, section, sample or subsample. This can then be configured to include the EUNIS data when that is addressed. In addition I will setup a specific download page with access to this report, which should allow a more flexible definition of the filters, including start and end dates.

[xaviermestdagh]

Occurrence report is only missing the new "reliabitiy" attribute, and if it is straightforward the section code is welcome (to link with the section code from the shapefile).

Sample report includes now all attributes from the visit details, which is great.

Site related attributes to be included at: Site level:

• Overall length
• Number of sections
• Transect width
• Sensitive
• "Habitat etc" attributes (EUNIS, management,... once addressed) Section level:
• Section length
• "Habitat etc" attributes (EUNIS, management,... once addressed) If possible, I imagine those site/section attributes in a dedicated report rather than in the occurrence or sample report. Your current plan looks promising.

[DavidRoy]

I agree that two download formats/files are needed. One for sites and one for sections. A good addition to the site.

@Gary-van-Breda - can you also deploy to UKBMS please

[Gary-van-Breda]

@DavidRoy : who do you want this available to? Is it for the super managers, branch/country managers, or general users? If it is available for branch/country managers and/or general users, do you want in filtered accordingly? NB there is already the capability to download a list of sites from the "My Sites" and "Branch/Country Sites" pages.

[DavidRoy]

All users, with permissions/filters applied:

• individual data recorders
• branch/country managers
• super managers

This Site report can replace the existing report on 'My sites' and 'branch/country sites' pages. The Site-section report can also be made available via these pages.

[Gary-van-Breda]

I have lodged 4 new reports into Git warehouse, hotfix-ebms-reports-updates @JimBacon or @BirenRathod : can I have them deployed into warehouse2? I know warehouse1 is kept upto date - I not sure how warehouse2 is done?

[BirenRathod]

@Gary-van-Breda Yes, I do regularly update that warehouse too. It has now upgraded to latest code.

[Gary-van-Breda]

@BirenRathod : I can't see the reports on the warehouse2, but they are in warehouse1. These reports are in the new(ish) indicia-reports repository - I suspect this has not been set up for warehouse2. Can you deploy the indicia-reports repository/module as given in the README? Thanks.

[DavidRoy]

@JimBacon can you look into this please

[JimBacon]

I've cloned and enabled the brc_reports module. While I was there I also pulled in the latest changes to media and client_helpers which were a bit behind.

[Gary-van-Breda]

@JimBacon : thanks, thats good.

Unfortunately this has shown up a bug in the code which calculates the total transect length: it produces a set of new values of the length in the first editing session where the transect sections are added, rather than just one. Subsequent editing sessions are OK. This leads to duplicate records in the reports. This code is inherited from UKBMS, and this will affect them as well.

[Gary-van-Breda]

@BirenRathod or @JimBacon : can you unzip and deploy the attached file into the iform/client_helpers/prebuilt_forms/js directory on the EBMS Drupal site? Thanks ebms_sectioned_transects_edit_transect.zip

[BirenRathod]

Hello @Gary-van-Breda I copied this on to live EBMS Drupal site.

[Gary-van-Breda]

Thanks Annual Summary page: samples report checked for new fields : OK Occurrences download changed to new EBMS version, and configured to download reliability field. The % Sun in this report now outputs the value entered for the section, not the parent sample. Now includes the section code. Full Site and Section download links added to the bottom of the "My Sites", "Branch Sites" and "All Sites" Grids. I am currently doing the data fix for the multiple total transect length attribute issues.

[Gary-van-Breda]

Data fix now complete. I've also set up the full data download page, under reporting. There is only one report, and it is currently set up with all the top level sample, section level sample and occurrence attributes as they stand at the moment. No location attributes, these can be added if required. This page is only available to administrator users (Xavier, me and admin), and is in English only.

I think this issue is now completed for EBMS. I am going to investigate putting the site/section reports onto UKBMS

[Gary-van-Breda]

Requires the deployment of a tweak to the reports for UKBMS to work: on hold until the reports are deployed. Change pushed into master branch of brc_reports. Required on warehouse1, can be deployed on warehouse2 as well, but not required.

[xaviermestdagh]

The name "% Sun" is given to two fields (I guess one for the % Sun at walk level, the other at section level). Is there a way to modify this (in the report or in the form?). For the rest it looks great, thanks!

[Gary-van-Breda]

One is the % Sun from the main sample, the other is from the section. Do you want both, or just one?