def contextMenuSlot(self, posPoint):
""" Manages right click context menu showing item and location ID numbers"""
#FIRTS HACK TO PREVENT FANTOM DOUBLE RIGHT CLICK
if self.prevContextMenuPos == posPoint:
return
self.prevContextMenuPos = posPoint
itemID = tradesdbmanager.getItemIdFromName(self.itemName)
cRow = self.table.currentRow(); #
locationName = self.table.item(cRow, self.LOCATION_COLUMN).text()
locationID = tradesdbmanager.getLocationIdFromName(locationName)
# setup dialog box
mb = QtGui.QMessageBox(self)
mb.setWindowTitle(self.itemName)
msgStr = "trading "+ self.itemName+ " at \n"+ locationName+ "\n"+ "itemID : "+ str(itemID)+"\nlocationID : "+ str(locationID)
mb.setText(msgStr)
mb.exec_()
def loadData(self):
self.table.clearContents()
self.setupTable()
self.table.setSortingEnabled(False)
with sqlite3.connect(dbinfo.DATABASE_FILE_NAME) as con:
cur = con.cursor()
# CALCULATE DISPAYED HISTORY MAX AND MIN VALUES
currentEpoch = int(time.time())
# NOW
maxHistoryEpoch = currentEpoch - self.toolbar.getMaxHistory() * SECONDS_PER_DAY
minHistoryEpoch = currentEpoch - self.toolbar.getMinHistory() * SECONDS_PER_DAY
# minEpoch = currentEpoch - self.daySelector.value() * SECONDS_PER_DAY
ttn = dbinfo.TRADE_TABLE_NAME
itn = dbinfo.ITEM_TYPE_TABLE_NAME
ltn = dbinfo.LOCATION_TABLE_NAME
# get location ID, if specified in toolbar combo box
locName = self.toolbar.getCurrentLocation()
locID = tradesdbmanager.getLocationIdFromName(locName)
# FIND ALL ITEM IDs TRADED WITHIN TIME FRAME
SQL_COMM = """SELECT DISTINCT {0}.typeID, {1}.typeName
FROM {0} INNER JOIN {1}
ON {0}.typeID = {1}.typeID
WHERE
{0}.transactionDateTime > {2} AND {0}.transactionDateTime < {3}""".format(
ttn, itn, maxHistoryEpoch, minHistoryEpoch
)
# IF LOCATION FILTER. ADD IT TO QUERY
if locID:
SQL_COMM += " AND {0}.stationID = {1}".format(ttn, locID)
SQL_COMM += " GROUP BY {0}.typeID".format(itn)
cur.execute(SQL_COMM)
items = cur.fetchall()
numItems = len(items)
self.totalProfit = 0
insertCounter = 0 # some items are not inserted. keep track of this
# for each item type that has been traded
for i in range(0, numItems):
item = items[i]
typeID = item[0]
typeName = item[1]
includeBuys = self.toolbar.buyCB.isChecked()
includeSales = self.toolbar.sellCB.isChecked()
# ---------------------------------------
# GET BOUGHT ITEMS INFORMATION
# ---------------------------------------
numBought = 0
priceBought = 0.0
if includeBuys: # IF TICKBOX AGREES...
SQL_COMM = """ SELECT SUM(quantity), SUM(price*quantity)
FROM {0}
WHERE
typeID = {1}
AND
transactionDateTime > {2} AND transactionDateTime < {3}""".format(
ttn, typeID, maxHistoryEpoch, minHistoryEpoch
)
# IF LOCATION FILTER SPECIFIED
if locID:
SQL_COMM += " AND {0}.stationID = {1}".format(ttn, locID)
SQL_COMM += """ AND transactionType={}""".format(dbinfo.TRANSACTION_TYPE_BUY)
cur.execute(SQL_COMM)
buyInfo = cur.fetchone()
numBought = 0 if buyInfo[0] == None else buyInfo[0]
priceBought = 0 if buyInfo[1] == None else buyInfo[1]
# ------------------------------------------------
# GET SALES INFORMATION FOR THIS ITEM TYPE
# ------------------------------------------------
numSold = 0
priceSold = 0.0
if includeSales:
SQL_COMM = """ SELECT SUM(quantity), SUM(price*quantity)
FROM {0}
WHERE
typeID = {1}
AND
transactionDateTime > {2} AND transactionDateTime < {3}""".format(
ttn, typeID, maxHistoryEpoch, minHistoryEpoch
)
if locID:
SQL_COMM += " AND {0}.stationID = {1}".format(ttn, locID)
SQL_COMM += """ AND transactionType={}""".format(dbinfo.TRANSACTION_TYPE_SELL)
cur.execute(SQL_COMM)
sellInfo = cur.fetchone()
numSold = 0 if sellInfo[0] == None else sellInfo[0]
priceSold = 0 if sellInfo[1] == None else sellInfo[1]
# SKIP THIS ITEM IF NOT BOUGHT AND SOLD
# THIS CAN BE TRUE WHEN CHECKBOXES FOR SALES/PURCHASES ARE UNTICKED
if numBought == 0 and numSold == 0:
continue
# CALCULATE BUY/SELL PRICE STATISTICS
profit = priceSold + priceBought # !! buy price is negative
self.totalProfit += priceSold + priceBought
avgBuyPrice = -priceBought / numBought if numBought > 0 else 0
avgSellPrice = priceSold / numSold if numSold > 0 else 0
# profit percentage
profitPct = 0
if (avgSellPrice != 0) and (avgBuyPrice != 0):
delta = avgSellPrice - avgBuyPrice
profitPct = 100 * delta / avgBuyPrice
# ADD ITEMS TO TABLE
self.table.setRowCount(insertCounter + 1)
self.table.setItem(insertCounter, 0, nti.NumericTableItem(profit, "{:,.2f}"))
itemName = QtGui.QTableWidgetItem(typeName)
itemName.setToolTip(typeName)
itemName.setFlags(QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEnabled)
self.table.setItem(insertCounter, 1, itemName)
if includeBuys:
self.table.setItem(insertCounter, 2, nti.NumericTableItem(numBought, "{:,}"))
self.table.setItem(insertCounter, 3, nti.NumericTableItem(avgBuyPrice, "{:,.2f}"))
if includeSales:
self.table.setItem(insertCounter, 4, nti.NumericTableItem(numSold, "{:,}"))
self.table.setItem(insertCounter, 5, nti.NumericTableItem(avgSellPrice, "{:,.2f}"))
if profitPct != 0:
self.table.setItem(insertCounter, 6, nti.NumericTableItem(profitPct, "{:,.2f}"))
insertCounter += 1
self.table.setSortingEnabled(True)
historyDays = self.toolbar.getMaxHistory() - self.toolbar.getMinHistory()
self.setWindowTitle("Total profit in {0} days : {1:,} ISK".format(historyDays, self.totalProfit))
