huahuibao/sql/run_all_payBack_sql.php

<?php
$host = '118.178.193.23';
$db = 'huahuibao';
$user = 'root';
$pass = 'zhh20250805';
$pdo = new PDO("mysql:host=$host;dbname=$db;charset=utf8mb4", $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$results = [];
function run(PDO $pdo, $sql, &$results, $label = '') {
    try {
        $pdo->exec($sql);
        $results[] = ['status' => 'ok', 'label' => $label, 'sql' => preg_replace('/\s+/', ' ', trim($sql))];
    } catch (Exception $e) {
        $results[] = ['status' => 'err', 'label' => $label, 'sql' => preg_replace('/\s+/', ' ', trim($sql)), 'msg' => $e->getMessage()];
    }
}

$cols = $pdo->query('DESCRIBE xhScanPay')->fetchAll(PDO::FETCH_COLUMN);
if (!in_array('tkPrice', $cols, true)) {
    run($pdo, "ALTER TABLE xhScanPay ADD COLUMN tkPrice decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '累计退款金额' AFTER actPrice", $results, 'add tkPrice');
} else {
    $results[] = ['status' => 'skip', 'label' => 'tkPrice exists'];
}
$cols = $pdo->query('DESCRIBE xhScanPay')->fetchAll(PDO::FETCH_COLUMN);
if (!in_array('refundLog', $cols, true)) {
    run($pdo, "ALTER TABLE xhScanPay ADD COLUMN refundLog tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否有退款记录' AFTER tkPrice", $results, 'add refundLog');
} else {
    $results[] = ['status' => 'skip', 'label' => 'refundLog exists'];
}

run($pdo, "CREATE TABLE IF NOT EXISTS xhScanPayRefund (
  id int(11) NOT NULL AUTO_INCREMENT,
  orderSn varchar(32) NOT NULL COMMENT '退款单号 SR*',
  relateOrderSn varchar(32) NOT NULL COMMENT '原收款单号 SC*',
  scanPayId int(11) NOT NULL DEFAULT 0,
  mainId int(11) NOT NULL DEFAULT 0,
  shopId int(11) NOT NULL DEFAULT 0,
  hdId int(11) NOT NULL DEFAULT 0,
  customId int(11) NOT NULL DEFAULT 0,
  customName varchar(64) NOT NULL DEFAULT '',
  refundPrice decimal(10,2) NOT NULL DEFAULT 0.00,
  payWay tinyint(1) NOT NULL DEFAULT 0 COMMENT '0微信 1支付宝',
  status tinyint(1) NOT NULL DEFAULT 1 COMMENT '1已通过',
  thirdRefundNo varchar(64) NOT NULL DEFAULT '' COMMENT '拉卡拉退款流水号',
  shopAdminId int(11) NOT NULL DEFAULT 0,
  shopAdminName varchar(32) NOT NULL DEFAULT '',
  remark varchar(255) NOT NULL DEFAULT '',
  addTime datetime NOT NULL,
  PRIMARY KEY (id),
  KEY idx_relateOrderSn (relateOrderSn),
  KEY idx_mainId (mainId),
  KEY idx_scanPayId (scanPayId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='收款码退款流水'", $results, 'create xhScanPayRefund');

run($pdo, "CREATE TABLE IF NOT EXISTS xhScanPayRefundSn (
  id int(11) NOT NULL AUTO_INCREMENT,
  mainId int(11) NOT NULL DEFAULT 0,
  shopId int(11) NOT NULL DEFAULT 0,
  hdId int(11) NOT NULL DEFAULT 0,
  customId int(11) NOT NULL DEFAULT 0,
  addTime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", $results, 'create xhScanPayRefundSn');

run($pdo, "ALTER TABLE xhScanPayRefundSn MODIFY addTime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP", $results, 'fix addTime default');

echo json_encode($results, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT) . PHP_EOL;