private void getFullName()
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT firstName + ' ' + lastName as fullName from Accounts where username = @username", conn))
{
cmd.Parameters.AddWithValue("@username", user);
using (DbDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.HasRows)
{
reader.Read();
int fullNameIndex = reader.GetOrdinal("fullName");
fullName = Convert.ToString(reader.GetValue(fullNameIndex));
}
}
}
}
private void updateListView()
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT * from ApparatusInventory", conn))
{
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 void fillSubjects()
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT DISTINCT subjName from Subjects", conn))
{
using (DbDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.HasRows)
{
cmbSubj.Items.Clear();
while (reader.Read())
{
string subjName = reader["subjName"].ToString();
cmbSubj.Items.Add(subjName);
}
}
}
}
}
private void fillInventory()
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT DISTINCT name from ApparatusInventory", conn))
{
using (DbDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.HasRows)
{
cmbInventName.Items.Clear();
while (reader.Read())
{
string appaName = reader["name"].ToString();
cmbInventName.Items.Add(appaName);
}
}
}
}
}
private void ComboBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
var comboBox = sender as ComboBox;
if (comboBox.SelectedItem != null)
{
LVIssuance item = dgSubject.SelectedItem as LVIssuance;
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
int maxQty = 0;
using (SqlCeCommand cmd = new SqlCeCommand("SELECT qty from ApparatusInventory where name = @name and manuf = @manuf and (size IS null or size = @size)", conn))
{
cmd.Parameters.AddWithValue("@size", item.size);
cmd.Parameters.AddWithValue("@manuf", item.manuf);
cmd.Parameters.AddWithValue("@name", item.inventName);
using (DbDataReader dr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (dr.HasRows)
{
while (dr.Read())
{
int qtyIndex = dr.GetOrdinal("qty");
maxQty = Convert.ToInt32(dr.GetValue(qtyIndex));
}
}
}
if (item.qty > maxQty)
{
var found = items.FirstOrDefault(x => (x.inventName == item.inventName) && (x.manuf == item.manuf) && ((x.size == item.size) || (x.size == null)));
if (found != null)
{
MessageBox.Show("Item " + item.inventName + " size: " + item.size + " manufacturer: " + item.manuf + " has low stocks, quantity has been set to the quantity of available stocks");
found.qty = maxQty;
}
}
}
}
}
private void fillInventoryList()
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT DISTINCT name from ApparatusInventory", conn))
{
using (DbDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.HasRows)
{
cmbInventName.Items.Clear();
while (reader.Read())
{
int inventoryNameIndex = reader.GetOrdinal("name");
string inventoryName = Convert.ToString(reader.GetValue(inventoryNameIndex));
cmbInventName.Items.Add(inventoryName);
}
}
}
}
}
public ForgotPassword(string username)
{
InitializeComponent();
user = username;
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT securityQuestion, answer FROM Accounts WHERE username = @username", conn))
{
cmd.Parameters.AddWithValue("@username", username);
SqlCeDataReader dr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (dr.Read())
{
int securityQuestionIndex = dr.GetOrdinal("securityQuestion");
question = Convert.ToString(dr.GetValue(securityQuestionIndex));
int answerIndex = dr.GetOrdinal("answer");
answer = Convert.ToString(dr.GetValue(answerIndex));
securityQuestion.Content = question;
}
}
}
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 borrowers)
{
string prodCode = "";
using (SqlCeCommand cmd = new SqlCeCommand("SELECT prodCode from ApparatusInventory where name = @appaName and manuf = @manuf and size = @size", conn))
{
cmd.Parameters.AddWithValue("@appaName", check.prodName);
cmd.Parameters.AddWithValue("@manuf", check.manuf);
cmd.Parameters.AddWithValue("@size", check.size);
using (SqlCeDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
while (reader.Read())
{
int prodCodeIndex = reader.GetOrdinal("prodCode");
prodCode = Convert.ToString(reader.GetValue(prodCodeIndex));
}
}
}
int qty = 0;
using (SqlCeCommand cmd = new SqlCeCommand("SELECT qty from BorrowerList where DATEDIFF(day, dateReq, @dateReq) = 0 and DATEDIFF(day, dateExp, @dateExp) = 0 and groupID = @grpID and lockNo = @lockNo and subject = @subj and expName = @experiment and prodCode = @prodCode", conn))
{
cmd.Parameters.AddWithValue("@subj", txtSubj.Text);
cmd.Parameters.AddWithValue("@grpID", txtGrpID.Text);
cmd.Parameters.AddWithValue("@experiment", txtExpName.Text);
cmd.Parameters.AddWithValue("@lockNo", txtLockNo.Text);
cmd.Parameters.AddWithValue("@dateReq", txtDateReq.Text);
cmd.Parameters.AddWithValue("@dateExp", txtDateExp.Text);
cmd.Parameters.AddWithValue("@prodCode", prodCode);
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) //IF THERE ARE SOME BREAKAGES
{
qty -= check.qty; //working apparatus
using (SqlCeCommand cmd = new SqlCeCommand("UPDATE BorrowerList set breakage = 1, qty = qty - @qty where dateReq = @dateReq and dateExp = @dateExp and groupID = @grpID and lockNo = @lockNo and subject = @subj and expName = @experiment and prodCode = @prodCode", conn))
{
cmd.Parameters.AddWithValue("@dateReq", txtDateReq.Text);
cmd.Parameters.AddWithValue("@dateExp", txtDateExp.Text);
cmd.Parameters.AddWithValue("@grpID", txtGrpID.Text);
cmd.Parameters.AddWithValue("@lockNo", txtLockNo.Text);
cmd.Parameters.AddWithValue("@subj", txtSubj.Text);
cmd.Parameters.AddWithValue("@experiment", txtExpName.Text);
cmd.Parameters.AddWithValue("@manuf", check.manuf);
cmd.Parameters.AddWithValue("@prodCode", 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", 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 //IF ALL QUANTITY OF THE SPECIFIC BORROWED APPARATUS ARE BROKEN
{
using (SqlCeCommand cmd = new SqlCeCommand("UPDATE BorrowerList set breakage = 1 where dateReq = @dateReq and dateExp = @dateExp and groupID = @grpID and lockNo = @lockNo and subject = @subj and expName = @experiment and prodCode = @prodCode", conn))
{
cmd.Parameters.AddWithValue("@dateReq", txtDateReq.Text);
cmd.Parameters.AddWithValue("@dateExp", txtDateExp.Text);
cmd.Parameters.AddWithValue("@grpID", txtGrpID.Text);
cmd.Parameters.AddWithValue("@lockNo", txtLockNo.Text);
cmd.Parameters.AddWithValue("@subj", txtSubj.Text);
cmd.Parameters.AddWithValue("@experiment", txtExpName.Text);
cmd.Parameters.AddWithValue("@prodCode", prodCode);
try
{
cmd.ExecuteNonQuery();
MessageBox.Show("Record has been updated!");
}
catch (SqlCeException ex)
{
MessageBox.Show("Error! Log has been updated with the error. " + ex);
return;
}
}
}
}
else //IF THERE ARE NONE BROKEN
{
using (SqlCeCommand cmd = new SqlCeCommand("INSERT into ArchiveBorrowerList SELECT dateReq, dateExp, studentNo, fullName, groupID, lockNo, subject, expName, prodCode, qty from BorrowerList where DATEDIFF(day, dateReq, @dateReq) = 0 and DATEDIFF(day, dateExp, @dateExp) = 0 and groupID = @grpID and lockNo = @lockNo and subject = @subj and expName = @experiment and prodCode = @prodCode", conn))
{
cmd.Parameters.AddWithValue("@dateReq", txtDateReq.Text);
cmd.Parameters.AddWithValue("@dateExp", txtDateExp.Text);
cmd.Parameters.AddWithValue("@grpID", txtGrpID.Text);
cmd.Parameters.AddWithValue("@lockNo", txtLockNo.Text);
cmd.Parameters.AddWithValue("@subj", txtSubj.Text);
cmd.Parameters.AddWithValue("@experiment", txtExpName.Text);
cmd.Parameters.AddWithValue("@prodCode", prodCode);
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", prodCode);
cmd1.Parameters.AddWithValue("@qty", check.qty);
try
{
cmd1.ExecuteNonQuery();
using (SqlCeCommand cmd2 = new SqlCeCommand("DELETE from BorrowerList where groupID = @grpID and subject = @subjName and expName = @experiment and DATEDIFF(day, dateReq, @dateReq) = 0 and DATEDIFF(day, dateExp, @dateExp) = 0 and prodCode = @prodCode", conn))
{
cmd2.Parameters.AddWithValue("@grpID", txtGrpID.Text);
cmd2.Parameters.AddWithValue("@subjName", txtSubj.Text);
cmd2.Parameters.AddWithValue("@experiment", txtExpName.Text);
cmd2.Parameters.AddWithValue("@dateReq", txtDateReq.Text);
cmd2.Parameters.AddWithValue("@dateExp", txtDateExp.Text);
cmd2.Parameters.AddWithValue("@prodCode", prodCode);
try
{
count = cmd2.ExecuteNonQuery();
Log = LogManager.GetLogger("borrowerFormRecord");
string newLine = System.Environment.NewLine;
Log.Info("A Borrower form has been returned with the following information: " + newLine +
"Date Requested: " + txtDateReq.Text + newLine +
"Date of Experiment" + txtDateExp.Text + newLine +
"Subject: " + txtSubj.Text + newLine +
"Experiment Title: " + txtExpName.Text + newLine +
"Locker No.: " + txtLockNo.Text + newLine +
"Group No: " + txtGrpID.Text
);
}
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");
return;
}
}
}
}
catch (SqlCeException ex)
{
MessageBox.Show("Error! Log has been updated with the error. " + ex);
return;
}
}
}
}
}
updateRecords();
break;
case MessageBoxResult.No:
break;
}
}
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 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 ObservableCollection <LVIssuance> LoadCollectionData()
{
SqlCeConnection conn = DBUtils.GetDBConnection();
string name = "", size = "", manuf = "";
conn.Open();
items.Clear();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT qty, name, size from Subjects where subjName = @subjName", conn))
{
cmd.Parameters.AddWithValue("@subjName", txtSubject.Text);
using (DbDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.HasRows)
{
while (reader.Read())
{
int qtyIndex = reader.GetOrdinal("qty");
int qty = Convert.ToInt32(reader.GetValue(qtyIndex));
int nameIndex = reader.GetOrdinal("name");
name = Convert.ToString(reader.GetValue(nameIndex));
int sizeIndex = reader.GetOrdinal("size");
size = Convert.ToString(reader.GetValue(sizeIndex));
List <string> manufacturer = new List <string>();
using (SqlCeCommand cmd1 = new SqlCeCommand("SELECT manuf from ApparatusInventory where name = @name and (size IS null or size = @size)", conn))
{
cmd1.Parameters.AddWithValue("@name", name);
cmd1.Parameters.AddWithValue("@size", size);
using (DbDataReader dr = cmd1.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (dr.HasRows)
{
while (dr.Read())
{
int manufIndex = dr.GetOrdinal("manuf");
manuf = Convert.ToString(dr.GetValue(manufIndex));
manufacturer.Add(manuf);
}
}
}
}
items.Add(new LVIssuance()
{
i = i,
inventName = name,
manufList = manufacturer,
size = size,
qty = qty
});
i++;
}
}
}
}
return(items);
}
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 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 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 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 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 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 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 fillList()
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT DISTINCT dateReq, dateExp, groupID, lockNo, subject, expName from BorrowerList", conn))
{
using (DbDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.HasRows)
{
lvList.Items.Clear();
while (reader.Read())
{
int dateReqIndex = reader.GetOrdinal("dateReq");
DateTime myDate = reader.GetDateTime(dateReqIndex);
string dateReq = myDate.ToString("MM/dd/yyyy");
int dateExpIndex = reader.GetOrdinal("dateExp");
myDate = reader.GetDateTime(dateExpIndex);
string dateExp = myDate.ToString("MM/dd/yyyy");
int subjIndex = reader.GetOrdinal("subject");
string subj = Convert.ToString(reader.GetValue(subjIndex));
int lockNoIndex = reader.GetOrdinal("lockNo");
int lockNo = Convert.ToInt32(reader.GetValue(lockNoIndex));
int expNameIndex = reader.GetOrdinal("expName");
string expName = Convert.ToString(reader.GetValue(expNameIndex));
int grpIDIndex = reader.GetOrdinal("groupID");
int grpID = Convert.ToInt32(reader.GetValue(grpIDIndex));
using (SqlCeCommand cmd1 = new SqlCeCommand("SELECT TOP 1 studentNo, fullName from BorrowerList where groupID = @groupID and subject = @subject and expName = @expName and DATEDIFF(day, dateReq, @dateReq) = 0 and DATEDIFF(day, dateExp, @dateExp) = 0 and lockNo = @lockNo", conn))
{
cmd1.Parameters.AddWithValue("@groupID", grpID);
cmd1.Parameters.AddWithValue("@subject", subj);
cmd1.Parameters.AddWithValue("@expName", expName);
cmd1.Parameters.AddWithValue("@dateReq", dateReq);
cmd1.Parameters.AddWithValue("@dateExp", dateExp);
cmd1.Parameters.AddWithValue("@lockNo", lockNo);
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));
lvList.Items.Add(new LVBorrower
{
dateReq = dateReq,
dateExp = dateExp,
studentNo = studentNo,
fullName = fullName,
lockNo = lockNo.ToString(),
subj = subj,
grpID = grpID,
experiment = expName,
});
i++;
}
}
}
}
}
}
}
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 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 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 btnGenForm_Click(object sender, RoutedEventArgs e)
{
if (string.IsNullOrEmpty(cmbSubj.Text) || string.IsNullOrEmpty(txtLock.Text) || string.IsNullOrEmpty(txtIssued.Text) || string.IsNullOrEmpty(txtDate.Text) || string.IsNullOrEmpty(txtProf.Text) || string.IsNullOrEmpty(txtSched.Text) || string.IsNullOrEmpty(txtStud1.Text) || string.IsNullOrEmpty(txtName1.Text))
{
MessageBox.Show("Fill in the missing fields");
}
else
{
string sMessageBoxText = "Are all fields accounted form?";
string sCaption = "Generate issuance form";
MessageBoxButton btnMessageBox = MessageBoxButton.YesNoCancel;
MessageBoxImage icnMessageBox = MessageBoxImage.Warning;
MessageBoxResult dr = MessageBox.Show(sMessageBoxText, sCaption, btnMessageBox, icnMessageBox);
switch (dr)
{
case MessageBoxResult.Yes:
bool success = false;
foreach (var item in items) //VALIDATION CHECKING
{
if (string.IsNullOrEmpty(item.manuf))
{
MessageBox.Show("One or more manufacturing fields are empty, please fill all out.");
return;
}
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
int maxQty = 0;
using (SqlCeCommand cmd = new SqlCeCommand("SELECT qty from ApparatusInventory where name = @name and manuf = @manuf and (size IS null or size = @size)", conn))
{
cmd.Parameters.AddWithValue("@size", item.size);
cmd.Parameters.AddWithValue("@manuf", item.manuf);
cmd.Parameters.AddWithValue("@name", item.inventName);
using (DbDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.HasRows)
{
while (reader.Read())
{
int qtyIndex = reader.GetOrdinal("qty");
maxQty = Convert.ToInt32(reader.GetValue(qtyIndex));
}
}
}
if (item.qty > maxQty)
{
var found = items.FirstOrDefault(x => (x.inventName == item.inventName) && (x.manuf == item.manuf) && ((x.size == item.size) || (x.size == null)));
if (found != null)
{
MessageBox.Show("Item " + item.inventName + " size: " + item.size + " manufacturer: " + item.manuf + " has low stocks, quantity has been set to the quantity of available stocks");
found.qty = maxQty;
return;
}
}
}
}
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 user = Environment.UserName;
string date = txtDate.Text.Replace("/", "-");
string filename = @"C:\Users\" + user + @"\Desktop\[" + date + "][" + txtLock.Text + "][" + cmbSubj.Text + "][" + txtSect.Text + "].docx";
filename = filename.Replace(" ", "-");
using (DocX document = DocX.Create(filename))
{
document.MarginBottom = InchesToPoints(.5f);
document.MarginTop = InchesToPoints(.5f);
document.MarginRight = InchesToPoints(.5f);
string underline = "";
var image = document.AddImage(@"resources/adulogo-blue.png");
var picture = image.CreatePicture(50, 200);
var p0 = document.InsertParagraph();
p0.AppendPicture(picture);
document.InsertParagraph();
var p1 = document.InsertParagraph("ISSUANCE FORM").Bold().FontSize(10)
.Alignment = Alignment.right;
var p2 = document.InsertParagraph("PHARMACY LABORATORY").Bold().FontSize(8)
.UnderlineColor(System.Drawing.Color.Black).SpacingAfter(15).Alignment = Alignment.right;
var t0 = document.AddTable(2, 1);
t0.Design = TableDesign.None;
t0.Alignment = Alignment.right;
underline = returnCount(12, txtLock.Text);
t0.Rows[0].Cells[0].Paragraphs[0].Bold().Append("________").Bold().UnderlineStyle(UnderlineStyle.thick).Alignment = Alignment.center;
t0.Rows[0].Cells[0].Paragraphs[0].Bold().Append(txtLock.Text + underline).Bold().UnderlineStyle(UnderlineStyle.thick).Alignment = Alignment.center; //LOCKER NO
underline = "";
t0.Rows[1].Cells[0].Paragraphs[0].Append("LOCKER NUMBER").Bold().Alignment = Alignment.center;
document.InsertTable(t0);
document.InsertParagraph();
var t1 = document.AddTable(2, 3);
t1.Design = TableDesign.None;
t1.AutoFit = AutoFit.Window;
foreach (Row row in t1.Rows)
{
row.Cells[0].Width = 200;
row.Cells[1].Width = 200;
row.Cells[2].Width = 200;
}
t1.Rows[1].Cells[0].Paragraphs[0].Append("PROFESSOR").Bold().Alignment = Alignment.center;
t1.Rows[0].Cells[0].Paragraphs[0].Append("___").FontSize(10).Bold().UnderlineStyle(UnderlineStyle.thick).Alignment = Alignment.center;
t1.Rows[1].Cells[1].Paragraphs[0].Append("SCHEDULE").FontSize(10).Bold().Alignment = Alignment.center;
t1.Rows[0].Cells[1].Paragraphs[0].Append("_____").Bold().UnderlineStyle(UnderlineStyle.thick).Alignment = Alignment.center;
t1.Rows[1].Cells[2].Paragraphs[0].Append("SUBJECT AND SECTION").Bold().Alignment = Alignment.center;
t1.Rows[0].Cells[2].Paragraphs[0].Append("__").FontSize(10).Bold().UnderlineStyle(UnderlineStyle.thick).Alignment = Alignment.center;
underline = returnCount(24, txtProf.Text);
t1.Rows[0].Cells[0].Paragraphs[0].Append(txtProf.Text + underline).FontSize(10).Bold().UnderlineStyle(UnderlineStyle.thick).FontSize(9).Alignment = Alignment.center; //PROFESSOR
underline = "";
underline = returnCount(20, txtSched.Text);
t1.Rows[0].Cells[1].Paragraphs[0].Append(txtSched.Text + underline).FontSize(10).Bold().UnderlineStyle(UnderlineStyle.thick).Alignment = Alignment.center; //SCHEDULE
underline = "";
underline = returnCount(25, txtSubject.Text + " " + txtSect.Text);
t1.Rows[0].Cells[2].Paragraphs[0].Append(txtSubject.Text + " " + txtSect.Text + underline).FontSize(9).Bold().UnderlineStyle(UnderlineStyle.thick).Alignment = Alignment.center; //SUBJECT
underline = "";
document.InsertTable(t1);
document.InsertParagraph(); //newline
var t2 = document.AddTable(26, 6);
t2.Design = TableDesign.TableGrid;
t2.AutoFit = AutoFit.Window;
foreach (Row row in t2.Rows)
{
row.Cells[0].Width = 30;
row.Cells[1].Width = 120;
row.Cells[2].Width = 200;
row.Cells[3].Width = 40;
row.Cells[4].Width = 80;
row.Cells[5].Width = 50;
}
t2.Rows[0].Cells[0].Paragraphs[0].Append("QTY").Bold().Alignment = Alignment.center;
t2.Rows[0].Cells[0].VerticalAlignment = Xceed.Words.NET.VerticalAlignment.Bottom;
t2.Rows[0].Cells[1].Paragraphs[0].Append("APPARATUS").Bold().Alignment = Alignment.center;
t2.Rows[0].Cells[1].VerticalAlignment = Xceed.Words.NET.VerticalAlignment.Bottom;
t2.Rows[0].Cells[2].Paragraphs[0].Append("SIZE / BRAND / REMARKS").Bold().Alignment = Alignment.center;
t2.Rows[0].Cells[2].VerticalAlignment = Xceed.Words.NET.VerticalAlignment.Bottom;
t2.Rows[0].Cells[3].Paragraphs[0].Append("RTN").Bold().Alignment = Alignment.center;
t2.Rows[0].Cells[3].Paragraphs[0].AppendLine("CHK").Bold().Alignment = Alignment.center;
t2.Rows[0].Cells[4].Paragraphs[0].Append("BREAKAGES").Bold().Alignment = Alignment.center;
t2.Rows[0].Cells[4].VerticalAlignment = Xceed.Words.NET.VerticalAlignment.Bottom;
t2.Rows[0].Cells[5].Paragraphs[0].Append("AMOUNT").Bold().Alignment = Alignment.center;
t2.Rows[0].Cells[5].Paragraphs[0].AppendLine("CHARGE").Bold().Alignment = Alignment.center;
//INSERTION OF ITEMS REQUESTED TO DATABASE AND WORD FILE
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
int rowx = 1;
foreach (var items in items)
{
if (string.IsNullOrEmpty(items.manuf))
{
MessageBox.Show("One or more manufacturing fields are empty, please fill all out.");
return;
}
else
{
string prodCode = "";
using (SqlCeCommand cmd = new SqlCeCommand("SELECT prodCode from ApparatusInventory where name = @name and manuf = @manuf and (size IS null or size = @size)", conn))
{
cmd.Parameters.AddWithValue("@name", items.inventName);
cmd.Parameters.AddWithValue("@manuf", items.manuf);
cmd.Parameters.AddWithValue("@size", items.size);
using (DbDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.HasRows)
{
while (reader.Read())
{
int prodCodeIndex = reader.GetOrdinal("prodCode");
prodCode = Convert.ToString(reader.GetValue(prodCodeIndex));
}
}
}
}
//////////////////////////////////////////////////////////////////////////////////////////////
t2.Rows[rowx].Cells[0].Paragraphs[0].Append(Convert.ToString(items.qty)).Bold().Alignment = Alignment.center; // QTY
t2.Rows[rowx].Cells[1].Paragraphs[0].Append(items.inventName).FontSize(8).Bold().Alignment = Alignment.center; //INVENT NAME
if (!string.IsNullOrEmpty(items.size))
{
t2.Rows[rowx].Cells[2].Paragraphs[0].Append(items.size + "/" + items.manuf + "/").Bold().Alignment = Alignment.center; //APPENDING SIZE MANUF AND REMARKS
}
else
{
t2.Rows[rowx].Cells[2].Paragraphs[0].Append(items.manuf + "/").Bold().Alignment = Alignment.center; //APPENDING MANUF AND REMARKS
}
rowx++;
foreach (var student in studInfo)
{
using (SqlCeCommand cmd = new SqlCeCommand("INSERT into IssuanceList (lockNo, prof, sched, subject, section, issuedDate, issuedBy, fullName, studentNo, prodCode, qty, breakage) VALUES (@lockNo, @prof, @sched, @subject, @section, @issuedDate, @issuedBy, @fullName, @studentNo, @prodCode, @qty, 0)", conn))
{
cmd.Parameters.AddWithValue("@lockNo", txtLock.Text);
cmd.Parameters.AddWithValue("@prof", txtProf.Text);
cmd.Parameters.AddWithValue("@sched", txtSched.Text);
cmd.Parameters.AddWithValue("@subject", txtSubject.Text);
cmd.Parameters.AddWithValue("@section", txtSect.Text);
cmd.Parameters.AddWithValue("@issuedDate", txtDate.Text);
cmd.Parameters.AddWithValue("@issuedBy", txtIssued.Text);
cmd.Parameters.AddWithValue("@fullName", student.studName);
cmd.Parameters.AddWithValue("@studentNo", student.studNo);
cmd.Parameters.AddWithValue("@prodCode", prodCode);
cmd.Parameters.AddWithValue("@qty", items.qty);
try
{
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
success = true;
}
}
catch (SqlCeException ex)
{
MessageBox.Show("Error! Log has been updated with the error.");
Log = LogManager.GetLogger("*");
Log.Error(ex, "Query Error");
}
}
}
using (SqlCeCommand cmd = new SqlCeCommand("UPDATE ApparatusInventory set qty = qty - @qty where prodCode = @prodCode", conn))
{
cmd.Parameters.AddWithValue("@qty", items.qty);
cmd.Parameters.AddWithValue("@prodCode", prodCode);
try
{
cmd.ExecuteNonQuery();
}
catch (SqlCeException ex)
{
MessageBox.Show("Error! Log has been updated with the error.");
Log = LogManager.GetLogger("*");
Log.Error(ex, "Query Error");
}
}
}
}
document.InsertTable(t2);
document.InsertParagraph(); //newline
var t3 = document.AddTable(2, 2);
t3.Design = TableDesign.None;
t3.AutoFit = AutoFit.Window;
t3.Rows[0].Cells[0].Paragraphs[0].Append("ISSUED ON : ").FontSize(9).Bold();
t3.Rows[1].Cells[0].Paragraphs[0].Append("ISSUED BY : ").FontSize(9).Bold().Alignment = Alignment.left;
t3.Rows[0].Cells[1].Paragraphs[0].Append("RETURNED ON :_________________________").FontSize(9).Bold().Alignment = Alignment.right;
t3.Rows[1].Cells[1].Paragraphs[0].Append("RECEIVED BY :_________________________").FontSize(9).Bold().Alignment = Alignment.right;
underline = returnCount(19, txtDate.Text);
t3.Rows[0].Cells[0].Paragraphs[0].Append("__" + txtDate.Text + underline).FontSize(9).Bold().UnderlineStyle(UnderlineStyle.thick).Alignment = Alignment.left; // ISSUED DATE
underline = "";
underline = returnCount(19, txtIssued.Text);
t3.Rows[1].Cells[0].Paragraphs[0].Append("__" + txtIssued.Text + underline).UnderlineStyle(UnderlineStyle.thick).FontSize(9).Bold().Alignment = Alignment.left; // ISSUED BY
underline = "";
document.InsertTable(t3);
document.InsertParagraph();
var p5 = document.InsertParagraph(@" I/We, the undersigned, acknowledge to have received the apparatus above clean, dry and in good condition. Said articles are to be returned upon the termination of the semester clean, dry and in good condition.").FontSize(10).Bold().Alignment = Alignment.both;
document.InsertParagraph();
var p6 = document.InsertParagraph("FILL THE BLANKS PROPERLY AND IN PRINT").Bold().UnderlineStyle(UnderlineStyle.singleLine);
document.InsertParagraph();
var t4 = document.AddTable(5, 4);
t4.Design = TableDesign.None;
t4.AutoFit = AutoFit.Window;
foreach (Row row in t4.Rows)
{
row.Cells[0].Width = 180;
row.Cells[1].Width = 120;
row.Cells[2].Width = 80;
row.Cells[3].Width = 80;
}
t4.Rows[0].Cells[0].Paragraphs[0].Append(" SURNAME FIRST NAME M.I.").FontSize(9).Bold();
t4.Rows[0].Cells[1].Paragraphs[0].Append("STUDENT NUMBER").FontSize(9).Bold().Alignment = Alignment.center;
t4.Rows[0].Cells[2].Paragraphs[0].Append("COURSE").FontSize(9).Bold().Alignment = Alignment.center;
t4.Rows[0].Cells[3].Paragraphs[0].Append("SIGNATURE").FontSize(9).Bold().Alignment = Alignment.center;
t4.Rows[1].Cells[0].Paragraphs[0].Append("1. ").Bold().FontSize(9);
t4.Rows[2].Cells[0].Paragraphs[0].Append("2. ").Bold().FontSize(9);
t4.Rows[3].Cells[0].Paragraphs[0].Append("3. ").Bold().FontSize(9);
t4.Rows[4].Cells[0].Paragraphs[0].Append("4. ").Bold().FontSize(9);
underline = returnCount(36, txtName1.Text);
t4.Rows[1].Cells[0].Paragraphs[0].Append("_" + txtName1.Text + underline).UnderlineStyle(UnderlineStyle.thick).Bold().FontSize(9); //STUDENT NAME
underline = "";
underline = returnCount(36, txtName2.Text);
t4.Rows[2].Cells[0].Paragraphs[0].Append("_" + txtName2.Text + underline).UnderlineStyle(UnderlineStyle.thick).Bold().FontSize(9); //STUDENT NAME
underline = "";
underline = returnCount(36, txtName3.Text);
t4.Rows[3].Cells[0].Paragraphs[0].Append("_" + txtName3.Text + underline).UnderlineStyle(UnderlineStyle.thick).Bold().FontSize(9); //STUDENT NAME
underline = "";
underline = returnCount(36, txtName4.Text);
t4.Rows[4].Cells[0].Paragraphs[0].Append("_" + txtName4.Text + underline).UnderlineStyle(UnderlineStyle.thick).Bold().FontSize(9); //STUDENT NAME
underline = "";
t4.Rows[1].Cells[1].Paragraphs[0].Append("____").UnderlineStyle(UnderlineStyle.thick).FontSize(9);
t4.Rows[2].Cells[1].Paragraphs[0].Append("____").UnderlineStyle(UnderlineStyle.thick).FontSize(9);
t4.Rows[3].Cells[1].Paragraphs[0].Append("____").UnderlineStyle(UnderlineStyle.thick).FontSize(9);
t4.Rows[4].Cells[1].Paragraphs[0].Append("____").UnderlineStyle(UnderlineStyle.thick).FontSize(9);
underline = returnCount(16, txtStud1.Text);
t4.Rows[1].Cells[1].Paragraphs[0].Append(txtStud1.Text + underline).UnderlineStyle(UnderlineStyle.thick).Bold().FontSize(9); //STUDENT NO
underline = "";
underline = returnCount(16, txtStud2.Text);
t4.Rows[2].Cells[1].Paragraphs[0].Append(txtStud2.Text + underline).UnderlineStyle(UnderlineStyle.thick).Bold().FontSize(9); //STUDENT NO
underline = "";
underline = returnCount(16, txtStud3.Text);
t4.Rows[3].Cells[1].Paragraphs[0].Append(txtStud3.Text + underline).UnderlineStyle(UnderlineStyle.thick).Bold().FontSize(9); //STUDENT NO
underline = "";
underline = returnCount(16, txtStud4.Text);
t4.Rows[4].Cells[1].Paragraphs[0].Append(txtStud4.Text + underline).UnderlineStyle(UnderlineStyle.thick).Bold().FontSize(9); //STUDENT NO
t4.Rows[1].Cells[2].Paragraphs[0].Append("_________________").UnderlineStyle(UnderlineStyle.thick).FontSize(9);
t4.Rows[2].Cells[2].Paragraphs[0].Append("_________________").UnderlineStyle(UnderlineStyle.thick).FontSize(9);
t4.Rows[3].Cells[2].Paragraphs[0].Append("_________________").UnderlineStyle(UnderlineStyle.thick).FontSize(9);
t4.Rows[4].Cells[2].Paragraphs[0].Append("_________________").UnderlineStyle(UnderlineStyle.thick).FontSize(9);
t4.Rows[1].Cells[3].Paragraphs[0].Append("_________________").UnderlineStyle(UnderlineStyle.thick).FontSize(9);
t4.Rows[2].Cells[3].Paragraphs[0].Append("_________________").UnderlineStyle(UnderlineStyle.thick).FontSize(9);
t4.Rows[3].Cells[3].Paragraphs[0].Append("_________________").UnderlineStyle(UnderlineStyle.thick).FontSize(9);
t4.Rows[4].Cells[3].Paragraphs[0].Append("_________________").UnderlineStyle(UnderlineStyle.thick).FontSize(9);
document.InsertTable(t4);
document.InsertParagraph();
var p7 = document.InsertParagraph("PHARMACY LABORATORY'S COPY").FontSize(9).Bold().Alignment = Alignment.center;
document.Save();
Process.Start("WINWORD.EXE", filename);
}
if (success)
{
Log = LogManager.GetLogger("generateIssuanceForm");
string newLine = System.Environment.NewLine;
Log.Info("A Issuance form has been generated with the following details: " + newLine +
"Date Requested: " + txtDate.Text + newLine +
"Subject: " + cmbSubj.Text + newLine +
"Section: " + txtSect.Text + newLine +
"Schedule: " + txtSched.Text + newLine +
"Professor: " + txtProf.Text + newLine +
"Locker No.: " + txtLock.Text + newLine +
"Issued by: " + txtIssued.Text
);
emptyFields();
}
break;
case MessageBoxResult.No:
break;
}
}
}
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 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;
}
}