Coverage for lib/stockage/lib_pg_safia_sys.py: 48%

1534 statements  

« prev     ^ index     » next       coverage.py v7.9.1, created at 2026-02-18 02:40 +0100

1import datetime 

2import json 

3import sys 

4 

5try : 

6 import psycopg2.extras 

7 from psycopg2 import connect, sql 

8 from psycopg2.extras import RealDictCursor, RealDictRow, Json as psycopg2Json 

9except ImportError as e: 

10 print("ImportError : " + str(e)) 

11 print("Please install psycopg2 and psycopg2-binary") 

12# sys.exit(1) 

13 

14from lib.lib_util import replace_non_alpha_with_underscore 

15 

16class LibPGSafiaSys(): 

17 def __init__(self, PG_HOST = None, PG_USER = None, PG_PASSWORD = None, PG_DB = None, PG_PORT = None): 

18 import logging 

19 self.logger = logging.getLogger() 

20 try: 

21 # super().__init__() 

22 

23 if PG_HOST != None: 

24 self.client = connect( 

25 dbname=PG_DB, user=PG_USER, password=PG_PASSWORD, host=PG_HOST, port=PG_PORT 

26 ) 

27 else : 

28 self.client = None 

29 self.user_table = "mtruser.user" 

30 self.user_payment_status = "mtruser.user_payment_status" 

31 self.projects_table = "mtruser.project" 

32 self.groups_table = "mtrright.group" 

33 self.log_query_table = "mtrlog.log_query" 

34 self.datou_table = "mtrdatou.datou" 

35 

36 if self.client != None: 

37 # - [ ] TODO VR 18-8-23 : je ne comprends pas pourquoi ca plantait pas avant, il faudrait regarder les précédentes versions 

38 self.client.autocommit = True # => avec cela les tests passent 

39 

40# self.client.set_session(readonly=False, autocommit=True) 

41# import psycopg2 

42# self.client.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) 

43 

44 self.get_admin_situation() 

45 self.PG_HOST = PG_HOST 

46 self.PG_USER = PG_USER 

47 self.PG_PASSWORD = PG_PASSWORD 

48 self.PG_DB = PG_DB 

49 self.PG_PORT = PG_PORT 

50 except Exception as e: 

51 print(str(e)) 

52 self.client = None 

53 

54 def reconnect(self): 

55 self.client = connect( 

56 dbname=self.PG_DB, user=self.PG_USER, password=self.PG_PASSWORD, host=self.PG_HOST, port=self.PG_PORT 

57 ) 

58 

59 def __del__(self): 

60 print("Calling destructor") 

61 self.logger.info("Calling destructor") 

62 try : 

63 self.get_admin_situation() 

64 self.client.commit() 

65 self.get_admin_situation() 

66 except Exception as e: 

67 print(str(e)) 

68 print("Exception while deleting stuff !") 

69 

70# cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) 

71 

72 def kill_all_lock_transaction(self, list_ids): 

73 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

74 for id in list_ids: 

75 terminate_lock = "SELECT pg_terminate_backend(%s)" 

76 cursor.execute(terminate_lock, (id,)) 

77 

78 def get_admin_situation(self, exclude_table = ["pg_namespace", "pg_index", "pg_class", "pg_authid", "pg_database"], 

79 verbose = False): 

80 query_get_lock = """ 

81SELECT 

82 relname, 

83 locktype, 

84 page, 

85 virtualtransaction, 

86 pid, 

87 mode, 

88 granted 

89FROM 

90 pg_locks l 

91LEFT JOIN 

92 pg_stat_all_tables t ON l.relation = t.relid 

93WHERE 

94 relname IS NOT NULL; 

95 """ 

96 

97 if self.client == None: 

98 return [], [] 

99 

100 list_locks = [] 

101 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

102 cursor.execute(query_get_lock) 

103 list_locks = cursor.fetchall() 

104 

105 query_get_running = """ 

106 SELECT pid, age(clock_timestamp(), query_start), usename, query FROM pg_stat_activity WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY query_start desc; 

107 """ 

108 list_running = [] 

109 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

110 cursor.execute(query_get_running) 

111 list_running = cursor.fetchall() 

112 

113 list_locks_kept = [] 

114 for l in list_locks: 

115 if l["relname"] not in exclude_table: 

116 list_locks_kept.append(l) 

117 

118 list_running_kept = [] 

119 for l in list_running: 

120 if l["query"] != "": 

121 list_running_kept.append(l) 

122 self.logger.info(" list_locks_kept : " + str(len(list_locks_kept))) 

123 self.logger.info(" list_running_kept : " + str(len(list_running_kept))) 

124 print(str(" list_locks_kept : " + str(len(list_locks_kept)))) 

125 print(str(" list_running_kept : " + str(len(list_running_kept)))) 

126 if verbose: 

127 self.logger.info(" list_locks_kept : " + str((list_locks_kept))) 

128 self.logger.info(" list_running_kept : " + str((list_running_kept))) 

129 print(str(" list_locks_kept : " + str((list_locks_kept)))) 

130 print(str(" list_running_kept : " + str((list_running_kept)))) 

131 

132 

133 

134 

135 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

136 cursor.execute("SHOW TRANSACTION ISOLATION LEVEL;") 

137 status = cursor.fetchall() 

138 print(str(status)) 

139 

140 # select pg_terminate_backend(47675); 

141 return list_locks_kept, list_running_kept 

142 

143 # TODO VR 1-5-24 : je ne sais pas d'ou cela est-il fait ! 

144 def create_statement(self, migration): 

145 with self.client.cursor() as cursor: 

146 cursor.execute(migration) 

147 self.client.commit() 

148 

149 def select_user(self, id): 

150 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

151 query = sql.SQL("SELECT * FROM {} WHERE id=%s").format(sql.Identifier(*self.user_table.split('.'))) 

152 cursor.execute(query, (id,)) 

153 one_fetch = cursor.fetchone() 

154 return one_fetch 

155 

156 def update_user(self, id, 

157 first_name = None, 

158 last_name = None, 

159 phone = None, 

160 email = None, 

161 misc_info = None): 

162 list_set = [] 

163 list_data = () 

164 if first_name != None: 

165 list_set.append(" firstname=%s ") 

166 list_data += (first_name,) 

167 if last_name != None: 

168 list_set.append(" lastname=%s ") 

169 list_data += (last_name,) 

170 if phone != None: 

171 list_set.append(" phone=%s ") 

172 list_data += (phone,) 

173 if email != None: 

174 list_set.append(" mail=%s ") 

175 list_data += (email,) 

176 if misc_info != None: 

177 list_set.append(" miscinfo=%s ") 

178 list_data += (json.dumps(misc_info),) 

179 

180 if len(list_set) == 0: 

181 return 0 

182 

183 info_to_update = ', '.join(list_set) 

184 

185 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

186 query = sql.SQL("UPDATE {} SET " + info_to_update + " WHERE id=%s RETURNING *").format(sql.Identifier(*self.user_table.split('.'))) 

187 list_data += (id,) 

188 cursor.execute(query, list_data) 

189 one_fetch = cursor.fetchone() 

190 return one_fetch 

191 

192 def get_user_from_mail(self, mail): 

193 if mail == None: 

194 return None 

195 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

196 query = sql.SQL("SELECT * FROM {} WHERE mail=%s").format(sql.Identifier(*self.user_table.split('.'))) 

197 print(query.as_string(self.client)) 

198 cursor.execute(query, (mail,)) 

199 all_fetch = cursor.fetchone() 

200 if all_fetch == None: 

201 return None 

202 return all_fetch 

203 

204 def get_user_info_from_id(self, user_id): 

205 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

206 query = sql.SQL("SELECT * FROM {} WHERE id=%s").format(sql.Identifier(*self.user_table.split('.'))) 

207 # print(query) 

208 cursor.execute(query, (user_id,)) 

209 one_fetch = cursor.fetchone() 

210 if one_fetch == None: 

211 return None 

212 return one_fetch 

213 

214 def get_user_from_phone(self, phone): 

215 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

216 query = sql.SQL("SELECT * FROM {} WHERE phone=%s").format(sql.Identifier(*self.user_table.split('.'))) 

217 print(query.as_string(self.client)) 

218 cursor.execute(query, (phone,)) 

219 all_fetch = cursor.fetchone() 

220 if all_fetch == None: 

221 return None 

222 return all_fetch 

223 

224 def topup_money(self, user_id, amount): # pragma no cover payment 

225 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

226 insert_query = sql.SQL("""INSERT INTO {} (user_id, amount) 

227 VALUES (%s, %s) 

228 ON CONFLICT (user_id) 

229 DO UPDATE SET 

230 amount = mtruser.user_payment_status.amount + EXCLUDED.amount, 

231 updated_at = NOW() 

232 RETURNING amount""").format(sql.Identifier(*self.user_payment_status.split('.'))) 

233 val = (user_id, amount) 

234 cursor.execute(insert_query, val) 

235 self.client.commit() 

236 one_fetch = cursor.fetchone() 

237 # TODO VR would prefer to do this in lss 

238 cursor.close() 

239 if one_fetch == None: 

240 return 0 

241 else : 

242 return one_fetch["amount"] 

243 

244 def get_current_amount(self, user_id): 

245 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

246 query = sql.SQL("SELECT * FROM {} WHERE user_id=%s").format(sql.Identifier(*self.user_payment_status.split('.'))) 

247 cursor.execute(query, (user_id,)) 

248 one_fetch = cursor.fetchone() 

249 # TODO VR would prefer to do this in lss 

250 if one_fetch == None: 

251 return 0 

252 else : 

253 return one_fetch["amount"] 

254 

255 def setup_user(self, email = "anonymous@opio.fr", auth_type = "otp_apia"): 

256 print(" email : " + str(email)) 

257 

258 # mtruser I owuld like it in database mtr OR ? 

259 

260 with self.client.cursor() as cursor: 

261# insert_query = "INSERT INTO \"user\" (mail, auth_type, schema_user_documents) VALUES (%s, %s, %s)" 

262 schema_name = replace_non_alpha_with_underscore(email) + "_documents" 

263# ret = cursor.execute(insert_query, (email, auth_type, schema_name)) 

264 

265 complete_insert_query = sql.SQL(""" 

266 INSERT INTO {} (mail, auth_type, schema_user_documents) VALUES (%s, %s, %s) 

267 RETURNING id""").format(sql.Identifier(*self.user_table.split('.'))) 

268# SELECT currval('user_id_seq'); 

269 

270 print(complete_insert_query.as_string(self.client)) 

271 

272 cursor.execute(complete_insert_query, (email, auth_type, schema_name)) 

273 all_fetch = cursor.fetchone() 

274 if len(all_fetch) == 1: 

275 ret = all_fetch[0] 

276 else : 

277 ret = None 

278 print("ERROR exiting (or not)") 

279 

280 create_schema_document_user = sql.SQL("CREATE SCHEMA IF NOT EXISTS {}").format(sql.Identifier(schema_name)) 

281 

282 cursor.execute(create_schema_document_user) 

283 self.client.commit() 

284 

285 print(" ret setup_user : " + str(ret)) 

286 

287 return ret 

288 

289 def get_projects_from_owner(self, user_id = 31, project_id = None): # info@opio.fr could be public project 

290 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

291 if project_id != None: 

292 query = "SELECT * FROM mtruser.project WHERE owner_id=%s AND id=%s" 

293 cursor.execute(query, (user_id, project_id)) 

294 else: 

295 query = "SELECT * FROM mtruser.project WHERE owner_id=%s" 

296 cursor.execute(query, (user_id,)) 

297 all_fetch = cursor.fetchall() 

298 return all_fetch 

299 

300 def get_project_with_description_and_user(self, user_id, description): 

301 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

302 

303 select_query = sql.SQL("SELECT * FROM {} WHERE owner_id=%s AND description=%s").format(sql.Identifier(*self.projects_table.split('.'))) 

304 cursor.execute(select_query, (user_id, description)) 

305 res = cursor.fetchone() 

306 if res != None : 

307 print(" One Project found for this user with the same description : " + str(res)) 

308 return res 

309 else : 

310 return None 

311 

312 def create_project(self, user_id, schema, description, otp): 

313 

314# with self.client.cursor(cursor_factory=DictCursor) as cursor: 

315 with self.client.cursor() as cursor: 

316 # VR todo on pourrait rajouter les initiales dans le noms des tables 

317 table_name = schema + "." + "dataset_" + otp 

318 match_page_sections_function_name = "match_" + otp 

319 if len(table_name) > 63: 

320 print(str(len(table_name))) 

321 print("We truncate the table name, and what about the matching function name ? ") 

322 table_name = table_name[:63] 

323 

324 select_query = sql.SQL("SELECT * FROM {} WHERE table_documents=%s").format(sql.Identifier(*self.projects_table.split('.'))) 

325 cursor.execute(select_query, (table_name,)) 

326 res = cursor.fetchone() 

327 if res != None: 

328 if len(res) == 1: 

329 return {"id" : res[0]} 

330 

331 insert_query = sql.SQL("INSERT INTO {} (owner_id, description, table_documents, function_search_documents) VALUES (%s, %s, %s, %s) RETURNING ID").format(sql.Identifier(*self.projects_table.split('.'))) 

332 

333 print(" insert_query : " + str(insert_query)) 

334 

335 cursor.execute(insert_query, (str(user_id), description, table_name, match_page_sections_function_name)) 

336 

337# print(" insert_query : " + str(insert_query)) 

338 

339# cursor.execute(insert_query, (str(user_id), description, table_name, match_page_sections_function_name)) 

340 

341 all_fetch = cursor.fetchone() 

342 #for row in all_fetch: 

343 # print(row) 

344 

345 print("NEED TO CREATE THE DOCUMENT TABLE NOW !") 

346 from lib.stockage.lib_pg_dataset_pg import build_query_create_documents 

347 hash40 = "idx_" + otp[:35] 

