checkGrowth validation

[illume]

See "instructions for writing a validation function". Below are notes from the spec, the original validation function (in the PHP language), and the SQL table structure.

checkGrowth	Check for extreme growth - for Old Trees form Flags stems where absolute annual growth > $annualGrowth mm Check for shrinkage in recensuses - only for Old Trees form Flags records with annual relative growth <= -0.05 in stems with previous dbh>=50mm and no current stem lost code	Local	To calculate growth rates, need DBH and date of previous census.

   $tempTableName = 'TempOldTrees';

   //Check for extreme growth - for Old Trees form
   //Tags stems where absolute annual growth > 65 mm
   $annualGrowth = '65';

   //Check for shrinkage in recensuses - only for Old Trees form
   //Tags records with annual relative growth <= -0.05 in stems with previous dbh>=50mm and no current stem lost code
   $dbhCheck2 = '50';
   $annualShrink = '-0.05';

    ////Helper to above////
    public function _getCodes($description)
    {
      $this->screeningdb->select('TSMCode');
      $this->screeningdb->like('Description',$description);
      $query = $this->screeningdb->get('TSMAttributes');

      $codesarr = array();

      if($query->num_rows() > 0)
      {
        $j = 0;
        foreach($query->result() as $row)
        $codesarr[$j++] = $row->TSMCode;
      }
      else
      {
        $codesarr[0] = 'none';
      }

      return $codesarr;
    }



    //////Old Trees screening/////////
    public function checkGrowth ($filen,$minDBH,$annualGrowth,$dbhCheck2,$annualShrink)
    {
      //Check for extreme growth - for Old Trees form
      //Tags stems where absolute annual growth > $annualGrowth mm

      $q1 = 'UPDATE '.$filen.' AS a,TempQuadratDates AS b SET Errors = CONCAT(TRIM(Errors),";DBH too large?") WHERE a.QuadratName=b.QuadratName AND HOM=OldHOM AND
             Errors <> "" AND Errors IS NOT NULL AND Errors<>"NONE" AND DBH>='.$minDBH.' AND OldDBH>='.$minDBH.' AND (DBH-OldDBH)/((to_days(ExactDate)-to_days(b.PrevDate))/365.25) > '.$annualGrowth;
      $q2 = 'UPDATE '.$filen.' AS a,TempQuadratDates AS b SET Errors = "DBH too large?" WHERE a.QuadratName=b.QuadratName AND HOM=OldHOM AND
             (Errors = "" OR ISNULL(Errors)) AND Errors<>"NONE"AND DBH>='.$minDBH.' AND OldDBH>='.$minDBH.' AND (DBH-OldDBH)/((to_days(ExactDate)-to_days(b.PrevDate))/365.25) > '.$annualGrowth;
      $runQ1 = $this->screeningdb->query($q1);
      $runQ2 = $this->screeningdb->query($q2);


      //Check for shrinkage in recensuses - only for Old Trees form
      //Tags records with annual relative growth <= -0.05 in stems with previous dbh>=50mm and no current stem lost code

      $resprcodes = $this->_getCodes('stem lost'); //The helper public function above

      //$q1 = "SELECT TSMCode FROM TSMAttributes WHERE LOWER(DESCRIPTION) like '%resprout%' or LOWER(DESCRIPTION) like '%broken%'";
      //$q1 = "SELECT TSMCode FROM TSMAttributes WHERE LOWER(DESCRIPTION)='stem lost'";
      //$r1 = mysql_query($q1, $connect) or die('Selecting stem lost codes failed: ' . mysql_error());
      //$resprcodes=array();
      //if (mysql_num_rows($r1)> 0) {
      //   $j=0;
      //   while ($row = mysql_fetch_assoc($r1)) {
      //            $resprcodes[$j++] = $row["TSMCode"]; }
      //  }

      $q2 = "Select TempID FROM ".$filen;
      $queryQ2 = $this->screeningdb->query($q2);
      // Go through entire file
      foreach($queryQ2->result() as $rowQ2)
      {
        //Check whether tree has resprout or broken code, in which case do not run shrinkage test
        $tempID = $rowQ2->TempID;
        $q3 = "SELECT Codes FROM ".$filen." WHERE TempID = ".$tempID;
        $queryQ3 = $this->screeningdb->query($q3);
        $rowQ3 = $queryQ3->row();
        $codes = $rowQ3->Codes;
        $continue = 0;
        if (!is_null($codes) AND $codes<>'*' AND $codes<>'')
        {
          $nocodes = SUBSTR_COUNT($codes,';');
          $arrcodes = explode(";", $codes);
          for ($i = 0; $i <= $nocodes; $i++)
          {
            if (in_array($arrcodes[$i], $resprcodes))
            {
              $continue=1;
            }
          }
        }

         if($continue==0)
         {

            $q4 = 'UPDATE '.$filen.' AS a,TempQuadratDates AS b SET Errors = CONCAT(TRIM(Errors),";DBH shrank too much") WHERE a.QuadratName=b.QuadratName AND HOM=OldHom
                   AND Errors <> "" AND Errors IS NOT NULL AND Errors<>"NONE" AND DBH>='.$minDBH.' AND olddbh>='.$dbhCheck2.' AND ((DBH-OldDBH)/OldDBH)/((to_days(ExactDate)-to_days(b.PrevDate))/365.25) <='.$annualShrink.'
                   AND TempID = '.$tempID;
            $q5 = 'UPDATE '.$filen.' AS a, TempQuadratDates AS b SET Errors = "DBH shrank too much?" WHERE a.QuadratName=b.QuadratName AND HOM=OldHOM
                   AND (Errors = "" OR ISNULL(Errors)) AND Errors<>"NONE" AND DBH>='.$minDBH.' AND olddbh>='.$dbhCheck2.' AND ((DBH-OldDBH)/OldDBH)/((to_days(ExactDate)-to_days(b.PrevDate))/365.25) <='.$annualShrink.'
                   AND tempID = '.$tempID;
            $runQ4 = $this->screeningdb->query($q4);
            $runQ5 = $this->screeningdb->query($q5);

          }
      }
    }

