pm/pkg/db/model.go

package db

import (
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"os"
	"reflect"
	"strconv"
	"strings"
	"time"
	"zhiyuan/pkg/app"
	"zhiyuan/pkg/config"
	"zhiyuan/pkg/utils"

	"github.com/gin-gonic/gin"
	"github.com/xuri/excelize/v2"
)

type JoinModel struct {
	Model Model
	As    string
	On    []string
}

type ExportField struct {
	Label string
	Name  string
	Width float64
}

type Model interface {
	TableName() string
	ListPrivilege(c *gin.Context, data map[string]interface{}, s *Select) bool
	ListSpan(model Model, list []map[string]interface{}, span []string) []map[string]interface{}
	OnePrivilege(c *gin.Context, id int64) bool
	AddPrivilege(c *gin.Context, data map[string]interface{}, post map[string]interface{}) error
	EditPrivilege(c *gin.Context, id int64, data map[string]interface{}, post map[string]interface{}) error
	DelPrivilege(c *gin.Context, id int64) error
	ListAfter(c *gin.Context, data map[string]interface{}, list []map[string]interface{}) []map[string]interface{}
	AddAfter(c *gin.Context, id int64, post map[string]interface{}, data map[string]interface{})
	EditAfter(c *gin.Context, id int64, post map[string]interface{}, data map[string]interface{})
	DelAfter(c *gin.Context, id int64)
	LeftJoin(data map[string]interface{}, s *Select) []JoinModel
	InnerJoin(data map[string]interface{}, s *Select) []JoinModel
	GroupBy() string
	Having() []string
	OrderBy() string
	Page() bool
	Count() bool
	PrimaryField() string
	CreatedField() string
	UpdatedField() string
	DeletedField() string
	OrderField() string
	ExportSpan() []string
	ExportFields() []ExportField
	ExportValue(row map[string]interface{}, rowIndex int, field ExportField, data []map[string]interface{}) string
	Export(model Model, data []map[string]interface{}, file *excelize.File) [][]string
	ExportAfter(data []map[string]interface{}, file *excelize.File)
	ExportMerge(model Model, data []map[string]interface{}, file *excelize.File)
	DB() *sql.DB
}

type BaseModel struct {
}

func (BaseModel) TableName() string {
	return ""
}

func (BaseModel) ListPrivilege(c *gin.Context, data map[string]interface{}, s *Select) bool {
	return false
}

func (BaseModel) ListSpan(model Model, list []map[string]interface{}, span []string) []map[string]interface{} {
	return listSpan(list, span, model.PrimaryField(), []string{})
}

func (BaseModel) OnePrivilege(c *gin.Context, id int64) bool {
	return false
}
func (BaseModel) AddPrivilege(c *gin.Context, data map[string]interface{}, post map[string]interface{}) error {
	return nil
}

func (BaseModel) EditPrivilege(c *gin.Context, id int64, data map[string]interface{}, post map[string]interface{}) error {
	return nil
}

func (BaseModel) DelPrivilege(c *gin.Context, id int64) error {
	return nil
}

func (BaseModel) ListAfter(c *gin.Context, data map[string]interface{}, list []map[string]interface{}) []map[string]interface{} {
	return list
}

func (BaseModel) AddAfter(c *gin.Context, id int64, post map[string]interface{}, data map[string]interface{}) {

}

func (BaseModel) EditAfter(c *gin.Context, id int64, post map[string]interface{}, data map[string]interface{}) {

}

func (BaseModel) DelAfter(c *gin.Context, id int64) {

}

func (BaseModel) LeftJoin(data map[string]interface{}, s *Select) []JoinModel {
	return []JoinModel{}
}

func (BaseModel) InnerJoin(data map[string]interface{}, s *Select) []JoinModel {
	return []JoinModel{}
}

func (BaseModel) GroupBy() string {
	return ""
}

func (BaseModel) Having() []string {
	return []string{}
}

func (BaseModel) OrderBy() string {
	return ""
}

func (BaseModel) Page() bool {
	return false
}

func (BaseModel) Count() bool {
	return false
}

func (BaseModel) PrimaryField() string {
	return "id"
}

func (BaseModel) CreatedField() string {
	return "created_at"
}

func (BaseModel) UpdatedField() string {
	return "updated_at"
}

func (BaseModel) DeletedField() string {
	return "deleted_at"
}

func (BaseModel) OrderField() string {
	return ""
}

func (BaseModel) ExportSpan() []string {
	return []string{}
}

func (BaseModel) ExportFields() []ExportField {
	return []ExportField{}
}

func (BaseModel) ExportValue(row map[string]interface{}, rowIndex int, field ExportField, data []map[string]interface{}) string {
	return ToString(row[field.Name])
}