348 docs = build_query_create_documents(table_name, match_page_sections_function_name, hash40) 

349 

350 print(docs) 

351 cursor.execute(docs) 

352 self.client.commit() 

353 cursor.close() 

354 

355 print(" ret : " + str(all_fetch)) 

356 

357 if len(all_fetch) == 1: 

358 return {"id" : all_fetch[0]} 

359 

360# data = [dict(row) for row in data] 

361 def get_project_info(self, project_id): 

362 if project_id == None or project_id == "None": 

363 return {} 

364 import logging 

365 logger = logging.getLogger() 

366 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

367 query = sql.SQL("SELECT * FROM {} WHERE id=%s").format(sql.Identifier(*self.projects_table.split('.'))) 

368 logger.info(" query : " + query.as_string(self.client)) 

369 cursor.execute(query, (project_id,)) 

370 logger.info(" before fetchall ") 

371 info_project = cursor.fetchall() 

372 cursor.close() 

373 if len(info_project) == 1: 

374 return dict(info_project[0]) 

375 else: 

376 print("Unexpected result ") 

377 return {} 

378 

379 def get_meta_info_project(self, table_documents, 

380 group_by = None ) : #"id"): # TODO wip plop 

381 import logging 

382 logger = logging.getLogger() 

383 logger.info(" in get_meta_info_project ") 

384# return {"number_documents" : 28} 

385 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

386 table_parts = table_documents.split('.') 

387 if group_by != None and group_by != None: 

388 # Note: group_by is a column name which cannot be parameterized, but it should be validated/sanitized by caller 

389 query = sql.SQL("SELECT count(*) FROM (SELECT count(*) AS number_documents, max(created_at) as latest_update FROM {} GROUP BY {}) as temp").format( 

390 sql.Identifier(*table_parts), 

391 sql.Identifier(group_by) 

392 ) 

393 else: 

394 query = sql.SQL("SELECT count(*) AS number_documents, max(created_at) as latest_update FROM {}").format( 

395 sql.Identifier(*table_parts) 

396 ) 

397 logger.info(" query : " + query.as_string(self.client)) 

398 cursor.execute(query) 

399 logger.info(" before fetchall ") 

400 nb_docs = cursor.fetchall() 

401 cursor.close() 

402 self.client.commit() # Pour test des nrt 19-6-23 2:30 

403 if len(nb_docs) == 1: 

404 return dict(nb_docs[0]) 

405 else: 

406 print("Unexpected result ") 

407 return {} 

408 

409 def truncate(self, table_documents, kill_all = False, verbose = False): 

410 only_table = table_documents.split(".")[1] if len(table_documents.split(".")) == 2 else "" 

411 locks, rt = self.get_admin_situation(verbose=verbose) 

412 self.logger.info(" locks : " + str(locks)) 

413 locks_with_table = list(filter(lambda x : x["relname"] == only_table, locks)) 

414 rt_with_table = list(filter(lambda x : table_documents in x["query"], rt)) 

415 self.logger.info(" locks_with_table : " + str(locks_with_table)) 

416 

417 # 32779 

418 if kill_all: 

419 list_ids = list(map(lambda x : x["pid"], rt_with_table)) 

420 self.kill_all_lock_transaction(list_ids) 

421 # TODO VR : would be better to check again but I don't know what's need to be done 

422 rt_with_table = [] 

423 locks_with_table = [] 

424 elif len(locks_with_table) > 0 or len(rt_with_table) > 0: 

425 print("We expect problem and should wait ! ") 

426 self.logger.info("We expect problem and should wait") 

427 self.logger.info("RETURNING") 

428 return "NO_TRUNCATE" 

429 import time 

430 waittime = 1 

431 while len(locks_with_table) > 0 or len(rt_with_table) > 0: 

432 time.sleep(waittime) 

433 waittime *= 2 

434 locks, rt = self.get_admin_situation(verbose=verbose) 

435 locks_with_table = list(filter(lambda x : x["relname"] == only_table, locks)) 

436 rt_with_table = list(filter(lambda x : table_documents in x["query"], rt)) 

437 print("STILL") 

438 self.logger.info("STIHL") 

439# time.sleep(10) 

440 self.get_admin_situation(verbose=verbose) 

441 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

442 table_parts = table_documents.split('.') 

443 query = sql.SQL("TRUNCATE {}").format(sql.Identifier(*table_parts)) 

444 print("About to truncate " + query.as_string(self.client)) 

445 self.logger.info("Truncate") 

446 cursor.execute(query) # got AccessExclusiveLock 

447 self.client.commit() 

448 cursor.close() 

449 self.logger.info("End of truncate !") 

450 self.get_admin_situation(verbose=verbose) 

451 return "TRUNCATE" 

452 

453 def add_cost_to_project(self, project_id : int, add_cost : float) -> dict: 

454 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

455 

456 query = sql.SQL("UPDATE {} SET cost = cost + %s WHERE id=%s").format(sql.Identifier(*self.projects_table.split('.'))) 

457 print(query.as_string(self.client)) 

458 cursor.execute(query, (add_cost, project_id)) 

459 cursor.close() 

460 

461 return {} 

462 

463 def update_project_info(self, project_id, info_json): 

464 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

465 

466 list_set = [] 

467 list_data = () 

468 if "shortname" in info_json: 

469 list_set.append(" shortlink=%s ") 

470 list_data += (info_json["shortname"],) 

471# list_data.append(info_json["shortname"]) 

472 

473 if "name" in info_json: 

474 list_set.append(" description=%s ") 

475 list_data += (info_json["name"],) 

476 

477 if "configuration" in info_json: 

478 if "key" in info_json and info_json["key"] != "" and info_json["key"] != "None": 

479# list_set.append(" configuration=%s ") 

480# list_data.append(info_json["configuration"]) 

481 value_to_insert = info_json["configuration"] 

482 list_keys = info_json["key"].split("/") 

483# if len(list_keys) == 1: # TODO VR 28-7-24 : we should refactor this to jsonb 

484# key_to_insert = list_keys[0] 

485# value_to_insert = info_json["configuration"] 

486# list_set.append("configuration = jsonb_set(configuration, '{" + key_to_insert + "}', to_jsonb('" + value_to_insert + "'), true)") 

487 

488 key_as_csv = ",".join(list_keys) 

489 

490 print(" value_to_insert type : " + str(type(value_to_insert))) 

491 if type(value_to_insert) == str: 

492 value_to_insert = "to_jsonb('" + value_to_insert + "'::text)" 

493 elif type(value_to_insert) == dict: 

494 import json 

495 # TODO make plus propre 

496# list_data += (json.dumps(value_to_insert),) 

497# value_to_insert = "to_jsonb(" + json.dumps(value_to_insert) + "::jsonb, true)" 

498# list_data += (json.dumps(value_to_insert),) 

499# value_to_insert = "to_jsonb(" + str("%s") + "::jsonb)" 

500# value_to_insert = "to_jsonb(" + str("%s") + ")" 

501 value_to_insert = "$$" + json.dumps(value_to_insert) + "$$::jsonb" 

502 

503 

504 to_insert = "configuration = jsonb_set(configuration, '{" + key_as_csv + "}', " + value_to_insert + ",true)" 

505 list_set.append(to_insert) 

506 print("We will need to update the configuration afer refactoring to jsonb, now in test 28-7-24") 

507 else: 

508 list_set.append( "configuration=%s ") 

509 import json 

510 list_data += (json.dumps(info_json["configuration"]),) 

511 

512 if len(list_set) == 0: 

513 return "NOTHING DONE" 

514 

515 set_clause = " AND ".join(list_set) 

516 query = sql.SQL("UPDATE {} SET " + set_clause + " WHERE id=%s").format(sql.Identifier(*self.projects_table.split('.'))) 

517 list_data += (project_id,) 

518 

519 print(query.as_string(self.client)) 

520 # Or 

521 # just 

522 # testing 

523 # what 

524 # will 

525 # be 

526 # sent 

527 # to 

528 # the 

529 # server 

530 

531 

532 if len(list_data) == 1: # Only project_id 

533 cursor.execute(query, list_data) 

534 return "SAVED" 

535 else: 

536 print(cursor.mogrify(query, list_data).decode('utf8')) 

537 cursor.execute(query, list_data) 

538# cursor.insert_many(query, list_data) 

539 cursor.close() 

540 return "SAVED" 

541 

542 def get_project_by_shortname(self, shortlink): 

543 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

544 query = "select id FROM mtruser.project where shortlink=%s;" 

545 cursor.execute(query, (shortlink, )) 

546 res = cursor.fetchall() 

547 return res 

548 

549 def set_valid_bill(self, bill_id): 

550 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

551 query = "UPDATE mtruser.mtr_factures SET validated=true WHERE id=%s" 

552 cursor.execute(query, (bill_id,)) 

553 self.client.commit() 

554 return True 

555 

556 def get_last_fact_id_month(self): 

557 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

558# query = "SELECT id FROM mtruser.mtr_factures WHERE validated=true ORDER BY created_at DESC LIMIT 1;" 

559# query = "SELECT max(id) FROM mtruser.mtr_factures;" 

560 query = "SELECT id, month FROM mtruser.mtr_factures order by id desc limit 1;" 

561 cursor.execute(query) 

562 res = cursor.fetchone() 

563 

564 res_max_id = res["id"] if "id" in res else None 

565 res_month_argmax = res["month"] if "month" in res else None 

566 

567 query2 = "select last_value from mtruser_mtr_facture_id_seq;" 

568 

569 cursor.execute(query2) 

570 res2 = cursor.fetchone() 

571 res_last_value = res2["last_value"] if "last_value" in res2 else None 

572 

573 return res_max_id, res_last_value, res_month_argmax 

574 

575 # TODO VR 20-4-25 : to be deleted since not used and not as good ! 

576 def reset_facture_info(self, new_max_id): 

577 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

578 query = "SELECT setval('mtruser_mtr_facture_id_seq', %s, false)" 

579 cursor.execute(query, (new_max_id,)) 

580 self.client.commit() 

581 

582 query = "DELETE FROM mtruser.mtr_factures WHERE id>%s" 

583 cursor.execute(query, (new_max_id,)) 

584 self.client.commit() 

585 

586 return True 

587 

588 def insert_new_bill(self, bill_id, filename, project_id, month_string_for_data, price_ht, price_ttc, nb_page_total): 

589 # TODO VR 20-4-25 

590 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

591 query = f"""INSERT INTO mtruser.mtr_factures (filename, project_id, month, price_ht, price_ttc, nb_pages) VALUES 

592 ('{filename}', {project_id}, '{month_string_for_data}', '{price_ht}', '{price_ttc}', '{nb_page_total}') RETURNING id;""" 

593 cursor.execute(query) 

594 self.client.commit() 

595 

596 all_fetch = cursor.fetchone() 

597 if len(all_fetch) == 1: 

598 query_id = all_fetch["id"] 

599 else : 

600 query_id = None 

601 print("ERROR exiting (or not)") 

602 

603 if query_id != bill_id: 

604 print("Inconsistent, please check !") 

605 

606 return query_id 

607 

608 def get_facture_from_project(self, project_id = None, is_validated = None): 

609 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

610 list_conditions = [] 

611 list_params = [] 

612 if project_id != None: 

613 list_conditions.append(" project_id=%s ") 

614 list_params.append(project_id) 

615 

616 if is_validated != None: 

617 list_conditions.append(" validated=%s ") 

618 list_params.append(is_validated) 

619 condition = " WHERE " + " AND ".join(list_conditions) if len(list_conditions) > 0 else "" 

620 query = "SELECT * FROM mtruser.mtr_factures " + condition 

621 cursor.execute(query, tuple(list_params)) 

622 res = cursor.fetchall() 

623 return res 

624 

625 def select_project_id_from_facture(self, path_facture): 

626 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

627 query = "SELECT project_id FROM mtruser.mtr_factures WHERE filename=%s" 

628 cursor.execute(query, (path_facture,)) 

629 res = cursor.fetchone() 

630 if res == None or "project_id" not in res: 

631 return None 

632 return res["project_id"] 

633 

634 def get_bill_validate(self, date_start): 

635 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

636 query = "SET lc_time = 'fr_FR.UTF-8';SELECT validated, count(*), string_agg(distinct month, ',') as months FROM mtruser.mtr_factures WHERE to_date(month, 'TMMonth YYYY') >= %s GROUP BY validated" 

637 # query = "SELECT validated, count(*), string_agg(distinct month, ',') as months FROM mtruser.mtr_factures WHERE created_at >= %s GROUP BY validated" 

638 cursor.execute(query, (date_start,)) 

639 res = cursor.fetchall() 

640 map_validated_count = {} 

641 map_validated_months = {} 

642 for r in res: 

643 map_validated_count[r["validated"]] = r["count"] 

644 map_validated_months[r["validated"]] = r["months"] 

645 if True not in map_validated_count: 

646 map_validated_count[True] = 0 

647 if False not in map_validated_count: 

648 map_validated_count[False] = 0 

649 if True not in map_validated_months: 

650 map_validated_months[True] = "" 

651 if False not in map_validated_months: 

652 map_validated_months[False] = "" 

653 return map_validated_count, map_validated_months 

654 

655 

656 

657 def get_min_bill_id_after_or_equal_date(self, date_start): 

658 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

659 query = "SELECT min(id) FROM mtruser.mtr_factures WHERE to_date(month, 'TMMonth YYYY') >= %s" 

660 cursor.execute(query, (date_start,)) 

661 res = cursor.fetchone() 

662 if res == None or "min" not in res: 

663 return None 

664 return res["min"] 

665 

666 def reset_facture(self, bill_id, nb_bill_expected = 0, verbose = False): 

667 # Check should have been done previously 

668 # Est-ce suffisnat ? 

669# client_for_rollback = self.client 

670# client_for_rollback.autocommit = False 

671 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

