MTP20_IF/models/ermcpAccMgr.go

/**
* @Author: zou.yingbin
* @Create  : 2021/3/30 13:38
* @Modify  : 2021/3/30 13:38
* @note    : 账户管理
**/

package models

import (
	"fmt"
	"mtp2_if/db"
	"mtp2_if/mtpcache"
	"mtp2_if/utils"
	"strconv"
	"strings"
)

// ErmcpLoginUserEx 登录账号(按角色)
type ErmcpLoginUserEx struct {
	ROLENAME      string           `json:"rolename"`                              // 角色名称(交易账户->用户名称)
	USERTYPE      int32            `json:"usertype"`                              // 用户类型 2-机构 7-企业成员(云平台)
	UserList      []ErmcpLoginUser `json:"userlist"`                              // 用户列表
	RoleId        int64            `json:"roleid"`                                // 角色id(交易账户->用户id)
	MOBILE        string           `json:"mobile"  xorm:"'MOBILE'"`               // 手机号
	ACCOUNTSTATUS int32            `json:"accountstatus"  xorm:"'ACCOUNTSTATUS'"` // 账户的状态 - 1:待激活 2:待审核 3:待复审 4:正常 5:审核拒绝 6:停用(注销) 7:注销(删除)
	QUERYTYPE     int32            `json:"querytype"`                             // 查询类型 1-业务账户 2-交易账户 3-管理账户
}

// ErmcpLoginUser 查询登录账户
type ErmcpLoginUser struct {
	USERID        int64                 `json:"userid"  xorm:"'USERID'"`             // 用户id
	MEMBERUSERID  int64                 `json:"memberuserid"  xorm:"'MEMBERUSERID'"` // 所属会员id
	ACCOUNTNAME   string                `json:"accountname"  xorm:"'ACCOUNTNAME'"`   // 用户名称
	USERTYPE      int32                 `json:"usertype"  xorm:"'USERTYPE'"`         // 用户类型 -  1:交易所 2:机构 3:会员子机构 4:经纪人 5:投资者 6:客户 7:企业成员(云平台)
	ACCOUNTSTATUS int32                 `json:"-"  xorm:"'ACCOUNTSTATUS'"`           // 所属账户的状态 - 1:待激活 2:待审核 3:待复审 4:正常 5:审核拒绝 6:停用(注销) 7:注销(删除)
	LOGINSTATUS   int32                 `json:"loginstatus"  xorm:"'LOGINSTATUS'"`   // 登录账户状态 - 1:正常 2:冻结 3:无效
	CREATETIME    string                `json:"createtime"  xorm:"'CREATETIME'"`     // 创建时间
	MODIFYTIME    string                `json:"modifytime"  xorm:"'MODIFYTIME'"`     // 修改时间
	ROLETYPE      string                `json:"roletype"  xorm:"'ROLETYPE'"`         // 角色类型(逗号隔开,如22,23), 22:业务员 23:跟单员 24:交易员
	CLIENTROLEID  int32                 `json:"clientroleid"  xorm:"'CLIENTROLEID'"` // 角色id(usertype=2)
	ROLENAME      string                `json:"rolename"  xorm:"'ROLENAME'"`         // 角色名称
	ROLESTATUS    int32                 `json:"rolestatus"  xorm:"'ROLESTATUS'"`     // 角色状态 1-启用 2-停用
	LOGINCODE     string                `json:"logincode"  xorm:"'LOGINCODE'"`       // 登录代码
	LOGINID       int64                 `json:"loginid"  xorm:"'LOGINID'"`           // 登录id(LOGINID)
	PASSWORD      string                `json:"password"  xorm:"'PASSWORD'"`         // 密码(不可解密)
	MOBILE        string                `json:"mobile"  xorm:"'MOBILE'"`             // 手机号
	LOGINNAME     string                `json:"loginname"  xorm:"'LOGINNAME'"`       // 登录名称(loginaccount表中的accountname字段)
	USERMOBILE    string                `json:"-"  xorm:"'usermobile'"`              // 所属用户手机号码
	AccList       []ErmcpLoginTaAccount `json:"acclist"`                             // 关联资金账号

	QueryType int32 `json:"-"` // 过滤条件 查询类型 1-业务账户 2-交易账户 3-管理账户
}

func (r *ErmcpLoginUser) addAcc(accLst []ErmcpLoginTaAccount) {
	for i := range accLst {
		if accLst[i].LOGINID == r.LOGINID {
			r.AccList = append(r.AccList, accLst[i])
		}
	}
}

func (r *ErmcpLoginUser) calc() {
	r.MOBILE = DecryptField(r.MOBILE)
	r.USERMOBILE = DecryptField(r.USERMOBILE)
}

func (r *ErmcpLoginUser) buildSqlRoleAcc() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select userid, wm_concat(roletype) roletype from arearole group by userid)" +
		"select t.userid," +
		"       t.memberuserid," +
		"       t.usertype," +
		"       t.accountname," +
		"       t.accountstatus," +
		"       u.mobile usermobile," +
		"       to_char(t.createtime, 'yyyy-mm-dd hh24:mi:ss') createtime," +
		"       to_char(t.modifytime, 'yyyy-mm-dd hh24:mi:ss') modifytime," +
		"       r.roletype," +
		"       1 as rolestatus," +
		"       l.logincode," +
		"       l.loginid," +
		"       l.clientroleid," +
		"       '企业成员' rolename," +
		"       l.loginstatus," +
		"       l.password," +
		"       l.mobile," +
		"       l.accountname loginname" +
		"  from useraccount t" +
		"  left join loginaccount l" +
		"    on t.userid = l.userid" +
		"  left join userinfo u on t.userid=u.userid" +
		"  left join tmp r" +
		"    on t.userid = r.userid" +
		" where 1 = 1" +
		"   and t.memberuserid = %v" +
		"   and t.usertype = 7"
	sqlId.FormatParam(r.USERID)
	return sqlId.String()
}

