private void ins_items()
{
foreach (DataRow r in d.get_data(sql).Tables[0].Rows)
{
r3 = d.getrowbyid(dt, "id=" + int.Parse(r["mabd"].ToString()));
if (r3 != null)
{
sql = "mabd=" + int.Parse(r["mabd"].ToString()) + " and dongia=" + decimal.Parse(r["dongia"].ToString());
r1 = d.getrowbyid(ds.Tables[0], sql);
if (r1 == null)
{
r2 = ds.Tables[0].NewRow();
r2["stt"] = r3["sttnhom"].ToString();
r2["tennhom"] = r3["tennhom"].ToString();
r2["mabd"] = r["mabd"].ToString();
r2["ma"] = r3["ma"].ToString();
r2["ten"] = r3["ten"].ToString().Trim() + " " + r3["hamluong"].ToString();
r2["dang"] = r3["dang"].ToString();
r2["tenhang"] = r3["tenhang"].ToString();
r2["soluong"] = r["soluong"].ToString();
r2["dongia"] = r["dongia"].ToString();
r2["sotien"] = decimal.Parse(r["soluong"].ToString()) * decimal.Parse(r["dongia"].ToString());
ds.Tables[0].Rows.Add(r2);
}
else
{
dr = ds.Tables[0].Select(sql);
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["soluong"].ToString()) * decimal.Parse(r["dongia"].ToString());
}
}
}
ds.AcceptChanges();
}
private void upd_mabd(string sql)
{
DataRow r2, r3;
//foreach(DataRow r1 in d.get_data(sql).Tables[0].Rows)
foreach (DataRow r1 in m.get_data_mmyy(sql, tu.Text.Substring(0, 10), den.Text.Substring(0, 10), true).Tables[0].Rows)
{
r = d.getrowbyid(dsmabd.Tables[0], "mabd=" + int.Parse(r1["mabd"].ToString()));
if (r == null)
{
r2 = d.getrowbyid(dtdmbd, "id=" + int.Parse(r1["mabd"].ToString()));
if (r2 != null)
{
r3 = dsmabd.Tables[0].NewRow();
r3["mabd"] = r1["mabd"].ToString();
r3["tenbd"] = r2["ten"].ToString().Trim() + " " + r2["hamluong"].ToString().Trim() + " " + r2["dang"].ToString();
r3["soluong"] = 0;
dsmabd.Tables[0].Rows.Add(r3);
}
}
}
DataRow [] dr = dsmabd.Tables[0].Select("true", "tenbd");
for (int i = 0; i < dr.Length; i++)
{
dc = new DataColumn();
dc.ColumnName = "C_" + dr[i]["mabd"].ToString().Trim();
dc.DataType = Type.GetType("System.Decimal");
ds.Tables[0].Columns.Add(dc);
}
}
private void upd_doituong(DataTable dt)
{
DataRow nr = m.getrowbyid(dt, "id=" + int.Parse(stt.Value.ToString()) + " and loai=" + cboLoai.SelectedIndex);
if (nr == null)
{
DataRow r = dt.NewRow();
r["id"] = int.Parse(stt.Value.ToString());
r["ma"] = ma.Text.Trim();
r["ten"] = ten.Text.Trim();
r["loai"] = cboLoai.SelectedIndex;
dt.Rows.Add(r);
}
else
{
nr["id"] = int.Parse(stt.Value.ToString());
nr["ma"] = ma.Text.Trim();
nr["ten"] = ten.Text.Trim();
nr["loai"] = cboLoai.SelectedIndex;
}
dt.AcceptChanges();
}
private void load_treeview()
{
string tu = ttb.DateToString("dd/MM/yyyy", ttb.StringToDate(s_ngay).AddDays(-1.0 * Convert.ToDouble(songay.Value)));
string den = ttb.DateToString("dd/MM/yyyy", ttb.StringToDate(s_ngay));//.AddDays(Convert.ToDouble(songay.Value))
sql = "select c.makp,b.mabd,sum(b.slyeucau-b.slthuc) as soluong, c.loai,c.phieu, f.ten as tenphieu,to_char(c.ngay,'dd/mm/yyyy') as ngay ";
sql += " from " + xxx + ".ttb_haophill a," + xxx + ".ttb_haophict b," + xxx + ".ttb_duyet c," + user + ".ttb_dmbd d," + user + ".ttb_dmphieu e, " + user + ".ttb_loaiphieu f ";
sql += " where a.id=b.id and a.idduyet=c.id and b.mabd=d.id and c.loai=e.id and c.phieu=f.id ";
sql += " and c.nhom=" + i_nhom + " and c.loai=" + i_loai;
sql += " and to_date(to_char(c.ngay,'dd/mm/yyyy'),'dd/mm/yyyy') between to_date('" + tu + "','dd/mm/yyyy') and to_date('" + den + "','dd/mm/yyyy')";
if (s_kho != "")
{
sql += " and b.makho in (" + s_kho.Substring(0, s_kho.Length - 1) + ")";
}
sql += " and c.done<>0 and b.slyeucau>b.slthuc and b.tutruc=0 group by c.makp,b.mabd,c.loai,c.phieu, f.ten,c.ngay order by c.makp,c.loai,c.phieu,b.mabd";
treeView1.Nodes.Clear();
TreeNode node = new TreeNode();
TreeNode node2 = new TreeNode();
DataTable dtngay = ttb.get_data(sql).Tables[0];
DataRow [] dr;
i_makp = 0;
string s_makp = "";
string str4 = "";
foreach (DataRow r1 in dtngay.Select("true", "makp,ngay,loai,phieu,mabd"))
{
//if (s_makp!=r1["makp"].ToString()+r1["loai"].ToString()+r1["phieu"].ToString())
//{
// i_makp=int.Parse(r1["makp"].ToString());
// s_makp=r1["makp"].ToString()+r1["loai"].ToString()+r1["phieu"].ToString();
// r=ttb.getrowbyid(dtkp,"id="+i_makp);
// if (r!=null)
// {
// node=treeView1.Nodes.Add(r["ten"].ToString()+" - "+r1["tenphieu"].ToString());
// dr=dtngay.Select("makp="+i_makp+" and loai="+r1["loai"].ToString()+" and phieu="+r1["phieu"].ToString());
// for(int i=0;i<dr.Length;i++)
// {
// r=ttb.getrowbyid(dtdmbd,"id="+int.Parse(dr[i]["mabd"].ToString()));
// if (r!=null) node.Nodes.Add(r["ten"].ToString().Trim()+"/"+r["tenhc"].ToString().Trim()+" "+r["dang"].ToString().Trim()+" ("+dr[i]["soluong"].ToString().Trim()+")");
// }
// }
//}
if (i_makp != int.Parse(r1["makp"].ToString()))
{
i_makp = int.Parse(r1["makp"].ToString());
r = d.getrowbyid(dtkp, "id=" + i_makp);
if (r != null)
{
node = treeView1.Nodes.Add(r["ten"].ToString());
}
str4 = "";
}
if (str4 != (r1["ngay"].ToString() + r1["loai"].ToString() + r1["phieu"].ToString()))
{
str4 = r1["ngay"].ToString() + r1["loai"].ToString() + r1["phieu"].ToString();
node2 = node.Nodes.Add(r1["ngay"].ToString().Substring(0, 5) + " - " + r1["tenphieu"].ToString());
DataRow[] rowArray = dtngay.Select(string.Concat(new object[] { "makp=", i_makp, " and ngay='", r1["ngay"].ToString(), "' and loai=", r1["loai"].ToString(), " and phieu=", r1["phieu"].ToString() }));
for (int i = 0; i < rowArray.Length; i++)
{
r = d.getrowbyid(dtdmbd, "id=" + int.Parse(rowArray[i]["mabd"].ToString()));
if (r != null)
{
node2.Nodes.Add(r["ten"].ToString().Trim() + "/" + r["tenhc"].ToString().Trim() + " " + r["dang"].ToString().Trim() + " (" + rowArray[i]["soluong"].ToString().Trim() + ")");
}
}
}
}
}
private void butIn_Click(object sender, System.EventArgs e)
{
/*
* xxx.bhytkb a,xxx.bhytthuoc b,xxx.bhytds c,"+user+".d_dmbd d,"+user+".d_dmnhom e,"+user+".v_nhomvp f,"+user+".btdkp_bv h,"+user+".v_nhombhyt i
*
* a.id=b.id and a.mabn=c.mabn and b.mabd=d.id and d.manhom=e.id and e.nhomvp=f.ma and a.makp=h.makp(+) and f.idnhombhyt=i.id
* */
Cursor = Cursors.WaitCursor;
ds.Clear();
dsts.Clear();
sql = "select a.id,a.mabn,c.hoten,a.sothe,h.tenkp,a.chandoan,a.maicd,";
sql += "to_char(a.ngay,'dd/mm/yy') as ngayra,to_char(a.ngay,'dd/mm/yy') as ngayvao,";
sql += "" + m.for_num_ngay("a.ngay") + "-" + m.for_num_ngay("a.ngay") + "+1 as songay,";
sql += "i.stt,b.soluong*j.giamua as sotien,a.congkham";
sql += " from xxx.bhytkb a inner join xxx.bhytthuoc b on a.id=b.id inner join xxx.bhytds c on a.mabn=c.mabn inner join " + user + ".d_dmbd d on b.mabd=d.id inner join " + user + ".d_dmnhom e on d.manhom=e.id inner join " + user + ".v_nhomvp f on e.nhomvp=f.ma left join " + user + ".btdkp_bv h on a.makp=h.makp inner join " + user + ".v_nhombhyt i on f.idnhombhyt=i.id inner join xxx.d_theodoi j on b.sttt=j.id ";
sql += " where ";
sql += " a.maphu=" + int.Parse(madoituong.SelectedValue.ToString());
sql += " and " + m.for_ngay("a.ngay", "'dd/mm/yyyy'") + " between to_date('" + tu.Text + "','dd/mm/yyyy') and to_date('" + den.Text + "','dd/mm/yyyy')";
if (sothe.Text != "")
{
sql += " and substr(a.sothe," + Convert.ToInt16(vitri.Value) + "," + sothe.Text.Trim().Length + ")='" + sothe.Text.Trim() + "'";
}
sql += " and a.sothe is not null";
if (s_tunguyen != "" && loaidt.SelectedIndex != 0)
{
if (loaidt.SelectedIndex == 1)
{
sql += " and substr(a.sothe," + v1 + "," + v2 + ") not in ('" + s_tunguyen.Substring(0, s_tunguyen.Length) + "')";
}
else if (loaidt.SelectedIndex == 2)
{
sql += " and substr(a.sothe," + v1 + "," + v2 + ") in ('" + s_tunguyen.Substring(0, s_tunguyen.Length) + "')";
}
}
/*
* xxx.bhytkb a,xxx.bhytcls b,xxx.bhytds c,"+user+".v_giavp d,"+user+".v_loaivp e,"+user+".v_nhomvp f,"+user+".btdkp_bv h,"+user+".v_nhombhyt i
*
* a.id=b.id and a.mabn=c.mabn and b.mavp=d.id and d.id_loai=e.id and e.id_nhom=f.ma and a.makp=h.makp(+) and f.idnhombhyt=i.id
* */
sql += " union all ";
sql += "select a.id,a.mabn,c.hoten,a.sothe,h.tenkp,a.chandoan,a.maicd,";
sql += "to_char(a.ngay,'dd/mm/yy') as ngayra,to_char(a.ngay,'dd/mm/yy') as ngayvao,";
sql += "" + m.for_num_ngay("a.ngay") + "-" + m.for_num_ngay("a.ngay") + "+1 as songay,";
sql += "i.stt,b.soluong*b.dongia as sotien,a.congkham";
sql += " from xxx.bhytkb a inner join xxx.bhytcls b on a.id=b.id inner join xxx.bhytds c on a.mabn=c.mabn inner join " + user + ".v_giavp d on b.mavp=d.id inner join " + user + ".v_loaivp e on d.id_loai=e.id inner join " + user + ".v_nhomvp f on e.id_nhom=f.ma left join " + user + ".btdkp_bv h on a.makp=h.makp inner join " + user + ".v_nhombhyt i on f.idnhombhyt=i.id ";
sql += " where ";
sql += " a.maphu=" + int.Parse(madoituong.SelectedValue.ToString());
sql += " and " + m.for_ngay("a.ngay", "'dd/mm/yyyy'") + " between to_date('" + tu.Text + "','dd/mm/yyyy') and to_date('" + den.Text + "','dd/mm/yyyy')";
if (sothe.Text != "")
{
sql += " and substr(a.sothe," + Convert.ToInt16(vitri.Value) + "," + sothe.Text.Trim().Length + ")='" + sothe.Text.Trim() + "'";
}
sql += " and a.sothe is not null";
if (s_tunguyen != "" && loaidt.SelectedIndex != 0)
{
if (loaidt.SelectedIndex == 1)
{
sql += " and substr(a.sothe," + v1 + "," + v2 + ") not in ('" + s_tunguyen.Substring(0, s_tunguyen.Length) + "')";
}
else if (loaidt.SelectedIndex == 2)
{
sql += " and substr(a.sothe," + v1 + "," + v2 + ") in ('" + s_tunguyen.Substring(0, s_tunguyen.Length) + "')";
}
}
System.Data.DataTable tmp = d.get_thuoc(tu.Text, den.Text, sql).Tables[0];
foreach (DataRow r in tmp.Rows)
{
r1 = d.getrowbyid(dsts.Tables[0], "id=" + decimal.Parse(r["id"].ToString()));
if (r1 == null)
{
r2 = dsts.Tables[0].NewRow();
r2["id"] = r["id"].ToString();
r2["sothe"] = r["sothe"].ToString();
dsts.Tables[0].Rows.Add(r2);
}
sql = "sothe='" + r["sothe"].ToString().Trim() + "'";
r1 = d.getrowbyid(ds.Tables[0], sql);
if (r1 == null)
{
r2 = ds.Tables[0].NewRow();
r2["id"] = r["id"].ToString();
if (r["sothe"].ToString().Trim().Length > 6)
{
r2["stt"] = (r["sothe"].ToString().Substring(2, 2) == s_thetrongtinh)?0:1;
r2["tinh"] = (r["sothe"].ToString().Substring(2, 2) == s_thetrongtinh)?"TỈNH":"KHÁC TỈNH";
r2["sothe1"] = (s_thetunguyen.IndexOf(r["sothe"].ToString().Substring(4, 2)) == -1)?"0":"1";
r2["doituong"] = (s_thetunguyen.IndexOf(r["sothe"].ToString().Substring(4, 2)) == -1)?"BẮT BUỘC":"TỰ NGUYỆN";
r2["sothe2"] = r["sothe"].ToString().Substring(2, 2);
r2["sothe3"] = r["sothe"].ToString().Substring(4, 2);
}
r2["sothe"] = r["sothe"].ToString().Trim();
r2["mabn"] = r["mabn"].ToString();
r2["hoten"] = r["hoten"].ToString();
r2["tenkp"] = r["tenkp"].ToString();
r2["chandoan"] = r["chandoan"].ToString();
r2["maicd"] = r["maicd"].ToString();
r2["ngayvao"] = r["ngayvao"].ToString();
r2["ngayra"] = r["ngayra"].ToString();
r2["songay"] = r["songay"].ToString();
foreach (DataRow r3 in dtnhom.Select("true", "stt"))
{
r2["c" + r3["stt"].ToString().Trim()] = 0;
}
r2["congkham"] = r["congkham"].ToString();
r2["c" + r["stt"].ToString().Trim()] = r["sotien"].ToString();
r2["tc"] = decimal.Parse(r["sotien"].ToString()) + decimal.Parse(r["congkham"].ToString());
ds.Tables[0].Rows.Add(r2);
}
else
{
dr = ds.Tables[0].Select(sql);
if (dr.Length > 0)
{
dr[0]["c" + r["stt"].ToString().Trim()] = decimal.Parse(dr[0]["c" + r["stt"].ToString().Trim()].ToString()) + decimal.Parse(r["sotien"].ToString());
dr[0]["tc"] = decimal.Parse(dr[0]["tc"].ToString()) + decimal.Parse(r["sotien"].ToString());
}
}
}
foreach (DataRow r in ds.Tables[0].Rows)
{
r["soluot"] = dsts.Tables[0].Select("sothe='" + r["sothe"].ToString() + "'").Length;
r["songay"] = dsts.Tables[0].Select("sothe='" + r["sothe"].ToString() + "'").Length;
}
dsxml = new DataSet();
dsxml = ds.Copy();
dsxml.Clear();
dsxml.Merge(ds.Tables[0].Select("true", "stt,sothe1,sothe3,sothe2,sothe,ngayvao,mabn"));
dsxml.WriteXml("..//..//..//xml//t_thkcbbhyt.xml", XmlWriteMode.WriteSchema);
Cursor = Cursors.Default;
if (dsxml.Tables[0].Rows.Count == 0)
{
MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), LibMedi.AccessData.Msg);
}
else
{
dllReportM.frmReport f = new dllReportM.frmReport(m, dsxml.Tables[0], "rptthkcbbhytngtr.rpt", (tu.Text == den.Text)?"Ngày " + tu.Text:"Từ ngày " + tu.Text + " đến " + den.Text, madoituong.Text, loaidt.Text, "", "", "", "", "", "", "");
f.ShowDialog();
}
}