func (BaseModel) Export(model Model, data []map[string]interface{}, file *excelize.File) [][]string {
	rows := make([][]string, 0)
	fields := model.ExportFields()

	header := make([]string, 0)
	for i, field := range fields {
		header = append(header, field.Label)
		if field.Width != 0 {
			col, _ := excelize.ColumnNumberToName(i + 1)
			file.SetColWidth("Sheet1", col, col, field.Width)
		}
	}
	rows = append(rows, header)

	for i, v := range data {
		row := make([]string, 0)
		for _, field := range fields {
			row = append(row, model.ExportValue(v, i, field, data))
		}
		rows = append(rows, row)
	}

	return rows
}

func (BaseModel) ExportMerge(model Model, data []map[string]interface{}, file *excelize.File) {
	fields := model.ExportFields()
	for n, field := range fields {
		span := ".span"
		prefix := strings.Split(field.Name, ".")
		prefix = prefix[0 : len(prefix)-1]
		if len(prefix) != 0 {
			span = span + "." + strings.Join(prefix, ".")
		}
		last := int64(-1)
		start := -1
		for m, row := range data {
			if s, ok := ToInt64(row[span]); ok {
				if s != last {
					if start >= 0 {
						cell1, _ := excelize.CoordinatesToCellName(n+1, start+2)
						cell2, _ := excelize.CoordinatesToCellName(n+1, m+1)
						if cell1 != cell2 {
							file.MergeCell("Sheet1", cell1, cell2)
						}
					}
					start = m
				}
				last = s
			}
		}
		if start >= 0 {
			cell1, _ := excelize.CoordinatesToCellName(n+1, start+2)
			cell2, _ := excelize.CoordinatesToCellName(n+1, len(data)+1)
			if cell1 != cell2 {
				file.MergeCell("Sheet1", cell1, cell2)
			}
		}
	}
}

func (BaseModel) ExportAfter(data []map[string]interface{}, file *excelize.File) {

}

func (BaseModel) DB() *sql.DB {
	return nil
}

func ToString(value interface{}) string {
	switch val := value.(type) {
	case string:
		return val
	case float32:
		return strconv.FormatFloat(float64(val), 'f', -1, 64)
	case float64:
		return strconv.FormatFloat(val, 'f', -1, 64)
	case int:
		return strconv.FormatInt(int64(val), 10)
	case uint:
		return strconv.FormatUint(uint64(val), 10)
	case int8:
		return strconv.FormatInt(int64(val), 10)
	case uint8:
		return strconv.FormatUint(uint64(val), 10)
	case int16:
		return strconv.FormatInt(int64(val), 10)
	case uint16:
		return strconv.FormatUint(uint64(val), 10)
	case int32:
		return strconv.FormatInt(int64(val), 10)
	case uint32:
		return strconv.FormatUint(uint64(val), 10)
	case int64:
		return strconv.FormatInt(val, 10)
	case uint64:
		return strconv.FormatUint(uint64(val), 10)
	case json.Number:
		return value.(json.Number).String()
	case []interface{}:
		s := make([]string, 0)
		for _, v := range val {
			s = append(s, ToString(v))
		}
		return strings.Join(s, ",")
	}
	return ""
}

func ToInt64(value interface{}) (int64, bool) {
	switch val := value.(type) {
	case string:
		if ret, err := strconv.ParseInt(val, 10, 64); err == nil {
			return ret, true
		}
	case float32:
		return int64(val), true
	case float64:
		return int64(val), true
	case int:
		return int64(val), true
	case uint:
		return int64(val), true
	case int8:
		return int64(val), true
	case uint8:
		return int64(val), true
	case int16:
		return int64(val), true
	case uint16:
		return int64(val), true
	case int32:
		return int64(val), true
	case uint32:
		return int64(val), true
	case int64:
		return val, true
	case uint64:
		return int64(val), true
	case json.Number:
		if ret, err := value.(json.Number).Int64(); err == nil {
			return ret, true
		}
	}
	return 0, false
}

func ToFloat64(value interface{}) (float64, bool) {
	switch val := value.(type) {
	case string:
		if ret, err := strconv.ParseFloat(val, 64); err == nil {
			return ret, true
		}
	case float32:
		return float64(val), true
	case float64:
		return val, true
	case int:
		return float64(val), true
	case uint:
		return float64(val), true
	case int8:
		return float64(val), true
	case uint8:
		return float64(val), true
	case int16:
		return float64(val), true
	case uint16:
		return float64(val), true
	case int32:
		return float64(val), true
	case uint32:
		return float64(val), true
	case int64:
		return float64(val), true
	case uint64:
		return float64(val), true
	case json.Number:
		if ret, err := value.(json.Number).Float64(); err == nil {
			return ret, true
		}
	}
	return 0, false
}

