/// <summary>
/// optimization. if iterating over ienumerable multiple times call
/// tolist first. Move the filter to within context to cut down array size
/// </summary>
/// <param name="parentUUID"></param>
/// <param name="filter"></param>
/// <returns></returns>
public IEnumerable <dynamic> GetSubEvents(string parentUUID, DateTime endDate, ref DataFilter filter)
{
try
{
if (filter.IncludePrivate)
{ //if looking at private then check permission
if (!this.DataAccessAuthorized("EVENT", false, filter.IncludePrivate))
{
filter.IncludePrivate = false;
// if not allowed private then check if they can see anything..
if (!this.DataAccessAuthorized("EVENT", false, filter.IncludePrivate))
{
return(new List <dynamic>());
}
}
}
using (var context = new GreenWerxDbContext(this._connectionKey))
{
DynamicParameters parameters = new DynamicParameters();// see function GetTableData
parameters.Add("@PARENTUUID", parentUUID);
parameters.Add("@PRIVATE", filter.IncludePrivate);
parameters.Add("@DELETED", filter.IncludeDeleted);
parameters.Add("@ENDDATE", endDate);
string sql = @"SELECT COUNT(*) FROM Events e
WHERE
(e.UUID = @PARENTUUID OR e.UUParentID = @PARENTUUID ) AND
(e.Private = 0 OR e.Private = @PRIVATE) AND
(e.Deleted = 0 OR e.Deleted = @DELETED) AND
(e.EndDate > @ENDDATE)" ;
string where = DatasetManager.BuildWhereClause(filter.Screens);
if (!string.IsNullOrWhiteSpace(where))
{
sql += " AND " + where;
var screenParams = DatasetManager.GetParameters(filter.Screens);
if (screenParams != null)
{
parameters.AddDynamicParams(screenParams);
}
}
filter.TotalRecordCount = (int)context.ExecuteScalar(sql, parameters);
if (filter.TotalRecordCount == 0)
{
return(new List <dynamic>());
}
parameters.Add("@CLIENTLAT", filter.Latitude);
parameters.Add("@CLIENTLON", filter.Longitude);
parameters.Add("@MEASURE", 3956.55); // 3956.55 = miles
//
sql = @"SELECT CEILING(dbo.CalcDistance(@CLIENTLAT, @CLIENTLON , el.Latitude, el.Longitude, @MEASURE ) ) as Distance
,a.Name AS HostName
,el.Name AS Location
,el.City
,el.State
,el.Country
,e.[Name] ,e.[Category] ,e.[EventDateTime],e.[RepeatCount]
,e.[RepeatForever] ,e.[Frequency] ,e.[StartDate] ,e.[EndDate]
,e.[Url] ,e.[HostAccountUUID] ,e.[GUUID] ,e.[GuuidType]
,e.[UUID] ,e.[UUIDType] ,e.[UUParentID] ,e.[UUParentIDType]
,e.[Status] ,e.[AccountUUID] ,e.[Active] ,e.[Deleted]
,e.[Private] ,e.[SortOrder] ,e.[CreatedBy] ,e.[DateCreated]
,e.[Image] ,e.[RoleWeight] ,e.[RoleOperation],e.[NSFW]
,e.[Latitude] ,e.[Longitude] ,e.[Description], e.[IsAffiliate]
,el.Latitude, el.Longitude
FROM Events e
LEFT JOIN Accounts a ON e.HostAccountUUID = a.UUID
LEFT JOIN (SELECT DISTINCT EventUUID,Latitude, Longitude, Name, City, State, Country FROM EventLocations) el ON e.UUID = el.EventUUID
WHERE
(e.UUID = @PARENTUUID OR e.UUParentID = @PARENTUUID ) AND
(e.Private = 0 OR e.Private = @PRIVATE) AND
(e.Deleted = 0 OR e.Deleted = @DELETED) AND
(e.EndDate > @ENDDATE)" ;
// where = DatasetManager.BuildWhereClause(filter.Screens);
if (!string.IsNullOrWhiteSpace(where))
{
sql += " AND " + where;
}
if (string.IsNullOrWhiteSpace(filter.SortBy))
{ //for events we want to default sort by start date
sql += " ORDER BY StartDate ASC";
}
else
{
if (string.IsNullOrWhiteSpace(filter.SortDirection))
{
filter.SortDirection = "ASC";
}
sql += " ORDER BY " + filter.SortBy + " " + filter.SortDirection;
}
if (filter.PageResults)
{
if (filter.Page <= 0)
{
filter.Page = 1;
}
parameters.Add("@PAGESIZE", filter.PageSize);
parameters.Add("@PAGEINDEX", filter.Page);
sql += @" OFFSET @PAGESIZE *(@PAGEINDEX - 1) ROWS FETCH NEXT @PAGESIZE ROWS ONLY";
}
var events = context.Select <dynamic>(sql, parameters);
if (events != null)
{
//events = events.Filter(ref filter);
events = events.GroupBy(x => x.UUID).Select(group => group.First()).ToList();
return(events);
}
#region old code
/*
* //sw.UUID == parentUUID Includes parent item
* var x = context.GetAll<Event>()?.Where(sw =>
* (sw.UUID == parentUUID || sw.UUParentID == parentUUID) &&
* (sw.Private == false || sw.Private == filter.IncludePrivate) &&
* (sw?.Deleted == false || sw?.Deleted == filter.IncludeDeleted)
* );
*
#region join filters out events that don't have eventlocation
*
* //?.Join(context.GetAll<EventLocation>(),
* // evt => evt.UUID,
* // loc => loc.EventUUID,
* // (evt, loc) => new { evt, loc })
* // ?.Select(s =>
* // {
* // s.evt.HostName = context.GetAll<Account>().FirstOrDefault(w => w.UUID == s.evt.HostAccountUUID)?.Name;
* // s.evt.Latitude = s.loc.Latitude;
* // s.evt.Longitude = s.loc.Longitude;
*
* // if (filterParams != null && (filterParams.Latitude != 0 && filterParams.Longitude != 0))
* // {
* // s.evt.Distance = MathHelper.Distance(filterParams.Latitude, filterParams.Longitude, s.evt.Latitude ?? 0, s.evt.Longitude ?? 0);
* // }
* // return s.evt;
* // });
*
#endregion join filters out events that don't have eventlocation
*
* if (x == null || x.Any() == false)
* return new List<Event>();
*
* if ((filter.Latitude + filter.Longitude ) != 0)
* {
* foreach (var z in x)
* {
* //z.HostName = context.GetAll<Account>().FirstOrDefault(w => w.UUID == z.HostAccountUUID)?.Name;
* //var eventLocation = context.GetAll<EventLocation>().FirstOrDefault(w => w.EventUUID == z.UUID);
* //if (eventLocation == null)
* // continue;
* z.Distance = Math.Ceiling(MathHelper.Distance(filter.Latitude, filter.Longitude, z.Latitude ?? 0, z.Longitude ?? 0));
* }
* }
*
* if (filter.IncludeParent == false)
* {
* x = x.Where(s => s.UUID != parentUUID);// see if this excludes parent, if not try below
* // x = x.Where(s => x.All(y => y.UUID != parentUUID));
* }
*
* if (!string.IsNullOrWhiteSpace(filter.SortBy) &&
* filter.SortBy.EqualsIgnoreCase("distance"))
* {
* x = x.OrderBy(ob => ob?.Distance);
* }
* else
* {
* x = x.OrderBy(ob => ob?.StartDate);
* }
*
* return x.ToList();
*/
#endregion old code
}
//////if (!this.DataAccessAuthorized(s, "GET", false)) return ServiceResponse.Error("You are not authorized this action.");
}
catch (Exception ex)
{
Debug.Assert(false, ex.Message);
}
return(new List <dynamic>());
}
//public List<Account> ClearSensitiveData(List<Account> accounts)
//{
// if (accounts == null)
// return accounts;
// accounts.ForEach(am =>
// {
// am.Email = "";
// });
// return accounts;
//}
//public Account ClearSensitiveData(Account account)
//{
// if (account == null)
// return account;
// account.Email = "";
// return account;
//}
#region test
public List <Account> GetAllAccountsEx(ref DataFilter filter)
{
try
{
if (filter.IncludePrivate)
{ //if looking at private then check permission
if (!this.DataAccessAuthorized("ACCOUNT", false, filter.IncludePrivate))
{
filter.IncludePrivate = false;
//// if not allowed private then check if they can see anything..
if (!this.DataAccessAuthorized("ACCOUNT", false, filter.IncludePrivate))
{
return(new List <Account>());
}
}
}
using (var context = new GreenWerxDbContext(this._connectionKey))
{
DynamicParameters parameters = new DynamicParameters();// see function GetTableData
parameters.Add("@PRIVATE", filter.IncludePrivate);
parameters.Add("@DELETED", filter.IncludeDeleted);
string sql = @"SELECT COUNT(*) FROM Accounts e
WHERE
(e.Private = 0 OR e.Private = @PRIVATE) AND
(e.Deleted = 0 OR e.Deleted = @DELETED)" ;
string where = DatasetManager.BuildWhereClause(filter.Screens);
if (!string.IsNullOrWhiteSpace(where))
{
sql += " AND " + where;
var screenParams = DatasetManager.GetParameters(filter.Screens);
if (screenParams != null)
{
parameters.AddDynamicParams(screenParams);
}
}
filter.TotalRecordCount = (int)context.ExecuteScalar(sql, parameters);
if (filter.TotalRecordCount == 0)
{
return(new List <Account>());
}
parameters.Add("@CLIENTLAT", filter.Latitude);
parameters.Add("@CLIENTLON", filter.Longitude);
parameters.Add("@MEASURE", 3956.55); // 3956.55 = miles
//
sql = @"SELECT CEILING(dbo.CalcDistance(@CLIENTLAT, @CLIENTLON , e.Latitude, e.Longitude, @MEASURE )) as Distance
,[Name] ,[UUID] ,[UUIDType] ,[GUUID],
[Image], [Phone], [Status]
,[Email],[OwnerUUID],[CreatedBy],[WebSite],[Private] ,[Category],[LocationUUID]
,e.[Latitude], e.[Longitude], e.[IsAffiliate]
FROM Accounts e
WHERE
(e.Private = 0 OR e.Private = @PRIVATE) AND
(e.Deleted = 0 OR e.Deleted = @DELETED)" ;
where = DatasetManager.BuildWhereClause(filter.Screens);
if (!string.IsNullOrWhiteSpace(where))
{
sql += " AND " + where;
}
if (string.IsNullOrWhiteSpace(filter.SortBy))
{ //for events we want to default sort by start date
sql += " ORDER BY Name ASC";
}
else
{
if (string.IsNullOrWhiteSpace(filter.SortDirection))
{
filter.SortDirection = "ASC";
}
sql += " ORDER BY " + filter.SortBy + " " + filter.SortDirection;
}
if (filter.PageResults)
{
if (filter.Page <= 0)
{
filter.Page = 1;
}
// filter.PageSize = 50;
parameters.Add("@PAGESIZE", filter.PageSize);
parameters.Add("@PAGEINDEX", filter.Page);
sql += @" OFFSET @PAGESIZE *(@PAGEINDEX - 1) ROWS FETCH NEXT @PAGESIZE ROWS ONLY";
}
var accounts = context.Select <Account>(sql, parameters);
return(accounts.ToList());
}
//////if (!this.DataAccessAuthorized(s, "GET", false)) return ServiceResponse.Error("You are not authorized this action.");
}
catch (Exception ex)
{
Debug.Assert(false, ex.Message);
}
return(new List <Account>());
}