screenDuplQuad 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.

screenDuplQuad	This public function checks to see if all the stems of a tree have the same quadrat	Cloud	All stems of a tree should be located within the same quadrat.

          $tempTableName = 'TempNewPlants';
          $tempTableName = 'TempOldTrees';
          $tempTableName = 'TempMultiStems';


    public function  screenDuplQuad ($fileName)
    {
      //This public function checks to see if all the stems of a tree have the same quadrat
      $q1 = "SELECT Tag,COUNT(Tag) AS cnt FROM (SELECT DISTINCT Tag, QuadratName, Errors FROM ".$fileName." GROUP BY Tag,QuadratName UNION SELECT DISTINCT Tag, QuadratName, Errors FROM ".$fileName."Error GROUP BY Tag, QuadratName) AS t2 WHERE Errors<>'NONE' GROUP BY Tag HAVING cnt>1";
      $runQ1 = $this->screeningdb->query($q1);

      if ($runQ1->num_rows() > 0)
      {
         foreach($runQ1->result() as $row)
         {
            $q2 = 'UPDATE '.$fileName.' SET Errors = CONCAT(TRIM(Errors),";>1 Quadrat") WHERE Tag = "'.$row->Tag.'" AND Errors <> "" AND Errors IS NOT NULL';
            $q3 = 'UPDATE '.$fileName.' SET Errors = ">1 Quadrat" WHERE Tag ="'.$row->Tag.'" AND (Errors = "" OR ISNULL(Errors))';
            $runQ3 = $this->screeningdb->query($q2);
            $runQ4 = $this->screeningdb->query($q3);
         }
      }
    }

DROP TABLE IF EXISTS `TempNewPlants`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `TempNewPlants` (
  `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=155 DEFAULT CHARSET=latin1;

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 */;


DROP TABLE IF EXISTS `TempMultiStems`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `TempMultiStems` (
  `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=5115 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

[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 ValidateFindTreeStemsInDifferentQuadrats()
BEGIN
    INSERT INTO forestgeo_bci.cmverrors (CoreMeasurementID, ValidationErrorID)
    SELECT
        cm1.CoreMeasurementID,
        5 AS ValidationErrorID
    FROM
        forestgeo_bci.stems s1
    JOIN
        forestgeo_bci.stems s2 ON s1.TreeID = s2.TreeID AND s1.StemID != s2.StemID
    JOIN
        forestgeo_bci.coremeasurements cm1 ON s1.StemID = cm1.StemID
    WHERE
        s1.QuadratID != s2.QuadratID
    GROUP BY 
        cm1.CoreMeasurementID;
END;