MTP20_IF/models/qhjAgreement.go

/**
* @Author: zou.yingbin
* @Create  : 2021/6/30 9:31
* @Modify  : 2021/6/30 9:31
 */

package models

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

// QhjAgreementConfig 协议配置
type QhjAgreementConfig struct {
	AGREEMENTID        int64  `json:"agreementid"  xorm:"AGREEMENTID" form:"agreementid"`                      // 协议ID(SEQ_QHJ_AGREEMENTCONFIG)
	AGREEMENTVERSION   int32  `json:"agreementversion"  xorm:"AGREEMENTVERSION" form:"agreementversion"`       // 协议版本号(从1开始每次修改加1)
	AGREEMENTNO        string `json:"agreementno"  xorm:"AGREEMENTNO" form:"agreementno"`                      // 协议编号
	AGREEMENTNAME      string `json:"agreementname"  xorm:"AGREEMENTNAME" form:"agreementname"`                // 协议名称
	AGREEMENTTYPE      int32  `json:"agreementtype"  xorm:"AGREEMENTTYPE" form:"agreementtype"`                // 协议类型 - 1:注册 2:登录 3:交易
	AGREEMENTFREQUENCY int32  `json:"agreementfrequency"  xorm:"AGREEMENTFREQUENCY" form:"agreementfrequency"` // 协议频率 - 1:首次 2:每次 [登录为2， 其它为1]
	AGREEMENTCONTENT   string `json:"agreementcontent"  xorm:"AGREEMENTCONTENT" form:"agreementcontent"`       // 协议内容
	ISVALID            int32  `json:"isvalid"  xorm:"ISVALID" form:"isvalid"`                                  // 是否有效 - 是否有效 - 1:正常 2:停用 3:注销
	CREATORSRC         int32  `json:"creatorsrc"  xorm:"CREATORSRC" form:"creatorsrc"`                         // 创建来源 - 1:管理端 2:终端
	CREATORID          int64  `json:"creatorid"  xorm:"CREATORID" form:"creatorid"`                            // 创建人
	CREATETIME         string `json:"createtime"  xorm:"CREATETIME" form:"createtime"`                         // 创建时间
	REVISERSRC         int32  `json:"revisersrc"  xorm:"REVISERSRC" form:"revisersrc"`                         // 修改来源 - 1:管理端 2:终端
	REVISERID          int64  `json:"reviserid"  xorm:"REVISERID" form:"reviserid"`                            // 修改人
	UPDATETIME         string `json:"updatetime"  xorm:"UPDATETIME" form:"updatetime"`                         // 更新时间
}

func (r *QhjAgreementConfig) calc() {

}

func (r *QhjAgreementConfig) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.AGREEMENTID," +
		"       t.AGREEMENTVERSION," +
		"       t.AGREEMENTNO," +
		"       t.AGREEMENTNAME," +
		"       t.AGREEMENTTYPE," +
		"       t.AGREEMENTFREQUENCY," +
		"       t.AGREEMENTCONTENT," +
		"       t.ISVALID," +
		"       t.CREATORSRC," +
		"       t.CREATORID," +
		"       to_char(t.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') CREATETIME," +
		"       t.REVISERSRC," +
		"       t.REVISERID," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME" +
		"  FROM QHJ_AgreementConfig t" +
		" WHERE 1 = 1"
	sqlId.AndEx("t.agreementid", r.AGREEMENTID, r.AGREEMENTID > 0)
	sqlId.AndEx("t.agreementversion", r.AGREEMENTVERSION, r.AGREEMENTVERSION > 0)
	sqlId.AndEx("t.agreementtype", r.AGREEMENTTYPE, r.AGREEMENTTYPE > 0)
	sqlId.AndEx("t.agreementfrequency", r.AGREEMENTFREQUENCY, r.AGREEMENTFREQUENCY > 0)
	sqlId.AndEx("t.isvalid", r.ISVALID, r.ISVALID > 0)
	sqlId.AndLike("t.agreementno", r.AGREEMENTNO)
	sqlId.AndLike("t.agreementname", r.AGREEMENTNAME)
	return sqlId.String()
}

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

