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()