Changeset 3784

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

closes #3131

closes #3074

T&E 0.6.6

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 week typed databases)

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

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

    r3521 r3784  
    88      description='Plugin to make Trac support time estimation and tracking', 
    99      keywords='trac plugin estimation timetracking', 
    10       version='0.6.5', 
     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.11/timingandestimationplugin/api.py

    r3362 r3784  
    22import dbhelper 
    33import time 
    4 from tande_filters import *  
     4from tande_filters import * 
    55from ticket_daemon import * 
    66from usermanual import * 
    …… 
    1010from trac.env import IEnvironmentSetupParticipant 
    1111from trac.perm import IPermissionRequestor, PermissionSystem 
    12 from webui import *  
     12from webui import * 
    1313from ticket_webui import * 
    1414from query_webui import * 
    …… 
    2020## report columns 
    2121## id|author|title|query|description 
    22     
     22 
    2323class TimeTrackingSetupParticipant(Component): 
    2424    """ This is the config that must be there for this plugin to work: 
    25          
     25 
    2626        [ticket-custom] 
    2727        totalhours = text 
    …… 
    3636        hours.value = 0 
    3737        hours.label = Hours to Add 
    38          
     38 
    3939        estimatedhours = text 
    4040        estimatedhours.value = 0 
    4141        estimatedhours.label = Estimated Hours? 
    42          
     42 
    4343        """ 
    4444    implements(IEnvironmentSetupParticipant) 
    …… 
    4646    db_version = None 
    4747    db_installed_version = None 
    48      
     48 
    4949    """Extension point interface for components that need to participate in the 
    5050    creation and upgrading of Trac environments, for example to create 
    …… 
    5252    def __init__(self): 
    5353        # Setup logging 
    54         dbhelper.mylog = self.log 
    55         dbhelper.env = self.env 
    5654        self.statuses_key = 'T&E-statuses' 
    5755        self.db_version_key = 'TimingAndEstimationPlugin_Db_Version' 
    5856        self.db_version = 6 
    5957        # Initialise database schema version tracking. 
    60         self.db_installed_version = dbhelper.get_system_value(\ 
     58        self.db_installed_version = dbhelper.get_system_value(self, \ 
    6159            self.db_version_key) or 0 
    6260 
    …… 
    6563        if self.environment_needs_upgrade(None): 
    6664            self.upgrade_environment(None) 
    67              
     65 
    6866 
    6967    def system_needs_upgrade(self): 
    7068        return self.db_installed_version < self.db_version 
    71          
     69 
    7270    def do_db_upgrade(self): 
    7371        # Legacy support hack (supports upgrades from 0.1.6 to 0.1.7) 
    7472        if self.db_installed_version == 0: 
    75             bill_date = dbhelper.db_table_exists('bill_date'); 
    76             report_version = dbhelper.db_table_exists('report_version'); 
     73            bill_date = dbhelper.db_table_exists(self, 'bill_date'); 
     74            report_version = dbhelper.db_table_exists(self, 'report_version'); 
    7775            if bill_date and report_version: 
    7876                self.db_installed_version = 1 
    7977        # End Legacy support hack 
    8078 
    81          
     79 
    8280        if self.db_installed_version < 1: 
    8381            print "Creating bill_date table" 
    …… 
    8987            ); 
    9088            """ 
    91             dbhelper.execute_non_query( sql) 
    92  
    93              
     89            dbhelper.execute_non_query(self, sql) 
     90 
     91 
    9492            print "Creating report_version table" 
    9593            sql = """ 
    …… 
    10098            ); 
    10199            """ 
    102             dbhelper.execute_non_query(sql) 
     100            dbhelper.execute_non_query(self, sql) 
    103101 
    104102        if self.db_installed_version < 4: 
    …… 
    107105            ALTER TABLE report_version ADD COLUMN tags varchar(1024) null; 
    108106            """ 
    109             dbhelper.execute_non_query(sql) 
     107            dbhelper.execute_non_query(self, sql) 
    110108 
    111109        if self.db_installed_version < 5: 
    …… 
    116114            sql = "DELETE FROM report " \ 
    117115                  "WHERE author=%s AND id IN (SELECT report FROM report_version)" 
    118             dbhelper.execute_non_query(sql, 'Timing and Estimation Plugin') 
     116            dbhelper.execute_non_query(self, sql, 'Timing and Estimation Plugin') 
    119117 
    120118            sql = "DROP TABLE report_version" 
    121             dbhelper.execute_non_query(sql) 
    122              
    123         #version 6 upgraded reports   
    124                  
     119            dbhelper.execute_non_query(self, sql) 
     120 
     121        #version 6 upgraded reports 
     122 
    125123        # This statement block always goes at the end this method 
    126         dbhelper.set_system_value(self.db_version_key, self.db_version) 
     124        dbhelper.set_system_value(self, self.db_version_key, self.db_version) 
    127125        self.db_installed_version = self.db_version 
    128      
     126 
    129127    def reports_need_upgrade(self): 
    130128        mgr = CustomReportManager(self.env, self.log) 
    …… 
    151149            self.log.debug ("T&E needs upgrades for the following reports: %s" % diff ) 
    152150        return len(diff) > 0 
    153          
     151 
    154152    def do_reports_upgrade(self, force=False): 
    155153        self.log.debug( "Beginning Reports Upgrade"); 
    156154        mgr = CustomReportManager(self.env, self.log) 
    157         statuses = get_statuses(self.config, self.env) 
     155        statuses = get_statuses(self) 
    158156        stat_vars = status_variables(statuses) 
    159          
     157 
    160158        for report_group in all_reports: 
    161159            rlist = report_group["reports"] 
    …… 
    164162                title = report["title"] 
    165163                new_version = report["version"] 
    166                  
     164 
    167165                sql = report["sql"].replace('#STATUSES#', stat_vars) 
    168166                mgr.add_report(report["title"], "Timing and Estimation Plugin", \ 
    …… 
    175173        ticket_custom = "ticket-custom" 
    176174        return not ( self.config.get( ticket_custom, "totalhours" ) and \ 
    177                       
     175 
    178176                     #self.config.get( ticket_custom, "billable" ) and \ 
    179177                     #self.config.get( ticket_custom, "billable.order") and \ 
    …… 
    187185 
    188186                     self.config.get( ticket_custom, "estimatedhours")) 
    189      
     187 
    190188    def do_ticket_field_upgrade(self): 
    191189        ticket_custom = "ticket-custom" 
    192          
     190 
    193191        self.config.set(ticket_custom,"totalhours", "text") 
    194192        if not self.config.get( ticket_custom, "totalhours.order") : 
    195193            self.config.set(ticket_custom,"totalhours.order", "4") 
    196194        self.config.set(ticket_custom,"totalhours.value", "0") 
    197         self.config.set(ticket_custom,"totalhours.label", "Total Hours")                 
     195        self.config.set(ticket_custom,"totalhours.label", "Total Hours") 
    198196 
    199197        self.config.set(ticket_custom,"billable", "checkbox") 
    …… 
    202200            self.config.set(ticket_custom,"billable.order", "3") 
    203201        self.config.set(ticket_custom,"billable.label", "Billable?") 
    204              
     202 
    205203        self.config.set(ticket_custom,"hours", "text") 
    206204        self.config.set(ticket_custom,"hours.value", "0") 
    …… 
    208206            self.config.set(ticket_custom,"hours.order", "2") 
    209207        self.config.set(ticket_custom,"hours.label", "Add Hours to Ticket") 
    210              
     208 
    211209        self.config.set(ticket_custom,"estimatedhours", "text") 
    212210        self.config.set(ticket_custom,"estimatedhours.value", "0") 
    …… 
    218216 
    219217    def needs_user_man(self): 
    220         maxversion = dbhelper.get_scalar("SELECT MAX(version) FROM wiki WHERE name like %s", 0, 
     218        maxversion = dbhelper.get_scalar(self, "SELECT MAX(version) FROM wiki WHERE name like %s", 0, 
    221219                                         user_manual_wiki_title) 
    222220        if (not maxversion) or maxversion < user_manual_version: 
    …… 
    231229        VALUES ( %s, %s, %s, 'Timing and Estimation Plugin', '127.0.0.1', %s,'',0) 
    232230        """ 
    233         dbhelper.execute_non_query(sql, 
     231        dbhelper.execute_non_query(self, sql, 
    234232                                   user_manual_wiki_title, 
    235233                                   user_manual_version, 
    236234                                   when, 
    237235                                   user_manual_content) 
    238              
    239          
     236 
     237 
    240238    def environment_needs_upgrade(self, db): 
    241239        """Called when Trac checks whether the environment needs to be upgraded. 
    242          
     240 
    243241        Should return `True` if this participant needs an upgrade to be 
    244242        performed, `False` otherwise. 
    …… 
    255253               (self.have_statuses_changed()) or \ 
    256254               (self.ticket_fields_need_upgrade()) or \ 
    257                (self.needs_user_man())  
    258              
     255               (self.needs_user_man()) 
     256 
    259257    def upgrade_environment(self, db): 
    260258        """Actually perform an environment upgrade. 
    261          
     259 
    262260        Implementations of this method should not commit any database 
    263261        transactions. This is done implicitly after all participants have 
    …… 
    272270        p("Upgrading reports") 
    273271        self.do_reports_upgrade(force=self.have_statuses_changed()) 
    274          
     272 
    275273        #make sure we upgrade the statuses string so that we dont need to always rebuild the 
    276274        # reports 
    277         stats = get_statuses(self.config, self.env) 
     275        stats = get_statuses(self) 
    278276        val = ','.join(list(stats)) 
    279         dbhelper.set_system_value(self.statuses_key, val) 
    280          
     277        dbhelper.set_system_value(self, self.statuses_key, val) 
     278 
    281279        if self.ticket_fields_need_upgrade(): 
    282280            p("Upgrading fields") 
    …… 
    292290        if we have different ones, throw return true 
    293291        """ 
    294         s = dbhelper.get_system_value(self.statuses_key) 
     292        s = dbhelper.get_system_value(self, self.statuses_key) 
    295293        if not s: 
    296294            return True 
    297         sys_stats = get_statuses(self.config, self.env) 
     295        sys_stats = get_statuses(self) 
    298296        s = s.split(',') 
    299297        sys_stats.symmetric_difference_update(s) 

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

    r3119 r3784  
    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.11/timingandestimationplugin/reports.py

    r3521 r3784  
    99    "uuid":"b24f08c0-d41f-4c63-93a5-25e18a8513c2", 
    1010    "title":"Ticket Work Summary", 
    11     "version":19, 
     11    "version":20, 
    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 (#STATUSES#)  
     27    t.status IN (#STATUSES#) 
    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, 
    …… 
    4141  FROM ticket_change 
    4242  JOIN ticket t on t.id = ticket_change.ticket 
    43   LEFT JOIN ticket_custom as billable on billable.ticket = t.id  
     43  LEFT JOIN ticket_custom as billable on billable.ticket = t.id 
    4444    and billable.name = 'billable' 
    4545  WHERE field = 'hours' and 
    46     t.status IN (#STATUSES#)  
     46    t.status IN (#STATUSES#) 
    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":16, 
     59    "version":17, 
    6060    "sql":""" 
    6161 
    62 SELECT  
     62SELECT 
    6363  milestone as __group__, __style__,  ticket, summary, newvalue as Work_added, 
    6464  time  as datetime, _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 (#STATUSES#)  
     75    t.status IN (#STATUSES#) 
    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 (#STATUSES#)  
     92    t.status IN (#STATUSES#) 
    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":16, 
     108    "version":17, 
    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 (#STATUSES#)  
     123    t.status IN (#STATUSES#) 
    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 (#STATUSES#)  
     140    t.status IN (#STATUSES#) 
    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 =14 
     151th_version =15 
    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 (#STATUSES#)  
    187       AND billable.value in ($BILLABLE, $UNBILLABLE) 
    188      
    189    
    190   UNION  
    191    
     185 
     186    WHERE t.status IN (#STATUSES#) 
     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 (#STATUSES#)  
     225 
     226    WHERE t.status IN (#STATUSES#) 
    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 (#STATUSES#)  
    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 (#STATUSES#) 
     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 (#STATUSES#)  
     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 (#STATUSES#) 
    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 (#STATUSES#)  
    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 (#STATUSES#) 
     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 (#STATUSES#)  
     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 (#STATUSES#) 
    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 (#STATUSES#)  
    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 (#STATUSES#) 
     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 (#STATUSES#)  
     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 (#STATUSES#) 
    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 (#STATUSES#)  
    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    &