public ActionResult GetRevertToPreviousChangeset()
{
SelectList changetsetTo = null;
try
{
HttpClient client = pharmixWebApiClient.InitializeClient(_apiBaseURI);
TraceService("_apiBaseURI " + _apiBaseURI + DateTime.Now);
HttpResponseMessage response = client.GetAsync("api/PharmixApi/GetRevertToPreviousChangeset?CurrentUserName="******"DmdChangeSetDetailID", "Description", 0);
}
catch (Exception ex)
{
TraceService("excetion " + ex.Message + DateTime.Now);;
}
return(Json(changetsetTo));
}
public void ExportDataToCSV(string tabID, string TotalCount, string PageId, int changetSetFrom, int changetSetTo, bool allItem, string BusinessUser, bool IsDownloadsqlQuery = true, string changetSetFromId = "", string changetSetToId = "")
{
if (!allItem && tabID != "MyBuniessChangeset")
{
tabID = "ToBeInserted";
}
string fileExtension = ".xlsx";
string fileName = string.Concat(PageId, fileExtension);
HttpClient client = pharmixWebApiClient.InitializeClient();
TraceService(client.Timeout.TotalMinutes.ToString());
HttpResponseMessage response = client.GetAsync("api/PharmixApi/ExportData?reportType=" + PageId + "&fileName=" + fileName + "&rootFolder=" + ConfigurationManager.AppSettings["ZipFilePath"].ToString() + "/Files" + "&changetSetFrom=" + changetSetFrom + "&changetSetTo=" + changetSetTo + "&tabID=" + tabID + "&changetSetFromId=" + changetSetFromId + "&changetSetToId=" + changetSetToId + "&IsDownloadsqlQuery=" + IsDownloadsqlQuery + "&BusinessEmail=" + BusinessUser).Result;
CustomReportModel customReportModel = new CustomReportModel();
if (response.IsSuccessStatusCode)
{
TraceService("Api call is done");
//TraceService("_connection " + _connection);
//TraceService("query " + query);
var result = response.Content.ReadAsStringAsync().Result;
customReportModel = JsonConvert.DeserializeObject <CustomReportModel>(result);
string zipName = String.Format("MyChangeset_{0}.zip", DateTime.Now.ToString("yyyy-MMM-dd-HHmmss"));
var filesCol = customReportModel.AllFilePathForMychangesetDetails.ToList();
// filesCol.Add("C:/Delete/MyBusinessChangesetDetails20190305170329804.sql");
using (var memoryStream = new MemoryStream())
{
using (var ziparchive = new ZipArchive(memoryStream, ZipArchiveMode.Create, true))
{
foreach (var item in filesCol)
{
FileInfo file = new FileInfo(item);
ziparchive.CreateEntryFromFile(file.FullName, file.Name);
file.Delete();
TraceService("Files List " + file.FullName + file.Name);
}
}
using (var fileStream = new FileStream(ConfigurationManager.AppSettings["ZipFilePath"].ToString() + zipName, FileMode.Create))
{
memoryStream.Seek(0, SeekOrigin.Begin);
memoryStream.CopyTo(fileStream);
}
EmailBody(BusinessUser, changetSetFromId, changetSetToId, zipName);
}
}
TraceService("ExportDataToCSV Job is ended at " + DateTime.Now);
}
public static CustomReportEntity ToNewEntity(this CustomReportModel model, int userId)
{
return(new CustomReportEntity
{
Name = model.Name,
UserId = userId,
ReportCategories = model.Categories.Select(x => new CustomReportCategoryEntity
{
CategoryId = x.CategoryId
}).ToList()
});
}
private static async Task ValidateModelCategories(CustomReportModel model, IUnitOfWork unitOfWork)
{
// Validate categories
var allCategories = await unitOfWork.CategoryRepository.ListAll();
var allValid = model.Categories.All(x => allCategories.Any(y => y.Id == x.CategoryId));
if (!allValid)
{
throw new NotFoundException(nameof(CategoryEntity));
}
}
public async Task <CustomReportModel> UpdateCustomReport(CustomReportModel model)
{
using var unitOfWork = _unitOfWork();
await ValidateModelCategories(model, unitOfWork);
var entity = await FindCustomReportWithCategories(model.Id, unitOfWork);
entity.UpdateWithModel(model);
await unitOfWork.SaveAsync();
// Reselect to get the categories included
entity = await FindCustomReportWithCategories(model.Id, unitOfWork);
return(entity.ToDto());
}
public async Task <CustomReportModel> CreateCustomReport(CustomReportModel model)
{
using var unitOfWork = _unitOfWork();
await ValidateModelCategories(model, unitOfWork);
var entity = model.ToNewEntity(_userIdProvider.GetCurrentUserId());
unitOfWork.CustomReportRepository.Insert(entity);
await unitOfWork.SaveAsync();
// Reselect to get the categories included
entity = await FindCustomReportWithCategories(entity.Id, unitOfWork);
return(entity.ToDto());
}
public IActionResult LoadDataUniversalSearchResult(string universalSearch)
{
string universalSearchID = HttpContext.Request.Query["universalSearch"].ToString();
var draw = HttpContext.Request.Form["draw"].FirstOrDefault();
// Skiping number of Rows count
var start = Request.Form["start"].FirstOrDefault();
// Paging Length 10,20
var length = Request.Form["length"].FirstOrDefault();
// Sort Column Name
var sortColumn = Request.Form["columns[" + Request.Form["order[0][column]"].FirstOrDefault() + "][name]"].FirstOrDefault();
// Sort Column Direction ( asc ,desc)
var sortColumnDirection = Request.Form["order[0][dir]"].FirstOrDefault();
// Search Value from (Search box)
var searchValue = Request.Form["search[value]"].FirstOrDefault();
//Paging Size (10,20,50,100)
int pageSize = length != null?Convert.ToInt32(length) : 0;
int skip = start != null?Convert.ToInt32(start) : 0;
int? recordsTotal = 0;
dynamic results = null;
if (!string.IsNullOrEmpty(universalSearch) && universalSearch.Substring(0, 1).Equals("#"))
{
universalSearch = universalSearch.Replace('#', '$');
}
HttpClient client = pharmixWebApiClient.InitializeClient(_apiBaseURI);
HttpResponseMessage response = client.GetAsync("api/PharmixApi/GetChangeWiseReport?pageSize=" + "&skip=" + "&searchValue=" + universalSearch + "&sortColumn=" + "&tabID=" + ViewBag.TabId + "&reportType=" + "UniversalSearch" + "&getTotal=" + true + "&CurrentUserName="******"#"))
{
recordsTotal = customReportModel.UniversalSearchResultDetails.Count;
}
return(Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = results }));
}
public string GenerateZipFiles(DataRow item)
{
TraceService("******************************** GenerateZipFiles is started " + DateTime.Now + " ******************************");
HttpClient client = pharmixWebApiClient.InitializeClient();
client.Timeout = TimeSpan.FromMinutes(30);
HttpResponseMessage response = client.GetAsync("api/PharmixApi/ExportData?reportType=" + string.Empty + "&fileName=" + string.Empty + "&rootFolder=" + ConfigurationManager.AppSettings["RootFolder"].ToString() + "&changetSetFrom=" + item[2] + "&changetSetTo=" + item[3] + "&tabID=" + "MyBuniessChangeset" + "&changetSetFromId=" + item[2] + "&changetSetToId=" + item[3] + "&IsDownloadsqlQuery=true" + "&BusinessEmail=" + item[1]).Result;
CustomReportModel customReportModel = new CustomReportModel();
string zipName = String.Format("MyChangeset_{0}.zip", DateTime.Now.ToString("yyyy-MMM-dd-HHmmss"));
string fullFilePath = ConfigurationManager.AppSettings["RootFolder"].ToString() + zipName;
if (response.IsSuccessStatusCode)
{
var result = response.Content.ReadAsStringAsync().Result;
customReportModel = JsonConvert.DeserializeObject <CustomReportModel>(result);
List <string> filesCol = customReportModel.AllFilePathForMychangesetDetails.ToList();
using (var memoryStream = new MemoryStream())
{
using (var ziparchive = new ZipArchive(memoryStream, ZipArchiveMode.Create, true))
{
foreach (var filePath in filesCol)
{
FileInfo file = new FileInfo(filePath);
ziparchive.CreateEntryFromFile(file.FullName, file.Name);
file.Delete();
}
}
using (var fileStream = new FileStream(fullFilePath, FileMode.Create))
{
memoryStream.Seek(0, SeekOrigin.Begin);
memoryStream.CopyTo(fileStream);
}
}
}
TraceService("******************************** GenerateZipFiles is completed " + "File Path " + zipName + " " + DateTime.Now + " ******************************");
return(zipName);
}
public static CustomReportEntity UpdateWithModel(this CustomReportEntity entity, CustomReportModel model)
{
entity.Name = model.Name;
entity.ReportCategories = model.Categories.Select(x => new CustomReportCategoryEntity
{
CategoryId = x.CategoryId
}).ToList();
return(entity);
}
public IActionResult ExportDataToCSV(string tabID, string TotalCount, string PageId, int changetSetFrom, int changetSetTo, bool allItem, string BusinessUser, bool IsDownloadsqlQuery = true, string changetSetFromId = "", string changetSetToId = "")
{
if (!allItem && tabID != "MyBuniessChangeset")
{
tabID = "ToBeInserted";
}
string rootFolder = _hostingEnvironment.WebRootPath + "/Download/";
string fileExtension = ".xlsx";
fileName = string.Concat(PageId, fileExtension);
if (tabID != "MyBuniessChangeset" && tabID != "AllItems")
{
HttpClient client = pharmixWebApiClient.InitializeClient(_apiBaseURI);
TraceService("Api Call Started " + System.DateTime.Now.ToString());
TraceService(client.Timeout.TotalMinutes.ToString());
HttpResponseMessage response = client.GetAsync("api/PharmixApi/ExportData?reportType=" + PageId + "&fileName=" + fileName + "&rootFolder=" + rootFolder + "&changetSetFrom=" + changetSetFrom + "&changetSetTo=" + changetSetTo + "&tabID=" + tabID + "&changetSetFromId=" + changetSetFromId + "&changetSetToId=" + changetSetToId + "&IsDownloadsqlQuery=" + IsDownloadsqlQuery + "&BusinessEmail=" + BusinessUser).Result;
CustomReportModel customReportModel = new CustomReportModel();
TraceService("Api Call Ended " + System.DateTime.Now.ToString());
if (response.IsSuccessStatusCode)
{
//string query = "exec GetSQLQuery '',1,3,'C:/Delete/','*****@*****.**','MyBusinessChangesetDetails20190305170329804.sql'";
//ExecuteQuery(query, _connection);
TraceService("Api call is done");
//TraceService("_connection " + _connection);
//TraceService("query " + query);
var result = response.Content.ReadAsStringAsync().Result;
customReportModel = JsonConvert.DeserializeObject <CustomReportModel>(result);
if (tabID == "MyBuniessChangeset")
{
string zipName = String.Format("MyChangeset_{0}.zip", DateTime.Now.ToString("yyyy-MMM-dd-HHmmss"));
var filesCol = customReportModel.AllFilePathForMychangesetDetails.ToList();
// filesCol.Add("C:/Delete/MyBusinessChangesetDetails20190305170329804.sql");
using (var memoryStream = new MemoryStream())
{
using (var ziparchive = new ZipArchive(memoryStream, ZipArchiveMode.Create, true))
{
foreach (var item in filesCol)
{
FileInfo file = new FileInfo(item);
ziparchive.CreateEntryFromFile(file.FullName, file.Name);
file.Delete();
TraceService("Files List " + file.FullName + file.Name);
}
}
TraceService("Date 2 " + System.DateTime.Now.ToString());
TraceService("zipName " + zipName);
return(File(memoryStream.ToArray(), "application/zip", zipName));
}
}
else
{
FileInfo file = new FileInfo(customReportModel.AllFilePathForMychangesetDetails.FirstOrDefault());
TempData["filename"] = file.Name;
TempData.Keep("filename");
}
}
if (!string.IsNullOrEmpty(PageId) && (IsDownloadsqlQuery))
{
rootFolder = _SQLQueryPath;
}
}
else
{
HttpClient client = pharmixWebApiClient.InitializeClient(_apiBaseURI);
TraceService("Api Call Started " + System.DateTime.Now.ToString());
TraceService(client.Timeout.TotalMinutes.ToString());
HttpResponseMessage response = client.GetAsync("api/PharmixApi/ToSaveExportDataToCSVDetails?reportType=" + PageId + "&fileName=" + fileName + "&rootFolder=" + rootFolder + "&changetSetFrom=" + changetSetFrom + "&changetSetTo=" + changetSetTo + "&tabID=" + tabID + "&changetSetFromId=" + changetSetFromId + "&changetSetToId=" + changetSetToId + "&IsDownloadsqlQuery=" + IsDownloadsqlQuery + "&BusinessEmail=" + BusinessUser).Result;
return(new JsonResult("Message"));
}
return(downloadFile(rootFolder));
}
public IActionResult LoadData(string tabID, string TotalCount, string PageId, int changetSetFrom, int changetSetTo, string SaveData, string universalSearch)
{
var draw = HttpContext.Request.Form["draw"].FirstOrDefault();
// Skiping number of Rows count
var start = Request.Form["start"].FirstOrDefault();
// Paging Length 10,20
var length = Request.Form["length"].FirstOrDefault();
// Sort Column Name
var sortColumn = Request.Form["columns[" + Request.Form["order[0][column]"].FirstOrDefault() + "][name]"].FirstOrDefault();
// Sort Column Direction ( asc ,desc)
var sortColumnDirection = Request.Form["order[0][dir]"].FirstOrDefault();
// Search Value from (Search box)
var searchValue = Request.Form["search[value]"].FirstOrDefault();
if (!string.IsNullOrEmpty(universalSearch) && universalSearch != "/")
{
if (universalSearch.Substring(0, 1).Equals("$"))
{
searchValue = universalSearch.Replace('$', '#');
}
else
{
searchValue = universalSearch;
}
}
//Paging Size (10,20,50,100)
int pageSize = length != null?Convert.ToInt32(length) : 0;
int skip = start != null?Convert.ToInt32(start) : 0;
int? recordsTotal = 0;
dynamic results = null;
bool isGetTotal = false;
if (Convert.ToInt32(start) == 0)
{
isGetTotal = true;
}
if (!string.IsNullOrEmpty(searchValue) && searchValue.Substring(0, 1).Equals("#"))
{
searchValue = searchValue.Replace('#', '$');
}
//if (!string.IsNullOrEmpty(universalSearch) && !searchValue.Substring(0, 1).Equals("#"))
//{
// PageId = "AllItems";
//}
HttpClient client = pharmixWebApiClient.InitializeClient(_apiBaseURI);
HttpResponseMessage response = client.GetAsync("api/PharmixApi/GetChangeWiseReport?pageSize=" + pageSize + "&skip=" + skip + "&searchValue=" + searchValue + "&sortColumn=" + sortColumn + "&tabID=" + tabID + "&reportType=" + PageId + "&getTotal=" + isGetTotal + "&changetSetFrom=" + changetSetFrom + "&changetSetTo=" + changetSetTo + "&CurrentUserName="******"&SaveData=" + SaveData + "&sortColumnDirection=" + sortColumnDirection).Result;
CustomReportModel customReportModel = new CustomReportModel();
//Checking the response is successful or not which is sent using HttpClient
if (response.IsSuccessStatusCode)
{
//Storing the response details recieved from web api
var result = response.Content.ReadAsStringAsync().Result;
customReportModel = JsonConvert.DeserializeObject <CustomReportModel>(result);
if (isGetTotal)
{
recordsTotal = customReportModel.totalCount;
}
else
{
recordsTotal = customReportModel.totalCount ?? Convert.ToInt32(TotalCount);
// recordsTotal = Convert.ToInt32(TotalCount);
}
if (tabID == "ToBeInserted")
{
TempData["ToBeInserted"] = recordsTotal;
}
if (tabID == "ToBeUpdated")
{
@ViewBag.ToBeUpdated = recordsTotal;
}
if (tabID == "ToBeDeleted")
{
@ViewBag.ToBeDeleted = recordsTotal;
}
if (PageId == "AMPDetails")
{
results = customReportModel.DmdAmpHistory;
}
if (PageId == "AMPPDetails")
{
results = customReportModel.DmdAmppHistory;
}
if (PageId == "VMPPDetails")
{
results = customReportModel.DmdVmppHistory;
}
if (PageId == "VMPDetails")
{
results = customReportModel.DmdVmpHistory;
}
if (PageId == "VTMDetails")
{
results = customReportModel.DmdVtmHistory;
}
if (PageId == "RouteDetails")
{
results = customReportModel.DmdROUTEHistory;
}
if (PageId == "SupplierDetails")
{
results = customReportModel.DmdSUPPLIERHistory;
}
if (PageId == "FormDetails")
{
results = customReportModel.DmdFormHistory;
}
if (PageId == "GtinDetails")
{
results = customReportModel.DmdGtinHistory;
}
if (PageId == "DMDCollatedData")
{
results = customReportModel.DmdReferenceCombinedDataset;
}
if (PageId == "AdminDetails")
{
results = customReportModel.DmdChangeSetDetails;
}
if (PageId == "MyBusinessChangesetDetails")
{
results = customReportModel.DmdBusinessChangeSetDetails;
}
return(Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = results }));
}
return(View());
}
public IActionResult Index(string universalSearch = "", string universalPageId = "")
{
string rootFolder = _hostingEnvironment.WebRootPath + "/Download/";
string pageID = HttpContext.Request.Query["PageId"].ToString();
string universalSearchID = HttpContext.Request.Query["universalSearch"].ToString();
ViewBag.TabId = HttpContext.Request.Query["TabId"].ToString();
pageID = !string.IsNullOrEmpty(pageID) ? pageID : universalPageId;
if (!string.IsNullOrEmpty(universalSearch) && universalSearch.Substring(0, 1).Equals("#"))
{
universalSearch = universalSearch.Replace('#', '$');
}
if (!string.IsNullOrEmpty(universalSearch))
{
ViewBag.TabId = "AllItems";
}
HttpClient client = pharmixWebApiClient.InitializeClient(_apiBaseURI);
HttpResponseMessage response = client.GetAsync("api/PharmixApi/GetChangeWiseReport?pageSize=" + "&skip=" + "&searchValue=" + universalSearch + "&sortColumn=" + "&tabID=" + ViewBag.TabId + "&reportType=" + pageID + "&getTotal=" + true + "&CurrentUserName="******"$"))
{
ViewBag.PageId = "UniversalSearchResults";
ViewBag.UniversalSearch = customReportModel.UniversalSearchResultDetails;
ViewBag.UniversalSearchID = customReportModel.UniversalSearchResultDetails.FirstOrDefault().UniversalSearch;
customReportModel.SearchValue = customReportModel.UniversalSearchResultDetails.FirstOrDefault().UniversalSearch;
}
else
{
ViewBag.PageId = customReportModel.PageId;
}
if (!string.IsNullOrEmpty(universalSearch) && universalSearch.Substring(0, 1).Equals("$"))
{
universalSearch = universalSearch.Replace('$', '#');
customReportModel.SearchValue = universalSearch.Replace("#", "$");
}
else
{
customReportModel.SearchValue = universalSearch;
}
ViewBag.SearchValue = universalSearch;
if (customReportModel.UniversalSearchResultDetails != null && customReportModel.UniversalSearchResultDetails.Count > 0)
{
ViewBag.SearchValue = universalSearch;
}
if (ViewBag.PageId != "AdminDetails")
{
if (customReportModel.DmdChangeSetDetails != null && customReportModel.DmdChangeSetDetails.Count > 1)
{
customReportModel.DmdChangeSetDetails.RemoveAt(1);
customReportModel.DmdChangeSetDetails.Insert(1, new PharmixWebApi.Model.Dmd_ChangeSetDetails {
Description = "Initial", DmdChangeSetDetailID = 1
});
}
if (customReportModel.MyChangesetId > 0 && ViewBag.PageId == "MyBusinessChangesetDetails")
{
customReportModel.DmdChangeSetDetails = customReportModel.DmdChangeSetDetails.Where(x => x.DmdChangeSetDetailID >= customReportModel.MyChangesetId).ToList();
}
}
ViewBag.ChangesetName = customReportModel.MyChangeset;
TempData["ChangesetName"] = customReportModel.MyChangeset;
TempData.Keep("ChangesetName");
}
if (ViewBag.PageId == null)
{
ViewBag.PageId = pageID;
}
if (string.IsNullOrEmpty(ViewBag.PageId) && string.IsNullOrEmpty(pageID))
{
ViewBag.PageId = "Index";
}
if (ViewBag.PageId == "UniversalSearch")
{
ViewBag.PageId = "UniversalSearchResults";
}
return(View(ViewBag.PageId, customReportModel));
}
public void MakeCustomReport(Window winref, DataSet ds, CustomReportModel reportdetails)
{
try
{
using (var xl = new ExcelPackage())
{
ExcelWorksheet ws;
ws = xl.Workbook.Worksheets.Add(reportdetails.Name);
int firstrow = 1;
int firstdatarow = firstrow + 1;
int lastdatarow = firstrow;
int dtcolumncount = 0;
int dtrowcount = 0;
int tblctr = 0;
foreach (DataTable dt in ds.Tables)
{
if (!reportdetails.CombineTables)
{
if (tblctr == 0)
{
ws.Name = dt.TableName;
}
else
{
ws = xl.Workbook.Worksheets.Add(dt.TableName);
}
}
tblctr++;
dtcolumncount = dt.Columns.Count;
dtrowcount = dt.Rows.Count;
for (int i = 0; i < dtrowcount; i++)
{
for (int j = 0; j < dtcolumncount; j++)
{
ws.Cells[firstrow + i + 1, j + 1].Value = dt.Rows[i][j];
}
}
FormatCustomTable(dt, ref ws, firstrow);
if (reportdetails.CombineTables)
{
firstrow = dtrowcount + firstrow + 2; //2 rows between tables
firstdatarow = firstrow + 1;
}
}
try
{
string filename = SaveAs(winref, reportdetails.Name);
if (!string.IsNullOrEmpty(filename))
{
xl.SaveAs(new System.IO.FileInfo(filename));
Process.Start(filename);
}
}
catch (Exception e)
{
IMessageBoxService msg = new MessageBoxService();
msg.ShowMessage("The file is already open.\nPlease close or select a different file name\n" + e.Message, "File already open", GenericMessageBoxButton.OK, GenericMessageBoxIcon.Exclamation);
msg = null;
}
}
}
catch
{
IMessageBoxService msg = new MessageBoxService();
msg.ShowMessage("An unexpected error has occurred in MakeCustomReport", "Error", GenericMessageBoxButton.OK, GenericMessageBoxIcon.Error);
msg = null;
}
}
