$chosenserverInstance = "APP_INSTANS"
$databaseServer = "ServerName"

add-type -AssemblyName System.Data;
Import-Module 'C:\Program Files\Microsoft Dynamics NAV\110\Service\NavAdminTool.ps1'
cls

function Format-DateTime {
    param (
        [datetime]$DateTime
    )

    return $DateTime.ToString("yyyy-MM-dd HH:mm:ss")
}


function Execute-SqlStatement {
    param(
        [Parameter(Mandatory=$true)]
        [string]$Server,
        
        [Parameter(Mandatory=$true)]
        [string]$Database,
        
        [Parameter(Mandatory=$false)]
        [string]$Username,
        
        [Parameter(Mandatory=$false)]
        [string]$Password,
        
        [Parameter(Mandatory=$true)]
        [string]$SqlStatement,
        
        [Parameter(Mandatory=$false)]
        [bool]$ReturnObject = $false,
        
        [int]$Timeout = 30
    )

    try {
        # Import the required .NET assembly
        Add-Type -AssemblyName System.Data

        # Construct the connection string
        $connectionString = "Server=$Server;Database=$Database;Integrated Security=True;Connect Timeout=$Timeout;"
        if($verbose) { Write-Host "ConnectionString : $connectionString" }

        # Create a SQL Server connection object
        $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)

        # Open the connection
        $connection.Open()

        # Create a SQL Server command object
        $command = $connection.CreateCommand()
        $command.CommandText = $SqlStatement

        # Execute the SQL statement
        if ($ReturnObject) {
            $dataTable = New-Object System.Data.DataTable
            $dataTable.Load($command.ExecuteReader())
            $connection.Close()
            return $dataTable
        } else {
            $rows = New-Object System.Collections.ArrayList
            $reader = $command.ExecuteReader()

            while ($reader.Read()) {
                $row = @{}
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $row[$reader.GetName($i)] = $reader[$i]
                }
                $rows.Add([PSCustomObject]$row)
            }
            $connection.Close()
            return $rows
        }
    }
    catch {
        Write-Error "Error occurred while executing SQL statement: $_"
        return $null
    }
}


function ConnectAndExecuteSQL{
    param(
        $server,
        $database,
        $sql,
        $psw,
        $username,
        [boolean] $returnval = $false,
        [boolean] $nonquery = $false
    )
   
    #$ObjArray = Execute-SQLScalar -conn $conn -sql $sql

    if ($nonquery){$ObjArray = Execute-SqlNonQueryWithParams -SqlStatement $sql -Server $server -Database $database -Password $psw -Username $username }
    else
    {$ObjArray = Execute-SqlStatement -SqlStatement $sql -Server $server -Database $database -Password $psw -Username $username -ReturnObject $returnval}
    return  $ObjArray
}


function Get-SqlServerDatabases {
    param(
        [Parameter(Mandatory=$true)]
        [string]$Server
    )

    try {
        # Import the required .NET assembly
        Add-Type -AssemblyName System.Data

        # Construct the connection string with Windows Authentication
        $connectionString = "Server=$Server;Integrated Security=True;"
        
        # Create a SQL Server connection object
        $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)

        # Open the connection
        $connection.Open()

        # Create a SQL Server command object to retrieve database names
        $command = $connection.CreateCommand()
        $command.CommandText = "SELECT name FROM sys.databases WHERE state_desc = 'ONLINE'"

        # Execute the SQL command and store the results in a DataTable
        $dataTable = New-Object System.Data.DataTable
        $dataTable.Load($command.ExecuteReader())

        # Close the connection
        $connection.Close()

        # Extract the database names from the DataTable and return as a list
        $databaseNames = @()
        foreach ($row in $dataTable.Rows) {
            $databaseNames += $row["name"]
        }

        return $databaseNames
    }
    catch {
        Write-Error "Error occurred while retrieving database names: $_"
        return $null
    }
}




# Get all NAV server instances on the server
$serverInstance = Get-NAVServerInstance -ServerInstance $chosenserverInstance 

# Loop through each NAV server instance

    if ($serverInstance.State -eq "Running"){
    
        # Get the list of tenants for this server instance
    
        $tenants = Get-NAVTenant -ServerInstance $serverInstance.ServerInstance.Split('´$')[1]; 

        # Loop through each tenant
        foreach ($tenant in $tenants) {
        
            # Get the list of companies for each tenant
            $Companies = Get-NAVCompany -ServerInstance $serverInstance.ServerInstance.Split('´$')[1] -Tenant $tenant.id
            write-host $tenant.Id
            # Loop through each company
            foreach ($Company in $Companies) {
                write-host " - "$Company.CompanyName
                      $sql = @"
                                UPDATE [$($tenant.DatabaseName)].[dbo].[$($Company.CompanyName)`$NS TS Exchange]
                                SET [Status] = 1
                                WHERE [Navision Error] LIKE '%Dubletkontrol fejlede på OriginalTrackingID:%'
                                  AND [Navision Error] NOT LIKE '%'+[Tracking ID]+'%';
"@

                # Get the Database Server from the NAV instance details
               

                # Execute the SQL command using the actual SQL server and tenant database
                $Ok = ConnectAndExecuteSQL -returnval $true -server  $databaseServer -database $tenant.DatabaseName -sql $sql -psw $password -username $username

            }
        } 
    
    }