599 lines
15 KiB
Go
599 lines
15 KiB
Go
package system
|
||
|
||
import (
|
||
"bytes"
|
||
"context"
|
||
"encoding/json"
|
||
"errors"
|
||
"fmt"
|
||
"mime/multipart"
|
||
"net/url"
|
||
"strconv"
|
||
"strings"
|
||
"time"
|
||
)
|
||
|
||
// ExportTemplate 导出模板实体
|
||
type ExportTemplate struct {
|
||
ID uint
|
||
DBName string
|
||
Name string
|
||
TableName string
|
||
TemplateID string
|
||
TemplateInfo string
|
||
Limit *int
|
||
Order string
|
||
Conditions []*Condition
|
||
JoinTemplate []*JoinTemplate
|
||
CreatedAt time.Time
|
||
UpdatedAt time.Time
|
||
}
|
||
|
||
// Condition 条件实体
|
||
type Condition struct {
|
||
ID uint
|
||
TemplateID string
|
||
From string
|
||
Column string
|
||
Operator string
|
||
}
|
||
|
||
// JoinTemplate 关联模板实体
|
||
type JoinTemplate struct {
|
||
ID uint
|
||
TemplateID string
|
||
Joins string
|
||
Table string
|
||
On string
|
||
}
|
||
|
||
// ExportTemplateSearchReq 导出模板搜索请求
|
||
type ExportTemplateSearchReq struct {
|
||
Page int
|
||
PageSize int
|
||
Name string
|
||
TableName string
|
||
TemplateID string
|
||
StartCreatedAt *time.Time
|
||
EndCreatedAt *time.Time
|
||
}
|
||
|
||
// ExportTemplateRepo 导出模板仓储接口
|
||
type ExportTemplateRepo interface {
|
||
Create(ctx context.Context, template *ExportTemplate) error
|
||
Update(ctx context.Context, template *ExportTemplate) error
|
||
Delete(ctx context.Context, id uint) error
|
||
FindByID(ctx context.Context, id uint) (*ExportTemplate, error)
|
||
FindByTemplateID(ctx context.Context, templateID string) (*ExportTemplate, error)
|
||
GetList(ctx context.Context, req *ExportTemplateSearchReq) ([]*ExportTemplate, int64, error)
|
||
DeleteConditionsByTemplateID(ctx context.Context, templateID string) error
|
||
DeleteJoinsByTemplateID(ctx context.Context, templateID string) error
|
||
CreateConditions(ctx context.Context, conditions []*Condition) error
|
||
CreateJoins(ctx context.Context, joins []*JoinTemplate) error
|
||
// Excel 相关
|
||
GetDB(dbName string) interface{}
|
||
ExecuteQuery(ctx context.Context, dbName, sql string, args ...interface{}) ([]map[string]interface{}, error)
|
||
GetColumnTypes(ctx context.Context, dbName, tableName string) ([]string, error)
|
||
HasDeletedAtColumn(ctx context.Context, tableName string) bool
|
||
ImportData(ctx context.Context, dbName, tableName string, items []map[string]interface{}) error
|
||
}
|
||
|
||
// ExportTemplateUsecase 导出模板用例
|
||
type ExportTemplateUsecase struct {
|
||
repo ExportTemplateRepo
|
||
}
|
||
|
||
// NewExportTemplateUsecase 创建导出模板用例
|
||
func NewExportTemplateUsecase(repo ExportTemplateRepo) *ExportTemplateUsecase {
|
||
return &ExportTemplateUsecase{repo: repo}
|
||
}
|
||
|
||
// CreateExportTemplate 创建导出模板
|
||
func (uc *ExportTemplateUsecase) CreateExportTemplate(ctx context.Context, template *ExportTemplate) error {
|
||
return uc.repo.Create(ctx, template)
|
||
}
|
||
|
||
// DeleteExportTemplate 删除导出模板
|
||
func (uc *ExportTemplateUsecase) DeleteExportTemplate(ctx context.Context, id uint) error {
|
||
return uc.repo.Delete(ctx, id)
|
||
}
|
||
|
||
// DeleteExportTemplateByIds 批量删除导出模板
|
||
func (uc *ExportTemplateUsecase) DeleteExportTemplateByIds(ctx context.Context, ids []uint) error {
|
||
for _, id := range ids {
|
||
if err := uc.repo.Delete(ctx, id); err != nil {
|
||
return err
|
||
}
|
||
}
|
||
return nil
|
||
}
|
||
|
||
// UpdateExportTemplate 更新导出模板
|
||
func (uc *ExportTemplateUsecase) UpdateExportTemplate(ctx context.Context, template *ExportTemplate) error {
|
||
// 删除旧的条件和关联
|
||
if err := uc.repo.DeleteConditionsByTemplateID(ctx, template.TemplateID); err != nil {
|
||
return err
|
||
}
|
||
if err := uc.repo.DeleteJoinsByTemplateID(ctx, template.TemplateID); err != nil {
|
||
return err
|
||
}
|
||
|
||
// 保存条件
|
||
conditions := template.Conditions
|
||
template.Conditions = nil
|
||
|
||
// 保存关联
|
||
joins := template.JoinTemplate
|
||
template.JoinTemplate = nil
|
||
|
||
// 更新模板
|
||
if err := uc.repo.Update(ctx, template); err != nil {
|
||
return err
|
||
}
|
||
|
||
// 创建新的条件
|
||
if len(conditions) > 0 {
|
||
for i := range conditions {
|
||
conditions[i].ID = 0
|
||
}
|
||
if err := uc.repo.CreateConditions(ctx, conditions); err != nil {
|
||
return err
|
||
}
|
||
}
|
||
|
||
// 创建新的关联
|
||
if len(joins) > 0 {
|
||
for i := range joins {
|
||
joins[i].ID = 0
|
||
}
|
||
if err := uc.repo.CreateJoins(ctx, joins); err != nil {
|
||
return err
|
||
}
|
||
}
|
||
|
||
return nil
|
||
}
|
||
|
||
// GetExportTemplate 根据ID获取导出模板
|
||
func (uc *ExportTemplateUsecase) GetExportTemplate(ctx context.Context, id uint) (*ExportTemplate, error) {
|
||
return uc.repo.FindByID(ctx, id)
|
||
}
|
||
|
||
// GetExportTemplateList 分页获取导出模板列表
|
||
func (uc *ExportTemplateUsecase) GetExportTemplateList(ctx context.Context, req *ExportTemplateSearchReq) ([]*ExportTemplate, int64, error) {
|
||
return uc.repo.GetList(ctx, req)
|
||
}
|
||
|
||
// ExportExcel 导出Excel
|
||
func (uc *ExportTemplateUsecase) ExportExcel(ctx context.Context, templateID string, values url.Values) (*bytes.Buffer, string, error) {
|
||
params := values.Get("params")
|
||
paramsValues, err := url.ParseQuery(params)
|
||
if err != nil {
|
||
return nil, "", fmt.Errorf("解析 params 参数失败: %v", err)
|
||
}
|
||
|
||
template, err := uc.repo.FindByTemplateID(ctx, templateID)
|
||
if err != nil {
|
||
return nil, "", err
|
||
}
|
||
|
||
// 解析模板信息
|
||
var templateInfoMap = make(map[string]string)
|
||
columns, err := getJSONKeys(template.TemplateInfo)
|
||
if err != nil {
|
||
return nil, "", err
|
||
}
|
||
if err := json.Unmarshal([]byte(template.TemplateInfo), &templateInfoMap); err != nil {
|
||
return nil, "", err
|
||
}
|
||
|
||
var tableTitle []string
|
||
var selectKeyFmt []string
|
||
for _, key := range columns {
|
||
selectKeyFmt = append(selectKeyFmt, key)
|
||
tableTitle = append(tableTitle, templateInfoMap[key])
|
||
}
|
||
|
||
selects := strings.Join(selectKeyFmt, ", ")
|
||
|
||
// 构建SQL
|
||
var sb strings.Builder
|
||
sb.WriteString("SELECT ")
|
||
sb.WriteString(selects)
|
||
sb.WriteString(" FROM ")
|
||
sb.WriteString(template.TableName)
|
||
|
||
// JOIN
|
||
if len(template.JoinTemplate) > 0 {
|
||
for _, join := range template.JoinTemplate {
|
||
sb.WriteString(" ")
|
||
sb.WriteString(join.Joins)
|
||
sb.WriteString(" ")
|
||
sb.WriteString(join.Table)
|
||
sb.WriteString(" ON ")
|
||
sb.WriteString(join.On)
|
||
}
|
||
}
|
||
|
||
// WHERE
|
||
var wheres []string
|
||
var args []interface{}
|
||
|
||
// 软删除过滤
|
||
filterDeleted := paramsValues.Get("filterDeleted") == "true"
|
||
if filterDeleted {
|
||
wheres = append(wheres, fmt.Sprintf("%s.deleted_at IS NULL", template.TableName))
|
||
for _, join := range template.JoinTemplate {
|
||
if uc.repo.HasDeletedAtColumn(ctx, join.Table) {
|
||
wheres = append(wheres, fmt.Sprintf("%s.deleted_at IS NULL", join.Table))
|
||
}
|
||
}
|
||
}
|
||
|
||
// 条件
|
||
for _, condition := range template.Conditions {
|
||
value := paramsValues.Get(condition.From)
|
||
op := strings.ToUpper(strings.TrimSpace(condition.Operator))
|
||
|
||
if op == "BETWEEN" {
|
||
startValue := paramsValues.Get("start" + condition.From)
|
||
endValue := paramsValues.Get("end" + condition.From)
|
||
if startValue != "" && endValue != "" {
|
||
wheres = append(wheres, fmt.Sprintf("%s BETWEEN ? AND ?", condition.Column))
|
||
args = append(args, startValue, endValue)
|
||
}
|
||
continue
|
||
}
|
||
|
||
if value == "" {
|
||
continue
|
||
}
|
||
|
||
if op == "LIKE" {
|
||
wheres = append(wheres, fmt.Sprintf("%s LIKE ?", condition.Column))
|
||
args = append(args, "%"+value+"%")
|
||
} else if op == "IN" || op == "NOT IN" {
|
||
wheres = append(wheres, fmt.Sprintf("%s %s (?)", condition.Column, op))
|
||
args = append(args, value)
|
||
} else {
|
||
wheres = append(wheres, fmt.Sprintf("%s %s ?", condition.Column, op))
|
||
args = append(args, value)
|
||
}
|
||
}
|
||
|
||
if len(wheres) > 0 {
|
||
sb.WriteString(" WHERE ")
|
||
sb.WriteString(strings.Join(wheres, " AND "))
|
||
}
|
||
|
||
// ORDER
|
||
order := paramsValues.Get("order")
|
||
if order == "" && template.Order != "" {
|
||
order = template.Order
|
||
}
|
||
if order != "" {
|
||
sb.WriteString(" ORDER BY ")
|
||
sb.WriteString(order)
|
||
}
|
||
|
||
// LIMIT
|
||
limit := paramsValues.Get("limit")
|
||
if limit == "" && template.Limit != nil && *template.Limit != 0 {
|
||
limit = strconv.Itoa(*template.Limit)
|
||
}
|
||
if limit != "" {
|
||
sb.WriteString(" LIMIT ")
|
||
sb.WriteString(limit)
|
||
}
|
||
|
||
// OFFSET
|
||
offset := paramsValues.Get("offset")
|
||
if offset != "" {
|
||
sb.WriteString(" OFFSET ")
|
||
sb.WriteString(offset)
|
||
}
|
||
|
||
// 执行查询
|
||
tableMap, err := uc.repo.ExecuteQuery(ctx, template.DBName, sb.String(), args...)
|
||
if err != nil {
|
||
return nil, "", err
|
||
}
|
||
|
||
// 生成Excel
|
||
return uc.generateExcel(tableMap, columns, tableTitle, template)
|
||
}
|
||
|
||
// generateExcel 生成Excel文件
|
||
func (uc *ExportTemplateUsecase) generateExcel(tableMap []map[string]interface{}, columns, tableTitle []string, template *ExportTemplate) (*bytes.Buffer, string, error) {
|
||
// 简化实现：生成CSV格式
|
||
var buf bytes.Buffer
|
||
|
||
// 写入标题行
|
||
buf.WriteString(strings.Join(tableTitle, ","))
|
||
buf.WriteString("\n")
|
||
|
||
// 写入数据行
|
||
for _, row := range tableMap {
|
||
var values []string
|
||
for _, col := range columns {
|
||
col = strings.ReplaceAll(col, "\"", "")
|
||
col = strings.ReplaceAll(col, "`", "")
|
||
if len(template.JoinTemplate) > 0 {
|
||
columnAs := strings.Split(col, " as ")
|
||
if len(columnAs) > 1 {
|
||
col = strings.TrimSpace(columnAs[1])
|
||
} else {
|
||
columnArr := strings.Split(col, ".")
|
||
if len(columnArr) > 1 {
|
||
col = columnArr[1]
|
||
}
|
||
}
|
||
}
|
||
if t, ok := row[col].(time.Time); ok {
|
||
values = append(values, t.Format("2006-01-02 15:04:05"))
|
||
} else {
|
||
values = append(values, fmt.Sprintf("%v", row[col]))
|
||
}
|
||
}
|
||
buf.WriteString(strings.Join(values, ","))
|
||
buf.WriteString("\n")
|
||
}
|
||
|
||
return &buf, template.Name, nil
|
||
}
|
||
|
||
// PreviewSQL 预览SQL
|
||
func (uc *ExportTemplateUsecase) PreviewSQL(ctx context.Context, templateID string, values url.Values) (string, error) {
|
||
params := values.Get("params")
|
||
paramsValues, _ := url.ParseQuery(params)
|
||
|
||
template, err := uc.repo.FindByTemplateID(ctx, templateID)
|
||
if err != nil {
|
||
return "", err
|
||
}
|
||
|
||
// 解析模板信息
|
||
var templateInfoMap = make(map[string]string)
|
||
columns, err := getJSONKeys(template.TemplateInfo)
|
||
if err != nil {
|
||
return "", err
|
||
}
|
||
if err := json.Unmarshal([]byte(template.TemplateInfo), &templateInfoMap); err != nil {
|
||
return "", err
|
||
}
|
||
|
||
var selectKeyFmt []string
|
||
for _, key := range columns {
|
||
selectKeyFmt = append(selectKeyFmt, key)
|
||
}
|
||
selects := strings.Join(selectKeyFmt, ", ")
|
||
|
||
// 构建SQL
|
||
var sb strings.Builder
|
||
sb.WriteString("SELECT ")
|
||
sb.WriteString(selects)
|
||
sb.WriteString(" FROM ")
|
||
sb.WriteString(template.TableName)
|
||
|
||
// JOIN
|
||
if len(template.JoinTemplate) > 0 {
|
||
for _, join := range template.JoinTemplate {
|
||
sb.WriteString(" ")
|
||
sb.WriteString(join.Joins)
|
||
sb.WriteString(" ")
|
||
sb.WriteString(join.Table)
|
||
sb.WriteString(" ON ")
|
||
sb.WriteString(join.On)
|
||
}
|
||
}
|
||
|
||
// WHERE
|
||
var wheres []string
|
||
|
||
filterDeleted := false
|
||
if paramsValues != nil {
|
||
filterDeleted = paramsValues.Get("filterDeleted") == "true"
|
||
}
|
||
if filterDeleted {
|
||
wheres = append(wheres, fmt.Sprintf("%s.deleted_at IS NULL", template.TableName))
|
||
for _, join := range template.JoinTemplate {
|
||
if uc.repo.HasDeletedAtColumn(ctx, join.Table) {
|
||
wheres = append(wheres, fmt.Sprintf("%s.deleted_at IS NULL", join.Table))
|
||
}
|
||
}
|
||
}
|
||
|
||
// 条件
|
||
for _, condition := range template.Conditions {
|
||
op := strings.ToUpper(strings.TrimSpace(condition.Operator))
|
||
col := strings.TrimSpace(condition.Column)
|
||
|
||
val := ""
|
||
if paramsValues != nil {
|
||
val = paramsValues.Get(condition.From)
|
||
}
|
||
|
||
switch op {
|
||
case "BETWEEN":
|
||
startValue := ""
|
||
endValue := ""
|
||
if paramsValues != nil {
|
||
startValue = paramsValues.Get("start" + condition.From)
|
||
endValue = paramsValues.Get("end" + condition.From)
|
||
}
|
||
if startValue != "" && endValue != "" {
|
||
wheres = append(wheres, fmt.Sprintf("%s BETWEEN '%s' AND '%s'", col, startValue, endValue))
|
||
} else {
|
||
wheres = append(wheres, fmt.Sprintf("%s BETWEEN {start%s} AND {end%s}", col, condition.From, condition.From))
|
||
}
|
||
case "IN", "NOT IN":
|
||
if val != "" {
|
||
wheres = append(wheres, fmt.Sprintf("%s %s ('%s')", col, op, val))
|
||
} else {
|
||
wheres = append(wheres, fmt.Sprintf("%s %s ({%s})", col, op, condition.From))
|
||
}
|
||
case "LIKE":
|
||
if val != "" {
|
||
wheres = append(wheres, fmt.Sprintf("%s LIKE '%%%s%%'", col, val))
|
||
} else {
|
||
wheres = append(wheres, fmt.Sprintf("%s LIKE {%%%s%%}", col, condition.From))
|
||
}
|
||
default:
|
||
if val != "" {
|
||
wheres = append(wheres, fmt.Sprintf("%s %s '%s'", col, op, val))
|
||
} else {
|
||
wheres = append(wheres, fmt.Sprintf("%s %s {%s}", col, op, condition.From))
|
||
}
|
||
}
|
||
}
|
||
|
||
if len(wheres) > 0 {
|
||
sb.WriteString(" WHERE ")
|
||
sb.WriteString(strings.Join(wheres, " AND "))
|
||
}
|
||
|
||
// ORDER
|
||
order := ""
|
||
if paramsValues != nil {
|
||
order = paramsValues.Get("order")
|
||
}
|
||
if order == "" && template.Order != "" {
|
||
order = template.Order
|
||
}
|
||
if order != "" {
|
||
sb.WriteString(" ORDER BY ")
|
||
sb.WriteString(order)
|
||
}
|
||
|
||
// LIMIT/OFFSET
|
||
limitStr := ""
|
||
offsetStr := ""
|
||
if paramsValues != nil {
|
||
limitStr = paramsValues.Get("limit")
|
||
offsetStr = paramsValues.Get("offset")
|
||
}
|
||
if limitStr == "" && template.Limit != nil && *template.Limit != 0 {
|
||
limitStr = strconv.Itoa(*template.Limit)
|
||
}
|
||
|
||
limitInt := 0
|
||
offsetInt := 0
|
||
if limitStr != "" {
|
||
if v, e := strconv.Atoi(limitStr); e == nil {
|
||
limitInt = v
|
||
}
|
||
}
|
||
if offsetStr != "" {
|
||
if v, e := strconv.Atoi(offsetStr); e == nil {
|
||
offsetInt = v
|
||
}
|
||
}
|
||
|
||
if limitInt > 0 {
|
||
sb.WriteString(" LIMIT ")
|
||
sb.WriteString(strconv.Itoa(limitInt))
|
||
if offsetInt > 0 {
|
||
sb.WriteString(" OFFSET ")
|
||
sb.WriteString(strconv.Itoa(offsetInt))
|
||
}
|
||
} else if offsetInt > 0 {
|
||
sb.WriteString(" OFFSET ")
|
||
sb.WriteString(strconv.Itoa(offsetInt))
|
||
}
|
||
|
||
return sb.String(), nil
|
||
}
|
||
|
||
// ExportTemplate 导出Excel模板
|
||
func (uc *ExportTemplateUsecase) ExportTemplate(ctx context.Context, templateID string) (*bytes.Buffer, string, error) {
|
||
template, err := uc.repo.FindByTemplateID(ctx, templateID)
|
||
if err != nil {
|
||
return nil, "", err
|
||
}
|
||
|
||
var templateInfoMap = make(map[string]string)
|
||
columns, err := getJSONKeys(template.TemplateInfo)
|
||
if err != nil {
|
||
return nil, "", err
|
||
}
|
||
if err := json.Unmarshal([]byte(template.TemplateInfo), &templateInfoMap); err != nil {
|
||
return nil, "", err
|
||
}
|
||
|
||
var tableTitle []string
|
||
for _, key := range columns {
|
||
tableTitle = append(tableTitle, templateInfoMap[key])
|
||
}
|
||
|
||
// 生成CSV模板
|
||
var buf bytes.Buffer
|
||
buf.WriteString(strings.Join(tableTitle, ","))
|
||
buf.WriteString("\n")
|
||
|
||
return &buf, template.Name, nil
|
||
}
|
||
|
||
// ImportExcel 导入Excel
|
||
func (uc *ExportTemplateUsecase) ImportExcel(ctx context.Context, templateID string, file *multipart.FileHeader) error {
|
||
template, err := uc.repo.FindByTemplateID(ctx, templateID)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
|
||
src, err := file.Open()
|
||
if err != nil {
|
||
return err
|
||
}
|
||
defer src.Close()
|
||
|
||
// 简化实现：读取CSV格式
|
||
var templateInfoMap = make(map[string]string)
|
||
if err := json.Unmarshal([]byte(template.TemplateInfo), &templateInfoMap); err != nil {
|
||
return err
|
||
}
|
||
|
||
var titleKeyMap = make(map[string]string)
|
||
for key, title := range templateInfoMap {
|
||
titleKeyMap[title] = key
|
||
}
|
||
|
||
// 这里需要实际的Excel解析逻辑
|
||
return errors.New("Excel导入功能需要excelize库支持")
|
||
}
|
||
|
||
// getJSONKeys 获取JSON对象的键（保持顺序）
|
||
func getJSONKeys(jsonStr string) ([]string, error) {
|
||
var result []string
|
||
dec := json.NewDecoder(strings.NewReader(jsonStr))
|
||
|
||
// 读取开始的 {
|
||
t, err := dec.Token()
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
if t != json.Delim('{') {
|
||
return nil, errors.New("expected {")
|
||
}
|
||
|
||
for dec.More() {
|
||
// 读取键
|
||
t, err := dec.Token()
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
key, ok := t.(string)
|
||
if !ok {
|
||
return nil, errors.New("expected string key")
|
||
}
|
||
result = append(result, key)
|
||
|
||
// 跳过值
|
||
var value interface{}
|
||
if err := dec.Decode(&value); err != nil {
|
||
return nil, err
|
||
}
|
||
}
|
||
|
||
return result, nil
|
||
}
|