def createReference(self):
sections = self.reference.allSections()
progressDlg = Gui.ProgressDialog(self.tr("Updating reference"),
self.tr("Cancel"), len(sections), self.parent())
for columnName in sections:
progressDlg.step()
if progressDlg.wasCanceled():
break
refSection = self.reference.section(columnName)
if isinstance(refSection, CrossReferenceSection):
rel = refSection.model.relationModel(1)
for i in range(rel.rowCount()):
data = rel.data(rel.index(i, rel.fieldIndex('value')))
parentId = rel.data(rel.index(i, rel.fieldIndex('id')))
query = QSqlQuery(self.db)
sql = "SELECT DISTINCT %s FROM coins WHERE %s<>'' AND %s IS NOT NULL AND %s=?" % (columnName, columnName, columnName, refSection.parentName)
query.prepare(sql)
query.addBindValue(data)
query.exec_()
refSection.fillFromQuery(parentId, query)
else:
sql = "SELECT DISTINCT %s FROM coins WHERE %s<>'' AND %s IS NOT NULL" % (columnName, columnName, columnName)
query = QSqlQuery(sql, self.db)
refSection.fillFromQuery(query)
progressDlg.reset()
def getImage(self, img_id):
query = QSqlQuery(self.database())
query.prepare("SELECT image FROM images WHERE id=?")
query.addBindValue(img_id)
query.exec_()
if query.first():
return query.record().value(0)
def insertRecord(self, row, record):
self._updateRecord(record)
record.setNull('id') # remove ID value from record
record.setValue('createdat', record.value('updatedat'))
record.remove(record.indexOf('image_id'))
image = record.value('image')
if image:
query = QSqlQuery(self.database())
query.prepare("INSERT INTO images (image) VALUES (?)")
query.addBindValue(image)
query.exec_()
img_id = query.lastInsertId()
record.setValue('image', img_id)
else:
record.setNull('image')
for i in range(4):
field = "photo%d" % (i + 1)
photo = record.value(field)
if not photo.isNull():
if photo.changed:
photo.save()
photo.image = QtGui.QImage() # free image
record.setValue(field, photo.id_)
else:
record.setNull(field)
return super(CollectionModel, self).insertRecord(row, record)
def _load(self):
self.clear()
count = 0
query = QSqlQuery(self.db)
query.prepare("SELECT COUNT(DISTINCT position) "
"FROM treeparam WHERE pageid=?")
query.addBindValue(self.pageId)
query.exec_()
if query.first():
count = query.record().value(0)
if count:
for _ in range(count):
self._params.append([])
query = QSqlQuery(self.db)
query.prepare("SELECT * FROM treeparam WHERE pageid=?")
query.addBindValue(self.pageId)
query.exec_()
while query.next():
record = query.record()
position = record.value('position')
fieldId = record.value('fieldid')
self._params[position].append(self.fields.field(fieldId))
def updateEquipment(self, equipmentLayout):
if not self.db.open():
return -1
self.devicesInfo = []
for serie in equipmentLayout.getSelectedSeries():
query = QSqlQuery()
query.prepare("SELECT d.description, " +
"d.brand, " +
"d.model, " +
"i.serie, " +
"i.id " +
"FROM Devices AS d " +
"INNER JOIN Items AS i " +
"ON d.id = i.device_id " +
"WHERE i.serie = ?")
query.addBindValue(serie)
query.exec_()
while query.next():
device = {}
self.devicesInfo.append(device)
device["name"] = str(query.value(0))
device["brand"] = str(query.value(1))
device["model"] = str(query.value(2))
device["serie"] = str(query.value(3))
device["id"] = str(query.value(4))
self.db.close()
def setValue(self, Id, Newvalue, field):
query = QSqlQuery()
queryText = 'update vgenesdb set ' + field + ' = ? where ID = ?'
query.prepare(queryText)
query.addBindValue(Newvalue)
query.addBindValue(Id)
return query.exec_()
def removeRow(self, row):
record = super().record(row)
ids = []
for field in ['obverseimg', 'reverseimg', 'edgeimg',
'photo1', 'photo2', 'photo3', 'photo4']:
value = record.value(field)
if value:
ids.append(value)
if ids:
ids_sql = '(' + ','.join('?' * len(ids)) + ')'
query = QSqlQuery(self.database())
query.prepare("DELETE FROM photos WHERE id IN " + ids_sql)
for id_ in ids:
query.addBindValue(id_)
query.exec_()
value = record.value('image')
if value:
query = QSqlQuery(self.database())
query.prepare("DELETE FROM images WHERE id=?")
query.addBindValue(value)
query.exec_()
return super().removeRow(row)
def savePositions(self, pages):
for position, page in enumerate(pages):
query = QSqlQuery(self.db)
query.prepare("UPDATE pages SET position=? WHERE id=?")
query.addBindValue(position)
query.addBindValue(page.id)
query.exec_()
def removePage(self, page):
page.listParam.remove()
page.treeParam.remove()
query = QSqlQuery(self.db)
query.prepare("DELETE FROM pages WHERE id=?")
query.addBindValue(page.id)
query.exec_()
def setState(self, item_id, state):
if not self.db.open():
return -1
query = QSqlQuery()
query.prepare("UPDATE Items SET state = ? WHERE id = ?")
query.addBindValue(state)
query.addBindValue(item_id)
self.db.close
return query.exec_()
def remove(self):
query = QSqlQuery(self.db)
query.prepare("DELETE FROM lists WHERE pageid=?")
query.addBindValue(self.page.id)
query.exec_()
query = QSqlQuery(self.db)
query.prepare("DELETE FROM filters WHERE pageid=?")
query.addBindValue(self.page.id)
query.exec_()
def fillFromQuery(self, query):
while query.next():
value = query.record().value(0)
fillQuery = QSqlQuery(self.db)
fillQuery.prepare("INSERT INTO %s (value) "
"SELECT ? "
"WHERE NOT EXISTS (SELECT 1 FROM %s WHERE value=?)" %
(self.name, self.name))
fillQuery.addBindValue(value)
fillQuery.addBindValue(value)
fillQuery.exec_()
def saveSort(self, sort):
if self.sort != sort:
self.sort = sort
query = QSqlQuery(self.db)
query.prepare("UPDATE sections SET sort=? WHERE name=?")
query.addBindValue(int(sort))
query.addBindValue(self.name)
query.exec_()
self.setSort()
def save(self):
self.db.transaction()
for key, value in self.items():
# TODO: Insert value if currently not present
query = QSqlQuery(self.db)
query.prepare("UPDATE settings SET value=? WHERE title=?")
query.addBindValue(str(value))
query.addBindValue(key)
query.exec_()
self.db.commit()
def update(self):
self._begin()
self.db.transaction()
sql = "ALTER TABLE lists ADD COLUMN sortorder INTEGER"
QSqlQuery(sql, self.db)
sql = "ALTER TABLE filters ADD COLUMN revert INTEGER"
QSqlQuery(sql, self.db)
query = QSqlQuery(self.db)
query.prepare("""INSERT INTO settings (title, value) VALUES (?, ?)""")
query.addBindValue('Type')
query.addBindValue(self.collection.settings['Type'])
query.exec_()
sql = """ALTER TABLE images RENAME TO photos"""
QSqlQuery(sql, self.db)
sql = """CREATE TABLE images (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
image BLOB)"""
QSqlQuery(sql, self.db)
query = QSqlQuery("SELECT id, image FROM coins", self.db)
while query.next():
self._updateRecord()
record = query.record()
if not record.isNull('image'):
insert_query = QSqlQuery(self.db)
insert_query.prepare("INSERT INTO images (image) VALUES (?)")
insert_query.addBindValue(record.value('image'))
insert_query.exec_()
img_id = insert_query.lastInsertId()
else:
img_id = None
update_query = QSqlQuery(self.db)
update_query.prepare("UPDATE coins SET image=? WHERE id=?")
update_query.addBindValue(img_id)
update_query.addBindValue(record.value('id'))
update_query.exec_()
self.collection.settings['Version'] = 3
self.collection.settings.save()
self.db.commit()
self._finish()
def save(self):
if self.cleared or self.isNull():
self.remove()
else:
if not self.file:
self.file = str(uuid.uuid1()) + '.jpg'
if self.id_:
query = QSqlQuery(self.db)
query.prepare("UPDATE photos SET file=?, url=? WHERE id=?")
query.addBindValue(self.file)
query.addBindValue(self.url)
query.addBindValue(self.id_)
query.exec_()
else:
query = QSqlQuery(self.db)
query.prepare("INSERT INTO photos (file, url) VALUES (?, ?)")
query.addBindValue(self.file)
query.addBindValue(self.url)
query.exec_()
self.id_ = query.lastInsertId()
if not self.image.isNull():
file_name = self._generateFileName(self.file, True)
self.image.save(file_name)
def getSort(self):
query = QSqlQuery(self.db)
query.prepare("SELECT sort FROM sections WHERE name=?")
query.addBindValue(self.name)
query.exec_()
if query.first():
data = query.record().value(0)
if data:
self.sort = bool(data)
else:
self.sort = False
query.clear()
return self.sort
def _load(self):
query = QSqlQuery(self.db)
query.prepare("SELECT * FROM statistics WHERE pageid=?")
query.addBindValue(self.pageId)
query.exec_()
if query.first():
record = query.record()
self.__setitem__('showed', bool(record.value('showed')))
self.__setitem__('chart', record.value('chart'))
self.__setitem__('fieldid', record.value('fieldid'))
self.__setitem__('subfieldid', record.value('subfieldid'))
self.__setitem__('items', record.value('items'))
self.__setitem__('period', record.value('period'))
self.__setitem__('color', bool(record.value('color')))
self.setAutoSave(True)
def remove(self):
if self.file:
file_name = self._generateFileName(self.file)
try:
os.remove(file_name)
except FileNotFoundError:
pass
self.file = None
if self.id_:
query = QSqlQuery(self.db)
query.prepare("DELETE FROM photos WHERE id=?")
query.addBindValue(self.id_)
query.exec_()
self.id_ = None
def save(self):
self.db.transaction()
self.remove()
for position, param in enumerate(self.params()):
for field in param:
query = QSqlQuery(self.db)
query.prepare("INSERT INTO treeparam (pageid, fieldid,"
" position) VALUES (?, ?, ?)")
query.addBindValue(self.pageId)
query.addBindValue(field.id)
query.addBindValue(position)
query.exec_()
self.db.commit()
def create(self, db=QSqlDatabase()):
db.transaction()
sql = "CREATE TABLE %s (\
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\
value TEXT, icon BLOB)" % self.name
QSqlQuery(sql, db)
query = QSqlQuery(db)
query.prepare("INSERT INTO sections (name, letter)\
VALUES (?, ?)")
query.addBindValue(self.name)
query.addBindValue(self.letter)
query.exec_()
db.commit()
def removeRow(self, row):
record = self.record(row)
img_id = record.value('image')
if img_id:
query = QSqlQuery(self.database())
query.prepare("DELETE FROM images WHERE id=?")
query.addBindValue(img_id)
query.exec_()
for i in range(4):
field = "photo%d" % (i + 1)
photo = record.value(field)
if photo:
photo.remove()
return super(CollectionModel, self).removeRow(row)
def create(db=QSqlDatabase()):
db.transaction()
sql = """CREATE TABLE settings (
title CHAR NOT NULL UNIQUE,
value CHAR)"""
QSqlQuery(sql, db)
for key, value in CollectionSettings.Default.items():
query = QSqlQuery(db)
query.prepare("""INSERT INTO settings (title, value)
VALUES (?, ?)""")
query.addBindValue(key)
query.addBindValue(str(value))
query.exec_()
db.commit()
def getImage(self, img_id):
"""Read the image data/fileName from the DB."""
query = QSqlQuery(self.database())
query.prepare("SELECT image FROM photos WHERE id=?")
query.addBindValue(img_id)
query.exec_()
if query.first():
imageOrFile = query.record().value(0)
if imageOrFile[:7] == 'file://':
try:
imageFile = open(imageOrFile[7:], "rb")
except IOError:
imageFile = open(self.badFileNameImage, "rb")
image = QtCore.QByteArray(imageFile.read())
imageFile.close()
return image, imageOrFile
else:
return imageOrFile, None
def create(self, collection):
self.db.transaction()
sql = """CREATE TABLE description (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
title TEXT,
description TEXT,
author TEXT)"""
QSqlQuery(sql, self.db)
query = QSqlQuery(self.db)
query.prepare("""INSERT INTO description (title, description, author)
VALUES (?, ?, ?)""")
query.addBindValue(collection.getCollectionName())
query.addBindValue('')
query.addBindValue('')
query.exec_()
self.db.commit()
def __init__(self, id_, model):
QtCore.QObject.__init__(self, model)
self.model = model
self.db = model.database()
self.id_ = id_
self.file = None
self.url = None
self.workingDir = model.workingDir
self.collectionName = model.collectionName
self.changed = False
self.cleared = False
self.image = QtGui.QImage()
query = QSqlQuery(self.db)
query.prepare("SELECT * FROM photos WHERE id=?")
query.addBindValue(self.id_)
query.exec_()
if query.first():
self.file = query.record().value('file')
self.url = query.record().value('url')
def update(self):
self._begin()
self.db.transaction()
fields = ['ruler', 'region']
for field in fields:
self._updateRecord()
fieldDesc = getattr(self.collection.fields, field)
fieldDesc.enabled = False
query = QSqlQuery(self.db)
query.prepare("INSERT INTO fields (id, title, enabled)"
" VALUES (?, ?, ?)")
query.addBindValue(fieldDesc.id)
query.addBindValue(fieldDesc.title)
query.addBindValue(int(fieldDesc.enabled))
query.exec_()
sql = "ALTER TABLE coins ADD COLUMN %s TEXT" % field
QSqlQuery(sql, self.db)
self.collection.fields.userFields.append(fieldDesc)
self.progressDlg.setLabelText(self.tr("Saving..."))
sql = "UPDATE photos SET title=NULL"
QSqlQuery(sql, self.db)
self.collection.settings['Version'] = 4
self.collection.settings.save()
self.db.commit()
self._finish()
def update(self):
self._begin()
self.db.transaction()
fields = ('category', 'sort_id')
for field in fields:
self._updateRecord()
fieldDesc = getattr(self.collection.fields, field)
fieldDesc.enabled = False
query = QSqlQuery(self.db)
query.prepare("INSERT INTO fields (id, title, enabled)"
" VALUES (?, ?, ?)")
query.addBindValue(fieldDesc.id)
query.addBindValue(fieldDesc.title)
query.addBindValue(int(False))
query.exec_()
sql = "ALTER TABLE coins ADD COLUMN %s %s" % (field, Type.toSql(fieldDesc.type))
QSqlQuery(sql, self.db)
self.collection.fields.userFields.append(fieldDesc)
self._updateRecord()
sql = "UPDATE coins SET sort_id = id"
QSqlQuery(sql, self.db)
self.collection.settings['Version'] = 6
self.collection.settings.save()
self.db.commit()
self._finish()
def update(self):
self._begin()
self.db.transaction()
fields = ('emitent', 'signaturetype', 'signature', 'signatureimg',
'address', 'latitude', 'longitude')
for field in fields:
self._updateRecord()
fieldDesc = getattr(self.collection.fields, field)
fieldDesc.enabled = False
query = QSqlQuery(self.db)
query.prepare("INSERT INTO fields (id, title, enabled)"
" VALUES (?, ?, ?)")
query.addBindValue(fieldDesc.id)
query.addBindValue(fieldDesc.title)
query.addBindValue(int(False))
query.exec_()
sql = "ALTER TABLE coins ADD COLUMN %s %s" % (field, Type.toSql(fieldDesc.type))
QSqlQuery(sql, self.db)
self.collection.fields.userFields.append(fieldDesc)
self._updateRecord()
self.collection.settings['Version'] = 7
self.collection.settings.save()
self.db.commit()
self._finish()
def fillFromQuery(self, parentId, query):
while query.next():
value = query.record().value(0)
fillQuery = QSqlQuery(self.db)
fillQuery.prepare("INSERT INTO %s (value, parentid) "
"SELECT ?, ? "
"WHERE NOT EXISTS "
"(SELECT 1 FROM %s WHERE value=? AND parentid=?)" %
(self.table_name, self.table_name))
fillQuery.addBindValue(value)
fillQuery.addBindValue(parentId)
fillQuery.addBindValue(value)
fillQuery.addBindValue(parentId)
fillQuery.exec_()
def create(self, db=QSqlDatabase()):
db.transaction()
sql = "CREATE TABLE %s (\
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\
parentid INTEGER NOT NULL,\
value TEXT, icon BLOB)" % self.name
QSqlQuery(sql, db)
query = QSqlQuery(db)
query.prepare("INSERT INTO sections (name, letter, parent)\
VALUES (?, ?, ?)")
query.addBindValue(self.name)
query.addBindValue(self.letter)
query.addBindValue(self.parentName)
query.exec_()
db.commit()
def main():
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("foo.sqlite")
if not db.open():
sys.exit(-1)
lib = CDLL(os.path.join(CURRENT_DIR, "libqsqlite.so"))
lib.enable_extension(c_void_p(sip.unwrapinstance(db.driver()).__int__()),
True)
load_spatialite()
query = QSqlQuery()
query.exec_("CREATE TABLE my_line(id INTEGER PRIMARY KEY)")
query.exec_(
"""SELECT AddGeometryColumn("my_line","geom" , 4326, "LINESTRING", 2)"""
)
polygon_wkt = "POLYGON ((11 50,11 51,12 51,12 50,11 50))"
XA = 11
YA = 52
XB = 12
YB = 49
line_wkt = "LINESTRING({0} {1}, {2} {3})".format(XA, YA, XB, YB)
query.prepare("""INSERT INTO my_line VALUES (?,GeomFromText(?, 4326))""")
query.addBindValue(1)
query.addBindValue(line_wkt)
query.exec_()
query.prepare(
"""SELECT astext(st_intersection(geom, GeomFromText(?, 4326))) from my_line WHERE st_intersects(geom, GeomFromText(?, 4326))"""
)
query.addBindValue(polygon_wkt)
query.addBindValue(polygon_wkt)
query.exec_()
while query.next():
for i in range(query.record().count()):
print(query.value(i))
def insertRecord(self, row, record):
self._updateRecord(record)
record.setNull('id') # remove ID value from record
record.setValue('createdat', record.value('updatedat'))
for field in [
'obverseimg', 'reverseimg', 'edgeimg', 'photo1', 'photo2',
'photo3', 'photo4'
]:
value = record.value(field)
if value:
query = QSqlQuery(self.database())
query.prepare(
"INSERT INTO photos (title, image) VALUES (?, ?)")
query.addBindValue(record.value(field + '_title'))
query.addBindValue(value)
query.exec_()
img_id = query.lastInsertId()
else:
img_id = None
record.setValue(field, img_id)
record.remove(record.indexOf(field + '_id'))
record.remove(record.indexOf(field + '_title'))
value = record.value('image')
if value:
query = QSqlQuery(self.database())
query.prepare("INSERT INTO images (image) VALUES (?)")
query.addBindValue(value)
query.exec_()
img_id = query.lastInsertId()
else:
img_id = None
record.setValue('image', img_id)
record.remove(record.indexOf('image_id'))
return super(CollectionModel, self).insertRecord(row, record)
def db_pip_update_insert(self):
from PyQt5 import QtSql
from PyQt5.QtSql import QSqlQuery
database = QtSql.QSqlDatabase.addDatabase('QSQLITE')
database.setDatabaseName('patata.db')
if database.open():
print("OK")
else:
print("连接数据库失败！")
insert_sql = 'insert into pip_install_info values (?,?,?)'
query = QSqlQuery()
query.prepare(insert_sql)
query.addBindValue(4)
query.addBindValue('test3')
query.addBindValue(1)
if not query.exec_():
print(query.lastError())
else:
print('inserted')
def create(db=QSqlDatabase()):
db.transaction()
sql = """CREATE TABLE fields (
id INTEGER NOT NULL PRIMARY KEY,
title TEXT,
enabled INTEGER)"""
QSqlQuery(sql, db)
fields = CollectionFieldsBase()
for field in fields:
query = QSqlQuery(db)
query.prepare("""INSERT INTO fields (id, title, enabled)
VALUES (?, ?, ?)""")
query.addBindValue(field.id)
query.addBindValue(field.title)
enabled = field in fields.userFields
query.addBindValue(int(enabled))
query.exec_()
db.commit()
def update(self):
self._begin()
self.db.transaction()
fields = ('category', 'sort_id')
for field in fields:
self._updateRecord()
fieldDesc = getattr(self.collection.fields, field)
fieldDesc.enabled = False
query = QSqlQuery(self.db)
query.prepare("INSERT INTO fields (id, title, enabled)"
" VALUES (?, ?, ?)")
query.addBindValue(fieldDesc.id)
query.addBindValue(fieldDesc.title)
query.addBindValue(int(False))
query.exec_()
sql = "ALTER TABLE coins ADD COLUMN %s %s" % (
field, Type.toSql(fieldDesc.type))
QSqlQuery(sql, self.db)
self.collection.fields.userFields.append(fieldDesc)
self._updateRecord()
sql = "UPDATE coins SET sort_id = id"
QSqlQuery(sql, self.db)
self.collection.settings['Version'] = 6
self.collection.settings.save()
self.db.commit()
self._finish()
def update(self):
self._begin()
self.db.transaction()
fields = ['quantity', 'url', 'barcode']
for field in fields:
fieldDesc = getattr(self.collection.fields, field)
fieldDesc.enabled = True
query = QSqlQuery(self.db)
query.prepare("""INSERT INTO fields (id, title, enabled)
VALUES (?, ?, ?)""")
query.addBindValue(fieldDesc.id)
query.addBindValue(fieldDesc.title)
query.addBindValue(int(fieldDesc.enabled))
query.exec_()
self.collection.fields.userFields.append(fieldDesc)
sql = """ALTER TABLE coins RENAME TO tmp_coins"""
QSqlQuery(sql, self.db)
self.collection.createCoinsTable()
query = QSqlQuery("SELECT * FROM tmp_coins", self.db)
while query.next():
self._updateRecord()
record = query.record()
imgIds = {}
fields = [
'obverseimg', 'reverseimg', 'edgeimg', 'photo1', 'photo2',
'photo3', 'photo4'
]
for field in fields:
if not record.isNull(field):
image_query = QSqlQuery(self.db)
image_query.prepare("""INSERT INTO images (title, image)
VALUES (?, ?)""")
fieldDesc = getattr(self.collection.fields, field)
image_query.addBindValue(fieldDesc.title)
image_query.addBindValue(record.value(field))
image_query.exec_()
imgIds[field] = image_query.lastInsertId()
else:
imgIds[field] = None
coin_query = QSqlQuery(self.db)
coin_query.prepare("""INSERT INTO coins (title, value, unit,
country, year, period, mint, mintmark, issuedate, type,
series, subjectshort, status, material, fineness,
shape, diameter, thickness, weight, grade, edge,
edgelabel, obvrev, quality, mintage, dateemis,
catalognum1, catalognum2, catalognum3, catalognum4,
rarity, price1, price2, price3, price4, variety,
obversevar, reversevar, edgevar, paydate, payprice,
totalpayprice, saller, payplace, payinfo, saledate,
saleprice, totalsaleprice, buyer, saleplace, saleinfo,
note, image, obverseimg, obversedesign,
obversedesigner, reverseimg, reversedesign,
reversedesigner, edgeimg, subject, photo1, photo2,
photo3, photo4, defect, storage, features, createdat,
updatedat)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""")
coin_query.addBindValue(record.value('title'))
coin_query.addBindValue(record.value('value'))
coin_query.addBindValue(record.value('unit'))
coin_query.addBindValue(record.value('country'))
coin_query.addBindValue(record.value('year'))
coin_query.addBindValue(record.value('period'))
coin_query.addBindValue(record.value('mint'))
coin_query.addBindValue(record.value('mintmark'))
coin_query.addBindValue(record.value('issuedate'))
coin_query.addBindValue(record.value('type'))
coin_query.addBindValue(record.value('series'))
coin_query.addBindValue(record.value('subjectshort'))
coin_query.addBindValue(record.value('status'))
coin_query.addBindValue(record.value('metal'))
coin_query.addBindValue(record.value('fineness'))
coin_query.addBindValue(record.value('form'))
coin_query.addBindValue(record.value('diameter'))
coin_query.addBindValue(record.value('thick'))
coin_query.addBindValue(record.value('mass'))
coin_query.addBindValue(record.value('grade'))
coin_query.addBindValue(record.value('edge'))
coin_query.addBindValue(record.value('edgelabel'))
coin_query.addBindValue(record.value('obvrev'))
coin_query.addBindValue(record.value('quality'))
coin_query.addBindValue(record.value('mintage'))
coin_query.addBindValue(record.value('dateemis'))
coin_query.addBindValue(record.value('catalognum1'))
coin_query.addBindValue(record.value('catalognum2'))
coin_query.addBindValue(record.value('catalognum3'))
coin_query.addBindValue(record.value('catalognum4'))
coin_query.addBindValue(record.value('rarity'))
coin_query.addBindValue(record.value('price1'))
coin_query.addBindValue(record.value('price2'))
coin_query.addBindValue(record.value('price3'))
coin_query.addBindValue(record.value('price4'))
coin_query.addBindValue(record.value('variety'))
coin_query.addBindValue(record.value('obversevar'))
coin_query.addBindValue(record.value('reversevar'))
coin_query.addBindValue(record.value('edgevar'))
coin_query.addBindValue(record.value('paydate'))
coin_query.addBindValue(record.value('payprice'))
coin_query.addBindValue(record.value('totalpayprice'))
coin_query.addBindValue(record.value('saller'))
coin_query.addBindValue(record.value('payplace'))
coin_query.addBindValue(record.value('payinfo'))
coin_query.addBindValue(record.value('saledate'))
coin_query.addBindValue(record.value('saleprice'))
coin_query.addBindValue(record.value('totalsaleprice'))
coin_query.addBindValue(record.value('buyer'))
coin_query.addBindValue(record.value('saleplace'))
coin_query.addBindValue(record.value('saleinfo'))
coin_query.addBindValue(record.value('note'))
coin_query.addBindValue(record.value('image'))
coin_query.addBindValue(imgIds['obverseimg'])
coin_query.addBindValue(record.value('obversedesign'))
coin_query.addBindValue(record.value('obversedesigner'))
coin_query.addBindValue(imgIds['reverseimg'])
coin_query.addBindValue(record.value('reversedesign'))
coin_query.addBindValue(record.value('reversedesigner'))
coin_query.addBindValue(imgIds['edgeimg'])
coin_query.addBindValue(record.value('subject'))
coin_query.addBindValue(imgIds['photo1'])
coin_query.addBindValue(imgIds['photo2'])
coin_query.addBindValue(imgIds['photo3'])
coin_query.addBindValue(imgIds['photo4'])
coin_query.addBindValue(record.value('defect'))
coin_query.addBindValue(record.value('storage'))
coin_query.addBindValue(record.value('features'))
coin_query.addBindValue(record.value('createdat'))
coin_query.addBindValue(record.value('updatedat'))
if not coin_query.exec_():
print(coin_query.lastError().text())
self.progressDlg.setLabelText(self.tr("Saving..."))
sql = """DROP TABLE tmp_coins"""
QSqlQuery(sql, self.db)
self.collection.settings['Version'] = 2
self.collection.settings.save()
query = QSqlQuery(self.db)
query.prepare("""INSERT INTO settings (title, value) VALUES (?, ?)""")
query.addBindValue('Password')
query.addBindValue(self.collection.settings['Password'])
query.exec_()
self.db.commit()
self.progressDlg.setLabelText(self.tr("Vacuum..."))
self.collection.vacuum()
self._finish()
def renamePage(self, page, title):
query = QSqlQuery(self.db)
query.prepare("UPDATE pages SET title=? WHERE id=?")
query.addBindValue(title)
query.addBindValue(page.id)
query.exec_()
def setFirstName(self, personId, firstName):
query = QSqlQuery()
query.prepare('update person set firstname = ? where id = ?')
query.addBindValue(firstName)
query.addBindValue(personId)
return query.exec_()
class MimaBox:
secretbox = None
def __init__(self,
nonce='',
title='',
username='',
website='',
password='',
notes='',
favorite=0,
deleted=''):
self.encrypted = None
self.COLUMNS = MIMA_COLUMNS
self.SELECT_FROM_TEMP = SELECT_FROM_MIMATEMP
self.INSERT_INTO_TEMP = INSERT_INTO_MIMATEMP
self.INSERT_INTO_DATABASE = INSERT_INTO_MIMA
self.UPDATE_DATABASE = UPDATE_MIMA
self.query = QSqlQuery()
# self.query.setForwardOnly(True)
if nonce:
self.nonce = nonce
else:
self.nonce = self.new_nonce()
self.title = title
self.username = username
self.website = website
self.password = password
self.notes = notes
self.favorite = favorite
if deleted:
self.deleted = deleted
else:
self.deleted = EPOCH
def new_nonce(self):
while True:
nonce = nacl.utils.random(nacl.secret.SecretBox.NONCE_SIZE)
nonce = base64.b64encode(nonce).decode()
self.query.prepare(FIND_NONCE_IN_MIMATEMP)
self.query.addBindValue(nonce)
self.query_exec()
result_1 = self.query.first()
self.query.prepare(FIND_NONCE_IN_HISTORYTEMP)
self.query.addBindValue(nonce)
self.query.exec_()
result_2 = self.query.first()
if result_1 is False and result_2 is False:
return nonce
def is_unique(self):
self.query.prepare(CHECK_UNIQUENESS)
for value in [self.title, self.username, self.deleted]:
self.query.addBindValue(value)
self.query_exec()
return not self.query.first()
def is_unique_except_itself(self):
self.query.prepare(CHECK_UNIQUENESS_EXCEPT_ITSELF)
for value in [self.title, self.username, self.deleted, self.nonce]:
self.query.addBindValue(value)
self.query_exec()
return not self.query.first()
def to_dict(self):
return dict(title=self.title,
username=self.username,
website=self.website,
password=self.password,
notes=self.notes,
favorite=self.favorite,
deleted=self.deleted)
# def to_list(self):
# return [self.title,
# self.username,
# self.website,
# self.password,
# self.notes,
# self.favorite,
# self.deleted]
def insert_into_temp(self, sql=None): # also use as update_mimatemp
if not sql:
sql = self.INSERT_INTO_TEMP
self.query.prepare(sql)
self.query.bindValue(':nonce', self.nonce)
if sql == INSERT_INTO_HISTORYTEMP:
self.query.bindValue(':mimanonce', self.mimanonce)
for k, v in self.to_dict().items():
self.query.bindValue(':' + k, v)
self.query_exec()
def update_temp(self):
self.insert_into_temp(UPDATE_MIMATEMP)
def encrypt(self):
message = json.dumps(self.to_dict()).encode()
encrypted = self.secretbox.encrypt(
message, base64.b64decode(self.nonce.encode()))
self.encrypted = QByteArray(encrypted)
def insert_into_database(self):
self.encrypt()
self.query.prepare(self.INSERT_INTO_DATABASE)
self.query.bindValue(':nonce', self.nonce)
self.query.bindValue(':encrypted', self.encrypted)
if self.INSERT_INTO_DATABASE == INSERT_INTO_HISTORY:
self.query.bindValue(':mimanonce', self.mimanonce)
self.query_exec()
def insert_into_database_and_temp(self):
self.insert_into_temp()
self.insert_into_database()
# self.query.exec_('select * from mimatemp')
# print('SIZE: ', self.query.size())
# record = self.query.record()
# self.query.next()
# for i in range(record.count()):
# print(record.fieldName(i), self.query.value(i))
# if self.query.lastError().type():
# raise RuntimeError(self.query.lastError().text())
# errorType = self.query.lastError().type()
# if errorType:
# print(self.query.lastError().text())
# else:
# print('No Error.', errorType)
# print('ACTIVE: ', self.query.isActive())
# print('VALID: ', self.query.isValid())
# print('AFFECTED: ', self.query.numRowsAffected())
# print('COMMIT: ', QSqlDatabase.database().commit())
def restore_by_nonce(self, nonce='', sql=None):
COLUMNS = self.COLUMNS
if sql == SELECT_FROM_MIMATEMP:
COLUMNS = MIMA_COLUMNS
if not sql:
sql = self.SELECT_FROM_TEMP
self.query.prepare(sql)
if not nonce:
nonce = self.nonce
self.query.addBindValue(nonce)
self.query_exec()
self.query.next()
self.title = self.query.value(COLUMNS['title'])
self.username = self.query.value(COLUMNS['username'])
self.website = self.query.value(COLUMNS['website'])
self.password = self.query.value(COLUMNS['password'])
self.notes = self.query.value(COLUMNS['notes'])
self.favorite = self.query.value(COLUMNS['favorite'])
self.deleted = self.query.value(COLUMNS['deleted'])
if self.SELECT_FROM_TEMP == SELECT_FROM_HISTORYTEMP \
and sql == SELECT_FROM_HISTORYTEMP:
self.mimanonce = self.query.value(HISTORY_COLUMNS['mimanonce'])
def update_database(self):
self.encrypt()
self.query.prepare(self.UPDATE_DATABASE)
self.query.bindValue(':encrypted', self.encrypted)
self.query.bindValue(':nonce', self.nonce)
self.query_exec()
def toggle_favorite(self):
self.favorite = not self.favorite
self.query.prepare(UPDATE_FAVORITE)
self.query.bindValue(':favorite', self.favorite)
self.query.bindValue(':nonce', self.nonce)
self.query_exec()
self.update_database()
def move_to_trash(self):
self.favorite = 0
self.deleted = datetime.datetime.now().isoformat(sep=' ')
self.query.prepare(UPDATE_DELETED)
self.query.bindValue(':deleted', self.deleted)
self.query.bindValue(':nonce', self.nonce)
self.query_exec()
self.update_database()
def recover(self):
self.deleted = EPOCH
self.query.prepare(RECOVER)
self.query.bindValue(':title', self.title)
self.query.bindValue(':nonce', self.nonce)
self.query_exec()
self.update_database()
def delete_forever(self, table='mima', temptable='mimatemp'):
self.query.prepare(f"DELETE FROM {temptable} WHERE nonce=?")
self.query.addBindValue(self.nonce)
self.query_exec()
self.query.prepare(f"DELETE FROM {table} WHERE nonce=?")
self.query.addBindValue(self.nonce)
self.query_exec()
def query_exec(self):
success = self.query.exec_()
if not success:
raise RuntimeError(self.query.lastError().text())
def videoTableUpdate(self, filters=[]):
'''
Generate SQL query from filters (filterName, categoryIndex)
'''
tableSorts = [
"publish_date", "save_date", "video_title", "C.channel_title"
]
sortDirs = ["ASC", "DESC"]
self.lastUsedFilters = filters
queries = []
search = self.tableSearchBar.text()
tokens = split(search)
query = QSqlQuery()
if len(filters) == 0 and len(search) == 0:
queryTemplate = """SELECT video_id, video_title, publish_date, save_date, C.channel_title
FROM videos V INNER JOIN channels C
ON V.channel_id = C.channel_id
ORDER BY """ + f"{tableSorts[self.tableSortDropDown.currentIndex()]} {sortDirs[self.tableSortDirDropDown.currentIndex()]}"
query.prepare(queryTemplate)
else:
if len(filters) > 0:
filterTables = [
"videos_playlists_link", "videos", "videos_tags_link",
"videos_yt_tags_link"
]
filterColumns = [
"playlist_id", "channel_id", "tag_name", "tag_name"
]
# queryTemplates = ["SELECT video_id FROM videos_playlists_link WHERE playlist_id = (?)",
# "SELECT video_id FROM videos WHERE channel_id = (?)",
# "SELECT video_id FROM videos_tags_link WHERE tag_name = (?)",
# "SELECT video_id FROM videos_yt_tags_link WHERE tag_name = (?)"]
selections = []
for filterName, categoryIndex in filters:
selections.append(
f"SELECT video_id FROM {filterTables[categoryIndex]} WHERE {filterColumns[categoryIndex]} = (?)"
)
# selections.append(queryTemplates[categoryIndex])
queryTemplate = "\nINTERSECT\n".join(selections)
queryTemplate = """SELECT video_id, video_title, publish_date, save_date, C.channel_title
FROM videos V INNER JOIN channels C
ON V.channel_id = C.channel_id
WHERE video_id IN (\n""" + queryTemplate + "\n)"
queries.append(queryTemplate)
if len(tokens) > 0:
posQuery = """
SELECT video_id FROM videos WHERE channel_id IN (
SELECT channel_id FROM channels WHERE channel_title LIKE (?)
)
UNION
SELECT video_id FROM videos_playlists_link WHERE playlist_id IN (
SELECT playlist_id FROM playlists WHERE playlist_title LIKE (?)
)
UNION
SELECT video_id FROM videos WHERE video_id LIKE (?) OR video_title LIKE (?) OR description LIKE (?) OR notes LIKE (?)
UNION
SELECT video_id FROM videos_tags_link WHERE tag_name LIKE (?)
UNION
SELECT video_id FROM videos_yt_tags_link WHERE tag_name LIKE (?)
"""
posQuery = """
SELECT video_id, video_title, publish_date, save_date, C.channel_title
FROM videos V INNER JOIN channels C
ON V.channel_id =C.channel_id
WHERE video_id IN (\n""" + posQuery + "\n)\n"
negQuery = """
SELECT video_id FROM videos WHERE channel_id IN (
SELECT channel_id FROM channels WHERE channel_title NOT LIKE (?)
)
INTERSECT
SELECT video_id FROM videos_playlists_link WHERE playlist_id IN (
SELECT playlist_id FROM playlists WHERE playlist_title NOT LIKE (?)
)
INTERSECT
SELECT video_id FROM videos WHERE video_id NOT LIKE (?) AND video_title NOT LIKE (?)
INTERSECT
SELECT video_id FROM videos_tags_link WHERE tag_name NOT LIKE (?)
"""
negQuery = """
SELECT video_id, video_title, publish_date, save_date, C.channel_title
FROM videos V INNER JOIN channels C
ON V.channel_id =C.channel_id
WHERE video_id IN (\n""" + negQuery + "\n)\n"
searchQuery = ""
for i, t in enumerate(tokens):
if i != 0:
searchQuery += "\nINTERSECT\n"
subquery = posQuery if t[0] != '-' else negQuery
searchQuery += subquery
queries.append(searchQuery)
# if len(search) > 0:
# searchQuery = """SELECT video_id FROM videos WHERE channel_id IN (
# SELECT channel_id FROM channels WHERE channel_title LIKE (?)
# )
# UNION
# SELECT video_id FROM videos_playlists_link WHERE playlist_id IN (
# SELECT playlist_id FROM playlists WHERE playlist_title LIKE (?)
# )
# UNION
# SELECT video_id FROM videos WHERE video_id LIKE (?) OR video_title LIKE (?) OR description LIKE (?) OR notes LIKE (?)
# UNION
# SELECT video_id FROM videos_tags_link WHERE tag_name LIKE (?)
# UNION
# SELECT video_id FROM videos_yt_tags_link WHERE tag_name LIKE (?)"""
# searchQuery = """SELECT video_id, video_title, publish_date, save_date, C.channel_title
# FROM videos V INNER JOIN channels C
# ON V.channel_id =C.channel_id
# WHERE video_id IN (\n""" + searchQuery + "\n)"
# queries.append(searchQuery)
preQuery = "\nINTERSECT\n".join(
queries
) + f"\nORDER BY {tableSorts[self.tableSortDropDown.currentIndex()]} {sortDirs[self.tableSortDirDropDown.currentIndex()]}"
query.prepare(preQuery)
if len(filters) > 0:
for filterName, categoryIndex in filters:
query.addBindValue(filterName)
if len(tokens) > 0:
for t in tokens:
if t[0] != '-':
for _ in range(posQuery.count("(?)")):
query.addBindValue(f"%{t}%")
else:
for _ in range(negQuery.count("(?)")):
query.addBindValue(f"%{t[1:]}%")
# if len(search) > 0:
# for _ in range(8):
# query.addBindValue(f"%{search}%")
query.exec()
self.videoTableQuery(query)
def exportToMobile(model, params):
IMAGE_FORMAT = 'jpg'
IMAGE_COMPRESS = 50
USED_FIELDS = ('title', 'unit', 'country', 'year', 'mint', 'mintmark',
'issuedate', 'type', 'series', 'subjectshort', 'material',
'fineness', 'diameter', 'thickness', 'weight', 'mintage',
'rarity', 'obverseimg', 'reverseimg', 'subject', 'price1',
'price2', 'price3', 'price4')
if os.path.isfile(params['file']):
os.remove(params['file'])
db = QSqlDatabase.addDatabase('QSQLITE', 'mobile')
db.setDatabaseName(params['file'])
if not db.open():
print(db.lastError().text())
QMessageBox.critical(None, "Create mobile collection",
"Can't open collection")
return
sql = """CREATE TABLE "android_metadata" ("locale" TEXT DEFAULT 'en_US')"""
QSqlQuery(sql, db)
sql = """INSERT INTO "android_metadata" VALUES ('en_US')"""
QSqlQuery(sql, db)
mobile_settings = {
'Version': 1,
'Type': 'MobilePro',
'Filter': params['filter']
}
sql = """CREATE TABLE settings (
title CHAR NOT NULL UNIQUE,
value CHAR)"""
QSqlQuery(sql, db)
for key, value in mobile_settings.items():
query = QSqlQuery(db)
query.prepare("""INSERT INTO settings (title, value)
VALUES (?, ?)""")
query.addBindValue(key)
query.addBindValue(str(value))
query.exec_()
sql = """CREATE TABLE updates (
title CHAR NOT NULL UNIQUE,
value CHAR)"""
QSqlQuery(sql, db)
sql = """INSERT INTO updates (title, value)
VALUES ('160203', '2016-02-03T10:19:00')"""
QSqlQuery(sql, db)
sql = """CREATE TABLE photos (
id INTEGER PRIMARY KEY,
image BLOB)"""
QSqlQuery(sql, db)
sql = """CREATE TABLE coins (
id INTEGER PRIMARY KEY,
description_id INTEGER,
grade INTEGER,
createdat STRING)"""
QSqlQuery(sql, db)
sql = """CREATE INDEX coins_descriptions ON coins(description_id)"""
QSqlQuery(sql, db)
sqlFields = []
fields = CollectionFieldsBase()
for field in fields:
if field.name == 'id':
sqlFields.append('id INTEGER PRIMARY KEY')
elif field.name == 'image':
sqlFields.append('image INTEGER')
elif field.name in USED_FIELDS:
sqlFields.append("%s %s" % (field.name, Type.toSql(field.type)))
sql = "CREATE TABLE descriptions (" + ", ".join(sqlFields) + ")"
QSqlQuery(sql, db)
while model.canFetchMore():
model.fetchMore()
dest_model = QSqlTableModel(None, db)
dest_model.setEditStrategy(QSqlTableModel.OnManualSubmit)
dest_model.setTable('descriptions')
dest_model.select()
height = 64
if params['density'] == 'HDPI':
height *= 1.5
elif params['density'] == 'XHDPI':
height *= 2
elif params['density'] == 'XXHDPI':
height *= 3
elif params['density'] == 'XXXHDPI':
height *= 4
maxHeight = height * 4
is_obverse_enabled = params['image'] in (ExportDialog.IMAGE_OBVERSE,
ExportDialog.IMAGE_BOTH)
is_reverse_enabled = params['image'] in (ExportDialog.IMAGE_REVERSE,
ExportDialog.IMAGE_BOTH)
fields = CollectionFieldsBase()
count = model.rowCount()
progressDlg = Gui.ProgressDialog("Exporting records", "Cancel", count,
None)
for i in range(count):
progressDlg.step()
if progressDlg.wasCanceled():
break
coin = model.record(i)
if coin.value('status') in ('pass', 'sold'):
continue
dest_record = dest_model.record()
for field in fields:
if field.name in ('id', 'image', 'obverseimg', 'reverseimg'):
continue
if field.name in USED_FIELDS:
val = coin.value(field.name)
if val is None or val == '':
continue
dest_record.setValue(field.name, val)
# Process images
is_obverse_present = not coin.isNull('obverseimg')
is_reverse_present = not coin.isNull('reverseimg')
if is_obverse_present or is_reverse_present:
obverseImage = QImage()
reverseImage = QImage()
if is_obverse_present:
ba = QtCore.QByteArray()
buffer = QtCore.QBuffer(ba)
buffer.open(QtCore.QIODevice.WriteOnly)
obverseImage.loadFromData(coin.value('obverseimg'))
if not obverseImage.isNull() and not params[
'fullimage'] and obverseImage.height() > maxHeight:
scaledImage = obverseImage.scaled(maxHeight, maxHeight,
Qt.KeepAspectRatio,
Qt.SmoothTransformation)
scaledImage.save(buffer, IMAGE_FORMAT, IMAGE_COMPRESS)
save_data = ba
else:
if not obverseImage.isNull():
obverseImage.save(buffer, IMAGE_FORMAT, IMAGE_COMPRESS)
save_data = ba
else:
save_data = coin.value('obverseimg')
query = QSqlQuery(db)
query.prepare("""INSERT INTO photos (image)
VALUES (?)""")
query.addBindValue(save_data)
query.exec_()
img_id = query.lastInsertId()
dest_record.setValue('obverseimg', img_id)
if not obverseImage.isNull():
obverseImage = obverseImage.scaledToHeight(
height, Qt.SmoothTransformation)
if is_reverse_present:
ba = QtCore.QByteArray()
buffer = QtCore.QBuffer(ba)
buffer.open(QtCore.QIODevice.WriteOnly)
reverseImage.loadFromData(coin.value('reverseimg'))
if not reverseImage.isNull() and not params[
'fullimage'] and reverseImage.height() > maxHeight:
scaledImage = reverseImage.scaled(maxHeight, maxHeight,
Qt.KeepAspectRatio,
Qt.SmoothTransformation)
scaledImage.save(buffer, IMAGE_FORMAT, IMAGE_COMPRESS)
save_data = ba
else:
if not reverseImage.isNull():
reverseImage.save(buffer, IMAGE_FORMAT, IMAGE_COMPRESS)
save_data = ba
else:
save_data = coin.value('reverseimg')
query = QSqlQuery(db)
query.prepare("""INSERT INTO photos (image)
VALUES (?)""")
query.addBindValue(save_data)
query.exec_()
img_id = query.lastInsertId()
dest_record.setValue('reverseimg', img_id)
if not reverseImage.isNull():
reverseImage = reverseImage.scaledToHeight(
height, Qt.SmoothTransformation)
if not is_obverse_enabled:
obverseImage = QImage()
if not is_reverse_enabled:
reverseImage = QImage()
image = QImage(obverseImage.width() + reverseImage.width(), height,
QImage.Format_RGB32)
image.fill(QColor(Qt.white).rgb())
paint = QPainter(image)
if is_obverse_present and is_obverse_enabled:
paint.drawImage(
QtCore.QRectF(0, 0, obverseImage.width(), height),
obverseImage,
QtCore.QRectF(0, 0, obverseImage.width(), height))
if is_reverse_present and is_reverse_enabled:
paint.drawImage(
QtCore.QRectF(obverseImage.width(), 0,
reverseImage.width(), height), reverseImage,
QtCore.QRectF(0, 0, reverseImage.width(), height))
paint.end()
ba = QtCore.QByteArray()
buffer = QtCore.QBuffer(ba)
buffer.open(QtCore.QIODevice.WriteOnly)
image.save(buffer, IMAGE_FORMAT, 75)
query = QSqlQuery(db)
query.prepare("""INSERT INTO photos (image)
VALUES (?)""")
query.addBindValue(ba)
query.exec_()
img_id = query.lastInsertId()
dest_record.setValue('image', img_id)
dest_model.insertRecord(-1, dest_record)
progressDlg.setLabelText("Saving...")
dest_model.submitAll()
progressDlg.setLabelText("Compact...")
QSqlQuery(
"""UPDATE descriptions
SET
reverseimg = (select t2.id from descriptions t3 join (select id, image from photos group by image having count(*) > 1) t2 on t1.image = t2.image join photos t1 on t3.reverseimg = t1.id where t1.id <> t2.id and t3.id = descriptions.id)
WHERE descriptions.id in (select t3.id from descriptions t3 join (select id, image from photos group by image having count(*) > 1) t2 on t1.image = t2.image join photos t1 on t3.reverseimg = t1.id where t1.id <> t2.id)
""", db)
QSqlQuery(
"""UPDATE descriptions
SET
obverseimg = (select t2.id from descriptions t3 join (select id, image from photos group by image having count(*) > 1) t2 on t1.image = t2.image join photos t1 on t3.obverseimg = t1.id where t1.id <> t2.id and t3.id = descriptions.id)
WHERE descriptions.id in (select t3.id from descriptions t3 join (select id, image from photos group by image having count(*) > 1) t2 on t1.image = t2.image join photos t1 on t3.obverseimg = t1.id where t1.id <> t2.id)
""", db)
QSqlQuery(
"""UPDATE descriptions
SET
image = (select t2.id from descriptions t3 join (select id, image from photos group by image having count(*) > 1) t2 on t1.image = t2.image join photos t1 on t3.image = t1.id where t1.id <> t2.id and t3.id = descriptions.id)
WHERE descriptions.id in (select t3.id from descriptions t3 join (select id, image from photos group by image having count(*) > 1) t2 on t1.image = t2.image join photos t1 on t3.image = t1.id where t1.id <> t2.id)
""", db)
QSqlQuery(
"""DELETE FROM photos
WHERE id NOT IN (SELECT id FROM photos GROUP BY image)""", db)
db.close()
progressDlg.setLabelText("Vacuum...")
db = QSqlDatabase.addDatabase('QSQLITE', 'mobile')
db.setDatabaseName(params['file'])
if not db.open():
print(db.lastError().text())
QMessageBox.critical(None, "Create mobile collection",
"Can't open collection")
return
QSqlQuery("VACUUM", db)
db.close()
progressDlg.reset()
def CadastrarAluguel(aluguel):
conn = ConexaoSQL
db = conn.getConexao()
db.open()
query = QSqlQuery()
query.prepare(
"UPDATE Veiculo SET Alugado = 'Sim' WHERE CodigoVeic = " +
aluguel.CodigoVeic)
query.exec_()
db.commit()
query.prepare(
"INSERT INTO Aluguel(DataAluguel, DataPrazo, DataDevolucao, ValorAluguel, "
"ValorMulta, KmEntrada, KmSaida, CodigoCli, CodigoVeic) "
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)")
query.addBindValue(aluguel.DataAluguel)
query.addBindValue(aluguel.DataPrazo)
query.addBindValue(aluguel.DataDevolucao)
query.addBindValue(aluguel.ValorAluguel)
query.addBindValue(aluguel.ValorMulta)
query.addBindValue(aluguel.KmEntrada)
query.addBindValue(aluguel.KmSaida)
query.addBindValue(aluguel.CodigoCli)
query.addBindValue(aluguel.CodigoVeic)
query.exec_()
db.commit()
def on_db_save_clicked(self):
"""
保存数据到数据库
:param: 无
:return: 无
"""
self.__DB = QSqlDatabase.addDatabase('QSQLITE')
self.__DB.setDatabaseName('Library_Management.db')
self.__DB.open()
query = QSqlQuery()
# 先清空所有记录
#truncate_statement = 'TRUNCATE TABLE book_baseinfo'
#query.exec_(truncate_statement)
now = self.__book_list.head()
flag = True
while now != None and now.next != None and flag == True:
insert_statement = 'insert into book_baseinfo values(?,?,?,?,?,?,?,?,?,?)'
query.prepare(insert_statement)
query.addBindValue(str(now.get_data().get_ID()))
query.addBindValue(str(now.get_data().get_name()))
query.addBindValue(str(now.get_data().get_ISBN()))
query.addBindValue(str(now.get_data().get_author()))
query.addBindValue(str(now.get_data().get_pageCnt()))
query.addBindValue(str(now.get_data().get_publishedDate()))
query.addBindValue(str(now.get_data().get_entryDate()))
query.addBindValue(str(now.get_data().get_price()))
query.addBindValue(str(now.get_data().get_catelog()))
query.addBindValue(str(now.get_data().get_balance()))
# 上面是书籍基本数据，现在要把所有的借还书记录搞出来
if not query.exec_():
print(query.lastError())
if now == self.__book_list.tail():
flag = False
break
now = now.next
insert_statement = 'insert into book_BRinfo values(?,?,?,?,?,?,?,?,?,?)'
cur_row_cnt = self.__ui.main_BRLog_table.rowCount()
cur_col_cnt = self.__ui.main_BRLog_table.columnCount()
if cur_row_cnt == 0:
return
for i in range(cur_row_cnt):
query.prepare(insert_statement)
for j in range(cur_col_cnt):
text = self.__ui.main_BRLog_table.item(i, j)
query.addBindValue(text)
try:
if not query.exec_():
raise ValueError(query.lastError())
except ValueError as v:
print('Error:', repr(v))
def save_lists(self, only_if_changed=False):
if not only_if_changed or self.__lists_changed:
self.db.transaction()
# Remove old values
self.__remove_lists()
for position, param in enumerate(self.columns):
query = QSqlQuery(self.db)
query.prepare("INSERT INTO lists (pageid, fieldid, position,"
" enabled, width, sortorder)"
" VALUES (?, ?, ?, ?, ?, ?)")
query.addBindValue(self.page.id)
query.addBindValue(param.fieldid)
query.addBindValue(position)
query.addBindValue(int(param.enabled))
if not param.enabled:
param.width = None
query.addBindValue(param.width)
query.addBindValue(param.sortorder)
query.exec_()
self.db.commit()
self.__lists_changed = False
def CadastrarVeiculo(veiculo):
conn = ConexaoSQL
db = conn.getConexao()
db.open()
query = QSqlQuery()
query.prepare("INSERT INTO Veiculo(Modelo, Marca, AnoModelo, Placa, "
"Alugado, Batido, KmAtual, ValorDiaria, Descricao, TipoVeiculo) "
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
query.addBindValue(veiculo.Modelo)
query.addBindValue(veiculo.Marca)
query.addBindValue(veiculo.AnoModelo)
query.addBindValue(veiculo.Placa)
query.addBindValue(veiculo.Alugado)
query.addBindValue(veiculo.Batido)
query.addBindValue(veiculo.KmAtual)
query.addBindValue(veiculo.ValorDiaria)
query.addBindValue(veiculo.Descricao)
query.addBindValue(veiculo.TipoVeiculo)
query.exec_()
db.commit()
def closedPages(self):
query = QSqlQuery(self.db)
query.prepare("SELECT * FROM pages WHERE isopen=? ORDER BY title")
query.addBindValue(int(False))
query.exec_()
return self.__queryToParam(query)
def openPage(self, page):
query = QSqlQuery(self.db)
query.prepare("UPDATE pages SET isopen=? WHERE id=?")
query.addBindValue(int(True))
query.addBindValue(page.id)
query.exec_()
def _fetch_data(self, sql: str, bind_values: list, sel_count: int) -> list:
"""It runs the given sql select query and returns the fetched data
The sql query placeholder should be "?". The values in the bind_values
parameter will replace "?".
:param sql: The sql query to run.
:type sql: str.
:param bind_values: The values to bind to the query placeholders.
:type bind_values: list.
:param sel_count: The number of fields in select query.
:type sel_count: int.
:return: The required data.
:rtype: list.
"""
# Try to open the connection and handle possible errors
if not self.con.open():
# The error is shown in message box
self._display_error("")
# The query object is created
query = QSqlQuery()
# If the sql query contains placeholders
if len(bind_values) > 0:
# Prepared query is used
query.prepare(sql)
# Each given bind value is added
for val in bind_values:
# The bind value is added
query.addBindValue(val)
# The query is run
if not query.exec():
self._display_error(sql)
else:
# The query is run
if not query.exec(sql):
self._display_error(sql)
# All rows
rows = []
# All rows are fetched
while query.next():
# The row of data
row = []
# All selected field values are fetched
for i in range(sel_count):
# The query value
qval = query.value(i)
# The query value is appended to the row
row.append(qval)
# The row in appended to the list of rows
rows.append(row)
# The resources associated with the query object are freed
query.finish()
# The connection is closed
#self.con.close()
# The data is returned
return rows
def remove(self):
query = QSqlQuery(self.db)
query.prepare("DELETE FROM statistics WHERE pageid=?")
query.addBindValue(self.pageId)
query.exec_()
class Database():
'''
数据库操作，包含查询密码，用户信息，和修改table，默认数据库为Sqlite
数据库名为test.db,包含两个表 user_info(用户信息）和sign_record（考勤记录）
不提供新建功能，只可清除表的内容
'''
def __init__(self):
'''
数据库初始化，数据库类型为Sqlite
'''
self.database = QtSql.QSqlDatabase.addDatabase('QSQLITE')
self.database.setDatabaseName('test.db')
self.database.open()
self.query = QSqlQuery()
def get_passwd(self,id):
'''
获取用户密码
:param id: 用户名ID type :int
:return: 用户密码
:type : str
'''
if self.query.exec('select passwd from user_info where id = %d'%id):
while self.query.next():
passwd = self.query.value(0)
return passwd
def get_user_info(self,id):
'''
获取用户信息
:param id: 用户ID type : int
:return: 用户所有信息
:type : dict
'''
if self.query.exec('select * from user_info where id = %d' % id):
while self.query.next():
ID = self.query.value(self.query.record().indexOf('id'))
username = self.query.value(self.query.record().indexOf('name'))
age = self.query.value(self.query.record().indexOf('age'))
sex = self.query.value(self.query.record().indexOf('sex'))
address = self.query.value(self.query.record().indexOf('address'))
tel = self.query.value(self.query.record().indexOf('tel'))
job = self.query.value(self.query.record().indexOf('job'))
finger_id = self.query.value(self.query.record().indexOf('finger_id'))
info = {'id':ID,'name':username,'age':age,'sex':sex,'address':address,'tel':tel,'job':job,'finger_id':finger_id}
return info
def change_user_info(self, new_info):
'''
更新用户信息
:param info:新的用户信息 type : dict
:return: None
'''
self.query.prepare('update user_info set name = ?, age = ?, job = ?, tel = ?, sex = ?, address = ? where id = %d'%(new_info['id']))
self.query.addBindValue(new_info['name'])
self.query.addBindValue(new_info['age'])
self.query.addBindValue(new_info['job'])
self.query.addBindValue(new_info['tel'])
self.query.addBindValue(new_info['sex'])
self.query.addBindValue(new_info['address'])
self.query.exec_()
def get_record(self):
'''
获取前100条签到数据
:return: data
:type:dict
'''
data = []
buff = dict()
if self.query.exec('select * from sign_record limit 100 '):
while self.query.next():
buff['time'] = self.query.value("time")
buff['id'] = self.query.value("id")
buff['name'] = self.query.value("name")
buff['operation'] = self.query.value("operation")
buff['illegal'] = self.query.value("illegal")
data.append(buff)
buff = dict()
return data
def get_record_by_id(self, id):
'''
通过id获取当前用户签到数据最多100条
:param id:用户id type : int
:return: data
:type:dict
'''
data = []
buff = dict()
if self.query.exec('select * from sign_record where id = %d limit 100' % id):
while self.query.next():
buff['time'] = self.query.value("time")
buff['id'] = self.query.value("id")
buff['name'] = self.query.value("name")
buff['operation'] = self.query.value("operation")
buff['illegal'] = self.query.value("illegal")
data.append(buff)
buff = dict()
return data
def new_record(self, id, operation):
'''
添加新的考勤记录
:param id:用户ID type:int
:param operation : 操作代号 type:int
:return: None
'''
current_time = datetime.now().strftime("%H:%M") #获取当前时间（时：分）
flag = judge_time_section(current_time)
if operation == 1:
operation ="签到"
if flag == 1:
illegal = "否"
else:
illegal = "是"
else:
operation = "离开"
if flag == 3:
illegal = "否"
else:
illegal = "是"
name = self.get_user_info(id)['name'] #获取对应ID的用户名
self.query.exec('INSERT INTO sign_record([id],[name],[operation],[illegal]) VALUES(%d,\'%s\',\'%s\',\'%s\'); ' %(id,name,operation,illegal))
def save(self):
self.db.transaction()
# Remove old values
self.remove()
# Save new all
for position, param in enumerate(self.columns):
query = QSqlQuery(self.db)
query.prepare("INSERT INTO lists (pageid, fieldid, position,"
" enabled, width, sortorder)"
" VALUES (?, ?, ?, ?, ?, ?)")
query.addBindValue(self.page.id)
query.addBindValue(param.fieldid)
query.addBindValue(position)
query.addBindValue(int(param.enabled))
if not param.enabled:
param.width = None
query.addBindValue(param.width)
query.addBindValue(param.sortorder)
query.exec_()
for fieldId, columnFilters in self.filters.items():
for filter_ in columnFilters.filters():
query = QSqlQuery(self.db)
query.prepare(
"INSERT INTO filters (pageid, fieldid, value,"
" blank, data, revert) VALUES (?, ?, ?, ?, ?, ?)")
query.addBindValue(self.page.id)
query.addBindValue(fieldId)
query.addBindValue(filter_.value)
if filter_.isBlank():
blank = int(True)
else:
blank = None
query.addBindValue(blank)
if filter_.isData():
data = int(True)
else:
data = None
query.addBindValue(data)
if filter_.isRevert():
revert = int(True)
else:
revert = None
query.addBindValue(revert)
query.exec_()
self.db.commit()
def addVideoToPlaylist(self, video_id, playlist_id):
query = QSqlQuery()
query.prepare("""INSERT INTO videos_playlists_link VALUES (?,?)""")
query.addBindValue(video_id)
query.addBindValue(playlist_id)
query.exec()
def __init__(self, page):
super(ListPageParam, self).__init__(page)
self.page = page
self.db = page.db
if 'lists' not in self.db.tables():
sql = """CREATE TABLE lists (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
pageid INTEGER,
fieldid INTEGER,
position INTEGER,
enabled INTEGER,
width INTEGER,
sortorder INTEGER)"""
QSqlQuery(sql, self.db)
query = QSqlQuery(self.db)
query.prepare("SELECT * FROM lists WHERE pageid=? ORDER BY position")
query.addBindValue(self.page.id)
query.exec_()
self.columns = []
while query.next():
param = ColumnListParam(query.record())
self.columns.append(param)
self.fields = page.fields
# Create default parameters
if not self.columns:
for field in self.fields.userFields:
enabled = False
if field.name in [
'image', 'title', 'value', 'unit', 'country', 'year',
'status'
]:
enabled = True
param = ColumnListParam(field.id, enabled)
self.columns.append(param)
if 'filters' not in self.db.tables():
sql = """CREATE TABLE filters (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
pageid INTEGER,
fieldid INTEGER,
value INTEGER,
blank INTEGER,
data INTEGER,
revert INTEGER)"""
QSqlQuery(sql, self.db)
query = QSqlQuery(self.db)
query.prepare("SELECT * FROM filters WHERE pageid=?")
query.addBindValue(self.page.id)
query.exec_()
self.filters = {}
while query.next():
fieldId = query.record().value('fieldid')
column_name = self.fields.field(fieldId).name
if not query.record().isNull('value'):
value = str(query.record().value('value'))
filter_ = ValueFilter(column_name, value)
if not query.record().isNull('data'):
if query.record().value('data'):
filter_ = DataFilter(column_name)
if not query.record().isNull('blank'):
if query.record().value('blank'):
filter_ = BlankFilter(column_name)
if not query.record().isNull('revert'):
if query.record().value('revert'):
filter_.revert = True
if fieldId not in self.filters.keys():
self.filters[fieldId] = ColumnFilters(column_name)
self.filters[fieldId].addFilter(filter_)
# 2. 新建表
# 新建一个名为student的表，包含id, name, age 三个属性, 其中ID为主键
query.prepare('create table student (id int primary key, name varchar(30), age int)')
if not query.exec_():
query.lastError()
else:
print('create a table')
# 3. 插入数据
# addBindValue()将值添加到列表中，调用顺序决定添加的顺序
insert_sql = 'insert into student values (?, ?, ?)'
query.prepare(insert_sql)
query.addBindValue(4)
query.addBindValue('test3')
query.addBindValue(1)
if not query.exec_():
print(query.lastError())
else:
print('inserted')
# 4. 查询
# 查询返回数据使用value(int)函数，例如select id, name, age from student value(0) 等于返回id属性的值, value(2) 等于age熟悉
# exec_() 查询成功返回true查询 否则返回false
query.prepare('select id, name, age from student')
if not query.exec_():
query.lastError()
def save(self):
self.db.transaction()
self.remove()
query = QSqlQuery(self.db)
query.prepare(
"INSERT INTO statistics (pageid, showed, chart, fieldid, subfieldid, items, period, color)"
" VALUES (?, ?, ?, ?, ?, ?, ?, ?)")
query.addBindValue(self.pageId)
query.addBindValue(int(self.__getitem__('showed')))
query.addBindValue(self.__getitem__('chart'))
query.addBindValue(self.__getitem__('fieldid'))
query.addBindValue(self.__getitem__('subfieldid'))
query.addBindValue(self.__getitem__('items'))
query.addBindValue(self.__getitem__('period'))
query.addBindValue(int(self.__getitem__('color')))
query.exec_()
self.db.commit()
def save_filters(self):
self.db.transaction()
# Remove old values
self.__remove_filters()
for fieldId, columnFilters in self.filters.items():
for filter_ in columnFilters.filters():
query = QSqlQuery(self.db)
query.prepare("INSERT INTO filters (pageid, fieldid, value,"
" blank, data, revert) VALUES (?, ?, ?, ?, ?, ?)")
query.addBindValue(self.page.id)
query.addBindValue(fieldId)
query.addBindValue(filter_.value)
if filter_.isBlank():
blank = int(True)
else:
blank = None
query.addBindValue(blank)
if filter_.isData():
data = int(True)
else:
data = None
query.addBindValue(data)
if filter_.isRevert():
revert = int(True)
else:
revert = None
query.addBindValue(revert)
query.exec_()
self.db.commit()
def new_reservation(ui, window, db, discount, thrd, mainui, model):
db.open()
query = QSqlQuery(db)
#Retrieve room price from DB
price_query = QSqlQuery(db)
price_query.prepare('SELECT Price FROM Room WHERE Number = ?')
price_query.addBindValue(ui.comboBox_2.currentText())
price_query.exec_()
price_query.next()
price = price_query.value(0)
#Prepare a query and add all values then execute and commit to DB
db.transaction()
query.prepare('INSERT INTO CurrentReservation VALUES (?,?,?,?,?,?,?,?)')
query.addBindValue(ui.lineEdit.text())
query.addBindValue(ui.treeView.currentIndex().siblingAtColumn(1).data())
query.addBindValue(ui.comboBox_2.currentText())
query.addBindValue(ui.dateEdit.date().toString("yyyy-MM-dd"))
query.addBindValue(ui.dateEdit_2.date().toString("yyyy-MM-dd"))
#Check if discount is applied
if discount:
query.addBindValue(ui.spinBox.value())
query.addBindValue(0)
query.addBindValue(price - (price * (ui.spinBox.value() / 100)))
else:
query.addBindValue(ui.spinBox.value())
query.addBindValue(0)
query.addBindValue(price)
query.exec_()
db.commit()
db.close()
if query.lastError().text() == '':
QtWidgets.QMessageBox.information(
window, 'New Reservation',
'New Reservation has been successfully created',
QtWidgets.QMessageBox.Ok)
else:
QtWidgets.QMessageBox.information(window, 'New Reservation',
query.lastError().text(),
QtWidgets.QMessageBox.Ok)
ui.lineEdit.clear()
ui.checkBox.setCheckState(QtCore.Qt.Unchecked)
hlist = [
'Reserv. ID', 'Customer ID', 'Room #', 'From', 'To', 'Discount',
'Extension', 'Net Total'
]
worker = TableWorker(
update_table("CurrentReservation", hlist, mainui.current_res, db,
model))
thrd.tryStart(worker)
class pyMailArchiver(QMainWindow):
def __init__(self, parent=None):
# Set up the user interface from Designer.
super(pyMailArchiver, self).__init__()
uic.loadUi("pyMailArchiverInterface.ui", self)
self.show()
self.read_config()
self.m_typingTimer = QTimer()
self.m_typingTimer.setSingleShot(True)
self.m_typingTimer.timeout.connect(self.filterEntries)
self.line_filter_all.textChanged.connect(self.onTextEdited)
self.line_filter_subject.textChanged.connect(self.onTextEdited)
self.line_filter_from.textChanged.connect(self.onTextEdited)
self.line_filter_to.textChanged.connect(self.onTextEdited)
self.line_filter_timestamp.textChanged.connect(self.onTextEdited)
self.line_filter_attachments.textChanged.connect(self.onTextEdited)
self.line_filter_message.textChanged.connect(self.onTextEdited)
int_validator = QRegExpValidator(QRegExp("^[0-9]*$"))
self.line_add_port.setValidator(int_validator)
self.pBar_progress_import.setMinimum(0)
self.pBar_progress_import.setValue(0)
self.lastFilterCall = time.time()
self.threadDump = []
##### Database Setup with QtSQL
db_exist = os.path.isfile(self.db_path)
self.database = QSqlDatabase.addDatabase("QSQLITE")
self.database.setDatabaseName(self.db_path)
self.database.open()
self.dbquery = QSqlQuery()
if db_exist == False:
# Create new database if none is there
self.dbquery.prepare(
'''CREATE TABLE `mails` (`MailID` INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, `MailHash` TEXT, `Subject` TEXT, `Sender` TEXT, `Recipient` TEXT, `CC_recp` TEXT, `BCC_recp` TEXT, `receive_time` TIMESTAMP, `attachments` TEXT, `account` TEXT, `folder` TEXT, `Message` TEXT)'''
)
self.dbquery.exec_()
self.database.commit()
# Setup TableModel to populate tables in UI directly from database
self.model_mails = QSqlQueryModel(self)
#self.model_mails.setTable("mails")
self.model_mails.setQuery(
"SELECT * from mails ORDER BY receive_time DESC")
#self.model_mails.select()
class dummy:
def text(self):
return ""
self.proxy = CustomSortFilterProxyModel()
self.proxy.setFilterTextBoxDict({
"all": self.line_filter_all,
"subject": self.line_filter_subject,
"from": self.line_filter_from,
"to": self.line_filter_to,
"cc": self.cBox_filter_cc,
"bcc": self.cBox_filter_bcc,
"receive_time": self.line_filter_timestamp,
"attachments_yes": self.cBox_filter_attachment_yes,
"attachments_no": self.cBox_filter_attachment_no,
"attachments_name": self.line_filter_attachments,
"account": self.cBox_filter_account,
"folder": self.cBox_filter_folder,
"message": self.line_filter_message
})
self.proxy.setSourceModel(self.model_mails)
#self.tableView_mails.setModel(self.proxy)
self.tableView_mails.setModel(self.model_mails)
self.tableView_mails.setColumnHidden(0, True)
self.tableView_mails.setColumnHidden(1, True)
self.tableView_mails.setColumnHidden(5, True)
self.tableView_mails.setColumnHidden(6, True)
self.tableView_mails.setColumnHidden(8, True)
self.tableView_mails.setColumnHidden(9, True)
self.tableView_mails.setColumnHidden(10, True)
self.tableView_mails.setColumnHidden(11, True)
self.tableView_mails.setEditTriggers(QAbstractItemView.NoEditTriggers)
self.tableView_mails.verticalHeader().setVisible(False)
self.tableView_mails.setSortingEnabled(True)
#self.tableView_mails.sortByColumn(7,QtCore.Qt.DescendingOrder)
self.tableView_mails.update()
# populate Account Name combobox
self.cBox_account.addItem("-- Account --")
for filename in os.listdir("."):
if filename.endswith(".account"):
self.cBox_account.addItem(filename.rsplit(".", 1)[0])
self.dbquery.prepare("SELECT DISTINCT account FROM mails")
self.dbquery.exec_()
while self.dbquery.next():
self.cBox_filter_account.addItem(self.dbquery.value(0))
self.dbquery.prepare("SELECT DISTINCT folder FROM mails")
self.dbquery.exec_()
while self.dbquery.next():
self.cBox_filter_folder.addItem(self.dbquery.value(0))
return
def read_config(self, filename="pyMailArchiverConfig.ini"):
config = ConfigParser()
config.read(filename)
#### Global
try:
self.db_path = config["Global"]["DatabaseFile"]
print(self.db_path)
except:
QMessageBox.warning(
self, 'Invalid config value',
'DatabaseFile value for Global in config file invalid',
QMessageBox.Ok)
try:
self.archiveBasePath = config["Global"]["archiveBasePath"]
self.archiveBasePath = self.archiveBasePath.replace("\\\\",
"/").replace(
"\\", "/")
if self.archiveBasePath[-1] != "/":
self.archiveBasePath += "/"
print(self.archiveBasePath)
except:
QMessageBox.warning(
self, 'Invalid config value',
'archiveBasePath value for Global in config file invalid',
QMessageBox.Ok)
def filterEntries(self):
queryString = "SELECT * FROM mails"
# variables for query. store them in list b/c QSqlQuery.addBindValue can only be called after QSqlQuery.prepare
bindValueList = []
# build query string based on actually used filter fields.
filterString = self.line_filter_subject.text()
if filterString != "":
queryString += " WHERE (Subject LIKE ?"
bindValueList.append("%" + filterString + "%")
filterString = self.line_filter_message.text()
if filterString != "":
if len(bindValueList) > 0:
queryString += " AND "
else:
queryString += " WHERE ("
queryString += "Message LIKE ?"
bindValueList.append("%" + filterString + "%")
filterString = self.line_filter_timestamp.text()
if filterString != "":
if len(bindValueList) > 0:
queryString += " AND "
else:
queryString += " WHERE ("
queryString += "receive_time LIKE ?"
bindValueList.append("%" + filterString + "%")
filterString = self.line_filter_from.text()
if filterString != "":
if len(bindValueList) > 0:
queryString += " AND "
else:
queryString += " WHERE ("
queryString += "Sender LIKE ?"
bindValueList.append("%" + filterString + "%")
filterString = self.line_filter_to.text()
if filterString != "":
if len(bindValueList) > 0:
queryString += " AND "
else:
queryString += " WHERE ("
if self.cBox_filter_cc.isChecked(
) or self.cBox_filter_bcc.isChecked():
queryString += "(Recipient LIKE ?"
bindValueList.append("%" + filterString + "%")
if self.cBox_filter_cc.isChecked():
queryString += " OR CC_recp LIKE ?"
bindValueList.append("%" + filterString + "%")
if self.cBox_filter_bcc.isChecked():
queryString += " OR BCC_recp LIKE ?"
bindValueList.append("%" + filterString + "%")
queryString += ")"
else:
queryString += "Recipient LIKE ?"
bindValueList.append("%" + filterString + "%")
filterString = self.line_filter_attachments.text()
if filterString != "":
if len(bindValueList) > 0:
queryString += " AND "
else:
queryString += " WHERE ("
queryString += "attachments LIKE ?"
bindValueList.append("%" + filterString + "%")
elif self.cBox_filter_attachment_yes.isChecked(
) != self.cBox_filter_attachment_no.isChecked():
if len(bindValueList) > 0:
queryString += " AND "
else:
queryString += " WHERE ("
if self.cBox_filter_attachment_yes.isChecked():
queryString += "attachments != ?"
else:
queryString += "attachments = ?"
bindValueList.append("None")
if self.cBox_filter_account.currentIndex() != 0:
if len(bindValueList) > 0:
queryString += " AND "
else:
queryString += " WHERE ("
queryString += "account = ?"
bindValueList.append(self.cBox_filter_account.currentText())
if self.cBox_filter_folder.currentIndex() != 0:
if len(bindValueList) > 0:
queryString += " AND "
else:
queryString += " WHERE ("
queryString += "folder = ?"
bindValueList.append(self.cBox_filter_folder.currentText())
if len(bindValueList) > 0:
queryString += ")"
# add filtering of all fields if filter line "all" is used
filterString = self.line_filter_all.text()
if filterString != "":
if len(bindValueList) > 0:
queryString += " AND "
else:
queryString += " WHERE "
queryString += "(Subject LIKE ? OR Message LIKE ? OR receive_time LIKE ? OR Sender LIKE ? OR Recipient LIKE ? OR CC_recp LIKE ? OR BCC_recp LIKE ? OR attachments LIKE ? OR account LIKE ? OR folder LIKE ?)"
for i in range(10):
bindValueList.append("%" + filterString + "%")
queryString += " ORDER BY receive_time DESC"
# prepare query and add bind values
self.dbquery.prepare(queryString)
for value in bindValueList:
self.dbquery.addBindValue(value)
self.dbquery.exec_()
self.model_mails.setQuery(self.dbquery)
print("filtering")
def onTextEdited(self):
self.m_typingTimer.start(200)
@pyqtSlot(int)
def on_cBox_filter_cc_stateChanged(self, state):
print(5)
self.filterEntries()
@pyqtSlot(int)
def on_cBox_filter_bcc_stateChanged(self, state):
print(6)
self.filterEntries()
@pyqtSlot(int)
def on_cBox_filter_attachment_yes_stateChanged(self, state):
print(8)
self.filterEntries()
@pyqtSlot(int)
def on_cBox_filter_attachment_no_stateChanged(self, state):
print(9)
self.filterEntries()
@pyqtSlot(int)
def on_cBox_filter_account_currentIndexChanged(self, state):
print(11)
self.filterEntries()
@pyqtSlot(int)
def on_cBox_filter_folder_currentIndexChanged(self, state):
print(12)
self.filterEntries()
@pyqtSlot(int)
def on_cBox_password_dont_save_stateChanged(self, state):
self.line_add_password.setEnabled(not state)
@pyqtSlot(QModelIndex)
def on_tableView_mails_doubleClicked(self, index):
row = index.row()
mailID = self.tableView_mails.model().data(
self.tableView_mails.model().index(row, 0))
filepath = self.constructFilePath(mailID)
print("opening ", filepath)
#os.startfile(filepath)
if sys.platform == "win32":
os.startfile(filepath)
else:
opener = "open" if sys.platform == "darwin" else "xdg-open"
subprocess.call(["thunderbird", filepath])
@pyqtSlot()
def on_btn_add_account_clicked(self):
server = self.line_add_server.text()
port = self.line_add_port.text()
username = self.line_add_username.text()
if self.cBox_password_dont_save.isChecked():
password = "******"
else:
password = self.line_add_password.text()
if "" in [server, port, username, password]:
QMessageBox.warning(self, 'Incomplete form!',
'Please fill all fields before continuing.',
QMessageBox.Ok)
return
if self.cBox_password_dont_save.isChecked():
text, ok = QtGui.QInputDialog.getText(
self,
'Input Dialog',
'Enter password:'******'Connection failed!',
'Connecting to server failed. Check server settings and credentials.',
QMessageBox.Ok)
return
accountConfig = ConfigParser()
accountConfig["server"] = {"name": server, "port": port}
accountConfig["credentials"] = {"name": username, "pass": password}
with open(username + ".account", "w") as configfile:
accountConfig.write(configfile)
QMessageBox.information(
self, 'Connection succesfull!',
'Successfully connected to server! Settings saved!',
QMessageBox.Ok)
return
@pyqtSlot()
def on_btn_archive_new_clicked(self):
account = self.cBox_account.currentText()
if account == "-- Select --":
return
# read config data from account file
accountData = ConfigParser()
accountData.read(account + ".account")
serverName = accountData['server']['name']
serverPort = accountData['server']['port']
username = accountData['credentials']['name']
password = accountData['credentials']['pass']
exclude_patterns = accountData['settings']['excludedFolders']
# process config data
if username.lower() == "input":
text, ok = QtGui.QInputDialog.getText(self, 'Input Dialog',
'Enter username:'******'Input Dialog',
'Enter password:'******'Parsing excluded folders failed!',
'Parsing excluded folders failed! Check config file and make sure excludedFolders is a list of valid regex expressions.',
QMessageBox.Ok)
return
# connect to server
try:
server = imaplib.IMAP4_SSL(serverName, int(serverPort))
server.login(username, password)
except:
QMessageBox.warning(
self, 'Connection failed!',
'Connecting to server failed. Check server settings and credentials in config file.',
QMessageBox.Ok)
return
# Get list of all folders
typ, data = server.list()
serverFolders = []
localFolders = []
for folder in data:
folder_str = folder.decode("utf-8")
print(folder)
print(folder_str)
folder_str = folder_str.split('"/"', 1)[1].strip()
serverFolders.append(folder_str)
folder_str = folder_str.replace('"', '') + "/"
localFolders.append(folder_str)
new_mails = 0
for i in range(len(serverFolders)):
serverFolder = serverFolders[i]
localFolder = localFolders[i]
self.label_progress_folder.setText("Processing Folder: " +
localFolder)
print("----")
# skip folder if in exclude list
skip = False
if exclude_patterns_compiled:
for pattern in exclude_patterns_compiled:
if pattern.match(localFolder):
print("skipping", localFolder)
skip = True
break
if skip:
print("now skipping")
continue
print(serverFolder)
server.select(serverFolder)
typ, data = server.search(None, 'ALL')
# iterate over mails in folder
num_emails = len(data[0].split())
self.pBar_progress_import.setMinimum(0)
self.pBar_progress_import.setMaximum(num_emails)
mailCount = 0
for num in data[0].split():
mailCount += 1
self.label_progress_email.setText("E-Mail: %s/%s" %
(mailCount, num_emails))
self.pBar_progress_import.setValue(mailCount)
QCoreApplication.processEvents()
header = {}
print("------")
print(localFolder)
# get basic data to create hash
typ, data = server.fetch(num, '(BODY[HEADER.FIELDS (date)])')
header["date"] = data[0][1].decode("utf-8").split(
":", 1)[1].split("\r")[0][1:].strip()
typ, data = server.fetch(num,
'(BODY[HEADER.FIELDS (subject)])')
header["subject"] = data[0][1].decode("utf-8").strip()
typ, data = server.fetch(num, '(BODY[HEADER.FIELDS (from)])')
header["from"] = data[0][1].decode("utf-8").strip()
typ, data = server.fetch(num, '(BODY[HEADER.FIELDS (to)])')
header["to"] = data[0][1].decode("utf-8").strip()
typ, data = server.fetch(num, '(BODY[HEADER.FIELDS (cc)])')
header["cc"] = data[0][1].decode("utf-8").strip()
typ, data = server.fetch(num, '(BODY[HEADER.FIELDS (bcc)])')
header["bcc"] = data[0][1].decode("utf-8").strip()
header = self.parseHeader(header)
unique_hash = self.createMailHash(header)
print("header", header)
print("hash", unique_hash)
if self.checkMailExist(unique_hash):
# message already exists
print(
"SKIPPING MAIL SKIPPING MAIL SKIPPING MAIL SKIPPING MAIL SKIPPING MAIL SKIPPING MAIL SKIPPING MAIL SKIPPING MAIL"
)
continue
new_mails += 1
#print("archiving")
typ, data = server.fetch(num, '(RFC822)')
message_bytes = data[0][1]
message = email.message_from_bytes(message_bytes)
self.saveMailToDatabase(message, account, localFolder)
# get MailID from database
self.dbquery.prepare(
"SELECT MailID FROM mails WHERE MailHash=?")
self.dbquery.addBindValue(unique_hash)
self.dbquery.exec_()
self.dbquery.next()
MailID = self.dbquery.value(0)
# generate savepath and save mail to file
savename = self.constructFilePath(MailID)
savepath = savename.rsplit("/", 1)[0]
if not os.path.exists(savepath):
os.makedirs(savepath)
f = open(savename, 'wb')
f.write(message_bytes)
f.close()
self.tableView_mails.update()
print("new mails", new_mails)
self.label_progress_folder.setText("Finished succesfully!")
self.label_progress_email.setText("%s new Mails" % new_mails)
def parseHeader(self, header):
ret_header = {}
print(header)
print(header["date"])
timeString = header["date"]
try:
timestamp = dateutil.parser.parse(header["date"].replace(
"(", "").replace(")", ""))
except (ValueError):
timestamp = dateutil.parser.parse(header["date"].replace(
"(", "").replace(")", "").rsplit(" ", 1)[0])
"""
# catch variations of empty subjects
if header["subject"] in ["\r\n", "\n", "\r"] or header["subject"] == None:
subject = ""
else:
subjectList = email.header.decode_header(header["subject"])
subject = ""
for subjectPart in subjectList:
if type(subjectPart[0]) == bytes:
print(subjectPart)
print(type(subjectPart))
print(type(subjectPart[0]))
print(type(subjectPart[1]))
if subjectPart[1] == None:
subject += subjectPart[0].decode(errors="replace")
else:
subject += subjectPart[0].decode(subjectPart[1],errors="replace")
else:
try:
subject += str(subjectPart[0])
except:
pass
# someteimes there is a "Subject:" in front of the subject
# get rid of this
if len(subject) > 7:
if subject[0:8] == "Subject:":
if len(subject) > 8:
subject = subject[8:]
else:
subject = ""
"""
def decode_header_field(fieldString):
# catch variations of empty subjects
if fieldString in ["\r\n", "\n", "\r"] or fieldString == None:
returnString = ""
else:
fieldList = email.header.decode_header(fieldString)
returnString = ""
for fieldPart in fieldList:
if type(fieldPart[0]) == bytes:
print(fieldPart)
print(type(fieldPart))
print(type(fieldPart[0]))
print(type(fieldPart[1]))
if fieldPart[1] == None:
returnString += fieldPart[0].decode(
errors="replace")
else:
returnString += fieldPart[0].decode(
fieldPart[1], errors="replace")
else:
try:
returnString += str(fieldPart[0])
except:
pass
return returnString
subject = decode_header_field(header["subject"]).replace(
"Subject:", "").strip()
sender = decode_header_field(header["from"]).replace("From:",
"").strip()
recipient = decode_header_field(header["to"]).replace("To:",
"").strip()
cc = decode_header_field(header["cc"]).replace("CC:", "").strip()
bcc = decode_header_field(header["bcc"]).replace("BCC:", "").strip()
def parseMailAdressList(AdressList):
adList = re.findall(r'[\w\.-]+@[\w\.-]+', AdressList)
adList = getUniqueList(adList)
return adList
ret_header["subject"] = subject
ret_header["date"] = timestamp
ret_header["from"] = sender
ret_header["to"] = recipient
ret_header["cc"] = cc
ret_header["bcc"] = bcc
ret_header["from_only_email"] = parseMailAdressList(header["from"])
ret_header["to_only_email"] = parseMailAdressList(header["to"])
ret_header["cc_only_email"] = parseMailAdressList(header["cc"])
ret_header["bcc_only_email"] = parseMailAdressList(header["bcc"])
return ret_header
def getMessageHeader(self, message):
header = {}
sender = message["from"]
if sender == None:
sender = ""
header["from"] = sender
recipient = message["to"]
if recipient == None:
recipient = ""
header["to"] = recipient
cc = message["cc"]
if cc == None:
cc = ""
header["cc"] = cc
bcc = message["bcc"]
if bcc == None:
bcc = ""
header["bcc"] = bcc
header["date"] = message["date"]
header["subject"] = message["subject"]
header = self.parseHeader(header)
return header
def saveMailToDatabase(self, message, account, localFolder):
if localFolder.endswith("/"):
localFolder = localFolder[:-1]
if localFolder.startswith("/"):
localFolder = localFolder[1:]
header = self.getMessageHeader(message)
messageBody, attachments = self.parseMessage(message)
def jsondump_list(dumplist):
if len(dumplist) > 1:
dumplist = json.dumps(dumplist)
elif type(dumplist) == type([1]) and len(dumplist) == 0:
dumplist = "None"
elif type(dumplist) == type([1]):
dumplist = dumplist[0]
return dumplist
sender = header["from"]
recipient = header["to"]
cc = header["cc"]
bcc = header["bcc"]
subject = header["subject"]
date_object = header["date"]
unique_hash = self.createMailHash(header)
print("header", header)
print("hash", unique_hash)
attachments = jsondump_list(attachments)
if recipient == "":
recipient = "None"
if cc == "":
cc = "None"
if bcc == "":
bcc = "None"
self.dbquery.prepare(
"INSERT INTO mails (MailHash, Subject, Sender, Recipient, CC_recp, BCC_recp, receive_time, attachments, account, folder, Message) VALUES (?,?,?,?,?,?,?,?,?,?,?)"
)
self.dbquery.addBindValue(unique_hash)
self.dbquery.addBindValue(subject)
self.dbquery.addBindValue(sender)
self.dbquery.addBindValue(recipient)
self.dbquery.addBindValue(cc)
self.dbquery.addBindValue(bcc)
self.dbquery.addBindValue(QtCore.QDateTime(date_object))
self.dbquery.addBindValue(attachments)
self.dbquery.addBindValue(account)
self.dbquery.addBindValue(localFolder)
self.dbquery.addBindValue(messageBody)
self.dbquery.exec_()
self.database.commit()
print("commited")
def createMailHash(self, header):
hash_string = str(header["date"]) + header["subject"] + ''.join(
header["from_only_email"] + header["to_only_email"] +
header["cc_only_email"] + header["bcc_only_email"])
unique_hash = hashlib.sha512(hash_string.encode("utf-8")).hexdigest()
return unique_hash
def checkMailExist(self, unique_hash):
self.dbquery.prepare("SELECT MailID FROM mails WHERE MailHash=?")
self.dbquery.addBindValue(unique_hash)
self.dbquery.exec_()
return self.dbquery.next()
def parseMessage(self, message):
attachments = []
try:
if message.is_multipart():
payload = message.get_payload()
for part in payload:
if part.get_content_type().find("text") != -1:
if part.get_content_charset():
messageBody = part.get_payload(decode=True).decode(
part.get_content_charset(), "replace")
else:
messageBody = part.get_payload(decode=False)
elif part.get_content_disposition() == "attachment":
filename = part.get_filename()
attachments.append(filename)
elif part.is_multipart():
messageBody, attachments = self.parseMessage(part)
elif message.get_content_type().find("text") != -1:
if message.get_content_charset():
messageBody = message.get_payload(decode=True).decode(
message.get_content_charset(), "replace")
else:
messageBody = message.get_payload(decode=False)
else:
print(message.get_content_type())
messageBody = messageBody.replace("\r", "").replace("\n", "")
except:
messageBody = "Message cannot be previewed here. Please open in external program."
return messageBody, attachments
@pyqtSlot()
def on_btn_import_folder_clicked(self):
path = str(QFileDialog.getExistingDirectory(
self, "Select Directory")).replace("\\", "/")
if path == "":
return
self.line_import_source_folder.setText(path)
folder = path.rsplit("/", 1)[1]
self.line_import_save_folder.setText(folder)
print(path)
@pyqtSlot()
def on_btn_file_import_clicked(self):
path = self.line_import_source_folder.text()
account = self.line_import_account.text()
localFolder = self.line_import_save_folder.text()
if not os.path.isdir(path) or "" in [
path.strip(),
account.strip(),
localFolder.strip()
]:
QMessageBox.warning(
self, 'Incomplete information',
'Please fill all fields and make sure the provided path exists.',
QMessageBox.Ok)
return
path = path.replace("\\", "/")
if not path.endswith("/"):
path += "/"
self.label_progress_folder.setText("Processing Folder: " + localFolder)
mailTotal = 0
for file in os.listdir(path):
if file.endswith(".eml"):
mailTotal += 1
self.pBar_progress_import.setMinimum(0)
self.pBar_progress_import.setMaximum(mailTotal)
self.pBar_progress_import.setValue(0)
mailCount = 0
newMailCount = 0
failedCount = 0
for file in os.listdir(path):
if file.endswith(".eml"):
mailCount += 1
self.label_progress_email.setText("E-Mail: %s/%s" %
(mailCount, mailTotal))
self.pBar_progress_import.setValue(mailCount)
QCoreApplication.processEvents()
print("working on file", file)
try:
message = email.message_from_file(open(path + file, "r"))
except:
failedCount += 1
continue
header = self.getMessageHeader(message)
hash = self.createMailHash(header)
if self.checkMailExist(hash):
print(
"SKIPPING MAIL SKIPPING MAIL SKIPPING MAIL SKIPPING MAIL SKIPPING MAIL SKIPPING MAIL SKIPPING MAIL SKIPPING MAIL SKIPPING MAIL SKIPPING MAIL"
)
continue
newMailCount += 1
self.saveMailToDatabase(message, account, localFolder)
# get MailID from database
self.dbquery.prepare(
"SELECT MailID FROM mails WHERE MailHash=?")
self.dbquery.addBindValue(hash)
self.dbquery.exec_()
self.dbquery.next()
MailID = self.dbquery.value(0)
# generate savepath and save mail to file
savename = self.constructFilePath(MailID)
savepath = savename.rsplit("/", 1)[0]
if not os.path.exists(savepath):
os.makedirs(savepath)
shutil.copyfile(path + file, savename)
self.label_progress_folder.setText("Finished succesfully!")
self.label_progress_email.setText("%s new Mails, %s failed" %
(newMailCount, failedCount))
def constructFilePath(self, MailID):
self.dbquery.prepare(
"SELECT MailHash, account, folder FROM mails WHERE MailID=?")
self.dbquery.addBindValue(MailID)
self.dbquery.exec_()
self.dbquery.next()
hash = self.dbquery.value(0)
account = self.dbquery.value(1)
folder = self.dbquery.value(2)
print("acc", account)
print("folder", folder)
print("hash", hash)
return self.archiveBasePath + account + "/" + folder + "/" + hash + ".eml"
@pyqtSlot()
def on_btnResetView_clicked(self):
print("reseting")
self.line_filter_all.setText("")
self.line_filter_subject.setText("")
self.line_filter_from.setText("")
self.line_filter_to.setText("")
self.cBox_filter_cc.setChecked(True)
self.cBox_filter_bcc.setChecked(True)
self.line_filter_timestamp.setText("")
self.cBox_filter_attachment_yes.setChecked(True)
self.cBox_filter_attachment_no.setChecked(True)
self.line_filter_attachments.setText("")
self.cBox_filter_account.setCurrentIndex(0)
self.cBox_filter_folder.setCurrentIndex(0)
self.line_filter_message.setText("")
self.model_mails.setQuery(
"SELECT * from mails ORDER BY receive_time DESC")
def closeEvent(self, event):
self.database.commit()
self.database.close()
event.accept()
def __remove_filters(self):
query = QSqlQuery(self.db)
query.prepare("DELETE FROM filters WHERE pageid=?")
query.addBindValue(self.page.id)
query.exec_()
