/// <summary> /// 生成查看人员 /// </summary> /// <param name="SurveyId"></param> /// <returns></returns> public bool CreateReadUser(string SurveyId) { SurveyReaderObj SrEnt = SurveyReaderObj.FindFirstByProperties(SurveyReaderObj.Prop_SurveyId, SurveyId); //组织下的人员 string RdSQL = this.ReadSQL; if (string.IsNullOrEmpty(SrEnt.OrgIds) || string.IsNullOrEmpty(SrEnt.OrgNames)) { RdSQL = RdSQL.Replace("##Query##", " and 1<>1 "); //组织为空情况 RdSQL = string.Format(RdSQL, SurveyId); } else { RdSQL = RdSQL.Replace("##Query##", " "); RdSQL = string.Format(RdSQL, SurveyId); } DataTable FilterUsrDt = DataHelper.QueryDataTable(RdSQL); // 添加的人员 string SQL = @"with SurObjUsr As ( select CA.* from FL_Culture..SurveyReaderObj As A cross apply( select Filed As UsrID from FL_Culture..GetTblByJson(A.AllowUser,'Id') ) As CA where SurveyId='{0}' ), UserInfo As ( select T.UserID,T.Name, T.WorkNo from SurObjUsr As A left join FL_PortalHR..Sysuser As T on A.UsrID=T.UserID ) select distinct * from UserInfo "; SQL = "select '' where 1<>1 "; //Change BY WGM 9-30 SQL = SQL.Replace("FL_PortalHR", Global.AimPortalDB); SQL = string.Format(SQL, SurveyId); DataTable AddUsrDt = DataHelper.QueryDataTable(SQL); //排除的人员 string ReMoveSQL = @" select CA.* from FL_Culture..SurveyReaderObj As A cross apply( select Filed As UserID from FL_Culture..GetTblByJson(A.NoAllowUser,'Id') ) As CA where SurveyId='{0}'"; ReMoveSQL = string.Format(ReMoveSQL, SurveyId); ReMoveSQL = "select '' where 1<>1 "; //Change BY WGM 9-30 DataTable RemoveDt = DataHelper.QueryDataTable(ReMoveSQL); //排除人员 if (RemoveDt.Rows.Count > 0) { for (int i = 0; i < FilterUsrDt.Rows.Count; i++) { for (int j = 0; j < RemoveDt.Rows.Count; j++) { if (FilterUsrDt.Rows[i]["UserID"].ToString() == RemoveDt.Rows[j]["UserID"].ToString()) { FilterUsrDt.Rows.Remove(FilterUsrDt.Rows[i]); } } } } // 合并 DataTable FinalDt = new DataTable(); DataColumn Dc_UserId = new DataColumn("UserId"); FinalDt.Columns.Add(Dc_UserId); DataColumn Dc_UserName = new DataColumn("UserName"); FinalDt.Columns.Add(Dc_UserName); DataColumn Dc_DeptId = new DataColumn("DeptId"); FinalDt.Columns.Add(Dc_DeptId); DataColumn Dc_DeptName = new DataColumn("DeptName"); FinalDt.Columns.Add(Dc_DeptName); DataColumn Dc_WorkNo = new DataColumn("WorkNo"); FinalDt.Columns.Add(Dc_WorkNo); for (int i = 0; i < FilterUsrDt.Rows.Count; i++) { DataRow dr = FinalDt.NewRow(); //dr["DeptId"] = FilterUsrDt.Rows[i]["GroupId"]; //dr["DeptName"] = FilterUsrDt.Rows[i]["GroupName"]; string FullDept = WFHelper.getZzjg(FilterUsrDt.Rows[i]["UserID"].ToString(), true); dr["DeptName"] = FullDept; dr["WorkNo"] = FilterUsrDt.Rows[i]["WorkNo"]; dr["UserId"] = FilterUsrDt.Rows[i]["UserID"]; //* dr["UserName"] = FilterUsrDt.Rows[i]["UserName"]; //* FinalDt.Rows.Add(dr); } //Add for (int i = 0; i < AddUsrDt.Rows.Count; i++) { DataRow dr = FinalDt.NewRow(); //dr["DeptId"] = AddUsrDt.Rows[i]["GroupID"]; //dr["DeptName"] = AddUsrDt.Rows[i]["GroupName"]; string FullDept = WFHelper.getZzjg(AddUsrDt.Rows[i]["UserID"].ToString(), true); dr["DeptName"] = FullDept; dr["WorkNo"] = AddUsrDt.Rows[i]["WorkNo"]; dr["UserId"] = AddUsrDt.Rows[i]["UserID"]; //* dr["UserName"] = AddUsrDt.Rows[i]["Name"]; //* FinalDt.Rows.Add(dr); } SurveyQuestion Ent = SurveyQuestion.Find(SurveyId); if (Ent != null) { //添加问卷创建者 DataRow CreateRow = FinalDt.NewRow(); CreateRow["UserId"] = Ent.CreateId; CreateRow["UserName"] = Ent.CreateName; FinalDt.Rows.Add(CreateRow); } //--------------------------------------------------------------- //生成调查用户 for (int i = 0; i < FinalDt.Rows.Count; i++) { SurveyCanReaderUsr UserEnt = new SurveyCanReaderUsr(); UserEnt.CreateWay = "1"; //1 表示生成的用户而非创建 UserEnt.SurveyId = SurveyId; UserEnt.DeptName = FinalDt.Rows[i]["DeptName"].ToString(); //UserEnt.DeptId = FinalDt.Rows[i]["DeptId"].ToString(); UserEnt.UserId = FinalDt.Rows[i]["UserId"].ToString(); UserEnt.UserName = FinalDt.Rows[i]["UserName"].ToString(); UserEnt.WorkNo = FinalDt.Rows[i]["WorkNo"].ToString(); UserEnt.DoCreate(); } return(true); }
/// <summary> /// 生成被调查人员 /// </summary> /// <param name="SurveyId"></param> /// <returns></returns> public bool CreateSurveyedUser(string SurveyId) { SurveyedObj Ent = SurveyedObj.FindFirstByProperties(SurveyedObj.Prop_SurveyId, SurveyId); // 根据条件筛选的人员 string where = string.Empty; if (string.IsNullOrEmpty(Ent.OrgIds) || string.IsNullOrEmpty(Ent.OrgNames)) { where = " and 1<>1 "; //组织结构为空情况 } else { where = AnalyzeSQL(Ent); } var FilterUsrDt = FilterUser(SurveyId, where); //筛选职位 if (!string.IsNullOrEmpty(Ent.PostionNames)) { string split = string.Empty; string[] tempArr = Ent.PostionNames.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries); for (int j = 0; j < tempArr.Length; j++) { if (j > 0) { split += " or "; } split += " JobName like '%" + tempArr[j] + "%' "; } DataRow[] rows = FilterUsrDt.Select(split); //查询gw DataTable tempDt = new DataTable(); DataColumn D_UserId = new DataColumn("UserId"); tempDt.Columns.Add(D_UserId); DataColumn D_UserName = new DataColumn("UserName"); tempDt.Columns.Add(D_UserName); DataColumn D_WorkNo = new DataColumn("WorkNo"); tempDt.Columns.Add(D_WorkNo); DataColumn D_JobName = new DataColumn("JobName"); tempDt.Columns.Add(D_JobName); for (int i = 0; i < rows.Length; i++) { DataRow dr = tempDt.NewRow(); dr["UserId"] = rows[i]["UserId"]; dr["UserName"] = rows[i]["UserName"]; dr["WorkNo"] = rows[i]["WorkNo"]; dr["JobName"] = rows[i]["JobName"]; tempDt.Rows.Add(dr); } if (tempDt.Rows.Count > 0) { FilterUsrDt = tempDt; } } //Modify By WGM 8/9 string SQL = @"with SurObjUsr As ( select CA.* from FL_Culture..SurveyedObj As A cross apply( select Filed As UsrID from FL_Culture..GetTblByJson(A.AddUserNames,'Id') ) As CA where SurveyId='{0}' ), UserInfo As ( select T.UserID,T.Name,T.WorkNo from SurObjUsr As A left join FL_PortalHR..Sysuser As T on A.UsrID=T.UserID ) select distinct * from UserInfo"; SQL = SQL.Replace("FL_PortalHR", Global.AimPortalDB); SQL = string.Format(SQL, SurveyId); SQL = "select '' where 1<>1 "; //Change BY WGM 9-30 DataTable AddUsrDt = DataHelper.QueryDataTable(SQL); //排除的人员 string ReMoveSQL = @" select CA.* from FL_Culture..SurveyedObj As A cross apply( select Filed As UserID from FL_Culture..GetTblByJson(A.AddUserNames,'Id') ) As CA where SurveyId='{0}'"; ReMoveSQL = string.Format(ReMoveSQL, SurveyId); ReMoveSQL = "select '' where 1<>1 "; //Change BY WGM 9-30 DataTable RemoveDt = DataHelper.QueryDataTable(ReMoveSQL); if (RemoveDt.Rows.Count > 0) { for (int i = 0; i < FilterUsrDt.Rows.Count; i++) { for (int j = 0; j < RemoveDt.Rows.Count; j++) { if (FilterUsrDt.Rows[i]["UserID"].ToString() == RemoveDt.Rows[j]["UserID"].ToString()) { FilterUsrDt.Rows.Remove(FilterUsrDt.Rows[i]); } } } } //---------------------------生成DataTable----------------------------- DataTable FinalDt = new DataTable(); DataColumn Dc_UserId = new DataColumn("UserId"); FinalDt.Columns.Add(Dc_UserId); DataColumn Dc_UserName = new DataColumn("UserName"); FinalDt.Columns.Add(Dc_UserName); DataColumn Dc_DeptId = new DataColumn("DeptId"); FinalDt.Columns.Add(Dc_DeptId); DataColumn Dc_DeptName = new DataColumn("DeptName"); FinalDt.Columns.Add(Dc_DeptName); DataColumn Dc_WorkNo = new DataColumn("WorkNo"); FinalDt.Columns.Add(Dc_WorkNo); // string FindSQL = @"select FL_Culture.dbo.GetGroupIds('{0}') As DeptId,FL_Culture.dbo.GetGroupNames('{0}') As DeptName"; for (int i = 0; i < FilterUsrDt.Rows.Count; i++) { //string tempSQL = string.Format(FindSQL, FilterUsrDt.Rows[i]["UserID"].ToString()); //DataTable tempDt = DataHelper.QueryDataTable(tempSQL); DataRow dr = FinalDt.NewRow(); //dr["DeptId"] = FilterUsrDt.Rows[i]["GroupIds"]; //dr["DeptName"] = FilterUsrDt.Rows[i]["GroupNames"]; string FullDept = WFHelper.getZzjg(FilterUsrDt.Rows[i]["UserID"].ToString(), true); dr["DeptName"] = FullDept; dr["UserId"] = FilterUsrDt.Rows[i]["UserID"]; //* dr["UserName"] = FilterUsrDt.Rows[i]["UserName"]; //* dr["WorkNo"] = FilterUsrDt.Rows[i]["WorkNo"]; FinalDt.Rows.Add(dr); } for (int i = 0; i < AddUsrDt.Rows.Count; i++) { DataRow dr = FinalDt.NewRow(); //dr["DeptId"] = AddUsrDt.Rows[i]["GroupID"]; //dr["DeptName"] = AddUsrDt.Rows[i]["GroupName"]; string FullDept = WFHelper.getZzjg(AddUsrDt.Rows[i]["UserID"].ToString(), true); dr["DeptName"] = FullDept; dr["UserId"] = AddUsrDt.Rows[i]["UserID"]; //* dr["UserName"] = AddUsrDt.Rows[i]["Name"]; //* dr["WorkNo"] = AddUsrDt.Rows[i]["WorkNo"]; FinalDt.Rows.Add(dr); } //添加问卷发起者 DataRow CreateRow = FinalDt.NewRow(); CreateRow["UserId"] = Ent.CreateId; CreateRow["UserName"] = Ent.CreateName; FinalDt.Rows.Add(CreateRow); //---------------------------------------------------------------------------- //生成调查用户 for (int i = 0; i < FinalDt.Rows.Count; i++) { SurveyFinallyUsr UserEnt = new SurveyFinallyUsr(); UserEnt.CreateWay = "1"; //1 表示生成的用户而非创建 UserEnt.SurveyId = SurveyId; UserEnt.DeptName = FinalDt.Rows[i]["DeptName"].ToString(); //UserEnt.DeptId = FinalDt.Rows[i]["DeptId"].ToString(); UserEnt.WorkNo = FinalDt.Rows[i]["WorkNo"].ToString(); UserEnt.UserId = FinalDt.Rows[i]["UserId"].ToString(); UserEnt.UserName = FinalDt.Rows[i]["UserName"].ToString(); UserEnt.DoCreate(); } return(true); }