MTP20_IF/models/qhj.go

/**
* @Author: zou.yingbin
* @Create  : 2021/6/1 14:14
* @Modify  : 2021/6/1 14:14
 */

package models

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

// 大连千海金

// QhjContract 千海金融资明细(合同)
type QhjContract struct {
	USERID              int64   `json:"userid"  xorm:"USERID"`                           // 用户ID
	SCFCONTRACTID       int64   `json:"scfcontractid"  xorm:"SCFCONTRACTID"`             // SCF合同ID(310+Unix秒时间戳(10位)+xxxxxx)
	SCFCONTRACTTYPE     int32   `json:"scfcontracttype"  xorm:"SCFCONTRACTTYPE"`         // 合同类型 - 1:仓单回购 2:仓单质押 3:合作套保 4:资金贷款 5:融资单
	GOODSID             int32   `json:"goodsid"  xorm:"GOODSID"`                         // 商品ID [5:融资单]
	CONTRACTCONFIRMTIME string  `json:"contractconfirmtime"  xorm:"CONTRACTCONFIRMTIME"` // 合同确认时间(时间)
	WRQTY               int32   `json:"wrqty"  xorm:"WRQTY"`                             // 仓单数量(数量)
	TRADEPRICE          float64 `json:"tradeprice"  xorm:"TRADEPRICE"`                   // 成交价格(成本价格)
	LENDERAMOUNT        float64 `json:"lenderamount"  xorm:"LENDERAMOUNT"`               // 贷款金额(总额|订单总额)
	MARGINRATIO         float64 `json:"marginratio"  xorm:"MARGINRATIO"`                 // 保证金比率(首付比例)
	INITMARGIN          float64 `json:"initmargin"  xorm:"'INITMARGIN'"`                 // 初始保证金(首付款)
	RECOVEREDMARGIN     float64 `json:"recoveredmargin"  xorm:"'RECOVEREDMARGIN'"`       // 已追缴保证金
	REMAINAMOUNT        float64 `json:"remainamount"  xorm:"'REMAINAMOUNT'"`             // 合同剩余金额(融资额)
	TOTALINTEREST       float64 `json:"totalinterest"  xorm:"'TOTALINTEREST'"`           // 已计总利息(利息)
	GOODSCODE           string  `json:"goodscode"  xorm:"'GOODSCODE'"`                   // 商品代码
	GOODSNAME           string  `json:"goodsname"  xorm:"'GOODSNAME'"`                   // 商品名称
	GOODUNITID          int32   `json:"goodunitid"  xorm:"'GOODUNITID'"`                 // 商品单位id

	PAYAMOUNT   float64 `json:"payamount"  xorm:"'PAYAMOUNT'"` // 已付(已付货款)
	USERNAME    string  `json:"username"  xorm:"'USERNAME'"`   // 用户名称
	ENUMDICNAME string  `json:"enumdicname"`                   // 单位名称
}

func (r *QhjContract) calc() {
	r.USERNAME = mtpcache.GetUserNameByUserId(r.USERID)
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.GOODUNITID)
	// 已付(已付货款) = 已追缴保证金  + 初始保证金
	r.PAYAMOUNT = r.RECOVEREDMARGIN + r.INITMARGIN
}

func (r *QhjContract) buildSql() string {
	var sqlId utils.SQLVal = "select t.borroweruserid userid," +
		"       t.scfcontractid," +
		"       t.scfcontracttype," +
		"       t.goodsid," +
		"       to_char(t.contractconfirmtime, 'yyyy-mm-dd hh24:mi:ss') contractconfirmtime," +
		"       t.wrqty," +
		"       t.tradeprice," +
		"       t.lenderamount," +
		"       t.marginratio," +
		"       s.initmargin," +
		"       s.recoveredmargin," +
		"       s.remainamount," +
		"       s.totalinterest," +
		"       g.goodscode," +
		"       g.goodsname," +
		"       g.goodunitid" +
		"  from scf_contract t" +
		"  left join scf_contractinfo s" +
		"    on t.scfcontractid = s.scfcontractid" +
		"  left join goods g" +
		"    on t.goodsid = g.goodsid" +
		"  where 1=1"
	sqlId.And("t.borroweruserid", r.USERID)
	sqlId.AndEx("t.SCFCONTRACTID", r.SCFCONTRACTID, r.SCFCONTRACTID > 0)
	return sqlId.String()
}

