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