使用Linq操作Datatable,按条件筛选数据,找出数量前10的数据,按字段分组
public string GetAreaAndPieJson()
{
DataTable dt = new DataTable();
DataTable dt2 =new DataTable();
DataTable dtt2 =new DataTable();
string areapieJson = "";
BaseBLL.MobileBLL mb = new BaseBLL.MobileBLL();
BaseBLL.SMZ smz = new BaseBLL.SMZ();
//如果是点击分公司,来赛选数据,则通过分公司单位编号来查找数据,否则则通过系统缓存来实现
if (Session["deptID"] != "" && Session["deptID"]!=null)
{
dt = mb.GetGDRT_ZCQK2(Session["deptID"].ToString());
dt2 = smz.GetRLJandAllcount(Session["deptID"].ToString());
dtt2 = smz.GetRJCS_Info(Session["deptID"].ToString());
}
else {
dt = mb.GetGDRT_ZCQK2(CondictionInfo.CurrentDWBH);
dt2 = smz.GetRLJandAllcount(CondictionInfo.CurrentDWBH);
dtt2 = smz.GetRJCS_Info(CondictionInfo.CurrentDWBH);
}
areaChartData area = new areaChartData();
area.zcgrs = dt.Rows.Count.ToString();
if (dt2 != null)
{
area.zsxms = dt2.Rows[0]["zsxms"].ToString();//在施项目数
area.zcrs = dt2.Rows[0]["zcrs"].ToString();//在册人数
area.rljjcrs = dt2.Rows[0]["rljs"].ToString();//在场工人数
}
areapieJson = Newtonsoft.Json.JsonConvert.SerializeObject(area);
//List<PieData> pl = new List<PieData> { };
PieDaList pls = new PieDaList();
pls.main();
var qys1 = from t in dt.AsEnumerable()
group t by new
{
t1 = t.Field<string>("GZ"),
} into jjj
select new
{
GZ = jjj.Key.t1
};
if (qys1 != null)
{
foreach (var qy in qys1)
{
int len = dt.Select("GZ='" + qy.GZ + "'").Length;
Pievalue pv = new Pievalue();
pv.value = len;
pv.name = qy.GZ+ "(" + pv.value + "人)";
pls.data.Add(qy.GZ + "(" + pv.value + "人)");
pls.values.Add(pv);
}
}
areapieJson += "---" + Newtonsoft.Json.JsonConvert.SerializeObject(pls);
braChartData braLists = new braChartData();
braLists.main();
var qys2 = from t in dt.AsEnumerable()
group t by new
{
t1 = t.Field<string>("GCMC"),
t2 = t.Field<string>("GCBH"),
} into jjj
select new
{
GCMC = jjj.Key.t1,
GCBH = jjj.Key.t2,
};
DataTable GDRY = new DataTable();
GDRY.Columns.Add("GCMC",typeof(string));
GDRY.Columns.Add("Count", typeof(Int32));
List<GDRYCount> List=new List<GDRYCount>();
GDRYCount co = new GDRYCount();
foreach (var qy in qys2)
{
co = new GDRYCount();
//取出前10的数据
DataRow[] drr = dt.Select("GCMC='" + qy.GCMC + "'");
GDRY.Rows.Add(qy.GCMC, drr.Length);
co.GDMC = qy.GCMC;
co.Count = drr.Length;
List.Add(co);
}
var qys4 = (from t in GDRY.AsEnumerable()
orderby t.Field<int>("Count") descending
select t).Take(10);
foreach (var qy in qys4)
{
string GCMC = qy["GCMC"].ToString();
braLists.labels.Add(GCMC);
DataRow[] drr = dt.Select("GCMC='" + GCMC + "'");
braLists.data1.Add(drr.Length);
var q = from t in drr.AsEnumerable()
group t by new
{
t1 = t.Field<string>("GZ"),
} into jjj
select new
{
GZ = jjj.Key.t1
};
braLists.data2.Add(q.ToList().Count);
braLists.data3.Add(dtt2.Select("GCBH='" +GCMC + "'").Length);
}
//foreach (var qy in qys2)
//{
// braLists.labels.Add(qy.GCMC);
// DataRow[] drr = dt.Select("GCMC='" + qy.GCMC + "'");
// braLists.data1.Add(drr.Length);
// var q = from t in drr.AsEnumerable()
// group t by new
// {
// t1 = t.Field<string>("GZ"),
// } into jjj
// select new
// {
// GZ = jjj.Key.t1
// };
// braLists.data2.Add(q.ToList().Count);
// braLists.data3.Add(dtt2.Select("GCBH='" + qy.GCBH + "'").Length);
//}
areapieJson += "---" + Newtonsoft.Json.JsonConvert.SerializeObject(braLists);
return areapieJson;
}