func ToBool(value interface{}) bool {
	switch val := value.(type) {
	case bool:
		return val
	case string:
		if val != "" {
			return true
		}
	case float32:
		if val != 0 {
			return true
		}
	case float64:
		if val != 0 {
			return true
		}
	case int:
		if val != 0 {
			return true
		}
	case uint:
		if val != 0 {
			return true
		}
	case int8:
		if val != 0 {
			return true
		}
	case uint8:
		if val != 0 {
			return true
		}
	case int16:
		if val != 0 {
			return true
		}
	case uint16:
		if val != 0 {
			return true
		}
	case int32:
		if val != 0 {
			return true
		}
	case uint32:
		if val != 0 {
			return true
		}
	case int64:
		if val != 0 {
			return true
		}
	case uint64:
		if val != 0 {
			return true
		}
	case json.Number:
		if ret, err := val.Float64(); err == nil {
			if ret != 0 {
				return true
			}
		}
	case []interface{}:
		if len(val) != 0 {
			return true
		}
	case map[string]interface{}:
		if len(val) != 0 {
			return true
		}
	case interface{}:
		if val != nil {
			return true
		}
	}
	return false
}

func ToArray(value interface{}) ([]interface{}, bool) {
	ret, ok := value.([]interface{})
	for i, v := range ret {
		switch val := v.(type) {
		case string:
			ret[i] = val
		case float32, float64, int, uint, int8, uint8, int16, uint16, int32, uint32, int64, uint64, json.Number:
			if ret[i], ok = ToFloat64(v); !ok {
				return nil, false
			}
		case []interface{}:
			if ret[i], ok = ToArray(v); !ok {
				return nil, false
			}
		default:
			return nil, false
		}
	}
	return ret, ok
}

func fieldValue(typ string, value interface{}, search bool, edit bool) interface{} {
	switch typ {
	case "byte":
		if ret := []byte(ToString(value)); !search || len(ret) != 0 {
			return ret
		}
	case "string":
		if ret := ToString(value); !search || ret != "" {
			return ret
		}
	case "int":
		if ret, ok := ToInt64(value); ok {
			return ret
		} else if edit {
			return 0
		}
	case "float":
		if ret, ok := ToFloat64(value); ok {
			return ret
		} else if edit {
			return 0
		}
	}
	return nil
}

func getProp(tag reflect.StructTag) map[string]string {
	ret := make(map[string]string)
	prop := tag.Get("prop")
	for _, p := range strings.Split(prop, " ") {
		split := strings.Split(p, ":")
		if len(split) == 0 || split[0] == "" {
			continue
		} else if len(split) == 1 {
			ret[split[0]] = "true"
		} else {
			ret[split[0]] = strings.Replace(split[1], "~", " ", -1)
		}
	}
	return ret
}

func selectFields(typ reflect.Type, data map[string]interface{}, s *Select, getall bool, having bool) {
	for n := 0; n < typ.NumField(); n++ {
		fieldType := typ.Field(n)
		key := fieldType.Tag.Get("json")
		if key == "" {
			if fieldType.Type.Kind() == reflect.Struct && fieldType.Name == fieldType.Type.Name() {
				if _, ok := reflect.New(fieldType.Type).Interface().(Model); ok {
					selectFields(fieldType.Type, data, s, getall, having)
				}
			}
			continue
		}
		prop := getProp(fieldType.Tag)
		if _, ok := prop["ignore"]; ok {
			continue
		}
		table := fieldType.Tag.Get("table")
		if table == "" {
			table = s.TableName
		}
		sel := fmt.Sprintf("`%s`.`%s`", table, key)
		if prop["select"] != "false" && prop["select"] != "true" && prop["select"] != "" {
			sel = prop["select"]
		}
		if getall || prop["select"] != "false" {
			s.Select[key] = sel
		}
		if prop["select"] == "false" {
			if _, ok := s.Select[key]; ok {
				delete(s.Select, key)
			}
		}
		search := fieldType.Tag.Get("search")
		if search != "" {
			request := fieldType.Tag.Get("request")
			if request == "" {
				request = key
			}
			if _, ok := data[request]; ok {
				where := ""
				if search == "like" {
					if str := ToString(data[request]); str != "" {
						where = "("
						for i, ss := range strings.Split(str, " ") {
							if i != 0 {
								where += " OR "
							}
							where += fmt.Sprintf("%s LIKE %s", sel, s.Param(fmt.Sprintf("%%%s%%", ss)))
						}
						where += ")"
					}
				} else if search == "rightlike" {
					if str := ToString(data[request]); str != "" {
						where = fmt.Sprintf("%s LIKE %s", sel, s.Param(fmt.Sprintf("%s%%", str)))
					}
				} else if search == "find_in_set" {
					if str := ToString(data[request]); str != "" {
						where = fmt.Sprintf("FIND_IN_SET(%s, %s)", s.Param(str), sel)
					}
				} else if search == "multiple" {
					if str := ToString(data[request]); str != "" {
						s.Where = append(s.Where, fmt.Sprintf("FIND_IN_SET(%s, %s)", sel, s.Param(str)))
					}
				} else {
					if val := fieldValue(fieldType.Tag.Get("type"), data[request], true, false); val != nil {
						where = fmt.Sprintf("%s = %s", sel, s.Param(val))
					}
				}
				if where != "" {
					if having {
						s.Having = append(s.Having, where)
					} else {
						s.Where = append(s.Where, where)
					}
				}
			}
		}
	}
}

