""" PCB Price Calculator engine. Ported from the supplied get_price.php, reading pricing tables *directly* from REMOTE_CALCULATOR-38.xls at import time (no intermediate JSON / database) using xlrd. If the spreadsheet is edited, just restart the app -- there is nothing to re-generate. Tables mirrored from the PHP script's database schema: price_master -> layer, qty, min_size ('<25','<50','<75','<100'), basic_price, setup_cost price_master_default -> layer, qty (>=50), basic_price, setup_cost ("default_status=1" fallback row) price_master_wo_qty -> layer, more_than_sqmtr, basic_price, setup_cost surface_finish_wo_qty -> layer, surface_fin, amt_per_sqmtr courier_charge -> layer, amt_per_sqmtr NOTE on fidelity: the rate-card sheet does not carry a per-row "courier_charge" on price_master, nor a quantity-based "surface_finish" add-amount table (those exist only in the live admin database, not in this spreadsheet). Both default to 0 for that specific column; the courier/surface-finish amounts actually shown are the area-based ones computed from the per-sq-meter tables above, same as get_price.php. """ import math import os import re import xlrd XLS_PATH = os.path.join(os.path.dirname(__file__), "REMOTE_CALCULATOR-38.xls") # (layer_code, first_row, last_row) for each block in the sheet LAYER_BLOCKS = [ ("1", 4, 38), ("2", 41, 75), ("4", 78, 109), ("6", 112, 140), ("8", 143, 171), ("10", 174, 202), ("MC", 205, 233), ] SURF_NAMES = ["Tin-Lead", "Lead Free", "ENIG", "Immersion Tin"] SIZE_LABEL_TO_NUM = {"<25": 25, "<50": 50, "<75": 75, "<100": 100} # Solder-mask colour: 5 fixed slots in the sheet (by row position, not by a # named column) -- Green is always the first/free slot, the other four # follow in this order. COLOURS = ["Green", "Red", "Blue", "Black", "White"] # Courier is billed by weight, not raw area: PCB weight per m² by board # thickness (from the supplied WEIGHT.pdf). 0.2mm/0.4mm aren't in that # table, so they fall back to the nearest documented thickness (see # _weight_per_sqm below) rather than being left unpriced. WEIGHT_TABLE_KG_PER_SQM = { 0.6: 1.4, 0.8: 2.0, 1.0: 2.2, 1.2: 2.64, 1.6: 3.5, 2.0: 4.4, 2.4: 5.28, 3.2: 7.0, } def _weight_per_sqm(thickness): if thickness in WEIGHT_TABLE_KG_PER_SQM: return WEIGHT_TABLE_KG_PER_SQM[thickness] if thickness is None: return None nearest = min(WEIGHT_TABLE_KG_PER_SQM, key=lambda t: abs(t - thickness)) return WEIGHT_TABLE_KG_PER_SQM[nearest] def _clean_money(v): if v is None: return None if isinstance(v, (int, float)): return float(v) s = str(v).replace(",", "").replace("/-", "").replace("PER", "").strip() if s in ("", "NOT", "NOT ", " "): return None try: return float(s) except ValueError: return None def _load_from_xls(path): wb = xlrd.open_workbook(path) sh = wb.sheet_by_index(0) price_master = [] price_master_default = [] price_master_wo_qty = [] surface_finish_wo_qty = [] courier_charge = [] courier_floor = [] surface_finish_floor = [] material_thickness = [] copper_thickness = [] working_days = [] colour_finish = [] layer_onoff = {} for layer, start, end in LAYER_BLOCKS: qty15_seen = 0 colour = [] for r in range(start, end + 1): row = sh.row_values(r) col1 = row[1] if isinstance(col1, str) and col1.strip(): # first non-blank ON/OFF cell in the block wins (the sheet # repeats the same flag on every row of a layer's block) layer_onoff.setdefault(layer, col1.strip().upper()) col3, col6, col8, col10, col12 = row[3], row[6], row[8], row[10], row[12] if isinstance(col3, str) and col3.strip().startswith("MORE THAN"): m = re.search(r"(\d+)", str(col6)) if m: price_master_wo_qty.append({ "layer": layer, "more_than_sqmtr": int(m.group(1)), "basic_price": _clean_money(col10), "setup_cost": _clean_money(col12), }) continue if isinstance(col8, str) and col8.strip().startswith("<"): qty = col6 size = col8.strip().replace(" ", "") price_master.append({ "layer": layer, "qty": qty, "min_size": size, "basic_price": _clean_money(col10), "setup_cost": _clean_money(col12), }) if qty == 15.0: qty15_seen += 1 if qty15_seen == 1: # columns are Tin-Lead, Lead Free, ENIG, Immersion Tin # in direct order -- Tin-Lead's cell is blank (it's # the free/default finish, no per-sqm charge) vals = [row[25], row[26], row[27], row[28]] for name, v in zip(SURF_NAMES, vals): cv = _clean_money(v) if cv is not None: surface_finish_wo_qty.append({"layer": layer, "surface_fin": name, "amt_per_sqmtr": cv}) cc = _clean_money(row[30] if len(row) > 30 else "") if cc is not None: courier_charge.append({"layer": layer, "amt_per_sqmtr": cc}) if row[14] not in ("", None): onoff = str(row[15]).strip().upper() if row[15] not in ("", None) else "OFF" material_thickness.append({"layer": layer, "qty": qty, "thickness": row[14], "add_amt": _clean_money(row[16]), "onoff": onoff}) if row[18] not in ("", None): onoff = str(row[19]).strip().upper() if row[19] not in ("", None) else "OFF" copper_thickness.append({"layer": layer, "qty": qty, "microns": row[18], "add_amt": _clean_money(row[20]), "onoff": onoff}) if len(row) > 32 and row[32] not in ("", None, " "): working_days.append({"layer": layer, "qty": qty, "days": row[32], "add_pct": _clean_money(row[34])}) # solder-mask colour: 5 fixed slots (Green/Red/Blue/Black/White) sit # in the first 5 rows of every block, keyed by position not value if qty15_seen == 0 and len(colour) < len(COLOURS): whole_qty = _clean_money(row[22]) if row[22] not in ("", None) else None inc = _clean_money(row[23]) if row[23] not in ("", None) else None if whole_qty is not None: colour.append({"layer": layer, "name": COLOURS[len(colour)], "whole_qty": whole_qty, "inc_amt": inc or 0}) continue if isinstance(col6, (int, float)) and col6 in (50.0, 75.0, 100.0, 250.0, 500.0, 1000.0, 2000.0, 3000.0, 5000.0) and col8 != "": price_master_default.append({ "layer": layer, "qty": col6, "basic_price": _clean_money(col10), "setup_cost": _clean_money(col12), }) continue colour_finish.extend(colour) # "NOT LESS THAN" floors (courier + the 3 paid surface finishes) # all sit 19 rows into the block, same row, different columns. floor_row = start + 19 if floor_row <= end: frow = sh.row_values(floor_row) floor_val = _clean_money(frow[30]) if floor_val is not None: courier_floor.append({"layer": layer, "amt": floor_val}) # Tin-Lead has no floor (it's free); Lead Free/ENIG/Immersion Tin do for name, col in zip(SURF_NAMES[1:], [26, 27, 28]): fv = _clean_money(frow[col]) if fv is not None: surface_finish_floor.append({"layer": layer, "surface_fin": name, "amt": fv}) return { "price_master": price_master, "price_master_default": price_master_default, "price_master_wo_qty": price_master_wo_qty, "surface_finish_wo_qty": surface_finish_wo_qty, "courier_charge": courier_charge, "courier_floor": courier_floor, "surface_finish_floor": surface_finish_floor, "material_thickness": material_thickness, "copper_thickness": copper_thickness, "working_days": working_days, "colour_finish": colour_finish, "layer_onoff": layer_onoff, } DATA = _load_from_xls(XLS_PATH) # ---- Build fast lookup structures -------------------------------------- PRICE_MASTER = {} for row in DATA["price_master"]: key = (str(row["layer"]), float(row["qty"]), row["min_size"]) PRICE_MASTER[key] = row PRICE_MASTER_BY_LQ = {} for row in DATA["price_master"]: key = (str(row["layer"]), float(row["qty"])) PRICE_MASTER_BY_LQ.setdefault(key, []).append(row) PRICE_MASTER_DEFAULT = {} for row in DATA["price_master_default"]: key = (str(row["layer"]), float(row["qty"])) PRICE_MASTER_DEFAULT[key] = row ALL_DEFAULT_QTYS = sorted({float(r["qty"]) for r in DATA["price_master_default"]}) PRICE_MASTER_WO_QTY = {} for row in DATA["price_master_wo_qty"]: key = (str(row["layer"]), row["more_than_sqmtr"]) PRICE_MASTER_WO_QTY[key] = row SURFACE_WO_QTY = {} for row in DATA["surface_finish_wo_qty"]: SURFACE_WO_QTY[(str(row["layer"]), row["surface_fin"])] = row["amt_per_sqmtr"] COURIER_CHARGE = {str(row["layer"]): row["amt_per_sqmtr"] for row in DATA["courier_charge"]} COURIER_FLOOR = {str(row["layer"]): row["amt"] for row in DATA["courier_floor"]} SURFACE_FLOOR = {(str(row["layer"]), row["surface_fin"]): row["amt"] for row in DATA["surface_finish_floor"]} ALL_QTYS = sorted({float(r["qty"]) for r in DATA["price_master"]} | set(ALL_DEFAULT_QTYS)) # ---- Layer ON/OFF ------------------------------------------------------- # The sheet has an ON/OFF flag per layer block (col B). Only layers # flagged ON are offered in the calculator -- exactly mirrors how the # admin panel hides a layer count without deleting its price rows. LAYER_ONOFF = DATA["layer_onoff"] LAYERS = sorted( (l for l in {str(r["layer"]) for r in DATA["price_master"]} if LAYER_ONOFF.get(l) == "ON"), key=lambda x: (len(x), x), ) SURFACE_FINISHES = SURF_NAMES # Tin-Lead included even though it's free (₹0) MTR_TIERS = [10, 20, 30, 50, 80, 100] # ---- Secondary option tables (thickness / copper / working days) ------ # These rows repeat qty after qty in the sheet with identical values, so # in practice they are per-LAYER option lists, not truly qty-dependent — # collapse them accordingly. Only ON-flagged rows are kept. def _layer_options_onoff(rows, value_key): by_layer = {} for r in rows: if r.get("onoff") != "ON": continue layer = str(r["layer"]) by_layer.setdefault(layer, {})[r[value_key]] = r["add_amt"] return {l: sorted(v.items()) for l, v in by_layer.items()} THICKNESS_OPTIONS = _layer_options_onoff(DATA["material_thickness"], "thickness") COPPER_OPTIONS = _layer_options_onoff(DATA["copper_thickness"], "microns") _wd_raw = {} for r in DATA["working_days"]: layer = str(r["layer"]) _wd_raw.setdefault(layer, {})[int(r["days"])] = r["add_pct"] WORKING_DAYS_OPTIONS = {l: sorted(v.items(), key=lambda x: -x[0]) for l, v in _wd_raw.items()} # Solder-mask colour: real per-colour data from the sheet -- Green is free # and always available; the other four each have their own INC (rupee) # add-on and only become available once qty reaches their "whole qty" # threshold (typically 500 pcs), matching the sheet's NOT LESS THAN rule. COLOUR_OPTIONS = {} for r in DATA["colour_finish"]: COLOUR_OPTIONS.setdefault(str(r["layer"]), {})[r["name"]] = { "whole_qty": r["whole_qty"], "inc_amt": r["inc_amt"] } def get_price(size_sqmm, qty, layer, surface): """Direct port of get_price.php's bucketing/fallback logic.""" layer = str(layer) qty = float(qty) surface = surface.replace("_", " ") size = (size_sqmm / 100.0) if size_sqmm > 0 else 0 # -> sq cm, per PHP comment surface_amt = 0.0 courier_charge = 0.0 result = None if size > 0: csize = size mflag = False mtr_size = size / 10000.0 # sq metre, per single piece msize = None bucket = None if size < 25: bucket = "<25"; csize = 25 elif size < 50: bucket = "<50"; csize = 50 elif size < 75: bucket = "<75"; csize = 75 elif size < 100 or size > 100: bucket = "<100"; csize = 100 elif mtr_size > 100: msize = 100; mflag = True elif mtr_size > 80: msize = 80; mflag = True elif mtr_size > 50: msize = 50; mflag = True elif mtr_size > 30: msize = 30; mflag = True elif mtr_size > 20: msize = 20; mflag = True elif mtr_size > 10: msize = 10; mflag = True else: bucket = str(size) # exact-match fallback (size == 100 edge case) # NOTE: get_price.php only applies the surface-finish surcharge once # total area exceeds 1 m². Here it scales continuously with area # instead, so small orders don't just show "Free" across the board. # (Courier is no longer computed here -- it's weight-based now, see # compute_quote(), since the weight depends on the chosen PCB # thickness which this function doesn't know about.) mtr_size_total = mtr_size * qty if (layer, surface) in SURFACE_WO_QTY: surface_amt = SURFACE_WO_QTY[(layer, surface)] * mtr_size_total floor = SURFACE_FLOOR.get((layer, surface)) if floor is not None and surface_amt > 0: surface_amt = max(surface_amt, floor) if not mflag: result = PRICE_MASTER.get((layer, qty, bucket)) if not result: candidates = [r for r in PRICE_MASTER_BY_LQ.get((layer, qty), []) if SIZE_LABEL_TO_NUM.get(r["min_size"], 0) < csize] if candidates: result = max(candidates, key=lambda r: SIZE_LABEL_TO_NUM.get(r["min_size"], 0)) else: result = PRICE_MASTER_WO_QTY.get((layer, msize)) if not result: result = PRICE_MASTER_DEFAULT.get((layer, qty)) if not result and ALL_DEFAULT_QTYS: snapped = min(ALL_DEFAULT_QTYS, key=lambda q: abs(q - qty)) result = PRICE_MASTER_DEFAULT.get((layer, snapped)) if not result: return None basic_price = result.get("basic_price") or 0 setup_cost = result.get("setup_cost") or 0 row_courier = result.get("courier_charge") or 0 # not present in this sheet -> 0 final_courier = courier_charge if courier_charge > 0 else row_courier return { "min_size": result.get("min_size") or result.get("more_than_sqmtr"), "basic_price": basic_price, "setup_cost": setup_cost, "courier_charge": final_courier, "surface_amt": surface_amt, } def compute_quote(size_sqmm, qty, layer, surface, thickness=None, copper=None, colour="Green", working_days=None): layer = str(layer) qty = float(qty) price = get_price(size_sqmm, qty, layer, surface) if not price: return None thickness_amt = dict(THICKNESS_OPTIONS.get(layer, [])).get(thickness, 0) if thickness is not None else 0 copper_amt = dict(COPPER_OPTIONS.get(layer, [])).get(copper, 0) if copper is not None else 0 wd_options = dict(WORKING_DAYS_OPTIONS.get(layer, [])) if working_days is None: working_days = max(wd_options.keys()) if wd_options else None rush_pct = wd_options.get(working_days, 0) colour = colour or "Green" colour_info = COLOUR_OPTIONS.get(layer, {}).get(colour) # No quantity condition for choosing a colour -- any colour is # selectable at any qty; only its rupee INC add-on applies. colour_amt = colour_info["inc_amt"] if colour_info else 0.0 # --- Courier: billed by shipment weight, not raw area --------------- # weight (kg) = thickness's kg/m² (WEIGHT.pdf) x total order area (m²), # rounded UP to the next whole kg, x the sheet's per-kg courier rate, # then floored at the sheet's "NOT LESS THAN" minimum. total_area_sqm = (size_sqmm / 1_000_000.0) * qty weight_per_sqm = _weight_per_sqm(thickness) total_weight_kg = (weight_per_sqm or 0) * total_area_sqm rounded_weight_kg = math.ceil(total_weight_kg) if total_weight_kg > 0 else 0 raw_courier = COURIER_CHARGE.get(layer, 0) * rounded_weight_kg courier_charge = max(raw_courier, COURIER_FLOOR.get(layer, 0)) if raw_courier > 0 else 0.0 # basic_price is a rate per sq cm (the "@ RUPEES" column sits next to # "MINIMUM SQUARE CENTIMETER"), so the per-piece board cost scales with # actual board area, not a flat per-piece amount: e.g. a 10cm x 10cm # board at qty 5 = 10 x 10 x 5 x basic_price. area_sqcm = size_sqmm / 100.0 board_unit_price = price["basic_price"] * area_sqcm # thickness/copper are flat rupee add-ons per piece; only working-days # (rush) is a percentage surcharge on the base price unit_price = (board_unit_price + thickness_amt + copper_amt) * (1 + rush_pct / 100.0) subtotal = (unit_price * qty) + price["setup_cost"] + price["surface_amt"] + \ courier_charge + (colour_amt * qty) tax = subtotal * 0.18 total = subtotal + tax return { **price, "qty": qty, "unit_price": round(unit_price, 4), "thickness_amt": thickness_amt, "copper_amt": copper_amt, "rush_pct": rush_pct, "colour_amt": colour_amt, "working_days": working_days, "total_weight_kg": round(total_weight_kg, 3), "rounded_weight_kg": rounded_weight_kg, "courier_charge": round(courier_charge, 2), "subtotal": round(subtotal, 2), "tax": round(tax, 2), "grand_total": round(total, 2), }