public async Task <List <MerchPostedTxnSearch> > WebMerchTxnSearch(TxnSearchModel _model)
{
var objDataEngine = new FleetDataEngine(AccessMode.CustomerService, DBType.Maint);
try
{
objDataEngine.InitiateConnection();
SqlParameter[] Parameters = new SqlParameter[7];
Parameters[0] = new SqlParameter("@AcqNo", this.GetAcqNo);
Parameters[1] = String.IsNullOrEmpty(_model.BusnLocation) ? new SqlParameter("@BusnLocation", DBNull.Value) : new SqlParameter("@BusnLocation", _model.BusnLocation);
Parameters[2] = String.IsNullOrEmpty(_model.SelectedMerchTxnCd) ? new SqlParameter("@TxnCd", DBNull.Value) : new SqlParameter("@TxnCd", _model.SelectedMerchTxnCd);
Parameters[3] = String.IsNullOrEmpty(_model.MerchAcctNo) ? new SqlParameter("@MerchAcctNo", DBNull.Value) : new SqlParameter("@MerchAcctNo", _model.MerchAcctNo);
Parameters[4] = String.IsNullOrEmpty(_model.MerchFromDate) ? new SqlParameter("@FrmTxnDate", DBNull.Value) : new SqlParameter("@FrmTxnDate", DateConverterDB(_model.MerchFromDate));
Parameters[5] = String.IsNullOrEmpty(_model.MerchToDate) ? new SqlParameter("@ToTxnDate", DBNull.Value) : new SqlParameter("@ToTxnDate", DateConverterDB(_model.MerchToDate));
Parameters[6] = String.IsNullOrEmpty(_model.SelectedTxnCategory) ? new SqlParameter("@TxnCat", DBNull.Value) : new SqlParameter("@TxnCat", _model.SelectedTxnCategory);
var execResult = await objDataEngine.ExecuteCommandAsync("WebMerchTxnSearch", CommandType.StoredProcedure, Parameters);
var _MerchPostedTxnSearch = new List <MerchPostedTxnSearch>();
while (execResult.Read())
{
_MerchPostedTxnSearch.Add(new MerchPostedTxnSearch
{
SelectedDealer = Convert.ToString(execResult["Dealer"]),
TermBatch = Convert.ToString(execResult["TermBatch"]),
TxnDate = Convert.ToString(execResult["TxnDate"]),
cardNo = Convert.ToString(execResult["CardNo"]),
TxnDesp = Convert.ToString(execResult["TxnDescp"]),
TxnAmt = ConverterDecimal(execResult["BillingAmt"]),
TermId = Convert.ToString(execResult["TermId"]),
AuthNo = Convert.ToString(execResult["AuthNo"]),
AuthCardNo = Convert.ToString(execResult["AuthCardNo"]),
PrcsDate = Convert.ToString(execResult["PrcsDate"]),
TxnId = Convert.ToString(execResult["TxnId"]),
ProductQty = Convert.ToString(execResult["ProductQty"]),
ProductAmt = ConverterDecimal(execResult["ProductAmt"]),
VATAmt = ConverterDecimal(execResult["VAT Amt"]),
BaseAmt = ConverterDecimal(execResult["Base Amt"]),
VATCd = Convert.ToString(execResult["VATCd"]),
VATRate = ConverterDecimal(execResult["VATRate"]),
ProductDescp = Convert.ToString(execResult["ProductDescp"])
});
}
;
return(_MerchPostedTxnSearch);
}
finally
{
objDataEngine.CloseConnection();
}
}
public async Task <ActionResult> FillData()
{
var Selects = new TxnSearchModel
{
TxnCategory = await BaseService.GetTransactionCategory(),
TxnCd = await BaseService.WebGetTxnCode("I", null, null),
MerchTxnCd = await BaseService.WebGetTxnCode("A", null, null),
StatementDate = (await BaseService.GetCycleStmt(null)).RefLibLst
};
var StatementDateLst = Selects.StatementDate.ToList();
StatementDateLst.Insert(0, new SelectListItem {
Value = "", Text = ""
});
Selects.StatementDate = StatementDateLst as IEnumerable <SelectListItem>;
return(Json(new { Selects = Selects, Model = new TxnSearchModel() }, JsonRequestBehavior.AllowGet));
}
public async Task <ActionResult> WebMerchTxnSearch(jQueryDataTableParamModel Params, TxnSearchModel Model, bool isDownload)
{
TxnSearchMaint _Maint = new TxnSearchMaint();
var _filtered = new List <MerchPostedTxnSearch>();
var list = (await TransactionSearchService.GetMerchTransactionSearch(Model.BusnLocation, Model.MerchAcctNo, Model.SelectedMerchTxnCd, Model.MerchFromDate, Model.MerchToDate, Model.SelectedTxnCategory)).merchPostedTxnSearches;
if (!string.IsNullOrEmpty(Params.sSearch))
{
Params.sSearch = Params.sSearch.ToLower();
}
if (!isDownload)
{
_filtered = list.Skip(Params.iDisplayStart).Take(Params.iDisplayLength).ToList();
return(Json(new
{
sEcho = Params.sEcho,
iTotalRecords = list.Count(),
iTotalDisplayRecords = list.Count(),
aaData = _filtered.Select(x => new object[] { null, x.SelectedDealer, x.TermBatch, x.TxnDate, x.cardNo, x.TxnDesp, x.TxnAmt, x.TermId,
x.AuthNo, x.AuthCardNo, x.PrcsDate, x.TxnId, x.ProductDescp, x.ProductQty, x.ProductAmt, x.VATAmt, x.BaseAmt, x.VATCd, x.VATRate, })
}, JsonRequestBehavior.AllowGet));
}
else
{
var title = "Transaction search | Merchant";
var toExport = new List <string[]>();
var Header = list.First().ExcelHeader;
foreach (var item in list)
{
toExport.Add(item.ExcelBody());
}
var ExcelPkg = Common.CommonHelpers.CreateExcel(Header, toExport, title);
return(File(ExcelPkg.GetAsByteArray(), "application/vnd.ms-excel", title + ".xlsx"));
}
}
public async Task <ActionResult> WebAcctTxnSearch(jQueryDataTableParamModel Params, TxnSearchModel Model, bool isDownload = false)
{
var _filtered = new List <AcctPostedTxnSearch>();
var list = (await TransactionSearchService.GetAccountTransactionSearch(Convert.ToInt64(Model.AcctNo), Convert.ToInt64(Model.CardNo), Model.SelectedTxnCategory, Convert.ToInt32(Model.SelectedTxnCd), Model.FromDate, Model.ToDate, Model.SelectedStatementDate)).transactionSearches;
if (!isDownload)
{
if (!string.IsNullOrEmpty(Params.sSearch))
{
Params.sSearch = Params.sSearch.ToLower();
}
if (!string.IsNullOrEmpty(Params.sSearch))
{
_filtered = list.Where(p => p.InvoicDt.ToLower().Contains(Params.sSearch) || p.TxnDate.ToLower().Contains(Params.sSearch)).ToList();
_filtered = _filtered.Skip(Params.iDisplayStart).Take(Params.iDisplayLength).ToList();
}
else
{
_filtered = list.Skip(Params.iDisplayStart).Take(Params.iDisplayLength).ToList();
}
return(Json(new
{
sEcho = Params.sEcho,
iTotalRecords = list.Count(),
iTotalDisplayRecords = list.Count(),
aaData = _filtered.Select(x => new object[] { null, x.InvoicDt, x.TxnDate, x.PrcsDate, x.AcctNo, x.SelectedCardNo, x.AuthCardNo, x.TxnDesp, x.VehRegNo, x.Stan, x.ApproveCd, x.RRn, x.VATNo, x.Dealer, x.TxnId, x.TxnAmt, x.ProductDescp, x.Quantity, x.ProductAmt, x.VATAmt, x.VATCd, x.VATRate, })
}, JsonRequestBehavior.AllowGet));
}
else
{
var title = "Transaction search | Account";
var toExport = new List <string[]>();
var Header = list.First().ExcelHeader;
foreach (var item in list)
{
toExport.Add(item.ExcelBody());
}
var ExcelPkg = Common.CommonHelpers.CreateExcel(Header, toExport, title);
return(File(ExcelPkg.GetAsByteArray(), "application/vnd.ms-excel", title + ".xlsx"));
}
}
public async Task <List <AcctPostedTxnSearch> > WebAcctTxnSearch(TxnSearchModel _acctPostedTxnSearch)
{
var objDataEngine = new FleetDataEngine(AccessMode.CustomerService, DBType.Maint);
try
{
objDataEngine.InitiateConnection();
SqlParameter[] Parameters = new SqlParameter[7];
Parameters[0] = new SqlParameter("@IssNo", GetIssNo);
Parameters[1] = String.IsNullOrEmpty(_acctPostedTxnSearch.AcctNo) ? new SqlParameter("@AcctNo", DBNull.Value) : new SqlParameter("@AcctNo", _acctPostedTxnSearch.AcctNo);
Parameters[2] = String.IsNullOrEmpty(_acctPostedTxnSearch.CardNo) ? new SqlParameter("@CardNo", DBNull.Value) : new SqlParameter("@CardNo", _acctPostedTxnSearch.CardNo);
Parameters[3] = String.IsNullOrEmpty(_acctPostedTxnSearch.SelectedTxnCategory) ? new SqlParameter("@TxnCategory", DBNull.Value) : new SqlParameter("@TxnCategory", _acctPostedTxnSearch.SelectedTxnCategory);
Parameters[4] = String.IsNullOrEmpty(_acctPostedTxnSearch.SelectedTxnCd) ? new SqlParameter("@TxnCd", DBNull.Value) : new SqlParameter("@TxnCd", _acctPostedTxnSearch.SelectedTxnCd);
Parameters[5] = new SqlParameter("@FromDate", ConvertDatetimeDB(_acctPostedTxnSearch.FromDate));
Parameters[6] = new SqlParameter("@ToDate", ConvertDatetimeDB(_acctPostedTxnSearch.ToDate));
var execResult = await objDataEngine.ExecuteCommandAsync("WebAcctTxnSearch", CommandType.StoredProcedure, Parameters);
var _AcctPostedTxnSearch = new List <AcctPostedTxnSearch>();
while (execResult.Read())
{
_AcctPostedTxnSearch.Add(new AcctPostedTxnSearch
{
InvoicDt = DateConverter(execResult["StatementDate"]),
TxnDate = Convert.ToString(execResult["TxnDate"]),
AcctNo = Convert.ToString(execResult["AcctNo"]),
SelectedCardNo = Convert.ToString(execResult["CardNo"]),
TaxInvoiceNo = Convert.ToString(execResult["TaxInvoiceNo"]),
TxnDesp = Convert.ToString(execResult["Txn Descp"]),
TxnAmt = ConverterDecimal(execResult["Txn Amt"]),
Dealer = Convert.ToString(execResult["Dealer"]),
VATNo = Convert.ToString(execResult["VAT No"]),
AuthCardNo = Convert.ToString(execResult["AuthCardNo"]),
PrcsDate = DateConverter(execResult["PrcsDate"]),
TxnId = Convert.ToString(execResult["Txn Id"]),
RecieptId = Convert.ToString(execResult["Receipt No"]),
Batch = Convert.ToString(execResult["BatchNo"]),
VehRegNo = Convert.ToString(execResult["VehRegsNo"]),
DriverName = Convert.ToString(execResult["Driver Name"]),
SiteId = Convert.ToString(execResult["SiteId"]),
Quantity = Convert.ToString(execResult["Qty"]),
ProductAmt = ConverterDecimal(execResult["ProductAmt"]),
VATAmt = ConverterDecimal(execResult["VAT Amt"]),
BaseAmt = ConverterDecimal(execResult["Base Amt"]),
VATCd = Convert.ToString(execResult["VATCd"]),
VATRate = ConverterDecimal(execResult["VATRate"]),
ProductDescp = Convert.ToString(execResult["ProductDescp"]),
RRn = Convert.ToString(execResult["Rrn"]),
Stan = Convert.ToString(execResult["Stan"]),
//TermId = Convert.ToString(execResult["TermId"]),
//TotalTxnAmt = ConverterDecimal(execResult["TotalTxnAmt"]),
ApproveCd = Convert.ToString(execResult["AppvCd"]),
//RRn = Convert.ToString(execResult["RRn"]),
//Stan = Convert.ToString(execResult["Stan"]),
});
}
;
return(_AcctPostedTxnSearch);
}
finally
{
objDataEngine.CloseConnection();
}
}
