/// <summary>
/// Gets data for checking
/// </summary>
/// <param name="lineCheckerJob">LineCheckerJob instance</param>
/// <param name="actualScriptsVersion">scripts version</param>
/// <param name="rowId">row id</param>
/// <returns>true if data for checking was retrieved</returns>
public static bool GetCheckData(LineCheckerJob lineCheckerJob, out int actualScriptsVersion, out object rowId)
{
rowId = null;
bool retVal = false;
actualScriptsVersion = 0;
using (DbConnection connection = GetConnection())
{
connection.Open();
string strQuery =
"SELECT SERVICE_VERIFICATION.ROWID, TNUMBER, USERID, PWD, SERVICE_VERIFICATION.ITEM_ID, IVR_PROJECT.NAME, IVR_SERVER.NAME, DB_CONN, ATTEMPTS, IVR_SERVER.AUTH_CHECKER, " +
"NVL(SERVICE_INFO.ILC_SCENARIO_ID, NVL(IVR_PROJECT.ILC_SCENARIO_ID, NVL(IVR_SERVER.ILC_SCENARIO_ID, (SELECT ILC_SCENARIO_ID FROM " +
schemaName + ".ILC_INSTANCE)))), (SELECT SCRIPTS_VERSION FROM " +
schemaName + ".ILC_INSTANCE)" +
" 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 SERVICE_VERIFICATION.ROWID=" + schemaName + ".GET_WORKER_ID(:WORKER_NAME, :A_REPORT_ID, :MAX_ATTEMPTS, :TIME_SPAN)";
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandText = strQuery;
AddInputParameter(cmd, "WORKER_NAME", lineCheckerJob.WorkerName);
AddInputParameter(cmd, "A_REPORT_ID", lineCheckerJob.SessionId);
AddInputParameter(cmd, "MAX_ATTEMPTS", AppSettings.GetAttemptsMaxNumber());
AddInputParameter(cmd, "TIME_SPAN", AppSettings.GetTimeSpanInDays());
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
rowId = reader[0];
lineCheckerJob.Phone = reader[1] as string;
lineCheckerJob.Login = reader[2] as string;
lineCheckerJob.Password = reader[3] as string;
lineCheckerJob.ItemId = Convert.ToInt32(reader[4]);
lineCheckerJob.ProjectName = reader[5] as string;
lineCheckerJob.ServerName = reader[6] as string;
lineCheckerJob.AuthLoginDbConn = reader[7] as string;
lineCheckerJob.VerificationAttempts = Convert.ToInt32(reader[8]);
// Implement Authentication Checking by plug-ins
lineCheckerJob.AuthCheckerName = reader[9] as string;
lineCheckerJob.ScenarioId = Convert.ToInt32(reader[10]);
actualScriptsVersion = Convert.ToInt32(reader[11]);
retVal = true;
}
}
}
}
return(retVal);
}
/// <summary>
/// Initializes session
/// </summary>
/// <param name="lineCheckerJob">LineCheckerJob instance</param>
/// <param name="wasMisfired">Was session misfired</param>
/// <param name="prjIds">Collection of project ids</param>
/// <returns>true if session was initialized</returns>
public static bool InitializeSession(LineCheckerJob lineCheckerJob, bool wasMisfired, ICollection <int> prjIds)
{
StringBuilder projectIds = new StringBuilder(",");
foreach (int id in prjIds)
{
projectIds.Append(id).Append(',');
}
using (DbConnection connection = GetConnection())
{
connection.Open();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT " + schemaName + ".SQ_WORKER_ID.NEXTVAL FROM DUAL";
lineCheckerJob.WorkerName = "WORKER" + cmd.ExecuteScalar();
}
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandText = schemaName + ".INITIALIZE_CHECKLIST";
cmd.CommandType = CommandType.StoredProcedure;
AddInputParameter(cmd, "TIME_SPAN", lineCheckerJob.GetTimeSpanSinceLastVerificationInDays());
AddInputParameter(cmd, "WAS_MISFIRED", wasMisfired);
AddInputParameter(cmd, "PROJECTS_LIST", projectIds.ToString());
DbParameter outputparam = cmd.CreateParameter();
outputparam.Direction = ParameterDirection.Output;
outputparam.ParameterName = "A_REPORT_ID";
outputparam.DbType = DbType.Int32;
cmd.Parameters.Add(outputparam);
DbParameter outputparam2 = cmd.CreateParameter();
outputparam2.Direction = ParameterDirection.Output;
outputparam2.ParameterName = "A_START_TIME";
outputparam2.DbType = DbType.DateTime;
cmd.Parameters.Add(outputparam2);
DbParameter outputparam3 = cmd.CreateParameter();
outputparam3.Direction = ParameterDirection.Output;
outputparam3.ParameterName = "PREV_START_TIME";
outputparam3.DbType = DbType.DateTime;
cmd.Parameters.Add(outputparam3);
DbParameter param2 = cmd.CreateParameter();
param2.Direction = ParameterDirection.Output;
param2.DbType = DbType.Int32;
param2.ParameterName = "IS_NEW_SESSION";
cmd.Parameters.Add(param2);
cmd.ExecuteNonQuery();
lineCheckerJob.SessionId = (int)outputparam.Value;
lineCheckerJob.StartTime = (DateTime)outputparam2.Value;
lineCheckerJob.PreviousStartTime = outputparam3.Value as DateTime?;
return((int)param2.Value != 0);
}
}
}
