public Data.Models.Firm[] GetFirmList()
{
using (Data.Models.ProAccountContext db = new Data.Models.ProAccountContext())
{
return(db.Firms.OrderBy(o => o.Name).ToArray());
}
}
public Data.Models.Account[] GetAccountByFirm(int firmId)
{
using (Data.Models.ProAccountContext db = new Data.Models.ProAccountContext())
{
return(db.Accounts.Where(w => w.FirmId == firmId).OrderBy(o => o.ContactName).ToArray());
}
}
public Data.Models.Firm[] GetAllFirm()
{
using (Data.Models.ProAccountContext db = new Data.Models.ProAccountContext())
{
return(db.Firms.ToArray());
}
}
public Data.Models.Firm GetFirmByName(string Name)
{
using (Data.Models.ProAccountContext db = new Data.Models.ProAccountContext())
{
return(db.Firms.FirstOrDefault(w => w.Name == Name));
}
}
public void SaveAccountType(Data.Models.AccountType accountType)
{
try
{
using (Data.Models.ProAccountContext db = new Data.Models.ProAccountContext())
{
if (accountType.Id > 0)
{
var data = db.AccountTypes.FirstOrDefault(w => w.Id == accountType.Id);
if (data != null)
{
data.Type = accountType.Type;
db.SaveChanges();
}
}
else
{
db.AccountTypes.Add(new Data.Models.AccountType
{
Type = accountType.Type,
});
db.SaveChanges();
}
}
}
catch (SqlException ex)
{
}
}
public Data.Models.AccountType[] GetAccountType()
{
using (Data.Models.ProAccountContext db = new Data.Models.ProAccountContext())
{
return(db.AccountTypes.OrderBy(o => o.Type).ToArray());
}
}
public Data.Models.Account GetAccountByName(string Name)
{
using (Data.Models.ProAccountContext db = new Data.Models.ProAccountContext())
{
return(db.Accounts.FirstOrDefault(w => Name.Contains(w.ContactName)));
}
}
public Array GetData(string Qry, string tblName)
{
try
{
using (Data.Models.ProAccountContext db = new Data.Models.ProAccountContext())
{
if (tblName == "Firm")
{
return(db.Database.SqlQuery <FirmResultModel>(Qry).ToArray());
}
else if (tblName == "Account")
{
return(db.Database.SqlQuery <AccountResultModel>(Qry).ToArray());
}
else if (tblName == "AccountType")
{
return(db.Database.SqlQuery <AccountTypeResultModel>(Qry).ToArray());
}
else
{
return(null);
}
}
}
catch (SqlException ex)
{
//MsgBox(ex.Message);
return(null);
}
}
public List <AccountTransactionResultModel> GetAllTransactionData(int FirmId, int AccountId, DateTime toDate, DateTime fromDate)
{
try
{
using (Data.Models.ProAccountContext db = new Data.Models.ProAccountContext())
{
var todayData = from a in db.AccountTransactions
where a.TransactionDate.Year >= fromDate.Year &&
a.TransactionDate.Month >= fromDate.Month &&
a.TransactionDate.Day >= fromDate.Day &&
a.TransactionDate.Year <= toDate.Year &&
a.TransactionDate.Month <= toDate.Month &&
a.TransactionDate.Day <= toDate.Day &&
a.FirmId == FirmId &&
a.AccountId == AccountId
orderby a.TransactionDate descending
select new AccountTransactionResultModel
{
Id = a.Id,
FirmName = a.Firm.Name,
AccountName = a.Account.ContactName,
Amount = a.Amount,
TransactionsType = a.TransactionsType,
PaymentType = a.PaymentType,
TransactionDate = a.TransactionDate,
Remarks = a.Remarks
};
return(todayData.ToList());
}
}
catch (SqlException ex)
{
return(null);
}
}
public Data.Models.Account GetAccountById(int Id)
{
using (Data.Models.ProAccountContext db = new Data.Models.ProAccountContext())
{
return(db.Accounts.FirstOrDefault(w => w.Id == Id));
}
}
public void DeleteAccountById(int Id)
{
using (Data.Models.ProAccountContext db = new Data.Models.ProAccountContext())
{
var data = db.Accounts.FirstOrDefault(W => W.Id == Id);
if (data != null)
{
db.Accounts.Remove(data);
db.SaveChanges();
}
}
}
public Data.Models.AccountTransaction GetTransactionById(int Id)
{
try
{
using (Data.Models.ProAccountContext db = new Data.Models.ProAccountContext())
{
return(db.AccountTransactions.FirstOrDefault(w => w.Id == Id));
}
}
catch (Exception ex)
{
return(null);
}
}
public Data.Models.Account GetOpeningBalance(int FirmId, int AccountId)
{
try
{
using (Data.Models.ProAccountContext db = new Data.Models.ProAccountContext())
{
return(db.Accounts.FirstOrDefault(w => w.Id == AccountId && w.FirmId == FirmId));
}
}
catch (SqlException ex)
{
return(null);
}
}
public Data.Models.ChequeDetail GetChequeDetailsById(int TransactionId)
{
try
{
using (Data.Models.ProAccountContext db = new Data.Models.ProAccountContext())
{
return(db.ChequeDetails.FirstOrDefault(w => w.TransactionId == TransactionId));
}
}
catch (SqlException)
{
return(null);
}
}
public Data.Models.User GetUserName(string UserName)
{
try
{
using (Data.Models.ProAccountContext db = new Data.Models.ProAccountContext())
{
return(db.Users.FirstOrDefault(w => w.UserName == UserName));
}
}
catch (SqlException ex)
{
return(null);
}
}
public bool IsAccountCodeExists(string AccountCode)
{
using (Data.Models.ProAccountContext db = new Data.Models.ProAccountContext())
{
var data = db.Accounts.FirstOrDefault(W => W.AccountCode == AccountCode);
if (data != null)
{
return(true);
}
else
{
return(false);
}
}
}
public bool FirmIsExists(string Name)
{
using (Data.Models.ProAccountContext db = new Data.Models.ProAccountContext())
{
var data = db.Firms.FirstOrDefault(w => w.Name == Name);
if (data != null)
{
return(true);
}
else
{
return(false);
}
}
}
public void DeleteTransactionById(int Id)
{
using (Data.Models.ProAccountContext db = new Data.Models.ProAccountContext())
{
var data = db.AccountTransactions.FirstOrDefault(w => w.Id == Id);
if (data != null)
{
if (data.PaymentType == "Cheque")
{
var chequeuData = db.ChequeDetails.FirstOrDefault(w => w.TransactionId == Id);
db.ChequeDetails.Remove(chequeuData);
db.SaveChanges();
}
db.AccountTransactions.Remove(data);
db.SaveChanges();
}
}
}
public void SaveFirm(int Id, string Name)
{
using (Data.Models.ProAccountContext db = new Data.Models.ProAccountContext())
{
if (Id > 0)
{
var result = db.Firms.FirstOrDefault(w => w.Id == Id);
if (result != null)
{
result.Name = Name;
db.SaveChanges();
}
}
else
{
db.Firms.Add(new Data.Models.Firm {
Name = Name
});
db.SaveChanges();
}
}
}
public void SaveAccount(Data.Models.Account accountData)
{
using (Data.Models.ProAccountContext db = new Data.Models.ProAccountContext())
{
if (accountData.Id > 0)
{
var data = db.Accounts.FirstOrDefault(W => W.Id == accountData.Id);
if (data != null)
{
data.FirmId = accountData.FirmId;
data.ContactName = accountData.ContactName;
data.ContactNo = accountData.ContactNo;
data.AccountCode = accountData.AccountCode;
data.AccountTypes = accountData.AccountTypes;
data.Address = accountData.Address;
data.Email = accountData.Email;
data.OpeningCredit = accountData.OpeningCredit;
data.OpeningDebit = accountData.OpeningDebit;
db.SaveChanges();
}
}
else
{
db.Accounts.Add(new Data.Models.Account {
FirmId = accountData.FirmId,
ContactName = accountData.ContactName,
ContactNo = accountData.ContactNo,
AccountCode = accountData.AccountCode,
AccountTypes = accountData.AccountTypes,
Address = accountData.Address,
Email = accountData.Email,
OpeningCredit = accountData.OpeningCredit,
OpeningDebit = accountData.OpeningDebit
});
db.SaveChanges();
}
}
}
public List <AccountTransactionReportModel> GetTransactionReportData(int FirmId, int AccountId, DateTime toDate, DateTime fromDate)
{
try
{
using (Data.Models.ProAccountContext db = new Data.Models.ProAccountContext())
{
var todayData = from a in db.AccountTransactions
where a.TransactionDate.Year >= fromDate.Year &&
a.TransactionDate.Month >= fromDate.Month &&
a.TransactionDate.Day >= fromDate.Day &&
a.TransactionDate.Year <= toDate.Year &&
a.TransactionDate.Month <= toDate.Month &&
a.TransactionDate.Day <= toDate.Day &&
a.FirmId == FirmId &&
a.AccountId == AccountId
orderby a.TransactionDate descending
select new AccountTransactionReportModel
{
Id = a.Id,
FirmName = a.Firm.Name,
AccountName = a.Account.ContactName,
Amount = a.Amount,
TransactionsType = a.TransactionsType,
PaymentType = a.PaymentType,
TransactionDate = a.TransactionDate,
Remarks = a.Remarks,
//OpeningCredit=(a.Account.OpeningCredit != null) ? a.Account.OpeningCredit : 0,
//OpeningDebit = (a.Account.OpeningDebit != null) ? a.Account.OpeningDebit : 0,
//Balance =(a.TransactionsType == "Cr")? ((a.Account.OpeningCredit - a.Account.OpeningDebit) + a.Amount) : ((a.Account.OpeningCredit - a.Account.OpeningDebit) - a.Amount)
};
return(todayData.ToList());
}
}
catch (SqlException ex)
{
return(null);
}
}
public void SaveTransaction(Data.Models.AccountTransaction datamodel, Data.Models.ChequeDetail chequeData)
{
try
{
using (Data.Models.ProAccountContext db = new Data.Models.ProAccountContext())
{
if (datamodel.Id > 0)
{
var existingdata = db.AccountTransactions.FirstOrDefault(W => W.Id == datamodel.Id);
if (existingdata != null)
{
existingdata.FirmId = datamodel.FirmId;
existingdata.AccountId = datamodel.AccountId;
existingdata.TransactionDate = datamodel.TransactionDate;
existingdata.TransactionsType = datamodel.TransactionsType;
existingdata.Amount = datamodel.Amount;
existingdata.PaymentType = datamodel.PaymentType;
existingdata.Remarks = datamodel.Remarks;
}
db.SaveChanges();
if (chequeData != null && datamodel.PaymentType == "Cheque")
{
db.ChequeDetails.Add(new Data.Models.ChequeDetail
{
TransactionId = datamodel.Id,
BankName = chequeData.BankName,
ChequeNo = chequeData.ChequeNo,
IFSCCode = chequeData.IFSCCode,
ChequeDate = chequeData.ChequeDate
});
db.SaveChanges();
}
}
else
{
if (datamodel != null)
{
var transaction = db.AccountTransactions.Add(new Data.Models.AccountTransaction
{
TransactionDate = datamodel.TransactionDate,
FirmId = datamodel.FirmId,
AccountId = datamodel.AccountId,
TransactionsType = datamodel.TransactionsType,
Amount = datamodel.Amount,
PaymentType = datamodel.PaymentType,
Remarks = datamodel.Remarks
});
db.SaveChanges();
if (chequeData != null && datamodel.PaymentType == "Cheque")
{
db.ChequeDetails.Add(new Data.Models.ChequeDetail
{
TransactionId = transaction.Id,
BankName = chequeData.BankName,
ChequeNo = chequeData.ChequeNo,
IFSCCode = chequeData.IFSCCode,
ChequeDate = chequeData.ChequeDate
});
db.SaveChanges();
}
}
}
}
}
catch (SqlException ex)
{
}
}
