def getPrincipalAgent(self):
try:
#Test to see if the Agent instance has a value for principal_agent_name
if self.principal_agent_name == None:
raise ValueError('Invalid Principal Agent Name')
else:
full_name = self.principal_agent_name.split()
result = None
if len(full_name) == 2:
cur.execute(
""" SELECT a.agent_id
FROM agents a
WHERE UPPER( a.first_name ) = UPPER( %s )
AND UPPER( a.last_name ) = UPPER( %s )""",
(cleanForSQL(full_name[0]), cleanForSQL(full_name[1])))
result = cur.fetchone()
if result is None:
self.principal_agent_id = None
else:
self.principal_agent_id = result[0]
return True
except (Exception, psycopg2.DatabaseError, ValueError) as error:
print("Error in Getting Principal Agent ID for Agency " +
self.name + "\n" + error)
return False
async def create_new_flair_to_role(guild, flair):
# build the discord.Color object
if flair['color'] == "":
color_hex = int("0x000000", 16)
else:
color_hex = int(flair['color'].replace("#", "0x"), 16)
color = client.discord.Color(color_hex)
# make the role
newrole = await guild.create_role(name=flair['label'],
color=color,
hoist=True)
# what time is it, right now (in UTC)
now = datetime.now(timezone.utc).isoformat()
# update the database
cur.execute(
"INSERT INTO flairmap (discord_server, discord_role, dgg_flair, last_updated, last_refresh) VALUES (?,?,?,?,?)",
(guild.id, newrole.id, flair['name'], now, now))
con.commit()
logger.info(
"create_new_flair_to_role() flair {0[name]} created as ID {1.id} on Discord {2.id}"
.format(flair, guild, newrole))
def checkAndStoreDocType(self, commit):
try:
if not checkRowExists(
f"SELECT 1 FROM document_type WHERE UPPER(description) = '{self.document_type_description}'"
):
document_type = returnNextSerialID('document_type',
'document_type')
cur.execute(
f""" INSERT INTO document_type(description)
VALUES( '{self.document_type_description}' )""",
"")
if commit:
conn.commit()
self.document_type = document_type
else:
cur.execute(
f"SELECT document_type FROM document_type WHERE UPPER(description) = '{self.document_type_description}'",
"")
result = cur.fetchone()
self.document_type = result[0]
except (Exception, psycopg2.DatabaseError) as error:
print("Error in Checking Description of File with File ID " +
str(self.file_id) + " with Document Type Description " +
self.document_type_description + "\n" + "Error: " + error)
return None
def storeInspectionDetails( listing_id, inspectionObject ):
try:
#Need to build the inspections and past inspections objects.
past_inspections = inspectionObject['pastInspections']
current_inspections = inspectionObject['inspections']
#do past_inspections first.
for inspection in past_inspections:
if 'description' in inspection:
description = inspection['description']
else:
description = "NULL"
cur.execute( """INSERT INTO listing_inspections( opening_time, closing_time, description, recurring, is_past, listings_id, entered_when )
VALUES( to_timestamp( %s, 'YYYY-MM-DD HH24:MI:SS' ), to_timestamp( %s, 'YYYY-MM-DD HH24:MI:SS' ), %s, %s, %s, %s, current_timestamp )""" ,
( convertJSONDate(inspection['openingDateTime']), convertJSONDate(inspection['closingDateTime']), description, inspection.get( 'recurrence' ), True, listing_id ) )
for inspection in current_inspections:
if 'description' in inspection:
description = inspection['description']
else:
description = "NULL"
cur.execute( """INSERT INTO listing_inspections( opening_time, closing_time, description, recurring, is_past, listings_id, entered_when )
VALUES( to_timestamp( %s, 'YYYY-MM-DD HH24:MI:SS' ), to_timestamp( %s, 'YYYY-MM-DD HH24:MI:SS' ), %s, %s, %s, %s, current_timestamp )""",
( convertJSONDate(inspection['openingDateTime']), convertJSONDate(inspection['closingDateTime']), description, inspection.get( 'recurrence' ), False, listing_id ) )
return True
except(Exception, psycopg2.DatabaseError ) as error:
print( "Error in INSERTING Inspection Details for listing " + "\n" + "Error: " + error )
return False
def storeFile(self, commit):
try:
#Need to check if we are saving an image or a document.
if self.file_type == FILE_TYPE_Images:
#This means that we are saving an image.
image_id = returnNextSerialID('images', 'image_id')
File.checkAndStoreDocType(self, False)
if self.image_date_taken is not None:
insert_image_date_taken = f"to_timestamp( '{convertJSONDate(self.image_date_taken)}', 'YYYY-MM-DD HH24:MI:SS' )"
else:
insert_image_date_taken = "NULL"
images_insert_statement = f""" INSERT into images( advert_id, date_taken, url )
VALUES( {ifNotNone(self.image_advert_id, "NULL")}, {insert_image_date_taken}, '{self.image_images_url}' )"""
cur.execute(images_insert_statement, "")
cur.execute(
f""" INSERT INTO files( file_id, file_type, object_type, keyval1, keyval2, document_type, entered_when )
VALUES( {image_id}, '{FILE_TYPE_Images}', {self.object_type}, '{self.keyval1}', '{self.keyval2}', {self.document_type}, current_timestamp ) """,
"")
if commit:
conn.commit()
return True
elif self.file_type == FILE_TYPE_Documents:
pass
else:
raise ValueError(str(self) + " has an invalid file type")
except (ValueError, Exception, psycopg2.DatabaseError) as error:
print("Error in INSERTING File with File ID " + str(image_id) +
"\n" + "Error " + error)
return False
def StoreListings( listingObject ):
#Prepare the secondary dictionaries
listing_sales = listingObject.get( 'saleDetails' )
lisitng_inspections = listingObject.get( 'inspectionDetails' )
listing_prices = listingObject.get( 'priceDetails' )
try:
#Insert the raw listing first.
#Get the listing_status
listing_status = QueryWithSingleValue( 'listing_status_lkp', 'description', listingObject['status'], 'listing_status_id', True )
#build the JSON from listingObject
raw_listing_JSON = json.dumps( listingObject )
#Get the value that will be used with listing_insert_statement
listings_id = returnNextSerialID( 'listings', 'listings_id' )
if lisitng_inspections is not None:
isByAppointmentOnly = lisitng_inspections.get( 'isByAppointmentOnly' )
else:
isByAppointmentOnly = None
cur.execute( """ INSERT INTO listings( domain_listings_id, headline, price_displayed, display_price, price, price_from, price_to, seo_url, listing_objective,
listing_status, land_area, building_area, energy_efficiency, is_new_development, date_updated, date_created,
entered_when, entered_by, raw_listing, inspection_appointment_only )
VALUES( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, to_timestamp( %s, 'YYYY-MM-DD HH24:MI:SS' ), to_timestamp( %s, 'YYYY-MM-DD HH24:MI:SS' ), current_timestamp, 1, %s, %s ) """,
( listingObject.get( 'id' ), listingObject.get( 'headline' ), listing_prices.get( 'canDisplayPrice' ), listing_prices.get( 'displayPrice' ), listing_prices.get( 'price' ),
listing_prices.get( 'priceFrom' ), listing_prices.get( 'priceTo' ), listingObject.get( 'seoUrl' ), listingObject.get( 'objective' ), listing_status, listingObject.get( 'landAreaSqm'),
listingObject.get( 'buildingAreaSqm' ), listingObject.get( 'energyEfficiencyRating' ), listingObject.get( 'isNewDevelopment' ), convertJSONDate(listingObject['dateUpdated']),
convertJSONDate(listingObject['dateListed']), cleanForSQL(raw_listing_JSON), isByAppointmentOnly ) )
#Insert the Features if the listing contains any.
#Set the object type
#Only do this if the listing already has a features object.
if 'features' in listingObject:
link_object_type = OBJECT_Listing
for feature in listingObject['features']:
storeFeatures( listings_id, link_object_type, feature )
if 'media' in listingObject:
#Store any media attached to the listing
for media in listingObject['media']:
mediaObject = File( FILE_TYPE_Images, OBJECT_Listing, str(listings_id), None, "listing_" + media['type'] )
mediaObject.addImageDetails( None, None, media['url'] )
mediaObject.storeFile( False )
#Store Listing Sales Information.
#First, we need to check if the listings has any sales information attached to it.
if listing_sales is not None:
storeListingSalesDetails( listing_sales, listings_id )
#Store the Inspection information (if the listing_inspections array is not None)
if lisitng_inspections is not None:
storeInspectionDetails( listings_id, lisitng_inspections )
return listings_id
except(Exception, psycopg2.DatabaseError) as error:
print("Error in INSERTING New Listing with Domain Listing ID " + "\n" + "Error: " + error )
return None
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 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 QueryWithSingleValue( tableName, searchColumn, searchValue, valueColumn, searchRequiresQuotes ):
"""
This Function retrieves a single value from a Sql Query built from the various passed parameters.
The format is:
SELECT (valueColumn) FROM (tableName) WHERE (searchColumn) = (appended_search_value)
If there are multiple values returned from the query, only the very first one is returned.
Keyword arguements:
tableName -- Name of the Table.
searchColumn -- Comparison Column(against searchValue)
searchValue -- Value we are using in the search.
valueColumn -- Column that holds the value we are searching for.
searchRequiresQuotes -- If True, a set of quotes is appended to the searchValue.
"""
try:
if searchRequiresQuotes:
appended_search_value = cleanForSQL( searchValue )
appended_search_value = VarIf(searchRequiresQuotes, "'" + appended_search_value + "'", appended_search_value )
query = f"SELECT {valueColumn} FROM {tableName} WHERE {searchColumn} = {appended_search_value}"
cur.execute( query, "")
row = cur.fetchone()
if row is None:
return None
else:
return row[0]
except(Exception, psycopg2.DatabaseError) as error:
print(error)
def adminRegistration(admin):
print('\nDigite os dados para a criação de um novo administrador!')
name = input('\nUsuário: ')
while True:
password = input('Senha: ')
if validatePassword(password):
salt = os.urandom(32)
key = hashPassword(password, salt)
cur.execute(
'''INSERT INTO administrator (name, salt, key_pass) VALUES (%s, %s, %s)''',
(name, salt, key))
conn.commit()
print('\nAdministrador cadastrado com sucesso!')
break
else:
print(
'\nA senha deve conter mais que 6 dígitos! Tente novamente.\n')
getOption(admin)
def add_user():
#verifying the role, than adding new user
role = request.form['role']
newuser=users(request.form['username'], request.form['password'], request.form['role'], request.form['projectname'])
db.session.add(newuser)
db.session.flush()
#diff between sessions and cursor objects
db.session.commit()
userid = str(newuser.id)
#adding within this userid all the rooms to the test table
conn = psycopg2.connect(
database="project1",
user="******",
host="localhost",
port="5432"
)
cur = conn.cursor()
if role == '2' :
#extracting the floors & rooms from the csv and instert to table
with open('Duplex_A_20110907_rooms.csv', 'r') as f:
reader = csv.reader(f, delimiter=';')
for row in reader:
#print(row[1],row[2])
cur.execute(
" INSERT INTO rooms (userid, floor, room) VALUES (%s, %s, %s)",
(userid ,row[1],row[2])
)
conn.commit()
return redirect('/admin')
else:
return redirect('/admin')
def login():
conn = psycopg2.connect(
database="project1",
user="******",
host="localhost",
port="5432"
)
cur = conn.cursor()
x = request.form['username']
y = request.form['password']
cur.execute( "SELECT id FROM users WHERE username = %s AND password = %s ",
(x,y)
)
userid = str(cur.fetchone()[0])
cur.execute("SELECT role FROM users Where id = %s ",
(userid)
)
role = cur.fetchone()[0]
session['userid'] = userid
if role is '1':
return redirect('/admin')
if role is '2':
return redirect('/user')
if role is '3':
return redirect('/superuser')
def deleteInvite(inviteCode):
sql = "delete from invite where inviteCode=%s"
try:
cur.execute(sql, [inviteCode])
conn.commit()
except:
pass
return
def register(userid):
sqli = "insert into user(iduser) values(%s);"
try:
cur.execute(sqli, (userid))
conn.commit()
return 1
except:
return 0
def saveInvite(inviteCode, trd, userid):
sql = "insert into invite values(%s,%s,%s)"
try:
cur.execute(sql, [inviteCode, trd, userid])
conn.commit()
except:
pass
return
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 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 storeSuburb(self, suburbName, state, postCode):
try:
new_suburb_id = returnNextSerialID('suburbs', 'suburb_id')
cur.execute(
""" INSERT INTO suburbs( name, state, is_completed, postcode )
VALUES( %s, %s, %s, %s ) """,
(suburbName, state, False, postCode))
return new_suburb_id
except (Exception, psycopg2.DatabaseError) as error:
print(error)
def get_flair_if_exists(guildid, flair):
cur.execute(
"SELECT * from flairmap WHERE discord_server=? AND dgg_flair=?",
(guildid, flair['name']))
row = cur.fetchone()
if row:
return row
else:
return None
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 clientRegistration(admin):
print('\nDigite os dados para o cadastro do cartão do cliente!')
name = input('\nNome Completo: ')
phone = input('Nº de Telefone: ')
while True:
cpf = input('CPF: ')
if validateCPF(cpf):
income = Decimal(float(input('Renda Mensal (R$): ')))
while True:
password = input('Senha: ')
if validatePassword(password):
salt = os.urandom(32)
key = hashPassword(password, salt)
credit_card = creditCardGenerator()
monthly_limit = income * Decimal(0.85)
available_credit = monthly_limit
cur.execute(
'''INSERT INTO client (name, cpf, phone, income, credit_card, monthly_limit, available_credit, salt, key_pass) VALUES
(%s, %s, %s, %s, %s, %s, %s, %s, %s)''',
(name, cpf, phone, income, credit_card, monthly_limit,
available_credit, salt, key))
conn.commit()
print('\nCadastro realizado com sucesso!')
print(
f'\nNúmero do Cartão: {credit_card}\nLimite de Crédito: R${monthly_limit:.2f}'
)
print(
f'\nATENÇÃO! O número do cartão ({credit_card}) é o seu username.'
)
getOption(admin)
break
else:
print(
'\nA senha deve conter mais que 6 dígitos! Tente novamente.\n'
)
break
else:
print(
'\nCPF inválido ou já cadastrado previamente! Tente novamente.\n'
)
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 checkStatusRequest(client, status):
if status is None:
print(
f'\n{client.getName()}, requisite uma mudança no limite de crédito do seu cartão!'
)
income = client.getIncome()
new_income = Decimal(float(input('\nInsira sua renda mensal: R$')))
if new_income != income:
new_monthly_limit = new_income * Decimal(0.85)
new_available_credit = client.getAvailableCredit() + (
new_monthly_limit - client.getMonthlyLimit())
print('new available credit type:', type(new_available_credit))
status = 'análise'
if new_available_credit > 0:
cur.execute(
f"""INSERT INTO limit_increase_request (client_id, new_income, new_monthly_limit, new_available_credit, status) VALUES (
{client.getId()},
{new_income},
{new_monthly_limit},
{new_available_credit},
'{status}');
""")
conn.commit()
print('\nSua requisição está em análise. Aguarde!')
elif new_available_credit < 0:
print(
'\nPedido Negado! Baseado em seus gastos desse mês, seu crédito disponível ficaria menor que zero.'
)
else:
print(
'\nPedido Negado! É necessário inserir uma renda diferente da cadastrada previamente.'
)
elif status == 'aprovado':
print(f'\nSua requisição para mudança de limite foi APROVADA!')
print(
f'Seu novo limite mensal é R${client.getMonthlyLimit():.2f} e você tem R${client.getAvailableCredit():.2f} de crédito disponível!'
)
elif status == 'negado':
print(f'\nSua requisição para mudança de limite foi NEGADA!')
else:
print('\nSua requisição está em análise. Aguarde!')
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 saveToDatabase(nvdResult, productResult, referenceResult, logicalTestResult):
cur.execute(INSERT_NVD % (
nvdResult['cve_id'],
nvdResult['published_datetime'],
nvdResult['last_modified_datetime'],
nvdResult['score'],
nvdResult['access_vector'],
nvdResult['access_complexity'],
nvdResult['authentication'],
nvdResult['confidentiality_impact'],
nvdResult['integrity_impact'],
nvdResult['availability_impact'],
nvdResult['source'],
nvdResult['generated_on_datetime'],
nvdResult['cwe_id'],
nvdResult['summary'],
))
id = cur.lastrowid
for product in productResult:
cur.execute(INSERT_PRODUCT % (id, product))
for reference in referenceResult:
cur.execute(INSERT_REFERENCE % (id, reference['type'], reference['source'], reference['reference'], reference['url']))
for logicalTest in logicalTestResult:
cur.execute(INSERT_LOGICAL_TEST % (id, logicalTest))
conn.commit()
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 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 monthlyPurchasesRegistration(client):
print('\nDespesas Mensais:')
client.printData()
#faz com que o user não precise repetir o processo de login, caso adicione um custo na sequência da outra
while True:
print('\nRegistre uma compra.')
available_credit = client.getAvailableCredit()
category = input('\nCategoria: ').capitalize()
#enquanto o custo for menor que R$0.00, o custo não é registrado
while True:
price = Decimal(float(input('Valor: R$')))
if price <= 0:
print('\nO valor da compra precisa ser maior que R$0.00')
break
else:
if available_credit >= price:
available_credit = available_credit - price
date = input('Data: ')
cur.execute(
f"""INSERT INTO purchase (client_id, category, price, date) VALUES ({client.getId()},
'{category}', {price}, '{date}');""")
cur.execute(
f"UPDATE client SET available_credit = '{available_credit}' WHERE client_id = {client.getId()};"
)
conn.commit()
client.setAvailableCredit(available_credit)
else:
print('\nCrédito Insuficiente!')
break
print(f'\nCrédito Disponível: R${available_credit:.2f}')
if input(
'\nDeseja registrar mais algum gasto? (S/N): ').upper() != 'S':
getOption(client)
break
def checkRowExists( query ):
"""
Function checks to see if a particular row exists inside the query parameter. The query parameter should be formatted in this fashion:
SELECT 1 FROM (tableName) WHERE (columnName) = (valueToFind)
Keyword arguements:
query -- The query that checks to see if a particular row exists (as described above).
"""
try:
cur.execute( query, "" )
return cur.fetchone() is not None
except(Exception, psycopg2.DatabaseError ) as error:
print (error)
print( "Offending Query " + query )
def populate_resume_link_list():
r = requests.get(sample_url.format(page_num=page_num), cookies=cookie)
soup = BeautifulSoup(r.text, features="lxml")
# print(soup)
candidate_rows = soup.findAll("td", {"class": "nowrap"})
print("Candidate rows..." ,candidate_rows)
for i in candidate_rows:
try:
link = i.a['href']
id = link.split("=")
print("id num", id[1])
cur.execute("INSERT into physicians VALUES ('{}', '{}')".format(i.a['href'], id[1]))
except:
continue
