/// <summary> /// Retrieves a list of call categories (mobile, local, etc) stored in the database /// </summary> /// <returns>Returns a CallCategory object that contains each of the call categories</returns> private ArrayList GetCallCategories(ArrayList categories) { ArrayList tempCategories = new ArrayList(); using (IDBManager manager = new DBManager(_provider, _connectionString)) { manager.Open(); IDataReader categoriesReader = manager.ExecuteReader(CommandType.Text, "SELECT [Regular Expression], [Block Size], [Cost], [Name], [Connection Cost] FROM [CallCategory]"); while (categoriesReader.Read()) { CallCategory category = new CallCategory(_connectionString, _provider); string reg = categoriesReader.GetValue(0).ToString(); Regex regExp = new Regex(reg); category.RegularExpression = regExp; category.BlockSize = Convert.ToInt32(categoriesReader.GetValue(1)); category.Cost = Convert.ToInt32(categoriesReader.GetValue(2)); category.Name = categoriesReader.GetValue(3).ToString(); category.ConnectionCost = Convert.ToInt32(categoriesReader.GetValue(4)); tempCategories.Add(category); } manager.CloseReader(); } return(tempCategories); }
// Delete the call cost at the specified ID public List <ValidationError> DeleteCallCost(int id) { List <ValidationError> errors = new List <ValidationError>(); try { if (id != -1) { CallCategory callCostToDelete = new CallCategory(_connectionString, _provider); callCostToDelete.Delete(id); } else { // if not a valid id then throw an exception to be caught in the application handler errors.Add(new ValidationError("Could not delete Call Cost. Invalid ID.")); } } catch (Exception ex) { errors.Add(new ValidationError("Unexpected Error: " + ex.ToString().Replace('\'', '"'))); } return(errors); }
public double GetCallCost(string callDate, string duration, string callerNumber, string receiverNumber) { // Fix date into database format DateTimeFormatInfo dtfo = new CultureInfo(CultureInfo.CurrentCulture.ToString(), true).DateTimeFormat; if (callDate != String.Empty) { DateTime cDate = Convert.ToDateTime(callDate, dtfo); callDate = cDate.ToString("yyyy-MM-dd"); } string query = @" SELECT * FROM ( SELECT * FROM newSearch ) AS All_Data WHERE (julianday(All_Data.[Call Date]) - julianday('" + callDate + @"')) > 0 AND (julianday(All_Data.[Call Date]) - julianday('" + callDate + @"')) < 1 AND All_Data.[Duration] = '" + duration + @"' AND All_Data.[Caller Number] = '" + callerNumber + @"' AND All_Data.[Receiver Number] = '" + receiverNumber + "'"; ArrayList categories = new ArrayList(); categories = GetCallCategories(categories); using (IDBManager manager = new DBManager(_provider, _connectionString)) { manager.Open(); string searchQuery = ""; if (_provider == DataProvider.SqlServer) { searchQuery = "SELECT CostData.[Duration], CostData.[Dialled Extension] FROM (" + query + ") AS CostData WHERE SUBSTRING(CostData.[Caller Extension], 1, 1) != 'T'"; } else if (_provider == DataProvider.Sqlite) { searchQuery = "SELECT CostData.[Duration], CostData.[Dialled Extension] FROM (" + query + ") AS CostData WHERE SUBSTR(CostData.[Caller Extension], 1, 1) != 'T'"; } IDataReader costsReader = manager.ExecuteReader(CommandType.Text, searchQuery); while (costsReader.Read()) { string numberToMatch = costsReader.GetValue(1).ToString(); // Called Number for (int i = 0; i < categories.Count; i++) { // Create a temporary RegexHolder CallCategory category = (CallCategory)categories[i]; if (category.RegularExpression.IsMatch(numberToMatch)) { // Get the duration from the reader string thisduration = costsReader.GetValue(0).ToString(); // Calculate the cost of the call based on the duration, cost per block and the block size double newTotal = calculateCosts(thisduration, category.Cost, category.BlockSize); return(newTotal); } } } return(0.0); } }
public string GetLastRowID() { CallCategory cc = new CallCategory(_connectionString, _provider); return(cc.GetLastRowID()); }
/// <summary> /// Add a new call cost to the database /// </summary> /// <param name="ID">A unique ID</param> /// <param name="blockSize">The size of each call cost block. For example, a 60 second block size is charged at $2</param> /// <param name="cost">The cost for the specified block size ($)</param> /// <param name="connectionCost">The amount charged to make the initial connection</param> /// <param name="regex">The regular expression that defines the calls for the specified call cost</param> /// <param name="name">A friendly name for the call cost</param> /// <param name="updating">Whether to update the existing call cost or create a new call cost</param> /// <returns></returns> public List <ValidationError> UpdateCallCost(string ID, string blockSize, string cost, string connectionCost, string regex, string type, string name, string priority, string chargeUnfinished, bool updating) { List <ValidationError> validationErrors = new List <ValidationError>(); CallCategory callCategory = new CallCategory(_connectionString, _provider); try { // Perform validation if (name != String.Empty) { if (regex != String.Empty) { callCategory.Name = name; // Validate the 'Block Size' field int block_size = 0; try { block_size = Convert.ToInt32(blockSize); if (block_size < 0) { throw new Exception(); } if (block_size > 999999) { throw new InvalidOperationException(); } } catch (InvalidOperationException) { validationErrors.Add(new ValidationError("Block Size value must be less than 1,000,000")); } catch (Exception) { validationErrors.Add(new ValidationError("Invalid block size - must be a number, greater than zero.")); } // Validate the 'Cost' field int costValue = 0; try { costValue = Convert.ToInt32(cost); if (costValue < 0) { throw new Exception(); } if (costValue > 999999) { throw new InvalidOperationException(); } } catch (InvalidOperationException) { validationErrors.Add(new ValidationError("Rate per Block value must be less than 1,000,000")); } catch (Exception) { validationErrors.Add(new ValidationError("Invalid 'Rate per Block' value - must be a number, equal to or greater than zero.")); } // Validate the 'Connection Cost' field int connCost = 0; try { connCost = Convert.ToInt32(connectionCost); if (connCost < 0) { throw new Exception(); } if (connCost > 999999) { throw new InvalidOperationException(); } } catch (InvalidOperationException) { validationErrors.Add(new ValidationError("Connection Cost must be less than 1,000,000")); } catch (Exception) { validationErrors.Add(new ValidationError("Invalid 'Connection Cost' value - must be a number, greater than or equal to zero.")); } // Validate the 'Regular Expression' field Regex reg = CreateRegex(regex); if (reg == null) { validationErrors.Add(new ValidationError("'Regular Expression' field must be a valid regular expression")); } //Validate the priority int priorityInt = 0; try { priorityInt = Convert.ToInt32(priority); if (priorityInt < 0) { throw new Exception(); } } catch (Exception) { validationErrors.Add(new ValidationError("'Priority' field must be a number")); } //Validate the Charge Unfinished int chargeInt = 0; try { chargeInt = Convert.ToInt32(chargeUnfinished); if (chargeInt < 0) { throw new Exception(); } } catch (Exception) { validationErrors.Add(new ValidationError("Error with the 'Charge Unfinished Block' check box.")); } if (ID != String.Empty) { callCategory.ID = Convert.ToInt32(ID); } else { callCategory.ID = -1; } callCategory.Name = name; callCategory.ConnectionCost = connCost; callCategory.Cost = costValue; callCategory.BlockSize = block_size; callCategory.RegularExpression = reg; callCategory.Type = type; callCategory.Priority = priorityInt; callCategory.ChargeUnfinished = chargeInt; } else { validationErrors.Add(new ValidationError("'Regular Expression' field cannot be blank")); } } else { validationErrors.Add(new ValidationError("'Name' field cannot be blank")); } if (validationErrors.Count == 0) { // Saves the Call Cost to the database if there are no errors callCategory.Save(updating); } } catch (Exception ex) { validationErrors.Add(new ValidationError("Unexpected Error: " + ex.ToString().Replace('\'', '"'))); } return(validationErrors); }