import requests import json import os import time import mysql.connector from datetime import datetime, timezone # ============================================= # Logging # ============================================= def log_system(msg): print(f"{datetime.now().isoformat()} [SYSTEM] {msg}") def log_error(msg): print(f"{datetime.now().isoformat()} [ERROR] {msg}") # ============================================= # Token retrieval # ============================================= def get_token(): token_url = "http://localhost/BI-HITS/dados/Hits/token/token.php" payload = { "username": "seu_usuario", "password": "sua_senha" } try: r = requests.post(token_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 # ============================================= # Fetch list of reservations # ============================================= 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 lista de reservas - Página {page} (size {page_size})") params = {"Type":1,"Status":1,"InitialDate":start_date,"FinalDate":end_date,"Page":page,"Size":page_size} try: r = requests.get(url, headers=headers, params=params, timeout=30) log_system(f"Request URL: {r.url} → Status {r.status_code}") r.raise_for_status() except Exception as e: log_error(f"Erro ao buscar lista de reservas: {e}") break data = r.json() if not isinstance(data, list) or not data: log_system(f"Nenhum dado na página {page}, encerrando.") break reservations.extend(data) if len(data) < page_size: log_system("Última página de lista alcançada.") break page += 1 time.sleep(0.5) return reservations # ============================================= # Fetch reservation details by ID # ============================================= 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}" } try: log_system(f"Buscando detalhes da reserva ID {reservation_id}") r = requests.get(url, headers=headers, timeout=30) log_system(f"Request URL: {r.url} → Status {r.status_code}") r.raise_for_status() return r.json() except Exception as e: log_error(f"Erro ao buscar detalhe reserva {reservation_id}: {e}") return None # ============================================= # Truncate tables (full reload) # ============================================= def truncate_tables(): conn = mysql.connector.connect(host="localhost", user="root", password="", database="bihits") cursor = conn.cursor() # Desabilita checagem de FK para truncar sem erros cursor.execute("SET FOREIGN_KEY_CHECKS=0;") cursor.execute("TRUNCATE TABLE reservation_details;") cursor.execute("TRUNCATE TABLE reservations;") cursor.execute("SET FOREIGN_KEY_CHECKS=1;") conn.commit() cursor.close() conn.close() log_system("Tabelas reservations e reservation_details truncadas.") # ============================================= # Ensure MySQL tables exist # ============================================= def ensure_tables(): conn = mysql.connector.connect(host="localhost", user="root", password="", database="bihits") cursor = conn.cursor() cursor.execute(""" CREATE TABLE IF NOT EXISTS reservations ( idReservation INT PRIMARY KEY, idEntity INT, name VARCHAR(200), mainFlag BOOLEAN, phone VARCHAR(50), mail VARCHAR(200), zipCode VARCHAR(20), addressDetails TEXT, address TEXT, neighborhood VARCHAR(100), number VARCHAR(50), city VARCHAR(100), country VARCHAR(100), stateName VARCHAR(100), stateCode VARCHAR(10), federalRegistrationNumber VARCHAR(50), documentType INT, checkIn DATETIME, checkOut DATETIME, dateAdd DATETIME, dateUp DATETIME, status INT, integrator VARCHAR(100), channel VARCHAR(100) )""") cursor.execute(""" CREATE TABLE IF NOT EXISTS reservation_details ( idReservation INT PRIMARY KEY, idEntityCompany INT, companyName VARCHAR(200), idRequesterCompany INT, requesterCompanyName VARCHAR(200), groupName VARCHAR(200), contactName VARCHAR(200), contact1 VARCHAR(200), contact2 VARCHAR(200), dateAdd DATETIME, dateUp DATETIME, notes JSON, rooms JSON, guests JSON, commissions JSON, revenueManagement JSON, creditState VARCHAR(100), FOREIGN KEY (idReservation) REFERENCES reservations(idReservation) )""") conn.commit() cursor.close() conn.close() log_system("Tabelas asseguradas no banco de dados.") # ============================================= # Upsert JSON into MySQL # ============================================= def upsert_reservations(res_list): conn = mysql.connector.connect(host="localhost", user="root", password="", database="bihits") cursor = conn.cursor() sql = """ INSERT INTO reservations (idReservation,idEntity,name,mainFlag,phone,mail, zipCode,addressDetails,address,neighborhood,number,city, country,stateName,stateCode,federalRegistrationNumber, documentType,checkIn,checkOut,dateAdd,dateUp,status,integrator,channel) VALUES (%(idReservation)s,%(idEntity)s,%(name)s,%(main)s,%(phone)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) ON DUPLICATE KEY UPDATE name=VALUES(name), mainFlag=VALUES(mainFlag), phone=VALUES(phone), mail=VALUES(mail), checkIn=VALUES(checkIn), checkOut=VALUES(checkOut), dateUp=VALUES(dateUp), status=VALUES(status), integrator=VALUES(integrator), channel=VALUES(channel) """ for r in res_list: try: cursor.execute(sql, r) except Exception as e: log_error(f"Erro no upsert reservations {r.get('idReservation')}: {e}") conn.commit() cursor.close() conn.close() log_system(f"Upsert em reservations concluído: {len(res_list)} registros.") def upsert_reservation_details(details_list): conn = mysql.connector.connect(host="localhost", user="root", password="", database="bihits") cursor = conn.cursor() sql = """ INSERT INTO reservation_details (idReservation,idEntityCompany,companyName,idRequesterCompany, requesterCompanyName,groupName,contactName,contact1,contact2, dateAdd,dateUp,notes,rooms,guests,commissions,revenueManagement,creditState) VALUES (%(idReservation)s,%(idEntityCompany)s,%(companyName)s,%(idRequesterCompany)s, %(requesterCompanyName)s,%(groupName)s,%(contactName)s,%(contact1)s,%(contact2)s, %(dateAdd)s,%(dateUp)s,%(notes_json)s,%(rooms_json)s,%(guests_json)s, %(commissions_json)s,%(revenueManagement_json)s,%(creditState)s) ON DUPLICATE KEY UPDATE companyName=VALUES(companyName), contactName=VALUES(contactName), dateUp=VALUES(dateUp), notes=VALUES(notes), rooms=VALUES(rooms), guests=VALUES(guests), commissions=VALUES(commissions), revenueManagement=VALUES(revenueManagement), creditState=VALUES(creditState) """ for d in details_list: params = { **d, "notes_json": json.dumps(d.get("notes", [])), "rooms_json": json.dumps(d.get("rooms", [])), "guests_json": json.dumps(d.get("guests", [])), "commissions_json": json.dumps(d.get("commissions", [])), "revenueManagement_json": json.dumps(d.get("revenueManagement", {})) } try: cursor.execute(sql, params) except Exception as e: log_error(f"Erro no upsert reservation_details {d.get('idReservation')}: {e}") conn.commit() cursor.close() conn.close() log_system(f"Upsert em reservation_details concluído: {len(details_list)} registros.") # ============================================= # Main # ============================================= def main(): base_dir = "dados-reservation" if not os.path.exists(base_dir): log_system(f"Pasta '{base_dir}' não encontrada. Carga completa: truncando tabelas.") truncate_tables() os.makedirs(base_dir) token = get_token() if not token: log_error("Token não obtido, abortando.") return start_date = "2025-01-01T00:00:00Z" end_date = datetime.now(timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ") ensure_tables() reservations = fetch_reservations(token, start_date, end_date) res_path = os.path.join(base_dir, "reservations.json") with open(res_path, "w", encoding="utf-8") as f: json.dump(reservations, f, ensure_ascii=False, indent=2) log_system(f"Salvo {len(reservations)} em {res_path}") details = [] for r in reservations: rid = r.get("idReservation") if rid is not None: d = fetch_reservation_by_id(token, rid) if d: details.append(d) time.sleep(0.2) det_path = os.path.join(base_dir, "reservation_details.json") with open(det_path, "w", encoding="utf-8") as f: json.dump(details, f, ensure_ascii=False, indent=2) log_system(f"Salvo {len(details)} em {det_path}") upsert_reservations(reservations) upsert_reservation_details(details) if __name__ == "__main__": main()