def init_best_before(self):
model = QSqlRelationalTableModel()
model.setTable("best_before")
model.setEditStrategy(QSqlRelationalTableModel.OnManualSubmit)
model.setRelation(1, QSqlRelation('nutrition', 'ndbno', 'desc'))
model.select()
#Nutrition table needs to be populated since otherwise only 256 rows are read
#And all inserts with ndbno > 100169 fail since they aren't found in nutrition
#table
nutri_model = model.relationModel(1)
while nutri_model.canFetchMore():
nutri_model.fetchMore()
self.bb_model = model
nutri_model = self.lv_keys.model()
#nutri_model = QSqlTableModel()
#nutri_model.setTable("nutrition")
##nutri_model.setRelation(2, QSqlRelation('nutrition', 'ndbno', 'desc'))
#nutri_model.setEditStrategy(QSqlRelationalTableModel.OnManualSubmit)
#nutri_model.setSort(1,Qt.AscendingOrder)
#nutri_model.select()
self.cb_bb_item.setModel(nutri_model)
self.cb_bb_item.setModelColumn(0)
self.best_before_model = model
self.tv_best_before.setModel(model)
self.tv_best_before.setSortingEnabled(True)
self.tv_best_before.sortByColumn(2, Qt.AscendingOrder)
self.tv_best_before.setItemDelegate(QSqlRelationalDelegate(self.tv_best_before))
self.tv_best_before.show()
#From Price
self.cb_price_item.setModel(nutri_model)
self.cb_price_item.setModelColumn(0)
#From Tag
self.cb_item_tag.setModel(nutri_model)
self.cb_item_tag.setModelColumn(0)
"""Updates Best before table"""
def update_bb():
print ("Updating Best Before")
if not self.bb_model.submitAll():
QMessageBox.critical(None, "Error updating Best Before",
"Couldn't update model: " +
self.bb_model.lastError().text())
"""Adds new data to best before table
update_bb also needs to be called"""
def add_bb():
print("Adding to BB")
ndbno = self._get_selected_ndbno(self.cb_bb_item.model() \
.record(self.cb_bb_item.currentIndex()))
print ("IDX:", self.cb_bb_item.currentIndex(),
ndbno)
row = self.bb_model.rowCount()
self.bb_model.insertRow(row)
#print ("NDBNO INDEX:", self.bb_model.fieldIndex("desc"))
r = self.bb_model.record()
#for i in range(r.count()):
#print ("{} => {}".format(i,r.fieldName(i)))
#for i in range(100000,100194):
#out_ndbno = self.bb_model.setData(self.bb_model.createIndex(row,
#self.bb_model.fieldIndex("desc")), i,
#Qt.EditRole)
#print ("{}? = {}".format(out_ndbno, i))
out_ndbno = self.bb_model.setData(self.bb_model.createIndex(row,
self.bb_model.fieldIndex("desc")), ndbno,
Qt.EditRole)
out_time = self.bb_model.setData(self.bb_model.createIndex(row,
self.bb_model.fieldIndex("time")),
self.de_bb.date(),
Qt.EditRole)
print ("NDBNO:", out_ndbno, "TIME:", out_time)
self.update_bb = update_bb
self.add_bb = add_bb
self.buttonBox_3.button(QDialogButtonBox.SaveAll).clicked.connect(self.update_bb)
self.buttonBox_3.button(QDialogButtonBox.Apply).clicked.connect(self.add_bb)
class PhoneLogDlg(QDialog):
FIRST, PREV, NEXT, LAST = range(4)
def __init__(self, parent=None):
super(PhoneLogDlg, self).__init__(parent)
callerLabel = QLabel("&Caller:")
self.callerEdit = QLineEdit()
callerLabel.setBuddy(self.callerEdit)
today = QDate.currentDate()
startLabel = QLabel("&Start:")
self.startDateTime = QDateTimeEdit()
startLabel.setBuddy(self.startDateTime)
self.startDateTime.setDateRange(today, today)
self.startDateTime.setDisplayFormat(DATETIME_FORMAT)
endLabel = QLabel("&End:")
self.endDateTime = QDateTimeEdit()
endLabel.setBuddy(self.endDateTime)
self.endDateTime.setDateRange(today, today)
self.endDateTime.setDisplayFormat(DATETIME_FORMAT)
topicLabel = QLabel("&Topic:")
topicEdit = QLineEdit()
topicLabel.setBuddy(topicEdit)
outcomeLabel = QLabel("&Outcome:")
self.outcomeComboBox = QComboBox()
outcomeLabel.setBuddy(self.outcomeComboBox)
firstButton = QPushButton()
firstButton.setIcon(QIcon(":/first.png"))
prevButton = QPushButton()
prevButton.setIcon(QIcon(":/prev.png"))
nextButton = QPushButton()
nextButton.setIcon(QIcon(":/next.png"))
lastButton = QPushButton()
lastButton.setIcon(QIcon(":/last.png"))
addButton = QPushButton("&Add")
addButton.setIcon(QIcon(":/add.png"))
deleteButton = QPushButton("&Delete")
deleteButton.setIcon(QIcon(":/delete.png"))
quitButton = QPushButton("&Quit")
quitButton.setIcon(QIcon(":/quit.png"))
if not MAC:
addButton.setFocusPolicy(Qt.NoFocus)
deleteButton.setFocusPolicy(Qt.NoFocus)
fieldLayout = QGridLayout()
fieldLayout.addWidget(callerLabel, 0, 0)
fieldLayout.addWidget(self.callerEdit, 0, 1, 1, 3)
fieldLayout.addWidget(startLabel, 1, 0)
fieldLayout.addWidget(self.startDateTime, 1, 1)
fieldLayout.addWidget(endLabel, 1, 2)
fieldLayout.addWidget(self.endDateTime, 1, 3)
fieldLayout.addWidget(topicLabel, 2, 0)
fieldLayout.addWidget(topicEdit, 2, 1, 1, 3)
fieldLayout.addWidget(outcomeLabel, 3, 0)
fieldLayout.addWidget(self.outcomeComboBox, 3, 1, 1, 3)
navigationLayout = QHBoxLayout()
navigationLayout.addWidget(firstButton)
navigationLayout.addWidget(prevButton)
navigationLayout.addWidget(nextButton)
navigationLayout.addWidget(lastButton)
fieldLayout.addLayout(navigationLayout, 4, 0, 1, 2)
buttonLayout = QVBoxLayout()
buttonLayout.addWidget(addButton)
buttonLayout.addWidget(deleteButton)
buttonLayout.addStretch()
buttonLayout.addWidget(quitButton)
layout = QHBoxLayout()
layout.addLayout(fieldLayout)
layout.addLayout(buttonLayout)
self.setLayout(layout)
self.model = QSqlRelationalTableModel(self)
self.model.setTable("calls")
self.model.setRelation(OUTCOMEID, QSqlRelation("outcomes", "id",
"name"))
self.model.setSort(STARTTIME, Qt.AscendingOrder)
self.model.select()
self.mapper = QDataWidgetMapper(self)
self.mapper.setSubmitPolicy(QDataWidgetMapper.ManualSubmit)
self.mapper.setModel(self.model)
self.mapper.setItemDelegate(QSqlRelationalDelegate(self))
self.mapper.addMapping(self.callerEdit, CALLER)
self.mapper.addMapping(self.startDateTime, STARTTIME)
self.mapper.addMapping(self.endDateTime, ENDTIME)
self.mapper.addMapping(topicEdit, TOPIC)
relationModel = self.model.relationModel(OUTCOMEID)
self.outcomeComboBox.setModel(relationModel)
self.outcomeComboBox.setModelColumn(relationModel.fieldIndex("name"))
self.mapper.addMapping(self.outcomeComboBox, OUTCOMEID)
self.mapper.toFirst()
firstButton.clicked.connect(lambda: self.saveRecord(PhoneLogDlg.FIRST))
prevButton.clicked.connect(lambda: self.saveRecord(PhoneLogDlg.PREV))
nextButton.clicked.connect(lambda: self.saveRecord(PhoneLogDlg.NEXT))
lastButton.clicked.connect(lambda: self.saveRecord(PhoneLogDlg.LAST))
addButton.clicked.connect(self.addRecord)
deleteButton.clicked.connect(self.deleteRecord)
quitButton.clicked.connect(self.done)
self.setWindowTitle("Phone Log")
def done(self, result=None):
self.mapper.submit()
QDialog.done(self, True)
def addRecord(self):
row = self.model.rowCount()
self.mapper.submit()
self.model.insertRow(row)
self.mapper.setCurrentIndex(row)
now = QDateTime.currentDateTime()
self.startDateTime.setDateTime(now)
self.endDateTime.setDateTime(now)
self.outcomeComboBox.setCurrentIndex(
self.outcomeComboBox.findText("Unresolved"))
self.callerEdit.setFocus()
def deleteRecord(self):
caller = self.callerEdit.text()
starttime = self.startDateTime.dateTime().toString(DATETIME_FORMAT)
if (QMessageBox.question(
self, "Delete",
"Delete call made by<br>{0} on {1}?".format(caller, starttime),
QMessageBox.Yes | QMessageBox.No) == QMessageBox.No):
return
row = self.mapper.currentIndex()
self.model.removeRow(row)
self.model.submitAll()
self.model.select()
if row + 1 >= self.model.rowCount():
row = self.model.rowCount() - 1
self.mapper.setCurrentIndex(row)
def saveRecord(self, where):
row = self.mapper.currentIndex()
self.mapper.submit()
if where == PhoneLogDlg.FIRST:
row = 0
elif where == PhoneLogDlg.PREV:
row = 0 if row <= 1 else row - 1
elif where == PhoneLogDlg.NEXT:
row += 1
if row >= self.model.rowCount():
row = self.model.rowCount() - 1
elif where == PhoneLogDlg.LAST:
row = self.model.rowCount() - 1
self.mapper.setCurrentIndex(row)
def init_price(self):
currencies = {"EUR": u"€", "HRK": u"kn"}
model = QSqlTableModel()
model.setTable("shop")
model.setEditStrategy(QSqlRelationalTableModel.OnManualSubmit)
model.select()
self.shop_model = model
price_model = QSqlRelationalTableModel()
price_model.setTable("price")
price_model.setEditStrategy(QSqlRelationalTableModel.OnManualSubmit)
price_model.setRelation(1, QSqlRelation('nutrition', 'ndbno', 'desc'))
price_model.setRelation(2, QSqlRelation('shop', 'id', 'name'))
#Nutrition table needs to be populated since otherwise only 256 rows are read
#And all inserts with ndbno > 100169 fail since they aren't found in nutrition
#table
nutri_model = price_model.relationModel(1)
while nutri_model.canFetchMore():
nutri_model.fetchMore()
self.price_model = price_model
self.price_model.select()
self.tv_price.setModel(price_model)
self.tv_price.setSortingEnabled(True)
self.tv_price.sortByColumn(1, Qt.AscendingOrder)
self.tv_price.setItemDelegate(QSqlRelationalDelegate(self.tv_price))
price_sort_model = QSqlQueryModel()
price_sort_model.setQuery(
"SELECT nutrition.ndbno, nutrition.desc, shop.name, " +
"last_updated, price, price/nutrition.package_weight*1000 as " +
" price_kg, nutrition.kcal/price as kcal_price_100, " +
"nutrition.protein/price as protein_price_100," +
" (nutrition.kcal*nutrition.package_weight/100)/price as " +
" kcal_price_package, " +
" (nutrition.protein*nutrition.package_weight/100)/price " +
" as protein_price_package, " + "lowered_price, lowered_untill, " +
"currency,comment, temporary FROM price " +
" JOIN shop ON shop.id == price.shop_id " +
" JOIN nutrition ON price.ndbno = nutrition.ndbno " +
" WHERE currency = 'EUR' "
" ORDER BY nutrition.desc, price")
proxy_model = QSortFilterProxyModel(self)
proxy_model.setSourceModel(price_sort_model)
proxy_model.setFilterKeyColumn(0)
self.price_proxy = proxy_model
#print (price_sort_model.lastError().text())
#print (price_sort_model.query().executedQuery())
self.tv_price_view.setModel(proxy_model)
self.tv_price_view.setSortingEnabled(True)
self.tv_price_view.horizontalHeader().setToolTip("protein/kcal_price "
"higher is better")
self.cb_shop.setModel(model)
self.cb_shop.setModelColumn(1)
#self.cb_shop.lineEdit().editingFinished.connect(self.add_shop)
model_currency = QStringListModel()
model_currency.setStringList(["EUR", "HRK"])
self.cb_currency.setModel(model_currency)
self.de_last_updated.setMaximumDate(QDate.currentDate())
self.de_last_updated.setDateTime(QDateTime.currentDateTime())
def update_price():
print("Updating price info")
if not price_model.submitAll():
QMessageBox.critical(
None, "Error updating price:",
"Couldn't update model: " + price_model.lastError().text())
def reset_price():
print("Resetting price info")
price_model.revertAll()
"""Sets suffix symbols for prices based on currency"""
def currency_changed(currency):
symbol = currencies[currency]
self.dsp_price.setSuffix(symbol)
self.dsp_low_price.setSuffix(symbol)
"""Adds new Shop to DB"""
def add_shop():
print(self.cb_shop.currentText())
row = self.shop_model.rowCount()
self.shop_model.insertRow(row)
self.shop_model.setData(self.shop_model.createIndex(row, 1),
self.cb_shop.currentText(), Qt.EditRole)
self.shop_model.submitAll()
def add_price():
shop_id = self.shop_model.record(self.cb_shop.currentIndex()) \
.field("id").value()
print("Shop ID:", shop_id, self.cb_shop.currentText())
record = self.cb_price_item.model() \
.record(self.cb_price_item.currentIndex())
ndbno = self._get_selected_ndbno(record)
package_weight = record.field("package_weight").value()
slices = record.field("num_of_slices").value()
last_updated = self.de_last_updated.date()
price = self.dsp_price.value()
lowered_price = self.dsp_low_price.value() if \
self.dsp_low_price.value() > 0 else None
lowered_untill = self.de_low_untill.date() if \
self.de_low_untill.date() > QDate.currentDate() else None
currency = self.cb_currency.currentText()
comment = self.le_comment.text() if \
len(self.le_comment.text()) > 3 else None
temporary = self.cb_temporary.isChecked()
print("ITEM:", self.cb_price_item.currentText(), ndbno)
print("Weight:", package_weight, " Slices: ", slices)
print("LU:", last_updated)
print("PRICE:", price, " Low Price:", lowered_price)
print("LOWU:", lowered_untill)
print("CU:", currency)
print("COMMENT:", comment)
print("Temp:", temporary)
print("NDBNO:", ndbno, type(ndbno))
print("SHOP ID:", shop_id, type(shop_id))
row = self.price_model.rowCount()
#print ("ROW:", row, self.price_model.relationModel(1).rowCount())
self.price_model.insertRow(row)
def add_data(idx, data):
return self.price_model.setData(
self.price_model.createIndex(row, idx), data, Qt.EditRole)
#for i in range(100000,100194):
#for i in self.session.query(LocalNutrition.ndbno).filter(LocalNutrition.ndbno
#< 100000).order_by(LocalNutrition.ndbno):
#out_ndbno = add_data(1, QVariant(i[0]))
#print ("{}? = {}".format(out_ndbno, i[0]))
#for i in range(100000,100194):
##for i in self.session.query(LocalNutrition.ndbno).filter(LocalNutrition.ndbno
##< 100000).order_by(LocalNutrition.ndbno):
#out_ndbno = add_data(1, QVariant(i))
#print ("{}? = {}".format(out_ndbno, i))
#FIXME: Why is ndbno found in nutrtition only if it lower then 100169
#relation = self.price_model.relation(1)
#print(relation, relation.displayColumn(), relation.indexColumn())
#print (relation.dictionary.contains(ndbno))
#return
add_data(1, ndbno)
add_data(2, shop_id)
add_data(3, last_updated)
add_data(4, price)
if lowered_price is not None and lowered_untill is not None:
add_data(5, lowered_price)
add_data(6, lowered_untill)
add_data(7, currency)
if comment is not None:
add_data(8, comment)
add_data(9, temporary)
self.price_model.submitAll()
self.update_price = update_price
self.reset_price = reset_price
self.currency_changed = currency_changed
self.buttonBox_4.button(QDialogButtonBox.SaveAll).clicked.connect(
self.update_price)
self.buttonBox_4.button(QDialogButtonBox.Reset).clicked.connect(
self.reset_price)
self.cb_currency.currentTextChanged.connect(self.currency_changed)
self.pb_add_shop.pressed.connect(add_shop)
self.pb_add_price.pressed.connect(add_price)