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