function ConnectMySQL([string]$user, [string]$pass, [string]$MySQLHost, [string]$database) {
    # Load MySQL .NET Connector Objects
    [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")

    # Open Connection
    $connStr = "server=" + $MySQLHost + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database="+$database
    try {
        $conn = New-Object MySql.Data.MySqlClient.MySqlConnection
        $conn.ConnectionString = $connStr
        $conn.Open()
    } catch [System.Management.Automation.PSArgumentException] {
        Write-Host "Unable to connect to MySQL server, do you have the MySQL connector installed..?"
        Write-Host $_
        Exit
    } catch {
        Write-Host "Unable to connect to MySQL server..."
        Write-Host $_.Exception.GetType().FullName
        Write-Host $_.Exception.Message

    }
    Write-Host "Connected to MySQL database $MySQLHost\$database"

    return $conn
}

function Execute-MySQLNonQuery($conn, [string]$query) {
  $command = $conn.CreateCommand()                  # Create command object
  $command.CommandText = $query                     # Load query into object
  $RowsInserted = $command.ExecuteNonQuery()        # Execute command
  $command.Dispose()                                # Dispose of command object
  if ($RowsInserted) {
    return $RowInserted
  } else {
    return $false
  }
}

function UpdateStatusTask($Connection ,[string] $status){
    $QueryUpdate = "UPDATE portal.task set TaskStatus=$status where TaskName='Hyper-V'"
    Execute-MySQLNonQuery $Connection $QueryUpdate
}

function SetVMInfo($Connection){
    $Servers = @('HCI160','HCI163')

    # Get VM info
        Get-VM -ComputerName $Servers| ForEach-Object  {
        $VHDSize = (Get-VMHardDiskDrive -ComputerName $_.ComputerName -VMName $_.VMName | Get-VHD -ComputerName $_.ComputerName | Measure-Object Size -Sum).Sum -as [double]
        $VHDFileSize = (Get-VMHardDiskDrive -VMName $_.VMName -ComputerName $_.ComputerName | Get-VHD -ComputerName $_.ComputerName | Measure-Object FileSize -Sum).Sum -as [double]

        $QueryInsert = "INSERT INTO portal.vps_hyperv_sync (HyperVType,HyperVName,HyperVRAMUsed,HyperVRAM,HyperVDiskUsed,
                HyperVDisk,HyperVStatus,HyperVOwnerNode, isCluster)
                    VALUES ('{0}','{1}','{2}','{3}','{4}',
                '{5}','{6}','{7}','{8}')" -f '1',$_.VMName,$_.MemoryAssigned.ToString(), $_.MemoryMaximum.ToString(),
                $VHDFileSize.ToString(), $VHDSize.ToString(), $_.State,$_.ComputerName,$_.IsClustered
        Execute-MySQLNonQuery $Connection $QueryInsert
        }
}



function Main(){
$MySQLAdminUserName = 'hyperv'
$MySQLAdminPassword = 'Hqdv37RJp0jFBrGtkJaHoNbNOFMZiB3u'
$MySQLDatabase = 'portal'
$MySQLHost = '192.168.9.2'
#$ConnectionString = "server=" + $MySQLHost + ";port=3306;uid=" + $MySQLAdminUserName + ";pwd=" + $MySQLAdminPassword + ";database="+$MySQLDatabase
#$QueryInsert = "INSERT INTO portal.vps_hyperv_sync (HyperVType,HyperVName,HyperVRAMUsed,HyperVRAM,HyperVDiskUsed,
#HyperVDisk,HyperVVPS,HyperVStatus,HyperVIP,HyperVOwnerNode, isCluster)
# VALUES ('HyperVType','HyperVName','HyperVRAMUsed','HyperVRAM','HyperVDiskUsed',
#'HyperVDisk','HyperVVPS','HyperVStatus','HyperVIP','HyperVNode',1)"
$QueryClear = "TRUNCATE table portal.vps_hyperv_sync"

$Connection = ConnectMySQL $MySQLAdminUserName $MySQLAdminPassword $MySQLHost $MySQLDatabase
UpdateStatusTask $Connection '1'
Execute-MySQLNonQuery $Connection $QueryClear
#Execute-MySQLNonQuery $Connection $QueryInsert
SetVMInfo $Connection
UpdateStatusTask $Connection '0'
$Connection.Close()
}

Main