public int borrw_book(int mem, int book, string acc_date, string ret_date)
{
int ret;
if (alredy_borrowed(book))
{
MessageBox.Show("BOOK Alredy Borrowed", "Error",
MessageBoxButtons.OK, MessageBoxIcon.Information);
ret = -1;
}
else if (pending_yes(mem))
{
MessageBox.Show("User alredy Borrowed 2 Books", "Error",
MessageBoxButtons.OK, MessageBoxIcon.Information);
ret = -1;
}
else
{
string query = "INSERT INTO tbl_borrow(mem_id,book_id,borrow_date,ret_date,pending)VALUES( " +
mem + ", " + book + ", '" + acc_date + "', '" + ret_date + "','YES');" +
"UPDATE tbl_Books SET bkstatus ='Unavailable' WHERE book_ID = " + book + ";";
dt = new Classes.Database();
ret = dt.updateDatabase(query);
}
return(ret);
}
/// <summary>
/// Method send informations about IP adress into database on the server
/// </summary>
public async void Send_IP_Raport_Into_Server()
{
// Preare async method
Task <string> task = new Task <string>(Classes.IP_Data.Get_Data_From_IP);
// Start working async
task.Start();
// Create instance of Database Connection
Classes.Database conn = new Classes.Database();
// Wait for results
var query = await task;
// Send IP data into server
conn.Insert(query);
// Check error flag
if (conn.Error_Flag)
{
// Set main error flag as true
Error_Flag = true;
// Set main error message
Error_Message = conn.Error_Message;
}
}
/// <summary>
/// Method check account exists in Admins_List
/// </summary>
/// <param name="Login"></param>
/// <param name="Password"></param>
/// <returns>
/// If exists return true
/// Else return flase
/// </returns>
public bool Check_Admin(string Login, string Password)
{
if (Admins_List.Exists(x => x.Login == Login && x.Password == Password))
{
Classes.Session.Admin_Login = Login;
Classes.Session.Admin_Password = Password;
// Create instance of Database Connection
Classes.Database conn = new Classes.Database();
// Wait for results
var query = $"UPDATE admins SET last_logedin = NOW() WHERE login='******'";
// Send IP data into server
conn.Update(query);
// Check error flag
if (conn.Error_Flag)
{
// Set main error flag as true
Error_Flag = true;
// Set main error message
Error_Message = conn.Error_Message;
}
return(true);
}
else
{
return(false);
}
}
public int logIN(string username, string password)
{
try
{
int result = 0;
string query = "select * from tbl_Librarian where username = '******';";
Classes.Database db = new Classes.Database();
DataTable tbl = db.retriveData(query);
foreach (DataRow row in tbl.Rows)
{
if (row["libpassword"].ToString() == password)
{
if (row["libtype"].ToString() == "Admin ")
{
result = 1;
}
else
{
result = 2;
}
}
}
return(result);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Method add new admin into database
/// </summary>
/// <param name="Database"></param>
private void Add_New_Admin(Classes.Database Database)
{
// Prepare query
var query = $"INSERT INTO admins (login, password, last_logedin) VALUES ('{Login_Textbox.Text.ToString()}', '{Password1_Textbox.Text.ToString()}', NOW())";
// Add new rew into database
Database.Insert(query);
}
public DataTable fill_book(int mem, int book)
{
dt = new Classes.Database();
string query = "SELECT * FROM tbl_Borrow WHERE mem_id =" + mem + "AND book_id = " + book +
";";
return(dt.retriveData(query));
}
/// <summary>
/// Method check if in our database already exists "login"
/// </summary>
/// <param name="Database"></param>
/// <returns> Number of found objects </returns>
private int Check_If_Exists(Classes.Database Database)
{
// Prepare query
var query = $"SELECT COUNT(*) FROM admins WHERE login = '******'";
// Send ask
// Return result
return(Database.Count(query));
}
private void Create_Button_Click(object sender, EventArgs e)
{
Classes.Database Database = new Classes.Database();
bool flag = true;
// Check if login already exists in database
if (Check_If_Exists(Database) > 0)
{
MessageBox.Show("Nick reserved", "Nick error");
Login_Textbox.BackColor = System.Drawing.Color.Red;
flag = false;
}
if (Login_Textbox.Text.Length < 6)
{
MessageBox.Show("Login must have at list 7 chars", "Password error");
Login_Textbox.BackColor = System.Drawing.Color.Red;
flag = false;
}
// Check password length
if (Password1_Textbox.Text.Length < 6)
{
MessageBox.Show("Password must have at list 7 chars", "Password error");
Password1_Textbox.BackColor = System.Drawing.Color.Red;
Password2_Textbox.BackColor = System.Drawing.Color.Red;
flag = false;
}
// Comparing passwords
if (Password1_Textbox.Text != Password2_Textbox.Text)
{
MessageBox.Show("Different passwords", "Password error");
Password1_Textbox.BackColor = System.Drawing.Color.Red;
Password2_Textbox.BackColor = System.Drawing.Color.Red;
flag = false;
}
if (flag)
{
// Create admin account
Add_New_Admin(Database);
// Close Create_Admin form
this.Close();
}
}
public DataTable searchmem(int memid)
{
string query = @"select * from tbl_Member where mem_ID = " + memid;
try
{
dt = new Classes.Database();
return(dt.retriveData(query));
}
catch (Exception ex)
{
throw ex;
}
}
public int delete_member(int mem)
{
try
{
string query = "DELETE FROM tbl_Member WHERE mem_ID = " + mem;
dt = new Classes.Database();
int ret = dt.updateDatabase(query);
return(ret);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable findb_book(int mem)
{
try
{
// Db Connection
dt = new Classes.Database();
string query = "SELECT tbl_borrow.book_id,book_name,fine_day FROM tbl_Borrow,tbl_Books WHERE mem_id =" +
mem + " AND pending= 'YES' AND tbl_borrow.book_id = tbl_Books.book_ID;";
return(dt.retriveData(query));
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable searchLibrarian(string keywords)
{
Classes.Database dt = new Classes.Database();
string query = @"SELECT * From tbl_Librarian WHERE libFirstName = '" + keywords + "' OR libFirstName LIKE '%" + keywords + "%' " +
"libLastName = '" + keywords + "' OR libLastName LIKE '%" + keywords + "%' ;";
try
{
dt = new Classes.Database();
return(dt.retriveData(query));
}
catch (Exception ex) {
throw ex;
}
}
public DataTable searchbk(int bkid)
{
string query = @"select * from tbl_Books where book_ID = " + bkid;
// query to select by book id
try
{
dt = new Classes.Database();
return(dt.retriveData(query));
// return the data as a data table
}
catch (Exception ex)
{
throw ex;
}
}
public int delete_book(int bkID)
{
try
{
string query = "DELETE FROM tbl_Books WHERE book_ID = " + bkID;
// SQL querry to delete the book record
dt = new Classes.Database();
int ret = dt.updateDatabase(query);
return(ret);
// returing the result of the query 1 == success ,other unsuccesfull
}
catch (Exception ex)
{
throw ex;
}
}
public int addMember(string FName, string LName, string line1, string city, string contact)
{
try
{
string query = "INSERT INTO tbl_Member(memFirstName,memLastName,memaddressLine1,memaddressLine2,memcontactNumber) VALUES(' " +
FName + "' , '" + LName + "' , '" + line1 + "' , '" + city + "' , '" + contact + "' )";
dt = new Classes.Database();
Console.WriteLine(query);
int ret = dt.updateDatabase(query);
return(ret);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Method load list of packs from the server
/// </summary>
public void Load_Packs_List()
{
// Create instance of Database Connection
Classes.Database conn = new Classes.Database();
// Load list of packs from server
conn.Select_Packs(ref Packs_List);
// Check error flag
if (conn.Error_Flag)
{
// Set main error flag as true
Error_Flag = true;
// Set main error message
Error_Message = conn.Error_Message;
}
}
public int addPayment(string p_type, float p_amt, string p_details, int libID, int memID, string curr_time)
{
try
{
dt = new Classes.Database();
string query = "INSERT INTO tbl_Payment (payType, paymentAmount, paymentDetails,lib_id,mem_id,date_time) VALUES('"
+ p_type + "' , " + p_amt + " , '" + p_details + "' , " + libID + " , " + memID + ", '" + curr_time + "' )";
MessageBox.Show(query);
int ret = dt.updateDatabase(query);
return(ret);
}
catch (Exception ex)
{
throw ex;
}
}
public int update_member(string FName, string LName, string line1, string city, string contact, int memid)
{
try
{
string query = "UPDATE tbl_Member SET memFirstName = '" + FName +
"', memLastName = '" + LName + "' ,memaddressLine1 ='" + line1 + "', memaddressLine2 = '" + city
+ "', memcontactNumber = '" + contact + "' WHERE mem_ID =" + memid;
dt = new Classes.Database();
Console.WriteLine(query);
int ret = dt.updateDatabase(query);
return(ret);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable bookSearch(string choice, string keywords)
{
//string query = "SELECT * From tbl_Books WHERE @combo = '@input' OR @combo like '%@input%' ";
string query = @"SELECT book_name AS Name, auther AS Auther, genre AS Genre, bklocation AS Location, bkstatus AS Status" +
" From tbl_Books WHERE " + choice + " = '" + keywords + "'" + "OR " + choice + " LIKE '%" + keywords + "%';";
// Query to get Book List When the user search for the Book
try
{
dt = new Classes.Database();
return(dt.retriveData(query));
// Return the List of Books as a Data Table according to the keywords and by book name or auther
}
catch (Exception ex)
{
throw ex;
}
}
public int addLibrarian(string FName, string LName, string line1, string city, string contact,
string uername, string password, string libtype)
{
try
{
string query = "INSERT INTO tbl_Librarian(libFirstName,libLastName,addressLine1,addressLine2,contactNumber,username,libpassword,libtype)VALUES(" + "'" + FName + "'" + ","
+ "'" + LName + "'" + "," + "'" + line1 + "'" + ","
+ "'" + city + "'" + "," + "'" + contact + "'" + "," +
"'" + uername + "'" + "," + "'" + password + "'" + "," +
"'" + libtype + "'" + ")";
Classes.Database db = new Classes.Database();
int ret = db.updateDatabase(query);
return(ret);
}
catch (Exception ex)
{
throw ex;
}
}
public int updateBook(int bkID, string BookName, string Auther, string Genre, float Price, float FineDay,
string Location, string Status)
{
try
{
// Db Connection
dt = new Classes.Database();
string query = "UPDATE tbl_Books SET book_name ='" + BookName + "', auther = '" + Auther + "',genre ='" +
Genre + "', price = " + Price + " ,fine_day =" + FineDay + ",bklocation = '" + Location + "', bkstatus ='" + Status
+ "' WHERE book_ID = " + bkID;
// SQL Auerry to update Book Details
int ret = dt.updateDatabase(query); // runnig the SQL query
return(ret); // returing the result of the query 1 == success ,other unsuccesfull
}
catch (Exception ex)
{
throw ex;
}
}
// Real Function
public int addBook(string BookName, string Auther, string Genre, float Price, float FineDay,
string Location, string Status)
{
try
{
// Db Connection
dt = new Classes.Database();
string query = "INSERT INTO tbl_Books(book_name,auther,genre,price,fine_day,bklocation,bkstatus)VALUES(" + "'" + BookName +
"'" + "," + "'" + Auther + "'" + "," + "'" + Genre + "'" + "," +
Price + "," + FineDay + "," +
"'" + Location + "'" + "," + "'" + Status + "'" + ")";
// SQL Auerry to insert Book Details
int ret = dt.updateDatabase(query); // runnig the SQL query
return(ret);
}
catch (Exception ex)
{
throw ex;
}
}
public int ret_book(int mem, int book, string date, float fine)
{
try
{
// Db Connection
string query = "UPDATE tbl_Books SET bkstatus ='Available' WHERE book_ID = " + book + "; " +
"UPDATE tbl_borrow SET pending ='NO' ,fine = " + fine +
" WHERE book_id = " + book + "AND mem_id =" + mem + ";";
DateTime today = DateTime.Today;
dt = new Classes.Database();
int ret = dt.updateDatabase(query);
return(ret);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Method create & send informations about PC adress into database on the server
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public void Send_PC_Raport_Into_Server()
{
// Create class with informations about user's PC
Classes.PC_Data A = new Classes.PC_Data();
var query = A.Query;
// Create instance of Database Connection
Classes.Database conn = new Classes.Database();
// Load list of packs from server
conn.Insert(query);
// Check error flag
if (conn.Error_Flag)
{
// Set main error flag as true
Error_Flag = true;
// Set main error message
Error_Message = conn.Error_Message;
}
}
private Boolean pending_yes(int memid)
{
try
{
dt = new Classes.Database();
string query = "SELECT * FROM tbl_Borrow WHERE mem_id =" + memid + "AND pending = 'YES';";
Boolean pending;
DataTable tbl = dt.retriveData(query);
if (tbl.Rows.Count >= 2)
{
pending = true;
}
else
{
pending = false;
}
return(pending);
}
catch (Exception)
{
throw;
}
}
private Boolean alredy_borrowed(int bookid)
{
string query = @"select * from tbl_Books where book_ID = " + bookid + " AND bkstatus ='Unavailable'";
try
{
dt = new Classes.Database();
Boolean borrowed;
DataTable tbl = dt.retriveData(query);
if (tbl.Rows.Count == 1)
{
borrowed = true;
}
else
{
borrowed = false;
}
return(borrowed);
}
catch (Exception ex)
{
throw ex;
}
}
public void Init()
{
this.DatabaseList = new List <Classes.Database>();
DirectoryInfo cfg = new DirectoryInfo(@"Config\Datenbanken");
if (cfg.Exists)
{
foreach (FileInfo fi in cfg.GetFiles())
{
if (fi.Extension != ".xml")
{
continue;
}
XmlDocument doc = new XmlDocument();
try
{
doc.Load(fi.FullName);
}
catch
{
Controllers.LogController.LogError($"Could not load file {fi.FullName}");
}
Classes.Database db = new Classes.Database();
XmlElement root = doc.DocumentElement;
if (!root.HasAttribute("key") | !root.HasAttribute("name"))
{
continue;
}
db.Name = root.GetAttribute("name");
db.KeyName = root.GetAttribute("key");
foreach (XmlNode node in root.SelectNodes("//filters//filter"))
{
if (db.Filter.Contains(node.InnerText) | node.InnerText == null)
{
continue;
}
db.Filter.Add(node.InnerText);
if (node.Attributes["single"] != null && node.Attributes["single"].Value == "J")
{
db.SingeFilters.Add(node.InnerText);
}
if (node.Attributes["dependency"] != null && node.Attributes["dependency"].Value != "")
{
db.FilterDependencies.Add(new Tuple <string, string>(node.InnerText, node.Attributes["dependency"].Value));
}
}
this.DatabaseList.Add(db);
}
}
this.actSite = 0;
this.cps = 10;
this.newSite = -1;
this.database = this.DatabaseList.FirstOrDefault();
var b = SetPartialList();
}
