/// <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()
{
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 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));
}
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 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 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 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);
}
}
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 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));
}
/// <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);
//}
}
/// <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);
}
}