public static void ExecuteQuery(string query) { using (var sipSorceryEntities = new SIPSorceryEntities()) { sipSorceryEntities.ExecuteStoreCommand(query, null); } }
public List<SIPDialplanLookup> GetLookups(string dialplanName) { try { using (var ssEntities = new SIPSorceryEntities()) { if (!dialplanName.IsNullOrBlank()) { return (from lookup in ssEntities.SIPDialplanLookups join dialplan in ssEntities.SIPDialPlans on lookup.DialPlanID equals dialplan.ID where lookup.Owner == m_owner && dialplan.DialPlanName.Contains(dialplanName) select lookup).ToList(); } else { return (from lookup in ssEntities.SIPDialplanLookups where lookup.Owner == m_owner select lookup).ToList(); } } } catch (Exception excp) { logger.Error("Exception GetLookups. " + excp.Message); return null; } }
public static void SIPProviderDeleted(SIPProvider sipProvider) { try { logger.Debug("SIPProviderBindingSynchroniser SIPProviderDeleted for " + sipProvider.Owner + " and " + sipProvider.ProviderName + "."); using (SIPSorceryEntities sipSorceryEntities = new SIPSorceryEntities()) { SIPProviderBinding existingBinding = (from binding in sipSorceryEntities.SIPProviderBindings where binding.ProviderID == sipProvider.ID select binding).FirstOrDefault(); if (existingBinding != null) { if (existingBinding.IsRegistered) { // Let the registration agent know the existing binding should be expired. existingBinding.BindingExpiry = 0; existingBinding.NextRegistrationTime = DateTime.UtcNow.ToString("o"); sipSorceryEntities.SaveChanges(); } else { sipSorceryEntities.SIPProviderBindings.DeleteObject(existingBinding); sipSorceryEntities.SaveChanges(); } } } } catch (Exception excp) { logger.Error("Exception SIPProviderBindingSynchroniser SIPProviderDeleted. " + excp.Message); } }
/// <summary> /// Adds a new CDR. /// </summary> /// <param name="rate">The rate record to add.</param> public void Add(SIPCDR cdr) { using (var sipSorceryEntities = new SIPSorceryEntities()) { var entityCDR = new CDR() { ID = cdr.CDRId.ToString(), Inserted = DateTimeOffset.UtcNow.ToString("o"), Created = cdr.Created.ToString("o"), Direction = cdr.CallDirection.ToString(), DstURI = cdr.Destination.ToString(), Dst = cdr.Destination.User, DstHost = cdr.Destination.Host, FromHeader = cdr.From.ToString(), FromName = cdr.From.FromName, FromUser = cdr.From.FromURI.User, CallID = cdr.CallId, Owner = cdr.Owner, AdminMemberID = cdr.AdminMemberId, RemoteSocket = (cdr.RemoteEndPoint != null) ? cdr.RemoteEndPoint.ToString() : null, LocalSocket = (cdr.LocalSIPEndPoint != null) ? cdr.LocalSIPEndPoint.ToString() : null }; sipSorceryEntities.CDRs.AddObject(entityCDR); sipSorceryEntities.SaveChanges(); } }
public static void SIPProviderAdded(SIPProvider sipProvider) { try { logger.Debug("SIPProviderBindingSynchroniser SIPProviderAdded for " + sipProvider.Owner + " and " + sipProvider.ProviderName + " (Provider ID=" + sipProvider.ID + ")."); if (sipProvider.RegisterEnabled) { using (SIPSorceryEntities sipSorceryEntities = new SIPSorceryEntities()) { SIPProvider existingProvider = (from provider in sipSorceryEntities.SIPProviders where provider.ID == sipProvider.ID select provider).FirstOrDefault(); if (existingProvider != null) { AddNewBindingForProvider(sipSorceryEntities, existingProvider); } else { logger.Warn("The SIP provider entry was not in the database when attempting to add a provider binding."); } } } } catch (Exception excp) { logger.Error("Exception SIPProviderBindingSynchroniser SIPProviderAdded. " + excp.Message); } }
/// <summary> /// Updates an existing customer account. /// </summary> /// <param name="customerAccount">The customer account to update.</param> public void Update(CustomerAccount customerAccount) { using (var db = new SIPSorceryEntities()) { var existingAccount = (from ca in db.CustomerAccounts where ca.ID == customerAccount.ID select ca).SingleOrDefault(); if (existingAccount == null) { throw new ApplicationException("The customer account to update could not be found"); } else if (existingAccount.Owner.ToLower() != customerAccount.Owner.ToLower()) { throw new ApplicationException("You are not authorised to update this customer account."); } else { CheckUniqueFields(customerAccount); logger.Debug("Updating customer account " + existingAccount.AccountName + " for " + existingAccount.Owner + "."); existingAccount.AccountName = customerAccount.AccountName; existingAccount.AccountNumber = customerAccount.AccountNumber; existingAccount.Credit = customerAccount.Credit; existingAccount.PIN = customerAccount.PIN; existingAccount.RatePlan = customerAccount.RatePlan; db.SaveChanges(); } } }
/// <summary> /// Deletes all the rates for a specific owner account. /// </summary> /// <param name="owner">The owner to delete all the rates for.</param> public void DeleteAll(string owner) { using (var sipSorceryEntities = new SIPSorceryEntities()) { sipSorceryEntities.ExecuteStoreCommand("delete from rate where owner = @p0", owner); } }
public void UpdateRealTimeCallControlCDRID(string oldCDRID, SIPCDR newCDR) { logger.Debug("UpdateRealTimeCallControlCDRID old CDR ID " + oldCDRID + ", new CDR ID " + newCDR.CDRId.ToString() + "."); using (var db = new SIPSorceryEntities()) { using (var trans = new TransactionScope()) { var realTimeCallControl = (from rtcc in db.RTCCs1 where rtcc.CDRID == oldCDRID select rtcc).FirstOrDefault(); if (realTimeCallControl == null) { logger.Error("No RTCC record could be found for CDR ID " + oldCDRID + "."); } else { //db.CDRs.AddObject(newCDR); realTimeCallControl.CDRID = newCDR.CDRId.ToString(); db.SaveChanges(); trans.Complete(); } } } }
/// <summary> /// Checks whether the account code is already in use for the system. /// </summary> /// <param name="accountCode">The account code to check.</param> /// <param name="updatingID">An optional parameter that can be supplied when the check is being made for an account /// that is being updated and in which case this ID is for the record being updated.</param> /// <returns>True if the account code is in use otherwise false.</returns> public bool IsAccountCodeInUse(string accountCode, string updatingID) { using (var db = new SIPSorceryEntities()) { return((from ca in db.CustomerAccounts where ca.AccountCode.ToLower() == accountCode.ToLower() && (updatingID == null || ca.ID != updatingID) select ca).Any()); } }
/// <summary> /// Deletes an existing rate. /// </summary> /// <param name="id">The ID of the rate record to delete.</param> public void Delete(string id) { using (var sipSorceryEntities = new SIPSorceryEntities()) { var rate = (from rt in sipSorceryEntities.Rates where rt.ID == id select rt).SingleOrDefault(); if (rate != null) { sipSorceryEntities.Rates.DeleteObject(rate); sipSorceryEntities.SaveChanges(); } } }
/// <summary> /// Checks the account code is valid and if not will also check the account number. /// </summary> /// <param name="owner">The owner of the customer accounts to check.</param> /// <param name="accountNumber">The account code to check.</param> /// <returns>If a matching accountcode or number is found the account code will be returned otherwise null.</returns> public CustomerAccount CheckAccountCode(string owner, string accountCode) { using (var db = new SIPSorceryEntities()) { var account = (from ca in db.CustomerAccounts where ca.Owner == owner.ToLower() && (ca.AccountCode.ToLower() == accountCode.ToLower() || ca.AccountNumber == accountCode) select ca).SingleOrDefault(); return((account != null) ? account : null); } }
/// <summary> /// Retrieves the customer record that matches the specified ID. /// </summary> /// <param name="id">The unique ID of the cutomer to try and retrieve.</param> /// <returns>If found the customer record otherwise null.</returns> public Customer Get(string id) { if (id.IsNullOrBlank()) { return(null); } using (var db = new SIPSorceryEntities()) { return((from cu in db.Customers where cu.ID == id select cu).SingleOrDefault()); } }
/// <summary> /// Adds a new customer account. /// </summary> /// <param name="customerAccount">The customer account to add.</param> public void Add(CustomerAccount customerAccount) { using (var sipSorceryEntities = new SIPSorceryEntities()) { customerAccount.AccountCode = Crypto.GetRandomString(ACCOUNT_CODE_RANDOM_STRING_PREFIX_LENGTH) + Crypto.GetRandomInt(ACCOUNT_CODE_RANDOM_NUMBER_SUFFIX_LENGTH).ToString(); CheckUniqueFields(customerAccount); customerAccount.ID = Guid.NewGuid().ToString(); customerAccount.Inserted = DateTimeOffset.UtcNow.ToString("o"); sipSorceryEntities.CustomerAccounts.AddObject(customerAccount); sipSorceryEntities.SaveChanges(); } }
/// <summary> /// Retrieves the customer record that matches the specified email address. /// </summary> /// <param name="emailAddress">The email address of the customer to try and retrieve.</param> /// <returns>If found the customer record otherwise null.</returns> public Customer GetForEmail(string emailAddress) { if (emailAddress.IsNullOrBlank()) { return(null); } using (var db = new SIPSorceryEntities()) { string lowerName = emailAddress.Trim().ToLower(); return((from cu in db.Customers where cu.EmailAddress.ToLower() == emailAddress select cu).SingleOrDefault()); } }
public CustomerAccount Get(string owner, string accountNumber) { if (accountNumber.IsNullOrBlank()) { return(null); } using (var db = new SIPSorceryEntities()) { return((from ca in db.CustomerAccounts where ca.Owner == owner.ToLower() && ca.AccountNumber.ToLower() == accountNumber.ToLower() select ca).SingleOrDefault()); } }
/// <summary> /// Retrieves the customer record that matches the specified name (name in this the username). /// </summary> /// <param name="name">The name of the customer to try and retrieve.</param> /// <returns>If found the customer record otherwise null.</returns> public Customer GetForName(string name) { if (name.IsNullOrBlank()) { return(null); } using (var db = new SIPSorceryEntities()) { string lowerName = name.Trim().ToLower(); return((from cu in db.Customers where cu.Name.ToLower() == lowerName select cu).SingleOrDefault()); } }
public decimal GetBalance(string accountCode) { using (var db = new SIPSorceryEntities()) { var customerAccount = db.CustomerAccounts.Where(x => x.AccountCode == accountCode).SingleOrDefault(); if (customerAccount == null) { return(-1); } else { return(customerAccount.Credit); } } }
public Rate Get(string owner, string id) { if (id.IsNullOrBlank()) { return(null); } using (var db = new SIPSorceryEntities()) { return((from ra in db.Rates where ra.Owner == owner.ToLower() && ra.ID.ToLower() == id.ToLower() select ra).SingleOrDefault()); } }
/// <summary> /// Checks whether the account number is already in use for this owner. /// </summary> /// <param name="owner">The owner of the customer accounts to check.</param> /// <param name="accountNumber">The account number to check.</param> /// <param name="updatingID">An optional parameter that can be supplied when the check is being made for an account /// that is being updated and in which case this ID is for the record being updated.</param> /// <returns>True if the account number is in use otherwise false.</returns> public bool IsAccountNumberInUse(string owner, string accountNumber, string updatingID) { if (accountNumber.IsNullOrBlank()) { return(false); } using (var db = new SIPSorceryEntities()) { return((from ca in db.CustomerAccounts where ca.Owner == owner.ToLower() && ca.AccountNumber.ToLower() == accountNumber.ToLower() && (updatingID == null || ca.ID != updatingID) select ca).Any()); } }
/// <summary> /// Attempts to retrieve a customer record based on the FTP prefix. /// </summary> /// <param name="ftpPrefix">The FTP prefix to retrieve the customer for.</param> /// <returns>If found the matching Customer record otherwise null.</returns> public Customer GetForFTPPrefix(string ftpPrefix) { if (ftpPrefix.IsNullOrBlank()) { return null; } using (var db = new SIPSorceryEntities()) { string ftpPrefixLower = ftpPrefix.ToLower(); return (from cu in db.Customers where cu.FTPPrefix.ToLower() == ftpPrefixLower select cu).SingleOrDefault(); } }
/// <summary> /// Attempts to retrieve a customer record based on the FTP prefix. /// </summary> /// <param name="ftpPrefix">The FTP prefix to retrieve the customer for.</param> /// <returns>If found the matching Customer record otherwise null.</returns> public Customer GetForFTPPrefix(string ftpPrefix) { if (ftpPrefix.IsNullOrBlank()) { return(null); } using (var db = new SIPSorceryEntities()) { string ftpPrefixLower = ftpPrefix.ToLower(); return((from cu in db.Customers where cu.FTPPrefix.ToLower() == ftpPrefixLower select cu).SingleOrDefault()); } }
public void SetCDRIsHangingUp(string rtccID) { using (var db = new SIPSorceryEntities()) { //var callCDR = (from cdr in db.CDRs where cdr.ID == cdrID select cdr).SingleOrDefault(); var callRTCC = (from rtcc in db.RTCCs1 where rtcc.ID == rtccID select rtcc).SingleOrDefault(); if (callRTCC == null) { logger.Debug("RTCC record could not be found for " + rtccID + " when attemping to set the IsHangingUp flag."); } else { callRTCC.IsHangingUp = true; db.SaveChanges(); } } }
/// <summary> /// Adds a new rate. /// </summary> /// <param name="rate">The rate record to add.</param> public void Add(Rate rate) { using (var sipSorceryEntities = new SIPSorceryEntities()) { if ((from rt in sipSorceryEntities.Rates where rt.Prefix == rate.Prefix && rt.Owner == rate.Owner select rt).Any()) { throw new ApplicationException("The rate prefix is already in use."); } else { rate.ID = Guid.NewGuid().ToString(); rate.Inserted = DateTimeOffset.UtcNow.ToString("o"); sipSorceryEntities.Rates.AddObject(rate); sipSorceryEntities.SaveChanges(); } } }
/// <summary> /// Adds a new rate. /// </summary> /// <param name="rate">The rate record to add.</param> public void Add(Rate rate) { using (var sipSorceryEntities = new SIPSorceryEntities()) { if ((from rt in sipSorceryEntities.Rates where rt.Prefix == rate.Prefix select rt).Any()) { throw new ApplicationException("The rate prefix is already in use."); } else { rate.ID = Guid.NewGuid().ToString(); rate.Inserted = DateTimeOffset.UtcNow.ToString("o"); sipSorceryEntities.Rates.AddObject(rate); sipSorceryEntities.SaveChanges(); } } }
/// <summary> /// Updates an existing CDR. /// </summary> /// <param name="cdr">The CDR to update.</param> public void Update(SIPCDR cdr) { using (var db = new SIPSorceryEntities()) { string cdrID = cdr.CDRId.ToString(); var existingCDR = (from cd in db.CDRs where cd.ID == cdrID select cd).SingleOrDefault(); if (existingCDR == null) { throw new ApplicationException("The CDR to update could not be found"); } else { // Fields that are not permitted to be updated. // ID // Inserted // Direction // Created // Destination // From // Call-ID existingCDR.Owner = cdr.Owner; existingCDR.AdminMemberID = cdr.AdminMemberId; existingCDR.BridgeID = (cdr.BridgeId != Guid.Empty) ? cdr.BridgeId.ToString() : null; existingCDR.InProgressTime = (cdr.ProgressTime != null) ? cdr.ProgressTime.Value.ToString("o") : null; existingCDR.InProgressStatus = cdr.ProgressStatus; existingCDR.InProgressReason = cdr.ProgressReasonPhrase; existingCDR.RingDuration = (cdr.ProgressTime != null && cdr.AnswerTime != null) ? Convert.ToInt32(cdr.AnswerTime.Value.Subtract(cdr.ProgressTime.Value).TotalSeconds) : 0; existingCDR.AnsweredTime = (cdr.AnswerTime != null) ? cdr.AnswerTime.Value.ToString("o") : null; existingCDR.AnsweredStatus = cdr.AnswerStatus; existingCDR.AnsweredReason = cdr.AnswerReasonPhrase; existingCDR.Duration = (cdr.AnswerTime != null && cdr.HangupTime != null) ? Convert.ToInt32(cdr.HangupTime.Value.Subtract(cdr.AnswerTime.Value).TotalSeconds) : 0;; existingCDR.HungupTime = (cdr.HangupTime != null) ? cdr.HangupTime.Value.ToString("o") : null; existingCDR.HungupReason = cdr.HangupReason; existingCDR.AnsweredAt = cdr.AnsweredAt; existingCDR.DialPlanContextID = (cdr.DialPlanContextID != Guid.Empty) ? cdr.DialPlanContextID.ToString() : null; existingCDR.RemoteSocket = (cdr.RemoteEndPoint != null) ? cdr.RemoteEndPoint.ToString() : null; existingCDR.LocalSocket = (cdr.LocalSIPEndPoint != null) ? cdr.LocalSIPEndPoint.ToString() : null; db.SaveChanges(); } } }
public void ChangeSIPDialPlanName(string authUser, string sipDialPlanID, string name) { if (authUser.IsNullOrBlank()) { throw new ArgumentException("An authenticated user is required for ChangeSIPDialPlanName."); } else if (name.IsNullOrBlank()) { throw new ArgumentNullException("The new name cannot be empty in ChangeSIPDialPlanName."); } using (var sipSorceryEntities = new SIPSorceryEntities()) { SIPDialPlan existingAccount = (from dp in sipSorceryEntities.SIPDialPlans where dp.ID == sipDialPlanID && dp.Owner.ToLower() == authUser.ToLower() select dp).FirstOrDefault(); if (existingAccount == null) { throw new ApplicationException("The SIP Dial Plan to change the name for could not be found."); } else if (existingAccount.Owner != authUser.ToLower()) { logger.Warn("User " + authUser + " was not authorised to change dial plan " + existingAccount.DialPlanName + " belonging to " + existingAccount.Owner + "."); throw new ApplicationException("Not authorised to change the SIP Dial Plan name."); } else if (existingAccount.IsReadOnly) { throw new ApplicationException("This Dial Plan is read-only. Please upgrade to a Premium service to enable it."); } logger.Debug("Changing the SIP dialplan " + existingAccount.DialPlanName + " for " + existingAccount.Owner + " to " + name + "."); if (sipSorceryEntities.SIPDialPlans.Any(x => x.DialPlanName.ToLower() == name.ToLower() && x.Owner.ToLower() == authUser.ToLower())) { throw new ApplicationException("There is already a dialplan with the same name. Please choose something different."); } // Need to update any SIP accounts that are using the old dialplan name. UpdateSIPAccountsDialPlanName(sipSorceryEntities, authUser, existingAccount.DialPlanName, name); existingAccount.DialPlanName = name; sipSorceryEntities.SaveChanges(); } }
private static void AddNewBindingForProvider(SIPSorceryEntities sipSorceryEntities, SIPProvider sipProvider) { try { logger.Debug("AddNewBindingForProvider provider ID=" + sipProvider.ID + "."); SIPProviderBinding newBinding = sipSorceryEntities.SIPProviderBindings.CreateObject(); newBinding.SetProviderFields(sipProvider); newBinding.ID = Guid.NewGuid().ToString(); newBinding.NextRegistrationTime = DateTimeOffset.UtcNow.ToString("o"); newBinding.ProviderID = sipProvider.ID; newBinding.Owner = sipProvider.Owner; sipSorceryEntities.SIPProviderBindings.AddObject(newBinding); sipSorceryEntities.SaveChanges(); } catch (Exception excp) { logger.Error("Exception AddNewBindingForProvider. " + excp.Message); logger.Error(excp); } }
/// <summary> /// Updates an existing rate. /// </summary> /// <param name="rate">The rate to update.</param> public void Update(Rate rate) { using (var db = new SIPSorceryEntities()) { var existingRate = (from ra in db.Rates where ra.ID == rate.ID select ra).SingleOrDefault(); if (existingRate == null) { throw new ApplicationException("The rate to update could not be found"); } else if (existingRate.Owner.ToLower() != rate.Owner.ToLower()) { throw new ApplicationException("You are not authorised to update this rate."); } else { if (existingRate.Prefix != rate.Prefix) { if ((from rt in db.Rates where rt.Prefix == rate.Prefix select rt).Any()) { throw new ApplicationException("The rate prefix is already in use."); } } logger.Debug("Updating rate " + existingRate.Description + " for " + existingRate.Owner + "."); existingRate.Description = rate.Description; existingRate.Prefix = rate.Prefix; existingRate.Rate1 = rate.Rate1; existingRate.RateCode = rate.RateCode; existingRate.SetupCost = rate.SetupCost; existingRate.IncrementSeconds = rate.IncrementSeconds; existingRate.RatePlan = rate.RatePlan; db.SaveChanges(); } } }
/// <summary> /// This method attempts to reserve a the initial amount of credit for a call. /// </summary> /// <param name="accountCode">The accountCode the credit should be reserved against.</param> /// <param name="amount">The amount of credit to reserve.</param> /// <param name="rate">The rate for the call destination and the values that will be used for subsequent credit reservations.</param> /// <param name="initialSeconds">IF the reservation is successful this parameter will hold the number of seconds that were reserved for the initial reservation.</param> /// <returns>True if there was enough credit for the reservation otherwise false.</returns> public decimal ReserveInitialCredit(string accountCode, string rateID, SIPCDR cdr, out int initialSeconds) { try { logger.Debug("ReserveInitialCredit for " + accountCode + " and rate ID " + rateID + "."); initialSeconds = 0; using (var db = new SIPSorceryEntities()) { using (var trans = new TransactionScope()) { var rate = db.Rates.Where(x => x.ID == rateID).SingleOrDefault(); if (accountCode.IsNullOrBlank() || (rate == null || rate.Rate1 <= 0)) { return(Decimal.MinusOne); } logger.Debug("ReserveInitialCredit for " + accountCode + ", rate " + rate.Rate1 + ", setup cost " + rate.SetupCost + ", increment seconds " + rate.IncrementSeconds + "."); var customerAccount = db.CustomerAccounts.Where(x => x.AccountCode == accountCode).SingleOrDefault(); if (customerAccount == null) { logger.Debug("The initial reservation for " + accountCode + " failed due to the no matching accountcode."); return(Decimal.MinusOne); } // Get the owning customer's RTCC billing increment. //int rtccIncrement = (from cust in db.Customers where cust.Name.ToLower() == customerAccount.Owner.ToLower() select cust.RTCCBillingIncrement).Single(); initialSeconds = (rate.IncrementSeconds > MINIMUM_INITIAL_RESERVATION_SECONDS) ? rate.IncrementSeconds : MINIMUM_INITIAL_RESERVATION_SECONDS; decimal reservationCost = ((decimal)initialSeconds / (decimal)60 * rate.Rate1) + rate.SetupCost; if (customerAccount.Credit < reservationCost) { logger.Debug("The initial reservation for " + accountCode + ", duration " + initialSeconds + "s and " + reservationCost.ToString("0.#####") + " failed due to lack of credit."); return(Decimal.MinusOne); } else { var rtccRecord = new RTCC() { ID = Guid.NewGuid().ToString(), CDRID = cdr.CDRId.ToString(), AccountCode = accountCode, SecondsReserved = initialSeconds, Cost = reservationCost, Rate = rate.Rate1, SetupCost = rate.SetupCost, IncrementSeconds = rate.IncrementSeconds, Inserted = DateTime.UtcNow }; db.RTCCs1.AddObject(rtccRecord); //var callCDR = (from cdr in db.CDRs where cdr.ID == cdrID select cdr).SingleOrDefault(); //if (callCDR == null) //{ // logger.Debug("The initial reservation for " + accountCode + " and " + reservationCost.ToString("0.#####") + " failed due to the no matching CDR for " + cdrID + "."); // return false; //} //else if (callCDR.HungupTime != null) //{ // logger.Debug("The initial reservation for " + accountCode + " and " + reservationCost.ToString("0.#####") + " failed due to the CDR already being hungup."); // return false; //} // The credit is available deduct it from the customer account balance and place it on the CDR. customerAccount.Credit = customerAccount.Credit - reservationCost; // Set the fields on the CDR. //callCDR.Rate = rate; //callCDR.SecondsReserved = seconds; //callCDR.AccountCode = accountCode; //callCDR.Cost = reservationCost; //db.CDRs.AddObject(cdr); db.SaveChanges(); trans.Complete(); logger.Debug("The initial reservation for " + accountCode + ", duration " + initialSeconds + "s and " + reservationCost.ToString("0.#####") + " was successful."); return(reservationCost); } } } } catch (Exception excp) { logger.Error("Exception ReserveInitialCredit. " + excp); throw; } }
public CustomerAccount Get(string owner, string accountNumber) { if (accountNumber.IsNullOrBlank()) { return null; } using (var db = new SIPSorceryEntities()) { return (from ca in db.CustomerAccounts where ca.Owner == owner.ToLower() && ca.AccountNumber.ToLower() == accountNumber.ToLower() select ca).SingleOrDefault(); } }
/// <summary> /// This method acts as a call rate lookup engine. It's very basic and simply matches the call destination /// based on the record that has the longest matching prefix. /// </summary> /// <param name="owner">The owner the call rate lookup is for.</param> /// <param name="rateCode">The rate code for the call. If specified and a matching rate is found it will /// take precedence over the destination.</param> /// <param name="destination">The call desintation the rate lookup is for.</param> /// <returns>If a matching rate is found a greater than 0 decimal value otherwise 0.</returns> public Rate GetRate(string owner, string rateCode, string destination, int ratePlan) { logger.Debug("GetRate for owner " + owner + ", ratecode " + rateCode + " and destination " + destination + "."); if (owner.IsNullOrBlank() || destination.IsNullOrBlank()) { return(null); } using (var db = new SIPSorceryEntities()) { Rate callRate = null; if (rateCode.NotNullOrBlank()) { callRate = (from rate in db.Rates where rate.Owner.ToLower() == owner.ToLower() && rate.RateCode == rateCode && rate.RatePlan == ratePlan select rate).SingleOrDefault(); } if (callRate == null) { callRate = (from rate in db.Rates where rate.Owner.ToLower() == owner.ToLower() && destination.StartsWith(rate.Prefix) && rate.RatePlan == ratePlan orderby rate.Prefix.Length descending select rate).FirstOrDefault(); } // If the rate is still null check for international prefixes. if (callRate == null) { //var customerAccount = db.CustomerAccounts.Where(x => x.AccountCode == accountCode).SingleOrDefault(); //if (customerAccount == null) //{ // logger.Debug("The rate lookup for " + accountCode + " failed due to the no matching accountcode."); // return null; //} string rtccInternationalPrefixes = (from cust in db.Customers where cust.Name.ToLower() == owner.ToLower() select cust.RTCCInternationalPrefixes).Single(); if (rtccInternationalPrefixes.NotNullOrBlank()) { string trimmedDestination = null; string[] prefixes = rtccInternationalPrefixes.Split(','); foreach (string prefix in prefixes.OrderByDescending(x => x.Length)) { if (destination.StartsWith(prefix)) { trimmedDestination = destination.Substring(prefix.Length); logger.Debug("The destination matched international prefix of " + prefix + ", looking up rate for " + trimmedDestination + "."); break; } } if (trimmedDestination != null) { callRate = (from rate in db.Rates where rate.Owner.ToLower() == owner.ToLower() && trimmedDestination.StartsWith(rate.Prefix) && rate.RatePlan == ratePlan orderby rate.Prefix.Length descending select rate).FirstOrDefault(); } } } if (callRate != null) { logger.Debug("Rate found for " + owner + " and " + destination + " was " + callRate.Rate1 + "."); return(callRate); } else { logger.Debug("No rate found for " + owner + " and " + destination + "."); return(null); } } }
/// <summary> /// Monitors the CDRs table for records that are using real-time call control and are within the limit that requires them to /// re-reserve credit. /// </summary> private void MonitorCDRs() { try { Thread.CurrentThread.Name = RTCC_THREAD_NAME; logger.Debug("RTCC Core Starting Monitor CDRs thread."); while (!m_exit) { using (var db = new SIPSorceryEntities()) { try { // Try and reserve credit on in progress calls. DateTime reservationDue = DateTime.Now.AddSeconds(m_reserveDueSeconds); var cdrsReservationDue = (from cdr in db.CDRs where cdr.AccountCode != null && cdr.HungupTime == null && cdr.AnsweredAt != null && cdr.SecondsReserved != null && cdr.SecondsReserved > 0 && cdr.AnsweredStatus >= 200 && cdr.AnsweredStatus <= 299 && EntityFunctions.AddSeconds(cdr.AnsweredAt, cdr.SecondsReserved) <= reservationDue && cdr.ReservationError == null orderby cdr.AnsweredAt select cdr).Take(NUMBER_CDRS_PER_ROUNDTRIP); while (cdrsReservationDue.Count() > 0) { foreach (CDR cdr in cdrsReservationDue) { Log_External(new SIPMonitorConsoleEvent(SIPMonitorServerTypesEnum.RTCC, SIPMonitorEventTypesEnum.DialPlan, "Reserving credit for call " + cdr.Dst + ".", cdr.Owner)); // Attempt to re-reserve the next chunk of credit for the call. m_customerAccountDataLayer.ReserveCredit(m_reservationAmountSeconds, cdr.ID); } } } //catch (ReflectionTypeLoadException ex) //{ // StringBuilder sb = new StringBuilder(); // foreach (Exception exSub in ex.LoaderExceptions) // { // sb.AppendLine(exSub.Message); // if (exSub is FileNotFoundException) // { // FileNotFoundException exFileNotFound = exSub as FileNotFoundException; // if (!string.IsNullOrEmpty(exFileNotFound.FusionLog)) // { // sb.AppendLine("Fusion Log:"); // sb.AppendLine(exFileNotFound.FusionLog); // } // } // sb.AppendLine(); // } // string errorMessage = sb.ToString(); // logger.Error(errorMessage); //} catch (Exception monitorExcp) { logger.Error("Exception MonitorCDRs Credit Reservation. " + monitorExcp); logger.Error("InnerException MonitorCDRs Credit Reservation. " + monitorExcp.InnerException); } try { // Terminate any calls that have reached their time limit. DateTime now = DateTime.Now; var cdrsTerminationDue = (from cdr in db.CDRs where !cdr.IsHangingUp && cdr.AccountCode != null && cdr.HungupTime == null && cdr.AnsweredAt != null && cdr.SecondsReserved != null && cdr.AnsweredStatus >= 200 && cdr.AnsweredStatus <= 299 && EntityFunctions.AddSeconds(cdr.AnsweredAt, cdr.SecondsReserved) <= now && !cdr.IsHangingUp orderby cdr.AnsweredAt select cdr).Take(NUMBER_CDRS_PER_ROUNDTRIP); while (cdrsTerminationDue.Count() > 0) { foreach (CDR cdr in cdrsTerminationDue) { Log_External(new SIPMonitorConsoleEvent(SIPMonitorServerTypesEnum.RTCC, SIPMonitorEventTypesEnum.DialPlan, "Terminating call due to reservation limit being reached " + cdr.Dst + ".", cdr.Owner)); m_customerAccountDataLayer.SetCDRIsHangingUp(cdr.ID); var dialogue = m_sipDialoguePersistor.Get(x => x.CDRId == cdr.ID, null, 0, 1).FirstOrDefault(); if (dialogue != null) { m_sipDialogueManager.CallHungup(dialogue.SIPDialogue, "RTCC time limit reached", true); } else { Log_External(new SIPMonitorConsoleEvent(SIPMonitorServerTypesEnum.RTCC, SIPMonitorEventTypesEnum.Warn, "A dialogue could not be found when terminating a call due to reservation limit being reached.", cdr.Owner)); } } } } catch (Exception monitorExcp) { logger.Error("Exception RTCCCore MonitorCDRs Call Termination. " + monitorExcp.Message); } } Thread.Sleep(1000); } logger.Warn("RTCCCore MonitorCDRs thread stopping."); } catch (Exception excp) { logger.Error("Exception RTCCCore MonitorCDRs. " + excp.Message); } }
/// <summary> /// This method should be called once a billable call has been completed. It will calculate the final cost of the call and return /// any usused credit back to the customer account. /// </summary> /// <param name="cdrID">The ID of the CDR the credit is being returned for.</param> /// <returns>The total cost of the completed call.</returns> public decimal ReturnUnusedCredit(string rtccID) { logger.Debug("ReturnUnusedCredit for RTCC ID " + rtccID + "."); decimal actualCallCost = Decimal.Zero; using (var db = new SIPSorceryEntities()) { using (var trans = new TransactionScope()) { var rtcc = (from rtc in db.RTCCs1.Include("cdr") where rtc.ID == rtccID select rtc).First(); if (rtcc.ReconciliationResult != null) { logger.Error("This CDR has already been reconciled, no further action will be taken."); } else { var callCDR = (from cdr in db.CDRs where cdr.ID == rtcc.CDRID select cdr).SingleOrDefault(); string reconciliationError = null; if (callCDR.Duration == null) { reconciliationError = "Error, the call duration was null."; logger.Warn("The unused credit could not be returned for " + rtcc.ID + " the CDR has not been hungup."); } else if (rtcc.Cost == null) { reconciliationError = "Error, the call cost was null."; logger.Warn("The unused credit could not be returned for " + rtcc.ID + " the call cost was empty."); } else if (rtcc.AccountCode.IsNullOrBlank()) { reconciliationError = "Error, the accountcode was null."; logger.Warn("The unused credit could not be returned for " + rtcc.ID + " due to the CDR having a blank accountcode."); } else if (rtcc.Rate <= 0) { reconciliationError = "Error, the rate was not set."; logger.Warn("The unused credit could not be returned for " + rtcc.ID + " due to the CDR having no rate."); } if (reconciliationError != null) { rtcc.ReconciliationResult = reconciliationError; db.SaveChanges(); } else { string accountCode = rtcc.AccountCode; var customerAccount = db.CustomerAccounts.Where(x => x.AccountCode == accountCode).SingleOrDefault(); logger.Debug("The pre-reconciliation balance for account " + accountCode + " was " + customerAccount.Credit + " when processing RTCC ID " + rtcc.ID + "."); if (customerAccount == null) { logger.Debug("The unused credit could not be returned for RTCC ID " + rtcc.ID + " due to the no matching accountcode."); rtcc.ReconciliationResult = "Error, no matching customer for " + accountCode + "."; db.SaveChanges(); } else { // Get the owning customer's RTCC billing increment. //int rtccIncrement = (from cust in db.Customers where cust.Name.ToLower() == callCDR.Owner.ToLower() select cust.RTCCBillingIncrement).Single(); int rtccIncrement = (rtcc.IncrementSeconds <= 0) ? DEFAULT_INCREMENT_SECONDS : rtcc.IncrementSeconds; int billableDuration = (callCDR.Duration.Value % rtccIncrement == 0) ? callCDR.Duration.Value : (callCDR.Duration.Value / rtccIncrement + 1) * rtccIncrement; if (billableDuration > 0) { actualCallCost = ((Convert.ToDecimal(billableDuration) / SECONDS_FOR_RATE) * rtcc.Rate.Value) + rtcc.SetupCost; } logger.Debug("RTCC billable duration " + billableDuration + " (increment " + rtccIncrement + "), actual call cost calculated at " + actualCallCost.ToString("0.#####") + " for call with cost of " + rtcc.Cost + "."); if (Math.Round(actualCallCost, 5) < Math.Round(rtcc.Cost.Value, 5)) { decimal returnCredit = Math.Round(rtcc.Cost.Value, 5) - Math.Round(actualCallCost, 5); if (returnCredit > 0) { // There is some credit to return to the customer account. rtcc.Cost = rtcc.Cost.Value - returnCredit; rtcc.ReconciliationResult = "ok"; customerAccount.Credit = customerAccount.Credit + returnCredit; rtcc.PostReconciliationBalance = customerAccount.Credit; logger.Debug("The billed call cost was " + actualCallCost.ToString("0.#####") + ", return credit amount " + returnCredit.ToString("0.#####") + ", post reconciliation balance " + customerAccount.Credit.ToString("0.#####") + "."); } else { // An error has occurred and the credit reserved was less than the cost of the call. rtcc.ReconciliationResult = "Error: Actual call cost calculated as " + actualCallCost.ToString("0.#####"); customerAccount.Credit = customerAccount.Credit + returnCredit; rtcc.PostReconciliationBalance = customerAccount.Credit; logger.Debug("Error, the billed call cost was " + actualCallCost.ToString("0.#####") + " which resulted in a negative return credit amount of " + returnCredit.ToString("0.#####") + ", post reconciliation balance " + customerAccount.Credit.ToString("0.#####") + "."); } db.SaveChanges(); } else if (Math.Round(actualCallCost, 5) > Math.Round(rtcc.Cost.Value, 5) && Math.Abs(callCDR.Duration.Value - rtcc.SecondsReserved.Value) <= SECONDS_LENIENCY_FOR_RECONCILIATION) { rtcc.ReconciliationResult = "ok"; rtcc.PostReconciliationBalance = customerAccount.Credit; logger.Debug("The billed call duration was in +/- " + SECONDS_LENIENCY_FOR_RECONCILIATION + " of actual call duration so a billable call cost of " + rtcc.Cost.Value.ToString("0.#####") + " was accepted for an actual call cost of " + actualCallCost.ToString("0.#####") + ", post reconciliation balance " + rtcc.PostReconciliationBalance.Value.ToString("0.#####") + "."); db.SaveChanges(); } else if (Math.Round(actualCallCost, 5) > Math.Round(rtcc.Cost.Value, 5)) { rtcc.ReconciliationResult = "Error, calculated cost of " + actualCallCost.ToString("0.#####") + " exceeded reserved cost of " + rtcc.Cost.Value.ToString("0.#####") + "."; rtcc.PostReconciliationBalance = customerAccount.Credit; logger.Debug("Error, calculated cost of " + actualCallCost.ToString("0.#####") + " exceeded reserved cost of " + rtcc.Cost.Value.ToString("0.#####") + ", post reconciliation balance " + rtcc.PostReconciliationBalance.Value.ToString("0.#####") + "."); db.SaveChanges(); } else { // Call cost exactly matches the reserved cost. rtcc.ReconciliationResult = "ok"; rtcc.PostReconciliationBalance = customerAccount.Credit; logger.Debug("The billed call duration was the exact amount reserved of " + rtcc.Cost.Value.ToString("0.#####") + ", post reconciliation balance " + rtcc.PostReconciliationBalance.Value.ToString("0.#####") + "."); db.SaveChanges(); } } } } trans.Complete(); } } return(actualCallCost); }
/// <summary> /// Checks whether the account number is already in use for this owner. /// </summary> /// <param name="owner">The owner of the customer accounts to check.</param> /// <param name="accountNumber">The account number to check.</param> /// <param name="updatingID">An optional parameter that can be supplied when the check is being made for an account /// that is being updated and in which case this ID is for the record being updated.</param> /// <returns>True if the account number is in use otherwise false.</returns> public bool IsAccountNumberInUse(string owner, string accountNumber, string updatingID) { if (accountNumber.IsNullOrBlank()) { return false; } using (var db = new SIPSorceryEntities()) { return (from ca in db.CustomerAccounts where ca.Owner == owner.ToLower() && ca.AccountNumber.ToLower() == accountNumber.ToLower() && (updatingID == null || ca.ID != updatingID) select ca).Any(); } }
/// <summary> /// Updates an existing rate. /// </summary> /// <param name="rate">The rate to update.</param> public void Update(Rate rate) { using (var db = new SIPSorceryEntities()) { var existingRate = (from ra in db.Rates where ra.ID == rate.ID select ra).SingleOrDefault(); if (existingRate == null) { throw new ApplicationException("The rate to update could not be found"); } else if (existingRate.Owner.ToLower() != rate.Owner.ToLower()) { throw new ApplicationException("You are not authorised to update this rate."); } else { if (existingRate.Prefix != rate.Prefix) { if ((from rt in db.Rates where rt.Prefix == rate.Prefix select rt).Any()) { throw new ApplicationException("The rate prefix is already in use."); } } logger.Debug("Updating rate " + existingRate.Description + " for " + existingRate.Owner + "."); existingRate.Description = rate.Description; existingRate.Prefix = rate.Prefix; existingRate.Rate1 = rate.Rate1; existingRate.RateCode = rate.RateCode; db.SaveChanges(); } } }
/// <summary> /// This method attempts to reserve a chunk of credit for to allow a call to continue. /// </summary> /// <param name="seconds">The number of seconds the reservation is being requested for.</param> /// <param name="cdrID">The CDR that the credit reservation will be applied to.</param> /// <returns>True if there was enough credit for the reservation otherwise false.</returns> public bool ReserveCredit(int seconds, string cdrID) { if (seconds <= 0 || cdrID.IsNullOrBlank()) { return false; } using (var db = new SIPSorceryEntities()) { using (var trans = new TransactionScope()) { var callCDR = (from cdr in db.CDRs where cdr.ID == cdrID select cdr).SingleOrDefault(); if (callCDR == null) { logger.Debug("The reservation for " + cdrID + " and " + seconds + "s failed due to the no matching CDR."); return false; } else { if (callCDR.HungupTime != null) { logger.Debug("The reservation for " + cdrID + " and " + seconds + "s failed due to the CDR already being hungup."); callCDR.ReservationError = "Error, call already hungup."; } else if (callCDR.AccountCode.IsNullOrBlank()) { logger.Debug("The reservation for " + cdrID + " and " + seconds + "s failed due to the CDR having a blank accountcode."); callCDR.ReservationError = "Error, empty accountcode."; } else if (callCDR.Rate <= 0) { logger.Debug("The reservation for " + cdrID + " and " + seconds + "s failed due to the CDR having no rate."); callCDR.ReservationError = "Error, empty rate."; } if (callCDR.ReservationError == null) { string accountCode = callCDR.AccountCode; var customerAccount = db.CustomerAccounts.Where(x => x.AccountCode == accountCode).SingleOrDefault(); if (customerAccount == null) { logger.Debug("The reservation for " + accountCode + " and " + seconds + "s failed due to the no matching accountcode."); callCDR.ReservationError = "Error, no customer for accountcode."; } else { decimal amount = (Convert.ToDecimal(seconds) / SECONDS_FOR_RATE) * callCDR.Rate.Value; if (customerAccount.Credit < amount) { logger.Debug("The reservation for " + accountCode + " and " + seconds + "s failed due to lack of credit."); callCDR.ReservationError = "Error, insufficient credit."; } else { // The credit is available deduct it from the customer account balance and place it on the CDR. customerAccount.Credit = customerAccount.Credit - amount; // Set the fields on the CDR. callCDR.SecondsReserved = callCDR.SecondsReserved + seconds; callCDR.Cost = callCDR.Cost + amount; } } } db.SaveChanges(); trans.Complete(); return callCDR.ReservationError == null; } } } }
/// <summary> /// This method attempts to reserve a the initial amount of credit for a call. /// </summary> /// <param name="accountCode">The accountCode the credit should be reserved against.</param> /// <param name="amount">The amount of credit to reserve.</param> /// <param name="rate">The rate for the call destination and the values that will be used for subsequent credit reservations.</param> /// <param name="initialSeconds">IF the reservation is successful this parameter will hold the number of seconds that were reserved for the initial reservation.</param> /// <returns>True if there was enough credit for the reservation otherwise false.</returns> public decimal ReserveInitialCredit(string accountCode, decimal rate, out int initialSeconds) { logger.Debug("ReserveInitialCredit for " + accountCode + ", rate " + rate + "."); initialSeconds = 0; if (accountCode.IsNullOrBlank() || rate <= 0) { return Decimal.MinusOne; } using (var db = new SIPSorceryEntities()) { using (var trans = new TransactionScope()) { var customerAccount = db.CustomerAccounts.Where(x => x.AccountCode == accountCode).SingleOrDefault(); if (customerAccount == null) { logger.Debug("The initial reservation for " + accountCode + " failed due to the no matching accountcode."); return Decimal.MinusOne; } // Get the owning customer's RTCC billing increment. int rtccIncrement = (from cust in db.Customers where cust.Name.ToLower() == customerAccount.Owner.ToLower() select cust.RTCCBillingIncrement).Single(); initialSeconds = (rtccIncrement > MINIMUM_INITIAL_RESERVATION_SECONDS) ? rtccIncrement : MINIMUM_INITIAL_RESERVATION_SECONDS; decimal reservationCost = (decimal)initialSeconds / (decimal)60 * rate; if (customerAccount.Credit < reservationCost) { logger.Debug("The initial reservation for " + accountCode + ", duration " + initialSeconds + "s and " + reservationCost.ToString("0.#####") + " failed due to lack of credit."); return Decimal.MinusOne; } else { //var callCDR = (from cdr in db.CDRs where cdr.ID == cdrID select cdr).SingleOrDefault(); //if (callCDR == null) //{ // logger.Debug("The initial reservation for " + accountCode + " and " + reservationCost.ToString("0.#####") + " failed due to the no matching CDR for " + cdrID + "."); // return false; //} //else if (callCDR.HungupTime != null) //{ // logger.Debug("The initial reservation for " + accountCode + " and " + reservationCost.ToString("0.#####") + " failed due to the CDR already being hungup."); // return false; //} // The credit is available deduct it from the customer account balance and place it on the CDR. customerAccount.Credit = customerAccount.Credit - reservationCost; // Set the fields on the CDR. //callCDR.Rate = rate; //callCDR.SecondsReserved = seconds; //callCDR.AccountCode = accountCode; //callCDR.Cost = reservationCost; db.SaveChanges(); trans.Complete(); logger.Debug("The initial reservation for " + accountCode + ", duration " + initialSeconds + "s and " + reservationCost.ToString("0.#####") + " was successful."); return reservationCost; } } } }
/// <summary> /// This method should be called once a billable call has been completed. It will calcualte the final cost of the call and return /// any usused credit back to the customer account. /// </summary> /// <param name="cdrID">The ID of the CDR the credit is being returned for.</param> public void ReturnUnusedCredit(string cdrID) { logger.Debug("ReturnUnusedCredit for CDR ID " + cdrID + "."); if (cdrID.NotNullOrBlank()) { using (var db = new SIPSorceryEntities()) { using (var trans = new TransactionScope()) { var callCDR = (from cdr in db.CDRs where cdr.ID == cdrID select cdr).SingleOrDefault(); if (callCDR == null) { logger.Error("The unused credit could not be returned for " + cdrID + " due to the no matching CDR."); } else { string reconciliationError = null; if (callCDR.Duration == null) { reconciliationError = "Error, the call duration was null."; logger.Warn("The unused credit could not be returned for " + cdrID + " the CDR has not been hungup."); } else if (callCDR.Cost == null) { reconciliationError = "Error, the call cost was null."; logger.Warn("The unused credit could not be returned for " + cdrID + " the call cost was empty."); } else if (callCDR.AccountCode.IsNullOrBlank()) { reconciliationError = "Error, the accountcode was null."; logger.Warn("The unused credit could not be returned for " + cdrID + " due to the CDR having a blank accountcode."); } else if (callCDR.Rate <= 0) { reconciliationError = "Error, the rate was not set."; logger.Warn("The unused credit could not be returned for " + cdrID + " due to the CDR having no rate."); } if (reconciliationError != null) { callCDR.ReconciliationResult = reconciliationError; db.SaveChanges(); } else { string accountCode = callCDR.AccountCode; var customerAccount = db.CustomerAccounts.Where(x => x.AccountCode == accountCode).SingleOrDefault(); if (customerAccount == null) { logger.Debug("The unused credit could not be returned for " + cdrID + " due to the no matching accountcode."); callCDR.ReconciliationResult = "Error, no matching customer for " + accountCode + "."; db.SaveChanges(); } else { // Get the owning customer's RTCC billing increment. int rtccIncrement = (from cust in db.Customers where cust.Name.ToLower() == callCDR.Owner.ToLower() select cust.RTCCBillingIncrement).Single(); int billableDuration = (callCDR.Duration.Value % rtccIncrement == 0) ? callCDR.Duration.Value : (callCDR.Duration.Value / rtccIncrement + 1) * rtccIncrement; decimal actualCallCost = (Convert.ToDecimal(billableDuration) / SECONDS_FOR_RATE) * callCDR.Rate.Value; logger.Debug("RTCC billable duration " + billableDuration + " (increment " + rtccIncrement + "), actual call cost calculated at " + actualCallCost.ToString("0.#####") + " for call with cost of " + callCDR.Cost + "."); if (Math.Round(actualCallCost, 5) < Math.Round(callCDR.Cost.Value, 5)) { decimal returnCredit = Math.Round(callCDR.Cost.Value, 5) - Math.Round(actualCallCost, 5); if (returnCredit > 0) { // There is some credit to return to the customer account. callCDR.Cost = callCDR.Cost.Value - returnCredit; callCDR.ReconciliationResult = "ok"; callCDR.PostReconciliationBalance = customerAccount.Credit + returnCredit; customerAccount.Credit = customerAccount.Credit + returnCredit; } else { // An error has occurred and the credit reserved was less than the cost of the call. callCDR.ReconciliationResult = "Error: Actual call cost calculated as " + actualCallCost.ToString("0.#####"); callCDR.PostReconciliationBalance = customerAccount.Credit + returnCredit; customerAccount.Credit = customerAccount.Credit + returnCredit; } db.SaveChanges(); } else if (Math.Round(actualCallCost, 5) > Math.Round(callCDR.Cost.Value, 5) && Math.Abs(callCDR.Duration.Value - callCDR.SecondsReserved.Value) <= SECONDS_LENIENCY_FOR_RECONCILIATION) { logger.Debug("The billed call duration was in +/- " + SECONDS_LENIENCY_FOR_RECONCILIATION + " of actual call duration so a a bille call cost of " + callCDR.Cost.Value.ToString("0.#####") + " was accepted for an actual call cost of " + actualCallCost.ToString("0.#####") + "."); callCDR.ReconciliationResult = "ok"; callCDR.PostReconciliationBalance = customerAccount.Credit; db.SaveChanges(); } else if (Math.Round(actualCallCost, 5) > Math.Round(callCDR.Cost.Value, 5)) { callCDR.ReconciliationResult = "Error, calculated cost of " + actualCallCost.ToString("0.#####") + " exceeded reserved cost of " + callCDR.Cost.Value.ToString("0.#####") + "."; callCDR.PostReconciliationBalance = customerAccount.Credit; db.SaveChanges(); } else { // Call cost exactly matches the reserved cost. callCDR.ReconciliationResult = "ok"; callCDR.PostReconciliationBalance = customerAccount.Credit; db.SaveChanges(); } } } } trans.Complete(); } } } }
public void SetCDRIsHangingUp(string cdrID) { using (var db = new SIPSorceryEntities()) { var callCDR = (from cdr in db.CDRs where cdr.ID == cdrID select cdr).SingleOrDefault(); if (callCDR == null) { logger.Debug("CDR could not be found for " + cdrID + " when attemping to set the IsHangingUp flag."); } else { callCDR.IsHangingUp = true; db.SaveChanges(); } } }
/// <summary> /// This method attempts to reserve a chunk of credit for to allow a call to continue. /// </summary> /// <param name="seconds">The number of seconds the reservation is being requested for.</param> /// <param name="cdrID">The CDR that the credit reservation will be applied to.</param> /// <returns>True if there was enough credit for the reservation otherwise false.</returns> public bool ReserveCredit(int seconds, string rtccID) { if (seconds <= 0 || rtccID.IsNullOrBlank()) { return(false); } using (var db = new SIPSorceryEntities()) { using (var trans = new TransactionScope()) { var callRTCC = (from rtcc in db.RTCCs1 where rtcc.ID == rtccID select rtcc).SingleOrDefault(); if (callRTCC == null) { logger.Debug("The reservation for " + rtccID + " and " + seconds + "s failed due to the no matching RTCC record."); return(false); } else { var callCDR = (from cdr in db.CDRs where cdr.ID == callRTCC.CDRID select cdr).SingleOrDefault(); if (callCDR.HungupTime != null) { logger.Debug("The reservation for " + rtccID + " and " + seconds + "s failed due to the CDR already being hungup."); callRTCC.ReservationError = "Error, call already hungup."; } if (callRTCC.ReservationError == null) { string accountCode = callRTCC.AccountCode; var customerAccount = db.CustomerAccounts.Where(x => x.AccountCode == accountCode).SingleOrDefault(); if (customerAccount == null) { logger.Debug("The reservation for " + accountCode + " and " + seconds + "s failed due to the no matching accountcode."); callRTCC.ReservationError = "Error, no customer for accountcode."; } else { decimal amount = (Convert.ToDecimal(seconds) / SECONDS_FOR_RATE) * callRTCC.Rate.Value; if (customerAccount.Credit < amount) { logger.Debug("The reservation for " + accountCode + " and " + seconds + "s failed due to lack of credit."); callRTCC.ReservationError = "Error, insufficient credit."; } else { // The credit is available deduct it from the customer account balance and place it on the CDR. customerAccount.Credit = customerAccount.Credit - amount; // Set the fields on the CDR. callRTCC.SecondsReserved = callRTCC.SecondsReserved + seconds; callRTCC.Cost = callRTCC.Cost + amount; } } } db.SaveChanges(); trans.Complete(); return(callRTCC.ReservationError == null); } } } }
public Rate Get(string owner, string id) { if (id.IsNullOrBlank()) { return null; } using (var db = new SIPSorceryEntities()) { return (from ra in db.Rates where ra.Owner == owner.ToLower() && ra.ID.ToLower() == id.ToLower() select ra).SingleOrDefault(); } }
/// <summary> /// Monitors for CDR's that utilised real-time call control and that have now completed and require credit reconciliation. /// </summary> private void ReconcileCDRs() { try { Thread.CurrentThread.Name = RTCC_THREAD_NAME; logger.Debug("RTCC Core Starting Reconcile CDRs thread."); while (!m_exit) { try { using (var db = new SIPSorceryEntities()) { var cdrsReconciliationDue = (from cdr in db.CDRs where cdr.AccountCode != null && (cdr.AnsweredStatus < 200 || cdr.AnsweredStatus >= 300 || cdr.HungupTime != null) && cdr.ReconciliationResult == null && cdr.Cost > 0 orderby cdr.HungupTime select cdr).Take(NUMBER_CDRS_PER_ROUNDTRIP); while (cdrsReconciliationDue.Count() > 0) { foreach (CDR cdr in cdrsReconciliationDue) { Log_External(new SIPMonitorConsoleEvent(SIPMonitorServerTypesEnum.RTCC, SIPMonitorEventTypesEnum.DialPlan, "Reconciling credit for call " + cdr.Dst + ".", cdr.Owner)); m_customerAccountDataLayer.ReturnUnusedCredit(cdr.ID); } } } } //catch (ReflectionTypeLoadException ex) //{ // StringBuilder sb = new StringBuilder(); // foreach (Exception exSub in ex.LoaderExceptions) // { // sb.AppendLine(exSub.Message); // if (exSub is FileNotFoundException) // { // FileNotFoundException exFileNotFound = exSub as FileNotFoundException; // if (!string.IsNullOrEmpty(exFileNotFound.FusionLog)) // { // sb.AppendLine("Fusion Log:"); // sb.AppendLine(exFileNotFound.FusionLog); // } // } // sb.AppendLine(); // } // string errorMessage = sb.ToString(); // logger.Error(errorMessage); //} catch (Exception monitorExcp) { logger.Error("Exception ReconcileCDRs Monitoring. " + monitorExcp); } Thread.Sleep(1000); } logger.Warn("RTCCCore ReconcileCDRs thread stopping."); } catch (Exception excp) { logger.Error("Exception RTCCCore ReconcileCDRs. " + excp.Message); logger.Error("InnerException RTCCCore ReconcileCDRs. " + excp.InnerException); } }
/// <summary> /// Checks whether the account code is already in use for the system. /// </summary> /// <param name="accountCode">The account code to check.</param> /// <param name="updatingID">An optional parameter that can be supplied when the check is being made for an account /// that is being updated and in which case this ID is for the record being updated.</param> /// <returns>True if the account code is in use otherwise false.</returns> public bool IsAccountCodeInUse(string accountCode, string updatingID) { using (var db = new SIPSorceryEntities()) { return (from ca in db.CustomerAccounts where ca.AccountCode.ToLower() == accountCode.ToLower() && (updatingID == null || ca.ID != updatingID) select ca).Any(); } }
/// <summary> /// Monitors for CDR's that utilised real-time call control and that have now completed and require credit reconciliation. /// </summary> private void ReconcileCDRs() { try { Thread.CurrentThread.Name = RTCC_THREAD_NAME; logger.Debug("RTCC Core Starting Reconcile CDRs thread."); while (!m_exit) { try { using (var db = new SIPSorceryEntities()) { var rtccReconciliationDue = (from rtcc in db.RTCCs1.Include("cdr") where rtcc.AccountCode != null && ((rtcc.cdr.AnsweredStatus > 0 && rtcc.cdr.AnsweredStatus < 200) || rtcc.cdr.AnsweredStatus >= 300 || rtcc.cdr.HungupTime != null || rtcc.cdr.HungupReason != null) && rtcc.ReconciliationResult == null && rtcc.PostReconciliationBalance == null && rtcc.Cost > 0 orderby rtcc.cdr.HungupTime select rtcc).Take(NUMBER_CDRS_PER_ROUNDTRIP); while (rtccReconciliationDue.Count() > 0) { foreach (RTCC rtcc in rtccReconciliationDue) { Log_External(new SIPMonitorConsoleEvent(SIPMonitorServerTypesEnum.RTCC, SIPMonitorEventTypesEnum.DialPlan, "Reconciling credit for call " + rtcc.cdr.Dst + ".", rtcc.cdr.Owner)); logger.Debug("Answered Status=" + rtcc.cdr.AnsweredStatus + ", hungup time=" + rtcc.cdr.HungupTime + ", hungup reason=" + rtcc.cdr.HungupReason + "."); logger.Debug("Reconciliation starting for CDR " + rtcc.cdr.ID + ", owner " + rtcc.cdr.Owner + ", destination " + rtcc.cdr.Dst + ", duration " + rtcc.cdr.Duration + ", rate " + rtcc.Rate + ", setup cost " + rtcc.SetupCost +", increment seconds " + rtcc.IncrementSeconds + " and reserved credit of " + rtcc.Cost + "."); decimal callCost = m_customerAccountDataLayer.ReturnUnusedCredit(rtcc.ID); if (callCost > Decimal.Zero) { // Check whether a reconciliation callback needs to be made for this customer account. var customer = _customerDataLayer.GetForName(rtcc.cdr.Owner); if(customer.RTCCReconciliationURL.NotNullOrBlank()) { // Send an async HTTP GET request to the specified URL. } } } } } } catch (Exception monitorExcp) { logger.Error("Exception ReconcileCDRs Monitoring. " + monitorExcp); } Thread.Sleep(1000); } logger.Warn("RTCCCore ReconcileCDRs thread stopping."); } catch (Exception excp) { logger.Error("Exception RTCCCore ReconcileCDRs. " + excp.Message); logger.Error("InnerException RTCCCore ReconcileCDRs. " + excp.InnerException); } }
/// <summary> /// This method acts as a call rate lookup engine. It's very basic and simply matches the call destination /// based on the record that has the longest matching prefix. /// </summary> /// <param name="accountCode">The accountCode the call rate lookup is for.</param> /// <param name="rateCode">The rate code for the call. If specified and a matching rate is found it will /// take precedence over the destination.</param> /// <param name="destination">The call desintation the rate lookup is for.</param> /// <returns>If a matching rate is found a greater than 0 decimal value otherwise 0.</returns> public decimal GetRate(string accountCode, string rateCode, string destination) { logger.Debug("GetRate for accountcode " + accountCode + ", ratecode " + rateCode + " and destination " + destination + "."); if (accountCode.IsNullOrBlank() || destination.IsNullOrBlank()) { return Decimal.MinusOne; } using (var db = new SIPSorceryEntities()) { Rate callRate = null; if (rateCode.NotNullOrBlank()) { callRate = (from rate in db.Rates join custAcc in db.CustomerAccounts on rate.Owner equals custAcc.Owner where custAcc.AccountCode == accountCode && rate.RateCode == rateCode select rate).SingleOrDefault(); } if (callRate == null) { callRate = (from rate in db.Rates join custAcc in db.CustomerAccounts on rate.Owner equals custAcc.Owner where custAcc.AccountCode == accountCode && destination.StartsWith(rate.Prefix) orderby rate.Prefix.Length descending select rate).FirstOrDefault(); } // If the rate is still null check for international prefixes. if (callRate == null) { var customerAccount = db.CustomerAccounts.Where(x => x.AccountCode == accountCode).SingleOrDefault(); if (customerAccount == null) { logger.Debug("The rate lookup for " + accountCode + " failed due to the no matching accountcode."); return Decimal.MinusOne; } string rtccInternationalPrefixes = (from cust in db.Customers where cust.Name.ToLower() == customerAccount.Owner.ToLower() select cust.RTCCInternationalPrefixes).Single(); if (rtccInternationalPrefixes.NotNullOrBlank()) { string trimmedDestination = null; string[] prefixes = rtccInternationalPrefixes.Split(','); foreach (string prefix in prefixes) { if (destination.StartsWith(prefix)) { trimmedDestination = destination.Substring(prefix.Length); logger.Debug("The destination matched international prefix of " + prefix + ", looking up rate for " + trimmedDestination + "."); break; } } if (trimmedDestination != null) { callRate = (from rate in db.Rates join custAcc in db.CustomerAccounts on rate.Owner equals custAcc.Owner where custAcc.AccountCode == accountCode && trimmedDestination.StartsWith(rate.Prefix) orderby rate.Prefix.Length descending select rate).FirstOrDefault(); } } } if (callRate != null) { logger.Debug("Rate found for " + accountCode + " and " + destination + " was " + callRate.Rate1 + "."); return callRate.Rate1; } else { logger.Debug("No rate found for " + accountCode + " and " + destination + "."); return Decimal.MinusOne; } } }
public override bool ValidateUser(string username, string password) { try { string ipAddress = null; if (OperationContext.Current != null) { OperationContext context = OperationContext.Current; MessageProperties properties = context.IncomingMessageProperties; RemoteEndpointMessageProperty endpoint = properties[RemoteEndpointMessageProperty.Name] as RemoteEndpointMessageProperty; if (endpoint != null) { ipAddress = endpoint.Address; } } else if (HttpContext.Current != null) { ipAddress = HttpContext.Current.Request.UserHostAddress; } if (username.IsNullOrBlank() || password.IsNullOrBlank()) { logger.Debug("Login from " + ipAddress + " failed, either username or password was not specified."); return false; } else { using (var sipSorceryEntities = new SIPSorceryEntities()) { Customer customer = (from cust in sipSorceryEntities.Customers where cust.Name.ToLower() == username.ToLower() select cust).SingleOrDefault(); if (customer != null && PasswordHash.Hash(password, customer.Salt) == customer.CustomerPassword) { if (!customer.EmailAddressConfirmed) { throw new ApplicationException("Your email address has not yet been confirmed."); } else if (customer.Suspended) { throw new ApplicationException("Your account is suspended."); } else if (customer.ServiceLevel == CustomerServiceLevels.PremiumPayReqd.ToString() || customer.ServiceLevel == CustomerServiceLevels.ProfessionalPayReqd.ToString() || customer.ServiceLevel == CustomerServiceLevels.SwitchboardPayReqd.ToString()) { var serviceLevel = (CustomerServiceLevels)Enum.Parse(typeof(CustomerServiceLevels), customer.ServiceLevel); throw new PaymentRequiredException(serviceLevel, "Your account requires payment before it can be activated. Please visit the payment page."); } else if (customer.ServiceRenewalDate != null && DateTimeOffset.Parse(customer.ServiceRenewalDate) < DateTimeOffset.Now.AddDays(-1)) { throw new PaymentRequiredException(CustomerServiceLevels.RenewalReqd, "Your account is overdue please login to the web site (non-Silverlight login) to renew."); } else { logger.Debug("Login from " + ipAddress + " successful for " + username + "."); return true; } } else { logger.Debug("Login from " + ipAddress + " failed for " + username + "."); return false; } } } } catch (ApplicationException) { throw; } catch (Exception excp) { logger.Error("Exception SIPSorceryMembershipProvider ValidateUser. " + excp); throw new ApplicationException("There was an " + excp.GetType().ToString() + " server error attempting to login."); } }
/// <summary> /// Checks the account code is valid and if not will also check the account number. /// </summary> /// <param name="owner">The owner of the customer accounts to check.</param> /// <param name="accountNumber">The account code to check.</param> /// <returns>If a matching accountcode or number is found the account code will be returned otherwise null.</returns> public CustomerAccount CheckAccountCode(string owner, string accountCode) { using (var db = new SIPSorceryEntities()) { var account = (from ca in db.CustomerAccounts where ca.Owner.ToLower() == owner.ToLower() && (ca.AccountCode.ToLower() == accountCode.ToLower() || ca.AccountNumber == accountCode) select ca).SingleOrDefault(); return (account != null) ? account : null; } }
public decimal GetBalance(string accountCode) { using (var db = new SIPSorceryEntities()) { var customerAccount = db.CustomerAccounts.Where(x => x.AccountCode == accountCode).SingleOrDefault(); if (customerAccount == null) { return -1; } else { return customerAccount.Credit; } } }
/// <summary> /// Updates an existing customer account. /// </summary> /// <param name="customerAccount">The customer account to update.</param> public void Update(CustomerAccount customerAccount) { using (var db = new SIPSorceryEntities()) { var existingAccount = (from ca in db.CustomerAccounts where ca.ID == customerAccount.ID select ca).SingleOrDefault(); if (existingAccount == null) { throw new ApplicationException("The customer account to update could not be found"); } else if (existingAccount.Owner.ToLower() != customerAccount.Owner.ToLower()) { throw new ApplicationException("You are not authorised to update this customer account."); } else { CheckUniqueFields(customerAccount); logger.Debug("Updating customer account " + existingAccount.AccountName + " for " + existingAccount.Owner + "."); existingAccount.AccountName = customerAccount.AccountName; existingAccount.AccountNumber = customerAccount.AccountNumber; existingAccount.Credit = customerAccount.Credit; existingAccount.PIN = customerAccount.PIN; db.SaveChanges(); } } }