private void kiemtra_toa() { string s_cond = "and a.ngay 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_treem.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() + ","; } } 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)) { if (ds.Tables.Count <= 0) { ds.Tables.Add(get_dsbn_bhyt(mmyy, s_kho.Substring(0, s_kho.Length - 1), s_cond).Tables[0].Copy()); } else { ds.Merge(get_dsbn_bhyt(mmyy, s_kho.Substring(0, s_kho.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("Trẻ em <6 Tuổi"), 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()); } 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(); } }
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 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 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 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); } }