public bool ThemDanhMuc(int id, string tendm, ref string err)
{
DBMain db = new DBMain();
string query = "insert into DANHMUC values (N'" + tendm + "', '0')";
return(db.MyExecuteNonQuery(query, CommandType.Text, ref err));
}
public bool CapNhatDanhMuc(int id, string tendm, ref string err)
{
DBMain db = new DBMain();
string query = "update DANHMUC set TenDanhMuc = N'" + tendm + "' where IDDanhMuc = '" + id + "'";
return(db.MyExecuteNonQuery(query, CommandType.Text, ref err));
}
public bool DoiMatKhau(string oldPass, string newPass, string idNhanVien)
{
//QuanLyNhaHangProjectEntities ql = new QuanLyNhaHangProjectEntities();
//var query = ql.TAIKHOANs.Where(x => x.IDNhanVien == idNhanVien).SingleOrDefault();
//if(query != null)
//{
// if (query.MatKhau == oldPass)
// {
// query.MatKhau = newPass;
// ql.SaveChanges();
// }
// else return false;
//}
DBMain db = new DBMain();
string f = "";
string query = "Select * from TAIKHOAN Where IDNhanVien = '" + idNhanVien + "' And MatKhau = '" + oldPass + "'";
if (db.ExecuteQueryDataSet(query, CommandType.Text).Rows.Count >= 1)
{
query = "Update TAIKHOAN Set MatKhau = '" + newPass + "' Where IDNhanVien = '" + idNhanVien + "' And MatKhau = '" + oldPass + "'";
return(db.MyExecuteNonQuery(query, CommandType.Text, ref f));
}
else
{
return(false);
}
}
private void btnDelLister_Click(object sender, RoutedEventArgs e)
{
if (dgListers.SelectedIndex != -1)
{
if (!BaseDataBase.CurrentUser.CanDelete)
{
MyMessageBox.Show("ليس لديك صلاحيات للحذف");
}
else
{
Lister l = Lister.GetListerByID((int)(dgListers.Items[dgListers.SelectedIndex] as DataRowView)[0]);
if (int.Parse(BaseDataBase._Scalar("select dbo.GetGroupCountByListerID(" + l.ListerID + ")")) > 0)
{
MyMessageBox.Show("لا يمكن حذف المقيّم \nبسبب وجود بيانات تقييم سابقة له ضمن فرق التقييم");
}
else if (int.Parse(BaseDataBase._Scalar("select COUNT(ID) from Trained where TrainedType = 2 and TrainedID = " + l.ListerID)) > 0)
{
MyMessageBox.Show("لا يمكن حذف المقيّم \nبسبب وجود بيانات تدريب سابقة له");
}
else if (DBMain.DeleteData(l))
{
dgListers.ItemsSource = Lister.GetAllListersTable;
MyMessage.DeleteMessage();
}
}
}
}
private void btnDelete_Click(object sender, RoutedEventArgs e)
{
var x = this.DataContext as AdminEvaluation;
if (x.FamilyID == null && x.OrphanID == null)
{
MyMessageBox.Show("لم يتم إدخال بيانات العائلة او اليتيم");
return;
}
if (x.ID != null)
{
if (!BaseDataBase.CurrentUser.CanDelete)
{
MyMessageBox.Show("لا يوجد لديك صلاحيات للحذف");
}
else if (DBMain.DeleteData(x))
{
MyMessage.UpdateMessage();
if (FamilyID != null)
{
FamilyID = FamilyID;
}
else
{
OrphanID = OrphanID;
}
}
}
}
private void btnSave_Click(object sender, RoutedEventArgs e)
{
var x = this.DataContext as FamilyProperty;
if (x.IsValidate())
{
if (x.FamilyID == null)
{
MyMessageBox.Show("لم يتم إدخال بيانات العائلة");
return;
}
if (x.FamilyPropertyID != null)
{
if (DBMain.UpdateData(x))
{
MyMessage.UpdateMessage();
UpdateCombo();
}
}
else
{
if (DBMain.InsertData(x))
{
MyMessage.InsertMessage();
FamilyProperties.Add(x);
TotalCount = FamilyProperties.Count;
Place = TotalCount;
UpdateCombo();
}
}
}
}
private void btnSave_Click(object sender, RoutedEventArgs e)
{
Family f = this.DataContext as Family;
if (f != null)
{
if (chk.IsChecked == false)
{
//f.CancelDate = null;
//f.CancelReason = "";
DBMain.UpdateCancelData(f);
DialogResult = true;
}
else if (f.CancelDate == null)
{
MessageBox.Show("يجب إدخال تاريخ الإلغاء");
}
/*else if(chkAcquittance.IsChecked == false && f.AcquittanceDate == null)
* {
* MessageBox.Show("يجب إدخال تاريخ منح براءة الذمة");
* }*/
else if (OldIsCanceled != f.IsCanceled && f.CancelReason == OldReason)
{
MyMessageBox.Show("يجب إدخال سبب الالغاء او التفعيل");
}
else
{
DBMain.UpdateCancelData(f);
DialogResult = true;
}
}
}
public string LayCongViecById(int id)
{
DBMain db = new DBMain();
string query = "select TenCongViec from CONGVIEC where IDCongViec = '" + id + "'";
return(db.ExecuteQueryDataSet(query, CommandType.Text).Rows[0]["TenCongViec"].ToString());
}
public DataSet SearchCutomer(string CustomerName, string IdentityCard)
{
DBMain db = new DBMain();
string sql = "Exec sp_SearchCustomer ";
if (CustomerName != null)
{
sql += " " + CustomerName;
if (IdentityCard != null)
{
sql += "," + IdentityCard;
}
}
else
{
if (IdentityCard != null && IdentityCard != "")
{
sql += "null," + IdentityCard;
}
else
{
sql = "select * from Customers";
}
}
return(db.ExecuteQueryDataSet(sql, CommandType.Text));
}
public DataSet ListOfInvoicesHasPaid()
{
DBMain db = new DBMain();
string sql = "Select * from vi_InvoicesHasPaid";
return(db.ExecuteQueryDataSet(sql, CommandType.Text));
}
public DataSet Chart()
{
DBMain db = new DBMain();
string sql = "Exec sp_Revenue5Month ";
return(db.ExecuteQueryDataSet(sql, CommandType.Text));
}
public bool XoaDanhMuc(int id, ref string err)
{
DBMain db = new DBMain();
string query = "delete from DANHMUC where IDDanhMuc = '" + id + "'";
return(db.MyExecuteNonQuery(query, CommandType.Text, ref err));
}
private void btnSave_Click(object sender, RoutedEventArgs e)
{
var x = this.DataContext as House;
if (x.IsValidate())
{
if (x.FamilyID == null && x.OrphanID == null)
{
MyMessageBox.Show("لم يتم إدخال بيانات العائلة او اليتيم");
return;
}
if (x.HouseID != null)
{
if (!BaseDataBase.CurrentUser.CanUpdate)
{
MyMessageBox.Show("لا يوجد لديك صلاحيات للتعديل");
}
else if (DBMain.UpdateData(x))
{
MyMessage.UpdateMessage();
}
}
else
{
if (DBMain.InsertData(x))
{
MyMessage.InsertMessage();
Houses.Add(x);
TotalCount = Houses.Count;
Place = TotalCount;
}
}
}
}
private void txtTaiKhoanDK_TextChanged(object sender, EventArgs e)
{
db = new DBMain();
//string LayMaQL = @"EXEC LayMaQuanLy '" + btn_userDK.Text.ToString().TrimEnd() + "'";
//DataTable dtQL = DBMain.getDataTable(LayMaQL);
//string LayMaNV = @"EXEC LayMaNhanVien '" + btn_userDK.Text.ToString().TrimEnd() + "'";
//DataTable dtNV = DBMain.getDataTable(LayMaNV);
SqlCommand sqlCmd = new SqlCommand(@"EXEC LayMaQuanLy '" + btn_userDK.Text.ToString().TrimEnd() + "'", db.conn)
{
CommandType = CommandType.Text
};
string laymaql = (db.FirstRowQuery(sqlCmd, ref err)).ToString();
sqlCmd = new SqlCommand(@"EXEC LayMaNhanVien '" + btn_userDK.Text.ToString().TrimEnd() + "'", db.conn)
{
CommandType = CommandType.Text
};
string laymanv = (db.FirstRowQuery(sqlCmd, ref err)).ToString();
if (laymaql == "" && laymanv == "")
{
positionDK.Text = "--------";
}
else if (laymaql.Length > 0)
{
positionDK.Text = "Quản Lý";
}
else if (laymanv.Length > 0)
{
positionDK.Text = "Nhân Viên";
}
}
private void btnSave_Click(object sender, RoutedEventArgs e)
{
var x = this.DataContext as FamilyPerson;
if (x.IsValidate())
{
if (x.FamilyID == null)
{
MyMessageBox.Show("لم يتم إدخال بيانات العائلة");
return;
}
if (x.FamilyPersonID.HasValue)
{
if (BaseDataBase.CurrentUser.CanUpdate && DBMain.UpdateData(x))
{
MyMessage.UpdateMessage();
}
}
else
{
if (DBMain.InsertData(x))
{
MyMessage.InsertMessage();
FamilyPersons.Add(x);
TotalCount = FamilyPersons.Count;
Place = TotalCount;
}
}
}
}
public List <MONAN> LayDanhMucMonAn(int tab)
{
/* (EntityModels)
* var queryMA = from nu in ql.MONANs
* where nu.IDDanhMuc == tab && nu.TrangThai == false
* select nu;
*/
DBMain db = new DBMain();
List <MONAN> lstNuoc = new List <MONAN>();
string query = "Select * From MONAN Where IDDanhMuc = '"
+ tab + "' And TrangThai = 0";
var kq = db.ExecuteQueryDataSet(query, CommandType.Text);
foreach (DataRow item in kq.Rows)
{
MONAN nu = new MONAN();
nu.IDMonAn = item["IDMonAn"].ToString();
nu.IDDanhMuc = (int)item["IDDanhMuc"];
nu.GiaTien = (int)item["GiaTien"];
nu.TenMon = item["TenMon"].ToString();
nu.HinhMA = item["HinhMA"].ToString();
lstNuoc.Add(nu);
}
return(lstNuoc);
}
private void DeleteFamilyPerson_CommandExecuted(object sender, ExecutedRoutedEventArgs e)
{
if (!BaseDataBase.CurrentUser.CanUpdateFamily)
{
MyMessageBox.Show("ليس لديك صلاحية حذف");
return;
}
FamilyPerson fp = dgChild.SelectedCells[0].Item as FamilyPerson;
if (fp != null)
{
if (MyMessageBox.Show("هل تريد حذف السجل؟", MessageBoxButton.YesNo) == MessageBoxResult.Yes)
{
var fps = dgChild.ItemsSource as List <FamilyPerson>;
if (fp != null)
{
dgChild.ItemsSource = null;
if (fp.FamilyPersonID.HasValue)
{
DBMain.DeleteData(fp);
}
fps.Remove(fp);
dgChild.ItemsSource = fps;
}
}
}
}
public void ThemMonAn(string tenMon, int danhMucMon, int giaMon, string pathHinh)
{
//Entity
// QuanLyNhaHangProjectEntities ql = new QuanLyNhaHangProjectEntities();
// var m = ql.MONANs.OrderByDescending(x => x.IDMonAn).FirstOrDefault();
// int idMonNuoc = int.Parse(m.IDMonAn.Split('A')[1]);
// MONAN ma = new MONAN();
// ma.IDMonAn = idMonNuoc >= 100 ? "MA" + (idMonNuoc + 1) : "MA0" + (idMonNuoc + 1);
// ma.IDDanhMuc = danhMucMon;
// ma.TenMon = tenMon;
// ma.GiaTien = giaMon;
// ma.HinhMA = pathHinh;
// ql.MONANs.Add(ma);
// ql.SaveChanges();
DBMain db = new DBMain();
string ID, f = "";
int idMoiNhat = this.LayIdMonNuocMoiNhat() + 1;
ID = idMoiNhat >= 100 ? "MA" + idMoiNhat : "MA0" + idMoiNhat;
System.Windows.Forms.MessageBox.Show(ID);
string query = "Insert into MONAN (IDMonAn, IDDanhMuc, TenMon, GiaTien, HinhMA, TrangThai)" +
" Values ('" + ID + "', " + danhMucMon.ToString() + ", N'" + tenMon + "', " + giaMon.ToString() + ", '" + pathHinh + "',0);";
db.MyExecuteNonQuery(query, CommandType.Text, ref f);
}
public void CapNhatMon_TenMon(string tenMon, string tenMonMoi, int giaMon, string pathHinh)
{
//Entity
// QuanLyNhaHangProjectEntities ql = new QuanLyNhaHangProjectEntities();
// var m = (from nu in ql.MONANs
// where nu.TenMon == tenMon
// select nu).SingleOrDefault();
// m.TenMon = tenMonMoi;
// m.GiaTien = giaMon;
// if (pathHinh != string.Empty)
// {
// m.HinhMA = pathHinh;
// }
// ql.SaveChanges();
DBMain db = new DBMain();
string query;
if (pathHinh != string.Empty)
{
query = "Update MONAN Set TenMon = N'" + tenMonMoi + "', GiaTien = " + giaMon.ToString()
+ ", HinhMA = '" + pathHinh + "' Where TenMon = N'" + tenMon + "'";
}
else
{
query = "Update MONAN Set TenMon = N'" + tenMonMoi + "', GiaTien = " + giaMon.ToString()
+ " Where TenMon = N'" + tenMon + "'";
}
string f = "";
db.MyExecuteNonQuery(query, CommandType.Text, ref f);
}
public bool XoaNhanVien(string IDNhanVien, ref string err)
{
DBMain db = new DBMain();
string query = "Delete from NHANVIEN where IDNhanVien = '" + IDNhanVien + "'";
return(db.MyExecuteNonQuery(query, CommandType.Text, ref err));
}
public HOADON LayHoaDonID(string ID) //Không sử dụng
{
/* (EntityModels)
* var hoadon = (ql.HOADONs.Where(x => x.IDHoaDon == ID).Select(x => x)).SingleOrDefault();
*/
DBMain db = new DBMain();
string query = "Select * " +
"From HOADON " +
"Where IDHoaDon = '" + ID + "'";
var kq = db.ExecuteQueryDataSet(query, CommandType.Text);
HOADON hd = new HOADON();
if (query != null)
{
hd.IDHoaDon = kq.Rows[0]["IDHoaDon"].ToString();
hd.IDBan = (int)kq.Rows[0]["IDBan"];
hd.IDNhanVien = kq.Rows[0]["IDNhanVien"].ToString();
hd.KhuyenMai = kq.Rows[0]["KhuyenMai"].ToString();
hd.NgayLap = (DateTime)kq.Rows[0]["NgayLap"];
if (kq.Rows[0]["NgayThanhToan"].ToString() != "")
{
hd.NgayThanhToan = (DateTime)kq.Rows[0]["NgayThanhToan"];
}
else
{
hd.NgayThanhToan = null;
}
hd.TongTien = (int)kq.Rows[0]["TongTien"];
}
return(hd);
}
public NHANVIEN LayNhanVienByUserName(string username)
{
/*
* var query = ql.TAIKHOANs.Where(x => x.TaiKhoan1 == username)
* .Join(ql.NHANVIENs, x => x.IDNhanVien, y => y.IDNhanVien, (x, y) => y)
* .SingleOrDefault();
*/
DBMain db = new DBMain();
string query = "Select NHANVIEN.IDNhanVien, Ho, Ten, DiaChi, NgaySinh, Email, SDT, IDCongViec, HinhNV " +
"From NHANVIEN join TAIKHOAN on NHANVIEN.IDNhanVien = TAIKHOAN.IDNhanVien " +
"Where TAIKHOAN.TaiKhoan = '" + username + "'";
var kq = db.ExecuteQueryDataSet(query, CommandType.Text);
NHANVIEN nv = new NHANVIEN();
if (kq != null)
{
nv.IDNhanVien = kq.Rows[0]["IDNhanVien"].ToString();
nv.Ho = kq.Rows[0]["Ho"].ToString();
nv.Ten = kq.Rows[0]["Ten"].ToString();
nv.DiaChi = kq.Rows[0]["DiaChi"].ToString();
nv.NgaySinh = (DateTime)kq.Rows[0]["NgaySinh"];
nv.Email = kq.Rows[0]["Email"].ToString();
nv.SDT = kq.Rows[0]["SDT"].ToString();
nv.IDCongViec = (int)kq.Rows[0]["IDCongViec"];
nv.HinhNV = kq.Rows[0]["HinhNV"].ToString();
}
return(nv);
}
public bool XoaBan(int idBan, ref string err)
{
DBMain db = new DBMain();
string query = "Delete From BAN Where IDBan = '" + idBan + "'";
return(db.MyExecuteNonQuery(query, CommandType.Text, ref err));
}
public List <BAN> LayBan()
{
/* (EntityModels)
* var kq = from tt in qlCF.BANs
* where tt.TrangThaiXoa == false
* select new { tt.IDBan, tt.TenBan, tt.TrangThai, tt.SucChua };
*/
List <BAN> lstBan = new List <BAN>();
DBMain db = new DBMain();
string query = "Select IDBan, TenBan, TrangThai, SucChua " +
"From BAN " +
"Where TrangThaiXoa = 0";
var kq = db.ExecuteQueryDataSet(query, CommandType.Text);
foreach (DataRow item in kq.Rows)
{
BAN ban = new BAN();
ban.IDBan = (int)item["IDBan"];
ban.TenBan = item["TenBan"].ToString();
ban.TrangThai = (bool)item["TrangThai"];
ban.SucChua = (int)item["SucChua"];
lstBan.Add(ban);
}
return(lstBan);
}
//public bool ThemBan(int idBan, string TenBan, int SucChua)
//{
// QuanLyNhaHangProjectEntities ql = new QuanLyNhaHangProjectEntities();
// var id = ql.BANs.OrderByDescending(x => x.IDBan).FirstOrDefault();
// BAN ban = new BAN();
// ban.TenBan = TenBan;
// ban.SucChua = SucChua;
// ql.BANs.Add(ban);
// ql.SaveChanges();
// return true;
//}
//public bool CapNhatBan(int idBan, string TenBan, int SucChua)
//{
// QuanLyNhaHangProjectEntities ql = new QuanLyNhaHangProjectEntities();
// var ban = (from b in ql.BANs
// where b.IDBan == idBan
// select b).SingleOrDefault();
// if (ban == null)
// {
// return false;
// }
// ban.TenBan = TenBan;
// ban.SucChua = SucChua;
// ql.SaveChanges();
// return true;
//}
public bool ThemBan(int idBan, string TenBan, int SucChua, ref string err)
{
DBMain db = new DBMain();
string query = "Insert into BAN values ('" + TenBan + "', '0', '" + SucChua + "', '0')";
return(db.MyExecuteNonQuery(query, CommandType.Text, ref err));
}
public DataTable LayChiTietHoaDon(string IDHoaDon)
{
/* (EntityModels)
* var queryCTHoaDon = from cthd in qlCF.CHITIETHOADONs
* join ma in qlCF.MONANs on cthd.IDMonAn equals ma.IDMonAn
* where cthd.IDHoaDon == IDHoaDon
* select new { cthd.IDHoaDon, ma.TenMon, ma.GiaTien, cthd.SoLuong, ThanhTien = ma.GiaTien * cthd.SoLuong };
*/
DBMain db = new DBMain();
string query = "Select CHITIETHOADON.IDHoaDon, TenMon, GiaTien, SoLuong, (GiaTien * SoLuong) as ThanhTien " +
"From CHITIETHOADON join MONAN on CHITIETHOADON.IDMonAN = MONAN.IDMonAn " +
"Where CHITIETHOADON.IDHoaDon = '" + IDHoaDon + "'";
var kq = db.ExecuteQueryDataSet(query, CommandType.Text);
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[]
{
new DataColumn("IDHoaDon"),
new DataColumn("TenMon"),
new DataColumn("GiaTien"),
new DataColumn("SoLuong"),
new DataColumn("ThanhTien"),
});;
foreach (DataRow i in kq.Rows)
{
dt.Rows.Add(i["IDHoaDon"].ToString(), i["TenMon"].ToString(), (int)i["GiaTien"], (int)i["SoLuong"], (int)i["ThanhTien"]);
}
return(dt);
}
public async void GetData()
{
await DBMain.UpdateAllPregnant();
List <string> lst1, lst2, lst3, lst4, lst5, lst6, lst7, lst8;
lst1 = lst2 = lst3 = lst4 = lst5 = lst6 = lst7 = lst8 = null;
await Task.Run(() =>
{
lst1 = BaseDataBase.GetAllStringsWithAll("select distinct job from (select Job from Parent Union select Job from FamilyPerson) x where ISNULL(Job,'') <> '' order by Job");
lst2 = BaseDataBase.GetAllStringsWithAll("select distinct HealthStatus from (select HealthStatus from Parent Union select HealthStatus from FamilyPerson) x WHERE ISNULL(HealthStatus,'') <> '' order by HealthStatus");
lst3 = BaseDataBase.GetAllStringsWithAll("select distinct StudyStatus from (select StudyStatus from Parent Union select StudyStatus from FamilyPerson) x where Isnull(StudyStatus,'') <> '' order by StudyStatus");
lst4 = BaseDataBase.GetAllStringsWithAll("select distinct HouseSection from House where ISNULL(HouseSection,'') <> '' order by HouseSection");
lst5 = BaseDataBase.GetAllStringsWithAll("select distinct HouseStreet from House where isnull(HouseStreet,'') <> '' order by HouseStreet");
lst6 = BaseDataBase.GetAllStringsWithAll("select distinct OldAddress from House x where ISNULL(OldAddress,'') <> '' order by OldAddress");
lst7 = BaseDataBase.GetAllStringsWithAll("select distinct impededType from (select impededType from Parent Union select impededType from FamilyPerson) x where ISNULL(impededType,'') <> '' order by impededType");
lst8 = BaseDataBase.GetAllStringsWithAll("select distinct RelationShip from (select 'أب' RelationShip Union select 'أم' Union (select RelationShip from FamilyPerson)) x where ISNULL(RelationShip,'') <> '' order by RelationShip");
});
cmboJob.ItemsSource = lst1;
cmboHealthStatus.ItemsSource = lst2;
cmboStudyStatus.ItemsSource = lst3;
cmboHouseSection.ItemsSource = lst4;
cmboHouseStreet.ItemsSource = lst5;
cmboOldAddress.ItemsSource = lst6;
cmboImpedeType.ItemsSource = lst7;
cmboRelationShip.ItemsSource = lst8;
}
public bool ThemNhanVien(string IDNhanVien, string Ho, int IDCongViec, string Ten, string Email, DateTime NgaySinh, string DiaChi, string DienThoai, string HinhNV, ref string err)
{
DBMain db = new DBMain();
string query = "insert into NHANVIEN values (N'" + IDNhanVien + "', N'" + Ho + "', N'" + Ten + "', '" + NgaySinh + "', '" +
DienThoai + "', N'" + DiaChi + "', N'" + Email + "', N'" + HinhNV + "', '" + IDCongViec + "', '0')";
return(db.MyExecuteNonQuery(query, CommandType.Text, ref err));
}
public bool CapNhatBan(int idBan, string TenBan, int SucChua, ref string err)
{
DBMain db = new DBMain();
string query = "Update BAN set TenBan = '" + TenBan + "', SucChua = '" + SucChua + "' " +
"where IDBan = '" + idBan + "'";
return(db.MyExecuteNonQuery(query, CommandType.Text, ref err));
}
public DataTable search(string name)
{
string str = "select a.masach, a.tensach, ls.maloaisach, a.soluong, a.make from sach as a, loaisach as ls where ls.tenloaisach = N'" + name + "' and ls.maloaisach = a.maloaisach";
DBMain dB = new DBMain();
DataTable table = dB.ExecuteQuery(str);
return(table);
}
