public void Delete(string MaBN)
{
sql = "Delete btdbn where mabn='" + MaBN + "'";
CConnection data = new CConnection();
data.setData(sql);
}
public void Delete(string ID)
{
sql = "Delete thanhtoanbhyt where [ID]='" + ID + "'";
CConnection data = new CConnection();
data.setData(sql);
}
public void Insert(CBenhNhan bn)
{
sql = "Insert into btdbn(mabn,hoten,namsinh,thon,phai)";
sql += " values('" + bn.MaBN + "',N'" + bn.HoTen + "','" + bn.NamSinh + "',N'" + bn.DiaChi + "'," + bn.GioiTinh + ")";
CConnection data = new CConnection();
data.setData(sql);
}
public DataSet get_trungmau01bv(string FromDate, string ToDate)
{
DataSet ds = new DataSet();
string s = "";
try
{
sql = "select convert(varchar,a.ngaylamviec,103) Ngay,a.MaBN MaBN from thanhtoanbhyt a where convert(varchar,a.ngaylamviec,112) >='" + for_ngay_yyyymmdd(FromDate) + "' and convert(varchar,a.ngaylamviec,112) <='" + for_ngay_yyyymmdd(ToDate) + "' group by a.ngaylamviec,a.mabn having count(*)>1";
CConnection data = new CConnection();
ds = data.getData(sql);
}
catch { }
return(ds);
}
public int InsertCLS20(string id, string mabn, string stt, string mavp, string dongia, string soluong, DateTime ngay)
{
int roweffect = 0;
try
{
sql = "Insert into d_clsbhyt20(ID,STT,MABN,MAVP,DONGIA,soluong,ngay)";
sql += " values(" + id + "," + stt + ",'" + mabn + "','" + mavp + "',";
sql += dongia + "," + soluong + ",'" + FormatYYYY_MM_DD(ngay) + "')";
CConnection data = new CConnection();
roweffect = data.setData(sql);
}
catch { }
return(roweffect);
}
public int InsertThuoc20(string id, string mabn, string stt, string mabd, string dongia, string soluong, string thanhtien, string GPNK, string BVAPTHAU, string noikham, DateTime ngay)
{
int roweffect = 0;
try
{
sql = "Insert into d_thuocbhyt20(ID,STT,MABN,MABD,DONGIA,SOLUONG,THANHTIEN,GPNK,BVAPTHAU,MaKho,ngay)";
sql += " values(" + id + "," + stt + ",'" + mabn + "','" + mabd + "',";
sql += dongia + "," + soluong + "," + thanhtien + ",'" + GPNK + "','" + BVAPTHAU + "','" + noikham + "','" + FormatYYYY_MM_DD(ngay) + "')";
CConnection data = new CConnection();
roweffect = data.setData(sql);
}
catch { }
return(roweffect);
}
public string KiemTraDaDuyet(string MaBN, string IDTTRV, DateTime Ngay)
{
string result = "";
DataSet ds = new DataSet();
try
{
sql = "select [ID] from Thanhtoanbhyt where mabn='" + MaBN + "' and IDTTRV='" + IDTTRV + "' and convert(varchar,NgayLamViec,112)='" + string.Format("{0:yyyyMMdd}", Ngay) + "'";
CConnection data = new CConnection();
ds = data.getData(sql);
result = ds.Tables[0].Rows[0]["ID"].ToString();
}
catch { }
return(result);
}
public int Insert(CThanhToanBHYT t)
{
int roweffect = 0;
try
{
sql = "Insert into thanhtoanbhyt(mabn,nhomthe,loaithe,mavaovien,sothebhyt,noidangky,icd,chandoan,tongtien,bhyttra,bntra,thuoc,mau,xetnghiem,cdha,dvktthongthuong,dvktcao,vtyt,tienkham,chiphivc,thamdochucnang,giuong,traituyen,idttrv,ngaylamviec,sophieu,khac,manhomthe,ngayhd,loaiba,sobienlai,tungay)";
sql += " values('" + t.MaBN + "',N'" + t.NhomThe + "','" + t.LoaiThe + "'," + t.MaVaoVien + ",";
sql += "'" + t.SoTheBHYT + "',N'" + t.NoiDangKyBHYT + "','" + t.ICD + "',N'" + t.ChanDoan + "',";
sql += t.TongTien + "," + t.BHYTTra + "," + t.BNTra + "," + t.Thuoc + "," + t.Mau + "," + t.XetNghiem + ",";
sql += t.CDHA + "," + t.DVKTthongthuong + "," + t.DVKTcao + "," + t.VTYT + "," + t.TienKham + "," + t.ChiPhiVC + ",";
sql += t.ThamDoChucNang + "," + t.Giuong + "," + t.TraiTuyen + "," + t.IDTTRV + ",'" + FormatYYYY_MM_DD(t.NgayLamViec) + "','" + t.SoPhieu + "'," + t.Khac + ",'" + t.MaNhomThe + "','" + FormatYYYY_MM_DD(t.HSD) + "','" + t.LoaiBA + "'," + t.SoBienLai + ",'" + FormatYYYY_MM_DD(t.TuNgay) + "')";
CConnection data = new CConnection();
roweffect = data.setData(sql);
}
catch { }
return(roweffect);
}
public string get_MAVAOVIENdaduyet(string FromDate, string ToDate)
{
DataSet ds = new DataSet();
string s = "";
try
{
sql = "select convert(varchar,A.mavaovien) [MAVAOVIEN] from thanhtoanbhyt a where convert(varchar,a.ngaylamviec,112) >='" + for_ngay_yyyymmdd(FromDate) + "' and convert(varchar,a.ngaylamviec,112) <='" + for_ngay_yyyymmdd(ToDate) + "' ";
CConnection data = new CConnection();
ds = data.getData(sql);
DataTable t = ds.Tables[0];
foreach (DataRow row in t.Rows)
{
s += row["MAVAOVIEN"].ToString() + ",";
}
}
catch { }
s = s.TrimEnd(',');
return(s);
}
public string get_rpt_ttravien_kp_ct(string MaBN, string mavaovien)
{
DataSet ds = new DataSet();
string s = "";
try
{
sql = " ";
CConnection data = new CConnection();
ds = data.getData(sql);
DataTable t = ds.Tables[0];
foreach (DataRow row in t.Rows)
{
s += row["IDTTRV"].ToString() + ",";
}
}
catch { }
s = s.TrimEnd(',');
return(s);
}
public bool KiemTraDangNhap(string user, string password)
{
string sql;
bool result = false;
DataSet ds = new DataSet();
try
{
sql = "select * from m_user where [username]='" + user + "' and [password]='" + password + "'";
CConnection data = new CConnection();
ds = data.getData(sql);
if (ds.Tables[0].Rows.Count > 0)
{
result = true;
}
}
catch { }
return(result);
}
public DataSet get_BaoCaoBHYT(string FromDate, string ToDate)
{
DataSet ds = new DataSet();
try
{
sql = "select convert(varchar,A.MABN) [MABN],B.HOTEN [HOTEN],B.NAMSINH [NAMSINH],A.ICD [ICD10],A.SOTHEBHYT [SOTHEBHYT],A.SOPHIEU [SOTOA],A.TONGTIEN [TONGTIEN],A.BHYTTRA [BHYTTRA],A.BNTRA [BNTRA],";
sql += "A.NOIDANGKY [TENBV],'701' [MATINH],A.MANHOMTHE [MANHOMTHE],";
sql += "A.NHOMTHE [NHOMTHE],A.LOAITHE [LOAITHE],convert(varchar,A.IDTTRV) [IDTTRV],A.MAVAOVIEN [MAVAOVIEN],";
sql += "A.MABV [MABV],A.NGAYHD [NGAYHD],convert(varchar,A.SOBIENLAI) [SOBIENLAI],B.PHAI [GIOITINH] ,";
sql += "A.NGAYVAO [NGAYVAO],A.NGAYRA [NGAYRA],A.LOAIBN [LOAIBN],";
sql += "A.CHANDOAN [CHANDOAN],A.THUOC [THUOC],A.MAU [MAU],A.XETNGHIEM [XETNGHIEM],";
sql += "A.CDHA [CDHA],A.DVKTTHONGTHUONG [DVKTTHONGTHUONG],A.DVKTCAO [DVKTCAO],";
sql += "A.VTYT [VATTUYTE],A.TIENKHAM [TIENKHAM],A.GIUONG [GIUONG],A.CHIPHIVC [CHIPHIVC],A.KHAC [KHAC],";
sql += "A.THAMDOCHUCNANG [THAMDOCHUCNANG],'1011' [MMYY],convert(varchar,A.NGAYLAMVIEC,103) [NGAYLAMVIEC],";
sql += "A.TRAITUYEN [TRAITUYEN],[ID] [ID]from thanhtoanbhyt a,btdbn b where a.mabn=b.mabn and convert(varchar,a.ngaylamviec,112) >='" + for_ngay_yyyymmdd(FromDate) + "' and convert(varchar,a.ngaylamviec,112) <='" + for_ngay_yyyymmdd(ToDate) + "' ";
CConnection data = new CConnection();
ds = data.getData(sql);
}
catch { }
return(ds);
}