[
  {
    "id": "2000851983567065089",
    "createBy": "bjt",
    "createTime": "2025-12-16 16:54:22",
    "updateBy": null,
    "updateTime": null,
    "serviceId": "2000821852159709186",
    "uniqueName": "按摘要查凭证",
    "name": "anzhaiyaochapingzheng_91da19f8",
    "description": "按照摘要描述查询凭证信息",
    "visualizable": 1,
    "toolPrompt": "按照摘要描述查询凭证信息",
    "toolType": "sql",
    "datasourceId": "158",
    "sqlTemplate": "WITH query_params AS (\n    SELECT \n        {year}::INT AS target_year,\n        '{digest}' AS digest_keyword,\n        '{company_names}' AS company_names_param,\n        '{months}' AS months_param\n),\ncompany_filters AS (\n    SELECT \n        TRIM(keyword) AS company_keyword\n    FROM (\n        SELECT unnest(string_to_array(nullif((SELECT company_names_param FROM query_params LIMIT 1), ''), ',')) AS keyword\n    ) t\n    WHERE keyword IS NOT NULL AND TRIM(keyword) <> ''\n),\nmonth_filters AS (\n    SELECT \n        month::INT AS target_month\n    FROM (\n        SELECT unnest(string_to_array(nullif((SELECT months_param FROM query_params LIMIT 1), ''), ',')) AS month\n    ) t\n    WHERE month IS NOT NULL AND TRIM(month) <> ''\n)\n\nSELECT \n    c.company_name AS 公司,\n    v.dbill_date AS 日期,\n    v.csign || LPAD(v.ino_id::text, 4, '0') AS 凭证号,\n    v.cdigest AS 摘要,\n    a.ccode AS 科目编码,\n    a.ccode_name AS 科目名称,\n    v.md AS 借方,\n    v.mc AS 贷方,\n    v.cbill AS 制单人\nFROM uf_voucher v\nJOIN uf_company c ON v.company_id = c.id\nJOIN uf_account_code a ON v.account_code_id = a.id\nCROSS JOIN query_params qp\nWHERE v.bdelete = false\n  AND v.iyear = qp.target_year\n  AND v.cdigest LIKE '%' || qp.digest_keyword || '%'\n  AND (\n      NOT EXISTS (SELECT 1 FROM company_filters)\n      OR EXISTS (\n          SELECT 1 FROM company_filters cf \n          WHERE c.company_name LIKE '%' || cf.company_keyword || '%'\n      )\n  )\n  AND (\n      NOT EXISTS (SELECT 1 FROM month_filters)\n      OR v.iperiod IN (SELECT target_month FROM month_filters)\n  )\nORDER BY c.company_name, v.dbill_date DESC, v.ino_id;",
    "sqlParams": "[{\"type\":\"paragraph\",\"name\":\"digest\",\"displayName\":\"摘要关键字\",\"maxLength\":500,\"defaultValue\":\"\",\"required\":true},{\"type\":\"string\",\"name\":\"company_names\",\"displayName\":\"公司名称\",\"maxLength\":200,\"defaultValue\":\"\",\"required\":false},{\"type\":\"number\",\"name\":\"year\",\"displayName\":\"会计年度\",\"maxLength\":4,\"defaultValue\":\"2025\",\"required\":true},{\"type\":\"string\",\"name\":\"months\",\"displayName\":\"月份\",\"maxLength\":50,\"defaultValue\":\"\",\"required\":false}]",
    "resultType": "list",
    "sourceType": "ai",
    "trainingTaskId": null,
    "tableMetadataIds": "",
    "executionCount": 0,
    "visualizationConfigs": [],
    "inputJsonSchema": "{}",
    "outputJsonSchema": "{\"type\":\"object\",\"properties\":{\"data\":{\"type\":\"array\"}}}",
    "lastExecutionTime": null
  },
  {
    "id": "2000852304343240706",
    "createBy": "bjt",
    "createTime": "2025-12-16 16:55:38",
    "updateBy": null,
    "updateTime": null,
    "serviceId": "2000821852159709186",
    "uniqueName": "按科目查凭证",
    "name": "ankemuchapingzheng_6a268ea1",
    "description": "按照科目名称查询凭证信息",
    "visualizable": 1,
    "toolPrompt": "按照科目名称查询凭证信息",
    "toolType": "sql",
    "datasourceId": "158",
    "sqlTemplate": "WITH query_params AS (\n    SELECT \n        {year}::INT AS target_year,\n        '{company_names}' AS company_names_param,\n        '{subject_name}' AS subject_keyword,\n        '{months}' AS months_param\n),\ncompany_filters AS (\n    SELECT \n        TRIM(keyword) AS company_keyword\n    FROM (\n        SELECT unnest(string_to_array(nullif((SELECT company_names_param FROM query_params LIMIT 1), ''), ',')) AS keyword\n    ) t\n    WHERE keyword IS NOT NULL AND TRIM(keyword) <> ''\n),\nmonth_filters AS (\n    SELECT \n        month::INT AS target_month\n    FROM (\n        SELECT unnest(string_to_array(nullif((SELECT months_param FROM query_params LIMIT 1), ''), ',')) AS month\n    ) t\n    WHERE month IS NOT NULL AND TRIM(month) <> ''\n),\n目标科目 AS (\n    SELECT DISTINCT a.id, a.ccode\n    FROM uf_account_code a\n    JOIN uf_company c ON a.company_id = c.id\n    CROSS JOIN query_params qp\n    WHERE a.ccode_name LIKE '%' || qp.subject_keyword || '%'\n      AND a.iyear = qp.target_year\n      AND (\n          NOT EXISTS (SELECT 1 FROM company_filters)\n          OR EXISTS (\n              SELECT 1 FROM company_filters cf \n              WHERE c.company_name LIKE '%' || cf.company_keyword || '%'\n          )\n      )\n),\n相关科目 AS (\n    SELECT a.id\n    FROM uf_account_code a\n    JOIN uf_company c ON a.company_id = c.id\n    CROSS JOIN query_params qp\n    WHERE a.iyear = qp.target_year\n      AND EXISTS (\n          SELECT 1 FROM 目标科目 t \n          WHERE a.ccode LIKE t.ccode || '%'\n      )\n      AND (\n          NOT EXISTS (SELECT 1 FROM company_filters)\n          OR EXISTS (\n              SELECT 1 FROM company_filters cf \n              WHERE c.company_name LIKE '%' || cf.company_keyword || '%'\n          )\n      )\n)\nSELECT \n    c.company_name AS 公司,\n    v.dbill_date AS 日期,\n    v.csign || LPAD(v.ino_id::text, 4, '0') AS 凭证号,\n    v.cdigest AS 摘要,\n    a.ccode AS 科目编码,\n    a.ccode_name AS 科目名称,\n    a.igrade AS 级次,\n    v.md AS 借方,\n    v.mc AS 贷方\nFROM uf_voucher v\nJOIN uf_company c ON v.company_id = c.id\nJOIN uf_account_code a ON v.account_code_id = a.id\nCROSS JOIN query_params qp\nWHERE v.bdelete = false\n  AND v.account_code_id IN (SELECT id FROM 相关科目)\n  AND v.iyear = qp.target_year\n  AND (\n      NOT EXISTS (SELECT 1 FROM company_filters)\n      OR EXISTS (\n          SELECT 1 FROM company_filters cf \n          WHERE c.company_name LIKE '%' || cf.company_keyword || '%'\n      )\n  )\n  AND (\n      NOT EXISTS (SELECT 1 FROM month_filters)\n      OR v.iperiod IN (SELECT target_month FROM month_filters)\n  )\nORDER BY c.company_name, a.ccode, v.dbill_date, v.ino_id;",
    "sqlParams": "[{\"type\":\"select\",\"name\":\"subject_name\",\"displayName\":\"科目名称\",\"maxLength\":100,\"defaultValue\":\"\",\"required\":true,\"options\":[\"管理费用\",\"销售费用\",\"财务费用\",\"应收账款\",\"应付账款\"]},{\"type\":\"paragraph\",\"name\":\"company_names\",\"displayName\":\"公司名称列表\",\"maxLength\":300,\"defaultValue\":\"\",\"required\":false},{\"type\":\"number\",\"name\":\"year\",\"displayName\":\"年度\",\"maxLength\":4,\"defaultValue\":\"2025\",\"required\":true},{\"type\":\"select\",\"name\":\"months\",\"displayName\":\"期间月份\",\"maxLength\":30,\"defaultValue\":\"\",\"required\":false,\"options\":[\"1\",\"2\",\"3\",\"4\",\"5\",\"6\",\"7\",\"8\",\"9\",\"10\",\"11\",\"12\"]}]",
    "resultType": "list",
    "sourceType": "ai",
    "trainingTaskId": null,
    "tableMetadataIds": "",
    "executionCount": 0,
    "visualizationConfigs": [],
    "inputJsonSchema": "{}",
    "outputJsonSchema": "{\"type\":\"object\",\"properties\":{\"data\":{\"type\":\"array\"}}}",
    "lastExecutionTime": null
  },
  {
    "id": "2000852610904920066",
    "createBy": "bjt",
    "createTime": "2025-12-16 16:56:51",
    "updateBy": null,
    "updateTime": null,
    "serviceId": "2000821852159709186",
    "uniqueName": "按科目查余额",
    "name": "ankemuchayue_68b302c2",
    "description": "按照科目名称查询余额",
    "visualizable": 1,
    "toolPrompt": "按照科目名称查询余额",
    "toolType": "sql",
    "datasourceId": "158",
    "sqlTemplate": "WITH query_params AS (\n    SELECT \n        {year}::INT AS target_year,\n        '{company_names}' AS company_names_param,\n        '{subject_name}' AS subject_keyword,\n        '{months}' AS months_param\n),\ncompany_filters AS (\n    SELECT \n        TRIM(keyword) AS company_keyword\n    FROM (\n        SELECT unnest(string_to_array(nullif((SELECT company_names_param FROM query_params LIMIT 1), ''), ',')) AS keyword\n    ) t\n    WHERE keyword IS NOT NULL AND TRIM(keyword) <> ''\n),\nmonth_filters AS (\n    SELECT \n        month::INT AS target_month\n    FROM (\n        SELECT unnest(string_to_array(nullif((SELECT months_param FROM query_params LIMIT 1), ''), ',')) AS month\n    ) t\n    WHERE month IS NOT NULL AND TRIM(month) <> ''\n),\n目标科目 AS (\n    SELECT DISTINCT a.id, a.ccode\n    FROM uf_account_code a\n    JOIN uf_company c ON a.company_id = c.id\n    CROSS JOIN query_params qp\n    WHERE a.ccode_name LIKE '%' || qp.subject_keyword || '%'\n      AND a.iyear = qp.target_year\n      AND (\n          NOT EXISTS (SELECT 1 FROM company_filters)\n          OR EXISTS (\n              SELECT 1 FROM company_filters cf \n              WHERE c.company_name LIKE '%' || cf.company_keyword || '%'\n          )\n      )\n),\n相关科目 AS (\n    SELECT a.id\n    FROM uf_account_code a\n    JOIN uf_company c ON a.company_id = c.id\n    CROSS JOIN query_params qp\n    WHERE a.iyear = qp.target_year\n      AND EXISTS (\n          SELECT 1 FROM 目标科目 t \n          WHERE a.ccode LIKE t.ccode || '%'\n      )\n      AND (\n          NOT EXISTS (SELECT 1 FROM company_filters)\n          OR EXISTS (\n              SELECT 1 FROM company_filters cf \n              WHERE c.company_name LIKE '%' || cf.company_keyword || '%'\n          )\n      )\n)\nSELECT \n    c.company_name AS 公司,\n    a.ccode AS 科目编码,\n    a.ccode_name AS 科目名称,\n    a.igrade AS 级次,\n    a.cclass AS 类别,\n    b.iyear AS 年,\n    b.iperiod AS 月,\n    b.cbegind_c AS 期初方向,\n    b.mb AS 期初余额,\n    b.md AS 借方发生,\n    b.mc AS 贷方发生,\n    b.cendd_c AS 期末方向,\n    b.me AS 期末余额\nFROM uf_account_balance b\nJOIN uf_company c ON b.company_id = c.id\nJOIN uf_account_code a ON b.account_code_id = a.id\nCROSS JOIN query_params qp\nWHERE b.iyear = qp.target_year\n  AND b.account_code_id IN (SELECT id FROM 相关科目)\n  AND (\n      NOT EXISTS (SELECT 1 FROM company_filters)\n      OR EXISTS (\n          SELECT 1 FROM company_filters cf \n          WHERE c.company_name LIKE '%' || cf.company_keyword || '%'\n      )\n  )\n  AND (\n      NOT EXISTS (SELECT 1 FROM month_filters)\n      OR b.iperiod IN (SELECT target_month FROM month_filters)\n  )\nORDER BY c.company_name, a.ccode, b.iperiod;",
    "sqlParams": "[{\"type\":\"string\",\"name\":\"subject_name\",\"displayName\":\"科目名称关键字\",\"maxLength\":100,\"defaultValue\":\"银行存款\",\"required\":true},{\"type\":\"string\",\"name\":\"company_names\",\"displayName\":\"公司筛选\",\"maxLength\":150,\"defaultValue\":\"\",\"required\":false},{\"type\":\"number\",\"name\":\"year\",\"displayName\":\"查询年度\",\"maxLength\":4,\"defaultValue\":\"2025\",\"required\":true},{\"type\":\"number\",\"name\":\"months\",\"displayName\":\"查询月份\",\"maxLength\":2,\"defaultValue\":\"\",\"required\":false}]",
    "resultType": "list",
    "sourceType": "ai",
    "trainingTaskId": null,
    "tableMetadataIds": "",
    "executionCount": 0,
    "visualizationConfigs": [],
    "inputJsonSchema": "{}",
    "outputJsonSchema": "{\"type\":\"object\",\"properties\":{\"data\":{\"type\":\"array\"}}}",
    "lastExecutionTime": null
  }
]