def modelChanged(self):
# Fetch all selected records
while self.model().canFetchMore():
self.model().fetchMore()
newCount = self.model().rowCount()
# Show updated coins count
sql = "SELECT count(*) FROM coins"
query = QSqlQuery(sql, self.model().database())
query.first()
totalCount = query.record().value(0)
labelText = self.tr("%d/%d coins") % (newCount, totalCount)
self.listCountLabel.setText(labelText)
# Restore selected row
if self.selectedRowId is not None:
idIndex = self.model().fieldIndex('id')
startIndex = self.model().index(0, idIndex)
indexes = self.proxyModel.match(startIndex, Qt.DisplayRole,
self.selectedRowId, 1,
Qt.MatchExactly)
if indexes:
self.selectRow(indexes[0].row())
else:
self.selectedRowId = None
def GenerateWeatherDescription(db, weatherCode):
categories = [
"Low Cloud Amount", "Visibility Kilometres", "Low Cloud Height",
"Weather", "Remarks Mission", "Remarks Weather"
]
query = QSqlQuery(db)
pos = 0
help = 0
weatherDescription = ""
for c in weatherCode:
qryStr = "select description from wetter where category = '{0}' and code = '{1}' limit 1".format(
categories[pos - help], c)
query.exec_(qryStr)
query.first()
fn = query.value(0)
if pos <= 5:
weatherDescription += categories[pos] + ': ' + fn
if pos < 5:
weatherDescription += '\n'
else:
weatherDescription += '; ' + fn
if pos >= 5:
help += 1
pos += 1
return weatherDescription
def setKgNameAndCode(self):
query = QSqlQuery(self.dbm.db)
qryStr = u"select CASE WHEN katastralgemeinde IS NULL AND katastralgemeindenummer IS NULL THEN '--' ELSE katastralgemeindenummer || ' - ' || katastralgemeinde END AS kg FROM fundort WHERE fundortnummer = '{0}'".format(
self.siteNumber)
query.exec_(qryStr)
query.first()
self.uiCadastralCommunityEdit.setText(query.value(0))
def loadRecordByKeyAttribute(self, attribute, value):
#self.model.setFilter(attribute + " = '" + value + "'")
#self.model.select()
# self.mapper.toFirst()
query = QSqlQuery(self.dbm.db)
#qryStr = "select {0} from film where {0} = '{1}' limit 1".format(attribute, value)
#qryStr = "SELECT rowid FROM film WHERE {0} = '{1}' limit 1".format(attribute, value)
qryStr = "SELECT" \
" (SELECT COUNT(*)" \
" FROM film AS t2" \
" WHERE t2.rowid < t1.rowid" \
" ) + (" \
" SELECT COUNT(*)" \
" FROM film AS t3" \
" WHERE t3.rowid = t1.rowid AND t3.rowid < t1.rowid" \
" ) AS rowNum" \
" FROM film AS t1" \
" WHERE {0} = '{1}'" \
" ORDER BY t1.rowid ASC".format(attribute, value)
query.exec_(qryStr)
#QMessageBox.warning(None, "Test", str(query.size()) + ',' + str(query.numRowsAffected()))
query.first()
fn = query.value(0)
if fn is not None:
self.loadRecordById(fn)
return True
else:
# Film does not exist
QMessageBox.warning(None, "Film Nummer", str("Der Film mit der Nummer {0} existiert nicht!".format(value)))
return False
def getMappingStats(self):
self.imageStatsDict = {}
query = QSqlQuery(self.dbm.db)
qryStr = "SELECT COUNT(*) AS anzahl_kartiert, MAX(bildnummer_nn) AS letzte_bildnummer, MIN(bildnummer_nn) AS erste_bildnummer FROM luftbild_{0}_cp WHERE filmnummer = '{1}'".format(
self.orientation, self.currentFilmNumber)
query.exec_(qryStr)
query.first()
statFields = [
"anzahl_kartiert", "letzte_bildnummer", "erste_bildnummer"
]
if query.value(0) is not None:
for key in statFields:
idx = query.record().indexOf(key)
if query.isNull(idx):
self.imageStatsDict[key] = 0
else:
self.imageStatsDict[key] = query.value(idx)
#QMessageBox.warning(None, u"Film Nummer", u"{0}, {1}, {2}".format(self.imageStatsDict["anzahl_kartiert"],self.imageStatsDict["letzte_bildnummer"],self.imageStatsDict["erste_bildnummer"]))
else:
self.imageStatsDict["anzahl_kartiert"] = 0
self.imageStatsDict["letzte_bildnummer"] = 0
self.imageStatsDict["erste_bildnummer"] = 0
QMessageBox.warning(
None, u"Film Nummer",
u"Für den Film mit der Nummer {0} sind noch keine Bilder kartiert!"
.format(self.currentFilmNumber))
def modelChanged(self):
# Fetch all selected records
while self.model().canFetchMore():
self.model().fetchMore()
newCount = self.model().rowCount()
# Show updated coins count
sql = "SELECT count(*) FROM coins"
query = QSqlQuery(sql, self.model().database())
query.first()
totalCount = query.record().value(0)
labelText = self.tr("%d/%d coins") % (newCount, totalCount)
self.listCountLabel.setText(labelText)
# Restore selected row
if self.selectedRowId is not None:
idIndex = self.model().fieldIndex('id')
startIndex = self.model().index(0, idIndex)
indexes = self.proxyModel.match(startIndex, Qt.DisplayRole,
self.selectedRowId, 1, Qt.MatchExactly)
if indexes:
self.selectRow(indexes[0].row())
else:
self.selectedRowId = None
def isFilm(self, filmNumber):
# check if filmNumber is a filmNumber in film Table
query = QSqlQuery(self.dbm.db)
query.prepare("SELECT weise FROM film WHERE filmnummer = '{0}'".format(
filmNumber))
query.exec_()
query.first()
return query.value(0)
def SiteHasFindspot(db, siteNumber):
query = QSqlQuery(db)
query.prepare(
u"SELECT COUNT(*) FROM fundstelle WHERE fundortnummer = '{0}'".format(
siteNumber))
query.exec_()
query.first()
return query.value(0)
def getFolderNameSite(self, siteNumber):
query = QSqlQuery(self.dbm.db)
#qryStr = u"SELECT trim(katastralgemeinde) || ' ' || trim(katastralgemeindenummer) || '.' || substr('000' || fundortnummer_nn_legacy, -3, 3) AS folderName FROM fundort f WHERE f.fundortnummer='{0}'".format(siteNumber)
query.prepare(
u"SELECT land || '\\' || CASE WHEN land = 'AUT' THEN replace(replace(replace(replace(lower(trim(katastralgemeinde)), '.',''), '-', ' '), '(', ''), ')', '') || ' ' ELSE '' END || substr('000000' || fundortnummer_nn, -6, 6) AS folderName FROM fundort f WHERE f.fundortnummer='{0}'"
.format(siteNumber))
query.exec_()
query.first()
return query.value(0)
def SitesHaveFindspots(db, siteNumbers):
sites = u", ".join(u"'{0}'".format(siteNumber)
for siteNumber in siteNumbers)
query = QSqlQuery(db)
query.prepare(
u"SELECT COUNT(*) FROM fundstelle WHERE fundortnummer IN ({0})".format(
sites))
query.exec_()
query.first()
return query.value(0)
def videoInDb(self, video_id):
'''
Checks if db contains video_id
'''
query = QSqlQuery()
query.prepare("""SELECT count(1) FROM videos WHERE video_id = (?)""")
query.addBindValue(video_id)
query.exec()
query.first()
return query.value(0)
def playlistInDb(self, playlist_id):
'''
Checks if db contains playlist_id
'''
query = QSqlQuery()
query.prepare(
"""SELECT count(1) FROM playlists WHERE playlist_id = (?)""")
query.addBindValue(playlist_id)
query.exec()
query.first()
return query.value(0)
def channelInDb(self, channel_id):
'''
Checks if db contains channel_id
'''
query = QSqlQuery()
query.prepare(
"""SELECT count(1) FROM channels WHERE channel_id = (?)""")
query.addBindValue(channel_id)
query.exec()
query.first()
return query.value(0)
def getCountryCode(self):
query = QSqlQuery(self.dbm.db)
#qryStr = "SELECT code FROM osm_boundaries WHERE within(MakePoint({0}, {1}, 4312), geometry)".format(self.imageCenterPoint.x(), self.imageCenterPoint.y())
qryStr = "SELECT code FROM osm_boundaries WHERE intersects(Transform(MakePoint({0}, {1}, 4312), 4326), geometry) AND ROWID IN (SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'osm_boundaries' AND search_frame = Transform(MakePoint({0}, {1}, 4312), 4326))".format(
self.imageCenterPoint.x(), self.imageCenterPoint.y())
query.exec_(qryStr)
query.first()
if query.value(0) is None:
return 'INT'
else:
return query.value(0)
def getChannelTitle(self, channel_id):
if self.channelInDb(channel_id):
query = QSqlQuery()
query.prepare(
"""SELECT channel_title FROM channels WHERE channel_id = (?)"""
)
query.addBindValue(channel_id)
query.exec()
query.first()
return query.value(0)
else:
return ""
def statusBarUpdate(self):
displayedVids = self.videoTable.model().rowCount()
totalVids = 0
query = QSqlQuery("SELECT count(*) FROM videos")
query.exec()
query.first()
totalVids = query.value(0)
msg = f"[{displayedVids} of {totalVids} videos"
if self.videoTableHasSelection():
msg += f", {len(self.videoTable.selectionModel().selectedRows(0))} selected"
msg += "]"
self.statusBar.showMessage(msg)
def DbHasTable(db, table):
query = QSqlQuery(db)
query.prepare(
"SELECT name FROM sqlite_master WHERE type='table' AND name='{0}'".
format(table))
query.exec_()
query.first()
#QMessageBox.information(None, "info", "{}".format(query.value(0) == table))
if query.value(0) == table:
return True
else:
return False
def IsFilm(db, filmNumber):
"""
Checks if filmNumber is a filmNumber in film Table
:param db: Database
:param filmNumber: 10 digit filmNumber
:return:
"""
query = QSqlQuery(db)
query.prepare(u"SELECT COUNT(*) FROM film WHERE filmnummer = '{0}'".format(
filmNumber))
query.exec_()
query.first()
return query.value(0)
def tagInDb(self, tag_name, yt_tags=False):
'''
Checks if db contains tag
'''
table_name = "yt_tags" if yt_tags else "tags"
query = QSqlQuery()
query.prepare(
"""SELECT count(1) FROM {} WHERE tag_name = (?)""".format(
table_name))
query.addBindValue(tag_name)
query.exec()
query.first()
return query.value(0)
def get_info(self, image_date):
query = QSqlQuery(self.db)
query.setForwardOnly(True)
query.prepare(
'SELECT copyright_info FROM copyright WHERE image_date = ?;')
query.addBindValue(image_date)
if not query.exec_():
self.error.emit('Error getting copyright info',
query.lastError().text())
return ''
query.first()
copyright_info = query.value(0).toString()
return copyright_info
def replay_info(self, uid):
ret = {}
ret["game_end"] = time.time()
with DbContext(self.db):
query = QSqlQuery(self.db)
queryStr = ("""
SELECT game_featuredMods.gamemod, gameType, filename, gameName, host, login, playerId, AI, team
FROM `game_stats`
LEFT JOIN game_player_stats
ON `game_player_stats`.`gameId` = game_stats.id
LEFT JOIN table_map
ON `game_stats`.`mapId` = table_map.id
LEFT JOIN login
ON login.id = `game_player_stats`.`playerId`
LEFT JOIN game_featuredMods
ON `game_stats`.`gameMod` = game_featuredMods.id
WHERE game_stats.id = {}
""".format(uid))
query.exec_(queryStr)
if query.size() == 0:
return None
RV = self.ReplayInfoQueryValues
ret["num_players"] = query.size()
query.first()
ret["featured_mod"] = str(_qv(query, RV.GAMEMOD))
ret["game_type"] = int(_qv(query, RV.GAMETYPE) or 0)
ret["title"] = str(_qv(query, RV.GAMENAME).encode('utf-8'))
ret["featured_mod_versions"] = self._featured_mod_versions(
_qv(query, RV.GAMEMOD))
mapname = str(_qv(query, RV.FILENAME))
ret["mapname"] = os.path.splitext(os.path.basename(mapname))[0]
ret["complete"] = True
query.previous()
teams = {}
while next(query):
team = int(_qv(query, RV.TEAM))
name = str(_qv(query, RV.LOGIN))
isAi = int(_qv(query, RV.AI))
if int(_qv(query, RV.HOST)) == int(_qv(query, RV.PLAYER_ID)):
ret["host"] = name
if isAi == 0:
if team not in teams:
teams[team] = []
teams[team].append(name)
ret["teams"] = teams
return ret
def __init__(self, collection):
super(CollectionDescription, self).__init__(collection)
self.db = collection.db
if 'description' not in self.db.tables():
self.create(collection)
query = QSqlQuery("SELECT * FROM description", self.db)
query.first()
record = query.record()
self.title = record.value('title')
self.description = record.value('description')
self.author = record.value('author')
def __init__(self, collection):
super().__init__(collection)
self.db = collection.db
if 'description' not in self.db.tables():
self.create(collection)
query = QSqlQuery("SELECT * FROM description", self.db)
query.first()
record = query.record()
self.title = record.value('title')
self.description = record.value('description')
self.author = record.value('author')
def get_item_progress(item_id):
query = QSqlQuery()
query.prepare(
"SELECT rename, derivatives, copy, complete FROM items WHERE id=:item_id"
)
query.bindValue(":item_id", item_id)
query.exec_()
query.first()
progress = {
"rename": query.value(0),
"derivatives": query.value(1),
"copy": query.value(2),
"complete": query.value(3)
}
return progress
def getFilmInfo(self):
filmFields = [
"filmnummer_hh_jjjj_mm", "filmnummer_nn", "filmnummer",
"anzahl_bilder", "form1", "form2", "weise", "kammerkonstante",
"datum_ersteintrag", "datum_aenderung", "projekt", "copyright"
]
self.currentFilmInfoDict = {}
query = QSqlQuery(self.dbm.db)
qryStr = "SELECT {0} FROM film WHERE filmnummer = '{1}'".format(
", ".join(filmFields), self.currentFilmNumber)
query.exec_(qryStr)
query.first()
if query.value(0) is not None:
for key in filmFields:
value = query.value(query.record().indexOf(key))
self.currentFilmInfoDict[key] = value
if self.currentFilmInfoDict["weise"] == u"schräg":
self.orientation = "schraeg"
self.isOblique = True
else:
self.orientation = "senk"
self.isOblique = False
# QMessageBox.information(None, "kkInfo0", f'type: {type(self.currentFilmInfoDict["kammerkonstante"])}')
if isinstance(
self.currentFilmInfoDict["kammerkonstante"], str
) and not self.currentFilmInfoDict["kammerkonstante"]:
QMessageBox.warning(
None, u"Film Nummer",
u"Der senkrecht Film mit der Nummer {0} hat keine Kammerkonstante (Wird für die Berechnung des Bildmaßstabs und in Folge des Footprints benötigt.) Geben Sie im Film Dialog die Kammerkonstante an, um korrekte Footprints zu erhalten."
.format(self.currentFilmNumber))
return False
else:
if not self.currentFilmInfoDict["kammerkonstante"] > 0:
QMessageBox.warning(
None, u"Film Nummer",
u"Der senkrecht Film mit der Nummer {0} hat eine Kammerkonstante (={1}), die für die Berechnung des Bildmaßstabs und in Folge des Footprints ungeeignet ist! Geben Sie im Film Dialog die Kammerkonstante an, um korrekte Footprints zu erhalten. Falls Bildmittelpunkte bereits gesetzt sind (mit einem Maßstab = 0) müssen auch diese neu kartiert werden!"
.format(
self.currentFilmNumber,
self.currentFilmInfoDict["kammerkonstante"]))
return False
return True
else:
QMessageBox.warning(
None, u"Film Nummer",
u"Der Film mit der Nummer {0} existiert nicht!".format(
self.currentFilmNumber))
return False
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 getPreviewImage(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 getImageTitle(self, img_id):
query = QSqlQuery(self.database())
query.prepare("SELECT title FROM photos WHERE id=?")
query.addBindValue(img_id)
query.exec_()
if query.first():
return query.record().value(0)
def isExist(self, record):
fields = [
'title', 'value', 'unit', 'country', 'period', 'year', 'mint',
'mintmark', 'type', 'series', 'subjectshort', 'status', 'material',
'quality', 'paydate', 'payprice', 'saller', 'payplace', 'saledate',
'saleprice', 'buyer', 'saleplace', 'variety', 'obversevar',
'reversevar', 'edgevar'
]
filterParts = [field + '=?' for field in fields]
sqlFilter = ' AND '.join(filterParts)
db = self.database()
query = QSqlQuery(db)
query.prepare("SELECT count(*) FROM coins WHERE id<>? AND " +
sqlFilter)
query.addBindValue(record.value('id'))
for field in fields:
query.addBindValue(record.value(field))
query.exec_()
if query.first():
count = query.record().value(0)
if count > 0:
return True
return False
def get_total_records(self):
try:
q = QSqlQuery("SELECT count(*) FROM connections", self.db)
if q.exec_() and q.first():
r = q.value(0)
except Exception as e:
print("db, get_total_records() error:", e)
def __exportOneQuery(self,
viewColumns,
query: QtSql.QSqlQuery,
sheetName='newsheet'):
vcs = viewColumns
self.__curRow = 0
# 添加一个sheet页
sheet = self.workbook.add_sheet(sheetName)
for col, vc in enumerate(vcs):
sheet.write(self.__curRow, col, vc.header, self.style_header)
self.__curRow += 1
bz = query.first()
aligns = self.__getAligns(vcs)
chars = [0] * len(vcs)
while bz:
for col, vc in enumerate(vcs):
rec = query.record()
vStr = vc.displayString(rec.value(col))
l = len(vStr) if vStr else 0
if l > chars[col]:
chars[col] = l
tempAlign = self.style_detail.alignment
self.style_detail.alignment = aligns[col]
sheet.write(self.__curRow, col, vStr, self.style_detail)
self.style_detail.alignment = tempAlign
self.__curRow += 1
bz = query.next()
for col, vc in enumerate(vcs):
sheet.col(col).width = 256 * chars[col]
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 _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 addNewSharding(self, siteNumber):
self.initalLoad = True
self.siteNumber = siteNumber
# get new sharding number
query = QSqlQuery(self.dbm.db)
qryStr = "SELECT CASE WHEN max(begehung) IS NULL THEN 1 ELSE max(begehung)+1 END begehungNeu FROM begehung WHERE fundortnummer='{0}'".format(
self.siteNumber)
query.exec_(qryStr)
query.first()
self.shardingNumber = query.value(0)
self.model = QSqlRelationalTableModel(self, self.dbm.db)
self.model.setTable("begehung")
self.model.setFilter("fundortnummer='{0}'".format(self.siteNumber))
res = self.model.select()
#self.model.submitAll()
while (self.model.canFetchMore()):
self.model.fetchMore()
row = self.model.rowCount()
#QMessageBox.information(None, "begehung", "{0}".format(row))
self.model.insertRow(row)
#QMessageBox.information(None, "begehung", "{0}".format(self.model.rowCount()))
self.setupMapper()
self.mapper.toLast()
self.addMode = True
self.startEditMode()
# self.mapper.submit()
# self.model.insertRow(row)
# self.mapper.setCurrentIndex(row)
self.uiSiteNumberEdit.setText(self.siteNumber)
self.uiShardingNumberEdit.setText(str(self.shardingNumber))
now = QDate.currentDate()
self.uiShardingDate.setDate(now)
self.setKgNameAndCode()
#QMessageBox.warning(None, self.tr(u"Neu"), self.tr(u"{0}, {1}".format(siteNumber,nn)))
self.initalLoad = False
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 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 __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 isExist(self, record):
fields = ['title', 'value', 'unit', 'country', 'period', 'year',
'mint', 'mintmark', 'type', 'series', 'subjectshort',
'status', 'material', 'quality', 'paydate', 'payprice',
'seller', 'payplace', 'saledate', 'saleprice', 'buyer',
'saleplace', 'variety', 'obversevar', 'reversevar',
'edgevar']
filterParts = [field + '=?' for field in fields]
sqlFilter = ' AND '.join(filterParts)
db = self.database()
query = QSqlQuery(db)
query.prepare("SELECT count(*) FROM coins WHERE id<>? AND " + sqlFilter)
query.addBindValue(record.value('id'))
for field in fields:
query.addBindValue(record.value(field))
query.exec_()
if query.first():
count = query.record().value(0)
if count > 0:
return True
return False
def merge(self, fileName):
db = QSqlDatabase.addDatabase('QSQLITE', 'merge')
db.setDatabaseName(fileName)
if not db.open():
print(db.lastError().text())
QMessageBox.critical(self.parent(),
self.tr("Merge collections"),
self.tr("Can't open collection"))
return
settings = CollectionSettings(db)
if self.settings['Type'] != version.AppName:
QMessageBox.critical(self.parent(),
self.tr("Merge collections"),
self.tr("Collection %s in wrong format %s") % (fileName, version.AppName))
if int(settings['Version']) != CollectionSettings.Default['Version']:
QMessageBox.critical(self.parent(),
self.tr("Merge collections"),
self.tr("Source collection %s in old format %d.\n(Try to open it before merging.)") % (fileName, int(settings['Version'])))
return
if settings['Password'] != cryptPassword():
dialog = PasswordDialog(settings, self.parent())
result = dialog.exec_()
if result == QDialog.Rejected:
return False
query = QSqlQuery("SELECT COUNT(id) FROM coins", db)
query.first()
count = query.record().value(0)
progressDlg = Gui.ProgressDialog(self.tr("Inserting records"),
self.tr("Cancel"), count, self.parent())
big_query = QSqlQuery("""SELECT coins.title AS 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", "obversedesign",
"obversedesigner", "reversedesign", "reversedesigner",
"subject", "defect",
"storage", "features", "createdat", "updatedat", "quantity", "url",
"barcode",
coins.image AS image, images.image AS images_image,
obverseimg, obverseimg.image AS obverseimg_image, obverseimg.title AS obverseimg_title,
reverseimg, reverseimg.image AS reverseimg_image, reverseimg.title AS reverseimg_title,
edgeimg, edgeimg.image AS edgeimg_image, edgeimg.title AS edgeimg_title,
photo1, photo1.image AS photo1_image, photo1.title AS photo1_title,
photo2, photo2.image AS photo2_image, photo2.title AS photo2_title,
photo3, photo3.image AS photo3_image, photo3.title AS photo3_title,
photo4, photo4.image AS photo4_image, photo4.title AS photo4_title
FROM coins
LEFT OUTER JOIN images ON coins.image=images.id
LEFT OUTER JOIN photos AS obverseimg ON coins.obverseimg=obverseimg.id
LEFT OUTER JOIN photos AS reverseimg ON coins.reverseimg=reverseimg.id
LEFT OUTER JOIN photos AS edgeimg ON coins.edgeimg=edgeimg.id
LEFT OUTER JOIN photos AS photo1 ON coins.photo1=photo1.id
LEFT OUTER JOIN photos AS photo2 ON coins.photo2=photo2.id
LEFT OUTER JOIN photos AS photo3 ON coins.photo3=photo3.id
LEFT OUTER JOIN photos AS photo4 ON coins.photo4=photo4.id""", db)
_model = QSqlTableModel(db=self.db)
_model.setTable('coins')
_model.select()
while big_query.next():
progressDlg.step()
if progressDlg.wasCanceled():
break
record = big_query.record()
record.setNull('id') # remove ID value from record
for field in ['obverseimg', 'reverseimg', 'edgeimg',
'photo1', 'photo2', 'photo3', 'photo4']:
value = record.value(field + '_image')
if value:
query = QSqlQuery(self.db)
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 + '_image'))
record.remove(record.indexOf(field + '_title'))
value = record.value('images_image')
if value:
query = QSqlQuery(self.db)
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('images_image'))
_model.insertRecord(-1, record)
_model.submitAll()
progressDlg.reset()
db.close()
QMessageBox.warning(self.parent(), self.tr("Merge collections"),
self.tr("The application will need to restart now"))
self.parent().restart()
def prepareMenu(self):
self.listWidget = QListWidget(self)
filters = self.filters.copy()
appliedValues = []
columnFilters = None
revert = False
if self.fieldid in filters.keys():
columnFilters = filters.pop(self.fieldid)
for filter_ in columnFilters.filters():
if filter_.isRevert():
revert = True
appliedValues.append(filter_.value)
hasBlanks = False
columnType = self.model.columnType(self.fieldid)
if self.model.columnName(self.fieldid) == 'year':
filtersSql = self.filtersToSql(filters.values())
if filtersSql:
filtersSql = 'WHERE ' + filtersSql
sql = "SELECT DISTINCT %s FROM coins %s" % (self.columnName, filtersSql)
query = QSqlQuery(sql, self.db)
while query.next():
icon = None
if query.record().isNull(0):
data = None
else:
orig_data = query.record().value(0)
data = str(orig_data)
label = data
try:
year = int(orig_data)
if year < 0:
label = "%d BC" % -year
except ValueError:
pass
if not data:
hasBlanks = True
continue
item = CustomSortListWidgetItem()
item.setData(Qt.DisplayRole, label)
item.setData(Qt.UserRole, data)
item.setData(Qt.UserRole + 1, orig_data)
if data in appliedValues:
if revert:
item.setCheckState(Qt.Checked)
else:
item.setCheckState(Qt.Unchecked)
else:
if revert:
item.setCheckState(Qt.Unchecked)
else:
item.setCheckState(Qt.Checked)
self.listWidget.addItem(item)
self.listWidget.sortItems()
elif columnType == Type.Text or columnType in Type.ImageTypes:
dataFilter = BlankFilter(self.columnName).toSql()
blanksFilter = DataFilter(self.columnName).toSql()
filtersSql = self.filtersToSql(filters.values())
sql = "SELECT 1 FROM coins WHERE " + filtersSql
if filtersSql:
sql += ' AND '
# Get blank row count
blank_sql = sql + blanksFilter + " LIMIT 1"
query = QSqlQuery(blank_sql, self.db)
if query.first():
hasBlanks = True
# Get not blank row count
not_blank_sql = sql + dataFilter + " LIMIT 1"
query = QSqlQuery(not_blank_sql, self.db)
if query.first():
if columnType in Type.ImageTypes:
label = self.tr("(Images)")
elif columnType == Type.Text:
label = self.tr("(Text)")
else:
label = self.tr("(Data)")
item = QListWidgetItem(label,
type=FilterMenuButton.DataType)
item.setData(Qt.UserRole, label)
item.setCheckState(Qt.Checked)
if columnFilters and columnFilters.hasData():
item.setCheckState(Qt.Unchecked)
self.listWidget.addItem(item)
elif columnType == Type.Status:
filtersSql = self.filtersToSql(filters.values())
if filtersSql:
filtersSql = 'WHERE ' + filtersSql
sql = "SELECT DISTINCT %s FROM coins %s ORDER BY %s ASC" % (
self.columnName, filtersSql, self.columnName)
query = QSqlQuery(sql, self.db)
while query.next():
value = query.record().value(0)
label = Statuses[value]
item = StatusSortListWidgetItem(label)
item.setData(Qt.UserRole, value)
if self.settings['show_filter_icons']:
icon = statusIcon(value)
item.setIcon(icon)
if value in appliedValues:
if revert:
item.setCheckState(Qt.Checked)
else:
item.setCheckState(Qt.Unchecked)
else:
if revert:
item.setCheckState(Qt.Unchecked)
else:
item.setCheckState(Qt.Checked)
self.listWidget.addItem(item)
self.listWidget.sortItems()
elif columnType == Type.Denomination:
filtersSql = self.filtersToSql(filters.values())
if filtersSql:
filtersSql = 'WHERE ' + filtersSql
sql = "SELECT DISTINCT %s FROM coins %s" % (self.columnName, filtersSql)
query = QSqlQuery(sql, self.db)
while query.next():
icon = None
if query.record().isNull(0):
data = None
else:
orig_data = query.record().value(0)
data = str(orig_data)
label, _ = numberWithFraction(data, self.settings['convert_fraction'])
if not data:
hasBlanks = True
continue
item = CustomSortListWidgetItem()
item.setData(Qt.DisplayRole, label)
item.setData(Qt.UserRole, data)
item.setData(Qt.UserRole + 1, orig_data)
if data in appliedValues:
if revert:
item.setCheckState(Qt.Checked)
else:
item.setCheckState(Qt.Unchecked)
else:
if revert:
item.setCheckState(Qt.Unchecked)
else:
item.setCheckState(Qt.Checked)
self.listWidget.addItem(item)
self.listWidget.sortItems()
else:
filtersSql = self.filtersToSql(filters.values())
if filtersSql:
filtersSql = 'WHERE ' + filtersSql
sql = "SELECT DISTINCT %s FROM coins %s" % (self.columnName, filtersSql)
query = QSqlQuery(sql, self.db)
while query.next():
icon = None
if query.record().isNull(0):
data = None
else:
orig_data = query.record().value(0)
data = str(orig_data)
if self.settings['show_filter_icons']:
icon = self.reference.getIcon(self.columnName, data)
if not data:
hasBlanks = True
continue
item = QListWidgetItem()
item.setData(Qt.DisplayRole, orig_data)
item.setData(Qt.UserRole, data)
if icon:
item.setIcon(icon)
if data in appliedValues:
if revert:
item.setCheckState(Qt.Checked)
else:
item.setCheckState(Qt.Unchecked)
else:
if revert:
item.setCheckState(Qt.Unchecked)
else:
item.setCheckState(Qt.Checked)
self.listWidget.addItem(item)
self.listWidget.sortItems()
item = QListWidgetItem(self.tr("(Select all)"),
type=FilterMenuButton.SelectAllType)
item.setData(Qt.UserRole, self.tr("(Select all)"))
item.setCheckState(Qt.Checked)
self.listWidget.insertItem(0, item)
if hasBlanks:
item = QListWidgetItem(self.tr("(Blanks)"),
type=FilterMenuButton.BlanksType)
item.setData(Qt.UserRole, self.tr("(Blanks)"))
item.setCheckState(Qt.Checked)
if revert:
if columnFilters and not columnFilters.hasBlank():
item.setCheckState(Qt.Unchecked)
else:
if columnFilters and columnFilters.hasBlank():
item.setCheckState(Qt.Unchecked)
self.listWidget.addItem(item)
self.listWidget.itemChanged.connect(self.itemChanged)
self.searchBox = QLineEdit(self)
self.searchBox.setPlaceholderText(self.tr("Filter"))
self.searchBox.textChanged.connect(self.applySearch)
self.buttonBox = QDialogButtonBox(Qt.Horizontal)
self.buttonBox.addButton(QDialogButtonBox.Ok)
self.buttonBox.addButton(QDialogButtonBox.Cancel)
self.buttonBox.accepted.connect(self.apply)
self.buttonBox.rejected.connect(self.menu().hide)
layout = QVBoxLayout()
layout.addWidget(self.searchBox)
layout.addWidget(self.listWidget)
layout.addWidget(self.buttonBox)
widget = QWidget(self)
widget.setLayout(layout)
widgetAction = QWidgetAction(self)
widgetAction.setDefaultWidget(widget)
self.menu().clear()
self.menu().addAction(widgetAction)
# Fill items
if self.listWidget.count() > 1:
self.itemChanged(self.listWidget.item(1))
def prepareMenu(self):
self.listWidget = QListWidget(self)
item = QListWidgetItem(self.tr("(Select all)"), self.listWidget,
FilterMenuButton.SelectAllType)
item.setData(Qt.UserRole, self.tr("(Select all)"))
item.setCheckState(Qt.PartiallyChecked)
self.listWidget.addItem(item)
filters = self.filters.copy()
appliedValues = []
columnFilters = None
revert = False
if self.fieldid in filters.keys():
columnFilters = filters.pop(self.fieldid)
for filter_ in columnFilters.filters():
if filter_.isRevert():
revert = True
appliedValues.append(filter_.value)
hasBlanks = False
columnType = self.model.columnType(self.fieldid)
if columnType == Type.Text or columnType in Type.ImageTypes:
dataFilter = BlankFilter(self.columnName).toSql()
blanksFilter = DataFilter(self.columnName).toSql()
filtersSql = self.filtersToSql(filters.values())
sql = "SELECT count(*) FROM coins WHERE " + filtersSql
if filtersSql:
sql += ' AND '
# Get blank row count
query = QSqlQuery(sql + blanksFilter, self.db)
query.first()
blanksCount = query.record().value(0)
# Get not blank row count
query = QSqlQuery(sql + dataFilter, self.db)
query.first()
dataCount = query.record().value(0)
if dataCount > 0:
if columnType in Type.ImageTypes:
label = self.tr("(Images)")
elif columnType == Type.Text:
label = self.tr("(Text)")
else:
label = self.tr("(Data)")
item = QListWidgetItem(label, self.listWidget,
FilterMenuButton.DataType)
item.setData(Qt.UserRole, label)
item.setCheckState(Qt.Checked)
if columnFilters and columnFilters.hasData():
item.setCheckState(Qt.Unchecked)
self.listWidget.addItem(item)
if blanksCount > 0:
hasBlanks = True
elif self.model.columnType(self.fieldid) == Type.Status:
filtersSql = self.filtersToSql(filters.values())
if filtersSql:
filtersSql = 'WHERE ' + filtersSql
sql = "SELECT DISTINCT %s FROM coins %s" % (self.columnName, filtersSql)
if self.settings['sort_filter']:
sql += " ORDER BY %s ASC" % self.columnName
query = QSqlQuery(sql, self.db)
while query.next():
value = query.record().value(0)
label = Statuses[value]
item = QListWidgetItem(label, self.listWidget)
item.setData(Qt.UserRole, value)
if label in appliedValues:
if revert:
item.setCheckState(Qt.Checked)
else:
item.setCheckState(Qt.Unchecked)
else:
if revert:
item.setCheckState(Qt.Unchecked)
else:
item.setCheckState(Qt.Checked)
self.listWidget.addItem(item)
else:
filtersSql = self.filtersToSql(filters.values())
if filtersSql:
filtersSql = 'WHERE ' + filtersSql
sql = "SELECT DISTINCT %s FROM coins %s" % (self.columnName, filtersSql)
if self.settings['sort_filter']:
sql += " ORDER BY %s ASC" % self.columnName
query = QSqlQuery(sql, self.db)
while query.next():
if query.record().isNull(0):
label = None
else:
label = str(query.record().value(0))
if not label:
hasBlanks = True
continue
item = QListWidgetItem(label, self.listWidget)
item.setData(Qt.UserRole, label)
if label in appliedValues:
if revert:
item.setCheckState(Qt.Checked)
else:
item.setCheckState(Qt.Unchecked)
else:
if revert:
item.setCheckState(Qt.Unchecked)
else:
item.setCheckState(Qt.Checked)
self.listWidget.addItem(item)
if hasBlanks:
item = QListWidgetItem(self.tr("(Blanks)"), self.listWidget,
FilterMenuButton.BlanksType)
item.setData(Qt.UserRole, self.tr("(Blanks)"))
item.setCheckState(Qt.Checked)
if revert:
if columnFilters and not columnFilters.hasBlank():
item.setCheckState(Qt.Unchecked)
else:
if columnFilters and columnFilters.hasBlank():
item.setCheckState(Qt.Unchecked)
self.listWidget.addItem(item)
self.listWidget.itemChanged.connect(self.itemChanged)
self.searchBox = QLineEdit(self)
self.searchBox.setPlaceholderText(self.tr("Filter"))
self.searchBox.textChanged.connect(self.applySearch)
self.buttonBox = QDialogButtonBox(Qt.Horizontal)
self.buttonBox.addButton(QDialogButtonBox.Ok)
self.buttonBox.addButton(QDialogButtonBox.Cancel)
self.buttonBox.accepted.connect(self.apply)
self.buttonBox.rejected.connect(self.menu().hide)
layout = QVBoxLayout(self)
layout.addWidget(self.searchBox)
layout.addWidget(self.listWidget)
layout.addWidget(self.buttonBox)
widget = QWidget(self)
widget.setLayout(layout)
widgetAction = QWidgetAction(self)
widgetAction.setDefaultWidget(widget)
self.menu().clear()
self.menu().addAction(widgetAction)
# Fill items
self.itemChanged(item)
def getTotalCount(self):
sql = "SELECT count(*) FROM coins"
query = QSqlQuery(sql, self.db)
query.first()
return query.record().value(0)
