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 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 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 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 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 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 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_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 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_DSMay(string timkiem, string khoaphong)
{
DataSet ds = new DataSet();
try
{
sql = " select a.id,a.mamay,a.ten,b.ten,c.ten,a.Vitri,e.Ten,f.Ten,a.DonGia,g.ten,a.ngaybdsd ";
sql += "from may a,dmquocgia b,dmkhoaphong c,dmloaimay e,dmloaichitiet f,dmtinhtrang g ";
sql += " where a.NuocSX=b.ID and a.MaKP=c.ID and a.MaLoai=e.ID and a.tinhtrang=g.id and a.LoaiChiTiet=f.ID ";
sql += "and (a.mamay like '%" + timkiem + "%' or a.ten like '%" + timkiem + "%') and a.makp='" + khoaphong + "' order by mamay";
CConnection data = new CConnection();
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_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)
{
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_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_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);
}
public DataSet get_DanhSachXoaChiDinhFromUser(string User, DateTime ngay)
{
DataSet ds = new DataSet();
try
{
sql = " select 0,a.stt,a.MaBN,b.ten ,a.Mavp,a.Ten tenvp,a.soluong,a.ngaythuchien,'-',a.makp,a.id,a.stt ";
sql += "from v_chidinh a,btdbn b";
sql += " where a.userid='" + User + "' and date(a.ngaythuchien)='" + f_formatDate(ngay) + "' ";
sql += " and a.mabn=b.id ";
CConnection data = new CConnection(3);
ds = data.getData(sql);
}
catch { }
return(ds);
}
public DataSet get_DanhSachXoaChiDinhFromMaBN(string MaBN, DateTime ngay)
{
DataSet ds = new DataSet();
try
{
sql = " select 0,a.stt,a.MaBN,b.ten ,a.Mavp,a.Ten tenvp,a.soluong,a.ngaythuchien,'-',a.makp,a.id,a.stt ";
sql += "from v_chidinh a,btdbn b";
sql += " where a.MaBN='" + MaBN + "' and month(a.ngaythuchien)='" + ngay.Month + "' and year(a.ngaythuchien)='" + ngay.Year + "'";
sql += " and a.mabn=b.id ";
CConnection data = new CConnection(3);
ds = data.getData(sql);
}
catch { }
return(ds);
}
public DataSet get_NoiDungXoaChiDinhFromIDYC(string IDYC)
{
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.STT='" + IDYC + "' ";
sql += " and a.mabn=b.id ";
CConnection data = new CConnection(3);
ds = data.getData(sql);
}
catch { }
return(ds);
}
public DataSet get_BCBaoTri(string s_tungay, string s_denngay)
{
DataSet ds = new DataSet();
try
{
string sql = "select DATE_FORMAT(bt.Ngay, '%d-%m-%Y') ngaybaotri,kp.Ten TenKP,m.MaMay MAMAY,m.Ten TENMAY,M.Vitri Vitri ,bt.Noidung from baotri bt ";
sql += " join may m on bt.MaMay=m.ID ";
sql += " join dmkhoaphong kp on m.MaKP=kp.ID ";
sql += " where bt.Ngay BETWEEN '{tungay}' and '{denngay}' ";
sql = sql.Replace("{tungay}", s_tungay);
sql = sql.Replace("{denngay}", s_denngay);
CConnection data = new CConnection();
ds = data.getData(sql);
}
catch { }
return(ds);
}
public string get_RightDangNhap(string ID)
{
DataSet ds = new DataSet();
string result = "-1";
try
{
sql = " select _Right ";
sql += " from dlogin";
sql += " where ID='" + ID + "'";
CConnection data = new CConnection(3);
ds = data.getData(sql);
result = ds.Tables[0].Rows[0]["_Right"].ToString();
}
catch { }
return(result);
}