requests.py 13.2 KB
Newer Older
Leo Peron's avatar
Leo Peron committed
1
2
import utils
import psycopg2 as sql
Leo Peron's avatar
Leo Peron committed
3
4
5
from psycopg2._psycopg import AsIs
from datetime import datetime as dt
from typing import List
Leo Peron's avatar
Leo Peron committed
6
7
8
9
10
11
12


class Requests:
    def __init__(self):
        self.utils = utils.Utils()
        self.datas = self.utils.loadDatas()
        self.conn = self.__connect()
Leo Peron's avatar
Leo Peron committed
13
        self.conn.autocommit = True
Leo Peron's avatar
Leo Peron committed
14
15
        self.cur = self.__getCursor()
        self.account_state = ["Ouvert", "Bloqué", "Fermé"]
16
        self.operation_state = ["Non Traitée", "Traitée"]
Leo Peron's avatar
Leo Peron committed
17
        self.account_type = ["courant", "revolving", "epargne"]
Leo Peron's avatar
Leo Peron committed
18
        self.operation_type = ["cartebleue", "virement", "cheque", "guichet"]
Leo Peron's avatar
Leo Peron committed
19
20
21
22
23
24
25
26
27
28
29
30

    def __connect(self):
        try:
            conn = sql.connect(
                f'host={self.datas["host"]} dbname={self.datas["dbname"]} user={self.datas["user"]} password={self.datas["pswd"]}'
            )
            return conn
        except sql.Error as e:
            print("Erreur de connexion")
            return None

    def __getCursor(self):
Leo Peron's avatar
Leo Peron committed
31
        if self.conn:
Leo Peron's avatar
Leo Peron committed
32
33
34
35
            return self.conn.cursor()
        else:
            return None

36
37
38
39
    def close(self):
        self.conn.close()
        self.utils.close()

Leo Peron's avatar
Leo Peron committed
40
41
    # GESTION D'UN UTILISATEUR

Leo Peron's avatar
Leo Peron committed
42
    def getUserByNum(self, num: int) -> List[str]:
43
        self.cur.execute("SELECT * FROM clients WHERE telephone=%s", (num,))
Leo Peron's avatar
Leo Peron committed
44
45
        return self.cur.fetchone()

Leo Peron's avatar
Leo Peron committed
46
    def createUser(self, num: int, prenom: str, adresse: str) -> bool:
Leo Peron's avatar
Leo Peron committed
47
        try:
Leo Peron's avatar
Leo Peron committed
48
49
50
            self.cur.execute(
                "INSERT INTO clients VALUES (%s,%s,%s)", (num, prenom, adresse)
            )
Leo Peron's avatar
Leo Peron committed
51
52
            return True
        except sql.Error as e:
Leo Peron's avatar
Leo Peron committed
53
            self.utils.writeLogs(e)
Leo Peron's avatar
Leo Peron committed
54
55
            return False

Leo Peron's avatar
Leo Peron committed
56
    def modifyUser(self, num: int, prenom: str, adresse: str) -> bool:
Leo Peron's avatar
Leo Peron committed
57
        try:
Leo Peron's avatar
Leo Peron committed
58
59
60
61
            self.cur.execute(
                "UPDATE clients SET prenom=%s, adresse=%s WHERE telephone=%s",
                (prenom, adresse, num),
            )
Leo Peron's avatar
Leo Peron committed
62
63
            return True
        except sql.Error as e:
Leo Peron's avatar
Leo Peron committed
64
            self.utils.writeLogs(e)
Leo Peron's avatar
Leo Peron committed
65
66
67
68
69
70
71
            return False

    def deleteUser(self, num: int) -> bool:
        try:
            self.cur.execute("DELETE FROM clients WHERE telephone=%s", (num,))
            return True
        except sql.Error as e:
Leo Peron's avatar
Leo Peron committed
72
            self.utils.writeLogs(e)
