public void writeErrorToFile(ExcelWorksheet expenseSheet, int rowData, Deca_Bank_TransactionsViewModel item, string error)
{
using (var dbConn = Helpers.OrmliteConnection.openConn())
{
var dataBank = dbConn.Select <DC_Bank_Definition>().OrderBy(a => a.BankName);
var dataBankActionStatus = dbConn.Select <Deca_Code_Master>("[CodeType] = {0}", "BankActionStatus").OrderBy(a => a.CodeID);
var dataBankInstallMent = dbConn.Select <Deca_Bank_Installment>();
int i = 1;
expenseSheet.Cells[rowData, i++].Value = dataBankActionStatus.FirstOrDefault(a => a.CodeID == item.BankActionStatus) == null ? "" : dataBankActionStatus.FirstOrDefault(a => a.CodeID == item.BankActionStatus).Description;
expenseSheet.Cells[rowData, i++].Value = item.OrderID;
expenseSheet.Cells[rowData, i++].Value = item.AuthTransRef;
expenseSheet.Cells[rowData, i++].Value = item.Amount;
expenseSheet.Cells[rowData, i++].Value = item.OrderDate;
expenseSheet.Cells[rowData, i++].Value = item.CustomerName;
expenseSheet.Cells[rowData, i++].Value = item.PhysicalID;
expenseSheet.Cells[rowData, i++].Value = item.MobilePhone;
expenseSheet.Cells[rowData, i++].Value = item.Installment;
expenseSheet.Cells[rowData, i++].Value = item.BankInstallment > 0 ? dataBankInstallMent.FirstOrDefault(a => a.ID == item.BankInstallment).Installment : 0;
expenseSheet.Cells[rowData, i++].Value = dataBank.FirstOrDefault(a => a.BankID == item.Bank) == null ? "" : dataBank.FirstOrDefault(a => a.BankID == item.Bank).BankName;
expenseSheet.Cells[rowData, i++].Value = item.TransactionInfo;
expenseSheet.Cells[rowData, i++].Value = item.CreatedAt;
expenseSheet.Cells[rowData, i++].Value = item.CreatedBy;
expenseSheet.Cells[rowData, i++].Value = item.UpdatedAt;
expenseSheet.Cells[rowData, i++].Value = item.UpdatedBy;
}
}
public ActionResult Read([DataSourceRequest] DataSourceRequest request)
{
if (asset.View)
{
using (IDbConnection dbConn = Helpers.OrmliteConnection.openConn())
{
var data = Deca_Bank_TransactionsViewModel.getRead("");
return(Json(data.ToDataSourceResult(request)));
}
}
else
{
return(Json(new List <Deca_Bank_TransactionsViewModel>().ToDataSourceResult(request)));
}
}
public ActionResult ExportData([DataSourceRequest] DataSourceRequest request)
{
if (asset.Export)
{
using (var dbConn = Helpers.OrmliteConnection.openConn())
{
//using (ExcelPackage excelPkg = new ExcelPackage())
FileInfo fileInfo = new FileInfo(Server.MapPath(@"~\ExportExcelFile\Deca_Bank_Transactions.xlsx"));
var excelPkg = new ExcelPackage(fileInfo);
string fileName = "Deca_Bank_Transactions_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx";
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
string query = "";
var data = Deca_Bank_TransactionsViewModel.getRead("").ToDataSourceResult(request);
ExcelWorksheet expenseSheet = excelPkg.Workbook.Worksheets["Transactions"];
var dataBank = dbConn.Select <DC_Bank_Definition>().OrderBy(a => a.BankName);
var dataBankActionStatus = dbConn.Select <Deca_Code_Master>("[CodeType] = {0} AND CodeID <> 'BankActionStatus01'", "BankActionStatus").OrderBy(a => a.CodeID);
var dataBankInstallMent = dbConn.Select <Deca_Bank_Installment>();
var dataStatus = dbConn.Select <Deca_Order_Status>();
int rowData = 2;
foreach (Deca_Bank_TransactionsViewModel item in data.Data)
{
int i = 1;
rowData++;
if (item.BankActionStatus == "BankActionStatus01")
{
dbConn.Update <Deca_Bank_Transactions>("BankActionStatus = 'BankActionStatus02'", "OrderID = '" + item.OrderID + "'");
item.BankActionStatus = "BankActionStatus02";
}
expenseSheet.Cells[rowData, i++].Value = dataBankActionStatus.FirstOrDefault(a => a.CodeID == item.BankActionStatus) == null ? "" : dataBankActionStatus.FirstOrDefault(a => a.CodeID == item.BankActionStatus).Description;
expenseSheet.Cells[rowData, i++].Value = item.OrderID;
expenseSheet.Cells[rowData, i++].Value = item.RefID;
expenseSheet.Cells[rowData, i++].Value = item.AuthTransRef;
expenseSheet.Cells[rowData, i++].Value = item.Amount;
expenseSheet.Cells[rowData, i++].Value = item.OrderDate;
expenseSheet.Cells[rowData, i++].Value = item.CustomerName;
expenseSheet.Cells[rowData, i++].Value = item.PhysicalID;
expenseSheet.Cells[rowData, i++].Value = item.MobilePhone;
expenseSheet.Cells[rowData, i++].Value = item.Installment;
expenseSheet.Cells[rowData, i++].Value = item.BankInstallment > 0 ? dataBankInstallMent.FirstOrDefault(a => a.ID == item.BankInstallment).Installment : 0;
expenseSheet.Cells[rowData, i++].Value = dataBank.FirstOrDefault(a => a.BankID == item.Bank) == null ? "" : dataBank.FirstOrDefault(a => a.BankID == item.Bank).BankName;
expenseSheet.Cells[rowData, i++].Value = item.TransactionInfo;
expenseSheet.Cells[rowData, i++].Value = dataStatus.FirstOrDefault(a => a.RefID == item.Status) == null ? "" : dataStatus.FirstOrDefault(a => a.RefID == item.Status).Name;
expenseSheet.Cells[rowData, i++].Value = item.CreatedAt;
expenseSheet.Cells[rowData, i++].Value = item.CreatedBy;
expenseSheet.Cells[rowData, i++].Value = item.UpdatedAt;
expenseSheet.Cells[rowData, i++].Value = item.UpdatedBy;
}
rowData = 2;
ExcelWorksheet dataSheet2 = excelPkg.Workbook.Worksheets["List1"];
foreach (Deca_Code_Master master in dataBankActionStatus)
{
dataSheet2.Cells[rowData++, 1].Value = master.Description;
}
MemoryStream output = new MemoryStream();
excelPkg.SaveAs(output);
output.Position = 0;
return(File(output.ToArray(), contentType, fileName));
}
}
else
{
return(Json(new { success = false }));
}
}
