protected DataSet GetDataSetBySpWithOutParaFromBondDb(string inName, OracleParameter[] inParms, out object outPara)
{
using (var bonddb = new BondDBEntities())
{
using (var spCmd = new OracleCommand())
{
bonddb.Database.Connection.Open();
spCmd.Connection = (OracleConnection)(bonddb.Database.Connection);
spCmd.CommandText = inName;
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
spCmd.Parameters.AddRange(inParms);
var da = new OracleDataAdapter(spCmd);
var ds = new DataSet();
da.Fill(ds);
outPara = spCmd.Parameters["OutPara"].Value;
return(ds);
}
}
}
public static void UpdateBondInfo(DateTime lastSyncTime, DateTime currentSyncTime)
{
using (var Db = new BondDBEntities())
{
try
{
Db.Database.Connection.Open();
DbCommand cmd = Db.Database.Connection.CreateCommand();
cmd.CommandText = "UpdateBondInfo";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.Parameters.AddRange(new[] {
new OracleParameter("LastSyncTime", OracleDbType.TimeStamp)
{
Value = lastSyncTime
},
new OracleParameter("CurrentSyncTime", OracleDbType.TimeStamp)
{
Value = currentSyncTime
}
});
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
Db.Database.Connection.Close();
}
}
}
public BaseReportRepository()
{
using (var bonddb = new BondDBEntities())
{
if (_typeOrder == null)
{
_typeOrder = bonddb.TYPEORDER.ToArray();
}
}
using (var CMADB = new CMAEntities())
{
if (_localizations == null)
{
_localizations = CMADB.LOCALIZATIONs.ToArray();
}
if (_columnDefination == null)
{
_columnDefination = CMADB.REPORTCOLUMNDEFINITIONs.ToArray();
}
}
using (var CMADB = new CMAEntities())
{
if (_gdtcolumnDefination == null)
{
_gdtcolumnDefination = CMADB.COLUMNDEFINITIONs.ToArray();
}
}
}
/// <summary>
/// Get data by store procedure
/// </summary>
/// <param name="inName">sp name</param>
/// <param name="inParms">parameters</param>
/// <returns></returns>
protected DataSet GetDataSetBySpFromBondDB(string inName, OracleParameter[] inParms, string outName, out object value)
{
using (var bonddb = new BondDBEntities())
{
using (var cmd = new OracleCommand())
{
bonddb.Database.Connection.Open();
cmd.Connection = (OracleConnection)(bonddb.Database.Connection);
cmd.CommandText = inName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
if (inParms != null)
{
cmd.Parameters.AddRange(inParms);
}
var da = new OracleDataAdapter(cmd);
var ds = new DataSet();
da.Fill(ds);
value = cmd.Parameters[outName].Value;
return(ds);
}
}
}
public string GetBondCodeById(string id)
{
using (var VAVDB = new BondDBEntities())
{
return(VAVDB.V_BOND_CN.Where(b => b.ASSETID == id).Select(b => b.CODE).FirstOrDefault());
}
}
public BondBasicInfo GetBondBasicInfoById(string id, string culture)
{
using (var VAVDB = new BondDBEntities())
{
if (culture == "zh-CN")
{
return(VAVDB.V_BOND_CN
.Where(b => b.ASSETID == id)
.Select(b => new BondBasicInfo
{
AssetId = id,
Issuer = b.ISSUER,
BondClassDescr = b.BONDCLASSDESCR,
CouponClassDescr = b.COUPONCLASSDESCR,
IssuerInduSectorDescr = b.ISSUERINDUSECTORDESCR,
PayFrequency = b.PAYFREQUENCY
}).FirstOrDefault());
}
else
{
return(VAVDB.V_BOND_EN
.Where(b => b.ASSETID == id)
.Select(b => new BondBasicInfo
{
AssetId = id,
Issuer = b.ISSUER,
BondClassDescr = b.BONDCLASSDESCR,
CouponClassDescr = b.COUPONCLASSDESCR,
IssuerInduSectorDescr = b.ISSUERINDUSECTORDESCR,
PayFrequency = b.PAYFREQUENCY
}).FirstOrDefault());
}
}
}
public IEnumerable <GCODES_COUPON_CLASS_CDS> GetCouponItems()
{
using (var bonddb = new BondDBEntities())
{
return(bonddb.GCODES_COUPON_CLASS_CDS.ToList());
}
}
public IEnumerable <BondInfoOption> GetPartyCntryIncorpEn()
{
using (var bonddb = new BondDBEntities())
{
return((from area in bonddb.BOND
where area.PartyCntryIncorpCd != null
orderby area.PartyCntryIncorpCd
select new BondInfoOption {
Type = area.PartyCntryIncorpDescr_en, Name = area.PartyCntryIncorpDescr_en.Trim()
}).Distinct().ToList());
}
}
/// <summary>
/// Get type order dic from db
/// </summary>
/// <returns></returns>
public Dictionary <string, int> GetTypeOrder()
{
Dictionary <string, int> typeOrderDic = new Dictionary <string, int>();
using (var cmadb = new BondDBEntities())
{
var typeOrder = from t in cmadb.TYPELIST select new { key = t.ENGLISH_NAME, value = t.TABLE_CD };
foreach (var i in typeOrder)
{
typeOrderDic.Add(i.key, (int)i.value);
}
}
return(typeOrderDic);
}
public BondExchangeCode GetBondExchangeCodeById(string id)
{
using (var bonddb = new BondDBEntities())
{
var exchangeCodes = bonddb.GOVCORP_ASSET_IDENT.Where(re => re.ASSETID == id).ToList();
var bondInfo = bonddb.V_BOND_CN.Where(b => b.ASSETID == id).FirstOrDefault();
if (bondInfo == null)
{
bondInfo = new V_BOND_CN();
}
return(new BondExchangeCode
{
AssetId = id,
Ric = bondInfo.RIC,
Isin = bondInfo.ISINNUM,
ExchaneCodeShg = exchangeCodes.FirstOrDefault(re => re.ID_CD == "SHG") != null?exchangeCodes.FirstOrDefault(re => re.ID_CD == "SHG").ID_NUMBER : "",
ExchaneCodeShz = exchangeCodes.FirstOrDefault(re => re.ID_CD == "SHZ") != null?exchangeCodes.FirstOrDefault(re => re.ID_CD == "SHZ").ID_NUMBER : "",
ExchaneCodeShc = exchangeCodes.FirstOrDefault(re => re.ID_CD == "SHC") != null?exchangeCodes.FirstOrDefault(re => re.ID_CD == "SHC").ID_NUMBER : "",
ExchaneCodeSed = exchangeCodes.FirstOrDefault(re => re.ID_CD == "SED") != null?exchangeCodes.FirstOrDefault(re => re.ID_CD == "SED").ID_NUMBER : ""
});
}
}
/// <summary>
/// Add value to the OptionType propity of the object got from the DB
/// </summary>
/// <param name="temp">The entity got from the database</param>
/// <returns></returns>
private IEnumerable <OpenMarketRepo> SetReportOption(List <OpenMarketRepo> repoList)
{
List <OpenMarketRepo> repo = new List <OpenMarketRepo>();
IEnumerable <string> assetIds;
using (var bonddb = new BondDBEntities())
{
assetIds = (from a in bonddb.V_ASSET where a.CDC_ASSET_CLASS_CD == "CBB" select a.ID_NUMBER).Distinct().ToList();
}
foreach (OpenMarketRepo item in repoList)
{
if (item.OperationType == null)
{
item.OperationType = "";
}
if (item.OperationTerm == null)
{
item.OperationTerm = "";
}
if (item.Direction == null)
{
item.Direction = "";
}
string subStr = item.Code.Substring(0, 2);
if (subStr == "CN")
{
string subStr1 = item.Code.Substring(item.Code.TrimEnd().Length - 8, 3);
string subStr2 = item.Code.Substring(item.Code.TrimEnd().Length - 7, 2);
string subMlf = item.Code.Substring(2, 3);
if (subMlf == "MLF")
{
item.Category = "MLF";
item.OperationType = "MLF" + item.OperationType.Trim();
repo.Add(item);
}
else if (subStr1 == "RRP")
{
item.Category = "RRP";
item.OperationType = "RRP" + item.OperationType.Trim();
repo.Add(item);
}
else if (subStr2 == "RP")
{
item.Category = "RP";
item.OperationType = "RP" + item.OperationType.Trim();
repo.Add(item);
}
else if (subStr1 == "FMD")
{
item.Category = "FMD";
item.OperationType = "FMD" + item.OperationType.Trim();
repo.Add(item);
}
}
else
{
//if (temp.Any(re => assetIds.Contains(re.Code.Trim())))
if (assetIds.Contains(item.Code.Trim()))
{
item.Category = "CBB";
item.OperationType = "CBB" + item.OperationType.Trim();
repo.Add(item);
}
}
}
return(repo);
}
/// <summary>
/// Get bond issue rate data
/// </summary>
/// <param name="bondIssueParams"></param>
/// <returns></returns>
public IEnumerable <BondIssueRate> GetBondIssueRatesRepo(BondIssueParams bondIssueParams)
{
var term = bondIssueParams.Term;
Func <BondIssueRate, bool> termCondition;
switch (term)
{
case "6M": termCondition = t => t.term > 170 && t.term < 190; break;
case "9M": termCondition = t => t.term > 260 && t.term < 280; break;
case "1Y": termCondition = t => t.term == 1; break;
case "2Y": termCondition = t => t.term == 2; break;
case "3Y": termCondition = t => t.term == 3; break;
case "5Y": termCondition = t => t.term == 5; break;
case "7Y": termCondition = t => t.term == 7; break;
case "10Y": termCondition = t => t.term == 10; break;
case "15Y": termCondition = t => t.term == 15; break;
case "20Y": termCondition = t => t.term == 20; break;
case "30Y": termCondition = t => t.term == 30; break;
default: termCondition = t => t.term > 80 && t.term < 100; break; //3M
}
Func <BondIssueRate, bool> ratingCondition = r => r.rating_number == bondIssueParams.Rating;
IEnumerable <BondIssueRate> repo;
using (var vavdb = new BondDBEntities())
{
repo = (from r in vavdb.BOND
where (r.orig_issue_dt >= bondIssueParams.StartDate) &&
(r.cdc_asset_class_cd == bondIssueParams.BondType) &&
(r.isfloat == bondIssueParams.IsFloat) &&
(r.re_issue != "1")
select new BondIssueRate
{
bond_name_cn = r.bond_name_cn,
bond_name_en = r.bond_name_en,
code = r.code,
orig_issue_dt = r.orig_issue_dt,
maturity_dt = r.maturity_dt,
term = r.term,
yield = r.yield,
orig_iss_amt = r.orig_iss_amt,
coupon_type_cn = r.coupon_class_cn,
coupon_type_en = r.coupon_class_en,
latest_rating_cd = r.latest_rating_cd,
cdc_asset_class_number = r.cdc_asset_class_number,
cdc_asset_class_cn = r.cdc_asset_class_cn,
cdc_asset_class_en = r.cdc_asset_class_en,
isfloat = r.isfloat,
rating_number = r.rating_number,
assetId = r.assetId,
re_issue = r.re_issue
}).ToList();
}
IEnumerable <BondIssueRate> result = repo;
if (bondIssueParams.Rating != null && bondIssueParams.Rating != "All")
{
result = repo.Where(ratingCondition);
}
return(result.Where(termCondition));
}
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));
}
}
