# Debug writes sql commands
$verbose = $false

#Output is 2 files containing the before and after lines. Review if needed
$SaveFilePath = "C:\temp\ListOutput"
$serverinstanceList= @('Serverinstance'); #eg. $serverinstanceList= @('ServerInstans1', 'Serverinstance2')



#Amos Iversen Økonomistyrelsen 
############################################################################
Import-Module GenericModule
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-MySqlNonQueryWithParams {
    param(
        [string]$Server,
        [string]$Database,
        [string]$Username,
        [string]$Password,
        [string]$TableName,
        [string]$objectname,
        [System.Object]$DataObjects
    )

    try {
        # Construct the connection string
        $connectionString = "Server=$Server;Port=3306;Database=$Database;Uid=$Username;Pwd=$Password;Timeout=30;"

        # Create a MySqlConnection object
        $connection = New-Object MySql.Data.MySqlClient.MySqlConnection
        $connection.ConnectionString = $connectionString

        # Open the connection
        $connection.Open()

        # Create a MySqlCommand object
        $command = New-Object MySql.Data.MySqlClient.MySqlCommand
        $command.Connection = $connection

        # Convert single object to ArrayList if needed
        if ($DataObjects -isnot [System.Collections.ArrayList]) {
            $DataObjects = [System.Collections.ArrayList]@($DataObjects)
        }

        # Initialize progress bar
        $totalCount = $DataObjects.Count
        $progress = 0

        # Iterate through each data object (DataRow)
        foreach ($DataRow in $DataObjects) {
            # Increment progress
            $progress++
            $percentComplete = ($progress / $totalCount) * 100

            # Construct the SQL insert statement
            $insertColumns = @()
            $insertValues = @()

            # Clear command parameters
            $command.Parameters.Clear()

            # Iterate through each property in the DataRow
            foreach ($property in $DataRow.PSObject.Properties) {
                $columnName = $property.Name
                $columnValue = $property.Value

                # Skip columns with empty or null values
                if ([string]::IsNullOrWhiteSpace($columnValue)) {
                    continue
                }

                # Determine the data type of the column value and set the parameter type accordingly
                if ($columnValue -is [DateTime]) {
                    $parameterType = 'datetime'
                } elseif ($columnValue -is [byte[]]) {
                    $parameterType = 'binary'
                } else {
                    $parameterType = 'varchar'
                }

                # Add column name to the list of insert columns
                $insertColumns += $columnName

                # Add parameter placeholder to the list of insert values
                $insertValues += "@$columnName"

                # Add parameter to the command with the determined data type
                $parameter = New-Object MySql.Data.MySqlClient.MySqlParameter
                $parameter.ParameterName = "@$columnName"
                $parameter.MySqlDbType = $parameterType
                $parameter.Value = $columnValue
                $command.Parameters.Add($parameter) | Out-Null
            }

            $insertColumnsString = [string]::Join(", ", $insertColumns)
            $insertValuesString = [string]::Join(", ", $insertValues)

            $sqlStatement = "INSERT INTO $TableName ($insertColumnsString) VALUES ($insertValuesString)"
            $command.CommandText = $sqlStatement

            # Execute the SQL statement
            $null = $command.ExecuteNonQuery()

            # Update progress bar
            Write-Progress -Activity "Executing SQL statements : $objectname" -Status "Processed $progress of $totalCount" -PercentComplete $percentComplete
        }

        # Close the connection
        $connection.Close()

        return $null  # Or return any desired output
    }
    catch {
        Write-Error "Error occurred while executing SQL statement: $_"
        return $null
    }
}

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 Import-CSVData {
    param(
        [string]$FilePath,
        [PSCustomObject]$CustomObjectTemplate
    )

    # Import data from CSV file
    $importedObjects = Import-Csv -Path $FilePath

    # Create an array to store imported objects
    $importedCustomObjects = @()

    foreach ($row in $importedObjects) {
        $customObject = $CustomObjectTemplate

        # Map CSV columns to custom object properties
        $customObject.From_SchemaName = $row.From_SchemaName
        $customObject.To_SchemaName = $row.To_SchemaName
        $customObject.participant_id = $row.participant_id
        
        $importedCustomObjects += $customObject
    }

    return $importedCustomObjects
}

function Export-DataObjectToFile {
    param(
        [Parameter(Mandatory=$true)]
        [object]$DataObject,
        
        [Parameter(Mandatory=$true)]
        [string]$FilePath,

        [ValidateSet('XML', 'CSV')]
        [string]$Format = 'XML'
    )

    try {
        if ($Format -eq 'XML') {
            # Serialize the data object to XML format and save it to the specified file path
            $DataObject | Export-Clixml -Path "$($FilePath).xml" -Encoding UTF8
            Write-Host "Data object exported to $FilePath in XML format"
        }
        elseif ($Format -eq 'CSV') {
            # Export the data object to CSV format and save it to the specified file path
            $DataObject | Export-Csv -Path "$($FilePath).csv" -Encoding UTF8 -NoTypeInformation
            Write-Host "Data object exported to $FilePath in CSV format"
        }
    }
    catch {
        Write-Error "Error occurred while exporting data object to file: $_"
    }
}


function Import-DataObjectFromFile {
    param(
        [Parameter(Mandatory=$true)]
        [string]$FilePath
    )

    try {
        # Import the serialized data object from the specified file path
        $DataObject = Import-Clixml -Path $FilePath
        Write-Host "Data object imported from $FilePath"
        return $DataObject
    }
    catch {
        Write-Error "Error occurred while importing data object from file: $_"
        return $null
    }
}

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

