public List<RenewalPolicyDetailed> GetRenewalPolicies(bool bypassCache) { var policies = CacheUtil.GetCache(Constants.ConsoleCacheKey) .Get(Constants.RenewalCacheKey) as List<RenewalPolicyDetailed>; if (bypassCache || policies == null) { policies = new List<RenewalPolicyDetailed>(); using (var conn = new SqlConnection(Properties.Settings.Default.SubscribeSQL)) { using (var cmd = new SqlCommand("rpt_RnwlMonitor", conn)) { cmd.CommandTimeout = 240; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@ORIGOFF", SqlDbType.VarChar, 10).Value = "ALL"; cmd.Parameters.Add("@COB", SqlDbType.VarChar, 10).Value = "ALL"; cmd.Parameters.Add("@IsBound", SqlDbType.Bit).Value = 1; cmd.Parameters.Add("@ExclDECLAR", SqlDbType.Bit).Value = 1; cmd.Parameters.Add("@ExclNR", SqlDbType.Bit).Value = 1; cmd.Parameters.Add("@ExclRenBOUND", SqlDbType.Bit).Value = 1; cmd.Parameters.Add("@ExclRenNTU", SqlDbType.Bit).Value = 1; cmd.Parameters.Add("@ExclRenQuote", SqlDbType.Bit).Value = 1; cmd.Parameters.Add("@Bkr", SqlDbType.VarChar, 4).Value = ""; cmd.Parameters.Add("@UserID", SqlDbType.VarChar, 255).Value = _currentHttpContext.CurrentUser.Identity.Name; cmd.Parameters.Add("@FrDt", SqlDbType.DateTime).Value = DateTime.Today.AddDays(-10); cmd.Parameters.Add("@ToDt", SqlDbType.DateTime).Value = DateTime.Today.AddDays(100); conn.Open(); using (var r = cmd.ExecuteReader()) { while (r.Read()) { var detail = new RenewalPolicyDetailed(); Utility.SetObjectPropertyValue(detail, "COB", r, "COB"); Utility.SetObjectPropertyValue(detail, "OriginatingOffice", r, "ORIGOFF"); Utility.SetObjectPropertyValue(detail, "Underwriter", r, "UWR"); Utility.SetObjectPropertyValue(detail, "Leader", r, "Leader"); Utility.SetObjectPropertyValue(detail, "Broker", r, "Broker"); Utility.SetObjectPropertyValue(detail, "BrokerContact", r, "Contact"); Utility.SetObjectPropertyValue(detail, "PolicyId", r, "Expiring Ref"); Utility.SetObjectPropertyValue(detail, "UMR", r, "UMR"); Utility.SetObjectPropertyValue(detail, "InsuredName", r, "InsdNm"); Utility.SetObjectPropertyValue(detail, "Description", r, "Description"); Utility.SetObjectPropertyValue(detail, "InceptionDate", r, "Inception"); Utility.SetObjectPropertyValue(detail, "ExpiryDate", r, "Expiry"); Utility.SetObjectPropertyValue(detail, "St", r, "St"); Utility.SetObjectPropertyValue(detail, "Line", r, "Line"); Utility.SetObjectPropertyValue(detail, "Currency", r, "Ccy"); Utility.SetObjectPropertyValue(detail, "MarketGrossPremium", r, "MktGrPm"); Utility.SetObjectPropertyValue(detail, "SyndicateGrossPremium", r, "SynGrPm"); Utility.SetObjectPropertyValue(detail, "SyndicateNetPremium", r, "SynNetPm"); Utility.SetObjectPropertyValue(detail, "SignedPremium", r, "Signed Pm"); Utility.SetObjectPropertyValue(detail, "PercentageOfEPI", r, "% of EPI"); Utility.SetObjectPropertyValue(detail, "HasClaims", r, "Clm"); Utility.SetObjectPropertyValue(detail, "RenewalPosition", r, "Non-renewable"); Utility.SetObjectPropertyValue(detail, "RenewalNotes", r, "Renewal Note"); Utility.SetObjectPropertyValue(detail, "ToBroker", r, "ToBroker"); Utility.SetObjectPropertyValue(detail, "ToBrokerContact", r, "ToContact"); Utility.SetObjectPropertyValue(detail, "ToPolicyId", r, "ToPolId"); Utility.SetObjectPropertyValue(detail, "ToStatus", r, "ToSt"); Utility.SetObjectPropertyValue(detail, "ToSubmissionStatus", r, "ToSubmSt"); Utility.SetObjectPropertyValue(detail, "ToEntryStatus", r, "ToEntSt"); policies.Add(detail); } } } } try { CacheUtil.GetCache("Console") .Put(Constants.RenewalCacheKey, policies, DateTime.Today.AddDays(1).Date - DateTime.Now); } catch (DataCacheException ex) { this._logHandler.WriteLog(ex.ToString(),LogSeverity.Error, LogCategory.BusinessComponent); } } return policies; }
private static List<RenewalPolicyDetailed> GetRenewalPolicyDetailPolicies() { var policies = new List<RenewalPolicyDetailed>(); using (var conn = new SqlConnection("Data Source=ukroomdev01;Initial Catalog=Subscribe;Integrated Security=True")) { using (var cmd = new SqlCommand("rpt_RnwlMonitor", conn)) { cmd.CommandTimeout = 240; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@ORIGOFF", SqlDbType.VarChar, 10).Value = "ALL"; cmd.Parameters.Add("@COB", SqlDbType.VarChar, 10).Value = "ALL"; cmd.Parameters.Add("@IsBound", SqlDbType.Bit).Value = 1; cmd.Parameters.Add("@ExclDECLAR", SqlDbType.Bit).Value = 1; cmd.Parameters.Add("@ExclNR", SqlDbType.Bit).Value = 1; cmd.Parameters.Add("@ExclRenBOUND", SqlDbType.Bit).Value = 1; cmd.Parameters.Add("@ExclRenNTU", SqlDbType.Bit).Value = 1; cmd.Parameters.Add("@ExclRenQuote", SqlDbType.Bit).Value = 1; cmd.Parameters.Add("@Bkr", SqlDbType.VarChar, 4).Value = ""; cmd.Parameters.Add("@UserID", SqlDbType.VarChar, 255).Value = ""; //_currentHttpContext.CurrentUser.Identity.Name; cmd.Parameters.Add("@FrDt", SqlDbType.DateTime).Value = DateTime.Today.AddDays(-10); cmd.Parameters.Add("@ToDt", SqlDbType.DateTime).Value = DateTime.Today.AddDays(100); conn.Open(); using (var r = cmd.ExecuteReader()) { while (r.Read()) { var detail = new RenewalPolicyDetailed(); Utility.SetObjectPropertyValue(detail, "COB", r, "COB"); Utility.SetObjectPropertyValue(detail, "OriginatingOffice", r, "ORIGOFF"); Utility.SetObjectPropertyValue(detail, "Underwriter", r, "UWR"); Utility.SetObjectPropertyValue(detail, "Leader", r, "Leader"); Utility.SetObjectPropertyValue(detail, "Broker", r, "Broker"); Utility.SetObjectPropertyValue(detail, "BrokerContact", r, "Contact"); Utility.SetObjectPropertyValue(detail, "PolicyId", r, "Expiring Ref"); Utility.SetObjectPropertyValue(detail, "UMR", r, "UMR"); Utility.SetObjectPropertyValue(detail, "InsuredName", r, "InsdNm"); Utility.SetObjectPropertyValue(detail, "Description", r, "Description"); Utility.SetObjectPropertyValue(detail, "InceptionDate", r, "Inception"); Utility.SetObjectPropertyValue(detail, "ExpiryDate", r, "Expiry"); Utility.SetObjectPropertyValue(detail, "St", r, "St"); Utility.SetObjectPropertyValue(detail, "Line", r, "Line"); Utility.SetObjectPropertyValue(detail, "Currency", r, "Ccy"); Utility.SetObjectPropertyValue(detail, "MarketGrossPremium", r, "MktGrPm"); Utility.SetObjectPropertyValue(detail, "SyndicateGrossPremium", r, "SynGrPm"); Utility.SetObjectPropertyValue(detail, "SyndicateNetPremium", r, "SynNetPm"); Utility.SetObjectPropertyValue(detail, "SignedPremium", r, "Signed Pm"); Utility.SetObjectPropertyValue(detail, "PercentageOfEPI", r, "% of EPI"); Utility.SetObjectPropertyValue(detail, "HasClaims", r, "Clm"); Utility.SetObjectPropertyValue(detail, "RenewalPosition", r, "Non-renewable"); Utility.SetObjectPropertyValue(detail, "RenewalNotes", r, "Renewal Note"); Utility.SetObjectPropertyValue(detail, "ToBroker", r, "ToBroker"); Utility.SetObjectPropertyValue(detail, "ToBrokerContact", r, "ToContact"); Utility.SetObjectPropertyValue(detail, "ToPolicyId", r, "ToPolId"); Utility.SetObjectPropertyValue(detail, "ToStatus", r, "ToSt"); Utility.SetObjectPropertyValue(detail, "ToSubmissionStatus", r, "ToSubmSt"); Utility.SetObjectPropertyValue(detail, "ToEntryStatus", r, "ToEntSt"); policies.Add(detail); } } } } return policies; }