func (r *ErmcpLoginUser) buildSqlManageAcc() string {
	var sqlId utils.SQLVal = "select t.userid," +
		"       t.memberuserid," +
		"       t.usertype," +
		"       t.accountname," +
		"       to_char(t.createtime, 'yyyy-mm-dd hh24:mi:ss') createtime," +
		"       to_char(t.modifytime, 'yyyy-mm-dd hh24:mi:ss') modifytime," +
		"       to_char(r.roletype) roletype," +
		"       r.rolestatus," +
		"       l.logincode," +
		"       l.loginid," +
		"       l.clientroleid," +
		"       Nvl(r.rolename, l.clientroleid) rolename," +
		"       l.loginstatus," +
		"       l.password," +
		"       l.mobile," +
		"       l.accountname loginname" +
		"  from useraccount t" +
		"  left join loginaccount l" +
		"    on t.userid = l.userid" +
		"  left join systemmanagerrole r" +
		"    on l.clientroleid = r.autoid" +
		"   and r.areauserid = l.userid" +
		" where 1 = 1" +
		"   and t.userid = %v" +
		"   and t.usertype = 2"
	sqlId.FormatParam(r.USERID)
	return sqlId.String()
}

// hasRoletype 是否拥用某种角色
func (r *ErmcpLoginUser) hasRoletype(nType int32) bool {
	sType := strings.Split(r.ROLETYPE, ",")
	strType := fmt.Sprintf("%v", nType)
	for i := range sType {
		if sType[i] == strType {
			return true
		}
	}
	return false
}

// getBizLoginAcc 获取业务账号(角色为22, 23)
func (r *ErmcpLoginUser) getBizLoginAcc() ([]ErmcpLoginUserEx, error) {
	sDataEx := make([]ErmcpLoginUserEx, 0)
	sDataEx = append(sDataEx, ErmcpLoginUserEx{
		ROLENAME:  "业务员",
		RoleId:    22,
		UserList:  make([]ErmcpLoginUser, 0),
		USERTYPE:  7,
		QUERYTYPE: r.QueryType})
	sDataEx = append(sDataEx, ErmcpLoginUserEx{
		ROLENAME:  "跟单员",
		RoleId:    23,
		UserList:  make([]ErmcpLoginUser, 0),
		USERTYPE:  7,
		QUERYTYPE: r.QueryType})
	// 查询用户
	sData := make([]ErmcpLoginUser, 0)
	err := db.GetEngine().SQL(r.buildSqlRoleAcc()).Find(&sData)
	for i := range sData {
		sData[i].calc()
		sData[i].AccList = make([]ErmcpLoginTaAccount, 0)
	}
	// 查询登录账号关联的资金账号
	if len(sData) > 0 {
		m := ErmcpLoginTaAccount{}
		if accLst, err := m.GetData(); err == nil {
			for i := range sData {
				sData[i].addAcc(accLst)
			}
		}
	}
	// 按角色分组
	for _, v := range sData {
		for i := range sDataEx {
			// 企业成员usertype=7需根据roletype判断
			if sDataEx[i].RoleId == 22 && v.hasRoletype(22) {
				v.ROLENAME = sDataEx[i].ROLENAME
				sDataEx[i].UserList = append(sDataEx[i].UserList, v)
			}
			if sDataEx[i].RoleId == 23 && v.hasRoletype(23) {
				v.ROLENAME = sDataEx[i].ROLENAME
				sDataEx[i].UserList = append(sDataEx[i].UserList, v)
			}
		}
	}

	return sDataEx, err
}

// getTradeLoginAcc 获取交易账号(角色id为24, 但是要按用户分组)
func (r *ErmcpLoginUser) getTradeLoginAcc() ([]ErmcpLoginUserEx, error) {
	sDataEx := make([]ErmcpLoginUserEx, 0)
	// 查询用户
	sData := make([]ErmcpLoginUser, 0)
	err := db.GetEngine().SQL(r.buildSqlRoleAcc()).Find(&sData)
	for i := range sData {
		sData[i].calc()
		sData[i].AccList = make([]ErmcpLoginTaAccount, 0)
	}
	// 查询登录账号关联的资金账号
	if len(sData) > 0 {
		m := ErmcpLoginTaAccount{}
		if accLst, err := m.GetData(); err == nil {
			for i := range sData {
				sData[i].addAcc(accLst)
			}
		}
	}
	// 按用户分组
	for _, v := range sData {
		if v.hasRoletype(24) {
			bExist := false
			for i := range sDataEx {
				if sDataEx[i].RoleId == v.USERID {
					sDataEx[i].UserList = append(sDataEx[i].UserList, v)
					bExist = true
					break
				}
			}
			if !bExist {
				val := ErmcpLoginUserEx{RoleId: v.USERID, ROLENAME: v.ACCOUNTNAME, USERTYPE: 7,
					MOBILE: v.USERMOBILE, ACCOUNTSTATUS: v.ACCOUNTSTATUS,
					UserList: make([]ErmcpLoginUser, 0), QUERYTYPE: r.QueryType}
				if v.LOGINID > 0 {
					val.UserList = append(val.UserList, v)
				}
				sDataEx = append(sDataEx, val)
			}
		}
	}

	return sDataEx, err
}

// getMangeLoginAcc 获取管理账户(角色表中创建的角色)
func (r *ErmcpLoginUser) getMangeLoginAcc() ([]ErmcpLoginUserEx, error) {
	sDataEx := make([]ErmcpLoginUserEx, 0)
	// 查询角色
	m := ErmcpRole{AREAUSERID: r.USERID}
	roles, _ := m.GetData()
	if len(roles) == 0 {
		return sDataEx, nil
	}
	// 初始化角色列表
	for _, val := range roles {
		sDataEx = append(sDataEx, ErmcpLoginUserEx{
			ROLENAME:  val.ROLENAME,
			RoleId:    int64(val.AUTOID),
			UserList:  make([]ErmcpLoginUser, 0),
			USERTYPE:  2,
			QUERYTYPE: r.QueryType,
		})
	}
	// 查询用户
	sData := make([]ErmcpLoginUser, 0)
	err := db.GetEngine().SQL(r.buildSqlManageAcc()).Find(&sData)
	for i := range sData {
		sData[i].calc()
		sData[i].AccList = make([]ErmcpLoginTaAccount, 0)
	}
	// 查询登录账号关联的资金账号
	if len(sData) > 0 {
		m := ErmcpLoginTaAccount{}
		if accLst, err := m.GetData(); err == nil {
			for i := range sData {
				sData[i].addAcc(accLst)
			}
		}
	}
	// 管理员用户分组(按角色)
	for _, v := range sData {
		for i := range sDataEx {
			// 企业管理角色直接使用CLIENTROLEID判断
			if sDataEx[i].RoleId == int64(v.CLIENTROLEID) {
				sDataEx[i].UserList = append(sDataEx[i].UserList, v)
			}
		}
	}

	return sDataEx, err
}

