private DatatablesResponse <OrganizationReportItem> GetOrganizationReport(DatatablesRequest req, int year, int month, int orgId) { var dt = GetAccountDetailsByOrgID(req, year, month, orgId, out int recordsTotal, out int recordsFiltered); var data = GetOrganizationReportItems(dt); var result = new DatatablesResponse <OrganizationReportItem>() { Data = data, Draw = req.Draw, Error = string.Empty, RecordsFiltered = recordsFiltered, RecordsTotal = recordsTotal }; return(result); }
public static DataTable GetAccountDetailsByOrgID(DatatablesRequest req, int year, int month, int orgId, out int recordsTotal, out int recordsFiltered) { DateTime sDate = new DateTime(year, month, 1); DateTime eDate = sDate.AddMonths(1); var command = DataCommand.Create(CommandType.Text); string columns = @" a.[Name] AS AccountName , RTRIM(LTRIM(a.ShortCode)) AS ShortCode , RIGHT(a.Number, 7) AS ProjectNumber , c.LName + ', ' + c.FName AS DisplayName , co.Phone , co.Email , co.IsManager , co.IsFinManager , fs.FundingSource AS FundingSourceName , tf.TechnicalField AS TechnicalFieldName" ; string select = @" DECLARE @alog_account table (Record int NOT NULL PRIMARY KEY) DECLARE @alog_clientorg table (Record int NOT NULL PRIMARY KEY) DECLARE @alog_client table (Record int NOT NULL PRIMARY KEY) INSERT @alog_account SELECT DISTINCT Record FROM dbo.ActiveLog WHERE TableName = 'Account' AND EnableDate < @eDate AND ((DisableDate IS NULL) OR (DisableDate > @sDate)) INSERT @alog_clientorg SELECT DISTINCT Record FROM dbo.ActiveLog WHERE TableName = 'ClientOrg' AND EnableDate < @eDate AND ((DisableDate IS NULL) OR (DisableDate > @sDate)) INSERT @alog_client SELECT DISTINCT Record FROM dbo.ActiveLog WHERE TableName = 'Client' AND EnableDate < @eDate AND ((DisableDate IS NULL) OR (DisableDate > @sDate)) SELECT {0} FROM dbo.Account a FULL JOIN dbo.ClientAccount ca ON ca.AccountID = a.AccountID FULL JOIN dbo.ClientOrg co ON co.ClientOrgID = ca.ClientOrgID FULL JOIN dbo.Client c ON c.ClientID = co.ClientID LEFT JOIN dbo.FundingSource fs ON fs.FundingSourceID = a.FundingSourceID LEFT JOIN dbo.TechnicalField tf ON tf.TechnicalFieldID = a.TechnicalFieldID" ; string where = @" WHERE a.OrgID = @OrgID AND EXISTS ( SELECT Record FROM @alog_account WHERE a.AccountID = Record ) AND EXISTS ( SELECT Record FROM @alog_clientorg WHERE co.ClientOrgID = Record ) AND EXISTS ( SELECT Record FROM @alog_client WHERE c.ClientID = Record )" ; string sql = string.Format(select, "COUNT(*)") + where; command .Param("sDate", sDate) .Param("eDate", eDate) .Param("OrgID", orgId); recordsTotal = command.ExecuteScalar <int>(sql).Value; if (req.Search != null && !string.IsNullOrEmpty(req.Search.Value)) { command.Param("Search", "%" + req.Search.Value + "%"); where += $@" AND ( a.[Name] LIKE @Search OR RTRIM(LTRIM(a.ShortCode)) LIKE @Search OR RIGHT(a.Number, 7) LIKE @Search OR c.LName + ', ' + c.FName LIKE @Search OR co.Phone LIKE @Search OR co.Email LIKE @Search OR CASE WHEN co.IsManager = 1 THEN 'true' ELSE 'false' END LIKE @Search OR CASE WHEN co.IsFinManager = 1 THEN 'true' ELSE 'false' END LIKE @Search OR fs.FundingSource LIKE @Search OR tf.TechnicalField LIKE @Search )"; sql = string.Format(select, "COUNT(*)") + where; recordsFiltered = command.ExecuteScalar <int>(sql).Value; } else { recordsFiltered = recordsTotal; } string orderBy = " ORDER BY "; string comma = string.Empty; if (req.Order != null) { foreach (var ord in req.Order) { if (req.Columns.Count() > ord.Column) { var col = req.Columns.ElementAt(ord.Column); if (col.Orderable) { orderBy += comma + col.Name + " " + ord.Direction; comma = ", "; } } } } if (orderBy == " ORDER BY ") { orderBy += "AccountName ASC, DisplayName ASC"; } sql = string.Format(select, columns) + where + orderBy; command.Param("Skip", req.Start); sql += " OFFSET @Skip ROWS"; if (req.Length > 0) { command.Param("Take", req.Length); sql += " FETCH NEXT @Take ROWS ONLY"; } return(command.FillDataTable(sql)); }