public string RPT_ACQUIREREISSMDB(DateTime SETTDATE, string reportPATH, string logopath, string conString)
{
//PARTY, ISSUER, ACQUIRER AND DASHBOARD
DateTime SETT;
SETT = SETTDATE;
string settlementpathdate = Path.Combine(reportPATH, "SETTLEMENT_DETAIL_SETT_" + SETT.ToString("dd-MMM-yyyy"));
if (!Directory.Exists(settlementpathdate))
{
Directory.CreateDirectory(settlementpathdate);
}
string output = string.Empty;
string bankName = string.Empty;
string bankshort = string.Empty;
string cbncode = string.Empty;
string cardScheme = string.Empty;
string DASHBOARDPathMerchant = Path.Combine(settlementpathdate, "UP_DASHBOARD");
if (!Directory.Exists(DASHBOARDPathMerchant))
{
Directory.CreateDirectory(DASHBOARDPathMerchant);
}
dsLoadclass dv = new dsLoadclass();
// output = rptUPDashboard.GenReport1("UP", null, SETT, reportPATH, DASHBOARDPathMerchant, logopath);
//BANKS ACQUIRER REPORTS
//ACQUIRER REPORTS
//////using (var con = new SqlConnection(sourceDb))
//////{
////// con.Open();
////// string qryISSA = "Select * from SM_INSTITUTION where lower(status)='active' and cbn_code in (select MERCHANTDEPOSITBANKCODE from SM_SETTLEMENTDETAIL where SETTLEMENTDATETIME='" + SETT.ToString("dd-MMM-yyyy") + "') order by INSTITUTION_NAME";
////// using (var cmd = new SqlCommand(qryISSA, con))
////// {
////// cmd.CommandTimeout = 0;
////// SqlDataReader readerA = cmd.ExecuteReader(CommandBehavior.CloseConnection);
////// // var recListISS = Fetch(c => c.Query<POSMISDB_INSTITUTION>(qryISS, null, commandTimeout: 3000, commandType: CommandType.Text), conString);
////// using (SqlDataReader reader = readerA)
////// {
////// while (reader.Read())
////// {
////// bankName = reader["INSTITUTION_NAME"].ToString();
////// bankshort = reader["INSTITUTION_SHORTCODE"].ToString();
////// cbncode = reader["CBN_CODE"].ToString();
////// string FILENAME_PATH = Path.Combine(settlementpathdate, bankName);
////// if (!Directory.Exists(FILENAME_PATH))
////// {
////// Directory.CreateDirectory(FILENAME_PATH);
////// }
////// try
////// {
////// //output = rptIssuer.GenReport1(bankName, bankshort, SETT, reportPATH, FILENAME_PATH, logopath, "NAIRA SETTLEMENT", "DOM");
////// //output = rptMDB.GenReport1(bankName, cbncode, SETT, reportPATH, FILENAME_PATH, logopath, "NAIRA SETTLEMENT", "DOM");
////// }
////// catch (Exception ex)
////// {
////// }
////// }
////// reader.Dispose();
////// }
////// }
//////}
string qry = "Select * from SM_INSTITUTION where lower(status)='active' AND CBN_CODE IN (SELECT DISTINCT CBN_CODE FROM SM_MCCMSC WHERE CBN_CODE IS NOT NULL) order by INSTITUTION_NAME";
using (SqlDataReader reader = dv.GetREcordCount(qry))
{
while (reader.Read())
{
//BANK LIST
bankName = reader["INSTITUTION_NAME"].ToString();
bankshort = reader["INSTITUTION_SHORTCODE"].ToString();
cbncode = reader["CBN_CODE"].ToString();
string FILENAME_PATH = Path.Combine(settlementpathdate, bankName);
if (!Directory.Exists(FILENAME_PATH))
{
Directory.CreateDirectory(FILENAME_PATH);
}
try
{
output = rptAcquirer.GenReport1(bankName, bankshort, SETT, reportPATH, FILENAME_PATH, logopath, "NAIRA SETTLEMENT", "DOM");
}
catch (Exception ex)
{
}
}
}
//ISSUER REPORTS
//string qryISS = "Select * from POSMISDB_INSTITUTION where lower(status)='active' order by INSTITUTION_NAME";
string qryISS = "Select * from SM_INSTITUTION where lower(status)='active' and cbn_code in (select MERCHANTDEPOSITBANKCODE from SM_SETTLEMENTDETAIL where SETTLEMENTDATETIME='" + SETT.ToString("dd-MMM-yyyy") + "') order by INSTITUTION_NAME";
// var recListISS = Fetch(c => c.Query<POSMISDB_INSTITUTION>(qryISS, null, commandTimeout: 3000, commandType: CommandType.Text), conString);
using (SqlDataReader reader = dv.GetREcordCount(qryISS))
{
while (reader.Read())
{
bankName = reader["INSTITUTION_NAME"].ToString();
bankshort = reader["INSTITUTION_SHORTCODE"].ToString();
cbncode = reader["CBN_CODE"].ToString();
string FILENAME_PATH = Path.Combine(settlementpathdate, bankName);
if (!Directory.Exists(FILENAME_PATH))
{
Directory.CreateDirectory(FILENAME_PATH);
}
try
{
//output = rptIssuer.GenReport1(bankName, bankshort, SETT, reportPATH, FILENAME_PATH, logopath, "NAIRA SETTLEMENT", "DOM");
//output = rptMDB.GenReport1(bankName, cbncode, SETT, reportPATH, FILENAME_PATH, logopath, "NAIRA SETTLEMENT", "DOM");
}
catch (Exception ex)
{
}
}
reader.Dispose();
}
return(null);
}
public async Task <ActionResult> RunPdf(DateTime?SetDate, string ReportType, string CardScheme, string DrBank, string MerchantId, string Channel)
{
try
{
string logopath = !string.IsNullOrEmpty(ConfigurationManager.AppSettings["LogoPath"]) ? Convert.ToString(ConfigurationManager.AppSettings["LogoPath"]) : string.Empty;
var fileName = "";
var handle = "";
string subReport = null;
string searchId = null;
string reportName = "";
string address = "";
int mtype = 0;
var companyname = repoComp.AllEager().FirstOrDefault().COMPANY_NAME;
var ReportList = await _repo.GetReportListAsync();
if (institutionId == 1)
{
if (!string.IsNullOrEmpty(MerchantId.Trim()))
{
//subReport = "M";
searchId = MerchantId.Trim();
var sig = repoM.AllEager(d => d.MERCHANTID == searchId).FirstOrDefault();
if (sig != null)
{
reportName = sig.MERCHANTNAME;
}
}
else
{
searchId = DrBank;
var sig = repoInst.AllEager(d => d.CBN_CODE == searchId).FirstOrDefault();
if (sig != null)
{
reportName = sig.INSTITUTION_NAME;
}
}
//string searchID,string CardScheme,string reportType,string reportClass, string subreporttype, string sett, string ReportPath, string logopath
var rec = ReportList.Where(d => d.Code == ReportType).FirstOrDefault();
fileName = string.Format("{0}-{1} for {2}.xlsx", rec.Description, reportName, SetDate.GetValueOrDefault().ToString("dd-MM-yy"));
dsLoadclass dv = new dsLoadclass();
var dtMain = dv.generateDS(searchId, CardScheme, rec.reporttype, rec.reportClass, rec.subreporttype, SetDate.GetValueOrDefault().ToString("yyyy-MM-dd"), int.Parse(Channel));
ExportDataTableToPdf(dtMain, SetDate.GetValueOrDefault(), fileName, rec.Description, logopath);
}
else
{
var sig = _repo.GetInstitution(institutionId, false).FirstOrDefault();
if (sig != null)
{
searchId = sig.CBN_CODE;
reportName = sig.INSTITUTION_NAME;
}
fileName = string.Format("Settlement Report-{0} for {1}.xlsx", reportName, SetDate.GetValueOrDefault().ToString("dd-MM-yy"));
dsLoadclass dv = new dsLoadclass();
var dtMain = dv.generateDS(searchId, CardScheme, "ALL", "U", subReport, SetDate.GetValueOrDefault().ToString("yyyy-MM-dd"), int.Parse(Channel));
// var excelBytes = ExcelHelper.ExportDataSet(dtMain, "SETTLEMENT DETAIL");
//var excelBytes = ExcelHelper.ExportDatasetWithLogo(10, dtMain, "SETTLEMENT DETAIL", reportName, mtype, address, companyname, logopath, SetDate.GetValueOrDefault().ToString("dd-MM-yy"));
//handle = Guid.NewGuid().ToString();
//Session[handle] = dtMain;
ExportDataTableToPdf(dtMain, SetDate.GetValueOrDefault(), fileName, "Merchant Report", logopath);
}
//List<FileContentResult> tr = new List<FileContentResult>();
//tr.Add(File(excelBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName));
//tr.Add(File(excelBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName + 1));
//Return file with the type and name.
//ContentType "application/vnd.ms-excel" does not work well for browsers other than IE.
//return excelBytes != null ? File(excelBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName) : null;
return(Json(new { FileGuid = handle, FileName = fileName, RespCode = 0, RespMessage = "" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(new { RespCode = 1, RespMessage = "Error Processing File" }, JsonRequestBehavior.AllowGet));
}
}
public static string GenReport2(string reportFor, string ID, DateTime reportDate, string reportPATH, string ISSUERPath, string logopath, string reportFolder, string reportFolderType)
{
//FileInfo template = new FileInfo(reportPATH + @"\TEMPLATES\Issuer Report Template.xlsx");
string sett = reportDate.ToString("dd-MMM-yyyy");
string reportname = reportFor + "_ACQUIRER_MERCHANTS.xlsx";
string oISSUERPath = System.IO.Path.Combine(ISSUERPath, "ACQUIRER");
if (!Directory.Exists(oISSUERPath))
{
System.IO.Directory.CreateDirectory(oISSUERPath);
}
string oISSUERPath2 = System.IO.Path.Combine(oISSUERPath, reportFolder);
if (!Directory.Exists(oISSUERPath2))
{
System.IO.Directory.CreateDirectory(oISSUERPath2);
}
string reportISSUERPath = oISSUERPath2 + "\\" + reportname;
try
{
if (System.IO.File.Exists(reportISSUERPath))
{
System.IO.File.Delete(reportISSUERPath);
}
}
catch
{
}
dsLoadclass dv = new dsLoadclass();
dv.generateDS(reportFor, ID, null, "ACQ_MERCH", "B", sett, 0);
return(null);
////FileInfo newFile = new FileInfo(reportISSUERPath);
////MemoryStream stream = new MemoryStream();
////using (ExcelPackage package = new ExcelPackage(stream))
////{
//// try
//// {
//// var ws = package.Workbook.Worksheets.Add("AQUIRER MERCHANTS");
//// ws = generateReport(ws, logopath, reportFor, ID, null, "ACQ_MERCH", "B","",0, "B", sett, 8);
//// }
//// catch (Exception ex)
//// {
//// }
//// package.Compression = CompressionLevel.BestSpeed;
//// package.SaveAs(newFile);
//// Stream.Dispose();:Stream.Dispose();:package.Dispose();
////}
////return newFile.FullName;
}
public static ExcelWorksheet generateReportSUMM(ExcelWorksheet ws, string img, string rName, string ID, string cardScheme, string reportType, string reportClass, string subreporttype, int Channel, string sett, int startRow)
{
//ExcelRange cols = ws.Cells["A:XFD"];
//cols.Style.Fill.PatternType = ExcelFillStyle.Solid;
//cols.Style.Fill.BackgroundColor.SetColor(Color.White); ;
System.Drawing.Image myImage = System.Drawing.Image.FromFile(img);
var pic = ws.Drawings.AddPicture("LOGO", myImage);
// Row, RowoffsetPixel, Column, ColumnOffSetPixel
pic.SetPosition(0, 0, 0, 0);
//Insert a row at the top. Note that the formula-addresses are shifted down
//ws.InsertRow(startRow-1, 1);
if (cardScheme == null)
{
cardScheme = String.Empty;
}
//Write the headers and style them
ws.Cells["A2"].Value = "Unified Payment Services Ltd";
ws.Cells["A2"].Style.Font.Size = 18;
ws.Cells["A2"].Style.Font.Bold = true;
ws.Cells["A2:M2"].Merge = true;
ws.Cells["A2:M2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
String CARDSCHEMDESC = String.Empty;
if (cardScheme.ToUpper() == "PAYA")
{
CARDSCHEMDESC = "PAY ATTITUDE";
}
else if (cardScheme.ToUpper() == "MAST")
{
CARDSCHEMDESC = "MASTER CARD";
}
else if (cardScheme.ToUpper() == "VISA")
{
CARDSCHEMDESC = "VISA CARD";
}
else if (cardScheme.ToUpper() == "CUPI")
{
CARDSCHEMDESC = "CHINA UNION PAY";
}
else
{
CARDSCHEMDESC = cardScheme.ToUpper();
}
ws.Cells["A4"].Value = "ACQUIRER INCOME SUMMARY ";
ws.Cells["A4"].Style.Font.Size = 14;
ws.Cells["A4"].Style.Font.Bold = true;
ws.Cells["A4:M4"].Merge = true;
ws.Cells["A4:M4"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
ws.Cells["A5"].Value = "SETTLEMENT DATE: " + sett;
ws.Cells["A6"].Value = rName;
ws.Cells["A6"].Style.Font.Size = 14;
ws.Cells["A6"].Style.Font.Bold = true;
ws.Cells["A6:H6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
ws.View.FreezePanes(8, 1);
int columnIDcnt = 0;
int row = startRow;
try
{
dsLoadclass dv = new dsLoadclass();
using (SqlDataReader dr = dv.generateDR(ID, cardScheme, reportType, reportClass, subreporttype, sett, Channel))
{
if (dr.HasRows)
{
int columnID = 1;
var fieldcount = dr.FieldCount;
DataTable dtSchema = dr.GetSchemaTable();
foreach (DataRow drow in dtSchema.Rows)
{
string columnName = Convert.ToString(drow["ColumnName"]);
ws.SetValue(startRow - 1, columnID, columnName);
columnID += 1;
}
columnIDcnt = columnID;
while (dr.Read())
{
for (int i = 0; i < fieldcount; i++)
{
ws.SetValue(row, i + 1, dr[i]);
}
row++;
////ws.SetValue(row, 1, dr[0]);
////ws.SetValue(row, 2, dr[1]);
////ws.SetValue(row, 3, dr[2]);
////ws.SetValue(row, 4, dr[3]);
////ws.SetValue(row, 5, dr[4]);
////ws.SetValue(row, 6, dr[5]);
////ws.SetValue(row, 7, dr[6]);
////ws.SetValue(row, 8, dr[7]);
////if (reportType == "ACQR_EXCEP")
////{
//// ws.SetValue(row, 9, dr[8]);
//// ws.SetValue(row, 10, dr[9]);
////}
////row++;
}
}
}
}
catch (Exception ex)
{
//Console.WriteLine(ex.Message );
}
if (columnIDcnt > 0)
{
ws.Cells[2, 2, row + 1, columnIDcnt - 1].Style.Numberformat.Format = "#,##0.00";
ws.Cells[row + 1, 1].Value = "TOTAL";
ws.Cells[row + 1, 1].Style.Font.Bold = true;
ws.Cells[row + 1, 2, row + 1, columnIDcnt - 1].Formula = string.Format("=SUM(B{0}:B{1})", startRow, row);
ws.Cells[row + 1, 2, row + 1, columnIDcnt - 1].Style.Numberformat.Format = "#,##0.00";
ws.Cells[startRow, 1, row, 80].AutoFitColumns(20);
ws.Select("C8");
}
return(ws);
}
public static ExcelWorksheet generateReport(ExcelWorksheet ws, string img, string rName, string ID, string cardScheme, string reportType, string reportClass, string subreporttype, int Channel, string sett, int startRow)
{
System.Drawing.Image myImage = System.Drawing.Image.FromFile(img);
var pic = ws.Drawings.AddPicture("LOGO", myImage);
// Row, RowoffsetPixel, Column, ColumnOffSetPixel
pic.SetPosition(0, 0, 0, 0);
//Insert a row at the top. Note that the formula-addresses are shifted down
//ws.InsertRow(startRow-1, 1);
if (cardScheme == null)
{
cardScheme = String.Empty;
}
//Write the headers and style them
ws.Cells["A2"].Value = "Unified Payment Services Ltd";
ws.Cells["A2"].Style.Font.Size = 18;
ws.Cells["A2"].Style.Font.Bold = true;
ws.Cells["A2:M2"].Merge = true;
ws.Cells["A2:M2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
String CARDSCHEMDESC = String.Empty;
if (cardScheme.ToUpper() == "PAYA")
{
CARDSCHEMDESC = "PAY ATTITUDE";
}
else if (cardScheme.ToUpper() == "MAST")
{
CARDSCHEMDESC = "MASTER CARD";
}
else if (cardScheme.ToUpper() == "VISA")
{
CARDSCHEMDESC = "VISA CARD";
}
else if (cardScheme.ToUpper() == "CUPI")
{
CARDSCHEMDESC = "CHINA UNION PAY";
}
else
{
CARDSCHEMDESC = cardScheme.ToUpper();
}
ws.Cells["A4"].Value = "ACQUIRER INCOME REPORT FOR " + CARDSCHEMDESC;
ws.Cells["A4"].Style.Font.Size = 14;
ws.Cells["A4"].Style.Font.Bold = true;
ws.Cells["A4:M4"].Merge = true;
ws.Cells["A4:M4"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
ws.Cells["A5"].Value = "SETTLEMENT DATE: " + sett;
ws.Cells["A6"].Value = rName;
ws.Cells["A6"].Style.Font.Size = 14;
ws.Cells["A6"].Style.Font.Bold = true;
ws.Cells["A6:H6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
ws.View.FreezePanes(8, 1);
int columnIDcnt = 0;
int row = startRow;
try
{
dsLoadclass dv = new dsLoadclass();
using (SqlDataReader dr = dv.generateDR(ID, cardScheme, reportType, reportClass, subreporttype, sett, Channel))
{
if (dr.HasRows)
{
int columnID = 1;
var fieldcount = dr.FieldCount;
DataTable dtSchema = dr.GetSchemaTable();
foreach (DataRow drow in dtSchema.Rows)
{
string columnName = Convert.ToString(drow["ColumnName"]);
ws.SetValue(startRow - 1, columnID, columnName);
columnID += 1;
}
columnIDcnt = columnID;
while (dr.Read())
{
for (int i = 0; i < fieldcount; i++)
{
ws.SetValue(row, i + 1, dr[i]);
}
row++;
////row++;
}
}
}
}
catch (Exception ex)
{
//Console.WriteLine(ex.Message );
}
////ws.Cells[2, 3, row + 1, 3].Style.Numberformat.Format = "dd-mm-yyyy hh:mm:ss AM/PM";
////ws.Cells[2, 5, row + 1, 6].Style.Numberformat.Format = "dd-mm-yyyy";
////ws.Cells[2, 8, row + 1, 8].Style.Numberformat.Format = "dd-mm-yyyy";
////ws.Cells[2, 43, row + 1, 48].Style.Numberformat.Format = "#,##0.00";
////ws.Cells[2, 49, row + 1, 50].Style.Numberformat.Format = "#,##0";
////ws.Cells[2, 51, row + 1, 57].Style.Numberformat.Format = "#,##0.00";
//// ws.Cells[2, 60, row + 1, 77].Style.Numberformat.Format = "#,##0.00";
////ws.Cells[row + 1, 1].Value = "TOTAL";
////ws.Cells[row + 1, 43, row + 1, 48].Formula = string.Format("=SUM(K{0}:K{1})", startRow, row);
////ws.Cells[row + 1, 43, row + 1, 48].Style.Numberformat.Format = "#,##0.00";
////ws.Cells[row + 1, 51, row + 1, 57].Formula = string.Format("=SUM(AX{0}:AX{1})", startRow, row);
////ws.Cells[row + 1, 51, row + 1, 57].Style.Numberformat.Format = "#,##0.00";
////ws.Cells[row + 1, 65, row + 1, 80].Formula = string.Format("=SUM(BL{0}:BL{1})", startRow, row);
////ws.Cells[row + 1, 65, row + 1, 80].Style.Numberformat.Format = "#,##0.00";
////ws.Cells[startRow, 1, row, 80].AutoFitColumns(20);
////ws.Select("C8");
////return ws;
if (columnIDcnt > 0)
{
ws.Cells[startRow, 3, row + 1, 3].Style.Numberformat.Format = "dd-mm-yyyy hh:mm:ss AM/PM";
ws.Cells[startRow, 5, row + 1, 7].Style.Numberformat.Format = "dd-mm-yyyy";
// ws.Cells[2, 8, row + 1, 8].Style.Numberformat.Format = "dd-mm-yyyy";
ws.Cells[startRow, 38, row + 1, 39].Style.Numberformat.Format = "#,##0.00";
ws.Cells[startRow, 44, row + 1, 45].Style.Numberformat.Format = "#,##0";
ws.Cells[startRow, 46, row + 1, 46].Style.Numberformat.Format = "#,##0.00";
ws.Cells[startRow, 48, row + 1, 52].Style.Numberformat.Format = "#,##0.00";
ws.Cells[startRow, 56, row + 1, 56].Style.Numberformat.Format = "#,##0.00";
ws.Cells[startRow, 60, row + 1, 60].Style.Numberformat.Format = "#,##0.00";
ws.Cells[startRow, 62, row + 1, 62].Style.Numberformat.Format = "#,##0.00";
ws.Cells[startRow, 65, row + 1, 66].Style.Numberformat.Format = "#,##0.00";
ws.Cells[startRow, 69, row + 1, 69].Style.Numberformat.Format = "#,##0.00";
ws.Cells[startRow, 72, row + 1, 72].Style.Numberformat.Format = "#,##0.00";
ws.Cells[startRow, 75, row + 1, 75].Style.Numberformat.Format = "#,##0.00";
ws.Cells[startRow, 78, row + 1, 78].Style.Numberformat.Format = "#,##0.00";
ws.Cells[startRow, 81, row + 1, 82].Style.Numberformat.Format = "#,##0.00";
// ws.Cells[2, 57, row + 1, 58].Style.Numberformat.Format = "#,##0.00";
// ws.Cells[2, 60, row + 1, 77].Style.Numberformat.Format = "#,##0.00";
//ws.Cells[row + 1, 1].Value = "TOTAL";
//ws.Cells[row + 1, 2, row + 1, columnIDcnt - 1].Formula = string.Format("=SUM(B{0}:B{1})", startRow, row);
//ws.Cells[row + 1, 2, row + 1, columnIDcnt - 1].Style.Numberformat.Format = "#,##0.00";
ws.Cells[row, 1].Value = "TOTAL";
ws.Cells[row, 38, row, 39].Formula = string.Format("=SUM(AL{0}:AL{1})", startRow, row - 1);
ws.Cells[row, 46, row, 46].Formula = string.Format("=SUM(AT{0}:AT{1})", startRow, row - 1);
ws.Cells[row, 48, row, 52].Formula = string.Format("=SUM(AV{0}:AV{1})", startRow, row - 1);
ws.Cells[row, 56, row, 56].Formula = string.Format("=SUM(BD{0}:BD{1})", startRow, row - 1);
ws.Cells[row, 60, row, 60].Formula = string.Format("=SUM(BH{0}:BH{1})", startRow, row - 1);
ws.Cells[row, 62, row, 62].Formula = string.Format("=SUM(BJ{0}:BJ{1})", startRow, row - 1);
ws.Cells[row, 65, row, 66].Formula = string.Format("=SUM(BM{0}:BN{1})", startRow, row - 1);
ws.Cells[row, 69, row, 69].Formula = string.Format("=SUM(BQ{0}:BQ{1})", startRow, row - 1);
ws.Cells[row, 72, row, 72].Formula = string.Format("=SUM(BT{0}:BT{1})", startRow, row - 1);
ws.Cells[row, 75, row, 75].Formula = string.Format("=SUM(BW{0}:BW{1})", startRow, row - 1);
ws.Cells[row, 78, row, 78].Formula = string.Format("=SUM(BZ{0}:BZ{1})", startRow, row - 1);
ws.Cells[row, 81, row, 82].Formula = string.Format("=SUM(CC{0}:CD{1})", startRow, row - 1);
ws.Cells[startRow, 1, row, columnIDcnt].AutoFitColumns(20);
ws.Select("C8");
}
return(ws);
}
public static string GenSettlementAll(string searchId, string reportType, string reportClass, DateTime setDate, string reportPATH, string filePath, string logopath, string reportFolder, string reportFolderType)
{
// LogFunction2.WriteMaintenaceLogToFile("IN........GenSettlementAll");
string sett = setDate.ToString("dd-MMM-yyyy");
string reportname = "SETTLEMENT_ALL" + ".xlsx";
string _filePath = Path.Combine(filePath, reportFolder);
if (!Directory.Exists(_filePath))
{
Directory.CreateDirectory(_filePath);
}
string reportPath = _filePath + "\\" + reportname;
try
{
if (File.Exists(reportPath))
{
File.Delete(reportPath);
}
}
catch
{
}
FileInfo newFile = new FileInfo(reportPath);
MemoryStream stream = new MemoryStream();
using (ExcelPackage package = new ExcelPackage(stream))
{
DataTable dtMain = null;
try
{
dsLoadclass dv = new dsLoadclass();
//string searchID,string CardScheme,string reportType,string reportClass, string subreporttype, string sett, string ReportPath, string logopath)
var wsA = package.Workbook.Worksheets.Add("DETAILS");
dtMain = dv.generateDS("", "", "ALL", "U", "", sett, 0);
//ExcelRange cols = wsA.Cells["A:XFD"];
//wsA.Cells["A:XFD"].Style.Fill.PatternType = ExcelFillStyle.Solid;
//wsA.Cells["A:XFD"].Style.Fill.BackgroundColor.SetColor(Color.White);
System.Drawing.Image myImage = System.Drawing.Image.FromFile(logopath);
var pic = wsA.Drawings.AddPicture("LOGO", myImage);
pic.SetPosition(0, 0, 0, 0);
var range = wsA.Cells["A11"].LoadFromDataTable(dtMain, true);
wsA.Row(11).Style.Fill.PatternType = ExcelFillStyle.Solid;
wsA.Row(11).Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Orange);
//wsA.Tables.Add(range, "data");
//Write the headers and style them
wsA.Cells["A2"].Value = "Xpress Payment Services Ltd";
wsA.Cells["A2"].Style.Font.Size = 18;
wsA.Cells["A2"].Style.Font.Bold = true;
wsA.Cells["A2:M2"].Merge = true;
wsA.Cells["A2:M2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
wsA.Cells["A3"].Value = "SETTLEMENT DETAIL REPORT ";
wsA.Cells["A3"].Style.Font.Size = 14;
wsA.Cells["A3"].Style.Font.Bold = true;
wsA.Cells["A3:M3"].Merge = true;
wsA.Cells["A3:M3"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
wsA.Cells["A5"].Value = "SETTLEMENT DATE: " + sett;
//wsA.Cells["A5"].Value = "MERCHANT NAME: " + MERCHANTNAME;
wsA.Cells["A5"].Style.Font.Size = 10;
wsA.Cells["A5:F5"].Merge = true;
//wsA.Cells["A6"].Value = "ADDRESS: " + ADDRESS;
wsA.Cells["A6"].Style.Font.Size = 10;
wsA.Cells["A6:F6"].Merge = true;
//wsA.Cells["A7"].Value = "SETTLEMENT CURRENCY " + SettlementCur;
wsA.Cells["A7"].Style.Font.Size = 10;
wsA.Cells["A7"].Style.Font.Bold = true;
wsA.View.ShowGridLines = true;
//wsA.Cells["A:D"].Style.Numberformat.Format = null;
//wsA.Cells["B:B"].Style.Numberformat.Format = "0.00";
//wsA.Cells[1, 1].Value = "AA";
//wsA.Cells[1, 2].Value = "BB";
//wsA.Cells[1, 3].Value = "CC";
//wsA.Cells[1, 4].Value = "DD";
if (dtMain != null && dtMain.Rows.Count > 1)
{
int row = wsA.Dimension.End.Row + 1;
//wsA.Cells[2, 4, row + 1, 4].Style.Numberformat.Format = "dd-mm-yyyy hh:mm:ss AM/PM";
wsA.Cells[2, 4, row + 1, 4].Style.Numberformat.Format = "dd-mm-yyyy";
wsA.Cells[2, 5, row + 1, 5].Style.Numberformat.Format = "dd-mm-yyyy";
//wsA.Cells[2, 21, row + 1, 21].Style.Numberformat.Format = "#,##0.00";
//wsA.Cells[2, 23, row + 1, 26].Style.Numberformat.Format = "#,##0.00";
//wsA.Cells[row + 1, 1].Value = "TOTAL";
//wsA.Cells[row + 1, 21, row + 1, 21].Formula = string.Format("=SUM(U{0}:U{1})", 12, row - 1);
//wsA.Cells[row + 1, 23, row + 1, 26].Formula = string.Format("=SUM(W{0}:W{1})", 12, row - 1);
//wsA.Cells[row + 1, 23, row + 1, 26].Style.Numberformat.Format = "#,##0.00";
}
wsA.View.FreezePanes(12, 1);
//var tbl1 = wsA.Tables[0];
if (range != null)
{
range.AutoFitColumns();
}
// tbl1.ShowFilter = false;
//wsA.Cells[wsA.Dimension.Address].AutoFilter = true;
}
catch (Exception ex)
{
LogFunction2.WriteMaintenaceLogToFile(string.Format("Maintenance Service Error on: {0}-{1} --", "SETT_GEN", sett) + "{0}" + ex.Message + ex.StackTrace);
}
dtMain.Dispose();
//try
//{
// dtMain = generateDS("GEN", "U", sett, reportPath, logopath,null);
// var wsA2 = package.Workbook.Worksheets.Add("SETTLEMENT REPORT");
// //ExcelRange cols = wsA2.Cells["A:XFD"];
// //wsA2.Cells["A:XFD"].Style.Fill.PatternType = ExcelFillStyle.Solid;
// //wsA2.Cells["A:XFD"].Style.Fill.BackgroundColor.SetColor(Color.White);
// var range2 = wsA2.Cells["A11"].LoadFromDataTable(dtMain, true);
// wsA2.Row(11).Style.Fill.PatternType = ExcelFillStyle.Solid;
// wsA2.Row(11).Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Orange);
// //wsA2.Tables.Add(range2, "data2");
// System.Drawing.Image myImage = System.Drawing.Image.FromFile(logopath);
// var pic = wsA2.Drawings.AddPicture("LOGO", myImage);
// pic.SetPosition(0, 0, 0, 0);
// //Write the headers and style them
// wsA2.Cells["A2"].Value = "Xpress Payment Services Ltd";
// wsA2.Cells["A2"].Style.Font.Size = 18;
// wsA2.Cells["A2"].Style.Font.Bold = true;
// wsA2.Cells["A2:M2"].Merge = true;
// wsA2.Cells["A2:M2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
// wsA2.Cells["A3"].Value = "SETTLEMENT DETAIL REPORT ";
// wsA2.Cells["A3"].Style.Font.Size = 14;
// wsA2.Cells["A3"].Style.Font.Bold = true;
// wsA2.Cells["A3:M3"].Merge = true;
// wsA2.Cells["A3:M3"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
// wsA2.Cells["A5"].Value = "SETTLEMENT DATE: " + sett;
// //wsA2.Cells["A5"].Value = "MERCHANT NAME: " + MERCHANTNAME;
// wsA2.Cells["A5"].Style.Font.Size = 10;
// wsA2.Cells["A5:F5"].Merge = true;
// //wsA2.Cells["A6"].Value = "ADDRESS: " + ADDRESS;
// wsA2.Cells["A6"].Style.Font.Size = 10;
// wsA2.Cells["A6:F6"].Merge = true;
// // wsA2.Cells["A7"].Value = "SETTLEMENT CURRENCY " + SettlementCur;
// wsA2.Cells["A7"].Style.Font.Size = 10;
// wsA2.Cells["A7"].Style.Font.Bold = true;
// if (dtMain != null && dtMain.Rows.Count > 1)
// {
// //int row = wsA2.Dimension.End.Row + 1;
// //wsA2.Cells[row + 1, 1].Value = "TOTAL";
// //wsA2.Cells[row + 1, 2, row + 1, 4 - 1].Formula = string.Format("=SUM(B{0}:B{1})", 12, row - 1);
// //wsA2.Cells[row + 1, 2, row + 1, 4 - 1].Style.Numberformat.Format = "#,##0.00";
// }
// wsA2.View.FreezePanes(12, 1);
// // var tbl2 = wsA2.Tables[0];
// if (range2 != null)
// {
// range2.AutoFitColumns();
// }
// // tbl2.ShowFilter = false;
// //wsA2.Cells[wsA2.Dimension.Address].AutoFilter = true;
//}
//catch (Exception ex)
//{
// LogFunction2.WriteMaintenaceLogToFile(string.Format("Maintenance Service Error on: {0}-{1} --", "SETT_ALL", sett) + "{0}" + ex.Message + ex.StackTrace);
//}
//dtMain.Dispose();
package.SaveAs(newFile);
stream.Dispose();
package.Dispose();
}
return(newFile.FullName);
}
public static OutPutObj GenerateNaps(DateTime sett_date, int channelid, int ID, string bid, string reqType, string userId)
{
var cnt = 0;
var rowCnt = 0;
var curDate = DateTime.Now;
DynamicParameters p;
using (var con = new RepoBase().OpenConnection(null))
{
p = new DynamicParameters();
p.Add("ReqType", reqType, DbType.String);
p.Add("SetDate", sett_date, DbType.Date);
var tt = con.Query <OutPutObj>("proc_ValidateNapsNibss", p, commandType: CommandType.StoredProcedure).FirstOrDefault();
if (tt.RespCode == 1)
{
return(tt);
}
// var dtMain = rptSett.generateDS("", "", "NIBSS_ALL", "U", "NAPS", sett_date.ToString("yyyy-MM-dd"), null, null,null);
dsLoadclass dv = new dsLoadclass();
if (ID == 1)
{
var dtMain = dv.generateDS("", "", "NIBSS_ALL", "U", "NAPS", sett_date.ToString("yyyy-MM-dd"), channelid);
p = new DynamicParameters();
p.Add("USERID", userId, DbType.String);
var t2 = con.Execute("SESS_PURGE_NAPS", p, commandType: CommandType.StoredProcedure);
int sqn = 0;
rowCnt = dtMain.Rows.Count;
foreach (DataRow dsrow in dtMain.Rows)
{
sqn++;
p = new DynamicParameters();
NapsObj obj = new NapsObj()
{
DEBITACCTNO = dsrow[0].ToString(),
DEBITBANKCODE = dsrow[1].ToString(),
BENEFICIARYNAME = dsrow[2].ToString(),
BENEFICIARYACCTNO = dsrow[3].ToString(),
BENEFICIARYBANKCODE = dsrow[4].ToString(),
CREDITAMOUNT = decimal.Parse(dsrow[5].ToString()),
BENEFICIARYNARRATION = dsrow[6].ToString(),
BATCHID = bid,
CREATEDATE = curDate,
REQUESTTYPE = reqType,
SETTLEMENTDATE = sett_date,
USERID = userId,
MERCHANTID = dsrow[7].ToString(),//TAKE NOTE
};
p.Add("DEBITACCTNO", obj.DEBITACCTNO, DbType.String);
p.Add("DEBITBANKCODE", obj.DEBITBANKCODE, DbType.String);
p.Add("BENEFICIARYACCTNO", obj.BENEFICIARYACCTNO, DbType.String);
p.Add("BENEFICIARYBANKCODE", obj.BENEFICIARYBANKCODE, DbType.String);
p.Add("BENEFICIARYNAME", obj.BENEFICIARYNAME, DbType.String);
p.Add("BENEFICIARYNARRATION", obj.BENEFICIARYNARRATION, DbType.String);
p.Add("CREDITAMOUNT", obj.CREDITAMOUNT, DbType.Decimal);
p.Add("SETTLEMENTDATE", obj.SETTLEMENTDATE, DbType.Date);
p.Add("MERCHANTID", obj.MERCHANTID, DbType.String);
p.Add("REQUESTTYPE", obj.REQUESTTYPE, DbType.String);
p.Add("USERID", obj.USERID, DbType.String);
p.Add("CREATEDATE", obj.CREATEDATE, DbType.DateTime);
p.Add("BATCHID", obj.BATCHID, DbType.String);
p.Add("REASON", obj.REASON, DbType.String);
p.Add("PID", obj.PID, DbType.String);
p.Add("POSTTYPE", 1, DbType.String);
p.Add("EVENTTYPE", "New", DbType.String);
p.Add("@VALIDATIONERRORMESSAGE", obj.VALIDATIONERRORMESSAGE, DbType.String);
p.Add("@VALIDATIONERRORSTATUS", obj.VALIDATIONERRORSTATUS, DbType.String);
p.Add("@WARNINGMESSAGE", obj.WARNINGMESSAGE, DbType.String);
p.Add("PostSequence", sqn++, DbType.Int32);
cnt += con.Execute("SESS_POST_NAPS", p, commandType: CommandType.StoredProcedure);
}
}
else
{
var dtMain = dv.generateDS("", "", "NIBSS_ALL", "U", "NEFT", sett_date.ToString("yyyy-MM-dd"), channelid);
p = new DynamicParameters();
p.Add("USERID", userId, DbType.String);
var t2 = con.Execute("SESS_PURGE_NAPS", p, commandType: CommandType.StoredProcedure);
int sqn = 0;
rowCnt = dtMain.Rows.Count;
foreach (DataRow dsrow in dtMain.Rows)
{
sqn++;
p = new DynamicParameters();
NapsObj obj = new NapsObj()
{
DEBITACCTNO = dsrow[0].ToString(),
DEBITBANKCODE = dsrow[1].ToString(),
BENEFICIARYNAME = dsrow[2].ToString(),
BENEFICIARYACCTNO = dsrow[3].ToString(),
BENEFICIARYBANKCODE = dsrow[4].ToString(),
CREDITAMOUNT = decimal.Parse(dsrow[5].ToString()),
BENEFICIARYNARRATION = dsrow[6].ToString(),
BATCHID = bid,
CREATEDATE = curDate,
REQUESTTYPE = reqType,
SETTLEMENTDATE = sett_date,
USERID = userId,
MERCHANTID = dsrow[7].ToString(),//TAKE NOTE
};
p.Add("DEBITACCTNO", obj.DEBITACCTNO, DbType.String);
p.Add("DEBITBANKCODE", obj.DEBITBANKCODE, DbType.String);
p.Add("BENEFICIARYACCTNO", obj.BENEFICIARYACCTNO, DbType.String);
p.Add("BENEFICIARYBANKCODE", obj.BENEFICIARYBANKCODE, DbType.String);
p.Add("BENEFICIARYNAME", obj.BENEFICIARYNAME, DbType.String);
p.Add("BENEFICIARYNARRATION", obj.BENEFICIARYNARRATION, DbType.String);
p.Add("CREDITAMOUNT", obj.CREDITAMOUNT, DbType.Decimal);
p.Add("SETTLEMENTDATE", obj.SETTLEMENTDATE, DbType.Date);
p.Add("MERCHANTID", obj.MERCHANTID, DbType.String);
p.Add("REQUESTTYPE", obj.REQUESTTYPE, DbType.String);
p.Add("USERID", obj.USERID, DbType.String);
p.Add("CREATEDATE", obj.CREATEDATE, DbType.DateTime);
p.Add("BATCHID", obj.BATCHID, DbType.String);
p.Add("REASON", obj.REASON, DbType.String);
p.Add("PID", obj.PID, DbType.String);
p.Add("POSTTYPE", 1, DbType.String);
p.Add("EVENTTYPE", "New", DbType.String);
p.Add("@VALIDATIONERRORMESSAGE", obj.VALIDATIONERRORMESSAGE, DbType.String);
p.Add("@VALIDATIONERRORSTATUS", obj.VALIDATIONERRORSTATUS, DbType.String);
p.Add("@WARNINGMESSAGE", obj.WARNINGMESSAGE, DbType.String);
p.Add("PostSequence", sqn++, DbType.Int32);
cnt += con.Execute("SESS_POST_NAPS", p, commandType: CommandType.StoredProcedure);
}
}
OutPutObj ret = new OutPutObj();
//==========================================
if (rowCnt == 0)
{
ret = new OutPutObj()
{
RespCode = 1,
RespMessage = "No Record found for selected date."
};
}
else if (cnt == rowCnt)
{
ret = new OutPutObj()
{
RespCode = 0,
RespMessage = ""
};
}
else
{
ret = new OutPutObj()
{
RespCode = 1,
RespMessage = ""
};
}
return(ret);
}
}
