public IActionResult KeepIndex(QryKeepListData qdata, int page = 1)
{
string ftype = qdata.KqtyFLOWTYPE;
string qtyKeepResult = qdata.KqtyKeepResult;
string qryEngId = qdata.KqtyEngCode;
List <KeepSearchListViewModel> kv = new List <KeepSearchListViewModel>();
/* Querying data. */
var kps = _context.Keeps.AsQueryable();
var keepFlows = _context.KeepFlows.AsQueryable();
var keepDtls = _context.KeepDtls.AsQueryable();
if (!string.IsNullOrEmpty(qryEngId))
{
kps = kps.Where(r => r.EngId == Convert.ToInt32(qryEngId));
}
if (!string.IsNullOrEmpty(ftype)) //流程狀態
{
switch (ftype)
{
case "未結案":
keepFlows = keepFlows.Where(kf => kf.Status == "?");
break;
case "已結案":
keepFlows = keepFlows.Where(kf => kf.Status == "2");
break;
}
}
else
{
keepFlows = keepFlows.Where(kf => kf.Status == "?" || kf.Status == "2");
}
/* If no search result. */
if (kps.Count() == 0)
{
return(PartialView("KeepList", kv.ToPagedList(1, pageSize)));
}
kps.Join(keepFlows, r => r.DocId, f => f.DocId,
(k, f) => new
{
keep = k,
flow = f
})
.Join(keepDtls, m => m.keep.DocId, d => d.DocId,
(m, d) => new
{
keep = m.keep,
flow = m.flow,
keepdtl = d
})
.Join(_context.Departments, j => j.keep.AccDpt, d => d.DptId,
(j, d) => new
{
keep = j.keep,
flow = j.flow,
keepdtl = j.keepdtl,
dpt = d
})
.ToList()
.ForEach(j => kv.Add(new KeepSearchListViewModel
{
DocType = "保養",
DocId = j.keep.DocId,
PlaceLoc = j.keep.PlaceLoc,
ApplyDpt = j.keep.DptId,
AccDpt = j.keep.AccDpt,
AccDptName = j.dpt.Name_C,
Result = (j.keepdtl.Result == null || j.keepdtl.Result == 0) ? "" : _context.KeepResults.Find(j.keepdtl.Result).Title,
InOut = j.keepdtl.InOut == "0" ? "自行" :
j.keepdtl.InOut == "1" ? "委外" :
j.keepdtl.InOut == "2" ? "租賃" :
j.keepdtl.InOut == "3" ? "保固" : "",
Memo = j.keepdtl.Memo,
Cost = j.keepdtl.Cost,
Days = DateTime.Now.Subtract(j.keep.SentDate.GetValueOrDefault()).Days,
Flg = j.flow.Status,
FlowUid = j.flow.UserId,
FlowCls = j.flow.Cls,
FlowUidName = _context.AppUsers.Find(j.flow.UserId).FullName,
Src = j.keep.Src,
SentDate = j.keep.SentDate,
EndDate = j.keepdtl.EndDate,
CloseDate = j.keepdtl.CloseDate.HasValue == true ? j.keepdtl.CloseDate.Value.Date : j.keepdtl.CloseDate,
IsCharged = j.keepdtl.IsCharged,
keepdata = j.keep
}));
/* 設備編號"有"、"無"的對應，"有"讀取table相關data，"無"只顯示申請人輸入的設備名稱 */
foreach (var item in kv)
{
if (!string.IsNullOrEmpty(item.keepdata.AssetNo))
{
var asset = _context.Assets.Where(a => a.AssetNo == item.keepdata.AssetNo).FirstOrDefault();
if (asset != null)
{
item.AssetNo = asset.AssetNo;
item.AssetName = asset.Cname;
item.Brand = asset.Brand;
item.Type = asset.Type;
}
}
else
{
item.AssetName = item.keepdata.AssetName;
}
}
/* Search KeepResults. */
if (!string.IsNullOrEmpty(qtyKeepResult))
{
kv = kv.Where(r => r.Result == _context.KeepResults.Find(Convert.ToInt32(qtyKeepResult)).Title).ToList();
}
if (kv.ToPagedList(page, pageSize).Count <= 0)
{
return(PartialView("KeepList", kv.ToPagedList(1, pageSize)));
}
return(PartialView("KeepList", kv.ToPagedList(page, pageSize)));
}
// GET: Admin/Search/KeepToExcel
public ActionResult KeepToExcel(QryKeepListData qdata)
{
string ftype = qdata.KqtyFLOWTYPE;
string qtyKeepResult = qdata.KqtyKeepResult;
string qryEngId = qdata.KqtyEngCode;
List <KeepSearchListViewModel> kv = new List <KeepSearchListViewModel>();
/* Querying data. */
var kps = _context.Keeps.AsQueryable();
var keepFlows = _context.KeepFlows.AsQueryable();
var keepDtls = _context.KeepDtls.AsQueryable();
if (!string.IsNullOrEmpty(qryEngId))
{
kps = kps.Where(r => r.EngId == Convert.ToInt32(qryEngId));
}
if (!string.IsNullOrEmpty(ftype)) //流程狀態
{
switch (ftype)
{
case "未結案":
keepFlows = keepFlows.Where(kf => kf.Status == "?");
break;
case "已結案":
keepFlows = keepFlows.Where(kf => kf.Status == "2");
break;
}
}
else
{
keepFlows = keepFlows.Where(kf => kf.Status == "?" || kf.Status == "2");
}
/* If no search result. */
if (kps.Count() == 0)
{
return(PartialView("KeepList", kv.ToPagedList(1, pageSize)));
}
kps.Join(keepFlows, r => r.DocId, f => f.DocId,
(k, f) => new
{
keep = k,
flow = f
})
.Join(keepDtls, m => m.keep.DocId, d => d.DocId,
(m, d) => new
{
keep = m.keep,
flow = m.flow,
keepdtl = d
})
.Join(_context.Departments, j => j.keep.AccDpt, d => d.DptId,
(j, d) => new
{
keep = j.keep,
flow = j.flow,
keepdtl = j.keepdtl,
dpt = d
})
.ToList()
.ForEach(j => kv.Add(new KeepSearchListViewModel
{
DocType = "保養",
DocId = j.keep.DocId,
PlaceLoc = j.keep.PlaceLoc,
ApplyDpt = j.keep.DptId,
AccDpt = j.keep.AccDpt,
AccDptName = j.dpt.Name_C,
Result = (j.keepdtl.Result == null || j.keepdtl.Result == 0) ? "" : _context.KeepResults.Find(j.keepdtl.Result).Title,
InOut = j.keepdtl.InOut == "0" ? "自行" :
j.keepdtl.InOut == "1" ? "委外" :
j.keepdtl.InOut == "2" ? "租賃" :
j.keepdtl.InOut == "3" ? "保固" : "",
Memo = j.keepdtl.Memo,
Cost = j.keepdtl.Cost,
Days = DateTime.Now.Subtract(j.keep.SentDate.GetValueOrDefault()).Days,
Flg = j.flow.Status,
FlowUid = j.flow.UserId,
FlowCls = j.flow.Cls,
FlowUidName = _context.AppUsers.Find(j.flow.UserId).FullName,
Src = j.keep.Src,
SentDate = j.keep.SentDate,
EndDate = j.keepdtl.EndDate,
CloseDate = j.keepdtl.CloseDate.HasValue == true ? j.keepdtl.CloseDate.Value.Date : j.keepdtl.CloseDate,
IsCharged = j.keepdtl.IsCharged,
keepdata = j.keep
}));
/* 設備編號"有"、"無"的對應，"有"讀取table相關data，"無"只顯示申請人輸入的設備名稱 */
foreach (var item in kv)
{
if (!string.IsNullOrEmpty(item.keepdata.AssetNo))
{
var asset = _context.Assets.Where(a => a.AssetNo == item.keepdata.AssetNo).FirstOrDefault();
if (asset != null)
{
item.AssetNo = asset.AssetNo;
item.AssetName = asset.Cname;
item.Brand = asset.Brand;
item.Type = asset.Type;
}
}
else
{
item.AssetName = item.keepdata.AssetName;
}
}
/* Search KeepResults. */
if (!string.IsNullOrEmpty(qtyKeepResult))
{
kv = kv.Where(r => r.Result == _context.KeepResults.Find(Convert.ToInt32(qtyKeepResult)).Title).ToList();
}
//ClosedXML的用法 先new一個Excel Workbook
using (XLWorkbook workbook = new XLWorkbook())
{
//取得要塞入Excel內的資料
var data = kv.Select(c => new {
c.DocId,
c.SentDate,
AccDpt = c.AccDptName + "(" + c.AccDpt + ")",
Asset = c.AssetName + "(" + c.AssetNo + ")",
c.PlaceLoc,
c.Result,
c.InOut,
c.Memo,
c.EndDate,
c.CloseDate,
c.Cost,
c.Days,
c.FlowCls,
c.FlowUidName
});
//一個workbook內至少會有一個worksheet,並將資料Insert至這個位於A1這個位置上
var ws = workbook.Worksheets.Add("sheet1", 1);
//Title
ws.Cell(1, 1).Value = "表單編號";
ws.Cell(1, 2).Value = "申請日期";
ws.Cell(1, 3).Value = "成本中心";
ws.Cell(1, 4).Value = "物品名稱(財產編號)";
ws.Cell(1, 5).Value = "放置地點";
ws.Cell(1, 6).Value = "保養狀態";
ws.Cell(1, 7).Value = "保養方式";
ws.Cell(1, 8).Value = "保養描述";
ws.Cell(1, 9).Value = "完工日期";
ws.Cell(1, 10).Value = "結案日期";
ws.Cell(1, 11).Value = "費用";
ws.Cell(1, 12).Value = "天數";
ws.Cell(1, 13).Value = "關卡";
ws.Cell(1, 14).Value = "關卡人員";
//如果是要塞入Query後的資料該資料一定要變成是data.AsEnumerable()
ws.Cell(2, 1).InsertData(data);
//因為是用Query的方式,這個地方要用串流的方式來存檔
using (MemoryStream memoryStream = new MemoryStream())
{
workbook.SaveAs(memoryStream);
//請注意 一定要加入這行,不然Excel會是空檔
memoryStream.Seek(0, SeekOrigin.Begin);
//注意Excel的ContentType,是要用這個"application/vnd.ms-excel"
string fileName = "工程師案件搜尋(保養)_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xlsx";
return(this.File(memoryStream.ToArray(), "application/vnd.ms-excel", fileName));
}
}
}
public IActionResult Index(QryKeepListData qdata)
{
string docid = qdata.KqtyDOCID;
string ano = qdata.KqtyASSETNO;
string acc = qdata.KqtyACCDPT;
string aname = qdata.KqtyASSETNAME;
string ftype = qdata.KqtyFLOWTYPE;
string dptid = qdata.KqtyDPTID;
string qtyDate1 = qdata.KqtyApplyDateFrom;
string qtyDate2 = qdata.KqtyApplyDateTo;
string qtyKeepResult = qdata.KqtyKeepResult;
string qtyIsCharged = qdata.KqtyIsCharged;
string qtyDateType = qdata.KqtyDateType;
bool searchAllDoc = qdata.KqtySearchAllDoc;
string qtyEngCode = qdata.KqtyEngCode;
string qtyTicketNo = qdata.KqtyTicketNo;
string qtyVendor = qdata.KqtyVendor;
string qtyOrderType = qdata.KqtyOrderType;
if (qtyEngCode != null)
{
searchAllDoc = true;
}
DateTime applyDateFrom = DateTime.Now;
DateTime applyDateTo = DateTime.Now;
/* Dealing search by date. */
if (qtyDate1 != null && qtyDate2 != null)// If 2 date inputs have been insert, compare 2 dates.
{
DateTime date1 = DateTime.Parse(qtyDate1);
DateTime date2 = DateTime.Parse(qtyDate2);
int result = DateTime.Compare(date1, date2);
if (result < 0)
{
applyDateFrom = date1.Date;
applyDateTo = date2.Date;
}
else if (result == 0)
{
applyDateFrom = date1.Date;
applyDateTo = date1.Date;
}
else
{
applyDateFrom = date2.Date;
applyDateTo = date1.Date;
}
}
else if (qtyDate1 == null && qtyDate2 != null)
{
applyDateFrom = DateTime.Parse(qtyDate2);
applyDateTo = DateTime.Parse(qtyDate2);
}
else if (qtyDate1 != null && qtyDate2 == null)
{
applyDateFrom = DateTime.Parse(qtyDate1);
applyDateTo = DateTime.Parse(qtyDate1);
}
List <KeepListVModel> kv = new List <KeepListVModel>();
/* Get login user. */
var ur = _userRepo.Find(u => u.UserName == this.User.Identity.Name).FirstOrDefault();
var kps = _context.Keeps.AsQueryable();
if (!string.IsNullOrEmpty(docid)) //表單編號
{
docid = docid.Trim();
kps = kps.Where(v => v.DocId == docid);
}
if (!string.IsNullOrEmpty(ano)) //財產編號
{
kps = kps.Where(v => v.AssetNo == ano);
}
if (!string.IsNullOrEmpty(dptid)) //所屬部門編號
{
kps = kps.Where(v => v.DptId == dptid);
}
if (!string.IsNullOrEmpty(acc)) //成本中心
{
kps = kps.Where(v => v.AccDpt == acc);
}
if (!string.IsNullOrEmpty(aname)) //物品名稱(關鍵字)
{
kps = kps.Where(v => v.AssetName != null)
.Where(v => v.AssetName.Contains(aname));
}
if (!string.IsNullOrEmpty(qtyTicketNo)) //發票號碼
{
qtyTicketNo = qtyTicketNo.ToUpper();
var resultDocIds = _context.KeepCosts.Include(kc => kc.TicketDtl)
.Where(kc => kc.TicketDtl.TicketDtlNo == qtyTicketNo)
.Select(kc => kc.DocId).Distinct();
kps = (from k in kps
where resultDocIds.Any(val => k.DocId.Contains(val))
select k);
}
if (!string.IsNullOrEmpty(qtyVendor)) //廠商關鍵字
{
var resultDocIds = _context.KeepCosts.Include(kc => kc.TicketDtl)
.Where(kc => kc.VendorName.Contains(qtyVendor))
.Select(kc => kc.DocId).Distinct();
kps = (from k in kps
where resultDocIds.Any(val => k.DocId.Contains(val))
select k);
}
/* Search date by DateType.(ApplyDate) */
if (string.IsNullOrEmpty(qtyDate1) == false || string.IsNullOrEmpty(qtyDate2) == false)
{
if (qtyDateType == "送單日")
{
kps = kps.Where(v => v.SentDate >= applyDateFrom && v.SentDate <= applyDateTo);
}
}
/* If no search result. */
if (kps.Count() == 0)
{
return(View("List", kv));
}
switch (ftype)
{
/* 與登入者相關且流程不在該登入者身上的文件 */
case "流程中":
kps.Join(_context.KeepFlows.Where(f2 => f2.UserId == ur.Id && f2.Status == "1")
.Select(f => f.DocId).Distinct(),
r => r.DocId, f2 => f2, (r, f2) => r)
.Join(_context.KeepFlows.Where(f => f.Status == "?" && f.UserId != ur.Id),
r => r.DocId, f => f.DocId,
(r, f) => new
{
keep = r,
flow = f
})
//.Join(_context.Assets, r => r.keep.AssetNo, a => a.AssetNo,
//(r, a) => new
//{
// keep = r.keep,
// asset = a,
// flow = r.flow
//})
.Join(_context.KeepDtls, m => m.keep.DocId, d => d.DocId,
(m, d) => new
{
keep = m.keep,
flow = m.flow,
//asset = m.asset,
keepdtl = d
})
.Join(_context.Departments, j => j.keep.AccDpt, d => d.DptId,
(j, d) => new
{
keep = j.keep,
flow = j.flow,
//asset = j.asset,
keepdtl = j.keepdtl,
dpt = d
}).ToList()
.ForEach(j => kv.Add(new KeepListVModel
{
DocType = "保養",
DocId = j.keep.DocId,
//AssetNo = j.keep.AssetNo,
//AssetName = j.keep.AssetName,
//Brand = j.asset.Brand,
//Type = j.asset.Type,
PlaceLoc = j.keep.PlaceLoc,
ApplyDpt = j.keep.DptId,
AccDpt = j.keep.AccDpt,
AccDptName = j.dpt.Name_C,
Result = (j.keepdtl.Result == null || j.keepdtl.Result == 0) ? "" : _context.KeepResults.Find(j.keepdtl.Result).Title,
InOut = j.keepdtl.InOut == "0" ? "自行" :
j.keepdtl.InOut == "1" ? "委外" :
j.keepdtl.InOut == "2" ? "租賃" :
j.keepdtl.InOut == "3" ? "保固" : "",
Memo = j.keepdtl.Memo,
Cost = j.keepdtl.Cost,
Days = DateTime.Now.Subtract(j.keep.SentDate.GetValueOrDefault()).Days,
Flg = j.flow.Status,
FlowUid = j.flow.UserId,
FlowCls = j.flow.Cls,
Src = j.keep.Src,
SentDate = j.keep.SentDate,
EndDate = j.keepdtl.EndDate,
IsCharged = j.keepdtl.IsCharged,
keepdata = j.keep
}));
break;
case "已結案":
var kf = _context.KeepFlows.Where(f => f.Status == "2");
if (userManager.IsInRole(User, "Admin") || userManager.IsInRole(User, "RepAdmin") ||
userManager.IsInRole(User, "Manager") || userManager.IsInRole(User, "RepEngineer"))
{
if (userManager.IsInRole(User, "Manager"))
{
kf = kf.Join(_context.Keeps.Where(r => r.AccDpt == ur.DptId),
f => f.DocId, r => r.DocId, (f, r) => f);
}
/* If no other search values, search the docs belong the login engineer. */
if (userManager.IsInRole(User, "RepEngineer") && searchAllDoc == false)
{
kf = kf.Join(_context.KeepFlows.Where(f2 => f2.UserId == ur.Id),
f => f.DocId, f2 => f2.DocId, (f, f2) => f);
}
}
else /* If normal user, search the docs belong himself. */
{
kf = kf.Join(_context.KeepFlows.Where(f2 => f2.UserId == ur.Id),
f => f.DocId, f2 => f2.DocId, (f, f2) => f);
}
//
kf.Select(f => new
{
f.DocId,
f.UserId,
f.Cls,
f.Status
}).Distinct()
.Join(kps.DefaultIfEmpty(), f => f.DocId, k => k.DocId,
(f, k) => new
{
keep = k,
flow = f
})
//.Join(_context.Assets, r => r.keep.AssetNo, a => a.AssetNo,
//(r, a) => new
//{
// keep = r.keep,
// asset = a,
// flow = r.flow
//})
.Join(_context.KeepDtls, m => m.keep.DocId, d => d.DocId,
(m, d) => new
{
keep = m.keep,
flow = m.flow,
//asset = m.asset,
keepdtl = d
})
.Join(_context.Departments, j => j.keep.AccDpt, d => d.DptId,
(j, d) => new
{
keep = j.keep,
flow = j.flow,
//asset = j.asset,
keepdtl = j.keepdtl,
dpt = d
}).ToList()
.ForEach(j => kv.Add(new KeepListVModel
{
DocType = "保養",
DocId = j.keep.DocId,
//AssetNo = j.keep.AssetNo,
//AssetName = j.keep.AssetName,
//Brand = j.asset.Brand,
//Type = j.asset.Type,
PlaceLoc = j.keep.PlaceLoc,
ApplyDpt = j.keep.DptId,
AccDpt = j.keep.AccDpt,
AccDptName = j.dpt.Name_C,
Result = (j.keepdtl.Result == null || j.keepdtl.Result == 0) ? "" : _context.KeepResults.Find(j.keepdtl.Result).Title,
InOut = j.keepdtl.InOut == "0" ? "自行" :
j.keepdtl.InOut == "1" ? "委外" :
j.keepdtl.InOut == "2" ? "租賃" :
j.keepdtl.InOut == "3" ? "保固" : "",
Memo = j.keepdtl.Memo,
Cost = j.keepdtl.Cost,
Days = DateTime.Now.Subtract(j.keep.SentDate.GetValueOrDefault()).Days,
Flg = j.flow.Status,
FlowUid = j.flow.UserId,
FlowCls = j.flow.Cls,
Src = j.keep.Src,
SentDate = j.keep.SentDate,
EndDate = j.keepdtl.EndDate,
CloseDate = j.keepdtl.CloseDate.Value.Date,
IsCharged = j.keepdtl.IsCharged,
keepdata = j.keep
}));
break;
case "待簽核":
/* Get all dealing repair docs. */
var keepFlows = _context.KeepFlows.Join(kps.DefaultIfEmpty(), f => f.DocId, k => k.DocId,
(f, k) => new
{
keep = k,
flow = f
}).Join(_context.AppUsers, f => f.flow.UserId, a => a.Id,
(f, a) => new
{
keep = f.keep,
flow = f.flow,
fuser = a
});
if (userManager.IsInRole(User, "Admin") || userManager.IsInRole(User, "RepAdmin") ||
userManager.IsInRole(User, "RepEngineer"))
{
/* If has other search values, search all RepairDocs which flowCls is in engineer. */
/* Else return the docs belong the login engineer. */
if (userManager.IsInRole(User, "RepEngineer") && searchAllDoc == true)
{
keepFlows = keepFlows.Where(f => f.flow.Status == "?" && f.flow.Cls.Contains("工程師"));
if (!string.IsNullOrEmpty(qtyEngCode)) //工程師搜尋
{
keepFlows = keepFlows.Where(f => f.keep.EngId == Convert.ToInt32(qtyEngCode));
}
}
else
{
/* 個人或同部門結案案件 */
keepFlows = keepFlows.Where(f => (f.flow.Status == "?" && f.flow.UserId == ur.Id) ||
(f.flow.Status == "?" && f.flow.Cls == "驗收人" &&
f.fuser.DptId == ur.DptId));
/* 個人案件 */
//keepFlows = keepFlows.Where(f => (f.flow.Status == "?" && f.flow.UserId == ur.Id)).ToList();
}
}
else
{
/* 個人或同部門結案案件 */
keepFlows = keepFlows.Where(f => (f.flow.Status == "?" && f.flow.UserId == ur.Id) ||
(f.flow.Status == "?" && f.flow.Cls == "驗收人" &&
f.fuser.DptId == ur.DptId));
/* 個人案件 */
//keepFlows = keepFlows.Where(f => (f.flow.Status == "?" && f.flow.UserId == ur.Id)).ToList();
}
keepFlows.Join(_context.KeepDtls, m => m.keep.DocId, d => d.DocId,
(m, d) => new
{
keep = m.keep,
flow = m.flow,
//asset = m.asset,
keepdtl = d
})
.Join(_context.Departments, j => j.keep.AccDpt, d => d.DptId,
(j, d) => new
{
keep = j.keep,
flow = j.flow,
//asset = j.asset,
keepdtl = j.keepdtl,
dpt = d
}).ToList()
.ForEach(j => kv.Add(new KeepListVModel
{
DocType = "保養",
DocId = j.keep.DocId,
//AssetNo = j.keep.AssetNo,
//AssetName = j.keep.AssetName,
//Brand = j.asset.Brand,
//Type = j.asset.Type,
PlaceLoc = j.keep.PlaceLoc,
ApplyDpt = j.keep.DptId,
AccDpt = j.keep.AccDpt,
AccDptName = j.dpt.Name_C,
Result = (j.keepdtl.Result == null || j.keepdtl.Result == 0) ? "" : _context.KeepResults.Find(j.keepdtl.Result).Title,
InOut = j.keepdtl.InOut == "0" ? "自行" :
j.keepdtl.InOut == "1" ? "委外" :
j.keepdtl.InOut == "2" ? "租賃" :
j.keepdtl.InOut == "3" ? "保固" : "",
Memo = j.keepdtl.Memo,
Cost = j.keepdtl.Cost,
Days = DateTime.Now.Subtract(j.keep.SentDate.GetValueOrDefault()).Days,
Flg = j.flow.Status,
FlowUid = j.flow.UserId,
FlowCls = j.flow.Cls,
Src = j.keep.Src,
SentDate = j.keep.SentDate,
EndDate = j.keepdtl.EndDate,
IsCharged = j.keepdtl.IsCharged,
keepdata = j.keep,
ArriveDate = j.flow.Rtt
}));
break;
}
;
/* 設備編號"有"、"無"的對應，"有"讀取table相關data，"無"只顯示申請人輸入的設備名稱 */
foreach (var item in kv)
{
if (!string.IsNullOrEmpty(item.keepdata.AssetNo))
{
var asset = _context.Assets.Where(a => a.AssetNo == item.keepdata.AssetNo).FirstOrDefault();
if (asset != null)
{
item.AssetNo = asset.AssetNo;
item.AssetName = asset.Cname;
item.Brand = asset.Brand;
item.Type = asset.Type;
}
}
else
{
item.AssetName = item.keepdata.AssetName;
}
}
/* Search date by DateType. */
if (string.IsNullOrEmpty(qtyDate1) == false || string.IsNullOrEmpty(qtyDate2) == false)
{
if (qtyDateType == "結案日")
{
kv = kv.Where(v => v.CloseDate >= applyDateFrom && v.CloseDate <= applyDateTo).ToList();
}
else if (qtyDateType == "完工日")
{
kv = kv.Where(v => v.EndDate >= applyDateFrom && v.EndDate <= applyDateTo).ToList();
}
}
/* Sorting search result. */
if (kv.Count() != 0)
{
if (qtyOrderType == "結案日")
{
kv = kv.OrderByDescending(r => r.CloseDate).ThenByDescending(r => r.DocId).ToList();
}
else if (qtyOrderType == "完工日")
{
kv = kv.OrderByDescending(r => r.EndDate).ThenByDescending(r => r.DocId).ToList();
}
else if (qtyOrderType == "送單日")
{
kv = kv.OrderByDescending(r => r.SentDate).ThenByDescending(r => r.DocId).ToList();
}
else
{
if (userManager.IsInRole(User, "RepEngineer") == true)
{
kv = kv.OrderByDescending(r => r.ArriveDate).ThenByDescending(r => r.SentDate).ThenByDescending(r => r.DocId).ToList();
}
else
{
kv = kv.OrderByDescending(r => r.SentDate).ThenByDescending(r => r.DocId).ToList();
}
}
}
/* Search KeepResults. */
if (!string.IsNullOrEmpty(qtyKeepResult))
{
kv = kv.Where(r => r.Result == _context.KeepResults.Find(Convert.ToInt32(qtyKeepResult)).Title).ToList();
}
/* Search IsCharged. */
if (!string.IsNullOrEmpty(qtyIsCharged))
{
kv = kv.Where(r => r.IsCharged == qtyIsCharged).ToList();
}
return(View("List", kv));
}
public async Task <IViewComponentResult> InvokeAsync()
{
List <SelectListItem> FlowlistItem = new List <SelectListItem>();
FlowlistItem.Add(new SelectListItem {
Text = "待簽核", Value = "待簽核"
});
FlowlistItem.Add(new SelectListItem {
Text = "流程中", Value = "流程中"
});
FlowlistItem.Add(new SelectListItem {
Text = "已結案", Value = "已結案"
});
ViewData["BMEDKeepFlowType"] = new SelectList(FlowlistItem, "Value", "Text", "待簽核");
/* 成本中心 & 申請部門的下拉選單資料 */
var dptList = new[] { "K", "P", "C" }; //本院部門
var departments = _context.Departments.Where(d => dptList.Contains(d.Loc)).ToList();
List <SelectListItem> listItem = new List <SelectListItem>();
foreach (var item in departments)
{
listItem.Add(new SelectListItem
{
Text = item.Name_C + "(" + item.DptId + ")", //show DptName(DptId)
Value = item.DptId
});
}
ViewData["BMEDKeepAccDpt"] = new SelectList(listItem, "Value", "Text");
ViewData["BMEDKeepApplyDpt"] = new SelectList(listItem, "Value", "Text");
/* 處理保養狀態的下拉選單 */
var keepResults = _context.BMEDKeepResults.ToList();
List <SelectListItem> listItem2 = new List <SelectListItem>();
foreach (var item in keepResults)
{
listItem2.Add(new SelectListItem
{
Text = item.Title,
Value = item.Id.ToString()
});
}
ViewData["BMEDKeepResult"] = new SelectList(listItem2, "Value", "Text");
/* 處理有無費用的下拉選單 */
List <SelectListItem> listItem3 = new List <SelectListItem>();
listItem3.Add(new SelectListItem {
Text = "有", Value = "Y"
});
listItem3.Add(new SelectListItem {
Text = "無", Value = "N"
});
ViewData["BMEDIsCharged"] = new SelectList(listItem3, "Value", "Text");
/* 處理日期查詢的下拉選單 */
List <SelectListItem> listItem4 = new List <SelectListItem>();
listItem4.Add(new SelectListItem {
Text = "送單日", Value = "送單日"
});
listItem4.Add(new SelectListItem {
Text = "完工日", Value = "完工日"
});
listItem4.Add(new SelectListItem {
Text = "結案日", Value = "結案日"
});
ViewData["BMEDKeepDateType"] = new SelectList(listItem4, "Value", "Text", "送單日");
/* 處理工程師查詢的下拉選單 */
var engs = roleManager.GetUsersInRole("MedEngineer").ToList();
List <SelectListItem> listItem5 = new List <SelectListItem>();
foreach (string l in engs)
{
var u = _context.AppUsers.Where(ur => ur.UserName == l).FirstOrDefault();
if (u != null)
{
listItem5.Add(new SelectListItem
{
Text = u.FullName + "(" + u.UserName + ")",
Value = u.Id.ToString()
});
}
}
ViewData["BMEDEngs"] = new SelectList(listItem5, "Value", "Text");
QryKeepListData data = new QryKeepListData();
return(View(data));
}
public IActionResult GetQueryList(QryKeepListData qdata)
{
string docid = qdata.KqtyDOCID;
string ano = qdata.KqtyASSETNO;
string acc = qdata.KqtyACCDPT;
string aname = qdata.KqtyASSETNAME;
string ftype = qdata.KqtyFLOWTYPE;
string dptid = qdata.KqtyDPTID;
string qtyDate1 = qdata.KqtyApplyDateFrom;
string qtyDate2 = qdata.KqtyApplyDateTo;
string qtyKeepResult = qdata.KqtyKeepResult;
string qtyIsCharged = qdata.KqtyIsCharged;
string qtyDateType = qdata.KqtyDateType;
string qtyTicketNo = qdata.KqtyTicketNo;
string qtyVendor = qdata.KqtyVendor;
string qtyOrderType = qdata.KqtyOrderType;
DateTime applyDateFrom = DateTime.Now;
DateTime applyDateTo = DateTime.Now;
/* Dealing search by date. */
if (qtyDate1 != null && qtyDate2 != null)// If 2 date inputs have been insert, compare 2 dates.
{
DateTime date1 = DateTime.Parse(qtyDate1);
DateTime date2 = DateTime.Parse(qtyDate2);
int result = DateTime.Compare(date1, date2);
if (result < 0)
{
applyDateFrom = date1.Date;
applyDateTo = date2.Date;
}
else if (result == 0)
{
applyDateFrom = date1.Date;
applyDateTo = date1.Date;
}
else
{
applyDateFrom = date2.Date;
applyDateTo = date1.Date;
}
}
else if (qtyDate1 == null && qtyDate2 != null)
{
applyDateFrom = DateTime.Parse(qtyDate2);
applyDateTo = DateTime.Parse(qtyDate2).AddSeconds(86399);
}
else if (qtyDate1 != null && qtyDate2 == null)
{
applyDateFrom = DateTime.Parse(qtyDate1);
applyDateTo = DateTime.Parse(qtyDate1).AddSeconds(86399);
}
List <KeepSearchListViewModel> kv = new List <KeepSearchListViewModel>();
/* Querying data. */
var kps = _context.Keeps.AsQueryable();
var keepFlows = _context.KeepFlows.AsQueryable();
var keepDtls = _context.KeepDtls.AsQueryable();
if (!string.IsNullOrEmpty(docid)) //表單編號
{
docid = docid.Trim();
kps = kps.Where(v => v.DocId == docid);
}
if (!string.IsNullOrEmpty(ano)) //財產編號
{
kps = kps.Where(v => v.AssetNo == ano);
}
if (!string.IsNullOrEmpty(dptid)) //所屬部門編號
{
kps = kps.Where(v => v.DptId == dptid);
}
if (!string.IsNullOrEmpty(acc)) //成本中心
{
kps = kps.Where(v => v.AccDpt == acc);
}
if (!string.IsNullOrEmpty(aname)) //物品名稱(關鍵字)
{
kps = kps.Where(v => v.AssetName != null)
.Where(v => v.AssetName.Contains(aname));
}
if (!string.IsNullOrEmpty(qtyTicketNo)) //發票號碼
{
qtyTicketNo = qtyTicketNo.ToUpper();
var resultDocIds = _context.KeepCosts.Include(kc => kc.TicketDtl)
.Where(kc => kc.TicketDtl.TicketDtlNo == qtyTicketNo)
.Select(kc => kc.DocId).Distinct();
kps = (from k in kps
where resultDocIds.Any(val => k.DocId.Contains(val))
select k);
}
if (!string.IsNullOrEmpty(qtyVendor)) //廠商關鍵字
{
var resultDocIds = _context.KeepCosts.Include(kc => kc.TicketDtl)
.Where(kc => kc.VendorName.Contains(qtyVendor))
.Select(kc => kc.DocId).Distinct();
kps = (from k in kps
where resultDocIds.Any(val => k.DocId.Contains(val))
select k);
}
if (string.IsNullOrEmpty(qtyDate1) == false || string.IsNullOrEmpty(qtyDate2) == false) //送單日
{
if (qtyDateType == "送單日")
{
kps = kps.Where(v => v.SentDate >= applyDateFrom && v.SentDate <= applyDateTo);
}
}
if (!string.IsNullOrEmpty(ftype)) //流程狀態
{
switch (ftype)
{
case "未結案":
keepFlows = keepFlows.Where(kf => kf.Status == "?");
break;
case "已結案":
keepFlows = keepFlows.Where(kf => kf.Status == "2");
break;
}
}
else
{
keepFlows = keepFlows.Where(kf => kf.Status == "?" || kf.Status == "2");
}
/* If no search result. */
if (kps.Count() == 0)
{
return(View("SearchList", kv));
}
kps.Join(keepFlows, r => r.DocId, f => f.DocId,
(k, f) => new
{
keep = k,
flow = f
})
.Join(keepDtls, m => m.keep.DocId, d => d.DocId,
(m, d) => new
{
keep = m.keep,
flow = m.flow,
keepdtl = d
})
.Join(_context.Departments, j => j.keep.AccDpt, d => d.DptId,
(j, d) => new
{
keep = j.keep,
flow = j.flow,
keepdtl = j.keepdtl,
dpt = d
})
.ToList()
.ForEach(j => kv.Add(new KeepSearchListViewModel
{
DocType = "保養",
DocId = j.keep.DocId,
PlaceLoc = j.keep.PlaceLoc,
ApplyDpt = j.keep.DptId,
AccDpt = j.keep.AccDpt,
AccDptName = j.dpt.Name_C,
Result = (j.keepdtl.Result == null || j.keepdtl.Result == 0) ? "" : _context.KeepResults.Find(j.keepdtl.Result).Title,
InOut = j.keepdtl.InOut == "0" ? "自行" :
j.keepdtl.InOut == "1" ? "委外" :
j.keepdtl.InOut == "2" ? "租賃" :
j.keepdtl.InOut == "3" ? "保固" : "",
Memo = j.keepdtl.Memo,
Cost = j.keepdtl.Cost,
Days = DateTime.Now.Subtract(j.keep.SentDate.GetValueOrDefault()).Days,
Flg = j.flow.Status,
FlowUid = j.flow.UserId,
FlowCls = j.flow.Cls,
FlowUidName = _context.AppUsers.Find(j.flow.UserId).FullName,
Src = j.keep.Src,
SentDate = j.keep.SentDate,
EndDate = j.keepdtl.EndDate,
CloseDate = j.keepdtl.CloseDate.HasValue == true ? j.keepdtl.CloseDate.Value.Date : j.keepdtl.CloseDate,
IsCharged = j.keepdtl.IsCharged,
keepdata = j.keep
}));
/* 設備編號"有"、"無"的對應，"有"讀取table相關data，"無"只顯示申請人輸入的設備名稱 */
foreach (var item in kv)
{
if (!string.IsNullOrEmpty(item.keepdata.AssetNo))
{
var asset = _context.Assets.Where(a => a.AssetNo == item.keepdata.AssetNo).FirstOrDefault();
if (asset != null)
{
item.AssetNo = asset.AssetNo;
item.AssetName = asset.Cname;
item.Brand = asset.Brand;
item.Type = asset.Type;
}
}
else
{
item.AssetName = item.keepdata.AssetName;
}
}
/* Search date by DateType. */
if (string.IsNullOrEmpty(qtyDate1) == false || string.IsNullOrEmpty(qtyDate2) == false)
{
if (qtyDateType == "結案日")
{
kv = kv.Where(v => v.CloseDate >= applyDateFrom && v.CloseDate <= applyDateTo).ToList();
}
else if (qtyDateType == "完工日")
{
kv = kv.Where(v => v.EndDate >= applyDateFrom && v.EndDate <= applyDateTo).ToList();
}
}
/* Sorting search result. */
if (kv.Count() != 0)
{
if (qtyOrderType == "結案日")
{
kv = kv.OrderByDescending(r => r.CloseDate).ThenByDescending(r => r.DocId).ToList();
}
else if (qtyOrderType == "完工日")
{
kv = kv.OrderByDescending(r => r.EndDate).ThenByDescending(r => r.DocId).ToList();
}
else
{
kv = kv.OrderByDescending(r => r.SentDate).ThenByDescending(r => r.DocId).ToList();
}
}
/* Search KeepResults. */
if (!string.IsNullOrEmpty(qtyKeepResult))
{
kv = kv.Where(r => r.Result == _context.KeepResults.Find(Convert.ToInt32(qtyKeepResult)).Title).ToList();
}
/* Search IsCharged. */
if (!string.IsNullOrEmpty(qtyIsCharged))
{
kv = kv.Where(r => r.IsCharged == qtyIsCharged).ToList();
}
return(View("SearchList", kv));
}
// GET: KeepSearch/
public IActionResult Index()
{
List <SelectListItem> FlowlistItem = new List <SelectListItem>();
FlowlistItem.Add(new SelectListItem {
Text = "未結案", Value = "未結案"
});
FlowlistItem.Add(new SelectListItem {
Text = "已結案", Value = "已結案"
});
ViewData["KeepFlowType"] = new SelectList(FlowlistItem, "Value", "Text");
/* 成本中心 & 申請部門的下拉選單資料 */
var dptList = new[] { "K", "P", "C" }; //本院部門
var departments = _context.Departments.Where(d => dptList.Contains(d.Loc)).ToList();
List <SelectListItem> listItem = new List <SelectListItem>();
foreach (var item in departments)
{
listItem.Add(new SelectListItem
{
Text = item.Name_C + "(" + item.DptId + ")", //show DptName(DptId)
Value = item.DptId
});
}
ViewData["KeepAccDpt"] = new SelectList(listItem, "Value", "Text");
ViewData["KeepApplyDpt"] = new SelectList(listItem, "Value", "Text");
/* 處理保養狀態的下拉選單 */
var keepResults = _context.KeepResults.ToList();
List <SelectListItem> listItem2 = new List <SelectListItem>();
foreach (var item in keepResults)
{
listItem2.Add(new SelectListItem
{
Text = item.Title,
Value = item.Id.ToString()
});
}
ViewData["KeepResult"] = new SelectList(listItem2, "Value", "Text");
/* 處理有無費用的下拉選單 */
List <SelectListItem> listItem3 = new List <SelectListItem>();
listItem3.Add(new SelectListItem {
Text = "有", Value = "Y"
});
listItem3.Add(new SelectListItem {
Text = "無", Value = "N"
});
ViewData["KeepIsCharged"] = new SelectList(listItem3, "Value", "Text");
/* 處理日期查詢的下拉選單 */
List <SelectListItem> listItem4 = new List <SelectListItem>();
listItem4.Add(new SelectListItem {
Text = "送單日", Value = "送單日"
});
listItem4.Add(new SelectListItem {
Text = "完工日", Value = "完工日"
});
listItem4.Add(new SelectListItem {
Text = "結案日", Value = "結案日"
});
ViewData["KeepDateType"] = new SelectList(listItem4, "Value", "Text", "送單日");
QryKeepListData data = new QryKeepListData();
return(View(data));
}
public async Task <IViewComponentResult> InvokeAsync()
{
// Get current user.
var user = _userRepo.Find(u => u.UserName == User.Identity.Name).FirstOrDefault();
/* 流程的下拉選單 */
List <SelectListItem> FlowlistItem = new List <SelectListItem>();
FlowlistItem.Add(new SelectListItem {
Text = "待簽核", Value = "待簽核"
});
FlowlistItem.Add(new SelectListItem {
Text = "流程中", Value = "流程中"
});
FlowlistItem.Add(new SelectListItem {
Text = "已結案", Value = "已結案"
});
FlowlistItem.Add(new SelectListItem {
Text = "請選擇", Value = "請選擇"
});
ViewData["BMEDKeepFlowType"] = new SelectList(FlowlistItem, "Value", "Text", "待簽核");
/* 成本中心 & 申請部門的下拉選單資料 */
var dptList = new[] { "K", "P", "C" }; //本院部門
//var departments = _context.Departments.Where(d => dptList.Contains(d.Loc)).ToList();
var departments = _context.Departments.ToList();
List <SelectListItem> listItem = new List <SelectListItem>();
foreach (var item in departments)
{
listItem.Add(new SelectListItem
{
Text = item.Name_C + "(" + item.DptId + ")", //show DptName(DptId)
Value = item.DptId
});
}
ViewData["BMEDKeepAccDpt"] = new SelectList(listItem, "Value", "Text");
ViewData["BMEDKeepApplyDpt"] = new SelectList(listItem, "Value", "Text");
/* 處理保養狀態的下拉選單 */
var keepResults = _context.BMEDKeepResults.ToList();
List <SelectListItem> listItem2 = new List <SelectListItem>();
foreach (var item in keepResults)
{
listItem2.Add(new SelectListItem
{
Text = item.Title,
Value = item.Id.ToString()
});
}
ViewData["BMEDKeepResult"] = new SelectList(listItem2, "Value", "Text");
/* 處理有無費用的下拉選單 */
List <SelectListItem> listItem3 = new List <SelectListItem>();
listItem3.Add(new SelectListItem {
Text = "有", Value = "Y"
});
listItem3.Add(new SelectListItem {
Text = "無", Value = "N"
});
ViewData["BMEDIsCharged"] = new SelectList(listItem3, "Value", "Text");
/* 處理日期查詢的下拉選單 */
List <SelectListItem> listItem4 = new List <SelectListItem>();
listItem4.Add(new SelectListItem {
Text = "送單日", Value = "送單日"
});
listItem4.Add(new SelectListItem {
Text = "完工日", Value = "完工日"
});
listItem4.Add(new SelectListItem {
Text = "結案日", Value = "結案日"
});
ViewData["BMEDKeepDateType"] = new SelectList(listItem4, "Value", "Text", "送單日");
/* 處理工程師查詢的下拉選單 */
var engs = roleManager.GetUsersInRole("MedEngineer").ToList();
List <SelectListItem> listItem5 = new List <SelectListItem>();
foreach (string l in engs)
{
var u = _context.AppUsers.Where(ur => ur.UserName == l).FirstOrDefault();
if (u != null)
{
listItem5.Add(new SelectListItem
{
Text = u.FullName + "(" + u.UserName + ")",
Value = u.Id.ToString()
});
}
}
ViewData["BMEDEngs"] = new SelectList(listItem5, "Value", "Text");
/* 處理保養方式的下拉選單 */
List <SelectListItem> listItem6 = new List <SelectListItem>();
listItem6.Add(new SelectListItem {
Text = "自行", Value = "自行"
});
listItem6.Add(new SelectListItem {
Text = "委外", Value = "委外"
});
listItem6.Add(new SelectListItem {
Text = "租賃", Value = "租賃"
});
listItem6.Add(new SelectListItem {
Text = "保固", Value = "保固"
});
ViewData["BMEDKeepInOut"] = new SelectList(listItem6, "Value", "Text", "");
/* 擷取該使用者單位底下所有人員 */
var dptUsers = _context.AppUsers.Where(a => a.DptId == user.DptId && a.Status == "Y").ToList();
List <SelectListItem> dptMemberList = new List <SelectListItem>();
foreach (var item in dptUsers)
{
dptMemberList.Add(new SelectListItem
{
Text = item.FullName + "(" + item.UserName + ")",
Value = item.Id.ToString()
});
}
// 使用者為工程師，帶工程師列表，其餘帶同部門人員
if (user.DptId == "7084" || user.DptId == "8420")
{
ViewData["BMEDClsUsers"] = new SelectList(listItem5, "Value", "Text");
}
else
{
ViewData["BMEDClsUsers"] = new SelectList(dptMemberList, "Value", "Text");
}
List <SelectListItem> listItem7 = new List <SelectListItem>();
listItem7.Add(new SelectListItem {
Text = "總院", Value = "總院"
});
listItem7.Add(new SelectListItem {
Text = "二林", Value = "L"
});
listItem7.Add(new SelectListItem {
Text = "員林", Value = "B"
});
listItem7.Add(new SelectListItem {
Text = "南投", Value = "N"
});
listItem7.Add(new SelectListItem {
Text = "鹿基", Value = "U"
});
listItem7.Add(new SelectListItem {
Text = "雲基", Value = "T"
});
ViewData["Location"] = new SelectList(listItem7, "Value", "Text");
QryKeepListData data = new QryKeepListData();
return(View(data));
}
