public IEnumerable <PersonOpeningViewModel> GetPersonOpeningListForIndex(int personId) { int OpeningDocCategoryId = new DocumentCategoryService(_unitOfWork).FindByName(TransactionDocCategoryConstants.Opening).DocumentCategoryId; IEnumerable <PersonOpeningViewModel> vm = (from H in db.LedgerHeader join L in db.Ledger on H.LedgerHeaderId equals L.LedgerHeaderId into LedgerTable from LedgerTab in LedgerTable.DefaultIfEmpty() where LedgerTab.LedgerAccount.PersonId == personId && H.DocType.DocumentCategoryId == OpeningDocCategoryId select new PersonOpeningViewModel { LedgerHeaderId = H.LedgerHeaderId, DocDate = H.DocDate, DocNo = H.DocNo, PartyDocNo = H.PartyDocNo, PartyDocDate = H.PartyDocDate ?? H.DocDate, Amount = LedgerTab.AmtDr != 0 ? LedgerTab.AmtDr : LedgerTab.AmtCr, DrCr = LedgerTab.AmtDr != 0 ? NatureConstants.Debit : NatureConstants.Credit, SiteName = H.Site.SiteName, DivisionName = H.Division.DivisionName, Narration = H.Narration }).ToList(); return(vm); }
public PersonOpeningViewModel GetPersonOpeningForEdit(int id) { PersonOpeningViewModel vm = (from H in db.LedgerHeader join L in db.Ledger on H.LedgerHeaderId equals L.LedgerHeaderId into LedgerTable from LedgerTab in LedgerTable.DefaultIfEmpty() where H.LedgerHeaderId == id select new PersonOpeningViewModel { LedgerHeaderId = H.LedgerHeaderId, DocTypeId = H.DocTypeId, DocDate = H.DocDate, DocNo = H.DocNo, PartyDocNo = H.PartyDocNo, PartyDocDate = H.PartyDocDate, Amount = LedgerTab.AmtDr != 0 ? LedgerTab.AmtDr : LedgerTab.AmtCr, DrCr = LedgerTab.AmtDr != 0 ? NatureConstants.Debit : NatureConstants.Credit, Narration = H.Narration, PersonId = LedgerTab.LedgerAccount.PersonId ?? 0, DivisionId = H.DivisionId, SiteId = H.SiteId }).FirstOrDefault(); return(vm); }
public void DeleteLedgerTransaction(int JobInvoiceHeaderId) { var Header = new JobInvoiceHeaderService(_unitOfWork).Find(JobInvoiceHeaderId); int TanaPaymentDueDocTypeId = new DocumentTypeService(_unitOfWork).Find(TanaPaymentDueDocType).DocumentTypeId; int TdsCollectionDocTypeId = new DocumentTypeService(_unitOfWork).Find(TdsCollectionDocType).DocumentTypeId; var LedgerHeaderList = (from H in db.LedgerHeader where H.ReferenceDocId == Header.JobInvoiceHeaderId && H.ReferenceDocTypeId == Header.DocTypeId && (H.DocTypeId == TanaPaymentDueDocTypeId || H.DocTypeId == TdsCollectionDocTypeId) select H).ToList(); foreach (var LedgerHeader in LedgerHeaderList) { var ExisitingLedgers = (from p in db.Ledger where p.LedgerHeaderId == LedgerHeader.LedgerHeaderId select p).ToList(); var LedgerLines = (from p in db.LedgerLine where p.LedgerHeaderId == LedgerHeader.LedgerHeaderId select p).ToList(); foreach (var item in ExisitingLedgers) { var LedgerAdjs = (from p in db.LedgerAdj where p.CrLedgerId == item.LedgerId || p.DrLedgerId == item.LedgerId select p).ToList(); foreach (var item2 in LedgerAdjs) { new LedgerAdjService(_unitOfWork).Delete(item2.LedgerAdjId); //item2.ObjectState = Model.ObjectState.Deleted; //db.LedgerAdj.Remove(item2); } //item.ObjectState = Model.ObjectState.Deleted; //db.Ledger.Remove(item); new LedgerService(_unitOfWork).Delete(item.LedgerId); } foreach (var item in LedgerLines) { var RefLines = (from p in db.LedgerLineRefValue where p.LedgerLineId == item.LedgerLineId select p).ToList(); foreach (var RefLine in RefLines) { //RefLine.ObjectState = Model.ObjectState.Deleted; //db.LedgerLineRefValue.Remove(RefLine); new LedgerLineRefValueService(_unitOfWork).Delete(RefLine.LedgerLineRefValueId); } //item.ObjectState = Model.ObjectState.Deleted; //db.LedgerLine.Remove(item); new LedgerLineService(_unitOfWork).Delete(item.LedgerLineId); } //LedgerHeader.ObjectState = Model.ObjectState.Deleted; //db.LedgerHeader.Remove(LedgerHeader); new LedgerHeaderService(_unitOfWork).Delete(LedgerHeader.LedgerHeaderId); } var LedgerAdj = (from L in db.JobInvoiceLine join H in db.JobInvoiceHeader on L.JobInvoiceHeaderId equals H.JobInvoiceHeaderId into JobInvoiceHeaderTable from JobInvoiceHeaderTab in JobInvoiceHeaderTable.DefaultIfEmpty() join Ld in db.Ledger on new { A1 = L.JobInvoiceLineId, A2 = JobInvoiceHeaderTab.DocTypeId } equals new { A1 = Ld.ReferenceDocLineId ?? 0, A2 = Ld.ReferenceDocTypeId ?? 0 } into LedgerTable from LedgerTab in LedgerTable.DefaultIfEmpty() join LAd in db.LedgerAdj on LedgerTab.LedgerId equals LAd.CrLedgerId into LedgerAdjTable from LedgerAdjTab in LedgerAdjTable.DefaultIfEmpty() where L.JobInvoiceHeaderId == JobInvoiceHeaderId && LedgerAdjTab.Adj_Type == "Advance Adjustment" select LedgerAdjTab).ToList(); foreach (var item in LedgerAdj) { //item.ObjectState = Model.ObjectState.Deleted; //db.LedgerAdj.Remove(item); new LedgerAdjService(_unitOfWork).Delete(item.LedgerAdjId); } }
public void LedgerPosting(ref LedgerHeaderViewModel LedgerHeaderViewModel, IEnumerable <CalculationHeaderCharge> HeaderTable, IEnumerable <CalculationLineCharge> LineTable) { int PersonAccountId = 6612; int LedgerHeaderId = 0; if (LedgerHeaderViewModel.LedgerHeaderId == 0) { LedgerHeader LedgerHeader = new LedgerHeader(); LedgerHeader.DocHeaderId = LedgerHeaderViewModel.DocHeaderId; LedgerHeader.DocTypeId = LedgerHeaderViewModel.DocTypeId; LedgerHeader.ProcessId = LedgerHeaderViewModel.ProcessId; LedgerHeader.DocDate = LedgerHeaderViewModel.DocDate; LedgerHeader.DocNo = LedgerHeaderViewModel.DocNo; LedgerHeader.DivisionId = LedgerHeaderViewModel.DivisionId; LedgerHeader.SiteId = LedgerHeaderViewModel.SiteId; LedgerHeader.Narration = LedgerHeaderViewModel.Narration; LedgerHeader.Remark = LedgerHeaderViewModel.Remark; LedgerHeader.CreatedBy = LedgerHeaderViewModel.CreatedBy; LedgerHeader.CreatedDate = DateTime.Now.Date; LedgerHeader.ModifiedBy = LedgerHeaderViewModel.ModifiedBy; LedgerHeader.ModifiedDate = DateTime.Now.Date; new LedgerHeaderService(_unitOfWork).Create(LedgerHeader); } else { LedgerHeader LedgerHeader = new LedgerHeaderService(_unitOfWork).Find((int)LedgerHeaderViewModel.LedgerHeaderId); LedgerHeader.DocHeaderId = LedgerHeaderViewModel.DocHeaderId; LedgerHeader.DocTypeId = LedgerHeaderViewModel.DocTypeId; LedgerHeader.ProcessId = LedgerHeaderViewModel.ProcessId; LedgerHeader.DocDate = LedgerHeaderViewModel.DocDate; LedgerHeader.DocNo = LedgerHeaderViewModel.DocNo; LedgerHeader.DivisionId = LedgerHeaderViewModel.DivisionId; LedgerHeader.SiteId = LedgerHeaderViewModel.SiteId; LedgerHeader.Narration = LedgerHeaderViewModel.Narration; LedgerHeader.Remark = LedgerHeaderViewModel.Remark; LedgerHeader.ModifiedBy = LedgerHeaderViewModel.ModifiedBy; LedgerHeader.ModifiedDate = DateTime.Now.Date; new LedgerHeaderService(_unitOfWork).Update(LedgerHeader); IEnumerable <Ledger> LedgerList = new LedgerService(_unitOfWork).FindForLedgerHeader(LedgerHeader.LedgerHeaderId); var LedgerAdjDrList = (from H in db.LedgerHeader join L in db.Ledger on H.LedgerHeaderId equals L.LedgerHeaderId into LedgerTable from LedgerTab in LedgerTable.DefaultIfEmpty() join La in db.LedgerAdj on LedgerTab.LedgerId equals La.DrLedgerId into LedgerAdjTable from LedgerAdjTab in LedgerAdjTable.DefaultIfEmpty() where H.LedgerHeaderId == LedgerHeader.LedgerHeaderId && LedgerAdjTab.LedgerAdjId != null select LedgerAdjTab).ToList(); foreach (LedgerAdj item in LedgerAdjDrList) { new LedgerAdjService(_unitOfWork).Delete(item.LedgerAdjId); } var LedgerAdjCrList = (from H in db.LedgerHeader join L in db.Ledger on H.LedgerHeaderId equals L.LedgerHeaderId into LedgerTable from LedgerTab in LedgerTable.DefaultIfEmpty() join La in db.LedgerAdj on LedgerTab.LedgerId equals La.CrLedgerId into LedgerAdjTable from LedgerAdjTab in LedgerAdjTable.DefaultIfEmpty() where H.LedgerHeaderId == LedgerHeader.LedgerHeaderId && LedgerAdjTab.LedgerAdjId != null select LedgerAdjTab).ToList(); foreach (LedgerAdj item in LedgerAdjCrList) { new LedgerAdjService(_unitOfWork).Delete(item.LedgerAdjId); } foreach (Ledger item in LedgerList) { new LedgerService(_unitOfWork).Delete(item); } LedgerHeaderId = LedgerHeader.LedgerHeaderId; } IEnumerable <LedgerPostingViewModel> LedgerHeaderAmtDr = from H in HeaderTable join A in db.LedgerAccount on H.PersonID equals A.PersonId into LedgerAccountTable from LedgerAccountTab in LedgerAccountTable.DefaultIfEmpty() where H.LedgerAccountDrId != null && H.Amount != 0 && H.Amount != null select new LedgerPostingViewModel { LedgerAccountId = (int)(H.LedgerAccountDrId == PersonAccountId ? LedgerAccountTab.LedgerAccountId : H.LedgerAccountDrId), ContraLedgerAccountId = (H.LedgerAccountCrId == PersonAccountId ? LedgerAccountTab.LedgerAccountId : H.LedgerAccountCrId), CostCenterId = H.CostCenterId, AmtDr = Math.Abs((H.Amount > 0 ? H.Amount : 0) ?? 0), AmtCr = Math.Abs((H.Amount < 0 ? H.Amount : 0) ?? 0) }; IEnumerable <LedgerPostingViewModel> LedgerHeaderAmtCr = from H in HeaderTable join A in db.LedgerAccount on H.PersonID equals A.PersonId into LedgerAccountTable from LedgerAccountTab in LedgerAccountTable.DefaultIfEmpty() where H.LedgerAccountCrId != null && H.Amount != 0 && H.Amount != null select new LedgerPostingViewModel { LedgerAccountId = (int)(H.LedgerAccountCrId == PersonAccountId ? LedgerAccountTab.LedgerAccountId : H.LedgerAccountCrId), ContraLedgerAccountId = (H.LedgerAccountDrId == PersonAccountId ? LedgerAccountTab.LedgerAccountId : H.LedgerAccountDrId), CostCenterId = H.CostCenterId, AmtCr = Math.Abs((H.Amount > 0 ? H.Amount : 0) ?? 0), AmtDr = Math.Abs((H.Amount < 0 ? H.Amount : 0) ?? 0) }; IEnumerable <LedgerPostingViewModel> LedgerLineAmtDr = from L in LineTable join A in db.LedgerAccount on L.PersonID equals A.PersonId into LedgerAccountTable from LedgerAccountTab in LedgerAccountTable.DefaultIfEmpty() where L.LedgerAccountDrId != null && L.Amount != 0 && L.Amount != null select new LedgerPostingViewModel { LedgerAccountId = (int)(L.LedgerAccountDrId == PersonAccountId ? LedgerAccountTab.LedgerAccountId : L.LedgerAccountDrId), ContraLedgerAccountId = (L.LedgerAccountCrId == PersonAccountId ? LedgerAccountTab.LedgerAccountId : L.LedgerAccountCrId), CostCenterId = L.CostCenterId, AmtDr = Math.Abs((L.Amount > 0 ? L.Amount : 0) ?? 0), AmtCr = Math.Abs((L.Amount < 0 ? L.Amount : 0) ?? 0) }; IEnumerable <LedgerPostingViewModel> LedgerLineAmtCr = from L in LineTable join A in db.LedgerAccount on L.PersonID equals A.PersonId into LedgerAccountTable from LedgerAccountTab in LedgerAccountTable.DefaultIfEmpty() where L.LedgerAccountCrId != null && L.Amount != 0 && L.Amount != null select new LedgerPostingViewModel { LedgerAccountId = (int)(L.LedgerAccountCrId == PersonAccountId ? LedgerAccountTab.LedgerAccountId : L.LedgerAccountCrId), ContraLedgerAccountId = (L.LedgerAccountDrId == PersonAccountId ? LedgerAccountTab.LedgerAccountId : L.LedgerAccountDrId), CostCenterId = L.CostCenterId, AmtCr = Math.Abs((L.Amount > 0 ? L.Amount : 0) ?? 0), AmtDr = Math.Abs((L.Amount < 0 ? L.Amount : 0) ?? 0) }; IEnumerable <LedgerPostingViewModel> LedgerCombind = LedgerHeaderAmtDr.Union(LedgerHeaderAmtCr).Union(LedgerLineAmtDr).Union(LedgerLineAmtCr); //IEnumerable<LedgerPostingViewModel> LedgerPost = from L in LedgerCombind // group new { L } by new { L.LedgerAccountId, L.ContraLedgerAccountId, L.CostCenterId } into Result // select new LedgerPostingViewModel // { // LedgerAccountId = Result.Key.LedgerAccountId, // ContraLedgerAccountId = Result.Key.ContraLedgerAccountId, // CostCenterId = Result.Key.CostCenterId, // AmtDr = Result.Sum(i => i.L.AmtDr), // AmtCr = Result.Sum(i => i.L.AmtCr) // }; IEnumerable <LedgerPostingViewModel> LedgerPost1 = from L in LedgerCombind join Ca in db.LedgerAccount on L.ContraLedgerAccountId equals Ca.LedgerAccountId into ContraLedgerAccountTable from ContraLedgerAccountTab in ContraLedgerAccountTable.DefaultIfEmpty() join Cag in db.LedgerAccountGroup on ContraLedgerAccountTab.LedgerAccountGroupId equals Cag.LedgerAccountGroupId into ContraLedgerAccountGroupTable from ContraLedgerAccountGroupTab in ContraLedgerAccountGroupTable.DefaultIfEmpty() group new { L, ContraLedgerAccountGroupTab } by new { L.LedgerAccountId, L.ContraLedgerAccountId, L.CostCenterId } into Result select new LedgerPostingViewModel { LedgerAccountId = Result.Key.LedgerAccountId, ContraLedgerAccountId = Result.Key.ContraLedgerAccountId, ContraLedgerAccountWeightage = Result.Max(i => i.ContraLedgerAccountGroupTab.Weightage ?? 0), CostCenterId = Result.Key.CostCenterId, AmtDr = Result.Sum(i => i.L.AmtDr), AmtCr = Result.Sum(i => i.L.AmtCr) }; IEnumerable <LedgerPostingViewModel> LedgerPost2 = from L in LedgerPost1 group new { L } by new { L.LedgerAccountId, L.CostCenterId } into Result select new LedgerPostingViewModel { LedgerAccountId = Result.Key.LedgerAccountId, ContraLedgerAccountWeightage = Result.Max(i => i.L.ContraLedgerAccountWeightage ?? 0), CostCenterId = Result.Key.CostCenterId, AmtDr = Result.Sum(i => i.L.AmtDr) - Result.Sum(i => i.L.AmtCr) > 0 ? Result.Sum(i => i.L.AmtDr) - Result.Sum(i => i.L.AmtCr) : 0, AmtCr = Result.Sum(i => i.L.AmtCr) - Result.Sum(i => i.L.AmtDr) > 0 ? Result.Sum(i => i.L.AmtCr) - Result.Sum(i => i.L.AmtDr) : 0, }; IEnumerable <LedgerPostingViewModel> LedgerPost = from L2 in LedgerPost2 join L1 in LedgerPost1 on new { A1 = L2.LedgerAccountId, A2 = L2.CostCenterId, A3 = L2.ContraLedgerAccountWeightage } equals new { A1 = L1.LedgerAccountId, A2 = L1.CostCenterId, A3 = L1.ContraLedgerAccountWeightage } into LedgerPost1Table from LedgerPost1Tab in LedgerPost1Table.DefaultIfEmpty() group new { LedgerPost1Tab, L2 } by new { L2.LedgerAccountId, L2.CostCenterId } into Result select new LedgerPostingViewModel { LedgerAccountId = Result.Key.LedgerAccountId, ContraLedgerAccountId = Result.Max(i => i.LedgerPost1Tab.ContraLedgerAccountId), CostCenterId = Result.Key.CostCenterId, AmtDr = Result.Max(i => i.L2.AmtDr), AmtCr = Result.Max(i => i.L2.AmtCr) }; var temp = (from L in LedgerPost group L by 1 into Result select new { AmtDr = Result.Sum(i => i.AmtDr), AmtCr = Result.Sum(i => i.AmtCr) }).FirstOrDefault(); if (temp != null) { if (temp.AmtDr != temp.AmtCr) { throw new Exception("Debit amount and credit amount is not equal.Check account posting."); } } foreach (LedgerPostingViewModel item in LedgerPost) { Ledger Ledger = new Ledger(); if (LedgerHeaderId != 0) { Ledger.LedgerHeaderId = LedgerHeaderId; } Ledger.LedgerAccountId = item.LedgerAccountId; Ledger.ContraLedgerAccountId = item.ContraLedgerAccountId; var TempCostCenter = (from C in db.CostCenter where C.CostCenterId == item.CostCenterId && C.LedgerAccountId == item.LedgerAccountId select new { CostCenterId = C.CostCenterId }).FirstOrDefault(); if (TempCostCenter != null) { Ledger.CostCenterId = item.CostCenterId; } Ledger.AmtDr = item.AmtDr * (LedgerHeaderViewModel.ExchangeRate ?? 1); Ledger.AmtCr = item.AmtCr * (LedgerHeaderViewModel.ExchangeRate ?? 1); Ledger.Narration = ""; new LedgerService(_unitOfWork).Create(Ledger); } }