[Rt-devel] Custom fields as dates patch

[sunnavy]

Hi Joop

I just fixed "before" and "after" in cf-date too,
could you run tests in t/api/cf_date_search.t and t/web/cf_date.t and
see if all of them can pass?

thanks!

On 10-07-27 12:56, Joop wrote:
> Joop wrote:
> >Found one more problem.
> >I have entered a date through the Calendar popup (2010-07-23) and
> >saved it.
> >Then I tried to search for it and wasn't able to find the ticket
> >back. Looking at the database I discovered why.
> >It is stored in the database (ObjectCustomFieldValues) as
> >2010-07-22 22:00:00
> >The datepicker picks the date in in my Timezone (GMT+1, +1 for DST
> >=+2) but its stored in the database as GMT. That is not the
> >problem in itself but the date entered in the Query builder is NOT
> >converted to GMT and so never finds the ticket.
> Using cf-date branch is I what should have done instead of manually
> patching HEAD with a single commit from cf-date. Using branch
> cf-date did result in a correct working search with a date CF but
> only when looking for ON not with BEFORE or AFTER
> 
> The reason that fails is because of the queries being build. The ON
> query looks like:
> SELECT COUNT (DISTINCT main.id)
>  FROM    Tickets main
>       JOIN
>          ObjectCustomFieldValues ObjectCustomFieldValues_1
>       ON     (ObjectCustomFieldValues_1.CustomField = '327')
>          AND (ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket')
>          AND (ObjectCustomFieldValues_1.Disabled = '0')
>          AND (ObjectCustomFieldValues_1.ObjectId = main.id)
> WHERE (main.Status != 'deleted')
>       AND (main.Queue = '13'
>            AND ( ( ( (ObjectCustomFieldValues_1.Content >=
>                          '2010-07-25 22:00:00'
>                       AND ObjectCustomFieldValues_1.Content <=
>                              '2010-07-26 22:00:00')))))
>       AND (main.TYPE = 'ticket')
>       AND (main.EffectiveId = main.id)
> 
> And the BEFORE/AFTER looks like:
> SELECT main.*
>  FROM    Tickets main
>       JOIN
>          ObjectCustomFieldValues ObjectCustomFieldValues_1
>       ON     (ObjectCustomFieldValues_1.CustomField = '327')
>          AND (ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket')
>          AND (ObjectCustomFieldValues_1.Disabled = '0')
>          AND (ObjectCustomFieldValues_1.ObjectId = main.id)
> WHERE (main.Status != 'deleted')
>       AND (main.Queue = '13'
>            AND ( ( (ObjectCustomFieldValues_1.Content < '2010-07-29'
>                     OR ( (ObjectCustomFieldValues_1.Content = ''
>                           OR ObjectCustomFieldValues_1.Content IS NULL)
> --                         AND ObjectCustomFieldValues_1.LargeContent <
> --                                '2010-07-29'
> )))))
>       AND (main.TYPE = 'ticket')
>       AND (main.EffectiveId = main.id)
> 
> This query works and returns the expected result because of the two
> commented lines. If I run the original query it doesn't since Oracle
> doesn't like comparing CLOB columns like LargeContent against a
> constant.
> 
> I don't know why LargeContent is used only in BEFORE/AFTER queries
> and not with ON. Solutions seems to be to not use LargeContent but
> don't know the ramifications of that.
> 
> Regards,
> 
> Joop
> 
> 
> _______________________________________________
> List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel