/// <summary>
/// 修改申請單記錄檔
/// </summary>
/// <param name="data">修改資料</param>
/// <param name="custodianFlag">是否為保管科</param>
/// <param name="searchData">申請表單查詢顯示區塊ViewModel</param>
/// <param name="userId">userId</param>
/// <returns></returns>
public MSGReturnModel <List <TreasuryAccessApprSearchDetailViewModel> > updateAplyNo(TreasuryAccessViewModel data, bool custodianFlag, TreasuryAccessApprSearchViewModel searchData, string userId)
{
MSGReturnModel <List <TreasuryAccessApprSearchDetailViewModel> > result = new MSGReturnModel <List <TreasuryAccessApprSearchDetailViewModel> >();
result.DESCRIPTION = Ref.MessageType.already_Change.GetDescription();
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
var updateData = db.TREA_APLY_REC.FirstOrDefault(x => x.APLY_NO == data.vAplyNo);
if (updateData != null)
{
if (updateData.LAST_UPDATE_DT > data.vLastUpdateTime)
{
return(result);
}
updateData.LAST_UPDATE_UID = userId;
updateData.ACCESS_REASON = data.vAccessReason;
updateData.EXPECTED_ACCESS_DATE = TypeTransfer.stringToDateTimeN(data.vExpectedAccessDate);
updateData.LAST_UPDATE_DT = DateTime.Now;
try
{
db.SaveChanges();
result.DESCRIPTION = Ref.MessageType.update_Success.GetDescription();
result.RETURN_FLAG = true;
result.Datas = GetCustodySearchDetail(searchData);
}
catch (DbUpdateException ex)
{
result.DESCRIPTION = ex.exceptionMessage();
}
}
}
return(result);
}
/// <summary>
/// 查詢
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public List <SpecifiedTimeTreasurySearchDetailViewModel> GetSearchDetail(SpecifiedTimeTreasurySearchViewModel data)
{
List <SpecifiedTimeTreasurySearchDetailViewModel> result = new List <SpecifiedTimeTreasurySearchDetailViewModel>();
if (!data.vAPLY_DT_S.Any() || !data.vAPLY_DT_E.Any()) // 無查詢日期
{
return(result);
}
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
DateTime?_vAPLY_DT_S = TypeTransfer.stringToDateTimeN(data.vAPLY_DT_S);
DateTime?_vAPLY_DT_E = TypeTransfer.stringToDateTimeN(data.vAPLY_DT_E).DateToLatestTime();
List <string> notShowList = new List <string>();
notShowList.Add("D1001");
notShowList.Add("D1002");
notShowList.Add("D1003");
var _data = db.TREA_OPEN_REC.AsNoTracking()
.Where(x => x.OPEN_TREA_TYPE == "2") //開庫類型: 2.指定時間開庫
.Where(x => x.CREATE_DT >= _vAPLY_DT_S, _vAPLY_DT_S != null) //申請日期(起)
.Where(x => x.CREATE_DT <= _vAPLY_DT_E, _vAPLY_DT_E != null) //申請日期(迄)
.Where(x => x.TREA_REGISTER_ID == data.vTREA_REGISTER_ID, !data.vTREA_REGISTER_ID.IsNullOrWhiteSpace()) //工作單號
.OrderBy(x => x.CREATE_DT);
var _item = db.TREA_ITEM.AsNoTracking()
.Select(x => new { x.ITEM_ID, x.ITEM_DESC }).ToList();
var _query =
_data.Join(db.SYS_CODE.AsNoTracking().Where(x => x.CODE_TYPE == "APPR_STATUS"),
x => x.APPR_STATUS, z => z.CODE, (x, z) => new { open = x, sysCode = z })
.AsEnumerable()
.Select((x, index) => new SpecifiedTimeTreasurySearchDetailViewModel()
{
vINDEX = (index + 1).ToString(),
vCREATE_DT = x.open.CREATE_DT?.ToString("yyyy/MM/dd"),
vTREA_REGISTER_ID = x.open.TREA_REGISTER_ID,
vAPLY_STATUS = x.sysCode.CODE_VALUE,
vOPEN_TREA_REASON = "取" +
string.Join("及", x.open.OPEN_TREA_REASON.Split(';').ToList()
.Where(z => !notShowList.Contains(z))
.Select(z => _item.FirstOrDefault(y => y.ITEM_ID == z)?.ITEM_DESC)
.Where(z => z != null)
),
vOPEN_TREA_TIME = x.open.OPEN_TREA_TIME,
vMEMO = x.open.MEMO,
vOPEN_TREA_DATE = x.open.OPEN_TREA_DATE.ToString("yyyy/MM/dd"),
vOPEN_TREA_REASON_ID = x.open.OPEN_TREA_REASON,
vEXEC_TIME_B = x.open.EXEC_TIME_B,
vEXEC_TIME_E = x.open.EXEC_TIME_E,
vCREATE_UID = x.open.CREATE_UID,
vAPLY_STATUS_ID = x.open.APPR_STATUS,
vAPPR_UID = x.open.APPR_UID
});
result.AddRange(_query);
}
return(result);
}
public List <AlreadyConfirmedSearchDetailViewModel> GetSearchDetail(AlreadyConfirmedSearchViewModel searchData)
{
List <AlreadyConfirmedSearchDetailViewModel> result = new List <AlreadyConfirmedSearchDetailViewModel>();
if (searchData.vDT_From.IsNullOrWhiteSpace() || searchData.vDT_To.IsNullOrWhiteSpace()) // 無查詢日期
{
return(result);
}
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
DateTime? _vDT_From = TypeTransfer.stringToDateTimeN(searchData.vDT_From);
DateTime? _vDT_To = TypeTransfer.stringToDateTimeN(searchData.vDT_To).DateToLatestTime();
List <string> register_List = new List <string>();
var emps = GetEmps();
var _TREA_OPEN_REC = db.TREA_OPEN_REC.AsNoTracking();
var _TREA_APLY_REC = db.TREA_APLY_REC.AsNoTracking().AsQueryable();
var _SYS_CODE_OPEN_TREA_TYPE = db.SYS_CODE.AsNoTracking().Where(x => x.CODE_TYPE == "OPEN_TREA_TYPE").ToList();
var _SYS_CODE_ACCESS_TYPE = db.SYS_CODE.AsNoTracking().Where(x => x.CODE_TYPE == "ACCESS_TYPE").ToList();
var _TREA_ITEM = db.TREA_ITEM.AsNoTracking();
var _ITEM_SEAL = db.ITEM_SEAL.AsNoTracking();
var _OTHER_ITEM_APLY = db.OTHER_ITEM_APLY.AsNoTracking();
if (_TREA_OPEN_REC.Where(x => x.OPEN_TREA_TYPE == searchData.vOPEN_TREA_TYPE, !searchData.vOPEN_TREA_TYPE.IsNullOrWhiteSpace()).ToList().Any())
{
register_List.AddRange(_TREA_OPEN_REC.Where(x => x.OPEN_TREA_TYPE == searchData.vOPEN_TREA_TYPE, !searchData.vOPEN_TREA_TYPE.IsNullOrWhiteSpace()).Select(x => x.TREA_REGISTER_ID).ToList());
}
_TREA_APLY_REC = _TREA_APLY_REC
.Where(x => x.CONFIRM_DT >= _vDT_From, _vDT_From != null)
.Where(x => x.CONFIRM_DT <= _vDT_To, _vDT_To != null)
.Where(x => register_List.Contains(x.TREA_REGISTER_ID), register_List.Any())
.Where(x => x.TREA_REGISTER_ID == searchData.vTREA_REGISTER_ID, !searchData.vTREA_REGISTER_ID.IsNullOrWhiteSpace())
.Where(x => x.CONFIRM_UID == searchData.vConfirm_Id, !searchData.vConfirm_Id.IsNullOrWhiteSpace())
.Where(x => x.CONFIRM_UID != null && x.CONFIRM_UID != "");
//.Where(x => !x.CONFIRM_UID.IsNullOrWhiteSpace());
result = _TREA_APLY_REC.AsEnumerable()
.Select(x => new AlreadyConfirmedSearchDetailViewModel()
{
vACTUAL_PUT_TIME = _TREA_OPEN_REC.FirstOrDefault(y => y.TREA_REGISTER_ID == x.TREA_REGISTER_ID)?.ACTUAL_PUT_TIME.dateTimeToStr(),
vTREA_REGISTER_ID = x.TREA_REGISTER_ID,
vOPEN_TREA_TYPE = _SYS_CODE_OPEN_TREA_TYPE.FirstOrDefault(y => y.CODE == _TREA_OPEN_REC.FirstOrDefault(z => z.TREA_REGISTER_ID == x.TREA_REGISTER_ID)?.OPEN_TREA_TYPE)?.CODE_VALUE,
//vConfirm_Id = string.Format("{0}-{1}", !x.TREA_REGISTER_ID.IsNullOrWhiteSpace() ? x.ACTUAL_ACCESS_UID : x.CONFIRM_UID, !x.TREA_REGISTER_ID.IsNullOrWhiteSpace() ? GetUserInfo(x.ACTUAL_ACCESS_UID).EMP_Name : GetUserInfo(x.CONFIRM_UID).EMP_Name),
vConfirm_Id = $@"{x.CONFIRM_UID}-{emps.FirstOrDefault(y => y.USR_ID == x.CONFIRM_UID)?.EMP_NAME?.Trim()}",
vITEM_DESC = _TREA_ITEM.FirstOrDefault(y => y.ITEM_ID == x.ITEM_ID)?.ITEM_DESC,
vSEAL_DESC = _TREA_ITEM.FirstOrDefault(y => y.ITEM_ID == x.ITEM_ID)?.ITEM_OP_TYPE == "2" ? _ITEM_SEAL.FirstOrDefault(z => z.ITEM_ID == _OTHER_ITEM_APLY.FirstOrDefault(a => a.APLY_NO == x.APLY_NO).ITEM_ID)?.SEAL_DESC: null,
vACCESS_TYPE = _TREA_ITEM.FirstOrDefault(y => y.ITEM_ID == x.ITEM_ID)?.ITEM_OP_TYPE == "2" ? _SYS_CODE_ACCESS_TYPE.FirstOrDefault(z => z.CODE == x.ACCESS_TYPE)?.CODE_VALUE : null,
vAPLY_NO = x.APLY_NO,
vACCESS_REASON = x.ACCESS_REASON,
hITEM_ID = x.ITEM_ID,
hITEM_OP_TYPE = _TREA_ITEM.FirstOrDefault(y => y.ITEM_ID == x.ITEM_ID)?.ITEM_OP_TYPE
}).ToList();
}
return(result);
}
/// <summary>
/// 共用存檔 申請單紀錄檔 回傳參數1 (APLY_NO) 回傳參數2 (log訊息)
/// </summary>
/// <param name="db">Entity</param>
/// <param name="taData">viewModel</param>
/// <param name="logStr">紀錄訊息</param>
/// <param name="dt">修改時間</param>
/// <returns></returns>
protected Tuple <string, string> SaveTREA_APLY_REC(TreasuryDBEntities db, TreasuryAccessViewModel taData, string logStr, DateTime dt)
{
//取得流水號
SysSeqDao sysSeqDao = new SysSeqDao();
String qPreCode = DateUtil.getCurChtDateTime().Split(' ')[0];
var cId = sysSeqDao.qrySeqNo("G6", qPreCode).ToString().PadLeft(3, '0');
if (logStr.IsNullOrWhiteSpace())
{
logStr = string.Empty;
}
#region 申請單紀錄檔
var _TAR = new TREA_APLY_REC()
{
APLY_NO = $@"G6{qPreCode}{cId}", //申請單號 G6+系統日期YYYMMDD(民國年)+3碼流水號
APLY_FROM = Ref.AccessProjectStartupType.M.ToString(), //人工
ITEM_ID = taData.vItem, //申請項目
ACCESS_TYPE = taData.vAccessType, //存入(P) or 取出(G)
ACCESS_REASON = taData.vAccessReason, //申請原因
APLY_STATUS = Ref.AccessProjectFormStatus.A01.ToString(), //表單申請
EXPECTED_ACCESS_DATE = TypeTransfer.stringToDateTimeN(taData.vExpectedAccessDate), //預計存取日期
APLY_UNIT = taData.vAplyUnit, //申請單位
APLY_UID = taData.vAplyUid, //申請人
APLY_DT = dt,
CREATE_UNIT = taData.vCreateUnit, //新增單位
CREATE_UID = taData.vCreateUid, //新增人
CREATE_DT = dt,
LAST_UPDATE_UID = taData.vCreateUid,
LAST_UPDATE_DT = dt
};
if (taData.vAplyUid != taData.vCreateUid) //當申請人不是新增人(代表為保管單位代申請)
{
_TAR.CUSTODY_UID = taData.vCreateUid; //保管單位直接帶 新增人
_TAR.CUSTODY_DT = dt;
}
logStr += _TAR.modelToString(logStr);
db.TREA_APLY_REC.Add(_TAR);
#endregion
#region 申請單歷程檔
var _ARH = new APLY_REC_HIS()
{
APLY_NO = _TAR.APLY_NO,
APLY_STATUS = _TAR.APLY_STATUS,
PROC_DT = dt,
PROC_UID = _TAR.CREATE_UID
};
logStr += _ARH.modelToString(logStr);
db.APLY_REC_HIS.Add(_ARH);
#endregion
return(new Tuple <string, string>(_TAR.APLY_NO, logStr));
}
/// <summary>
/// get A58 Data
/// </summary>
/// <param name="datepicker">ReportDate</param>
/// <param name="sType">Rating_Type</param>
/// <param name="from">Origination_Date start</param>
/// <param name="to">Origination_Date to</param>
/// <param name="bondNumber">bondNumber</param>
/// <param name="version">version</param>
/// <param name="search">全部or缺漏</param>
/// <returns></returns>
public Tuple <bool, List <A58ViewModel> > GetA58(
string datepicker,
string sType,
string from,
string to,
string bondNumber,
string version,
string search)
{
DateTime?dp = TypeTransfer.stringToDateTimeN(datepicker);
DateTime?df = TypeTransfer.stringToDateTimeN(from);
DateTime?dt = TypeTransfer.stringToDateTimeN(to);
int ver = 0;
Int32.TryParse(version.Trim(), out ver);
var data = db.Bond_Rating_Summary.AsQueryable();
if (dp.HasValue)
{
data = data.Where(x => x.Report_Date != null &&
x.Report_Date == dp.Value &&
x.Version != 0 &&
x.Version == ver);
}
if (df.HasValue && dt.HasValue)
{
data = data.Where(x => x.Origination_Date != null &&
x.Origination_Date >= df &&
x.Origination_Date <= dt);
}
if (!sType.IsNullOrWhiteSpace())
{
data = data.Where(x => x.Rating_Type == sType);
}
if (!bondNumber.IsNullOrWhiteSpace())
{
data = data.Where(x => x.Bond_Number == bondNumber.Trim());
}
if ("Miss".Equals(search))
{
data = data.Where(x => x.Grade_Adjust == null);
}
if (data.Any())
{
return(new Tuple <bool, List <A58ViewModel> >(true,
data.AsEnumerable().Select(x => { return getA58ViewModel(x); }).ToList()));
}
else
{
return(new Tuple <bool, List <A58ViewModel> >(false, new List <A58ViewModel>()));
}
}
public JsonResult SearchA57(
string datepicker,
string version,
string from,
string to,
string SMF,
string stype,
string bondNumber,
string issuer,
string status)
{
MSGReturnModel result = new MSGReturnModel();
result.RETURN_FLAG = false;
DateTime dp = DateTime.MinValue;
DateTime? df = TypeTransfer.stringToDateTimeN(from);
DateTime? dt = TypeTransfer.stringToDateTimeN(to);
int v = 0;
bool flag = false;
Rating_Status rs = Rating_Status.All;
EnumUtil.GetValues <Rating_Status>().ToList().ForEach(x =>
{
if (x.ToString() == status)
{
rs = x;
flag = true;
}
});
if (!flag || !DateTime.TryParse(datepicker, out dp) || !Int32.TryParse(version, out v))
{
result.DESCRIPTION = Message_Type.parameter_Error.GetDescription();
}
else
{
Cache.Invalidate(CacheList.A57DbfileData); //清除
var datas = A5Repository.GetA57(dp, v, df, dt, SMF, stype, bondNumber, issuer, rs);
if (datas.Any())
{
result.RETURN_FLAG = true;
Cache.Set(CacheList.A57DbfileData, datas); //把資料存到 Cache
}
else
{
result.DESCRIPTION = Message_Type.not_Find_Any.GetDescription();
}
}
return(Json(result));
}
public JsonResult D73DelTxtFile(
string Id,
string date,
string ver
)
{
MSGReturnModel result = new MSGReturnModel();
result.RETURN_FLAG = false;
result = D7Repository.DelD73(Id);
if (result.RETURN_FLAG)
{
var datas = D7Repository.GetD73(TypeTransfer.stringToDateTimeN(date), TypeTransfer.stringToIntN(ver));
Cache.Invalidate(CacheList.D73DbfileData); //清除
if (datas.Any())
{
result.RETURN_FLAG = true;
Cache.Set(CacheList.D73DbfileData, datas); //把資料存到 Cache
}
}
return(Json(result));
}
public JsonResult SearchD73(
string date,
string ver
)
{
MSGReturnModel result = new MSGReturnModel();
result.RETURN_FLAG = false;
var datas = D7Repository.GetD73(TypeTransfer.stringToDateTimeN(date), TypeTransfer.stringToIntN(ver));
Cache.Invalidate(CacheList.D73DbfileData); //清除
if (datas.Any())
{
result.RETURN_FLAG = true;
Cache.Set(CacheList.D73DbfileData, datas); //把資料存到 Cache
}
else
{
result.DESCRIPTION = Message_Type.not_Find_Any.GetDescription(Table_Type.D73.tableNameGetDescription());
}
return(Json(result));
}
/// <summary>
/// 金庫物品存取申請覆核作業 覆核查詢
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public List <TreasuryAccessApprSearchDetailViewModel> GetApprSearchDetail(TreasuryAccessApprSearchViewModel data)
{
List <TreasuryAccessApprSearchDetailViewModel> result = new List <TreasuryAccessApprSearchDetailViewModel>();
List <string> dptList = new List <string>()
{
data.vCreateUnit
};
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
var depts = GetDepts();
var emps = GetEmps();
var _CUser = emps.FirstOrDefault(x => x.USR_ID == data.vCreateUid);
if (_CUser != null)
{
using (DB_INTRAEntities dbIntra = new DB_INTRAEntities())
{
var _V_EMPLY2_DPT_CD = dbIntra.V_EMPLY2.AsNoTracking()
.Where(x => x.DPT_HEAD == _CUser.EMP_NO).Select(x => x.DPT_CD).Distinct();
_V_EMPLY2_DPT_CD.ToList().ForEach(x => {
dptList.Add(x?.Trim());
});
}
}
var treaItems = db.TREA_ITEM.AsNoTracking().Where(x => x.ITEM_OP_TYPE == "3").ToList();
DateTime?_vAPLY_DT_S = TypeTransfer.stringToDateTimeN(data.vAPLY_DT_S);
DateTime?_vAPLY_DT_E = TypeTransfer.stringToDateTimeN(data.vAPLY_DT_E).DateToLatestTime();
result = db.TREA_APLY_REC.AsNoTracking()
.Where(x => x.APLY_DT >= _vAPLY_DT_S, _vAPLY_DT_S != null) //申請日期(起)
.Where(x => x.APLY_DT <= _vAPLY_DT_E, _vAPLY_DT_E != null) //申請日期(迄)
.Where(x => x.APLY_NO == data.vAPLY_NO, !data.vAPLY_NO.IsNullOrWhiteSpace()) //申請單號
//.Where(x => x.CREATE_UNIT == data.vCreateUnit && apprStatus.Contains(x.APLY_STATUS)) //相同部門 & 符合狀態 的資料
.Where(x => dptList.Distinct().Contains(x.CREATE_UNIT) && apprStatus.Contains(x.APLY_STATUS), dptList.Any()) //相同部門含主管 & 符合狀態 的資料
.AsEnumerable()
.Select(x => TreaAplyRecToTAASDViewModel(data.vCreateUid, x, treaItems, depts, emps)).ToList();
}
return(result);
}
/// <summary>
/// 金庫物品存取申請覆核作業 覆核查詢
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public List <TreasuryAccessApprSearchDetailViewModel> GetCustodyApprSearchDetail(TreasuryAccessApprSearchViewModel data)
{
List <TreasuryAccessApprSearchDetailViewModel> result = new List <TreasuryAccessApprSearchDetailViewModel>();
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
var depts = GetDepts();
var emps = GetEmps();
var formStatus = db.SYS_CODE.AsNoTracking().Where(x => x.CODE_TYPE == "FORM_STATUS").ToList();
var treaItems = db.TREA_ITEM.AsNoTracking().Where(x => x.ITEM_OP_TYPE == "3").ToList();
DateTime?_vAPLY_DT_S = TypeTransfer.stringToDateTimeN(data.vAPLY_DT_S);
DateTime?_vAPLY_DT_E = TypeTransfer.stringToDateTimeN(data.vAPLY_DT_E).DateToLatestTime();
result = db.TREA_APLY_REC.AsNoTracking()
.Where(x => x.APLY_DT >= _vAPLY_DT_S, _vAPLY_DT_S != null) //申請日期(起)
.Where(x => x.APLY_DT <= _vAPLY_DT_E, _vAPLY_DT_E != null) //申請日期(迄)
.Where(x => x.APLY_NO == data.vAPLY_NO, !data.vAPLY_NO.IsNullOrWhiteSpace()) //申請單號
.Where(x => x.APLY_STATUS == apprStatus) //符合狀態 的資料
.AsEnumerable()
.Select(x => TreaAplyRecToTAASDViewModel(data.vCreateUid, x, treaItems, depts, emps, formStatus, false))
.OrderByDescending(x => x.vAPLY_NO).ToList();
}
return(result);
}
/// <summary>
/// 覆核作業查詢
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public List <SpecifiedTimeTreasuryApprSearchDetailViewModel> GetApprSearchData(SpecifiedTimeTreasuryApprSearchViewModel data)
{
List <SpecifiedTimeTreasuryApprSearchDetailViewModel> result = new List <SpecifiedTimeTreasuryApprSearchDetailViewModel>();
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
var depts = GetDepts();
var emps = GetEmps();
DateTime?_vAPLY_DT_S = TypeTransfer.stringToDateTimeN(data.vAPLY_DT_S);
DateTime?_vAPLY_DT_E = TypeTransfer.stringToDateTimeN(data.vAPLY_DT_E).DateToLatestTime();
result = db.TREA_OPEN_REC.AsNoTracking()
.Where(x => x.OPEN_TREA_TYPE == "2")
.Where(x => x.CREATE_DT >= _vAPLY_DT_S, _vAPLY_DT_S != null) //申請日期(起)
.Where(x => x.CREATE_DT <= _vAPLY_DT_E, _vAPLY_DT_E != null) //申請日期(迄)
.Where(x => x.TREA_REGISTER_ID == data.vTREA_REGISTER_ID, !data.vTREA_REGISTER_ID.IsNullOrWhiteSpace()) //工作單號
//.Where(x => x.CREATE_UNIT == data.vCreateUnit) //相同部門
.Where(x => x.APPR_STATUS == "1")
.AsEnumerable()
.Select(x => new SpecifiedTimeTreasuryApprSearchDetailViewModel()
{
vCREATE_DT = x.CREATE_DT?.ToString("yyyy/MM/dd"),
vTREA_REGISTER_ID = x.TREA_REGISTER_ID,
vCREATE_UID = x.CREATE_UID,
vCREATE_NAME = emps.FirstOrDefault(y => y.USR_ID == x.CREATE_UID)?.EMP_NAME,
vOPEN_TREA_DATE = x.OPEN_TREA_DATE.ToString("yyyy/MM/dd"),
vEXEC_TIME_B = x.EXEC_TIME_B,
vEXEC_TIME_E = x.EXEC_TIME_E,
vOPEN_TREA_TIME = x.OPEN_TREA_TIME,
vMEMO = x.MEMO,
vOPEN_TREA_REASON_ID = x.OPEN_TREA_REASON,
vLAST_UPDATE_DT = x.LAST_UPDATE_DT,
vAPPR_FLAG = x.CREATE_UID != data.vCreateUid
}).ToList();
}
return(result);
}
/// <summary>
/// 文字檢核DateTime
/// </summary>
/// <param name="_par"></param>
/// <returns></returns>
protected bool checkStringToDateTime(string _par)
{
return(checkString(_par) || TypeTransfer.stringToDateTimeN(_par) == null);
}
public override DataSet GetData(List <reportParm> parms)
{
//報表資料
List <DepositReportESTATEData> ReportDataList = new List <DepositReportESTATEData>();
var resultsTable = new DataSet();
var ReportData = new DepositReportESTATEData();
string vdept = parms.Where(x => x.key == "vdept").FirstOrDefault()?.value ?? string.Empty;
string vsect = parms.Where(x => x.key == "vsect").FirstOrDefault()?.value ?? string.Empty;
string BOOK_NO = parms.Where(x => x.key == "vBook_No").FirstOrDefault()?.value ?? string.Empty;
string JobProject = parms.Where(x => x.key == "vJobProject").FirstOrDefault()?.value ?? string.Empty;
string APLY_DT_From = parms.Where(x => x.key == "APLY_DT_From").FirstOrDefault()?.value ?? string.Empty; //庫存日期
string APLY_ODT_From = parms.Where(x => x.key == "APLY_ODT_From").FirstOrDefault()?.value ?? string.Empty;
string APLY_ODT_To = parms.Where(x => x.key == "APLY_ODT_To").FirstOrDefault()?.value ?? string.Empty;
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
var _APLY_DT = TypeTransfer.stringToDateTime(APLY_DT_From).DateToLatestTime();
var _APLY_DT_Date = _APLY_DT.Date;
var dtn = DateTime.Now.Date;
var _APLY_ODT_From = TypeTransfer.stringToDateTimeN(APLY_ODT_From);
var _APLY_ODT_To = TypeTransfer.stringToDateTimeN(APLY_ODT_To).DateToLatestTime();
int TOTAL = 0;
INVENTORY_STATUSs.AddRange(new List <string>()
{
((int)AccessInventoryType._5).ToString(), //預約取出，計庫存
((int)AccessInventoryType._6).ToString(), //已被取出，計庫存
((int)AccessInventoryType._9).ToString()
}); //預約存入，計庫存
var _IRE = db.ITEM_REAL_ESTATE.AsNoTracking().ToList();
_IRE = db.ITEM_REAL_ESTATE.AsNoTracking()//判斷是否在庫
.Where(x => INVENTORY_STATUSs.Contains(x.INVENTORY_STATUS), _APLY_DT_Date == dtn)
.Where(x =>
(INVENTORY_STATUSs.Contains(x.INVENTORY_STATUS) && x.PUT_DATE <= _APLY_DT) // 在庫 且 存入日期 <= 庫存日期
||
(x.INVENTORY_STATUS == INVENTORY_STATUSg &&
x.PUT_DATE <= _APLY_DT &&
_APLY_DT < x.GET_DATE), //存入日期 <= 庫存日期 且 庫存日期 < 取出日期
_APLY_DT_Date != dtn)
.Where(x => x.CHARGE_DEPT == vdept, vdept != "All")
.Where(x => x.CHARGE_SECT == vsect, vsect != "All")
.Where(x => x.GROUP_NO.ToString() == BOOK_NO, BOOK_NO != "All")
.Where(x => x.PUT_DATE >= _APLY_ODT_From, _APLY_ODT_From != null)
.Where(x => x.PUT_DATE <= _APLY_ODT_To, _APLY_ODT_To != null).ToList();
var depts = new List <VW_OA_DEPT>();
var types = new List <SYS_CODE>();
var book = new List <ITEM_BOOK>();
using (DB_INTRAEntities dbINTRA = new DB_INTRAEntities())
{
depts = dbINTRA.VW_OA_DEPT.AsNoTracking().Where(x => x.DPT_CD != null).ToList();
}
types = db.SYS_CODE.AsNoTracking().Where(x => x.CODE != null && x.CODE_TYPE == "ESTATE_TYPE").ToList();
book = db.ITEM_BOOK.AsNoTracking().Where(x => x.GROUP_NO.ToString() != null).ToList();
_IRE.ForEach(x =>
{
x.ESTATE_FORM_NO = types.FirstOrDefault(y => y.CODE_VALUE == x.ESTATE_FORM_NO)?.CODE;
});
foreach (var ESTATEdata in _IRE.OrderBy(x => x.GROUP_NO).ThenBy(x => x.PUT_DATE).ThenBy(x => x.ESTATE_FORM_NO).ThenBy(x => x.ESTATE_DATE).ThenBy(x => x.OWNERSHIP_CERT_NO))
{
TOTAL++;
var _CHARGE_DEPT = getEmpName(depts, ESTATEdata.CHARGE_DEPT);
var _CHARGE_SECT = getEmpName(depts, ESTATEdata.CHARGE_SECT)?.Replace(_CHARGE_DEPT, "")?.Trim();
ReportData = new DepositReportESTATEData()
{
ROW = TOTAL,
PUT_DATE = ESTATEdata.PUT_DATE.dateTimeToStr(),
ESTATE_FORM_NO = types.FirstOrDefault(x => x.CODE == ESTATEdata.ESTATE_FORM_NO)?.CODE_VALUE,
ESTATE_DATE = ESTATEdata.ESTATE_DATE.dateTimeToStr(),
OWNERSHIP_CERT_NO = ESTATEdata.OWNERSHIP_CERT_NO,
LAND_BUILDING_NO = ESTATEdata.LAND_BUILDING_NO,
HOUSE_NO = ESTATEdata.HOUSE_NO,
ESTATE_SEQ = ESTATEdata.ESTATE_SEQ,
BOOK_NO_DETAIL = ESTATEdata.GROUP_NO.ToString(),
BUILDING_NAME = getBuildName(book, ESTATEdata.GROUP_NO.ToString()),
LOCATED = getLocated(book, ESTATEdata.GROUP_NO.ToString()),
CHARGE_DEPT = _CHARGE_DEPT,
CHARGE_SECT = _CHARGE_SECT,
CHARGE_DEPT_ID = ESTATEdata.CHARGE_DEPT,
CHARGE_SECT_ID = ESTATEdata.CHARGE_SECT,
MEMO = ESTATEdata.MEMO,
};
ReportDataList.Add(ReportData);
}
}
resultsTable.Tables.Add(ReportDataList.ToDataTable());
return(resultsTable);
}
public override DataSet GetData(List <reportParm> parms)
{
var resultsTable = new DataSet();
List <TreasuryKeyCheckReport> ReportDataList = new List <TreasuryKeyCheckReport>();
var _Parameters = new List <SqlParameter>();
var ReportData = new TreasuryKeyCheckReport();
var APLY_DT_From = parms.Where(x => x.key == "APLY_DT_From").FirstOrDefault()?.value;
var APLY_DT_To = parms.Where(x => x.key == "APLY_DT_To").FirstOrDefault()?.value;
string CONTROL_MODE = parms.Where(x => x.key == "CONTROL_MODE").FirstOrDefault()?.value;
string CONTROL_MODE_TEXT = parms.Where(x => x.key == "CONTROL_MODE_TEXT").FirstOrDefault()?.value;
string CUSTODY_MODE = parms.Where(x => x.key == "CUSTODY_MODE").FirstOrDefault()?.value;
string EMP_NAME = parms.Where(x => x.key == "EMP_NAME").FirstOrDefault()?.value;
string AGENT_NAME = parms.Where(x => x.key == "AGENT_NAME").FirstOrDefault()?.value;
int Total = 0;
var _datas = new List <CODE_ROLE_TREA_ITEM>();
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
var _APLY_DT_From = TypeTransfer.stringToDateTimeN(APLY_DT_From);
DateTime? _APLY_DT_To = TypeTransfer.stringToDateTimeN(APLY_DT_To).DateToLatestTime();
List <string> _EMP_Roles = new List <string>();
List <string> _AGENT_Roles = new List <string>();
List <string> _ITEM_ID_AlreadyHave = new List <string>();
if (EMP_NAME != "All")
{
_EMP_Roles = db.CODE_USER_ROLE.AsNoTracking().Where(x => x.USER_ID == EMP_NAME).Select(x => x.ROLE_ID).ToList();
}
if (AGENT_NAME != "All")
{
_AGENT_Roles = db.CODE_USER_ROLE.AsNoTracking().Where(x => x.USER_ID == AGENT_NAME).Select(x => x.ROLE_ID).ToList();
}
var _CRTI = db.CODE_ROLE_TREA_ITEM.AsNoTracking()//角色金庫設備資料檔-設備代碼
.Where(x => _APLY_DT_From <= x.LAST_UPDATE_DT, _APLY_DT_From != null)
.Where(x => x.LAST_UPDATE_DT <= _APLY_DT_To, _APLY_DT_To != null)
//.Where(x => _EMP_Roles.Contains(x.ROLE_ID) , EMP_NAME != "All")
//.Where(x => _AGENT_Roles.Contains())
.Where(x => x.CUSTODY_MODE == CUSTODY_MODE)
.ToList();
if (_EMP_Roles.Any() || _AGENT_Roles.Any())
{
var _CRTI_EMP = _CRTI.Where(x => x.CUSTODY_ORDER == 1)
.Where(x => _EMP_Roles.Contains(x.ROLE_ID), _EMP_Roles.Any()).ToList();
var _CRTI_AGENT = _CRTI.Where(x => x.CUSTODY_ORDER == 2)
.Where(x => _AGENT_Roles.Contains(x.ROLE_ID), _AGENT_Roles.Any()).ToList();
_CRTI = _CRTI_EMP;
_CRTI.AddRange(_CRTI_AGENT);
}
var _TREA_EQUIP_IDs = _CRTI.Select(x => x.TREA_EQUIP_ID).ToList();
var _TE = db.TREA_EQUIP.AsNoTracking()//金庫設備名稱
.Where(x => x.IS_DISABLED == "N")
.Where(x => _TREA_EQUIP_IDs.Contains(x.TREA_EQUIP_ID))
.Where(x => x.CONTROL_MODE == CONTROL_MODE)
.ToList();
var depts = new List <V_EMPLY2>();
var types = new List <SYS_CODE>();
using (DB_INTRAEntities dbINTRA = new DB_INTRAEntities())
{
depts = dbINTRA.V_EMPLY2.AsNoTracking().Where(x => x.USR_ID != null).ToList();
}
types = db.SYS_CODE.AsNoTracking().Where(x => x.CODE != null).ToList();
var curs = db.CODE_USER_ROLE.AsNoTracking().ToList();
foreach (var item in _CRTI.GroupBy(x => new { x.TREA_EQUIP_ID, x.CUSTODY_MODE }))
{
bool addFlag = false;
if (EMP_NAME != "All" && AGENT_NAME != "All")
{
var order1_Role_Ids = item.Where(x => x.CUSTODY_ORDER == 1).Select(x => x.ROLE_ID).ToList();
var order2_Role_Ids = item.Where(x => x.CUSTODY_ORDER == 2).Select(x => x.ROLE_ID).ToList();
if (curs.Any(x => order1_Role_Ids.Contains(x.ROLE_ID) && x.USER_ID == EMP_NAME) &&
curs.Any(x => order2_Role_Ids.Contains(x.ROLE_ID) && x.USER_ID == AGENT_NAME))
{
addFlag = true;
}
}
else if (EMP_NAME != "All")
{
var order1_Role_Ids = item.Where(x => x.CUSTODY_ORDER == 1).Select(x => x.ROLE_ID).ToList();
if (curs.Any(x => order1_Role_Ids.Contains(x.ROLE_ID) && x.USER_ID == EMP_NAME))
{
addFlag = true;
}
}
else if (AGENT_NAME != "All")
{
var order2_Role_Ids = item.Where(x => x.CUSTODY_ORDER == 2).Select(x => x.ROLE_ID).ToList();
if (curs.Any(x => order2_Role_Ids.Contains(x.ROLE_ID) && x.USER_ID == AGENT_NAME))
{
addFlag = true;
}
}
else
{
addFlag = true;
}
if (addFlag)
{
_ITEM_ID_AlreadyHave.Add(item.Key.TREA_EQUIP_ID);
var TREA_EQUIP_ID_Key = item.Key.TREA_EQUIP_ID;
var TE = _TE.FirstOrDefault(x => x.TREA_EQUIP_ID == TREA_EQUIP_ID_Key);
var CUSTODY_Value_1 = string.Join("、", getEmpName(depts, curs, item.Where(x => x.CUSTODY_ORDER == 1).Select(x => x.ROLE_ID).ToList())); //保管人
var CUSTODY_Value_2 = string.Join("、", getEmpName(depts, curs, item.Where(x => x.CUSTODY_ORDER == 2).Select(x => x.ROLE_ID).ToList())); //代理人
if (TE != null)
{
Total++;
var CUSTODY_MODE_Key = getCSMTtype(types, item.Key.CUSTODY_MODE);
var TREA_EQUIP_ID_Key_Name = TE?.EQUIP_NAME;
var MEMO = TE?.MEMO;
ReportData = new TreasuryKeyCheckReport()
{
ROW = Total,
CUSTODY_MODE = CUSTODY_MODE_Key,
EQUIP_NAME = TREA_EQUIP_ID_Key_Name,
EMP_NAME = CUSTODY_Value_1,
AGENT_NAME = CUSTODY_Value_2,
MEMO = MEMO,
};
ReportDataList.Add(ReportData);
}
}
}
if (EMP_NAME == "All" && AGENT_NAME == "All")
{
var _TREA_EQUIP = db.TREA_EQUIP.AsNoTracking()
.Where(x => x.CONTROL_MODE == CONTROL_MODE)
.Where(x => x.IS_DISABLED == "N")
.Where(x => !_ITEM_ID_AlreadyHave.Contains(x.TREA_EQUIP_ID));
foreach (var item in _TREA_EQUIP)
{
var EQUIPData = new TreasuryKeyCheckReport()
{
ROW = ++Total,
CUSTODY_MODE = getCSMTtype(types, CUSTODY_MODE),
EQUIP_NAME = item.EQUIP_NAME,
EMP_NAME = "",
AGENT_NAME = "",
MEMO = "",
};
ReportDataList.Add(EQUIPData);
}
}
}
resultsTable.Tables.Add(ReportDataList.ToDataTable());
return(resultsTable);
}
public override DataSet GetData(List <reportParm> parms)
{
//報表資料
List <DepositReportSealData> ReportDataList = new List <DepositReportSealData>();
var resultsTable = new DataSet();
var ReportData = new DepositReportSealData();
string vdept = parms.Where(x => x.key == "vdept").FirstOrDefault()?.value ?? string.Empty;
string vsect = parms.Where(x => x.key == "vsect").FirstOrDefault()?.value ?? string.Empty;
string JobProject = parms.Where(x => x.key == "vJobProject").FirstOrDefault()?.value ?? string.Empty;
string APLY_DT_From = parms.Where(x => x.key == "APLY_DT_From").FirstOrDefault()?.value ?? string.Empty; //庫存日期
string APLY_ODT_From = parms.Where(x => x.key == "APLY_ODT_From").FirstOrDefault()?.value ?? string.Empty;
string APLY_ODT_To = parms.Where(x => x.key == "APLY_ODT_To").FirstOrDefault()?.value ?? string.Empty;
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
var _APLY_DT = TypeTransfer.stringToDateTime(APLY_DT_From).DateToLatestTime();
var _APLY_DT_Date = _APLY_DT.Date;
var dtn = DateTime.Now.Date;
var _APLY_ODT_From = TypeTransfer.stringToDateTimeN(APLY_ODT_From);
var _APLY_ODT_To = TypeTransfer.stringToDateTimeN(APLY_ODT_To).DateToLatestTime();
int TOTAL = 0;
INVENTORY_STATUSs.AddRange(new List <string>()
{
((int)AccessInventoryType._5).ToString(), //預約取出，計庫存
((int)AccessInventoryType._6).ToString(), //已被取出，計庫存
((int)AccessInventoryType._9).ToString()
}); //預約存入，計庫存
//預約取出 , 用印 , 存入用印
var _IS = db.ITEM_SEAL.AsNoTracking() //判斷是否在庫
.Where(x => INVENTORY_STATUSs.Contains(x.INVENTORY_STATUS), _APLY_DT_Date == dtn)
.Where(x =>
(INVENTORY_STATUSs.Contains(x.INVENTORY_STATUS) && x.PUT_DATE_ACCESS <= _APLY_DT) // 在庫 且 存入日期 <= 庫存日期 //(18/12/27 update x.PUT_DATE => x.PUT_DATE_ACCESS)
||
(x.INVENTORY_STATUS == INVENTORY_STATUSg &&
x.PUT_DATE_ACCESS <= _APLY_DT && //(18 / 12 / 27 update x.PUT_DATE => x.PUT_DATE_ACCESS)
_APLY_DT < x.GET_DATE), //存入日期 <= 庫存日期 且 庫存日期 < 取出日期
_APLY_DT_Date != dtn)
.Where(x => x.CHARGE_DEPT == vdept, vdept != "All")
.Where(x => x.CHARGE_SECT == vsect, vsect != "All")
.Where(x => x.PUT_DATE_ACCESS >= _APLY_ODT_From, _APLY_ODT_From != null) //(18 / 12 / 27 update x.PUT_DATE => x.PUT_DATE_ACCESS)
.Where(x => x.PUT_DATE_ACCESS <= _APLY_ODT_To, _APLY_ODT_To != null) //(18 / 12 / 27 update x.PUT_DATE => x.PUT_DATE_ACCESS)
.Where(x => x.TREA_ITEM_NAME == JobProject).ToList();
var depts = new List <VW_OA_DEPT>();
//var types = new List<SYS_CODE>();
using (DB_INTRAEntities dbINTRA = new DB_INTRAEntities())
{
depts = dbINTRA.VW_OA_DEPT.AsNoTracking().Where(x => x.DPT_CD != null).ToList();
}
//types = db.SYS_CODE.AsNoTracking().Where(x => x.CODE !=null).ToList();
foreach (var Sealdata in _IS.OrderBy(x => x.PUT_DATE_ACCESS).ThenByDescending(x => x.LAST_UPDATE_DT) /*.ThenBy(x=>x.CHARGE_DEPT).ThenBy(x=>x.CHARGE_SECT)*/)
{
TOTAL++;
var _CHARGE_DEPT = getEmpName(depts, Sealdata.CHARGE_DEPT);
var _CHARGE_SECT = getEmpName(depts, Sealdata.CHARGE_SECT)?.Replace(_CHARGE_DEPT, "")?.Trim();
ReportData = new DepositReportSealData()
{
ROW = TOTAL,
PUT_DATE = Sealdata.PUT_DATE_ACCESS.dateTimeToStr(),
CHARGE_DEPT = _CHARGE_DEPT,
CHARGE_SECT = _CHARGE_SECT,
SEAL_DESC = Sealdata.SEAL_DESC,
MEMO = Sealdata.MEMO,
CHARGE_DEPT_ID = Sealdata.CHARGE_DEPT,
CHARGE_SECT_ID = Sealdata.CHARGE_SECT
};
ReportDataList.Add(ReportData);
}
}
resultsTable.Tables.Add(ReportDataList.ToDataTable());
return(resultsTable);
}
/// <summary>
/// save A81.A82.A83
/// </summary>
/// <param name="type">(A81 or A82 or A83)</param>
/// <param name="dataModel">Exhibit10Model</param>
/// <returns></returns>
public MSGReturnModel SaveA8(string type, List <Exhibit10Model> dataModel)
{
MSGReturnModel result = new MSGReturnModel();
try
{
List <string> A8Type = new List <string>()
{
Table_Type.A81.ToString(),
Table_Type.A82.ToString(),
Table_Type.A83.ToString()
};
if (!A8Type.Contains(type))
{
result.RETURN_FLAG = false;
result.DESCRIPTION = Message_Type.parameter_Error.GetDescription();
return(result);
}
#region save Moody_Monthly_PD_Info(A81)
if (Table_Type.A81.ToString().Equals(type))
{
if (db.Moody_Monthly_PD_Info.Any())
{
db.Moody_Monthly_PD_Info.RemoveRange(
db.Moody_Monthly_PD_Info); //資料全刪除
}
int id = 1;
foreach (var item in dataModel)
{
DateTime?dt = TypeTransfer.stringToDateTimeN(item.Trailing);
db.Moody_Monthly_PD_Info.Add(
new Moody_Monthly_PD_Info()
{
Id = id,
Trailing_12m_Ending = dt,
Actual_Allcorp =
TypeTransfer.stringToDoubleN(item.Actual_Allcorp),
Baseline_forecast_Allcorp =
TypeTransfer.stringToDoubleN(item.Baseline_forecast_Allcorp),
Pessimistic_Forecast_Allcorp =
TypeTransfer.stringToDoubleN(item.Pessimistic_Forecast_Allcorp),
Actual_SG = TypeTransfer.stringToDoubleN(item.Actual_SG),
Baseline_forecast_SG = TypeTransfer.stringToDoubleN(item.Baseline_forecast_SG),
Pessimistic_Forecast_SG =
TypeTransfer.stringToDoubleN(item.Pessimistic_Forecast_SG),
Data_Year = (dt == null) ? string.Empty : ((DateTime)dt).Year.ToString()
});
id += 1;
}
}
#endregion save Moody_Monthly_PD_Info(A81)
#region save Moody_Quartly_PD_Info(A82)
if (Table_Type.A82.ToString().Equals(type))
{
if (db.Moody_Quartly_PD_Info.Any())
{
db.Moody_Quartly_PD_Info.RemoveRange(
db.Moody_Quartly_PD_Info);
}
int id = 1;
List <Moody_Quartly_PD_Info> allData = new List <Moody_Quartly_PD_Info>();
List <int> months = new List <int>()
{
3, 6, 9, 12
}; //只搜尋3.6.9.12 月份
foreach (var item in dataModel
.Where(x => !string.IsNullOrWhiteSpace(x.Actual_Allcorp) && //要有Actual_Allcorp (排除今年)
months.Contains(DateTime.Parse(x.Trailing).Month)) //只搜尋3.6.9.12 月份
.OrderByDescending(x => x.Trailing)) //排序=>日期大到小
{
DateTime dt = DateTime.Parse(item.Trailing);
string quartly = dt.Year.ToString();
switch (dt.Month) //判斷季別
{
case 3:
quartly += "Q1";
break;
case 6:
quartly += "Q2";
break;
case 9:
quartly += "Q3";
break;
case 12:
quartly += "Q4";
break;
}
double?actualAllcorp = null;
if (!string.IsNullOrWhiteSpace(item.Actual_Allcorp))
{
actualAllcorp = double.Parse(item.Actual_Allcorp);
}
db.Moody_Quartly_PD_Info.Add(new Moody_Quartly_PD_Info()
{
Id = id,
Data_Year = dt.Year.ToString(),
Year_Quartly = quartly,
PD = actualAllcorp
});
id += 1;
}
}
#endregion save Moody_Quartly_PD_Info(A82)
#region save Moody_Predit_PD_Info(A83)
if (Table_Type.A83.ToString().Equals(type))
{
if (db.Moody_Predit_PD_Info.Any())
{
db.Moody_Predit_PD_Info.RemoveRange(db.Moody_Predit_PD_Info);
}
List <Exhibit10Model> models = (from q in dataModel
where !string.IsNullOrWhiteSpace(q.Actual_Allcorp) && //排除掉今年
12.Equals(DateTime.Parse(q.Trailing).Month) //只取12月
select q).ToList();
string maxYear = models.Max(x => DateTime.Parse(x.Trailing)).Year.ToString(); //抓取最大年
string minYear = models.Min(x => DateTime.Parse(x.Trailing)).Year.ToString(); //抓取最小年
double?PD = null;
double PDValue = models.Sum(x => double.Parse(x.Actual_Allcorp)) / models.Count; //計算 PD
if (PDValue > 0)
{
PD = PDValue;
}
//int id = 1;
db.Moody_Predit_PD_Info.Add(new Moody_Predit_PD_Info()
{
Id = 1,
Data_Year = maxYear,
Period = minYear + "-" + maxYear,
PD_TYPE = PD_Type.Past_Year_AVG.ToString(),
PD = PD
});
var dtn = DateTime.Now.Year;
Exhibit10Model model =
dataModel.Where(x => dtn.Equals(DateTime.Parse(x.Trailing).Year) &&
12.Equals(DateTime.Parse(x.Trailing).Month)).FirstOrDefault(); //抓今年又是12月的資料
string baselineForecastAllcorp = string.Empty;
if (model != null)
{
baselineForecastAllcorp = model.Baseline_forecast_Allcorp;
}
PD = null;
if (!string.IsNullOrWhiteSpace(baselineForecastAllcorp))
{
PD = double.Parse(baselineForecastAllcorp);
}
db.Moody_Predit_PD_Info.Add(new Moody_Predit_PD_Info()
{
Id = 2,
Data_Year = maxYear,
Period = dtn.ToString(),
PD_TYPE = PD_Type.Forcast.ToString(),
PD = PD
});
}
#endregion save Moody_Predit_PD_Info(A83)
SaveChange();
result.RETURN_FLAG = true;
result.DESCRIPTION = Message_Type.save_Success.GetDescription(type);
}
catch (DbUpdateException ex)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = Message_Type
.save_Fail.GetDescription(type,
$"message: {ex.Message}" +
$", inner message {ex.InnerException?.InnerException?.Message}");
}
return(result);
}
public override DataSet GetData(List <reportParm> parms)
{
//報表資料
List <DepositReportCAData> ReportDataList = new List <DepositReportCAData>();
var resultsTable = new DataSet();
var ReportData = new DepositReportCAData();
string vdept = parms.Where(x => x.key == "vdept").FirstOrDefault()?.value ?? string.Empty;
string vsect = parms.Where(x => x.key == "vsect").FirstOrDefault()?.value ?? string.Empty;
string JobProject = parms.Where(x => x.key == "vJobProject").FirstOrDefault()?.value ?? string.Empty;
string APLY_DT_From = parms.Where(x => x.key == "APLY_DT_From").FirstOrDefault()?.value ?? string.Empty; //庫存日期
string APLY_ODT_From = parms.Where(x => x.key == "APLY_ODT_From").FirstOrDefault()?.value ?? string.Empty;
string APLY_ODT_To = parms.Where(x => x.key == "APLY_ODT_To").FirstOrDefault()?.value ?? string.Empty;
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
var _APLY_DT = TypeTransfer.stringToDateTime(APLY_DT_From).DateToLatestTime();
var _APLY_DT_Date = _APLY_DT.Date;
var dtn = DateTime.Now.Date;
var _APLY_ODT_From = TypeTransfer.stringToDateTimeN(APLY_ODT_From);
var _APLY_ODT_To = TypeTransfer.stringToDateTimeN(APLY_ODT_To).DateToLatestTime();
int TOTAL = 0;
INVENTORY_STATUSs.AddRange(new List <string>()
{
((int)AccessInventoryType._5).ToString(), //預約取出，計庫存
((int)AccessInventoryType._6).ToString(), //已被取出，計庫存
((int)AccessInventoryType._9).ToString()
}); //預約存入，計庫存
var _IC = db.ITEM_CA.AsNoTracking() //判斷是否在庫
.Where(x => INVENTORY_STATUSs.Contains(x.INVENTORY_STATUS), _APLY_DT_Date == dtn)
.Where(x =>
(INVENTORY_STATUSs.Contains(x.INVENTORY_STATUS) && x.PUT_DATE <= _APLY_DT) // 在庫 且 存入日期 <= 庫存日期
||
(x.INVENTORY_STATUS == INVENTORY_STATUSg &&
x.PUT_DATE <= _APLY_DT &&
_APLY_DT < x.GET_DATE), //存入日期 <= 庫存日期 且 庫存日期 < 取出日期
_APLY_DT_Date != dtn)
.Where(x => x.CHARGE_DEPT == vdept, vdept != "All")
.Where(x => x.CHARGE_SECT == vsect, vsect != "All")
.Where(x => x.PUT_DATE >= _APLY_ODT_From, _APLY_ODT_From != null)
.Where(x => x.PUT_DATE <= _APLY_ODT_To, _APLY_ODT_To != null)
.ToList();
var depts = new List <VW_OA_DEPT>();
var types = new List <SYS_CODE>();
using (DB_INTRAEntities dbINTRA = new DB_INTRAEntities())
{
depts = dbINTRA.VW_OA_DEPT.AsNoTracking().Where(x => x.DPT_CD != null).ToList();
}
types = db.SYS_CODE.AsNoTracking().Where(x => x.CODE != null).ToList();
foreach (var CAdata in _IC /*.OrderBy(x => x.PUT_DATE).ThenBy(x=>x.CHARGE_DEPT)*/.OrderBy(x => x.CHARGE_DEPT).ThenBy(x => x.CHARGE_SECT).ThenBy(x => x.CA_USE).ThenBy(x => x.CA_DESC).ThenBy(x => x.BANK).ThenBy(x => x.CA_NUMBER))
{
TOTAL++;
var _CHARGE_DEPT = getEmpName(depts, CAdata.CHARGE_DEPT);
var _CHARGE_SECT = getEmpName(depts, CAdata.CHARGE_SECT)?.Replace(_CHARGE_DEPT, "")?.Trim();
ReportData = new DepositReportCAData()
{
ROW = TOTAL,
PUT_DATE = CAdata.PUT_DATE.dateTimeToStr(),
CHARGE_DEPT = _CHARGE_DEPT,
CHARGE_SECT = _CHARGE_SECT,
CA_DESC = getCAdesc(types, CAdata.CA_DESC),
CA_USE = getCAuse(types, CAdata.CA_USE),
BANK = CAdata.BANK,
CA_NUMBER = CAdata.CA_NUMBER,
MEMO = CAdata.MEMO,
CHARGE_DEPT_ID = CAdata.CHARGE_DEPT,
CHARGE_SECT_ID = CAdata.CHARGE_SECT
};
ReportDataList.Add(ReportData);
}
}
resultsTable.Tables.Add(ReportDataList.ToDataTable());
return(resultsTable);
}
public override DataSet GetData(List <reportParm> parms)
{
//報表資料
List <DepositReportMargingData> ReportDataList = new List <DepositReportMargingData>();
var resultsTable = new DataSet();
var ReportData = new DepositReportMargingData();
string vdept = parms.Where(x => x.key == "vdept").FirstOrDefault()?.value ?? string.Empty;
string vsect = parms.Where(x => x.key == "vsect").FirstOrDefault()?.value ?? string.Empty;
string JobProject = parms.Where(x => x.key == "vJobProject").FirstOrDefault()?.value ?? string.Empty;
string APLY_DT_From = parms.Where(x => x.key == "APLY_DT_From").FirstOrDefault()?.value ?? string.Empty; //庫存日期
string APLY_ODT_From = parms.Where(x => x.key == "APLY_ODT_From").FirstOrDefault()?.value ?? string.Empty;
string APLY_ODT_To = parms.Where(x => x.key == "APLY_ODT_To").FirstOrDefault()?.value ?? string.Empty;
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
var _APLY_DT = TypeTransfer.stringToDateTime(APLY_DT_From).DateToLatestTime();
var _APLY_DT_Date = _APLY_DT.Date;
var dtn = DateTime.Now.Date;
var _APLY_ODT_From = TypeTransfer.stringToDateTimeN(APLY_ODT_From);
var _APLY_ODT_To = TypeTransfer.stringToDateTimeN(APLY_ODT_To).DateToLatestTime();
INVENTORY_STATUSs.AddRange(new List <string>()
{
((int)AccessInventoryType._5).ToString(), //預約取出，計庫存
((int)AccessInventoryType._6).ToString(), //已被取出，計庫存
((int)AccessInventoryType._9).ToString()
}); //預約存入，計庫存
var _IRD = db.ITEM_REFUNDABLE_DEP.AsNoTracking() //判斷是否在庫
.Where(x => INVENTORY_STATUSs.Contains(x.INVENTORY_STATUS), _APLY_DT_Date == dtn)
.Where(x =>
(INVENTORY_STATUSs.Contains(x.INVENTORY_STATUS) && x.PUT_DATE <= _APLY_DT) // 在庫 且 存入日期 <= 庫存日期
||
(x.INVENTORY_STATUS == INVENTORY_STATUSg &&
x.PUT_DATE <= _APLY_DT &&
_APLY_DT < x.GET_DATE), //存入日期 <= 庫存日期 且 庫存日期 < 取出日期
_APLY_DT_Date != dtn)
.Where(x => x.CHARGE_DEPT == vdept, vdept != "All")
.Where(x => x.CHARGE_SECT == vsect, vsect != "All")
.Where(x => x.PUT_DATE >= _APLY_ODT_From, _APLY_ODT_From != null)
.Where(x => x.PUT_DATE <= _APLY_ODT_To, _APLY_ODT_To != null)
.ToList();
var depts = new List <VW_OA_DEPT>();
var types = new List <SYS_CODE>();
using (DB_INTRAEntities dbINTRA = new DB_INTRAEntities())
{
depts = dbINTRA.VW_OA_DEPT.AsNoTracking().Where(x => x.DPT_CD != null).ToList();
}
types = db.SYS_CODE.AsNoTracking().Where(x => x.CODE != null).ToList();
foreach (var Stockdata in _IRD.OrderBy(x => x.MARGIN_DEP_TYPE).ThenBy(x => x.ITEM_ID).ThenBy(x => x.BOOK_NO).ThenBy(x => x.PUT_DATE).ThenBy(x => x.CHARGE_DEPT).ThenBy(x => x.CHARGE_SECT))
{
var _CHARGE_DEPT = getEmpName(depts, Stockdata.CHARGE_DEPT);
var _CHARGE_SECT = !_CHARGE_DEPT.IsNullOrWhiteSpace() ? getEmpName(depts, Stockdata.CHARGE_SECT)?.Replace(_CHARGE_DEPT, "")?.Trim() : null;
ReportData = new DepositReportMargingData()
{
MARGIN_DEP_TYPE = getMDTtype(types, Stockdata.MARGIN_DEP_TYPE),
ITEM_ID = Stockdata.ITEM_ID,
BOOK_NO = Stockdata.BOOK_NO,
PUT_DATE = Stockdata.PUT_DATE.dateTimeToStr(),
CHARGE_DEPT = _CHARGE_DEPT,
CHARGE_SECT = !_CHARGE_SECT.IsNullOrWhiteSpace() ? _CHARGE_SECT : null,
TRAD_PARTNERS = Stockdata.TRAD_PARTNERS,
AMOUNT = Stockdata.AMOUNT,
WORKPLACE_CODE = Stockdata.WORKPLACE_CODE,
DESCRIPTION = Stockdata.DESCRIPTION,
MEMO = Stockdata.MEMO,
CHARGE_DEPT_ID = Stockdata.CHARGE_DEPT,
CHARGE_SECT_ID = Stockdata.CHARGE_SECT
};
ReportDataList.Add(ReportData);
}
}
resultsTable.Tables.Add(ReportDataList.ToDataTable());
return(resultsTable);
}
public List <TDAApprSearchDetailViewModel> GetSearchDetail(TDAApprSearchViewModel data)
{
List <TDAApprSearchDetailViewModel> result = new List <TDAApprSearchDetailViewModel>();
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
var emps = GetEmps();
var _TREA_ITEM_HIS = db.TREA_ITEM_HIS.AsNoTracking(); //金庫存取作業設定異動檔
var _TREA_EQUIP_HIS = db.TREA_EQUIP_HIS.AsNoTracking(); //金庫設備設定異動檔
var _MAIL_CONTENT_HIS = db.MAIL_CONTENT_HIS.AsNoTracking(); //mail發送內文設定異動檔
var _MAIL_TIME_HIS = db.MAIL_TIME_HIS.AsNoTracking(); //發送時間定義異動檔
var _ITEM_CHARGE_UNIT_HIS = db.ITEM_CHARGE_UNIT_HIS.AsNoTracking(); //保管單位設定異動檔
var _DEP_CHK_ITEM_HIS = db.DEP_CHK_ITEM_HIS.AsNoTracking(); //定存檢核表項目設定異動檔
var _TREA_ITEM = (Ref.DefinitionType.TREA_ITEM).ToString(); // 設定檔項目 => 金庫存取作業設定檔
var _TREA_EQUIP = (Ref.DefinitionType.TREA_EQUIP).ToString(); // 設定檔項目 => 金庫設備設定檔
var _MAIL_CONTENT = (Ref.DefinitionType.MAIL_CONTENT).ToString(); // 設定檔項目 => mail發送內文設定檔
var _MAIL_TIME = (Ref.DefinitionType.MAIL_TIME).ToString(); // 設定檔項目 => 發送時間定義檔
var _ITEM_CHARGE_UNIT = (Ref.DefinitionType.ITEM_CHARGE_UNIT).ToString(); // 設定檔項目 => 保管單位設定檔
var _DEP_CHK_ITEM = (Ref.DefinitionType.DEP_CHK_ITEM).ToString(); // 設定檔項目 => 定存檢核表項目設定檔
DateTime?_vAPLY_DT_S = TypeTransfer.stringToDateTimeN(data.vAPLY_DT_S);
DateTime?_vAPLY_DT_E = TypeTransfer.stringToDateTimeN(data.vAPLY_DT_E).DateToLatestTime();
Dictionary <string, string> DefinitionTypes = new Dictionary <string, string>()
{
};
EnumUtil.GetValues <Ref.DefinitionType>().ToList()
.ForEach(x =>
{
DefinitionTypes.Add(x.ToString(), x.GetDescription());
});
var apprStatus = db.SYS_CODE.AsNoTracking().Where(x => x.CODE_TYPE == "APPR_STATUS").ToList();
if (data.vTDA_Id == "All" || data.vTDA_Id == _TREA_ITEM)
{
//金庫存取作業設定異動檔
var _TIH = _TREA_ITEM_HIS
.Where(x => x.APLY_DATE >= _vAPLY_DT_S, _vAPLY_DT_S != null) //申請日期(起)
.Where(x => x.APLY_DATE <= _vAPLY_DT_E, _vAPLY_DT_E != null) //申請日期(迄)
.Where(x => x.APLY_NO == data.vAplyNo, !data.vAplyNo.IsNullOrWhiteSpace()) //申請單號
.Where(x => x.APLY_UID == data.vAPLY_ID, !data.vAPLY_ID.IsNullOrWhiteSpace()) //申請人
.AsEnumerable()
.Select(x => new TDAApprSearchDetailViewModel()
{
vTDA_Id = _TREA_ITEM,
vTDA_Desc = DefinitionTypes[_TREA_ITEM],
vAply_Dt = x.APLY_DATE != null ? x.APLY_DATE.ToString("yyyy/MM/dd") : null,
vAply_No = x.APLY_NO,
vAply_Uid = x.APLY_UID,
vAply_Uid_Name = emps.FirstOrDefault(y => y.USR_ID == x.APLY_UID)?.EMP_NAME,
vApprFlag = x.APLY_UID != data.vCreateUid,
vAppr_Desc = x.APPR_DESC,
vAppr_Status = apprStatus.FirstOrDefault(y => y.CODE == x.APPR_STATUS)?.CODE_VALUE,
}).ToList();
result.AddRange(_TIH);
}
if (data.vTDA_Id == "All" || data.vTDA_Id == _TREA_EQUIP)
{
//金庫設備設定異動檔
var _TEH = _TREA_EQUIP_HIS
.Where(x => x.APLY_DATE >= _vAPLY_DT_S, _vAPLY_DT_S != null) //申請日期(起)
.Where(x => x.APLY_DATE <= _vAPLY_DT_E, _vAPLY_DT_E != null) //申請日期(迄)
.Where(x => x.APLY_NO == data.vAplyNo, !data.vAplyNo.IsNullOrWhiteSpace()) //申請單號
.Where(x => x.APLY_UID == data.vAPLY_ID, !data.vAPLY_ID.IsNullOrWhiteSpace()) //申請人
.AsEnumerable()
.Select(x => new TDAApprSearchDetailViewModel()
{
vTDA_Id = _TREA_EQUIP,
vTDA_Desc = EnumUtil.GetValues <Ref.DefinitionType>().FirstOrDefault(y => y.ToString() == _TREA_EQUIP).GetDescription(),
vAply_Dt = x.APLY_DATE != null ? x.APLY_DATE.ToString("yyyy/MM/dd") : null,
vAply_No = x.APLY_NO,
vAply_Uid = x.APLY_UID,
vAply_Uid_Name = emps.FirstOrDefault(y => y.USR_ID == x.APLY_UID)?.EMP_NAME,
vApprFlag = x.APLY_UID != data.vCreateUid,
vAppr_Desc = x.APPR_DESC,
vAppr_Status = apprStatus.FirstOrDefault(y => y.CODE == x.APPR_STATUS)?.CODE_VALUE,
}).ToList();
result.AddRange(_TEH);
}
if (data.vTDA_Id == "All" || data.vTDA_Id == _MAIL_CONTENT)
{
//mail發送內文設定異動檔
var _MCH = _MAIL_CONTENT_HIS
.Where(x => x.APLY_DATE >= _vAPLY_DT_S, _vAPLY_DT_S != null) //申請日期(起)
.Where(x => x.APLY_DATE <= _vAPLY_DT_E, _vAPLY_DT_E != null) //申請日期(迄)
.Where(x => x.APLY_NO == data.vAplyNo, !data.vAplyNo.IsNullOrWhiteSpace()) //申請單號
.Where(x => x.APLY_UID == data.vAPLY_ID, !data.vAPLY_ID.IsNullOrWhiteSpace()) //申請人
.AsEnumerable()
.Select(x => new TDAApprSearchDetailViewModel()
{
vTDA_Id = _MAIL_CONTENT,
vTDA_Desc = EnumUtil.GetValues <Ref.DefinitionType>().FirstOrDefault(y => y.ToString() == _MAIL_CONTENT).GetDescription(),
vAply_Dt = x.APLY_DATE != null ? x.APLY_DATE.ToString("yyyy/MM/dd") : null,
vAply_No = x.APLY_NO,
vAply_Uid = x.APLY_UID,
vAply_Uid_Name = emps.FirstOrDefault(y => y.USR_ID == x.APLY_UID)?.EMP_NAME,
vApprFlag = x.APLY_UID != data.vCreateUid,
vAppr_Desc = x.APPR_DESC,
vAppr_Status = apprStatus.FirstOrDefault(y => y.CODE == x.APPR_STATUS)?.CODE_VALUE,
}).ToList();
result.AddRange(_MCH);
}
if (data.vTDA_Id == "All" || data.vTDA_Id == _MAIL_TIME)
{
//發送時間定義異動檔
var _MTH = _MAIL_TIME_HIS
.Where(x => x.APLY_DATE >= _vAPLY_DT_S, _vAPLY_DT_S != null) //申請日期(起)
.Where(x => x.APLY_DATE <= _vAPLY_DT_E, _vAPLY_DT_E != null) //申請日期(迄)
.Where(x => x.APLY_NO == data.vAplyNo, !data.vAplyNo.IsNullOrWhiteSpace()) //申請單號
.Where(x => x.APLY_UID == data.vAPLY_ID, !data.vAPLY_ID.IsNullOrWhiteSpace()) //申請人
.AsEnumerable()
.Select(x => new TDAApprSearchDetailViewModel()
{
vTDA_Id = _MAIL_TIME,
vTDA_Desc = EnumUtil.GetValues <Ref.DefinitionType>().FirstOrDefault(y => y.ToString() == _MAIL_TIME).GetDescription(),
vAply_Dt = x.APLY_DATE != null ? x.APLY_DATE.ToString("yyyy/MM/dd") : null,
vAply_No = x.APLY_NO,
vAply_Uid = x.APLY_UID,
vAply_Uid_Name = emps.FirstOrDefault(y => y.USR_ID == x.APLY_UID)?.EMP_NAME,
vApprFlag = x.APLY_UID != data.vCreateUid,
vAppr_Desc = x.APPR_DESC,
vAppr_Status = apprStatus.FirstOrDefault(y => y.CODE == x.APPR_STATUS)?.CODE_VALUE,
}).ToList();
result.AddRange(_MTH);
}
if (data.vTDA_Id == "All" || data.vTDA_Id == _ITEM_CHARGE_UNIT)
{
//保管單位設定異動檔
var _ICUH = _ITEM_CHARGE_UNIT_HIS
.Where(x => x.APLY_DATE >= _vAPLY_DT_S, _vAPLY_DT_S != null) //申請日期(起)
.Where(x => x.APLY_DATE <= _vAPLY_DT_E, _vAPLY_DT_E != null) //申請日期(迄)
.Where(x => x.APLY_NO == data.vAplyNo, !data.vAplyNo.IsNullOrWhiteSpace()) //申請單號
.Where(x => x.APLY_UID == data.vAPLY_ID, !data.vAPLY_ID.IsNullOrWhiteSpace()) //申請人
.AsEnumerable()
.Select(x => new TDAApprSearchDetailViewModel()
{
vTDA_Id = _ITEM_CHARGE_UNIT,
vTDA_Desc = EnumUtil.GetValues <Ref.DefinitionType>().FirstOrDefault(y => y.ToString() == _ITEM_CHARGE_UNIT).GetDescription(),
vAply_Dt = x.APLY_DATE != null ? x.APLY_DATE.ToString("yyyy/MM/dd") : null,
vAply_No = x.APLY_NO,
vAply_Uid = x.APLY_UID,
vAply_Uid_Name = emps.FirstOrDefault(y => y.USR_ID == x.APLY_UID)?.EMP_NAME,
vApprFlag = x.APLY_UID != data.vCreateUid,
vAppr_Desc = x.APPR_DESC,
vAppr_Status = apprStatus.FirstOrDefault(y => y.CODE == x.APPR_STATUS)?.CODE_VALUE,
}).ToList();
result.AddRange(_ICUH);
}
if (data.vTDA_Id == "All" || data.vTDA_Id == _DEP_CHK_ITEM)
{
//定存檢核表項目設定異動檔
var _DCIH = _DEP_CHK_ITEM_HIS
.Where(x => x.APLY_DATE >= _vAPLY_DT_S, _vAPLY_DT_S != null) //申請日期(起)
.Where(x => x.APLY_DATE <= _vAPLY_DT_E, _vAPLY_DT_E != null) //申請日期(迄)
.Where(x => x.APLY_NO == data.vAplyNo, !data.vAplyNo.IsNullOrWhiteSpace()) //申請單號
.Where(x => x.APLY_UID == data.vAPLY_ID, !data.vAPLY_ID.IsNullOrWhiteSpace()) //申請人
.AsEnumerable()
.Select(x => new TDAApprSearchDetailViewModel()
{
vTDA_Id = _DEP_CHK_ITEM,
vTDA_Desc = EnumUtil.GetValues <Ref.DefinitionType>().FirstOrDefault(y => y.ToString() == _DEP_CHK_ITEM).GetDescription(),
vAply_Dt = x.APLY_DATE != null ? x.APLY_DATE.ToString("yyyy/MM/dd") : null,
vAply_No = x.APLY_NO,
vAply_Uid = x.APLY_UID,
vAply_Uid_Name = emps.FirstOrDefault(y => y.USR_ID == x.APLY_UID)?.EMP_NAME,
vApprFlag = x.APLY_UID != data.vCreateUid,
vAppr_Desc = x.APPR_DESC,
vAppr_Status = apprStatus.FirstOrDefault(y => y.CODE == x.APPR_STATUS)?.CODE_VALUE,
}).ToList();
result.AddRange(_DCIH);
}
}
result = result.Distinct(new TDAApprSearchDetailViewModel_Comparer()).OrderBy(x => x.vAply_No).ToList();
return(result);
}
/// <summary>
/// save A59 (save A5 & A58)
/// </summary>
/// <param name="dataModel">A59ViewModel</param>
/// <returns></returns>
public MSGReturnModel saveA59(List <A59ViewModel> dataModel)
{
MSGReturnModel result = new MSGReturnModel();
if (!dataModel.Any())
{
result.RETURN_FLAG = false;
result.DESCRIPTION = Message_Type.parameter_Error
.GetDescription(Table_Type.A59.ToString());
return(result);
}
DateTime startTime = DateTime.Now;
string startDt = startTime.ToString("yyyy/MM/dd");
string sql = string.Empty;
dataModel.ForEach(x =>
{
var pros = x.GetType().GetProperties()
.Skip(15).TakeWhile(z => z.Name != "Processing_Date").ToList();
var rateInfo = pros.Where(i => !i.GetValue(x).ToString().IsNullOrWhiteSpace() &&
!i.Name.EndsWith("DT")).FirstOrDefault();
var dtInfo = pros.Where(i => !i.GetValue(x).ToString().IsNullOrWhiteSpace() &&
i.Name.EndsWith("DT")).FirstOrDefault();
string rate = null; //Rate
if (rateInfo != null)
{
rate = forRating(rateInfo.GetValue(x).ToString());
}
DateTime?rateDate = null; //RateDate
if (dtInfo != null)
{
rateDate = TypeTransfer.stringToDateTimeN(dtInfo.GetValue(x).ToString(), 8);
}
string rateDt = rateDate.HasValue ? rateDate.Value.ToString("yyyy/MM/dd") : null;
if (!rate.IsNullOrWhiteSpace())
{
var ratingType = x.Rating_Type.Equals(Rating_Type.A.GetDescription()) ? "1" : "2";
#region Save A57
sql += $@"
Update Bond_Rating_Info
Set Rating = '{rate}',
Rating_Date = '{rateDt}',
Grade_Adjust = GMooInfo.Grade_Adjust,
Fill_up_Date = '{startDt}',
Fill_up_YN = 'Y'
From Bond_Rating_Info BR_Info --A57
Left Join Grade_Mapping_Info GMapInfo --A52
on BR_Info.Rating_Org = GMapInfo.Rating_Org
AND GMapInfo.Rating = '{rate}'
Left Join Grade_Moody_Info GMooInfo --A51
on GMapInfo.PD_Grade = GMooInfo.PD_Grade
Where BR_Info.Rating_Type = '{ratingType}'
AND BR_Info.Bond_Number = '{x.Bond_Number}'
AND BR_Info.Lots = '{x.Lots}'
AND BR_Info.Portfolio_Name = '{x.Portfolio_Name}' ";
if (ratingType == "1")
{
sql += $" AND BR_Info.Report_Date = '{x.Report_Date}' ; ";
}
if (ratingType == "2")
{
sql += $" AND BR_Info.Origination_Date = '{x.Origination_Date}' ; ";
}
#endregion
#region Save A58
sql +=
$@" WITH TEMP AS (
Select TOP 1 Grade_Adjust
From Bond_Rating_Info BR_Info
Where BR_Info.Rating_Type = '{ratingType}'
AND BR_Info.Bond_Number = '{x.Bond_Number}'
AND BR_Info.Lots = '{x.Lots}'
AND BR_Info.Portfolio_Name = '{x.Portfolio_Name}' ";
if (ratingType == "1")
{
sql += $" AND BR_Info.Report_Date = '{x.Report_Date}' ";
}
if (ratingType == "2")
{
sql += $" AND BR_Info.Origination_Date = '{x.Origination_Date}' ";
}
sql += $@" )
Update Bond_Rating_Summary
Set Grade_Adjust = TEMP.Grade_Adjust,
Processing_Date = CASE WHEN TEMP.Grade_Adjust is null
THEN null else '{startDt}' END
from Bond_Rating_Summary BR_Summary,TEMP
Where BR_Summary.Rating_Type = '{ratingType}'
AND BR_Summary.Bond_Number = '{x.Bond_Number}'
AND BR_Summary.Lots = '{x.Lots}'
AND BR_Summary.Portfolio_Name = '{x.Portfolio_Name}' ";
if (ratingType == "1")
{
sql += $" AND BR_Summary.Report_Date = '{x.Report_Date}' ; ";
}
if (ratingType == "2")
{
sql += $" AND BR_Summary.Origination_Date = '{x.Origination_Date}' ; ";
}
#endregion
}
});
try
{
db.Database.ExecuteSqlCommand(sql);
result.RETURN_FLAG = true;
result.DESCRIPTION = Message_Type.save_Success.GetDescription();
}
catch (DbUpdateException ex)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = Message_Type
.save_Fail.GetDescription(Table_Type.A59.ToString(),
$"message: {ex.Message}" +
$", inner message {ex.InnerException?.InnerException?.Message}");
}
return(result);
}
public override DataSet GetData(List <reportParm> parms)
{
//報表資料
List <DepositReportSTOCKData> ReportDataList = new List <DepositReportSTOCKData>();
var resultsTable = new DataSet();
var ReportData = new DepositReportSTOCKData();
string vdept = parms.Where(x => x.key == "vdept").FirstOrDefault()?.value ?? string.Empty;
string vsect = parms.Where(x => x.key == "vsect").FirstOrDefault()?.value ?? string.Empty;
string BOOK_NO = parms.Where(x => x.key == "vName").FirstOrDefault()?.value ?? string.Empty;
string JobProject = parms.Where(x => x.key == "vJobProject").FirstOrDefault()?.value ?? string.Empty;
string APLY_DT_From = parms.Where(x => x.key == "APLY_DT_From").FirstOrDefault()?.value ?? string.Empty; //庫存日期
string APLY_ODT_From = parms.Where(x => x.key == "APLY_ODT_From").FirstOrDefault()?.value ?? string.Empty;
string APLY_ODT_To = parms.Where(x => x.key == "APLY_ODT_To").FirstOrDefault()?.value ?? string.Empty;
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
var _APLY_DT = TypeTransfer.stringToDateTime(APLY_DT_From).DateToLatestTime();
var _APLY_DT_Date = _APLY_DT.Date;
var dtn = DateTime.Now.Date;
var _APLY_ODT_From = TypeTransfer.stringToDateTimeN(APLY_ODT_From);
var _APLY_ODT_To = TypeTransfer.stringToDateTimeN(APLY_ODT_To).DateToLatestTime();
var _IS = new List <ITEM_STOCK>();
_IS = db.ITEM_STOCK.AsNoTracking()//判斷是否在庫
.Where(x => INVENTORY_STATUSs.Contains(x.INVENTORY_STATUS), _APLY_DT_Date == dtn)
.Where(x =>
(INVENTORY_STATUSs.Contains(x.INVENTORY_STATUS) && x.PUT_DATE <= _APLY_DT) // 在庫 且 存入日期 <= 庫存日期
||
(x.INVENTORY_STATUS == INVENTORY_STATUSg &&
x.PUT_DATE <= _APLY_DT &&
_APLY_DT < x.GET_DATE), //存入日期 <= 庫存日期 且 庫存日期 < 取出日期
_APLY_DT_Date != dtn)
.Where(x => x.CHARGE_DEPT == vdept, vdept != "All")
.Where(x => x.CHARGE_SECT == vsect, vsect != "All")
.Where(x => x.PUT_DATE >= _APLY_ODT_From, _APLY_ODT_From != null)
.Where(x => x.PUT_DATE <= _APLY_ODT_To, _APLY_ODT_To != null)
.Where(x => x.GROUP_NO.ToString() == BOOK_NO, BOOK_NO != "All") //判斷為全部或單一
.ToList();
var depts = new List <VW_OA_DEPT>();
var area = new List <SYS_CODE>();
var types = new List <SYS_CODE>();
var book = new List <ITEM_BOOK>();
using (DB_INTRAEntities dbINTRA = new DB_INTRAEntities())
{
depts = dbINTRA.VW_OA_DEPT.AsNoTracking().Where(x => x.DPT_CD != null).ToList();
}
area = db.SYS_CODE.AsNoTracking().Where(x => x.CODE != null && x.CODE_TYPE == "STOCK_AREA").ToList();
types = db.SYS_CODE.AsNoTracking().Where(x => x.CODE != null && x.CODE_TYPE == "STOCK_TYPE").ToList();
book = db.ITEM_BOOK.AsNoTracking().Where(x => x.GROUP_NO.ToString() != null).ToList();
foreach (var STOCKdata in _IS.OrderBy(x => x.GROUP_NO).ThenBy(x => x.PUT_DATE).ThenBy(x => x.TREA_BATCH_NO).ThenBy(x => x.CHARGE_DEPT).ThenBy(x => x.CHARGE_SECT).ThenBy(x => x.GROUP_NO).ThenBy(x => x.STOCK_NO_PREAMBLE).ThenBy(x => x.STOCK_NO_B))
{
var _CHARGE_DEPT = getEmpName(depts, STOCKdata.CHARGE_DEPT);
var _CHARGE_SECT = getEmpName(depts, STOCKdata.CHARGE_SECT)?.Replace(_CHARGE_DEPT, "")?.Trim();
ReportData = new DepositReportSTOCKData()
{
PUT_DATE = STOCKdata.PUT_DATE.dateTimeToStr(),
STOCK_NO_B = STOCKdata.STOCK_NO_B,
STOCK_NO_E = STOCKdata.STOCK_NO_E,
STOCK_CNT = STOCKdata.STOCK_CNT,
DENOMINATION = STOCKdata.DENOMINATION,
NUMBER_OF_SHARES = STOCKdata.NUMBER_OF_SHARES,
AREA = area.FirstOrDefault(z => z.CODE == getArea(book, STOCKdata.GROUP_NO.ToString()))?.CODE_VALUE,
STOCK_TYPE = types.FirstOrDefault(z => z.CODE == STOCKdata.STOCK_TYPE)?.CODE_VALUE,
BATCH_NO = STOCKdata.TREA_BATCH_NO.ToString(),
STOCK_NO_PREAMBLE = STOCKdata.STOCK_NO_PREAMBLE,
AMOUNT_PER_SHARE = STOCKdata.AMOUNT_PER_SHARE,
SINGLE_NUMBER_OF_SHARES = STOCKdata.SINGLE_NUMBER_OF_SHARES,
CHARGE_DEPT = _CHARGE_DEPT,
CHARGE_SECT = _CHARGE_SECT,
MEMO = STOCKdata.MEMO,
BOOK_NO = STOCKdata.GROUP_NO.ToString(),
NAME = getName(book, STOCKdata.GROUP_NO.ToString()),
CHARGE_DEPT_ID = STOCKdata.CHARGE_DEPT,
CHARGE_SECT_ID = STOCKdata.CHARGE_SECT
};
ReportDataList.Add(ReportData);
}
}
resultsTable.Tables.Add(ReportDataList.ToDataTable());
return(resultsTable);
}
/// <summary>
/// 異動紀錄查詢資料
/// </summary>
/// <param name="searchModel">查詢ViwModel</param>
/// <param name="aply_No">申請單號</param>
/// <returns></returns>
public IEnumerable <ITinItem> GetChangeRecordSearchData(ITinItem searchModel, string aply_No = null)
{
var searchData = (TreasuryMailContentHistorySearchViewModel)searchModel;
List <TreasuryMailContentHistoryViewModel> result = new List <TreasuryMailContentHistoryViewModel>();
//if (searchData == null)
// return result;
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
var emps = GetEmps();
var _CODE_FUNC = db.CODE_FUNC.AsNoTracking().ToList();
var _sysCodes = db.SYS_CODE.AsNoTracking().ToList();
var _EXEC_ACTION = _sysCodes
.Where(x => x.CODE_TYPE == "EXEC_ACTION").ToList();
var _Is_Disabled = _sysCodes
.Where(x => x.CODE_TYPE == "IS_DISABLED").ToList();
var _Appr_Status = _sysCodes
.Where(x => x.CODE_TYPE == "APPR_STATUS").ToList();
DateTime?_AplyDate = null;
if (searchData != null)
{
_AplyDate = TypeTransfer.stringToDateTimeN(searchData.vAply_Date);
}
var his = db.MAIL_RECEIVE_HIS.AsNoTracking()
.Where(x => x.MAIL_CONTENT_ID == searchData.vMAIL_CONTENT_ID,
searchData != null &&
!searchData.vMAIL_CONTENT_ID.IsNullOrWhiteSpace() &&
searchData.vMAIL_CONTENT_ID != "All")
.Where(x => x.APLY_NO == aply_No, !aply_No.IsNullOrWhiteSpace())
.ToList();
result = db.MAIL_CONTENT_HIS.AsNoTracking()
.Where(x => x.MAIL_CONTENT_ID == searchData.vMAIL_CONTENT_ID,
searchData != null &&
!searchData.vMAIL_CONTENT_ID.IsNullOrWhiteSpace() &&
searchData.vMAIL_CONTENT_ID != "All")
.Where(x => x.APPR_STATUS == searchData.vAPPR_STATUS, searchData != null && !searchData.vAPPR_STATUS.IsNullOrWhiteSpace() && searchData.vAPPR_STATUS != "All")
.Where(x => DbFunctions.TruncateTime(x.APLY_DATE) == _AplyDate, _AplyDate != null)
.Where(x => x.APLY_NO == aply_No, !aply_No.IsNullOrWhiteSpace())
.AsEnumerable()
.Select(x => new TreasuryMailContentHistoryViewModel()
{
APLY_NO = x.APLY_NO,
APLY_DT = TypeTransfer.dateTimeToString(x.APLY_DATE, false),
APLY_UID = emps.FirstOrDefault(y => y.USR_ID == x.APLY_UID)?.EMP_NAME?.Trim(),
vIS_DISABLED_B = _Is_Disabled.FirstOrDefault(y => y.CODE == x.IS_DISABLED_B)?.CODE_VALUE,
vIS_DISABLED = _Is_Disabled.FirstOrDefault(y => y.CODE == x.IS_DISABLED)?.CODE_VALUE,
vMAIL_CONTENT_B = x.MAIL_CONTENT_B,
vMAIL_CONTENT = x.MAIL_CONTENT,
vMAIL_SUBJECT_B = x.MAIL_SUBJECT_B,
vMAIL_SUBJECT = x.MAIL_SUBJECT,
vAPPR_DESC = x.APPR_DESC,
vAPPR_STATUS = _Appr_Status.FirstOrDefault(y => y.CODE == x.APPR_STATUS)?.CODE_VALUE,
FunFlag = his.Any(y => y.APLY_NO == x.APLY_NO && x.MAIL_CONTENT_ID == y.MAIL_CONTENT_ID) ? "Y" : "N",
subData = his.Where(y => y.APLY_NO == x.APLY_NO && x.MAIL_CONTENT_ID == y.MAIL_CONTENT_ID)
.AsEnumerable()
.Select(z => new TreasuryMailReceivelViewModel()
{
FUNC_ID = z.FUNC_ID,
FUNC_ID_Name = _CODE_FUNC.FirstOrDefault(y => y.FUNC_ID == z.FUNC_ID)?.FUNC_NAME,
vStatus = z.EXEC_ACTION,
vStatus_D = _EXEC_ACTION.FirstOrDefault(y => y.CODE == z.EXEC_ACTION)?.CODE_VALUE,
}).ToList()
}).OrderBy(x => x.APLY_NO).ToList();
}
return(result);
}
public override DataSet GetData(List <reportParm> parms)
{
//報表資料
List <DepositReportDEPOSIT_DEP_ORDER_M_Data> ReportDataList = new List <DepositReportDEPOSIT_DEP_ORDER_M_Data>();
List <DepositReportDEPOSIT_DEP_ORDER_M_Data> ReportDataList2 = new List <DepositReportDEPOSIT_DEP_ORDER_M_Data>();
var resultsTable = new DataSet();
var ReportData = new DepositReportDEPOSIT_DEP_ORDER_M_Data();
string vdept = parms.Where(x => x.key == "vdept").FirstOrDefault()?.value ?? string.Empty; //權責部門
string vsect = parms.Where(x => x.key == "vsect").FirstOrDefault()?.value ?? string.Empty; //權責科別
string JobProject = parms.Where(x => x.key == "vJobProject").FirstOrDefault()?.value ?? string.Empty; //庫存表名稱
string APLY_DT_From = parms.Where(x => x.key == "APLY_DT_From").FirstOrDefault()?.value ?? string.Empty; //庫存日期
string APLY_ODT_From = parms.Where(x => x.key == "APLY_ODT_From").FirstOrDefault()?.value ?? string.Empty; //入庫日期(起)
string APLY_ODT_To = parms.Where(x => x.key == "APLY_ODT_To").FirstOrDefault()?.value ?? string.Empty; //入庫日期(迄)
string TRAD_Partners = parms.Where(x => x.key == "vTRAD_Partners").FirstOrDefault()?.value ?? string.Empty; //交易對象
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
var _APLY_DT = TypeTransfer.stringToDateTime(APLY_DT_From).DateToLatestTime();
var _APLY_DT_Date = _APLY_DT.Date;
var dtn = DateTime.Now.Date;
var dtnStr = dtn.ToString("yyyy/MM/dd");
var _APLY_ODT_From = TypeTransfer.stringToDateTimeN(APLY_ODT_From);
var _APLY_ODT_To = TypeTransfer.stringToDateTimeN(APLY_ODT_To).DateToLatestTime();
var _DEP_SET_QUALITY = db.ITEM_DEP_ORDER_M.AsNoTracking().Select(x => x.DEP_SET_QUALITY).ToList();
List <ITEM_DEP_ORDER_M> _IDOM = new List <ITEM_DEP_ORDER_M>();
var _IDOM_N = db.ITEM_DEP_ORDER_M.AsNoTracking()
.Where(x => INVENTORY_STATUSs.Contains(x.INVENTORY_STATUS), _APLY_DT_Date == dtn)
.Where(x =>
(INVENTORY_STATUSs.Contains(x.INVENTORY_STATUS) && x.PUT_DATE <= _APLY_DT) // 在庫 且 存入日期 <= 庫存日期
||
(x.INVENTORY_STATUS == INVENTORY_STATUSg &&
x.PUT_DATE <= _APLY_DT &&
_APLY_DT < x.GET_DATE), //存入日期 <= 庫存日期 且 庫存日期 < 取出日期
_APLY_DT_Date != dtn)
.Where(x => x.TRAD_PARTNERS == TRAD_Partners, TRAD_Partners != "All")
.Where(x => x.CHARGE_DEPT == vdept, vdept != "All")
.Where(x => x.CHARGE_SECT == vsect, vsect != "All")
.Where(x => x.PUT_DATE >= _APLY_ODT_From, _APLY_ODT_From != null)
.Where(x => x.PUT_DATE <= _APLY_ODT_To, _APLY_ODT_To != null)
.Where(x => x.DEP_SET_QUALITY == "N") //設質否等於N
.ToList();
_IDOM.AddRange(_IDOM_N);
var _IDOM_Y = db.ITEM_DEP_ORDER_M.AsNoTracking()
.Where(x => x.PUT_DATE <= _APLY_DT && _APLY_DT < x.TRANS_EXPIRY_DATE)
//存入日期 <= 庫存日期 且 庫存日期 < 轉期後到期日
.Where(x => x.TRAD_PARTNERS == TRAD_Partners, TRAD_Partners != "All")
.Where(x => x.CHARGE_DEPT == vdept, vdept != "All")
.Where(x => x.CHARGE_SECT == vsect, vsect != "All")
.Where(x => x.PUT_DATE >= _APLY_ODT_From, _APLY_ODT_From != null)
.Where(x => x.PUT_DATE <= _APLY_ODT_To, _APLY_ODT_To != null)
.Where(x => x.DEP_SET_QUALITY == "Y") //設質否等於Y
.ToList();
_IDOM.AddRange(_IDOM_Y);
var _IDOM_ItemIDs = _IDOM.Select(x => x.ITEM_ID).ToList();
var _ITEM_DEP_ORDER_D = db.ITEM_DEP_ORDER_D.AsNoTracking()
.Where(x => _IDOM_ItemIDs.Contains(x.ITEM_ID)).ToList();
var depts = new List <VW_OA_DEPT>();
var types = new List <SYS_CODE>();
using (DB_INTRAEntities dbINTRA = new DB_INTRAEntities())
{
depts = dbINTRA.VW_OA_DEPT.AsNoTracking().Where(x => x.DPT_CD != null).ToList();
}
types = db.SYS_CODE.AsNoTracking().Where(x => x.CODE != null).ToList();
foreach (var Stockdata in _IDOM
//.OrderByDescending(x => x.CURRENCY != "NTD")
//.ThenBy(x => x.CURRENCY)
//.ThenBy(x => x.DEP_TYPE)
//.ThenByDescending(x => x.COMMIT_DATE)
//.ThenBy(x => x.DEP_SET_QUALITY)
)
{
ReportDataList.Add(new DepositReportDEPOSIT_DEP_ORDER_M_Data()
{
ITEMID = Stockdata.ITEM_ID,
PUT_DATE = Stockdata.PUT_DATE?.dateTimeToStr(),
COMMIT_DATE = Stockdata.COMMIT_DATE.dateTimeToStr(),
EXPIRY_DATE = Stockdata.EXPIRY_DATE.dateTimeToStr(),
TRAD_PARTNERS = Stockdata.TRAD_PARTNERS,
CURRENCY = Stockdata.CURRENCY,
CURRENCY_Flag = Stockdata.CURRENCY == "NTD" ? "台幣" : "外幣",
DEP_TYPE = getDEPName(types, Stockdata.DEP_TYPE),
INTEREST_RATE = Stockdata.INTEREST_RATE,
MEMO = Stockdata.MEMO,
CHARGE_DEPT_ID = Stockdata.CHARGE_DEPT,
CHARGE_SECT_ID = Stockdata.CHARGE_SECT,
DEP_SET_QUALITY = Stockdata.DEP_SET_QUALITY,
});
}
//群組 : 類別 & 交易類型 群組
foreach (var group in ReportDataList.GroupBy(x => new { x.CURRENCY_Flag, x.DEP_TYPE }).OrderBy(x => x.Key.CURRENCY_Flag))
{
Dictionary <string, decimal> _data = new Dictionary <string, decimal>();
//201905220094-00 190524 Edited by Bianco 新增張數小計
Dictionary <string, int> _data_CNT = new Dictionary <string, int>();
#region 排序一 庫存日=承作日期時,庫存日當日承作的定期存單排在最上面 再依到期日先後/登打順序排列
int _pk_id = 0;
foreach (var item in group.Where(x => x.DEP_SET_QUALITY == "N" && x.PUT_DATE == x.COMMIT_DATE && x.COMMIT_DATE == APLY_DT_From)
.OrderBy(x => x.PUT_DATE == dtnStr).ThenBy(x => x.EXPIRY_DATE).ThenBy(x => x.ITEMID))
{
_pk_id = _pk_id + 1;
//var _setData = setData(_ITEM_DEP_ORDER_D, item);
var _setData = setData(_ITEM_DEP_ORDER_D, item, _pk_id);
ReportDataList2.AddRange(_setData.Item1);
string key = $@"{item.CURRENCY},{item.DEP_SET_QUALITY}"; //幣別,設質否
decimal value = 0M;
int count = 0; //201905220094-00 190524 Edited by Bianco 新增張數小計
if (_data.TryGetValue(key, out value))
{
value += _setData.Item2;
_data[key] = value;
}
else
{
_data.Add(key, _setData.Item2);
}
//201905220094-00 190524 Edited by Bianco 新增張數小計
if (_data_CNT.TryGetValue(key, out count))
{
count += _setData.Item3;
_data_CNT[key] = count;
}
else
{
_data_CNT.Add(key, _setData.Item3);
}
}
#endregion 排序一 庫存日=承作日期時,庫存日當日承作的定期存單排在最上面 再依到期日先後/登打順序排列
#region 排序二 其他的定期存單列示在中間區塊 再依到期日/承作日/登打順序排列
_pk_id = 0;
foreach (var item in group.Where(x => x.DEP_SET_QUALITY == "N" && (x.PUT_DATE != x.COMMIT_DATE || x.COMMIT_DATE != APLY_DT_From))
.OrderBy(x => x.EXPIRY_DATE).ThenBy(x => x.COMMIT_DATE).ThenBy(x => x.ITEMID))
{
_pk_id = _pk_id + 1;
//var _setData = setData(_ITEM_DEP_ORDER_D, item);
var _setData = setData(_ITEM_DEP_ORDER_D, item, _pk_id);
ReportDataList2.AddRange(_setData.Item1);
string key = $@"{item.CURRENCY},{item.DEP_SET_QUALITY}"; //幣別,設質否
decimal value = 0M;
int count = 0; //201905220094-00 190524 Edited by Bianco 新增張數小計
if (_data.TryGetValue(key, out value))
{
value += _setData.Item2;
_data[key] = value;
}
else
{
_data.Add(key, _setData.Item2);
}
//201905220094-00 190524 Edited by Bianco 新增張數小計
if (_data_CNT.TryGetValue(key, out count))
{
count += _setData.Item3;
_data_CNT[key] = count;
}
else
{
_data_CNT.Add(key, _setData.Item3);
}
}
#endregion 排序二 其他的定期存單列示在中間區塊 再依到期日/承作日/登打順序排列
#region 排序三 質押存單排在報表最下面 再承作日期/到期日/登打順序排列
foreach (var item in group.Where(x => x.DEP_SET_QUALITY == "Y")
.OrderBy(x => x.COMMIT_DATE).ThenBy(x => x.EXPIRY_DATE).ThenBy(x => x.ITEMID))
{
_pk_id = _pk_id + 1;
//var _setData = setData(_ITEM_DEP_ORDER_D, item);
var _setData = setData(_ITEM_DEP_ORDER_D, item, _pk_id);
ReportDataList2.AddRange(_setData.Item1);
string key = $@"{item.CURRENCY},{item.DEP_SET_QUALITY}"; //幣別,設質否
decimal value = 0M;
int count = 0; //201905220094-00 190524 Edited by Bianco 新增張數小計
if (_data.TryGetValue(key, out value))
{
value += _setData.Item2;
_data[key] = value;
}
else
{
_data.Add(key, _setData.Item2);
}
//201905220094-00 190524 Edited by Bianco 新增張數小計
if (_data_CNT.TryGetValue(key, out count))
{
count += _setData.Item3;
_data_CNT[key] = count;
}
else
{
_data_CNT.Add(key, _setData.Item3);
}
}
#endregion 排序三 質押存單排在報表最下面 再承作日期/到期日/登打順序排列
//排序 : 承作日期(降冪)+設質否(按N、Y順序)
//foreach (var item in group.OrderByDescending(x => x.CURRENCY != "NTD").ThenByDescending(x => x.COMMIT_DATE).ThenBy(x => x.DEP_SET_QUALITY))
//{
// decimal total_DENOMINATION = 0M;
// List<DepositReportDEPOSIT_DEP_ORDER_M_Data> tempData =
// new List<DepositReportDEPOSIT_DEP_ORDER_M_Data>();
// //明細
// foreach (var i in _ITEM_DEP_ORDER_D.Where(x => x.ITEM_ID == item.ITEMID).OrderBy(x => x.DATA_SEQ))
// {
// ReportData = new DepositReportDEPOSIT_DEP_ORDER_M_Data()
// {
// TYPE = "1",
// ITEMID = item.ITEMID,
// COMMIT_DATE = item.COMMIT_DATE,
// EXPIRY_DATE = item.EXPIRY_DATE,
// TRAD_PARTNERS = item.TRAD_PARTNERS,
// CURRENCY = item.CURRENCY,
// CURRENCY_Flag = item.CURRENCY_Flag,
// DEP_TYPE = item.DEP_TYPE,
// INTEREST_RATE = item.INTEREST_RATE,
// DEP_NO_B = i.DEP_NO_B,
// DEP_NO_E = i.DEP_NO_E,
// DEP_CNT = i.DEP_CNT,
// DENOMINATION = i.DENOMINATION,
// SUBTOTAL_DENOMINATION = i.SUBTOTAL_DENOMINATION,
// //SUMTOTAL_DENOMINATION = 0,
// MEMO = item.MEMO,
// CHARGE_DEPT_ID = item.CHARGE_DEPT,
// CHARGE_SECT_ID = item.CHARGE_SECT,
// DEP_SET_QUALITY = item.DEP_SET_QUALITY,
// };
// total_DENOMINATION += ReportData.SUBTOTAL_DENOMINATION;
// tempData.Add(ReportData);
// //ReportDataList2.Add(ReportData);
// }
// tempData.ForEach(x => x.SUMTOTAL_DENOMINATION = total_DENOMINATION);
// ReportDataList2.AddRange(tempData);
// //ReportDataList2.Add(new DepositReportDEPOSIT_DEP_ORDER_M_Data() {
// // TYPE = "1",
// // ITEMID = item.ITEMID,
// // COMMIT_DATE = item.COMMIT_DATE,
// // EXPIRY_DATE = item.EXPIRY_DATE,
// // TRAD_PARTNERS = item.TRAD_PARTNERS,
// // CURRENCY = item.CURRENCY,
// // CURRENCY_Flag = item.CURRENCY_Flag,
// // DEP_TYPE = item.DEP_TYPE,
// // INTEREST_RATE = item.INTEREST_RATE,
// // DEP_CNT = 0,
// // DENOMINATION = 0,
// // SUBTOTAL_DENOMINATION = 0,
// // SUMTOTAL_DENOMINATION = total_DENOMINATION,
// // DEP_SET_QUALITY = item.DEP_SET_QUALITY
// //});
// string key = $@"{item.CURRENCY},{item.DEP_SET_QUALITY}"; //幣別,設質否
// decimal value = 0M;
// if (_data.TryGetValue(key, out value))
// {
// value += total_DENOMINATION;
// _data[key] = value;
// }
// else
// {
// _data.Add(key, total_DENOMINATION);
// }
//}
ReportDataList2.Add(new DepositReportDEPOSIT_DEP_ORDER_M_Data()
{
TYPE = "3",
SUMTOTAL_DENOMINATION = _data.Sum(x => x.Value),
CURRENCY_Flag = group.Key.CURRENCY_Flag,
DEP_TYPE = group.Key.DEP_TYPE,
SUMTOTAL_DEP_CNT = _data_CNT.Sum(x => x.Value) //201905220094-00 190524 Edited by Bianco 新增張數小計
});
ReportDataList2.Add(new DepositReportDEPOSIT_DEP_ORDER_M_Data()
{
TYPE = "3",
CURRENCY_Flag = group.Key.CURRENCY_Flag,
DEP_TYPE = group.Key.DEP_TYPE,
});
ReportDataList2.Add(new DepositReportDEPOSIT_DEP_ORDER_M_Data()
{
TYPE = "3",
//COMMIT_DATE = "幣別",
//EXPIRY_DATE = "設質否",
//TRAD_PARTNERS = "面額合計",
PK_ID = "幣別",
COMMIT_DATE = "設質否",
EXPIRY_DATE = "面額合計",
CURRENCY_Flag = group.Key.CURRENCY_Flag,
DEP_TYPE = group.Key.DEP_TYPE,
});
foreach (var d in _data.OrderBy(x => x.Key))
{
ReportDataList2.Add(new DepositReportDEPOSIT_DEP_ORDER_M_Data()
{
TYPE = "3",
//COMMIT_DATE = d.Key.Split(',')[0],
//EXPIRY_DATE = d.Key.Split(',')[1],
//TRAD_PARTNERS = d.Value.ToString().formateThousand(),
PK_ID = d.Key.Split(',')[0],
COMMIT_DATE = d.Key.Split(',')[1],
EXPIRY_DATE = d.Value.ToString().formateThousand(),
CURRENCY_Flag = group.Key.CURRENCY_Flag,
DEP_TYPE = group.Key.DEP_TYPE,
});
}
}
}
resultsTable.Tables.Add(ReportDataList2.ToDataTable());
return(resultsTable);
}
/// <summary>
/// 查詢
/// </summary>
/// <param name="data">金庫物品存取主畫面查詢ViewModel</param>
/// <returns></returns>
public List <TreasuryAccessSearchDetailViewModel> GetSearchDetail(TreasuryAccessSearchViewModel data)
{
List <TreasuryAccessSearchDetailViewModel> result = new List <TreasuryAccessSearchDetailViewModel>();
if (data.vItem == null || data.vAplyUnit == null || !data.vItem.Any() || !data.vAplyUnit.Any()) //無查詢項目 or 申請單位 表示沒有權限查詢
{
return(result);
}
var depts = GetDepts();
var emps = GetEmps();
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
DateTime?_vAPLY_DT_S = TypeTransfer.stringToDateTimeN(data.vAPLY_DT_S);
DateTime?_vAPLY_DT_E = TypeTransfer.stringToDateTimeN(data.vAPLY_DT_E).DateToLatestTime();
DateTime?_vActualAccessDate_S = TypeTransfer.stringToDateTimeN(data.vActualAccessDate_S);
DateTime?_vActualAccessDate_E = TypeTransfer.stringToDateTimeN(data.vActualAccessDate_E).DateToLatestTime();
var formStatus = db.SYS_CODE.AsNoTracking().Where(x => x.CODE_TYPE == "FORM_STATUS").ToList();
var treaItems = db.TREA_ITEM.AsNoTracking().Where(x => x.ITEM_OP_TYPE == "3").ToList();
var _data = db.TREA_APLY_REC.AsNoTracking()
.Where(x => data.vItem.Contains(x.ITEM_ID)) //項目
.Where(x => data.vAplyUnit.Contains(x.APLY_UNIT), data.vCustodianFlag && data.vAplyUnit.Count == 1) //保管科還是要單獨查詢申請單位
.Where(x => data.vAplyUnit.Contains(x.APLY_UNIT), !data.vCustodianFlag) //非保管科要加入申請單位條件 (20181105調整)
.Where(x => x.APLY_DT >= _vAPLY_DT_S, _vAPLY_DT_S != null) //申請日期(起)
.Where(x => x.APLY_DT <= _vAPLY_DT_E, _vAPLY_DT_E != null) //申請日期(迄)
.Where(x => x.APLY_NO == data.vAPLY_NO, !data.vAPLY_NO.IsNullOrWhiteSpace()) //申請單號
.Where(x => x.TREA_REGISTER_ID == data.vTREA_REGISTER_ID, !data.vTREA_REGISTER_ID.IsNullOrWhiteSpace()); //金庫登記簿單號
if (_vActualAccessDate_S != null || _vActualAccessDate_E != null) //實際存取
{
var status = new List <string>()
{
Ref.AccessProjectFormStatus.D03.ToString(),
Ref.AccessProjectFormStatus.E01.ToString()
}; //狀態須符合這兩個
_data = _data.Where(x => status.Contains(x.APLY_STATUS))
.Join(db.TREA_OPEN_REC.AsNoTracking()
.Where(x => x.REGI_APPR_DT >= _vActualAccessDate_S, _vActualAccessDate_S != null)
.Where(x => x.REGI_APPR_DT <= _vActualAccessDate_E, _vActualAccessDate_E != null),
x => x.TREA_REGISTER_ID,
y => y.TREA_REGISTER_ID,
(x, y) => x);
}
var dataTAR = _data.ToList();
var TRIDs = dataTAR.Where(x => x.TREA_REGISTER_ID != null).Select(x => x.TREA_REGISTER_ID).ToList();
var dataTOR = db.TREA_OPEN_REC.AsNoTracking().Where(x => TRIDs.Contains(x.TREA_REGISTER_ID)).ToList();
result.AddRange(
from TAR in dataTAR
join TOR in dataTOR
on TAR.TREA_REGISTER_ID equals TOR.TREA_REGISTER_ID into temp
from TOR in temp.DefaultIfEmpty()
select new TreasuryAccessSearchDetailViewModel
{
vACCESS_REASON = TAR.ACCESS_REASON,
vAPLY_DT = TypeTransfer.dateTimeNToString(TAR.APLY_DT),
vREGI_APPR_DT = TypeTransfer.dateTimeNToString(TOR?.REGI_APPR_DT),
vAPLY_NO = TAR.APLY_NO,
vAPLY_STATUS = TAR.APLY_STATUS,
vAPLY_STATUS_D = formStatus.FirstOrDefault(x => x.CODE == TAR.APLY_STATUS)?.CODE_VALUE,
vAPLY_UNIT = depts.FirstOrDefault(y => y.DPT_CD.Trim() == TAR.APLY_UNIT)?.DPT_NAME,
vAPLY_UID = TAR.APLY_UID,
vAPLY_UID_NAME = emps.FirstOrDefault(x => x.USR_ID == TAR.APLY_UID)?.EMP_NAME,
vCancleFlag = TAR.APLY_STATUS == Ref.AccessProjectFormStatus.A01.ToString() && TAR.CREATE_UID == data.vCreateUid ? "Y" : "N",
vInvalidFlag = invalidStatus.Contains(TAR.APLY_STATUS) &&
TAR.CREATE_UID == data.vCreateUid ? "Y" : "N",
vPrintFlag = printsStatus.Contains(TAR.APLY_STATUS) ? "Y" : "N",
vItem = TAR.ITEM_ID,
vItemDec = treaItems.FirstOrDefault(x => x.ITEM_ID == TAR.ITEM_ID)?.ITEM_DESC,
vDESC = !TAR.APLY_APPR_DESC.IsNullOrWhiteSpace() ? TAR.APLY_APPR_DESC : TAR.CUSTODY_APPR_DESC,
vACCESS_TYPE = TAR.ACCESS_TYPE,
vLast_Update_Time = TAR.LAST_UPDATE_DT
});
}
return(result);
}
/// <summary>
/// save A81.A82.A83
/// </summary>
/// <param name="type">(A81 or A82 or A83)</param>
/// <param name="dataModel">Exhibit10Model</param>
/// <returns></returns>
public MSGReturnModel SaveA8(string type, List <Exhibit10Model> dataModel)
{
MSGReturnModel result = new MSGReturnModel();
try
{
bool _flag = true;
string _message = string.Empty;
List <string> A8Type = new List <string>()
{
Table_Type.A81.ToString(),
Table_Type.A82.ToString(),
Table_Type.A83.ToString()
};
if (!A8Type.Contains(type))
{
result.RETURN_FLAG = false;
result.DESCRIPTION = Message_Type.parameter_Error.GetDescription();
return(result);
}
//using (IFRS9DBEntities db = new IFRS9DBEntities())
//{
#region save Moody_Monthly_PD_Info(A81)
if (Table_Type.A81.ToString().Equals(type))
{
int id = 1;
var A81s = new List <Moody_Monthly_PD_Info>();
foreach (var item in dataModel)
{
DateTime?dt = TypeTransfer.stringToDateTimeN(item.Trailing);
A81s.Add(
new Moody_Monthly_PD_Info()
{
Id = id,
Trailing_12m_Ending = dt,
Actual_Allcorp =
TypeTransfer.stringToDoubleN(item.Actual_Allcorp),
Baseline_forecast_Allcorp =
TypeTransfer.stringToDoubleN(item.Baseline_forecast_Allcorp),
Pessimistic_Forecast_Allcorp =
TypeTransfer.stringToDoubleN(item.Pessimistic_Forecast_Allcorp),
Actual_SG = TypeTransfer.stringToDoubleN(item.Actual_SG),
Baseline_forecast_SG = TypeTransfer.stringToDoubleN(item.Baseline_forecast_SG),
Pessimistic_Forecast_SG =
TypeTransfer.stringToDoubleN(item.Pessimistic_Forecast_SG),
Data_Year = (dt == null) ? string.Empty : ((DateTime)dt).Year.ToString()
});
id += 1;
}
if (A81s.Any())
{
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 10, 0)))
{
IFRS9DBEntities db = null;
try
{
db = new IFRS9DBEntities();
db.Configuration.AutoDetectChangesEnabled = false;
if (db.Moody_Monthly_PD_Info.Any())
{
db.Moody_Monthly_PD_Info.RemoveRange(
db.Moody_Monthly_PD_Info); //資料全刪除
}
db.SaveChanges();
db.Dispose();
db = new IFRS9DBEntities();
db.Configuration.AutoDetectChangesEnabled = false;
int count = 0;
foreach (var A81 in A81s)
{
++count;
db = Common.AddToContext(db, A81, count, 100, true);
}
db.SaveChanges();
}
catch (Exception ex)
{
_flag = false;
_message = ex.exceptionMessage();
}
finally
{
if (_flag)
{
scope.Complete();
}
db.Dispose();
}
}
}
}
#endregion save Moody_Monthly_PD_Info(A81)
#region save Moody_Quartly_PD_Info(A82)
if (Table_Type.A82.ToString().Equals(type))
{
int id = 1;
List <Moody_Quartly_PD_Info> A82s = new List <Moody_Quartly_PD_Info>();
List <Moody_Quartly_PD_Info> allData = new List <Moody_Quartly_PD_Info>();
List <int> months = new List <int>()
{
3, 6, 9, 12
}; //只搜尋3.6.9.12 月份
foreach (var item in dataModel
.Where(x => !string.IsNullOrWhiteSpace(x.Actual_Allcorp) && //要有Actual_Allcorp (排除今年)
months.Contains(DateTime.Parse(x.Trailing).Month)) //只搜尋3.6.9.12 月份
.OrderByDescending(x => x.Trailing)) //排序=>日期大到小
{
DateTime dt = DateTime.Parse(item.Trailing);
string quartly = dt.Year.ToString();
switch (dt.Month) //判斷季別
{
case 3:
quartly += "Q1";
break;
case 6:
quartly += "Q2";
break;
case 9:
quartly += "Q3";
break;
case 12:
quartly += "Q4";
break;
}
double?actualAllcorp = null;
if (!string.IsNullOrWhiteSpace(item.Actual_Allcorp))
{
actualAllcorp = double.Parse(item.Actual_Allcorp);
}
A82s.Add(new Moody_Quartly_PD_Info()
{
Id = id,
Data_Year = dt.Year.ToString(),
Year_Quartly = quartly,
PD = actualAllcorp
});
id += 1;
}
if (A82s.Any())
{
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 10, 0)))
{
IFRS9DBEntities db = null;
try
{
db = new IFRS9DBEntities();
db.Configuration.AutoDetectChangesEnabled = false;
if (db.Moody_Quartly_PD_Info.Any())
{
db.Moody_Quartly_PD_Info.RemoveRange(
db.Moody_Quartly_PD_Info);
}
db.SaveChanges();
db.Dispose();
db = new IFRS9DBEntities();
db.Configuration.AutoDetectChangesEnabled = false;
int count = 0;
foreach (var A82 in A82s)
{
++count;
db = Common.AddToContext(db, A82, count, 100, true);
}
db.SaveChanges();
using (IFRS9DBEntities db2 = new IFRS9DBEntities())
{
string sql = string.Empty;
var C04s = db2.Econ_F_YYYYMMDD.ToList();
int _count = 0;
List <SqlParameter> sps = new List <SqlParameter>();
foreach (var A82 in A82s)
{
++_count;
var C04 = C04s.FirstOrDefault(x => x.Year_Quartly == A82.Year_Quartly);
if (C04 != null)
{
sql += $@"
update Econ_F_YYYYMMDD
set PD_Quartly = @PD_Quartly{_count},
LastUpdate_User = '******',
LastUpdate_Date = @LastUpdate_Date,
LastUpdate_Time = @LastUpdate_Time
where Year_Quartly = @Year_Quartly{_count};
";
sps.Add(new SqlParameter($"PD_Quartly{_count}", A82.PD));
sps.Add(new SqlParameter($"Year_Quartly{_count}", A82.Year_Quartly));
}
}
sps.Add(new SqlParameter("LastUpdate_Date", _UserInfo._date));
sps.Add(new SqlParameter("LastUpdate_Time", _UserInfo._time));
if (sql.Length > 0)
{
db.Database.ExecuteSqlCommand(sql, sps.ToArray());
}
}
}
catch (Exception ex)
{
_flag = false;
_message = ex.exceptionMessage();
}
finally
{
if (_flag)
{
scope.Complete();
}
db.Dispose();
}
}
}
}
#endregion save Moody_Quartly_PD_Info(A82)
#region save Moody_Predit_PD_Info(A83)
if (Table_Type.A83.ToString().Equals(type))
{
using (IFRS9DBEntities db = new IFRS9DBEntities())
{
if (db.Moody_Predit_PD_Info.Any())
{
db.Moody_Predit_PD_Info.RemoveRange(db.Moody_Predit_PD_Info);
}
List <Exhibit10Model> models = (from q in dataModel
where !string.IsNullOrWhiteSpace(q.Actual_Allcorp) && //排除掉今年
12.Equals(DateTime.Parse(q.Trailing).Month) //只取12月
select q).ToList();
string maxYear = models.Max(x => DateTime.Parse(x.Trailing)).Year.ToString(); //抓取最大年
string minYear = models.Min(x => DateTime.Parse(x.Trailing)).Year.ToString(); //抓取最小年
double?PD = null;
double PDValue = models.Sum(x => double.Parse(x.Actual_Allcorp)) / models.Count; //計算 PD
if (PDValue > 0)
{
PD = PDValue;
}
db.Moody_Predit_PD_Info.Add(new Moody_Predit_PD_Info()
{
Id = 1,
Data_Year = maxYear,
Period = minYear + "-" + maxYear,
PD_TYPE = PD_Type.Past_Year_AVG.ToString(),
PD = PD
});
var dtn = DateTime.Now.Year;
Exhibit10Model model =
dataModel.Where(x => dtn.Equals(DateTime.Parse(x.Trailing).Year) &&
12.Equals(DateTime.Parse(x.Trailing).Month)).FirstOrDefault(); //抓今年又是12月的資料
string baselineForecastAllcorp = string.Empty;
if (model != null)
{
baselineForecastAllcorp = model.Baseline_forecast_Allcorp;
}
PD = null;
if (!string.IsNullOrWhiteSpace(baselineForecastAllcorp))
{
PD = double.Parse(baselineForecastAllcorp);
}
db.Moody_Predit_PD_Info.Add(new Moody_Predit_PD_Info()
{
Id = 2,
Data_Year = maxYear,
Period = dtn.ToString(),
PD_TYPE = PD_Type.Forcast.ToString(),
PD = PD
});
db.SaveChanges();
}
}
#endregion save Moody_Predit_PD_Info(A83)
if (_flag)
{
result.RETURN_FLAG = true;
result.DESCRIPTION = Message_Type.save_Success.GetDescription(type);
}
else
{
result.RETURN_FLAG = false;
result.DESCRIPTION = _message;
}
}
catch (DbUpdateException ex)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = Message_Type
.save_Fail.GetDescription(type,
$"message: {ex.Message}" +
$", inner message {ex.InnerException?.InnerException?.Message}");
}
return(result);
}
/// <summary>
/// 查詢CDC資料
/// </summary>
/// <param name="searchModel">CDC 查詢畫面條件</param>
/// <param name="aply_No">資料庫異動申請單紀錄檔 INVENTORY_CHG_APLY 單號</param>
/// <returns></returns>
public IEnumerable <ICDCItem> GetCDCSearchData(CDCSearchViewModel searchModel, string aply_No = null, string charge_Dept = null, string charge_Sect = null)
{
List <CDCCAViewModel> result = new List <CDCCAViewModel>();
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
var emps = GetEmps();
var depts = GetDepts();
if (aply_No.IsNullOrWhiteSpace())
{
var PUT_DATE_From = TypeTransfer.stringToDateTimeN(searchModel.vAPLY_DT_From);
var PUT_DATE_To = TypeTransfer.stringToDateTimeN(searchModel.vAPLY_DT_To).DateToLatestTime();
var GET_DATE_From = TypeTransfer.stringToDateTimeN(searchModel.vAPLY_ODT_From);
var GET_DATE_To = TypeTransfer.stringToDateTimeN(searchModel.vAPLY_ODT_To).DateToLatestTime();
result.AddRange(db.ITEM_CA.AsNoTracking()
.Where(x => TreasuryIn.Contains(x.INVENTORY_STATUS), searchModel.vTreasuryIO == "Y")
.Where(x => x.INVENTORY_STATUS == TreasuryOut, searchModel.vTreasuryIO == "N")
.Where(x => x.PUT_DATE != null && x.PUT_DATE.Value >= PUT_DATE_From.Value, PUT_DATE_From != null)
.Where(x => x.PUT_DATE != null && x.PUT_DATE.Value <= PUT_DATE_To.Value, PUT_DATE_To != null)
.Where(x => x.GET_DATE != null && x.GET_DATE.Value >= GET_DATE_From.Value, GET_DATE_From != null)
.Where(x => x.GET_DATE != null && x.GET_DATE.Value <= GET_DATE_To.Value, GET_DATE_To != null)
.Where(x => x.CHARGE_DEPT == charge_Dept, !charge_Dept.IsNullOrWhiteSpace())
.Where(x => x.CHARGE_SECT == charge_Sect, !charge_Sect.IsNullOrWhiteSpace())
.AsEnumerable()
.Select((x) => new CDCCAViewModel()
{
vItemId = x.ITEM_ID,
vStatus = x.INVENTORY_STATUS,
vPUT_Date = x.PUT_DATE?.dateTimeToStr(),
vGet_Date = x.GET_DATE?.dateTimeToStr(),
vAPLY_UID = x.APLY_UID,
vAPLY_UID_Name = emps.FirstOrDefault(y => y.USR_ID == x.APLY_UID)?.EMP_NAME?.Trim(),
vCharge_Dept = x.CHARGE_DEPT,
vCharge_Dept_AFT = x.CHARGE_DEPT_AFT,
vCharge_Dept_Name = depts.FirstOrDefault(y => y.DPT_CD.Trim() == x.CHARGE_DEPT)?.DPT_NAME?.Trim(),
vCharge_Dept_Name_AFT = depts.FirstOrDefault(y => y.DPT_CD.Trim() == x.CHARGE_DEPT_AFT)?.DPT_NAME?.Trim(),
vCharge_Sect = x.CHARGE_SECT,
vCharge_Sect_AFT = x.CHARGE_SECT_AFT,
vCharge_Sect_Name = depts.FirstOrDefault(y => y.DPT_CD.Trim() == x.CHARGE_SECT)?.DPT_NAME?.Trim(),
vCharge_Sect_Name_AFT = depts.FirstOrDefault(y => y.DPT_CD.Trim() == x.CHARGE_SECT_AFT)?.DPT_NAME?.Trim(),
vCA_Use = x.CA_USE,
vCA_Use_AFT = x.CA_USE_AFT,
vCA_Desc = x.CA_DESC,
vCA_Desc_AFT = x.CA_DESC_AFT,
vCA_Bank = x.BANK,
vCA_Bank_AFT = x.BANK_AFT,
vCA_Number = x.CA_NUMBER,
vCA_Number_AFT = x.CA_NUMBER_AFT,
vCA_Memo = x.MEMO,
vCA_Memo_AFT = x.MEMO_AFT,
vLast_Update_Time = x.LAST_UPDATE_DT
}).ToList());
if (searchModel.vTreasuryIO == "N") //取出
{
if (result.Any())
{
var itemIds = result.Select(x => x.vItemId).ToList();
var uids = GetAplyUidName(itemIds);
result.ForEach(x =>
{
x.vGet_Uid_Name = uids.FirstOrDefault(y => y.itemId == x.vItemId)?.getAplyUidName;
});
}
}
}
else
{
var itemIds = db.OTHER_ITEM_APLY.AsNoTracking()
.Where(x => x.APLY_NO == aply_No).Select(x => x.ITEM_ID).ToList();
result.AddRange(db.ITEM_CA.AsNoTracking()
.Where(x => itemIds.Contains(x.ITEM_ID))
.AsEnumerable()
.Select((x) => new CDCCAViewModel()
{
vItemId = x.ITEM_ID,
vStatus = x.INVENTORY_STATUS,
vPUT_Date = x.PUT_DATE?.dateTimeToStr(),
vGet_Date = x.GET_DATE?.dateTimeToStr(),
vAPLY_UID = x.APLY_UID,
vAPLY_UID_Name = emps.FirstOrDefault(y => y.USR_ID == x.APLY_UID)?.EMP_NAME?.Trim(),
vCharge_Dept = x.CHARGE_DEPT,
vCharge_Dept_AFT = x.CHARGE_DEPT_AFT,
vCharge_Dept_Name = depts.FirstOrDefault(y => y.DPT_CD.Trim() == x.CHARGE_DEPT)?.DPT_NAME?.Trim(),
vCharge_Dept_Name_AFT = depts.FirstOrDefault(y => y.DPT_CD.Trim() == x.CHARGE_DEPT_AFT)?.DPT_NAME?.Trim(),
vCharge_Sect = x.CHARGE_SECT,
vCharge_Sect_AFT = x.CHARGE_SECT_AFT,
vCharge_Sect_Name = depts.FirstOrDefault(y => y.DPT_CD.Trim() == x.CHARGE_SECT)?.DPT_NAME?.Trim(),
vCharge_Sect_Name_AFT = depts.FirstOrDefault(y => y.DPT_CD.Trim() == x.CHARGE_SECT_AFT)?.DPT_NAME?.Trim(),
vCA_Use = x.CA_USE,
vCA_Use_AFT = x.CA_USE_AFT,
vCA_Desc = x.CA_DESC,
vCA_Desc_AFT = x.CA_DESC_AFT,
vCA_Bank = x.BANK,
vCA_Bank_AFT = x.BANK_AFT,
vCA_Number = x.CA_NUMBER,
vCA_Number_AFT = x.CA_NUMBER_AFT,
vCA_Memo = x.MEMO,
vCA_Memo_AFT = x.MEMO_AFT,
vLast_Update_Time = x.LAST_UPDATE_DT
}).ToList());
}
result.ForEach(x =>
{
x.vCharge_Name = !x.vCharge_Sect_Name.IsNullOrWhiteSpace() ? x.vCharge_Sect_Name : x.vCharge_Dept_Name;
x.vCharge_Name_AFT = !x.vCharge_Sect_Name_AFT.IsNullOrWhiteSpace() ? x.vCharge_Sect_Name_AFT : (!x.vCharge_Dept_Name_AFT.IsNullOrWhiteSpace() ? x.vCharge_Dept_Name_AFT : null);
});
}
return(result);
}
public override DataSet GetData(List <reportParm> parms)
{
//報表資料
List <DepositReportITEMIMPData> ReportDataList = new List <DepositReportITEMIMPData>();
var resultsTable = new DataSet();
var ReportData = new DepositReportITEMIMPData();
string vdept = parms.Where(x => x.key == "vdept").FirstOrDefault()?.value ?? string.Empty;
string vsect = parms.Where(x => x.key == "vsect").FirstOrDefault()?.value ?? string.Empty;
string JobProject = parms.Where(x => x.key == "vJobProject").FirstOrDefault()?.value ?? string.Empty;
string APLY_DT_From = parms.Where(x => x.key == "APLY_DT_From").FirstOrDefault()?.value ?? string.Empty; //庫存日期
string APLY_ODT_From = parms.Where(x => x.key == "APLY_ODT_From").FirstOrDefault()?.value ?? string.Empty;
string APLY_ODT_To = parms.Where(x => x.key == "APLY_ODT_To").FirstOrDefault()?.value ?? string.Empty;
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
var _APLY_DT = TypeTransfer.stringToDateTime(APLY_DT_From).DateToLatestTime();
var _APLY_DT_Date = _APLY_DT.Date;
var dtn = DateTime.Now.Date;
var _APLY_ODT_From = TypeTransfer.stringToDateTimeN(APLY_ODT_From);
var _APLY_ODT_To = TypeTransfer.stringToDateTimeN(APLY_ODT_To).DateToLatestTime();
//int TOTAL= 0;
INVENTORY_STATUSs.AddRange(new List <string>()
{
((int)AccessInventoryType._5).ToString(), //預約取出，計庫存
((int)AccessInventoryType._6).ToString(), //已被取出，計庫存
((int)AccessInventoryType._9).ToString()
}); //預約存入，計庫存
var _II = db.ITEM_IMPO.AsNoTracking() //判斷是否在庫
.Where(x => INVENTORY_STATUSs.Contains(x.INVENTORY_STATUS), _APLY_DT_Date == dtn)
.Where(x =>
(INVENTORY_STATUSs.Contains(x.INVENTORY_STATUS) && x.PUT_DATE <= _APLY_DT) // 在庫 且 存入日期 <= 庫存日期
||
(x.INVENTORY_STATUS == INVENTORY_STATUSg &&
x.PUT_DATE <= _APLY_DT &&
_APLY_DT < x.GET_DATE), //存入日期 <= 庫存日期 且 庫存日期 < 取出日期
_APLY_DT_Date != dtn)
.Where(x => x.CHARGE_DEPT == vdept, vdept != "All")
.Where(x => x.CHARGE_SECT == vsect, vsect != "All")
.Where(x => x.PUT_DATE >= _APLY_ODT_From, _APLY_ODT_From != null)
.Where(x => x.PUT_DATE <= _APLY_ODT_To, _APLY_ODT_To != null)
.ToList();
var depts = new List <VW_OA_DEPT>();
var types = new List <SYS_CODE>();
using (DB_INTRAEntities dbINTRA = new DB_INTRAEntities())
{
depts = dbINTRA.VW_OA_DEPT.AsNoTracking().Where(x => x.DPT_CD != null).ToList();
}
types = db.SYS_CODE.AsNoTracking().Where(x => x.CODE != null).ToList();
if (_APLY_DT_Date != dtn)
{
List <string> Item_IDs = _II.Select(x => x.ITEM_ID_FROM == null ? x.ITEM_ID : x.ITEM_ID_FROM).Distinct().ToList();
List <ITEM_IMPO> ITEM_IMPOs = db.ITEM_IMPO.AsNoTracking().Where(x => Item_IDs.Contains(x.ITEM_ID)).ToList();
List <ITEM_IMPO> GetITEM_IMPOs = db.ITEM_IMPO.AsNoTracking()
.Where(x => x.ITEM_ID_FROM != null && Item_IDs.Contains(x.ITEM_ID_FROM) && _APLY_DT > x.GET_DATE).ToList();
//取出日 < 庫存日,之前取出
foreach (var item in ITEM_IMPOs)
{
var _CHARGE_DEPT = getEmpName(depts, item.CHARGE_DEPT);
var _CHARGE_SECT = getEmpName(depts, item.CHARGE_SECT)?.Replace(_CHARGE_DEPT, "")?.Trim();
ReportData = new DepositReportITEMIMPData()
{
ITEM_ID = item.ITEM_ID,
PUT_DATE = item.PUT_DATE.dateTimeToStr(),
CHARGE_DEPT = _CHARGE_DEPT,
CHARGE_SECT = _CHARGE_SECT,
ITEM_NAME = item.ITEM_NAME,
QUANTITY = GetQUANTITY(item, GetITEM_IMPOs),
AMOUNT = item.AMOUNT,
EXPECTED_ACCESS_DATE = item.EXPECTED_ACCESS_DATE.dateTimeToStr(),
DESCRIPTION = item.DESCRIPTION,
MEMO = item.MEMO,
CHARGE_DEPT_ID = item.CHARGE_DEPT,
CHARGE_SECT_ID = item.CHARGE_SECT
};
ReportDataList.Add(ReportData);
}
}
else
{
foreach (var item in _II)
{
var _CHARGE_DEPT = getEmpName(depts, item.CHARGE_DEPT);
var _CHARGE_SECT = getEmpName(depts, item.CHARGE_SECT)?.Replace(_CHARGE_DEPT, "")?.Trim();
ReportData = new DepositReportITEMIMPData()
{
ITEM_ID = item.ITEM_ID,
PUT_DATE = item.PUT_DATE.dateTimeToStr(),
CHARGE_DEPT = _CHARGE_DEPT,
CHARGE_SECT = _CHARGE_SECT,
ITEM_NAME = item.ITEM_NAME,
QUANTITY = item.REMAINING,
AMOUNT = item.AMOUNT,
EXPECTED_ACCESS_DATE = item.EXPECTED_ACCESS_DATE.dateTimeToStr(),
DESCRIPTION = item.DESCRIPTION,
MEMO = item.MEMO,
CHARGE_DEPT_ID = item.CHARGE_DEPT,
CHARGE_SECT_ID = item.CHARGE_SECT
};
ReportDataList.Add(ReportData);
}
}
}
resultsTable.Tables.Add(ReportDataList
.OrderBy(x => x.ITEM_ID)
.ThenBy(x => x.PUT_DATE)
.ThenBy(x => x.CHARGE_DEPT_ID)
.ThenBy(x => x.CHARGE_SECT_ID)
.ToList().ToDataTable());
return(resultsTable);
}
