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