672 query = "WITH deleted AS (DELETE FROM mtruser.mtr_factures WHERE id>=%s AND validated=false RETURNING *) SELECT count(*) FROM deleted" 

673 cursor.execute(query, (bill_id,)) 

674 res = cursor.fetchone() 

675 print(" res : " + str(res)) 

676 if res == None or "count" not in res or res["count"] != nb_bill_expected: 

677 print("Didn't manage to make rollback works : " + str(res) + " nb_bill_expected : " + str(nb_bill_expected)) 

678 raise Exception("Inconsistent data, nothing deleted neither done !") 

679# self.client.rollback() 

680# return False 

681 else: 

682 self.client.commit() 

683 

684 query = "SELECT setval('mtruser_mtr_facture_id_seq', %s)" 

685 res = cursor.execute(query, (bill_id - 1,)) 

686 print(" res : " + str(res)) 

687 self.client.commit() 

688 

689 return True 

690 

691 def insert_query_log(self, project_id, user_id, endpoint, request_method, 

692 request_body = "", ip_address = "0.0.0.0", 

693 elapsed_time = 0): 

694 import logging 

695 logger = logging.getLogger() 

696 logger.info("inside insert_query_log from lpgss") 

697 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

698 

699 complete_insert_query = sql.SQL(""" 

700 INSERT INTO {} ( 

701 project_id, 

702 user_id, 

703 endpoint, 

704 request_method, 

705 request_body, 

706 ip_address, 

707 elapsed_time) VALUES (%s, %s, %s, %s, %s, %s, %s) 

708 RETURNING id""").format(sql.Identifier(*self.log_query_table.split('.'))) 

709 

710 print(complete_insert_query.as_string(self.client)) 

711 logger.info(complete_insert_query.as_string(self.client)) 

712 

713 cursor.execute(complete_insert_query, (project_id, user_id, endpoint, request_method, request_body, ip_address, elapsed_time)) 

714 all_fetch = cursor.fetchone() 

715 if len(all_fetch) == 1: 

716 query_id = all_fetch["id"] 

717 else : 

718 query_id = None 

719 print("ERROR exiting (or not)") 

720 

721 self.client.commit() 

722 cursor.close() 

723 return query_id 

724 

725 def update_query_end(self, query_id, response_status = 200, response_body = "", 

726 elapsed_time = 0, cost = 0, detailed_time = {}, 

727 verbose = False): 

728 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

729 

730 detailed_time_str = str(detailed_time)[:1023] 

731 complete_update_query = sql.SQL(""" 

732 UPDATE {} 

733 SET 

734 response_status = %s, 

735 response_body = %s, 

736 elapsed_time = %s, 

737 cost = %s, 

738 detailed_time = %s, 

739 finished_at=NOW() 

740 WHERE id=%s""").format(sql.Identifier(*self.log_query_table.split('.'))) 

741 

742 if verbose: 

743 print(complete_update_query.as_string(self.client)) 

744 

745 try : 

746 cursor.execute(complete_update_query, (response_status, str(response_body), elapsed_time, cost, detailed_time_str, query_id)) 

747 self.client.commit() 

748 except Exception as e: 

749 print("Error in update_query_end: " + str(e)) 

750 self.client.rollback() 

751 finally: 

752 cursor.close() 

753 

754 def get_queries(self, project_id): 

755 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

756 select_query = sql.SQL("SELECT * FROM {} WHERE project_id=%s ORDER BY id DESC LIMIT 100").format(sql.Identifier(*self.log_query_table.split('.'))) 

757 cursor.execute(select_query, (project_id,)) 

758 all_fetch = cursor.fetchall() 

759 return all_fetch 

760 

761 def check_table_exists(self, schema_and_table_name): 

762 table_name = schema_and_table_name 

763 table_schema = "" 

764 if schema_and_table_name == None: 

765 return False 

766 if "." in schema_and_table_name: 

767 table_name = schema_and_table_name.split(".")[1] if len(schema_and_table_name.split(".")) == 2 else "" 

768 table_schema = schema_and_table_name.split(".")[0] if len(schema_and_table_name.split(".")) == 2 else "" 

769 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

770 query = "SELECT EXISTS (SELECT FROM information_schema.tables WHERE table_name = %s and table_schema = %s);" 

771 cursor.execute(query, (table_name, table_schema,)) 

772 exists = cursor.fetchone()["exists"] 

773 return exists 

774 

775 def get_documents(self, table_name, document_id = None, chunk_id = None, 

776 limit = 100, offset = 0, 

777 order_by_recent = False): 

778 if table_name == None: 

779 return [] 

780 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

781 table_parts = table_name.split('.') 

782 params = [] 

783 

784 if chunk_id != None: 

785 query = sql.SQL("SELECT * FROM {} WHERE id=%s").format(sql.Identifier(*table_parts)) 

786 params.append(str(document_id) + "_" + str(chunk_id)) 

787 elif document_id != None: 

788 query = sql.SQL("SELECT * FROM {} WHERE document_id=%s").format(sql.Identifier(*table_parts)) 

789 params.append(document_id) 

790 else: 

791 query = sql.SQL("SELECT * FROM {}").format(sql.Identifier(*table_parts)) 

792 

793 if order_by_recent: 

794 query = sql.SQL(query.as_string(self.client) + " ORDER BY created_at DESC") 

795 if limit != None and limit != 0: 

796 query = sql.SQL(query.as_string(self.client) + " LIMIT %s OFFSET %s") 

797 params.extend([limit, offset]) 

798 

799 print(query.as_string(self.client)) 

800 cursor.execute(query, tuple(params) if params else None) 

801 list_res = cursor.fetchall() 

802 from lib.lib_util import get_id_order_document 

803 try : 

804 list_res_ordered = list(sorted(list_res, key=lambda x : get_id_order_document(x["id"]))) 

805 except Exception as e: 

806 print(str(e)) 

807 print("ERROR treated as warning we don't re-order the list") 

808 list_res_ordered = list_res 

809 return list_res_ordered 

810 

811 def get_descs(self, table_name, document_id = None, limit = 0, offset = 0): 

812# if limit > 0: 

813# print(" Limit not yet managed in get_descs ") 

814# return [] 

815 

816 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

817 table_parts = table_name.split('.') 

818 params = [] 

819 

820 if document_id != None: 

821 query = sql.SQL("SELECT * FROM {} WHERE id=%s").format(sql.Identifier(*table_parts)) 

822 params.append(document_id) 

823 else: 

824 query = sql.SQL("SELECT * FROM {}").format(sql.Identifier(*table_parts)) 

825 

826 if limit > 0: 

827 query = sql.SQL(query.as_string(self.client) + " LIMIT %s OFFSET %s") 

828 params.extend([limit, offset]) 

829 

830 print(" query desc : " + query.as_string(self.client)) 

831 cursor.execute(query, tuple(params) if params else None) 

832 all_result = cursor.fetchall() 

833 return all_result 

834 

835# TO TEST 

836 # TODO VR avoid sql injection ! => done je crois 21-6-23 

837 def update_document(self, table_name, document_id, new_values): 

838 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

839 tuple_join = () 

840 list_clause = [] 

841 for col, val in new_values.items(): 

842 tuple_join += (str(val),) 

843 if col == "source": # to avoid sql injection (pg do the rest) 

844 list_clause.append("source = %s") 

845 if col == "source_id": 

846 list_clause.append("source_id = %s") 

847 if col == "content": 

848 list_clause.append("content = %s") 

849 if col == "document_id": 

850 list_clause.append("document_id = %s") 

851 if col == "author": 

852 list_clause.append("author = %s") 

853 if col == "url": 

854 list_clause.append("url = %s") 

855 

856 set_clause = ', '.join(list_clause) 

857 query = f"UPDATE {table_name} SET {set_clause} WHERE id = '{document_id}'" 

858 self.logger.info(query) 

859 self.logger.info(tuple_join) 

860 cursor.execute(query, tuple_join) 

861 self.client.commit() 

862 

863 def delete_document(self, table_name, document_id): 

864 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

865 cursor.execute(f"DELETE FROM {table_name} WHERE document_id = '{document_id}'") 

866 self.client.commit() 

867 

868 # - [ ] TODO VR : rename_document est tout pourri en fait car on ne modifie pas les id, en changeant leur nom 

869 def rename_document(self, table_name, document_id, rename): 

870 

871 list_chunks = self.get_documents(table_name, document_id=document_id, chunk_id=None) 

872 

873 list_old_new_id = [] 

874 for l in list_chunks: 

875 old_id = l["id"] 

876 new_id = l["id"].replace(document_id, rename) 

877 list_old_new_id.append({"old_id":old_id, "new_id":new_id}) 

878 

879 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

880 for ch in list_old_new_id: 

881 new_id = ch["new_id"] 

882 old_id = ch["old_id"] 

883 cursor.execute(f"UPDATE {table_name} SET id='{new_id}' WHERE id = '{old_id}'") 

884 self.client.commit() 

885 

886 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

887 cursor.execute(f"UPDATE {table_name} SET document_id='{rename}' WHERE document_id = '{document_id}'") 

888 self.client.commit() 

889 

890 def get_configuration_option(self): 

891 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

892 select_query = """ 

893 SELECT layer_type, string_agg(configuration, ',') as options, string_agg(class_name, ',') as list_class_name, string_agg(module_name, ',') as list_module_name 

894 FROM mtradmin.brick_layer_custom GROUP BY layer_type; 

895 """ 

896 cursor.execute(select_query) 

897 all_fetch = cursor.fetchall() 

898 self.client.commit() 

899 return all_fetch 

900 

901 def get_configuration_option_one(self, configuration): 

902 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

903 select_query = f""" 

904 SELECT * 

905 FROM mtradmin.brick_layer_custom WHERE configuration = '{configuration}'; 

906 """ 

907 cursor.execute(select_query) 

908 all_fetch = cursor.fetchall() 

909 self.client.commit() 

910 return all_fetch 

911 

912 def build_insert_query_configuration_aux(self, list_data): 

913 base_query = """ 

914 INSERT INTO mtradmin.configuration (layer_type, configuration, user_id, project_id) 

915 VALUES %s 

916 ON CONFLICT (layer_type, user_id, project_id) 

917 DO UPDATE SET 

918 configuration = EXCLUDED.configuration, 

919 modified_at = NOW(); 

920 """ 

921 formatted_data = ', '.join(['(%s, %s, %s, %s)' % tuple(map(lambda x: "'%s'" % x if isinstance(x, str) else x, data)) for data in list_data]) 

922 final_query = base_query % formatted_data 

923 return final_query 

924 

925 def update_configuration(self, list_data): 

926 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

927 insert_query = self.build_insert_query_configuration_aux(list_data) 

928 print(" insert_query : " + str(insert_query)) 

929 cursor.execute(insert_query) 

930 self.client.commit() 

931 

932 def load_configuration(self, user_id, project_id): 

933 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

934 list_conditions = [] 

935 params = [] 

936 if str(user_id).isdigit(): 

937 list_conditions.append(" user_id=%s ") 

938 params.append(user_id) 

939 if str(project_id).isdigit(): 

940 list_conditions.append(" project_id=%s ") 

941 params.append(project_id) 

942 string_condition = " WHERE" + " AND ".join(list_conditions) if len(list_conditions) > 0 else "" 

943 select_configuration = "SELECT * FROM mtradmin.configuration " + string_condition 

944 print(" select_configuration : " + str(select_configuration)) 

945 cursor.execute(select_configuration, tuple(params) if params else None) 

946 all_result = cursor.fetchall() 

947 return all_result 

948 

949 def load_conf_project(self, project_id, 

950 key = None): 

951 current_conf_json = {} 

952 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

953# query = "SELECT configuration FROM mtruser.project where project_id=%s;" 

954# cursor.execute(query, (project_id)) 

955 if key != None and key != "": 

956 list_keys = key.split("/") 

957 # Note: key is used for column path extraction, should be validated by caller 

958 key_with_arrow = "->" + "->".join(list(map(lambda x : "'" + x + "'", list_keys))) + " as " + key.replace("/", "_") 

959 else : 

960 key_with_arrow = "" 

961 if project_id != 'all': 

962 print("TODO REFACTO remove load_sub_json in external function and voila") 

963 key_with_arrow = "" 

964 

965 if project_id == 'all': 

966 query = "SELECT id as project_id, configuration" + key_with_arrow + " FROM mtruser.project" 

967 cursor.execute(query) 

968 current_conf = cursor.fetchall() 

969# if res != None and "configuration" in res and res["configuration"] != None: 

970# current_conf = res["configuration"] 

971 #current_conf = {"all" : current_conf} 

972 else : 

973 query = "SELECT id as project_id, configuration" + key_with_arrow + " FROM mtruser.project WHERE id=%s" 

974 cursor.execute(query, (project_id,)) 

975 res = cursor.fetchone() 

976 if res != None and "configuration" in res and res["configuration"] != None: 

977 current_conf = res["configuration"] 

978 else: 

979 current_conf = "{}" 

980 

981 print(" current_conf_json : " + str(current_conf)[:100]) 

982 if type(current_conf) == type({}) or project_id == 'all': 

983 current_conf_json = current_conf 

984 else : 

985 print("DEPRECATED 10-10 to delete 11-11") 

986 try : 

987 current_conf_json = json.loads(current_conf) 

988 except Exception as e: 

989 print(str(e)) 

990 print("load_conf_project failed set to {} ") 

991 current_conf_json = {} 

992 return current_conf_json 

993 

994 def update_configuration_project(self, project_id, data_json, replace = False): 

995 

996# import json 

997# current_conf_as_json = json.loads(current_conf) 

998 

999# current_conf.update(data_json) 

1000 # VR 18-11-23 : je prefere supprimer les anciennes configuration par exemple des datous qui ont été supprimé => test à mettre à jour ! 

