$tempTableName = 'TempOldTrees';
//Check for extreme growth - for Old Trees form
//Tags stems where absolute annual growth > 65 mm
$annualGrowth = '65';
//Check for shrinkage in recensuses - only for Old Trees form
//Tags records with annual relative growth <= -0.05 in stems with previous dbh>=50mm and no current stem lost code
$dbhCheck2 = '50';
$annualShrink = '-0.05';
////Helper to above////
public function _getCodes($description)
{
$this->screeningdb->select('TSMCode');
$this->screeningdb->like('Description',$description);
$query = $this->screeningdb->get('TSMAttributes');
$codesarr = array();
if($query->num_rows() > 0)
{
$j = 0;
foreach($query->result() as $row)
$codesarr[$j++] = $row->TSMCode;
}
else
{
$codesarr[0] = 'none';
}
return $codesarr;
}
//////Old Trees screening/////////
public function checkGrowth ($filen,$minDBH,$annualGrowth,$dbhCheck2,$annualShrink)
{
//Check for extreme growth - for Old Trees form
//Tags stems where absolute annual growth > $annualGrowth mm
$q1 = 'UPDATE '.$filen.' AS a,TempQuadratDates AS b SET Errors = CONCAT(TRIM(Errors),";DBH too large?") WHERE a.QuadratName=b.QuadratName AND HOM=OldHOM AND
Errors <> "" AND Errors IS NOT NULL AND Errors<>"NONE" AND DBH>='.$minDBH.' AND OldDBH>='.$minDBH.' AND (DBH-OldDBH)/((to_days(ExactDate)-to_days(b.PrevDate))/365.25) > '.$annualGrowth;
$q2 = 'UPDATE '.$filen.' AS a,TempQuadratDates AS b SET Errors = "DBH too large?" WHERE a.QuadratName=b.QuadratName AND HOM=OldHOM AND
(Errors = "" OR ISNULL(Errors)) AND Errors<>"NONE"AND DBH>='.$minDBH.' AND OldDBH>='.$minDBH.' AND (DBH-OldDBH)/((to_days(ExactDate)-to_days(b.PrevDate))/365.25) > '.$annualGrowth;
$runQ1 = $this->screeningdb->query($q1);
$runQ2 = $this->screeningdb->query($q2);
//Check for shrinkage in recensuses - only for Old Trees form
//Tags records with annual relative growth <= -0.05 in stems with previous dbh>=50mm and no current stem lost code
$resprcodes = $this->_getCodes('stem lost'); //The helper public function above
//$q1 = "SELECT TSMCode FROM TSMAttributes WHERE LOWER(DESCRIPTION) like '%resprout%' or LOWER(DESCRIPTION) like '%broken%'";
//$q1 = "SELECT TSMCode FROM TSMAttributes WHERE LOWER(DESCRIPTION)='stem lost'";
//$r1 = mysql_query($q1, $connect) or die('Selecting stem lost codes failed: ' . mysql_error());
//$resprcodes=array();
//if (mysql_num_rows($r1)> 0) {
// $j=0;
// while ($row = mysql_fetch_assoc($r1)) {
// $resprcodes[$j++] = $row["TSMCode"]; }
// }
$q2 = "Select TempID FROM ".$filen;
$queryQ2 = $this->screeningdb->query($q2);
// Go through entire file
foreach($queryQ2->result() as $rowQ2)
{
//Check whether tree has resprout or broken code, in which case do not run shrinkage test
$tempID = $rowQ2->TempID;
$q3 = "SELECT Codes FROM ".$filen." WHERE TempID = ".$tempID;
$queryQ3 = $this->screeningdb->query($q3);
$rowQ3 = $queryQ3->row();
$codes = $rowQ3->Codes;
$continue = 0;
if (!is_null($codes) AND $codes<>'*' AND $codes<>'')
{
$nocodes = SUBSTR_COUNT($codes,';');
$arrcodes = explode(";", $codes);
for ($i = 0; $i <= $nocodes; $i++)
{
if (in_array($arrcodes[$i], $resprcodes))
{
$continue=1;
}
}
}
if($continue==0)
{
$q4 = 'UPDATE '.$filen.' AS a,TempQuadratDates AS b SET Errors = CONCAT(TRIM(Errors),";DBH shrank too much") WHERE a.QuadratName=b.QuadratName AND HOM=OldHom
AND Errors <> "" AND Errors IS NOT NULL AND Errors<>"NONE" AND DBH>='.$minDBH.' AND olddbh>='.$dbhCheck2.' AND ((DBH-OldDBH)/OldDBH)/((to_days(ExactDate)-to_days(b.PrevDate))/365.25) <='.$annualShrink.'
AND TempID = '.$tempID;
$q5 = 'UPDATE '.$filen.' AS a, TempQuadratDates AS b SET Errors = "DBH shrank too much?" WHERE a.QuadratName=b.QuadratName AND HOM=OldHOM
AND (Errors = "" OR ISNULL(Errors)) AND Errors<>"NONE" AND DBH>='.$minDBH.' AND olddbh>='.$dbhCheck2.' AND ((DBH-OldDBH)/OldDBH)/((to_days(ExactDate)-to_days(b.PrevDate))/365.25) <='.$annualShrink.'
AND tempID = '.$tempID;
$runQ4 = $this->screeningdb->query($q4);
$runQ5 = $this->screeningdb->query($q5);
}
}
}
DROP TABLE IF EXISTS `TSMAttributes`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `TSMAttributes` (
`TSMID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`TSMCode` char(10) NOT NULL,
`Description` varchar(128) NOT NULL,
`Status` enum('alive','alive-not measured','dead','missing','broken below','stem dead') DEFAULT NULL,
PRIMARY KEY (`TSMID`)
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=latin1;
(1,'F','Fallen or missing (1982)','alive'),(2,'L','Leaning','alive')
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 */;
--
-- Dumping data for table `TempOldTrees`
--
LOCK TABLES `TempOldTrees` WRITE;
/*!40000 ALTER TABLE `TempOldTrees` DISABLE KEYS */;
INSERT INTO `TempOldTrees` VALUES (6117,492702,'27545',1026544,'',NULL,'0000',13,NULL,'protte',NULL,114,349,11,NULL,NULL,NULL,156,151,1.3,1.3,'','','','2019-08-27','',NULL,'n',NULL,NULL,NULL,'\r',NULL,NULL),(6118,492703,'27546',1026545,'',NULL,'0000',14,NULL,'soroaf',NULL,114,349,11,NULL,NULL,NULL,101,103,1.3,1.3,'','','','2019-08-27','',NULL,'n',NULL,NULL,NULL,'\r',NULL,NULL)