MTP20_IF/models/ermcpUser.go

/**
* @Author: zou.yingbin
* @Create  : 2021/1/7 17:31
* @Modify  : 2021/1/7 17:31
 */

package models

import (
	"encoding/hex"
	"fmt"
	"mtp2_if/db"
	"mtp2_if/logger"
	"mtp2_if/utils"
)

// ErmcpUserModel 客户资料结构
type ErmcpUserModel struct {
	USERID            int    `json:"userid"  xorm:"'USERID'"`                       // 用户ID
	MEMBERUSERID      int    `json:"memberuserid"  xorm:"'MEMBERUSERID'"`           // 所属机构ID
	USERINFOTYPE      string `json:"userinfotype"  xorm:"'USERINFOTYPE'"`           // 客户类型
	CUSTOMERNAME      string `json:"customername"  xorm:"'CUSTOMERNAME'"`           // 企业名称
	CARDTYPE          int32  `json:"cardtype"  xorm:"'CARDTYPE'"`                   // 证件类型
	CARDTYPENAME      string `json:"cardtypename"  xorm:"'CARDTYPENAME'"`           // 证件类型名称
	CARDNUM           string `json:"cardnum"  xorm:"'CARDNUM'"`                     // 证件号码
	MOBILE            string `json:"mobile"  xorm:"'MOBILE'"`                       // 手机号码
	TELPHONE          string `json:"telphone"  xorm:"'TELPHONE'"`                   // 联系电话
	ADDRESS           string `json:"address"  xorm:"'ADDRESS'"`                     // 通讯地址
	REMARK            string `json:"remark"  xorm:"'REMARK'"`                       // 备注
	STATUS            int32  `json:"status"  xorm:"'STATUS'"`                       // 账户状态 -  1:待激活 2:待审核 3:待复审 4:正常 5:审核拒绝 6:注销
	STATUSDESC        string `json:"statusdesc"`                                    // 账户状态中文描述
	ATTACHMENT1       string `json:"attachment1"  xorm:"'ATTACHMENT1'"`             // 附件1
	CARDFRONTPHOTOURL string `json:"cardfrontphotourl"  xorm:"'CARDFRONTPHOTOURL'"` // 证件正面图片地址
	COUNTRYID         int32  `json:"countryid"  xorm:"'COUNTRYID'"`                 // 国家
	PROVINCEID        int32  `json:"provinceid"  xorm:"'PROVINCEID'"`               // 省
	CITYID            int32  `json:"cityid"  xorm:"'CITYID'"`                       // 市
	DISTRICTID        int32  `json:"districtid"  xorm:"'DISTRICTID'"`               // 区域
	CREATETIME        string `json:"createtime"  xorm:"'CREATETIME'"`               // 创建时间
	MODIFYTIME        string `json:"modifytime"  xorm:"'MODIFYTIME'"`               // 修改时间
	AUDITTIME         string `json:"audittime"  xorm:"'AUDITTIME'"`                 // 审核时间
	NICKNAME          string `json:"nickname"  xorm:"'NICKNAME'"`                   // 昵称
}

func (r *ErmcpUserModel) buildWskhSql(accStatus string) string {
	str := "select t.userid," +
		"       t.memberareaid      MEMBERUSERID," +
		"       t.USERINFOTYPE," +
		"       t.CUSTOMERNAME," +
		"       t.NICKNAME," +
		"       t.CARDTYPE," +
		"       e.enumdicname       CARDTYPENAME," +
		"       t.CARDNUM," +
		"       t.mobilephone       MOBILE," +
		"       t.TELPHONE," +
		"       t.cardaddress       ADDRESS," +
		"       t.REMARK," +
		"       t.ATTACHMENT1," +
		"       t.CARDFRONTPHOTOURL," +
		"       t.userstate         status," +
		"       t.countryid," +
		"       t.provinceid," +
		"       t.districtid," +
		"       to_char(t.createtime,'yyyy-mm-dd hh24:mi:ss') createtime," +
		"       t.cityid," +
		"       to_char(t.modifiedtime,'yyyy-mm-dd hh24:mi:ss') modifytime," +
		"       to_char(t.auditime,'yyyy-mm-dd hh24:mi:ss') audittime" +
		"  from wskh_userinfo t" +
		"  left join enumdicitem e" +
		"    on t.cardtype = e.enumitemname" +
		"   and e.enumdiccode = 'certificatetype'" +
		" where t.userstate in (%v)" +
		"   and t.usertype = 6" +
		"   and t.memberareaid = %v" +
		" order by t.modifiedtime desc"
	return fmt.Sprintf(str, accStatus, r.MEMBERUSERID)
}

