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"
)

// 大连千海金

// 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"`                       // 审核时间(发货时间)
	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," +
		"       t.ADDRESS," +
		"       t.TAKEREMARK," +
		"       t.TAKEORDERSTATUS," +
		"       t.AUDITER," +
		"       to_char(t.AUDITTIME, 'yyyy-mm-dd hh24:mi:ss') AUDITTIME," +
		"       t.CHECKREMARK," +
		"       t.TRADEDATE," +
		"       t.MARKETID," +
		"       t.HANDLESTATUS," +
		"       t.AMOUNT," +
		"       t.COUNTRYID," +
		"       t.PROVINCEID," +
		"       t.CITYID," +
		"       t.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" +
		" 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"` // 地区

	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() {

}

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()
}

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

// 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
}