def add_todo(description, list_id, parent_id):
sys_time = get_time_system()
priority = util.query_db('SELECT max(priority) FROM todo WHERE parent_id='+str(parent_id))[0][0]
if priority is None: priority = 1
else: priority += 1
query = '''INSERT INTO todo (description, list_id, parent_id, priority, status, created) VALUES ("'''+description+'''", '''+str(list_id)+''', '''+str(parent_id)+''', '''+str(priority)+''', 0, "'''+sys_time+'''")'''
util.query_db(query)
def html_edit_name(id, name, alias, action):
html = '<div id="editname"><h1>'
# edit a name
if action is None:
(name, alias) = util.query_db('SELECT name, alias FROM people WHERE rowid='+str(id))[0]
html += '''Edit Name #'''+str(id)+'''<p/>
<form name="editor" action="" method="get">
<input type="hidden" name="id" value="'''+str(id)+'''">
<input type="hidden" name="action" value="modify">
Name: <input type="text" name="name" value="'''+name+'''">
Alias: <input type="text" name="alias" value="'''+alias+'''">
<p/><input type="submit" value="Edit">
</form>
<p/><a href="?action=delete&id='''+str(id)+'''">Delete</a>'''
# name modified
elif str(action) == "modify":
html += '''<b>Modified:</b>
<br/>Id: '''+str(id)+'''
<br/>Name: '''+name+'''
<br/>Alias: '''+alias+'''
<p/><a href="/">[back]</a>'''
log.modify_name_lookup(id,name,alias)
# name deleted
elif str(action) == "delete":
(name, alias) = util.query_db('SELECT name, alias FROM people WHERE rowid='+str(id))[0]
html += '''<b>Deleted:</b>
<br/>Id: '''+str(id)+'''
<br/>Name: '''+name+'''
<br/>Alias: '''+alias+'''
<p/><a href="/">[back]</a>'''
log.delete_name_lookup(id)
html += '</h1></div>'
return html
def delete_todo(idx):
if idx=="completed":
completed = util.query_db('SELECT * FROM todo WHERE status=1')
for c in completed: delete_todo_idx(c[0])
else:
children = util.query_db('SELECT * FROM todo WHERE parent_id='+str(idx)) # delete children
for child in children: delete_todo(child[0])
delete_todo_idx(idx)
def replace_string(s1, s2): query = 'SELECT rowid, * FROM events' events = util.query_db(query) for event in events: if s1 in event[5]: newevent = re.sub(s1, s2, event[5]) logstring = "UPDATE events SET log=\"" + newevent + "\" WHERE rowid=" + str(event[0]) util.query_db(logstring)
def get_log(keyword, date1, date2, location, limit):
where = []
if keyword is not None:
where.append('log like "%'+keyword+'%"')
if date1 is not None:
if date2 is None:
d1 = date1.split("-")
date2 = datetime.date(int(d1[0]), int(d1[1]), int(d1[2]))
date2 += datetime.timedelta(1)
time1 = str(date1) + ' 05:00'
time2 = str(date2) + ' 05:00'
where.append('time > "'+time1+'"')
where.append('time < "'+time2+'"')
if location is not None:
[lat,lng] = location.split(',')
wstr = '(latitude-' + str(lng) + ')*(latitude-' + str(lng) + ') + (longitude-' + str(lat) + ')*(longitude-' + str(lat) +') < 0.000001'
where.append(wstr)
query = 'SELECT rowid, * FROM events '
if len(where):
query += "WHERE " + " AND ".join(where) + " ORDER BY time"
else:
if limit is None:
query += " ORDER BY time desc"
else:
query += " ORDER BY time desc LIMIT " + str(limit)
log0 = util.query_db(query)
return log0
def html_names_lookup():
query = "SELECT rowid, * FROM people"
names = util.query_db(query)
html = """
<table width="33%" border=0 cellpadding=4>
<tr>
<td width="10%"><b>Index</b></td>
<td width="35%"><b>Short</b></td>
<td width="50%"><b>Name</b></td>
<td width="5%"></td>
</tr>
"""
for name in names:
html += (
"""
<tr>
<td>"""
+ str(name[0])
+ """</td>
<td>"""
+ name[1]
+ """</td>
<td>"""
+ name[2]
+ """</td>
<td><a href="/editname/?id="""
+ str(name[0])
+ """">[m]</a></td>
</tr>
"""
)
html += "</table>"
return html
def html_locations_lookup():
query = "SELECT rowid, * FROM locations"
locations = util.query_db(query)
html = """
<table width="33%" border=0 cellpadding=4>
<tr>
<td width="8%"><b>Index</b></td>
<td width="54%"><b>Short</b></td>
<td width="38%"><b>Location</b></td>
</tr>
"""
for loc in locations:
html += (
"""
<tr>
<td>"""
+ str(loc[0])
+ """</td>
<td>"""
+ loc[1]
+ """</td>
<td>"""
+ str(loc[2])
+ ", "
+ str(loc[3])
+ """</td>
<td><a href="/editlocation/?id="""
+ str(loc[0])
+ """">[m]</a></td>
</tr>
"""
)
html += "</table>"
return html
def rq_from_db(inflow_group):
"""
Get list of UNIQUAC r and q parameters for the list of inflow species
inflow_group - [
[[group1, num], [group2, num], [group3, num]] - species1,
[[group1, num], [group2, num]] - species2,
[[group1, num], [group2, num], [group3, num]] - species3
]
"""
# get a set of subgroups
group_set = get_group_set(inflow_group)
s_set = [str(i) for i in group_set]
line = ','.join(s_set)
# connect to db file
cwd = os.getcwd()
db_path = os.path.join(cwd,r'data\UNIFAC.db')
rq_data = query_db(db_path, 'SUBGROUP, R, Q', 'GROUP_INFO', f'SUBGROUP IN ({line})')
# convert from list to dic
r_dic = {}
q_dic = {}
for subgroup, r, q in rq_data:
r_dic[subgroup] = r
q_dic[subgroup] = q
spsize = len(inflow_group)
rq = np.zeros((2, spsize))
for i, molecule in enumerate(inflow_group):
for group in molecule:
subgroup = group[0]
ngroup = group[1]
rq[0, i] += r_dic[subgroup]*ngroup
rq[1, i] += q_dic[subgroup]*ngroup
return rq
def get_department_admin(department_id):
'''
Get the user_id of a department admin given the deparment_id
'''
response = query_db('''SELECT department_admin FROM departments
WHERE department_id = ?''', [department_id],
one=True)
return response[0] if response else False
def query_interaction(self):
s_set = [str(i) for i in self.maingroup_set]
line = ','.join(s_set)
# connect to db file
cwd = os.getcwd()
db_path = os.path.join(cwd, r'data\UNIFAC.db')
# rq_data = query_db(db_path, 'SUBGROUP, R, Q', 'GROUP_INFO', f'SUBGROUP IN ({line})')
self.interaction_list = query_db(db_path, 'MAIN_I, MAIN_J, AIJ, AJI', 'UNIFAC_INTERACTION', f'MAIN_I IN ({line}) AND MAIN_J IN ({line})')
def add_calendar(name, description, datenew, start, end, rm, rt, rw, rr, rf, rs, rsu, until):
sys_time = get_time_system()
name = substitute_aliases(name)
description = substitute_aliases(description)
query = "INSERT INTO calendar (name, description, start, end, created) VALUES ('"+name+"','"+description+"','"+datenew+" "+start+"', '"+datenew+" "+end+"', '"+sys_time+"')"
util.query_db(query)
if len(until) > 0: # if there is repetition selected
repeat_days = [ r[0] for r in enumerate((rm=='1', rt=='1', rw=='1', rr=='1', rf=='1', rs=='1', rsu=='1')) if r[1]==True ]
date1 = datetime.datetime(int(str(datenew)[0:4]), int(str(datenew)[5:7]), int(str(datenew)[8:10]))
date2 = datetime.datetime(int(str(until)[0:4]), int(str(until)[5:7]), int(str(until)[8:10]))
day_count = (date2 - date1).days + 1
for d in [d for d in (date1 + datetime.timedelta(n) for n in range(1,day_count)) if d <= date2]:
if d.weekday() in repeat_days:
start2 = str(d)[0:10] + ' ' + start
end2 = str(d)[0:10] + ' ' + end
query = "INSERT INTO calendar (name, description, start, end, created) VALUES ('"+name+"','"+description+"','"+start2+"', '"+end2+"', '"+sys_time+"')"
util.query_db(query)
def is_department_admin(user_id):
'''
Check if a user is a department admin
If they are, return the department_id
'''
response = query_db('''SELECT department_id FROM departments
WHERE department_admin = ?''', [user_id],
one=True)
return response[0] if response else False
def html_edit(id, time0, lat, lng, log0, action):
html = '<div id="editlog"><h1>'
# edit an entry
if action is None:
(time0, lat, lng, log0) = util.query_db('SELECT time, latitude, longitude, log FROM events WHERE rowid='+str(id))[0]
html += '''Edit entry #'''+str(id)+'''<p/>
<form name="editor" action="" method="get">
<input type="hidden" name="id" value="'''+str(id)+'''">
<input type="hidden" name="action" value="modify">
Time: <input type="text" name="time" value="'''+time0+'''">
<p/>
Latitude: <input type="text" name="lat" value="'''+str(lat)+'''">
Longitude: <input type="text" name="lng" value="'''+str(lng)+'''">
<p/>
<textarea name="log0" cols="60" rows="10">'''+str(log0)+'''</textarea>
<p/>
<input type="submit" value="Edit">
</form>
<p/><a href="?action=delete&id='''+str(id)+'''">Delete</a>'''
# entry modified
elif str(action) == "modify":
html += '''<b>Modified</b>:
<br/>Id: '''+str(id)+'''
<br/>Time: '''+str(time0)+'''
<br/>Latitude: '''+str(lat)+'''
<br/>Longitude: '''+str(lng)+'''
<br/>Log: '''+str(log0)+'''
<p/><a href="/">[back]</a>'''
log.modify_event(id, time0, lat, lng, log0)
# entry deleted
elif str(action) == "delete":
(time0, lat, lng, log0) = util.query_db('SELECT time, latitude, longitude, log FROM events WHERE rowid='+str(id))[0]
html += '''<b>Deleted:</b>
<br/>Id: '''+str(id)+'''
<br/>Time: '''+str(time0)+'''
<br/>Latitude: '''+str(lat)+'''
<br/>Longitude: '''+str(lng)+'''
<br/>Log: '''+str(log0)+'''
<p/><a href="/">[back]</a>'''
log.delete_event(id)
html += '</h1></div>'
return html
def get_lists():
lists = {}
for (idx, name, priority, created, archived) in util.query_db("SELECT * FROM todo_lists"):
lists[idx] = {"index": idx, "priority": priority, "name": name, "tasks": {}}
for (idx, description, list_idx, parent_idx, priority, status, created, modified) in util.query_db(
"SELECT * FROM todo ORDER BY parent_id"
):
n = Task(idx, list_idx, parent_idx, priority, description, status)
lists[list_idx]["tasks"][idx] = n
if parent_idx != 0:
lists[list_idx]["tasks"][parent_idx].add_child(n)
return lists
def html_edit_calendar(id, name = None, description = None, start = None, end = None, action = None):
html = '<div id="editcalendar"><h1>'
# edit a name
if action is None:
(name, description, start, end) = util.query_db('SELECT name, description, start, end FROM calendar WHERE rowid='+str(id))[0]
html += '''Edit Calendar #'''+str(id)+'''<p/>
<form name="editor" action="" method="get">
<input type="hidden" name="id" value="'''+str(id)+'''">
<input type="hidden" name="action" value="modify">
Name:<br/><textarea name="name" cols=60 rows=1>'''+name+'''</textarea><p/>
Description:<br/><textarea name="description" cols=60 rows=5>'''+description+'''</textarea>
<p/>
<p/>Start: <input type="text" name="start" value="'''+start+'''">
End: <input type="text" name="end" value="'''+end+'''">
<p/><input type="submit" value="Edit">
</form>
<p/><a href="?action=delete&id='''+str(id)+'''">Delete</a>'''
# name modified
elif str(action) == "modify":
html += '''<b>Modified:</b>
<br/>Id: '''+str(id)+'''
<br/>Name: '''+name+'''
<br/>Description: '''+description+'''
<br/>Start: '''+start+'''
<br/>End: '''+end
log.modify_calendar(id, name, description, start, end)
# name deleted
elif str(action) == "delete":
(name, description, start, end) = util.query_db('SELECT name, description, start, end FROM calendar WHERE rowid='+str(id))[0]
html += '''<b>Deleted:</b>
<br/>Id: '''+str(id)+'''
<br/>Name: '''+name+'''
<br/>Description: '''+description+'''
<br/>Start: '''+start+'''
<br/>End: '''+end
log.delete_calendar(id)
html += '</h1></div>'
return html
def get_geolocation_manual(log):
(lat, lng) = (None, None)
geo = re.compile('@\((.+)\)').findall(log)
if geo:
gps = re.compile('@\(([^A-Za-z)]+)\)').findall(log)
if gps:
(lat, lng) = gps[0].split(",")
(lat, lng) = (float(lat), float(lng))
else:
query = 'SELECT * FROM locations WHERE name="'+geo[0]+'"'
loc = util.query_db(query)
if loc: (lat, lng) = (loc[0][1], loc[0][2])
else: (lat, lng) = (None, None)
return (lat, lng)
def html_mass_edit(backlog_text = None, action = None):
html = '''<style>
#backlog {
background-color:#FFF;
border:4px solid #5599DD;
margin:10px;
margin-top:16px;
padding:3px;
}
#massedit {
margin:10px;
margin-top:32px;
}
</style>'''
if backlog_text is not None:
html += '<table id="backlog" width="96%" border=1 style="margin:32px">'
html += '<tr><td width="8%"><b>category</b></td><td width="60%"><b>log</b></td><td width="16%"><b>time</b></td><td width="16%"><b>location</b></td></tr>'
backlog_lines = backlog_text.split('\n')
for l in backlog_lines:
logstring = re.compile('^log ').split(l)[-1]
(logstring, time0, gps, category) = log.process_log_string(logstring, 'log', False)
print gps
if gps[0] is None: location = '<font color="#FF0000">none</font>'
else: location = '%0.3f, %0.3f' % gps
html += '<tr><td>'+category+'</td><td>'+logstring+'</td><td>'+time0+'</td><td>'+location+'</td></tr>'
if action=='addtolog':
log.add_log(logstring, time0, gps, category)
html += '</table><p>'
if action != 'addtolog':
last_log = util.query_db('SELECT rowid, time, log FROM events ORDER BY time DESC LIMIT 1')
html += '<p><div id="massedit">'
for l in last_log: html += '<br> '+str(l[0])+' ('+l[1][:-3]+') :: '+l[2]
html += '''<form name="editor" action="" method="post">
<textarea id="backlog" name="backlog" cols=110 rows=30 maxlength=2500>'''
if backlog_text is not None:
html += backlog_text
html += '''</textarea><p/>
<select name="action">
<option value="validate">validate</option>
<option value="addtolog">add to log</option>
</select>
<input type="submit" value="Submit">
<input type="button" value="Save" id="save">
<a href="javascript:removeLimit();">[remove limit]</a>
</form>
</div>
'''
else:
html += '<p><a href="/">back</a>'
return html
def prioritize_todo(idx):
(parent_id, priority) = util.query_db('SELECT parent_id, priority FROM todo WHERE id='+str(idx))[0]
others = util.query_db('SELECT id, priority FROM todo WHERE parent_id='+str(parent_id))
others = [o for o in others if o[1]<priority]
if len(others) > 0:
max_prio = max([o[1] for o in others])
(other_idx, other_priority) = [o for o in others if o[1]==max_prio][0]
util.query_db('UPDATE todo SET priority='+str(priority)+' WHERE id='+str(other_idx))
util.query_db('UPDATE todo SET priority='+str(other_priority)+' WHERE id='+str(idx))
def html_edit_location(id, name, lat, lng, action):
html = '<div id="editlocation"><h1>'
# edit a location
if action is None:
(name, lat, lng) = util.query_db('SELECT name, latitude, longitude FROM locations WHERE rowid='+str(id))[0]
html += '''Edit location #'''+str(id)+'''<p/>
<form name="editor" action="" method="get">
<input type="hidden" name="id" value="'''+str(id)+'''">
<input type="hidden" name="action" value="modify">
Name: <input type="text" name="name" value="'''+name+'''">
<p/>
Latitude: <input type="text" name="lat" value="'''+str(lat)+'''">
Longitude: <input type="text" name="lng" value="'''+str(lng)+'''">
<p/><input type="submit" value="Edit">
</form>
<p/><a href="?action=delete&id='''+str(id)+'''">Delete</a>'''
# location modified
elif str(action) == "modify":
html += '''<b>Modified:</b>
<br/>Id: '''+str(id)+'''
<br/>Name: '''+name+'''
<br/>Latitude: '''+str(lat)+'''
<br/>Longitude: '''+str(lng)+'''
<p/><a href="/">[back]</a>'''
log.modify_location_lookup(id,name,lat,lng)
# location deleted
elif str(action) == "delete":
(name, lat, lng) = util.query_db('SELECT name, latitude, longitude FROM locations WHERE rowid='+str(id))[0]
html += '''<b>Deleted:</b>
<br/>Id: '''+str(id)+'''
<br/>Name: '''+name+'''
<br/>Latitude: '''+str(lat)+'''
<br/>Longitude: '''+str(lng)+'''
<p/><a href="/">[back]</a>'''
log.delete_location_lookup(id)
html += '</h1></div>'
return html
def get_media(date1, date2):
where = []
if date1 is not None:
if date2 is None:
d1 = date1.split("-")
date2 = datetime.date(int(d1[0]), int(d1[1]), int(d1[2]))
date2 += datetime.timedelta(1)
time1 = str(date1) + ' 05:00'
time2 = str(date2) + ' 05:00'
where.append('time > "'+time1+'"')
where.append('time < "'+time2+'"')
query = 'SELECT * FROM media '
if len(where): query += "WHERE " + " AND ".join(where) + " ORDER BY time"
media = util.query_db(query)
return media
def toggle_todo(idx, new_status = None):
if new_status is None:
status = util.query_db('SELECT status FROM todo WHERE id='+str(idx))[0][0]
new_status = 1 - status
util.query_db(query = 'UPDATE todo SET status='+str(new_status)+' WHERE id='+str(idx)) # update self
if new_status==1:
util.query_db('UPDATE todo SET completed="'+get_time_system()+'" WHERE id='+str(idx))
children = util.query_db('SELECT * FROM todo WHERE parent_id='+str(idx)) # update children
for child in children:
toggle_todo(child[0], new_status)
def get_all_issues():
'''
Called when super admin logs in
Get all issues on the system
'''
response = query_db('''SELECT istbl.issue_id,istbl.raised_by,
istbl.description,istbl.created,usrtbl.forename AS raised_forename,
usrtbl.surname AS raised_surname,
usrtbl2.forename AS assigned_to_forename,
usrtbl2.surname AS assigned_to_surname,isstbl.status_name
FROM issues AS istbl INNER JOIN users AS usrtbl ON
istbl.raised_by = usrtbl.user_id
LEFT JOIN users AS usrtbl2 ON
istbl.assigned_to = usrtbl2.user_id
INNER JOIN issue_status AS isstbl ON
istbl.status = isstbl.issue_status_id
ORDER BY istbl.created desc''')
return response if response else False
def get_assigned_issues(rep_id):
'''
Called when a support rep logs in
Get all issues asisgned to rep
'''
response = query_db(
'''SELECT istbl.issue_id,istbl.raised_by,
istbl.description,istbl.created,usrtbl.forename AS raised_forename,
usrtbl.surname AS raised_surname,
usrtbl2.forename AS assigned_to_forename,
usrtbl2.surname AS assigned_to_surname,isstbl.status_name
FROM issues AS istbl INNER JOIN users AS usrtbl ON
istbl.raised_by = usrtbl.user_id
LEFT JOIN users AS usrtbl2 ON
istbl.assigned_to = usrtbl2.user_id
INNER JOIN issue_status AS isstbl ON
istbl.status = isstbl.issue_status_id
WHERE assigned_to = ?
ORDER BY istbl.created desc''', [rep_id])
return response if response else False
def get_department_issues(user_id):
'''
Called when a department admin logs in
Get all issues tagged for their department
'''
department_id = is_department_admin(user_id)
if department_id:
response = query_db(
'''SELECT istbl.issue_id,istbl.raised_by,
istbl.description,istbl.created,usrtbl.forename AS raised_forename,
usrtbl.surname AS raised_surname,
usrtbl2.forename AS assigned_to_forename,
usrtbl2.surname AS assigned_to_surname,isstbl.status_name
FROM issues AS istbl INNER JOIN users AS usrtbl ON
istbl.raised_by = usrtbl.user_id
LEFT JOIN users AS usrtbl2 ON
istbl.assigned_to = usrtbl2.user_id
INNER JOIN issue_status AS isstbl ON
istbl.status = isstbl.issue_status_id
WHERE department = ? ORDER BY istbl.created desc''',
[department_id])
return response if response else False
else:
return False
def modify_event(id, time, lat, lng, log): query = 'UPDATE events SET time="'+str(time)+'", latitude='+str(lat)+', longitude='+str(lng)+', log="'+str(log)+'" WHERE rowid='+str(id) util.query_db(query)
def delete_event(r): query = 'DELETE FROM events WHERE rowid='+str(r) util.query_db(query)
def delete_todo_idx(idx):
(idx, description, list_id, parent_id, priority, status, created, completed) = util.query_db('SELECT * FROM todo WHERE id='+str(idx))[0] # move self to archive
if completed is None: completed = "NULL"
deleted = get_time_system()
util.query_db('INSERT INTO todo_archive (id, description, list_id, parent_id, priority, created, completed, archived) VALUES ('+str(idx)+', "'+description+'", '+str(list_id)+', '+str(parent_id)+', '+str(priority)+', "'+created+'", "'+completed+'", "'+deleted+'")')
util.query_db('DELETE FROM todo WHERE id='+str(idx))
def modify_calendar(id, name, description, start, end): query = 'UPDATE calendar SET name="'+name+'", description="'+description+'", start="'+start+'", end="'+end+'" WHERE rowid='''+str(id) util.query_db(query)
def fix_locations_names():
util.query_db('DROP TABLE locations2')
util.query_db('DROP TABLE people2')
util.query_db('CREATE TABLE locations2 (id INTEGER PRIMARY KEY, name TEXT, latitude DOUBLE, longitude DOUBLE)')
util.query_db('CREATE TABLE people2 (id INTEGER PRIMARY KEY, name TEXT, alias TEXT)')
for loc in util.query_db('SELECT * FROM locations'):
(name, lat, lng) = loc
print name
query = 'INSERT INTO locations2 (name, latitude, longitude) VALUES ("'+name+'", '+str(lat)+', '+str(lng)+')'
print query
util.query_db(query)
print "======================"
for p in util.query_db('SELECT * FROM people'):
(name, alias) = p
print name+ ' ' + alias
query = 'INSERT INTO people2 (name, alias) VALUES ("'+name+'", "'+alias+'")'
print query
util.query_db(query)
print "======================"
util.query_db('ALTER TABLE locations RENAME TO locations3')
util.query_db('ALTER TABLE locations2 RENAME TO locations')
util.query_db('ALTER TABLE people RENAME TO people3')
util.query_db('ALTER TABLE people2 RENAME TO people')
def add_log(log, time, gps, category):
if gps[0] is None: gps = ("NULL", "NULL")
log = substitute_aliases(log)
log = re.sub("'", "''", log)
query = "INSERT INTO events (category, time, latitude, longitude, log) VALUES ('"+category+"','"+time+"',"+str(gps[0])+","+str(gps[1])+",'"+log+"')"
util.query_db(query)
def add_location(name, lat, lng):
query = "INSERT INTO locations (name, latitude, longitude) VALUES ('"+name+"',"+str(lat)+","+str(lng)+")"
util.query_db(query)
def add_person(name, alias):
query = "INSERT INTO people (name, alias) VALUES ('"+name+"','"+alias+"')"
util.query_db(query)
def disp_tasks():
util.query_db('DROP TABLE todo2')
util.query_db('DROP TABLE todo_lists2')
util.query_db('DROP TABLE todo_archive2')
util.query_db('CREATE TABLE todo2 (id INTEGER PRIMARY KEY, description TEXT, list_id INTEGER, parent_id INTEGER, priority INTEGER, status INTEGER, created DATETIME, completed DATETIME)')
util.query_db('CREATE TABLE todo_archive2 (id INTEGER, description TEXT, list_id INTEGER, parent_id INTEGER, priority INTEGER, status INTEGER, created DATETIME, completed DATETIME, archived DATETIME)')
util.query_db('CREATE TABLE todo_lists2 (id INTEGER PRIMARY KEY, name TEXT, priority INTEGER, created DATETIME, archived DATETIME)')
print "============\nLISTS\n============"
for tlist in util.get_todo_lists():
p = 1
(idx, name, created, archived) = tlist
query = 'INSERT INTO todo_lists2 (id, name, priority, created) VALUES ('+str(idx)+', "'+name+'", '+str(p)+', "'+created+'")'
p+=1
print query
util.query_db(query)
print "============\nTASKS\n============"
for task in util.get_todo():
(idx, description, list_id, parent_id, status, created, completed) = task
if completed is None: completed = 'NULL'
priority = 1 + util.query_db('SELECT count(*) FROM todo2 WHERE parent_id='+str(parent_id))[0][0]
query = '''INSERT INTO todo2 (id, description, list_id, parent_id, priority, status, created, completed)
VALUES ('''+str(idx)+''', "'''+description+'''", '''+str(list_id)+''', '''+str(parent_id)+''', '''+str(priority)+''', '''+str(status)+''', "'''+created+'''", "'''+completed+'''")'''
print query
util.query_db(query)
print "============\nARCHIVE\n============"
for task in util.get_todo_archive():
(idx, description, list_id, parent_id, created, completed, archived) = task
if completed is None: completed = 'NULL'
priority = 1 + util.query_db('SELECT count(*) FROM todo_archive2 WHERE parent_id='+str(parent_id))[0][0]
query = '''INSERT INTO todo_archive2 (id, description, list_id, parent_id, priority, status, created, completed, archived)
VALUES ('''+str(idx)+''', "'''+description+'''", '''+str(list_id)+''', '''+str(parent_id)+''', '''+str(priority)+''', '''+str(status)+''', "'''+created+'''", "'''+completed+'''", "'''+archived+'''")'''
print query
util.query_db(query)
util.query_db('ALTER TABLE todo RENAME TO todo3')
util.query_db('ALTER TABLE todo_archive RENAME TO todo_archive3')
util.query_db('ALTER TABLE todo_lists RENAME TO todo_lists3')
util.query_db('ALTER TABLE todo2 RENAME TO todo')
util.query_db('ALTER TABLE todo_archive2 RENAME TO todo_archive')
util.query_db('ALTER TABLE todo_lists2 RENAME TO todo_lists')
def modify_location_lookup(id, name, lat, lng): query = 'UPDATE locations SET name="'+name+'", latitude='+str(lat)+', longitude='+str(lng)+' WHERE rowid='+str(id) util.query_db(query)
def delete_calendar(id): query = 'DELETE FROM calendar WHERE rowid='+str(id) util.query_db(query)
def modify_name_lookup(id, name, alias): query = 'UPDATE people SET name="'+name+'", alias="'+alias+'" WHERE rowid='''+str(id) util.query_db(query)
def copy_old_db_to_new_db():
#util.create_log()
#util.create_todo()
# copy events
#events = util.query_db('SELECT * FROM events', OLD_DB)
#for (r, (time, lat, lng, description)) in enumerate(events):
# description = re.sub("'", '"', description)
# log.add_log(description, time, [lat,lng], description)
util.query_db('DROP TABLE people')
util.query_db('DROP TABLE locations')
util.query_db('CREATE TABLE people (name TEXT, alias TEXT)')
util.query_db('CREATE TABLE locations (name TEXT, latitude DOUBLE, longitude DOUBLE)')
# copy names
names = util.query_db('SELECT * FROM names_lookup', OLD_DB)
for (r, (s, l)) in enumerate(names):
s = re.sub('~','',s)
l = re.sub('~','',l)
log.add_person(l, s)
# copy locat ions
places = util.query_db('SELECT * FROM locations_lookup', OLD_DB)
for (r, (name, lat, lng)) in enumerate(places):
print r
log.add_location(name, lat, lng)