Leo Peron's avatar
Leo Peron committed
73
74
            return False

Leo Peron's avatar
Leo Peron committed
75
76
    def getUserAccounts(self, num: int, type: str) -> List[List[str]]:
        if type not in self.account_type:
Leo Peron's avatar
Leo Peron committed
77
78
79
            return None
        try:
            self.cur.execute(
Leo Peron's avatar
Leo Peron committed
80
81
                "SELECT * FROM appartenance INNER JOIN %s ON appartenance.%s = %s.id WHERE client=%s",
                (AsIs("comptes"+type), AsIs(type),AsIs("comptes"+type), num),
Leo Peron's avatar
Leo Peron committed
82
83
84
85
86
            )
            return self.cur.fetchall()
        except sql.Error as e:
            self.utils.writeLogs(e)
            return None
Leo Peron's avatar
Leo Peron committed
87

Leo Peron's avatar
Leo Peron committed
88
    def getUserOperations(self, num: int, type: str) -> List[List[str]]:
89
90
91
92
        if (type not in self.operation_type):
            print("Type d'opération invalide")
            return False

Leo Peron's avatar
Leo Peron committed
93
94
        try:
            self.cur.execute(
Leo Peron's avatar
Leo Peron committed
95
96
                "SELECT * FROM %s WHERE client=%s",
                (AsIs("operations" + type), num)
Leo Peron's avatar
Leo Peron committed
97
98
99
100
101
            )
            return self.cur.fetchall()
        except sql.Error as e:
            self.utils.writeLogs(e)
            return None
Leo Peron's avatar
Leo Peron committed
102

103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
    def getUntreatedUserOperations(self, num: int, type: str) -> List[List[str]]:
        if (type not in self.operation_type):
            print("Type d'opération invalide")
            return False

        try:
            self.cur.execute(
                "SELECT * FROM %s WHERE client=%s AND etat=%s",
                (AsIs("operations" + type), num, self.operation_state[0])
            )
            return self.cur.fetchall()
        except sql.Error as e:
            self.utils.writeLogs(e)
            return None

Leo Peron's avatar
Leo Peron committed
118

Leo Peron's avatar
Leo Peron committed
119
120
121
122
123
    # CREATION DES COMPTES BANCAIRES

    def createCourantAccount(self, num: int, solde: int, decouvert: int) -> bool:
        id = self.__generateAccountId()
        try:
Leo Peron's avatar
Leo Peron committed
124
125
126
127
            self.cur.execute(
                "INSERT INTO comptescourant VALUES (%s, current_timestamp , %s,%s,%s,%s,%s)",
                (id, self.account_state[0], solde, solde, solde, decouvert),
            )
Leo Peron's avatar
Leo Peron committed
128
129
130
            self.addUserToAccount(num, id, "courant")
            return True
        except sql.Error as e:
Leo Peron's avatar
Leo Peron committed
131
            self.utils.writeLogs(e)
Leo Peron's avatar
Leo Peron committed
132
133
            return False

134
    def createRevolvingAccount(self, num: int, solde: int, taux: int, montant: int) -> bool:
Leo Peron's avatar
Leo Peron committed
135
136
        id = self.__generateAccountId()
        try:
Leo Peron's avatar
Leo Peron committed
137
138
139
140
            self.cur.execute(
                "INSERT INTO comptesepargne VALUES (%s, current_timestamp, %s,%s,%s,%s)",
                (id, self.account_state[0], taux, montant, solde),
            )
Leo Peron's avatar
Leo Peron committed
141
142
143
            self.addUserToAccount(num, id, "revolving")
            return True
        except sql.Error as e:
Leo Peron's avatar
Leo Peron committed
144
            self.utils.writeLogs(e)
Leo Peron's avatar
Leo Peron committed
145
146
            return False

147
    def createEpargneAccount(self, num: int, interet: int, plafond: int, solde: int) -> bool:
Leo Peron's avatar
Leo Peron committed
148
149
        id = self.__generateAccountId()
        try:
