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

checkQuadrat	Check for quadrats not matching another file, i.e. tempNewPlants. Is there an easier way??? Remember that there may be more than one record with the same tag in tempNewPlants "SELECT DISTINCT Tag, QuadratName FROM ".$fileout." WHERE PlotID=".$plotID	Cloud	Looks for quadrats that don’t match those available at a given site

    $filein     $tempTableName = 'TempLocations';
    $fileout    $tempNewTable = 'TempNewPlants';

    public function checkQuadrat ($filein, $fileout, $plotID)
    {
      //Check for quadrats not matching another file, i.e. tempNewPlants
      //Is there an easier way??? Remember that there may be more than one record with the same Tag in tempNewPlants

      $q1 = "SELECT Tag, QuadratName FROM ".$fileout." WHERE PlotID=".$plotID." GROUP BY Tag";
      $runQ1 = $this->screeningdb->query($q1);
      if ($runQ1->num_rows() > 0)
      {
        foreach($runQ1->result() as $row)
        {
           $q2 = 'UPDATE '.$filein.' SET Errors = CONCAT(TRIM(Errors),";Tree is in quadrat='.$row->QuadratName.' in '.$fileout.'") WHERE Tag = "'.$row->Tag.'" and QuadratName <> "'.$row->QuadratName.'"  AND Errors <> "NONE" AND Errors <> "" AND Errors IS NOT NULL';
           $q3 = 'UPDATE '.$filein.' SET Errors = "Tree is in quadrat='.$row->QuadratName.' in '.$fileout.'" WHERE Tag ="'.$row->Tag.'" AND QuadratName<>"'.$row->QuadratName.'" AND Errors <> "NONE" AND (Errors = "" OR ISNULL(Errors))';
           $runQ2 = $this->screeningdb->query($q2);
           $runQ3 = $this->screeningdb->query($q3);
        }
      }
    }

DROP TABLE IF EXISTS `TempLocations`;
CREATE TABLE `TempLocations` (
  `TempID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `QuadratName` varchar(12) DEFAULT NULL,
  `Tag` varchar(10) DEFAULT NULL,
  `StemTag` varchar(32) DEFAULT NULL,
  `X` float DEFAULT NULL,
  `Y` float DEFAULT NULL,
  `Comments` varchar(256) DEFAULT NULL,
  `TreeID` int(10) unsigned DEFAULT NULL,
  `PlotCensusNumber` int(10) unsigned DEFAULT NULL,
  `CensusID` int(10) unsigned DEFAULT NULL,
  `PlotID` int(10) unsigned DEFAULT NULL,
  `Errors` varchar(256) DEFAULT NULL,
  `OldTrees` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`TempID`),
  KEY `indexTag` (`Tag`),
  KEY `indexSTemTag` (`Tag`,`StemTag`),
  KEY `indexTreeID` (`TreeID`),
  KEY `indexQuadratName` (`QuadratName`)
) ENGINE=InnoDB AUTO_INCREMENT=299 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

DROP TABLE IF EXISTS `TempNewPlants`;
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;

[siddheshraze]

Here is an updated procedure to run this! Function's been reviewed with GPT so it should work as intended, but full testing is still pending:

create
    definer = azureroot@`%` procedure ValidateCheckIfQuadratByNameExistsInPlot(IN quadratName text, IN plotID int)
BEGIN
    SELECT EXISTS (
        SELECT 1
        FROM forestgeo_bci.quadrats
        WHERE QuadratName = quadratName AND PlotID = plotID
    ) AS QuadratExists;
END;