Notes

using OfficeOpenXml;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Threading;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml.Linq;


public partial class Pension_Fund_InsertExcel : System.Web.UI.Page
{
cls_User loggedUser;
cls_PublicFunctions pFunc;
string trxName = "LOAN";
string tmpTrxType = "1";

protected override void InitializeCulture()
{
string lang = string.Empty;

string LanguageID = Request.Cookies["iBankLanguage"] == null ? "MN" : Request.Cookies["iBankLanguage"].Value;

if (LanguageID == "MN")
lang = "mn-MN";
else
lang = "en-US";

if (lang != string.Empty && lang != null)
{
Thread.CurrentThread.CurrentUICulture = new CultureInfo(lang);
Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US");
}

base.InitializeCulture();
}
protected void Page_PreInit(object sender, EventArgs e)
{
if (Session["loggedUser"] != null && Session["publicFunctions"] != null)
{
loggedUser = (cls_User)Session["loggedUser"];
pFunc = (cls_PublicFunctions)Session["publicFunctions"];
pFunc.checkLinkRequest(loggedUser, Response, "8");
pFunc.SetMasterPage(loggedUser, this, Response);

}

}
private bool _refreshState;
private bool _isRefresh;
private string ViewState;

protected override object SaveViewState()
{
Session["__ISREFRESH"] = _refreshState;
object[] allStates = new object[3];
allStates[0] = base.SaveViewState();
allStates[1] = !_refreshState;
allStates[2] = _UserPin;
return allStates;
}

protected override void LoadViewState(object savedState)
{
object[] allStates = (object[])savedState;
base.LoadViewState(allStates[0]);
_refreshState = (bool)allStates[1];
if(_isRefresh = _refreshState == (bool)Session["__ISREFRESH"])
{
Response.Redirect("~/Pension_Fund/InsertExcel.aspx");
}

_UserPin = (String)allStates[2];
}

public bool IsRefresh
{
get
{
return _isRefresh;
}
}

public string _UserPin
{
get
{
return ViewState;
}
set
{
ViewState = value;
}
}


protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack == false)
{
lblError.Visible = false;
TextBox302.Text = DateTime.Now.ToString("yyyy-MM-dd");

if (pFunc.IsOrgan(loggedUser))
{
pFunc.CheckOrgTranMakePermission(loggedUser, Response);
}
else//irgen
{
pFunc.CheckTranPermission(loggedUser, Response);
}


loadData();
_UserPin = "gun1";


}
}
private void loadData()
{
MultiView1.ActiveViewIndex = 0;
}


