/** * @Author: zou.yingbin * @Create : 2021/6/24 10:07 * @Modify : 2021/6/24 10:07 */ package models import ( "fmt" "mtp2_if/db" "mtp2_if/mtpcache" "mtp2_if/utils" ) // QhjMgrSubArea 子机构列表 type QhjMgrSubArea struct { PARENTTOPUSER string `json:"parenttopuser" xorm:"PARENTTOPUSER"` // 上级顶级机构 [092=0,1时，默认为1， 092=2时若自已为顶级，则填入自己，自己不为顶级，填入ParentUserID的"ParentTopUser"] SUBACCOUNTLEVEL int32 `json:"subaccountlevel" xorm:"SUBACCOUNTLEVEL"` // 子账户层数 ROOTUSERID string `json:"rootuserid" xorm:"ROOTUSERID"` // 根用户ID USERID int64 `json:"userid" xorm:"USERID"` // 用户ID USERTYPE int32 `json:"usertype" xorm:"USERTYPE"` // 账户类型 - 1:交易所 2:机构 3:会员子机构 4:经纪人 5:投资者 6:客户 7:企业成员(云平台) ACCOUNTNAME string `json:"accountname" xorm:"ACCOUNTNAME"` // 账户名称(机构名称) PARENTUSERID int64 `json:"parentuserid" xorm:"PARENTUSERID"` // 所属机构ID SUBAREALEVELPATH string `json:"subarealevelpath" xorm:"SUBAREALEVELPATH"` // 子机构层级路径(逗号分隔,首尾加逗号) MEMBERUSERID int64 `json:"memberuserid" xorm:"MEMBERUSERID"` // 所属会员ID } func (r *QhjMgrSubArea) calc() { } func (r *QhjMgrSubArea) buildSql() string { var sqlId utils.SQLVal = "select t.userid," + " t.accountname," + " t.parentuserid," + " t.rootuserid," + " t.memberuserid," + " t.parenttopuser," + " t.subarealevelpath," + " t.subaccountlevel," + " t.usertype" + " from useraccount t" + " where 1 = 1" sqlId.And("t.usertype", 2) sqlId.Join(fmt.Sprintf("and t.userid != %v", r.USERID)) sqlId.Join(fmt.Sprintf(" and t.subarealevelpath like ',%%%v%%,'", r.USERID)) return sqlId.String() } // GetDataEx 获取子机构列表 func (r *QhjMgrSubArea) GetDataEx() (interface{}, error) { sData := make([]QhjMgrSubArea, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].calc() } return sData, err } // QhjMgrCustomer 客户资料 type QhjMgrCustomer struct { USERID int64 `json:"userid" xorm:"'USERID'"` // 用户ID MEMBERUSERID int64 `json:"memberuserid" xorm:"'MEMBERUSERID'"` // 所属机构ID(所属会员) PARENTUSERID int64 `json:"parentuserid" xorm:"'PARENTUSERID'"` // 上级机构(所属机构) USERINFOTYPE int32 `json:"userinfotype" xorm:"'USERINFOTYPE'"` // 客户类型 1-个人 2-企业 CUSTOMERNAME string `json:"customername" xorm:"'CUSTOMERNAME'"` // 客户名称 CARDTYPE int32 `json:"cardtype" xorm:"'CARDTYPE'"` // 证件类型 CARDNUM string `json:"cardnum" xorm:"'CARDNUM'"` // 证件号码 MOBILE string `json:"mobile" xorm:"'MOBILE'"` // 手机号码 TELPHONE string `json:"telphone" xorm:"'TELPHONE'"` // 联系电话 ADDRESS string `json:"address" xorm:"'ADDRESS'"` // 通讯地址 REMARK string `json:"remark" xorm:"'REMARK'"` // 备注 STATUS int32 `json:"status" xorm:"'STATUS'"` // 开户状态(网上开户表wskh_userinfo) 1:未提交 2: 待初审 3:初审拒绝 4:待复审 5:复审拒绝 6:测评不通过 ; 账户状态(正式表useraccount) 1:待激活 2:待审核 3:待复审 4:正常 5:审核拒绝 6:停用(注销) 7:注销(删除) STATUSDESC string `json:"statusdesc"` // 账户状态中文描述 ATTACHMENT1 string `json:"attachment1" xorm:"'ATTACHMENT1'"` // 附件1 CARDFRONTPHOTOURL string `json:"cardfrontphotourl" xorm:"'CARDFRONTPHOTOURL'"` // 证件正面图片地址 CARDBACKPHOTOURL string `json:"cardbackphotourl" xorm:"'CARDBACKPHOTOURL'"` // 证件反面图片地址 PROXYSTATEMENTURL string `json:"proxystatementurl" xorm:"'PROXYSTATEMENTURL'"` // 授权委托书 COUNTRYID int32 `json:"countryid" xorm:"'COUNTRYID'"` // 国家 PROVINCEID int32 `json:"provinceid" xorm:"'PROVINCEID'"` // 省 CITYID int32 `json:"cityid" xorm:"'CITYID'"` // 市 DISTRICTID int32 `json:"districtid" xorm:"'DISTRICTID'"` // 区域 CREATETIME string `json:"createtime" xorm:"'CREATETIME'"` // 创建时间 MODIFYTIME string `json:"modifytime" xorm:"'MODIFYTIME'"` // 修改时间 AUDITTIME string `json:"audittime" xorm:"'AUDITTIME'"` // 审核时间 NICKNAME string `json:"nickname" xorm:"'NICKNAME'"` // 昵称 TAXPAYERNUM string `json:"taxpayernum" xorm:"'taxpayernum'"` // 纳税人识别号 LEGALPERSONNAME string `json:"legalpersonname" xorm:"'legalpersonname'"` // 法人姓名(企业) CONTACTNAME string `json:"contactname" xorm:"'contactname'"` // 联系人 EMAIL string `json:"email" xorm:"'EMAIL'"` // 邮件 USERNAME string `json:"username" xorm:"'USERNAME'"` // 用户名称 LOGINCODE string `json:"logincode" xorm:"'LOGINCODE'"` // 登录账号(代码) BROKERID int64 `json:"brokerid" xorm:"'BROKERID'"` // 所属客户经理id BROKERNAME string `json:"brokername" xorm:"'BROKERNAME'"` // 所属客户经理名称 BIRTHDAY string `json:"birthday" xorm:"'BIRTHDAY'" form:"birthday"` // 生日 SEX int32 `json:"sex" xorm:"'SEX'"` // 性别 0-女 1-男 COUNTRYNAME string `json:"countryname"` // 国家名称 CITYNAME string `json:"cityname"` // 城市名称 PROVINCENAME string `json:"provincename"` // 省名称 DISTRICTNAME string `json:"districtname"` // 地区名称 CARDTYPENAME string `json:"cardtypename"` // 证件类型名称 MEMBERUSERNAME string `json:"memberusername"` // 所属会员名称 PARENTUSERNAME string `json:"parentusername"` //所属机构名称 QUERYTYPE int32 `json:"querytype"` // 查询类型 1:未提交(网上开户表) 2:待审核(网上开户表) 3:正常 4:停用 IncludeSub int32 `json:"-"` // 包子机构客户 1-包含 } func (r *QhjMgrCustomer) calc() { r.MEMBERUSERNAME = mtpcache.GetUserNameByUserId(r.MEMBERUSERID) if len(r.USERNAME) == 0 { r.USERNAME = mtpcache.GetUserNameByUserId(r.USERID) } r.PARENTUSERNAME = mtpcache.GetUserNameByUserId(r.PARENTUSERID) r.COUNTRYNAME = mtpcache.GetDivisionName(r.COUNTRYID) r.CITYNAME = mtpcache.GetDivisionName(r.CITYID) r.PROVINCENAME = mtpcache.GetDivisionName(r.PROVINCEID) r.DISTRICTNAME = mtpcache.GetDivisionName(r.DISTRICTID) if r.CARDTYPE >= 0 { // 如果数据库里cardtype为空, 则nvl(cardtype,-1)取出, 用于判断是否为空 r.CARDTYPENAME = mtpcache.GetCardName(r.CARDTYPE) } else { r.CARDTYPE = 0 } fDesc := func(status int32, vDesc string) { if r.STATUS == status { r.STATUSDESC = vDesc } } switch r.QUERYTYPE { case 1, 2: // 网上开户表 r.ADDRESS = DecryptField(r.ADDRESS) r.TELPHONE = DecryptField(r.TELPHONE) //开户状态 - 1:未提交 2: 待初审 3:初审拒绝 4:待复审 5:复审拒绝 6:测评不通过 fDesc(1, "未提交") fDesc(2, "待初审") fDesc(3, "初审拒绝") fDesc(4, "待复审") fDesc(5, "复审拒绝") fDesc(6, "测评不通过") case 3, 4: // 正式表 r.TELPHONE = DecryptField(r.TELPHONE) //账户状态 - 1:待激活 2:待审核 3:待复审 4:正常 5:审核拒绝 6:停用(注销) 7:注销(删除) fDesc(1, "待激活") fDesc(2, "待审核") fDesc(3, "待复审") fDesc(4, "正常") fDesc(5, "审核拒绝") fDesc(6, "停用(注销)") fDesc(7, "注销(删除)") } r.CARDNUM = DecryptField(r.CARDNUM) r.MOBILE = DecryptField(r.MOBILE) r.EMAIL = DecryptField(r.EMAIL) if r.LOGINCODE == "" { r.LOGINCODE = r.MOBILE } if len(r.CUSTOMERNAME) == 0 { r.CUSTOMERNAME = r.USERNAME } } func (r *QhjMgrCustomer) level() int32 { if r.IncludeSub > 0 { // 层级, 最多20层 return 20 } return 1 } func (r *QhjMgrCustomer) buildSql() string { if r.QUERYTYPE == 1 || r.QUERYTYPE == 2 { return r.buildSqlWskh() } return r.buildSqlNormal() } func (r *QhjMgrCustomer) buildSqlWskh() string { var sqlId utils.SQLVal = ` select u.accountname brokername, a.* from (select t.userid, t.memberareaid MEMBERUSERID, t.USERINFOTYPE, t.username, t.CUSTOMERNAME, t.NICKNAME, nvl(t.CARDTYPE,-1) CARDTYPE, t.CARDNUM, t.mobilephone MOBILE, t.TELPHONE, t.cardaddress ADDRESS, t.REMARK, t.ATTACHMENT1, t.CARDFRONTPHOTOURL, t.CARDBACKPHOTOURL, t.proxystatementurl, t.userstate status, t.countryid, t.provinceid, t.districtid, to_char(t.createtime, 'yyyy-mm-dd hh24:mi:ss') createtime, t.cityid, to_char(t.modifiedtime, 'yyyy-mm-dd hh24:mi:ss') modifytime, to_char(t.auditime, 'yyyy-mm-dd hh24:mi:ss') audittime, t.taxpayernum, t.legalpersonname, t.contactname, t.email, t.areaid parentuserid, t.logincode, t.usertype, t.sex, to_char(t.birthday, 'yyyy-mm-dd hh24:mi:ss') birthday, to_number(t.brokerid) brokerid, t.birthday birthday2 from wskh_userinfo t where 1=1 %v) a left join useraccount u on a.brokerid = u.userid where 1 = 1 and a.status in (%v) ` var strParm string if r.IncludeSub == 0 { strParm = fmt.Sprintf(" and t.areaid = %v", r.USERID) } else { str := ` and t.areaid in (select t.userid from useraccount t where t.subarealevelpath like '%%,%v,%%' ) ` strParm = fmt.Sprintf(str, r.USERID) } status := DecodeStr(r.QUERYTYPE == 1, "1", "2,4,5") sqlId.FormatParam(strParm, status) sqlId.AndEx("a.USERINFOTYPE", r.USERINFOTYPE, r.USERINFOTYPE > 0) sqlId.JoinEx(len(r.CUSTOMERNAME) > 0, fmt.Sprintf(` and a.customername like '%%%v%%'`, r.CUSTOMERNAME)) sqlId.JoinEx(len(r.NICKNAME) > 0, fmt.Sprintf(` and a.nickname like '%%%v%%'`, r.NICKNAME)) sqlId.JoinEx(len(r.BROKERNAME) > 0, fmt.Sprintf(` and u.accountname like '%%%v%%'`, r.BROKERNAME)) if len(r.BIRTHDAY) == 2 { sqlId.JoinFormat(" and to_char(a.birthday2, 'mm') = '%v'", r.BIRTHDAY) } else if len(r.BIRTHDAY) == 4 { sqlId.JoinFormat(" and to_char(a.birthday2, 'mmdd') = '%v'", r.BIRTHDAY) } return sqlId.String() } func (r *QhjMgrCustomer) buildSqlNormal() string { var sqlId utils.SQLVal = ` with tmp as (select t.userid, wm_concat(t.logincode) logincode from loginaccount t group by t.userid) select a.*, k.logincode, u.USERINFOTYPE, u.CUSTOMERNAME, u.NICKNAME, nvl(u.CARDTYPEID,-1) CARDTYPE, u.CARDNUM, u.MOBILE, u.TELPHONE, u.ADDRESS, u.REMARK, u.ATTACHMENT1, u.CARDFRONTPHOTOURL, u.CARDBACKPHOTOURL, u.proxystatementurl, u.countryid, u.provinceid, u.districtid, u.taxpayernum, u.legalpersonname, u.contactname, u.email, u.cityid, u.sex, to_char(u.birthday, 'yyyy-mm-dd hh24:mi:ss') birthday, u2.accountname brokername from (select t.userid, t.usertype, t.parentuserid, t.memberuserid, t.broker, t.accountstatus status, to_char(t.createtime, 'yyyy-mm-dd hh24:mi:ss') createtime, to_char(t.modifytime, 'yyyy-mm-dd hh24:mi:ss') modifytime, to_char(t.audittime, 'yyyy-mm-dd hh24:mi:ss') audittime from useraccount t where 1 = 1 and t.usertype = 6 %v) a left join tmp k on a.userid = k.userid left join userinfo u on a.userid = u.userid left join useraccount u2 on a.broker=u2.userid where 1 = 1 ` status := DecodeStr(r.QUERYTYPE == 3, "4", "6") var sqlParam utils.SQLVal sqlParam.And("t.accountstatus", status) if r.IncludeSub == 1 { sqlParam.Join(fmt.Sprintf(" and t.subarealevelpath like '%%,%v,%%'", r.USERID)) } else { sqlParam.And("t.parentuserid", r.USERID) } sqlId.FormatParam(sqlParam.String()) sqlId.AndEx("u.USERINFOTYPE", r.USERINFOTYPE, r.USERINFOTYPE > 0) sqlId.JoinEx(len(r.CUSTOMERNAME) > 0, fmt.Sprintf(` and u.customername like '%%%v%%'`, r.CUSTOMERNAME)) sqlId.JoinEx(len(r.NICKNAME) > 0, fmt.Sprintf(` and u.nickname like '%%%v%%'`, r.NICKNAME)) sqlId.JoinEx(len(r.BROKERNAME) > 0, fmt.Sprintf(` and u2.accountname like '%%%v%%'`, r.BROKERNAME)) if len(r.BIRTHDAY) == 2 { sqlId.JoinFormat(" and to_char(u.birthday, 'mm') = '%v'", r.BIRTHDAY) } else if len(r.BIRTHDAY) == 4 { sqlId.JoinFormat(" and to_char(u.birthday, 'mmdd') = '%v'", r.BIRTHDAY) } return sqlId.String() } // GetDataEx 获取客户资料 func (r *QhjMgrCustomer) GetDataEx() (interface{}, error) { sData := make([]QhjMgrCustomer, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].QUERYTYPE = r.QUERYTYPE sData[i].calc() } return sData, err } // QhjMgrAccountOutInApply 充值提现（出入金） type QhjMgrAccountOutInApply struct { EXECUTETYPE int32 `json:"executetype" xorm:"EXECUTETYPE"` // 申请类型 - 1:出金 2:入金 3: 单边账调整:入金; 4:单边账调整:出金 5:外部母账户调整:入金 6:外部母账户调整:出金 7:外部子账户:入金 8:外部子账户:出金 AMOUNT float64 `json:"amount" xorm:"AMOUNT"` // 金额 CURRENCY string `json:"currency" xorm:"CURRENCY"` // 币种 CHARGE float64 `json:"charge" xorm:"CHARGE"` // 手续费 ACCOUNTPWD string `json:"accountpwd" xorm:"ACCOUNTPWD"` // 资金密码 UPDATETIME string `json:"updatetime" xorm:"UPDATETIME"` // 更新时间(申请时间) BANKID string `json:"bankid" xorm:"BANKID"` // 银行编号 BANKNAME string `json:"bankname" xorm:"'BANKNAME'"` // 银行名称 BRANCHBANKID string `json:"branchbankid" xorm:"BRANCHBANKID"` // 银行支行号 BRANCHBANKNAME string `json:"branchbankname" xorm:"BRANCHBANKNAME"` // 银行支行名称 BANKACCOUNTNO string `json:"bankaccountno" xorm:"BANKACCOUNTNO"` // 银行卡号 BANKACCOUNTNAME string `json:"bankaccountname" xorm:"BANKACCOUNTNAME"` // 银行账户名 EXTENDINFO string `json:"extendinfo" xorm:"EXTENDINFO"` // 扩展信息 AUDITID int64 `json:"auditid" xorm:"AUDITID"` // 审核人 AUDITTIME string `json:"audittime" xorm:"AUDITTIME"` // 审核时间 REMARK string `json:"remark" xorm:"REMARK"` // 备注 NETADDR string `json:"netaddr" xorm:"NETADDR"` // 调转网址 ACCOUNTTICKET string `json:"accountticket" xorm:"ACCOUNTTICKET"` // 最新账户服务流水号 CHECKERRORFLAG int32 `json:"checkerrorflag" xorm:"CHECKERRORFLAG"` // 对账差错标志 - 1:为单边账;其它为正常出入金 REMARK2 string `json:"remark2" xorm:"REMARK2"` // 备注(失败原因) REAUDITID int64 `json:"reauditid" xorm:"REAUDITID"` // 复审人 REAUDITTIME string `json:"reaudittime" xorm:"REAUDITTIME"` // 复审时间 REAUDITREMARK string `json:"reauditremark" xorm:"REAUDITREMARK"` // 复审备注 APPLYREMARK string `json:"applyremark" xorm:"APPLYREMARK"` // 申请备注 RELATEDORDERID string `json:"relatedorderid" xorm:"RELATEDORDERID"` // 三方关联ID CAPAMOUNTOUT float64 `json:"capamountout" xorm:"CAPAMOUNTOUT"` // 出金(劣后本金) - 外部子账户 INFAMOUNT float64 `json:"infamount" xorm:"INFAMOUNT"` // 劣后金额(自有) PRIAMOUNT float64 `json:"priamount" xorm:"PRIAMOUNT"` // 优先金额(授信) BANK_APPLY_TICKET string `json:"bank_apply_ticket" xorm:"BANK_APPLY_TICKET"` // 银行申请流水 CERTIFICATEPHOTOURL string `json:"certificatephotourl" xorm:"CERTIFICATEPHOTOURL"` // 凭证地址 SOUCREAMOUNT float64 `json:"soucreamount" xorm:"SOUCREAMOUNT"` // 原始出入金金额 SOUCRECURRENCYID int64 `json:"soucrecurrencyid" xorm:"SOUCRECURRENCYID"` // 原始出入金币种 ACCOUNTCODE string `json:"accountcode" xorm:"ACCOUNTCODE"` // 资金账号 CUSBANKID string `json:"cusbankid" xorm:"CUSBANKID"` // 托管银行编号 TRADEDATE string `json:"tradedate" xorm:"TRADEDATE"` // 交易日(yyyyMMdd) EXCHTICKET string `json:"exchticket" xorm:"EXCHTICKET"` // 银行服务流水号 EXTOPERATEID int64 `json:"extoperateid" xorm:"EXTOPERATEID"` // 交易服务流水号 BANKTICKET string `json:"bankticket" xorm:"BANKTICKET"` // 银行流水 APPLYSTATUS int32 `json:"applystatus" xorm:"APPLYSTATUS"` // 申请状态 - 1:待审核 2:待复审 3:初审拒绝 4:交易冻结中 5:交易解冻中 6:交易解冻扣款中 7:交易入金中 8:交易冻结/解冻/扣款中(银行发起出金时用) 9:银行出金中 10:银行入金中 11:成功 12:失败 13:银行审核中 14:账户服务入金失败； 15:账户服务解冻失败； 16:账户服务解冻扣款失败； 17:账户服务出金失败 18:复审通过 19:复审拒绝 20:提交审核，账户冻结中 21:审核拒绝，账户解冻中；22: 待审核，账户服务解冻回滚中； 23：待复审，账户服务解冻回滚中； 24： 审核通过，账户冻结金额检查中；25： 复审通过，账户冻结金额检查中； USERID int64 `json:"userid" xorm:"'USERID'"` // 用户id LOGINCODE string `json:"logincode" xorm:"'LOGINCODE'"` // 登录账号(账号) USERINFOTYPE int32 `json:"userinfotype" xorm:"'USERINFOTYPE'"` // 账户类型 1-个人 2-企业 ACCOUNTNAME string `json:"accountname" xorm:"'ACCOUNTNAME'"` // 用户名称(名称) QUERYTYPE int32 `json:"-"` // 查询类型 1-提现 2-充值 STATUS int32 `json:"-"` // 查询状态 1-待审核 2-审核通过 3-审核拒绝 BeginDate string `json:"-"` // 开始日期(yyyymmdd) EndDate string `json:"-"` // 结束日期(yyyymmdd) FilterName string `json:"-"` // 账户(模糊匹配) } func (r *QhjMgrAccountOutInApply) calc() { r.BANKACCOUNTNAME = DecryptField(r.BANKACCOUNTNAME) r.BANKACCOUNTNO = DecryptField(r.BANKACCOUNTNO) } func (r *QhjMgrAccountOutInApply) buildSql() string { var sqlId utils.SQLVal = "with tmp as(select t.userid, wm_concat(t.logincode) logincode from loginaccount t group by t.userid)" + "SELECT t.EXECUTETYPE," + " t.AMOUNT," + " t.CURRENCY," + " t.CHARGE," + " t.ACCOUNTPWD," + " to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," + " t.BANKID," + " t.BRANCHBANKID," + " t.BRANCHBANKNAME," + " t.BANKACCOUNTNO," + " t.BANKACCOUNTNAME," + " t.EXTENDINFO," + " t.AUDITID," + " to_char(t.AUDITTIME, 'yyyy-mm-dd hh24:mi:ss') AUDITTIME," + " t.REMARK," + " t.NETADDR," + " t.ACCOUNTTICKET," + " t.CHECKERRORFLAG," + " t.REMARK2," + " t.REAUDITID," + " to_char(t.REAUDITTIME, 'yyyy-mm-dd hh24:mi:ss') REAUDITTIME," + " t.REAUDITREMARK," + " t.APPLYREMARK," + " t.RELATEDORDERID," + " t.CAPAMOUNTOUT," + " t.INFAMOUNT," + " t.PRIAMOUNT," + " t.BANK_APPLY_TICKET," + " to_char(t.CERTIFICATEPHOTOURL) CERTIFICATEPHOTOURL," + " t.SOUCREAMOUNT," + " t.SOUCRECURRENCYID," + " t.ACCOUNTCODE," + " t.CUSBANKID," + " t.TRADEDATE," + " t.EXCHTICKET," + " t.EXTOPERATEID," + " t.BANKTICKET," + " t.APPLYSTATUS," + " ta.userid," + " u.accountname," + " ui.userinfotype," + " tmp.logincode," + " b.bankname" + " FROM BANK_ACCOUNTOUTINAPPLY t" + " INNER JOIN TAACCOUNT ta" + " on t.accountcode = to_char(ta.accountid)" + " INNER JOIN USERACCOUNT u" + " on ta.relateduserid = u.userid" + " INNER JOIN USERINFO ui" + " on u.userid = ui.userid" + " LEFT JOIN BANK_BANKINFO b on t.bankid=b.bankid" + " LEFT JOIN tmp" + " on u.userid = tmp.userid" + " WHERE 1 = 1" sqlId.And("EXECUTETYPE", r.QUERYTYPE) switch r.STATUS { case 1: sqlId.JoinFormat(" and t.APPLYSTATUS in(%v)", InStr(1, 2)) case 2: sqlId.JoinFormat(" and t.APPLYSTATUS in(%v)", InStr(11, 18, 24, 25)) case 3: sqlId.JoinFormat(" and t.APPLYSTATUS in(%v)", InStr(3, 12, 21)) } if len(r.BeginDate) > 0 { sqlId.Join(fmt.Sprintf(" and t.TRADEDATE >= %v", r.BeginDate)) } if len(r.EndDate) > 0 { sqlId.Join(fmt.Sprintf(" and t.TRADEDATE <= %v", r.EndDate)) } if len(r.FilterName) > 0 { sqlId.Join(fmt.Sprintf(" and (tmp.logincode like '%%%v%%' or u.accountname like '%%%v%%')", r.FilterName, r.FilterName)) } sqlId.Join(" order by t.UPDATETIME desc") return sqlId.String() } // GetDataEx 获取充值提现（出入金） func (r *QhjMgrAccountOutInApply) GetDataEx() (interface{}, error) { sData := make([]QhjMgrAccountOutInApply, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].calc() } return sData, err } // QhjTradeEx 额外的用户和商品信息 type QhjTradeEx struct { LOGINCODE string `json:"logincode" xorm:"'LOGINCODE'"` // 登录代码(账户) USERID int64 `json:"userid" xorm:"'USERID'"` // 用户id ACCOUNTNAME string `json:"accountname" xorm:"'ACCOUNTNAME'"` // 用户名称 ENUMDICNAME string `json:"enumdicname" xorm:"'ENUMDICNAME'"` // 单位名称(商品) GOODSCODE string `json:"goodscode" xorm:"GOODSCODE"` // 商品代码(内部) GOODSNAME string `json:"goodsname" xorm:"GOODSNAME"` // 商品名称 GOODUNITID int32 `json:"goodunitid" xorm:"GOODUNITID"` // 报价单位ID AGREEUNIT float64 `json:"agreeunit" xorm:"AGREEUNIT"` // 合约乘数 DECIMALPLACE int32 `json:"decimalplace" xorm:"DECIMALPLACE"` // 报价小数位 QTYDECIMALPLACE int32 `json:"qtydecimalplace" xorm:"QTYDECIMALPLACE"` // 成交量小数位 USERINFOTYPE int32 `json:"userinfotype" xorm:"'USERINFOTYPE'"` // 账户类型 1-个人 2-企业 MOBILE string `json:"-" xorm:"'MOBILE'"` // 手机号码 MARKETID int32 `json:"marketid" xorm:"'MARKETID'"` // 市场id TRADEMODE int32 `json:"trademode" xorm:"'TRADEMODE'"` // 交易模式 } func (r *QhjTradeEx) dealConvert() { if r.LOGINCODE == "" { r.LOGINCODE = DecryptField(r.MOBILE) } } // QhjMgrPositionRsp 持仓汇总 type QhjMgrPositionRsp struct { QhjTradeEx `xorm:"extends"` ACCOUNTID int64 `json:"accountid"` // 账号Id GOODSID int32 `json:"goodsid"` // 商品Id HOLDERTYPE int32 `json:"holdertype"` // 持仓类别 - 1:单边持仓 2:双边持仓 HOLDERQTY float64 `json:"holderqty"` // 持有数量 FROZENQTY float64 `json:"frozenqty"` // 冻结数量 AVIALQTY float64 `json:"avialqty"` // 可用数量 HOLDERAMOUNT float64 `json:"holderamount"` // 金额 AVERAGEPRICE float64 `json:"averageprice"` // 均价 } // QhjMgrTradePosition 持仓汇总 type QhjMgrTradePosition struct { ACCOUNTID int64 `json:"accountid" xorm:"ACCOUNTID"` // 账号Id GOODSID int32 `json:"goodsid" xorm:"GOODSID"` // 商品Id HOLDERTYPE int32 `json:"holdertype" xorm:"HOLDERTYPE"` // 持仓类别 - 1:单边持仓 2:双边持仓 BUYPOSITIONQTY float64 `json:"buypositionqty" xorm:"BUYPOSITIONQTY"` // 买期初持仓数量 BUYHOLDERAMOUNT float64 `json:"buyholderamount" xorm:"BUYHOLDERAMOUNT"` // 买期初持仓总金额[商品币种] BUYCURPOSITIONQTY float64 `json:"buycurpositionqty" xorm:"BUYCURPOSITIONQTY"` // 买当前持仓总数量 BUYCURHOLDERAMOUNT float64 `json:"buycurholderamount" xorm:"BUYCURHOLDERAMOUNT"` // 买当前持仓总金额[商品币种] BUYFROZENQTY float64 `json:"buyfrozenqty" xorm:"BUYFROZENQTY"` // 买持仓冻结数量 BUYOTHERFROZENQTY float64 `json:"buyotherfrozenqty" xorm:"BUYOTHERFROZENQTY"` // 买持仓其他冻结数量(交割冻结) BUYOPENREQQTY float64 `json:"buyopenreqqty" xorm:"BUYOPENREQQTY"` // 买开仓申请数量(用于比较最大持仓数量) BUYOPENTOTALQTY float64 `json:"buyopentotalqty" xorm:"BUYOPENTOTALQTY"` // 今日买开仓总数量 BUYCLOSETOTALQTY float64 `json:"buyclosetotalqty" xorm:"BUYCLOSETOTALQTY"` // 今日买平仓总数量 SELLPOSITIONQTY float64 `json:"sellpositionqty" xorm:"SELLPOSITIONQTY"` // 卖期初持仓数量 SELLHOLDERAMOUNT float64 `json:"sellholderamount" xorm:"SELLHOLDERAMOUNT"` // 卖期初持仓总金额[商品币种] SELLCURPOSITIONQTY float64 `json:"sellcurpositionqty" xorm:"SELLCURPOSITIONQTY"` // 卖当前持仓数量 SELLCURHOLDERAMOUNT float64 `json:"sellcurholderamount" xorm:"SELLCURHOLDERAMOUNT"` // 卖当前持仓总金额[商品币种] SELLFROZENQTY float64 `json:"sellfrozenqty" xorm:"SELLFROZENQTY"` // 卖持仓冻结 SELLOTHERFROZENQTY float64 `json:"sellotherfrozenqty" xorm:"SELLOTHERFROZENQTY"` // 卖持仓其他冻结(交割冻结) SELLOPENREQQTY float64 `json:"sellopenreqqty" xorm:"SELLOPENREQQTY"` // 卖开仓申请数量(用于比较最大持仓数量) SELLOPENTOTALQTY float64 `json:"sellopentotalqty" xorm:"SELLOPENTOTALQTY"` // 今日卖开仓总数量 SELLCLOSETOTALQTY float64 `json:"sellclosetotalqty" xorm:"SELLCLOSETOTALQTY"` // 今日卖平仓总数量 USEDMARGIN float64 `json:"usedmargin" xorm:"USEDMARGIN"` // 占用保证金[商品币种] TRADEPROPERTY int32 `json:"tradeproperty" xorm:"TRADEPROPERTY"` // 交易属性 BUYTNQTY float64 `json:"buytnqty" xorm:"BUYTNQTY"` // 买T+N冻结总量 BUYTNUSEDQTY float64 `json:"buytnusedqty" xorm:"BUYTNUSEDQTY"` // 买T+N使用量(可以使用T+N的冻结数量) SELLTNQTY float64 `json:"selltnqty" xorm:"SELLTNQTY"` // 卖T+N冻结总量 SELLTNUSEDQTY float64 `json:"selltnusedqty" xorm:"SELLTNUSEDQTY"` // 卖T+N使用量(可以使用T+N的冻结数量) BUYCURTDPOSITION float64 `json:"buycurtdposition" xorm:"BUYCURTDPOSITION"` // 买期末今日头寸 BUYFRETDPOSITION float64 `json:"buyfretdposition" xorm:"BUYFRETDPOSITION"` // 买冻结今日头寸 SELLCURTDPOSITION float64 `json:"sellcurtdposition" xorm:"SELLCURTDPOSITION"` // 卖期末今日头寸 SELLFRETDPOSITION float64 `json:"sellfretdposition" xorm:"SELLFRETDPOSITION"` // 卖冻结今日头寸 QhjTradeEx `xorm:"extends"` IncludeSub int32 `json:"-"` // 是否包含子级 1-包含 FilterName string `json:"-"` // 模糊搜索 } func (r *QhjMgrTradePosition) calc() { r.dealConvert() } func (r *QhjMgrTradePosition) buildSql() string { var sqlId utils.SQLVal = "with k as" + " (select t.userid, wm_concat(t.logincode) logincode" + " from loginaccount t" + " group by t.userid)" + "select u.userid," + " u.accountname," + " k.logincode," + " u.subarealevelpath," + " ui.userinfotype," + " ui.mobile," + " g.goodsname," + " g.goodscode," + " g.goodunitid," + " g.qtydecimalplace," + " g.decimalplace," + " g.agreeunit," + " m.marketid," + " m.trademode," + " e.enumdicname," + " t.*" + " from tradeposition t" + " inner join taaccount ta" + " on t.accountid = ta.accountid" + " inner join useraccount u" + " on ta.relateduserid = u.userid" + " inner join userinfo ui" + " on u.userid = ui.userid" + " left join k" + " on u.userid = k.userid" + " left join goods g" + " on t.goodsid = g.goodsid" + " left join market m on g.marketid=m.marketid" + " left join enumdicitem e" + " on e.enumitemname = g.goodunitid" + " and e.enumdiccode = 'goodsunit'" + " where 1 = 1" if r.IncludeSub == 1 { sqlId.JoinFormat(" and u.subarealevelpath like '%%,%v,%%'", r.USERID) } else { sqlId.And("u.parentuserid", r.USERID) } sqlId.AndEx("ui.USERINFOTYPE", r.USERINFOTYPE, r.USERINFOTYPE > 0) sqlId.AndEx("m.TRADEMODE", r.TRADEMODE, r.TRADEMODE > 0) sqlId.AndEx("t.goodsid", r.GOODSID, r.GOODSID > 0) if len(r.FilterName) > 0 { sqlId.JoinFormat(" and (k.logincode like '%%%v%%' or u.accountname like '%%%v%%')", r.FilterName, r.FilterName) } return sqlId.String() } // GetDataEx 获取持仓汇总 func (r *QhjMgrTradePosition) GetDataEx() (interface{}, error) { sData := make([]QhjMgrTradePosition, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) sData2 := make([]QhjMgrPositionRsp, 0) for i := range sData { sData[i].calc() v := QhjMgrPositionRsp{QhjTradeEx: sData[i].QhjTradeEx} v.GOODSID = sData[i].GOODSID v.ACCOUNTID, v.HOLDERTYPE = sData[i].ACCOUNTID, sData[i].HOLDERTYPE v.HOLDERAMOUNT = sData[i].BUYCURHOLDERAMOUNT v.HOLDERQTY = sData[i].BUYCURPOSITIONQTY v.FROZENQTY = sData[i].BUYFROZENQTY v.AVIALQTY = v.HOLDERQTY - v.FROZENQTY zoomFun := NewZoomFun(int(v.QTYDECIMALPLACE)) zoomFun(&v.HOLDERQTY, &v.FROZENQTY, &v.AVIALQTY) if v.HOLDERQTY > 1e-10 { v.AVERAGEPRICE = v.HOLDERAMOUNT / v.HOLDERQTY } sData2 = append(sData2, v) } return sData2, err } // QhjMgrTradeDetailRsp type QhjMgrTradeDetailRsp struct { TRADEID string `json:"tradeid"` // 成交单号 GOODSID int32 `json:"goodsid"` // 商品id BUYORSELL int32 `json:"buyorsell"` // 成交类型(方向) 0:买 1:卖 ORDERID string `json:"orderid"` // 关联委托单号 TRADEDATE string `json:"tradedate"` // 交易日 ACCOUNTID int64 `json:"accountid"` // 资金账号 TRADETIME string `json:"tradetime"` // 成交时间 TRADEPRICE float64 `json:"tradeprice"` // 价格 TRADEQTY float64 `json:"tradeqty"` // 数量 TRADEAMOUNT float64 `json:"tradeamount"` // 金额 QhjTradeEx `xorm:"extends"` } // QhjMgrTradeDetail 成交明细 type QhjMgrTradeDetail struct { TRADEID string `json:"tradeid" xorm:"TRADEID2"` // 成交单号(101+Unix秒时间戳(10位)+2位(MarketServiceID)+xxxx) BUYORSELL int32 `json:"buyorsell" xorm:"BUYORSELL"` // 方向 - 0:买 1:卖 ORDERID string `json:"orderid" xorm:"ORDERID2"` // 委托单号 TRADEDATE string `json:"tradedate" xorm:"TRADEDATE"` // 交易日(yyyyMMdd) ACCOUNTID int64 `json:"accountid" xorm:"ACCOUNTID"` // 账户ID[报价币种] GOODSID int32 `json:"goodsid" xorm:"GOODSID"` // 商品ID MEMBERUSERID int64 `json:"memberuserid" xorm:"MEMBERUSERID"` // 会员id 个人投资者 需要填写 MATCHACCOUNTID int64 `json:"matchaccountid" xorm:"MATCHACCOUNTID"` // 对手账号id TRADETIME string `json:"tradetime" xorm:"TRADETIME2"` // 成交时间 TRADEPRICE float64 `json:"tradeprice" xorm:"TRADEPRICE"` // 成交价格 TRADEQTY float64 `json:"tradeqty" xorm:"TRADEQTY"` // 成交数量 TRADEAMOUNT float64 `json:"tradeamount" xorm:"TRADEAMOUNT"` // 成交金额[账户币种，用于所有权] CLOSEPL float64 `json:"closepl" xorm:"CLOSEPL"` // 平仓盈亏 INTCLOSEPL int64 `json:"intclosepl" xorm:"INTCLOSEPL"` // 整型盈亏(用于交易结算试算平衡-收益权) OPENCHARGE float64 `json:"opencharge" xorm:"OPENCHARGE"` // 建仓手续费(支付总手续费=(交易所比率+会员比率)*成交金额) CLOSECHARGE float64 `json:"closecharge" xorm:"CLOSECHARGE"` // 平仓手续费(支付总手续费=(交易所比率+会员比率)*成交金额) TRADETYPE int32 `json:"tradetype" xorm:"TRADETYPE"` // 成交类别 - 1:正常委托成交 2:定向做市成交(接单) 3:交割协议平仓成交 4:交割减仓成交 5:到期强平成交 6:风控斩仓成交 7:协议平仓(管理端)成交 8:仓单转持仓成交 9: 交割协议转让成交 10:受托竞价成交(接单) 11:协议转让成交 12:系统强行平仓 13:期权违约平仓 BUILDTYPE int32 `json:"buildtype" xorm:"BUILDTYPE"` // 委托单据类型 1:建仓 2:平仓 3:先平后建 OPENQTY float64 `json:"openqty" xorm:"OPENQTY"` // 开仓数量(先建后平操作 需要记录) CLOSEQTY float64 `json:"closeqty" xorm:"CLOSEQTY"` // 平仓数量(先建后平操作 需要记录) STATUS int32 `json:"status" xorm:"STATUS"` // 处理状态 - 1:待处理 2:已处理 3:处理失败 ISRECKONED int32 `json:"isreckoned" xorm:"ISRECKONED"` // 是否结算 - 0:未结算 1:已结算 TRADEPROPERTY int32 `json:"tradeproperty" xorm:"TRADEPROPERTY"` // 交易属性 OPENFEEALGORITHM int32 `json:"openfeealgorithm" xorm:"OPENFEEALGORITHM"` // 建仓手续费收取方式 1：比率 2：固定 OPENMEMBERCHARGEVALUE float64 `json:"openmemberchargevalue" xorm:"OPENMEMBERCHARGEVALUE"` // 建仓会员手续费设置值 OPENEXCHAGECHARGEVALUE float64 `json:"openexchagechargevalue" xorm:"OPENEXCHAGECHARGEVALUE"` // 建仓交易所手续费设置值 CLOSEFEEALGORITHM int32 `json:"closefeealgorithm" xorm:"CLOSEFEEALGORITHM"` // 平仓手续费收取方式 1：比率 2：固定 CLOSEMEMBERCHARGEVALUE float64 `json:"closememberchargevalue" xorm:"CLOSEMEMBERCHARGEVALUE"` // 平仓会员手续费设置值 CLOSEEXCHAGECHARGEVALUE float64 `json:"closeexchagechargevalue" xorm:"CLOSEEXCHAGECHARGEVALUE"` // 平仓交易所手续费设置值 OPTIONTYPE int32 `json:"optiontype" xorm:"OPTIONTYPE"` // 期权类型 - 1:认购(看涨) 2:认沽(看跌) PREMIUM float64 `json:"premium" xorm:"PREMIUM"` // 权利金 - [持仓单的权利金] ISPREEXERCISE int32 `json:"ispreexercise" xorm:"ISPREEXERCISE"` // 是否预申报- 0:否 1:是 PREEXERCISEPRICE float64 `json:"preexerciseprice" xorm:"PREEXERCISEPRICE"` // 预申报价格 ISCONFIRMEXERCISE int32 `json:"isconfirmexercise" xorm:"ISCONFIRMEXERCISE"` // 是否确认行权- 0:否 1:是 ISMAIN int32 `json:"ismain" xorm:"ISMAIN"` // 是否主单 - 0:不是 1:是 PERFORMANCEPLANID int64 `json:"performanceplanid" xorm:"PERFORMANCEPLANID"` // 履约计划ID[期权] PERFORMANCESTATUS int32 `json:"performancestatus" xorm:"PERFORMANCESTATUS"` // 履约状态[期权] - 0:无履约 1:未履约 2:履约中 3:履约完成 CREDITAMOUNT float64 `json:"creditamount" xorm:"CREDITAMOUNT"` // 授信金额 GCACCOUNTID int64 `json:"gcaccountid" xorm:"GCACCOUNTID"` // 账户ID[合约币种] CLOSEPL2 float64 `json:"closepl2" xorm:"CLOSEPL2"` // 平仓盈亏[逐笔] RELATEDOUTTRADEID int64 `json:"relatedouttradeid" xorm:"RELATEDOUTTRADEID"` // 关联外部成交单ID QhjTradeEx `xorm:"extends"` IncludeSub int32 `json:"-"` // 是否包含子级 1-包含 BeginDate string `json:"-"` // 开始交易日 EndDate string `json:"-"` // 结束交易日 FilterName string `json:"-"` // 模糊搜索 } func (r *QhjMgrTradeDetail) calc() { r.dealConvert() } func (r *QhjMgrTradeDetail) buildSql(bQueryHis bool) string { var sqlId utils.SQLVal = "with k as" + " (select t.userid, wm_concat(t.logincode) logincode" + " from loginaccount t" + " group by t.userid)" + "select u.userid," + " u.accountname," + " k.logincode," + " u.subarealevelpath," + " ui.userinfotype," + " ui.mobile," + " g.goodsname," + " g.goodscode," + " g.goodunitid," + " g.qtydecimalplace," + " g.decimalplace," + " g.agreeunit," + " m.marketid," + " m.trademode," + " e.enumdicname," + " to_char(t.TRADEID) TRADEID2," + " to_char(t.ORDERID) ORDERID2," + " to_char(t.TRADETIME, 'yyyy-mm-dd hh24:mi:ss') TRADETIME2," + " t.*" + " from %v t" + " inner join taaccount ta" + " on t.accountid = ta.accountid" + " inner join useraccount u" + " on ta.relateduserid = u.userid" + " inner join userinfo ui" + " on u.userid = ui.userid" + " left join k" + " on u.userid = k.userid" + " left join goods g" + " on t.goodsid = g.goodsid" + " left join market m on g.marketid=m.marketid" + " left join enumdicitem e" + " on e.enumitemname = g.goodunitid" + " and e.enumdiccode = 'goodsunit'" + " where 1 = 1" if bQueryHis { sqlId.FormatParam("his_trade_tradedetail") sqlId.And("t.isvaliddata", 1) } else { sqlId.FormatParam("trade_tradedetail") } if r.IncludeSub == 1 { sqlId.JoinFormat(" and u.subarealevelpath like '%%,%v,%%'", r.USERID) } else { sqlId.And("u.parentuserid", r.USERID) } sqlId.AndEx("ui.USERINFOTYPE", r.USERINFOTYPE, r.USERINFOTYPE > 0) sqlId.AndEx("m.TRADEMODE", r.TRADEMODE, r.TRADEMODE > 0) sqlId.AndEx("t.goodsid", r.GOODSID, r.GOODSID > 0) if len(r.BeginDate) > 0 { sqlId.JoinFormat(" and t.TRADEDATE >=%v", r.BeginDate) } if len(r.EndDate) > 0 { sqlId.JoinFormat(" and t.TRADEDATE <=%v", r.EndDate) } if len(r.FilterName) > 0 { sqlId.JoinFormat(" and (k.logincode like '%%%v%%' or u.accountname like '%%%v%%')", r.FilterName, r.FilterName) } return sqlId.String() } // GetDataEx 获取成交明细 func (r *QhjMgrTradeDetail) GetDataEx() (interface{}, error) { sData := make([]QhjMgrTradeDetail, 0) sCurData := make([]QhjMgrTradeDetail, 0) err := db.GetEngine().SQL(r.buildSql(false)).Find(&sCurData) sHisData := make([]QhjMgrTradeDetail, 0) err = db.GetEngine().SQL(r.buildSql(true)).Find(&sHisData) sData2 := make([]QhjMgrTradeDetailRsp, 0) sData = append(sData, sCurData...) sData = append(sData, sHisData...) for i := range sData { sData[i].calc() v := QhjMgrTradeDetailRsp{QhjTradeEx: sData[i].QhjTradeEx} v.ACCOUNTID, v.TRADEID = sData[i].ACCOUNTID, sData[i].TRADEID v.TRADEAMOUNT, v.TRADEQTY = sData[i].TRADEAMOUNT, sData[i].TRADEQTY v.TRADEDATE, v.TRADETIME = sData[i].TRADEDATE, sData[i].TRADETIME v.GOODSID, v.ORDERID = sData[i].GOODSID, sData[i].ORDERID v.TRADEPRICE, v.BUYORSELL = sData[i].TRADEPRICE, sData[i].BUYORSELL zoonFun := NewZoomFun(int(v.QTYDECIMALPLACE)) zoonFun(&v.TRADEQTY) sData2 = append(sData2, v) } return sData2, err } // QhjMgrTradeOrderDetailRsp 委托明细(给终端) type QhjMgrTradeOrderDetailRsp struct { GOODSID int32 `json:"goodsid"` // 商品id ORDERID string `json:"orderid"` // 关联委托单号 TRADEDATE string `json:"tradedate"` // 交易日 ACCOUNTID int64 `json:"accountid"` // 资金账号 ORDERTIME string `json:"ordertime"` // 委托时间 ORDERPRICE float64 `json:"orderprice"` // 价格 ORDERQTY float64 `json:"orderqty"` // 数量 AMOUNT float64 `json:"amount"` // 金额 ORDERSTATUS int32 `json:"orderstatus"` // 委托状态 - 1： 委托请求 2：待冻结 3：委托成功 4： 委托失败 5：配对成功 6： 已撤销 7：部分成交 8：已成交 9：部成部撤 10：成交失败 11：已拒绝 12:经过摘牌(先摘后挂专用-先摘后挂已摘过) 13:冻结成功(通道交易专用) 14:通道已撤 15:通道部成部撤 16:成交失败违约(荷兰式竞拍专用) QhjTradeEx `xorm:"extends"` } // QhjMgrTradeOrderDetail 委托明细 type QhjMgrTradeOrderDetail struct { CLOSEMEMBERCHARGEVALUE float64 `json:"closememberchargevalue" xorm:"CLOSEMEMBERCHARGEVALUE"` // 平仓会员手续费设置值 CLOSEEXCHAGECHARGEVALUE float64 `json:"closeexchagechargevalue" xorm:"CLOSEEXCHAGECHARGEVALUE"` // 平仓交易所手续费设置值 OPTIONTYPE int32 `json:"optiontype" xorm:"OPTIONTYPE"` // 期权类型 - 1:认购(看涨) 2:认沽(看跌) PREMIUM float64 `json:"premium" xorm:"PREMIUM"` // 权利金 ISPREEXERCISE int32 `json:"ispreexercise" xorm:"ISPREEXERCISE"` // 是否预申报- 0:否 1:是 PREEXERCISEPRICE float64 `json:"preexerciseprice" xorm:"PREEXERCISEPRICE"` // 预申报价格 ISCONFIRMEXERCISE int32 `json:"isconfirmexercise" xorm:"ISCONFIRMEXERCISE"` // 是否确认行权- 0:否 1:是 QUOTEID int64 `json:"quoteid" xorm:"QUOTEID"` // 报价单ID GCACCOUNTID int64 `json:"gcaccountid" xorm:"GCACCOUNTID"` // 账户ID[合约币种] SESSIONID int64 `json:"sessionid" xorm:"SESSIONID"` // 会话ID FREEZEQTY float64 `json:"freezeqty" xorm:"FREEZEQTY"` // 冻结数量 AMOUNTFLAG int32 `json:"amountflag" xorm:"AMOUNTFLAG"` // 资金标识 - 1:余额 2;待付 ORDERFLAG int32 `json:"orderflag" xorm:"ORDERFLAG"` // 委托标识 - 1:按量 2:按金额 ORDERID string `json:"orderid" xorm:"ORDERID2"` // 委托单号(100+Unix秒时间戳(10位)+2位(MarketServiceID)+xxxx) TRADEDATE string `json:"tradedate" xorm:"TRADEDATE"` // 交易日(yyyyMMdd) BUILDTYPE int32 `json:"buildtype" xorm:"BUILDTYPE"` // 委托单据类型 - 1:建仓 2:平仓 3:先平后建 PREORDERID string `json:"preorderid" xorm:"PREORDERID"` // 关联预埋单号(止盈止损单时填写) CANCELORDERID int64 `json:"cancelorderid" xorm:"CANCELORDERID"` // 撤单单号(撤单时填写) RELATEDID string `json:"relatedid" xorm:"RELATEDID"` // 关联单号(交割单) MARKETID int32 `json:"marketid" xorm:"MARKETID"` // 市场ID GOODSID int32 `json:"goodsid" xorm:"GOODSID"` // 商品ID ACCOUNTID int64 `json:"accountid" xorm:"ACCOUNTID"` // 账户ID[报价币种] MEMBERUSERID int64 `json:"memberuserid" xorm:"MEMBERUSERID"` // 所属会员UserID BUYORSELL int32 `json:"buyorsell" xorm:"BUYORSELL"` // 买卖 - 0:买 1:卖 PRICEMODE int32 `json:"pricemode" xorm:"PRICEMODE"` // 取价方式 - 1:市价 2: 限价 ORDERPRICE float64 `json:"orderprice" xorm:"ORDERPRICE"` // 委托价格 MARKETMAXSUB float64 `json:"marketmaxsub" xorm:"MARKETMAXSUB"` // 市价最大偏移范围 ORDERQTY float64 `json:"orderqty" xorm:"ORDERQTY"` // 委托数量 TRADEQTY float64 `json:"tradeqty" xorm:"TRADEQTY"` // 成交数量 CANCELQTY float64 `json:"cancelqty" xorm:"CANCELQTY"` // 撤单数量 OPENQTY float64 `json:"openqty" xorm:"OPENQTY"` // 开仓数量(先建后平操作,需要记录) CLOSEQTY float64 `json:"closeqty" xorm:"CLOSEQTY"` // 平仓数量(先建后平操作 需要记录) OPENTRADEQTY float64 `json:"opentradeqty" xorm:"OPENTRADEQTY"` // 开仓成交数量(先建后平操作,需要记录) CLOSETRADEQTY float64 `json:"closetradeqty" xorm:"CLOSETRADEQTY"` // 平仓成交数量(先建后平操作,需要记录) FREEZEMARGIN float64 `json:"freezemargin" xorm:"FREEZEMARGIN"` // 冻结保证金(冻结交易金额) UNFREEZEMARGIN float64 `json:"unfreezemargin" xorm:"UNFREEZEMARGIN"` // 解冻保证金 FREEZECHARGE float64 `json:"freezecharge" xorm:"FREEZECHARGE"` // 冻结手续费 UNFREEZECHARGE float64 `json:"unfreezecharge" xorm:"UNFREEZECHARGE"` // 解冻手续费 OPENFREEZECHARGE float64 `json:"openfreezecharge" xorm:"OPENFREEZECHARGE"` // 开仓冻结手续费(先建后平操作,需要记录) CLOSEFREEZECHARGE float64 `json:"closefreezecharge" xorm:"CLOSEFREEZECHARGE"` // 平仓冻结手续费(先建后平操作,需要记录) OPENUNFREEZECHARGE float64 `json:"openunfreezecharge" xorm:"OPENUNFREEZECHARGE"` // 开仓解冻手续费(先建后平操作,需要记录) CLOSEUNFREEZECHARGE float64 `json:"closeunfreezecharge" xorm:"CLOSEUNFREEZECHARGE"` // 平仓解冻手续费(先建后平操作,需要记录) VALIDTYPE int32 `json:"validtype" xorm:"VALIDTYPE"` // 有效类型 - 1当日有效 2本周有效 3指定日期有效 4一直有效 5指定时间有效 VALIDTIME string `json:"validtime" xorm:"VALIDTIME"` // 有效期限 VOLUMETYPE int32 `json:"volumetype" xorm:"VOLUMETYPE"` // 当时间有效类型为 “立即执行否则取消 IOC” 时，需要此项 - 0:任意量 1:最小量（暂时不支持） 2:全部量 OPERATETYPE int32 `json:"operatetype" xorm:"OPERATETYPE"` // 操作类型 - 1:正常下单 2:斩仓 3:转单 4:结算撤单 5:系统卖出(适用于先平后建的卖出) 6:行情源报价 7:（结算)到期强平 8:（结算)协议转让 9:系统对冲单 10:（结算)到期无效 11:交割协议转让 12:交割协议平仓 13:交割成交(所有权) 14:管理端强行平仓 15:管理端协议转让 ... 23:融资买入 ORDERTIME string `json:"ordertime" xorm:"ORDERTIME2"` // 委托时间 ORDERSRC int32 `json:"ordersrc" xorm:"ORDERSRC"` // 委托来源 - 1:客户端 2:管理端 3:风控服务 4:交割服务 5:交易服务 6:交易日结 7:商品强平 8:管理端商品退市强平 9:交易接口 10:交割服务商被动(受托竞价) 11:预埋触发 ORDERSTATUS int32 `json:"orderstatus" xorm:"ORDERSTATUS"` // 委托状态 - 1： 委托请求 2：待冻结 3：委托成功 4： 委托失败 5：配对成功 6： 已撤销 7：部分成交 8：已成交 9：部成部撤 10：成交失败 11：已拒绝 12:经过摘牌(先摘后挂专用-先摘后挂已摘过) 13:冻结成功(通道交易专用) 14:通道已撤 15:通道部成部撤 16:成交失败违约(荷兰式竞拍专用) OPERATORID int64 `json:"operatorid" xorm:"OPERATORID"` // 登录账号(LoginID) UPDATETIME string `json:"updatetime" xorm:"UPDATETIME"` // 更新时间 CLIENTORDERTIME string `json:"clientordertime" xorm:"CLIENTORDERTIME"` // 客户端委托时间 CLIENTTICKET string `json:"clientticket" xorm:"CLIENTTICKET"` // 客户端流水号 UUID string `json:"uuid" xorm:"UUID"` // 发起端唯一id CLIENTTYPE int32 `json:"clienttype" xorm:"CLIENTTYPE"` // 客户端类型 - 0:保留为未填终端类型 1:PC管理端 2:PC交易端 3:手机客户端_安卓 4:网页客户端 5:微信客户端 6:手机客户端_苹果 7:网上开户客户端 8:无效终端编号 9:报价终端(中江) RETCODE int32 `json:"retcode" xorm:"RETCODE"` // 错误代码 TRADEPROPERTY int32 `json:"tradeproperty" xorm:"TRADEPROPERTY"` // 交易属性 LISTINGSELECTTYPE int32 `json:"listingselecttype" xorm:"LISTINGSELECTTYPE"` // 挂牌点选类型 - 1:挂牌 2:摘牌 3:先摘后挂 DELISTINGTYPE int32 `json:"delistingtype" xorm:"DELISTINGTYPE"` // 摘牌类型 - 1:价格最优 2:点选成交 MARGINALGORITHM int32 `json:"marginalgorithm" xorm:"MARGINALGORITHM"` // 保证金收取方式 1：比率 2：固定 MARGINVALUE float64 `json:"marginvalue" xorm:"MARGINVALUE"` // 即市保证金设置值 OPENFEEALGORITHM int32 `json:"openfeealgorithm" xorm:"OPENFEEALGORITHM"` // 建仓手续费收取方式 1：比率 2：固定 OPENMEMBERCHARGEVALUE float64 `json:"openmemberchargevalue" xorm:"OPENMEMBERCHARGEVALUE"` // 建仓会员手续费设置值 OPENEXCHAGECHARGEVALUE float64 `json:"openexchagechargevalue" xorm:"OPENEXCHAGECHARGEVALUE"` // 建仓交易所手续费设置值 CLOSEFEEALGORITHM int32 `json:"closefeealgorithm" xorm:"CLOSEFEEALGORITHM"` // 平仓手续费收取方式 1：比率 2：固定 QhjTradeEx `xorm:"extends"` IncludeSub int32 `json:"-"` // 是否包含子级 1-包含 BeginDate string `json:"-"` // 开始交易日 EndDate string `json:"-"` // 结束交易日 FilterName string `json:"-"` // 模糊搜索 } func (r *QhjMgrTradeOrderDetail) calc() { r.dealConvert() } func (r *QhjMgrTradeOrderDetail) buildSql(bQueryHis bool) string { var sqlId utils.SQLVal = "with k as" + " (select t.userid, wm_concat(t.logincode) logincode" + " from loginaccount t" + " group by t.userid)" + "select u.userid," + " u.accountname," + " k.logincode," + " u.subarealevelpath," + " ui.userinfotype," + " ui.mobile," + " g.goodsname," + " g.goodscode," + " g.goodunitid," + " g.qtydecimalplace," + " g.decimalplace," + " g.agreeunit," + " m.marketid," + " m.trademode," + " e.enumdicname," + " to_char(t.ORDERID) ORDERID2," + " to_char(t.ordertime, 'yyyy-mm-dd hh24:mi:ss') ORDERTIME2," + " t.*" + " from %v t" + " inner join taaccount ta" + " on t.accountid = ta.accountid" + " inner join useraccount u" + " on ta.relateduserid = u.userid" + " inner join userinfo ui" + " on u.userid = ui.userid" + " left join k" + " on u.userid = k.userid" + " left join goods g" + " on t.goodsid = g.goodsid" + " left join market m" + " on g.marketid = m.marketid" + " left join enumdicitem e" + " on e.enumitemname = g.goodunitid" + " and e.enumdiccode = 'goodsunit'" + " where 1 = 1" if bQueryHis { sqlId.FormatParam("his_trade_orderdetail") sqlId.And("t.isvaliddata", 1) } else { sqlId.FormatParam("trade_orderdetail") } if r.IncludeSub == 1 { sqlId.JoinFormat(" and u.subarealevelpath like '%%,%v,%%'", r.USERID) } else { sqlId.And("u.parentuserid", r.USERID) } sqlId.AndEx("ui.USERINFOTYPE", r.USERINFOTYPE, r.USERINFOTYPE > 0) sqlId.AndEx("m.TRADEMODE", r.TRADEMODE, r.TRADEMODE > 0) sqlId.AndEx("t.goodsid", r.GOODSID, r.GOODSID > 0) if len(r.BeginDate) > 0 { sqlId.JoinFormat(" and t.TRADEDATE >=%v", r.BeginDate) } if len(r.EndDate) > 0 { sqlId.JoinFormat(" and t.TRADEDATE <=%v", r.EndDate) } if len(r.FilterName) > 0 { sqlId.JoinFormat(" and (k.logincode like '%%%v%%' or u.accountname like '%%%v%%')", r.FilterName, r.FilterName) } return sqlId.String() } // GetDataEx 获取委托明细 func (r *QhjMgrTradeOrderDetail) GetDataEx() (interface{}, error) { sData := make([]QhjMgrTradeOrderDetail, 0) sCurData := make([]QhjMgrTradeOrderDetail, 0) err := db.GetEngine().SQL(r.buildSql(false)).Find(&sCurData) sHisData := make([]QhjMgrTradeOrderDetail, 0) err = db.GetEngine().SQL(r.buildSql(true)).Find(&sHisData) sData2 := make([]QhjMgrTradeOrderDetailRsp, 0) sData = append(sData, sCurData...) sData = append(sData, sHisData...) for i := range sData { sData[i].calc() v := QhjMgrTradeOrderDetailRsp{QhjTradeEx: sData[i].QhjTradeEx} v.ACCOUNTID, v.ORDERID = sData[i].ACCOUNTID, sData[i].ORDERID v.ORDERPRICE, v.ORDERQTY = sData[i].ORDERPRICE, sData[i].ORDERQTY v.TRADEDATE, v.ORDERTIME = sData[i].TRADEDATE, sData[i].ORDERTIME v.GOODSID, v.ORDERSTATUS = sData[i].GOODSID, sData[i].ORDERSTATUS zoonFun := NewZoomFun(int(v.QTYDECIMALPLACE)) zoonFun(&v.ORDERQTY) v.AMOUNT = v.ORDERPRICE * v.ORDERQTY * v.AGREEUNIT sData2 = append(sData2, v) } return sData2, err } // QhjMgrTradePayOrder 待付款单据 type QhjMgrTradePayOrder struct { TRADEID string `json:"tradeid" xorm:"TRADEID"` // 成交单号(101+Unix秒时间戳(10位)+2位(MarketServiceID)+xxxx) TRADEDATE string `json:"tradedate" xorm:"TRADEDATE"` // 交易日(yyyyMMdd) MARKETID int32 `json:"marketid" xorm:"MARKETID"` // 市场ID GOODSID int32 `json:"goodsid" xorm:"GOODSID"` // 商品ID BUYORDERID string `json:"buyorderid" xorm:"BUYORDERID"` // 买方委托单号(关联单号) BUYACCOUNTID int64 `json:"buyaccountid" xorm:"BUYACCOUNTID"` // 买方账号ID[报价币种] SELLORDERID string `json:"sellorderid" xorm:"SELLORDERID"` // 卖方委托单号 SELLACCOUNTID int64 `json:"sellaccountid" xorm:"SELLACCOUNTID"` // 卖方账号ID[报价币种] TRADEAMOUNT float64 `json:"tradeamount" xorm:"TRADEAMOUNT"` // 成交金额(优惠后金额) TRADECHARGE float64 `json:"tradecharge" xorm:"TRADECHARGE"` // 成交手续费(买方) PAYFLAG int32 `json:"payflag" xorm:"PAYFLAG"` // 付款标识 - 1:未支付 2:已支付 3:已过期 4:已撤销 5:结算过期 6:预售终止 CREATETIME string `json:"createtime" xorm:"CREATETIME2"` // 创建时间 PAYTIME string `json:"paytime" xorm:"PAYTIME2"` // 付款时间 PAYLIMITEDTIME string `json:"paylimitedtime" xorm:"PAYLIMITEDTIME2"` // 支付期限 OFFAMOUNT float64 `json:"offamount" xorm:"OFFAMOUNT"` // 优惠金额 TRADEPRICE float64 `json:"tradeprice" xorm:"TRADEPRICE"` // 成交价格(价格) TRADEQTY float64 `json:"tradeqty" xorm:"TRADEQTY"` // 成交数量(数量) PAYAMOUNT float64 `json:"payamount" xorm:"PAYAMOUNT"` // 支付金额(金额) OPERATETYPE int32 `json:"operatetype" xorm:"OPERATETYPE"` // 买委托操作类型 - 1:正常下单 16:融资买入 ADVANCERATIO float64 `json:"advanceratio" xorm:"ADVANCERATIO"` // 首付比率 TRADETIME string `json:"tradetime"` // 成交时间 QhjTradeEx `xorm:"extends"` IncludeSub int32 `json:"-"` // 是否包含子级 1-包含 BeginDate string `json:"-"` // 开始交易日 EndDate string `json:"-"` // 结束交易日 FilterName string `json:"-"` // 模糊搜索 FilterPayFlag string `json:"-"` // 付款标识 } func (r *QhjMgrTradePayOrder) calc() { ZoomByDecimal(int(r.QTYDECIMALPLACE), &r.TRADEQTY) r.TRADETIME = r.CREATETIME r.dealConvert() } func (r *QhjMgrTradePayOrder) buildSql() string { var sqlId utils.SQLVal = "with k as" + " (select t.userid, wm_concat(t.logincode) logincode" + " from loginaccount t" + " group by t.userid)" + "select u.userid," + " u.accountname," + " k.logincode," + " u.subarealevelpath," + " ui.userinfotype," + " ui.mobile," + " g.goodsname," + " g.goodscode," + " g.goodunitid," + " g.qtydecimalplace," + " g.decimalplace," + " g.agreeunit," + " m.marketid," + " m.trademode," + " e.enumdicname," + " to_char(t.BUYORDERID) BUYORDERID2," + " to_char(t.TRADEID) TRADEID2," + " to_char(t.Paylimitedtime, 'yyyy-mm-dd hh24:mi:ss') Paylimitedtime2," + " to_char(t.PAYTIME, 'yyyy-mm-dd hh24:mi:ss') PAYTIME2," + " to_char(t.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') CREATETIME2," + " t.*" + " from TRADE_PAYORDER t" + " inner join taaccount ta" + " on t.buyaccountid = ta.accountid" + " inner join useraccount u" + " on ta.relateduserid = u.userid" + " inner join userinfo ui" + " on u.userid = ui.userid" + " left join k" + " on u.userid = k.userid" + " left join goods g" + " on t.goodsid = g.goodsid" + " left join market m" + " on g.marketid = m.marketid" + " left join enumdicitem e" + " on e.enumitemname = g.goodunitid" + " and e.enumdiccode = 'goodsunit'" + " where 1 = 1" if r.IncludeSub == 1 { sqlId.JoinFormat(" and u.subarealevelpath like '%%,%v,%%'", r.USERID) } else { sqlId.And("u.parentuserid", r.USERID) } sqlId.AndEx("ui.USERINFOTYPE", r.USERINFOTYPE, r.USERINFOTYPE > 0) sqlId.AndEx("m.TRADEMODE", r.TRADEMODE, r.TRADEMODE > 0) sqlId.AndEx("t.goodsid", r.GOODSID, r.GOODSID > 0) if len(r.FilterPayFlag) > 0 { sqlId.JoinFormat(" and t.PAYFLAG in(%v)", r.FilterPayFlag) } if len(r.BeginDate) > 0 { sqlId.JoinFormat(" and t.TRADEDATE >=%v", r.BeginDate) } if len(r.EndDate) > 0 { sqlId.JoinFormat(" and t.TRADEDATE <=%v", r.EndDate) } if len(r.FilterName) > 0 { sqlId.JoinFormat(" and (k.logincode like '%%%v%%' or u.accountname like '%%%v%%')", r.FilterName, r.FilterName) } return sqlId.String() } // GetDataEx 获取待付款单据 func (r *QhjMgrTradePayOrder) GetDataEx() (interface{}, error) { sData := make([]QhjMgrTradePayOrder, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].calc() } return sData, err } // QhjMgrContract 千海金融资明细(合同) type QhjMgrContract struct { USERID int64 `json:"userid" xorm:"USERID"` // 用户ID SCFCONTRACTID string `json:"scfcontractid" xorm:"SCFCONTRACTID"` // SCF合同ID(310+Unix秒时间戳(10位)+xxxxxx) SCFCONTRACTTYPE int32 `json:"scfcontracttype" xorm:"SCFCONTRACTTYPE"` // 合同类型 - 1:仓单回购 2:仓单质押 3:合作套保 4:资金贷款 5:融资单 GOODSID int32 `json:"goodsid" xorm:"GOODSID"` // 商品ID [5:融资单] CONTRACTCONFIRMTIME string `json:"contractconfirmtime" xorm:"CONTRACTCONFIRMTIME"` // 合同确认时间(时间) WRQTY float64 `json:"wrqty" xorm:"WRQTY"` // 仓单数量(数量) TRADEPRICE float64 `json:"tradeprice" xorm:"TRADEPRICE"` // 成交价格(成本价格) LENDERAMOUNT float64 `json:"lenderamount" xorm:"LENDERAMOUNT"` // 贷款金额(总额|订单总额) MARGINRATIO float64 `json:"marginratio" xorm:"MARGINRATIO"` // 保证金比率(首付比例) INITMARGIN float64 `json:"initmargin" xorm:"'INITMARGIN'"` // 初始保证金(首付款) RECOVEREDMARGIN float64 `json:"recoveredmargin" xorm:"'RECOVEREDMARGIN'"` // 已追缴保证金 REMAINAMOUNT float64 `json:"remainamount" xorm:"'REMAINAMOUNT'"` // 合同剩余金额(融资额) TOTALINTEREST float64 `json:"totalinterest" xorm:"'TOTALINTEREST'"` // 已计总利息(利息) GOODSCODE string `json:"goodscode" xorm:"'GOODSCODE'"` // 商品代码 GOODSNAME string `json:"goodsname" xorm:"'GOODSNAME'"` // 商品名称 GOODUNITID int32 `json:"goodunitid" xorm:"'GOODUNITID'"` // 商品单位id CURRISKLEVEL int32 `json:"currisklevel" xorm:"'CURRISKLEVEL'"` // 风险级别 - 1:正常 2:追加货款 3:支付尾款 RECKONRECOVERMARGIN float64 `json:"reckonrecovermargin" xorm:"'RECKONRECOVERMARGIN'"` // 结算应追缴保底金(结算时到过风险率时临时存储) - [仓单回购\仓单质押] DECIMALPLACE int32 `json:"decimalplace" xorm:"'DECIMALPLACE'"` // 商品报价小数位 SCFCONTRACTSTATUS int32 `json:"scfcontractstatus" xorm:"'SCFCONTRACTSTATUS'"` // 合同状态 - 1:待确认 2:已确认 3:确认拒绝 4:已支付保证金 5:已激活 6:已违约 7:到期结束 8:延期结束 9:违约结束 10:已注销 11:已关闭 99:未提交 LOGINCODE string `json:"logincode" xorm:"'LOGINCODE'"` // 账号(登录代码) USERINFOTYPE int32 `json:"userinfotype" xorm:"'USERINFOTYPE'"` // 用户类型 1-个人 2-企业 MOBILE string `json:"mobile" xorm:"'MOBILE'"` // 电话号码(用户资料里的电话) TRADEDATE string `json:"tradedate" xorm:"'TRADEDATE'"` // 交易日 PAYAMOUNT float64 `json:"payamount" xorm:"'PAYAMOUNT'"` // 已付(已付货款) USERNAME string `json:"username" xorm:"'USERNAME'"` // 用户名称 ENUMDICNAME string `json:"enumdicname"` // 单位名称 BeginDate string `json:"-"` // 开始交易日 EndDate string `json:"-"` // 结束交易日 FilterName string `json:"-"` // 模糊搜索 FilterRiskLevel string `json:"-"` // 风险级别 } func (r *QhjMgrContract) calc() { r.USERNAME = mtpcache.GetUserNameByUserId(r.USERID) r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.GOODUNITID) // 已付(已付货款) = 已追缴保证金 + 初始保证金 r.PAYAMOUNT = r.RECOVEREDMARGIN + r.INITMARGIN if r.SCFCONTRACTSTATUS == 10 || r.SCFCONTRACTSTATUS == 11 { // 当合同关闭时, 利息重置为0 r.TOTALINTEREST = 0 } if r.LOGINCODE == "" { r.LOGINCODE = DecryptField(r.MOBILE) } } func (r *QhjMgrContract) buildSql() string { var sqlId utils.SQLVal = "with k as (select t.userid, wm_concat(t.logincode) logincode from loginaccount t group by t.userid)" + "select t.borroweruserid userid," + " to_char(t.scfcontractid) scfcontractid," + " t.scfcontracttype," + " t.goodsid," + " to_char(t.contractconfirmtime, 'yyyy-mm-dd hh24:mi:ss') contractconfirmtime," + " t.wrqty," + " t.tradeprice," + " t.lenderamount," + " t.marginratio," + " s.initmargin," + " s.recoveredmargin," + " s.remainamount," + " s.totalinterest," + " s.currisklevel," + " s.reckonrecovermargin," + " s.scfcontractstatus," + " s.tradedate," + " g.goodscode," + " g.goodsname," + " g.decimalplace," + " g.goodunitid," + " ui.userinfotype," + " ui.mobile," + " u.accountname," + " k.logincode" + " from scf_contract t" + " left join scf_contractinfo s" + " on t.scfcontractid = s.scfcontractid" + " left join goods g" + " on t.goodsid = g.goodsid" + " left join userinfo ui on t.borroweruserid=ui.userid" + " left join k on ui.userid=k.userid" + " left join useraccount u on t.borroweruserid=u.userid" + " where 1 = 1" sqlId.AndEx("t.borroweruserid", r.USERID, r.USERID > 0) sqlId.AndEx("ui.USERINFOTYPE", r.USERINFOTYPE, r.USERINFOTYPE > 0) sqlId.AndEx("t.goodsid", r.GOODSID, r.GOODSID > 0) if len(r.FilterRiskLevel) > 0 { sqlId.JoinFormat(" and s.currisklevel in(%v)", r.FilterRiskLevel) } if len(r.BeginDate) > 0 { sqlId.JoinFormat(" and s.TRADEDATE >=%v", r.BeginDate) } if len(r.EndDate) > 0 { sqlId.JoinFormat(" and s.TRADEDATE <=%v", r.EndDate) } if len(r.FilterName) > 0 { sqlId.JoinFormat(" and (k.logincode like '%%%v%%' or u.accountname like '%%%v%%')", r.FilterName, r.FilterName) } return sqlId.String() } // GetDataEx 获取融资明细(合同) func (r *QhjMgrContract) GetDataEx() (interface{}, error) { sData := make([]QhjMgrContract, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].calc() } return sData, err } // QhjMgrPickGoods 提货商品 type QhjMgrPickGoods struct { PICKUPGOODSID string `json:"pickupgoodsid" xorm:"PICKUPGOODSID"` // 提货商品ID PICKUPGOODSNAME string `json:"pickupgoodsname" xorm:"PICKUPGOODSNAME"` // 提货商品名称 PICKUPGOODSUNITID int32 `json:"pickupgoodsunitid" xorm:"PICKUPGOODSUNITID"` // 单位ID PICKUPGOODSAGREEUNIT float64 `json:"pickupgoodsagreeunit" xorm:"PICKUPGOODSAGREEUNIT"` // 合约乘数 PICKUPGOODSDESC string `json:"pickupgoodsdesc" xorm:"PICKUPGOODSDESC"` // 描述 IMAGEADDRESS string `json:"imageaddress" xorm:"IMAGEADDRESS"` // 图片 REMARK string `json:"remark" xorm:"REMARK"` // 备注 MODIFYSRC int32 `json:"modifysrc" xorm:"MODIFYSRC"` // 修改来源 - 1:管理端 2:终端 MODIFIERID int64 `json:"modifierid" xorm:"MODIFIERID"` // 修改人 UPDATETIME string `json:"updatetime" xorm:"UPDATETIME"` // 更新时间 PREPAREDAYS int32 `json:"preparedays" xorm:"PREPAREDAYS"` // 备货期(天) PICKUPGOODSSTATUS int32 `json:"pickupgoodsstatus" xorm:"PICKUPGOODSSTATUS"` // 状态 - 1:正常 2:停用 3:注销 GOODSID int32 `json:"goodsid" xorm:"'GOODSID'"` // 商品id PICKUPRATIO float64 `json:"pickupratio" xorm:"'PICKUPRATIO'"` // 提货系数 MODIFIERNAME string `json:"modifiername"` // 修改人名称 FilterStatus string `json:"-"` // 状态过淲 ENUMDICNAME string `json:"enumdicname"` // 单位名称(提货商品) } func (r *QhjMgrPickGoods) calc() { if r.MODIFYSRC == 1 { r.MODIFIERNAME = mtpcache.GetSystemmangerLoginCode(r.MODIFIERID) } else { r.MODIFIERNAME = mtpcache.GetLoginCodeByLoginId(r.MODIFIERID) } if r.MODIFIERNAME == "" { r.MODIFIERNAME = mtpcache.GetUserNameByUserId(r.MODIFIERID) } r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.PICKUPGOODSUNITID) } func (r *QhjMgrPickGoods) buildSql() string { var sqlId utils.SQLVal = "SELECT t.PICKUPGOODSID," + " t.PICKUPGOODSNAME," + " t.PICKUPGOODSUNITID," + " t.PICKUPGOODSAGREEUNIT," + " to_char(t.PICKUPGOODSDESC) PICKUPGOODSDESC," + " to_char(t.IMAGEADDRESS) IMAGEADDRESS," + " t.REMARK," + " t.MODIFYSRC," + " t.MODIFIERID," + " to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," + " t.PREPAREDAYS," + " t.PICKUPGOODSSTATUS," + " FROM PICKUPGOODS t" + " WHERE 1 = 1" if len(r.FilterStatus) > 0 { sqlId.Join(fmt.Sprintf(" and t.PICKUPGOODSSTATUS in(%v)", r.FilterStatus)) } return sqlId.String() } // GetDataEx 获取提货商品 func (r *QhjMgrPickGoods) GetDataEx() (interface{}, error) { sData := make([]QhjMgrPickGoods, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].calc() } return sData, err } // 经纪人扩展信息 type BrokerExInfo struct { USERNAME string `json:"username" xorm:"'USERNAME'"` // 用户名称 PARENTUSERID int64 `json:"parentuserid" xorm:"'PARENTUSERID'"` // 所属机构id USERINFOTYPE int32 `json:"userinfotype" xorm:"'USERINFOTYPE'"` // 用户类型 1-个人 2-企业 MOBILE string `json:"mobile" xorm:"'MOBILE'"` // 手机号码 PARENTUSERNAME string `json:"parentusername" xorm:"'PARENTUSERNAME'"` // 所属机构名称 LOGINCODE string `json:"logincode" xorm:"'LOGINCODE'"` // 登录代码 SUBAREALEVELPATH string `json:"subarealevelpath" xorm:"'SUBAREALEVELPATH'"` // 层级路径 REFEREEUSERID int64 `json:"refereeuserid" xorm:"'REFEREEUSERID'"` // 推荐人id(经纪人) REFERNUM string `json:"refernum" xorm:"'REFERNUM'"` // 推荐码 } // QhjMgrBrokerApply 经济人申请表 type QhjMgrBrokerApply struct { APPLYSRC int32 `json:"applysrc" xorm:"APPLYSRC" form:"applysrc"` // 申请来源 - 1:管理端 2:终端 APPLYSTATUS int32 `json:"applystatus" xorm:"APPLYSTATUS" form:"applystatus"` // 申请状态 - 1:待审核 2:审核通过 3:审核拒绝 4:处理失败 5:已撤回 AUDITSRC int32 `json:"auditsrc" xorm:"AUDITSRC" form:"auditsrc"` // 审核来源 - 1:管理端 2:终端 APPLICANTID int64 `json:"applicantid" xorm:"APPLICANTID" form:"applicantid"` // 申请人 AUDITORID int64 `json:"auditorid" xorm:"AUDITORID" form:"auditorid"` // 审核人 BROKERAPPLYID int64 `json:"brokerapplyid" xorm:"BROKERAPPLYID" form:"brokerapplyid"` // 申请ID(SEQ_BROKERAPPLY) USERID int64 `json:"userid" xorm:"USERID" form:"userid"` // 申请人用户ID APPLYTIME string `json:"applytime" xorm:"APPLYTIME" form:"applytime"` // 申请时间 AUDITREMARK string `json:"auditremark" xorm:"AUDITREMARK" form:"auditremark"` // 审核备注 AUDITTIME string `json:"audittime" xorm:"AUDITTIME" form:"audittime"` // 审核时间 AUDITTRADEDATE string `json:"audittradedate" xorm:"AUDITTRADEDATE" form:"audittradedate"` // 审核交易日(yyyyMMdd) CARDBACKPHOTOURL string `json:"cardbackphotourl" xorm:"CARDBACKPHOTOURL" form:"cardbackphotourl"` // 证件背面图片地址 CARDFRONTPHOTOURL string `json:"cardfrontphotourl" xorm:"CARDFRONTPHOTOURL" form:"cardfrontphotourl"` // 证件正面图片地址 CARDNUM string `json:"cardnum" xorm:"CARDNUM" form:"cardnum"` // 证件号码(加密存储) 注:数据库没存证件类型字段,产品说类型固定为身份证 CUSTOMERNAME string `json:"customername" xorm:"CUSTOMERNAME" form:"customername"` // 姓名 REMARK string `json:"remark" xorm:"REMARK" form:"remark"` // 备注 TRADEDATE string `json:"tradedate" xorm:"TRADEDATE" form:"tradedate"` // 交易日(yyyyMMdd) BrokerExInfo `xorm:"extends"` IncludeSub int32 `json:"-" form:"includesub"` // 是否包含子级 1-包含 FilterName string `json:"-" form:"filtername"` // 模糊搜索名称 FilterStatus string `json:"filterstatus" form:"filterstatus"` // 筛选条件 } func (r *QhjMgrBrokerApply) calc() { r.MOBILE = DecryptField(r.MOBILE) r.CARDNUM = DecryptField(r.CARDNUM) if r.LOGINCODE == "" { r.LOGINCODE = r.MOBILE } } func (r *QhjMgrBrokerApply) buildSql() string { var sqlId utils.SQLVal = "with k as (select t.userid, wm_concat(t.logincode) logincode from loginaccount t group by t.userid)" + "SELECT t.CUSTOMERNAME," + " t.CARDNUM," + " t.CARDFRONTPHOTOURL," + " t.CARDBACKPHOTOURL," + " t.BROKERAPPLYID," + " t.USERID," + " t.TRADEDATE," + " t.APPLYSRC," + " t.APPLICANTID," + " t.REMARK," + " to_char(t.APPLYTIME, 'yyyy-mm-dd hh24:mi:ss') APPLYTIME," + " t.APPLYSTATUS," + " t.AUDITTRADEDATE," + " t.AUDITORID," + " t.AUDITSRC," + " to_char(t.AUDITTIME, 'yyyy-mm-dd hh24:mi:ss') AUDITTIME," + " t.AUDITREMARK," + " u.accountname USERNAME," + " u.parentuserid," + " u.refereeuserid," + " u.refernum," + " ui.userinfotype," + " ui.mobile," + " u2.accountname PARENTUSERNAME," + " k.logincode," + " u.subarealevelpath" + " FROM BROKERAPPLY t" + " INNER JOIN USERACCOUNT u on t.userid=u.userid" + " INNER JOIN USERINFO ui on t.userid=ui.userid" + " LEFT JOIN k on t.userid=k.userid" + " LEFT JOIN USERACCOUNT u2 on u.parentuserid=u2.userid" + " WHERE 1 = 1" if r.IncludeSub == 1 { sqlId.JoinFormat(" and u.subarealevelpath like '%%,%v,%%,%%'", r.USERID) } else { sqlId.And("u.parentuserid", r.USERID) } sqlId.JoinEx(r.FilterStatus != "", fmt.Sprintf(" and t.APPLYSTATUS in(%v)", r.FilterStatus)) sqlId.AndEx("ui.USERINFOTYPE", r.USERINFOTYPE, r.USERINFOTYPE > 0) if len(r.FilterName) > 0 { sqlId.JoinFormat(" and (k.logincode like '%%%v%%' or t.CUSTOMERNAME like '%%%v%%')", r.FilterName, r.FilterName) } return sqlId.String() } // GetDataEx 获取经济人申请表 func (r *QhjMgrBrokerApply) GetDataEx() (interface{}, error) { sData := make([]QhjMgrBrokerApply, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].calc() } return sData, err } // QhjMgrBrokerRewardReport 经纪人奖励报表 type QhjMgrBrokerRewardReport struct { CYCLETIME string `json:"cycletime" xorm:"CYCLETIME" form:"cycletime"` // 周期时间 日(YYYYMMDD) 月(YYYYMM) 季(YYYYQ) 年(YYYY) 周(YYYYMMDD) 全(0)【原值】 CYCLETYPE int32 `json:"cycletype" xorm:"CYCLETYPE" form:"cycletype"` // 周期类型 - 0:日 1:月 2:季 3:年 4:周 5:全报表【原值】 USERID int64 `json:"userid" xorm:"USERID" form:"userid"` // 经纪人用户ID DIRECTTOTALNUM int32 `json:"directtotalnum" xorm:"DIRECTTOTALNUM" form:"directtotalnum"` // 直推总人数【期末】 INDIRECTTOTALNUM int32 `json:"indirecttotalnum" xorm:"INDIRECTTOTALNUM" form:"indirecttotalnum"` // 间推总人数【期末】 TODAYREWARDAMOUNT float64 `json:"todayrewardamount" xorm:"TODAYREWARDAMOUNT" form:"todayrewardamount"` // 奖励总金额【汇总】 UPDATETIME string `json:"updatetime" xorm:"UPDATETIME" form:"updatetime"` // 更新时间 USERNAME string `json:"username" xorm:"'USERNAME'"` // 用户名称 REFEREEUSERID int64 `json:"refereeuserid" xorm:"'REFEREEUSERID'"` // 推荐人id(经纪人id) REFERNUM string `json:"refernum" xorm:"'REFERNUM'"` // 推荐码 SUBAREALEVELPATH string `json:"subarealevelpath" xorm:"'SUBAREALEVELPATH'"` // 用户层级路径 CREATETIME string `json:"createtime" xorm:"'CREATETIME'"` // 创建时间(注册时间) USERINFOTYPE int32 `json:"userinfotype" xorm:"'USERINFOTYPE'"` // 用户类型 1-个人 2-企业 MOBILE string `json:"mobile" xorm:"'MOBILE'"` // 电话号码 REFEREEUSERNAME string `json:"refereeusername" xorm:"'REFEREEUSERNAME'"` // 推荐人名称(经纪人) 上级经纪人名称 IncludeSub int32 `json:"-" form:"includesub"` // 是否包含子级 1-包含 FilterName string `json:"-" form:"filtername"` // 模糊搜索名称 } func (r *QhjMgrBrokerRewardReport) calc() { r.MOBILE = DecryptField(r.MOBILE) } func (r *QhjMgrBrokerRewardReport) sqlDay() string { var sqlId utils.SQLVal = "SELECT t.reckondate as CYCLETIME," + " 0 as CYCLETYPE," + " t.USERID," + " t.DIRECTTOTALNUM," + " t.INDIRECTTOTALNUM," + " t.TODAYREWARDAMOUNT," + " to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," + " u.accountname USERNAME," + " u.refereeuserid," + " u.refernum," + " u.subarealevelpath," + " to_char(u.createtime, 'yyyy-mm-dd hh24:mi:ss') CREATETIME," + " ui.userinfotype," + " ui.mobile," + " u2.accountname REFEREEUSERNAME" + " FROM RECKON_BROKERREWARD t" + " INNER JOIN USERACCOUNT u on t.userid=u.userid" + " INNER JOIN USERINFO ui on t.userid=ui.userid" + " LEFT JOIN USERACCOUNT u2 on u.refereeuserid=u2.userid" + " WHERE 1 = 1" return sqlId.String() } func (r *QhjMgrBrokerRewardReport) sqlCycle() string { var sqlId utils.SQLVal = "SELECT t.CYCLETIME," + " t.CYCLETYPE," + " t.USERID," + " t.DIRECTTOTALNUM," + " t.INDIRECTTOTALNUM," + " t.TODAYREWARDAMOUNT," + " to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," + " u.accountname USERNAME," + " u.refereeuserid," + " u.refernum," + " u.subarealevelpath," + " to_char(u.createtime, 'yyyy-mm-dd hh24:mi:ss') CREATETIME," + " ui.userinfotype," + " ui.mobile," + " u2.accountname REFEREEUSERNAME" + " FROM REPORT_BROKERREWARD t" + " INNER JOIN USERACCOUNT u on t.userid=u.userid" + " INNER JOIN USERINFO ui on t.userid=ui.userid" + " LEFT JOIN USERACCOUNT u2 on u.refereeuserid=u2.userid" + " WHERE 1 = 1" return sqlId.String() } func (r *QhjMgrBrokerRewardReport) buildSql() string { var sqlId utils.SQLVal if r.CYCLETYPE == 0 { sqlId = utils.SQLVal(r.sqlDay()) sqlId.AndEx("t.reckondate", r.CYCLETIME, r.CYCLETIME != "") } else { sqlId = utils.SQLVal(r.sqlCycle()) sqlId.And("t.CYCLETYPE", r.CYCLETYPE) // 转换时间 // 兼容性处理, 月报表日期如果是yyyymmdd, 只取yyyymm if r.CYCLETYPE == 1 && len(r.CYCLETIME) > 6 { r.CYCLETIME = r.CYCLETIME[:6] } else if r.CYCLETYPE == 4 { r.CYCLETIME = mtpcache.GetWeekIW(r.CYCLETIME) } sqlId.And("t.CYCLETIME", r.CYCLETIME) } if r.IncludeSub == 1 { //sqlId.JoinFormat(" and u.subarealevelpath like '%%,%v,%%,%%'", r.USERID) // 要用 refereeuserid 递归查询, 不能用subarealevelpath } else { sqlId.And("u.refereeuserid", r.USERID) } if len(r.FilterName) > 0 { sqlId.JoinFormat(" and u.accountname like '%%%v%%'", r.FilterName) } return sqlId.String() } // GetDataEx 获取经纪人奖励报表 func (r *QhjMgrBrokerRewardReport) GetDataEx() (interface{}, error) { sData := make([]QhjMgrBrokerRewardReport, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].calc() } return sData, err }