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