smithsonian / forestgeo Goto Github PK

    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);
        }
      }
    }

    public function checkDuplTagout ($filein, $fileout, $plotid)
    {
        //Check for duplicate Tree tag in another database
        if ($fileout == 'ViewFullTable')
        {
          $q1 = "SELECT TempID,Tag FROM ".$filein." WHERE Tag IN (SELECT DISTINCT TAG FROM ".$fileout." WHERE PlotID=".$plotid.") AND Errors<>'NONE'";
        }
        else
        {
          $q1 = "SELECT TempID,Tag FROM ".$filein." WHERE Tag IN (SELECT DISTINCT TAG FROM ".$fileout." WHERE PlotID=".$plotid." UNION SELECT DISTINCT TAG FROM ".$fileout."Error WHERE PlotID=".$plotid.") AND Errors<>'NONE'";
        }

        $runQuery1 = $this->screeningdb->query($q1);

        if ($runQuery1->num_rows() > 0)
        {
           foreach($runQuery1->result() as $row)
           {
              $q2 = 'UPDATE '.$filein.' SET Errors = CONCAT(TRIM(Errors),";Tag exists in '.$fileout.' table") WHERE TempID = '.$row->TempID.' AND Errors <> "" AND Errors IS NOT NULL';
              $q3 = 'UPDATE '.$filein.' SET Errors = "Tag exists in '.$fileout.' table" WHERE TempID ='.$row->TempID.' AND (Errors = "" OR ISNULL(Errors))';
              $runQuery2 = $this->screeningdb->query($q2);
              $runQuery3 = $this->screeningdb->query($q3);
           }
        }
    }

    public function screenMainStems ($fileName)
    {
      //Assumes there are main stem codes
      //Check if there is more than one main stem, if there is none, if main stem died, etc. for multiple-stemmed trees


      // Get all the codes
      $codetmp = $this->_getCodes('main');
      $maincode = $codetmp[0];   //Should be one main stem code only

      $deadarr = $this->_getCodes('dead');
      $resprarr = $this->_getCodes('stem lost');

      // Get all the multiple-stemmed trees
      $q1 = "SELECT Tag, COUNT(Tag) as stems FROM ".$fileName." WHERE Errors<>'NONE' GROUP BY Tag HAVING stems>1";
      $runQ1 = $this->screeningdb->query($q1);

      foreach ($runQ1->result() as $q1Row)
      {

         $nummain = 0;
         $largestdbh = 0;
         $largestdbhid = 0;
         $numalive = 0;
         $maintempid = 0;
         $deadtreeid = 0;
         $mainst = 'n';
         $deadst = 'n';
         $resprst = 'n';
         $maindead = 'n';


         $q2 = "SELECT TempID, Codes, DBH, Errors FROM ".$fileName." WHERE Tag = '".$q1Row->Tag."' AND Errors<>'NONE'";
         $runQ2 = $this->screeningdb->query($q2);

         foreach($runQ2->result() as $q2Row)
         {
           if ($q2Row->DBH > $largestdbh)
           {
             $largestdbh = $q2Row->DBH;
             $largestdbhid = $q2Row->TempID;
           }

           $checkcodes = trim($q2Row->Codes);
           // Check codes

           if (!is_null($checkcodes) AND $checkcodes<>'*' AND $checkcodes<>'')
           {
             $nocodes = SUBSTR_COUNT($checkcodes,';');
             $arrcodes = explode(";", $checkcodes);
             for ($i = 0; $i <= $nocodes; $i++)
             {
                if (in_array($arrcodes[$i],$deadarr))
                {
                  $deadst = 'y';
                  $deadtreeid = $q2Row->TempID;
                }

                if (in_array($arrcodes[$i],$resprarr))
                {
                  $resprst = 'y';
                }

                if ($arrcodes[$i] === $maincode)
                {
                   $mainst = 'y';
                   $nummain++ ;
                   $maintempid = $q2Row->TempID;
                   // Change to this tempID even if dbh is the same, since this is main stem
                   if ($q2Row->DBH == $largestdbh)
                   $largestdbhid = $q2Row->TempID;
                 }  // end if main code
              }  //end for
            }  //end if there are codes

            if ($mainst === 'y' AND $deadst === 'y' and $deadtreeid == $maintempid)
            {
              $maindead = 'y';
            }
            if ($q2Row->DBH >= 10 || $resprst === 'y')
            {
              $numalive++;
            }
      }  // End for looking at stems of one tag

       // Screen for more than one main stem
       if ($nummain > 1 )
       {
           $q3 = 'UPDATE '.$fileName.' SET Errors = CONCAT(TRIM(Errors),";More than 1 main stem") WHERE Tag = "'.$q1Row->Tag.'" and codes like "%'.$maincode.'%"';
           $runQ3 = $this->screeningdb->query($q3);
           if ($largestdbhid > 0)
           {
             $q4 = 'UPDATE '.$fileName.' SET Errors = CONCAT(TRIM(Errors),";Main stem?") WHERE TempID = '.$largestdbhid.' and Errors not like "%Main stem?%"';
             $runQ4 = $this->screeningdb->query($q4);
           }
        }

        // Screen if no main stem
        if ($nummain == 0 )
        {
           $q3 = 'UPDATE '.$fileName.' SET Errors = CONCAT(TRIM(Errors),";No main stem code") WHERE Tag = "'.$q1Row->Tag.'"';
           $runQ3 = $this->screeningdb->query($q3);
           if ($largestdbhid > 0)
           {
              $q4 = 'UPDATE '.$fileName.' SET Errors = CONCAT(TRIM(Errors),";Main stem?") WHERE TempID = '.$largestdbhid.' and Errors not like "%Main stem?%"';
              $runQ4 = $this->screeningdb->query($q4);
           }
        }

        // Screen if main stem died and there are other live stems
        if ($maindead === 'y' && $numalive > 0 )
        {
           $q3 = 'UPDATE '.$fileName.' SET Errors = CONCAT(TRIM(Errors),";Main stem is dead") WHERE TempID = '.$deadtreeid;
           $runQ3 = $this->screeningdb->query($q3);
           if ($largestdbhid > 0)
           {
             $q4 = 'UPDATE '.$fileName.' SET Errors = CONCAT(TRIM(Errors),";Main stem?") WHERE TempID = '.$largestdbhid .' and Errors not like "%Main stem?%"';
             $runQ4 = $this->screeningdb->query($q4);
           }
         }

         // Screen if main stem does not have largest dbh
         if ($maintempid <> $largestdbhid AND $maintempid>0 AND $largestdbhid>0)
         {
            $q3 = 'UPDATE '.$fileName.' SET Errors = CONCAT(TRIM(Errors),";Another stem has larger dbh?") WHERE TempID = '.$maintempid;
            $runQ3 = $this->screeningdb->query($q3);

            $q4 = 'UPDATE '.$fileName.' SET Errors = CONCAT(TRIM(Errors),";Main stem?") WHERE TempID = '.$largestdbhid.' and Errors not like "%Main stem?%"';
            $runQ4 = $this->screeningdb->query($q4);
          }

      }  // End foreach

      $q3 = 'UPDATE '.$fileName.' SET Errors = substring(Errors,2) WHERE substring(Errors,1,1)=";"';
      $runQ3 = $this->screeningdb->query($q3);
    }



    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;
    }




    public function screenMainStems2 ($file1, $file2)
    {
      //Check main stems for multiple-stemmed trees in 2 files: Old Trees and Multiple Stems

      // Get all the codes
      $codetmp = $this->_getCodes('main');
      $maincode = $codetmp[0];   //Should be one main stem code only

      $deadarr = $this->_getCodes('dead');
      $resprarr = $this->_getCodes('stem lost');

      $tagarray1 = array();
      // Get all the multiple-stemmed trees in first file
      $q1 = "SELECT Tag, COUNT(Tag) AS stems FROM ".$file1." WHERE Errors<>'NONE' GROUP BY Tag HAVING stems>1";
      $queryQ1 = $this->screeningdb->query($q1);

      if ($queryQ1->num_rows() > 0)
      {
        $i = 0;
        foreach($queryQ1->result() as $rowQ1)
        {
          $tagarray1[$i++] = $rowQ1->Tag;
        }
      }

      // Get all the tree tags from multiple-stemmed file
      $q2 = "SELECT DISTINCT Tag FROM ".$file2." WHERE Errors<>'NONE'";
      $queryQ2 = $this->screeningdb->query($q2);
      if ($queryQ2->num_rows() > 0)
      {
        $i = 0;
        foreach($queryQ2->result() as $rowQ2)
        {
          $tagarray2[$i++] = $rowQ2->Tag;
        }
        $multistems = 'y';
        $tagarray = array_unique(array_merge($tagarray1,$tagarray2));
      }
      else
      {
        $multistems = 'n';
        $tagarray = $tagarray1;
      }

      foreach ($tagarray as $key => $val)
      {
        $nummain = 0;
        $largestdbh = 0;
        $largestdbhid = 0;
        $numalive = 0;
        $maintempid = 0;
        $deadtreeid = 0;
        $mainst = 'n';
        $deadst = 'n';
        $resprst = 'n';
        $maindead = 'n';

        unset($treearr);

        $q3 = "SELECT TempID, Codes, DBH, Errors FROM ".$file1." WHERE Tag = '".$val."' AND Errors<>'NONE'";
        $queryQ3 = $this->screeningdb->query($q3);
        $i = 0;
        foreach($queryQ3->result() as $rowQ3)
        {
          $treearr['File'][$i] = $file1;
          $treearr['tempID'][$i] = $rowQ3->TempID;
          $treearr['codes'][$i] = $rowQ3->Codes;
          $treearr['DBH'][$i] = $rowQ3->DBH;
          $treearr['Errors'][$i] = $rowQ3->Errors;
          $i++;
        }

        if ($multistems == 'y')
        {
          $q4 = "SELECT TempID, Codes, DBH, Errors FROM ".$file2." WHERE Tag = '".$val."' AND Errors<>'NONE'";
          $queryQ4 = $this->screeningdb->query($q4);
          foreach($queryQ4->result() as $rowQ4)
          {
            $treearr['File'][$i] = $file2;
            $treearr['tempID'][$i] = $rowQ4->TempID;
            $treearr['codes'][$i] = $rowQ4->Codes;
            $treearr['DBH'][$i] = $rowQ4->DBH;
            $treearr['Errors'][$i] = $rowQ4->Errors;
            $i++;
          }
        }

        $numstems = sizeof($treearr['tempID']);

        for ($i = 0; $i < $numstems; $i++)
        {
          if ($treearr["DBH"][$i] > $largestdbh)
          {
            $largestdbh = $treearr["DBH"][$i];
            $largestdbhid = $treearr["tempID"][$i];
            $largestdbhfile = $treearr["File"][$i];
          }

          $checkcodes = trim($treearr["codes"][$i]);

          // Check codes
          if (!is_null($checkcodes) AND $checkcodes<>'*' AND $checkcodes<>'')
          {
            $nocodes = SUBSTR_COUNT($checkcodes,';');
            $arrcodes = explode(";", $checkcodes);
            for ($j = 0; $j <= $nocodes; $j++)
            {
              if (in_array($arrcodes[$j],$deadarr))
              {
                $deadst = 'y';
                $deadtreeid = $treearr["tempID"][$i];
                $deadtreefile = $treearr["File"][$i];
              }

              if (in_array($arrcodes[$j],$resprarr))
              {
                $resprst = 'y';
              }
              if ($arrcodes[$j] === $maincode)
              {
                $mainst = 'y';
                $nummain++ ;
                $maintempid = $treearr["tempID"][$i];
                $mainfile = $treearr["File"][$i];
                // Change to this tempID even if dbh is the same, since this is main stem
                if ($treearr["DBH"][$i] == $largestdbh)
                {
                  $largestdbhid = $treearr["tempID"][$i];
                  $largestdbhfile = $treearr["File"][$i];
                }
              }  // end if main code

            }  //end for

          }  //end if there are codes


          if ($mainst === 'y' AND $deadst === 'y' and $deadtreeid == $maintempid)
          {
            $maindead = 'y';
          }
          if ($treearr["DBH"][$i] >= 10 || $resprst === 'y')
          {
            $numalive++;
          }
        }  // End while looking at stems of one tag


        // Screen for more than one main stem
        if ($nummain > 1 )
        {
          $q2 = 'UPDATE '.$file1.' SET Errors = CONCAT(TRIM(Errors),";More than 1 main stem code") WHERE Tag = "'.$val.'" and Codes like "%'.$maincode.'%"';
          $runQ2 = $this->screeningdb->query($q2);

          $q3 = 'UPDATE '.$file2.' SET Errors = CONCAT(TRIM(Errors),";More than 1 main stem code") WHERE Tag = "'.$val.'" and Codes like "%'.$maincode.'%"';
          $runQ3 = $this->screeningdb->query($q3);

          if ($largestdbhid > 0)
          {
            $q4 = 'UPDATE '.$largestdbhfile.' SET Errors = CONCAT(TRIM(Errors),";Main stem?") WHERE TempID = '.$largestdbhid.' and Errors not like "%Main stem?%"';
            $runQ4 = $this->screeningdb->query($q4);
          }
        }

        // Screen if no main stem
        if ($nummain == 0 )
        {
          $q2 = 'UPDATE '.$file1.' SET Errors = CONCAT(TRIM(Errors),";No main stem code") WHERE Tag = "'.$val.'"';
          $runQ2 = $this->screeningdb->query($q2);

          $q3 = 'UPDATE '.$file2.' SET Errors = CONCAT(TRIM(Errors),";No main stem code") WHERE Tag = "'.$val.'"';
          $runQ3 = $this->screeningdb->query($q3);

          if ($largestdbhid > 0)
          {
            $q4 = 'UPDATE '.$largestdbhfile.' SET Errors = CONCAT(TRIM(Errors),";Main stem?") WHERE TempID = '.$largestdbhid.' and Errors not like "%Main stem?%"';
            $runQ4 = $this->screeningdb->query($q4);
          }
        }

        // Screen if main stem died and there are other live stems
        if ($maindead === 'y' && $numalive > 0 )
        {
          $q2 = 'UPDATE '.$deadtreefile.' SET Errors = CONCAT(TRIM(Errors),";Main stem is dead") WHERE TempID = '.$deadtreeid;
          $runQ2 = $this->screeningdb->query($q2);
          if ($largestdbhid > 0)
          {
            $q4 = 'UPDATE '.$largestdbhfile.' SET Errors = CONCAT(TRIM(Errors),";Main stem?") WHERE TempID = '.$largestdbhid .' and Errors not like "%Main stem?%"';
            $runQ4 = $this->screeningdb->query($q4);
          }
        }


        // Screen if main stem does not have largest dbh
        if ($maintempid <> $largestdbhid AND $maintempid>0 AND $largestdbhid>0)
        {
          $q2 = 'UPDATE '.$mainfile.' SET Errors = CONCAT(TRIM(Errors),";Another stem has larger dbh?") WHERE TempID = '.$maintempid;

          $q4 = 'UPDATE '.$largestdbhfile.' SET Errors = CONCAT(TRIM(Errors),";Main stem?") WHERE TempID = '.$largestdbhid.' and Errors not like "%Main stem?%"';
        }
      }  // End foreach ($tagarray as $key => $val)

      $q2 = 'UPDATE '.$file1.' SET Errors = substring(Errors,2) WHERE substring(Errors,1,1)=";"';
      $runQ2 = $this->screeningdb->query($q2);

      $q3 = 'UPDATE '.$file2.' SET Errors = substring(Errors,2) WHERE substring(Errors,1,1)=";"';
      $runQ3 = $this->screeningdb->query($q3);
    }

   $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);

          }
      }
    }

    public function checkDuplTagin ($file)
    {
      //Check for duplicate Tree tags within temporary database.
      $q1 = "SELECT Tag, COUNT(Tag) AS cnt FROM ".$file." WHERE Errors<>'NONE' GROUP BY Tag HAVING cnt > 1";
      $runQuery1 = $this->screeningdb->query($q1);
      if ($runQuery1->num_rows() > 0)
      {
         foreach($runQuery1->result() as $row)
         {
            $q2 = 'UPDATE '.$file.' SET Errors = CONCAT(TRIM(Errors),";Duplicate Tag") WHERE Tag = "'.$row->Tag.'" AND Errors <> "" AND Errors IS NOT NULL';
            $q3 = 'UPDATE '.$file.' SET Errors = "Duplicate Tag" WHERE Tag = "'.$row->Tag.'" AND (Errors = "" OR ISNULL(Errors))';
            $runQuery2 = $this->screeningdb->query($q2);
            $runQuery3 = $this->screeningdb->query($q3);
         }
      }
    }

    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))';
        }
      }
    }

    public function screenCodes ($fileName)
    {
      //Check for invalid codes AND
      //for Trees with both a D code and diameter measurement

      //Get codes from TSMAttributes table and put in 1-D array
      $queryCodes = "SELECT TSMCode FROM TSMAttributes";
      $runQueryCodes = $this->screeningdb->query($queryCodes);

      $j = 0;
      foreach($runQueryCodes->result() as $row)
      {
        $tsmcodesarray[$j++] = $row->TSMCode;
      }

      //Get codes that mean dead
      $queryCodes = "SELECT TSMCode FROM TSMAttributes WHERE LOWER(Description) LIKE '%dead%'";
      $runQueryCodes = $this->screeningdb->query($queryCodes);
      $j = 0;
      foreach($runQueryCodes->result() as $row)
      {
        $deadcodesarray[$j++] = $row->TSMCode;
      }

      $query = "SELECT TempID,Codes FROM ".$fileName." WHERE !ISNULL(Codes) AND Codes<>'*' AND Codes<>''";
      $runQuery = $this->screeningdb->query($query);
      foreach($runQuery->result() as $queryRow)
      {
          $nocodes = SUBSTR_COUNT($queryRow->Codes,';');
          $arrcodes = explode(";", $queryRow->Codes);

          //Check if there are duplicate codes
          $uniquecodes = array_unique($arrcodes);
          $cntcodes1 = count($arrcodes);
          $cntcodes2 = count($uniquecodes);
          if ($cntcodes1 <> $cntcodes2)
          {
             $q2 = 'UPDATE '.$fileName.' SET Errors = CONCAT(TRIM(Errors),";Duplicate code") WHERE TempID = '.$queryRow->TempID.' AND Errors<>"NONE" AND Errors <>"" AND Errors IS NOT NULL';
             $q3 = 'UPDATE '.$fileName.' SET Errors = "Duplicate code" WHERE TempID = '.$queryRow->TempID.' AND Errors<>"NONE" AND (Errors = "" OR ISNULL(Errors))';
             $runQ2 = $this->screeningdb->query($q2);
             $runQ3 = $this->screeningdb->query($q3);
          }

          for ($i = 0; $i <= $nocodes; $i++)
          {
            //Tag if code does not appear in TSMAttributes table
            if (!in_array($arrcodes[$i], $tsmcodesarray))
            {
              $q2 = 'UPDATE '.$fileName.' SET Errors = CONCAT(TRIM(Errors),";Check code:","'.$arrcodes[$i].'") WHERE TempID = '.$queryRow->TempID.' AND Errors<>"NONE" AND Errors <>"" AND Errors IS NOT NULL';
              $q3 = 'UPDATE '.$fileName.' SET Errors = "Check code:'.$arrcodes[$i].'" WHERE TempID = '.$queryRow->TempID.' AND Errors<>"NONE" AND (Errors = "" OR ISNULL(Errors))';
              $runQ2 = $this->screeningdb->query($q2);
              $runQ3 = $this->screeningdb->query($q3);
            }

            //Tag if tree is dead and there is a dbh
            if (in_array($arrcodes[$i], $deadcodesarray))
            {
              $q2 = 'UPDATE '.$fileName.' SET Errors = CONCAT(TRIM(Errors),";Tree dead with dbh") WHERE tempID = '.$queryRow->TempID.' AND Errors<>"NONE" AND Errors <>"" AND Errors IS NOT NULL AND DBH>=10';
              $q3 = 'UPDATE '.$fileName.' SET Errors = "Tree dead with dbh" WHERE TempID = '.$queryRow->TempID.' AND Errors<>"NONE" AND (Errors = "" OR ISNULL(Errors)) AND DBH>=10';
              $runQ2 = $this->screeningdb->query($q2);
              $runQ3 = $this->screeningdb->query($q3);
            }

          }
       }
    }

          $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);
         }
      }
    }

    $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);
        }
      }
    }

