public static void executeSQLStatement(string sql, List <KeyValuePair <string, object> > parameters = null, string databaseName = "connectionString_SynapseDataStore", NpgsqlConnection existingCon = null)
{
NpgsqlConnection con = existingCon ?? new NpgsqlConnection(Environment.GetEnvironmentVariable(databaseName));
//using (con)
//{
if (existingCon == null)
{
con.Open();
}
// Insert some data
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = con;
cmd.CommandText = sql;
if (parameters.IsCollectionValid())
{
foreach (var param in parameters)
{
cmd.Parameters.AddWithValue(param.Key, param.Value);
}
}
cmd.ExecuteNonQuery();
}
//}
if (existingCon == null)
{
con.Close();
con.Dispose();
}
}
private List <FormularyTreeModel> ConvertToFormularyTreeModel(List <FormularyListAPIModel> apiModelList, Dictionary <string, string> recordStatusDictionary)
{
var treeList = new List <FormularyTreeModel>();
if (!apiModelList.IsCollectionValid())
{
return(treeList);
}
foreach (FormularyListAPIModel result in apiModelList)
{
var formularyTree = new FormularyTreeModel();
formularyTree.Data["Level"] = result.ProductType;
formularyTree.Key = result.Code;
formularyTree.Title = result.Name;
formularyTree.FormularyVersionId = result.FormularyVersionId;
formularyTree.Data["recordstatus"] = new FormaryLookupModel {
IsDuplicate = result.IsDuplicate, Code = result.RecStatusCode, Description = result.RecStatusCode.IsNotEmpty() ? recordStatusDictionary[result.RecStatusCode] : null
};
formularyTree.Lazy = true;
if (string.Compare(result.ProductType, "amp", true) == 0)
{
formularyTree.Children = new List <FormularyTreeModel>();
}
else if (result.Children.IsCollectionValid())
{
formularyTree.Children = ConvertToFormularyTreeModel(result.Children, recordStatusDictionary);
}
treeList.Add(formularyTree);
}
return(treeList);
}
public static async Task <TerminologyAPIResponse <ImportFormularyAPIModel> > ImportMeds(List <string> meds, string token, string formularyStatusCd = null, string recordStatusCd = null)
{
var url = $"{FormularyAPI_URI}/import";
var urlParams = new List <string>();
string urlParam = string.Empty;
if (formularyStatusCd.IsNotEmpty())
{
urlParams.Add($"formularyStatusCd={formularyStatusCd}");
}
if (recordStatusCd.IsNotEmpty())
{
urlParams.Add($"recordStatusCd={recordStatusCd}");
}
if (urlParams.IsCollectionValid())
{
urlParam = urlParams[0];
if (urlParams.Count > 1)
{
urlParam = string.Join("&", urlParams);
}
}
if (urlParam.IsNotEmpty())
{
url = $"{url}?{urlParam}";
}
var result = await InvokeService <ImportFormularyAPIModel>(url, token, HttpMethod.Post, meds);
return(result);
}
/// <summary>
/// Method to get all countries on focus.
/// </summary>
private void GetAllCountries()
{
Task.Run(() =>
{
List <LookUpEntity> countryList = new List <LookUpEntity>();
if (_mainVM.CountryList.IsCollectionValid())
{
countryList.AddRange(_mainVM.CountryList);
}
if (!countryList.IsCollectionValid())
{
return;
}
var autocompleteList = new List <AutoCompleteEntry>();
countryList.ForEach(keyWordItem =>
{
var entry = new AutoCompleteEntry(keyWordItem.Name, keyWordItem.Value);
autocompleteList.Add(entry);
});
CountryAutoComplete.AutoCompletionList = autocompleteList;
});
}
private async Task <List <FormularyListAPIModel> > InvokeSearchAPI(FormularyFilterCriteriaAPIModel filterCriteria, string token)
{
//If has any search criteria - hit search api
if (filterCriteria.searchTerm.IsNotEmpty() || filterCriteria.formularyStatusCd.IsCollectionValid() || filterCriteria.IncludeDeleted || filterCriteria.recStatusCds.IsCollectionValid() || filterCriteria.Flags.IsCollectionValid() || filterCriteria.hideArchived)
{
var recStsCodes = new List <string>();
if (filterCriteria.recStatusCds.IsCollectionValid())
{
recStsCodes = filterCriteria.recStatusCds;
}
//if (filterCriteria.hideArchived)
//{
// recStsCodes.Clear();
// recStsCodes.Add("004");
//}
if (recStsCodes.IsCollectionValid())
{
filterCriteria.recStatusCds = recStsCodes;
}
var formulariesResponse = await TerminologyAPIService.SearchFormularies(filterCriteria, token);
if (formulariesResponse.StatusCode != DataService.APIModel.StatusCode.Success)
{
string errors = "Error getting the Formularies data.";
if (formulariesResponse.ErrorMessages.IsCollectionValid())
{
errors += string.Join('\n', formulariesResponse.ErrorMessages.ToArray());
}
_toastNotification.AddErrorToastMessage(errors);
return(null);
}
return(formulariesResponse.Data?.data);
}
else
{
var formulariesResponse = await TerminologyAPIService.GetLatestTopLevelFormulariesBasicInfo(token);
if (formulariesResponse.StatusCode != DataService.APIModel.StatusCode.Success)
{
string errors = "Error getting the Formularies data.";
if (formulariesResponse.ErrorMessages.IsCollectionValid())
{
errors += string.Join('\n', formulariesResponse.ErrorMessages.ToArray());
}
_toastNotification.AddErrorToastMessage(errors);
return(null);
}
return(formulariesResponse.Data);
}
}
/// <summary>
/// Get city for the selected text
/// </summary>
/// <param name="searchText">The search text.</param>
private void GetCityForSearchText(string searchText)
{
try
{
if (searchText.IsNotNull() && !string.IsNullOrEmpty((searchText)))
{
var autoCompleteTask = Task.Run(() =>
{
List <LookUpEntity> cityList = new List <LookUpEntity>();
if (_mainVM.CityList.IsCollectionValid())
{
var cities = _mainVM.CityList.Where(x =>
(x.Name.StartsWith(searchText, StringComparison.OrdinalIgnoreCase)))
.Select(i => new { i.Name, i.Value }).ToList();
foreach (var item in cities)
{
cityList.Add(new LookUpEntity
{
Name = item.Name,
Value = item.Value
});
}
}
if (!cityList.IsCollectionValid())
{
return;
}
var autocompleteList = new List <AutoCompleteEntry>();
cityList.ForEach(keyWordItem =>
{
var entry = new AutoCompleteEntry(keyWordItem.Name, keyWordItem.Value);
autocompleteList.Add(entry);
});
CityAutoComplete.AutoCompletionList = autocompleteList;
});
autoCompleteTask.ContinueWith((t) =>
{
if (t.IsFaulted || t.Exception != null)
{
throw t.Exception.GetBaseException();
}
});
}
else
{
}
}
catch (Exception ex)
{
}
}
public static int GetBusinessDays(DateTime firstDay, DateTime lastDay, List <DateTime> holidays)
{
firstDay = firstDay.Date;
lastDay = lastDay.Date;
//if (firstDay > lastDay)
// throw new ArgumentException("Incorrect last day " + lastDay);
TimeSpan span = lastDay - firstDay;
int businessDays = span.Days + 1;
int fullWeekCount = businessDays / 7;
// find out if there are weekends during the time exceedng the full weeks
if (businessDays > fullWeekCount * 7)
{
// we are here to find out if there is a 1-day or 2-days weekend
// in the time interval remaining after subtracting the complete weeks
int firstDayOfWeek = (int)firstDay.DayOfWeek;
int lastDayOfWeek = (int)lastDay.DayOfWeek;
if (lastDayOfWeek < firstDayOfWeek)
{
lastDayOfWeek += 7;
}
if (firstDayOfWeek <= 6)
{
if (lastDayOfWeek >= 7)// Both Saturday and Sunday are in the remaining time interval
{
businessDays -= 2;
}
else if (lastDayOfWeek >= 6)// Only Saturday is in the remaining time interval
{
businessDays -= 1;
}
}
else if (firstDayOfWeek <= 7 && lastDayOfWeek >= 7)// Only Sunday is in the remaining time interval
{
businessDays -= 1;
}
}
// subtract the weekends during the full weeks in the interval
businessDays -= fullWeekCount + fullWeekCount;
if (holidays.IsCollectionValid())
{
// subtract the number of bank holidays during the time interval
foreach (DateTime bankHoliday in holidays)
{
DateTime bh = bankHoliday.Date;
if (firstDay <= bh && bh <= lastDay)
{
--businessDays;
}
}
}
return(businessDays);
}
protected List <string> ConvertValueToString(List <CodeNameSelectorModel> src)
{
if (!src.IsCollectionValid())
{
return(null);
}
return(src.Select(x => x.Id).ToList());
}
private List <CodeNameSelectorModel> ConvertLookupAPIListToCodeNameModelWithDefaults(List <FormularyLookupAPIModel> apiModel)
{
if (apiModel == null || !apiModel.IsCollectionValid())
{
return(null);
}
var codeNameList = apiModel.Select(rec => rec.ConvertToCodeNameModel((item) => item.Cd, item => item.Desc, item => item.Source)).ToList();
return(codeNameList);
}
public List <FormularyAdditionalCodeModel> Convert(List <FormularyAdditionalCodeAPIModel> sourceMember, ResolutionContext context)
{
if (sourceMember == null || !sourceMember.IsCollectionValid())
{
return(null);
}
var additonalCodesDTO = context.Mapper.Map <List <FormularyAdditionalCodeModel> >(sourceMember);
return(additonalCodesDTO.Where(ac => ac.CodeType.IsNotEmpty() && string.Compare(ac.CodeType, _codeType, true) == 0)?.ToList());
}
private void DisplayEmailPreview(List <Tuple <string, string> > emailData, EmailTemplateDTO emailTemplate)
{
if (emailData.IsCollectionValid())
{
var message = $"<b>To:</b> {emailTemplate.To.Aggregate((first, sec) => string.Format("{0}; {1}", first, sec))}<br>";
message += $"<b>CC:</b> <br>";
message += $"<b>BCC:</b> <br><br>";
message += $"<b>Subject:</b> {GetEmailSubject(emailTemplate)}<br><br>";
message += $"<b>Body:</b> <br>";
message += string.Join("<br>", emailData.Select(d => $"{d.Item1}: {d.Item2}"));
message = $"<br>{message}<br><br>";
X.Msg.Alert("Email preview", message).Show();
}
}
public static DataSet DataSetFromSQL(string sqlQueryString, List <KeyValuePair <string, object> > parameters = null, string databaseName = "connectionString_SynapseDataStore", NpgsqlConnection existingCon = null)
{
NpgsqlConnection con = existingCon ?? new NpgsqlConnection(Environment.GetEnvironmentVariable(databaseName));
DataSet ds = new DataSet();
//using (con)
//{
if (existingCon == null)
{
con.Open();
}
NpgsqlCommand cmd = new NpgsqlCommand();
NpgsqlDataAdapter da = new NpgsqlDataAdapter();
da.SelectCommand = cmd;
cmd.CommandText = sqlQueryString;
if (parameters.IsCollectionValid())
{
foreach (var param in parameters)
{
cmd.Parameters.AddWithValue(param.Key, param.Value);
}
}
da.SelectCommand.Connection = con;
da.Fill(ds);
if (existingCon == null)
{
con.Close();
if (cmd != null)
{
cmd.Dispose();
}
con.Dispose();
}
//}
return(ds);
}
private List <FormularyLookupAPIModel> ConvertCodeNameModelsWithDefaultsToCodeDescAPI(List <CodeNameSelectorModel> codeNameModel)
{
if (codeNameModel == null || !codeNameModel.IsCollectionValid())
{
return(null);
}
var codeNameList = codeNameModel.Select(rec =>
{
return(new FormularyLookupAPIModel
{
Cd = rec.Id,
Desc = rec.Name,
Source = rec.Source
});
}).ToList();
return(codeNameList);
}
/// <summary>
/// Method to get all cities on focus.
/// </summary>
private void GetAllCities()
{
Task.Run(() =>
{
List <LookUpEntity> cityList = new List <LookUpEntity>();
if (_mainVM.CityList.IsCollectionValid())
{
var cities = _mainVM.CityList.ToList();
foreach (var item in cities)
{
cityList.Add(new LookUpEntity
{
Name = item.Name,
Value = item.Value
});
}
}
if (!cityList.IsCollectionValid())
{
return;
}
var autocompleteList = new List <AutoCompleteEntry>();
cityList.ForEach(keyWordItem =>
{
var entry = new AutoCompleteEntry(keyWordItem.Name, keyWordItem.Value);
autocompleteList.Add(entry);
});
CityAutoComplete.AutoCompletionList = autocompleteList;
});
}
public static String ExcecuteNonQueryFromSQL(string sqlQueryString, List <KeyValuePair <string, object> > parameters = null, string databaseName = "connectionString_SynapseDataStore")
{
NpgsqlConnection con = new NpgsqlConnection(Environment.GetEnvironmentVariable(databaseName));
string retVal = "";
using (con)
{
NpgsqlCommand cmd = new NpgsqlCommand(sqlQueryString, con);
if (parameters.IsCollectionValid())
{
foreach (var param in parameters)
{
cmd.Parameters.AddWithValue(param.Key, param.Value);
}
}
con.Open();
cmd.ExecuteNonQuery();
}
return(retVal);
}
public void AddModuleInDepartment(List<ModuleInDepartment> moduleInDepartment)
{
using (var uow = new UnitOfWork())
{
if (moduleInDepartment.IsCollectionValid())
{
moduleInDepartment.Each(x =>
{
var moduleInDept = uow.GetRepository<ModuleInDepartment>().Items.Where(y => y.ModuleId == x.ModuleId).ToList();
if (!moduleInDept.IsCollectionValid())
uow.GetRepository<ModuleInDepartment>().Insert(x);
});
uow.SaveChanges();
}
}
}
public async Task <IActionResult> ExcelImport()
{
string token = HttpContext.Session.GetString("access_token");
int.TryParse(_configuration["SynapseCore:Settings:FileImportBatchSize"], out int batSizeForFileFromConfig);
var retries = 0;
var responseStringBuilder = new StringBuilder();
IFormFile xlFile = Request.Form.Files[0];
var colmsConfigured = GetColumnDetailsFromConfig;
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
var xlFileXtn = Path.GetExtension(xlFile.FileName).ToLower();
if (xlFileXtn != ".xls" && xlFileXtn != ".xlsx")
{
return(StatusCode((int)HttpStatusCode.NotAcceptable));
}
//var dirPath = CreateDirectory();
if (xlFile.Length > 0)
{
//string fullPath = Path.Combine(dirPath, xlFile.FileName);
//using (var stream = System.IO.File.Create(fullPath))
//{
// await xlFile.CopyToAsync(stream);
//}
var requests = new List <FormularyHeaderAPIModel>();
using (var reader = ExcelReaderFactory.CreateReader(xlFile.OpenReadStream()))
{
var result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true,
}
}).Tables[0];// get the first sheet data with index 0.
var rowData = GetRowData(colmsConfigured);
//var totalBatches = (result.Rows.Count) / 500;
//List<int> source = Enumerable.Range(1, 19).ToList();
//int batchsize = 10;
//List<List<int>> batches = new List<List<int>>();
//for (int i = 0; i < source.Count; i += batchsize)
//{
// var batch = source.Skip(i).Take(batchsize);
// batches.Add(batch.ToList());
//}
var batchsize = batSizeForFileFromConfig;
var batchedRequests = new List <List <FormularyHeaderAPIModel> >();
for (var rowIndex = 0; rowIndex < result.Rows.Count; rowIndex++)
{
var row = result.Rows[rowIndex];
if (!CanProcessRow(row, colmsConfigured))
{
continue;
}
var requestRow = new FormularyHeaderAPIModel
{
Detail = new FormularyDetailAPIModel(),
FormularyRouteDetails = new List <FormularyRouteDetailAPIModel>()
};
colmsConfigured.Keys.Each(k =>
{
if (rowData.ContainsKey(k))
{
rowData[k](row, requestRow);
}
});
requests.Add(requestRow);
}
for (var reqIndex = 0; reqIndex < requests.Count; reqIndex += batchsize)
{
var batches = requests.Skip(reqIndex).Take(batchsize);
batchedRequests.Add(batches.ToList());
}
//for (var rowIndex = 0; rowIndex < result.Rows.Count; rowIndex++)
//for (var rowIndex = 0; rowIndex < result.Rows.Count; rowIndex += batchsize)
//{
// var requests = new List<CreateFormularyAPIRequest>();
// var row = result.Rows[rowIndex];
// if (!CanProcessRow(row, colmsConfigured)) continue;
// var requestRow = new CreateFormularyAPIRequest
// {
// Detail = new CreateFormularyDetailAPIRequest(),
// FormularyRouteDetails = new List<CreateFormularyRouteDetailAPIRequest>()
// };
// colmsConfigured.Keys.Each(k =>
// {
// if (rowData.ContainsKey(k))
// rowData[k](row, requestRow);
// });
// requests.Add(requestRow);
// batchedRequests.Add(requests);
// //var response = await DataService.TerminologyAPIService.FileImportMedication(requests, token);
// //FormatResponseMessage(response, requestRow, responseStringBuilder);
//}
foreach (var batchReq in batchedRequests)
{
retries = 0;
try
{
var response = await DataService.TerminologyAPIService.FileImportMedication(batchReq, token);
FormatResponseMessageForBulk(response, responseStringBuilder);
await Task.Delay(200);
if (response.StatusCode == DataService.APIModel.StatusCode.Fail)
{
await RetryPosting(batchReq);
}
}
catch (Exception ex)
{
if (retries == 0)
{
await RetryPosting(batchReq);
}
else
{
throw ex;
}
}
}
}
async Task RetryPosting(List <FormularyHeaderAPIModel> batchReq)
{
if (retries >= 3)
{
throw new Exception("Exhausted re-tries during formulary file import");
}
try
{
retries++;
var response = await DataService.TerminologyAPIService.FileImportMedication(batchReq, token);
FormatResponseMessageForBulk(response, responseStringBuilder);
await Task.Delay(200);
if (response.StatusCode == DataService.APIModel.StatusCode.Fail)
{
await RetryPosting(batchReq);
}
}
catch (Exception ex)
{
if (retries <= 2)
{
await RetryPosting(batchReq);
}
else
{
throw ex;
}
}
}
var codesInExcel = new List <string>();
if (requests.IsCollectionValid())
{
codesInExcel = requests.Select(rec => rec.Code).ToList();
}
await UploadNonFormulariesInDMD(codesInExcel, token, responseStringBuilder);
await DataService.TerminologyAPIService.InvokePostImportProcess(token);
}
//return Ok("Uploaded successfully");
return(Json(responseStringBuilder.ToString()));
}
public List<Module> GetAllModule(long departmentId)
{
using (var uow = new UnitOfWork())
{
var moduleList = new List<Module>();
if (departmentId != 0)
{
moduleList = uow.GetRepository<Module>().Items.Where(x => x.DepartmentId == departmentId).ToList();
if (!moduleList.IsCollectionValid())
{
var modules = uow.GetRepository<Module>().Items.Where(x => x.DepartmentId == null).ToList();
foreach (var mod in modules)
{
moduleList.Add(mod);
}
}
}
return moduleList;
}
}
private void ValidateUserLoginDetails(List<UserLoginViewModel> userLoginViewModel)
{
if (userLoginViewModel.IsCollectionValid())
{
userLoginViewModel.ForEach(x =>
{
if (x.Password.IsNull() || x.Password.IsEmpty())
ModelState.AddModelError("Password", "Password is mandatory.");
if (x.SecurityAnswer.IsNull() || x.SecurityAnswer.IsEmpty())
ModelState.AddModelError("SecurityAnswer", "Security Answer is mandatory.");
});
}
}
