public ActionResult TableHtml(string year)
{
List <DeptMonthData> data = new List <DeptMonthData>();
DataTable dt = new HseObserveBLL().GetWXX(year);
if (dt.Rows != null && dt.Rows.Count > 0)
{
List <HseKeyValue> hseKeyValues = new List <HseKeyValue>();
var enumtor = dt.Rows.GetEnumerator();
while (enumtor.MoveNext())
{
HseKeyValue keyValue = new HseKeyValue();
DataRow dr = enumtor.Current as DataRow;
keyValue.Key = dr["CONTENT"] == null ? null : dr["CONTENT"].ToString();
keyValue.Str1 = dr["MONTH"] == null ? null : dr["MONTH"].ToString();
keyValue.Num1 = dr["COUNT"] == null ? 0 : Convert.ToInt32(dr["COUNT"]);
hseKeyValues.Add(keyValue);
}
;
List <string> contentList = hseKeyValues.Select(p => p.Key).Distinct().ToList();
contentList.ForEach(p => {
DeptMonthData deptMonth = new DeptMonthData(year, true);
deptMonth.Ttile = p;
deptMonth.MonthData.ForEach(x => {
x.Value = hseKeyValues.Where(hse => hse.Key == p && hse.Str1 == x.Key).Sum(d => d.Num1);
});
data.Add(deptMonth);
});
}
var month = new DeptMonthData(year, true).MonthData.Select(p => p.Key).ToList();
ViewBag.MonthInfo = month;
return(View(data));
}
/// <summary>
/// 预警指标卡各部门提交数据统计
/// </summary>
/// <param name="deptIdList">要统计的部门的ID</param>
/// <param name="start">开始时间</param>
/// <param name="end">结束时间</param>
/// <returns></returns>
public List <HseKeyValue> GetWarningCardCount(List <string> deptIdList, string start, string end)
{
List <HseKeyValue> data = new List <HseKeyValue>();
var db = new RepositoryFactory().BaseRepository();
string sql = @" SELECT B.DEPTID,
COUNT(DISTINCT B.CHECKRECORDID) AS ALLCOUNT,
SUM(C.NUM1)+SUM(C.NUM4) AS SAFETYCOUNT,
SUM(C.NUM2)+SUM(C.NUM3) RISKCOUNT
FROM HSE_CHECKRECORD B LEFT JOIN HSE_CHECKITEM C ON B.CHECKRECORDID=C.CHECKRECORDID WHERE 1=1 ";
if (!string.IsNullOrWhiteSpace(start))
{
sql += " AND B.CREATETIME>=to_date('" + start + "','yyyy-MM-dd') ";
}
if (!string.IsNullOrWhiteSpace(end))
{
sql += " AND B.CREATETIME<=to_date('" + end + "','yyyy-MM-dd') ";
}
if (deptIdList != null && deptIdList.Count > 0)
{
List <string> deptsStr = new List <string>();
deptIdList.ForEach(x =>
{
deptsStr.Add("'" + x + "'");
});
sql += " AND B.DEPTID in (" + string.Join(",", deptsStr) + ")";
}
sql += " group by B.deptid";
DataTable dt = db.FindTable(sql);
if (dt != null && dt.Rows != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
HseKeyValue keyValue = new HseKeyValue()
{
DeptId = dr["DEPTID"] == DBNull.Value ? "" : dr["DEPTID"].ToString(),
Num1 = dr["ALLCOUNT"] == DBNull.Value ? 0 : Convert.ToInt64(dr["ALLCOUNT"]),
Num3 = dr["SAFETYCOUNT"] == DBNull.Value ? 0 : Convert.ToInt64(dr["SAFETYCOUNT"]),
Num4 = dr["RISKCOUNT"] == DBNull.Value ? 0 : Convert.ToInt64(dr["RISKCOUNT"]),
};
data.Add(keyValue);
}
}
return(data);
}
/// <summary>
/// 危险项统计
/// </summary>
/// <param name="year">年份</param>
/// <param name="month">月份</param>
/// <returns></returns>
public List <HseKeyValue> GetWXXCount(string year, string month)
{
string sql = "SELECT CONTENT,COUNT(*) AS COUNT FROM HSE_SECURITYOBSERVE WHERE 1=1 ";
if (!string.IsNullOrEmpty(year))
{
sql += " AND TO_CHAR( OBSERVEDATE,'YYYY' )='" + year + "'";
}
if (!string.IsNullOrEmpty(month))
{
if (int.Parse(month) < 10)
{
month = "0" + month;
}
sql += " AND TO_CHAR( OBSERVEDATE,'MM' )='" + month + "' ";
}
sql += " AND ( OBSERVETYPE LIKE '%未遂事件%' OR OBSERVETYPE LIKE '%不安全状况%' OR OBSERVETYPE LIKE '%不安全行为%') GROUP BY CONTENT";
sql = " SELECT CONTENT,SUM(COUNT) as COUNT FROM ( SELECT REGEXP_SUBSTR(T.CONTENT,'[^,]+',1,l) as content,COUNT FROM( " + sql;
sql += " ) T, (select level l from dual connect by level <= 100) b where l <= length(T.content) - length(replace(T.content, ',')) + 1 ) T2 GROUP BY CONTENT";
DataTable dt = new RepositoryFactory().BaseRepository().FindTable(sql);
List <HseKeyValue> data = new List <HseKeyValue>();
if (dt.Rows != null && dt.Rows.Count > 0)
{
var drList = dt.Rows.GetEnumerator();
while (drList.MoveNext())
{
var dr = drList.Current as DataRow;
if (dr["CONTENT"] != null)
{
HseKeyValue keyValue = new HseKeyValue()
{
Key = dr["CONTENT"].ToString(), Value = dr["COUNT"] == null ? 0 : Convert.ToInt32(dr["COUNT"])
};
data.Add(keyValue);
}
}
}
return(data);
}
/// <summary>
/// 获取危险项表格每月数据
/// </summary>
/// <param name="form"></param>
/// <returns></returns>
public ActionResult GetWXX(FormCollection form)
{
try
{
var year = form["Year"];
List <DeptMonthData> data = new List <DeptMonthData>();
DataTable dt = new HseObserveBLL().GetWXX(year);
if (dt.Rows != null && dt.Rows.Count > 0)
{
List <HseKeyValue> hseKeyValues = new List <HseKeyValue>();
var enumtor = dt.Rows.GetEnumerator();
while (enumtor.MoveNext())
{
HseKeyValue keyValue = new HseKeyValue();
DataRow dr = enumtor.Current as DataRow;
keyValue.Key = dr["CONTENT"] == null ? null : dr["CONTENT"].ToString();
keyValue.Str1 = dr["MONTH"] == null ? null : dr["MONTH"].ToString();
keyValue.Num1 = dr["COUNT"] == null ? 0 : Convert.ToInt32(dr["COUNT"]);
hseKeyValues.Add(keyValue);
}
;
List <string> contentList = hseKeyValues.Select(p => p.Key).Distinct().ToList();
contentList.ForEach(p => {
DeptMonthData deptMonth = new DeptMonthData(year, true);
deptMonth.Ttile = p;
deptMonth.MonthData.ForEach(x => {
x.Value = hseKeyValues.Where(hse => hse.Key == p && hse.Str1 == x.Key).Sum(d => d.Num1);
});
data.Add(deptMonth);
});
}
var month = new DeptMonthData(year, true).MonthData.Select(p => p.Key).ToList();
return(Json(new { Code = 0, Data = new { Info = data, MonthInfo = month } }));
}
catch (Exception ex)
{
return(Json(new { Code = -1, ex.Message }));
}
}
public ActionResult GetWarningCardCount(FormCollection form)
{
try
{
var user = OperatorProvider.Provider.Current();
List <DepartmentEntity> allDeptList = new DepartmentBLL().GetList().ToList();
//1、获取页面上要显示的部门（当前用户所在的部门的下级部门）
List <DepartmentEntity> showDepts = new List <DepartmentEntity>();
List <string> searchDeptIds = new List <string>();
List <HseKeyValueParameter> serchParameter = new List <HseKeyValueParameter>();//搜索用参数
var userDept = allDeptList.FirstOrDefault(p => p.DepartmentId.Equals(user.DeptId));
if (userDept == null)
{
return(Json(new { Code = -1, Message = "找不到当前用户的部门" }));
}
if (userDept.Nature == "班组")
{
//如果改用户是班组级用户则查上级部门下的所有班组
showDepts = allDeptList.Where(p => p.ParentId == userDept.ParentId).ToList();
showDepts.ForEach(p =>
{
serchParameter.Add(new HseKeyValueParameter()
{
RootId = p.DepartmentId,
RootName = p.FullName,
DeptIds = new List <string>()
{
p.DepartmentId
}
});
});
}
else
{
//如果不是班组级的话 ，则查当前用户部门的下级部门即可，预警指标卡数据根据Encode模糊查询出的部门的去做匹配
if (userDept.IsOrg == 1)
{
//厂级部门查全厂的数据
showDepts = allDeptList.Where(p => p.ParentId == userDept.OrganizeId).ToList();
}
else
{
//非厂级部门查 本子部门的数据
showDepts = allDeptList.Where(p => p.ParentId == userDept.DepartmentId).ToList();
}
showDepts.ForEach(p =>
{
serchParameter.Add(new HseKeyValueParameter()
{
RootId = p.DepartmentId,
RootName = p.FullName,
DeptIds = allDeptList.Where(x => x.EnCode.StartsWith(p.EnCode)).Select(m => m.DepartmentId).ToList()
});
});
}
var bll = new WarningCardBLL();
List <HseKeyValue> dataCount = bll.GetWarningCardCount(serchParameter.SelectMany(p => p.DeptIds).ToList(), form["start"], form["end"]);
List <HseKeyValue> data = new List <HseKeyValue>();
var allUser = new UserBLL().GetList().Where(p => p.IsPresence == "1").ToList();
serchParameter.ForEach(p =>
{
HseKeyValue keyValue = new HseKeyValue();
keyValue.Key = p.RootName;
var matchData = dataCount.Where(x => p.DeptIds.Contains(x.DeptId)).ToList();
keyValue.Num1 = matchData.Sum(m => m.Num1);
keyValue.Num3 = matchData.Sum(m => m.Num3);
keyValue.Num4 = matchData.Sum(m => m.Num4);
keyValue.Num5 = (keyValue.Num3 + keyValue.Num4) == 0 ? 0 : Math.Round(keyValue.Num3 / (keyValue.Num3 + keyValue.Num4) * 100, 2);
int userCount = allUser.Count(u => u.DepartmentId == p.RootId || p.DeptIds.Contains(u.DepartmentId)); //本子部门的用户的数量
keyValue.Num2 = userCount == 0 ? 0 : Math.Round(keyValue.Num1 / userCount, 2); //如果没有用户 ，默认0
data.Add(keyValue);
});
return(Json(new { Code = 0, Data = data }));
}
catch (Exception ex)
{
return(Json(new { Code = -1, ex.Message }));
}
}
/// <summary>
/// 获取参与率
/// </summary>
/// <param name="year">要查询的年份</param>
/// <param name="deptEncode">部门编码</param>
/// <returns></returns>
public List <HseKeyValue> GetCYLData(string year, string deptEncode)
{
List <HseKeyValue> data = null;
string sql = @" select
case to_char(OBSERVEDATE,'mm')
when '01' then '一月份'
when '02' then '二月份'
when '03' then '三月份'
when '04' then '四月份'
when '05' then '五月份'
when '06' then '六月份'
when '07' then '七月份'
when '08' then '八月份'
when '09' then '九月份'
when '10' then '十月份'
when '11' then '十一月份'
when '12' then '十二月份'
else '其他'
end as Month,
Count(ID) as SUBMITCOUNT,
Count(DISTINCT CREATEUSERID) as SUBMITUSER,
(select (case cycle when '每周' then 0.25 when '每月' then 1 when '每季度' then 3 when '每年' then 12 else 1 end) as cycle from hse_observersetting
where settingname = '安全观察卡') as cycle, (select times from hse_observersetting
where settingname = '安全观察卡') as times
from hse_securityobserve
WHERE 1=1 ";
if (!string.IsNullOrWhiteSpace(year) && year != DateTime.Now.Year.ToString())
{
sql += " AND to_char( OBSERVEDATE,'yyyy' )='" + year + "' ";
data = new HseKeyValue().InitData(1);
}
else
{
//为空默认当前年
year = DateTime.Now.Year.ToString();
sql += " AND to_char( OBSERVEDATE,'yyyy' )='" + year + "' ";
data = new HseKeyValue().InitData(2); //如果为当前年，只查询到当前月的上月（例：现在是五月，只查到6月，生成的数据接收实体有变化，其他的无变化）
}
if (!string.IsNullOrWhiteSpace(deptEncode))
{
sql += " AND CREATEUSERDEPTCODE LIKE '" + deptEncode + "%'";
}
sql += " GROUP BY to_char(OBSERVEDATE,'mm')";
DataTable dt = new RepositoryFactory().BaseRepository().FindTable(sql);
string sqlByUserCount = " select Count(*) as Count from BASE_USER where departmentCode like '" + deptEncode + "%'";
decimal userCount = Convert.ToDecimal(new RepositoryFactory <UserEntity>().BaseRepository().FindObject(string.Format(@"
SELECT
count( 1 )
FROM
base_user a
INNER JOIN HSE_OBSERVERSETTINGITEM b ON b.DEPTID = a.departmentid
INNER JOIN HSE_OBSERVERSETTING c ON c.settingid = b.settingid
WHERE
a.ispresence = '1'
AND c.settingname = '安全观察卡'
AND a.departmentcode LIKE '{0}%'
", deptEncode)));//单位的人员数量（应提交人数）
if (dt.Rows != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
var obj = data.FirstOrDefault(p => p.Key == dr["Month"].ToString());
if (obj != null)
{
//参与度=(已提交卡总数/总人数*周数)*(实际提交人数/总人数)*100%
decimal submitcount = dr["SUBMITCOUNT"] == null ? 0 : Convert.ToDecimal(dr["SUBMITCOUNT"]); //已提交总数
decimal submituser = dr["SUBMITUSER"] == null ? 0 : Convert.ToDecimal(dr["SUBMITUSER"]); //已提交人数 (重复提交的人只算一次)
decimal cycle = dr["CYCLE"] == null ? 0 : Convert.ToDecimal(dr["CYCLE"]); //
decimal times = dr["TIMES"] == null ? 0 : Convert.ToDecimal(dr["TIMES"]); //
if (cycle == 0)
{
cycle = 1;
}
if (times == 0)
{
times = 1;
}
if (userCount > 0) //分母不为0
{
obj.Value = Math.Round((submitcount / (userCount * times / cycle)) * (submituser / userCount) * 100, 2); //百分比
}
}
}
}
return(data);
}
/// <summary>
/// 获取安全比趋势图
/// </summary>
/// <param name="year">年份</param>
/// <param name="deptId">部门ID</param>
/// <returns></returns>
public List <HseKeyValue> GetAQBData(string year, string deptId)
{
var db = new RepositoryFactory().BaseRepository();
string yearWhere = string.Empty;
if (!string.IsNullOrEmpty(year))
{
yearWhere = " to_char( b.CREATETIME,'yyyy' )='" + year + "' ";
}
string deptWhere = string.Empty;
if (!string.IsNullOrEmpty(deptId))
{
//如果部门ID不是空的，查询本部门以及下属部门
string deptSql = string.Format("select DepartmentId from base_department where departmentid='{0}' or encode like ''|| (select encode from base_department where departmentid='{0}') ||'%'", deptId);
var dtIds = db.FindTable(deptSql);
var deptIds = new List <string>();
if (dtIds.Rows != null && dtIds.Rows.Count > 0)
{
foreach (DataRow dr in dtIds.Rows)
{
deptIds.Add("'" + dr[0].ToString() + "'");
}
}
deptWhere = " B.deptid in (" + string.Join(",", deptIds) + ")";
}
#region 废弃
// select
//(select ROUND((SUM(A.num1) + SUM(A.num4)) / (SUM(A.num1) + SUM(A.num2) + SUM(A.num3) + SUM(A.num4)), 2) from hse_checkitem A LEFT JOIN hse_checkrecord B ON A.checkrecordid = B.checkrecordid where to_char(B.checktime, 'mm') = '01') as 一月份,
//(select ROUND((SUM(A.num1) + SUM(A.num4)) / (SUM(A.num1) + SUM(A.num2) + SUM(A.num3) + SUM(A.num4)), 2) from hse_checkitem A LEFT JOIN hse_checkrecord B ON A.checkrecordid = B.checkrecordid where to_char(B.checktime,'mm')= '02') as 二月份,
//(select ROUND((SUM(A.num1) + SUM(A.num4)) / (SUM(A.num1) + SUM(A.num2) + SUM(A.num3) + SUM(A.num4)), 2) from hse_checkitem A LEFT JOIN hse_checkrecord B ON A.checkrecordid = B.checkrecordid where to_char(B.checktime,'mm')= '03') as 三月份,
//(select ROUND((SUM(A.num1) + SUM(A.num4)) / (SUM(A.num1) + SUM(A.num2) + SUM(A.num3) + SUM(A.num4)), 2) from hse_checkitem A LEFT JOIN hse_checkrecord B ON A.checkrecordid = B.checkrecordid where to_char(B.checktime,'mm')= '04') as 四月份,
//(select ROUND((SUM(A.num1) + SUM(A.num4)) / (SUM(A.num1) + SUM(A.num2) + SUM(A.num3) + SUM(A.num4)), 2) from hse_checkitem A LEFT JOIN hse_checkrecord B ON A.checkrecordid = B.checkrecordid where to_char(B.checktime,'mm')= '05') as 五月份,
//(select ROUND((SUM(A.num1) + SUM(A.num4)) / (SUM(A.num1) + SUM(A.num2) + SUM(A.num3) + SUM(A.num4)), 2) from hse_checkitem A LEFT JOIN hse_checkrecord B ON A.checkrecordid = B.checkrecordid where to_char(B.checktime,'mm')= '06') as 六月份,
//(select ROUND((SUM(A.num1) + SUM(A.num4)) / (SUM(A.num1) + SUM(A.num2) + SUM(A.num3) + SUM(A.num4)), 2) from hse_checkitem A LEFT JOIN hse_checkrecord B ON A.checkrecordid = B.checkrecordid where to_char(B.checktime,'mm')= '07') as 七月份,
//(select ROUND((SUM(A.num1) + SUM(A.num4)) / (SUM(A.num1) + SUM(A.num2) + SUM(A.num3) + SUM(A.num4)), 2) from hse_checkitem A LEFT JOIN hse_checkrecord B ON A.checkrecordid = B.checkrecordid where to_char(B.checktime,'mm')= '08') as 八月份,
//(select ROUND((SUM(A.num1) + SUM(A.num4)) / (SUM(A.num1) + SUM(A.num2) + SUM(A.num3) + SUM(A.num4)), 2) from hse_checkitem A LEFT JOIN hse_checkrecord B ON A.checkrecordid = B.checkrecordid where to_char(B.checktime,'mm')= '09') as 九月份,
//(select ROUND((SUM(A.num1) + SUM(A.num4)) / (SUM(A.num1) + SUM(A.num2) + SUM(A.num3) + SUM(A.num4)), 2) from hse_checkitem A LEFT JOIN hse_checkrecord B ON A.checkrecordid = B.checkrecordid where to_char(B.checktime,'mm')= '10') as 十月份,
//(select ROUND((SUM(A.num1) + SUM(A.num4)) / (SUM(A.num1) + SUM(A.num2) + SUM(A.num3) + SUM(A.num4)), 2) from hse_checkitem A LEFT JOIN hse_checkrecord B ON A.checkrecordid = B.checkrecordid where to_char(B.checktime,'mm')= '11') as 十一月份,
//(select ROUND((SUM(A.num1) + SUM(A.num4)) / (SUM(A.num1) + SUM(A.num2) + SUM(A.num3) + SUM(A.num4)), 2) from hse_checkitem A LEFT JOIN hse_checkrecord B ON A.checkrecordid = B.checkrecordid where to_char(B.checktime,'mm')= '12') as 十二月份
//from dual
#endregion
string sql = @"select case to_char(B.CREATETIME,'mm')
when '01' then '一月份'
when '02' then '二月份'
when '03' then '三月份'
when '04' then '四月份'
when '05' then '五月份'
when '06' then '六月份'
when '07' then '七月份'
when '08' then '八月份'
when '09' then '九月份'
when '10' then '十月份'
when '11' then '十一月份'
when '12' then '十二月份'
else '其他'
end as Month,
ROUND((SUM(A.num1)+SUM(A.num4))/(SUM(A.num1)+SUM(A.num2)+SUM(A.num3)+SUM(A.num4))*100,2) as AQB
from hse_checkitem A LEFT JOIN hse_checkrecord B ON A.checkrecordid=B.checkrecordid ";
if (!string.IsNullOrEmpty(yearWhere))
{
sql += " where " + yearWhere;
}
if (!string.IsNullOrEmpty(deptWhere))
{
if (!string.IsNullOrWhiteSpace(yearWhere))
{
sql += " AND " + deptWhere;
}
else
{
sql += " where " + deptWhere;
}
}
sql += " group by to_char(B.CREATETIME,'mm')";
DataTable dt = new RepositoryFactory().BaseRepository().FindTable(sql);
List <HseKeyValue> listObj = new HseKeyValue().InitData(1);
if (dt.Rows != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
var obj = listObj.FirstOrDefault(p => p.Key == dr["Month"].ToString());
if (obj != null)
{
obj.Value = dr["AQB"] == null ? 0 : Convert.ToDecimal(dr["AQB"]);
}
//switch (dr["Month"].ToString())
//{
// case "一月份":
// break;
// case "二月份":
// break;
// case "三月份":
// break;
// case "四月份":
// break;
// case "五月份":
// break;
// case "六月份":
// break;
// case "七月份":
// break;
// case "八月份":
// break;
// case "九月份":
// break;
// case "十月份":
// break;
// case "十一月份":
// break;
// case "十二月份":
// break;
//}
}
}
return(listObj);
}
/// <summary>
/// 参与度趋势统计数据
/// </summary>
/// <param name="year">年份</param>
/// <param name="deptId">部门ID</param>
/// <returns></returns>
public List <HseKeyValue> GetCYDData(string year, string deptId)
{
var db = new RepositoryFactory().BaseRepository();
string yearWhere = string.Empty;
string deptWhere = string.Empty;
string sql = string.Format(@" select
case to_char(createtime,'mm')
when '01' then '一月份'
when '02' then '二月份'
when '03' then '三月份'
when '04' then '四月份'
when '05' then '五月份'
when '06' then '六月份'
when '07' then '七月份'
when '08' then '八月份'
when '09' then '九月份'
when '10' then '十月份'
when '11' then '十一月份'
when '12' then '十二月份'
else '其他'
end as Month,
count(1) as COUNT,
(SELECT
count( 1 )
FROM
base_user a
INNER JOIN HSE_OBSERVERSETTINGITEM b ON b.DEPTID = a.departmentid
INNER JOIN HSE_OBSERVERSETTING c ON c.settingid = b.settingid
WHERE
a.ispresence = '1'
AND c.settingname = '预警指标卡'
AND a.departmentcode LIKE '' || ( SELECT encode FROM base_department WHERE departmentid = '{0}' ) || '%') as USERCOUNT,
count(distinct createuserid) as SUBMITCOUNT,
(select (case cycle when '每周' then 0.25 when '每月' then 1 when '每季度' then 3 when '每年' then 12 else 1 end) as cycle from hse_observersetting
where settingname = '预警指标卡') as cycle, (select times from hse_observersetting
where settingname = '预警指标卡') as times
from HSE_CHECKRECORD WHERE 1=1 ", deptId);
// COUNT 总提交数 USERCOUNT应提交总人数 SUBMITCOUNT已提交的人数Month月份
if (!string.IsNullOrEmpty(year))
{
sql += " AND to_char( createtime,'yyyy' )='" + year + "' ";
}
if (!string.IsNullOrEmpty(deptId))
{
//如果部门ID不是空的，查询本部门以及下属部门
string deptSql = string.Format("select DepartmentId from base_department where departmentid='{0}' or encode like ''|| (select encode from base_department where departmentid='{0}') ||'%'", deptId);
var dtIds = db.FindTable(deptSql);
var deptIds = new List <string>();
if (dtIds.Rows != null && dtIds.Rows.Count > 0)
{
foreach (DataRow dr in dtIds.Rows)
{
deptIds.Add("'" + dr[0].ToString() + "'");
}
}
sql += " and deptid in (" + string.Join(",", deptIds) + ")";
}
sql += " group by to_char(createtime,'mm')";
DataTable dt = new RepositoryFactory().BaseRepository().FindTable(sql);
List <HseKeyValue> listObj = new HseKeyValue().InitData(1);
if (dt.Rows != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
var obj = listObj.FirstOrDefault(p => p.Key == dr["Month"].ToString());
if (obj != null)
{
//参与度=(已提交卡总数/总人数*周数)*(实际提交人数/总人数)*100%
decimal count = dr["COUNT"] == null ? 0 : Convert.ToDecimal(dr["COUNT"]); //已提交总数
decimal userCount = dr["USERCOUNT"] == null ? 0 : Convert.ToDecimal(dr["USERCOUNT"]); //总人数
decimal submitcount = dr["SUBMITCOUNT"] == null ? 0 : Convert.ToDecimal(dr["SUBMITCOUNT"]); //已提交人数 (重复提交的人只算一次)
decimal cycle = dr["CYCLE"] == null ? 0 : Convert.ToDecimal(dr["CYCLE"]);
decimal times = dr["TIMES"] == null ? 0 : Convert.ToDecimal(dr["TIMES"]);
if (cycle == 0)
{
cycle = 1;
}
if (times == 0)
{
times = 1;
}
if (userCount > 0)//分母不为0
{
//obj.Value = Math.Round((count / (userCount * 4)) * (submitcount / userCount) * 100, 2);//百分比
obj.Value = Math.Round((count / (userCount * times / cycle)) * (submitcount / userCount) * 100, 2);//百分比
}
}
}
}
return(listObj);
}
