public async Task <IActionResult> MoneyTransferToExcel(MoneyTransferRptQuery model) { if (ModelState.IsValid) { var currentRoleId = partnerManager.GetCurrentUserRole(this.HttpContext); var permission = partnerActivity.GetPartAct("Report.MoneyTransfer.StatReport.ExportToExcel", currentRoleId); if (permission == null) { toastNotification.AddErrorToastMessage("ليس لديك الصلاحية الكافية", new ToastrOptions { Title = "" }); return(Redirect(Request.Headers["Referer"].ToString())); } var param = new MoneyTransferRptQueryParam { ChannelId = model.Param.ChannelId, PosId = model.Param.PosId, TransTypeId = model.Param.TransTypeId, LevelId = model.Param.LevelId, StartDate = model.Param.StartDate, EndDate = model.Param.EndDate }; var result = await new MoneyTransferRepo(db, null, null).GetStatReportAsync(param); model.Results = result; if (result != null) { using (var workbook = new XLWorkbook()) { var worksheet = workbook.AddWorksheet("MoneyTransfer"); worksheet.SetRightToLeft(); worksheet.ColumnWidth = 15; var currRow = 1; worksheet.Range(1, 1, 1, 5).Merge().Value = param.Title; worksheet.Range(1, 1, 1, 5).Style.Fill.BackgroundColor = XLColor.LightYellow; worksheet.Cell(currRow, 1).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center); worksheet.Cell(currRow, 1).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); worksheet.Row(1).Height = 100; worksheet.Row(1).Style.Font.Bold = true; worksheet.Row(1).Style.Font.FontSize = 16; ++currRow; worksheet.Cell(currRow, 1).Value = "القناة"; worksheet.Cell(currRow, 2).Value = model.Param.LevelId == "pos" ? "رقم النقطة" : "اليوم"; worksheet.Cell(currRow, 3).Value = model.Param.LevelId == "pos" ? "رقم النقطة" : ""; worksheet.Cell(currRow, 4).Value = "عدد العمليات"; worksheet.Cell(currRow, 5).Value = "اجمالي المبلغ"; worksheet.Row(currRow).Style.Font.Bold = true; worksheet.Row(currRow).Style.Font.FontSize = 14; worksheet.Row(currRow).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); worksheet.Row(currRow).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center); worksheet.Row(currRow).Height = 25; worksheet.Range(currRow, 1, currRow, 5).Style.Fill.BackgroundColor = XLColor.LightPastelPurple; foreach (var item in result) { ++currRow; worksheet.Columns(5, 5).Style.NumberFormat.SetFormat("#,##0.00"); worksheet.Columns(4, 4).Style.NumberFormat.SetFormat("#,##0"); worksheet.Row(currRow).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); worksheet.Row(currRow).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center); worksheet.Cell(currRow, 1).Value = item.Channel; worksheet.Cell(currRow, 2).Value = model.Param.LevelId == "pos" ? item.Partner.Id : item.CollDay; worksheet.Cell(currRow, 3).Value = model.Param.LevelId == "pos" ? item.Partner.Name : ""; worksheet.Cell(currRow, 4).Value = item.Count; worksheet.Cell(currRow, 5).Value = item.Amount; worksheet.Row(currRow).Height = 20; } ++currRow; worksheet.Row(currRow).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); worksheet.Row(currRow).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center); worksheet.Row(currRow).Height = 25; worksheet.Row(currRow).Style.Font.Bold = true; worksheet.Row(currRow).Style.Font.FontSize = 12; worksheet.Cell(currRow, "D").SetFormulaA1("=SUM(D3:D" + (currRow - 1) + ")"); worksheet.Cell(currRow, "E").SetFormulaA1("=SUM(E3:E" + (currRow - 1) + ")"); worksheet.Range(currRow, 1, currRow, 3).Merge().Value = "الاجــمـــالــي"; worksheet.Range(currRow, 1, currRow, 5).Style.Fill.BackgroundColor = XLColor.LightPastelPurple; worksheet.Range(1, 1, currRow, 5).Style.Border.BottomBorder = XLBorderStyleValues.Thick; worksheet.Range(1, 1, currRow, 5).Style.Border.BottomBorderColor = XLColor.Black; worksheet.Range(1, 1, currRow, 5).Style.Border.LeftBorder = XLBorderStyleValues.Thick; worksheet.Range(1, 1, currRow, 5).Style.Border.LeftBorderColor = XLColor.Black; worksheet.Range(1, 1, currRow, 5).Style.Border.RightBorder = XLBorderStyleValues.Thick; worksheet.Range(1, 1, currRow, 5).Style.Border.RightBorderColor = XLColor.Black; worksheet.Range(1, 1, currRow, 5).Style.Border.TopBorder = XLBorderStyleValues.Thick; worksheet.Range(1, 1, currRow, 5).Style.Border.TopBorderColor = XLColor.Black; worksheet.Range(1, 1, currRow, 5).Style.Border.InsideBorder = XLBorderStyleValues.Thin; worksheet.Range(1, 1, currRow, 5).Style.Border.InsideBorderColor = XLColor.LightGray; using (var stream = new MemoryStream()) { workbook.SaveAs(stream); var content = stream.ToArray(); return(File( content, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "MoneyTransfer" + DateTime.Today.ToString("yyyyMMdd") + ".xlsx" )); } } } } var channels = new CommonCodeRepo(db).GetCodesByType("access.channel"); var transTypes = new CommonCodeRepo(db).GetCodesByType("TransType"); model.Channels = channels; model.TransType = transTypes; return(View("MoneyTransfer", model)); }
public async Task <List <MoneyTransferRpt> > GetStatReportAsync(MoneyTransferRptQueryParam param) { #region Parameters param.Title = "تقرير احصائي يوضح اجمالي نقل ارصدة"; var parameters = new List <OracleParameter>(); var whereCluase = new StringBuilder(); if (param != null) { if (!string.IsNullOrEmpty(param.PosId)) { whereCluase.Append(param.TransTypeId == "debit" ? " WHERE part_id = :PosId" : " WHERE createdby = :PosId"); var p = new OracleParameter { ParameterName = "PosId", OracleDbType = OracleDbType.Varchar2, Value = param.PosId }; parameters.Add(p); param.Title += $"{Environment.NewLine} للرقم {param.PosId} "; } if (!string.IsNullOrEmpty(param.ChannelId) && param.ChannelId != "-1") { whereCluase.Append(whereCluase.Length > 0 ? " AND access_channel = :ChannelId" : " WHERE access_channel = :ChannelId"); var p = new OracleParameter { ParameterName = "ChannelId", OracleDbType = OracleDbType.Varchar2, Value = param.ChannelId }; parameters.Add(p); } if (param.StartDate > DateTime.MinValue && param.StartDate != null) { whereCluase.Append(whereCluase.Length > 0 ? " AND createdon >= :StartDate" : " WHERE createdon >= :StartDate"); var p = new OracleParameter { ParameterName = "StartDate", OracleDbType = OracleDbType.Date, Value = param.StartDate }; parameters.Add(p); } if (param.EndDate > DateTime.MinValue && param.EndDate != null) { whereCluase.Append(whereCluase.Length > 0 ? " AND createdon <= :EndDate" : " WHERE createdon <= :EndDate"); var p = new OracleParameter { ParameterName = "EndDate", OracleDbType = OracleDbType.Date, Value = param.EndDate.AddDays(1) }; parameters.Add(p); } } #endregion string strSql = string.Empty; if (param.LevelId == "pos") { param.Title += $"{Environment.NewLine} للفترة من {param.StartDate.ToShortDateString()} الى {param.EndDate.ToShortDateString()} " + "على مستوى نقطة البيع"; strSql = $"select t.access_channel,t.access_channel_name, " + (param.TransTypeId == "debit" ? "t.part_id, t.part_name," : "t.createdby, t.creator_name,") + " count(*) cnt, sum(t.amount) amt from v_money_transfer t " + $" {whereCluase}" + $" group by t.access_channel, " + (param.TransTypeId == "debit" ? "t.part_id, t.part_name," : "t.createdby, t.creator_name,") + " t.access_channel_name "; } else { param.Title += $"{Environment.NewLine} للفترة من {param.StartDate.ToShortDateString()} الى {param.EndDate.ToShortDateString()} " + "على مستوى اليوم"; strSql = $"select t.access_channel,t.access_channel_name,to_char(createdon,'yyyy/mm/dd') d, count(*) cnt, sum(t.amount) amt from v_money_transfer t " + $" {whereCluase}" + $" group by t.access_channel,t.access_channel_name, to_char(createdon,'yyyy/mm/dd') "; } DataTable masterDataTable; masterDataTable = await db.GetDataAsync(strSql, parameters); if (masterDataTable == null) { return(null); } if (masterDataTable.Rows.Count == 0) { return(null); } var results = new List <MoneyTransferRpt>(); foreach (DataRow row in masterDataTable.Rows) { var obj = new MoneyTransferRpt(); obj.Amount = row["amt"] == DBNull.Value ? 0 : double.Parse(row["amt"].ToString()); obj.Count = row["cnt"] == DBNull.Value ? 0 : int.Parse(row["cnt"].ToString()); obj.Channel = row["access_channel_name"] == DBNull.Value ? string.Empty : row["access_channel_name"].ToString(); if (param.LevelId == "pos") { obj.Partner.Id = row[(param.TransTypeId == "debit" ? "part_id" : "createdby")] == DBNull.Value ? string.Empty : row[(param.TransTypeId == "debit" ? "part_id" : "createdby")].ToString(); obj.Partner.Name = row[(param.TransTypeId == "debit" ? "part_name" : "creator_name")] == DBNull.Value ? string.Empty : row[(param.TransTypeId == "debit" ? "part_name" : "creator_name")].ToString(); } else if (param.LevelId == "day") { obj.CollDay = row["d"] == DBNull.Value ? string.Empty : row["d"].ToString(); } results.Add(obj); } return(results); }