def handleAuthLogonChallenge(self, pkt):
data = ST_AUTH_LOGON_CHALLENGE_S.parse(pkt)
#print(data)
self.srp.set_I(self.env['username'])
self.srp.set_P(self.env['userpass'])
self.srp.set_B(convertool.strToInt(data.SRP_B[::-1]))
self.srp.set_g(convertool.strToInt(data.SRP_g[::-1]))
self.srp.set_N(convertool.strToInt(data.SRP_N[::-1]))
self.srp.set_s(convertool.strToInt(data.SRP_s[::-1]))
self.srp.generate_a()
self.srp.calculate_A()
self.srp.calculate_x()
self.srp.calculate_v()
self.srp.calculate_u()
self.srp.calculate_S_client()
self.srp.calculate_K()
self.srp.calculate_M1()
self.srp.calculate_M2()
# Store the sessionkey in the DB
cursor = db.cursor()
cursor.execute('UPDATE accounts SET client_K = ? WHERE username = ?', [convertool.intToHex(self.srp.get_K(), 80), self.env['username']])
db.commit()
r = ST_AUTH_LOGON_PROOF_C.build(Container(
opcode = 1,
SRP_A = convertool.intToStr(self.srp.get_A())[::-1],
SRP_M1 = convertool.intToStr(self.srp.get_M1()),
CRC = chr(0) * 20, # don't know how to calculate it
unk = 0
))
self.writebuffer.append(r)
def get_comments_from_site(self, limit=None, store_in_db=True):
subreddit = self.session.get_subreddit(self.subreddit)
# get the newest comment we've previously seen as a stopping point
last_comment = (
db.query(Comment)
.filter_by(subreddit=self.subreddit)
.order_by(Comment.date.desc())
.first()
)
seen_ids = set()
comments = []
for comment in subreddit.get_comments(limit=limit):
comment = Comment(comment)
if last_comment:
if (comment.id == last_comment.id or
comment.date <= last_comment.date):
break
# somehow there are occasionally duplicates - skip over them
if comment.id in seen_ids:
continue
seen_ids.add(comment.id)
comments.append(comment)
if store_in_db:
db.add(comment)
if store_in_db:
db.commit()
return comments
def handleRealmList(self, pkt):
if not self.env['m_realms']:
self.env['condition'].acquire()
while not self.env['realms']:
self.env['condition'].wait()
# Retrieve the realm address
cursor = db.cursor()
rn = cursor.execute('SELECT realm_name FROM accounts WHERE username = ?', [self.env['username']]).fetchone()[0] # not safe
rn = rn.encode('latin-1') # maybe realms name are in unicode need to check
cursor.execute('UPDATE accounts SET realm_address = ? WHERE username = ?', [self.env['realms'][rn].address, self.env['username']])
db.commit()
# Alter it and send the altered version
m_realms = self.env['realms']
m_realms[rn].address = '127.0.0.1:8085' # need to change to the proxy address
m_realms[rn].name += ' - PROXY'
c = Container(
unk1 = 0,
nb_realms = len(m_realms),
Realm = m_realms.values(),
unk2 = 0x10,
unk3 = 0
)
pkt_p = ST_REALM_LIST_S_PAYLOAD.build(c)
c = Container(
opcode = 0x10,
size = len(pkt_p)
)
pkt_h = ST_REALM_LIST_S_HEADER.build(c)
self.env['m_realms'] = pkt_h + pkt_p
self.env['condition'].release()
self.writebuffer.append(self.env['m_realms'])
def handleAuthLogonProof(self, pkt):
data = ST_AUTH_LOGON_PROOF_C.parse(pkt)
#print(data)
self.srp.set_A(convertool.strToInt(data.SRP_A[::-1]))
self.srp.calculate_u()
self.srp.calculate_S_server()
self.srp.calculate_K()
self.srp.calculate_M1()
self.srp.calculate_M2()
# Store the sessionkey in the DB
cursor = db.cursor()
cursor.execute('UPDATE accounts SET server_K = ? WHERE username = ?', [convertool.intToHex(self.srp.get_K(), 80), self.env['username']])
db.commit()
# Check for authentification correctness
if data.SRP_M1.encode('hex') == '%x' % self.srp.get_M1():
print('M1 Matches !')
else:
print('Something goes wrong during authentification :(')
# Create the response packet
r = ST_AUTH_LOGON_PROOF_S.build(Container(
opcode = 01,
error = 0,
SRP_M2 = convertool.intToStr(self.srp.get_M2()),
unk1 = 0x00800000,
unk2 = 0,
unk3 = 0
))
def post(self, post_id):
user = self.current_user
post_id = int(post_id)
post = db.query(Post).get(post_id)
if not post:
raise tornado.web.HTTPError(404)
else:
origin_content = self.get_argument("comment-content", "")
content = md(origin_content)
if origin_content == "":
self.redirect(self.next_url)
return
comment = Comment(post_id=post_id, user_id=user.id, content=content, origin_content=origin_content)
db.add(comment)
db.commit()
the_comment = (
db.query(Comment).order_by(sa.desc(Comment.created_at)).filter(Comment.user_id == user.id).first()
)
if self.is_ajax():
self.write(
tornado.escape.json_encode(
{
"username": user.name,
"avatar": user.get_avatar(size=24),
"time": formatDate(int(time.time())),
"content": content,
}
)
)
if post.user_id != user.id:
db.add(Notifier(post_id=post_id, who_id=user.id, whom_id=post.user_id, comment_id=the_comment.id))
db.commit()
if content.find("@") != -1:
put_notifier(comment=comment, post=post)
def get_submissions_from_site(self, limit=None, store_in_db=True):
subreddit = self.session.get_subreddit(self.subreddit)
# get the newest submission we've previously seen as a stopping point
last_submission = (
db.query(Submission)
.filter_by(subreddit=self.subreddit)
.order_by(Submission.date.desc())
.first()
)
seen_ids = set()
submissions = []
for submission in subreddit.get_new(limit=limit):
submission = Submission(submission)
if last_submission:
if (submission.id == last_submission.id or
submission.date <= last_submission.date):
break
# somehow there are occasionally duplicates - skip over them
if submission.id in seen_ids:
continue
seen_ids.add(submission.id)
submissions.append(submission)
if store_in_db:
db.add(submission)
if store_in_db:
db.commit()
return submissions
def create(account_id=None,
application_name=None,
application_guid=None,
application_key=None,
application_secret=None,
application_algorithm=None,
created_user_id=None,
app_metadata=None,
training_status=None):
try:
application = Applications(account_id=account_id,
application_name=application_name,
application_guid=application_guid,
application_key=application_key,
application_secret=application_secret,
application_algorithm=application_algorithm,
created_user_id=created_user_id,
app_metadata=app_metadata,
training_status=training_status)
db.add(application)
db.commit()
return application.application_id
except Exception as e:
db.rollback()
raise Exception(e.message)
def sell_product(product_id):
product = db.query(Product).filter_by(product_id=product_id).first()
if product.status == 'in_stock':
product.status = 'sold_out'
db.commit()
return True
return False
def submit():
errors = 0
for i in range(len(studentdata)):
if studentdataEntries[i].get() != studentdataValues[i]:
try:
cursor.execute(
'update studentdata set %s = %s where studentId = %s'
% (studentdata[i], studentdataEntries[i].get(),
studentId))
except:
messagebox.showerror('Student Info',
'Invalid input')
errors += 1
for i in range(len(parentinfo)):
if parentinfoEntries[i].get() != parentinfoValues[i]:
try:
cursor.execute(
'update parentinfo set %s = %s where studentId = %s'
% (parentinfo[i], parentinfoEntries[i].get(),
studentId))
except:
messagebox.showerror('Student Info',
'Invalid input')
errors += 1
db.commit()
if errors == 0:
messagebox.showinfo('Student Info',
'Data successfully updated!')
def delete_reply(pid,rid):
feed = db.query(Reply).filter_by(id=rid).first()
if not feed:
abort(404)
db.delete(feed)
db.commit()
return redirect(u'/posts/post/'+pid)
def save():
# app.logger.debug(request.data)
jsonData = json.loads(request.data)
if 'Event-Name' in request.headers:
save_event_data(request)
if 'optOut' in jsonData and jsonData['optOut']:
return get_entities()
entity = None
data = jsonData['entity']
data["ip"] = request.remote_addr
data["edit_type"] = None
if data['id']:
entity = Entity.query.get(data['id'])
elif data['name']:
data["edit_type"] = "create"
entity = Entity(data['name'])
db.add(entity)
db.commit()
if entity:
if not data["edit_type"]:
data["edit_type"] = "update"
update(entity, data)
return get_entities()
def on_put_user(self, req, resp, id):
try:
identificador = int(id)
except:
erro = {'mensagem': 'Id informado invalido'}
resp.status = falcon.HTTP_400
resp.body = json.dumps(erro)
return
cursor = db.cursor(dictionary=True)
cursor.execute("SELECT * FROM users WHERE id = %s", (id, ))
usuario = cursor.fetchone()
if (usuario is None):
erro = {
'mensagem': 'Nao ha registro de usuario para o id informado.'
}
resp.status = falcon.HTTP_400
resp.body = json.dumps(erro)
return
usuarioAlterado = json.loads(req.stream.read().decode('utf-8'))
tuplaEdicaoDeUsuario = (usuarioAlterado['user_name'],
usuarioAlterado['fullname'])
cursor = db.cursor(dictionary=True)
cursor.execute("UPDATE users SET user_name = %s, fullname = %s",
(tuplaEdicaoDeUsuario))
db.commit()
resp.status = falcon.HTTP_200
resp.body = json.dumps(usuarioAlterado, default=str)
def add_reply(pid):
# name = self.get_argument("reply[name]", default='')
# email = self.get_argument("reply[email]", default='')
# website = self.get_argument("reply[website]", default='')
# origin_content = self.get_argument("reply[content]", default='')
name = request.form["reply[name]"]
email = request.form["reply[email]"]
website = request.form["reply[website]"]
origin_content = request.form["reply[content]"]
content = markdown.markdown(formatText(origin_content))
if name == "":
return redirect("/post/%d" % int(pid), error=u"请填入名字")
if email == "":
return redirect("/post/%d" % int(pid), error=u"请填入邮箱地址")
if origin_content == "":
return redirect("/post/%d" % int(pid), error=u"请输入评论内容")
number = db.query(Reply).filter(Reply.pid == pid).count() + 1
db.add(
Reply(
pid=int(pid),
name=name,
email=email,
website=website,
content=content,
origin_content=origin_content,
number=number,
)
)
db.commit()
base.replyerSet(name, email, website)
return redirect("/post/%d" % (int(pid)))
def import_source():
"""Parse `images.toml` and update databases"""
with open('images.toml', 'rt', encoding='utf8') as infile:
newdefinitions = toml.load(infile)
# images section
urls = set()
for keyw, images in newdefinitions['images'].items():
if isinstance(images, list):
for image in images:
db.merge(Image(image, keyw))
if image in urls:
print('Duplicate: ', image)
else:
urls.add(image)
else:
db.merge(Image(images, keyw))
if images in urls:
print('Duplicate: ', images)
else:
urls.add(images)
db.merge(Keyword(keyw, keyw))
# alias section
for keyw, kalias in newdefinitions['alias'].items():
db.merge(Keyword(keyw, kalias))
# hidden section
for keyw, hidden in newdefinitions['hidden'].items():
db.query(Keyword).get(keyw).hidden = hidden
# ignored
for keyw, ignored in newdefinitions['ignored'].items():
KeywordCandidate.get_or_create(keyw).ignored = ignored
db.commit()
async def handle_editmessage(message: discord.Message, client: discord.Client,
server_data: ServerData):
channel: discord.TextChannel = message.channel
author: discord.Member = message.author
await channel.send(embed=discord.Embed(
color=discord.Color.gold(),
description=
"Ok please send me now the new message and i change it for you"))
def check(m):
if m.author.id == author.id and m.channel.id == channel.id:
return m
newmessage = await client.wait_for("message", check=check, timeout=None)
nmsg = newmessage.content
server_data.ruletext = nmsg
rules_channel_id = server_data.ruleschannel
rules_channel = client.get_channel(int(rules_channel_id))
new_message = await rules_channel.send(embed=discord.Embed(
color=discord.Color.green(), description=nmsg).set_footer(
text="Please accept the rules with ✅ or decline them with ❌ "))
server_data.messageid = new_message.id
server_data.save()
db.commit()
await new_message.add_reaction("✅")
await new_message.add_reaction("❌")
def handleAuthLogonChallenge(self, pkt):
data = ST_AUTH_LOGON_CHALLENGE_S.parse(pkt)
#print(data)
self.srp.set_I(self.env['username'])
self.srp.set_P(self.env['userpass'])
self.srp.set_B(convertool.strToInt(data.SRP_B[::-1]))
self.srp.set_g(convertool.strToInt(data.SRP_g[::-1]))
self.srp.set_N(convertool.strToInt(data.SRP_N[::-1]))
self.srp.set_s(convertool.strToInt(data.SRP_s[::-1]))
self.srp.generate_a()
self.srp.calculate_A()
self.srp.calculate_x()
self.srp.calculate_v()
self.srp.calculate_u()
self.srp.calculate_S_client()
self.srp.calculate_K()
self.srp.calculate_M1()
self.srp.calculate_M2()
# Store the sessionkey in the DB
cursor = db.cursor()
cursor.execute(
'UPDATE accounts SET client_K = ? WHERE username = ?',
[convertool.intToHex(self.srp.get_K(), 80), self.env['username']])
db.commit()
r = ST_AUTH_LOGON_PROOF_C.build(
Container(
opcode=1,
SRP_A=convertool.intToStr(self.srp.get_A())[::-1],
SRP_M1=convertool.intToStr(self.srp.get_M1()),
CRC=chr(0) * 20, # don't know how to calculate it
unk=0))
self.writebuffer.append(r)
def register_post():
username = request.form.get('username')
email = request.form.get('email')
name = request.form.get('name')
password = request.form.get('password')
user = User.query.filter_by(email=email).first(
) # if this returns a user, then the email already exists in database
# if a user is found, we want to redirect back to signup page so user can try again
if user:
flash('Email address already exists')
return redirect(url_for('register'))
# create new user with the form data. Hash the password so plaintext version isn't saved.
new_user = User(username=username,
email=email,
name=name,
password=generate_password_hash(password, method='sha256'))
# add the new user to the database
db.add(new_user)
db.commit()
# when we add the user to the database, we will redirect to the login route
return redirect(url_for('login'))
def handleAuthLogonProof(self, pkt):
data = ST_AUTH_LOGON_PROOF_C.parse(pkt)
#print(data)
self.srp.set_A(convertool.strToInt(data.SRP_A[::-1]))
self.srp.calculate_u()
self.srp.calculate_S_server()
self.srp.calculate_K()
self.srp.calculate_M1()
self.srp.calculate_M2()
# Store the sessionkey in the DB
cursor = db.cursor()
cursor.execute(
'UPDATE accounts SET server_K = ? WHERE username = ?',
[convertool.intToHex(self.srp.get_K(), 80), self.env['username']])
db.commit()
# Check for authentification correctness
if data.SRP_M1.encode('hex') == '%x' % self.srp.get_M1():
print('M1 Matches !')
else:
print('Something goes wrong during authentification :(')
# Create the response packet
r = ST_AUTH_LOGON_PROOF_S.build(
Container(opcode=01,
error=0,
SRP_M2=convertool.intToStr(self.srp.get_M2()),
unk1=0x00800000,
unk2=0,
unk3=0))
def handleRealmList(self, pkt):
if not self.env['m_realms']:
self.env['condition'].acquire()
while not self.env['realms']:
self.env['condition'].wait()
# Retrieve the realm address
cursor = db.cursor()
rn = cursor.execute(
'SELECT realm_name FROM accounts WHERE username = ?',
[self.env['username']]).fetchone()[0] # not safe
rn = rn.encode(
'latin-1') # maybe realms name are in unicode need to check
cursor.execute(
'UPDATE accounts SET realm_address = ? WHERE username = ?',
[self.env['realms'][rn].address, self.env['username']])
db.commit()
# Alter it and send the altered version
m_realms = self.env['realms']
m_realms[
rn].address = '127.0.0.1:8085' # need to change to the proxy address
m_realms[rn].name += ' - PROXY'
c = Container(unk1=0,
nb_realms=len(m_realms),
Realm=m_realms.values(),
unk2=0x10,
unk3=0)
pkt_p = ST_REALM_LIST_S_PAYLOAD.build(c)
c = Container(opcode=0x10, size=len(pkt_p))
pkt_h = ST_REALM_LIST_S_HEADER.build(c)
self.env['m_realms'] = pkt_h + pkt_p
self.env['condition'].release()
self.writebuffer.append(self.env['m_realms'])
def update_finance(finances, ftype):
# Delete any finances which have been removed.
new_finances = [finance['id'] for finance in finances if finance['id']]
if ftype == 'revenues':
entity.revenues = [revenue for revenue in entity.revenues if revenue.id in new_finances]
elif ftype == 'expenses':
entity.expenses = [expense for expense in entity.expenses if expense.id in new_finances]
# Do this or else list comprehensions don't work as expected.
db.commit()
# Create or update.
for finance in finances:
if finance['id']:
# Finance exists, update data.
oldfinance = Finance.query.get(finance['id'])
if oldfinance.amount != finance['amount']:
oldfinance.amount = finance['amount']
app.logger.debug('UPDATING ' + ftype + ' AMOUNT: ' + str(oldfinance.amount))
if oldfinance.year != finance['year']:
oldfinance.year = finance['year']
app.logger.debug('UPDATING ' + ftype + ' YEAR: ' + str(oldfinance.year))
else:
# Finance doesn't exist, create it.
if ftype is 'revenues':
revenue = Revenue(finance['amount'], finance['year'])
entity.revenues.append(revenue)
app.logger.debug('NEW REVENUE -- ' + str(revenue.year) + ': ' + str(revenue.amount))
elif ftype is 'expenses':
expense = Expense(finance['amount'], finance['year'])
entity.expenses.append(expense)
app.logger.debug('NEW EXPENSE -- ' + str(expense.year) + ': ' + str(expense.amount))
db.commit()
def analysis_new_mission_commonparameters_tagged_contains(city):
lines = commonparameters(city)
db = pymysql.connect(host=HOST,
user=USER,
password=PASSWD,
db=DB,
charset='utf8')
cursor = db.cursor()
for l in range(0, len(lines)):
cgi = lines[l][4]
district = lines[l][3]
shapes = gaodemapscene(district, city, 120000)
Path = mpath.Path
for s in range(0, len(shapes)):
shape = shapes[s][-1]
shape_array = np.array([
float(i)
for i in shape.replace('|', ',').replace(';', ',').split(',')
]).reshape(-1, 2)
point = (float(lines[l][15]), float(lines[l][14]))
p = Path(shape_array)
if p.contains_points([point]):
query_commonparameters_tagged = """UPDATE {} set residential_flag='1' where cgi='{}'""".format(
TABLE_NAME_ANALYSIS_Commonparameters, cgi)
cursor.execute(query_commonparameters_tagged)
break
print('%.2f%%' % (l / len(lines) * 100))
cursor.close()
db.commit()
db.close()
def update_log(id, text):
sql = ("UPDATE logs SET text = %s WHERE id = %s")
cursor.execute(sql, (text, id))
db.commit()
print("Log {} updated".format(id))
def on_post(self, req, resp):
novoLivro = json.loads(req.stream.read().decode('utf-8'))
# realizar verificação do author_id
# realizar verificação do genre_id
# realizar verificação se o author_id recebido existe no banco de dados
# realizar verificação se o genre_id recebido existe no banco de dados
tuplaLivro = (novoLivro['title'], novoLivro['sinopsis'],
novoLivro['release_date'], novoLivro['thumbnail_url'],
novoLivro['author_id'])
cursor = db.cursor(dictionary=True)
cursor.execute(
"INSERT INTO books (title, sinopsis, release_date, thumbnail_url, author_id) VALUES (%s, %s, %s, %s, %s)",
(tuplaLivro))
bookId = cursor.lastrowid
tuplaLivroGenero = (bookId, novoLivro['genre_id'])
cursor.execute(
"INSERT INTO books_genres (book_id, genre_id) VALUES (%s, %s)",
tuplaLivroGenero)
db.commit()
resp.status = falcon.HTTP_201
resp.body = json.dumps(novoLivro, default=str)
def add_letter(id, letter):
x = ord(letter)
if (x > 60 and x < 91):
x = x + 32
if (x > 96 and x < 123):
t = get_letter(id, chr(x)) + 1
sql = ("UPDATE logs SET " + letter + " = %s WHERE id = %s")
cursor.execute(sql, (t, id))
db.commit()
elif (x > 47 and x < 58):
m = 'zero'
if (letter == '0'):
m = 'zero'
elif (letter == '1'):
m = 'one'
elif (letter == '2'):
m = 'two'
elif (letter == '3'):
m = 'three'
elif (letter == '4'):
m = 'four'
elif (letter == '5'):
m = 'five'
elif (letter == '6'):
m = 'six'
elif (letter == '7'):
m = 'seven'
elif (letter == '8'):
m = 'eight'
elif (letter == '9'):
m = 'nine'
t = get_letter(id, chr(x)) + 1
sql = ("UPDATE logs SET " + m + " = %s WHERE id = %s")
cursor.execute(sql, (t, id))
db.commit()
def _check_temp_password(dict, password):
"""
Verifies a secondary (temp) password for the identity.
Params:
dict (dictionary): A Represention of an identity.
Pulled straight from database.
password (string): The temp password of the identity to authenticate.
Returns:
None
Raises:
AuthFailed - Temp password has expired.
"""
temp_password_hash = dict['temp_password_hash']
if temp_password_hash:
bcrypt = Bcrypt(app)
did_pass = bcrypt.check_password_hash(temp_password_hash, password)
if did_pass:
username = dict['username']
try:
connection = db.connection()
db.call(connection, 'delete_identity_temp_password',
[username])
db.commit(connection)
except Exception:
pass # Let's not let a db issue stop us from continuing here.
return did_pass
return False
def signup():
try:
name = request.form.get("name")
email = request.form.get("email")
password = (hashlib.sha256(
request.form.get("password").encode())).hexdigest()
if name is "" or password is "" or email is "":
raise Exception
if db.execute("SELECT * FROM user_details WHERE email = :username", {
"username": email
}).rowcount == 0:
db.execute(
"INSERT INTO user_details (name, email, password) VALUES (:name, :email, :password)",
{
"name": name,
"email": email,
"password": password
})
db.commit()
return render_template(
"success.html", message="Your account is created successfully")
else:
return render_template("error.html",
message="Email already Registered")
except Exception as e:
return render_template("error.html", message="Invalid URL.")
def update_controller(timer,func):
sql_update = "UPDATE controller SET next_run = '%s', status = 'running' WHERE function = '%s'" % (func,timer)
try:
cursor.execute(sql_update)
db.commit()
except:
pass
def register():
"""Register user"""
if request.method == "POST":
username = request.form.get("username")
usr = db.execute("SELECT * FROM users WHERE username = :username", {
"username": username
}).fetchone()
# If db.execute returns a value to `usr` then the username is taken
if usr is not None:
flash("Name already in use.")
return render_template("register.html")
password = request.form.get("new-password")
if not password or len(password) < 5:
flash("Password must contain at least 5 characters.")
return render_template("register.html")
passhash = generate_password_hash(password)
db.execute(
"INSERT INTO users (username, password) VALUES \
(:username, :password)", {
"username": username,
"password": passhash
})
db.commit()
return redirect("/")
else:
return render_template("register.html")
def update_edit(ip, e_type):
edit = Edit(ip)
edit.entity_id = entity.id
edit.edit_type = e_type
edit.edit_time = datetime.utcnow()
db.add(edit)
db.commit()
def update_finance(finances, ftype):
# Delete any finances which have been removed.
new_finances = [finance['id'] for finance in finances if finance['id']]
if ftype == 'revenues':
entity.revenues = [revenue for revenue in entity.revenues if revenue.id in new_finances]
elif ftype == 'expenses':
entity.expenses = [expense for expense in entity.expenses if expense.id in new_finances]
# Do this or else list comprehensions don't work as expected.
db.commit()
# Create or update.
for finance in finances:
if finance['id']:
# Finance exists, update data.
oldfinance = Finance.query.get(finance['id'])
if oldfinance.amount != finance['amount']:
oldfinance.amount = finance['amount']
app.logger.debug('UPDATING ' + ftype + ' AMOUNT: ' + str(oldfinance.amount))
if oldfinance.year != finance['year']:
oldfinance.year = finance['year']
app.logger.debug('UPDATING ' + ftype + ' YEAR: ' + str(oldfinance.year))
else:
# Finance doesn't exist, create it.
if ftype is 'revenues':
revenue = Revenue(finance['amount'], finance['year'])
entity.revenues.append(revenue)
app.logger.debug('NEW REVENUE -- ' + str(revenue.year) + ': ' + str(revenue.amount))
elif ftype is 'expenses':
expense = Expense(finance['amount'], finance['year'])
entity.expenses.append(expense)
app.logger.debug('NEW EXPENSE -- ' + str(expense.year) + ': ' + str(expense.amount))
db.commit()
def delete_log(id):
if get_log(id) != None:
sql = ("DELETE FROM logs WHERE id = %s")
cursor.execute(sql, (id,))
db.commit()
print("Log deleted")
#venv deactivate
### Add a log ###
#add_log('It is one more log', 'Jonathan')
#add_log('It is one more log', 'Bruno')
#add_log('It is one more log', 'Cristian')
### Return all logs ###
#get_logs()
### To add an id to return one log ###
#get_log(9)
### Update a log ###
#update_log(9, "Updated log")
#### Delete one log ###
#delete_log(9)
def register():
if request.method == 'POST':
name = request.form.get('username')
#double checking, since it is done in the front end
if not name:
return apology("You must provide a user name.")
row = db.execute("select * from users where name = :name", {"name":name}).fetchone()
if row is not None:
return apology('This user is already registered.')
password = request.form.get('password')
if not password:
return apology("You must provide a passord")
confirmation = request.form.get('confirmation')
if not confirmation:
return apology("You must confirm your passord.")
# end double ckeck
hash_passw = generate_password_hash(password)
if not check_password_hash(hash_passw, confirmation):
return apology('Password and Confirmation do not match.')
else:
db.execute('insert into users(name, password) values(:name,:password)',
{"name":name, "password":hash_passw})
db.commit()
flash(f"User {name} is registered.")
return render_template('index.html')
# return redirect('/')
else:
return render_template('register.html')
def update_dataconnections(connections, direction):
# Delete any connections that have been removed.
new_connections = [connection['id'] for connection in connections if connection['id']]
# Watch out for odd behavior in list iteration while deleting.
if direction is 'given':
for connection in entity.data_given:
if connection.id not in new_connections:
db.delete(connection)
elif direction is 'received':
for connection in entity.data_received:
if connection.id not in new_connections:
db.delete(connection)
db.commit()
for connection in connections:
if connection['id']:
oldconnection = Dataconnection.query.get(connection['id'])
if oldconnection.details != connection['details']:
oldconnection.details = connection['details']
elif 'entity_id' in connection:
otherentity = Entity.query.get(connection['entity_id'])
newconnection = Dataconnection()
if connection['details']:
newconnection.details = connection['details']
if direction is 'given':
entity.data_given.append(newconnection)
otherentity.data_received.append(newconnection)
elif direction is 'received':
entity.data_received.append(newconnection)
otherentity.data_given.append(newconnection)
db.commit()
def update_location(location, json):
location.address_line = json[
'address_line'] if 'address_line' in json else None
location.locality = json['locality'] if 'locality' in json else None
location.district = json['district'] if 'district' in json else None
location.postal_code = json[
'postal_code'] if 'postal_code' in json else None
location.country = json['country'] if 'country' in json else None
location.country_code = json[
'country_code'] if 'country_code' in json else None
location.latitude = json['coordinates'][
0] if 'coordinates' in json else None
location.longitude = json['coordinates'][
1] if 'coordinates' in json else None
app.logger.debug(
"********************\nHaving an existential crisis\n%s\n********************",
entity.entitytype)
if entity.entitytype == 'Individual':
location.address_line = None
location.postal_code = None
location.latitude = None
location.longitude = None
app.logger.debug(
"********************\n%s\n********************", location)
db.commit()
def on_put(self, req, resp):
try:
identificador = int(id)
except:
erro = {'mensagem': 'Id informado invalido'}
resp.status = falcon.HTTP_400
resp.body = json.dumps(erro)
return
cursor = db.cursor(dictionary=True)
cursor.execute("SELECT * FROM genres WHERE id = %s", (id, ))
genero = cursor.fetchone()
if (genero is None):
erro = {
'mensagem': 'Nao ha registro de genero para o id informado.'
}
resp.status = falcon.HTTP_400
resp.body = json.dumps(erro)
return
generoAlterado = json.loads(req.stream.read().decode('utf-8'))
tuplaEdicaoDeGenero = (generoAlterado['name'], )
cursor = db.cursor(dictionary=True)
cursor.execute("UPDATE genres SET name = %s", (tuplaEdicaoDeGenero))
db.commit()
resp.status = falcon.HTTP_200
resp.body = json.dumps(generoAlterado, default=str)
def update_edit(ip, e_type):
edit = Edit(ip)
edit.entity_id = entity.id
edit.edit_type = e_type
edit.edit_time = datetime.utcnow()
db.add(edit)
db.commit()
def update_dataconnections(connections, direction):
# Delete any connections that have been removed.
new_connections = [
connection['id'] for connection in connections if connection['id']
]
# Watch out for odd behavior in list iteration while deleting.
if direction is 'given':
for connection in entity.data_given:
if connection.id not in new_connections:
db.delete(connection)
elif direction is 'received':
for connection in entity.data_received:
if connection.id not in new_connections:
db.delete(connection)
db.commit()
for connection in connections:
if connection['id']:
oldconnection = Dataconnection.query.get(connection['id'])
if oldconnection.details != connection['details']:
oldconnection.details = connection['details']
elif 'entity_id' in connection:
otherentity = Entity.query.get(connection['entity_id'])
newconnection = Dataconnection()
if connection['details']:
newconnection.details = connection['details']
if direction is 'given':
entity.data_given.append(newconnection)
otherentity.data_received.append(newconnection)
elif direction is 'received':
entity.data_received.append(newconnection)
otherentity.data_given.append(newconnection)
db.commit()
def add_reply(pid):
#name = self.get_argument("reply[name]", default='')
#email = self.get_argument("reply[email]", default='')
#website = self.get_argument("reply[website]", default='')
#origin_content = self.get_argument("reply[content]", default='')
name = request.form["reply[name]"]
email = request.form["reply[email]"]
website = request.form["reply[website]"]
origin_content = request.form["reply[content]"]
content = markdown.markdown(formatText(origin_content))
if name == "":
return redirect("/post/%d" % int(pid), error=u"请填入名字")
if email == "":
return redirect("/post/%d" % int(pid), error=u"请填入邮箱地址")
if origin_content == "":
return redirect("/post/%d" % int(pid), error=u"请输入评论内容")
number = db.query(Reply).filter(Reply.pid == pid).count() + 1
db.add(
Reply(pid=int(pid),
name=name,
email=email,
website=website,
content=content,
origin_content=origin_content,
number=number))
db.commit()
base.replyerSet(name, email, website)
return redirect("/post/%d" % (int(pid)))
def update_key_people(key_people):
# Delete any key people who have been removed.
# TODO: Check for names too, in case you're getting an id from an old cleared form field.
# TODO: Make sure they're deleted from the db and not just removed from entity.key_people.
new_keypeople = [
key_person['id'] for key_person in key_people if key_person['id']
]
entity.key_people = [
key_person for key_person in entity.key_people
if key_person.id in new_keypeople
]
# Do this or else list comprehensions don't work as expected.
db.commit()
# Create or update.
for key_person in key_people:
if key_person['id']:
# Key person exists, update their name.
keyperson = Keyperson.query.get(key_person['id'])
if keyperson.name != key_person['name']:
keyperson.name = key_person['name']
app.logger.debug('UPDATED KEY PERSON NAME ' +
keyperson.name)
else:
# Key person doesn't exist, create them.
keyperson = Keyperson(key_person['name'])
entity.key_people.append(keyperson)
app.logger.debug('NEW KEY PERSON ' + keyperson.name)
db.commit()
def delete_post(pid):
feed = db.query(Post).filter_by(id=pid).first()
if not feed:
abort(404)
db.delete(feed)
db.commit()
return redirect("/posts/archive")
def get_submissions_from_site(self, limit=None, store_in_db=True):
subreddit = self.session.get_subreddit(self.subreddit)
# get the newest submission we've previously seen as a stopping point
last_submission = (
db.query(Submission)
.filter_by(subreddit=self.subreddit)
.order_by(Submission.date.desc())
.first()
)
seen_ids = set()
submissions = []
for submission in subreddit.get_new(limit=limit):
submission = Submission(submission)
if last_submission:
if (submission.id == last_submission.id or
submission.date <= last_submission.date):
break
# somehow there are occasionally duplicates - skip over them
if submission.id in seen_ids:
continue
seen_ids.add(submission.id)
submissions.append(submission)
if store_in_db:
db.add(submission)
if store_in_db:
db.commit()
return submissions
def get_comments_from_site(self, limit=None, store_in_db=True):
subreddit = self.session.get_subreddit(self.subreddit)
# get the newest comment we've previously seen as a stopping point
last_comment = (
db.query(Comment)
.filter_by(subreddit=self.subreddit)
.order_by(Comment.date.desc())
.first()
)
seen_ids = set()
comments = []
for comment in subreddit.get_comments(limit=limit):
comment = Comment(comment)
if last_comment:
if (comment.id == last_comment.id or
comment.date <= last_comment.date):
break
# somehow there are occasionally duplicates - skip over them
if comment.id in seen_ids:
continue
seen_ids.add(comment.id)
comments.append(comment)
if store_in_db:
db.add(comment)
if store_in_db:
db.commit()
return comments
def update_key_people(key_people):
# Delete any key people who have been removed.
# TODO: Check for names too, in case you're getting an id from an old cleared form field.
# TODO: Make sure they're deleted from the db and not just removed from entity.key_people.
new_keypeople = [key_person['id'] for key_person in key_people if key_person['id']]
entity.key_people = [key_person for key_person in entity.key_people if
key_person.id in new_keypeople]
# Do this or else list comprehensions don't work as expected.
db.commit()
# Create or update.
for key_person in key_people:
if key_person['id']:
# Key person exists, update their name.
keyperson = Keyperson.query.get(key_person['id'])
if keyperson.name != key_person['name']:
keyperson.name = key_person['name']
app.logger.debug('UPDATED KEY PERSON NAME ' + keyperson.name)
else:
# Key person doesn't exist, create them.
keyperson = Keyperson(key_person['name'])
entity.key_people.append(keyperson)
app.logger.debug('NEW KEY PERSON ' + keyperson.name)
db.commit()
def set_repository_phab_hosted(self, details, callsign):
connection = db.connect()
with connection.cursor() as cursor:
cursor.execute(repository_hosted_update_sql, (details, callsign))
db.commit(connection)
db.disconnect(connection)
def set_repository_policy(self, callsign, view_policy, edit_policy, push_policy):
connection = db.connect()
with connection.cursor() as cursor:
cursor.execute(repository_policy_update_sql, (view_policy, edit_policy, push_policy, callsign))
db.commit(connection)
db.disconnect(connection)
def do_update(db):
for k, v in request.forms.items():
if k.endswith('-name'):
user = db.query(m.Person).filter(m.Person.crsid == k[:-5]).one()
user.name = v.decode('utf8')
db.add(user)
db.commit()
return update_names(db)
def update_fundingconnections(connections, ftype, direction):
# Delete and connections that have been removed.
new_connections = [connection['id'] for connection in connections if connection['id']]
# TODO: See if you can make this generic to handle any set of connections for simplicity.
# TODO: Maybe list comprehensions in stead depending on how cascade='delete-orphan' works.
if ftype is 'investment':
if direction is 'given':
for connection in entity.investments_made:
if connection.id not in new_connections:
db.delete(connection)
elif direction is 'received':
for connection in entity.investments_received:
if connection.id not in new_connections:
db.delete(connection)
elif ftype is 'grant':
if direction is 'given':
for connection in entity.grants_given:
if connection.id not in new_connections:
db.delete(connection)
elif direction is 'received':
for connection in entity.grants_received:
if connection.id not in new_connections:
db.delete(connection)
db.commit()
for connection in connections:
if connection['id']:
# Connection exists, update amount and year.
oldconnection = Fundingconnection.query.get(connection['id'])
if oldconnection.amount != connection['amount']:
oldconnection.amount = connection['amount']
app.logger.debug('UPDATING ' + ftype + ' AMOUNT: ' + str(oldconnection.amount))
if oldconnection.year != connection['year']:
oldconnection.year = connection['year']
app.logger.debug('UPDATING ' + ftype + ' YEAR: ' + str(oldconnection.year))
elif 'entity_id' in connection:
# Connection doesn't exist, create it connect entities.
otherentity = Entity.query.get(connection['entity_id'])
if ftype is 'investment':
newconnection = Investment(connection['amount'], connection['year'])
if direction is 'given':
entity.investments_made.append(newconnection)
otherentity.investments_received.append(newconnection)
elif direction is 'received':
entity.investments_received.append(newconnection)
otherentity.investments_made.append(newconnection)
elif ftype is 'grant':
newconnection = Grant(connection['amount'], connection['year'])
if direction is 'given':
entity.grants_given.append(newconnection)
otherentity.grants_received.append(newconnection)
elif direction is 'received':
entity.grants_received.append(newconnection)
otherentity.grants_given.append(newconnection)
db.commit()
def post(self):
username = self.get_argument("username")
email = self.get_argument("email")
password = self.get_argument("password")
confirm_password = self.get_argument("confirm_password")
db.add(User(name=username,email=email,pw_hash= generate_password_hash(password)))
db.commit()
#self.set_cookie("email",email)
#self.currentUserSet(username)
self.redirect("/login")
def post(self):
self.checkAdmin()
title = self.get_argument("post[title]", '')
origin_content = self.get_argument("post[content]", '')
content = md(origin_content)
if title != '' and origin_content != '':
db.add(Post(title=title, content=content,
origin_content=origin_content))
db.commit()
self.redirect("/")
else:
self.render("postadd.html", error=u"标题或内容不能为空。",
title=title, origin_content=origin_content)
def create(self, rules):
connection = db.connect()
new_phid = "PHID-PLCY-prophessor%s" % (
''.join([random.choice(string.ascii_letters + string.digits) for _ in range(10)])
)
timestamp = int(time.time())
with connection.cursor() as cursor:
cursor.execute(insert_sql, (new_phid, json.dumps(rules), timestamp, timestamp))
db.commit(connection)
db.disconnect(connection)
return new_phid
def submitCall(self):
tabelas = []
camposDeRetorno = {}
camposDeBusca = {}
camposDeFiltro = {}
#leitura do input do usuario
for tabela, matriz in self.tabelas.iteritems():
camposDeRetorno[tabela] = []
camposDeBusca[tabela] = []
camposDeFiltro[tabela] = []
for coluna in matriz:
#caso seja campo de retorno
if coluna[2].isChecked():
camposDeRetorno[tabela].append(coluna[0])
if tabela not in tabelas:
tabelas.append(tabela)
#caso seja campo de busca
if str((coluna[3].text()).toUtf8()) != "":
valor = (coluna[3].text()).toUtf8()
try:
valor = float(valor)
except:
valor = str(valor)
if tabela not in tabelas:
tabelas.append(tabela)
if coluna[4].isChecked():
camposDeFiltro[tabela].append((coluna[0], valor))
else:
camposDeBusca[tabela].append((coluna[0], valor))
if self.count.isChecked():
camposDeRetorno = 'count(*)'
sql = db.sqlSelectGeneratorSearchFilter(self.schema, tabelas, camposDeRetorno,
camposDeBusca, camposDeFiltro)
DataGridView(db.query(sql), db.camposRetornoCabecalho(self.schema, camposDeRetorno),
(sql.decode("utf-8")).upper())
db.commit()
def sqlCall(self):
#abre janela de dialogo
sql, ok = QtGui.QInputDialog.getText(self, 'Inserir SQL',
'Insira o comando SQL:')
#executa sql e abre janela com resultado
if ok:
sql = str(sql)
campos = db.camposRetornoSql(sql)
if campos[0] == '*':
campos = None
db.insereSqlLog(sql)
dataGridView.DataGridView(db.query(sql), campos, sql.upper())
db.commit()
def delete(query=None):
"""
This methods deletes the record
:param query:
:return:
"""
try:
db.query(Users).\
filter_by(**query).\
delete()
db.commit()
except Exception as e:
db.rollback()
raise Exception(e.message)
def post(self, pid):
self.checkAdmin()
title = self.get_argument("post[title]", default='')
origin_content = self.get_argument("post[content]", default='')
content = md(origin_content)
post = db.query(Post).get(pid)
post.title = title
post.origin_content = origin_content
post.content = content
if title != '' and origin_content != '':
db.commit()
self.redirect("/post/%d" % (int(pid)))
else:
self.render("postedit.html", error=u"标题或内容不能为空。",
post=post)
def update_categories(categories):
# Add any new categories.
for category in categories:
if category['id']:
cat = Category.query.get(category['id'])
if cat not in entity.categories:
app.logger.debug('ADDING CATEGORY ' + cat.name)
entity.categories.append(cat)
# Delete any categories that have been removed.
new_categories = [category['id'] for category in categories]
for category in entity.categories:
if category.id not in new_categories:
app.logger.debug('REMOVING CATEGORY ' + category.name)
entity.categories.remove(category)
db.commit()
def save():
entity = None
data = json.loads(request.data)['entity']
if data['id']:
entity = Entity.query.get(data['id'])
elif data['name']:
entity = Entity(data['name'])
db.add(entity)
db.commit()
if entity:
update(entity, data)
cache.clear()
else:
print 'NO UPDATE'
return get_entities()
def add_bot(query, nerd):
"""
Adding applications to accounts
:param query:
:param bot:
:return:
"""
try:
account = db.query(Accounts). \
filter_by(**query).one()
account.nerds.append(nerd)
db.commit()
except Exception as e:
db.rollback()
raise Exception(e.message)
def add_user(query, user):
"""
Adds users to an existing account
:param query:
:param user:
:return:
"""
try:
account = db.query(Accounts). \
filter_by(**query).one()
account.users.append(user)
db.commit()
except Exception as e:
db.rollback()
raise Exception(e.message)
def update(query=None, updated_value=None):
"""
This method update the account
:param query:
:param updated_value:
:return:
"""
try:
db.query(Accounts) \
.filter_by(**query) \
.update(updated_value)
db.commit()
except Exception as e:
db.rollback()
raise Exception(e.message)
def update(query=None, new_user=None):
"""
This method update the user
:param query:
:param new_user:
:return:
"""
try:
db.query(Users) \
.filter_by(**query) \
.update(new_user)
db.commit()
except Exception as e:
db.rollback()
raise Exception(e.message)
