public DataSet get_KiemKe(string s_tungay, string s_denngay, string loaimay, string khoaphong)
{
DataSet ds = new DataSet();
try
{
sql = " select a.id id,a.mamay mamay,a.ten tenmay,b.ten tennuoc,c.ten tenkhoaphong,a.Vitri vitri, ";
sql += " e.Ten loaimay ,f.Ten loaichitiet,a.DonGia dongia,g.ten tinhtrang,a.ngaybdsd ngaybdsd";
sql += " from may a";
sql += " join dmquocgia b on a.NuocSX=b.ID ";
sql += " join dmkhoaphong c on a.MaKP=c.ID ";
sql += " join dmloaimay e on a.MaLoai=e.ID ";
sql += " join dmloaichitiet f on a.LoaiChiTiet=f.ID ";
sql += " join dmtinhtrang g on a.tinhtrang=g.id ";
sql += " where 1 ";
if (loaimay.Length > 0)
{
sql += " and a.maloai in (" + loaimay + ") ";
}
if (khoaphong.Length > 0)
{
sql += " and a.makp in (" + khoaphong + ") ";
}
CConnection data = new CConnection();
ds = data.getData(sql);
}
catch { }
return(ds);
}
public string get_DSMayThongTin(string timkiem, string khoaphong)
{
DataSet ds = new DataSet();
string strKQ = "";
try
{
if (khoaphong != "-1")
{
sql = " select c.ten , e.Ten , count(*) ";
sql += "from may a,dmkhoaphong c,dmloaimay e ";
sql += " where a.MaKP=c.ID and a.MaLoai=e.ID ";
sql += "and (a.mamay like '%" + timkiem + "%' or a.ten like '%" + timkiem + "%') and a.makp='" + khoaphong + "'";
sql += " group by c.ten,e.ten ";
}
else
{
sql = " select c.ten , e.Ten , count(*) ";
sql += "from may a,dmkhoaphong c,dmloaimay e ";
sql += " where a.MaKP=c.ID and a.MaLoai=e.ID ";
sql += "and (a.mamay like '%" + timkiem + "%' or a.ten like '%" + timkiem + "%') ";
sql += " group by c.ten,e.ten ";
}
CConnection data = new CConnection();
ds = data.getData(sql);
foreach (DataRow dr in ds.Tables[0].Rows)
{
strKQ += dr[1].ToString() + ": " + dr[2].ToString() + ";";
}
}
catch { }
return(strKQ);
}
public int DeleteDanhMuc(string TableName, string ID)
{
int roweffect = 0;
try
{
sql = "Delete from " + TableName + " where ID='" + ID + "'";
CConnection data = new CConnection(4);
roweffect = data.setData(sql);
}
catch { }
return(roweffect);
}
public int InsertCauHinhMaull(string ID, string MoTa)
{
int roweffect = 0;
try
{
sql = "Insert into cauhinhmaull(ID,Ten) values ('" + ID + "',N'" + MoTa + "')";
CConnection data = new CConnection();
roweffect = data.setData(sql);
}
catch { }
return(roweffect);
}
public int InsertBaoTri(string ID, DateTime Ngay, string MaMay, string NoiDung)
{
int roweffect = 0;
try
{
sql = "Insert into baotri(ID,Ngay,MaMay,NoiDung,ngaybd) values ('" + ID + "','" + f_formatDate(Ngay) + "','" + MaMay + "',N'" + NoiDung + "',now())";
CConnection data = new CConnection();
roweffect = data.setData(sql);
}
catch { }
return(roweffect);
}
public DataSet get_BCBaoTri(string mamay)
{
DataSet ds = new DataSet();
try
{
sql = "select a.id,DATE_FORMAT(a.Ngay, '%d-%m-%Y') ngay,a.noidung from baotri a where a.mamay='" + mamay + "'";
CConnection data = new CConnection();
ds = data.getData(sql);
}
catch { }
return(ds);
}
public DataSet get_BaoTri(string table, string id)
{
DataSet ds = new DataSet();
try
{
sql = "select a.id,a.ngay,a.mamay,b.ten,a.noidung from " + table + " a,may b where a.mamay=b.id and a.mamay='" + id + "'";
CConnection data = new CConnection();
ds = data.getData(sql);
}
catch { }
return(ds);
}
public int InsertCauHinh(string ID, string STT, string MaTB, string NoiDung)
{
int roweffect = 0;
try
{
sql = "Insert into cauhinh(ID,STT,MaTB,NoiDUng) values ('" + ID + "','" + STT + "','" + MaTB + "',N'" + NoiDung + "')";
CConnection data = new CConnection();
roweffect = data.setData(sql);
}
catch { }
return(roweffect);
}
public DataSet get_DanhMucCT(string table, string loaimay)
{
DataSet ds = new DataSet();
try
{
sql = "select id,ten,Enable,LoaiMay from " + table + " where loaimay='" + loaimay + "' and Enable='1' order by ten";
CConnection data = new CConnection(4);
ds = data.getData(sql);
}
catch { }
return(ds);
}
public int UpdateDanhMuc(string TableName, string ID, string Ten, string Enable, string loaimay)
{
int roweffect = 0;
try
{
sql = "Update " + TableName + " set Ten=N'" + Ten + "',Enable='" + Enable + "', loaimay='" + loaimay + "' where ID='" + ID + "'";
CConnection data = new CConnection(4);
roweffect = data.setData(sql);
}
catch { }
return(roweffect);
}
public int InsertDanhMuc(string TableName, string ID, string Ten, string Enable, string loai)
{
int roweffect = 0;
try
{
sql = "Insert into " + TableName + "(ID,Ten,Enable,loaimay) values ('" + ID + "',N'" + Ten + "','" + Enable + "','" + loai + "')";
CConnection data = new CConnection(4);
roweffect = data.setData(sql);
}
catch { }
return(roweffect);
}
public DataSet get_DanhMucCT(string table)
{
DataSet ds = new DataSet();
try
{
sql = "select id,ten,Enable,LoaiMay from " + table;
CConnection data = new CConnection(4);
ds = data.getData(sql);
}
catch { }
return(ds);
}
public int InsertDanhMuc_v_chidinh(string ID, string STT, string MaBN, string Loai, string mavp, string tenvp, string makp, DateTime ngayxoa, string nguoixoa, string Userid, string ngayCD, string soluong)
{
int roweffect = 0;
try
{
sql = "Insert into v_chidinh(ID,STT,MaBN,loai,mavp,ten,makp,ngaythuchien,tennguoixoa,userid,ngaychidinh,soluong) values ('" + ID + "','" + STT + "','" + MaBN + "','" + Loai + "','" + mavp + "',N'" + tenvp + "','" + makp + "','" + string.Format("{0:yyyy/MM/dd}", ngayxoa) + "',N'" + nguoixoa + "','" + Userid + "','" + ngayCD + "'," + soluong + ")";
CConnection data = new CConnection(3);
roweffect = data.setData(sql);
}
catch { }
return(roweffect);
}
public int InsertDanhMuc_btdbn(string MaBN, string Ten, string NamSinh, string DiaChi)
{
int roweffect = 0;
try
{
sql = "Insert into btdbn(MaBN,Hoten,Namsinh,DiaChi) values ('" + MaBN + "',N'" + Ten + "','" + NamSinh + "',N'" + DiaChi + "')";
CConnection data = new CConnection(3);
roweffect = data.setData(sql);
}
catch { }
return(roweffect);
}
public DataSet get_cauhinhmau(string id)
{
DataSet ds = new DataSet();
try
{
sql = "select a.matb,b.ten,a.noidung from cauhinhmauct a,dmthietbi b where a.matb=b.id and a.id='" + id + "'";
CConnection data = new CConnection();
ds = data.getData(sql);
}
catch { }
return(ds);
}
public DataSet get_DanhMuc(string table, string Enable)
{
DataSet ds = new DataSet();
try
{
sql = "select id,ten,Enable from " + table + " where Enable='" + Enable + "' order by ten";
CConnection data = new CConnection(4);
ds = data.getData(sql);
}
catch { }
return(ds);
}
public int UpdateBaoTri(string ID, string NoiDung, DateTime Ngay)
{
int roweffect = 0;
try
{
sql = "Update baotri set NoiDung=N'" + NoiDung + "',Ngay='" + f_formatDate(Ngay) + "' where ID='" + ID + "'";
CConnection data = new CConnection();
roweffect = data.setData(sql);
}
catch { }
return(roweffect);
}
public int InsertTiepNhanYC(string ID, string MaBN, string Hoten, string LoaiYC, string NoiDung, string NguoiYC, string KhoaPhong, string NguoiNhan, string HinhThuc, string TinhTrang, string HSD, string traituyen)
{
int roweffect = 0;
try
{
sql = "Insert into tiepnhanyc(ID,MaBN,Hoten,LoaiYeuCau,NoiDung,NguoiYeuCau,KhoaPhong, NguoiNhanYC,ThoiGianYC, ThoiGianKT,NgayUD,HinhThuc,TinhTrang,HSD,TraiTuyen) ";
sql += " values ('" + ID + "','" + MaBN + "',N'" + Hoten + "','" + LoaiYC + "','" + NoiDung + "',N'" + NguoiYC + "','" + KhoaPhong + "'," + NguoiNhan + ",NOW(),NOW(),NOW(),'" + HinhThuc + "','" + TinhTrang + "','" + HSD + "','" + traituyen + "')";
CConnection data = new CConnection(4);
roweffect = data.setData(sql);
}
catch { }
return(roweffect);
}
public int InsertMay(string ID, string MaMay, string Ten, string NuocSX, string MaKP, string MaLoai, string loaichitiet, string DonGia, string ViTri, string tinhtrang, DateTime NgayBDSD)
{
int roweffect = 0;
try
{
sql = "Insert into May(ID,MaMay,Ten,NuocSX,MaKP,MaLoai,LoaiChiTiet,DonGia,Vitri,NgayUD,tinhtrang,ngaybdsd) ";
sql += " values ('" + ID + "','" + MaMay + "',N'" + Ten + "','" + NuocSX + "','" + MaKP + "','" + MaLoai + "','" + loaichitiet + "'," + DonGia + ",'" + ViTri + "',now(),'" + tinhtrang + "','" + string.Format("{0:yyyy-MM-dd}", NgayBDSD) + "')";
CConnection data = new CConnection();
roweffect = data.setData(sql);
}
catch { }
return(roweffect);
}
public DataSet get_May(string barcode)
{
DataSet ds = new DataSet();
try
{
sql = " select id,mamay,ten,nuocsx,makp,maloai,loaichitiet,dongia,vitri,tinhtrang,ngaybdsd ";
sql += "from may ";
sql += " where mamay='" + barcode + "'";
CConnection data = new CConnection();
ds = data.getData(sql);
}
catch { }
return(ds);
}
public string getID(string tablename)
{
string strID = "1";
DataSet ds = new DataSet();
try
{
string sql = "select Max(ID+1) from " + tablename;
CConnection data = new CConnection(4);
ds = data.getData(sql);
int value = int.Parse(ds.Tables[0].Rows[0][0].ToString());
strID = value.ToString();
}
catch { }
return(strID);
}
public DataSet get_MayFromID(string ID)
{
DataSet ds = new DataSet();
try
{
sql = " select a.id,a.mamay,a.ten tenmay,b.ten nuocsx,c.ten khoaphong,a.Vitri,e.Ten loaimay,f.Ten loaichitiet,a.DonGia,g.ten tinhtrang,a.ngaybdsd ngaybdsd ";
sql += "from may a,dmquocgia b,dmkhoaphong c,dmloaimay e,dmloaichitiet f,dmtinhtrang g ";
sql += " where a.id='" + ID + "' and a.NuocSX=b.ID and a.MaKP=c.ID and a.MaLoai=e.ID and a.tinhtrang=g.id and a.LoaiChiTiet=f.ID";
CConnection data = new CConnection();
ds = data.getData(sql);
}
catch { }
return(ds);
}
public int UpdateNoiDung(string ID, string NoiDung)
{
int roweffect = 0;
try
{
sql = "Update TiepNhanYC set Noidung=N'" + NoiDung + "' ";
sql += "WHERE ID='" + ID + "'";
CConnection data = new CConnection(3);
roweffect = data.setData(sql);
}
catch { }
return(roweffect);
}
public int UpdateTiepNhan_Done(string ID, string done, DateTime thoigiantra)
{
int roweffect = 0;
try
{
sql = "Update TiepNhanYC set done=" + done + ",thoigiankt='" + FormatYYYY_MM_DD_hh_ii(thoigiantra) + "' ";
sql += "WHERE ID='" + ID + "'";
CConnection data = new CConnection(4);
roweffect = data.setData(sql);
}
catch { }
return(roweffect);
}
public DataSet get_DanhSachFrom_Ngay_Dot(string timkiem, DateTime Ngay, string Dot)
{
DataSet ds = new DataSet();
try
{
sql = " select a.id id,a.done,DATE_FORMAT(a.ThoiGianYC, '%d-%m-%Y %H:%i') thoigianyc,a.MaBN,a.HoTen,b.Ten KhoaPhong,a.NoiDung NoiDung,a.NguoiYeuCau,c.Ten NguoiNhanYC,d.Ten LoaiYeuCau,e.Ten tinhtrang,a.ThoiGianKT ";
sql += "from tiepnhanyc a,dmkhoaphong b,dmuser c, dmloaiyeucau d,dmtinhtrang e";
sql += " where a.KhoaPhong=b.ID and a.NguoiNhanYC=c.ID and a.LoaiYeuCau=d.ID and a.TinhTrang=e.ID";
sql += " and (a.mabn like '%" + timkiem + "%' or a.hoten like '%" + timkiem + "%') and ";
sql += " date(thoigianyc)='" + f_formatDate(Ngay) + "' and a.loaiyeucau='" + Dot + "'";
CConnection data = new CConnection(4);
ds = data.getData(sql);
}
catch { }
return(ds);
}
public DataSet get_DanhSach(string timkiem, DateTime TuNgay, DateTime DenNgay, string done)
{
DataSet ds = new DataSet();
try
{
sql = " select a.id id,a.Done,DATE_FORMAT(a.ThoiGianYC, '%d-%m-%Y %H:%i') thoigianyc,a.MaBN,a.HoTen,b.Ten KhoaPhong,a.NoiDung NoiDung,a.NguoiYeuCau SoThe,c.Ten NguoiNhanYC,d.Ten LoaiYeuCau,e.Ten tinhtrang,a.ThoiGianKT ,0 sotien,HSD Denngay,traituyen ";
sql += "from tiepnhanyc a,dmkhoaphong b,dmuser c, dmloaiyeucau d,dmtinhtrang e";
sql += " where a.KhoaPhong=b.ID and a.NguoiNhanYC=c.ID and a.LoaiYeuCau=d.ID and a.TinhTrang=e.ID";
sql += " and (a.mabn like '%" + timkiem + "%' or a.hoten like '%" + timkiem + "%') and ";
sql += " date(thoigianyc)>='" + f_formatDate(TuNgay) + "' and date(thoigianyc)<='" + f_formatDate(DenNgay) + "' and (a.done is null or a.done=0)";
CConnection data = new CConnection(4);
ds = data.getData(sql);
}
catch { }
return(ds);
}
public DataSet get_DanhSachDuyetXoaCLS(DateTime tungay, DateTime denngay)
{
DataSet ds = new DataSet();
try
{
sql = " select 0,a.stt,a.MaBN,b.ten ,a.Mavp,a.Ten tenvp,a.soluong,DATE_FORMAT(a.ngaythuchien, '%d-%m-%Y ') ngaythuchien,'-',a.makp,a.id,a.stt,a.tennguoixoa ,a.ngaychidinh";
sql += " from v_chidinh a,btdbn b";
sql += " where a.mabn=b.id ";
sql += " and date(a.ngaythuchien)>='" + f_formatDate(tungay) + "' and date(a.ngaythuchien)<='" + f_formatDate(denngay) + "'";
CConnection data = new CConnection(3);
ds = data.getData(sql);
}
catch { }
return(ds);
}
public DataSet get_DanhSach(string timkiem)
{
DataSet ds = new DataSet();
try
{
sql = " select a.id,a.ThoiGianYC,a.MaBN,a.HoTen,b.Ten,a.NoiDung,a.NguoiYeuCau,c.Ten NguoiNhanYC,d.Ten LoaiYeuCau,e.Ten tinhtrang,a.ThoiGianKT ";
sql += "from tiepnhanyc a,dmkhoaphong b,dmuser c, dmloaiyeucau d,dmtinhtrang e";
sql += " where a.KhoaPhong=b.ID and a.NguoiNhanYC=c.ID and a.LoaiYeuCau=d.ID and a.TinhTrang=e.ID";
sql += " and (a.mabn like '%" + timkiem + "%' or a.hoten like '%" + timkiem + "%')";
CConnection data = new CConnection(4);
ds = data.getData(sql);
}
catch { }
return(ds);
}
public DataSet get_DanhSachXoaChiDinhFromUser(DateTime ngay)
{
DataSet ds = new DataSet();
try
{
sql = " select 0,stt,MaBN,'-',Mavp,Ten tenvp,soluong,ngaythuchien,'-',makp,id ";
sql += "from v_chidinh ";
sql += " where date(ngaythuchien)='" + f_formatDate(ngay) + "'";
CConnection data = new CConnection(3);
ds = data.getData(sql);
}
catch { }
return(ds);
}
public DataSet get_TiepNhanYCFromID(string ID)
{
DataSet ds = new DataSet();
try
{
sql = " select ID,MaBN,Hoten,LoaiYeuCau,NoiDung,NguoiYeuCau,KhoaPhong, NguoiNhanYC,ThoiGianYC, ThoiGianKT,NgayUD,HinhThuc,TinhTrang,HSD,TraiTuyen ";
sql += "from tiepnhanyc";
sql += " where id='" + ID + "'";
CConnection data = new CConnection(4);
ds = data.getData(sql);
}
catch { }
return(ds);
}