func addFields(typ reflect.Type, data map[string]interface{}) (map[string]interface{}, error) {
	ret := make(map[string]interface{})
	for n := 0; n < typ.NumField(); n++ {
		fieldType := typ.Field(n)
		key := fieldType.Tag.Get("json")
		if key == "" {
			if fieldType.Type.Kind() == reflect.Struct && fieldType.Name == fieldType.Type.Name() {
				if _, ok := reflect.New(fieldType.Type).Interface().(Model); ok {
					m, err := addFields(fieldType.Type, data)
					if err != nil {
						return map[string]interface{}{}, err
					}
					for k, v := range m {
						ret[k] = v
					}
				}
			}
			continue
		}
		prop := getProp(fieldType.Tag)
		if _, ok := prop["ignore"]; ok {
			continue
		}
		typ := fieldType.Tag.Get("type")
		label := fieldType.Tag.Get("label")
		if label == "" {
			label = key
		}
		if prop["add"] != "false" {
			request := fieldType.Tag.Get("request")
			if request == "" {
				request = key
			}
			if _, ok := data[request]; ok {
				val := fieldValue(typ, data[request], false, false)
				if val != nil {
					ret[key] = val
					continue
				}
			}
			if prop["add"] == "true" {
				return map[string]interface{}{}, errors.New(label + "为必填项")
			}
		}
		def := fieldType.Tag.Get("default")
		if def != "" {
			val := fieldValue(typ, def, false, false)
			if val != nil {
				ret[key] = val
			}
		}
	}
	return ret, nil
}

func editFields(typ reflect.Type, data map[string]interface{}) map[string]interface{} {
	ret := make(map[string]interface{})
	for n := 0; n < typ.NumField(); n++ {
		fieldType := typ.Field(n)
		key := fieldType.Tag.Get("json")
		if key == "" {
			if fieldType.Type.Kind() == reflect.Struct && fieldType.Name == fieldType.Type.Name() {
				if _, ok := reflect.New(fieldType.Type).Interface().(Model); ok {
					for k, v := range editFields(fieldType.Type, data) {
						ret[k] = v
					}
				}
			}
			continue
		}
		prop := getProp(fieldType.Tag)
		if _, ok := prop["ignore"]; ok {
			continue
		}
		typ := fieldType.Tag.Get("type")
		if prop["edit"] == "true" {
			request := fieldType.Tag.Get("request")
			if request == "" {
				request = key
			}
			if _, ok := data[request]; ok {
				val := fieldValue(typ, data[request], false, true)
				if val != nil {
					ret[key] = val
				}
			}
		}
	}
	return ret
}

func ModelQuery(typ reflect.Type, data map[string]interface{}, getall bool) Select {
	var s Select
	s.Select = make(map[string]string)
	model := reflect.New(typ).Interface().(Model)
	s.TableName = model.TableName()
	s.GroupBy = model.GroupBy()
	s.Having = model.Having()

	selectFields(typ, data, &s, getall, s.GroupBy != "")
	for _, v := range model.InnerJoin(data, &s) {
		s.InnerJoin = append(s.InnerJoin, Join{
			v.Model.TableName(),
			v.As,
			v.On,
		})
	}

	for _, v := range model.LeftJoin(data, &s) {
		s.LeftJoin = append(s.LeftJoin, Join{
			v.Model.TableName(),
			v.As,
			v.On,
		})
	}

	s.OrderBy = model.OrderBy()
	if s.OrderBy == "" {
		if _, ok := data["order"]; ok {
			if _, ok := data["prop"]; ok {
				if order, ok := data["order"].(string); ok {
					if prop, ok := data["prop"].(string); ok {
						soft := "ASC"
						if order == "descending" {
							soft = "DESC"
						}
						s.OrderBy = fmt.Sprintf("`%s` %s", prop, soft)
					}
				}
			}
		}
	}
	if s.OrderBy == "" {
		order := model.OrderField()
		if order != "" {
			s.OrderBy = fmt.Sprintf("`%s`.`%s` DESC", s.TableName, model.OrderField())
		}
	}
	if s.OrderBy == "" {
		s.OrderBy = fmt.Sprintf("`%s`.`%s` DESC", s.TableName, model.PrimaryField())
	}

	_, size_ok := data["size"]
	_, page_ok := data["page"]

	if !getall && (model.Page() || size_ok && page_ok) {
		var size int64 = 20
		var page int64 = 1
		if num, ok := ToInt64(data["size"]); ok {
			size = num
		}
		if num, ok := ToInt64(data["page"]); ok {
			page = num
		}
		s.Limit = size
		s.Offset = size * (page - 1)
	}

	return s
}

