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