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)); } }