1001 # D'apères les tests il y a un truc ou on a pas sauver ce qu'il fallait, dans certains cas configuration est egale a 'data': '{"store_in_project": "true", "project_id": "1"}'} 

1002 # Je ne sqis pas lequel est bien utilisé 

1003 if replace: 

1004 current_conf = data_json 

1005 else : 

1006 current_conf = dict(self.load_conf_project(project_id)) 

1007 if current_conf == None: 

1008 current_conf = {} 

1009 # fix un bug mais fais clairement planter les tests ! 

1010# current_conf.update(data_json) 

1011 

1012 from pydantic.utils import deep_update 

1013 

1014 current_conf = deep_update(current_conf, data_json) 

1015 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1016 query = "UPDATE mtruser.project SET configuration=%s where id=%s;" 

1017 import json 

1018 cursor.execute(query, (json.dumps(current_conf), project_id)) 

1019 

1020 

1021 def get_datou_step_template(self): 

1022 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1023 select_query = """ 

1024 SELECT * 

1025 FROM mtrdatou.datou_step_template; 

1026 """ 

1027 cursor.execute(select_query) 

1028 all_fetch = cursor.fetchall() 

1029 return all_fetch 

1030 

1031 def get_datou_user(self, user_id, list_datou_ids = []): 

1032 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1033 params = [] 

1034 if user_id != None: 

1035 select_query = sql.SQL("SELECT * FROM {} WHERE (public OR owner_id = %s)").format(sql.Identifier(*self.datou_table.split('.'))) 

1036 params.append(user_id) 

1037 else: 

1038 select_query = sql.SQL("SELECT * FROM {} WHERE public").format(sql.Identifier(*self.datou_table.split('.'))) 

1039 

1040 if len(list_datou_ids) > 0: 

1041 placeholders = ','.join(['%s'] * len(list_datou_ids)) 

1042 select_query = sql.SQL(select_query.as_string(self.client) + " AND id IN (" + placeholders + ")") 

1043 params.extend(list_datou_ids) 

1044 

1045 cursor.execute(select_query, tuple(params) if params else None) 

1046 all_fetch = cursor.fetchall() 

1047 return all_fetch 

1048 

1049 def get_datou_step_user(self, user_id, list_datou_ids = [], verbose = False): 

1050 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1051 params = [] 

1052 if user_id != None: 

1053 condition_right = " md.public OR md.owner_id = %s " 

1054 params.append(user_id) 

1055 else: 

1056 condition_right = " md.public " 

1057 

1058 select_query = sql.SQL(""" 

1059 SELECT mds.*, mddst.name 

1060 FROM mtrdatou.datou_step mds, {} md, 

1061 mtrdatou.datou_step_template mddst 

1062 WHERE mddst.id=mds.datou_step_type_id 

1063 AND mds.mtr_datou_id=md.id 

1064 AND (""" + condition_right + """)""").format(sql.Identifier(*self.datou_table.split('.'))) 

1065 

1066 if len(list_datou_ids) > 0: 

1067 placeholders = ','.join(['%s'] * len(list_datou_ids)) 

1068 select_query = sql.SQL(select_query.as_string(self.client) + " AND md.id IN (" + placeholders + ")") 

1069 params.extend(list_datou_ids) 

1070 

1071 if verbose: 

1072 print(select_query.as_string(self.client)) 

1073 cursor.execute(select_query, tuple(params) if params else None) 

1074 all_fetch = cursor.fetchall() 

1075 return all_fetch 

1076 

1077 # TODO optimize with loop inside cursor 

1078 def update_datou_param_json(self, update_datou_json): # pragma no cover TO REMOVE 17-7-23, don't want to complexify interface jquery to "spare" ids ! 

1079 for d in update_datou_json: 

1080 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1081 update_query = """ 

1082 UPDATE mtrdatou.datou_step set param_json=%s 

1083 WHERE id='%s' 

1084 """ 

1085 import json 

1086 tuple = (json.dumps(d["param_json"]), d["id"]) 

1087 print(update_query) 

1088 cursor.execute(update_query, tuple) 

1089 self.client.commit() 

1090 cursor.close() 

1091 

1092 def update_datou(self, user_id, datou_name, datou_id_to_update, datou_is_public = None): 

1093 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1094 list_id_col_name = [] 

1095 list_id_col_val = [] 

1096 list_update_command = [] 

1097 tuple_value = () 

1098 suffix_ret_id_or_not = " RETURNING ID;" 

1099 if datou_id_to_update != None: 

1100 list_id_col_name.append("id") 

1101 list_id_col_val.append("%s") 

1102 tuple_value += (datou_id_to_update,) 

1103# suffix_ret_id_or_not = ";" # Commenting simplify the code at the end 

1104 

1105 list_id_col_name.append("filename") 

1106 list_id_col_val.append("%s") 

1107 tuple_value += (datou_name,) 

1108 list_update_command.append(" filename = EXCLUDED.filename ") 

1109 

1110 list_id_col_name.append("owner_id") 

1111 list_id_col_val.append("%s") 

1112 tuple_value += (user_id,) 

1113 

1114 if datou_is_public != None: 

1115 list_id_col_name.append("public") 

1116 list_id_col_val.append("%s") 

1117 tuple_value += (datou_is_public,) 

1118 list_update_command.append(" public = EXCLUDED.public ") 

1119 

1120 id_col_name = ", ".join(list_id_col_name) 

1121 id_col_val = ", ".join(list_id_col_val) 

1122 set_update = " SET " + ", ".join(list_update_command) if len(list_update_command) > 0 else "" 

1123 

1124 insert_query = sql.SQL("""INSERT INTO {} ( """ + (id_col_name) + """ ) 

1125 VALUES ( """ + (id_col_val) + """ ) 

1126 ON CONFLICT (id) DO UPDATE 

1127-- ON CONFLICT ON CONSTRAINT unique_userid_filename DO UPDATE 

1128 """ + set_update + suffix_ret_id_or_not).format(sql.Identifier(*self.datou_table.split('.'))) 

1129 print(insert_query.as_string(self.client)) 

1130 

1131 cursor.execute(insert_query, tuple_value) 

1132 one_result = cursor.fetchone() 

1133 datou_id_to_return = one_result["id"] 

1134 cursor.close() 

1135 self.client.commit() 

1136 

1137 return datou_id_to_return 

1138 

1139 

1140 # TODO VR : comment faut-il mieux gérer les step_type_id, depuis l'interface ou les rechercher ? 

1141 # TODO VR : doit-on dupliquer cet info du coup 

1142 

1143# id SERIAL PRIMARY KEY, 

1144# mtr_datou_id INTEGER, 

1145# datou_step_type_id INTEGER, 

1146# param_json JSON, 

1147# created_at TIMESTAMP, 

1148# modified_at TIMESTAMP, 

1149# order_step INTEGER NOT NULL, 

1150 

1151 def update_datou_step(self, list_steps): 

1152 if len(list_steps) == 0: 

1153 return [] 

1154 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1155 # TODO VR for empty datou now ? 

1156 # TODO VR uses datou_step_id when exists (need to make two type of query) 

1157 # TODO nettoyer 

1158 delete_query = "DELETE FROM mtrdatou.datou_step WHERE mtr_datou_id=%s" 

1159 cursor.execute(delete_query, (list_steps[0]["datou_id"],)) 

1160 

1161# (id_col_name, id_col_val) = ("id, ", "%s, ") if datou_id_to_update is not None else ("", "") 

1162 insert_query = """INSERT INTO mtrdatou.datou_step (mtr_datou_id, datou_step_type_id, param_json, order_step) 

1163 VALUES (%s, %s, %s, %s) 

1164 RETURNING ID; 

1165 """ 

1166 list_data = [] 

1167 for l in list_steps: 

1168 import json 

1169 # json.dumps( ?? 

1170 param_json_str = json.dumps(l["param_json"]) if type(l["param_json"]) == type({}) else l["param_json"] 

1171 order_step = l["order"] if "order" in l else l["order_step"] # TODO VR backward compatibility while chaning filename to name 

1172 tuple_value = (l["datou_id"], int(l["datou_step_type_id"]), param_json_str, int(order_step)) 

1173 

1174 list_data.append(tuple_value) 

1175 

1176 print(insert_query) 

1177 cursor.executemany(insert_query, list_data) 

1178 try : 

1179 all_results = cursor.fetchall() 

1180 self.client.commit() # au cas ou le comportement est que le curseur est détruit au commit (mais rend le commit inutile du coup) 

1181 except Exception as e: 

1182 print(str(e)) 

1183 print("MAis ce n'est sans doute pas bloquant vu que je supprime tout") 

1184 all_results = [] 

1185 cursor.close() 

1186 

1187 return all_results 

1188 

1189 # TODO VR : NEED REFACTO ON DELETE CASCADE 

1190 def delete_datou(self, mtr_datou_id, user_id): 

1191 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1192 delete_query = sql.SQL("DELETE FROM {} WHERE id=%s AND owner_id=%s").format(sql.Identifier(*self.datou_table.split('.'))) 

1193 print(delete_query.as_string(self.client)) 

1194 cursor.execute(delete_query, (mtr_datou_id, user_id)) 

1195 cursor.close() 

1196 self.client.commit() 

1197# return "" 

1198 

1199 def create_group(self, user_id, name = "self_group"): 

1200 query = "INSERT INTO mtrright.group (owner_id, name) VALUES (%s, %s) ON CONFLICT (owner_id, name) DO NOTHING RETURNING id;" 

1201 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1202 cursor.execute(query, (user_id, name)) 

1203 self.client.commit() 

1204 one_result = cursor.fetchone() 

1205 return one_result 

1206 

1207 # TODO VR 13-7 : est-ce bien utile d'avoir ces guillemets là ? 

1208 def is_owner(self, table_name_object, user_id, project_id): 

1209 table_parts = table_name_object.split('.') 

1210 query = sql.SQL("SELECT id FROM {} WHERE owner_id=%s AND id=%s").format(sql.Identifier(*table_parts)) 

1211 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1212 cursor.execute(query, (user_id, project_id)) 

1213 one_result = cursor.fetchone() 

1214 if one_result == None: 

1215 return False 

1216 else : 

1217 return True 

1218 

1219 def get_role_on_object(self, table_name_group_object, user_id, object_id = None): 

1220 table_parts = table_name_group_object.split('.') 

1221 params = [user_id] 

1222 

1223 if object_id != None: 

1224 query = sql.SQL("""SELECT mtgr.role, gu.role, gu.user_id, mtgr.object_id, g.name, g.id, g.owner_id, mtu.mail 

1225 FROM {} mtgr, 

1226 mtrright.group_user gu, 

1227 mtrright.group g, 

1228 mtruser.user mtu 

1229 WHERE mtu.id=g.owner_id 

1230 AND g.id=gu.group_id 

1231 AND gu.user_id=%s 

1232 AND g.id=mtgr.group_id 

1233 AND mtgr.object_id=%s""").format(sql.Identifier(*table_parts)) 

1234 params.append(object_id) 

1235 else: 

1236 query = sql.SQL("""SELECT mtgr.role, gu.role, gu.user_id, mtgr.object_id, g.name, g.id, g.owner_id, mtu.mail 

1237 FROM {} mtgr, 

1238 mtrright.group_user gu, 

1239 mtrright.group g, 

1240 mtruser.user mtu 

1241 WHERE mtu.id=g.owner_id 

1242 AND g.id=gu.group_id 

1243 AND gu.user_id=%s 

1244 AND g.id=mtgr.group_id""").format(sql.Identifier(*table_parts)) 

1245 

1246 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1247 cursor.execute(query, tuple(params)) 

1248 all_fetch = cursor.fetchall() 

1249 return all_fetch 

1250 

1251 # - [ ] TODO VR : peut-on merger cette fonction avec get_role_on_object (ou le object_id est optionnel) 

1252 # - [ ] TODO VR : le user_role ne devrait sans doute pas exister (sauf pour administrer les groupes grrrr !) 

1253 # - [ ] TODO VR : ne devrait-on pas récupérer ici les email ? Bon on va voir si ce sont les memes 

1254 # - [ ] TODO VR : on peut vouloir faire un distinct user_id, mais en fait cela pose la question de savoir comment faire les changements de droits ! :-) => il y a plein de cas ! 

1255 def get_all_role_on_object(self, table_name_group_object, object_id): 

1256 table_parts = table_name_group_object.split('.') 

1257 query = sql.SQL("""SELECT mtgr.role as group_role, gu.role as user_role, gu.user_id, mtgr.object_id, g.name, g.id, g.owner_id, mtu.mail 

1258 FROM {} mtgr, 

1259 mtrright.group_user gu, 

1260 mtrright.group g, 

1261 mtruser.user mtu 

1262 WHERE mtu.id=gu.user_id 

1263 AND g.id=gu.group_id 

1264 AND g.id=mtgr.group_id 

1265 AND mtgr.object_id=%s""").format(sql.Identifier(*table_parts)) 

1266 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1267 cursor.execute(query, (object_id,)) 

1268 all_fetch = cursor.fetchall() 

1269 return all_fetch 

1270 

1271 def get_all_member_groups(self, list_group): 

1272 placeholders = ','.join(['%s'] * len(list_group)) 

1273 query = """SELECT gu.role as user_role, gu.user_id, g.name, g.id as group_id, g.owner_id, mtu.mail, mtu.id 

1274 FROM mtrright.group_user gu, 

1275 mtrright.group g, 

1276 mtruser.user mtu 

1277 WHERE mtu.id=gu.user_id 

1278 AND g.id=gu.group_id 

1279 AND g.id IN (""" + placeholders + """)""" 

1280 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1281 cursor.execute(query, tuple(list_group)) 

1282 all_fetch = cursor.fetchall() 

1283 return all_fetch 

1284 

1285 def get_email_from_user_ids(self, list_user_ids): 

1286 if len(list_user_ids) == 0: 

1287 return [] 

1288 

