Added set customer command.
2 Database lib for cronrec.
3 Author: Eugen Sawin (sawine@me73.com)
4 Dependencies: libsqlite3-dev
9 ACTIVITIES_TABLE = """activities(
13 COMPANIES_TABLE = """companies(
21 PROJECTS_TABLE = """projects(
25 constraint customer_fk foreign key(customer) references companies(rowid) on delete
28 RATES_TABLE = """rates(
33 constraint project_fk foreign key(project) references projects(rowid) on delete
35 constraint activity_fk foreign key(activity) references activities(rowid) on delete
38 TASKS_TABLE = """tasks(
44 constraint project_fk foreign key(project) references projects(rowid) on delete
46 constraint activity_fk foreign key(activity) references activities(rowid) on delete
49 BREAKS_TABLE = """breaks(
54 constraint task_fk foreign key(task) references tasks(rowid) on delete
57 TABLES = (ACTIVITIES_TABLE, COMPANIES_TABLE, PROJECTS_TABLE, RATES_TABLE,
58 TASKS_TABLE, BREAKS_TABLE)
60 DEF_PROJECT = "default"
61 DEF_ACTIVITY = "default"
64 con = sqlite3.connect(db_file)
65 return con, con.cursor()
68 con, cur = session(db_file)
69 cur.execute("select * from projects")
73 cur.execute("select * from activities")
77 cur.execute("select * from tasks")
81 cur.execute("select * from breaks")
89 con, cur = session(db_file)
91 sql = "create table " + table
96 def update_where(db_file, table, dep_column, dep_value, set_column, set_value):
97 con, cur = session(db_file)
98 values = (set_value, dep_value)
99 sql = "update %s set %s=? where %s=?" % (table, set_column, dep_column)
100 cur.execute(sql, values)
105 def find_last_project(db_file, time):
106 con, cur = session(db_file)
108 sql = "select project from tasks where begin < ? and end is null order by \
110 cur.execute(sql, values)
114 sql = "select name from projects where rowid=?"
115 cur.execute(sql, values)
116 project = cur.fetchone()[0]
118 project = None#DEF_PROJECT
123 def find_last_activity(db_file, time):
124 con, cur = session(db_file)
126 sql = "select activity from tasks where begin < ? and end is null order by \
128 cur.execute(sql, values)
132 sql = "select name from activities where rowid=?"
133 cur.execute(sql, values)
134 activity = cur.fetchone()[0]
136 activity = None#DEF_ACTIVITY
141 def find_active_task(db_file, time=None):
142 con, cur = session(db_file)
144 values = (time, time)
145 sql = "select project, activity from tasks where begin < ? and \
146 (end is null or end > ?)"
149 sql = "select project, activity from tasks where end is null"
150 cur.execute(sql, values)
151 task = cur.fetchone()
157 def id_name(db_file, table, id):
158 con, cur = session(db_file)
160 sql = "select name from %s where rowid=?" % table
161 cur.execute(sql, values)
166 def row_id(db_file, table, name):
167 con, cur = session(db_file)
169 sql = "select rowid from %s where name=?" % table
170 cur.execute(sql, values)
173 sql = "insert into %s(name) values(?)" % table
174 cur.execute(sql, values)
177 return row_id(db_file, table, name)
182 def project_id(db_file, name):
183 return row_id(db_file, "projects", name)
185 def activity_id(db_file, name):
186 return row_id(db_file, "activities", name)
188 def end(db_file, project, activity, time, log):
189 print "ending: %s:%s" % (project, activity)
190 if activity or project:
192 activity = find_last_activity(db_file, time)
194 project = find_last_project(db_file, time)
196 con, cur = session(db_file)
197 if activity == "all" and project == "all":
199 sql = "update tasks set end=?, log=? where end is null"
201 values = (time, log, project_id(db_file, project),
202 activity_id(db_file, activity))
203 sql = "update tasks set end=?, log=? where project=? and activity=?"
204 cur.execute(sql, values)
209 def begin(db_file, project, activity, time):
210 print "beginning: %s:%s" % (project, activity)
211 last_project = find_last_project(db_file, time)
212 last_activity = find_last_activity(db_file, time)
214 project = last_project
216 activity = DEF_ACTIVITY
217 if activity != last_activity or project != last_project:
218 end(db_file, last_project, last_activity, time, None)
219 con, cur = session(db_file)
220 values = (time, None, project_id(db_file, project),
221 activity_id(db_file, activity), None)
222 sql = "insert into tasks values(?, ?, ?, ?, ?)"
223 cur.execute(sql, values)
228 def is_paused(db_file, time):
229 con, cur = session(db_file)
231 sql = "select rowid from breaks where begin < ? and end is null"
232 cur.execute(sql, values)
233 paused = cur.fetchone()
237 def pause(db_file, time):
239 if not is_paused(db_file, time):
240 task = find_active_task(db_file, time)
241 con, cur = session(db_file)
242 values = (time, None, task, None)
243 sql = "insert into breaks values(?, ?, ?, ?)"
244 cur.execute(sql, values)
249 def resume(db_file, task, time, log):
251 if is_paused(db_file, time):
253 task = find_active_task(db_file, time)
254 con, cur = session(db_file)
255 values = (time, log, task)
256 sql = "update breaks set end=?, log=? where task=? and end is null"
257 cur.execute(sql, values)
263 task = find_active_task(db_file)
264 con, cur = session(db_file)
268 sql = "select project, activity from tasks where rowid=?"
269 cur.execute(sql, values)
270 result = cur.fetchone()
272 result = (id_name(db_file, "projects", result[0]),
273 id_name(db_file, "activities", result[1]))