Changeset 2590

Show
Ignore:
Timestamp:
08/22/07 12:35:23 (1 year ago)
Author:
bobbysmith007
Message:

closes #1697 I was wrong about this, It was just a leftover quirk and has been removed

closes #901 reports now contain time remaining

closes #1958 added case statements all over the place to better support postegres when there are null/empty values

closes #1959 changed the work summary report to include links to the ticket and ticket summaries

Now at version 0.4.9

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

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

    r2538 r2590  
    88      description='Plugin to make Trac support time estimation and tracking', 
    99      keywords='trac plugin estimation timetracking', 
    10       version='0.4.8', 
     10      version='0.4.9', 
    1111      url='http://www.trac-hacks.org/wiki/TimingAndEstimationPlugin', 
    1212      license='http://www.opensource.org/licenses/mit-license.php', 

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

    r2390 r2590  
    5050        dbhelper.mylog = self.log 
    5151        self.db_version_key = 'TimingAndEstimationPlugin_Db_Version' 
    52         self.db_version = 5 
     52        self.db_version = 6 
    5353        self.db_installed_version = None 
    5454 

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

    r2460 r2590  
    99    "uuid":"b24f08c0-d41f-4c63-93a5-25e18a8513c2", 
    1010    "title":"Ticket Work Summary", 
    11     "version":14, 
     11    "version":16, 
    1212    "sql":""" 
    13 SELECT __ticket__ as __group__, __style__, __ticket__, 
     13SELECT __ticket__ as __group__, __style__, ticket, 
    1414newvalue as Work_added, author, time, _ord 
    1515FROM( 
    16   SELECT '' as __style__, author, t.id as __ticket__, 
    17   CAST(newvalue as DECIMAL) as newvalue, ticket_change.time as time, 0 as _ord 
     16  SELECT '' as __style__, author, 
     17  t.summary as __ticket__, 
     18  t.id as ticket, 
     19  CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0 
     20    ELSE CAST( newvalue AS DECIMAL ) END AS newvalue, 
     21  ticket_change.time as time, 0 as _ord 
    1822  FROM ticket_change 
    1923  JOIN ticket t on t.id = ticket_change.ticket 
    …… 
    3034  SELECT 'background-color:#DFE;' as __style__, 
    3135    'Total work done on the ticket in the selected time period ' as author, 
    32     t.id as __ticket__, sum( CAST(newvalue as DECIMAL) ) as newvalue, 
     36    t.summary as __ticket__, 
     37    t.id as ticket, 
     38  SUM( CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0 
     39         ELSE CAST( newvalue AS DECIMAL ) END ) as newvalue, 
     40    NULL as time, 1 as _ord 
     41  FROM ticket_change 
     42  JOIN ticket t on t.id = ticket_change.ticket 
     43  LEFT JOIN ticket_custom as billable on billable.ticket = t.id 
     44    and billable.name = 'billable' 
     45  WHERE field = 'hours' and 
     46    t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
     47      AND billable.value in ($BILLABLE, $UNBILLABLE) 
     48      AND ticket_change.time >= $STARTDATE 
     49      AND ticket_change.time < $ENDDATE 
     50  GROUP By t.id 
     51)  as tbl 
     52ORDER BY __ticket__, _ord ASC, time ASC 
     53 
     54    """ 
     55    },#END Ticket work summary 
     56        { 
     57    "uuid":"af13564f-0e36-4a17-96c0-632dc68d8d14", 
     58    "title":"Milestone Work Summary", 
     59    "version":14, 
     60    "sql":""" 
     61 
     62SELECT  
     63  milestone as __group__, __style__,  ticket, summary, newvalue as Work_added, 
     64  time, _ord 
     65FROM( 
     66  SELECT '' as __style__, t.id as ticket, 
     67    SUM( CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0 
     68         ELSE CAST( newvalue AS DECIMAL ) END) as newvalue, t.summary as summary, 
     69    MAX(ticket_change.time) as time, t.milestone as milestone, 0 as _ord 
     70  FROM ticket_change 
     71  JOIN ticket t on t.id = ticket_change.ticket 
     72  LEFT JOIN ticket_custom as billable on billable.ticket = t.id  
     73    and billable.name = 'billable' 
     74  WHERE field = 'hours' and 
     75    t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
     76      AND billable.value in ($BILLABLE, $UNBILLABLE) 
     77      AND ticket_change.time >= $STARTDATE 
     78      AND ticket_change.time < $ENDDATE 
     79  GROUP BY t.milestone, t.id, t.summary 
     80   
     81  UNION  
     82   
     83  SELECT 'background-color:#DFE;' as __style__, NULL as ticket, 
     84    sum( CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0 
     85         ELSE CAST( newvalue AS DECIMAL ) END) as newvalue, 'Total work done' as summary, 
     86    NULL as time, t.milestone as milestone, 1 as _ord 
     87  FROM ticket_change 
     88  JOIN ticket t on t.id = ticket_change.ticket 
     89  LEFT JOIN ticket_custom as billable on billable.ticket = t.id  
     90    and billable.name = 'billable' 
     91  WHERE field = 'hours' and 
     92    t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
     93      AND billable.value in ($BILLABLE, $UNBILLABLE) 
     94      AND ticket_change.time >= $STARTDATE 
     95      AND ticket_change.time < $ENDDATE 
     96  GROUP By t.milestone 
     97)  as tbl 
     98ORDER BY milestone,  _ord ASC, ticket, time 
     99 
     100 
     101 
     102    """ 
     103    },#END Milestone work summary 
     104         
     105    { 
     106    "uuid":"7bd4b0ce-da6d-4b11-8be3-07e65b540d99", 
     107    "title":"Developer Work Summary", 
     108    "version":14, 
     109    "sql":""" 
     110SELECT author as __group__,__style__, ticket, summary, 
     111  newvalue as Work_added, time as time, _ord 
     112FROM( 
     113  SELECT '' as __style__, author, t.id  as ticket, 
     114    t.summary as summary, 
     115    CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0 
     116         ELSE CAST( newvalue AS DECIMAL ) END as newvalue, 
     117    ticket_change.time as time, 0 as _ord 
     118  FROM ticket_change 
     119  JOIN ticket t on t.id = ticket_change.ticket 
     120  LEFT JOIN ticket_custom as billable on billable.ticket = t.id  
     121    and billable.name = 'billable' 
     122  WHERE field = 'hours' and 
     123    t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
     124      AND billable.value in ($BILLABLE, $UNBILLABLE) 
     125      AND ticket_change.time >= $STARTDATE 
     126      AND ticket_change.time < $ENDDATE 
     127       
     128  UNION  
     129   
     130  SELECT 'background-color:#DFE;' as __style__, author, NULL as ticket, 
     131    Null as summary, 
     132    SUM( CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0 
     133         ELSE CAST( newvalue AS DECIMAL ) END) as newvalue, 
    33134    NULL as time, 1 as _ord 
    34135  FROM ticket_change 
    …… 
    41142      AND ticket_change.time >= $STARTDATE 
    42143      AND ticket_change.time < $ENDDATE 
    43   GROUP By t.id 
    44 )  as tbl 
    45 ORDER BY __ticket__, _ord ASC, time ASC 
    46  
    47     """ 
    48     },#END Ticket work summary 
    49         { 
    50     "uuid":"af13564f-0e36-4a17-96c0-632dc68d8d14", 
    51     "title":"Milestone Work Summary", 
    52     "version":13, 
    53     "sql":""" 
    54  
    55 SELECT  
    56   milestone as __group__, __style__,  ticket, summary, newvalue as Work_added, 
    57   time, _ord 
    58 FROM( 
    59   SELECT '' as __style__, t.id as ticket, 
    60     SUM(CAST(newvalue as DECIMAL)) as newvalue, t.summary as summary, 
    61     MAX(ticket_change.time) as time, t.milestone as milestone, 0 as _ord 
    62   FROM ticket_change 
    63   JOIN ticket t on t.id = ticket_change.ticket 
    64   LEFT JOIN ticket_custom as billable on billable.ticket = t.id  
    65     and billable.name = 'billable' 
    66   WHERE field = 'hours' and 
    67     t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
    68       AND billable.value in ($BILLABLE, $UNBILLABLE) 
    69       AND ticket_change.time >= $STARTDATE 
    70       AND ticket_change.time < $ENDDATE 
    71   GROUP BY t.milestone, t.id, t.summary 
    72    
    73   UNION  
    74    
    75   SELECT 'background-color:#DFE;' as __style__, NULL as ticket, 
    76     sum(CAST(newvalue as DECIMAL)) as newvalue, 'Total work done' as summary, 
    77     NULL as time, t.milestone as milestone, 1 as _ord 
    78   FROM ticket_change 
    79   JOIN ticket t on t.id = ticket_change.ticket 
    80   LEFT JOIN ticket_custom as billable on billable.ticket = t.id  
    81     and billable.name = 'billable' 
    82   WHERE field = 'hours' and 
    83     t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
    84       AND billable.value in ($BILLABLE, $UNBILLABLE) 
    85       AND ticket_change.time >= $STARTDATE 
    86       AND ticket_change.time < $ENDDATE 
    87   GROUP By t.milestone 
    88 )  as tbl 
    89 ORDER BY milestone,  _ord ASC, ticket, time 
    90  
    91  
    92  
    93     """ 
    94     },#END Milestone work summary 
    95          
    96     { 
    97     "uuid":"7bd4b0ce-da6d-4b11-8be3-07e65b540d99", 
    98     "title":"Developer Work Summary", 
    99     "version":13, 
    100     "sql":""" 
    101 SELECT author as __group__,__style__, ticket, 
    102   newvalue as Work_added, time as time, _ord 
    103 FROM( 
    104   SELECT '' as __style__, author, cast(t.id as text) as ticket, 
    105     CAST(newvalue as DECIMAL) as newvalue, ticket_change.time as time, 0 as _ord 
    106   FROM ticket_change 
    107   JOIN ticket t on t.id = ticket_change.ticket 
    108   LEFT JOIN ticket_custom as billable on billable.ticket = t.id  
    109     and billable.name = 'billable' 
    110   WHERE field = 'hours' and 
    111     t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
    112       AND billable.value in ($BILLABLE, $UNBILLABLE) 
    113       AND ticket_change.time >= $STARTDATE 
    114       AND ticket_change.time < $ENDDATE 
    115        
    116   UNION  
    117    
    118   SELECT 'background-color:#DFE;' as __style__, author, NULL as ticket, 
    119     sum(CAST(newvalue as DECIMAL)) as newvalue, NULL as time, 1 as _ord 
    120   FROM ticket_change 
    121   JOIN ticket t on t.id = ticket_change.ticket 
    122   LEFT JOIN ticket_custom as billable on billable.ticket = t.id  
    123     and billable.name = 'billable' 
    124   WHERE field = 'hours' and 
    125     t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)  
    126       AND billable.value in ($BILLABLE, $UNBILLABLE) 
    127       AND ticket_change.time >= $STARTDATE 
    128       AND ticket_change.time < $ENDDATE 
    129144  GROUP By author 
    130145)  as tbl 
    …… 
    134149    },#END Hours Per Developer 
    135150] 
     151th_version =12 
    136152ticket_hours_reports = [ 
    137153{ 
    138154    "uuid":"8d785cdb-dcf5-43c9-b2a6-216997b0011a", 
    139155    "title": "Ticket Hours", 
    140     "version":10, 
     156    "version":th_version, 
    141157    "sql": """ 
    142158SELECT __color__, __style__, ticket, summary, component ,version, severity, 
    …… 
    147163    t.id AS ticket, summary AS summary,             -- ## Break line here 
    148164    component,version, severity, milestone, status, owner, 
    149     CAST(EstimatedHours.value as DECIMAL) as Estimated_work, 
    150     CAST(totalhours.value as DECIMAL) as Total_work,  
     165    CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     166      ELSE CAST( EstimatedHours.value AS DECIMAL ) END as Estimated_work, 
     167    CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     168      ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work,  
    151169    CASE WHEN billable.value = 1 THEN 'Y' else 'N' END as billable, 
    152170    time AS created, changetime AS modified,         -- ## Dates are formatted 
    …… 
    175193         'background-color:#DFE;' as __style__, 
    176194         NULL as ticket, 'Total' AS summary,              
    177          NULL as component,NULL as version, NULL as severity, NULL as  milestone, NULL as status, NULL as owner, 
    178          SUM(CAST(EstimatedHours.value as DECIMAL)) as Estimated_work, 
    179          SUM(CAST(totalhours.value as DECIMAL)) as Total_work, 
     195         NULL as component,NULL as version, NULL as severity, NULL as  milestone, 
     196         'Time Remaining: ' as status, 
     197         CAST( 
     198       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     199         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) -  
     200       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     201         ELSE CAST( totalhours.value AS DECIMAL ) END) 
     202         AS VARCHAR(1024))  as owner, 
     203         SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     204      ELSE CAST( EstimatedHours.value AS DECIMAL ) END) as Estimated_work, 
     205         SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     206      ELSE CAST( totalhours.value AS DECIMAL ) END) as Total_work, 
    180207         NULL as billable, 
    181208         NULL as created, NULL as modified,         -- ## Dates are formatted 
    …… 
    203230    """ 
    204231    }, 
    205 #END Ticket Hours  
     232#END Ticket Hours 
    206233{ 
    207234    "uuid":"71e7c36d-e512-4d0b-b499-087d4d20ff0b", 
    208235    "title": "Ticket Hours with Description", 
    209     "version":11, 
     236    "version":th_version, 
    210237    "sql": """ 
    211238SELECT __color__,  __style__,  ticket, summary, component ,version, severity, 
    …… 
    222249       t.id AS ticket, summary AS summary,             -- ## Break line here 
    223250       component,version, severity, milestone, status, owner, 
    224        CAST(EstimatedHours.value as DECIMAL) as Estimated_work, 
    225        CAST(totalhours.value as DECIMAL) as Total_work, 
     251       CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     252      ELSE CAST( EstimatedHours.value AS DECIMAL ) END as Estimated_work, 
     253       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     254      ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work, 
    226255       CASE WHEN billable.value = 1 THEN 'Y' 
    227256            else 'N' 
    …… 
    252281       'background-color:#DFE;' as __style__, 
    253282       NULL as ticket, 'Total' AS summary,              
    254        NULL as component,NULL as version, NULL as severity, NULL as  milestone, NULL as status, NULL as owner, 
    255        SUM(CAST(EstimatedHours.value as DECIMAL)) as Estimated_work, 
    256        SUM(CAST(totalhours.value as DECIMAL)) as Total_work, 
     283       NULL as component,NULL as version, NULL as severity, NULL as  milestone, 
     284       'Time Remaining: ' as status, 
     285       CAST( 
     286       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     287         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) -  
     288       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     289         ELSE CAST( totalhours.value AS DECIMAL ) END) 
     290         AS VARCHAR(1024))  as owner, 
     291       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     292         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) as Estimated_work, 
     293       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     294         ELSE CAST( totalhours.value AS DECIMAL ) END) as Total_work, 
    257295       NULL as billable, 
    258296       NULL as created, NULL as modified,         -- ## Dates are formatted 
    …… 
    285323    "uuid":"5f33b102-e6a6-47e8-976c-ac7a6794a909", 
    286324    "title":"Ticket Hours Grouped By Component", 
    287     "version":10, 
     325    "version":th_version, 
    288326    "sql": """ 
    289327SELECT __color__, __group__, __style__, ticket, summary, __component__ ,version, 
    …… 
    297335       t.id AS ticket, summary AS summary,             -- ## Break line here 
    298336       component as __component__,version, severity, milestone, status, owner, 
    299        CAST(EstimatedHours.value as DECIMAL) as Estimated_work, 
    300        CAST(totalhours.value as DECIMAL) as Total_work, 
     337       CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     338         ELSE CAST( EstimatedHours.value AS DECIMAL ) END as Estimated_work, 
     339       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     340         ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work, 
    301341       CASE WHEN billable.value = 1 THEN 'Y' 
    302342            else 'N' 
    …… 
    329369       NULL as ticket, 'Total work' AS summary,              
    330370       t.component as __component__, NULL as version, NULL as severity, 
    331        NULL as  milestone, NULL as status, 
    332        NULL as owner, 
    333        SUM(CAST(EstimatedHours.value as DECIMAL)) as Estimated_work, 
    334        SUM(CAST(totalhours.value as DECIMAL)) as Total_work, 
     371       NULL as  milestone, 'Time Remaining: ' as status, 
     372       CAST( 
     373       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     374         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) -  
     375       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     376         ELSE CAST( totalhours.value AS DECIMAL ) END) 
     377         AS VARCHAR(1024))  as owner, 
     378       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     379         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) as Estimated_work, 
     380       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     381         ELSE CAST( totalhours.value AS DECIMAL ) END) as Total_work, 
    335382       NULL as billable, 
    336383       NULL as created, 
    …… 
    365412    "uuid":"7816f034-a174-4a94-aed6-358fb648b2fc", 
    366413    "title":"Ticket Hours Grouped By Component with Description", 
    367     "version":9, 
     414    "version":th_version, 
    368415    "sql": """ 
    369416SELECT __color__, __group__, __style__,  ticket, summary, __component__ , 
    …… 
    377424       t.id AS ticket, summary AS summary,             -- ## Break line here 
    378425       component as __component__, version, severity, milestone, status, owner, 
    379        CAST(EstimatedHours.value as DECIMAL) as Estimated_work, 
    380        CAST(totalhours.value as DECIMAL) as Total_work, 
     426       CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     427         ELSE CAST( EstimatedHours.value AS DECIMAL ) END as Estimated_work, 
     428       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     429         ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work, 
    381430       CASE WHEN billable.value = 1 THEN 'Y' else 'N' END as billable, 
    382431       time AS created, changetime AS modified,         -- ## Dates are formatted 
    …… 
    407456       NULL as ticket, 'Total work' AS summary,              
    408457       t.component as __component__, NULL as version, NULL as severity, 
    409        NULL as  milestone, NULL as status, NULL as owner, 
    410        SUM(CAST(EstimatedHours.value as DECIMAL)) as Estimated_work, 
    411        SUM(CAST(totalhours.value as DECIMAL)) as Total_work, 
     458       NULL as  milestone, 'Time Remaining: ' as status, 
     459       CAST( 
     460       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     461         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) -  
     462       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     463         ELSE CAST( totalhours.value AS DECIMAL ) END) 
     464         AS VARCHAR(1024))  as owner, 
     465       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     466         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) as Estimated_work, 
     467       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     468         ELSE CAST( totalhours.value AS DECIMAL ) END) as Total_work, 
    412469       NULL as billable, 
    413470       NULL as created, NULL as modified,         -- ## Dates are formatted 
    …… 
    440497    "uuid":"03815803-7688-4f3a-8e65-8d254cc1d1fb", 
    441498    "title":"Ticket Hours Grouped By Milestone", 
    442     "version":10, 
     499    "version":th_version, 
    443500    "sql": """ 
    444501SELECT __color__, __group__, __style__,  ticket, summary, component ,version, 
    …… 
    452509       t.id AS ticket, summary AS summary,             -- ## Break line here 
    453510       component,version, severity, milestone as __milestone__, status, owner, 
    454        CAST(EstimatedHours.value as DECIMAL) as Estimated_work, 
    455        CAST(totalhours.value as DECIMAL) as Total_work, 
     511       CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     512         ELSE CAST( EstimatedHours.value AS DECIMAL ) END as Estimated_work, 
     513       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     514         ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work, 
    456515       CASE WHEN billable.value = 1 THEN 'Y' 
    457516            else 'N' 
    …… 
    483542       NULL as ticket, 'Total work' AS summary,              
    484543       NULL as component,NULL as version, NULL as severity, 
    485        t.milestone as  __milestone__, NULL as status, NULL as owner, 
    486        SUM(CAST(EstimatedHours.value as DECIMAL)) as Estimated_work, 
    487        SUM(CAST(totalhours.value as DECIMAL)) as Total_work, 
     544       t.milestone as  __milestone__, 'Time Remaining: ' as status, 
     545       CAST( 
     546       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     547         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) -  
     548       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     549         ELSE CAST( totalhours.value AS DECIMAL ) END) 
     550         AS VARCHAR(1024)) as owner, 
     551       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     552         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) as Estimated_work, 
     553       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     554         ELSE CAST( totalhours.value AS DECIMAL ) END) as Total_work, 
    488555       NULL as billable, 
    489556       NULL as created, NULL as modified,         -- ## Dates are formatted 
    …… 
    516583    "uuid":"040c9025-7641-4d18-96ad-2b26b4095566", 
    517584    "title":"Ticket Hours Grouped By MileStone with Description", 
    518     "version":10, 
     585    "version":th_version, 
    519586    "sql": """ 
    520587SELECT __color__, __group__, __style__,  ticket, summary, component ,version, severity, 
    …… 
    528595       t.id AS ticket, summary AS summary,             -- ## Break line here 
    529596       component,version, severity, milestone as __milestone__, status, owner, 
    530        CAST(EstimatedHours.value as DECIMAL) as Estimated_work, 
    531        CAST(totalhours.value as DECIMAL) as Total_work, 
     597       CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     598         ELSE CAST( EstimatedHours.value AS DECIMAL ) END as Estimated_work, 
     599       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     600         ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work, 
    532601       CASE WHEN billable.value = 1 THEN 'Y' 
    533602            else 'N' 
    …… 
    561630       NULL as component,NULL as version, NULL as severity, 
    562631       t.milestone as __milestone__, 
    563        NULL as status, NULL as owner, 
    564        SUM(CAST(EstimatedHours.value as DECIMAL)) as Estimated_work, 
    565        SUM(CAST(totalhours.value as DECIMAL)) as Total_work, 
     632       'Time Remaining: ' as status, 
     633       CAST( 
     634       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     635         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) -  
     636       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     637         ELSE CAST( totalhours.value AS DECIMAL ) END) 
     638         AS VARCHAR(1024)) as owner, 
     639       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     640         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) as Estimated_work, 
     641       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     642         ELSE CAST( totalhours.value AS DECIMAL ) END) as Total_work, 
    566643       NULL as billable, 
    567644       NULL as created, NULL as modified,         -- ## Dates are formatted 

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

    r2538 r2590  
    88      description='Plugin to make Trac support time estimation and tracking', 
    99      keywords='trac plugin estimation timetracking', 
    10       version='0.4.8', 
     10      version='0.4.9', 
    1111      url='http://www.trac-hacks.org/wiki/TimingAndEstimationPlugin', 
    1212      license='http://www.opensource.org/licenses/mit-license.php', 

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

    r2538 r2590  
    5252        dbhelper.mylog = self.log 
    5353        self.db_version_key = 'TimingAndEstimationPlugin_Db_Version' 
    54         self.db_version = 5 
     54        self.db_version = 6 
    5555        self.db_installed_version = None 
    5656 
    …… 
    127127            sql = "DROP TABLE report_version" 
    128128            dbhelper.execute_non_query(self.env.get_db_cnx(), sql) 
    129  
     129        #version 6 upgraded reports 
    130130                 
    131131        # This statement block always goes at the end this method 
    …… 
    134134                                   sql, self.db_version, self.db_version_key) 
    135135        self.db_installed_version = self.db_version 
    136      
    137  
     136         
    138137    def do_reports_upgrade(self): 
    139138        self.log.debug( "Beginning Reports Upgrade"); 

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

    r2460 r2590  
    99    "uuid":"b24f08c0-d41f-4c63-93a5-25e18a8513c2", 
    1010    "title":"Ticket Work Summary", 
    11     "version":14, 
     11    "version":16, 
    1212    "sql":""" 
    13 SELECT __ticket__ as __group__, __style__, __ticket__, 
     13SELECT __ticket__ as __group__, __style__, ticket, 
    1414newvalue as Work_added, author, time, _ord 
    1515FROM( 
    16   SELECT '' as __style__, author, t.id as __ticket__, 
    17   CAST(newvalue as DECIMAL) as newvalue, ticket_change.time as time, 0 as _ord 
     16  SELECT '' as __style__, author, 
     17  t.summary as __ticket__, 
     18  t.id as ticket, 
     19  CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0 
     20    ELSE CAST( newvalue AS DECIMAL ) END AS newvalue, 
     21  ticket_change.time as time, 0 as _ord 
    1822  FROM ticket_change 
    1923  JOIN ticket t on t.id = ticket_change.ticket 
    …… 
    3034  SELECT 'background-color:#DFE;' as __style__, 
    3135    'Total work done on the ticket in the selected time period ' as author, 
    32     t.id as __ticket__, sum( CAST(newvalue as DECIMAL) ) as newvalue, 
     36    t.summary as __ticket__, 
     37    t.id as ticket, 
     38  SUM( CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0 
     39         ELSE CAST( newvalue AS DECIMAL ) END ) as newvalue, 
    3340    NULL as time, 1 as _ord 
    3441  FROM ticket_change 
    …… 
    5057    "uuid":"af13564f-0e36-4a17-96c0-632dc68d8d14", 
    5158    "title":"Milestone Work Summary", 
    52     "version":13, 
     59    "version":14, 
    5360    "sql":""" 
    5461 
    …… 
    5865FROM( 
    5966  SELECT '' as __style__, t.id as ticket, 
    60     SUM(CAST(newvalue as DECIMAL)) as newvalue, t.summary as summary, 
     67    SUM( CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0 
     68         ELSE CAST( newvalue AS DECIMAL ) END) as newvalue, t.summary as summary, 
    6169    MAX(ticket_change.time) as time, t.milestone as milestone, 0 as _ord 
    6270  FROM ticket_change 
    …… 
    7482   
    7583  SELECT 'background-color:#DFE;' as __style__, NULL as ticket, 
    76     sum(CAST(newvalue as DECIMAL)) as newvalue, 'Total work done' as summary, 
     84    sum( CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0 
     85         ELSE CAST( newvalue AS DECIMAL ) END) as newvalue, 'Total work done' as summary, 
    7786    NULL as time, t.milestone as milestone, 1 as _ord 
    7887  FROM ticket_change 
    …… 
    97106    "uuid":"7bd4b0ce-da6d-4b11-8be3-07e65b540d99", 
    98107    "title":"Developer Work Summary", 
    99     "version":13, 
     108    "version":14, 
    100109    "sql":""" 
    101 SELECT author as __group__,__style__, ticket, 
     110SELECT author as __group__,__style__, ticket, summary, 
    102111  newvalue as Work_added, time as time, _ord 
    103112FROM( 
    104   SELECT '' as __style__, author, cast(t.id as text) as ticket, 
    105     CAST(newvalue as DECIMAL) as newvalue, ticket_change.time as time, 0 as _ord 
     113  SELECT '' as __style__, author, t.id  as ticket, 
     114    t.summary as summary, 
     115    CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0 
     116         ELSE CAST( newvalue AS DECIMAL ) END as newvalue, 
     117    ticket_change.time as time, 0 as _ord 
    106118  FROM ticket_change 
    107119  JOIN ticket t on t.id = ticket_change.ticket 
    …… 
    117129   
    118130  SELECT 'background-color:#DFE;' as __style__, author, NULL as ticket, 
    119     sum(CAST(newvalue as DECIMAL)) as newvalue, NULL as time, 1 as _ord 
     131    Null as summary, 
     132    SUM( CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0 
     133         ELSE CAST( newvalue AS DECIMAL ) END) as newvalue, 
     134    NULL as time, 1 as _ord 
    120135  FROM ticket_change 
    121136  JOIN ticket t on t.id = ticket_change.ticket 
    …… 
    134149    },#END Hours Per Developer 
    135150] 
     151th_version =12 
    136152ticket_hours_reports = [ 
    137153{ 
    138154    "uuid":"8d785cdb-dcf5-43c9-b2a6-216997b0011a", 
    139155    "title": "Ticket Hours", 
    140     "version":10, 
     156    "version":th_version, 
    141157    "sql": """ 
    142158SELECT __color__, __style__, ticket, summary, component ,version, severity, 
    …… 
    147163    t.id AS ticket, summary AS summary,             -- ## Break line here 
    148164    component,version, severity, milestone, status, owner, 
    149     CAST(EstimatedHours.value as DECIMAL) as Estimated_work, 
    150     CAST(totalhours.value as DECIMAL) as Total_work,  
     165    CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     166      ELSE CAST( EstimatedHours.value AS DECIMAL ) END as Estimated_work, 
     167    CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     168      ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work,  
    151169    CASE WHEN billable.value = 1 THEN 'Y' else 'N' END as billable, 
    152170    time AS created, changetime AS modified,         -- ## Dates are formatted 
    …… 
    175193         'background-color:#DFE;' as __style__, 
    176194         NULL as ticket, 'Total' AS summary,              
    177          NULL as component,NULL as version, NULL as severity, NULL as  milestone, NULL as status, NULL as owner, 
    178          SUM(CAST(EstimatedHours.value as DECIMAL)) as Estimated_work, 
    179          SUM(CAST(totalhours.value as DECIMAL)) as Total_work, 
     195         NULL as component,NULL as version, NULL as severity, NULL as  milestone, 
     196         'Time Remaining: ' as status, 
     197         CAST( 
     198       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     199         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) -  
     200       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     201         ELSE CAST( totalhours.value AS DECIMAL ) END) 
     202         AS VARCHAR(1024))  as owner, 
     203         SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     204      ELSE CAST( EstimatedHours.value AS DECIMAL ) END) as Estimated_work, 
     205         SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     206      ELSE CAST( totalhours.value AS DECIMAL ) END) as Total_work, 
    180207         NULL as billable, 
    181208         NULL as created, NULL as modified,         -- ## Dates are formatted 
    …… 
    203230    """ 
    204231    }, 
    205 #END Ticket Hours  
     232#END Ticket Hours 
    206233{ 
    207234    "uuid":"71e7c36d-e512-4d0b-b499-087d4d20ff0b", 
    208235    "title": "Ticket Hours with Description", 
    209     "version":11, 
     236    "version":th_version, 
    210237    "sql": """ 
    211238SELECT __color__,  __style__,  ticket, summary, component ,version, severity, 
    …… 
    222249       t.id AS ticket, summary AS summary,             -- ## Break line here 
    223250       component,version, severity, milestone, status, owner, 
    224        CAST(EstimatedHours.value as DECIMAL) as Estimated_work, 
    225        CAST(totalhours.value as DECIMAL) as Total_work, 
     251       CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     252      ELSE CAST( EstimatedHours.value AS DECIMAL ) END as Estimated_work, 
     253       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     254      ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work, 
    226255       CASE WHEN billable.value = 1 THEN 'Y' 
    227256            else 'N' 
    …… 
    252281       'background-color:#DFE;' as __style__, 
    253282       NULL as ticket, 'Total' AS summary,              
    254        NULL as component,NULL as version, NULL as severity, NULL as  milestone, NULL as status, NULL as owner, 
    255        SUM(CAST(EstimatedHours.value as DECIMAL)) as Estimated_work, 
    256        SUM(CAST(totalhours.value as DECIMAL)) as Total_work, 
     283       NULL as component,NULL as version, NULL as severity, NULL as  milestone, 
     284       'Time Remaining: ' as status, 
     285       CAST( 
     286       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     287         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) -  
     288       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     289         ELSE CAST( totalhours.value AS DECIMAL ) END) 
     290         AS VARCHAR(1024))  as owner, 
     291       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     292         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) as Estimated_work, 
     293       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     294         ELSE CAST( totalhours.value AS DECIMAL ) END) as Total_work, 
    257295       NULL as billable, 
    258296       NULL as created, NULL as modified,         -- ## Dates are formatted 
    …… 
    285323    "uuid":"5f33b102-e6a6-47e8-976c-ac7a6794a909", 
    286324    "title":"Ticket Hours Grouped By Component", 
    287     "version":10, 
     325    "version":th_version, 
    288326    "sql": """ 
    289327SELECT __color__, __group__, __style__, ticket, summary, __component__ ,version, 
    …… 
    297335       t.id AS ticket, summary AS summary,             -- ## Break line here 
    298336       component as __component__,version, severity, milestone, status, owner, 
    299        CAST(EstimatedHours.value as DECIMAL) as Estimated_work, 
    300        CAST(totalhours.value as DECIMAL) as Total_work, 
     337       CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     338         ELSE CAST( EstimatedHours.value AS DECIMAL ) END as Estimated_work, 
     339       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     340         ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work, 
    301341       CASE WHEN billable.value = 1 THEN 'Y' 
    302342            else 'N' 
    …… 
    329369       NULL as ticket, 'Total work' AS summary,              
    330370       t.component as __component__, NULL as version, NULL as severity, 
    331        NULL as  milestone, NULL as status, 
    332        NULL as owner, 
    333        SUM(CAST(EstimatedHours.value as DECIMAL)) as Estimated_work, 
    334        SUM(CAST(totalhours.value as DECIMAL)) as Total_work, 
     371       NULL as  milestone, 'Time Remaining: ' as status, 
     372       CAST( 
     373       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     374         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) -  
     375       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     376         ELSE CAST( totalhours.value AS DECIMAL ) END) 
     377         AS VARCHAR(1024))  as owner, 
     378       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     379         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) as Estimated_work, 
     380       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     381         ELSE CAST( totalhours.value AS DECIMAL ) END) as Total_work, 
    335382       NULL as billable, 
    336383       NULL as created, 
    …… 
    365412    "uuid":"7816f034-a174-4a94-aed6-358fb648b2fc", 
    366413    "title":"Ticket Hours Grouped By Component with Description", 
    367     "version":9, 
     414    "version":th_version, 
    368415    "sql": """ 
    369416SELECT __color__, __group__, __style__,  ticket, summary, __component__ , 
    …… 
    377424       t.id AS ticket, summary AS summary,             -- ## Break line here 
    378425       component as __component__, version, severity, milestone, status, owner, 
    379        CAST(EstimatedHours.value as DECIMAL) as Estimated_work, 
    380        CAST(totalhours.value as DECIMAL) as Total_work, 
     426       CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     427         ELSE CAST( EstimatedHours.value AS DECIMAL ) END as Estimated_work, 
     428       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     429         ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work, 
    381430       CASE WHEN billable.value = 1 THEN 'Y' else 'N' END as billable, 
    382431       time AS created, changetime AS modified,         -- ## Dates are formatted 
    …… 
    407456       NULL as ticket, 'Total work' AS summary,              
    408457       t.component as __component__, NULL as version, NULL as severity, 
    409        NULL as  milestone, NULL as status, NULL as owner, 
    410        SUM(CAST(EstimatedHours.value as DECIMAL)) as Estimated_work, 
    411        SUM(CAST(totalhours.value as DECIMAL)) as Total_work, 
     458       NULL as  milestone, 'Time Remaining: ' as status, 
     459       CAST( 
     460       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     461         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) -  
     462       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     463         ELSE CAST( totalhours.value AS DECIMAL ) END) 
     464         AS VARCHAR(1024))  as owner, 
     465       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     466         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) as Estimated_work, 
     467       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     468         ELSE CAST( totalhours.value AS DECIMAL ) END) as Total_work, 
    412469       NULL as billable, 
    413470       NULL as created, NULL as modified,         -- ## Dates are formatted 
    …… 
    440497    "uuid":"03815803-7688-4f3a-8e65-8d254cc1d1fb", 
    441498    "title":"Ticket Hours Grouped By Milestone", 
    442     "version":10, 
     499    "version":th_version, 
    443500    "sql": """ 
    444501SELECT __color__, __group__, __style__,  ticket, summary, component ,version, 
    …… 
    452509       t.id AS ticket, summary AS summary,             -- ## Break line here 
    453510       component,version, severity, milestone as __milestone__, status, owner, 
    454        CAST(EstimatedHours.value as DECIMAL) as Estimated_work, 
    455        CAST(totalhours.value as DECIMAL) as Total_work, 
     511       CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     512         ELSE CAST( EstimatedHours.value AS DECIMAL ) END as Estimated_work, 
     513       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     514         ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work, 
    456515       CASE WHEN billable.value = 1 THEN 'Y' 
    457516            else 'N' 
    …… 
    483542       NULL as ticket, 'Total work' AS summary,              
    484543       NULL as component,NULL as version, NULL as severity, 
    485        t.milestone as  __milestone__, NULL as status, NULL as owner, 
    486        SUM(CAST(EstimatedHours.value as DECIMAL)) as Estimated_work, 
    487        SUM(CAST(totalhours.value as DECIMAL)) as Total_work, 
     544       t.milestone as  __milestone__, 'Time Remaining: ' as status, 
     545       CAST( 
     546       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     547         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) -  
     548       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     549         ELSE CAST( totalhours.value AS DECIMAL ) END) 
     550         AS VARCHAR(1024)) as owner, 
     551       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     552         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) as Estimated_work, 
     553       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     554         ELSE CAST( totalhours.value AS DECIMAL ) END) as Total_work, 
    488555       NULL as billable, 
    489556       NULL as created, NULL as modified,         -- ## Dates are formatted 
    …… 
    516583    "uuid":"040c9025-7641-4d18-96ad-2b26b4095566", 
    517584    "title":"Ticket Hours Grouped By MileStone with Description", 
    518     "version":10, 
     585    "version":th_version, 
    519586    "sql": """ 
    520587SELECT __color__, __group__, __style__,  ticket, summary, component ,version, severity, 
    …… 
    528595       t.id AS ticket, summary AS summary,             -- ## Break line here 
    529596       component,version, severity, milestone as __milestone__, status, owner, 
    530        CAST(EstimatedHours.value as DECIMAL) as Estimated_work, 
    531        CAST(totalhours.value as DECIMAL) as Total_work, 
     597       CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     598         ELSE CAST( EstimatedHours.value AS DECIMAL ) END as Estimated_work, 
     599       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     600         ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work, 
    532601       CASE WHEN billable.value = 1 THEN 'Y' 
    533602            else 'N' 
    …… 
    561630       NULL as component,NULL as version, NULL as severity, 
    562631       t.milestone as __milestone__, 
    563        NULL as status, NULL as owner, 
    564        SUM(CAST(EstimatedHours.value as DECIMAL)) as Estimated_work, 
    565        SUM(CAST(totalhours.value as DECIMAL)) as Total_work, 
     632       'Time Remaining: ' as status, 
     633       CAST( 
     634       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     635         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) -  
     636       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     637         ELSE CAST( totalhours.value AS DECIMAL ) END) 
     638         AS VARCHAR(1024)) as owner, 
     639       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 
     640         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) as Estimated_work, 
     641       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 
     642         ELSE CAST( totalhours.value AS DECIMAL ) END) as Total_work, 
    566643       NULL as billable, 
    567644       NULL as created, NULL as modified,         -- ## Dates are formatted