func (r *ErmcpUserModel) buildSql(accStatus string) string {
	str := "select t.userid," +
		"       t.memberuserid," +
		"       u.USERINFOTYPE," +
		"       u.CUSTOMERNAME," +
		"       u.NICKNAME," +
		"       u.CARDTYPEID        CARDTYPE," +
		"       e.enumdicname       CARDTYPENAME," +
		"       u.CARDNUM," +
		"       u.MOBILE," +
		"       u.TELPHONE," +
		"       u.ADDRESS," +
		"       u.REMARK," +
		"       u.ATTACHMENT1," +
		"       u.CARDFRONTPHOTOURL," +
		"       t.accountstatus     status," +
		"       u.countryid," +
		"       u.provinceid," +
		"       u.districtid," +
		"       to_char(t.createtime,'yyyy-mm-dd hh24:mi:ss') createtime," +
		"       to_char(t.modifytime,'yyyy-mm-dd hh24:mi:ss') modifytime," +
		"       to_char(t.audittime,'yyyy-mm-dd hh24:mi:ss') audittime," +
		"       u.cityid" +
		"  from useraccount t" +
		"  left join userinfo u" +
		"    on t.userid = u.userid" +
		"  left join enumdicitem e" +
		"    on u.cardtypeid = e.enumitemname" +
		"   and e.enumdiccode = 'certificatetype'" +
		" where t.usertype = 6" +
		"   and t.accountstatus in (%v)" +
		"   and t.memberuserid = %v"
	// 查正常状态,按审核时间排序
	if accStatus == "4" {
		str += " order by t.audittime desc"
	} else if accStatus == "6" {
		// 停用状态按修改时间排序
		str += " order by t.modifytime desc"
	}

	return fmt.Sprintf(str, accStatus, r.MEMBERUSERID)
}

// DecryptField 解密卡号和电话号码字段
func (r *ErmcpUserModel) DecryptField(isWskh bool) {
	key := "0d299ce2d4105282f7471074cb0f9f9d"
	key2, _ := hex.DecodeString(key)
	fd := func(str string) string {
		if str == "" {
			return str
		}
		d, _ := hex.DecodeString(str)
		if dst, err := utils.AESDecrypt(d, key2); err == nil {
			return string(dst)
		}
		return str
	}

	r.CARDNUM = fd(r.CARDNUM)
	r.MOBILE = fd(r.MOBILE)

	if !isWskh {
		// 正式用户表(非网上开户)需要解密电话号码
		r.TELPHONE = fd(r.TELPHONE)
	}

	if isWskh && len(r.ADDRESS) != 0 { // 地址不为空
		r.ADDRESS = fd(r.ADDRESS)
	}
}

// DecodeStausName 账户状态名称
func (r *ErmcpUserModel) DecodeStausName(queryType int32) {
	if queryType == 1 || queryType == 2 {
		// 网上开户状态
		if r.STATUS == 1 {
			r.STATUSDESC = "未提交"
		} else if r.STATUS == 2 || r.STATUS == 4 {
			r.STATUSDESC = "待审核"
		} else if r.STATUS == 3 || r.STATUS == 5 {
			r.STATUSDESC = "审核拒绝"
		}
	} else if queryType == 3 || queryType == 4 {
		if r.STATUS == 4 {
			r.STATUSDESC = "正常"
		} else if r.STATUS == 6 {
			r.STATUSDESC = "已注销"
		}
	}
}

// GetData 查询客户资料
func (r *ErmcpUserModel) GetData(queryType int32) ([]ErmcpUserModel, error) {
	sData := make([]ErmcpUserModel, 0)
	e := db.GetEngine()
	var sqlId string
	var isWskh bool = false
	switch queryType {
	case 1: //未提交
		sqlId = r.buildWskhSql("1")
		isWskh = true
	case 2: //待审核
		sqlId = r.buildWskhSql("2,4,5")
		isWskh = true
	case 3: //正常
		sqlId = r.buildSql("4")
	case 4: //已停用
		sqlId = r.buildSql("6")
	}
	s := e.SQL(sqlId)
	if err := s.Find(&sData); err != nil {
		logger.GetLogger().Errorf("ermcp query fail:%v", err)
		return sData, err
	}

	// 解密
	for i := range sData {
		sData[i].DecryptField(isWskh)
		sData[i].DecodeStausName(queryType)
	}

	return sData, nil
}