protected void InsertExcel_Click(object sender, EventArgs e)
{
Response.Redirect("~/Pension_Fund/InsertExcel.aspx");
}
protected void OT_Click(object sender, EventArgs e)
{
Response.Redirect("~/Pension_Fund/OT.aspx");
}
protected void Upload_Click(object sender, EventArgs e)
{

}
protected void Send_Click(object sender, EventArgs e)
{

if (FileUpload1.HasFile && Path.GetExtension(FileUpload1.FileName) == ".xlsx")
{
string fileName = FileUpload1.FileName;
try
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package = new ExcelPackage(FileUpload1.PostedFile.InputStream))
{
var worksheet = package.Workbook.Worksheets[0]; // Assuming first worksheet

// Create a DataTable to hold the Excel data
DataTable dt = new DataTable();

// Assuming you have six columns, add them to the DataTable
for (int col = 1; col <= 3; col++)
{
dt.Columns.Add("Column" + col);
}
int limit = int.Parse( ConfigurationManager.AppSettings["pensionStatementLimit"]);
if (worksheet.Dimension.End.Row > limit + 11) // header hesgiin 11 mur nemeh
{
MultiView1.ActiveViewIndex = 0;
lblError1.Visible = true;
lblError1.Text = "Нэг удаад 2000 мөр гүйлгээ хийх боломжтой";
}
else {
for (int rowNum = 10; rowNum <= worksheet.Dimension.End.Row; rowNum++)
{
var wsRow = worksheet.Cells[rowNum, 1, rowNum, 3];
if (wsRow.All(cell => string.IsNullOrWhiteSpace(cell.Text)))
{
continue;
}
DataRow row = dt.Rows.Add();
foreach (var cell in wsRow)
{
row[cell.Start.Column - 1] = cell.Text;
}
}



bool allCellsNotNull = dt.AsEnumerable().All(row => !row.ItemArray.Any(cell => cell is DBNull || string.IsNullOrEmpty(cell.ToString())));


if (allCellsNotNull)
{
// Now you have the data in a DataTable (dt)
Hashtable param = new Hashtable();
int rowCount = dt.Rows.Count;
XElement elm = new XElement("BulkStatement");
XElement elmTr = null;
string narration = null;

switch (DropDownList301.SelectedIndex)
{
case 1:
narration = DateTime.Now.Month.ToString() + " " + "сарын эхний гүйлгээ";
break;
case 2:
narration = DateTime.Now.Month.ToString() + " " + "сарын сүүлийн гүйлгээ";
break;
}

// Loop through the rows and process data as needed
foreach (DataRow row in dt.Rows)
{

elmTr = new XElement("Transaction");

elmTr.Add(new XElement("SapID", row["Column1"].ToString()));
elmTr.Add(new XElement("Currency", row["Column2"].ToString()));
elmTr.Add(new XElement("Amount", row["Column3"].ToString()));
elmTr.Add(new XElement("Narration", narration));
elmTr.Add(new XElement("Create_ts", TextBox302.Text));
elmTr.Add(new XElement("CorpID", loggedUser.pCorporateID));
elmTr.Add(new XElement("FileName", fileName));
elmTr.Add(new XElement("Modified_by", loggedUser.pUserID));
elm.Add(elmTr);
}
param["BulkStatement"] = elm;
cls_XmlBuilder gateway = (cls_XmlBuilder)Session["gateway"];
XDocument res = gateway.CallFunction(loggedUser.pUserID, loggedUser.pLanguageID, "AddAccountStatement", param, Request, Response);
try
{
if (res.Element("IBankGateWay").Element("GWHeader").Element("Status").Value == "OK")
{

XElement resval = res.Element("IBankGateWay").Element("GWBody").Element("Response");
Session["soList"] = resval.ToString();
int ctr = res.Element("IBankGateWay").Element("GWBody").Element("Response").Elements("Statement").Count();
if (ctr > 0)
{
DataTable res_dt = pFunc.XMLToDataTable(resval.ToString());
int dtRowsCount = res_dt.Rows.Count;
res_dt.Columns.Add("rowNumber", typeof(int));
int rowNumber = 1;
foreach (DataRow dr in res_dt.Rows)
{
if (dr["error_status"].Equals("505") == true)
{
dr["error_status"] = "Вальют алдаатай";

}
else if (dr["error_status"].Equals("500") == true)
{
dr["error_status"] = "Үнийн дүн алдаатай";

}
else if (dr["error_status"].Equals("504") == true)
{
dr["error_status"] = "Ажилтны мэдээлэл давхардаж орсон";

}
else
{
dr["error_status"] = "Ажилтны мэдээлэл алдаатай";
}

dr["rowNumber"] = rowNumber;
rowNumber++;
}

Label1.Text = rowCount + " upload хийснээс дараах " + dtRowsCount + " мөр алдаатай байна. ";
GridView1.DataSource = res_dt;
GridView1.DataBind();
MultiView1.ActiveViewIndex = 1;

}
else
{
MultiView1.ActiveViewIndex = 2;
lblError.Visible = true;
lblError.Text = "Гүйлгээ амжилттай";
}

}
}
catch (Exception ex)
{
MultiView1.ActiveViewIndex = 0;
lblError.Visible = true;
lblError.Text = "An error occurred: " + ex.Message;
}
}
else
{
// Some cells are null, display an error message
lblError1.Visible = true;
lblError1.Text = "Бүх мэдээлэл заавал бөглөгдсөн байх шаардлагатай. Excel file -аа шалгаад ахин оролдож үзнэ үү!";
MultiView1.ActiveViewIndex = 0;
}
}


}
}
catch (Exception ex)
{
MultiView1.ActiveViewIndex = 2;
lblError.Visible = true;
lblError.Text = "An error occurred: " + ex.Message;
}
}
else
{
lblError.Visible = true;
lblError.Text = "Зөвхөн excel файл сонгоно уу!";
MultiView1.ActiveViewIndex = 2;
}
}
protected void Button1_Click(object sender, EventArgs e)
{

ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

// Create a DataTable to hold your data (you can replace this with your actual data)
DataTable dataTable = new DataTable();

foreach (DataControlFieldHeaderCell headerCell in GridView1.HeaderRow.Cells)
{
dataTable.Columns.Add(headerCell.Text);
}

foreach (GridViewRow row in GridView1.Rows)
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < row.Cells.Count; i++)
{
dataRow[i] = row.Cells[i].Text;
}
dataTable.Rows.Add(dataRow);
}

// Export DataTable to Excel
if (dataTable != null && dataTable.Rows.Count > 0)
{
// Generate Excel content and save it to a temporary file
var package = new ExcelPackage();
var worksheet = package.Workbook.Worksheets.Add("Sheet1");
worksheet.Cells["A1"].LoadFromDataTable(dataTable, true);
worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();

// Specify the custom file name
string customFileName = "Statement_errorlog" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
// Combine the user's download folder path and the custom file name
string downloadFolderPath = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) + "\Downloads\";
string customFilePath = Path.Combine(downloadFolderPath, customFileName);

// Save the Excel package to the custom file path
File.WriteAllBytes(customFilePath, package.GetAsByteArray());

// Execute JavaScript to open the print dialog for the custom file
string script = "var win = window.open('" + customFilePath.Replace("\", "\\") + "'); win.print(); win.close();";
ClientScript.RegisterStartupScript(this.GetType(), "PrintScript", script, true);
Label16.Visible = true;
Label16.Text = customFileName + " нэртэй excel file татагдлаа Downloads хавтасаа шалгана уу!";
}
else
{

}
}
protected void LinkButton1_Click1(object sender, EventArgs e)
{
string fileName = "";
fileName = "ER_template_Admin.xlsx";
string filePath = Server.MapPath("~/_Public/files/tempfile/" + fileName);

// Open the existing Excel file
using (var package = new ExcelPackage(new FileInfo(filePath)))
{
HttpResponse response = HttpContext.Current.Response;

response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
response.AddHeader("Content-Disposition", "attachment; filename=ER_template_Admin.xlsx");
response.WriteFile(filePath);
response.Flush();

}

}
}