public PartialViewResult Settlement(DateTime trxdate, string chkType)
{
string s = trxdate.Date.ToShortDateString();
List <mSettlementReport> srl = new List <mSettlementReport>();
if (HttpContext.Session["UID"] == null)
{
TempData["ErrorMessage"] = "Session Error";
TempData.Keep();
return(PartialView(srl));
}
try
{
mSettlementReport sr = new mSettlementReport();
srl = sr.Retrieve_Settlement_Report(chkType, trxdate);
}
catch (Exception ex)
{
TempData["ErrorMessage"] = ex.Message;
TempData.Keep();
}
return(PartialView(srl));
}
internal FileResult SettlementExportExcel(DateTime trxdate, string chkType)
{
if (HttpContext.Session["UID"] != null && trxdate != null && chkType != null)
{
List <mSettlementReport> srl = new List <mSettlementReport>();
mSettlementReport sr = new mSettlementReport();
srl = sr.Retrieve_Settlement_Report(chkType, trxdate);
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
ExcelPackage Ep = new ExcelPackage();
ExcelWorksheet Sheet = Ep.Workbook.Worksheets.Add("SettlementReport");
Sheet.Cells["A1"].Value = "TrxDate";
Sheet.Cells["B1"].Value = "MID";
Sheet.Cells["C1"].Value = "Account Name";
Sheet.Cells["D1"].Value = "Merchant Name";
Sheet.Cells["E1"].Value = "Settlement A/C";
Sheet.Cells["F1"].Value = "Total Trans";
Sheet.Cells["G1"].Value = "Tran Amount";
Sheet.Cells["H1"].Value = "MDR Value";
Sheet.Cells["I1"].Value = "Settlement Amount";
int row = 2;
foreach (var item in srl)
{
Sheet.Cells[string.Format("A{0}", row)].Value = item.TrxDate.Value.Date.ToString("MM/dd/yyyy");
Sheet.Cells[string.Format("B{0}", row)].Value = item.MPU_Merchant_ID;
Sheet.Cells[string.Format("C{0}", row)].Value = item.SettAccountName;
Sheet.Cells[string.Format("D{0}", row)].Value = item.Merchant_Name;
Sheet.Cells[string.Format("E{0}", row)].Value = item.Settlement_Acc;
Sheet.Cells[string.Format("F{0}", row)].Value = item.TotalTrans;
Sheet.Cells[string.Format("G{0}", row)].Value = item.TrxAmount;
Sheet.Cells[string.Format("H{0}", row)].Value = item.MDRValue;
Sheet.Cells[string.Format("I{0}", row)].Value = item.SettAmount;
row++;
}
using (ExcelRange rng = Sheet.Cells[string.Format("G{0}:I{1}", 2, row + 1)])
{
rng.Style.Numberformat.Format = "###,###,##0.00";
}
Sheet.Cells[string.Format("A{0}:E{1}", row, row)].Merge = true;
Sheet.Cells[string.Format("A{0}", row)].Value = "Total";
Sheet.Cells[string.Format("F{0}", row)].Value = srl.Sum(i => i.TotalTrans);
Sheet.Cells[string.Format("G{0}", row)].Value = srl.Sum(i => i.TrxAmount);
Sheet.Cells[string.Format("H{0}", row)].Value = srl.Sum(i => i.MDRValue);
Sheet.Cells[string.Format("I{0}", row)].Value = srl.Sum(i => i.SettAmount);
Sheet.Cells["A:AZ"].AutoFitColumns();
Response.Clear();
var FileByteArray = Ep.GetAsByteArray();
return(File(FileByteArray, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", trxdate.ToString("MM/dd/yyyy") + chkType + "_SettlementReport.xlsx"));
//Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//Response.AddHeader("content-disposition", "attachment: filename=" +trxdate.ToString("MM/dd/yyyy")+chkType+ "_SettlementReport.xlsx");
//Response.BinaryWrite(Ep.GetAsByteArray());
//Response.End();
}
else
{
return(File(new byte[0], "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
}
}
