MTP20_IF/models/ferroalloy.go

package models

import (
	"errors"
	"fmt"
	"mtp2_if/db"
	"mtp2_if/logger"
	"mtp2_if/utils"
	"strconv"
	"strings"
	"sync"
	"time"
)

// 上海铁合金项目

// GErmcpspotgoodsprice 现货市价表
type GErmcpspotgoodsprice struct {
	DELIVERYGOODSID        int32     `json:"deliverygoodsid" xorm:"DELIVERYGOODSID"`               // 现货品种ID
	WRSTANDARDID           int64     `json:"wrstandardid" xorm:"WRSTANDARDID"`                     // 现货商品ID(通用则为0)
	SPOTGOODSBRANDID       int32     `json:"spotgoodsbrandid" xorm:"SPOTGOODSBRANDID"`             // 现货品牌ID(通用则为0, 不为0则须先有商品ID)
	CURRENCYID             int64     `json:"currencyid" xorm:"CURRENCYID"`                         // 报价货币ID
	SPOTGOODSPRICE         float64   `json:"spotgoodsprice" xorm:"SPOTGOODSPRICE"`                 // 【最新价】当前价格
	TRADEDATE              string    `json:"tradedate" xorm:"TRADEDATE"`                           // 【日期】交易日
	OPERATESRC             int32     `json:"-" xorm:"OPERATESRC"`                                  // 最后操作来源 - 1:管理端 2:终端
	OPERATEID              int64     `json:"-" xorm:"OPERATEID"`                                   // 最后操作人
	OPERATETIME            time.Time `json:"-" xorm:"OPERATETIME"`                                 // 最后操作时间
	ISVALID                int32     `json:"-" xorm:"ISVALID"`                                     // 是否有效 - 0:无效 1:有效
	YSTSPOTGOODSPRICE      float64   `json:"ystspotgoodsprice" xorm:"YSTSPOTGOODSPRICE"`           // 昨价
	TODAYSPOTGOODSPRICE    float64   `json:"todayspotgoodsprice" xorm:"TODAYSPOTGOODSPRICE"`       // 今日指定价
	TODAYPRICEDTOTALQTY    float64   `json:"todaypricedtotalqty" xorm:"TODAYPRICEDTOTALQTY"`       // 今日定价总量
	TODAYPRICEDTOTALAMOUNT float64   `json:"todaypricedtotalamount" xorm:"TODAYPRICEDTOTALAMOUNT"` // 今日定价金额
	SRCMARKETNAME          string    `json:"srcmarketname" xorm:"SRCMARKETNAME"`                   // 来源市场名称
	PRESPOTGOODSPRICE      float64   `json:"prespotgoodsprice" xorm:"PRESPOTGOODSPRICE"`           // 上日价格

	WRSTANDARDCODE string `json:"wrstandardcode" xorm:"WRSTANDARDCODE"` // 现货商品代码
	WRSTANDARDNAME string `json:"wrstandardname" xorm:"WRSTANDARDNAME"` // 【名称】现货商品名称

	CHG float64 `json:"chg" xorm:"CHG"` // 涨跌幅

	PageEx `xorm:"extends"` // 页码信息
}

func (r *GErmcpspotgoodsprice) calc() {
	if len(r.TRADEDATE) == 8 {
		r.TRADEDATE = fmt.Sprintf("%v-%v-%v", r.TRADEDATE[0:4], r.TRADEDATE[4:6], r.TRADEDATE[6:8])
	}
}

