public async Task <ActionResult> Get(TypeBranchDateFromDateToViewModel Filter)
{
if (!ModelState.IsValid)
{
return(Json(Response.RenderError(ModelState)));
}
// Parameter
string filetype = HttpContext.Request.Query["FileType"].ToString().ToLower();
Pagination pagination = new Pagination(HttpContext);
// Auth Data
var userData = User.Claims.SingleOrDefault(c => c.Type.Equals("User")).Value;
UserMapRole UserData = JsonConvert.DeserializeObject <UserMapRole>(userData);
DateTime dateFrom = DateTime.ParseExact(Filter.DateFrom, "dd/MM/yyyy", enUS);
DateTime dateTo = DateTime.ParseExact(Filter.DateTo, "dd/MM/yyyy", enUS);
StringBuilder sqlStringBuilder = new StringBuilder();
sqlStringBuilder.Append("EXEC sp_RPT314_DailyCOD '");
sqlStringBuilder.Append(dateFrom.ToString("yyyyMMdd", enUS));
sqlStringBuilder.Append("','");
sqlStringBuilder.Append(dateTo.ToString("yyyyMMdd", enUS));
sqlStringBuilder.Append("','");
sqlStringBuilder.Append(UserData.Username);
sqlStringBuilder.Append("','");
sqlStringBuilder.Append(Filter.BranchList);
sqlStringBuilder.Append("'");
string strSQL = sqlStringBuilder.ToString();
List <DailyCOD> DailyCOD = await DB.DailyCOD.FromSql(strSQL).ToListAsync();
if (filetype.Equals("excel"))
{
return(ExportExcelDailyCOD(dateFrom, dateTo, DailyCOD));
}
int totalCount = DailyCOD.Count();
DailyCOD = DailyCOD.Skip(pagination.From()).Take(pagination.To()).ToList();
Response.Success = true;
Response.Result = DailyCOD;
Response.ResultInfo = new
{
page = pagination.Page,
perPage = pagination.PerPage,
count = DailyCOD.Count(),
totalCount = totalCount
};
DB.Dispose();
return(Json(Response.Render()));
}
public async Task <ActionResult> Get(TypeBranchDateFromDateToViewModel Filter, string FileType)
{
if (!ModelState.IsValid)
{
return(Json(Response.RenderError(ModelState)));
}
// Parameter
string filetype = (FileType ?? "").ToLower();
Pagination pagination = new Pagination(HttpContext);
// Auth Data
string userData = User.Claims.SingleOrDefault(c => c.Type.Equals("User")).Value;
UserMapRole UserData = JsonConvert.DeserializeObject <UserMapRole>(userData);
DateTime dateFrom = DateTime.ParseExact(Filter.DateFrom, "dd/MM/yyyy", enUS);
DateTime dateTo = DateTime.ParseExact(Filter.DateTo, "dd/MM/yyyy", enUS);
string parameter = "'" + dateFrom.ToString("yyyyMMdd", enUS) + "', '" + dateTo.ToString("yyyyMMdd", enUS) + "', '" + UserData.Username + "', '" + Filter.BranchList + "'";
string sql = "EXEC sp_PDC_Report_DailyRevenueVerify_Get " + parameter;
_logger.LogInformation(sql);
IQueryable <DailyRevenueVerify> queryableDailyRevenueVerify = DB.DailyRevenueVerify.FromSql(sql);
if (filetype.Equals("excel"))
{
//string exec = $"sp_PDC_Dashboard_EODCoverPage_Get '{dateFrom.ToString("yyyyMMdd", new CultureInfo("en-US"))}', '" + dateTo.ToString("yyyyMMdd", enUS) + "', '{UserData.Username}', '{filter.BranchList}'";
string exec = $"sp_PDC_Dashboard_CloseShopCoverPage_Get '{dateFrom.ToString("yyyyMMdd", new CultureInfo("en-US"))}', '{dateTo.ToString("yyyyMMdd", new CultureInfo("en-US"))}', '{UserData.Username}', '{Filter.BranchList}'";
IQueryable <EOD> queryableEOD = DB.EOD.FromSql(exec);
return(ExportExcelDailyRevenueVerify(dateFrom, dateTo, Filter.BranchList, await queryableDailyRevenueVerify.ToListAsync(), await queryableEOD.ToListAsync()));
}
int totalCount = queryableDailyRevenueVerify.Count();
List <DailyRevenueVerify> DailyRevenueVerify = queryableDailyRevenueVerify.Skip(pagination.From()).Take(pagination.To()).ToList();
Response.Success = true;
Response.Result = DailyRevenueVerify;
Response.ResultInfo = new
{
page = pagination.Page,
perPage = pagination.PerPage,
count = DailyRevenueVerify.Count(),
totalCount
};
DB.Dispose();
return(Json(Response.Render()));
}
public async Task <ActionResult> Get(TypeBranchDateFromDateToViewModel Filter, string FileType)
{
if (!ModelState.IsValid)
{
return(Json(Response.RenderError(ModelState)));
}
// Parameter
string filetype = (FileType ?? "").ToLower();
Pagination pagination = new Pagination(HttpContext);
// Auth Data
var userData = User.Claims.SingleOrDefault(c => c.Type.Equals("User")).Value;
UserMapRole UserData = JsonConvert.DeserializeObject <UserMapRole>(userData);
DateTime dateFrom = DateTime.ParseExact(Filter.DateFrom, "dd/MM/yyyy", enUS);
DateTime dateTo = DateTime.ParseExact(Filter.DateTo, "dd/MM/yyyy", enUS);
string parameter = "'" + dateFrom.ToString("yyyyMMdd", enUS) + "', '" + dateTo.ToString("yyyyMMdd", enUS) + "', '" + UserData.Username + "', '" + Filter.BranchList + "'";
string sql = "EXEC sp_PDC_Report_StockOrder_Get " + parameter;
_logger.LogInformation(sql);
List <StockOrder> StockOrder = await DB.StockOrder.FromSql(sql).ToListAsync();
if (filetype.Equals("excel"))
{
return(ExportExcel(dateFrom, dateTo, StockOrder));
}
int totalCount = StockOrder.Count();
StockOrder = StockOrder.Skip(pagination.From()).Take(pagination.To()).ToList();
Response.Success = true;
Response.Result = StockOrder;
Response.ResultInfo = new
{
page = pagination.Page,
perPage = pagination.PerPage,
count = StockOrder.Count(),
totalCount = totalCount
};
DB.Dispose();
return(Json(Response.Render()));
}
public async Task <ActionResult> Detail(TypeBranchDateFromDateToViewModel Filter, string id)
{
// Parameter
string filetype = HttpContext.Request.Query["FileType"].ToString().ToLower();
Pagination pagination = new Pagination(HttpContext);
// Auth Data
var userData = User.Claims.SingleOrDefault(c => c.Type.Equals("User")).Value;
UserMapRole UserData = JsonConvert.DeserializeObject <UserMapRole>(userData);
DateTime dateFrom = DateTime.ParseExact(Filter.DateFrom, "dd/MM/yyyy", enUS);
DateTime dateTo = DateTime.ParseExact(Filter.DateTo, "dd/MM/yyyy", enUS);
List <DailyCODDetail> DailyCODDetail = await DB.DailyCODDetail.FromSql($"EXEC sp_RPT314_DailyCODDetail '{UserData.Username}', '{id}', '{dateFrom.ToString("yyyyMMdd", enUS)}', '{dateTo.ToString("yyyyMMdd", enUS)}'--, {pagination.From()}, {pagination.To()}").ToListAsync();
if (filetype.Equals("excel"))
{
return(ExportExcelDailyCODDetail(id, dateFrom, dateTo, DailyCODDetail));
}
int totalCount = DailyCODDetail.Count();
DailyCODDetail = DailyCODDetail.Skip(pagination.From()).Take(pagination.To()).ToList();
Response.Success = true;
Response.Result = DailyCODDetail;
Response.ResultInfo = new
{
page = pagination.Page,
perPage = pagination.PerPage,
count = DailyCODDetail.Count(),
totalCount = totalCount
};
DB.Dispose();
return(Json(Response.Render()));
}
public async Task <ActionResult> Get(TypeBranchDateFromDateToViewModel Filter, string FileType)
{
if (!ModelState.IsValid)
{
return(Json(Response.RenderError(ModelState)));
}
// Parameter
string filetype = (FileType ?? "").ToLower();
bool withReviewBalance = filetype.Equals("excelreviewbalance");
Pagination pagination = new Pagination(HttpContext);
// Auth Data
var userData = User.Claims.SingleOrDefault(c => c.Type.Equals("User")).Value;
UserMapRole UserData = JsonConvert.DeserializeObject <UserMapRole>(userData);
DateTime dateFrom = DateTime.ParseExact(Filter.DateFrom, "dd/MM/yyyy", enUS);
DateTime dateTo = DateTime.ParseExact(Filter.DateTo, "dd/MM/yyyy", enUS);
string parameter = $"'{ dateFrom.ToString("yyyyMMdd", enUS) }', '{ dateTo.ToString("yyyyMMdd", enUS) }', '{ UserData.Username }', '{ Filter.BranchList }'";
string sql = "EXEC sp_PDC_Report_DailyRevenueConfirm_Get " + parameter;
_logger.LogInformation(sql);
List <DailyRevenueConfirm> DailyRevenueConfirm = await DB.DailyRevenueConfirm.FromSql(sql).ToListAsync();
if (withReviewBalance)
{
string[] branchList = Filter.BranchList.Split(',', StringSplitOptions.RemoveEmptyEntries);
ReviewBalanceReport = DB.ReviewBalanceReport
.Where(
rb => rb.ReportDate.Value >= dateFrom &&
rb.ReportDate <= dateTo &&
branchList.Contains(rb.BranchID))
.OrderBy(rb => rb.OracleDC)
.OrderBy(rb => rb.ReceiptDate)
.ToList();
}
if (filetype.Equals("excel") || filetype.Equals("excelreviewbalance"))
{
return(ExportExcelDailyRevenueConfirm(dateFrom, dateTo, DailyRevenueConfirm, withReviewBalance, ReviewBalanceReport));
}
int totalCount = DailyRevenueConfirm.Count();
DailyRevenueConfirm = DailyRevenueConfirm.Skip(pagination.From()).Take(pagination.To()).ToList();
Response.Success = true;
Response.Result = DailyRevenueConfirm;
Response.ResultInfo = new
{
page = pagination.Page,
perPage = pagination.PerPage,
count = DailyRevenueConfirm.Count(),
totalCount
};
DB.Dispose();
return(Json(Response.Render()));
}
public async Task <ActionResult> Pivot(TypeBranchDateFromDateToViewModel Filter)
{
if (!ModelState.IsValid)
{
return(Json(Response.RenderError(ModelState)));
}
DateTime DateTimeStartGen = DateTime.Now;
// Auth Data
string userData = User.Claims.SingleOrDefault(c => c.Type.Equals("User")).Value;
UserMapRole UserData = JsonConvert.DeserializeObject <UserMapRole>(userData);
DateTime dateFrom = DateTime.ParseExact(Filter.DateFrom, "dd/MM/yyyy", enUS);
DateTime dateTo = DateTime.ParseExact(Filter.DateTo, "dd/MM/yyyy", enUS);
string parameter = $"'{ dateFrom.ToString("yyyyMMdd", enUS) }', '{ dateTo.ToString("yyyyMMdd", enUS) }', '{ UserData.Username }', '{ Filter.BranchList }'";
string sql = "EXEC sp_PDC_Report_DailyRevenueConfirm_Pivot " + parameter;
_logger.LogInformation(sql);
List <DailyRevenuePivot> DailyRevenuePivot = await DB.DailyRevenuePivot.FromSql(sql).ToListAsync();
// Load the Excel Template
Stream xlsxStream = System.IO.File.OpenRead(_hostingEnvironment.WebRootPath + @"\assets\templates\DailyRevenueConfirmPivot.xlsx");
ExcelEngine excelEngine = new ExcelEngine();
// Loads or open an existing workbook through Open method of IWorkbooks
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(xlsxStream);
workbook.Version = ExcelVersion.Excel2016;
string strDateRange = $"{dateFrom.ToString("dd/MM/yyyy")} - {dateTo.ToString("dd/MM/yyyy")}";
// Sheet #2 (Daily Revenue Confirm)
IWorksheet worksheet = workbook.Worksheets[1];
worksheet.Range["E2"].Text = strDateRange;
worksheet.ImportData(DailyRevenuePivot, 4, 1, false);
// Copying a Range “A1” to “A5”.
int rangeStart = 4;
int rangeEnd = (rangeStart + DailyRevenuePivot.Count()) - 1;
//IRange source = worksheet.Range[$"AH{rangeStart}:AR{rangeStart}"];
//IRange destination = worksheet.Range[$"AH{rangeStart}:AR{rangeEnd}"];
//source.CopyTo(destination, ExcelCopyRangeOptions.All);
// Sheet #3 - #7
workbook.Worksheets[2].Range["C3"].Text = strDateRange;
workbook.Worksheets[3].Range["C3"].Text = strDateRange;
workbook.Worksheets[4].Range["C3"].Text = strDateRange;
workbook.Worksheets[5].Range["C3"].Text = strDateRange;
workbook.Worksheets[6].Range["C3"].Text = strDateRange;
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
// Close the instance of IWorkbook.
workbook.Close();
// Dispose the instance of ExcelEngine.
excelEngine.Dispose();
xlsxStream.Dispose();
_logger.LogInformation($"Generate duration: {(DateTime.Now - DateTimeStartGen).TotalSeconds}");
return(File(ms, "Application/msexcel", "KE_PDC_DailyRevenuePivot_Report_" + DateTime.Now.ToString("yyyMMdd_HHmmss") + ".xlsx"));
}