// QhjAgreementChangeLog 协议配置变更流水
type QhjAgreementChangeLog struct {
	LOGID              int64  `json:"logid"  xorm:"LOGID" form:"logid"`                                        // 流水ID(SEQ_QHJ_AGREEMENTCHANGELOG)
	AGREEMENTID        int64  `json:"agreementid"  xorm:"AGREEMENTID" form:"agreementid"`                      // 协议ID
	AGREEMENTVERSION   int32  `json:"agreementversion"  xorm:"AGREEMENTVERSION" form:"agreementversion"`       // 协议版本号
	AGREEMENTNO        string `json:"agreementno"  xorm:"AGREEMENTNO" form:"agreementno"`                      // 协议编号
	AGREEMENTNAME      string `json:"agreementname"  xorm:"AGREEMENTNAME" form:"agreementname"`                // 协议名称
	AGREEMENTTYPE      int32  `json:"agreementtype"  xorm:"AGREEMENTTYPE" form:"agreementtype"`                // 协议类型 - 1:注册 2:登录 3:交易
	AGREEMENTFREQUENCY int32  `json:"agreementfrequency"  xorm:"AGREEMENTFREQUENCY" form:"agreementfrequency"` // 协议频率 - 1:首次 2:每次 [登录为2， 其它为1]
	AGREEMENTCONTENT   string `json:"agreementcontent"  xorm:"AGREEMENTCONTENT" form:"agreementcontent"`       // 协议内容
	REVISERSRC         int32  `json:"revisersrc"  xorm:"REVISERSRC" form:"revisersrc"`                         // 修改来源 - 1:管理端 2:终端
	REVISERID          int64  `json:"reviserid"  xorm:"REVISERID" form:"reviserid"`                            // 修改人
	UPDATETIME         string `json:"updatetime"  xorm:"UPDATETIME" form:"updatetime"`                         // 更新时间
}

func (r *QhjAgreementChangeLog) calc() {

}

func (r *QhjAgreementChangeLog) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.LOGID," +
		"       t.AGREEMENTID," +
		"       t.AGREEMENTVERSION," +
		"       t.AGREEMENTNO," +
		"       t.AGREEMENTNAME," +
		"       t.AGREEMENTTYPE," +
		"       t.AGREEMENTFREQUENCY," +
		"       t.AGREEMENTCONTENT," +
		"       t.REVISERSRC," +
		"       t.REVISERID," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME" +
		"  FROM QHJ_AGREEMENTCHANGELOG t" +
		" WHERE 1 = 1"
	sqlId.AndEx("t.logid", r.LOGID, r.LOGID > 0)
	sqlId.AndEx("t.agreementid", r.AGREEMENTID, r.AGREEMENTID > 0)
	sqlId.AndEx("t.agreementversion", r.AGREEMENTVERSION, r.AGREEMENTVERSION > 0)
	sqlId.AndEx("t.agreementtype", r.AGREEMENTTYPE, r.AGREEMENTTYPE > 0)
	sqlId.AndLike("t.agreementno", r.AGREEMENTNO)
	sqlId.AndLike("t.agreementname", r.AGREEMENTNAME)

	return sqlId.String()
}

