最后统计根据YLJGDM统计:
public DataTable dtComHospital = new DataTable();
///
/// 短信统计
///
private void BindChildProtectedSMSStatistice()
{
StringBuilder strSql1 = new StringBuilder();
strSql1.Append("select count(*) as totalcount,send_user,UserFlag from dbo.SentRecord inner join dbo.SMSClass on SentRecord.Class_ID=dbo.SMSClass.Class_ID ");
strSql1.Append("group by Send_User,UserFlag");
DataTable dtSMS = db.Query(strSql1.ToString()).Tables[0];
dtComHospital.Columns.Add(new DataColumn("comhospital_yljgdm",typeof(string)));
dtComHospital.Columns.Add(new DataColumn("comhospital_count",typeof(int)));
foreach (DataRow dr in dtSMS.Rows)
{
StringBuilder strsql2 = new StringBuilder();
if (dr["USerFlag"].ToString() == "1")
{
strsql2.Append("select users.ID,users.comhospital_yljgdm as yljgdm,comhospital.comhospital_name from Users left join ComHospital on users.comhospital_yljgdm=ComHospital.comhospital_yljgdm where users.id='" + dr["Send_User"] + "'");
}
else if(dr["UserFlag"].ToString()=="0")
{
strsql2.Append("select yhry.ID,yhry.yljgdm,comhospital.comhospital_name from Yhry left join ComHospital on yhry.yljgdm=ComHospital.comhospital_yljgdm where yhry.id='" + dr["Send_User"] + "'");
}
DataTable dt = sqlU.Query(strsql2.ToString()).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
string yljgdm=dt.Rows[0]["yljgdm"].ToString();
DataRow drowComHospital=dtComHospital.NewRow();
if (dtComHospital.Rows.Count > 0)
{
if (String.IsNullOrEmpty(yljgdm))
{
continue;//跳出本次循环,继续下次循环
}
//查找是否存在此yljgdm,如果存在,更新此yljgdm的统计数量
DataRow[] row=dtComHospital.Select(" comhospital_yljgdm=" + yljgdm + "");
if (row.Length == 0)
{
drowComHospital["comhospital_yljgdm"] = yljgdm;
drowComHospital["comhospital_count"] = dr["totalcount"];
dtComHospital.Rows.Add(drowComHospital);
}
else
{
row[0].BeginEdit();
row[0]["comhospital_count"] = (Convert.ToInt32(row[0]["comhospital_count"]) + Convert.ToInt32(dr["totalcount"])).ToString();
row[0].EndEdit();
}
}
else
{
drowComHospital["comhospital_yljgdm"] = yljgdm;
drowComHospital["comhospital_count"] = dr["totalcount"];
dtComHospital.Rows.Add(drowComHospital);
}
}
}
rptSMSStatistics.DataSource = dtComHospital;
rptSMSStatistics.DataBind();
}
再根据yljgdm获取医院名称
///
/// 获取社区医院名称
///
///
///
public string GetComHospitalName(string comhospital_yljgdm)
{
string comhospital_name = "";
if (comhospital_yljgdm != "")
{
string strsql = "select comhospital_name from ComHospital where comhospital_yljgdm='" + comhospital_yljgdm + "'";
DataTable dt = sqlU.Query(strsql).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
comhospital_name = dt.Rows[0]["comhospital_name"].ToString();
}
else
{
comhospital_name = "卫生局";
}
}
return comhospital_name;
}
页面显示: