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(); } }