func GetCount(s Select, db *sql.DB) (int64, error) {
	var tmp Select
	tmp = s
	tmp.Limit = 0
	tmp.Offset = 0
	query, params := tmp.Query()
	return GetQueryCount(query, params, db)
}

func GetQueryCount(query string, params map[string]interface{}, db *sql.DB) (int64, error) {
	query = fmt.Sprintf("SELECT count(*) AS count FROM (%s) AS `query`", query)
	ret, err := QueryMap(query, params, db)
	if err != nil {
		return 0, err
	}
	count, _ := ToInt64(ret[0]["count"])
	return count, nil
}

func WhereParse(s *Select, data map[string]interface{}) {
	for k, v := range data {
		table, name, symbol := "", k, "="
		split := strings.Split(k, " ")
		if len(split) > 1 {
			name = split[0]
			symbol = split[1]
		}
		split = strings.Split(name, ".")
		if len(split) > 1 {
			table = split[0]
			name = split[1]
		}
		if table == "" {
			table = s.TableName
		}
		table = strings.Trim(table, "`")
		name = strings.Trim(name, "`")
		if symbol == "find_in_set" {
			s.Where = append(s.Where, fmt.Sprintf("FIND_IN_SET(%s, `%s`.`%s`)", s.Param(v), table, name))
		} else if symbol == "by" {
			if val, ok := v.(string); ok {
				switch name {
				case "group":
					s.GroupBy = val
				case "order":
					s.OrderBy = val
				}
			}
		} else if name == "" {
			s.Where = append(s.Where, ToString(v))
		} else {
			s.Where = append(s.Where, fmt.Sprintf("`%s`.`%s` %s %s", table, name, symbol, s.Param(v)))
		}
	}
}

func GetModel(data map[string]interface{}, ret interface{}) {
	typ := Type(ret)
	model := reflect.New(typ).Interface().(Model)
	s := ModelQuery(typ, map[string]interface{}{}, true)
	WhereParse(&s, data)
	query, params := s.Query()
	Query(query, params, ret, model.DB())
}

func GetModelMap(typ reflect.Type, data map[string]interface{}, fields []string) ([]map[string]interface{}, error) {
	//var s Select
	//s.Select = make(map[string]string)
	model := reflect.New(typ).Interface().(Model)
	//s.TableName = model.TableName()
	s := ModelQuery(typ, map[string]interface{}{}, true)
	if fields != nil {
		s.Select = make(map[string]string)
		for _, v := range fields {
			s.Select[v] = v
		}
	}
	WhereParse(&s, data)
	query, params := s.Query()
	return QueryMap(query, params, model.DB())
}

func GetOneModelMap(typ reflect.Type, data map[string]interface{}, fields []string) (map[string]interface{}, error) {
	list, err := GetModelMap(typ, data, fields)
	if err != nil {
		return nil, err
	}
	if len(list) == 0 {
		return nil, nil
	}
	return list[0], nil
}

func ModelList(typ reflect.Type, where map[string]interface{}, c *gin.Context) {
	var post map[string]interface{}

	model := reflect.New(typ).Interface().(Model)
	if err := c.ShouldBindJSON(&post); err != nil {
		//app.ErrorMsg(c, err.Error(), nil)
		//return
	}
	s := ModelQuery(typ, post, false)
	WhereParse(&s, where)
	deleted := model.DeletedField()
	if deleted != "" {
		s.Where = append(s.Where, fmt.Sprintf("`%s`.`%s` = 0", model.TableName(), deleted))
	}
	if !model.ListPrivilege(c, post, &s) {
		app.ErrorMsg(c, "没有权限", nil)
		return
	}
	query, params := s.Query()
	fmt.Println("query", query)
	fmt.Println("params", params)
	fmt.Println("Db", model.DB())
	list, err := QueryMap(query, params, model.DB())
	if err != nil {
		app.ErrorMsg(c, err.Error(), nil)
		return
	}
	if list == nil {
		list = make([]map[string]interface{}, 0)
	}
	list = model.ListAfter(c, post, list)
	data := gin.H{
		"list": list,
		//"query":  query,
		//"params": params,
	}
	if model.Count() {
		count, err := GetCount(s, model.DB())
		if err != nil {
			app.ErrorMsg(c, err.Error(), nil)
			return
		}
		data["count"] = count
	}
	app.Success(c, data)
}

