/// <summary>
/// Adds the master tapes from file.
/// </summary>
/// <param name="worker">Background worker</param>
/// <param name="importStream">Import Stream for file</param>
/// <param name="ofd">The file returned from OpenFileDialog</param>
/// <returns></returns>
public static bool AddMasterTapesFromFile(BackgroundWorker worker, Stream importStream, OpenFileDialog ofd, string masterArchive, int media, bool tmp = false)
{
//List of Projectvalues to send to database
List<MasterTapeValues> projectList = new List<MasterTapeValues>();
MasterTapeValues values = new MasterTapeValues();
//open file if one was selected
if ((importStream = ofd.OpenFile()) != null)
{
try
{
//items for
string line;
string newLine;
char[] seperators = ",".ToCharArray();
//streamReader to read csv file
StreamReader textReader = new StreamReader(importStream);
while ((line = textReader.ReadLine()) != null)
{
//parse line and make a line array
newLine = Regex.Replace(line, "[\u2013\u2014]", "-");
string[] lineArray = newLine.Split(seperators, 3);
//check to make sure there are 3 parts to each line
if (lineArray.Length > 1)
{
lineArray[0] = lineArray[0].Trim();
lineArray[1] = lineArray[1].Replace(',', '-').Trim();
int clipNumber = Convert.ToInt32(lineArray[0]);
//CHANGE VALUE LATER
values = new MasterTapeValues(lineArray[1], lineArray[2], masterArchive, clipNumber.ToString("000"));
projectList.Add(values);
}
else
{
//only one part, it will not add this value to database
}
}
}
catch
{
//Index may be out of range, but that is supposed to happen if entry already exists
}
}
//counters for updating the progress bar
int counter = 0;
int progressCounter = 0;
float queryCounter = 100.0f / projectList.Count;
float progress = 0.0f;
try
{
//connect to DB
SQLiteConnection masterConnection = new SQLiteConnection(database);
masterConnection.Open();
//Insert new Archive media if it doesn't already exist
string masterQuery = "insert or ignore into MasterList(master_archive, master_media) values (@m_archive, @m_media)";
SQLiteCommand command = new SQLiteCommand(masterQuery, masterConnection);
command.Parameters.AddWithValue("@m_archive", masterArchive);
command.Parameters.AddWithValue("@m_media", media);
if(command.ExecuteNonQuery() == 1)
{
Debug.WriteLine("Master List insert Success");
}else
{
Debug.WriteLine("Master List insert Failed");
}
//iterate over each entry and insert it into the DB
foreach (MasterTapeValues master in projectList)
{
string query = "insert into MasterArchiveVideos (project_id, video_name, master_tape, clip_number) values (@projectID, @videoName, @masterTape, @clipNumber)";
command = new SQLiteCommand(query, masterConnection);
command.Parameters.Clear();
command.Parameters.AddWithValue("@projectID", master.ProjectID);
command.Parameters.AddWithValue("@videoName", master.VideoName);
command.Parameters.AddWithValue("@masterTape", master.MasterTape);
command.Parameters.AddWithValue("@clipNumber", master.ClipNumber);
if (command.ExecuteNonQuery() == 1)
{
//Success
counter++;
progressCounter++;
if ((progressCounter * queryCounter) >= 1)
{
UpdateTapesWithMaster(master.ProjectID, master.MasterTape, masterConnection);
progress += (queryCounter * progressCounter);
progressCounter = 0;
//update the progess bar
worker.ReportProgress(Convert.ToInt32(progress));
Debug.WriteLine("Added: " + master.ProjectID);
}
}
else
{
//Failure
}
}
if (counter > 0)
{
Debug.WriteLine(counter + " items added to database");
MainForm.LogFile(counter + " master archive(s) added to database");
masterConnection.Close();
DeleteFile(tmp, ofd, importStream);
return true;
}
else
{
//No entries found
Debug.WriteLine("No master archive(s) added to database");
masterConnection.Close();
DeleteFile(tmp, ofd, importStream);
return false;
}
}
catch (SQLiteException e)
{
MainForm.LogFile("Import Projects Error: " + e.Message);
DeleteFile(tmp, ofd, importStream);
return false;
}
}
/// <summary>
/// Gets all archive video values.
/// </summary>
/// <returns></returns>
public static List<MasterTapeValues> GetAllArchiveVideoValues()
{
//declare values
List<MasterTapeValues> mList = new List<MasterTapeValues>();
MasterTapeValues value;
try
{
//establish connection
SQLiteConnection archiveConnection = new SQLiteConnection(database);
archiveConnection.Open();
//new command
SQLiteCommand command = new SQLiteCommand(archiveConnection);
//add query
command.CommandText = "select * from MasterArchiveVideos order by project_id asc";
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
//data has been returned
while (reader.Read())
{
value = new MasterTapeValues(reader["project_id"].ToString(), reader["video_name"].ToString(), reader["master_tape"].ToString(), reader["clip_number"].ToString(), Convert.ToInt32(reader["id"]));
mList.Add(value);
}
CloseConnections(command, archiveConnection);
}
else
{
//returned nothing
CloseConnections(command, archiveConnection);
}
}
}catch(SQLiteException e)
{
MainForm.LogFile("SQLite Error: " + e.Message);
}
return mList;
}