public ActionResult CustomLedger(DtoCustomLedger CLdr)
{
DataTable CusLdr = _repo.GetCustomLedger(CLdr);
CusLdr.TableName = "DS_CustomLedger";
ReportViewer reportViewer = new ReportViewer();
reportViewer.ProcessingMode = ProcessingMode.Local;
reportViewer.SizeToReportContent = true;
reportViewer.Width = Unit.Percentage(900);
reportViewer.Height = Unit.Percentage(900);
reportViewer.LocalReport.ReportPath = Request.MapPath(Request.ApplicationPath) + @"Reports\CustomLedger.rdlc";
reportViewer.LocalReport.DataSources.Add(new ReportDataSource("DS_CustomLedger", CusLdr));
ReportParameterCollection rptParam = new ReportParameterCollection
{
new ReportParameter("ParamDateFrom", CLdr.StartDate.ToShortDateString()),
new ReportParameter("ParamDateTo", CLdr.EndDate.ToShortDateString()),
new ReportParameter("DOName", CLdr.AccString)
};
reportViewer.LocalReport.SetParameters(rptParam);
ViewBag.ReportViewer = reportViewer;
ViewBag.ReportTitle = "Customer Ledger";
return(View("_ReportView"));
}
public DataTable GetCustomLedger(DtoCustomLedger CLdr)
{
DataTable DT = new DataTable();
DT = (from Main in
((from MainLDR in
((from AVOO in
((from AVO in
((from AV in _db.AccVouchers
where
((
from AAV in _db.AccVouchers
where AAV.AccString == CLdr.AccString
select new { AAV.VNo }
).Distinct()).Contains(new { VNo = AV.VNo })
select new
{
AV.VDate,
AV.VNo,
AV.AccString,
AV.Debit,
AV.Credit,
AV.HeadCode,
AV.SubHeadCode,
AV.AccCode
}
))
where (
(AVO.HeadCode == 41 ||
AVO.HeadCode == 44 ||
AVO.HeadCode == 43 ||
AVO.HeadCode == 14)
//||
//AVO.HeadCode == 13
)
select new
{
AVO.VDate,
AVO.AccString,
AVO.Debit,
AVO.Credit,
AVO.HeadCode,
AVO.SubHeadCode,
AVO.AccCode
}) //AVO
)
join AA in _db.AccAccounts on AVOO.AccString equals AA.AccString
join AH in _db.AccHead on AVOO.HeadCode equals AH.HeadCode
join AT in _db.AccTypes on AH.TypeCode equals AT.TypeCode
group new { AA, AVOO, AT } by new
{
AVOO.VDate,
AVOO.AccString,
AA.AccName,
AT.TypeCode
} into g
where (g.Key.VDate >= CLdr.StartDate && g.Key.VDate <= CLdr.EndDate)
select new
{
VDate = (DateTime?)g.Key.VDate,
g.Key.AccString,
g.Key.AccName,
SDebit = (decimal?)g.Sum(p => p.AVOO.Debit),
SCredit = (decimal?)g.Sum(p => p.AVOO.Credit),
SBalance =
g.Key.TypeCode == 1 ||
g.Key.TypeCode == 5 ? (g.Sum(p => ((Decimal?)p.AVOO.Debit ?? (Decimal?)0)) - g.Sum(p => ((Decimal?)p.AVOO.Credit ?? (Decimal?)0))) : (g.Sum(p => ((Decimal?)p.AVOO.Credit ?? (Decimal?)0)) - g.Sum(p => ((Decimal?)p.AVOO.Debit ?? (Decimal?)0)))
}
))
select new
{
MainLDR.VDate,
MainLDR.AccString,
MainLDR.AccName,
MainLDR.SDebit,
MainLDR.SCredit,
MainLDR.SBalance
}
))
join Opening in
(from O in
(
(from AV in _db.AccVouchers
where
AV.AccString == CLdr.AccString
group AV by new
{
AV.VDate
} into g
select new
{
VVDate = g.Key.VDate,
Balance = (decimal?)(g.Sum(p => p.Debit) - g.Sum(p => p.Credit))
}))
select new
{
O.VVDate,
O.Balance,
OBalance = (decimal?)
(from OC in
(
(from AV in _db.AccVouchers
where
AV.AccString == CLdr.AccString
group AV by new
{
AV.VDate
} into g
select new
{
VVDate = g.Key.VDate,
Balance = (decimal?)(g.Sum(p => p.Debit) - g.Sum(p => p.Credit))
}))
where
OC.VVDate <= (DateTime)O.VVDate
select new
{
OC.Balance
}).Sum(p => p.Balance) - O.Balance
}) on Main.VDate equals Opening.VVDate into Opn
from Op in Opn.DefaultIfEmpty()
join Closing in
(from C in
(
(from AV in _db.AccVouchers
where
AV.AccString == CLdr.AccString
group AV by new
{
AV.VDate
} into g
select new
{
VVDate = g.Key.VDate,
Balance = (decimal?)(g.Sum(p => p.Debit) - g.Sum(p => p.Credit))
}))
select new
{
C.VVDate,
C.Balance,
CBalance = (decimal?)
(from CC in
(
(from AV in _db.AccVouchers
where
AV.AccString == CLdr.AccString
group AV by new
{
AV.VDate
} into g
select new
{
VVDate = g.Key.VDate,
Balance = (decimal?)(g.Sum(p => p.Debit) - g.Sum(p => p.Credit))
}))
where
CC.VVDate <= (DateTime)C.VVDate
select new
{
CC.Balance
}).Sum(p => p.Balance)
}) on Main.VDate equals Closing.VVDate into CLO
from CL in CLO.DefaultIfEmpty()
select new
{
Main.VDate,
Main.AccString,
Main.AccName,
Main.SDebit,
Main.SCredit,
Main.SBalance,
Op.OBalance,
CL.CBalance
}).ToList().ToDataTable();
return(DT);
}