func ModelOne(typ reflect.Type, c *gin.Context) {
	id, ok := ToInt64(c.Param("id"))
	if !ok && id <= 0 {
		app.ErrorMsg(c, "id must be a number", nil)
		return
	}

	model := reflect.New(typ).Interface().(Model)
	if !model.OnePrivilege(c, id) {
		app.ErrorMsg(c, "没有权限", nil)
		return
	}
	s := ModelQuery(typ, map[string]interface{}{}, false)
	s.Where = append(s.Where, fmt.Sprintf("`%s`.`%s` = %s", s.TableName, model.PrimaryField(), s.Param(id)))
	deleted := model.DeletedField()
	if deleted != "" {
		s.Where = append(s.Where, fmt.Sprintf("`%s`.`%s` = 0", model.TableName(), deleted))
	}
	query, params := s.Query()
	list, err := QueryMap(query, params, model.DB())
	if err != nil {
		app.ErrorMsg(c, err.Error(), nil)
		return
	}
	if len(list) == 0 {
		app.ErrorMsg(c, "没有权限", nil)
		return
	}
	app.Success(c, list[0])
}

func formatUpdate(data map[string]interface{}) map[string]interface{} {
	format := make(map[string]interface{})
	for k, v := range data {
		format["`"+k+"`"] = v
	}
	return format
}

func InsertData(model Model, data map[string]interface{}) map[string]interface{} {
	created := model.CreatedField()
	if created != "" {
		if _, ok := data[created]; !ok {
			data[created] = time.Now().Unix()
		}
	}
	updated := model.UpdatedField()
	if updated != "" {
		if _, ok := data[updated]; !ok {
			data[updated] = time.Now().Unix()
		}
	}
	order := model.OrderField()
	if order != "" {
		if _, ok := data[order]; !ok {
			data[order] = time.Now().Unix()
		}
	}
	return formatUpdate(data)
}

func InsertModel(typ reflect.Type, data map[string]interface{}) (int64, error) {
	model := reflect.New(typ).Interface().(Model)
	id, err := BuildInsert(model.TableName(), []map[string]interface{}{InsertData(model, data)}, model.DB())
	if err != nil {
		return 0, err
	}
	return id, nil
}

func InsertModels(typ reflect.Type, datas []map[string]interface{}) (int64, error) {
	model := reflect.New(typ).Interface().(Model)
	for i, data := range datas {
		datas[i] = InsertData(model, data)
	}
	id, err := BuildInsert(model.TableName(), datas, model.DB())
	if err != nil {
		return 0, err
	}
	return id, nil
}

func ModelAdd(typ reflect.Type, c *gin.Context) {
	var post map[string]interface{}

	model := reflect.New(typ).Interface().(Model)
	if err := c.ShouldBindJSON(&post); err != nil {
		//app.ErrorMsg(c, err.Error(), nil)
		//return
	}
	data, err := addFields(typ, post)
	if err != nil {
		app.ErrorMsg(c, err.Error(), nil)
		return
	}
	err = model.AddPrivilege(c, data, post)
	if err != nil {
		app.ErrorMsg(c, err.Error(), nil)
		return
	}
	id, err := InsertModel(typ, data)
	if err != nil {
		app.ErrorMsg(c, err.Error(), nil)
		return
	}
	model.AddAfter(c, id, post, data)
	app.Success(c, gin.H{"id": id})
}

func UpdateModel(typ reflect.Type, id int64, data map[string]interface{}) error {
	model := reflect.New(typ).Interface().(Model)
	updated := model.UpdatedField()
	if updated != "" {
		if _, ok := data[updated]; !ok {
			data[updated] = time.Now().Unix()
		}
	}
	_, err := BuildUpdate(model.TableName(), map[string]interface{}{model.PrimaryField(): id}, formatUpdate(data), model.DB())
	return err
}

func UpdateModels(typ reflect.Type, cond map[string]interface{}, data map[string]interface{}) error {
	model := reflect.New(typ).Interface().(Model)
	updated := model.UpdatedField()
	if updated != "" {
		if _, ok := data[updated]; !ok {
			data[updated] = time.Now().Unix()
		}
	}
	_, err := BuildUpdate(model.TableName(), cond, formatUpdate(data), model.DB())
	return err
}

func ModelEdit(typ reflect.Type, c *gin.Context) {
	var post map[string]interface{}

	id, ok := ToInt64(c.Param("id"))
	if !ok && id <= 0 {
		app.ErrorMsg(c, "id must be a number", nil)
		return
	}

	model := reflect.New(typ).Interface().(Model)
	if err := c.ShouldBindJSON(&post); err != nil {
		//app.ErrorMsg(c, err.Error(), nil)
		//return
	}
	data := editFields(typ, post)
	err := model.EditPrivilege(c, id, data, post)
	if err != nil {
		app.ErrorMsg(c, err.Error(), nil)
		return
	}
	if len(data) == 0 {
		app.Success(c, nil)
		return
	}
	err = UpdateModel(typ, id, data)
	if err != nil {
		app.ErrorMsg(c, err.Error(), nil)
		return
	}
	model.EditAfter(c, id, post, data)
	app.Success(c, nil)
}

