/// <summary>
/// Get bond detail data
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public IEnumerable <BondDetail> GetBondDetailByTypeAndSubType(BondDetailParams param)
{
var table = GetIssueAmountBondDetailByTypeAndSubType(param);
var pResult = DataTableSerializer.ToList <BondDetail>(table);
return(pResult);
}
private JsonTable GetBottomTable(BondDetailParams param, int id)
{
var userColumnService = (UserColumnService)DependencyResolver.Current.GetService(typeof(UserColumnService));
var columns = userColumnService.GetUserColumns(UserSettingHelper.GetUserId(Request), id);
int total;
var bottomTable = BondReportRepository.GetBondDetailByTypeNew(param, out total);
var reportcolumndefinitions = columns as REPORTCOLUMNDEFINITION[] ?? columns.ToArray();
var jsonTable = BuidJsonTable(bottomTable, reportcolumndefinitions, total, param.StartPage, param.PageSize);
List <Dictionary <string, string> > rowData = jsonTable.RowData;
foreach (Dictionary <string, string> currentRow in rowData)
{
foreach (DataRow row in bottomTable.Rows)
{
if (row["AssetId"].ToString() == currentRow["AssetId"])
{
if (!reportcolumndefinitions.Any(x => x.COLUMN_NAME == "IssuerOrgId"))
{
currentRow.Add("IssuerOrgId", row["IssuerOrgId"].ToString());
}
break;
}
}
}
return(jsonTable);
}
public ActionResult ExportExcelForIssuanceMaturesDetail(DateTime start, DateTime end, string type, string typeValue, int useSubType, string subType, string subTypeValue, string unit, string term, string reportName, int id, string order, string itemList, int startPage, int pageSize)
{
var userColumnService = (UserColumnService)DependencyResolver.Current.GetService(typeof(UserColumnService));
var columns = userColumnService.GetUserColumns(UserSettingHelper.GetUserId(Request), id);
var param = new BondDetailParams
{
Type = type,
TypeValue = typeValue,
UseSubType = useSubType == 1,
SubType = subType,
SubTypeValue = subTypeValue,
StartDate = start,
EndDate = end,
Term = term,
OrderBy = order,
ItemList = itemList,
StartPage = startPage,
PageSize = pageSize
};
int total;
var bottomTable = BondReportRepository.GetBondDetailByTypeNew(param, out total);
ReportParameter reportParam = new ReportParameter
{
StartDate = start,
EndDate = end,
Unit = string.IsNullOrEmpty(unit) ? "100M" : unit,
};
var reportcolumndefinitions = columns as REPORTCOLUMNDEFINITION[] ?? columns.ToArray();
return(new ExcelResult(bottomTable.AsEnumerable().AsQueryable(), reportcolumndefinitions.Select(x => x.DisplayName).ToArray(), reportcolumndefinitions.Select(x => x.COLUMN_NAME).ToArray(), reportName, reportName, false, null, reportParam, false, specificDateFormat: "yyyy-MM-dd"));
}
/// <summary>
/// Get bond depository data for isssue bond and mutarity bond
/// </summary>
/// <param name="param"></param>
/// <param name="isIssued"></param>
/// <returns></returns>
public IEnumerable <BondDetail> GetBondDetailByType(BondDetailParams param)
{
return(BondReportRepository.GetBondDetailByType(param));
}
/// <summary>
/// Get bond detail by type and date
/// </summary>
/// <param name="reportParams"></param>
/// <returns></returns>
public IEnumerable <BondDetail> GetBondDetailByTypeAndSubType(BondDetailParams reportParams)
{
return(BondReportRepository.GetBondDetailByTypeAndSubType(reportParams));
}
public DataTable GetIssueAmountBondDetailByTypeAndSubType(BondDetailParams param)
{
if (param.StartDate != null)
{
if (param.EndDate != null)
{
var paramArray = new[]
{
new OracleParameter("AssetType", OracleDbType.Varchar2)
{
Value = param.Type
},
new OracleParameter("AssetTypeValue", OracleDbType.Varchar2)
{
Value = param.TypeValue
},
new OracleParameter("IsUseSubAssetType", OracleDbType.Int32)
{
Value = param.UseSubType ? 1 : 0
},
new OracleParameter("SubAssetType", OracleDbType.Varchar2)
{
Value = param.SubType
},
new OracleParameter("SubAssetTypeValue", OracleDbType.Varchar2)
{
Value = param.SubTypeValue
},
new OracleParameter("StartDate", OracleDbType.TimeStamp)
{
Value = (OracleTimeStamp)param.StartDate.Value
},
new OracleParameter("EndDate", OracleDbType.TimeStamp)
{
Value = (OracleTimeStamp)param.EndDate.Value
},
new OracleParameter("Culture", OracleDbType.Varchar2)
{
Value = Thread.CurrentThread.CurrentUICulture.Name
},
new OracleParameter("ItemList", OracleDbType.Varchar2)
{
Value = param.ItemList
},
new OracleParameter("StartPage", OracleDbType.Int32)
{
Value = param.StartPage
},
new OracleParameter("PageSize", OracleDbType.Int32)
{
Value = param.PageSize
},
new OracleParameter("Total", OracleDbType.Int32, ParameterDirection.Output),
new OracleParameter("Cur", OracleDbType.RefCursor)
{
Direction = ParameterDirection.Output
}
};
object value;
var table = GetDataSetBySpFromBondDB("GetBondIssueAmountDetail", paramArray, "Total", out value).Tables[0];
//var total = value.ToString() != "null" ? Convert.ToInt32(value.ToString()) : 0;
return(table);
}
}
return(new DataTable());
}
public IEnumerable <BondDetail> GetBondDetailByType(BondDetailParams param)
{
Func <BOND, bool> whereFunc = GetFunc(param.Type, param.TypeValue);
Func <BOND, bool> dateFilter = b => (b.orig_issue_dt >= param.StartDate && b.orig_issue_dt <= param.EndDate) || (b.maturity_dt >= param.StartDate && b.maturity_dt <= param.EndDate);
using (var bonddb = new BondDBEntities())
{
return(bonddb.BOND
.Where(whereFunc)
.Where(dateFilter)
.Select(b => new BondDetail
{
AssetId = b.assetId,
Code = b.code,
BondNameCn = b.bond_name_cn,
BondNameEn = b.bond_name_en,
BondTermEn = b.BondTerm_en,
BondTermCn = b.BondTerm_cn,
BondRating = b.latest_rating_cd,
BondRatingAgencyEn = b.rating_src_en,
BondRatingAgencyCn = b.rating_src_cn,
PartyRating = b.party_rating_cd,
PartyRatingAgencyCn = b.party_rating_src_cn,
PartyRatingAgencyEn = b.party_rating_src_en,
IssueDate = b.orig_issue_dt,
ValueDate = b.orig_dated_dt,
MaturityDate = b.maturity_dt,
ListingDate = b.listing_dt,
IssueAmount = b.orig_iss_amt,
IssuePrice = b.orig_iss_px,
RefYield = b.yield,
CouponClassCn = b.coupon_class_cn,
CouponClassEn = b.coupon_class_en,
CouponFreqEn = b.freq_en,
CouponFreqCn = b.freq_cn,
CouponRate = b.orig_iss_cpn,
CDCType = b.cdc_asset_class_cd,
CDCTypeCn = b.cdc_asset_class_cn,
CDCTypeEn = b.cdc_asset_class_en,
Currency = b.orig_iss_curr_cd,
FloatIndex = b.float_index,
Spread = b.float_offset,
DayCountEn = b.day_count_en,
DayCountCn = b.day_count_cn,
OptionEn = b.callorput_en,
OptionCn = b.callorput_cn,
Issuer = b.offer_registrant_name,
ISBN = b.isin_nm,
Seniority = b.seniority,
IsIssued = b.orig_issue_dt >= param.StartDate && b.orig_issue_dt <= param.EndDate,
IsMatured = b.maturity_dt >= param.StartDate && b.maturity_dt <= param.EndDate,
OrigAvgLife = b.orig_avg_life,
Term = Convert.ToString(b.term),
re_issue = b.re_issue,
ExchangeNameEn = b.exchange_name_en,
ExchangeNameCn = b.exchange_name_cn,
TrusteeNameCn = b.trustee_name_cn,
TrusteeNameEn = b.trustee_name_en
}).ToList().OrderByDescending(b => b.ValueDate));
}
}
private void GetIssueAmountReportData(BondIssueAmountParams param, out IEnumerable <BondIssueAmount> topGrid, out IEnumerable <BondDetail> bottomGrid)
{
var detailParam = new BondDetailParams();
if (param == null)
{
int year = DateTime.Now.Year;
int month;
if (DateTime.Now.Month == 1)
{
year = DateTime.Now.Year - 1;
month = 12;
}
else
{
month = DateTime.Now.Month - 1;
}
param = new BondIssueAmountParams();
param.Type = "Bond_Class";
param.TypeList = UIStaticDataCache.Instance.AssetClass.Select(re => re.Value).ToList();
param.Unit = "100M";
param.UseSubType = false;
param.SubType = "";
param.StartDate = new DateTime(year, month, 1);
param.EndDate = DateTime.Now;
_log.Error("GetIssueAmount Data 1:" + DateTime.Now);
topGrid = ReportService.GetIssueAmount(param).ToList();
_log.Error("GetIssueAmount Data 2:" + DateTime.Now);
if (topGrid.Count() == 0)
{
bottomGrid = new List <BondDetail>();
}
else
{
detailParam.Type = ConstValues.Type_Bond_Class;
detailParam.TypeValue = topGrid.Select(t => t.Type).First();
detailParam.StartDate = new DateTime(year, month, 1);
detailParam.EndDate = DateTime.Now;
detailParam.UseSubType = false;
detailParam.ItemList = detailParam.TypeValue;
detailParam.StartPage = 1;
detailParam.PageSize = 300;
bottomGrid = ReportService.GetBondDetailByTypeAndSubType(detailParam);
_log.Error("GetIssueAmount Data 3:" + DateTime.Now);
}
}
else
{
_log.Error("GetIssueAmount Data 4:" + DateTime.Now);
topGrid = ReportService.GetIssueAmount(param);
_log.Error("GetIssueAmount Data 5:" + DateTime.Now);
var bondIssueAmounts = topGrid as BondIssueAmount[] ?? topGrid.ToArray();
if (bondIssueAmounts.Count() == 0)
{
bottomGrid = new List <BondDetail>();
}
else
{
detailParam.Type = param.Type;
detailParam.TypeValue = bondIssueAmounts.Select(t => t.Type).First();
detailParam.StartDate = param.StartDate;
detailParam.EndDate = param.EndDate;
detailParam.UseSubType = false;
detailParam.PageSize = 300;
detailParam.StartPage = 1;
detailParam.ItemList = string.Join(",", param.TypeList);
bottomGrid = ReportService.GetBondDetailByTypeAndSubType(detailParam);
}
_log.Error("GetIssueAmount Data 6:" + DateTime.Now);
}
}
public ActionResult ExportReport(string reportId, string startDate, string endDate, string type, string typeValue, string typeList, string subType, string subTypeValue, string useSecType, string unit, string isParent, string reportName, string reportType, int startPage = 1)
{
IEnumerable <BondIssueAmountExport> summaryExport;
IEnumerable <BondDetail> detailExport;
BondIssueAmountParams summaryParam;
BondDetailParams detailParam;
string[] headers;
string[] rowKeys;
if (reportType == "summary")
{
summaryParam = new BondIssueAmountParams
{
Type = type,
TypeList = string.IsNullOrEmpty(typeList) ? null : typeList.Split(',').ToList(),
UseSubType = useSecType == "y",
SubType = subType,
StartDate = DateTime.Parse(startDate),
EndDate = DateTime.Parse(endDate),
Unit = unit
};
var summaryReport = ReportService.GetIssueAmount(summaryParam);
summaryExport = (from s in summaryReport
select new BondIssueAmountExport
{
TypeName = s.IsParent == 1 ? (string.IsNullOrEmpty(s.Type) ? Resources.Global.Tip_Other : s.TypeName) : (string.IsNullOrEmpty(s.SubType) ? Resources.Global.Tip_Other : s.SubTypName),
Issues = s.Issues,
IssuesPercent = s.IssuesPercent,
IssuesAmount = s.IssuesAmount,
IssuesAmountPercent = s.IssuesAmountPercent,
LowestIssueRate = s.LowestIssueRate,
HighestIssueRate = s.HighestIssueRate,
Row_level = s.IsParent == 1 ? "0" : "1"
}).ToList();
headers = GetIssueAmountHeader("summary");
rowKeys = GetIssueAmountCloumns("summary");
string sumGroupColumnName = "TypeName";
string groupedRowLevelColumnName = "Row_level";
try
{
return(new ExcelResult(summaryExport.AsQueryable(), headers, rowKeys, reportName, reportName, sumGroupColumnName: sumGroupColumnName, groupedRowLevelColumnName: groupedRowLevelColumnName));
}
catch (Exception ex)
{
throw ex;
}
}
else
{
detailParam = new BondDetailParams
{
Type = type,
TypeValue = typeValue,
UseSubType = (useSecType == "y"), // && !string.IsNullOrEmpty(subTypeValue)) ? true : false,
SubType = subType,
SubTypeValue = subTypeValue,
IsParent = isParent == "true",
StartDate = DateTime.Parse(startDate),
EndDate = DateTime.Parse(endDate),
ItemList = typeList,
PageSize = 300,
StartPage = startPage
};
detailExport = ReportService.GetBondDetailByTypeAndSubType(detailParam);
headers = GetIssueAmountHeader("detail");
rowKeys = GetIssueAmountCloumns("detail");
var bondDetails = detailExport as BondDetail[] ?? detailExport.ToArray();
bondDetails.ToList().ForEach(r => r.Term = r.Term + (r.OrigAvgLife < 1 ? Resources.Global.Time_Day : Resources.Global.Time_Year));
string dateFormat = "yyyy-MM-dd";
try
{
return(new ExcelResult(bondDetails.AsQueryable(), headers, rowKeys, reportName, reportName, specificDateFormat: dateFormat));
}
catch (Exception ex)
{
throw ex;
}
}
}
