import requests import json import os import time import mysql.connector from datetime import datetime, timezone def parse_iso(dt_str): # Remove trailing 'Z' if present and parse ISO datetime return datetime.fromisoformat(dt_str.rstrip('Z')) # ============================================= # Configurações de diretórios e LOGs # ============================================= BASE_DIR = os.path.abspath(os.path.dirname(__file__)) DATA_DIR = os.path.join(BASE_DIR, 'dados-reservation') LOG_BASE = os.path.join(BASE_DIR, 'logs', 'system-reservation') LOG_ERROR_DIR = os.path.join(LOG_BASE, 'errors') LOG_UPDATE_DIR = os.path.join(LOG_BASE, 'updates') LOG_INSERT_DIR = os.path.join(LOG_BASE, 'inserts') LOG_OPER_DIR = os.path.join(LOG_BASE, 'operations') for d in [LOG_BASE, LOG_ERROR_DIR, LOG_UPDATE_DIR, LOG_INSERT_DIR, LOG_OPER_DIR, DATA_DIR]: os.makedirs(d, exist_ok=True) def _write_log(dir_path, filename, msg): path = os.path.join(dir_path, filename) with open(path, 'a', encoding='utf-8') as f: f.write(msg + '\n') def log_system(msg): ts = datetime.now(timezone.utc).astimezone().isoformat() line = f"{ts} [SYSTEM] {msg}" print(line) _write_log(LOG_OPER_DIR, 'operations.log', line) def log_error(msg): ts = datetime.now(timezone.utc).astimezone().isoformat() line = f"{ts} [ERROR] {msg}" print(line) _write_log(LOG_ERROR_DIR, 'errors.log', line) def log_update(msg): ts = datetime.now(timezone.utc).astimezone().isoformat() line = f"{ts} [UPDATE] {msg}" print(line) _write_log(LOG_UPDATE_DIR, 'updates.log', line) def log_insert(msg): ts = datetime.now(timezone.utc).astimezone().isoformat() line = f"{ts} [INSERT] {msg}" print(line) _write_log(LOG_INSERT_DIR, 'inserts.log', line) # ============================================= # Conexão MySQL # ============================================= def get_db_connection(): return mysql.connector.connect(host="localhost", user="root", password="", database="bihits") def get_existing_reservations_map(): conn = get_db_connection() cursor = conn.cursor() cursor.execute("SELECT idReservation, idEntity, dateUp FROM reservations") m = {(rid, eid): dt for rid, eid, dt in cursor} cursor.close() conn.close() return m def get_existing_details_map(): conn = get_db_connection() cursor = conn.cursor() cursor.execute("SELECT idReservation, dateUp FROM reservation_details") m = {rid: dt for rid, dt in cursor} cursor.close() conn.close() return m # ============================================= # Carregamento incremental de arquivos locais # ============================================= def load_local_reservations_map(): path = os.path.join(DATA_DIR, 'reservations.json') if not os.path.isfile(path): return {} with open(path, 'r', encoding='utf-8') as f: data = json.load(f) return { (r['idReservation'], r['idEntity']): datetime.fromisoformat(r['dateUp'].rstrip('Z')) for r in data } def load_local_details_map(): path = os.path.join(DATA_DIR, 'reservation_details.json') if not os.path.isfile(path): return {} with open(path, 'r', encoding='utf-8') as f: data = json.load(f) latest = {} for d in data: rid = d['idReservation'] dt = datetime.fromisoformat(d['dateUp'].rstrip('Z')) if rid not in latest or dt > latest[rid]: latest[rid] = dt return latest # ============================================= # Token e requisições seguras # ============================================= def get_token(): url = "http://localhost/BI-HITS/dados/Hits/token/token.php" payload = {"username": "seu_usuario", "password": "sua_senha"} try: r = requests.post(url, data=payload, timeout=30) r.raise_for_status() raw = r.text.strip() log_system(f"Resposta bruta do token: {raw}") prefix = "Token obtido:" return raw.replace(prefix, "").strip() if raw.startswith(prefix) else r.json().get("token") except Exception as e: log_error(f"Erro ao obter token: {e}") return None def safe_get(url, headers, params=None, timeout=30): delay = 5 for attempt in range(6): r = requests.get(url, headers=headers, params=params, timeout=timeout) log_system(f"Request URL: {r.url} → Status {r.status_code}") if r.status_code == 429: log_system(f"429 recebido, aguardando {delay}s (tentativa {attempt+1}/6)") time.sleep(delay) delay *= 2 continue r.raise_for_status() return r log_error(f"Falha após múltiplas tentativas: {url}") return None # ============================================= # Fetch de reservas e detalhes # ============================================= def fetch_reservations(token, start_date, end_date, page_size=50): url = "https://api.hitspms.net/Datashare/WebCheckinOut/Reservations" headers = { "Accept": "application/json", "X-API-VERSION": "1", "X-API-TENANT-NAME": "thecoralbeachresort", "X-API-PROPERTY-CODE": "1", "X-API-PARTNER-USERID": "0", "X-API-LANGUAGE-CODE": "pt-br", "X-Client-Id": "THECORALBR", "Authorization": f"Bearer {token}" } reservations = [] page = 0 while True: log_system(f"Buscando reservas – página {page}") params = { "Type": 1, "Status": 1, "InitialDate": start_date, "FinalDate": end_date, "Page": page, "Size": page_size } r = safe_get(url, headers, params=params) if not r: break data = r.json() if not data: break reservations.extend(data) if len(data) < page_size: break page += 1 time.sleep(0.5) return reservations def fetch_reservation_by_id(token, reservation_id): url = f"https://api.hitspms.net/Datashare/WebCheckinOut/Reservation/{reservation_id}" headers = { "Accept": "application/json", "X-API-VERSION": "1", "X-API-TENANT-NAME": "thecoralbeachresort", "X-API-PROPERTY-CODE": "1", "X-API-PARTNER-USERID": "0", "X-API-LANGUAGE-CODE": "pt-br", "X-Client-Id": "THECORALBR", "Authorization": f"Bearer {token}" } log_system(f"Buscando detalhes da reserva ID {reservation_id}") r = safe_get(url, headers) return r.json() if r else None # ============================================= # Funções de manutenção de tabelas # ============================================= def truncate_tables(): conn = get_db_connection() cur = conn.cursor() cur.execute("SET FOREIGN_KEY_CHECKS=0;") cur.execute("TRUNCATE TABLE reservation_details;") cur.execute("TRUNCATE TABLE reservations;") cur.execute("SET FOREIGN_KEY_CHECKS=1;") conn.commit() cur.close() conn.close() log_system("Tabelas truncadas.") def ensure_tables(): # Mantido vazio pois tabelas já foram criadas externamente pass # ============================================= # Upsert em reservations # ============================================= def upsert_reservations(res_list): existing = get_existing_reservations_map() conn = get_db_connection() cur = conn.cursor() sql = """ INSERT INTO reservations (idReservation,idEntity,name,phone,main,mail, zipCode,addressDetails,address,neighborhood,number,city, country,stateName,stateCode,federalRegistrationNumber, documentType,checkIn,checkOut,dateAdd,dateUp,status,integrator,channel,raw_json) VALUES (%(idReservation)s,%(idEntity)s,%(name)s,%(phone)s,%(main)s,%(mail)s, %(zipCode)s,%(addressDetails)s,%(address)s,%(neighborhood)s,%(number)s,%(city)s, %(country)s,%(stateName)s,%(stateCode)s,%(federalRegistrationNumber)s, %(documentType)s,%(checkIn)s,%(checkOut)s,%(dateAdd)s,%(dateUp)s, %(status)s,%(integrator)s,%(channel)s,%(raw_json)s) ON DUPLICATE KEY UPDATE name=VALUES(name), phone=VALUES(phone), main=VALUES(main), mail=VALUES(mail), zipCode=VALUES(zipCode), addressDetails=VALUES(addressDetails), address=VALUES(address), neighborhood=VALUES(neighborhood), number=VALUES(number), city=VALUES(city), country=VALUES(country), stateName=VALUES(stateName), stateCode=VALUES(stateCode), federalRegistrationNumber=VALUES(federalRegistrationNumber), documentType=VALUES(documentType), checkIn=VALUES(checkIn), checkOut=VALUES(checkOut), dateUp=VALUES(dateUp), status=VALUES(status), integrator=VALUES(integrator), channel=VALUES(channel), raw_json=VALUES(raw_json) """ for r in res_list: key = (r['idReservation'], r['idEntity']) dt_api = parse_iso(r['dateUp']) if key not in existing or dt_api > existing[key]: params = { **r, 'raw_json': json.dumps(r, ensure_ascii=False) } cur.execute(sql, params) if key not in existing: log_insert(f"reservations {r['idReservation']} inserted") else: log_update(f"reservations {r['idReservation']} updated") conn.commit() cur.close() conn.close() # ============================================= # Upsert em reservation_details # ============================================= def upsert_reservation_details(details_list): existing = get_existing_details_map() grouped = {} for d in details_list: rid = d['idReservation'] dt = parse_iso(d['dateUp']) if rid not in grouped or dt > parse_iso(grouped[rid]['dateUp']): grouped[rid] = d conn = get_db_connection() cur = conn.cursor() sql = """ INSERT INTO reservation_details (idReservation,idEntityCompany,companyName,idRequesterCompany, requesterCompanyName,groupName,contactName,contact1,contact2, dateAdd,dateUp,creditState,notes,rooms,guests,commissions, revenueManagement,raw_json) VALUES (%(idReservation)s,%(idEntityCompany)s,%(companyName)s,%(idRequesterCompany)s, %(requesterCompanyName)s,%(groupName)s,%(contactName)s,%(contact1)s,%(contact2)s, %(dateAdd)s,%(dateUp)s,%(creditState)s,%(notes)s,%(rooms)s,%(guests)s, %(commissions)s,%(revenueManagement)s,%(raw_json)s) ON DUPLICATE KEY UPDATE idEntityCompany=VALUES(idEntityCompany), companyName=VALUES(companyName), idRequesterCompany=VALUES(idRequesterCompany), requesterCompanyName=VALUES(requesterCompanyName), groupName=VALUES(groupName), contactName=VALUES(contactName), contact1=VALUES(contact1), contact2=VALUES(contact2), dateUp=VALUES(dateUp), creditState=VALUES(creditState), notes=VALUES(notes), rooms=VALUES(rooms), guests=VALUES(guests), commissions=VALUES(commissions), revenueManagement=VALUES(revenueManagement), raw_json=VALUES(raw_json) """ for d in grouped.values(): rid = d['idReservation'] dt_api = parse_iso(d['dateUp']) if rid not in existing or dt_api > existing[rid]: payload = { **d, 'notes': json.dumps(d.get('notes', []), ensure_ascii=False), 'rooms': json.dumps(d.get('rooms', []), ensure_ascii=False), 'guests': json.dumps(d.get('guests', []), ensure_ascii=False), 'commissions': json.dumps(d.get('commissions', []), ensure_ascii=False), 'revenueManagement': json.dumps(d.get('revenueManagement', {}), ensure_ascii=False), 'raw_json': json.dumps(d, ensure_ascii=False) } cur.execute(sql, payload) if rid not in existing: log_insert(f"reservation_details {rid} inserted") else: log_update(f"reservation_details {rid} updated") conn.commit() cur.close() conn.close() # ============================================= # Main # ============================================= def main(): # caminhos dos dumps path_res = os.path.join(DATA_DIR, 'reservations.json') path_det = os.path.join(DATA_DIR, 'reservation_details.json') # 0) se não existirem os dumps, faz carga completa (trunca) if not os.path.isfile(path_res) or not os.path.isfile(path_det): log_system("Dumps JSON ausentes — fazendo carga completa e truncando tabelas.") truncate_tables() else: log_system("Dumps JSON encontrados — mantendo histórico e usando cargas incrementais.") # 1) carrega mapas locais (vazios se arquivos não existirem) local_res_map = load_local_reservations_map() local_det_map = load_local_details_map() # 2) obtém token e intervalo de datas token = get_token() if not token: log_error("Token não obtido, abortando.") return start_date = "2025-02-01T00:00:00Z" end_date = datetime.now(timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ") ensure_tables() # 3) busca todas as reservas na API api_res = fetch_reservations(token, start_date, end_date) # 4) filtra só as que são novas ou atualizadas to_fetch = [] for rec in api_res: key = (rec['idReservation'], rec['idEntity']) dt_api = parse_iso(rec['dateUp']) if key not in local_res_map or dt_api > local_res_map[key]: to_fetch.append(rec) # 5) busca detalhes apenas dessas details = [] for r in to_fetch: d = fetch_reservation_by_id(token, r['idReservation']) if d: details.append(d) time.sleep(2.2) # 6) atualiza os dumps (sobrescreve com o estado completo) with open(path_res, 'w', encoding='utf-8') as f: json.dump(api_res, f, ensure_ascii=False, indent=2) with open(path_det, 'w', encoding='utf-8') as f: json.dump(details, f, ensure_ascii=False, indent=2) # 7) faz o upsert no banco upsert_reservations(api_res) upsert_reservation_details(details) if __name__ == "__main__": main()