模块:Xb2QueryHelper

来自乐园数据管理室
ヒカリ​（留言 | 贡献）2020年2月2日 (日) 19:13的版本 （MWBot）

此模块的文档可以在模块:Xb2QueryHelper/doc创建 

local p = {}

p.language = "cn"

local cache = {}

function p.query(sql)
  local cacheKey = p.language .. ":" .. sql
  if cache[cacheKey] then
    return cache[cacheKey]
  end
  local result = mw.xbdb.query(sql, p.language)
  cache[cacheKey] = result
  return result
end

function p.queryOne(sql)
  local result = p.query(sql)
  if #result == 1 then
    result = result[1]
  elseif #result == 0 then
    error("SQL返回结果为空\n" .. sql)
  else
    error("结果行数大于1，请检查WHERE条件\n" .. sql)
  end
  return result
end

function p.getEnhance(id)
  if type(id) ~= "number" then
    error("id must be number")
  end

  local result =
    p.queryOne(
    [[
    SELECT
      "BTL_Enhance"."Param1",
      "BTL_Enhance"."Param2",
      "BTL_EnhanceEff"."Param",
      "btl_enhance_cap"."name" AS "Caption",
      "btl_buff_ms"."name" AS "BuffName" 
    FROM
      "BTL_Enhance"
      INNER JOIN "BTL_EnhanceEff" ON "BTL_Enhance"."EnhanceEffect" = "BTL_EnhanceEff".row_id
      LEFT JOIN "btl_enhance_cap" ON "BTL_Enhance"."Caption" = "btl_enhance_cap".row_id
      LEFT JOIN "btl_buff_ms" ON "BTL_EnhanceEff"."Name" = "btl_buff_ms".row_id
    WHERE
      "BTL_Enhance".row_id = ]] ..
      id
  )

  local CaptionText =
    p.replaceCaptionParam(
    result.Caption or "",
    {
      Param = result.Param,
      Param1 = result.Param1,
      Param2 = result.Param2
    }
  )

  result.RenderedCaption = CaptionText

  return result
end

function p.replaceCaptionParam(caption, params)
  local str = caption

  local paramStrArray = {}
  local paramStr = ""
  local inStr = false

  for i = 1, mw.ustring.len(str) do
    local c = mw.ustring.sub(str, i, i)

    if c == "]" then
      inStr = false

      if not mw.ustring.find(paramStr, "ML:Enhance") then
        error("找不到 ML:Enhance:" .. paramStr)
      end

      if mw.ustring.find(paramStr, "Param1") then
        paramStrArray.Param1 = paramStr
      elseif mw.ustring.find(paramStr, "Param2") then
        paramStrArray.Param2 = paramStr
      else
        paramStrArray.Param = paramStr
      end
    end

    if inStr then
      paramStr = paramStr .. c
    end

    if c == "[" then
      inStr = true
      paramStr = ""
    end
  end

  for k, v in pairs(paramStrArray) do
    str = mw.ustring.gsub(str, "%[" .. v .. "%]", params[k])
  end

  return str
end

function p.getBuff(id)
  return p.queryOne(
    [[
    SELECT
      "btl_buff_ms"."name" AS "Name",
      "btl_buff_cap"."name" AS "Caption",
      "BaseNum",
      "BaseTime",
      "BaseRecast",
      "BaseProb",
      "Interval" 
    FROM
      "BTL_Buff"
      LEFT JOIN "btl_buff_cap" ON "BTL_Buff"."Caption" = "btl_buff_cap".row_id
      LEFT JOIN "btl_buff_ms" ON "BTL_Buff"."Name" = "btl_buff_ms".row_id
    WHERE
      "BTL_Buff".row_id = ]] ..
      id
  )
end

function p.getArtsDr(id)
  return p.queryOne(
    [[
    SELECT
      btl_arts_dr_ms."name" AS "NameText",
      btl_arts_dr_cap."name" AS "CaptionText",
      "BTL_Arts_Dr".* 
    FROM
      "BTL_Arts_Dr"
      LEFT JOIN btl_arts_dr_ms ON "BTL_Arts_Dr"."Name" = btl_arts_dr_ms.row_id
      LEFT JOIN btl_arts_dr_cap ON "BTL_Arts_Dr"."Caption" = btl_arts_dr_cap.row_id
    WHERE
      "BTL_Arts_Dr".row_id = ]] ..
      id
  )
end

function p.getArtsDrListByDriver(id)
  return p.query(
    [[
    SELECT
      btl_arts_dr_ms."name" AS "NameText",
      btl_arts_dr_cap."name" AS "CaptionText",
      condcapms."name" AS "CondCapText",
      talentcapms."name" AS "TalentCapText",
      "BTL_Arts_Dr".* 
    FROM
      "BTL_Arts_Dr"
      LEFT JOIN btl_arts_dr_ms ON "BTL_Arts_Dr"."Name" = btl_arts_dr_ms.row_id
      LEFT JOIN btl_arts_dr_cap ON "BTL_Arts_Dr"."Caption" = btl_arts_dr_cap.row_id
			LEFT JOIN btl_arts_dr_cap AS condcapms ON "BTL_Arts_Dr"."CondCap" = condcapms.row_id
      LEFT JOIN btl_arts_dr_cap AS talentcapms ON "BTL_Arts_Dr"."TalentCap" = talentcapms.row_id
    WHERE
      "BTL_Arts_Dr"."Driver" = ]] ..
      id
  )
