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))