1289 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1290 placeholders = ','.join(['%s'] * len(list_user_ids)) 

1291 query = sql.SQL("SELECT id, mail FROM {} WHERE id IN (" + placeholders + ")").format(sql.Identifier(*self.user_table.split('.'))) 

1292 cursor.execute(query, tuple(list_user_ids)) 

1293 all_fetch = cursor.fetchall() 

1294 return all_fetch 

1295 

1296 def get_all_users(self): 

1297 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1298 query = sql.SQL("SELECT * FROM {} ORDER BY modified_at DESC").format(sql.Identifier(*self.user_table.split('.'))) 

1299 cursor.execute(query) 

1300 all_fetch = cursor.fetchall() 

1301 return all_fetch 

1302 

1303 def get_group_by_owner(self, user_id, name=None): 

1304 params = [user_id] 

1305 if name != None: 

1306 query = "SELECT * FROM mtrright.group WHERE owner_id=%s AND name=%s" 

1307 params.append(name) 

1308 else: 

1309 query = "SELECT * FROM mtrright.group WHERE owner_id=%s" 

1310 

1311 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1312 cursor.execute(query, tuple(params)) 

1313 all_fetch = cursor.fetchall() 

1314 return all_fetch 

1315 

1316 def create_group(self, user_id, name): 

1317 query = "INSERT INTO mtrright.group (owner_id, name) VALUES (%s, %s) RETURNING ID;" 

1318 

1319 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1320 cursor.execute(query, (user_id, name)) 

1321 one_fetch = cursor.fetchone() 

1322 return one_fetch 

1323 

1324 def get_projects(self, list_project_ids): 

1325 print(" TODO project or maybe duplicate => YES INDEED ! but could be refacto ! ") 

1326 return [] 

1327 

1328 def create_invitation(self, user_id, mail, action, param_json, link, token): 

1329 # - [x] TODO add valid_until => one day ! 

1330 # - [x] TODO used_at => using updated_at 

1331 # - [x] TODO add token 

1332 query = """INSERT INTO mtrright.invitation (owner_id, mail, action, param_json, link, token) 

1333 VALUES (%s, %s, %s, %s, %s, %s) RETURNING id;""" 

1334 import json 

1335 one_val = (user_id, mail, action, json.dumps(param_json), link, token) 

1336 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1337 cursor.execute(query, one_val) 

1338 self.client.commit() 

1339 all_fetch = cursor.fetchone() 

1340 return all_fetch 

1341 

1342 def get_invitation_info_from_token(self, token): 

1343 query = "SELECT * FROM mtrright.invitation WHERE token=%s" 

1344 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1345 cursor.execute(query, (token,)) 

1346 all_fetch = cursor.fetchone() 

1347 return all_fetch 

1348 

1349 

1350# 

1351# log_send_mail 

1352# send_at 

1353# valid_until 

1354 

1355 pass 

1356 

1357 def add_role_project_to_user(self, group_id, project_id, role): 

1358 query = """INSERT INTO mtrright.group_project (group_id, object_id, role) 

1359 VALUES (%s, %s, %s) on CONFLICT(group_id, object_id, role) DO NOTHING;""" 

1360 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1361 cursor.execute(query, (group_id, project_id, role)) 

1362 self.client.commit() 

1363 

1364 def remove_role_to_user(self, group_id, project_id, role = None): 

1365 params = [group_id, project_id] 

1366 if role != None: 

1367 query = """DELETE FROM mtrright.group_project WHERE group_id=%s AND object_id=%s AND role=%s""" 

1368 params.append(role) 

1369 else: 

1370 query = """DELETE FROM mtrright.group_project WHERE group_id=%s AND object_id=%s""" 

1371 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1372 cursor.execute(query, tuple(params)) 

1373 self.client.commit() 

1374 

1375 def add_role_to_user_group_buggue(self):#, group_id, user_id, role): 

1376 query = """INSERT INTO mtrright.group_user (group_id, user_id, role) 

1377 VALUES (49, 9, 'admin') on duplicate(group_id, user_id, role) DO NOTHING;""" 

1378 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1379 cursor.execute(query) #, (group_id, user_id, role)) 

1380 self.client.commit() 

1381 

1382 def add_role_to_user_group(self, group_id, user_id, role): 

1383 query = """INSERT INTO mtrright.group_user (group_id, user_id, role) 

1384 VALUES (%s, %s, %s) on CONFLICT(group_id, user_id, role) DO NOTHING;""" 

1385 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1386 cursor.execute(query, (group_id, user_id, role)) 

1387 self.client.commit() 

1388 

1389 def remove_role_group_to_user(self, group_id, user_id): 

1390 select = "SELECT * FROM mtrright.group_user WHERE group_id=%s AND user_id=%s" 

1391 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1392 cursor.execute(select, (group_id, user_id)) 

1393 all_fetch = cursor.fetchall() 

1394 if len(all_fetch) == 0: 

1395 print("Internal error we could not find the group or user") 

1396 else : 

1397 query = """DELETE FROM mtrright.group_user WHERE group_id=%s AND user_id=%s""" 

1398 cursor.execute(query, (group_id, user_id)) 

1399 self.client.commit() 

1400 

1401 def update_invitation_used(self, id, user_id): 

1402 query = "UPDATE mtrright.invitation SET user_id=%s, used_at=NOW() WHERE id=%s" 

1403 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1404 cursor.execute(query, (user_id, id)) 

1405 self.client.commit() 

1406 

1407# TODO log_activation 

1408 pass 

1409 

1410# import psycopg2 

1411 

1412 # needs to be replaced by correctly configured psycopg2.extras.Json 

1413 # https://www.psycopg.org/docs/extras.html#json-adaptation 

1414 # https://www.psycopg.org/docs/extras.html#jsonb-adaptation 

1415 # needs to be replaced by correclty configured upsert_audit_info 

1416 une_var = [ 

1417{"variable": "project_id", "type": "text"}, 

1418{"variable": "audit_info", "type": "json"}, 

1419{"variable": "mtr_datou_id", "type": "text"}, 

1420{"variable": "mtr_user_id", "type": "text"}, 

1421{"variable": "launched_at", "type": "text"} 

1422] 

1423 def save_audit_info(self, project_id, 

1424 safia_doc_id, 

1425 audit_info, 

1426 hash_id_treatment, 

1427 mtr_datou_id, 

1428 mtr_user_id, 

1429 launched_at): 

1430 if mtr_datou_id < 0: 

1431 return None 

1432 # JE veux insérer ces données dans la table mtraudit.mtr_datou_exec 

1433 insert_sql = """ 

1434 INSERT INTO mtraudit.mtr_datou_exec ( 

1435 mtr_datou_id, 

1436 launched_at, 

1437 user_id, 

1438 project_id, 

1439 safia_doc_id, 

1440 hash_id_treatment, 

1441 audit_info 

1442 ) VALUES (%s, %s, %s, %s, %s, %s, %s) 

1443 """ 

1444 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1445 res = cursor.execute(insert_sql, ( 

1446 mtr_datou_id, 

1447 launched_at, 

1448 mtr_user_id, 

1449 project_id, 

1450 safia_doc_id, 

1451 hash_id_treatment, 

1452 json.dumps(audit_info, default=str) 

1453# psycopg2Json(audit_info) c'est assez iunteressant pour gérer tout cela ! 

1454 )) 

1455 self.client.commit() 

1456 return res 

1457 

1458 def upsert_audit_info(self, hash_id_treatment, 

1459 list_json_update = [ 

1460{"variable": "project_id", "type": "text"}, 

1461{"variable": "safia_doc_id", "type": "text"}, 

1462{"variable": "audit_info", "type": "json"}, 

1463{"variable": "mtr_datou_id", "type": "text"}, 

1464{"variable": "user_id", "type": "text"}, 

1465{"variable": "launched_at", "type": "text"}, 

1466{"variable": "id_file", "type": "text"} 

1467], 

1468 input_values = {}, 

1469 verbose = False): 

1470 list_variables = ["hash_id_treatment"] 

1471 list_values = ["%s"] 

1472 list_updates = [] 

1473 tuple_values = (hash_id_treatment,) 

1474 

1475 for one_value in list_json_update: 

1476 variable = one_value["variable"] 

1477 if variable not in input_values or input_values[variable] == None: 

1478 sys.stdout.write("ç") 

1479 continue 

1480 

1481 value = input_values[variable] 

1482 type_variable = one_value["type"] if "type" in one_value else "text" 

1483 

1484 list_variables.append(variable) 

1485 list_values.append("%s") 

1486 if type_variable == "json": 

1487 try : 

1488 new_val = json.dumps(value, default=str) 

1489 except Exception as e: 

1490 print(str(e)) 

1491 print("ERROR in upsert_audit_info, will try to save it anyway : hash_id_treatment " + str(hash_id_treatment)) 

1492 from lib.lib_util import remove_circular_refs 

1493 value = remove_circular_refs(value) 

1494 new_val = json.dumps(value, default=str) 

1495# new_val = str(value) 

1496 try : 

1497 tuple_values += (new_val,) 

1498 except Exception as e: 

1499 print(str(e)) 

1500 print("ERROR in upsert_audit_info cat tuple") 

1501 tuple_values += ("",) 

1502# tuple_values += (psycopg2Json) 

1503# tuple_values += (psycopg2.extras.Json(value),) 

1504 elif type_variable == "text": 

1505 tuple_values += (value,) 

1506 else: 

1507 print("Unsupported type : " + str(type_variable)) 

1508 continue 

1509 list_updates.append(f"{variable} = EXCLUDED.{variable}") 

1510 

1511 if len(list_variables) == 0: 

1512 return None 

1513 command_sql = f""" 

1514 INSERT INTO mtraudit.mtr_datou_exec ({', '.join(list_variables)}) 

1515 VALUES ({', '.join(list_values)}) 

1516 """ 

1517 if len(list_updates) > 0: 

1518 command_sql += f""" 

1519 ON CONFLICT(hash_id_treatment) DO UPDATE SET 

1520 {', '.join(list_updates)}; 

1521 """ 

1522 else: 

1523 command_sql += " ON CONFLICT(hash_id_treatment) DO NOTHING;" 

1524 if verbose: 

1525 print(command_sql) 

1526 print(" length data to insert : " + str(len(str(tuple_values)))) 

1527 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1528 cursor.execute(command_sql, tuple_values) 

1529 self.client.commit() 

1530 

1531 # just for test, needs to be deprecated soon ! 8-5-24 

1532 # deprecated on 26-9-24 

1533 def update_audit_info(self, hash_id_treatment, id_file): 

1534 update_sql = """ 

1535 INSERT INTO mtraudit.mtr_datou_exec (hash_id_treatment, id_file) 

1536 VALUES(%s, %s) 

1537 ON CONFLICT(hash_id_treatment) DO UPDATE SET id_file=EXCLUDED.id_file; 

1538 """ 

1539 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1540 cursor.execute(update_sql, (hash_id_treatment, id_file)) 

1541 self.client.commit() 

1542 

1543 def load_data_audit(self, hash_id_treatment = None, 

1544 user_id = None, 

1545 col_csv = "id,id_file,mtr_datou_id,created_at,launched_at,modified_at,deleted_at,valid_until,user_id,project_id,safia_doc_id,hash_id_treatment,cdn,input_data,nb_page,nb_modif,input_file,audit_resume,info_anon,info_customer,info_lab,info_cdn,info_back,info_date,info_suivi,info_action,info_usage_integration,info_ml,info_init,info_extract,info_consolidate,info_result,info_exec,info_page,info_stat", 

1546 output_type="dict", 

1547 limit = None, 

1548 offset = 0, 

1549 condition = []): # or pd 

1550 list_conditions = [] 

1551 params = [] 

1552 if hash_id_treatment != None: 

1553 list_conditions.append(" hash_id_treatment = %s") 

1554 params.append(hash_id_treatment) 

1555 col_csv = "*" 

1556 else: 

1557 if col_csv == None: 

1558 col_csv = "id,mtr_datou_id,created_at,launched_at,modified_at,deleted_at,valid_until,user_id,project_id,safia_doc_id,hash_id_treatment,cdn" 

1559 

1560 for one_cond in condition: 

1561 type_condition = one_cond["type"] if "type" in one_cond else "EQUAL" 

1562 variable = one_cond["variable"] 

1563 # Note: variable is a column name - should be validated/sanitized by caller to prevent injection 

1564 if "value" in one_cond: 

1565 value = one_cond["value"] 

1566 data_type = one_cond["data_type"] if "data_type" in one_cond else "text" 

1567 cast = "" 

1568 if data_type == "int": 

1569 cast = "::integer" 

1570 

1571 rel = "=" # default EQUAL 

1572 if type_condition == "NOTEQUAL": 

1573 rel = "!=" 

1574 elif type_condition == "GREATER": 

1575 rel = ">" 

1576 

1577 if value == "formula": 

1578 # WARNING: Formula is potentially unsafe - should be validated by caller 

1579 formula = one_cond["formula"] 

1580 list_conditions.append(f"{variable}{cast} {rel} {formula}") 

1581 else: 

1582 list_conditions.append(f"{variable}{cast} {rel} %s") 

1583 params.append(value) 

1584 else: 

1585 list_conditions.append(f"{variable} IS NOT NULL") 

1586 

1587 cond_sql = "" 

1588 if len(list_conditions) > 0: 

1589 cond_sql = " WHERE " + " AND ".join(list_conditions) 

1590 

1591 order_by = "" 

1592 if col_csv != "count(*)" and col_csv != "sum(nb_page)": 

1593 order_by = " ORDER BY id DESC " 

1594 

1595 limit_offset = "" 

1596 if limit != None and limit != 0: 

1597 limit_offset = " LIMIT %s OFFSET %s" 

1598 params.extend([limit, offset]) 

1599 

1600 # WARNING: col_csv should be validated/sanitized by caller to prevent injection 