end

function p.getDr(id)
  local result =
    p.queryOne(
    [[
    SELECT
      chr_dr_ms."name" AS "NameText",
      category1."name" AS "FavoriteCategory1Text",
      itm1ms."name" AS "FavoriteItem1Text",
      category2."name" AS "FavoriteCategory2Text",
      itm2ms."name" AS "FavoriteItem2Text",
      "CHR_Dr".* 
    FROM
      "CHR_Dr"
      LEFT JOIN chr_dr_ms ON "CHR_Dr"."Name" = chr_dr_ms.row_id
      LEFT JOIN menu_favorite_category AS category1 ON "CHR_Dr"."FavoriteCategory1" - 11 = category1.row_id
      LEFT JOIN menu_favorite_category AS category2 ON "CHR_Dr"."FavoriteCategory2" - 11 = category2.row_id
      LEFT JOIN "ITM_FavoriteList" AS item1 ON "CHR_Dr"."FavoriteItem1" = item1.row_id
      LEFT JOIN itm_favorite AS itm1ms ON item1."Name" = itm1ms.row_id
      LEFT JOIN "ITM_FavoriteList" AS item2 ON "CHR_Dr"."FavoriteItem2" = item2.row_id
      LEFT JOIN itm_favorite AS itm2ms ON item2."Name" = itm2ms.row_id 
    WHERE
      "CHR_Dr".row_id = ]] ..
      id
  )

  local WpnType = result.WpnType
  WpnType = mw.ustring.sub(WpnType, 2)
  WpnType = mw.ustring.sub(WpnType, 1, -2)

  local WpnTypeArray = {}
  for str in mw.ustring.gmatch(WpnType, "([^,]+)") do
    table.insert(WpnTypeArray, str)
  end

  local condition = {}
  for i, v in ipairs(WpnTypeArray) do
    if tonumber(v) > 0 then
      table.insert(condition, i)
    end
  end
  condition = table.concat(condition, ", ")

  result.WpnType =
    p.query(
    [[
    SELECT
      "ITM_PcWpnType".row_id,
      itm_pcwpntype_ms."name" AS "Name",
      menu_role_name_ms."name" AS "Role",
      "ITM_PcWpnType"."JustRange",
      "ITM_PcWpnType"."JustRange2"
    FROM
      "ITM_PcWpnType"
      LEFT JOIN itm_pcwpntype_ms ON itm_pcwpntype_ms.row_id = "ITM_PcWpnType"."Name"
      LEFT JOIN menu_role_name_ms ON menu_role_name_ms.row_id = "ITM_PcWpnType"."Role"    
    WHERE
      "ITM_PcWpnType".row_id IN (]] ..
      condition .. [[)
    ]]
  )

  return result
end

