def send_message(messageJson):
third = messageJson['trd']
seq = messageJson['seq']
sqli = "select openid,sequence from session where third_session=%s;"
sql = "select sendtime,content,sessionid from log natural join userid where openid=%s;"
sqld = "delete from log where openid=%s;"
try:
cur.execute(sqli, [third])
temp = cur.fetchall()
if temp:
openid = temp[0][0]
seqs = temp[0][1]
if seq != seqs:
print seq, " ", seqs
return [], 0
else:
seq = seq + 1
seqs = seqs + 2
sqls = "update session set sequence=%s where third_session=%s"
cur.execute(sqls, [seqs, third])
conn.commit()
cur.execute(sql, [openid])
log = cur.fetchall()
cur.execute(sqld, [openid])
conn.commit()
if log:
return log, seq
else:
return [], seq
else:
print "no user"
return [], 0
except:
print "sql error"
return [], 0
def generateBankStatement(client):
print('\nExtrato Mensal')
client.printData()
print('\nDespesas: \n')
cur.execute(
f"SELECT category, price, date FROM purchase WHERE client_id = '{client.getId()}';"
)
purchases = cur.fetchall()
if len(purchases) > 0:
for purchase in purchases:
print(f'{purchase[0]} - R${purchase[1]:.2f} - {purchase[2]}')
else:
print('Não há nenhuma despesa registrada!')
limit = client.getMonthlyLimit()
available_credit = client.getAvailableCredit()
print(f'\nCrédito Limite (mês): R${limit:.2f}')
print(f'Crédito Gasto: R${limit - available_credit:.2f}')
print(f'Crédito Disponível: R${available_credit:.2f}')
getOption(client)
def save_session(_3rd_session, session_key, openid):
try:
sql = "select openid from session where openid=%s;"
cur.execute(sql, [openid])
temp = cur.fetchall()
if len(temp):
try:
sqlu = "update session set third_session = %s where openid = %s;"
cur.execute(sqlu, [_3rd_session, openid])
conn.commit()
except:
print "error"
else:
try:
sqli = "insert into session(third_session,session_key,openid,sequence) values(%s,%s,%s,0);"
sql1 = "insert into userid(openid,id) values(%s,%s);"
import hashlib
userid = hashlib.sha256()
userid.update(openid)
temp = userid.hexdigest()
cur.execute(sqli, [_3rd_session, session_key, openid])
conn.commit()
cur.execute(sql1, [openid, temp])
conn.commit()
return temp
except:
print "error"
except:
print "error"
return
def validateCPF(cpf):
if len(cpf) == 11 and cpf != cpf[::-1]:
cpf_numbers = []
calc = 0
for i in range(9):
cpf_numbers.append(int(cpf[i]))
calc += cpf_numbers[i] * (10 - i)
if (calc * 10) % 11 == int(cpf[-2]):
cpf_numbers.clear()
calc = 0
for j in range(10):
cpf_numbers.append(int(cpf[j]))
calc += cpf_numbers[j] * (11 - j)
if (calc * 10) % 11 == int(cpf[-1]):
cur.execute("SELECT cpf FROM client;")
all_cpf = cur.fetchall()
if cpf not in all_cpf:
return True
else:
return False
else:
return False
else:
return False
def searchInvite(inviteCode):
sql = "select trd,userid from invite where inviteCode=%s"
try:
cur.execute(sql, [inviteCode])
temp = cur.fetchall()
return temp[0][0], temp[0][1]
except:
pass
return None, None
def flair_map(server):
flairmap = {}
cur.execute("SELECT * from flairmap WHERE discord_server=?", (server.id,))
rows = cur.fetchall()
for row in rows:
flairmap.update({row[1]:row[2]})
return flairmap
def getuserid(trd):
sql = "select id from session natural join userid where third_session=%s"
try:
cur.execute(sql, [trd])
temp = cur.fetchall()
userid = temp[0][0]
return userid
except:
pass
return
def login(userid):
sqls = "select password from user where iduser=%s;"
try:
cur.execute(sqls, [userid])
temp = cur.fetchall()
return 1
except:
register(userid)
return 0
def suburbs():
#define a query and get the suburb_id and name of the suburb in a tuple array
results = []
try:
cur.execute( """SELECT s.suburb_id, s.name FROM suburbs s"""
, "" )
results = cur.fetchall()
return jsonify(results )
except(Exception, psycopg2.DatabaseError) as error:
print(error)
return "Test"
def creditCardGenerator():
while True:
credit_card = ''
for i in range(16):
random_number = random.randint(0, 9)
credit_card += str(random_number)
cur.execute("SELECT credit_card FROM client;")
credit_cards = cur.fetchall()
if credit_card not in credit_cards:
return credit_card
def printAllClients(admin):
print('\nListagem de todos os clientes: ')
cur.execute("SELECT name, cpf, credit_card FROM client;")
clients = cur.fetchall()
if len(clients) > 0:
print('\nCPF \t\t Nome \t\t\t Número do Cartão')
for client in clients:
print(f'{client[1]} \t {client[0]} \t\t {client[2]}')
else:
print('\nNão há nenhum cliente cadastrado no sistema!')
getOption(admin)
def suburbName( suburbName ):
#We need to get the relevant information for properties inside the requested suburb
try:
cur.execute( """ SELECT l.headline, l.price, a.full_address, ag.first_name || ' ' || ag.last_name, l.seo_url, l.display_price
FROM listings l, properties_listings pl, properties p, address a, suburbs s, agents ag, agent_listings al
WHERE l.price is not null
AND pl.listings_id = l.listings_id
AND p.property_id = pl.property_id
AND p.address_id = a.address_id
AND a.suburb_id = s.suburb_id
AND ag.agent_id = al.agent_id
AND al.listings_id = l.listings_id
AND UPPER( s.name ) = UPPER( %s ) """,
( suburbName, ) )
results = cur.fetchall()
return jsonify( results )
#Convert the prices to string
except(Exception, psycopg2.DatabaseError ) as error:
print(error)
return "Failure"
def recv_message(messageJson):
target = messageJson['friendID']
message = messageJson['messageEnc']
sessionid = messageJson['sessionid']
import time
sendtime = time.strftime('%Y-%m-%d-%H-%M-%S', time.localtime(time.time()))
#sql="select id from session natural join userid where third_session = %s"
sqls = "select openid,third_session from userid natural join session where id = %s"
try:
#cur.execute(sql,[name])
#temp=cur.fetchall()
cur.execute(sqls, [target])
temp1 = cur.fetchall()
if temp1:
des = temp1[0][0]
trd = temp1[0][1]
return des, sendtime, message, trd, sessionid
else:
pass
except:
print "sql error"
return
r = requests.get(base_url + link[0], cookies=cookie)
soup = BeautifulSoup(r.text)
tags = [
"<div>", "</div>", "<p>", "</p>", "<br/>", "<h3>", "</h3>",
'<div class="textContent">'
]
resume = soup.findAll("div", {"class": "textContent"})[1]
resume_string = str(resume)
for tag in tags:
resume_string = resume_string.replace(tag, "")
return resume_string
cur.execute("SELECT * from physicians")
# This is a list of tuples
links = cur.fetchall()
# file_limiter = 0
for link in links:
# if file_limiter > 60:
# break
file = open(str(link[1]) + ".txt", "w")
file.write(parse_resume_page(link))
file.close()
# file_limiter += 1
print("file writter!")
# resume_string = str(divs[1])
# print(resume_string)
# no_open_p = no_div.replace("<p>", "")
# no_closed_p = no_open_p.replace("<p>", "")
def checkRequests(admin):
print('\nAnalise o pedido de mudança no limite de crédito dos clientes!')
cur.execute(
"SELECT * FROM limit_increase_request WHERE status = 'análise';")
requests = cur.fetchall()
if len(requests) > 0:
print('\nCartão | Cliente')
for request in requests:
cur.execute(
f"SELECT name, credit_card FROM client WHERE client_id = {request[1]}"
)
client_data = cur.fetchone()
print(f'{client_data[1]} {client_data[0]}')
verify = input(
'\nVocê deseja análisar a mudança de limite de crédito de algum cliente? (S/N): '
).upper()
if verify == 'S':
client = getClientByCreditCard()
if client != -1:
request = getRequestByClient(client)
if request != -1:
print(f'\nDados da Requisição do {client.getName()}:')
print(f'\nRenda Atual: R${client.getIncome():.2f}')
print(
f'Limite de Crédito Atual: R${client.getMonthlyLimit():.2f}'
)
print(f'\nNova Renda: R${request.getNewIncome():.2f}')
print(
f'Novo Limite de Crédito: R${request.getNewMonthlyLimit():.2f}'
)
approval = input(
f'\nVocê deseja aprovar essa requisição feita pelo cliente, {client.getName()}? (S/N): '
).upper()
if approval == 'S':
status = 'aprovado'
cur.execute(
f"""UPDATE client SET income = {request.getNewIncome()}, monthly_limit = {request.getNewMonthlyLimit()},
available_credit = {request.getNewAvailableCredit()} WHERE client_id = {client.getId()};"""
)
print(
'\nRequisição Aprovada! Novo limite de crédito definido.'
)
else:
status = 'negado'
print('\nRequisição Negada!')
cur.execute(
f"UPDATE limit_increase_request SET admin_id = {admin.getId()}, status = '{status}' WHERE request_id = {request.getId()};"
)
print('real status:', status)
conn.commit()
else:
print('\nNenhum cliente encontrado! Tente novamente.')
else:
print('\nNão há nenhuma requisição no momento!')
getOption(admin)
def cleanPrice():
#We need to establish a set of rules to clean the data
try:
#As Ella suggested, split each line into a string. But we first need to query the actual information.
cur.execute(
"""SELECT l.display_price, l.listings_id
FROM listings l
WHERE l.price_displayed = FALSE""", ())
# Get the Results
results = cur.fetchall()
result_list_exceptions = []
#Time for the rules section.
for line in results:
new_price = None
#In actuality, the very first thing that we need to check is the - symbol, as that will identify a range.
result = ''.join(line[0])
#We probably need to check if the price is the only thing there too. (just in case)
only_price = checkIsPrice(result)
if only_price is not None:
new_price = only_price
else:
#result = re.sub( r'(\(|\))', '', result )
check_range = result.find("-")
while True:
if check_range != -1:
#Not sure how to go from here...
#We need to get the prices from both sides...
result_list = str(result).split()
seperator_index = result_list.index('-')
start_price = checkIsPrice(
result_list[seperator_index - 1])
end_price = checkIsPrice(result_list[seperator_index +
1])
if start_price is not None and end_price is not None:
new_price = round(
(int(start_price) + int(end_price)) / 2, 2)
break
#if the string contains the word "From" and an int that can be identified as a string...
from_find_result = re.findall(r'from ?\$?\d+\$?', result)
if len(from_find_result) != 0:
result_list = from_find_result[0].split()
#Verify that the second string is actually a price. (It could have been something like From Your Wonderful Property Developers -_-)
check_price = checkIsPrice(result_list[1])
if check_price is not None:
new_price = check_price
break
# If both the From and the Range have failed, try and disect the string normally.
result_list = str(result).split(" ")
if len(result_list) == 1:
#If there is only one word in the entire display price, use a regular expression to test if it could be the price.
word_result = checkIsPrice(result_list[0])
if word_result is not None:
new_price = word_result
break
else:
new_price = None
result_list_exceptions.append(result)
#Step 2: If there is more then one eleement within the string, we need to iterate through all of them and test each one.
else:
for word in result_list:
#Check if it's a price first.
word_result = checkIsPrice(word)
if word_result is None:
continue
else:
new_price = word_result
break
#If it's completely failed
if new_price is None:
result_list_exceptions.append(result)
break
if new_price is not None:
# If the new price was successfully found for that row, perform an update
cur.execute(
""" UPDATE listings
SET price = %s,
price_displayed = true
WHERE listings_id = %s """,
(new_price, line[1]))
return True
except (Exception, psycopg2.DatabaseError) as error:
print(error)
print(line[1])
return False