// GetDataEx 获取协议配置变更流水
func (r *QhjAgreementChangeLog) GetDataEx() (interface{}, error) {
	sData := make([]QhjAgreementChangeLog, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// QhjNodeAgreementConfig 协议节点配置
type QhjNodeAgreementConfig struct {
	AGREEMENTTYPE    int32  `json:"agreementtype"  xorm:"AGREEMENTTYPE" form:"agreementtype"`          // 协议类型 - 1:注册 2:登录 3:交易
	AGREEMENTVERSION int32  `json:"agreementversion"  xorm:"AGREEMENTVERSION" form:"agreementversion"` // 协议版本号
	NODETYPE         int32  `json:"nodetype"  xorm:"NODETYPE" form:"nodetype"`                         // 环节类型 - 1:注册  2:登录 3:购买 4:融资购买 5:定投 6:卖出
	CONFIGID         int64  `json:"configid"  xorm:"CONFIGID" form:"configid"`                         // 配置(SEQ_QHJ_NODEAGREEMENTCONFIG)
	AGREEMENTID      int64  `json:"agreementid"  xorm:"AGREEMENTID" form:"agreementid"`                // 协议ID
	CREATETIME       string `json:"createtime"  xorm:"CREATETIME" form:"createtime"`                   // 创建时间
	GOODSID          int32  `json:"goodsid"  xorm:"GOODSID" form:"goodsid"`                            // 商品ID [默认为0, NodeType>=3 的为商品值]
	AGREEMENTNO      string `json:"agreementno"  xorm:"AGREEMENTNO" form:"agreementno"`                // 协议编号
	AGREEMENTNAME    string `json:"agreementname"  xorm:"AGREEMENTNAME" form:"agreementname"`          // 协议名称
	AGREEMENTCONTENT string `json:"agreementcontent"  xorm:"AGREEMENTCONTENT" form:"agreementcontent"` // 协议内容
}

func (r *QhjNodeAgreementConfig) calc() {

}

func (r *QhjNodeAgreementConfig) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.CONFIGID," +
		"       t.NODETYPE," +
		"       t.GOODSID," +
		"       t.AGREEMENTID," +
		"       t.AGREEMENTTYPE," +
		"       t.AGREEMENTVERSION," +
		"       to_char(t.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') CREATETIME," +
		"       c.agreementno," +
		"       c.agreementname," +
		"       c.agreementcontent" +
		"  FROM QHJ_NODEAGREEMENTCONFIG t" +
		"  LEFT JOIN QHJ_AGREEMENTCONFIG c on t.agreementid=c.agreementid" +
		" WHERE 1 = 1 and c.isvalid = 1"
	sqlId.AndEx("t.configid", r.CONFIGID, r.CONFIGID > 0)
	sqlId.AndEx("t.nodetype", r.NODETYPE, r.NODETYPE > 0)
	sqlId.AndEx("t.goodsid", r.GOODSID, r.GOODSID > 0)
	sqlId.AndEx("t.agreementid", r.AGREEMENTID, r.AGREEMENTID > 0)
	sqlId.AndEx("t.agreementtype", r.AGREEMENTTYPE, r.AGREEMENTTYPE > 0)
	sqlId.AndEx("t.agreementversion", r.AGREEMENTVERSION, r.AGREEMENTVERSION > 0)
	sqlId.AndLike("c.agreementno", r.AGREEMENTNO)
	sqlId.AndLike("c.agreementname", r.AGREEMENTNAME)
	return sqlId.String()
}

// GetDataEx 获取协议节点配置
func (r *QhjNodeAgreementConfig) GetDataEx() (interface{}, error) {
	sData := make([]QhjNodeAgreementConfig, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// QhjCustomerSignStatus 协议签约状态
type QhjCustomerSignStatus struct {
	NODETYPE      int32  `json:"nodetype"  xorm:"NODETYPE" form:"nodetype"`                // 环节类型 - 1:注册  2:登录 3:购买 4:融资购买 5:定投 6:卖出  99:首次交易
	USERID        int64  `json:"userid"  xorm:"USERID" form:"userid"`                      // 客户ID
	CREATETIME    string `json:"createtime"  xorm:"CREATETIME" form:"createtime"`          // 创建时间
	GOODSID       int32  `json:"goodsid"  xorm:"GOODSID" form:"goodsid"`                   // 商品ID [默认为0, NodeType>=3 的为商品值]
	SIGNTIMESTAMP string `json:"signtimestamp"  xorm:"SIGNTIMESTAMP" form:"signtimestamp"` // 签约状态时间戳
	TRADEDATE     string `json:"tradedate"  xorm:"TRADEDATE" form:"tradedate"`             // 创建交易日(yyyyMMdd)
	UPDATETIME    string `json:"updatetime"  xorm:"UPDATETIME" form:"updatetime"`          // 更新时间

	USERNAME  string `json:"username"  xorm:"USERNAME" form:"USERNAME"`    // 客户名称
	GOODSNAME string `json:"goodsname"  xorm:"GOODSNAME" form:"GOODSNAME"` // 商品名称
	GOODSCODE string `json:"goodscode"  xorm:"GOODSCODE" form:"GOODSCODE"` // 商品代码
}

func (r *QhjCustomerSignStatus) calc() {

}

func (r *QhjCustomerSignStatus) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.USERID," +
		"       t.NODETYPE," +
		"       t.GOODSID," +
		"       to_char(t.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') CREATETIME," +
		"       t.TRADEDATE," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       t.SIGNTIMESTAMP," +
		"       u.accountname username," +
		"       g.goodscode," +
		"       g.goodsname" +
		"  FROM QHJ_CUSTOMERSIGNSTATUS t" +
		"  LEFT JOIN USERACCOUNT u on t.userid=u.userid" +
		"  LEFT JOIN GOODS g on t.goodsid=g.goodsid" +
		" WHERE 1 = 1"
	sqlId.AndEx("t.userid", r.USERID, r.USERID > 0)
	sqlId.AndEx("t.nodetype", r.NODETYPE, r.NODETYPE > 0)
	sqlId.AndEx("t.goodsid", r.GOODSID, r.GOODSID > 0)
	sqlId.AndEx("t.tradedate", r.TRADEDATE, r.TRADEDATE != "")
	sqlId.AndLike("u.accountname", r.USERNAME)
	return sqlId.String()
}

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

// QhjCustomerSignLog 客户协议签约流水
type QhjCustomerSignLog struct {
	AGREEMENTVERSION int32  `json:"agreementversion"  xorm:"AGREEMENTVERSION" form:"agreementversion"` // 协议版本号
	NODETYPE         int32  `json:"nodetype"  xorm:"NODETYPE" form:"nodetype"`                         // 环节类型 - 1:注册  2:登录 3:购买 4:融资购买 5:定投 6:卖出
	LOGID            int64  `json:"logid"  xorm:"LOGID" form:"logid"`                                  // 流水ID(SEQ_QHJ_CUSTOMERSIGNLOG)
	USERID           int64  `json:"userid"  xorm:"USERID" form:"userid"`                               // 客户ID
	AGREEMENTID      int64  `json:"agreementid"  xorm:"AGREEMENTID" form:"agreementid"`                // 协议ID
	CREATETIME       string `json:"createtime"  xorm:"CREATETIME" form:"createtime"`                   // 创建时间
	GOODSID          int32  `json:"goodsid"  xorm:"GOODSID" form:"goodsid"`                            // 商品ID [默认为0, NodeType>=3 的为商品值]
	SIGNADRESSURL    string `json:"signadressurl"  xorm:"SIGNADRESSURL" form:"signadressurl"`          // 签约文件地址
	SIGNTIMESTAMP    string `json:"signtimestamp"  xorm:"SIGNTIMESTAMP" form:"signtimestamp"`          // 签约状态时间戳

	USERNAME         string `json:"username"  xorm:"USERNAME" form:"USERNAME"`                         // 客户名称
	GOODSNAME        string `json:"goodsname"  xorm:"GOODSNAME" form:"GOODSNAME"`                      // 商品名称
	GOODSCODE        string `json:"goodscode"  xorm:"GOODSCODE" form:"GOODSCODE"`                      // 商品代码
	AGREEMENTNO      string `json:"agreementno"  xorm:"AGREEMENTNO" form:"agreementno"`                // 协议编号
	AGREEMENTNAME    string `json:"agreementname"  xorm:"AGREEMENTNAME" form:"agreementname"`          // 协议名称
	AGREEMENTTYPE    int32  `json:"agreementtype"  xorm:"AGREEMENTTYPE" form:"agreementtype"`          // 协议类型 - 1:注册 2:登录 3:交易
	AGREEMENTCONTENT string `json:"agreementcontent"  xorm:"AGREEMENTCONTENT" form:"agreementcontent"` // 协议内容
}

func (r *QhjCustomerSignLog) calc() {

}

func (r *QhjCustomerSignLog) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.LOGID," +
		"       t.USERID," +
		"       t.NODETYPE," +
		"       t.GOODSID," +
		"       t.AGREEMENTID," +
		"       t.AGREEMENTVERSION," +
		"       to_char(t.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') CREATETIME," +
		"       t.SIGNTIMESTAMP," +
		"       t.SIGNADRESSURL," +
		"       c.agreementno," +
		"       c.agreementname," +
		"       c.agreementtype," +
		"       c.agreementcontent," +
		"       u.accountname username," +
		"       g.goodscode," +
		"       g.goodsname" +
		"  FROM QHJ_CUSTOMERSIGNLOG t" +
		"  LEFT JOIN QHJ_AGREEMENTCONFIG c on t.agreementid=c.agreementid" +
		"  LEFT JOIN USERACCOUNT u on t.userid=u.userid" +
		"  LEFT JOIN GOODS g on t.goodsid=g.goodsid" +
		" WHERE 1 = 1"
	sqlId.AndEx("t.logid", r.LOGID, r.LOGID > 0)
	sqlId.AndEx("t.userid", r.USERID, r.USERID > 0)
	sqlId.AndEx("t.nodetype", r.NODETYPE, r.NODETYPE > 0)
	sqlId.AndEx("t.goodsid", r.GOODSID, r.GOODSID > 0)
	sqlId.AndEx("t.agreementid", r.AGREEMENTID, r.AGREEMENTID > 0)
	sqlId.AndLike("u.accountname", r.USERNAME)
	sqlId.AndLike("c.agreementno", r.AGREEMENTNO)
	sqlId.AndLike("c.agreementname", r.AGREEMENTNAME)
	return sqlId.String()
}

// GetDataEx 获取客户协议签约流水
func (r *QhjCustomerSignLog) GetDataEx() (interface{}, error) {
	sData := make([]QhjCustomerSignLog, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// QhjUserNodeCfgAndStatus 客户签约节点和状态
type QhjUserNodeCfgAndStatus struct {
	AGREEMENTTYPE    int32  `json:"agreementtype"  xorm:"AGREEMENTTYPE" form:"agreementtype"`          // 协议类型 - 1:注册 2:登录 3:交易
	AGREEMENTVERSION int32  `json:"agreementversion"  xorm:"AGREEMENTVERSION" form:"agreementversion"` // 协议版本号
	NODETYPE         int32  `json:"nodetype"  xorm:"NODETYPE"`                                         // 环节类型 - 1:注册  2:登录 3:购买 4:融资购买 5:定投 6:卖出
	CONFIGID         int64  `json:"configid"  xorm:"CONFIGID" form:"configid"`                         // 配置(SEQ_QHJ_NODEAGREEMENTCONFIG)
	AGREEMENTID      int64  `json:"agreementid"  xorm:"AGREEMENTID" form:"agreementid"`                // 协议ID
	CREATETIME       string `json:"createtime"  xorm:"CREATETIME" form:"createtime"`                   // 创建时间
	GOODSID          int32  `json:"goodsid"  xorm:"GOODSID" form:"goodsid"`                            // 商品ID [默认为0, NodeType>=3 的为商品值]
	AGREEMENTNO      string `json:"agreementno"  xorm:"AGREEMENTNO" form:"agreementno"`                // 协议编号
	AGREEMENTNAME    string `json:"agreementname"  xorm:"AGREEMENTNAME" form:"agreementname"`          // 协议名称
	AGREEMENTCONTENT string `json:"agreementcontent"  xorm:"AGREEMENTCONTENT" form:"agreementcontent"` // 协议内容
	USERID           int64  `json:"userid"  xorm:"USERID" form:"userid"`                               // 客户ID
	SIGNTIMESTAMP    string `json:"signtimestamp"  xorm:"SIGNTIMESTAMP" form:"signtimestamp"`          // 签约状态时间戳(最近一次)
	TRADEDATE        string `json:"tradedate"  xorm:"TRADEDATE" form:"tradedate"`                      // 签约交易日(yyyyMMdd)

	FilterNodeType string `json:"-" form:"nodetype"` // 环节类型筛选条件
}

func (r *QhjUserNodeCfgAndStatus) calc() {

}

func (r *QhjUserNodeCfgAndStatus) buildSql() string {
	var sqlId utils.SQLVal = "with k as" +
		" (select *" +
		"    from (select row_number() over(partition by userid, nodetype order by tradedate desc) cnt," +
		"                 r.*" +
		"            from QHJ_CustomerSignStatus r)" +
		"   where cnt = 1)" +
		"SELECT t.CONFIGID," +
		"       t.NODETYPE," +
		"       t.GOODSID," +
		"       t.AGREEMENTID," +
		"       t.AGREEMENTTYPE," +
		"       t.AGREEMENTVERSION," +
		"       to_char(t.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') CREATETIME," +
		"       c.agreementno," +
		"       c.agreementname," +
		"       c.agreementcontent," +
		"       k.userid," +
		"       k.tradedate," +
		"       k.signtimestamp" +
		"  FROM QHJ_NODEAGREEMENTCONFIG t" +
		"  LEFT JOIN QHJ_AGREEMENTCONFIG c" +
		"    on t.agreementid = c.agreementid" +
		"  LEFT JOIN k on k.nodetype=t.nodetype and t.goodsid=k.goodsid and k.userid=%v" +
		" WHERE 1 = 1 and c.isvalid = 1"
	sqlId.FormatParam(r.USERID)
	sqlId.AndEx("t.configid", r.CONFIGID, r.CONFIGID > 0)
	sqlId.AndEx("t.goodsid", r.GOODSID, r.GOODSID > 0)
	sqlId.AndEx("t.agreementid", r.AGREEMENTID, r.AGREEMENTID > 0)
	sqlId.AndEx("t.agreementtype", r.AGREEMENTTYPE, r.AGREEMENTTYPE > 0)
	sqlId.AndEx("t.agreementversion", r.AGREEMENTVERSION, r.AGREEMENTVERSION > 0)
	sqlId.JoinEx(r.FilterNodeType != "", fmt.Sprintf(" and t.nodetype in(%v)", r.FilterNodeType))
	sqlId.AndLike("c.agreementno", r.AGREEMENTNO)
	sqlId.AndLike("c.agreementname", r.AGREEMENTNAME)
	return sqlId.String()
}

// GetDataEx 获取客户签约节点和状态
func (r *QhjUserNodeCfgAndStatus) GetDataEx() (interface{}, error) {
	sData := make([]QhjUserNodeCfgAndStatus, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}