You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
498 lines
19 KiB
498 lines
19 KiB
using OfficeOpenXml;
|
|
using OfficeOpenXml.Style;
|
|
using OfficeOpenXml.Table;
|
|
using Swift.DAL.BL.Remit.Transaction;
|
|
using Swift.web.Library;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Globalization;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using System.Text.RegularExpressions;
|
|
using System.Web;
|
|
|
|
namespace Swift.web.RemittanceSystem.RemittanceReports
|
|
{
|
|
public class ReportDownload
|
|
{
|
|
private string fieldFormatExcel = "";
|
|
public bool UserDefinedHeader { get; set; }
|
|
public int DataStartingRow = 0;
|
|
public bool ShowTableFilter { get; set; }
|
|
public int TotalColumns { get; set; }
|
|
public int TotalRows { get; set; }
|
|
public bool CalculateTotal { get; set; }
|
|
public string TotalTextValue { get; set; }
|
|
public string PrepareReportFormat()
|
|
{
|
|
|
|
string reportName = GetStatic.ReadQueryString("reportName", "").ToLower();
|
|
string path = "";
|
|
switch (reportName)
|
|
{
|
|
case "accountingsummary":
|
|
path = PrepareAccountingSummaryReport();
|
|
break;
|
|
case "customerandtxnlist":
|
|
path = PrepareCustomerAndTxnList();
|
|
break;
|
|
case "customerregistration":
|
|
path = PrepareCustomerRegistrationReport();
|
|
break;
|
|
}
|
|
return path;
|
|
|
|
}
|
|
public string PrepareCustomerRegistrationReport()
|
|
{
|
|
this.UserDefinedHeader = false;
|
|
this.ShowTableFilter = false;
|
|
this.fieldFormatExcel = "T|T|T|T|T|T|T|D|D|T|T|T|D|T|T|D|T|T|T|T|T|T|T";
|
|
|
|
string fromDate = GetStatic.ReadQueryString("from", "");
|
|
string toDate = GetStatic.ReadQueryString("to", "");
|
|
string sAgent = GetStatic.ReadQueryString("sAgent", "");
|
|
string sBranch = GetStatic.ReadQueryString("sBranch", "");
|
|
string withAgent = GetStatic.ReadQueryString("withAgent", "");
|
|
DataSet dataset = (new TranAgentReportDao().GetNewRegistrationReport(GetStatic.GetUser(), "customer", fromDate, toDate, sAgent, sBranch, withAgent)).Result;
|
|
this.TotalColumns = dataset.Tables[0].Columns.Count;
|
|
this.TotalRows = dataset.Tables[0].Rows.Count;
|
|
return DataSetToExcelNew(dataset, "CustomerRegistration");
|
|
|
|
}
|
|
public string PrepareCustomerAndTxnList()
|
|
{
|
|
this.UserDefinedHeader = false;
|
|
this.ShowTableFilter = false;
|
|
|
|
string fromDate = GetStatic.ReadQueryString("fromDate", "");
|
|
string toDate = GetStatic.ReadQueryString("toDate", "");
|
|
string searchBy = GetStatic.ReadQueryString("searchBy", "");
|
|
string groupBy = GetStatic.ReadQueryString("groupBy", "");
|
|
|
|
if(searchBy == "detail")
|
|
{
|
|
this.fieldFormatExcel = "D|T|T|D|D|T|D|T|D|T|T|T|T|T|T|D|D|T|T|T|T|D|D|D|M|M|M|M|M|T|T|T|T|T|T|T|T";
|
|
}
|
|
else if (searchBy == "summary" && groupBy == "tDate")
|
|
{
|
|
this.fieldFormatExcel = "D|T|";
|
|
}
|
|
else
|
|
{
|
|
this.fieldFormatExcel = "T|T|";
|
|
}
|
|
DataSet dataset = (new TranAgentReportDao().GetCustomerAndTxnList(GetStatic.GetUser(), "s-customerAndTxn", fromDate, toDate, searchBy, groupBy)).Result;
|
|
this.TotalColumns = dataset.Tables[0].Columns.Count;
|
|
this.TotalRows = dataset.Tables[0].Rows.Count;
|
|
return DataSetToExcelNew(dataset, "DetailTransactionReport");
|
|
}
|
|
public string PrepareAccountingSummaryReport()
|
|
{
|
|
this.UserDefinedHeader = true;
|
|
this.ShowTableFilter = false;
|
|
this.fieldFormatExcel = "D|M|M|M|M|M|M|M|M|M|M|M|M|M|M|M|M|M|M|M|M|M|M|M|M|M|M|M|M|M|M";
|
|
this.DataStartingRow = 4;
|
|
this.CalculateTotal = true;
|
|
this.TotalTextValue = "Total";
|
|
|
|
string fromDate = GetStatic.ReadQueryString("fromDate", "");
|
|
string toDate = GetStatic.ReadQueryString("toDate", "");
|
|
DataSet dataset = (new TranAgentReportDao().GetAccountingSummary(GetStatic.GetUser(), "RPT", fromDate, toDate)).Result;
|
|
this.TotalColumns = dataset.Tables[0].Columns.Count;
|
|
this.TotalRows = dataset.Tables[0].Rows.Count;
|
|
return DataSetToExcelNew(dataset, "AccountingReport");
|
|
}
|
|
public string DataSetToExcelNew(DataSet ds, string strFileName)
|
|
{
|
|
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
|
|
ExcelPackage excelPackg = new ExcelPackage();
|
|
string fileName = strFileName; // Guid.NewGuid().ToString();
|
|
var formatArr = fieldFormatExcel.Split('|');
|
|
|
|
ExcelWorksheet workSheet = excelPackg.Workbook.Worksheets.Add("Sheet1");
|
|
if (this.UserDefinedHeader == true)
|
|
{
|
|
PrepareUserDefinedHeader(workSheet, ds);
|
|
}
|
|
else
|
|
{
|
|
PrepareHeader(workSheet, ds, strFileName);
|
|
}
|
|
PopulateRowData(workSheet, ds);
|
|
|
|
if (CalculateTotal == true)
|
|
{
|
|
SetTotalFormula(workSheet, this.TotalRows + DataStartingRow, this.TotalColumns);
|
|
}
|
|
|
|
workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();
|
|
|
|
string path = GetStatic.ReadWebConfig("root", "") + @"\ExcelSample\" + GetStatic.GetUser() + @"\";
|
|
if (!Directory.Exists(path))
|
|
Directory.CreateDirectory(path);
|
|
|
|
File.Delete(path + fileName + ".xlsx");
|
|
|
|
excelPackg.SaveAs(new FileInfo(path + fileName + ".xlsx"));
|
|
return GetStatic.GetUrlRoot() + @"\ExcelSample\" + GetStatic.GetUser() + @"\" + fileName + ".xlsx";
|
|
}
|
|
public void SetTotalFormula(ExcelWorksheet workSheet, int endingIndex, int totalColumn)
|
|
{
|
|
SetFormatForTotal(workSheet, endingIndex, totalColumn);
|
|
CalaulateTotalValue(workSheet, endingIndex, totalColumn);
|
|
|
|
}
|
|
public void SetFormatForTotal(ExcelWorksheet workSheet, int endingIndex, int totalColumn)
|
|
{
|
|
using (ExcelRange Rng = workSheet.Cells[GetCellRangeNew(0, endingIndex)])
|
|
{
|
|
Rng.Style.Numberformat.Format = "@";
|
|
Rng.Value = TotalTextValue;
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
|
|
for (int i = 1; i < totalColumn; i++)
|
|
{
|
|
using (ExcelRange Rng = workSheet.Cells[GetCellRangeNew(i, endingIndex)])
|
|
{
|
|
Rng.Style.Numberformat.Format = "#,##0.00"; ;
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
}
|
|
}
|
|
public void CalaulateTotalValue(ExcelWorksheet workSheet, int endingIndex, int totalColumn)
|
|
{
|
|
for (int i = 1; i < totalColumn; i++)
|
|
{
|
|
workSheet.Cells[GetCellRangeNew(i, endingIndex)].Formula = "=SUM(" + GetCellRangeNew(i, 4) + ":" + GetCellRangeNew(i, endingIndex - 1) + ")";
|
|
}
|
|
}
|
|
public void PopulateRowData(ExcelWorksheet workSheet, DataSet ds)
|
|
{
|
|
try
|
|
{
|
|
int j = (this.DataStartingRow > 0) ? this.DataStartingRow : 2;
|
|
var fieldFormatArray = fieldFormatExcel.Split('|');
|
|
int k = 0;
|
|
foreach (DataRow item in ds.Tables[0].Rows)
|
|
{
|
|
for (int i = 0; i < this.TotalColumns; i++)
|
|
{
|
|
using (ExcelRange Rng = workSheet.Cells[GetCellRangeNew(i, j)])
|
|
{
|
|
string columnName = ds.Tables[0].Columns[i].ToString();
|
|
if (fieldFormatArray[i] == "D")
|
|
{
|
|
Rng.Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
|
|
Rng.Value = GetStatic.ParseDateTime(ds.Tables[0].Rows[k][columnName].ToString());
|
|
}
|
|
else if (fieldFormatArray[i] == "M")
|
|
{
|
|
Rng.Style.Numberformat.Format = "#,##0.00";
|
|
Rng.Value = GetStatic.ParseDouble(ds.Tables[0].Rows[k][columnName].ToString());
|
|
}
|
|
else
|
|
{
|
|
Rng.Style.Numberformat.Format = "@";
|
|
Rng.Value = ds.Tables[0].Rows[k][columnName].ToString();
|
|
}
|
|
}
|
|
}
|
|
j++;
|
|
k++;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
|
|
}
|
|
|
|
}
|
|
public void PrepareUserDefinedHeader(ExcelWorksheet workSheet, DataSet ds)
|
|
{
|
|
string reportName = GetStatic.ReadQueryString("reportName", "").ToLower();
|
|
switch (reportName)
|
|
{
|
|
case "accountingsummary":
|
|
PrepareHeaderOfAccountingSummary(workSheet, ds);
|
|
break;
|
|
}
|
|
|
|
}
|
|
|
|
public void PrepareHeader(ExcelWorksheet workSheet, DataSet ds, string strFileName)
|
|
{
|
|
var totalColumnName = ExcelColumnFromNumber(this.TotalColumns);
|
|
this.TotalRows = this.TotalRows + 1;
|
|
using (ExcelRange Rng = workSheet.Cells["A1:" + totalColumnName + "" + this.TotalRows + ""])
|
|
{
|
|
ExcelTableCollection tblcollection = workSheet.Tables;
|
|
ExcelTable table = tblcollection.Add(Rng, strFileName);
|
|
//Set Columns position & name
|
|
for (int i = 0; i < this.TotalColumns; i++)
|
|
{
|
|
table.Columns[i].Name = ds.Tables[0].Columns[i].ToString();
|
|
}
|
|
|
|
table.ShowFilter = ShowTableFilter;
|
|
|
|
table.TableStyle = TableStyles.Dark9;
|
|
}
|
|
}
|
|
|
|
public static string ExcelColumnFromNumber(int column)
|
|
{
|
|
string columnString = "";
|
|
decimal columnNumber = column;
|
|
while (columnNumber > 0)
|
|
{
|
|
decimal currentLetterNumber = (columnNumber - 1) % 26;
|
|
char currentLetter = (char)(currentLetterNumber + 65);
|
|
columnString = currentLetter + columnString;
|
|
columnNumber = (columnNumber - (currentLetterNumber + 1)) / 26;
|
|
}
|
|
return columnString;
|
|
}
|
|
private string GetCellRangeNew(int i, int j)
|
|
{
|
|
if (i == 0)
|
|
return "A" + j.ToString();
|
|
else if (i == 1)
|
|
return "B" + j.ToString();
|
|
else if (i == 2)
|
|
return "C" + j.ToString();
|
|
else if (i == 3)
|
|
return "D" + j.ToString();
|
|
else if (i == 4)
|
|
return "E" + j.ToString();
|
|
else if (i == 5)
|
|
return "F" + j.ToString();
|
|
else if (i == 6)
|
|
return "G" + j.ToString();
|
|
else if (i == 7)
|
|
return "H" + j.ToString();
|
|
else if (i == 8)
|
|
return "I" + j.ToString();
|
|
else if (i == 9)
|
|
return "J" + j.ToString();
|
|
else if (i == 10)
|
|
return "K" + j.ToString();
|
|
else if (i == 11)
|
|
return "L" + j.ToString();
|
|
else if (i == 12)
|
|
return "M" + j.ToString();
|
|
else if (i == 13)
|
|
return "N" + j.ToString();
|
|
else if (i == 14)
|
|
return "O" + j.ToString();
|
|
else if (i == 15)
|
|
return "P" + j.ToString();
|
|
else if (i == 16)
|
|
return "Q" + j.ToString();
|
|
else if (i == 17)
|
|
return "R" + j.ToString();
|
|
else if (i == 18)
|
|
return "S" + j.ToString();
|
|
else if (i == 19)
|
|
return "T" + j.ToString();
|
|
else if (i == 20)
|
|
return "U" + j.ToString();
|
|
else if (i == 21)
|
|
return "V" + j.ToString();
|
|
else if (i == 22)
|
|
return "W" + j.ToString();
|
|
else if (i == 23)
|
|
return "X" + j.ToString();
|
|
else if (i == 24)
|
|
return "Y" + j.ToString();
|
|
else if (i == 25)
|
|
return "Z" + j.ToString();
|
|
else if (i == 26)
|
|
return "AA" + j.ToString();
|
|
else if (i == 27)
|
|
return "AB" + j.ToString();
|
|
else if (i == 28)
|
|
return "AC" + j.ToString();
|
|
else if (i == 29)
|
|
return "AD" + j.ToString();
|
|
else if (i == 30)
|
|
return "AE" + j.ToString();
|
|
else if (i == 31)
|
|
return "AF" + j.ToString();
|
|
else if (i == 32)
|
|
return "AG" + j.ToString();
|
|
else if (i == 33)
|
|
return "AH" + j.ToString();
|
|
else if (i == 34)
|
|
return "AI" + j.ToString();
|
|
else if (i == 35)
|
|
return "AJ" + j.ToString();
|
|
else if (i == 36)
|
|
return "AK" + j.ToString();
|
|
else if (i == 37)
|
|
return "AL" + j.ToString();
|
|
else
|
|
return "AM" + j.ToString();
|
|
}
|
|
public void PrepareHeaderOfAccountingSummary(ExcelWorksheet workSheet, DataSet ds)
|
|
{
|
|
using (ExcelRange Rng = workSheet.Cells["A1:U1"])
|
|
{
|
|
Rng.Value = "Accounting Summary";
|
|
Rng.Merge = true;
|
|
Rng.Style.Font.Size = 11;
|
|
Rng.Style.Font.Bold = true;
|
|
Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
|
|
}
|
|
|
|
using (ExcelRange Rng = workSheet.Cells["A2"])
|
|
{
|
|
Rng.Value = "Date";
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["B2:E2"])
|
|
{
|
|
Rng.Value = "Remittance Send Tamt";
|
|
Rng.Merge = true;
|
|
Rng.Style.Font.Size = 11;
|
|
Rng.Style.Font.Bold = true;
|
|
Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["F2:I2"])
|
|
{
|
|
Rng.Value = "Remittance Cancelled Tamt";
|
|
Rng.Merge = true;
|
|
Rng.Style.Font.Size = 11;
|
|
Rng.Style.Font.Bold = true;
|
|
Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["J2:M2"])
|
|
{
|
|
Rng.Value = "Service Charge";
|
|
Rng.Merge = true;
|
|
Rng.Style.Font.Size = 11;
|
|
Rng.Style.Font.Bold = true;
|
|
Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["N2:Q2"])
|
|
{
|
|
Rng.Value = "Commission";
|
|
Rng.Merge = true;
|
|
Rng.Style.Font.Size = 11;
|
|
Rng.Style.Font.Bold = true;
|
|
Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["R2:U2"])
|
|
{
|
|
Rng.Value = "Forex Income";
|
|
Rng.Merge = true;
|
|
Rng.Style.Font.Size = 11;
|
|
Rng.Style.Font.Bold = true;
|
|
Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
|
|
}
|
|
|
|
using (ExcelRange Rng = workSheet.Cells["B3"])
|
|
{
|
|
Rng.Value = "JME Nepal";
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["C3"])
|
|
{
|
|
Rng.Value = "DongA";
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["D3"])
|
|
{
|
|
Rng.Value = "Transfast";
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["E3"])
|
|
{
|
|
Rng.Value = "Total";
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["F3"])
|
|
{
|
|
Rng.Value = "JME Nepal";
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["G3"])
|
|
{
|
|
Rng.Value = "DongA";
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["H3"])
|
|
{
|
|
Rng.Value = "Transfast";
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["I3"])
|
|
{
|
|
Rng.Value = "Total";
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["J3"])
|
|
{
|
|
Rng.Value = "JME Nepal";
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["K3"])
|
|
{
|
|
Rng.Value = "DongA";
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["L3"])
|
|
{
|
|
Rng.Value = "Transfast";
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["M3"])
|
|
{
|
|
Rng.Value = "Total";
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["N3"])
|
|
{
|
|
Rng.Value = "JME Nepal";
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["O3"])
|
|
{
|
|
Rng.Value = "DongA";
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["P3"])
|
|
{
|
|
Rng.Value = "Transfast";
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["Q3"])
|
|
{
|
|
Rng.Value = "Total";
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["R3"])
|
|
{
|
|
Rng.Value = "JME Nepal";
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["S3"])
|
|
{
|
|
Rng.Value = "DongA";
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["T3"])
|
|
{
|
|
Rng.Value = "Transfast";
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
using (ExcelRange Rng = workSheet.Cells["U3"])
|
|
{
|
|
Rng.Value = "Total";
|
|
Rng.Style.Font.Bold = true;
|
|
}
|
|
}
|
|
}
|
|
}
|