/// <summary>
/// 统计戒毒人员一栏数据
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public int GetGetBaneDataCount(BaneListParams param)
{
sb?.Clear();
sb.Append(string.Format(@"SELECT COUNT(*) FROM
(SELECT user_sex,user_identify,user_type,user_phone FROM Bane_User
WHERE 1=1 "));
if (!string.IsNullOrEmpty(param.baneName))
{
sb.Append(string.Format(" AND user_name LIKE '%{0}%' ", param.baneName));
}
if (!string.IsNullOrEmpty(param.baneType))
{
sb.Append(string.Format(" AND user_type='{0}' ", param.baneType));
}
//判断是否为父节点
if (param.isParent)
{
sb.Append(string.Format(" AND org_id IN(SELECT folder_id FROM dbo.T_OrgFolder WHERE folder_path<>'{0}' AND folder_path LIKE '{0}%') AND user_id IN(SELECT person_id FROM dbo.T_UserUnitPersonRelation WHERE user_id={1}) ", param.folder_path, param.user_id));
}
else
{
sb.Append(string.Format(" AND org_id={0} AND user_id IN(SELECT person_id FROM dbo.T_UserUnitPersonRelation WHERE user_id={1})", param.orgId, param.user_id));
}
sb.Append(@") users LEFT JOIN
(SELECT ROW_NUMBER() OVER(PARTITION BY user_identify ORDER BY start_date DESC) rank,user_identify AS a0177 FROM Bane_RecoveryInfo
WHERE 1=1 ");
if (!string.IsNullOrEmpty(param.baneEnd))
{
sb.Append(string.Format(" AND end_reason='{0}' ", param.baneEnd));
}
sb.Append(@") info ON users.user_identify=info.a0177 WHERE info.rank=1;");
return(HCQ2_Common.Helper.ToInt(HCQ2_Common.SQL.SqlHelper.ExecuteScalar(sb.ToString())));
}
/// <summary>
/// 统计定期尿检人员数量
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public int GetBaneProDataCount(BaneListParams param)
{
if (null == param)
{
return(0);
}
return(DBSession.IBane_UserDAL.GetBaneProDataCount(param));
}
/// <summary>
/// 获取定期尿检记录数据
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public List <BaneProModel> GetBaneProData(BaneListParams param)
{
if (null == param)
{
return(null);
}
return(DBSession.IBane_UserDAL.GetBaneProData(param));
}
/// <summary>
/// 统计定期尿检人员数量
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public int GetBaneProDataCount(BaneListParams param)
{
if (null == param)
{
return(0);
}
return(DBSession.IBane_UserDAL.GetBaneProDataCount(param, GetPermissions(param.user_id), HCQ2UI_Helper.Session.SysPermissSession.isAreaManager));
}
/// <summary>
/// 获取定期尿检记录数据
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public List <BaneProModel> GetBaneProData(BaneListParams param)
{
if (null == param)
{
return(null);
}
return(DBSession.IBane_UserDAL.GetBaneProData(param, GetPermissions(param.user_id), HCQ2UI_Helper.Session.SysPermissSession.isAreaManager));
}
/// <summary>
/// 获取戒毒人员一栏数据
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public List <BaneListModel> GetBaneData(BaneListParams param, List <int> perList, bool isManager)
{
sb?.Clear();
sb.Append(string.Format(@"SELECT TOP {0} * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY user_id ASC) rowNumber,user_name,user_sex,user_identify,user_type,user_phone,DATEDIFF(yy,CONVERT(varchar,user_birth,101),GETDATE())AS user_age,
(CASE WHEN LEN(user_identify)=18 THEN LEFT(user_identify,6)+REPLICATE('*',8)+RIGHT(user_identify,4)
WHEN LEN(user_identify)=15 THEN LEFT(user_identify,6)+REPLICATE('*',6)+RIGHT(user_identify,3) ELSE REPLICATE('*',LEN(user_identify)) END) AS hiden_identify FROM Bane_User
WHERE 1=1 ", param.rows));
if (!string.IsNullOrEmpty(param.baneName))
{
sb.Append(string.Format(" AND user_name LIKE '%{0}%' ", param.baneName));
}
if (!string.IsNullOrEmpty(param.baneType))
{
sb.Append(string.Format(" AND user_type='{0}' ", param.baneType));
}
//判断是否为父节点
if (param.isParent)
{
if (isManager)
{
sb.AppendFormat(@" AND org_id IN(SELECT folder_id FROM dbo.T_OrgFolder WHERE folder_path<>'{0}' AND folder_path LIKE '{0}%') AND org_id IN(
SELECT unit_id FROM dbo.T_UserUnitRelation WHERE user_id={1}
UNION
SELECT area_code AS unit_id FROM dbo.T_AreaPermissRelation WHERE per_id IN({2})) " , param.folder_path, param.user_id, string.Join(",", perList));
}
else
{
sb.Append(string.Format(" AND org_id IN(SELECT folder_id FROM dbo.T_OrgFolder WHERE folder_path<>'{0}' AND folder_path LIKE '{0}%') AND (user_id IN(SELECT person_id FROM dbo.T_UserUnitPersonRelation WHERE user_id={1}) OR user_id IN(SELECT user_id FROM Bane_UserPermissRelation WHERE per_id in({2})))", param.folder_path, param.user_id, string.Join(",", perList)));
}
}
else
if (isManager)
{
sb.AppendFormat(@" AND org_id={0} ", param.orgId);
}
else
{
sb.Append(string.Format(" AND org_id={0} AND (user_id IN(SELECT person_id FROM dbo.T_UserUnitPersonRelation WHERE user_id={1}) OR user_id IN(SELECT user_id FROM Bane_UserPermissRelation WHERE per_id IN({2})))", param.orgId, param.user_id, string.Join(",", perList)));
}
sb.Append(string.Format(@") users LEFT JOIN
(SELECT ROW_NUMBER() OVER(PARTITION BY user_identify ORDER BY start_date DESC) rank,CONVERT(varchar(100),start_date,23) AS start_date,
CONVERT(varchar(100),end_date,23) AS end_date,end_reason,user_identify AS a0177 FROM Bane_RecoveryInfo
WHERE 1=1 "));
if (!string.IsNullOrEmpty(param.baneEnd))
{
sb.Append(string.Format(" AND end_reason='{0}' ", param.baneEnd));
}
sb.Append(string.Format(@") info ON users.user_identify=info.a0177 WHERE info.rank=1 AND users.rowNumber>{0};", (param.page - 1) * param.rows));
DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text);
return(HCQ2_Common.Data.DataTableHelper.DataTableToIList <BaneListModel>(dt));
}
/// <summary>
/// 获取定期尿检记录数据
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public List <BaneProModel> GetBaneProData(BaneListParams param)
{
sb?.Clear();
sb.Append(string.Format(@"SELECT TOP {0} ur_id,user_name,user_sex,user_age,user_identify,user_type,this_date,next_date,ISNULL(approve_status,2) AS approve_status,hiden_name,hiden_identify FROM
(SELECT ROW_NUMBER() OVER(ORDER BY user_id ASC) rowNumber,ur_next_date,user_name,user_sex,DATEDIFF(yy,CONVERT(varchar,user_birth,101),GETDATE())AS user_age,user_identify,user_type,CONVERT(varchar(100),ur_next_date,20) AS next_date,LEFT(user_name,1)+REPLICATE('*',LEN(user_name)-1) AS hiden_name,(CASE WHEN LEN(user_identify)=18 THEN LEFT(user_identify,6)+REPLICATE('*',8)+RIGHT(user_identify,4) WHEN LEN(user_identify)=15 THEN LEFT(user_identify,6)+REPLICATE('*',6)+RIGHT(user_identify,3) ELSE REPLICATE('*',LEN(user_identify)) END) AS hiden_identify FROM dbo.Bane_User WHERE 1=1 ", param.rows));
if (param.isParent)
{
sb.Append(string.Format(@" AND org_id in(SELECT folder_id FROM dbo.T_OrgFolder WHERE folder_path <> '{0}' AND folder_path LIKE '{0}%') AND user_id IN(SELECT person_id FROM dbo.T_UserUnitPersonRelation WHERE user_id={1}) ", param.folder_path, param.user_id));
}
else
{
sb.Append(string.Format(" AND org_id={0} AND user_id IN(SELECT person_id FROM dbo.T_UserUnitPersonRelation WHERE user_id={1}) ", param.orgId, param.user_id));
}
if (!string.IsNullOrEmpty(param.baneName))
{
sb.Append(string.Format(" AND user_name LIKE '%{0}%' ", param.baneName));
}
if (!string.IsNullOrEmpty(param.baneType))
{
sb.Append(string.Format(" AND user_type='{0}' ", param.baneType));
}
//定期尿检页面
if (param.baneTask == "0")
{
sb.Append(@")users LEFT JOIN (SELECT * FROM(
SELECT ROW_NUMBER() OVER(PARTITION BY user_identify ORDER BY ur_id DESC) number, ur_id, user_identify AS a0177,approve_status,
CONVERT(varchar(100), ur_reality_date, 20) AS this_date,ur_reality_date FROM dbo.Bane_UrinalysisRecord) uRecord WHERE uRecord.number = 1)");
}
else
{
sb.Append(")users INNER JOIN (SELECT ur_id,user_identify AS a0177,approve_status,CONVERT(varchar(100),ur_reality_date,20) AS this_date,ur_reality_date FROM dbo.Bane_UrinalysisRecord WHERE approve_status=0)");
}
sb.Append(string.Format(@" record ON record.a0177 = users.user_identify WHERE users.rowNumber>{0} ", (param.page - 1) * param.rows));
switch (param.banedays)
{
//已过检测
case "0": sb.AppendFormat(" AND DATEDIFF(dd,CONVERT(varchar,users.ur_next_date,101),GETDATE())>{0}", param.banedays); break;
//一周内
case "-7": sb.AppendFormat(" AND DATEDIFF(dd,CONVERT(varchar,users.ur_next_date,101),GETDATE()) BETWEEN -7 AND 0"); break;
//本月应检
case "30": sb.AppendFormat(" AND MONTH(ur_next_date)=MONTH(GETDATE()) "); break;
//本月已检
case "-30": sb.AppendFormat(" AND LEFT(CONVERT(varchar(100),GETDATE(),23),7)=LEFT(CONVERT(varchar(100),record.ur_reality_date,23),7) "); break;
}
DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text);
return(HCQ2_Common.Data.DataTableHelper.DataTableToIList <BaneProModel>(dt));
}
/// <summary>
/// 统计定期尿检人员数量
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public int GetBaneProDataCount(BaneListParams param)
{
sb?.Clear();
sb.Append(string.Format(@"SELECT COUNT(*) FROM
(SELECT ur_next_date,user_name,user_identify,user_type FROM dbo.Bane_User WHERE 1=1 "));
if (param.isParent)
{
sb.Append(string.Format(@" AND org_id in(SELECT folder_id FROM dbo.T_OrgFolder WHERE folder_path <> '{0}' AND folder_path LIKE '{0}%') AND user_id IN(SELECT person_id FROM dbo.T_UserUnitPersonRelation WHERE user_id={1}) ", param.folder_path, param.user_id));
}
else
{
sb.Append(string.Format(" AND org_id={0} AND user_id IN(SELECT person_id FROM dbo.T_UserUnitPersonRelation WHERE user_id={1}) ", param.orgId, param.user_id));
}
if (!string.IsNullOrEmpty(param.baneName))
{
sb.Append(string.Format(" AND user_name LIKE '%{0}%' ", param.baneName));
}
if (!string.IsNullOrEmpty(param.baneType))
{
sb.Append(string.Format(" AND user_type='{0}' ", param.baneType));
}
if (param.baneTask == "0")
{
sb.Append(@")users LEFT JOIN (SELECT * FROM(
SELECT ROW_NUMBER() OVER(PARTITION BY user_identify ORDER BY ur_id ASC) number, ur_id, user_identify AS a0177, approve_status,
CONVERT(varchar(100), ur_reality_date, 23) AS this_date,ur_reality_date FROM dbo.Bane_UrinalysisRecord) uRecord WHERE uRecord.number = 1) record ON record.a0177 = users.user_identify ");
}
else
{
sb.Append(")users INNER JOIN (SELECT ur_id,user_identify AS a0177,ur_reality_date FROM dbo.Bane_UrinalysisRecord WHERE approve_status=0) record ON record.a0177 = users.user_identify ");
}
switch (param.banedays)
{
//已过检测
case "0": sb.AppendFormat(" WHERE DATEDIFF(dd,CONVERT(varchar,users.ur_next_date,101),GETDATE())>{0}", param.banedays); break;
//一周内
case "-7": sb.AppendFormat(" WHERE DATEDIFF(dd,CONVERT(varchar,users.ur_next_date,101),GETDATE()) BETWEEN -7 AND 0"); break;
//本月应检
case "30": sb.AppendFormat(" WHERE MONTH(ur_next_date)=MONTH(GETDATE()) "); break;
//本月已检
case "-30": sb.AppendFormat(" AND LEFT(CONVERT(varchar(100),GETDATE(),23),7)=LEFT(CONVERT(varchar(100),record.ur_reality_date,23),7) "); break;
}
return(HCQ2_Common.Helper.ToInt(HCQ2_Common.SQL.SqlHelper.ExecuteScalar(sb.ToString())));
}
public ActionResult GetBaneUserData()
{
string keyword = RequestHelper.GetDeStrByName("baneName"), //用户名
baneType = RequestHelper.GetDeStrByName("baneType"), //人员类别，戒毒，康复
baneEnd = RequestHelper.GetDeStrByName("baneEnd"), //结束监管原因
folder_path = RequestHelper.GetStrByName("folder_path");
int page = Helper.ToInt(Request["page"]),
rows = Helper.ToInt(Request["rows"]),
orgId = RequestHelper.GetIntByName("orgId"),//组织机构ID
isParent = RequestHelper.GetIntByName("isParent");
keyword = (!string.IsNullOrEmpty(keyword)) ? HttpUtility.UrlDecode(keyword) : keyword;
BaneListParams param = new BaneListParams(operateContext.Usr.user_id, keyword, baneType, baneEnd, folder_path, (isParent > 0?true:false), orgId, page, rows, "", "");
List <BaneListModel> bane = base.operateContext.bllSession.Bane_User.GetBaneData(param);
TableModel tModel = new TableModel()
{
total = operateContext.bllSession.Bane_User.GetGetBaneDataCount(param),
rows = bane
};
return(Json(tModel, JsonRequestBehavior.AllowGet));
}
