/// <summary>
/// Gets list of project IDs and cron expression strings assigned to them.
/// </summary>
/// <returns>list of project IDs and cron expression strings assigned to them</returns>
public static IList <KeyValuePair <string, int> > GetCronStringsByProjectIds()
{
List <KeyValuePair <string, int> > list = new List <KeyValuePair <string, int> >();
using (DbConnection connection = new OracleConnection(AppSettings.GetConnectionString()))
{
connection.Open();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT DISTINCT SERVICE_INFO.IVR_PROJECT_ID," +
"NVL(NVL(IVR_PROJECT.SCHEDULE_CRON,IVR_SERVER.SCHEDULE_CRON)," +
"(SELECT SCHEDULE_CRON FROM " + AppSettings.GetSchemaName() +
".ILC_INSTANCE)) FROM " + AppSettings.GetSchemaName() + ".SERVICE_INFO INNER JOIN " +
AppSettings.GetSchemaName() + ".IVR_PROJECT ON " +
"SERVICE_INFO.IVR_PROJECT_ID = IVR_PROJECT.IVR_PROJECT_ID AND " +
"IVR_PROJECT.ENABLED != 0 INNER JOIN " + AppSettings.GetSchemaName() +
".IVR_SERVER ON IVR_PROJECT.IVR_SERVER_ID = IVR_SERVER.IVR_SERVER_ID AND " +
"IVR_SERVER.ENABLED != 0 WHERE SERVICE_INFO.ENABLED != 0";
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(new KeyValuePair <string, int>(reader[1] as string, Convert.ToInt32(reader[0])));
}
}
}
}
return(list);
}
/// <summary>
/// Gets scenarios
/// </summary>
/// <returns>scenarios list</returns>
public static IList <ScenarioItem> GetScenarios()
{
List <ScenarioItem> list = new List <ScenarioItem>();
using (DbConnection connection = new OracleConnection(AppSettings.GetConnectionString()))
{
connection.Open();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT ILC_SCENARIO_ID, NAME, ASSEMBLIES, SCRIPTING_EXPRESSION FROM " +
AppSettings.GetSchemaName() + ".ILC_SCENARIO";
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ScenarioItem item = new ScenarioItem();
item.id = Convert.ToInt32(reader[0]);
item.name = reader[1] as string;
item.assemblies = reader[2] as string;
item.scriptingExpression = reader[3] as string;
list.Add(item);
}
}
}
}
return(list);
}
/// <summary>
/// Gets ILC configuration settings from the database.
/// </summary>
/// <returns>container for ILC configuration settings</returns>
public static IDictionary GetIlcConfigurationSettings()
{
Hashtable map = new Hashtable();
using (DbConnection connection = new OracleConnection(AppSettings.GetConnectionString()))
{
connection.Open();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT NAME, VALUE FROM " +
AppSettings.GetSchemaName() + ".RUNTIME_CONFIGURATION";
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string name = reader["NAME"] as string;
string value = reader["VALUE"] as string;
ConfigurationManager.AppSettings[name] = value;
}
}
}
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT OUT_CHANNEL_NUM, TERMINATOR_KEY, TIMESPAN,COMMON_RECIPIENT_EMAIL," +
"TIME_BETWEEN_VERIFICATION, MAX_ATTEMPTS, FROM_EMAIL, SILENCE_TIMEOUT," +
"RECORD_DURATION, LOGIN_SILENCE_TIMEOUT, SUMMARY_REPORT_CRON," +
"SUMMARY_RECIPIENT_EMAIL, JOB_MISFIRE_THRESHOLD, VERSION," +
"SCRIPTS_VERSION FROM " + AppSettings.GetSchemaName() + ".ILC_INSTANCE";
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
map[AppSettings.OutgoingChannelsKey] = reader[0] as decimal?;
map[AppSettings.TerminatorKeyKey] = reader[1] as string;
map[AppSettings.TimeSpanKey] = reader[2] as decimal?;
map[AppSettings.CommonRecipientEmailAddressesKey] = reader[3] as string;
map[AppSettings.TimeSpanBetweenVerificationsKey] = reader[4] as decimal?;
map[AppSettings.AttemptsMaxNumberKey] = reader[5] as decimal?;
map[AppSettings.EmailFromAddressKey] = reader[6] as string;
map[AppSettings.SilenceTimeoutKey] = reader[7] as decimal?;
map[AppSettings.RecordDurationKey] = reader[8] as decimal?;
map[AppSettings.LoginSilenceTimeoutKey] = reader[9] as decimal?;
map[AppSettings.SummaryReportCronKey] = reader[10] as string;
map[AppSettings.SummaryRecipientEmailKey] = reader[11] as string;
map[AppSettings.JobMisfireThresholdKey] = reader[12] as decimal?;
map[AppSettings.SettingsVersion] = reader[13] as decimal?;
map[AppSettings.ScriptsVersion] = reader[14] as decimal?;
}
}
}
}
return(map);
}
/// <summary>
/// Fixes ILC state inconsistency after improper service termination etc.
/// </summary>
public static void ResetIlcState()
{
using (DbConnection connection = new OracleConnection(AppSettings.GetConnectionString()))
{
connection.Open();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE " + AppSettings.GetSchemaName() +
".SERVICE_VERIFICATION_SESSION SET IS_REPORTED=1";
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// Gets version of service settings
/// </summary>
/// <returns>version of service settings that increments on each settings update</returns>
public static uint GetSettingsVersion()
{
string strQuery = "SELECT VERSION FROM " + AppSettings.GetSchemaName() + ".ILC_INSTANCE";
using (DbConnection connection = new OracleConnection(AppSettings.GetConnectionString()))
{
connection.Open();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = strQuery;
return(Convert.ToUInt32(cmd.ExecuteScalar()));
}
}
}