Leo Peron's avatar
Leo Peron committed
150
151
152
153
            self.cur.execute(
                "INSERT INTO ComptesEpargne VALUES (%s, current_timestamp, %s,%s,%s,%s)",
                (id, self.account_state[0], interet, plafond, solde),
            )
Leo Peron's avatar
Leo Peron committed
154
155
156
            self.addUserToAccount(num, id, "epargne")
            return True
        except sql.Error as e:
Leo Peron's avatar
Leo Peron committed
157
            self.utils.writeLogs(e)
Leo Peron's avatar
Leo Peron committed
158
159
160
161
162
163
            return False

    # MODIFICATION DES COMPTES BANCAIRES

    def modifyCourantAccount(self, id: int, decouvert: int) -> bool:
        try:
Leo Peron's avatar
Leo Peron committed
164
            self.cur.execute(
165
                "UPDATE comptescourant SET decouvert=%s WHERE id=%s AND id IN (SELECT courant FROM appartenance WHERE client=%s)", (decouvert, id, num)
Leo Peron's avatar
Leo Peron committed
166
            )
Leo Peron's avatar
Leo Peron committed
167
168
            return True
        except sql.Error as e:
Leo Peron's avatar
Leo Peron committed
169
            self.utils.writeLogs(e)
Leo Peron's avatar
Leo Peron committed
170
171
172
173
            return False

    def modifyRevolvingAccount(self, id: int, taux: int) -> bool:
        try:
Leo Peron's avatar
Leo Peron committed
174
            self.cur.execute(
175
                "UPDATE comptesrevolving SET taux=%s WHERE id=%s AND id IN (SELECT courant FROM appartenance WHERE client=%s)", (id, taux, num)
Leo Peron's avatar
Leo Peron committed
176
            )
Leo Peron's avatar
Leo Peron committed
177
178
            return True
        except sql.Error as e:
Leo Peron's avatar
Leo Peron committed
179
            self.utils.writeLogs(e)
Leo Peron's avatar
Leo Peron committed
180
181
182
183
            return False

    def modifyEpargneAccount(self, num: int, interet: int) -> bool:
        try:
Leo Peron's avatar
Leo Peron committed
184
            self.cur.execute(
185
                "UPDATE comptesrevolving SET interet=%s WHERE id=%s AND id IN (SELECT revolving FROM appartenance WHERE client=%s)", (id, interet, num)
Leo Peron's avatar
Leo Peron committed
186
            )
Leo Peron's avatar
Leo Peron committed
187
188
            return True
        except sql.Error as e:
Leo Peron's avatar
Leo Peron committed
189
190
191
            self.utils.writeLogs(e)
            return False

192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
    def modifyAccountStatus(self, num: int, type: str, id: int, statut: str) -> bool:
        if (type not in self.account_type):
            print("Type de compte invalide")
            return False

        if (statut not in self.account_state):
            print("Etat de compte invalide")
            return False

        try:

            self.cur.execute("UPDATE comptes%s SET statut=%s WHERE id=%s AND id IN (SELECT %s FROM appartenance WHERE client=%s)", (AsIs(type), statut, id, AsIs(type), num))
            return True
        except sql.Error as e:
            self.utils.writeLogs(e)
            return False

Leo Peron's avatar
Leo Peron committed
209
210
    # RECUPERATION DES COMPTES BANCAIRES

Leo Peron's avatar
Leo Peron committed
211
    def getAccountsByType(self, type: str):
212
213
214
215
        if (type not in self.account_type):
            print("Type de compte invalide")
            return False

Leo Peron's avatar
Leo Peron committed
216
        try:
Leo Peron's avatar
Leo Peron committed
217
            self.cur.execute("SELECT * FROM comptes%s", (AsIs(type),))
Leo Peron's avatar
Leo Peron committed
218
219
220
            return self.cur.fetchall()
        except sql.Error as e:
            self.utils.writeLogs(e)
