//Edit button pressed
private void editTapeEditButton_Click(object sender, EventArgs e)
{
//check to see if project ID is a number
if (commonMethod.StringIsANumber(editProjectIDTextbox.Text))
{
AddToDatabase editDB = new AddToDatabase();
string projectNameEdit = "";
if (editProjectNameLabel.Text.Length > 0)
{
projectNameEdit = editProjectNameLabel.Text;
}
else
{
projectNameEdit = editTapeNameTextbox.Text;
}
//if there is text in tags textbox then add it on the end of the tag string
if (editTagsTextbox.TextLength > 0 && !editTagList.Contains(editTagsTextbox.Text.ToLower().Replace(",", "")))
{
editTagList.Add(editTagsTextbox.Text);
}
//Create new TapeDatabaseValues for edited entry
TapeDatabaseValues newTapeValues = new TapeDatabaseValues(
editTapeNameTextbox.Text, editTapeNumberUpDown.Value.ToString(), editProjectIDTextbox.Text, projectNameEdit,
commonMethod.GetCameraNumber(editCameraDropdown.Text), String.Join(",", editTagList), commonMethod.ConvertDateFromDropdownForDB(editDateShotDate.Value),
editTapeMasterListLabel.Text, editPersonDropdown.Text);
//Check if user made a change
if (CompareOldEditValues(newTapeValues))
{
//Update entry and check to make sure it is updated
if (editDB.UpdateTapeDatabase(newTapeValues, tapeValues))
{
//Enrty Update Successful
ClearEditControls();
tapeValues.Clear();
PopulateTapeList();
MakeBoxesVisible();
tapeListListView.Focus();
updateStatus.UpdateStatusBar("Values Updated in Database", mainform);
}
else
{
//Entry Update Unsuccessful
updateStatus.UpdateStatusBar("There Was A Problem Updated Entry", mainform);
}
}
else
{
//User Needs to change a value
updateStatus.UpdateStatusBar("Must Change At Least One Value", mainform);
}
}else
{
//project ID was NOT a number
updateStatus.UpdateStatusBar("Project ID must be a number", mainform);
}
}
/// <summary>
/// Compares the old edit values.
/// </summary>
/// <param name="newValues">The new tape values.</param>
/// <returns>True if they do not match</returns>
private bool CompareOldEditValues(TapeDatabaseValues newValues)
{
//check to see if old and new values match
if(tapeValues.ProjectId.Equals(newValues.ProjectId) && tapeValues.ProjectName.Equals(newValues.ProjectName) &&
tapeValues.TapeName.Equals(newValues.TapeName) && tapeValues.TapeNumber.Equals(newValues.TapeNumber) &&
tapeValues.Camera.Equals(newValues.Camera) && tapeValues.DateShot.Equals(newValues.DateShot) &&
tapeValues.TapeTags.Equals(newValues.TapeTags) && tapeValues.MasterArchive.Equals(newValues.MasterArchive) &&
tapeValues.PersonEntered.Equals(newValues.PersonEntered))
{
return false;
}
else
{
return true;
}
}
/// <summary>
/// Adds the tapes from file.
/// </summary>
/// <param name="worker">The worker.</param>
/// <param name="importStream">The import stream.</param>
/// <param name="ofd">The ofd.</param>
/// <returns>true if succeeded, false if failed</returns>
public static bool AddTapesFromFile(BackgroundWorker worker, Stream importStream, OpenFileDialog ofd)
{
List<TapeDatabaseValues> tapeList = new List<TapeDatabaseValues>();
TapeDatabaseValues tapeValues = new TapeDatabaseValues();
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)
{
newLine = line.Replace("-", ",");
string[] lineArray = newLine.Split(seperators, 7);
//check to make sure there are 2 parts to each line
if (lineArray.Length > 1)
{
lineArray[0] = lineArray[0].Trim();
lineArray[1] = lineArray[1].Replace(',', '-').Trim();
tapeValues = new TapeDatabaseValues(lineArray[0], lineArray[1], lineArray[2], lineArray[3], commonMethod.GetCameraNumber(lineArray[4]), lineArray[6], commonMethod.ConvertDateFromDropdownForDB(Convert.ToDateTime(lineArray[5])), "", "Imported");
tapeList.Add(tapeValues);
}
else
{
//only one part, it will not add this value to database
}
}
}
catch (Exception error)
{
MainForm.LogFile(error.Message);
}
}
int counter = 0;
int progressCounter = 0;
float queryCounter = 100.0f / tapeList.Count;
float progress = 0.0f;
string projectName = "";
try
{
SQLiteConnection projectConnection = new SQLiteConnection(database);
projectConnection.Open();
SQLiteCommand command = new SQLiteCommand(projectConnection);
foreach (TapeDatabaseValues value in tapeList)
{
//list for the master list query
List<string> masterList = new List<string>();
//query db for names of masterlist for a project
command.CommandText = "select master_tape from MasterArchiveVideos where project_id = @pid";
command.Parameters.Clear();
command.Parameters.AddWithValue("@pid", value.ProjectId);
//read results and add values if any to a comma seperated string
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
masterList.Add(reader["master_tape"].ToString());
}
value.MasterArchive = String.Join(",", masterList);
}
}
//query db for the project name
command.CommandText = "select project_name from Projects where project_id = @pid limit 1";
command.Parameters.Clear();
command.Parameters.AddWithValue("@pid", value.ProjectId);
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
projectName = reader["project_name"].ToString();
}
//add value for addition based on if empty or not
if (!projectName.Equals(""))
{
value.ProjectName = projectName;
}
}
}
//add values into tape database table
command.Parameters.Clear();
command.CommandText = "insert into TapeDatabase (tape_name,tape_number,project_id, project_name,camera,tape_tags,date_shot,master_archive,person_entered) values (@tapeName,@tapeNumber,@projectID, @projectName,@camera,@tapeTags,@dateShot,@masterArchive,@person)";
command.Parameters.AddWithValue("@tapeName", value.TapeName);
command.Parameters.AddWithValue("@tapeNumber", value.TapeNumber);
command.Parameters.AddWithValue("@projectID", value.ProjectId);
command.Parameters.AddWithValue("@projectName", value.ProjectName);
command.Parameters.AddWithValue("@camera", value.Camera);
command.Parameters.AddWithValue("@tapeTags", value.TapeTags);
command.Parameters.AddWithValue("@dateShot", value.DateShot);
command.Parameters.AddWithValue("@masterArchive", value.MasterArchive);
command.Parameters.AddWithValue("@person", ComputerInfo.ComputerUser);
if (command.ExecuteNonQuery() == 1)
{
//Success
counter++;
progressCounter++;
if ((progressCounter * queryCounter) >= 1)
{
progress += (queryCounter * progressCounter);
progressCounter = 0;
worker.ReportProgress(Convert.ToInt32(progress));
Debug.WriteLine("Added: " + value.ProjectId + ", Progress = " + progress);
}
}
else
{
//Failure
}
}
if (counter > 0)
{
Debug.WriteLine(counter + " items added to database");
MainForm.LogFile(counter + " tapes added to database");
projectConnection.Close();
return true;
}
else
{
Debug.WriteLine("No Tapes added to database");
projectConnection.Close();
return false;
}
}
catch (SQLiteException e)
{
MainForm.LogFile("Import Tapes Error: " + e.Message);
return false;
}
}
//Delete entry button pressed
private void tapeListDeleteEntryButton_Click(object sender, EventArgs e)
{
//Place delete tape groupbox and make visible
if (tapeListListView.SelectedItems.Count == 1)
{
LoadTapeValuesFromList();
deleteProjectIDLabel.Text = tapeValues.ProjectId;
deleteProjectNameLabel.Text = tapeValues.ProjectName;
deleteTapeNameLabel.Text = tapeValues.TapeName;
deleteTapeNumberLabel.Text = tapeValues.TapeNumber;
deleteCameraLabel.Text = commonMethod.GetCameraName(tapeValues.Camera);
//split csv int a list and display
deleteTagList = tapeValues.TapeTags.Split(',').ToList();
DisplayTags("delete", deleteTagFlowLayoutPanel, deleteTagList);
deleteDateShotLabel.Text = tapeValues.DateShot;
deleteMasterArchiveLabel.Text = tapeValues.MasterArchive;
deletePersonLabel.Text = tapeValues.PersonEntered;
MakeBoxesVisible("delete");
}else if(tapeListListView.SelectedItems.Count > 1)
{
//multiple items selected in listview
//Show message box to make sure user is to be deleted
DialogResult deleteMessage = MessageBox.Show("Do you want to delete these " + tapeListListView.SelectedItems.Count + " entries?", "Deletion Warning!", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
//Check to see if user pressed yes or no
if (deleteMessage == DialogResult.Yes)
{
//clear delete list
if (tapesToDelete == null)
{
tapesToDelete = new List<TapeDatabaseValues>();
}
else
{
tapesToDelete.Clear();
}
//iterate over each item selected and save data in a value, then a list
foreach (ListViewItem item in tapeListListView.SelectedItems)
{
TapeDatabaseValues value = new TapeDatabaseValues(
item.SubItems[2].Text, item.SubItems[3].Text, item.SubItems[0].Text, item.SubItems[1].Text,
commonMethod.GetCameraNumber(item.SubItems[4].Text), item.SubItems[5].Text, item.SubItems[6].Text,
item.SubItems[7].Text, item.SubItems[8].Text, Convert.ToInt32(item.Tag)
);
tapesToDelete.Add(value);
}
if (tapeListListView.SelectedItems.Count > 1 && tapesToDelete.Count > 0)
{
Debug.WriteLine("sending " + tapesToDelete.Count + " items to delete");
updateStatus.UpdateStatusBar(AddToDatabase.DeleteMultipleTapeSelected(tapesToDelete) + " items deleted", mainform);
}
PopulateTapeList();
MakeBoxesVisible();
}
else if (deleteMessage == DialogResult.No)
{
//No Pressed, nothing will be done
}
}
}
/// <summary>
/// Gets all tables from imported database and inserts the information into the default database
/// </summary>
/// <param name="file">The file.</param>
/// <param name="mainForm">The main form.</param>
public static void GetTableName(string file, TNG_Database.MainForm mainForm)
{
List<string> allTables = new List<string>();
Debug.WriteLine("Starting to import");
//new database filename
string fileDatabase = "Data Source=" + file + ";Version=3;";
Debug.WriteLine(fileDatabase);
//progressbar status for each table
int everyTable = 0;
int progress = 0;
try
{
//command to get all tables in database
const string GET_TABLES_QUERY = "select name from sqlite_master where type='table'";
SQLiteConnection connection = new SQLiteConnection(fileDatabase);
connection.Open();
SQLiteCommand command = new SQLiteCommand(GET_TABLES_QUERY, connection);
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
Debug.WriteLine("import has tables");
while (reader.Read())
{
if (TNGTables.Contains(reader.GetString(0)))
{
allTables.Add(reader.GetString(0));
}
}
}
}
//close connection
connection.Close();
}catch(SQLiteException e)
{
MainForm.LogFile("SQLite Error: " + e.Message);
Debug.WriteLine("SQLite Error: " + e.Message);
}
try
{
//check to see if there are any tables returned
if (!allTables.Count.Equals(0))
{
//set progressbar status to division on count of tables
everyTable = 100 / allTables.Count;
Debug.WriteLine("Table Number #: " + everyTable);
AddToDatabase addDB = new AddToDatabase();
foreach (string table in allTables)
{
//connection for new database
SQLiteConnection connect = new SQLiteConnection(fileDatabase);
connect.Open();
SQLiteCommand command = new SQLiteCommand(connect);
command.CommandText = "Select * from " + table;
switch (table)
{
case "TapeDatabase":
case "DeleteTapeDatabase":
//create values object
List<TapeDatabaseValues> tapeList = new List<TapeDatabaseValues>();
TapeDatabaseValues tapeValues = new TapeDatabaseValues();
//execute query
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
tapeValues.Clear();
tapeValues.ProjectId = reader["project_id"].ToString();
tapeValues.ProjectName = reader["project_name"].ToString();
tapeValues.TapeName = reader["tape_name"].ToString();
tapeValues.TapeNumber = reader["tape_number"].ToString();
tapeValues.Camera = Convert.ToInt32(reader["camera"]);
tapeValues.TapeTags = reader["tape_tags"].ToString();
tapeValues.DateShot = reader["date_shot"].ToString();
tapeValues.MasterArchive = reader["master_archive"].ToString();
tapeValues.PersonEntered = reader["person_entered"].ToString();
//add values to list
tapeList.Add(tapeValues);
}
}
}
//Close import connection
connect.Close();
//iterate over values to add to database
foreach (TapeDatabaseValues values in tapeList)
{
if (table == "TapeDatabase")
{
//add value to database
addDB.AddTapeDatabase(values);
}
else if(table == "DeleteTapeDatabase")
{
//Open up new connection
SQLiteConnection deleteConnect = new SQLiteConnection(database);
deleteConnect.Open();
command.Connection = deleteConnect;
command.CommandText = "select count(*) from DeleteTapeDatabase where lower(project_name) = @projectName and project_id = @projectID";
command.Parameters.Clear();
command.Parameters.AddWithValue("@projectID", values.ProjectId);
command.Parameters.AddWithValue("@projectName", values.ProjectName.ToLower());
Int32 check = Convert.ToInt32(command.ExecuteScalar());
//Make sure that entry doesn't exist already
if (check < 1)
{
//add value to deleted database
//There is not an entry go ahead and insert new row
command.CommandText = "insert into DeleteTapeDatabase (tape_name, tape_number, project_id, project_name, camera, tape_tags, date_shot, master_archive, person_entered) values (@tapeName, @tapeNumber, @projectID, @projectName, @camera, @tapeTags, @dateShot, @masterArchive, @personEntered)";
command.Parameters.Clear();
command.Parameters.AddWithValue("@tapeName", values.TapeName);
command.Parameters.AddWithValue("@tapeNumber", values.TapeNumber);
command.Parameters.AddWithValue("@projectID", values.ProjectId);
command.Parameters.AddWithValue("@projectName", values.ProjectName);
command.Parameters.AddWithValue("@camera", values.Camera);
command.Parameters.AddWithValue("@tapeTags", values.TapeTags);
command.Parameters.AddWithValue("@dateShot", values.DateShot);
command.Parameters.AddWithValue("@masterArchive", values.MasterArchive);
command.Parameters.AddWithValue("@personEntered", values.PersonEntered);
command.ExecuteNonQuery();
}
deleteConnect.Close();
}
}
UpdateProgess(everyTable, mainForm);
progress += everyTable;
break;
case "Projects":
case "DeleteProjects":
//create values object
List<ProjectValues> projectList = new List<ProjectValues>();
ProjectValues projectValues = new ProjectValues();
//execute query
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
projectValues.Clear();
projectValues.ProjectID = reader["project_id"].ToString();
projectValues.Projectname = reader["project_name"].ToString();
//add values to list
projectList.Add(projectValues);
}
}
}
//Close import connection
connect.Close();
//iterate over values to add to database
foreach (ProjectValues values in projectList)
{
if (table == "Projects")
{
//add value to database
addDB.AddProjects(values);
}
else if (table == "DeleteProjects")
{
//Open up new connection
SQLiteConnection deleteConnect = new SQLiteConnection(database);
deleteConnect.Open();
command.Connection = deleteConnect;
command.CommandText = "select count(*) from DeleteProjects where lower(project_name) = @projectName and project_id = @projectID";
command.Parameters.Clear();
command.Parameters.AddWithValue("@projectID", values.ProjectID);
command.Parameters.AddWithValue("@projectName", values.Projectname.ToLower());
Int32 check = Convert.ToInt32(command.ExecuteScalar());
//Make sure that entry doesn't exist already
if (check < 1)
{
//add value to deleted database
//There is not an entry go ahead and insert new row
command.CommandText = "insert into DeleteProjects (project_id, project_name) values (@projectID, @projectName)";
command.Parameters.Clear();
command.Parameters.AddWithValue("@projectID", values.ProjectID);
command.Parameters.AddWithValue("@projectName", values.Projectname);
command.ExecuteNonQuery();
}
deleteConnect.Close();
}
}
UpdateProgess(everyTable, mainForm);
progress += everyTable;
break;
case "People":
case "DeletePeople":
//create values object
List<PeopleValues> peopleList = new List<PeopleValues>();
PeopleValues peopleValues = new PeopleValues();
//execute query
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
peopleValues.Clear();
peopleValues.PersonName = reader["person_name"].ToString();
//add values to list
peopleList.Add(peopleValues);
}
}
}
//Close import connection
connect.Close();
//iterate over values to add to database
foreach (PeopleValues values in peopleList)
{
if (table == "People")
{
//add value to database
addDB.AddPerson(values);
}
else if (table == "DeletePeople")
{
//Open up new connection
SQLiteConnection deleteConnect = new SQLiteConnection(database);
deleteConnect.Open();
command.Connection = deleteConnect;
command.CommandText = "select count(*) from DeletePeople where lower(person_name) = @personName";
command.Parameters.Clear();
command.Parameters.AddWithValue("@personName", values.PersonName.ToLower());
Int32 check = Convert.ToInt32(command.ExecuteScalar());
//Make sure that entry doesn't exist already
if (check < 1)
{
//add value to deleted database
//There is not an entry go ahead and insert new row
command.CommandText = "insert into DeletePeole (person_name) values (@personName)";
command.Parameters.Clear();
command.Parameters.AddWithValue("@personName", values.PersonName);
command.ExecuteNonQuery();
}
deleteConnect.Close();
}
}
UpdateProgess(everyTable, mainForm);
progress += everyTable;
break;
case "MasterList":
case "DeleteMasterList":
//create values object
List<MasterListValues> masterTapeList = new List<MasterListValues>();
MasterListValues masterTapeValues = new MasterListValues();
//execute query
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
masterTapeValues.Clear();
masterTapeValues.MasterArchive = reader["master_archive"].ToString();
masterTapeValues.MasterMedia = Convert.ToInt32(reader["master_media"]);
//add values to list
masterTapeList.Add(masterTapeValues);
}
}
}
//Close import connection
connect.Close();
//iterate over values to add to database
foreach (MasterListValues values in masterTapeList)
{
if (table == "MasterList")
{
//add value to database
addDB.AddMasterList(values);
}
else if (table == "DeleteMasterList")
{
//Open up new connection
SQLiteConnection deleteConnect = new SQLiteConnection(database);
deleteConnect.Open();
command.Connection = deleteConnect;
command.CommandText = "select count(*) from DeleteMasterList where lower(master_archive) = @masterArchive and master_media = @masterMedia";
command.Parameters.Clear();
command.Parameters.AddWithValue("@masterArchive", values.MasterArchive.ToLower());
command.Parameters.AddWithValue("@masterMedia", values.MasterMedia);
Int32 check = Convert.ToInt32(command.ExecuteScalar());
//Make sure that entry doesn't exist already
if (check < 1)
{
//add value to deleted database
//There is not an entry go ahead and insert new row
command.CommandText = "insert into DeleteMasterList (master_archive, master_media) values (@masterArchive, @masterMedia)";
command.Parameters.Clear();
command.Parameters.AddWithValue("@masterArchive", values.MasterArchive);
command.Parameters.AddWithValue("@masterMedia", values.MasterMedia);
command.ExecuteNonQuery();
}
deleteConnect.Close();
}
}
UpdateProgess(everyTable, mainForm);
progress += everyTable;
break;
case "MasterArchiveVideos":
case "DeleteMasterArchiveVideos":
//create values object
List<MasterArchiveVideoValues> masterArchiveVideoList = new List<MasterArchiveVideoValues>();
MasterArchiveVideoValues MasterArchiveVideoValues = new MasterArchiveVideoValues();
//execute query
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
MasterArchiveVideoValues.Clear();
MasterArchiveVideoValues.ProjectId = reader["project_id"].ToString();
MasterArchiveVideoValues.VideoName = reader["video_name"].ToString();
MasterArchiveVideoValues.MasterTape = reader["master_tape"].ToString();
MasterArchiveVideoValues.ClipNumber = reader["clip_number"].ToString();
//add values to list
masterArchiveVideoList.Add(MasterArchiveVideoValues);
}
}
}
//Close import connection
connect.Close();
//iterate over values to add to database
foreach (MasterArchiveVideoValues values in masterArchiveVideoList)
{
if (table == "MasterArchiveVideos")
{
//add value to database
addDB.AddMasterArchiveVideo(values);
}
else if (table == "DeleteMasterArchiveVideos")
{
//Open up new connection
SQLiteConnection deleteConnect = new SQLiteConnection(database);
deleteConnect.Open();
command.Connection = deleteConnect;
command.CommandText = "select count(*) from DeleteMasterArchiveVideos where project_id = @projectID and video_name = @videoName and clip_number = @clipNumber and master_tape = @masterTape";
command.Parameters.Clear();
command.Parameters.AddWithValue("@projectID", values.ProjectId);
command.Parameters.AddWithValue("@videoName", values.VideoName);
command.Parameters.AddWithValue("@masterTape", values.MasterTape);
command.Parameters.AddWithValue("@clipNumber", values.ClipNumber);
Int32 check = Convert.ToInt32(command.ExecuteScalar());
//Make sure that entry doesn't exist already
if (check < 1)
{
//add value to deleted database
//There is not an entry go ahead and insert new row
command.CommandText = "insert into DeleteMasterArchiveVideos (project_id, video_name, master_tape, clip_number) values (@projectID, @videoName, @masterTape, @clipNumber)";
command.Parameters.Clear();
command.Parameters.AddWithValue("@projectID", values.ProjectId);
command.Parameters.AddWithValue("@videoName", values.VideoName);
command.Parameters.AddWithValue("@masterTape", values.MasterTape);
command.Parameters.AddWithValue("@clipNumber", values.ClipNumber);
command.ExecuteNonQuery();
}
deleteConnect.Close();
}
}
UpdateProgess(everyTable, mainForm);
progress += everyTable;
break;
case "ComputerInfo":
//create values object
List<ComputerInfoValues> computerInfoList = new List<ComputerInfoValues>();
ComputerInfoValues computerInfoValues = new ComputerInfoValues();
//execute query
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
computerInfoValues.Clear();
computerInfoValues.UniqueHash = reader["computer_hash"].ToString();
computerInfoValues.ComputerName = reader["computer_name"].ToString();
computerInfoValues.ComputerUser = reader["computer_user"].ToString();
//add values to list
computerInfoList.Add(computerInfoValues);
}
}
}
//Close import connection
connect.Close();
//iterate over values to add to database
foreach (ComputerInfoValues values in computerInfoList)
{
//add value to database
addDB.AddComputerInfo(values);
}
UpdateProgess(everyTable, mainForm);
progress += everyTable;
break;
}
}
}
}catch(SQLiteException e)
{
MainForm.LogFile("SQLite Error: " + e.Message);
Debug.WriteLine("SQLite Table Error: " + e.Message);
}
if((100 - progress) > 0)
{
UpdateProgess((100-progress), mainForm);
}
}