def get_autores(): a = dbmain.query_many('select au_id, au_name from authors order by au_name asc') gl.dsAutores = [] #QStringList() gl.autores_dict = {} for n in a: gl.autores_dict[n[1].lower()] = n[0] gl.dsAutores.append(n[1])
def get_book_data(index): conn = psycopg2.connect(gl.conn_string) dict_cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) sql = '''SELECT livros.pu_id, livros.pu_title, pu_sub_title, authors.au_name, livros.pu_cota,types.ty_name, livros.pu_obs, livros.pu_isbn,status.st_nome, pu_sinopse, pu_volume,pu_ed_year,types.ty_id,status.st_id FROM livros, authors,types, status WHERE livros.pu_id = %s and types.ty_id = livros.pu_type and livros.pu_author_id=authors.au_id AND livros.pu_status = status.st_id;''' dict_cur.execute(sql, (index, )) gl.record_current_dict = dict_cur.fetchone() dict_cur.close() conn.close() if not gl.record_current_dict: # houve um erro e o registo está limpo. return False else: # load level 1 gl.tags_special_level1_data = dbmain.query_many('''select tag_key,ta_name,tags_special_name from tags_reference inner join tags on tags_reference.tags_ref_tag_id=tags.ta_id inner join tags_special on tags.tag_key=tags_special.tags_special_key where tags_ref_book=%s and tag_key is not null and tags_special.tags_special_level=1 order by tags_special.tags_special_order''', (index,)) return True
def get_types(): a = dbmain.query_many('select ty_id,ty_name from types ') gl.types_dict ={} gl.ds_types = [] for n in a: gl.types_dict[n[1].lower()] = n[0] gl.ds_types.append(n[1])
def main_search_click(self): """will search in title, tags and ISBN""" if not self.mainToSearchEdt.text() == '': print(self.mainSearchCbox.currentText()) if self.mainSearchCbox.currentIndex() == 0: # title gl.SEARCH_DICT = { 'WHERE': 'title', 'WHAT': self.mainToSearchEdt.text() } elif self.mainSearchCbox.currentIndex() == 1: # ISBN gl.SEARCH_DICT = { 'WHERE': 'isbn', 'WHAT': self.mainToSearchEdt.text() } elif self.mainSearchCbox.currentIndex() == 2: # tags gl.SEARCH_DICT = { 'WHERE': 'tags', 'WHAT': self.mainToSearchEdt.text() } gl.SEARCH_DICT.update(self.filter_options()) sql = lib_gabo.make_sql(gl.SEARCH_DICT) gl.FILTER_DATASET = dbmain.query_many(sql) gl.records_in_ds = len(gl.FILTER_DATASET) if gl.records_in_ds == 0: self.grid.setRowCount(0) else: self.update_grid()
def get_status(): a = dbmain.query_many('select st_id,st_nome from status') gl.dsStatus = [] gl.status_dict = {} for n in a: gl.status_dict[n[1].lower()] = n[0] gl.dsStatus.append(n[1])
def get_areas(): gl.db_params = stdio.read_config_file('gabo.ini') gl.db_params = gl.db_params[1] gl.conn_string = "host=" + gl.db_params['db_host'] + ' port=' + gl.db_params['db_port'] + ' dbname=' + gl.db_params[ 'db_database'] + \ ' user='******'db_user'] + ' password='******'db_password'] areas = dbmain.query_many( 'select distinct pu_cota from livros order by pu_cota')
def get_tags_from_record(self): sql = 'SELECT tags.ta_name FROM tags_reference INNER JOIN public.tags ON (public.tags_reference.tags_ref_tag_id = public.tags.ta_id)\ where tags_reference.tags_ref_book =%s and tags_ref_level =0' # + str(self.pub_id) a = dbmain.query_many(sql, (self.pub_id, )) tags = '' for n in a: tags += n[0].lower() + ',' return tags.rstrip(',')
def get_special_tags(level=1): a = dbmain.query_many('''SELECT tags_special_name, tags_special_key FROM tags_special WHERE tags_special_level=%s ORDER BY tags_special_order''', (level,)) gl.tag_special_list = [] for n in a: gl.tag_special_list.append((n[0], n[1])) gl.tag_special_dict[n[1].upper()] = n[0]
def search_tags_mode_click(self): if not self.tags_to_searchEdit.text() == '': self.mainToSearchEdt.setText('') sql = self.make_sql() gl.FILTER_DATASET = dbmain.query_many(sql) if len(gl.FILTER_DATASET) == 0: self.grid.setRowCount(0) else: self.update_grid()
def filter_cbox_change(self): gl.SEARCH_DICT.update(self.filter_options()) sql = lib_gabo.make_sql(gl.SEARCH_DICT) gl.FILTER_DATASET = dbmain.query_many(sql) gl.records_in_ds = len(gl.FILTER_DATASET) if gl.records_in_ds == 0: self.grid.setRowCount(0) else: self.update_grid()
def show_delete_click(self): sql = '''SELECT livros.pu_id, livros.pu_title, authors.au_name, types.ty_name, status.st_nome, livros.pu_cota, livros.pu_volume FROM livros, authors, types, status WHERE''' sql += ' livros.pu_author_id =0 and livros.pu_type > 0 and livros.pu_status > 0 and ' sql += ''' livros.pu_status = status.st_id AND authors.au_id = livros.pu_author_id AND types.ty_id = livros.pu_type ORDER BY pu_id desc;''' gl.FILTER_DATASET = dbmain.query_many(sql) self.update_grid()
def get_params(): a = dbmain.query_many('select * from params') for n in a: if n[0] == 'LAST_TAGS': toto = n[1].rstrip(',') dum = toto.split(',') for f in dum: gl.last_tags.append(f) elif n[0] == 'SHOW_RECORDS': gl.SHOW_RECORDS = n[1] elif n[0] == 'OWNER': gl.OWNER = n[1]
def grid_refresh(self): sql = '''select pu_cota, count(pu_cota) from livros group by pu_cota order by pu_cota''' dataset = dbmain.query_many(sql) ex_grid.ex_grid_update(self.grid, { 0: ['Cota', 's'], 1: ['Livros', 'i'] }, dataset) self.grid.setColumnWidth(0, 200) self.grid.setColumnWidth(1, 60)
def get_data(self): sql = '''SELECT livros.pu_id, livros.pu_title, authors.au_name, types.ty_name, status.st_nome, livros.pu_cota,livros.pu_volume, pu_ed_year FROM livros, authors, types, status WHERE livros.pu_status = status.st_id AND authors.au_id = livros.pu_author_id AND types.ty_id = livros.pu_type ORDER BY livros.pu_title ASC LIMIT 50;''' a = dbmain.query_many(sql) gl.FILTER_DATASET = a # é global para que possa ser utilizado nos relatorios em html return gl.FILTER_DATASET
def tag_refresh(self): self.c_grid = 10 # sql = '''select ta_id, ta_name from tags order by ta_name''' sql = '''select ta_id,ta_name from tags where tag_key is null order by ta_name''' dataset = dbmain.query_many(sql) ex_grid.ex_grid_update(self.grid, { 0: ['ID', 'i'], 1: ['Nome', 's'] }, dataset, hidden=0) self.grid.horizontalHeader().setVisible(False) self.grid.setColumnWidth(0, 80) self.grid.setColumnWidth(1, 350)
def tag_search_changed(self, text): if len(text) > 3: search = '\'%%' + text + '%%\'' # print 'search ',text sql = '''select ta_id, ta_name from tags where ta_name like unaccent(''' + search + ''') order by ta_name''' dataset = dbmain.query_many(sql) ex_grid.ex_grid_update(self.grid, { 0: ['ID', 'i'], 1: ['Nome', 's'] }, dataset, hidden=0) self.grid.horizontalHeader().setVisible(False) self.grid.hideColumn(0) self.grid.setColumnWidth(1, 200)
def limit_change(self): gl.SHOW_RECORDS = self.recordLimitEdt.text() try: dum = int(gl.SHOW_RECORDS) except ValueError: gl.SHOW_RECORDS = '80' self.recordLimitEdt.setText(gl.SHOW_RECORDS) data_access.save_param('SHOW_RECORDS', gl.SHOW_RECORDS) self.sortByCbox.setCurrentIndex(0) self.typesCbox.setCurrentIndex(0) self.last_fiveBtn.setText('Ultimos ' + gl.SHOW_RECORDS) foo = lib_gabo.make_sql_raw(gl.SHOW_RECORDS, True) gl.FILTER_DATASET = dbmain.query_many(foo) gl.records_in_ds = len(gl.FILTER_DATASET) self.update_grid()
def locals_click(self): form = locals.BrowserLocals() form.exec_() if not form.toto == '': gl.records_in_ds = 0 gl.LAST_SEARCH_WHERE = 1 gl.SEARCH_DICT = {'WHERE': 'local', 'WHAT': form.toto} gl.SEARCH_DICT.update(self.filter_options()) sql = lib_gabo.make_sql(gl.SEARCH_DICT) gl.FILTER_DATASET = dbmain.query_many(sql) gl.records_in_ds = len(gl.FILTER_DATASET) if gl.records_in_ds == 0: self.grid.setRowCount(0) else: self.update_grid()
def grid_search_changed(self, text): search = '\'%%' + text.lower() + '%%\'' sql = '''select pu_author_id, authors.au_name,count(*) as a from livros,authors where authors.au_id = pu_author_id and unaccent(lower(au_name)) like unaccent(''' + search + ''') group by pu_author_id,authors.au_name order by authors.au_name''' dataset = dbmain.query_many(sql) ex_grid.ex_grid_update(self.authorGrid, { 0: ['ID', 'i'], 1: ['Nome', 's'], 2: ['Livros', 'i'] }, dataset) self.authorGrid.setColumnWidth(0, 0) self.authorGrid.setColumnWidth(1, 500) self.authorGrid.setColumnWidth(2, 60)
def grid_refresh(self): self.c_grid = 10 sql = '''select pu_author_id, authors.au_name,count(*) as a from livros,authors where authors.au_id = pu_author_id and au_id > 0 group by pu_author_id,authors.au_name order by authors.au_name''' dataset = dbmain.query_many(sql) ex_grid.ex_grid_update(self.authorGrid, { 0: ['ID', 'i'], 1: ['Nome', 's'], 2: ['Livros', 'i'] }, dataset) self.authorGrid.setColumnWidth(0, 0) self.authorGrid.setColumnWidth(1, 500) self.authorGrid.setColumnWidth(2, 60)
def set_sizes(): gl.db_params = stdio.read_config_file('gabo.ini') gl.db_params = gl.db_params[1] gl.conn_string = "host=" + gl.db_params['db_host'] + ' port=' + gl.db_params['db_port'] + ' dbname=' + gl.db_params[ 'db_database'] + \ ' user='******'db_user'] + ' password='******'db_password'] dbmain.execute_query('delete from sizes', (True, )) t = dbmain.query_many('select ta_name from tags order by ta_name') for n in t: a = n[0].split('x') if len(a) == 3: try: dum = int(a[0]) + int(a[1]) + int(a[2]) print(n[0]) dbmain.execute_query( 'insert into sizes (size_name) VALUES (%s)', (n[0], )) except ValueError: pass
def calc_width_in_filter(): publication_ids = [] xl = dbmain.query_many(gl.CURRENT_SQL) for n in xl: publication_ids.append(str(n[0])) dimentions = [] for n in xl: sql = '''select ta_name, livros.pu_title from tags_reference left join livros on tags_reference.tags_ref_book = livros.pu_id left join tags on ta_id=tags_reference.tags_ref_tag_id where livros.pu_id =''' + str(n[0]) + ''' and ta_name like \'dim:%\'''' as_dim = dbmain.query_one_simple(sql) if as_dim is None: dimentions.append((n[0], n[1], n[2], n[5], n[6], n[7], '')) else: dimentions.append((n[0], n[1], n[2], n[5], n[6], n[7], as_dim[0].replace('dim:', ''))) leng = 0 max_width = 0 max_height = 0 missing = 0 for n in dimentions: # print(n) if n[6] == '': missing += 1 else: dum = n[6].replace('dim:', '') dum_list = dum.split('x') leng += int(dum_list[2]) if int(dum_list[0]) > max_width: max_width = int(dum_list[0]) if int(dum_list[1]) > max_height: max_height = int(dum_list[1]) print(' largura:', leng / 10.0) print('profundidade:', max_width / 10.0) print(' altura:', max_height / 10.0) print(' livros:', len(dimentions)) print(' livros SEM:', missing) print('-' * 40) return dimentions
def info_click(self): dataset = [] dataset.append(('Livros', str( dbmain.query_one('select count(pu_id) from livros', (True, ))[0]))) dataset.append( ('Autores', str( dbmain.query_one('select count(au_id) from authors', (True, ))[0]))) dataset.append(('Por categoria', '')) dataset = dataset + (dbmain.query_many( '''select types.ty_name, to_char(count(*),'999999999') as a from livros,types where types.ty_id = pu_name group by pu_name,types.ty_name order by ty_name asc''')) self.grid.setRowCount(len(dataset)) ex_grid.ex_grid_update(self.grid, { 0: ['Tabela', 's'], 1: ['Total', 'sr'] }, dataset)
def simple_table(): pdfmetrics.registerFont(TTFont('calibri', "calibri.ttf")) pdfmetrics.registerFont(TTFont('calibrib', "calibrib.ttf")) # and # unaccent(lower(authors.au_name)) # LIKE # unaccent('%varios%') data = dbmain.query_many( '''SELECT livros.pu_id, left(livros.pu_title, 80), left(authors.au_name, 40), left(types.ty_name, 3), left(status.st_nome,5),livros.pu_cota,livros.pu_volume, pu_ed_year FROM livros, authors, status, types where authors.au_id=livros.pu_author_id and types.ty_id= livros.pu_type and status.st_id= livros.pu_status AND livros.pu_status = status.st_id AND authors.au_id = livros.pu_author_id AND types.ty_id = livros.pu_type and types.ty_id not in(7,8,9,10,11) ORDER BY livros.pu_title asc LIMIT 99999 ''') # '%charlier,giraud%' doc = SimpleDocTemplate("simple_table.pdf", pagesize=A4, rightMargin=72, leftMargin=62, topMargin=30, bottomMargin=18) styles = getSampleStyleSheet() story = [] text = "<font face=calibri size=8>cabeçalho</font>" para = Paragraph(text, style=styles["Normal"]) story.append(para) cnt = 1 page_count = 1 PAGE_BREAK = 75 back_color = colors.lightgrey for n in data: # a = [n] # print(a) if cnt & 1: back_color = colors.lightgrey else: back_color = colors.white tblstyle = TableStyle([('ROWBACKGROUNDS', (0, 0), (-1, -1), [back_color, colors.green]), ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'), ('FONT', (0, 0), (-1, -1), 'calibri', 8), ('ALIGN', (0, 0), (0, -1), 'RIGHT'), ('ALIGN', (1, 0), (5, -1), 'LEFT'), ('ALIGN', (6, 0), (6, -1), 'RIGHT'), ('ALIGN', (7, 0), (7, -1), 'RIGHT')]) tbl = Table([n], colWidths=[30, 260, 150, 20, 30, 50, 20, 20], rowHeights=[10 for x in range(len([n]))]) if PAGE_BREAK == cnt: story.append(tbl) tbl.setStyle(tblstyle) para = Paragraph("<font face=calibri size=8>Página " + str(page_count) + "</font>", style=styles["Normal"]) story.append(para) text = "<font face=calibri size=8>Livros" + "</font>" cnt = 1 para = Paragraph(text, style=styles["Normal"]) story.append(para) page_count += 1 else: story.append(tbl) tbl.setStyle(tblstyle) cnt += 1 story.append(para) doc.build(story)
def make_sql(what='', where_index='nada', sort_by=''): gl.CURRENT_SQL = '' select_ = "SELECT livros.pu_id, livros.pu_title, authors.au_name, types.ty_name, status.st_nome,livros.pu_cota,livros.pu_volume, pu_ed_year FROM livros, authors, types, status" join_ = " livros.pu_status = status.st_id AND authors.au_id = livros.pu_author_id AND types.ty_id = livros.pu_type " where_ = ' WHERE ' order_ = '' search_ = '' limit_ = '' where_index = where_index.lower() # self.key_sort = self.sort_dic[self.sortByCbox.currentText().lower()] if not what == '': self.recordLimitEdt.setText('0') # self.key_search = self.sort_dic[where_index] if where_index == 'local': # na cota tem de ser exactamente igual self.key_search = self.sort_dic['local'] text_to_search = "\'" + what.lower().strip() + "\'" else: text_to_search = "\'%" + what.lower().strip() + "%\'" if len(text_to_search) > 1: where_ += '''unaccent(lower(''' + self.key_search + ''')) LIKE unaccent(''' + text_to_search + ''') AND ''' else: where_ += ''' unaccent(upper(''' + self.key_search + ''')) SIMILAR TO ''' + '''unaccent(''' + text_to_search.upper( ) + ')' + ''' AND ''' elif not self.tags_to_searchEdit.text() == '': # modo para tags self.recordLimitEdt.setText('0') tags = self.tags_to_searchEdit.text().replace("\'", "\\\'") tags = tags.split(',') in_data = '' if self.logicTags.currentIndex() == 0: # ou for n in tags: toto = n.lower().strip() in_data += "unaccent(\'" + toto + "\')," a = dbmain.query_many( '''select ta_id from tags where unaccent(ta_name) in (''' + in_data[:-1] + ''')''') i = [] for t in a: i.append(t[0]) if i: i = str(i) where_ += ''' pu_id in(select * from (select tags_ref_book from tags_reference where tags_ref_tag_id in(''' + i[ 1:-1] + ''')) as foo) AND ''' else: where_ += '''pu_id in(select * from (select tags_ref_book from tags_reference where tags_ref_tag_id in(-1)) as foo) AND ''' else: t = '' for n in tags: t = t + "\'" + n.strip() + "\'," t = t[:-1] c = str(len(tags)) where_ = ''' where EXISTS (SELECT NULL FROM tags_reference tg JOIN TAGS t ON t.ta_id = tg.tags_ref_tag_id WHERE unaccent(t.ta_name) IN (unaccent(''' + t + ''')) AND tg.tags_ref_book = livros.pu_id GROUP BY tg.tags_ref_book HAVING COUNT(t.ta_name) =''' + c + ''') and ''' else: # não procura em nada where_ += ''' livros.pu_status = status.st_id AND authors.au_id = livros.pu_author_id AND types.ty_id = livros.pu_type and ''' self.key_sort = self.sort_dic[self.sortByCbox.currentText().lower()] if self.typesCbox.currentIndex() != 0: where_ += " livros.pu_type = (select ty_id from types where ty_name like \'" + str( self.typesCbox.currentText()) + '\') and ' if self.statusCbox.currentIndex() != 0: where_ += " livros.pu_status = (select st_id from status where st_nome like \'" + str( self.statusCbox.currentText()) + "\') and " order_ = ''' ORDER BY ''' + self.key_sort + ' asc ' if int(gl.SHOW_RECORDS) > 0: # int(self.recordLimitEdt.text()) > 0: limit_ = 'LIMIT ' + gl.SHOW_RECORDS order_ = ''' ORDER BY ''' + self.key_sort + ' asc ' else: self.recordLimitEdt.setText(gl.SHOW_RECORDS) limit_ = 'LIMIT 99999' sql = select_ + where_ + join_ + order_ + limit_ gl.CURRENT_SQL = sql
def filter_click(self): foo = self.make_sql() gl.FILTER_DATASET = dbmain.query_many(foo) gl.records_in_ds = len(gl.FILTER_DATASET) self.update_grid()
def load_preps(): a = dbmain.query_many('select prep_word from prep') gl.prep_dict = {} for n in a: gl.prep_dict[n[0].lower()]=n[0]
def get_areas(): a = dbmain.query_many('''select distinct pu_cota from livros WHERE pu_cota IS NOT NULL and pu_cota <>'' order by pu_cota''') gl.ds_areas = [] for n in a: gl.ds_areas.append(n[0])
def get_locals(): a = dbmain.query_many('select lc_id,lc_name from locals') gl.locals_list = [] gl.locals_dict = {} for n in a: gl.locals_list.append(n[1])