// GetDataEx 查询登录用户
func (r *ErmcpLoginUser) GetDataEx() (interface{}, error) {
	if r.QueryType == 1 {
		return r.getBizLoginAcc()
	} else if r.QueryType == 2 {
		return r.getTradeLoginAcc()
	} else {
		return r.getMangeLoginAcc()
	}
}

// ErmcpTaAccount 期货账户(分组)
type ErmcpTaAccountEx struct {
	MainAcc    ErmcpTaAccountM  `json:"mainAcc"`    // 主账号
	SubAccList []ErmcpTaAccount `json:"subacclist"` // 子账号列表
}

// 显示主账户额外信息
type ErmcpTaAccountM struct {
	ErmcpTaAccount
	FCID   int32  `json:"fcid"  xorm:"'FCID'"`     // 期货公司id
	FCNAME string `json:"fcname"  xorm:"'FCNAME'"` // 期货公司代码
}

// ErmcpTaAccount 期货账户
type ErmcpTaAccount struct {
	ACCOUNTID          int64  `json:"accountid"  xorm:"'ACCOUNTID'"`                   // 账户id
	CURRENCYID         int32  `json:"currencyid"  xorm:"'CURRENCYID'"`                 // 币种id
	TRADESTATUS        int32  `json:"tradestatus"  xorm:"'TRADESTATUS'"`               // 交易状态 - 1:正常 2:受限 3:冻结 4:禁止建仓(人工受限) 5:禁止交易(人工冻结) 6:待激活 7:已注销
	ISMAIN             int32  `json:"ismain"  xorm:"'ISMAIN'"`                         // 是否主账户 0-否 1-是
	USERID             int64  `json:"userid"  xorm:"'USERID'"`                         // 用户id
	RELATEDUSERID      int64  `json:"relateduserid"  xorm:"'RELATEDUSERID'"`           // 关联userid
	ACCOUNTNAME        string `json:"accountname"  xorm:"'ACCOUNTNAME'"`               // 账户名称
	PARENTACCOUNTID    int64  `json:"parentaccountid"  xorm:"'PARENTACCOUNTID'"`       // 父账户id
	TRADEMARGINTMPID   int32  `json:"trademargintmpid"  xorm:"'TRADEMARGINTMPID'"`     // 保证金模板id
	TRADEFEETMPID      int32  `json:"tradefeetmpid"  xorm:"'TRADEFEETMPID'"`           // 手续费模板id
	TRADERATETMPID     int32  `json:"traderatetmpid"  xorm:"'TRADERATETMPID'"`         // 汇率模板id
	TRADEMARGINTMPNAME string `json:"trademargintmpname"  xorm:"'TRADEMARGINTMPNAME'"` // 保证金模板名称
	TRADEFEETMPNAME    string `json:"tradefeetmpname"  xorm:"'TRADEFEETMPNAME'"`       // 手续费模板名称
	TRADERATETMPNAME   string `json:"traderatetmpname"  xorm:"'TRADERATETMPNAME'"`     // 汇率模板名称

	HEDGEACCOUNTCODE string `json:"hedgeaccountcode"  xorm:"'HEDGEACCOUNTCODE'"` // 主账号登录代码(仅当是主账号时有值,子账号没有登录账号)
}

func (r *ErmcpTaAccount) calc() {

}

func (r *ErmcpTaAccount) buildSql() string {
	var sqlId utils.SQLVal = "select t.accountid," +
		"       t.currencyid," +
		"       t.tradestatus," +
		"       t.ismain," +
		"       t.userid," +
		"       t.relateduserid," +
		"       t.accountname," +
		"       t.parentaccountid," +
		"       l.hedgeaccountcode," +
		"       c.trademargintmpid," +
		"       c.tradefeetmpid," +
		"       c.traderatetmpid," +
		"       a.tradeconfigtmpname trademargintmpname," +
		"       b.tradeconfigtmpname tradefeetmpname," +
		"       k.tradeconfigtmpname traderatetmpname" +
		"  from taaccount t" +
		"  left join hedge_outmainconfig l" +
		"    on t.accountid = l.accountid" +
		"   and t.ismain = 1" +
		"  left join taaccountconfig c on t.accountid=c.accountid" +
		"  left join tradeconfigtmp a on c.trademargintmpid = a.tradeconfigtmpid" +
		"  left join tradeconfigtmp b on c.tradefeetmpid = b.tradeconfigtmpid" +
		"  left join tradeconfigtmp k on c.traderatetmpid = k.tradeconfigtmpid" +
		" where 1 = 1" +
		"   and t.taaccounttype = 1"
	sqlId.And("t.userid", r.USERID)
	return sqlId.String()
}

