private void searchCode_MouseLeftButtonUp(object sender, MouseButtonEventArgs e)
{
if (string.IsNullOrEmpty(txtSubjCode.Text))
{
MessageBox.Show("Subject code field is empty!");
}
else
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT COUNT(1) from Subjects where subjCode = @subjCode", conn))
{
cmd.Parameters.AddWithValue("@subjCode", txtSubjCode.Text);
int subjCount;
subjCount = (int)cmd.ExecuteScalar();
if (subjCount > 0)
{
string subjCode = txtSubjCode.Text;
using (SqlCeCommand cmd1 = new SqlCeCommand("SELECT * from Subjects where subjCode = @subjCode", conn))
{
cmd1.Parameters.AddWithValue("@subjCode", subjCode);
using (SqlCeDataReader reader = cmd1.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.HasRows)
{
string subjName = null;
while (reader.Read())
{
int subjNameIndex = reader.GetOrdinal("subjName");
subjName = Convert.ToString(reader.GetValue(subjNameIndex));
int nameIndex = reader.GetOrdinal("name");
string name = Convert.ToString(reader.GetValue(nameIndex));
int sizeIndex = reader.GetOrdinal("size");
string size = Convert.ToString(reader.GetValue(sizeIndex));
int qtyIndex = reader.GetOrdinal("qty");
int qty = Convert.ToInt32(reader.GetValue(qtyIndex));
items.Add(new LVApparatusStockOut
{
i = i,
inventName = name,
size = size,
qty = qty,
});
i++;
}
txtSubjName.Text = subjName;
}
}
}
}
else
{
MessageBox.Show("Subject does not exist or is not in the database!");
}
}
}
}
private void btnSave_Click(object sender, RoutedEventArgs e)
{
if (string.IsNullOrEmpty(txtSubjCode.Text) || string.IsNullOrEmpty(txtSubjName.Text))
{
MessageBox.Show("One or more fields are empty!");
}
else if (lvApparatus.Items.Count == 0)
{
MessageBox.Show("There are no apparatuses!");
}
else
{
string sMessageBoxText = "Are all fields correct?";
string sCaption = "Add Subject";
MessageBoxButton btnMessageBox = MessageBoxButton.YesNoCancel;
MessageBoxImage icnMessageBox = MessageBoxImage.Warning;
MessageBoxResult dr = MessageBox.Show(sMessageBoxText, sCaption, btnMessageBox, icnMessageBox);
switch (dr)
{
case MessageBoxResult.Yes:
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
string subjCode = txtSubjCode.Text;
string subjName = txtSubjName.Text;
foreach (var row in items)
{
using (SqlCeCommand cmd = new SqlCeCommand("INSERT into Subjects (subjCode, subjName, name, size, qty) VALUES (@subjCode, @subjName, @name, @size, @qty)", conn))
{
cmd.Parameters.AddWithValue("@subjCode", subjCode);
cmd.Parameters.AddWithValue("@subjName", subjName);
cmd.Parameters.AddWithValue("@name", row.inventName);
if (string.IsNullOrWhiteSpace(row.size))
{
cmd.Parameters.AddWithValue("@size", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@size", row.size);
}
cmd.Parameters.AddWithValue("@qty", row.qty);
try
{
cmd.ExecuteNonQuery();
check = true;
}
catch (SqlCeException ex)
{
MessageBox.Show("Error! Log has been updated with the error.");
Log = LogManager.GetLogger("*");
Log.Error(ex, "Query Error");
}
}
}
if (check == true)
{
MessageBox.Show("Subject Added!");
Log = LogManager.GetLogger("addSubject");
Log.Info("Subject " + txtSubjName.Text + " has been added to database");
items.Clear();
emptyFields();
}
break;
case MessageBoxResult.No: break;
}
}
}
private void btnDelete_Click(object sender, RoutedEventArgs e)
{
if (string.IsNullOrEmpty(txtSubjCode.Text) || string.IsNullOrEmpty(txtSubjName.Text))
{
MessageBox.Show("One or more fields are empty!");
}
else
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT COUNT(1) from Subjects where subjCode = @subjCode", conn))
{
cmd.Parameters.AddWithValue("@subjCode", txtSubjCode.Text);
int subjCount;
subjCount = (int)cmd.ExecuteScalar();
if (subjCount > 0)
{
string sMessageBoxText = "Do you want to delete the subject?";
string sCaption = "Delete Subject";
MessageBoxButton btnMessageBox = MessageBoxButton.YesNoCancel;
MessageBoxImage icnMessageBox = MessageBoxImage.Warning;
MessageBoxResult dr = MessageBox.Show(sMessageBoxText, sCaption, btnMessageBox, icnMessageBox);
switch (dr)
{
case MessageBoxResult.Yes:
using (SqlCeCommand cmd1 = new SqlCeCommand("INSERT into ArchivedSubjects (subjCode, subjName) select TOP 1 subjCode, subjName from Subjects where subjCode = @subjCode", conn))
{
cmd1.Parameters.AddWithValue("@subjCode", txtSubjCode.Text);
int result = cmd1.ExecuteNonQuery();
if (result == 1)
{
using (SqlCeCommand command = new SqlCeCommand("DELETE from Subjects where subjCode= @subjCode", conn))
{
command.Parameters.AddWithValue("@subjCode", txtSubjCode.Text);
command.ExecuteNonQuery();
Log = LogManager.GetLogger("deleteSubject");
Log.Info("Subject " + txtSubjName.Text + " has been deleted");
MessageBox.Show("Subject has been deleted!");
emptyAppa();
emptyFields();
}
}
else
{
MessageBox.Show("Subject does not exist1!");
}
}
emptyFields();
break;
case MessageBoxResult.No: break;
}
}
else
{
MessageBox.Show("Subject does not exist!");
}
}
}
}
private void btnDelete_Click(object sender, RoutedEventArgs e)
{
if (string.IsNullOrEmpty(txtUser.Text))
{
MessageBox.Show("Username field is empty!");
}
else
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("Select COUNT(1) from Accounts where username = @username", conn))
{
cmd.Parameters.AddWithValue("@username", txtUser.Text);
int userCount;
userCount = (int)cmd.ExecuteScalar();
if (userCount > 0)
{
string sMessageBoxText = "Do you want to delete the account?";
string sCaption = "Delete Account";
MessageBoxButton btnMessageBox = MessageBoxButton.YesNoCancel;
MessageBoxImage icnMessageBox = MessageBoxImage.Warning;
MessageBoxResult dr = MessageBox.Show(sMessageBoxText, sCaption, btnMessageBox, icnMessageBox);
switch (dr)
{
case MessageBoxResult.Yes:
using (SqlCeCommand cmd1 = new SqlCeCommand("INSERT into ArchivedAccounts (firstName, lastName, username, password, securityQuestion, answer) select firstName, lastName, username, password, securityQuestion, answer from Accounts where username = @username", conn))
{
cmd1.Parameters.AddWithValue("@username", txtUser.Text);
try
{
cmd1.ExecuteNonQuery();
using (SqlCeCommand command = new SqlCeCommand("DELETE from Accounts where username= @username", conn))
{
command.Parameters.AddWithValue("@username", txtUser.Text);
command.ExecuteNonQuery();
MessageBox.Show("Account has been deleted!");
Log = LogManager.GetLogger("deleteAccount");
Log.Info("Account " + txtUser.Text + " has been deleted!");
emptyFields();
}
}
catch (SqlCeException ex)
{
MessageBox.Show("Error! Log has been updated with the error.");
Log = LogManager.GetLogger("*");
Log.Error(ex, "Query Error");
}
}
break;
case MessageBoxResult.No:
break;
}
}
else
{
MessageBox.Show("User does not exist!");
}
}
}
}
private void btnSave_Click(object sender, RoutedEventArgs e)
{
Regex reg = new Regex("^(?=.*[a-z])(?=.*[A-Z])(?=.*\\d)[a-zA-Z\\d]{8,}$");
bool result = reg.IsMatch(txtPass.Password.ToString());
if (result)
{
if (string.IsNullOrEmpty(txtUser.Text) || string.IsNullOrEmpty(txtFirstName.Text) || string.IsNullOrEmpty(txtLastName.Text) || string.IsNullOrEmpty(cmbQuestion.Text) || string.IsNullOrEmpty(txtAns.Password) || string.IsNullOrEmpty(txtConfirmPass.Password) || string.IsNullOrEmpty(txtPass.Password) || string.IsNullOrEmpty(txtPass.Password))
{
MessageBox.Show("One or more fields are empty!");
}
else
{
if (txtPass.Password.Equals(txtConfirmPass.Password))
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("Select COUNT(1) from Accounts where username = @username", conn))
{
cmd.Parameters.AddWithValue("@username", txtUser.Text);
int userCount;
userCount = (int)cmd.ExecuteScalar();
if (userCount > 0)
{
MessageBox.Show("User already exist!");
}
else
{
string sMessageBoxText = "Are all fields correct?";
string sCaption = "Add Account";
MessageBoxButton btnMessageBox = MessageBoxButton.YesNoCancel;
MessageBoxImage icnMessageBox = MessageBoxImage.Warning;
MessageBoxResult dr = MessageBox.Show(sMessageBoxText, sCaption, btnMessageBox, icnMessageBox);
switch (dr)
{
case MessageBoxResult.Yes:
using (SqlCeCommand cmd1 = new SqlCeCommand("INSERT into Accounts (firstName, lastName, username, password, securityQuestion, answer, tries, userLevel) VALUES (@firstName, @lastName, @username, @password, @securityQuestion, @answer, 0, @userLevel)", conn))
{
cmd1.Parameters.AddWithValue("@firstName", txtFirstName.Text);
cmd1.Parameters.AddWithValue("@lastName", txtLastName.Text);
cmd1.Parameters.AddWithValue("@username", txtUser.Text);
cmd1.Parameters.AddWithValue("@password", txtPass.Password);
cmd1.Parameters.AddWithValue("@securityQuestion", cmbQuestion.Text);
cmd1.Parameters.AddWithValue("@answer", txtAns.Password);
if (cmbUserLevel.Text.Equals("Administrator"))
{
cmd1.Parameters.AddWithValue("@userLevel", 0);
}
else
{
cmd1.Parameters.AddWithValue("@userLevel", 1);
}
try
{
cmd1.ExecuteNonQuery();
MessageBox.Show("Registered successfully");
Log = LogManager.GetLogger("registerAccount");
Log.Info("Account " + txtUser.Text + " has been registered!");
emptyFields();
}
catch (SqlCeException ex)
{
MessageBox.Show("Error! Log has been updated with the error.");
Log = LogManager.GetLogger("*");
Log.Error(ex, "Query Error");
}
}
break;
case MessageBoxResult.No:
break;
}
}
}
}
else
{
MessageBox.Show("Your password and confirmation password do not match.");
}
}
}
else
{
MessageBox.Show("Password is Invalid");
}
}
private void btnAdd_Click(object sender, RoutedEventArgs e)
{
if (string.IsNullOrEmpty(cmbInventName.Text) || string.IsNullOrEmpty(txtQty.Text) || string.IsNullOrEmpty(cmbManuf.Text))
{
MessageBox.Show("One or more fields are empty!");
}
else if (cmbInventName.Text.Length > 0 && cmbSize.Items.Count > 0 && string.IsNullOrEmpty(cmbSize.Text))
{
MessageBox.Show("Please select size!");
cmbSize.Focus();
}
else if (!string.IsNullOrEmpty(cmbSize.Text) && !string.IsNullOrEmpty(cmbInventName.Text) && !string.IsNullOrEmpty(cmbManuf.Text) && !string.IsNullOrEmpty(txtQty.Text))
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT * from ApparatusInventory where name = @inventName and manuf = @manuf and size = @size", conn))
{
cmd.Parameters.AddWithValue("@inventName", cmbInventName.Text);
cmd.Parameters.AddWithValue("@manuf", cmbManuf.Text);
cmd.Parameters.AddWithValue("@size", cmbSize.Text);
using (DbDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
reader.Read();
int qtyIndex = reader.GetOrdinal("qty");
int qty = Convert.ToInt32(reader.GetValue(qtyIndex));
int manufIndex = reader.GetOrdinal("manuf");
string manuf = Convert.ToString(reader.GetValue(manufIndex));
int sizeIndex = reader.GetOrdinal("size");
string size = Convert.ToString(reader.GetValue(sizeIndex));
int reqQty = Convert.ToInt32(txtQty.Text);
if (reqQty > qty)
{
MessageBox.Show("Requested quantity cannot be greater than the available quantity!");
return;
}
else
{
var found = stockOut.FirstOrDefault(x => (x.inventName == cmbInventName.Text) && (x.manuf == cmbManuf.Text) && ((x.size == cmbSize.Text) || (x.size == null)));
if (found != null)
{
if (found.qty + reqQty > qty)
{
MessageBox.Show("Requested quantity cannot be greater than the available quantity!");
return;
}
else
{
found.qty = found.qty + reqQty;
}
}
else
{
stockOut.Add(new LVApparatusStockOut
{
i = i,
inventName = cmbInventName.Text,
qty = reqQty,
manuf = manuf,
size = size
});
i++;
}
emptyFields();
}
}
}
}
else
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT * from ApparatusInventory where name = @inventName and manuf = @manuf", conn))
{
cmd.Parameters.AddWithValue("@inventName", cmbInventName.Text);
cmd.Parameters.AddWithValue("@manuf", cmbManuf.Text);
using (DbDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
reader.Read();
int qtyIndex = reader.GetOrdinal("qty");
int qty = Convert.ToInt32(reader.GetValue(qtyIndex));
int manufIndex = reader.GetOrdinal("manuf");
string manuf = Convert.ToString(reader.GetValue(manufIndex));
string size = null;
int reqQty = Convert.ToInt32(txtQty.Text);
if (reqQty > qty)
{
MessageBox.Show("Requested quantity cannot be greater than the available quantity!");
return;
}
else
{
var found = stockOut.FirstOrDefault(x => (x.inventName == cmbInventName.Text) && (x.manuf == cmbManuf.Text) && (x.size == cmbSize.Text));
if (found != null)
{
if (found.qty + reqQty > qty)
{
MessageBox.Show("Requested quantity cannot be greater than the available quantity!");
return;
}
else
{
found.qty = found.qty + reqQty;
}
}
else
{
stockOut.Add(new LVApparatusStockOut
{
i = i,
inventName = cmbInventName.Text,
qty = reqQty,
manuf = manuf,
size = size
});
i++;
}
emptyFields();
}
}
}
}
}
private void btnStockOut_Click(object sender, RoutedEventArgs e)
{
if (lvAppaStockOut.Items.Count == 0)
{
MessageBox.Show("There are no apparatus(es) to be stock out");
}
//else if (string.IsNullOrEmpty(txtDate.Text) || string.IsNullOrEmpty(txtGroup.Text) || string.IsNullOrEmpty(cmbSubject.Text) || string.IsNullOrEmpty(txtExperiment.Text) || string.IsNullOrEmpty(txtLocker.Text) || string.IsNullOrEmpty(txtDateExp.Text))
//{
// MessageBox.Show("One or more fields are empty!");
//}
//else if (string.IsNullOrEmpty(txtStud1.Text) || String.IsNullOrEmpty(txtName1.Text))
//{
// MessageBox.Show("Student Info fields are empty!");
//}
else
{
string sMessageBoxText = "Are all fields correct?";
string sCaption = "Stock Out";
MessageBoxButton btnMessageBox = MessageBoxButton.YesNoCancel;
MessageBoxImage icnMessageBox = MessageBoxImage.Warning;
MessageBoxResult dr = MessageBox.Show(sMessageBoxText, sCaption, btnMessageBox, icnMessageBox);
switch (dr)
{
case MessageBoxResult.Yes:
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
bool check = false;
//studInfo.Add(new StudentInfo
//{
// studName = txtName1.Text,
// studNo = txtStud1.Text
//});
if (string.IsNullOrEmpty(txtStud2.Text) && txtName2.Text.Length > 0)
{
MessageBox.Show("Please fill up the missing student field!");
txtStud2.Focus();
return;
}
else if (string.IsNullOrEmpty(txtName2.Text) && txtStud2.Text.Length > 0)
{
MessageBox.Show("Please fill up the missing student field!");
txtName2.Focus();
return;
}
else if (txtName2.Text.Length > 0 && txtStud2.Text.Length > 0)
{
studInfo.Add(new StudentInfo
{
studName = txtName2.Text,
studNo = txtStud2.Text
});
}
if (string.IsNullOrEmpty(txtStud3.Text) && txtName3.Text.Length > 0)
{
MessageBox.Show("Please fill up the missing student field!");
txtStud3.Focus();
return;
}
else if (string.IsNullOrEmpty(txtName3.Text) && txtStud3.Text.Length > 0)
{
MessageBox.Show("Please fill up the missing student field!");
txtName3.Focus();
return;
}
else if (txtName3.Text.Length > 0 && txtStud3.Text.Length > 0)
{
studInfo.Add(new StudentInfo
{
studName = txtName3.Text,
studNo = txtStud3.Text
});
}
if (string.IsNullOrEmpty(txtStud4.Text) && txtName4.Text.Length > 0)
{
MessageBox.Show("Please fill up the missing student field!");
txtStud4.Focus();
return;
}
else if (string.IsNullOrEmpty(txtName4.Text) && txtStud4.Text.Length > 0)
{
MessageBox.Show("Please fill up the missing student field!");
txtName4.Focus();
return;
}
else if (txtName4.Text.Length > 0 && txtStud4.Text.Length > 0)
{
studInfo.Add(new StudentInfo
{
studName = txtName4.Text,
studNo = txtStud4.Text
});
}
string date = txtDate.Text.Replace("/", "-");
string temp = @"C:\Users\" + user + @"\Desktop\[" + date + "][" + cmbSubject.Text + "][" + txtExperiment.Text + "][" + txtGroup.Text + "][" + txtLocker.Text + "].docx";
temp = temp.Replace(" ", "-");
object filename = temp;
try
{
//START INSTANCE OF WORD AND SET MARGIN TO .50
object oMissing = Missing.Value;
Word.Application oWord = new Word.Application();
Word.Document oDoc = null;
object format = System.AppDomain.CurrentDomain.BaseDirectory + @"resources\BFTemplate.docx";
object readOnly = false;
object isVisible = false;
oWord.Visible = false;
oDoc = oWord.Documents.Open(ref format, ref oMissing, ref readOnly,
ref oMissing, ref oMissing, ref oMissing,
ref oMissing, ref oMissing, ref oMissing,
ref oMissing, ref oMissing, ref oMissing,
ref oMissing, ref oMissing, ref oMissing, ref oMissing);
oDoc.Activate();
//FIND AND REPLACE
this.findAndReplace(oWord, "<SUBJ>", cmbSubject.Text);
this.findAndReplace(oWord, "<SECT>", txtSect.Text);
this.findAndReplace(oWord, "<SCHED>", txtSched.Text);
this.findAndReplace(oWord, "<LOCKER>", txtLocker.Text);
this.findAndReplace(oWord, "<DATEREQ>", txtDate.Text);
this.findAndReplace(oWord, "<DATEEXP>", txtDateExp.Text);
this.findAndReplace(oWord, "<GROUPNO>", txtGroup.Text);
this.findAndReplace(oWord, "<EXPNAME>", txtExperiment.Text);
//REPLACE QUANTITY, UNIT AND DESC
int i = 1;
foreach (var row in stockOut)
{
this.findAndReplace(oWord, "<Q" + i + ">", row.qty);
this.findAndReplace(oWord, "<U" + i + ">", Regex.Replace(row.size + "0", @"[\d-]", string.Empty));
this.findAndReplace(oWord, "<DESC" + i + ">", row.inventName + "[" + row.manuf + "][" + row.size + "]");
i++;
//using (SqlCeCommand cmd = new SqlCeCommand("UPDATE ApparatusInventory set qty = qty - @qty where name = @inventName and (size IS null or size = @size) and manuf = @manuf", conn))
//{
// cmd.Parameters.AddWithValue("@qty", row.qty);
// cmd.Parameters.AddWithValue("@inventName", row.inventName);
// cmd.Parameters.AddWithValue("@manuf", row.manuf);
// if (!string.IsNullOrEmpty(row.size))
// {
// cmd.Parameters.AddWithValue("@size", row.size);
// }
// else
// {
// row.size = "";
// cmd.Parameters.AddWithValue("@size", row.size);
// }
// try
// {
// cmd.ExecuteNonQuery();
// check = true;
// int ordinal = 0;
// string prodCode = null;
// using (SqlCeCommand cmd2 = new SqlCeCommand("SELECT prodCode from ApparatusInventory where name = @inventName and manuf = @manuf", conn))
// {
// cmd2.Parameters.AddWithValue("@inventName", row.inventName);
// cmd2.Parameters.AddWithValue("@manuf", row.manuf);
// DbDataReader result = cmd2.ExecuteResultSet(ResultSetOptions.Scrollable);
// if (result.Read())
// {
// ordinal = result.GetOrdinal("prodCode");
// prodCode = Convert.ToString(result.GetValue(ordinal));
// }
// }
// int x = 1;
// foreach (var student in studInfo)
// {
//this.findAndReplace(oWord, "<N" + x + ">", x );
//this.findAndReplace(oWord, "<NAME" + x + ">", txtName1.Text);
//this.findAndReplace(oWord, "<STUD" + x + ">", txtStud1.Text);
// using (SqlCeCommand cmd1 = new SqlCeCommand("INSERT into BorrowerList (dateReq, dateExp, studentNo, fullName, groupID, lockNo ,subject, expName ,prodCode, qty, breakage) VALUES (@dateReq, @dateExp, @studentNo, @fullName, @groupID, @lockNo, @subject, @expName ,@prodCode, @qty, 0)", conn))
// {
// cmd1.Parameters.AddWithValue("@dateReq", txtDate.Text);
// cmd1.Parameters.AddWithValue("@dateExp", txtDateExp.Text);
// cmd1.Parameters.AddWithValue("@studentNo", student.studNo);
// cmd1.Parameters.AddWithValue("@fullName", student.studName);
// cmd1.Parameters.AddWithValue("@groupID", txtGroup.Text);
// cmd1.Parameters.AddWithValue("@subject", cmbSubject.Text);
// cmd1.Parameters.AddWithValue("@lockNo", txtLocker.Text);
// cmd1.Parameters.AddWithValue("@expName", txtExperiment.Text);
// cmd1.Parameters.AddWithValue("@prodCode", prodCode);
// cmd1.Parameters.AddWithValue("@qty", row.qty);
// try
// {
// cmd1.ExecuteNonQuery();
// }
// catch (SqlCeException ex)
// {
// MessageBox.Show("Error! Log has been updated with the error.");
// Log = LogManager.GetLogger("*");
// Log.Error(ex, "Query Error");
// }
// }
// }
// }
// catch (SqlCeException ex)
// {
// MessageBox.Show("Error! Log has been updated with the error.");
// Log = LogManager.GetLogger("*");
// Log.Error(ex, "Query Error");
// }
//}
}
oDoc.SaveAs(ref filename, ref oMissing, ref oMissing, ref oMissing,
ref oMissing, ref oMissing, ref oMissing,
ref oMissing, ref oMissing, ref oMissing,
ref oMissing, ref oMissing, ref oMissing,
ref oMissing, ref oMissing, ref oMissing);
oDoc.Close(ref oMissing, ref oMissing, ref oMissing);
//END
//oWord.Documents[filename].Save();
//Process.Start("WINWORD.EXE", filename);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
if (check == true)
{
MessageBox.Show("Stock Out Successfully");
Log = LogManager.GetLogger("generateBorrowerForm");
string newLine = System.Environment.NewLine;
Log.Info("A Borrower form has been generated with the following details: " + newLine +
"Date Requested: " + txtDate.Text + newLine +
"Date of Experiment" + txtDateExp.Text + newLine +
"Subject: " + cmbSubject.Text + newLine +
"Experiment Title: " + txtExperiment.Text + newLine +
"Locker No.: " + txtLocker.Text + newLine +
"Group No: " + txtGroup.Text
);
}
studInfo.Clear();
stockOut.Clear();
i = 1;
emptyFields();
break;
case MessageBoxResult.No: break;
}
}
}
private void txtInventName_TextChanged(object sender, TextChangedEventArgs e)
{
if (string.IsNullOrEmpty(txtInventName.Text))
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT * from ApparatusInventory", conn))
{
i = 1;
summary.Clear();
using (SqlCeDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
while (reader.Read())
{
int prodCodeIndex = reader.GetOrdinal("prodCode");
string prodCode = Convert.ToString(reader.GetValue(prodCodeIndex));
int inventNameIndex = reader.GetOrdinal("name");
string inventName = Convert.ToString(reader.GetValue(inventNameIndex));
int manufIndex = reader.GetOrdinal("manuf");
string manuf = Convert.ToString(reader.GetValue(manufIndex));
int qtyIndex = reader.GetOrdinal("qty");
int qty = Convert.ToInt32(reader.GetValue(qtyIndex));
int sizeIndex = reader.GetOrdinal("size");
string size = Convert.ToString(reader.GetValue(sizeIndex));
summary.Add(new LVOutstanding
{
i = i,
prodCode = prodCode,
inventName = inventName,
manuf = manuf,
qty = qty.ToString(),
size = size,
});
i++;
}
}
}
}
else
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT * from ApparatusInventory where name LIKE @name", conn))
{
cmd.Parameters.AddWithValue("@name", "%" + txtInventName.Text + "%");
i = 1;
summary.Clear();
using (SqlCeDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
while (reader.Read())
{
int prodCodeIndex = reader.GetOrdinal("prodCode");
string prodCode = Convert.ToString(reader.GetValue(prodCodeIndex));
int inventNameIndex = reader.GetOrdinal("name");
string inventName = Convert.ToString(reader.GetValue(inventNameIndex));
int manufIndex = reader.GetOrdinal("manuf");
string manuf = Convert.ToString(reader.GetValue(manufIndex));
int qtyIndex = reader.GetOrdinal("qty");
int qty = Convert.ToInt32(reader.GetValue(qtyIndex));
int sizeIndex = reader.GetOrdinal("size");
string size = Convert.ToString(reader.GetValue(sizeIndex));
summary.Add(new LVOutstanding
{
i = i,
prodCode = prodCode,
inventName = inventName,
manuf = manuf,
qty = qty.ToString(),
size = size,
});
i++;
}
}
}
}
}
private ObservableCollection <LVIssuance> LoadCollectionData()
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
list.Clear();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT DISTINCT lockNo, prof, sched, section, issuedDate, issuedBy from IssuanceList where subject = @subjName", conn))
{
cmd.Parameters.AddWithValue("@subjName", cmbSubject.Text);
using (DbDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.HasRows)
{
while (reader.Read())
{
int lockNoIndex = reader.GetOrdinal("lockNo");
string lockNo = Convert.ToString(reader.GetValue(lockNoIndex));
int profIndex = reader.GetOrdinal("prof");
string prof = Convert.ToString(reader.GetValue(profIndex));
int schedIndex = reader.GetOrdinal("sched");
string sched = Convert.ToString(reader.GetValue(schedIndex));
int sectionIndex = reader.GetOrdinal("section");
string section = Convert.ToString(reader.GetValue(sectionIndex));
int issuedDateIndex = reader.GetOrdinal("issuedDate");
DateTime temp = reader.GetDateTime(issuedDateIndex);
string issuedDate = temp.ToString("MM/dd/yyyy");
int issuedByIndex = reader.GetOrdinal("issuedBy");
string issuedBy = Convert.ToString(reader.GetValue(issuedByIndex));
using (SqlCeCommand cmd1 = new SqlCeCommand("SELECT TOP 1 studentNo, fullName from IssuanceList where section = @section and subject = @subject and prof = @prof and lockNo = @lockNo and DATEDIFF(day, issuedDate, @issuedDate) = 0 and sched = @sched", conn))
{
cmd1.Parameters.AddWithValue("@prof", prof);
cmd1.Parameters.AddWithValue("@lockNo", lockNo);
cmd1.Parameters.AddWithValue("@subject", cmbSubject.Text);
cmd1.Parameters.AddWithValue("@issuedDate", issuedDate);
cmd1.Parameters.AddWithValue("@section", section);
cmd1.Parameters.AddWithValue("@sched", sched);
using (DbDataReader rd = cmd1.ExecuteResultSet(ResultSetOptions.Scrollable))
{
rd.Read();
int studentNoIndex = rd.GetOrdinal("studentNo");
string studentNo = Convert.ToString(rd.GetValue(studentNoIndex));
int fullNameIndex = rd.GetOrdinal("fullName");
string fullName = Convert.ToString(rd.GetValue(fullNameIndex));
list.Add(new LVIssuance
{
lockNo = lockNo,
sect = section,
sched = sched,
issuedDate = issuedDate,
issuedBy = issuedBy,
fullName = fullName,
studentNo = studentNo
});
}
}
}
}
}
}
return(list);
}
private void searchInName_MouseLeftButtonUp(object sender, MouseButtonEventArgs e)
{
if (string.IsNullOrEmpty(txtProdCode.Text))
{
MessageBox.Show("Inventory Name field is empty!");
}
else
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT COUNT(1) from ApparatusInventory where prodCode = @prodCode", conn))
{
cmd.Parameters.AddWithValue("@prodCode", txtProdCode.Text);
int count = (int)cmd.ExecuteScalar();
if (count > 0)
{
using (SqlCeCommand cmd1 = new SqlCeCommand("SELECT * from ApparatusInventory where prodCode = @prodCode", conn))
{
cmd1.Parameters.AddWithValue("@prodCode", txtProdCode.Text);
using (DbDataReader reader = cmd1.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.HasRows)
{
while (reader.Read())
{
int prodCodeIndex = reader.GetOrdinal("prodCode");
string prodCode = Convert.ToString(reader.GetValue(prodCodeIndex));
int appaNameIndex = reader.GetOrdinal("name");
string appaName = Convert.ToString(reader.GetValue(appaNameIndex));
int manufIndex = reader.GetOrdinal("manuf");
string manuf = Convert.ToString(reader.GetValue(manufIndex));
int qtyIndex = reader.GetOrdinal("qty");
int qty = Convert.ToInt32(reader.GetValue(qtyIndex));
int sizeIndex = reader.GetOrdinal("size");
string size = Convert.ToString(reader.GetValue(sizeIndex));
var unit = Regex.Replace(size, @"[\d-]", string.Empty);
string numberOnly = Regex.Replace(size, "[^0-9.]", "");
txtProdCode.Text = prodCode;
txtInventName.Text = appaName;
txtManuf.Text = manuf;
txtQty.Text = qty.ToString();
txtSize.Text = numberOnly;
cmbUnit.Text = unit;
disableFields();
txtSize.IsEnabled = true;
cmbUnit.IsEnabled = true;
process = 1;
}
}
}
}
}
else
{
MessageBox.Show("Item does not exist!");
emptyFields();
}
}
}
}
private void btnDelete_Click(object sender, RoutedEventArgs e)
{
if (string.IsNullOrEmpty(txtInventName.Text))
{
MessageBox.Show("Inventory name field is empty!");
txtInventName.Focus();
}
else if (string.IsNullOrEmpty(txtManuf.Text))
{
MessageBox.Show("Manufacturer field is empty!");
txtManuf.Focus();
}
else
{
if (process == 1)
{
string sMessageBoxText = "Do you want to delete this record?";
string sCaption = "Delete Inventory record";
MessageBoxButton btnMessageBox = MessageBoxButton.YesNoCancel;
MessageBoxImage icnMessageBox = MessageBoxImage.Warning;
MessageBoxResult dr = MessageBox.Show(sMessageBoxText, sCaption, btnMessageBox, icnMessageBox);
switch (dr)
{
case MessageBoxResult.Yes:
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("INSERT into ArchiveApparatusInventory (prodCode, name, qty, size, manuf) SELECT prodCode, name, qty, size, manuf from ApparatusInventory where name = @inventName and manuf = @manuf", conn))
{
cmd.Parameters.AddWithValue("@inventName", txtInventName.Text);
cmd.Parameters.AddWithValue("@manuf", txtManuf.Text);
int result = cmd.ExecuteNonQuery();
if (result > 0)
{
using (SqlCeCommand command = new SqlCeCommand("DELETE from ApparatusInventory where name = @inventName and manuf = @manuf", conn))
{
command.Parameters.AddWithValue("@inventName", txtInventName.Text);
command.Parameters.AddWithValue("@manuf", txtManuf.Text);
int query = command.ExecuteNonQuery();
MessageBox.Show("Item has been deleted!");
Log = LogManager.GetLogger("deleteApparatus");
Log.Info("Item " + txtProdCode.Text + " has been archived!");
emptyFields();
enableFields();
process = 0;
}
}
else
{
MessageBox.Show("Item does not exist!");
}
}
break;
case MessageBoxResult.No:
break;
}
}
else
{
MessageBox.Show("Please search the inventory item first before deleting any record!");
}
}
}
private void btnSave_Click(object sender, RoutedEventArgs e)
{
if (string.IsNullOrEmpty(txtInventName.Text) || string.IsNullOrEmpty(txtManuf.Text) || string.IsNullOrEmpty(txtQty.Text) || string.IsNullOrEmpty(txtProdCode.Text))
{
MessageBox.Show("One or more fields are empty!");
}
else if (txtSize.Text.Length > 0 && string.IsNullOrEmpty(cmbUnit.Text))
{
MessageBox.Show("Unit cannot be empty if size has value!");
cmbUnit.Focus();
}
else if (process == 1)
{
MessageBox.Show("Search only works in junction with Edit and Delete Record, please press the reset button if you're trying to add new inventory record!");
}
else
{
string sMessageBoxText = "Are all fields correct?";
string sCaption = "Add Inventory";
MessageBoxButton btnMessageBox = MessageBoxButton.YesNoCancel;
MessageBoxImage icnMessageBox = MessageBoxImage.Warning;
MessageBoxResult dr = MessageBox.Show(sMessageBoxText, sCaption, btnMessageBox, icnMessageBox);
switch (dr)
{
case MessageBoxResult.Yes:
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd1 = new SqlCeCommand("SELECT COUNT(1) from ApparatusInventory where prodCode = @prodCode", conn))
{
cmd1.Parameters.AddWithValue("@prodCode", txtProdCode.Text);
int count = (int)cmd1.ExecuteScalar();
if (count > 0)
{
MessageBox.Show("Product code already exists! Please choose another and try again.");
}
else
{
string unit = "";
if (cmbUnit.Text == "N/A")
{
unit = null;
}
else
{
unit = cmbUnit.Text;
}
using (SqlCeCommand cmd = new SqlCeCommand("INSERT into ApparatusInventory (prodCode, name, manuf, qty, size) VALUES (@prodCode, @inventName, @manuf, @qty, @size)", conn))
{
cmd.Parameters.AddWithValue("@prodCode", txtProdCode.Text);
cmd.Parameters.AddWithValue("@inventName", txtInventName.Text);
cmd.Parameters.AddWithValue("@manuf", txtManuf.Text);
cmd.Parameters.AddWithValue("@qty", txtQty.Text);
if (!string.IsNullOrEmpty(txtSize.Text))
{
cmd.Parameters.AddWithValue("@size", txtSize.Text + unit);
}
else
{
cmd.Parameters.AddWithValue("@size", DBNull.Value);
}
try
{
cmd.ExecuteNonQuery();
MessageBox.Show("Added Successfully");
Log = LogManager.GetLogger("addNewApparatus");
Log.Info("Item " + txtProdCode.Text + " has been added to database!");
emptyFields();
}
catch (SqlCeException ex)
{
MessageBox.Show("Error! Log has been updated with the error. ");
Log = LogManager.GetLogger("*");
Log.Error(ex);
}
}
}
}
break;
case MessageBoxResult.No: break;
}
}
}
private void btnEdit_Click(object sender, RoutedEventArgs e)
{
if (string.IsNullOrEmpty(txtInventName.Text) || string.IsNullOrEmpty(cmbUnit.Text))
{
MessageBox.Show("One or more field is empty!");
}
else if (txtSize.Text.Length > 0 && string.IsNullOrEmpty(cmbUnit.Text))
{
MessageBox.Show("Unit cannot be empty if size has value!");
cmbUnit.Focus();
}
else if (cmbUnit.Text.Length > 0 && string.IsNullOrEmpty(txtSize.Text))
{
MessageBox.Show("Size cannot be empty if unit has value!");
txtSize.Focus();
}
else
{
if (process == 1)
{
string sMessageBoxText = "Do you want to update the record?";
string sCaption = "Edit Record";
MessageBoxButton btnMessageBox = MessageBoxButton.YesNoCancel;
MessageBoxImage icnMessageBox = MessageBoxImage.Warning;
MessageBoxResult dr = MessageBox.Show(sMessageBoxText, sCaption, btnMessageBox, icnMessageBox);
switch (dr)
{
case MessageBoxResult.Yes:
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("UPDATE ApparatusInventory set size = @size where name = @inventName and manuf = @manuf and prodCode = @prodCode", conn))
{
cmd.Parameters.AddWithValue("@size", txtSize.Text + cmbUnit.Text);
cmd.Parameters.AddWithValue("@inventName", txtInventName.Text);
cmd.Parameters.AddWithValue("@manuf", txtManuf.Text);
cmd.Parameters.AddWithValue("@prodCode", txtProdCode.Text);
try
{
cmd.ExecuteNonQuery();
MessageBox.Show("Updated Successfully");
Log = LogManager.GetLogger("editApparatus");
Log.Info("Item " + txtProdCode.Text + " records has been modified/updated");
emptyFields();
enableFields();
}
catch (SqlCeException ex)
{
MessageBox.Show("Error! Log has been updated with the error.");
Log = LogManager.GetLogger("*");
Log.Error(ex, "Query Error");
}
}
break;
case MessageBoxResult.No: break;
}
}
else
{
MessageBox.Show("Please search the inventory item first before editing any record!");
}
}
}
private void btnReturn_Click(object sender, RoutedEventArgs e)
{
string sMessageBoxText = "Are all fields checked?";
string sCaption = "Return process";
MessageBoxButton btnMessageBox = MessageBoxButton.YesNoCancel;
MessageBoxImage icnMessageBox = MessageBoxImage.Warning;
MessageBoxResult dr = MessageBox.Show(sMessageBoxText, sCaption, btnMessageBox, icnMessageBox);
switch (dr)
{
case MessageBoxResult.Yes:
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
foreach (var check in request)
{
int qty = 0;
using (SqlCeCommand cmd = new SqlCeCommand("SELECT qty from IssuanceList where lockNo = @lockNo and subject = @subject and prodCode = @prodCode and section = @section and sched = @sched", conn))
{
cmd.Parameters.AddWithValue("@subject", txtSubject.Text);
cmd.Parameters.AddWithValue("@lockNo", txtLockNo.Text);
cmd.Parameters.AddWithValue("@issuedBy", txtIssued.Text);
cmd.Parameters.AddWithValue("@prodCode", check.prodCode);
cmd.Parameters.AddWithValue("@sched", txtSched.Text);
cmd.Parameters.AddWithValue("@section", txtSection.Text);
using (SqlCeDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.HasRows)
{
while (reader.Read())
{
int qtyIndex = reader.GetOrdinal("qty");
qty = Convert.ToInt32(reader.GetValue(qtyIndex));
}
}
}
}
if (check.qty > qty)
{
MessageBox.Show("Quantity of the product is greater than the old quantity value! Please change the value to be able to proceed");
break;
}
else
{
if (check.breakage == true)
{
if (check.qty < qty)
{
qty -= check.qty; //working apparatus
using (SqlCeCommand cmd = new SqlCeCommand("UPDATE IssuanceList set breakage = 1, qty = qty - @qty where where section = @sect and sched = @sched and lockNo = @lockNo and subject = @subject and prodCode = @prodCode", conn))
{
cmd.Parameters.AddWithValue("@subject", txtSubject.Text);
cmd.Parameters.AddWithValue("@sched", txtSched.Text);
cmd.Parameters.AddWithValue("@sect", txtSection.Text);
cmd.Parameters.AddWithValue("@lockNo", txtLockNo.Text);
cmd.Parameters.AddWithValue("@issuedBy", txtIssued.Text);
cmd.Parameters.AddWithValue("@prodCode", check.prodCode);
cmd.Parameters.AddWithValue("@qty", qty);
try
{
int count = cmd.ExecuteNonQuery();
MessageBox.Show(count.ToString());
if (count > 0)
{
using (SqlCeCommand cmd1 = new SqlCeCommand("UPDATE ApparatusInventory set qty = qty + @qty where prodCode = @prodCode", conn))
{
cmd1.Parameters.AddWithValue("@prodCode", check.prodCode);
cmd1.Parameters.AddWithValue("@qty", qty);
try
{
cmd1.ExecuteNonQuery();
}
catch (SqlCeException ex)
{
MessageBox.Show("Error! Log has been updated with the error.");
Log = LogManager.GetLogger("*");
Log.Error(ex, "Query Error");
return;
}
}
}
}
catch (SqlCeException ex)
{
MessageBox.Show("Error! Log has been updated with the error.");
Log = LogManager.GetLogger("*");
Log.Error(ex, "Query Error");
return;
}
}
}
else
{
using (SqlCeCommand cmd = new SqlCeCommand("UPDATE IssuanceList set breakage = 1 where section = @sect and sched = @sched and lockNo = @lockNo and subject = @subject and prodCode = @prodCode", conn))
{
cmd.Parameters.AddWithValue("@subject", txtSubject.Text);
cmd.Parameters.AddWithValue("@sched", txtSched.Text);
cmd.Parameters.AddWithValue("@sect", txtSection.Text);
cmd.Parameters.AddWithValue("@lockNo", txtLockNo.Text);
cmd.Parameters.AddWithValue("@issuedBy", txtIssued.Text);
cmd.Parameters.AddWithValue("@prodCode", check.prodCode);
try
{
cmd.ExecuteNonQuery();
MessageBox.Show("Record has been updated!");
}
catch (SqlCeException ex)
{
MessageBox.Show("Error! Log has been updated with the error.");
Log = LogManager.GetLogger("*");
Log.Error(ex, "Query Error");
return;
}
}
}
}
else
{
using (SqlCeCommand cmd = new SqlCeCommand("INSERT into ArchiveIssuanceList SELECT lockNo, prof, sched, subject, section, issuedDate, issuedBy, fullName, studentNo, prodCode, qty from IssuanceList where lockNo = @lockNo and sched = @sched and subject = @subject and section = @sect and issuedBy = @issuedBy", conn))
{
cmd.Parameters.AddWithValue("@subject", txtSubject.Text);
cmd.Parameters.AddWithValue("@sched", txtSched.Text);
cmd.Parameters.AddWithValue("@sect", txtSection.Text);
cmd.Parameters.AddWithValue("@lockNo", txtLockNo.Text);
cmd.Parameters.AddWithValue("@issuedBy", txtIssued.Text);
try
{
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
using (SqlCeCommand cmd1 = new SqlCeCommand("UPDATE ApparatusInventory set qty = qty + @qty where prodCode = @prodCode", conn))
{
cmd1.Parameters.AddWithValue("@prodCode", check.prodCode);
cmd1.Parameters.AddWithValue("@qty", check.qty);
try
{
cmd1.ExecuteNonQuery();
using (SqlCeCommand cmd2 = new SqlCeCommand("DELETE from IssuanceList where lockNo = @lockNo and subject = @subject and section = @sect and sched = @sched and issuedBy = @issuedBy and prodCode = @prodCode", conn))
{
cmd2.Parameters.AddWithValue("@subject", txtSubject.Text);
cmd2.Parameters.AddWithValue("@sched", txtSched.Text);
cmd2.Parameters.AddWithValue("@sect", txtSection.Text);
cmd2.Parameters.AddWithValue("@lockNo", txtLockNo.Text);
cmd2.Parameters.AddWithValue("@issuedBy", txtIssued.Text);
cmd2.Parameters.AddWithValue("@prodCode", check.prodCode);
try
{
cmd2.ExecuteNonQuery();
Log = LogManager.GetLogger("issuanceFormRecord");
string newLine = System.Environment.NewLine;
Log.Info("A Issuance form has been returned with the ff information: " + newLine +
"Date Requested: " + txtDate.Text + newLine +
"Subject: " + txtSubject.Text + newLine +
"Section: " + txtSection.Text + newLine +
"Schedule: " + txtSched.Text + newLine +
"Locker No.: " + txtLockNo.Text + newLine +
"Issued by: " + txtIssued.Text
);
}
catch (SqlCeException ex)
{
MessageBox.Show("Error! Log has been updated with the error. " + ex);
}
}
}
catch (SqlCeException ex)
{
MessageBox.Show("Error! Log has been updated with the error. " + ex);
return;
}
}
}
}
catch (SqlCeException ex)
{
MessageBox.Show("Error! Log has been updated with the error. " + ex);
return;
}
}
}
}
}
updateRecords();
break;
case MessageBoxResult.No:
break;
}
}
