Ad hoc Geek

# Configuration
$camera_home = "C:\Recordings";
$connection_string = "Data Source=localhost;Initial Catalog=MyDatabase;Trusted_Connection=True";
 
# Sanity Check
if (!(Test-Path $camera_home)) 
{
    [Console]::WriteLine($camera_home + " not found!"); 
    return;
};
 
# Set up the SQL Connection
$conn = new-object System.Data.SqlClient.SqlConnection($connection_string);
$conn.Open();
 
# Get a list of all cameras
$cameras = Get-ChildItem $camera_home;
 
# Iterate over the list
foreach($camera in $cameras)
{
    [Console]::WriteLine($camera);
    $guid = $camera.Name;
 
    # Check whether the current camera exists in the database.
    $camera_getid = new-object System.Data.SqlClient.SqlCommand("select ID from dbo.Camera where CameraGUID = @CameraGUID", $conn);
    $camera_getid.Parameters.Add("@CameraGUID", [System.Data.SqlDbType]"NVarChar").Value = $guid;
    $id = $camera_getid.ExecuteScalar();
    if ($id -eq $Null)
    {
        # Create a new camera record
        $camera_create = new-object System.Data.SqlClient.SqlCommand("insert into dbo.Camera(CameraGUID, CameraName) values(@CameraGUID, 'New Camera')", $conn);
        $camera_create.Parameters.Add("@CameraGUID", [System.Data.SqlDbType]"NVarChar").Value = $guid;
        $camera_create.ExecuteNonQuery();
        $id = $camera_getid.ExecuteScalar();        
    }
 
    # Get the date of the last recording we have a record for
    $camera_getlastrecording = new-object System.Data.SqlClient.SqlCommand("select case when Max(RecordingDate) is null then convert(datetime, '1/1/2001', 103) else MAX(RecordingDate) end from dbo.CameraData where CameraID = @CameraID", $conn);
    $camera_getlastrecording.Parameters.Add("@CameraID", [System.Data.SqlDbType]"Int").Value = $id;
    $lastrecording = $camera_getlastrecording.ExecuteScalar();
    [Console]::WriteLine([String]::Concat("    Last recording date : ", $lastrecording ));
 
    # Get a list of new recordings
    $recordings = Get-ChildItem -recurse -include "*.acsm" $camera_home\$camera | Where-Object {$_.CreationTime -gt $lastrecording};
    if ($recordings -eq $Null) 
    { 
        [Console]::WriteLine([String]::Concat("    No new recordings found."));
        continue 
    }
    [Console]::WriteLine([String]::Concat("    ", $recordings.Count, " new recordings found."));
 
    # Insert new recordings into the database
    foreach($recording in $recordings)
    {
        [Console]::WriteLine([String]::Concat($recording, ",", $recording.CreationTime.ToString("dd/MM/yyyy HH:mm:ss.FF")));
        $recording_create = new-object System.Data.SqlClient.SqlCommand("insert into dbo.CameraData(CameraID, RecordingName, RecordingDate) values(@CameraID, @RecordingName, @RecordingDate)", $conn);
        $recording_create.Parameters.Add("@CameraID", [System.Data.SqlDbType]"Int").Value = $id;
        $recording_create.Parameters.Add("@RecordingName", [System.Data.SqlDbType]"NVarChar").Value = $recording.FullName;
        $recording_create.Parameters.Add("@RecordingDate", [System.Data.SqlDbType]"DateTime").Value = $recording.CreationTime;
        $recording_create.ExecuteNonQuery();
    }
}