func (r *GErmcpspotgoodsprice) buildSql() string {
	var sqlId utils.SQLVal = `
	SELECT 
		t.DELIVERYGOODSID       ,
		t.WRSTANDARDID          ,
		t.SPOTGOODSBRANDID      ,
		t.CURRENCYID            ,
		t.SPOTGOODSPRICE        ,
		t.TRADEDATE             ,
		t.YSTSPOTGOODSPRICE     ,
		t.TODAYSPOTGOODSPRICE   ,
		t.TODAYPRICEDTOTALQTY   ,
		t.TODAYPRICEDTOTALAMOUNT,
		t.SRCMARKETNAME,
		t.PRESPOTGOODSPRICE,
		ROUND(((t.SPOTGOODSPRICE - t.PRESPOTGOODSPRICE) / t.PRESPOTGOODSPRICE), 4) CHG,
		w.WRSTANDARDCODE,
		w.WRSTANDARDNAME         
	FROM ERMCP_SPOTGOODSPRICE t
	LEFT JOIN WRStandard w ON t.WRSTANDARDID = w.WRSTANDARDID
	WHERE t.SPOTGOODSBRANDID = 0 AND t.CURRENCYID = 1 AND t.ISVALID = 1
	`
	sqlId.OrderByDesc("w.WRSTANDARDNAME")

	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

func (r *GErmcpspotgoodsprice) GetDataByPage() (interface{}, error, int, int, int) {
	sData := make([]GErmcpspotgoodsprice, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	total := 0
	for i := range sData {
		sData[i].calc()
		total = sData[i].Total
	}
	return sData, err, r.Page, r.PageSize, total
}

type THJSigninReq struct {
	USERID int64 `form:"userid" binding:"required"` // 用户ID
}

type THJSigninRsp struct {
	SigninStatus int `json:"signinstatus"` // 状态操作标志 1-签到成功 2-当日已签到，不能重复签到
}

// Signin 用户签到
func (r *THJSigninReq) Signin() (rsp *THJSigninRsp, err error) {
	// 资源锁
	var lock sync.Mutex
	lock.Lock()
	defer lock.Unlock()

	engine := db.GetEngine()

	// 获取目标用户信息
	if u, err := GetUserInfo(int(r.USERID)); err != nil || u == nil {
		logger.GetLogger().Errorf("Thjsignin failed: %s", err.Error())
		err = errors.New("错误的用户ID")
		return nil, err
	}

	// 判断目标用户当日是否已经签到
	p := new(Thjsignin)
	if has, err := engine.Where("userid = ?", r.USERID).And("tradedate = to_char(sysdate, 'yyyymmdd')").Get(p); err != nil {
		logger.GetLogger().Errorf("Thjsignin failed: %s", err.Error())
		err = errors.New("数据错误")
		return nil, err
	} else {
		if has {
			return &THJSigninRsp{SigninStatus: 2}, nil
		}
	}

	// 获取签到积配置
	c := Thjscoreconfig{
		SCORECONFIGTYPE: 2,
	}
	if has, err := engine.Get(&c); err != nil || !has {
		logger.GetLogger().Errorf("Thjsignin failed: %s", err.Error())
		err = errors.New("数据错误")
		return nil, err
	}

	// 事务
	session := engine.NewSession()
	defer session.Close()
	// add Begin() before any action
	if err := session.Begin(); err != nil {
		logger.GetLogger().Errorf("Thjsignin failed: %s", err.Error())
		return nil, errors.New("数据错误")
	}

	now := time.Now()

	// 签到积分配置大于0才记积分和流水
	if c.PARMA1 > 0 {
		// 判断用户积分表是否已经存在此用户
		curscore := 0
		t := Thjuserscore{USERID: r.USERID}
		has, err := session.Get(&t)
		if err != nil {
			session.Rollback()
			logger.GetLogger().Errorf("Thjsignin failed: %s", err.Error())
			return nil, errors.New("数据错误")
		}
		if has {
			// 更新积分
			curscore = int(t.CURSCORE)
			t.CURSCORE += int64(c.PARMA1)
			t.UPDATETIME = now
			// _, err = session.Update(&t)
			sql := fmt.Sprintf(`UPDATE THJ_USERSCORE  
		SET CURSCORE = %v, UPDATETIME = to_date('%v', 'yyyy-mm-dd hh24:mi:ss') 
		WHERE USERID = %v`,
				t.CURSCORE, t.UPDATETIME.Format("2006-01-02 15:04:05"), t.USERID)
			_, err = session.Exec(sql)
			if err != nil {
				session.Rollback()
				logger.GetLogger().Errorf("Thjsignin failed: %s", err.Error())
				return nil, errors.New("数据错误")
			}
		} else {
			// 新增用户积分记录
			t.CURSCORE = int64(c.PARMA1)
			t.UPDATETIME = now
			// _, err = session.Insert(&t)
			sql := fmt.Sprintf(`INSERT INTO THJ_USERSCORE VALUES (%v, %v, %v, to_date('%v', 'yyyy-mm-dd hh24:mi:ss'))`,
				t.USERID, t.CURSCORE, 0, t.UPDATETIME.Format("2006-01-02 15:04:05"))
			_, err = session.Exec(sql)
			if err != nil {
				session.Rollback()
				logger.GetLogger().Errorf("Thjsignin failed: %s", err.Error())
				return nil, errors.New("数据错误")
			}
		}

		// 记录积分流水
		q := Thjuserscorelog{}
		seqMap, err := session.QueryString("SELECT SEQ_THJ_USERSCORELOG.nextval SEQID FROM dual")
		if err != nil {
			session.Rollback()
			logger.GetLogger().Errorf("Thjsignin failed: %s", err.Error())
			return nil, errors.New("数据错误")
		}
		if len(seqMap) <= 0 {
			session.Rollback()
			return nil, errors.New("获取自增ID错误")
		}
		seqID, _ := strconv.Atoi(seqMap[0]["SEQID"])
		q.AUTOID = int64(seqID)
		q.USERID = r.USERID
		q.SCORECONFIGTYPE = 2 // 2:签到送积分
		q.SCORE = float64(c.PARMA1)
		q.ORISCORE = float64(curscore)
		q.CURSCORE = float64(t.CURSCORE)
		q.CREATETIME = now
		q.RELATEDORDERID = r.USERID
		// _, err = session.Insert(&q)
		sql := fmt.Sprintf(`INSERT INTO THJ_USERSCORELOG 
			(AUTOID, USERID, SCORECONFIGTYPE, SCORE, ORISCORE, CURSCORE, CREATETIME)
			VALUES 
			(%v, %v, %v, %v, %v, %v, to_date('%v', 'yyyy-mm-dd hh24:mi:ss'))`,
			q.AUTOID, q.USERID, q.SCORECONFIGTYPE, q.SCORE, q.ORISCORE, q.CURSCORE, q.CREATETIME.Format("2006-01-02 15:04:05"))
		_, err = session.Exec(sql)
		if err != nil {
			session.Rollback()
			logger.GetLogger().Errorf("Thjsignin failed: %s", err.Error())
			return nil, errors.New("数据错误")
		}
	}

	// 增加签到记录
	sql := fmt.Sprintf(`INSERT INTO THJ_SIGNIN VALUES (%v, %v, to_date('%v', 'yyyy-mm-dd hh24:mi:ss'))`,
		r.USERID, now.Format("20060102"), now.Format("2006-01-02 15:04:05"))
	_, err = session.Exec(sql)
	if err != nil {
		session.Rollback()
		logger.GetLogger().Errorf("Thjsignin failed: %s", err.Error())
		return nil, errors.New("数据错误")
	}

	// add Commit() after all actions
	err = session.Commit()
	if err != nil {
		session.Rollback()
		logger.GetLogger().Errorf("Thjsignin failed: %s", err.Error())
		return nil, errors.New("数据错误")
	}

	return &THJSigninRsp{SigninStatus: 1}, nil
}

// 我的推荐列表
type MyRefer struct {
	Accountname string  `json:"accountname" xorm:"ACCOUNTNAME"` // 用户名（脱敏）
	Score       float64 `json:"score" xorm:"SCORE"`             // 积分
	CREATETIME  string  `json:"createtime" xorm:"CREATETIME"`   // 时间（yyyy-mm-dd）

	UserID int `json:"-" form:"userid" binding:"required"` // 用户ID

	PageEx `xorm:"extends"` // 页码信息
}

func (r *MyRefer) calc() {
	r.Accountname = EncryptByStar(r.Accountname)
}

func (r *MyRefer) buildSql() string {
	var sqlId utils.SQLVal = `
	select 
		u.ACCOUNTNAME,
		t.SCORE,
		to_char(t.CREATETIME, 'yyyy-mm-dd') CREATETIME
	from THJ_USERSCORELOG t
	inner join USERACCOUNT u on u.USERID = t.RELATEDORDERID
	where t.USERID = %v
	order by t.CREATETIME desc
`
	sqlId.FormatParam(r.UserID)

	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

func (r *MyRefer) GetDataByPage() (interface{}, error, int, int, int) {
	sData := make([]MyRefer, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	total := 0
	for i := range sData {
		sData[i].calc()
		total = sData[i].Total
	}
	return sData, err, r.Page, r.PageSize, total
}

type GThjuserscorelog struct {
	USERID          int64   `json:"-" xorm:"USERID" form:"userid" binding:"required"` // 用户ID
	SCORECONFIGTYPE int32   `json:"scoreconfigtype" xorm:"SCORECONFIGTYPE"`           // 配置类型 - 1:注册红包 2:签到积分 3:推广积分 4:下级用户下单积分 5:自己采购下单积分 6:自己供求下单积分 7:抽奖配置
	SCORE           float64 `json:"score" xorm:"SCORE"`                               // 变动积分
	ORISCORE        float64 `json:"-" xorm:"ORISCORE"`                                // 期初积分(变动前积)
	CURSCORE        float64 `json:"-" xorm:"CURSCORE"`                                // 期末积分(变动后积)
	CREATETIME      string  `json:"createtime" xorm:"CREATETIME"`                     // 记账时间
	REMARK          string  `json:"-" xorm:"REMARK"`                                  // 备注
	RELATEDORDERID  int64   `json:"-" xorm:"RELATEDORDERID"`                          // 关联单号\推荐人UserID
	REDPACKETVALUE  float64 `json:"redpacketvalue" xorm:"REDPACKETVALUE"`             // 红包值【抽奖】

	STYPE string `form:"stype"` // [格式:1,2,3] 配置类型 - 1:注册红包 2:签到积分 3:推广积分 4:下级用户下单积分 5:自己采购下单积分 6:自己供求下单积分 7:抽奖配置

	PageEx `xorm:"extends"` // 页码信息
}

func (r *GThjuserscorelog) calc() {

}

func (r *GThjuserscorelog) buildSql() string {
	var sqlId utils.SQLVal = `
	select 
		t.SCORECONFIGTYPE,
		t.SCORE,
		t.REDPACKETVALUE,
		to_char(t.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') CREATETIME
	from THJ_USERSCORELOG t
	where t.USERID = %v and %v 
	order by t.CREATETIME desc
`
	param := "1=1"
	if r.STYPE != "" {
		param = fmt.Sprintf("t.SCORECONFIGTYPE in (%v)", r.STYPE)
	}
	sqlId.FormatParam(r.USERID, param)

	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

func (r *GThjuserscorelog) GetDataByPage() (interface{}, error, int, int, int) {
	sData := make([]GThjuserscorelog, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	total := 0
	for i := range sData {
		sData[i].calc()
		total = sData[i].Total
	}
	return sData, err, r.Page, r.PageSize, total
}

// THJWrstandard 现货商品表
type THJWrstandard struct {
	WRSTANDARDID      int64     `json:"wrstandardid" xorm:"WRSTANDARDID"`                           // 现货商品ID(自增 SEQ_GOODS 确保不重复)
	WRSTANDARDCODE    string    `json:"wrstandardcode" xorm:"WRSTANDARDCODE"`                       // 现货商品代码
	WRSTANDARDNAME    string    `json:"wrstandardname" xorm:"WRSTANDARDNAME" form:"wrstandardname"` // 现货商品名称（模糊查询）
	DELIVERYGOODSID   int32     `json:"deliverygoodsid" xorm:"DELIVERYGOODSID"`                     // 现货品种ID
	UNITID            int32     `json:"unitid" xorm:"UNITID"`                                       // 现货商品单位ID
	MINIVALUE         int64     `json:"minivalue" xorm:"MINIVALUE"`                                 // 最小变动值
	MINIVALUEDP       int64     `json:"minivaluedp" xorm:"MINIVALUEDP"`                             // 最小变动值小数位
	REALMINIVALUE     int64     `json:"realminivalue" xorm:"REALMINIVALUE"`                         // 实际最小变动值
	REALMINIVALUEDP   int64     `json:"realminivaluedp" xorm:"REALMINIVALUEDP"`                     // 实际最小变动值小数位
	WRSSTATUS         int32     `json:"wrsstatus" xorm:"WRSSTATUS"`                                 // 状态 - 作废 - 0:未激活 1:正常
	CREATORID         int64     `json:"-" xorm:"CREATORID"`                                         // 创建人
	CREATETIME        time.Time `json:"-" xorm:"CREATETIME"`                                        // 创建时间
	UPDATORID         int64     `json:"-" xorm:"UPDATORID"`                                         // 更新人
	UPDATETIME        time.Time `json:"-" xorm:"UPDATETIME"`                                        // 更新时间
	FACTORYITEMJSON   string    `json:"-" xorm:"FACTORYITEMJSON"`                                   // 要素项定义Json[{"DGFactoryItemTypeID": ,"ItemTypeMode": ,"FactoryItemIDs": },{.....},]DGFactoryItemTypeID - 要素项类型ID --DGFactoryItem->DGFactoryItemTypeIDItemTypeMode - 要素项类型模式 --DGFactoryItem->ItemTypeModeFactoryItemIDs - 选择项IDs--DGFactoryItem->DGFactoryItemID, 逗号分隔
	ISVALID           int32     `json:"isvalid" xorm:"ISVALID"`                                     // 是否有效 - 0:无效 1:有效
	AREAUSERID        int64     `json:"areauserid" xorm:"AREAUSERID"`                               // 所属机构
	REMARK            string    `json:"-" xorm:"REMARK"`                                            // 备注
	CONVERTFACTOR     float64   `json:"convertfactor" xorm:"CONVERTFACTOR"`                         // 标仓系数
	VATRATE           float64   `json:"vatrate" xorm:"VATRATE"`                                     // 现货增值税率
	STORAGEFEE        float64   `json:"storagefee" xorm:"STORAGEFEE"`                               // 仓储费(固定: 111)
	THUMURLS          string    `json:"thumurls" xorm:"THUMURLS"`                                   // 缩略图片(1:1)(逗号分隔)
	PICTUREURLS       string    `json:"pictureurls" xorm:"PICTUREURLS"`                             // 详情图片(逗号分隔)
	BANNERPICURL      string    `json:"bannerpicurl" xorm:"BANNERPICURL"`                           // Banner图(逗号分隔)
	PROVIDERUSERID    int64     `json:"-" xorm:"PROVIDERUSERID"`                                    // 供应链提供商
	PROVIDERACCOUNTID int64     `json:"-" xorm:"PROVIDERACCOUNTID"`                                 // 供应链提供商资金账户 ID

	GoodsType int `json:"-" xorm:"-"` // 类型 0-产品介绍 1-供求列表

	PageEx `xorm:"extends"` // 页码信息
}

func (r *THJWrstandard) calc() {

}

func (r *THJWrstandard) buildSql() string {
	//取委托单中有量的商品
	var sqlId utils.SQLVal = `
	select 
		wr.*
	from wrstandard wr
	where wr.wrstandardid in 
	(select distinct t.wrstandardid
	from WR_PresaleInfo t
  	inner join wrtrade_orderdetail od on t.sellwrtradeorderid = od.wrtradeorderid
	where t.presalestatus = 2 and (od.orderqty - od.tradeqty) > 0
	and t.marketid = 64201) and %v 
	order by wr.wrstandardname
	`
	param := "1=1"
	if r.WRSTANDARDNAME != "" {
		param = fmt.Sprintf("wr.wrstandardname like '%%%v%%'", r.WRSTANDARDNAME)
	}
	sqlId.FormatParam(param)

	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

func (r *THJWrstandard) buildSql2() string {
	var sqlId utils.SQLVal = `
	select 
		wr.* 
	from wrstandard wr 
	where wr.wrstandardid in (select distinct t.wrstandardid from WRTrade_OrderDetail t where t.marketid=65201 and t.wrtradetype = 1 and t.wrtradeorderstatus in (3,7))
	and %v
	order by wr.wrstandardname
	`
	param := "1=1"
	if r.WRSTANDARDNAME != "" {
		param = fmt.Sprintf("wr.wrstandardname like '%%%v%%'", r.WRSTANDARDNAME)
	}
	sqlId.FormatParam(param)

	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

func (r *THJWrstandard) GetDataByPage() (interface{}, error, int, int, int) {
	sql := r.buildSql()
	if r.GoodsType == 1 {
		sql = r.buildSql2()
	}

	sData := make([]THJWrstandard, 0)
	err := db.GetEngine().SQL(sql).Find(&sData)
	total := 0
	for i := range sData {
		sData[i].calc()
		total = sData[i].Total
	}
	return sData, err, r.Page, r.PageSize, total
}

// RegisterMoney 注册红包
type RegisterMoney struct {
	Amount float64 `json:"amount" xorm:"AMOUNT"` // 红包

	Accountid int64 `json:"-" form:"accountid" binding:"required"` // 资金账户ID
}

func (r *RegisterMoney) calc() {

}

func (r *RegisterMoney) buildSql() string {
	var sqlId utils.SQLVal = `
	select 
		t.amount 
	from taaccountlog t 
	where t.accountid = %v and t.businesscode = '734'
	`
	sqlId.FormatParam(r.Accountid)

	return sqlId.String()
}

// GetDataEx 从数据库中查询数据
func (r *RegisterMoney) GetDataEx() (interface{}, error) {
	e := db.GetEngine()
	s := e.SQL(r.buildSql())
	sData := make([]RegisterMoney, 0)
	if err := s.Find(&sData); err != nil {
		return nil, err
	}
	for i := range sData {
		sData[i].calc()
	}
	return sData, nil
}

// THJWrstandardDetail_GoodsInfo 采购商品信息
type THJWrstandardDetail_GoodsInfo struct {
	WRSTANDARDID      int64     `json:"wrstandardid" xorm:"WRSTANDARDID"`     // 现货商品ID(自增 SEQ_GOODS 确保不重复)
	WRSTANDARDCODE    string    `json:"wrstandardcode" xorm:"WRSTANDARDCODE"` // 现货商品代码
	WRSTANDARDNAME    string    `json:"wrstandardname" xorm:"WRSTANDARDNAME"` // 现货商品名称
	DELIVERYGOODSID   int32     `json:"-" xorm:"DELIVERYGOODSID"`             // 现货品种ID
	UNITID            int32     `json:"unitid" xorm:"UNITID"`                 // 现货商品单位ID
	MINIVALUE         int64     `json:"-" xorm:"MINIVALUE"`                   // 最小变动值
	MINIVALUEDP       int64     `json:"-" xorm:"MINIVALUEDP"`                 // 最小变动值小数位
	REALMINIVALUE     int64     `json:"-" xorm:"REALMINIVALUE"`               // 实际最小变动值
	REALMINIVALUEDP   int64     `json:"-" xorm:"REALMINIVALUEDP"`             // 实际最小变动值小数位
	WRSSTATUS         int32     `json:"-" xorm:"WRSSTATUS"`                   // 状态 - 作废 - 0:未激活 1:正常
	CREATORID         int64     `json:"-" xorm:"CREATORID"`                   // 创建人
	CREATETIME        time.Time `json:"-" xorm:"CREATETIME"`                  // 创建时间
	UPDATORID         int64     `json:"-" xorm:"UPDATORID"`                   // 更新人
	UPDATETIME        time.Time `json:"-" xorm:"UPDATETIME"`                  // 更新时间
	FACTORYITEMJSON   string    `json:"-" xorm:"FACTORYITEMJSON"`             // 要素项定义Json[{"DGFactoryItemTypeID": ,"ItemTypeMode": ,"FactoryItemIDs": },{.....},]DGFactoryItemTypeID - 要素项类型ID --DGFactoryItem->DGFactoryItemTypeIDItemTypeMode - 要素项类型模式 --DGFactoryItem->ItemTypeModeFactoryItemIDs - 选择项IDs--DGFactoryItem->DGFactoryItemID, 逗号分隔
	ISVALID           int32     `json:"-" xorm:"ISVALID"`                     // 是否有效 - 0:无效 1:有效
	AREAUSERID        int64     `json:"-" xorm:"AREAUSERID"`                  // 所属机构
	REMARK            string    `json:"-" xorm:"REMARK"`                      // 备注
	CONVERTFACTOR     float64   `json:"-" xorm:"CONVERTFACTOR"`               // 标仓系数
	VATRATE           float64   `json:"-" xorm:"VATRATE"`                     // 现货增值税率
	STORAGEFEE        float64   `json:"-" xorm:"STORAGEFEE"`                  // 仓储费(固定: 111)
	THUMURLS          string    `json:"-" xorm:"THUMURLS"`                    // 缩略图片(1:1)(逗号分隔)
	PICTUREURLS       string    `json:"pictureurls" xorm:"PICTUREURLS"`       // 详情图片(逗号分隔)
	BANNERPICURL      string    `json:"bannerpicurl" xorm:"BANNERPICURL"`     // Banner图(逗号分隔)
	PROVIDERUSERID    int64     `json:"-" xorm:"PROVIDERUSERID"`              // 供应链提供商
	PROVIDERACCOUNTID int64     `json:"-" xorm:"PROVIDERACCOUNTID"`           // 供应链提供商资金账户 ID

	SPOTGOODSPRICE float64 `json:"spotgoodsprice" xorm:"SPOTGOODSPRICE"` // 现货价格
}

// THJDeliveryMode 交割方式
type THJDeliveryMode struct {
	ENUMDICNAME  string `json:"enumdicname" xorm:"ENUMDICNAME"`   // 枚举项名称
	ENUMITEMNAME int64  `json:"enumitemname" xorm:"ENUMITEMNAME"` // 枚举项值
}

// THJDeliveryMonth 交割月份
type THJDeliveryMonth struct {
	PRESALEAPPLYID string  `json:"presaleapplyid" xorm:"PRESALEAPPLYID"` // 预售申请ID(184+Unix秒时间戳(10位)+xxxxxx)
	ENDMONTH       string  `json:"endmonth" xorm:"ENDMONTH"`             // 预售结束月份(yyyy-mm)
	ENDDATE        string  `json:"enddate" xorm:"ENDDATE"`               // 预售结束日期(yyyy-mm-dd)
	ORDERQTY       int64   `json:"orderqty" xorm:"ORDERQTY"`             // 委托数量
	TRADEQTY       int64   `json:"tradeqty" xorm:"TRADEQTY"`             // 成交数量
	REMAINQTY      int64   `json:"remainqty" xorm:"REMAINQTY"`           // 可用数量
	UNITPRICE      float64 `json:"unitprice" xorm:"UNITPRICE"`           // [参考价]商品单价
}

// THJPresaleApplyDeposit 支付方式
type THJPresaleApplyDeposit struct {
	DEPOSITID      string  `json:"depositid" xorm:"DEPOSITID"`           // 自增ID autoid
	PRESALEAPPLYID string  `json:"presaleapplyid" xorm:"PRESALEAPPLYID"` // 预售申请ID(184+Unix秒时间戳(10位)+xxxxxx)
	DEPOSITRATE    float64 `json:"depositrate" xorm:"DEPOSITRATE"`       // 定金比例
	DISCOUNTAMOUNT float64 `json:"discountamount" xorm:"DISCOUNTAMOUNT"` // 优惠金额(每吨)
}

// THJSpotGoodsPriceLog 历史价格走势
type THJSpotGoodsPriceLog struct {
	SPOTGOODSPRICE float64 `json:"spotgoodsprice" xorm:"SPOTGOODSPRICE"` // 现货价格
	TRADEDATE      string  `json:"tradedate" xorm:"TRADEDATE"`           // 交易日(yyyyMMdd)
}

type THJWrstandardDetailReq struct {
	WRSTANDARDID int64 `form:"wrstandardid" binding:"required"` // 现货商品ID
}

// THJWrstandardDetailRsp 采购商品详情
type THJWrstandardDetailRsp struct {
	GoodsInfo            THJWrstandardDetail_GoodsInfo `json:"goodsinfo"`            // 商品信息
	DeliveryModes        []THJDeliveryMode             `json:"deliverymodes"`        // 交割方式
	DeliveryMonth        []THJDeliveryMonth            `json:"deliverymonth"`        // 交割月份
	PresaleApplyDeposits []THJPresaleApplyDeposit      `json:"presaleapplydeposits"` // 支付方式
	SpotGoodsPriceLogs   []THJSpotGoodsPriceLog        `json:"spotgoodspricelogs"`   // 历史价格走势
}

// GetTHJWrstandardDetail 获取采购商品详情
func (r *THJWrstandardDetailReq) GetTHJWrstandardDetail() (rsp *THJWrstandardDetailRsp, err error) {
	engine := db.GetEngine()

	rsp = new(THJWrstandardDetailRsp)

	// 采购商品信息
	var goodsInfo THJWrstandardDetail_GoodsInfo
	sql := fmt.Sprintf(`
		select
			t.WRSTANDARDID,
			t.WRSTANDARDCODE,
			t.WRSTANDARDNAME,
			t.PICTUREURLS,
			t.BANNERPICURL,
			t.UNITID,
			p.SPOTGOODSPRICE
		from wrstandard t
		left join ERMCP_SpotGoodsPrice p on t.WRSTANDARDID = p.WRSTANDARDID and p.spotgoodsbrandid=0 and p.currencyid = 1 
		where t.WRSTANDARDID = %v
	`, r.WRSTANDARDID)
	if _, err = engine.SQL(sql).Get(&goodsInfo); err != nil {
		return
	}
	rsp.GoodsInfo = goodsInfo

	// 交割方式
	deliveryModes := make([]THJDeliveryMode, 0)
	sql = `
		select 
			t.ENUMDICNAME,
			t.ENUMITEMNAME
		from enumdicitem t 
		where t.enumdiccode = 'THJDeliveryMode'
	`
	if err = engine.SQL(sql).Find(&deliveryModes); err != nil {
		return
	}
	rsp.DeliveryModes = deliveryModes

	// 交割月份
	deliveryMonths := make([]THJDeliveryMonth, 0)
	sql = fmt.Sprintf(`
		select
			to_char(t.PRESALEAPPLYID) PRESALEAPPLYID,  
			to_char(t.TAKESTARTDATE, 'yyyy-mm') ENDMONTH, 
			to_char(t.ENDDATE, 'yyyy-mm-dd') ENDDATE, 
			t.UNITPRICE,
			od.ORDERQTY,
			od.TRADEQTY,
			(od.orderqty - od.tradeqty) REMAINQTY
		from WR_PresaleInfo t
		inner join wrtrade_orderdetail od on t.sellwrtradeorderid = od.wrtradeorderid
		where t.wrstandardid = %v
			and t.presalestatus = 2
			and od.wrtradeorderstatus in (3, 7)
			and (od.orderqty - od.tradeqty) > 0
		order by t.enddate
	`, r.WRSTANDARDID)
	if err = engine.SQL(sql).Find(&deliveryMonths); err != nil {
		return
	}
	rsp.DeliveryMonth = deliveryMonths

	// 支付方式
	ids := make([]string, 0)
	for _, item := range deliveryMonths {
		ids = append(ids, item.PRESALEAPPLYID)
	}
	if len(ids) > 0 {
		presaleApplyDeposits := make([]THJPresaleApplyDeposit, 0)
		sql = fmt.Sprintf(`
			select 
				to_char(t.AutoID) DEPOSITID,
				to_char(t.PRESALEAPPLYID) PRESALEAPPLYID,  
				t.DEPOSITRATE, 
				t.DISCOUNTAMOUNT 
			from THJ_PresaleApplyDeposit t
			where t.presaleapplyid in (%v)
			order by t.DEPOSITRATE desc
		`, strings.Join(ids, ","))
		if err = engine.SQL(sql).Find(&presaleApplyDeposits); err != nil {
			return
		}
		rsp.PresaleApplyDeposits = presaleApplyDeposits
	}

	// 历史价格走势
	spotGoodsPriceLogs := make([]THJSpotGoodsPriceLog, 0)
	sql = fmt.Sprintf(`
		select 
			t.SPOTGOODSPRICE, 
			t.TRADEDATE 
		from ERMCP_SpotGoodsPriceLog t
		where t.SPOTGOODSPRICE <> 0 AND t.wrstandardid = %v
		order by t.tradedate
	`, r.WRSTANDARDID)
	if err = engine.SQL(sql).Find(&spotGoodsPriceLogs); err != nil {
		return
	}
	rsp.SpotGoodsPriceLogs = spotGoodsPriceLogs

	return
}

// Thjpurchasetradedetail 铁合金采购成交扩展表
type Thjpurchasetradedetail struct {
	WRTRADEDETAILID      string    `json:"wrtradedetailid" xorm:"WRTRADEDETAILID"`                      // 采购成交单ID(321+Unix秒时间戳(10位)+xxxxxx)
	PRESALEAPPLYID       string    `json:"presaleapplyid" xorm:"PRESALEAPPLYID"`                        // 预售申请ID(184+Unix秒时间戳(10位)+xxxxxx)
	TRADEDATE            string    `json:"tradedate" xorm:"TRADEDATE"`                                  // 交易日(yyyyMMdd)
	MARKETID             int32     `json:"marketid" xorm:"MARKETID"`                                    // 市场ID
	WRFACTORTYPEID       string    `json:"wrfactortypeid" xorm:"WRFACTORTYPEID"`                        // 仓单要素类型ID
	DELIVERYGOODSID      int32     `json:"deliverygoodsid" xorm:"DELIVERYGOODSID"`                      // 现货品种ID
	WRSTANDARDID         int32     `json:"wrstandardid" xorm:"WRSTANDARDID"`                            // 现货商品ID
	WAREHOUSEID          int64     `json:"warehouseid" xorm:"WAREHOUSEID"`                              // 仓库ID
	BUYUSERID            int64     `json:"buyuserid" xorm:"BUYUSERID" form:"userid" binding:"required"` // 买方用户ID
	BUYACCOUNTID         int64     `json:"buyaccountid" xorm:"BUYACCOUNTID"`                            // 买方账号ID
	SELLUSERID           int64     `json:"selluserid" xorm:"SELLUSERID"`                                // 卖方用户ID
	SELLACCOUNTID        int64     `json:"sellaccountid" xorm:"SELLACCOUNTID"`                          // 卖方账号ID
	TRADETIME            time.Time `json:"tradetime" xorm:"TRADETIME"`                                  // 摘牌时间(委托时间)
	TRADEPRICE           float64   `json:"tradeprice" xorm:"TRADEPRICE"`                                // 摘牌价格 - 参考价
	TRADEQTY             int64     `json:"tradeqty" xorm:"TRADEQTY"`                                    // [预售价]摘牌数量
	TRADEAMOUNT          float64   `json:"tradeamount" xorm:"TRADEAMOUNT"`                              // 摘牌总货款
	DEPOSITRATE          float64   `json:"depositrate" xorm:"DEPOSITRATE"`                              // 定金比例
	DISCOUNTAMOUNT       float64   `json:"discountamount" xorm:"DISCOUNTAMOUNT"`                        // 优惠总金额(优惠金额*TradeQty)
	PAYEDDEPOSIT         float64   `json:"payeddeposit" xorm:"PAYEDDEPOSIT"`                            // [定金]买方已付定金(预付款)
	THJDELIVERYMODE      int32     `json:"thjdeliverymode" xorm:"THJDELIVERYMODE"`                      // 交割方式 - 1：平台仓储 2：自提 3：代办运输 (枚举：THJDeliveryMode)
	CONTACTNAME          string    `json:"contactname" xorm:"CONTACTNAME"`                              // 联系人姓名
	CONTACTINFO          string    `json:"contactinfo" xorm:"CONTACTINFO"`                              // 联系方式
	DESADDRESS           string    `json:"desaddress" xorm:"DESADDRESS"`                                // 目的地地址
	RECEIPTINFO          string    `json:"receiptinfo" xorm:"RECEIPTINFO"`                              // 发票信息
	ORDERSTATUS          int32     `json:"orderstatus" xorm:"ORDERSTATUS"`                              // 单据状态 - 1:待付尾款 2:支付尾款成功 3:支付尾款失败 4:已违约 5:卖方已确认 6:买方已确认 7:系统自动确认 (枚举：THJOrderStatus)
	LASTPRICE            float64   `json:"lastprice" xorm:"LASTPRICE"`                                  // [实际价]到期价格
	LASTAMOUNT           float64   `json:"lastamount" xorm:"LASTAMOUNT"`                                // 到期总货款 = 到期价格 * 摘牌数量
	BUYCHARGEVALUE       float64   `json:"buychargevalue" xorm:"BUYCHARGEVALUE"`                        // 买方到期手续费
	SELLCHARGEVALUE      float64   `json:"sellchargevalue" xorm:"SELLCHARGEVALUE"`                      // 卖方到期手续费
	REMAINAMOUNT         float64   `json:"remainamount" xorm:"REMAINAMOUNT"`                            // [尾款]剩余货款 = 到期总货款 - 已付定金 - 优惠金额
	SELLCONFIRMREMARK    string    `json:"sellconfirmremark" xorm:"SELLCONFIRMREMARK"`                  // 卖方确认备注
	SELLCONFIRMATTACH    string    `json:"sellconfirmattach" xorm:"SELLCONFIRMATTACH"`                  // 卖方确认附件
	SELLCONFIRMTIME      time.Time `json:"sellconfirmtime" xorm:"SELLCONFIRMTIME"`                      // 卖方确认时间
	SELLCONFIRMTRADEDATE string    `json:"sellconfirmtradedate" xorm:"SELLCONFIRMTRADEDATE"`            // 卖方确认交易日
	BUYCONFIRMREMARK     string    `json:"buyconfirmremark" xorm:"BUYCONFIRMREMARK"`                    // 买方确认备注
	BUYCONFIRMTIME       time.Time `json:"buyconfirmtime" xorm:"BUYCONFIRMTIME"`                        // 买方确认时间
	HANDLESTATUS         int32     `json:"handlestatus" xorm:"HANDLESTATUS"`                            // 处理状态
	CONTRACTADDRBUY      string    `json:"contractaddrbuy" xorm:"CONTRACTADDRBUY"`                      // 贸易合同地址(买家)
	CONTRACTADDRSELL     string    `json:"contractaddrsell" xorm:"CONTRACTADDRSELL"`                    // 贸易合同地址(卖家)
	PAYEDTRADEDATE       string    `json:"payedtradedate" xorm:"PAYEDTRADEDATE"`                        // 支付尾款成功交易日
	BUYFREEZECHARGE      float64   `json:"buyfreezecharge" xorm:"BUYFREEZECHARGE"`                      // 买方摘牌冻结手续费
	LASTPRICEDATE        time.Time `json:"lastpricedate" xorm:"LASTPRICEDATE"`                          // 到期价格日期 - 暂不使用

	WRSTANDARDNAME string `json:"wrstandardname" xorm:"WRSTANDARDNAME"` // 现货商品名称
	WAREHOUSENAME  string `json:"warehousename" xorm:"WAREHOUSENAME"`   // 仓库名称

	THJDELIVERYMODEDISPLAY string `json:"thjdeliverymodedisplay" xorm:"THJDELIVERYMODEDISPLAY"` // 提货方式
	THJORDERSTATUSDISPLAY  string `json:"thjorderstatusdisplay" xorm:"THJORDERSTATUSDISPLAY"`   // 状态

	ENDDATEMONTH  string  `json:"enddatemonth" xorm:"ENDDATEMONTH"`   // 交收月份(提货日期)
	ENDDATE       string  `json:"enddate" xorm:"ENDDATE"`             // 到期日期
	UNITID        int32   `json:"unitid" xorm:"UNITID"`               // 商品单位
	TRANSFERPRICE float64 `json:"transferprice" xorm:"TRANSFERPRICE"` // 转让价格 - 购买价
	CREATETIME    string  `json:"createtime" xorm:"CREATETIME"`       // 预售挂牌时间

	PRESALESTATUS int32 `json:"presalestatus" xorm:"PRESALESTATUS"` // 预留状态 1：未开始 2：进行中 3：已结束 4：已关闭 5：处理中 6:：处理失败 7：已完成

	FIRSTBUYUSERID   int64   `json:"firstbuyuserid" xorm:"FIRSTBUYUSERID"`     // 买方用户ID(预售摘牌人)
	AVAILABLETIME    int32   `json:"availabletime" xorm:"AVAILABLETIME"`       // 可用次数
	POINTPRICE       float64 `json:"pointprice" xorm:"POINTPRICE"`             // 已点价格
	POINTFLAG        bool    `json:"pointflag" xorm:"POINTFLAG"`               // 是否已点价
	POINTINCOME      float64 `json:"pointincome" xorm:"POINTINCOME"`           // 点价总收益
	POINTTIMES       int32   `json:"pointtimes" xorm:"POINTTIMES"`             // 点价总次
	TODAYPOINTINCOME float64 `json:"todaypointincome" xorm:"TODAYPOINTINCOME"` // 点价总收益

	TYPE int `json:"-" xorm:"-" form:"type"` // 类型 - 0:未完成 1:已完成 2:可点订单 3:已点订单

	PageEx `xorm:"extends"` // 页码信息
}

func (r *Thjpurchasetradedetail) calc() {

}

func (r *Thjpurchasetradedetail) buildSql() string {
	var sqlId utils.SQLVal = `
	select 
		wr.WRSTANDARDNAME,
		wh.WAREHOUSENAME,
		en1.enumdicname THJDELIVERYMODEDISPLAY,
		en2.enumdicname THJORDERSTATUSDISPLAY,
		to_char(t.WRTRADEDETAILID) WRTRADEDETAILID,
		to_char(t.PRESALEAPPLYID) PRESALEAPPLYID,
		t.TRADEDATE,
		t.MARKETID,
		to_char(t.WRFACTORTYPEID) WRFACTORTYPEID,
		t.DELIVERYGOODSID,
		t.WRSTANDARDID,
		t.WAREHOUSEID,
		t.BUYUSERID,
		t.BUYACCOUNTID,
		t.SELLUSERID,
		t.SELLACCOUNTID,
		to_char(t.TRADETIME,'yyyy-mm-dd hh24:mi:ss') TRADETIME,
		t.TRADEPRICE,
		t.TRADEQTY,
		t.TRADEAMOUNT,
		t.DEPOSITRATE,
		t.DISCOUNTAMOUNT,
		t.PAYEDDEPOSIT,
		t.THJDELIVERYMODE,
		t.CONTACTNAME,
		t.CONTACTINFO,
		t.DESADDRESS,
		t.RECEIPTINFO,
		t.ORDERSTATUS,
		t.LASTPRICE,
		t.LASTAMOUNT,
		t.BUYCHARGEVALUE,
		t.SELLCHARGEVALUE,
		t.REMAINAMOUNT,
		t.SELLCONFIRMREMARK,
		t.SELLCONFIRMATTACH,
		t.SELLCONFIRMTIME,
		t.SELLCONFIRMTRADEDATE,
		t.BUYCONFIRMREMARK,
		t.BUYCONFIRMTIME,
		t.HANDLESTATUS,
		t.CONTRACTADDRBUY,
		t.CONTRACTADDRSELL,
		t.PAYEDTRADEDATE,
		t.BUYFREEZECHARGE,
		t.LASTPRICEDATE,
		to_char(pi.TAKESTARTDATE, 'yyyy-MM') ENDDATEMONTH,
        to_char(pi.ENDDATE, 'yyyy-MM-dd') ENDDATE,
		wr.UNITID,
		t.TRANSFERPRICE,
		pi.PRESALESTATUS,
		to_char(pi.CREATETIME, 'yyyy-MM-dd hh24:mi:ss') CREATETIME,
		t.FIRSTBUYUSERID,
		t.POINTTIMES,
		t.AVAILABLETIME,
		t.POINTPRICE,
		t.POINTFLAG,
		t.POINTINCOME,
		t.TODAYPOINTINCOME
	from THJ_PurchaseTradeDetail t
	inner join WR_PresaleInfo pi 
		on t.presaleapplyid = pi.presaleapplyid
	left join wrstandard wr
		on t.wrstandardid = wr.wrstandardid
	left join warehouseinfo wh
		on t.warehouseid = wh.autoid
	left join enumdicitem en1
		on en1.enumdiccode = 'THJDeliveryMode'
	and t.thjdeliverymode = en1.enumitemname
	left join enumdicitem en2
		on en2.enumdiccode = 'THJOrderStatus'
	and t.orderstatus = en2.enumitemname
	where t.buyuserid = %v
	and t.orderstatus in (4,6,7,8)
	order by t.tradetime desc
	`
	sqlId.FormatParam(r.BUYUSERID)

	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

func (r *Thjpurchasetradedetail) buildSql2() string {
	var sqlId utils.SQLVal = `select aa.* from (
	select 
		wr.WRSTANDARDNAME,
		wh.WAREHOUSENAME,
		en1.enumdicname THJDELIVERYMODEDISPLAY,
		en2.enumdicname THJORDERSTATUSDISPLAY,
		case t.orderstatus
			when 3 then 1
			when 5 then 2
			when 9 then 3
			when 1 then 10
			when 2 then 11
		else 20 end OrderIndex,
		t.WRTRADEDETAILID,
		t.PRESALEAPPLYID,
		t.TRADEDATE,
		t.MARKETID,
		t.WRFACTORTYPEID,
		t.DELIVERYGOODSID,
		t.WRSTANDARDID,
		t.WAREHOUSEID,
		t.BUYUSERID,
		t.BUYACCOUNTID,
		t.SELLUSERID,
		t.SELLACCOUNTID,
		to_char(t.TRADETIME,'yyyy-mm-dd hh24:mi:ss') TRADETIME,
		t.TRADEPRICE,
		t.TRADEQTY,
		t.TRADEAMOUNT,
		t.DEPOSITRATE,
		t.DISCOUNTAMOUNT,
		t.PAYEDDEPOSIT,
		t.THJDELIVERYMODE,
		t.CONTACTNAME,
		t.CONTACTINFO,
		t.DESADDRESS,
		t.RECEIPTINFO,
		t.ORDERSTATUS,
		t.LASTPRICE,
		t.LASTAMOUNT,
		t.BUYCHARGEVALUE,
		t.SELLCHARGEVALUE,
		t.REMAINAMOUNT,
		t.SELLCONFIRMREMARK,
		t.SELLCONFIRMATTACH,
		t.SELLCONFIRMTIME,
		t.SELLCONFIRMTRADEDATE,
		t.BUYCONFIRMREMARK,
		t.BUYCONFIRMTIME,
		t.HANDLESTATUS,
		t.CONTRACTADDRBUY,
		t.CONTRACTADDRSELL,
		t.PAYEDTRADEDATE,
		t.BUYFREEZECHARGE,
		t.LASTPRICEDATE,
		to_char(pi.TAKESTARTDATE, 'yyyy-MM') ENDDATEMONTH,
        to_char(pi.ENDDATE, 'yyyy-MM-dd') ENDDATE,
		wr.UNITID,
		t.TRANSFERPRICE,
		pi.PRESALESTATUS,
		to_char(pi.CREATETIME, 'yyyy-MM-dd') CREATETIME,
		t.FIRSTBUYUSERID,
		t.POINTTIMES,
		t.AVAILABLETIME,
		t.POINTPRICE,
		t.POINTFLAG,
		t.POINTINCOME,
		t.TODAYPOINTINCOME
	from THJ_PurchaseTradeDetail t
	inner join WR_PresaleInfo pi 
		on t.presaleapplyid = pi.presaleapplyid
	left join wrstandard wr
		on t.wrstandardid = wr.wrstandardid
	left join warehouseinfo wh
		on t.warehouseid = wh.autoid
	left join enumdicitem en1
		on en1.enumdiccode = 'THJDeliveryMode'
	and t.thjdeliverymode = en1.enumitemname
	left join enumdicitem en2
		on en2.enumdiccode = 'THJOrderStatus'
	and t.orderstatus = en2.enumitemname
	where t.buyuserid = %v
	and t.orderstatus in (1,2,3,5,9)) aa
	order by aa.orderindex, aa.tradetime desc
	`
	sqlId.FormatParam(r.BUYUSERID)

	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

func (r *Thjpurchasetradedetail) buildSql3() string {
	//可点订单： buyuserid = firstbuyuserid = [参数] && presalestatus = 2 && OrderStatus = 1 && availabletime > 0
	var sqlId utils.SQLVal = `
	select 
		wr.WRSTANDARDNAME,
		wh.WAREHOUSENAME,
		en1.enumdicname THJDELIVERYMODEDISPLAY,
		en2.enumdicname THJORDERSTATUSDISPLAY,
		to_char(t.WRTRADEDETAILID) WRTRADEDETAILID,
		to_char(t.PRESALEAPPLYID) PRESALEAPPLYID,
		t.TRADEDATE,
		t.MARKETID,
		to_char(t.WRFACTORTYPEID) WRFACTORTYPEID,
		t.DELIVERYGOODSID,
		t.WRSTANDARDID,
		t.WAREHOUSEID,
		t.BUYUSERID,
		t.BUYACCOUNTID,
		t.SELLUSERID,
		t.SELLACCOUNTID,
		to_char(t.TRADETIME,'yyyy-mm-dd hh24:mi:ss') TRADETIME,
		t.TRADEPRICE,
		t.TRADEQTY,
		t.TRADEAMOUNT,
		t.DEPOSITRATE,
		t.DISCOUNTAMOUNT,
		t.PAYEDDEPOSIT,
		t.THJDELIVERYMODE,
		t.CONTACTNAME,
		t.CONTACTINFO,
		t.DESADDRESS,
		t.RECEIPTINFO,
		t.ORDERSTATUS,
		t.LASTPRICE,
		t.LASTAMOUNT,
		t.BUYCHARGEVALUE,
		t.SELLCHARGEVALUE,
		t.REMAINAMOUNT,
		t.SELLCONFIRMREMARK,
		t.SELLCONFIRMATTACH,
		t.SELLCONFIRMTIME,
		t.SELLCONFIRMTRADEDATE,
		t.BUYCONFIRMREMARK,
		t.BUYCONFIRMTIME,
		t.HANDLESTATUS,
		t.CONTRACTADDRBUY,
		t.CONTRACTADDRSELL,
		t.PAYEDTRADEDATE,
		t.BUYFREEZECHARGE,
		t.LASTPRICEDATE,
		to_char(pi.TAKESTARTDATE, 'yyyy-MM') ENDDATEMONTH,
        to_char(pi.ENDDATE, 'yyyy-MM-dd') ENDDATE,
		wr.UNITID,
		t.TRANSFERPRICE,
		pi.PRESALESTATUS,
		to_char(pi.CREATETIME, 'yyyy-MM-dd hh24:mi:ss') CREATETIME,
		t.FIRSTBUYUSERID,
		t.POINTTIMES,
		t.AVAILABLETIME,
		t.POINTPRICE,
		t.POINTFLAG,
		t.POINTINCOME,
		t.TODAYPOINTINCOME
	from THJ_PurchaseTradeDetail t
	inner join WR_PresaleInfo pi 
		on t.presaleapplyid = pi.presaleapplyid
	left join wrstandard wr
		on t.wrstandardid = wr.wrstandardid
	left join warehouseinfo wh
		on t.warehouseid = wh.autoid
	left join enumdicitem en1
		on en1.enumdiccode = 'THJDeliveryMode'
	and t.thjdeliverymode = en1.enumitemname
	left join enumdicitem en2
		on en2.enumdiccode = 'THJOrderStatus'
	and t.orderstatus = en2.enumitemname
	where t.buyuserid = %v
	and t.buyuserid = t.firstbuyuserid
   	and t.orderstatus = 1   	
   	and pi.presalestatus = 2	
	and t.availabletime > 0
	order by t.tradetime desc
	`
	sqlId.FormatParam(r.BUYUSERID)

	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

func (r *Thjpurchasetradedetail) buildSql4() string {
	//已点订单： pointflag = 1 && firstbuyuserid = [参数]
	var sqlId utils.SQLVal = `
	select 
		wr.WRSTANDARDNAME,
		wh.WAREHOUSENAME,
		en1.enumdicname THJDELIVERYMODEDISPLAY,
		en2.enumdicname THJORDERSTATUSDISPLAY,
		to_char(t.WRTRADEDETAILID) WRTRADEDETAILID,
		to_char(t.PRESALEAPPLYID) PRESALEAPPLYID,
		t.TRADEDATE,
		t.MARKETID,
		to_char(t.WRFACTORTYPEID) WRFACTORTYPEID,
		t.DELIVERYGOODSID,
		t.WRSTANDARDID,
		t.WAREHOUSEID,
		t.BUYUSERID,
		t.BUYACCOUNTID,
		t.SELLUSERID,
		t.SELLACCOUNTID,
		to_char(t.TRADETIME,'yyyy-mm-dd hh24:mi:ss') TRADETIME,
		t.TRADEPRICE,
		t.TRADEQTY,
		t.TRADEAMOUNT,
		t.DEPOSITRATE,
		t.DISCOUNTAMOUNT,
		t.PAYEDDEPOSIT,
		t.THJDELIVERYMODE,
		t.CONTACTNAME,
		t.CONTACTINFO,
		t.DESADDRESS,
		t.RECEIPTINFO,
		t.ORDERSTATUS,
		t.LASTPRICE,
		t.LASTAMOUNT,
		t.BUYCHARGEVALUE,
		t.SELLCHARGEVALUE,
		t.REMAINAMOUNT,
		t.SELLCONFIRMREMARK,
		t.SELLCONFIRMATTACH,
		t.SELLCONFIRMTIME,
		t.SELLCONFIRMTRADEDATE,
		t.BUYCONFIRMREMARK,
		t.BUYCONFIRMTIME,
		t.HANDLESTATUS,
		t.CONTRACTADDRBUY,
		t.CONTRACTADDRSELL,
		t.PAYEDTRADEDATE,
		t.BUYFREEZECHARGE,
		t.LASTPRICEDATE,
		to_char(pi.TAKESTARTDATE, 'yyyy-MM') ENDDATEMONTH,
        to_char(pi.ENDDATE, 'yyyy-MM-dd') ENDDATE,
		wr.UNITID,
		t.TRANSFERPRICE,
		pi.PRESALESTATUS,
		to_char(pi.CREATETIME, 'yyyy-MM-dd hh24:mi:ss') CREATETIME,
		t.FIRSTBUYUSERID,
		t.POINTTIMES,
		t.AVAILABLETIME,
		t.POINTPRICE,
		t.POINTFLAG,
		t.POINTINCOME,
		t.TODAYPOINTINCOME
	from THJ_PurchaseTradeDetail t
	inner join WR_PresaleInfo pi 
		on t.presaleapplyid = pi.presaleapplyid
	left join wrstandard wr
		on t.wrstandardid = wr.wrstandardid
	left join warehouseinfo wh
		on t.warehouseid = wh.autoid
	left join enumdicitem en1
		on en1.enumdiccode = 'THJDeliveryMode'
	and t.thjdeliverymode = en1.enumitemname
	left join enumdicitem en2
		on en2.enumdiccode = 'THJOrderStatus'
	and t.orderstatus = en2.enumitemname
	where t.firstbuyuserid = %v
	and t.pointflag = 1
	order by t.tradetime desc
	`
	sqlId.FormatParam(r.BUYUSERID)

	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

func (r *Thjpurchasetradedetail) GetDataByPage() (interface{}, error, int, int, int) {
	//未完成
	sql := r.buildSql2()
	//已完成
	if r.TYPE == 1 {
		sql = r.buildSql()
	}
	//可点价
	if r.TYPE == 2 {
		sql = r.buildSql3()
	}
	//已点价
	if r.TYPE == 3 {
		sql = r.buildSql4()
	}
	sData := make([]Thjpurchasetradedetail, 0)
	err := db.GetEngine().SQL(sql).Find(&sData)
	total := 0
	for i := range sData {
		sData[i].calc()
		total = sData[i].Total
	}
	return sData, err, r.Page, r.PageSize, total
}

type ThjscoreconfigReq struct {
	STYPE string `form:"stype"` // [格式:1,2,3] 配置类型 - 1:注册红包 2:签到积分 3:推广积分 4:下级用户下单积分 5:自己采购下单积分 6:自己供求下单积分 7:抽奖配置
}

func (r *ThjscoreconfigReq) Get() (data []Thjscoreconfig, err error) {
	data = make([]Thjscoreconfig, 0)
	engine := db.GetEngine().NewSession()
	if r.STYPE != "" {
		engine = engine.Where(fmt.Sprintf("SCORECONFIGTYPE in (%v)", r.STYPE))
	}
	err = engine.Find(&data)

	return
}

type THJProduct struct {
	WRSTANDARDID   string `json:"wrstandardid" xorm:"WRSTANDARDID"`     // 现货商品ID(自增 SEQ_GOODS 确保不重复)
	WRSTANDARDCODE string `json:"wrstandardcode" xorm:"WRSTANDARDCODE"` // 现货商品代码
	WRSTANDARDNAME string `json:"wrstandardname" xorm:"WRSTANDARDNAME"` // 现货商品名称
	BANNERPICURL   string `json:"bannerpicurl" xorm:"BANNERPICURL"`     // Banner图(逗号分隔)
	PICTUREURLS    string `json:"pictureurls" xorm:"PICTUREURLS"`       // 详情图片(逗号分隔)
	THUMURLS       string `json:"thumurls" xorm:"THUMURLS"`             // 缩略图片(1:1)(逗号分隔)

	UserID       int  `json:"-" xorm:"-" form:"userid" binding:"required"` // 用户ID
	FavoriteFlag bool `json:"-" xorm:"-" form:"favoriteflag"`              // 关注标志 true-已关注 false-未关注

	PageEx `xorm:"extends"` // 页码信息
}

func (r *THJProduct) calc() {

}

func (r *THJProduct) buildSql() string {
	var sqlId utils.SQLVal = `
	select 
		to_char(t.WRSTANDARDID) WRSTANDARDID, 
		t.WRSTANDARDNAME, 
		t.BANNERPICURL, 
		t.PICTUREURLS,
		t.THUMURLS 
	from wrstandard t
	where t.IsValid = 1 
		and t.wrstandardid 
		%v  
		(select t.goodsid from UserFavoriteGoods t where t.userid = %v)
`
	param := "not in"
	if r.FavoriteFlag {
		param = "in"
	}
	sqlId.FormatParam(param, r.UserID)

	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

func (r *THJProduct) GetDataByPage() (interface{}, error, int, int, int) {
	sData := make([]THJProduct, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	total := 0
	for i := range sData {
		sData[i].calc()
		total = sData[i].Total
	}
	return sData, err, r.Page, r.PageSize, total
}

type THJTradeData struct {
	RECKONDATE   string  `json:"reckondate" xorm:"RECKONDATE"`                                // 日照日期(yyyyMMdd)日照日期(yyyyMMdd)
	MARKETID     int64   `json:"marketid" xorm:"MARKETID" form:"marketid" binding:"required"` // 市场ID 采购-64201 供求-65201
	BUYTRADELOT  int32   `json:"buytradelot" xorm:"BUYTRADELOT"`                              // 购买笔数
	BUYTRADEQTY  float64 `json:"buytradeqty" xorm:"BUYTRADEQTY"`                              // 购买数量
	SELLTRADELOT int32   `json:"selltradelot" xorm:"SELLTRADELOT"`                            // 销售笔数
	SELLTRADEQTY float64 `json:"selltradeqty" xorm:"SELLTRADEQTY"`                            // 销售数量
	USERID       int64   `json:"userid" xorm:"USERID" form:"userid" binding:"required"`       // 用户ID

	WRSTANDARDNAME string `json:"wrstandardname" xorm:"WRSTANDARDNAME"` // 现货商品名称

	ACCOUNTNAME string `json:"accountname" xorm:"ACCOUNTNAME"` // 被推荐人

	PageEx `xorm:"extends"` // 页码信息
}

func (r *THJTradeData) calc() {
	r.ACCOUNTNAME = EncryptByStar(r.ACCOUNTNAME)
}

func (r *THJTradeData) buildSql() string {
	var sqlId utils.SQLVal = `
	select 
		a.*
  	from 
		(select 
			to_char(to_date(t.reckondate, 'yyyymmdd'), 'yyyy-mm-dd') RECKONDATE,
			wr.WRSTANDARDNAME,
			ua.ACCOUNTNAME,
			t.BUYTRADEQTY, 
			t.BUYTRADELOT,
			t.SELLTRADEQTY,
			t.SELLTRADELOT,
			t.MARKETID,
			t.updatetime
		from Reckon_WRTradeSum t
		left join wrstandard wr on t.wrstandardid = wr.wrstandardid
		left join useraccount ua on t.userid = ua.userid
		where 
			t.refereeuserid = %v 
			and
			t.marketid = %v) a
 	order by a.updatetime desc
	`
	sqlId.FormatParam(r.USERID, r.MARKETID)

	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

func (r *THJTradeData) GetDataByPage() (interface{}, error, int, int, int) {
	sData := make([]THJTradeData, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	total := 0
	for i := range sData {
		sData[i].calc()
		total = sData[i].Total
	}
	return sData, err, r.Page, r.PageSize, total
}

// THJGoodsDetailReq 商品详情
type THJGoodsDetailReq struct {
	WRSTANDARDID int64 `form:"wrstandardid" binding:"required"` // 现货商品ID
}

// THJGoodsDetailRsp 商品详情
type THJGoodsDetailRsp struct {
	GoodsInfo          THJWrstandardDetail_GoodsInfo `json:"goodsinfo"`          // 商品信息
	SpotGoodsPriceLogs []THJSpotGoodsPriceLog        `json:"spotgoodspricelogs"` // 历史价格走势
}

func (r *THJGoodsDetailReq) GetTHJGoodsDetail() (rsp *THJGoodsDetailRsp, err error) {
	engine := db.GetEngine()

	rsp = new(THJGoodsDetailRsp)

	// 采购商品信息
	var goodsInfo THJWrstandardDetail_GoodsInfo
	sql := fmt.Sprintf(`
		select
			t.WRSTANDARDID,
			t.WRSTANDARDCODE,
			t.WRSTANDARDNAME,
			t.PICTUREURLS,
			t.BANNERPICURL,
			p.SPOTGOODSPRICE
		from wrstandard t
		left join ERMCP_SpotGoodsPrice p on t.WRSTANDARDID = p.WRSTANDARDID and p.spotgoodsbrandid=0 and p.currencyid = 1 
		where t.WRSTANDARDID = %v
	`, r.WRSTANDARDID)
	if _, err = engine.SQL(sql).Get(&goodsInfo); err != nil {
		return
	}
	rsp.GoodsInfo = goodsInfo

	// 历史价格走势
	spotGoodsPriceLogs := make([]THJSpotGoodsPriceLog, 0)
	sql = fmt.Sprintf(`
		select 
			t.SPOTGOODSPRICE, 
			t.TRADEDATE 
		from ERMCP_SpotGoodsPriceLog t
		where t.wrstandardid = %v 
			and t.SPOTGOODSBRANDID = 0 
			and t.CURRENCYID = 1
		order by t.tradedate
	`, r.WRSTANDARDID)
	if err = engine.SQL(sql).Find(&spotGoodsPriceLogs); err != nil {
		return
	}
	rsp.SpotGoodsPriceLogs = spotGoodsPriceLogs

	return
}

type PurchaseTransfer struct {
	WRSTANDARDID   int64  `json:"wrstandardid" xorm:"WRSTANDARDID"`                           // 现货商品ID(自增 SEQ_GOODS 确保不重复)
	WRSTANDARDCODE string `json:"wrstandardcode" xorm:"WRSTANDARDCODE"`                       // 现货商品代码
	WRSTANDARDNAME string `json:"wrstandardname" xorm:"WRSTANDARDNAME" form:"wrstandardname"` // 现货商品名称（模糊查询）
	UNITID         int32  `json:"unitid" xorm:"UNITID"`                                       // 现货商品单位ID
	THUMURLS       string `json:"thumurls" xorm:"THUMURLS"`                                   // 缩略图片(1:1)(逗号分隔)
	PICTUREURLS    string `json:"pictureurls" xorm:"PICTUREURLS"`                             // 详情图片(逗号分隔)
	REFGOODSID     int64  `json:"refgoodsid" xorm:"REFGOODSID"`                               // 参考商品ID

	PageEx `xorm:"extends"` // 页码信息
}

func (r *PurchaseTransfer) calc() {

}

func (r *PurchaseTransfer) buildSql() string {
	var sqlId utils.SQLVal = `
	select 
		wr.WRSTANDARDID  ,
		wr.WRSTANDARDCODE,
		wr.WRSTANDARDNAME,
		wr.UNITID        ,
		wr.THUMURLS      ,
		wr.PICTUREURLS   ,
		wr.REFGOODSID    
  	from wrstandard wr
	where wr.wrstandardid in (
		select td.wrstandardid
        from THJ_PurchaseTransfer t
        inner join THJ_PurchaseTradeDetail td on t.wrtradedetailid = td.wrtradedetailid
         where t.TransferStatus = 1)
   	and %v 
	order by wr.wrstandardname
`
	param := "1=1"
	if r.WRSTANDARDNAME != "" {
		param = fmt.Sprintf("wr.wrstandardname like '%%%v%%'", r.WRSTANDARDNAME)
	}
	sqlId.FormatParam(param)

	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

func (r *PurchaseTransfer) GetDataByPage() (interface{}, error, int, int, int) {
	sData := make([]PurchaseTransfer, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	total := 0
	for i := range sData {
		sData[i].calc()
		total = sData[i].Total
	}
	return sData, err, r.Page, r.PageSize, total
}

type THJPurchaseTradeDetail_Goodsinfo struct {
	WRSTANDARDID   int64  `json:"wrstandardid" xorm:"WRSTANDARDID"`                           // 现货商品ID(自增 SEQ_GOODS 确保不重复)
	WRSTANDARDCODE string `json:"wrstandardcode" xorm:"WRSTANDARDCODE"`                       // 现货商品代码
	WRSTANDARDNAME string `json:"wrstandardname" xorm:"WRSTANDARDNAME" form:"wrstandardname"` // 现货商品名称（模糊查询）
	UNITID         int32  `json:"unitid" xorm:"UNITID"`                                       // 现货商品单位ID
	PICTUREURLS    string `json:"pictureurls" xorm:"PICTUREURLS"`                             // 详情图片(逗号分隔)
	REFGOODSID     int64  `json:"refgoodsid" xorm:"REFGOODSID"`                               // 参考商品ID
	REFGOODSCODE   string `json:"refgoodscode" xorm:"REFGOODSCODE"`                           // 参考商品代码
	MINIVALUE      int64  `json:"minivalue" xorm:"MINIVALUE"`                                 // 最小变动值
	MINIVALUEDP    int64  `json:"minivaluedp" xorm:"MINIVALUEDP"`                             // 最小变动值小数位
}

type THJPurchaseTradeDetail_Date struct {
	ENDMONTH      string `json:"endmonth" xorm:"ENDMONTH"`           // 交收月份(yyyy-mm)
	TAKESTARTDATE string `json:"takestartdate" xorm:"TAKESTARTDATE"` // 提货开始日期
	ENDDATE       string `json:"enddate" xorm:"ENDDATE"`             // 预售结束日期

	DEPOSITRATE float64 `json:"depositrate" xorm:"DEPOSITRATE"` // 定金比例
	TRADEPRICE  float64 `json:"tradeprice" xorm:"TRADEPRICE"`   // 摘牌价格
	TRADEQTY    int64   `json:"tradeqty" xorm:"TRADEQTY"`       // 摘牌数量

	TRANSFERID     string  `json:"transferid" xorm:"TRANSFERID"`         // 协议转让ID(332+Unix秒时间戳(10位)+xxxxxx)
	TRANSFERPRICE  float64 `json:"transferprice" xorm:"TRANSFERPRICE"`   // 转让价格
	TRANSFERAMOUNT float64 `json:"transferamount" xorm:"TRANSFERAMOUNT"` // 转让金额
}

type HistoryData struct {
	Opened        float64   `json:"o"`  // 开盘价
	Highest       float64   `json:"h"`  // 最高价
	Lowest        float64   `json:"l"`  // 最低价
	Closed        float64   `json:"c"`  // 收盘价
	TotleVolume   int       `json:"tv"` // 总量（成交量）
	TotleTurnover float64   `json:"tt"` // 总金额
	HoldVolume    int       `json:"hv"` // 持仓量
	Settle        float64   `json:"s"`  // 结算价，日线周期（包括）以上才有
	TimeStamp     time.Time `json:"ts"` // 时间
	IsFill        bool      `json:"f"`  // 是否补充数据
}

type THJPurchaseTradeDetailReq struct {
	WRSTANDARDID int64 `form:"wrstandardid" binding:"required"` // 现货商品ID
}

// THJPurchaseTradeDetailRsp 合同转让详情
type THJPurchaseTradeDetailRsp struct {
	GoodsInfo          THJPurchaseTradeDetail_Goodsinfo `json:"goodsinfo"`          // 商品信息
	DeliveryDates      []THJPurchaseTradeDetail_Date    `json:"deliverydate"`       // 交割日期
	SpotGoodsPriceLogs []THJSpotGoodsPriceLog           `json:"spotgoodspricelogs"` // 历史价格走势
	// History       []HistoryData                    `json:"history"`      // 历史价格走势（历史数据日线）
}

// GetTHJWrstandardDetail 获取采购商品详情
func (r *THJPurchaseTradeDetailReq) GetData() (rsp *THJPurchaseTradeDetailRsp, err error) {
	engine := db.GetEngine()

	rsp = new(THJPurchaseTradeDetailRsp)

	// 采购商品信息
	var goodsInfo THJPurchaseTradeDetail_Goodsinfo
	sql := fmt.Sprintf(`
		select 
			t.WRSTANDARDID  ,
			t.WRSTANDARDCODE,
			t.WRSTANDARDNAME,
			t.UNITID        ,
			t.PICTUREURLS   ,
			t.REFGOODSID    ,
			t.REFGOODSCODE,
			t.MINIVALUE,
			t.MINIVALUEDP
		from wrstandard t
		where t.wrstandardid = %v
	`, r.WRSTANDARDID)
	if _, err = engine.SQL(sql).Get(&goodsInfo); err != nil {
		return
	}
	rsp.GoodsInfo = goodsInfo

	// 交割日期
	deliveryDates := make([]THJPurchaseTradeDetail_Date, 0)
	sql = fmt.Sprintf(`
	select 
		to_char(pi.TAKESTARTDATE, 'yyyy-mm') ENDMONTH,
		to_char(pi.TAKESTARTDATE, 'yyyy-mm-dd') TAKESTARTDATE,
		to_char(pi.ENDDATE, 'yyyy-mm-dd') ENDDATE,
		td.DEPOSITRATE,
		td.TRADEPRICE,
		td.TRADEQTY,
		t.TRANSFERPRICE,
		t.TRANSFERAMOUNT,
		wr.REFGOODSID,
		to_char(t.TRANSFERID) TRANSFERID
	from THJ_PurchaseTransfer t
	inner join THJ_PurchaseTradeDetail td on t.wrtradedetailid = td.wrtradedetailid
	inner join WR_PresaleInfo pi on t.presaleapplyid = pi.presaleapplyid
	inner join wrstandard wr on wr.wrstandardid = td.wrstandardid
	where t.transferstatus = 1 and td.wrstandardid = %v
	order by pi.takestartdate, td.depositrate
	`, r.WRSTANDARDID)
	if err = engine.SQL(sql).Find(&deliveryDates); err != nil {
		return
	}
	rsp.DeliveryDates = deliveryDates

	// 历史价格走势
	// cycleDatas, err := GetHistoryCycleDatas(CycleTypeMinutesDay, goodsInfo.REFGOODSCODE, nil, nil, 50, true)
	// if err != nil {
	// 	return
	// }
	// // 获取目标商品报价小数位
	// dcplace := mtpcache.GetGoodsDecimalplace(goodsInfo.REFGOODSCODE)
	// // 计算最终价格
	// rst := make([]HistoryData, 0)
	// for _, v := range cycleDatas {
	// 	historyData := HistoryData{
	// 		Opened:        utils.IntToFloat64(v.Open, dcplace),
	// 		Highest:       utils.IntToFloat64(v.High, dcplace),
	// 		Lowest:        utils.IntToFloat64(v.Low, dcplace),
	// 		Closed:        utils.IntToFloat64(v.Close, dcplace),
	// 		TotleVolume:   v.TV,
	// 		TotleTurnover: float64(v.TT),
	// 		HoldVolume:    v.HV,
	// 		Settle:        utils.IntToFloat64(v.SP, dcplace),
	// 		TimeStamp:     time.Unix(int64(v.ST), 0),
	// 	}
	// 	rst = append(rst, historyData)
	// }
	// rsp.History = rst

	// 历史价格走势
	spotGoodsPriceLogs := make([]THJSpotGoodsPriceLog, 0)
	sql = fmt.Sprintf(`
		select 
			t.SPOTGOODSPRICE, 
			t.TRADEDATE 
		from ERMCP_SpotGoodsPriceLog t
		where t.wrstandardid = %v 
			and t.SPOTGOODSBRANDID = 0 
			and t.CURRENCYID = 1
		order by t.tradedate
	`, r.WRSTANDARDID)
	if err = engine.SQL(sql).Find(&spotGoodsPriceLogs); err != nil {
		return
	}
	rsp.SpotGoodsPriceLogs = spotGoodsPriceLogs

	return
}

// Thjpurchasetransfer 铁合金采购协议表
type Thjpurchasetransfer struct {
	TRANSFERID               string  `json:"transferid" xorm:"TRANSFERID"`                             // 协议转让ID(332+Unix秒时间戳(10位)+xxxxxx)
	WRTRADEDETAILID          string  `json:"wrtradedetailid" xorm:"WRTRADEDETAILID"`                   // 采购成交单ID(321+Unix秒时间戳(10位)+xxxxxx)
	PRESALEAPPLYID           string  `json:"presaleapplyid" xorm:"PRESALEAPPLYID"`                     // 预售申请ID(184+Unix秒时间戳(10位)+xxxxxx)
	TRADEDATE                string  `json:"tradedate" xorm:"TRADEDATE"`                               // 交易日(yyyyMMdd)
	WRFACTORTYPEID           string  `json:"wrfactortypeid" xorm:"WRFACTORTYPEID"`                     // 仓单要素类型ID
	DELIVERYGOODSID          int32   `json:"deliverygoodsid" xorm:"DELIVERYGOODSID"`                   // 现货品种ID
	WRSTANDARDID             int32   `json:"wrstandardid" xorm:"WRSTANDARDID"`                         // 现货商品ID
	WAREHOUSEID              int64   `json:"warehouseid" xorm:"WAREHOUSEID"`                           // 仓库ID
	MARKETID                 int32   `json:"marketid" xorm:"MARKETID"`                                 // 市场ID(固定为(64202)
	SELLUSERID               int64   `json:"selluserid" xorm:"SELLUSERID"`                             // 卖方用户ID
	SELLACCOUNTID            int64   `json:"sellaccountid" xorm:"SELLACCOUNTID"`                       // 卖方账号ID
	TRANSFERQTY              int64   `json:"transferqty" xorm:"TRANSFERQTY"`                           // 数量
	TRANSFERPRICE            float64 `json:"transferprice" xorm:"TRANSFERPRICE"`                       // 转让价格
	TRANSFERAMOUNT           float64 `json:"transferamount" xorm:"TRANSFERAMOUNT"`                     // 转让金额
	REMARK                   string  `json:"remark" xorm:"REMARK"`                                     // 备注
	BUYUSERID                int64   `json:"buyuserid" xorm:"BUYUSERID"`                               // 买方用户ID
	BUYACCOUNTID             int64   `json:"buyaccountid" xorm:"BUYACCOUNTID"`                         // 买方账号ID
	CONTRACTADDR             string  `json:"contractaddr" xorm:"CONTRACTADDR"`                         // 协议转让合同地址
	SELLCHARGEALGORITHM      int32   `json:"sellchargealgorithm" xorm:"SELLCHARGEALGORITHM"`           // 卖方手续费收取方式 1：比率  2：固定
	SELLCHARGEALGORITHMVALUE float64 `json:"sellchargealgorithmvalue" xorm:"SELLCHARGEALGORITHMVALUE"` // 手续费设置值(交易所部分)
	SELLCHARGE               float64 `json:"sellcharge" xorm:"SELLCHARGE"`                             // 卖方手续费值
	TRANSFERTRADEDATE        string  `json:"transfertradedate" xorm:"TRANSFERTRADEDATE"`               // 转让交易日
	TRANSFERSTATUS           int32   `json:"transferstatus" xorm:"TRANSFERSTATUS"`                     // 转让状态 - 1:挂牌中 2:已撤销 3:处理中 4:已转让
	HANDLESTATUS             int32   `json:"handlestatus" xorm:"HANDLESTATUS"`                         // 处理状态

	TRADEPRICE   float64 `json:"tradeprice" xorm:"TRADEPRICE"`     // 参考价
	PAYEDDEPOSIT float64 `json:"payeddeposit" xorm:"PAYEDDEPOSIT"` // 买方已付定金(预付款)

	WRSTANDARDNAME string `json:"wrstandardname" xorm:"WRSTANDARDNAME"` // 现货商品名称

	USERID    int64 `json:"-" xorm:"-" form:"userid" binding:"required"`                    // 用户ID
	ORDERTYPE int   `json:"ordertype" xorm:"ORDERTYPE" form:"ordertype" binding:"required"` // 类型 - 1:转让委托 - 挂牌中(卖方) 2:转让成交(卖方) 3:转让委托 - 已完成(卖方)

	UNITID int32 `json:"unitid" xorm:"UNITID"` // 现货商品单位ID

	ENDDATEMONTH      string `json:"enddatemonth" xorm:"ENDDATEMONTH"`           // 交收月份(提货日期)
	ENDDATE           string `json:"enddate" xorm:"ENDDATE"`                     // 到期日期
	CREATETIME        string `json:"createtime" xorm:"CREATETIME"`               // 转让挂牌时间
	TRANSFERTRADETIME string `json:"transfertradetime" xorm:"TRANSFERTRADETIME"` // 转让成交时间

	PRESALESTATUS int32 `json:"presalestatus" xorm:"PRESALESTATUS"` // 预售状态 1：未开始 2：进行中 3：已结束 4：已关闭 5：处理中 6:：处理失败 7：已完成

	POINTFLAG   bool    `json:"pointflag" xorm:"POINTFLAG"`     // 是否已点价
	POINTPRICE  float64 `json:"pointprice" xorm:"POINTPRICE"`   // 已点价格
	POINTINCOME float64 `json:"pointincome" xorm:"POINTINCOME"` // 点价总收益

	PageEx `xorm:"extends"` // 页码信息
}

func (r *Thjpurchasetransfer) calc() {

}

func (r *Thjpurchasetransfer) buildSql() string {
	var sqlId utils.SQLVal = `
	select 
	    wr.WRSTANDARDNAME, 
		p.TRADEPRICE, 
		p.PAYEDDEPOSIT, 
		to_char(t.TRANSFERID) TRANSFERID,
		to_char(t.WRTRADEDETAILID) WRTRADEDETAILID,
		to_char(t.PRESALEAPPLYID) PRESALEAPPLYID,
		t.TRADEDATE,
		to_char(t.WRFACTORTYPEID) WRFACTORTYPEID,
		t.DELIVERYGOODSID,
		t.WRSTANDARDID,
		t.WAREHOUSEID,
		t.MARKETID,
		t.SELLUSERID,
		t.SELLACCOUNTID,
		t.TRANSFERQTY,
		t.TRANSFERPRICE,
		t.TRANSFERAMOUNT,
		t.REMARK,
		t.BUYUSERID,
		t.BUYACCOUNTID,
		t.CONTRACTADDR,
		t.SELLCHARGEALGORITHM,
		t.SELLCHARGEALGORITHMVALUE,
		t.SELLCHARGE,
		t.TRANSFERTRADEDATE,
		t.TRANSFERSTATUS,
		t.HANDLESTATUS,
		wr.UNITID,
		to_char(pi.TAKESTARTDATE, 'yyyy-MM') ENDDATEMONTH,
        to_char(pi.ENDDATE, 'yyyy-MM-dd') ENDDATE,
		pi.PRESALESTATUS,
		to_char(t.CREATETIME, 'yyyy-MM-dd hh24:mi:ss') CREATETIME,
		to_char(t.TRANSFERTRADETIME, 'yyyy-MM-dd hh24:mi:ss') TRANSFERTRADETIME,
		%v ORDERTYPE,
		p.POINTFLAG, 
        p.POINTPRICE,
        p.POINTINCOME
	from THJ_PurchaseTransfer t
	inner join THJ_PurchaseTradeDetail p on t.wrtradedetailid = p.wrtradedetailid
	inner join wr_presaleinfo pi on pi.presaleapplyid = t.presaleapplyid
	inner join wrstandard wr on t.wrstandardid = wr.wrstandardid
	where %v and 1=1
	order by t.CREATETIME desc
`
	//转让委托 - 挂牌中  -卖方
	param := fmt.Sprintf("t.selluserid = %v and t.transferstatus = 1", r.USERID)
	if r.ORDERTYPE == 2 {
		//转让成交 - 买方
		param = fmt.Sprintf("t.buyuserid = %v", r.USERID)
	} else if r.ORDERTYPE == 3 {
		//转让委托 - 已完成 - 卖方
		param = fmt.Sprintf("t.selluserid = %v and t.transferstatus in (2,3,4)", r.USERID)
	}
	sqlId.FormatParam(r.ORDERTYPE, param)

	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

func (r *Thjpurchasetransfer) GetDataByPage() (interface{}, error, int, int, int) {
	sData := make([]Thjpurchasetransfer, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	total := 0
	for i := range sData {
		sData[i].calc()
		total = sData[i].Total
	}
	return sData, err, r.Page, r.PageSize, total
}

type ThjpurchasetransferDetail struct {
	WRTRADEDETAILID  string  `json:"wrtradedetailid" xorm:"WRTRADEDETAILID"`         // 采购成交单ID(321+Unix秒时间戳(10位)+xxxxxx)
	TRANSFERID       string  `json:"transferid" xorm:"TRANSFERID" form:"transferid"` // 转让单号
	WRSTANDARDID     int32   `json:"wrstandardid" xorm:"WRSTANDARDID"`               // 现货商品ID
	TRANSFERPRICE    float64 `json:"transferprice" xorm:"TRANSFERPRICE"`             // 转让价格
	TRANSFERAMOUNT   float64 `json:"transferamount" xorm:"TRANSFERAMOUNT"`           // 转让金额
	TRANSFERSTATUS   int32   `json:"transferstatus" xorm:"TRANSFERSTATUS"`           // 转让状态 - 1:挂牌中 2:已撤销 3:处理中 4:已转让
	TRADEPRICE       float64 `json:"tradeprice" xorm:"TRADEPRICE"`                   // 预售价格
	PAYEDDEPOSIT     float64 `json:"payeddeposit" xorm:"PAYEDDEPOSIT"`               // 买方已付定金(预付款(含定金))
	CONTRACTADDRBUY  string  `json:"contractaddrbuy" xorm:"CONTRACTADDRBUY"`         // 贸易合同地址(买家)
	CONTRACTADDRSELL string  `json:"contractaddrsell" xorm:"CONTRACTADDRSELL"`       // 贸易合同地址(卖家)
	WRSTANDARDCODE   string  `json:"wrstandardcode" xorm:"WRSTANDARDCODE"`           // 现货商品代码
	WRSTANDARDNAME   string  `json:"wrstandardname" xorm:"WRSTANDARDNAME"`           // 现货商品名称
	WAREHOUSENAME    string  `json:"warehousename" xorm:"WAREHOUSENAME"`             // 仓库名称
	BUYUSERNAME      string  `json:"buyusername" xorm:"BUYUSERNAME"`                 // 受让人
	SELLUSERNAME     string  `json:"sellusername" xorm:"SELLUSERNAME"`               // 转让人

	TRANSFERQTY int32 `json:"transferqty" xorm:"TRANSFERQTY"` // 转让数量

	DEPOSITRATE       float64 `json:"depositrate" xorm:"DEPOSITRATE"`             // 定金比例
	LASTPRICE         float64 `json:"lastprice" xorm:"LASTPRICE"`                 // [实际价]到期价格
	THJDELIVERYMODE   int32   `json:"thjdeliverymode" xorm:"THJDELIVERYMODE"`     // 交割方式 - 1：平台仓储 2：自提 3：代办运输 (枚举：THJDeliveryMode)
	CONTACTNAME       string  `json:"contactname" xorm:"CONTACTNAME"`             // 联系人姓名
	CONTACTINFO       string  `json:"contactinfo" xorm:"CONTACTINFO"`             // 联系方式
	DESADDRESS        string  `json:"desaddress" xorm:"DESADDRESS"`               // 目的地地址
	RECEIPTINFO       string  `json:"receiptinfo" xorm:"RECEIPTINFO"`             // 发票信息
	ENDDATEMONTH      string  `json:"enddatemonth" xorm:"ENDDATEMONTH"`           // 交收月份(提货日期)
	ENDDATE           string  `json:"enddate" xorm:"ENDDATE"`                     // 到期日期
	CREATETIME        string  `json:"createtime" xorm:"CREATETIME"`               // 转让委托时间
	TRANSFERTRADETIME string  `json:"transfertradetime" xorm:"TRANSFERTRADETIME"` // 转让成交时间
	CONTRACTADDR      string  `json:"contractaddr" xorm:"CONTRACTADDR"`           // 协议转让合同地址

	POINTFLAG   bool    `json:"pointflag" xorm:"POINTFLAG"`     // 是否已点价
	POINTPRICE  float64 `json:"pointprice" xorm:"POINTPRICE"`   // 已点价格
	POINTINCOME float64 `json:"pointincome" xorm:"POINTINCOME"` // 点价总收益

	SELLUSERID     int64 `json:"-" xorm:"SELLUSERID"`                  // 转让卖方用户ID
	FIRSTBUYUSERID int64 `json:"firstbuyuserid" xorm:"FIRSTBUYUSERID"` // 预售采购用户ID

}

func (r *ThjpurchasetransferDetail) calc() {

}

func (r *ThjpurchasetransferDetail) buildSql() string {
	var sqlId utils.SQLVal = `
	select 
		to_char(t.TRANSFERID) TRANSFERID,
		t.WRSTANDARDID,
		t.TRANSFERPRICE,
		t.TRANSFERAMOUNT,
		t.TRANSFERSTATUS,
		d.TRADEPRICE,
		d.PAYEDDEPOSIT,
		d.CONTRACTADDRBUY,
		d.CONTRACTADDRSELL,
		wr.WRSTANDARDCODE,
		wr.WRSTANDARDNAME,
		wh.WAREHOUSENAME,
		u1.accountname BUYUSERNAME,
		u2.accountname SELLUSERNAME,
		t.TRANSFERQTY,
		d.DEPOSITRATE,
		d.PAYEDDEPOSIT,
		d.LASTPRICE,
		d.THJDELIVERYMODE,
		d.CONTACTNAME,
		d.CONTACTINFO,
		d.DESADDRESS,
		d.RECEIPTINFO,
		to_char(pi.TAKESTARTDATE, 'yyyy-MM') ENDDATEMONTH,
        to_char(pi.ENDDATE, 'yyyy-MM-dd') ENDDATE,
		to_char(t.CREATETIME, 'yyyy-MM-dd hh24:mi:ss') CREATETIME,
		to_char(t.TRANSFERTRADETIME, 'yyyy-MM-dd hh24:mi:ss') TRANSFERTRADETIME,
		to_char(t.WRTRADEDETAILID) WRTRADEDETAILID,
		t.CONTRACTADDR,
		t.SELLUSERID,
		d.FIRSTBUYUSERID,
		d.POINTFLAG, 
        d.POINTPRICE,
        d.POINTINCOME
	from THJ_PurchaseTransfer t 
	inner join THJ_PurchaseTradeDetail d on d.WRTRADEDETAILID = t.WRTRADEDETAILID
	left join wr_presaleinfo pi on pi.presaleapplyid = t.presaleapplyid
	left join wrstandard wr on wr.wrstandardid = t.wrstandardid
	left join WAREHOUSEINFO wh on wh.autoid = t.warehouseid
	left join useraccount u1 on u1.userid = t.buyuserid
	left join useraccount u2 on u2.userid = t.selluserid
	where t.TRANSFERID = %v
	`
	sqlId.FormatParam(r.TRANSFERID)

	return sqlId.String()
}

// GetDataEx 从数据库中查询数据
func (r *ThjpurchasetransferDetail) GetDataEx() (interface{}, error) {
	e := db.GetEngine()
	s := e.SQL(r.buildSql())
	sData := make([]ThjpurchasetransferDetail, 0)
	if err := s.Find(&sData); err != nil {
		return nil, err
	}
	for i := range sData {
		sData[i].calc()
	}
	return sData, nil
}

type PromotionIncome struct {
	PROFITMONTH  string  `json:"profitmonth" xorm:"PROFITMONTH"`                       // 时间
	SUMAMOUNT    float64 `json:"sumamount" xorm:"SUMAMOUNT"`                           // 金额
	PROFITSTATUS int32   `json:"profitstatus" xorm:"PROFITSTATUS" form:"profitstatus"` // 状态 - 1:已支付 2:未支付 3:支取中

	USERID int64 `json:"-" xorm:"-" form:"userid" binding:"required"` // 用户ID

	PageEx `xorm:"extends"` // 页码信息
}

func (r *PromotionIncome) calc() {

}

func (r *PromotionIncome) buildSql() string {
	var sqlId utils.SQLVal = `
	select 
		substr(t.reckondate, 0, 6) PROFITMONTH,		
		sum(t.profitamount) SUMAMOUNT
	from THJ_Reckon_ProfitSum t
	where t.userid = %v and %v
	group by substr(t.reckondate, 0, 6)
	order by substr(t.reckondate, 0, 6) desc
`
	param := "1=1"
	if r.PROFITSTATUS > 0 {
		param = fmt.Sprintf("t.profitstatus = %v", r.PROFITSTATUS)
	}
	sqlId.FormatParam(r.USERID, param)

	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

func (r *PromotionIncome) GetDataByPage() (interface{}, error, int, int, int) {
	sData := make([]PromotionIncome, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	total := 0
	for i := range sData {
		sData[i].calc()
		total = sData[i].Total
	}
	return sData, err, r.Page, r.PageSize, total
}

type PromotionIncomeDetail struct {
	TRADETIME      string  `json:"tradetime" xorm:"TRADETIME"`           // 时间
	WRSTANDARDID   int64   `json:"wrstandardid" xorm:"WRSTANDARDID"`     // 现货商品ID(自增 SEQ_GOODS 确保不重复)
	WRSTANDARDCODE string  `json:"wrstandardcode" xorm:"WRSTANDARDCODE"` // 现货商品代码
	WRSTANDARDNAME string  `json:"wrstandardname" xorm:"WRSTANDARDNAME"` // 现货商品名称
	MARKET         string  `json:"market" xorm:"MARKET"`                 // 市场
	BUYORSELL      string  `json:"buyorsell" xorm:"BUYORSELL"`           // 方向
	PROFITROLETYPE string  `json:"profitroletype" xorm:"PROFITROLETYPE"` // 级别
	PROFITAMOUNT   float64 `json:"profitamount" xorm:"PROFITAMOUNT"`     // 金额

	USERID int64  `json:"-" xorm:"-" form:"userid" binding:"required"` // 用户ID
	MOUTH  string `json:"-" xorm:"-" form:"mouth" binding:"required"`  // 月份，格式：yyyymm

	PageEx `xorm:"extends"` // 页码信息
}

func (r *PromotionIncomeDetail) calc() {

}

func (r *PromotionIncomeDetail) buildSql() string {
	var sqlId utils.SQLVal = `
	select 
		to_char(t.TRADETIME,'yyyy-mm-dd hh24:mi:ss') TRADETIME, 
		wr.WRSTANDARDID,
		wr.WRSTANDARDCODE,
		wr.WRSTANDARDNAME, 
		en.enumdicname MARKET,
		en1.enumdicname BUYORSELL,
		case t.profitroletype when 3 then '一级' else '二级' end PROFITROLETYPE,
		t.PROFITAMOUNT
	from THJ_Reckon_ProfitRecode t
	left join wrstandard wr on t.wrstandardid = wr.wrstandardid
	left join enumdicitem en on t.marketid = en.enumitemname and en.enumdiccode='THJMarket'
	left join enumdicitem en1 on t.buyorsell = en1.enumitemname and en1.enumdiccode='buyOrSell'
	where substr(t.tradedate,0,6) = '%v' and t.userid = %v and t.profitroletype in (3,4)
	order by t.tradetime desc
`
	sqlId.FormatParam(r.MOUTH, r.USERID)

	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

func (r *PromotionIncomeDetail) GetDataByPage() (interface{}, error, int, int, int) {
	sData := make([]PromotionIncomeDetail, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	total := 0
	for i := range sData {
		sData[i].calc()
		total = sData[i].Total
	}
	return sData, err, r.Page, r.PageSize, total
}

type THJUserLevelInfo struct {
	LEVELGROUP       string  `json:"levelgroup" xorm:"LEVELGROUP"`         // 当前等级
	NEXTLEVELGROUPID string  `json:"nextlevelgroup" xorm:"NEXTLEVELGROUP"` // 下一等级
	CheckDay         string  `json:"checkday" xorm:"-"`                    // 最近一次考核日
	AppUp            int     `json:"appup"`                                // 晋升已累计的数量
	NotYet           int     `json:"notyet"`                               // 晋升还差多少数量
	Progress         float64 `json:"progress"`                             // 进度

	USERID int64 `json:"-" xorm:"-" form:"userid" binding:"required"` // 用户ID
}

func (r *THJUserLevelInfo) Get() (err error) {
	type tmpStruct struct {
		USERID            int64   `json:"userid" xorm:"USERID"`                       // 用户ID
		LEVELGROUPID      int32   `json:"levelgroupid" xorm:"LEVELGROUPID"`           // 交易商级别分组(THJ)
		BUYTOTALQTY       int64   `json:"buytotalqty" xorm:"BUYTOTALQTY"`             // 一级买总量（采购市场）(THJ)
		BUYTOTALQTY2      int64   `json:"buytotalqty2" xorm:"BUYTOTALQTY2"`           // 一级买总量（供求市场）(THJ)
		BROKERPROFITRATE  float64 `json:"brokerprofitrate" xorm:"BROKERPROFITRATE"`   // 会员采购分出比例(THJ)
		BROKERPROFITRATE2 float64 `json:"brokerprofitrate2" xorm:"BROKERPROFITRATE2"` // 会员供求分出比例(THJ)
		BROKERPROFITRATE3 float64 `json:"brokerprofitrate3" xorm:"BROKERPROFITRATE3"` // 会员转让分出比例(THJ)
		BUYTOTALQTY3      int64   `json:"buytotalqty3" xorm:"BUYTOTALQTY3"`           // 一级买总量（合同转让）(THJ)

		GROUPNAME  string `json:"groupname" xorm:"GROUPNAME"`   // 分级名称
		GROUPNAME2 string `json:"groupname2" xorm:"GROUPNAME2"` // 下一级分级名称
		UPBUYQTY   int32  `json:"upbuyqty" xorm:"UPBUYQTY"`     // 晋级一级采购总量
	}
	t := tmpStruct{}
	sql := fmt.Sprintf(`
	select 
		i.GROUPNAME, 
		i2.groupname GROUPNAME2,
		nvl(i2.UPBUYQTY, -1) UPBUYQTY,
		t.* 
	from thj_useraccount t 
	left join thj_investorlevelgroup i on i.levelgroupid = t.levelgroupid
	left join thj_investorlevelgroup i2 on i2.grouplevel = (i.grouplevel + 1)
	where t.userid = %v`, r.USERID)
	_, err = db.GetEngine().SQL(sql).Get(&t)
	if err != nil {
		return
	}
	r.LEVELGROUP = t.GROUPNAME
	r.NEXTLEVELGROUPID = t.GROUPNAME2
	r.AppUp = int(t.BUYTOTALQTY) + int(t.BUYTOTALQTY2) + int(t.BUYTOTALQTY3)
	if t.UPBUYQTY > 0 {
		r.NotYet = int(t.UPBUYQTY) - r.AppUp
	}

	r.Progress = float64(r.AppUp) / float64(t.UPBUYQTY)

	dates := make([]string, 0)
	sql = `
	select aa.d from 
	(select to_char(to_date(extract(year from sysdate) || t.checkday, 'yyyymmdd'),'yyyy-mm-dd') d,
		(to_date(extract(year from sysdate) || t.checkday,'yyyymmdd') - sysdate) a
	from thj_investorcheckday t
	union
	select to_char(to_date(extract(year from sysdate) + 1 || t.checkday, 'yyyymmdd'),'yyyy-mm-dd') d,
		(to_date(extract(year from sysdate)+1 || t.checkday,'yyyymmdd') - sysdate) a
	from thj_investorcheckday t) aa where aa.a >= 0 order by aa.a
	`
	err = db.GetEngine().SQL(sql).Find(&dates)
	if err != nil {
		return
	}
	if len(dates) > 0 {
		r.CheckDay = dates[0]
	}

	return
}

// Spotgoodsprice 现货市价
type Spotgoodsprice struct {
	WRSTANDARDID        int64            `json:"wrstandardid" xorm:"WRSTANDARDID" form:"wrstandardid"` // 现货商品ID(通用则为0)
	WRSTANDARDCODE      string           `json:"wrstandardcode" xorm:"WRSTANDARDCODE"`                 // 现货商品代码
	WRSTANDARDNAME      string           `json:"wrstandardname" xorm:"WRSTANDARDNAME"`                 // 现货商品名称
	SPOTGOODSPRICE      float64          `json:"spotgoodsprice" xorm:"SPOTGOODSPRICE"`                 // 当前价格
	PRESPOTGOODSPRICE   float64          `json:"prespotgoodsprice" xorm:"PRESPOTGOODSPRICE"`           // 上日价格
	TRADEDATE           string           `json:"tradedate" xorm:"TRADEDATE"`                           // 交易日(yyyyMMdd)
	SPOTGOODSQTY        float64          `json:"spotgoodsqty" xorm:"SPOTGOODSQTY"`                     // 招标量
	PRICEFLAG           int              `json:"priceflag" xorm:"PRICEFLAG"`                           // 点价标识(1:为可点价-本月招标价已发布，否则为0)
	YSTSPOTGOODSPRICE   float64          `json:"ystspotgoodsprice" xorm:"YSTSPOTGOODSPRICE"`           // 昨结价格
	TODAYSPOTGOODSPRICE float64          `json:"todayspotgoodsprice" xorm:"TODAYSPOTGOODSPRICE"`       // 今开价格
	DIFFPRICE           float64          `json:"diffprice" xorm:"DIFFPRICE"`                           // 差价
	TRADEDATE2          string           `json:"tradedate2" xorm:"TRADEDATE2"`                         // 交易日-期货(yyyyMMdd)
	PageEx              `xorm:"extends"` // 页码信息
}

func (r *Spotgoodsprice) calc() {

}

func (r *Spotgoodsprice) buildSql() string {
	var sqlId utils.SQLVal = `
	select 
		wr.WRSTANDARDID,
		wr.WRSTANDARDCODE,
		wr.wrstandardname, 
		t.prespotgoodsprice, 
		t.spotgoodsprice,
		t.spotgoodsqty,
		to_char(to_date(t.TRADEDATE, 'yyyymmdd'), 'yyyy-mm-dd') TRADEDATE,
		t.ystspotgoodsprice,
    	t.todayspotgoodsprice,
    	t.diffprice,
		t.tradedate2,
		case
        	when t.diffprice > 0 and t.tradedate2 in (select t.tradedate from marketrun t where t.marketid=0) then 1
         	else 0 end priceflag
  	from ERMCP_SpotGoodsPrice t
	left join wrstandard wr
    	on t.wrstandardid = wr.wrstandardid
	where 1=1
`
	sqlId.AndEx("t.WRSTANDARDID", r.WRSTANDARDID, r.WRSTANDARDID != 0)
	sqlId.OrderBy("wr.wrstandardname")

	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

func (r *Spotgoodsprice) GetDataByPage() (interface{}, error, int, int, int) {
	sData := make([]Spotgoodsprice, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	total := 0
	for i := range sData {
		sData[i].calc()
		total = sData[i].Total
	}
	return sData, err, r.Page, r.PageSize, total
}

// Thjspotquoteconfig THJ现货行情配置表
type Thjspotquoteconfig struct {
	AUTOID     int64  `json:"autoid" xorm:"AUTOID"`                     // 自增ID(SEQ_THJ_SPOTQUOTECONFIG)
	SPOTSRC    string `json:"spotsrc" xorm:"SPOTSRC"`                   // 现货来源
	SPOTNAME   string `json:"spotname" xorm:"SPOTNAME" form:"spotname"` // 现货名称
	SPOTPRICE  string `json:"spotprice" xorm:"SPOTPRICE"`               // 现货招标价
	SPOTQTY    string `json:"spotqty" xorm:"SPOTQTY"`                   // 现货采购量
	SPOTMONTH  string `json:"spotmonth" xorm:"SPOTMONTH"`               // 现货月份(yyyy-MM)
	RELATEDID  int64  `json:"relatedid" xorm:"RELATEDID"`               // 关联新闻ID(Site_ColumnDetail)
	UPDATETIME string `json:"updatetime" xorm:"UPDATETIME"`             // 更新时间
}

func (r *Thjspotquoteconfig) calc() {

}

func (r *Thjspotquoteconfig) buildSql() string {
	var sqlId utils.SQLVal = `
	select 
		t.AUTOID,
		t.SPOTSRC,
		t.SPOTNAME,
		t.SPOTPRICE,
		t.SPOTQTY,
		t.SPOTMONTH,
		t.RELATEDID,
		to_char(t.updatetime, 'yyyy-MM-dd hh24:mi:ss') UPDATETIME
	from THJ_SPOTQUOTECONFIG t
	where 1 = 1  
	`

	sqlId.AndEx("t.SPOTNAME", r.SPOTNAME, r.SPOTNAME != "")
	sqlId.OrderBy("t.SPOTMONTH desc, t.SPOTNAME")

	return sqlId.String()
}

// GetDataEx 从数据库中查询数据
func (r *Thjspotquoteconfig) GetDataEx() (interface{}, error) {
	e := db.GetEngine()
	s := e.SQL(r.buildSql())
	sData := make([]Thjspotquoteconfig, 0)
	if err := s.Find(&sData); err != nil {
		return nil, err
	}
	for i := range sData {
		sData[i].calc()
	}
	return sData, nil
}

type ThjSpotQuote struct {
	SPOTNAME string `json:"spotname" xorm:"SPOTNAME"` // 现货名称
}

func (r *ThjSpotQuote) calc() {

}

func (r *ThjSpotQuote) buildSql() string {
	var sqlId utils.SQLVal = `
	select distinct
		t.SPOTNAME
	from THJ_SPOTQUOTECONFIG t
	`

	return sqlId.String()
}

// GetDataEx 从数据库中查询数据
func (r *ThjSpotQuote) GetDataEx() (interface{}, error) {
	e := db.GetEngine()
	s := e.SQL(r.buildSql())
	sData := make([]ThjSpotQuote, 0)
	if err := s.Find(&sData); err != nil {
		return nil, err
	}
	for i := range sData {
		sData[i].calc()
	}
	return sData, nil
}

func (r *Thjinvestorlevelgroup) calc() {

}

func (r *Thjinvestorlevelgroup) buildSql() string {
	var sqlId utils.SQLVal = `
	select t.* from thj_investorlevelgroup t order by t.grouplevel
	`

	return sqlId.String()
}

// GetDataEx 从数据库中查询数据
func (r *Thjinvestorlevelgroup) GetDataEx() (interface{}, error) {
	e := db.GetEngine()
	s := e.SQL(r.buildSql())
	sData := make([]Thjinvestorlevelgroup, 0)
	if err := s.Find(&sData); err != nil {
		return nil, err
	}
	for i := range sData {
		sData[i].calc()
	}
	return sData, nil
}

type THJFriend struct {
	FRIENDUSERID string `json:"frienduserid" xorm:"FRIENDUSERID" form:"frienduserid"` // 用户UserID
	CUSTOMERNAME string `json:"customername" xorm:"CUSTOMERNAME" form:"customername"` // 客户名称(企业名称)，模糊查询
	MOBILE       string `json:"mobile" xorm:"MOBILE" form:"mobile"`                   // 手机号码(加密存储)，加密串精确查询
	CREATETIME   string `json:"createtime" xorm:"CREATETIME"`                         // 创建时间

	GROUPNAME    string `json:"groupname" xorm:"GROUPNAME"`       // 分级名称
	ACCOUTSTATUS string `json:"accoutstatus" xorm:"ACCOUTSTATUS"` // 状态

	HASAUTH int32 `json:"hasauth" xorm:"HASAUTH"` // 是否已实名认证 - 0:未认证 1:已认证 2:已提交(待审核) 3:已拒绝

	USERID       int64 `json:"-" xorm:"-" form:"userid" binding:"required"` // 用户ID
	LEVELGROUPID int64 `json:"-" xorm:"-" form:"levelgroupid"`              // 等级ID

	PageEx `xorm:"extends"` // 页码信息
}

func (r *THJFriend) calc() {

}

func (r *THJFriend) buildSql() string {
	var sqlId utils.SQLVal = `
	select 
	    t.USERID FRIENDUSERID,
		t.CUSTOMERNAME,
		t.MOBILE,
		g.GROUPNAME,
		ed.enumdicname ACCOUTSTATUS,
		ua.HASAUTH,
		t.CREATETIME
	from userinfo t
	inner join useraccount ua on t.userid = ua.userid
	left join THJ_UserAccount thjua on t.userid = thjua.userid
	left join thj_investorlevelgroup g on thjua.levelgroupid = g.levelgroupid
	left join enumdicitem ed on ed.enumdiccode = 'accountstatus' and ua.accountstatus = ed.enumitemname
	where ua.refereeuserid = %v
	`
	sqlId.FormatParam(r.USERID)
	sqlId.AndLike("t.customername", r.CUSTOMERNAME)
	sqlId.AndEx("t.mobile", r.MOBILE, r.MOBILE != "")
	sqlId.AndEx("g.levelgroupid", r.LEVELGROUPID, r.LEVELGROUPID != 0)

	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

func (r *THJFriend) GetDataByPage() (interface{}, error, int, int, int) {
	sData := make([]THJFriend, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	total := 0
	for i := range sData {
		sData[i].calc()
		total = sData[i].Total
	}
	return sData, err, r.Page, r.PageSize, total
}

type THJProfit struct {
	Marketname            string  `json:"marketname" xorm:"marketname"`                       // 市场
	Firend                string  `json:"firend" xorm:"firend"`                               // 下单好友
	Buyorselldisplay      string  `json:"buyorselldisplay" xorm:"buyorselldisplay"`           // 方向
	Wrstandardname        string  `json:"wrstandardname" xorm:"wrstandardname"`               // 商品
	Tradeqty              int     `json:"tradeqty" xorm:"tradeqty"`                           // 成交数量
	Chargeamount          float64 `json:"chargeamount" xorm:"chargeamount"`                   // 手续费总额
	Profitamount          float64 `json:"profitamount" xorm:"profitamount"`                   // 分润金额
	Profitroletypedisplay string  `json:"profitroletypedisplay" xorm:"profitroletypedisplay"` // 分润角色
	Brokerrate            string  `json:"brokerrate" xorm:"brokerrate"`                       // 会员比例
	Brokerprofitrate      string  `json:"brokerprofitrate" xorm:"brokerprofitrate"`           // 会员释出比例
	Levelonevalue         string  `json:"levelonevalue" xorm:"levelonevalue"`                 // 一级比例
	Leveltwovalue         string  `json:"leveltwovalue" xorm:"leveltwovalue"`                 // 二级比例
	Tradeid               string  `json:"tradeid" xorm:"tradeid"`                             // 成交单号
	Tradetimedisplay      string  `json:"tradetimedisplay" xorm:"tradetimedisplay"`           // 成交时间

	USERID         int64  `json:"-" xorm:"-" form:"userid" binding:"required"` // 用户ID
	MarketID       int    `json:"-" xorm:"-" form:"marketid"`                  // 市场ID
	Accountname    string `json:"-" xorm:"-" form:"accountname"`               // 好友名称，模糊查询
	Goods          string `json:"-" xorm:"-" form:"goods"`                     // 商品代码或商品名称，模糊查询
	PROFITROLETYPE int32  `json:"-" xorm:"-" form:"profitroletype"`            // 分润机构角色 - 1:平台 2:经纪会员 3:一级交易商 4:二级交易商(枚举：THJProfitRoleType)

	PageEx `xorm:"extends"` // 页码信息
}

func (r *THJProfit) calc() {

}

func (r *THJProfit) buildSql() string {
	var sqlId utils.SQLVal = `
	select 
		m1.enumdicname "marketname",

       ua2.accountname "firend",

       m2.enumdicname "buyorselldisplay",

       wr.wrstandardcode || '/' || wr.wrstandardname "wrstandardname",

       htd.tradeqty "tradeqty",

       t.chargeamount "chargeamount",

       t.profitamount "profitamount",

       m4.enumdicname "profitroletypedisplay",

       to_char(t.brokerrate * 100, 'fm999999990.00') || '%' "brokerrate",

       to_char(t.brokerprofitrate * 100, 'fm999999990.00') || '%' "brokerprofitrate",

       case t.levelonealgorithm

         when 0 then

          '-'

         when 1 then

          '比例/' || to_char(to_char(t.levelonevalue * 100, 'fm999999990.00')) || ' %'

         else

          '固定/' || to_char(t.levelonevalue, 'fm999999990.00')

       end "levelonevalue",

       case t.leveltwoalgorithm

         when 0 then

          '-'

         when 1 then

          '比例/' || to_char(to_char(t.leveltwovalue * 100, 'fm999999990.00')) || ' %'

         else

          '固定/' || to_char(t.leveltwovalue, 'fm999999990.00')

       end "leveltwovalue",

       to_char(t.tradetime, 'yyyy-MM-dd hh24:mi:ss') "tradetimedisplay",

       to_char(t.tradeid) "tradeid"

  from THJ_Reckon_ProfitRecode t

  left join enumdicitem m1

    on m1.enumdiccode = 'THJMarket'

   and t.marketid = m1.enumitemname

  left join enumdicitem m2

    on m2.enumdiccode = 'buyOrSell'

   and t.buyorsell = m2.enumitemname

  left join enumdicitem m4

    on m4.enumdiccode = 'THJProfitRoleType'

   and t.profitroletype = m4.enumitemname

  left join wrstandard wr

    on t.wrstandardid = wr.wrstandardid

  left join useraccount ua

    on ua.userid = t.userid

  left join (select a.transferid  tradeid,

                    1             buyorsell,

                    a.selluserid  userid,

                    a.transferqty tradeqty

               from THJ_PurchaseTransfer a

             union

             select a2.wrtradedetailid tradeid,

                    a2.buyorsell       buyorsell,

                    ta2.userid         userid,

                    a2.tradeqty        tradeqty

               from his_wrtrade_tradedetail a2

               left join taaccount ta2

                 on a2.accountid = ta2.accountid

              where a2.isvaliddata = 1) htd

    on t.tradeid = htd.tradeid

   and t.buyorsell = htd.buyorsell

  left join useraccount ua2

    on ua2.userid = htd.userid

 where 1=1 
 `

	// sqlId.FormatParam(r.USERID)  // 发生错误 %!v(MISSING)
	// sqlId = utils.SQLVal(fmt.Sprintf(string(sqlId), r.USERID))
	sqlId.AndEx("t.userid", r.USERID, r.USERID != 0)
	sqlId.AndEx("t.marketid", r.MarketID, r.MarketID != 0)
	sqlId.AndLike("ua2.accountname", r.Accountname)
	sqlId.AndLikes2("wr.wrstandardcode", "wr.wrstandardname", r.Goods)
	sqlId.AndEx(" t.profitroletype", r.PROFITROLETYPE, r.PROFITROLETYPE != 0)
	sqlId.OrderByDesc("t.tradetime")

	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

func (r *THJProfit) GetDataByPage() (interface{}, error, int, int, int) {
	sData := make([]THJProfit, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	total := 0
	for i := range sData {
		sData[i].calc()
		total = sData[i].Total
	}
	return sData, err, r.Page, r.PageSize, total
}

// 定金比例个性化查询
type GThjinvesotrdeposit struct {
	USERID      int64   `json:"userid" xorm:"USERID" form:"userid" binding:"required"` // 用户ID
	DEPOSITRATE float64 `json:"depositrate" xorm:"DEPOSITRATE"`                        // 定金比例
	DEPOSITQTY  int64   `json:"-" xorm:"DEPOSITQTY"`                                   // 可用量

	QTY int64 `json:"qty" xorm:"QTY"` // 可用量

	PRESALEAPPLYID string `json:"-" xorm:"-" form:"presaleapplyid" binding:"required"` // 预售申请ID(184+Unix秒时间戳(10位)+xxxxxx)

	PageEx `xorm:"extends"` // 页码信息
}

func (r *GThjinvesotrdeposit) calc() {

}

func (r *GThjinvesotrdeposit) buildSql() string {
	var sqlId utils.SQLVal = `
		select 
			t.DEPOSITRATE, 
			sum(t.depositqty) QTY 
		from THJ_InvesotrDeposit t
		where (
			t.userid = %v or 
			t.userid in (select t.refereeuserid from useraccount t where t.userid=%v))
			AND t.depositrate not in (select distinct t.depositrate from THJ_PresaleApplyDeposit t where t.presaleapplyid = %v
		) and t.depositqty >0
		group by t.depositrate
		order by t.depositrate
	`
	sqlId.FormatParam(r.USERID, r.USERID, r.PRESALEAPPLYID)

	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

func (r *GThjinvesotrdeposit) GetDataByPage() (interface{}, error, int, int, int) {
	sData := make([]GThjinvesotrdeposit, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	total := 0
	for i := range sData {
		sData[i].calc()
		total = sData[i].Total
	}
	return sData, err, r.Page, r.PageSize, total
}

type QueryMyDepositReq struct {
	UserId int64 `form:"userid" binding:"required"` // 用户ID
}

func (r *Thjinvesotrdeposit) GetAll(req QueryMyDepositReq) (rsp []Thjinvesotrdeposit, err error) {
	rsp = make([]Thjinvesotrdeposit, 0)
	err = db.GetEngine().Where("userid = ?", req.UserId).Find(&rsp)

	return
}

// 比例变更流水
type GThjinvesotrdepositlog struct {
	LOGID           string  `json:"logid" xorm:"LOGID"`                                                   // 流水ID(336+Unix秒时间戳(10位)+xxxxxx)
	USERID          int64   `json:"userid" xorm:"USERID" form:"userid" binding:"required"`                // 用户ID
	DEPOSITRATE     float64 `json:"depositrate" xorm:"DEPOSITRATE" form:"depositrate" binding:"required"` // 定金比例（选中比例）
	ORIDEPOSITQTY   int64   `json:"oridepositqty" xorm:"ORIDEPOSITQTY"`                                   // 可用量(变更前)
	CHANGEQTY       int64   `json:"changeqty" xorm:"CHANGEQTY"`                                           // 变更量
	CURDEPOSITQTY   string  `json:"curdepositqty" xorm:"CURDEPOSITQTY"`                                   // 可用量(变更后)
	BUYUSERID       int64   `json:"buyuserid" xorm:"BUYUSERID"`                                           // 采购用户ID
	WRTRADEDETAILID int64   `json:"wrtradedetailid" xorm:"WRTRADEDETAILID"`                               // 采购成交单ID
	UPDATORID       int64   `json:"updatorid" xorm:"UPDATORID"`                                           // 更新人
	UPDATETIME      string  `json:"updatetime" xorm:"UPDATETIME"`                                         // 更新时间

	Accountname string `json:"accountname" xorm:"ACCOUNTNAME"` // 用户名

	PageEx `xorm:"extends"` // 页码信息
}

func (r *GThjinvesotrdepositlog) calc() {

}

func (r *GThjinvesotrdepositlog) buildSql() string {
	var sqlId utils.SQLVal = `
	select 
		t.UPDATETIME,
		t.ORIDEPOSITQTY, 
		t.CHANGEQTY, 
		t.CURDEPOSITQTY,
		ua.ACCOUNTNAME, 
		t.WRTRADEDETAILID 
	from THJ_InvesotrDepositLog t
	left join useraccount ua on t.buyuserid = ua.userid
	where t.userid = %v and t.depositrate = %v 
	order by t.logid desc
	`
	sqlId.FormatParam(r.USERID, r.DEPOSITRATE)

	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

func (r *GThjinvesotrdepositlog) GetDataByPage() (interface{}, error, int, int, int) {
	sData := make([]GThjinvesotrdepositlog, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	total := 0
	for i := range sData {
		sData[i].calc()
		total = sData[i].Total
	}
	return sData, err, r.Page, r.PageSize, total
}