//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 + "_MDB_MERCHANTS.xlsx";
// string oISSUERPath = System.IO.Path.Combine(ISSUERPath, "MERCHANTBANK");
// 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
// {
// }
// generateDSReport(reportFor, ID, null, "MDB_MERCH", reportFolderType, "B", sett, reportISSUERPath, logopath);
// return null;
// ////FileInfo newFile = new FileInfo(reportISSUERPath);
// ////MemoryStream stream = new MemoryStream();
// ////using (ExcelPackage package = new ExcelPackage(stream))
// ////{
// //// try
// //// {
// //// var ws = package.Workbook.Worksheets.Add("DEPOSIT BANK MERCHANTS");
// //// ws = generateReport(ws, logopath, reportFor, ID, null, "MDB_MERCH", reportFolderType, "B", sett, 8);
// //// }
// //// catch (Exception ex)
// //// {
// //// }
// //// package.Compression = CompressionLevel.BestSpeed;
// //// package.SaveAs(newFile);
// //// Stream.Dispose();:package.Dispose();
// ////}
// ////return newFile.FullName;
//}
public static string generateDSReport(string reportFor, string ID, string cardScheme, string reportType, string DOMINT, string reportClass, string sett, string ReportPath, string logopath)
{
try
{
OracleConnection Standby_connection = new OracleConnection(oradb);
string qry = string.Empty;
if (DOMINT == "DOM")
{
qry = "RPT_SETTLEMETDETAIL";
}
else
{
qry = "RPT_SETTLEMETDETAIL_INT";
}
OracleCommand cmd = new OracleCommand();
cmd.Connection = Standby_connection;
// var dr = default(OracleDataReader);
if (Standby_connection == null)
{
Standby_connection = new OracleConnection(oradb);
}
if (Standby_connection.State != ConnectionState.Open)
{
Standby_connection.Open();
}
cmd.Connection = Standby_connection;
cmd.CommandText = qry;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter(":P_searchID", OracleDbType.Varchar2, ParameterDirection.Input)).Value = ID;
cmd.Parameters.Add(new OracleParameter(":P_CardScheme", OracleDbType.Varchar2, ParameterDirection.Input)).Value = cardScheme;
cmd.Parameters.Add(new OracleParameter(":P_reporttype", OracleDbType.Varchar2, ParameterDirection.Input)).Value = reportType;
cmd.Parameters.Add(new OracleParameter(":P_reportClass", OracleDbType.Varchar2, ParameterDirection.Input)).Value = reportClass;
cmd.Parameters.Add(new OracleParameter(":P_SETT", OracleDbType.Varchar2, ParameterDirection.Input)).Value = sett;
cmd.Parameters.Add(new OracleParameter(":CURSOR_ ", OracleDbType.RefCursor, ParameterDirection.Output));
try
{
DataTable dt = new DataTable();
DataSet ds = new DataSet();
var dr = cmd.ExecuteReader();
dsExcel drReport = new dsExcel();
dt.Load(dr);
ds.Tables.Add(dt);
cmd.Dispose();
drReport.ExportDataTOExcel(ds, ReportPath, reportFor, null, 0);
}
catch
{
}
cmd.Dispose();
}
catch (Exception ex)
{
//Console.WriteLine(ex.Message );
}
return(null);
}
public static ExcelWorksheet generateReportSUMM(ExcelWorksheet ws, string img, string rName, string ID, string cardScheme, string reportType, string DOMINT, string reportClass, 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 = "ISSUER REPORT 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
{
OracleConnection Standby_connection = new OracleConnection(oradb);
string qry = string.Empty;
if (DOMINT == "DOM")
{
qry = "RPT_SETTLEMETDETAIL";
}
else
{
qry = "RPT_SETTLEMETDETAIL_INT";
}
OracleCommand cmd = new OracleCommand();
cmd.Connection = Standby_connection;
// var dr = default(OracleDataReader);
if (Standby_connection == null)
{
Standby_connection = new OracleConnection(oradb);
}
if (Standby_connection.State != ConnectionState.Open)
{
Standby_connection.Open();
}
cmd.Connection = Standby_connection;
cmd.CommandText = qry;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter(":P_searchID", OracleDbType.Varchar2, ParameterDirection.Input)).Value = ID;
cmd.Parameters.Add(new OracleParameter(":P_CardScheme", OracleDbType.Varchar2, ParameterDirection.Input)).Value = cardScheme;
cmd.Parameters.Add(new OracleParameter(":P_reporttype", OracleDbType.Varchar2, ParameterDirection.Input)).Value = reportType;
cmd.Parameters.Add(new OracleParameter(":P_reportClass", OracleDbType.Varchar2, ParameterDirection.Input)).Value = reportClass;
cmd.Parameters.Add(new OracleParameter(":P_SETT", OracleDbType.Varchar2, ParameterDirection.Input)).Value = sett;
cmd.Parameters.Add(new OracleParameter(":CURSOR_ ", OracleDbType.RefCursor, ParameterDirection.Output));
using (var dr = cmd.ExecuteReader())
{
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++;
}
}
cmd.Dispose();
}
}
catch (Exception ex)
{
//Console.WriteLine(ex.Message );
}
ws.Cells[2, 2, row + 1, 4].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.Cells[row + 3, 1].Value = "NOTE:";
ws.Cells[row + 3, 2].Value = "ISSUER OBLIGATION INCLUDES PROCESSING FEE WHERE APPLICABLE:";
ws.Select("C8");
return(ws);
}
public static ExcelWorksheet generateIssuer(ExcelWorksheet ws, string img, string rName, string ID, string cardScheme, string reportType, string reportClass, 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["A1"].Value = "UNIFIED PAYMENTS SETTLEMENT DASHBOARD";
ws.Cells["A1"].Style.Font.Size = 18;
ws.Cells["A1"].Style.Font.Bold = true;
ws.Cells["A1:Z1"].Merge = true;
ws.Cells["A1:Z1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
ws.Cells["A3"].Value = "SETTLEMENT DATE: " + sett;
ws.Cells["A5"].Value = "SN";
ws.Cells["A5:A6"].Merge = true;
ws.Cells["A5:A6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
ws.Cells["B5"].Value = "BANKS";
ws.Cells["B5:B6"].Merge = true;
ws.Cells["B5:B6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
ws.Cells["B5:B6"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["B5:B6"].Style.Fill.BackgroundColor.SetColor(Color.Gray);
ws.Cells["C5"].Value = "ALL MERCHANTs";
ws.Cells["C5:E5"].Merge = true;
ws.Cells["C5:E5"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
ws.Cells["C5:E5"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["C5:E5"].Style.Fill.BackgroundColor.SetColor(Color.SkyBlue);
ws.Cells["C6"].Value = "TOTAL DEBIT";
ws.Cells["C6"].Style.Font.Color.SetColor(Color.Red);
ws.Cells["D6"].Value = "TOTAL CREDIT";
ws.Cells["E6"].Value = "NET";
ws.Cells["F5"].Value = "PTSPs";
ws.Cells["F5:H5"].Merge = true;
ws.Cells["F5:H5"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
ws.Cells["F5:H5"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["F5:H5"].Style.Fill.BackgroundColor.SetColor(Color.Orange);
ws.Cells["F6"].Value = "TOTAL DEBIT";
ws.Cells["F6"].Style.Font.Color.SetColor(Color.Red);
ws.Cells["G6"].Value = "TOTAL CREDIT";
ws.Cells["H6"].Value = "NET";
ws.Cells["I5"].Value = "TERMINAL OWNERs";
ws.Cells["I5:K5"].Merge = true;
ws.Cells["I5:K5"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
ws.Cells["I5:K5"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["I5:K5"].Style.Fill.BackgroundColor.SetColor(Color.SkyBlue);
ws.Cells["I6"].Value = "TOTAL DEBIT";
ws.Cells["I6"].Style.Font.Color.SetColor(Color.Red);
ws.Cells["J6"].Value = "TOTAL CREDIT";
ws.Cells["K6"].Value = "NET";
////Console.WriteLine("loading excel cell");
ws.Cells["L5"].Value = "PTSAs";
ws.Cells["L5:N5"].Merge = true;
ws.Cells["L5:N5"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
ws.Cells["L5:N5"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["L5:N5"].Style.Fill.BackgroundColor.SetColor(Color.Orange);
ws.Cells["L6"].Value = "TOTAL DEBIT";
ws.Cells["L6"].Style.Font.Color.SetColor(Color.Red);
ws.Cells["M6"].Value = "TOTAL CREDIT";
ws.Cells["N6"].Value = "NET";
ws.Cells["O5"].Value = "ACQUIRING OBLIGATION";
ws.Cells["O5:Q5"].Merge = true;
ws.Cells["O5:Q5"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
ws.Cells["O5:Q5"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["O5:Q5"].Style.Fill.BackgroundColor.SetColor(Color.SkyBlue);
ws.Cells["O6"].Value = "TOTAL DEBIT";
ws.Cells["O6"].Style.Font.Color.SetColor(Color.Red);
ws.Cells["P6"].Value = "TOTAL CREDIT";
ws.Cells["Q6"].Value = "NET";
ws.Cells["R5"].Value = "ISSUEING OBLIGATION";
ws.Cells["R5:T5"].Merge = true;
ws.Cells["R5:T5"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
ws.Cells["R5:T5"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["R5:T5"].Style.Fill.BackgroundColor.SetColor(Color.Orange);
ws.Cells["R6"].Value = "TOTAL DEBIT";
ws.Cells["R6"].Style.Font.Color.SetColor(Color.Red);
ws.Cells["S6"].Value = "TOTAL CREDIT";
ws.Cells["T6"].Value = "NET";
ws.Cells["U5"].Value = "OTHERS";
ws.Cells["U5:W5"].Merge = true;
ws.Cells["U5:W5"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
ws.Cells["U5:W5"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["U5:W5"].Style.Fill.BackgroundColor.SetColor(Color.SkyBlue);
ws.Cells["U6"].Value = "TOTAL DEBIT";
ws.Cells["U6"].Style.Font.Color.SetColor(Color.Red);
ws.Cells["V6"].Value = "TOTAL CREDIT";
ws.Cells["W6"].Value = "NET";
ws.Cells["X5"].Value = "TOTAL";
ws.Cells["X5:Z5"].Merge = true;
ws.Cells["X5:Z5"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
ws.Cells["X5:Z5"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["X5:Z5"].Style.Fill.BackgroundColor.SetColor(Color.Orange);
ws.Cells["X6"].Value = "TOTAL DEBIT";
ws.Cells["X6"].Style.Font.Color.SetColor(Color.Red);
ws.Cells["Y6"].Value = "TOTAL CREDIT";
ws.Cells["Z6"].Value = "NET";
ws.View.FreezePanes(1, 1);
// using (var rng = ws.Cells["A" + (startRow - 1) + ":Z" + (startRow - 1)])
int row = startRow;
try
{
OracleConnection Standby_connection = new OracleConnection(oradb);
string qry = "RPT_SETTLEMETDETAIL";
OracleCommand cmd = new OracleCommand();
cmd.Connection = Standby_connection;
// var dr = default(OracleDataReader);
if (Standby_connection == null)
{
Standby_connection = new OracleConnection(oradb);
}
if (Standby_connection.State != ConnectionState.Open)
{
Standby_connection.Open();
}
cmd.Connection = Standby_connection;
cmd.CommandText = qry;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter(":P_searchID", OracleDbType.Varchar2, ParameterDirection.Input)).Value = ID;
cmd.Parameters.Add(new OracleParameter(":P_CardScheme", OracleDbType.Varchar2, ParameterDirection.Input)).Value = cardScheme;
cmd.Parameters.Add(new OracleParameter(":P_reporttype", OracleDbType.Varchar2, ParameterDirection.Input)).Value = reportType;
cmd.Parameters.Add(new OracleParameter(":P_reportClass", OracleDbType.Varchar2, ParameterDirection.Input)).Value = reportClass;
cmd.Parameters.Add(new OracleParameter(":P_SETT", OracleDbType.Varchar2, ParameterDirection.Input)).Value = sett;
cmd.Parameters.Add(new OracleParameter(":CURSOR_ ", OracleDbType.RefCursor, ParameterDirection.Output));
using (var dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
while (dr.Read())
{
//////Console.WriteLine("reading excel cell");
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, 6, dr[4]);
ws.SetValue(row, 7, dr[5]);
ws.SetValue(row, 9, dr[6]);
ws.SetValue(row, 10, dr[7]);
ws.SetValue(row, 12, dr[8]);
ws.SetValue(row, 13, dr[9]);
ws.SetValue(row, 15, dr[10]);
ws.SetValue(row, 16, dr[11]);
ws.SetValue(row, 18, dr[12]);
ws.SetValue(row, 19, dr[13]);
row++;
}
}
cmd.Dispose();
}
}
catch (Exception ex)
{
//Console.WriteLine(ex.Message );
}
ws.Cells[startRow, 5, row, 5].FormulaR1C1 = "RC[-2]-RC[-1]";
ws.Cells[startRow, 8, row, 8].FormulaR1C1 = "RC[-2]-RC[-1]";
ws.Cells[startRow, 11, row, 11].FormulaR1C1 = "RC[-2]-RC[-1]";
ws.Cells[startRow, 14, row, 14].FormulaR1C1 = "RC[-2]-RC[-1]";
ws.Cells[startRow, 17, row, 17].FormulaR1C1 = "RC[-2]-RC[-1]";
ws.Cells[startRow, 20, row, 20].FormulaR1C1 = "RC[-2]-RC[-1]";
ws.Cells[startRow, 24, row, 24].FormulaR1C1 = "RC[-21]+RC[-18]+RC[-15]+RC[-12]+RC[-9]+RC[-6]+RC[-3]";
ws.Cells[startRow, 25, row, 25].FormulaR1C1 = "RC[-21]+RC[-18]+RC[-15]+RC[-12]+RC[-9]+RC[-6]+RC[-3]";
ws.Cells[startRow, 26, row, 26].FormulaR1C1 = "RC[-21]+RC[-18]+RC[-15]+RC[-12]+RC[-9]+RC[-6]+RC[-3]";
//Add a sum at the end
using (var rng = ws.Cells["A5:Z" + row])
{
// rng.Style.Font.Bold = true;
rng.Style.Border.Top.Style = ExcelBorderStyle.Thin;
rng.Style.Border.Left.Style = ExcelBorderStyle.Thin;
rng.Style.Border.Right.Style = ExcelBorderStyle.Thin;
rng.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
//rng.Style.Border = "2pt";
}
ws.Cells[row + 1, 1].Value = "GRAND TOTAL";
ws.Cells[row + 1, 5].Formula = string.Format("Sum({0})", new ExcelAddress(1, 5, row, 5).Address);
ws.Cells[row + 1, 8].Formula = string.Format("Sum({0})", new ExcelAddress(1, 8, row, 8).Address);
ws.Cells[row + 1, 11].Formula = string.Format("Sum({0})", new ExcelAddress(1, 11, row, 11).Address);
ws.Cells[row + 1, 14].Formula = string.Format("Sum({0})", new ExcelAddress(1, 14, row, 14).Address);
ws.Cells[row + 1, 17].Formula = string.Format("Sum({0})", new ExcelAddress(1, 17, row, 17).Address);
ws.Cells[row + 1, 20].Formula = string.Format("Sum({0})", new ExcelAddress(1, 20, row, 20).Address);
ws.Cells[startRow, 3, row, 26].Style.Numberformat.Format = "#,##0.00";
ws.Cells[row + 1, 3, row + 1, 26].Style.Font.Bold = true;
//////Format the date and numeric columns
ws.Cells[row, 1, row, 26].AutoFitColumns(20);
ws.Select("A1");
return(ws);
}
