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);
}
}
private void GetGuid()
{
SurveyQuestion ent = new SurveyQuestion();
ent.IsFixed = "1";
UserContextInfo UC = new UserContextInfo();
ent.CompanyId = UC.GetUserCurrentCorpId(UserInfo.UserID);//判断公司登陆
string sql = @"select top 1 Name from SysGroup where GroupId='{0}' ";
sql = string.Format(sql, ent.CompanyId);
object obj = DataHelper.QueryValue(sql);
if (obj != null)
{
ent.CompanyName = obj.ToString();
}
ent.State = "2"; //默认停用
ent.DoCreate();
this.PageState.Add("Guid", ent.Id);
}
/// <summary>
/// 审批人验证 审批人必须pk_corp在该公司才能审批
/// </summary>
/// <returns>SQL string</returns>
private string AppealUsrAuth()
{
string CorpId = "";
// 判断公司登陆
UserContextInfo UC = new UserContextInfo();
CorpId = UC.GetUserCurrentCorpId(UserInfo.UserID);
//判断是否为员工旅游审批人
string SQL = @"select top 1 TravelWelfareId,TravelWelfareName from FL_Culture..SysApproveConfig where DeptId is null and CompanyId='{0}'";
SQL = string.Format(SQL, CorpId);
DataTable Dt = DataHelper.QueryDataTable(SQL);
if (Dt.Rows.Count > 0)
{
if (Dt.Rows[0]["TravelWelfareId"] + "" == UserInfo.UserID)
{
return(" and CompanyId='" + CorpId + "' "); //*
}
else
{
return(" and 1<>1 ");
}
}
else
{
return(" and 1<>1 ");
}
}
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);
}
/// <summary>
/// 查询
/// </summary>
private void DoSelect()
{
string where = string.Empty;
foreach (CommonSearchCriterionItem item in SearchCriterion.Searches.Searches)
{
if (!String.IsNullOrEmpty(item.Value.ToString()))
{
switch (item.PropertyName)
{
case "CreateTime":
where += " year(CreateTime)=" + item.Value + " ";
break;
}
}
}
SearchCriterion.RemoveSearch("CreateTime");
SearchCriterion.SetOrder(TravelMoneyConfig.Prop_Corp); //公司
SearchCriterion.SetOrder(TravelMoneyConfig.Prop_Indutydate, true); //日期
CommPowerSplit PS = new CommPowerSplit();
SysUser UsrEnt = SysUser.Find(UserInfo.UserID);
if (PS.TraveMoneyConfig(UserInfo.UserID, UserInfo.LoginName)) //总部HR权限 HR1
{
if (!string.IsNullOrEmpty(where))
{
ents = TravelMoneyConfig.FindAll(SearchCriterion, Expression.Sql(where));
this.PageState.Add("TravelMoneyConfigList", ents);
}
else
{
ents = TravelMoneyConfig.FindAll(SearchCriterion);
this.PageState.Add("TravelMoneyConfigList", ents);
}
}
else
{
//公司权限
UserContextInfo UC = new UserContextInfo();
SearchCriterion.SetSearch(TravelMoneyConfig.Prop_Corp, UC.GetUserCurrentCorpId(UserInfo.UserID));
if (!string.IsNullOrEmpty(where))
{
ents = TravelMoneyConfig.FindAll(SearchCriterion, Expression.Sql(where));
this.PageState.Add("TravelMoneyConfigList", ents);
}
else
{
ents = TravelMoneyConfig.FindAll(SearchCriterion);
this.PageState.Add("TravelMoneyConfigList", ents);
}
}
}
string type = String.Empty; // 对象类型
#endregion
protected void Page_Load(object sender, EventArgs e)
{
op = RequestData.Get <string>("op");
id = RequestData.Get <string>("id");
type = RequestData.Get <string>("type");
string NoticeWay = RequestData.Get("NoticeWay") + "";
UseWelfareNote ent = null;
switch (this.RequestAction)
{
case RequestActionEnum.Update:
ent = this.GetMergedData <UseWelfareNote>();
ent.NoticeWay = NoticeWay;
ent.DoUpdate();
break;
case RequestActionEnum.Insert:
case RequestActionEnum.Create:
ent = this.GetPostedData <UseWelfareNote>();
ent.NoticeWay = NoticeWay;
ent.State = "0"; //0 创建 1 发起 2 撤销
ent.CreateTime = DateTime.Now;
// 判断公司登陆
string CorpIds = string.Empty;
UserContextInfo UC = new UserContextInfo();
CorpIds = UC.GetUserCurrentCorpId(UserInfo.UserID);
ent.CreateCorp = CorpIds;
ent.DoCreate();
break;
default:
if (RequestActionString == "GetNextUsers")
{
GetNextUsers();
}
else if (RequestActionString == "submitfinish")
{
SubmitFinish();
}
else
{
Doselect();
}
break;
}
}
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 CreateGuid()
{
SurveyQuestion ent = new SurveyQuestion();
var Et = SysUser.Find(UserInfo.UserID);
ent.IsFixed = "0"; //0 一般调查问卷
UserContextInfo UC = new UserContextInfo();
ent.CompanyId = UC.GetUserCurrentCorpId(UserInfo.UserID); //判断公司登陆
//部门
string DeptSQL = @"select A.UserID,A.WorkNo,A.Name,B.GroupID as CropId,B.Name as CropName,
C.GroupID as DeptId,C.Name as DeptName
from FL_PortalHR..SysUser As A
left join FL_PortalHR..SysGroup As B
on A.Pk_corp=B.GroupID
left join FL_PortalHR..SysGroup As C
on A.Pk_deptdoc=C.GroupID
where UserID='{0}' and A.pk_corp='{1}' ";
DeptSQL = DeptSQL.Replace("FL_PortalHR", Global.AimPortalDB);
DeptSQL = string.Format(DeptSQL, UserInfo.UserID, Et.Pk_corp);
DataTable dt = DataHelper.QueryDataTable(DeptSQL);
if (dt.Rows.Count > 0)
{
ent.CompanyId = dt.Rows[0]["CropId"].ToString();
ent.CompanyName = dt.Rows[0]["CropName"].ToString();
ent.DeptId = dt.Rows[0]["DeptId"].ToString();
ent.DeptName = dt.Rows[0]["DeptName"].ToString();
}
else
{
ent.CompanyName = DataHelper.QueryValue("select * from sysgroup where GroupId='" + ent.CompanyId + "'") + "";
}
ent.DoCreate();
this.PageState.Add("Guid", ent.Id);
}
/// <summary>
/// 查询
/// </summary>
private void DoSelect()
{
var UserEnt = SysUser.Find(UserInfo.UserID);
string CorpIds = string.Empty;
CommPowerSplit ps = new CommPowerSplit();
if (ps.IsNoticeRole(UserInfo.UserID, UserInfo.LoginName))
{
ents = UseWelfareNote.FindAll(SearchCriterion);
}
else
{
// 判断公司登陆
UserContextInfo UC = new UserContextInfo();
CorpIds = UC.GetUserCurrentCorpId(UserInfo.UserID);
SearchCriterion.SetSearch("CreateCorp", CorpIds);
ents = UseWelfareNote.FindAll(SearchCriterion);
}
this.PageState.Add("UseWelfareNoteList", ents);
}
/// <summary>
/// 审批人配置判断
/// </summary>
private void GetApproveUser()
{
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) + "";
// 判断公司登陆
string CorpIds = string.Empty;
UserContextInfo UC = new UserContextInfo();
CorpIds = UC.GetUserCurrentCorpId(UserInfo.UserID);
//选取配置最近的配置 HR经理
SQL = @"select top 1 HRManagerId As UserID,HRManagerName As Name ,
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 HRManagerId is not null order by SortIndex";
SQL = string.Format(SQL, CorpIds, DeptPathStr);
DataTable AppUsrDt = DataHelper.QueryDataTable(SQL);
string status = AppUsrDt.Rows.Count > 0 ? "1" : "0";
this.PageState.Add("Status", status);
}
/// <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);
}
}
/// <summary>
/// 生成状态检查
/// </summary>
private void CreateCheck()
{
string CorpId = string.Empty;
UserContextInfo UC = new UserContextInfo();
CorpId = UC.GetUserCurrentCorpId(UserInfo.UserID);
string sql = string.Empty;
string CheckStr = "C|" + CorpId + "_" + UserInfo.UserID + ""; //生成标志
sql = @"select sum(T) As T from
(
select count(*) As T from FL_Culture..TravelMoneyConfig where year(createtime)={0} and Ext1='{1}'
union all
select count(*) As T from FL_Culture..TravelMoneyConfig where year(createtime)={0} and Corp='{2}'
) As T";
sql = string.Format(sql, DateTime.Now.Year, CheckStr, CorpId);
try
{
int a = DataHelper.QueryValue <int>(sql);
if (a > 0)
{
this.PageState.Add("staus", "0");
}
else
{
this.PageState.Add("staus", "1");
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
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 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 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>
/// check 审批对象 1|||总经理|UserID|UserName
/// </summary>
private string CheckFL()
{
string data = string.Empty;
if (String.IsNullOrEmpty(SurveyId))
{
return("");
}
string SQL = @"select A.WorkFlowName, B.MustCheckFlow,B.ApproveRoleId,B.ApproveRoleName from FL_Culture..SurveyQuestion As A
left join FL_Culture..SurveyType As B
on B.Id=A.SurveyTypeId where A.Id='{0}'";
SQL = string.Format(SQL, SurveyId);
//是否审批(1)|WorkFlowName|ApproveRoleId|ApproveRoleName 1|||总经理|UserID|UserName
DataTable dt = DataHelper.QueryDataTable(SQL);
if (dt.Rows.Count <= 0)
{
return("");
}
data = dt.Rows[0]["MustCheckFlow"].ToString() + "|" + dt.Rows[0]["WorkFlowName"].ToString();
//关联审批角色人员 ApproveRoleName
if (!string.IsNullOrEmpty(dt.Rows[0]["ApproveRoleName"] + ""))
{
//data += "|" + dt.Rows[0]["ApproveRoleId"] + "|" + dt.Rows[0]["ApproveRoleName"];
// //获取人员部门
// var UsrEnt = SysUser.Find(UserInfo.UserID);
// 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) + "";
// //先查看是否有配置,问卷审批人ManagerName 这是有排序
// SQL = @"select top 1 ManagerId As UserID,ManagerName As Name ,
// 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 ManagerId is not null and ManagerId<>''
// order by SortIndex";
// SQL = string.Format(SQL, UsrEnt.Pk_corp, DeptPathStr);
// DataTable AppUsrDt = DataHelper.QueryDataTable(SQL);
// StringBuilder StrUserID = new StringBuilder();
// StringBuilder StrUserName = new StringBuilder();
// //no config 模糊匹配
// if (AppUsrDt.Rows.Count <= 0)
// {
// SQL = @"select distinct D.UserID,D.WorkNo,D.Name from HR_OA_MiddleDB..fld_ryxx As A
// left join HR_OA_MiddleDB..fld_gw As B
// on A.Pk_corp=B.pk_corp --or A.pk_gw=B.pk_fld_gw
// left join HR_OA_MiddleDB..fld_ryxx As C
// on C.pk_gw=B.pk_jobcode
// left join FL_PortalHR..SysUser As D
// on D.WorkNo=C.psncode
// where A.psncode='{0}' and B.jobName like '{1}' and C.id is not null ";
// SQL = SQL.Replace("HR_OA_MiddleDB", Global.HR_OA_MiddleDB);
// SQL = string.Format(SQL, UsrEnt.WorkNo, dt.Rows[0]["ApproveRoleName"]);
// AppUsrDt = DataHelper.QueryDataTable(SQL);
// }
//-----------------------------------change by WGM 10/24----
StringBuilder StrUserID = new StringBuilder();
StringBuilder StrUserName = new StringBuilder();
//审批角色名称
string RoleName = dt.Rows[0]["ApproveRoleName"] + "";
//获取公司
string Corp = string.Empty;
UserContextInfo UC = new UserContextInfo();
Corp = UC.GetUserCurrentCorpId(UserInfo.UserID); //判断公司登陆
SQL = @"select top 1 UserNames from FL_Culture..ManagementGroup As A
inner join FL_Culture..ManagementInfo AS B
on A.Id=B.PId
where A.MName='{0}' and B.CompanyId='{1}' order by B.CreateTime desc ";
SQL = string.Format(SQL, RoleName, Corp);
string ApproveRoleName = DataHelper.QueryValue(SQL) + "";
//data += "|" + dt.Rows[0]["ApproveRoleId"] + "|" + ApproveRoleName;
data += "|" + dt.Rows[0]["ApproveRoleId"] + "|" + dt.Rows[0]["ApproveRoleName"];
// //职位关联人员
// SQL = @" declare @Gw nvarchar(1000)
// select top 1 @Gw=UserNames
// from FL_Culture..ManagementGroup As A
// inner join FL_Culture..ManagementInfo AS B
// on A.Id=B.PId
// where A.MName='{0}' and B.CompanyId='{1}' ;
// select * from FL_PortalHR..SysUser where WorkNo in
// (
// select top 2 B.psncode from HR_OA_MiddleDB..fld_gw AS A
// left join HR_OA_MiddleDB..fld_ryxx AS B
// on A.pk_jobcode=B.pk_gw
// where A.isabort='N' and (B.outdutydate is null or B.outdutydate='' )
// and
// B.pk_corp='{1}' and (@Gw like '%'+ jobname+'%' or jobname like '%'+@Gw+'%' )
// )";
// SQL = string.Format(SQL, RoleName, Corp);
// SQL = SQL.Replace("HR_OA_MiddleDB", Global.HR_OA_MiddleDB);
// SQL = SQL.Replace("FL_PortalHR", Global.AimPortalDB);
// DataTable AppUsrDt = DataHelper.QueryDataTable(SQL);
// ////拼接审批人员
// for (int i = 0; i < AppUsrDt.Rows.Count; i++)
// {
// if (i > 0)
// {
// StrUserID.Append(",");
// StrUserName.Append(",");
// }
// StrUserID.Append(AppUsrDt.Rows[i]["UserID"].ToString());
// StrUserName.Append(AppUsrDt.Rows[i]["Name"].ToString());
// }
// data += "|" + StrUserID.ToString() + "|" + StrUserName.ToString();
//data = "1|||总经理|UserID|UserName";
//--------------------------------------------------------------------------
//this.PageState.Add("ChState", data);
}
return(data);
}
/// <summary>
/// 查询
/// </summary>
private void DoSelect()
{
string SurveyId = this.RequestData.Get("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;
}
}
}
//权限过滤
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 C.GroupId='" + UC.GetUserCurrentCorpId(UserInfo.UserID) + "' ";
}
string sql = @"select A.Id,A.SurveyId,A.SurveyName,A.WorkNo,A.SurveyedUserId,A.SurveyedUserName,A.CreateTime,
A.TotalScore,A.ScoreInfo,
B.WorkNo As WorkNo1 , C.GroupID As CropId ,C.Name As CropName,D.GroupID As DeptId,D.Name AS DeptName,
E.IsNoName
from FL_Culture..SurveyCommitHistory As A
left join FL_PortalHR..SysUser As B
on A.SurveyedUserId=B.UserID or A.WorkNo=B.WorkNo
left join FL_PortalHR..SysGroup As C
on C.GroupID=B.Pk_corp
left join FL_PortalHR..SysGroup As D
on D.GroupID =B.Pk_deptdoc
left join FL_Culture..SurveyQuestion As E
on A.SurveyId=E.Id
where A.SurveyId='{0}' ";
sql = sql.Replace("FL_PortalHR", Global.AimPortalDB);
sql += where;
sql = string.Format(sql, SurveyId);
//SearchCriterion.SetSearch("SurveyId", SurveyId);
//ents = SurveyCommitHistory.FindAll(SearchCriterion);
//this.PageState.Add("SurveyCommitHistoryList", ents);
this.PageState.Add("DataList", GetPageData(sql, SearchCriterion));
}
private void CheckUsr()
{
string CheckUsr = RequestData.Get("UserIds") + "";
string SurveyId = RequestData.Get("SurveyId") + "";
string SQL = @"select top 1 A.WorkFlowName, B.MustCheckFlow,B.ApproveRoleId,B.ApproveRoleName from FL_Culture..SurveyQuestion As A
left join FL_Culture..SurveyType As B
on B.Id=A.SurveyTypeId where A.Id='{0}' ";
SQL = string.Format(SQL, SurveyId);
DataTable Dt = DataHelper.QueryDataTable(SQL);
string rolename = string.Empty;
if (DataHelper.QueryDataTable(SQL).Rows.Count > 0)
{
rolename = Dt.Rows[0]["ApproveRoleName"] + "";
}
//获取公司
string Corp = string.Empty;
UserContextInfo UC = new UserContextInfo();
Corp = UC.GetUserCurrentCorpId(UserInfo.UserID);//判断公司登陆
SQL = @"select top 1 UserNames from FL_Culture..ManagementGroup As A
inner join FL_Culture..ManagementInfo AS B
on A.Id=B.PId
where A.MName='{0}' and B.CompanyId='{1}' order by B.CreateTime desc ";
SQL = string.Format(SQL, rolename, Corp);
string configRoleName = DataHelper.QueryValue(SQL) + "";//审批职位
SQL = @"select * from sysuser where charindex(UserId, '{0}')>0";
SQL = string.Format(SQL, CheckUsr);
DataTable UDt = DataHelper.QueryDataTable(SQL);
bool Has = false;
for (int i = 0; i < UDt.Rows.Count; i++)
{
string sql = @"select B.jobname from HR_OA_MiddleDB..fld_ryxx as A
left join HR_OA_MiddleDB..fld_gw AS B
on A.pk_gw=B.pk_jobcode where A.psncode='{0}'
and (outdutydate is null or outdutydate='')
and B.pk_corp='{1}' ";
sql = string.Format(sql, UDt.Rows[i]["WorkNo"] + "", Corp);
sql = sql.Replace("HR_OA_MiddleDB", Global.HR_OA_MiddleDB);
DataTable tempdt = DataHelper.QueryDataTable(sql);
for (int j = 0; j < tempdt.Rows.Count; j++)
{
if (!string.IsNullOrEmpty(tempdt.Rows[j]["jobname"] + "") && configRoleName.Contains(tempdt.Rows[j]["jobname"] + ""))
{
string[] tmArr = configRoleName.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
for (int k = 0; k < tmArr.Length; k++)
{
if (tmArr[k] == tempdt.Rows[j]["jobname"] + "")
{
Has = true;
break;
}
}
}
if (Has)
{
break;
}
}
if (Has)
{
break;
}
}
this.PageState.Add("State", Has ? "1" : "0");
}
/// <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);
//}
}
/// <summary>
/// 获取人员
/// </summary>
private void GetNextUsers()
{
string CurrentNode = RequestData.Get <string>("taskName");
string nextName = RequestData.Get <string>("nextName");
string UserId = string.Empty, UserName = string.Empty;
string CorpIds = string.Empty; //公司ID
// according id find create user
SysUser UsrEnt = null;
UsrAppealList AppEnt = UsrAppealList.TryFind(id);
if (AppEnt != null)
{
UsrEnt = SysUser.Find(AppEnt.UserId);
CorpIds = UsrEnt.Pk_corp;
}
else
{
// 判断公司登陆
UserContextInfo UC = new UserContextInfo();
CorpIds = UC.GetUserCurrentCorpId(UserInfo.UserID);
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;
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 order by SortIndex";
SQL = string.Format(SQL, CorpIds, DeptPathStr);
DataTable dt = DataHelper.QueryDataTable(SQL);
if (dt == null || dt.Rows.Count == 0)
{
return;
}
//HR专员-->HR经理(一级组织负责人)-->总部HR专员--->总部HR经理
if (CurrentNode == "AppealUsr" && string.IsNullOrEmpty(nextName))//申诉人
{
if (!string.IsNullOrEmpty(id))
{
var Ent = UsrAppealList.Find(id);
if (ent != null)
{
UserId = Ent.UserId;
UserName = Ent.UserName;
}
}
}
else if (CurrentNode == "AppealUsr" && nextName == "提交") //申诉人-->HR专员
{
UserId = dt.Rows[0]["HRUsrId"].ToString();
UserName = dt.Rows[0]["HRUserName"].ToString();
}
else if (CurrentNode == "AppealUsr" && nextName == "上诉") //申诉人-->总部HR专员
{
//需跳环节
// string sql = @"select * from Task where PatIndex('%{0}%',EFormName)>0 and Status='4'
// and ApprovalNodeName='总部HR经理' order by FinishTime asc";
// sql = string.Format(sql, id);
// DataTable rowDt = DataHelper.QueryDataTable(sql);
// if (rowDt.Rows.Count > 0) //申诉人-->总部HR经理-->HR总监
// {
// UserId = dt.Rows[0]["HQHRMajorId"].ToString();
// UserName = dt.Rows[0]["HQHRMajorName"].ToString();
// }
// else
// {
UserId = dt.Rows[0]["HQHRUserId"].ToString();
UserName = dt.Rows[0]["HQHRUserName"].ToString();
// }
}
else if (CurrentNode == "HRUsr" && string.IsNullOrEmpty(nextName)) //申诉人-->HR专员
{
UserId = dt.Rows[0]["HRUsrId"].ToString();
UserName = dt.Rows[0]["HRUserName"].ToString();
}
else if (CurrentNode == "HRUsr" && nextName == "提交上一级") //Hr专员--> HR经理
{
UserId = dt.Rows[0]["HRManagerId"].ToString();
UserName = dt.Rows[0]["HRManagerName"].ToString();
}
else if (CurrentNode == "HQHRUser" && nextName == "提交上一级") //总部HR专员--> 总部HR经理
{
UserId = dt.Rows[0]["HQHRManagerId"].ToString();
UserName = dt.Rows[0]["HQHRManagerName"].ToString();
}
else if (CurrentNode == "HQHRManager" && nextName == "提交上一级") // 总部HR经理---->总部HR总监
{
UserId = dt.Rows[0]["HQHRMajorId"].ToString();
UserName = dt.Rows[0]["HQHRMajorName"].ToString();
}
//else if (CurrentNode == "CompanyLeader") //一级组织负责人
//{
// UserId = dt.Rows[0]["CompanyLeaderId"].ToString();
// UserName = dt.Rows[0]["CompanyLeaderName"].ToString();
PageState.Add("NextUsers", new { nextUserId = UserId, nextUserName = UserName });
}
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));
}
/// <summary>
/// 生成旅游金额
/// </summary>
private void CreateMoney()
{
string CorpId = string.Empty;
UserContextInfo UC = new UserContextInfo();
CorpId = UC.GetUserCurrentCorpId(UserInfo.UserID);
EasyDictionary LimitDate = SysEnumeration.GetEnumDict("TravelLimitDate");
string limitDateStr = string.Empty;
if ((LimitDate["LimitDate"] + "").ToUpper() == "2L") //2 月最后一天
{
limitDateStr = "'" + GetLastDayOfMonth(DateTime.Now.Year, 2).ToString("yyyy-MM-dd") + "'";
}
else
{
string prefix = LimitDate["LimitDate"] + "";
limitDateStr = "'" + DateTime.Now.Year + "-" + prefix + "'";
}
EasyDictionary Dic = SysEnumeration.GetEnumDict("BaseMoney");
string One = Dic[">1<5"] + "";
string Two = Dic[">=5<10"] + "";
string Three = Dic[">=10<15"] + "";
string Four = Dic[">=15<20"] + "";
EasyDictionary DicBase = SysEnumeration.GetEnumDict("WorkYearMoney");
string BaseMoney_One = DicBase["<1年"] + "";
string BaseMoney_two = DicBase[">1年"] + "";
string SQL = @"select A.* ,
case
when 1<datediff(year, Indutydate,getdate())and datediff(year,Indutydate,getdate())<5 then {0}
when 5<=datediff(year,Indutydate,getdate())and datediff(year,Indutydate,getdate())<10 then {1}
when 10<=datediff(year,Indutydate,getdate())and datediff(year,Indutydate,getdate())<15 then {2}
when 15<=datediff(year,Indutydate,getdate())and datediff(year,Indutydate,getdate())<20 then {3}
else 0
end As YearMoney,
case
when charindex('正式工',B.psnclassname)>0 and year(Indutydate)>1 then {5}
else {4}
end As BaseMoney ,C.GroupID as CorpId,C.Name As CorpName,D.GroupID As DeptId,D.Name As DeptName
from FL_PortalHR..sysuser As A
left join HR_OA_MiddleDB..fld_rylb As B
on B.pk_fld_rylb=A.Pk_rylb
left join FL_PortalHR..SysGroup As C
on C.GroupID=A.PK_Corp
left join FL_PortalHR..SysGroup As D
on D.GroupID=A.Pk_deptdoc
where
(OutdutyDate='' or OutdutyDate is null) and A.Status=1 and A.Indutydate<>'' ##QUERY## ";
SQL = string.Format(SQL, One, Two, Three, Four, BaseMoney_One, BaseMoney_two);
SQL = SQL.Replace("FL_PortalHR", Global.AimPortalDB);
SQL = SQL.Replace("HR_OA_MiddleDB", Global.HR_OA_MiddleDB);
SQL = SQL.Replace("getdate()", limitDateStr);
string where = string.Empty;
CommPowerSplit PS = new CommPowerSplit();
if (PS.IsAdmin(UserInfo.LoginName) || PS.IsHR(UserInfo.UserID, UserInfo.LoginName))
{
}
else
{
where += " and A.Pk_corp='" + CorpId + "' ";
}
SQL = SQL.Replace("##QUERY##", where);
DataTable UDt = DataHelper.QueryDataTable(SQL);
for (int i = 0; i < UDt.Rows.Count; i++)
{
try
{
TravelMoneyConfig TM = new TravelMoneyConfig();
string YearMoney = UDt.Rows[i]["YearMoney"] + "", BaseMoney = UDt.Rows[i]["BaseMoney"] + "";
if (!string.IsNullOrEmpty(YearMoney))
{
decimal M = 0.0m;
if (decimal.TryParse(YearMoney, out M))
{
TM.Money = M;
}
}
else
{
TM.Money = 0;
}
//基本津贴
if (!string.IsNullOrEmpty(BaseMoney))
{
decimal M = 0.0m;
if (decimal.TryParse(BaseMoney, out M))
{
TM.BaseMoney = M;
}
}
else
{
TM.BaseMoney = 0;
}
TM.UserId = UDt.Rows[i]["UserID"] + "";
TM.UserName = UDt.Rows[i]["Name"] + "";
TM.WorkNo = UDt.Rows[i]["WorkNo"] + "";
DateTime DTime = new DateTime();
if (DateTime.TryParse(UDt.Rows[i]["Indutydate"] + "", out DTime))
{
TM.Indutydate = DTime;
}
TM.HaveUsed = "N";
TM.Corp = UDt.Rows[i]["CorpId"] + "";
TM.CorpName = UDt.Rows[i]["CorpName"] + "";
TM.DeptId = UDt.Rows[i]["DeptId"] + "";
TM.DeptName = UDt.Rows[i]["DeptName"] + "";
TM.CreateTime = DateTime.Now;
TM.Ext1 = "C|" + CorpId + "_" + UserInfo.UserID + ""; //生成标志
TM.Create();
}
catch { }
}
this.PageState.Add("State", "1");
}
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);
}
}
