แปลง 4GL → SQL Query

CREATE VIEW vw_coa_report AS
SELECT
    -- Header
    h.t$coan                    AS coa_number,
    h.t$item                    AS item_code,
    item.t$dsca                 AS item_description,
    h.t$stbp                    AS ship_to_bp,
    bp.t$nama                   AS bp_name,
    CONCAT(addr.t$ln01, ' ', addr.t$ln02) AS bp_address,
    addr.t$ccty                 AS country,
    CASE addr.t$ccty
        WHEN 'THA' THEN 'Local' ELSE 'Export'
    END                         AS coa_type,
    h.t$cwar                    AS warehouse,
    h.t$loca                    AS location,
    h.t$qnty                    AS quantity,
    u.t$dsca                    AS unit_desc,
    h.t$mfgd                    AS mfg_date,
    h.t$expd                    AS exp_date,
    
    -- Detail
    d.t$prts                    AS sort_key,
    d.t$char                    AS char_code,
    c.t$dsca                    AS char_name,
    c.t$ctyp                    AS char_type,
    d.t$mval                    AS measured_value,
    COALESCE(opt.t$dsca, CAST(d.t$mval AS VARCHAR))
                                AS result_display,
    d.t$llmt                    AS lower_limit,
    d.t$ulmt                    AS upper_limit,
    d.t$fxsp                    AS fixed_spec,
    d.t$prto                    AS spec_type,
    c.t$cuni                    AS char_unit

FROM      sccoa003 h
JOIN      sccoa004 d   ON d.t$coan = h.t$coan
JOIN      qmptc001 c   ON c.t$char = d.t$char
LEFT JOIN tccom100 bp  ON bp.t$bpid = h.t$stbp
LEFT JOIN tccom130 addr ON addr.t$cadr = bp.t$cadr
LEFT JOIN tcibd001 item ON item.t$item = h.t$item
LEFT JOIN tcmcs001 u   ON u.t$cuni = h.t$cuni
LEFT JOIN qmptc014 opt ON opt.t$oset = d.t$oset 
                        AND opt.t$optn = d.t$mopt
WHERE     h.t$tbvr = 0  -- เฉพาะ COA ที่ผ่าน QC
ORDER BY h.t$coan, d.t$prts;

import pyodbc
from flask import Flask, jsonify
from datetime import datetime, timedelta

# ===== Config (ย้ายมาจาก Hardcode ในโปรแกรม) =====
HIGH_PRECISION_CHARS = ["SP_GRAV2", "RE_DEN25", "RI_25C"]

BP_CONFIG = {
    "110472": {
        "vendor_no": "VenderSiteNo: 100786789",
        "vendor_city": "VenderSiteCity: SamutPrakan TH"
    }
}

ITEM_PACKAGING = {"03020": "IBC"}

# ===== Business Logic Functions =====
def utc_to_date(utc_val):
    """Logic จากบรรทัด 675-703 ของ COA.4gl"""
    if not utc_val or utc_val == 0:
        return None
    dt = datetime(1970, 1, 1) + timedelta(seconds=utc_val)
    return dt.strftime("%d/%m/%Y")

def format_result(char_code, char_type, mval, opt_desc):
    """Logic จากบรรทัด 290-360 ของ COA.4gl"""
    if char_type == 1:  # Option
        return opt_desc or ""
    elif char_type == 2:  # Fraction
        decimals = 4 if char_code.strip() in HIGH_PRECISION_CHARS else 2
        return f"{mval:.{decimals}f}"
    else:  # Integer
        return str(int(mval)) if mval else "0"

def format_spec(prto, llmt, ulmt, fxsp, char_code, char_type):
    """Logic จากบรรทัด 409-572 ของ COA.4gl"""
    fmt = lambda v: format_result(char_code, char_type, v, None)
    specs = {
        1: lambda: f"Min. {fmt(llmt)}",
        2: lambda: f"Max. {fmt(ulmt)}",
        3: lambda: f"{fmt(llmt)} - {fmt(ulmt)}",
        4: lambda: f"Min. {fmt(llmt)}",
        5: lambda: f"Max. {fmt(ulmt)}",
        6: lambda: fxsp or "",
    }
    return specs.get(prto, lambda: "")()

@app.route('/api/coa/<coa_number>')
def get_coa(coa_number):
    cursor.execute("SELECT * FROM vw_coa_report WHERE coa_number = ?", coa_number)
    rows = cursor.fetchall()
    
    if not rows:
        return jsonify({"error": "COA not found"}), 404
    
    header = rows[0]
    bp_extra = BP_CONFIG.get(header.ship_to_bp.strip()[:6], {})
    
    return jsonify({
        "coa_number": coa_number,
        "coa_type": header.coa_type,
        "mfg_date": utc_to_date(header.mfg_date),  # ← Logic #4
        "exp_date": utc_to_date(header.exp_date),
        "vendor_info": bp_extra,                   # ← Logic #6
        "packaging": ITEM_PACKAGING.get(header.item_code.strip(), ""),
        "details": [{
            "name": r.char_name,
            "result": format_result(                 # ← Logic #1, #2
                r.char_code, r.char_type, r.measured_value, r.option_desc),
            "spec": format_spec(                      # ← Logic #3
                r.spec_type, r.lower_limit, r.upper_limit, 
                r.fixed_spec, r.char_code, r.char_type),
            "unit": r.char_unit
        } for r in rows]
    })