private void load_grid()
{
ds.Clear();
sql = "select to_char(a.ngay,'yyyymmdd') as yyyymmdd,to_char(a.ngay,'dd/mm/yyyy') as ngay,a.mabn,d.hoten,c.ten,e.tenkp,f.hoten as tenbs,a.soluong as c01,a.soluong*(a.dongia+a.vattu) as c02,a.paid ";
sql += " from xxx.v_chidinh a inner join " + user + ".v_giavp c on a.mavp=c.id ";
sql += " inner join " + user + ".v_loaivp b on c.id_loai=b.id ";
sql += " inner join " + user + ".btdbn d on a.mabn=d.mabn ";
sql += " left join " + user + ".btdkp_bv e on a.makp=e.makp ";
//sql += " left join xxx.benhanpk g on a.maql=g.maql ";
sql += " left join (select maql,mabs from xxx.benhanpk union all select maql,mabs from xxx.benhancc ) g on a.maql=g.maql ";//gam 21/10/2011
sql += " left join " + user + ".dmbs f on g.mabs=f.ma";
sql += " where " + m.for_ngay("a.ngay", stime) + " between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")";
if (tenloai.SelectedIndex != -1)
{
sql += " and b.id=" + int.Parse(tenloai.SelectedValue.ToString());
}
if (tenkp.SelectedIndex != -1)
{
sql += " and a.makp='" + tenkp.SelectedValue.ToString() + "'";
}
if (madoituong.SelectedIndex != -1)
{
sql += " and a.madoituong=" + int.Parse(madoituong.SelectedValue.ToString());
}
sql += " and " + cont;
ds.Merge(v.get_vienphi(tu.Text, den.Text, sql).Tables[0].Select("true", "yyyymmdd,mabn"));
}
private void load_grid()
{
ds.Clear();
DataRow r2, r3;
DataRow [] dr;
sql = "select b.stt,c.ten,a.mavp,a.paid,a.done,sum(a.soluong) as soluong,sum(a.soluong*(a.dongia+a.vattu)) as sotien ";
sql += " from xxx.v_chidinh a," + user + ".v_loaivp b," + user + ".v_giavp c where a.mavp=c.id and c.id_loai=b.id ";
sql += " and " + m.for_ngay("a.ngay", stime) + " between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")";
if (tenloai.SelectedIndex != -1)
{
sql += " and b.id=" + int.Parse(tenloai.SelectedValue.ToString());
}
if (tenkp.SelectedIndex != -1)
{
sql += " and a.makp='" + tenkp.SelectedValue.ToString() + "'";
}
if (madoituong.SelectedIndex != -1)
{
sql += " and a.madoituong=" + int.Parse(madoituong.SelectedValue.ToString());
}
sql += " group by b.stt,c.ten,a.mavp,a.paid,a.done";
foreach (DataRow r in v.get_vienphi(tu.Text, den.Text, sql).Tables[0].Select("true", "stt,ten"))
{
r2 = m.getrowbyid(ds.Tables[0], "mavp=" + int.Parse(r["mavp"].ToString()));
if (r2 == null)
{
r3 = ds.Tables[0].NewRow();
r3["mavp"] = r["mavp"].ToString();
r3["ten"] = r["ten"].ToString();
r3["c01"] = r["soluong"].ToString();
r3["c02"] = 0;
r3["c03"] = 0;
r3["c04"] = 0;
if (r["paid"].ToString() == "1")
{
r3["c02"] = r["soluong"].ToString();
r3["c03"] = r["sotien"].ToString();
}
if (r["done"].ToString() == "1")
{
r3["c04"] = r["soluong"].ToString();
}
ds.Tables[0].Rows.Add(r3);
}
else
{
dr = ds.Tables[0].Select("mavp=" + int.Parse(r["mavp"].ToString()));
if (dr.Length > 0)
{
dr[0]["c01"] = decimal.Parse(dr[0]["c01"].ToString()) + decimal.Parse(r["soluong"].ToString());
if (r["paid"].ToString() == "1")
{
dr[0]["c02"] = decimal.Parse(dr[0]["c02"].ToString()) + decimal.Parse(r["soluong"].ToString());
dr[0]["c03"] = decimal.Parse(dr[0]["c03"].ToString()) + decimal.Parse(r["sotien"].ToString());
}
if (r["done"].ToString() == "1")
{
dr[0]["c04"] = decimal.Parse(dr[0]["c04"].ToString()) + decimal.Parse(r["soluong"].ToString());
}
}
}
}
dataGrid1.DataSource = ds.Tables[0];
}