protected void SetDetail(string aply_No, string isNTD, string vDep_Type) { _REC = new REC(); using (TreasuryDBEntities db = new TreasuryDBEntities()) { _REC.APLY_NO = aply_No; _REC.CURRENCY = isNTD == "Y" ? "台幣" : "外幣"; _REC.DEP_TYPE = vDep_Type; //_REC.SYS_TYPE = DateTime.Now.DateToTaiwanDate(9); _REC.SYS_TYPE = TypeTransfer.dateTimeToString(DateTime.Now, false); //取得承作日期 var _TAR = db.TREA_APLY_REC.AsNoTracking() .FirstOrDefault(x => x.APLY_NO == aply_No); if (_TAR != null) { //使用單號去其他存取項目檔抓取物品編號 var OIAs = db.OTHER_ITEM_APLY.AsNoTracking() .Where(x => x.APLY_NO == _TAR.APLY_NO).Select(x => x.ITEM_ID).ToList(); //使用物品編號去定期存單庫存資料檔抓取資料 var _IDOM_DataList = db.ITEM_DEP_ORDER_M.AsNoTracking() .Where(x => OIAs.Contains(x.ITEM_ID)).ToList(); _REC.COMMIT_DATE = TypeTransfer.dateTimeToString(_IDOM_DataList .Where(x => x.CURRENCY == "NTD", isNTD == "Y") .Where(x => x.CURRENCY != "NTD", isNTD == "N") .Where(x => x.DEP_TYPE == vDep_Type, vDep_Type != "0") .Select(x => x.COMMIT_DATE).FirstOrDefault(), false); _REC.APLY_STATUS = getStatue(_TAR.APLY_STATUS); //申請狀態 } } }
public override DataSet GetData(List <reportParm> parms) { var resultsTable = new DataSet(); string aply_No = parms.Where(x => x.key == "aply_No").FirstOrDefault()?.value ?? string.Empty; string isNTD = parms.Where(x => x.key == "isNTD").FirstOrDefault()?.value ?? string.Empty; string vDep_Type = parms.Where(x => x.key == "vDep_Type").FirstOrDefault()?.value ?? string.Empty; SetDetail(aply_No, isNTD, vDep_Type); //報表資料 List <DepositReportData> ReportDataList = new List <DepositReportData>(); using (TreasuryDBEntities db = new TreasuryDBEntities()) { var _TAR = db.TREA_APLY_REC.AsNoTracking() .FirstOrDefault(x => x.APLY_NO == aply_No); var ReportData = new DepositReportData(); int TOTAL_DEP_CNT = 0; //取得定存明細資料 if (_TAR != null) { //使用單號去其他存取項目檔抓取物品編號 var OIAs = db.OTHER_ITEM_APLY.AsNoTracking() .Where(x => x.APLY_NO == _TAR.APLY_NO).Select(x => x.ITEM_ID).ToList(); //使用物品編號去定期存單庫存資料檔抓取資料 var _IDOM_DataList = db.ITEM_DEP_ORDER_M.AsNoTracking() .Where(x => OIAs.Contains(x.ITEM_ID)) .Where(x => x.CURRENCY == "NTD", isNTD == "Y") .Where(x => x.CURRENCY != "NTD", isNTD == "N") .Where(x => x.DEP_TYPE != null) .Where(x => x.DEP_TYPE == vDep_Type, vDep_Type != "0") .OrderBy(x => x.DEP_TYPE) .ThenBy(x => x.ITEM_ID) .ToList(); var DEP_TYPEs = db.SYS_CODE.AsNoTracking() .Where(x => x.CODE_TYPE == "DEP_TYPE").ToList(); if (_IDOM_DataList.Any()) { //設值否=N var _IDOM_DataNList = _IDOM_DataList.Where(x => x.DEP_SET_QUALITY == "N").ToList(); foreach (var MasterDataN in _IDOM_DataNList) { Decimal TOTAL_DENOMINATION = 0; //使用物品編號去定期存單庫存資料明細檔抓取資料 var _IDOD_DataList = db.ITEM_DEP_ORDER_D.AsNoTracking() .Where(x => x.ITEM_ID == MasterDataN.ITEM_ID).ToList(); List <DepositReportData> addDatas = new List <DepositReportData>(); foreach (var DetailData in _IDOD_DataList) { ReportData = new DepositReportData() { TYPE = "Data-N", ITEM_ID = DetailData.ITEM_ID, EXPIRY_DATE = TypeTransfer.dateTimeToString(MasterDataN.EXPIRY_DATE, false), TRAD_PARTNERS = MasterDataN.TRAD_PARTNERS, DEP_TYPE = MasterDataN.DEP_TYPE, DEP_NO_B = DetailData.DEP_NO_B, DEP_NO_E = DetailData.DEP_NO_E, DEP_CNT = DetailData.DEP_CNT, DENOMINATION = DetailData.DENOMINATION, MSG = MasterDataN.GET_MSG }; //ReportDataList.Add(ReportData); addDatas.Add(ReportData); TOTAL_DENOMINATION += DetailData.SUBTOTAL_DENOMINATION; TOTAL_DEP_CNT += DetailData.DEP_CNT; } addDatas.ForEach(x => { x.TOTAL_DENOMINATION = TOTAL_DENOMINATION; }); ReportDataList.AddRange(addDatas); } //設值否=Y var _IDOM_DataYList = _IDOM_DataList.Where(x => x.DEP_SET_QUALITY == "Y").ToList(); if (_IDOM_DataYList.Any()) //設質否 { _REC.DEP_SET_QUALITY = "Y"; } foreach (var MasterDataY in _IDOM_DataYList) { Decimal TOTAL_DENOMINATION = 0; //使用物品編號去定期存單庫存資料明細檔抓取資料 var _IDOD_DataList = db.ITEM_DEP_ORDER_D.AsNoTracking() .Where(x => x.ITEM_ID == MasterDataY.ITEM_ID).ToList(); List <DepositReportData> addDatas = new List <DepositReportData>(); foreach (var DetailData in _IDOD_DataList) { ReportData = new DepositReportData() { TYPE = "Data-Y", CURRENCY = _REC.CURRENCY, EXPIRY_DATE = TypeTransfer.dateTimeToString(MasterDataY.EXPIRY_DATE, false), TRAD_PARTNERS = MasterDataY.TRAD_PARTNERS, DEP_TYPE_D = DEP_TYPEs.FirstOrDefault(x => x.CODE == MasterDataY.DEP_TYPE)?.CODE_VALUE, DEP_TYPE = MasterDataY.DEP_TYPE, DEP_NO_B = DetailData.DEP_NO_B, DEP_NO_E = DetailData.DEP_NO_E, DEP_CNT = DetailData.DEP_CNT, DENOMINATION = DetailData.DENOMINATION, }; addDatas.Add(ReportData); //ReportDataList.Add(ReportData); TOTAL_DENOMINATION += DetailData.SUBTOTAL_DENOMINATION; } addDatas.ForEach(x => { x.TOTAL_DENOMINATION = TOTAL_DENOMINATION; }); ReportDataList.AddRange(addDatas); //ReportData = new DepositReportData() //{ // TYPE = "Data-Y", // CURRENCY = _REC.CURRENCY, // EXPIRY_DATE = TypeTransfer.dateTimeToString(MasterDataY.EXPIRY_DATE,false), // TRAD_PARTNERS = MasterDataY.TRAD_PARTNERS, // DEP_TYPE = MasterDataY.DEP_TYPE, // TOTAL_DENOMINATION = TOTAL_DENOMINATION //}; //ReportDataList.Add(ReportData); } } } //取得定存交割檢核項目 var _Dep_Chk_Item = db.DEP_CHK_ITEM.AsNoTracking() .Where(x => x.ACCESS_TYPE == "G") .Where(x => x.IS_DISABLED == "N") .OrderBy(x => x.ITEM_ORDER).ToList(); int Item_NO = 1;//項次預設1 foreach (var item in _Dep_Chk_Item) { string DEP_CHK_ITEM_DESC = string.Empty; //判斷是否有取代變數 if (string.IsNullOrEmpty(item.REPLACE)) { DEP_CHK_ITEM_DESC = item.DEP_CHK_ITEM_DESC; } else { switch (item.REPLACE) { case "G1": DEP_CHK_ITEM_DESC = item.DEP_CHK_ITEM_DESC?.Replace("@_G1_", TOTAL_DEP_CNT.ToString().formateThousand()); break; default: DEP_CHK_ITEM_DESC = item.DEP_CHK_ITEM_DESC; break; } } ReportData = new DepositReportData() { TYPE = "Item", ISORTBY = Item_NO.ToString(), DEP_CHK_ITEM_DESC = DEP_CHK_ITEM_DESC }; ReportDataList.Add(ReportData); Item_NO++; } } resultsTable.Tables.Add(ReportDataList.ToDataTable()); SetExtensionParm(); 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); }