// GetDataEx 获取融资明细(合同)
func (r *QhjContract) GetDataEx() (interface{}, error) {
	sData := make([]QhjContract, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// QhjContractLog 融资明细流水log
type QhjContractLog struct {
	LOGID         string  `json:"logid"  xorm:"LOGID"`                 // LogID(315+Unix秒时间戳(10位)+2位(MarketServiceID)+xxxx)
	SCFCONTRACTID int64   `json:"scfcontractid"  xorm:"SCFCONTRACTID"` // SCF合同ID(310+Unix秒时间戳(10位)+xxxxxx)
	AMOUNTTYPE    int32   `json:"amounttype"  xorm:"AMOUNTTYPE"`       // 资金类型 - 1:首付款 2:尾款 3:利息  4:追加款
	AMOUNT        float64 `json:"amount"  xorm:"AMOUNT"`               // 支付金额
	REMAINAMOUNT  float64 `json:"remainamount"  xorm:"REMAINAMOUNT"`   // 剩余货款
	UPDATETIME    string  `json:"updatetime"  xorm:"UPDATETIME"`       // 更新时间

	FilterType string `json:"-"` // 资金类型
}

func (r *QhjContractLog) calc() {
}

func (r *QhjContractLog) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.LOGID," +
		"       t.SCFCONTRACTID," +
		"       t.AMOUNTTYPE," +
		"       t.AMOUNT," +
		"       t.REMAINAMOUNT," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME" +
		"  FROM SCF_CONTRACTAMOUNTLOG t" +
		" WHERE 1 = 1"
	sqlId.AndEx("t.SCFCONTRACTID", r.SCFCONTRACTID, r.SCFCONTRACTID > 0)
	sqlId.JoinEx(len(r.FilterType) > 0, fmt.Sprintf(" and t.AMOUNTTYPE in(%v)", r.FilterType))
	return sqlId.String()
}

// GetDataEx 获取融资明细流水log
func (r *QhjContractLog) GetDataEx() (interface{}, error) {
	sData := make([]QhjContractLog, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// QhjRStrategy 定投规则策略
type QhjRStrategy struct {
	REGULARLYCYCLE          int32   `json:"regularlycycle"  xorm:"REGULARLYCYCLE"`                   // 定投频率 - 1:每周 2:每月
	REGULARLYCYCLEVALUE     string  `json:"regularlycyclevalue"  xorm:"REGULARLYCYCLEVALUE"`         // 定投频率值 - 每周几(0-6)  或 每月几号(<=28)
	REGULARLYMODE           int32   `json:"regularlymode"  xorm:"REGULARLYMODE"`                     // 定投方式 - 1:按数量 2:按金额
	REGULARLYMODEVALUE      float64 `json:"regularlymodevalue"  xorm:"REGULARLYMODEVALUE"`           // 定投方式值 - 数量或金额
	REMARK                  string  `json:"remark"  xorm:"REMARK"`                                   // 备注
	REGULARLYSTRATEGYSTATUS int32   `json:"regularlystrategystatus"  xorm:"REGULARLYSTRATEGYSTATUS"` // 策略状态 - 1:正常 2:暂停 3:终止
	TRIGGERTOTALTIMES       string  `json:"triggertotaltimes"  xorm:"TRIGGERTOTALTIMES"`             // 累计定投总次数
	TRIGGERSUCCESSTIMES     string  `json:"triggersuccesstimes"  xorm:"TRIGGERSUCCESSTIMES"`         // 累计定投成功次数
	TRIGGERTOTALQTY         float64 `json:"triggertotalqty"  xorm:"TRIGGERTOTALQTY"`                 // 累计定投数量(成功) [按小数位放大值]
	TRIGGERTOTALAMOUNT      float64 `json:"triggertotalamount"  xorm:"TRIGGERTOTALAMOUNT"`           // 累计定投金额(成功)
	LASTEFFECTTRADEDATE     string  `json:"lasteffecttradedate"  xorm:"LASTEFFECTTRADEDATE"`         // 最新生效交易日
	LASTSTOPTRADEDATE       string  `json:"laststoptradedate"  xorm:"LASTSTOPTRADEDATE"`             // 最新暂停交易日
	LASTTRIGGERTRADEDATE    string  `json:"lasttriggertradedate"  xorm:"LASTTRIGGERTRADEDATE"`       // 最新触发交易日
	NEXTTRIGGERDATE         string  `json:"nexttriggerdate"  xorm:"NEXTTRIGGERDATE"`                 // 下次触发日期
	NEXTTRIGGERQTY          float64 `json:"nexttriggerqty"  xorm:"NEXTTRIGGERQTY"`                   // 下次触发数量 [按小数位放大值]
	NEXTTRIGGERAMOUNT       float64 `json:"nexttriggeramount"  xorm:"NEXTTRIGGERAMOUNT"`             // 下次触发金额
	UPDATORID               string  `json:"updatorid"  xorm:"UPDATORID"`                             // 更新人
	UPDATESRC               int32   `json:"updatesrc"  xorm:"UPDATESRC"`                             // 更新来源 - 1:管理端 2:终端
	UPDATETIME              string  `json:"updatetime"  xorm:"UPDATETIME"`                           // 更新时间
	REGULARLYSTRATEGYID     int64   `json:"regularlystrategyid"  xorm:"REGULARLYSTRATEGYID"`         // 定投策略ID (SEQ_QHJ_REGULARLYSTRATEGY)
	USERID                  int64   `json:"userid"  xorm:"USERID"`                                   // 所属用户ID
	ACCOUNTID               int64   `json:"accountid"  xorm:"ACCOUNTID"`                             // 用户资金账户
	GOODSID                 string  `json:"goodsid"  xorm:"GOODSID"`                                 // 定投商品ID
	GOODSCODE               string  `json:"goodscode"  xorm:"'GOODSCODE'"`                           // 定投商品代码
	GOODSNAME               string  `json:"goodsname"  xorm:"'GOODSNAME'"`                           // 定投商品名称
	GOODUNITID              int32   `json:"goodunitid"  xorm:"'GOODUNITID'"`                         // 定投商品单位id
	CURRENCYID              int32   `json:"currencyid"  xorm:"'CURRENCYID'"`                         // 币种id

	USERNAME     string `json:"username"`     // 用户名称(userid对应的名称)
	ACCOUNTNAME  string `json:"accountname"`  // 账号名称(ACCOUNTID对应的名称)
	ENUMDICNAME  string `json:"enumdicname"`  // 单位名称
	CURRENCYNAME string `json:"currencyname"` // 币种名称

	FilterStatus string `json:"-"` // 状态过滤条件
}

func (r *QhjRStrategy) calc() {
	r.USERNAME = mtpcache.GetUserNameByUserId(r.USERID)
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.GOODUNITID)
	r.CURRENCYNAME = mtpcache.GetCurrencyName(r.CURRENCYID)
}

func (r *QhjRStrategy) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.REGULARLYCYCLE," +
		"       t.REGULARLYCYCLEVALUE," +
		"       t.REGULARLYMODE," +
		"       t.REGULARLYMODEVALUE," +
		"       t.REMARK," +
		"       t.REGULARLYSTRATEGYSTATUS," +
		"       t.TRIGGERTOTALTIMES," +
		"       t.TRIGGERSUCCESSTIMES," +
		"       t.TRIGGERTOTALQTY," +
		"       t.TRIGGERTOTALAMOUNT," +
		"       t.LASTEFFECTTRADEDATE," +
		"       t.LASTSTOPTRADEDATE," +
		"       t.LASTTRIGGERTRADEDATE," +
		"       to_char(t.NEXTTRIGGERDATE, 'yyyy-mm-dd hh24:mi:ss') NEXTTRIGGERDATE," +
		"       t.NEXTTRIGGERQTY," +
		"       t.NEXTTRIGGERAMOUNT," +
		"       t.UPDATORID," +
		"       t.UPDATESRC," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       t.REGULARLYSTRATEGYID," +
		"       t.USERID," +
		"       t.ACCOUNTID," +
		"       t.GOODSID," +
		"       g.goodscode," +
		"       g.goodsname," +
		"       g.goodunitid," +
		"       ta.accountname" +
		"  FROM QHJ_REGULARLYSTRATEGY t" +
		"  LEFT JOIN GOODS g on t.goodsid=g.goodsid" +
		"  LEFT JOIN TAACCOUNT ta on t.accountid=ta.accountid" +
		" WHERE 1 = 1"
	sqlId.And("t.USERID", r.USERID)
	sqlId.JoinEx(len(r.FilterStatus) > 0, fmt.Sprintf(" and t.REGULARLYSTRATEGYSTATUS in(%v)", r.FilterStatus))
	return sqlId.String()
}

// GetDataEx 获取定投规则策略
func (r *QhjRStrategy) GetDataEx() (interface{}, error) {
	sData := make([]QhjRStrategy, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// QhjRSTriggerLog 定投规则策略触发记录
type QhjRSTriggerLog struct {
	TRADEID             string  `json:"tradeid"  xorm:"TRADEID"`                         // 成交单ID
	LOGID               string  `json:"logid"  xorm:"LOGID"`                             // LogID(SEQ_QHJ_RSTRIGGERLOG)
	REGULARLYSTRATEGYID int64   `json:"regularlystrategyid"  xorm:"REGULARLYSTRATEGYID"` // 定投策略ID
	USERID              int64   `json:"userid"  xorm:"USERID"`                           // 所属用户ID
	ACCOUNTID           int64   `json:"accountid"  xorm:"ACCOUNTID"`                     // 用户资金账户
	GOODSID             string  `json:"goodsid"  xorm:"GOODSID"`                         // 定投商品ID
	REGULARLYCYCLE      int32   `json:"regularlycycle"  xorm:"REGULARLYCYCLE"`           // 定投频率 - 1:每周 2:每月
	REGULARLYCYCLEVALUE string  `json:"regularlycyclevalue"  xorm:"REGULARLYCYCLEVALUE"` // 定投频率值 - 每周几(0-6)  或 每月几号(<=28)
	REGULARLYMODE       int32   `json:"regularlymode"  xorm:"REGULARLYMODE"`             // 定投方式 - 1:按数量 2:按金额
	REGULARLYMODEVALUE  float64 `json:"regularlymodevalue"  xorm:"REGULARLYMODEVALUE"`   // 定投方式值 - 数量或金额
	TRIGGERTIME         string  `json:"triggertime"  xorm:"TRIGGERTIME"`                 // 触发时间
	TRIGGERTRADEDATE    string  `json:"triggertradedate"  xorm:"TRIGGERTRADEDATE"`       // 触发交易日
	TRIGGERQTY          float64 `json:"triggerqty"  xorm:"TRIGGERQTY"`                   // 触发数量
	TRIGGERAMOUNT       float64 `json:"triggeramount"  xorm:"TRIGGERAMOUNT"`             // 触发金额
	TRIGGERSTATUS       int32   `json:"triggerstatus"  xorm:"TRIGGERSTATUS"`             // 触发状态 - 1:成功 2:失败
	RETCODE             string  `json:"retcode"  xorm:"RETCODE"`                         // 错误码
	GOODSCODE           string  `json:"goodscode"  xorm:"'GOODSCODE'"`                   // 定投商品代码
	GOODSNAME           string  `json:"goodsname"  xorm:"'GOODSNAME'"`                   // 定投商品名称
	GOODUNITID          int32   `json:"goodunitid"  xorm:"'GOODUNITID'"`                 // 定投商品单位id
	CURRENCYID          int32   `json:"currencyid"  xorm:"'CURRENCYID'"`                 // 币种id

	USERNAME     string `json:"username"`     // 用户名称(userid对应的名称)
	ACCOUNTNAME  string `json:"accountname"`  // 账号名称(ACCOUNTID对应的名称)
	ENUMDICNAME  string `json:"enumdicname"`  // 单位名称
	CURRENCYNAME string `json:"currencyname"` // 币种名称
}

func (r *QhjRSTriggerLog) calc() {
	r.USERNAME = mtpcache.GetUserNameByUserId(r.USERID)
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.GOODUNITID)
	r.CURRENCYNAME = mtpcache.GetCurrencyName(r.CURRENCYID)
}

func (r *QhjRSTriggerLog) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.TRADEID," +
		"        t.LOGID," +
		"        t.REGULARLYSTRATEGYID," +
		"        t.USERID," +
		"        t.ACCOUNTID," +
		"        t.GOODSID," +
		"        t.REGULARLYCYCLE," +
		"        t.REGULARLYCYCLEVALUE," +
		"        t.REGULARLYMODE," +
		"        t.REGULARLYMODEVALUE," +
		"        to_char(t.TRIGGERTIME, 'yyyy-mm-dd hh24:mi:ss') TRIGGERTIME," +
		"        t.TRIGGERTRADEDATE," +
		"        t.TRIGGERQTY," +
		"        t.TRIGGERAMOUNT," +
		"        t.TRIGGERSTATUS," +
		"        t.RETCODE," +
		"        g.goodscode," +
		"        g.goodsname," +
		"        g.goodunitid," +
		"        ta.accountname," +
		"        ta.currencyid" +
		"   FROM QHJ_RSTRIGGERLOG t" +
		"   LEFT JOIN GOODS g" +
		"     on t.goodsid = g.goodsid" +
		"   LEFT JOIN TAACCOUNT ta" +
		"     on t.accountid = ta.accountid" +
		"  WHERE 1 = 1"
	sqlId.And("t.USERID", r.USERID)
	sqlId.AndEx("t.REGULARLYSTRATEGYID", r.REGULARLYSTRATEGYID, r.REGULARLYSTRATEGYID > 0)
	return sqlId.String()
}

// GetDataEx 获取定投规则策略触发记录
func (r *QhjRSTriggerLog) GetDataEx() (interface{}, error) {
	sData := make([]QhjRSTriggerLog, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// UserReceiveInfo 用户收货信息
type UserReceiveInfo struct {
	AUTOID       int32  `json:"autoid"  xorm:"AUTOID"`             // AutoID
	USERID       int64  `json:"userid"  xorm:"USERID"`             // 用户ID
	RECEIVERNAME string `json:"receivername"  xorm:"RECEIVERNAME"` // 提货人姓名
	CARDTYPEID   int32  `json:"cardtypeid"  xorm:"CARDTYPEID"`     // 证件类型
	CARDNUM      string `json:"cardnum"  xorm:"CARDNUM"`           // 证件号码(加密存储)
	PHONENUM     string `json:"phonenum"  xorm:"PHONENUM"`         // 提货人联系方式(加密存储)
	ADDRESS      string `json:"address"  xorm:"ADDRESS"`           // 提货人详细地址
	TAKEREMARK   string `json:"takeremark"  xorm:"TAKEREMARK"`     // 提货备注
	COUNTRYID    int32  `json:"countryid"  xorm:"COUNTRYID"`       // 国家
	PROVINCEID   int32  `json:"provinceid"  xorm:"PROVINCEID"`     // 省
	CITYID       int32  `json:"cityid"  xorm:"CITYID"`             // 市
	DISTRICTID   int32  `json:"districtid"  xorm:"DISTRICTID"`     // 地区
	ISDEFAULT    int32  `json:"isdefault"  xorm:"ISDEFAULT"`       // 是否默认地址 - 0:否 1:是
	HASENCRYPT   int32  `json:"hasencrypt"  xorm:"HASENCRYPT"`     // 数据是否已加密 - 0:未加密 1:已加密
	COUNTRYNAME  string `json:"countryname"`                       // 国家名称
	CITYNAME     string `json:"cityname"`                          // 城市名称
	PROVINCENAME string `json:"provincename"`                      // 省名称
	DISTRICTNAME string `json:"districtname"`                      // 地区名称
}

func (r *UserReceiveInfo) calc() {
	// 加密过的长度 > 16, 否则是未加密的
	if len(r.CARDNUM) > 16 {
		r.CARDNUM = DecryptField(r.CARDNUM)
	}
	if len(r.PHONENUM) > 16 {
		r.PHONENUM = DecryptField(r.PHONENUM)
	}
	r.COUNTRYNAME = mtpcache.GetDivisionName(r.COUNTRYID)
	r.CITYNAME = mtpcache.GetDivisionName(r.CITYID)
	r.PROVINCENAME = mtpcache.GetDivisionName(r.PROVINCEID)
	r.DISTRICTNAME = mtpcache.GetDivisionName(r.DISTRICTID)
}

func (r *UserReceiveInfo) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.AUTOID," +
		"       t.USERID," +
		"       t.RECEIVERNAME," +
		"       t.CARDTYPEID," +
		"       t.CARDNUM," +
		"       t.PHONENUM," +
		"       t.ADDRESS," +
		"       t.TAKEREMARK," +
		"       t.COUNTRYID," +
		"       t.PROVINCEID," +
		"       t.CITYID," +
		"       t.DISTRICTID," +
		"       t.ISDEFAULT," +
		"       t.HASENCRYPT" +
		"  FROM USERRECEIVEINFO t" +
		" WHERE 1 = 1"
	sqlId.And("t.USERID", r.USERID)
	return sqlId.String()
}

// GetDataEx 获取用户收货信息
func (r *UserReceiveInfo) GetDataEx() (interface{}, error) {
	sData := make([]UserReceiveInfo, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// UserCollectConfig 提现账户设置
type UserCollectConfig struct {
	COLLECTCONFIGID       string `json:"collectconfigid"  xorm:"COLLECTCONFIGID"`             // 收款账户配置ID(SEQ_USERCOLLECTCONFIG)
	USERID                int64  `json:"userid"  xorm:"USERID"`                               // 收款人用户ID
	COLLECTACCOUNTNO      string `json:"collectaccountno"  xorm:"COLLECTACCOUNTNO"`           // 收款人账户
	COLLECTACCOUNTNAME    string `json:"collectaccountname"  xorm:"COLLECTACCOUNTNAME"`       // 收款人账户名称
	COLLECTBANKNAME       string `json:"collectbankname"  xorm:"COLLECTBANKNAME"`             // 收款人开户行
	COLLECTBRANCHBANKNAME string `json:"collectbranchbankname"  xorm:"COLLECTBRANCHBANKNAME"` // 收款人开户支行
	REMARK                string `json:"remark"  xorm:"REMARK"`                               // 备注
	ACCOUNTSTATUS         int32  `json:"accountstatus"  xorm:"ACCOUNTSTATUS"`                 // 账户状态 -  1:正常 2:停用 3:注销
}

func (r *UserCollectConfig) calc() {
	// 加密后的长度是32的倍数
	if len(r.COLLECTACCOUNTNO)%32 == 0 {
		r.COLLECTACCOUNTNO = DecryptField(r.COLLECTACCOUNTNO)
	}
}

func (r *UserCollectConfig) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.COLLECTCONFIGID," +
		"       t.USERID," +
		"       t.COLLECTACCOUNTNO," +
		"       t.COLLECTACCOUNTNAME," +
		"       t.COLLECTBANKNAME," +
		"       t.COLLECTBRANCHBANKNAME," +
		"       t.REMARK," +
		"       t.ACCOUNTSTATUS" +
		"  FROM USERCOLLECTCONFIG t" +
		" WHERE 1 = 1"
	sqlId.And("t.USERID", r.USERID)
	return sqlId.String()
}

// GetDataEx 获取提现账户设置
func (r *UserCollectConfig) GetDataEx() (interface{}, error) {
	sData := make([]UserCollectConfig, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// QhjTradeGoodsPickup 提货单
type QhjTradeGoodsPickup struct {
	AUDITSRC             int32   `json:"auditsrc"  xorm:"AUDITSRC"`                         // 更新来源 - 1:管理端 2:终端
	STOREUSERID          int64   `json:"storeuserid"  xorm:"STOREUSERID"`                   // 门店用户ID (千海金)
	ARTRADEDATE          string  `json:"artradedate"  xorm:"ARTRADEDATE"`                   // 自动收货交易日(yyyyMMdd)
	EXPRESSCOMPANY       string  `json:"expresscompany"  xorm:"EXPRESSCOMPANY"`             // 物流公司
	UPDATETIME           string  `json:"updatetime"  xorm:"UPDATETIME"`                     // 更新时间
	AUDITTRADEDATE       string  `json:"audittradedate"  xorm:"AUDITTRADEDATE"`             // 审核交易日(yyyyMMdd)
	EXPRESSNUM           string  `json:"expressnum"  xorm:"EXPRESSNUM"`                     // 物流单号
	TAKEORDERID          int64   `json:"takeorderid"  xorm:"TAKEORDERID"`                   // 提货单号(905+Unix秒时间戳(10位)+xxxxxx)
	ACCOUNTID            int64   `json:"accountid"  xorm:"ACCOUNTID"`                       // 账户ID
	GOODSID              int64   `json:"goodsid"  xorm:"GOODSID"`                           // 商品ID
	USERID               int64   `json:"userid"  xorm:"USERID"`                             // 用户ID
	QTY                  float64 `json:"qty"  xorm:"QTY"`                                   // 交易商品总手数(交易商品 - 69模式按成交量放大) - 明细表的交易商品手数汇总
	REQTIME              string  `json:"reqtime"  xorm:"REQTIME"`                           // 提货申请时间
	RECIVERNAME          string  `json:"recivername"  xorm:"RECIVERNAME"`                   // 提货人姓名
	CARDTYPEID           int32   `json:"cardtypeid"  xorm:"CARDTYPEID"`                     // 提货人证件类型
	CARDNUM              string  `json:"cardnum"  xorm:"CARDNUM"`                           // 提货人证件号码
	PHONENUM             string  `json:"phonenum"  xorm:"PHONENUM"`                         // 提货人联系方式
	TAKEMODE             int32   `json:"takemode"  xorm:"TAKEMODE"`                         // 提货方式 - 2:自提 3:配送(邮寄)
	ADDRESS              string  `json:"address"  xorm:"ADDRESS"`                           // 提货人详细地址
	TAKEREMARK           string  `json:"takeremark"  xorm:"TAKEREMARK"`                     // 提货备注
	TAKEORDERSTATUS      int32   `json:"takeorderstatus"  xorm:"TAKEORDERSTATUS"`           // 提货状态 -  1:备货中 2:待取货(自提) 3:已取货(自提) 4:待收货(邮寄) 5:已收货(邮寄) 6:自动收货(邮寄) 7:已拒绝 8:已撤销
	AUDITER              int32   `json:"auditer"  xorm:"AUDITER"`                           // 审核人
	AUDITTIME            string  `json:"audittime"  xorm:"AUDITTIME"`                       // 审核时间(发货时间 | 备货完成时间 都是这个字段)
	CONFIRMTIME          string  `json:"confirmtime"  xorm:"'CONFIRMTIME'"`                 // 取货时间 或 收货时间
	CHECKREMARK          string  `json:"checkremark"  xorm:"CHECKREMARK"`                   // 审核备注
	TRADEDATE            string  `json:"tradedate"  xorm:"TRADEDATE"`                       // 交易日(yyyyMMdd)
	MARKETID             int32   `json:"marketid"  xorm:"MARKETID"`                         // 市场ID
	HANDLESTATUS         int32   `json:"handlestatus"  xorm:"HANDLESTATUS"`                 // 处理状态
	AMOUNT               float64 `json:"amount"  xorm:"AMOUNT"`                             // 提货金额
	COUNTRYID            int32   `json:"countryid"  xorm:"COUNTRYID"`                       // 国家
	PROVINCEID           int32   `json:"provinceid"  xorm:"PROVINCEID"`                     // 省
	CITYID               int32   `json:"cityid"  xorm:"CITYID"`                             // 市
	DISTRICTID           int32   `json:"districtid"  xorm:"DISTRICTID"`                     // 地区
	PICKUPGOODSID        string  `json:"pickupgoodsid"  xorm:"PICKUPGOODSID"`               // 提货商品ID
	GOODSAGREEUNIT       float64 `json:"goodsagreeunit"  xorm:"GOODSAGREEUNIT"`             // 交易商品乘数
	PICKUPGOODSAGREEUNIT float64 `json:"pickupgoodsagreeunit"  xorm:"PICKUPGOODSAGREEUNIT"` // 提货商品合约乘数
	PICKUPRATIO          float64 `json:"pickupratio"  xorm:"PICKUPRATIO"`                   // 提货系数
	PICKUPGOODSQTY       float64 `json:"pickupgoodsqty"  xorm:"PICKUPGOODSQTY"`             // 提货商品手数(数量)
	GOODSQTY             float64 `json:"goodsqty"  xorm:"GOODSQTY"`                         // 交易商品手数(69模式按成交量小数位放大取整)
	PREPAREDAYS          int32   `json:"preparedays"  xorm:"PREPAREDAYS"`                   // 备货期(天)-提货单明细表
	PICKUPGOODSNAME      string  `json:"pickupgoodsname"  xorm:"PICKUPGOODSNAME"`           // 提货商品名称
	PICKUPGOODSUNITID    int32   `json:"pickupgoodsunitid"  xorm:"PICKUPGOODSUNITID"`       // 单位ID(提货商品)
	GOODSNAME            string  `json:"goodsname"  xorm:"'GOODSNAME'"`                     // 商品名称
	GOODSCODE            string  `json:"goodscode"  xorm:"'GOODSCODE'"`                     // 商品代码

	ENUMDICNAME   string `json:"enumdicname"`   // 单位名称(提货商品)
	USERNAME      string `json:"username"`      // 用户名称
	STOREUSERNAME string `json:"storeusername"` // 门店用户名称
	COUNTRYNAME   string `json:"countryname"`   // 国家名称
	CITYNAME      string `json:"cityname"`      // 城市名称
	PROVINCENAME  string `json:"provincename"`  // 省名称
	DISTRICTNAME  string `json:"districtname"`  // 地区名称
}

func (r *QhjTradeGoodsPickup) calc() {
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.PICKUPGOODSUNITID)
	r.STOREUSERNAME = mtpcache.GetUserNameByUserId(r.STOREUSERID)
	r.USERNAME = mtpcache.GetUserNameByUserId(r.USERID)
	r.COUNTRYNAME = mtpcache.GetDivisionName(r.COUNTRYID)
	r.CITYNAME = mtpcache.GetDivisionName(r.CITYID)
	r.PROVINCENAME = mtpcache.GetDivisionName(r.PROVINCEID)
	r.DISTRICTNAME = mtpcache.GetDivisionName(r.DISTRICTID)
}

func (r *QhjTradeGoodsPickup) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.AUDITSRC," +
		"       t.STOREUSERID," +
		"       t.ARTRADEDATE," +
		"       t.EXPRESSCOMPANY," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       t.AUDITTRADEDATE," +
		"       t.EXPRESSNUM," +
		"       t.TAKEORDERID," +
		"       t.ACCOUNTID," +
		"       t.GOODSID," +
		"       t.USERID," +
		"       t.QTY," +
		"       to_char(t.REQTIME, 'yyyy-mm-dd hh24:mi:ss') REQTIME," +
		"       t.RECIVERNAME," +
		"       t.CARDTYPEID," +
		"       t.CARDNUM," +
		"       t.PHONENUM," +
		"       t.TAKEMODE," +
		"       decode(t.takemode, 3, t.address, u.address) address," +
		"       t.TAKEREMARK," +
		"       t.TAKEORDERSTATUS," +
		"       t.AUDITER," +
		"       to_char(t.AUDITTIME, 'yyyy-mm-dd hh24:mi:ss') AUDITTIME," +
		"       to_char(t.CONFIRMTIME, 'yyyy-mm-dd hh24:mi:ss') CONFIRMTIME," +
		"       t.CHECKREMARK," +
		"       t.TRADEDATE," +
		"       t.MARKETID," +
		"       t.HANDLESTATUS," +
		"       t.AMOUNT," +
		"       decode(t.takemode, 3, t.COUNTRYID, u.countryid) COUNTRYID," +
		"       decode(t.takemode, 3, t.PROVINCEID, u.provinceid) PROVINCEID," +
		"       decode(t.takemode, 3, t.CITYID, u.cityid) CITYID," +
		"       decode(t.takemode, 3, t.DISTRICTID, u.districtid) DISTRICTID," +
		"       k.PICKUPGOODSID," +
		"       k.GOODSAGREEUNIT," +
		"       k.PICKUPGOODSAGREEUNIT," +
		"       k.PICKUPRATIO," +
		"       k.PICKUPGOODSQTY," +
		"       k.GOODSQTY," +
		"       k.PREPAREDAYS," +
		"       g.PICKUPGOODSNAME," +
		"       g.PICKUPGOODSUNITID," +
		"       g2.goodsname," +
		"       g2.goodscode" +
		"  FROM TRADE_GOODSPICKUP t" +
		"  LEFT JOIN TRADE_GOODSPICKUPDETAIL k" +
		"    on t.takeorderid = k.takeorderid" +
		"  LEFT JOIN PICKUPGOODS g" +
		"    on k.pickupgoodsid = g.pickupgoodsid" +
		"  LEFT JOIN GOODS g2" +
		"    on t.goodsid = g2.goodsid" +
		"  LEFT JOIN USERINFO u" +
		"    on t.storeuserid = u.userid" +
		" WHERE 1 = 1"
	sqlId.And("t.USERID", r.USERID)
	return sqlId.String()
}

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

// QhjPickGoods 提货商品
type QhjPickGoods struct {
	PICKUPGOODSID        string  `json:"pickupgoodsid"  xorm:"PICKUPGOODSID"`               // 提货商品ID
	PICKUPGOODSNAME      string  `json:"pickupgoodsname"  xorm:"PICKUPGOODSNAME"`           // 提货商品名称
	PICKUPGOODSUNITID    int32   `json:"pickupgoodsunitid"  xorm:"PICKUPGOODSUNITID"`       // 单位ID
	PICKUPGOODSAGREEUNIT float64 `json:"pickupgoodsagreeunit"  xorm:"PICKUPGOODSAGREEUNIT"` // 合约乘数
	PICKUPGOODSDESC      string  `json:"pickupgoodsdesc"  xorm:"PICKUPGOODSDESC"`           // 描述
	IMAGEADDRESS         string  `json:"imageaddress"  xorm:"IMAGEADDRESS"`                 // 图片
	REMARK               string  `json:"remark"  xorm:"REMARK"`                             // 备注
	MODIFYSRC            int32   `json:"modifysrc"  xorm:"MODIFYSRC"`                       // 修改来源 - 1:管理端 2:终端
	MODIFIERID           int64   `json:"modifierid"  xorm:"MODIFIERID"`                     // 修改人
	UPDATETIME           string  `json:"updatetime"  xorm:"UPDATETIME"`                     // 更新时间
	PREPAREDAYS          int32   `json:"preparedays"  xorm:"PREPAREDAYS"`                   // 备货期(天)
	PICKUPGOODSSTATUS    int32   `json:"pickupgoodsstatus"  xorm:"PICKUPGOODSSTATUS"`       // 状态 - 1:正常 2:停用 3:注销

	MODIFIERNAME string `json:"modifiername"` // 修改人名称
	FilterStatus string `json:"-"`            // 状态过淲
}

func (r *QhjPickGoods) calc() {
	if r.MODIFYSRC == 1 {
		r.MODIFIERNAME = mtpcache.GetSystemmangerLoginCode(r.MODIFIERID)
	} else {
		r.MODIFIERNAME = mtpcache.GetLoginCodeByLoginId(r.MODIFIERID)
	}
	if r.MODIFIERNAME == "" {
		r.MODIFIERNAME = mtpcache.GetUserNameByUserId(r.MODIFIERID)
	}
}

func (r *QhjPickGoods) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.PICKUPGOODSID," +
		"       t.PICKUPGOODSNAME," +
		"       t.PICKUPGOODSUNITID," +
		"       t.PICKUPGOODSAGREEUNIT," +
		"       to_char(t.PICKUPGOODSDESC) PICKUPGOODSDESC," +
		"       to_char(t.IMAGEADDRESS) IMAGEADDRESS," +
		"       t.REMARK," +
		"       t.MODIFYSRC," +
		"       t.MODIFIERID," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       t.PREPAREDAYS," +
		"       t.PICKUPGOODSSTATUS" +
		"  FROM PICKUPGOODS t" +
		" WHERE 1 = 1"
	if len(r.FilterStatus) > 0 {
		sqlId.Join(fmt.Sprintf(" and t.PICKUPGOODSSTATUS in(%v)", r.FilterStatus))
	}
	return sqlId.String()
}

// GetDataEx 获取提货商品
func (r *QhjPickGoods) GetDataEx() (interface{}, error) {
	sData := make([]QhjPickGoods, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// QhjPickArea 提货门店
type QhjPickArea struct {
	ISRETURNSCORE  int32  `json:"-"  xorm:"ISRETURNSCORE"`       // 是否返还积分 [经纪会员-辽东湾] - 0:不返还 1:返还
	ORGTYPE        int32  `json:"-"  xorm:"ORGTYPE"`             // 组织机构类型 1:机构\分支节点 2:交易员\叶子节点 3:部门\分支节点
	ROLETYPE       int32  `json:"-"  xorm:"ROLETYPE"`            // 角色类型
	SUBORGTYPE     int32  `json:"suborgtype"  xorm:"SUBORGTYPE"` // 子机构类型(千海金) - 0:机构 1:代理 2:门店
	CREATORID      int64  `json:"-"  xorm:"CREATORID"`           // 创建人ID
	FROMUSERID     int64  `json:"fromuserid"  xorm:"FROMUSERID"` // 所属机构(组织机构)( OrgType = 1时为自己， = 2\3时取上级机构的所属机构)
	MODIFIERID     int64  `json:"-"  xorm:"MODIFIERID"`          // 修改人ID
	PARENTUSERID   int64  `json:"-"  xorm:"PARENTUSERID"`        // 上级组织机构(组织机构)
	ROOTUSERID     int64  `json:"-"  xorm:"ROOTUSERID"`          // 顶级组织机构用户ID(组织机构)
	TRADEACCOUNTID int64  `json:"-"  xorm:"TRADEACCOUNTID"`      // 默认交易资金账号ID(做市会员被动接单使用、商城店铺默认资金账 号、圈内会员默认资金账号)  - 作废， 使用UserReckonAccount
	USERID         int64  `json:"userid"  xorm:"USERID"`         // 机构用户ID 其他做市关系，都设置为 1
	CREATETIME     string `json:"-"  xorm:"CREATETIME"`          // 创建时间
	MODIFYTIME     string `json:"-"  xorm:"MODIFYTIME"`          // 修改时间
	OUTUSERID      string `json:"-"  xorm:"OUTUSERID"`           // 外部账户[场外期权做市商]
	SHOPPASSWORD   string `json:"-"  xorm:"SHOPPASSWORD"`        // 商城店铺登陆密码(商城店铺角色使用)
	SUBLEVELPATH   string `json:"-"  xorm:"SUBLEVELPATH"`        // 组织机构层级路径(逗号分隔,首尾加逗号)
	TRANSURL       string `json:"-"  xorm:"TRANSURL"`            // 物流地址(物流机构)
	COUNTRYID      int32  `json:"countryid"  xorm:"COUNTRYID"`   // 国家
	PROVINCEID     int32  `json:"provinceid"  xorm:"PROVINCEID"` // 省
	CITYID         int32  `json:"cityid"  xorm:"CITYID"`         // 市
	DISTRICTID     int32  `json:"districtid"  xorm:"DISTRICTID"` // 地区
	ADDRESS        string `json:"address"  xorm:"'ADDRESS'"`     // 详细地址

	USERNAME     string `json:"username"`     // 机构用户名称(门店门称)
	FROMUSERNAME string `json:"fromusername"` // 所属机构名称
	COUNTRYNAME  string `json:"countryname"`  // 国家名称
	CITYNAME     string `json:"cityname"`     // 城市名称
	PROVINCENAME string `json:"provincename"` // 省名称
	DISTRICTNAME string `json:"districtname"` // 地区名称
}

func (r *QhjPickArea) calc() {
	r.USERNAME = mtpcache.GetUserNameByUserId(r.USERID)
	r.FROMUSERNAME = mtpcache.GetUserNameByUserId(r.FROMUSERID)
	r.COUNTRYNAME = mtpcache.GetDivisionName(r.COUNTRYID)
	r.CITYNAME = mtpcache.GetDivisionName(r.CITYID)
	r.PROVINCENAME = mtpcache.GetDivisionName(r.PROVINCEID)
	r.DISTRICTNAME = mtpcache.GetDivisionName(r.DISTRICTID)
}

func (r *QhjPickArea) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.FROMUSERID," +
		"       t.SUBORGTYPE," +
		"       t.USERID," +
		"       t.ROLETYPE," +
		"       t.TRADEACCOUNTID," +
		"       to_char(t.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') CREATETIME," +
		"       t.CREATORID," +
		"       to_char(t.MODIFYTIME, 'yyyy-mm-dd hh24:mi:ss') MODIFYTIME," +
		"       t.MODIFIERID," +
		"       t.SHOPPASSWORD," +
		"       t.ISRETURNSCORE," +
		"       t.TRANSURL," +
		"       t.OUTUSERID," +
		"       t.PARENTUSERID," +
		"       t.ROOTUSERID," +
		"       t.SUBLEVELPATH," +
		"       t.ORGTYPE," +
		"       u.countryid," +
		"       u.provinceid," +
		"       u.cityid," +
		"       u.districtid," +
		"       u.address" +
		"  FROM AREAROLE t" +
		"  LEFT JOIN USERINFO u on t.userid=u.userid" +
		" WHERE 1 = 1"
	sqlId.And("t.SUBORGTYPE", 2)
	return sqlId.String()
}

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

// QhjBankAccountSign 签约银行
type QhjBankAccountSign struct {
	ACCOUNTTYPE      int32  `json:"accounttype"  xorm:"ACCOUNTTYPE"`           // 账户类型(UserType)
	BANKACCOUNTTYPE  int32  `json:"bankaccounttype"  xorm:"BANKACCOUNTTYPE"`   // 银行账户类型 - 1-对私; 2-对公
	BANKCARDTYPE     int32  `json:"bankcardtype"  xorm:"BANKCARDTYPE"`         // 银行卡类型
	DIRECT           int32  `json:"direct"  xorm:"DIRECT"`                     // 方向(可以判断那方先发起的签约)
	SIGNSTATUS       int32  `json:"signstatus"  xorm:"SIGNSTATUS"`             // 签约状态 - 1:未签约 2:签约待审核 3:签约中 4:已签约 5:解约待审核 6:解约中 7:已解约 8:已解绑 9:绑卡中 10:审核拒绝 11:换签待审核
	AUDITID          int64  `json:"auditid"  xorm:"AUDITID"`                   // 审核人
	ACCOUNTCODE      string `json:"accountcode"  xorm:"ACCOUNTCODE"`           // 资金账号
	ACCOUNTNAME      string `json:"accountname"  xorm:"ACCOUNTNAME"`           // 资金账号对应的账号名(UserName)
	APPLICATIONTIME  string `json:"applicationtime"  xorm:"APPLICATIONTIME"`   // 最后一次更新的时间
	APPLYEXCHTICKET  string `json:"applyexchticket"  xorm:"APPLYEXCHTICKET"`   // 申请流水
	AUDITTIME        string `json:"audittime"  xorm:"AUDITTIME"`               // 审核时间
	BANKACCOUNTNAME  string `json:"bankaccountname"  xorm:"BANKACCOUNTNAME"`   // 签约银行卡账号名
	BANKACCOUNTNO    string `json:"bankaccountno"  xorm:"BANKACCOUNTNO"`       // 银行卡号
	BANKCHILDACCOUNT string `json:"bankchildaccount"  xorm:"BANKCHILDACCOUNT"` // 银行子账号（签约成功后，很多银行会返回对应的这个虚拟账号）
	BANKCITY         string `json:"bankcity"  xorm:"BANKCITY"`                 // 开户行城市
	BANKID           string `json:"bankid"  xorm:"BANKID"`                     // 签约银行ID
	BANKPROVINCE     string `json:"bankprovince"  xorm:"BANKPROVINCE"`         // 开户行省份
	BRANCHBANKID     string `json:"branchbankid"  xorm:"BRANCHBANKID"`         // 签约银行支行号
	BRANCHBANKNAME   string `json:"branchbankname"  xorm:"BRANCHBANKNAME"`     // 签约银行支行名称
	CARDNO           string `json:"cardno"  xorm:"CARDNO"`                     // 证件号码
	CARDTYPE         string `json:"cardtype"  xorm:"CARDTYPE"`                 // 证件类型
	CURRENCY         string `json:"currency"  xorm:"CURRENCY"`                 // 币种
	CUSBANKID        string `json:"cusbankid"  xorm:"CUSBANKID"`               // 托管银行编号
	EXCHTICKET       string `json:"exchticket"  xorm:"EXCHTICKET"`             // 最后一次签约成功的流水号
	EXTENDINFO       string `json:"extendinfo"  xorm:"EXTENDINFO"`             // 扩展信息(JSON字符串)
	MOBILEPHONE      string `json:"mobilephone"  xorm:"MOBILEPHONE"`           // 手机号
	NETADDR          string `json:"netaddr"  xorm:"NETADDR"`                   // 调转网址
	UPDATETIME       string `json:"updatetime"  xorm:"UPDATETIME"`             // 更新时间(签解约更新时间)
	RELATEDUSERID    int64  `json:"relateduserid"  xorm:"'RELATEDUSERID'"`     // 关联使用者userid
}

func (r *QhjBankAccountSign) calc() {
	r.ACCOUNTNAME = DecryptField(r.ACCOUNTNAME)
	r.BANKACCOUNTNAME = DecryptField(r.BANKACCOUNTNAME)
	r.CARDNO = DecryptField(r.CARDNO)
	r.BANKACCOUNTNO = DecryptField(r.BANKACCOUNTNO)
	r.MOBILEPHONE = DecryptField(r.MOBILEPHONE)
}

func (r *QhjBankAccountSign) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.ACCOUNTCODE," +
		"       t.CUSBANKID," +
		"       t.SIGNSTATUS," +
		"       t.CURRENCY," +
		"       t.ACCOUNTTYPE," +
		"       t.ACCOUNTNAME," +
		"       t.BANKCARDTYPE," +
		"       t.BANKACCOUNTNO," +
		"       t.BANKACCOUNTTYPE," +
		"       t.BANKCHILDACCOUNT," +
		"       t.BANKID," +
		"       t.BRANCHBANKID," +
		"       t.BRANCHBANKNAME," +
		"       t.BANKACCOUNTNAME," +
		"       t.CARDTYPE," +
		"       t.CARDNO," +
		"       t.BANKPROVINCE," +
		"       t.BANKCITY," +
		"       t.DIRECT," +
		"       t.MOBILEPHONE," +
		"       t.EXTENDINFO," +
		"       t.EXCHTICKET," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       to_char(t.APPLICATIONTIME, 'yyyy-mm-dd hh24:mi:ss') APPLICATIONTIME," +
		"       t.NETADDR," +
		"       t.AUDITID," +
		"       to_char(t.AUDITTIME, 'yyyy-mm-dd hh24:mi:ss') AUDITTIME," +
		"       t.APPLYEXCHTICKET," +
		"       ta.accountid," +
		"       ta.relateduserid" +
		"  FROM BANK_ACCOUNTSIGN t" +
		" INNER JOIN TAACCOUNT ta" +
		"    on t.accountcode = to_char(ta.accountid)" +
		" WHERE 1 = 1 and ta.taaccounttype = 2"
	sqlId.And("ta.RELATEDUSERID", r.RELATEDUSERID)
	return sqlId.String()
}

// GetDataEx 获取签约银行
func (r *QhjBankAccountSign) GetDataEx() (interface{}, error) {
	sData := make([]QhjBankAccountSign, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// QhjBankInfo 银行信息列表
type QhjBankInfo struct {
	STATUS      int32  `json:"status"  xorm:"STATUS"`           // 状态 - 0:正常 1:注销
	ORDERINDEX  int64  `json:"orderindex"  xorm:"ORDERINDEX"`   // 排序顺序
	BANKID      string `json:"bankid"  xorm:"BANKID"`           // 银行ID
	BANKNAME    string `json:"bankname"  xorm:"BANKNAME"`       // 银行名称
	CLEARBANKNO string `json:"clearbankno"  xorm:"CLEARBANKNO"` // 清算系统银行编号
}

func (r *QhjBankInfo) calc() {

}

func (r *QhjBankInfo) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.BANKID,t.BANKNAME,t.CLEARBANKNO,t.STATUS,t.ORDERINDEX FROM BANK_BANKINFO t WHERE 1=1 and t.status=0"

	return sqlId.String()
}

// GetData 获取银行信息列表
func (r *QhjBankInfo) GetData() ([]QhjBankInfo, error) {
	sData := make([]QhjBankInfo, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// GetDataEx 获取银行信息列表
func (r *QhjBankInfo) GetDataEx() (interface{}, error) {
	return r.GetData()
}

// QhjReckonPriceLog 结算价(定投价)
type QhjReckonPriceLog struct {
	LASTRECKONPRICE  float64 `json:"lastreckonprice"  xorm:"LASTRECKONPRICE"`      // 上日结算价
	NEXTDNLIMITPRICE float64 `json:"nextdnlimitprice"  xorm:"NEXTDNLIMITPRICE"`    // 下个交易日跌停价
	NEXTUPLIMITPRICE float64 `json:"nextuplimitprice"  xorm:"NEXTUPLIMITPRICE"`    // 下个交易日涨停价
	RECKONPRICE      float64 `json:"reckonprice"  xorm:"RECKONPRICE"`              // 今日结算价(定投价)
	UPDOWNRATE       float64 `json:"updownrate"  xorm:"UPDOWNRATE"`                // 今日涨跌幅
	UPDOWNRATE_2D    float64 `json:"updownrate_2d"  xorm:"UPDOWNRATE_2D"`          // 连续两日涨跌幅
	UPDOWNRATE_3D    float64 `json:"updownrate_3d"  xorm:"UPDOWNRATE_3D"`          // 连续三日涨跌幅
	TOTALHOLDERQTY   int32   `json:"totalholderqty"  xorm:"TOTALHOLDERQTY"`        // 总持仓数
	TOTALSTOCK       int32   `json:"totalstock"  xorm:"TOTALSTOCK"`                // 总库存
	UPDOWNCOUNT      int32   `json:"updowncount"  xorm:"UPDOWNCOUNT"`              // 连续涨(跌)停天数 (涨跌停标志变为0时值归0，变成涨停或是跌停时值为1，连续涨停天数+1，连续跌停天数+1)
	UPDOWNFLAG       int32   `json:"updownflag"  xorm:"UPDOWNFLAG"`                // 连续涨跌停标志 - 0:无涨跌停 -1:跌停 1:涨停
	GOODSID          int64   `json:"goodsid"  xorm:"GOODSID" form:"goodsid"`       // 商品ID
	NEXTTRADE        string  `json:"nexttrade"  xorm:"NEXTTRADE"`                  // 下个交易日
	RECKONTIME       string  `json:"reckontime"  xorm:"RECKONTIME"`                // 结算时间
	TRADEDATE        string  `json:"tradedate"  xorm:"TRADEDATE" form:"tradedate"` // 交易日
}

func (r *QhjReckonPriceLog) calc() {

}

func (r *QhjReckonPriceLog) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.TRADEDATE," +
		"       t.GOODSID," +
		"       to_char(t.RECKONTIME, 'yyyy-mm-dd hh24:mi:ss') RECKONTIME," +
		"       t.LASTRECKONPRICE," +
		"       t.RECKONPRICE," +
		"       t.UPDOWNRATE," +
		"       t.UPDOWNFLAG," +
		"       t.UPDOWNCOUNT," +
		"       t.UPDOWNRATE_2D," +
		"       t.UPDOWNRATE_3D," +
		"       t.NEXTTRADE," +
		"       t.NEXTUPLIMITPRICE," +
		"       t.NEXTDNLIMITPRICE," +
		"       t.TOTALHOLDERQTY," +
		"       t.TOTALSTOCK," +
		"       g.goodsname," +
		"       g.goodscode" +
		"  FROM RECKON_PRICELOG t" +
		"  LEFT JOIN GOODS g on t.goodsid=g.goodsid" +
		"  INNER JOIN MARKET m on g.marketid=g.marketid" +
		" WHERE 1 = 1 and m.marketid=69201"
	sqlId.AndEx("t.TRADEDATE", r.TRADEDATE, len(r.TRADEDATE) > 0)
	sqlId.AndEx("t.GOODSID", r.GOODSID, r.GOODSID > 0)
	return sqlId.String()
}

// GetDataEx 获取结算价(定投价)
func (r *QhjReckonPriceLog) GetDataEx() (interface{}, error) {
	sData := make([]QhjReckonPriceLog, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// QhjCustomer 客户资料
type QhjCustomer struct {
	USERID            int64  `json:"userid"  xorm:"'USERID'"`                       // 用户ID
	MEMBERUSERID      int64  `json:"memberuserid"  xorm:"'MEMBERUSERID'"`           // 所属机构ID(所属会员)
	PARENTUSERID      int64  `json:"parentuserid"  xorm:"'PARENTUSERID'"`           // 上级机构(所属机构)
	USERINFOTYPE      int32  `json:"userinfotype"  xorm:"'USERINFOTYPE'"`           // 客户类型 1-个人 2-企业
	CUSTOMERNAME      string `json:"customername"  xorm:"'CUSTOMERNAME'"`           // 客户名称
	CARDTYPE          int32  `json:"cardtype"  xorm:"'CARDTYPE'"`                   // 证件类型
	CARDNUM           string `json:"cardnum"  xorm:"'CARDNUM'"`                     // 证件号码
	MOBILE            string `json:"mobile"  xorm:"'MOBILE'"`                       // 手机号码
	TELPHONE          string `json:"telphone"  xorm:"'TELPHONE'"`                   // 联系电话
	ADDRESS           string `json:"address"  xorm:"'ADDRESS'"`                     // 通讯地址
	REMARK            string `json:"remark"  xorm:"'REMARK'"`                       // 备注
	STATUS            int32  `json:"status"  xorm:"'STATUS'"`                       // 账户状态 -  1:待激活 2:待审核 3:待复审 4:正常 5:审核拒绝 6:注销
	STATUSDESC        string `json:"statusdesc"`                                    // 账户状态中文描述
	ATTACHMENT1       string `json:"attachment1"  xorm:"'ATTACHMENT1'"`             // 附件1
	CARDFRONTPHOTOURL string `json:"cardfrontphotourl"  xorm:"'CARDFRONTPHOTOURL'"` // 证件正面图片地址
	CARDBACKPHOTOURL  string `json:"cardbackphotourl"  xorm:"'CARDBACKPHOTOURL'"`   // 证件反面图片地址
	COUNTRYID         int32  `json:"countryid"  xorm:"'COUNTRYID'"`                 // 国家
	PROVINCEID        int32  `json:"provinceid"  xorm:"'PROVINCEID'"`               // 省
	CITYID            int32  `json:"cityid"  xorm:"'CITYID'"`                       // 市
	DISTRICTID        int32  `json:"districtid"  xorm:"'DISTRICTID'"`               // 区域
	CREATETIME        string `json:"createtime"  xorm:"'CREATETIME'"`               // 创建时间
	MODIFYTIME        string `json:"modifytime"  xorm:"'MODIFYTIME'"`               // 修改时间
	AUDITTIME         string `json:"audittime"  xorm:"'AUDITTIME'"`                 // 审核时间
	NICKNAME          string `json:"nickname"  xorm:"'NICKNAME'"`                   // 昵称
	TAXPAYERNUM       string `json:"taxpayernum"  xorm:"'taxpayernum'"`             // 纳税人识别号
	LEGALPERSONNAME   string `json:"legalpersonname"  xorm:"'legalpersonname'"`     // 法人姓名(企业)
	CONTACTNAME       string `json:"contactname"  xorm:"'contactname'"`             // 联系人
	EMAIL             string `json:"email"  xorm:"'EMAIL'"`                         // 邮件

	COUNTRYNAME    string `json:"countryname"`    // 国家名称
	CITYNAME       string `json:"cityname"`       // 城市名称
	PROVINCENAME   string `json:"provincename"`   // 省名称
	DISTRICTNAME   string `json:"districtname"`   // 地区名称
	CARDTYPENAME   string `json:"cardtypename"`   // 证件类型名称
	MEMBERUSERNAME string `json:"memberusername"` // 所属会员名称
	USERNAME       string `json:"username"`       // 用户名称
	PARENTUSERNAME string `json:"parentusername"` //所属机构名称

	QUERYTYPE  int32 `json:"-"` // 查询类型(1:未提交(网上开户表) 2:待审核(网上开户表) 3:正常 4:停用)
	IncludeSub int32 `json:"-"` // 包子机构客户 1-包含
}

func (r *QhjCustomer) calc() {
	r.MEMBERUSERNAME = mtpcache.GetUserNameByUserId(r.MEMBERUSERID)
	r.USERNAME = mtpcache.GetUserNameByUserId(r.USERID)
	r.PARENTUSERNAME = mtpcache.GetUserNameByUserId(r.PARENTUSERID)
	r.COUNTRYNAME = mtpcache.GetDivisionName(r.COUNTRYID)
	r.CITYNAME = mtpcache.GetDivisionName(r.CITYID)
	r.PROVINCENAME = mtpcache.GetDivisionName(r.PROVINCEID)
	r.DISTRICTNAME = mtpcache.GetDivisionName(r.DISTRICTID)
}

func (r *QhjCustomer) level() int32 {
	if r.IncludeSub > 0 {
		// 层级, 最多20层
		return 20
	}
	return 1
}

func (r *QhjCustomer) buildSql() string {
	if r.QUERYTYPE == 1 || r.QUERYTYPE == 2 {
		return r.buildSqlWskh()
	}
	return r.buildSqlNormal()
}

func (r *QhjCustomer) buildSqlWskh() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select t.userid, wm_concat(t.logincode) logincode" +
		"    from loginaccount t" +
		"   group by t.userid)" +
		"select a.*, k.logincode" +
		"  from (select t.userid," +
		"               t.memberareaid MEMBERUSERID," +
		"               t.USERINFOTYPE," +
		"               t.CUSTOMERNAME," +
		"               t.NICKNAME," +
		"               t.CARDTYPE," +
		"               t.CARDNUM," +
		"               t.mobilephone MOBILE," +
		"               t.TELPHONE," +
		"               t.cardaddress ADDRESS," +
		"               t.REMARK," +
		"               t.ATTACHMENT1," +
		"               t.CARDFRONTPHOTOURL," +
		"               t.CARDBACKPHOTOURL," +
		"               t.userstate status," +
		"               t.countryid," +
		"               t.provinceid," +
		"               t.districtid," +
		"               to_char(t.createtime, 'yyyy-mm-dd hh24:mi:ss') createtime," +
		"               t.cityid," +
		"               to_char(t.modifiedtime, 'yyyy-mm-dd hh24:mi:ss') modifytime," +
		"               to_char(t.auditime, 'yyyy-mm-dd hh24:mi:ss') audittime," +
		"               t.taxpayernum," +
		"               t.legalpersonname," +
		"               t.contactname," +
		"               t.email," +
		"               t.areaid parentuserid" +
		"          from wskh_userinfo t" +
		"         start with t.areaid = %v" +
		"        connect by prior t.userid = t.areaid" +
		"               and t.usertype = 6" +
		"               and level <= %v) a" +
		"  left join tmp k" +
		"    on a.userid = k.userid" +
		" where 1 = 1 and a.status in(%v)"
	status := DecodeStr(r.QUERYTYPE == 1, "1", "2,4,5")
	sqlId.FormatParam(r.USERID, r.level(), status)
	sqlId.AndEx("a.USERINFOTYPE", r.USERINFOTYPE, r.USERINFOTYPE > 0)
	sqlId.JoinEx(len(r.CUSTOMERNAME) > 0, fmt.Sprintf(` and a.customername like '%%%v%%'`, r.CUSTOMERNAME))
	sqlId.JoinEx(len(r.NICKNAME) > 0, fmt.Sprintf(` and a.nickname like '%%%v%%'`, r.NICKNAME))
	return sqlId.String()
}

func (r *QhjCustomer) buildSqlNormal() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select t.userid, wm_concat(t.logincode) logincode" +
		"    from loginaccount t" +
		"   group by t.userid)" +
		"select a.*," +
		"       k.logincode," +
		"       u.USERINFOTYPE," +
		"       u.CUSTOMERNAME," +
		"       u.NICKNAME," +
		"       u.CARDTYPEID CARDTYPE," +
		"       u.CARDNUM," +
		"       u.MOBILE," +
		"       u.TELPHONE," +
		"       u.ADDRESS," +
		"       u.REMARK," +
		"       u.ATTACHMENT1," +
		"       u.CARDFRONTPHOTOURL," +
		"       u.CARDBACKPHOTOURL," +
		"       u.countryid," +
		"       u.provinceid," +
		"       u.districtid," +
		"       u.taxpayernum," +
		"       u.legalpersonname," +
		"       u.contactname," +
		"       u.email," +
		"       u.cityid" +
		"  from (select t.userid," +
		"               t.usertype," +
		"               t.parentuserid," +
		"               t.memberuserid," +
		"               t.accountstatus status," +
		"               to_char(t.createtime, 'yyyy-mm-dd hh24:mi:ss') createtime," +
		"               to_char(t.modifytime, 'yyyy-mm-dd hh24:mi:ss') modifytime," +
		"               to_char(t.audittime, 'yyyy-mm-dd hh24:mi:ss') audittime" +
		"          from useraccount t" +
		"         start with t.usertype=6 and t.parentuserid = %v" +
		"        connect by prior t.userid = t.parentuserid" +
		"               and t.usertype = 6" +
		"               and level <= %v) a" +
		"  left join tmp k" +
		"    on a.userid = k.userid" +
		"  left join userinfo u" +
		"    on a.userid = u.userid" +
		" where 1=1 and a.status in(%v)"
	status := DecodeStr(r.QUERYTYPE == 3, "4", "6")
	sqlId.FormatParam(r.USERID, r.level(), status)
	sqlId.AndEx("u.USERINFOTYPE", r.USERINFOTYPE, r.USERINFOTYPE > 0)
	sqlId.JoinEx(len(r.CUSTOMERNAME) > 0, fmt.Sprintf(` and u.customername like '%%%v%%'`, r.CUSTOMERNAME))
	sqlId.JoinEx(len(r.NICKNAME) > 0, fmt.Sprintf(` and u.nickname like '%%%v%%'`, r.NICKNAME))
	return sqlId.String()
}

// GetDataEx 获取客户资料
func (r *QhjCustomer) GetDataEx() (interface{}, error) {
	sData := make([]QhjCustomer, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// QhjCusBankInfo 托管银行信息
type QhjCusBankInfo struct {
	CUSBANKID          string `json:"cusbankid"  xorm:"CUSBANKID"`                   // 托管银行编号(对应清算中心TRAN_NO)
	CUSBANKNAME        string `json:"cusbankname"  xorm:"CUSBANKNAME"`               // 银行业务名称
	CUSBANKSHORTNAME   string `json:"cusbankshortname"  xorm:"CUSBANKSHORTNAME"`     // 银行业务简称
	EXCHBANKID         string `json:"exchbankid"  xorm:"EXCHBANKID"`                 // 交易所开户银行ID
	EXCHPAYBANKACCNO   string `json:"exchpaybankaccno"  xorm:"EXCHPAYBANKACCNO"`     // 交易所支出结算账户
	EXCHPAYBANKACCNAME string `json:"exchpaybankaccname"  xorm:"EXCHPAYBANKACCNAME"` // 交易所支出结算账户户名
	CURRENCY           string `json:"currency"  xorm:"CURRENCY"`                     // 币种
	CANSIGN            int32  `json:"cansign"  xorm:"CANSIGN"`                       // 是否可签约 - 0:不可 1:可以
	CANRELEASE         int32  `json:"canrelease"  xorm:"CANRELEASE"`                 // 是否可解约 - 0:不可 1:可以
	CANINAMOUNT        int32  `json:"caninamount"  xorm:"CANINAMOUNT"`               // 是否可入金 - 0:不可 1:可以
	CANOUTAMOUNT       int32  `json:"canoutamount"  xorm:"CANOUTAMOUNT"`             // 是否可出金 - 0:不可 1:可以
	RISKINFO           string `json:"riskinfo"  xorm:"RISKINFO"`                     // 签约风险提示书
	CANSIGN2           int32  `json:"cansign2"  xorm:"CANSIGN2"`                     // 是否可签约(网银端) - 0:不可 1:可以
	CANRELEASE2        int32  `json:"canrelease2"  xorm:"CANRELEASE2"`               // 是否可解约(网银端) - 0:不可 1:可以
	CANINAMOUNT2       int32  `json:"caninamount2"  xorm:"CANINAMOUNT2"`             // 是否可入金(网银端) - 0:不可 1:可以
	CANOUTAMOUNT2      int32  `json:"canoutamount2"  xorm:"CANOUTAMOUNT2"`           // 是否可出金(网银端) - 0:不可 1:可以
	CANBINDCARD        int32  `json:"canbindcard"  xorm:"CANBINDCARD"`               // 是否可绑卡与解卡 - 0:不可 1:可以
	ISNEEDCLEAR        int32  `json:"isneedclear"  xorm:"ISNEEDCLEAR"`               // 是否需要清算 - 0:不需要 1:需要
	CANMODIFYPHONE     int32  `json:"canmodifyphone"  xorm:"CANMODIFYPHONE"`         // 是否可修改手机号 - 0:不可 1:可以
	CANMODIFYSIGNINFO  int32  `json:"canmodifysigninfo"  xorm:"CANMODIFYSIGNINFO"`   // 是否可修改签约信息 - 0:不可 1:可以
	ISNEEDCHECK        int32  `json:"isneedcheck"  xorm:"ISNEEDCHECK"`               // 是否需要对账 - 0:需要 1:不需要 [不需要界面配置]
	CANMODIFYBANKCARD  int32  `json:"canmodifybankcard"  xorm:"CANMODIFYBANKCARD"`   // 是否可修改银行卡号 - 0:不可 1:可以
	CANALLOT           int32  `json:"canallot"  xorm:"CANALLOT"`                     // 是否支持跨行调拨 - 0:不支持 1:支持
	CANALLOTQUERY      int32  `json:"canallotquery"  xorm:"CANALLOTQUERY"`           // 是否支持跨行调拨查询 - 0:不支持 1:支持
}

func (r *QhjCusBankInfo) calc() {

}

func (r *QhjCusBankInfo) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.CUSBANKID," +
		"       t.CUSBANKNAME," +
		"       t.CUSBANKSHORTNAME," +
		"       t.EXCHBANKID," +
		"       t.EXCHPAYBANKACCNO," +
		"       t.EXCHPAYBANKACCNAME," +
		"       t.CURRENCY," +
		"       t.CANSIGN," +
		"       t.CANRELEASE," +
		"       t.CANINAMOUNT," +
		"       t.CANOUTAMOUNT," +
		"       to_char(t.RISKINFO) RISKINFO," +
		"       t.CANSIGN2," +
		"       t.CANRELEASE2," +
		"       t.CANINAMOUNT2," +
		"       t.CANOUTAMOUNT2," +
		"       t.CANBINDCARD," +
		"       t.ISNEEDCLEAR," +
		"       t.CANMODIFYPHONE," +
		"       t.CANMODIFYSIGNINFO," +
		"       t.ISNEEDCHECK," +
		"       t.CANMODIFYBANKCARD," +
		"       t.CANALLOT," +
		"       t.CANALLOTQUERY" +
		"  FROM BANK_CUSBANKINFO t" +
		" WHERE 1 = 1"
	return sqlId.String()
}

// GetData 获取托管银行信息
func (r *QhjCusBankInfo) GetData() ([]QhjCusBankInfo, error) {
	sData := make([]QhjCusBankInfo, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// GetDataEx 获取托管银行信息
func (r *QhjCusBankInfo) GetDataEx() (interface{}, error) {
	return r.GetData()
}

// QhjCusBankSignBank 托管银行签约关系
type QhjCusBankSignBank struct {
	CUSBANKID string `json:"cusbankid"  xorm:"CUSBANKID"` // 托管银行ID
	BANKID    string `json:"bankid"  xorm:"BANKID"`       // 银行ID
}

func (r *QhjCusBankSignBank) calc() {

}

func (r *QhjCusBankSignBank) buildSql() string {
	var sqlId utils.SQLVal = "select * from bank_cusbanksignbank"
	return sqlId.String()
}

// GetData 获取托管银行签约关系
func (r *QhjCusBankSignBank) GetData() ([]QhjCusBankSignBank, error) {
	sData := make([]QhjCusBankSignBank, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// GetDataEx 获取托管银行签约关系
func (r *QhjCusBankSignBank) GetDataEx() (interface{}, error) {
	return r.GetData()
}

// QhjCusBankSignBankEx 托管银行签约银行详细
type QhjCusBankSignBankEx struct {
	QhjCusBankInfo
	Banklst []QhjBankInfo // 银行列表
}

// GetDataEx 获取托管银行签约银行详细
func (r *QhjCusBankSignBankEx) GetDataEx() (interface{}, error) {
	sData := make([]QhjCusBankSignBankEx, 0)
	a := QhjCusBankSignBank{}
	relateData, _ := a.GetData()
	if len(relateData) == 0 {
		return sData, nil
	}
	b := QhjCusBankInfo{}
	bData, _ := b.GetData()
	c := QhjBankInfo{}
	cData, _ := c.GetData()
	for _, vb := range bData {
		// 托管银行
		v := QhjCusBankSignBankEx{QhjCusBankInfo: vb, Banklst: make([]QhjBankInfo, 0)}
		for _, vr := range relateData {
			// 托管关系
			if vb.CUSBANKID == vr.CUSBANKID {
				for _, vc := range cData {
					if vc.BANKID == vr.BANKID {
						v.Banklst = append(v.Banklst, vc)
					}
				}
			}
		}
		if len(v.Banklst) > 0 {
			sData = append(sData, v)
		}
	}
	return sData, nil
}

// QhjSiteColumnDetail 资讯
type QhjSiteColumnDetail struct {
	ID          int64  `json:"id"  xorm:"ID"`                   // ID(SEQ_SITE_COLUMNDETAIL)
	COLUMNID    int64  `json:"columnid"  xorm:"COLUMNID"`       // 所属栏目
	USERID      int64  `json:"userid"  xorm:"USERID"`           // 用户ID(默认为0， 核心为为机构用户ID)
	TITLE       string `json:"title"  xorm:"TITLE"`             // 标题
	COVERIMAGE  string `json:"coverimage"  xorm:"COVERIMAGE"`   // 封面图片
	AUTHOR      string `json:"author"  xorm:"AUTHOR"`           // 作者
	BRIEF       string `json:"brief"  xorm:"BRIEF"`             // 简介
	CONTEXT     string `json:"context"  xorm:"CONTEXT"`         // 内容
	STATUS      int32  `json:"status"  xorm:"STATUS"`           // 状态 - 1:暂存 10:待审核 20:审核通过 30:审核拒绝
	ISTOP       int32  `json:"istop"  xorm:"ISTOP"`             // 是否置顶 - 0:不置 1:置顶
	HITS        int64  `json:"hits"  xorm:"HITS"`               // 点击数
	PUBLISHDATE string `json:"publishdate"  xorm:"PUBLISHDATE"` // 发布日期
	CREAEDATE   string `json:"creaedate"  xorm:"CREAEDATE"`     // 创建时间
	CREATORID   int64  `json:"creatorid"  xorm:"CREATORID"`     // 创建人
	UPDATEDATE  string `json:"updatedate"  xorm:"UPDATEDATE"`   // 修改时间
	MODIFIERID  int64  `json:"modifierid"  xorm:"MODIFIERID"`   // 操作人
	ISSHOW      int32  `json:"isshow"  xorm:"ISSHOW"`           // 是否展示 - 0:不展示 1:展示

	USERNAME string `json:"username"` // 用户名称
}

func (r *QhjSiteColumnDetail) calc() {
	if r.USERID > 0 {
		r.USERNAME = mtpcache.GetUserNameByUserId(r.USERID)
	}
}

func (r *QhjSiteColumnDetail) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.ID," +
		"       t.COLUMNID," +
		"       t.USERID," +
		"       t.TITLE," +
		"       t.COVERIMAGE," +
		"       t.AUTHOR," +
		"       t.BRIEF," +
		"       to_char(t.CONTEXT) CONTEXT," +
		"       t.STATUS," +
		"       t.ISTOP," +
		"       t.HITS," +
		"       to_char(t.PUBLISHDATE, 'yyyy-mm-dd hh24:mi:ss') PUBLISHDATE," +
		"       to_char(t.CREAEDATE, 'yyyy-mm-dd hh24:mi:ss') CREAEDATE," +
		"       t.CREATORID," +
		"       to_char(t.UPDATEDATE, 'yyyy-mm-dd hh24:mi:ss') UPDATEDATE," +
		"       t.MODIFIERID," +
		"       t.ISSHOW" +
		"  FROM SITE_COLUMNDETAIL t" +
		" WHERE 1 = 1"
	sqlId.And("t.USERID", r.USERID)
	return sqlId.String()
}

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

// QhjAccountOutInApply 充值提现（出入金）
type QhjAccountOutInApply struct {
	EXECUTETYPE         int32   `json:"executetype"  xorm:"EXECUTETYPE"`                 // 申请类型 - 1:出金 2:入金 3: 单边账调整:入金; 4:单边账调整:出金 5:外部母账户调整:入金 6:外部母账户调整:出金 7:外部子账户:入金 8:外部子账户:出金
	AMOUNT              float64 `json:"amount"  xorm:"AMOUNT"`                           // 金额
	CURRENCY            string  `json:"currency"  xorm:"CURRENCY"`                       // 币种
	CHARGE              float64 `json:"charge"  xorm:"CHARGE"`                           // 手续费
	ACCOUNTPWD          string  `json:"accountpwd"  xorm:"ACCOUNTPWD"`                   // 资金密码
	UPDATETIME          string  `json:"updatetime"  xorm:"UPDATETIME"`                   // 更新时间(申请时间)
	BANKID              string  `json:"bankid"  xorm:"BANKID"`                           // 银行编号
	BRANCHBANKID        string  `json:"branchbankid"  xorm:"BRANCHBANKID"`               // 银行支行号
	BRANCHBANKNAME      string  `json:"branchbankname"  xorm:"BRANCHBANKNAME"`           // 银行支行名称
	BANKACCOUNTNO       string  `json:"bankaccountno"  xorm:"BANKACCOUNTNO"`             // 银行卡号
	BANKACCOUNTNAME     string  `json:"bankaccountname"  xorm:"BANKACCOUNTNAME"`         // 银行账户名
	EXTENDINFO          string  `json:"extendinfo"  xorm:"EXTENDINFO"`                   // 扩展信息
	AUDITID             int64   `json:"auditid"  xorm:"AUDITID"`                         // 审核人
	AUDITTIME           string  `json:"audittime"  xorm:"AUDITTIME"`                     // 审核时间
	REMARK              string  `json:"remark"  xorm:"REMARK"`                           // 备注
	NETADDR             string  `json:"netaddr"  xorm:"NETADDR"`                         // 调转网址
	ACCOUNTTICKET       string  `json:"accountticket"  xorm:"ACCOUNTTICKET"`             // 最新账户服务流水号
	CHECKERRORFLAG      int32   `json:"checkerrorflag"  xorm:"CHECKERRORFLAG"`           // 对账差错标志 - 1:为单边账;其它为正常出入金
	REMARK2             string  `json:"remark2"  xorm:"REMARK2"`                         // 备注(失败原因)
	REAUDITID           int64   `json:"reauditid"  xorm:"REAUDITID"`                     // 复审人
	REAUDITTIME         string  `json:"reaudittime"  xorm:"REAUDITTIME"`                 // 复审时间
	REAUDITREMARK       string  `json:"reauditremark"  xorm:"REAUDITREMARK"`             // 复审备注
	APPLYREMARK         string  `json:"applyremark"  xorm:"APPLYREMARK"`                 // 申请备注
	RELATEDORDERID      string  `json:"relatedorderid"  xorm:"RELATEDORDERID"`           // 三方关联ID
	CAPAMOUNTOUT        float64 `json:"capamountout"  xorm:"CAPAMOUNTOUT"`               // 出金(劣后本金) - 外部子账户
	INFAMOUNT           float64 `json:"infamount"  xorm:"INFAMOUNT"`                     // 劣后金额(自有)
	PRIAMOUNT           float64 `json:"priamount"  xorm:"PRIAMOUNT"`                     // 优先金额(授信)
	BANK_APPLY_TICKET   string  `json:"bank_apply_ticket"  xorm:"BANK_APPLY_TICKET"`     // 银行申请流水
	CERTIFICATEPHOTOURL string  `json:"certificatephotourl"  xorm:"CERTIFICATEPHOTOURL"` // 凭证地址
	SOUCREAMOUNT        float64 `json:"soucreamount"  xorm:"SOUCREAMOUNT"`               // 原始出入金金额
	SOUCRECURRENCYID    int64   `json:"soucrecurrencyid"  xorm:"SOUCRECURRENCYID"`       // 原始出入金币种
	ACCOUNTCODE         string  `json:"accountcode"  xorm:"ACCOUNTCODE"`                 // 资金账号
	CUSBANKID           string  `json:"cusbankid"  xorm:"CUSBANKID"`                     // 托管银行编号
	TRADEDATE           string  `json:"tradedate"  xorm:"TRADEDATE"`                     // 交易日(yyyyMMdd)
	EXCHTICKET          string  `json:"exchticket"  xorm:"EXCHTICKET"`                   // 银行服务流水号
	EXTOPERATEID        int64   `json:"extoperateid"  xorm:"EXTOPERATEID"`               // 交易服务流水号
	BANKTICKET          string  `json:"bankticket"  xorm:"BANKTICKET"`                   // 银行流水
	APPLYSTATUS         int32   `json:"applystatus"  xorm:"APPLYSTATUS"`                 // 申请状态 - 1:待审核 2:待复审 3:初审拒绝 4:交易冻结中 5:交易解冻中 6:交易解冻扣款中 7:交易入金中 8:交易冻结/解冻/扣款中(银行发起出金时用) 9:银行出金中 10:银行入金中 11:成功 12:失败 13:银行审核中  14:账户服务入金失败； 15:账户服务解冻失败； 16:账户服务解冻扣款失败； 17:账户服务出金失败 18:复审通过 19:复审拒绝 20:提交审核，账户冻结中 21:审核拒绝，账户解冻中；22: 待审核，账户服务解冻回滚中； 23：待复审，账户服务解冻回滚中； 24： 审核通过，账户冻结金额检查中；25： 复审通过，账户冻结金额检查中；
	USERID              int64   `json:"userid"  xorm:"'USERID'"`                         // 用户id
	LOGINCODE           string  `json:"logincode"  xorm:"'LOGINCODE'"`                   // 登录账号(账号)
	USERINFOTYPE        int32   `json:"userinfotype"  xorm:"'USERINFOTYPE'"`             // 账户类型 1-个人 2-企业
	ACCOUNTNAME         string  `json:"accountname"  xorm:"'ACCOUNTNAME'"`               // 用户名称(名称)

	BeginDate  string `json:"-"` // 开始日期(yyyymmdd)
	EndDate    string `json:"-"` // 结束日期(yyyymmdd)
	FilterName string `json:"-"` // 账户(模糊匹配)
}

func (r *QhjAccountOutInApply) calc() {

}

func (r *QhjAccountOutInApply) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as(select t.userid, wm_concat(t.logincode) logincode from loginaccount t group by t.userid)" +
		"SELECT t.EXECUTETYPE," +
		"       t.AMOUNT," +
		"       t.CURRENCY," +
		"       t.CHARGE," +
		"       t.ACCOUNTPWD," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       t.BANKID," +
		"       t.BRANCHBANKID," +
		"       t.BRANCHBANKNAME," +
		"       t.BANKACCOUNTNO," +
		"       t.BANKACCOUNTNAME," +
		"       t.EXTENDINFO," +
		"       t.AUDITID," +
		"       to_char(t.AUDITTIME, 'yyyy-mm-dd hh24:mi:ss') AUDITTIME," +
		"       t.REMARK," +
		"       t.NETADDR," +
		"       t.ACCOUNTTICKET," +
		"       t.CHECKERRORFLAG," +
		"       t.REMARK2," +
		"       t.REAUDITID," +
		"       to_char(t.REAUDITTIME, 'yyyy-mm-dd hh24:mi:ss') REAUDITTIME," +
		"       t.REAUDITREMARK," +
		"       t.APPLYREMARK," +
		"       t.RELATEDORDERID," +
		"       t.CAPAMOUNTOUT," +
		"       t.INFAMOUNT," +
		"       t.PRIAMOUNT," +
		"       t.BANK_APPLY_TICKET," +
		"       to_char(t.CERTIFICATEPHOTOURL) CERTIFICATEPHOTOURL," +
		"       t.SOUCREAMOUNT," +
		"       t.SOUCRECURRENCYID," +
		"       t.ACCOUNTCODE," +
		"       t.CUSBANKID," +
		"       t.TRADEDATE," +
		"       t.EXCHTICKET," +
		"       t.EXTOPERATEID," +
		"       t.BANKTICKET," +
		"       t.APPLYSTATUS," +
		"       ta.userid," +
		"       u.accountname," +
		"       ui.userinfotype," +
		"       tmp.logincode" +
		"  FROM BANK_ACCOUNTOUTINAPPLY t" +
		"  INNER JOIN TAACCOUNT ta on t.accountcode=to_char(ta.accountid)" +
		"  INNER JOIN USERACCOUNT u on ta.relateduserid=u.userid" +
		"  INNER JOIN USERINFO ui on u.userid=ui.userid" +
		"  LEFT JOIN tmp on u.userid=tmp.userid" +
		" WHERE 1 = 1"
	if len(r.BeginDate) > 0 {
		sqlId.Join(fmt.Sprintf(" and t.UPDATETIME >= to_date(%v,'yyyymmdd')", r.BeginDate))
	}
	if len(r.EndDate) > 0 {
		sqlId.Join(fmt.Sprintf(" and t.UPDATETIME <= to_date(%v,'yyyymmdd')", r.EndDate))
	}
	if len(r.FilterName) > 0 {
		sqlId.Join(fmt.Sprintf(" and (tmp.logincode like '%%%v%%' or u.accountname like '%%%v%%')", r.FilterName, r.FilterName))
	}
	return sqlId.String()
}

// GetDataEx 获取充值提现（出入金）
func (r *QhjAccountOutInApply) GetDataEx() (interface{}, error) {
	sData := make([]QhjAccountOutInApply, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

type DBINT64 int64

func (r *DBINT64) FromDB(bytes []byte) error {
	d, err := strconv.ParseInt(string(bytes), 10, 64)
	if err == nil {
		*r = DBINT64(d)
	}
	return err
}

func (r *DBINT64) ToDB() ([]byte, error) {
	d := strconv.FormatInt(int64(*r), 10)
	return []byte(d), nil
}

// QhjPayOrder 待付款单据
type QhjPayOrder struct {
	OPERATETYPE    int32   `json:"operatetype"  xorm:"OPERATETYPE"`       // 买委托操作类型 - 1:正常下单 16:融资买入
	TRADEID        string  `json:"tradeid"  xorm:"TRADEID"`               // 成交单号(101+Unix秒时间戳(10位)+2位(MarketServiceID)+xxxx)
	TRADEDATE      string  `json:"tradedate"  xorm:"TRADEDATE"`           // 交易日(yyyyMMdd)
	MARKETID       int32   `json:"marketid"  xorm:"MARKETID"`             // 市场ID
	GOODSID        int32   `json:"goodsid"  xorm:"GOODSID"`               // 商品ID
	BUYORDERID     string  `json:"buyorderid"  xorm:"BUYORDERID"`         // 买方委托单号
	BUYACCOUNTID   int64   `json:"buyaccountid"  xorm:"BUYACCOUNTID"`     // 买方账号ID[报价币种]
	SELLORDERID    string  `json:"sellorderid"  xorm:"SELLORDERID"`       // 卖方委托单号
	SELLACCOUNTID  int64   `json:"sellaccountid"  xorm:"SELLACCOUNTID"`   // 卖方账号ID[报价币种]
	TRADEAMOUNT    float64 `json:"tradeamount"  xorm:"TRADEAMOUNT"`       // 成交金额(优惠后金额)
	TRADECHARGE    float64 `json:"tradecharge"  xorm:"TRADECHARGE"`       // 成交手续费(买方)
	PAYFLAG        int32   `json:"payflag"  xorm:"PAYFLAG"`               // 付款标识 - 1:未支付 2:已支付 3:已过期 4:已撤销 5:结算过期 6:预售终止
	CREATETIME     string  `json:"createtime"  xorm:"CREATETIME"`         // 创建时间
	PAYTIME        string  `json:"paytime"  xorm:"PAYTIME"`               // 付款时间
	PAYLIMITEDTIME string  `json:"paylimitedtime"  xorm:"PAYLIMITEDTIME"` // 支付期限
	OFFAMOUNT      float64 `json:"offamount"  xorm:"OFFAMOUNT"`           // 优惠金额
	TRADEPRICE     float64 `json:"tradeprice"  xorm:"TRADEPRICE"`         // 成交价格
	TRADEQTY       int32   `json:"tradeqty"  xorm:"TRADEQTY"`             // 成交数量
	PAYAMOUNT      float64 `json:"payamount"  xorm:"PAYAMOUNT"`           // 支付金额=TradeAmount + TradeCharge
	GOODSCODE      string  `json:"goodscode"  xorm:"'GOODSCODE'"`         // 商品代码
	GOODSNAME      string  `json:"goodsname"  xorm:"'GOODSNAME'"`         // 商品名称
}

func (r *QhjPayOrder) calc() {

}

func (r *QhjPayOrder) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.OPERATETYPE," +
		"       to_char(t.TRADEID) TRADEID," +
		"       t.TRADEDATE," +
		"       t.MARKETID," +
		"       t.GOODSID," +
		"       to_char(t.BUYORDERID) BUYORDERID," +
		"       t.BUYACCOUNTID," +
		"       to_char(t.SELLORDERID) SELLORDERID," +
		"       t.SELLACCOUNTID," +
		"       t.TRADEAMOUNT," +
		"       t.TRADECHARGE," +
		"       t.PAYFLAG," +
		"       to_char(t.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') CREATETIME," +
		"       to_char(t.PAYTIME, 'yyyy-mm-dd hh24:mi:ss') PAYTIME," +
		"       to_char(t.PAYLIMITEDTIME, 'yyyy-mm-dd hh24:mi:ss') PAYLIMITEDTIME," +
		"       t.OFFAMOUNT," +
		"       t.TRADEPRICE," +
		"       t.TRADEQTY," +
		"       t.PAYAMOUNT," +
		"       g.goodscode," +
		"       g.goodsname" +
		"  FROM TRADE_PAYORDER t" +
		"  LEFT JOIN GOODS g on t.goodsid=g.goodsid" +
		" WHERE 1 = 1"
	sqlId.AndEx("t.BUYACCOUNTID", r.BUYACCOUNTID, r.BUYACCOUNTID > 0)
	sqlId.AndEx("t.TRADEID", r.TRADEID, len(r.TRADEID) > 0)
	sqlId.AndEx("t.PAYFLAG", r.PAYFLAG, r.PAYFLAG > 0)
	return sqlId.String()
}

// GetDataEx 获取待付款单据
func (r *QhjPayOrder) GetDataEx() (interface{}, error) {
	sData := make([]QhjPayOrder, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// Qhjgoodsex 交易商品扩展表(千海金)
type Qhjgoodsex struct {
	Goodsid               int32   `json:"goodsid"  xorm:"GOODSID"`                             // 商品ID(自增ID SEQ_GOODS)
	Minamount             float64 `json:"minamount"  xorm:"MINAMOUNT"`                         // 最小购买金额
	Canfinance            int32   `json:"canfinance"  xorm:"CANFINANCE"`                       // 是否支持融资 - 0:不支持 1:支持【69:铂金宝】
	Canregularlybuy       int32   `json:"canregularlybuy"  xorm:"CANREGULARLYBUY"`             // 是否支持定投 - 0:不支持 1:支持【69:铂金宝】
	Regularlybuyminqty    float64 `json:"regularlybuyminqty"  xorm:"REGULARLYBUYMINQTY"`       // 定投最小数量
	Regularlybuyminamount float64 `json:"regularlybuyminamount"  xorm:"REGULARLYBUYMINAMOUNT"` // 定投最小金额
	Deliverygoodsid       int32   `json:"deliverygoodsid"  xorm:"DELIVERYGOODSID"`             // 现货品种ID
	Wrstandardid          int32   `json:"wrstandardid"  xorm:"WRSTANDARDID"`                   // 现货商品ID
	Spotgoodsbrandid      int32   `json:"spotgoodsbrandid"  xorm:"SPOTGOODSBRANDID"`           // 现货品牌ID -  存“DGFactoryItem”表"DGFactoryItemID"
	Defaulttradeuserid    int64   `json:"defaulttradeuserid"  xorm:"DEFAULTTRADEUSERID"`       // 默认交易用户ID
}

func (r *Qhjgoodsex) calc() {

}

func (r *Qhjgoodsex) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.GOODSID," +
		"       t.MINAMOUNT," +
		"       t.CANFINANCE," +
		"       t.CANREGULARLYBUY," +
		"       t.REGULARLYBUYMINQTY," +
		"       t.REGULARLYBUYMINAMOUNT," +
		"       t.DELIVERYGOODSID," +
		"       t.WRSTANDARDID," +
		"       t.SPOTGOODSBRANDID," +
		"       t.DEFAULTTRADEUSERID" +
		"  FROM QHJ_GOODSEX t" +
		" WHERE 1 = 1"
	sqlId.AndEx("t.GOODSID", r.Goodsid, r.Goodsid > 0)
	return sqlId.String()
}

// GetDataEx 获取交易商品扩展表(千海金)
func (r *Qhjgoodsex) GetDataEx() (interface{}, error) {
	sData := make([]Qhjgoodsex, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}