func ModelDel(typ reflect.Type, c *gin.Context) {
	id, ok := ToInt64(c.Param("id"))
	if !ok && id <= 0 {
		app.ErrorMsg(c, "id must be a number", nil)
		return
	}

	model := reflect.New(typ).Interface().(Model)
	err := model.DelPrivilege(c, id)
	if err != nil {
		app.ErrorMsg(c, err.Error(), nil)
		return
	}
	deleted := model.DeletedField()
	if deleted == "" {
		_, err = Delete(model.TableName(), map[string]interface{}{model.PrimaryField(): id})
	} else {

		_, err = BuildUpdate(model.TableName(), map[string]interface{}{model.PrimaryField(): id}, map[string]interface{}{deleted: time.Now().Unix()}, model.DB())
	}
	if err != nil {
		app.ErrorMsg(c, err.Error(), nil)
		return
	}
	model.DelAfter(c, id)
	app.Success(c, nil)
}

func ModelOrder(typ reflect.Type, c *gin.Context) {
	var post map[string]interface{}

	id, ok := ToInt64(c.Param("id"))
	if !ok && id <= 0 {
		app.ErrorMsg(c, "id must be a number", nil)
		return
	}

	model := reflect.New(typ).Interface().(Model)
	if err := c.ShouldBindJSON(&post); err != nil {
		//app.ErrorMsg(c, err.Error(), nil)
		//return
	}
	order := model.OrderField()
	if order == "" {
		app.ErrorMsg(c, "没有权限", nil)
		return
	}

	one, err := GetOneModelMap(typ, map[string]interface{}{model.PrimaryField(): id}, []string{order})
	if err != nil || one == nil {
		app.ErrorMsg(c, "没有权限", nil)
		return
	}

	orders := "up"
	if _, ok := post["order"]; ok {
		if _, ok := post["order"].(string); ok {
			orders = post["order"].(string)
		}
	}
	var s Select
	s.TableName = model.TableName()
	s.Select = make(map[string]string)
	selectFields(typ, post, &s, true, false)
	deleted := model.DeletedField()
	if deleted != "" {
		s.Where = append(s.Where, fmt.Sprintf("`%s`.`%s` = 0", model.TableName(), deleted))
	}
	s.Select = map[string]string{
		model.PrimaryField(): model.PrimaryField(),
		order:                order,
	}
	s.Limit = 1
	switch orders {
	case "start":
		s.OrderBy = fmt.Sprintf("`%s` DESC", order)
	case "end":
		s.OrderBy = fmt.Sprintf("`%s` ASC", order)
	case "down":
		s.Where = append(s.Where, fmt.Sprintf("`%s` < %s", order, s.Param(one[order])))
		s.OrderBy = fmt.Sprintf("`%s` DESC", order)
	default:
		s.Where = append(s.Where, fmt.Sprintf("`%s` > %s", order, s.Param(one[order])))
		s.OrderBy = fmt.Sprintf("`%s` ASC", order)
	}
	query, params := s.Query()
	list, err := QueryMap(query, params, model.DB())
	if err != nil {
		app.ErrorMsg(c, err.Error(), nil)
		return
	}
	if len(list) == 0 {
		app.Success(c, nil)
		return
	}
	two := list[0]
	oneData := map[string]interface{}{
		order: two[order],
	}
	twoData := map[string]interface{}{
		order: one[order],
	}
	o, _ := ToInt64(two[order])
	switch orders {
	case "start":
		oneData[order] = o + 1
	case "end":
		oneData[order] = o - 1
	}
	err = model.EditPrivilege(c, id, oneData, post)
	if err != nil {
		app.ErrorMsg(c, err.Error(), nil)
		return
	}
	if orders != "start" && orders != "end" {
		err = model.EditPrivilege(c, two[model.PrimaryField()].(int64), twoData, post)
		if err != nil {
			app.ErrorMsg(c, err.Error(), nil)
			return
		}
	}
	updated := model.UpdatedField()
	if updated != "" {
		oneData[updated] = time.Now().Unix()
		twoData[updated] = time.Now().Unix()
	}
	_, err = BuildUpdate(model.TableName(), map[string]interface{}{model.PrimaryField(): id}, formatUpdate(oneData), model.DB())
	if err != nil {
		app.ErrorMsg(c, err.Error(), nil)
		return
	}
	if orders != "start" && orders != "end" {
		_, err = BuildUpdate(model.TableName(), map[string]interface{}{model.PrimaryField(): two[model.PrimaryField()]}, formatUpdate(twoData), model.DB())
		if err != nil {
			app.ErrorMsg(c, err.Error(), nil)
			return
		}
	}
	app.Success(c, nil)
}

