//Add Groupbox Add button clicked
private void addMasterListAddButton_Click(object sender, EventArgs e)
{
//Add Entry button pressed, gather info and add entry
if(addMasterListNameTextbox.Text.Length > 0)
{
AddToDatabase database = new AddToDatabase();
sendValues = new MasterListValues(addMasterListNameTextbox.Text, commonMethod.GetCameraNumber(cameraAddMasterCombo.Text));
if (database.AddMasterList(sendValues))
{
//Add entry success
updateStatus.UpdateStatusBar("New Master Tape added to database", mainform);
addMasterListNameTextbox.Clear();
cameraAddMasterCombo.SelectedIndex = 0;
MakeGroupboxesInvisible();
PopulateMasterList();
}
else
{
//Add Entry failure
updateStatus.UpdateStatusBar("Entry was not added to database", mainform);
}
}
}
//Delete button pressed
private void masterListDeleteButton_Click(object sender, EventArgs e)
{
if(masterListListBox.SelectedItems.Count == 1)
{
//Make every other groupbox invisible
MakeGroupboxesInvisible("delete");
//Label Name and Camera of item selected
LabelSelectedItem(deleteMasterNameMasterListLabel, deleteCameraNameMasterListLabel);
//Make delete groupbox visible
deleteMasterListGroupBox.Visible = true;
}else if(masterListListBox.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 " + masterListListBox.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 (masterListValues == null)
{
masterListValues = new List<MasterListValues>();
}
else
{
masterListValues.Clear();
}
//iterate over each item selected and save data in a value, then a list
foreach (var item in masterListListBox.SelectedItems)
{
MasterListValues value = new MasterListValues(item.ToString(), GetCameraName(item.ToString()));
masterListValues.Add(value);
}
if (masterListListBox.SelectedItems.Count > 1 && masterListValues.Count > 0)
{
Debug.WriteLine("sending " + masterListValues.Count + " people to delete");
updateStatus.UpdateStatusBar(AddToDatabase.DeleteMultipleMasterListselected(masterListValues) + " lists deleted", mainform);
}
PopulateMasterList();
MakeGroupboxesInvisible("default");
masterListDeleteButton.Enabled = false;
masterListDeleteButton.Text = "Delete";
}
else if (deleteMessage == DialogResult.No)
{
//No Pressed, nothing will be done
}
}
}
/// <summary>
/// Get name and camera for selected item and make label to reflect that
/// </summary>
/// <param name="nameLabel"></param>
/// <param name="cameraLabel"></param>
private void LabelSelectedItem(Label nameLabel, Label cameraLabel)
{
//check to make sure masterList is not empty
if (masterList != null)
{
//Iterate through master list
foreach (MasterListValues item in masterList)
{
//If name in master list matches item selected, then get name and camera info
if (masterListListBox.GetItemText(masterListListBox.SelectedItem).Equals(item.MasterArchive))
{
sendValues = item;
nameLabel.Text = item.MasterArchive;
cameraLabel.Text = commonMethod.GetCameraName(Convert.ToInt32(item.MasterMedia));
}
}
}
else
{
//Update Application Status
updateStatus.UpdateStatusBar("There was a problem", mainform);
}
}
//Edit Groupbox edit button pressed
private void editMasterEditButton_Click(object sender, EventArgs e)
{
//Edit Entry button pressed, gather info and update entry
//check to make sure something is entered in the textbox
if(editNewNameMasterTextbox.Text.Length > 0)
{
AddToDatabase database = new AddToDatabase();
//Create MasterListValues of old info
MasterListValues oldValues = new MasterListValues(sendValues.MasterArchive, sendValues.MasterMedia, sendValues.ID);
//Create MasterListValues of old info
MasterListValues newValues = new MasterListValues(editNewNameMasterTextbox.Text, commonMethod.GetCameraNumber(editCameraNewMasterDropdown.GetItemText(editCameraNewMasterDropdown.SelectedItem)));
//Send to update method in AddToDatabase class & check if successful
if(database.UpdateMasterList(oldValues, newValues))
{
//update successful
updateStatus.UpdateStatusBar("Update of Master Tape successful", mainform);
//Clear items and close groupbox
editNewNameMasterTextbox.Clear();
editCameraNewMasterDropdown.SelectedIndex = 0;
MakeGroupboxesInvisible();
//Load new database info into listbox
PopulateMasterList();
}else
{
//update failed
updateStatus.UpdateStatusBar("Update Error", mainform);
}
}
}
//Delete Groupbox Delete button clicked
private void deleteMasterListDeleteButton_Click(object sender, EventArgs e)
{
//Delete button pressed, gather info and delete entry
//Show message box to make sure user is to be deleted
DialogResult deleteMessage = MessageBox.Show("Do you want to delete " + deleteMasterNameMasterListLabel.Text + "?", "Deletion Warning!", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
//Check to see if user pressed yes or no
if (deleteMessage == DialogResult.Yes)
{
//Yes Pressed, delete user from DB
Debug.WriteLine("Yes Pressed for deletion");
AddToDatabase deleteDB = new AddToDatabase();
int _id = 0;
foreach(MasterListValues item in masterList)
{
if (item.MasterArchive.Equals(deleteMasterNameMasterListLabel.Text))
{
_id = item.ID;
}
}
MasterListValues values = new MasterListValues(deleteMasterNameMasterListLabel.Text, commonMethod.GetCameraDropdownIndex(deleteCameraNameMasterListLabel.Text),_id);
//Delete user from database
if (deleteDB.DeleteMasterList(values))
{
//deletion success
updateStatus.UpdateStatusBar(deleteMasterNameMasterListLabel.Text + " deleted!", mainform);
MakeGroupboxesInvisible("delete");
PopulateMasterList();
}
else
{
updateStatus.UpdateStatusBar("There was an error deleting " + deleteMasterNameMasterListLabel.Text, mainform);
MakeGroupboxesInvisible("delete");
}
}
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);
}
}
