private void genKeyId()
{
SqlConnect con = new SqlConnect();
con.conOpen();
if (con != null)
{
string q = "update tblSettings SET PrevVal = (SELECT NextVal FROM tblSettings WHERE id = 1 ) , NextVal = (SELECT(NextVal + 1) FROM tblSettings WHERE id = 1 ) WHERE id = 1";
SqlCommand cmd = new SqlCommand(q, con.Con);
cmd.ExecuteNonQuery();
string query = "SELECT NextVal FROM tblSettings where id = 1";
SqlCommand x = new SqlCommand(query, con.Con);
SqlDataReader sdr = x.ExecuteReader();
string latestval = "";
while (sdr.Read())
{
latestval = sdr["NextVal"].ToString();
}
string sdate = DateTime.Now.ToString();
DateTime convertsdate = Convert.ToDateTime(sdate.ToString());
string year = convertsdate.Year.ToString();
string format = "101-" + year.ToString() + "-" + latestval.ToString();
txtBarrowerId.Text = format;
}
else
{
return;
}
con.conclose();
}
private void onLoadBooks()
{
SqlConnect con = new SqlConnect();
con.conOpen();
if (con == null)
{
return;
}
else
{
string query = "SELECT A.*, (B.author_fname + ' ' + B.author_lname) AS Author FROM tblBooks A INNER JOIN tblAuthor B ON A.bookauthor_id = B.authorid";
SqlCommand cmd = new SqlCommand(query, con.Con);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
DataTable dt = new DataTable();
sda.Fill(dt);
gridDataLoading.ItemsSource = dt.DefaultView;
gridDataLoading.Columns[0].Visibility = Visibility.Collapsed;
gridDataLoading.Columns[2].Visibility = Visibility.Collapsed;
gridDataLoading.Columns[1].Header = "Book Title";
gridDataLoading.Columns[3].Header = "Date Published";
gridDataLoading.Columns[4].Header = "Quantity";
gridDataLoading.Columns[5].Header = "Stocks";
gridDataLoading.CanUserAddRows = false;
con.conclose();
}
}
private void btnA_Edit_Click(object sender, RoutedEventArgs e)
{
try
{
//let's check first if the textboxes are empty since we cannot allow a blank data
if (txtCcode.Text.Trim() == null || txtCdesc.Text.Trim() == null)
{
MessageBox.Show("Course Data cannot be empty");
return;
}
SqlConnect con = new SqlConnect();
con.conOpen();
if (con != null)
{
//let's check first if the data trying to change is already exist.
string check = "SELECT TOP 1 * FROM tblCourse WHERE coursecode = @course and course_id <> @id";
SqlCommand cmdcheck = new SqlCommand(check, con.Con);
cmdcheck.Parameters.AddWithValue("@course", txtCcode.Text.Trim());
cmdcheck.Parameters.AddWithValue("@id", txtCourseiD.Text.Trim());
SqlDataReader sdr = cmdcheck.ExecuteReader();
if (sdr.Read()) //if theres a record, return, meaning we cannot allow it
{
MessageBox.Show("Cannot update record, the data you are trying to change already exist.");
sdr.Close();
return;
}
else //we can change or update it
{
sdr.Close();
CrudConfiguration update = new CrudConfiguration();
string tablename = "tblCourse"; //tablename
string[] col = new string[] { "coursecode", "coursedesc" }; //tblcolumns to update
string[] values = new string[] { txtCcode.Text.Trim(), txtCdesc.Text.Trim() }; //values to pass
Dictionary <string, string> conds = new Dictionary <string, string>
{
{ "course_id", txtCourseiD.Text.Trim() }
}; //the conditions to make
if (update.updateTrans(tablename, col, values, conds))
{
CourseLoad("");
enabledNewTransbutton();
clearControls();
MessageBox.Show("Record updated successfully");
}
}
}
else
{
return;
}
con.conclose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnB_Edit_Click(object sender, RoutedEventArgs e)
{
try
{
//let's check first if the textboxes are empty since we cannot allow a blank data
if (txtFname.Text.Trim() == null || txtLname.Text.Trim() == null || cboCourse.Text == null)
{
MessageBox.Show("Barrower information cannot be empty");
return;
}
SqlConnect con = new SqlConnect();
con.conOpen();
if (con != null)
{
//let's check first if the data trying to change is already exist.
string check = "SELECT TOP 1 * FROM tblBarrower WHERE barrower_fname = @fname and barrower_lastname = @lname and course = @course and barrower_id <> @id";
SqlCommand cmdcheck = new SqlCommand(check, con.Con);
cmdcheck.Parameters.AddWithValue("@fname", txtFname.Text.Trim());
cmdcheck.Parameters.AddWithValue("@lname", txtLname.Text.Trim());
cmdcheck.Parameters.AddWithValue("@course", cboCourse.SelectedValue.ToString());
cmdcheck.Parameters.AddWithValue("@id", txtBarrowerId.Text.Trim());
SqlDataReader sdr = cmdcheck.ExecuteReader();
if (sdr.Read()) //if theres a record, return, meaning we cannot allow it
{
MessageBox.Show("Cannot update record, the data you are trying to change already exist.");
sdr.Close();
return;
}
else //we can change or update it
{
sdr.Close();
string query = "UPDATE tblBarrower SET barrower_fname = @fname, barrower_lastname = @lname, course = @course WHERE barrower_id = @id";
SqlCommand cmd = new SqlCommand(query, con.Con);
cmd.Parameters.AddWithValue("@fname", txtFname.Text.Trim());
cmd.Parameters.AddWithValue("@lname", txtLname.Text.Trim());
cmd.Parameters.AddWithValue("@course", cboCourse.SelectedValue.ToString());
cmd.Parameters.AddWithValue("@id", txtBarrowerId.Text.Trim());
cmd.ExecuteNonQuery();
gridBarrowerLoad("");
enabledNewTransbutton();
clearControls();
MessageBox.Show("Record updated successfully");
}
}
else
{
return;
}
con.conclose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnA_Save_Click(object sender, RoutedEventArgs e)
{
try
{
//let's check first if textboxes are empty since we cannot insert blank data
if (txtCcode.Text.Trim() == null || txtCdesc.Text.Trim() == null)
{
MessageBox.Show("Course data cannot be empty.");
return;
}
SqlConnect con = new SqlConnect();
con.conOpen();
if (con != null)
{
//check first if the record trying to add is already exist
string check = "SELECT TOP 1 * FROM tblCourse WHERE coursecode = @ccode and coursedesc = @cdesc";
SqlCommand cmdcheck = new SqlCommand(check, con.Con);
cmdcheck.Parameters.AddWithValue("@ccode", txtCcode.Text.Trim());
cmdcheck.Parameters.AddWithValue("@cdesc", txtCdesc.Text.Trim());
SqlDataReader sdr = cmdcheck.ExecuteReader();
if (sdr.Read()) //if theres a record
{
MessageBox.Show("Cannot Insert. The record you are trying to add is already exist. ");
sdr.Close();
return;
}
else //let's insert and add the data
{
sdr.Close();
CrudConfiguration insert = new CrudConfiguration();
string[] col = new string[] { "coursecode", "coursedesc" };
string[] values = new string[] { txtCcode.Text.Trim(), txtCdesc.Text.Trim() };
string tablename = "tblCourse";
if (insert.saveTrans(tablename, col, values))
{
CourseLoad("");
enabledNewTransbutton();
clearControls();
MessageBox.Show("Record saved successfully");
}
}
}
else
{
return;
}
con.conclose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnA_Save_Click(object sender, RoutedEventArgs e)
{
try
{
//let's check first if textboxes are empty since we cannot insert blank data
if (txtAuthorFname.Text.Trim() == null || txtAuthorLname.Text.Trim() == null)
{
MessageBox.Show("Author name cannot be empty.");
return;
}
SqlConnect con = new SqlConnect();
con.conOpen();
if (con != null)
{
//check first if the record trying to add is already exist
string check = "SELECT TOP 1 * FROM tblAuthor WHERE author_fname = @fname and author_lname = @lname";
SqlCommand cmdcheck = new SqlCommand(check, con.Con);
cmdcheck.Parameters.AddWithValue("@fname", txtAuthorFname.Text.Trim());
cmdcheck.Parameters.AddWithValue("@lname", txtAuthorLname.Text.Trim());
SqlDataReader sdr = cmdcheck.ExecuteReader();
if (sdr.Read()) //if theres a record
{
MessageBox.Show("Cannot Insert. The record you are trying to add is already exist. \n Author Firstname: " + txtAuthorFname.Text.Trim() + " \n Author LastName: " + txtAuthorLname.Text.Trim() + " ");
sdr.Close();
return;
}
else //let's insert and add the data
{
sdr.Close();
string query = "INSERT INTO tblAuthor (author_fname, author_lname) VALUES (@fname, @lname)";
SqlCommand cmd = new SqlCommand(query, con.Con);
cmd.Parameters.AddWithValue("@fname", txtAuthorFname.Text.Trim());
cmd.Parameters.AddWithValue("@lname", txtAuthorLname.Text.Trim());
cmd.ExecuteNonQuery();
AuthorLoad("");
enabledNewTransbutton();
clearControls();
MessageBox.Show("Record saved successfully");
}
}
else
{
return;
}
con.conclose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnA_Delete_Click(object sender, RoutedEventArgs e)
{
try
{
SqlConnect con = new SqlConnect();
con.conOpen();
if (con != null)
{
//let's check first if the data trying to delete is being used by the borrowers list.
string check = "SELECT TOP 1 * FROM tblBarrower WHERE course = @id";
SqlCommand cmdcheck = new SqlCommand(check, con.Con);
cmdcheck.Parameters.AddWithValue("@id", txtCourseiD.Text.Trim());
SqlDataReader sdr = cmdcheck.ExecuteReader();
if (sdr.Read()) //if theres a record, return, meaning we cannot allow it
{
MessageBox.Show("Cannot delete record, the data you are trying to delete is being used by the barrowers");
sdr.Close();
return;
}
else //we can delete it
{
sdr.Close();
CrudConfiguration delete = new CrudConfiguration();
string tablename = "tblCourse"; //tablename
Dictionary <string, string> conds = new Dictionary <string, string>
{
{ "course_id", txtCourseiD.Text.Trim() }
}; //the conditions to make
if (delete.deleteTrans(tablename, conds))
{
CourseLoad("");
enabledNewTransbutton();
clearControls();
MessageBox.Show("Record deleted successfully");
}
}
}
else
{
return;
}
con.conclose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnA_Delete_Click(object sender, RoutedEventArgs e)
{
try
{
SqlConnect con = new SqlConnect();
con.conOpen();
if (con != null)
{
//check first if theres any book from tblBooks that uses the id of author
string check = "SELECT TOP 1 * FROM tblBooks WHERE bookauthor_id = @id";
SqlCommand cmdcheck = new SqlCommand(check, con.Con);
cmdcheck.Parameters.AddWithValue("@id", txtAuthoriD.Text.Trim());
SqlDataReader sdr = cmdcheck.ExecuteReader();
if (sdr.Read()) //if theres a record
{
string booktitle = sdr["booktitle"].ToString(); // if ever the booktitle is ask, we can use this.
MessageBox.Show("Cannot delete record, there's a reference from book list.");
sdr.Close();
return;
}
else //we can proceed to deleting of that record
{
sdr.Close();
string query = "DELETE FROM tblAuthor WHERE authorid = @id";
SqlCommand cmd = new SqlCommand(query, con.Con);
cmd.Parameters.AddWithValue("@id", txtAuthoriD.Text.Trim());
cmd.ExecuteNonQuery();
AuthorLoad("");
enabledNewTransbutton();
clearControls();
MessageBox.Show("Record deleted successfully");
}
}
else
{
return;
}
con.conclose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnLogin_Click(object sender, RoutedEventArgs e)
{
SqlConnect con = new SqlConnect();
con.conOpen();
if (con == null)
{
return;
}
else
{
Console.WriteLine("Successfully Connected");
SqlCommand cmd = new SqlCommand("SELECT * FROM tblUsers WHERE username = @username and password = @password ", con.Con);
cmd.Parameters.AddWithValue("@username", txtUsername.Text);
cmd.Parameters.AddWithValue("@password", txtPassword.Password);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
if (sda.Fill(dt) > 0)
{
Console.WriteLine("Valid Account");
con.conclose();
this.Close();
n.Show();
}
else
{
Console.WriteLine("Invalid Account");
}
//SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM tblUsers WHERE username = '******' and password = '******' ", con.Con);
//parametarized
//string sql = "SELECT * FROM tblUsers WHERE username = @username and password = @password ";
//using (SqlCommand cmd = new SqlCommand(sql, con.Con))
//{
// var acctParam = new SqlParameter("username", SqlDbType.VarChar);
// acctParam.Value = txtUsername.Text;
// cmd.Parameters.Add(acctParam);
// var results = cmd.ExecuteReader();
//}
}
}
private void AuthorLoad(string value)
{
var _condition = "";
if (value == "")
{
_condition = "";
}
else
{
//_condition = "WHERE author_fname LIKE '%" + value + "%' OR author_lname LIKE '%" + value + "%'";
_condition = "WHERE author_fname LIKE '%" + @value + "%' OR author_lname LIKE '%" + @value + "%'";
}
SqlConnect con = new SqlConnect();
con.conOpen();
if (con == null)
{
return;
}
else
{
try
{
string q = "SELECT * , (author_fname + ' ' + author_lname )as Author FROM tblAuthor " + _condition;
SqlCommand cmd = new SqlCommand(q, con.Con);
cmd.Parameters.AddWithValue("@value", value);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
DataTable dt = new DataTable();
sda.Fill(dt);
dgAuthor.ItemsSource = dt.DefaultView;
dgAuthor.Columns[0].Visibility = Visibility.Collapsed;
dgAuthor.Columns[1].Visibility = Visibility.Collapsed;
dgAuthor.Columns[2].Visibility = Visibility.Collapsed;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
con.conclose();
}
private void btnB_Delete_Click(object sender, RoutedEventArgs e)
{
try
{
SqlConnect con = new SqlConnect();
con.conOpen();
if (con != null)
{
//let's check first if the data trying to delete has a record in our main transactions.
string check = "SELECT TOP 1 * FROM tblTransaction WHERE barrower_id = @id";
SqlCommand cmdcheck = new SqlCommand(check, con.Con);
cmdcheck.Parameters.AddWithValue("@id", txtBarrowerId.Text.Trim());
SqlDataReader sdr = cmdcheck.ExecuteReader();
if (sdr.Read()) //if theres a record, return, meaning we cannot allow it
{
MessageBox.Show("Cannot delete record, the data you are trying to delete has a history on main transaction.");
sdr.Close();
return;
}
else //we can change or update it
{
sdr.Close();
string query = "DELETE FROM tblBarrower WHERE barrower_id = @id";
SqlCommand cmd = new SqlCommand(query, con.Con);
cmd.Parameters.AddWithValue("@id", txtBarrowerId.Text.Trim());
cmd.ExecuteNonQuery();
gridBarrowerLoad("");
enabledNewTransbutton();
clearControls();
MessageBox.Show("Record deleted successfully");
}
}
else
{
return;
}
con.conclose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void CourseLoad(string value)
{
var _condition = "";
if (value == "")
{
_condition = "";
}
else
{
_condition = "WHERE coursecode LIKE '%" + @value + "%' OR coursedesc LIKE '%" + @value + "%'";
}
SqlConnect con = new SqlConnect();
con.conOpen();
if (con == null)
{
return;
}
else
{
try
{
string q = "SELECT * FROM tblCourse " + _condition;
SqlCommand cmd = new SqlCommand(q, con.Con);
cmd.Parameters.AddWithValue("@value", value);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
DataTable dt = new DataTable();
sda.Fill(dt);
dgCourse.ItemsSource = dt.DefaultView;
dgCourse.Columns[0].Visibility = Visibility.Collapsed;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
con.conclose();
}
private void gridLoading(string value)
{
var _condition = "";
if (value == "")
{
_condition = "";
}
else
{
_condition = "WHERE A.booktitle LIKE '%" + @value + "%' OR B.author_fname LIKE '%" + @value + "%' OR B.author_lname LIKE '%" + @value + "%'";
}
SqlConnect con = new SqlConnect();
con.conOpen();
if (con != null)
{
string q = "SELECT A.*, (B.author_fname + ' ' + B.author_lname) AS Author FROM tblBooks A INNER JOIN tblAuthor B ON A.bookauthor_id = B.authorid " + _condition;
SqlCommand cmd = new SqlCommand(q, con.Con);
cmd.Parameters.AddWithValue("@value", value);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
dgBooks.ItemsSource = dt.DefaultView;
dgBooks.Columns[0].Visibility = Visibility.Collapsed;
dgBooks.Columns[2].Visibility = Visibility.Collapsed;
dgBooks.Columns[1].Header = "Book Title";
dgBooks.Columns[6].Header = "Author";
dgBooks.Columns[3].Header = "Published";
dgBooks.Columns[4].Header = "Quantity";
dgBooks.Columns[5].Header = "Stocks";
}
else
{
return;
}
con.conclose();
}
public void comboBoxLoad()
{
SqlConnect con = new SqlConnect();
con.conOpen();
if (con != null)
{
string q = "SELECT * , (author_fname + ' ' + author_lname) as Author FROM tblAuthor";
SqlCommand cmd = new SqlCommand(q, con.Con);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet dt = new DataSet();
sda.Fill(dt, "tblAuthor");
cboBauthor.ItemsSource = dt.Tables[0].DefaultView;
cboBauthor.DisplayMemberPath = dt.Tables[0].Columns["Author"].ToString();
cboBauthor.SelectedValuePath = dt.Tables[0].Columns["authorid"].ToString();
}
else
{
return;
}
con.conclose();
}
public void loadCourse()
{
SqlConnect con = new SqlConnect();
con.conOpen();
if (con != null)
{
string q = "SELECT course_id, coursecode FROM tblCourse";
SqlCommand cmd = new SqlCommand(q, con.Con);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet dt = new DataSet();
sda.Fill(dt, "tblCourse");
cboCourse.ItemsSource = dt.Tables[0].DefaultView;
cboCourse.DisplayMemberPath = dt.Tables[0].Columns["coursecode"].ToString();
cboCourse.SelectedValuePath = dt.Tables[0].Columns["course_id"].ToString();
}
else
{
return;
}
con.conclose();
}
private void gridBarrowerLoad(string value)
{
var _condition = "";
if (value == "")
{
_condition = "";
}
else
{
_condition = "WHERE A.barrower_fname LIKE '%" + @value + "%' OR A.barrower_lastname LIKE '%" + @value + "%' OR B.coursecode LIKE '%" + @value + "%'";
}
SqlConnect con = new SqlConnect();
con.conOpen();
if (con != null)
{
string q = "SELECT A.*, B.course_id FROM tblBarrower A INNER JOIN tblCourse B ON A.course = B.course_id " + _condition;
SqlCommand cmd = new SqlCommand(q, con.Con);
cmd.Parameters.AddWithValue("@value", value);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
dgBarrower.ItemsSource = dt.DefaultView;
dgBarrower.Columns[0].Header = "Barrower Id";
dgBarrower.Columns[1].Header = "First Name";
dgBarrower.Columns[2].Header = "Last Name";
dgBarrower.Columns[3].Header = "Course";
}
else
{
return;
}
con.conclose();
}
private void btnB_Save_Click(object sender, RoutedEventArgs e)
{
try
{
//let's check first if the textboxes are empty since we cannot allow a blank data
if (txtbTitle.Text.Trim() == null || txtbQty.Text.Trim() == null || cboBauthor.Text == null || dP_datePublished.Text == null)
{
MessageBox.Show("Book info cannot be empty");
return;
}
if (Convert.ToInt32(txtbQty.Text) < 0)
{
MessageBox.Show("Quantity cannot be less than zero");
return;
}
SqlConnect con = new SqlConnect();
con.conOpen();
if (con != null)
{
//let's check first if the data trying to change is already exist.
string check = "SELECT TOP 1 * FROM tblBooks WHERE booktitle = @title";
SqlCommand cmdcheck = new SqlCommand(check, con.Con);
cmdcheck.Parameters.AddWithValue("@title", txtbTitle.Text.Trim());
SqlDataReader sdr = cmdcheck.ExecuteReader();
if (sdr.Read()) //if theres a record, return, meaning we cannot allow it
{
MessageBox.Show("Cannot add this record, the data you are trying to insert already exist.");
sdr.Close();
return;
}
else //we can add or insert it
{
sdr.Close();
string query = "INSERT INTO tblBooks (booktitle, bookauthor_id, datepublished, qty, stocks) VALUES (@title, @author, @dtpub, @qty, @qty)";
SqlCommand cmd = new SqlCommand(query, con.Con);
cmd.Parameters.AddWithValue("@title", txtbTitle.Text.Trim());
cmd.Parameters.AddWithValue("@author", cboBauthor.SelectedValue.ToString());
cmd.Parameters.AddWithValue("@dtpub", dP_datePublished.Text.Trim());
cmd.Parameters.AddWithValue("@qty", txtbQty.Text.Trim());
//cmd.Parameters.AddWithValue("@stocks", txtbStocks.Text.Trim());
cmd.ExecuteNonQuery();
gridLoading("");
enabledNewTransbutton();
clearControls();
MessageBox.Show("Record saved successfully");
}
}
else
{
return;
}
con.conclose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}