· 8 years ago · Apr 17, 2018, 01:18 PM
1#!/usr/bin/env python
2"""
3Copyright (c) 2009, Aaron Bycoffe
4All rights reserved.
5
6Redistribution and use in source and binary forms, with or without
7modification, are permitted provided that the following conditions
8are met:
91. Redistributions of source code must retain the above copyright
10 notice, this list of conditions and the following disclaimer.
112. Redistributions in binary form must reproduce the above copyright
12 notice, this list of conditions and the following disclaimer in the
13 documentation and/or other materials provided with the distribution.
143. The name of the author may not be used to endorse or promote products
15 derived from this software without specific prior written permission.
16
17THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR
18IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
19OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
20IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT,
21INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
22NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
23DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
24THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
25(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
26THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
27
28
29A script for tracking hours spent on projects. Data is stored in a sqlite
30database.
31
32If using a version of Python earlier than 2.5, the sqlite module is required.
33
34Usage:
35 $ timetrack -p testproject -t 4 # Record 4 hours on testproject
36 Total hours spent on testproject: 4.0
37 $ timetrack -p anotherproject -t 6.5 # Record 6.5 hours on anotherproject
38 Total hours spent on anotherproject: 6.5
39 $ timetrack -p testproject -t 3.25 # Record 3.25 hours on testproject
40 Total hours spent on testproject: 7.5
41 $ timetrack # With no arguments, will show a report on all projects:
42 Time tracking report for aaron
43 --------------------------------------------------
44 Project, Hours
45 testproject, 7.5
46 anotherproject, 6.5
47
48 # With just a -p argument, will show a report on
49 # the specified project.
50 $ timetrack -p testproject
51 Time tracking report for aaron
52 --------------------------------------------------
53 Project, Hours
54 testproject, 7.5
55
56 $ timetrack -d testproject # delete testproject
57 Are you sure you want to delete testproject?
58 [yes/no] yes
59 Project testproject deleted
60
61
62To do:
63 - Reporting by time period (last N days, last N weeks, etc.)
64 - Fuller report showing comments on each submission (when user uses -p
65 without -t)
66"""
67from decimal import Decimal
68import getopt
69import os
70import sqlite3
71import sys
72import time
73
74
75def create_tables(cursor):
76 """To be used the first time this script is run by this user.
77 """
78 queries = ["""CREATE TABLE projects
79 (id INTEGER PRIMARY KEY,
80 name TEXT)
81 """,
82 """CREATE TABLE hours
83 (project_id INTEGER,
84 hours REAL,
85 timestamp INTEGER,
86 comment TEXT
87 )
88 """, ]
89 for query in queries:
90 cursor.execute(query)
91 cursor.connection.commit()
92
93def create_cursor():
94 """If the database already exists, connect to it
95 and return a cursor object.
96
97 If not, create the database and tables, then
98 return the cursor object.
99 """
100 db = '/'.join([os.getenv('HOME'), 'timetracker', ])
101 if not os.path.isfile(db):
102 connection = sqlite3.connect(db)
103 cursor = connection.cursor()
104 create_tables(cursor)
105 else:
106 connection = sqlite3.connect(db)
107 cursor = connection.cursor()
108 return cursor
109
110
111def save_hours(cursor, project, hours, comment=None):
112 """Save hours to the database.
113 """
114 # Check whether the project already exists.
115 cursor.execute("SELECT id FROM projects WHERE name = ?", [str(project), ])
116 result = cursor.fetchone()
117 if result:
118 project_id = result[0]
119 else:
120 cursor.execute("""INSERT INTO projects
121 (name)
122 VALUES
123 (?)""", [project, ])
124 cursor.connection.commit()
125 project_id = cursor.lastrowid
126
127 cursor.execute("""INSERT INTO hours
128 (project_id, hours, timestamp, comment)
129 VALUES
130 (?, ?, ?, ?)""",
131 [project_id, float(hours), int(time.time()), comment, ])
132 cursor.connection.commit()
133
134 return project_id, cursor.lastrowid
135
136def get_project_hours(cursor, project_id):
137 """Calculate the total number of hours spent on the given project.
138 """
139 cursor.execute("SELECT SUM(hours) FROM hours WHERE project_id = ?",
140 [project_id, ])
141 cursor.connection.commit()
142 return cursor.fetchone()[0]
143
144
145def print_report(cursor, project=None):
146 if not project:
147 cursor.execute("""SELECT p.name, sum(h.hours) FROM projects p INNER JOIN hours
148 h ON p.id = h.project_id GROUP BY p.name""")
149 else:
150 cursor.execute("""SELECT p.name, sum(h.hours) FROM projects p INNER JOIN hours
151 h ON p.id = h.project_id
152 WHERE p.name = ?
153 GROUP BY p.name""", [project, ])
154 print "Time tracking report for %s" % os.getenv('USER')
155 print "-"*50
156 print "Project, Hours"
157 for name, hours in cursor.fetchall():
158 print ', '.join([name, unicode(hours),])
159
160
161def delete_project(cursor, project):
162 delete = raw_input('Are you sure you want to delete %s?\n[yes/no] ' % project)
163 if delete != 'yes':
164 return False
165
166 # Get project ID so its hours can be deleted.
167 cursor.execute("SELECT id FROM projects WHERE name = ?", [project, ])
168 if not cursor.rowcount:
169 return False
170
171 id = cursor.fetchone()[0]
172 cursor.execute("DELETE FROM hours WHERE project_id = ?", [id, ])
173 cursor.execute("DELETE FROM projects WHERE id = ?", [id, ])
174 cursor.connection.commit()
175 return True
176
177
178def _main():
179 cursor = create_cursor()
180
181 opts, args = getopt.getopt(sys.argv[1:], "p:t:m:d:", [])
182
183 if not opts:
184 print_report(cursor)
185 sys.exit(os.EX_OK)
186
187 opts = dict(opts)
188
189 if '-d' in opts: # delete a project
190 project = opts['-d']
191 deleted = delete_project(cursor, project)
192 if deleted:
193 print 'Project %s deleted' % project
194 else:
195 print 'No projects deleted'
196 sys.exit(os.EX_OK)
197
198 if '-p' in opts and '-t' not in opts: # single-project report
199 project = opts['-p']
200 print_report(cursor, project)
201 sys.exit(os.EX_OK)
202
203 #if '-p' not in opts or '-t' not in opts:
204 # raise ValueError("You must specify both the project name and number of hours")
205
206 project = opts['-p']
207 hours = opts['-t']
208 try:
209 hours = Decimal(hours)
210 except ValueError:
211 sys.exit(os.EX_DATAERR)
212
213 comment = None
214 if '-m' in opts:
215 comment = opts['-m']
216
217 project_id, hours_id = save_hours(cursor, project, hours, comment)
218
219 total_project_hours = get_project_hours(cursor, project_id)
220
221 print "Total hours spent on %s: %s" % (project, total_project_hours)
222
223
224if __name__ == '__main__':
225 _main()