private void butIn_Click(object sender, System.EventArgs e) { s_manhom = ""; for (int i = 0; i < manhom.Items.Count; i++) { if (manhom.GetItemChecked(i)) { s_manhom += dtnhom.Rows[i]["id"].ToString().Trim() + ","; } } i_loai = (maloai.SelectedIndex != -1)?int.Parse(maloai.SelectedValue.ToString()):0; i_tt = stt.SelectedIndex; i_bhyt = (bhyt.Checked)?1:0; i_thietyeu = (thietyeu.Checked)?1:0; i_sunghiep = (sunghiep.Checked)?1:0; i_tonkho = (tonkho.Checked)?1:0; string tenfile = "d_dmbd_ten.rpt"; sql = "select a.*,trim(a.ten)||' '||a.hamluong as tenbd,b.ten as tennhom,c.ten as tenloai,d.ten as tenhang,e.ten as tennuoc,f.ten as tenbo," + "g.ten as nhomkt,b.stt as ttnhom,c.stt as ttloai"; sql += " from " + user + ".d_dmbd a inner join " + user + ".d_dmnhom b on a.manhom=b.id inner join " + user + ".d_dmloai c on a.maloai=c.id inner join " + user + ".d_dmhang d on a.mahang=d.id inner join " + user + ".d_dmnuoc e on a.manuoc=e.id left join " + user + ".d_nhombo f on a.nhombo=f.id left join " + user + ".d_dmnhomkt g on a.sotk=g.id"; sql += " where a.nhom=" + i_nhomkho; if (s_manhom != "") { sql += " and a.manhom in (" + s_manhom.Substring(0, s_manhom.Length - 1) + ")"; } if (i_loai != 0) { sql += " and a.maloai=" + i_loai; } if (i_bhyt != 0) { sql += " and a.bhyt<>0"; } if (i_thietyeu != 0) { sql += " and a.thietyeu=1"; } if (i_sunghiep != 0) { sql += " and a.sunghiep=1"; } if (i_tonkho != 0) { sql += " and a.id in (select mabd from " + user + s_mmyy + ".d_tonkhoth where tondau+slnhap-slxuat>0)"; //gam 14/11/2011 } if (i_tt == 0) { sql += " order by a.ten"; } else if (i_tt == 3) { sql += " order by a.stt"; } else { sql += " order by b.stt,c.stt"; if (i_tt == 1) { sql += ",a.ten"; } else { sql += ",a.tenhc,a.ten"; } tenfile = "d_dmbd_nhom.rpt"; } string tit = "DANH MỤC THUỐC "; if (i_thietyeu != 0) { tit += " CHỦ YẾU"; } if (i_bhyt != 0) { tit += " BHYT"; } if (i_sunghiep != 0) { tit += " SỰ NGHIỆP"; } tit += " SỬ DỤNG TRONG BỆNH VIỆN"; ds = d.get_data(sql); if (ds.Tables[0].Rows.Count > 0) { ds.WriteXml("..//..//dataxml//dmbd.xml", XmlWriteMode.WriteSchema); frmReport f = new frmReport(d, ds.Tables[0], i_userid, tenfile, tit, "", "", "", "", "", "", "", "", ""); f.ShowDialog(); } }
private void butIn_Click(object sender, System.EventArgs e) { Cursor = Cursors.WaitCursor; ds.Clear(); s_makho = ""; for (int i = 0; i < makho.Items.Count; i++) { if (makho.GetItemChecked(i)) { s_makho += dtkho.Rows[i]["id"].ToString().Trim() + ","; } } 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 += "1 as songay,"; sql += "i.stt,b.soluong*t.giamua as sotien,a.congkham"; sql += " from xxx.bhytkb a inner join xxx.bhytthuoc b on a.id=b.id "; sql += " inner join xxx.bhytds c on a.mabn=c.mabn "; sql += " inner join xxx.d_theodoi t on b.sttt=t.id "; sql += " inner join " + user + ".d_dmbd d on b.mabd=d.id "; sql += " inner join " + user + ".d_dmnhom e on d.manhom=e.id "; sql += " inner join " + user + ".v_nhomvp f on e.nhomvp=f.ma "; sql += " left join " + user + ".btdkp_bv h on a.makp=h.makp "; sql += " inner join " + user + ".v_nhombhyt i on f.idnhombhyt=i.id "; sql += " where a.maphu=" + int.Parse(madoituong.SelectedValue.ToString()); sql += " and a.ngay between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")"; if (sothe.Text != "") { sql += " and substr(a.sothe," + Convert.ToInt16(vitri.Value) + "," + sothe.Text.Trim().Length + ")='" + sothe.Text.Trim() + "'"; } if (s_makho != "") { sql += " and b.makho in (" + s_makho.Substring(0, s_makho.Length - 1) + ")"; } sql += " and a.nhom=" + i_nhom; 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) + "')"; } } 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 += "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 "; sql += " inner join xxx.bhytds c on a.mabn=c.mabn "; sql += " inner join " + user + ".v_giavp d on b.mavp=d.id "; sql += " inner join " + user + ".v_loaivp e on d.id_loai=e.id "; sql += " inner join " + user + ".v_nhomvp f on e.id_nhom=f.ma "; sql += " left join " + user + ".btdkp_bv h on a.makp=h.makp "; sql += " inner join " + user + ".v_nhombhyt i on f.idnhombhyt=i.id "; sql += " where a.maphu=" + int.Parse(madoituong.SelectedValue.ToString()); sql += " and a.ngay between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")"; if (sothe.Text != "") { sql += " and substr(a.sothe," + Convert.ToInt16(vitri.Value) + "," + sothe.Text.Trim().Length + ")='" + sothe.Text.Trim() + "'"; } sql += " and a.nhom=" + i_nhom; 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) { sql = "id=" + decimal.Parse(r["id"].ToString()); 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(); 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()); } } } 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 { frmReport f = new frmReport(d, dsxml.Tables[0], i_userid, "rptkcbbhytngtr.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 printer(int toa, long l_id) { sql = "select a.stt,a.sttt,a.mabd,b.ma,trim(b.ten)||' '||b.hamluong ten,b.tenhc,b.dang,e.ten tenkho,c.ten tennguon,d.ten tennhomcc,a.handung,a.losx,a.soluong,round(a.sotien/a.soluong,3) dongia,a.sotien,a.giaban,a.makho,a.manguon,a.nhomcc,a.soluong*a.giaban sotienban,a.soluong soluongcu,a.sotien sotiencu "; sql += " from d_ngtruct a," + d.user + ".d_dmbd b," + d.user + ".d_dmnguon c," + d.user + ".d_nhomcc d," + d.user + ".d_dmkho e where a.mabd=b.id and a.manguon=c.id and a.nhomcc=d.id and a.makho=e.id and a.id=" + l_id + " order by a.stt"; DataTable dtct = d.get_data(s_mmyy, sql).Tables[0]; DataTable dt = d.get_data(s_mmyy, "select lanin from d_ngtrull where id=" + l_id).Tables[0]; int lanin = (dt.Rows.Count > 0)?int.Parse(dt.Rows[0]["lanin"].ToString()):0; if (lanin > 0) { lanin++; if (MessageBox.Show("Lần in thứ " + lanin.ToString() + "\nBạn có muốn in ?", d.Msg, MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No) { return; } } DataTable ldt = dtct.Copy(); ldt.Columns.Add("lien"); int i_lien = 1; string d_tenrpt = (i_lien > 1)?"d_xuatban.rpt":"d_xuatban_a5.rpt"; string s_c8 = (lanin > 1)?"Lần in thứ " + lanin.ToString():""; tongcong_n_lien(ldt, i_lien); decimal d_sotoa = Convert.ToDecimal(toa); string tmp_ngaygio = s_ngay + " " + DateTime.Now.Hour.ToString().PadLeft(2, '0') + ":" + DateTime.Now.Minute.ToString().PadLeft(2, '0'); string s_toa = d_sotoa.ToString("###,###") + " (" + tmp_ngaygio + " - " + s_loai + ")"; string s_chu = doiso.Doiso_Unicode(Convert.ToInt64(d_tongcong.ToString()).ToString()); DataRow r = d.getrowbyid(dtll, "id=" + l_id); if (r != null) { r["done"] = 1; } d.execute_data(s_mmyy, "update d_ngtrull set lanin=lanin+1,done=1 where id=" + l_id); if (d.bPreview) { frmReport f = new frmReport(d, ldt, d_tenrpt, s_toa, hoten, "", "", s_ngay, s_userid, s_chu, s_c8, "", ""); f.ShowDialog(); } else { try { ReportDocument oRpt = new ReportDocument(); oRpt.Load("..\\..\\..\\report\\" + d_tenrpt, OpenReportMethod.OpenReportByTempCopy); oRpt.SetDataSource(ldt); oRpt.DataDefinition.FormulaFields["soyte"].Text = "'" + d.Syte + "'"; oRpt.DataDefinition.FormulaFields["benhvien"].Text = "'" + d.Tenbv + "'"; oRpt.DataDefinition.FormulaFields["c1"].Text = "'" + s_toa + "'"; oRpt.DataDefinition.FormulaFields["c2"].Text = "'" + hoten + "'"; oRpt.DataDefinition.FormulaFields["c3"].Text = ""; oRpt.DataDefinition.FormulaFields["c4"].Text = ""; oRpt.DataDefinition.FormulaFields["c5"].Text = "'" + s_ngay + "'"; oRpt.DataDefinition.FormulaFields["c6"].Text = "'" + s_userid + "'"; oRpt.DataDefinition.FormulaFields["c7"].Text = "'" + s_chu + "'"; oRpt.DataDefinition.FormulaFields["c8"].Text = "'" + s_c8 + "'"; oRpt.DataDefinition.FormulaFields["c9"].Text = ""; oRpt.DataDefinition.FormulaFields["c10"].Text = ""; oRpt.DataDefinition.FormulaFields["giamdoc"].Text = ""; oRpt.DataDefinition.FormulaFields["phutrach"].Text = ""; oRpt.DataDefinition.FormulaFields["thongke"].Text = ""; oRpt.DataDefinition.FormulaFields["ketoan"].Text = ""; oRpt.DataDefinition.FormulaFields["thukho"].Text = ""; oRpt.DataDefinition.FormulaFields["l_soluong"].Text = i_soluong_le.ToString(); oRpt.DataDefinition.FormulaFields["l_dongia"].Text = i_dongia_le.ToString(); oRpt.DataDefinition.FormulaFields["l_thanhtien"].Text = i_thanhtien_le.ToString(); //oRpt.PrintOptions.PaperSize=PaperSize.PaperA4; //oRpt.PrintOptions.PaperOrientation=PaperOrientation.Portrait; if (d.bPrintDialog) { result = Thongso(); if (result == DialogResult.OK) { oRpt.PrintOptions.PrinterName = p.PrinterSettings.PrinterName; oRpt.PrintToPrinter(p.PrinterSettings.Copies, false, p.PrinterSettings.FromPage, p.PrinterSettings.ToPage); } } else { oRpt.PrintToPrinter(1, false, 0, 0); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); return; } } }
private void print() { ds.Clear(); string s_tenquay = "", s_tenkho = ""; s_quay = ""; if (quay.CheckedItems.Count > 0) { for (int i = 0; i < quay.Items.Count; i++) { if (quay.GetItemChecked(i)) { s_quay += dtquay.Rows[i]["id"].ToString() + ","; s_tenquay += dtquay.Rows[i][(bUser)?"hoten":"ten"].ToString() + ","; } } } s_kho = ""; if (kho.CheckedItems.Count > 0) { for (int i = 0; i < kho.Items.Count; i++) { if (kho.GetItemChecked(i)) { s_kho += dtdmkho.Rows[i]["id"].ToString() + ","; s_tenkho += dtdmkho.Rows[i]["ten"].ToString() + ","; } } } string s_title = "Từ ngày " + tu.Text + " đến ngày " + den.Text; if (tu.Text == den.Text) { s_title = "Ngày " + tu.Text; } if (rb3.Checked) { s_title += " (HÓA ĐƠN TÀI CHÍNH)"; } DateTime dt1 = d.StringToDate(tu.Text).AddDays(-d.iNgaykiemke); DateTime dt2 = d.StringToDate(den.Text).AddDays(d.iNgaykiemke); int y1 = dt1.Year, m1 = dt1.Month; int y2 = dt2.Year, m2 = dt2.Month; int itu, iden; string mmyy = ""; DataRow r1, r2, r3; DataRow [] dr; //string gia = (chkXml.Checked) ? "gianovat" : "giamua"; for (int i = y1; i <= y2; i++) { itu = (i == y1)?m1:1; iden = (i == y2)?m2:12; for (int j = itu; j <= iden; j++) { mmyy = j.ToString().PadLeft(2, '0') + i.ToString().Substring(2, 2); if (d.bMmyy(mmyy)) { xxx = user + mmyy; //gia = (chkXml.Checked) ? "gianovat" : "giamua"; if (!hoantra.Checked) { sql = " select b.mabd,b.giaban,"; if (chkGiamua.Checked) { sql += "t.giamua,t.gianovat,"; } else { sql += "0 as giamua,0 as gianovat,"; } sql += "sum(b.soluong) as soluong,sum(b.soluong*b.giaban) as sotien"; sql += " from " + xxx + ".d_ngtrull a," + xxx + ".d_ngtruct b," + xxx + ".d_theodoi t "; sql += " where a.id=b.id and b.sttt=t.id and b.paid=1 and b.idttrv=0"; if (s_quay != "") { if (bUser) { sql += " and a.userid in (" + s_quay.Substring(0, s_quay.Length - 1) + ")"; } else { sql += " and a.loai in (" + s_quay.Substring(0, s_quay.Length - 1) + ")"; } } if (s_kho != "") { sql += " and b.makho in (" + s_kho.Substring(0, s_kho.Length - 1) + ")"; } sql += " and a.ngay between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")"; if (rb2.Checked) { sql += " and a.id not in (select id from " + xxx + ".d_bienlai)"; } else if (rb3.Checked) { sql += " and a.id in (select id from " + xxx + ".d_bienlai)"; } sql += " group by b.mabd,b.giaban"; if (chkGiamua.Checked) { sql += ",t.giamua,t.gianovat"; } foreach (DataRow r in d.get_data(sql).Tables[0].Rows) { r3 = d.getrowbyid(dtdm, "id=" + int.Parse(r["mabd"].ToString())); if (r3 != null) { sql = "mabd=" + int.Parse(r["mabd"].ToString()) + " and giaban=" + decimal.Parse(r["giaban"].ToString()); if (chkGiamua.Checked) { sql += " and giamua=" + decimal.Parse(r["giamua"].ToString()) + " and gianovat=" + decimal.Parse(r["gianovat"].ToString()); } r1 = d.getrowbyid(ds.Tables[0], sql); if (r1 == null) { r2 = ds.Tables[0].NewRow(); r2["tennhom"] = (rnhom.Checked) ? r3["tennhom"].ToString() : (rloai.Checked) ? r3["tenloai"].ToString() : r3["tenbaocao"].ToString(); r2["mabd"] = r["mabd"].ToString(); r2["ten"] = r3["ten"].ToString().Trim() + " " + r3["hamluong"].ToString(); r2["dang"] = r3["dang"].ToString(); r2["soluong"] = r["soluong"].ToString(); r2["gianovat"] = r["gianovat"].ToString(); r2["giamua"] = r["giamua"].ToString(); r2["giaban"] = r["giaban"].ToString(); r2["sotien"] = r["sotien"].ToString(); ds.Tables[0].Rows.Add(r2); } else { dr = ds.Tables[0].Select(sql); if (dr.Length > 0) { dr[0]["soluong"] = decimal.Parse(dr[0]["soluong"].ToString()) + decimal.Parse(r["soluong"].ToString()); dr[0]["sotien"] = decimal.Parse(dr[0]["sotien"].ToString()) + decimal.Parse(r["sotien"].ToString()); } } } } } if (chkHoantra.Checked || hoantra.Checked) { //gia = (chkXml.Checked) ? "dongia" : "giamua"; sql = " select b.mabd,b.giaban,"; if (chkGiamua.Checked) { sql += "b.giamua,b.dongia as gianovat,"; } else { sql += "0 as giamua,0 as gianovat,"; } if (hoantra.Checked) { sql += "sum(b.soluong) as soluong,sum(b.soluong*b.giaban) as sotien"; } else { sql += "sum(-1*b.soluong) as soluong,sum(-1*b.soluong*b.giaban) as sotien"; } sql += " from " + xxx + ".d_nhapll a," + xxx + ".d_nhapct b "; sql += " where a.id=b.id and a.loai='N'"; if (s_quay != "") { if (bUser) { sql += " and a.userid in (" + s_quay.Substring(0, s_quay.Length - 1) + ")"; } else { sql += " and a.lydo in (" + s_quay.Substring(0, s_quay.Length - 1) + ")"; } } if (s_kho != "") { sql += " and a.makho in (" + s_kho.Substring(0, s_kho.Length - 1) + ")"; } sql += " and a.ngaysp between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")"; sql += " group by b.mabd,b.giaban"; if (chkGiamua.Checked) { sql += ",b.giamua,b.dongia"; } foreach (DataRow r in d.get_data(sql).Tables[0].Rows) { r3 = d.getrowbyid(dtdm, "id=" + int.Parse(r["mabd"].ToString())); if (r3 != null) { sql = "mabd=" + int.Parse(r["mabd"].ToString()) + " and giaban=" + decimal.Parse(r["giaban"].ToString()); if (chkGiamua.Checked) { sql += " and giamua=" + decimal.Parse(r["giamua"].ToString()) + " and gianovat=" + decimal.Parse(r["gianovat"].ToString()); } r1 = d.getrowbyid(ds.Tables[0], sql); if (r1 == null) { r2 = ds.Tables[0].NewRow(); r2["tennhom"] = (rnhom.Checked) ? r3["tennhom"].ToString() : (rloai.Checked) ? r3["tenloai"].ToString() : r3["tenbaocao"].ToString(); r2["mabd"] = r["mabd"].ToString(); r2["ten"] = r3["ten"].ToString().Trim() + " " + r3["hamluong"].ToString(); r2["dang"] = r3["dang"].ToString(); r2["soluong"] = r["soluong"].ToString(); r2["gianovat"] = r["gianovat"].ToString(); r2["giamua"] = r["giamua"].ToString(); r2["giaban"] = r["giaban"].ToString(); r2["sotien"] = r["sotien"].ToString(); ds.Tables[0].Rows.Add(r2); } else { dr = ds.Tables[0].Select(sql); if (dr.Length > 0) { dr[0]["soluong"] = decimal.Parse(dr[0]["soluong"].ToString()) + decimal.Parse(r["soluong"].ToString()); dr[0]["sotien"] = decimal.Parse(dr[0]["sotien"].ToString()) + decimal.Parse(r["sotien"].ToString()); } } } } } } } } if (ds.Tables[0].Rows.Count == 0) { MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), d.Msg); } else { tc = 0; foreach (DataRow r in ds.Tables[0].Rows) { tc += decimal.Parse(r["sotien"].ToString()); } dsxml.Clear(); dsxml.Merge(ds.Tables[0].Select("true", "tennhom,ten")); if (chkXml.Checked) { if (!System.IO.Directory.Exists("..\\xml")) { System.IO.Directory.CreateDirectory("..\\xml"); } dsxml.WriteXml("..\\xml\\banhang.xml", XmlWriteMode.WriteSchema); } frmReport f1 = new frmReport(d, dsxml.Tables[0], i_userid, (chkGiamua.Checked) ? "d_banhang_gmua.rpt" : "d_banhang.rpt", "", s_title, s_tenquay, s_tenkho, (hoantra.Checked) ? "BẢNG KÊ HOÀN TRẢ" : "BẢNG KÊ XUẤT BÁN HÀNG", "", "", "", "", doiso.Doiso_Unicode(Convert.ToInt64(tc).ToString())); f1.ShowDialog(this); } }
private void kiemtra_toa() { string s_cond = ""; if (quay.SelectedIndex >= 0) { if (bUser) { s_cond += " and a.userid=" + quay.SelectedValue.ToString(); } else { s_cond += " and a.loai=" + quay.SelectedValue.ToString(); } } if (r1.Checked) { s_cond += " and a.done=1"; } else if (r2.Checked) { s_cond += " and a.done=0"; } string s_title = "Từ ngày " + tu.Text + " đến ngày " + den.Text; if (tu.Text == den.Text) { s_title = "Ngày " + tu.Text; } string s_rpt = "d_pxtoa_bl_ct.rpt"; if (rb3.Checked) { s_rpt = "d_pxtoa_bl_hd.rpt"; } if (r2.Checked) { s_title = " Số toa thuốc chưa in " + s_title; } else if (r3.Checked) { s_title = " Danh sách toa hủy " + s_title; } // s_kho = ""; if (kho.CheckedItems.Count > 0) { for (int i = 0; i < kho.Items.Count; i++) { if (kho.GetItemChecked(i)) { s_kho += dtdmkho.Rows[i]["id"].ToString() + ","; } } } s_mabs = ""; if (mabs.CheckedItems.Count > 0) { for (int i = 0; i < mabs.Items.Count; i++) { if (mabs.GetItemChecked(i)) { s_mabs += dtbs.Rows[i]["ma"].ToString() + ","; } } } get_dsbn_toa(s_cond); // if (ds.Tables[0].Rows.Count <= 0) { MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), d.Msg, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } else { doiso.Doisototext dd = new doiso.Doisototext(); decimal tc = 0; foreach (DataRow r in ds.Tables[0].Select("sotien>0")) { tc += decimal.Parse(r["sotien"].ToString()); } DataSet tmp = ds.Copy(); if (rb3.Checked) { tmp.Clear(); tmp.Merge(ds.Tables[0].Select("true", "mabn,ma")); } if (chkXML.Checked) { if (!System.IO.Directory.Exists("..\\xml")) { System.IO.Directory.CreateDirectory("..\\xml"); } tmp.WriteXml("..\\xml\\banle.xml", XmlWriteMode.WriteSchema); } frmReport f = new frmReport(d, tmp.Tables[0], i_userid, s_rpt, "", s_title, kho.Text, "", "", "", kho.Text, "", "", dd.Doiso_Unicode(Convert.ToInt64(tc).ToString())); f.ShowDialog(); } }
private void kiemtra_toa_th() { string s_cond = ""; if (quay.SelectedIndex >= 0) { s_cond += " and a.loai=" + quay.SelectedValue.ToString(); } if (r1.Checked) { s_cond += " and a.done=1"; } else if (r2.Checked) { s_cond += " and a.done=0"; } string s_title = "Từ ngày " + tu.Text + " đến ngày " + den.Text; if (tu.Text == den.Text) { s_title = "Ngày " + tu.Text; } string s_rpt = "d_pxtoa_bl.rpt"; if (r2.Checked) { s_title = "Số toa thuốc chưa in " + s_title; } else if (r3.Checked) { s_title = "Số thuốc thuộc các toa đã hủy " + s_title; } // s_kho = ""; if (kho.CheckedItems.Count > 0) { for (int i = 0; i < kho.Items.Count; i++) { if (kho.GetItemChecked(i)) { s_kho += dtdmkho.Rows[i]["id"].ToString() + ","; } } } s_mabs = ""; s_tenbs = ""; if (mabs.CheckedItems.Count > 0) { for (int i = 0; i < mabs.Items.Count; i++) { if (mabs.GetItemChecked(i)) { s_mabs += dtbs.Rows[i]["ma"].ToString() + ","; s_tenbs += dtbs.Rows[i]["hoten"].ToString() + ";"; } } } get_dsbn_toa_th(s_cond); // if (ds.Tables[0].Rows.Count <= 0) { MessageBox.Show( lan.Change_language_MessageText("Không có số liệu !"), d.Msg, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } else { if (r4.Checked) { decimal tc = 0; foreach (DataRow r in ds.Tables[0].Select("sotien>0")) { tc += decimal.Parse(r["sotien"].ToString()); } //tong hop theo tien if (!bUser) { s_rpt = "d_pxtoa_bl_th.rpt"; frmReport f = new frmReport(d, ds.Tables[0], i_userid, s_rpt, "", s_title, kho.Text, "", "", "", kho.Text, "", "", dd.Doiso_Unicode(Convert.ToInt64(tc).ToString())); f.ShowDialog(); } //tong hop theo thuoc s_rpt = "d_pxtoa_bl.rpt"; frmReport f1 = new frmReport(d, ds.Tables[0], i_userid, s_rpt, "", s_title, kho.Text, "", "", "", kho.Text, "", "", dd.Doiso_Unicode(Convert.ToInt64(tc).ToString())); f1.ShowDialog(); } else { decimal tc = 0; DataSet dsxml = new DataSet(); dsxml = ds.Copy(); dsxml.Clear(); DataRow r5, r6; DataRow [] dr; foreach (DataRow r in ds.Tables[0].Select("soluong>0", "ma")) { sql = "ma='" + r["ma"].ToString() + "' and giaban=" + decimal.Parse(r["giaban"].ToString()); r5 = d.getrowbyid(dsxml.Tables[0], sql); if (r5 == null) { r6 = dsxml.Tables[0].NewRow(); r6["ma"] = r["ma"].ToString(); r6["ten"] = r["ten"].ToString(); r6["hamluong"] = r["hamluong"].ToString(); r6["dang"] = r["dang"].ToString(); r6["tenhang"] = r["tenhang"].ToString(); r6["soluong"] = r["soluong"].ToString(); r6["giaban"] = r["giaban"].ToString(); r6["sotien"] = r["sotien"].ToString(); dsxml.Tables[0].Rows.Add(r6); } else { dr = dsxml.Tables[0].Select(sql); if (dr.Length > 0) { dr[0]["soluong"] = decimal.Parse(dr[0]["soluong"].ToString()) + decimal.Parse(r["soluong"].ToString()); } } tc += decimal.Parse(r["soluong"].ToString()) * decimal.Parse(r["giaban"].ToString()); } frmReport f1 = new frmReport(d, dsxml.Tables[0], i_userid, "d_bacsi_thuoc.rpt", s_tenbs, s_title, kho.Text, "", "", "", kho.Text, "", "", dd.Doiso_Unicode(Convert.ToInt64(tc).ToString())); f1.ShowDialog(); } } }
private void kiemtra_toa() { // //string s_cond="and a.ngay between to_date('"+tu.Text+"',"+stime+") and to_date('"+den.Text+"',"+stime+")"; string s_cond = "and to_date(to_char(a.ngay," + stime + ")," + stime + ") between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")"; //if(manguon.SelectedIndex>=0)s_cond+=" and t.manguon="+manguon.SelectedValue.ToString(); string s_title = "Từ ngày " + tu.Text + " đến ngày " + den.Text; if (tu.Text == den.Text) { s_title = "Ngày " + tu.Text; } string s_rpt = "d_pxuatct_kp.rpt"; // s_kho = ""; if (kho.CheckedItems.Count == 0) { for (int i = 0; i < kho.Items.Count; i++) { kho.SetItemCheckState(i, CheckState.Checked); } } for (int i = 0; i < kho.Items.Count; i++) { if (kho.GetItemChecked(i)) { s_kho += dtdmkho.Rows[i]["id"].ToString() + ","; } } string s_pk = "", s_tenpk = ""; if (makp.CheckedItems.Count == 0) { s_pk = ","; s_tenpk = ","; } // for(int i=0;i<makp.Items.Count;i++) makp.SetItemCheckState(i,CheckState.Checked); for (int i = 0; i < makp.Items.Count; i++) { if (makp.GetItemChecked(i)) { s_pk += "'" + dtkp.Rows[i]["makp"].ToString() + "',"; s_tenpk += dtkp.Rows[i]["tenkp"].ToString() + ", "; } } // // DateTime dt1 = d.StringToDate(tu.Text).AddDays(-d.iNgaykiemke); DateTime dt2 = d.StringToDate(den.Text).AddDays(d.iNgaykiemke); int y1 = dt1.Year, m1 = dt1.Month; int y2 = dt2.Year, m2 = dt2.Month; int itu, iden; string mmyy = ""; ds = new DataSet(); for (int i = y1; i <= y2; i++) { itu = (i == y1)?m1:1; iden = (i == y2)?m2:12; for (int j = itu; j <= iden; j++) { mmyy = j.ToString().PadLeft(2, '0') + i.ToString().Substring(2, 2); if (d.bMmyy(mmyy)) { xxx = user + mmyy; //Cap nhat lai table bhytds /* * string s_sql = "insert into " + xxx + ".bhytds select mabn, hoten, namsinh, substr(thon,0,50) from " + user + ".btdbn where mabn in( " + * " select mabn from " + xxx + ".bhytkb where mabn not in(select mabn from " + xxx + ".bhytds))"; * d.execute_data(s_sql); */ if (ds.Tables.Count <= 0) { ds.Tables.Add(get_dsbn_bhyt(mmyy, s_kho.Substring(0, s_kho.Length - 1), s_pk.Substring(0, s_pk.Length - 1), s_cond).Tables[0].Copy()); } else { ds.Merge(get_dsbn_bhyt(mmyy, s_kho.Substring(0, s_kho.Length - 1), s_pk.Substring(0, s_pk.Length - 1), s_cond).Tables[0].Copy()); } } } } // // if (ds.Tables[0].Rows.Count <= 0) { MessageBox.Show(lan.Change_language_MessageText("Không có số liệu."), lan.Change_language_MessageText("BHYT"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } else { doiso.Doisototext dd = new doiso.Doisototext(); decimal tc = 0; foreach (DataRow r in ds.Tables[0].Select("sotien<>0")) { tc += decimal.Parse(r["sotien"].ToString()); } if (System.IO.Directory.Exists("..\\..\\dataxml") == false) { System.IO.Directory.CreateDirectory("..\\..\\dataxml"); } ds.WriteXml("..\\..\\dataxml\\d_pxuatct_kp.xml", XmlWriteMode.WriteSchema);//khuyen 03/03/2014 frmReport f = new frmReport(d, ds.Tables[0], d_userid, s_rpt, "", s_title, kho.Text, s_tenpk.Substring(0, s_tenpk.Trim().Length - 1), "", "", kho.Text, "", "", dd.Doiso_Unicode(Convert.ToInt64(tc).ToString())); f.ShowDialog(); } }
private void butIn_Click(object sender, System.EventArgs e) { frmReport f = new frmReport(d, d.get_data("select b.ma,trim(b.ten)||' '||b.hamluong as ten,b.tenhc,b.dang,a.soluong as tondau,0 as sttondau from " + user + ".d_cosotutruc a," + user + ".d_dmbd b where a.mabd=b.id and a.soluong<>0 and a.mmyy='" + s_mmyy + "'" + " and a.nhom=" + i_nhom + " and a.makp=" + i_makp + " order by b.ten").Tables[0], d_userid, "d_cosotutruc.rpt", this.Text.Trim().ToUpper(), "", "", "", "", "", "", "", "", ""); f.ShowDialog(); }
private void print(bool prn) { s_quay = "'"; if (quay.CheckedItems.Count > 0) { for (int i = 0; i < quay.Items.Count; i++) { if (quay.GetItemChecked(i)) { s_quay += dtquay.Rows[i]["ma"].ToString() + "','"; } } } string s_title = "Từ ngày " + tu.Text + " đến ngày " + den.Text; if (tu.Text == den.Text) { s_title = "Ngày " + tu.Text; } DateTime dt1 = d.StringToDate(tu.Text).AddDays(-d.iNgaykiemke); DateTime dt2 = d.StringToDate(den.Text).AddDays(d.iNgaykiemke); int y1 = dt1.Year, m1 = dt1.Month; int y2 = dt2.Year, m2 = dt2.Month; int itu, iden, be = 0; string mmyy = ""; for (int i = y1; i <= y2; i++) { itu = (i == y1)?m1:1; iden = (i == y2)?m2:12; for (int j = itu; j <= iden; j++) { mmyy = j.ToString().PadLeft(2, '0') + i.ToString().Substring(2, 2); if (d.bMmyy(mmyy)) { xxx = user + mmyy; sql = " select a.mabs,sum(b.soluong*b.giaban) as sotien"; sql += " from " + xxx + ".d_ngtrull a," + xxx + ".d_ngtruct b "; sql += " where a.id=b.id"; if (s_quay.Length > 1) { sql += " and a.mabs in (" + s_quay.Substring(0, s_quay.Length - 2) + ")"; } sql += " and a.ngay between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")"; sql += " group by a.mabs"; if (be == 0) { ds = d.get_data(sql); } else { ds.Merge(d.get_data(sql)); } be++; } } } if (ds.Tables[0].Rows.Count == 0) { MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), d.Msg); } else { dsxml.Clear(); DataRow [] dr; DataRow r1, r2, r3; decimal tc = 0; foreach (DataRow r in ds.Tables[0].Select("sotien>0", "mabs")) { sql = "mabs='" + r["mabs"].ToString() + "'"; r1 = d.getrowbyid(dsxml.Tables[0], sql); if (r1 == null) { r3 = d.getrowbyid(dtquay, "ma='" + r["mabs"].ToString() + "'"); if (r3 != null) { r2 = dsxml.Tables[0].NewRow(); r2["mabs"] = r["mabs"].ToString(); r2["hoten"] = r3["hoten"].ToString(); r2["sotien"] = r["sotien"].ToString(); r2["tenkp"] = ""; r2["trichthuong"] = 0; //decimal.Parse(r["sotien"].ToString())*(tyle.Value/100); dsxml.Tables[0].Rows.Add(r2); } } else { dr = dsxml.Tables[0].Select(sql); if (dr.Length > 0) { dr[0]["sotien"] = decimal.Parse(dr[0]["sotien"].ToString()) + decimal.Parse(r["sotien"].ToString()); } } tc += decimal.Parse(r["sotien"].ToString()); } foreach (DataRow r in dsxml.Tables[0].Rows) { r["trichthuong"] = decimal.Parse(r["sotien"].ToString()) * (tyle.Value / 100); } doiso.Doisototext dd = new doiso.Doisototext(); if (prn) { frmReport f1 = new frmReport(d, dsxml.Tables[0], i_userid, "d_dsbacsi.rpt", "", s_title, "", "", "", "", "", "", "", dd.Doiso_Unicode(Convert.ToInt64(tc).ToString())); f1.ShowDialog(this); } else { d.check_process_Excel(); string tenfile = d.Export_Excel(dsxml, "bacsi"); oxl = new Excel.Application(); owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)); osheet = (Excel._Worksheet)owb.ActiveSheet; oxl.ActiveWindow.DisplayGridlines = true; oxl.ActiveWindow.DisplayZeros = false; osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape; osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4; osheet.PageSetup.LeftMargin = 20; osheet.PageSetup.RightMargin = 20; osheet.PageSetup.TopMargin = 30; osheet.PageSetup.CenterFooter = "Trang : &P/&N"; oxl.Visible = true; } } }
private void butIn_Click(object sender, System.EventArgs e) { ds.Clear(); s_kho = ""; s_tennhom = ""; if (kho.CheckedItems.Count == 0) { for (int i = 0; i < kho.Items.Count; i++) { kho.SetItemCheckState(i, CheckState.Checked); } } for (int i = 0; i < kho.Items.Count; i++) { if (kho.GetItemChecked(i)) { s_kho += dtdmkho.Rows[i]["id"].ToString() + ","; s_tennhom += dtdmkho.Rows[i]["ten"].ToString() + ","; } } s_manhom = ""; for (int i = 0; i < nhom.Items.Count; i++) { if (nhom.GetItemChecked(i)) { s_manhom += dtnhom.Rows[i]["id"].ToString() + ","; } } DateTime dt1 = d.StringToDate(tu.Text).AddDays(-d.iNgaykiemke); DateTime dt2 = d.StringToDate(den.Text).AddDays(d.iNgaykiemke); int y1 = dt1.Year, m1 = dt1.Month; int y2 = dt2.Year, m2 = dt2.Month; int itu, iden; string mmyy = ""; for (int i = y1; i <= y2; i++) { itu = (i == y1)?m1:1; iden = (i == y2)?m2:12; for (int j = itu; j <= iden; j++) { mmyy = j.ToString().PadLeft(2, '0') + i.ToString().Substring(2, 2); if (d.bMmyy(mmyy)) { if (i_nhapxuat == 1) { get_nhap(mmyy); } else { get_xuat(mmyy); get_hoantra(mmyy); } } } } // if (ds.Tables[0].Rows.Count == 0) { MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), d.Msg); return; } get_sort(); if (chkXML.Checked) { dsxml.WriteXml("..//..//dataxml//" + tenfile.Replace(".rpt", ".xml"), XmlWriteMode.WriteSchema); } if (tenfile == "d_phieuxuat.rpt") { decimal d_tongcong = 0; foreach (DataRow r in dsxml.Tables[0].Rows) { d_tongcong += decimal.Parse(r["sotien"].ToString()); } frmReport f = new frmReport(d, dsxml.Tables[0], i_userid, tenfile, "", tu.Text, "", "", "", "Xuất sử dụng", s_tennhom, doiso.Doiso_Unicode(Convert.ToInt64(d_tongcong).ToString()), "", ""); f.ShowDialog(); } else { frmReport f1 = new frmReport(d, dsxml.Tables[0], i_userid, tenfile, (tu.Text == den.Text)?" Ngày :" + tu.Text:"Từ ngày :" + tu.Text + " đến :" + den.Text, (s_tennhom == "")?"":"Kho :" + s_tennhom, (manguon.SelectedIndex == -1)?"":"Nguồn :" + manguon.Text, "", this.Text.Trim().ToUpper(), "", "", "", "", ""); f1.ShowDialog(); } }
private void butIn_Click(object sender, System.EventArgs e) { frmReport f = new frmReport(d, ds.Tables[0], i_userid, (tu.Text == den.Text)?"Ngày :" + tu.Text:"Từ ngày : " + tu.Text + " đến ngày : " + den.Text, "bieu_07.rpt"); f.ShowDialog(this); }
private void print() { bool bBoquaInphieuxuatban = d.bInPhieuxuatban(i_nhom); s_quay = ""; if (quay.CheckedItems.Count > 0) { for (int i = 0; i < quay.Items.Count; i++) { if (quay.GetItemChecked(i)) { s_quay += dtquay.Rows[i]["id"].ToString() + ","; } } } s_kho = ""; if (kho.CheckedItems.Count > 0) { for (int i = 0; i < kho.Items.Count; i++) { if (kho.GetItemChecked(i)) { s_kho += dtdmkho.Rows[i]["id"].ToString() + ","; } } } string s_title = "Từ ngày " + tu.Text + " đến ngày " + den.Text; if (tu.Text == den.Text) { s_title = "Ngày " + tu.Text; } if (rb3.Checked) { s_title += " (HÓA ĐƠN TÀI CHÍNH)"; } DateTime dt1 = d.StringToDate(tu.Text).AddDays(-d.iNgaykiemke); DateTime dt2 = d.StringToDate(den.Text).AddDays(d.iNgaykiemke); int y1 = dt1.Year, m1 = dt1.Month; int y2 = dt2.Year, m2 = dt2.Month; int itu, iden; string mmyy = ""; dsxml.Clear(); tc = 0; for (int i = y1; i <= y2; i++) { itu = (i == y1) ? m1 : 1; iden = (i == y2) ? m2 : 12; for (int j = itu; j <= iden; j++) { mmyy = j.ToString().PadLeft(2, '0') + i.ToString().Substring(2, 2); if (d.bMmyy(mmyy)) { xxx = user + mmyy; sql = " select to_char(a.ngay,'yyyy/mm/dd') as ngay,"; if (bUser && !chkThungan.Checked) { sql += " a.userid as loai,"; } else if (bUser && chkThungan.Checked) { sql += "a.userid_vp as loai,"; } else { sql += "a.loai,"; } sql += "sum(b.soluong*b.giaban) as sotien,0 as sotoa,0 as done,sum(b.soluong*c.giamua) as sotiengiamua,sum(b.soluong*c.gianovat) as sotiennovat"; sql += " from " + xxx + ".d_ngtrull a," + xxx + ".d_ngtruct b," + xxx + ".d_theodoi c "; sql += " where a.id=b.id and b.sttt=c.id and b.idttrv=0"; //khuyen 20/02/14 sql += " and b.paid=1 "; //thanh quan done=1,paid=0 if (!bBoquaInphieuxuatban) { sql += " and done=1"; } if (s_quay != "") { if (bUser) { if (chkThungan.Checked) { sql += " and a.userid_vp in (" + s_quay.Substring(0, s_quay.Length - 1) + ")"; } else { sql += " and a.userid in (" + s_quay.Substring(0, s_quay.Length - 1) + ")"; } } } if (s_kho != "") { sql += " and b.makho in (" + s_kho.Substring(0, s_kho.Length - 1) + ")"; } sql += " and a.ngay between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")"; if (rb2.Checked) { sql += " and a.id not in (select id from " + xxx + ".d_bienlai)"; } else if (rb3.Checked) { sql += " and a.id in (select id from " + xxx + ".d_bienlai)"; } sql += " group by to_char(a.ngay,'yyyy/mm/dd'),"; if (bUser && !chkThungan.Checked) { sql += "a.userid"; } else if (bUser && chkThungan.Checked) { sql += "a.userid_vp"; } else { sql += "a.loai"; } ds = d.get_data(sql); ins_items(); if (chkHoantra.Checked) { sql = " select to_char(a.ngaysp,'yyyy/mm/dd') as ngay,"; sql += (bUser) ? "a.userid as loai," : "a.lydo as loai,"; sql += "sum(-1*b.soluong*b.giaban) as sotien,0 as sotoa,0 as done,sum(-1*b.soluong*c.giamua) as sotiengiamua,sum(-1*b.soluong*c.gianovat) as sotiennovat "; sql += " from " + xxx + ".d_nhapll a," + xxx + ".d_nhapct b," + xxx + ".d_tonkhoct d," + xxx + ".d_theodoi c "; sql += " where a.id=b.id and d.idn=b.id and b.stt=d.sttn and d.stt=c.id and a.loai='N'"; if (s_quay != "") { if (bUser) { sql += " and a.userid in (" + s_quay.Substring(0, s_quay.Length - 1) + ")"; } else { sql += " and a.lydo in (" + s_quay.Substring(0, s_quay.Length - 1) + ")"; } } if (s_kho != "") { sql += " and a.makho in (" + s_kho.Substring(0, s_kho.Length - 1) + ")"; } sql += " and a.ngaysp between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")"; sql += " group by to_char(a.ngaysp,'yyyy/mm/dd'),"; sql += (bUser) ? "a.userid" : "a.lydo"; ds = d.get_data(sql); ins_items(); } if (!bBoquaInphieuxuatban) { //chua in sql = " select to_char(a.ngay,'yyyy/mm/dd') as ngay,"; if (bUser && !chkThungan.Checked) { sql += " a.userid as loai,"; } else if (bUser && chkThungan.Checked) { sql += "a.userid_vp as loai,"; } else { sql += "a.loai,"; } sql += "sum(b.soluong*b.giaban) as sotien,a.sotoa,1 as done,sum(b.soluong*c.giamua) as sotiengiamua,sum(b.soluong*c.gianovat) as sotiennovat "; sql += " from " + xxx + ".d_ngtrull a," + xxx + ".d_ngtruct b," + xxx + ".d_theodoi c "; sql += " where a.id=b.id and b.sttt=c.id "; //khuyen 20/02/14 sql += " and done=0 ";//thanh quan done=1,paid=0 if (s_quay != "") { if (bUser) { sql += " and a.userid in (" + s_quay.Substring(0, s_quay.Length - 1) + ")"; } else { sql += " and a.loai in (" + s_quay.Substring(0, s_quay.Length - 1) + ")"; } } if (s_kho != "") { sql += " and b.makho in (" + s_kho.Substring(0, s_kho.Length - 1) + ")"; } sql += " and a.ngay between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")"; if (rb2.Checked) { sql += " and a.id not in (select id from " + xxx + ".d_bienlai)"; } else if (rb3.Checked) { sql += " and a.id in (select id from " + xxx + ".d_bienlai)"; } sql += " group by to_char(a.ngay,'yyyy/mm/dd'),"; if (bUser && !chkThungan.Checked) { sql += "a.userid,"; } else if (bUser && chkThungan.Checked) { sql += "a.userid_vp,"; } else { sql += "a.loai,"; } sql += " a.sotoa"; ds = d.get_data(sql); ins_items(); } //huy sql = " select to_char(a.ngay,'yyyy/mm/dd') as ngay,"; sql += (bUser) ? "a.userid as loai," : "a.loai as loai,"; sql += "sum(b.soluong*b.giaban) as sotien,a.sotoa,2 as done,sum(b.soluong*c.giamua) as sotiengiamua,sum(b.soluong*c.gianovat) as sotiennovat "; sql += " from " + xxx + ".d_huybanll a," + xxx + ".d_huybanct b," + xxx + ".d_theodoi c "; sql += " where a.id=b.id and b.sttt=c.id and b.paid=1 and b.idttrv=0"; if (s_quay != "") { if (bUser) { sql += " and a.userid in (" + s_quay.Substring(0, s_quay.Length - 1) + ")"; } else { sql += " and a.loai in (" + s_quay.Substring(0, s_quay.Length - 1) + ")"; } } if (s_kho != "") { sql += " and b.makho in (" + s_kho.Substring(0, s_kho.Length - 1) + ")"; } sql += " and a.ngay between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")"; if (rb2.Checked) { sql += " and a.id not in (select id from " + xxx + ".d_bienlai)"; } else if (rb3.Checked) { sql += " and a.id in (select id from " + xxx + ".d_bienlai)"; } sql += " group by to_char(a.ngay,'yyyy/mm/dd'),"; sql += (bUser) ? "a.userid," : "a.loai,"; sql += "a.sotoa"; ds = d.get_data(sql); ins_items(); } } } if (dsxml.Tables[0].Rows.Count == 0) { MessageBox.Show( lan.Change_language_MessageText("Không có số liệu !"), d.Msg); } else { if (!System.IO.Directory.Exists("..\\xml")) { System.IO.Directory.CreateDirectory("..\\xml"); } dsxml.WriteXml("..\\xml\\d_thutien.xml", XmlWriteMode.WriteSchema); doiso.Doisototext dd = new doiso.Doisototext(); frmReport f1 = new frmReport(d, dsxml.Tables[0], i_userid, "d_thutien.rpt", "", s_title, "", "", "", "", "", "", "", dd.Doiso_Unicode(Convert.ToInt64(tc).ToString())); f1.ShowDialog(this); } }
private void print() { Cursor = Cursors.WaitCursor; string s_title = "Từ ngày " + tu.Text + " đến ngày " + den.Text; if (tu.Text == den.Text) { s_title = "Ngày " + tu.Text; } foreach (DataRow r in ds.Tables[0].Rows) { r["vn_st"] = 0; r["vn_sl"] = 0; r["nn_st"] = 0; r["nn_sl"] = 0; r["tongso"] = 0; } dsloai.Clear(); DateTime dt1 = d.StringToDate(tu.Text).AddDays(-d.iNgaykiemke); DateTime dt2 = d.StringToDate(den.Text).AddDays(d.iNgaykiemke); int y1 = dt1.Year, m1 = dt1.Month; int y2 = dt2.Year, m2 = dt2.Month; int itu, iden; for (int i = y1; i <= y2; i++) { itu = (i == y1)?m1:1; iden = (i == y2)?m2:12; for (int j = itu; j <= iden; j++) { mmyy = j.ToString().PadLeft(2, '0') + i.ToString().Substring(2, 2); if (d.bMmyy(mmyy)) { get_xuat_tien(); get_xuat_loai(); } } } #region dem loai thuoc foreach (DataRow r1 in ds.Tables[0].Select("ma<>''")) { foreach (DataRow r in dsloai.Tables[0].Select("nhombo<>0")) { if (r1["ma"].ToString().IndexOf(r["nhombo"].ToString().Trim() + ",") != -1) { r1["vn_sl"] = decimal.Parse(r1["vn_sl"].ToString()) + decimal.Parse(r["vn_sl"].ToString()); r1["nn_sl"] = decimal.Parse(r1["nn_sl"].ToString()) + decimal.Parse(r["nn_sl"].ToString()); } } } DataRow r2 = d.getrowbyid(ds.Tables[0], "ma=''"); if (r2 != null) { r2["vn_sl"] = dsloai.Tables[0].Select("nhombo=0 and vn_sl<>0").Length; r2["nn_sl"] = dsloai.Tables[0].Select("nhombo=0 and nn_sl<>0").Length; } #endregion Cursor = Cursors.Default; if (ds.Tables[0].Rows.Count == 0) { MessageBox.Show( lan.Change_language_MessageText("Không có số liệu !"), d.Msg); } else { frmReport f1 = new frmReport(d, ds.Tables[0], i_userid, "d_bctienthuoc.rpt", "", s_title, "", "", "", "", "", "", "", ""); f1.ShowDialog(this); } }