1601 query = f"""SELECT {col_csv} FROM mtraudit.mtr_datou_exec {cond_sql} {order_by} {limit_offset}""" 

1602 print(" in load_data_audit " + str(query)) 

1603 if output_type == "pd": 

1604 import pandas.io.sql as psql 

1605 if params: 

1606 dataframe = psql.read_sql(query, self.client, params=tuple(params)) 

1607 else: 

1608 dataframe = psql.read_sql(query, self.client) 

1609 return dataframe 

1610 elif output_type == "dict": 

1611 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1612 cursor.execute(query, tuple(params) if params else None) 

1613 if hash_id_treatment != None: 

1614 all_fetch = cursor.fetchone() 

1615 else : 

1616 all_fetch = cursor.fetchall() 

1617 

1618 return all_fetch 

1619 else : 

1620 print("Unsupported output_type : " + str(output_type)) 

1621 return None 

1622 

1623 def duplicate_data_audit(self, hash_id_treatment = "default_nothing_happen", 

1624 new_hash_id_treatment = "None"): 

1625 if new_hash_id_treatment == None: 

1626 from auth.lib_auth import create_id 

1627 new_hash_id_treatment = create_id() 

1628 

1629 query_duplicate = """ INSERT INTO mtraudit.mtr_datou_exec (info_date, id_file, nb_page, mtr_datou_id, created_at, launched_at, modified_at, deleted_at, valid_until, user_id, project_id, safia_doc_id, hash_id_treatment, cdn, audit_info) 

1630 SELECT info_date, id_file, nb_page, mtr_datou_id, created_at, launched_at, modified_at, deleted_at, valid_until, user_id, project_id, safia_doc_id, %s, cdn, audit_info 

1631 FROM mtraudit.mtr_datou_exec 

1632 WHERE hash_id_treatment=%s""" 

1633 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1634 cursor.execute(query_duplicate, (new_hash_id_treatment, hash_id_treatment)) 

1635 self.client.commit() 

1636 return new_hash_id_treatment 

1637# return cursor.fetchall() 

1638 

1639 def delete_data_manual(self, id = None, hash_id_treatment = None): 

1640 query = "DELETE FROM mtraudit.mtr_manual WHERE id=%s;" 

1641 if id != None and str(id).isdigit(): 

1642 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1643 cursor.execute(query, (id,)) 

1644 self.client.commit() 

1645 

1646 def update_data_manual(self, id = None, hash_id_treatment = None, new_hit = None): 

1647 new_hit_fetch = None 

1648 query_find_id_new_hit = "SELECT id FROM mtraudit.mtr_datou_exec WHERE hash_id_treatment=%s" 

1649 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1650 cursor.execute(query_find_id_new_hit, (new_hit,)) 

1651 new_hit_fetch = cursor.fetchone() 

1652 if new_hit_fetch != None and id != None and str(id).isdigit(): 

1653 query = "UPDATE mtraudit.mtr_manual SET mtr_datou_exec=%s where id=%s;" 

1654 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1655 cursor.execute(query, (new_hit_fetch, id)) 

1656 self.client.commit() 

1657 

1658 def select_datou_exec_from_id_file_and_mtr_datou_id(self, id_file, mtr_datou_id = 51): 

1659 query_find_id_new_hit = "SELECT id, hash_id_treatment as hit FROM mtraudit.mtr_datou_exec WHERE id_file=%s AND mtr_datou_id=%s" 

1660 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1661 cursor.execute(query_find_id_new_hit, (id_file, mtr_datou_id)) 

1662 all_fetch = cursor.fetchall() 

1663 if len(all_fetch) == 0: 

1664 print(" No result for id_file : " + str(id_file) + " and mtr_datou_id : " + str(mtr_datou_id)) 

1665 return None 

1666 elif len(all_fetch) >= 1: 

1667 print(" Warning, more than one result for id_file : " + str(id_file) + " and mtr_datou_id : " + str(mtr_datou_id)) 

1668 return all_fetch 

1669 

1670 def save_data_manual(self, hash_id_treatment, user_id, manual_result): 

1671 self.table_mtr_manual = "mtraudit.mtr_manual" 

1672 all_result = self.load_data_audit(hash_id_treatment=hash_id_treatment, 

1673 col_csv="id,hash_id_treatment") 

1674 if (type(all_result) == dict or type(all_result) == RealDictRow) and "id" in all_result: 

1675 id_mde = all_result["id"] 

1676 elif len(all_result) != 1: 

1677 print("Unexpected result, return None, the rest should fail : ") 

1678 id_mde = None 

1679 else: 

1680 id_mde = all_result[0]["id"] if "id" in all_result[0] else None 

1681 

1682 if id_mde != None: 

1683 insert_query = sql.SQL("INSERT INTO {} (mtr_exec_id, user_id, manual_input_info) VALUES (%s, %s, %s)").format(sql.Identifier(*self.table_mtr_manual.split('.'))) 

1684 

1685 val = (id_mde, user_id, json.dumps(manual_result, default=str)) 

1686 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1687 cursor.execute(insert_query, val) 

1688 res = self.client.commit() 

1689# return res 

1690 # one_fetch = cursor.fetchone() 

1691 

1692 else : 

1693 print(" None id_mde, not saving anything ! ") 

1694 

1695# return 0 

1696 

1697 def update_data_manual(self, hash_id_treatment = None, 

1698 new_hash_id_treatment = None, 

1699 id = None): 

1700 

1701 query = "SELECT id FROM mtraudit.mtr_datou_Exec WHERE hash_id_treatment=%s" 

1702 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1703 cursor.execute(query, (new_hash_id_treatment,)) 

1704 new_hash_id_treatment_fetch = cursor.fetchone()#.fetchall() 

1705 

1706 query = "UPDATE mtraudit.mtr_manual SET mtr_exec_id=%s WHERE id=%s" 

1707 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1708 cursor.execute(query, (new_hash_id_treatment_fetch["id"], id)) 

1709 

1710 def load_data_manual(self, ids_manual = None, hash_id_treatment = None, 

1711 limit = 0, offset = 0, list_cols_as_csv = "mm.id,mm.mtr_exec_id,mm.created_at,mm.modified_at,mm.user_id,mm.manual_input_info,mm.auto_info"): 

1712 list_cond = [] 

1713 params = [] 

1714 list_tables = ["mtraudit.mtr_manual mm"] 

1715 order_by = " ORDER BY id DESC" 

1716 list_col_as_list = list(map(lambda x : x.strip(" "), list_cols_as_csv.split(","))) 

1717 if hash_id_treatment != None: 

1718 print(" Not yet supported !") 

1719 list_cond.append("mde.hash_id_treatment=%s") 

1720 params.append(hash_id_treatment) 

1721 list_cond.append("mde.id=mm.mtr_exec_id") 

1722 list_tables.append("mtraudit.mtr_datou_exec mde") 

1723 # list_col_as_list = list(map(lambda x : "mm." + x, list_col_as_list)) 

1724 order_by = " ORDER BY mm.id" 

1725 elif ids_manual != None: # JE ne vois pas comment gérer ce cas, mais du coup la logique d'alias de table nécessite de hardcoder mtr_manual comme mm 

1726 # WARNING: ids_manual should be validated by caller to prevent SQL injection 

1727 list_cond.append(f"mm.ids_manual IN ({ids_manual})") 

1728 else: 

1729 print("Not yet supported, but maybe should use the limit then !") 

1730 

1731 cond_sql = "" 

1732 if len(list_cond) > 0: 

1733 cond_sql = "WHERE " + " AND ".join(list_cond) 

1734 list_table_csv = ", ".join(list_tables) 

1735 

1736 limit_cond = "" 

1737 if limit != None and int(limit) > 0: 

1738 limit_cond = " LIMIT %s OFFSET %s" 

1739 params.extend([limit, offset]) 

1740 

1741 # WARNING: list_cols_as_csv should be validated by caller to prevent SQL injection 

1742 list_col = ",".join(list_col_as_list) 

1743 query = f"""SELECT {list_col} FROM {list_table_csv} {cond_sql} {order_by} {limit_cond}""" 

1744 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1745 cursor.execute(query, tuple(params) if params else None) 

1746 all_fetch = cursor.fetchall() 

1747 

1748 return all_fetch 

1749 

1750 

1751 

1752 def load_data_comms(self, hash_id_treatment = None, 

1753 mtr_datou_id_extract = 40): 

1754 

1755 if hash_id_treatment == None: 

1756 print("hash_id_treatment none not supported, but maybe should use the limit then !") 

1757 return {} 

1758 

1759 query = """SELECT comms->comm_ids as commentaire, comm_ids as id_doc, comm_ids::int+1 as nb_doc 

1760FROM 

1761( 

1762SELECT json_object_keys(comms) as comm_ids, comms, hit 

1763FROM 

1764( 

1765SELECT (json_extract_path_text(mde.audit_info->'io_exec'->'9'->'output', 'df_complet_as_json')::json)->'Commentaires' as comms, mde.hash_id_treatment as hit 

1766FROM mtraudit.mtr_datou_exec mde WHERE mde.hash_id_treatment=%s 

1767AND mde.mtr_datou_id=%s 

1768) 

1769as comms_json 

1770) 

1771as comm_json""" 

1772 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1773 cursor.execute(query, (hash_id_treatment, mtr_datou_id_extract)) 

1774 all_fetch = cursor.fetchall() 

1775 

1776 return all_fetch 

1777 

1778 

1779 def get_treatment_done(self, last_month, year_last_month, 

1780 mtr_datou_id = 40, 

1781 project_id = [], 

1782 excluded_project_id = [22, 121, 135, 99, 93, 94, 95, 96, 97, 268, 92]): 

1783 

1784 list_conditions = [] 

1785 params = [] 

1786 if len(excluded_project_id) > 0: 

1787 placeholders = ','.join(['%s'] * len(excluded_project_id)) 

1788 list_conditions.append(f" project_id NOT IN ({placeholders})") 

1789 params.extend(excluded_project_id) 

1790 if len(project_id) > 0: 

1791 placeholders = ','.join(['%s'] * len(project_id)) 

1792 list_conditions.append(f" project_id IN ({placeholders})") 

1793 params.extend(project_id) 

1794 if last_month != None: 

1795# list_conditions.append(" EXTRACT(MONTH FROM valid_until) <= 6 ") # + str(last_month)) 

1796 # list_conditions.append(" EXTRACT(MONTH FROM valid_until) = %s") 

1797 list_conditions.append(" EXTRACT(MONTH FROM created_at) = %s") 

1798 params.append(last_month) 

1799 if year_last_month != None: 

1800 list_conditions.append(" EXTRACT(YEAR FROM created_at) = %s") 

1801 params.append(year_last_month) 

1802 if mtr_datou_id != None: 

1803 list_conditions.append(" mtr_datou_id = %s") 

1804 params.append(mtr_datou_id) 

1805 

1806 cond_sql = "" 

1807 if len(list_conditions) > 0: 

1808 cond_sql = " WHERE " + " AND ".join(list_conditions) 

1809 

1810 query = f"""SELECT created_at, nb_page, project_id, info_date, mtr_datou_id, info_consolidate, id_file, hash_id_treatment FROM mtraudit.mtr_datou_exec {cond_sql}""" 

1811 

1812 print(str(query)) 

1813 

1814 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1815 cursor.execute(query, tuple(params) if params else None) 

1816 all_fetch = cursor.fetchall() 

1817 

1818 return all_fetch 

1819 

1820 

1821 def load_auto_val(self, type_doc = "document_type", verbose = False, limit = 1000000 

1822 , project_id = None): 

1823 condition_project_id = " and mde.project_id in (" + ",".join(list(map(str, project_id))) + ")" if project_id != None and project_id != 0 and (type(project_id) == list and len(project_id) > 0) else "" 

1824 

1825 query = """select json_extract_path_text(df_complet_as_json, '""" + type_doc + """', id_row::text) as auto_val, id_row, 

1826nb_page, query_type, user_id, created_at, col_name, manual_val, corrected_at, lgth_audit, id_file, datou_step, hit 

1827from ( 

1828select 

1829generate_series(0,nb_page-1) as id_row, 

1830nb_page as nb_page, 'only_auto' as query_type, mde.user_id as user_id, 

1831mde.created_at as created_at, 

1832'""" + type_doc + """' as col_name, 

1833'None' as manual_val, 

1834'None' as corrected_at, 

1835length(mde.audit_info::text) as lgth_audit, 

1836mde.id_file as id_file, 

1837mde.audit_info->'io_exec'->'9'->'datou_step' as datou_step, 

1838json_extract_path_text(mde.audit_info->'io_exec'->'9'->'output', 'df_complet_as_json')::json as df_complet_as_json, 

1839mde.hash_id_treatment as hit from mtraudit.mtr_datou_exec mde where mde.project_id  

1840not in (22, 121, 135) and mde.mtr_datou_id=40  

1841""" + condition_project_id + """ 

1842order by mde.id desc 

1843limit """ + str(limit) + """ 

1844) 

1845as temp; 

1846""" 

1847 

1848 if verbose: 

1849 print(query) 

1850 

1851 import pandas.io.sql as psql 

1852 dataframe = psql.read_sql(query, self.client) 

1853 return dataframe 

1854 

1855 def load_manual_correct_val(self, type_doc = "document_type", verbose = False, limit = 1000000, project_id = None): 

1856 

1857 condition_project_id = " and mde.project_id in (" + ",".join(list(map(str, project_id))) + ")" if project_id != None and project_id != 0 and (type(project_id) == list and len(project_id) > 0) else "" 

1858 