function p.getSkillDrList(id)
  local driverIds = {
    [1] = true,
    [2] = true,
    [3] = true,
    [4] = true,
    [5] = true,
    [6] = true,
    [17] = true,
    [18] = true,
    [19] = true
  }
  if not driverIds[tonumber(id)] then
    return nil
  end

  if tonumber(id) < 10 then
    id = "0" .. tostring(tonumber(id))
  end

  local result =
    p.query(
    [[
      SELECT
        btl_skill_dr_name."name" AS "NameText",
        "BTL_Skill_Dr_Table]] ..
      id ..
        [[".*,
        "BTL_Skill_Dr"."Enhance"
      FROM
        "BTL_Skill_Dr_Table]] ..
          id ..
            [["
      LEFT JOIN "BTL_Skill_Dr" ON "BTL_Skill_Dr_Table]] ..
              id ..
                [["."SkillID" = "BTL_Skill_Dr".row_id
      LEFT JOIN  btl_skill_dr_name ON btl_skill_dr_name.row_id = "BTL_Skill_Dr"."Name"
      ORDER BY
        "Round",
        "RowNum",
        "ColumnNum"
    ]]
  )

  for _, row in ipairs(result) do
    row.Enhance = p.getEnhance(tonumber(row.Enhance))
  end
  return result
end

function p.getNpcPopByNpcId(npcId)
  if type(npcId) ~= "number" then
    error("getNpcPopByNpcId 的参数不是number")
  end

  -- 获取所有NpcPops表名
  local tables =
    p.query(
    mw.ustring.format(
      [[SELECT tablename FROM pg_catalog.pg_tables WHERE "schemaname" = '%s' AND "tablename" != 'ma90a_FLD_NpcPop' AND "tablename" like '%%_FLD_NpcPop']],
      p.language
    )
  )

  local result = {}
  for _, t in ipairs(tables) do
    local sql = mw.ustring.format([[SELECT * FROM "%s" WHERE "NpcID" = '%s']], t.tablename, npcId)
    local resultInSingleTable = p.query(sql)
    for _, npcPopRow in ipairs(resultInSingleTable) do
      -- Npc存在Group字段，则用Group覆盖自身数据
      if npcPopRow.Group > 0 then
        local groupData =
          p.queryOne(
          mw.ustring.format(
            [[SELECT "memberNum", "ScenarioFlagMin", "ScenarioFlagMax", "QuestFlag", "QuestFlagMin", "QuestFlagMax", "TimeRange", "Condition" FROM "FLD_NpcGroupId" WHERE "row_id" = '%d']],
            npcPopRow.Group
          )
        )
        for k, v in pairs(groupData) do
          npcPopRow[k] = v
        end
      end
      table.insert(result, npcPopRow)
    end
  end

  return result
end

function p.getNpcByName(npcName)
  return p.query(
    mw.ustring.format(
      [[SELECT * FROM "RSC_NpcList" WHERE "Name" IN (SELECT row_id FROM fld_npcname WHERE "name"='%s')]],
      npcName
    )
  )
end

function p.getNpcPopByNpcName(npcName)
  local npcs = p.getNpcByName(npcName)
  for _, npc in ipairs(npcs) do
    local npcId = npc.row_id
    npc.Pops = p.getNpcPopByNpcId(npcId)
  end
  return npcs
end

local function getEventTableName(id)
  if id > 60000 then
    return "EVT_listDeb01"
  end
  if (id > 40000) then
    return "EVT_listTlk01"
  end
  if (id > 30000) then
    return "EVT_listFev01"
  end
  if (id > 20000) then
    return "EVT_listQst01"
  end
  if (id > 19000) then
    return "EVT_listBl"
  end
  return "EVT_listBf"
end

local function checkTableExist(tablename)
  local talkTableCount =
    p.queryOne(
    mw.ustring.format(
      [[SELECT COUNT(*) FROM pg_catalog.pg_tables WHERE "schemaname" = '%s' AND  "tablename" = '%s']],
      p.language,
      tablename
    )
  )

  if talkTableCount.count == 0 then
    -- mw.log("eventTable: " .. tablename .. " not exist")
    return false
  end

  return true
end

function p.getDialogByEventId(eventId)
  if type(eventId) ~= "number" then
    error("event id must be number")
  end

  if eventId == 0 then
    return {}
  end

  local eventTable = getEventTableName(eventId)
  local event = p.queryOne(mw.ustring.format([[SELECT "evtName" FROM "%s" WHERE "row_id" = %s]], eventTable, eventId))
  local talkTable = event.evtName .. "_ms"

  mw.log(talkTable)

  if checkTableExist(talkTable) == false then
    return {}
  end
  local talks = p.query(mw.ustring.format([[SELECT "name" FROM "%s" WHERE "style" != 62 AND "style" != 0]], talkTable))
  local ret = {}
  for _, talk in ipairs(talks) do
    table.insert(ret, talk.name)
  end
  return ret
end

local function getQuestListTableName(id, ira)
  if ira then
    -- 黄金国
    if id > 2000 then
      return "FLD_QuestListNormalIra", "fld_quest_normal"
    end
  else
    -- 本篇
    if id > 7000 then
      return "FLD_QuestListAchievement", "fld_quest_achievement"
    end
    if id > 6000 then
      return "FLD_QuestListMercenaries", "fld_quest_mercenaries"
    end
    if id > 5000 then
      return "FLD_QuestListBlade", "fld_quest_blade"
    end
    if id > 2000 then
      return "FLD_QuestListNormal", "fld_quest_normal"
    end
    if id > 1000 then
      return "FLD_QuestListMini", "fld_quest_mini"
    end
  end
end

function p.getQuestTitleByQuestId(questId, ira)
  if type(questId) ~= "number" then
    error("quest id must be number")
  end

  if questId == 0 then
    return nil
  end

  local questTable, questTableMs = getQuestListTableName(questId, ira)
  if questTable == nil then
    return nil
  end

  -- 通过QuestTitle查询实际任务名
  local function getQuestTitle(titleId)
    if titleId <= 0 then
      return nil
    end
    local questTitle =
      p.queryOne(mw.ustring.format([[SELECT "name" FROM "%s" WHERE "row_id" = '%s']], questTableMs, titleId))
    return questTitle.name
  end

  local quest =
    p.queryOne(
    mw.ustring.format([[SELECT "QuestTitle", "PRTQuestID" FROM "%s" WHERE "row_id" = '%s']], questTable, questId)
  )

  -- 若结果是子任务，通过PRTQuestID字段查询父任务
  if quest.QuestTitle > 0 then
    return getQuestTitle(quest.QuestTitle)
  elseif quest.PRTQuestID > 0 then
    local prtQuest =
      p.queryOne(
      mw.ustring.format([[SELECT "QuestTitle" FROM "%s" WHERE "row_id" = '%s']], questTable, quest.PRTQuestID)
    )
    return getQuestTitle(prtQuest.QuestTitle)
  end
  return nil
end

return p
检索自“https://xenoblade2.cn/index.php?title=模块:Xb2QueryHelper&oldid=41713”