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