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

screenQuadratInfo	Check for date ranges SELECT TempID FROM TempPersonnelInfo WHERE DateWorked NOT BETWEEN '".$start."' AND '".$end."' AND Errors<>'NONE'	Local	Makes sure all dates recorded in data collection, by quadrat, are within range of census. Appears to be related to startCensusDate.

    public function censusStartDate($plotID,$censusNumber)
    {
      $this->screeningdb->select('StartDate');
      $this->screeningdb->where('PlotID',$plotID);
      $this->screeningdb->where('PlotCensusNumber',$censusNumber);
      $query = $this->screeningdb->get('Census');
      if($query->num_rows() > 0)
      {
        $row = $query->row();
        $startDate = $row->StartDate;
      }
      else
      {
        echo "Error in screeningmodel/censusStartDate";
        die();
      }
      return $startDate;
    }


          $startDate = $this->screeningmodel->censusStartDate($plotID,$censusNumber);
          $enddate = date("Y-m-d");

    public function screenQuadratInfo ($start,$end)
    {
      //Check for date ranges
      $q1 = "SELECT TempID FROM TempPersonnelInfo WHERE DateWorked NOT BETWEEN '".$start."' AND '".$end."' AND Errors<>'NONE'";
      $query1 = $this->screeningdb->query($q1);
      if ($query1->num_rows() > 0)
      {
        foreach($query1->result() as $row)
        {
          $q2 = 'UPDATE TempPersonnelInfo SET Errors = CONCAT(TRIM(Errors),";Check Date") WHERE TempID = '.$row->TempID.' AND Errors <> "" AND Errors IS NOT NULL';
          $q3 = 'UPDATE TempPersonnelInfo SET Errors = "Check Date" WHERE tempID = '.$row->TempID.' AND (Errors = "" OR ISNULL(Errors))';
        }
      }
    }

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;

[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 ValidateFindMeasurementsOutsideCensusDateBoundsGroupByQuadrat()
begin
    INSERT INTO forestgeo_bci.cmverrors (CoreMeasurementID, ValidationErrorID)
    SELECT 
        cm.CoreMeasurementID,
        8 as ValidationErrorID
    FROM 
        forestgeo_bci.coremeasurements cm
    JOIN 
        forestgeo_bci.quadrats q ON cm.QuadratID = q.QuadratID
    JOIN 
        forestgeo_bci.census c ON q.PlotID = c.PlotID
    WHERE 
        (cm.MeasurementDate < c.StartDate OR cm.MeasurementDate > c.EndDate)
        AND cm.MeasurementDate IS NOT NULL
    GROUP BY 
        q.QuadratID, c.CensusID, c.StartDate, c.EndDate;

end;