Leo Peron's avatar
Leo Peron committed
221
222
223
224
            return False

    # SUPPRESION D'UN COMPTE

225
226
227
228
229
230
231
232
233
234
235
    def deleteAccount(self, num: int, type: str, id: int):
        if (type not in self.account_type):
            print("Type de compte invalide")
            return False

        try:
            self.cur.execute("DELETE FROM comptes%s WHERE id=%s AND id IN (SELECT %s FROM appartenance WHERE client=%s)", (AsIs(type), id, AsIs(type), num))
            return True
        except sql.Error as e:
            self.utils.writeLogs(e)
            return False
Leo Peron's avatar
Leo Peron committed
236
237
238

    # Insertion d'une opération

Leo Peron's avatar
Leo Peron committed
239
    def createOperation(self, compte: int, client: int, op_type: str, acc_type: str, montant: int):
240
241
242
243
244
245
246
247
        if (acc_type not in self.account_type):
            print("Type de compte invalide")
            return False

        if (op_type not in self.operation_type):
            print("Type d'opération invalide")
            return False

Leo Peron's avatar
Leo Peron committed
248
249
        id = self.__generateOperationId()
        try:
Leo Peron's avatar
Leo Peron committed
250
            self.cur.execute(
Leo Peron's avatar
Leo Peron committed
251
                "INSERT INTO %s (id, %s, montant, etat, date, client) VALUES (%s, %s, %s, %s, current_timestamp, %s)",
Leo Peron's avatar
Leo Peron committed
252
253
254
255
256
257
258
                (
                    AsIs("operations" + op_type),
                    AsIs(acc_type),
                    id,
                    compte,
                    montant,
                    self.operation_state[0],
Leo Peron's avatar
Leo Peron committed
259
                    client
Leo Peron's avatar
Leo Peron committed
260
261
                ),
            )
Leo Peron's avatar
Leo Peron committed
262
263
            return True
        except sql.Error as e:
Leo Peron's avatar
Leo Peron committed
264
            self.utils.writeLogs(e)
Leo Peron's avatar
Leo Peron committed
265
266
267
            return False
        pass

Leo Peron's avatar
Leo Peron committed
268
    def getOperationByDate(self, date: str, type: str):
269
270
271
272
        if (type not in self.operation_type):
            print("Type d'opération invalide")
            return False

Leo Peron's avatar
Leo Peron committed
273
274
275
276
277
278
279
280
281
        try:
            self.cur.execute(
                "SELECT * FROM %s WHERE date=%s",
                (AsIs("operations" + type), date)
            )
            return self.cur.fetchone()
        except sql.Error as e:
            self.utils.writeLogs(e)
            return None
Leo Peron's avatar
Leo Peron committed
282

283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
    # TRAITEMENT D'UNE OPERATION

    def treatOperation(self, num: int, id: str, type: str):
        if (type not in self.operation_type):
            print("Type d'opération invalide")
            return False

        try:
            self.cur.execute("SELECT * FROM operations%s WHERE id=%s AND etat=%s AND client=%s", (AsIs(type), id, operation_state[0], num))
            operation = self.cur.fetchone()
            if operation:
                result = self.__updateSoldById(compte, acc_type, montant)
                if result:
                    self.cur.execute("UPDATE operations%s SET etat=%s WHERE id=%s", (AsIs(type), operation_state[1], id))
                    return True
                else:
                    print("Mise à jour du solde a échoué")
                    return False
            else:
                print("Aucune opération trouvée")
                return False
        except sql.Error as e:
            self.utils.writeLogs(e)
            return False

    def deleteOperation(self, num: int, type: str, id: int):
        if (type not in self.account_type):
            print("Type de compte invalide")
            return False

        try:
            self.cur.execute("DELETE FROM operations%s WHERE id=%s AND client=%s", (AsIs(type), id, num))
            return True
        except sql.Error as e:
            self.utils.writeLogs(e)
            return False

