"""記事「分納と受注残はどう管理する?伝票の分け方2通り」(cms/articles/partial-shipment-order-backlog.md)の実験と図。すべて架空のデータ。 架空の卸の100受注(分納あり、乱数の種を固定)を、2つのテーブル設計で SQLite(メモリ上)に流し、次を数える。 設計1「1伝票」: 表 slip_line の1つに受注・出荷・売上を状態つきの行で持つ。分納のたびに、受注残の行を 「出荷した分の行(状態=売上)」と「残りの行(状態=受注残)」に分ける。残りの行は1明細に1つだけにする。 受注残 = 状態が「受注残」の行の数量の合計。分けた行は親の明細番号(parent)で元の明細につなぐ。 設計2「分ける」: 表 order_line(受注明細)と表 shipment(出荷=売上の行)の2つ。受注明細は書き換えない。 受注残 = 受注数 − 出荷数の累計。出荷の取り消しはマイナスの出荷行を1行足す(赤伝)。 数えるもの: 1. 受注残の正しさ:出来事の一覧から Python で出した正解と、両設計の SQL の答えを全明細で突き合わせる 2. 行数:最後の時点の表の行数と、1枚の受注伝票に載る明細行の最大(B社の公開仕様の1伝票100行と比べる) 3. 手数:出荷と取り消しで書き込んだ行の回数(insert・update・delete を1回ずつ数える)と、取り消しの記録が残る件数 条件:受注の明細数は 70% が1〜10行・25% が11〜30行・5% が40〜70行。明細ごとの出荷の回数は 1回55%・2回25%・3回12%・4回8%。 締めの時点で明細の30%は最後の1回分がまだ出ていない(受注残)。出荷の5%をあとで取り消す(取り消した数量は受注残に戻る)。 設計1の取り消し:残りの行があれば数量を足して出荷の行を消す(2回)、なければ出荷の行の状態を受注残に戻す(1回)。 使い方: python scripts/figs/partial_shipment_backlog.py (fields/sales-management-app/ で実行。Python 標準ライブラリだけ) 出力: public/img/articles/partial-shipment-order-backlog-compare.svg(図2。frontmatter の image とアイキャッチ)、 scripts/figs/.out/psb_fig1_tables.svg・psb_fig3_slip_lines.svg(記事に貼るインラインの SVG)、数値は標準出力(JSON)。 """ import json, pathlib, platform, random, sqlite3, sys ROOT = pathlib.Path(__file__).resolve().parents[2] OUT_SVG = ROOT / 'public/img/articles/partial-shipment-order-backlog-compare.svg' OUTDIR = ROOT / 'scripts/figs/.out' SEED = 20261016 N_ORDERS = 100 SLIP_LIMIT = 100 # B社の公開仕様:1伝票の明細は100行(cms/_facts.md 1章) rng = random.Random(SEED) def n_lines(): r = rng.random() if r < 0.70: return rng.randint(1, 10) if r < 0.95: return rng.randint(11, 30) return rng.randint(40, 70) def n_ships(): r = rng.random() return 1 if r < 0.55 else 2 if r < 0.80 else 3 if r < 0.92 else 4 # ---- 架空の受注と出来事 ---- orders = [] # (order_no, [(line_no, product, qty)]) events = [] # ('ship', ship_id, order, line, qty, day) / ('cancel', ship_id, day) ship_id = 0 for o in range(1, N_ORDERS + 1): lines = [] for ln in range(1, n_lines() + 1): k = n_ships() qty = rng.randint(k * 2, 60) if k > 1 else rng.randint(1, 60) lines.append((ln, rng.randrange(1, 2001), qty)) # 数量を k 回に分ける cuts = sorted(rng.sample(range(1, qty), k - 1)) if k > 1 else [] parts = [b - a for a, b in zip([0] + cuts, cuts + [qty])] if rng.random() < 0.30: # 最後の1回分はまだ出ていない parts = parts[:-1] if k > 1 else [] if k == 1: # 1回で出す予定の明細は、半分だけ出たことにして残りを受注残に parts = [qty // 2] if qty >= 2 else [] day = rng.randint(1, 5) for p in parts: ship_id += 1 events.append(('ship', ship_id, o, ln, p, day)) day += rng.randint(2, 7) orders.append((o, lines)) ships = [e for e in events if e[0] == 'ship'] cancel_ids = set(rng.sample([e[1] for e in ships], round(len(ships) * 0.05))) for e in ships: if e[1] in cancel_ids: events.append(('cancel', e[1], e[5] + rng.randint(1, 3))) events.sort(key=lambda e: (e[-1], 0 if e[0] == 'ship' else 1, e[1])) ship_of = {e[1]: e for e in ships} # 正解(出来事から直接) truth = {} for o, lines in orders: for ln, _, q in lines: truth[(o, ln)] = q for e in events: if e[0] == 'ship': truth[(e[2], e[3])] -= e[4] else: s = ship_of[e[1]]; truth[(s[2], s[3])] += s[4] # ---- 設計1:1伝票 ---- d1 = sqlite3.connect(':memory:') d1.executescript(''' create table slip_line(id integer primary key, order_no int, line_no int, parent int, product int, qty int, status text, ship_id int, ship_day int); ''') w1 = {'insert': 0, 'update': 0, 'delete': 0} w1_ship = w1_cancel = 0 for o, lines in orders: for ln, p, q in lines: d1.execute('insert into slip_line(order_no,line_no,parent,product,qty,status) values(?,?,?,?,?,?)', (o, ln, ln, p, q, '受注残')) init_rows = sum(len(l) for _, l in orders) cancel_ops1 = [] for e in events: if e[0] == 'ship': _, sid, o, ln, q, day = e rid, rest = d1.execute("select id, qty from slip_line where order_no=? and parent=? and status='受注残'", (o, ln)).fetchone() if q == rest: # 残りを全部出す:状態を売上に d1.execute("update slip_line set status='売上', ship_id=?, ship_day=? where id=?", (sid, day, rid)); w1['update'] += 1; w1_ship += 1 else: # 一部を出す:残りの行を減らし、出した分の行を足す d1.execute('update slip_line set qty=qty-? where id=?', (q, rid)); w1['update'] += 1 nxt = d1.execute('select max(line_no)+1 from slip_line where order_no=?', (o,)).fetchone()[0] d1.execute("insert into slip_line(order_no,line_no,parent,product,qty,status,ship_id,ship_day) select order_no,?,parent,product,?,'売上',?,? from slip_line where id=?", (nxt, q, sid, day, rid)); w1['insert'] += 1; w1_ship += 2 else: sid = e[1] rid, o, par, q = d1.execute("select id, order_no, parent, qty from slip_line where ship_id=?", (sid,)).fetchone() rest = d1.execute("select id from slip_line where order_no=? and parent=? and status='受注残'", (o, par)).fetchone() if rest: d1.execute('update slip_line set qty=qty+? where id=?', (q, rest[0])); d1.execute('delete from slip_line where id=?', (rid,)) w1['update'] += 1; w1['delete'] += 1; cancel_ops1.append(2) else: d1.execute("update slip_line set status='受注残', ship_id=null, ship_day=null where id=?", (rid,)); w1['update'] += 1; cancel_ops1.append(1) BACKLOG1 = "select order_no, parent, sum(qty) from slip_line where status='受注残' group by order_no, parent" got1 = {(o, l): q for o, l, q in d1.execute(BACKLOG1)} mis1 = sum(1 for k, v in truth.items() if got1.get(k, 0) != v) rows1 = d1.execute('select count(*) from slip_line').fetchone()[0] slip1 = [r[0] for r in d1.execute('select count(*) from slip_line group by order_no')] # 設計1で取り消しの記録が残るか:取り消した出荷の ship_id が表に残っている件数 kept1 = d1.execute(f"select count(*) from slip_line where ship_id in ({','.join(map(str, cancel_ids))})").fetchone()[0] # ---- 設計2:分ける ---- d2 = sqlite3.connect(':memory:') d2.executescript(''' create table order_line(order_no int, line_no int, product int, qty int, primary key(order_no, line_no)); create table shipment(id integer primary key, ship_id int, order_no int, line_no int, qty int, day int, cancel_of int); ''') w2 = {'insert': 0, 'update': 0, 'delete': 0} for o, lines in orders: d2.executemany('insert into order_line values(?,?,?,?)', [(o, ln, p, q) for ln, p, q in lines]) for e in events: if e[0] == 'ship': _, sid, o, ln, q, day = e d2.execute('insert into shipment(ship_id,order_no,line_no,qty,day) values(?,?,?,?,?)', (sid, o, ln, q, day)); w2['insert'] += 1 else: sid, day = e[1], e[2] d2.execute('insert into shipment(ship_id,order_no,line_no,qty,day,cancel_of) select null,order_no,line_no,-qty,?,ship_id from shipment where ship_id=?', (day, sid)); w2['insert'] += 1 BACKLOG2 = '''select o.order_no, o.line_no, o.qty - coalesce(sum(s.qty), 0) as backlog from order_line o left join shipment s on s.order_no = o.order_no and s.line_no = o.line_no group by o.order_no, o.line_no''' got2 = {(o, l): q for o, l, q in d2.execute(BACKLOG2)} mis2 = sum(1 for k, v in truth.items() if got2.get(k, 0) != v) rows2 = {'order_line': d2.execute('select count(*) from order_line').fetchone()[0], 'shipment': d2.execute('select count(*) from shipment').fetchone()[0]} slip2 = [r[0] for r in d2.execute('select count(*) from order_line group by order_no')] kept2 = d2.execute('select count(*) from shipment where cancel_of is not null').fetchone()[0] n_ship = len(ships); n_cancel = len(cancel_ids) partial_lines = sum(1 for v in truth.values() if v > 0) total_lines = len(truth) multi = {} for e in ships: multi[(e[2], e[3])] = multi.get((e[2], e[3]), 0) + 1 res = { 'seed': SEED, 'python': sys.version.split()[0], 'sqlite': sqlite3.sqlite_version, 'os': platform.platform(), 'orders': N_ORDERS, 'orderLines': total_lines, 'shipments': n_ship, 'cancels': n_cancel, 'linesShippedTwiceOrMore': sum(1 for v in multi.values() if v >= 2), 'linesWithBacklog': partial_lines, 'backlogQty': sum(truth.values()), 'design1': {'rows': rows1, 'initRows': init_rows, 'writes': w1, 'writesTotal': sum(w1.values()), 'shipWrites': w1_ship, 'cancelWrites': sum(cancel_ops1), 'cancel2': cancel_ops1.count(2), 'cancel1': cancel_ops1.count(1), 'mismatch': mis1, 'maxSlipLines': max(slip1), 'slipsOverLimit': sum(1 for x in slip1 if x > SLIP_LIMIT), 'cancelKept': kept1}, 'design2': {'rows': rows2, 'rowsTotal': sum(rows2.values()), 'writes': w2, 'writesTotal': sum(w2.values()), 'mismatch': mis2, 'maxSlipLines': max(slip2), 'slipsOverLimit': sum(1 for x in slip2 if x > SLIP_LIMIT), 'cancelKept': kept2}, 'slipLines1': sorted(slip1, reverse=True)[:10], 'slipLines2': sorted(slip2, reverse=True)[:10], } # ---------- 図(テーマ:地は白、面は #678b2c、文字は #557323、墨 #111、灰 #6b6b6b) ---------- G, GT, INK, MUTE, RULE, PALE, GRAYBAR = '#678b2c', '#557323', '#111111', '#6b6b6b', '#dcdcdc', '#eef3e6', '#c9c6bf' FONT = "'Hiragino Sans','Yu Gothic','Meiryo',sans-serif" CAP = f'商いの帳場の実験(架空の100受注・明細{total_lines:,}行・出荷{n_ship:,}回・取り消し{n_cancel}回、乱数の種 {SEED}、SQLite {sqlite3.sqlite_version})' def t(x, y, s, size=13, fill=INK, anchor='start', w=400): return f"{s}" def head(W, H, tid, title, inline=True): st = " style='max-width:100%;height:auto'" if inline else '' return [f"", f"{title}", f""] def row(x, y, w, cells, fill='#fff', bold=False, col=INK): s = [f""] cx = x for txt, cw in cells: s.append(t(cx + 8, y + 17, txt, 12, col, w=700 if bold else 400)); cx += cw return ''.join(s) def fig1(): """1つの明細(数量50)を 20 → 15 と分けて出し、2回目を取り消したときの表の中身""" W, H = 720, 330 s = head(W, H, 'psb-tables', '1つの受注明細(数量50)を20と15に分けて出荷し、2回目を取り消したときの表の中身。1伝票の設計は受注残30と売上20の2行が残り、取り消した出荷の記録は消える。分ける設計は受注明細1行と出荷3行(+20、+15、−15)が残り、受注残は50−20=30') s.append(t(20, 26, '例:数量50の明細を「20出荷 → 15出荷 → 2回目を取り消し」と動かした後の表', 14, INK, w=700)) x = 20 s.append(t(x, 58, '設計1 1伝票(表は slip_line の1つ)', 13, GT, w=700)) s.append(row(x, 68, 320, [('行', 40), ('親', 40), ('数量', 60), ('状態', 80), ('出荷', 100)], PALE, True)) s.append(row(x, 92, 320, [('1', 40), ('1', 40), ('30', 60), ('受注残', 80), ('—', 100)])) s.append(row(x, 116, 320, [('2', 40), ('1', 40), ('20', 60), ('売上', 80), ('1回目', 100)])) s.append(f"") s.append(t(x + 8, 157, '(15の売上の行は取り消しで消えた)', 12, MUTE)) s.append(t(x, 192, '書き込み:出荷 2+2回、取り消し 2回 = 6回', 12.5, INK)) s.append(t(x, 212, '受注残 = 状態が受注残の行の合計 = 30', 12.5, INK, w=700)) s.append(t(x, 232, '取り消した出荷の記録:残らない', 12.5, INK)) x = 380 s.append(t(x, 58, '設計2 分ける(order_line と shipment)', 13, GT, w=700)) s.append(row(x, 68, 320, [('受注明細', 90), ('数量', 60), ('', 170)], PALE, True)) s.append(row(x, 92, 320, [('1', 90), ('50', 60), ('(書き換えない)', 170)])) s.append(row(x, 124, 320, [('出荷', 90), ('明細', 60), ('数量', 70), ('取り消し元', 100)], PALE, True)) s.append(row(x, 148, 320, [('1回目', 90), ('1', 60), ('+20', 70), ('—', 100)])) s.append(row(x, 172, 320, [('2回目', 90), ('1', 60), ('+15', 70), ('—', 100)])) s.append(row(x, 196, 320, [('赤', 90), ('1', 60), ('−15', 70), ('2回目', 100)])) s.append(t(x, 240, '書き込み:出荷 1+1回、取り消し 1回 = 3回', 12.5, INK)) s.append(t(x, 260, '受注残 = 50 −(20+15−15)= 30', 12.5, INK, w=700)) s.append(t(x, 280, '取り消した出荷の記録:残る(赤の行)', 12.5, INK)) s.append(t(20, 318, '商いの帳場の作図。どちらの設計も受注残は30で同じ。違うのは書き込みの回数と、取り消しの記録が残るか', 11, MUTE)) s.append('') return ''.join(s) def fig2(): """3つの物差しで2設計を比べる(図2。画像ファイル)""" W, H = 760, 400 title = (f"分納のある架空の100受注で2つの設計を比べた横棒グラフ。受注残の誤りはどちらも0件。書き込みの回数は1伝票{res['design1']['writesTotal']:,}回、分ける{res['design2']['writesTotal']:,}回。" f"表の行数は1伝票{rows1:,}行、分ける{sum(rows2.values()):,}行。1枚の伝票の最大の明細行は1伝票{max(slip1)}行、分ける{max(slip2)}行") s = head(W, H, 'psb-compare', title, inline=False) s.append(t(24, 30, '分納のある架空の100受注で比べた2つの設計(受注残の誤りはどちらも0件)', 15, INK, w=700)) panels = [ ('書き込みの回数(出荷+取り消し)', res['design1']['writesTotal'], res['design2']['writesTotal'], 3000, '回', None), ('表の行数(最後の時点)', rows1, sum(rows2.values()), 3000, '行', None), ('1枚の伝票の明細行(最大)', max(slip1), max(slip2), 150, '行', SLIP_LIMIT), ] x0, x1 = 250, 640 for i, (label, a, b, mx, unit, lim) in enumerate(panels): y = 62 + i * 100 s.append(t(24, y + 10, label, 13.5, INK, w=700)) for j, (name, v, col) in enumerate([('設計1 1伝票', a, GRAYBAR), ('設計2 分ける', b, G)]): yy = y + 22 + j * 30 wv = (x1 - x0) * v / mx s.append(t(x0 - 12, yy + 16, name, 12.5, '#444444', 'end')) s.append(f"") s.append(t(round(x0 + wv + 8), yy + 16, f'{v:,}{unit}', 12.5, GT if j else INK, w=700)) if lim: lx = x0 + (x1 - x0) * lim / mx s.append(f"") s.append(t(round(lx) + 4, y + 12, 'B社の公開仕様 1伝票100行', 11, INK)) s.append(t(24, 372, f"取り消し{n_cancel}回の記録が残った数:設計1 0件、設計2 {kept2}件(マイナスの出荷行)", 12.5, INK)) s.append(t(24, 392, CAP, 11, MUTE)) s.append('') return ''.join(s) def fig3(): """明細行の多い受注5件で、1枚の伝票の行数を比べる""" big = sorted(zip(slip1, slip2), reverse=True)[:5] W, H = 700, 305 title = ('明細行の多い架空の受注5件で、1枚の伝票の行数を比べた横棒グラフ。1伝票の設計では' + '・'.join(str(a) for a, _ in big) + '行で、3件がB社の公開仕様の100行を超える。分ける設計では受注明細の' + '・'.join(str(b) for _, b in big) + '行のまま') s = head(W, H, 'psb-slip', title) s.append(t(20, 26, '明細行の多い受注5件:1枚の伝票に載る行数', 14, INK, w=700)) x0, x1, mx = 110, 640, 150 for v in range(0, 151, 50): x = x0 + (x1 - x0) * v / mx s.append(f"") s.append(t(round(x), 266, f'{v}行', 11, MUTE, 'middle')) lx = x0 + (x1 - x0) * SLIP_LIMIT / mx s.append(f"") s.append(t(round(lx) + 6, 48, 'B社の公開仕様 100行', 11, INK)) for i, (a, b) in enumerate(big): y = 56 + i * 40 s.append(t(x0 - 10, y + 22, f'受注{i + 1}', 12.5, INK, 'end', 700)) wa, wb = (x1 - x0) * a / mx, (x1 - x0) * b / mx s.append(f"" + t(round(x0 + wa + 6), y + 12, f'{a}行', 11.5, INK, w=700)) s.append(f"" + t(round(x0 + wb + 6), y + 29, f'{b}行', 11.5, GT, w=700)) s.append(f"" + t(x0 + 18, 283, '設計1 1伝票(分けた行が同じ伝票に増える)', 11.5, INK)) s.append(f"" + t(x0 + 308, 283, '設計2 分ける(受注明細のまま)', 11.5, INK)) s.append(t(20, 300, CAP, 10.5, MUTE)) s.append('') return ''.join(s), big if __name__ == '__main__': print(json.dumps(res, ensure_ascii=False, indent=1)) OUT_SVG.parent.mkdir(parents=True, exist_ok=True); OUTDIR.mkdir(parents=True, exist_ok=True) OUT_SVG.write_text(fig2(), encoding='utf-8') (OUTDIR / 'psb_fig1_tables.svg').write_text(fig1(), encoding='utf-8') f3, big = fig3() (OUTDIR / 'psb_fig3_slip_lines.svg').write_text(f3, encoding='utf-8') print('big5', big)