Ejemplo n.º 1
0
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)
Ejemplo n.º 2
0
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
Ejemplo n.º 3
0
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)
Ejemplo n.º 4
0
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)	
Ejemplo n.º 5
0
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
Ejemplo n.º 6
0
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
Ejemplo n.º 7
0
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
Ejemplo n.º 8
0
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
Ejemplo n.º 9
0
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
Ejemplo n.º 10
0
 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})')
Ejemplo n.º 11
0
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)
Ejemplo n.º 12
0
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
Ejemplo n.º 13
0
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
Ejemplo n.º 14
0
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
Ejemplo n.º 15
0
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
Ejemplo n.º 16
0
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)
Ejemplo n.º 17
0
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>&nbsp;&nbsp;&nbsp;'+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">&nbsp;&nbsp;&nbsp;&nbsp;
		<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
Ejemplo n.º 18
0
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))
Ejemplo n.º 19
0
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
Ejemplo n.º 20
0
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
Ejemplo n.º 21
0
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)
Ejemplo n.º 22
0
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
Ejemplo n.º 23
0
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
Ejemplo n.º 24
0
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
Ejemplo n.º 25
0
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)
Ejemplo n.º 26
0
def delete_event(r):
	query = 'DELETE FROM events WHERE rowid='+str(r)
	util.query_db(query)
Ejemplo n.º 27
0
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))	
Ejemplo n.º 28
0
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)
Ejemplo n.º 29
0
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')
Ejemplo n.º 30
0
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)
Ejemplo n.º 31
0
def add_location(name, lat, lng):
	query = "INSERT INTO locations (name, latitude, longitude) VALUES ('"+name+"',"+str(lat)+","+str(lng)+")"
	util.query_db(query)
Ejemplo n.º 32
0
def add_person(name, alias):
	query = "INSERT INTO people (name, alias) VALUES ('"+name+"','"+alias+"')"
	util.query_db(query)
Ejemplo n.º 33
0
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')
Ejemplo n.º 34
0
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)
Ejemplo n.º 35
0
def delete_calendar(id):
	query = 'DELETE FROM calendar WHERE rowid='+str(id)
	util.query_db(query)
Ejemplo n.º 36
0
def modify_name_lookup(id, name, alias):
	query = 'UPDATE people SET name="'+name+'", alias="'+alias+'" WHERE rowid='''+str(id)
	util.query_db(query)
Ejemplo n.º 37
-1
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)