DROP TABLE IF EXISTS `TSMAttributes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `TSMAttributes` (
  `TSMID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `TSMCode` char(10) NOT NULL,
  `Description` varchar(128) NOT NULL,
  `Status` enum('alive','alive-not measured','dead','missing','broken below','stem dead') DEFAULT NULL,
  PRIMARY KEY (`TSMID`)
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=latin1;

(1,'F','Fallen or missing (1982)','alive'),(2,'L','Leaning','alive')




DROP TABLE IF EXISTS `TempOldTrees`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `TempOldTrees` (
  `TempID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `TreeID` int(10) unsigned DEFAULT NULL,
  `Tag` varchar(10) DEFAULT NULL,
  `StemID` int(10) unsigned DEFAULT NULL,
  `StemTag` varchar(32) DEFAULT NULL,
  `QuadratID` int(10) unsigned DEFAULT NULL,
  `QuadratName` varchar(12) DEFAULT NULL,
  `Subquad` int(10) unsigned DEFAULT NULL,
  `SpeciesID` int(10) unsigned DEFAULT NULL,
  `Mnemonic` varchar(10) DEFAULT NULL,
  `SubSpeciesID` int(10) unsigned DEFAULT NULL,
  `PlotID` int(10) unsigned DEFAULT NULL,
  `CensusID` int(10) unsigned DEFAULT NULL,
  `PlotCensusNumber` int(10) unsigned DEFAULT NULL,
  `PrimaryStem` varchar(20) DEFAULT NULL,
  `X` float DEFAULT NULL,
  `Y` float DEFAULT NULL,
  `DBH` float DEFAULT NULL,
  `OldDBH` float DEFAULT NULL,
  `HOM` float DEFAULT NULL,
  `OldHOM` float DEFAULT NULL,
  `Codes` varchar(50) DEFAULT NULL,
  `OldCodes` varchar(50) DEFAULT NULL,
  `Comments` varchar(256) DEFAULT NULL,
  `ExactDate` date DEFAULT NULL,
  `Errors` varchar(256) DEFAULT NULL,
  `OldTrees` varchar(10) DEFAULT NULL,
  `BigTrees` varchar(10) DEFAULT NULL,
  `Extra1` varchar(80) DEFAULT NULL,
  `Extra2` varchar(80) DEFAULT NULL,
  `Extra3` varchar(80) DEFAULT NULL,
  `Extra4` varchar(80) DEFAULT NULL,
  `Extra5` varchar(80) DEFAULT NULL,
  `DBHID` int(11) DEFAULT NULL,
  PRIMARY KEY (`TempID`),
  KEY `indexTagStemTag` (`Tag`,`StemTag`),
  KEY `indexTreeIDStemID` (`TreeID`,`StemID`),
  KEY `indexQuadratNamePlotID` (`QuadratName`,`PlotID`),
  KEY `indexMnemonic` (`Mnemonic`)
) ENGINE=InnoDB AUTO_INCREMENT=6635 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `TempOldTrees`
--

LOCK TABLES `TempOldTrees` WRITE;
/*!40000 ALTER TABLE `TempOldTrees` DISABLE KEYS */;
INSERT INTO `TempOldTrees` VALUES (6117,492702,'27545',1026544,'',NULL,'0000',13,NULL,'protte',NULL,114,349,11,NULL,NULL,NULL,156,151,1.3,1.3,'','','','2019-08-27','',NULL,'n',NULL,NULL,NULL,'\r',NULL,NULL),(6118,492703,'27546',1026545,'',NULL,'0000',14,NULL,'soroaf',NULL,114,349,11,NULL,NULL,NULL,101,103,1.3,1.3,'','','','2019-08-27','',NULL,'n',NULL,NULL,NULL,'\r',NULL,NULL)

[siddheshraze]

Here is an updated procedure to run this and mark its errors in the CMVErrors table! Function's been reviewed with GPT so it should work as intended, but full testing is still pending:

create
    definer = azureroot@`%` procedure ValidateDBHGrowthExceedsMax()
begin
    INSERT INTO cmverrors (CoreMeasurementID, ValidationErrorID)
    SELECT
        cm2.CoreMeasurementID,
        14 as ValidationErrorID
    FROM forestgeo_bci.coremeasurements cm1
             JOIN
         forestgeo_bci.coremeasurements cm2 ON cm1.StemID = cm2.StemID
             AND cm1.TreeID = cm2.TreeID
             AND YEAR(cm2.MeasurementDate) = YEAR(cm1.MeasurementDate) + 1
             LEFT JOIN
         forestgeo_bci.cmattributes cma ON cm1.CoreMeasurementID = cma.CoreMeasurementID
             LEFT JOIN
         forestgeo_bci.attributes a ON cma.Code = a.Code
    WHERE (a.Status NOT IN ('dead', 'stem dead', 'broken below', 'missing', 'omitted') OR a.Status IS NULL)
      AND cm1.MeasuredDBH IS NOT NULL
      AND cm2.MeasuredDBH IS NOT NULL
      AND (cm2.MeasuredDBH - cm1.MeasuredDBH > 65);
end;

create
    definer = azureroot@`%` procedure ValidateDBHShrinkageExceedsMax()
begin
    INSERT INTO cmverrors (CoreMeasurementID, ValidationErrorID) 
    SELECT cm2.CoreMeasurementID,
           13 as ValidationErrorID
    FROM forestgeo_bci.coremeasurements cm1
             JOIN
         forestgeo_bci.coremeasurements cm2 ON cm1.StemID = cm2.StemID
             AND cm1.TreeID = cm2.TreeID
             AND YEAR(cm2.MeasurementDate) = YEAR(cm1.MeasurementDate) + 1
             LEFT JOIN
         forestgeo_bci.cmattributes cma ON cm1.CoreMeasurementID = cma.CoreMeasurementID
             LEFT JOIN
         forestgeo_bci.attributes a ON cma.Code = a.Code
    WHERE (a.Status NOT IN ('dead', 'stem dead', 'broken below', 'missing', 'omitted') OR a.Status IS NULL)
      AND cm1.MeasuredDBH IS NOT NULL
      AND cm2.MeasuredDBH IS NOT NULL
      AND (cm2.MeasuredDBH < cm1.MeasuredDBH * 0.95);
end;