263 lines
9.9 KiB
Python
263 lines
9.9 KiB
Python
"""
|
|
GL Accounts API — backend for Excel Web Office Script.
|
|
|
|
Architecture:
|
|
Excel Web (Office Script) → HTTP GET /gl/all → this API → PostgreSQL on vps107.geneva.hosting
|
|
|
|
Run locally:
|
|
pip install -r requirements.txt
|
|
uvicorn main:app --reload --port 8000
|
|
|
|
Expose for Excel Web (Office Scripts run on Microsoft servers, need a public URL):
|
|
ngrok http 8000
|
|
→ copy the https://xxxx.ngrok.io URL into the Office Script API_BASE constant
|
|
"""
|
|
|
|
from fastapi import FastAPI, HTTPException, Query, Request
|
|
from fastapi.middleware.cors import CORSMiddleware
|
|
from fastapi.responses import HTMLResponse, PlainTextResponse
|
|
from dotenv import load_dotenv
|
|
import psycopg2
|
|
import os
|
|
|
|
load_dotenv()
|
|
|
|
app = FastAPI(title="GL Accounts API")
|
|
|
|
app.add_middleware(
|
|
CORSMiddleware,
|
|
allow_origins=["*"], # Excel Web / Office Scripts need open CORS for POC
|
|
allow_methods=["GET"],
|
|
allow_headers=["*"],
|
|
)
|
|
|
|
DB_CONFIG = {
|
|
"host": os.getenv("DB_HOST", "vps107.geneva.hosting"),
|
|
"port": int(os.getenv("DB_PORT", "5432")),
|
|
"dbname": os.getenv("DB_NAME", "tradon"),
|
|
"user": os.getenv("DB_USER", "tradon_readonly"),
|
|
"password": os.getenv("DB_PASSWORD", "change_me"),
|
|
"connect_timeout": 10,
|
|
}
|
|
|
|
# Ordered list used by the Office Script to map rows 6-13 in the Functions sheet
|
|
GL_FUNCTIONS = [
|
|
"get_account_base_amount",
|
|
"get_account_real_base_amount",
|
|
"get_account_amount",
|
|
"get_account_abbr",
|
|
"get_account_name",
|
|
"get_account_contact",
|
|
"get_account_contact_code",
|
|
"get_account_contact_name",
|
|
]
|
|
|
|
|
|
def run_query(function_name: str, company: str, account: str, currency: str, value_date: str):
|
|
with psycopg2.connect(**DB_CONFIG) as conn:
|
|
with conn.cursor() as cur:
|
|
cur.execute(
|
|
f"SELECT excel_api.{function_name}(%s, %s, %s, %s::date)",
|
|
(company, account, currency, value_date),
|
|
)
|
|
row = cur.fetchone()
|
|
return row[0] if row and row[0] is not None else None
|
|
|
|
|
|
@app.get("/gl/all")
|
|
def get_all(
|
|
company: str = Query(..., description="Company party code or name"),
|
|
account: str = Query(..., description="GL account code"),
|
|
currency: str = Query(..., description="Currency code / name / id"),
|
|
date: str = Query(..., description="Cutoff date YYYY-MM-DD"),
|
|
):
|
|
"""
|
|
Returns all 8 GL function results in one round-trip.
|
|
Called by the Office Script RefreshGLApi.ts.
|
|
"""
|
|
if function_name_invalid := [f for f in GL_FUNCTIONS if f not in GL_FUNCTIONS]:
|
|
raise HTTPException(400, f"Unknown function: {function_name_invalid}")
|
|
|
|
results: dict = {}
|
|
errors: dict = {}
|
|
|
|
for fn in GL_FUNCTIONS:
|
|
try:
|
|
results[fn] = run_query(fn, company, account, currency, date)
|
|
except Exception as e:
|
|
results[fn] = None
|
|
errors[fn] = str(e)
|
|
|
|
return {"results": results, "errors": errors or None}
|
|
|
|
|
|
@app.get("/health")
|
|
def health():
|
|
try:
|
|
with psycopg2.connect(**DB_CONFIG) as conn:
|
|
with conn.cursor() as cur:
|
|
cur.execute("SELECT 1")
|
|
return {"status": "ok", "db": "connected"}
|
|
except Exception as e:
|
|
raise HTTPException(503, f"DB unreachable: {e}")
|
|
|
|
|
|
# ── Excel Custom Functions Add-in ────────────────────────────────────────────
|
|
|
|
ADDIN_GUID = "3F4A8B2C-9D1E-4F5A-B6C7-8D9E0F1A2B3C"
|
|
|
|
|
|
@app.get("/addin/icon.png")
|
|
def addin_icon():
|
|
# Minimal 1x1 green PNG
|
|
import base64
|
|
png = base64.b64decode(
|
|
"iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVR42mNk+M9QDwADhgGAWjR9awAAAABJRU5ErkJggg=="
|
|
)
|
|
from fastapi.responses import Response
|
|
return Response(content=png, media_type="image/png")
|
|
|
|
|
|
@app.get("/addin/manifest.xml", response_class=PlainTextResponse)
|
|
def addin_manifest(request: Request):
|
|
base = str(request.base_url).rstrip("/")
|
|
xml = f"""<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
|
|
<OfficeApp xmlns="http://schemas.microsoft.com/office/appforoffice/1.1"
|
|
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
|
|
xmlns:bt="http://schemas.microsoft.com/office/officeappbasictypes/1.0"
|
|
xsi:type="TaskPaneApp">
|
|
<Id>{ADDIN_GUID}</Id>
|
|
<Version>1.0.0.0</Version>
|
|
<ProviderName>Tradon</ProviderName>
|
|
<DefaultLocale>en-US</DefaultLocale>
|
|
<DisplayName DefaultValue="Tradon GL API"/>
|
|
<Description DefaultValue="GL account functions backed by PostgreSQL"/>
|
|
<IconUrl DefaultValue="{base}/addin/icon.png"/>
|
|
<HighResolutionIconUrl DefaultValue="{base}/addin/icon.png"/>
|
|
<SupportUrl DefaultValue="https://vps107.geneva.hosting"/>
|
|
<AppDomains>
|
|
<AppDomain>{base}</AppDomain>
|
|
</AppDomains>
|
|
<Hosts>
|
|
<Host Name="Workbook"/>
|
|
</Hosts>
|
|
<DefaultSettings>
|
|
<SourceLocation DefaultValue="{base}/addin/taskpane.html"/>
|
|
</DefaultSettings>
|
|
<Permissions>ReadWriteDocument</Permissions>
|
|
<VersionOverrides xmlns="http://schemas.microsoft.com/office/taskpaneappversionoverrides"
|
|
xmlns:bt="http://schemas.microsoft.com/office/officeappbasictypes/1.0"
|
|
xsi:type="VersionOverridesV1_0">
|
|
<Hosts>
|
|
<Host xsi:type="Workbook">
|
|
<AllFormFactors>
|
|
<ExtensionPoint xsi:type="CustomFunctions">
|
|
<Script>
|
|
<SourceLocation resid="Functions.Script.Url"/>
|
|
</Script>
|
|
<Page>
|
|
<SourceLocation resid="Functions.Page.Url"/>
|
|
</Page>
|
|
<Metadata>
|
|
<SourceLocation resid="Functions.Metadata.Url"/>
|
|
</Metadata>
|
|
<Namespace resid="Functions.Namespace"/>
|
|
</ExtensionPoint>
|
|
</AllFormFactors>
|
|
</Host>
|
|
</Hosts>
|
|
<Resources>
|
|
<bt:Urls>
|
|
<bt:Url id="Functions.Script.Url" DefaultValue="{base}/addin/functions.js"/>
|
|
<bt:Url id="Functions.Page.Url" DefaultValue="{base}/addin/functions.html"/>
|
|
<bt:Url id="Functions.Metadata.Url" DefaultValue="{base}/addin/functions.json"/>
|
|
</bt:Urls>
|
|
<bt:ShortStrings>
|
|
<bt:String id="Functions.Namespace" DefaultValue="TRADON"/>
|
|
</bt:ShortStrings>
|
|
</Resources>
|
|
</VersionOverrides>
|
|
</OfficeApp>"""
|
|
return PlainTextResponse(content=xml, media_type="application/xml")
|
|
|
|
|
|
@app.get("/addin/functions.html", response_class=HTMLResponse)
|
|
def addin_functions_html():
|
|
return HTMLResponse("""<!DOCTYPE html><html><head>
|
|
<script src="https://appsforoffice.microsoft.com/lib/1/hosted/office.js"></script>
|
|
<script src="/addin/functions.js"></script>
|
|
</head><body></body></html>""")
|
|
|
|
|
|
@app.get("/addin/taskpane.html", response_class=HTMLResponse)
|
|
def addin_taskpane():
|
|
return HTMLResponse("""<!DOCTYPE html><html><head>
|
|
<script src="https://appsforoffice.microsoft.com/lib/1/hosted/office.js"></script>
|
|
</head><body><p>Tradon GL API Add-in loaded.</p></body></html>""")
|
|
|
|
|
|
@app.get("/addin/functions.json")
|
|
def addin_functions_json():
|
|
functions = [
|
|
("GET_ACCOUNT_BASE_AMOUNT", "get_account_base_amount", "Balance converted to base currency"),
|
|
("GET_ACCOUNT_REAL_BASE_AMOUNT", "get_account_real_base_amount", "Posted balance in base currency"),
|
|
("GET_ACCOUNT_AMOUNT", "get_account_amount", "Balance in transaction currency"),
|
|
("GET_ACCOUNT_ABBR", "get_account_abbr", "Account abbreviation/code"),
|
|
("GET_ACCOUNT_NAME", "get_account_name", "Account name"),
|
|
("GET_ACCOUNT_CONTACT", "get_account_contact", "Contact (code or name)"),
|
|
("GET_ACCOUNT_CONTACT_CODE", "get_account_contact_code", "Contact party codes"),
|
|
("GET_ACCOUNT_CONTACT_NAME", "get_account_contact_name", "Contact party names"),
|
|
]
|
|
params = [
|
|
{"name": "company", "dimensionality": "scalar", "type": "string"},
|
|
{"name": "account", "dimensionality": "scalar", "type": "string"},
|
|
{"name": "currency", "dimensionality": "scalar", "type": "string"},
|
|
{"name": "date", "dimensionality": "scalar", "type": "string"},
|
|
]
|
|
return {
|
|
"functions": [
|
|
{"id": fn_id, "name": fn_id, "description": desc,
|
|
"parameters": params, "result": {"dimensionality": "scalar", "type": "string"}}
|
|
for fn_id, _, desc in functions
|
|
]
|
|
}
|
|
|
|
|
|
@app.get("/addin/functions.js", response_class=PlainTextResponse)
|
|
def addin_functions_js(request: Request):
|
|
base = str(request.base_url).rstrip("/")
|
|
js = f"""/* Tradon GL Custom Functions — auto-generated, base={base} */
|
|
const _API = "{base}";
|
|
const _HDR = {{"ngrok-skip-browser-warning": "true"}};
|
|
|
|
async function _gl(fn, company, account, currency, date) {{
|
|
const url = _API + "/gl/all?company=" + encodeURIComponent(company)
|
|
+ "&account=" + encodeURIComponent(account)
|
|
+ "¤cy=" + encodeURIComponent(currency)
|
|
+ "&date=" + encodeURIComponent(date);
|
|
const r = await fetch(url, {{headers: _HDR}});
|
|
if (!r.ok) throw new Error("HTTP " + r.status);
|
|
const d = await r.json();
|
|
const v = d.results[fn];
|
|
return v !== null && v !== undefined ? v : "";
|
|
}}
|
|
|
|
CustomFunctions.associate("GET_ACCOUNT_BASE_AMOUNT",
|
|
(c,a,u,d) => _gl("get_account_base_amount", c,a,u,d));
|
|
CustomFunctions.associate("GET_ACCOUNT_REAL_BASE_AMOUNT",
|
|
(c,a,u,d) => _gl("get_account_real_base_amount", c,a,u,d));
|
|
CustomFunctions.associate("GET_ACCOUNT_AMOUNT",
|
|
(c,a,u,d) => _gl("get_account_amount", c,a,u,d));
|
|
CustomFunctions.associate("GET_ACCOUNT_ABBR",
|
|
(c,a,u,d) => _gl("get_account_abbr", c,a,u,d));
|
|
CustomFunctions.associate("GET_ACCOUNT_NAME",
|
|
(c,a,u,d) => _gl("get_account_name", c,a,u,d));
|
|
CustomFunctions.associate("GET_ACCOUNT_CONTACT",
|
|
(c,a,u,d) => _gl("get_account_contact", c,a,u,d));
|
|
CustomFunctions.associate("GET_ACCOUNT_CONTACT_CODE",
|
|
(c,a,u,d) => _gl("get_account_contact_code", c,a,u,d));
|
|
CustomFunctions.associate("GET_ACCOUNT_CONTACT_NAME",
|
|
(c,a,u,d) => _gl("get_account_contact_name", c,a,u,d));
|
|
"""
|
|
return PlainTextResponse(content=js, media_type="application/javascript")
|