func ModelExport(typ reflect.Type, where map[string]interface{}, c *gin.Context) {
	var post map[string]interface{}

	model := reflect.New(typ).Interface().(Model)
	if err := c.ShouldBindJSON(&post); err != nil {
		//app.ErrorMsg(c, err.Error(), nil)
		//return
	}
	s := ModelQuery(typ, post, false)
	WhereParse(&s, where)
	deleted := model.DeletedField()
	if deleted != "" {
		s.Where = append(s.Where, fmt.Sprintf("`%s`.`%s` = 0", model.TableName(), deleted))
	}
	if !model.ListPrivilege(c, post, &s) {
		app.ErrorMsg(c, "没有权限", nil)
		return
	}
	query, params := s.Query()
	list, err := QueryMap(query, params, model.DB())
	if err != nil {
		app.ErrorMsg(c, err.Error(), nil)
		return
	}
	if list == nil {
		list = make([]map[string]interface{}, 0)
	}
	list = model.ListAfter(c, post, list)
	span := model.ExportSpan()
	list = model.ListSpan(model, list, span)
	export := excelize.NewFile()
	//export := xlsx.NewFile()
	//sh, err := export.AddSheet("Sheet1")
	rows := model.Export(model, list, export)
	style, err := export.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{
			Horizontal: "center",
			Vertical:   "center",
		},
	})
	if err != nil {
		app.ErrorMsg(c, err.Error(), nil)
		return
	}
	for i, row := range rows {
		//shrow := sh.AddRow()
		//shrow.SetHeight(30)
		export.SetSheetRow("Sheet1", "A"+strconv.Itoa(i+1), &row)
		//for _, cell := range row {
		//	c := shrow.AddCell()
		//	c.Value = cell
		//}
	}
	export.SetRowStyle("Sheet1", 1, len(rows), style)
	model.ExportMerge(model, list, export)
	model.ExportAfter(list, export)
	exportFileName := utils.ToStr(time.Now().UnixNano()) + ".xlsx"
	b, err := export.WriteToBuffer()
	if err != nil {
		app.ErrorMsg(c, "导出失败", nil)
		return
	}
	f, err := os.Create(config.Cfg.App.ExportPath + exportFileName)
	if err != nil {
		app.ErrorMsg(c, "导出失败", nil)
		return
	}
	defer f.Close()
	_, _ = b.WriteTo(f)
	//if err := export.Save(config.Cfg.App.ExportPath + exportFileName); err != nil {
	//	app.ErrorMsg(c, "导出失败", nil)
	//	return
	//}
	app.Success(c, gin.H{"path": "export/" + exportFileName, "filename": exportFileName})
}

func Type(model interface{}) (typ reflect.Type) {
	typ = reflect.TypeOf(model)
	for typ.Kind() != reflect.Struct {
		typ = typ.Elem()
	}
	return
}

func listSpan(list []map[string]interface{}, span []string, primary string, prefix []string) []map[string]interface{} {
	if span == nil || len(span) == 0 {
		return list
	}

	pre := span[0]
	skey := ".span"
	lastidkey := primary
	if len(prefix) != 0 {
		pre = strings.Join(prefix, ".") + "." + pre
		skey = skey + "." + strings.Join(prefix, ".")
		lastidkey = "_" + primary + "_" + strings.Join(prefix, "_")
	}
	idkey := "_" + primary + "_" + pre

	spanlist := make([]map[string]interface{}, 0)
	for i, p := range list {
		if sublist, ok := p[pre].([]map[string]interface{}); ok {
			for n, s := range sublist {
				sub := make(map[string]interface{})
				if n == 0 {
					for k, v := range p {
						sub[k] = v
					}
				}
				for k, v := range s {
					sub[pre+"."+k] = v
				}
				sub[idkey] = ToString(p[lastidkey]) + "_" + ToString(s[primary])
				sub[skey] = i
				spanlist = append(spanlist, sub)
			}

		} else {
			//spanlist = append(spanlist, p)
		}
	}

	prefix = append(prefix, span[0])
	subspan := span[1:]

	return listSpan(spanlist, subspan, primary, prefix)
}

func Router(router *gin.RouterGroup, model Model, path string) {

	typ := Type(model)
	router.POST(path+"/:id", func(c *gin.Context) {
		ModelOne(typ, c)
	})
	router.POST(path+"/list", func(c *gin.Context) {
		ModelList(typ, map[string]interface{}{}, c)
	})
	router.POST(path+"/add", func(c *gin.Context) {
		ModelAdd(typ, c)
	})
	router.POST(path+"/edit/:id", func(c *gin.Context) {
		ModelEdit(typ, c)
	})
	router.POST(path+"/del/:id", func(c *gin.Context) {
		ModelDel(typ, c)
	})
	router.POST(path+"/export", func(c *gin.Context) {
		ModelExport(typ, map[string]interface{}{}, c)
	})
	if model.OrderField() != "" {
		router.POST(path+"/order/:id", func(c *gin.Context) {
			ModelOrder(typ, c)
		})
	}
}