def extract_xlsx_data(file):
data = []
# Load file
wb = opxl.load_workbook('download/' + file)
# Open worksheet
work_sheet = time_now('date.month.year')
ws = wb.get_sheet_by_name(work_sheet)
# Read specific columns row by row contents from file
for row in range(2, ws.max_row + 1):
data_item = []
for column in "BLOMPDEH":
cell_name = "{}{}".format(column, row)
cell_data = ws[cell_name].value
data_item.append(cell_data)
data.append(data_item)
# Delete temporary file downloaded
with contextlib.suppress(FileNotFoundError):
os.remove('download/' + file)
return data
def register_users():
## SQLite3 user database initialization
create_user_table()
# Open users worksheet
sheet = access_spreadsheet()
users_sheet = sheet.get_worksheet(0)
# Get number of rows (new user registrations)
number_of_users = (users_sheet.row_count - 1)
print('Number of new registrations: ' + str(number_of_users))
# Loop through new user registrations
for x in range(number_of_users):
new_user = users_sheet.row_values(2 + x)
# Formatting user data
reg_date = time_now('date.month.year')
email = new_user[1]
firstname = new_user[2]
lastname = new_user[3]
age = new_user[4]
postal_number = new_user[5]
street_name = new_user[6]
street_number = new_user[7]
phone = new_user[8]
bsu = new_user[9]
bsu_bank = new_user[10]
savings = new_user[11]
savings_bank = new_user[12]
savings_limit = new_user[13]
savings_limit_bank = new_user[14]
retirement = new_user[15]
retirement_bank = new_user[16]
usagesalary = new_user[17]
usagesalary_bank = new_user[18]
# Formatting current user to class
current_user = User(reg_date, email, firstname, lastname, age,
postal_number, street_name, street_number, phone,
bsu, bsu_bank, savings, savings_bank,
savings_limit, savings_limit_bank, retirement,
retirement_bank, usagesalary, usagesalary_bank)
# Insert new user into the user database
response = insert_user(current_user)
# Send confirmation email for new registrations and updates
if response == 'new_user':
registration_email(current_user)
elif response == 'update_user':
update_email(current_user)
# Loop through and delete user entries in sheet
print('Deleting new user entries from sheet...')
for x in range(number_of_users):
users_sheet.delete_row((number_of_users + 1) - x)
def __update_db__(attachment_pk: int, res_peoples: list, last_parse: int,
type_request: str) -> Union[None, int]:
"""
Функция добавления записи в базу
:param attachment_pk: к какому pk прикреплять
:param res_peoples: что нужно записать
:param last_parse: возможен ли дальнейший парсинг
:param type_request: тип запроса
:return: attachment_pk
"""
if len(res_peoples) == 0:
return
get_request_db = GetRequestsToDB()
update_request_db = UpdateRequestsToDB()
if attachment_pk is None:
update_request_db.insert_in_table(
tb_name=update_request_db.get_requests,
data=[type_request, 0, REQUIRES_DATA,
time_now(), last_parse])
attachment_pk = get_request_db.get_records(
tb_name=get_request_db.get_requests,
select=['pk'],
one_record=True,
order='pk DESC')
get_request_db.connect_bd.close()
attachment_pk = int(attachment_pk['pk'])
peoples = json.dumps(res_peoples, ensure_ascii=False)[1:-1]
update_request_db.insert_in_table(
tb_name=update_request_db.additional_get_requests,
data=[attachment_pk, peoples])
del peoples
del res_peoples
gc.collect()
return attachment_pk
def news_email(
usr, saving_data
): #bsu_data, savings_account_data, savings_limit_account_data, retirement_data, usagesalary_data):
TO = usr.email
FROM = credentials()[0]
SUBJECT = 'Oppdatering fra Finansvarsel: ' + time_now('date.month.year')
news_firstname = usr.firstname
news_lastname = usr.lastname
bsu_data = saving_data[0]
savings_account_data = saving_data[1]
savings_limit_account_data = saving_data[2]
retirement_data = saving_data[3]
usagesalary_data = saving_data[4]
number_of_bsu_banks = str(len(bsu_data))
number_of_savings_banks = str(len(savings_account_data))
number_of_savings_limit_banks = str(len(savings_limit_account_data))
number_of_retirement_banks = str(len(retirement_data))
number_of_usagesalary_banks = str(len(usagesalary_data))
email_content = """
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>""" + SUBJECT + """</title>
</head>
<body>
<p>Hei """ + news_firstname + """ """ + news_lastname + """,<br></p>
<p><br></p>
<p>
"""
## Adding BSU data
if not bsu_data == '':
email_content += """
<b>Boligsparing Ungdom (BSU):</b> Finansvarsel har registrert <b>""" + number_of_bsu_banks + """</b> kontoer i norske banker med bedre rentevilkår enn banken du i dag vurderer/benytter:<p>
"""
yourBankId = get_user_savings_bank_id(usr, 'bsu')
productType = 'banksparing'
product = 'bsu'
for x in range(len(bsu_data)):
seletectedBankId = bsu_data[x].bank_id
url_change_bsu_bank = url_change_bank + '?yourBankId=' + yourBankId + '&selectedBankId=' + seletectedBankId + '&productType=' + productType + '&product=' + product
bank_name = bsu_data[x].bank_name
email_content += """
<p>""" + str(x + 1) + """. Rente: %.2f""" % bsu_data[
x].interest_rate + """%. <a href='""" + bsu_data[
x].bank_url + """'>""" + bank_name + """</a>. Søk om å bytte til """ + bank_name + """: """ + url_change_bsu_bank + """</p>
"""
email_content += """<p><br></p>"""
## Adding savings account data
if not savings_account_data == '':
email_content += """
<b>Sparekonto / Innskuddskonto (uten bruksbegrensning):</b> Finansvarsel har registrert <b>""" + number_of_savings_banks + """</b> kontoer i norske banker med bedre rentevilkår enn banken du i dag vurderer/benytter:<br>
"""
yourBankId = get_user_savings_bank_id(usr, 'savings_nolimit')
productType = 'banksparing'
product = 'bankinnskudd'
for x in range(len(savings_account_data)):
seletectedBankId = savings_account_data[x].bank_id
url_change_savings_nolimit_bank = url_change_bank + '?yourBankId=' + yourBankId + '&selectedBankId=' + seletectedBankId + '&productType=' + productType + '&product=' + product
bank_name = savings_account_data[x].bank_name
email_content += """
<p>""" + str(x + 1) + """. Rente: %.2f""" % savings_account_data[
x].interest_rate + """%. <a href='""" + savings_account_data[
x].bank_url + """'>""" + bank_name + """</a>. Søk om å bytte til """ + bank_name + """: """ + url_change_savings_nolimit_bank + """</p>
"""
email_content += """<p><br></p>"""
## Adding savings limit account data
if not savings_limit_account_data == '':
email_content += """
<b>Sparekonto / Innskuddskonto (med bruksbegrensning):</b> Finansvarsel har registrert <b>""" + number_of_savings_limit_banks + """</b> kontoer i norske banker med bedre rentevilkår enn banken du i dag vurderer/benytter:<br>
"""
yourBankId = get_user_savings_bank_id(usr, 'savings_limit')
productType = 'banksparing'
product = 'bankinnskudd'
for x in range(len(savings_limit_account_data)):
seletectedBankId = savings_limit_account_data[x].bank_id
url_change_savings_limit_bank = url_change_bank + '?yourBankId=' + yourBankId + '&selectedBankId=' + seletectedBankId + '&productType=' + productType + '&product=' + product
bank_name = savings_limit_account_data[x].bank_name
email_content += """
<p>""" + str(
x + 1
) + """. Rente: %.2f""" % savings_limit_account_data[
x].interest_rate + """%. <a href='""" + savings_limit_account_data[
x].bank_url + """'>""" + bank_name + """</a>. Søk om å bytte til """ + bank_name + """: """ + url_change_savings_limit_bank + """</p>
"""
email_content += """<p><br></p>"""
## Adding retirement account data
if not retirement_data == '':
email_content += """
<b>Pensjonssparekonto:</b> Finansvarsel har registrert <b>""" + number_of_retirement_banks + """</b> kontoer i norske banker med bedre rentevilkår enn banken du i dag vurderer/benytter:<br>
"""
yourBankId = get_user_savings_bank_id(usr, 'retirement')
productType = 'banksparing'
product = 'bankinnskudd'
for x in range(len(retirement_data)):
seletectedBankId = retirement_data[x].bank_id
url_change_retirement_bank = url_change_bank + '?yourBankId=' + yourBankId + '&selectedBankId=' + seletectedBankId + '&productType=' + productType + '&product=' + product
bank_name = retirement_data[x].bank_name
email_content += """
<p>""" + str(x + 1) + """. Rente: %.2f""" % retirement_data[
x].interest_rate + """%. <a href='""" + retirement_data[
x].bank_url + """'>""" + bank_name + """</a>. Søk om å bytte til """ + bank_name + """: """ + url_change_retirement_bank + """</p>
"""
email_content += """<p><br></p>"""
## Adding usage / salary data
if not usagesalary_data == '':
email_content += """
<b>Bruks-/lønnskonto:</b> Finansvarsel har registrert <b>""" + number_of_usagesalary_banks + """</b> kontoer i norske banker med bedre rentevilkår enn banken du i dag vurderer/benytter:<br>
"""
yourBankId = get_user_savings_bank_id(usr, 'usage_salary')
productType = 'banksparing'
product = 'bankinnskudd'
for x in range(len(usagesalary_data)):
seletectedBankId = usagesalary_data[x].bank_id
url_change_usagesalary_bank = url_change_bank + '?yourBankId=' + yourBankId + '&selectedBankId=' + seletectedBankId + '&productType=' + productType + '&product=' + product
bank_name = (usagesalary_data[x].bank_name)
email_content += """
<p>""" + str(x + 1) + """. Rente: %.2f""" % usagesalary_data[
x].interest_rate + """%. <a href='""" + usagesalary_data[
x].bank_url + """'>""" + bank_name + """</a>. Søk om å bytte til """ + bank_name + """: """ + url_change_usagesalary_bank + """</p>
"""
email_content += """<p><br></p>"""
email_content += """
<p>Tusen takk for at du benytter Finansvarsel som er utviklet av Fredrik Bakken. Prosjektet hadde ikke vært mulig uten dataene <a href='https://www.finansportalen.no'>Finansportalen</a> tilbyr.<br></p>
<p>Med vennlig hilsen,<br>
Finansvarsel<br>
http://fredrikbakken.no</p>
"""
send_email(SUBJECT, email_content, TO, FROM)
def parsing_by_groups(self, widgets: dict) -> None:
"""
Парсинг по критериям
:param widgets: словарь {ключ: виджет}
:return:
"""
pk = widgets['entry_pk'].get().strip()
if bool(pk) is False:
showerror('Не выбрана запись',
ERROR_MSG['Parsing']['Dont_choose_pk'])
return
pk = int(pk)
need_country = widgets['var_need_country'].get()
need_city_region = widgets['var_need_city_region'].get()
var_city_region = widgets['var_city_region'].get()
cmb_city_region = widgets['cmb_city_region'].get()
need_followers = widgets['var_need_count_followers'].get()
need_data = widgets['var_need_old'].get()
need_relationship = widgets['var_need_relationship'].get()
need_political = widgets['var_need_political'].get()
need_life_main = widgets['var_need_life_main'].get()
need_people_main = widgets['var_need_people_main'].get()
need_smoking = widgets['var_need_smoking'].get()
need_alcohol = widgets['var_need_alcohol'].get()
need_entry_status = widgets['var_need_entry_status'].get()
need_entry_about = widgets['var_need_entry_about'].get()
need_last_seen = widgets['var_need_last_seen'].get()
sex = widgets['var_sex'].get()
send_message = widgets['var_can_send_message'].get()
online = widgets['var_only'].get()
photos = widgets['var_has_photo'].get()
deactivated = widgets['var_deactivate'].get()
lbl_progress = widgets['lbl_progress']
progressbar = widgets['progressbar']
followers_from, followers_to, data_from, data_to = 0, 0, 0, 0
relationship, last_seen_from, last_seen_to, political = 0, 0, 0, 0
people_main, life_main, smoking, alcohol, entry_status = 0, 0, 0, 0, ''
entry_about, country, city, cities = '', 0, 0, []
if (need_country == 0) and (need_city_region == 1):
need = askyesno('Не включена настройка',
ASKS['Parsing']['Dont_enable_country'])
if need is False:
return
need_city_region = 0
if need_city_region == 1 and cmb_city_region == 'Нажмите "Настройка"':
need = askyesno('Не сделана настройка',
ASKS['Parsing']['Dont_settings'])
if need is False:
return
need_city_region = 0
if need_followers == 1:
try:
followers_from = widgets['var_followers_from'].get()
followers_to = widgets['var_followers_to'].get()
except TclError:
showerror('Неверное значение',
ERROR_MSG['Parsing']['Validate_follower']['Not_int'])
return
if followers_from > followers_to:
showerror(
'Неверное значение',
ERROR_MSG['Validate_follower']['Not_int']['From_more_to'])
return
if (followers_from > FOLLOWERS_MAX) or (followers_to >
FOLLOWERS_MAX):
showerror(
'Неверное значение', ERROR_MSG['Validate_follower']
['Not_int']['Max_value'].format(FOLLOWERS_MAX))
return
if need_data == 1:
data_from = int(widgets['var_old_from'].get())
data_to = int(widgets['var_old_to'].get())
if data_from > data_to:
showerror('Неверное значение',
ERROR_MSG['Parsing']['Validate_old']['From_more_to'])
return
if need_relationship == 1:
relationship = widgets['var_relationship'].get()
relationship = STATUS_VK_PERSON[relationship]
if (online == 0) and (need_last_seen == 1):
try:
last_seen_from = widgets['var_last_seen_from'].get()
last_seen_to = widgets['var_last_seen_to'].get()
except TclError:
showerror(
'Неверное значение',
ERROR_MSG['Parsing']['Validate_last_seen']['Not_int'])
return
if last_seen_from > last_seen_to:
showerror(
'Неверное значение',
ERROR_MSG['Parsing']['Validate_last_seen']['From_more_to'])
return
if (last_seen_from > LAST_SEEN_MAX) or (last_seen_to >
LAST_SEEN_MAX):
showerror(
'Неверное значение',
ERROR_MSG['Parsing']['Validate_last_seen']['Max_value'])
return
if need_political == 1:
political = widgets['var_political'].get()
political = POLITICAL[political]
if need_people_main == 1:
people_main = widgets['var_people_main'].get()
people_main = PEOPLE_MAIN[people_main]
if need_life_main == 1:
life_main = widgets['var_life_main'].get()
life_main = LIFE_MAIN[life_main]
if need_smoking == 1:
smoking = widgets['var_smoking'].get()
smoking = SMOKING[smoking]
if need_alcohol == 1:
alcohol = widgets['var_alcohol'].get()
alcohol = ALCOHOL[alcohol]
if need_entry_status == 1:
entry_status = widgets['var_entry_status'].get().strip()
if entry_status == '':
showerror('Неверное значение',
ERROR_MSG['Parsing']['Validate_status']['Empty'])
return
if need_entry_about == 1:
entry_about = widgets['var_entry_about'].get().strip()
if entry_about == '':
showerror('Неверное значение',
ERROR_MSG['Parsing']['Validate_about']['Empty'])
return
if need_country == 1:
country = widgets['var_country'].get()
country = LIST_COUNTRIES[country]
if need_city_region == 1:
city_region = widgets['var_city_region'].get()
if city_region == 0:
city = widgets['cmb_city_region'].get()
city = self.additional_functions.cities['cities'][city]
else:
region = widgets['cmb_city_region'].get()
region = self.additional_functions.regions['regions'][region]
params = {
'country_id': country,
'region_id': region,
'need_all': 1
}
cities_res = GetRequestsToVkApi().get_all_object(
'database.getCities', **params)
cities = []
for item in cities_res['items']:
cities.append(item['id'])
lbl_progress.configure(text='Подождите...')
lbl_progress.update()
get_requests_db = GetRequestsToDB()
records = get_requests_db.get_records(
tb_name=get_requests_db.get_requests,
select=['response', 'count_people'],
where=f'pk={pk}',
one_record=True)
count_peoples = records['count_people']
step = PROGRESSBAR_MAX / count_peoples
pg_value = 0
records = records['response']
iteration = 0
result = []
lbl_text = f'Прогресс {iteration}/{count_peoples}. Подождите, ' \
'это может занять несколько минут... '
configure_progress_lbl(progressbar, lbl_progress, pg_value, lbl_text)
if records != REQUIRES_DATA:
pks = [json.loads(f'[{records}]')]
else:
pks = get_requests_db.get_records(
tb_name=get_requests_db.additional_get_requests,
select=['pk'],
where=f'pk_attachment={pk}',
dictionary=False)
pks = [item[0] for item in pks]
del records
gc.collect()
for pk in pks:
if type(pk) == str or type(pk) == int:
record = get_requests_db.get_records(
tb_name=get_requests_db.additional_get_requests,
select=['response'],
where=f'pk={pk}',
one_record=True)['response']
record = json.loads(f'[{record}]')
else:
record = pk
for item in record:
iteration += 1
lbl_text = f'Прогресс {iteration}/{count_peoples}. ' \
'Подождите, это может занять несколько минут... '
pg_value += step
configure_progress_lbl(progressbar, lbl_progress, pg_value,
lbl_text)
if deactivated == 1:
if item.get('deactivated'):
continue
else:
if item.get('deactivated'):
result.append(item['id'])
continue
if need_country == 1:
if item.get('country'):
if item['country']['id'] != country:
continue
else:
continue
if need_city_region == 1:
if var_city_region == 0:
if item.get('city'):
if item['city']['id'] != city:
continue
else:
continue
else:
if item.get('city'):
if item['city']['id'] not in cities:
continue
else:
continue
if need_followers == 1:
if item.get('followers_count'):
count = item['followers_count']
if followers_from > count > followers_to:
continue
else:
continue
if need_data == 1:
if item.get('bdate'):
bdate = item['bdate'].split('.')
if len(bdate) != 3:
pass
else:
year_now = int(datetime.datetime.now().year)
date_from = year_now - data_from
date_to = year_now - data_to
bdate = int(bdate[2])
if date_from <= bdate or bdate <= date_to:
del year_now, date_from, date_to, bdate
continue
del year_now, date_from, date_to, bdate
if need_relationship == 1:
if item.get('relation'):
if item['relation'] != relationship:
continue
else:
continue
if sex != 0:
if item.get('sex'):
if item['sex'] != sex:
continue
else:
continue
if send_message == 1:
if item.get('can_write_private_message'):
if item['can_write_private_message'] != 1:
continue
else:
continue
if online == 1:
if item.get('online'):
if item['online'] != 1:
continue
else:
continue
if (online == 0) and (need_last_seen == 1):
if item.get('online'):
time = time_now()
time_from = time - (last_seen_from * 24 * 60 * 60)
time_to = time - (last_seen_to * 24 * 60 * 60)
if (item['online'] < time_from) or (item['online'] >
time_to):
continue
else:
continue
if photos == 1:
if item.get('has_photo'):
if item['has_photo'] != 1:
continue
else:
continue
if (need_political == 1) or (need_life_main == 1) or (
need_people_main == 1) \
or (need_smoking == 1) or (need_alcohol == 1):
if item.get('personal'):
personal = item['personal']
if need_political == 1:
if personal.get('political'):
if personal['political'] != political:
continue
else:
continue
if need_life_main == 1:
if personal.get('life_main'):
if personal['life_main'] != life_main:
continue
else:
continue
if need_people_main == 1:
if personal.get('people_main'):
if personal['people_main'] != people_main:
continue
else:
continue
if need_smoking == 1:
if personal.get('smoking'):
if personal['smoking'] != smoking:
continue
else:
continue
if need_alcohol == 1:
if personal.get('alcohol'):
if personal['alcohol'] != alcohol:
continue
else:
continue
else:
continue
if need_entry_about == 1:
if item.get('about'):
if entry_about not in item['about']:
continue
else:
continue
if need_entry_status == 1:
if item.get('status'):
if entry_status not in item['status']:
continue
else:
continue
result.append(item['id'])
del pk, record
gc.collect()
lbl_progress.configure(text='Запись данных',
foreground=styles.NOTABLE_LABEL_FONT)
lbl_progress.update()
count = len(result)
if count == 0:
configure_progress_lbl(lbl=lbl_progress)
showinfo('Не найдено пользователей',
INFO_MSG['Parsing']['none_value_by_criteria'])
return
configure_progress_lbl(progressbar, lbl_progress, 0,
'Запись результатов', styles.NOTABLE_LABEL_FONT)
type_request = NAME_PARSING['by_criteria']
UpdateRequestsToDB().insert_many_values_into_get_requests(
type_request=type_request,
count=count,
response=result,
time=time_now(),
last_parse=0)
configure_progress_lbl(lbl=lbl_progress)
del result, pks, lbl_text
gc.collect()