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

screenDuplSpec	This public function checks to see if all the stems of a tree have the same species code	Local	All stems of a tree should have the same species code.

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

    public function  screenDuplSpec ($fileName)
    {
      //This public function checks to see if all the stems of a tree have the same species code
      //$q1 = "SELECT Tag,count(Tag) AS cnt FROM (SELECT DISTINCT Tag, Mnemonic, Errors FROM ".$fileName." GROUP BY Tag,Mnemonic,Errors UNION SELECT DISTINCT Tag, Mnemonic, Errors FROM ".$fileName."Error GROUP BY Tag,Mnemonic,Errors) AS t2 WHERE Errors<>'NONE' GROUP BY Tag HAVING cnt>1";
      $q1 = "SELECT Tag,count(Tag) AS cnt FROM (SELECT DISTINCT Tag, Mnemonic FROM ".$fileName." WHERE Errors<>'NONE' UNION SELECT DISTINCT Tag, Mnemonic FROM ".$fileName."Error WHERE Errors<>'NONE') AS t2 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 Species") WHERE Tag = "'.$row->Tag.'" AND Errors <> "" AND Errors IS NOT NULL';
            $q3 = 'UPDATE '.$fileName.' SET Errors = ">1 Species" WHERE Tag ="'.$row->Tag.'" AND (Errors = "" OR ISNULL(Errors))';
            $runQ2 = $this->screeningdb->query($q2);
            $runQ3 = $this->screeningdb->query($q3);
         }
      }
    }

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 `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;
/*!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 ValidateFindStemsInTreeWithDifferentSpecies()
BEGIN
    INSERT INTO forestgeo_bci.cmverrors (CoreMeasurementID, ValidationErrorID)
    SELECT
        cm.CoreMeasurementID,
        7 AS ValidationErrorID
    FROM
        forestgeo_bci.stems s
    JOIN
        forestgeo_bci.trees t ON s.TreeID = t.TreeID
    JOIN
        forestgeo_bci.species sp ON t.SpeciesID = sp.SpeciesID
    JOIN
        forestgeo_bci.coremeasurements cm ON s.StemID = cm.StemID
    GROUP BY
        t.TreeID, cm.CoreMeasurementID
    HAVING
        COUNT(DISTINCT sp.SpeciesCode) > 1;
END;