//取得貸款帳戶交易資料
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));
}
