def update_tags_list(tag_list):
for n in tag_list:
toto = n.lower().strip()
if toto != '':
a = dbmain.OutputQueryOne('select ta_id from tags where ta_name = %s', (toto, ))
if a.output == None:
dbmain.execute_query('insert into tags (ta_name) values(%s)', (toto, ))
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 attach_file(encoded_file, pub_id, info):
dbmain.execute_query('delete from edoc_data where edoc_pub_id= %s',(pub_id,))
conn = psycopg2.connect(pa.conn_string)
curs = conn.cursor()
curs.execute("INSERT into edoc_data (edoc_pub_id, edoc_data, edoc_size, edoc_file_name,edoc_file_format) values (%s,%s,%s,%s,%s)",
(pub_id, psycopg2.Binary(encoded_file),info['file_size'],info['file_name'],info['ext'].upper()))
conn.commit()
curs.close()
conn.close()
def edit_click(self):
self.toto = ''
au_id = int(
self.authorGrid.item(self.authorGrid.currentRow(), 0).text())
text, flag = QInputDialog.getText(
None, "Edita nome do Autor:", "", QLineEdit.Normal,
self.authorGrid.item(self.authorGrid.currentRow(), 1).text())
if flag:
dbmain.execute_query(
'update authors set au_name=%s where au_id=%s ', (text, au_id))
self.grid_search_changed(self.searchEdit.text())
data_access.get_autores()
else:
print('faz nada')
def update_record(self):
sql = '''UPDATE livros SET
pu_author_id=(select au_id from authors where lower(au_name)=%s),
pu_cota =%s,
pu_type=(select ty_id from types where lower(ty_name)=%s),
pu_isbn =%s,
pu_obs=%s,
pu_sinopse=%s,
pu_status=(select st_id from status where lower(st_nome)=%s),
pu_title=%s,
pu_volume=%s,
pu_ed_year=%s,
pu_sub_title=%s
WHERE pu_id = %s'''
data = (self.pu_author_id.currentText().lower(), )
data += (self.pu_cota.text().upper().strip(), )
data += (self.pu_type.currentText().lower(), )
data += (self.pu_isbn.text().strip().replace('-', ''), )
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().upper(), )
data += (write_record(self.pu_ed_year), self.pu_sub_title.text())
data += (self.pub_id, )
a = dbmain.execute_query(sql, data)
''' actualiza obs'''
# if str(self.tags_stack) != str(self.tags.toPlainText()):
self.update_tags()
gl.TYPE = self.pu_type.currentText()
gl.STATUS = self.pu_status.currentText()
def check_status(self):
# editora
dum = self.pu_status.currentText().strip()
if dum == '':
self.error_list.append('Não foi definido o Estado.')
else:
if dum.lower() not in gl.status_dict:
recordFlag = False
if self.askForNew("Foi encontrad um novo Estado.",
"Adiciono este Estado/Lista?", dum):
self.pu_status.setEditText(dum)
dbmain.execute_query(
'INSERT INTO status (st_nome) VALUES(%s); ', (dum, ))
data_access.get_status()
else:
self.error_list.append('Não foi definido o Estado.')
def check_genere(self):
# types
dum = self.pu_type.currentText().strip()
if dum == '':
self.error_list.append('Não foi definido o Tipo.')
else:
if dum.lower() not in gl.types_dict:
recordFlag = False
if self.askForNew("Foi encontrada um novo types",
"Adicionar este types?", dum):
self.pu_type.setEditText(dum)
dbmain.execute_query(
'INSERT INTO types (ty_name) VALUES(%s); ', (dum, ))
data_access.get_types()
else:
self.error_list.append('Não foi definido o Tipo.')
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 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 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 update_tags(self):
''' get tags'''
xl = self.tags.toPlainText() #.rstrip(',')
xl = xl.replace(',,', ',')
xl = xl.rstrip(',')
xl = xl.lstrip(',')
tags_list = xl.split(',')
tags_list = stdio.remove_duplicates(tags_list)
if tags_list[0] == '':
dbmain.execute_query(
'delete from tags_reference where tags_ref_book = %s and tags_ref_level=0',
(self.pub_id, ))
else:
dbmain.execute_query(
'delete from tags_reference where tags_ref_book = %s and tags_ref_level=0',
(self.pub_id, ))
data_access.update_tags(self.pub_id, tags_list)
# if self.tags_special_level1_data[0]:
if self.up_date_special_tags:
lib_tags.update_special_tags(self.pub_id, 1)
def check_autor(self):
# autor
dum = self.pu_author_id.currentText().strip()
if len(dum) > 150:
self.error_list.append('Nome do Autor com mais de 150 caracteres!')
else:
if dum == '':
self.error_list.append('Não foi definido o Autor.')
else:
dum = stdio.authors_process(dum)
if dum.lower() not in gl.autores_dict:
if self.askForNew("Foi encontrada um novo Autor",
"Adicionar este Autor?", dum):
dbmain.execute_query(
'INSERT INTO authors (au_name) VALUES(%s); ',
(dum, ))
self.pu_author_id.setEditText(dum)
data_access.get_autores()
else:
self.error_list.append('Não foi definido o Autor.')
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 addRecord2Table(table, field, value, type_field):
value = str(value)
dbmain.execute_query('insert into ' + table + ' ( ' + field + ',' + type_field +') values(%s); ', (value,))
def save_last_tags_params():
"""input gl.LAST_TAGS"""
last_tags_string = ','.join(gl.last_tags)
dbmain.execute_query('update params set param_data=%s where param_key=%s', (last_tags_string,'LAST_TAGS'))
def save_param(k_name, k_data):
dbmain.execute_query('update params set param_data=%s where param_key=%s', (k_data,k_name))