县城乡镇同城外卖加跑腿：四端订单、商家审核与结算验收

CREATE TABLE merchant_application (
  id INTEGER PRIMARY KEY,
  merchant_name TEXT NOT NULL,
  subject_type TEXT NOT NULL,
  license_no TEXT,
  food_permit_no TEXT,
  permit_expire_at TEXT,
  contact_phone TEXT NOT NULL,
  settle_account TEXT,
  audit_status TEXT NOT NULL DEFAULT 'PENDING',
  audit_note TEXT,
  created_at TEXT NOT NULL
);
CREATE INDEX idx_merchant_audit ON merchant_application(audit_status, permit_expire_at);

// order-pricing.ts
// 依赖：Node 18+，typescript 与 tsx
// 运行：npm i -D typescript tsx && npx tsx order-pricing.ts

type BizType = 'takeout' | 'errand';
type ErrandType = 'buy' | 'send' | 'pickup';

interface QuoteInput {
  bizType: BizType;
  errandType?: ErrandType;
  distanceKm: number;
  weightKg: number;
  isNight: boolean;
  itemsAmount?: number;
}

interface QuoteLine {
  name: string;
  amount: number;
}

interface QuoteResult {
  total: number;
  lines: QuoteLine[];
}

const BASE_FEE = 4;
const BASE_KM = 2;
const EXTRA_PER_KM = 1.5;
const FREE_WEIGHT_KG = 5;
const EXTRA_PER_KG = 1;
const NIGHT_FEE = 2;

function isInServiceArea(distanceKm: number, maxKm: number): boolean {
  return distanceKm > maxKm ? false : true;
}

function quoteErrand(input: QuoteInput): QuoteResult {
  const lines: QuoteLine[] = [];
  lines.push({ name: '起步价(含2公里)', amount: BASE_FEE });
  const extraKm = Math.max(0, input.distanceKm - BASE_KM);
  if (extraKm > 0) {
    lines.push({ name: '超程费', amount: Number((extraKm * EXTRA_PER_KM).toFixed(2)) });
  }
  const extraWeight = Math.max(0, input.weightKg - FREE_WEIGHT_KG);
  if (extraWeight > 0) {
    lines.push({ name: '超重费', amount: Number((extraWeight * EXTRA_PER_KG).toFixed(2)) });
  }
  if (input.isNight) {
    lines.push({ name: '夜间服务费', amount: NIGHT_FEE });
  }
  const itemsAmount = input.itemsAmount ?? 0;
  if (input.errandType === 'buy' && itemsAmount > 0) {
    lines.push({ name: '商品预估价(不计入服务费)', amount: Number(itemsAmount.toFixed(2)) });
  }
  const total = lines
    .filter((line) => line.name.startsWith('商品预估价') === false)
    .reduce((sum, line) => sum + line.amount, 0);
  return { total: Number(total.toFixed(2)), lines };
}

const cases: QuoteInput[] = [
  { bizType: 'errand', errandType: 'send', distanceKm: 1.2, weightKg: 2, isNight: false },
  { bizType: 'errand', errandType: 'buy', distanceKm: 3.5, weightKg: 6, isNight: true, itemsAmount: 38 },
  { bizType: 'takeout', distanceKm: 3.5, weightKg: 6, isNight: true },
];

for (const item of cases) {
  if (item.bizType !== 'errand') {
    console.log(item.bizType, '走外卖计价规则，本示例只展示跑腿');
    continue;
  }
  console.log(JSON.stringify(quoteErrand(item), null, 2));
}
console.log('超范围校验:', isInServiceArea(3.5, 3));

CREATE TABLE settlement_entry (
  id INTEGER PRIMARY KEY,
  order_no TEXT NOT NULL,
  biz_type TEXT NOT NULL,
  account_type TEXT NOT NULL,
  amount_cents INTEGER NOT NULL,
  created_at TEXT NOT NULL
);

SELECT order_no,
       SUM(CASE WHEN account_type = 'user_paid' THEN amount_cents ELSE 0 END) AS paid,
       SUM(CASE WHEN account_type = 'merchant_payable' THEN amount_cents ELSE 0 END) AS merchant,
       SUM(CASE WHEN account_type = 'rider_income' THEN amount_cents ELSE 0 END) AS rider,
       SUM(CASE WHEN account_type = 'platform_fee' THEN amount_cents ELSE 0 END) AS platform,
       SUM(CASE WHEN account_type = 'refund' THEN amount_cents ELSE 0 END) AS refund
FROM settlement_entry
GROUP BY order_no;