/// <summary> /// 获取记事本 /// </summary> /// <param name="context"></param> /// <param name="msg"></param> /// <param name="P1">类型</param> /// <param name="P2">查询条件</param> /// <param name="strUserName"></param> public void GETNOTELIST(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo) { int DataID = -1; int.TryParse(context.Request["ID"] ?? "-1", out DataID);//页码 int page = 0; int.TryParse(context.Request["p"] ?? "1", out page);//页码 page = page == 0 ? 1 : page; int recordCount = 0; string strWhere = string.Format(" note.ComId={0} And note.CRUser='******'", UserInfo.User.ComId, UserInfo.User.UserName); if (P1 != "")//分类 { strWhere += string.Format("And note.LeiBie={0}", P1); } if (P2 != "")//内容查询 { strWhere += string.Format(" And note.NoteContent like '%{0}%'", P2); } if (DataID != -1) { string strIsHasDataQX = new JH_Auth_QY_ModelB().ISHASDATAREADQX("NOTE", DataID, UserInfo); if (strIsHasDataQX == "Y") { strWhere += string.Format(" And note.ID = '{0}'", DataID); } } DataTable dt = new SZHL_NOTEB().GetDataPager(" SZHL_NOTE note inner join JH_Auth_ZiDian zd on LeiBie= zd.ID and Class=8 ", " note.ID,note.NoteTitle,note.NoteContent,note.LeiBie,note.CRDate,note.CRUser,note.UPUser,note.UPDDate,zd.TypeName ", 8, page, "note.CRDate desc", strWhere, ref recordCount); msg.Result = dt; msg.Result1 = Math.Ceiling(recordCount * 1.0 / 8); }
/// <summary> /// 数据库备份还原列表 /// </summary> /// <param name="context"></param> /// <param name="msg"></param> /// <param name="P1"></param> /// <param name="P2"></param> /// <param name="UserInfo"></param> public void GETDBBRLIST(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo) { string userName = UserInfo.User.UserName; string strWhere = " 1=1 and ComId=" + UserInfo.User.ComId; int DataID = -1; int.TryParse(context.Request.QueryString["ID"] ?? "-1", out DataID);//记录Id if (DataID != -1) { string strIsHasDataQX = new JH_Auth_QY_ModelB().ISHASDATAREADQX("DBGL", DataID, UserInfo); if (strIsHasDataQX == "Y") { strWhere += string.Format(" And ID = '{0}'", DataID); } } if (P1 != "") { int page = 0; int pagecount = 8; int.TryParse(context.Request.QueryString["p"] ?? "1", out page); int.TryParse(context.Request.QueryString["pagecount"] ?? "8", out pagecount);//页数 page = page == 0 ? 1 : page; int total = 0; DataTable dt = new DataTable(); switch (P1) { case "0": //手机单条数据 { //设置usercenter已读 new JH_Auth_User_CenterB().ReadMsg(UserInfo, DataID, "DBGL"); } break; case "1": //备份 { strWhere += " And Type ='" + P1 + "'"; } break; case "2": //还原 { strWhere += " And Type ='" + P1 + "'"; } break; } dt = new SZHL_DBGLB().GetDataPager(" SZHL_DBGL ", " *,case Type when '1' then '备份' when '2' then '还原' end as TypeName ", pagecount, page, " CRDate desc", strWhere, ref total); msg.Result = dt; msg.Result1 = total; } }
/// <summary> /// 当前用户的资产列表 /// </summary> public void GETZCGLLISTTHISUSER(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo) { string userName = UserInfo.User.UserName; string strWhere = " z.IsDel=0 and z.ComId=" + UserInfo.User.ComId + " and z.UserName='******'"; string typeid = context.Request["typeid"] ?? ""; if (typeid != "") { strWhere += string.Format(" And z.TypeID='{0}' ", typeid); } string searchstr = context.Request["searchstr"] ?? ""; searchstr = searchstr.TrimEnd(); if (searchstr != "") { strWhere += string.Format(" And ( z.Name like '%{0}%' or z.Code like '%{0}%')", searchstr); } int DataID = -1; int.TryParse(context.Request.QueryString["ID"] ?? "-1", out DataID);//记录Id if (DataID != -1) { string strIsHasDataQX = new JH_Auth_QY_ModelB().ISHASDATAREADQX("ZCGL", DataID, UserInfo); if (strIsHasDataQX == "Y") { strWhere += string.Format(" And z.ID = '{0}'", DataID); } } int page = 0; int pagecount = 10; int.TryParse(context.Request.QueryString["p"] ?? "1", out page); int.TryParse(context.Request.QueryString["pagecount"] ?? "10", out pagecount);//页数 page = page == 0 ? 1 : page; int total = 0; DataTable dt = new DataTable(); dt = new SZHL_ZCGLB().GetDataPager("SZHL_ZCGL z left join SZHL_ZCGL_Type t on z.TypeID=t.ID left join SZHL_ZCGL_Location l on z.LocationID=l.ID left join JH_Auth_User u on z.UserName=u.UserName left join JH_Auth_Branch b on z.BranchCode=b.DeptCode", "z.*, b.DeptName, u.UserRealName, t.Title, l.Title as LocTitle", pagecount, page, " z.CRDate desc", strWhere, ref total); msg.Result = dt; msg.Result1 = total; }
/// <summary> /// 个人工资条列表 /// </summary> /// <param name="context"></param> /// <param name="msg"></param> /// <param name="P1"></param> /// <param name="P2"></param> /// <param name="UserInfo"></param> public void GETGZDLIST(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo) { string strWhere = string.Format(" ComId={0} and UserName='******' ", UserInfo.User.ComId, UserInfo.User.UserName); int DataID = -1; int.TryParse(context.Request("ID") ?? "-1", out DataID);//记录Id if (DataID != -1) { string strIsHasDataQX = new JH_Auth_QY_ModelB().ISHASDATAREADQX("XZFF", DataID, UserInfo); if (strIsHasDataQX == "Y") { strWhere += string.Format(" And ID = '{0}'", DataID); } } int page = 0; int pagecount = 8; int.TryParse(context.Request("p") ?? "1", out page); //页码 int.TryParse(context.Request("pagecount") ?? "8", out pagecount); //页数 page = page == 0 ? 1 : page; int recordCount = 0; string strContent = context.Request("Content") ?? ""; if (strContent != "") { strWhere += string.Format(" And ( title like '%{0}%' )", strContent); } var dt = new SZHL_XZ_GZDB().Db.Queryable <SZHL_XZ_GZD>().Where(strWhere).OrderBy(it => it.CRDate, OrderByType.Desc).ToPageList(page, pagecount, ref recordCount); msg.Result = dt; msg.Result1 = recordCount; }
/// <summary> /// 个人工资条列表 /// </summary> /// <param name="context"></param> /// <param name="msg"></param> /// <param name="P1"></param> /// <param name="P2"></param> /// <param name="UserInfo"></param> public void GETGZDLIST(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo) { string strWhere = string.Format(" ComId={0} and UserName='******' ", UserInfo.User.ComId, UserInfo.User.UserName); int DataID = -1; int.TryParse(context.Request["ID"] ?? "-1", out DataID);//记录Id if (DataID != -1) { string strIsHasDataQX = new JH_Auth_QY_ModelB().ISHASDATAREADQX("XZFF", DataID, UserInfo); if (strIsHasDataQX == "Y") { strWhere += string.Format(" And ID = '{0}'", DataID); } } int page = 0; int pagecount = 8; int.TryParse(context.Request["p"] ?? "1", out page); //页码 int.TryParse(context.Request["pagecount"] ?? "8", out pagecount); //页数 page = page == 0 ? 1 : page; int recordCount = 0; string strContent = context.Request["Content"] ?? ""; if (strContent != "") { strWhere += string.Format(" And ( title like '%{0}%' )", strContent); } DataTable dt = new SZHL_XZ_GZDB().GetDataPager(" SZHL_XZ_GZD ", " * ", pagecount, page, " CRDate desc ", strWhere, ref recordCount); msg.Result = dt; msg.Result1 = recordCount; }
public void GETJFBXLIST(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo) { string userName = UserInfo.User.UserName; string strUserName = context.Request["username"] ?? "";//经费报销统计中的查看列表需要的参数 if (!string.IsNullOrEmpty(strUserName)) { userName = strUserName; } string strWhere = " ComId =" + UserInfo.User.ComId.Value; DataTable dtList = new DataTable(); int page = 0; int pagecount = 8; int.TryParse(context.Request["p"] ?? "1", out page); //页码 int.TryParse(context.Request["pagecount"] ?? "8", out pagecount); //页数 int DataID = -1; int.TryParse(context.Request["ID"] ?? "-1", out DataID);//记录Id if (DataID != -1) { string strIsHasDataQX = new JH_Auth_QY_ModelB().ISHASDATAREADQX("JFBX", DataID, UserInfo); if (strIsHasDataQX == "Y") { strWhere += string.Format(" And ID = '{0}'", DataID); } } switch (P1) { case "0": //手机单条数据 { //设置usercenter已读 new JH_Auth_User_CenterB().ReadMsg(UserInfo, DataID, "JFBX"); } break; case "1": //创建的 { strWhere += " And CRUser ='******'"; } break; case "2": //待审核 { var intProD = new Yan_WF_PIB().GetDSH(UserInfo.User).Select(d => d.PIID.ToString()).ToList(); if (intProD.Count > 0) { strWhere += " And intProcessStanceid in (" + (intProD.ListTOString(',') == "" ? "0" : intProD.ListTOString(',')) + ")"; } else { strWhere += " And 1=0"; } } break; case "3": //已审核 { var intProD = new Yan_WF_PIB().GetYSH(UserInfo.User).Select(d => d.PIID.ToString()).ToList(); if (intProD.Count > 0) { strWhere += " And intProcessStanceid in (" + (intProD.ListTOString(',') == "" ? "0" : intProD.ListTOString(',')) + ")"; } else { strWhere += " And 1=0"; } } break; case "4": //获取当前登录人负责的下属人员 string Users = new JH_Auth_UserB().GetUserBranchUsers(UserInfo.User.ComId.Value, UserInfo.User.UserName).Select(d => d.UserName).ToList().ListTOString(','); strWhere += string.Format("and CRUser in ('{0}')", Users.ToFormatLike()); break; } //根据创建时间查询 string time = context.Request["time"] ?? ""; if (time != "") { if (time == "1") //近一周 { strWhere += string.Format(" And datediff(day,BXDate,getdate())<7"); } else if (time == "2") { //近一月 strWhere += string.Format(" And datediff(day,BXDate,getdate())<30"); } else if (time == "3") //自定义时间 { string strTime = context.Request["starTime"] ?? ""; string endTime = context.Request["endTime"] ?? ""; if (strTime != "") { strWhere += string.Format(" And BXDate >='{0}'", strTime); } if (endTime != "") { strWhere += string.Format(" And BXDate <='{0}'", endTime); } } } string strContent = context.Request["Content"] ?? ""; strContent = strContent.TrimEnd(); if (strContent != "") { strWhere += string.Format(" And (BranchName like '%{0}%' or ShenQingRen like '%{0}%' or FormCode like '%{0}%' )", strContent); } page = page == 0 ? 1 : page; int recordCount = 0; dtList = new SZHL_JFBXB().GetDataPager(" SZHL_JFBX ", "*,dbo.fn_PDStatus(intProcessStanceid) AS StateName ", pagecount, page, "CRDate desc", strWhere, ref recordCount); msg.Result = dtList; msg.Result1 = recordCount; }
/// <summary> /// 请假列表 /// </summary> /// <param name="context"></param> /// <param name="msg"></param> /// <param name="P1"></param> /// <param name="P2"></param> /// <param name="UserInfo"></param> public void GETCCXJLIST(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo) { string userName = UserInfo.User.UserName; string strWhere = " 1=1 and cc.ComId=" + UserInfo.User.ComId; string leibie = context.Request["lb"] ?? ""; if (leibie != "") { strWhere += string.Format(" And cc.LeiBie='{0}' ", leibie); } string strContent = context.Request["Content"] ?? ""; strContent = strContent.TrimEnd(); if (strContent != "") { strWhere += string.Format(" And ( cc.ZhuYaoShiYou like '%{0}%' )", strContent); } int DataID = -1; int.TryParse(context.Request["ID"] ?? "-1", out DataID);//记录Id if (DataID != -1) { string strIsHasDataQX = new JH_Auth_QY_ModelB().ISHASDATAREADQX("CCXJ", DataID, UserInfo); if (strIsHasDataQX == "Y") { strWhere += string.Format(" And cc.ID = '{0}'", DataID); } } if (P1 != "") { int page = 0; int pagecount = 8; int.TryParse(context.Request["p"] ?? "1", out page); int.TryParse(context.Request["pagecount"] ?? "8", out pagecount);//页数 page = page == 0 ? 1 : page; int total = 0; DataTable dt = new DataTable(); switch (P1) { case "0": //手机单条数据 { //设置usercenter已读 new JH_Auth_User_CenterB().ReadMsg(UserInfo, DataID, "CCXJ"); } break; case "1": //创建的 { strWhere += " And cc.CRUser ='******'"; } break; case "2": //待审核 { var intProD = new Yan_WF_PIB().GetDSH(UserInfo.User).Select(d => d.PIID.ToString()).ToList(); if (intProD.Count > 0) { strWhere += " And cc.intProcessStanceid in (" + (intProD.ListTOString(',') == "" ? "0" : intProD.ListTOString(',')) + ")"; } else { strWhere += " And 1=0"; } } break; case "3": //已审核 { var intProD = new Yan_WF_PIB().GetYSH(UserInfo.User).Select(d => d.PIID.ToString()).ToList(); if (intProD.Count > 0) { strWhere += " And cc.intProcessStanceid in (" + (intProD.ListTOString(',') == "" ? "0" : intProD.ListTOString(',')) + ")"; } else { strWhere += " And 1=0"; } } break; } dt = new SZHL_CCXJB().GetDataPager("SZHL_CCXJ cc left join JH_Auth_ZiDian zd on cc.LeiBie=zd.ID", "cc.*,zd.TypeName ,dbo.fn_PDStatus(cc.intProcessStanceid) AS StateName", pagecount, page, " cc.CRDate desc", strWhere, ref total); if (dt.Rows.Count > 0) { dt.Columns.Add("FileList", Type.GetType("System.Object")); foreach (DataRow dr in dt.Rows) { if (dr["Files"] != null && dr["Files"].ToString() != "") { dr["FileList"] = new FT_FileB().GetEntities(" ID in (" + dr["Files"].ToString() + ")"); } } } msg.Result = dt; msg.Result1 = total; } }
/// <summary> /// 获取话题列表 /// </summary> /// <param name="context"></param> /// <param name="msg"></param> /// <param name="P1"></param> /// <param name="P2"></param> /// <param name="UserInfo"></param> public void GETHTLIST(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo) { string userName = UserInfo.User.UserName; int page = 0; int pagecount = 8; int.TryParse(context.Request["p"] ?? "1", out page); //页码 int.TryParse(context.Request["pagecount"] ?? "8", out pagecount); //页数 page = page == 0 ? 1 : page; int recordCount = 0; string strWhere = string.Format(" sq.ComId={0} ", UserInfo.User.ComId); string strContent = context.Request["Content"] ?? ""; strContent = strContent.TrimEnd(); if (strContent != "") { strWhere += string.Format(" And ( sq.HTNR like '%{0}%' )", strContent); } string leibie = context.Request["lb"] ?? ""; if (leibie != "") { strWhere += string.Format(" And sq.LeiBie='{0}' ", leibie); } int DataID = -1; int.TryParse(context.Request["ID"] ?? "-1", out DataID);//记录Id if (DataID != -1) { string strIsHasDataQX = new JH_Auth_QY_ModelB().ISHASDATAREADQX("TSSQ", DataID, UserInfo); if (strIsHasDataQX == "Y") { strWhere += string.Format(" And sq.ID = '{0}'", DataID); } } if (P1 != "") { switch (P1) { case "0": { //设置usercenter已读 new JH_Auth_User_CenterB().ReadMsg(UserInfo, DataID, "TSSQ"); } break; case "1": //我的 { strWhere += " And sq.CRUser='******'"; } break; case "2": //我评论过的 { //DataTable dtPL = new JH_Auth_TLB().GetDTByCommand(string.Format("SELECT tl.MSGTLYID FROM JH_Auth_TL tl WHERE tl.MSGType='TSSQ' AND tl.MsgISShow <> 'Y' AND tl.CRUser='******'", UserInfo.User.UserName)); string htIDs = new JH_Auth_TLB().GetEntities(d => d.MSGType == "TSSQ" && d.CRUser == UserInfo.User.UserName && (d.MsgISShow != "Y" && d.MsgISShow != "N")).Select(d => d.MSGTLYID).ToList().ListTOString(','); if (!string.IsNullOrWhiteSpace(htIDs)) { strWhere += "And sq.ID IN (" + htIDs + ") "; } else { strWhere += "And sq.ID ='' "; } } break; case "3": //全部 { strWhere += " And (ISNULL(sq.CYR,'') = '' OR ','+sq.CYR+',' like '%," + userName + ",%' )"; } break; } DataTable dt = new SZHL_TSSQB().GetDataPager(@" SZHL_TSSQ sq LEFT JOIN JH_Auth_ZiDian zd ON sq.LeiBie=zd.ID and Class=19", " sq.*,zd.TypeName ", pagecount, page, " sq.CRDate DESC ", strWhere, ref recordCount); #region 附件评论 string Ids = ""; string fileIDs = ""; foreach (DataRow row in dt.Rows) { Ids += row["ID"].ToString() + ","; if (!string.IsNullOrEmpty(row["Files"].ToString())) { fileIDs += row["Files"].ToString() + ","; } } Ids = Ids.TrimEnd(','); fileIDs = fileIDs.TrimEnd(','); if (Ids != "") { List <FT_File> FileList = new List <FT_File>(); DataTable dtPL = new JH_Auth_TLB().GetDTByCommand(string.Format("SELECT tl.* FROM JH_Auth_TL tl WHERE tl.MSGType='TSSQ' AND (ISNULL(tl.MsgISShow,'') <> 'Y' and ISNULL(tl.MsgISShow,'') <> 'N') AND tl.MSGTLYID in ({0}) ORDER BY CRDate ASC", Ids)); DataTable zandt = new JH_Auth_TLB().GetDTByCommand(string.Format("SELECT tl.* FROM JH_Auth_TL tl WHERE tl.MSGType='TSSQ' AND MsgISShow='Y' AND tl.MSGTLYID in ({0})", Ids)); if (!string.IsNullOrEmpty(fileIDs)) { int[] fileId = fileIDs.SplitTOInt(','); FileList = new FT_FileB().GetEntities(d => fileId.Contains(d.ID)).ToList(); } dt.Columns.Add("PLList", Type.GetType("System.Object")); dt.Columns.Add("FileList", Type.GetType("System.Object")); dt.Columns.Add("ZanList", Type.GetType("System.Object")); dt.Columns.Add("IsZan", Type.GetType("System.Object")); foreach (DataRow row in dt.Rows) { DataTable dtPLs = dtPL.FilterTable("MSGTLYID='" + row["ID"] + "'"); dtPLs.Columns.Add("FileList", Type.GetType("System.Object")); foreach (DataRow dr in dtPLs.Rows) { if (dr["MSGisHasFiles"] != null && dr["MSGisHasFiles"].ToString() != "") { int[] fileIds = dr["MSGisHasFiles"].ToString().SplitTOInt(','); dr["FileList"] = new FT_FileB().GetEntities(d => fileIds.Contains(d.ID)); } } row["PLList"] = dtPLs; row["ZanList"] = zandt.FilterTable("MSGTLYID='" + row["ID"] + "'"); row["IsZan"] = zandt.FilterTable(" MSGTLYID='" + row["ID"] + "' and CRUser='******'").Rows.Count > 0 ? "Y" : "N"; if (FileList.Count > 0) { string[] fileIds = row["Files"].ToString().Split(','); row["FileList"] = FileList.Where(d => fileIds.Contains(d.ID.ToString())); } } } #endregion msg.Result = dt; msg.Result1 = recordCount; } }
/// <summary> /// 用车列表 /// </summary> /// <param name="context"></param> /// <param name="msg"></param> /// <param name="P1"></param> /// <param name="P2"></param> /// <param name="UserInfo"></param> public void GETYCGLLIST(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo) { string userName = UserInfo.User.UserName; string strWhere = " 1=1 and yc.ComId=" + UserInfo.User.ComId; string leibie = context.Request["lb"] ?? ""; if (leibie != "") { strWhere += string.Format(" And yc.CarID='{0}' ", leibie); } string strContent = context.Request["Content"] ?? ""; strContent = strContent.TrimEnd(); if (strContent != "") { strWhere += string.Format(" And ( yc.Remark like '%{0}%' or yc.StartAddress like '%{0}%' or yc.EndAddress like '%{0}%')", strContent); } int DataID = -1; int.TryParse(context.Request["ID"] ?? "-1", out DataID);//记录Id if (DataID != -1) { string strIsHasDataQX = new JH_Auth_QY_ModelB().ISHASDATAREADQX("YCGL", DataID, UserInfo); if (strIsHasDataQX == "Y") { strWhere += string.Format(" And yc.ID = '{0}'", DataID); } } if (P1 != "") { int page = 0; int pagecount = 8; int.TryParse(context.Request["p"] ?? "1", out page); int.TryParse(context.Request["pagecount"] ?? "8", out pagecount);//页数 page = page == 0 ? 1 : page; int total = 0; DataTable dt = new DataTable(); #region no use // switch (P1) // { // case "0": // string colNme1 = @"ycgl.*,car.CarBrand,car.CarType,car.CarNum , case WHEN wfpi.isComplete is null and wfpi.IsCanceled is null THEN '正在审批' // when wfpi.isComplete='Y' then '已审批' WHEN wfpi.IsCanceled='Y' then '已退回' END StateName"; // //strWhere += " And cc.CRUser ='******'"; // dt = new SZHL_CCXJB().GetDataPager("SZHL_YCGL ycgl left outer join SZHL_YCGL_CAR car on ycgl.CarID=car.ID inner join Yan_WF_PI wfpi on ycgl.intProcessStanceid=wfpi.ID", colNme1, 8, page, " ycgl.CRDate desc", strWhere, ref total); // break; // case "1": // string colNme = @"ycgl.*,car.CarBrand,car.CarType,car.CarNum , case WHEN wfpi.isComplete is null and wfpi.IsCanceled is null THEN '正在审批' // when wfpi.isComplete='Y' then '已审批' WHEN wfpi.IsCanceled='Y' then '已退回' END StateName"; // strWhere += " And ycgl.CRUser ='******'"; // dt = new SZHL_CCXJB().GetDataPager("SZHL_YCGL ycgl left outer join SZHL_YCGL_CAR car on ycgl.CarID=car.ID inner join Yan_WF_PI wfpi on ycgl.intProcessStanceid=wfpi.ID", colNme, 8, page, " ycgl.CRDate desc", strWhere, ref total); // break; // case "2": // List<string> intProD = new Yan_WF_PIB().GetDSH(UserInfo.User).Select(d => d.PIID.ToString()).ToList(); // if (intProD.Count > 0) // { // string tableNameD = string.Format(@" SZHL_YCGL ycgl left outer join SZHL_YCGL_CAR car on ycgl.CarID=car.ID"); // string tableColumnD = "ycgl.* ,car.CarBrand,car.CarType,car.CarNum , '正在审批' StateName"; // strWhere += " And ycgl.intProcessStanceid in (" + (intProD.ListTOString(',') == "" ? "0" : intProD.ListTOString(',')) + ")"; // //string strSql = string.Format("Select {0} From {1} where {2} order by cc.CRDate desc", tableColumnD, tableNameD, strWhere); // dt = new SZHL_CCXJB().GetDataPager(tableNameD, tableColumnD, 8, page, " ycgl.CRDate desc", strWhere, ref total); // } // break; // case "3": // List<Yan_WF_TI> ListData = new Yan_WF_TIB().GetEntities("TaskUserID ='" + UserInfo.User.UserName + "' AND EndTime IS NOT NULL AND TaskUserView!='发起表单'").ToList(); // List<string> intPro = ListData.Select(d => d.PIID.ToString()).ToList(); // string tableName = string.Format(@" SZHL_YCGL ycgl left outer join SZHL_YCGL_CAR car on ycgl.CarID=car.ID inner join Yan_WF_PI wfpi on ycgl.intProcessStanceid=wfpi.ID"); // string tableColumn = "ycgl.* ,car.CarBrand,car.CarType,car.CarNum , case when wfpi.IsCanceled is null then '已审批' WHEN wfpi.IsCanceled='Y' then '已退回' END StateName "; // strWhere += " And ycgl.intProcessStanceid in (" + (intPro.ListTOString(',') == "" ? "0" : intPro.ListTOString(',')) + ")"; // dt = new SZHL_CCXJB().GetDataPager(tableName, tableColumn, 8, page, " ycgl.CRDate desc", strWhere, ref total); // break; // } #endregion switch (P1) { case "0": //手机单条数据 { //设置usercenter已读 new JH_Auth_User_CenterB().ReadMsg(UserInfo, DataID, "YCGL"); } break; case "1": //创建的 { strWhere += " And yc.CRUser ='******'"; } break; case "2": //待审核 { var intProD = new Yan_WF_PIB().GetDSH(UserInfo.User).Select(d => d.PIID.ToString()).ToList(); if (intProD.Count > 0) { strWhere += " And yc.intProcessStanceid in (" + (intProD.ListTOString(',') == "" ? "0" : intProD.ListTOString(',')) + ")"; } else { strWhere += " And 1=0"; } } break; case "3": //已审核 { var intProD = new Yan_WF_PIB().GetYSH(UserInfo.User).Select(d => d.PIID.ToString()).ToList(); if (intProD.Count > 0) { strWhere += " And yc.intProcessStanceid in (" + (intProD.ListTOString(',') == "" ? "0" : intProD.ListTOString(',')) + ")"; } else { strWhere += " And 1=0"; } } break; } dt = new SZHL_CCXJB().GetDataPager("SZHL_YCGL yc left join SZHL_YCGL_CAR car on yc.CarID=car.ID", "yc.*,car.CarBrand,car.CarType,car.CarNum ,dbo.fn_PDStatus(yc.intProcessStanceid) AS StateName", pagecount, page, " yc.CRDate desc", strWhere, ref total); if (dt.Rows.Count > 0) { dt.Columns.Add("FileList", Type.GetType("System.Object")); foreach (DataRow dr in dt.Rows) { if (dr["Files"] != null && dr["Files"].ToString() != "") { dr["FileList"] = new FT_FileB().GetEntities(" ID in (" + dr["Files"].ToString() + ")"); } } } msg.Result = dt; msg.Result1 = total; } }
/// <summary> /// 获取提醒事项列表 /// </summary> /// <param name="context"></param> /// <param name="msg"></param> /// <param name="P1"></param> /// <param name="P2"></param> /// <param name="UserInfo"></param> public void GETTXSXLIST(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo) { string userName = UserInfo.User.UserName; string strWhere = " 1=1 and ComId=" + UserInfo.User.ComId; string type = context.Request["lb"] ?? "1"; if (type == "1") { strWhere += string.Format(" And CRUser='******'", userName); } else if (type == "2") { strWhere += string.Format(" And ','+TXUser+',' like '%,{0},%'", userName); } if (P1 == "2") { strWhere += string.Format(" and Status='1' "); } else if (P1 == "1") { strWhere += string.Format(" and Status='0' "); } string strContent = context.Request["Content"] ?? ""; strContent = strContent.TrimEnd(); if (strContent != "") { //strWhere += string.Format(" And ( TXContent like '%{0}%' or TXUser like '%{0}%' )", strContent); strWhere += string.Format(" And ( TXContent like '%{0}%')", strContent); } int DataID = -1; int.TryParse(context.Request["ID"] ?? "-1", out DataID);//记录Id if (DataID != -1) { string strIsHasDataQX = new JH_Auth_QY_ModelB().ISHASDATAREADQX("TXSX", DataID, UserInfo); if (strIsHasDataQX == "Y") { strWhere += string.Format(" And ID = '{0}'", DataID); } } int page = 0; int pagecount = 8; int.TryParse(context.Request["p"] ?? "1", out page); int.TryParse(context.Request["pagecount"] ?? "8", out pagecount);//页数 page = page == 0 ? 1 : page; int total = 0; DataTable dt = new DataTable(); if (P2 == "-1" || P2 == "") { strWhere += " and type in ('0','1','2')"; } else { strWhere += " and type ='" + P2 + "'"; } dt = new SZHL_CCXJB().GetDataPager("SZHL_TXSX", "*", pagecount, page, " CRDate desc", strWhere, ref total); dt.Columns.Add("TXMS", Type.GetType("System.Object")); foreach (DataRow dr in dt.Rows) { string html = "", html2 = "", html3 = ""; switch (dr["TXType"].ToString()) { case "0": html2 = "立即发送"; break; case "1": html2 = "仅一次," + dr["Date"] + " " + dr["Hour"] + ":" + dr["Minute"]; break; case "2": html2 = "每个工作日的" + dr["Hour"] + ":" + dr["Minute"]; break; case "3": html2 = "每天的" + dr["Hour"] + ":" + dr["Minute"]; break; case "4": html2 = "每周" + dr["Days"] + "的" + dr["Hour"] + ":" + dr["Minute"]; break; case "5": html2 = "每月" + dr["Days"] + "日的" + dr["Hour"] + ":" + dr["Minute"]; break; } if (!(dr["TXType"].ToString() == "0" || dr["TXType"].ToString() == "1")) { switch (dr["CFType"].ToString()) { case "1": html3 = ",无结束时间"; break; case "2": html3 = "," + dr["CFCount"].ToString() + "次后结束,已经执行" + (string.IsNullOrWhiteSpace(dr["ZXCount"].ToString()) ? "0" : dr["ZXCount"].ToString()) + "次"; break; case "3": html3 = ",结束时间:" + (string.IsNullOrWhiteSpace(dr["CFJZDate"].ToString()) ? "" : dr["CFJZDate"].ToString().Substring(0, 10)); break; } } html = html2 + html3; dr["TXMS"] = html; } msg.Result = dt; msg.Result1 = total; }
/// <summary> /// 会议列表 /// </summary> /// <param name="context"></param> /// <param name="msg"></param> /// <param name="P1"></param> /// <param name="P2"></param> /// <param name="UserInfo"></param> public void GETHYGLLIST(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo) { string userName = UserInfo.User.UserName; string strWhere = " 1=1 and hy.ComId=" + UserInfo.User.ComId; string leibie = context.Request["lb"] ?? ""; if (leibie != "") { strWhere += string.Format(" And hy.RoomID='{0}' ", leibie); } string strContent = context.Request["Content"] ?? ""; strContent = strContent.TrimEnd(); if (strContent != "") { strWhere += string.Format(" And ( hy.Title like '%{0}%' )", strContent); } int DataID = -1; int.TryParse(context.Request["ID"] ?? "-1", out DataID);//记录Id if (DataID != -1) { string strIsHasDataQX = new JH_Auth_QY_ModelB().ISHASDATAREADQX("HYGL", DataID, UserInfo); if (strIsHasDataQX == "Y") { strWhere += string.Format(" And hy.ID = '{0}'", DataID); } } if (P1 != "") { int page = 0; int pagecount = 8; int.TryParse(context.Request["p"] ?? "1", out page); int.TryParse(context.Request["pagecount"] ?? "8", out pagecount);//页数 page = page == 0 ? 1 : page; int total = 0; DataTable dt = new DataTable(); #region no use // switch (P1) // { // case "0": // string colNme1 = @"ycgl.*,case when ycgl.StartTime>getdate() then '即将开始' when ycgl.StartTime<=getdate() and ycgl.EndTime>=getdate() then '正在进行' // when ycgl.EndTime<getdate() then '已结束' end as HLStatus,car.Name ,case WHEN wfpi.isComplete is null and wfpi.IsCanceled is null THEN '正在审批' // when wfpi.isComplete='Y' then '已审批' WHEN wfpi.IsCanceled='Y' then '已退回' END as StateName"; // //strWhere += " And cc.CRUser ='******'"; // dt = new SZHL_CCXJB().GetDataPager("SZHL_HYGL ycgl inner join SZHL_HYGL_ROOM car on ycgl.RoomID=car.ID inner join Yan_WF_PI wfpi on ycgl.intProcessStanceid=wfpi.ID", colNme1, 8, page, " ycgl.CRDate desc", strWhere, ref total); // break; // case "1": // string colNme = @"ycgl.*,case when ycgl.StartTime>getdate() then '即将开始' when ycgl.StartTime<=getdate() and ycgl.EndTime>=getdate() then '正在进行' // when ycgl.EndTime<getdate() then '已结束' end as HLStatus,car.Name ,case WHEN wfpi.isComplete is null and wfpi.IsCanceled is null THEN '正在审批' // when wfpi.isComplete='Y' then '已审批' WHEN wfpi.IsCanceled='Y' then '已退回' END as StateName"; // strWhere += " And ycgl.CRUser ='******'"; // dt = new SZHL_CCXJB().GetDataPager("SZHL_HYGL ycgl inner join SZHL_HYGL_ROOM car on ycgl.RoomID=car.ID inner join Yan_WF_PI wfpi on ycgl.intProcessStanceid=wfpi.ID", colNme, 8, page, " ycgl.CRDate desc", strWhere, ref total); // break; // case "2": // string colNme2 = @"ycgl.*,case when ycgl.StartTime>getdate() then '即将开始' when ycgl.StartTime<=getdate() and ycgl.EndTime>=getdate() then '正在进行' // when ycgl.EndTime<getdate() then '已结束' end as HLStatus,car.Name ,'已审批' StateName"; // strWhere += string.Format(" And (','+ycgl.CYUser+',' like '%,{0},%' or ','+ycgl.JLUser+',' like '%,{0},%' or ','+ycgl.ZCUser+',' like '%,{0},%' or ','+ycgl.SXUser+',' like '%,{0},%' ) and wfpi.isComplete='Y'", userName); // dt = new SZHL_CCXJB().GetDataPager("SZHL_HYGL ycgl inner join SZHL_HYGL_ROOM car on ycgl.RoomID=car.ID inner join Yan_WF_PI wfpi on ycgl.intProcessStanceid=wfpi.ID", colNme2, 8, page, " ycgl.StartTime desc", strWhere, ref total); // break; // case "3": // List<string> intProD = new Yan_WF_PIB().GetDSH(UserInfo.User).Select(d => d.PIID.ToString()).ToList(); // if (intProD.Count > 0) // { // string tableNameD = string.Format(@" SZHL_HYGL ycgl inner join SZHL_HYGL_ROOM car on ycgl.RoomID=car.ID"); // string tableColumnD = "ycgl.* ,car.Name , '正在审批' StateName,case when ycgl.StartTime>getdate() then '即将开始' when ycgl.StartTime<=getdate() and ycgl.EndTime>=getdate() then '正在进行' when ycgl.EndTime<getdate() then '已结束' end as HLStatus"; // strWhere += " And ycgl.intProcessStanceid in (" + (intProD.ListTOString(',') == "" ? "0" : intProD.ListTOString(',')) + ")"; // dt = new SZHL_CCXJB().GetDataPager(tableNameD, tableColumnD, 8, page, " ycgl.CRDate desc", strWhere, ref total); // } // break; // case "4": // List<Yan_WF_TI> ListData = new Yan_WF_TIB().GetEntities("TaskUserID ='" + UserInfo.User.UserName + "' AND EndTime IS NOT NULL AND TaskUserView!='发起表单'").ToList(); // List<string> intPro = ListData.Select(d => d.PIID.ToString()).ToList(); // string tableName = string.Format(@" SZHL_HYGL ycgl inner join SZHL_HYGL_ROOM car on ycgl.RoomID=car.ID inner join Yan_WF_PI wfpi on ycgl.intProcessStanceid=wfpi.ID"); // string tableColumn = "ycgl.* ,car.Name , case when wfpi.IsCanceled is null then '已审批' WHEN wfpi.IsCanceled='Y' then '已退回' END StateName,case when ycgl.StartTime>getdate() then '即将开始' when ycgl.StartTime<=getdate() and ycgl.EndTime>=getdate() then '正在进行' when ycgl.EndTime<getdate() then '已结束' end as HLStatus "; // strWhere += " And ycgl.intProcessStanceid in (" + (intPro.ListTOString(',') == "" ? "0" : intPro.ListTOString(',')) + ")"; // dt = new SZHL_CCXJB().GetDataPager(tableName, tableColumn, 8, page, " ycgl.CRDate desc", strWhere, ref total); // break; // } #endregion switch (P1) { case "0": //手机单条数据 { //设置usercenter已读 new JH_Auth_User_CenterB().ReadMsg(UserInfo, DataID, "HYGL"); } break; case "1": //创建的 { strWhere += " And hy.CRUser ='******'"; } break; case "2": //参与的 { strWhere += string.Format(" And (','+hy.CYUser+',' like '%,{0},%' or ','+hy.JLUser+',' like '%,{0},%' or ','+hy.ZCUser+',' like '%,{0},%' or ','+hy.SXUser+',' like '%,{0},%' ) and ( dbo.fn_PDStatus(hy.intProcessStanceid)='已审批' or dbo.fn_PDStatus(hy.intProcessStanceid)='-1')", userName); } break; case "3": //待审核 { var intProD = new Yan_WF_PIB().GetDSH(UserInfo.User).Select(d => d.PIID.ToString()).ToList(); if (intProD.Count > 0) { strWhere += " And hy.intProcessStanceid in (" + (intProD.ListTOString(',') == "" ? "0" : intProD.ListTOString(',')) + ")"; } else { strWhere += " And 1=0"; } } break; case "4": //已审核 { var intProD = new Yan_WF_PIB().GetYSH(UserInfo.User).Select(d => d.PIID.ToString()).ToList(); if (intProD.Count > 0) { strWhere += " And hy.intProcessStanceid in (" + (intProD.ListTOString(',') == "" ? "0" : intProD.ListTOString(',')) + ")"; } else { strWhere += " And 1=0"; } } break; } dt = new SZHL_CCXJB().GetDataPager("SZHL_HYGL hy left join SZHL_HYGL_ROOM hys on hy.RoomID=hys.ID", "hy.*,hys.Name ,dbo.fn_PDStatus(hy.intProcessStanceid) AS StateName,case when hy.StartTime>getdate() then '即将开始' when hy.StartTime<=getdate() and hy.EndTime>=getdate() then '正在进行' when hy.EndTime<getdate() then '已结束' end as HLStatus ", pagecount, page, " hy.CRDate desc", strWhere, ref total); if (dt.Rows.Count > 0) { dt.Columns.Add("RYStatus", Type.GetType("System.String")); dt.Columns.Add("FileList", Type.GetType("System.Object")); dt.Columns.Add("PLList", Type.GetType("System.Object")); foreach (DataRow dr in dt.Rows) { if (dr["Files"] != null && dr["Files"].ToString() != "") { dr["FileList"] = new FT_FileB().GetEntities(" ID in (" + dr["Files"].ToString() + ")"); } string strid = dr["ID"].ToString(); var hysat = new JH_Auth_TLB().GetEntities(p => p.ComId == UserInfo.User.ComId && p.MSGType == "HYGL" && p.MSGTLYID == strid && p.CRUser == UserInfo.User.UserName && p.MsgISShow != null).OrderByDescending(p => p.CRDate).ToList(); if (hysat.Count() > 0) { string strs = string.Empty; foreach (var l in hysat) { if (string.IsNullOrEmpty(strs)) { strs = l.MsgISShow; } else { strs = strs + "," + l.MsgISShow; } } dr["RYStatus"] = strs; } dr["PLList"] = new JH_Auth_TLB().GetEntities(p => p.ComId == UserInfo.User.ComId && p.MSGType == "HYGL" && p.MSGTLYID == strid); } } msg.Result = dt; msg.Result1 = total; } }
/// <summary> /// 外勤签到列表 /// </summary> /// <param name="context"></param> /// <param name="msg"></param> /// <param name="P1"></param> /// <param name="P2"></param> /// <param name="UserInfo"></param> public void GETWQQDLIST(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo) { string userName = UserInfo.User.UserName; string strWhere = " 1=1 and ComId=" + UserInfo.User.ComId; string strContent = context.Request["Content"] ?? ""; strContent = strContent.TrimEnd(); if (strContent != "") { strWhere += string.Format(" And ( QDContent like '%{0}%' or CRUserName like '%{0}%' or BranchName like '%{0}%')", strContent); } //根据创建时间查询 string time = context.Request["time"] ?? ""; if (time != "") { if (time == "1") //近一周 { strWhere += string.Format(" And datediff(day,CRDate,getdate())<7"); } else if (time == "2") { //近一月 strWhere += string.Format(" And datediff(day,CRDate,getdate())<30"); } else if (time == "3") //自定义时间 { string strTime = context.Request["starTime"] ?? ""; string endTime = context.Request["endTime"] ?? ""; if (strTime != "") { strWhere += string.Format(" And convert(varchar(10),CRDate,120) >='{0}'", strTime); } if (endTime != "") { strWhere += string.Format(" And convert(varchar(10),CRDate,120) <='{0}'", endTime); } } } int DataID = -1; int.TryParse(context.Request["ID"] ?? "-1", out DataID);//记录Id if (DataID != -1) { string strIsHasDataQX = new JH_Auth_QY_ModelB().ISHASDATAREADQX("WQQD", DataID, UserInfo); if (strIsHasDataQX == "Y") { strWhere += string.Format(" And ID = '{0}'", DataID); } } if (P1 != "") { int page = 0; int pagecount = 8; int.TryParse(context.Request["p"] ?? "1", out page); int.TryParse(context.Request["pagecount"] ?? "8", out pagecount);//页数 page = page == 0 ? 1 : page; int total = 0; DataTable dt = new DataTable(); switch (P1) { case "0": //手机单条数据 { //设置usercenter已读 new JH_Auth_User_CenterB().ReadMsg(UserInfo, DataID, "WQQD"); } break; case "1": //创建的 { strWhere += " And CRUser ='******'"; } break; case "2": //汇报我的 { strWhere += string.Format(" And ','+HBUser+',' like '%,{0},%'", userName); } break; case "3": //下属签到 { //获取当前登录人负责的下属人员 string Users = new JH_Auth_UserB().GetUserBranchUsers(UserInfo.User.ComId.Value, UserInfo.User.UserName).Select(d => d.UserName).ToList().ListTOString(','); strWhere += string.Format("and CRUser in ('{0}')", Users.ToFormatLike()); } break; } dt = new SZHL_WQQDB().GetDataPager("SZHL_WQQD", "*", pagecount, page, " CRDate desc", strWhere, ref total); if (dt.Rows.Count > 0) { dt.Columns.Add("FileList", Type.GetType("System.Object")); foreach (DataRow dr in dt.Rows) { if (dr["Files"] != null && dr["Files"].ToString() != "") { dr["FileList"] = new FT_FileB().GetEntities(" ID in (" + dr["Files"].ToString() + ")"); } } } msg.Result = dt; msg.Result1 = total; } }
/// <summary> /// 获取日志列表 /// </summary> /// <param name="context"></param> /// <param name="msg"></param> /// <param name="P1">日志类型</param> /// <param name="P2">查询条件</param> /// <param name="strUserName"></param> public void GETGZBGLIST(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo) { int DataID = -1; int.TryParse(context.Request["ID"] ?? "-1", out DataID);//微信获取单个数据的ID int page = 0; int pagecount = 8; int.TryParse(context.Request["p"] ?? "1", out page); //页码 int.TryParse(context.Request["pagecount"] ?? "8", out pagecount); //页数 page = page == 0 ? 1 : page; int recordCount = 0; string strWhere = string.Format(" bg.ComId={0} ", UserInfo.User.ComId); string type = context.Request["type"] ?? "1"; if (type == "2")//下属报告 { //获取当前登录人负责的下属人员 string Users = new JH_Auth_UserB().GetUserBranchUsers(UserInfo.User.ComId.Value, UserInfo.User.UserName).Select(d => d.UserName).ToList().ListTOString(','); strWhere += string.Format("and bg.CRUser in ('{0}')", Users.ToFormatLike()); } else if (type == "1") //当前登录人报告 { strWhere += string.Format("and bg.CRUser='******'", UserInfo.User.UserName); } else if (type == "3")//获取抄送人报告 { strWhere += string.Format("and ','+bg.ChaoSongUser+',' like '%{0}%'", UserInfo.User.UserName); } if (P1 != "")//分类 { strWhere += string.Format("And bg.LeiBie={0}", P1); } if (P2 != "")//内容查询 { strWhere += string.Format(" And (bg.RBContent like '%{0}%' OR bg.CRUserName like '%{0}%' OR bg.BranchName like '%{0}%' )", P2); } //根据创建时间查询 string time = context.Request["time"] ?? ""; if (time != "") { if (time == "1") //近一周 { strWhere += string.Format(" And datediff(day,bg.RBDate,getdate())<7"); } else if (time == "2") { //近一月 strWhere += string.Format(" And datediff(day,bg.RBDate,getdate())<30"); } else if (time == "3") //自定义时间 { string strTime = context.Request["starTime"] ?? ""; string endTime = context.Request["endTime"] ?? ""; if (strTime != "") { strWhere += string.Format(" And convert(varchar(10),bg.RBDate,120) >='{0}'", strTime); } if (endTime != "") { strWhere += string.Format(" And convert(varchar(10),bg.RBDate,120) <='{0}'", endTime); } } } if (DataID != -1) { string strIsHasDataQX = new JH_Auth_QY_ModelB().ISHASDATAREADQX("GZBG", DataID, UserInfo); if (strIsHasDataQX == "Y") { strWhere += string.Format(" And bg.ID = '{0}'", DataID); } //更新消息为已读状态 new JH_Auth_User_CenterB().ReadMsg(UserInfo, DataID, "GZBG"); } DataTable dt = new SZHL_GZBGB().GetDataPager(" SZHL_GZBG bg inner join JH_Auth_ZiDian zd on LeiBie= zd.ID and Class=6 ", "bg.BranchName,bg.CRUserName,CONVERT(varchar(100), bg.RBDate, 23) as RBDate,zd.TypeName,bg.CRUser,bg.RBContent,bg.RBJSR,bg.RBWCQK,bg.LeiBie,bg.CRDate,bg.ID,bg.Files ", pagecount, page, "bg.CRDate desc", strWhere, ref recordCount); string Ids = ""; string fileIDs = ""; foreach (DataRow row in dt.Rows) { Ids += row["ID"].ToString() + ","; if (!string.IsNullOrEmpty(row["Files"].ToString())) { fileIDs += row["Files"].ToString() + ","; } } Ids = Ids.TrimEnd(','); fileIDs = fileIDs.TrimEnd(','); dt.Columns.Add("PLList", Type.GetType("System.Object")); dt.Columns.Add("FileList", Type.GetType("System.Object")); if (Ids != "") { List <FT_File> FileList = new List <FT_File>(); DataTable dtPL = new JH_Auth_TLB().GetDTByCommand(string.Format("SELECT tl.* FROM JH_Auth_TL tl WHERE tl.MSGType='GZBG' AND tl.MSGTLYID in ({0})", Ids)); dtPL.Columns.Add("FileList", Type.GetType("System.Object")); foreach (DataRow dr in dtPL.Rows) { if (dr["MSGisHasFiles"] != null && dr["MSGisHasFiles"].ToString() != "") { int[] fileIds = dr["MSGisHasFiles"].ToString().SplitTOInt(','); dr["FileList"] = new FT_FileB().GetEntities(d => fileIds.Contains(d.ID)); } } if (!string.IsNullOrEmpty(fileIDs)) { int[] fileId = fileIDs.SplitTOInt(','); FileList = new FT_FileB().GetEntities(d => fileId.Contains(d.ID)).ToList(); } foreach (DataRow row in dt.Rows) { row["PLList"] = dtPL.FilterTable("MSGTLYID='" + row["ID"] + "'"); if (FileList.Count > 0) { string[] fileIds = row["Files"].ToString().Split(','); row["FileList"] = FileList.Where(d => fileIds.Contains(d.ID.ToString())); } } } msg.Result = dt; msg.Result1 = recordCount; }
/// <summary> /// 活动列表 /// </summary> /// <param name="context"></param> /// <param name="msg"></param> /// <param name="P1"></param> /// <param name="P2"></param> /// <param name="UserInfo"></param> public void GETQYHDLIST_PAGE(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo) { int DataID = -1; int.TryParse(context.Request["ID"] ?? "-1", out DataID);//获取单个数据的ID int listType = 0; int.TryParse(context.Request["listType"] ?? "0", out listType); string strWhere = "ComId=" + UserInfo.User.ComId + " AND (CYR='' OR CYR like '%" + UserInfo.User.UserName + "%' OR CRUser='******')"; if (listType == 1) { strWhere = "ComId=" + UserInfo.User.ComId + " And CRUser='******'"; } if (P1 == "0") { strWhere += string.Format(" And Type = 0 "); } else if (P1 == "1") { strWhere += string.Format(" And Type=1 "); } if (P2 != "") { strWhere += string.Format(" And Title like '%{0}%' ", P2); } if (DataID != -1) { string strIsHasDataQX = new JH_Auth_QY_ModelB().ISHASDATAREADQX("QYHD", DataID, UserInfo); if (strIsHasDataQX == "Y") { strWhere += string.Format(" And ID = '{0}'", DataID); } //更新消息为已读状态 new JH_Auth_User_CenterB().ReadMsg(UserInfo, DataID, "QYHD"); } int page = 0; int pagecount = 8; int.TryParse(context.Request["p"] ?? "1", out page); int.TryParse(context.Request["pagecount"] ?? "8", out pagecount);//页数 int recordCount = 0; //HDStatus 0已结束 2 正在进行 1 未开始 string strSql = @"SELECT qyhd.ID,qyhd.Type,qyhd.FQF,qyhd.Title,qyhd.JZDate,qyhd.HDDate,qyhd.Status,qyhd.CRDate,qyhd.Files,qyhd.CRUser,qyhd.ComId,qyhd.CYR,qyhd.StartTime,qyhd.EndTime,qyhd.HD_Content,qyhd.HD_Adress,qyhd.TP_IsNM,TP_Type,TP_IsPublic,qyhd.IsPublic,COUNT(result.ID) BMCount ,SUM(case when result.CRUser='******' then 1 else 0 end ) UserBMCount, case when StartTime>GETDATE() then 1 when EndTime>GETDATE() and StartTime<GETDATE() then 2 when EndTime<GETDATE() then 0 End HDStatus from SZHL_QYHDN qyhd left join SZHL_QYHD_Result result on qyhd.Id=result.HDID group by qyhd.ID,qyhd.Type,qyhd.FQF,qyhd.Title,qyhd.JZDate,qyhd.HDDate,qyhd.Status,qyhd.Files,qyhd.CRDate,qyhd.CRUser,qyhd.ComId,qyhd.CYR,qyhd.StartTime,qyhd.EndTime,qyhd.HD_Content,qyhd.HD_Adress,qyhd.TP_IsNM,TP_Type,TP_IsPublic,qyhd.IsPublic"; DataTable dt = new SZHL_QYHDNB().GetDataPager("(" + strSql + ") newTab ", "*", pagecount, page, "HDStatus desc,CRDate desc", strWhere, ref recordCount); string Ids = ""; string fileIDs = ""; string BMIDs = ""; //报名列表 string TPItemIds = ""; //投票选项 #region 附件,评论列表 foreach (DataRow row in dt.Rows) { Ids += row["ID"].ToString() + ","; if (!string.IsNullOrEmpty(row["Files"].ToString())) { fileIDs += row["Files"].ToString() + ","; } if (row["Type"].ToString() == "0") { BMIDs += row["ID"].ToString() + ","; } else { TPItemIds += row["ID"].ToString() + ","; } } Ids = Ids.TrimEnd(','); fileIDs = fileIDs.TrimEnd(','); BMIDs = BMIDs.TrimEnd(','); TPItemIds = TPItemIds.TrimEnd(','); if (Ids != "") { List <FT_File> FileList = new List <FT_File>(); DataTable dtPL = new JH_Auth_TLB().GetDTByCommand(string.Format("SELECT * FROM JH_Auth_TL tl WHERE tl.MSGType='QYHD' AND tl.MSGTLYID in ({0})", Ids)); if (!string.IsNullOrEmpty(fileIDs)) { int[] fileId = fileIDs.SplitTOInt(','); FileList = new FT_FileB().GetEntities(d => fileId.Contains(d.ID)).ToList(); } //报名列表 List <SZHL_QYHD_Result> resultList = new List <SZHL_QYHD_Result>(); if (BMIDs != "") { int[] HDIds = BMIDs.SplitTOInt(','); resultList = new SZHL_QYHD_ResultB().GetEntities(d => HDIds.Contains(d.HDID.Value) && d.ComId == UserInfo.User.ComId).ToList(); } //投票选项 DataTable dtOption = new DataTable(); if (TPItemIds != "") { string strOpSql = string.Format(@"SELECT op.ID,op.OptionText,op.HDId,COUNT(result.ID) TPCount from SZHL_QYHD_Option op left join SZHL_QYHD_Result result on op.ID=result.OptionID and op.HDId in ({0}) GROUP by op.ID,op.OptionText,op.HDId", TPItemIds); dtOption = new SZHL_QYHD_OptionB().GetDTByCommand(strOpSql); } dt.Columns.Add("PLList", Type.GetType("System.Object")); dt.Columns.Add("FileList", Type.GetType("System.Object")); dt.Columns.Add("BMList", Type.GetType("System.Object")); dt.Columns.Add("OptionList", Type.GetType("System.Object")); foreach (DataRow row in dt.Rows) { int HDId = int.Parse(row["ID"].ToString()); row["BMList"] = resultList.Where(d => d.HDID == HDId); //row["PLList"] = dtPL.FilterTable("MSGTLYID='" + row["ID"] + "'"); DataTable dtPLs = dtPL.FilterTable("MSGTLYID='" + row["ID"] + "'"); dtPLs.Columns.Add("FileList", Type.GetType("System.Object")); foreach (DataRow dr in dtPLs.Rows) { if (dr["MSGisHasFiles"] != null && dr["MSGisHasFiles"].ToString() != "") { int[] fileIds = dr["MSGisHasFiles"].ToString().SplitTOInt(','); dr["FileList"] = new FT_FileB().GetEntities(d => fileIds.Contains(d.ID)); } } row["PLList"] = dtPLs; if (dtOption.Rows.Count > 0) { row["OptionList"] = dtOption.Where("HDId=" + row["ID"]); } if (FileList.Count > 0) { string[] fileIds = row["Files"].ToString().Split(','); row["FileList"] = FileList.Where(d => fileIds.Contains(d.ID.ToString())); } } } #endregion msg.Result = dt; msg.Result1 = recordCount; }
public void GETXMGLDATA(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo) { string strWhere = " xm.ComId=" + UserInfo.User.ComId; if (P1 != "") { strWhere += string.Format(" And xm.Status='{0}'", P1); } if (P2 != "") { switch (P2) { case "0": strWhere += string.Format(" And (xm.CRUser='******' or ','+XMFZR+',' like '%,{0},%' or ','+XMCYR+',' like '%,{0},%' )", UserInfo.User.UserName); break; case "1": strWhere += string.Format(" And xm.CRUser='******'", UserInfo.User.UserName); break; case "2": strWhere += string.Format(" And (','+XMFZR+',' like '%," + UserInfo.User.UserName + ",%' )"); break; case "3": strWhere += string.Format(" And (','+XMCYR+',' like '%," + UserInfo.User.UserName + ",%' )"); break; } } string lb = context.Request["lb"] ?? ""; if (lb != "") { strWhere += string.Format(" And YXJ='{0}'", lb); } string strContent = context.Request["Content"] ?? ""; strContent = strContent.TrimEnd(); if (strContent != "") { strWhere += string.Format(" And ( XMMC like '%{0}%' )", strContent); } //根据创建时间查询 string time = context.Request["time"] ?? ""; if (time != "") { if (time == "1") //近一周 { strWhere += string.Format(" And datediff(day,xm.CRDate,getdate())<7"); } else if (time == "2") { //近一月 strWhere += string.Format(" And datediff(day,xm.CRDate,getdate())<30"); } else if (time == "4") { //今年 strWhere += string.Format(" And datediff(year,xm.CRDate,getdate())=0"); } else if (time == "5") { //上一年 strWhere += string.Format(" And datediff(year,xm.CRDate,getdate())=1"); } else if (time == "3") //自定义时间 { string strTime = context.Request["starTime"] ?? ""; string endTime = context.Request["endTime"] ?? ""; if (strTime != "") { strWhere += string.Format(" And convert(varchar(10),xm.CRDate,120) >='{0}'", strTime); } if (endTime != "") { strWhere += string.Format(" And convert(varchar(10),xm.CRDate,120) <='{0}'", endTime); } } } int DataID = -1; int.TryParse(context.Request["ID"] ?? "-1", out DataID);//记录Id if (DataID != -1) { string strIsHasDataQX = new JH_Auth_QY_ModelB().ISHASDATAREADQX("CRM", DataID, UserInfo); if (strIsHasDataQX == "Y") { strWhere += string.Format(" And xm.ID = '{0}'", DataID); } } int page = 0; int pagecount = 8; int.TryParse(context.Request["p"] ?? "1", out page); int.TryParse(context.Request["pagecount"] ?? "8", out pagecount);//页数 page = page == 0 ? 1 : page; int total = 0; DataTable dtList = new SZHL_XMGLB().GetDataPager("SZHL_XMGL xm left join JH_Auth_ZiDian zd on convert(varchar,YXJ)=convert(varchar,zd.ID)", "xm.* ,zd.TypeName", pagecount, page, " xm.CRDate desc,xm.Status ", strWhere, ref total); dtList.Columns.Add("PLList", Type.GetType("System.Object")); dtList.Columns.Add("FileList", Type.GetType("System.Object")); for (int i = 0; i < dtList.Rows.Count; i++) { dtList.Rows[i]["PLList"] = new JH_Auth_TLB().GetTL("XMGL", dtList.Rows[i]["ID"].ToString()); if (dtList.Rows[i]["Files"].ToString() != "") { dtList.Rows[i]["FileList"] = new FT_FileB().GetEntities(" ID in (" + dtList.Rows[i]["Files"].ToString() + ")"); } } msg.Result = dtList; msg.Result1 = total; }
public void GETRWGLLIST(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo) { string userName = UserInfo.User.UserName; string strWhere = " bg.ComId =" + UserInfo.User.ComId.Value; if (P1 != "") { if (P1 == "2") { // strWhere += string.Format(" And datediff(day,bg.RWJZDate,getdate())>0"); strWhere += string.Format(" And bg.RWJZDate < CONVERT(char(10), GETDATE(), 120) And RWStatus='0'"); } else if (P1 == "1") { strWhere += string.Format(" And RWStatus={0}", P1); } else if (P1 == "0") { //strWhere += string.Format(" And bg.RWJZDate >= CONVERT(char(10), GETDATE(), 120) And RWStatus={0}", P1); strWhere += string.Format(" And RWStatus={0}", P1); } } if (P2 != "") { switch (P2) { case "0": strWhere += string.Format(" And (bg.CRUser='******' or ','+RWFZR+',' like '%,{0},%' or ','+RWCYR+',' like '%,{0},%' or ','+KHFXRS+',' like '%,{0},%' )", userName); break; case "1": strWhere += string.Format(" And (','+RWFZR+',' like '%," + userName + ",%' )"); break; case "2": strWhere += string.Format(" And bg.CRUser='******'", UserInfo.User.UserName); break; case "3": strWhere += string.Format(" And (','+RWCYR+',' like '%," + userName + ",%' )"); break; case "4": strWhere += string.Format(" And (','+KHFXRS+',' like '%," + userName + ",%' )"); break; } } string leibie = context.Request["lb"] ?? ""; if (leibie != "") { strWhere += string.Format(" And LeiBie='{0}' ", leibie); } string strContent = context.Request["Content"] ?? ""; strContent = strContent.TrimEnd(); if (strContent != "") { strWhere += string.Format(" And ( RWTitle like '%{0}%' )", strContent); } //根据时间查询数据 string time = context.Request["time"] ?? ""; if (time != "") { if (time == "1") //近一周 { strWhere += string.Format(" And datediff(day,bg.CRDate,getdate())<7"); } else if (time == "2") { //近一月 strWhere += string.Format(" And datediff(day,bg.CRDate,getdate())<30"); } else if (time == "3") //自定义时间 { string strTime = context.Request["starTime"] ?? ""; string endTime = context.Request["endTime"] ?? ""; if (strTime != "") { strWhere += string.Format(" And convert(varchar(10),bg.CRDate,120) >='{0}'", strTime); } if (endTime != "") { strWhere += string.Format(" And convert(varchar(10),bg.CRDate,120) <='{0}'", endTime); } } } int DataID = -1; int.TryParse(context.Request["ID"] ?? "-1", out DataID);//记录Id if (DataID != -1) { string strIsHasDataQX = new JH_Auth_QY_ModelB().ISHASDATAREADQX("RWGL", DataID, UserInfo); if (strIsHasDataQX == "Y") { strWhere += string.Format(" And bg.ID = '{0}'", DataID); } //更新消息为已读状态 new JH_Auth_User_CenterB().ReadMsg(UserInfo, DataID, "RWGL"); } DataTable dtList = new DataTable(); int page = 0; int pagecount = 8; int.TryParse(context.Request["p"] ?? "1", out page); //页码 int.TryParse(context.Request["pagecount"] ?? "8", out pagecount); //页数 page = page == 0 ? 1 : page; int recordCount = 0; dtList = new SZHL_GZBGB().GetDataPager(" SZHL_RWGL bg LEFT JOIN JH_Auth_ZiDian zd on LeiBie= zd.ID and Class=7 LEFT JOIN JH_Auth_User jau ON bg.RWFZR = jau.UserName AND jau.ComId=bg.ComId ", @" [CRUserName], jau.UserRealName, zd.TypeName, [RWJZDate], [RWTitle], [RWContent], [LeiBie], [RWFZR], [RWCYR], [RWStatus], [BiaoQian], [BeiZhu], [IsSend], [IsComPlete], [IsCancel], [CancelDate], bg.[CRDate], bg.[CRUser], bg.[Files], bg.[Remark], bg.[ID], [intProcessStanceid], [KHFXRS], [TopID], bg.[ComId], [TaskJD], [IsTX],CASE WHEN RWJZDate<CONVERT(char(20), GETDATE(), 23) THEN 1 else 0 END AS jzstatus" , pagecount, page, "bg.RWJZDate desc", strWhere, ref recordCount); string Ids = ""; string fileIDs = ""; foreach (DataRow row in dtList.Rows) { Ids += row["ID"].ToString() + ","; if (!string.IsNullOrEmpty(row["Files"].ToString())) { fileIDs += row["Files"].ToString() + ","; } } Ids = Ids.TrimEnd(','); fileIDs = fileIDs.TrimEnd(','); if (Ids != "") { List <FT_File> FileList = new List <FT_File>(); DataTable dtPL = new JH_Auth_TLB().GetDTByCommand(string.Format("SELECT tl.* FROM JH_Auth_TL tl WHERE tl.MSGType='RWGL' AND tl.MSGTLYID in ({0})", Ids)); dtPL.Columns.Add("FileList", Type.GetType("System.Object")); foreach (DataRow dr in dtPL.Rows) { if (dr["MSGisHasFiles"] != null && dr["MSGisHasFiles"].ToString() != "") { int[] fileIds = dr["MSGisHasFiles"].ToString().SplitTOInt(','); dr["FileList"] = new FT_FileB().GetEntities(d => fileIds.Contains(d.ID)); } } if (!string.IsNullOrEmpty(fileIDs)) { int[] fileId = fileIDs.SplitTOInt(','); FileList = new FT_FileB().GetEntities(d => fileId.Contains(d.ID)).ToList(); } dtList.Columns.Add("PLList", Type.GetType("System.Object")); dtList.Columns.Add("FileList", Type.GetType("System.Object")); foreach (DataRow row in dtList.Rows) { row["PLList"] = dtPL.FilterTable("MSGTLYID='" + row["ID"] + "'"); if (FileList.Count > 0) { string[] fileIds = row["Files"].ToString().Split(','); row["FileList"] = FileList.Where(d => fileIds.Contains(d.ID.ToString())); } } } msg.Result = dtList; msg.Result1 = recordCount; }