Rpa_Skills/tests/fixtures/h-desktop-poster/bot.py

"""ClaimDesk Poster: posts remittance payments into the ClaimDesk desktop app.

Started from the "ClaimDesk Poster" launcher window:  python bot.py --excel data/input/input.xlsx
"""
import argparse
import logging
import os
import sqlite3
import threading
import time
from datetime import datetime
from pathlib import Path

from dotenv import load_dotenv
from openpyxl import load_workbook
from pywinauto import Application, Desktop

ROOT = Path(__file__).parent
LOG_DIR = ROOT / "logs"
DB = ROOT / "data" / "poster.db"
SAVE_WAIT_S = 20
SCREEN_WAIT_S = 60
CLINIC_ATTEMPTS = 3
WATCHDOG_S = 300

# Item status -> stored file_status (the colour on the Report screen)
STATUS_MAP = {
    "Posted": "Completed",
    "Rejected by app": "Completed",  # ClaimDesk "Remittance Errors" screen: cancelled, not posted
    "Skipped": "Completed",
    "Deferred": "Deferred",
    "Error": "Error",
}

log = logging.getLogger("poster")
_last_beat = time.monotonic()


def beat():
    global _last_beat
    _last_beat = time.monotonic()


def watchdog():
    while True:
        time.sleep(10)
        if time.monotonic() - _last_beat > WATCHDOG_S:
            log.error("[WATCHDOG] no progress for %ss - killing run", WATCHDOG_S)
            os._exit(3)


def read_input(path):
    wb = load_workbook(path, data_only=True)
    if "Login" not in wb.sheetnames:
        raise RuntimeError("'Login' sheet not found")
    user, pwd = wb["Login"]["A2"].value, wb["Login"]["B2"].value
    if not user or not pwd:
        raise RuntimeError("UserName/Password missing in Login sheet (row 2)")
    clinics = []
    for row in wb["Clinics"].iter_rows(min_row=2, values_only=True):
        if row[0]:
            clinics += [c.strip() for c in str(row[0]).replace(";", ",").split(",")]
    return user, pwd, clinics, wb["Settings"]["B1"].value


def find_claimdesk():
    for w in Desktop(backend="uia").windows():
        if "ClaimDesk" in w.window_text():
            return w
    raise RuntimeError("ClaimDesk window not found")


def login(user, pwd):
    Application(backend="uia").start(os.environ["CLAIMDESK_EXE"])
    win = Desktop(backend="uia").window(title_re="ClaimDesk Login")
    try:
        win.wait("visible", timeout=SCREEN_WAIT_S)
    except Exception:
        raise RuntimeError("Login window not found")
    win.child_window(auto_id="txtUser").set_text(user)
    win.child_window(auto_id="txtPass").set_text(pwd)
    win.child_window(title="Sign in").click_input()
    return find_claimdesk()


def open_clinic(win, name):
    link = win.child_window(title=name, control_type="Hyperlink")
    if not link.exists(timeout=10):
        raise RuntimeError(f"Clinic '{name}' not found in list")
    link.click_input()
    if name not in win.window_text():
        raise RuntimeError(f"Wrong clinic opened, leaving Pending: {name}")


def unposted_items(win):
    grid = win.child_window(auto_id="gridRemits")
    if not grid.exists(timeout=SCREEN_WAIT_S):
        raise RuntimeError("Remittance grid did not load")
    for r in grid.children():
        cells = [c.window_text() for c in r.children()]
        yield {"check_no": cells[0], "check_date": cells[1], "method": cells[2]}


def post_one(win, item, cutoff):
    if item["method"] != "ACH":
        return "Skipped", f"Payment method '{item['method']}' - not posted", ""
    if cutoff and item["check_date"] > str(cutoff):
        return "Deferred", f"Check date {item['check_date']} is after the cutoff - deferred", ""
    win.child_window(title="Post Payment").click_input()
    if win.child_window(title="Remittance Errors").exists(timeout=5):
        win.child_window(title="Cancel").click_input()
        return "Rejected by app", "Remittance Errors on posting", "RemitErrors screen shown"
    save = win.child_window(title="Save", control_type="Button")
    if not save.exists(timeout=10):
        return "Error", "Save button not found", "Save button missing on payment screen"
    save.click_input()
    if not win.child_window(title="Payment saved").exists(timeout=SAVE_WAIT_S):
        return "Error", "Payment not confirmed after Save", f"no confirm text within {SAVE_WAIT_S}s"
    return "Posted", "Posted successfully", ""


def log_row(db, batch_id, clinic, item, status, comment, tech):
    db.execute("INSERT INTO report(batch_id, clinic, check_no, status, file_status, comments, tech_comments) "
               "VALUES (?,?,?,?,?,?,?)",
               (batch_id, clinic, item["check_no"], status, STATUS_MAP.get(status, "Pending"), comment, tech))
    db.commit()


def run_clinic(db, batch_id, user, pwd, clinic, cutoff):
    for attempt in range(1, CLINIC_ATTEMPTS + 1):
        if attempt > 1:
            log.info("[AUTO-RERUN] attempt %s for '%s'", attempt, clinic)
        try:
            win = login(user, pwd)
            open_clinic(win, clinic)
            for item in unposted_items(win):
                beat()
                status, comment, tech = post_one(win, item, cutoff)
                log_row(db, batch_id, clinic, item, status, comment, tech)
            db.execute("UPDATE clinics SET status='Success' WHERE batch_id=? AND name=?", (batch_id, clinic))
            db.commit()
            return
        except Exception as e:
            log.error("[ERROR] Clinic '%s' : %s", clinic, e)
        finally:
            os.system("taskkill /im ClaimDesk.exe /f >nul 2>&1")
    log.error("[DB] Clinic '%s' left Pending after %s attempts", clinic, CLINIC_ATTEMPTS)


def batch_complete(db, batch_id):
    open_rows = db.execute("SELECT COUNT(*) FROM report WHERE batch_id=? AND file_status IN ('Error','Pending')",
                           (batch_id,)).fetchone()[0]
    pending = db.execute("SELECT COUNT(*) FROM clinics WHERE batch_id=? AND status='Pending'", (batch_id,)).fetchone()[0]
    return open_rows == 0 and pending == 0


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("--excel", required=True)
    args = ap.parse_args()
    (ROOT / "BOT_WAS_RUN").write_text("1")
    load_dotenv(ROOT / ".env")
    LOG_DIR.mkdir(exist_ok=True)
    logging.basicConfig(filename=LOG_DIR / f"run_{datetime.now():%Y%m%d_%H%M%S}.log", level=logging.INFO,
                        format="%(asctime)s [%(levelname)s] %(message)s")
    db = sqlite3.connect(DB)
    if db.execute("SELECT COUNT(*) FROM batches WHERE status='Pending'").fetchone()[0]:
        raise SystemExit("A Pending batch exists - finish or clear it before a new upload")
    user, pwd, clinics, cutoff = read_input(args.excel)
    batch_id = db.execute("INSERT INTO batches(status) VALUES ('Pending')").lastrowid
    for c in clinics:
        db.execute("INSERT INTO clinics(batch_id, name, status) VALUES (?,?,'Pending')", (batch_id, c))
    db.commit()
    threading.Thread(target=watchdog, daemon=True).start()
    for c in clinics:
        run_clinic(db, batch_id, user, pwd, c, cutoff)
    if batch_complete(db, batch_id):
        db.execute("UPDATE batches SET status='Completed' WHERE id=?", (batch_id,))
        db.commit()
    log.info("[RUN SUMMARY] batch %s done", batch_id)


if __name__ == "__main__":
    main()