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 }