/// <summary>
/// Action method to Export data in Excel format.
/// </summary>
/// <returns>An action result where the data is being exported.</returns>
public async Task <IActionResult> ExportToExcelAsync()
{
// Gets the list of all Shift users.
var shiftResponse = await this.GetAllShiftUsersAsync().ConfigureAwait(false);
// Gets the list of all Kronos users.
var kronosResonse = await this.GetAllKronosUsersByQueryAsync().ConfigureAwait(false);
if (kronosResonse != null)
{
// Converting the users list to DataTable.
var exportDt = ConvertModelToDataTable(shiftResponse, kronosResonse);
string fileName = Resources.KronosShiftUserMappingXML;
// returning the excel file into browser as stream.
using (var stream = ExportImportHelper.ExportToExcel(exportDt))
{
return(this.File(stream.ToArray(), Resources.SpreadsheetContentType, fileName));
}
}
else
{
return(this.RedirectToAction("Index", "UserMapping").WithErrorMessage(Resources.ErrorNotificationHeaderText, Resources.WorkforceIntegrationNotRegister));
}
}
/// <summary>
/// Method to convert the model to DataTable.
/// </summary>
/// <param name="shiftList">List of Shifts users.</param>
/// <param name="kronosList">List of Kronos users.</param>
/// <returns>File to browser's response.</returns>
private static List <DataTable> ConvertModelToDataTable(List <ShiftUser> shiftList, List <KronosUserModel> kronosList)
{
DataTable shiftDt = new DataTable();
DataTable kronosDt = new DataTable();
try
{
// Setting table names.
string kronosDtName = "Kronos Users";
string shiftDtName = "Shift Users";
shiftDt = ExportImportHelper.ToDataTable(shiftList, shiftDtName);
kronosDt = ExportImportHelper.ToDataTable(kronosList, kronosDtName);
List <DataTable> listDt = new List <DataTable>()
{
shiftDt,
kronosDt,
};
return(listDt);
}
finally
{
if (shiftDt != null)
{
((IDisposable)shiftDt).Dispose();
}
if (kronosDt != null)
{
((IDisposable)kronosDt).Dispose();
}
}
}
public object Get(ExportToExcelReport request)
{
List <ExportToExcelResult> reportResult = null;
using (var dbCmd = Db.CreateCommand())
{
dbCmd.CommandType = CommandType.StoredProcedure;
dbCmd.CommandTimeout = 300;
dbCmd.CommandText = "sp_ServerExpotToExcelReport";
dbCmd.Parameters.Add(new SqlParameter("@SAP_Org_Chart", request.SAP_Org_Chart));
dbCmd.Parameters.Add(new SqlParameter("@C_BAY", request.C_BAY));
dbCmd.Parameters.Add(new SqlParameter("@Active", request.Active));
dbCmd.Parameters.Add(new SqlParameter("@SecurityPassID", request.SecurityPassID));
dbCmd.Parameters.Add(new SqlParameter("@OrganizationID", request.OrganizationID));
dbCmd.Parameters.Add(new SqlParameter("@HiringCompanyID", request.HiringCompanyID));
dbCmd.Parameters.Add(new SqlParameter("@LineManagerID", request.LineManagerID));
using (var reader = dbCmd.ExecuteReader())
{
reportResult = reader.ConvertToList <ExportToExcelResult>();
}
MemoryStream stream = null;
if (reportResult == null)
{
reportResult = new List <ExportToExcelResult>();
}
stream = ExportImportHelper.CreateCSVDocumentWithCaption(reportResult);
HttpResult res = new HttpResult(stream, HeadcountResource.CSVContentType);
res.Headers[HttpHeaders.ContentDisposition] = "attachment; filename=Report_" + DateTime.Today.ToString("dd-MMM-yyyy") + ".csv";
return(res);
}
}
