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