function Invoke-DBCheck
{
	[OutputType([hashtable])]
	param
	(
		[Parameter(Mandatory = $true)]
		[string]$searchString
	)
	$output = @{}
	## Variablen für Zugriff auf Datenbank
	$Server = ""
	$sql = "SELECT cArtNr FROM dbo.tARtikel WHERE cArtNr LIKE '$searchString'"
	$Database = ''
	$User = ""
	$Password = ""
	
	## Verbindung herstellen
	$Connection = New-Object System.Data.SQLClient.SQLConnection
	$Connection.ConnectionString = "server=$Server;database=$Database;user=$User;password=$Password;trusted_connection=false;"
	$Connection.Open()
	
	## SQL absetzen
	$Command = New-Object System.Data.SQLClient.SQLCommand
	$Command.Connection = $Connection
	$Command.CommandText = $sql
	$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter ($sql, $Connection)
	$DataSet = New-Object System.Data.DataSet
	
	$result = $SqlAdapter.Fill($DataSet)
	$Connection.Close()
	$Command.Dispose()
	if ($DataSet.Tables[0])
	{
		if ($result -gt 0)
		{
			foreach ($row in $DataSet.Tables[0].Rows)
			{
				$output.Add($row.cArtNr, '')
			}
		}
	}
	return $output
}
$startingDirectory = (Get-Location).Path

$files = [System.IO.Directory]::GetFiles($startingDirectory)
[Hashtable]$products = @{ }
Write-Progress -Activity "FileIndex" -PercentComplete 0 -CurrentOperation "Retrieving Files" -Status "Running"
foreach ($file in $files)
{
	[System.IO.FileInfo]$file = $file
	
	$fileNameArray = ([System.IO.Path]::GetFileNameWithoutExtension($file.FullName).Replace('_', '-').Split('-'))
	if ($fileNameArray.Length -eq 5)
	{
		$parentArtNo = $fileNameArray[1]
		$colorNo = $fileNameArray[2]
		$imageType = $fileNameArray[3]
		$index = $fileNameArray[4]
		if ($null -ne $parentArtNo)
		{
			if (-not $products.ContainsKey($parentArtNo))
			{
				$products.Add($parentArtNo, @{ })
			}
			if ($null -ne $colorNo)
			{
				if (-not ($products[$parentArtNo]).ContainsKey($colorNo))
				{
					($products[$parentArtNo]).Add($colorNo, @{ Packshots = @{ }; ModelShots = @{ }; ChildArticles = @{ } })
				}
				try
				{
					switch ($imageType)
					{
						'P'
						{
							($products[$parentArtNo][$colorNo]).Packshots.Add($index, $file.FullName)
						}
						'M'
						{
							($products[$parentArtNo][$colorNo]).ModelShots.Add($index, $file.FullName)
						}
					}
				}
				catch
				{
					$e = $_.Exception
				}
			}
		}
	}
}

Write-Progress -Activity "FileIndex" -PercentComplete 0 -CurrentOperation "Checking if exists" -Status "Running"

$countAll = $products.Count + 0.01
$existingProducts = @{};

foreach ($productNo in $products.Keys)
{
	$existingProducts.Add($productNo,$products[$productNo].Clone())
}

$step = 1
foreach ($productNo in $products.Keys)
{
	if ((Invoke-DBCheck -searchString $productNo).Count -gt 0)
	{
		foreach ($colorNo in $products[$productNo].Keys)
		{
			$searchString = "$productNo-$colorNo%"
			$articleNos = (Invoke-DBCheck -searchString $searchString)
			if ($articleNos.Count -eq 0)
			{
				$existingProducts[$productNo].Remove($colorNo)
			}
			else
			{
				$existingProducts[$productNo][$colorNo].ChildArticles = $articleNos
			}
		}
	}
	else
	{
		$existingProducts.Remove($productNo)
	}
	$step++

	    #Write-Progress -Activity "FileIndex" -PercentComplete (($step/$countAll)*100) -CurrentOperation "Checking if exists" -Status "Running"
    
}

foreach ($prodNo in $existingProducts.Keys)
{
	foreach ($colorNo in $existingProducts[$prodNo].Keys)
	{
		foreach ($artNo in $existingProducts[$prodNo][$colorNo].ChildArticles.Keys)
		{
			($artNo + ';"' + $existingProducts[$prodNo][$colorNo].Packshots["1"] + '";"' + $existingProducts[$prodNo][$colorNo].Packshots["2"] + '";"' + $existingProducts[$prodNo][$colorNo].Packshots["3"] + '";"' + $existingProducts[$prodNo][$colorNo].Packshots["4"] + '";"'`
             + $existingProducts[$prodNo][$colorNo].Packshots["A"] + '";"' + $existingProducts[$prodNo][$colorNo].Packshots["B"] `
				+ '";"' + $existingProducts[$prodNo][$colorNo].ModelShots["1"] + '";"' + $existingProducts[$prodNo][$colorNo].ModelShots["2"] + '";"' + $existingProducts[$prodNo][$colorNo].ModelShots[2] + '";"' + $existingProducts[$prodNo][$colorNo].ModelShots[3] + '";"' + $existingProducts[$prodNo][$colorNo].ModelShots[4]+'"') | Out-File -FilePath ([System.IO.Path]::Combine($startingDirectory,"ImageFeed.csv")) -Encoding ascii -Append -Force
		}
	}
}
Write-Progress -Activity "FileIndex" -PercentComplete 100 -CurrentOperation "Finished" -Status "Done"