def get_index_info(conn, modalidade):
"""
Get basic information about product or L20
"""
query = Queries().IndexInfoProd().replace('\n', '').strip()
prod_code = None
try:
# try to find data for l20 if product wasnt found
result, conn = execute_query_temp(query, conn)
result = result[0]
if result[0] is None:
query = Queries().IndexInfoL20().replace('\n', '').strip()
result, conn = execute_query_temp(query, conn)
result = result[0]
if result[0] is None:
raise Exception()
except Exception as e:
print("\n**** Too Bad! It seems your l20 wasnt found either! ****")
raise L20notfound(
"Nao encontrado dados para os seguintes parametros: {}".format(
str(modalidade)))
# return information about l20 or product
return {
'units_per_visit': result[0],
'min_perc_disc': result[1],
'weight_flag': result[2],
'is_alcohol': result[3]
}
def getQueryLaunchProduct(l30, target_code, target, str_un, un_code,
str_filters, str_dna, loyalty_program, regions,
str_join, filters):
"""
Lealdade 10 Compra lancamentos e minha categoria
"""
query, table = Queries().LealdadeLaunchProduct()
queries = []
str_join_item = ""
query, table = Queries().LealdadeLaunchProduct()
if len(str_dna) > 0:
str_join_item = str_join.format(table=table, filter_personas=str_dna)
queries.append(
query.format(lcode=target_code,
filters=str_filters,
program=loyalty_program,
regions=regions,
joins=str_join_item))
final_query = "select count(*) from ({queries}) AS T;".format(
queries=" union ".join(queries))
return final_query
def getQueryDoesntBuyProduct(l30, l20, target, str_un, un_code, str_filters,
str_dna, loyalty_program, regions, str_join,
filters):
"""
lealdade 5
"""
operator = ' AND '
if 'operador' in filters:
operator = filters['operador']
# filters_l30_table = str_filters.replace('AND ', 'AND t_audience_l21_{l30}.'.format(l30=l30))
# loyalty_program_code_l30_table = loyalty_program.replace('AND ', 'AND t_audience_l21_{l30}.'.format(l30=l30))
# regions_l30_table = regions.replace('AND ', 'AND t_audience_l21_{l30}.'.format(l30=l30))
filters_un_table = str_filters.replace('AND ', 'AND u.')
loyalty_program_code_un_table = loyalty_program.replace('AND ', 'AND u.')
regions_un_table = regions.replace('AND ', 'AND u.')
if not str_un and not len(str_dna) > 0:
audienceQuery = getQueryAudiencenoUN(l30, target, str_filters, str_dna,
loyalty_program, regions,
str_join)
query = Queries().LealdadeDoesntBuyProductNoUN()[0]
query = query.format(l30=l30,
filters=str_filters,
filters_un=str_filters[4:],
loyalty_program=loyalty_program,
regions=regions,
audience=audienceQuery)
query = query.lower().replace('where and', 'where').replace(
'where and', 'where').replace('where and', 'where')
else:
un = ''
if str_un:
un = str_un[4:len(str_un)]
un = un.replace('and', operator)
un = 'AND ({un}) '.format(un=un)
str_join_un = ""
if len(str_dna) > 0:
str_join_un = str_join.format(table='u', filter_personas=str_dna)
audienceQuery = getQueryAudienceUN(l30, target, str_filters, str_dna,
loyalty_program, regions, str_join)
query = Queries().LealdadeDoesntBuyProductUN()
query = query[0].format(
condition=un,
filters_un_table=filters_un_table,
loyalty_program_code_un_table=loyalty_program_code_un_table,
regions_un_table=regions_un_table,
join_persona=str_join_un,
audience=audienceQuery)
# print query
return query
def get_redemption(banner, level_code, audience_type, vendor, conn):
query = Queries().conversion_rates().format(level=level_code)
if banner:
query += " AND banner ='{}' ".format(banner)
if audience_type:
query += " AND aba = '{}'".format(audience_type)
if vendor != "":
query += " AND cr.supplier_code = '{}'".format(vendor)
result, conn = execute_query_temp(query, conn)
result = pd.DataFrame(result)
if result['conversion_rate'][0] == 0:
message = "Redemption rate not found for level {l}, aba {a}, banner {b}".format(
l=level_code, a=audience_type, b=banner)
print(message)
try:
now = datetime.now()
f = open("L20ConversionNotfound.log", "a")
f.write("\n{0} - Erro: {1} ".format(
now.strftime("%d/%m/%Y %H:%M:%S"), message))
f.flush()
f.close()
finally:
r = False
else:
r = result['conversion_rate'][0]
r = format(r, '.4f')
return r
def getQueryAudienceUN(l30, target, str_filters, str_dna, loyalty_program,
regions, str_join):
"""
get audience query for loyalty 5 with UN
"""
query, table = Queries().LealdadeDoesntBuyProductAudience()
queries = []
str_join_item = ""
if target == 'SUB-CATEGORIA':
loyalty = 'is_loyal_l30'
elif target == 'CATEGORIA':
loyalty = 'is_loyal_l40'
else:
loyalty = 'is_loyal_b'
for l30_item in l30:
# check if l30 table exists
if checkl30(l30_item):
raise Exception('Nao existe cubo para:{}'.format(l30_item))
if len(str_dna) > 0:
str_join_item = str_join.format(table=table.format(l30=l30_item),
filter_personas=str_dna)
queries.append(
query.format(l30=l30_item,
filters=str_filters,
program=loyalty_program,
regions=regions,
joins=str_join_item,
loyalty=loyalty))
final_queries = " {queries} ".format(queries=" union ".join(queries))
return final_queries
def post(self):
# Region Set Up
#create resultService object
_resultsService = ResultsService()
#load queries
q = Queries()
# create connection to postgres database
_connector = Connector()
conn = _connector.connect(1)
# create cursor
cur = conn.cursor()
#define query parameters
_failID = self.get_argument("_failureId")
# endregion
# Region main block
cur.execute(q._email_failure, (_failID, ))
_result = cur.fetchall()
_result_to_json = _resultsService.parse(_result)
self.render("emailMonitor.html", results=_result)
cur.close()
conn.close
def classificacao():
q = Queries()
resultado = None
while not resultado:
id_aleatorio = random.randrange(1324911, 5324911)
query_txt = 'SELECT id, tribunal, texto_decisao from jurisprudencia_2_inst.jurisprudencia_2_inst where lower(texto_decisao) like "%saúde%" and classificacao is null and id = "{}";'.format(
str(id_aleatorio))
# id_aleatorio = random.randrange(19803,321358) # ID ALEATÓRIO STF
# QUERY STF
# query_txt = 'SELECT id, texto_decisao from stf.decisoes where lower(texto_decisao) like "%saúde%" and classificacao is null and id = "{}";'.format(str(id_aleatorio))
# id_aleatorio = random.randrange(448,299891) # ID ALEATÓRIO STj
# QUERY STJ
# query_txt = 'SELECT id, voto from stj.votos where lower(voto) like "%saúde%" and classificacao is null and id = "{}";'.format(str(id_aleatorio))
dados = q.query_padrao(query_text=query_txt)
if dados:
resultado = 1
session['id_p'] = dados[0][0]
id_p = dados[0][0]
# 2ª INSTÂNCIA
tribunal = dados[0][1]
texto_decisao = dados[0][2].replace('\n', '</p>\n<p>')
# STF
# tribunal = 'STF'
# texto_decisao = dados[0][1].replace('\n', '</p>\n<p>')
# STj
# tribunal = 'STj'
# texto_decisao = dados[0][1].replace('\n', '</p>\n<p>')
return render_template('classificacao.html',
texto_decisao=texto_decisao,
id_p=id_p,
tribunal=tribunal)
def getQueryLaunchProduct(l30, target_code, target, str_un, un_code,
str_filters, str_dna, loyalty_program, regions,
str_join, filters):
"""
Lealdade 10 Compra lancamentos e minha categoria
"""
query, table = Queries().LealdadeLaunchProduct()
queries = []
str_join_item = ""
if len(str_dna) > 0:
str_join_item = str_join.format(table=table, filter_personas=str_dna)
queries.append(
query.format(lcode=target_code,
filters=str_filters,
program=loyalty_program,
regions=regions,
joins=str_join_item))
subtract_query = getQuerySpendMostloyaty10(l30, target_code, target,
str_un, un_code, str_filters,
str_dna, loyalty_program,
regions, str_join, filters)
final_query = "SELECT (select sum(qty) from ({queries}) as t ) - ({subquery} as d) as c".format(
queries=" union ".join(queries), subquery=subtract_query)
return final_query
def post(self):
# Region Set Up
_resultsService = ResultsService()
_q = Queries()
_connector = Connector()
conn = _connector.connect(2)
cur = conn.cursor()
# endregion
# Region Main Block
cur.execute(_q._get_all_users)
_result = cur.fetchall()
_result_to_json = _resultsService.parse(_result)
for item in _result:
_rID = str(item[3])
_id = self.get_arguments("_checkbox")
if _rID in _id:
print("Deleting " + str(item[0]))
cur.execute(_q._delete_user, (item[3], ))
conn.commit()
cur.close()
conn.close()
self.redirect("/users")
def post(self):
# Region Set Up
#create resultService object
_resultsService = ResultsService()
#load queries
q = Queries()
# create connection to postgres database
_connector = Connector()
conn = _connector.connect(2)
# create cursor
cur = conn.cursor()
#define query parameters
_firstname = self.get_argument("_firstname_field")
_lastname = self.get_argument("_lastname_field")
_timenow = datetime.datetime.now()
# endregion
# Region main block
_query = q._get_all_users
cur.execute(_query)
_result = cur.fetchall()
_result_to_json = _resultsService.parse(_result)
_query = q._create_new_user
cur.execute(_query, (_firstname, _lastname, _timenow))
conn.commit()
cur.close()
conn.close
self.redirect("/users")
def getQueryAudienceUN(l30, target, str_filters, str_dna, loyalty_program,
regions, str_join):
"""
get audience query for loyalty 5 with UN
"""
query, table = Queries().LealdadeDoesntBuyProductAudience()
queries = []
str_join_item = ""
if target == 'SUB-CATEGORIA':
loyalty = 'is_loyal_l30'
elif target == 'CATEGORIA':
loyalty = 'is_loyal_l40'
else:
loyalty = 'is_loyal_b'
if loyalty_program == "AND loyalty_program_code in ('3')":
loyalty_program == ""
for l30_item in l30:
if len(str_dna) > 0:
str_join_item = str_join.format(table=table.format(l30=l30_item),
filter_personas=str_dna)
queries.append(
query.format(l30=l30_item,
filters=str_filters,
program=loyalty_program,
regions=regions,
joins=str_join_item,
loyalty=loyalty))
final_queries = " {queries} ".format(queries=" union ".join(queries))
return final_queries
def checkl30(l30):
query = Queries().TableExists().format('t_audience_l21_{}'.format(l30))
result = pd.DataFrame(execute_query(query), columns=['Table'])
return result['Table'][0] == None
def getQuerySpendMostloyaty10(l30, target_code, target, str_un, un_code,
str_filters, str_dna, loyalty_program, regions,
str_join, filters):
"""
Lealdade 6 gasta mais com meu produto usado na lealdade 10
"""
query, table = Queries().LealdadeSpendMostMyProductLoyalty10()
queries = []
str_join_item = ""
if loyalty_program == "AND loyalty_program_code in ('3')":
loyalty_program = ""
for l30_item in l30:
if len(str_dna) > 0:
str_join_item = str_join.format(table=table.format(l30=l30_item),
filter_personas=str_dna)
queries.append(
query.format(l30=l30_item,
filters=str_filters,
program=loyalty_program,
regions=regions,
joins=str_join_item,
loyalty=''))
final_queries = "select count(distinct prsn_id) from ({queries})".format(
queries=" union ".join(queries))
return final_queries
def getQueryDoesntSpendMost(l30, target_code, target, str_un, un_code,
str_filters, str_dna, loyalty_program, regions,
str_join, filters):
"""
Lealdade 7 Gasta mais com produtos concorrentes do que com meu produto
"""
query, table = Queries().LealdadeSpendMostMyProduct()
queries = []
str_join_item = ""
if target == 'SUB-CATEGORIA':
loyalty = 'is_loyal_l30'
elif target == 'CATEGORIA':
loyalty = 'is_loyal_l40'
else:
loyalty = 'is_loyal_b'
for l30_item in l30:
if len(str_dna) > 0:
str_join_item = str_join.format(table=table.format(l30=l30_item),
filter_personas=str_dna)
queries.append(
query.format(l30=l30_item,
filters=str_filters,
loyal='false',
program=loyalty_program,
regions=regions,
joins=str_join_item,
loyalty=loyalty))
final_query = "select count(*) from ({queries}) AS T;".format(
queries=" union ".join(queries))
return final_query
def get_template(template_type, keywords_dict):
queries = Queries()
try:
character = keywords_dict['CHARACTER']
except KeyError:
character = None
try:
place = keywords_dict['PLACE']
except KeyError:
place = None
try:
loyalty = keywords_dict['LOYALTY']
except KeyError:
loyalty = None
try:
gender = keywords_dict['GENDER']
except KeyError:
gender = None
try:
fictional = keywords_dict['FICTIONAL']
except KeyError:
fictional = None
try:
event = keywords_dict['EVENT']
except KeyError:
event = None
try:
chapter = keywords_dict['CHAPTER']
except KeyError:
chapter = None
try:
time = keywords_dict['TIME']
except KeyError:
time = None
if template_type == 'BIRTHPLACE':
queries.birthplace(character, place)
elif template_type == 'LOYALTY':
queries.loyalty(character, loyalty)
elif template_type == 'LIFESPAN':
queries.lifespan(character)
elif template_type == 'GENDER':
queries.gender(character, gender)
elif template_type == 'CHARACTER_INFO':
queries.about_character(character)
elif template_type == 'CHAPTER':
queries.which_chapter(event, chapter)
elif template_type == 'EVENT_INFO':
queries.event_info(event)
elif template_type == 'EVENT_HISTORY':
queries.event_history(event)
elif template_type == 'EVENT_LOCATION':
queries.event_location(event, place)
elif template_type == 'EVENT_DATE':
queries.event_time(event, time)
elif template_type == 'EVENT_INVOLVED':
queries.event_involved(event, character)
elif template_type == 'FICTIONAL':
queries.fictional(character, fictional)
def pesquisa():
if request.method == 'POST':
try:
q = Queries()
return str(q.query_tribunais(request.form['query'],tribunal='segunda_inst'))
except Exception as e:
return e
else:
return 'Preencha o formulário corretamente, por favor.'
def createQueries(config):
q = Queries(int(config['query']['runs']), config['others']['output-dir'],
config['query']['output-dir'],
config['observation']['start_timestamp'],
int(config['observation']['days']),
int(config['query']['num-locations']),
int(config['query']['num-sensors']),
int(config['query']['time-delta']))
q.generateQueries()
def QRIS(pcap, website, chinese, queryset, bigrams, trident, topk, verbose):
'''
Query Recognition in Incremental Search.
'''
# Load network packets
packets = Packets(pcap, website)
if website is None:
print('Detected website:', packets.website)
if verbose:
print('Server IP:', packets.packets.loc[0, 'dst'])
# Correlate keystroke packets with DFA states
keystrokes = packets.correlate_state(chinese, trident)
if verbose:
print('Keystrokes:\n', keystrokes)
# Load query set and metadata
queries = Queries(packets.website, chinese, queryset, bigrams, verbose)
if verbose:
print('Number of queries:', len(queries.queries))
# Filter by query length (keystroke number)
candidates = queries.filter_by_length(keystrokes)
if verbose:
print('Filtered by length:', [len(x) for x in candidates])
# Filter by query token length (delimiter sequence)
keystrokes = packets.delimit_token(chinese)
candidates = queries.filter_by_token(keystrokes)
if verbose:
print('Filtered by token:', [len(x) for x in candidates])
# Filter by query compressed size pattern
keystrokes = packets.size_pattern()
candidates = queries.filter_by_pattern(keystrokes)
if verbose:
print('Filtered by pattern:', [len(x) for x in candidates])
# Rank by query typing rhythm (keystroke timing)
keystrokes = packets.timing_interval()
candidates = queries.rank_by_rhythm(keystrokes)
if verbose:
print('Ranked by rhythm:\n', [x.sort_values('rank') if len(x) > 0 else x for x in candidates])
# Return inferred queries
querylist = []
for group in candidates:
if len(group) == 0:
continue
ranked = group.sort_values('rank')['query']
if len(ranked) < topk:
querylist += ranked.tolist()
else:
querylist += ranked[:topk].tolist()
return querylist
def __init__(self):
AuctionScanner.__init__(self)
self.q = Queries()
self.base_url = 'https://www.tutti.ch/api/v10/list.json?aggregated=1&limit=50&o=1&q=lego&with_all_regions=true'
self.headers = {
'User-Agent':
'Mozilla/5.0 (X11; Linux x86_64; rv:80.0) Gecko/20100101 Firefox/80.0',
'Accept': 'application/json, text/plain, */*',
'X-Tutti-Hash': '858521e7-f8d9-40ef-a534-6ae88d93165c'
}
def get_average_price(self, conn):
"""
return average price for the products, need the connection that holds product temporary table
"""
query = Queries().AveragePrice()
result, conn = execute_query_temp(query, conn)
result = pd.DataFrame(
result, columns=['avg_prod_price_clex', 'avg_prod_price_pa'])
return result['avg_prod_price_clex'][0], result['avg_prod_price_pa'][
0],
def main():
# Input: SQLite3
conn = sqlite3.connect(
'../ExampleData/example_data_before_migration.sqlite3')
conn.row_factory = sqlite3.Row
# Output: MongoDB
client = MongoClient('localhost', 27017)
db = client.db_before_migration
if db.user.count_documents({}) == 0:
print('Loading data\n')
data_loader.populate_database(conn, db)
else:
print('Warning: Database already exists; not reloading data\n')
# Read parameters and reference results
tc_file = open('../ExampleData/query_results_before_migration.json')
test_cases = TestCases(tc_file)
queries = Queries(db, test_cases)
results, metrics = queries.execute()
failed = False
# Write complexity metrics to file
metrics.to_csv('metrics.csv')
if len(test_cases) != len(results):
raise RuntimeError('There are {} queries but {} tests'.format(
len(results), len(test_cases)))
for result in results:
if len(result.violation_indexes) == 0:
# Success
print(
'{0.name} Succeeded in {0.elapsed_time:.4f} s\n----------------'
.format(result))
else:
# Error
actual = pretty_print_json(result.actual)
expected = pretty_print_json(result.expected)
print('''{result.name} FAILED
Mismatch indexes: {result.violation_indexes}
Actual results: {actual}
Expected results: {expected}
----------------'''.format(result=result, actual=actual, expected=expected))
failed = True
if failed:
raise RuntimeError('Some tests failed')
def get(self):
_q = Queries()
_resultsService = ResultsService()
# create connection to postgres database
_connector = Connector()
conn = _connector.connect(2)
# create cursor
cur = conn.cursor()
cur.execute(_q._get_all_ship_classes)
_result = cur.fetchall()
_result_to_json = _resultsService.parse(_result)
self.render("shipClass.html", result=_result)
def getl20_30subgrupo(modalidade, vendor):
"""
Create a temporary table with the products of this target by a subgroup
return L20 and l30 of the product
"""
query = Queries().ProductStructure()
depart = modalidade._value.get('departamento')
categoria = modalidade._value.get('categoria')
subcategoria = modalidade._value.get('subcategoria')
grupo = modalidade._value.get('grupo')
subgrupo = modalidade._value.get('subgrupo')
code = (depart, categoria, subcategoria, grupo, subgrupo)
str_codes = "".join(code)
query = Queries().ProductStructure()
query = query.format(wheres=" prod_hier_l10_code in ('{subgrupo}')".format(
subgrupo=str_codes),
vendor=vendor)
return query
def classificacao():
q = Queries()
resultado = None
while not resultado:
id_aleatorio = random.randrange(1324911,5324911)
query_txt = 'SELECT id, tribunal, texto_decisao from jurisprudencia_2_inst.jurisprudencia_2_inst where lower(texto_decisao) like "%saúde%" and classificacao is null and id = "{}";'.format(str(id_aleatorio))
dados = q.query_padrao(query_text=query_txt)
if dados:
resultado = 1
session['id_p'] = dados[0][0]
id_p = dados[0][0]
tribunal = dados[0][1]
texto_decisao = dados[0][2].replace('\n', '</p>\n<p>')
return render_template('classificacao.html', texto_decisao = texto_decisao, id_p = id_p, tribunal = tribunal)
def getl20_30Plu(modalidade, vendor):
"""
Create a temporary table with the products of this target by a PLU list
return L20 and l30 of the product
"""
query = Queries().ProductStructure()
str_prod_codes = list2str(modalidade._value.get("produto"))
query = query.format(
wheres=" prod_code in ({prods})".format(prods=str_prod_codes),
vendor=vendor)
return query
def getQueryDoesntBuyL20(l21, l20, target, str_un, un_code, str_filters,
str_dna, loyalty_program, regions, str_join, filters):
query, table = Queries().LealdadeDoesntBuyl20()
if len(str_dna) > 0:
str_join = str_join.format(table=table, filter_personas=str_dna)
query = query.format(condition=str_un,
filters=str_filters,
un_code=un_code,
program=loyalty_program,
regions=regions,
joins=str_join)
return query
def __init__(self, set_number, condition='new'):
self._url = 'http://www.bricklink.com/catalogPG.asp?S={}-1&ColorID=0'.format(
set_number)
self._headers = {
'User-Agent':
'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.132 Safari/537.36'
}
self.condition = condition
self.prices = {}
self.set_number = set_number
self.scan_date = datetime.now()
self.exchange_rates = {}
self.toCurrency = 'CHF'
self.result = {}
self.db = MySQLDatabase()
self.q = Queries()
self._parse_prices()
def getQueryLoyaltyUnder50(l21, l20, target, str_un, un_code, str_filters,
str_dna, loyalty_program, regions, str_join,
filters):
query, table = Queries().Lealdade50()
if len(str_dna) > 0:
str_join = str_join.format(table=table.format(l21=l21),
filter_personas=str_dna)
query = query.format(l21=l21,
filters=str_filters,
loyal='false',
program=loyalty_program,
regions=regions,
joins=str_join)
return query
def getl20_30Familia(modalidade, vendor):
"""
Create a temporary table with the products of this target by a PLU and Familia
return L20 and l30 of the product
"""
query = Queries().ProductStructureFamily()
family_code = modalidade._value.get("familia")
#subgrupo = modalidade._value.get("subgrupo")
#modalidade.subgrupo
query = query.format(subgrupo=modalidade.subgrupo,
family=family_code,
vendor=vendor)
return query
def getQueryRecover(l30, target_code, target, str_un, un_code, str_filters,
str_dna, loyalty_program, regions, str_join, filters):
"""
Lealdade 20 Compra lancamentos e minha categoria
"""
query, table = Queries().LealdadeRecoverProduct()
#query = query.format(l21=l21,
# filters=str_filters, loyal='true',
# program=loyalty_program, regions=regions,
# joins=str_join)
queries = []
str_join_item = ""
if target == 'SUB-CATEGORIA':
loyalty = 'is_loyal_l30'
elif target == 'CATEGORIA':
loyalty = 'is_loyal_l40'
else:
loyalty = 'is_loyal_b'
if loyalty_program == "AND loyalty_program_code in ('3')":
loyalty_program == ""
for l30_item in l30:
if len(str_dna) > 0:
str_join_item = str_join.format(table=table.format(l30=l30_item),
filter_personas=str_dna)
queries.append(
query.format(l30=l30_item,
filters=str_filters,
loyal='true',
program=loyalty_program,
regions=regions,
joins=str_join_item,
loyalty=loyalty))
final_query = "select count(distinct t.prsn_id) from ({queries}) AS T;".format(
queries=" union ".join(queries))
return final_query
