/// <summary>
/// display author details and assign author id for navigation
/// </summary>
private void LoadAuthorDetails()
{
errProvider.Clear();
string sqlAuthorById = $"SELECT * FROM Author WHERE AuthorId = '{currentAuthorId}' ORDER BY FirstName, LastName";
string sqlNav = $@"
SELECT
(SELECT TOP(1) AuthorId FROM Author ORDER BY FirstName, LastName) AS FirstID,
(SELECT TOP(1) AuthorId FROM Author ORDER BY FirstName DESC, LastName DESC) AS LastID,
q.PreviousID,
q.NextID,
q.RowNumber,
(SELECT COUNT(*) FROM Author) AS NumOfAuthors
FROM
(SELECT AuthorId,
FirstName,
LastName,
LEAD(AuthorId) OVER (ORDER BY FirstName, LastName) AS NextID,
LAG(AuthorId) OVER (ORDER BY FirstName, LastName) AS PreviousID,
ROW_NUMBER() OVER (ORDER BY FirstName, LastName) As RowNumber
FROM Author
) AS q
WHERE q.AuthorId = '{currentAuthorId}'
ORDER BY FirstName, LastName";
sqlNav = DataAccess.SQLCleaner(sqlNav);
string[] sqlStatements = new string[] { sqlAuthorById, sqlNav };
DataSet ds = DataAccess.GetData(sqlStatements);
DataRow selectedAuthor = ds.Tables["Table"].Rows[0];
txtFirstName.Text = selectedAuthor["FirstName"].ToString();
txtLastName.Text = selectedAuthor["LastName"].ToString();
txtMiddleName.Text = selectedAuthor["MiddleName"].ToString();
txtContactNumber.Text = selectedAuthor["ContactNumber"].ToString();
switch (selectedAuthor["Gender"])
{
case "M":
rdoMale.Checked = true;
break;
case "F":
rdoFemale.Checked = true;
break;
default:
rdoNonBinary.Checked = true;
break;
}
txtNumOfBooks.Text = selectedAuthor["NumOfBooks"].ToString();
cmbMainCategory.SelectedValue = selectedAuthor["MainCategory"];
txtAward.Text = selectedAuthor["Award"].ToString();
numberOfAuthors = Convert.ToInt32(ds.Tables["Table1"].Rows[0]["NumOfAuthors"]);
firstAuthorId = Convert.ToInt32(ds.Tables["Table1"].Rows[0]["FirstID"]);
lastAuthorId = Convert.ToInt32(ds.Tables["Table1"].Rows[0]["LastID"]);
previousAuthorId = ds.Tables["Table1"].Rows[0]["PreviousID"] != DBNull.Value ? Convert.ToInt32(ds.Tables["Table1"].Rows[0]["PreviousID"]) : (int?)null;
nextAuthorId = ds.Tables["Table1"].Rows[0]["NextID"] != DBNull.Value ? Convert.ToInt32(ds.Tables["Table1"].Rows[0]["NextID"]) : (int?)null;
currentRecord = Convert.ToInt32(ds.Tables["Table1"].Rows[0]["RowNumber"]);
GetLatestBooks();
DisplayCurrentPosition();
}
private void btn_borrow_Click(object sender, EventArgs e)
{
//button for borrowing
String sqlconstring = "Data Source = LOCALHOST; Initial Catalog = it_2d; username = root; password = ''";
MySqlConnection sqlconnect = new MySqlConnection(sqlconstring);
MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter();
DataSet DS = new DataSet();
sqlconnect.Open();
//check if the user already borrowed an item that needed to be returned
MySqlCommand check_User_Name = new MySqlCommand("SELECT * FROM tbl_borrow WHERE (`b_no` = '" + txt_bnum.Text + "') ", sqlconnect);
if (txt_bnum.Text == "" || txt_id.Text == "" || txt_num.Text == "")
{
MessageBox.Show("Incomplete Fields", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else {
if (check_User_Name.ExecuteScalar() != null)
{
string UserExist = check_User_Name.ExecuteScalar().ToString();
if (UserExist != null)
{
DateTime time = DateTime.Now;
string format = "yyyy-MM-dd HH:mm:ss";
var mytime = time.ToString(format);
MySqlCommand sqlcommand = new MySqlCommand();
//update the copies deduction
sqlcommand.CommandText = "UPDATE `tbl_itemlist` SET `b_copies` = `b_copies` + 1 where `b_no` = '" + txt_bnum.Text + "'";
sqlcommand.CommandType = CommandType.Text;
sqlcommand.Connection = sqlconnect;
sqlcommand.ExecuteNonQuery();
sqlDataAdapter.SelectCommand = sqlcommand;
//update tbl_penalty for record
sqlcommand.CommandText = "update tbl_penalty set d_returned='" + this.dtp_returned.Text + "' where b_no='" + txt_bnum.Text + "'AND `u_un` = '" + txt_num.Text + "';";
sqlcommand.CommandType = CommandType.Text;
sqlcommand.Connection = sqlconnect;
sqlcommand.ExecuteNonQuery();
sqlDataAdapter.SelectCommand = sqlcommand;
//insert the transaction to return
sqlcommand.CommandText = "INSERT INTO `it_2d`.`tbl_return` (`b_no`, `u_un`,`d_returned`) VALUES ('" + txt_bnum.Text + "' ,'" + txt_num.Text + "','" + this.dtp_returned.Text + "');";
sqlcommand.CommandType = CommandType.Text;
sqlcommand.Connection = sqlconnect;
sqlcommand.ExecuteNonQuery();
sqlDataAdapter.SelectCommand = sqlcommand;
//delete the user account
sqlcommand.CommandText = "DELETE FROM `tbl_borrow` WHERE `u_un` = ('" + txt_num.Text + "') ";
sqlcommand.CommandType = CommandType.Text;
sqlcommand.Connection = sqlconnect;
sqlcommand.ExecuteNonQuery();
sqlDataAdapter.SelectCommand = sqlcommand;
//display and refresh the datagridview
sqlcommand.CommandText = "SELECT `b_no` as 'Accession No.', `b_title` as 'Title', `b_author` as 'Author', `u_un` as 'Account No.', `d_borrowed` as 'Date Borrowed' FROM `tbl_borrow` WHERE 1 ";
sqlcommand.CommandType = CommandType.Text;
sqlcommand.Connection = sqlconnect;
sqlcommand.ExecuteNonQuery();
sqlDataAdapter.SelectCommand = sqlcommand;
sqlDataAdapter.Fill(DS, "KAIBIGAN");
dataGridView1.DataSource = DS;
dataGridView1.DataMember = "KAIBIGAN";
//insert the transaction to activitylog
sqlcommand.CommandText = "INSERT INTO `it_2d`.`tbl_activitylog` (`librarian_id`, `activity`,`account_no`,`date_activity`) VALUES ('" + txt_id.Text + "' ,' Return Item','" + txt_num.Text + "','" + mytime + "');";
sqlcommand.CommandType = CommandType.Text;
sqlcommand.Connection = sqlconnect;
sqlcommand.ExecuteNonQuery();
sqlDataAdapter.SelectCommand = sqlcommand;
MessageBox.Show("Item Returned", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
txt_num.Clear();
txt_bnum.Clear();
txt_borrowed.Clear();
}
else
{
//accession no. doesnt exist
MessageBox.Show("Invalid accession no.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
}