Modify

Opened 18 months ago

Last modified 18 months ago

#11211 new defect

Report 90 not functioning

Reported by: trenb@… Owned by: framay
Priority: high Component: BudgetingPlugin
Severity: normal Keywords:
Cc: Trac Release: 1.0

Description

New install of trac 1.0.1 and the version of the budgeting plugin from svn. Report 90 does not function. It errors out with the following error:

Report execution failed:
OperationalError: (1060, "Duplicate column name 'status'")
SELECT COUNT(*) FROM (
SELECT t.id, t.summary, t.milestone AS __group__,
           '../milestone/' || t.milestone AS __grouplink__,
           t.owner, t.reporter, t.status, t.type, t.priority, t.component,
           COUNT(b.ticket) AS num,
           SUM(b.cost) AS cost,
           SUM(b.estimation) AS estimation,
           FLOOR(AVG(b.status)) || '%' AS status, 
           (CASE t.status 
               WHEN 'closed' THEN 'budgeting_report_closed'
               ELSE (CASE SUM(b.cost) > SUM(b.estimation)
                      WHEN true THEN 'budgeting_report_est_exceeded'
                    END)
           END) AS __class__
    FROM ticket t LEFT JOIN budgeting b ON b.ticket = t.id
    WHERE
        t.milestone LIKE (CASE %s WHEN '' THEN '%' ELSE %s END)
    AND
        (t.component LIKE (CASE %s WHEN '' THEN '%' ELSE %s END)
        OR t.component IS NULL)
    AND 
        (t.owner LIKE (CASE %s WHEN '' THEN %s ELSE %s END)
        OR t.owner IS NULL OR b.username LIKE (CASE %s 
                                                WHEN '' THEN %s ELSE %s
                                              END))
    GROUP BY
        t.id, t.type, t.priority, t.summary, t.owner, t.reporter,
        t.component, t.status, t.milestone
    HAVING COUNT(b.ticket) > 0
    ORDER BY t.milestone desc, t.status, t.id
    DESC
) AS tab

Any assistance would be appreciated, as I cannot actually make use of the module.

When I create a new ticket, I do not get the option to enter budgeting information.

Kind of at a loss. Do I need to downgrade my trac installation to make this work?

Thanks in advance.

Attachments (0)

Change History (4)

comment:1 Changed 18 months ago by trenb@…

1) I was wrong about the module not working. I figured out how to add budgeting info to a ticket. 2) How do I recreate the report 90 that wasn't working? I removed it, but it doesn't seem to come back after a re-install of the module.

Thanks in advance!

comment:2 Changed 18 months ago by framay

Well this our report 90 (copied from report view in Trac):

SELECT t.id, t.summary, t.milestone AS __group__, '../milestone/' || t.milestone AS __grouplink__, t.owner, t.reporter, t.status, t.type, t.priority, t.component,
    count(b.ticket) AS Anz, sum(b.cost) AS Aufwand, sum(b.estimation) AS Schätzung, floor(avg(b.status)) || '%' AS "Status", 
(CASE t.status 
      WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
      ELSE 
        (CASE sum(b.cost) > sum(b.estimation) WHEN true THEN 'font-weight: bold; background: orange;' END)
    END) AS __style__
    from ticket t
    left join budgeting b ON b.ticket = t.id left join milestone m on t.milestone = m.name
    where t.milestone like 
    (CASE $MILESTONE
              WHEN '' THEN '%' 
              ELSE $MILESTONE END) and not milestone in ('abgelehnt', 'tbd') and
    (t.component like (CASE $COMPONENT
              WHEN '' THEN '%' 
              ELSE $COMPONENT END)) and 
    (t.owner like (CASE $OWNER
              WHEN '' THEN $USER 
              ELSE $OWNER END) or 
     b.username like (CASE $OWNER
              WHEN '' THEN $USER 
              ELSE $OWNER END) )
    group by t.id, t.type, t.priority, t.summary, t.owner, t.reporter, t.component, t.milestone, t.status, case when m.due=0 then 1500000000000000+bit_length(m.name) else m.due end
order by case when m.due=0 then 1500000000000000+bit_length(m.name) else m.due end desc, t.id desc

Note that we use German words, so maybe you get errors when using "Schätzung" at an ANSI-DB. We use Postgres 8.4 for Trac; we haven't tested it with SQLite.

comment:3 follow-up: Changed 18 months ago by trenb@…

I tried using your report and it fails in the same way. I am not using SQLite, but MySQL 5.5.

Does this report only work on Postgres? If so, it should be marked as such.

Here is your report with the german words translated:

Report execution failed:
OperationalError: (1060, "Duplicate column name 'Status'")
SELECT COUNT(*) FROM (
SELECT t.id, t.summary, t.milestone AS __group__, '../milestone/' ||
t.milestone AS __grouplink__, t.owner, t.reporter, t.status, t.type,
t.priority, t.component,
     count(b.ticket) AS Num, sum(b.cost) AS Cost, sum(b.estimation) AS
Estimation, floor(avg(b.status)) || '%' AS "Status",
(CASE t.status
       WHEN 'closed' THEN 'color: #777; background: #ddd; border-color:
#ccc;'
       ELSE
         (CASE sum(b.cost) > sum(b.estimation) WHEN true THEN 'font-weight:
bold; background: orange;' END)
     END) AS __style__
     from ticket t
     left join budgeting b ON b.ticket = t.id left join milestone m on
t.milestone = m.name
     where t.milestone like
     (CASE %s
               WHEN '' THEN '%'
               ELSE %s END) and not milestone in ('rejected',
'tbd') and
     (t.component like (CASE %s
               WHEN '' THEN '%'
               ELSE %s END)) and
     (t.owner like (CASE %s
               WHEN '' THEN %s
               ELSE %s END) or
      b.username like (CASE %s
               WHEN '' THEN %s
               ELSE %s END) )
     group by t.id, t.type, t.priority, t.summary, t.owner, t.reporter,
t.component, t.milestone, t.status, case when m.due=0 then
1500000000000000+bit_length(m.name) else m.due end
order by case when m.due=0 then 1500000000000000+bit_length(m.name) else
m.due end desc, t.id desc
) AS tab

comment:4 in reply to: ↑ 3 Changed 18 months ago by framay

Replying to trenb@…: When I replace (CASE %s ... ) (from where t.milestone like until group by t.id... with that it works:

(CASE $MILESTONE
              WHEN '' THEN '%' 
              ELSE $MILESTONE END) and not milestone in ('abgelehnt', 'tbd') and
    (t.component like (CASE $COMPONENT
              WHEN '' THEN '%' 
              ELSE $COMPONENT END)) and 
    (t.owner like (CASE $OWNER
              WHEN '' THEN $USER 
              ELSE $OWNER END) or 
     b.username like (CASE $OWNER
              WHEN '' THEN $USER 
              ELSE $OWNER END) )

Note that you only get an count with your SQL.

Add Comment

Modify Ticket

Action
as new The owner will remain framay.
Author


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

 
Note: See TracTickets for help on using tickets.