Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Yanick Van Barneveld 27 posts 148 karma points
    Nov 10, 2016 @ 10:26
    Yanick Van Barneveld
    0

    UIOMaticListViewFilter - Distinct by year

    Hi,

    I am trying to create a UIOMaticListViewFilter by using unique years. Now there are multiple values with a different date but with the same year. See the example below:

    2016-11-11T17:00:00Z
    2016-11-04T17:00:00Z
    2016-12-08T16:00:00Z
    2016-12-28T16:00:00Z
    2017-02-09T17:00:00Z
    2015-10-07T13:00:00Z
    

    I am using the following code to create the ListViewFilter.

    [UIOMaticListViewFilter(Name = "Jaar"), UIOMaticListViewField(Name = "Datum", Config = "{'format' : '{{value | date:\"dd/MM/yyyy\"}}'}"), UIOMaticField(Name = "Begindatum", Description = "Geef de begindatum en tijd op", View = "datetime")]
    

    I do not have any idea how to get the unique years instead of all the dates it is returning right now. Is this even possible yet?

    Hope someone can help me out.

    Kind regards,
    Yanick

  • Tim Geyssens 6419 posts 15020 karma points MVP 2x c-trib
    Nov 10, 2016 @ 10:30
    Tim Geyssens
    0

    Haven't tried it but you could add a new property to the model, where you just output the year (not the full date) by fetching it from the full date and then applying the ListViewFilter attribute on that one?

  • Yanick Van Barneveld 27 posts 148 karma points
    Nov 10, 2016 @ 10:42
    Yanick Van Barneveld
    0

    Hmm, I have added succesful a filter like this:

    [Required]
    [UIOMaticListViewField(Name = "Datum", Config = "{'format' : '{{value | date:\"dd/MM/yyyy\"}}'}"), UIOMaticField(Name = "Begindatum", Description = "Geef de begindatum en tijd op", View = "datetime")]
    public DateTime Begintime { get; set; }
    
    [Ignore]
    [UIOMaticListViewFilter(Name = "Jaar"), UIOMaticListViewField(Name = "Jaar")]
    public int Year
    {
        get { return Begintime.Year; }
    }
    

    Filter shows only the years, like I wanted. But now when I select a year it returns the following error:

    Server error: Contact administrator, see log for full details.
    Failed to get paged
    

    Logs is saying this, like expected (column Year does not exists):

    System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'Year'.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteScalar()
       at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClassa.<ExecuteScalarWithRetry>b__9()
       at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
       at Umbraco.Core.Persistence.Database.ExecuteScalar[T](String sql, Object[] args)
    

    Any other idea's?

  • Tim Geyssens 6419 posts 15020 karma points MVP 2x c-trib
    Nov 10, 2016 @ 10:45
    Tim Geyssens
    0

    So if you try to sort by year or what are you trying todo? So what does "select a year" mean :)

  • Tim Geyssens 6419 posts 15020 karma points MVP 2x c-trib
    Nov 10, 2016 @ 11:01
    Tim Geyssens
    0

    AH ok if you actually filter by a year, got it :) hmmm thought that is done in memory will check

  • Tim Geyssens 6419 posts 15020 karma points MVP 2x c-trib
    Nov 10, 2016 @ 11:03
    Tim Geyssens
    1

    Hmm yeah in that case you'll need to make sure that the sql statement actually returns a year, you can use the event model for that, will try to set you up with an example

  • Yanick Van Barneveld 27 posts 148 karma points
    Nov 10, 2016 @ 11:44
    Yanick Van Barneveld
    0

    Super! Maybe I have to save it in my database as extra field (What is not a really nice workaround)?

    Like to see your example :)

    Thanks!

  • Tim Geyssens 6419 posts 15020 karma points MVP 2x c-trib
    Nov 10, 2016 @ 13:02
    Tim Geyssens
    0

    Mind sharing your table create script? THen I can provide you with the code sample

  • Yanick Van Barneveld 27 posts 148 karma points
    Nov 10, 2016 @ 13:11
    Yanick Van Barneveld
    1

    The SQL to create the table is as following:

    CREATE TABLE [dbo].[Event](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](255) NOT NULL,
    [Location] [nvarchar](255) NOT NULL,
    [Excerpt] [nvarchar](255) NOT NULL,
    [Body] [text] NOT NULL,
    [Begintime] [datetime] NOT NULL,
    [Endtime] [datetime] NOT NULL,
    [Places] [int] NOT NULL,
    [AlbumId] [int] NULL,
    [CoverId] [int] NULL,
    CONSTRAINT [PK_Event] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    

    My POCO is like this:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using UIOMatic.Attributes;
    using UIOMatic.Enums;
    using Umbraco.Core.Persistence;
    using Umbraco.Core.Persistence.DatabaseAnnotations;
    
    namespace Company.Models
    {
        [UIOMatic("event", "Evenementen", "Evenement", FolderIcon = "icon-users", ItemIcon = "icon-user", RenderType = UIOMaticRenderType.List, ShowOnSummaryDashboard = true)]
        [System.Web.DynamicData.TableName("Event")]
        public class Event
        {
            [UIOMaticListViewField, PrimaryKeyColumn(AutoIncrement = true)]
            public int Id { get; set; }
    
            [Required]
            [UIOMaticListViewField(Name = "Titel"), UIOMaticField(Name = "Titel", Description = "Geef de titel op")]
            public string Title { get; set; }
    
            [Required]
            [UIOMaticListViewFilter(Name = "Locatie"), UIOMaticListViewField(Name = "Locatie"), UIOMaticField(Name = "Locatie", Description = "Geef de locatie op")]
            public string Location { get; set; }
    
            [Required]
            [UIOMaticField(Name = "Korte omschrijving", Description = "Geef een korte omschrijving op")]
            public string Excerpt { get; set; }
    
            [Required]
            [UIOMaticListViewField(Name = "Datum", Config = "{'format' : '{{value | date:\"dd/MM/yyyy\"}}'}"), UIOMaticField(Name = "Begindatum", Description = "Geef de begindatum en tijd op", View = "datetime")]
            public DateTime Begintime { get; set; }
    
            [Ignore]
            [UIOMaticListViewFilter(Name = "Jaar"), UIOMaticListViewField(Name = "Jaar")]
            public int Year
            {
                get { return Begintime.Year; }
            }
    
            [Required]
            [UIOMaticField(Name = "Einddatum", Description = "Geef de einddatum en tijd op", View = "datetime")]
            public DateTime Endtime { get; set; }
    
            [Required]
            [UIOMaticField(Name = "Beschikbare plaatsen", Description ="Aantal beschikbare plaatsen", View = "number")]
            public int Places { get; set; }
    
            [UIOMaticField(Name = "Hoofdfoto", Description = "Foto bevindt zich in de header", View = UIOMatic.Constants.FieldEditors.PickerMedia)]
            public int CoverId { get; set; }
    
            [UIOMaticField(Name= "Fotoalbum", Description = "Fotoalbum indien aanwezig", View = UIOMatic.Constants.FieldEditors.PickerMedia)]
            public int AlbumId { get; set; }
    
            [Ignore]
            public List<Media> Album
            {
                get
                {
                    if (AlbumId != 0)
                    {
                        return MediaRepository.getAlbum(AlbumId);
                    }
    
                    return null;
                }
            }
    
            [Ignore]
            public Media Cover
            {
                get
                {
                    if (CoverId != 0)
                    {
                        return MediaRepository.getMedia(CoverId);
                    }
    
                    return null;
                }
            }
    
            [Required]
            [UIOMaticField(Name = "Volledig bericht", Description = "Geef een volledige beschrijving op", View = "rte")]
            public string Body { get; set; }
    
            public List<Registration> Registrations(int statusId = -1)
            {
                if (Id != 0)
                    return RegistrationRepository.getRegistrations(Id, statusId);
    
                return null;
            }
    
            [Ignore]
            public Registration Registration {
                get {
                    if (Id != 0)
                    {
                        Registration registration = EventRepository.getStatus(this);
    
                        if ((registration != null) &&  registration.Id != 0)
                            return registration;
                    }
    
                    return null;
                }
            }
    
            public override string ToString()
            {
                return Title;
            }
        }
    }
    

    I hope you can do something with this :)

    POCO on Pastebin: http://pastebin.com/UFytV5nz

  • Tim Geyssens 6419 posts 15020 karma points MVP 2x c-trib
    Nov 10, 2016 @ 13:13
    Tim Geyssens
    1

    Ah awesome yeah gonna try to same as you so a filter on distinct year

  • Tim Geyssens 6419 posts 15020 karma points MVP 2x c-trib
    Nov 10, 2016 @ 13:36
    Tim Geyssens
    1

    Ok can confirm the behaviour :) so now looking into a way we can get this working

  • Tim Geyssens 6419 posts 15020 karma points MVP 2x c-trib
    Nov 10, 2016 @ 14:22
    Tim Geyssens
    100

    Aight I needed to make a change to the core since filters wasn't passed as event args... so you'll have to wait untill v2.0.3 is out (which I can trigger for you).

    It's quite a bit of code but this is because filters/search and ordering needs to keep working, I'll see if I can provide a couple of helper methods so we can keep this code shorter...

    But it should then work with this code

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    using System.Web.Routing;
    using UIOMatic.Examples.ContactEntries.Controllers;
    using UIOMatic.Examples.ContactEntries.Models;
    using Umbraco.Core;
    using Umbraco.Core.Persistence;
    using Umbraco.Web.UI.JavaScript;
    using Umbraco.Web;
    namespace UIOMatic.Examples.ContactEntries
    {
        public class App: ApplicationEventHandler
        {
            protected override void ApplicationStarted(UmbracoApplicationBase umbracoApplication, ApplicationContext applicationContext)
            {
                UIOMatic.Services.UIOMaticObjectService.BuiltQuery += UIOMaticObjectService_BuiltQuery;
            }
    
            private void UIOMaticObjectService_BuiltQuery(object sender, QueryEventArgs e)
            {
                if(e.ObjectType == typeof(Event))
                {
                    e.Query = Umbraco.Core.Persistence.Sql.Builder
                       .Append("SELECT DATEPART(yyyy,Event.Begintime) AS [Year], Event.*")
                       .Append("FROM Event")
                       .Append("WHERE 1=1");
    
                    // Filter by search term
                    if (!string.IsNullOrEmpty(e.SearhTerm))
                    {
                        e.Query.Append("AND (1=0");
    
                        var c = 0;
                        foreach (var property in typeof(Event).GetProperties())
                        {
                            var attris = property.GetCustomAttributes(true);
                            if (attris.All(x => x.GetType() != typeof(IgnoreAttribute)))
                            {
                                var columnName = property.Name;
    
                                var columnAttri = attris.FirstOrDefault(x => x.GetType() == typeof(ColumnAttribute)) as ColumnAttribute;
                                if (columnAttri != null)
                                    columnName = columnAttri.Name;
    
                                e.Query.Append("OR " + columnName + " like @0", "%" + e.SearhTerm + "%");
                                c++;
    
                            }
                        }
    
                        e.Query.Append(")");
                    }
                    if (e.Filters != null && e.Filters.Any())
                    {
                        foreach (var filter in e.Filters)
                        {
                            if (filter.Key == "Year")
                                e.Query.Append("AND DATEPART(yyyy,Event.Begintime) = @0", filter.Value);
                            else
                                e.Query.Append("AND " + filter.Key + " = @0", filter.Value);
                        }
                    }
    
                    e.Query.Append("ORDER BY " + (string.IsNullOrEmpty(e.SortColumn) ? " Id desc" : e.SortColumn + " " + e.SortOrder));
    
    
                }
            }
        }
    }
    
  • Tim Geyssens 6419 posts 15020 karma points MVP 2x c-trib
    Nov 10, 2016 @ 14:26
    Tim Geyssens
    0

    So when would you need this? I can then plan the 2.0.3 release :)

  • Yanick Van Barneveld 27 posts 148 karma points
    Nov 10, 2016 @ 14:38
    Yanick Van Barneveld
    0

    Awesome, thanks for this fix! I need it before next month, so not really in a hurry. It is good to know that there will be a possibility, thanks for all the work!

  • Tim Geyssens 6419 posts 15020 karma points MVP 2x c-trib
    Nov 10, 2016 @ 14:40
    Tim Geyssens
    1

    Cool well the release is scheduled for next Thursday (currently doing a maintenance releaase every Thursday) so that would be in time, if you need it sooner let me know

  • Tim Geyssens 6419 posts 15020 karma points MVP 2x c-trib
    Nov 17, 2016 @ 11:20
    Tim Geyssens
    1

    Maintenance release is out, so you should be able to update to 2.0.3 once it's indexed by nuget

  • Yanick Van Barneveld 27 posts 148 karma points
    Nov 17, 2016 @ 12:56
    Yanick Van Barneveld
    0

    Great, I will try it asap! :)

  • Tim Geyssens 6419 posts 15020 karma points MVP 2x c-trib
    Nov 17, 2016 @ 12:56
    Tim Geyssens
    0

    Super :)

Please Sign in or register to post replies

Write your reply to:

Draft