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)); } }