public int GetAccountTypeID(string AccountCode)
{
try
{
int AccountTypeID = 0;
using (var db = new SoneriCISEntities())
{
AccountTypeID = db.tbl_Account_Types.Where(a => a.Code == AccountCode).FirstOrDefault()?.ID ?? 0;
}
return(AccountTypeID);
}
catch (Exception ex)
{
throw ex;
}
}
public tbl_Account_Types GetAccountTypeDetailsById(int AccountTypeID)
{
try
{
tbl_Account_Types Val = new DAL.tbl_Account_Types();
using (var db = new SoneriCISEntities())
{
Val = db.tbl_Account_Types.Include("tbl_Customer_Accounts").Include("tbl_Card_Charges").Where(a => a.ID == AccountTypeID).FirstOrDefault();
}
return(Val);
}
catch (Exception ex)
{
throw ex;
}
}
public tbl_Account_Types GetAccountTypeByCode(string AccountCode)
{
try
{
tbl_Account_Types Val = new DAL.tbl_Account_Types();
using (var db = new SoneriCISEntities())
{
Val = db.tbl_Account_Types.Where(a => a.Code == AccountCode).FirstOrDefault();
}
return(Val);
}
catch (Exception ex)
{
throw ex;
}
}
public tbl_Sector GetSectorbyID(int ID)
{
try
{
tbl_Sector row = new tbl_Sector();
using (var db = new SoneriCISEntities())
{
row = db.tbl_Sector.FirstOrDefault(e => e.ID == ID);
}
return(row);
}
catch (Exception ex)
{
throw ex;
}
}
public List <tbl_Account_Types> GetAllAccountTypes()
{
try
{
List <tbl_Account_Types> lst = new List <DAL.tbl_Account_Types>();
using (var db = new SoneriCISEntities())
{
lst = db.tbl_Account_Types.Where(a => a.IsActive == true).ToList();
}
return(lst);
}
catch (Exception ex)
{
throw ex;
}
}
public List <tbl_Sector> GetSector()
{
try
{
List <tbl_Sector> lst = new List <tbl_Sector>();
using (var db = new SoneriCISEntities())
{
lst = db.tbl_Sector.ToList();
}
return(lst);
}
catch (Exception ex)
{
throw ex;
}
}
public tbl_Sector GetSectorbyCode(string Code)
{
try
{
tbl_Sector row = new tbl_Sector();
using (var db = new SoneriCISEntities())
{
row = db.tbl_Sector.FirstOrDefault(e => e.Code == Code);
}
return(row);
}
catch (Exception ex)
{
throw ex;
}
}
public bool AddSector(tbl_Sector row)
{
try
{
using (var db = new SoneriCISEntities())
{
db.tbl_Sector.Add(row);
db.SaveChanges();
}
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public static bool CheckExpiry(string UserName)
{
using (var db = new SoneriCISEntities())
{
var query = db.tbl_Users.FirstOrDefault(e => e.UserLogin.ToLower() == UserName.ToLower() && e.IsActive == true);
if (query.ExpiryDate != null)
{
if (query.ExpiryDate < DateTime.Now)
{
return(false);
}
}
return(true);
}
}
public tbl_Card_Types GetCardType(int CardTypeID)
{
try
{
tbl_Card_Types cardType = new DAL.tbl_Card_Types();
using (var db = new SoneriCISEntities())
{
cardType = db.tbl_Card_Types.Include("tbl_Card_Upgrade_Types").Include("tbl_Card_Upgrade_Types1").Include("tbl_Card_Charges").Where(a => a.ID == CardTypeID).FirstOrDefault();
}
return(cardType);
}
catch (Exception ex)
{
throw ex;
}
}
public List <tbl_Card_Types> GetCardTypes(string AuthorizationStatus)
{
try
{
List <tbl_Card_Types> lst = new List <DAL.tbl_Card_Types>();
using (var db = new SoneriCISEntities())
{
lst = db.tbl_Card_Types.Where(a => a.IsActive == true && a.AuthorizationStatus == AuthorizationStatus).ToList();
}
return(lst);
}
catch (Exception ex)
{
throw ex;
}
}
public List <tbl_Card_Types> GetAllCardTypesActiveInactive()
{
try
{
List <tbl_Card_Types> lst = new List <DAL.tbl_Card_Types>();
using (var db = new SoneriCISEntities())
{
lst = db.tbl_Card_Types.ToList();
}
return(lst);
}
catch (Exception ex)
{
throw ex;
}
}
public bool AddIncomeAccounts(tbl_IncomeAccounts request)
{
try
{
using (var db = new SoneriCISEntities())
{
db.tbl_IncomeAccounts.Add(request);
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
return(false);
}
}
public static List <AuthorizationVM> FilterRequestDetails(FilterAuthorizationVM request)
{
using (var db = new SoneriCISEntities())
{
var _query = new List <AuthorizationVM>();
var query = db.tbl_Requests.Where(e => e.IsActive == true && e.AuthorizationStatus == "R" && e.RequestType == request.RequestType).AsEnumerable();
if (request.From != null)
{
query = query.Where(a => a.RequestDate >= request.From);
}
if (request.To != null)
{
query = query.Where(a => a.RequestDate <= request.To);
}
if (request.CIFNumber != null)
{
query = query.Where(a => a.CIFNo == request.CIFNumber);
}
if (request.AccountNumber != null)
{
query = query.Where(a => a.AccountNo == request.AccountNumber);
}
if (request.RequestNumber != null)
{
query = query.Where(a => a.ID == request.RequestNumber);
}
_query = query.Select(o => new AuthorizationVM
{
ID = o.ID,
AccountNo = o.AccountNo,
CardNo = o.CardNo,
CardTitle = o.CardTitle,
CardTypeID = o.CardTypeID,
CIFNo = o.CIFNo,
RequestDate = o.RequestDate,
RequestType = o.RequestType,
WaiveCharges = o.WaiveCharges,
AuthorizationComments = o.AuthorizationComments
}).ToList();
_query.ForEach(e => e.CardType = db.tbl_Card_Types.FirstOrDefault(o => o.ID == e.CardTypeID && o.IsActive == true));
return(_query);
}
}
public bool UpdateCustomerRequestAccount(int ID, string Address, string Mobile, string Landline)
{
try
{
using (var db = new SoneriCISEntities())
{
tbl_Request_Customer_Accounts val = new DAL.tbl_Request_Customer_Accounts();
val = db.tbl_Request_Customer_Accounts.Where(a => a.ID == ID).FirstOrDefault();
val.ID = val.ID;
val.AccountNo = val.AccountNo;
val.CIF = val.CIF;
val.CardNo = val.CardNo;
val.AccountStatusActive = val.AccountStatusActive;
val.Salutation = val.Salutation;
val.AccountTitle = val.AccountTitle;
val.Address = val.Address;
val.Mobile = val.Mobile;
val.DateofBirth = val.DateofBirth;
val.MotherMaidenName = val.MotherMaidenName;
val.Identification = val.Identification;
val.CNIC = val.CNIC;
val.AddressType = val.AddressType;
val.AccountTypeID = val.AccountTypeID;
val.WaiveCharges = val.WaiveCharges;
val.PassportNo = val.PassportNo;
val.LandlineNo = val.LandlineNo;
val.Email = val.Email;
val.Nationality = val.Nationality;
val.AccountCategoryCode = val.AccountCategoryCode;
val.PhoneOffice = val.PhoneOffice;
val.Company = val.Company;
val.IdentificationType = val.IdentificationType;
val.Mobile2 = val.Mobile2;
val.Address2 = val.Address2;
val.Address3 = val.Address3;
val.MainMobile = Mobile;
val.MainLandline = Landline;
val.MainAddress = Address;
db.SaveChanges();
}
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public tbl_Requests GetRequestToBeReviewed(int RequestID, string RequestType)
{
try
{
tbl_Requests request = new tbl_Requests();
using (var db = new SoneriCISEntities())
{
request = db.tbl_Requests.Where(a => a.AuthorizationStatus == "R" && a.IsActive == true
&& a.RequestType == RequestType && a.ID == RequestID).FirstOrDefault();
}
return request;
}
catch (Exception ex)
{
throw ex;
}
}
public tbl_Requests GetRequestForImport(string CIF,string AccountNo)
{
try
{
tbl_Requests request = new DAL.tbl_Requests();
using (var db = new SoneriCISEntities())
{
request = db.tbl_Requests.Where(a => a.AuthorizationStatus == "A" && a.IsActive == true && a.IsExported == true
&& (a.RequestType == "R" || a.RequestType == "N") && a.CIFNo == CIF && a.AccountNo == AccountNo).OrderByDescending(e => e.ID).FirstOrDefault();
}
return request;
}
catch (Exception ex)
{
throw ex;
}
}
public long AddRequest2(tbl_Requests row)
{
try
{
using (var db = new SoneriCISEntities())
{
db.tbl_Requests.Add(row);
db.SaveChanges();
return row.ID;
}
}
catch (Exception ex)
{
return 0;
}
}
public tbl_Requests GetRequest(string CardNo, string AccountNo, string CIF)
{
using (var db = new SoneriCISEntities())
{
var query = db.tbl_Requests.Where(e => e.CardNo == CardNo && e.AccountNo == AccountNo && e.CIFNo == CIF && e.AuthorizationStatus == "A" && e.RequestType == "R" && e.IsActive == true).FirstOrDefault();
if (query == null)
{
//return db.tbl_Requests.FirstOrDefault(e => e.AccountNo == AccountNo && e.CIFNo == CIF && e.IsActive == true && e.RequestType == "N" && e.AuthorizationStatus == "A" && e.IsExported == true);
return db.tbl_Requests.Where(e => e.AccountNo == AccountNo && e.CIFNo == CIF && e.IsActive == true && e.AuthorizationStatus == "A" && (e.RequestType == "R" || e.RequestType == "N") && e.IsExported == true).OrderByDescending(e => e.ID).FirstOrDefault();
}
else
{
return db.tbl_Requests.Where(e => e.AccountNo == AccountNo && e.CIFNo == CIF && e.IsActive == true && e.AuthorizationStatus == "A" && (e.RequestType == "R" || e.RequestType == "N") && e.IsExported == true && e.ID < query.ID).OrderByDescending(e=>e.ID).FirstOrDefault();
}
}
}
public bool AddRequest(tbl_Requests row)
{
row.RequestDate = DateTime.Now;
try
{
using (var db = new SoneriCISEntities())
{
db.tbl_Requests.Add(row);
db.SaveChanges();
}
return true;
}
catch (Exception ex)
{
return false;
}
}
public bool UpdateUserLog(long requestId, string json)
{
using (var db = new SoneriCISEntities())
{
var query = db.tbl_User_Log.Where(e => e.EntityID == requestId).FirstOrDefault();
if (query != null)
{
query.Descp = json;
db.SaveChanges();
return(true);
}
else
{
return(false);
}
}
}
public tbl_Customer_Cards GetCustomerCard(string CardNo)
{
try
{
tbl_Customer_Cards card = new tbl_Customer_Cards();
using (var db = new SoneriCISEntities())
{
card = db.tbl_Customer_Cards.Where(a => a.CardNo == CardNo).FirstOrDefault();
}
return(card);
}
catch (Exception ex)
{
throw ex;
}
}
public string GetLastApplicableDate()
{
try
{
string result;
var db = new SoneriCISEntities();
//var context = (from c in db.tbl_Card_Charges_Recovery select c.ApplicableDate).LastOrDefault();
var context = db.tbl_Card_Charges_Recovery.OrderByDescending(r => r.ID).First();
result = context.ApplicableDate.Value.AddDays(1).Date.ToString("d");
return(result);
}
catch (Exception ex)
{
throw ex;
}
}
public List <tbl_Customer_Accounts> GetCustomerAccountbyCIF(string AccountNo, string CIF)
{
try
{
List <tbl_Customer_Accounts> lst = new List <tbl_Customer_Accounts>();
using (var db = new SoneriCISEntities())
{
lst = db.tbl_Customer_Accounts.Where(a => a.AccountNo == AccountNo && a.CIF == CIF).ToList();
}
return(lst);
}
catch (Exception ex)
{
throw ex;
}
}
public string GetLastSTAN()
{
try
{
string STAN = "";
tbl_transactions t = new DAL.tbl_transactions();
using (var db = new SoneriCISEntities())
{
t = db.tbl_transactions.OrderByDescending(a => a.ID).FirstOrDefault();
}
return(t.STAN);
}
catch (Exception ex)
{
throw ex;
}
}
public long GetLastID()
{
try
{
long ID = 0;
tbl_transactions t = new DAL.tbl_transactions();
using (var db = new SoneriCISEntities())
{
t = db.tbl_transactions.OrderByDescending(a => a.ID).FirstOrDefault();
}
return(t.ID);
}
catch (Exception ex)
{
throw ex;
}
}
public bool IsUniqueTransaction(string AccountNo, double?AmountToDeduct, string Narration)
{
bool IsUnique = false;
DateTime LastTransaction = DateTime.Now.AddSeconds(-60);
List <tbl_transactions> lst = new List <tbl_transactions>();
using (var db = new SoneriCISEntities())
{
lst = db.tbl_transactions.Where(x => x.DebitAccountNo == AccountNo && x.Amount == AmountToDeduct && x.TransactionDateTime == LastTransaction).ToList();
}
if (lst.Count <= 0)
{
IsUnique = true;
}
return(IsUnique);
}
public static bool ValidateLogin(string UserName)
{
var lastFiveMins = DateTime.Now.AddMinutes(-5); //mkhan: Need to change this, as don't know business
var current = DateTime.Now;
using (var db = new SoneriCISEntities())
{
var query = db.tbl_User_Log.Where(e => e.tbl_Users.UserLogin.ToLower() == UserName.ToLower() && e.EntityName == "login" && e.EventName == "l" && e.ActionTimestamp >= lastFiveMins && e.ActionTimestamp <= current && e.EntityID == null).Count();
if (query >= int.MaxValue) //mkhan: Need to remove this, as don't know business
{
return(false);
}
return(true);
}
}
public List <tbl_Card_Charges> GetCardChargesByCardAccount(int AccountTypeID, int CardTypeID)
{
try
{
List <tbl_Card_Charges> lst = new List <DAL.tbl_Card_Charges>();
using (var db = new SoneriCISEntities())
{
lst = db.tbl_Card_Charges.Where(a => a.IsActive == true && a.AccountTypeID == AccountTypeID && a.CardTypeID == CardTypeID).ToList();
lst = lst.Where(a => a.AuthorizationStatus == "A").ToList();
}
return(lst);
}
catch (Exception ex)
{
throw ex;
}
}
public static List <CardTypeVM> GetCardTypeRequestDetails(string authorizationStatus)
{
using (var db = new SoneriCISEntities())
{
return(db.tbl_Card_Types.Where(e => /*e.IsActive == true && */ e.AuthorizationStatus == authorizationStatus).Select(e => new CardTypeVM
{
ID = e.ID,
AuthorizationComments = e.AuthorizationComments,
AuthorizationStatus = e.AuthorizationStatus,
Title = e.Title,
Description = e.Description,
IsIssuance = e.IsIssuance,
IsReplacement = e.IsReplacement,
IsUpgrade = e.IsUpgrade
}).ToList());
}
}