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

screenDuplQuadratInfo	Checks for duplicated quadrats	Local	Makes sure no quadrats (20 x 20 meter subsection of plot) are repeated

    public function screenDuplQuadratInfo ()
    {
      $q1 = "SELECT QuadratName,PersonnelID,RoleID,DateWorked,Form,COUNT(QuadratName) AS cnt FROM TempPersonnelInfo WHERE Errors<>'NONE' GROUP BY QuadratName,PersonnelID,RoleID,DateWorked,Form HAVING cnt > 1";
      $query1 = $this->screeningdb->query($q1);
      if ($query1->num_rows() > 0)
      {
        foreach($query1->result() as $row)
        {
          $q2 = 'UPDATE TempPersonnelInfo SET Errors = CONCAT(TRIM(Errors),";Duplicate Record") WHERE QuadratName = "'.$row->QuadratName.'" AND PersonnelID='.$row->PersonnelID.' AND RoleID='.$row->RoleID.' AND DateWorked="'.$row->DateWorked.'" and Form="'.$row->Form.'" and Errors <> "" AND Errors IS NOT NULL';
          $q3 = 'UPDATE TempPersonnelInfo SET Errors = "Duplicate Record" WHERE QuadratName = "'.$row->QuadratName.'" AND PersonnelID='.$row->PersonnelID.' AND RoleID='.$row->RoleID.' AND DateWorked="'.$row->DateWorked.'" and Form="'.$row->Form.'" AND (Errors = "" OR ISNULL(Errors))';
          $runQ2 = $this->screeningdb->query($q2);
          $runQ3 = $this->screeningdb->query($q3);
        }
      }
    }

DROP TABLE IF EXISTS `TempPersonnelInfo`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `TempPersonnelInfo` (
  `TempID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `QuadratName` varchar(12) DEFAULT NULL,
  `QuadratID` int(10) unsigned DEFAULT NULL,
  `PersonnelID` smallint(5) unsigned DEFAULT NULL,
  `Form` varchar(32) DEFAULT NULL,
  `PlotID` int(10) unsigned DEFAULT NULL,
  `CensusID` int(10) unsigned DEFAULT NULL,
  `PlotCensusNumber` int(10) unsigned DEFAULT NULL,
  `Role` varchar(128) DEFAULT NULL,
  `RoleID` smallint(5) unsigned DEFAULT NULL,
  `DateWorked` date DEFAULT NULL,
  `Errors` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`TempID`),
  KEY `indexQuadratName` (`QuadratName`)
) ENGINE=InnoDB 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 ValidateFindDuplicatedQuadratsByName()
BEGIN
    INSERT INTO forestgeo_bci.cmverrors (CoreMeasurementID, ValidationErrorID)
    SELECT
        cm.CoreMeasurementID,
        11 AS ValidationErrorID
    FROM
        forestgeo_bci.quadrats q
    JOIN
        forestgeo_bci.coremeasurements cm ON q.QuadratID = cm.QuadratID
    WHERE
        (q.PlotID, q.QuadratName) IN (
            SELECT
                PlotID,
                QuadratName
            FROM
                forestgeo_bci.quadrats
            GROUP BY
                PlotID, QuadratName
            HAVING
                COUNT(*) > 1
        )
    GROUP BY 
        cm.CoreMeasurementID;
END;