public Guid InsertOrUpdateTextTemplate(TextTemplateDTO templateDTO)
{
string spName = "apt_code.SmsAppointmentService.createUpdateSMSText";
try
{
using (var connection = m_providerFactory.CreateConnection())
{
var oracleParameters = TextTemplateQueryParamsHelper.GetTextTemplateSaveUpdateQueryParams(templateDTO);
var newGuid = SaveAndReturnPK(spName, "l_SMSTEXTID", oracleParameters, connection);
return newGuid;
}
}
catch (Oracle.ManagedDataAccess.Client.OracleException ex)
{
s_log.ErrorException("InsertOrUpdateTextTemplate (Error from Oracle)", ex);
throw new DBOperationException(ex.Message, ex.Number, DBExceptionScenarios.OracleExceptionOccured, ex);
}
catch (DBOperationException e)
{
s_log.WarnException("Error Occured in InsertOrUpdateTextTemplate", e);
throw;
}
catch (Exception ex)
{
s_log.ErrorException("InsertOrUpdateTextTemplate Fails", ex);
throw new DBOperationException(ex.Message, DBExceptionScenarios.ExceptionOccured, ex);
}
}
public List<TextTemplateDTO> GetGetTextTemplateByHospitalLevel_OPD(Guid scheduleId, long hospitalId, long opdId, long? locationId, List<int> contactTypes, List<int> officialLevelofcare)
{
string spName = "apt_code.SmsAppointmentService.getEnhancedSearchByHospitalOPD";
try
{
using (var connection = m_providerFactory.CreateConnection())
{
var oracleParameters = TextTemplateQueryParamsHelper.GetFiletrByHospitalOPDLevelQueryParams(scheduleId, hospitalId,opdId ,locationId, contactTypes, officialLevelofcare);
var selectedSmsTextList = connection.Query(spName, oracleParameters, commandType: CommandType.StoredProcedure);
if (selectedSmsTextList == null)
{
s_log.Error("Filter SMSText Template by Hospital and OPD , Set returned null result.");
throw new DBOperationException("Filter By Hospital level and OPD, SMSText Template Set returned null result.", GlobalOptions.DBExceptionScenarios.DBReturnedEmptyOrNullDataSet);
}
var smsTextList = new List<TextTemplateDTO>();
foreach (var result in selectedSmsTextList)
{
var resultDictionary = result as IDictionary<string, object>;
smsTextList.Add(MapToTextTemplateDTO(resultDictionary));
}
return smsTextList;
}
}
catch (Oracle.ManagedDataAccess.Client.OracleException ex)
{
s_log.ErrorException("Search text template Fails(Error from Oracle)", ex);
throw new DBOperationException(ex.Message, ex.Number, DBExceptionScenarios.OracleExceptionOccured, ex);
}
catch (DBOperationException e)
{
s_log.WarnException("Error Occured in Search text template", e);
throw;
}
catch (Exception ex)
{
s_log.ErrorException("Search text template Fails", ex);
throw new DBOperationException(ex.Message, DBExceptionScenarios.ExceptionOccured, ex);
}
}
public List<TextTemplateDTO> SelectTextTemplateOn(long? departmetnID, long? opdID, long? sectionID, long? wardID, string searchTerm, bool isActive, bool isHospitalOnly, long hospitalId)
{
string spName = "apt_code.SmsAppointmentService.searchSMSText";
try
{
using (var connection = m_providerFactory.CreateConnection())
{
var oracleParameters = TextTemplateQueryParamsHelper.GetTextTemplateSearchQueryParams(departmetnID, opdID, sectionID, wardID, searchTerm, isActive, isHospitalOnly, hospitalId);
var resultSet = connection.Query(spName, oracleParameters, commandType: CommandType.StoredProcedure);
if (resultSet == null)
{
s_log.Error("Search SMSText Template Set returned null result.");
throw new DBOperationException("Search SMSText Template Set returned null result.", GlobalOptions.DBExceptionScenarios.DBReturnedEmptyOrNullDataSet);
}
var smsTextList = new List<TextTemplateDTO>();
foreach (var result in resultSet)
{
var resultDictionary = result as IDictionary<string, object>;
smsTextList.Add(MapToTextTemplateDTO(resultDictionary));
}
return smsTextList;
}
}
catch (Oracle.ManagedDataAccess.Client.OracleException ex)
{
s_log.ErrorException("Search text template Fails(Error from Oracle)", ex);
throw new DBOperationException(ex.Message, ex.Number, DBExceptionScenarios.OracleExceptionOccured, ex);
}
catch (DBOperationException e)
{
s_log.WarnException("Error Occured in Search text template", e);
throw;
}
catch (Exception ex)
{
s_log.ErrorException("Search text template Fails", ex);
throw new DBOperationException(ex.Message, DBExceptionScenarios.ExceptionOccured, ex);
}
}
public TextTemplateDTO SelectTextTemplateById(Guid templateGuid)
{
string spName = "apt_code.SmsAppointmentService.getSMSTextByGuid";
try
{
using (var connection = m_providerFactory.CreateConnection())
{
var oracleParameters = TextTemplateQueryParamsHelper.GetTextTemplateBYIdQueryParams(templateGuid);
var resultSet = connection.Query(spName, oracleParameters, commandType: CommandType.StoredProcedure);
if (resultSet == null)
{
s_log.Error($"GetSMSTextByGuid sp returned null result. No entry exist for template id - {templateGuid}");
throw new DBOperationException("Get SMStextTemplate By Id returned null result.", GlobalOptions.DBExceptionScenarios.DBReturnedEmptyOrNullDataSet);
}
var smsTextList = new List<TextTemplateDTO>();
foreach (var result in resultSet)
{
var resultDictionary = result as IDictionary<string, object>;
smsTextList.Add(MapToTextTemplateDTOGetOne(resultDictionary));
}
return smsTextList.FirstOrDefault();
}
}
catch (Oracle.ManagedDataAccess.Client.OracleException ex)
{
s_log.ErrorException("Get SMStextTemplate By Id Fails(Error from Oracle)", ex);
throw new DBOperationException(ex.Message, ex.Number, DBExceptionScenarios.OracleExceptionOccured, ex);
}
catch (DBOperationException e)
{
s_log.WarnException("Error Occured in Get SMStextTemplate By Id", e);
throw;
}
catch (Exception ex)
{
s_log.ErrorException("Get SMStextTemplate By Id Fails", ex);
throw new DBOperationException(ex.Message, DBExceptionScenarios.ExceptionOccured, ex);
}
}
public List<TextTemplateDTO> SelectFullTextTemplatesOn(bool isActive,long hospitalId)
{
string spName = "apt_code.SmsAppointmentService.getOverviewTree";
try
{
using (var connection = m_providerFactory.CreateConnection())
{
var oracleParameters = TextTemplateQueryParamsHelper.GetTextTemplateOverviewQueryParams(isActive,hospitalId);
var resultSet = connection.Query(spName, oracleParameters, commandType: CommandType.StoredProcedure);
if (resultSet == null)
{
s_log.Error("Get overview of text templates returned null result.");
throw new DBOperationException("Overview of all SMSText Templates Set returned null result.", GlobalOptions.DBExceptionScenarios.DBReturnedEmptyOrNullDataSet);
}
var smsTextList = new List<TextTemplateDTO>();
foreach (var result in resultSet)
{
var resultDictionary = result as IDictionary<string, object>;
smsTextList.Add(MapToTextTemplateDTO(resultDictionary));
}
return smsTextList;
}
}
catch (Oracle.ManagedDataAccess.Client.OracleException ex)
{
s_log.ErrorException("Get overview of text templates Fails(Error from Oracle)", ex);
throw new DBOperationException(ex.Message, ex.Number, DBExceptionScenarios.OracleExceptionOccured, ex);
}
catch (DBOperationException e)
{
s_log.WarnException("Error Occured in Get overview of text templates", e);
throw;
}
catch (Exception ex)
{
s_log.ErrorException("Get overview of text templates Fails", ex);
throw new DBOperationException(ex.Message, DBExceptionScenarios.ExceptionOccured, ex);
}
}
