Modify

#11005 closed defect (fixed)

sql error on Ticket Work Summary

Reported by: mlh@… Owned by: bobbysmith007
Priority: normal Component: TimingAndEstimationPlugin
Severity: normal Keywords:
Cc: Trac Release: 1.0

Description

after installation of the non-permissions based plugin I went to the view tickets section and attempted to load the Ticket Work Summary report and received the error:

Report execution failed: 
DataError: invalid input syntax for integer: "" LINE 19: AND ticket_change.time >= E'' ^ 
SELECT COUNT(*) FROM (

SELECT __ticket__ as __group__, __style__, ticket,
newvalue as Work_added, author, time as datetime, _ord
FROM(
  SELECT '' as __style__, author,
  t.summary as __ticket__,
  t.id as ticket,
  CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0
    ELSE CAST( newvalue AS DECIMAL ) END AS newvalue,
  ticket_change.time as time, 0 as _ord
  FROM ticket_change
  JOIN ticket t on t.id = ticket_change.ticket
  LEFT JOIN ticket_custom as billable on billable.ticket = t.id
    and billable.name = 'billable'
  WHERE field = 'hours' and
    t.status IN (%s, %s, %s, %s, %s)
      AND billable.value in (%s, %s)
      AND ticket_change.time >= %s
      AND ticket_change.time < %s

  UNION

  SELECT 'background-color:#DFE;' as __style__,
    'Total work done on the ticket in the selected time period ' as author,
    t.summary as __ticket__,
    t.id as ticket,
  SUM( CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0
         ELSE CAST( newvalue AS DECIMAL ) END ) as newvalue,
    NULL as time, 1 as _ord
  FROM ticket_change
  JOIN ticket t on t.id = ticket_change.ticket
  LEFT JOIN ticket_custom as billable on billable.ticket = t.id
    and billable.name = 'billable'
  WHERE field = 'hours' and
    t.status IN (%s, %s, %s, %s, %s)
      AND billable.value in (%s, %s)
      AND ticket_change.time >= %s
      AND ticket_change.time < %s
  GROUP By t.id, t.summary
)  as tbl
ORDER BY __ticket__, _ord ASC, time ASC

    
) AS tab

Attachments (0)

Change History (4)

comment:1 Changed 18 months ago by bobbysmith007

As noted on the report description: "Reports Must Be Accessed From the Management Screen".

The Time reports require variables to be filled in. In order to provide those variables with values, you should access the report via the Management nav bar item (/billing). This will build links based off of some form fields and provide those missing values.

A couple interesting points:

  • The reports used to be removed from the active tickets screen, but they don't seem to be at this point (I will look into this).
  • There is a form field on the reports page that allows changing those values, but apparently it doesn't show up when there are no provided values (that seems a bit off).

comment:2 Changed 18 months ago by bobbysmith007

active tickets screen

Sorry, meant "view tickets screen"

comment:3 Changed 18 months ago by bobbysmith007

(In [12966]) remove hours/work summary reports from the view-tickets screen re #11005

comment:4 Changed 18 months ago by bobbysmith007

  • Resolution set to fixed
  • Status changed from new to closed

The plugin once again hides its reports on the view-tickets screen so unless I hear otherwise this is closed

Add Comment

Modify Ticket

Action
as closed .
The resolution will be deleted. Next status will be 'reopened'.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.