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