def make_filter_list(filter_id, rows):
"""
make list with assigned value
:param filter_id:
:param rows:
:return:
"""
session = Session()
filter_ = session.query(FilterF).get(filter_id)
ready_valus = []
for row in rows:
flag = False
row = [i.strip().lower() for i in row]
for category in filter_.categorys:
items = [[b.strip().lower() for b in a]
for a in category.items]
if row in items:
#make QStandardItem to make column in sti model
item = QStandardItem(str(category.name))
ready_valus.append(item)
flag = True
if flag is False:
item = QStandardItem('')
ready_valus.append(item)
session.close()
return ready_valus
def assign_value_for_filter(self, filter_id, show=True):
"""
preper list with value from main filter, send it to make_filter_list.
Replace column for filter_id with column with assigned data
:param filter_id:
:return:
"""
session = Session()
filter_ = session.query(FilterF).get(filter_id)
rows = []
for row in range(self.sti.rowCount()):
row_value = [
self.sti.item(row, col).text() for col in filter_.columns
]
rows.append(row_value)
read_rows = self.make_filter_list(filter_id, rows)
col_nr = filter_.column_nr
self.sti.takeColumn(col_nr)
self.sti.insertColumn(col_nr, read_rows)
self.sti.setHorizontalHeaderLabels(self.headers)
session.close()
self.set_color_on_header()
if show:
QMessageBox.information(self, "Informacja", "Operacja zakończona.")
def run_filters(self):
"""
make filters
:return:
"""
session = Session()
# filters_ = session.query(FilterF).filter_by(type='words').all()
filters_ = session.query(FilterF).all()
for fil in filters_:
if fil.type == 'words':
rows = self.get_data_from_columns(fil.columns)
assignded_column = self.make_words_list(fil.id, rows)
self.replace_column(fil.column_nr, assignded_column)
elif fil.type == 'cut':
if fil.name == 'model':
rows = self.get_data_from_columns(fil.columns)
assignded_column = self.filter_cut_model(rows)
self.replace_column(fil.column_nr, assignded_column)
elif fil.name == 'subbrand_brand_model':
rows = self.get_data_from_columns(fil.columns)
assignded_column = self.join_columns(rows)
self.replace_column(fil.column_nr, assignded_column)
session.close()
QMessageBox.information(self, "Informacja", "Operacja zakończona.")
def make_words_list(filter_id, rows):
"""
make words list for filters,
if find word in list assigned name of category
:param filter_id:
:param rows:
:return:
"""
session = Session()
filter_ = session.query(FilterF).get(filter_id)
ready_valus = []
for row in rows:
flag = False
row = [i.lower() for i in row]
row = ' '.join(row)
for category in filter_.categorys:
words = [b.lower() for b in category.words]
for word in words:
if word in row:
item = QStandardItem(str(category.name))
ready_valus.append(item)
flag = True
break
if flag is False:
item = QStandardItem('')
ready_valus.append(item)
session.close()
return ready_valus
def set_color_on_header(self):
"""
changes the color of the header
:return:
"""
session = Session()
filtersf = session.query(FilterF).all()
for filterf in filtersf:
if filterf.type == 'manual':
self.table.model().setHeaderData(filterf.column_nr,
Qt.Horizontal,
QBrush(QColor(121, 166, 210)),
Qt.BackgroundRole)
self.table.model().setHeaderData(
filterf.column_nr, Qt.Horizontal,
self.headers[filterf.column_nr], Qt.DisplayRole)
if filterf.type in ('words', 'cut'):
self.table.model().setHeaderData(filterf.column_nr,
Qt.Horizontal,
QBrush(QColor(212, 214, 219)),
Qt.BackgroundRole)
self.table.model().setHeaderData(
filterf.column_nr, Qt.Horizontal,
self.headers[filterf.column_nr], Qt.DisplayRole)
session.close()
def run_filtes(self):
"""
run assign function from main form for all checked filters id.
"""
chitems = self.get_checked_items(self.lw_filters)
session = Session()
filter_id = [fil.id for fil in session.query(FilterF).filter(FilterF.name.in_(chitems)).all()]
#start assign
for id in filter_id:
self.main.assign_value_for_filter(id, False)
QMessageBox.information(self, 'Filtry', 'Operacja zakończona')
def get_filters(self):
"""
connect to data base and get all manual filters
"""
try:
session = Session()
filters = session.query(FilterF).filter_by(type='manual').all()
except:
QMessageBox.critical(self, 'Bład', 'Brak połaczenia z baza danych')
filter_name = [fil.name for fil in filters]
self.set_cb_value(self.lw_filters, filter_name)
session.close()
def connect_raports(self):
"""
raports connected
:return:
"""
main_rap_txt = self.cb_main_rap.currentText()
conne_rap_txt = self.cb_connect_rap.currentText()
if main_rap_txt != conne_rap_txt:
try:
session = Session()
main_rap = session.query(Competitive).filter_by(
name=main_rap_txt).first()
conne_rap = session.query(Competitive).filter_by(
name=conne_rap_txt).first()
#data_rows = session.query(Data).filter_by(competitive_id=conne_rap.id).all()
main_rap.datas = main_rap.datas + conne_rap.datas
session.commit()
session.close()
QMessageBox.information(self, "Raporty",
"Raporty zostały połączone.")
except:
pass
else:
QMessageBox.critical(
self, "Błąd",
"Nie można połączyć raportów o tej samej nazwie.")
def add_new_competitive(self):
"""
add new competitive name to data base
:return:
"""
new_name = self.leaddraport.text()
if self.check_is_not_empty(new_name):
"""
add new name to data base
"""
try:
session = Session()
name = Competitive(new_name)
session.add(name)
session.commit()
session.close()
except exc.IntegrityError:
QMessageBox.critical(self, "Bład", "Błąd zapisu danych do bazy.\n Podana nazwa już istnieje")
return
"""
add value to combo box and clear line edit
"""
self.leaddraport.setText('')
self.cbraports.addItem(new_name)
self.cbrapo_remove.addItem(new_name)
self.cbraports.setCurrentText(new_name)
def set_filter_section(self):
session = Session()
id_nr = self.get_data_id()
year = [
ye.year for ye in session.query(Data).distinct(Data.year).filter(
Data.competitive_id == id_nr).order_by(Data.year).all()
]
self.set_cb_value(self.lv_year, year)
month = [
mo.month for mo in session.query(Data).distinct(Data.month).filter(
Data.competitive_id == id_nr).order_by(Data.month).all()
]
self.set_cb_value(self.lv_month, month)
week = [
we.week_nr
for we in session.query(Data).distinct(Data.week_nr).filter(
Data.competitive_id == id_nr).order_by(Data.week_nr).all()
]
self.set_cb_value(self.lv_week, week)
media = [
me.media for me in session.query(Data).distinct(Data.media).filter(
Data.competitive_id == id_nr).order_by(Data.media).all()
]
self.set_cb_value(self.lv_media, media)
session.close()
def showfilterforms(self, i):
"""
Show filters for collumn nr"
:param i:
:return:
"""
session = Session()
filter_ = session.query(FilterF).filter_by(column_nr=i).one_or_none()
if filter_ is not None and filter_.type == 'manual':
columns = self.prapercolumns(filter_.columns)
headersname = [self.headers[i] for i in filter_.columns]
self.filter = FiltersForm(filter_.id, columns, headersname, self)
self.filter.show()
session.close()
def get_data_from_database(self):
"""
get all categorys for filter and send to show in treewidget
"""
session = Session()
categorys = session.query(Category).filter_by(filter_id=self.filter_id).all()
self.tw.blockSignals(True)
for category in categorys:
head = QTreeWidgetItem(self.tw, [category.name])
head.setCheckState(0, Qt.CheckState.Unchecked)
for item in category.items:
newch = QTreeWidgetItem(head, [''] + item)
newch.setCheckState(1, Qt.CheckState.Unchecked)
self.tw.blockSignals(False)
def populate_row_competitive(self):
"""
read rows from database with competitive name
:return:
"""
try:
session = Session()
competits = session.query(Competitive).all()
except:
QMessageBox.critical(self, "Błąd", "Nie można połączyć się z bazą danych")
return
for compet in competits:
self.cbraports.addItem(compet.name)
self.cbrapo_remove.addItem((compet.name))
def set_raports(self):
"""
fill combo box: cb_raports
"""
try:
session = Session()
competitiev = session.query(Competitive).all()
except:
QMessageBox.critical(self, "Błąd", "Nie można połączyć się z bazą danych")
return
self.cb_raports.clear()
for compet in competitiev:
self.cb_raports.addItem(compet.name)
session.close()
def get_data_id(self):
com_name = self.cb_raport_name.currentText()
self.compative_name = com_name
if com_name != '':
try:
session = Session()
compative_id = session.query(
Competitive.id).filter_by(name=com_name).one()
except:
QMessageBox.critical(self, "Błąd",
"Nie można połączyć się z bazą danych")
return
session.close()
return compative_id.id
def lw_channge_month(self):
years = self.get_checked_items(self.lv_year)
months = self.get_checked_items(self.lv_month)
session = Session()
compative = session.query(Competitive).filter_by(
name=self.compative_name).one()
# week
week = [
we.week_nr
for we in session.query(Data).distinct(Data.week_nr).filter(
and_(
Data.year.in_(years),
Data.month.in_(months),
Data.competitive_id == compative.id,
)).order_by(Data.week_nr).all()
]
self.set_cb_value(self.lv_week, week)
media = [
me.media for me in session.query(Data).distinct(Data.media).filter(
and_(
Data.year.in_(years),
Data.month.in_(months),
Data.week_nr.in_(week),
Data.competitive_id == compative.id,
)).order_by(Data.media).all()
]
self.set_cb_value(self.lv_media, media)
session.close()
def set_compatives(self):
"""
read rows from database with competitive name
:return:
"""
try:
session = Session()
competitev = session.query(Competitive).all()
except:
QMessageBox.critical(self, "Błąd",
"Nie można połączyć się z bazą danych")
return
self.cb_raport_name.clear()
for compet in competitev:
self.cb_raport_name.addItem(compet.name)
session.close()
def save(self):
"""
save data to data base
:return:
"""
#deleta rows from data base for compative name
session = Session()
comat = session.query(Competitive).filter(
Competitive.name.ilike(f'%{self.compative_name}%')).first()
session.query(Data).filter_by(competitive_id=comat.id).delete()
#read data from row and save to data base
for row in range(self.sti.rowCount()):
datas = []
for col in range(self.sti.columnCount()):
if col in (0, 1, 2, 20, 24):
try:
#tutaj poprawic nie chce wpisać
if self.sti.item(row, col) is not None:
datas.append(int(self.sti.item(row, col).text()))
else:
datas.append(None)
except ValueError:
datas.append(None)
elif col in (25, 26, 28, 29):
try:
if self.sti.item(row, col) is not None:
datas.append(float(self.sti.item(row, col).text()))
else:
datas.append(None)
except ValueError:
datas.append(None)
else:
if self.sti.item(row, col) is not None:
datas.append(self.sti.item(row, col).text())
else:
datas.append(None)
comat.datas.append(Data(*datas))
session.commit()
session.close()
QMessageBox.information(self, "Zapis",
"Zapis zakonczyl się powodzeniem.")
def set_table(self):
"""
set tabel with data from data base
:return:
"""
#temporaty get all data, after change to set query
id_nr = self.get_data_id()
years = self.get_checked_items(self.lv_year)
months = self.get_checked_items(self.lv_month)
weeks = self.get_checked_items(self.lv_week)
media = self.get_checked_items(self.lv_media)
session = Session()
# data = session.query(Data).filter_by(competitive_id=id_nr).all()
data = session.query(Data).filter(
and_(
Data.year.in_(years),
Data.month.in_(months),
Data.week_nr.in_(weeks),
Data.media.in_(media),
Data.competitive_id == id_nr,
)).all()
headers = Data.__table__.columns.keys()
#remove id, compatitive_id
headers = headers[2:]
#make list
final_list = []
for row in data:
columns = []
for key in headers:
columns.append(row.__dict__[key])
final_list.append(columns)
session.close()
return final_list
def get_data(self, paths, compative_name):
"""
receives data from the filechose form
:param paths: dictionary with paht to techege, adexpert
:param compative_name: name of raport
:return:
"""
self.compative_name = compative_name
if len(paths[0]) > 0:
self.techegedata = Excel.get_data(paths[0])
else:
self.techegedata = None
if len(paths[1]) > 0:
self.adxpert = Excel.get_data(paths[1], False)
else:
self.adxpert = None
#wczytanie arkusza z bazy danych
session = Session()
self.compativedata = session.query(Competitive).filter_by(
name=compative_name).first()
self.populate_row()
session.close()
def remove_competitive(self):
"""
remove compatitive from comboboxes and database
:return:
"""
reply = QMessageBox.information(self, 'Uwaga!!!',
"Usniecia raportu spowoduje usunicie powiązanych danych.\n Kliknij nie aby anulować operacje. ",
QMessageBox.Yes | QMessageBox.No, QMessageBox.No)
if reply == QMessageBox.Yes:
cur_text = self.cbrapo_remove.currentText()
session = Session()
session.query(Competitive).filter_by(name = cur_text).delete()
session.commit()
session.close()
self.cbrapo_remove.removeItem(self.cbrapo_remove.currentIndex())
self.cbraports.removeItem(self.cbraports.findText(cur_text))
def remove_duplicate(self):
"""
remove duplicate from list
"""
data = self.get_data()
rows = [x[2:32] for x in data]
session = Session()
for nr in reversed(range(len(rows))):
if rows.count(rows[nr]) > 1:
rows.pop(nr)
session.query(Data).filter(and_(
Data.id == data[nr][0],
Data.competitive_id == data[nr][1]
)).delete()
session.commit()
session.close()
self.li_nr_dup.setText('')
QMessageBox.information(self,'Duplicaty', 'Spoty zostały usunięte')
def get_data(self):
rap_name = self.cb_raports.currentText()
session = Session()
compative = session.query(Competitive).filter_by(name=rap_name).first()
data = session.query(Data).filter_by(competitive_id=compative.id).all()
headers = Data.__table__.columns.keys()
# remove id, compatitive_id
headers = headers[0:32]
# make list
final_list = []
for row in data:
columns = []
for key in headers:
columns.append(row.__dict__[key])
final_list.append(columns)
session.close()
return final_list
def save_data_from_tree(self):
"""
1) delete in data base all categorys for filter
2) save all catecorys and under categroy to database
"""
#delete all categorys
session = Session()
filter_ = session.query(FilterF).get(self.filter_id)
delete_q = Category.__table__.delete().where(Category.filter_id == filter_.id)
session.execute(delete_q)
session.commit()
root = self.tw.invisibleRootItem()
child_count = root.childCount()
for i in range(child_count):
item = root.child(i)
if item.parent() is None:
parent_name = item.text(0)
all_childs = []
for j in range(item.childCount()):
childs = [item.child(j).text(i) for i in range(self.tw.columnCount())[1:]]
all_childs.append(childs)
new_category = Category(parent_name, all_childs)
filter_.categorys.append(new_category)
session.commit()
session.close()
def get_user_annotation(annotation):
session = Session()
return session.query(User).filter(User.annotation == annotation).all()
def get_users():
session = Session()
return session.query(User).all()
