def execute(self, data):
self.set(data)
with dataset(selected_database) as database:
print self.query()
print self.data
database.execute(self.query(), self.data)
return database.last_id()
def create_new_milestone(data):
data['status_id'] = data.get('status_id', STATUS_NEW)
data['updated'] = time.strftime('%Y-%m-%d %H:%M:%S')
required_values(data, required={'title', 'user_id', 'project_id'})
with dataset(selected_database) as database:
query = "insert into milestones (title, project_id, status_id, user_id) VALUES (%(title)s, %(project_id)s, %(status_id)s, %(user_id)s );"
database.execute(query, data)
def execute(self, data):
self.set(data)
self.clean_data()
with dataset(selected_database) as database:
print self.query()
print self.data
database.execute(self.query(), self.data)
self.query_offset += 1
def populate_user_cache(self):
print 'populating user cache'
if not self.user_cache:
self.user_cache = []
with dataset(selected_database) as database:
for row in database.fetchall(u"select user_id, mantis_user_id, email, username from plugin_mantis_users join users on plugin_mantis_users.user_id=users.id;", []):
self.user_cache.append((row['user_id'], row['mantis_user_id'], row['email'], row['username']))
def execute(self, data):
if not data.get('note'):
return
data['status_id'] = data.get('status_id', STATUS_NEW)
data['updated'] = time.strftime('%Y-%m-%d %H:%M:%S')
with dataset(selected_database) as database:
query = "insert into ticket_notes (ticket_id, user_id, note) VALUES (%(ticket_id)s, %(user_id)s, %(note)s);"
database.execute(query, data)
return database.last_id()
def new_ticket_note_delme(data):
data['status_id'] = data.get('status_id', STATUS_NEW)
data['updated'] = time.strftime('%Y-%m-%d %H:%M:%S')
required_values(data, required={'ticket_id', 'user_id', 'note'})
with dataset(selected_database) as database:
query = "insert into ticket_notes (ticket_id, user_id, note) VALUES (%(ticket_id)s, %(user_id)s, %(note)s);"
database.execute(query, data)
data['ticket_note_id'] = database.last_id()
def execute(self, data):
self.set(data)
with dataset(selected_database) as database:
query = u"select id from user_projects where user_id=%(user_id)s and project_id=%(project_id)s;"
result = database.fetchone(query, self.data)
if result is None:
database.execute(self.query, self.data)
return database.last_id()
return result['id']
def execute(self, data):
self.set(data)
with dataset(selected_database) as database:
query = u"select id from milestones where project_id=%(project_id)s and title=%(title)s;"
result = database.fetchone(query, self.data)
if result:
return result['id']
database.execute(self.query, self.data)
return database.last_id()
return None
def get_user_info_old(data):
required = {'username', 'password'}
try:
if not data.viewkeys() >= {'username', 'password'}:
raise ValueError
with dataset(selected_database) as database:
user_data = database.fetchone(sql = load_sql('get_user_details_by_username.sql'), values=data)
if not user_data:
raise ValueError
except ValueError:
return None
return user_data
def execute(self, data):
print 'execute new'
self.set(data)
data['status_id'] = data.get('status_id', STATUS_NEW)
data['updated'] = time.strftime('%Y-%m-%d %H:%M:%S')
data['selected_image'] = data.get('selected_image', '')
with dataset(selected_database) as database:
query = "select * from projects where title=%(title)s;"
result = database.fetchone(query, data)
if result is None:
print self.query
database.execute(self.query, data)
return database.last_id()
return result['id']
def ticket_assign_to_user(data):
if not data.viewkeys() >= {'ticket_id', 'user_id'}:
raise ValueError
with dataset(selected_database) as database:
query = open(os.path.abspath('./sql/user_details.sql')).read()
user_data = database.fetchone(query, values=data)
if not user_data:
raise ValueError
data['team_id'] = user_data.get('team_id')
print data
query = "update ticket_relations set user_id=%(user_id)s, team_id=%(team_id)s where ticket_id=%(ticket_id)s;"
return database.execute(query, data)
def execute(self, data):
self.set(data)
with dataset(selected_database) as database:
query = u"select cid from tickets where cid=%(cid)s;"
result = database.fetchone(query, self.data)
if result is None:
query = u"insert into tickets (title, owner_user_id, updated, cid) VALUES (%(title)s, %(owner_user_id)s, %(updated)s, %(cid)s);"
database.execute(query, self.data)
data['ticket_id'] = database.last_id()
# add relationship data for new ticket
query = u"insert into ticket_relations (ticket_id, user_id, project_id) VALUES (%(ticket_id)s, %(owner_user_id)s, %(project_id)s);"
database.execute(query, self.data)
return 0
def clean():
with dataset(selected_database) as database:
database.fetchone("truncate plugin_mantis_users", [])
database.fetchone("truncate plugin_mantis_projects", [])
database.fetchone("truncate plugin_mantis_user_projects", [])
database.fetchone("truncate plugin_mantis_tickets", [])
database.fetchone("truncate plugin_mantis_ticket_notes", [])
database.fetchone("truncate milestones", [])
database.fetchone("truncate users", [])
database.fetchone("truncate users", [])
database.fetchone("truncate projects", [])
database.fetchone("truncate user_projects", [])
database.fetchone("truncate tickets", [])
database.fetchone("truncate ticket_relations", [])
database.fetchone("truncate ticket_notes", [])
def execute(self):
data['status_id'] = data.get('status_id', STATUS_NEW)
data['updated'] = time.strftime('%Y-%m-%d %H:%M:%S')
required_values(data, required={'title', 'owner_user_id', 'milestone_id', 'status_id', 'updated'})
with dataset(selected_database) as database:
database.execute(self.query(), data)
data['ticket_id'] = database.last_id()
# add relationship data for new ticket
database.execute(self.query(), data)
if data.get('user_id'):
ticket_assign_to_user(data)
if data.get('notes'):
create_ticket_note(data)
return 0
def execute(self, data):
self.set(data)
with dataset(selected_database) as database:
query = u"select ticket_id from plugin_mantis_tickets where mantis_ticket_id=%(cid)s;"
result = database.fetchone(query, self.data)
if result is None:
query = u"insert into tickets (title, owner_user_id, updated) VALUES (%(title)s, %(owner_user_id)s, %(updated)s);"
database.execute(query, self.data)
data['ticket_id'] = database.last_id()
query = u"insert into ticket_notes (ticket_id, user_id, note) VALUES (%(ticket_id)s, %(user_id)s, %(description)s);"
database.execute(query, self.data)
#data['ticket_notes_id'] = database.last_id()
# add relationship data for new ticket
self.update_query('insert_ticket.sql')
#~ query = u"""insert into ticket_relations
#~ (ticket_id, user_id, project_id, milestone_id, status_id)
#~ VALUES
#~ (%(ticket_id)s, %(owner_user_id)s, %(project_id)s, %(milestone_id)s, %(status_id)s)
#~ ON DUPLICATE KEY UPDATE
#~ user_id=VALUES(user_id), project_id=VALUES(project_id);"""
self.execute(query, self.data)
query = u'insert into plugin_mantis_tickets (ticket_id, mantis_ticket_id) VALUES (%(ticket_id)s, %(cid)s);'
database.execute(query, self.data)
else:
print result
data['ticket_id'] = result['ticket_id']
for note in data.get('notes', []):
note_data = {'ticket_id': data['ticket_id'], 'user_id': 1, 'note': note[1], 'mantis_ticket_note_id': note[0]}
query = "select mantis_ticket_note_id from plugin_mantis_ticket_notes where mantis_ticket_note_id=%(mantis_ticket_note_id)s;"
result = database.fetchone(query, note_data)
if result is None:
query = u"insert into ticket_notes (ticket_id, user_id, note) VALUES (%(ticket_id)s, %(user_id)s, %(note)s);"
database.execute(query, note_data)
note_data['mantis_ticket_note_id'] = note[0]
note_data['ticket_note_id'] = database.last_id()
query = u"insert into plugin_mantis_ticket_notes (ticket_note_id, mantis_ticket_note_id) VALUES (%(ticket_note_id)s, %(mantis_ticket_note_id)s);"
database.execute(query, note_data)
return 0
def execute(self, data):
self.set(data)
with dataset(selected_database) as database:
# check if user exists and add if they do not
if [user for user in self.user_cache if user[-1] == self.data.get('email')]:
return
database.execute(self.query, self.data)
user = [(database.last_id(), self.data['email'])]
user_id = database.last_id()
self.user_cache.append((user_id, self.data['mantis_user_id'], self.data['email'], self.data['username']))
data['user_id'] = user[0][0]
database.execute('insert into plugin_mantis_users (user_id, mantis_user_id) VALUES (%(user_id)s, %(mantis_user_id)s);', self.data)
#~ query = u"select user_id, mantis_user_id from plugin_mantis_users where mantis_user_id=%(mantis_user_id)s;"
#~ result = database.fetchone(query, self.data)
#~ if result is None:
#~ query = "select * from users where email=%(email)s;"
#~ result = database.fetchone(query, data)
#~ user = [user for user in self.user_cache if user[1] == self.data.get('email')]
#~ if not user:
#~ print 'inserting user'
#~ database.execute(self.query, self.data)
#~ user = [(database.last_id(), self.data['email'])]
#~ self.user_cache.append((user[0], self.data['mantis_user_id'], self.data['email']))
#~ data['user_id'] = user[0][0]
#~ database.execute('insert into plugin_mantis_users (user_id, mantis_user_id) VALUES (%(user_id)s, %(mantis_user_id)s);', self.data)
#~ else:
#~ print 'cahced user'
#~ data['user_id'] = user[0][0]
# add relationship data for new ticket
print user
#~ query = "select * from user_projects where user_id=%(user_id)s AND project_id=%(project_id)s;"
#~ print query
#~ print data
#~ result = database.fetchone(query, data)
#~ if result is None:
#~ query = u"insert into user_projects (user_id, project_id) VALUES (%(user_id)s, %(project_id)s);"
#~ database.execute(query, self.data)
return 0
def create_new_ticket(data):
data['status_id'] = data.get('status_id', STATUS_NEW)
data['updated'] = time.strftime('%Y-%m-%d %H:%M:%S')
required_values(data, required={'title', 'owner_user_id', 'milestone_id', 'status_id', 'updated'})
with dataset(selected_database) as database:
query = u"insert into tickets (title, owner_user_id, updated) VALUES (%(title)s, %(owner_user_id)s, %(updated)s);"
print query
database.execute(query, data)
data['ticket_id'] = database.last_id()
# add relationship data for new ticket
query = u"insert into ticket_relations (ticket_id, user_id, milestone_id, project_id) VALUES (%(ticket_id)s, %(owner_user_id)s, %(milestone_id)s, %(project_id)s);"
database.execute(query, data)
if data.get('user_id'):
ticket_assign_to_user(data)
if data.get('notes'):
create_ticket_note(data)
return 0
def create_new_project(data):
data['status_id'] = data.get('status_id', STATUS_NEW)
data['updated'] = time.strftime('%Y-%m-%d %H:%M:%S')
required_values(data, required={'title', 'user_id'})
with dataset(selected_database) as database:
query = "select * from projects where title=%(title)s;"
result = database.fetchone(query, data)
if result is None:
query = "insert into projects (title, user_id, status_id) VALUES (%(title)s, %(user_id)s, %(status_id)s);"
print data
database.execute(query, data)
data['project_id'] = database.last_id()
query = "insert into user_projects (user_id, project_id) VALUES (%(user_id)s, %(project_id)s);"
print query
print data
database.execute(query, data)
data['project_id'] = database.last_id()
query = "insert into plugin_mantis_projects (mantis_project_id, project_id) VALUES (%(mantis_id)s, %(project_id)s);"
database.execute(query, data)
return data['project_id']
return result['id']
def __iter__(self):
with dataset(selected_database) as database:
for row in database.fetchall(self.query(), values=self.data):
yield row
if self.count_rows is True:
self.total_rows = database.fetchone('SELECT FOUND_ROWS();').get('FOUND_ROWS()', 0)
def get(self):
with dataset(selected_database) as database:
print self.query()
return database.fetchone(self.query(), values=self.data)
def get(self):
with dataset(selected_database) as database:
return database.fetchone(self.query(True), values=self.data)
def get_row_count():
with dataset(selected_database) as database:
return database.fetchone('SELECT FOUND_ROWS();').get('FOUND_ROWS()', 0)
def __iter__(self):
with dataset(selected_database) as database:
for row in database.fetchall(self.query(), values=self.data):
row['status'] = STATUS_LIST.get(row['status_id'])
yield row
self.total_rows = database.fetchone('SELECT FOUND_ROWS();').get('FOUND_ROWS()', 0)
def lookup_mantis_project_id(data):
required_values(data, required={'project_id'})
with dataset(selected_database) as database:
query = "select * from plugin_mantis_projects where id=%(project_id)s;"
return database.fetchone(query, data)
def populate_project_cache(self):
with dataset(selected_database) as database:
for row in database.fetchall(self.query, []):
self.project_cache.append((row['mantis_project_id'], row['project_id']))
def execute(self, data):
self.set(data)
with dataset(selected_database) as database:
database.execute(self.query(), self.data)