// TODO: TO UPDATE THE EXISTING MEMBER
public Boolean updateMemeber(Member member)
{
try
{
String sql = @"UPDATE members
SET membername = @p1
, membercode = @p2
, phonenumber = @p3
, updateddate = NOW()
, updatedby = @p4
, discountrate = @p5
, membertypeid = @p6
WHERE memberid = @p7";
return DBUtility.ExecuteNonQuery(sql, member.Membername, member.MemberCode, member.Phonenumber, member.Updatedby.Staffid, member.Discountrate, member.MemberTypeId, member.Memberid);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
return false;
}
}
// TODO: TO ADD NEW MEMBER
public Boolean addMember(Member member)
{
try
{
MySqlConnection cnn = DBUtility.getConnection();
cnn.Open();
if (cnn != null)
{
MySqlTransaction transaction = cnn.BeginTransaction();
try
{
String sql = @"INSERT INTO members(
membername
, membercode
, phonenumber
, createddate
, createdby
, discountrate
, membertypeid)
VALUES(@memberName
, @memberCode
, @phoneNumber
, NOW()
, @createdBy
, @discountRate
, @membertypeid)";
MySqlCommand cmd = new MySqlCommand(sql, cnn);
cmd.Prepare();
cmd.Parameters.AddWithValue("@memberName", member.Membername);
cmd.Parameters.AddWithValue("@memberCode", member.MemberCode);
cmd.Parameters.AddWithValue("@phoneNumber", member.Phonenumber);
//cmd.Parameters.AddWithValue("@createdDate", member.Createddate);
cmd.Parameters.AddWithValue("@createdBy", member.Createdby.Staffid);
//cmd.Parameters.AddWithValue("@updatedDate", member.Updateddate);
//.Parameters.AddWithValue("@updatedBy", member.Updatedby);
cmd.Parameters.AddWithValue("@discountRate", member.Discountrate);
cmd.Parameters.AddWithValue("@membertypeid", member.MemberTypeId);
int result = cmd.ExecuteNonQuery();
transaction.Commit();
if (result != 0)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
transaction.Rollback();
}
finally
{
cnn.Close();
}
}
}
catch (Exception ex)
{
Console.WriteLine("CONNECTION CATCH : " + ex.ToString());
}
return false;
}
// 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;
}
}
// 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;
}
}
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;
}