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