import sys import requests import json import os import time import mysql.connector import logging from datetime import datetime, timedelta, timezone from zoneinfo import ZoneInfo # Configurações TZ = ZoneInfo('America/Fortaleza') FULL_SYNC_START = '2024-01-01T00:00:00Z' DAILY_RETRO_DAYS = 90 FULL_SYNC_HOUR = 2 POLL_INTERVAL = 300 PAGE_SIZE = 50 # Diretórios base def get_base_dirs(): if getattr(sys, 'frozen', False): root = os.path.dirname(sys.argv[0]) else: root = os.path.abspath(os.path.dirname(__file__)) dist = os.path.join(root, 'dist') integrator = 'integrator_reservation' base = os.path.join(dist, integrator) data = os.path.join(base, 'data') log_root = os.path.join(base, 'logs') return base, data, log_root BASE_DIR, DATA_DIR, LOG_ROOT = get_base_dirs() BIN_DIR = os.path.join(BASE_DIR, 'bin') LOG_BASE = LOG_ROOT LOG_DIRS = { 'errors': os.path.join(LOG_BASE, 'errors'), 'updates': os.path.join(LOG_BASE, 'updates'), 'inserts': os.path.join(LOG_BASE, 'inserts'), 'operations': os.path.join(LOG_BASE, 'operations'), 'api': os.path.join(LOG_BASE, 'api'), } # Cria diretórios os.makedirs(BIN_DIR, exist_ok=True) os.makedirs(DATA_DIR, exist_ok=True) for d in LOG_DIRS.values(): os.makedirs(d, exist_ok=True) # Logger setup def setup_logger(name, path, level=logging.INFO, console=True): logger = logging.getLogger(name) if logger.hasHandlers(): return logger logger.setLevel(level) fmt = logging.Formatter('%(asctime)s [%(levelname)s] %(message)s', '%Y-%m-%d %H:%M:%S') fh = logging.FileHandler(path, encoding='utf-8') fh.setFormatter(fmt) logger.addHandler(fh) if console: ch = logging.StreamHandler() ch.setFormatter(fmt) logger.addHandler(ch) return logger # Cria loggers log_ops = setup_logger('operations', os.path.join(LOG_DIRS['operations'], 'operations.log')) log_err = setup_logger('errors', os.path.join(LOG_DIRS['errors'], 'errors.log'), level=logging.ERROR) log_upd = setup_logger('updates', os.path.join(LOG_DIRS['updates'], 'updates.log')) log_ins = setup_logger('inserts', os.path.join(LOG_DIRS['inserts'], 'inserts.log')) log_api = setup_logger('api', os.path.join(LOG_DIRS['api'], 'api.log')) # Conexão ao banco def get_db_connection(): return mysql.connector.connect( host=os.getenv('DB_HOST', 'localhost'), user=os.getenv('DB_USER', 'root'), password=os.getenv('DB_PASS', ''), database=os.getenv('DB_NAME', 'bihits') ) # Parser de datas ISO def parse_iso(dt_str): if dt_str.endswith('Z'): dt_str = dt_str[:-1] + '+00:00' return datetime.fromisoformat(dt_str) # Funções de leitura de estado existente no banco def get_existing_reservations_map(): conn = get_db_connection() cur = conn.cursor() cur.execute("SELECT idReservation, idEntity, dateUp FROM reservations") mapping = { (rid, eid): du for rid, eid, du in cur } cur.close() conn.close() return mapping def get_existing_details_map(): conn = get_db_connection() cur = conn.cursor() cur.execute("SELECT idReservation, dateUp FROM reservation_details") mapping = { rid: du for rid, du in cur } cur.close() conn.close() return mapping # Cache JSON def load_reservations_cache(path): if not os.path.isfile(path): return {} with open(path, 'r', encoding='utf-8') as f: data = json.load(f) return {(item['idReservation'], item['idEntity']): item for item in data} def load_details_cache(path): if not os.path.isfile(path): return {} with open(path, 'r', encoding='utf-8') as f: data = json.load(f) return {item['idReservation']: item for item in data} def save_cache(path, data_dict): with open(path, 'w', encoding='utf-8') as f: json.dump(list(data_dict.values()), f, ensure_ascii=False, indent=2) # 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_ops.info("Tabelas truncadas.") def ensure_tables(): pass # HTTP Auth and Requests def get_token(): url = "https://bric-investment.com/BI-HITS/dados/Hits/token/token.php" payload = {"username": os.getenv('API_USER'), "password": os.getenv('API_PASS')} try: r = requests.post(url, data=payload, timeout=30) r.raise_for_status() text = r.text.strip() prefix = "Token obtido:" token = text.replace(prefix, "").strip() if text.startswith(prefix) else r.json().get('token') log_ops.info("Token obtido com sucesso") return token except Exception as e: log_err.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): try: r = requests.get(url, headers=headers, params=params, timeout=timeout) log_api.info(f"Request {r.url} → Status {r.status_code}") if r.status_code == 429: log_api.info(f"429 recebido, retry em {delay}s (tentativa {attempt+1}/6)") time.sleep(delay) delay *= 2 continue if r.status_code == 401: new_token = get_token() if new_token: headers["Authorization"] = f"Bearer {new_token}" continue r.raise_for_status() return r except requests.RequestException as ex: log_api.error(f"Request error: {ex}") time.sleep(delay) delay *= 2 log_err.error(f"Falha após múltiplas tentativas: {url}") return None def fetch_reservations(token, start_date, end_date, type_filter, status, page_size=PAGE_SIZE): reservations = [] page = 0 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}" } while True: params = {"Type": type_filter, "Status": status, "InitialDate": start_date, "FinalDate": end_date, "Page": page, "Size": page_size} log_ops.info(f"➡️ Fetch Reservations: status={status}, page={page}") resp = safe_get(url, headers, params) if not resp: break data = resp.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_ops.info(f"🔍 Fetch detail for reservation {reservation_id}") resp = safe_get(url, headers) return resp.json() if resp else None # Upsert em reservations def upsert_reservations(res_list): existing = { rid: dt for rid, dt in get_existing_reservations_map().items() } 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) """ conn = get_db_connection() cur = conn.cursor() 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_ins.info(f"reservations {r['idReservation']} inserted") else: log_upd.info(f"reservations {r['idReservation']} updated") conn.commit() cur.close() conn.close() # Upsert em reservation_details def upsert_reservation_details(details_list): existing = { rid: dt for rid, dt in get_existing_details_map().items() } 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 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) """ conn = get_db_connection() cur = conn.cursor() 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_ins.info(f"reservation_details {rid} inserted") else: log_upd.info(f"reservation_details {rid} updated") conn.commit() cur.close() conn.close() # Fluxo principal def main(): last_full_date = None path_res = os.path.join(DATA_DIR, 'reservations.json') path_det = os.path.join(DATA_DIR, 'reservation_details.json') # Detect first run: if either cache file does not exist, truncate the tables initial_run = not os.path.isfile(path_res) or not os.path.isfile(path_det) if initial_run: log_ops.info("🚀 Initial run detected – truncating tables before sync.") truncate_tables() old_res = load_reservations_cache(path_res) old_det = load_details_cache(path_det) save_cache(path_res, old_res) save_cache(path_det, old_det) while True: now_utc = datetime.now(timezone.utc) now_local = now_utc.astimezone(TZ) today = now_local.date() if last_full_date != today and now_local.hour >= FULL_SYNC_HOUR: start_date = FULL_SYNC_START last_full_date = today log_ops.info("🔄 Starting full sync for entire period") else: start_date = (now_utc - timedelta(days=DAILY_RETRO_DAYS)).strftime('%Y-%m-%dT%H:%M:%SZ') end_date = now_utc.strftime('%Y-%m-%dT%H:%M:%SZ') log_ops.info(f"📅 Sync window: {start_date} to {end_date}") token = get_token() if not token: log_err.error("Token not obtained, skipping cycle.") time.sleep(POLL_INTERVAL) continue new_res_map = {} new_det_map = {} statuses = [1, 2, 3, 4] for idx, status in enumerate(statuses): recs = fetch_reservations(token, start_date, end_date, type_filter=1, status=status) api_map = {} for r in recs: key = (r['idReservation'], r['idEntity']) if key not in api_map or parse_iso(r['dateUp']) > parse_iso(api_map[key]['dateUp']): api_map[key] = r to_delete = set(old_res) - set(api_map) if to_delete: with get_db_connection() as conn: cur = conn.cursor() for rid, eid in to_delete: cur.execute("DELETE FROM reservation_details WHERE idReservation=%s", (rid,)) cur.execute("DELETE FROM reservations WHERE idReservation=%s AND idEntity=%s", (rid, eid)) old_res.pop((rid, eid), None) old_det.pop(rid, None) conn.commit() to_fetch = [] for key, rec in api_map.items(): if key not in old_res or parse_iso(rec['dateUp']) > parse_iso(old_res[key]['dateUp']): to_fetch.append((key, rec)) old_res[key] = rec total = len(to_fetch) for num, (key, rec) in enumerate(to_fetch, start=1): rid, _ = key log_ops.info(f"📦 ({num}/{total}) Fetching details for reservation {rid}") det = fetch_reservation_by_id(token, rid) if det: date_up = parse_iso(det['dateUp']).strftime('%Y-%m') log_ops.info(f"📅 ({num}/{total}) Reservation {rid} dateUp: {date_up}") new_det_map[rid] = det old_det[rid] = det time.sleep(2.2) new_res_map.update(api_map) if idx < len(statuses) - 1: log_ops.info(f"⏱ Sleeping {POLL_INTERVAL//60}min until status {statuses[idx+1]}") time.sleep(POLL_INTERVAL) upsert_reservations(list(new_res_map.values())) upsert_reservation_details(list(new_det_map.values())) save_cache(path_res, old_res) save_cache(path_det, old_det) log_ops.info("✅ Cycle complete, waiting for next...") time.sleep(POLL_INTERVAL) if __name__ == '__main__': main()