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 = []
