AiDOPWarehouse/doc/plan/sql/uat-data/_tmp_sd7_query_roles.py

#!/usr/bin/env python3
import re
from pathlib import Path

import pymysql

raw = Path(__file__).resolve().parents[4].joinpath(
    "server/Admin.NET.Application/Configuration/Database.json"
).read_text(encoding="utf-8-sig")
value = next(
    item
    for item in re.findall(r'(?m)^\s*"ConnectionString"\s*:\s*"([^"]+)"', raw)
    if "Database=aidopdev" in item
)
parts = {
    item.split("=", 1)[0].strip().lower(): item.split("=", 1)[1].strip()
    for item in value.split(";")
    if "=" in item
}
conn = pymysql.connect(
    host=parts["server"],
    port=int(parts["port"]),
    user=parts["uid"],
    password=parts["pwd"],
    database=parts["database"],
    charset="utf8mb4",
    cursorclass=pymysql.cursors.DictCursor,
)
cur = conn.cursor()
cur.execute(
    """
    SELECT CAST(r.Id AS CHAR) role_id, r.Code, r.Status role_status,
           CAST(r.TenantId AS CHAR) tenant_id, r.Name
    FROM SysRole r
    WHERE r.Code LIKE 'SMART_OPS%'
      AND r.TenantId IN (838257186181189,838257212780613,838257237606469)
    ORDER BY r.TenantId, r.Code, r.Id
    """
)
print("ROLES")
for row in cur.fetchall():
    print(row)
cur.execute(
    """
    SELECT CAST(r.Id AS CHAR) role_id, r.Code, CAST(ur.UserId AS CHAR) user_id,
           u.Account, u.Status user_status, CAST(u.TenantId AS CHAR) user_tenant
    FROM SysRole r
    LEFT JOIN SysUserRole ur ON ur.RoleId=r.Id
    LEFT JOIN SysUser u ON u.Id=ur.UserId
    WHERE r.Code='SMART_OPS_DEPT_HEAD' AND r.TenantId=838257186181189
    """
)
print("A DEPT HEAD BINDINGS")
for row in cur.fetchall():
    print(row)
cur.execute("SHOW COLUMNS FROM SysUserRole")
print("USERROLE COLS", [x["Field"] for x in cur.fetchall()])
conn.close()