#$databaseList = Get-SqlServerDatabases -Server $DatabaseServer
$OutPre = @()
$outAfter = @()
Write-host " serverinstance - Tenant.Id - CompanyName "
Foreach($serverInstance in $serverinstanceList){
    $tenantList = Get-NAVTenant -Serverinstance $serverInstance 
    foreach($Tenant in $tenantList){
        $database =  $Tenant.DatabaseName
        $DatabaseServer = $Tenant.DatabaseServer
        $Companies = Get-NAVCompany -ServerInstance $serverInstance -Tenant $Tenant.Id
        Foreach($Company in $Companies){
        
            $sql = @"
            SELECT 
                [Inbound Document No_],
                [Original Tracking ID],
                [Status] 
            FROM [$($database)].[dbo].[$($Company.CompanyName)`$Inbound Purch_ Document Header] as Tab
            WHERE [Original Tracking ID] IN (
                SELECT [Original Tracking ID]
                FROM [$($database)].[dbo].[$($Company.CompanyName)`$Inbound Purch_ Document Header]
                WHERE [Original Tracking ID] <> ''
                GROUP BY [Original Tracking ID]
                HAVING COUNT(*) > 1
                );
"@

            #if($verbose){Write-host $sql}
            $DupletList = ConnectAndExecuteSQL -returnval $true -server $DatabaseServer -database $database -sql $($sql) 
            $Count = $DupletList.length
            if ($trackingIDArrays) {
                $trackingIDArrays.Clear()
            } 

            # Create an empty array to store arrays for each unique Original Tracking ID
            $trackingIDArrays = @()
            $trackingIDArrays = @{}

            # Iterate through each item in $DupletList
            foreach ($item in $DupletList) {
                $trackingID = $item.'Original Tracking ID'

                # Add extra columns to the $item object
                $item | Add-Member -MemberType NoteProperty -Name ServerInstance -Value $serverInstance
                $item | Add-Member -MemberType NoteProperty -Name Tenant -Value $Tenant.Id
                $item | Add-Member -MemberType NoteProperty -Name Company -Value $Company.CompanyName

                # Check if the Original Tracking ID exists in $trackingIDArrays
                if ($trackingIDArrays.ContainsKey($trackingID)) {
                    # If the ID exists, add the item to the corresponding array
                    $trackingIDArrays[$trackingID] += $item
                } else {
                    # If the ID doesn't exist, create a new array with the item
                    $trackingIDArrays[$trackingID] = @($item)
                }
            }


            foreach ($trackingID in $trackingIDArrays.Keys) {
            
                # Get the array of items for the current Original Tracking ID
                $array = $trackingIDArrays[$trackingID]

                # Check if there is a status of 1 in the array
                $status1Exists = $array | Where-Object { $_.'Status' -eq 1 }

                # Check if there is a status of 2 in the array
                $status2Exists = $array | Where-Object { $_.'Status' -eq 2 }

                # Check if all statuses are 0
                $allStatuses0 = ($array | Where-Object { $_.'Status' -eq 0 }).Count -eq $array.Count

                $setstatus = $true
                # Update statuses based on the rules
                foreach ($item in $array) {
                    $outPre += $item
                    if ($status1Exists) {
                        if ($item.'Status' -ne 1) {
                            $item.'Status' = 2
                        }
                    } elseif ($status2Exists) {
                        if ($item.'Status' -eq 0 -and $setstatus) {
                            $setstatus = $false
                        
                        } else {
                            $item.'Status' = 2
                        }
                    } elseif ($allStatuses0 -and $item -ne $array[0]) {
                        $item.'Status' = 2

                    }
                    $outAfter += $item;
                }
                
            }
            
            

            Foreach($orginalTrackingIDList in $trackingIDArrays.Values){
                Foreach($line in $orginalTrackingIDList){ 
            
                    $sql = @"
                        UPDATE [$($database)].[dbo].[$($Company.CompanyName)`$Inbound Purch_ Document Header]
                        SET [Status] = $($line.'Status')
                        WHERE [Original Tracking ID] = '$($line.'Original Tracking ID')' AND [Inbound Document No_] = '$($line.'Inbound Document No_')';
"@
                    ConnectAndExecuteSQL -returnval $true -server $DatabaseServer -database $database -sql $($sql) 
                    if($line.'Status' -eq 2){
                        $sql2 =@"
                         UPDATE [$($database)].[dbo].[$($Company.CompanyName)`$Inbound Purch_ Doc_ Header 2]
                            SET [IndFak2 ID] = 'Slettet ved dublet kontrol Kørsel'
                            WHERE [Inbound Document No_] = '$($line.'Inbound Document No_')';
"@
                       ConnectAndExecuteSQL -returnval $true -server $DatabaseServer -database $database -sql $($sql2) 
                       
                    }
                     if ($verbose){
                            write-host $sql
                            write-host $sql2
                        }
                    
                    
                    
                }
               
            }
           
            

            # Calculate the total length of the string
            $totalLength = $serverinstance.Length + $tenantId.Length + $companyName.Length + $total.Length

            # Calculate the number of spaces needed for alignment
            $spacesNeeded = 60 - $totalLength  # Adjust 50 to the desired total length

            # Create the output string with spaces
            $output = "$($serverinstance) - $($Tenant.Id) - $($Company.CompanyName) ".PadRight($totalLength + $spacesNeeded) + "total: $($Count)"

            # Output the result
            Write-Host $output
            


         }
    }

}
  Export-DataObjectToFile -DataObject $OutPre -FilePath $($SaveFilePath + "Before") -Format CSV
  Export-DataObjectToFile -DataObject $OutAfter -FilePath  $($SaveFilePath + "After") -Format CSV