//public FinancialDisplaySettings GetParameterSettings(FinancialDisplayViewModel vm)
//{
// var SettingList = (List<FinancialDisplaySettings>)System.Web.HttpContext.Current.Session["SettingList"];
// var SettingForReportType = (from H in SettingList where H.ReportType == vm.ReportType select H).FirstOrDefault();
// FinancialDisplaySettings FinancialDisplaySettings = new FinancialDisplaySettings();
// if (SettingForReportType == null)
// {
// FinancialDisplaySettings.ReportType = vm.ReportType;
// FinancialDisplayParameters SiteParameter = new FinancialDisplayParameters();
// SiteParameter.ParameterName = "Site";
// SiteParameter.Value = vm.SiteIds;
// SiteParameter.IsApplicable = true;
// FinancialDisplayParameters DivisionParameter = new FinancialDisplayParameters();
// DivisionParameter.ParameterName = "Division";
// DivisionParameter.Value = vm.DivisionIds;
// DivisionParameter.IsApplicable = true;
// FinancialDisplayParameters FromDateParameter = new FinancialDisplayParameters();
// FromDateParameter.ParameterName = "FromDate";
// FromDateParameter.Value = vm.FromDate;
// FromDateParameter.IsApplicable = true;
// FinancialDisplayParameters ToDateParameter = new FinancialDisplayParameters();
// ToDateParameter.ParameterName = "ToDate";
// ToDateParameter.Value = vm.ToDate;
// ToDateParameter.IsApplicable = true;
// FinancialDisplaySettings.FinancialDisplayParameters = new List<FinancialDisplayParameters>();
// FinancialDisplaySettings.FinancialDisplayParameters.Add(SiteParameter);
// FinancialDisplaySettings.FinancialDisplayParameters.Add(DivisionParameter);
// FinancialDisplaySettings.FinancialDisplayParameters.Add(FromDateParameter);
// FinancialDisplaySettings.FinancialDisplayParameters.Add(ToDateParameter);
// System.Web.HttpContext.Current.Session["CurrentSetting"] = FinancialDisplaySettings;
// //((List<FinancialDisplaySettings>)System.Web.HttpContext.Current.Session["SettingList"]).Add(FinancialDisplaySettings);
// }
// else
// {
// //var SiteSetting = (from H in SettingForReportType.FinancialDisplayParameters where H.ParameterName == "Site" select H).FirstOrDefault();
// //SiteSetting.Value = vm.SiteIds;
// //var DivisionSetting = (from H in SettingForReportType.FinancialDisplayParameters where H.ParameterName == "Division" select H).FirstOrDefault();
// //DivisionSetting.Value = vm.DivisionIds;
// //var FromDateSetting = (from H in SettingForReportType.FinancialDisplayParameters where H.ParameterName == "FromDate" select H).FirstOrDefault();
// //FromDateSetting.Value = vm.FromDate;
// //var ToDateSetting = (from H in SettingForReportType.FinancialDisplayParameters where H.ParameterName == "ToDate" select H).FirstOrDefault();
// //ToDateSetting.Value = vm.ToDate;
// FinancialDisplaySettings = SettingForReportType;
// }
// return FinancialDisplaySettings;
//}
public FinancialDisplaySettings SetCurrentParameterSettings(FinancialDisplayViewModel vm)
{
FinancialDisplaySettings FinancialDisplaySettings = new FinancialDisplaySettings();
FinancialDisplaySettings.ReportType = vm.ReportType;
FinancialDisplayParameters SiteParameter = new FinancialDisplayParameters();
SiteParameter.ParameterName = "Site";
SiteParameter.Value = vm.SiteIds;
SiteParameter.IsApplicable = true;
FinancialDisplayParameters DivisionParameter = new FinancialDisplayParameters();
DivisionParameter.ParameterName = "Division";
DivisionParameter.Value = vm.DivisionIds;
DivisionParameter.IsApplicable = true;
FinancialDisplayParameters FromDateParameter = new FinancialDisplayParameters();
FromDateParameter.ParameterName = "FromDate";
FromDateParameter.Value = vm.FromDate;
FromDateParameter.IsApplicable = true;
FinancialDisplayParameters ToDateParameter = new FinancialDisplayParameters();
ToDateParameter.ParameterName = "ToDate";
ToDateParameter.Value = vm.ToDate;
ToDateParameter.IsApplicable = true;
FinancialDisplayParameters LedgerAccountGroupParameter = new FinancialDisplayParameters();
LedgerAccountGroupParameter.ParameterName = "LedgerAccountGroup";
LedgerAccountGroupParameter.Value = vm.LedgerAccountGroup.ToString();
LedgerAccountGroupParameter.IsApplicable = true;
FinancialDisplayParameters LedgerAccountParameter = new FinancialDisplayParameters();
LedgerAccountParameter.ParameterName = "LedgerAccount";
LedgerAccountParameter.Value = vm.LedgerAccount.ToString();
LedgerAccountParameter.IsApplicable = true;
FinancialDisplaySettings.FinancialDisplayParameters = new List <FinancialDisplayParameters>();
FinancialDisplaySettings.FinancialDisplayParameters.Add(SiteParameter);
FinancialDisplaySettings.FinancialDisplayParameters.Add(DivisionParameter);
FinancialDisplaySettings.FinancialDisplayParameters.Add(FromDateParameter);
FinancialDisplaySettings.FinancialDisplayParameters.Add(ToDateParameter);
FinancialDisplaySettings.FinancialDisplayParameters.Add(LedgerAccountGroupParameter);
FinancialDisplaySettings.FinancialDisplayParameters.Add(LedgerAccountParameter);
System.Web.HttpContext.Current.Session["CurrentSetting"] = FinancialDisplaySettings;
return(FinancialDisplaySettings);
}
public FinancialDisplaySettings SetCurrentParameterSettings(FinancialDisplayViewModel vm)
{
FinancialDisplaySettings FinancialDisplaySettings = new FinancialDisplaySettings();
FinancialDisplaySettings.ReportType = vm.ReportType;
FinancialDisplayParameters SiteParameter = new FinancialDisplayParameters();
SiteParameter.ParameterName = "Site";
SiteParameter.Value = vm.SiteIds;
SiteParameter.IsApplicable = true;
FinancialDisplayParameters DivisionParameter = new FinancialDisplayParameters();
DivisionParameter.ParameterName = "Division";
DivisionParameter.Value = vm.DivisionIds;
DivisionParameter.IsApplicable = true;
FinancialDisplayParameters FromDateParameter = new FinancialDisplayParameters();
FromDateParameter.ParameterName = "FromDate";
FromDateParameter.Value = vm.FromDate;
FromDateParameter.IsApplicable = true;
FinancialDisplayParameters ToDateParameter = new FinancialDisplayParameters();
ToDateParameter.ParameterName = "ToDate";
ToDateParameter.Value = vm.ToDate;
ToDateParameter.IsApplicable = true;
FinancialDisplayParameters CostCenterParameter = new FinancialDisplayParameters();
CostCenterParameter.ParameterName = "CostCenter";
CostCenterParameter.Value = vm.CostCenterIds;
CostCenterParameter.IsApplicable = true;
FinancialDisplayParameters DrCrParameter = new FinancialDisplayParameters();
DrCrParameter.ParameterName = "DrCr";
DrCrParameter.Value = vm.DrCr;
DrCrParameter.IsApplicable = true;
FinancialDisplayParameters IsIncludeZeroBalanceParameter = new FinancialDisplayParameters();
IsIncludeZeroBalanceParameter.ParameterName = "IsIncludeZeroBalance";
IsIncludeZeroBalanceParameter.Value = vm.IsIncludeZeroBalance.ToString();
IsIncludeZeroBalanceParameter.IsApplicable = true;
FinancialDisplayParameters IsShowContraAccountParameter = new FinancialDisplayParameters();
IsShowContraAccountParameter.ParameterName = "IsShowContraAccount";
IsShowContraAccountParameter.Value = vm.IsShowContraAccount.ToString();
IsShowContraAccountParameter.IsApplicable = true;
FinancialDisplayParameters IsIncludeOpeningParameter = new FinancialDisplayParameters();
IsIncludeOpeningParameter.ParameterName = "IsIncludeOpening";
IsIncludeOpeningParameter.Value = vm.IsIncludeOpening.ToString();
IsIncludeOpeningParameter.IsApplicable = true;
FinancialDisplayParameters IsShowDetailParameter = new FinancialDisplayParameters();
IsShowDetailParameter.ParameterName = "IsShowDetail";
IsShowDetailParameter.Value = vm.IsShowDetail.ToString();
IsShowDetailParameter.IsApplicable = true;
FinancialDisplayParameters IsFullHierarchyParameter = new FinancialDisplayParameters();
IsFullHierarchyParameter.ParameterName = "IsFullHierarchy";
IsFullHierarchyParameter.Value = vm.IsFullHierarchy.ToString();
IsFullHierarchyParameter.IsApplicable = true;
FinancialDisplayParameters LedgerAccountGroupParameter = new FinancialDisplayParameters();
LedgerAccountGroupParameter.ParameterName = "LedgerAccountGroup";
LedgerAccountGroupParameter.Value = vm.LedgerAccountGroup == null ? null : vm.LedgerAccountGroup.ToString();
LedgerAccountGroupParameter.IsApplicable = true;
FinancialDisplayParameters LedgerAccountParameter = new FinancialDisplayParameters();
LedgerAccountParameter.ParameterName = "LedgerAccount";
LedgerAccountParameter.Value = vm.LedgerAccount == null ? null : vm.LedgerAccount.ToString();
LedgerAccountParameter.IsApplicable = true;
FinancialDisplaySettings.FinancialDisplayParameters = new List <FinancialDisplayParameters>();
FinancialDisplaySettings.FinancialDisplayParameters.Add(SiteParameter);
FinancialDisplaySettings.FinancialDisplayParameters.Add(DivisionParameter);
FinancialDisplaySettings.FinancialDisplayParameters.Add(FromDateParameter);
FinancialDisplaySettings.FinancialDisplayParameters.Add(ToDateParameter);
FinancialDisplaySettings.FinancialDisplayParameters.Add(CostCenterParameter);
FinancialDisplaySettings.FinancialDisplayParameters.Add(DrCrParameter);
FinancialDisplaySettings.FinancialDisplayParameters.Add(IsIncludeZeroBalanceParameter);
FinancialDisplaySettings.FinancialDisplayParameters.Add(IsShowContraAccountParameter);
FinancialDisplaySettings.FinancialDisplayParameters.Add(IsIncludeOpeningParameter);
FinancialDisplaySettings.FinancialDisplayParameters.Add(IsShowDetailParameter);
FinancialDisplaySettings.FinancialDisplayParameters.Add(IsFullHierarchyParameter);
FinancialDisplaySettings.FinancialDisplayParameters.Add(LedgerAccountGroupParameter);
FinancialDisplaySettings.FinancialDisplayParameters.Add(LedgerAccountParameter);
System.Web.HttpContext.Current.Session["CurrentSetting"] = FinancialDisplaySettings;
return(FinancialDisplaySettings);
}
public JsonResult FinancialDisplayFill(FinancialDisplayViewModel vm)
{
FinancialDisplaySettings SettingParameter = SetCurrentParameterSettings(vm);
if (vm.ReportType == ReportType_TrialBalance)
{
if (vm.DisplayType == DisplayType_Balance)
{
if (vm.IsShowDetail == true)
{
if (vm.IsFullHierarchy == true)
{
IEnumerable <TrialBalanceViewModel> TrialBalanceDetail = _FinancialDisplayService.GetTrialBalanceDetailWithFullHierarchy(SettingParameter);
if (TrialBalanceDetail != null)
{
JsonResult json = Json(new { Success = true, Data = TrialBalanceDetail.ToList() }, JsonRequestBehavior.AllowGet);
json.MaxJsonLength = int.MaxValue;
return(json);
}
}
else
{
IEnumerable <TrialBalanceViewModel> TrialBalanceDetail = _FinancialDisplayService.GetTrialBalanceDetail(SettingParameter);
if (TrialBalanceDetail != null)
{
JsonResult json = Json(new { Success = true, Data = TrialBalanceDetail.ToList() }, JsonRequestBehavior.AllowGet);
json.MaxJsonLength = int.MaxValue;
return(json);
}
}
}
else
{
IEnumerable <TrialBalanceViewModel> TrialBalance = _FinancialDisplayService.GetTrialBalance(SettingParameter);
if (TrialBalance != null)
{
JsonResult json = Json(new { Success = true, Data = TrialBalance.ToList() }, JsonRequestBehavior.AllowGet);
json.MaxJsonLength = int.MaxValue;
return(json);
}
}
}
else if (vm.DisplayType == DisplayType_Summary)
{
if (vm.IsShowDetail == true)
{
if (vm.IsFullHierarchy == true)
{
IEnumerable <TrialBalanceSummaryViewModel> TrialBalanceSummary = _FinancialDisplayService.GetTrialBalanceDetailSummaryWithFullHierarchy(SettingParameter);
if (TrialBalanceSummary != null)
{
JsonResult json = Json(new { Success = true, Data = TrialBalanceSummary.ToList() }, JsonRequestBehavior.AllowGet);
json.MaxJsonLength = int.MaxValue;
return(json);
}
}
else
{
IEnumerable <TrialBalanceSummaryViewModel> TrialBalanceSummary = _FinancialDisplayService.GetTrialBalanceDetailSummary(SettingParameter);
if (TrialBalanceSummary != null)
{
JsonResult json = Json(new { Success = true, Data = TrialBalanceSummary.ToList() }, JsonRequestBehavior.AllowGet);
json.MaxJsonLength = int.MaxValue;
return(json);
}
}
}
else
{
IEnumerable <TrialBalanceSummaryViewModel> TrialBalanceSummary = _FinancialDisplayService.GetTrialBalanceSummary(SettingParameter);
if (TrialBalanceSummary != null)
{
JsonResult json = Json(new { Success = true, Data = TrialBalanceSummary.ToList() }, JsonRequestBehavior.AllowGet);
json.MaxJsonLength = int.MaxValue;
return(json);
}
}
}
}
else if (vm.ReportType == ReportType_SubTrialBalance)
{
if (vm.DisplayType == DisplayType_Balance)
{
IEnumerable <SubTrialBalanceViewModel> SubTrialBalance = _FinancialDisplayService.GetSubTrialBalance(SettingParameter);
if (SubTrialBalance != null)
{
JsonResult json = Json(new { Success = true, Data = SubTrialBalance.ToList() }, JsonRequestBehavior.AllowGet);
json.MaxJsonLength = int.MaxValue;
return(json);
}
}
else if (vm.DisplayType == DisplayType_Summary)
{
IEnumerable <SubTrialBalanceSummaryViewModel> SubTrialBalanceSummary = _FinancialDisplayService.GetSubTrialBalanceSummary(SettingParameter);
if (SubTrialBalanceSummary != null)
{
JsonResult json = Json(new { Success = true, Data = SubTrialBalanceSummary.ToList() }, JsonRequestBehavior.AllowGet);
json.MaxJsonLength = int.MaxValue;
return(json);
}
}
}
else if (vm.ReportType == ReportType_Ledger)
{
if (vm.LedgerAccount != null)
{
IEnumerable <LedgerBalanceViewModel> LedgerBalance = _FinancialDisplayService.GetLedgerBalance(SettingParameter);
if (LedgerBalance != null)
{
JsonResult json = Json(new { Success = true, Data = LedgerBalance.ToList() }, JsonRequestBehavior.AllowGet);
json.MaxJsonLength = int.MaxValue;
return(json);
}
}
}
return(Json(new { Success = true }, JsonRequestBehavior.AllowGet));
}
public List <TrialBalanceAsPerDetailViewModel> GetTrialBalanceAsPerDetail(FinancialDisplaySettings Settings)
{
var SiteSetting = (from H in Settings.FinancialDisplayParameters where H.ParameterName == "Site" select H).FirstOrDefault();
var DivisionSetting = (from H in Settings.FinancialDisplayParameters where H.ParameterName == "Division" select H).FirstOrDefault();
var FromDateSetting = (from H in Settings.FinancialDisplayParameters where H.ParameterName == "FromDate" select H).FirstOrDefault();
var ToDateSetting = (from H in Settings.FinancialDisplayParameters where H.ParameterName == "ToDate" select H).FirstOrDefault();
var LedgerAccountGroupSetting = (from H in Settings.FinancialDisplayParameters where H.ParameterName == "LedgerAccountGroup" select H).FirstOrDefault();
string SiteId = SiteSetting.Value;
string DivisionId = DivisionSetting.Value;
string FromDate = FromDateSetting.Value;
string ToDate = ToDateSetting.Value;
string LedgerAccountGroup = LedgerAccountGroupSetting.Value;
SqlParameter SqlParameterSiteId = new SqlParameter("@Site", !string.IsNullOrEmpty(SiteId) ? SiteId : (object)DBNull.Value);
SqlParameter SqlParameterDivisionId = new SqlParameter("@Division", !string.IsNullOrEmpty(DivisionId) ? DivisionId : (object)DBNull.Value);
SqlParameter SqlParameterFromDate = new SqlParameter("@FromDate", FromDate);
SqlParameter SqlParameterToDate = new SqlParameter("@ToDate", ToDate);
SqlParameter SqlParameterLedgerAccountGroup = new SqlParameter("@LedgerAccountGroup", LedgerAccountGroup);
string mCondStr = "";
if (SiteId != null)
{
mCondStr = mCondStr + " AND LH.SiteId IN (SELECT Items FROM [dbo].[Split] (@Site, ','))";
}
if (DivisionId != null)
{
mCondStr = mCondStr + " AND LH.SiteId IN (SELECT Items FROM [dbo].[Split] (@Division, ','))";
}
if (FromDate != null)
{
mCondStr = mCondStr + " AND LH.DocDate >= @FromDate";
}
if (ToDate != null)
{
mCondStr = mCondStr + " AND LH.DocDate <= @ToDate";
}
if (LedgerAccountGroup != null && LedgerAccountGroup != "")
{
mCondStr = mCondStr + " AND LAG.LedgerAccountGroupId = @LedgerAccountGroup";
}
string mBalanceCondStr = "";
//if (ShowZeroBalance != null) mCondStr = "HAVING sum(isnull(H.AmtDr,0)) <> sum(isnull(H.AmtCr,0))";
string mQry = @"WITH CTE_LedgerBalance AS
(
SELECT LAG.LedgerAccountGroupId, max(LAG.LedgerAccountGroupName) AS LedgerAccountGroupName, Max(Lag.ParentLedgerAccountGroupId) AS ParentLedgerAccountGroupId,
Sum(isnull(H.AmtDr,0)) - sum(isnull(H.AmtCr,0)) AS Balance,
'Trial Balance' AS ReportType
FROM web.LedgerHeaders LH WITH (Nolock)
LEFT JOIN web.Ledgers H WITH (Nolock) ON LH.LedgerHeaderId = H.LedgerHeaderId
LEFT JOIN web.LedgerAccounts LA WITH (Nolock) ON LA.LedgerAccountId = H.LedgerAccountId
LEFT JOIN web.LedgerAccountGroups LAG WITH (Nolock) ON LAG.LedgerAccountGroupId = LA.LedgerAccountGroupId
WHERE LAG.LedgerAccountGroupId IS NOT NULL AND LH.DocDate >= '01/Apr/2017'
AND LH.DocDate <= '30/Sep/2017'
--AND LAG.LedgerAccountGroupName LIKE '%Sundry Debtors%'
GROUP BY LAG.LedgerAccountGroupId
),--SELECT CASE WHEN balance>0 THEN balance END AS dr FROM cte_LedgerBalance,
CTE_LedgerAccountGroup AS
(
SELECT L.LedgerAccountGroupId AS BaseLedgerAccountGroupId, L.LedgerAccountGroupId, L.LedgerAccountGroupName, L.ParentLedgerAccountGroupId AS ParentLedgerAccountGroupId, 0 AS [level]
FROM CTE_LedgerBalance L
UNION ALL
SELECT H.BaseLedgerAccountGroupId, L.LedgerAccountGroupId, L.LedgerAccountGroupName, L.ParentLedgerAccountGroupId, H.level + 1
FROM CTE_LedgerAccountGroup H
INNER JOIN CTE_LedgerBalance L ON H.ParentLedgerAccountGroupId = L.LedgerAccountGroupId
),
CTE_LedgerBalanceTotals AS
(
SELECT IsNull(Sum(VTotals.AmtDr),0) AS TotalAmtDr, IsNull(Sum(VTotals.AmtCr),0) AS TotalAmtCr
FROM (
SELECT L.LedgerAccountGroupId, Max(L.LedgerAccountGroupName) AS LedgerAccountGroupName, Max(L.ParentLedgerAccountGroupId) AS ParentLedgerAccountGroupId,
CASE WHEN Sum(isnull(Lb.Balance,0)) > 0 THEN Sum(isnull(Lb.Balance,0)) ELSE NULL END AS AmtDr,
CASE WHEN Sum(isnull(Lb.Balance,0)) < 0 THEN abs(Sum(isnull(Lb.Balance,0))) ELSE NULL END AS AmtCr
FROM CTE_LedgerAccountGroup L
LEFT JOIN CTE_LedgerBalance Lb ON L.BaseLedgerAccountGroupId = Lb.LedgerAccountGroupId
WHERE L.ParentLedgerAccountGroupId IS NULL
GROUP BY L.LedgerAccountGroupId
) AS VTotals
)
SELECT L.LedgerAccountGroupId, Max(L.LedgerAccountGroupName) AS LedgerAccountGroupName, Max(L.ParentLedgerAccountGroupId) AS ParentLedgerAccountGroupId,
CASE WHEN Sum(isnull(Lb.Balance,0)) > 0 THEN Sum(isnull(Lb.Balance,0)) ELSE NULL END AS AmtDr,
CASE WHEN Sum(isnull(Lb.Balance,0)) < 0 THEN abs(Sum(isnull(Lb.Balance,0))) ELSE NULL END AS AmtCr,
Max(IsNull(Lbt.TotalAmtDr,0)) AS TotalAmtDr, Max(IsNull(Lbt.TotalAmtCr,0)) AS TotalAmtCr
FROM CTE_LedgerAccountGroup L
LEFT JOIN CTE_LedgerBalance Lb ON L.BaseLedgerAccountGroupId = Lb.LedgerAccountGroupId
LEFT JOIN CTE_LedgerBalanceTotals Lbt ON 1=1
GROUP BY L.LedgerAccountGroupId
UNION ALL
SELECT LA.LedgerAccountId AS LedgerAccountGroupId, max(LA.LedgerAccountName) AS LedgerAccountGroupName,
Max(LA.LedgerAccountGroupId) AS ParentLedgerAccountGroupId,
CASE WHEN sum(isnull(H.AmtDr,0)) - sum(isnull(H.AmtCr,0)) > 0 THEN sum(isnull(H.AmtDr,0)) - sum(isnull(H.AmtCr,0)) ELSE NULL END AS AmtDr,
CASE WHEN sum(isnull(H.AmtDr,0)) - sum(isnull(H.AmtCr,0)) < 0 THEN abs(sum(isnull(H.AmtDr,0)) - sum(isnull(H.AmtCr,0))) ELSE NULL END AS AmtCr,
Max(IsNull(Lbt.TotalAmtDr,0)) AS TotalAmtDr, Max(IsNull(Lbt.TotalAmtCr,0)) AS TotalAmtCr
FROM web.LedgerHeaders LH WITH (Nolock)
LEFT JOIN web.Ledgers H WITH (Nolock) ON LH.LedgerHeaderId = H.LedgerHeaderId
LEFT JOIN web.LedgerAccounts LA WITH (Nolock) ON LA.LedgerAccountId = H.LedgerAccountId
LEFT JOIN CTE_LedgerBalanceTotals Lbt ON 1=1
LEFT JOIN (
SELECT Ag.ParentLedgerAccountGroupId
FROM Web.LedgerAccountGroups Ag
WHERE Ag.ParentLedgerAccountGroupId IS NOT NULL
GROUP BY Ag.ParentLedgerAccountGroupId
) AS V1 ON La.LedgerAccountGroupId = V1.ParentLedgerAccountGroupId
WHERE LH.DocDate >= '01/Apr/2017'
AND LH.DocDate <= '30/Sep/2017'
AND V1.ParentLedgerAccountGroupId IS NOT NULL
GROUP BY LA.LedgerAccountId ";
//OR Ag.LedgerAccountGroupName = 'Sundry Debtors (Group)' OR Pag.LedgerAccountGroupName = 'Sundry Debtors (Group)')
IEnumerable <TrialBalanceAsPerDetailViewModel> TrialBalanceList = db.Database.SqlQuery <TrialBalanceAsPerDetailViewModel>(mQry, SqlParameterSiteId, SqlParameterDivisionId, SqlParameterFromDate, SqlParameterToDate, SqlParameterLedgerAccountGroup).ToList();
List <TrialBalanceAsPerDetailViewModel> temp = GenerateTreeStructureForList(TrialBalanceList.ToList());
//List<TrialBalanceAsPerDetailViewModel> temp1 = temp.Where(i => i.ChildTrialBalanceAsPerDetailViewModel != null && i.ParentLedgerAccountGroupId == null).ToList();
return(temp);
}
public IEnumerable <TrialBalanceViewModel> GetTrialBalance(FinancialDisplaySettings Settings)
{
var SiteSetting = (from H in Settings.FinancialDisplayParameters where H.ParameterName == "Site" select H).FirstOrDefault();
var DivisionSetting = (from H in Settings.FinancialDisplayParameters where H.ParameterName == "Division" select H).FirstOrDefault();
var FromDateSetting = (from H in Settings.FinancialDisplayParameters where H.ParameterName == "FromDate" select H).FirstOrDefault();
var ToDateSetting = (from H in Settings.FinancialDisplayParameters where H.ParameterName == "ToDate" select H).FirstOrDefault();
var LedgerAccountGroupSetting = (from H in Settings.FinancialDisplayParameters where H.ParameterName == "LedgerAccountGroup" select H).FirstOrDefault();
string SiteId = SiteSetting.Value;
string DivisionId = DivisionSetting.Value;
string FromDate = FromDateSetting.Value;
string ToDate = ToDateSetting.Value;
string LedgerAccountGroup = LedgerAccountGroupSetting.Value;
SqlParameter SqlParameterSiteId = new SqlParameter("@Site", !string.IsNullOrEmpty(SiteId) ? SiteId : (object)DBNull.Value);
SqlParameter SqlParameterDivisionId = new SqlParameter("@Division", !string.IsNullOrEmpty(DivisionId) ? DivisionId : (object)DBNull.Value);
SqlParameter SqlParameterFromDate = new SqlParameter("@FromDate", FromDate);
SqlParameter SqlParameterToDate = new SqlParameter("@ToDate", ToDate);
SqlParameter SqlParameterLedgerAccountGroup = new SqlParameter("@LedgerAccountGroup", LedgerAccountGroup);
string mCondStr = "";
if (SiteId != null)
{
mCondStr = mCondStr + " AND LH.SiteId IN (SELECT Items FROM [dbo].[Split] (@Site, ','))";
}
if (DivisionId != null)
{
mCondStr = mCondStr + " AND LH.SiteId IN (SELECT Items FROM [dbo].[Split] (@Division, ','))";
}
if (FromDate != null)
{
mCondStr = mCondStr + " AND LH.DocDate >= @FromDate";
}
if (ToDate != null)
{
mCondStr = mCondStr + " AND LH.DocDate <= @ToDate";
}
if (LedgerAccountGroup != null && LedgerAccountGroup != "")
{
mCondStr = mCondStr + " AND LAG.LedgerAccountGroupId = @LedgerAccountGroup";
}
string mBalanceCondStr = "";
//if (ShowZeroBalance != null) mCondStr = "HAVING sum(isnull(H.AmtDr,0)) <> sum(isnull(H.AmtCr,0))";
string mQry = @"SELECT LAG.LedgerAccountGroupId, max(LAG.LedgerAccountGroupName) AS LedgerAccountGroupName,
CASE WHEN sum(isnull(H.AmtDr,0)) - sum(isnull(H.AmtCr,0)) > 0 THEN sum(isnull(H.AmtDr,0)) - sum(isnull(H.AmtCr,0)) ELSE NULL END AS AmtDr,
CASE WHEN sum(isnull(H.AmtDr,0)) - sum(isnull(H.AmtCr,0)) < 0 THEN abs(sum(isnull(H.AmtDr,0)) - sum(isnull(H.AmtCr,0))) ELSE NULL END AS AmtCr,
'Trial Balance' AS ReportType
FROM web.LedgerHeaders LH WITH (Nolock)
LEFT JOIN web.Ledgers H WITH (Nolock) ON LH.LedgerHeaderId = H.LedgerHeaderId
LEFT JOIN web.LedgerAccounts LA WITH (Nolock) ON LA.LedgerAccountId = H.LedgerAccountId
LEFT JOIN web.LedgerAccountGroups LAG WITH (Nolock) ON LAG.LedgerAccountGroupId = LA.LedgerAccountGroupId
WHERE LAG.LedgerAccountGroupId IS NOT NULL " + mCondStr +
@" GROUP BY LAG.LedgerAccountGroupId " +
mBalanceCondStr +
"Order By LedgerAccountGroupName";
IEnumerable <TrialBalanceViewModel> TrialBalanceList;
TrialBalanceList = db.Database.SqlQuery <TrialBalanceViewModel>(mQry, SqlParameterSiteId, SqlParameterDivisionId, SqlParameterFromDate, SqlParameterToDate, SqlParameterLedgerAccountGroup).ToList();
return(TrialBalanceList);
}
public IEnumerable <LedgerBalanceViewModel> GetLedgerBalance(FinancialDisplaySettings Settings)
{
var SiteSetting = (from H in Settings.FinancialDisplayParameters where H.ParameterName == "Site" select H).FirstOrDefault();
var DivisionSetting = (from H in Settings.FinancialDisplayParameters where H.ParameterName == "Division" select H).FirstOrDefault();
var FromDateSetting = (from H in Settings.FinancialDisplayParameters where H.ParameterName == "FromDate" select H).FirstOrDefault();
var ToDateSetting = (from H in Settings.FinancialDisplayParameters where H.ParameterName == "ToDate" select H).FirstOrDefault();
var LedgerAccountSetting = (from H in Settings.FinancialDisplayParameters where H.ParameterName == "LedgerAccount" select H).FirstOrDefault();
string SiteId = SiteSetting.Value;
string DivisionId = DivisionSetting.Value;
string FromDate = FromDateSetting.Value;
string ToDate = ToDateSetting.Value;
string LedgerAccount = LedgerAccountSetting.Value;
SqlParameter SqlParameterSiteId = new SqlParameter("@Site", !string.IsNullOrEmpty(SiteId) ? SiteId : (object)DBNull.Value);
SqlParameter SqlParameterDivisionId = new SqlParameter("@Division", !string.IsNullOrEmpty(DivisionId) ? DivisionId : (object)DBNull.Value);
SqlParameter SqlParameterFromDate = new SqlParameter("@FromDate", FromDate);
SqlParameter SqlParameterToDate = new SqlParameter("@ToDate", ToDate);
SqlParameter SqlParameterLedgerAccount = new SqlParameter("@LedgerAccount", LedgerAccount);
string mCondStr = "";
if (SiteId != null)
{
mCondStr = mCondStr + " AND LH.SiteId IN (SELECT Items FROM [dbo].[Split] (@Site, ','))";
}
if (DivisionId != null)
{
mCondStr = mCondStr + " AND LH.SiteId IN (SELECT Items FROM [dbo].[Split] (@Division, ','))";
}
if (LedgerAccount != null && LedgerAccount != "")
{
mCondStr = mCondStr + " AND LA.LedgerAccountId = @LedgerAccount";
}
string mOpeningDateCondStr = "";
if (FromDate != null)
{
mOpeningDateCondStr = mOpeningDateCondStr + " AND LH.DocDate < @FromDate ";
}
string mDateCondStr = "";
if (FromDate != null)
{
mDateCondStr = mDateCondStr + " AND LH.DocDate >= @FromDate ";
}
if (ToDate != null)
{
mDateCondStr = mDateCondStr + " AND LH.DocDate <= @ToDate ";
}
int ShowContraAccount = 1;
string mQry = @"SELECT VMain.LedgerAccountId, VMain.LedgerHeaderId, VMain.DocHeaderId, VMain.DocTypeId, VMain.LedgerAccountName,VMain.PersonId, VMain.ContraLedgerAccountName,
D.DivisionShortCode + S.SiteShortCode + '-' + VMain.DocumentTypeShortName + '-' + VMain.DocNo As DocNo, VMain.DocumentTypeShortName, REPLACE(CONVERT(VARCHAR(11),VMain.DocDate,106), ' ','/') AS DocDate, IsNull(VMain.Narration,'') As Narration, VMain.LedgerId,
CASE WHEN VMain.AmtDr = 0 THEN NULL ELSE VMain.AmtDr END AS AmtDr, CASE WHEN VMain.AmtCr = 0 THEN NULL ELSE VMain.AmtCr END AS AmtCr,
abs(sum(isnull(VMain.AmtDr,0)) OVER( PARTITION BY VMain.LedgerAccountId ORDER BY VMain.DocDate, VMain.DocTypeId, VMain.DocNo, VMain.LedgerId ) - sum(isnull(VMain.AmtCr,0)) OVER( PARTITION BY VMain.LedgerAccountId ORDER BY VMain.DocDate, VMain.DocTypeId, VMain.DocNo ,VMain.LedgerId )) AS Balance,
CASE WHEN sum(isnull(VMain.AmtDr,0)) OVER( ORDER BY VMain.DocDate, VMain.DocTypeId, VMain.DocNo ,VMain.LedgerId ) - sum(isnull(VMain.AmtCr,0)) OVER( PARTITION BY VMain.LedgerAccountId ORDER BY VMain.DocDate, VMain.DocTypeId, VMain.DocNo ,VMain.LedgerId ) >= 0 THEN 'Dr' ELSE 'Cr' END AS BalanceType ,
'Ledgers' AS ReportType,
VMain.LedgerAccountName AS LedgerAccountText,
S.SiteName AS SiteText,D.DivisionName AS DivisionText,VMain.CostCenterName AS CostCenterName
FROM
(
SELECT Max(LH.SiteId) AS SiteId, Max(LH.DivisionId) AS DivisionId, H.LedgerAccountId, 0 AS LedgerHeaderId, 0 AS DocHeaderId, 0 AS DocTypeId, Max(LA.LedgerAccountName) AS LedgerAccountName,max(LA.PersonId) AS PersonId, 'Opening' AS ContraLedgerAccountName, 'Opening' AS DocNo, 'Opening' AS DocumentTypeShortName, @FromDate AS DocDate, 'Opening' AS Narration,
'Opening' AS Narration1, 'Opening' AS Narration2, 0 AS LedgerId,
CASE WHEN sum(isnull(H.AmtDr,0)) - sum(isnull(H.AmtCr,0)) > 0 THEN sum(isnull(H.AmtDr,0)) - sum(isnull(H.AmtCr,0)) ELSE 0 END AS AmtDr,
CASE WHEN sum(isnull(H.AmtDr,0)) - sum(isnull(H.AmtCr,0)) < 0 THEN abs(sum(isnull(H.AmtDr,0)) - sum(isnull(H.AmtCr,0))) ELSE 0 END AS AmtCr,
NULL AS DomainName, NULL AS ControllerActionId ,'Opening' AS CostCenterName
FROM web.LedgerHeaders LH WITH (Nolock)
LEFT JOIN web.Ledgers H WITH (Nolock) ON LH.LedgerHeaderId = H.LedgerHeaderId
LEFT JOIN web.LedgerAccounts LA WITH (Nolock) ON LA.LedgerAccountId = H.LedgerAccountId
WHERE H.LedgerAccountId IS NOT NULL " + mCondStr + mOpeningDateCondStr +
@" GROUP BY H.LedgerAccountId
UNION ALL
SELECT LH.SiteId, LH.DivisionId, H.LedgerAccountId, H.LedgerHeaderId, IsNull(LH.DocHeaderId,H.LedgerHeaderId) AS DocHeaderId, LH.DocTypeId, LA.LedgerAccountName,LA.PersonId, CLA.LedgerAccountName AS ContraLedgerAccountName, LH.DocNo, DT.DocumentTypeShortName, LH.DocDate AS DocDate,
CASE When " + ShowContraAccount + @" <> 0 And CLA.LedgerAccountName Is Not Null
Then '<Strong>' + CLA.LedgerAccountName + '</Strong>' + '</br>' +
CASE When Lh.PartyDocNo Is Not Null THen 'Party Doc No : ' + LH.PartyDocNo + ', ' Else '' End +
CASE When Lh.PartyDocDate Is Not Null THen 'Party Doc Date : ' + REPLACE(CONVERT(VARCHAR(11),LH.PartyDocDate,106), ' ','/') + '</br>' Else '' End +
CASE When H.ChqNo Is Not Null THen 'Chq No.: ' + H.ChqNo + ', ' Else '' End +
CASE When H.ChqDate Is Not Null THen 'Chq Date: ' + REPLACE(CONVERT(VARCHAR(11),H.ChqDate,106), ' ','/') + '</br>' Else '' End +
' (' + IsNull(LH.Narration,'') + ')'
Else
CASE When Lh.PartyDocNo Is Not Null THen 'Party Doc No : ' + LH.PartyDocNo + ', ' Else '' End +
CASE When Lh.PartyDocDate Is Not Null THen 'Party Doc Date : ' + REPLACE(CONVERT(VARCHAR(11),LH.PartyDocDate,106), ' ','/') + '</br>' Else '' End +
CASE When H.ChqNo Is Not Null THen 'Chq No.: ' + H.ChqNo + ', ' Else '' End +
CASE When H.ChqDate Is Not Null THen 'Chq Date: ' + REPLACE(CONVERT(VARCHAR(11),H.ChqDate,106), ' ','/') + '</br>' Else '' End +
' (' + IsNull(LH.Narration,'') + ')'
End As Narration,
CLA.LedgerAccountName AS Narration1, H.Narration AS Narration2,
H.LedgerId, H.AmtDr, H.AmtCr, DT.DomainName, DT.ControllerActionId ,C.CostCenterName
FROM web.Ledgers H WITH (Nolock)
LEFT JOIN web.LedgerHeaders LH WITH (Nolock) ON LH.LedgerHeaderId = H.LedgerHeaderId
LEFT JOIN web.DocumentTypes DT WITH (Nolock) ON DT.DocumentTypeId = LH.DocTypeId
LEFT JOIN web.LedgerAccounts LA WITH (Nolock) ON LA.LedgerAccountId = H.LedgerAccountId
LEFT JOIN web.LedgerAccounts CLA WITH (Nolock) ON CLA.LedgerAccountId = H.ContraLedgerAccountId
LEFT JOIN Web.CostCenters C WITH (Nolock) ON C.CostCenterId=H.CostCenterId
WHERE LA.LedgerAccountId IS NOT NULL " + mCondStr + mDateCondStr +
@" ) VMain
LEFT JOIN Web.Sites S ON S.SiteId = VMain.SiteId
LEFT JOIN Web.Divisions D ON D.DivisionId = VMain.DivisionId ";
IEnumerable <LedgerBalanceViewModel> LedgerBalanceList = db.Database.SqlQuery <LedgerBalanceViewModel>(mQry, SqlParameterSiteId, SqlParameterDivisionId, SqlParameterFromDate, SqlParameterToDate, SqlParameterLedgerAccount).ToList();
return(LedgerBalanceList);
}
public IEnumerable <SubTrialBalanceSummaryViewModel> GetSubTrialBalanceSummary(FinancialDisplaySettings Settings)
{
var SiteSetting = (from H in Settings.FinancialDisplayParameters where H.ParameterName == "Site" select H).FirstOrDefault();
var DivisionSetting = (from H in Settings.FinancialDisplayParameters where H.ParameterName == "Division" select H).FirstOrDefault();
var FromDateSetting = (from H in Settings.FinancialDisplayParameters where H.ParameterName == "FromDate" select H).FirstOrDefault();
var ToDateSetting = (from H in Settings.FinancialDisplayParameters where H.ParameterName == "ToDate" select H).FirstOrDefault();
var LedgerAccountGroupSetting = (from H in Settings.FinancialDisplayParameters where H.ParameterName == "LedgerAccountGroup" select H).FirstOrDefault();
string SiteId = SiteSetting.Value;
string DivisionId = DivisionSetting.Value;
string FromDate = FromDateSetting.Value;
string ToDate = ToDateSetting.Value;
string LedgerAccountGroup = LedgerAccountGroupSetting.Value;
SqlParameter SqlParameterSiteId = new SqlParameter("@Site", !string.IsNullOrEmpty(SiteId) ? SiteId : (object)DBNull.Value);
SqlParameter SqlParameterDivisionId = new SqlParameter("@Division", !string.IsNullOrEmpty(DivisionId) ? DivisionId : (object)DBNull.Value);
SqlParameter SqlParameterFromDate = new SqlParameter("@FromDate", FromDate);
SqlParameter SqlParameterToDate = new SqlParameter("@ToDate", ToDate);
SqlParameter SqlParameterLedgerAccountGroup = new SqlParameter("@LedgerAccountGroup", LedgerAccountGroup);
string mCondStr = "";
if (SiteId != null)
{
mCondStr = mCondStr + " AND LH.SiteId IN (SELECT Items FROM [dbo].[Split] (@Site, ','))";
}
if (DivisionId != null)
{
mCondStr = mCondStr + " AND LH.SiteId IN (SELECT Items FROM [dbo].[Split] (@Division, ','))";
}
if (LedgerAccountGroup != null && LedgerAccountGroup != "")
{
mCondStr = mCondStr + " AND LAG.LedgerAccountGroupId = @LedgerAccountGroup";
}
string mOpeningDateCondStr = "";
if (FromDate != null)
{
mOpeningDateCondStr = mOpeningDateCondStr + " AND LH.DocDate < @FromDate ";
}
string mDateCondStr = "";
if (FromDate != null)
{
mDateCondStr = mDateCondStr + " AND LH.DocDate >= @FromDate ";
}
if (ToDate != null)
{
mDateCondStr = mDateCondStr + " AND LH.DocDate <= @ToDate ";
}
string mQry = @"SELECT VMain.LedgerAccountId, max(LA.LedgerAccountName + ',' + LA.LedgerAccountSuffix ) AS LedgerAccountName, max(LAG.LedgerAccountGroupName) AS LedgerAccountGroupName,
CASE WHEN abs(Sum(Isnull(VMain.Opening,0))) = 0 THEN NULL ELSE abs(Sum(Isnull(VMain.Opening,0))) END AS Opening,
CASE WHEN Sum(Isnull(VMain.Opening,0)) = 0 THEN NULL ELSE Sum(Isnull(VMain.Opening,0)) END AS OpeningValue,
CASE WHEN Sum(Isnull(VMain.Opening,0)) >= 0 THEN 'Dr' ELSE 'Cr' END AS OpeningType,
CASE WHEN Sum(isnull(Vmain.AmtDr,0)) = 0 THEN NULL ELSE Sum(isnull(Vmain.AmtDr,0)) END AS AmtDr, CASE WHEN sum(isnull(VMain.AmtCr,0)) = 0 THEN NULL ELSE sum(isnull(VMain.AmtCr,0)) END AS AmtCr,
abs(Sum(Isnull(VMain.Opening,0)) + Sum(isnull(Vmain.AmtDr,0)) - sum(isnull(VMain.AmtCr,0))) AS Balance,
Sum(Isnull(VMain.Opening,0)) + Sum(isnull(Vmain.AmtDr,0)) - sum(isnull(VMain.AmtCr,0)) AS BalanceValue,
CASE WHEN ( Sum(Isnull(VMain.Opening,0)) + Sum(isnull(Vmain.AmtDr,0)) - sum(isnull(VMain.AmtCr,0))) >= 0 THEN 'Dr' ELSE 'Cr' END AS BalanceType,
'Sub Trial Balance' AS ReportType
FROM
(
SELECT H.LedgerAccountId , sum(isnull(H.AmtDr,0)) - sum(isnull(H.AmtCr,0)) AS Opening,
0 AS AmtDr,0 AS AmtCr
FROM web.LedgerHeaders LH WITH (Nolock)
LEFT JOIN web.Ledgers H WITH (Nolock) ON LH.LedgerHeaderId = H.LedgerHeaderId
LEFT JOIN web.LedgerAccounts LA WITH (Nolock) ON LA.LedgerAccountId = H.LedgerAccountId
LEFT JOIN web.LedgerAccountGroups LAG WITH (Nolock) ON LAG.LedgerAccountGroupId = LA.LedgerAccountGroupId
WHERE H.LedgerAccountId IS NOT NULL " + mCondStr + mOpeningDateCondStr +
@" GROUP BY H.LedgerAccountId
HAVING sum(isnull(H.AmtDr,0)) - sum(isnull(H.AmtCr,0)) <> 0
UNION ALL
SELECT H.LedgerAccountId, 0 AS Opening,
sum(isnull(H.AmtDr,0)) AS AmtDr,sum(isnull(H.AmtCr,0)) AS AmtCr
FROM web.LedgerHeaders LH WITH (Nolock)
LEFT JOIN web.Ledgers H WITH (Nolock) ON LH.LedgerHeaderId = H.LedgerHeaderId
LEFT JOIN web.LedgerAccounts LA WITH (Nolock) ON LA.LedgerAccountId = H.LedgerAccountId
LEFT JOIN web.LedgerAccountGroups LAG WITH (Nolock) ON LAG.LedgerAccountGroupId = LA.LedgerAccountGroupId
WHERE H.LedgerAccountId IS NOT NULL " + mCondStr + mDateCondStr +
@" GROUP BY H.LedgerAccountId
) AS VMain
LEFT JOIN web.LedgerAccounts LA WITH (Nolock) ON LA.LedgerAccountId = VMain.LedgerAccountId
LEFT JOIN web.LedgerAccountGroups LAG WITH (Nolock) ON LAG.LedgerAccountGroupId = LA.LedgerAccountGroupId
Where 1=1
GROUP BY VMain.LedgerAccountId
Order By max(LA.LedgerAccountName + ',' + LA.LedgerAccountSuffix) ";
IEnumerable <SubTrialBalanceSummaryViewModel> SubTrialBalanceSummaryList = db.Database.SqlQuery <SubTrialBalanceSummaryViewModel>(mQry, SqlParameterSiteId, SqlParameterDivisionId, SqlParameterFromDate, SqlParameterToDate, SqlParameterLedgerAccountGroup).ToList();
return(SubTrialBalanceSummaryList);
}
