Monthly/Annual Query Report in Permit Reporting not working when linking Facility to new Permit [#2820924]

Problem/Motivation:

Editing permit conditions may cause the startdate and enddate properties to be set to a date of 1970-01-01, which effectively renders these conditions expired, which causes the SQL views that show use versus permitted to return no results.

Example Active Permit: gwp-permit-dash-compliance/66438
Example Expired Permit: gwp-permit-dash-compliance/66437
Monthly Use View: d.dh/admin/structure/views/view/ows_permit_monthly_use_report/edit/page
Annual Use View: d.dh/admin/structure/views/view/ows_compliance_report_annual_use/edit/page

Proposed Solution

Fix dHPropertiesController::save() to correctly handle NULL or '' in date fields.


  public function save($entity, DatabaseTransaction $transaction = NULL) {
    $timecols = array('startdate', 'enddate');
    foreach ($timecols as $thiscol) {
      if (property_exists($entity, $thiscol)) {
        $entity->$thiscol = dh_handletimestamp($entity->$thiscol);
        if ($entity->$thiscol == '') {
          $entity->$thiscol = NULL;
        }
      }
    }
    if (strlen($entity->propvalue) == 0) {
      unset($entity->propvalue);
    }
    return parent::save($entity, $transaction);
  }

Temporary fixing of affected conditions can be achieved by the following code:

-- target specific properties
update dh_properties_revision set startdate = null, enddate = null where startdate = 0 and propname = 'wd_limit_gpy';
update dh_properties set startdate = null, enddate = null where startdate = 0 and propname = 'wd_limit_gpy';

-- update all properties with startdate = 0 - this allows potential for over-writing properties who actually have a startdate of '1970-01-01'

select count(*) from dh_properties where startdate = 0 and varid in (select hydroid from dh_variabledefinition where data_entry not ilike '%date%');
 count
-------
   374
(1 row)

-- only apply to those with no data_entry settings of type date
update dh_properties set startdate = null, enddate = null where startdate = 0 and varid in (select hydroid from dh_variabledefinition where data_entry not ilike '%date%');

Remaining Tasks

~~Update properties for affected permit conditions on d.dh (in order to restore functionality for compliance views while we test the fix)~~
~~Apply code fix on d.alpha~~
~~Test on d.alpha~~
~~Migrate module to d.beta~~
Test on d.beta
Final migrating module to d.dh

Comments

Comment #1

20 October 2016 at 21:03

c_thomas created an issue. See original summary.

Comment #2

c_thomas CreditAttribution: c_thomas commented 8 December 2016 at 16:59

Robert - I did look into this to make sure that there was no water use data associated with the permit in question. You had said you didn't find any water use data for the Cavalier Golf Course Permit (66438) but one of the wells has been reporting, with some data for 2015 and earlier. So could there be another explanation for the Permit reports not getting populated? A link to the wells water use reporting data is below as well as the permit page with the empty queries.

Active Well with Reporting: dh/?q=gwp-well-dash-compliance/1634/water_use
Permit Compliance: dh/?q=gwp-permit-dash-compliance/66438

Comment #3

robertwb CreditAttribution: robertwb commented 31 October 2016 at 13:52

Just to be clear, it is not that there is no data associated with the permit (via its link with the MPs), but the permit time period. It looks to me like the only data reported in that permit period is water quality, and there is no limit associated with that, so I think that it correctly returns no results. But I am open to correction.

Comment #4

c_thomas CreditAttribution: c_thomas commented 8 December 2016 at 16:58

This issue has been brought up again. I don't fully understand the "dH OWS - GWPermit Views" so can't seem to get to the bottom of this. In the case below, the permit definitely has withdrawal reporting data associated with it during the permit time period. As an example, I've provided one where the monthly and annual queries are not working and one where the queries are working.

Not Working Compliance Queries: dh/?q=gwp-permit-dash-compliance/66501
Working Compliance Queries: dh/?q=gwp-permit-dash-compliance/66418
Annual Compliance View: dh/admin/structure/views/view/ows_compliance_report_permit_annual_use/edit/page
Monthly Compliance View: dh/admin/structure/views/view/ows_permit_monthly_use_report/edit/page

Comment #5

robertwb CreditAttribution: robertwb commented 17 November 2016 at 12:06

Traced this to the startdate and enddate of the permit conditions annual limit as the culprit. Essentially, if there is no expiration on the condition (there are 45 permits with temporary limits), the startdate/enddate should be NULL. For a handful of these, the startdate is 0. I reset the startdate and enddate to be null and voila – the Retreat has data.

Have verified that these other 12 are ALSO a problem:

select featureid, startdate, enddate, propvalue, propname, varid from dh_properties where varid = 217 and startdate = 0;
featureid | startdate | enddate | propvalue |   propname   | varid
-----------+-----------+---------+-----------+--------------+-------
     67641 |         0 |       0 |   2300000 | wd_limit_gpy |   217
     67629 |         0 |       0 |   4200000 | wd_limit_gpy |   217
     67669 |         0 |       0 |  81675000 | wd_limit_gpy |   217
     66840 |         0 |       0 |   6300000 | wd_limit_gpy |   217
     66283 |         0 |       0 |   7200000 | wd_limit_gpy |   217
     66501 |         0 |       0 |   8294214 | wd_limit_gpy |   217
     66394 |         0 |       0 |  61400000 | wd_limit_gpy |   217
     66624 |         0 |       0 |  59000000 | wd_limit_gpy |   217
     67666 |         0 |       0 | 137200000 | wd_limit_gpy |   217
     66783 |         0 |       0 |   5113300 | wd_limit_gpy |   217
     66671 |         0 |       0 |  21700000 | wd_limit_gpy |   217
    224229 |         0 |       0 | 240000000 | wd_limit_gpy |   217
(12 rows)

Comment #6

robertwb CreditAttribution: robertwb commented 17 November 2016 at 12:32

Issue summary:

View changes

Comment #7

robertwb CreditAttribution: robertwb commented 17 November 2016 at 12:38

Issue summary:	View changes
Status:	Active	» Needs review

I have applied fixes to the module and tested on d.alpha, I have updated the module on d.beta, but I think we should test there somewhat extensively until we migrate. If you can let me know when testing on d.beta is complete I will do the final migration (this is bundled in with a number of other changes the need to go in prior to VWUDS launch, so we should test property related edit and views for GW-2, WSP, GWP, Compliance, and VWUDS before doing the final migration).

Comment #8

robertwb CreditAttribution: robertwb commented 17 November 2016 at 12:39

I have also manually updated all affected properties on d.dh, so the compliance views should work in the meantime. If you can test that on d.dh (just that all previously affected permits are now behaving) that would be most awesome.

Comment #9

c_thomas CreditAttribution: c_thomas commented 8 December 2016 at 16:58

I tested and confirmed that the changes to the GWP SQL views worked. It now does not upset the query when a permit condition is edited. That should be good to go.

In testing the property edit widgets, I'm finding two different cases. Editing with the widgets still works fine. However, with the WSP and GW-2 edit pages I was unable to Add new properties through the widgets. It does take you to the Add page, but then gives an Error upon pressing Save. I know this was working previously and did not uncover the problem (though it could be a problem with the way the widget is set up). The property widget DOES work to Add new properties with the VWP page that I set up. It is two cases of not working and one case of working, so it may very well that widgets are set up improperly and the code is change isn't causing the problem.

Not Working to Add New Property:
WSP: d.beta/?q=ows-submittal-wsp_plan_system-sources/176988
GW2: d.beta/gw2-submission-edit-form/182651

Working to Add New Property:
VWP: d.beta/gwp-permit-dash-vwpconditions/66501

Comment #10

robertwb CreditAttribution: robertwb commented 18 November 2016 at 19:27

Ahh - you uncovered a bug in the properties add form code where it was generating a default propname when none was supplied - since propname is mandatory, but the property add widget WAS supplying a propname in the URL, but the system was not using it. So, you managed to fix 2 bugs at once

The default propname was screwy and adding a name that was larger than the capacity of the propname field (now defaults to varkey which is 32 chars andpropname max is 64),
The propname that was being supplied by the prop widget was being ignored, so now it is much nicer.

Pushed this to beta for further testing.