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