public DataTable getPlanList()
{
result.Clear();
sqlQuery = "Select trim(GoiTap.ma) + ' - ' + trim(GoiTap.ten) as List" +
" From GoiTap ";
conString.ConString constring = new conString.ConString(); //this will hide the database info ... sort of
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
// this will query your database and return the result to your datatable
da.Fill(result);
con.Close();
}
}
}
catch (SqlException ex)
{
}
return(result);
}
public DataTable searchData(string keyword)
{
result.Clear();
sqlQuery = " select * from [Attendance]";
sqlQuery += " WHERE ([ma] LIKE CONCAT('%','" + keyword + "','%'))";
sqlQuery += " OR ([alias] LIKE CONCAT('%','" + keyword + "','%'))";
conString.ConString constring = new conString.ConString(); //this will hide the database info ... sort of
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
// this will query your database and return the result to your datatable
da.Fill(result);
con.Close();
}
}
}
catch (SqlException ex)
{
}
return(result);
}
public int getDuration(string maGoi)
{
result.Clear();
sqlQuery = "select thoiHan from GoiTap where ma = '" + maGoi + "'";
int duration = 1;
conString.ConString constring = new conString.ConString(); //this will hide the database info ... sort of
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
// this will query your database and return the result to your datatable
duration = (int)cmd.ExecuteScalar();
con.Close();
}
}
}
catch (SqlException ex)
{
}
return(duration);
}
public DataTable getIncomeofYear(DateTime fromDate, DateTime toDate)
{
result.Clear();
sqlQuery = "SELECT YEAR(dateOfPay) as SalesYear, SUM(price) AS TotalSales" +
" FROM MonthlyIncome" +
" WHERE dateOfPay >= '" + fromDate + "' AND dateOfPay <= '" + toDate + "'" +
" GROUP BY YEAR(dateOfPay)" +
" ORDER BY YEAR(dateOfPay)";
conString.ConString constring = new conString.ConString(); //this will hide the database info ... sort of
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
// this will query your database and return the result to your datatable
da.Fill(result);
con.Close();
}
}
}
catch (SqlException ex)
{
}
return(result);
}
public DataTable getMemberpaymentinfo(String maHV)
{
sqlQuery = "Select Member.ten as HoiVien, MonthlyIncome.dateOfPay," +
" trim(GoiTap.ma) + ' - ' + trim(GoiTap.ten) as Goi, MonthlyIncome.price" +
" From MonthlyIncome inner join Member on Member.ma = MonthlyIncome.maKH inner join" +
" GoiTap on GoiTap.ma = MonthlyIncome.maGoiTap" +
" Where Member.ma = '" + maHV + "'";
conString.ConString constring = new conString.ConString(); //this will hide the database info ... sort of
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
// this will query your database and return the result to your datatable
da.Fill(result);
con.Close();
con.Dispose();
}
}
}
catch (SqlException ex)
{
}
return(result);
}
public DateTime getMemberExpireday(String maHV)
{
DateTime date = DateTime.Today;
sqlQuery = "Select DATEADD(MONTH, SUM(PaymentPeriod), Member.ngayThamGia) AS expireDate" +
" From Member inner join MonthlyIncome on Member.ma = MonthlyIncome.maKH" +
" Where Member.ma = '" + maHV + "'" +
" Group by Member.ngayThamGia, Member.ma";
conString.ConString constring = new conString.ConString();
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
date = DateTime.Parse(cmd.ExecuteScalar().ToString());
con.Dispose();
}
}
}
catch (SqlException ex)
{
}
return(date);
}
public bool getRefication(string UserName, string password)
{
sqlQuery = "SELECT CASE WHEN EXISTS ( " +
"SELECT * FROM LogInData " +
"WHERE username = '******' and passW = '" + password + "') " +
"THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END";
conString.ConString constring = new conString.ConString(); //this will hide the database info ... sort of
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
//con.Open();
//SqlDataAdapter da = new SqlDataAdapter(cmd);
//// this will query your database and return the result to your datatable
//con.Close();
con.Open();
result = (bool)cmd.ExecuteScalar();
con.Close();
}
}
}
catch (SqlException ex)
{
//con.Close();
}
return(result);
}
public DataTable getData()
{
DataTable result = new DataTable();
sqlQuery = "select * from [Log]";
conString.ConString constring = new conString.ConString(); //this will hide the database info ... sort of
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
// this will query your database and return the result to your datatable
da.Fill(result);
con.Close();
}
}
}
catch (SqlException ex)
{
}
return(result);
}
public string getID(string UserName)
{
sqlQuery = "Select NHANVIEN.ID from NHANVIEN inner join LogInData on NHANVIEN.ID = LogInData.ma " +
"where LogInData.username = '******'";
conString.ConString constring = new conString.ConString(); //this will hide the database info ... sort of
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
//con.Open();
//SqlDataAdapter da = new SqlDataAdapter(cmd);
//// this will query your database and return the result to your datatable
//con.Close();
con.Open();
ID = cmd.ExecuteScalar().ToString();
con.Close();
}
}
}
catch (SqlException ex)
{
//con.Close();
}
return(ID);
}
public DataTable searchData(string keyword)
{
sqlQuery = " select * from [TrangThietBi]";
sqlQuery += " WHERE ([ten] LIKE CONCAT('%',@sKeyword,'%'))";
sqlQuery += " OR ([ma] LIKE CONCAT('%',@sKeyword,'%'))";
sqlQuery += " OR ([tinhTrang] LIKE CONCAT('%',@sKeyword,'%'))";
sqlQuery += " OR ([ngayMua] LIKE CONCAT('%',@sKeyword,'%'))";
sqlQuery += " OR ([giaThanh] LIKE CONCAT('%',@sKeyword,'%'))";
sqlQuery += " OR ([baoHanh] LIKE CONCAT('%',@sKeyword,'%'))";
sqlQuery += " OR ([ngayHetHanBaoHanh] LIKE CONCAT('%',@sKeyword,'%'))";
sqlQuery += " OR ([hangSanXuat] LIKE CONCAT('%',@sKeyword,'%'))";
conString.ConString constring = new conString.ConString(); //this will hide the database info ... sort of
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
cmd.Parameters.AddWithValue("@sKeyword", keyword);
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
// this will query your database and return the result to your datatable
da.Fill(result);
con.Close();
}
}
}
catch (SqlException ex)
{
}
return(result);
}
public DataTable searchDataintimespan(DateTime from, DateTime to)
{
result.Clear();
sqlQuery = " select * from [Attendance]";
sqlQuery += " WHERE thoigian BETWEEN '" + from + "' AND '" + to + "';";
conString.ConString constring = new conString.ConString(); //this will hide the database info ... sort of
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
// this will query your database and return the result to your datatable
da.Fill(result);
con.Close();
}
}
}
catch (SqlException ex)
{
}
return(result);
}
public DataTable getMemberofMonth(DateTime fromDate, DateTime toDate)
{
sqlQuery = "SELECT bucketName AS thang," +
" (SELECT COUNT(*) FROM Member WHERE ngayThamGia <= bucketLastDay)" +
"- (SELECT COUNT(*) FROM Member WHERE ngayKetThuc<bucketFirstDay) as total" +
" FROM monthBucket WHERE bucketLastDay >= '" + fromDate + "' AND bucketFirstDay <= '" + toDate + "'" +
" ORDER BY bucketFirstDay";
conString.ConString constring = new conString.ConString(); //this will hide the database info ... sort of
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
// this will query your database and return the result to your datatable
da.Fill(result);
con.Close();
}
}
}
catch (SqlException ex)
{
}
return(result);
}
public int getPermission(string UserName)
{
sqlQuery = "Select LogInData.permissionLevel from LogInData where LogInData.username = '******'";
conString.ConString constring = new conString.ConString(); //this will hide the database info ... sort of
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
//con.Open();
//SqlDataAdapter da = new SqlDataAdapter(cmd);
//// this will query your database and return the result to your datatable
//con.Close();
con.Open();
permission = (int)cmd.ExecuteScalar();
con.Close();
}
}
}
catch (SqlException ex)
{
//con.Close();
}
return(permission);
}
public void Deleteinactive()
{
sqlQuery = "Delete from Member where ma in (Select ma from Member Where dateadd(yy,-2,getdate()) > ngayKetThuc)";
conString.ConString constring = new conString.ConString(); //this will hide the database info ... sort of
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
con.Close();
}
}
}
catch (SqlException ex)
{
}
}
public void Insert(string maNV, int level, string UserName, string pass)
{
sqlQuery = "INSERT INTO LogInData (maNV, permissionLevel, username, passW) VALUES ('" + maNV + "'," + level + ",'" + UserName + "','" + pass + "')";
conString.ConString constring = new conString.ConString();
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
catch (SqlException ex)
{
}
}
public void deleteData(Class.trangThietBi data)
{
sqlQuery = "delete from TrangThietBi where ma ='" + data.ma + "'";
conString.ConString constring = new conString.ConString(); //this will hide the database info ... sort of
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
catch (SqlException ex)
{
}
}
public void Insert(AttendanceClass attendanceClass)
{
sqlQuery = "insert into Attendance (ma,thoigian, alias) " +
" values ('" + attendanceClass.ma + "','" + attendanceClass.attendanceTime + "', N'" + attendanceClass.alias + "')";
conString.ConString constring = new conString.ConString(); //this will hide the database info ... sort of
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
catch (SqlException ex)
{
}
}
public void MakememberInactive()
{
sqlQuery = "Update Member set ngayKetThuc = getdate()" +
"where ma in (select ma from Attendance group by ma having dateadd(yy,-1,getdate()) > max(thoigian))";
conString.ConString constring = new conString.ConString(); //this will hide the database info ... sort of
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
con.Close();
}
}
}
catch (SqlException ex)
{
}
}
public void insertData(Class.trangThietBi data)
{
sqlQuery = "insert into TrangThietBi (ten, ma, tinhTrang, ngayMua, giaThanh, baoHanh, ngayHetHanBaoHanh, hangSanXuat) values (N'" +
data.ten + "','" + data.ma + "',N'" + data.tinhTrang + "','" +
data.ngayMua + "'," + data.giaThanh + "," + data.baoHanh + ",'" + data.ngayHetHanBaoHanh + "',N'" + data.hangSanXuat + "') ";
conString.ConString constring = new conString.ConString();
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
catch (SqlException ex)
{
}
}
public void updateData(Class.trangThietBi data)
{
sqlQuery = "update TrangThietBi set ten = N'" + data.ten + "', tinhTrang = N'" +
data.tinhTrang + "', ngayMua= '" + data.ngayMua + "', giaThanh=" + data.giaThanh + ", baoHanh=" + data.baoHanh +
", ngayHetHanBaoHanh= '" + data.ngayHetHanBaoHanh + "', hangSanXuat= '" + data.hangSanXuat + "' where ma ='" + data.ma + "'";
conString.ConString constring = new conString.ConString();
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
catch (SqlException ex)
{
}
}
public string GetIDfromName(string ten)
{
sqlQuery = "select ma from Member where ten = N'" + ten + "'";
string ma = "";
conString.ConString constring = new conString.ConString(); //this will hide the database info ... sort of
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
ma = cmd.ExecuteScalar().ToString();
con.Dispose();
}
}
}
catch (SqlException ex)
{
}
return(ma);
}
public void Insert(string task)
{
valiballecommon valiballecommon = valiballecommon.GetStorage();
sqlQuery = "insert into Log (username, thoigian, task) values ('" +
valiballecommon.UserName + "',N'" + DateTime.Now + "', N'" + task + "')";
conString.ConString constring = new conString.ConString();
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
catch (SqlException ex)
{
}
}
public DataTable AddPayment(string maKH, string maGoiTap, DateTime dateOfPay, int PaymentPeriod, int price)
{
sqlQuery = "insert into MonthlyIncome (maKH, maGoiTap, dateOfPay, PaymentPeriod, price) " +
" values ('" + maKH + "','" + maGoiTap + "','" + dateOfPay + "'," + PaymentPeriod + "," + price + ")";
conString.ConString constring = new conString.ConString(); //this will hide the database info ... sort of
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
}
}
catch (SqlException ex)
{
}
return(result);
}
public DataTable DeletePayment(string maKH, DateTime dateOfPay)
{
sqlQuery = "Delete from MonthlyIncome where maKH = '" + maKH + "' AND " +
"year(dateOfPay) = '" + dateOfPay.Year + "' and month(dateOfPay) = '" + dateOfPay.Month + "' and day(dateOfPay) = '" + dateOfPay.Day + "'";
conString.ConString constring = new conString.ConString(); //this will hide the database info ... sort of
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
}
}
catch (SqlException ex)
{
}
return(result);
}
public bool Insert(feedBack feedback)
{
sqlQuery = "insert into Feedback (maKH,thoigian,feedback)" +
" values('" + feedback.maHV + "', '" + feedback.date + "', N'" + feedback.feedback + "')";
conString.ConString constring = new conString.ConString(); //this will hide the database info ... sort of
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
return(true);
}
}
}
catch (SqlException ex)
{
return(false);
}
}
public DataTable getMemberListCode()
{
sqlQuery = "Select Trim(Member.ma) as list" +
" From Member ";
conString.ConString constring = new conString.ConString(); //this will hide the database info ... sort of
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
// this will query your database and return the result to your datatable
da.Fill(result);
con.Close();
}
}
}
catch (SqlException ex)
{
}
return(result);
}
public DataTable getMemberDetailInfo(string ma)
{
sqlQuery = "Select ma, ten from Member " +
" where ma = '" + ma + "' ";
conString.ConString constring = new conString.ConString(); //this will hide the database info ... sort of
try
{
using (var con = new SqlConnection(constring.initString()))
{
using (var cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
// this will query your database and return the result to your datatable
da.Fill(result);
con.Close();
}
}
}
catch (SqlException ex)
{
}
return(result);
}