TempLocations

    public function screenXY ($file, $plotID)
    {
      $q1 = "SELECT TempID FROM ".$file." as a,Site as b WHERE a.PlotID=b.PlotID and a.X NOT BETWEEN 0 AND b.QDimX AND Errors<>'NONE'";
      $runQuery1 = $this->screeningdb->query($q1);
      if ($runQuery1->num_rows() > 0)
      {
         foreach($runQuery1->result() as $row)
         {
            $q2 = 'UPDATE '.$file.' SET Errors = CONCAT(TRIM(Errors),";Check x-coordinate") WHERE TempID = '.$row->TempID.' AND Errors <> "" AND Errors IS NOT NULL';
            $q3 = 'UPDATE '.$file.' SET Errors = "Check x-coordinate" WHERE TempID = '.$row->TempID.' AND (Errors = "" OR ISNULL(Errors))';
            $runQuery2 = $this->screeningdb->query($q2);
            $runQuery3 = $this->screeningdb->query($q3);
         }
      }


      $q1 = "SELECT TempID FROM ".$file." as a,Site as b WHERE a.PlotID=b.PlotID and a.Y NOT BETWEEN 0 AND b.QDimY AND Errors<>'NONE'";
      $runQuery1 = $this->screeningdb->query($q1);
      if ($runQuery1->num_rows() > 0)
      {
         foreach($runQuery1->result() as $row)
         {
            $q2 = 'UPDATE '.$file.' SET Errors = CONCAT(TRIM(Errors),";Check y-coordinate") WHERE TempID = '.$row->TempID.' AND Errors <> "" AND Errors IS NOT NULL';
            $q3 = 'UPDATE '.$file.' SET Errors = "Check y-coordinate" WHERE TempID = '.$row->TempID.' AND (Errors = "" OR ISNULL(Errors))';
            $runQuery2 = $this->screeningdb->query($q2);
            $runQuery3 = $this->screeningdb->query($q3);
        }
      }
    }

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


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

    public function  checkSpecies ($filein, $fileout)
    {
      //Check that species codes in filein are found in fileout

      $q1 = "SELECT TempID,Mnemonic FROM ".$filein." WHERE Mnemonic IS NULL or Mnemonic NOT IN (SELECT DISTINCT Mnemonic FROM ".$fileout.") AND Errors<>'NONE'";
      $runQ1 = $this->screeningdb->query($q1);

      if ($runQ1->num_rows() > 0)
      {
         foreach($runQ1->result() as $row)
         {
            $q2 = 'UPDATE '.$filein.' SET Errors = CONCAT(TRIM(Errors),";Check species") WHERE TempID = '.$row->TempID.' AND Errors <> "" AND Errors IS NOT NULL';
            $q3 = 'UPDATE '.$filein.' SET Errors = "Check species" WHERE TempID ='.$row->TempID.' AND (Errors = "" OR ISNULL(Errors))';
            $runQ2 = $this->screeningdb->query($q2);
            $runQ3 = $this->screeningdb->query($q3);
         }
      }
    }

    public function checkDuplStemin ($fileName)
    {
      //Check for duplicate Tree and Stem tags within temporary database.
      //Do not include dead stems that couldn't be tagged (StemTag=NULL)???
      //$q1 = "SELECT Tag, StemTag, COUNT(Tag) as cnt FROM (SELECT Tag, StemTag, Errors FROM ".$fileName." UNION SELECT Tag, StemTag, Errors FROM ".$fileName."Error) as a GROUP BY Tag, StemTag HAVING cnt > 1";
      $q1 = "SELECT Tag, StemTag, COUNT(Tag) as cnt FROM ".$fileName." GROUP BY Tag, StemTag HAVING cnt > 1";
      $runQuery1 = $this->screeningdb->query($q1);
      if ($runQuery1->num_rows() > 0)
      {
         foreach($runQuery1->result() as $row)
         {
            $q2 = 'UPDATE '.$fileName.' SET Errors = CONCAT(TRIM(Errors),";Duplicate Tag/StemTag") WHERE Tag = "'.$row->Tag.'" AND StemTag="'.$row->StemTag.'" AND Errors <> "" AND Errors IS NOT NULL';
            $q3 = 'UPDATE '.$fileName.' SET Errors = "Duplicate Tag/StemTag" WHERE Tag = "'.$row->Tag.'" AND StemTag="'.$row->StemTag.'" AND (Errors = "" OR ISNULL(Errors))';
            $runQuery2 = $this->screeningdb->query($q2);
            $runQuery3 = $this->screeningdb->query($q3);
         }
      }
    }

    public function makeErrorBlank($fileName)
    {
      $query = "UPDATE ".$fileName." SET Errors='' WHERE Errors IS NULL || Errors<>'NONE'";
      $runQuery = $this->screeningdb->query($query);
      if($runQuery == TRUE)
      {
        $status = "Success";
      }
      else
      {
        $status = "Fail";
      }
      return $status;
    }