private ObservableCollection <itemCollection> LoadCollectionData()
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
list.Clear();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT (br.fName + br.mName + br.lName) as fullName, it.itemName, it.brand, pr.qty, pr.itemCode, pr.transactNo, pr.dateBorrowed, pr.subject, pr.schedule, pr.remarks from tbl_pendingReturn pr INNER JOIN tbl_borrower br on pr.idNo = br.idNo INNER JOIN tbl_items it on it.itemCode = pr.itemCode where pr.status = 'no'", conn))
{
using (DbDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.HasRows)
{
while (reader.Read())
{
string fullName = reader["fullName"].ToString();
string itemName = reader["itemName"].ToString();
string brand = reader["brand"].ToString();
string dateBorrowed = reader["dateBorrowed"].ToString();
string subject = reader["subject"].ToString();
string schedule = reader["schedule"].ToString();
string remarks = reader["remarks"].ToString();
string itemCode = reader["itemCode"].ToString();
string transactNo = reader["transactNo"].ToString();
int qty = Convert.ToInt32(reader["qty"]);
list.Add(new itemCollection
{
fullName = fullName,
itemName = itemName,
brand = brand,
itemCode = itemCode,
transactNo = transactNo,
qty = qty,
dateBorrowed = dateBorrowed,
subject = subject,
schedule = schedule,
status = remarks
});
}
}
}
}
return(list);
}
private void btnProceed_Click(object sender, RoutedEventArgs e)
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd1 = new SqlCeCommand("INSERT INTO tbl_Borrow(transactID, FullName, studentID, Professor, GroupNumber, itemCode, DateBorrowed, subjectName, semester, releasedBy, sy, qtyBorrowed) VALUES(@transactID, @FullName, @studentID, @Professor, @GroupNumber, @itemCode, @DateBorrowed, @subjectName, @semester, @releasedBy, @sy, @qtyBorrowed)", conn))
{
//get values and insert to query string
cmd1.Parameters.Clear();
cmd1.Parameters.AddWithValue("@transactID", "00"); //get number after two zeros and add 1
cmd1.Parameters.AddWithValue("@FullName", txtFullName.Text);
cmd1.Parameters.AddWithValue("@studentID", txtStudNo.Text);
cmd1.Parameters.AddWithValue("@subjectName", txtSubject.Text);
cmd1.Parameters.AddWithValue("@Professor", txtProf.Text);
cmd1.Parameters.AddWithValue("@GroupNumber", txtGroupNo.Text);
cmd1.Parameters.AddWithValue("@DateBorrowed", DateTime.Now.Date.ToString("MM/dd/yyyy"));
cmd1.Parameters.AddWithValue("@semester", "n/a"); //not in UI
cmd1.Parameters.AddWithValue("@releasedBy", "n/a"); //not in UI
cmd1.Parameters.AddWithValue("@sy", "n/a"); //not in UI
cmd1.Parameters.AddWithValue("@qtyBorrowed", "0"); //get from data grid
cmd1.Parameters.AddWithValue("@itemCode", "0"); //get from data grid
try
{
cmd1.ExecuteNonQuery();
string sMessageBoxText = "Borrowed Successfully!";
string sCaption = "Notification";
MessageBoxButton btnMessageBox = MessageBoxButton.OK;
MessageBoxImage icnMessageBox = MessageBoxImage.Information;
MessageBox.Show(sMessageBoxText, sCaption, btnMessageBox, icnMessageBox);
}
catch (SqlCeException ex)
{
MessageBox.Show("Error! Log has been updated with the error.");
return;
}
}
conn.Close();
}
private ObservableCollection <itemCollection> LoadCollectionData()
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
list.Clear();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT (s.fName + ' ' + s.mName + ' ' + s.lName) as fullName, it.itemName, it.brand, b.qtyBorrowed, b.itemCode, b.transactID, b.dateBorrowed, b.subjectName from tbl_Borrow b INNER JOIN tbl_Student s on s.studentID = b.studentID INNER JOIN tbl_Items it on it.itemCode = b.itemCode", conn))
{
using (DbDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.HasRows)
{
while (reader.Read())
{
string fullName = reader["fullName"].ToString();
string itemName = reader["itemName"].ToString();
string brand = reader["brand"].ToString();
string dateBorrowed = reader["dateBorrowed"].ToString();
string subject = reader["subjectName"].ToString();
string itemCode = reader["itemCode"].ToString();
string transactNo = reader["transactID"].ToString();
int qtyBorrowed = Convert.ToInt32(reader["qtyBorrowed"]);
list.Add(new itemCollection
{
fullName = fullName,
itemName = itemName,
brand = brand,
itemCode = itemCode,
transactNo = transactNo,
qty = qtyBorrowed,
dateBorrowed = dateBorrowed,
subject = subject
});
}
}
}
}
return(list);
}
private ObservableCollection <itemCollection> LoadCollectionData()
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
list.Clear();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT itemCode, itemName, itemType, brand, remarks, quantityOnStock from tbl_Items", conn))
{
using (DbDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.HasRows)
{
while (reader.Read())
{
string itemCode = reader["itemCode"].ToString();
string itemName = reader["itemName"].ToString();
string itemType = reader["itemType"].ToString();
string brand = reader["brand"].ToString();
string remarks = reader["remarks"].ToString();
int quantityOnStock = Convert.ToInt32(reader["quantityOnStock"]);
list.Add(new itemCollection
{
itemCode = itemCode,
itemName = itemName,
brand = brand,
itemType = itemType,
qty = quantityOnStock,
remarks = remarks,
});
}
}
}
}
return(list);
}
private ObservableCollection <itemCollection1> LoadCollectionData()
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
list.Clear();
using (SqlCeCommand cmd = new SqlCeCommand(" SELECT itemName, brand, QuantityOnStock, itemCode from tbl_Items", conn))
{
using (DbDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.HasRows)
{
while (reader.Read())
{
string itemName = reader["itemName"].ToString();
string brand = reader["brand"].ToString();
string QuantityOnStock = reader["QuantityOnStock"].ToString();
string itemCode = reader["itemCode"].ToString();
list.Add(new itemCollection1
{
itemName = itemName,
brand = brand,
itemCode = itemCode,
QuantityOnStock = QuantityOnStock,
});
}
}
}
}
return(list);
}
private void SAList_Load()
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
int a = 0;
using (SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM tbl_User Where UserLevel = 'Student Assistant'", conn))
{
using (DbDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.HasRows)
{
while (reader.Read())
{
string Lname = reader["LName"].ToString();
string Fname = reader["FName"].ToString();
string Mname = reader["MName"].ToString();
string Uname = reader["Username"].ToString();
string Upass = reader["UserPW"].ToString();
string Ulevel = reader["UserLevel"].ToString();
System.Windows.Controls.TextBlock newTxtBk = new TextBlock();
newTxtBk.Text = " " + Fname + " " + Mname + " " + Lname;
newTxtBk.Name = "UserTextBlock" + a.ToString();
newTxtBk.Height = 20;
newTxtBk.FontSize = 16;
StudASP.Children.Add(newTxtBk);
//still no image
//need to put source
/*System.Windows.Controls.Image newImg = new Image();
* newImg.Stretch = Stretch.Fill;
* newImg.Height = 100;
* newImg.Width = 100;
* StudASP.Children.Add(newImg);*/
System.Windows.Controls.Label newTxtLbl = new Label();
newTxtLbl.Content = " " + Uname + "\n " + Ulevel;
newTxtLbl.Name = "UserInfoLabel" + a.ToString();
newTxtLbl.VerticalContentAlignment = VerticalAlignment.Top;
newTxtLbl.Height = 30;
StudASP.Children.Add(newTxtLbl);
a++;
}
}
}
}
// int i = 0;
// using (SqlCeCommand cmd1 = new SqlCeCommand("SELECT * FROM User Where UserLevel = 'Administrator'", conn))
using (SqlCeCommand cmd1 = new SqlCeCommand("SELECT * FROM tbl_User Where UserLevel = 'Administrator'", conn))
{
int i = 0;
using (DbDataReader reader = cmd1.ExecuteResultSet(ResultSetOptions.Scrollable))
//using (SqlCeCommand cmd1 = new SqlCeCommand("SELECT * FROM tbl_User Where UserLevel = 'Administrator'", conn))
{
if (reader.HasRows)
{
while (reader.Read())
{
string ALname = reader["LName"].ToString();
string AFname = reader["FName"].ToString();
string AMname = reader["MName"].ToString();
string AUname = reader["Username"].ToString();
string AUlevel = reader["UserLevel"].ToString();
System.Windows.Controls.TextBlock newTxtBk1 = new TextBlock();
newTxtBk1.Text = " " + AFname + " " + AMname + " " + ALname;
newTxtBk1.Name = "AdminTextBlock" + i.ToString();
newTxtBk1.Height = 20;
newTxtBk1.FontSize = 16;
AdminSP.Children.Add(newTxtBk1);
/*System.Windows.Controls.Image newImg1 = new Image();
* newImg1.Stretch = Stretch.Fill;
* newImg1.Height = 100;
* newImg1.Width = 100;
* StudASP.Children.Add(newImg1);*/
System.Windows.Controls.Label newTxtLbl1 = new Label();
newTxtLbl1.Content = " " + AUname + "\n " + AUlevel;
newTxtLbl1.Name = "AdminInfoLabel" + i.ToString();
newTxtLbl1.VerticalContentAlignment = VerticalAlignment.Top;
newTxtLbl1.Height = 30;
AdminSP.Children.Add(newTxtLbl1);
i++;
}
}
}
}
conn.Close();
}
private void btnReturn_Click(object sender, RoutedEventArgs e)
{
string sMessageBoxText = "Are all rows accounted for?";
string sCaption = "Return borrowed items";
MessageBoxButton btnMessageBox = MessageBoxButton.YesNoCancel;
MessageBoxImage icnMessageBox = MessageBoxImage.Warning;
MessageBoxResult dr = MessageBox.Show(sMessageBoxText, sCaption, btnMessageBox, icnMessageBox);
switch (dr)
{
case MessageBoxResult.Yes:
foreach (var row in list)
{
var check = list.FirstOrDefault(x => (x.isReturned == true));
if (check != null)
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("UPDATE tbl_items set qty = qty + @qty where itemCode = @itemCode", conn))
{
cmd.Parameters.AddWithValue("@qty", row.qty);
cmd.Parameters.AddWithValue("@itemCode", row.itemCode);
try
{
cmd.ExecuteNonQuery();
using (SqlCeCommand cmd1 = new SqlCeCommand("UPDATE tbl_pendingReturn set status = 'returned' where transactNo = @transactNo", conn))
{
cmd1.Parameters.AddWithValue("@transactNo", row.transactNo);
try
{
cmd1.ExecuteNonQuery();
MessageBox.Show("Borrowed item(s) has been returned!");
LoadCollectionData();
}
catch (SqlCeException ex)
{
MessageBox.Show("Error! Log has been updated with the error.");
return;
}
}
}
catch (SqlCeException ex)
{
MessageBox.Show("Error! Log has been updated with the error.");
return;
}
}
MessageBox.Show("Existing!");
}
else
{
MessageBox.Show("Not existing!");
}
}
break;
case MessageBoxResult.No:
break;
}
}
private void Create_Click(object sender, RoutedEventArgs e)
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
//Check if record exists
using (SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM tbl_User WHERE AdminID = @UserID", conn))
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@UserID", SAUserID.Text);
using (DbDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (!reader.HasRows)
{
//insert values to database
using (SqlCeCommand cmd1 = new SqlCeCommand("INSERT INTO User(UserID, LName, FName, MName, Username, UserPW, UserLevel) VALUES(@UserID, @LName, @FName, @MName, @Username, @UserPW, @UserLevel) ", conn))
{
//get values and insert to query string
cmd1.Parameters.Clear();
cmd1.Parameters.AddWithValue("@UserID", SAUserID.Text);
cmd1.Parameters.AddWithValue("@LName", SALname.Text);
cmd1.Parameters.AddWithValue("@FName", SAFname.Text);
cmd1.Parameters.AddWithValue("@MName", SAMname.Text);
cmd1.Parameters.AddWithValue("@Username", SAUname.Text);
cmd1.Parameters.AddWithValue("@UserPW", SAPass.Text);
cmd1.Parameters.AddWithValue("@UserLevel", SAUlevel.Text);
try
{
cmd.ExecuteNonQuery();
string sMessageBoxText = "Successfully Created!";
string sCaption = "Notification";
MessageBoxButton btnMessageBox = MessageBoxButton.OK;
MessageBoxImage icnMessageBox = MessageBoxImage.Information;
MessageBox.Show(sMessageBoxText, sCaption, btnMessageBox, icnMessageBox);
}
catch (SqlCeException ex)
{
MessageBox.Show("Error! Log has been updated with the error.");
return;
}
}
}
else
{
string sMessageBoxText = "Record Already Exist!";
string sCaption = "Notification";
MessageBoxButton btnMessageBox = MessageBoxButton.OK;
MessageBoxImage icnMessageBox = MessageBoxImage.Warning;
MessageBox.Show(sMessageBoxText, sCaption, btnMessageBox, icnMessageBox);
}
reader.Close();
reader.Dispose();
}
//For Log
//string fLog = "added a Student Assistant";
//query
}
conn.Close();
}
private void btnReturn_Click(object sender, RoutedEventArgs e)
{
string sMessageBoxText = "Are all rows accounted for?";
string sCaption = "Return borrowed items";
MessageBoxButton btnMessageBox = MessageBoxButton.YesNoCancel;
MessageBoxImage icnMessageBox = MessageBoxImage.Warning;
MessageBoxResult dr = MessageBox.Show(sMessageBoxText, sCaption, btnMessageBox, icnMessageBox);
switch (dr)
{
case MessageBoxResult.Yes:
foreach (var row in list)
{
var check = list.FirstOrDefault(x => (x.isReturned == true));
if (check != null)
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("UPDATE tbl_Items set quantityOnStock = quantityOnStock + @qty where ItemCode = @itemCode", conn))
{
cmd.Parameters.AddWithValue("@qty", row.qty);
cmd.Parameters.AddWithValue("@itemCode", row.itemCode);
try
{
cmd.ExecuteNonQuery();
using (SqlCeCommand cmd1 = new SqlCeCommand("INSERT into tbl_Return (StudentId, ItemCode, BorrowID, DateReturned, ReceivedBy, Remarks, qtyReturned) SELECT StudentID, ItemCode, BorrowID, @dateReturned, @ReceivedBy, @Remarks, @qtyReturned) from tbl_Borrow where transactID = @transactID", conn))
{
cmd1.Parameters.AddWithValue("@transactID", row.transactNo);
cmd1.Parameters.AddWithValue("@dateReturned", DateTime.Today);
cmd1.Parameters.AddWithValue("@receivedBy", "dummy");
cmd1.Parameters.AddWithValue("@qtyReturned", row.qty);
if (string.IsNullOrEmpty(row.remarks))
{
cmd1.Parameters.AddWithValue("@remarks", "none");
}
else
{
cmd1.Parameters.AddWithValue("@remarks", row.remarks);
}
try
{
cmd1.ExecuteNonQuery();
using (SqlCeCommand cmd2 = new SqlCeCommand("DELETE from tbl_Borrow where transactID = @transactID", conn))
{
cmd2.Parameters.AddWithValue("@transactID", row.transactNo);
try
{
cmd2.ExecuteNonQuery();
}
catch (SqlCeException ex)
{
MessageBox.Show("Error! Log has been updated with the error." + ex);
return;
}
}
MessageBox.Show("Borrowed item(s) has been returned!");
LoadCollectionData();
}
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.");
return;
}
}
}
else
{
MessageBox.Show("There are no data to be returned!");
}
}
break;
case MessageBoxResult.No:
break;
}
}