public object getPrintTableData(exportPunchLogXlsxPara exportPara)
{
string sWebRootFolder = _hostingEnvironment.WebRootPath;
string subFolder = definePara.getPunchExcelSubFolder();
string fileFolderPath = Path.Combine(sWebRootFolder, subFolder);
if (!Directory.Exists(fileFolderPath)) //需用完整目錄來檢查
{
Directory.CreateDirectory(fileFolderPath);
}
string sFileName = $"{loginID},{Guid.NewGuid()}.xlsx";
FileInfo file = new FileInfo(Path.Combine(fileFolderPath, sFileName));
exportPara.crossDepart = ((ruleVal & ruleCode.allEmployeeList) > 0)? true: false;
exportPara.loginID = (int)loginID;
var tableData = xlsxFn.createXlsxPunchLog(file, exportPara);
if (tableData != null)
{
_session.SetString("punchLogXlsxFile", Path.Combine(subFolder, sFileName)); //若下載是從根目錄開始算
_session.SetInt32("hasPunchXlsxFile", 1);
}
return(rAjaxResult(tableData));
}
public List <OvertimeApply> GetRequestOvertimeApplyLog(exportPunchLogXlsxPara qPara, int id)
{
var query = _DbContext.overtimeApply.Where(
b => b.accountID == id && b.applyStatus == 1 &&
b.workDate >= qPara.sDate && b.workDate <= qPara.eDate)
.OrderBy(b => b.workDate);
return(query.ToList());
}
public List <PunchCardLog> GetRequestPunchLog(exportPunchLogXlsxPara qPara, int id)
{
var query = _DbContext.punchcardlogs.Where(
b => b.accountID == id &&
b.logDate >= qPara.sDate && b.logDate <= qPara.eDate)
.OrderBy(b => b.logDate);
return(query.ToList());
}
public List <LeaveOfficeApply> GetApplyLeaveLogs(exportPunchLogXlsxPara qPara, int id)
{
var query = _DbContext.leaveofficeapplys.Where(a =>
a.accountID == id && a.applyStatus == 1 &&
((a.startTime >= qPara.sDate && a.startTime < qPara.eDate) ||
(a.endTime > qPara.sDate && a.endTime <= qPara.eDate)
)
);
return(query.ToList());
}
public exportXlsxData GetNormalDetail_Day(exportPunchLogXlsxPara qPara)
{
var query = from a in _DbContext.accounts
join b in _DbContext.departments on a.departmentID equals b.ID into noDepart
from bb in noDepart.DefaultIfEmpty()
join c in _DbContext.worktimerules on a.timeRuleID equals c.ID
where a.ID == qPara.accID
select new exportXlsxData {
accID = a.ID, name = a.userName,
workClass = (c.name + (c.type == 1? "(排休制)":"(固定制)")),
startTime = c.startTime, endTime = c.endTime,
department = (bb == null? noDepartStr:bb.department),
position = (bb == null? "":bb.position)
};
return(query.FirstOrDefault());
}
public List <exportXlsxData> GetNormalDetail_Month(exportPunchLogXlsxPara qPara)
{
var query = new List <exportXlsxData>();
if (!qPara.crossDepart)
{
var baseQu = (from a in _DbContext.accounts
join b in _DbContext.employeeprincipals on a.ID equals b.employeeID
join c in _DbContext.worktimerules on a.timeRuleID equals c.ID
where b.principalID == qPara.loginID || b.principalAgentID == qPara.loginID
select new { a, b, c }).ToList();
if (qPara.departName == noDepartStr)
{
query = baseQu.Where(d => d.a.departmentID == 0)
.Select(d => new exportXlsxData {
accID = d.a.ID, name = d.a.userName,
workClass = (d.c.name + (d.c.type == 1? "(排休制)":"(固定制)")),
department = noDepartStr, position = ""
}).ToList();
}
else //XX部門 所有部門
{
var myData = (from a in _DbContext.accounts
join b in _DbContext.departments on a.departmentID equals b.ID into noDepart
from bb in noDepart.DefaultIfEmpty()
join c in _DbContext.worktimerules on a.timeRuleID equals c.ID
where a.ID == qPara.loginID
select new {
acc = a, depart = bb, wt = c
}).ToList();
if (qPara.departName != "所有部門")
{
var tmp = (from d in baseQu
join e in _DbContext.departments on d.a.departmentID equals e.ID
where (e.department.Contains(qPara.departName))
select new{ acc = d.a, depart = e, wt = d.c }).ToList();
myData = myData.Where(k => k.depart.department.Contains(qPara.departName)).Union(tmp).ToList();
}
else
{
var tmp = (from d in baseQu
join e in _DbContext.departments on d.a.departmentID equals e.ID into depart
from f in depart.DefaultIfEmpty()
select new { acc = d.a, depart = f, wt = d.c }).ToList();
myData = myData.Union(tmp).ToList();
}
query = myData.Select(k => new exportXlsxData {
accID = k.acc.ID, name = k.acc.userName,
workClass = (k.wt.name + (k.wt.type == 1? "(排休制)":"(固定制)")),
department = (k.depart == null? noDepartStr:k.depart.department),
position = (k.depart == null? "":k.depart.position)
}).ToList();
}
}
else //所有部門
{
if (qPara.departName == noDepartStr)
{
query = (from a in _DbContext.accounts
join b in _DbContext.worktimerules on a.timeRuleID equals b.ID
where a.departmentID == 0 && a.accLV != definePara.getDIMALV()
select new exportXlsxData {
accID = a.ID, name = a.userName,
workClass = (b.name + (b.type == 1? "(排休制)":"(固定制)")),
department = noDepartStr, position = ""
}).ToList();
}
else
{
var tmp = (from a in _DbContext.accounts
join b in _DbContext.departments on a.departmentID equals b.ID into noDepart
from bb in noDepart.DefaultIfEmpty()
join c in _DbContext.worktimerules on a.timeRuleID equals c.ID
select new { acc = a, depart = bb, wt = c }).ToList();
if (qPara.departName != "所有部門")
{
tmp = tmp.Where(k => k.depart != null && k.depart.department.Contains(qPara.departName)).ToList();
}
query = tmp.Select(k => new exportXlsxData {
accID = k.acc.ID, name = k.acc.userName,
workClass = (k.wt.name + (k.wt.type == 1? "(排休制)":"(固定制)")),
department = (k.depart == null? noDepartStr:k.depart.department),
position = (k.depart == null? "":k.depart.position)
}).ToList();
}
}
return(query);
}
