· 4 years ago · Sep 02, 2021, 10:42 AM
1from settings import *
2
3
4class DB:
5 def __init__(self):
6 self.settings = Settings()
7 self.currency_symbol = self.settings.currency_symbol
8 self.db = self.settings.db
9 self.cur = self.db.cursor()
10
11 # Table_actions
12 def tables_create(self, showerror=''):
13 """Creating tables, if argument showerror is true, will print errors if
14 it's exist"""
15 # Create lotteries table
16
17 sql = "CREATE TABLE IF NOT EXISTS lotteries (" \
18 "id int NOT NULL AUTO_INCREMENT, " \
19 "ticket_price float(255, 2) NOT NULL DEFAULT 0, " \
20 "max_tickets int(255) NOT NULL, " \
21 "total_tickets int(255) NOT NULL, " \
22 "start_at int(255) NOT NULL, " \
23 "repeat_me varchar(255) NOT NULL, " \
24 "winner_ticket_id int(255) NULL, " \
25 "PRIMARY KEY (id))"
26 try:
27 self.cur.execute(sql)
28 print(f"TABLE lotteries created")
29 except mysql.connector.errors.ProgrammingError:
30 if showerror != '':
31 print("TABLE lotteries already exist")
32
33 # Create tickets table
34 sql = "CREATE TABLE IF NOT EXISTS tickets (" \
35 "id int NOT NULL AUTO_INCREMENT," \
36 "username varchar(255) NOT NULL," \
37 "telegram_id varchar(255) NOT NULL," \
38 "lottery_id int(255) NOT NULL," \
39 "price float(255, 2) NOT NULL DEFAULT 0," \
40 "win_amount float(255, 2) NOT NULL DEFAULT 0," \
41 "winner int(2) DEFAULT 0," \
42 "PRIMARY KEY (id))"
43
44 try:
45 self.cur.execute(sql)
46 print(f"TABLE tickets created")
47 except mysql.connector.errors.ProgrammingError:
48 if showerror != '':
49 print("TABLE tickets already exist")
50
51 # Create jackpot table
52 sql = "CREATE TABLE IF NOT EXISTS jackpot (" \
53 "id int NOT NULL AUTO_INCREMENT," \
54 "balance float(255, 2) NOT NULL DEFAULT 0," \
55 "winner_ticket int(255) NULL," \
56 "price float(255, 2) NOT NULL DEFAULT 0," \
57 "winner int(2) DEFAULT 0," \
58 "PRIMARY KEY (id))"
59
60 try:
61 self.cur.execute(sql)
62 print(f"TABLE jackpot created")
63 except mysql.connector.errors.ProgrammingError:
64 if showerror != '':
65 print("TABLE jackpot already exist")
66
67 # Create users_log table
68 sql = "CREATE TABLE IF NOT EXISTS users_log (`id` int NOT NULL " \
69 "AUTO_INCREMENT," \
70 "`telegram_id`varchar(255) NOT NULL," \
71 "`timestamp` timestamp DEFAULT CURRENT_TIMESTAMP," \
72 "`action`varchar(255) NOT NULL," \
73 "`amount`float(255, 2) NULL," \
74 "PRIMARY KEY(`id`))"
75 try:
76 self.cur.execute(sql)
77 print(f"TABLE users_log created")
78 except mysql.connector.errors.ProgrammingError:
79 if showerror != '':
80 print("TABLE users_log already exist")
81
82 # Create users table
83 sql = "CREATE TABLE IF NOT EXISTS users (id int NOT NULL " \
84 "AUTO_INCREMENT," \
85 "username varchar(255) NULL," \
86 "telegram_id int(255) NULL," \
87 "balance float(255, 2) NOT NULL DEFAULT 0," \
88 "PRIMARY KEY (id)," \
89 "UNIQUE (telegram_id))"
90 try:
91 self.cur.execute(sql)
92 print(f"TABLE users created")
93 except mysql.connector.errors.ProgrammingError:
94 if showerror != '':
95 print("TABLE users already exist")
96
97 # User actions
98
99 def user_is_registered(self, telegram_id):
100 sql = "SELECT telegram_id FROM users WHERE telegram_id = %s"
101 val = (telegram_id,)
102 self.cur.execute(sql, val)
103 if self.cur.fetchall():
104 print(f"\nWelcome back, {telegram_id}!")
105 return True
106 else:
107 return False
108
109 def user_add(self, username, telegram_id):
110
111 """Adding user to db and checking if exist"""
112 sql = "INSERT INTO users (username, telegram_id, balance) VALUES (" \
113 "%s, %s, 20)"
114 val = (username, telegram_id,)
115 try:
116 self.cur.execute(sql, val)
117 self.db.commit()
118
119 message = f"Hello, {telegram_id}!\nYou are " \
120 f"successfully registered "
121
122 return message
123 except mysql.connector.errors.ProgrammingError:
124
125 self.cur.execute(sql, val)
126 self.db.commit()
127 message = f"Table is not exist, but will be created " \
128 f"and user will be added.\n" \
129 f"Hello, {telegram_id}! You are " \
130 f"successfully registered "
131 return message
132
133 except mysql.connector.errors.IntegrityError:
134 message = f"Welcome back, {telegram_id}!"
135 return message
136
137 # Log actions
138 def user_log_add(self, telegram_id, action='', amount=''):
139
140 """Adding to table user_log record with information, time and
141 amount, about actions with users account( recharge, deduct, win,
142 input, withdrawal)"""
143 sql = "INSERT INTO users_log (telegram_id, action, amount) " \
144 "VALUES (%s, %s, %s)"
145 val = (telegram_id, action, amount)
146 self.cur.execute(sql, val)
147 self.db.commit()
148
149 # Balance actions
150 def balance_check(self, telegram_id):
151 """Checking balance for user with telegram_id"""
152 sql_check = "SELECT balance FROM users " \
153 "WHERE telegram_id = %s"
154 val = (telegram_id,)
155 self.cur.execute(sql_check, val)
156 try:
157 for balance in self.cur.fetchone():
158 return balance
159 except TypeError:
160 print("Sorry that telegram_id is not exist")
161 except mysql.connector.errors.ProgrammingError:
162 print("Table is not exist")
163
164 def balance_recharge(self, telegram_id, amount, show=False):
165 """Adding AMOUNT to USER with telegram_id, if show arg is TRUE print
166 message with info to which telegram_id account and how much added"""
167
168 try:
169 sql_update = "UPDATE users SET balance = %s WHERE telegram_id = %s"
170 after_recharge = self.balance_check(telegram_id) + float(amount)
171 val = (after_recharge, telegram_id)
172 self.cur.execute(sql_update, val)
173 self.db.commit()
174
175 if show:
176 print(f"\nTo account {telegram_id} recharged"
177 f" {float(amount)} $")
178 except TypeError:
179 print("Sorry that telegram_id is not exist")
180 except mysql.connector.errors.ProgrammingError:
181 print("Table is not exist")
182
183 def balance_deduct(self, telegram_id, amount, show=False):
184 """Deducting AMOUNT to USER with telegram_id, if show arg is TRUE print
185 message with info to which telegram_id account and how much
186 deducted"""
187 sql_update = "UPDATE users SET balance = %s WHERE telegram_id = %s"
188 try:
189 after_deduct = self.balance_check(telegram_id) - float(amount)
190 if after_deduct < 0.0:
191 if show:
192 print("\n---Your balance is not \
193 enough---\n")
194 return True
195
196 else:
197 val = (after_deduct, telegram_id)
198 self.cur.execute(sql_update, val)
199 self.db.commit()
200
201 if show:
202 print(f"\nFrom account {telegram_id} deducted"
203 f" {float(amount)} $")
204 except TypeError:
205 print("Sorry that telegram_id not exist")
206 except mysql.connector.errors.ProgrammingError:
207 print("Table is not exist")
208
209 # Account actions
210 def account_withdrawal(self, telegram_id, amount):
211 """Withdrawal from telegram_id account AMOUNT and write to the
212 users_log"""
213 self.user_log_add(telegram_id, "Account withdrawal", amount)
214 self.balance_deduct(telegram_id, amount)
215
216 def account_input(self, telegram_id, amount):
217 """Input to telegram_id account AMOUNT and write to the
218 users_log"""
219 print(self.db.is_connected())
220 self.balance_recharge(telegram_id, amount)
221 print(self.db.is_connected())
222 self.user_log_add(
223 telegram_id, "Account input", amount)
224
225 # Lottery actions
226 def lottery_add(self, max_tickets, start_at, ticket_price, repeat_me="no"):
227 """Insert new record into LOTTERIES table with following data:
228 max_tickets, start_at, ticket_price"""
229
230 sql = "INSERT INTO lotteries " \
231 "(max_tickets, start_at, ticket_price, repeat_me)" \
232 "VALUES (%s, %s, %s,%s)"
233 val = (int(max_tickets), int(start_at),
234 float(ticket_price), str(repeat_me))
235
236 self.cur.execute(sql, val)
237 self.db.commit()
238 return
239
240 def lottery_ticket_create(self, username, telegram_id, lottery_id,
241 ticket_price):
242 """Inserting new record into TICKETS table with following data:
243 telegram_id, lottery_id, ticket_price. Then add record in to
244 USERS_LOG"""
245 # Creating new ticket
246 create_ticket = "INSERT INTO tickets (username, telegram_id, " \
247 "lottery_id, price) VALUES(%s,%s,%s,%s)"
248 val_ticket = (username, telegram_id, lottery_id, ticket_price)
249 self.user_log_add(telegram_id, "Bought ticket", ticket_price)
250 self.cur.execute(create_ticket, val_ticket)
251 self.db.commit()
252 print(telegram_id, lottery_id)
253
254 print(f"\nYou bought 1 ticket for {ticket_price} "
255 f"{self.settings.currency_symbol}."
256 f"Your balance: {self.balance_check(telegram_id)}")
257
258 def lottery_update_total_tickets(self, lottery_id, total_tickets):
259 """Updating TOTAL_TICKETS in LOTTERIES table where id == LOTTERY_ID """
260 # Updating lotteries table
261 update_lotteries = \
262 "UPDATE lotteries SET total_tickets = %s " \
263 "WHERE id = %s"
264 val_lotteries = (total_tickets, lottery_id)
265 self.cur.execute(update_lotteries, val_lotteries)
266 self.db.commit()
267
268 def lottery_select(self, lottery_id):
269 """Fetch ticket_price, max_tickets, total_tickets, start_at from
270 lotteries where id == LOTTERY_ID. Return tuple."""
271
272 sql = "SELECT ticket_price, max_tickets, total_tickets, " \
273 "start_at, repeat_me FROM lotteries WHERE id = %s" \
274 " AND winner_ticket_id IS NULL"
275 val = (lottery_id,)
276 self.cur.execute(sql, val)
277
278 return self.cur.fetchall()
279
280 def lottery_ticket_buy(self, username, telegram_id, lottery_id):
281 """Buying ticket, required telegram_id(buyer of ticket) and lottery_id.
282 If winner_ticket_id in lotteries table is not NULL,
283 checking if total_tickets < start_at, otherwise allows to buy ticket if
284 enough balance. When all tickets sold, automatically starting
285 lottery to chose random winner. If lottery is finished but somehow
286 somebody like to buy ticket, he will receive message
287 that lottery is finished. """
288
289 for ticket_price, \
290 max_tickets, \
291 total_tickets, \
292 start_at, \
293 repeat_me \
294 in \
295 self.lottery_select(lottery_id):
296 if total_tickets <= start_at:
297
298 if self.balance_deduct(telegram_id,
299 ticket_price):
300 return "Not enough balance, please recharge."
301
302 elif total_tickets == start_at - 1:
303 total_tickets += 1
304 # Create new ticket
305 self.lottery_ticket_create(username, telegram_id,
306 lottery_id,
307 ticket_price)
308 # Lottery update total tickets
309 self.lottery_update_total_tickets(lottery_id,
310 total_tickets)
311 # Here autostart lottery
312 self.lottery_start(lottery_id)
313 # Create new lottery with same parameters
314 if repeat_me == "yes":
315 self.lottery_add(max_tickets, start_at, ticket_price,
316 repeat_me)
317 else:
318 total_tickets += 1
319 # Create new ticket
320 self.lottery_ticket_create(username, telegram_id,
321 lottery_id,
322 ticket_price)
323 # Lottery update total tickets
324 self.lottery_update_total_tickets(lottery_id,
325 total_tickets)
326 # All tickets sold
327 else:
328 # print("All tickets sold and lottery will be started now!.")
329 # Create new lottery with same parameters
330 if repeat_me == "yes":
331 self.lottery_add(max_tickets, start_at, ticket_price,
332 repeat_me)
333
334 # Here autostart lottery
335
336 self.lottery_start(lottery_id)
337
338 # print(
339 # f'\nLottery with ID: {lottery_id}, '
340 # f'is finished. You can`t buy the ticket.')
341
342 return "All tickets sold and lottery will be started now!."
343
344 def lottery_found_all(self, lot_id):
345 """Fetching ticket_price, max_tickets, total_tickets from LOT_ID and
346 then return result"""
347
348 sql = "SELECT ticket_price, max_tickets, total_tickets, " \
349 "start_at, winner_ticket_id FROM lotteries WHERE id = %s"
350 val = (lot_id,)
351 self.cur.execute(sql, val)
352
353 return self.cur.fetchall()
354
355 def lottery_ticket_found_all(self, lottery_id):
356 """Fetching id, telegram_id, winner from tickets where
357 lottery_id == LOTTERY_ID"""
358
359 sql = "SELECT id, telegram_id, winner FROM tickets " \
360 "WHERE lottery_id = %s"
361 val = (lottery_id,)
362 self.cur.execute(sql, val)
363
364 return self.cur.fetchall()
365
366 def lottery_ticket_update(self, ticket_id, amount):
367 """Updating winner to 1 in tickets table where id == TICKET_ID"""
368 # Update winner ticket
369 sql = "UPDATE tickets SET winner = %s, win_amount = %s WHERE id = %s"
370 val_ticket = (1, amount, ticket_id)
371 self.cur.execute(sql, val_ticket)
372 self.db.commit()
373
374 def lottery_update(self, ticket_id, lottery_id):
375 """Updating winner_ticket_id to TICKET_ID in lotteries table where
376 id == LOTTERY_ID"""
377 # Updating lotteries table
378 update_lotteries = \
379 "UPDATE lotteries SET winner_ticket_id = %s " \
380 "WHERE id = %s"
381 val_lotteries = (ticket_id, lottery_id)
382 self.cur.execute(update_lotteries, val_lotteries)
383 self.db.commit()
384
385 def lottery_start(self, lottery_id):
386 import random
387 tickets = {}
388 for ticket_price, \
389 max_tickets, \
390 total_tickets, \
391 start_at, \
392 winner_ticket_id \
393 in \
394 self.lottery_found_all(lottery_id):
395 if winner_ticket_id:
396 print(f"This lottery is finished. Winner ticket ID: "
397 f"{winner_ticket_id}")
398 else:
399 if total_tickets >= start_at:
400 for _id, telegram_id, winner in \
401 self.lottery_ticket_found_all(lottery_id):
402 tickets[_id] = telegram_id
403
404 tickets_id_list = list(tickets.keys())
405 ticket_win = random.randrange(min(tickets_id_list),
406 max(tickets_id_list))
407
408 winner_telegram_id = tickets[ticket_win]
409
410 total = (total_tickets * ticket_price)
411
412 to_jackpot = total * self.settings.jackpot_rate
413
414 to_winner = total - to_jackpot
415
416 self.lottery_ticket_update(ticket_win, to_winner)
417 self.lottery_update(ticket_win, lottery_id)
418 self.balance_recharge(winner_telegram_id, to_winner)
419 self.user_log_add(
420 winner_telegram_id, f"Win lottery",
421 to_winner)
422 # self.lottery_jackpot_recharge(amount=to_jackpot)
423
424 else:
425 print(f"\nNow it's {total_tickets} of {start_at} tickets "
426 f"sold.\n Lottery will start automatically when "
427 f"all tickets will be sold!")
428
429 def lottery_jackpot_check(self):
430 """Return balance of JackPot where winner_ticket IS NULL."""
431 try:
432 sql_check = "SELECT " \
433 "balance " \
434 "FROM jackpot WHERE winner_ticket IS NULL AND " \
435 "id in (SELECT MAX(id) from jackpot)"
436 self.cur.execute(sql_check)
437 # print(self.settings.timestamp + ' - ' + "Jackpot balance is " self.cur.fetchone())
438 print(1)
439 return 1
440
441 except TypeError:
442 print("Sorry that id is not exist")
443 except mysql.connector.errors.ProgrammingError:
444 print("Table is not exist")
445
446 def lottery_jackpot_recharge(self, amount, show=False):
447 """Update balance + AMOUNT, in jackpot table where winner_ticket is
448 null"""
449 sql_update = "UPDATE jackpot SET balance = %s WHERE " \
450 "winner_ticket IS NULL AND id in (SELECT MAX(id) " \
451 "from jackpot)"
452 sql_update1 = "UPDATE jackpot SET balance = %s WHERE id=4)"
453 try:
454 new_amount = self.lottery_jackpot_check() + float(amount)
455 val = (new_amount,)
456 self.cur.execute(sql_update1, val)
457 self.db.commit()
458
459 if show:
460 print(f"\nTo active JackPot account recharged:"
461 f" {float(amount)} $")
462 except TypeError:
463
464 sql = "INSERT INTO jackpot (balance)" \
465 "VALUES (%s)"
466 val = (float(amount),)
467 self.cur.execute(sql, val)
468 self.db.commit()
469
470 if show:
471 print(f"\nBut was created and recharged to active\n "
472 f"JackPot account: {float(amount)} $")
473 except mysql.connector.errors.ProgrammingError:
474 print("\nTable is not exist")
475
476 def lottery_jackpot_deduct(self, amount, show=False):
477 """Update balance - AMOUNT, in jackpot table where
478 winner_ticket is null and there is enough balance to deduct"""
479 try:
480 after_deduct = \
481 self.lottery_jackpot_check() - float(amount)
482 if after_deduct < 0.0:
483 if show:
484 print("\n---JackPot is balance is not so big---\n")
485 return True
486
487 else:
488 sql_update = "UPDATE " \
489 "jackpot " \
490 "SET " \
491 "balance = %s " \
492 "WHERE winner_ticket IS NULL"
493 val = (after_deduct,)
494 self.cur.execute(sql_update, val)
495 self.db.commit()
496
497 if show:
498 print(f"\nFrom active JackPot account deducted:"
499 f" {float(amount)} $")
500 except TypeError:
501 print("Sorry that id_tg not exist")
502
503 except mysql.connector.errors.ProgrammingError:
504 print("Table is not exist")
505
506 def lottery_winner(self, lottery_id):
507 sql = "SELECT id, username, telegram_id, win_amount FROM tickets " \
508 "WHERE " \
509 "lottery_id " \
510 "= %s " \
511 "AND winner = 1"
512 val = (lottery_id,)
513 self.cur.execute(sql, val)
514 result = self.cur.fetchone()
515 if result:
516 return result
517
518 # Statistic
519
520 def stats_show(self, telegram_id):
521 print(f"\n"
522 f"telegram_id : {telegram_id}\n"
523 f"Balance: {self.balance_check(telegram_id)}\n"
524 f"Total spend: {self.stats_total_spend(telegram_id)}\n"
525 f"Total earn: {self.stats_total_earn(telegram_id)}\n"
526 f"Profit: {self.stats_total_profit(telegram_id)}\n"
527 f"Your luck is {self.stats_luck(telegram_id)}%")
528
529 def stats_total_spend(self, telegram_id):
530 sql = "SELECT " \
531 "price " \
532 "FROM " \
533 "tickets " \
534 "WHERE " \
535 "winner = 0 AND telegram_id = %s"
536 val = (telegram_id,)
537
538 self.cur.execute(sql, val)
539 spend = 0
540 for prices in self.cur.fetchall():
541 for price in prices:
542 spend += price
543
544 # print(f"Total spend: {spend} $.")
545 return spend
546
547 def stats_total_earn(self, telegram_id):
548 sql = "SELECT " \
549 "id " \
550 "FROM " \
551 "tickets " \
552 "WHERE " \
553 "winner = 1 AND telegram_id = %s"
554 val = (telegram_id,)
555 earn_total = 0
556 self.cur.execute(sql, val)
557
558 for results in self.cur.fetchall():
559 for winner_ticket_id in results:
560 sql = "SELECT " \
561 "id, " \
562 "ticket_price, " \
563 "total_tickets " \
564 "FROM " \
565 "lotteries " \
566 "WHERE " \
567 "winner_ticket_id = %s"
568 val = (winner_ticket_id,)
569 self.cur.execute(sql, val)
570 for _id, price, tickets in \
571 self.cur.fetchall():
572 earn_total += price * tickets
573
574 after_jackpot_fee = earn_total - (earn_total *
575 self.settings.jackpot_rate)
576
577 return after_jackpot_fee
578
579 def stats_total_tickets_bought(self, telegram_id):
580 sql = "SELECT " \
581 "id " \
582 "FROM " \
583 "tickets " \
584 "WHERE " \
585 "telegram_id = %s"
586 val = (telegram_id,)
587
588 self.cur.execute(sql, val)
589 total_tickets = len(self.cur.fetchall())
590
591 return total_tickets
592
593 def stats_total_tickets_win(self, telegram_id):
594 sql = "SELECT " \
595 "winner " \
596 "FROM " \
597 "tickets " \
598 "WHERE " \
599 "telegram_id = %s " \
600 "AND " \
601 "winner = 1"
602 val = (telegram_id,)
603
604 self.cur.execute(sql, val)
605 total_tickets = len(self.cur.fetchall())
606
607 return total_tickets
608
609 def stats_total_recharged(self, telegram_id):
610 sql = "SELECT amount FROM user_log WHERE action = %s and " \
611 "telegram_id = %s"
612 val = ("recharge", telegram_id)
613
614 self.cur.execute(sql, val)
615 total = 0
616 for amount in self.cur.fetchall():
617 total += amount
618
619 return total
620
621 def stats_total_withdrawal(self, telegram_id):
622 sql = "SELECT amount FROM user_log WHERE action = %s and " \
623 "telegram_id = %s"
624 val = ("withdrawal", telegram_id)
625
626 self.cur.execute(sql, val)
627
628 total = 0
629 for amount in self.cur.fetchall():
630 total += amount
631
632 return total
633
634 def stats_luck(self, telegram_id):
635 try:
636 lack_rate = self.stats_total_tickets_win(telegram_id) / \
637 self.stats_total_tickets_bought(telegram_id)
638 return int(lack_rate * 100)
639 except ZeroDivisionError:
640 print("You don`t have tickets")
641
642 def stats_total_profit(self, telegram_id):
643 total_profit = self.stats_total_earn(telegram_id) - \
644 self.stats_total_spend(telegram_id)
645 return total_profit
646
647 def lotteries_list(self):
648 sql = 'SELECT id, ticket_price, max_tickets, total_tickets, ' \
649 'start_at, repeat_me ' \
650 'FROM lotteries ' \
651 'WHERE winner_ticket_id IS NULL'
652 self.cur.execute(sql)
653
654 return self.cur.fetchall()
655