1859 query = """select id, id_row, hit, col_name, manual_val, created_at, corrected_at, user_id, id_file, lgth_audit, 

1860json_extract_path_text(saxia_auto_res, id_row) as auto_val 

1861from (select id, hit, json_extract_path_text(actions, 'id') as id_row, 

1862 json_extract_path_text(actions, 'col_name') as col_name, corrected_at, created_at, 

1863json_extract_path_text(actions, 'manual_value') as manual_val, 

1864user_id, lgth_audit, id_file, saxia_auto_all->'""" + type_doc + """' as saxia_auto_res 

1865from ( 

1866select mm.id as id, mde.hash_id_treatment as hit, mm.user_id as user_id, mde.created_at as created_at, mm.created_at as corrected_at, 

1867json_array_elements(mm.manual_input_info->'list_actions') as actions, 

1868length(mde.audit_info::text) as lgth_audit, mde.id_file as id_file, 

1869(json_extract_path_text(mde.audit_info->'io_exec'->'9'->'output', 'df_complet_as_json')::json) 

1870as saxia_auto_all, mde.audit_info->'io_exec'->'9'->'datou_step' as datou_step, 

1871mde.hash_id_treatment from mtraudit.mtr_manual mm, mtraudit.mtr_datou_exec mde 

1872where mm.mtr_exec_id=mde.id and mde.project_id not in (22, 121, 135) 

1873""" + condition_project_id + """ 

1874 and mde.mtr_datou_id=40  

1875 order by mde.id desc 

1876 limit """ + str(limit) + """ 

1877) as temp) as temp2 where col_name='""" + type_doc + """' order by created_at; 

1878 

1879 

1880""" 

1881 

1882 

1883 if verbose: 

1884 print(query) 

1885 

1886 import pandas.io.sql as psql 

1887 dataframe = psql.read_sql(query, self.client) 

1888 return dataframe 

1889 

1890 def load_df_cons(self, verbose = False, limit = 0, project_id = None, 

1891 mtr_datou_id = 44, with_out_folder = True): 

1892 condition_project_id = " and mde.project_id in (" + ",".join(list(map(str, project_id))) + ")" if project_id != None and project_id != 0 and (type(project_id) == list and len(project_id) > 0) else "" 

1893 

1894 list_col_output = ["""json_extract_path_text(mde.info_consolidate, 'df_cons')::json as df_cons, 

1895 mde.hash_id_treatment, mde.id_file, mde.project_id, mde.id, json_extract_path_text(mde.info_consolidate, 'hash_id_treatment_input') as mhit"""] 

1896 list_table = ["mtraudit.mtr_datou_exec mde"] 

1897 list_condition = [""] 

1898 if with_out_folder: 

1899 list_col_output.append("json_extract_path_text(mdeh.info_back, 'out_folder') as out_folder") 

1900 list_table.append("mtraudit.mtr_datou_exec mdeh") 

1901 list_condition.append(" mdeh.mtr_datou_id = 40 and mdeh.hash_id_treatment = json_extract_path_text(mde.info_consolidate, 'hash_id_treatment_input')") 

1902 print("TO ADAPT") 

1903 else: 

1904 print("NOT DONE, with_out_folder by default") 

1905 

1906 if (type(project_id) == str and project_id.isdigit()) or type(project_id) == int: 

1907 condition_project_id = " and mde.project_id = " + str(project_id) + " " 

1908 

1909 limit_cond = f" LIMIT {limit}" if limit != None and int(limit) > 0 else "" 

1910 

1911 query = f"""SELECT {', '.join(list_col_output)} 

1912FROM {', '.join(list_table)} 

1913WHERE mde.mtr_datou_id=44 

1914{' AND '.join(list_condition)} 

1915{condition_project_id} 

1916{limit_cond} 

1917 """ 

1918 

1919 if verbose: 

1920 print(query) 

1921 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1922 cursor.execute(query) 

1923 all_fetch = cursor.fetchall() 

1924 

1925 return all_fetch 

1926 #import pandas.io.sql as psql 

1927 #dataframe = psql.read_sql(query, self.client) 

1928 #return dataframe 

1929 

1930 def load_output_folder_from_mhit(self, list_mhits = [], verbose = False): 

1931 if len(list_mhits) == 0: 

1932 return [] 

1933 query = f"""SELECT hash_id_treatment as mhit, json_extract_path_text(info_back, 'out_folder') as out_folder 

1934 from mtraudit.mtr_datou_exec where hash_id_treatment in ({",".join(list(map(lambda x : "'" + x + "'", list_mhits)))})""" 

1935 

1936 if verbose: 

1937 print(query) 

1938 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1939 cursor.execute(query) 

1940 all_fetch = cursor.fetchall() 

1941 

1942 return all_fetch 

1943 

1944 def running_job(self, project_id = None, verbose = False, 

1945 mtr_datou_id_upload = 51, 

1946 mtr_datou_id_anon = 25, 

1947 mtr_datou_id_extract = 40, 

1948 mtr_datou_id_prediag = 55, 

1949 mtr_datou_id_consolidate = 44, 

1950 nb_day = 0): 

1951 

1952 params = [] 

1953 list_conditions = ["current_date-created_at::date<=%s"] 

1954 params.append(nb_day) 

1955 list_conditions.append("id_file IS NOT NULL") 

1956 if project_id != None and str(project_id) != "0": 

1957 list_conditions.append("project_id=%s") 

1958 params.append(project_id) 

1959 

1960 if len(list_conditions) > 0: 

1961 cond_sql = " WHERE " + " AND ".join(list_conditions) 

1962 else : 

1963 cond_sql = "" 

1964 

1965 query = f""" 

1966 SELECT id_file, info_lab, info_date, created_at, nb_page, project_id, mtr_datou_id, info_back, hash_id_treatment FROM mtraudit.mtr_datou_exec {cond_sql} 

1967 """ 

1968 

1969 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

1970 cursor.execute(query, tuple(params)) 

1971 all_fetch = cursor.fetchall() 

1972 

1973 list_uploaded = [] 

1974 list_treated = [] 

1975 list_anon = [] 

1976 list_prediag = [] 

1977 list_consolidate = [] 

1978 for d in all_fetch: 

1979 if str(d["mtr_datou_id"]) == str(mtr_datou_id_upload): 

1980 list_uploaded.append(d) 

1981 if str(d["mtr_datou_id"]) == str(mtr_datou_id_extract): 

1982 list_treated.append(d) 

1983 if str(d["mtr_datou_id"]) == str(mtr_datou_id_anon): 

1984 list_anon.append(d) 

1985 if str(d["mtr_datou_id"]) == str(mtr_datou_id_prediag): 

1986 list_prediag.append(d) 

1987 if str(d["mtr_datou_id"]) == str(mtr_datou_id_consolidate): 

1988 list_consolidate.append(d) 

1989 

1990 list_running = [] 

1991 for d in list_uploaded: 

1992 found_treated = False 

1993 for t in list_treated: 

1994 if d["id_file"] == t["id_file"] and d["project_id"] == t["project_id"]: 

1995 found_treated = True 

1996 break 

1997 

1998 found_prediag = False 

1999 for a in list_prediag: 

2000 if d["id_file"] == a["id_file"] and d["project_id"] == a["project_id"]: 

2001 found_prediag = True 

2002 break 

2003 

2004 found_anon = False 

2005 for n in list_anon: 

2006 if d["id_file"] == n["id_file"] and d["project_id"] == n["project_id"]: 

2007 found_anon = True 

2008 break 

2009 

2010 if found_prediag: 

2011 d["prediag"] = a 

2012 

2013 if found_anon: 

2014 d["anon"] = n 

2015 

2016 if not found_treated: 

2017 list_running.append(d) 

2018 

2019 if len(list_running) > 0: 

2020 # print("could Build link pdf or anon in suivi running") 

2021 # {'in_folder': 'static/onedrive/workarea_anon/output_wfrom_api_d_2210_Oct_15', 

2022 # 'work_folder_images': 'static/onedrive/workarea_anon/output_wfrom_api_d_2210_Oct_15', 

2023 # 'out_folder': 'static/onedrive/workarea_anon/output_wfrom_api_d_2210_Oct_15', 

2024 # 'work_folder': '/Users/moilerat/Documents/Fotonower/Safia/prompt/python/server', 

2025 # 'out_file': 'id_4_nb_1_d_20240612_e813c6b0fd8327e25b11697930b84d15d0c03ff6239f10734e4458b040873942.docx'} 

2026 for data in list_running: 

2027 # print("TODO") 

2028 data["link_pdf"] = "" 

2029 #del data["info_back"] 

2030 

2031 return {"running_or_failed": list_running, 

2032 "zprediag" : list_prediag, 

2033 "uploaded": list_uploaded, 

2034 "treated": list_treated, 

2035 "zanon" : list_anon, 

2036 "zconsolidate" : list_consolidate} 

2037 

2038 

2039 

2040 def record_validation_code(self, user_id, code, ipv4): 

2041 query = "INSERT INTO mtruser.code_double_factor (code, user_id, created_at, ip_used) VALUES (%s, %s, now(), %s)" 

2042 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

2043 cursor.execute(query, (code, user_id, ipv4)) 

2044 self.client.commit() 

2045 

2046 def check_validation_code(self, user_id, code): 

2047 

2048 query = """ 

2049 SELECT id, user_id, created_at, validated_at FROM mtruser.code_double_factor WHERE code=%s AND user_id=%s AND created_at + interval '1 hour' > now() AND validated_at IS NULL 

2050 """ 

2051 

2052 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

2053 cursor.execute(query, (code, user_id)) 

2054 all_fetch = cursor.fetchall() 

2055 

2056 if len(all_fetch) == 0: 

2057 return False 

2058 else : 

2059 print(" We should check valide") 

2060 print(" all_fetch validation code : " + str(all_fetch)) 

2061 

2062 query_update = "UPDATE mtruser.code_double_factor SET validated_at=now() WHERE id=%s" 

2063 cursor.execute(query_update, (all_fetch[0]["id"],)) 

2064 

2065 return True 

2066 

2067 def record_ip_2fa(self, user_id, ip_validated, white_list_ip = False, soft = True): 

2068 

2069 if white_list_ip: 

2070 valid_until = "NOW() + interval '1 year'" 

2071 else: 

2072 valid_until = "NOW()" 

2073 

2074 if soft: 

2075 action_on_conflict = "DO NOTHING " 

2076 action_on_conflict = f"DO UPDATE SET user_id={user_id}" 

2077 else : 

2078 action_on_conflict = f"DO UPDATE SET valid_until={valid_until}" 

2079 

2080 query = f"""insert into mtruser.validated_ip (user_id, ipv4, valid_until)  

2081 values ({user_id}, '{ip_validated}', {valid_until}) 

2082 ON CONFLICT(user_id, ipv4) {action_on_conflict} 

2083 RETURNING id;""" 

2084 

2085 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

2086 cursor.execute(query) 

2087 res = cursor.fetchone() 

2088 self.client.commit() 

2089 if 'id' in res: 

2090 return res['id'] 

2091 else : 

2092 return None 

2093 

2094 # VR on dirait qu'on utilise pas cela ! 

2095 def retrieve_ips_2fa(self, user_id, ip_search = None, only_valid = True): 

2096 params = [user_id] 

2097 query = "SELECT * FROM mtruser.validated_ip WHERE user_id=%s" 

2098 if ip_search != None: 

2099 query += " AND ipv4=%s" 

2100 params.append(ip_search) 

2101 

2102 if only_valid: 

2103 query += " AND valid_until > NOW()" 

2104 

2105 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

2106 cursor.execute(query, tuple(params)) 

2107 all_fetch = cursor.fetchall() 

2108 return all_fetch 

2109 

2110 def deactivate_all_ips_2fa(self, user_id): 

2111 query = "UPDATE mtruser.validated_ip SET valid_until=NOW() WHERE user_id=%s" 

2112 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

2113 cursor.execute(query, (user_id,)) 

2114 

2115 def record_connection_ip_info(self, id_ipv4, type, info): 

2116 

2117# UPDATE mtruser.project SET configuration = jsonb_set(configuration, '{custom_display,saxia_suivi,quick}', '{"saxia.config.project": {"project_id": 91}}', true) WHERE id = 91; 

2118 

2119 import datetime 

2120 info["created_at"] = datetime.datetime.now().isoformat() 

2121 

2122 info_json_as_str = json.dumps(info) 

2123 

2124 # Note: type is used as a JSON key path - should be validated by caller to prevent injection 

2125 query = """UPDATE mtruser.validated_ip 

2126 SET misc_info = jsonb_set( 

2127 misc_info, 

2128 '{""" + type + """}', 

2129 COALESCE(misc_info->'""" + type + """', '[]'::jsonb) || %s::jsonb 

2130) 

2131WHERE id = %s""" 

2132 

2133 print(query) 

2134 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

2135 cursor.execute(query, (info_json_as_str, id_ipv4)) 

2136 self.client.commit() 

2137 

2138 def get_usage_connection_ip_info(self, mtr_user_id = None, ipv4 = None, type = None, 

2139 nb_day = 0, limit = 0, offset = 0): 

2140 

2141 cond_json_key = "misc_info" 

2142 params = [] 

2143 if type != None: 

2144 # Note: type is used as a JSON key path - should be validated by caller 

2145 cond_json_key += "->'" + str(type) + "'" 

2146 data_col = " jsonb_array_elements(coalesce(" + cond_json_key + ", '[]'::jsonb))" 

2147 else : 

2148 print(" TODO VR 4/2/25 to use jsonb_object_keys Je ne sais pas si on va gérer cela car il faut un jsonb_object_keys ou un truc imbriqué ! ") 

2149 data_col = " " + cond_json_key + " " 

2150 

2151 list_condition = [] 

2152 if mtr_user_id != None: 

2153 list_condition.append("user_id=%s") 

2154 params.append(mtr_user_id) 

2155 if ipv4 != None: 

2156 list_condition.append("ipv4=%s") 

2157 params.append(ipv4) 

2158 

2159 condition = "" 

2160 if len(list_condition) > 0: 

2161 condition = " WHERE " + " AND ".join(list_condition) 

2162 

2163 # Note: type is used in query construction - should be validated by caller 

