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")); }