<?php

$servername = "localhost";
$username = "xxxx";
$password = "xxxx";
$dbname = "xxxxx";


// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error)
{
die("Connection failed: " . $conn->connect_error);
}
//read the json file contents
$files = scandir('ansible/');

foreach($files as $file)
  {
  if ($file != '.' && $file != '..')
  {
    $jsondata = file_get_contents('ansible/'.$file);
    $data = json_decode($jsondata, true);
    $hostname = $data['ansible_facts']['ansible_hostname'];
      echo 'Data For :'.$hostname;
      echo '<br />';
      echo '<br />';
      echo $hostname;
      echo '<br />';
    $domainname = $data['ansible_facts']['ansible_domain'];
      echo $domainname;
      echo '<br />';
    $vendorname = $data['ansible_facts']['ansible_system_vendor'];
      echo $vendorname;
      echo '<br />';
    $productname = $data['ansible_facts']['ansible_product_name'];
      echo $productname;
      echo '<br />';
  if($vendorname == "VMware, Inc.")
    {
    $machinetype = "VMware Guest";
      echo $machinetype;
      echo '<br />';
    }
  else
    {
    $machinetype = "Standalone";
      echo $machinetype;
      echo '<br />';
    }
    $operatingsystem = $data['ansible_facts']['ansible_distribution'];
      echo $operatingsystem;
      echo '<br />';
    $osrelease = $data['ansible_facts']['ansible_distribution_version'];
      echo $osrelease;
      echo '<br />';
    $machineserial = $data['ansible_facts']['ansible_product_serial'];
      echo $machineserial;
      echo '<br />';
      echo '<br />';
      echo '<br />';
      echo '<br />';


$sql = "SELECT id FROM hostname WHERE systemname = '$hostname'";
  if ($result = $conn->query($sql))
  {
    switch ($result->num_rows)
      {
        case 0:
                $sql = "INSERT INTO hostname (systemname, domain, vendor, model, machinetype, os, osrelease, machineserial) VALUES ('$hostname', '$domainname', '$vendorname','$productname','$machinetype','$operatingsystem','$osrelease','$machineserial')";
                if (!$conn->query($sql))
                  {
                    die ("Something wrong with insert query");
                  }
                $id = $conn->insert_id;
                $sql1 = "INSERT INTO details (detid, inservice,globalstatus,managedby,machineconsole,monitoring,application,datacenter,rackspace,mlog)
                        VALUES ('$id','1','1','TechOps','console-mgmt.domain','Monitoring','Application','Datacenter','rackspace','')";
                if ($conn->query($sql1) === TRUE)
                  {
                    echo "New record created successfully ";
                    echo '<br />';
                  }
                else
                  {
                    echo "Error: " . $sql1 . "<br>" . $conn->error;
                  }
        break;
        case 1:
                $row = $result->fetch_assoc();
                $id = $row['id'];
                $sql = "UPDATE hostname SET systemname = '$hostname', domain = '$domainname', vendor = '$vendorname', model = '$productname', machinetype = '$machinetype', os = '$operatingsystem', osrelease = '$osrelease', machineserial = '$machineserial' WHERE id = '$id'";
                if ($conn->query($sql) === TRUE)
                  {
                    echo "Record updated successfully ";
                    echo '<br />';
                  }
                else
                  {
                    echo "Error: " . $sql . "<br>" . $conn->error;
                  }
        break;
        default:
                die ("Something went wrong... we got multiple records for hostname... this is bad.  STOP");
        break;
    } #end of switch
  } #end of if ($result = $conn->query($sql))
  else
  {
    die ("Something wrong with select query");
  }

#Network Part

$interfacez = $data['ansible_facts']['ansible_interfaces'];
  foreach ($interfacez as $interf)
    {
      if (preg_match("/p1/i", $interf)) {
      continue;
    }
  elseif (preg_match("/p2/i", $interf))
    {
      continue;
    }
  elseif (preg_match("/p3/i", $interf))
    {
      continue;
    }
  elseif (preg_match("/p4/i", $interf))
    {
      continue;
    }
  elseif (preg_match("/p5/i", $interf))
    {
      continue;
    }
  elseif (preg_match("/p6/i", $interf))
    {
      continue;
    }
  elseif (preg_match("/lo/i", $interf))
    {
      continue;
    }


$idevice = $data['ansible_facts']['ansible_' . $interf]['device'];
  if(!isset($data['ansible_facts']['ansible_' . $interf]['ipv4']['address']))
    {
      $iaddress = "N/A";
      $igateway = "N/A";
    }
  else
    {
      $iaddress = $data['ansible_facts']['ansible_' . $interf]['ipv4']['address'];
      $igateway = $data['ansible_facts']['ansible_default_ipv4']['gateway'];
    }
$imacaddress = $data['ansible_facts']['ansible_' . $interf]['macaddress'];
  if(!$data['ansible_facts']['ansible_' . $interf]['active'])
    {
      $istatus = "0";
    }
  else
    {
      $istatus = $data['ansible_facts']['ansible_' . $interf]['active'];
    }

$sql = "SELECT * FROM network where netid = '$id'";
    #here i can get the $id for each loop
  if ($result = $conn->query($sql))
    #here i can get the $id for each loop
    {
      switch($result->num_rows)
        {
          case 0:
                  $sql = "INSERT INTO network (netid, interface, ipaddress, macaddress, gateway, interfacestatus)
                          VALUES ('$id','$idevice', '$iaddress', '$imacaddress','$igateway','$istatus')";
                  if ($conn->query($sql) === TRUE)
                    {
                      echo "New record created successfully ".$idevice;
                      echo '<br />';
                    }
                  else
                    {
                      echo "Error: " . $sql . "<br>" . $conn->error;
                    }
                $id = $conn->insert_id;
                #here works only first time second time i get an empty variable ? or null ? or what ever
          break;
          case 1:
                  $row = $result->fetch_assoc();
                  $id = $row['netid'];
                  echo $id;
                  echo "<br />";
                    $sql = "UPDATE network SET interface = '$idevice', ipaddress = '$iaddress', macaddress = '$imacaddress', gateway = '$igatewat', interfacestatus = '$istatus',  WHERE netid = '$id'";
                      if ($conn->query($sql) === TRUE)
                        {
                          echo "Record updated successfully ";
                          echo '<br />';
                        }
                      else
                        {
                          echo "Error: " . $sql . "<br>" . $conn->error;
                        }
                #here works only first time second time i get an empty variable ? or null ? or what ever
          break;
          default:
                  die ("Something went wrong"); #on second loop i end up here and my script dies
          break;
}
}
}  # end foreach on interfaces

}

}

$conn->close();
?>