2164 query = """ 

2165 SELECT ipv4, user_id, '""" + str(type) + """' as type, """ + data_col + """ as elem FROM mtruser.validated_ip 

2166 """ + condition + """ """ 

2167 

2168 if nb_day != None and str(nb_day).isdigit() and int(nb_day) > 0: 

2169 # Note: nb_day should be validated as integer by caller 

2170 query = "SELECT * FROM ( " + query + " ) as temp WHERE (elem->>'created_at')::date + interval '" + str(nb_day) + " day' > now() ORDER BY (elem->>'created_at')::timestamp" 

2171 

2172# list_condition.append("elem->'created_at'::date >= current_date - interval '" + str(nb_day) + " day'") 

2173 

2174 print(query) 

2175 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

2176 cursor.execute(query, tuple(params) if params else None) 

2177 all_fetch = cursor.fetchall() 

2178 return all_fetch 

2179 

2180 return {} 

2181 

2182 def audit_or_delete_user(self, user_id, also_delete = False): 

2183 

2184 all_info = {"list_deleted" : [], "list_kept" : []} 

2185 if str(user_id).isdigit(): 

2186 all_project_owned = self.get_projects_from_owner(user_id) 

2187 info_user = self.select_user(user_id) 

2188 info_user2 = self.get_user_info_from_id(user_id) 

2189 

2190 all_info["audit"] = {"info_user" : info_user, 

2191 "info_user2" : info_user2, 

2192 "projects" : all_project_owned} 

2193 

2194 schema_user_documents = info_user["schema_user_documents"] if info_user != None and "schema_user_documents" in info_user else "" 

2195 if also_delete: 

2196 query_delete_schema = sql.SQL("DROP SCHEMA IF EXISTS {} CASCADE").format(sql.Identifier(schema_user_documents)) 

2197 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

2198 cursor.execute(query_delete_schema) 

2199 self.client.commit() 

2200 

2201 for pid in all_project_owned: 

2202 info = self.get_project_info(pid["id"]) 

2203 

2204 if str(user_id) == str(info["owner_id"]): 

2205 all_info["list_deleted"].append(info["id"]) 

2206 if also_delete: 

2207 query_delete_project = "DELETE FROM mtruser.project WHERE id=%s" 

2208 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

2209 cursor.execute(query_delete_project, (info["id"],)) 

2210 self.client.commit() 

2211 else : 

2212 all_info["list_kept"].append(info) 

2213 

2214 if also_delete: 

2215 query = "DELETE FROM mtruser.user WHERE id=%s" 

2216 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

2217 cursor.execute(query, (user_id,)) 

2218 self.client.commit() 

2219 

2220 query = "SELECT * FROM mtruser.flask_user WHERE mtr_user_id=%s" 

2221 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

2222 cursor.execute(query, (user_id,)) 

2223 self.client.commit() 

2224 list_data_fu = cursor.fetchall() 

2225 all_info["list_fu"] = list_data_fu 

2226 if len(list_data_fu) > 1: 

2227 all_info["error"].append("Multiple login account not expected : internal error, please report to admin !") 

2228 

2229 if len(list_data_fu) == 1 and also_delete: 

2230 id = list_data_fu[0]["id"] 

2231 query = "DELETE FROM mtruser.flask_user WHERE id=%s" 

2232 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

2233 cursor.execute(query, (id,)) 

2234 self.client.commit() 

2235 

2236 return all_info 

2237 else : 

2238 return "Nothing DONE" 

2239 

2240 def get_token(self, mtr_user_id, token = None, project_id = None): # token and project unused and no limit ! 

2241 query = """SELECT id, valid_until, mtr_user_id, description, token, configuration, default_project_id, created_at FROM mtruser.mtr_token WHERE mtr_user_id = %s AND valid_until > NOW() """ 

2242 query = """ 

2243 SELECT id, user_id, client_id, access_token as token, 'auto' as description, 

2244 TO_TIMESTAMP(issued_at) + interval '1 second' * expires_in as valid_until 

2245 FROM mtruser.oauth2_token WHERE user_id = %s  

2246 """ # and access_token_revoked_at is null ;""" 

2247 

2248# CAST(TO_TIMESTAMP(issued_at) as date) + interval '1 second' * expires_in as valid_untile 

2249 

2250# AND token = %s 

2251 params = (mtr_user_id,) 

2252 

2253 if project_id is not None: 

2254 query += " AND default_project_id = %s" 

2255 params += (project_id,) 

2256 

2257 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

2258 cursor.execute(query, params) 

2259 res = cursor.fetchall() 

2260 if res: 

2261 return res 

2262 else: 

2263 return None 

2264 

2265 def create_token(self, mtr_user_id, description, token, configuration = {}, project_id = None): 

2266 

2267 list_columns = ["mtr_user_id", "description", "token", "configuration"] 

2268 table_name = "mtruser.mtr_token" 

2269 list_values_format = ["%s", "%s", "%s", "%s"] 

2270 tuple_values = (mtr_user_id, description, token, json.dumps(configuration)) 

2271 

2272 from werkzeug.security import gen_salt 

2273 access_token = gen_salt(42) 

2274 table_name = "mtruser.oauth2_token" 

2275 list_columns = ["user_id", "client_id", "access_token", 

2276 "refresh_token", "issued_at", "expires_in", 

2277 "access_token_revoked_at", "refresh_token_revoked_at", 

2278 "token_type"] 

2279 list_values_format = ["%s", "%s", "%s", "%s", "%s", "%s", 

2280 "%s", "%s", "%s"] 

2281 

2282# strftime('%Y-%m-%d %H:%M:%S') 

2283 issued_at = datetime.datetime.now() #.time() 

2284 tuple_values = (mtr_user_id, "B9VTPyioeANFgaotf4tFwdwz", access_token, "", issued_at.timestamp(), "31530000", 0 , 0, "Bearer") 

2285 

2286 if project_id is not None: 

2287 list_columns.append("default_project_id") 

2288 configuration["project_id"] = project_id 

2289 list_values_format.append("%s") 

2290 tuple_values += (project_id,) 

2291 

2292 query = f"INSERT INTO {table_name} (" + ", ".join(list_columns) + ") VALUES (" + ", ".join(list_values_format) + ") RETURNING id;" 

2293 

2294 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

2295 cursor.execute(query, tuple_values) 

2296 res = cursor.fetchone() 

2297 self.client.commit() 

2298 

2299 if res and 'id' in res: 

2300 return res['id'] 

2301 else: 

2302 return None 

2303 

2304 def update_token(self, token, configuration = None, project_id = None, mtr_user_id = None, set_not_valid=True, description=None): 

2305 # en fait je ne veux pas de description ! 

2306 list_columns = [] 

2307 tuple_values = () 

2308 if configuration is not None: 

2309 list_columns.append("configuration = %s") 

2310 tuple_values += (json.dumps(configuration),) 

2311 if project_id is not None: 

2312 list_columns.append("default_project_id = %s") 

2313 tuple_values += (project_id,) 

2314 if mtr_user_id is not None: 

2315 list_columns.append("mtr_user_id = %s") 

2316 tuple_values += (mtr_user_id,) 

2317 if set_not_valid: 

2318 list_columns.append("valid_until = NOW()") 

2319 if description is not None: 

2320 list_columns.append("description = %s") 

2321 tuple_values += (description,) 

2322 if len(list_columns) == 0: 

2323 print("Nothing to update, returning False") 

2324 return False 

2325 list_columns.append("modified_at = NOW()") 

2326 query = f"UPDATE mtruser.mtr_token SET {', '.join(list_columns)} WHERE token = %s;" 

2327 tuple_values += (token,) 

2328 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

2329 cursor.execute(query, tuple_values) 

2330 self.client.commit() 

2331 return True 

2332 

2333# get_token_if_is_valid 

2334 def check_bearer_token(self, token): 

2335 query = """SELECT id, mtr_user_id, description, configuration, default_project_id, created_at FROM mtruser.mtr_token WHERE token = %s AND valid_until > NOW(); 

2336 """ 

2337 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

2338 cursor.execute(query, (token,)) 

2339 res = cursor.fetchone() 

2340 if res: 

2341 return res 

2342 else: 

2343 return None 

2344 

2345 def list_autorized_client(self, mtr_user_id): 

2346 query = """SELECT * FROM mtruser.client WHERE mtr_user_id={mtr_user_id}; 

2347 """ 

2348 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

2349 cursor.execute(query) 

2350 res = cursor.fetchone() 

2351 if res: 

2352 return res 

2353 else: 

2354 return None 

2355 

2356 def list_token_access_usage(self, mtr_user_id): 

2357 pass 

2358 

2359 def list_grant_access_to_client_app(self, mtr_user_id): 

2360 query = """SELECT * FROM mtruser.grant WHERE mtr_user_id={mtr_user_id}; 

2361 """ 

2362 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

2363 cursor.execute(query) 

2364 res = cursor.fetchone() 

2365 if res: 

2366 return res 

2367 else: 

2368 return None 

2369 

2370 def revoke_client(self, mtr_user_id, client_id): 

2371 pass 

2372 

2373 

2374 

2375 

2376 

2377 def list_client_app_to_uld(self, client_name): 

2378 pass 

2379 

2380 def create_client_app_to_uld(self, client_name): 

2381 pass 

2382 

2383 # OAuth2 Client management methods 

2384 def get_oauth_clients_by_user(self, user_id): 

2385 """ 

2386 Retrieve all OAuth2 clients for a specific user 

2387 

2388 Args: 

2389 user_id: The user ID to filter clients 

2390 

2391 Returns: 

2392 List of dictionaries containing client information 

2393 id | user_id | client_id | client_secret | client_id_issued_at | client_secret_expires_at | client_metadata  

2394 

2395 """ 

2396 query = """ 

2397 SELECT id, user_id, client_id, client_secret, client_id_issued_at, client_secret_expires_at, client_metadata 

2398 FROM mtruser.oauth2_client 

2399 WHERE user_id = %s 

2400 ORDER BY client_id_issued_at DESC 

2401 """ 

2402 

2403 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

2404 cursor.execute(query, (user_id,)) 

2405 res = cursor.fetchall() 

2406 if res: 

2407 return res 

2408 else: 

2409 return None 

2410 

2411 def create_oauth_client(self, client_id, client_secret, user_id, client_name, 

2412 redirect_uris, grant_types, response_types, 

2413 scope = None, client_uri = None, client_metadata = {}): 

2414 """ 

2415 Create a new OAuth2 client in the database 

2416 

2417 Args: 

2418 client_id: Generated unique client identifier 

2419 client_secret: Generated secret for the client 

2420 user_id: The user who owns this client 

2421 client_metadata: JSON string of additional metadata 

2422 redirect_uris: Space-separated string of redirect URIs 

2423 grant_types: Space-separated string of grant types 

2424 response_types: Space-separated string of response types 

2425 client_uri: Optional URL of the client application 

2426 scope: OAuth scopes for the client 

2427 client_name: Human-readable name for the client 

2428 

2429 Returns: 

2430 The ID of the newly created client 

2431 """ 

2432 query = """ 

2433 INSERT INTO mtruser.oauth2_client 

2434 (client_id, client_secret, user_id, client_metadata, client_id_issued_at, client_secret_expires_at) 

2435 VALUES (%s, %s, %s, %s, EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP AT TIME ZONE 'UTC')), 

2436 EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP + interval '10000 hour') AT TIME ZONE 'UTC') ) 

2437 RETURNING id 

2438 """ 

2439 

2440 if isinstance(redirect_uris, str) and redirect_uris.strip() != "": 

2441 redirect_uris = [uri.strip() for uri in redirect_uris.split()] 

2442 else: 

2443 redirect_uris = [] 

2444 if isinstance(redirect_uris, list): 

2445 if "redirect_uris" in client_metadata: 

2446 redirect_uris = list(set(client_metadata["redirect_uris"] + redirect_uris)) 

2447 client_metadata["redirect_uris"] = redirect_uris 

2448 

2449 if isinstance(grant_types, str) and grant_types.strip() != "": 

2450 grant_types = [gt.strip() for gt in grant_types.split()] 

2451 else: 

2452 grant_types = [] 

2453 if isinstance(grant_types, list): 

2454 if "grant_types" in client_metadata: 

2455 grant_types = list(set(client_metadata["grant_types"] + grant_types)) 

2456 client_metadata["grant_types"] = grant_types 

2457 

2458 if isinstance(response_types, str) and response_types.strip() != "": 

2459 response_types = [rt.strip() for rt in response_types.split()] 

2460 else: 

2461 response_types = [] 

2462 if isinstance(response_types, list): 

2463 if "response_types" in client_metadata: 

2464 response_types = list(set(client_metadata["response_types"] + response_types)) 

2465 client_metadata["response_types"] = response_types 

2466 if client_uri != None: 

2467 client_metadata["client_uri"] = client_uri 

2468 if scope != None: 

2469 client_metadata["scope"] = scope 

2470 

2471 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

2472 cursor.execute(query, (client_id, client_secret, user_id, json.dumps(client_metadata))) 

2473 result = cursor.fetchone() 

2474 self.client.commit() 

2475 return result['id'] if result and 'id' in result else None 

2476 

2477 def delete_oauth_client(self, client_id, user_id): 

2478 """ 

2479 Delete an OAuth2 client from the database 

2480 

2481 Args: 

2482 client_id: The client identifier to delete 

2483 user_id: The user who owns this client  

2484 Returns: 

2485 True if deletion was successful, False otherwise 

2486 """ 

2487 query = """ 

2488 DELETE FROM mtruser.oauth2_client 

2489 WHERE client_id = %s AND user_id = %s 

2490 """ 

2491 

2492 with self.client.cursor(cursor_factory=RealDictCursor) as cursor: 

2493 cursor.execute(query, (client_id, user_id)) 

2494 self.client.commit() 

2495 return cursor.rowcount > 0 

2496 

2497