//取得貸款帳戶交易資料 public BankLoanInfo getBankLoan(string bl_id, string supplier) { BankLoanInfo item = new BankLoanInfo(); using (var context = new topmepEntities()) { try { logger.Info("get bank transaction BL_ID=" + bl_id); item.LoanInfo = context.FIN_BANK_LOAN.Find(long.Parse(bl_id)); long blid = long.Parse(bl_id); item.LoanTransaction = context.FIN_LOAN_TRANACTION.Where(b => b.BL_ID == blid).ToList(); string sql = ""; //取得期數與匯總金額 if (supplier == "Y") { sql = "SELECT ISNULL(CUR_PERIOD, 0)CUR_PERIOD , ISNULL(AMOUNT, 0)AMOUNT, (SELECT ISNULL(SUM(AMOUNT), 0) FROM FIN_BANK_LOAN f " + "LEFT JOIN FIN_LOAN_TRANACTION t ON f.BL_ID = t.BL_ID WHERE t.TRANSACTION_TYPE = 1 AND f.BL_ID =@BL_ID) AS paybackAmt, " + "(SELECT ISNULL(SUM(AMOUNT), 0) FROM FIN_BANK_LOAN f LEFT JOIN FIN_LOAN_TRANACTION t ON f.BL_ID = t.BL_ID " + "WHERE t.TRANSACTION_TYPE = -1 AND f.BL_ID =@BL_ID) AS eventAmt FROM FIN_BANK_LOAN f " + "LEFT JOIN (SELECT BL_ID, MAX(ISNULL(PERIOD, 0)) CUR_PERIOD, SUM(TRANSACTION_TYPE * AMOUNT) AMOUNT " + "FROM FIN_LOAN_TRANACTION GROUP BY BL_ID)flt ON flt.BL_ID = f.BL_ID WHERE f.BL_ID =@BL_ID "; } else { sql = "SELECT ISNULL(CUR_PERIOD, 0)CUR_PERIOD , ISNULL(AMOUNT, 0)AMOUNT, ROUND(ISNULL(fbl.QUOTA * (1-IIF(fbl.vaRatio >= fbl.CUM_AR_RATIO , 1, fbl.QUOTA_AVAILABLE_RATIO/100)), 0), 0) AS SURPLUS_AMOUNT, " + "(SELECT ISNULL(SUM(AMOUNT), 0) FROM FIN_BANK_LOAN f LEFT JOIN FIN_LOAN_TRANACTION t ON f.BL_ID = t.BL_ID WHERE t.TRANSACTION_TYPE = 1 AND f.BL_ID =@BL_ID) AS paybackAmt, " + "(SELECT ISNULL(SUM(AMOUNT), 0) FROM FIN_BANK_LOAN f LEFT JOIN FIN_LOAN_TRANACTION t ON f.BL_ID = t.BL_ID WHERE t.TRANSACTION_TYPE = -1 AND f.BL_ID =@BL_ID) AS eventAmt " + "FROM (SELECT BL_ID, QUOTA, CUM_AR_RATIO, QUOTA_AVAILABLE_RATIO, ISNULL(VALUATION_AMOUNT,0) / ISNULL(contractAtm, 1) * 100 AS vaRatio FROM FIN_BANK_LOAN f LEFT JOIN " + "(SELECT PROJECT_ID, SUM(ITEM_UNIT_PRICE * ITEM_QUANTITY) AS contractAtm FROM PLAN_ITEM GROUP BY PROJECT_ID)pi ON f.PROJECT_ID = pi.PROJECT_ID " + "LEFT JOIN (SELECT PROJECT_ID, SUM(VALUATION_AMOUNT)AS VALUATION_AMOUNT FROM PLAN_VALUATION_FORM GROUP BY PROJECT_ID)v ON f.PROJECT_ID = v.PROJECT_ID " + "WHERE BL_ID=@BL_ID)fbl LEFT JOIN (SELECT lt.BL_ID, MAX(ISNULL(PERIOD,0)) CUR_PERIOD, IIF(QUOTA_RECYCLABLE = 'Y',SUM(TRANSACTION_TYPE*AMOUNT), SUM(IIF(TRANSACTION_TYPE = 1,0,-1)*AMOUNT)) AMOUNT " + "from FIN_LOAN_TRANACTION lt LEFT JOIN FIN_BANK_LOAN bl ON lt.BL_ID = bl.BL_ID GROUP BY lt.BL_ID, bl.QUOTA_RECYCLABLE)flt ON flt.BL_ID = fbl.BL_ID "; } logger.Debug("sql=" + sql); Dictionary <string, object> para = new Dictionary <string, object>(); para.Add("BL_ID", blid); DataSet ds = ExecuteStoreQuery(sql, System.Data.CommandType.Text, para); if (ds.Tables[0].Rows.Count > 0) { item.CurPeriod = long.Parse(ds.Tables[0].Rows[0]["CUR_PERIOD"].ToString()); item.SumTransactionAmount = (decimal)ds.Tables[0].Rows[0]["AMOUNT"]; item.paybackAmt = (decimal)ds.Tables[0].Rows[0]["paybackAmt"]; item.eventAmt = (decimal)ds.Tables[0].Rows[0]["eventAmt"]; if (supplier != "Y") { item.SurplusQuota = (decimal)ds.Tables[0].Rows[0]["SURPLUS_AMOUNT"]; } } } catch (Exception ex) { logger.Error(ex.Message + ":StackTrace=" + ex.StackTrace); } } return(item); }
/// <summary> /// 取得貸款銀行基本資料 /// </summary> /// <returns></returns> public string getBasicInfo() { ContextService4BankInfo service = new ContextService4BankInfo(); string blid = Request["id"]; BankLoanInfo bank = service.getBankLoan(blid, "N"); //處理Json 日期格式 var settings = new JsonSerializerSettings { DateFormatString = "yyyy/MM/dd" }; string bankJson = JsonConvert.SerializeObject(bank.LoanInfo, settings); log.Debug("get bakn info id =" + bankJson); return(bankJson); }
/// <summary> /// 貸還款交易維護 /// </summary> /// <returns></returns> public ActionResult BankLoanTransaction() { string blid = Request["BL_ID"]; string supplier = "N"; if (null != Request["IS_SUPPLIER"] && Request["IS_SUPPLIER"] != "") { supplier = Request["IS_SUPPLIER"]; } log.Info("IS_SUPPLIER =" + supplier); ContextService4BankInfo service = new ContextService4BankInfo(); BankLoanInfo loanInfo = service.getBankLoan(blid, supplier); log.Debug("Bank Loan Transaction:" + blid); return(View(loanInfo)); }