let // =================================================================================== // 用户参数 - 请确保已在 Power Query 参数管理器中定义以下参数 // =================================================================================== // --- K3 Cloud 连接与认证参数 --- // Parameter_BaseUrl: (文本) K3 Cloud 实例URL。 // Parameter_AccountID: (文本) K3 账套 ID。 // Parameter_Username: (文本) K3 用户名。 // Parameter_Password: (文本) K3 密码。 // --- 数据查询参数 --- // Parameter_FormId: (文本) 查询的单据/数据源 FormId。 // Parameter_FieldKeys: (文本) 需检索的字段键 (逗号分隔)。 // Parameter_PageSize: (数字, 可选, 默认 2000) 每页行数。 // Parameter_StartRow: (数字, 可选, 默认 0) 起始行索引。 // Parameter_OrderString: (文本, 可选) 排序字符串。 // Parameter_TopRowCount: (数字, 可选, 默认 0) 返回前 N 行。 // Parameter_SubSystemId: (文本, 可选) 子系统 ID。 // --- 日期筛选参数 (若 Parameter_FilterConditions_Structure 中使用) --- // Parameter_StartTime: (文本) 开始日期时间 (格式: "yyyy-MM-dd HH:mm:ss")。 // Parameter_EndTime: (文本) 结束日期时间 (格式: "yyyy-MM-dd HH:mm:ss")。 // --- 筛选条件 (高级) --- // Parameter_FilterConditions_Structure: (列表类型) 在代码中定义的高级筛选条件结构。 // - 依赖于 Parameter_StartTime 和 Parameter_EndTime。 // - Kingdee 比较操作符示例: "39" (>=), "16" (<=), "8" (=)。 Parameter_FilterConditions_Structure = { [FieldName = "FDATE", Compare = "39", Value = Parameter_StartTime, Left = "", Right = "", Logic = "0"], // Logic "0" 通常表示 AND [FieldName = "FDATE", Compare = "16", Value = Parameter_EndTime, Left = "", Right = "", Logic = "0"] }, // 若不需要任何筛选条件，可将上面的 Parameter_FilterConditions_Structure 设置为 null 或空列表 {} // --- 去重操作参数 --- // Parameter_DistinctByColumnName: (文本, 可选) 基于此列去重。留空或设为 null 则禁用。 // =================================================================================== // 脚本核心逻辑 - 通常无需修改此部分 // =================================================================================== // --- 硬编码常量 --- Hardcoded_Lcid = "2052", // Lcid: "2052" 代表简体中文 // --- 辅助变量 --- // 确保 Parameter_FieldKeys 存在且不为空 DynamicColumnNames = if Parameter_FieldKeys <> null and Parameter_FieldKeys <> "" then Text.Split(Parameter_FieldKeys, ",") else {"Error_FieldKeys_Not_Set"}, // --- 登录函数 --- // 功能: 登录 K3 Cloud 并获取会话 ID。 LoginFunction = (baseUrl as text, acctID as text, username as text, password as text) as record => let _ = if baseUrl = null or acctID = null or username = null or password = null then error "K3 登录参数不完整。请在 Power Query 参数管理器中检查 Parameter_BaseUrl, Parameter_AccountID, Parameter_Username, Parameter_Password。" else null, loginRequestBody = Json.FromValue([acctID = acctID, username = username, password = password, lcid = Hardcoded_Lcid]), loginResponseSource = Web.Contents( baseUrl & "Kingdee.BOS.WebApi.ServicesStub.AuthService.ValidateUser.common.kdsvc", [Headers = [#"Content-Type" = "application/json"], Content = loginRequestBody, Timeout = #duration(0, 0, 2, 0)] // 登录超时2分钟 ), jsonResponse = Json.Document(loginResponseSource), loginResultType = Record.FieldOrDefault(jsonResponse, "LoginResultType", -1), errorMessage = Record.FieldOrDefault(jsonResponse, "Message", "未知登录错误"), sessionId = if loginResultType = 1 and Record.HasFields(jsonResponse, "Context") and Record.FieldOrDefault(jsonResponse[Context], "SessionId", null) <> null then jsonResponse[Context][SessionId] else error "K3 Cloud 登录失败: " & errorMessage & " (LoginResultType: " & Text.From(loginResultType) & ")", kdsVCSessionId = if loginResultType = 1 and Record.HasFields(jsonResponse, "KDSVCSessionId") then jsonResponse[KDSVCSessionId] else error "K3 Cloud 登录失败: 未能获取 KDSVCSessionId. " & errorMessage in [SessionId = sessionId, KDSVCSessionId = kdsVCSessionId], // 执行登录操作 loginCredentials = LoginFunction(Parameter_BaseUrl, Parameter_AccountID, Parameter_Username, Parameter_Password), currentSessionId = loginCredentials[SessionId], currentKDSVCSessionId = loginCredentials[KDSVCSessionId], // --- 数据获取函数 --- // 功能: 调用 K3 Cloud API 获取数据。 GetDataFunction = ( baseUrl as text, sessionId as text, kdsessionId as text, formIdToQuery as text, fieldKeysToQuery as text, pageSizeToUse as number, filterConditionsToApply as any, orderStringCriteria as text, topRowCountNum as number, startRowNum as number, subSystemIdToUse as text, columnNamesForTable as list, optionalDistinctColumnName as nullable text ) as table => let _ = if formIdToQuery = null or fieldKeysToQuery = null then error "数据查询参数不完整。请检查 Parameter_FormId 和 Parameter_FieldKeys。" else null, actualFilterString = if filterConditionsToApply = null then "" else if filterConditionsToApply is list then filterConditionsToApply // API 端点必须支持此结构 else Text.From(filterConditionsToApply), queryPayload = [ FormId = formIdToQuery, FieldKeys = fieldKeysToQuery, FilterString = actualFilterString, OrderString = orderStringCriteria, TopRowCount = topRowCountNum, StartRow = startRowNum, Limit = pageSizeToUse, SubSystemId = subSystemIdToUse ], requestBody = Json.FromValue([formid = formIdToQuery, data = queryPayload]), apiResponseSource = Web.Contents( baseUrl & "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc", [ Headers = [#"Content-Type" = "application/json", #"Cookie" = "ASP.NET_SessionId=" & sessionId & "; kdservice-sessionid=" & kdsessionId], Content = requestBody, Timeout = #duration(0, 0, 5, 0) // 数据查询超时5分钟 ] ), responseText = Text.FromBinary(apiResponseSource, TextEncoding.Utf8), rawJsonResult = Json.Document(responseText), validatedResult = if rawJsonResult is list then rawJsonResult else if rawJsonResult is record then let apiErrorMessage = Record.FieldOrDefault(Record.FieldOrDefault(rawJsonResult, "Result", rawJsonResult), "Message", "无法解析API错误信息。") in error "K3 Cloud API 调用失败: " & Text.From(apiErrorMessage) & " 完整响应: " & responseText else error "K3 Cloud API 未返回预期的列表或记录格式。响应: " & responseText, resultTable = Table.FromRows(validatedResult, columnNamesForTable), distinctTable = if optionalDistinctColumnName <> null and optionalDistinctColumnName <> "" and List.Contains(Table.ColumnNames(resultTable), optionalDistinctColumnName) then Table.Distinct(resultTable, {optionalDistinctColumnName}) else resultTable, typeTransformations = List.Transform(columnNamesForTable, each {_, type text}), finalTypedTable = Table.TransformColumnTypes(distinctTable, typeTransformations) in finalTypedTable, // 处理可选参数的默认值 Actual_PageSize = try Parameter_PageSize otherwise 0, Actual_StartRow = try Parameter_StartRow otherwise 0, Actual_OrderString = try Parameter_OrderString otherwise "", Actual_TopRowCount = try Parameter_TopRowCount otherwise 0, Actual_SubSystemId = try Parameter_SubSystemId otherwise "", Actual_DistinctByColumnName = try Parameter_DistinctByColumnName otherwise null, Actual_FilterConditions = if (try Parameter_StartTime otherwise null) <> null and (try Parameter_EndTime otherwise null) <> null then (try Parameter_FilterConditions_Structure otherwise null) else null, // 时间参数缺失则不应用筛选 // 调用数据获取函数 ResultOutputTable = GetDataFunction( Parameter_BaseUrl, currentSessionId, currentKDSVCSessionId, Parameter_FormId, Parameter_FieldKeys, Actual_PageSize, Actual_FilterConditions, Actual_OrderString, Actual_TopRowCount, Actual_StartRow, Actual_SubSystemId, DynamicColumnNames, Actual_DistinctByColumnName ) in ResultOutputTable