public string Insert(ExpenseBill expenseBill)
{
int id = 0;
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction trn = connection.BeginTransaction();
try
{
var internalId = DatabaseCommonRepository.GetNewDocNo(connection, expenseBill.OrganizationId, 14, true, trn);
expenseBill.ExpenseNo = internalId;
if (expenseBill.SoOrJc == "JC")
{
expenseBill.SaleOrderId = null;
}
else
{
expenseBill.JobCardId = null;
}
string sql = string.Empty;
sql += "insert into ExpenseBill(ExpenseNo,ExpenseDate,ExpenseBillRef,ExpenseBillDate,ExpenseBillDueDate,SupplierId,ExpenseRemarks,TotalAddition,TotalDeduction,TotalAmount,CurrencyId,SaleOrderId,JobCardId,OrganizationId)";
sql += " values(@ExpenseNo,@ExpenseDate,@ExpenseBillRef,@ExpenseBillDate,@ExpenseBillDueDate,@SupplierId,@ExpenseRemarks,@TotalAddition,@TotalDeduction,@TotalAmount,@CurrencyId,@SaleOrderId,@JobCardId,@OrganizationId);";
sql += " SELECT CAST(SCOPE_IDENTITY() as int);";
id = connection.Query <int>(sql, expenseBill, trn).Single();
foreach (var item in expenseBill.ExpenseBillItem)
{
sql = string.Empty;
sql += "insert into ExpenseBillItem(ExpenseId, AddDedId, ExpenseItemRate, ExpenseItemQty, ExpenseItemAmount, ExpenseItemAddDed)";
sql += " values(@ExpenseId,ISNULL(@AddDedId,0), @ExpenseItemRate, @ExpenseItemQty, @ExpenseItemAmount, @ExpenseItemAddDed)";
item.ExpenseId = id;
item.ExpenseItemAddDed = 1;
connection.Query(sql, item, trn);
}
foreach (var item in expenseBill.deductions)
{
sql = string.Empty;
sql += "insert into ExpenseBillItem(ExpenseId, AddDedId, ExpenseItemRate, ExpenseItemQty, ExpenseItemAmount, ExpenseItemAddDed)";
sql += " values(@ExpenseId, ISNULL(@AddDedId,0), @ExpenseItemRate, @ExpenseItemQty, @ExpenseItemAmount, @ExpenseItemAddDed)";
item.ExpenseId = id;
item.ExpenseItemAddDed = 2;
connection.Query(sql, item, trn);
}
InsertLoginHistory(dataConnection, expenseBill.CreatedBy, "Create", "Expense Bill", id.ToString(), "0");
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
}
}
return(expenseBill.ExpenseNo);
}
public int Approve(ExpenseBill model)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
string query = @"UPDATE ExpenseBill SET isApproved = 1, ApprovedBy = @ApprovedBy WHERE ExpenseId = @ExpenseId";
return(connection.Execute(query, model));
}
}
public async Task <decimal> Update(ExpenseBill bill, decimal oldSum, int?oldAccountId)
{
var flow = await _repository.LoadAsync <ExpenseFlow>(bill.ExpenseFlowId);
var newAccount = bill.AccountId != null ? await _repository.LoadAsync <Account>(bill.AccountId.Value) : null;
flow.Balance = flow.Balance + oldSum;
var lack = bill.SumPrice - Math.Max(flow.Balance, 0);
decimal withdrawTotal = 0m;
if (lack > 0 && newAccount != null)
{
withdrawTotal = bill.SumPrice - lack;
newAccount.AvailBalance -= lack;
}
else
{
withdrawTotal = bill.SumPrice;
}
flow.Balance -= withdrawTotal;
flow.Version++;
_repository.Update(flow);
if (!bill.IsCorrection)
{
if (oldAccountId != bill.AccountId)
{
if (oldAccountId != null)
{
var oldAccount = await _repository.LoadAsync <Account>(oldAccountId.Value);
oldAccount.Balance += oldSum;
_repository.Update(oldAccount);
}
if (newAccount != null)
{
newAccount.Balance -= bill.SumPrice;
newAccount.LastWithdraw = DateTime.Now;
_repository.Update(newAccount);
}
}
else if (newAccount != null)
{
newAccount.Balance += oldSum - bill.SumPrice;
_repository.Update(newAccount);
}
}
else if (newAccount != null)
{
_repository.Update(newAccount);
}
return(newAccount?.Balance ?? 0);
}
public async Task <int> Create(ExpenseBillModel model, bool correction = false)
{
if (model == null)
{
throw new ArgumentNullException(nameof(model));
}
model.Validate();
var bill = new ExpenseBill
{
ExpenseFlowId = model.ExpenseFlowId,
AccountId = model.AccountId,
DateTime = model.DateTime,
SumPrice = model.Cost,
OwnerId = _currentSession.UserId,
IsCorrection = correction,
};
model.IsCorection = correction;
_repository.Create(bill);
foreach (var item in model.Items)
{
_repository.Create(new ExpenseItem
{
Bill = bill,
CategoryId = item.CategoryId,
ProductId = item.ProductId,
Price = item.Cost,
Quantity = item.Quantity,
Comment = item.Comment
});
}
Account account = null;
if (model.AccountId != null)
{
account = await _repository.LoadAsync <Account>(model.AccountId.Value);
}
if (account != null)
{
var balancesUpdater = _balancesUpdaterFactory.Create(account.AccountType);
await balancesUpdater.Create(account, bill).ConfigureAwait(false);
_transactionBuilder.CreateExpense(bill, account.Balance);
}
await _repository.SaveChangesAsync().ConfigureAwait(false);
model.Id = bill.Id;
return(model.Id);
}
public Task Create(Account account, ExpenseBill bill)
{
if (!bill.IsCorrection)
{
account.Balance -= bill.SumPrice;
}
account.AvailBalance -= bill.SumPrice;
_repository.Update(account);
return(Task.CompletedTask);
}
public async Task DeleteExpense(ExpenseBill bill)
{
var queries = _repository.GetQuery <Transaction>();
var transaction = await queries.SingleOrDefaultAsync(
x => x.InitiatorId == bill.AccountId && x.BillId == bill.Id)
.ConfigureAwait(false);
if (transaction == null)
{
return;
}
_repository.Delete(transaction);
}
public ActionResult Edit(ExpenseBill model)
{
ViewBag.Title = "Edit";
model.CreatedBy = UserID.ToString();
model.CreatedDate = DateTime.Now;
FillDropdowns();
var repo = new ExpenseRepository();
var result1 = new ExpenseRepository().CHECK(model.ExpenseId);
if (result1 > 0)
{
TempData["error"] = "Sorry!!..Already Used!!";
TempData["PurchaseRequestNo"] = null;
return(View("Edit", model));
}
else
{
try
{
var result2 = new ExpenseRepository().DeleteExpenseBillDT(model.ExpenseId);
string result3 = new ExpenseRepository().UpdateExpenseBillHD(model);
string id = new ExpenseRepository().InsertDT(model);
//TempData["success"] = "Updated Successfully (" + model.SalesInvoiceRefNo + ")";
//TempData["SalesInvoiceRefNo"] = model.SalesInvoiceRefNo;
TempData["success"] = "Updated successfully. Purchase Request Reference No. is " + result3;
TempData["error"] = "";
return(RedirectToAction("Create"));
}
catch (SqlException sx)
{
TempData["error"] = "Some error occured while connecting to database. Please check your network connection and try again.|" + sx.Message;
}
catch (NullReferenceException nx)
{
TempData["error"] = "Some required data was missing. Please try again.|" + nx.Message;
}
catch (Exception ex)
{
TempData["error"] = "Some error occured. Please try again.|" + ex.Message;
}
return(RedirectToAction("Create"));
}
}
public async Task Delete(ExpenseBill bill)
{
if (bill.AccountId == null)
{
return;
}
var account = await _repository.LoadAsync <Account>(bill.AccountId.Value);
if (!bill.IsCorrection)
{
account.Balance += bill.SumPrice;
}
account.AvailBalance += bill.SumPrice;
_repository.Update(account);
}
public async Task Delete(ExpenseBill bill)
{
var flow = await _repository.LoadAsync <ExpenseFlow>(bill.ExpenseFlowId).ConfigureAwait(false);
flow.Balance += bill.SumPrice;
_repository.Update(flow);
if (bill.AccountId != null && !bill.IsCorrection)
{
var account = await _repository.LoadAsync <Account>(bill.AccountId.Value);
account.Balance += bill.SumPrice;
_repository.Update(account);
}
}
public async Task UpdateExpense(ExpenseBill bill, int?oldAccountId, decimal balance)
{
var transactionQueries = _repository.GetQuery <Transaction>();
if (oldAccountId == bill.AccountId)
{
var transaction = await transactionQueries
.SingleOrDefaultAsync(x => x.OwnerId == bill.OwnerId &&
x.InitiatorId == bill.AccountId && x.BillId == bill.Id)
.ConfigureAwait(false);
if (transaction != null)
{
transaction.DateTime = bill.DateTime;
transaction.Total = bill.SumPrice;
transaction.Balance = balance;
_repository.Update(transaction);
}
}
else if (bill.AccountId != null)
{
var transaction = await transactionQueries
.SingleOrDefaultAsync(x => x.OwnerId == bill.OwnerId &&
x.InitiatorId == oldAccountId && x.BillId == bill.Id)
.ConfigureAwait(false);
if (transaction == null)
{
transaction = new Transaction
{
OwnerId = bill.OwnerId,
DateTime = bill.DateTime,
BillId = bill.Id,
InitiatorId = bill.AccountId.Value,
Total = bill.SumPrice,
Balance = balance
};
_repository.Create(transaction);
}
else
{
transaction.InitiatorId = bill.AccountId.Value;
transaction.Total = bill.SumPrice;
transaction.Balance = balance;
_repository.Update(transaction);
}
}
}
public ExpenseBill GetExpenseBill(int id)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
string query = @"SELECT *, CASE WHEN SaleOrderId IS NULL THEN 'JC' ELSE 'SO' END SoOrJc FROM ExpenseBill WHERE ExpenseId = @id";
ExpenseBill model = connection.Query <ExpenseBill>(query, new { id = id }).Single();
query = @"SELECT * FROM ExpenseBillItem WHERE ExpenseId = @id AND ExpenseItemAddDed = 1";
model.ExpenseBillItem = connection.Query <ExpenseBillItem>(query, new { id = id }).ToList() ?? new List <ExpenseBillItem>();
query = @"SELECT * FROM ExpenseBillItem WHERE ExpenseId = @id AND ExpenseItemAddDed = 2";
model.deductions = connection.Query <ExpenseBillItem>(query, new { id = id }).ToList() ?? new List <ExpenseBillItem>();
return(model);
}
}
public DateTime GetDueDate(DateTime d, int sup)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
var param = new DynamicParameters();
ExpenseBill ExpenseBill = connection.Query <ExpenseBill>(
"select DATEADD(day,CreditPeriod,@date) ExpenseBillDueDate FROM Supplier WHERE SupplierId= " + sup,
new { date = d }).Single <ExpenseBill>();
DateTime duedate = System.DateTime.Today;
if (ExpenseBill != null)
{
duedate = ExpenseBill.ExpenseBillDueDate;
}
return(duedate);
}
}
public ActionResult Approve(ExpenseBill model)
{
model.ApprovedBy = UserID;
int i = new ExpenseRepository().Approve(model);
if (i > 0)
{
TempData["success"] = "Approved Successfully";
return(RedirectToAction("PendingApproval"));
}
else
{
FillDropdowns();
TempData["error"] = "Some error occured while approving. Please try again.";
return(View("Create", model));
}
}
public void CreateExpense(ExpenseBill bill, decimal balance)
{
if (bill.AccountId == null)
{
return;
}
var transation = new Transaction
{
OwnerId = bill.OwnerId,
DateTime = bill.DateTime,
InitiatorId = bill.AccountId.Value,
Bill = bill,
Total = bill.SumPrice,
Balance = balance
};
_repository.Create(transation);
}
public ActionResult Create()
{
string internalId = "";
internalId = DatabaseCommonRepository.GetNextDocNo(14, OrganizationId);
FillDropdowns();
ExpenseBill expense = new ExpenseBill();
expense.ExpenseDate = expense.ExpenseBillDate = expense.ExpenseBillDueDate = DateTime.Now;
expense.ExpenseNo = internalId;
expense.CurrencyId = new CurrencyRepository().GetCurrencyFrmOrganization(OrganizationId).CurrencyId;
expense.ExpenseBillItem = new List <ExpenseBillItem>();
expense.deductions = new List <ExpenseBillItem>();
expense.ExpenseBillItem.Add(new ExpenseBillItem());
expense.deductions.Add(new ExpenseBillItem());
return(View(expense));
}
/// <summary>
/// Delete ExpenseBill HD Details
/// </summary>
/// <returns></returns>
public string UpdateExpenseBillHD(ExpenseBill objExpenseBill)
{
//string result;
using (IDbConnection connection = OpenConnection(dataConnection))
{
//string sql = @" DELETE FROM ExpenseBill WHERE ExpenseId=@Id";
string sql = @"UPDATE ExpenseBill SET ExpenseDate = @ExpenseDate,ExpenseBillRef=@ExpenseBillRef,
ExpenseBillDate=@ExpenseBillDate,ExpenseBillDueDate=@ExpenseBillDueDate,
SupplierId = @SupplierId,ExpenseRemarks=@ExpenseRemarks,TotalAddition=@TotalAddition,TotalDeduction=@TotalDeduction,
TotalAmount=@TotalAmount,CurrencyId=@CurrencyId,SaleOrderId=@SaleOrderId,
JobCardId = @JobCardId,OrganizationId = @OrganizationId
OUTPUT INSERTED.ExpenseNo WHERE ExpenseId = @ExpenseId";
{
var id = connection.Execute(sql, objExpenseBill);
return(objExpenseBill.ExpenseNo);
}
}
}
public async Task Create(Account account, ExpenseBill bill)
{
var flow = await _repository.LoadAsync <ExpenseFlow>(bill.ExpenseFlowId).ConfigureAwait(false);
if (flow == null)
{
throw new InvalidOperationException($"Can't find expense flow with id {bill.ExpenseFlowId}");
}
var lack = bill.SumPrice - Math.Max(flow.Balance, 0);
var withdrawTotal = 0m;
var needUpdateAccount = false;
if (lack > 0 && account != null)
{
withdrawTotal = bill.SumPrice - lack;
account.AvailBalance -= lack;
needUpdateAccount = true;
}
else
{
withdrawTotal = bill.SumPrice;
}
flow.Balance -= withdrawTotal;
flow.Version++;
_repository.Update(flow);
if (account != null && !bill.IsCorrection)
{
account.Balance -= bill.SumPrice;
account.LastWithdraw = DateTime.Now;
needUpdateAccount = true;
}
if (needUpdateAccount)
{
_repository.Update(account);
}
}
public ActionResult Create(ExpenseBill model)
{
model.CreatedBy = UserID.ToString();
model.CreatedDate = DateTime.Now;
model.OrganizationId = OrganizationId;
if (ModelState.IsValid)
{
ExpenseRepository repo = new ExpenseRepository();
TempData["success"] = "Saved Successfully. Reference no. is " + repo.Insert(model);
return(RedirectToAction("Create"));
}
else
{
FillSupplier();
FillAddition();
FillDeduction();
FillSO();
FillJC();
FillCurrency();
TempData["error"] = "Some error occurred. Please try again.";
return(View(model));
}
}