eta_bridge/models/jiayue/category.go

package jiayue

import (
	"database/sql"
	"eta/eta_bridge/global"
	"fmt"
)

var (
	IndexMenuTableName       = "DICT_CATEGORY"  // 指标目录表
	IndexMenuRelateTableName = "INDEX_CATEGORY" // 指标目录关联表
)

type DictCategory struct {
	Id          int    `description:"主键"`
	ParentId    int    `description:"父级目录ID"`
	ParentName  string `description:"父级目录名称"`
	Type        string `description:"目录类型"`
	Code        string `description:"目录编码"`
	Name        string `description:"目录名称"`
	Icon        string `description:"目录图标"`
	Sorting     int    `description:"排序"`
	Description string `description:"描述"`
	UserId      int    `description:"创建用户"`
	Path        string `description:"目录全路径"`
}

type DictCategorySql struct {
	Id          int            `description:"主键" json:"ID"`
	ParentId    sql.NullInt32  `description:"父级目录ID" json:"PARENT_ID"`
	ParentName  sql.NullString `description:"父级目录名称" json:"PARENT_NAME"`
	Type        sql.NullString `description:"目录类型" json:"TYPE"`
	Code        sql.NullString `description:"目录编码" json:"CODE"`
	Name        sql.NullString `description:"目录名称" json:"NAME"`
	Icon        sql.NullString `description:"目录图标" json:"ICON"`
	Sorting     sql.NullInt32  `description:"排序" json:"SORTING"`
	Description sql.NullString `description:"描述" json:"DESCRIPTION"`
	UserId      sql.NullInt32  `description:"创建用户" json:"USER_ID"`
	Path        sql.NullString `description:"目录全路径" json:"PATH"`
}

// GetDictCategory 获取指标目录列表
func GetDictCategory(condition string, pars []interface{}, orderRule string) (categories []DictCategory, err error) {
	defer func() {
		if err != nil {
			global.LOG.Info("GetDictCategory Err:" + err.Error())
		}
	}()

	fields := "ID, PARENT_ID, PARENT_NAME, TYPE, CODE, NAME, ICON, SORTING, DESCRIPTION, USER_ID, PATH"
	querySql := fmt.Sprintf(`SELECT %s FROM %s WHERE 1=1 %s %s`, fields, IndexMenuTableName, condition, orderRule)
	order := ``
	if orderRule != "" {
		order += fmt.Sprintf(` ORDER BY %s`, orderRule)
	}
	global.LOG.Info("querySql SQL: ", querySql)

	list, e := execDictCategory(querySql, pars)
	if e != nil {
		err = fmt.Errorf("execDictCategory err: %s", e.Error())
		return
	}
	categories = list
	return
}

// GetIndexCategory 获取指标关联目录
func GetIndexCategory(condition string, pars []interface{}, orderRule string) (categories []DictCategory, err error) {
	defer func() {
		if err != nil {
			global.LOG.Info("GetIndexCategory Err:" + err.Error())
		}
	}()

	fields := "C.ID, C.PARENT_ID, C.PARENT_NAME, C.TYPE, C.CODE, C.NAME, C.ICON, C.SORTING, C.DESCRIPTION, C.USER_ID, C.PATH"
	querySql := fmt.Sprintf(`SELECT %s FROM %s C JOIN %s R ON C.ID = R.CATEGORY_ID WHERE %s %s`, fields, IndexMenuTableName, IndexMenuRelateTableName, condition, orderRule)
	order := ``
	if orderRule != "" {
		order += fmt.Sprintf(` ORDER BY %s`, orderRule)
	}
	global.LOG.Info("querySql SQL: ", querySql)

	list, e := execDictCategory(querySql, pars)
	if e != nil {
		err = fmt.Errorf("execDictCategory err: %s", e.Error())
		return
	}
	categories = list
	return
}

// execDictCategory 执行目录查询SQL
func execDictCategory(querySql string, pars []interface{}) (categories []DictCategory, err error) {
	stmt, e := global.OracleJy.Prepare(querySql)
	if e != nil {
		err = fmt.Errorf("execDictCategory Prepare err: %s", e.Error())
		return
	}
	rows, e := stmt.Query(pars...) //输入sql中对应参数的值
	if e != nil {
		err = fmt.Errorf("execDictCategory Query err: %s", e.Error())
		return
	}
	defer func() {
		_ = rows.Close()
	}()

	for rows.Next() {
		var t DictCategorySql
		e = rows.Scan(&t.Id, &t.ParentId, &t.ParentName, &t.Type, &t.Code, &t.Name, &t.Icon, &t.Sorting, &t.Description, &t.UserId, &t.Path)
		if e != nil {
			err = fmt.Errorf("execDictCategory Scan err:" + e.Error())
			return
		}
		v := DictCategory{
			Id:          t.Id,
			ParentId:    int(t.ParentId.Int32),
			ParentName:  t.ParentName.String,
			Type:        t.Type.String,
			Code:        t.Code.String,
			Name:        t.Name.String,
			Icon:        t.Icon.String,
			Sorting:     int(t.Sorting.Int32),
			Description: t.Description.String,
			UserId:      int(t.UserId.Int32),
			Path:        t.Path.String,
		}
		categories = append(categories, v)
	}
	if e = rows.Err(); e != nil {
		err = fmt.Errorf("execDictCategory rows err: %s", e.Error())
		return
	}
	defer func() {
		_ = stmt.Close()
	}()
	return
}