public bool UpdateStaff(Staff staff) { MySqlConnection cnn = DBUtility.getConnection(); if (cnn != null) { try { cnn.Open(); const string SQL = "UPDATE staffs SET staffname = @staffname, staffpassword = @staffpassword, stafftype= @stafftype, commisionrate= @commisionrate, storeid=@storeid where staffid= @staffid"; MySqlCommand command = new MySqlCommand(SQL, cnn); command.Prepare(); command.Parameters.AddWithValue("@staffname", staff.Staffname); command.Parameters.AddWithValue("@staffpassword", staff.Staffpassword); command.Parameters.AddWithValue("@stafftype", staff.Stafftype); command.Parameters.AddWithValue("@commisionrate", staff.Commisionrate); command.Parameters.AddWithValue("@staffid", staff.Staffid); command.Parameters.AddWithValue("@storeid", staff.StoreId); if (command.ExecuteNonQuery() > 0) { return true; } } catch (MySqlException e) { Console.WriteLine(e); } finally { cnn.Close(); } } return false; }
public Transaction(decimal incomeamount, decimal expenseamount, Staff createdby, string remark) { this.incomeamount = incomeamount; this.expenseamount = expenseamount; this.createdby = createdby; this.remark = remark; }
public Transaction(int transactionid, DateTime transactiondate, decimal incomeamount, decimal expenseamount, Staff createdby, string remark) { this.transactionid = transactionid; this.transactiondate = transactiondate; this.incomeamount = incomeamount; this.expenseamount = expenseamount; this.createdby = createdby; this.remark = remark; }
public Product(int productid, String productcode, String barcode , String productname ,String description , decimal pricein ,decimal priceout, String remark ,Staff createdby , Staff updatedby ,Category category ) { this.productid = productid; this.productcode = productcode; this.barcode = barcode; this.productcode = productcode; this.productname = productname; this.description = description; this.pricein = pricein; this.priceout = priceout; this.remark = remark; this.createdby = createdby; this.updatedby = updatedby; this.category = category; }
public bool AddStaff(Staff staff) { MySqlConnection cnn = DBUtility.getConnection(); if (cnn != null) { cnn.Open(); MySqlTransaction transaction = cnn.BeginTransaction(); try { const string SQL = @"INSERT INTO staffs(staffname, staffpassword, stafftype, lastlogin, commisionrate, storeid) VALUES(LOWER(@staffname), @staffpassword, @stafftype, @lastlogin, @commisionrate, @storeid);"; MySqlCommand command = new MySqlCommand(SQL, cnn); command.Prepare(); command.Parameters.AddWithValue("@staffname", staff.Staffname); command.Parameters.AddWithValue("@staffpassword", staff.Staffpassword); command.Parameters.AddWithValue("@stafftype", staff.Stafftype); command.Parameters.AddWithValue("@lastlogin", staff.Lastlogin); command.Parameters.AddWithValue("@commisionrate", staff.Commisionrate); command.Parameters.AddWithValue(@"storeid", staff.StoreId); if (command.ExecuteNonQuery() > 0) { transaction.Commit(); return true; } } catch (MySqlException e) { Console.WriteLine(e); transaction.Rollback(); } finally { cnn.Close(); } } return false; }
public Staff Login(string username, string password) { MySqlConnection cnn = DBUtility.getConnection(); Staff staff = null; try { if (cnn != null) { cnn.Open(); const string SQL = @"SELECT staffid , staffname , staffpassword , stafftype , lastlogin , storeid FROM staffs WHERE staffname = LOWER(@staffname) AND staffpassword = @staffpassword"; MySqlCommand command = new MySqlCommand(SQL, cnn); command.Prepare(); command.Parameters.AddWithValue("@staffname", username); command.Parameters.AddWithValue(@"staffpassword", password); MySqlDataReader reader = command.ExecuteReader(); while(reader.Read()) { staff = new Staff(); staff.Staffid = reader.GetInt16("staffid"); staff.Staffname = reader.GetString("staffname"); staff.Staffpassword = reader.GetString("staffpassword"); staff.Stafftype = reader.GetString("stafftype"); staff.StoreId = reader.GetInt16("storeid"); staff.Lastlogin = reader.GetDateTime("lastlogin"); } } } catch (Exception e) { Console.WriteLine(e); } finally { cnn.Close(); } return staff; }
// TODO: TO GET ALL THE MEMBERS public List<Member> getAllMembers() { try { List<Member> members = new List<Member>(); String sql = @"SELECT memberid , membercode , membername , phonenumber , members.createddate , createddate , (SELECT staffname FROM staffs WHERE staffs.staffid = members.createdby) AS createdby , updateddate , (SELECT staffname FROM staffs WHERE staffs.staffid = members.updatedby) AS updatedby , discountrate FROM members"; DataSet ds = DBUtility.ExecuteQuery(sql); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { Member member = new Member(); member.Memberid = (int)ds.Tables[0].Rows[i]["memberid"]; member.MemberCode = ds.Tables[0].Rows[i]["membercode"].ToString(); member.Membername = ds.Tables[0].Rows[i]["membername"].ToString(); member.Phonenumber = ds.Tables[0].Rows[i]["phonenumber"].ToString(); member.Createddate = (System.DateTime)ds.Tables[0].Rows[i]["createddate"]; Staff createdStaff = new Staff(); createdStaff.Staffname = ds.Tables[0].Rows[i]["createdby"].ToString(); member.Createdby = createdStaff; member.Createddate = (System.DateTime)ds.Tables[0].Rows[i]["createddate"]; Staff updatedStaff = new Staff(); updatedStaff.Staffname = ds.Tables[0].Rows[i]["updatedby"].ToString(); member.Updatedby = updatedStaff; member.Updateddate = (System.DateTime)ds.Tables[0].Rows[i]["updateddate"]; } return members; } catch (Exception ex) { Console.WriteLine(ex.ToString()); return null; } }
// TODO: TO GET A MEMBER BY ID public Member getMemberById(int id) { try { Member member = new Member(); String sql = @"SELECT memberid , membercode , membername , phonenumber , createddate , (SELECT staffname FROM staffs WHERE staffs.staffid = members.createdby) AS createdby , updateddate , (SELECT staffname FROM staffs WHERE staffs.staffid = members.updatedby) AS updatedby , discountrate , (SELECT membertypeid FROM membertypes WHERE membertypes.membertypeid = members.membertypeid) AS membertypeid , (SELECT membertypename FROM membertypes WHERE membertypes.membertypeid = members.membertypeid) AS membertypename FROM members WHERE memberid = @p1"; DataSet ds = DBUtility.ExecuteQuery(sql, id); member.Memberid = (int)ds.Tables[0].Rows[0]["memberid"]; member.MemberCode = ds.Tables[0].Rows[0]["membercode"].ToString(); member.Membername = ds.Tables[0].Rows[0]["membername"].ToString(); member.Phonenumber = ds.Tables[0].Rows[0]["phonenumber"].ToString(); member.Createddate = (System.DateTime)ds.Tables[0].Rows[0]["createddate"]; Staff createdStaff = new Staff(); createdStaff.Staffname = ds.Tables[0].Rows[0]["createdby"].ToString(); member.Createdby = createdStaff; member.Createddate = (System.DateTime)ds.Tables[0].Rows[0]["createddate"]; if (!DBNull.Value.Equals(ds.Tables[0].Rows[0]["updatedby"])) { Staff updatedStaff = new Staff(); updatedStaff.Staffname = ds.Tables[0].Rows[0]["updatedby"].ToString(); member.Updatedby = updatedStaff; member.Updateddate = (System.DateTime)ds.Tables[0].Rows[0]["updateddate"]; } member.Discountrate = decimal.Parse(ds.Tables[0].Rows[0]["discountrate"].ToString()); return member; } catch (Exception ex) { Console.WriteLine(ex.ToString()); return null; } }
public Invoice getInvoice(int invoiceId) { MySqlConnection con = DBUtility.getConnection(); if (con != null) con.Open(); { try { MySqlCommand cmdInv = new MySqlCommand("SELECT I.invoiceid, I.invoicedate, I.remark, I.discount, ID.quantity, ID.pricein, ID.priceout, ID.discount id_discount, S.staffid, S.staffname, S.stafftype, M.memberid, M.membername, M.membercode, M.phonenumber, M.createddate, P.productid, P.productcode, P.barcode, P.productname, P.description FROM Invoices I INNER JOIN InvoiceDetail ID ON I.invoiceid=ID.invoiceid INNER JOIN Products P ON ID.productid=P.productid INNER JOIN Staffs S ON I.Staffid=S.Staffid INNER JOIN Members M ON I.memberid=M.memberid WHERE I.invoiceid=" + invoiceId, con); MySqlDataReader drInv = cmdInv.ExecuteReader(); ArrayList arrInvDetail = new ArrayList(); Invoice inv = new Invoice(); inv.Invoiceid = invoiceId; Member member = new Member(); Staff staff = new Staff(); while (drInv.Read()) { inv.Invoicedate = drInv.GetDateTime("invoicedate"); inv.Discount = drInv.GetDecimal("discount"); inv.Remark = DBUtility.SafeGetString(drInv, "remark"); InvoiceDetail invDetail = new InvoiceDetail(); Product product = new Product(); product.Productid = drInv.GetInt16("productid"); product.Productname = DBUtility.SafeGetString(drInv, "productname"); product.Productcode = DBUtility.SafeGetString(drInv, "productCode"); product.Barcode = DBUtility.SafeGetString(drInv, "barcode"); product.Quantity = drInv.GetDecimal("quantity"); product.Description = DBUtility.SafeGetString(drInv, "description"); invDetail.Product = product; invDetail.Pricein = drInv.GetDecimal("pricein"); invDetail.Priceout = drInv.GetDecimal("priceout"); invDetail.Quantity = drInv.GetDecimal("quantity"); invDetail.Dicount = drInv.GetDecimal("id_discount"); arrInvDetail.Add(invDetail); member.Memberid = drInv.GetInt16("memberid"); member.Membername = DBUtility.SafeGetString(drInv, "membername"); member.Phonenumber = DBUtility.SafeGetString(drInv, "phonenumber"); staff.Staffid = drInv.GetInt16("staffid"); staff.Staffname = drInv.GetString("staffname"); staff.Stafftype = drInv.GetString("stafftype"); inv.Staff = staff; inv.Member = member; } inv.InvoiceDetail = arrInvDetail; return inv; } catch (MySqlException e) { Console.WriteLine(e.ToString()); } finally { con.Close(); } } return null; }
public Product getProductById(String productId, int storeid) { MySqlConnection cnn = DBUtility.getConnection(); if (cnn != null) { try { cnn.Open(); const string SQL = "SELECT P.productid,productcode,barcode,productname,description,pricein,priceout,remark,createddate,createdby,updateddate,updatedby,categoryid, Quantity, ReturnQuantity FROM products P LEFT JOIN StoreProduct SP ON P.productid=SP.productid WHERE P.productid=@productId AND SP.storeid = @storeid;"; MySqlCommand command = new MySqlCommand(SQL, cnn); command.Prepare(); command.Parameters.AddWithValue("@productId", productId); command.Parameters.AddWithValue("@storeid", storeid); MySqlDataReader reader = command.ExecuteReader(); Product product = null; while (reader.Read()) { product = new Product(); product.Productid = reader.GetInt16("productid"); product.Productcode = DBUtility.SafeGetString(reader, "productcode"); product.Barcode = DBUtility.SafeGetString(reader, "barcode"); product.Productname = DBUtility.SafeGetString(reader, "productname"); product.Quantity = reader.GetInt16("quantity"); product.Description = DBUtility.SafeGetString(reader, "description"); product.Pricein = reader.GetDecimal("pricein"); product.Priceout = reader.GetDecimal("priceout"); product.Returnquantity = reader.GetInt16("returnquantity"); product.Remark = DBUtility.SafeGetString(reader, "remark"); product.Createddate = reader.GetDateTime("createddate"); Staff createdby = new Staff(); createdby.Staffid = reader.GetInt16("createdby"); Staff updatedby = new Staff(); updatedby.Staffid = reader.GetInt16("updatedby"); product.Createdby = createdby; product.Updatedby = updatedby; product.Updateddate = reader.GetDateTime("updateddate"); Category category = new Category(); category.Categoryid = reader.GetInt16("categoryid"); product.Category = category; } return product; } catch (MySqlException e) { Console.WriteLine(e); } finally { cnn.Close(); } } return null; }