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