def addItemButtons( self ): sender = self.sender() if (sender.text() == "Next"): if (self.itemNameEdit.text() == "" or self.itemBrandEdit.text() == "" or self.storeNameEdit.text() == ""): self.displayMessage( "Fill out all forms", "Error" ) else: self.itemName = self.itemNameEdit.text() self.itemBrand = self.itemBrandEdit.text() self.storeName = self.storeNameEdit.text() self.addItemInfoScreen() elif (sender.text() == "Add"): if (self.itemPrice.text() == "" or self.itemQuantity.text() == "" or \ self.itemQType.text() == "" or self.itemDate.text() == ""): self.displayMessage( "Fill out all forms", "Error" ) else: try: db = DBAccess.DBAccess() db.addStore( self.storeName ) db.addItem( self.itemName, self.itemBrand ) db.addPrice( self.itemName, self.itemBrand, float( self.itemPrice.text() ), self.itemQType.text(), float( self.itemQuantity.text() ), int( self.itemCouponUsed.isChecked() ), self.itemDate.text() ) self.lastDate = self.itemDate.text() self.setStatus( "Item Added Successfully" ) except: self.displayMessage( str( sys.exc_info()[ 0 ] ), "Database Error" ) finally: self.addItemScreen() else: self.status.clearMessage() self.setCentralText( "Check menu for options" )
def showMostOnSale( self ): db = DBAccess.DBAccess() mostRecorded = pd.read_sql( '''SELECT ITEMNAME, COUNT( ITEMNAME) AS CNT FROM PRICES GROUP BY ITEMNAME ORDER BY COUNT( * ) DESC LIMIT 3; ''', db.getDBConnection() ) pyplot.plot( mostRecorded[ 'ITEMNAME' ], mostRecorded[ 'CNT' ] ) pyplot.xlabel( "Item Name" ) pyplot.ylabel( "# of ads item was found in" ) pyplot.show()
def showMostValue( self ): db = DBAccess.DBAccess() mostValue = pd.read_sql( '''SELECT DISTINCT ITEMNAME AS ITEM, (PRICES.PRICE/PRICES.QUANTITY) AS VAL FROM PRICES ORDER BY (PRICES.PRICE/PRICES.QUANTITY) LIMIT 5; ''', db.getDBConnection() ) pyplot.plot( mostValue[ 'ITEM' ], mostValue[ 'VAL' ] ) pyplot.ylabel( "Cost per unit in $" ) pyplot.xlabel( "Item Name" ) pyplot.show()
def showMostDiverseStore( self ): db = DBAccess.DBAccess() mostDiverseStore = pd.read_sql( ''' SELECT BRAND, COUNT( * ) AS C FROM ( STORES INNER jOIN ITEMS ON BRAND=STORENAME ) GROUP BY BRAND ORDER BY COUNT( * ) DESC limit 4; ''', db.getDBConnection() ) pyplot.plot( mostDiverseStore[ 'BRAND' ], mostDiverseStore[ 'C' ] ) pyplot.ylabel( "# of unique items sold by store" ) pyplot.xlabel( "Store Name" ) pyplot.show()
def summaryScreen( self ): widget = QWidget() self.setCentralWidget( widget ) db = DBAccess.DBAccess() numItems = pd.read_sql( "SELECT DISTINCT ITEMNAME FROM PRICES;", db.getDBConnection() ) numPrices = pd.read_sql( "SELECT ITEMNAME FROM PRICES;", db.getDBConnection() ) mostValue = pd.read_sql( '''SELECT * FROM 'PRICES' ORDER BY (PRICES.PRICE/PRICES.QUANTITY) limit 1;''', db.getDBConnection() ) mostRecorded = pd.read_sql( '''SELECT ITEMNAME, COUNT( ITEMNAME) AS CNT FROM PRICES GROUP BY ITEMNAME ORDER BY COUNT( * ) DESC LIMIT 3; ''', db.getDBConnection() ) mostDiverseStore = pd.read_sql( ''' SELECT BRAND, COUNT( * ) AS C FROM ( STORES INNER jOIN ITEMS ON BRAND=STORENAME ) GROUP BY BRAND ORDER BY COUNT( * ) DESC; ''', db.getDBConnection() ) summary = "" summary += "Number of Unique Items in DB: " + str( numItems[ 'ITEMNAME' ].count() ) summary += "\nNumber of Prices Logged: " + str( numPrices[ 'ITEMNAME' ].count() ) summary += "\nMost value item ( Price/Quantity ): " + str( mostValue[ 'ITEMNAME' ][ 0 ] ) summary += "\nItem most on sale: " + str( mostRecorded[ 'ITEMNAME' ][ 0 ] ) + " (x" + \ str( mostRecorded[ 'CNT' ][ 0 ] ) + ")" summary += "\nMost diverse in-house products: " + str( mostDiverseStore[ 'BRAND' ][ 0 ] ) summaryLine = QHBoxLayout() summaryLine.addStretch( 1 ) summaryLine.addWidget( QLabel( summary ) ) summaryLine.addStretch( 1 ) screen = QVBoxLayout() screen.addStretch( 1 ) screen.addLayout( summaryLine ) screen.addStretch( 1 ) widget.setLayout( screen )
def GetDbAccessHandle(fileName): if fileName is None: fileName = Defaults.GetDefaultDbFile() return DBAccess(fileName)
def initDB( self ): db = DBAccess.DBAccess() db.initialize() self.status.showMessage( "Database initialized" ) self.setCentralText( "Use menu to add/search items to database" )