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