-
Notifications
You must be signed in to change notification settings - Fork 0
/
db.py
232 lines (177 loc) · 7.63 KB
/
db.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
import sqlite3
from datetime import datetime, timedelta, date
from types import resolve_bases
from config import TOKEN, bot, dp, log_file, whitelist, monthes, logger
from to_rus import rus_category, rus_month
month_name = date.today().strftime("%B")
now_year = str(datetime.now().strftime("%Y"))
async def create_connection(user_id):
try:
connection = sqlite3.connect(f"data/finances_{user_id}_{month_name}_{now_year}.db")
cursor = connection.cursor()
connection.close()
except Exception as err:
logger.error(f"{err}")
async def create_table(user_id):
try:
connection = sqlite3.connect(f"data/finances_{user_id}_{month_name}_{now_year}.db")
cursor = connection.cursor()
cursor.execute(
"""
CREATE TABLE IF NOT EXISTS income (
id INTEGER PRIMARY KEY,
date TEXT NOT NULL,
value INTEGER NOT NULL,
note TEXT
)
"""
)
cursor.execute(
"""
CREATE TABLE IF NOT EXISTS consumption (
id INTEGER PRIMARY KEY,
date TEXT NOT NULL,
value INTEGER NOT NULL,
note TEXT
)
"""
)
connection.commit()
connection.close()
except Exception as err:
logger.error(f"{err}")
async def add_consumption(user_id, value, note):
try:
today = datetime.now()
now = today.strftime("%d.%m.%Y")
connection = sqlite3.connect(f"data/finances_{user_id}_{month_name}_{now_year}.db")
cursor = connection.cursor()
logger.debug(
cursor.execute(
"INSERT INTO consumption (date, value, note) VALUES (?, ?, ?)",
(now, value, note)
)
)
connection.commit()
connection.close()
except Exception as err:
logger.error(f"{err}")
async def add_income(user_id, value, note):
try:
today = datetime.now()
now = today.strftime("%d.%m.%Y")
connection = sqlite3.connect(f"data/finances_{user_id}_{month_name}_{now_year}.db")
cursor = connection.cursor()
logger.debug(
cursor.execute(
"INSERT INTO income (date, value, note) VALUES (?, ?, ?)",
(now, value, note)
)
)
connection.commit()
connection.close()
except Exception as err:
logger.error(f"{err}")
async def get_summary(user_id):
try:
connection = sqlite3.connect(f"data/finances_{user_id}_{month_name}_{now_year}.db")
cursor = connection.cursor()
cursor.execute("SELECT SUM(value) FROM income")
sum_income = cursor.fetchone()[0]
cursor.execute("SELECT SUM(value) FROM consumption")
sum_consumption = cursor.fetchone()[0]
cursor.close()
connection.close()
if sum_consumption is not None and sum_income is not None:
result = f"Ваша статистика за текущий месяц:\n+{str(sum_income)}\n-{str(sum_consumption)}\n\n{str(int(sum_income) - int(sum_consumption))} руб."
else:
result = f"Ваша статистика за текущий месяц:\n+{str(sum_income)}\n-{str(sum_consumption)}"
result = result.replace('None', '0')
return result
except Exception as err:
logger.error(f"{err}")
async def get_summary_by_month(user_id, month):
try:
connection = sqlite3.connect(f"data/finances_{user_id}_{month}_{now_year}.db")
cursor = connection.cursor()
cursor.execute("SELECT SUM(value) FROM income")
sum_income = cursor.fetchone()[0]
cursor.execute("SELECT SUM(value) FROM consumption")
sum_consumption = cursor.fetchone()[0]
cursor.close()
connection.close()
if sum_consumption is not None and sum_income is not None:
result = f"Данные за {str(await rus_month(month_name))}:\n+{str(sum_income)}\n-{str(sum_consumption)}\n\n{str(int(sum_income) - int(sum_consumption))} руб."
else:
result = f"Данные за {str(await rus_month(month_name))}:\n+{str(sum_income)}\n-{str(sum_consumption)}"
result = result.replace('None', '0')
return result
except Exception as err:
logger.error(f"{err}")
async def today(user_id):
try:
today = datetime.now()
current_date = str(today.strftime("%d.%m.%Y"))
connection = sqlite3.connect(f"data/finances_{user_id}_{month_name}_{now_year}.db")
cursor = connection.cursor()
cursor.execute("SELECT SUM(value) FROM income WHERE date = ?", (current_date,))
income = cursor.fetchall()
cursor.execute("SELECT SUM(value) FROM consumption WHERE date = ?", (current_date,))
consumption = cursor.fetchall()
cursor.close()
connection.close()
if consumption[0][0] is not None and income[0][0] is not None:
result = f"Статистика за {current_date}\n+ {str(income[0][0])} руб.\n- {str(consumption[0][0])} руб.\n\n{str(int(income[0][0]) - int(consumption[0][0]))} руб."
else:
result = f"Статистика за {current_date}\n+ {str(income[0][0])} руб.\n- {str(consumption[0][0])} руб."
result = result.replace('None', '0')
except Exception as err:
logger.error(f"{err}")
result = f"{monthes[i]} Ошибка\n"
return result
async def get_year_summary(user_id):
try:
message = " "
year_income = 0
year_consumption = 0
for i in range(12):
try:
connection = sqlite3.connect(
f"data/finances_{user_id}_{monthes[i]}_{now_year}.db"
)
cursor = connection.cursor()
cursor.execute("SELECT SUM(value) FROM income")
total_income = cursor.fetchall()
cursor.execute("SELECT SUM(value) FROM consumption")
total_consumption = cursor.fetchall()
message += f"\n{str(await rus_month(monthes[i]))}:\n+ {str(total_income[0][0])} руб.\n- {str(total_consumption[0][0])} руб."
message = message.replace('None', '0')
cursor.close()
connection.close()
except Exception as err:
logger.error(f"{err}")
return message
except Exception as err:
logger.error(f"{err}")
async def get_summary_by_category(user_id, category, month):
try:
connection = sqlite3.connect(f"data/finances_{user_id}_{month}_{now_year}.db")
cursor = connection.cursor()
cursor.execute(
"SELECT SUM(value) FROM income WHERE note = ?", (category,)
)
income = cursor.fetchall()
cursor.execute(
"SELECT SUM(value) FROM consumption WHERE note = ?", (category,)
)
consumption = cursor.fetchall()
cursor.close()
connection.close()
if consumption[0][0] is not None and income[0][0] is not None:
result = f"{str(await rus_month(month))}\nСтатистика по категории: {str(await rus_category(category))}\n+ {str(income[0][0])} руб.\n- {str(consumption[0][0])} руб.\n\n{str(int(income[0][0]) - int*(consumption[0][0]))} руб."
else:
result = f"{str(await rus_month(month))}\nСтатистика по категории: {str(await rus_category(category))}\n+ {str(income[0][0])} руб.\n- {str(consumption[0][0])} руб."
result = result.replace('None', '0')
return result
except Exception as err:
logger.error(f"{err}")