private void butIN_Click(object sender, System.EventArgs e)
{
Cursor = Cursors.WaitCursor;
namsinh = int.Parse(tu.Text.ToString().Substring(6, 4));
ds.Clear();
sql = "select a.mabn,b.hoten,to_char(a.ngay,'dd/mm/yyyy') as ngay,e.ten as tenkp,";
sql += " case when a.kham=1 and " + namsinh + "-to_number(b.namsinh)<=6 then case when f.id is null then 1 else case when g.mat=0 then 1 else f.mp+f.mt end end else 0 end as c01,";
sql += " case when a.kham=1 and " + namsinh + "-to_number(b.namsinh)>6 and " + namsinh + "-to_number(b.namsinh)<=14 then case when f.id is null then 1 else case when g.mat=0 then 1 else f.mp+f.mt end end else 0 end as c02,";
sql += " case when a.kham=1 and " + namsinh + "-to_number(b.namsinh)>14 and b.phai=0 then case when f.id is null then 1 else case when g.mat=0 then 1 else f.mp+f.mt end end else 0 end as c03,";
sql += " case when a.kham=1 and " + namsinh + "-to_number(b.namsinh)>14 and b.phai=1 then case when f.id is null then 1 else case when g.mat=0 then 1 else f.mp+f.mt end end else 0 end as c04,";
sql += " case when a.kham=0 and " + namsinh + "-to_number(b.namsinh)<=6 then case when f.id is null then 1 else case when g.mat=0 then 1 else f.mp+f.mt end end else 0 end as c05,";
sql += " case when a.kham=0 and " + namsinh + "-to_number(b.namsinh)>6 and " + namsinh + "-to_number(b.namsinh)<=14 then case when f.id is null then 1 else case when g.mat=0 then 1 else f.mp+f.mt end end else 0 end as c06,";
sql += " case when a.kham=0 and " + namsinh + "-to_number(b.namsinh)>14 and b.phai=0 then case when f.id is null then 1 else case when g.mat=0 then 1 else f.mp+f.mt end end else 0 end as c07,";
sql += " case when a.kham=0 and " + namsinh + "-to_number(b.namsinh)>14 and b.phai=1 then case when f.id is null then 1 else case when g.mat=0 then 1 else f.mp+f.mt end end else 0 end as c08,";
sql += " case when b.matt='" + matt + "' and a.madoituong=1 then case when f.id is null then 1 else case when g.mat=0 then 1 else f.mp+f.mt end end else 0 end as c09,";
sql += " case when b.matt='" + matt + "' and " + namsinh + "-to_number(b.namsinh)<=6 and a.madoituong<>1 and c.mien=0 then case when f.id is null then 1 else case when g.mat=0 then 1 else f.mp+f.mt end end else 0 end as c10,";
sql += " case when b.matt='" + matt + "' and " + namsinh + "-to_number(b.namsinh)>6 and " + namsinh + "-to_number(b.namsinh)<=14 and a.madoituong<>1 and c.mien=0 then case when f.id is null then 1 else case when g.mat=0 then 1 else f.mp+f.mt end end else 0 end as c11,";
sql += " case when b.matt='" + matt + "' and " + namsinh + "-to_number(b.namsinh)<=6 and a.madoituong<>1 and c.mien=1 then case when f.id is null then 1 else case when g.mat=0 then 1 else f.mp+f.mt end end else 0 end as c12,";
sql += " case when b.matt='" + matt + "' and " + namsinh + "-to_number(b.namsinh)>6 and " + namsinh + "-to_number(b.namsinh)<=14 and a.madoituong<>1 and c.mien=1 then case when f.id is null then 1 else case when g.mat=0 then 1 else f.mp+f.mt end end else 0 end as c13,";
sql += " case when b.matt<>'" + matt + "' and a.madoituong=1 then case when f.id is null then 1 else case when g.mat=0 then 1 else f.mp+f.mt end end else 0 end as c14,";
sql += " case when b.matt<>'" + matt + "' and " + namsinh + "-to_number(b.namsinh)<=6 and a.madoituong<>1 and c.mien=0 then case when f.id is null then 1 else case when g.mat=0 then 1 else f.mp+f.mt end end else 0 end as c15,";
sql += " case when b.matt<>'" + matt + "' and " + namsinh + "-to_number(b.namsinh)>6 and " + namsinh + "-to_number(b.namsinh)<=14 and a.madoituong<>1 and c.mien=0 then case when f.id is null then 1 else case when g.mat=0 then 1 else f.mp+f.mt end end else 0 end as c16,";
sql += " case when b.matt<>'" + matt + "' and " + namsinh + "-to_number(b.namsinh)<=6 and a.madoituong<>1 and c.mien=1 then case when f.id is null then 1 else case when g.mat=0 then 1 else f.mp+f.mt end end else 0 end as c17,";
sql += " case when b.matt<>'" + matt + "' and " + namsinh + "-to_number(b.namsinh)>6 and " + namsinh + "-to_number(b.namsinh)<=14 and a.madoituong<>1 and c.mien=1 then case when f.id is null then 1 else case when g.mat=0 then 1 else f.mp+f.mt end end else 0 end as c18";
sql += " from xxx.cls_thuchien a inner join " + user + ".btdbn b on a.mabn=b.mabn ";
sql += " inner join " + user + ".doituong c on a.madoituong=c.madoituong ";
sql += " left join xxx.cls_sdthuoc d on a.id=d.id ";
sql += " left join " + user + ".cls_thuoc e on a.id=e.id ";
sql += " left join xxx.cls_mat f on a.id=f.id ";
sql += " inner join " + user + ".cls_loai g on a.loai=g.id";
sql += " where " + m.for_ngay("a.ngay", stime) + " between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")";
sql += " and a.loai=" + int.Parse(loai.SelectedValue.ToString());
sql += " order by a.ngay,b.hoten";
ds = m.get_data_mmyy(sql, tu.Text, den.Text, false);
Cursor = Cursors.Default;
if (ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), LibMedi.AccessData.Msg);
}
else
{
string tenfile = (dt.Rows[loai.SelectedIndex]["thuoc"].ToString() == "1")?"rpttkdscls_t.rpt":"rpttkdscls.rpt";
dllReportM.frmReport f = new dllReportM.frmReport(m, ds, loai.Text.Trim().ToUpper() + "," + ((tu.Text == den.Text)?"Ngày :" + tu.Text:"Từ ngày " + tu.Text + " đến " + den.Text), tenfile);
f.ShowDialog();
}
}
private void butIn_Click(object sender, EventArgs e)
{
if (ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), LibMedi.AccessData.Msg);
}
else
{
string title = "";
if (tu.Text != "" && den.Text != "")
{
title = (tu.Text == den.Text) ? "Ngày :" + tu.Text : "Từ ngày :" + tu.Text + " đến " + den.Text;
}
dllReportM.frmReport f = new dllReportM.frmReport(m, ds, "rptTimcls.rpt", title, loai.Text, idcls.Text, hoten.Text, namsinh.Text, maloai.Text);
f.ShowDialog();
}
}
private void butIn_Click(object sender, EventArgs e)
{
bIn = true;
butLuu_Click(null, null);
if (!dachon)
{
return;
}
bIn = false;
DataTable dt = new DataTable();
string[] s_mmyy = m.get_s_mmyy(m.StringToDate(dtVaccin.Rows[0]["ngaytiem"].ToString()).AddDays(-i_ngaylv_ngayht), m.StringToDate(dtVaccin.Rows[0]["ngaytiem"].ToString()).AddDays(i_ngaylv_ngayht)).Split(',');
for (int i = 0; i < s_mmyy.Length; i++)
{
try
{
sql = "select j.ten loai,b.ten thuoc,a.muitiem,b.duongdung,a.vitritiem,c.hoten,to_char(a.ngaytiem,'dd/mm/yyyy hh24:mi') ngaytiem , d.sttt,g.ten hangsx,h.ten nhacungcap, ";
sql += " case when to_number(f.handung)=0 then '' else f.handung end handung,f.losx,k.mabn, k.tenbome,k.mota,k.tiensu,k.ghichu,to_char(k.ngay,'dd/mm/yyyy hh:mi') ngaypu,m.ten tinhtrang,n.hoten nguoinhap,a.phanung,a.mabd ";
sql += " from " + m.user + ".phieutiemchung a left join " + m.user + ".dmbs c on a.mabs=c.ma, " + m.user + ".d_dmbd b, " + m.user + s_mmyy[i] + ".v_chidinh e," + m.user + s_mmyy[i] + ".d_xuatsdct d, ";
sql += " " + m.user + s_mmyy[i] + ".d_theodoi f," + m.user + ".d_dmhang g, " + m.user + ".d_dmnx h ," + m.user + ".d_dmloai j," + m.user + ".phanung_sautc k," + m.user + ".dmtinhtrangsautc m," + m.user + ".dlogin n ";
sql += " where a.mabd=b.id and e.idduoc=d.id and a.mabd=d.mabd and a.id=" + d_id.ToString() + " and e.id=a.id and f.id=d.sttt and b.mahang=g.id and f.nhomcc=h.id and j.id=b.maloai and k.id=a.id and m.id=k.id_tinhtrang and k.userid=n.id";
dt = m.get_data(sql).Tables[0];
if (dt.Rows.Count != 0)
{
break;
}
}
catch { }
}
if (dt.Rows.Count == 0)
{
MessageBox.Show(lan.Change_language_MessageText("Không có số liệu!"), LibMedi.AccessData.Msg);
return;
}
if (chkXml.Checked)
{
dt.WriteXml("..\\xml\\rptThongtinpu_sautc.xml");
}
dllReportM.frmReport f = new dllReportM.frmReport(m, dt, "rptThongtinpu_sautc.rpt", txtMabn.Text, txtHoten.Text, txtTuoi.Text, txtGioitinh.Text, txtDiachi.Text, "", "", "", "", "");
f.ShowDialog();
if (dt.Select("phanung=1").Length > 0)
{
dllReportM.frmReport f1 = new dllReportM.frmReport(m, dt, "rptTheodoiPhanung_sautc.rpt", txtMabn.Text, txtHoten.Text, txtTuoi.Text, txtGioitinh.Text, txtDiachi.Text, "", "", "", "", "");
f1.ShowDialog();
}
}
private void butIn_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
ds.Tables.Add("Table");
DataRow r = m.getrowbyid(dt, "id=" + d_id);
if (r == null)
{
return;
}
ds.Tables[0].Columns.Add("soluutru");
ds.Tables[0].Columns.Add("ngay");
ds.Tables[0].Columns.Add("dieuduongtruong");
ds.Tables[0].Columns.Add("truongbophan");
ds.Tables[0].Columns.Add("dieuduongphong");
ds.Tables[0].Columns.Add("tungay");
ds.Tables[0].Columns.Add("denngay");
ds.Tables[0].Columns.Add("vaccin");
ds.Tables[0].Columns.Add("soluong");
ds.Tables[0].Columns.Add("losx");
DataRow r1 = ds.Tables[0].NewRow();
r1["soluutru"] = r["soluutru"];
r1["ngay"] = r["ngay"];
r1["dieuduongtruong"] = m.getrowbyid(dtbs, "ma='" + r["madieuduong"].ToString() + "'")["hoten"].ToString();
r1["truongbophan"] = m.getrowbyid(dtbs, "ma='" + r["truongbophan"].ToString() + "'")["hoten"].ToString();
r1["dieuduongphong"] = m.getrowbyid(dtbs, "ma='" + r["dieuduongphong"].ToString() + "'")["hoten"].ToString();
r1["tungay"] = r["tungay"];
r1["denngay"] = r["denngay"];
r1["vaccin"] = m.getrowbyid(dtVaccin, "id='" + r["mabd"].ToString() + "'")["ten"].ToString();
r1["soluong"] = r["soluong"];
r1["losx"] = r["losx"];
ds.Tables[0].Rows.Add(r1);
if (chkXml.Checked)
{
if (!System.IO.Directory.Exists("..\\..\\dataxml"))
{
System.IO.Directory.CreateDirectory("..\\..\\dataxml");
}
ds.WriteXml("..\\..\\dataxml\\rptBBHuyvolovaccin.xml", XmlWriteMode.WriteSchema);
}
dllReportM.frmReport f = new dllReportM.frmReport(m, ds.Tables[0], "rptBBHuyvolovaccin.rpt", "", "", "", "", "", "", "", "", "", "");
f.ShowDialog();
}
private void butIn_Click(object sender, System.EventArgs e)
{
load_grid();
if (ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show(
lan.Change_language_MessageText("Không có số liệu !"), LibMedi.AccessData.Msg);
}
else
{
ds.WriteXml("m_cdvpcls.xml");
dllReportM.frmReport f = new dllReportM.frmReport(m, ds.Tables[0], "m_cdvpcls.rpt", (tu.Text == den.Text)?
lan.Change_language_MessageText("Ngày") + " " + tu.Text:
lan.Change_language_MessageText("Từ ngày") + " " + tu.Text + " " +
lan.Change_language_MessageText("đến") + " " + den.Text, (madoituong.SelectedIndex != -1)?madoituong.Text:"", (tenloai.SelectedIndex != -1)?tenloai.Text:"", (tenkp.SelectedIndex != -1)?tenkp.Text:"", this.Text, "", "", "", "", "");
f.ShowDialog();
}
}
private void butIn_Click(object sender, System.EventArgs e)
{
if (!kiemtra())
{
return;
}
dsxml.Clear();
dsxml.Merge(ds.Tables[0].Select("true", "ten"));
decimal d_tongcong = 0;
foreach (DataRow r in dsxml.Tables[0].Rows)
{
d_tongcong += decimal.Parse(r["sotien"].ToString());
}
string title = (tu.Text == den.Text)?"Ngày " + tu.Text:"Từ ngày " + tu.Text + " đến " + den.Text;
dllReportM.frmReport f = new dllReportM.frmReport(m, dsxml.Tables[0], "d_phieuxuat.rpt", "", tu.Text, "", "", s_tenloai, "Xuất sử dụng", s_tenkho, doiso.doiraso(Convert.ToInt64(d_tongcong).ToString()), "", "");
f.ShowDialog(this);
}
private void butIN_Click(object sender, System.EventArgs e)
{
Cursor = Cursors.WaitCursor;
namsinh = int.Parse(tu.Text.ToString().Substring(6, 4));
ds.Clear();
sql = "select to_char(a.ngay,'yyyymmdd') as ngay,";
sql += " sum(1) as ts,";
sql += " sum(case when a.kham=1 and " + namsinh + "-to_number(b.namsinh)<=6 then 1 else 0 end) as c01,";
sql += " sum(case when a.kham=1 and " + namsinh + "-to_number(b.namsinh)>6 and " + namsinh + "-to_number(b.namsinh)<=14 then 1 else 0 end) as c02,";
sql += " sum(case when a.kham=1 and " + namsinh + "-to_number(b.namsinh)>14 and b.phai=0 then 1 else 0 end) as c03,";
sql += " sum(case when a.kham=1 and " + namsinh + "-to_number(b.namsinh)>14 and b.phai=1 then 1 else 0 end) as c04,";
sql += " sum(case when a.kham=0 and " + namsinh + "-to_number(b.namsinh)<=6 then 1 else 0 end) as c05,";
sql += " sum(case when a.kham=0 and " + namsinh + "-to_number(b.namsinh)>6 and " + namsinh + "-to_number(b.namsinh)<=14 then 1 else 0 end) as c06,";
sql += " sum(case when a.kham=0 and " + namsinh + "-to_number(b.namsinh)>14 and b.phai=0 then 1 else 0 end) as c07,";
sql += " sum(case when a.kham=0 and " + namsinh + "-to_number(b.namsinh)>14 and b.phai=1 then 1 else 0 end) as c08,";
sql += " sum(case when b.matt='" + matt + "' and a.madoituong=1 then 1 else 0 end) as c09,";
sql += " sum(case when b.matt='" + matt + "' and " + namsinh + "-to_number(b.namsinh)<=6 and a.madoituong<>1 and c.mien=0 then 1 else 0 end) as c10,";
sql += " sum(case when b.matt='" + matt + "' and " + namsinh + "-to_number(b.namsinh)>6 and " + namsinh + "-to_number(b.namsinh)<=14 and a.madoituong<>1 and c.mien=0 then 1 else 0 end) as c11,";
sql += " sum(case when b.matt='" + matt + "' and " + namsinh + "-to_number(b.namsinh)<=6 and a.madoituong<>1 and c.mien=1 then 1 else 0 end) as c12,";
sql += " sum(case when b.matt='" + matt + "' and " + namsinh + "-to_number(b.namsinh)>6 and " + namsinh + "-to_number(b.namsinh)<=14 and a.madoituong<>1 and c.mien=1 then 1 else 0 end) as c13,";
sql += " sum(case when b.matt<>'" + matt + "' and a.madoituong=1 then 1 else 0 end) as c14,";
sql += " sum(case when b.matt<>'" + matt + "' and " + namsinh + "-to_number(b.namsinh)<=6 and a.madoituong<>1 and c.mien=0 then 1 else 0 end) as c15,";
sql += " sum(case when b.matt<>'" + matt + "' and " + namsinh + "-to_number(b.namsinh)>6 and " + namsinh + "-to_number(b.namsinh)<=14 and a.madoituong<>1 and c.mien=0 then 1 else 0 end) as c16,";
sql += " sum(case when b.matt<>'" + matt + "' and " + namsinh + "-to_number(b.namsinh)<=6 and a.madoituong<>1 and c.mien=1 then 1 else 0 end) as c17,";
sql += " sum(case when b.matt<>'" + matt + "' and " + namsinh + "-to_number(b.namsinh)>6 and " + namsinh + "-to_number(b.namsinh)<=14 and a.madoituong<>1 and c.mien=1 then 1 else 0 end) as c18";
sql += " from kx_dieutri a,btdbn b,doituong c";
sql += " where a.mabn=b.mabn and a.madoituong=c.madoituong";
sql += " and to_date(a.ngay,'dd/mm/yy') between to_date('" + tu.Text + "','dd/mm/yy') and to_date('" + den.Text + "','dd/mm/yy')";
sql += " group by to_char(a.ngay,'yyyymmdd')";
sql += " order by to_char(a.ngay,'yyyymmdd')";
ds = m.get_data(sql);
Cursor = Cursors.Default;
if (ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), LibMedi.AccessData.Msg);
}
else
{
dllReportM.frmReport f = new dllReportM.frmReport(m, ds, "KHÁM KHÚC XẠ" + "," + ((tu.Text == den.Text)?"Ngày :" + tu.Text:"Từ ngày " + tu.Text + " đến " + den.Text), "rpttkthkx.rpt");
f.ShowDialog();
}
}
private void butIn_Click(object sender, EventArgs e)
{
string sql = "";
sql = "select a.ngay,a.mabn,b.hoten,b.sonha||'-'||b.thon||'-'||e.tenpxa||'-'||d.tenquan||'-'||c.tentt ";
sql += "as diachi,f.ten as dangkt,g.ten as mucdokt,";
sql += "a.chandoan,a.ghichu,b.phai,(case when b.phai=0 then (to_number(to_char(sysdate,'yyyy'))-to_number(b.namsinh)) else 0 end) as tuoinam ";
sql += ",(case when b.phai=1 then (to_number(to_char(sysdate,'yyyy'))-to_number(b.namsinh)) else 0 end) as tuoinu ";
sql += "from " + user + ".khuyettat a, " + user + ".btdbn b," + user + ".btdtt c,";
sql += "" + user + ".btdquan d," + user + ".btdpxa e," + user + ".dmdangkt f," + user + ".dmmucdokt g ";
sql += "where a.mabn=b.mabn and b.matt=c.matt and b.maqu=d.maqu and b.maphuongxa=e.maphuongxa ";
sql += "and a.id_dang=f.id and a.id_mucdo=g.id and a.mabn='" + mabn.Text.Trim() + "'";
DataSet ds = m.get_data(sql);
ds.WriteXml("khuyettat.xml", XmlWriteMode.WriteSchema);
if (ds.Tables[0].Rows.Count > 0)
{
dllReportM.frmReport f = new dllReportM.frmReport(m, ds.Tables[0], "rptKhuyettat.rpt", s_bskham, "", "", "", "", "", "", "", "", "");
f.ShowDialog();
}
}
private void f_In()
{
try
{
if (m_ds.Tables[0].Rows.Count <= 0)
{
MessageBox.Show(this, lan.Change_language_MessageText("Chọn icd cần in"), lan.Change_language_MessageText("Thông báo"), MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
string areport = "m_icd10.rpt";
string angayin = "Ngày " + DateTime.Now.Day.ToString().PadLeft(2, '0') + " tháng " + DateTime.Now.Month.ToString().PadLeft(2, '0') + " năm " + DateTime.Now.Year.ToString();
string aghichu = chkICDBV.Checked?"(Các mã icd do bệnh viện khai)":"";
aghichu = chkAll.Checked?"(Các mã icd do bệnh viện khai + bộ y tế)":aghichu;
aghichu = chkAll_Bo.Checked?"(Các mã icd do bộ y tế cấp)":aghichu;
dllReportM.frmReport f = new dllReportM.frmReport(m, m_ds, angayin, "", aghichu, areport);
f.ShowDialog();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void butInxacnhan_Click(object sender, EventArgs e)
{
DataSet dsGiayxn = new DataSet();
string tiensu = "";
string user = m.user;
string mmyy = m.mmyy(s_ngay);
sql = "select a.mabn,a.hoten,case when a.ngaysinh is null then a.namsinh else to_char(a.ngaysinh,'dd/mm/yyyy') end ngaysinh,case when a.phai=0 then '" + lan.Change_language_MessageText("Nam") + "' else '" + lan.Change_language_MessageText("Nữ") + "' end phai, a.cmnd,a.sonha,a.thon, b.tenpxa, ";
sql += " c.tenquan, d.tentt,e.tennn || (case when trim(a.cholam)='' then '' else ' - '||a.cholam end) nghenghiep ,to_char(f.ngay,'dd/mm/yyyy hh24:mi') ngayvaopk, ";
sql += " to_char(f.ngay,'dd/mm/yyyy hh24:mi') ngayrapk, to_char(fff.ngay,'dd/mm/yyyy hh24:mi') ngayvaocc,case when fff.ngayrv is null then to_char(Now(),'dd/mm/yyyy hh24:mi') else to_char(fff.ngayrv,'dd/mm/yyyy hh24:mi') end ngayracc, ";
sql += " to_char(ff.ngay,'dd/mm/yyyy hh24:mi') ngayvaonoitru,to_char(ffa.ngay,'dd/mm/yyyy hh24:mi') ngayranoitru, ";
sql += " 0 noitru,'' tiensu,f.chandoan chandoanpk, ";
sql += " ffa.chandoan chandoannoitru,fff.chandoan chandoancc,g.kedon,g.tinhtrangsk,i.tenbv chuyenvien ";
sql += " from " + user + ".btdbn a left join " + user + ".btdpxa b on a.maphuongxa=b.maphuongxa left join " + user + ".btdquan c on a.maqu=c.maqu left join " + user + ".btdtt d on a.matt=d.matt ";
sql += " left join " + user + ".btdnn e on a.mann=e.mann ";
sql += " left join " + user + mmyy + ".benhanpk f on a.mabn=f.mabn and f.maql=" + d_maql;
sql += " left join " + user + ".benhandt ff on a.mabn=ff.mabn and ff.mavaovien=" + d_mavaovien;
sql += " left join " + user + ".xuatvien ffa on ff.maql=ffa.maql ";
sql += " left join " + user + mmyy + ".benhancc fff on a.mabn=fff.mabn and fff.maql=" + d_maql;
sql += " left join " + user + ".blgd_ra g on a.mabn=g.mabn and g.mavaovien=" + d_mavaovien;
sql += " left join " + user + ".chuyenvien h on h.maql=" + d_maql;
sql += " left join " + user + ".tenvien i on h.mabv=i.mabv ";
sql += " where a.mabn='" + s_mabn + "' ";
dsGiayxn = m.get_data(sql);
if (dsGiayxn.Tables[0].Rows.Count == 0)
{
MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), s_msg);
return;
}
sql = "select noidung from " + user + ".theodoitsu where mabn='" + s_mabn + "'";
foreach (DataRow r in m.get_data(sql).Tables[0].Rows)
{
tiensu += r["noidung"].ToString() + "; ";
}
dsGiayxn.Tables[0].Rows[0]["tiensu"] = tiensu;
dsGiayxn.WriteXml("..\\xml\\rptGiayxacnhanBLGD.xml", XmlWriteMode.WriteSchema);
dllReportM.frmReport f = new dllReportM.frmReport(m, dsGiayxn.Tables[0], "rptGiayxacnhanBLGD.rpt", "", "", "", "", "", "", "", "", "", "");
f.ShowDialog();
}
private void butin_Click(object sender, EventArgs e)
{
int i_buoi = -1;
if (cboBuoi.SelectedIndex == 0)
{
i_buoi = 0;
}
else if (cboBuoi.SelectedIndex == 1)
{
i_buoi = 1;
}
string s_sql = "";
if (i_buoi == 0 || i_buoi == -1)
{
s_sql += s_sql == "" ? "" : " union all ";
s_sql += " select to_char(ngay,'dd')||'s' as ngay,nhietdo_sang as nhietdo,to_number(to_char(ngay,'dd')||'0') as stt from " + user + ".theodoinhietdo where id_phongthuchiencls=" +
cboDonvi.SelectedValue.ToString() + " and to_char(ngay,'mmyyyy')='" + cmbThang.Text.PadLeft(2, '0') + numNam.Value.ToString() + "'";
}
if (i_buoi == 1 || i_buoi == -1)
{
s_sql += s_sql == "" ? "" : " union all ";
s_sql += " select to_char(ngay,'dd')||'c' as ngay,nhietdo_chieu as nhietdo,to_number(to_char(ngay,'dd')||'1') as stt from " + user + ".theodoinhietdo where id_phongthuchiencls=" +
cboDonvi.SelectedValue.ToString() + " and to_char(ngay,'mmyyyy')='" + cmbThang.Text.PadLeft(2, '0') + numNam.Value.ToString() + "'";
}
string sql = "select ngay,nhietdo from (" + s_sql + ") order by stt";
DataTable dt = m.get_data(sql).Tables[0];
if (chkXml.Checked)
{
dt.DataSet.Copy().WriteXml("..//..//dataxml//theodoinhietdo.xml", XmlWriteMode.WriteSchema);
}
if (dt.Rows.Count > 0)
{
dllReportM.frmReport f = new dllReportM.frmReport(m, dt, "rptTheodoinhietdo.rpt", "", "", "", "", "", "", "", "", "", "");
f.ShowDialog();
}
}
private void bieu_11(bool prn)
{
ds = e.bieu_11_khoa(s_tu, s_tu1, s_den, s_table, s_makp, phatsinh.Checked, time.Checked);
string tenfile = (m.Mabv.Substring(0, 3) == "701")?"bieu_11_18.rpt":"bieu_11.rpt";
if (m.getrowbyid(ds.Tables[0], "c01+c02+c03+c04+c05+c06+c07+c08+c09+c10+c11+c12>0") == null)
{
MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), LibMedi.AccessData.Msg);
}
else
{
if (prn)
{
p.Printer(m, ds, tenfile, title.ToUpper(), 2);
}
else
{
dllReportM.frmReport f = new dllReportM.frmReport(m, ds, title.ToUpper(), tenfile);
f.ShowDialog(this);
}
}
}
private void butIn_Click(object sender, System.EventArgs e)
{
try
{
if (i_loaiba == 1 && chkDoituong.Checked == false)
{
ds = exp.upd_ththbn(tu.Text, den.Text, (makp.SelectedIndex == -1)?"":makp.SelectedValue.ToString(), time.Checked);
}
else if (i_loaiba == 1 && chkDoituong.Checked == true)
{
ds = exp.upd_ththbn_doituong(tu.Text, den.Text, (makp.SelectedIndex == -1) ? "" : makp.SelectedValue.ToString(), time.Checked);
}
else
{
ds = exp.upd_ththbn_ngtru(tu.Text, den.Text, (makp.SelectedIndex == -1)?"":makp.SelectedValue.ToString(), i_loaiba, time.Checked);
}
if (ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), AccessData.Msg);
return;
}
else
{
string title = "Từ ngày " + tu.Text + " Đến ngày " + den.Text;
if (tu.Text == den.Text)
{
title = "Ngày " + tu.Text;
}
if (System.IO.Directory.Exists("..\\..\\dataxml") == false)
{
System.IO.Directory.CreateDirectory("..\\..\\dataxml");
}
ds.WriteXml("..\\..\\dataxml\\ththbn.xml", XmlWriteMode.WriteSchema);
dllReportM.frmReport f = new dllReportM.frmReport(m, ds, title, (i_loaiba == 1) ? ((chkDoituong.Checked == false) ? "rptThthbn.rpt" : "rptThthbn_doituong.rpt") : "rptThthbn_ngtr.rpt");
f.ShowDialog();
}
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
}
private void butIn_Click(object sender, System.EventArgs e)
{
taotable();
//foreach (DataRow r in ds.Tables[0].Rows) r["ten"]=m.holot_ten(r["ten"].ToString());
if (chkxml.Checked)
{
if (!System.IO.Directory.Exists("..//xml"))
{
System.IO.Directory.CreateDirectory("..//xml");
}
ds.WriteXml("..//xml//baoan.xml", XmlWriteMode.WriteSchema);
}
if (ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show(
lan.Change_language_MessageText("Không có số liệu !"), LibMedi.AccessData.Msg);
tu.Focus();
return;
}
dllReportM.frmReport f = new dllReportM.frmReport(m, ds, "rptBaoan.rpt", (tu.Text == den.Text) ? "Ngày :" + tu.Text : "Từ ngày :" + tu.Text + " đến :" + den.Text, makp.Text, (madoituong.SelectedIndex != -1)?madoituong.Text:"", s_tenphieu, tennv.Text, "");
f.ShowDialog();
}
private void f_print(string s_tenrp)
{
DataSet dsinhc = new DataSet();
string xxx = s_user + m.mmyy(s_ngay);
string sql = " select a.id,a.mabn,to_char(a.ngay,'dd/mm/yyyy hh24:mi') ngay,a.chandoan,a.icd10,a.tv_canthietchupct,a.tv_tacdungphu,a.hb_tiensudiung,a.hb_tinhtrangmatnuoc,";
sql += " a.hb_roiloanhd,a.hb_suythan,a.hb_thaiky,a.hb_dautuy,a.hb_benhlytm,a.hb_tinhtrangntr,a.mach,a.nhietdo,a.huyetap,a.nhiptho,a.ketquabun,";
sql += " a.ketquacreatinin,b.iddmbophan,c.ten,d.hoten,d.namsinh,d.phai ,f.ten tencls,g.ten tenloaicls, ";
sql += " f.id_loai idloai,(h.sonha ||' '||h.thon||' '||i.tenpxa||' '||l.tenquan||' '||m.tentt) diachi,n.nha sodtnha,n.coquan sodtcq,n.didong,n1.tennn,n2.dantoc,h.cholam,to_char(n3.ngay,'dd/mm/yyyy') ngayxn ";
sql += " from " + s_user + ".kbct_canquangll a left join " + s_user + ".kbct_canquangct b on a.id=b.id left join " + s_user + ".dmbophan_hoichan c on b.iddmbophan=c.id ";
sql += " left join " + s_user + ".btdbn d on a.mabn=d.mabn";
sql += " left join " + xxx + ".v_chidinh e on a.id=e.id left join " + s_user + ".v_giavp f on e.mavp=f.id left join " + s_user + ".v_loaivp g on f.id_loai=g.id ";
sql += " inner join " + s_user + ".btdbn h on a.mabn=h.mabn inner join " + s_user + ".btdpxa i on h.maphuongxa=i.maphuongxa inner join " + s_user + ".btdquan l on h.maqu=l.maqu ";
sql += " inner join " + s_user + ".btdtt m on h.matt=m.matt left join " + s_user + ".dienthoai n on a.mabn=n.mabn left join " + s_user + ".btdnn n1 on h.mann=n1.mann left join " + s_user + ".btddt n2 on h.madantoc=n2.madantoc";
sql += " left join " + xxx + ".xn_phieu n3 on a.mabn=n3.mabn and a.maql=n3.maql ";
sql += " where a.id in(" + decimal.Parse(cbLoaicls.SelectedValue.ToString()) + ")";
dsinhc = m.get_data(sql);/// m.get_data_mmyy(sql, s_ngay, s_ngay, 31);
if (dsinhc.Tables[0].Rows.Count > 0)
{
if (!System.IO.Directory.Exists("..//xml"))
{
System.IO.Directory.CreateDirectory("..//xml");
}
dsinhc.WriteXml("..//xml//rptCTscannercq.xml", XmlWriteMode.WriteSchema);
dllReportM.frmReport f = new dllReportM.frmReport(m, dsinhc, s_ngay.Substring(0, 10), s_tenrp);
f.LayDauVanTay = true;
f.MaBenhNhan = s_mabn;
f.LoaiChungTu = (int)LibMedi.LoaiChungTuCanKyTen.PhieuKhamBenhCTXQuang;
f.UserID = i_userid;
f.NgayKyGiay = s_ngay;
f.ShowDialog();
}
else
{
MessageBox.Show(lan.Change_language_MessageText(" Chưa có dữ liệu."), "Medisoft THIS", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
}
private void butIn_Click(object sender, System.EventArgs e)
{
string s_makp = "";
if (makp.SelectedItems.Count > 0)
{
for (int i = 0; i < makp.Items.Count; i++)
{
if (makp.GetItemChecked(i))
{
s_makp += dtkp.Rows[i]["makp"].ToString() + ",";
}
}
}
s_makp = (s_makp != "")?s_makp.Substring(0, s_makp.Length - 1):"";
ds = exp.get_btpkham(tu.Text, den.Text, s_makp, time.Checked);
if (ds.Tables[0].Rows.Count > 0)
{
m.delrec(ds.Tables[0], "loai=1");
DataTable dt = exp.get_btpkham_nguoi(tu.Text, den.Text, s_makp, time.Checked).Tables[0];
int i01, i02, i03, i04, i05, i06;
i01 = dt.Select("c01>0").Length;
i02 = dt.Select("c02>0").Length;
i03 = dt.Select("c03>0").Length;
i04 = dt.Select("c04>0").Length;
i05 = dt.Select("c05>0").Length;
i06 = dt.Select("c06>0").Length;
dllReportM.frmReport f = new dllReportM.frmReport(m, ds, (tu.Text == den.Text)?
lan.Change_language_MessageText("Ngày") + " " + tu.Text:
lan.Change_language_MessageText("Từ ngày") + " " + tu.Text + " " +
lan.Change_language_MessageText("đến") + " " + den.Text, "rptbtpkham.rpt", i01, i02, i03, i04, i05, i06, 0, 0, 0, 0);
f.ShowDialog();
}
else
{
MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), AccessData.Msg);
}
}
private void butIN_Click(object sender, System.EventArgs e)
{
if (lbSTT.Text == "0")
{
MessageBox.Show(lan.Change_language_MessageText("Vui lòng bấm nút lấy số thứ tự !"));
butCapSTT.Focus();
return;
}
string asql = "select " + lbSTT.Text + " as stt from dual ";
DataSet ads = m.get_data(asql);
ads.WriteXml("..//..//dataxml//capstt.xml");
if (chkXem.Checked)
{
dllReportM.frmReport f = new dllReportM.frmReport(ads, "", "", "capstt.rpt");
f.ShowDialog(this);
}
else
{
print.Printer1(m, ads, lbSTT.Text, "capstt.rpt", 0);
}
lbSTT.Text = "0";
}
private void load_grid()
{
if (tu.Text == "" || den.Text == "")
{
if (tu.Text == "")
{
tu.Focus();
}
else
{
den.Focus();
}
return;
}
ds.Clear();
sql = "select a.idvung,count(*) as so ";
sql += " from xxx.cls_ketqua a inner join " + user + ".btdbn b on a.mabn=b.mabn ";
sql += " inner join " + user + ".cls_loai c on a.loai=c.id ";
sql += " inner join " + user + ".cls_may d on a.idmay=d.id ";
sql += " where " + m.for_ngay("a.ngay", stime) + " between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")";
if (loai.SelectedIndex != -1)
{
sql += " and a.loai=" + int.Parse(loai.SelectedValue.ToString());
}
if (may.SelectedIndex != -1)
{
sql += " and a.idmay=" + int.Parse(may.SelectedValue.ToString());
}
sql += " group by a.idvung";
DataRow r1, r2, r3;
DataRow [] dr;
foreach (DataRow r in m.get_data_mmyy(sql, tu.Text, den.Text, false).Tables[0].Select("true", "idvung"))
{
r1 = m.getrowbyid(ds.Tables[0], "id=" + int.Parse(r["idvung"].ToString()));
if (r1 == null)
{
r2 = ds.Tables[0].NewRow();
r2["id"] = r["idvung"].ToString();
r3 = m.getrowbyid(dt, "id=" + int.Parse(r["idvung"].ToString()));
if (r3 != null)
{
r2["ten"] = r3["ten"].ToString();
}
r2["so"] = r["so"].ToString();
ds.Tables[0].Rows.Add(r2);
}
else
{
dr = ds.Tables[0].Select("id=" + int.Parse(r["idvung"].ToString()));
if (dr.Length > 0)
{
dr[0]["so"] = decimal.Parse(dr[0]["so"].ToString()) + decimal.Parse(r["so"].ToString());
}
}
}
if (ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show(
lan.Change_language_MessageText("Không có số liệu !"), LibMedi.AccessData.Msg);
}
else
{
dllReportM.frmReport f = new dllReportM.frmReport(m, ds, "rpttksocavung.rpt", (tu.Text == den.Text) ? "Ngày " + tu.Text : "Từ ngày " + tu.Text + " đến " + den.Text, loai.Text, may.Text, "", "", "");
f.ShowDialog();
}
}
private void butIN_Click(object sender, System.EventArgs e)
{
Cursor = Cursors.WaitCursor;
s_cls = "";
for (int i = 0; i < loai.Items.Count; i++)
{
if (loai.GetItemChecked(i))
{
s_cls += dt.Rows[i]["id"].ToString() + ",";
}
}
ds.Clear();
sql = "select a.loai,a.ma,";
sql += " sum(case when d.id is null then 1 else case when g.mat=0 then 1 else d.mp+d.mt end end) as c01,";
sql += " sum(case when a.loaibn=0 then case when d.id is null then 1 else case when g.mat=0 then 1 else d.mp+d.mt end end else 0 end) as c02,";
sql += " sum(case when b.canquang is not null and b.canquang=1 then case when d.id is null then 1 else case when g.mat=0 then 1 else d.mp+d.mt end end else 0 end) as c03,";
sql += " sum(case when a.loaibn=0 and b.canquang is not null and b.canquang=1 then case when d.id is null then 1 else case when g.mat=0 then 1 else d.mp+d.mt end end else 0 end) as c04,";
sql += " sum(case when b.gayme is not null and b.gayme=1 then case when d.id is null then 1 else case when g.mat=0 then 1 else d.mp+d.mt end end else 0 end) as c05,";
sql += " sum(case when a.loaibn=0 and b.gayme is not null and b.gayme=1 then case when d.id is null then 1 else case when g.mat=0 then 1 else d.mp+d.mt end end else 0 end) as c06";
sql += " from xxx.cls_thuchien a left join xxx.cls_motact b on a.id=b.id ";
sql += " left join xxx.cls_mat d on a.id=d.id ";
sql += " inner join " + user + ".cls_loai g on a.loai=g.id";
sql += " where " + m.for_ngay("a.ngay", stime) + " between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")";
if (s_cls != "")
{
sql += " and a.loai in (" + s_cls.Substring(0, s_cls.Length - 1) + ")";
}
sql += " group by a.loai,a.ma";
sql += " order by a.loai,a.ma";
DataRow r1, r2, r3, r4;
foreach (DataRow r in m.get_data_mmyy(sql, tu.Text, den.Text, false).Tables[0].Rows)
{
sql = "loai=" + int.Parse(r["loai"].ToString()) + " and mabn='" + r["ma"].ToString() + "'";
r1 = m.getrowbyid(ds.Tables[0], sql);
if (r1 == null)
{
r3 = m.getrowbyid(dt, "id=" + int.Parse(r["loai"].ToString()));
r4 = m.getrowbyid(dtnd, "ma='" + r["ma"].ToString() + "'");
if (r3 != null && r4 != null)
{
r2 = ds.Tables[0].NewRow();
r2["loai"] = r["loai"].ToString();
r2["mabn"] = r["ma"].ToString();
r2["hoten"] = r3["ten"].ToString();
r2["tenkp"] = r4["ten"].ToString();
r2["ts"] = 1;
r2["c01"] = r["c01"].ToString();
r2["c02"] = r["c02"].ToString();
r2["c03"] = 0;
r2["c04"] = 0;
ds.Tables[0].Rows.Add(r2);
if (decimal.Parse(r["c03"].ToString()) + decimal.Parse(r["c04"].ToString()) > 0)
{
r2 = ds.Tables[0].NewRow();
r2["loai"] = r["loai"].ToString();
r2["mabn"] = r["ma"].ToString();
r2["hoten"] = r3["ten"].ToString();
r2["tenkp"] = "CÓ CẢN QUANG";
r2["ts"] = 2;
r2["c01"] = 0;
r2["c02"] = 0;
r2["c03"] = r["c03"].ToString();
r2["c04"] = r["c04"].ToString();
ds.Tables[0].Rows.Add(r2);
}
if (decimal.Parse(r["c05"].ToString()) + decimal.Parse(r["c06"].ToString()) > 0)
{
r2 = ds.Tables[0].NewRow();
r2["loai"] = r["loai"].ToString();
r2["mabn"] = r["ma"].ToString();
r2["hoten"] = r3["ten"].ToString();
r2["tenkp"] = "CÓ GÂY MÊ";
r2["ts"] = 3;
r2["c01"] = 0;
r2["c02"] = 0;
r2["c03"] = r["c05"].ToString();
r2["c04"] = r["c06"].ToString();
ds.Tables[0].Rows.Add(r2);
}
}
}
}
Cursor = Cursors.Default;
if (ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), LibMedi.AccessData.Msg);
}
else
{
dllReportM.frmReport f = new dllReportM.frmReport(m, ds, ((tu.Text == den.Text)?"Ngày :" + tu.Text:"Từ ngày " + tu.Text + " đến " + den.Text), "rpttkthcls_n.rpt");
f.ShowDialog();
}
}
private void butIn_Click(object sender, System.EventArgs e)
{
title = "";
s_makp = "";
s_mapm = "";
string s_tenkp = "", s_tenpm = "";
for (int i = 0; i < makp.Items.Count; i++)
{
if (makp.GetItemChecked(i))
{
s_makp += dtkp.Rows[i]["makp"].ToString() + ",";
s_tenkp += dtkp.Rows[i]["tenkp"].ToString() + ",";
}
}
for (int i = 0; i < mapm.Items.Count; i++)
{
if (mapm.GetItemChecked(i))
{
s_mapm += dtpm.Rows[i]["ma"].ToString() + ",";
s_tenpm += dtpm.Rows[i]["ten"].ToString() + ",";
}
}
title = (tu.Text == den.Text)?
lan.Change_language_MessageText("Ngày") + " " + tu.Text:
lan.Change_language_MessageText("Từ ngày") + " " + tu.Text + " " +
lan.Change_language_MessageText("đến") + " " + den.Text;
if (s_tenkp != "")
{
title = title + " " +
lan.Change_language_MessageText("KHOA") + " " + s_tenkp;
}
if (s_tenpm != "")
{
title = title + " " +
lan.Change_language_MessageText("PHÒNG MỖ") + " " + s_tenpm;
}
if (loaipt.SelectedIndex != -1)
{
title = title + " " + loaipt.Text;
}
sql = " select p.id,b.hoten,b.mabn,b.phai,b.namsinh,to_char(p.ngay,'dd/mm/yyyy hh24:mi') as ngay,tenpt,pp.ten,pt.dacbiet,pt.loai1,pt.loai2,pt.loai3,pt.loaipt,lpt.ten as tenloaipt, ";
sql += " mc.hoten as hoten1,mc.hoten as hotenfull1,p1.hoten as hoten2,p1.hoten as hotenfull2,p2.hoten as hoten22,p2.hoten as hotenfull22,bsgm.hoten as hoten3," +
"bsgm.hoten as hotenfull3,ktvgm.hoten as hoten4,ktvgm.hoten as hotenfull4,hs.hoten as hoten5,hs.hoten as hotenfull5,dcu.hoten as hoten6,dcu.hoten as hotenfull6,p.somat,p.molaimien,";
sql += "vp.gia_th,vp.gia_cs,vp.gia_bh,vp.gia_dv,vp.gia_nn";
sql += ", p.chandoant, p.maicdt, p.chandoans, p.maicds, case when bh1.sothe is null then bh2.sothe else bh1.sothe end sothe ";
sql += ",b.cholam, b.thon, b.sonha, tt.tentt, qu.tenquan, px.tenpxa ";
sql += " from " + user + ".btdbn b inner join xxx.pttt p on b.mabn=p.mabn inner join " + user + ".dmpttt pt on p.mapt=pt.mapt ";
sql += " inner join " + user + ".dmbs mc on p.ptv=mc.ma left join " + user + ".dmbs p1 on p.phu1=p1.ma ";
sql += " left join " + user + ".dmbs p2 on p.phu2=p2.ma left join " + user + ".dmbs bsgm on p.bsgayme=bsgm.ma ";
sql += " left join " + user + ".dmbs ktvgm on p.ktvgayme=ktvgm.ma left join " + user + ".dmbs hs on p.hoisuc=hs.ma ";
sql += " left join " + user + ".dmbs dcu on p.dungcu=dcu.ma inner join " + user + ".dmmete pp on p.phuongphap=pp.ma ";
sql += " inner join " + user + ".loaipttt lpt on pt.loaipt=lpt.ma ";
sql += " left join " + user + ".v_giavp vp on pt.mavp=vp.id";
sql += " left join " + user + ".bhyt bh1 on p.maql=bh1.maql and (bh1.sudung is null or bh1.sudung=1)";
sql += " left join xxx.bhyt bh2 on p.maql=bh2.maql";
sql += " inner join " + user + ".btdtt tt on b.matt=tt.matt ";
sql += " inner join " + user + ".btdquan qu on b.maqu=qu.maqu ";
sql += " inner join " + user + ".btdpxa px on b.maphuongxa=px.maphuongxa ";
sql += " where ";
sql += m.for_ngay("p.ngay", "'dd/mm/yyyy'") + " between to_date('" + tu.Text + "','dd/mm/yyyy') and to_date('" + den.Text + "','dd/mm/yyyy') ";
string s;
if (s_makp != "")
{
s = s_makp.Replace(",", "','");
sql += " and p.makp in ('" + s.Substring(0, s.Length - 3) + "')";
}
if (s_mapm != "")
{
s = s_mapm.Replace(",", "','");
sql += " and p.mapmo in ('" + s.Substring(0, s.Length - 3) + "')";
}
if (loaipt.SelectedIndex != -1)
{
sql += " and substr(p.mapt,1,1)='" + loaipt.Text.Substring(0, 1) + "'";
}
if (chkmonoisoi.Checked)
{
sql += " and p.noisoi = 1";
}
if (chkmolai.Checked)
{
sql += " and p.molaimien = 1";
}
sql += " order by p.makp,p.mapmo,p.ngay";
ds = m.get_data_mmyy(sql, tu.Text, den.Text, false);
//
//binh 280308
string sql1 = "select a.id, a.mabn, a.maql, b.loai, b.mabs, c.ten, d.hoten ";
sql1 += " from xxx.pttt a, medibv.pttt_bs b, medibv.pttt_loai c, medibv.dmbs d ";
sql1 += " where a.id=b.id and b.loai=c.id and b.mabs=d.ma";
if (chkmonoisoi.Checked)
{
sql1 += " and a.noisoi = 1";
}
if (chkmolai.Checked)
{
sql1 += " and a.molaimien = 1";
}
sql1 += " and to_date(to_char(a.ngay, 'dd/mm/yyyy'),'dd/mm/yyyy') between to_date('" + tu.Text + "','dd/mm/yyyy') and to_date('" + den.Text + "','dd/mm/yyyy') ";
DataSet ds1 = m.get_data_mmyy(sql1, tu.Text, den.Text, false);
//
sql = " select a.hoten,b.ten from " + user + ".dmbs a," + user + ".dmchucdanh b where a.chucdanh=b.id order by ma";
DataTable dtchucdanh = new DataTable();
dtchucdanh = m.get_data(sql).Tables[0];
ds.Tables[0].Columns.Add("chucdanh1");
ds.Tables[0].Columns.Add("chucdanh2");
ds.Tables[0].Columns.Add("chucdanh22");
ds.Tables[0].Columns.Add("chucdanh3");
ds.Tables[0].Columns.Add("chucdanh4");
ds.Tables[0].Columns.Add("chucdanh5");
ds.Tables[0].Columns.Add("chucdanh6");
foreach (DataRow r in ds.Tables[0].Rows)
{
foreach (DataRow r0 in dtchucdanh.Rows)
{
if (r["hoten1"].ToString() == r0["hoten"].ToString())
{
r["chucdanh1"] = r0["ten"].ToString();
}
if (r["hoten2"].ToString() == r0["hoten"].ToString())
{
r["chucdanh2"] = r0["ten"].ToString();
}
if (r["hoten22"].ToString() == r0["hoten"].ToString())
{
r["chucdanh22"] = r0["ten"].ToString();
}
if (r["hoten3"].ToString() == r0["hoten"].ToString())
{
r["chucdanh3"] = r0["ten"].ToString();
}
if (r["hoten4"].ToString() == r0["hoten"].ToString())
{
r["chucdanh4"] = r0["ten"].ToString();
}
if (r["hoten5"].ToString() == r0["hoten"].ToString())
{
r["chucdanh5"] = r0["ten"].ToString();
}
if (r["hoten6"].ToString() == r0["hoten"].ToString())
{
r["chucdanh6"] = r0["ten"].ToString();
}
}
r["hoten1"] = m.holot_ten(r["hoten1"].ToString());
r["hoten2"] = m.holot_ten(r["hoten2"].ToString());
r["hoten22"] = m.holot_ten(r["hoten22"].ToString());
r["hoten3"] = m.holot_ten(r["hoten3"].ToString());
r["hoten4"] = m.holot_ten(r["hoten4"].ToString());
r["hoten5"] = m.holot_ten(r["hoten5"].ToString());
r["hoten6"] = m.holot_ten(r["hoten6"].ToString());
//binh280308
foreach (DataRow r1 in ds1.Tables[0].Select("id=" + r["id"].ToString()))
{
switch (r1["loai"].ToString())
{
case "1":
r["hoten1"] += ", " + m.holot_ten(r1["hoten"].ToString()); //mo chinh
break;
case "2":
r["hoten2"] += ", " + m.holot_ten(r1["hoten"].ToString()); //phu vt
break;
case "3":
r["hoten22"] += ", " + m.holot_ten(r1["hoten"].ToString()); //phu vn
break;
case "4":
r["hoten3"] += ", " + m.holot_ten(r1["hoten"].ToString()); //BS GM
break;
case "5":
r["hoten4"] += ", " + m.holot_ten(r1["hoten"].ToString()); //KTV GM
break;
case "6":
r["hoten5"] += ", " + m.holot_ten(r1["hoten"].ToString()); //hoi suc
break;
case "7":
r["hoten6"] += ", " + m.holot_ten(r1["hoten"].ToString()); //dung cu
break;
default:
break;
}
}
//end binh280308
}
ds.Dispose();
string tenfile = (m.Mabv_so == 701424)?"rptDspttt_mat.rpt":"rptDspttt.rpt";
if (chkXML.Checked)
{
if (!System.IO.Directory.Exists("..\\xml"))
{
System.IO.Directory.CreateDirectory("..\\xml");
}
ds.WriteXml("..\\xml\\dspttt.xml", XmlWriteMode.WriteSchema);
}
if (ds.Tables[0].Rows.Count != 0)
{
dllReportM.frmReport f = new dllReportM.frmReport(m, ds, title, tenfile, false);
f.ShowDialog();
}
else
{
MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), AccessData.Msg);
}
}
private void butIN_Click(object sender, System.EventArgs e)
{
Cursor = Cursors.WaitCursor;
DataRow r1, r2, r3, r4;
DataRow [] dr;
ds.Clear();
sql = "select 0 as loai,b.ma,a.makp,sum(case when d.id is null then 1 else case when g.mat=0 then 1 else d.mp+d.mt end end) as so ";
sql += "from xxx.cls_thuchien a inner join " + user + ".dmbs b on a.bsth=b.ma ";
sql += " inner join " + user + ".btdkp_bv c on a.makp=c.makp ";
sql += " left join xxx.cls_mat d on a.id=d.id ";
sql += " inner join " + user + ".cls_loai g on a.loai=g.id";
sql += " where " + m.for_ngay("a.ngay", stime) + " between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")";
sql += " and a.loai=" + int.Parse(loai.SelectedValue.ToString());
sql += " group by b.ma,a.makp";
sql += " union all ";
sql += " select 1 as loai,b.ma,a.makp,count(*) as so ";
sql += "from xxx.cls_thuchien a inner join " + user + ".dmbs b on a.ytaphu=b.ma ";
sql += " inner join " + user + ".btdkp_bv c on a.makp=c.makp ";
sql += " inner join " + user + ".cls_loai g on a.loai=g.id";
sql += " where " + m.for_ngay("a.ngay", stime) + " between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")";
sql += " and a.loai=" + int.Parse(loai.SelectedValue.ToString());
sql += " group by b.ma,a.makp";
foreach (DataRow r in m.get_data_mmyy(sql, tu.Text, den.Text, false).Tables[0].Rows)
{
r1 = m.getrowbyid(dtbs, "ma='" + r["ma"].ToString() + "'");
if (r1 != null)
{
sql = "loai=" + int.Parse(r["loai"].ToString()) + " and mabn='" + r["ma"].ToString() + "'";
r2 = m.getrowbyid(ds.Tables[0], sql);
if (r2 == null)
{
r3 = ds.Tables[0].NewRow();
r3["loai"] = r["loai"].ToString();
r3["mabn"] = r["ma"].ToString();
r3["hoten"] = r1["hoten"].ToString();
r3["c01"] = (r["loai"].ToString() == "0")?decimal.Parse(r["so"].ToString()):0;
r3["c02"] = (r["loai"].ToString() == "1")?decimal.Parse(r["so"].ToString()):0;
r4 = m.getrowbyid(dtkp, "makp='" + r["makp"].ToString() + "'");
if (r4 != null)
{
r3["tenkp"] = r4["tenkp"].ToString();
}
ds.Tables[0].Rows.Add(r3);
}
else
{
dr = ds.Tables[0].Select(sql);
if (dr.Length > 0)
{
dr[0]["c01"] = decimal.Parse(dr[0]["c01"].ToString()) + ((r["loai"].ToString() == "0")?decimal.Parse(r["so"].ToString()):0);
dr[0]["c02"] = decimal.Parse(dr[0]["c02"].ToString()) + ((r["loai"].ToString() == "1")?decimal.Parse(r["so"].ToString()):0);
}
}
}
}
Cursor = Cursors.Default;
if (ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), LibMedi.AccessData.Msg);
}
else
{
dllReportM.frmReport f = new dllReportM.frmReport(m, ds, loai.Text.Trim().ToUpper() + "," + ((tu.Text == den.Text)?"Ngày :" + tu.Text:"Từ ngày " + tu.Text + " đến " + den.Text), "rpttkbscls.rpt");
f.ShowDialog();
}
}
private void butIn_Click(object sender, EventArgs e)
{
dsxml.Clear();
if (cmbten.SelectedIndex == -1)
{
return;
}
string s = "'", xxx = user + s_mmyy, _ngay = m.ngayhienhanh_server.Substring(0, 10);
long l_id = 0;
if (chkAll.Checked)
{
s = "','";
}
else if (chkAlldv.Checked)
{
foreach (DataRow r in dt2.Rows)
{
s += r["mabn"].ToString() + "','";
}
}
else if (dt2.Rows.Count > 0)
{
s += dataGrid2[dataGrid2.CurrentCell.RowNumber, 1].ToString() + "','";
}
if (s.Length > 1)
{
s = s.Substring(0, s.Length - 2);
sql = "select f.ten as donvi,a.mabn,a.hoten,a.namsinh,a.phai,a.iddonvi";
sql += " from " + xxx + ".ct_btdbn a inner join " + user + ".ct_donvi f on a.iddonvi=f.id ";
if (chkAll.Checked)
{
sql += " where f.iddoan=" + l_doan;
}
else
{
sql += " where a.mabn in (" + s + ")";
}
sql += " order by f.stt,a.stt";
dtmuc = m.get_data("select * from " + user + ".ct_muc where iddoan=" + l_doan + " order by stt").Tables[0];
dtmucct = m.get_data("select b.*,c.ten from " + user + ".ct_muc a," + user + ".ct_mucct b," + user + ".ct_giavp c where a.id=b.id and b.mavp=c.id and a.iddoan=" + l_doan + " order by stt").Tables[0];
foreach (DataRow r in m.get_data(sql).Tables[0].Rows)
{
barcode.Text = r["mabn"].ToString();
barcode.Update();
barcode.Picture.Save("..//..//..//xml//barcode.bmp", System.Drawing.Imaging.ImageFormat.Bmp);
fstr = new FileStream("..//..//..//xml//barcode.bmp", FileMode.Open, FileAccess.Read);
imagemabn = new byte[fstr.Length];
fstr.Read(imagemabn, 0, System.Convert.ToInt32(fstr.Length));
fstr.Dispose();
fstr.Close();
updrec(cmbten.Text.ToUpper(), r["donvi"].ToString(), r["mabn"].ToString(), r["hoten"].ToString(), r["namsinh"].ToString(), int.Parse(r["phai"].ToString()));
//
if (m.get_data("select id from " + xxx + ".ct_ketqua where mabn='" + r["mabn"].ToString() + "' and iddonvi=" + long.Parse(r["iddonvi"].ToString())).Tables[0].Rows.Count == 0)
{
l_id = m.get_id_ct_ketqua;
m.upd_ct_ketqua(s_mmyy, l_id, long.Parse(r["iddonvi"].ToString()), r["mabn"].ToString(), _ngay, "", i_loai, "", i_userid);
foreach (DataRow r1 in dsxml.Tables[0].Select("mabn='" + r["mabn"].ToString() + "'", "stt"))
{
m.upd_ct_chitiet(s_mmyy, l_id, int.Parse(r1["stt"].ToString()), long.Parse(r1["mavp"].ToString()), r1["ten"].ToString(), decimal.Parse(r1["dongia"].ToString()), 0, "", "", "");
}
}
//
}
if (dsxml.Tables[0].Rows.Count > 0)
{
dllReportM.frmReport f = new dllReportM.frmReport(m, dsxml, cmbten.Text.ToUpper(), "rptPhieuksk.rpt");
f.ShowDialog();
}
else
{
MessageBox.Show(
lan.Change_language_MessageText(
"Không có số liệu !"), LibMedi.AccessData.Msg);
}
}
else
{
MessageBox.Show(
lan.Change_language_MessageText(
"Không có số liệu !"), LibMedi.AccessData.Msg);
}
}
private void butTim_Click(object sender, System.EventArgs e)
{
try
{
string cont = "", sql = solieu.SelectedValue.ToString();
DataRow r;
sql = sql.Replace("medibvmmyy", "xxx");
sql = sql.Replace("medibv", m_user);
for (int i = 0; i < listBox1.Items.Count; i++)
{
if (listBox1.GetItemChecked(i))
{
r = m.getrowbyid(dsxml.Tables[0], "ma='" + i.ToString().PadLeft(2, '0') + "'");
cont += (cont != "")?" or ":"";
cont += r["cont"].ToString();
}
}
if (solieu.SelectedIndex != 4)
{
if (tu.Text != "" && den.Text != "")
{
sql += " and to_char(ac.ngay,'yyyy') between '" + tu.Text + "' and '" + den.Text + "'";
}
if (phai.SelectedIndex != -1)
{
sql += " and b.phai=" + phai.SelectedIndex;
}
if (mann.SelectedIndex != -1)
{
sql += " and b.mann='" + mann.SelectedValue.ToString() + "'";
}
if (madantoc.SelectedIndex != -1)
{
sql += " and b.madantoc='" + madantoc.SelectedValue.ToString() + "'";
}
if (matt.SelectedIndex != -1)
{
sql += " and b.matt='" + matt.SelectedValue.ToString() + "'";
}
if (maqu.SelectedIndex != -1)
{
sql += " and b.maqu='" + maqu.SelectedValue.ToString() + "'";
}
if (makp.SelectedIndex != -1)
{
sql += " and ac.makp='" + makp.SelectedValue.ToString() + "'";
}
if (dotuoi.SelectedIndex != -1)
{
sql += " and to_number(to_char(sysdate,'yyyy'),'0000') - to_number(b.namsinh,'0000')" + dotuoi.SelectedValue.ToString();
}
if (ttlucrv.SelectedIndex != -1 && ttlucrv.Enabled)
{
sql += " and ac.ttlucrv=" + int.Parse(ttlucrv.SelectedValue.ToString());
}
if (maicd.Text != "")
{
sql += " and ac.maicd='" + maicd.Text + "'";
}
if (checkBox1.Checked)
{
sql += " and ac.maicd in (select ma from " + m.user + ".dmicd10)";
}
}
else
{
if (tu.Text != "" && den.Text != "")
{
sql += " and to_char(a.ngay,'yyyy') between '" + tu.Text + "' and '" + den.Text + "'";
}
if (phai.SelectedIndex != -1)
{
sql += " and b.phai=" + phai.SelectedIndex;
}
if (mann.SelectedIndex != -1)
{
sql += " and b.mann='" + mann.SelectedValue.ToString() + "'";
}
if (madantoc.SelectedIndex != -1)
{
sql += " and b.madantoc='" + madantoc.SelectedValue.ToString() + "'";
}
if (matt.SelectedIndex != -1)
{
sql += " and b.matt='" + matt.SelectedValue.ToString() + "'";
}
if (maqu.SelectedIndex != -1)
{
sql += " and b.maqu='" + maqu.SelectedValue.ToString() + "'";
}
if (makp.SelectedIndex != -1)
{
sql += " and a.makp='" + makp.SelectedValue.ToString() + "'";
}
if (dotuoi.SelectedIndex != -1)
{
sql += " and to_number(to_char(sysdate,'yyyy'),'0000') - to_number(b.namsinh,'0000')" + dotuoi.SelectedValue.ToString();
}
if (ttlucrv.SelectedIndex != -1 && ttlucrv.Enabled)
{
sql += " and a.ttlucrv=" + int.Parse(ttlucrv.SelectedValue.ToString());
}
if (maicd.Text != "")
{
sql += " and a.maicd='" + maicd.Text + "'";
}
if (checkBox1.Checked)
{
sql += " and a.maicd in (select ma from " + m.user + ".dmicd10)";
}
}
if (cont != "")
{
sql += " and (" + cont + ")";
}
r = m.getrowbyid(dssl.Tables[0], "ma='" + solieu.Text + "'");
sql += " group by " + r["stt"].ToString() + " order by " + r["stt"].ToString();
if (sql.IndexOf("xxx") > 0)
{
ds = m.get_data_mmyy(sql, "01/01/" + tu.Text, "31/12/" + den.Text, false);
}
else
{
ds = m.get_data(sql);
}
if (ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), AccessData.Msg);
return;
}
//if (solieu.SelectedIndex != 4)
//{
ds = m.get_sum(ds, new string[] { "maicd", "nam" }, new string[] { "c01", "c02", "c03", "c04", "c05", "c06", "c07", "c08", "c09", "c10", "c11", "c12" });
//}
dc = new DataColumn();
dc.ColumnName = "ten";
dc.DataType = Type.GetType("System.String");
ds.Tables[0].Columns.Add(dc);
DataRow r2;
foreach (DataRow r1 in ds.Tables[0].Rows)
{
r2 = m.getrowbyid(dt, "cicd10='" + r1["maicd"].ToString() + "'");
if (r2 != null)
{
r1["ten"] = r1["maicd"].ToString().PadRight(9, ' ') + " " + r2["vviet"].ToString();
}
else
{
r1["ten"] = "xxx";
}
}
m.delrec(ds.Tables[0], "ten='xxx'");
string tuden = (tu.Text == "")?"":" Từ năm " + tu.Text + " năm " + den.Text;
dllReportM.frmReport f = new dllReportM.frmReport(m, ds, solieu.Text.ToUpper() + " " + tuden, "rptTknam.rpt");
f.ShowDialog();
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
}
private void load_grid()
{
if (loai.SelectedIndex == -1)
{
loai.Focus();
return;
}
if (tu.Text == "" || den.Text == "")
{
if (tu.Text == "")
{
tu.Focus();
}
else
{
den.Focus();
}
return;
}
s_dongthem = "";
DataRow r1, r2 = m.getrowbyid(dscq.Tables[0], "mavp<>'' and loai=" + int.Parse(loai.SelectedValue.ToString()));
s_dongthem = (r2 != null) ? r2["mavp"].ToString().Trim() : "";
songay = 7;
string s_tu = m.DateToString("dd/MM/yyyy", m.StringToDate(tu.Text).AddDays(-songay)), s_den = m.DateToString("dd/MM/yyyy", m.StringToDate(den.Text).AddDays(songay));
decimal tcvp = 0;
sql = "select sum(b.soluong*b.dongia-b.mien-b.thieu-b.tra) as sotien";
sql += " from xxx.v_vienphill a inner join xxx.v_vienphict b on a.id=b.id ";
sql += " inner join " + user + ".v_giavp c on b.mavp=c.id ";
sql += " inner join " + user + ".v_loaivp d on c.id_loai=d.id ";
sql += " inner join " + user + ".v_nhomvp e on d.id_nhom=e.ma ";
sql += " where b.soluong*b.dongia-b.mien-b.thieu-b.tra>0 ";
sql += " and a.ngay between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")";
sql += " and d.id=" + decimal.Parse(dtloai.Rows[loai.SelectedIndex]["nhomvp"].ToString());
DataTable temp = m.get_data_mmyy(sql, tu.Text, den.Text, false).Tables[0];
if (temp.Rows[0]["sotien"].ToString() != "")
{
tcvp = decimal.Parse(temp.Rows[0]["sotien"].ToString());
}
sql = "select a.mabn,to_char(a.ngay,'dd/mm/yyyy') as ngay,to_char(a.id) as id,b.mavp,b.soluong*b.dongia-b.mien-b.thieu as sotien,c.hoten as tenuser,to_char(a.ngayud,'dd/mm/yyyy hh24:mi') as ngayud ";
sql += " from xxx.v_vienphill a,xxx.v_vienphict b," + user + ".v_dlogin c," + user + ".v_giavp d," + user + ".v_loaivp e ";
sql += " where a.id=b.id and a.userid=c.id and b.mavp=d.id and d.id_loai=e.id and b.tra=0 and e.id=" + decimal.Parse(dtloai.Rows[loai.SelectedIndex]["nhomvp"].ToString());
sql += " and " + m.for_ngay("a.ngay", stime) + " between to_date('" + s_tu + "'," + stime + ") and to_date('" + s_den + "'," + stime + ")";
sql += " order by a.mabn,a.ngayud";
dsvp = m.get_data_mmyy(sql, s_tu, s_den, false);
sql = "select to_char(a.ngay,'dd/mm/yyyy hh24:mi') as ngay,a.mabn,f.hoten,a.idcls,a.idvp,g.hoten as tenbs,";
sql += "d.ten as may,e.ten as vung,000000000000.00 as vp,a.cp,a.lt,'' as ngayvp,'' as nguoivp,";
sql += "case when h.canquang is null then 0 else h.canquang end as canquang ";
sql += " from xxx.cls_ketqua a inner join " + user + ".btdbn b on a.mabn=b.mabn ";
sql += " inner join " + user + ".cls_loai c on a.loai=c.id ";
sql += " left join " + user + ".cls_may d on a.idmay=d.id";
sql += " left join " + user + ".cls_noidung e on a.idvung=e.id";
sql += " left join " + user + ".btdbn f on a.mabn=f.mabn";
sql += " left join " + user + ".dmbs g on a.mabs=g.ma";
sql += " left join xxx.cls_motact h on a.id=h.id ";
sql += " where " + m.for_ngay("a.ngay", stime) + " between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")";
if (loai.SelectedIndex != -1)
{
sql += " and a.loai=" + int.Parse(loai.SelectedValue.ToString());
}
if (s_trasau != "")
{
sql += " and a.madoituong not in (" + s_trasau.Substring(0, s_trasau.Length - 1) + ")";
}
if (s_mien != "")
{
sql += " and a.madoituong not in (" + s_mien.Substring(0, s_mien.Length - 1) + ")";
}
sql += " order by a.idcls";
ds = m.get_data_mmyy(sql, tu.Text, den.Text, false);
if (ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), LibMedi.AccessData.Msg);
}
else
{
decimal st = 0;
foreach (DataRow r in ds.Tables[0].Rows)
{
sql = "id='" + r["idvp"].ToString().Trim() + "'";
if (s_dongthem != "")
{
sql = " and mavp not in (" + s_dongthem + ")";
}
r1 = m.getrowbyid(dsvp.Tables[0], sql);
if (r1 != null)
{
r["ngayvp"] = r1["ngay"].ToString();
r["nguoivp"] = r1["tenuser"].ToString();
st = decimal.Parse(r1["sotien"].ToString());
r1.Delete();
if (s_dongthem != "")
{
sql = "mabn='" + r["mabn"].ToString() + "' and mavp in (" + s_dongthem + ")";
r2 = m.getrowbyid(dsvp.Tables[0], sql);
if (r2 != null)
{
st += decimal.Parse(r2["sotien"].ToString());
r2.Delete();
}
}
r["vp"] = st;
}
else
{
sql = "mabn='" + r["mabn"].ToString() + "' and mavp not in (" + s_dongthem + ")";
r1 = m.getrowbyid(dsvp.Tables[0], sql);
if (r1 != null)
{
r["ngayvp"] = r1["ngay"].ToString();
r["nguoivp"] = r1["tenuser"].ToString();
st = decimal.Parse(r1["sotien"].ToString());
r1.Delete();
if (s_dongthem != "")
{
sql = "mabn='" + r["mabn"].ToString() + "' and mavp in (" + s_dongthem + ")";
r2 = m.getrowbyid(dsvp.Tables[0], sql);
if (r2 != null)
{
st += decimal.Parse(r2["sotien"].ToString());
r2.Delete();
}
}
r["vp"] = st;
}
}
}
DataSet tmp;
if (rb2.Checked)
{
tmp = ds.Copy();
ds.Clear();
ds.Merge(tmp.Tables[0].Select("cp<>vp", "idcls"));
}
else if (rb3.Checked)
{
tmp = ds.Copy();
ds.Clear();
ds.Merge(tmp.Tables[0].Select("substring(ngay,1,10)<>ngayvp", "idcls"));
}
dllReportM.frmReport f = new dllReportM.frmReport(m, ds, "rptdoichieuvp.rpt", (tu.Text == den.Text) ? "Ngày " + tu.Text : "Từ ngày " + tu.Text + " đến " + den.Text, loai.Text, tcvp.ToString(), "", "", "");
f.ShowDialog();
}
}
private void butXem_Click(object sender, System.EventArgs e)
{
dllReportM.frmReport f = new dllReportM.frmReport(m, ds, "rptBiaTkbv.rpt", benhvien.Text, mabv.Text, matt.Text, diachi.Text, dienthoai.Text, noidung.Text);
f.ShowDialog(this);
}
private void butIn_Click(object sender, System.EventArgs e)
{
Cursor = Cursors.WaitCursor;
ds.Clear();
s_makp = "'";
for (int i = 0; i < makp.Items.Count; i++)
{
if (makp.GetItemChecked(i))
{
s_makp += dtkp.Rows[i]["makp"].ToString().Trim() + "','";
}
}
if (rb1.Checked)
{
sql = "select a.id,a.mabn,g.hoten,c.sothe,h.tenkp,a.chandoan,a.maicd,";
sql += "to_char(a.ngayra,'dd/mm/yy') as ngayra,to_char(a.ngayvao,'dd/mm/yy') as ngayvao,";
sql += "" + m.for_num_ngay("a.ngayra") + "-" + m.for_num_ngay("a.ngayvao") + "+1 as songay,";
sql += "i.stt,b.sotien";
sql += " from xxx.v_thvpll a,xxx.v_thvpct b,xxx.v_thvpbhyt c," + user + ".d_dmbd d," + user + ".d_dmnhom e," + user + ".v_nhomvp f," + user + ".btdbn g," + user + ".btdkp_bv h," + user + ".v_nhombhyt i";
sql += " where a.id=b.id and a.mabn=g.mabn and a.id=c.id and b.mavp=d.id and d.manhom=e.id and e.nhomvp=f.ma and a.makp=h.makp and f.idnhombhyt=i.id";
sql += " and b.madoituong=" + int.Parse(madoituong.SelectedValue.ToString());
sql += " and " + m.for_ngay("a.ngayra", "'dd/mm/yyyy'") + " between to_date('" + tu.Text + "','dd/mm/yyyy') and to_date('" + den.Text + "','dd/mm/yyyy')";
if (sothe.Text != "")
{
sql += " and substr(c.sothe," + Convert.ToInt16(vitri.Value) + "," + sothe.Text.Trim().Length + ")='" + sothe.Text.Trim() + "'";
}
if (s_makp.Length > 1)
{
sql += " and b.makp in (" + s_makp.Substring(0, s_makp.Length - 2) + ")";
}
sql += " and c.sothe is not null";
if (s_tunguyen != "" && loaidt.SelectedIndex != 0)
{
if (loaidt.SelectedIndex == 1)
{
sql += " and substr(c.sothe," + v1 + "," + v2 + ") not in ('" + s_tunguyen.Substring(0, s_tunguyen.Length) + "')";
}
else if (loaidt.SelectedIndex == 2)
{
sql += " and substr(c.sothe," + v1 + "," + v2 + ") in ('" + s_tunguyen.Substring(0, s_tunguyen.Length) + "')";
}
}
sql += " union all ";
sql += "select a.id,a.mabn,g.hoten,c.sothe,h.tenkp,a.chandoan,a.maicd,";
sql += "to_char(a.ngayra,'dd/mm/yy') as ngayra,to_char(a.ngayvao,'dd/mm/yy') as ngayvao,";
sql += "" + m.for_num_ngay("a.ngayra") + "-" + m.for_num_ngay("a.ngayvao") + "+1 as songay,";
sql += "i.stt,b.sotien";
sql += " from xxx.v_thvpll a,xxx.v_thvpct b,xxx.v_thvpbhyt c," + user + ".v_giavp d," + user + ".v_loaivp e," + user + ".v_nhomvp f," + user + ".btdbn g," + user + ".btdkp_bv h," + user + ".v_nhombhyt i";
sql += " where a.id=b.id and a.mabn=g.mabn and a.id=c.id and b.mavp=d.id and d.id_loai=e.id and e.id_nhom=f.ma and a.makp=h.makp and f.idnhombhyt=i.id";
sql += " and b.madoituong=" + int.Parse(madoituong.SelectedValue.ToString());
sql += " and " + m.for_ngay("a.ngayra", "'dd/mm/yyyy'") + " between to_date('" + tu.Text + "','dd/mm/yyyy') and to_date('" + den.Text + "','dd/mm/yyyy')";
if (sothe.Text != "")
{
sql += " and substr(c.sothe," + Convert.ToInt16(vitri.Value) + "," + sothe.Text.Trim().Length + ")='" + sothe.Text.Trim() + "'";
}
if (s_makp.Length > 1)
{
sql += " and b.makp in (" + s_makp.Substring(0, s_makp.Length - 2) + ")";
}
sql += " and c.sothe is not null";
if (s_tunguyen != "" && loaidt.SelectedIndex != 0)
{
if (loaidt.SelectedIndex == 1)
{
sql += " and substr(c.sothe," + v1 + "," + v2 + ") not in ('" + s_tunguyen.Substring(0, s_tunguyen.Length) + "')";
}
else if (loaidt.SelectedIndex == 2)
{
sql += " and substr(c.sothe," + v1 + "," + v2 + ") in ('" + s_tunguyen.Substring(0, s_tunguyen.Length) + "')";
}
}
}
else
{
sql = "select a.id,j.mabn,g.hoten,c.sothe,h.tenkp,j.chandoan,j.maicd,";
sql += "to_char(j.ngayra,'dd/mm/yy') as ngayra,to_char(j.ngayvao,'dd/mm/yy') as ngayvao,";
sql += "" + m.for_num_ngay("j.ngayra") + "-" + m.for_num_ngay("j.ngayvao") + "+1 as songay,";
sql += "i.stt,b.sotien";
sql += " from xxx.v_ttrvll a,xxx.v_ttrvct b,xxx.v_ttrvbhyt c,xxx.v_ttrvds j," + user + ".d_dmbd d," + user + ".d_dmnhom e," + user + ".v_nhomvp f," + user + ".btdbn g," + user + ".btdkp_bv h," + user + ".v_nhombhyt i";
sql += " where a.id=b.id and a.id=j.id and j.mabn=g.mabn and a.id=c.id and b.mavp=d.id and d.manhom=e.id and e.nhomvp=f.ma and a.makp=h.makp and f.idnhombhyt=i.id";
sql += " and b.madoituong=" + int.Parse(madoituong.SelectedValue.ToString());
sql += " and " + m.for_ngay("a.ngay", "'dd/mm/yyyy'") + " between to_date('" + tu.Text + "','dd/mm/yyyy') and to_date('" + den.Text + "','dd/mm/yyyy')";
if (sothe.Text != "")
{
sql += " and substr(c.sothe," + Convert.ToInt16(vitri.Value) + "," + sothe.Text.Trim().Length + ")='" + sothe.Text.Trim() + "'";
}
if (s_makp.Length > 1)
{
sql += " and b.makp in (" + s_makp.Substring(0, s_makp.Length - 2) + ")";
}
sql += " and c.sothe is not null";
if (s_tunguyen != "" && loaidt.SelectedIndex != 0)
{
if (loaidt.SelectedIndex == 1)
{
sql += " and substr(c.sothe," + v1 + "," + v2 + ") not in ('" + s_tunguyen.Substring(0, s_tunguyen.Length) + "')";
}
else if (loaidt.SelectedIndex == 2)
{
sql += " and substr(c.sothe," + v1 + "," + v2 + ") in ('" + s_tunguyen.Substring(0, s_tunguyen.Length) + "')";
}
}
sql += " union all ";
sql += "select a.id,j.mabn,g.hoten,c.sothe,h.tenkp,j.chandoan,j.maicd,";
sql += "to_char(j.ngayra,'dd/mm/yy') as ngayra,to_char(j.ngayvao,'dd/mm/yy') as ngayvao,";
sql += "" + m.for_num_ngay("j.ngayra") + "-" + m.for_num_ngay("j.ngayvao") + "+1 as songay,";
sql += "i.stt,b.sotien";
sql += " from xxx.v_ttrvll a,xxx.v_ttrvct b,xxx.v_ttrvbhyt c,xxx.v_ttrvds j," + user + ".v_giavp d," + user + ".v_loaivp e," + user + ".v_nhomvp f," + user + ".btdbn g," + user + ".btdkp_bv h," + user + ".v_nhombhyt i";
sql += " where a.id=b.id and a.id=j.id and j.mabn=g.mabn and a.id=c.id and b.mavp=d.id and d.id_loai=e.id and e.id_nhom=f.ma and a.makp=h.makp and f.idnhombhyt=i.id";
sql += " and b.madoituong=" + int.Parse(madoituong.SelectedValue.ToString());
sql += " and " + m.for_ngay("a.ngay", "'dd/mm/yyyy'") + " between to_date('" + tu.Text + "','dd/mm/yyyy') and to_date('" + den.Text + "','dd/mm/yyyy')";
if (sothe.Text != "")
{
sql += " and substr(c.sothe," + Convert.ToInt16(vitri.Value) + "," + sothe.Text.Trim().Length + ")='" + sothe.Text.Trim() + "'";
}
if (s_makp.Length > 1)
{
sql += " and b.makp in (" + s_makp.Substring(0, s_makp.Length - 2) + ")";
}
sql += " and c.sothe is not null";
if (s_tunguyen != "" && loaidt.SelectedIndex != 0)
{
if (loaidt.SelectedIndex == 1)
{
sql += " and substr(c.sothe," + v1 + "," + v2 + ") not in ('" + s_tunguyen.Substring(0, s_tunguyen.Length) + "')";
}
else if (loaidt.SelectedIndex == 2)
{
sql += " and substr(c.sothe," + v1 + "," + v2 + ") in ('" + s_tunguyen.Substring(0, s_tunguyen.Length) + "')";
}
}
}
System.Data.DataTable tmp = v.get_vienphi(tu.Text, den.Text, sql).Tables[0];
foreach (DataRow r in tmp.Rows)
{
//sql="id="+decimal.Parse(r["id"].ToString());
sql = "sothe='" + r["sothe"].ToString() + "' and mabn='" + r["mabn"].ToString() + "' and ngayvao='" + r["ngayvao"].ToString() + "'";
r1 = m.getrowbyid(ds.Tables[0], sql);
if (r1 == null)
{
r2 = ds.Tables[0].NewRow();
r2["id"] = r["id"].ToString();
if (r["sothe"].ToString().Trim().Length > 6)
{
r2["stt"] = (r["sothe"].ToString().Substring(2, 2) == s_thetrongtinh)?0:1;
r2["tinh"] = (r["sothe"].ToString().Substring(2, 2) == s_thetrongtinh)?"TỈNH":"KHÁC TỈNH";
r2["sothe1"] = (s_thetunguyen.IndexOf(r["sothe"].ToString().Substring(4, 2)) == -1)?"0":"1";
r2["doituong"] = (s_thetunguyen.IndexOf(r["sothe"].ToString().Substring(4, 2)) == -1)?"BẮT BUỘC":"TỰ NGUYỆN";
r2["sothe2"] = r["sothe"].ToString().Substring(2, 2);
r2["sothe3"] = r["sothe"].ToString().Substring(4, 2);
}
r2["sothe"] = r["sothe"].ToString();
r2["mabn"] = r["mabn"].ToString();
r2["hoten"] = r["hoten"].ToString();
r2["tenkp"] = r["tenkp"].ToString();
r2["chandoan"] = r["chandoan"].ToString();
r2["maicd"] = r["maicd"].ToString();
r2["ngayvao"] = r["ngayvao"].ToString();
r2["ngayra"] = r["ngayra"].ToString();
r2["songay"] = r["songay"].ToString();
foreach (DataRow r3 in dtnhom.Select("true", "stt"))
{
r2["c" + r3["stt"].ToString().Trim()] = 0;
}
r2["c" + r["stt"].ToString().Trim()] = r["sotien"].ToString();
r2["tc"] = r["sotien"].ToString();
ds.Tables[0].Rows.Add(r2);
}
else
{
dr = ds.Tables[0].Select(sql);
if (dr.Length > 0)
{
dr[0]["c" + r["stt"].ToString().Trim()] = decimal.Parse(dr[0]["c" + r["stt"].ToString().Trim()].ToString()) + decimal.Parse(r["sotien"].ToString());
dr[0]["tc"] = decimal.Parse(dr[0]["tc"].ToString()) + decimal.Parse(r["sotien"].ToString());
}
}
}
dsxml = new DataSet();
dsxml = ds.Copy();
dsxml.Clear();
dsxml.Merge(ds.Tables[0].Select("true", "stt,sothe1,sothe3,sothe2,sothe,ngayvao,mabn"));
dsxml.WriteXml("..//..//..//xml//t_kcbbhyt.xml", XmlWriteMode.WriteSchema);
Cursor = Cursors.Default;
if (dsxml.Tables[0].Rows.Count == 0)
{
MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), LibMedi.AccessData.Msg);
}
else
{
dllReportM.frmReport f = new dllReportM.frmReport(m, dsxml.Tables[0], "rptkcbbhyt.rpt", (tu.Text == den.Text)?"Ngày " + tu.Text:"Từ ngày " + tu.Text + " đến " + den.Text, madoituong.SelectedValue.ToString(), loaidt.Text, "", "", "", "", "", "", "");
f.ShowDialog();
}
}
private void butIn_Click(object sender, System.EventArgs e)
{
/*
* xxx.bhytkb a,xxx.bhytthuoc b,xxx.bhytds c,"+user+".d_dmbd d,"+user+".d_dmnhom e,"+user+".v_nhomvp f,"+user+".btdkp_bv h,"+user+".v_nhombhyt i
*
* a.id=b.id and a.mabn=c.mabn and b.mabd=d.id and d.manhom=e.id and e.nhomvp=f.ma and a.makp=h.makp(+) and f.idnhombhyt=i.id
* */
Cursor = Cursors.WaitCursor;
ds.Clear();
dsts.Clear();
sql = "select a.id,a.mabn,c.hoten,a.sothe,h.tenkp,a.chandoan,a.maicd,";
sql += "to_char(a.ngay,'dd/mm/yy') as ngayra,to_char(a.ngay,'dd/mm/yy') as ngayvao,";
sql += "" + m.for_num_ngay("a.ngay") + "-" + m.for_num_ngay("a.ngay") + "+1 as songay,";
sql += "i.stt,b.soluong*j.giamua as sotien,a.congkham";
sql += " from xxx.bhytkb a inner join xxx.bhytthuoc b on a.id=b.id inner join xxx.bhytds c on a.mabn=c.mabn inner join " + user + ".d_dmbd d on b.mabd=d.id inner join " + user + ".d_dmnhom e on d.manhom=e.id inner join " + user + ".v_nhomvp f on e.nhomvp=f.ma left join " + user + ".btdkp_bv h on a.makp=h.makp inner join " + user + ".v_nhombhyt i on f.idnhombhyt=i.id inner join xxx.d_theodoi j on b.sttt=j.id ";
sql += " where ";
sql += " a.maphu=" + int.Parse(madoituong.SelectedValue.ToString());
sql += " and " + m.for_ngay("a.ngay", "'dd/mm/yyyy'") + " between to_date('" + tu.Text + "','dd/mm/yyyy') and to_date('" + den.Text + "','dd/mm/yyyy')";
if (sothe.Text != "")
{
sql += " and substr(a.sothe," + Convert.ToInt16(vitri.Value) + "," + sothe.Text.Trim().Length + ")='" + sothe.Text.Trim() + "'";
}
sql += " and a.sothe is not null";
if (s_tunguyen != "" && loaidt.SelectedIndex != 0)
{
if (loaidt.SelectedIndex == 1)
{
sql += " and substr(a.sothe," + v1 + "," + v2 + ") not in ('" + s_tunguyen.Substring(0, s_tunguyen.Length) + "')";
}
else if (loaidt.SelectedIndex == 2)
{
sql += " and substr(a.sothe," + v1 + "," + v2 + ") in ('" + s_tunguyen.Substring(0, s_tunguyen.Length) + "')";
}
}
/*
* xxx.bhytkb a,xxx.bhytcls b,xxx.bhytds c,"+user+".v_giavp d,"+user+".v_loaivp e,"+user+".v_nhomvp f,"+user+".btdkp_bv h,"+user+".v_nhombhyt i
*
* a.id=b.id and a.mabn=c.mabn and b.mavp=d.id and d.id_loai=e.id and e.id_nhom=f.ma and a.makp=h.makp(+) and f.idnhombhyt=i.id
* */
sql += " union all ";
sql += "select a.id,a.mabn,c.hoten,a.sothe,h.tenkp,a.chandoan,a.maicd,";
sql += "to_char(a.ngay,'dd/mm/yy') as ngayra,to_char(a.ngay,'dd/mm/yy') as ngayvao,";
sql += "" + m.for_num_ngay("a.ngay") + "-" + m.for_num_ngay("a.ngay") + "+1 as songay,";
sql += "i.stt,b.soluong*b.dongia as sotien,a.congkham";
sql += " from xxx.bhytkb a inner join xxx.bhytcls b on a.id=b.id inner join xxx.bhytds c on a.mabn=c.mabn inner join " + user + ".v_giavp d on b.mavp=d.id inner join " + user + ".v_loaivp e on d.id_loai=e.id inner join " + user + ".v_nhomvp f on e.id_nhom=f.ma left join " + user + ".btdkp_bv h on a.makp=h.makp inner join " + user + ".v_nhombhyt i on f.idnhombhyt=i.id ";
sql += " where ";
sql += " a.maphu=" + int.Parse(madoituong.SelectedValue.ToString());
sql += " and " + m.for_ngay("a.ngay", "'dd/mm/yyyy'") + " between to_date('" + tu.Text + "','dd/mm/yyyy') and to_date('" + den.Text + "','dd/mm/yyyy')";
if (sothe.Text != "")
{
sql += " and substr(a.sothe," + Convert.ToInt16(vitri.Value) + "," + sothe.Text.Trim().Length + ")='" + sothe.Text.Trim() + "'";
}
sql += " and a.sothe is not null";
if (s_tunguyen != "" && loaidt.SelectedIndex != 0)
{
if (loaidt.SelectedIndex == 1)
{
sql += " and substr(a.sothe," + v1 + "," + v2 + ") not in ('" + s_tunguyen.Substring(0, s_tunguyen.Length) + "')";
}
else if (loaidt.SelectedIndex == 2)
{
sql += " and substr(a.sothe," + v1 + "," + v2 + ") in ('" + s_tunguyen.Substring(0, s_tunguyen.Length) + "')";
}
}
System.Data.DataTable tmp = d.get_thuoc(tu.Text, den.Text, sql).Tables[0];
foreach (DataRow r in tmp.Rows)
{
r1 = d.getrowbyid(dsts.Tables[0], "id=" + decimal.Parse(r["id"].ToString()));
if (r1 == null)
{
r2 = dsts.Tables[0].NewRow();
r2["id"] = r["id"].ToString();
r2["sothe"] = r["sothe"].ToString();
dsts.Tables[0].Rows.Add(r2);
}
sql = "sothe='" + r["sothe"].ToString().Trim() + "'";
r1 = d.getrowbyid(ds.Tables[0], sql);
if (r1 == null)
{
r2 = ds.Tables[0].NewRow();
r2["id"] = r["id"].ToString();
if (r["sothe"].ToString().Trim().Length > 6)
{
r2["stt"] = (r["sothe"].ToString().Substring(2, 2) == s_thetrongtinh)?0:1;
r2["tinh"] = (r["sothe"].ToString().Substring(2, 2) == s_thetrongtinh)?"TỈNH":"KHÁC TỈNH";
r2["sothe1"] = (s_thetunguyen.IndexOf(r["sothe"].ToString().Substring(4, 2)) == -1)?"0":"1";
r2["doituong"] = (s_thetunguyen.IndexOf(r["sothe"].ToString().Substring(4, 2)) == -1)?"BẮT BUỘC":"TỰ NGUYỆN";
r2["sothe2"] = r["sothe"].ToString().Substring(2, 2);
r2["sothe3"] = r["sothe"].ToString().Substring(4, 2);
}
r2["sothe"] = r["sothe"].ToString().Trim();
r2["mabn"] = r["mabn"].ToString();
r2["hoten"] = r["hoten"].ToString();
r2["tenkp"] = r["tenkp"].ToString();
r2["chandoan"] = r["chandoan"].ToString();
r2["maicd"] = r["maicd"].ToString();
r2["ngayvao"] = r["ngayvao"].ToString();
r2["ngayra"] = r["ngayra"].ToString();
r2["songay"] = r["songay"].ToString();
foreach (DataRow r3 in dtnhom.Select("true", "stt"))
{
r2["c" + r3["stt"].ToString().Trim()] = 0;
}
r2["congkham"] = r["congkham"].ToString();
r2["c" + r["stt"].ToString().Trim()] = r["sotien"].ToString();
r2["tc"] = decimal.Parse(r["sotien"].ToString()) + decimal.Parse(r["congkham"].ToString());
ds.Tables[0].Rows.Add(r2);
}
else
{
dr = ds.Tables[0].Select(sql);
if (dr.Length > 0)
{
dr[0]["c" + r["stt"].ToString().Trim()] = decimal.Parse(dr[0]["c" + r["stt"].ToString().Trim()].ToString()) + decimal.Parse(r["sotien"].ToString());
dr[0]["tc"] = decimal.Parse(dr[0]["tc"].ToString()) + decimal.Parse(r["sotien"].ToString());
}
}
}
foreach (DataRow r in ds.Tables[0].Rows)
{
r["soluot"] = dsts.Tables[0].Select("sothe='" + r["sothe"].ToString() + "'").Length;
r["songay"] = dsts.Tables[0].Select("sothe='" + r["sothe"].ToString() + "'").Length;
}
dsxml = new DataSet();
dsxml = ds.Copy();
dsxml.Clear();
dsxml.Merge(ds.Tables[0].Select("true", "stt,sothe1,sothe3,sothe2,sothe,ngayvao,mabn"));
dsxml.WriteXml("..//..//..//xml//t_thkcbbhyt.xml", XmlWriteMode.WriteSchema);
Cursor = Cursors.Default;
if (dsxml.Tables[0].Rows.Count == 0)
{
MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), LibMedi.AccessData.Msg);
}
else
{
dllReportM.frmReport f = new dllReportM.frmReport(m, dsxml.Tables[0], "rptthkcbbhytngtr.rpt", (tu.Text == den.Text)?"Ngày " + tu.Text:"Từ ngày " + tu.Text + " đến " + den.Text, madoituong.Text, loaidt.Text, "", "", "", "", "", "", "");
f.ShowDialog();
}
}
private void butIn_Click(object sender, System.EventArgs e)
{
string sql1;
s_loai = ""; s_phieu = "";
for (int i = 0; i < loai.Items.Count; i++)
{
if (loai.GetItemChecked(i))
{
s_loai += dtloai.Rows[i]["id"].ToString().Trim() + ",";
}
}
for (int i = 0; i < phieu.Items.Count; i++)
{
if (phieu.GetItemChecked(i))
{
s_phieu += dtphieu.Rows[i]["id"].ToString().Trim() + ",";
}
}
sql = "select * from xxx.d_daduyet where nhom=" + int.Parse(nhom.SelectedValue.ToString());
sql += " and ngay between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")";
sql += " and makp=" + int.Parse(makp.SelectedValue.ToString());
if (s_makho != "")
{
sql += " and makho in (" + s_makho.Substring(0, s_makho.Length - 1) + ")";
}
if (d.get_thuoc(tu.Text, den.Text, sql).Tables[0].Rows.Count == 0)
{
MessageBox.Show(
lan.Change_language_MessageText("Không có số liệu !"), d.Msg);
tu.Focus();
return;
}
sql1 = sql;
sql = "select * from xxx.d_duyet where loai<>2 and done=1 and nhom=" + int.Parse(nhom.SelectedValue.ToString());
sql += " and makhoa=" + int.Parse(makp.SelectedValue.ToString());
sql += " and ngay between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")";
if (s_loai != "")
{
sql += " and loai in (" + s_loai.Substring(0, s_loai.Length - 1) + ")";
}
if (s_phieu != "")
{
sql += " and phieu in (" + s_phieu.Substring(0, s_phieu.Length - 1) + ")";
}
if (d.get_thuoc(tu.Text, den.Text, sql).Tables[0].Rows.Count > 0)
{
MessageBox.Show(
lan.Change_language_MessageText("Số liệu kho chưa duyệt xong !"), d.Msg);
tu.Focus();
return;
}
taotable();
if (dsxml.Tables[0].Rows.Count == 0)
{
MessageBox.Show(
lan.Change_language_MessageText("Không có số liệu !"), d.Msg);
tu.Focus();
return;
}
s_mmyy = d.get_mmyy(tu.Text, den.Text, sql1);
if (s_mmyy == "")
{
s_mmyy = d.mmyy(tu.Text);
}
if (s_makho != "")
{
s_makho = s_makho.Substring(0, s_makho.Length - 1);
}
id = d.get_id_phieuxuat(tu.Text, int.Parse(makp.SelectedValue.ToString()), i_nhom, s_loai, s_phieu, s_makho, s_mmyy);
if (id == 0)
{
id = d.get_id_phieuxuat();
soct = d.get_phieuxuat(s_mmyy, i_nhom, s_makho);
}
else
{
soct = d.get_phieuxuat(s_mmyy, id);
}
d.upd_phieuxuat(s_mmyy, id, soct, tu.Text, int.Parse(makp.SelectedValue.ToString()), i_nhom, s_loai, s_phieu, s_makho, d_tongcong, "", "", "", i_userid);
string s_tndn = tu.Text;
dllReportM.frmReport f = new dllReportM.frmReport(m, dsxml.Tables[0], "d_phieuxuat.rpt", soct, s_tndn, "", "", makp.Text, "Xuất sử dụng", s_tenkho, doiso.doiraso(Convert.ToInt64(d_tongcong).ToString()), "", "");
f.ShowDialog();
}
private void butTim_Click(object sender, System.EventArgs e)
{
try
{
string cont = "", sql = solieu.SelectedValue.ToString();
sql = sql.Replace("medibvmmyy", "xxx");
sql = sql.Replace("medibv", m.user);
DataRow r;
if (listBox1.CheckedItems.Count > 0)
{
for (int i = 0; i < listBox1.Items.Count; i++)
{
if (listBox1.GetItemChecked(i))
{
r = m.getrowbyid(dsxml.Tables[0], "ma='" + i.ToString().PadLeft(2, '0') + "'");
cont += (cont != "")?" or ":"";
cont += r["cont"].ToString();
}
}
}
if (tu.Text != "")
{
sql += " and " + m.for_ngay("a.ngay", "'" + m.f_ngay + "'") + " between to_date('" + tu.Text + "','dd/mm/yyyy') and to_date('" + den.Text + "','dd/mm/yyyy')";
}
if (phai.SelectedIndex != -1)
{
sql += " and b.phai=" + phai.SelectedIndex;
}
if (mann.SelectedIndex != -1)
{
sql += " and b.mann='" + mann.SelectedValue.ToString() + "'";
}
if (madantoc.SelectedIndex != -1)
{
sql += " and b.madantoc='" + madantoc.SelectedValue.ToString() + "'";
}
if (matt.SelectedIndex != -1)
{
sql += " and b.matt='" + matt.SelectedValue.ToString() + "'";
}
if (maqu.SelectedIndex != -1)
{
sql += " and b.maqu='" + maqu.SelectedValue.ToString() + "'";
}
if (makp.SelectedIndex != -1)
{
sql += " and a.makp='" + makp.SelectedValue.ToString() + "'";
}
if (dotuoi.SelectedIndex != -1)
{
sql += " and to_number(to_char(now(),'yyyy'))-to_number(b.namsinh) " + dotuoi.SelectedValue.ToString();
}
if (ttlucrv.SelectedIndex != -1 && ttlucrv.Enabled)
{
sql += " and a.ttlucrv=" + int.Parse(ttlucrv.SelectedValue.ToString());
}
if (maicd.Text != "")
{
sql += " and a.maicd='" + maicd.Text + "'";
}
if (checkBox1.Checked)
{
sql += " and a.maicd in (select ma from " + m.user + ".dmicd10)";
}
if (cont != "")
{
sql += " and (" + cont + ")";
}
r = m.getrowbyid(dssl.Tables[0], "ma='" + solieu.Text + "'");
sql += " group by " + r["stt"].ToString() + " order by " + r["stt"].ToString();
if (sql.IndexOf("xxx") >= 0)
{
ds = m.get_data_mmyy(sql, tu.Text, den.Text, false);
}
else
{
ds = m.get_data(sql);
}
if (ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), AccessData.Msg);
return;
}
ds = m.get_sum(ds, new string[] { "makp" }, new string[] { "tso", "c01", "c02", "c03", "c04", "c05", "c06", "c07" });
dc = new DataColumn();
dc.ColumnName = "ten";
dc.DataType = Type.GetType("System.String");
ds.Tables[0].Columns.Add(dc);
DataRow r2;
foreach (DataRow r1 in ds.Tables[0].Rows)
{
r2 = m.getrowbyid(dt, "makp='" + r1["makp"].ToString() + "'");
if (r2 != null)
{
r1["ten"] = r2["tenkp"].ToString();
}
}
string tuden = (tu.Text == "")?"":" Từ ngày " + tu.Text + " đến " + den.Text;
dllReportM.frmReport f = new dllReportM.frmReport(m, ds, solieu.Text.ToUpper() + " " + tuden, "rptTkttlucrv.rpt");
f.ShowDialog();
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
}
private void butIN_Click(object sender, System.EventArgs e)
{
Cursor = Cursors.WaitCursor;
DataRow r1, r2, r3;
DataRow [] dr;
ds.Clear();
sql = "select a.mabs,count(*) as so ";
sql += "from xxx.cls_ketqua a inner join " + user + ".dmbs b on a.mabs=b.ma ";
sql += " inner join " + user + ".cls_loai g on a.loai=g.id";
sql += " left join xxx.cls_motact i on a.id=i.id ";
sql += " where " + m.for_ngay("a.ngay", stime) + " between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")";
sql += " and a.loai=" + int.Parse(loai.SelectedValue.ToString());
if (rb1.Checked)
{
sql += " and i.canquang=1";
}
else if (rb2.Checked)
{
sql += " and (i.canquang=0 or i.canquang is null)";
}
if (may.SelectedIndex != -1)
{
sql += " and a.idmay=" + int.Parse(may.SelectedValue.ToString());
}
sql += " group by a.mabs";
foreach (DataRow r in m.get_data_mmyy(sql, tu.Text, den.Text, false).Tables[0].Select("true", "mabs"))
{
r1 = m.getrowbyid(dtbs, "ma='" + r["mabs"].ToString() + "'");
if (r1 != null)
{
sql = " mabn='" + r["mabs"].ToString() + "'";
r2 = m.getrowbyid(ds.Tables[0], sql);
if (r2 == null)
{
r3 = ds.Tables[0].NewRow();
if (may.SelectedIndex != -1)
{
r3["tenkp"] = may.Text;
}
r3["ngay"] = loai.Text;
r3["mabn"] = r["mabs"].ToString();
r3["hoten"] = r1["hoten"].ToString();
r3["c01"] = decimal.Parse(r["so"].ToString());
ds.Tables[0].Rows.Add(r3);
}
else
{
dr = ds.Tables[0].Select(sql);
if (dr.Length > 0)
{
dr[0]["c01"] = decimal.Parse(dr[0]["c01"].ToString()) + decimal.Parse(r["so"].ToString());
}
}
}
}
Cursor = Cursors.Default;
if (ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), LibMedi.AccessData.Msg);
}
else
{
string title = (rb1.Checked) ? "CE, " : (rb2.Checked) ? "NE, " : "";
title += (tu.Text == den.Text) ? "Ngày " + tu.Text : "Từ ngày " + tu.Text + " đến " + den.Text;
dllReportM.frmReport f = new dllReportM.frmReport(m, ds, title, "rpttkbsdoc.rpt");
f.ShowDialog();
}
}