Changeset 3785

Show
Ignore:
Timestamp:
06/04/08 08:36:00 (6 months ago)
Author:
bobbysmith007
Message:

closes #3131

closes #3074

T&E(trac 10) 0.6.6

I brought the trac 10 branch up to the same version number of the plugin so that hopefully there is less confusion on my part about versions.

Fixes horrible bug relating to database access (due to my misunderstanding of which things were stateful in trac/python).

Fixed some bugs in the way billable values were being compared in the reports file (strong vs weak typed databases)

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • timingandestimationplugin/branches/trac0.10/setup.py

    r3520 r3785  
    88      description='Plugin to make Trac support time estimation and tracking', 
    99      keywords='trac plugin estimation timetracking', 
    10       version='0.5.6', 
     10      version='0.6.6', 
    1111      url='http://www.trac-hacks.org/wiki/TimingAndEstimationPlugin', 
    1212      license='http://www.opensource.org/licenses/mit-license.php', 
    …… 
    3535## trac-hacks user: masariello 
    3636## Helped Get Reports working in postgres 
    37 ## and started moving toward generic work  
     37## and started moving toward generic work 
    3838## rather than hours 
    3939 

  • timingandestimationplugin/branches/trac0.10/timingandestimationplugin/api.py

    r3398 r3785  
    99from trac.env import IEnvironmentSetupParticipant 
    1010from trac.perm import IPermissionRequestor, PermissionSystem 
    11 from webui import *  
     11from webui import * 
    1212from ticket_webui import * 
    1313from reportmanager import CustomReportManager 
    …… 
    1515## report columns 
    1616## id|author|title|query|description 
    17     
     17 
    1818class TimeTrackingSetupParticipant(Component): 
    1919    """ This is the config that must be there for this plugin to work: 
    20          
     20 
    2121        [ticket-custom] 
    2222        totalhours = text 
    …… 
    3131        hours.value = 0 
    3232        hours.label = Hours to Add 
    33          
     33 
    3434        estimatedhours = text 
    3535        estimatedhours.value = 0 
    3636        estimatedhours.label = Estimated Hours? 
    37          
     37 
    3838        """ 
    3939    implements(IEnvironmentSetupParticipant) 
    …… 
    4242    db_version = None 
    4343    db_installed_version = None 
    44      
     44 
    4545    """Extension point interface for components that need to participate in the 
    4646    creation and upgrading of Trac environments, for example to create 
    …… 
    4848    def __init__(self): 
    4949        # Setup logging 
    50         dbhelper.mylog = self.log 
    51         dbhelper.env = self.env 
    5250        self.db_version_key = 'TimingAndEstimationPlugin_Db_Version' 
    5351        self.db_version = 7 
    …… 
    7270        if self.environment_needs_upgrade(None): 
    7371            self.upgrade_environment(None) 
    74              
     72 
    7573    def system_needs_upgrade(self): 
    7674        return self.db_installed_version < self.db_version 
    77          
     75 
    7876    def do_db_upgrade(self): 
    7977        # Legacy support hack (supports upgrades from 0.1.6 to 0.1.7) 
    8078        if self.db_installed_version == 0: 
    81             bill_date = dbhelper.db_table_exists('bill_date'); 
    82             report_version = dbhelper.db_table_exists('report_version'); 
     79            bill_date = dbhelper.db_table_exists(self, 'bill_date'); 
     80            report_version = dbhelper.db_table_exists(self, 'report_version'); 
    8381            if bill_date and report_version: 
    8482                self.db_installed_version = 1 
    8583        # End Legacy support hack 
    86          
    87          
     84 
     85 
    8886        if self.db_installed_version < 1: 
    8987            print "Creating bill_date table" 
    …… 
    9593            ); 
    9694            """ 
    97             dbhelper.execute_non_query( sql) 
    98              
     95            dbhelper.execute_non_query(self, sql) 
     96 
    9997            print "Creating report_version table" 
    10098            sql = """ 
    …… 
    105103            ); 
    106104            """ 
    107             dbhelper.execute_non_query(sql) 
     105            dbhelper.execute_non_query(self, sql) 
    108106 
    109107        if self.db_installed_version < 4: 
    …… 
    112110            ALTER TABLE report_version ADD COLUMN tags varchar(1024) null; 
    113111            """ 
    114             dbhelper.execute_non_query(sql) 
     112            dbhelper.execute_non_query(self, sql) 
    115113 
    116114        if self.db_installed_version < 5: 
    …… 
    121119            sql = "DELETE FROM report " \ 
    122120                  "WHERE author=%s AND id IN (SELECT report FROM report_version)" 
    123             dbhelper.execute_non_query( sql, 'Timing and Estimation Plugin') 
    124              
     121            dbhelper.execute_non_query(self, sql, 'Timing and Estimation Plugin') 
     122 
    125123            sql = "DROP TABLE report_version" 
    126             dbhelper.execute_non_query( sql) 
    127          
     124            dbhelper.execute_non_query(self, sql) 
     125 
    128126        # 6 & 7 are report upgrades 
    129          
     127 
    130128        # This statement block always goes at the end this method 
    131         dbhelper.set_system_value(self.db_version_key, self.db_version) 
     129        dbhelper.set_system_value(self, self.db_version_key, self.db_version) 
    132130        self.db_installed_version = self.db_version 
    133      
     131 
    134132 
    135133    def do_reports_upgrade(self): 
    …… 
    150148                               group_title) 
    151149 
    152      
     150 
    153151    def ticket_fields_need_upgrade(self): 
    154152        ticket_custom = "ticket-custom" 
    …… 
    158156                     #self.config.get( ticket_custom, "billable.order") and \ 
    159157                     #(not self.config.get( ticket_custom, "lastbilldate" )) and \ 
    160                       
     158 
    161159                     self.config.get( ticket_custom, "hours" ) and \ 
    162160                     self.config.get( ticket_custom, "totalhours.order") and \ 
    …… 
    164162                     self.config.get( ticket_custom, "estimatedhours.order") and \ 
    165163                     self.config.get( ticket_custom, "estimatedhours")) 
    166      
     164 
    167165    def do_ticket_field_upgrade(self): 
    168166        ticket_custom = "ticket-custom" 
    169          
     167 
    170168        self.config.set(ticket_custom,"totalhours", "text") 
    171169        if not self.config.get( ticket_custom, "totalhours.order") : 
    172170            self.config.set(ticket_custom,"totalhours.order", "4") 
    173171        self.config.set(ticket_custom,"totalhours.value", "0") 
    174         self.config.set(ticket_custom,"totalhours.label", "Total Hours")                 
     172        self.config.set(ticket_custom,"totalhours.label", "Total Hours") 
    175173 
    176174        self.config.set(ticket_custom,"billable", "checkbox") 
    …… 
    179177            self.config.set(ticket_custom,"billable.order", "3") 
    180178        self.config.set(ticket_custom,"billable.label", "Billable?") 
    181              
     179 
    182180        self.config.set(ticket_custom,"hours", "text") 
    183181        self.config.set(ticket_custom,"hours.value", "0") 
    …… 
    185183            self.config.set(ticket_custom,"hours.order", "2") 
    186184        self.config.set(ticket_custom,"hours.label", "Add Hours to Ticket") 
    187              
     185 
    188186        self.config.set(ticket_custom,"estimatedhours", "text") 
    189187        self.config.set(ticket_custom,"estimatedhours.value", "0") 
    …… 
    195193 
    196194    def needs_user_man(self): 
    197         maxversion = dbhelper.get_scalar("SELECT MAX(version) FROM wiki WHERE name like %s", 0, 
     195        maxversion = dbhelper.get_scalar(self, "SELECT MAX(version) FROM wiki WHERE name like %s", 0, 
    198196                                         user_manual_wiki_title) 
    199197        if (not maxversion) or maxversion < user_manual_version: 
    …… 
    208206        VALUES ( %s, %s, %s, 'Timing and Estimation Plugin', '127.0.0.1', %s,'',0) 
    209207        """ 
    210         dbhelper.execute_non_query(sql, 
     208        dbhelper.execute_non_query(self, sql, 
    211209                                   user_manual_wiki_title, 
    212210                                   user_manual_version, 
    213211                                   when, 
    214212                                   user_manual_content) 
    215              
    216          
     213 
     214 
    217215    def environment_needs_upgrade(self, db): 
    218216        """Called when Trac checks whether the environment needs to be upgraded. 
    219          
     217 
    220218        Should return `True` if this participant needs an upgrade to be 
    221219        performed, `False` otherwise. 
    …… 
    224222        return (self.system_needs_upgrade()) or \ 
    225223               (self.ticket_fields_need_upgrade()) or \ 
    226                (self.needs_user_man())  
    227              
     224               (self.needs_user_man()) 
     225 
    228226    def upgrade_environment(self, db): 
    229227        """Actually perform an environment upgrade. 
    230          
     228 
    231229        Implementations of this method should not commit any database 
    232230        transactions. This is done implicitly after all participants have 
    …… 
    251249 
    252250 
    253          
    254  
    255  
    256  
     251 
     252 
     253 
     254 

  • timingandestimationplugin/branches/trac0.10/timingandestimationplugin/dbhelper.py

    r3119 r3785  
    1 mylog = None; 
    2 env = None; 
    31 
    4 def get_all(sql, *params): 
     2def get_all(com, sql, *params): 
    53    """Executes the query and returns the (description, data)""" 
    6     db = env.get_db_cnx() 
     4    db = com.env.get_db_cnx() 
    75    cur = db.cursor() 
    86    desc  = None 
    …… 
    1412        db.commit(); 
    1513    except Exception, e: 
    16         mylog.error('There was a problem executing sql:%s \n \ 
     14        com.log.error('There was a problem executing sql:%s \n \ 
    1715with parameters:%s\nException:%s'%(sql, params, e)); 
    1816        db.rollback(); 
    …… 
    2119    except: 
    2220        pass 
    23      
     21 
    2422    return (desc, data) 
    2523 
    26 def execute_non_query( sql, *params): 
     24def execute_non_query(com, sql, *params): 
    2725    """Executes the query on the given project""" 
    28     db = env.get_db_cnx() 
     26    db = com.env.get_db_cnx() 
    2927    cur = db.cursor() 
    3028    try: 
    …… 
    3230        db.commit() 
    3331    except Exception, e: 
    34         mylog.error('There was a problem executing sql:%s \n \ 
     32        com.log.error('There was a problem executing sql:%s \n \ 
    3533with parameters:%s\nException:%s'%(sql, params, e)); 
    3634        db.rollback(); 
    …… 
    3937    except: 
    4038        pass 
    41      
    42 def get_first_row( sql,*params): 
     39 
     40def get_first_row(com, sql,*params): 
    4341    """ Returns the first row of the query results as a tuple of values (or None)""" 
    44     db = env.get_db_cnx() 
     42    db = com.env.get_db_cnx() 
    4543    cur = db.cursor() 
    4644    data = None; 
    …… 
    5048        db.commit(); 
    5149    except Exception, e: 
    52         mylog.error('There was a problem executing sql:%s \n \ 
     50        com.log.error('There was a problem executing sql:%s \n \ 
    5351        with parameters:%s\nException:%s'%(sql, params, e)); 
    5452        db.rollback() 
    …… 
    5957    return data; 
    6058 
    61 def get_scalar(sql, col=0, *params): 
     59def get_scalar(com, sql, col=0, *params): 
    6260    """ Gets a single value (in the specified column) from the result set of the query""" 
    63     data = get_first_row(sql, *params); 
     61    data = get_first_row(com, sql, *params); 
    6462    if data: 
    6563        return data[col] 
    …… 
    6765        return None; 
    6866 
    69 def execute_in_trans(*args): 
    70     db = env.get_db_cnx() 
     67def execute_in_trans(com, *args): 
     68    db = com.env.get_db_cnx() 
    7169    cur = db.cursor() 
    7270    result = True 
    …… 
    7674        db.commit() 
    7775    except Exception, e: 
    78         mylog.error('There was a problem executing sql:%s \n \ 
     76        com.log.error('There was a problem executing sql:%s \n \ 
    7977        with parameters:%s\nException:%s'%(sql, params, e)); 
    8078        db.rollback(); 
    …… 
    8684    return result 
    8785 
    88 def db_table_exists( table): 
    89     db = env.get_db_cnx() 
     86def db_table_exists(com, table): 
     87    db = com.env.get_db_cnx() 
    9088    sql = "SELECT * FROM %s LIMIT 1" % table; 
    9189    cur = db.cursor() 
    …… 
    9795        has_table = False 
    9896        db.rollback() 
    99          
     97 
    10098    try: 
    10199        db.close() 
    …… 
    104102    return has_table 
    105103 
    106 def get_column_as_list(sql, col=0, *params): 
    107     data = get_all(sql, *params)[1] or () 
     104def get_column_as_list(com, sql, col=0, *params): 
     105    data = get_all(com, sql, *params)[1] or () 
    108106    return [valueList[col] for valueList in data] 
    109107 
    110 def get_system_value(key): 
    111     return get_scalar("SELECT value FROM system WHERE name=%s", 0, key) 
     108def get_system_value(com, key): 
     109    return get_scalar(com, "SELECT value FROM system WHERE name=%s", 0, key) 
    112110 
    113 def set_system_value(key, value): 
    114     if get_system_value(key): 
    115         execute_non_query("UPDATE system SET value=%s WHERE name=%s", value, key)         
     111def set_system_value(com, key, value): 
     112    if get_system_value(com, key): 
     113        execute_non_query(com, "UPDATE system SET value=%s WHERE name=%s", value, key) 
    116114    else: 
    117         execute_non_query("INSERT INTO system (value, name) VALUES (%s, %s)", 
     115        execute_non_query(com, "INSERT INTO system (value, name) VALUES (%s, %s)", 
    118116            value, key) 
    119117 
    120118 
    121 def get_result_set(sql, *params): 
     119def get_result_set(com, sql, *params): 
    122120    """Executes the query and returns a Result Set""" 
    123     tpl = get_all(sql, *params); 
     121    tpl = get_all(com, sql, *params); 
    124122    if tpl and tpl[0] and tpl[1]: 
    125123        return ResultSet(tpl) 
    …… 
    131129    """ the result of calling getResultSet """ 
    132130    def __init__ (self, (columnDescription, rows)): 
    133         self.columnDescription, self.rows = columnDescription, rows  
     131        self.columnDescription, self.rows = columnDescription, rows 
    134132        self.columnMap = self.get_column_map() 
    135133 
    …… 
    144142                i+=1 
    145143        return h; 
    146      
     144 
    147145    def value(self, col, row ): 
    148146        """ given a row(list or idx) and a column( name or idx ), retrieve the appropriate value""" 
    …… 
    165163        else: 
    166164            print ("rs.value Type Failed col:%s  row:%s" % (type(col), type(row))) 
    167     
     165 
    168166    def json_out(self): 
    169167        json = "[%s]" % ',\r\n'. join( 

  • timingandestimationplugin/branches/trac0.10/timingandestimationplugin/reports.py

    r3520 r3785  
    99    "uuid":"b24f08c0-d41f-4c63-93a5-25e18a8513c2", 
    1010    "title":"Ticket Work Summary", 
    11     "version":17, 
     11    "version":18, 
    1212    "sql":""" 
    1313SELECT __ticket__ as __group__, __style__, ticket, 
    …… 
    2222  FROM ticket_change 
    2323  JOIN ticket t on t.id = ticket_change.ticket 
    24   LEFT JOIN ticket_custom as billable on billable.ticket = t.id  
     24  LEFT JOIN ticket_custom as billable on billable.ticket = t.id 
    2525    and billable.name = 'billable' 
    2626  WHERE field = 'hours' and 
    27     t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
     27    t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
    2828      AND billable.value in ($BILLABLE, $UNBILLABLE) 
    2929      AND ticket_change.time >= $STARTDATE 
    3030      AND ticket_change.time < $ENDDATE 
    31    
    32   UNION  
    33    
     31 
     32  UNION 
     33 
    3434  SELECT 'background-color:#DFE;' as __style__, 
    3535    'Total work done on the ticket in the selected time period ' as author, 
    …… 
    4444    and billable.name = 'billable' 
    4545  WHERE field = 'hours' and 
    46     t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
     46    t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
    4747      AND billable.value in ($BILLABLE, $UNBILLABLE) 
    4848      AND ticket_change.time >= $STARTDATE 
    …… 
    5757    "uuid":"af13564f-0e36-4a17-96c0-632dc68d8d14", 
    5858    "title":"Milestone Work Summary", 
    59     "version":14, 
     59    "version":15, 
    6060    "sql":""" 
    6161 
    62 SELECT  
     62SELECT 
    6363  milestone as __group__, __style__,  ticket, summary, newvalue as Work_added, 
    6464  time, _ord 
    …… 
    7070  FROM ticket_change 
    7171  JOIN ticket t on t.id = ticket_change.ticket 
    72   LEFT JOIN ticket_custom as billable on billable.ticket = t.id  
     72  LEFT JOIN ticket_custom as billable on billable.ticket = t.id 
    7373    and billable.name = 'billable' 
    7474  WHERE field = 'hours' and 
    75     t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
     75    t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
    7676      AND billable.value in ($BILLABLE, $UNBILLABLE) 
    7777      AND ticket_change.time >= $STARTDATE 
    7878      AND ticket_change.time < $ENDDATE 
    7979  GROUP BY t.milestone, t.id, t.summary 
    80    
    81   UNION  
    82    
     80 
     81  UNION 
     82 
    8383  SELECT 'background-color:#DFE;' as __style__, NULL as ticket, 
    8484    sum( CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0 
    …… 
    8787  FROM ticket_change 
    8888  JOIN ticket t on t.id = ticket_change.ticket 
    89   LEFT JOIN ticket_custom as billable on billable.ticket = t.id  
     89  LEFT JOIN ticket_custom as billable on billable.ticket = t.id 
    9090    and billable.name = 'billable' 
    9191  WHERE field = 'hours' and 
    92     t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
     92    t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
    9393      AND billable.value in ($BILLABLE, $UNBILLABLE) 
    9494      AND ticket_change.time >= $STARTDATE 
    …… 
    102102    """ 
    103103    },#END Milestone work summary 
    104          
     104 
    105105    { 
    106106    "uuid":"7bd4b0ce-da6d-4b11-8be3-07e65b540d99", 
    107107    "title":"Developer Work Summary", 
    108     "version":14, 
     108    "version":15, 
    109109    "sql":""" 
    110110SELECT author as __group__,__style__, ticket, summary, 
    …… 
    118118  FROM ticket_change 
    119119  JOIN ticket t on t.id = ticket_change.ticket 
    120   LEFT JOIN ticket_custom as billable on billable.ticket = t.id  
     120  LEFT JOIN ticket_custom as billable on billable.ticket = t.id 
    121121    and billable.name = 'billable' 
    122122  WHERE field = 'hours' and 
    123     t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
     123    t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
    124124      AND billable.value in ($BILLABLE, $UNBILLABLE) 
    125125      AND ticket_change.time >= $STARTDATE 
    126126      AND ticket_change.time < $ENDDATE 
    127        
    128   UNION  
    129    
     127 
     128  UNION 
     129 
    130130  SELECT 'background-color:#DFE;' as __style__, author, NULL as ticket, 
    131131    Null as summary, 
    …… 
    135135  FROM ticket_change 
    136136  JOIN ticket t on t.id = ticket_change.ticket 
    137   LEFT JOIN ticket_custom as billable on billable.ticket = t.id  
     137  LEFT JOIN ticket_custom as billable on billable.ticket = t.id 
    138138    and billable.name = 'billable' 
    139139  WHERE field = 'hours' and 
    140     t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
     140    t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
    141141      AND billable.value in ($BILLABLE, $UNBILLABLE) 
    142142      AND ticket_change.time >= $STARTDATE 
    …… 
    145145)  as tbl 
    146146ORDER BY author,  _ord ASC, time 
    147      
     147 
    148148    """ 
    149149    },#END Hours Per Developer 
    150150] 
    151 th_version =13 
     151th_version =14 
    152152ticket_hours_reports = [ 
    153153{ 
    …… 
    166166      ELSE CAST( EstimatedHours.value AS DECIMAL ) END as Estimated_work, 
    167167    CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
    168       ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work,  
    169     CASE WHEN billable.value = 1 THEN 'Y' else 'N' END as billable, 
     168      ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work, 
     169    CASE WHEN billable.value = '1' THEN 'Y' else 'N' END as billable, 
    170170    time AS created, changetime AS modified,         -- ## Dates are formatted 
    171171    description AS _description_,                    -- ## Uses a full row 
    172172    changetime AS _changetime, 
    173173    reporter AS _reporter 
    174     ,0 as _ord                                         
    175          
     174    ,0 as _ord 
     175 
    176176    FROM ticket as t 
    177177    JOIN enum as p ON p.name=t.priority AND p.type='priority' 
    178      
     178 
    179179  LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' 
    180180        AND EstimatedHours.Ticket = t.Id 
    …… 
    183183  LEFT JOIN ticket_custom as billable ON billable.name='billable' 
    184184        AND billable.Ticket = t.Id 
    185    
    186     WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
    187       AND billable.value in ($BILLABLE, $UNBILLABLE) 
    188      
    189    
    190   UNION  
    191    
     185 
     186    WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
     187      AND billable.value in ($BILLABLE, $UNBILLABLE) 
     188 
     189 
     190  UNION 
     191 
    192192  SELECT '1' AS __color__, 
    193193         'background-color:#DFE;' as __style__, 
    194          NULL as ticket, 'Total' AS summary,              
     194         NULL as ticket, 'Total' AS summary, 
    195195         NULL as component,NULL as version, NULL as severity, NULL as  milestone, 
    196196         'Time Remaining: ' as status, 
    197197         CAST( 
    198198       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
    199          ELSE CAST( EstimatedHours.value AS DECIMAL ) END) -  
     199         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) - 
    200200       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
    201201         ELSE CAST( totalhours.value AS DECIMAL ) END) 
    …… 
    207207         NULL as billable, 
    208208         NULL as created, NULL as modified,         -- ## Dates are formatted 
    209    
     209 
    210210         NULL AS _description_, 
    211211         NULL AS _changetime, 
    …… 
    214214    FROM ticket as t 
    215215    JOIN enum as p ON p.name=t.priority AND p.type='priority' 
    216      
     216 
    217217  LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' 
    218218        AND EstimatedHours.Ticket = t.Id 
    219    
     219 
    220220  LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' 
    221221        AND totalhours.Ticket = t.Id 
    222    
     222 
    223223  LEFT JOIN ticket_custom as billable ON billable.name='billable' 
    224224        AND billable.Ticket = t.Id 
    225      
    226     WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
     225 
     226    WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
    227227      AND billable.value in ($BILLABLE, $UNBILLABLE) 
    228228)  as tbl 
    …… 
    253253       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
    254254      ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work, 
    255        CASE WHEN billable.value = 1 THEN 'Y' 
     255       CASE WHEN billable.value = '1' THEN 'Y' 
    256256            else 'N' 
    257257       END as billable, 
    …… 
    260260       changetime AS _changetime, 
    261261       reporter AS _reporter 
    262        ,0 as _ord                                         
    263          
    264   FROM ticket as t 
    265   JOIN enum as p ON p.name=t.priority AND p.type='priority' 
    266    
    267 LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' 
    268       AND EstimatedHours.Ticket = t.Id 
    269 LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' 
    270       AND totalhours.Ticket = t.Id 
    271 LEFT JOIN ticket_custom as billable ON billable.name='billable' 
    272       AND billable.Ticket = t.Id 
    273  
    274   WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
    275     AND billable.value in ($BILLABLE, $UNBILLABLE) 
    276    
    277  
    278 UNION  
     262       ,0 as _ord 
     263 
     264  FROM ticket as t 
     265  JOIN enum as p ON p.name=t.priority AND p.type='priority' 
     266 
     267LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' 
     268      AND EstimatedHours.Ticket = t.Id 
     269LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' 
     270      AND totalhours.Ticket = t.Id 
     271LEFT JOIN ticket_custom as billable ON billable.name='billable' 
     272      AND billable.Ticket = t.Id 
     273 
     274  WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
     275    AND billable.value in ($BILLABLE, $UNBILLABLE) 
     276 
     277 
     278UNION 
    279279 
    280280SELECT '1' AS __color__, 
    281281       'background-color:#DFE;' as __style__, 
    282        NULL as ticket, 'Total' AS summary,              
     282       NULL as ticket, 'Total' AS summary, 
    283283       NULL as component,NULL as version, NULL as severity, NULL as  milestone, 
    284284       'Time Remaining: ' as status, 
    285285       CAST( 
    286286       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
    287          ELSE CAST( EstimatedHours.value AS DECIMAL ) END) -  
     287         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) - 
    288288       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
    289289         ELSE CAST( totalhours.value AS DECIMAL ) END) 
    …… 
    302302  FROM ticket as t 
    303303  JOIN enum as p ON p.name=t.priority AND p.type='priority' 
    304    
    305 LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' 
    306       AND EstimatedHours.Ticket = t.Id 
    307  
    308 LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' 
    309       AND totalhours.Ticket = t.Id 
    310  
    311 LEFT JOIN ticket_custom as billable ON billable.name='billable' 
    312       AND billable.Ticket = t.Id 
    313    
    314   WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
     304 
     305LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' 
     306      AND EstimatedHours.Ticket = t.Id 
     307 
     308LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' 
     309      AND totalhours.Ticket = t.Id 
     310 
     311LEFT JOIN ticket_custom as billable ON billable.name='billable' 
     312      AND billable.Ticket = t.Id 
     313 
     314  WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
    315315    AND billable.value in ($BILLABLE, $UNBILLABLE) 
    316316)  as tbl 
    …… 
    318318    """ 
    319319    }, 
    320 #END Ticket Hours  
     320#END Ticket Hours 
    321321 
    322322    { 
    …… 
    339339       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
    340340         ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work, 
    341        CASE WHEN billable.value = 1 THEN 'Y' 
     341       CASE WHEN billable.value = '1' THEN 'Y' 
    342342            else 'N' 
    343343       END as billable, 
    …… 
    346346       changetime AS _changetime, 
    347347       reporter AS _reporter 
    348        ,0 as _ord                                         
    349          
    350   FROM ticket as t 
    351   JOIN enum as p ON p.name=t.priority AND p.type='priority' 
    352    
    353 LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' 
    354       AND EstimatedHours.Ticket = t.Id 
    355 LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' 
    356       AND totalhours.Ticket = t.Id 
    357 LEFT JOIN ticket_custom as billable ON billable.name='billable' 
    358       AND billable.Ticket = t.Id 
    359  
    360   WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
    361     AND billable.value in ($BILLABLE, $UNBILLABLE) 
    362    
    363  
    364 UNION  
     348       ,0 as _ord 
     349 
     350  FROM ticket as t 
     351  JOIN enum as p ON p.name=t.priority AND p.type='priority' 
     352 
     353LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' 
     354      AND EstimatedHours.Ticket = t.Id 
     355LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' 
     356      AND totalhours.Ticket = t.Id 
     357LEFT JOIN ticket_custom as billable ON billable.name='billable' 
     358      AND billable.Ticket = t.Id 
     359 
     360  WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
     361    AND billable.value in ($BILLABLE, $UNBILLABLE) 
     362 
     363 
     364UNION 
    365365 
    366366SELECT '1' AS __color__, 
    367367       t.component AS __group__, 
    368368       'background-color:#DFE;' as __style__, 
    369        NULL as ticket, 'Total work' AS summary,              
     369       NULL as ticket, 'Total work' AS summary, 
    370370       t.component as __component__, NULL as version, NULL as severity, 
    371371       NULL as  milestone, 'Time Remaining: ' as status, 
    372372       CAST( 
    373373       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
    374          ELSE CAST( EstimatedHours.value AS DECIMAL ) END) -  
     374         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) - 
    375375       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
    376376         ELSE CAST( totalhours.value AS DECIMAL ) END) 
    …… 
    390390  FROM ticket as t 
    391391  JOIN enum as p ON p.name=t.priority AND p.type='priority' 
    392    
    393 LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' 
    394       AND EstimatedHours.Ticket = t.Id 
    395  
    396 LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' 
    397       AND totalhours.Ticket = t.Id 
    398  
    399 LEFT JOIN ticket_custom as billable ON billable.name='billable' 
    400       AND billable.Ticket = t.Id 
    401    
    402   WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
     392 
     393LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' 
     394      AND EstimatedHours.Ticket = t.Id 
     395 
     396LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' 
     397      AND totalhours.Ticket = t.Id 
     398 
     399LEFT JOIN ticket_custom as billable ON billable.name='billable' 
     400      AND billable.Ticket = t.Id 
     401 
     402  WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
    403403    AND billable.value in ($BILLABLE, $UNBILLABLE) 
    404404  GROUP BY t.component 
    …… 
    408408    }, 
    409409# END Ticket Hours  GROUPED BY COMPONENT 
    410      
     410 
    411411    { 
    412412    "uuid":"7816f034-a174-4a94-aed6-358fb648b2fc", 
    …… 
    428428       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
    429429         ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work, 
    430        CASE WHEN billable.value = 1 THEN 'Y' else 'N' END as billable, 
     430       CASE WHEN billable.value = '1' THEN 'Y' else 'N' END as billable, 
    431431       time AS created, changetime AS modified,         -- ## Dates are formatted 
    432432       description AS _description_,                    -- ## Uses a full row 
    433433       changetime AS _changetime, 
    434434       reporter AS _reporter 
    435        ,0 as _ord                                         
    436          
    437   FROM ticket as t 
    438   JOIN enum as p ON p.name=t.priority AND p.type='priority' 
    439    
    440 LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' 
    441       AND EstimatedHours.Ticket = t.Id 
    442 LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' 
    443       AND totalhours.Ticket = t.Id 
    444 LEFT JOIN ticket_custom as billable ON billable.name='billable' 
    445       AND billable.Ticket = t.Id 
    446  
    447   WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
    448     AND billable.value in ($BILLABLE, $UNBILLABLE) 
    449    
    450  
    451 UNION  
     435       ,0 as _ord 
     436 
     437  FROM ticket as t 
     438  JOIN enum as p ON p.name=t.priority AND p.type='priority' 
     439 
     440LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' 
     441      AND EstimatedHours.Ticket = t.Id 
     442LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' 
     443      AND totalhours.Ticket = t.Id 
     444LEFT JOIN ticket_custom as billable ON billable.name='billable' 
     445      AND billable.Ticket = t.Id 
     446 
     447  WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
     448    AND billable.value in ($BILLABLE, $UNBILLABLE) 
     449 
     450 
     451UNION 
    452452 
    453453SELECT '1' AS __color__, 
    454454       t.component AS __group__, 
    455455       'background-color:#DFE;' as __style__, 
    456        NULL as ticket, 'Total work' AS summary,              
     456       NULL as ticket, 'Total work' AS summary, 
    457457       t.component as __component__, NULL as version, NULL as severity, 
    458458       NULL as  milestone, 'Time Remaining: ' as status, 
    459459       CAST( 
    460460       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
    461          ELSE CAST( EstimatedHours.value AS DECIMAL ) END) -  
     461         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) - 
    462462       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
    463463         ELSE CAST( totalhours.value AS DECIMAL ) END) 
    …… 
    476476  FROM ticket as t 
    477477  JOIN enum as p ON p.name=t.priority AND p.type='priority' 
    478    
    479 LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' 
    480       AND EstimatedHours.Ticket = t.Id 
    481  
    482 LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' 
    483       AND totalhours.Ticket = t.Id 
    484  
    485 LEFT JOIN ticket_custom as billable ON billable.name='billable' 
    486       AND billable.Ticket = t.Id 
    487    
    488   WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
     478 
     479LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' 
     480      AND EstimatedHours.Ticket = t.Id 
     481 
     482LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' 
     483      AND totalhours.Ticket = t.Id 
     484 
     485LEFT JOIN ticket_custom as billable ON billable.name='billable' 
     486      AND billable.Ticket = t.Id 
     487 
     488  WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
    489489    AND billable.value in ($BILLABLE, $UNBILLABLE) 
    490490  GROUP BY t.component 
    …… 
    513513       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
    514514         ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work, 
    515        CASE WHEN billable.value = 1 THEN 'Y' 
     515       CASE WHEN billable.value = '1' THEN 'Y' 
    516516            else 'N' 
    517517       END as billable, 
    …… 
    519519       description AS _description_,                    -- ## Uses a full row 
    520520       changetime AS _changetime, 
    521        reporter AS _reporter, 0 as _ord                                         
    522          
    523   FROM ticket as t 
    524   JOIN enum as p ON p.name=t.priority AND p.type='priority' 
    525    
    526 LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' 
    527       AND EstimatedHours.Ticket = t.Id 
    528 LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' 
    529       AND totalhours.Ticket = t.Id 
    530 LEFT JOIN ticket_custom as billable ON billable.name='billable' 
    531       AND billable.Ticket = t.Id 
    532  
    533   WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
    534     AND billable.value in ($BILLABLE, $UNBILLABLE) 
    535    
    536  
    537 UNION  
     521       reporter AS _reporter, 0 as _ord 
     522 
     523  FROM ticket as t 
     524  JOIN enum as p ON p.name=t.priority AND p.type='priority' 
     525 
     526LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' 
     527      AND EstimatedHours.Ticket = t.Id 
     528LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' 
     529      AND totalhours.Ticket = t.Id 
     530LEFT JOIN ticket_custom as billable ON billable.name='billable' 
     531      AND billable.Ticket = t.Id 
     532 
     533  WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
     534    AND billable.value in ($BILLABLE, $UNBILLABLE) 
     535 
     536 
     537UNION 
    538538 
    539539SELECT '1' AS __color__, 
    540540       t.milestone AS __group__, 
    541541       'background-color:#DFE;' as __style__, 
    542        NULL as ticket, 'Total work' AS summary,              
     542       NULL as ticket, 'Total work' AS summary, 
    543543       NULL as component,NULL as version, NULL as severity, 
    544544       t.milestone as  __milestone__, 'Time Remaining: ' as status, 
    545545       CAST( 
    546546       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
    547          ELSE CAST( EstimatedHours.value AS DECIMAL ) END) -  
     547         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) - 
    548548       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
    549549         ELSE CAST( totalhours.value AS DECIMAL ) END) 
    …… 
    562562  FROM ticket as t 
    563563  JOIN enum as p ON p.name=t.priority AND p.type='priority' 
    564    
    565 LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' 
    566       AND EstimatedHours.Ticket = t.Id 
    567  
    568 LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' 
    569       AND totalhours.Ticket = t.Id 
    570  
    571 LEFT JOIN ticket_custom as billable ON billable.name='billable' 
    572       AND billable.Ticket = t.Id 
    573    
    574   WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
     564 
     565LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' 
     566      AND EstimatedHours.Ticket = t.Id 
     567 
     568LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' 
     569      AND totalhours.Ticket = t.Id 
     570 
     571LEFT JOIN ticket_custom as billable ON billable.name='billable' 
     572      AND billable.Ticket = t.Id 
     573 
     574  WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
    575575    AND billable.value in ($BILLABLE, $UNBILLABLE) 
    576576  GROUP BY t.milestone 
    …… 
    599599       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
    600600         ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work, 
    60