// GetDataEx 查询企业期货账户
func (r *ErmcpTaAccount) GetDataEx() (interface{}, error) {
	sData := make([]ErmcpTaAccount, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	sDataEx := make([]ErmcpTaAccountEx, 0)
	// 主账号
	for i := range sData {
		if sData[i].ISMAIN == 1 {
			val := ErmcpTaAccountEx{MainAcc: ErmcpTaAccountM{ErmcpTaAccount: sData[i]},
				SubAccList: make([]ErmcpTaAccount, 0)}
			sDataEx = append(sDataEx, val)
		}
	}
	// 获取主账号额外信息
	m := MainAccountInfo{USERID: r.USERID}
	if d, err := m.GetDataEx(); err == nil {
		if acc, ok := d.([]MainAccountInfo); ok {
			for _, v := range acc {
				for i := range sDataEx {
					if sDataEx[i].MainAcc.ACCOUNTID == v.ACCOUNTID {
						sDataEx[i].MainAcc.FCID = v.FCID
						sDataEx[i].MainAcc.FCNAME = v.FCNAME
					}
				}
			}
		}
	}
	// 子账号
	for i := range sData {
		if sData[i].ISMAIN == 0 {
			for k := range sDataEx {
				if sDataEx[k].MainAcc.ACCOUNTID == sData[i].PARENTACCOUNTID {
					sDataEx[k].SubAccList = append(sDataEx[k].SubAccList, sData[i])
				}
			}
		}
	}

	return sDataEx, err
}

// MainAccountInfo 主账号详情
type MainAccountInfo struct {
	ACCOUNTID        int64  `json:"accountid"  xorm:"'ACCOUNTID'"`               // 账号id
	HEDGEACCOUNTCODE string `json:"hedgeaccountcode"  xorm:"'HEDGEACCOUNTCODE'"` // 外部账号代码(主账号登录代码)
	HEDGEACCOUNTPWD  string `json:"hedgeaccountpwd"  xorm:"'HEDGEACCOUNTPWD'"`   // 密码
	AUTHCODE         string `json:"authcode"  xorm:"'AUTHCODE'"`                 // 授权码
	APPID            string `json:"appid"  xorm:"'APPID'"`                       // AppID
	BROKERID         string `json:"brokerid"  xorm:"'BROKERID'"`                 // 经纪商id
	FCID             int32  `json:"fcid"  xorm:"'FCID'"`                         // 期货公司id
	FCNAME           string `json:"fcname"  xorm:"'FCNAME'"`                     // 期货公司代码
	ISMAIN           int32  `json:"ismain"  xorm:"'ISMAIN'"`                     // 是否主账号
	USERID           int64  `json:"userid"  xorm:"'USERID'"`                     // 关联用户id
	RELATEDUSERID    int64  `json:"relateduserid"  xorm:"'RELATEDUSERID'"`       // 关联用户id
	ACCOUNTNAME      string `json:"accountname"  xorm:"'ACCOUNTNAME'"`           // 账号名称(taaccount表中的名称)
}

func (r *MainAccountInfo) calc() {
	r.HEDGEACCOUNTPWD = DecryptField(r.HEDGEACCOUNTPWD)
}

func (r *MainAccountInfo) buildSql() string {
	var sqlId utils.SQLVal = "select c.accountid," +
		"       c.hedgeaccountcode," +
		"       c.hedgeaccountpwd," +
		"       c.authcode," +
		"       c.appid," +
		"       c.brokerid," +
		"       c.fcid," +
		"       f.fcname," +
		"       ta.ismain," +
		"       ta.relateduserid," +
		"       ta.userid," +
		"       ta.accountname" +
		"  from hedge_outmainconfig c" +
		"  left join ERMCP_FuturesCompany f" +
		"    on c.fcid = f.fcid" +
		"  left join taaccount ta" +
		"    on c.accountid = ta.accountid" +
		" where 1 = 1"

	sqlId.AndEx("ta.userid", r.USERID, r.USERID > 0)
	sqlId.AndEx("ta.relateduserid", r.RELATEDUSERID, r.RELATEDUSERID > 0)
	sqlId.AndInterEx("c.ACCOUNTID", r.ACCOUNTID, r.ACCOUNTID > 0)

	return sqlId.String()
}

// GetDataEx 查询主账号信息
func (r *MainAccountInfo) GetDataEx() (interface{}, error) {
	sData := make([]MainAccountInfo, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// ErmcpRole 角色
type ErmcpRole struct {
	AUTOID     int32  `json:"autoid"  xorm:"'AUTOID'"`         // 角色ID(自增ID)
	ROLENAME   string `json:"rolename"  xorm:"'ROLENAME'"`     // 角色名称
	AREAUSERID int64  `json:"areauserid"  xorm:"'AREAUSERID'"` // 所属机构
	ROLESTATUS int32  `json:"rolestatus"  xorm:"'ROLESTATUS'"` // 角色状态 - 1:启用  2：停用
	ROLETYPE   int32  `json:"roletype"  xorm:"'ROLETYPE'"`     // 角色类型 - 1- 管理端   2-  交易端
	MODIFYTIME string `json:"modifytime"  xorm:"'MODIFYTIME'"` // 修改时间(创建时间)
	MODIFIERID int64  `json:"modifierid"  xorm:"'MODIFIERID'"` // 修改人(创建人)

	MODIFIERNAME string `json:"modifiername"` // 修改人名称
}

func (r *ErmcpRole) calc() {
	if r.MODIFIERID > 0 {
		r.MODIFIERNAME = mtpcache.GetSystemmangerLoginCode(r.MODIFIERID)
		// 目前看表里修改人id存的是管理员id,为防止以后终端操作存的是登录id,多做一层判断
		// 如果按管理员id找不到，则按登录id试试
		if r.MODIFIERID > 1e8 && len(r.MODIFIERNAME) == 0 {
			r.MODIFIERNAME = mtpcache.GetLoginCodeByLoginId(r.MODIFIERID)
		}
	}
}

func (r *ErmcpRole) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.AUTOID," +
		"       t.ROLENAME," +
		"       t.AREAUSERID," +
		"       t.ROLESTATUS," +
		"       t.ROLETYPE," +
		"       t.SENSITIVEFIELDS," +
		"       to_char(t.MODIFYTIME, 'yyyy-mm-dd hh24:mi:ss') MODIFYTIME," +
		"       to_char(t.MODIFIERID) MODIFIERID" +
		"  FROM SYSTEMMANAGERROLE t" +
		" WHERE 1 = 1"
	if r.AREAUSERID > 0 {
		sqlId.And("t.AREAUSERID", r.AREAUSERID)
	}
	return sqlId.String()
}

// GetData
func (r *ErmcpRole) GetData() ([]ErmcpRole, error) {
	sData := make([]ErmcpRole, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// GetDataEx 查询角色
func (r *ErmcpRole) GetDataEx() (interface{}, error) {
	return r.GetData()
}

// ErmcpRoleMenuEx 角色菜单(分层级)
type ErmcpRoleMenuEx struct {
	Menu    ErmcpRoleMenu     // 父级菜单
	SubMenu []ErmcpRoleMenuEx // 子级菜单
}

// add 添加子级菜单
func (r *ErmcpRoleMenuEx) add(menu ErmcpRoleMenuEx) {
	if menu.Menu.PARENTCODE == r.Menu.RESOURCECODE {
		r.SubMenu = append(r.SubMenu, menu)
	}
}

// addEx 添加子级菜单（不包括PARENTCODE为空的）
func (r *ErmcpRoleMenuEx) addEx(menus []ErmcpRoleMenu) {
	for _, v := range menus {
		if v.PARENTCODE == r.Menu.RESOURCECODE {
			e := ErmcpRoleMenuEx{Menu: v, SubMenu: make([]ErmcpRoleMenuEx, 0)}
			// 递归构建子菜单
			e.addEx(menus)

			r.SubMenu = append(r.SubMenu, e)
		}
	}
}

// setRold 设置菜单权限（包括子菜单）
func (r *ErmcpRoleMenuEx) setRold(menus []ErmcpRoleMenu) {
	for _, v := range menus {
		if r.Menu.RESOURCECODE == v.RESOURCECODE {
			r.Menu.ISHADROLE = true
			break
		}
	}

	// 递归设置子菜单权限
	for i := range r.SubMenu {
		s := &r.SubMenu[i]
		s.setRold(menus)
	}
}

// ErmcpRoleMenu 角色菜单
type ErmcpRoleMenu struct {
	USERID        int64  `json:"userid"  xorm:"'USERID'"`               // 用户id
	ROLEID        int32  `json:"roleid"  xorm:"'ROLEID'"`               // 角色id
	RESOURCECODE  string `json:"resourcecode"  xorm:"'RESOURCECODE'"`   // 菜单代码
	RESOURCENAME  string `json:"resourcename"  xorm:"'RESOURCENAME'"`   // 菜单名称
	RESOURCELEVEL int32  `json:"resourcelevel"  xorm:"'RESOURCELEVEL'"` // 级别
	MENUTYPE      int32  `json:"menutype"  xorm:"'MENUTYPE'"`           // 菜单类型 1:管理端 2:交易端 3:终端(企业云平台)
	PARENTCODE    string `json:"parentcode"  xorm:"'PARENTCODE'"`       // 上级资源代码
	URL           string `json:"url"  xorm:"'URL'"`                     // URL
	SORT          int32  `json:"sort"  xorm:"'SORT'"`                   // 排序
	ICONAME       string `json:"iconame"  xorm:"'ICONAME'"`             // 菜单图标
	REMARK        string `json:"remark"  xorm:"'REMARK'"`               // 菜单备注

	ISHADROLE bool `json:"ishadrole" xorm:"-"` // 是否有权限

	FilterRoleId string `json:"-"` // 查询过滤条件, 角色 - 逗号隔开
}

func (r *ErmcpRoleMenu) calc() {
}

func (r *ErmcpRoleMenu) buildSql() string {
	var sqlId utils.SQLVal = "select distinct t.userid," +
		"       t.roleid," +
		"       f.resourcecode," +
		"       f.resourcename," +
		"       f.resourcelevel," +
		"       f.menutype," +
		"       f.parentcode," +
		"       f.url," +
		"       f.sort," +
		"       f.iconame," +
		"       f.remark" +
		"  from ermcp_memberfuncmenu t" +
		" inner join funcmenulist f" +
		"    on t.resourcecode = f.resourcecode and f.menutype = %v" +
		" where t.isvalid = 1"
	if r.USERID > 0 {
		sqlId.And("t.userid", r.USERID)
	}
	if len(r.FilterRoleId) > 0 {
		sqlId.Join(fmt.Sprintf(" and t.roleid in(%v)", r.FilterRoleId))
	}
	if r.MENUTYPE == 0 {
		r.MENUTYPE = 3
	}
	sqlId.FormatParam(r.MENUTYPE)
	sqlId.Join(" order by f.resourcelevel, f.sort")

	return sqlId.String()
}

// GetDataEx 查询角色菜单
func (r *ErmcpRoleMenu) GetDataEx() (interface{}, error) {
	sDataEx := make([]ErmcpRoleMenuEx, 0)

	// 获取所有菜单
	sData := make([]ErmcpRoleMenu, 0)
	sql := "select 0 userid," +
		"       0 roleid," +
		"       f.resourcecode," +
		"       f.resourcename," +
		"       f.resourcelevel," +
		"       f.menutype," +
		"       f.parentcode," +
		"       f.url," +
		"       f.sort," +
		"       f.iconame," +
		"       f.remark" +
		" from funcmenulist f" +
		" where f.resourcelevel <> 0"
	if r.MENUTYPE == 0 {
		r.MENUTYPE = 3
	}
	sql += fmt.Sprintf(" and f.menutype = %v", r.MENUTYPE)
	_ = db.GetEngine().SQL(sql).Find(&sData)
	// 先创建PARENTCODE为空的记录
	for _, v := range sData {
		// 特殊处理“铂金宝”菜单，解决4层的问题
		if v.PARENTCODE == "" || v.PARENTCODE == "client_pc_web_qhj" || v.RESOURCELEVEL == 1 {
			sDataEx = append(sDataEx, ErmcpRoleMenuEx{Menu: v, SubMenu: make([]ErmcpRoleMenuEx, 0)})
		}
	}
	// 再构建所有子菜单
	for i := range sDataEx {
		v := &sDataEx[i]
		v.addEx(sData)
	}

	// 替换角色id, 20变为0, 如果多个角色, 则不要20
	if len(r.FilterRoleId) > 0 {
		var newRoleId string
		sRole := strings.Split(r.FilterRoleId, ",")
		// 把不是20的取出来, 去除20角色
		for _, v := range sRole {
			if d, err := strconv.ParseInt(v, 10, 32); err == nil {
				if d != 20 {
					if len(newRoleId) > 0 {
						newRoleId += ","
					}
					newRoleId += v
				}
			}
		}
		// 只有20的角色, 则变为0角色
		if len(newRoleId) == 0 {
			newRoleId = "0"
		}
		r.FilterRoleId = newRoleId
	}

	// 获取有权限的菜单
	var err error
	if len(r.FilterRoleId) > 0 {
		rData := make([]ErmcpRoleMenu, 0)
		err = db.GetEngine().SQL(r.buildSql()).Find(&rData)
		// 判断权限
		for i := range sDataEx {
			s := &sDataEx[i]
			s.setRold(rData)
		}
	}

	return sDataEx, err
}

// ErmcpBizGroupSpotGoods 业务类型分组商品表
type ErmcpBizGroupSpotGoods struct {
	BIZTYPE           int32  `json:"biztype"  xorm:"'BIZTYPE'"`                     // 业务类型 1-套保 2-套利
	BIZGROUPID        int64  `json:"bizgroupid"  xorm:"'BIZGROUPID'"`               // 分组id
	DELIVERYGOODSID   int32  `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`     // 现货商品id
	DELIVERYGOODSCODE string `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"` // 现货商品代码
	DELIVERYGOODSNAME string `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"` // 现货商品名称
	GOODSUNITID       int32  `json:"unitid"  xorm:"'UNITID'"`                       // 现货商品单位id
	AREAUSERID        int64  `json:"areauserid"  xorm:"'AREAUSERID'"`               // 所属机构id
}

func (r *ErmcpBizGroupSpotGoods) calc() {
}

func (r *ErmcpBizGroupSpotGoods) buildSql() string {
	var sqlId utils.SQLVal = "select t.biztype," +
		"       t.bizgroupid," +
		"       t.deliverygoodsid," +
		"       g.deliverygoodsname," +
		"       g.deliverygoodscode," +
		"       g.goodsunitid," +
		"       g.areauserid" +
		"  from ERMCP_BizGroupSpotGoods t" +
		"  left join deliverygoods g" +
		"    on t.deliverygoodsid = g.deliverygoodsid" +
		"  where 1=1"
	sqlId.AndEx("g.areauserid", r.AREAUSERID, r.AREAUSERID > 0)
	sqlId.AndEx("t.deliverygoodsid", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0)
	return sqlId.String()
}

// GetDataEx 获取业务类型分组商品
func (r *ErmcpBizGroupSpotGoods) GetDataEx() ([]ErmcpBizGroupSpotGoods, error) {
	sData := make([]ErmcpBizGroupSpotGoods, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// ErcmpBizGroupTaaccount 业务分组账号
type ErmcpBizGroupTaAccount struct {
	BIZGROUPID  int64  `json:"bizgroupid"  xorm:"'BIZGROUPID'"`   // 业务分组id
	ACCOUNTID   int64  `json:"accountid"  xorm:"'ACCOUNTID'"`     // 账号id
	AREAUSERID  int64  `json:"areauserid"  xorm:"'AREAUSERID'"`   // 所属机构id
	ACCOUNTNAME string `json:"accountname"  xorm:"'ACCOUNTNAME'"` // 账号名称
	ISMAIN      int32  `json:"ismain"  xorm:"'ISMAIN'"`           // 是否主账号 1-是 0-否
}

func (r *ErmcpBizGroupTaAccount) calc() {
}

func (r *ErmcpBizGroupTaAccount) buildSql() string {
	var sqlId utils.SQLVal = "select t.bizgroupid," +
		"       t.accountid," +
		"       ta.relateduserid areauserid," +
		"       ta.accountname," +
		"       ta.ismain" +
		"  from ermcp_bizgrouptaaccount t" +
		"  left join taaccount ta" +
		"    on t.accountid = ta.accountid" +
		" where 1 = 1"
	if r.AREAUSERID > 0 {
		sqlId.And("ta.relateduserid", r.AREAUSERID)
	}
	return sqlId.String()
}

// GetDataEx 获取业务分组账号
func (r *ErmcpBizGroupTaAccount) GetDataEx() ([]ErmcpBizGroupTaAccount, error) {
	sData := make([]ErmcpBizGroupTaAccount, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// 账户设置分组信息
type ErmcpBizGroupEx struct {
	BIZTYPE    int32 `json:"biztype"  xorm:"'BIZTYPE'"`       // 业务类型 1-套保 2-套利
	BIZGROUPID int64 `json:"bizgroupid"  xorm:"'BIZGROUPID'"` // 业务分组id

	AccList []ErmcpBizGroupTaAccount `json:"acclist"` // 账号列表
	WrList  []ErmcpBizGroupSpotGoods `json:"wrlist"`  // 现货商品表

	AREAUSERID      int64 `json:"-"` // 所属机构
	DELIVERYGOODSID int32 `json:"-"` // 现货商品id
}

// GetDataEx 获取账户设置分组信息
func (r *ErmcpBizGroupEx) GetDataEx() (interface{}, error) {
	sData := make([]ErmcpBizGroupEx, 0)
	mWr := ErmcpBizGroupSpotGoods{AREAUSERID: r.AREAUSERID, DELIVERYGOODSID: r.DELIVERYGOODSID}
	mAcc := ErmcpBizGroupTaAccount{AREAUSERID: r.AREAUSERID}
	if wrLst, err := mWr.GetDataEx(); err == nil {
		for i := range wrLst {
			var bFind = false
			for k := range sData {
				if sData[k].BIZGROUPID == wrLst[i].BIZGROUPID &&
					sData[k].BIZTYPE == wrLst[i].BIZTYPE {
					sData[k].WrList = append(sData[k].WrList, wrLst[i])
					bFind = true
				}
			}

			if !bFind {
				var d = ErmcpBizGroupEx{BIZGROUPID: wrLst[i].BIZGROUPID,
					BIZTYPE: wrLst[i].BIZTYPE,
					AccList: make([]ErmcpBizGroupTaAccount, 0),
					WrList:  make([]ErmcpBizGroupSpotGoods, 0)}
				d.WrList = append(d.WrList, wrLst[i])
				sData = append(sData, d)
			}
		}
	}

	if len(sData) == 0 {
		// 如果没有业务分组, 则不用查关联资金账户了
		return sData, nil
	}

	if accLst, err := mAcc.GetDataEx(); err == nil {
		for i := range accLst {
			for k := range sData {
				if sData[k].BIZGROUPID == accLst[i].BIZGROUPID {
					sData[k].AccList = append(sData[k].AccList, accLst[i])
				}
			}
		}
	}

	return sData, nil
}

// 资金账号信息
type ErmcpTaAccountInfo struct {
	CREDITDECREASE        float64 `json:"creditdecrease"  xorm:"'CREDITDECREASE'"`             // 今日授信减少
	OTHERCREDITINCREASE   float64 `json:"othercreditincrease"  xorm:"'OTHERCREDITINCREASE'"`   // 今日其它授信增加
	OTHERCREDITDECREASE   float64 `json:"othercreditdecrease"  xorm:"'OTHERCREDITDECREASE'"`   // 今日其它授信减少
	TRANSFERAMOUNT        float64 `json:"transferamount"  xorm:"'TRANSFERAMOUNT'"`             // 今日划转金额(母子账号资金划转，从划入账号为正，从账号划出为负)
	OTHERPAY              float64 `json:"-"  xorm:"'OTHERPAY'"`                                // 其他支出(交割付款 申购付款 全款买入 商城买入 卖家退货)
	OTHERINCOME           float64 `json:"-"  xorm:"'OTHERINCOME'"`                             // 其他收入(交割收款 申购收款 全款卖出 商城卖出 买家退货 会员手续费收入)
	OUTTHRESHOLD          float64 `json:"outthreshold"  xorm:"'OUTTHRESHOLD'"`                 // 出金阈值
	TRADESTATUSCHANGETIME string  `json:"-"  xorm:"'TRADESTATUSCHANGETIME'"`                   // 激活时间
	CHANGETIME            string  `json:"-"  xorm:"'CHANGETIME'"`                              // 账户状态变更时间
	THIRDINAMOUNT         float64 `json:"thirdinamount"  xorm:"'THIRDINAMOUNT'"`               // 今日三方入金
	THIRDOUTAMOUNT        float64 `json:"thirdoutamount"  xorm:"'THIRDOUTAMOUNT'"`             // 今日三方出金
	CAPITALBALANCE        float64 `json:"capitalbalance"  xorm:"'CAPITALBALANCE'"`             // 本金余额[外部子账户实际出入金余额]
	FROMACCOUNTID         int64   `json:"fromaccountid"  xorm:"'FROMACCOUNTID'"`               // 所属上级账户
	SUBLEVELPATH          string  `json:"sublevelpath"  xorm:"'SUBLEVELPATH'"`                 // 账号层级路径(逗号分隔,首尾加逗号)
	SERIVCEGROUP          int64   `json:"-"  xorm:"'SERIVCEGROUP'"`                            // 服务分组
	ACCOUNTNAME           string  `json:"accountname"  xorm:"'ACCOUNTNAME'"`                   // 账户名称
	ACCOUNTFLAG           int32   `json:"-"  xorm:"'ACCOUNTFLAG'"`                             // 账户标识 - 0\1 (默认为0， 当上级账户与本账户的关联用户均为自己时更新为1)
	ORIDEBT               float64 `json:"oridebt"  xorm:"'ORIDEBT'"`                           // 期初欠款
	CURDEBT               float64 `json:"curdebt"  xorm:"'CURDEBT'"`                           // 期末欠款
	ACCOUNTID             int64   `json:"accountid"  xorm:"'ACCOUNTID'"`                       // 资金账户ID
	USERID                int64   `json:"userid"  xorm:"'USERID'"`                             // 用户ID
	CURRENCYID            int64   `json:"currencyid"  xorm:"'CURRENCYID'"`                     // 货币ID
	CHANGEFLAG            int32   `json:"-"  xorm:"'CHANGEFLAG'"`                              // 变动标志(当前账户资金有任何变动更新为1系统结算时更新0;供清算时使用) 0：无变动 1：有变动
	PASSWORD              string  `json:"-"  xorm:"'PASSWORD'"`                                // 资金密码
	TRADESTATUS           int32   `json:"tradestatus"  xorm:"'TRADESTATUS'"`                   // 交易状态 - 1:正常 2:受限 3:冻结 4:禁止建仓(人工受限) 5:禁止交易(人工冻结) 6:待激活 7:已注销
	SIGNSTATUS            int32   `json:"signstatus"  xorm:"'SIGNSTATUS'"`                     // 签约状态 - 1:未签约 2:签约待审核 3:签约中 4:已签约 5:解约待审核 6:解约中 7:已解约 8:已解绑 9:绑卡中
	ISMAIN                int32   `json:"ismain"  xorm:"'ISMAIN'"`                             // 是否母账号 0:不是母账户 1:是母账户
	PARENTACCOUNTID       int64   `json:"parentaccountid"  xorm:"'PARENTACCOUNTID'"`           // 所属根账号
	RELATEDUSERID         int64   `json:"relateduserid"  xorm:"'RELATEDUSERID'"`               // 关联用户
	RELATEDACCOUNTSTATUS  int32   `json:"-"  xorm:"'RELATEDACCOUNTSTATUS'"`                    // 关联用户交易权限状态 - 1:正常(可交易) 2:受限(可平仓，不可建仓) 3:冻结(不可交易)
	TAACCOUNTTYPE         int32   `json:"taaccounttype"  xorm:"'TAACCOUNTTYPE'"`               // 账号类型 - 1:外部账号 2:内部账号 3:内部做市自营账号 4:内部做市接单账号
	ISRECKONACCOUNT       int32   `json:"-"  xorm:"'ISRECKONACCOUNT'"`                         // 是否机构分润账号 0:不是 1:是
	ISMARKETACCOUNT       int32   `json:"-"  xorm:"'ISMARKETACCOUNT'"`                         // 是否机构接单账号 0:不是 1:是
	BALANCE               float64 `json:"balance"  xorm:"'BALANCE'"`                           // 期初余额
	ORIFREEZEMARGIN       float64 `json:"orifreezemargin"  xorm:"'ORIFREEZEMARGIN'"`           // 期初冻结保证金
	ORIUSEDMARGIN         float64 `json:"oriusedmargin"  xorm:"'ORIUSEDMARGIN'"`               // 期初占用保证金
	ORIOTHERFREEZEMARGIN  float64 `json:"oriotherfreezemargin"  xorm:"'ORIOTHERFREEZEMARGIN'"` // 期初其他冻结保证金(出金冻结资金 交割买方冻结 申购冻结 全款买入 商城买入)
	ORIFREEZECHARGE       float64 `json:"orifreezecharge"  xorm:"'ORIFREEZECHARGE'"`           // 期初手续费冻结
	ORIMORTGAGECREDIT     float64 `json:"orimortgagecredit"  xorm:"'ORIMORTGAGECREDIT'"`       // 期初授信金额
	ORIOTHERCREDIT        float64 `json:"oriothercredit"  xorm:"'ORIOTHERCREDIT'"`             // 期初其它授信金额
	ORIOUTAMOUNTFREEZE    float64 `json:"orioutamountfreeze"  xorm:"'ORIOUTAMOUNTFREEZE'"`     // 期初出金冻结
	FREEZEMARGIN          float64 `json:"freezemargin"  xorm:"'FREEZEMARGIN'"`                 // 冻结保证金
	USEDMARGIN            float64 `json:"usedmargin"  xorm:"'USEDMARGIN'"`                     // 占用保证金
	OTHERFREEZEMARGIN     float64 `json:"otherfreezemargin"  xorm:"'OTHERFREEZEMARGIN'"`       // 其他冻结保证金(交割买方冻结 申购冻结 全款买入 商城买入)
	FREEZECHARGE          float64 `json:"freezecharge"  xorm:"'FREEZECHARGE'"`                 // 手续费冻结
	MORTGAGECREDIT        float64 `json:"mortgagecredit"  xorm:"'MORTGAGECREDIT'"`             // 授信金额
	OTHERCREDIT           float64 `json:"othercredit"  xorm:"'OTHERCREDIT'"`                   // 其它授信金额
	OUTAMOUNTFREEZE       float64 `json:"outamountfreeze"  xorm:"'OUTAMOUNTFREEZE'"`           // 出金冻结
	INAMOUNT              float64 `json:"inamount"  xorm:"'INAMOUNT'"`                         // 今日入金金额(包括三方入金)
	CURRENTBALANCE        float64 `json:"currentbalance"  xorm:"'CURRENTBALANCE'"`             // 期末余额
	OUTAMOUNT             float64 `json:"outamount"  xorm:"'OUTAMOUNT'"`                       // 今日出金金额(包括三方出金)
	PAYCHARGE             float64 `json:"paycharge"  xorm:"'PAYCHARGE'"`                       // 今日手续费支出
	CLOSEPL               float64 `json:"closepl"  xorm:"'CLOSEPL'"`                           // 今日平仓盈亏
	CREDITINCREASE        float64 `json:"creditincrease"  xorm:"'CREDITINCREASE'"`             // 今日授信增加
}

func (r *ErmcpTaAccountInfo) calc() {
}

func (r *ErmcpTaAccountInfo) buildSql() string {
	var sqlId utils.SQLVal = "select * from taaccount t where 1 = 1"
	if r.USERID > 0 {
		sqlId.And("t.RELATEDUSERID", r.RELATEDUSERID)
	}
	if r.ACCOUNTID > 0 {
		sqlId.And("t.accountid", r.ACCOUNTID)
	}
	return sqlId.String()
}

// 获取资金账号信息
func (r *ErmcpTaAccountInfo) GetDataEx() (interface{}, error) {
	sData := make([]ErmcpTaAccountInfo, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// ErmcpLoginTaAccount 登录id关联的资金账号
type ErmcpLoginTaAccount struct {
	ACCOUNTID   int64  `json:"accountid"  xorm:"'ACCOUNTID'"`     // 账号id
	ACCOUNTNAME string `json:"accountname"  xorm:"'ACCOUNTNAME'"` // 账号名称
	LOGINID     int64  `json:"-"  xorm:"'LOGINID'"`               // 登录id
}

func (r *ErmcpLoginTaAccount) buildSql() string {
	var sqlId utils.SQLVal = "select t.loginid, t.accountid, ta.accountname" +
		"  from logintaaccount t" +
		" inner join taaccount ta" +
		"    on t.accountid = ta.accountid"
	return sqlId.String()
}

// GetData
func (r *ErmcpLoginTaAccount) GetData() ([]ErmcpLoginTaAccount, error) {
	sData := make([]ErmcpLoginTaAccount, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	return sData, err
}

// RoleMenu 角色菜单
type RoleMenu struct {
	USERID        int64  `json:"userid"  xorm:"'USERID'"`                     // 用户id
	ROLEID        int32  `json:"roleid"  xorm:"'ROLEID'"`                     // 角色id
	RESOURCECODE  string `json:"resourcecode"  xorm:"'RESOURCECODE'"`         // 菜单代码
	RESOURCENAME  string `json:"resourcename"  xorm:"'RESOURCENAME'"`         // 菜单名称
	RESOURCELEVEL int32  `json:"resourcelevel"  xorm:"'RESOURCELEVEL'"`       // 级别
	MENUTYPE      int32  `json:"menutype"  xorm:"'MENUTYPE'" form:"menutype"` // 菜单类型 1:管理端 2:交易端 3:终端(企业云平台)
	PARENTCODE    string `json:"parentcode"  xorm:"'PARENTCODE'"`             // 上级资源代码
	URL           string `json:"url"  xorm:"'URL'"`                           // URL
	SORT          int32  `json:"sort"  xorm:"'SORT'"`                         // 排序
	ICONAME       string `json:"iconame"  xorm:"'ICONAME'"`                   // 菜单图标
	REMARK        string `json:"remark"  xorm:"'REMARK'"`                     // 菜单备注
	HASRIGHT      int32  `json:"hasright"  xorm:"'HASRIGHT'"`                 // 是否有权限 0-没有权限 1-有权限

	LOGINID int64 `json:"-" form:"loginid" binding:"required"` // 登录id

	SubMenu []RoleMenu // 子级菜单(结构同上层一样, swag解析不出来)
}

func (r *RoleMenu) calc() {

}

func (r *RoleMenu) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select *" +
		"    from (select count(*) over() cnt," +
		"                 t.userid," +
		"                 case" +
		"                   when t.clientroleid is not null then" +
		"                    t.clientroleid" +
		"                   else" +
		"                    decode(a.roletype, 20, 0, a.roletype)" +
		"                 end roleid," +
		"                 t.accountname loginName," +
		"                 t.logincode," +
		"                 u.accountname userName," +
		"                 u.usertype," +
		"                 u.rootuserid" +
		"            from loginaccount t" +
		"            left join useraccount u" +
		"              on t.userid = u.userid" +
		"            left join arearole a" +
		"              on t.userid = a.userid" +
		"           where 1 = 1" +
		"             and t.loginid = %v) k" +
		"   where (cnt > 1 and roleid != 0)" +
		"      or cnt = 1)," +
		"tmpMenu as" +
		" (select distinct resourcecode" +
		"    from ermcp_memberfuncmenu m" +
		"   inner join tmp" +
		"      on m.userid = tmp.rootuserid" +
		"     and m.roleid = tmp.roleid)" +
		"select f.resourcecode," +
		"       f.resourcename," +
		"       f.resourcelevel," +
		"       f.menutype," +
		"       f.parentcode," +
		"       f.url," +
		"       f.sort," +
		"       f.iconame," +
		"       f.remark," +
		"       case" +
		"         when f.resourcecode in (select resourcecode from tmpMenu) then" +
		"          1" +
		"         else" +
		"          0" +
		"       end hasRight" +
		"  from funcmenulist f" +
		" where f.menutype = %v" +
		"   and f.resourcelevel <> 0" +
		" order by f.resourcelevel, f.sort"
	if r.MENUTYPE == 0 {
		r.MENUTYPE = 3 // 不传参数则默认查3
	}
	sqlId.FormatParam(r.LOGINID, r.MENUTYPE)
	return sqlId.String()
}

func (r *RoleMenu) addSub(lst []RoleMenu) {
	for i := range lst {
		if r.RESOURCECODE == lst[i].PARENTCODE {
			v := lst[i]
			v.SubMenu = make([]RoleMenu, 0)
			v.addSub(lst)
			r.SubMenu = append(r.SubMenu, v)
		}
	}
}

// GetDataEx 获取角色菜单
func (r *RoleMenu) GetDataEx() (interface{}, error) {
	sData := make([]RoleMenu, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}

	// 构建层级关系
	sData2 := make([]RoleMenu, 0)
	for _, v := range sData {
		if v.RESOURCELEVEL == 1 {
			v.SubMenu = make([]RoleMenu, 0)
			sData2 = append(sData2, v)
		}
	}

	for i := range sData2 {
		sData2[i].addSub(sData)
	}
	return sData2, err
}