public static DocGia_DTO layTTDocGia(string maDocGia)
{
SqlConnection conn = SQLConn.connect();
DocGia_DTO docGia = new DocGia_DTO();
conn.Open();
docGia.MaDocGia = maDocGia;
SqlCommand cmd = new SqlCommand("Select TenDocGia from DocGia where MaDocGia = '" + maDocGia + "'", conn);
docGia.TenDocGia = cmd.ExecuteScalar().ToString();
SqlCommand cmd1 = new SqlCommand("Select GioiTinh from DocGia where MaDocGia = '" + maDocGia + "'", conn);
docGia.GioiTinh = cmd1.ExecuteScalar().ToString();
SqlCommand cmd2 = new SqlCommand("Select NgaySinh from DocGia where MaDocGia = '" + maDocGia + "'", conn);
string chuoiNgaySinh = cmd2.ExecuteScalar().ToString();
DateTime ngaySinh = new DateTime();
DateTime.TryParse(chuoiNgaySinh, out ngaySinh);
docGia.NgaySinh = ngaySinh;
SqlCommand cmd3 = new SqlCommand("Select DiaChi from DocGia where MaDocGia = '" + maDocGia + "'", conn);
docGia.DiaChi = cmd3.ExecuteScalar().ToString();
SqlCommand cmd4 = new SqlCommand("Select SoCMT from DocGia where MaDocGia = '" + maDocGia + "'", conn);
docGia.SoCMT = cmd4.ExecuteScalar().ToString();
SqlCommand cmd5 = new SqlCommand("Select MaLoaiDocGia from DocGia where MaDocGia = '" + maDocGia + "'", conn);
docGia.MaLoaiDocGia = cmd5.ExecuteScalar().ToString();
conn.Close();
return(docGia);
}
public bool Insert(Account_DTO dn, DocGia_DTO dk)
{
bool them = true;
if (GetData("select* from ACCOUNT where MaDocGia = '" + dn.MaDocGia + "'").Rows.Count > 0)
{
them = false;
}
else if (GetData("select* from ACCOUNT where TenDangNhap = '" + dn.TenDangNhap + "'").Rows.Count > 0)
{
them = false;
}
else
{
string sql = "INSERT INTO ACCOUNT(MaDocGia, TenDangNhap, MatKhau, Quyen)VALUES (";
sql += "N'" + dn.MaDocGia + "',N'" + dn.TenDangNhap + "','" + dn.MatKhau + "','" + 0 + "')";
Excute(sql);
string Sql = "INSERT INTO DOCGIA (MaDocGia, TenDangNhap, HoTen, DiaChi,GioiTinh,NamSinh) VALUES (";
Sql += "N'" + dn.MaDocGia + "',N'" + dn.TenDangNhap + "',N'" + dk.HoTen + "',N'" + dk.DiaChi + "',N'" + dk.GioiTinh + "','" + dk.NamSinh + "')";
Excute(Sql);
if (KThoa(dn.MatKhau) == false)
{
them = false;
}
}
return(them);
}
private void btnCN_Click(object sender, EventArgs e)
{
DocGia_DTO dg = new DocGia_DTO();
dg.HoTen = txtHT.Text;
dg.NamSinh = dtNS.Value;
if (rbtnNam.Checked == true)
{
dg.GioiTinh = "Nam";
}
else if (rbtnNu.Checked == true)
{
dg.GioiTinh = "Nữ";
}
dg.DiaChi = txtDC.Text;
if (!dgBUS.SuaDG1(dg, tdn))
{
MessageBox.Show("Cập nhật thông tin của bạn không thành công!!!");
}
else
{
MessageBox.Show("Cập nhật thành công");
ThayDoiTT_Load(sender, e);
}
}
public bool UpdateDocGia(DocGia_DTO pm)
{
try
{
_conn.Open();
_conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = _conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "INSERTPHIEUMUON";
cmd.Parameters.AddWithValue("MaDocGia", pm.MADOCGIA);
cmd.Parameters.AddWithValue("HoTen", pm.HOTEN);
cmd.Parameters.AddWithValue("GioiTinh", pm.GIOITINH);
cmd.Parameters.AddWithValue("NamSinh", pm.NAMSINH);
cmd.Parameters.AddWithValue("DiaChi", pm.DIACHI);
cmd.Parameters.AddWithValue("SoDienThoai", pm.SODIENTHOAI);
cmd.Parameters.AddWithValue("Email", pm.EMAIL);
if (cmd.ExecuteNonQuery() > 0)
{
return(true);
}
}
finally
{
_conn.Close();
}
return(false);
}
private void barBtnDoiMatKhau_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
//Sửa
try
{
txtDienThoai.ReadOnly = false;
txtDiaChi.ReadOnly = false;
txtEmail.ReadOnly = false;
txtGTinh.ReadOnly = false;
DocGia_DTO dg = new DocGia_DTO();
dg.MaDocGia = int.Parse(MaDGia);
dg.HoTen = txtTen.Text;
dg.GioiTinh = 1;
if (txtGTinh.Text == "Nam")
{
dg.GioiTinh = 0;
}
dg.NgaySinh = dtNgaySinh.Value;
dg.Email = txtEmail.Text;
dg.DienThoai = txtDienThoai.Text;
dg.DiaChi = txtDiaChi.Text;
DocGia_DAO.Sua(dg);
grdDocGia.DataSource = DocGia_DAO.LoadDuLieu();
}
catch (Exception ex)
{
MessageBox.Show("Chưa chọn độc giả cần chỉnh sửa", "THÔNG BÁO", MessageBoxButtons.OK);
}
}
public bool Update(DocGia_DTO docgia)
{
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd;
string sqlQuyery1 = " select count(*) from DocGia WHERE MaDocGia = N'" + docgia.MaDocGia + "'";
cmd = new SqlCommand(sqlQuyery1, con);
var i = cmd.ExecuteScalar();
if (int.Parse(i.ToString()) == 0)
{
return(false);
}
else
{
string sqlQuery = @"Update DocGia set TenDocGia = N'" + docgia.HoTen + "',GioiTinh = N'" + docgia.GioiTinh + "', NgaySinh = N'" + docgia.NgaySinh + "', DiaChi = N'" + docgia.DiaChi + "', Email = N'" + docgia.Email + "' WHERE MaDocGia = N'" + docgia.MaDocGia + "' ";
cmd = new SqlCommand(sqlQuery, con);
cmd.ExecuteNonQuery();
con.Close();
return(true);
}
}
catch
{
return(false);
}
}
public bool KiemTra(DocGia_DTO docgia)
{
if (docgia.MaDocGia == "" || docgia.HoTen == "" || docgia.GioiTinh == "" || docgia.NgaySinh == "" || docgia.DiaChi == "" || docgia.Email == "" || docgia.HoTen.Length < 0)
{
return(false);
}
return(true);
}
private void btnOkThem_Click(object sender, EventArgs e)
{
if (DateTime.Compare(dtpNgaySinh.Value, DateTime.Today) < 0)
{
if ((txtMaDG.Text != "") && (txtTenDG.Text != "") && (txtDiaChi.Text != "") && (txtSoCMT.Text != "") && (txtMK.Text != ""))
{
try
{
DocGia_DTO docGia = new DocGia_DTO(txtMaDG.Text, txtTenDG.Text, cboGioiTinh.SelectedItem.ToString(), dtpNgaySinh.Value, txtDiaChi.Text, txtSoCMT.Text, (string)cboLoaiDG.SelectedValue, txtMK.Text);
BUS_OBJ.themDG(docGia);
if (cbTaoThe.Checked == true)
{
autoThemThe();
}
autoThemUser();/////
HienThiDuLieu();
}
catch (Exception)
{
MetroFramework.MetroMessageBox.Show(this, "Mã độc giả bị trùng. \nKhông thể thêm mới", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
//MessageBox.Show(ex.Message);
}
finally
{
btnCancel_Click(sender, e);
//if (conn.State == ConnectionState.Open) conn.Close();
}
}
else
{
MetroFramework.MetroMessageBox.Show(this, "Nhập thiếu dữ liệu.", "Lỗi.", MessageBoxButtons.OK, MessageBoxIcon.Warning);
if (txtMK.Text == "")
{
txtMK.Focus();
}
if (txtSoCMT.Text == "")
{
txtSoCMT.Focus();
}
if (txtDiaChi.Text == "")
{
txtDiaChi.Focus();
}
if (txtTenDG.Text == "")
{
txtTenDG.Focus();
}
if (txtMaDG.Text == "")
{
txtMaDG.Focus();
}
}
}
else
{
MetroFramework.MetroMessageBox.Show(this, "Nhập ngày sinh sai.", "Lỗi.", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public bool SuaDG1(DocGia_DTO dg, string tdn)
{
if (string.IsNullOrEmpty(tdn))
{
return(false);
}
dgDAO.SuaDG(dg, tdn);
return(true);
}
public bool SuaDG1(DocGia_DTO dg, Account_DTO acc)
{
if (string.IsNullOrEmpty(acc.MaDocGia))
{
return(false);
}
dgDao.SuaDG(dg, acc);
return(true);
}
public string dem_nxb()
{
string sql = "select COUNT(*) as TongNXB from NXB_SOSACH ";
DataTable lm = connData.getdata(sql);
DocGia_DTO dg = new DocGia_DTO();
DataRow g = lm.Rows[0];
string t = g["TongNXB"].ToString();
return(t);
}
//đếm độc giả
public string demdocgia()
{
string sql = "select * from TongDG";
DataTable s = connData.getdata(sql);
DocGia_DTO dg = new DocGia_DTO();
DataRow g = s.Rows[0];
string t = g["tongDG"].ToString();
return(t);
}
// dem doc gia trong bang lượt mượn
public string demDG_Luotmuon()
{
string sql = "select COUNT(*) as DG_LM from DocGia_TongMuon";
DataTable lm = connData.getdata(sql);
DocGia_DTO dg = new DocGia_DTO();
DataRow g = lm.Rows[0];
string t = g["DG_LM"].ToString();
return(t);
}
public void SuaDG(DocGia_DTO dg, Account_DTO acc)
{
string Sql = string.Format("update ACCOUNT set TenDangNhap = N'{0}' where MaDocGia = '{1}'",
acc.TenDangNhap, acc.MaDocGia);
Excute(Sql);
string sql = string.Format("update DOCGIA set TenDangNhap = N'{0}', HoTen = N'{1}', GioiTinh = N'{2}', NamSinh = '{3}', DiaChi = N'{4}' where MaDocGia = '{5}'",
acc.TenDangNhap, dg.HoTen, dg.GioiTinh, dg.NamSinh, dg.DiaChi, acc.MaDocGia);
Excute(sql);
}
public bool SuaDG(DocGia_DTO dg, string tdn)
{
if (GetData("select* from DOCGIA where TenDangNhap = '" + tdn + "'").Rows.Count <= 0)
{
return(false);
}
string sql = string.Format("update DOCGIA set HoTen = N'{0}', GioiTinh = N'{1}', NamSinh = '{2}', DiaChi = N'{3}' where TenDangNhap = N'{4}'",
dg.HoTen, dg.GioiTinh, dg.NamSinh, dg.DiaChi, tdn);
Excute(sql);
return(true);
}
public int ThemDG1(DocGia_DTO dg, Account_DTO acc)
{
if (string.IsNullOrEmpty(acc.MaDocGia))
{
return(0);
}
if (!dgDao.ThemDG(dg, acc))
{
return(-1);
}
return(1);
}
public List <DocGia_DTO> LoadToanBoDocGia()
{
List <DocGia_DTO> lstDocGia = new List <DocGia_DTO>();
DataTable data = DataProvider.Instance.ExecuteQuery("LayTatCaDocGia");
foreach (DataRow item in data.Rows)
{
DocGia_DTO x = new DocGia_DTO(item);
lstDocGia.Add(x);
}
return(lstDocGia);
}
public frm_DocGia()
{
InitializeComponent();
docgiaBUS = new DocGia_BUS();
docgiaDTO = new DocGia_DTO();
qd1 = new QuyDinh1_BUS();
DataTable table = qd1.Select();
var i = table.Rows[0].ItemArray;
TuoiMin = int.Parse(i[0].ToString());
TuoiMax = int.Parse(i[1].ToString());
KhoiTaoCmbTiemKiem();
}
public List <DocGia_DTO> LoadDocGiaComboBox()
{
List <DocGia_DTO> lstDocGia = new List <DocGia_DTO>();
string query = " SELECT * FROM DocGia";
DataTable data = DataProvider.Instance.ExecuteQuery(query);
foreach (DataRow item in data.Rows)
{
DocGia_DTO kh = new DocGia_DTO(item);
lstDocGia.Add(kh);
}
return(lstDocGia);
}
private void btnOkSua_Click(object sender, EventArgs e)
{
if (DateTime.Compare(dtpNgaySinh.Value, DateTime.Today) < 0)
{
if ((txtMaDG.Text != "") && (txtTenDG.Text != "") && (txtDiaChi.Text != "") && (txtSoCMT.Text != "") && (txtMK.Text != ""))
{
try
{
DocGia_DTO docGia = new DocGia_DTO(txtMaDG.Text, txtTenDG.Text, cboGioiTinh.SelectedItem.ToString(), dtpNgaySinh.Value, txtDiaChi.Text, txtSoCMT.Text, (string)cboLoaiDG.SelectedValue, txtMK.Text);
BUS_OBJ.suaDG(docGia);
autoSuaUser();
HienThiDuLieu();
}
catch (Exception ex)
{
MetroFramework.MetroMessageBox.Show(this, ex.Message);
}
finally
{
btnCancel_Click(sender, e);
//if (conn.State == ConnectionState.Open) conn.Close();
}
}
else
{
MessageBox.Show(this, "Nhập thiếu dữ liệu.", "Lỗi.", MessageBoxButtons.OK, MessageBoxIcon.Warning);
if (txtMK.Text == "")
{
txtMK.Focus();
}
if (txtSoCMT.Text == "")
{
txtSoCMT.Focus();
}
if (txtDiaChi.Text == "")
{
txtDiaChi.Focus();
}
if (txtTenDG.Text == "")
{
txtTenDG.Focus();
}
//if (txtMaDG.Text == "") txtMaDG.Focus();
}
}
else
{
MetroFramework.MetroMessageBox.Show(this, "Nhập ngày sinh sai.", "Lỗi.", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
//Sửa Độc Giả vào CSDL
public bool SuaDG(DocGia_DTO dg)
{
if (KiemTraTruocKhiLuu(dg))
{
string sql = string.Format("UPDATE DOCGIA SET HoTenDG=N'{1}', EmailDG=N'{2}', GioiTinhDG={3}, NgaySinhDG=N'{4}', DiaChiDG=N'{5}', DienThoaiDG=N'{6}', NgayLamThe=N'{7}', NgayHetHan=N'{8}' WHERE MaDG=N'{0}'",
dg.MaDG, dg.HoTenDG, dg.EmailDG, dg.GioiTinhDG, dg.NgaySinhDG, dg.DiaChiDG, dg.DienThoaiDG, dg.NgayLamThe, dg.NgayHetHan);
if (connData.ThucThiSQL(sql))
{
MessageBox.Show("Sửa Độc Giả thành công !", "Thông tin", MessageBoxButtons.OK, MessageBoxIcon.Information);
return(true);
}
}
return(false);
}
//Thêm Độc Giả vào CSDL
public bool ThemDG(DocGia_DTO dg)
{
if (KiemTraTruocKhiLuu(dg))
{
string sql = string.Format("INSERT INTO DOCGIA (MaDG, HoTenDG, EmailDG, GioiTinhDG, NgaySinhDG, DiaChiDG, DienThoaiDG, NgayLamThe, NgayHetHan)"
+ " VALUES ('{0}', N'{1}', N'{2}', '{3}',N'{4}', N'{5}', N'{6}', N'{7}', N'{8}')", dg.MaDG, dg.HoTenDG, dg.EmailDG, dg.GioiTinhDG, dg.NgaySinhDG, dg.DiaChiDG, dg.DienThoaiDG, dg.NgayLamThe, dg.NgayHetHan);
if (connData.ThucThiSQL(sql))
{
MessageBox.Show("Thêm Độc Giả thành công", "Thông tin", MessageBoxButtons.OK, MessageBoxIcon.Information);
return(true);
}
}
return(false);
}
//Lấy Thông tin Độc giả
private DocGia_DTO LayTTDG()
{
DocGia_DTO dg = new DocGia_DTO();
dg.MaDG = txtMaDG.Text;
dg.HoTenDG = txtTenDG.Text;
dg.EmailDG = txtEmail.Text;
dg.GioiTinhDG = (cboGioiTinh.Text == "Nam") ? 1 : 0;
dg.NgaySinhDG = Convert.ToDateTime(cboNgaySinh.EditValue.ToString());
dg.DiaChiDG = txtDiaChi.Text;
dg.DienThoaiDG = txtSdt.Text;
dg.NgayLamThe = Convert.ToDateTime(cboNgayLamThe.EditValue.ToString());
dg.NgayHetHan = Convert.ToDateTime(cboNgayHetHan.EditValue.ToString());
return(dg);
}
public bool Them(PhieuMuon_DTO pm)
{
Sach_DTO s = new Sach_DTO();
DocGia_DTO dg = new DocGia_DTO();
if (GetData("select* from PHIEUMUON where MaPhieu = '" + pm.MaPhieu + "'").Rows.Count > 0)
{
return(false);
}
string sql = "insert into PHIEUMUON (MaPhieu, MaDocGia, MaSach, NgayMuon, NgayPhaiTra ) values('" + pm.MaPhieu + "','" + pm.MaDocGia + "','" + pm.MaSach + "','" + pm.NgayMuon + "','" + pm.NgayPhaiTra + "')";
Excute(sql);
return(true);
}
private void bt_luu_Click(object sender, EventArgs e)
{
DocGia_DTO tg = LayTTDG();
if (add)
{
dgBUS.ThemDG(tg);
ttDocGia_Load(sender, e);
}
if (update)
{
dgBUS.SuaDG(tg);
}
ttDocGia_Load(sender, e);
}
public static bool Xoa(DocGia_DTO DG)
{
try
{
con = DataProvider.KetNoi();
string sTruyVan = string.Format("Delete From DocGia where MaDocGia = '{0}'", DG.MaDocGia);
DataProvider.ThucThiTruyVanNonQuery(sTruyVan, con);
DataProvider.DongKetNoi(con);
return(true);
}
catch
{
return(false);
}
}
public static bool Sua(DocGia_DTO DG)
{
try
{
con = DataProvider.KetNoi();
string sTruyVan = string.Format("Update DocGia set HoTen = N'{0}' ,NgaySinh = '{1}',GioiTinh='{2}',DienThoai = '{3}',DiaChi=N'{4}',Email='{5}' where MaDocGia ='{6}'", DG.HoTen, DG.NgaySinh, DG.GioiTinh, DG.DienThoai, DG.DiaChi, DG.Email, DG.MaDocGia);
DataProvider.ThucThiTruyVanNonQuery(sTruyVan, con);
DataProvider.DongKetNoi(con);
return(true);
}
catch
{
return(false);
}
}
public static bool Them(DocGia_DTO DG)
{
try
{
string sTruyVan = string.Format("Insert into DocGia(HoTen,NgaySinh,GioiTinh,DienThoai,DiaChi,Email) values(N'{0}','{1}','{2}','{3}',N'{4}','{5}')", DG.HoTen, DG.NgaySinh, DG.GioiTinh, DG.DienThoai, DG.DiaChi, DG.Email);
con = DataProvider.KetNoi();
DataProvider.ThucThiTruyVanNonQuery(sTruyVan, con);
DataProvider.DongKetNoi(con);
return(true);
}
catch
{
return(false);
}
}
public List <DocGia_DTO> TimKiemDG(string str)
{
List <DocGia_DTO> DanhSachDG = new List <DocGia_DTO>();
string query = "select maDocGia,tenDocGia,diaChi,SDT"
+ " from DocGia "
+ "where tenDocGia like N'%" + str + "%'";
DataTable data = DataProvider.Instance.ExecuteQuery(query);
foreach (DataRow item in data.Rows)
{
DocGia_DTO DG = new DocGia_DTO(item);
DanhSachDG.Add(DG);
}
return(DanhSachDG);
}
public DocGia_DTO TimDocGiaTheoMaDocGia(string MaDocGia)
{
DocGia_DTO dg_DTO = new DocGia_DTO();
//tim 1 nhan vien theo ma nhan vien
DocGia dg = data.DocGias.SingleOrDefault(u => u.MaDocGia == MaDocGia && u.TrangThai.Value == true);
// đỗ dữ liệu từ nhân viên trong database sang nhân viên dto de chuyen di
dg_DTO.MaDocGia = dg.MaDocGia;
dg_DTO.TenDocGia = dg.TenDocGia;
dg_DTO.CMND = dg.CMND;
dg_DTO.DiaChi = dg.DiaChi;
dg_DTO.SoThe = dg.SoThe;
dg_DTO.TrangThai = dg.TrangThai.Value;
return(dg_DTO);
}
