/// <summary> /// 查询 /// </summary> private void DoSelect() { string sql = @"select B.GroupID As CorpId ,B.Name As CorpName from sysuser As A left join SysGroup As B on A.Pk_corp=B.GroupID where A.UserID='{0}' " ; sql = string.Format(sql, UserInfo.UserID); DataTable Dt = DataHelper.QueryDataTable(sql); CommPowerSplit ps = new CommPowerSplit(); if (ps.IsHR(UserInfo.UserID, UserInfo.LoginName) || ps.IsSetMgrRole(UserInfo.UserID, UserInfo.LoginName)) //设置管理角色 or HR { ents = WelfareConfig.FindAll(SearchCriterion); } else { string CorpIds = string.Empty; SysUser Ent = SysUser.Find(UserInfo.UserID); // 判断公司登陆 if (Session["CompanyId"] != null) { CorpIds = Session["CompanyId"] + ""; } else { CorpIds = Ent.Pk_corp; } ents = WelfareConfig.FindAll(SearchCriterion, Expression.Sql("CorpId='" + CorpIds + "' or CreateId='" + UserInfo.UserID + "' ")); } this.PageState.Add("DataList", ents); }
private void DoSelect() { CommPowerSplit ps = new CommPowerSplit(); if (ps.IsSurveyRole(UserInfo.UserID, UserInfo.LoginName)) { this.PageState.Add("IsCanGrant", "1"); SearchCriterion.SetSearch("IsFixed", "1"); // SurveyQuestion[] Ents = SurveyQuestion.FindAll(SearchCriterion); this.PageState.Add("DataList", Ents); } else { string SQL = @"select * from SysRole As A inner join SysUserRole As B on A.RoleID=B.RoleID where A.Code='PubSurvey' and B.UserId='{0}'"; SQL = string.Format(SQL, UserInfo.UserID); object obj = DataHelper.QueryValue(SQL); if (obj == null) { if (Session["CompanyId"] != null) { string CorpId = Session["CompanyId"].ToString(); CorpId = !string.IsNullOrEmpty(CorpId) ? CorpId : "1007"; SearchCriterion.SetSearch("CompanyId", CorpId); } } SearchCriterion.SetSearch("IsFixed", "1"); // SurveyQuestion[] Ents = SurveyQuestion.FindAll(SearchCriterion); this.PageState.Add("DataList", Ents); } }
private void DoSelect() { CommPowerSplit ps = new CommPowerSplit(); if (ps.IsSurveyRole(UserInfo.UserID, UserInfo.LoginName)) { SearchCriterion.SetSearch("IsFixed", "2"); //固定问卷 SurveyQuestion[] Ents = SurveyQuestion.FindAll(SearchCriterion); this.PageState.Add("DataList", Ents); } else { SearchCriterion.SetSearch("IsFixed", "2"); //固定问卷 string CompanyId = string.Empty; //公司ID //first depend login corpid var Ent = SysUser.Find(UserInfo.UserID); UserContextInfo UC = new UserContextInfo(); CompanyId = UC.GetUserCurrentCorpId(UserInfo.UserID); SearchCriterion.AddSearch("CompanyId", CompanyId); SurveyQuestion[] Ents = SurveyQuestion.FindAll(SearchCriterion); this.PageState.Add("DataList", Ents); } }
/// <summary> /// 问卷类型枚举 /// </summary> private void SurveyTypeEnum() { string SQL = string.Empty; CommPowerSplit ps = new CommPowerSplit(); if (ps.IsSurveyRole(UserInfo.UserID, UserInfo.LoginName)) //admin or surveyRole { SQL = @"select '' As Id, '请选择...' As TypeName union all select Id,TypeName from FL_Culture..SurveyType"; this.PageState.Add("TypeEnum", DataHelper.QueryDict(SQL)); } else { // SQL = @" select '' As Id, '请选择...' As TypeName // union all // select Id,TypeName from FL_Culture..SurveyType // where ( EnabledState='1' or (EnabledState='1' and (AccessPower is null or len(AccessPower)=0))) // and Id in (select distinct TypeId from FL_Culture..View_SuryTypeUsr where UserID='{0}') "; SQL = @" select '' As Id, '请选择...' As TypeName union all select Id,TypeName from FL_Culture..SurveyType where ( EnabledState='1') and Id in (select distinct TypeId from FL_Culture..View_SuryTypeUsr where UserID='{0}') "; SQL = string.Format(SQL, UserInfo.UserID); this.PageState.Add("TypeEnum", DataHelper.QueryDict(SQL)); } }
/// <summary> /// 查询 /// </summary> private void DoSelect() { EasyDictionary dic = SysEnumeration.GetEnumDict("EmpAppeal"); dic.Add("%%", "请选择..."); PageState.Add("AppealTypeName", dic); //问卷角色或管理员 CommPowerSplit Role = new CommPowerSplit(); if (Role.IsAppealRole(UserInfo.UserID, UserInfo.LoginName)) { ents = UsrAppealList.FindAll(SearchCriterion); this.PageState.Add("UsrAppealListList", ents); } else { StringBuilder strb = new StringBuilder(); if (Session["CompanyId"] != null) //判断公司登陆 { SearchCriterion.SetSearch(UsrAppealList.Prop_CompanyId, Session["CompanyId"]); ents = UsrAppealList.FindAll(SearchCriterion); this.PageState.Add("UsrAppealListList", ents); } else { var UsrEnt = SysUser.Find(UserInfo.UserID); SearchCriterion.SetSearch(UsrAppealList.Prop_CompanyId, UsrEnt.Pk_corp); ents = UsrAppealList.FindAll(SearchCriterion); this.PageState.Add("UsrAppealListList", ents); } } }
private void Doselect() { string SurveyId = RequestData.Get("SurveyId") + ""; //V_SurveyAnswerExplain[] Ents = V_SurveyAnswerExplain.FindAllByProperties("SurveyId", SurveyId); string where = ""; foreach (CommonSearchCriterionItem item in SearchCriterion.Searches.Searches) { if (!String.IsNullOrEmpty(item.Value.ToString())) { switch (item.PropertyName) { case "StartTime": where += " and CreateTime>='" + item.Value + "' "; break; case "EndTime": where += " and CreateTime<='" + (item.Value.ToString()).Replace(" 0:00:00", " 23:59:59") + "' "; break; default: where += " and " + item.PropertyName + " like '%" + item.Value + "%' "; break; } } } var SEnt = SurveyQuestion.TryFind(SurveyId); if (SEnt != null && SEnt.IsFixed == "2") { CommPowerSplit PS = new CommPowerSplit(); if (PS.IsHR(UserInfo.UserID, UserInfo.LoginName) || PS.IsAdmin(UserInfo.LoginName) || PS.IsInAdminsRole(UserInfo.UserID)) { } else { UserContextInfo UC = new UserContextInfo(); where += " and B.pk_corp='" + UC.GetUserCurrentCorpId(UserInfo.UserID) + "' "; } } string SQL = @"SELECT distinct A.* from FL_Culture..V_SurveyAnswerExplain As A left join FL_PortalHR..SysUser as B on A.UserId=B.UserID where SurveyId='{0}' "; SQL = string.Format(SQL, SurveyId); SQL = SQL.Replace("FL_PortalHR", Global.AimPortalDB); SQL += where; this.PageState.Add("DataList", GetPageData(SQL, SearchCriterion)); // this.PageState.Add("DataList", Ents); }
private void DoSelect() { //SurveyQuestion[] Ent = SurveyQuestion.FindAll(SearchCriterion); string where = ""; foreach (CommonSearchCriterionItem item in SearchCriterion.Searches.Searches) { if (!String.IsNullOrEmpty(item.Value.ToString())) { switch (item.PropertyName) { case "StartTime": where += " and StartTime>='" + item.Value + "' "; break; case "EndTime": where += " and StartTime<='" + (item.Value.ToString()).Replace(" 0:00:00", " 23:59:59") + "' "; break; default: where += " and " + item.PropertyName + " like '%" + item.Value + "%' "; break; } } } //创建人或在角色组中的才可查看 string SQL = @"select T1.MustCheckFlow As IsCheck,T.* from FL_Culture..SurveyQuestion As T left join FL_Culture..SurveyType AS T1 on T.SurveyTypeId=T1.Id where IsFixed='0' and ( SurveyTitile is not null or SurveyTitile<>'' ) "; CommPowerSplit ps = new CommPowerSplit();//角色组 or HR if (!ps.IsSurveyRole(UserInfo.UserID, UserInfo.LoginName) || !ps.IsHR(UserInfo.UserID, UserInfo.LoginName)) { SQL += " and ( T.CreateId='{0}' or T.CompanyId in ({1}) ) "; //UserContextInfo UC = new UserContextInfo(); //加到公司权限的才能看到对应公司发布的问卷,否则只能看自己的 string Corps = ps.GetCorps(UserInfo.UserID) + ""; Corps = string.IsNullOrEmpty(Corps.TrimEnd(',')) ? "NULL" : string.Join(",", Corps.TrimEnd(',').Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).Select(ten => "'" + ten + "'").ToArray()); SQL = string.Format(SQL, UserInfo.UserID, Corps); } SQL = SQL.Replace("FL_PortalHR", Global.AimPortalDB); SQL += where; this.PageState.Add("DataList", GetPageData(SQL, SearchCriterion)); }
public void SurveyStatistic(string SurveyId) { //sql = string.Format(sql, SurveyId); //权限过滤 var SEnt = SurveyQuestion.TryFind(SurveyId); if (SEnt != null && SEnt.IsFixed == "2") { CommPowerSplit PS = new CommPowerSplit(); if (PS.IsHR(UserInfo.UserID, UserInfo.LoginName) || PS.IsAdmin(UserInfo.LoginName) || PS.IsInAdminsRole(UserInfo.UserID)) { } else { UserContextInfo UC = new UserContextInfo(); string SQL = "exec FL_Culture..pro_SummarySurvey_Fix '{0}','{1}' "; SQL = string.Format(SQL, SurveyId, UC.GetUserCurrentCorpId(UserInfo.UserID)); this.PageState.Add("DataList", DataHelper.QueryDataTable(SQL)); return; } } string sql = ""; sql = "select * from FL_Culture..SummarySurvey where SurveyId='{0}' order by SortIndex "; sql = string.Format(sql, SurveyId); var Ents = DataHelper.QueryDataTable(sql); if (Ents.Rows.Count != 0) { this.PageState.Add("DataList", Ents); } else { sql = "exec FL_Culture..pro_SummarySurvey '{0}'"; sql = string.Format(sql, SurveyId); Ents = DataHelper.QueryDataTable(sql); this.PageState.Add("DataList", Ents); } // this.PageState.Add("DataList", Ents); //if() //sql = @"select * from FL_Culture..QuestionItem where SurveyId='{0}' and QuestionType like '%填写项%' "; //sql = string.Format(sql, Id); //this.PageState.Add("FillQuestion", DataHelper.QueryDictList(sql)); }
// private void DoSelect() { string sort = RequestData.Get("sort") + ""; string tp = RequestData.Get("tp") + ""; //my个人积分 mgr 管理员 string where = ""; foreach (CommonSearchCriterionItem item in SearchCriterion.Searches.Searches) { if (!String.IsNullOrEmpty(item.Value.ToString())) { switch (item.PropertyName) { default: where += " and " + item.PropertyName + " like '%" + item.Value + "%' "; break; } } } string sql = " select * from FL_Culture..V_SurveyScore where UserName is not null "; if (tp.Contains("mgr")) //角色验证-->配置 { CommPowerSplit ps = new CommPowerSplit(); if (ps.IsScoreRole(UserInfo.UserID, UserInfo.LoginName)) { this.PageState.Add("Power", "1"); } else //分公司判断 { string corp = string.Empty; UserContextInfo UC = new UserContextInfo(); corp = UC.GetUserCurrentCorpId(UserInfo.UserID); this.PageState.Add("Power", "1"); where += " and CorpId='" + corp + "' "; } } else if (tp.Contains("my")) //个人 { where += " and UserID='" + UserInfo.UserID + "' "; this.PageState.Add("Power", "0"); } sql += where; this.PageState.Add("DataList", GetPageData(sql, SearchCriterion)); }
private void ImpExcel() { string where = string.Empty; //权限过滤 var Ent = SurveyQuestion.TryFind(SurveyId); if (Ent != null && Ent.IsFixed == "2") { CommPowerSplit PS = new CommPowerSplit(); if (PS.IsInAdminsRole(UserInfo.UserID) || PS.IsAdmin(UserInfo.LoginName) || PS.IsHR(UserInfo.UserID, UserInfo.LoginName)) { } else { UserContextInfo UC = new UserContextInfo(); where += " and D.Pk_corp='" + UC.GetUserCurrentCorpId(UserInfo.UserID) + "' "; } } tmpSQL = tmpSQL.Replace("##QUERY##", where); tmpSQL = tmpSQL.Replace("HR_OA_MiddleDB", Global.HR_OA_MiddleDB); string sql = string.Format(tmpSQL, SurveyId); string path = RequestData.Get <string>("path"); string fileName = RequestData.Get <string>("fileName"); string xlsName = fileName + "_" + System.DateTime.Now.ToString("yyyMMddhhmmss"); DataTable forExcelDt = DataHelper.QueryDataTable(sql); if (forExcelDt.Rows.Count > 0) { forExcelDt.TableName = "data"; WorkbookDesigner designer = new WorkbookDesigner(); string xlsMdlPath = Server.MapPath(path); designer.Open(xlsMdlPath); designer.SetDataSource(forExcelDt); designer.Process(); Aspose.Cells.Worksheet ws = designer.Workbook.Worksheets.GetSheetByCodeName(fileName); string newXls = xlsName + ".xls"; System.IO.DirectoryInfo xlspath = new System.IO.DirectoryInfo(Server.MapPath("../Excel/tempexcel")); ExcelHelper.deletefile(xlspath); designer.Save(Server.MapPath("../Excel/tempexcel") + "\\" + newXls, FileFormatType.Excel2003); this.PageState.Add("fileName", "/Excel/tempexcel/" + newXls); } }
// //问卷模板 private void GetTplEnum() { string SQL = string.Empty; CommPowerSplit ps = new CommPowerSplit(); if (ps.IsSurveyRole(UserInfo.UserID, UserInfo.LoginName)) //admin or surveyRole { SQL = @"select Id,SurveyTitile As Name from FL_Culture..SurveyQuestion where IsFixed='1' and State='1'"; SQL = SQL.Replace("FL_PortalHR", Global.AimPortalDB); PageState.Add("tplEnum", DataHelper.QueryDict(SQL)); } else { var Ent = SysUser.Find(UserInfo.UserID); SQL = @" select Id,SurveyTitile As Name from FL_Culture..SurveyQuestion where IsFixed='1' and State='1' and CompanyId='{0}' Or charindex('{0}',GrantCorpId ) >0 "; SQL = SQL.Replace("FL_PortalHR", Global.AimPortalDB); SQL = string.Format(SQL, Ent.Pk_corp); PageState.Add("tplEnum", DataHelper.QueryDict(SQL)); } }
/// <summary> /// 查询 /// </summary> private void DoSelect() { string CorpId = string.Empty; string where = string.Empty; var UsrEnt = SysUser.Find(UserInfo.UserID); CommPowerSplit ps = new CommPowerSplit(); if (ps.IsNoticeRole(UserInfo.UserID, UserInfo.LoginName)) { where += " (WorkFlowState='1' or WorkFlowState='2' or WorkFlowState='-1') "; if (!string.IsNullOrEmpty(SearchCriterion.GetSearchValue("Year") + "")) { where += " and year(ApplyTime)= " + SearchCriterion.GetSearchValue("Year") + " "; } SearchCriterion.RemoveSearch("Year"); ents = UsrTravelWelfare.FindAll(SearchCriterion, Expression.Sql(where)); this.PageState.Add("UsrTravelWelfareList", ents); } else { // 判断公司登陆 UserContextInfo UC = new UserContextInfo(); CorpId = UC.GetUserCurrentCorpId(UserInfo.UserID); //SearchCriterion.AddSearch("CompanyId", CorpId); where += " (WorkFlowState='1' or WorkFlowState='2' or WorkFlowState='-1') " + AppealUsrAuth(); if (!string.IsNullOrEmpty(SearchCriterion.GetSearchValue("Year") + "")) { where += " and year(ApplyTime)= " + SearchCriterion.GetSearchValue("Year") + " "; } SearchCriterion.RemoveSearch("Year"); SearchCriterion.SetOrder("CompanyId", true); ents = UsrTravelWelfare.FindAll(SearchCriterion, Expression.Sql(where)); this.PageState.Add("UsrTravelWelfareList", ents); } }
private void DefaultSelect() { string where = ""; foreach (CommonSearchCriterionItem item in SearchCriterion.Searches.Searches) { if (!String.IsNullOrEmpty(item.Value.ToString())) { switch (item.PropertyName) { case "WorkNo": where += " and A." + item.PropertyName + " like '%" + item.Value + "%' "; break; default: where += " and " + item.PropertyName + " like '%" + item.Value + "%' "; break; } } } //权限过滤 var Ent = SurveyQuestion.TryFind(SurveyId); if (Ent != null && Ent.IsFixed == "2") { CommPowerSplit PS = new CommPowerSplit(); if (PS.IsInAdminsRole(UserInfo.UserID) || PS.IsAdmin(UserInfo.LoginName) || PS.IsHR(UserInfo.UserID, UserInfo.LoginName)) { } else { UserContextInfo UC = new UserContextInfo(); where += " and D.Pk_corp='" + UC.GetUserCurrentCorpId(UserInfo.UserID) + "' "; } } string sql = @" IF (OBJECT_ID('tempdb..#ST') IS NOT NULL) DROP TABLE tempdb..#ST; select A.*, B.SortIndex As P, C.SortIndex As S into #ST from FL_Culture..SummarySurvey_detail As A left join FL_Culture..QuestionItem As B on B.Id=A.QuestionId and A.SurveyId=B.SurveyId left join FL_Culture..QuestionAnswerItem As C on A.SurveyId=C.SurveyId and A.QuestionItemId=C.Id left join FL_PortalHR..SysUser As D on A.UserId=D.UserId where A.SurveyId='{0}' and A.WorkNo is not null ##query## order by A.UserId, P,S "; if (!string.IsNullOrEmpty(where)) { sql = sql.Replace("##query##", where); } else { sql = sql.Replace("##query##", ""); } sql = string.Format(sql, SurveyId); sql = sql.Replace("FL_PortalHR", Global.AimPortalDB); sql = sql.Replace("HR_OA_MiddleDB", Global.HR_OA_MiddleDB); this.PageState.Add("DataList", GetPageData(sql, SearchCriterion)); }
private void ImpExcel() { string WorkFlowState = RequestData.Get <string>("WorkFlowState"); string where = string.Empty; //审批意见 if (!string.IsNullOrEmpty(WorkFlowState)) { where += " and A.WorkFlowState='" + WorkFlowState + "' "; } CommPowerSplit ps = new CommPowerSplit(); if (ps.IsNoticeRole(UserInfo.UserID, UserInfo.LoginName) || ps.IsHR(UserInfo.UserID, UserInfo.LoginName)) { //管理员或HR组 } else { where += AppealUsrAuth(); } //A.TravelMoney, 旅游费用/每人 string sql = @"select distinct A.UserName,A.WorkNo,A.CompanyName,A.DeptName,A.Sex, A.TravelAddr,A.TravelTime as TimeSeg, A.XLMoney as TravelMoney, case when HaveFamily='Y' then '是' when HaveFamily='N' then '否' end As IsFamily, convert(varchar(10),A.ApplyTime ,120) As ApplyTime, C.Indutydate As IndutyDate, datediff(year,C.Indutydate,getdate()) As WorkYear, B.Name As Fname,B.Sex As OSex, B.Age As OAge,cast(B.Height as varchar(10)) Height, Case when WorkFlowState='1' then '未处理' when WorkFlowState='-1' then '不同意' when WorkFlowState='2' then '同意' when WorkFlowState='Exception' then '异常' End As State from FL_Culture..UsrTravelWelfare As A left join FL_Culture..UsrTravelInfo As B on A.Id=B.WelfareTravelId left join FL_PortalHR..SysUser As C on C.UserID=A.UserId where (WorkFlowState='1' or WorkFlowState='2' or WorkFlowState='-1') and A.Id is not null"; sql = sql.Replace("FL_PortalHR", Global.AimPortalDB); sql += where; string path = RequestData.Get <string>("path"); string fileName = RequestData.Get <string>("fileName"); string xlsName = fileName + "_" + System.DateTime.Now.ToString("yyyMMddhhmmss"); DataTable forExcelDt = DataHelper.QueryDataTable(sql); forExcelDt = DtDetail(forExcelDt); if (forExcelDt.Rows.Count > 0) { forExcelDt.TableName = "data"; WorkbookDesigner designer = new WorkbookDesigner(); string xlsMdlPath = Server.MapPath(path); designer.Open(xlsMdlPath); designer.SetDataSource(forExcelDt); designer.Process(); Aspose.Cells.Worksheet ws = designer.Workbook.Worksheets.GetSheetByCodeName(fileName); string newXls = xlsName + ".xls"; System.IO.DirectoryInfo xlspath = new System.IO.DirectoryInfo(Server.MapPath("../Excel/tempexcel")); ExcelHelper.deletefile(xlspath); designer.Save(Server.MapPath("../Excel/tempexcel") + "\\" + newXls, FileFormatType.Excel2003); this.PageState.Add("fileName", "../Excel/tempexcel/" + newXls); } }
private void Doeselect() { string where = ""; foreach (CommonSearchCriterionItem item in SearchCriterion.Searches.Searches) { if (!String.IsNullOrEmpty(item.Value.ToString())) { switch (item.PropertyName) { case "StartTime": where += " and CreateTime>='" + item.Value + "' "; break; case "EndTime": where += " and CreateTime<='" + (item.Value.ToString()).Replace(" 0:00:00", " 23:59:59") + "' "; break; default: where += " and " + item.PropertyName + " like '%" + item.Value + "%' "; break; } } } string SQL = string.Empty; //问卷角色或管理员 获取HR CommPowerSplit Role = new CommPowerSplit(); if (Role.IsHR(UserInfo.UserID, UserInfo.LoginName) || Role.IsSurveyRole(UserInfo.UserID, UserInfo.LoginName)) { SQL = @" select A.*,B.SummitCount from FL_Culture..SurveyQuestion As A left join ( select SurveyId ,count(*) As SummitCount from FL_Culture..SurveyCommitHistory group by SurveyId ) As B on A.Id=B.SurveyId where A.IsFixed='2' and A.State='1' "; } else { string CompanyId = string.Empty;//判断公司登陆 UserContextInfo UC = new UserContextInfo(); CompanyId = UC.GetUserCurrentCorpId(UserInfo.UserID); SQL = @" select A.*,B.SummitCount from FL_Culture..SurveyQuestion As A left join ( select SurveyId ,count(*) As SummitCount from FL_Culture..SurveyCommitHistory As A left join FL_PortalHR..sysuser As B on A.SurveyedUserId=B.UserId where B.Pk_corp='{0}' group by SurveyId ) As B on A.Id=B.SurveyId where A.IsFixed='2' and A.state='1'"; // --- 常用问卷不需要此限制条件 and A.CompanyId='{0}' SQL = string.Format(SQL, CompanyId); } SQL += where; //SearchCriterion.SetOrder("CreateTime", false); //IList<SurveyQuestion> Ents = SurveyQuestion.FindAll(SearchCriterion); this.PageState.Add("DataList", GetPageData(SQL, SearchCriterion)); }
public void DoSelect(string year, string month, string SurveyId) { //问题项 string sql = @" select Distinct * from FL_Culture..QuestionItem where SurveyId='{0}' and '单选项,多选项' like '%'+QuestionType+'%' and Content like '%主要原因%' " ; sql = string.Format(sql, SurveyId); DataTable TypeDt = DataHelper.QueryDataTable(sql); //选择项 sql = @"select A.Id,A.SurveyTitle,A.Content, A.SubItemId,A.SortIndex, B.Id As ItemId,B.AnSwer,B.SortIndex from ( select * from FL_Culture..QuestionItem where SurveyId='{0}' and '单选项,多选项' like '%'+QuestionType+'%' ) As A left join FL_Culture..QuestionAnswerItem As B on A.SubItemId=B.QuestionItemId and A.SurveyId=B.SurveyId where Content like '%主要原因%' order by A.SortIndex,B.SortIndex"; sql = string.Format(sql, SurveyId); DataTable QDt = DataHelper.QueryDataTable(sql); //每月 sql = @"select QuestionId, QuestionItemId,count(QuestionItemId) As ChoiceTotal from FL_Culture..SurveyedResult As A left join FL_PortalHR..SysUser As B on A.UserId=B.UserID where surveyId='{0}' and QuestionItemContent='1' and year(CreateTime)={1} and month(CreateTime)={2} and Len(QuestionItemId)>0 and 1=1 group by QuestionId,QuestionItemId order by QuestionId"; sql = sql.Replace("FL_PortalHR", Global.AimPortalDB); sql = string.Format(sql, SurveyId, year, month); //权限过滤 CommPowerSplit PS = new CommPowerSplit(); if (PS.IsHR(UserInfo.UserID, UserInfo.LoginName) || PS.IsAdmin(UserInfo.LoginName) || PS.IsInAdminsRole(UserInfo.UserID)) { } else { UserContextInfo UC = new UserContextInfo(); sql = sql.Replace("and 1=1", " and B.Pk_corp='" + UC.GetUserCurrentCorpId(UserInfo.UserID) + "' "); } DataTable ItemTal = DataHelper.QueryDataTable(sql); //每月和 int monthTotal = 0; if (ItemTal.Rows.Count > 0) { for (int i = 0; i < ItemTal.Rows.Count; i++) { int temp = 0; if (int.TryParse(ItemTal.Rows[i]["ChoiceTotal"].ToString(), out temp)) { monthTotal += temp; } } } MonthTotal = monthTotal; //全年每项 sql = @"select QuestionId, count(QuestionId) As ChoiceTotal from FL_Culture..SurveyedResult As A left join FL_PortalHR..SysUser As B on A.UserId=B.UserID where surveyId='{0}' and QuestionItemContent='1' and 1=1 and year(CreateTime)={1} and Len(QuestionItemId)>0 group by QuestionId order by QuestionId"; sql = string.Format(sql, SurveyId, year); sql = sql.Replace("FL_PortalHR", Global.AimPortalDB); //权限过滤 if (PS.IsHR(UserInfo.UserID, UserInfo.LoginName) || PS.IsAdmin(UserInfo.LoginName) || PS.IsInAdminsRole(UserInfo.UserID)) { } else { UserContextInfo UC = new UserContextInfo(); sql = sql.Replace("and 1=1", " and B.Pk_corp='" + UC.GetUserCurrentCorpId(UserInfo.UserID) + "' "); } DataTable YearDt = DataHelper.QueryDataTable(sql); //全年和 int yearTotal = 0; if (YearDt.Rows.Count > 0) { for (int i = 0; i < YearDt.Rows.Count; i++) { int temp = 0; if (int.TryParse(YearDt.Rows[i]["ChoiceTotal"].ToString(), out temp)) { yearTotal += temp; } } } YearTotal = yearTotal; for (int i = 0; i < TypeDt.Rows.Count; i++) { //问题项 QItem tempItem = new QItem(); tempItem.type = TypeDt.Rows[i]["Content"].ToString(); //子项 DataRow[] rows = QDt.Select(" Id='" + TypeDt.Rows[i]["Id"] + "' "); tempItem.items = new List <string>(); tempItem.itemsChoices = new List <string>(); for (int j = 0; j < rows.Length; j++) { tempItem.items.Add(rows[j]["AnSwer"].ToString()); DataRow tempRow = ItemTal.Select(" QuestionItemId='" + rows[j]["ItemId"] + "' ").FirstOrDefault(); if (tempRow == null) { tempItem.itemsChoices.Add("0"); } else { tempItem.itemsChoices.Add(tempRow["ChoiceTotal"].ToString()); } } //每年该项 if (YearDt.Rows.Count > 0) { DataRow yearRow = YearDt.Select(" QuestionId ='" + TypeDt.Rows[i]["Id"] + "'").FirstOrDefault(); if (yearRow == null) { tempItem.yearTotal = 0; } else { tempItem.yearTotal = int.Parse(string.IsNullOrEmpty(yearRow["ChoiceTotal"].ToString()) ? "0 " : yearRow["ChoiceTotal"].ToString()); } } QItm.Add(tempItem); } }
private void Doeselect() { string where = ""; foreach (CommonSearchCriterionItem item in SearchCriterion.Searches.Searches) { if (!String.IsNullOrEmpty(item.Value.ToString())) { switch (item.PropertyName) { case "StartTime": where += " and StartTime>='" + item.Value + "' "; break; case "EndTime": where += " and StartTime<='" + (item.Value.ToString()).Replace(" 0:00:00", " 23:59:59") + "' "; break; default: where += " and " + item.PropertyName + " like '%" + item.Value + "%' "; break; } } } string SQL = string.Empty; //问卷角色或管理员 CommPowerSplit Role = new CommPowerSplit(); if (Role.IsSurveyRole(UserInfo.UserID, UserInfo.LoginName)) { SQL = @" select A.*,B.SummitCount from FL_Culture..SurveyQuestion As A left join ( select SurveyId ,count(*) As SummitCount from FL_Culture..SurveyCommitHistory group by SurveyId ) As B on A.Id=B.SurveyId where A.IsFixed='0' and ( A.state='1' or A.state='2' ) "; } else { string CompanyId = string.Empty; UserContextInfo UC = new UserContextInfo(); CompanyId = UC.GetUserCurrentCorpId(UserInfo.UserID); //判断公司登陆 //(A.state='1' or A.state='2') 开始 结束 SQL = @" select A.*,B.SummitCount from FL_Culture..SurveyQuestion As A left join ( select SurveyId ,count(*) As SummitCount from FL_Culture..SurveyCommitHistory group by SurveyId ) As B on A.Id=B.SurveyId where A.IsFixed='0' and A.CompanyId='{0}' and (A.state='1' or A.state='2') "; SQL = string.Format(SQL, CompanyId); } SQL += where; this.PageState.Add("DataList", GetPageData(SQL, SearchCriterion)); }
private void DoSelect() { // 角色 根据公司,部门找到配置的HR,然后具有查看权限 string where = string.Empty, Company = string.Empty; string sql = @"select B.WorkNo,A.*,A.CorpName+'/'+A.DeptName As Org from FL_Culture..EmpVoiceAskQuestion As A left join FL_PortalHR..SysUser As B on A.CreateId=B.UserID where 1=1 and Category<>'' "; sql = sql.Replace("FL_PortalHR", Global.AimPortalDB); //公司权限 CommPowerSplit ps = new CommPowerSplit(); if (!ps.IsEmpVoiceRole(UserInfo.UserID, UserInfo.LoginName)) { //获取人员部门 var UsrEnt = SysUser.Find(UserInfo.UserID); string SQL = @"with GetTree as ( select * from HR_OA_MiddleDB..fld_bmml where pk_deptdoc='{0}' union all select A.* from HR_OA_MiddleDB..fld_bmml As A join GetTree as B on A.pk_deptdoc=B.pk_fathedept ) select deptname+',' as [text()] from getTree FOR XML PATH('') " ; SQL = SQL.Replace("HR_OA_MiddleDB", Global.HR_OA_MiddleDB); SQL = string.Format(SQL, UsrEnt.Pk_deptdoc); string DeptPathStr = DataHelper.QueryValue(SQL).ToString(); DeptPathStr = string.IsNullOrEmpty(DeptPathStr) ? "" : DeptPathStr; //找到配置的HR专员(HRUsrId) SQL = @"select top 1 *, case when patindex('%'+DeptName+'%','{1}')=0 then 100 else patindex('%'+DeptName+'%','{1}') end As SortIndex from FL_Culture..SysApproveConfig As A where A.CompanyId='{0}' and HRUsrId is not null and ( HRUsrId='{2}' or HRManagerId='{2}' ) order by SortIndex"; // 判断公司登陆 UserContextInfo UC = new UserContextInfo(); Company = UC.GetUserCurrentCorpId(UserInfo.UserID); SQL = string.Format(SQL, Company, DeptPathStr, UserInfo.UserID); DataTable dt = DataHelper.QueryDataTable(SQL); if (dt.Rows.Count > 0) { where += " and CorpId= '" + Company + "' "; } else { where += " and 1<>1 "; } } //分类 if (!string.IsNullOrEmpty(nodeName) && (nodeName != "所有分类") && nodeName != "null") { sql += " and Category='" + nodeName + "' "; } sql += where; string qry = ""; foreach (CommonSearchCriterionItem item in SearchCriterion.Searches.Searches) { if (!String.IsNullOrEmpty(item.Value.ToString())) { switch (item.PropertyName) { case "StartTime": qry += " and CreateTime>='" + item.Value + "' "; break; case "EndTime": qry += " and CreateTime<='" + (item.Value.ToString()).Replace(" 0:00:00", " 23:59:59") + "' "; break; default: qry += " and " + item.PropertyName + " like '%" + item.Value + "%' "; break; } } } sql += qry; SearchCriterion.SetOrder(EmpVoiceAskQuestion.Prop_CreateTime); OrderCriterionItem oci1 = new OrderCriterionItem(EmpVoiceAskQuestion.Prop_CreateTime); SearchCriterion.Orders.Add(oci1); this.PageState.Add("DataList", GetPageData(sql, SearchCriterion)); }
/// <summary> /// 查询导出 /// </summary> private void QryAndImpExcel() { string where = ""; string Corp = RequestData.Get("Corp") + ""; string WorkNo = RequestData.Get("WorkNo") + ""; string UserName = RequestData.Get("UserName") + ""; string JobName = RequestData.Get("JobName") + ""; string WorkAge = RequestData.Get("WorkAge") + ""; string SurveyId = RequestData.Get("SurveyId") + ""; string title = RequestData.Get("title") + ""; if (!string.IsNullOrEmpty(Corp)) { where += " and A.Corp like '%" + Corp + "%' "; } if (!string.IsNullOrEmpty(WorkNo)) { where += " and A.WorkNo like '%" + WorkNo + "%' "; } if (!string.IsNullOrEmpty(UserName)) { where += " and A.UserName like '%" + UserName + "%' "; } if (!string.IsNullOrEmpty(JobName)) { where += " and A.JobName like '%" + JobName + "%' "; } if (!string.IsNullOrEmpty(WorkAge)) { where += " and A.WorkAge like '%" + WorkAge + "%' "; } string sql = @"select newid() '编号', A.WorkNo '工号', UserName '姓名',A.Sex '性别',Corp '公司',Dept '部门',convert(varchar(10),A.Indutydate,120) '入职日期',WorkAge '工龄',Crux '关键岗位', convert(varchar(10), BornDate,120) '出生日期',A.Age '年龄',JobName '岗位',JobDegree '岗位等级',JobSeq '岗位序列',Skill '技能',A.Content, A.QuestionType, Case when Explanation<>'' then A.Answer+'(' + Explanation+')' else A.Answer End As Answer, B.SortIndex As P, C.SortIndex As S from FL_Culture..SummarySurvey_detail As A left join FL_Culture..QuestionItem As B on B.Id=A.QuestionId and A.SurveyId=B.SurveyId left join FL_Culture..QuestionAnswerItem As C on A.SurveyId=C.SurveyId and A.QuestionItemId=C.Id left join FL_PortalHR..SysUser As D on A.WorkNo=D.WorkNo where A.SurveyId='{0}' and A.WorkNo is not null ##query## order by A.UserId, P,S "; //权限过滤 var SEnt = SurveyQuestion.TryFind(SurveyId); if (SEnt != null && SEnt.IsFixed == "2") //IsFixed "2" 常用问卷 { CommPowerSplit PS = new CommPowerSplit(); if (PS.IsInAdminsRole(UserInfo.UserID) || PS.IsAdmin(UserInfo.LoginName) || PS.IsHR(UserInfo.UserID, UserInfo.LoginName)) { } else { UserContextInfo UC = new UserContextInfo(); where += " and D.Pk_corp='" + UC.GetUserCurrentCorpId(UserInfo.UserID) + "' "; } } if (!string.IsNullOrEmpty(where)) { sql = sql.Replace("##query##", where); } else { sql = sql.Replace("##query##", ""); } sql = sql.Replace("FL_PortalHR", Global.AimPortalDB); sql = sql.Replace("HR_OA_MiddleDB", Global.HR_OA_MiddleDB); sql = string.Format(sql, SurveyId); DataTable OrigDt = DataHelper.QueryDataTable(sql); DataTable newDt = CreateNewTable(OrigDt, SurveyId, "IMP"); newDt.Columns.Remove("编号"); newDt.Columns.Remove("Content"); newDt.Columns.Remove("QuestionType"); newDt.Columns.Remove("Answer"); if (title.Contains("内部服务评分")) { string CorpStr = string.Empty; for (int i = 0; i < newDt.Rows.Count; i++) { if (i > 0) { CorpStr += ","; } CorpStr += newDt.Rows[i]["公司"] + ""; } string[] CorpArr = CorpStr.Split(','); string DeptStr = @"财务管理中心,信息管理中心,综合管理中心,物流研发中心,物流事业部,法务稽核部,营销服务中心,人力资源中心,海运事业部,空运事业部,商业发展部"; string[] DeptArr = DeptStr.Split(','); ExportExcel(DeptArr, CorpArr, title); return; } else { string xlsName = title + "_" + System.DateTime.Now.ToString("yyyMMddhhmm") + ".xls"; string FilnalName = Server.MapPath("../Excel/tempexcel") + "/" + xlsName; OutFileToDisk(newDt, "DataSource2", FilnalName); this.PageState.Add("fileName", "/Excel/tempexcel/" + xlsName); } }
private void DefaultSelect() { string where = ""; foreach (CommonSearchCriterionItem item in SearchCriterion.Searches.Searches) { if (!String.IsNullOrEmpty(item.Value.ToString())) { switch (item.PropertyName) { default: where += " and A." + item.PropertyName + " like '%" + item.Value + "%' "; break; } } } //iframe string qstSQl = string.Empty; if (!string.IsNullOrEmpty(RequestData.Get("type") + "")) { if (!string.IsNullOrEmpty(RequestData.Get("Qty") + "")) { string Qty = RequestData.Get("Qty") + ""; switch ((RequestData.Get("GroupType") + "").Trim().ToLower()) { case "corp": //公司维度 where += " and A.Corp='" + Qty + "' "; break; case "sex": where += " and A.Sex='" + Qty + "' "; break; case "workage": if (Qty.Contains("未知")) { where += " and A.WorkAge is null "; } else { where += " and A.WorkAge=" + Qty + " "; } break; case "ageseg": { string OraStr = Qty; string[] Arr = Qty.Split('-'); if (Arr.Length > 1) { where += " and A.Age between " + Arr[0] + " and " + Arr[1] + " "; } else { where += " and A.age " + Arr[0] + " "; } } break; } string QuestionId = RequestData.Get("QuestionId") + ""; string QuestionItemId = RequestData.Get("QuestionItemId") + ""; if (!String.IsNullOrEmpty(QuestionId) && !string.IsNullOrEmpty(QuestionItemId)) { string tmpSQL = @" and A.WorkNo in ( select Distinct WorkNo from FL_Culture..SummarySurvey_detail As A where A.SurveyId='{0}' and QuestionId='{1}' and QuestionItemId='{2}' ) " ; qstSQl = string.Format(tmpSQL, SurveyId, QuestionId, QuestionItemId); } } } //权限过滤 var Ent = SurveyQuestion.TryFind(SurveyId); if (Ent != null && Ent.IsFixed == "2") { CommPowerSplit PS = new CommPowerSplit(); if (PS.IsInAdminsRole(UserInfo.UserID) || PS.IsAdmin(UserInfo.LoginName) || PS.IsHR(UserInfo.UserID, UserInfo.LoginName)) { } else { UserContextInfo UC = new UserContextInfo(); where += " and B.Pk_corp='" + UC.GetUserCurrentCorpId(UserInfo.UserID) + "' "; } } //查询SQL string sql = @"select * from FL_Culture..SummarySurvey_detail As A left join FL_PortalHR..SysUser As B on A.WorkNo=B.WorkNo where SurveyId='{0}' and 1=1 "; sql = sql + where + qstSQl; sql = string.Format(sql, SurveyId); sql = sql.Replace("FL_PortalHR", Global.AimPortalDB); DataTable OrigDt = DataHelper.QueryDataTable(sql); DataTable newDt = CreateNewTable(OrigDt, SurveyId, ""); DataTable dt = GetPagedTable(newDt, SearchCriterion.CurrentPageIndex, SearchCriterion.PageSize); SearchCriterion.RecordCount = newDt.Rows.Count; PageState.Add("DataList", dt); }
/// <summary> /// 默认查询 /// </summary> private void DoDefaultSelect() { if (this.RequestData.Get("ation") + "" == "query") { QueryData(); return; } //权限过滤 var SEnt = SurveyQuestion.TryFind(surveyId); if (SEnt != null && SEnt.IsFixed == "2") { CommPowerSplit PS = new CommPowerSplit(); if (PS.IsHR(UserInfo.UserID, UserInfo.LoginName) || PS.IsAdmin(UserInfo.LoginName) || PS.IsInAdminsRole(UserInfo.UserID)) { } else { UserContextInfo UC = new UserContextInfo(); string where = string.Empty, CorpId = string.Empty; where += " and C.GroupID='" + UC.GetUserCurrentCorpId(UserInfo.UserID) + "' "; CorpId = UC.GetUserCurrentCorpId(UserInfo.UserID) + ""; // 获取公司枚举 string SQL = @"select distinct C.GroupID, C.Name from FL_Culture..SurveyCommitHistory As A left join FL_PortalHR..SysUser As B on A.SurveyedUserId=B.UserId left join FL_PortalHR..SysGroup As C on B.Pk_corp=C.GroupID where C.type=2 and A.SurveyId='{0}' and C.Name like '%公司%' "; SQL = SQL.Replace("FL_PortalHR", Global.AimPortalDB); SQL = string.Format(SQL, surveyId); SQL += where; var CropEnt = DataHelper.QueryDict(SQL); this.PageState.Add("CropEnum", CropEnt); SQL = "exec FL_Culture..pro_SummarySurvey_Choices_Fix '{0}','{1}' "; SQL = string.Format(SQL, surveyId, CorpId); DataTable MtDt = DataHelper.QueryDataTable(SQL); this.PageState.Add("DataList", GetTreeData(MtDt)); return; } } string sql = string.Empty; if (!string.IsNullOrEmpty(surveyId)) { // 获取公司枚举 sql = @"select distinct C.GroupID, C.Name from FL_Culture..SurveyCommitHistory As A left join FL_PortalHR..SysUser As B on A.SurveyedUserId=B.UserId left join FL_PortalHR..SysGroup As C on B.Pk_corp=C.GroupID where C.type=2 and A.SurveyId='{0}' and C.Name like '%公司%' "; sql = sql.Replace("FL_PortalHR", Global.AimPortalDB); sql = string.Format(sql, surveyId); var CropEnt = DataHelper.QueryDict(sql); this.PageState.Add("CropEnum", CropEnt); } //if (IsPostBack) //{ sql = "exec FL_Culture..pro_SummarySurvey_Choices '{0}'"; sql = string.Format(sql, surveyId); DataTable Dt = DataHelper.QueryDataTable(sql); Dt = GetTreeData(Dt); this.PageState.Add("DataList", Dt); //} }
private void ImpExcel() { var year = RequestData.Get("year") + ""; var WorkFlowState = RequestData.Get("WorkFlowState") + ""; var WelfareType = RequestData.Get("WelfareType") + ""; var month = RequestData.Get("month") + ""; var type = RequestData.Get("type") + ""; var DealState = RequestData.Get("DealState") + ""; string where = string.Empty; if (string.IsNullOrEmpty(year)) { year = DateTime.Now.Year + ""; where += " and Year(ApplyTime)=" + year + " "; } else { where += " and Year(ApplyTime)=" + year + " "; } //处理结果 if (!string.IsNullOrEmpty(DealState)) { where += " and WorkFlowState='" + DealState + "' "; } //月份 if (!string.IsNullOrEmpty(month)) { where += " and Month(ApplyTime)=" + month + " "; } //未处理 if (type == "n") { where += " and A.WorkFlowState='1' "; } else //已处理 { where += " and (WorkFlowState='2' or WorkFlowState='-1') "; } //审批意见 if (!string.IsNullOrEmpty(WorkFlowState)) { where += " and A.WorkFlowState='" + WorkFlowState + "' "; } CommPowerSplit ps = new CommPowerSplit(); if (!ps.IsNoticeRole(UserInfo.UserID, UserInfo.LoginName)) { where += AppealUsrAuth(); } string SQL = @" select distinct Year(A.ApplyTime) As Year,Month(A.ApplyTime) As Month, A.UserName,A.WorkNo,A.CompanyName,A.DeptName, convert(varchar(10),A.IndutyData,120) As IndutyDate, A.Sex,A.OtherUserName,A.OtherIdentityCard,A.OSex, C.UsrName as ChildName,C.Sex As ChildSex,C.IDCartNo AS ChildIDCard,C.IDType, case when A.WorkFlowState='2' then '同意' when A.WorkFlowState='-1' then '不同意' end As State, case when A.IsSingleChild='Y' then '是' when A.IsSingleChild='N' then '否' end As IsSingleChild, case when A.IsDoubleWorker='Y' then '是' when A.IsDoubleWorker='N' then '否' end As IsDoubleWorker, OtherUserWorkNo from FL_Culture..UsrChildWelfare As A left join FL_Culture..UsrWelfareChildInfo As C on C.ChildWelfareId=A.Id where A.Id is not null "; SQL += where; //导出SQL string childSQL = string.Empty, dbleSQL = string.Empty; if (WelfareType == "double") { dbleSQL = SQL + " and C.BeRelation='配偶' "; } else if (WelfareType == "child") { childSQL = SQL + " and C.BeRelation='子女' "; } else { dbleSQL = SQL + " and C.BeRelation='配偶' "; childSQL = SQL + " and C.BeRelation='子女' "; } string xlsNameDouble = "员工保险汇总表_配偶保险" + "_" + System.DateTime.Now.ToString("yyyMMddhhmmss"); string xlsNameChild = "员工保险汇总表_子女保险" + "_" + System.DateTime.Now.ToString("yyyMMddhhmmss"); DataTable dble = null; DataTable child = null; string url = System.Configuration.ConfigurationManager.AppSettings["SurveyUrl"] + ""; if (string.IsNullOrEmpty(WelfareType)) { child = DataHelper.QueryDataTable(childSQL); dble = DataHelper.QueryDataTable(dbleSQL); string fileName = string.Empty; { string FullPath = @"/Excel/EmpChild.xls"; if (url.Contains("FD")) { FullPath = @"/FD/Excel/EmpChild.xls"; } child.TableName = "data"; WorkbookDesigner designer = new WorkbookDesigner(); string xlsMdlPath = Server.MapPath(FullPath); designer.Open(xlsMdlPath); designer.SetDataSource(child); designer.Process(); Aspose.Cells.Worksheet ws = designer.Workbook.Worksheets.GetSheetByCodeName("子女保险"); string newXls = xlsNameChild + ".xls"; System.IO.DirectoryInfo xlspath = new System.IO.DirectoryInfo(Server.MapPath("../Excel/tempexcel")); ExcelHelper.deletefile(xlspath); designer.Save(Server.MapPath("../Excel/tempexcel") + "\\" + newXls, FileFormatType.Excel2003); if (!string.IsNullOrEmpty(fileName)) { fileName += "|" + "/Excel/tempexcel/" + newXls; // | 文件分割 } else { fileName += "/Excel/tempexcel/" + newXls; } } { //double string FullPath = @"../Excel/EmpDouble.xls"; if (url.Contains("FD")) { FullPath = @"/FD/Excel/EmpDouble.xls"; } dble.TableName = "data"; WorkbookDesigner designer = new WorkbookDesigner(); string xlsMdlPath = Server.MapPath(FullPath); designer.Open(xlsMdlPath); designer.SetDataSource(dble); designer.Process(); Aspose.Cells.Worksheet ws = designer.Workbook.Worksheets.GetSheetByCodeName("配偶保险"); string newXls = xlsNameDouble + ".xls"; System.IO.DirectoryInfo xlspath = new System.IO.DirectoryInfo(Server.MapPath("../Excel/tempexcel")); if (xlspath.GetFiles(xlsNameChild + ".xls").Length <= 0) { ExcelHelper.deletefile(xlspath); } designer.Save(Server.MapPath("../Excel/tempexcel") + "\\" + newXls, FileFormatType.Excel2003); if (!string.IsNullOrEmpty(fileName)) { fileName += "|" + "/Excel/tempexcel/" + newXls; } else { fileName += "/Excel/tempexcel/" + newXls; } } this.PageState.Add("fileName", fileName); } else if (WelfareType == "double") { string FullPath = @"/Excel/EmpDouble.xls"; if (url.Contains("FD")) { FullPath = @"/FD/Excel/EmpDouble.xls"; } dble = DataHelper.QueryDataTable(dbleSQL); CrateExcel(dble, "配偶保险", FullPath, xlsNameDouble); } else if (WelfareType == "child") { string FullPath = @"/Excel/EmpChild.xls"; if (url.Contains("FD")) { FullPath = @"/FD/Excel/EmpChild.xls"; } child = DataHelper.QueryDataTable(childSQL); CrateExcel(child, "子女保险", FullPath, xlsNameChild); } }
// private void DoSelect() { string where = ""; CommPowerSplit ps = new CommPowerSplit(); if (!ps.IsNoticeRole(UserInfo.UserID, UserInfo.LoginName)) { where += AppealUsrAuth(); } if (SearchCriterion.Searches.Searches.Count == 0) { where += " and year(A.ApplyTime)=" + DateTime.Now.Year.ToString() + " "; } foreach (CommonSearchCriterionItem item in SearchCriterion.Searches.Searches) { if (!String.IsNullOrEmpty(item.Value.ToString()) || item.PropertyName == "Year") //默认本年度 { switch (item.PropertyName) { case "Year": string tempVal = string.IsNullOrEmpty(item.Value.ToString()) ? DateTime.Now.Year.ToString() : item.Value.ToString(); //where += " and Year='" + tempVal + "'"; where += " and year(A.ApplyTime)=" + tempVal + " "; break; case "Month": //where += " and Month=" + item.Value.ToString() + " "; where += " and month(A.ApplyTime)=" + item.Value.ToString() + " "; break; case "CompanyName": if (!string.IsNullOrEmpty(item.Value.ToString())) { where += " and CompanyName like '%" + item.Value + "%' "; } break; case "WelfareType": if (item.Value.ToString() == "child") { where += " and BeRelation='子女' "; } if (item.Value.ToString() == "double") { where += " and BeRelation='配偶' "; } break; case "WorkFlowState": if (item.Value.ToString() == "2,-1") { where += " and (WorkFlowState='2' or WorkFlowState='-1' ) "; } else { where += " and " + item.PropertyName + "='" + item.Value + "' "; } break; default: where += " and " + item.PropertyName + " like '%" + item.Value + "%' "; break; } } } string SQL = @"select year(A.ApplyTime) Year,month(A.ApplyTime) Month, A.*,C.UsrName As ChildName ,C.IDCartNo as ChildIdCart,C.Sex As ChlidSex,C.IDType from FL_Culture..UsrChildWelfare As A left join FL_Culture..UsrWelfareChildInfo As C on C.ChildWelfareId=A.Id where A.Id is not null ##Query## "; //未处理 if (type == "n") { where += " and WorkFlowState='1' "; } else//已处理 { where += " and (WorkFlowState='-1' or WorkFlowState='2' ) "; } SQL = SQL.Replace("##Query##", where); SearchCriterion.SetOrder("CreateTime", false); //var Ent = DataHelper.QueryDictList(SQL); this.PageState.Add("DataList", GetPageData(SQL, SearchCriterion)); }
private void Doselect() { string SurveyId = RequestData.Get("SurveyId") + ""; { string QuestionSQL = @"select Content val,content As Name from FL_Culture..QuestionItem where SurveyId='{0}' and QuestionType like '填写项%' order by sortindex "; QuestionSQL = string.Format(QuestionSQL, SurveyId); this.PageState.Add("QuestionItem", DataHelper.QueryDict(QuestionSQL)); } string where = ""; foreach (CommonSearchCriterionItem item in SearchCriterion.Searches.Searches) { if (!String.IsNullOrEmpty(item.Value.ToString())) { switch (item.PropertyName) { case "QuestionItem": where += " and Content like '" + item.Value.ToString() + "%' "; break; case "StartTime": where += " and CreateTime>='" + item.Value + "' "; break; case "EndTime": where += " and CreateTime<='" + (item.Value.ToString()).Replace(" 0:00:00", " 23:59:59") + "' "; break; default: where += " and " + item.PropertyName + " like '%" + item.Value + "%' "; break; } } } //权限过滤 var SEnt = SurveyQuestion.TryFind(SurveyId); if (SEnt != null && SEnt.IsFixed == "2") { CommPowerSplit PS = new CommPowerSplit(); if (PS.IsHR(UserInfo.UserID, UserInfo.LoginName) || PS.IsAdmin(UserInfo.LoginName) || PS.IsInAdminsRole(UserInfo.UserID)) { } else { UserContextInfo UC = new UserContextInfo(); where += " and D.pk_corp='" + UC.GetUserCurrentCorpId(UserInfo.UserID) + "' "; } } string SQL = @"SELECT newid() As Id, A.SurveyTitile, A.IsNoName , B.Content, C.QuestionContent ,C.UserId, C.UserName,C.CreateTime from FL_Culture..SurveyQuestion As A left join FL_Culture..QuestionItem As B on A.Id=B.SurveyId left join FL_Culture..SurveyedResult As C on C.QuestionId=B.Id left join FL_PortalHR..SysUser As D on C.UserId=D.UserId where B.QuestionType='填写项' and A.Id='{0}'"; SQL = SQL.Replace("FL_PortalHR", Global.AimPortalDB); SQL = string.Format(SQL, SurveyId); SQL += where; this.PageState.Add("DataList", GetPageData(SQL, SearchCriterion)); }
/// <summary> /// 根据条件筛选数据 /// </summary> private void QueryData() { //查询条件 string SurveyId = this.RequestData.Get("SurveyId") + ""; string CropId = this.RequestData.Get("cropId") + ""; string DeptId = this.RequestData.Get("deptId") + ""; string Year = this.RequestData.Get("year") + ""; string Sex = this.RequestData.Get("sex") + ""; string AgeSegment = RequestData.Get("age") + ""; string ationType = this.RequestData.Get("ationType") + ""; string where = string.Empty; //sex where += !string.IsNullOrEmpty(Sex) ? " and sex='" + Sex + "'" : ""; //OrgId string OrgId = string.IsNullOrEmpty(DeptId) ? CropId : DeptId; //year if (!string.IsNullOrEmpty(Year) && Year.Split('-').Length > 1) { string[] tmpArr = Year.Split('-'); // where += " and datepart(year,getdate())- datepart(year,Indutydate) between " + tmpArr[0] + " and " + tmpArr[1] + " "; where += " and datepart(year,getdate())- datepart(year,Indutydate) >= " + tmpArr[0] + " and datepart(year,getdate())- datepart(year,Indutydate) < " + tmpArr[1] + " "; } else if (!string.IsNullOrEmpty(Year)) { if (!string.IsNullOrEmpty(OrgId)) { where += " and datepart(year,getdate())- datepart(year,Indutydate) " + Year + " "; } else { where += " and datepart(year,getdate())- datepart(year,Indutydate) " + Year + " "; } } //Age segment ryxl D.def4 年龄段 if (!string.IsNullOrEmpty(AgeSegment) && AgeSegment.Split('-').Length > 1) { string[] tmpArr = AgeSegment.Split('-'); where += " and datediff(year,D.def4,getdate()) >= " + tmpArr[0] + " and datediff(year,D.def4,getdate()) < " + tmpArr[1] + " "; } else if (!string.IsNullOrEmpty(AgeSegment)) { where += " and datediff(year,D.def4,getdate()) " + AgeSegment + " "; } //-----------------condition end--------------------------------------------- //空组织 string IndutySql = string.Empty; if (string.IsNullOrEmpty(OrgId)) { IndutySql = @" select UserID from FL_PortalHR..Sysuser where Status=1 and (Outdutydate is null or Outdutydate='' ) ##Query##"; } //有年龄段 if (!string.IsNullOrEmpty(AgeSegment)) { IndutySql = @"select UserID from FL_PortalHR..Sysuser As A left join HR_OA_MiddleDB..fld_ryxx As D on A.WorkNo=D.psncode and A.Name=D.psnname where Status=1 and (A.Outdutydate is null or A.Outdutydate='' ) ##Query## "; where = where.Replace("Indutydate", "A.Indutydate"); // IndutySql = @" select UserID from FL_PortalHR..Sysuser where Status=1 and (Outdutydate is null or Outdutydate='' ) ##Query##"; } //组织部门 if (!string.IsNullOrEmpty(OrgId)) { IndutySql = @"select distinct C.UserID from ( select GroupID,Name from FL_PortalHR..SysGroup where GroupID='{0}' or Path like '%{0}%' and Status='1' ) As A left join FL_PortalHR..SysUserGroup As B on A.GroupID=B.GroupID left join FL_PortalHR..SysUser As C on C.UserID=B.UserID left join HR_OA_MiddleDB..fld_ryxx As D on D.psncode=C.WorkNo and D.pk_gw is not null and D.def3 is null where C.UserID is not null and C.Status=1 and ( C.Outdutydate is null or C.Outdutydate='') ##Query## "; IndutySql = string.Format(IndutySql, OrgId); where = where.Replace("A.Indutydate", "Indutydate"); //上面关联 A.Indutydate 年龄段 where = where.Replace("Indutydate", "C.Indutydate"); } IndutySql = IndutySql.Replace("FL_PortalHR", Global.AimPortalDB); IndutySql = IndutySql.Replace("HR_OA_MiddleDB", Global.HR_OA_MiddleDB); //权限过滤 string CorpId = string.Empty; var SEnt = SurveyQuestion.TryFind(SurveyId); if (SEnt != null && SEnt.IsFixed == "2") { CommPowerSplit PS = new CommPowerSplit(); if (PS.IsHR(UserInfo.UserID, UserInfo.LoginName) || PS.IsAdmin(UserInfo.LoginName) || PS.IsInAdminsRole(UserInfo.UserID)) { } else { UserContextInfo UC = new UserContextInfo(); where += " and pk_corp='" + UC.GetUserCurrentCorpId(UserInfo.UserID) + "' "; CorpId = UC.GetUserCurrentCorpId(UserInfo.UserID); } } IndutySql = IndutySql.Replace("##Query##", where); //------------------------end------------------------------------------- if (ationType == "GetCount") //获取人数 { string total = @"select count(1) As Qt from ( select UserID from FL_Culture..SurveyedResult where SurveyId='{0}' and UserId IN ({1}) group by UserID ) As A"; total = string.Format(total, surveyId, IndutySql); this.PageState.Add("Total", DataHelper.QueryValue(total)); } else { string sql = @"-----删除临时表 IF (OBJECT_ID('tempdb..#T1') IS NOT NULL) DROP TABLE tempdb..#T1; IF (OBJECT_ID('tempdb..#PerTbl') IS NOT NULL) DROP TABLE tempdb..#PerTbl; IF (OBJECT_ID('tempdb..#SurveyedResult') IS NOT NULL) DROP TABLE tempdb..#SurveyedResult; ---所有问题项 select A.*, B.Id As ItemId ,B.Answer,B.IsExplanation,B.SortIndex As SubIndex into #T1 from FL_Culture..QuestionItem As A left join FL_Culture..QuestionAnswerItem As B on A.SubItemId=B.QuestionItemId and A.SurveyId=B.SurveyId where A.SurveyId='{0}' ; ----筛选人数---- select * into #SurveyedResult from FL_Culture..SurveyedResult where SurveyId='{0}' and UserId IN ({1}); --计算百分比 With PerTbl As ( select distinct A.QuestionId,Items,T.Tol As total, cast ((100.* CA.total/T.Tol) as Decimal(8,2)) as Per from FL_Culture..SurveyedResult As A inner join ( ---每一题选择总次数 select QuestionId, sum(Tol) As Tol from #SurveyedResult As A Cross Apply( select Count(*) As Tol from FL_Culture..f_splitstr(A.QuestionItemId,',') ) As CA left join FL_PortalHR..SysUser As B on A.UserId=B.UserID where SurveyId='{0}' ##FIX## group by QuestionId )As T on A.QuestionId=T.QuestionId cross Apply( --选项的次数 select distinct QuestionId,Items, count(*) over (partition by Items) As total from #SurveyedResult As Tt cross apply ( select F1 AS Items from FL_Culture.dbo.f_splitstr( Tt.QuestionItemId,',') ) As CA left join FL_PortalHR..SysUser As B on Tt.UserId=B.UserID where SurveyId='{0}' and Tt.QuestionId= A.QuestionId ##FIX## ) AS CA ) select * into #PerTbl from PerTbl; ---组合结果 With Final As ( select distinct T1.*, isnull(T2.total,0) As Total ,isnull(T2.Per,0.00) As Per from #T1 AS T1 left join #PerTbl As T2 on T1.itemId=T2.Items ), Final1 As ( select Id As QuestionId ,Content, QuestionType,IsMustAnswer,IsComment, Case when ImgIds is not null and ImgIds<>'' then 'Y' else 'N' end As HasImg, Answer+'|'+cast(Per as varchar(10)) +'|'+IsExplanation+'|'+ItemId As Integ, Total As Qty,SortIndex,SubIndex from Final ) ---合并item项 select distinct G1.QuestionId,Content,QuestionType,IsMustAnswer,IsComment,HasImg,SortIndex,Qty, STUFF(( select '$'+ Integ As [text()] from Final1 As G2 where G2.QuestionId=G1.QuestionId order by SubIndex for xml path('')), 1, 1, '') AS ItemSet from Final1 As G1 order by SortIndex" ; if (SEnt != null && SEnt.IsFixed == "2") { sql = sql.Replace("##FIX##", " and B.pk_corp=" + CorpId + " "); } else { sql = sql.Replace("##FIX##", ""); } sql = sql.Replace("FL_PortalHR", Global.AimPortalDB); sql = string.Format(sql, SurveyId, IndutySql); DataTable Dt = DataHelper.QueryDataTable(sql); Dt = GetTreeData(Dt); this.PageState.Add("DataList", Dt); } }