public override DataSet GetData(List <reportParm> parms) { var resultsTable = new DataSet(); using (var conn = new SqlConnection(defaultConnection)) { string sql = string.Empty; sql += $@" WITH TEMPT AS --期末 ( Select * from IFRS9_Bond_Report WHERE Report_Date = @Final AND IAS39_CATEGORY = @IAS39_CATEGORY ), TEMPT1 AS --期初 ( Select * from IFRS9_Bond_Report WHERE Report_Date = @Initial AND IAS39_CATEGORY = @IAS39_CATEGORY ), TEMPI AS --表3 ( Select T.IAS39_CATEGORY AS IAS39_CATEGORY_T,--期末公報分類(期末E) T1.IAS39_CATEGORY AS IAS39_CATEGORY_T1, --期初公報分類(期初E) T.Impairment_Stage AS Impairment_Stage_T, --期末減損階段(U) T1.Impairment_Stage AS Impairment_Stage_T1, --期初減損階段(T) T1.Principal_EL_Ex AS Principal_EL_Ex_T1_3, --期初累計減損_本金(期初報表日匯率台幣)(表3-AG) --期初剩餘部位之期末ECL(報表日匯率台幣)(表3-AR) = (表3-AG)-(表3-AN)-(表3-AP) CASE WHEN ISNULL(T.Ori_Amount,0) = 0 THEN T1.Principal_EL_Ex ELSE 0 END AS Excluding_Monetary_Assets_ECL_Ex_3, --除列之金融資產ECL(期初報表日匯率台幣)(表3-AN) CASE WHEN (T.Ori_Amount > 0 AND (T.Ori_Amount - T1.Ori_Amount) < 0) THEN T1.Principal_EL_Ex * ABS( (T.Ori_Amount - T1.Ori_Amount) / T1.Ori_Amount ) ELSE 0 END AS Section_Excluding_Monetary_Assets_ECL_Ex_3 --部分除列之金融資產ECL(報表日匯率台幣)(表3-AP) from TEMPT1 AS T1 left join TEMPT AS T ON T1.Bond_Number = CASE WHEN T.ISIN_Changed_Ind = 'Y' THEN T.Bond_Number_Old ELSE T.Bond_Number END AND T1.Lots = CASE WHEN T.ISIN_Changed_Ind = 'Y' THEN T.Lots_Old ELSE T.Lots END AND T1.Portfolio_Name = CASE WHEN T.ISIN_Changed_Ind = 'Y' THEN T.Portfolio_Name_Old ELSE T.Portfolio_Name END ), TEMPF AS --表4 ( Select T.IAS39_CATEGORY AS IAS39_CATEGORY_T,--期末公報分類(期末E) T1.IAS39_CATEGORY AS IAS39_CATEGORY_T1, --期初公報分類(期初E) T.Impairment_Stage AS Impairment_Stage_T, --期末減損階段(U) T1.Impairment_Stage AS Impairment_Stage_T1, --期初減損階段(T) T.Principal_EL_Ex AS Principal_EL_Ex_T_4, --本期累計減損_本金(報表日匯率台幣)(表4-AK) CASE WHEN ISNULL(T1.Ori_Amount,0) = 0 THEN T.Principal_EL_Ex ELSE 0 END AS Purchase_Monetary_Assets_ECL_Ex_4, --新購入之金融資產ECL(報表日匯率台幣)(表4-AN) CASE WHEN (T1.Ori_Amount > 0 AND (T.Ori_Amount - T1.Ori_Amount) > 0) THEN T.Principal_EL_Ex * (T.Ori_Amount - T1.Ori_Amount) / T1.Ori_Amount ELSE 0 END AS Section_Purchase_Monetary_Assets_ECL_Ex_4, --部分新增之金融資產ECL(報表日匯率台幣)(表4-AP) --期初剩餘部位之期末ECL(報表日匯率台幣)(表4-AR) = (表4-AK)-(表4-AN)-(表4-AP) T1.Ex_rate AS Ex_rate_T1_4, --期初報表日匯率(表4-AC) T.Principal_EL_Ori AS Principal_EL_Ori_T_4, --本期累計減損_本金(原幣)(表4-AI) CASE WHEN ISNULL(T1.Ori_Amount,0) = 0 THEN T.Principal_EL_Ori ELSE 0 END AS Purchase_Monetary_Assets_ECL_Ori_4, --新購入之金融資產ECL(原幣)(表4-AM) CASE WHEN (T1.Ori_Amount > 0 AND (T.Ori_Amount - T1.Ori_Amount) > 0) THEN T.Principal_EL_Ori * (T.Ori_Amount - T1.Ori_Amount) / T1.Ori_Amount ELSE 0 END AS Section_Purchase_Monetary_Assets_ECL_Ori_4 --部分新增之金融資產ECL(原幣)(表4-AO) --期初剩餘部位之期末ECL(原幣)(表4-AQ) = (表4-AI)-(表4-AM)-(表4-AO) --期初剩餘部位之期末ECL(期初報表日匯率台幣) (表4-AS) = (表4-AQ)*(表4-AC) --匯兌變動影響(表4-AT) = (表4-AR)-(表4-AS) from TEMPT AS T left join TEMPT1 AS T1 ON T1.Bond_Number = CASE WHEN T.ISIN_Changed_Ind = 'Y' THEN T.Bond_Number_Old ELSE T.Bond_Number END AND T1.Lots = CASE WHEN T.ISIN_Changed_Ind = 'Y' THEN T.Lots_Old ELSE T.Lots END AND T1.Portfolio_Name = CASE WHEN T.ISIN_Changed_Ind = 'Y' THEN T.Portfolio_Name_Old ELSE T.Portfolio_Name END ) , TEMP1 AS ( Select IAS39_CATEGORY_T,--期末公報分類(期末E) IAS39_CATEGORY_T1, --期初公報分類(期初E) Impairment_Stage_T, --期末減損階段(U) Impairment_Stage_T1, --期初減損階段(T) Principal_EL_Ex_T1_3, --期初累計減損_本金(期初報表日匯率台幣)(表3-AG) Principal_EL_Ex_T1_3 - Excluding_Monetary_Assets_ECL_Ex_3 - Section_Excluding_Monetary_Assets_ECL_Ex_3 AS Final_Remaining_Part_ECL_Ex_3, --期初剩餘部位之期末ECL(報表日匯率台幣)(表3-AR) = (表3-AG)-(表3-AN)-(表3-AP) Excluding_Monetary_Assets_ECL_Ex_3, --除列之金融資產ECL(期初報表日匯率台幣)(表3-AN) Section_Excluding_Monetary_Assets_ECL_Ex_3 --部分除列之金融資產ECL(報表日匯率台幣)(表3-AP) from TEMPI ) , TEMP2 AS ( Select IAS39_CATEGORY_T,--期末公報分類(期末E) IAS39_CATEGORY_T1, --期初公報分類(期初E) Impairment_Stage_T, --期末減損階段(U) Impairment_Stage_T1, --期初減損階段(T) Purchase_Monetary_Assets_ECL_Ex_4, --新購入之金融資產ECL(報表日匯率台幣)(表4-AN) Section_Purchase_Monetary_Assets_ECL_Ex_4, --部分新增之金融資產ECL(報表日匯率台幣)(表4-AP) (Principal_EL_Ori_T_4 - Purchase_Monetary_Assets_ECL_Ori_4 - Section_Purchase_Monetary_Assets_ECL_Ori_4) * --期初剩餘部位之期末ECL(原幣)(表4-AQ) = (表4-AI)-(表4-AM)-(表4-AO) Ex_rate_T1_4 AS Initial_Remaining_Part_ECL_Ex_Rate_4, --期初剩餘部位之期末ECL(期初報表日匯率台幣) (表4-AS) = (表4-AQ)*(表4-AC) (Principal_EL_Ex_T_4 - Purchase_Monetary_Assets_ECL_Ex_4 - Section_Purchase_Monetary_Assets_ECL_Ex_4) - --期初剩餘部位之期末ECL(報表日匯率台幣)(表4-AR) = (表4-AK)-(表4-AN)-(表4-AP) ((Principal_EL_Ori_T_4 - Purchase_Monetary_Assets_ECL_Ori_4 - Section_Purchase_Monetary_Assets_ECL_Ori_4) * Ex_rate_T1_4) AS Exchange_Changes_4 --匯兌變動影響(表4-AT) = (表4-AR)-(表4-AS) from TEMPF ) Select --期初餘額(5) (Select SUM(TEMP1.Principal_EL_Ex_T1_3) from TEMP1 where TEMP1.Impairment_Stage_T1 = '1') AS E5, --'(T-1期)Impairment_Stage = 1 期初累計減損_本金(期初報表日匯率台幣)(T - 1期)Principal_EL_Ex 報表3中,E欄 = AC且T欄 = 1之AG欄加總' (Select SUM(TEMP1.Principal_EL_Ex_T1_3) from TEMP1 where TEMP1.Impairment_Stage_T1 = '2') AS G5, --'(T-1期)Impairment_Stage=2 期初累計減損_本金(期初報表日匯率台幣) (T-1期)Principal_EL_Ex 報表3中,E欄=AC且T欄=2之AG欄加總' (Select SUM(TEMP1.Principal_EL_Ex_T1_3) from TEMP1 where TEMP1.Impairment_Stage_T1 = '3') AS H5, --'(T-1期)Impairment_Stage=3 期初累計減損_本金(期初報表日匯率台幣) (T-1期)Principal_EL_Ex 報表3中,E欄=AC且T欄=3之AG欄加總' --因期初已認列之金融工具所產生之變動:(6) -- 轉為存續期間預期信用損失(7) (Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) * -1 from TEMP1 where TEMP1.Impairment_Stage_T1 = '1' and TEMP1.Impairment_Stage_T = '2') AS E7, --'(減項) 加總報表3中符合T-1期Impairment_Stage=1且T期Impairment_Stage=2條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中,E欄=AC、T欄=1、U欄=2之AR欄加總' (Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) from TEMP1 where TEMP1.Impairment_Stage_T1 IN('1', '3') and TEMP1.Impairment_Stage_T = '2') AS G7, --'(加項) 加總報表3中符合T-1期Impairment_Stage=1 or 3且T期Impairment_Stage=2條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中,E欄=AC、T欄=1 or 3、U欄=2之AR欄加總' (Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) * -1 from TEMP1 where TEMP1.Impairment_Stage_T1 = '3' and TEMP1.Impairment_Stage_T = '2') AS H7, --'(減項) 加總報表3中符合T-1期Impairment_Stage=3且T期Impairment_Stage=2條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中,E欄=AC、T欄=3、U欄=2之AR欄加總' -- 轉為信用減損金融資產(8) (Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) * -1 from TEMP1 where TEMP1.Impairment_Stage_T1 = '1' and TEMP1.Impairment_Stage_T = '3') AS E8, --'(減項) 加總報表3中符合T-1期Impairment_Stage=1且T期Impairment_Stage=3條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中,E欄=AC、T欄=1、U欄=3之AR欄加總' (Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) * -1 from TEMP1 where TEMP1.Impairment_Stage_T1 = '2' and TEMP1.Impairment_Stage_T = '3') AS G8, --'(減項) 加總報表3中符合T-1期Impairment_Stage=2且T期Impairment_Stage=3條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中,E欄=AC、T欄=2、U欄=3之AR欄加總' (Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) from TEMP1 where TEMP1.Impairment_Stage_T1 IN('1', '2') and TEMP1.Impairment_Stage_T = '3') AS H8, --'(加項) 加總報表3中符合T-1期Impairment_Stage=1 or 2且T期Impairment_Stage=3條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中,E欄=AC、T欄=1 or 2、U欄=3之AR欄加總' -- 轉為12個月預期信用損失(9) (Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) from TEMP1 where TEMP1.Impairment_Stage_T1 IN('2', '3') and TEMP1.Impairment_Stage_T = '1') AS E9, --'(加項) 加總報表3中符合T-1期Impairment_Stage=2 or 3且T期Impairment_Stage=1條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中,E欄=AC、T欄=2 or 3、U欄=1之AR欄加總' (Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) * -1 from TEMP1 where TEMP1.Impairment_Stage_T1 = '2' and TEMP1.Impairment_Stage_T = '1') AS G9, --'(減項) 加總報表3中符合T-1期Impairment_Stage=2且T期Impairment_Stage=1條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中,E欄=AC、T欄=2、U欄=1之AR欄加總' (Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) * -1 from TEMP1 where TEMP1.Impairment_Stage_T1 = '3' and TEMP1.Impairment_Stage_T = '1') AS H9, --'(減項) 加總報表3中符合T-1期Impairment_Stage=3且T期Impairment_Stage=1條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中,E欄=AC、T欄=3、U欄=1之AR欄加總' -- 於當期除列之金融資產(10) (Select(SUM(TEMP1.Excluding_Monetary_Assets_ECL_Ex_3) + SUM(TEMP1.Section_Excluding_Monetary_Assets_ECL_Ex_3)) * -1 from TEMP1 where TEMP1.Impairment_Stage_T1 = '1') AS E10, --'(減項) 加總報表3中符合T-1期Impairment_Stage=1條件者之除列之金融資產ECL(期初報表日匯率台幣)及部分除列之金融資產ECL(期初報表日匯率台幣) 報表3中,E欄=AC及T欄=1之AN欄及AP欄加總' (Select(SUM(TEMP1.Excluding_Monetary_Assets_ECL_Ex_3) + SUM(TEMP1.Section_Excluding_Monetary_Assets_ECL_Ex_3)) * -1 from TEMP1 where TEMP1.Impairment_Stage_T1 = '2') AS G10, --'(減項) 加總報表3中符合T-1期Impairment_Stage=2條件者之除列之金融資產ECL(期初報表日匯率台幣)及部分除列之金融資產ECL(期初報表日匯率台幣) 報表3中,E欄=AC及T欄=2之AN欄及AP欄加總' (Select(SUM(TEMP1.Excluding_Monetary_Assets_ECL_Ex_3) + SUM(TEMP1.Section_Excluding_Monetary_Assets_ECL_Ex_3)) * -1 from TEMP1 where TEMP1.Impairment_Stage_T1 = '3') AS H10, --'(減項) 加總報表3中符合T-1期Impairment_Stage=3條件者之除列之金融資產ECL(期初報表日匯率台幣)及部分除列之金融資產ECL(期初報表日匯率台幣) 報表3中,E欄=AC及T欄=3之AN欄及AP欄加總' --創始或購入之新金融資產(11) (Select(SUM(TEMP2.Purchase_Monetary_Assets_ECL_Ex_4) + SUM(TEMP2.Section_Purchase_Monetary_Assets_ECL_Ex_4)) from TEMP2 where TEMP2.Impairment_Stage_T = '1') AS E11, --'(加項) 加總報表4中符合T期Impairment_Stage=1條件者之新購入之金融資產ECL(報表日匯率台幣)及部分新增之金融資產ECL(報表日匯率台幣) 報表4中,E欄=AC、U欄=1之AN欄及AP欄加總' (Select(SUM(TEMP2.Purchase_Monetary_Assets_ECL_Ex_4) + SUM(TEMP2.Section_Purchase_Monetary_Assets_ECL_Ex_4)) from TEMP2 where TEMP2.Impairment_Stage_T = '2') AS G11, --'(加項) 加總報表4中符合T期Impairment_Stage=2條件者之新購入之金融資產ECL(報表日匯率台幣)及部分新增之金融資產ECL(報表日匯率台幣) 報表4中,E欄=AC、U欄=2之AN欄及AP欄加總' (Select(SUM(TEMP2.Purchase_Monetary_Assets_ECL_Ex_4) + SUM(TEMP2.Section_Purchase_Monetary_Assets_ECL_Ex_4)) from TEMP2 where TEMP2.Impairment_Stage_T = '3') AS H11, --'(加項) 加總報表4中符合T期Impairment_Stage=3條件者之新購入之金融資產ECL(報表日匯率台幣)及部分新增之金融資產ECL(報表日匯率台幣) 報表4中,E欄=AC、U欄=3之AN欄及AP欄加總' --本期沖銷(12) --本期收回(13) --模型 / 風險參數之改變(14) (Select SUM(TEMP2.Initial_Remaining_Part_ECL_Ex_Rate_4) from TEMP2 where TEMP2.Impairment_Stage_T = '1') - (Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) from TEMP1 where TEMP1.Impairment_Stage_T = '1') AS E14, --'(加項) 加總報表4中符合T期Impairment_Stage=1條件者之期初剩餘部位之期末ECL(期初報表日匯率台幣) 報表4中,E欄=AC、U欄=1之AS欄加總 --(減項) 加總報表3中符合T期Impairment_Stage = 1條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中,E欄 = AC、U欄 = 1之AR欄加總' (Select SUM(TEMP2.Initial_Remaining_Part_ECL_Ex_Rate_4) from TEMP2 where TEMP2.Impairment_Stage_T = '2') - (Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) from TEMP1 where TEMP1.Impairment_Stage_T = '2') AS G14, --'(加項) 加總報表4中符合T期Impairment_Stage=2條件者之期初剩餘部位之期末ECL(期初報表日匯率台幣) 報表4中,E欄=AC、U欄=2之AS欄加總 --(減項) 加總報表3中符合T期Impairment_Stage = 2條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中,E欄 = AC、U欄 = 2之AR欄加總' (Select SUM(TEMP2.Initial_Remaining_Part_ECL_Ex_Rate_4) from TEMP2 where TEMP2.Impairment_Stage_T = '3') - (Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) from TEMP1 where TEMP1.Impairment_Stage_T = '3') AS H14, --'(加項) 加總報表4中符合T期Impairment_Stage=3條件者之期初剩餘部位之期末ECL(期初報表日匯率台幣) 報表4中,E欄=AC、U欄=3之AS欄加總 --(減項) 加總報表3中符合T期Impairment_Stage = 3條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中,E欄 = AC、U欄 = 3之AR欄加總' --匯兌及其他變動(15) (Select SUM(TEMP2.Exchange_Changes_4) from TEMP2 where TEMP2.Impairment_Stage_T = '1') AS E15, --'(加項) 加總報表4中符合T期Impairment_Stage=1條件者之匯兌變動影響 報表4中,E欄=AC、U欄=1之AT欄加總' (Select SUM(TEMP2.Exchange_Changes_4) from TEMP2 where TEMP2.Impairment_Stage_T = '2') AS G15, --'(加項) 加總報表4中符合T期Impairment_Stage=2條件者之匯兌變動影響 報表4中,E欄=AC、U欄=2之AT欄加總' (Select SUM(TEMP2.Exchange_Changes_4) from TEMP2 where TEMP2.Impairment_Stage_T = '3') AS H15 --'(加項) 加總報表4中符合T期Impairment_Stage=3條件者之匯兌變動影響 報表4中,E欄=AC、U欄=3之AT欄加總' --期末餘額(16) "; using (var cmd = new SqlCommand(sql, conn)) { string _IAS39_CATEGORY = "AC"; string Initial = parms.Where(x => x.key == "Initial").FirstOrDefault()?.value ?? string.Empty; string Final = parms.Where(x => x.key == "Final").FirstOrDefault()?.value ?? string.Empty; DateTime Initial_Date = DateTime.Parse(Initial); DateTime Final_Date = DateTime.Parse(Final); var _Initial = new SqlParameter(); _Initial.SqlDbType = SqlDbType.VarChar; _Initial.ParameterName = "@Initial"; _Initial.Value = Initial; _Initial.Direction = ParameterDirection.Input; var _Final = new SqlParameter(); _Final.SqlDbType = SqlDbType.VarChar; _Final.ParameterName = "@Final"; _Final.Value = Final; _Final.Direction = ParameterDirection.Input; var IAS39_CATEGORY = new SqlParameter(); IAS39_CATEGORY.SqlDbType = SqlDbType.VarChar; IAS39_CATEGORY.ParameterName = "@IAS39_CATEGORY"; IAS39_CATEGORY.Value = _IAS39_CATEGORY; IAS39_CATEGORY.Direction = ParameterDirection.Input; cmd.Parameters.Add(_Initial); //期初 cmd.Parameters.Add(_Final); //期末 cmd.Parameters.Add(IAS39_CATEGORY); cmd.CommandTimeout = 0; //190624 USER產報表跳出逾時訊息 //cmd.Parameters.Add(new SqlParameter("Initial", Initial)); //期初 //cmd.Parameters.Add(new SqlParameter("Final", Final)); //期末 //cmd.Parameters.Add(new SqlParameter("IAS39_CATEGORY", _IAS39_CATEGORY)); conn.Open(); var adapter = new SqlDataAdapter(cmd); adapter.Fill(resultsTable); var DateRange = new reportParm() { key = "DateRange", value = $@"{Initial_Date.ToString("yyyy/MM/dd")}~{Final_Date.ToString("yyyy/MM/dd")}" }; extensionParms.Add(DateRange); } } return(resultsTable); }
public override DataSet GetData(List <reportParm> parms) { var resultsTable = new DataSet(); using (var conn = new SqlConnection(defaultConnection)) { string sql = string.Empty; sql += $@" WITH TempD62 AS ( select TOP 1 Version from Bond_Basic_Assessment where Report_Date = @ReportDate ORDER BY Version DESC ), TempD62All AS ( select A41.Assessment_Sub_Kind AS Assessment_Sub_Kind, --產業別 (A41#48) D62.Bond_Type AS Bond_Type, --債券種類 (D62#5) D62.Bond_Number AS Bond_Number, --債券編號 (D62#8) D62.Lots AS Lots, --Lots (D62#9) D62.Portfolio_Name AS Portfolio_Name, -- Portfolio_英文 (D62#11) D62.Portfolio AS Portfolio, --Portfolio_中文 (D62#10) A41.Segment_Name, --債券(資產)名稱 (A41#4) convert(varchar, A41.Origination_Date, 111) AS Origination_Date, --債券購入(認列)日期 (62#12) D62.Cost_Value, --攤銷後之成本數(原幣) (D62#20) D62.Market_Value_Ori, --市價(原幣) (D62#21) D62.Value_Change_Ratio, --未實現累計損失率_本月 (D62#22) D62.Chg_In_Spread, --信用利差_本月 (D62#30) CASE WHEN D62.Original_External_Rating is null THEN 'N' ELSE 'Y' END AS Original_External_Rating_Flag, --是否有購買日信評 (判斷 D62#13) D62.Original_External_Rating, --購買日信評(主標尺轉換值) (D62#13) D62.Current_External_Rating, --報導日信評(主標尺轉換值) (D62#14) D62.Rating_Ori_Good_IND, --原始購買信評是否為低信用風險等級 (D62#15) D62.Rating_Curr_Good_Ind, --報導日信評是否為低信用風險等級 (D62#16) D62.Curr_Ori_Rating_Diff, --評等下降數 (D62#17) D62.Accumulation_Loss_This_Month, --未實現累計損失率逾越30%(含)連續月數 (D62#24) D62.Chg_In_Spread_This_Month,--信用利差拓寬300基點(含)以上連續月數 (D62#33) D62.Map_Rule_Id_D70 AS MapRule, --對應規則參數編號 (D62#27) D62.Version from (SELECT _D62.* FROM Bond_Basic_Assessment _D62 , TempD62 WHERE _D62.Report_Date = @ReportDate AND _D62.Version = TempD62.Version AND _D62.Watch_IND = 'Y' --觀察名單 ) AS D62 JOIN ( SELECT A41.* FROM Bond_Account_Info A41,TempD62 WHERE A41.Report_Date = @ReportDate AND A41.Version = TempD62.Version ) AS A41 ON D62.Reference_Nbr = A41.Reference_Nbr) Select * from TempD62All union select null AS Assessment_Sub_Kind, null AS Bond_Type, null AS Bond_Number, null AS Lots, null AS Portfolio_Name, null AS Portfolio, null AS Segment_Name, null AS Origination_Date, null AS Cost_Value, null AS Market_Value_Ori, null AS Value_Change_Ratio, null AS Chg_In_Spread, null AS Original_External_Rating_Flag, null AS Original_External_Rating, null AS Current_External_Rating, null AS Rating_Ori_Good_IND, null AS Rating_Curr_Good_Ind, null AS Curr_Ori_Rating_Diff, null AS Accumulation_Loss_This_Month, null AS Chg_In_Spread_This_Month, null AS MapRule, TempD62.Version AS Version from TempD62 "; using (var cmd = new SqlCommand(sql, conn)) { string reportDate = parms.Where(x => x.key == "ReportDate").FirstOrDefault()?.value ?? string.Empty; cmd.Parameters.Add(new SqlParameter("ReportDate", reportDate)); Extension.NlogSet(cmd.CommandText); conn.Open(); var adapter = new SqlDataAdapter(cmd); adapter.Fill(resultsTable); var Version = new reportParm() { key = "Version", value = "" }; var dt = resultsTable.Tables[0].AsEnumerable(); if (dt.Any()) { var first = dt.First(); Version.value = first.Field <int>("Version").ToString(); } extensionParms.Add(Version); } } return(resultsTable); }
public override DataSet GetData(List <reportParm> parms) { var resultsTable = new DataSet(); string Initial = parms.Where(x => x.key == "Initial").FirstOrDefault()?.value ?? string.Empty; string Final = parms.Where(x => x.key == "Final").FirstOrDefault()?.value ?? string.Empty; string Group_Product_Code = parms.Where(x => x.key == "Group_Product_Code").FirstOrDefault()?.value ?? string.Empty; using (var conn = new SqlConnection(defaultConnection)) { string sql = string.Empty; sql += $@" WITH T AS --期末 ( SELECT D52.* FROM (select * from IFRS9_Bond_Report WHERE Report_Date = @Final) D52 JOIN (select PRJID,FLOWID from Flow_Info where Group_Product_Code = @Group_Product_Code) FI on D52.PRJID = FI.PRJID and D52.FLOWID = FI.FLOWID ), T1 AS --期初 ( SELECT D52.* FROM (select * from IFRS9_Bond_Report WHERE Report_Date = @Initial) D52 JOIN (select PRJID,FLOWID from Flow_Info where Group_Product_Code = @Group_Product_Code) FI on D52.PRJID = FI.PRJID and D52.FLOWID = FI.FLOWID ), TEMP AS ( SELECT T1.Reference_Nbr, --Reference_Nbr T1.Security_Name, --Security Name(A) T1.Bond_Number, --債券編號(B) T1.Lots, --Lots(C) T1.Segment_Name, --債券(資產)名稱(D) T1.IAS39_CATEGORY, --公報分類(E) T1.Bond_Aera, --國內\國外(F) T1.Asset_Type, --金融商品分類(G) T1.Currency_Code, --幣別(H) T1.ASSET_SEG, --資產區隔(I) T1.IH_OS, --自操\委外(J) T1.Portfolio, --Portfolio(K) T1.PRODUCT, --SMF(L) T1.Ori_Amount AS Ori_Amount_T1, --期初帳列面額(原幣)(M) T.Ori_Amount AS Ori_Amount_T, --期末帳列面額(原幣)(N) T.Ori_Amount - T1.Ori_Amount AS Ori_Amount, --面額增(減)變動(原幣)(O) T1.Principal AS Principal_T1, --期初攤銷後成本(原幣)(P) T.Principal AS Principal_T, --期末攤銷後成本(原幣)(Q) T1.Interest_Receivable AS Interest_Receivable_T1, --期初應收利息(原幣)(R) T.Interest_Receivable AS Interest_Receivable_T, --期末應收利息(原幣)(S) T1.Impairment_Stage AS Impairment_Stage_T1, --期初減損階段(T) T.Impairment_Stage AS Impairment_Stage_T, --期末減損階段(U) T1.PD AS PD_T1, --期初第一年違約率(V) T.PD AS PD_T, --期末第一年違約率(W) CASE WHEN ISNULL((T1.Principal * T1.Current_LGD) , 0) = 0 THEN NULL ELSE T1.Principal_EL_Ori/(T1.Principal * T1.Current_LGD) END AS Default_Rate_T1, --期初違約率(X) CASE WHEN ISNULL((T.Principal * T.Current_LGD) , 0) = 0 THEN NULL ELSE T.Principal_EL_Ori/(T.Principal * T.Current_LGD) END AS Default_Rate_T, --期末違約率(Y) T1.Current_LGD AS Current_LGD_T1, --期初違約損失率(Z) T.Current_LGD AS Current_LGD_T, --期末違約損失率(AA) T1.Ori_Ex_rate, --成本匯率(AB) T1.Ex_rate AS Ex_rate_T1, --期初報表日匯率(AC) T.Ex_rate AS Ex_rate_T, --報表日匯率(AD) T1.Principal_EL_Ori AS Principal_EL_Ori_T1, --期初累計減損_本金(原幣)(AE) T1.Principal_EL_Ori_Ex AS Principal_EL_Ori_Ex_T1, --期初累計減損_本金(成本匯率台幣)(AF) T1.Principal_EL_Ex AS Principal_EL_Ex_T1, --期初累計減損_本金(期初報表日匯率台幣)(AG) T1.Principal_Diff_Tw AS Principal_Diff_Tw_T1, --期初累計減損_本金匯兌損益(台幣)(AH) T.Principal_EL_Ori AS Principal_EL_Ori_T, --本期累計減損_本金(原幣)(AI) T.Principal_EL_Ori_Ex AS Principal_EL_Ori_Ex_T, --本期累計減損_本金(成本匯率台幣)(AJ) T.Principal_EL_Ex AS Principal_EL_Ex_T, --本期累計減損_本金(報表日匯率台幣)(AK) T.Principal_Diff_Tw AS Principal_Diff_Tw_T, --本期累計減損_本金匯兌損益(台幣)(AL) CASE WHEN ISNULL(T.Ori_Amount,0) = 0 THEN T1.Principal_EL_Ori ELSE 0 END AS Excluding_Monetary_Assets_ECL_Ori, --除列之金融資產ECL(原幣)(AM) CASE WHEN ISNULL(T.Ori_Amount,0) = 0 THEN T1.Principal_EL_Ex ELSE 0 END AS Excluding_Monetary_Assets_ECL_Ex, --除列之金融資產ECL(期初報表日匯率台幣)(AN) CASE WHEN (T.Ori_Amount > 0 AND (T.Ori_Amount - T1.Ori_Amount) < 0) THEN T1.Principal_EL_Ori * ABS( (T.Ori_Amount - T1.Ori_Amount) / T1.Ori_Amount ) ELSE 0 END AS Section_Excluding_Monetary_Assets_ECL_Ori, --部分除列之金融資產ECL(原幣)(AO) CASE WHEN (T.Ori_Amount > 0 AND (T.Ori_Amount - T1.Ori_Amount) < 0) THEN T1.Principal_EL_Ex * ABS( (T.Ori_Amount - T1.Ori_Amount) / T1.Ori_Amount ) ELSE 0 END AS Section_Excluding_Monetary_Assets_ECL_Ex --部分除列之金融資產ECL(報表日匯率台幣)(AP) FROM T1 left join T ON T1.Bond_Number = CASE WHEN T.ISIN_Changed_Ind = 'Y' THEN T.Bond_Number_Old ELSE T.Bond_Number END AND T1.Lots = CASE WHEN T.ISIN_Changed_Ind = 'Y' THEN T.Lots_Old ELSE T.Lots END AND T1.Portfolio_Name = CASE WHEN T.ISIN_Changed_Ind = 'Y' THEN T.Portfolio_Name_Old ELSE T.Portfolio_Name END ) select TEMP.*, TEMP.Principal_EL_Ori_T1 - TEMP.Excluding_Monetary_Assets_ECL_Ori - TEMP.Section_Excluding_Monetary_Assets_ECL_Ori AS Final_Remaining_Part_ECL_Ori, --期初剩餘部位之期末ECL(原幣)(AQ) = AE欄-AM欄-AO欄 TEMP.Principal_EL_Ex_T1 - TEMP.Excluding_Monetary_Assets_ECL_Ex - TEMP.Section_Excluding_Monetary_Assets_ECL_Ex AS Final_Remaining_Part_ECL_Ex --期初剩餘部位之期末ECL(報表日匯率台幣)(AR) = AG欄-AN欄-AP欄 from TEMP ; "; DateTime Initial_Date = DateTime.Parse(Initial); DateTime Final_Date = DateTime.Parse(Final); //using (IFRS9DBEntities db = new IFRS9DBEntities()) //{ // var _data = db.Database.DynamicSqlQuery(sql // , null, // new object[] { // new SqlParameter("Initial", Initial), // new SqlParameter("Final", Final), // new SqlParameter("Group_Product_Code", Group_Product_Code) // } // ); // db.Database.Connection.Close(); // resultsTable.Tables.Add(_data.ToDataTable()); //} using (var cmd = new SqlCommand(sql, conn)) { var _Initial = new SqlParameter(); _Initial.SqlDbType = SqlDbType.VarChar; _Initial.ParameterName = "@Initial"; _Initial.Value = Initial; var _Final = new SqlParameter(); _Final.SqlDbType = SqlDbType.VarChar; _Final.ParameterName = "@Final"; _Final.Value = Final; var _Group_Product_Code = new SqlParameter(); _Group_Product_Code.SqlDbType = SqlDbType.VarChar; _Group_Product_Code.ParameterName = "@Group_Product_Code"; _Group_Product_Code.Value = Group_Product_Code; cmd.Parameters.Add(_Initial); //期初 cmd.Parameters.Add(_Final); //期末 cmd.Parameters.Add(_Group_Product_Code); conn.Open(); var adapter = new SqlDataAdapter(cmd); adapter.Fill(resultsTable); } var DateRange = new reportParm() { key = "DateRange", value = $@"{Initial_Date.ToString("yyyy/MM/dd")}~{Final_Date.ToString("yyyy/MM/dd")}" }; extensionParms.Add(DateRange); } return(resultsTable); }
public override DataSet GetData(List <reportParm> parms) { var resultsTable = new DataSet(); string Initial = parms.Where(x => x.key == "Initial").FirstOrDefault()?.value ?? string.Empty; string Final = parms.Where(x => x.key == "Final").FirstOrDefault()?.value ?? string.Empty; string Group_Product_Code = parms.Where(x => x.key == "Group_Product_Code").FirstOrDefault()?.value ?? string.Empty; using (var conn = new SqlConnection(defaultConnection)) { string sql = string.Empty; sql += $@" WITH T AS --期末 ( SELECT D52.* FROM (select * from IFRS9_Bond_Report WHERE Report_Date = @Final ) D52 JOIN (select PRJID,FLOWID from Flow_Info where Group_Product_Code = @Group_Product_Code ) FI on D52.PRJID = FI.PRJID and D52.FLOWID = FI.FLOWID ), T1 AS --期初 ( SELECT D52.* FROM (select * from IFRS9_Bond_Report WHERE Report_Date = @Initial ) D52 JOIN (select PRJID,FLOWID from Flow_Info where Group_Product_Code = @Group_Product_Code ) FI on D52.PRJID = FI.PRJID and D52.FLOWID = FI.FLOWID ), TEMP AS ( SELECT T.Reference_Nbr, -- Reference_Nbr T.Security_Name, --Security Name(A) T.Bond_Number, --債券編號(B) T.Lots, --Lots(C) T.Segment_Name, --債券(資產)名稱(D) T.IAS39_CATEGORY, --公報分類(E) T.Bond_Aera, --國內\國外(F) T.Asset_Type, --金融商品分類(G) T.Currency_Code, --幣別(H) T.ASSET_SEG, --資產區隔(I) T.IH_OS, --自操\委外(J) T.Portfolio, --Portfolio(K) T.PRODUCT, --SMF(L) T1.Ori_Amount AS Ori_Amount_T1, --期初帳列面額(原幣)(M) T.Ori_Amount AS Ori_Amount_T, --期末帳列面額(原幣)(N) T.Ori_Amount - T1.Ori_Amount AS Ori_Amount, --面額增(減)變動(原幣)(O) T1.Principal AS Principal_T1, --期初攤銷後成本(原幣)(P) T.Principal AS Principal_T, --期末攤銷後成本(原幣)(Q) T1.Interest_Receivable AS Interest_Receivable_T1, --期初應收利息(原幣)(R) T.Interest_Receivable AS Interest_Receivable_T, --期末應收利息(原幣)(S) T1.Impairment_Stage AS Impairment_Stage_T1, --期初減損階段(T) T.Impairment_Stage AS Impairment_Stage_T, --期末減損階段(U) T1.PD AS PD_T1, --期初第一年違約率(V) T.PD AS PD_T, --期末第一年違約率(W) CASE WHEN ISNULL((T1.Principal * T1.Current_LGD), 0) = 0 THEN NULL ELSE T1.Principal_EL_Ori/(T1.Principal * T1.Current_LGD) END AS Default_Rate_T1, --期初違約率(X) CASE WHEN ISNULL((T.Principal * T.Current_LGD) , 0) = 0 THEN NULL ELSE T.Principal_EL_Ori/(T.Principal * T.Current_LGD) END AS Default_Rate_T, --期末違約率(Y) T1.Current_LGD AS Current_LGD_T1, --期初違約損失率(Z) T.Current_LGD AS Current_LGD_T, --期末違約損失率(AA) T.Ori_Ex_rate, --成本匯率(AB) T1.Ex_rate AS Ex_rate_T1, --期初報表日匯率(AC) T.Ex_rate AS Ex_rate_T, --報表日匯率(AD) T1.Principal_EL_Ori AS Principal_EL_Ori_T1, --期初累計減損_本金(原幣)(AE) T1.Principal_EL_Ori_Ex AS Principal_EL_Ori_Ex_T1, --期初累計減損_本金(成本匯率台幣)(AF) T1.Principal_EL_Ex AS Principal_EL_Ex_T1, --期初累計減損_本金(期初報表日匯率台幣)(AG) T1.Principal_Diff_Tw AS Principal_Diff_Tw_T1, --期初累計減損_本金匯兌損益(台幣)(AH) T.Principal_EL_Ori AS Principal_EL_Ori_T, --本期累計減損_本金(原幣)(AI) T.Principal_EL_Ori_Ex AS Principal_EL_Ori_Ex_T, --本期累計減損_本金(成本匯率台幣)(AJ) T.Principal_EL_Ex AS Principal_EL_Ex_T, --本期累計減損_本金(報表日匯率台幣)(AK) T.Principal_Diff_Tw AS Principal_Diff_Tw_T, --本期累計減損_本金匯兌損益(台幣)(AL) CASE WHEN ISNULL(T1.Ori_Amount,0) = 0 THEN T.Principal_EL_Ori ELSE 0 END AS Purchase_Monetary_Assets_ECL_Ori, --新購入之金融資產ECL(原幣)(AM) CASE WHEN ISNULL(T1.Ori_Amount,0) = 0 THEN T.Principal_EL_Ex ELSE 0 END AS Purchase_Monetary_Assets_ECL_Ex, --新購入之金融資產ECL(報表日匯率台幣)(AN) CASE WHEN (T1.Ori_Amount > 0 AND (T.Ori_Amount - T1.Ori_Amount) > 0) THEN T.Principal_EL_Ori * (T.Ori_Amount - T1.Ori_Amount) / T1.Ori_Amount ELSE 0 END AS Section_Purchase_Monetary_Assets_ECL_Ori, --部分新增之金融資產ECL(原幣)(AO) CASE WHEN (T1.Ori_Amount > 0 AND (T.Ori_Amount - T1.Ori_Amount) > 0) THEN T.Principal_EL_Ex * (T.Ori_Amount - T1.Ori_Amount) / T1.Ori_Amount ELSE 0 END AS Section_Purchase_Monetary_Assets_ECL_Ex, --部分新增之金融資產ECL(報表日匯率台幣)(AP) T.ISIN_Changed_Ind AS ISIN_Changed_Ind, --是否為換券 T.Bond_Number_Old AS Bond_Number_Old, --債券編號_換券前 T.Lots_Old AS Lots_Old, --Lots_舊 T.Portfolio_Name_Old AS Portfolio_Name_Old, --Portfolio英文_舊 convert(varchar, T.Origination_Date_Old , 111) AS Origination_Date_Old --舊券原始購入日 FROM T Left join T1 ON T1.Bond_Number = CASE WHEN T.ISIN_Changed_Ind = 'Y' THEN T.Bond_Number_Old ELSE T.Bond_Number END AND T1.Lots = CASE WHEN T.ISIN_Changed_Ind = 'Y' THEN T.Lots_Old ELSE T.Lots END AND T1.Portfolio_Name = CASE WHEN T.ISIN_Changed_Ind = 'Y' THEN T.Portfolio_Name_Old ELSE T.Portfolio_Name END ) select TEMP.*, TEMP.Principal_EL_Ori_T - TEMP.Purchase_Monetary_Assets_ECL_Ori - TEMP.Section_Purchase_Monetary_Assets_ECL_Ori AS Initial_Remaining_Part_ECL_Ori, --期初剩餘部位之期末ECL(原幣)(AQ) = AI欄-AM欄-AO欄 TEMP.Principal_EL_Ex_T - TEMP.Purchase_Monetary_Assets_ECL_Ex - TEMP.Section_Purchase_Monetary_Assets_ECL_Ex AS Initial_Remaining_Part_ECL_Ex, --期初剩餘部位之期末ECL(報表日匯率台幣)(AR) = AK欄-AN欄-AP欄 (TEMP.Principal_EL_Ori_T - TEMP.Purchase_Monetary_Assets_ECL_Ori - TEMP.Section_Purchase_Monetary_Assets_ECL_Ori) * Ex_rate_T1 AS Initial_Remaining_Part_ECL_Ex_Rate, --期初剩餘部位之期末ECL(期初報表日匯率台幣) (AS) = AQ欄*AC欄 (TEMP.Principal_EL_Ex_T - TEMP.Purchase_Monetary_Assets_ECL_Ex - TEMP.Section_Purchase_Monetary_Assets_ECL_Ex) - (TEMP.Principal_EL_Ori_T - TEMP.Purchase_Monetary_Assets_ECL_Ori - TEMP.Section_Purchase_Monetary_Assets_ECL_Ori) * Ex_rate_T1 AS Exchange_Changes --匯兌變動影響(AT) = AR欄-AS欄 from TEMP ; "; using (var cmd = new SqlCommand(sql, conn)) { DateTime Initial_Date = DateTime.Parse(Initial); DateTime Final_Date = DateTime.Parse(Final); var _Initial = new SqlParameter(); _Initial.SqlDbType = SqlDbType.VarChar; _Initial.ParameterName = "@Initial"; _Initial.Value = Initial; _Initial.Direction = ParameterDirection.Input; var _Final = new SqlParameter(); _Final.SqlDbType = SqlDbType.VarChar; _Final.ParameterName = "@Final"; _Final.Value = Final; _Final.Direction = ParameterDirection.Input; var _Group_Product_Code = new SqlParameter(); _Group_Product_Code.SqlDbType = SqlDbType.VarChar; _Group_Product_Code.ParameterName = "@Group_Product_Code"; _Group_Product_Code.Value = Group_Product_Code; _Group_Product_Code.Direction = ParameterDirection.Input; cmd.Parameters.Add(_Initial); //期初 cmd.Parameters.Add(_Final); //期末 cmd.Parameters.Add(_Group_Product_Code); conn.Open(); var adapter = new SqlDataAdapter(cmd); adapter.Fill(resultsTable); var DateRange = new reportParm() { key = "DateRange", value = $@"{Initial_Date.ToString("yyyy/MM/dd")}~{Final_Date.ToString("yyyy/MM/dd")}" }; extensionParms.Add(DateRange); } } return(resultsTable); }