394 lines
15 KiB
Python
394 lines
15 KiB
Python
"""
|
||
generate_bid_json.py
|
||
====================
|
||
Bulk JSON generator for bid submission payloads.
|
||
|
||
FOLDER STRUCTURE (input_root):
|
||
input_root/
|
||
<segment>/ e.g. GDAM | DAM | HPDAM
|
||
<order_type>/ e.g. Single | Block
|
||
<order_side>/ e.g. Buy | Sell
|
||
<TM_folder>/ e.g. C1001 → becomes userId AND tmId
|
||
<tacId>.xlsx ← one file per TAC / bid sheet
|
||
|
||
SEGMENT → JSON "segment" MAPPING:
|
||
GDAM → "GREEN"
|
||
DAM → "CONVENTIONAL"
|
||
HPDAM → "HPDAM"
|
||
|
||
SEGMENT → JSON order "source" MAPPING:
|
||
GDAM → "SL"
|
||
DAM → "CO"
|
||
HPDAM → "HP"
|
||
|
||
ORDER SIDE → JSON "orderSide" MAPPING:
|
||
Buy → "B"
|
||
Sell → "S"
|
||
|
||
ORDER TYPE → JSON "orderType" MAPPING:
|
||
Single → "N"
|
||
Block → "B"
|
||
|
||
EXCEL LAYOUT (every .xlsx):
|
||
Row 1 : "Select Source" | "Order 1" | "Order 2" ... "Order N"
|
||
Row 2 : "<label>" | <price1> | <price2> ... <priceN>
|
||
Row 3+: "HH:MM-HH:MM [slot]" | <qty1> | <qty2> ...
|
||
(rows where ALL qty cells are blank are skipped)
|
||
|
||
OUTPUT:
|
||
output_root/
|
||
<segment>/
|
||
<order_type>/
|
||
<order_side>/
|
||
<TM_folder>/
|
||
<tacId>_bid.json
|
||
|
||
CONFIGURABLE via CLI (or edit DEFAULTS below):
|
||
--input_root path to input folder tree
|
||
--output_root path where JSON tree is written
|
||
--product_code e.g. "IDAS(01)"
|
||
--password password applied to every payload
|
||
--ocf_opted Y or N
|
||
--ocf_premium_disc integer
|
||
--ocf_qty integer
|
||
|
||
FIXED / AUTO-DERIVED:
|
||
transcode = "3040" (always)
|
||
requestType = "N" (always)
|
||
productType = "I" (always)
|
||
userId = TM folder name
|
||
tmId = TM folder name
|
||
segment = derived from segment folder name (see mapping above)
|
||
orderSide = derived from order_side folder name
|
||
orderType = derived from order_type folder name
|
||
deliveryDate = tomorrow's date (auto-calculated at run time)
|
||
|
||
USAGE:
|
||
python generate_bid_json.py \\
|
||
--input_root ./input \\
|
||
--output_root ./output \\
|
||
--product_code "IDAS(01)" \\
|
||
--password "your_password"
|
||
|
||
Or just edit DEFAULTS below and run:
|
||
python generate_bid_json.py
|
||
"""
|
||
|
||
import argparse
|
||
import json
|
||
import re
|
||
import sys
|
||
from datetime import date, timedelta
|
||
from pathlib import Path
|
||
|
||
from openpyxl import load_workbook
|
||
|
||
|
||
# ─────────────────────────────────────────────────────────────────────────────
|
||
# DEFAULTS – edit here or override via CLI
|
||
# ─────────────────────────────────────────────────────────────────────────────
|
||
DEFAULTS = {
|
||
"input_root": "D:/Load testing input json/Input scenario upload files",
|
||
"output_root": "D:/Load testing input json/UAT_Team_10%_Load_File",
|
||
"product_code": "IDASTEST25",
|
||
"password": "CHANGE_ME",
|
||
"ocf_opted": "N",
|
||
"ocf_premium_disc": 0,
|
||
"ocf_qty": 0,
|
||
}
|
||
|
||
# ─────────────────────────────────────────────────────────────────────────────
|
||
# FIXED CONSTANTS
|
||
# ─────────────────────────────────────────────────────────────────────────────
|
||
TRANSCODE = "3040"
|
||
REQUEST_TYPE = "N"
|
||
PRODUCT_TYPE = "I"
|
||
|
||
# Segment folder name → JSON segment value
|
||
SEGMENT_MAP = {
|
||
"GDAM": "GREEN",
|
||
"DAM": "Conventional",
|
||
"HPDAM": "HPDAM",
|
||
}
|
||
|
||
# Order-type folder name → JSON orderType value
|
||
ORDER_TYPE_MAP = {
|
||
"Single": "N",
|
||
"Block": "B",
|
||
}
|
||
|
||
# Order-side folder name → JSON orderSide value
|
||
ORDER_SIDE_MAP = {
|
||
"Buy": "B",
|
||
"Sell": "S",
|
||
}
|
||
|
||
# Segment folder name → JSON order "source" value
|
||
SOURCE_MAP = {
|
||
"GDAM": "SL",
|
||
"DAM": "CO",
|
||
"HPDAM": "HP",
|
||
}
|
||
|
||
|
||
# ─────────────────────────────────────────────────────────────────────────────
|
||
# HELPERS
|
||
# ─────────────────────────────────────────────────────────────────────────────
|
||
def tomorrow_str() -> str:
|
||
"""Return tomorrow's date as 'DD-Mon-YYYY', e.g. '18-Jun-2026'."""
|
||
d = date.today() + timedelta(days=1)
|
||
# %-d is Linux-only; %#d is Windows-only — use manual strip for cross-platform
|
||
return d.strftime("%d-%b-%Y").lstrip("0")
|
||
|
||
|
||
def parse_excel(xlsx_path: Path, order_type_val: str, order_side_val: str, source_val: str, seg_folder: str, cfg: dict) -> list[dict]:
|
||
"""
|
||
Parse one bid Excel file and return a list of order dicts.
|
||
tacId is the filename stem (e.g. 'MCOC100500002').
|
||
"""
|
||
wb = load_workbook(str(xlsx_path), read_only=True, data_only=True)
|
||
ws = wb.active
|
||
rows = list(ws.iter_rows(values_only=True))
|
||
|
||
if len(rows) < 3:
|
||
raise ValueError(f"File has fewer than 3 rows: {xlsx_path}")
|
||
|
||
# Row 2 (index 1): price row
|
||
price_row = rows[1]
|
||
prices = []
|
||
for cell in price_row[1:]:
|
||
if cell is None or cell == "":
|
||
break
|
||
prices.append(int(round(float(cell))))
|
||
|
||
if not prices:
|
||
raise ValueError(f"No price columns found in row 2 of: {xlsx_path}")
|
||
|
||
tac_id = xlsx_path.stem
|
||
orders = []
|
||
|
||
for row in rows[2:]:
|
||
label = row[0]
|
||
if not label:
|
||
continue
|
||
match = re.search(r'\[(\d+)\]', str(label))
|
||
if not match:
|
||
continue
|
||
slot_num = int(match.group(1))
|
||
|
||
qtys = []
|
||
for i in range(len(prices)):
|
||
raw = row[i + 1] if (i + 1) < len(row) else None
|
||
qtys.append(round(abs(float(raw)), 4) if (raw is not None and raw != "") else None)
|
||
|
||
# Skip slots where every qty cell is blank
|
||
if all(q is None for q in qtys):
|
||
continue
|
||
|
||
pq_data = [
|
||
{"price": price, "qty": qty}
|
||
for price, qty in zip(prices, qtys)
|
||
if qty is not None
|
||
]
|
||
|
||
order = {
|
||
"tacId": tac_id,
|
||
"fromSlot": slot_num,
|
||
"toSlot": slot_num,
|
||
"orderType": order_type_val,
|
||
"orderSide": order_side_val,
|
||
"source": source_val,
|
||
"sourceType": "",
|
||
}
|
||
# OCF fields are excluded for HPDAM segment
|
||
if seg_folder != "HPDAM":
|
||
order["ocfOpted"] = cfg["ocf_opted"]
|
||
order["ocfPremiumDisc"] = cfg["ocf_premium_disc"]
|
||
order["ocfQty"] = cfg["ocf_qty"]
|
||
order["pqData"] = pq_data
|
||
orders.append(order)
|
||
|
||
return orders
|
||
|
||
|
||
def build_payload(tm_id: str, segment_val: str, orders: list[dict], cfg: dict) -> dict:
|
||
return {
|
||
"transcode": TRANSCODE,
|
||
"userId": tm_id,
|
||
"password": cfg["password"],
|
||
"tmId": tm_id,
|
||
"requestType": REQUEST_TYPE,
|
||
"productCode": cfg["product_code"],
|
||
"productType": PRODUCT_TYPE,
|
||
"segment": segment_val,
|
||
"deliveryDate": tomorrow_str(),
|
||
"orders": orders,
|
||
}
|
||
|
||
|
||
# ─────────────────────────────────────────────────────────────────────────────
|
||
# MAIN WALK
|
||
# ─────────────────────────────────────────────────────────────────────────────
|
||
def process_all(cfg: dict):
|
||
input_root = Path(cfg["input_root"])
|
||
output_root = Path(cfg["output_root"])
|
||
|
||
if not input_root.exists():
|
||
print(f"[ERROR] input_root not found: {input_root}", file=sys.stderr)
|
||
sys.exit(1)
|
||
|
||
total_files = 0
|
||
total_errors = 0
|
||
|
||
print(f"Delivery date (tomorrow): {tomorrow_str()}")
|
||
print(f"Input : {input_root.resolve()}")
|
||
print(f"Output : {output_root.resolve()}")
|
||
print("-" * 72)
|
||
|
||
# ── Level 1: segment folder (GDAM / DAM / HPDAM) ─────────────────────────
|
||
for seg_dir in sorted(input_root.iterdir()):
|
||
if not seg_dir.is_dir() or seg_dir.name.startswith("."):
|
||
continue
|
||
|
||
seg_folder = seg_dir.name
|
||
segment_val = SEGMENT_MAP.get(seg_folder)
|
||
if segment_val is None:
|
||
print(f"[WARN] Unknown segment folder '{seg_folder}' — skipping.")
|
||
continue
|
||
source_val = SOURCE_MAP[seg_folder] # guaranteed present since SEGMENT_MAP check passed
|
||
|
||
# ── Level 2: order type folder (Single / Block) ───────────────────────
|
||
for ot_dir in sorted(seg_dir.iterdir()):
|
||
if not ot_dir.is_dir():
|
||
continue
|
||
|
||
ot_folder = ot_dir.name
|
||
if ot_folder == "Block":
|
||
print(f" [SKIP] Block order-type folder ignored: {ot_dir.relative_to(input_root)}")
|
||
continue
|
||
order_type_val = ORDER_TYPE_MAP.get(ot_folder)
|
||
if order_type_val is None:
|
||
print(f"[WARN] Unknown order-type folder '{ot_folder}' — skipping.")
|
||
continue
|
||
|
||
# ── Level 3: order side folder (Buy / Sell) ───────────────────────
|
||
for os_dir in sorted(ot_dir.iterdir()):
|
||
if not os_dir.is_dir():
|
||
continue
|
||
|
||
os_folder = os_dir.name
|
||
order_side_val = ORDER_SIDE_MAP.get(os_folder)
|
||
if order_side_val is None:
|
||
print(f"[WARN] Unknown order-side folder '{os_folder}' — skipping.")
|
||
continue
|
||
# Fix 2: source is blank for Buy orders, segment-mapped only for Sell
|
||
effective_source = "" if os_folder == "Buy" else source_val
|
||
|
||
# ── Level 4: TM folder (e.g. C1001) ──────────────────────────
|
||
for tm_dir in sorted(os_dir.iterdir()):
|
||
if not tm_dir.is_dir():
|
||
continue
|
||
tm_id = tm_dir.name # → userId AND tmId
|
||
|
||
# ── Level 5: xlsx files ───────────────────────────────────
|
||
for xlsx_file in sorted(tm_dir.glob("*.xlsx")):
|
||
total_files += 1
|
||
rel_path = xlsx_file.relative_to(input_root)
|
||
print(f" {rel_path}", end=" ... ")
|
||
|
||
try:
|
||
orders = parse_excel(
|
||
xlsx_file,
|
||
order_type_val=order_type_val,
|
||
order_side_val=order_side_val,
|
||
source_val=effective_source,
|
||
seg_folder=seg_folder,
|
||
cfg=cfg,
|
||
)
|
||
|
||
payload = build_payload(
|
||
tm_id=tm_id,
|
||
segment_val=segment_val,
|
||
orders=orders,
|
||
cfg=cfg,
|
||
)
|
||
|
||
# Output lands directly in output_root/<segment>/
|
||
out_dir = output_root / seg_folder
|
||
out_dir.mkdir(parents=True, exist_ok=True)
|
||
out_file = out_dir / f"{xlsx_file.stem}_bid.json"
|
||
|
||
with open(out_file, "w") as f:
|
||
json.dump(payload, f, indent=4)
|
||
|
||
print(f"OK → {out_file.relative_to(output_root)} ({len(orders)} orders)")
|
||
|
||
except Exception as exc:
|
||
print(f"FAILED — {exc}", file=sys.stderr)
|
||
total_errors += 1
|
||
|
||
print("-" * 72)
|
||
print(f"Done. {total_files} file(s) processed, {total_errors} error(s).")
|
||
|
||
|
||
# ─────────────────────────────────────────────────────────────────────────────
|
||
# CLI
|
||
# ─────────────────────────────────────────────────────────────────────────────
|
||
def main():
|
||
parser = argparse.ArgumentParser(
|
||
description="Bulk-generate bid JSON files from Excel bid sheets.",
|
||
formatter_class=argparse.RawDescriptionHelpFormatter,
|
||
epilog="""
|
||
Folder structure expected under --input_root:
|
||
<segment>/ GDAM | DAM | HPDAM
|
||
<order_type>/ Single | Block
|
||
<order_side>/ Buy | Sell
|
||
<TM>/ e.g. C1001 (becomes userId + tmId)
|
||
*.xlsx bid data files
|
||
""",
|
||
)
|
||
parser.add_argument(
|
||
"--input_root",
|
||
default=DEFAULTS["input_root"],
|
||
help="Root folder containing segment/order_type/order_side/TM/file.xlsx tree (default: %(default)s)",
|
||
)
|
||
parser.add_argument(
|
||
"--output_root",
|
||
default=DEFAULTS["output_root"],
|
||
help="Root folder where JSON files are written (same sub-tree) (default: %(default)s)",
|
||
)
|
||
parser.add_argument(
|
||
"--product_code",
|
||
default=DEFAULTS["product_code"],
|
||
help='Product code string, e.g. "IDAS(01)" (default: %(default)s)',
|
||
)
|
||
parser.add_argument(
|
||
"--password",
|
||
default=DEFAULTS["password"],
|
||
help="Password applied to every payload (default: %(default)s)",
|
||
)
|
||
parser.add_argument(
|
||
"--ocf_opted",
|
||
default=DEFAULTS["ocf_opted"],
|
||
help="OCF opted flag Y/N (default: %(default)s)",
|
||
)
|
||
parser.add_argument(
|
||
"--ocf_premium_disc",
|
||
type=int,
|
||
default=DEFAULTS["ocf_premium_disc"],
|
||
help="OCF premium discount integer (default: %(default)s)",
|
||
)
|
||
parser.add_argument(
|
||
"--ocf_qty",
|
||
type=int,
|
||
default=DEFAULTS["ocf_qty"],
|
||
help="OCF quantity integer (default: %(default)s)",
|
||
)
|
||
|
||
args = parser.parse_args()
|
||
process_all(vars(args))
|
||
|
||
|
||
if __name__ == "__main__":
|
||
main()
|