"""記事 sales-table-design-minimum の計算と図(商いの帳場)。 python scripts/figs/sales_table_design.py (fields/sales-management-app/ で実行。外部データなし、標準ライブラリだけ) 架空の卸3社(導入例(イメージ):青葉食品・三浦資材・東和パック)の帳票から、販売管理に要るテーブルを SQLite に作って、 1) テーブル数と列数(PRAGMA table_info で数える) 2) 1か月に増える行数(乱数の種を固定した1か月分の受注から、各表に書く行を数える) を出し、cms/figs/ に図2枚(ER図・月の増加行数)を書く。結果は scripts/figs/out/sales-table-design.json。 置いた前提(すべて架空): 青葉食品(小) 得意先80・商品600 ・締め日1つ(末日)・単価は得意先×商品の表・受注と出荷は1枚の売上伝票 三浦資材(中) 得意先400・商品3,000・締め日3つ(15日・20日・末日)・掛け率+特値・直送あり(仕入を同時に作る)・受注と出荷を分ける 東和パック(大) 得意先2,000・商品8,000・分納・預かり在庫・返品・入金の消込・納入先を得意先の下に持つ 1か月の受注:得意先ごとの月の受注回数(青葉6.0・三浦3.5・東和2.5の平均、ポアソン分布)、1受注の明細行(平均 5・6・5)。 請求書は月1回の締めで、その月に買った得意先ごとに1通。入金は請求書ごとに1件。 """ import json, pathlib, random, sqlite3, platform, math ROOT = pathlib.Path(__file__).resolve().parents[2] FIGS = ROOT / 'cms/figs'; FIGS.mkdir(parents=True, exist_ok=True) OUT = ROOT / 'scripts/figs/out'; OUT.mkdir(parents=True, exist_ok=True) SEED = 20261017 SMALL = { 'customer': 'code name kana zip addr1 addr2 tel closing_day pay_offset pay_day tax_rounding active'.split(), 'product': 'code name unit std_price tax_rate active'.split(), 'price': 'customer_code product_code unit_price valid_from'.split(), 'sales_header': 'no sales_date customer_code subtotal tax8 tax10 invoice_no'.split(), 'sales_line': 'no line product_code qty unit_price amount tax_rate'.split(), 'invoice': 'no customer_code issue_date period_from period_to total8 total10 tax8 tax10 prev_balance total'.split(), 'receipt': 'no receipt_date customer_code amount invoice_no'.split(), } MID = dict(SMALL) del MID['sales_header'], MID['sales_line'] MID['customer'] = 'code name kana zip addr1 addr2 tel closing_group markup_rate tax_rounding active'.split() MID['closing_group'] = 'code closing_day pay_offset pay_day'.split() MID['product'] = 'code name unit std_price cost supplier_code tax_rate active'.split() MID['supplier'] = 'code name kana zip addr1 addr2 tel closing_day active'.split() MID['price'] = 'customer_code product_code special_price valid_from valid_to'.split() # 特値 MID['order_header'] = 'no order_date customer_code delivery_to drop_ship subtotal status'.split() MID['order_line'] = 'no line product_code qty unit_price amount tax_rate shipped_qty'.split() MID['ship_header'] = 'no ship_date order_no customer_code subtotal tax8 tax10 invoice_no'.split() MID['ship_line'] = 'no line order_line product_code qty unit_price amount'.split() MID['purchase_header'] = 'no purchase_date supplier_code ship_no subtotal'.split() MID['purchase_line'] = 'no line product_code qty cost amount'.split() BIG = dict(MID) BIG['delivery_site'] = 'code customer_code name zip addr1 addr2 tel'.split() BIG['warehouse'] = 'code name'.split() BIG['stock'] = 'warehouse_code product_code owner_customer qty'.split() # 預かり在庫は持ち主の列で区別 BIG['return_header'] = 'no return_date customer_code ship_no reason subtotal'.split() BIG['return_line'] = 'no line product_code qty unit_price amount'.split() BIG['invoice_line'] = 'invoice_no line ship_no amount tax_rate'.split() BIG['receipt_alloc'] = 'receipt_no invoice_no amount'.split() BIG['price_history'] = 'customer_code product_code old_price new_price changed_on'.split() def build(schema): db = sqlite3.connect(':memory:') for t, cols in schema.items(): db.execute(f'CREATE TABLE {t} (' + ', '.join(cols) + ')') n = db.execute("select count(*) from sqlite_master where type='table'").fetchone()[0] cols = {t: len(db.execute(f'pragma table_info({t})').fetchall()) for t in schema} return n, cols def poisson(rng, lam): L, k, p = math.exp(-lam), 0, 1.0 while True: p *= rng.random() if p <= L: return k k += 1 def month(customers, per_cust, lines_avg, drop_pct=0.0, partial_pct=0.0, return_pct=0.0): rng = random.Random(SEED + customers) o = l = dn = dl = sn = sl = rn = rl = ordering = 0 for _ in range(customers): k = poisson(rng, per_cust) ordering += 1 if k else 0 for _ in range(k): o += 1 n = poisson(rng, lines_avg - 1) + 1 l += n if rng.random() < drop_pct: dn += 1; dl += n two = rng.random() < partial_pct sn += 2 if two else 1 sl += n + (n // 2 if two else 0) if rng.random() < return_pct: rn += 1; rl += 1 + (1 if rng.random() < .3 else 0) return dict(orders=o, lines=l, ordering=ordering, drop_n=dn, drop_l=dl, ship_n=sn, ship_l=sl, ret_n=rn, ret_l=rl) def rows_small(): m = month(80, 6.0, 5) return m, {'sales_header': m['orders'], 'sales_line': m['lines'], 'invoice': m['ordering'], 'receipt': m['ordering']} def rows_mid(): m = month(400, 3.5, 6, drop_pct=.30) return m, {'order_header': m['orders'], 'order_line': m['lines'], 'ship_header': m['orders'], 'ship_line': m['lines'], 'purchase_header': m['drop_n'], 'purchase_line': m['drop_l'], 'invoice': m['ordering'], 'receipt': m['ordering']} def rows_big(): m = month(2000, 2.5, 5, drop_pct=.10, partial_pct=.10, return_pct=.03) return m, {'order_header': m['orders'], 'order_line': m['lines'], 'ship_header': m['ship_n'], 'ship_line': m['ship_l'], 'purchase_header': m['drop_n'], 'purchase_line': m['drop_l'], 'return_header': m['ret_n'], 'return_line': m['ret_l'], 'invoice': m['ordering'], 'invoice_line': m['ship_n'], 'receipt': m['ordering'], 'receipt_alloc': m['ordering']} res = {} for key, label, schema, fn, cust, prod in [('aoba', '青葉食品(小)', SMALL, rows_small, 80, 600), ('miura', '三浦資材(中)', MID, rows_mid, 400, 3000), ('towa', '東和パック(大)', BIG, rows_big, 2000, 8000)]: nt, cols = build(schema) m, rows = fn() res[key] = dict(label=label, customers=cust, products=prod, tables=nt, columns=sum(cols.values()), cols=cols, month_rows=rows, month_total=sum(rows.values()), month_orders=m['orders'], month_lines=m['lines'], table_names=list(schema)) res['env'] = dict(python=platform.python_version(), sqlite=sqlite3.sqlite_version, os=platform.platform(), seed=SEED) (OUT / 'sales-table-design.json').write_text(json.dumps(res, ensure_ascii=False, indent=1), encoding='utf-8') FONT = "'Trebuchet MS','Yu Gothic','Hiragino Sans','Meiryo',sans-serif" def box(x, y, w, name, cols, hl=False): h = 24 + 15 * len(cols) c = '#86603a' if hl else '#111' s = f"" s += f"{name}" for i, t in enumerate(cols): s += f"{t}" return s, h def er(): W, H = 760, 500 BW = 210 items = { 'product': ('商品 product', ['code(商品コード)', 'name・unit', 'std_price(標準価格)', 'tax_rate(税率)'], 20, 20, False), 'price': ('単価 price', ['customer_code', 'product_code', 'unit_price', 'valid_from(適用開始日)'], 20, 150, False), 'customer': ('得意先 customer', ['code(得意先コード)', 'name・住所・電話', 'closing_day(締め日)', 'pay_offset・pay_day(支払)', 'tax_rounding(端数処理)'], 275, 20, True), 'sales_header': ('売上ヘッダ sales_header', ['no(伝票番号)', 'sales_date', 'customer_code', 'subtotal・tax8・tax10', 'invoice_no'], 530, 20, False), 'sales_line': ('売上明細 sales_line', ['no・line', 'product_code', 'qty・unit_price', 'amount・tax_rate'], 530, 170, False), 'invoice': ('請求 invoice', ['no(請求書番号)', 'customer_code', 'period_from・period_to', 'total8・total10', 'tax8・tax10', 'prev_balance・total'], 275, 290, False), 'receipt': ('入金 receipt', ['no', 'receipt_date', 'customer_code', 'amount・invoice_no'], 20, 330, False), } pos, parts = {}, [] for k, (name, cols, x, y, hl) in items.items(): s, h = box(x, y, BW, name, cols, hl) parts.append(s) pos[k] = (x, y, BW, h) L = [] def line(p, q, lab, dx=0, dy=-5): L.append(f"") L.append(f"{lab}") def R(k): x, y, w, h = pos[k]; return (x + w, y + h // 2) def Lf(k): x, y, w, h = pos[k]; return (x, y + h // 2) def T(k, dx=0): x, y, w, h = pos[k]; return (x + w // 2 + dx, y) def B(k, dx=0): x, y, w, h = pos[k]; return (x + w // 2 + dx, y + h) line(R('customer'), Lf('sales_header'), '1 対 多') line(B('product'), T('price'), '1 対 多', 28, 4) line(B('sales_header'), T('sales_line'), '1 対 多', 28, 4) line(B('customer'), T('invoice'), '1 対 多', 28, 4) line(Lf('invoice'), (pos['receipt'][0] + BW, pos['receipt'][1] + 45), '1 対 多', 0, -6) line((pos['customer'][0], pos['customer'][1] + 60), (pos['price'][0] + BW, pos['price'][1] + 30), '1 対 多', 0, -6) line(R('invoice'), (pos['sales_line'][0] + 40, pos['sales_line'][1] + pos['sales_line'][3]), 'invoice_no で束ねる', -6, 14) return (f"" f"青葉食品(架空・小)の販売管理に要る7つのテーブルの関係図。得意先と商品が土台で、単価、売上ヘッダ、売上明細、請求、入金がつながる。締めグループは得意先の締め日の列で持つ" f"" + ''.join(L) + ''.join(parts) + f"架空の導入例(イメージ)。列は主なものだけで、全列は7表で{res['aoba']['columns']}列。商いの帳場の設計") def bars(): W, H = 760, 330 d = [(res[k]['label'], res[k]['month_total'], res[k]['tables']) for k in ('aoba', 'miura', 'towa')] mx = max(v for _, v, _ in d) s = '' for i, (lab, v, t) in enumerate(d): y = 56 + i * 76 w = int(v / mx * 430) s += f"{lab}テーブル{t}" s += f"" s += f"{v:,}行" return (f"" f"架空の卸3社が1か月に全テーブルへ書き足す行数。青葉食品{d[0][1]:,}行、三浦資材{d[1][1]:,}行、東和パック{d[2][1]:,}行" f"1か月に増える行数(全テーブルの合計、計算値){s}" f"架空の導入例(イメージ)。乱数の種{SEED}、Python {platform.python_version()}・SQLite {sqlite3.sqlite_version}。商いの帳場の計算") (FIGS / 'sales-table-design-er.svg').write_text(er(), encoding='utf-8') (FIGS / 'sales-table-design-rows.svg').write_text(bars(), encoding='utf-8') print(json.dumps({k: {x: res[k][x] for x in ('tables', 'columns', 'month_rows', 'month_total', 'month_orders', 'month_lines')} for k in ('aoba', 'miura', 'towa')}, ensure_ascii=False))