432 lines
13 KiB
C#
432 lines
13 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using System.Runtime.InteropServices;
|
|
using System.Text;
|
|
using System.Text.Encodings.Web;
|
|
using System.Text.Json;
|
|
using System.Text.RegularExpressions;
|
|
using System.Threading;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace AxCopilot.Services.Agent;
|
|
|
|
public class DataPivotTool : IAgentTool
|
|
{
|
|
public string Name => "data_pivot";
|
|
|
|
public string Description => "Group, pivot, and aggregate CSV/JSON data without external dependencies. Supports: group_by columns, aggregate functions (sum/avg/count/min/max), filter conditions, sorting, and output as table/csv/json.";
|
|
|
|
public ToolParameterSchema Parameters
|
|
{
|
|
get
|
|
{
|
|
ToolParameterSchema toolParameterSchema = new ToolParameterSchema();
|
|
Dictionary<string, ToolProperty> obj = new Dictionary<string, ToolProperty>
|
|
{
|
|
["source_path"] = new ToolProperty
|
|
{
|
|
Type = "string",
|
|
Description = "Path to CSV or JSON data file."
|
|
},
|
|
["group_by"] = new ToolProperty
|
|
{
|
|
Type = "array",
|
|
Description = "Column names to group by.",
|
|
Items = new ToolProperty
|
|
{
|
|
Type = "string"
|
|
}
|
|
},
|
|
["aggregates"] = new ToolProperty
|
|
{
|
|
Type = "array",
|
|
Description = "Aggregation specs: [{\"column\": \"sales\", \"function\": \"sum\"}, ...]. Functions: sum, avg, count, min, max.",
|
|
Items = new ToolProperty
|
|
{
|
|
Type = "object"
|
|
}
|
|
},
|
|
["filter"] = new ToolProperty
|
|
{
|
|
Type = "string",
|
|
Description = "Optional filter expression: 'column == value' or 'column > 100'. Supports: ==, !=, >, <, >=, <=, contains. Multiple conditions: 'region == Seoul AND year >= 2025'."
|
|
},
|
|
["sort_by"] = new ToolProperty
|
|
{
|
|
Type = "string",
|
|
Description = "Column name to sort results by. Prefix with '-' for descending."
|
|
},
|
|
["top_n"] = new ToolProperty
|
|
{
|
|
Type = "integer",
|
|
Description = "Limit results to top N rows. Default: all rows."
|
|
}
|
|
};
|
|
ToolProperty obj2 = new ToolProperty
|
|
{
|
|
Type = "string",
|
|
Description = "Output format: table (markdown), csv, json. Default: table"
|
|
};
|
|
int num = 3;
|
|
List<string> list = new List<string>(num);
|
|
CollectionsMarshal.SetCount(list, num);
|
|
Span<string> span = CollectionsMarshal.AsSpan(list);
|
|
span[0] = "table";
|
|
span[1] = "csv";
|
|
span[2] = "json";
|
|
obj2.Enum = list;
|
|
obj["output_format"] = obj2;
|
|
toolParameterSchema.Properties = obj;
|
|
num = 1;
|
|
List<string> list2 = new List<string>(num);
|
|
CollectionsMarshal.SetCount(list2, num);
|
|
CollectionsMarshal.AsSpan(list2)[0] = "source_path";
|
|
toolParameterSchema.Required = list2;
|
|
return toolParameterSchema;
|
|
}
|
|
}
|
|
|
|
public Task<ToolResult> ExecuteAsync(JsonElement args, AgentContext context, CancellationToken ct)
|
|
{
|
|
string path = args.GetProperty("source_path").GetString() ?? "";
|
|
string text = FileReadTool.ResolvePath(path, context.WorkFolder);
|
|
if (!context.IsPathAllowed(text))
|
|
{
|
|
return Task.FromResult(ToolResult.Fail("경로 접근 차단: " + text));
|
|
}
|
|
if (!File.Exists(text))
|
|
{
|
|
return Task.FromResult(ToolResult.Fail("파일 없음: " + text));
|
|
}
|
|
try
|
|
{
|
|
string text2 = Path.GetExtension(text).ToLowerInvariant();
|
|
List<Dictionary<string, string>> list = ((!(text2 == ".json")) ? LoadCsv(text) : LoadJson(text));
|
|
if (list.Count == 0)
|
|
{
|
|
return Task.FromResult(ToolResult.Fail("데이터가 비어있습니다."));
|
|
}
|
|
int count = list.Count;
|
|
if (args.TryGetProperty("filter", out var value))
|
|
{
|
|
string text3 = value.GetString() ?? "";
|
|
if (!string.IsNullOrWhiteSpace(text3))
|
|
{
|
|
list = ApplyFilter(list, text3);
|
|
}
|
|
}
|
|
List<Dictionary<string, string>> list4;
|
|
if (args.TryGetProperty("group_by", out var value2) && value2.ValueKind == JsonValueKind.Array)
|
|
{
|
|
List<string> list2 = new List<string>();
|
|
foreach (JsonElement item2 in value2.EnumerateArray())
|
|
{
|
|
list2.Add(item2.GetString() ?? "");
|
|
}
|
|
List<(string, string)> list3 = new List<(string, string)>();
|
|
if (args.TryGetProperty("aggregates", out var value3) && value3.ValueKind == JsonValueKind.Array)
|
|
{
|
|
foreach (JsonElement item3 in value3.EnumerateArray())
|
|
{
|
|
JsonElement value4;
|
|
string text4 = (item3.TryGetProperty("column", out value4) ? (value4.GetString() ?? "") : "");
|
|
JsonElement value5;
|
|
string item = (item3.TryGetProperty("function", out value5) ? (value5.GetString() ?? "count") : "count");
|
|
if (!string.IsNullOrEmpty(text4))
|
|
{
|
|
list3.Add((text4, item));
|
|
}
|
|
}
|
|
}
|
|
list4 = GroupAndAggregate(list, list2, list3);
|
|
}
|
|
else
|
|
{
|
|
list4 = list;
|
|
}
|
|
if (args.TryGetProperty("sort_by", out var value6))
|
|
{
|
|
string text5 = value6.GetString() ?? "";
|
|
if (!string.IsNullOrWhiteSpace(text5))
|
|
{
|
|
list4 = ApplySort(list4, text5);
|
|
}
|
|
}
|
|
if (args.TryGetProperty("top_n", out var value7) && value7.TryGetInt32(out var value8) && value8 > 0)
|
|
{
|
|
list4 = list4.Take(value8).ToList();
|
|
}
|
|
JsonElement value9;
|
|
string format = (args.TryGetProperty("output_format", out value9) ? (value9.GetString() ?? "table") : "table");
|
|
string value10 = FormatOutput(list4, format);
|
|
return Task.FromResult(ToolResult.Ok($"\ud83d\udcca 데이터 피벗 완료: {count}행 → 필터 후 {list.Count}행 → 결과 {list4.Count}행\n\n{value10}"));
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return Task.FromResult(ToolResult.Fail("데이터 피벗 실패: " + ex.Message));
|
|
}
|
|
}
|
|
|
|
private static List<Dictionary<string, string>> LoadCsv(string path)
|
|
{
|
|
string[] array = File.ReadAllLines(path, Encoding.UTF8);
|
|
if (array.Length < 2)
|
|
{
|
|
return new List<Dictionary<string, string>>();
|
|
}
|
|
List<string> list = ParseCsvLine(array[0]);
|
|
List<Dictionary<string, string>> list2 = new List<Dictionary<string, string>>();
|
|
for (int i = 1; i < array.Length; i++)
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(array[i]))
|
|
{
|
|
List<string> list3 = ParseCsvLine(array[i]);
|
|
Dictionary<string, string> dictionary = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
|
|
for (int j = 0; j < list.Count && j < list3.Count; j++)
|
|
{
|
|
dictionary[list[j]] = list3[j];
|
|
}
|
|
list2.Add(dictionary);
|
|
}
|
|
}
|
|
return list2;
|
|
}
|
|
|
|
private static List<string> ParseCsvLine(string line)
|
|
{
|
|
List<string> list = new List<string>();
|
|
StringBuilder stringBuilder = new StringBuilder();
|
|
bool flag = false;
|
|
foreach (char c in line)
|
|
{
|
|
switch (c)
|
|
{
|
|
case '"':
|
|
flag = !flag;
|
|
continue;
|
|
case ',':
|
|
if (!flag)
|
|
{
|
|
list.Add(stringBuilder.ToString().Trim());
|
|
stringBuilder.Clear();
|
|
continue;
|
|
}
|
|
break;
|
|
}
|
|
stringBuilder.Append(c);
|
|
}
|
|
list.Add(stringBuilder.ToString().Trim());
|
|
return list;
|
|
}
|
|
|
|
private static List<Dictionary<string, string>> LoadJson(string path)
|
|
{
|
|
string json = File.ReadAllText(path, Encoding.UTF8);
|
|
JsonDocument jsonDocument = JsonDocument.Parse(json);
|
|
List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
|
|
JsonElement value;
|
|
JsonElement jsonElement = ((jsonDocument.RootElement.ValueKind == JsonValueKind.Array) ? jsonDocument.RootElement : (jsonDocument.RootElement.TryGetProperty("data", out value) ? value : jsonDocument.RootElement));
|
|
if (jsonElement.ValueKind != JsonValueKind.Array)
|
|
{
|
|
return list;
|
|
}
|
|
foreach (JsonElement item in jsonElement.EnumerateArray())
|
|
{
|
|
Dictionary<string, string> dictionary = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
|
|
foreach (JsonProperty item2 in item.EnumerateObject())
|
|
{
|
|
dictionary[item2.Name] = item2.Value.ToString();
|
|
}
|
|
list.Add(dictionary);
|
|
}
|
|
return list;
|
|
}
|
|
|
|
private static List<Dictionary<string, string>> ApplyFilter(List<Dictionary<string, string>> data, string filter)
|
|
{
|
|
string[] array = filter.Split(new string[2] { " AND ", " and " }, StringSplitOptions.TrimEntries);
|
|
List<Dictionary<string, string>> list = data;
|
|
string[] array2 = array;
|
|
foreach (string input in array2)
|
|
{
|
|
Match match = Regex.Match(input, "(\\w+)\\s*(==|!=|>=|<=|>|<|contains)\\s*(.+)");
|
|
if (!match.Success)
|
|
{
|
|
continue;
|
|
}
|
|
string col = match.Groups[1].Value;
|
|
string op = match.Groups[2].Value;
|
|
string val = match.Groups[3].Value.Trim().Trim('\'', '"');
|
|
list = list.Where(delegate(Dictionary<string, string> row)
|
|
{
|
|
if (!row.TryGetValue(col, out var value))
|
|
{
|
|
return false;
|
|
}
|
|
if (1 == 0)
|
|
{
|
|
}
|
|
double result2;
|
|
double result3;
|
|
double result4;
|
|
double result5;
|
|
double result6;
|
|
double result7;
|
|
double result8;
|
|
double result9;
|
|
bool result = op switch
|
|
{
|
|
"==" => value.Equals(val, StringComparison.OrdinalIgnoreCase),
|
|
"!=" => !value.Equals(val, StringComparison.OrdinalIgnoreCase),
|
|
"contains" => value.Contains(val, StringComparison.OrdinalIgnoreCase),
|
|
">" => double.TryParse(value, out result2) && double.TryParse(val, out result3) && result2 > result3,
|
|
"<" => double.TryParse(value, out result4) && double.TryParse(val, out result5) && result4 < result5,
|
|
">=" => double.TryParse(value, out result6) && double.TryParse(val, out result7) && result6 >= result7,
|
|
"<=" => double.TryParse(value, out result8) && double.TryParse(val, out result9) && result8 <= result9,
|
|
_ => true,
|
|
};
|
|
if (1 == 0)
|
|
{
|
|
}
|
|
return result;
|
|
}).ToList();
|
|
}
|
|
return list;
|
|
}
|
|
|
|
private static List<Dictionary<string, string>> GroupAndAggregate(List<Dictionary<string, string>> data, List<string> groupCols, List<(string Column, string Function)> aggregates)
|
|
{
|
|
IEnumerable<IGrouping<string, Dictionary<string, string>>> enumerable = data.GroupBy(delegate(Dictionary<string, string> row)
|
|
{
|
|
StringBuilder stringBuilder = new StringBuilder();
|
|
foreach (string groupCol in groupCols)
|
|
{
|
|
row.TryGetValue(groupCol, out var value2);
|
|
stringBuilder.Append(value2 ?? "").Append('|');
|
|
}
|
|
return stringBuilder.ToString();
|
|
});
|
|
List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
|
|
foreach (IGrouping<string, Dictionary<string, string>> item2 in enumerable)
|
|
{
|
|
Dictionary<string, string> dictionary = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
|
|
Dictionary<string, string> dictionary2 = item2.First();
|
|
foreach (string groupCol2 in groupCols)
|
|
{
|
|
dictionary[groupCol2] = (dictionary2.TryGetValue(groupCol2, out var value) ? value : "");
|
|
}
|
|
foreach (var aggregate in aggregates)
|
|
{
|
|
string aggCol = aggregate.Column;
|
|
string item = aggregate.Function;
|
|
string value2;
|
|
double result;
|
|
List<double> list2 = (from r in item2
|
|
select r.TryGetValue(aggCol, out value2) ? value2 : "" into v
|
|
where double.TryParse(v, out result)
|
|
select double.Parse(v)).ToList();
|
|
string text = item.ToLowerInvariant();
|
|
if (1 == 0)
|
|
{
|
|
}
|
|
double num;
|
|
switch (text)
|
|
{
|
|
case "sum":
|
|
num = list2.Sum();
|
|
break;
|
|
case "avg":
|
|
case "average":
|
|
num = ((list2.Count > 0) ? list2.Average() : 0.0);
|
|
break;
|
|
case "min":
|
|
num = ((list2.Count > 0) ? list2.Min() : 0.0);
|
|
break;
|
|
case "max":
|
|
num = ((list2.Count > 0) ? list2.Max() : 0.0);
|
|
break;
|
|
case "count":
|
|
num = item2.Count();
|
|
break;
|
|
default:
|
|
num = item2.Count();
|
|
break;
|
|
}
|
|
if (1 == 0)
|
|
{
|
|
}
|
|
double num2 = num;
|
|
string key = aggCol + "_" + item;
|
|
dictionary[key] = ((item == "count") ? ((int)num2).ToString() : num2.ToString("F2"));
|
|
}
|
|
if (aggregates.Count == 0)
|
|
{
|
|
dictionary["count"] = item2.Count().ToString();
|
|
}
|
|
list.Add(dictionary);
|
|
}
|
|
return list;
|
|
}
|
|
|
|
private static List<Dictionary<string, string>> ApplySort(List<Dictionary<string, string>> data, string sortBy)
|
|
{
|
|
bool flag = sortBy.StartsWith('-');
|
|
string col = sortBy.TrimStart('-');
|
|
return (flag ? data.OrderByDescending((Dictionary<string, string> r) => GetSortKey(r, col)) : data.OrderBy((Dictionary<string, string> r) => GetSortKey(r, col))).ToList();
|
|
}
|
|
|
|
private static object GetSortKey(Dictionary<string, string> row, string col)
|
|
{
|
|
if (!row.TryGetValue(col, out string value))
|
|
{
|
|
return "";
|
|
}
|
|
if (double.TryParse(value, out var result))
|
|
{
|
|
return result;
|
|
}
|
|
return value;
|
|
}
|
|
|
|
private static string FormatOutput(List<Dictionary<string, string>> data, string format)
|
|
{
|
|
if (data.Count == 0)
|
|
{
|
|
return "(결과 없음)";
|
|
}
|
|
List<string> list = data.SelectMany((Dictionary<string, string> r) => r.Keys).Distinct().ToList();
|
|
if (!(format == "json"))
|
|
{
|
|
if (format == "csv")
|
|
{
|
|
StringBuilder stringBuilder = new StringBuilder();
|
|
stringBuilder.AppendLine(string.Join(",", list));
|
|
foreach (Dictionary<string, string> row in data)
|
|
{
|
|
string value;
|
|
IEnumerable<string> values = list.Select((string c) => row.TryGetValue(c, out value) ? ("\"" + value + "\"") : "\"\"");
|
|
stringBuilder.AppendLine(string.Join(",", values));
|
|
}
|
|
return stringBuilder.ToString();
|
|
}
|
|
StringBuilder stringBuilder2 = new StringBuilder();
|
|
stringBuilder2.AppendLine("| " + string.Join(" | ", list) + " |");
|
|
stringBuilder2.AppendLine("| " + string.Join(" | ", list.Select((string _) => "---")) + " |");
|
|
foreach (Dictionary<string, string> row2 in data)
|
|
{
|
|
string value;
|
|
IEnumerable<string> values2 = list.Select((string c) => row2.TryGetValue(c, out value) ? value : "");
|
|
stringBuilder2.AppendLine("| " + string.Join(" | ", values2) + " |");
|
|
}
|
|
return stringBuilder2.ToString();
|
|
}
|
|
return JsonSerializer.Serialize(data, new JsonSerializerOptions
|
|
{
|
|
WriteIndented = true,
|
|
Encoder = JavaScriptEncoder.UnsafeRelaxedJsonEscaping
|
|
});
|
|
}
|
|
}
|