def insert_record(self):
sql = '''insert into livros (
pu_author_id, pu_cota , pu_type, pu_isbn , pu_obs,
pu_sinopse, pu_status, pu_title, pu_volume,pu_ed_year, pu_sub_title)
VALUES ((select au_id from authors where lower(au_name)=%s),%s,(select ty_id from types where lower(ty_name)=%s)
,%s,%s,%s,(select st_id from status where lower(st_nome)=%s),%s,%s,%s,%s)'''
data = (
stdio.authors_process(self.pu_author_id.currentText()).lower(),
self.pu_cota.text().upper(),
)
data += (
self.pu_type.currentText().lower(),
self.pu_isbn.text().strip(),
)
data += (self.pu_obs.toPlainText(), )
data += (self.pu_sinopse.toPlainText(), )
data += (self.pu_status.currentText().lower(), )
data += (self.pu_title.text().strip(), )
data += (self.pu_volume.text(), write_record(self.pu_ed_year),
self.pu_sub_title.text())
dbmain.execute_query(sql, data)
''' campos especiais '''
self.pub_id = dbmain.query_one('select max(pu_id) from livros',
(True, ))[0]
gl.last_id = self.pub_id
''' actualiza obs'''
self.update_tags()
gl.TYPE = self.pu_type.currentText()
gl.STATUS = self.pu_status.currentText()
def search_data_in_table(table, field, data_to_search):
sql = 'SELECT * from ' + table + ' where ' + 'lower(' + field +') = \'' + data_to_search.lower() + '\''
print(sql)
if dbmain.query_one(sql, (True,)):
print('existe')
else:
print('nao existe')
def update_tags(pub_id,tag_list):
# id = livro
tags_id = []
tag_max = dbmain.query_one('''Select max(ta_id)+1 as t from tags''', (True,))[0]
if tag_max == None:
tag_max = 1
for n in tag_list:
toto = n.lower().strip()
if toto != '':
a = dbmain.query_one('select ta_id from tags where ta_name = %s', (toto,))
if a == None: # é nova
dbmain.execute_query('insert into tags (ta_name) values(%s)', (toto, ))
tags_id.append((pub_id,tag_max))
tag_max +=1
else:
tags_id.append((pub_id,a[0]))
sql = ''' INSERT INTO tags_reference(tags_ref_book, tags_ref_tag_id) VALUES''' + str(tags_id)[1:-1]
dbmain.execute_query(sql, (True, ))
def update_special_tags(pub_id,level=1):
dbmain.execute_query('DELETE FROM tags_reference '
'WHERE tags_ref_book = %s and tags_ref_key in (select tags_special_key FROM tags_special WHERE tags_special_level=%s) '
, (pub_id,level))
tags_id = []
tag_max = dbmain.query_one('''SELECT MAX(ta_id)+1 AS t FROM tags''', (True,))[0]
if tag_max is None:
tag_max = 1
for n in gl.tags_special_level1_data:
a = dbmain.query_one('SELECT ta_id FROM tags where lower(ta_name) = %s and tag_key = %s', (n[1].lower(),n[0]))
if a == None: # é nova
dbmain.execute_query('INSERT INTO tags (ta_name, tag_key) values(%s,%s)', (n[1],n[0]))
tags_id.append((pub_id,tag_max, n[0],level))
tag_max +=1
else:
tags_id.append((pub_id,a[0], n[0],level))
sql = ''' INSERT INTO tags_reference(tags_ref_book, tags_ref_tag_id, tags_ref_key, tags_ref_level) VALUES''' + str(tags_id)[1:-1]
if not tags_id == []:
dbmain.execute_query(sql, (True, ))
def attachment_save(pu_id, file_path):
if file_path is not None:
sql = '''select edoc_id, edoc_data, edoc_pub_id, edoc_size, edoc_file_name, edoc_file_format from edoc_data where edoc_pub_id = %s'''
try:
hl = dbmain.query_one(sql, (pu_id,))
a = base64.b64decode(hl[1])
print(hl[3]/1024,hl[4],hl[5])
print('-----------------------')
f = open(file_path + hl[4] + '.' + hl[5], 'wb')
f.write(a)
except Exception as e:
print(str(e))
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 check_ISBN(self):
""" impede/informa quando há um ISBN duplicado """
isbn = str(self.pu_isbn.text())
if isbn != '':
a = dbmain.query_one(
'select pu_isbn from livros where pu_isbn =%s', (isbn, ))
if a != None:
if self.askForNew(
"ISBN duplicado.",
"Já existe outra publicação com este ISBN\nAdiciono mais esta? ",
isbn):
pass
else:
self.error_list.append('O ISBN está duplicado')
def edit_tags_click(self):
dum = self.tags.toPlainText().rstrip(',')
tags_txt = dum.split(',')
tags_data = []
for n in tags_txt:
toto = n.lower().strip()
if toto != '':
a = dbmain.query_one(
'select ta_id from tags where ta_name = %s', (toto, ))
tags_data.append((a, toto))
form = tag_browser.EditRecordTags(tags_data)
form.exec_()
if form.flag:
if form.tag_list == '':
pass
else:
self.tags.setHtml('<font color="blue"><strong>' +
form.tag_list)
def change_locals_click(self):
# check souce local exists
sql = '''select count(pu_cota) from livros where pu_cota = %s'''
a = dbmain.query_one(sql, (self.fromEdt.text().upper(), ))
flag = True
if not a[0] > 0:
b = QMessageBox.question(
self, self.tr("Cota inistente"),
self.tr("""A Cota de origem não existe!
Continuar?"""),
QMessageBox.StandardButtons(QMessageBox.No | QMessageBox.Yes),
QMessageBox.No)
if b == QMessageBox.No:
flag == False
if flag:
sql = '''UPDATE livros set pu_cota=%s where pu_cota=%s'''
dbmain.execute_query(
sql, (self.toEdt.text().upper(), self.fromEdt.text().upper()))
self.grid_refresh()
def load_parameters():
a = dbmain.query_one('select param_json from params where param_key=%s', ('FORM',))[0]
gl.forms = json.loads(a)