public bool addFee(fee f, gymMember g)
{
try
{
paymentCalculation(f, g);
DataLayer.dbConnect dbfee = new DataLayer.dbConnect();
dbfee.openConnection();
string qf1 = "insert into tbl_fee (amount,service,valde_date,paid_date,paid_time,memberID) values(@amount,@service,@valid_date,@paid_date,@paidTime,@memerID)";
SqlCommand cmd1 = new SqlCommand(qf1, dbfee.getConnection());
cmd1.Parameters.AddWithValue("@amount", f.newAmount);
cmd1.Parameters.AddWithValue("@service", f.service);
cmd1.Parameters.AddWithValue("@valid_date", f.PaymentvalidDate);
cmd1.Parameters.AddWithValue("@paid_date", f.paidDate);
cmd1.Parameters.AddWithValue("@memerID", f.memberID);
cmd1.Parameters.AddWithValue("@paidTime", f.paidTime);
cmd1.ExecuteNonQuery();
return(true);
}
catch (Exception ext)
{
MessageBox.Show(ext.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
throw;
}
}
public void paymentCalculation(fee f, gymMember g)
{
if (string.IsNullOrWhiteSpace(f.lastVPaymentDate))
{
f.lastVPaymentDate = g.joinedDate;
}
}
public bool deleteMemeber(gymMember gm)
{
bool tempdel = false;
DataLayer.dbConnect cond = new DataLayer.dbConnect();
cond.openConnection();
SqlTransaction trnd = cond.getConnection().BeginTransaction();
try
{
string qd = "DELETE tbl_gymMember WHERE memberID=@mem ";
SqlCommand cmdel = new SqlCommand(qd, cond.getConnection());
cmdel.Parameters.AddWithValue("@mem", gm.MemberID);
cmdel.Transaction = trnd;
cmdel.ExecuteNonQuery();
string qd1 = "DELETE tbl_member WHERE regNo=@mem ";
SqlCommand cmdel1 = new SqlCommand(qd1, cond.getConnection());
cmdel1.Parameters.AddWithValue("@mem", gm.MemberID);
cmdel1.Transaction = trnd;
cmdel1.ExecuteNonQuery();
trnd.Commit();
cond.closeConnection();
tempdel = true;
}
catch (Exception edel)
{
trnd.Rollback();
throw;
}
if (tempdel == true)
{
return(true);
}
else
{
return(false);
}
}
public bool searchMemberDerailsFor_fee(gymMember gm)
{
try
{
DataLayer.dbConnect dbmem_seach = new DataLayer.dbConnect();
dbmem_seach.openConnection();
bool temp = false;
string query1 = "SELECT * FROM tbl_member WHERE regNo=@reg OR name=@name OR nic=@nic";
SqlCommand cmd1 = new SqlCommand(query1, dbmem_seach.getConnection());
cmd1.Parameters.AddWithValue("@reg", gm.MemberID);
cmd1.Parameters.AddWithValue("@name", gm.name);
cmd1.Parameters.AddWithValue("@nic", gm.nic);
DataTable dtq = new DataTable();
SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
da1.Fill(dtq);
if (dtq.Rows.Count > 0)
{
gm.MemberID = int.Parse(dtq.Rows[0]["regNo"].ToString());
gm.name = dtq.Rows[0]["name"].ToString();
gm.nic = dtq.Rows[0]["nic"].ToString();
// gm.addresss = dtq.Rows[0]["address"].ToString();
// gm.phone = int.Parse(dtq.Rows[0]["phone"].ToString());
temp = true;
}
string query2 = "SELECT * FROM tbl_gymMember WHERE memberID=@id";
SqlCommand cmd2 = new SqlCommand(query2, dbmem_seach.getConnection());
SqlDataAdapter da2 = new SqlDataAdapter(cmd2);
DataTable dt2 = new DataTable();
cmd2.Parameters.AddWithValue("@id", gm.MemberID);
da2.Fill(dt2);
bool temp1 = false;
if (dt2.Rows.Count > 0)
{
gm.paymentPlan = dt2.Rows[0]["payment_plan"].ToString();
temp1 = true;
}
if (temp == true && temp1 == true)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception er)
{
throw;
}
}
public bool updateGymMember(gymMember g)
{
bool tup = false;
try
{
DataLayer.dbConnect dbu = new DataLayer.dbConnect();
//SqlTransaction trnup = dbu.getConnection().BeginTransaction();
dbu.openConnection();
SqlCommand cmdu = null;
string qup = "UPDATE tbl_member set name=@name, dob=@dob, address=@address , nic=@nic, gender=@gender, phone=@phone where regNo=@reg ";
cmdu = new SqlCommand(qup, dbu.getConnection());
cmdu.Parameters.AddWithValue("@name", g.name);
cmdu.Parameters.AddWithValue("@dob", g.dob);
cmdu.Parameters.AddWithValue("@address", g.addresss);
cmdu.Parameters.AddWithValue("@nic", g.nic);
cmdu.Parameters.AddWithValue("@gender", g.gender);
cmdu.Parameters.AddWithValue("@phone", g.phone);
cmdu.Parameters.AddWithValue("@reg", g.MemberID);
//cmdu.Transaction = trnup;
cmdu.ExecuteNonQuery();
string qup2 = "UPDATE tbl_gymMember set email= @email, BMI=@bmi,height=@h,weight=@w, payment_plan= @pp, photo=@photo, fat_level=@fat WHERE memberID=@mem";
SqlCommand cmdup2 = new SqlCommand(qup2, dbu.getConnection());
cmdup2.Parameters.AddWithValue("@mem", g.MemberID);
cmdup2.Parameters.AddWithValue("@email", g.email);
cmdup2.Parameters.AddWithValue("@h", g.height);
cmdup2.Parameters.AddWithValue("@w", g.weight);
cmdup2.Parameters.AddWithValue("@pp", g.paymentPlan);
cmdup2.Parameters.AddWithValue("@photo", g.photo);
cmdup2.Parameters.AddWithValue("@fat", g.fatLevel);
cmdup2.Parameters.AddWithValue("@bmi", g.BMIratio);
// cmdup2.Transaction = trnup;
cmdup2.ExecuteNonQuery();
//trnup.Commit();
tup = true;
}
catch (Exception epu)
{
// trnup.Rollback();
MessageBox.Show(epu.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
throw;
}
if (tup == true)
{
return(true);
}
else
{
return(false);
}
}
public bool save(gymMember mem)
{
DataLayer.dbConnect newdbcon = new DataLayer.dbConnect();
newdbcon.openConnection();
SqlTransaction trans = newdbcon.getConnection().BeginTransaction();
bool status = false, temp = true;
try
{
string qt1 = "select * from tbl_member where name=@name and nic=@nic";
SqlCommand qt = new SqlCommand(qt1, newdbcon.getConnection());
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(qt);
qt.Parameters.AddWithValue("@name", mem.name);
qt.Parameters.AddWithValue("@nic", mem.nic);
qt.Transaction = trans;
da.Fill(dt);
if (dt.Rows.Count > 0)
{
temp = false;
}
if (temp == true)
{
SqlCommand cmdt = null;
// query 1
string query1 = "INSERT INTO tbl_member (name, dob, address,nic,gender, phone) VALUES (@name,@dob,@address,@nic,@gender,@phone)";
//initialize sqlCommand
cmdt = new SqlCommand(query1, newdbcon.getConnection());
// add values to command using parameters
cmdt.Parameters.AddWithValue("@name", mem.name);
cmdt.Parameters.AddWithValue("@dob", mem.dob);
cmdt.Parameters.AddWithValue("@address", mem.addresss);
cmdt.Parameters.AddWithValue("@gender", mem.gender);
cmdt.Parameters.AddWithValue("@phone", mem.phone);
cmdt.Parameters.AddWithValue("@nic", mem.nic);
cmdt.Transaction = trans;
cmdt.ExecuteNonQuery();
string query2 = "SELECT regNo from tbl_member where name=@memName and nic=@nic";
SqlCommand cmdt1 = new SqlCommand(query2, newdbcon.getConnection());
cmdt1.Parameters.AddWithValue("@memName", mem.name);
cmdt1.Parameters.AddWithValue("@nic", mem.nic);
cmdt1.Transaction = trans;
SqlDataReader dr = cmdt1.ExecuteReader();
while (dr.Read())
{
mem.MemberID = (int)dr["regNo"];
}
dr.Close();
string query3 = "INSERT INTO tbl_gymMember values (@regno,@email,@joinedDate,@bmi,@height,@weight,@payplan,@photo,@fatLevel)";
SqlCommand cmdt2 = new SqlCommand(query3, newdbcon.getConnection());
cmdt2.Parameters.AddWithValue("@regno", mem.MemberID);
cmdt2.Parameters.AddWithValue("@email", mem.email);
cmdt2.Parameters.AddWithValue("@joinedDate", mem.joinedDate);
cmdt2.Parameters.AddWithValue("@bmi", mem.BMIratio);
cmdt2.Parameters.AddWithValue("@height", mem.height);
cmdt2.Parameters.AddWithValue("@weight", mem.weight);
cmdt2.Parameters.AddWithValue("@payplan", mem.paymentPlan);
cmdt2.Parameters.AddWithValue("@photo", mem.photo);
cmdt2.Parameters.AddWithValue("@fatLevel", mem.fatLevel);
cmdt2.Transaction = trans;
cmdt2.ExecuteNonQuery();
trans.Commit();
newdbcon.closeConnection();
status = true;
}
else
{
MessageBox.Show("This member already exists.", "Infromation", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ep)
{
trans.Rollback();
MessageBox.Show(ep.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
if (status == true)
{
return(true);
}
else
{
return(false);
}
}
public gymMember search(int reNo, string name, string nic)
{
try
{
DataLayer.dbConnect dbmem_seach = new DataLayer.dbConnect();
dbmem_seach.openConnection();
bool temp = false;
string query1 = "SELECT * FROM tbl_member WHERE regNo=@reg OR name=@name OR nic=@nic";
SqlCommand cmd1 = new SqlCommand(query1, dbmem_seach.getConnection());
cmd1.Parameters.AddWithValue("@reg", reNo);
cmd1.Parameters.AddWithValue("@name", name);
cmd1.Parameters.AddWithValue("@nic", nic);
// SqlDataReader dr1 = cmd1.ExecuteReader();
DataTable dtq = new DataTable();
SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
gymMember gm = new gymMember();
da1.Fill(dtq);
//while (dr1.Read())
//{
// gm.MemberID = (int)dr1["regNo"];
// gm.name = dr1["name"].ToString();
// gm.nic = dr1["nic"].ToString();
// gm.dob = dr1["dob"].ToString();
// gm.gender = dr1["gender"].ToString();
// gm.addresss = dr1["address"].ToString();
// gm.phone = (int)dr1["phone"];
//}
if (dtq.Rows.Count > 0)
{
gm.MemberID = int.Parse(dtq.Rows[0]["regNo"].ToString());
gm.name = dtq.Rows[0]["name"].ToString();
gm.nic = dtq.Rows[0]["nic"].ToString();
gm.dob = dtq.Rows[0]["dob"].ToString();
gm.gender = dtq.Rows[0]["gender"].ToString();
gm.addresss = dtq.Rows[0]["address"].ToString();
gm.phone = dtq.Rows[0]["phone"].ToString();
temp = true;
}
// dr1.Close();
string query2 = "SELECT * FROM tbl_gymMember WHERE memberID=@id";
SqlCommand cmd2 = new SqlCommand(query2, dbmem_seach.getConnection());
SqlDataAdapter da2 = new SqlDataAdapter(cmd2);
DataTable dt2 = new DataTable();
cmd2.Parameters.AddWithValue("@id", gm.MemberID);
da2.Fill(dt2);
bool temp1 = false;
// SqlDataReader dr2 = cmd2.ExecuteReader();
//while (dr2.Read())
//{
// gm.email = dr2["email"].ToString();
// gm.paymentPlan = dr2["payment_plan"].ToString();
// gm.joinedDate = dr2["joined_date"].ToString();
// gm.height = (double)dr2["height"];
// gm.weight = (double)dr2["weight"];
// gm.BMIratio = (double)dr2["BMI"];
// gm.fatLevel = (double)dr2["fat_level"];
// gm.photo = (byte[])dr2["photo"];
if (dt2.Rows.Count > 0)
{
// gm.MemberID = int.Parse(dt2.Rows[0]["regNo"].ToString());
gm.email = dt2.Rows[0]["email"].ToString();
gm.paymentPlan = dt2.Rows[0]["payment_plan"].ToString();
gm.joinedDate = dt2.Rows[0]["joined_date"].ToString();
gm.height = double.Parse(dt2.Rows[0]["height"].ToString());
gm.weight = double.Parse(dt2.Rows[0]["weight"].ToString());
gm.BMIratio = double.Parse(dt2.Rows[0]["BMI"].ToString());
gm.fatLevel = double.Parse(dt2.Rows[0]["fat_level"].ToString());
gm.photo = (byte[])dt2.Rows[0]["photo"];
temp1 = true;
}
dbmem_seach.closeConnection();
//dr1.Close();
// dbmem_seach.closeConnection();
if (temp == true && temp1 == true)
{
MessageBox.Show("Record found", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("No record found.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(gm);
}
catch (Exception er)
{
throw;
}
//gm.joinedDate = (string)dr1["joined_date"];
}
