def getTipster(year=None, month=None): if year is not None: date = str(year) + "-" + str(month) sql = 'select tipster.name, sport.name, ' \ '(SELECT count(*) from bet as b1 WHERE b1.result in (1, 4) ' \ 'and b1.tipster = bet.tipster and b1.sport=bet.sport and b1.date LIKE "' + date + '%") as acierto, ' \ '(SELECT count(*) from bet as b1 WHERE b1.result in (2, 5) and b1.tipster = bet.tipster ' \ 'and b1.sport=bet.sport and b1.date LIKE "' + date + '%") as fallo, (SELECT SUM(profit) ' \ 'from bet as b1 WHERE b1.result <> 0 and b1.tipster = bet.tipster and b1.sport=bet.sport and b1.date LIKE "' + date + '%") as prof, count(*), ' \ 'SUM(bet), avg(stake), avg(quota) from bet, tipster, sport WHERE bet.tipster=tipster.id ' \ 'and bet.sport=sport.id and bet.date LIKE "' + date + '%" GROUP BY bet.tipster,bet.sport ' else: sql = 'SELECT tipster.name, sport.name, (SELECT count(*) FROM bet AS b1 WHERE b1.result IN ' \ '(1, 4) AND b1.tipster = bet.tipster AND b1.sport=bet.sport) AS acierto, ' \ '(SELECT count(*) FROM bet AS b1 WHERE b1.result IN (2, 5) AND ' \ 'b1.tipster = bet.tipster AND b1.sport=bet.sport) AS fallo, (SELECT SUM(profit) ' \ 'FROM bet AS b1 WHERE b1.result <> 0 AND b1.tipster = bet.tipster AND b1.sport = bet.sport) AS prof, count(*), ' \ 'SUM(bet), avg(stake), avg(quota) FROM bet, tipster, sport ' \ 'WHERE bet.tipster=tipster.id AND bet.sport=sport.id GROUP BY bet.tipster,bet.sport' bd = Bbdd() datasql = bd.executeQuery(sql) bd.close() data = [] for i in datasql: row = [] if i[4] is None: continue row.append(i[0]) # Tipster row.append(i[1]) # Sports row.append(str(i[5])) # Number of bets try: win = i[2] / (i[2] + i[3]) # Percentage of win bet except ZeroDivisionError: win = 0 win = win * 100 row.append("{0:.2f}%".format(round((win), 2))) row.append("{0:.2f}".format(round( (i[6]), 2)) + LibStats.coin) # Money bet row.append("{0:.2f}".format(round( (i[4]), 2)) + LibStats.coin) # Profit row.append("{0:.2f}".format(round((i[7]), 2))) # Average Stake row.append("{0:.2f}".format(round((i[8]), 2))) # Average Quota data.append(row) return data
def getMarket(year=None, month=None): if year is not None: date = str(year)+"-"+str(month) sql = 'select market.name, sport.name, ' \ '(SELECT count(*) from bet as b1 WHERE b1.result in ("Acertada", "Medio Acertada") ' \ 'and b1.market = bet.market and b1.sport=bet.sport and b1.date LIKE "' + date + '%") as acierto, ' \ '(SELECT count(*) from bet as b1 WHERE b1.result in ("Fallada", "Medio Fallada") and b1.market = bet.market ' \ 'and b1.sport=bet.sport and b1.date LIKE "' + date + '%") as fallo, (SELECT SUM(profit) ' \ 'from bet as b1 WHERE b1.result <> "Pendiente" and b1.market = bet.market and b1.sport=bet.sport and b1.date LIKE "' + date + '%") as prof, count(*), ' \ 'SUM(bet), avg(stake), avg(quota) from bet, market, sport WHERE bet.market=market.id ' \ 'and bet.sport=sport.id and bet.date LIKE "' + date + '%" GROUP BY bet.market, bet.sport ' else: sql = 'SELECT market.name, sport.name, (SELECT count(*) FROM bet AS b1 WHERE b1.result IN ' \ '("Acertada", "Medio Acertada") AND b1.market = bet.market AND b1.sport=bet.sport) AS acierto, ' \ '(SELECT count(*) FROM bet AS b1 WHERE b1.result IN ("Fallada", "Medio Fallada") AND ' \ 'b1.market = bet.market AND b1.sport=bet.sport) AS fallo, (SELECT SUM(profit) ' \ 'FROM bet AS b1 WHERE b1.result <> "Pendiente" AND b1.market = bet.market AND b1.sport = bet.sport) AS prof, count(*), ' \ 'SUM(bet), avg(stake), avg(quota) FROM bet, market, sport ' \ 'WHERE bet.market=market.id AND bet.sport=sport.id GROUP BY bet.market, bet.sport' bd = Bbdd() datasql = bd.executeQuery(sql) bd.close() data = [] for i in datasql: if i[4] is None: continue row = [] row.append(i[0]) # Region row.append(i[1]) # Sports row.append(str(i[5])) # Number of bets try: win = i[2] / (i[2] + i[3]) # Percentage of win bet except ZeroDivisionError: win = 0 win = win * 100 row.append(str(win) + "%") row.append(str(i[6])) # Money bet row.append(str(i[4])) # Profit row.append(str(i[7])) # Average Stake row.append(str(i[8])) # Average Quota data.append(row) return data
def getMonth(year=None, month=None, day=None): date = str(year) if month is not None and month is not "": date += "-" + str(month) if day is not None and day is not "": date += "-" + day sql = 'select SUM(bet), ' \ '(select SUM(profit) from bet as b1 WHERE profit>0 AND b1.date LIKE "' + date + '%"), ' \ '(select SUM(profit) from bet as b1 WHERE profit<0 AND b1.date LIKE "' + date + '%"), ' \ 'SUM(profit), (select SUM(bet) from bet as b1 WHERE b1.result=0 AND bet.date LIKE "' + date + '%"), ' \ 'AVG(quota), count(bet), (select COUNT(*) from bet as b1 WHERE profit>0 AND b1.date LIKE "' + date + '%"), ' \ '(select count(*) from bet as b1 WHERE profit<0 AND b1.result<>0 AND b1.date LIKE "' + date + '%"),' \ '(select count(*) from bet as b1 WHERE profit=0 AND b1.date LIKE "' + date + '%"), AVG(bet)' \ ' from bet WHERE bet.date LIKE "' + date + '%"' bd = Bbdd() datasql = bd.executeQuery(sql) bd.close() bonus = Bookie.sumBonus("date LIKE '" + date + "%'") datasql = datasql[0] if bonus is None: bonus = 0 if datasql[0] == 0: return [0, 0, 0, 0, 0, "0%", 0, 0, 0, 0, 0, "0%", 0] yi = "{0:.2f}%".format( round(((datasql[3] + bonus) / datasql[0]) * 100, 2)) quota = float("{0:.2f}".format(datasql[5], 2)) bet = float("{0:.2f}".format(datasql[10], 2)) aciertos = "{0:.2f}%".format(round((datasql[7] / datasql[6]) * 100, 2)) data0 = "{0:.2f}".format(round(datasql[0], 2)) data1 = 0.0 if datasql[1] is None else "{0:.2f}".format( round(datasql[1] + bonus, 2)) data2 = 0.0 if datasql[2] is None else "{0:.2f}".format( round(datasql[2], 2)) data3 = 0.0 if datasql[3] is None else "{0:.2f}".format( round(datasql[3] + bonus, 2)) data4 = 0.0 if datasql[4] is None else "{0:.2f}".format( round(datasql[4], 2)) data = [ data0, data1, data2, data3, data4, yi, quota, datasql[6], datasql[7], datasql[8], datasql[9], aciertos, bet ] return data
def getBookie(year=None, month=None): if year is not None: date = str(year) + "-" + str(month) sql = 'select bookie.name, (SELECT count(*) from bet as b1 WHERE b1.result in ' \ '(1, 4) and b1.bookie = bet.bookie and b1.date LIKE "' + date + '%") as acierto, ' \ '(SELECT count(*) from bet as b1 WHERE b1.result in (2, 5) and ' \ 'b1.bookie = bet.bookie and b1.date LIKE "' + date + '%") as fallo, (SELECT SUM(profit) ' \ 'from bet as b1 WHERE b1.result <> 0 and b1.bookie = bet.bookie and b1.date LIKE "' + date + '%") as prof, count(*), ' \ 'SUM(bet), avg(stake), avg(quota) from bet, bookie ' \ 'WHERE bet.bookie=bookie.id and bet.date LIKE "' + date + '%" GROUP BY bet.bookie' else: sql = 'select bookie.name, (SELECT count(*) from bet as b1 WHERE b1.result in (1, 4)' \ ' and b1.bookie = bet.bookie) as acierto, (SELECT count(*) from bet as b1 WHERE b1.result in ' \ '(2, 5) and b1.bookie = bet.bookie) as fallo, ' \ '(SELECT SUM(profit) from bet as b1 WHERE b1.result <> 0 and' \ ' b1.bookie = bet.bookie) as prof, count(*), SUM(bet), avg(stake), ' \ 'avg(quota) from bet, bookie WHERE bet.bookie=bookie.id GROUP BY bet.bookie' bd = Bbdd() datasql = bd.executeQuery(sql) bd.close() data = [] for i in datasql: if i[3] is None: continue row = [] row.append(i[0]) # Name row.append(str(i[4])) # Number of bets try: win = i[1] / (i[1] + i[2]) # Percentage of win bet except ZeroDivisionError: win = 0 win = win * 100 win = round(win, 2) row.append("{0:.2f}%".format(round((win), 2))) row.append("{0:.2f}".format(round( (i[5]), 2)) + LibStats.coin) # Money bet row.append("{0:.2f}".format(round( (i[3]), 2)) + LibStats.coin) # Profit row.append("{0:.2f}".format(round((i[6]), 2))) # Average Stake row.append("{0:.2f}".format(round((i[7]), 2))) # Average Quota data.append(row) return data
def getStake(year=None, month=None): if year is not None: date = str(year) + "-" + str(month) sql = 'select bet.stake, (SELECT count(*) from bet as b1 WHERE b1.result in ' \ '("Acertada", "Medio Acertada") and b1.stake=bet.stake and b1.date LIKE "' + date + '%") as acierto, ' \ '(SELECT count(*) from bet as b1 WHERE b1.result in ("Fallada", "Medio Fallada") and ' \ 'b1.stake=bet.stake and b1.date LIKE "' + date + '%") as fallo, (SELECT SUM(profit) ' \ 'from bet as b1 WHERE b1.result <> "Pendiente" and b1.stake=bet.stake and b1.date LIKE "' + date + '%") as prof, count(*), ' \ 'SUM(bet), avg(quota) from bet WHERE bet.date LIKE "' + date + '%" GROUP BY bet.stake' else: sql = 'select bet.stake, (SELECT count(*) from bet as b1 WHERE b1.result in ("Acertada", "Medio Acertada")' \ ' and b1.stake = bet.stake) as acierto, (SELECT count(*) from bet as b1 WHERE b1.result in ' \ '("Fallada", "Medio Fallada") and b1.stake = bet.stake) as fallo, ' \ '(SELECT SUM(profit) from bet as b1 WHERE b1.result <> "Pendiente" and' \ ' b1.stake = bet.stake) as prof, count(*), SUM(bet), ' \ 'avg(quota) from bet GROUP BY bet.stake' bd = Bbdd() datasql = bd.executeQuery(sql) bd.close() data = [] for i in datasql: if i[3] is None: continue row = [] row.append(str(i[0])) # Stake row.append(str(i[4])) # Number of bets try: win = i[1] / (i[1] + i[2]) # Percentage of win bet except ZeroDivisionError: win = 0 win = win * 100 win = round(win, 2) row.append(str(win) + "%") row.append(str(i[5])) # Money bet row.append(str(i[3])) # Profit row.append(str(i[6])) # Average Quota data.append(row) return data
def initData(self): # dtDate sDate = datetime.now().strftime('%Y-%m-%d %H:%M:%S') date = QDateTime.fromString(sDate, "yyyy-MM-dd hh:mm:ss") self.dtDate.setDateTime(date) # cmbSport bd = Bbdd() data = bd.select("sport", "name") self.sportIndexToId = {} index = 0 for i in data: id = i[0] name = i[1] self.cmbSport.addItem(name) self.sportIndexToId[index] = id index += 1 # cmbBookie data = bd.select("bookie", "name") self.bookieIndexToId = {} index = 0 for i in data: id = i[0] name = i[1] self.cmbBookie.addItem(name) self.bookieIndexToId[index] = id index += 1 # cmbMarket data = bd.select("market", "name") self.marketIndexToId = {} index = 0 for i in data: id = i[0] name = i[1] self.cmbMarket.addItem(name) self.marketIndexToId[index] = id index += 1 # cmbTipster data = bd.select("tipster", "name") self.tipsterIndexToId = {} index = 0 for i in data: id = i[0] name = i[1] self.cmbTipster.addItem(name) self.tipsterIndexToId[index] = id index += 1 self.players = bd.executeQuery( "SELECT player1 as player FROM bet UNION SELECT player2 as player FROM bet ORDER BY player" ) self.players = [row[0] for row in self.players] self.txtPlayer1.addItems(self.players) self.txtPlayer2.addItems(self.players) bd.close() # cmbCompetition self.setRegion() #Combined self.contComb = 0 self.dates = [] self.sports = [] self.regions = [] self.competitions = [] self.players1 = [] self.players2 = [] self.picks = [] self.results = [] self.buttons = [] self.regionIndexToIdCmb = [] self.competitionIndexToIdCmb = [] self.calcStake()
def initData(self): # dtDate bd = Bbdd() sDate = bd.getValue(self.id, "bet", "date") date = QDateTime.fromString(sDate, "yyyy-MM-dd hh:mm:ss") self.dtDate.setDateTime(date) # cmbSport data = bd.select("sport", "name") self.sportIndexToId = {} index, idCmb = 0, 0 idBd = bd.getValue(self.id, "bet", "sport") for i in data: id = i[0] if id == idBd: idCmb = index name = i[1] self.cmbSport.addItem(name) self.sportIndexToId[index] = id index += 1 self.cmbSport.setCurrentIndex(idCmb) # cmbBookie data = bd.select("bookie", "name") index, idCmb = 0, 0 idBd = bd.getValue(self.id, "bet", "bookie") self.bookieIndexToId = {} index = 0 for i in data: id = i[0] if id == idBd: idCmb = index name = i[1] country = i[2] if LibYaml().interface['bookieCountry'] == 'Y': name += ' (' + country + ')' self.cmbBookie.addItem(name) self.bookieIndexToId[index] = id index += 1 self.cmbBookie.setCurrentIndex(idCmb) self.players = bd.executeQuery( "SELECT player1 AS player FROM bet UNION SELECT player2 AS player FROM bet ORDER BY player" ) self.players = [row[0] for row in self.players] self.txtPlayer1.addItems(self.players) self.txtPlayer2.addItems(self.players) # txtPlayer1 player1 = bd.getValue(self.id, "bet", "player1") self.txtPlayer1.setCurrentText(player1) # txtPlayer2 player2 = bd.getValue(self.id, "bet", "player2") self.txtPlayer2.setCurrentText(player2) # txtPick pick = bd.getValue(self.id, "bet", "pick") self.txtPick.setText(pick) # cmbMarket data = bd.select("market", "name") index, idCmb = 0, -1 idBd = bd.getValue(self.id, "bet", "market") self.marketIndexToId = {} index = 0 for i in data: id = i[0] if id == idBd: idCmb = index name = i[1] self.cmbMarket.addItem(name) self.marketIndexToId[index] = id index += 1 self.cmbMarket.setCurrentIndex(idCmb) # cmbTipster data = bd.select("tipster", "name") index, idCmb = 0, 0 idBd = bd.getValue(self.id, "bet", "tipster") self.tipsterIndexToId = {} index = 0 for i in data: id = i[0] if id == idBd: idCmb = index name = i[1] self.cmbTipster.addItem(name) self.tipsterIndexToId[index] = id index += 1 self.cmbTipster.setCurrentIndex(idCmb) # txtStake stake = bd.getValue(self.id, "bet", "stake") self.txtStake.setValue(stake) # txtOne one = bd.getValue(self.id, "bet", "one") self.txtOne.setValue(one) # txtBet bet = bd.getValue(self.id, "bet", "bet") self.txtBet.setValue(bet) # txtQuota quota = bd.getValue(self.id, "bet", "quota") self.txtQuota.setValue(quota) # txtProfit profit = bd.getValue(self.id, "bet", "profit") self.txtProfit.setValue(profit) result = bd.getValue(self.id, "bet", "result") self.cmbResult.setCurrentIndex(int(result)) freeBet = bd.getValue(self.id, "bet", "free") self.chkFree.setChecked(freeBet) bd.close() self.setRegion() # Combined self.contComb = 0 self.dates = [] self.sports = [] self.regions = [] self.competitions = [] self.players1 = [] self.players2 = [] self.picks = [] self.results = [] self.buttons = [] self.regionIndexToIdCmb = [] self.competitionIndexToIdCmb = []