def report_month_sales(file, dbase):
for_date = date = datetime.date.today()
query = QSqlQuery("SELECT employee.firstname AS firstname, employee.lastname AS lastname, employee.middlename AS middlename, \
count(orders.id) AS qnt, sum(orders.price) AS price \
FROM employee INNER JOIN orders ON employee.id = orders.employee_id \
WHERE orders.regdate >= \"{}\"\
GROUP BY employee.firstname, employee.lastname, employee.middlename".format(for_date.strftime("%Y.%m.01")), dbase)
if not query.isActive():
return print(query.lastError().text())
with open("reports/emp_sales.html", "r") as f:
html = f.read()
rows = []
qnt_total = 0
price_total = 0
while query.next():
rows.append("<tr><td align='center'>{}</td><td align='center'>{}</td><td align='center'>{}</td></tr>".format(
"{} {}. {}.".format(query.value("lastname"), query.value("firstname")[0], query.value("middlename")[0]),
int(query.value("qnt")), int(query.value("price"))))
qnt_total += int(query.value("qnt"))
price_total += int(query.value("price"))
rows.append("<b><tr><td align='right'>{}</td><td align='center'>{}</td><td align='center'>{}</td></tr></b>".format(
"Итого:", qnt_total, price_total))
date = datetime.date.today().strftime("%d.%m.%Y")
html = html.format(for_month = for_date.strftime("%m.%Y"), date = date, rows = "\n".join(rows))
tmpfile = "/tmp/report{}.html".format(random.randrange(1000000))
with open(tmpfile, "w") as f:
f.write(html)
subprocess.call(["wkhtmltopdf", tmpfile, file])
def report_day_sales(for_date, file, dbase):
query = QSqlQuery("SELECT shop.name AS name, sum(order_detail.quantity) AS qnt, sum(detail.price * order_detail.quantity) AS price \
FROM shop INNER JOIN orders ON shop.id = orders.shop_id \
INNER JOIN order_detail ON order_detail.order_id = orders.id \
INNER JOIN detail ON detail.id = order_detail.detail_id \
WHERE orders.regdate = \"{}\"\
GROUP BY shop.name".format(for_date.toString("yyyy.MM.dd")), dbase)
if not query.isActive():
return print(query.lastError().text())
with open("reports/goods.html", "r") as f:
html = f.read()
rows = []
qnt_total = 0
price_total = 0
while query.next():
rows.append("<tr><td align='center'>{}</td><td align='center'>{}</td><td align='center'>{}</td></tr>".format(
query.value("name"), int(query.value("qnt")), int(query.value("price"))))
qnt_total += int(query.value("qnt"))
price_total += int(query.value("price"))
rows.append("<b><tr><td align='right'>{}</td><td align='center'>{}</td><td align='center'>{}</td></tr></b>".format(
"Итого:", qnt_total, price_total))
date = datetime.date.today().strftime("%d.%m.%Y")
html = html.format(for_day = for_date.toString("dd.MM.yyyy"), date = date, rows = "\n".join(rows))
tmpfile = "/tmp/report{}.html".format(random.randrange(1000000))
with open(tmpfile, "w") as f:
f.write(html)
subprocess.call(["wkhtmltopdf", tmpfile, file])
def addButtonClicked(self):
shop = self.shopModel.record(self.list.currentIndex().row())
query = QSqlQuery("SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as name \
FROM detail WHERE NOT(detail.id IN (SELECT detail_id FROM shop_detail \
WHERE shop_id={}))".format(shop.value("id")))
details = {}
while query.next():
details[query.value("name")] = query.value("id")
if not details:
return QMessageBox.warning(None, "Ошибка добавления",
"Не удалось добавить новый товар на склад: все возможные товары уже добавлены.")
choice, ok = QInputDialog.getItem(None, "Товар", "Укажите товар:",
list(details.keys()), 0, False)
if not ok: return
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:",
1, 1)
if not ok: return
detail_id = details[choice]
shop_id = shop.value("id")
query = QSqlQuery("INSERT INTO shop_detail (shop_id, detail_id, quantity) \
VALUES ({}, {}, {})".format(shop_id, detail_id, qnt))
if not query.isActive():
print(query.lastError().text())
self.setShopIndex(self.list.currentIndex().row())
self.table.selectionModel().clearSelection()
def _start_query(self):
self.setCursor(Qt.WaitCursor)
QCoreApplication.processEvents()
# noinspection PyBroadException
try:
self.model.clear()
QCoreApplication.processEvents()
line = self.text.text().strip()
if not line:
self.model.setQuery("SELECT media_id from Media;")
# self.model.setQuery("SELECT DISTINCT media_id from ManifoldItems NATURAL JOIN Manifolds WHERE ready;")
return
if line == '@import':
_source_dir = QFileDialog.getExistingDirectory(
self, "Select a directory to import", "",
QFileDialog.DontResolveSymlinks | QFileDialog.ReadOnly)
_source_dir = os.path.abspath(_source_dir)
assert os.path.isdir(_source_dir)
self._logger.info(f"Starting ingestion of {_source_dir}")
self._py_db.ingest_file_directory(
_source_dir
) # NOTE: this will freeze the GUI for a long time!
self._py_db.notify_bg_manifold_build()
self._py_db.thumbs_load()
self._logger.info(f"Ingestion finished. ")
_source_dir_url_like = 'file:' + pathname2url(
_source_dir) + '%'
query = QSqlQuery()
query.prepare(
'SELECT media_id FROM MediaLocations WHERE url LIKE ? ORDER BY url'
)
query.bindValue(0, _source_dir_url_like)
self.model.setQuery(query)
return
results_id, inserted = self._py_db.search(line,
n=1000,
search_k=-1)
query_s = (f'SELECT media_id FROM results.ResultsMediaFiltered '
f'WHERE results_id = {results_id:d} ORDER BY rank ASC')
query = QSqlQuery()
query.exec(query_s)
if not query.isActive():
self._logger.debug(f'results query: {query.lastQuery():s}')
self._logger.error(
f'results query error: {query.lastError().text():s}')
self.model.setQuery(query)
except Exception:
import traceback
traceback.print_exc()
finally:
self.setCursor(Qt.ArrowCursor)
def deleteButtonClicked(self):
if not self.table.currentIndex().isValid(): return
detail = self.detailModel.record(self.table.currentIndex().row())
shop = self.shopModel.record(self.list.currentIndex().row())
query = QSqlQuery("DELETE FROM shop_detail WHERE \
shop_id={} AND detail_id={} LIMIT 1".format(
shop.value("id"), detail.value("id")))
if not query.isActive():
print(query.lastError().text())
self.setShopIndex(self.list.currentIndex().row())
def deleteButtonClicked(self):
if not self.orderTable.currentIndex().isValid(): return
detail = self.detailModel.record(self.orderTable.currentIndex().row())
order = self.form.currentRecord()
query = QSqlQuery("DELETE FROM order_detail WHERE \
order_id={} AND detail_id={} LIMIT 1".format(
order.value("id"), detail.value("id")))
query.exec_()
if not query.isActive():
print(query.lastError().text())
self.form.update()
def editButtonClicked(self):
detail = self.detailModel.record(self.table.currentIndex().row())
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:",
detail.value("qnt"), 0)
if not ok: return
shop = self.shopModel.record(self.list.currentIndex().row())
if qnt > 0:
query = QSqlQuery("UPDATE shop_detail SET quantity={} \
WHERE shop_id={} AND detail_id={}".format(qnt,
shop.value("id"), detail.value("id")))
else:
query = QSqlQuery("DELETE FROM shop_detail WHERE \
shop_id={} AND detail_id={} LIMIT 1".format(
shop.value("id"), detail.value("id")))
if not query.isActive():
print(query.lastError().text())
self.setShopIndex(self.list.currentIndex().row())
def editButtonClicked(self):
detail = self.detailModel.record(self.orderTable.currentIndex().row())
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:",
detail.value("qnt"), 0)
if not ok: return
order = self.form.currentRecord()
if qnt > 0:
query = QSqlQuery("UPDATE order_detail SET quantity={} \
WHERE order_id={} AND detail_id={}".format(qnt,
order.value("id"), detail.value("id")))
else:
query = QSqlQuery("DELETE FROM order_detail WHERE \
order_id={} AND detail_id={} LIMIT 1".format(
order.value("id"), detail.value("id")))
query.exec_()
if not query.isActive():
print(query.lastError().text())
self.form.update()
def addButtonClicked(self):
order = self.form.currentRecord()
query = QSqlQuery("SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as name \
FROM detail WHERE NOT(detail.id IN (SELECT detail_id FROM order_detail \
WHERE order_id={}))".format(order.value("id")))
details = {}
while query.next():
details[query.value("name")] = query.value("id")
if not details:
return QMessageBox.warning(None, "Ошибка добавления",
"Не удалось добавить новый товар к заказу: все возможные товары уже добавлены.")
choice, ok = QInputDialog.getItem(None, "Товар", "Укажите товар:",
list(details.keys()), 0, False)
if not ok: return
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:", 1, 1)
if not ok: return
detail_id = details[choice]
order_id = order.value("id")
query = QSqlQuery("INSERT INTO order_detail (order_id, detail_id, quantity) \
VALUES ({}, {}, {})".format(order_id, detail_id, qnt))
if not query.isActive():
print(query.lastError().text())
self.form.update()
class Query:
"""A prepared SQL query."""
def __init__(self, querystr, forward_only=True):
"""Prepare a new SQL query.
Args:
querystr: String to prepare query from.
forward_only: Optimization for queries that will only step forward.
Must be false for completion queries.
"""
self.query = QSqlQuery(QSqlDatabase.database())
log.sql.debug('Preparing SQL query: "{}"'.format(querystr))
ok = self.query.prepare(querystr)
self._check_ok('prepare', ok)
self.query.setForwardOnly(forward_only)
def __iter__(self):
if not self.query.isActive():
raise BugError("Cannot iterate inactive query")
rec = self.query.record()
fields = [rec.fieldName(i) for i in range(rec.count())]
rowtype = collections.namedtuple( # type: ignore[misc]
'ResultRow', fields)
while self.query.next():
rec = self.query.record()
yield rowtype(*[rec.value(i) for i in range(rec.count())])
def _check_ok(self, step, ok):
if not ok:
query = self.query.lastQuery()
error = self.query.lastError()
msg = 'Failed to {} query "{}": "{}"'.format(
step, query, error.text())
raise_sqlite_error(msg, error)
def _bind_values(self, values):
for key, val in values.items():
self.query.bindValue(':{}'.format(key), val)
if any(val is None for val in self.bound_values().values()):
raise BugError("Missing bound values!")
def run(self, **values):
"""Execute the prepared query."""
log.sql.debug('Running SQL query: "{}"'.format(self.query.lastQuery()))
self._bind_values(values)
log.sql.debug('query bindings: {}'.format(self.bound_values()))
ok = self.query.exec_()
self._check_ok('exec', ok)
return self
def run_batch(self, values):
"""Execute the query in batch mode."""
log.sql.debug('Running SQL query (batch): "{}"'.format(
self.query.lastQuery()))
self._bind_values(values)
db = QSqlDatabase.database()
ok = db.transaction()
self._check_ok('transaction', ok)
ok = self.query.execBatch()
try:
self._check_ok('execBatch', ok)
except Error:
# Not checking the return value here, as we're failing anyways...
db.rollback()
raise
ok = db.commit()
self._check_ok('commit', ok)
def value(self):
"""Return the result of a single-value query (e.g. an EXISTS)."""
if not self.query.next():
raise BugError("No result for single-result query")
return self.query.record().value(0)
def rows_affected(self):
return self.query.numRowsAffected()
def bound_values(self):
return self.query.boundValues()
conn.setHostName("127.0.0.1")
conn.setUserName("postgres")
conn.setPassword("12345")
conn.setPort("5432")
"""
# Открываем базу данных
conn = QSqlDatabase.addDatabase('QSQLITE')
# Определяем путь до базы данных
conn.setDatabaseName('test.sqlite3')
if conn.open():
# Выполняем действия с базой
query = QSqlQuery()
query.exec("select * from vendors")
results = []
if query.isActive():
query.first()
while query.isValid():
results.append(query.value('name'))
query.next()
for el in results:
print(el)
else:
# Выводим текст ошибки
print(conn.lastError().text())
conn.close()
bin()
class Query:
"""A prepared SQL query."""
def __init__(self, querystr, forward_only=True):
"""Prepare a new SQL query.
Args:
querystr: String to prepare query from.
forward_only: Optimization for queries that will only step forward.
Must be false for completion queries.
"""
self.query = QSqlQuery(QSqlDatabase.database())
log.sql.debug('Preparing SQL query: "{}"'.format(querystr))
ok = self.query.prepare(querystr)
self._check_ok('prepare', ok)
self.query.setForwardOnly(forward_only)
def __iter__(self):
if not self.query.isActive():
raise BugError("Cannot iterate inactive query")
rec = self.query.record()
fields = [rec.fieldName(i) for i in range(rec.count())]
rowtype = collections.namedtuple('ResultRow', fields)
while self.query.next():
rec = self.query.record()
yield rowtype(*[rec.value(i) for i in range(rec.count())])
def _check_ok(self, step, ok):
if not ok:
query = self.query.lastQuery()
error = self.query.lastError()
msg = 'Failed to {} query "{}": "{}"'.format(step, query,
error.text())
raise_sqlite_error(msg, error)
def _bind_values(self, values):
for key, val in values.items():
self.query.bindValue(':{}'.format(key), val)
if any(val is None for val in self.bound_values().values()):
raise BugError("Missing bound values!")
def run(self, **values):
"""Execute the prepared query."""
log.sql.debug('Running SQL query: "{}"'.format(
self.query.lastQuery()))
self._bind_values(values)
log.sql.debug('query bindings: {}'.format(self.bound_values()))
ok = self.query.exec_()
self._check_ok('exec', ok)
return self
def run_batch(self, values):
"""Execute the query in batch mode."""
log.sql.debug('Running SQL query (batch): "{}"'.format(
self.query.lastQuery()))
self._bind_values(values)
db = QSqlDatabase.database()
ok = db.transaction()
self._check_ok('transaction', ok)
ok = self.query.execBatch()
try:
self._check_ok('execBatch', ok)
except Error:
# Not checking the return value here, as we're failing anyways...
db.rollback()
raise
ok = db.commit()
self._check_ok('commit', ok)
def value(self):
"""Return the result of a single-value query (e.g. an EXISTS)."""
if not self.query.next():
raise BugError("No result for single-result query")
return self.query.record().value(0)
def rows_affected(self):
return self.query.numRowsAffected()
def bound_values(self):
return self.query.boundValues()
class Query:
"""A prepared SQL query."""
def __init__(self,
database: Database,
querystr: str,
forward_only: bool = True) -> None:
"""Prepare a new SQL query.
Args:
database: The Database object on which to operate.
querystr: String to prepare query from.
forward_only: Optimization for queries that will only step forward.
Must be false for completion queries.
"""
self._database = database
self.query = QSqlQuery(database.qt_database())
log.sql.vdebug(f'Preparing: {querystr}') # type: ignore[attr-defined]
ok = self.query.prepare(querystr)
self._check_ok('prepare', ok)
self.query.setForwardOnly(forward_only)
def __iter__(self) -> Iterator[Any]:
if not self.query.isActive():
raise BugError("Cannot iterate inactive query")
rec = self.query.record()
fields = [rec.fieldName(i) for i in range(rec.count())]
rowtype = collections.namedtuple( # type: ignore[misc]
'ResultRow', fields)
while self.query.next():
rec = self.query.record()
yield rowtype(*[rec.value(i) for i in range(rec.count())])
def _check_ok(self, step: str, ok: bool) -> None:
if not ok:
query = self.query.lastQuery()
error = self.query.lastError()
msg = f'Failed to {step} query "{query}": "{error.text()}"'
raise_sqlite_error(msg, error)
def _bind_values(self, values: Mapping[str, Any]) -> Dict[str, Any]:
for key, val in values.items():
self.query.bindValue(f':{key}', val)
bound_values = self.bound_values()
if None in bound_values.values():
raise BugError("Missing bound values!")
return bound_values
def run(self, **values: Any) -> 'Query':
"""Execute the prepared query."""
log.sql.debug(self.query.lastQuery())
bound_values = self._bind_values(values)
if bound_values:
log.sql.debug(f' {bound_values}')
ok = self.query.exec()
self._check_ok('exec', ok)
return self
def run_batch(self, values: Mapping[str, MutableSequence[Any]]) -> None:
"""Execute the query in batch mode."""
log.sql.debug(f'Running SQL query (batch): "{self.query.lastQuery()}"')
self._bind_values(values)
db = self._database.qt_database()
ok = db.transaction()
self._check_ok('transaction', ok)
ok = self.query.execBatch()
try:
self._check_ok('execBatch', ok)
except Error:
# Not checking the return value here, as we're failing anyways...
db.rollback()
raise
ok = db.commit()
self._check_ok('commit', ok)
def value(self) -> Any:
"""Return the result of a single-value query (e.g. an EXISTS)."""
if not self.query.next():
raise BugError("No result for single-result query")
return self.query.record().value(0)
def rows_affected(self) -> int:
"""Return how many rows were affected by a non-SELECT query."""
assert not self.query.isSelect(), self
assert self.query.isActive(), self
rows = self.query.numRowsAffected()
assert rows != -1
return rows
def bound_values(self) -> Dict[str, Any]:
return self.query.boundValues()
def dropEvent(self, a0: QDropEvent) -> None:
data = a0.mimeData()
action = a0.dropAction()
if action == Qt.IgnoreAction:
a0.ignore()
return
if not data.hasFormat('text/uri-list'):
a0.ignore()
return
a0.accept()
self.setCursor(Qt.WaitCursor)
self.model.clear()
QCoreApplication.processEvents()
ul = data.data('text/uri-list').data().decode().splitlines()
ul = map(str.strip, ul)
ul = filter(lambda _uri: not _uri.startswith('#'), ul)
ul = [
'file:/' +
_uri[len('file:///'):] if _uri.startswith('file:///') else _uri
for _uri in ul
]
ul = list(ul)
file_binds = []
dir_binds = []
# Ingest file and dirs from received uri-list
for uri in ul:
if uri.startswith('file:'):
pathname = url2pathname(uri[len('file:'):])
if os.path.isdir(pathname):
self._logger.info(f'RECURSIVELY ADDING {pathname}')
out = self._py_db.ingest_file_directory(pathname)
dir_binds.append(f'{uri}%')
self._logger.debug(
f'ingest_file_directory({pathname}) -> {out}')
continue
self._logger.info(f'ADDING {uri}')
media_id, flags, ex = self._py_db.try_ingest_url(uri)
if ex is not None:
self._logger.exception(f'FAILING {uri}: {ex}')
else:
file_binds.append(uri)
self._logger.debug(f'{media_id=} {flags=} {ex=}')
self._py_db.notify_bg_manifold_build()
self._py_db.thumbs_load()
# Build WHERE expression with file and dir binds
conditions = []
file_placeholders = ', '.join('?' * len(file_binds))
if file_placeholders:
file_placeholders = f'url IN ({file_placeholders})'
conditions.append(file_placeholders)
conditions.extend(['url LIKE ?'] * len(dir_binds))
# Prepare query and bind values for file and dirs
query = QSqlQuery()
query.prepare(
f'SELECT media_id FROM MediaLocations WHERE {" OR ".join(conditions)} ORDER BY url'
)
i = -1
for i, bind in enumerate(file_binds):
query.bindValue(i, bind)
for j, bind in zip(range(i + 1, i + 1 + len(dir_binds)), dir_binds):
query.bindValue(j, bind)
query.exec()
if not query.isActive():
self._logger.debug(f'results query: {query.lastQuery():s}')
self._logger.error(
f'results query error: {query.lastError().text():s}')
self.model.setQuery(query)
self.setCursor(Qt.ArrowCursor)
