ylrz_saas_pharmacy/java/scripts/add-conversation-summary-menu.js

/**
 * Deploy menu 29948 (conversation-summary) to master + all tenant DBs.
 * Use \\u escapes only for Chinese literals.
 */
const mysql = require('mysql2/promise')

const MENU = {
  id: 29948,
  name: '\u4f1a\u8bdd\u6458\u8981',
  parentId: 29900,
  orderNum: 161,
  path: 'conversation-summary',
  component: 'lobster/conversation-summary/index',
  perms: 'workflow:lobster:summary',
  icon: 'el-icon-document-copy',
  refMenuId: 29923
}

async function main() {
  const host = process.env.DB_HOST || 'cq-cdb-8fjmemkb.sql.tencentcdb.com'
  const port = Number(process.env.DB_PORT || 27220)
  const user = process.env.DB_USER || 'root'
  const password = process.env.DB_PASSWORD || 'Ylrz_1q2w3e4r5t6y'
  const masterDb = process.env.DB_NAME || 'ylrz_saas'

  const master = await mysql.createConnection({ host, port, user, password, database: masterDb, charset: 'utf8mb4' })
  console.log('=== MASTER ===')
  await deployMaster(master)
  await master.end()

  const tenantConn = await mysql.createConnection({ host, port, user, password, database: masterDb, charset: 'utf8mb4' })
  const [tenants] = await tenantConn.query('SELECT id, tenant_code, db_url, db_account, db_pwd FROM tenant_info WHERE status=1')
  await tenantConn.end()

  for (const t of tenants) {
    const db = parseDbName(t.db_url)
    if (!db) continue
    console.log('=== TENANT', t.id, t.tenant_code, '===')
    try {
      const conn = await mysql.createConnection({
        host, port, user: t.db_account || user, password: t.db_pwd || password, database: db, charset: 'utf8mb4'
      })
      await deployTenant(conn)
      await conn.end()
    } catch (e) {
      console.log('skip:', e.message)
    }
  }
  console.log('Done.')
}

async function fixMenuNames(conn, tables) {
  for (const table of tables) {
    if (!(await tableExists(conn, table))) continue
    const [r] = await conn.query(
      `UPDATE ${table} SET menu_name=? WHERE menu_id=? OR component=?`,
      [MENU.name, MENU.id, MENU.component]
    )
    if (r.affectedRows) console.log(table, 'name fix rows=', r.affectedRows)
  }
}

async function deployMaster(conn) {
  if (await tableExists(conn, 'tenant_sys_menu')) {
    const [r] = await conn.query(
      `INSERT IGNORE INTO tenant_sys_menu
      (menu_id, menu_name, parent_id, order_num, path, component, menu_type, icon, visible, status, is_frame, is_cache, perms, create_by, create_time, remark)
      VALUES (?, ?, ?, ?, ?, ?, 'C', ?, '0', '0', 0, 0, ?, 'admin', NOW(), ?)`,
      [MENU.id, MENU.name, MENU.parentId, MENU.orderNum, MENU.path, MENU.component, MENU.icon, MENU.perms, MENU.name]
    )
    console.log('tenant_sys_menu rows=', r.affectedRows)
  }
  if (await tableExists(conn, 'tenant_company_menu')) {
    const [r] = await conn.query(
      `INSERT IGNORE INTO tenant_company_menu
      (menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, company_id, remark)
      SELECT menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, NULL, remark
      FROM tenant_sys_menu WHERE menu_id = ?`,
      [MENU.id]
    )
    console.log('tenant_company_menu rows=', r.affectedRows)
  }
  await fixMenuNames(conn, ['tenant_sys_menu', 'tenant_company_menu'])
}

async function deployTenant(conn) {
  if (!(await tableExists(conn, 'sys_menu'))) return
  const [r1] = await conn.query(
    `INSERT IGNORE INTO sys_menu
    (menu_id, menu_name, parent_id, order_num, path, component, menu_type, icon, visible, status, is_frame, is_cache, perms, create_by, create_time, remark)
    VALUES (?, ?, ?, ?, ?, ?, 'C', ?, '0', '0', 0, 0, ?, 'admin', NOW(), ?)`,
    [MENU.id, MENU.name, MENU.parentId, MENU.orderNum, MENU.path, MENU.component, MENU.icon, MENU.perms, MENU.name]
  )
  console.log('sys_menu rows=', r1.affectedRows)

  if (await tableExists(conn, 'company_menu')) {
    const [r2] = await conn.query(
      `INSERT IGNORE INTO company_menu
      (menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, company_id, remark)
      SELECT menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, NULL, remark
      FROM sys_menu WHERE menu_id = ?`,
      [MENU.id]
    )
    console.log('company_menu rows=', r2.affectedRows)
  }

  if (await tableExists(conn, 'sys_role_menu')) {
    const [r3] = await conn.query(
      `INSERT IGNORE INTO sys_role_menu (role_id, menu_id)
       SELECT DISTINCT rm.role_id, ?
       FROM sys_role_menu rm
       JOIN sys_menu m ON m.menu_id = rm.menu_id
       WHERE m.menu_id = ? OR m.parent_id = ?`,
      [MENU.id, MENU.parentId, MENU.parentId]
    )
    console.log('sys_role_menu rows=', r3.affectedRows)
  }

  if (await tableExists(conn, 'company_role_menu')) {
    const [r4] = await conn.query(
      `INSERT IGNORE INTO company_role_menu (role_id, menu_id)
       SELECT DISTINCT rm.role_id, ?
       FROM company_role_menu rm
       JOIN company_menu m ON m.menu_id = rm.menu_id
       WHERE m.menu_id = ? OR m.parent_id = ?`,
      [MENU.id, MENU.parentId, MENU.parentId]
    )
    console.log('company_role_menu rows=', r4.affectedRows)
  }

  await fixMenuNames(conn, ['sys_menu', 'company_menu'])
}

async function tableExists(conn, table) {
  const [r] = await conn.query('SHOW TABLES LIKE ?', [table])
  return r.length > 0
}

function parseDbName(dbUrl) {
  if (!dbUrl) return null
  const m = String(dbUrl).match(/\/([^/?]+)(?:\?|$)/)
  return m ? m[1] : null
}

main().catch(e => { console.error(e); process.exit(1) })