/// <summary>
/// Gets server data
/// </summary>
/// <param name="id">server id</param>
/// <returns>DetailServerData object</returns>
public static DetailServerData GetDetailServerData(string id)
{
DetailServerData data = new DetailServerData();
using (DbConnection connection = new OracleConnection(WebSettings.GetConnectionString()))
{
connection.Open();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText =
"SELECT IVR_SERVER_ID, NAME, NUM_CHANNELS, DB_CONN, ENABLED, AUTH_CHECKER," +
"SCHEDULE_CRON FROM " + schemaName + ".IVR_SERVER WHERE IVR_SERVER_ID =:PARAM_SERVER_ID";
AddInputParameter(cmd, "PARAM_SERVER_ID", id);
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
data.serverId = Convert.ToString(reader[0]);
data.name = Convert.ToString(reader[1]);
data.channels = Convert.ToString(reader[2]);
data.connectionString = Convert.ToString(reader[3]);
data.enabled = Convert.ToString(reader[4]);
data.checker = Convert.ToString(reader[5]);
data.cron = Convert.ToString(reader[6]);
}
}
}
}
return(data);
}
/// <summary>
/// Check whether user exists
/// </summary>
/// <param name="strName">user name</param>
/// <param name="strPassword">user password</param>
/// <returns>true if user exists</returns>
public static bool IsUserExist(string strName, string strPassword)
{
bool userExists = false;
using (DbConnection connection = new OracleConnection(WebSettings.GetConnectionString()))
{
connection.Open();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText =
"SELECT * FROM ASPIRIN.AUTHUSERS WHERE USERID =:PARAM_USERID AND PASSWORD =:PARAM_PASSWORD";
AddInputParameter(cmd, "PARAM_USERID", strName);
AddInputParameter(cmd, "PARAM_PASSWORD", Connect.XCode(strPassword, true));
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
userExists = true;
}
}
}
}
return(userExists);
}
/// <summary>
/// Gets ILC status data
/// </summary>
/// <returns>IlcStatusData object</returns>
public static IlcStatusData GetStatusData()
{
IlcStatusData data = new IlcStatusData();
using (DbConnection connection = new OracleConnection(WebSettings.GetConnectionString()))
{
connection.Open();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText =
"WITH STAT AS (SELECT FINISHED, COUNT(1) AS CNT FROM (SELECT DECODE(STATUS, 'SUCCEEDED', 1, 'FAILED', 1, 'AWAITING', DECODE(ATTEMPTS, (SELECT MAX_ATTEMPTS FROM " +
schemaName + ".ILC_INSTANCE), 1, 0), 0) AS FINISHED FROM " + schemaName + ".SERVICE_VERIFICATION INNER JOIN " +
schemaName + ".SERVICE_VERIFICATION_SESSION ON SERVICE_VERIFICATION.REPORT_ID=SERVICE_VERIFICATION_SESSION.REPORT_ID AND SERVICE_VERIFICATION_SESSION.IS_REPORTED=0) S GROUP BY FINISHED) " +
"SELECT START_TIME, IS_REPORTED, NVL((SELECT CNT FROM STAT WHERE FINISHED=0), 0) AS REMAINING, NVL((SELECT CNT FROM STAT WHERE FINISHED=1), 0) AS FINISHED FROM " +
schemaName + ".SERVICE_VERIFICATION_SESSION";
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
data.lastCheckDate = (DateTime)reader[0];
int remained = Convert.ToInt32(reader[2]);
int passed = Convert.ToInt32(reader[3]);
data.currServiceState = (Convert.ToInt32(reader[1]) == 0) ? "Working: " + passed + " out of " + (passed + remained) + "..."
: ConstExpressions.WORK_STATUS_IDLE;
}
}
}
}
return(data);
}
/// <summary>
/// Gets script data
/// </summary>
/// <param name="id">script id</param>
/// <returns>DetailScriptData object</returns>
public static DetailScriptData GetDetailScriptData(string id)
{
DetailScriptData data = new DetailScriptData();
using (DbConnection connection = new OracleConnection(WebSettings.GetConnectionString()))
{
connection.Open();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText =
"SELECT ILC_SCENARIO_ID, NAME, SCRIPTING_EXPRESSION FROM " +
schemaName + ".ILC_SCENARIO WHERE ILC_SCENARIO_ID =:PARAM_SCENARIO_ID";
AddInputParameter(cmd, "PARAM_SCENARIO_ID", id);
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
data.scriptId = Convert.ToString(reader[0]);
data.name = Convert.ToString(reader[1]);
data.scriptingExspression = Convert.ToString(reader[2]);
}
}
}
}
return(data);
}
/// <summary>
/// Gets list of projects
/// </summary>
/// <param name="serverId">server id</param>
/// <returns>projects list</returns>
public static IList <KeyValuePair <string, string> > GetProjectsList(string serverId)
{
List <KeyValuePair <string, string> > list = new List <KeyValuePair <string, string> >();
using (DbConnection connection = new OracleConnection(WebSettings.GetConnectionString()))
{
connection.Open();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText =
"SELECT IVR_PROJECT_ID, NAME FROM " + schemaName +
".IVR_PROJECT WHERE IVR_SERVER_ID =:PARAM_IVR_SERVER_ID";
AddInputParameter(cmd, "PARAM_IVR_SERVER_ID", serverId);
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(new KeyValuePair <string, string>(Convert.ToString(reader[0]), reader[1] as string));
}
}
}
}
return(list);
}
/// <summary>
/// Gets project data
/// </summary>
/// <param name="id">project id</param>
/// <returns>DetailProjectData object</returns>
public static DetailProjectData GetDetailProjectData(string id)
{
DetailProjectData data = new DetailProjectData();
using (DbConnection connection = new OracleConnection(WebSettings.GetConnectionString()))
{
connection.Open();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText =
"SELECT IVR_PROJECT_ID, IVR_SERVER_ID, NAME, EMAIL_ADDRESSES, ENABLED," +
"SCHEDULE_CRON FROM " + schemaName +
".IVR_PROJECT WHERE IVR_PROJECT_ID =:PARAM_IVR_PROJECT_ID";
AddInputParameter(cmd, "PARAM_IVR_PROJECT_ID", id);
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
data.projectId = Convert.ToString(reader[0]);
data.serverId = Convert.ToString(reader[1]);
data.name = Convert.ToString(reader[2]);
data.emails = Convert.ToString(reader[3]);
data.enabled = Convert.ToString(reader[4]);
data.cron = Convert.ToString(reader[5]);
}
}
}
}
return(data);
}
/// <summary>
/// Gets item data
/// </summary>
/// <param name="id">item id</param>
/// <returns>DetailItemData object</returns>
public static DetailItemData GetDetailItemData(string id)
{
DetailItemData data = new DetailItemData();
using (DbConnection connection = new OracleConnection(WebSettings.GetConnectionString()))
{
connection.Open();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText =
"SELECT ITEM_ID, TNUMBER, IVR_PROJECT_ID, USERID, PWD, ENABLED FROM " +
schemaName + ".SERVICE_INFO WHERE ITEM_ID=:PARAM_ITEM_ID";
AddInputParameter(cmd, "PARAM_ITEM_ID", id);
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
data.itemId = Convert.ToString(reader[0]);
data.phoneNumber = Convert.ToString(reader[1]);
data.projectId = Convert.ToString(reader[2]);
data.login = Convert.ToString(reader[3]);
data.password = Convert.ToString(reader[4]);
data.enabled = Convert.ToString(reader[5]);
}
}
}
}
return(data);
}
private static void QuickCheck()
{
try
{
ServiceController serviceController = new ServiceController(WebSettings.GetServiceName(),
WebSettings.GetServiceLocation());
serviceController.ExecuteCommand((int)ServiceCustomCommand.StartCheck);
}
catch (Exception ex)
{
new ExceptionLogger().HandleException(ex);
}
}
private static void StartService()
{
try
{
ServiceController serviceController = new ServiceController(WebSettings.GetServiceName(),
WebSettings.GetServiceLocation());
if (serviceController.Status == ServiceControllerStatus.Stopped)
{
serviceController.Start();
}
}
catch (Exception ex)
{
new ExceptionLogger().HandleException(ex);
}
}
/// <summary>
/// Check whether script name exist
/// </summary>
/// <param name="name">script name</param>
/// <param name="id">script id</param>
/// <returns>true if name exists</returns>
public static bool IsScriptNameExist(string name, string id)
{
using (DbConnection connection = new OracleConnection(WebSettings.GetConnectionString()))
{
connection.Open();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT NAME FROM " + schemaName + ".ILC_SCENARIO " +
"WHERE UPPER(NAME) = :NAME AND " + schemaName + ".ILC_SCENARIO.ILC_SCENARIO_ID != :SCENARIO_ID";
AddInputParameter(cmd, "NAME", name.ToUpper());
AddInputParameter(cmd, "SCENARIO_ID", id);
return(cmd.ExecuteScalar() != null);
}
}
}
private static DbConnection GetConnection(IPrincipal user)
{
DbConnection connection = new OracleConnection(WebSettings.GetConnectionString());
try
{
connection.Open();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandText = schemaName + ".SET_ILC_CTX";
cmd.CommandType = CommandType.StoredProcedure;
AddInputParameter(cmd, "p_ilc_user", user.Identity.Name);
cmd.ExecuteNonQuery();
}
return(connection);
}
catch (Exception)
{
connection.Dispose();
throw;
}
}
/// <summary>
/// Gets ILC settings data
/// </summary>
/// <returns>IlcSettingsData object</returns>
public static IlcSettingsData GetIlcSettingsData()
{
IlcSettingsData data = new IlcSettingsData();
using (DbConnection connection = new OracleConnection(WebSettings.GetConnectionString()))
{
connection.Open();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText =
"SELECT SCHEDULE_CRON,OUT_CHANNEL_NUM, TIMESPAN,COMMON_RECIPIENT_EMAIL," +
"TIME_BETWEEN_VERIFICATION, MAX_ATTEMPTS, FROM_EMAIL," +
"SUMMARY_REPORT_CRON,SUMMARY_RECIPIENT_EMAIL, JOB_MISFIRE_THRESHOLD FROM " +
schemaName + ".ILC_INSTANCE";
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
data.scheduleCron = Convert.ToString(reader[0]);
data.outgoingChannels = Convert.ToString(reader[1]);
data.timeSpan = Convert.ToString(reader[2]);
data.commonRecipientEmailAddresses = Convert.ToString(reader[3]);
data.timeSpanBetweenVerifications = Convert.ToString(reader[4]);
data.attemptsMaxNumber = Convert.ToString(reader[5]);
data.emailFromAddress = Convert.ToString(reader[6]);
data.summaryReportCron = Convert.ToString(reader[7]);
data.summaryRecipientEmail = Convert.ToString(reader[8]);
data.jobMisfireThreshold = Convert.ToString(reader[9]);
}
}
}
}
return(data);
}
/// <summary>
/// Gets list of servers
/// </summary>
/// <returns>servers list</returns>
public static IList <KeyValuePair <string, string> > GetServersList()
{
List <KeyValuePair <string, string> > list = new List <KeyValuePair <string, string> >();
using (DbConnection connection = new OracleConnection(WebSettings.GetConnectionString()))
{
connection.Open();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT IVR_SERVER_ID, NAME FROM " + schemaName + ".IVR_SERVER";
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(new KeyValuePair <string, string>(Convert.ToString(reader[0]), reader[1] as string));
}
}
}
}
return(list);
}
/// <summary>
/// Gets service status
/// </summary>
/// <returns>service status string</returns>
public static string GetServiceStatus()
{
try
{
ServiceController serviceController = new ServiceController(WebSettings.GetServiceName(),
WebSettings.GetServiceLocation());
switch (serviceController.Status)
{
case ServiceControllerStatus.Running:
return(ConstExpressions.SERVICE_STATUS_STARTED);
case ServiceControllerStatus.Stopped:
return(ConstExpressions.SERVICE_STATUS_STOPPED);
default:
return(ConstExpressions.SERVICE_STATUS_PAUSED);
}
}
catch (Exception ex)
{
return(((ex.InnerException != null) ? ex.InnerException.Message + ". " : "") + ex.Message);
}
}
/// <summary>
/// Gets data for summary report
/// </summary>
/// <returns>collection of report items</returns>
/// <param name="startDate">Reporting period start date and time</param>
/// <param name="endDate">Reporting period end date and time</param>
/// <param name="queryParams">report query parameters</param>
public static IList <SummaryReportItem> GetSummaryReportData(
DateTime startDate,
DateTime endDate,
List <KeyValuePair <string, object> > queryParams)
{
using (DbConnection connection = new OracleConnection(WebSettings.GetConnectionString()))
{
connection.Open();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
AddInputParameter(cmd, "startDate", startDate);
AddInputParameter(cmd, "endDate", endDate);
StringBuilder condition = new StringBuilder();
int paramCounter = 0;
foreach (KeyValuePair <string, object> kvp in queryParams)
{
paramCounter++;
condition.Append(" AND ").Append(kvp.Key).Append(" = :").Append(paramCounter);
AddInputParameter(cmd, paramCounter.ToString(), kvp.Value);
}
cmd.CommandText = "SELECT IVR_PROJECT.NAME," +
"IVR_SERVER.NAME, SERVICE_INFO.TNUMBER, SERVICE_INFO.USERID," +
"COUNT(1), SUM (DECODE(STATUS, 'SUCCEEDED', 0, 1)) AS FAILED_CNT," +
"MAX (DECODE (STATUS, 'SUCCEEDED', TIME, TO_DATE(NULL)))," +
"MAX (DECODE (STATUS, 'SUCCEEDED', TO_DATE(NULL), TIME)) FROM " +
schemaName + ".SERVICE_VERIFICATION INNER JOIN " + schemaName + ".SERVICE_INFO ON " +
"SERVICE_VERIFICATION.ITEM_ID = SERVICE_INFO.ITEM_ID INNER JOIN " + schemaName +
".IVR_PROJECT ON SERVICE_INFO.IVR_PROJECT_ID = IVR_PROJECT.IVR_PROJECT_ID " +
"INNER JOIN " + schemaName + ".IVR_SERVER ON " +
"IVR_PROJECT.IVR_SERVER_ID = IVR_SERVER.IVR_SERVER_ID " +
"WHERE TIME >= :startDate AND TIME < :endDate" +
condition +
" GROUP BY IVR_PROJECT.NAME, IVR_SERVER.NAME, TNUMBER, USERID " +
"ORDER BY FAILED_CNT DESC";
using (DbDataReader reader = cmd.ExecuteReader())
{
IList <SummaryReportItem> reportData = new List <SummaryReportItem>();
while (reader.Read())
{
SummaryReportItem item = new SummaryReportItem();
item.projectName = reader[0] as string;
item.serverName = reader[1] as string;
item.phone = reader[2] as string;
item.userId = reader[3] as string;
item.checksCount = Convert.ToInt32(reader[4]);
item.failedChecksCount = Convert.ToInt32(reader[5]);
item.lastSuccessDate = reader[6] as DateTime?;
item.lastFailedDate = reader[7] as DateTime?;
reportData.Add(item);
}
return(reportData);
}
}
}
}
