#!/usr/bin/perl -w
#DatenbankAbfragen

use strict;
use warnings;
use DBI;

use Digest::MD5;
my $dbh;
my  $raw_data;
my  $sqldata;
my  $rc;

#####################################RC-CISCO LETZTEN STATUS ABFRAGEN ########################

 sub Query{ 
 my $query;
 my $sth;     
 my $execu;    
 my $newdata;
 my $file;
 my $ds;
 my @ergebnisse;
 my @row;
 my $sqlrecord;
 my $db_id;
 my $neid;
 my $CISCOID;
 my $datarefid;
 my $sth1;
 my $query1;
 my $found;
 my $th_dbh;
  
  
  
  $query="SELECT * FROM (SELECT RC_CISCO.ID, RC_CISCO.NEID, GATE_STATE.NEID, GATE_STATE.AState, GATE_STATE.TS_LastChange, ".
                   "GATE_STATE.Data_RefID FROM (RC_CISCO LEFT JOIN GATE_STATE ON RC_CISCO.ID = GATE_STATE.Data_RefID) WHERE ( (GATE_STATE.NEID='$neid')) ORDER BY RC_CISCO.TS DESC) AS SUB1 GROUP BY SUB1.ID";
         

            $sth = $dbh->prepare($query) or die "$DBI::errstr\n";
            $ds = $sth->execute or die "$DBI::errstr\n";

            while($newdata = $sth->fetchrow_hashref)
            {
            
            
              
              if (($newdata ->{ID}) eq ($newdata->{Data_RefID}))
                 { 
                   #RC_CISCO DATEN VERGLEICHEN 
                
                 if (($newdata->{ID} eq "RC_CISCO$_->{CISCOID}") && ($newdata->{NEID} eq "$_->{neid}")
                  {
               print "$stext:RC_CISCO$_->{CISCOID} unverändert -> Updaten\n";
               
               $query="UPDATE GATE_STATE SET TS_LastChange=NOW() WHERE Data_RefID='$newdata->{ID}' ORDER BY TS_LastChange DESC LIMIT 1;
               $sth1= $th_dbh->prepare($query) or die "$DBI::errstr\n";
                      
                      $ds = $sth1->execute or die "$DBI::errstr\n";
                      $sth1->finish();
               
               
               }
               }
             else
            
                    {
            
                      print "$stext:Unterschiedliche RC_CISCO Daten für RC_CISCO$_->{CISCOID}...\n";
                      # Updaten 
                      if ($newdata->{ID} ne "RC_CISCO$_->{CISCOID}")
                      {
                        $query="UPDATE RC_CISCO SET ID='RC_CISCO$_->{CISCOID}' WHERE ID='$newdata->{ID}' LIMIT 1;";
                        $sth1 = $dbh->prepare($query) or die "$DBI::errstr\n";
                        $ds = $sth1->execute or die "$DBI::errstr\n";
                        $sth1->finish();
                      }
                   }

 }
 }
 }