Leo Peron's avatar
Leo Peron committed
320
321
322
323

    # GESTION DES RELATIONS COMPTES - CLIENTS

    def addUserToAccount(self, num: int, id: int, type: str) -> bool:
324
325
326
327
        if (type not in self.account_type):
            print("Type de compte invalide")
            return False

Leo Peron's avatar
Leo Peron committed
328
        try:
Leo Peron's avatar
Leo Peron committed
329
330
331
332
            self.cur.execute(
                "INSERT INTO appartenance (client, %s) VALUES (%s, %s)",
                (AsIs(type), num, id),
            )
Leo Peron's avatar
Leo Peron committed
333
334
            return True
        except sql.Error as e:
335
            self.utils.writeLogs(e)
Leo Peron's avatar
Leo Peron committed
336
337
338
            return False

    def removeUserFromAccount(self, num: int, id: int, type: str) -> bool:
339
340
341
342
        if (type not in self.account_type):
            print("Type de compte invalide")
            return False

Leo Peron's avatar
Leo Peron committed
343
        try:
Leo Peron's avatar
Leo Peron committed
344
345
346
347
            self.cur.execute(
                "DELETE FROM appartenance WHERE client=%s AND %s=%s",
                (num, AsIs(type), id),
            )
Leo Peron's avatar
Leo Peron committed
348
349
            return True
        except sql.Error as e:
350
            self.utils.writeLogs(e)
Leo Peron's avatar
Leo Peron committed
351
352
353
354
355
            return False

    # GENERATIONS CLES COMPTES ET OPERATIONS

    def __generateAccountId(self) -> int:
Leo Peron's avatar
Leo Peron committed
356
357
358
        self.cur.execute(
            "SELECT MAX(id) FROM (SELECT id FROM ComptesEpargne UNION SELECT id FROM ComptesRevolving UNION SELECT id FROM ComptesCourant) AS comptes"
        )
Leo Peron's avatar
Leo Peron committed
359
360
361
362
363
364
        cle = self.cur.fetchone()
        if not cle[0]:
            return 1
        return cle[0] + 1

    def __generateOperationId(self) -> int:
Leo Peron's avatar
Leo Peron committed
365
366
367
        self.cur.execute(
            "SELECT MAX(id) FROM (SELECT id FROM operationscartebleue UNION SELECT id FROM operationscheque UNION SELECT id FROM operationsguichet UNION SELECT id FROM operationsvirement) AS operation"
        )
Leo Peron's avatar
Leo Peron committed
368
369
370
371
372
        cle = self.cur.fetchone()
        if not cle[0]:
            return 1
        return cle[0] + 1

Leo Peron's avatar
Leo Peron committed
373
374
    def getAccountById(self, id):
        self.cur.execute(
Leo Peron's avatar
Leo Peron committed
375
            "SELECT * FROM comptecourant, compterevolving, comptecourant WHERE Courant.id = {id} OR Revolving.id = {id} OR Epargne.id = {id}"
Leo Peron's avatar
Leo Peron committed
376
377
        )

Leo Peron's avatar
Leo Peron committed
378
379
    def __updateSoldById(self, id: int, type: str, var: int) -> bool:
        try:
Leo Peron's avatar
Leo Peron committed
380
            self.cur.execute("SELECT solde FROM %s WHERE id =%s", (AsIs('comptes'+type), id))
Leo Peron's avatar
Leo Peron committed
381
382
            raw = self.cur.fetchone()
            self.cur.execute(
Leo Peron's avatar
Leo Peron committed
383
384
                "UPDATE %s SET solde=%s WHERE id=%s",
                (AsIs("comptes" + type), raw[0] + var, id),
Leo Peron's avatar
Leo Peron committed
385
386
387
            )
            return True
        except sql.Error as e:
388
            self.utils.writeLogs(e)
Leo Peron's avatar
Leo Peron committed
389
            return False