/** * @Author: zou.yingbin * @Create : 2021/4/16 18:22 * @Modify : 2021/4/16 18:22 */ package models import ( "fmt" "mtp2_if/db" "mtp2_if/mtpcache" "mtp2_if/utils" "strings" ) // Ermcp3ReportOPLog 报表合同操作记录通用查询 type Ermcp3ReportOPLog struct { LOGID string `json:"logid" xorm:"'lOGID'"` // 流水ID(604+Unix秒时间戳(10位)+xxxxxx) BIZTYPE int32 `json:"biztype" xorm:"'BIZTYPE'"` // 业务类型 - 1:套保计划 2:现货合同 OPERATELOGTYPE int32 `json:"operatelogtype" xorm:"'OPERATELOGTYPE'"` // 操作流水类型 - RELATEDID string `json:"relatedid" xorm:"'RELATEDID'"` // 现货合同ID\套保计划 LOGVALUE string `json:"logvalue" xorm:"'LOGVALUE'"` // 数值 LOGDATETIME string `json:"logdatetime" xorm:"'LOGDATETIME'"` // 流水日期(时间) TRADEDATE string `json:"tradedate" xorm:"'TRADEDATE'"` // 交易日(yyyyMMdd) APPLYID int64 `json:"applyid" xorm:"'APPLYID'"` // 操作人 CONTRACTTYPE int32 `json:"contracttype" xorm:"'CONTRACTTYPE'"` // 现货合同类型 - 1:采购 -1:销售 USERID int64 `json:"userid" xorm:"'USERID'"` // 机构ID RELATEDNO string `json:"relatedno" xorm:"'RELATEDNO'"` // 合同编号 UNITID int32 `json:"-" xorm:"'UNITID'"` // 现货商品单位id ENUMDICNAME string `json:"enumdicName"` // 单位名称 OPTYPENAME string `json:"optypename"` // 流水类型名称 LOGTYPENAME string `json:"logtypename"` // 合同类型(名称) APPLYNAME string `json:"applyname"` // 操作人名称 BRANDNAME string `json:"brandname" xorm:"'brandname'"` // 品牌名称 DELIVERYGOODSID int32 `json:"deliverygoodsid" xorm:"'DELIVERYGOODSID'"` // 现货品种id DELIVERYGOODSCODE string `json:"deliverygoodscode" xorm:"'DELIVERYGOODSCODE'"` // 现货品种代码 DELIVERYGOODSNAME string `json:"deliverygoodsname" xorm:"'DELIVERYGOODSNAME'"` // 现货品种名称 WRSTANDARDID int32 `json:"wrstandardid" xorm:"'wrstandardid'"` // 品类id WRSTANDARDCODE string `json:"wrstandardcode" xorm:"'wrstandardcode'"` // 品类代码 WRSTANDARDNAME string `json:"wrstandardname" xorm:"'wrstandardname'"` // 品类名称 SPOTGOODSBRANDID int32 `json:"spotgoodsbrandid" xorm:"'SPOTGOODSBRANDID'"` // 品牌id LogTypeFilter string `json:"-"` // 查询日志类型, 逗号隔开(如 1,2,4) } // Calc 处理数据 func (r *Ermcp3ReportOPLog) Calc() { r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID) r.APPLYNAME = mtpcache.GetUserNameByUserId(r.APPLYID) if r.APPLYNAME == "" { // APPLYID 可能存的是loginId r.APPLYNAME = mtpcache.GetLoginCodeByLoginId(r.APPLYID) } //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:销售出库 26:生产入库 27:生产出库 // 数据库注释与返回值映身关系： 结算量->确定量, 收款->收款金额, 退款->退款金额, 付款->付款金额, 收票->收票金额, 开票->开票金额 sDes := []string{"点价价格", "点价数量", "确定量", "其它费用", "追加保证金", "溢短金额", "调整金额", "付款", "收款", "退款", "收票", "开票", "提交审核(合同)", "审核通过(合同)", "审核拒绝(合同)", "合同撤回", "提交审核(计划)", "审核通过(计划)", "审核拒绝(计划)", "计划撤回", "正常完结(合同)", "异常终止(合同)", "退还保证金", "采购入库", "销售出库", "生产入库", "生产出库"} if r.OPERATELOGTYPE >= 1 && r.OPERATELOGTYPE <= 27 { r.OPTYPENAME = sDes[r.OPERATELOGTYPE-1] } // 收款款项:采购合同的退款、销售合同的收款应用正数显示，付款款项：采购合同的付款、销售合同的退款应用负数显示 if r.CONTRACTTYPE == 1 { r.LOGTYPENAME = "采购" // 采购合同/退款 正数 if r.OPERATELOGTYPE == 10 && len(r.LOGVALUE) > 0 { if r.LOGVALUE[0] == '-' { r.LOGVALUE = r.LOGVALUE[1:] } } // 采购合同/付款 负数 if r.OPERATELOGTYPE == 8 && len(r.LOGVALUE) > 0 { if r.LOGVALUE[0] != '-' { r.LOGVALUE = "-" + r.LOGVALUE } } } else if r.CONTRACTTYPE == -1 { r.LOGTYPENAME = "销售" // 销售合同/收款 正数 if r.OPERATELOGTYPE == 9 && len(r.LOGVALUE) > 0 { if r.LOGVALUE[0] == '-' { r.LOGVALUE = r.LOGVALUE[1:] } } // 销售合同/退款 负数 if r.OPERATELOGTYPE == 10 && len(r.LOGVALUE) > 0 { if r.LOGVALUE[0] != '-' { r.LOGVALUE = "-" + r.LOGVALUE } } } // 去除多余的0,最后4个0 if r.LOGVALUE[len(r.LOGVALUE)-4:] == "0000" { r.LOGVALUE = r.LOGVALUE[:len(r.LOGVALUE)-4] } if r.BIZTYPE == 1 { r.LOGTYPENAME += "计划" } else if r.BIZTYPE == 2 { r.LOGTYPENAME += "合同" } } func (r *Ermcp3ReportOPLog) buildSql() string { var sqlId utils.SQLVal = "SELECT to_char(t.LOGID) LOGID," + " t.BIZTYPE," + " t.OPERATELOGTYPE," + " to_char(t.RELATEDID) RELATEDID," + " t.LOGVALUE," + " to_char(t.LOGDATETIME, 'yyyy-mm-dd hh24:mi:ss') LOGDATETIME," + " t.TRADEDATE," + " t.APPLYID," + " t.CONTRACTTYPE," + " t.USERID," + " t.wrstandardid," + " s.contractno relatedno," + " s.spotgoodsbrandid," + " g.deliverygoodsid," + " g.deliverygoodscode," + " g.deliverygoodsname," + " g.goodsunitid unitid," + " gb.dgfactoryitemvalue brandname," + " s.accountid," + " w.wrstandardcode," + " w.wrstandardname" + " FROM ERMCP_CONTRACTOPERATELOG t" + " inner join ermcp_spotcontract s" + " on t.RELATEDID = s.spotcontractid" + " left join deliverygoods g" + " on t.deliverygoodsid = g.deliverygoodsid" + " left join wrstandard w on t.wrstandardid=w.wrstandardid" + " left join dgfactoryitem gb" + " on s.spotgoodsbrandid = gb.dgfactoryitemid" + " WHERE t.biztype = 2" // 筛选条件 sqlId.And("t.userid", r.USERID) sqlId.And("t.TRADEDATE", r.TRADEDATE) sqlId.AndEx("t.deliverygoodsid", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0) sqlId.AndEx("t.wrstandardid", r.WRSTANDARDID, r.WRSTANDARDID > 0) sqlId.AndEx("s.spotgoodsbrandid", r.SPOTGOODSBRANDID, r.SPOTGOODSBRANDID > 0) if r.LogTypeFilter != "" { sqlId.Join(fmt.Sprintf(" and t.OPERATELOGTYPE in(%v)", r.LogTypeFilter)) } return sqlId.String() } // GetDataEx 获取日志记录 func (r *Ermcp3ReportOPLog) GetDataEx() (interface{}, error) { sData := make([]Ermcp3ReportOPLog, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].Calc() } return sData, err } // Ermcp3ReckonAreaSpotSub 现货日报表(作废) type Ermcp3ReckonAreaSpotSub struct { RECKONDATE string `json:"reckondate" xorm:"'RECKONDATE'" form:"reckondate" binding:"required"` // 日照时期(yyyyMMdd) AREAUSERID int64 `json:"areauserid" xorm:"'AREAUSERID'" form:"userid" binding:"required"` // 账户所属机构 ACCOUNTID int64 `json:"accountid" xorm:"'ACCOUNTID'" form:"accountid"` // 期货账户ID WRFACTORTYPEID int64 `json:"wrfactortypeid" xorm:"'WRFACTORTYPEID'"` // 仓单要素类型ID(212+Unix秒时间戳(10位)+xxxxxx) DELIVERYGOODSID int64 `json:"deliverygoodsid" xorm:"'DELIVERYGOODSID'" form:"deliverygoodsid"` // 现货品种ID WRSTANDARDID int64 `json:"wrstandardid" xorm:"'WRSTANDARDID'" form:"wrstandardid"` // 现货品类ID SPOTGOODSBRANDID int32 `json:"spotgoodsbrandid" xorm:"'SPOTGOODSBRANDID'" form:"spotgoodsbrandid"` // 现货品牌ID ORIBUYPRICEDQTY float64 `json:"oribuypricedqty" xorm:"'ORIBUYPRICEDQTY'"` // 期初采购合同已定价数量 ORISELLPRICEDQTY float64 `json:"orisellpricedqty" xorm:"'ORISELLPRICEDQTY'"` // 期初销售合同已定价数量 BUYPRICEDQTY float64 `json:"buypricedqty" xorm:"'BUYPRICEDQTY'"` // 采购合同已定价数量 SELLPRICEDQTY float64 `json:"sellpricedqty" xorm:"'SELLPRICEDQTY'"` // 销售合同已定价数量 TOTALSPOTQTY float64 `json:"totalspotqty" xorm:"'TOTALSPOTQTY'"` // 现货头寸总量 = 采购合同已定价数量 - 销售合同已定价数量 UPDATETIME string `json:"updatetime" xorm:"'UPDATETIME'"` // 更新时间 TODAYBUYRECKONQTY float64 `json:"todaybuyreckonqty" xorm:"'TODAYBUYRECKONQTY'"` // 今日采购合同已确定数量 TODAYSELLRECKONQTY float64 `json:"todaysellreckonqty" xorm:"'TODAYSELLRECKONQTY'"` // 今日销售合同已确定数量 ORIBUYPRICEDAMOUNT float64 `json:"oribuypricedamount" xorm:"'ORIBUYPRICEDAMOUNT'"` // 期初采购合同已定价金额 ORISELLPRICEDAMOUNT float64 `json:"orisellpricedamount" xorm:"'ORISELLPRICEDAMOUNT'"` // 期初销售合同已定价金额 BUYPRICEDAMOUNT float64 `json:"buypricedamount" xorm:"'BUYPRICEDAMOUNT'"` // 采购合同已定价金额 SELLPRICEDAMOUNT float64 `json:"sellpricedamount" xorm:"'SELLPRICEDAMOUNT'"` // 销售合同已定价金额 ORIBUYRECKONQTY float64 `json:"oribuyreckonqty" xorm:"'ORIBUYRECKONQTY'"` // 期初采购合同交收量 ORISELLRECKONQTY float64 `json:"orisellreckonqty" xorm:"'ORISELLRECKONQTY'"` // 期初销售合同交收量 ORIBUYCONTRACTINQTY float64 `json:"oribuycontractinqty" xorm:"'ORIBUYCONTRACTINQTY'"` // 期初采购合同入库量 ORISELLCONTRACTOUTQTY float64 `json:"orisellcontractoutqty" xorm:"'ORISELLCONTRACTOUTQTY'"` // 期初销售合同出库量 ORIPRODUCEINQTY float64 `json:"oriproduceinqty" xorm:"'ORIPRODUCEINQTY'"` // 期初生产入库量 ORIPRODUCEOUTQTY float64 `json:"oriproduceoutqty" xorm:"'ORIPRODUCEOUTQTY'"` // 期初生产出库量 BUYRECKONQTY float64 `json:"buyreckonqty" xorm:"'BUYRECKONQTY'"` // 采购合同交收量 SELLRECKONQTY float64 `json:"sellreckonqty" xorm:"'SELLRECKONQTY'"` // 销售合同交收量 BUYCONTRACTINQTY float64 `json:"buycontractinqty" xorm:"'BUYCONTRACTINQTY'"` // 采购合同入库量 SELLCONTRACTOUTQTY float64 `json:"sellcontractoutqty" xorm:"'SELLCONTRACTOUTQTY'"` // 销售合同出库量 PRODUCEINQTY float64 `json:"produceinqty" xorm:"'PRODUCEINQTY'"` // 生产入库量 PRODUCEOUTQTY float64 `json:"produceoutqty" xorm:"'PRODUCEOUTQTY'"` // 生产出库量 ENUMDICNAME string `json:"enumdicname" xorm:"'ENUMDICNAME'"` // 现货商品单位名称 WRSTANDARDNAME string `json:"wrstandardname" xorm:"'wrstandardname'"` // 品类名称 WRSTANDARDCODE string `json:"wrstandardcode" xorm:"'wrstandardcode'"` // 品类代码 DELIVERYGOODSCODE string `json:"deliverygoodscode" xorm:"'deliverygoodscode'"` // 现货商品代码 DELIVERYGOODSNAME string `json:"deliverygoodsname" xorm:"'deliverygoodsname'"` // 现货商品名称 GOODSUNITID int32 `json:"goodsunitid" xorm:"'goodsunitid'"` // 现货商品单位id BRANDNAME string `json:"brandname" xorm:"'brandname'"` // 品牌名称 ACCOUNTNAME string `json:"accountname" xorm:"'accountname'"` // 所属机构名称 BIZTYPE int32 `json:"biztype" xorm:"'BIZTYPE'"` // 业务类型 1-套保 2-套利 CURRENCYID int32 `json:"currencyid" xorm:"'CURRENCYID'"` // 币种id CURRENCYNAME string `json:"currencyname"` // 币种名称 TOTALBUYPRICEDQTY float64 `json:"totalbuypricedqty"` // 采购定价量 = 期末 - 期初 TOTALSELLPRICEDQTY float64 `json:"totalsellpricedqty"` // 销售定价量 = 期末 - 期初 BeginDate string `json:"-"` // 开始日期 EndDate string `json:"-"` // 结束日期 } func (r *Ermcp3ReckonAreaSpotSub) calc() { r.TOTALBUYPRICEDQTY = r.BUYPRICEDQTY - r.ORIBUYPRICEDQTY r.TOTALSELLPRICEDQTY = r.SELLPRICEDQTY - r.ORISELLPRICEDQTY r.CURRENCYNAME = mtpcache.GetCurrencyName(r.CURRENCYID) } func (r *Ermcp3ReckonAreaSpotSub) buildSql() string { var sqlId utils.SQLVal = "SELECT t.ORIBUYRECKONQTY," + " t.ORISELLRECKONQTY," + " t.ORIBUYCONTRACTINQTY," + " t.ORISELLCONTRACTOUTQTY," + " t.ORIPRODUCEINQTY," + " t.ORIPRODUCEOUTQTY," + " t.BUYRECKONQTY," + " t.SELLRECKONQTY," + " t.BUYCONTRACTINQTY," + " t.SELLCONTRACTOUTQTY," + " t.PRODUCEINQTY," + " t.PRODUCEOUTQTY," + " t.Reckondate," + " t.AREAUSERID," + " t.ACCOUNTID," + " t.WRFACTORTYPEID," + " t.DELIVERYGOODSID," + " t.WRSTANDARDID," + " t.SPOTGOODSBRANDID," + " t.ORIBUYPRICEDQTY," + " t.ORISELLPRICEDQTY," + " t.BUYPRICEDQTY," + " t.SELLPRICEDQTY," + " t.TOTALSPOTQTY," + " to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," + " t.TODAYBUYRECKONQTY," + " t.TODAYSELLRECKONQTY," + " t.ORIBUYPRICEDAMOUNT," + " t.ORISELLPRICEDAMOUNT," + " t.BUYPRICEDAMOUNT," + " t.SELLPRICEDAMOUNT," + " t.currencyid," + " t.biztype," + " w.wrstandardname," + " w.wrstandardcode," + " g.deliverygoodscode," + " g.deliverygoodsname," + " g.goodsunitid," + " d.dgfactoryitemvalue brandname," + " u.accountname," + " e.enumdicname" + " FROM RECKON_ERMCP_AREASPOTSUB t" + " LEFT JOIN WRSTANDARD w" + " on t.wrstandardid = w.wrstandardid" + " LEFT JOIN DELIVERYGOODS g" + " on t.deliverygoodsid = g.deliverygoodsid" + " LEFT JOIN DGFACTORYITEM d" + " on t.spotgoodsbrandid = d.dgfactoryitemid" + " LEFT JOIN USERACCOUNT u" + " on t.areauserid = u.userid" + " LEFT JOIN ENUMDICITEM e" + " on g.goodsunitid = e.enumitemname" + " and e.enumdiccode = 'goodsunit'" + " and e.enumitemstatus = 1" + " WHERE 1 = 1" sqlId.And("t.AREAUSERID", r.AREAUSERID) sqlId.AndEx("t.ACCOUNTID", r.ACCOUNTID, r.ACCOUNTID > 0) sqlId.AndEx("t.deliverygoodsid", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0) sqlId.AndEx("t.WRSTANDARDID", r.WRSTANDARDID, r.WRSTANDARDID > 0) sqlId.AndEx("t.SPOTGOODSBRANDID", r.SPOTGOODSBRANDID, r.SPOTGOODSBRANDID > 0) if r.RECKONDATE != "" { sqlId.And("t.RECKONDATE", r.RECKONDATE) } else if r.BeginDate != "" && r.BeginDate == r.EndDate { sqlId.And("t.RECKONDATE", r.BeginDate) } else { if r.BeginDate != "" { sqlId.BiggerOrEq("t.RECKONDATE", r.BeginDate) } if r.EndDate != "" { sqlId.LessOrEq("t.RECKONDATE", r.EndDate) } } sqlId.Join(" order by t.RECKONDATE") return sqlId.String() } // GetDataEx 获取现货日报表 func (r *Ermcp3ReckonAreaSpotSub) GetDataEx() (interface{}, error) { sData := make([]Ermcp3ReckonAreaSpotSub, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].calc() } return sData, err } // Ermcp3ReportAreaSpotSub 现货分类月报表(作废) type Ermcp3ReportAreaSpotSub struct { ORIBUYRECKONQTY float64 `json:"oribuyreckonqty" xorm:"'ORIBUYRECKONQTY'"` // 期初采购合同交收量【期初】 ORISELLRECKONQTY float64 `json:"orisellreckonqty" xorm:"'ORISELLRECKONQTY'"` // 期初销售合同交收量【期初】 ORIBUYCONTRACTINQTY float64 `json:"oribuycontractinqty" xorm:"'ORIBUYCONTRACTINQTY'"` // 期初采购合同入库量【期初】 ORISELLCONTRACTOUTQTY float64 `json:"orisellcontractoutqty" xorm:"'ORISELLCONTRACTOUTQTY'"` // 期初销售合同出库量【期初】 ORIPRODUCEINQTY float64 `json:"oriproduceinqty" xorm:"'ORIPRODUCEINQTY'"` // 期初生产入库量【期初】 ORIPRODUCEOUTQTY float64 `json:"oriproduceoutqty" xorm:"'ORIPRODUCEOUTQTY'"` // 期初生产出库量【期初】 BUYRECKONQTY float64 `json:"buyreckonqty" xorm:"'BUYRECKONQTY'"` // 采购合同交收量【期末】 SELLRECKONQTY float64 `json:"sellreckonqty" xorm:"'SELLRECKONQTY'"` // 销售合同交收量【期末】 BUYCONTRACTINQTY float64 `json:"buycontractinqty" xorm:"'BUYCONTRACTINQTY'"` // 采购合同入库量【期末】 SELLCONTRACTOUTQTY float64 `json:"sellcontractoutqty" xorm:"'SELLCONTRACTOUTQTY'"` // 销售合同出库量【期末】 PRODUCEINQTY float64 `json:"produceinqty" xorm:"'PRODUCEINQTY'"` // 生产入库量【期末】 PRODUCEOUTQTY float64 `json:"produceoutqty" xorm:"'PRODUCEOUTQTY'"` // 生产出库量【期末】 CYCLETIME string `json:"cycletime" xorm:"'CYCLETIME'" form:"cycletime"` // 周期时间 月(YYYYMM) 季(YYYYQ) 年(YYYY) 周(YYYYIW) 全(0)【原值】 CYCLETYPE int32 `json:"cycletype" xorm:"'CYCLETYPE'" form:"cycletype"` // 周期类型 - 1:月 2:季 3:年 4:周 5:全报表【原值】 AREAUSERID int64 `json:"areauserid" xorm:"'AREAUSERID'" form:"userid" binding:"required"` // 账户所属机构 ACCOUNTID int64 `json:"accountid" xorm:"'ACCOUNTID'" form:"accountid"` // 期货账户ID WRFACTORTYPEID int64 `json:"wrfactortypeid" xorm:"'WRFACTORTYPEID'"` // 仓单要素类型ID(212+Unix秒时间戳(10位)+xxxxxx) DELIVERYGOODSID int64 `json:"deliverygoodsid" xorm:"'DELIVERYGOODSID'" form:"deliverygoodsid"` // 现货品种ID WRSTANDARDID int64 `json:"wrstandardid" xorm:"'WRSTANDARDID'" form:"wrstandardid"` // 品类ID SPOTGOODSBRANDID int32 `json:"spotgoodsbrandid" xorm:"'SPOTGOODSBRANDID'" form:"spotgoodsbrandid"` // 现货品牌ID ORIBUYPRICEDQTY float64 `json:"oribuypricedqty" xorm:"'ORIBUYPRICEDQTY'"` // 期初采购合同已定价数量【期初】 ORISELLPRICEDQTY float64 `json:"orisellpricedqty" xorm:"'ORISELLPRICEDQTY'"` // 期初销售合同已定价数量【期初】 BUYPRICEDQTY float64 `json:"buypricedqty" xorm:"'BUYPRICEDQTY'"` // 采购合同已定价数量【期末】 SELLPRICEDQTY float64 `json:"sellpricedqty" xorm:"'SELLPRICEDQTY'"` // 销售合同已定价数量【期末】 TOTALSPOTQTY float64 `json:"totalspotqty" xorm:"'TOTALSPOTQTY'"` // 现货头寸总量 = 采购合同已定价数量 - 销售合同已定价数量【期末】 UPDATETIME string `json:"updatetime" xorm:"'UPDATETIME'"` // 更新时间 TODAYBUYRECKONQTY float64 `json:"todaybuyreckonqty" xorm:"'TODAYBUYRECKONQTY'"` // 今日采购合同已确定数量【汇总】 TODAYSELLRECKONQTY float64 `json:"todaysellreckonqty" xorm:"'TODAYSELLRECKONQTY'"` // 今日销售合同已确定数量【汇总】 ORIBUYPRICEDAMOUNT float64 `json:"oribuypricedamount" xorm:"'ORIBUYPRICEDAMOUNT'"` // 期初采购合同已定价金额【期初】 ORISELLPRICEDAMOUNT float64 `json:"orisellpricedamount" xorm:"'ORISELLPRICEDAMOUNT'"` // 期初销售合同已定价金额【期初】 BUYPRICEDAMOUNT float64 `json:"buypricedamount" xorm:"'BUYPRICEDAMOUNT'"` // 采购合同已定价金额【期末】 SELLPRICEDAMOUNT float64 `json:"sellpricedamount" xorm:"'SELLPRICEDAMOUNT'"` // 销售合同已定价金额【期末】 ENUMDICNAME string `json:"enumdicname" xorm:"'ENUMDICNAME'"` // 现货商品单位名称 WRSTANDARDNAME string `json:"wrstandardname" xorm:"'wrstandardname'"` // 品类名称 WRSTANDARDCODE string `json:"wrstandardcode" xorm:"'wrstandardcode'"` // 品类代码 DELIVERYGOODSCODE string `json:"deliverygoodscode" xorm:"'deliverygoodscode'"` // 现货商品代码 DELIVERYGOODSNAME string `json:"deliverygoodsname" xorm:"'deliverygoodsname'"` // 现货商品名称 GOODSUNITID int32 `json:"goodsunitid" xorm:"'goodsunitid'"` // 现货商品单位id BRANDNAME string `json:"brandname" xorm:"'brandname'"` // 品牌名称 ACCOUNTNAME string `json:"accountname" xorm:"'accountname'"` // 所属机构名称 BIZTYPE int32 `json:"biztype" xorm:"'BIZTYPE'"` // 业务类型 1-套保 2-套利 CURRENCYID int32 `json:"currencyid" xorm:"'CURRENCYID'"` // 币种id CURRENCYNAME string `json:"currencyname"` // 币种名称 TOTALBUYPRICEDQTY float64 `json:"totalbuypricedqty"` // 采购定价量 = 期末 - 期初 TOTALSELLPRICEDQTY float64 `json:"totalsellpricedqty"` // 销售定价量 = 期末 - 期初 } func (r *Ermcp3ReportAreaSpotSub) calc() { r.CURRENCYNAME = mtpcache.GetCurrencyName(r.CURRENCYID) r.TOTALBUYPRICEDQTY = r.BUYPRICEDQTY - r.ORIBUYPRICEDQTY r.TOTALSELLPRICEDQTY = r.SELLPRICEDQTY - r.ORISELLPRICEDQTY } func (r *Ermcp3ReportAreaSpotSub) buildSql() string { var sqlId utils.SQLVal = "SELECT t.ORIBUYRECKONQTY," + " t.ORISELLRECKONQTY," + " t.ORIBUYCONTRACTINQTY," + " t.ORISELLCONTRACTOUTQTY," + " t.ORIPRODUCEINQTY," + " t.ORIPRODUCEOUTQTY," + " t.BUYRECKONQTY," + " t.SELLRECKONQTY," + " t.BUYCONTRACTINQTY," + " t.SELLCONTRACTOUTQTY," + " t.PRODUCEINQTY," + " t.PRODUCEOUTQTY," + " t.CYCLETIME," + " t.CYCLETYPE," + " t.AREAUSERID," + " t.ACCOUNTID," + " t.WRFACTORTYPEID," + " t.DELIVERYGOODSID," + " t.WRSTANDARDID," + " t.SPOTGOODSBRANDID," + " t.ORIBUYPRICEDQTY," + " t.ORISELLPRICEDQTY," + " t.BUYPRICEDQTY," + " t.SELLPRICEDQTY," + " t.TOTALSPOTQTY," + " to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," + " t.TODAYBUYRECKONQTY," + " t.TODAYSELLRECKONQTY," + " t.ORIBUYPRICEDAMOUNT," + " t.ORISELLPRICEDAMOUNT," + " t.BUYPRICEDAMOUNT," + " t.SELLPRICEDAMOUNT," + " t.currencyid," + " t.biztype," + " w.wrstandardname," + " w.wrstandardcode," + " g.deliverygoodscode," + " g.deliverygoodsname," + " g.goodsunitid," + " d.dgfactoryitemvalue brandname," + " u.accountname," + " e.enumdicname" + " FROM REPORT_ERMCP_AREASPOTSUB t" + " LEFT JOIN WRSTANDARD w on t.wrstandardid=w.wrstandardid" + " LEFT JOIN DELIVERYGOODS g on t.deliverygoodsid=g.deliverygoodsid" + " LEFT JOIN DGFACTORYITEM d on t.spotgoodsbrandid=d.dgfactoryitemid" + " LEFT JOIN USERACCOUNT u on t.areauserid=u.userid" + " LEFT JOIN ENUMDICITEM e on g.goodsunitid=e.enumitemname and e.enumdiccode='goodsunit' and e.enumitemstatus=1" + " WHERE 1 = 1" sqlId.And("t.AREAUSERID", r.AREAUSERID) sqlId.And("t.cycletype", r.CYCLETYPE) sqlId.And("t.cycletime", r.CYCLETIME) sqlId.AndEx("t.ACCOUNTID", r.ACCOUNTID, r.ACCOUNTID > 0) sqlId.AndEx("t.deliverygoodsid", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0) sqlId.AndEx("t.WRSTANDARDID", r.WRSTANDARDID, r.WRSTANDARDID > 0) sqlId.AndEx("t.SPOTGOODSBRANDID", r.SPOTGOODSBRANDID, r.SPOTGOODSBRANDID > 0) return sqlId.String() } // GetDataEx 获取现货分类报表 func (r *Ermcp3ReportAreaSpotSub) GetDataEx() (interface{}, error) { sData := make([]Ermcp3ReportAreaSpotSub, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].calc() } return sData, err } // Ermcp3ExpourseReport 敞口报表 type Ermcp3ExpourseReport struct { RECKONDATE string `json:"reckondate" xorm:"'RECKONDATE'"` // 日照时期(yyyyMMdd) CYCLETIME string `json:"cycletime" xorm:"'CYCLETIME'"` // 周期时间 月(YYYYMM) 季(YYYYQ) 年(YYYY) 周(YYYYIW) 全(0)【原值】 CYCLETYPE int32 `json:"cycletype" xorm:"'CYCLETYPE'"` // 周期类型 - 1:月 2:季 3:年 4:周 5:全报表【原值】 ORITOTALSPOTQTY float64 `json:"oritotalspotqty" xorm:"'ORITOTALSPOTQTY'"` // 期初现货头寸总量 ORITOTALFUTUREQTY float64 `json:"oritotalfutureqty" xorm:"'ORITOTALFUTUREQTY'"` // 期初期货头寸总量 ORITOTALEXPOSURE float64 `json:"oritotalexposure" xorm:"'ORITOTALEXPOSURE'"` // 期初实时总敞口 ORINEEDHEDGEEXPOSOURE float64 `json:"orineedhedgeexposoure" xorm:"'ORINEEDHEDGEEXPOSOURE'"` // 期初应套保敞口 ORIHEDGEQTY float64 `json:"orihedgeqty" xorm:"'ORIHEDGEQTY'"` // 期初套保量 ORIARBITRAGEQTY float64 `json:"oriarbitrageqty" xorm:"'ORIARBITRAGEQTY'"` // 期初套利量 ORINEEDHEDGEQTY float64 `json:"orineedhedgeqty" xorm:"'ORINEEDHEDGEQTY'"` // 期初应套保量 ORINEEDARBITRAGEQTY float64 `json:"orineedarbitrageqty" xorm:"'ORINEEDARBITRAGEQTY'"` // 期初应套利量 ORITOTALNEEDHEDGEQTY float64 `json:"oritotalneedhedgeqty" xorm:"'ORITOTALNEEDHEDGEQTY'"` // 期初应套保总量 MIDDLEGOODSID int64 `json:"middlegoodsid" xorm:"'MIDDLEGOODSID'"` // 套保品种ID AREAUSERID int64 `json:"areauserid" xorm:"'AREAUSERID'"` // 所属机构\交易用户ID ACCOUNTID int64 `json:"accountid" xorm:"'ACCOUNTID'"` // 期货账户ID (作废, 默认为0) ORIBUYPLANQTY float64 `json:"oribuyplanqty" xorm:"'ORIBUYPLANQTY'"` // 期初采购计划数量 ORIBUYPRICEDQTY float64 `json:"oribuypricedqty" xorm:"'ORIBUYPRICEDQTY'"` // 期初采购合同已定价数量 ORISELLPLANQTY float64 `json:"orisellplanqty" xorm:"'ORISELLPLANQTY'"` // 期初销售计划数量 ORISELLPRICEDQTY float64 `json:"orisellpricedqty" xorm:"'ORISELLPRICEDQTY'"` // 期初销售合同已定价数量 ORIBUYFUTUREQTY float64 `json:"oribuyfutureqty" xorm:"'ORIBUYFUTUREQTY'"` // 期初买入期货数量 ORISELLFUTUREQTY float64 `json:"orisellfutureqty" xorm:"'ORISELLFUTUREQTY'"` // 期初卖出期货数量 BUYPLANQTY float64 `json:"buyplanqty" xorm:"'BUYPLANQTY'"` // 采购计划数量 BUYPRICEDQTY float64 `json:"buypricedqty" xorm:"'BUYPRICEDQTY'"` // 采购合同已定价数量 SELLPLANQTY float64 `json:"sellplanqty" xorm:"'SELLPLANQTY'"` // 销售计划数量 SELLPRICEDQTY float64 `json:"sellpricedqty" xorm:"'SELLPRICEDQTY'"` // 销售合同已定价数量 BUYFUTUREQTY float64 `json:"buyfutureqty" xorm:"'BUYFUTUREQTY'"` // 买入期货数量 SELLFUTUREQTY float64 `json:"sellfutureqty" xorm:"'SELLFUTUREQTY'"` // 卖出期货数量 TOTALSPOTQTY float64 `json:"totalspotqty" xorm:"'TOTALSPOTQTY'"` // 现货头寸总量 TOTALFUTUREQTY float64 `json:"totalfutureqty" xorm:"'TOTALFUTUREQTY'"` // 期货头寸总量(期货总量) TOTALEXPOSURE float64 `json:"totalexposure" xorm:"'TOTALEXPOSURE'"` // 实时总敞口(总敞口) TOTALHEDGERATIO float64 `json:"totalhedgeratio" xorm:"'TOTALHEDGERATIO'"` // 敞口比例 TOTALNEEDHEDGEQTY float64 `json:"totalneedhedgeqty" xorm:"'TOTALNEEDHEDGEQTY'"` // 应套保总量(现货应套保总量) NEEDHEDGEEXPOSOURE float64 `json:"needhedgeexposoure" xorm:"'NEEDHEDGEEXPOSOURE'"` // 应套保敞口(套保敞口) NEEDHEDGERATIO float64 `json:"needhedgeratio" xorm:"'NEEDHEDGERATIO'"` // 应套保敞口比例 UPDATETIME string `json:"updatetime" xorm:"'UPDATETIME'"` // 更新时间 HEDGEQTY float64 `json:"hedgeqty" xorm:"'HEDGEQTY'"` // 套保量 ARBITRAGEQTY float64 `json:"arbitrageqty" xorm:"'ARBITRAGEQTY'"` // 套利量 NEEDHEDGEQTY float64 `json:"needhedgeqty" xorm:"'NEEDHEDGEQTY'"` // 应套保量 NEEDARBITRAGEQTY float64 `json:"needarbitrageqty" xorm:"'NEEDARBITRAGEQTY'"` // 应套利量 MIDDLEGOODSNAME string `json:"middlegoodsname" xorm:"'MIDDLEGOODSNAME'"` // 套保商品名称 MIDDLEGOODSCODE string `json:"middlgoodscode" xorm:"'MIDDLEGOODSCODE'"` // 套保商品代码 UNITID int32 `json:"unitid" xorm:"'UNITID'"` // 单位id MGNEEDHEDGERATIO float64 `json:"mgneedhedgeratio" xorm:"'MGNEEDHEDGERATIO'"` // 套保比例(套保品的) NEEDARBITRAGERATIO float64 `json:"needarbitrageratio" xorm:"'NEEDARBITRAGERATIO'"` // 套利比例(套保品的) ENUMDICNAME string `json:"enumdicname"` // 单位名称 ACCOUNTNAME string `json:"accountname"` // 机构名称 DiffSpotQty float64 `json:"diffspotqty"` // 变动量(现货) DiffMgQtyA float64 `json:"diffmgqtya"` // 套保变动量 DiffMgQtyB float64 `json:"diffmgqtyb"` // 套利变动量 DiffFutuQty float64 `json:"difffutuqty"` // 变动量(期货) DiffQty float64 `json:"diffqty"` // 变动量(总敞口) DiffHedgeQty float64 `json:"diffhedgeqty"` // 变动量(现货应套保总量) DiffExposure float64 `json:"diffexposure"` // 变动量(套保敞口) QueryType int32 `json:"-"` // 查询类型 1-报表 BeginDate string `json:"begindate"` // 开始交易日 EndDate string `json:"enddate"` // 结束交易日 } func (r *Ermcp3ExpourseReport) calc() { r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID) r.ACCOUNTNAME = mtpcache.GetUserNameByUserId(r.AREAUSERID) // 变动量(现货) = 现货头寸总量 - 期初现货头寸总量 r.DiffSpotQty = r.TOTALSPOTQTY - r.ORITOTALSPOTQTY // 套保变动量 = 套保量 - 期初套保量 r.DiffMgQtyA = r.HEDGEQTY - r.ORIHEDGEQTY // 套利变动量 = 套利量 - 期初套利量 r.DiffMgQtyB = r.ARBITRAGEQTY - r.ORIARBITRAGEQTY // 变动量(期货) = (买入 - 买入期初) - (卖出 - 卖出期初) r.DiffFutuQty = (r.BUYFUTUREQTY - r.ORIBUYFUTUREQTY) - (r.SELLFUTUREQTY - r.ORISELLFUTUREQTY) // 变动量(敞口) = 实时敞口 - 期初实时敞口 r.DiffQty = r.TOTALEXPOSURE - r.ORITOTALEXPOSURE // 变动量(现货应套保总量) r.DiffHedgeQty = r.TOTALNEEDHEDGEQTY - r.ORITOTALNEEDHEDGEQTY // 变动量(套保敞口) r.DiffExposure = r.NEEDHEDGEEXPOSOURE - r.ORINEEDHEDGEEXPOSOURE } func (r *Ermcp3ExpourseReport) buildSql() string { if r.CYCLETYPE == 0 { return r.buildSqlDay() } return r.buildSqlCycle() } func (r *Ermcp3ExpourseReport) buildSqlDay() string { var sqlId utils.SQLVal = "SELECT t.ORITOTALSPOTQTY," + " t.ORITOTALFUTUREQTY," + " t.ORITOTALEXPOSURE," + " t.ORINEEDHEDGEEXPOSOURE," + " t.ORIHEDGEQTY," + " t.ORIARBITRAGEQTY," + " t.ORINEEDHEDGEQTY," + " t.ORINEEDARBITRAGEQTY," + " t.ORITOTALNEEDHEDGEQTY," + " t.RECKONDATE," + " t.MIDDLEGOODSID," + " t.AREAUSERID," + " t.ACCOUNTID," + " t.ORIBUYPLANQTY," + " t.ORIBUYPRICEDQTY," + " t.ORISELLPLANQTY," + " t.ORISELLPRICEDQTY," + " t.ORIBUYFUTUREQTY," + " t.ORISELLFUTUREQTY," + " t.BUYPLANQTY," + " t.BUYPRICEDQTY," + " t.SELLPLANQTY," + " t.SELLPRICEDQTY," + " t.BUYFUTUREQTY," + " t.SELLFUTUREQTY," + " t.TOTALSPOTQTY," + " t.TOTALFUTUREQTY," + " t.TOTALEXPOSURE," + " t.TOTALHEDGERATIO," + " t.TOTALNEEDHEDGEQTY," + " t.NEEDHEDGEEXPOSOURE," + " t.NEEDHEDGERATIO," + " to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," + " t.HEDGEQTY," + " t.ARBITRAGEQTY," + " t.NEEDHEDGEQTY," + " t.NEEDARBITRAGEQTY," + " g.middlegoodsname," + " g.middlegoodscode," + " g.goodsunitid unitid," + " g.needhedgeratio MGNEEDHEDGERATIO," + " g.needarbitrageratio" + " FROM RECKON_ERMCP_AREAEXPOSURE t" + " LEFT JOIN ERMS_MIDDLEGOODS g" + " on t.middlegoodsid = g.middlegoodsid" + " WHERE 1 = 1" sqlId.AndEx("t.AREAUSERID", r.AREAUSERID, r.AREAUSERID > 0) if r.QueryType == 1 { sqlId.And("t.RECKONDATE", r.RECKONDATE) } else if r.QueryType == 2 { sqlId.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate)) } return sqlId.String() } func (r *Ermcp3ExpourseReport) buildSqlCycle() string { var sqlId utils.SQLVal = "SELECT t.cycletime," + " t.cycletype," + " t.ORITOTALSPOTQTY," + " t.ORITOTALFUTUREQTY," + " t.ORITOTALEXPOSURE," + " t.ORINEEDHEDGEEXPOSOURE," + " t.ORIHEDGEQTY," + " t.ORIARBITRAGEQTY," + " t.ORINEEDHEDGEQTY," + " t.ORINEEDARBITRAGEQTY," + " t.ORITOTALNEEDHEDGEQTY," + " t.MIDDLEGOODSID," + " t.AREAUSERID," + " t.ACCOUNTID," + " t.ORIBUYPLANQTY," + " t.ORIBUYPRICEDQTY," + " t.ORISELLPLANQTY," + " t.ORISELLPRICEDQTY," + " t.ORIBUYFUTUREQTY," + " t.ORISELLFUTUREQTY," + " t.BUYPLANQTY," + " t.BUYPRICEDQTY," + " t.SELLPLANQTY," + " t.SELLPRICEDQTY," + " t.BUYFUTUREQTY," + " t.SELLFUTUREQTY," + " t.TOTALSPOTQTY," + " t.TOTALFUTUREQTY," + " t.TOTALEXPOSURE," + " t.TOTALHEDGERATIO," + " t.TOTALNEEDHEDGEQTY," + " t.NEEDHEDGEEXPOSOURE," + " t.NEEDHEDGERATIO," + " to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," + " t.HEDGEQTY," + " t.ARBITRAGEQTY," + " t.NEEDHEDGEQTY," + " t.NEEDARBITRAGEQTY," + " g.middlegoodsname," + " g.middlegoodscode," + " g.goodsunitid unitid," + " g.needhedgeratio MGNEEDHEDGERATIO," + " g.needarbitrageratio" + " FROM REPORT_ERMCP_AREAEXPOSURE t" + " LEFT JOIN ERMS_MIDDLEGOODS g" + " on t.middlegoodsid = g.middlegoodsid" + " WHERE 1 = 1" sqlId.And("t.AREAUSERID", r.AREAUSERID) sqlId.And("t.CYCLETYPE", r.CYCLETYPE) sqlId.And("t.CYCLETIME", r.CYCLETIME) return sqlId.String() } // GetDataEx 获取敞报表 func (r *Ermcp3ExpourseReport) GetDataEx() (interface{}, error) { sData := make([]Ermcp3ExpourseReport, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].calc() sData[i].BeginDate = r.BeginDate sData[i].EndDate = r.EndDate } return sData, err } // Ermcp3ExposureContractDetail 敞口合同明细 type Ermcp3ExposureContractDetail struct { MIDDLEGOODSID int32 `json:"middlegoodsid" xorm:"'MIDDLEGOODSID'"` // 套保品种id DELIVERYGOODSID int32 `json:"deliverygoodsid" xorm:"'DELIVERYGOODSID'"` // 现货品种id CONVERTRATIO float64 `json:"convertratio" xorm:"'CONVERTRATIO'"` // 折算系数(套保品种) USERID int64 `json:"userid" xorm:"'USERID'"` // 机构id SPOTCONTRACTID string `json:"spotcontractid" xorm:"'SPOTCONTRACTID'"` // 合同id CONTRACTNO string `json:"contractno" xorm:"'CONTRACTNO'"` // 合同编号 CONTRACTTYPE int32 `json:"contracttype" xorm:"'CONTRACTTYPE'"` // 现货合同类型 - 1:采购 -1:销售 BIZTYPE int32 `json:"biztype" xorm:"'BIZTYPE'"` // 业务类型 1-套保 2-套利 BUYUSERID int64 `json:"buyuserid" xorm:"'BUYUSERID'"` // 采购方id SELLUSERID int64 `json:"selluserid" xorm:"'SELLUSERID'"` // 销售方id QTY float64 `json:"qty" xorm:"'QTY'"` // 合同量 PRICEDQTY float64 `json:"pricedqty" xorm:"'PRICEDQTY'"` // 已定价量 WRSTANDARDID int32 `json:"wrstandardid" xorm:"'WRSTANDARDID'"` // 现货商品id WRSTANDARDNAME string `json:"wrstandardname" xorm:"'WRSTANDARDNAME'"` // 现货商品名称 WRSTANDARDCODE string `json:"wrstandardcode" xorm:"'WRSTANDARDCODE'"` // 现货商品代码 UNITID int32 `json:"unitid" xorm:"'UNITID'"` // 品类单位id MIDDLEGOODSNAME string `json:"middlegoodsname" xorm:"'MIDDLEGOODSNAME'"` // 套保品种名称 MIDDLEGOODSCODE string `json:"middlegoodscode" xorm:"'MIDDLEGOODSCODE'"` // 套保品种代码 GOODSUNITID int32 `json:"-" xorm:"'GOODSUNITID'"` // 套保品种单位id TRADEDATE string `json:"tradedate" xorm:"'tradedate'"` // 交易日 NEEDHEDGERATIO float64 `json:"needhedgeratio" xorm:"'NEEDHEDGERATIO'"` // 应套保比例 NEEDARBITRAGERATIO float64 `json:"needarbitrageratio" xorm:"'NEEDARBITRAGERATIO'"` // 应套利比例 BRANDNAME string `json:"brandname" xorm:"'BRANDNAME'"` // 品牌名称 DELIVERYGOODSCODE string `json:"deliverygoodscode" xorm:"'DELIVERYGOODSCODE'"` // 现货品种代码 DELIVERYGOODSNAME string `json:"deliverygoodsname" xorm:"'DELIVERYGOODSNAME'"` // 现货品种名称 CONVERTFACTOR float64 `json:"convertfactor" xorm:"'CONVERTFACTOR'"` // 标仓系数 TODAYPRICEDQTY float64 `json:"todaypricedqty" xorm:"'TODAYPRICEDQTY'"` // 今定价量 EnumdicName string `json:"enumdicname"` // 单位名称 MGUNITNAME string `json:"mgunitname"` // 套保品种单位名称 BUYUSERNAME string `json:"buyusername"` // 采购方名称 SELLUSERNAME string `json:"sellusername"` // 销售方名称 USERNAME string `json:"username"` // 交易用户 CurQty float64 `json:"curqty"` // 今定价量 DiffQty float64 `json:"diffqty"` // 套保品种今变动量 DiffHedgeQty float64 `json:"diffhedgeqty"` // 套保变动量 DiffNeedHedgeQty float64 `json:"diffneedhedgeqty"` // 应套保变动量 BeginDate string `json:"-"` // 开始交易日 EndDate string `json:"-"` // 结束交易日 } func (r *Ermcp3ExposureContractDetail) calc() { r.CurQty = r.PRICEDQTY r.DiffQty = r.QTY * r.CONVERTRATIO r.DiffHedgeQty = r.TODAYPRICEDQTY * r.CONVERTRATIO * r.CONVERTFACTOR if r.BIZTYPE == 1 { r.DiffNeedHedgeQty = r.TODAYPRICEDQTY * r.CONVERTRATIO * r.CONVERTFACTOR * r.NEEDHEDGERATIO } else { r.DiffNeedHedgeQty = r.TODAYPRICEDQTY * r.CONVERTRATIO * r.CONVERTFACTOR * r.NEEDARBITRAGERATIO } r.EnumdicName = mtpcache.GetEnumDicitemName(r.UNITID) r.MGUNITNAME = mtpcache.GetEnumDicitemName(r.GOODSUNITID) r.BUYUSERNAME = mtpcache.GetUserNameByUserId(r.BUYUSERID) r.SELLUSERNAME = mtpcache.GetUserNameByUserId(r.SELLUSERID) r.USERNAME = mtpcache.GetUserNameByUserId(r.USERID) } func (r *Ermcp3ExposureContractDetail) buildSql() string { var sqlId utils.SQLVal = ` select t.middlegoodsid, t.deliverygoodsid, t.convertratio, s.userid, to_char(s.spotcontractid) spotcontractid, s.contractno, s.contracttype, s.biztype, s.buyuserid, s.selluserid, s.qty, s.pricedqty, s.wrstandardid, s.reckondate tradedate, s.todaypricedqty, w.wrstandardname, w.wrstandardcode, w.unitid, w.convertfactor, mg.middlegoodsname, mg.middlegoodscode, mg.goodsunitid, mg.needhedgeratio, mg.needarbitrageratio, dg.dgfactoryitemid brandid, dg.dgfactoryitemvalue brandName, g.deliverygoodscode, g.deliverygoodsname from erms2_wrsconvertdetail t inner join reckon_ermcp_spotcontract s on t.wrstandardid = s.wrstandardid left join erms_middlegoods mg on t.middlegoodsid = mg.middlegoodsid left join wrstandard w on s.wrstandardid = w.wrstandardid left join dgfactoryitem dg on s.spotgoodsbrandid = dg.dgfactoryitemid left join deliverygoods g on s.deliverygoodsid = g.deliverygoodsid where 1 = 1 and s.contractstatus in (2, 3) ` sqlId.AndEx("s.reckondate", r.TRADEDATE, len(r.TRADEDATE) > 0) if len(r.BeginDate) > 0 && len(r.EndDate) > 0 { sqlId.Join(fmt.Sprintf(" and s.reckondate >= '%v' and s.reckondate <= '%v'", r.BeginDate, r.EndDate)) } sqlId.And("t.middlegoodsid", r.MIDDLEGOODSID) sqlId.Join(fmt.Sprintf(" and %v in(s.userid, s.tradeuserid)", r.USERID)) return sqlId.String() } // GetDataEx 获取敞口合同明细 func (r *Ermcp3ExposureContractDetail) GetDataEx() (interface{}, error) { sData := make([]Ermcp3ExposureContractDetail, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].USERID = r.USERID sData[i].calc() } return sData, err } // Ermcp3ExposureHedgeplanDetail 敞口套保计划明细 type Ermcp3ExposureHedgeplanDetail struct { MIDDLEGOODSID int32 `json:"middlegoodsid" xorm:"'MIDDLEGOODSID'"` // 套保品种id DELIVERYGOODSID int32 `json:"deliverygoodsid" xorm:"'DELIVERYGOODSID'"` // 现货商品id CONVERTRATIO float64 `json:"convertratio" xorm:"'CONVERTRATIO'"` // 折算系数(套保品种) USERID int64 `json:"userid" xorm:"'USERID'"` // 机构id HEDGEPLANID string `json:"hedgeplanid" xorm:"'HEDGEPLANID'"` // 套保计划id HEDGEPLANNO string `json:"hedgeplanno" xorm:"'HEDGEPLANNO'"` // 套保计划名称 BIZTYPE int32 `json:"biztype" xorm:"'BIZTYPE'"` // 业务类型(期现用途,业务类型就是期现用途) 1-套保 2-套利 QTY float64 `json:"qty" xorm:"'QTY'"` // 合同量 PRICEDQTY float64 `json:"pricedqty" xorm:"'PRICEDQTY'"` // 已定价量 WRSTANDARDID int32 `json:"wrstandardid" xorm:"'WRSTANDARDID'"` // 品类id WRSTANDARDNAME string `json:"wrstandardname" xorm:"'WRSTANDARDNAME'"` // 品类名称 WRSTANDARDCODE string `json:"wrstandardcode" xorm:"'WRSTANDARDCODE'"` // 品类代码 UNITID int32 `json:"unitid" xorm:"'UNITID'"` // 品类单位id MIDDLEGOODSNAME string `json:"middlegoodsname" xorm:"'MIDDLEGOODSNAME'"` // 套保品种名称 MIDDLEGOODSCODE string `json:"middlegoodscode" xorm:"'MIDDLEGOODSCODE'"` // 套保品种代码 GOODSUNITID int32 `json:"-" xorm:"'GOODSUNITID'"` // 套保品种单位id TRADEDATE string `json:"tradedate" xorm:"'tradedate'"` // 交易日(登记日) NEEDHEDGERATIO float64 `json:"needhedgeratio" xorm:"'NEEDHEDGERATIO'"` // 应套保比例 NEEDARBITRAGERATIO float64 `json:"needarbitrageratio" xorm:"'NEEDARBITRAGERATIO'"` // 应套利比例 CONTRACTTYPE int32 `json:"contracttype" xorm:"'CONTRACTTYPE'"` // 计划类型 - 1:采购 -1:销售 HEDGEPLANSTATUS int32 `json:"hedgeplanstatus" xorm:"'HEDGEPLANSTATUS'"` // 套保计划状态 - 0:未提交 1:待审核 2:执行中 3:正常完结 4:审核拒绝 5:异常完结 6:已撤回 DELIVERYGOODSCODE string `json:"deliverygoodscode" xorm:"'DELIVERYGOODSCODE'"` // 现货商品代码 DELIVERYGOODSNAME string `json:"deliverygoodsname" xorm:"'DELIVERYGOODSNAME'"` // 现货商吕名称 DGUNITID int32 `json:"dgunitid" xorm:"'DGUNITID'"` // 现货商品单位id EnumdicName string `json:"enumdicname"` // 单位名称(品类) MGUNITIDNAME string `json:"mgunitidname"` // 单位名称(套保商品) DGUNITIDNAME string `json:"dgunitidname"` // 单位名称(现货商品) CurQty float64 `json:"-"` // 今定价量 DiffQty float64 `json:"diffqty"` // 套保品种今变动量 ChangQty float64 `json:"changqty"` // 应套保总量变化量 BeginDate string `json:"-"` EndDate string `json:"-"` } func (r *Ermcp3ExposureHedgeplanDetail) calc() { r.EnumdicName = mtpcache.GetEnumDicitemName(r.UNITID) r.MGUNITIDNAME = mtpcache.GetEnumDicitemName(r.GOODSUNITID) r.DGUNITIDNAME = mtpcache.GetEnumDicitemName(r.DGUNITID) r.CurQty = r.QTY r.DiffQty = r.QTY * r.CONVERTRATIO r.ChangQty = r.QTY * r.NEEDHEDGERATIO * r.CONVERTRATIO } func (r *Ermcp3ExposureHedgeplanDetail) buildSql() string { var sqlId utils.SQLVal = ` select t.middlegoodsid, t.deliverygoodsid, t.convertratio, s.areauserid userid, to_char(s.hedgeplanid) hedgeplanid, s.hedgeplanno, s.contracttype, s.biztype, s.planqty qty, s.wrstandardid, s.audittradedate tradedate, s.hedgeplanstatus, w.wrstandardname, w.wrstandardcode, w.unitid, g.deliverygoodscode, g.deliverygoodsname, g.goodsunitid, mg.middlegoodsname, mg.middlegoodscode, mg.goodsunitid, mg.needhedgeratio, mg.needarbitrageratio from erms2_wrsconvertdetail t inner join ermcp_hedgeplan s on t.wrstandardid = s.wrstandardid left join erms_middlegoods mg on t.middlegoodsid = mg.middlegoodsid left join wrstandard w on s.wrstandardid = w.wrstandardid left join deliverygoods g on s.deliverygoodsid = g.deliverygoodsid where 1 = 1 and s.hedgeplanstatus in (2, 3) ` sqlId.AndEx("s.audittradedate", r.TRADEDATE, len(r.TRADEDATE) > 0) if len(r.BeginDate) > 0 && len(r.EndDate) > 0 { sqlId.Join(fmt.Sprintf(" and s.audittradedate >= '%v' and s.audittradedate <= '%v'", r.BeginDate, r.EndDate)) } sqlId.And("t.middlegoodsid", r.MIDDLEGOODSID) sqlId.Join(fmt.Sprintf(" and %v in(s.areauserid, s.tradeuserid)", r.USERID)) return sqlId.String() } // GetDataEx 获取敞口套保计划明细 func (r *Ermcp3ExposureHedgeplanDetail) GetDataEx() (interface{}, error) { sData := make([]Ermcp3ExposureHedgeplanDetail, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].calc() } return sData, err } // Ermcp3AreaSpotPLReport 现货报表(日/月/周/季/年) type Ermcp3AreaSpotPLReport struct { RECKONDATE string `json:"reckondate" xorm:"RECKONDATE"` // 日照时期(yyyyMMdd) CYCLETIME string `json:"cycletime" xorm:"'CYCLETIME'"` // 周期时间 月(YYYYMM) 季(YYYYQ) 年(YYYY) 周(YYYYIW) 全(0)【原值】 CYCLETYPE int32 `json:"cycletype" xorm:"'CYCLETYPE'"` // 周期类型 - 1:月 2:季 3:年 4:周 5:全报表【原值】 AREAUSERID int64 `json:"areauserid" xorm:"AREAUSERID"` // 所属机构\交易用户ID ACCOUNTID int64 `json:"accountid" xorm:"ACCOUNTID"` // 期货账户ID (作废, 默认为0) WRFACTORTYPEID int64 `json:"wrfactortypeid" xorm:"WRFACTORTYPEID"` // 仓单要素类型ID(212+Unix秒时间戳(10位)+xxxxxx) CURRENCYID int32 `json:"currencyid" xorm:"CURRENCYID"` // 结算币种ID【原值】 BIZTYPE int32 `json:"biztype" xorm:"BIZTYPE"` // 业务类型 - 1:套保 2:套利 DELIVERYGOODSID int64 `json:"deliverygoodsid" xorm:"DELIVERYGOODSID"` // 现货品种ID WRSTANDARDID int64 `json:"wrstandardid" xorm:"WRSTANDARDID"` // 现货品类ID SPOTGOODSBRANDID int32 `json:"spotgoodsbrandid" xorm:"SPOTGOODSBRANDID"` // 现货品牌ID ORIBUYQTY float64 `json:"oribuyqty" xorm:"ORIBUYQTY"` // 期初采购总量 ORIBUYAMOUNT float64 `json:"oribuyamount" xorm:"ORIBUYAMOUNT"` // 期初采购总额 ORISELLQTY float64 `json:"orisellqty" xorm:"ORISELLQTY"` // 期初销售总量 ORISELLAMOUNT float64 `json:"orisellamount" xorm:"ORISELLAMOUNT"` // 期初销售总额 ORIQTY float64 `json:"oriqty" xorm:"ORIQTY"` // 期初量 ORIAVERAGEPRICE SFLOAT64 `json:"oriaverageprice" xorm:"ORIAVERAGEPRICE"` // 期初均价 ORIAMOUNT float64 `json:"oriamount" xorm:"ORIAMOUNT"` // 期初额 TODAYBUYQTY float64 `json:"todaybuyqty" xorm:"TODAYBUYQTY"` // 今日采购量(今采购量) TODAYBUYAMOUNT float64 `json:"todaybuyamount" xorm:"TODAYBUYAMOUNT"` // 今日采购额(今采购额) TODAYBUYAVERAGEPRICE SFLOAT64 `json:"todaybuyaverageprice" xorm:"TODAYBUYAVERAGEPRICE"` // 今日采购均价 TODAYSELLQTY float64 `json:"todaysellqty" xorm:"TODAYSELLQTY"` // 今日销售量(今销售量) TODAYSELLAMOUNT float64 `json:"todaysellamount" xorm:"TODAYSELLAMOUNT"` // 今日销售额(今销售额) TODAYSELLAVERAGEPRICE SFLOAT64 `json:"todaysellaverageprice" xorm:"TODAYSELLAVERAGEPRICE"` // 今日销售均价 CURBUYQTY float64 `json:"curbuyqty" xorm:"CURBUYQTY"` // 期末采购总量 CURBUYAMOUNT float64 `json:"curbuyamount" xorm:"CURBUYAMOUNT"` // 期末采购总额 CURSELLQTY float64 `json:"cursellqty" xorm:"CURSELLQTY"` // 期末销售总量 CURSELLAMOUNT float64 `json:"cursellamount" xorm:"CURSELLAMOUNT"` // 期末销售总额 CURQTY float64 `json:"curqty" xorm:"CURQTY"` // 期末量 CURAVERAGEPRICE SFLOAT64 `json:"curaverageprice" xorm:"CURAVERAGEPRICE"` // 期末均价 CURAMOUNT float64 `json:"curamount" xorm:"CURAMOUNT"` // 期末额 CURSPOTPRICE SFLOAT64 `json:"curspotprice" xorm:"CURSPOTPRICE"` // 参考市价(最新价) CURMARKETVALUE SFLOAT64 `json:"curmarketvalue" xorm:"CURMARKETVALUE"` // 参考市值(期末市值) ACTUALPL float64 `json:"actualpl" xorm:"ACTUALPL"` // 现货损益 FLOATPL SFLOAT64 `json:"floatpl" xorm:"FLOATPL"` // 浮动损益 UPDATETIME string `json:"updatetime" xorm:"UPDATETIME"` // 更新时间 TODAYINQTY float64 `json:"todayinqty" xorm:"TODAYINQTY"` // 今日入库量(今入库量) TODAYOUTQTY float64 `json:"todayoutqty" xorm:"TODAYOUTQTY"` // 今日出库量(今出库量) DELIVERYGOODSCODE string `json:"deliverygoodscode" xorm:"'DELIVERYGOODSCODE'"` // 现货品种代码 DELIVERYGOODSNAME string `json:"deliverygoodsname" xorm:"'DELIVERYGOODSNAME'"` // 现货品种名称 GOODSUNITID int32 `json:"goodsunitid" xorm:"'GOODSUNITID'"` // 现货品种单位id(作废) WRSTANDARDCODE string `json:"wrstandardcode" xorm:"'WRSTANDARDCODE'"` // 现货商品(品类)代码 WRSTANDARDNAME string `json:"wrstandardname" xorm:"'WRSTANDARDNAME'"` // 现货商品(品类)名称 UNITID int32 `json:"unitid" xorm:"'UNITID'"` // 品类单位id BRANDNAME string `json:"brandname" xorm:"'BRANDNAME'"` // 品牌名称 EnumdicName string `json:"enumdicname"` // 现货品种单位名称(作废) UNITIDNAME string `json:"unitidname"` // 品类单位名称 ACCOUNTNAME string `json:"accountname"` // 机构名称 CURRENCYNAME string `json:"currencyname"` // 币种名称 QueryType int32 `json:"-"` // 查询类型 1-报表 2-报表明细 BeginDate string `json:"begindate"` // 开始交易日 EndDate string `json:"enddate"` // 结束交易日 SumFlag int32 `json:"-" form:"sumflag"` // 日报表统计维度 1：品种+品类 } func (r *Ermcp3AreaSpotPLReport) calc() { r.EnumdicName = mtpcache.GetEnumDicitemName(r.GOODSUNITID) r.UNITIDNAME = mtpcache.GetEnumDicitemName(r.UNITID) r.ACCOUNTNAME = mtpcache.GetUserNameByUserId(r.AREAUSERID) r.CURRENCYNAME = mtpcache.GetCurrencyName(r.CURRENCYID) fCalcAvePrice := func(avePrice *SFLOAT64, amount, qty float64) { if qty > 1e-10 { avePrice.Set(amount / qty) avePrice.Round(3) } else if qty < -1e-10 { avePrice.Set(amount / qty) avePrice.Round(3) } else { *avePrice = 0 } } // 期初均价 fCalcAvePrice(&r.ORIAVERAGEPRICE, r.ORIAMOUNT, r.ORIQTY) // 期末均价 fCalcAvePrice(&r.CURAVERAGEPRICE, r.CURAMOUNT, r.CURQTY) // 今采购均价 fCalcAvePrice(&r.TODAYBUYAVERAGEPRICE, r.TODAYBUYAMOUNT, r.TODAYBUYQTY) // 今销售均价 fCalcAvePrice(&r.TODAYSELLAVERAGEPRICE, r.TODAYSELLAMOUNT, r.TODAYSELLQTY) } func (r *Ermcp3AreaSpotPLReport) buildSql() string { // 日报表 if r.QueryType == 1 && r.CYCLETYPE == 0 { if r.SumFlag == 1 { return r.buildSqlDay2() } else { return r.buildSqlDay() } } // 日报表明细 if r.QueryType == 2 && r.CYCLETYPE == 0 { return r.buildSqlDayDetail() } return r.buildSqlCycle() } // buildSqlDay 现货日报表查询语句 func (r *Ermcp3AreaSpotPLReport) buildSqlDay() string { var sqlId utils.SQLVal = "select a.*," + " g.deliverygoodscode," + " g.deliverygoodsname," + " g.goodsunitid" + " from (SELECT t.RECKONDATE," + " t.AREAUSERID," + " t.CURRENCYID," + " t.DELIVERYGOODSID," + " sum(t.ORIBUYQTY) ORIBUYQTY," + " sum(t.ORIBUYAMOUNT) ORIBUYAMOUNT," + " sum(t.ORISELLQTY) ORISELLQTY," + " sum(t.ORISELLAMOUNT) ORISELLAMOUNT," + " sum(t.ORIQTY) ORIQTY," + " sum(t.ORIAMOUNT) ORIAMOUNT," + " sum(t.TODAYBUYQTY) TODAYBUYQTY," + " sum(t.TODAYBUYAMOUNT) TODAYBUYAMOUNT," + " sum(t.TODAYSELLQTY) TODAYSELLQTY," + " sum(t.TODAYSELLAMOUNT) TODAYSELLAMOUNT," + " sum(t.CURBUYQTY) CURBUYQTY," + " sum(t.CURBUYAMOUNT) CURBUYAMOUNT," + " sum(t.CURSELLQTY) CURSELLQTY," + " sum(t.CURSELLAMOUNT) CURSELLAMOUNT," + " sum(t.CURQTY) CURQTY," + " sum(t.CURAMOUNT) CURAMOUNT," + " max(t.CURSPOTPRICE) CURSPOTPRICE," + " sum(t.CURMARKETVALUE) CURMARKETVALUE," + " sum(t.ACTUALPL) ACTUALPL," + " sum(t.FLOATPL) FLOATPL," + " sum(t.TODAYINQTY) TODAYINQTY," + " sum(t.TODAYOUTQTY) TODAYOUTQTY" + " FROM RECKON_ERMCP_AREASPOTPL t" + " WHERE 1=1 %v" + " GROUP BY t.RECKONDATE," + " t.AREAUSERID," + " t.CURRENCYID," + " t.DELIVERYGOODSID) a" + " LEFT JOIN DELIVERYGOODS g" + " on a.deliverygoodsid = g.deliverygoodsid" var sqlParam utils.SQLVal sqlParam.And("t.AREAUSERID", r.AREAUSERID) if r.QueryType == 1 { sqlParam.And("t.RECKONDATE", r.RECKONDATE) } else if r.QueryType == 2 { sqlParam.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate)) } sqlId.FormatParam(sqlParam.String()) return sqlId.String() } // buildSqlDay2 现货日报表查询语句(汇总维度与buildSqlDay不同, 给pcweb版本用) func (r *Ermcp3AreaSpotPLReport) buildSqlDay2() string { var sqlId utils.SQLVal = ` select a.*, g.deliverygoodscode, g.deliverygoodsname, g.goodsunitid, w.wrstandardcode, w.wrstandardname, w.unitid from (SELECT t.RECKONDATE, t.AREAUSERID, t.CURRENCYID, t.DELIVERYGOODSID, t.WRSTANDARDID, sum(t.ORIBUYQTY) ORIBUYQTY, sum(t.ORIBUYAMOUNT) ORIBUYAMOUNT, sum(t.ORISELLQTY) ORISELLQTY, sum(t.ORISELLAMOUNT) ORISELLAMOUNT, sum(t.ORIQTY) ORIQTY, sum(t.ORIAMOUNT) ORIAMOUNT, sum(t.TODAYBUYQTY) TODAYBUYQTY, sum(t.TODAYBUYAMOUNT) TODAYBUYAMOUNT, sum(t.TODAYSELLQTY) TODAYSELLQTY, sum(t.TODAYSELLAMOUNT) TODAYSELLAMOUNT, sum(t.CURBUYQTY) CURBUYQTY, sum(t.CURBUYAMOUNT) CURBUYAMOUNT, sum(t.CURSELLQTY) CURSELLQTY, sum(t.CURSELLAMOUNT) CURSELLAMOUNT, sum(t.CURQTY) CURQTY, sum(t.CURAMOUNT) CURAMOUNT, max(t.CURSPOTPRICE) CURSPOTPRICE, sum(t.CURMARKETVALUE) CURMARKETVALUE, sum(t.ACTUALPL) ACTUALPL, sum(t.FLOATPL) FLOATPL, sum(t.TODAYINQTY) TODAYINQTY, sum(t.TODAYOUTQTY) TODAYOUTQTY FROM RECKON_ERMCP_AREASPOTPL t WHERE 1 = 1 %v GROUP BY t.RECKONDATE, t.AREAUSERID, t.CURRENCYID, t.DELIVERYGOODSID, t.WRSTANDARDID) a LEFT JOIN DELIVERYGOODS g on a.deliverygoodsid = g.deliverygoodsid LEFT JOIN WRSTANDARD w on a.wrstandardid = w.wrstandardid ` var sqlParam utils.SQLVal sqlParam.And("t.AREAUSERID", r.AREAUSERID) if r.QueryType == 1 { sqlParam.And("t.RECKONDATE", r.RECKONDATE) } else if r.QueryType == 2 { sqlParam.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate)) } sqlId.FormatParam(sqlParam.String()) return sqlId.String() } func (r *Ermcp3AreaSpotPLReport) buildSqlDayDetail() string { var sqlId utils.SQLVal = "select a.*," + " g.deliverygoodscode," + " g.deliverygoodsname," + " g.goodsunitid," + " w.wrstandardcode," + " w.wrstandardname," + " w.unitid," + " dg.dgfactoryitemvalue brandName" + " from (SELECT t.RECKONDATE," + " t.AREAUSERID," + " t.CURRENCYID," + " t.DELIVERYGOODSID," + " t.WRSTANDARDID," + " t.SPOTGOODSBRANDID," + " sum(t.ORIBUYQTY) ORIBUYQTY," + " sum(t.ORIBUYAMOUNT) ORIBUYAMOUNT," + " sum(t.ORISELLQTY) ORISELLQTY," + " sum(t.ORISELLAMOUNT) ORISELLAMOUNT," + " sum(t.ORIQTY) ORIQTY," + " sum(t.ORIAMOUNT) ORIAMOUNT," + " sum(t.TODAYBUYQTY) TODAYBUYQTY," + " sum(t.TODAYBUYAMOUNT) TODAYBUYAMOUNT," + " sum(t.TODAYSELLQTY) TODAYSELLQTY," + " sum(t.TODAYSELLAMOUNT) TODAYSELLAMOUNT," + " sum(t.CURBUYQTY) CURBUYQTY," + " sum(t.CURBUYAMOUNT) CURBUYAMOUNT," + " sum(t.CURSELLQTY) CURSELLQTY," + " sum(t.CURSELLAMOUNT) CURSELLAMOUNT," + " sum(t.CURQTY) CURQTY," + " sum(t.CURAMOUNT) CURAMOUNT," + " max(t.CURSPOTPRICE) CURSPOTPRICE," + " sum(t.CURMARKETVALUE) CURMARKETVALUE," + " sum(t.ACTUALPL) ACTUALPL," + " sum(t.FLOATPL) FLOATPL," + " sum(t.TODAYINQTY) TODAYINQTY," + " sum(t.TODAYOUTQTY) TODAYOUTQTY" + " FROM RECKON_ERMCP_AREASPOTPL t" + " WHERE 1 = 1 %v" + " GROUP BY t.RECKONDATE," + " t.AREAUSERID," + " t.CURRENCYID," + " t.DELIVERYGOODSID," + " t.WRSTANDARDID," + " t.SPOTGOODSBRANDID) a" + " LEFT JOIN DELIVERYGOODS g" + " on a.deliverygoodsid = g.deliverygoodsid" + " LEFT JOIN WRSTANDARD w" + " on a.wrstandardid = w.wrstandardid" + " LEFT JOIN DGFACTORYITEM dg" + " on a.spotgoodsbrandid = dg.dgfactoryitemid" var sqlParam utils.SQLVal sqlParam.And("t.AREAUSERID", r.AREAUSERID) sqlParam.AndEx("t.RECKONDATE", r.RECKONDATE, len(r.RECKONDATE) > 0) if len(r.BeginDate) > 0 && len(r.EndDate) > 0 { sqlParam.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate)) } sqlParam.And("t.CURRENCYID", r.CURRENCYID) sqlParam.And("t.DELIVERYGOODSID", r.DELIVERYGOODSID) sqlId.FormatParam(sqlParam.String()) return sqlId.String() } // buildSqlCycle 周期报表查询语句 func (r *Ermcp3AreaSpotPLReport) buildSqlCycle() string { var sqlId utils.SQLVal = "select a.*," + " g.deliverygoodscode," + " g.deliverygoodsname," + " g.goodsunitid" + " from (SELECT t.cycletype," + " t.cycletime," + " t.AREAUSERID," + " t.CURRENCYID," + " t.DELIVERYGOODSID," + " sum(t.ORIBUYQTY) ORIBUYQTY," + " sum(t.ORIBUYAMOUNT) ORIBUYAMOUNT," + " sum(t.ORISELLQTY) ORISELLQTY," + " sum(t.ORISELLAMOUNT) ORISELLAMOUNT," + " sum(t.ORIQTY) ORIQTY," + " sum(t.ORIAMOUNT) ORIAMOUNT," + " sum(t.TODAYBUYQTY) TODAYBUYQTY," + " sum(t.TODAYBUYAMOUNT) TODAYBUYAMOUNT," + " sum(t.TODAYSELLQTY) TODAYSELLQTY," + " sum(t.TODAYSELLAMOUNT) TODAYSELLAMOUNT," + " sum(t.CURBUYQTY) CURBUYQTY," + " sum(t.CURBUYAMOUNT) CURBUYAMOUNT," + " sum(t.CURSELLQTY) CURSELLQTY," + " sum(t.CURSELLAMOUNT) CURSELLAMOUNT," + " sum(t.CURQTY) CURQTY," + " sum(t.CURAMOUNT) CURAMOUNT," + " max(t.CURSPOTPRICE) CURSPOTPRICE," + " sum(t.CURMARKETVALUE) CURMARKETVALUE," + " sum(t.ACTUALPL) ACTUALPL," + " sum(t.FLOATPL) FLOATPL," + " sum(t.TODAYINQTY) TODAYINQTY," + " sum(t.TODAYOUTQTY) TODAYOUTQTY" + " FROM REPORT_ERMCP_AREASPOTPL t" + " WHERE 1=1 %v" + " GROUP BY t.AREAUSERID," + " t.cycletype," + " t.cycletime," + " t.CURRENCYID," + " t.DELIVERYGOODSID) a" + " LEFT JOIN DELIVERYGOODS g" + " on a.deliverygoodsid = g.deliverygoodsid" var sqlParam utils.SQLVal sqlParam.And("t.AREAUSERID", r.AREAUSERID) sqlParam.And("t.Cycletype", r.CYCLETYPE) sqlParam.And("t.CYCLETIME", r.CYCLETIME) sqlId.FormatParam(sqlParam.String()) return sqlId.String() } // GetDataEx 获取现货报表(日/月/周/季/年) func (r *Ermcp3AreaSpotPLReport) GetDataEx() (interface{}, error) { sData := make([]Ermcp3AreaSpotPLReport, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].calc() sData[i].BeginDate = r.BeginDate sData[i].EndDate = r.EndDate } return sData, err } // Ermcp3FinanceReport 财务报表 type Ermcp3FinanceReport struct { RECKONDATE string `json:"reckondate" xorm:"RECKONDATE"` // 日照时期(yyyyMMdd) CYCLETIME string `json:"cycletime" xorm:"CYCLETIME"` // 周期时间 月(YYYYMM) 季(YYYYQ) 年(YYYY) 周(YYYYIW) 全(0)【原值】 CYCLETYPE int32 `json:"cycletype" xorm:"CYCLETYPE"` // 周期类型 - 1:月 2:季 3:年 4:周 5:全报表【原值】 SELLPREINVOICEDAMOUNT float64 `json:"sellpreinvoicedamount" xorm:"SELLPREINVOICEDAMOUNT"` // 销售预开票额(预开票额) SELLUNINVOICEDAMOUNT float64 `json:"selluninvoicedamount" xorm:"SELLUNINVOICEDAMOUNT"` // 销售应开票额(应开票额) TODAYRECEIVESUM float64 `json:"todayreceivesum" xorm:"TODAYRECEIVESUM"` // 今收款合计 TODAYPAYSUM float64 `json:"todaypaysum" xorm:"TODAYPAYSUM"` // 今付款合计 UPDATETIME string `json:"updatetime" xorm:"UPDATETIME"` // 更新时间 AREAUSERID int64 `json:"areauserid" xorm:"AREAUSERID"` // 所属机构\交易用户ID CURRENCYID int32 `json:"currencyid" xorm:"CURRENCYID"` // 结算币种ID BIZTYPE int32 `json:"biztype" xorm:"BIZTYPE"` // 业务类型 - 1:套保 2:套利 BUYTODAYSETTLEAMOUNT float64 `json:"buytodaysettleamount" xorm:"BUYTODAYSETTLEAMOUNT"` // 采购今付款额(今付货款额) SELLTODAYSETTLEAMOUNT float64 `json:"selltodaysettleamount" xorm:"SELLTODAYSETTLEAMOUNT"` // 销售今收款额(今收货款额) BUYTODAYREFUNDAMOUNT float64 `json:"buytodayrefundamount" xorm:"BUYTODAYREFUNDAMOUNT"` // 采购今收退款额(今收退款额) SELLTODAYREFUNDAMOUNT float64 `json:"selltodayrefundamount" xorm:"SELLTODAYREFUNDAMOUNT"` // 销售今付退款额(今付退款额) SELLTODAYINVOICEAMOUNT float64 `json:"selltodayinvoiceamount" xorm:"SELLTODAYINVOICEAMOUNT"` // 销售今开票额(今开票额) BUYTODAYINVOICEAMOUNT float64 `json:"buytodayinvoiceamount" xorm:"BUYTODAYINVOICEAMOUNT"` // 采购今收票额(今收票额) BUYPREPAIDAMOUNT float64 `json:"buyprepaidamount" xorm:"BUYPREPAIDAMOUNT"` // 采购预付款额(预付货款额) BUYUNPAIDAMOUNT float64 `json:"buyunpaidamount" xorm:"BUYUNPAIDAMOUNT"` // 采购应付款额(应付货款额) BUYPREINVOICEDAMOUNT float64 `json:"buypreinvoicedamount" xorm:"BUYPREINVOICEDAMOUNT"` // 采购预收票额(预收票额) BUYUNINVOICEDAMOUNT float64 `json:"buyuninvoicedamount" xorm:"BUYUNINVOICEDAMOUNT"` // 采购应收票额(应收票额) SELLPREPAIDAMOUNT float64 `json:"sellprepaidamount" xorm:"SELLPREPAIDAMOUNT"` // 销售预收款额(预收货款额) SELLUNPAIDAMOUNT float64 `json:"sellunpaidamount" xorm:"SELLUNPAIDAMOUNT"` // 销售应收款额(应收货款额) WRFACTORTYPEID int64 `json:"wrfactortypeid" xorm:"WRFACTORTYPEID"` // 仓单要素类型ID(212+Unix秒时间戳(10位)+xxxxxx) DELIVERYGOODSID int64 `json:"deliverygoodsid" xorm:"DELIVERYGOODSID"` // 现货品种ID WRSTANDARDID int64 `json:"wrstandardid" xorm:"WRSTANDARDID"` // 现货商品ID SPOTGOODSBRANDID int32 `json:"spotgoodsbrandid" xorm:"SPOTGOODSBRANDID"` // 现货品牌ID DELIVERYGOODSCODE string `json:"deliverygoodscode" xorm:"'DELIVERYGOODSCODE'"` // 现货品种代码 DELIVERYGOODSNAME string `json:"deliverygoodsname" xorm:"'DELIVERYGOODSNAME'"` // 现货品种名称 WRSTANDARDCODE string `json:"wrstandardcode" xorm:"'WRSTANDARDCODE'"` // 品类代码 WRSTANDARDNAME string `json:"wrstandardname" xorm:"'WRSTANDARDNAME'"` // 品类名称 BRANDNAME string `json:"brandname" xorm:"'BRANDNAME'"` // 品牌名称 ACCOUNTNAME string `json:"accountname"` // 机构名称 CURRENCYNAME string `json:"currencyname"` // 币种名称 QueryType int32 `json:"-"` // 查询类型 1-报表 2-报表明细 BeginDate string `json:"begindate"` // 开始交易日 EndDate string `json:"enddate"` // 结束交易日 SumFields string `json:"-"` // 维度字段 } func (r *Ermcp3FinanceReport) calc() { r.CURRENCYNAME = mtpcache.GetCurrencyName(r.CURRENCYID) r.ACCOUNTNAME = mtpcache.GetUserNameByUserId(r.AREAUSERID) f := func(v *float64) { if *v < 0 { *v = 0 } } f(&r.BUYPREPAIDAMOUNT) f(&r.SELLPREINVOICEDAMOUNT) f(&r.BUYUNINVOICEDAMOUNT) f(&r.SELLPREPAIDAMOUNT) f(&r.BUYPREINVOICEDAMOUNT) } // getSumField 获取汇总维度字段 func (r *Ermcp3FinanceReport) getSumField() (fields []string) { fields = append(fields, "areauserid") fields = append(fields, "currencyid") if r.CYCLETYPE == 0 { // 日报表 fields = append(fields, "reckondate") } else { // 周期报表 fields = append(fields, "cycletype") fields = append(fields, "cycletime") } baseField := map[string]string{ "1": "deliverygoodsid", "2": "wrstandardid", "3": "spotgoodsbrandid", } var param []string if len(r.SumFields) > 0 { param = strings.Split(r.SumFields, ",") } for k, v := range baseField { for _, p := range param { if k == p { fields = append(fields, v) } } } return } func (r *Ermcp3FinanceReport) getExFieldInfo() (strFiled string, strleftJoin string) { if len(r.SumFields) > 0 { param := strings.Split(r.SumFields, ",") fHas := func(v string) bool { for i := range param { if param[i] == v { return true } } return false } if fHas("1") { if len(strFiled) > 0 { strFiled += "," } strFiled += "g.deliverygoodscode, g.deliverygoodsname, g.goodsunitid" strleftJoin += " LEFT JOIN deliverygoods g on a.deliverygoodsid = g.deliverygoodsid" } if fHas("2") { if len(strFiled) > 0 { strFiled += "," } strFiled += "w.wrstandardname, w.wrstandardcode, w.unitid" strleftJoin += " LEFT JOIN WRSTANDARD w on a.wrstandardid = w.wrstandardid" } if fHas("3") { if len(strFiled) > 0 { strFiled += "," } strFiled += "gb.dgfactoryitemvalue brandname" strleftJoin += " LEFT JOIN dgfactoryitem gb on a.spotgoodsbrandid = gb.dgfactoryitemid" } } return } func (r *Ermcp3FinanceReport) buildSql() string { fields := r.getSumField() if r.CYCLETYPE == 0 { // 查日照报 return r.buildSqlReckon(fields) } else { // 查报表表 return r.buildSqlReport(fields) } } func (r *Ermcp3FinanceReport) buildSqlReckon(fields []string) string { var sqlId, sqlParam utils.SQLVal var strFileds string for _, v := range fields { if len(strFileds) > 0 { strFileds += "," } strFileds += "t." + v } sqlParam.And("t.AREAUSERID", r.AREAUSERID) sqlParam.AndEx("t.RECKONDATE", r.RECKONDATE, len(r.RECKONDATE) > 0) if len(r.BeginDate) > 0 && len(r.EndDate) > 0 { sqlParam.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate)) } sqlParam.AndEx("t.CURRENCYID", r.CURRENCYID, r.CURRENCYID > 0) exFileds, strleftJoin := r.getExFieldInfo() if len(exFileds) > 0 { // 有左联查额外信息(RECKON_ERMCP_AFINANCESUB明细表, 注意是不同的表) sqlId = "select a.*, %v" + " from (SELECT %v," + " sum(t.BUYTODAYSETTLEAMOUNT) BUYTODAYSETTLEAMOUNT," + " sum(t.BUYTODAYREFUNDAMOUNT) BUYTODAYREFUNDAMOUNT," + " sum(t.BUYPREPAIDAMOUNT) BUYPREPAIDAMOUNT," + " sum(t.BUYUNPAIDAMOUNT) BUYUNPAIDAMOUNT," + " sum(t.BUYTODAYINVOICEAMOUNT) BUYTODAYINVOICEAMOUNT," + " sum(t.BUYPREINVOICEDAMOUNT) BUYPREINVOICEDAMOUNT," + " sum(t.BUYUNINVOICEDAMOUNT) BUYUNINVOICEDAMOUNT," + " sum(t.SELLTODAYSETTLEAMOUNT) SELLTODAYSETTLEAMOUNT," + " sum(t.SELLTODAYREFUNDAMOUNT) SELLTODAYREFUNDAMOUNT," + " sum(t.SELLPREPAIDAMOUNT) SELLPREPAIDAMOUNT," + " sum(t.SELLUNPAIDAMOUNT) SELLUNPAIDAMOUNT," + " sum(t.SELLTODAYINVOICEAMOUNT) SELLTODAYINVOICEAMOUNT," + " sum(t.SELLPREINVOICEDAMOUNT) SELLPREINVOICEDAMOUNT," + " sum(t.SELLUNINVOICEDAMOUNT) SELLUNINVOICEDAMOUNT," + " sum(t.TODAYRECEIVESUM) TODAYRECEIVESUM," + " sum(t.TODAYPAYSUM) TODAYPAYSUM" + " FROM RECKON_ERMCP_AFINANCESUB t" + " WHERE 1 = 1 %v" + " GROUP BY %v) a %v" sqlId.FormatParam(exFileds, strFileds, sqlParam.String(), strFileds, strleftJoin) } else { sqlId = "SELECT %v," + " sum(t.BUYTODAYSETTLEAMOUNT) BUYTODAYSETTLEAMOUNT," + " sum(t.BUYTODAYREFUNDAMOUNT) BUYTODAYREFUNDAMOUNT," + " sum(t.BUYPREPAIDAMOUNT) BUYPREPAIDAMOUNT," + " sum(t.BUYUNPAIDAMOUNT) BUYUNPAIDAMOUNT," + " sum(t.BUYTODAYINVOICEAMOUNT) BUYTODAYINVOICEAMOUNT," + " sum(t.BUYPREINVOICEDAMOUNT) BUYPREINVOICEDAMOUNT," + " sum(t.BUYUNINVOICEDAMOUNT) BUYUNINVOICEDAMOUNT," + " sum(t.SELLTODAYSETTLEAMOUNT) SELLTODAYSETTLEAMOUNT," + " sum(t.SELLTODAYREFUNDAMOUNT) SELLTODAYREFUNDAMOUNT," + " sum(t.SELLPREPAIDAMOUNT) SELLPREPAIDAMOUNT," + " sum(t.SELLUNPAIDAMOUNT) SELLUNPAIDAMOUNT," + " sum(t.SELLTODAYINVOICEAMOUNT) SELLTODAYINVOICEAMOUNT," + " sum(t.SELLPREINVOICEDAMOUNT) SELLPREINVOICEDAMOUNT," + " sum(t.SELLUNINVOICEDAMOUNT) SELLUNINVOICEDAMOUNT," + " sum(t.TODAYRECEIVESUM) TODAYRECEIVESUM," + " sum(t.TODAYPAYSUM) TODAYPAYSUM" + " FROM RECKON_ERMCP_AREAFINANCE t" + " WHERE 1 = 1 %v" + " GROUP BY %v" sqlId.FormatParam(strFileds, sqlParam.String(), strFileds) } return sqlId.String() } func (r *Ermcp3FinanceReport) buildSqlReport(fields []string) string { var sqlId, sqlParam utils.SQLVal var strFileds string for _, v := range fields { if len(strFileds) > 0 { strFileds += "," } strFileds += "t." + v } sqlParam.And("t.AREAUSERID", r.AREAUSERID) sqlParam.And("t.CYCLETYPE", r.CYCLETYPE) sqlParam.And("t.CYCLETIME", r.CYCLETIME) sqlParam.AndEx("t.CURRENCYID", r.CURRENCYID, r.CURRENCYID > 0) exFileds, strleftJoin := r.getExFieldInfo() if len(exFileds) > 0 { // 有左联查额外信息(REPORT_ERMCP_AFINANCESUB注意表名不同) sqlId = "select a.*, %v" + " from (SELECT %v," + " sum(t.BUYTODAYSETTLEAMOUNT) BUYTODAYSETTLEAMOUNT," + " sum(t.BUYTODAYREFUNDAMOUNT) BUYTODAYREFUNDAMOUNT," + " sum(t.BUYPREPAIDAMOUNT) BUYPREPAIDAMOUNT," + " sum(t.BUYUNPAIDAMOUNT) BUYUNPAIDAMOUNT," + " sum(t.BUYTODAYINVOICEAMOUNT) BUYTODAYINVOICEAMOUNT," + " sum(t.BUYPREINVOICEDAMOUNT) BUYPREINVOICEDAMOUNT," + " sum(t.BUYUNINVOICEDAMOUNT) BUYUNINVOICEDAMOUNT," + " sum(t.SELLTODAYSETTLEAMOUNT) SELLTODAYSETTLEAMOUNT," + " sum(t.SELLTODAYREFUNDAMOUNT) SELLTODAYREFUNDAMOUNT," + " sum(t.SELLPREPAIDAMOUNT) SELLPREPAIDAMOUNT," + " sum(t.SELLUNPAIDAMOUNT) SELLUNPAIDAMOUNT," + " sum(t.SELLTODAYINVOICEAMOUNT) SELLTODAYINVOICEAMOUNT," + " sum(t.SELLPREINVOICEDAMOUNT) SELLPREINVOICEDAMOUNT," + " sum(t.SELLUNINVOICEDAMOUNT) SELLUNINVOICEDAMOUNT," + " sum(t.TODAYRECEIVESUM) TODAYRECEIVESUM," + " sum(t.TODAYPAYSUM) TODAYPAYSUM" + " FROM REPORT_ERMCP_AFINANCESUB t" + " WHERE 1 = 1 %v" + " GROUP BY %v) a %v" sqlId.FormatParam(exFileds, strFileds, sqlParam.String(), strFileds, strleftJoin) } else { sqlId = "SELECT %v," + " sum(t.BUYTODAYSETTLEAMOUNT) BUYTODAYSETTLEAMOUNT," + " sum(t.BUYTODAYREFUNDAMOUNT) BUYTODAYREFUNDAMOUNT," + " sum(t.BUYPREPAIDAMOUNT) BUYPREPAIDAMOUNT," + " sum(t.BUYUNPAIDAMOUNT) BUYUNPAIDAMOUNT," + " sum(t.BUYTODAYINVOICEAMOUNT) BUYTODAYINVOICEAMOUNT," + " sum(t.BUYPREINVOICEDAMOUNT) BUYPREINVOICEDAMOUNT," + " sum(t.BUYUNINVOICEDAMOUNT) BUYUNINVOICEDAMOUNT," + " sum(t.SELLTODAYSETTLEAMOUNT) SELLTODAYSETTLEAMOUNT," + " sum(t.SELLTODAYREFUNDAMOUNT) SELLTODAYREFUNDAMOUNT," + " sum(t.SELLPREPAIDAMOUNT) SELLPREPAIDAMOUNT," + " sum(t.SELLUNPAIDAMOUNT) SELLUNPAIDAMOUNT," + " sum(t.SELLTODAYINVOICEAMOUNT) SELLTODAYINVOICEAMOUNT," + " sum(t.SELLPREINVOICEDAMOUNT) SELLPREINVOICEDAMOUNT," + " sum(t.SELLUNINVOICEDAMOUNT) SELLUNINVOICEDAMOUNT," + " sum(t.TODAYRECEIVESUM) TODAYRECEIVESUM," + " sum(t.TODAYPAYSUM) TODAYPAYSUM" + " FROM REPORT_ERMCP_AREAFINANCE t" + " WHERE 1 = 1 %v" + " GROUP BY %v" sqlId.FormatParam(strFileds, sqlParam.String(), strFileds) } return sqlId.String() } // GetDataEx 获取财务报表 func (r *Ermcp3FinanceReport) GetDataEx() (interface{}, error) { sData := make([]Ermcp3FinanceReport, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].calc() sData[i].BeginDate = r.BeginDate sData[i].EndDate = r.EndDate } return sData, err } // Ermcp3AreaStockReportEx 库存报表 type Ermcp3AreaStockReportEx struct { RECKONDATE string `json:"reckondate" xorm:"RECKONDATE"` // 日照时期(yyyyMMdd) CYCLETIME string `json:"cycletime" xorm:"CYCLETIME"` // 周期时间 月(YYYYMM) 季(YYYYQ) 年(YYYY) 周(YYYYIW) 全(0)【原值】 CYCLETYPE int32 `json:"cycletype" xorm:"CYCLETYPE"` // 周期类型 - 1:月 2:季 3:年 4:周 5:全报表【原值】 USERID int64 `json:"userid" xorm:"'USERID'"` // 机构ID WRSTANDARDID int32 `json:"wrstandardid" xorm:"'WRSTANDARDID'"` // 品类ID SPOTGOODSBRANDID int32 `json:"spotgoodsbrandid" xorm:"'SPOTGOODSBRANDID'"` // 现货品牌ID WAREHOUSEINFOID string `json:"warehouseinfoid" xorm:"'WAREHOUSEINFOID'"` // 仓库ID ORISTOCK float64 `json:"oristock" xorm:"'ORISTOCK'"` // 期初库存量 CURSTOCK float64 `json:"curstock" xorm:"'CURSTOCK'"` // 期末库存量 TODAYBUYINQTY float64 `json:"todaybuyinqty" xorm:"'TODAYBUYINQTY'"` // 今采购入库量 TODAYPRODUCEINQTY float64 `json:"todayproduceinqty" xorm:"'TODAYPRODUCEINQTY'"` // 今内部入库量 TODAYSELLOUTQTY float64 `json:"todayselloutqty" xorm:"'TODAYSELLOUTQTY'"` // 今销售出库量 TODAYPRODUCEOUTQTY float64 `json:"todayproduceoutqty" xorm:"'TODAYPRODUCEOUTQTY'"` // 今内部出库量 UPDATETIME string `json:"updatetime" xorm:"'UPDATETIME'"` // 更新时间 WRSTANDARDNAME string `json:"wrstandardname" xorm:"'WRSTANDARDNAME'"` // 品类名称 WRSTANDARDCODE string `json:"wrstandardcode" xorm:"'WRSTANDARDCODE'"` // 品类代码 UNITID int32 `json:"unitid" xorm:"'UNITID'"` // 品类单位id BRANDNAME string `json:"brandname" xorm:"'BRANDNAME'"` // 品牌名称 WAREHOUSENAME string `json:"warehousename" xorm:"'WAREHOUSENAME'"` // 仓库名称 WAREHOUSECODE string `json:"warehousecode" xorm:"'WAREHOUSECODE'"` // 仓库代码 WAREHOUSETYPE int32 `json:"warehousetype" xorm:"'WAREHOUSETYPE'"` // 仓库类型 - 1 厂库 2 自有库 3 合作库 USERNAME string `json:"username"` // 机构名称 ENUMDICNAME string `json:"enumdicname"` // 单位名称 DELIVERYGOODSID int32 `json:"deliverygoodsid" xorm:"'DELIVERYGOODSID'"` // 现货品种id DELIVERYGOODSCODE string `json:"deliverygoodscode" xorm:"'DELIVERYGOODSCODE'"` // 现货品种代码 DELIVERYGOODSNAME string `json:"deliverygoodsname" xorm:"'DELIVERYGOODSNAME'"` // 现货品种名称 GOODSUNITID int32 `json:"goodsunitid" xorm:"'GOODSUNITID'"` // 现货单位id UNBUYINQTY float64 `json:"unbuyinqty"` // 采购未入库量(数据库未找到相关字段?) UNSELLOUTQTY float64 `json:"unselloutqty"` // 销售未出库量(数据库未找到相关字段?) DiffQty float64 `json:"diffqty"` // 库存变化量 = 期末 - 期初 BeginDate string `json:"begindate"` // 开始交易日 EndDate string `json:"enddate"` // 结束交易日 SumFields string `json:"-"` // 汇总维度(逗号隔开) 1-品种 2-品类 3-品牌 4-仓库 } func (r *Ermcp3AreaStockReportEx) calc() { r.USERNAME = mtpcache.GetUserNameByUserId(r.USERID) r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID) r.DiffQty = r.CURSTOCK - r.ORISTOCK } // getSumField 获取汇总维度字段 func (r *Ermcp3AreaStockReportEx) getSumField() (fields []string, bSum bool) { fields = append(fields, "userid") if r.CYCLETYPE == 0 { // 日报表 fields = append(fields, "reckondate") } else { // 周期报表 fields = append(fields, "cycletype") fields = append(fields, "cycletime") } baseField := map[string]string{ "1": "deliverygoodsid", "2": "wrstandardid", "3": "spotgoodsbrandid", "4": "warehouseinfoid", } bSum = false var param []string if len(r.SumFields) > 0 { param = strings.Split(r.SumFields, ",") } if len(param) == 0 { // 全字段, 不需要汇总 for _, v := range baseField { fields = append(fields, v) } bSum = false } else { bIncludeAll := true // 判断是否包含所有维度 for k := range baseField { bExist := false for _, p := range param { if k == p { bExist = true break } } if !bExist { bIncludeAll = false break } } if bIncludeAll { // 包含了所有维度, 不需要汇总 for _, v := range baseField { fields = append(fields, v) } bSum = false } else { tmp := map[string]string{} for _, p := range param { if v, ok := baseField[p]; ok { // 利用map特性去重, 先保存在一个临时map中 tmp[p] = v bSum = true } } if bSum { for _, v := range tmp { fields = append(fields, v) } } } } return } func (r *Ermcp3AreaStockReportEx) getExFieldInfo() (strFiled string, strleftJoin string) { if len(r.SumFields) > 0 { param := strings.Split(r.SumFields, ",") fHas := func(v string) bool { for i := range param { if param[i] == v { return true } } return false } if fHas("1") { if len(strFiled) > 0 { strFiled += "," } strFiled += "g.deliverygoodscode, g.deliverygoodsname, g.goodsunitid" strleftJoin += " LEFT JOIN deliverygoods g on a.deliverygoodsid = g.deliverygoodsid" } if fHas("2") { if len(strFiled) > 0 { strFiled += "," } strFiled += "w.wrstandardname, w.wrstandardcode, w.unitid" strleftJoin += " LEFT JOIN WRSTANDARD w on a.wrstandardid = w.wrstandardid" } if fHas("3") { if len(strFiled) > 0 { strFiled += "," } strFiled += "gb.dgfactoryitemvalue brandname" strleftJoin += " LEFT JOIN dgfactoryitem gb on a.spotgoodsbrandid = gb.dgfactoryitemid" } if fHas("4") { if len(strFiled) > 0 { strFiled += "," } strFiled += "h.warehousecode, h.warehousename, h.warehousetype" strleftJoin += " LEFT JOIN WAREHOUSEINFO h on a.warehouseinfoid=h.autoid" } } return } func (r *Ermcp3AreaStockReportEx) buildSql() (sqlId string) { fields, bSum := r.getSumField() if r.CYCLETYPE == 0 { // 日报表 if bSum { sqlId = r.buildSqlReckonSum(fields) } else { sqlId = r.buildSqlReckon() } } else { // 周期报表 if bSum { sqlId = r.buildSqlReportSum(fields) } else { sqlId = r.buildSqlReport() } } return } // GetDataEx 获取库存报表 func (r *Ermcp3AreaStockReportEx) GetDataEx() (interface{}, error) { sData := make([]Ermcp3AreaStockReportEx, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].calc() sData[i].BeginDate = r.BeginDate sData[i].EndDate = r.EndDate } return sData, err } // buildSqlReckon 日照表 func (r *Ermcp3AreaStockReportEx) buildSqlReckon() string { var sqlId utils.SQLVal = "SELECT t.TODAYBUYINQTY," + " t.TODAYPRODUCEINQTY," + " t.reckondate," + " t.TODAYSELLOUTQTY," + " t.TODAYPRODUCEOUTQTY," + " to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," + " t.USERID," + " t.WRSTANDARDID," + " t.SPOTGOODSBRANDID," + " t.WAREHOUSEINFOID," + " t.deliverygoodsid," + " t.ORISTOCK," + " t.CURSTOCK," + " w.wrstandardname," + " w.wrstandardcode," + " w.unitid," + " gb.dgfactoryitemvalue brandname," + " h.warehousename," + " h.warehousecode," + " h.warehousetype," + " g.deliverygoodscode," + " g.deliverygoodsname," + " g.goodsunitid" + " FROM RECKON_ERMCP_AREASTOCK t" + " LEFT JOIN WRSTANDARD w" + " on t.wrstandardid = w.wrstandardid" + " LEFT JOIN dgfactoryitem gb" + " on t.spotgoodsbrandid = gb.dgfactoryitemid" + " LEFT JOIN deliverygoods g" + " on t.deliverygoodsid = g.deliverygoodsid" + " LEFT JOIN WAREHOUSEINFO h" + " on t.warehouseinfoid = h.autoid" + " WHERE 1 = 1" sqlId.And("t.USERID", r.USERID) sqlId.AndEx("t.RECKONDATE", r.RECKONDATE, len(r.RECKONDATE) > 0) if len(r.BeginDate) > 0 && len(r.EndDate) > 0 { sqlId.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate)) } // 现货商品id sqlId.AndEx("t.DELIVERYGOODSID", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0) // 品类id sqlId.AndEx("t.WRSTANDARDID", r.WRSTANDARDID, r.WRSTANDARDID > 0) // 品牌id sqlId.AndEx("t.SPOTGOODSBRANDID", r.SPOTGOODSBRANDID, r.SPOTGOODSBRANDID > 0) // 仓库id sqlId.AndEx("t.WAREHOUSEINFOID", r.WAREHOUSEINFOID, len(r.WAREHOUSEINFOID) > 0) sqlId.Join(" order by t.reckondate") return sqlId.String() } // buildSqlReckonSum 日照表(汇总维度) func (r *Ermcp3AreaStockReportEx) buildSqlReckonSum(fields []string) string { var sqlId utils.SQLVal = "SELECT a.*, %v" + " from (SELECT %v," + " sum(t.ORISTOCK) ORISTOCK," + " sum(t.CURSTOCK) CURSTOCK," + " sum(t.TODAYBUYINQTY) TODAYBUYINQTY," + " sum(t.TODAYPRODUCEINQTY) TODAYPRODUCEINQTY," + " sum(t.TODAYSELLOUTQTY) TODAYSELLOUTQTY," + " sum(t.TODAYPRODUCEOUTQTY) TODAYPRODUCEOUTQTY" + " FROM RECKON_ERMCP_AREASTOCK t" + " WHERE 1 = 1 %v" + " GROUP BY %v) a %v" var sqlParam utils.SQLVal sqlParam.And("t.USERID", r.USERID) sqlParam.AndEx("t.RECKONDATE", r.RECKONDATE, len(r.RECKONDATE) > 0) if len(r.BeginDate) > 0 && len(r.EndDate) > 0 { sqlParam.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate)) } // 现货商品id sqlParam.AndEx("t.DELIVERYGOODSID", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0) // 品类id sqlParam.AndEx("t.WRSTANDARDID", r.WRSTANDARDID, r.WRSTANDARDID > 0) // 品牌id sqlParam.AndEx("t.SPOTGOODSBRANDID", r.SPOTGOODSBRANDID, r.SPOTGOODSBRANDID > 0) // 仓库id sqlParam.AndEx("t.WAREHOUSEINFOID", r.WAREHOUSEINFOID, len(r.WAREHOUSEINFOID) > 0) var strFileds string for _, v := range fields { if len(strFileds) > 0 { strFileds += "," } strFileds += "t." + v } exFields, exLeftJoinTable := r.getExFieldInfo() sqlId.FormatParam(exFields, strFileds, sqlParam.String(), strFileds, exLeftJoinTable) sqlId.Join(" order by a.reckondate") return sqlId.String() } // buildSqlReport 报表表 func (r *Ermcp3AreaStockReportEx) buildSqlReport() string { var sqlId utils.SQLVal = "SELECT t.TODAYBUYINQTY," + " t.TODAYPRODUCEINQTY," + " t.cycletype," + " t.cycletime," + " t.USERID," + " t.WRSTANDARDID," + " t.SPOTGOODSBRANDID," + " t.WAREHOUSEINFOID," + " t.deliverygoodsid," + " t.TODAYSELLOUTQTY," + " t.TODAYPRODUCEOUTQTY," + " to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," + " t.ORISTOCK," + " t.CURSTOCK," + " w.wrstandardname," + " w.wrstandardcode," + " w.unitid," + " gb.dgfactoryitemvalue brandname," + " h.warehousename," + " h.warehousecode," + " h.warehousetype," + " g.deliverygoodscode," + " g.deliverygoodsname," + " g.goodsunitid" + " FROM REPORT_ERMCP_AREASTOCK t" + " LEFT JOIN WRSTANDARD w" + " on t.wrstandardid = w.wrstandardid" + " LEFT JOIN dgfactoryitem gb" + " on t.spotgoodsbrandid = gb.dgfactoryitemid" + " LEFT JOIN deliverygoods g" + " on t.deliverygoodsid = g.deliverygoodsid" + " LEFT JOIN WAREHOUSEINFO h" + " on t.warehouseinfoid = h.autoid" + " WHERE 1 = 1" sqlId.And("t.USERID", r.USERID) sqlId.And("t.cycletype", r.CYCLETYPE) sqlId.And("t.cycletime", r.CYCLETIME) // 现货商品id sqlId.AndEx("t.DELIVERYGOODSID", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0) // 品类id sqlId.AndEx("t.WRSTANDARDID", r.WRSTANDARDID, r.WRSTANDARDID > 0) // 品牌id sqlId.AndEx("t.SPOTGOODSBRANDID", r.SPOTGOODSBRANDID, r.SPOTGOODSBRANDID > 0) // 仓库id sqlId.AndEx("t.WAREHOUSEINFOID", r.WAREHOUSEINFOID, len(r.WAREHOUSEINFOID) > 0) return sqlId.String() } // buildSqlReport 报表表(汇总维度) func (r *Ermcp3AreaStockReportEx) buildSqlReportSum(fields []string) string { var sqlId utils.SQLVal = "SELECT a.*, %v" + " from (SELECT %v," + " sum(t.ORISTOCK) ORISTOCK," + " sum(t.CURSTOCK) CURSTOCK," + " sum(t.TODAYBUYINQTY) TODAYBUYINQTY," + " sum(t.TODAYPRODUCEINQTY) TODAYPRODUCEINQTY," + " sum(t.TODAYSELLOUTQTY) TODAYSELLOUTQTY," + " sum(t.TODAYPRODUCEOUTQTY) TODAYPRODUCEOUTQTY" + " FROM REPORT_ERMCP_AREASTOCK t" + " WHERE 1 = 1 %v" + " GROUP BY %v) a %v" var sqlParam utils.SQLVal sqlParam.And("t.USERID", r.USERID) sqlParam.And("t.cycletype", r.CYCLETYPE) sqlParam.And("t.cycletime", r.CYCLETIME) // 现货商品id sqlParam.AndEx("t.DELIVERYGOODSID", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0) // 品类id sqlParam.AndEx("t.WRSTANDARDID", r.WRSTANDARDID, r.WRSTANDARDID > 0) // 品牌id sqlParam.AndEx("t.SPOTGOODSBRANDID", r.SPOTGOODSBRANDID, r.SPOTGOODSBRANDID > 0) // 仓库id sqlParam.AndEx("t.WAREHOUSEINFOID", r.WAREHOUSEINFOID, len(r.WAREHOUSEINFOID) > 0) var strFileds string for _, v := range fields { if len(strFileds) > 0 { strFileds += "," } strFileds += "t." + v } exFields, exLeftJoinTable := r.getExFieldInfo() sqlId.FormatParam(exFields, strFileds, sqlParam.String(), strFileds, exLeftJoinTable) return sqlId.String() } // Ermcp3ArealSumPL 损益汇总表 type Ermcp3ArealSumPL struct { RECKONDATE string `json:"reckondate" xorm:"RECKONDATE"` // 日照时期(yyyyMMdd) CYCLETIME string `json:"cycletime" xorm:"'CYCLETIME'"` // 周期时间 月(YYYYMM) 季(YYYYQ) 年(YYYY) 周(YYYYIW) 全(0)【原值】 CYCLETYPE int32 `json:"cycletype" xorm:"'CYCLETYPE'"` // 周期类型 - 1:月 2:季 3:年 4:周 5:全报表【原值】 AREAUSERID int64 `json:"areauserid" xorm:"AREAUSERID"` // 所属机构\交易用户ID MIDDLEGOODSID int64 `json:"middlegoodsid" xorm:"MIDDLEGOODSID"` // 套保品种ID(SEQ_ERMS_MIDDLEGOODS) CURRENCYID int32 `json:"currencyid" xorm:"CURRENCYID"` // 结算币种ID SPOTACTUALPL float64 `json:"spotactualpl" xorm:"SPOTACTUALPL"` // 现货损益 SPOTFLOATPL float64 `json:"spotfloatpl" xorm:"SPOTFLOATPL"` // 现货浮动损益 FUTUREACTUALPL float64 `json:"futureactualpl" xorm:"FUTUREACTUALPL"` // 期货损益 FUTUREFLOATPL float64 `json:"futurefloatpl" xorm:"FUTUREFLOATPL"` // 期货浮动损益 SUMACTUALPL float64 `json:"sumactualpl" xorm:"SUMACTUALPL"` // 实际损益 = (ActualPL + FutureActualPL) SUMPL float64 `json:"sumpl" xorm:"'SUMPL'"` // 总敞口损益 | 汇总损益 UPDATETIME string `json:"updatetime" xorm:"UPDATETIME"` // 更新时间 MIDDLEGOODSNAME string `json:"middlegoodsname" xorm:"'MIDDLEGOODSNAME'"` // 套保品种名称 MIDDLEGOODSCODE string `json:"middlegoodscode" xorm:"'MIDDLEGOODSCODE'"` // 套保品种代码 GOODSUNITID int32 `json:"goodsunitid" xorm:"'GOODSUNITID'"` // 套保品种单位id TOTALSPOTQTY float64 `json:"totalspotqty" xorm:"'TOTALSPOTQTY'"` // 现货头寸总量 TOTALFUTUREQTY float64 `json:"totalfutureqty" xorm:"'TOTALFUTUREQTY'"` // 期货头寸总量 TOTALNEEDHEDGEQTY float64 `json:"totalneedhedgeqty" xorm:"'TOTALNEEDHEDGEQTY'"` // 应套保总量 FUTURESPOTRATIO float64 `json:"futurespotratio" xorm:"'FUTURESPOTRATIO'"` // 期现比例 = 期货头寸总量 / 现货头寸总量 HEDGEEXPOSUREPL float64 `json:"hedgeexposurepl" xorm:"'HEDGEEXPOSUREPL'"` // 套保敞口损益 ACCOUNTNAME string `json:"accountname"` // 机构名称 ENUMDICNAME string `json:"enumdicname"` // 单位名称(套保品种) CURRENCYNAME string `json:"currencyname"` // 币种名称 QueryType int32 `json:"-"` // 查询类型 1-报表 2-报表明细 BeginDate string `json:"begindate"` // 开始交易日 EndDate string `json:"enddate"` // 结束交易日 } func (r *Ermcp3ArealSumPL) calc() { r.CURRENCYNAME = mtpcache.GetCurrencyName(r.CURRENCYID) r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.GOODSUNITID) r.ACCOUNTNAME = mtpcache.GetUserNameByUserId(r.AREAUSERID) } func (r *Ermcp3ArealSumPL) buildSql() string { // 日报表 if r.QueryType == 1 && r.CYCLETYPE == 0 { return r.buildSqlDay() } // 日报表明细 if r.QueryType == 2 && r.CYCLETYPE == 0 { return r.buildSqlDayDetail() } return r.buildSqlCycle() } // buildSqlDay 损益汇总日报表(需要汇总维度) func (r *Ermcp3ArealSumPL) buildSqlDay() string { var sqlId utils.SQLVal = ` SELECT t.RECKONDATE, t.AREAUSERID, t.MIDDLEGOODSID, t.CURRENCYID, t.SPOTACTUALPL, t.SPOTFLOATPL, t.FUTUREACTUALPL, t.FUTUREFLOATPL, t.SUMACTUALPL, t.SUMPL, to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME, g.middlegoodsname, g.middlegoodscode, g.goodsunitid, t.totalspotqty, t.totalfutureqty, t.totalneedhedgeqty, t.futurespotratio, t.hedgeexposurepl FROM RECKON_ERMCP_AREASUMPL t LEFT JOIN ERMS_MIDDLEGOODS g on t.middlegoodsid = g.middlegoodsid WHERE 1 = 1 ` sqlId.And("t.AREAUSERID", r.AREAUSERID) if r.QueryType == 1 { sqlId.And("t.RECKONDATE", r.RECKONDATE) } else if r.QueryType == 2 { sqlId.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate)) } return sqlId.String() } func (r *Ermcp3ArealSumPL) buildSqlDayDetail() string { var sqlId utils.SQLVal = ` SELECT t.RECKONDATE, t.AREAUSERID, t.MIDDLEGOODSID, t.CURRENCYID, t.SPOTACTUALPL, t.SPOTFLOATPL, t.FUTUREACTUALPL, t.FUTUREFLOATPL, t.SUMACTUALPL, t.SUMPL, to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME, g.middlegoodsname, g.middlegoodscode, g.goodsunitid, t.totalspotqty, t.totalfutureqty, t.totalneedhedgeqty, t.futurespotratio, t.hedgeexposurepl FROM RECKON_ERMCP_AREASUMPL t LEFT JOIN ERMS_MIDDLEGOODS g on t.middlegoodsid = g.middlegoodsid WHERE 1 = 1 ` sqlId.And("t.AREAUSERID", r.AREAUSERID) sqlId.AndEx("t.RECKONDATE", r.RECKONDATE, len(r.RECKONDATE) > 0) if len(r.BeginDate) > 0 && len(r.EndDate) > 0 { sqlId.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate)) } sqlId.And("t.CURRENCYID", r.CURRENCYID) return sqlId.String() } func (r *Ermcp3ArealSumPL) buildSqlCycle() string { var sqlId utils.SQLVal = ` SELECT t.CYCLETYPE, t.CYCLETIME, t.AREAUSERID, t.MIDDLEGOODSID, t.CURRENCYID, t.SPOTACTUALPL, t.SPOTFLOATPL, t.FUTUREACTUALPL, t.FUTUREFLOATPL, t.SUMACTUALPL, t.SUMPL, to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME, g.middlegoodsname, g.middlegoodscode, g.goodsunitid, t.totalspotqty, t.totalfutureqty, t.totalneedhedgeqty, t.futurespotratio, t.hedgeexposurepl FROM REPORT_ERMCP_AREASUMPL t LEFT JOIN ERMS_MIDDLEGOODS g on t.middlegoodsid = g.middlegoodsid WHERE 1 = 1 ` sqlId.And("t.AREAUSERID", r.AREAUSERID) sqlId.And("t.cycletype", r.CYCLETYPE) sqlId.And("t.cycletime", r.CYCLETIME) return sqlId.String() } // GetDataEx 获取损益汇总表 func (r *Ermcp3ArealSumPL) GetDataEx() (interface{}, error) { sData := make([]Ermcp3ArealSumPL, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].calc() sData[i].BeginDate = r.BeginDate sData[i].EndDate = r.EndDate } return sData, err } // Ermcp3TaFutuReDataReport 期货报表 type Ermcp3TaFutuReDataReport struct { RECKONDATE string `json:"reckondate" xorm:"RECKONDATE"` // 日照时期(yyyyMMdd) CYCLETIME string `json:"cycletime" xorm:"CYCLETIME"` // 周期时间 月(YYYYMM) 季(YYYYQ) 年(YYYY) 周(YYYYIW) 全(0)【原值】 CYCLETYPE int32 `json:"cycletype" xorm:"CYCLETYPE"` // 周期类型 - 1:月 2:季 3:年 4:周 5:全报表【原值】 ACCOUNTID int64 `json:"accountid" xorm:"ACCOUNTID"` // 交易账户 GOODSID int32 `json:"goodsid" xorm:"GOODSID"` // 交易合约 BUYORSELL int32 `json:"buyorsell" xorm:"BUYORSELL"` // 持仓方向 CURRENCYID int32 `json:"currencyid" xorm:"CURRENCYID"` // 结算币种ID USERID int64 `json:"userid" xorm:"USERID"` // 账户所属用户ID RELATEDUSERID int64 `json:"relateduserid" xorm:"RELATEDUSERID"` // 账户关联用户ID GOODSGROUPID int32 `json:"goodsgroupid" xorm:"GOODSGROUPID"` // 交易品种 ORIHOLDQTY SFLOAT64 `json:"oriholdqty" xorm:"ORIHOLDQTY"` // 期初持仓量 ORIOPENAMOUNT float64 `json:"oriopenamount" xorm:"ORIOPENAMOUNT"` // 期初开仓额 ORIHOLDAMOUNT float64 `json:"oriholdamount" xorm:"ORIHOLDAMOUNT"` // 期初持仓额 TODAYOPENQTY SFLOAT64 `json:"todayopenqty" xorm:"TODAYOPENQTY"` // 今开仓量 TODAYOPENAMOUNT float64 `json:"todayopenamount" xorm:"TODAYOPENAMOUNT"` // 今开仓额 TODAYCLOSEQTY SFLOAT64 `json:"todaycloseqty" xorm:"TODAYCLOSEQTY"` // 今平仓量 TODAYCLOSEAMOUNT float64 `json:"todaycloseamount" xorm:"TODAYCLOSEAMOUNT"` // 今平仓额 HOLDQTY SFLOAT64 `json:"holdqty" xorm:"HOLDQTY"` // 期末持仓量 OPENAMOUNT float64 `json:"openamount" xorm:"OPENAMOUNT"` // 期末开仓额 HOLDAMOUNT float64 `json:"holdamount" xorm:"HOLDAMOUNT"` // 期末持仓额 RECKONPRICE float64 `json:"reckonprice" xorm:"RECKONPRICE"` // 结算价 RECKONHOLDAMOUNT float64 `json:"reckonholdamount" xorm:"RECKONHOLDAMOUNT"` // 结算持仓额 CHARGE float64 `json:"charge" xorm:"CHARGE"` // 手续费 CLOSEPL float64 `json:"closepl" xorm:"CLOSEPL"` // 平仓损益 RECKONPL float64 `json:"reckonpl" xorm:"RECKONPL"` // 结算损益 LASTPRICE float64 `json:"lastprice" xorm:"LASTPRICE"` // 最新价 LASTHOLDAMOUNT float64 `json:"lastholdamount" xorm:"LASTHOLDAMOUNT"` // 当前持仓额 FLOATPL float64 `json:"floatpl" xorm:"FLOATPL"` // 浮动损益 GOODSGROUPSPOTQTY float64 `json:"goodsgroupspotqty" xorm:"GOODSGROUPSPOTQTY"` // 交易品种期末量 (= 期末持仓量 * 合约乘数) TODAYGOODSGROUPSPOTQTY float64 `json:"todaygoodsgroupspotqty" xorm:"TODAYGOODSGROUPSPOTQTY"` // 交易品种变化量 (=（期末持仓量 - 期初持仓量）* 合约乘数) MIDDLEGOODSQTY float64 `json:"middlegoodsqty" xorm:"MIDDLEGOODSQTY"` // 套保品种期末量 (=交易品种期末量 * 期货品种折算系数) TODAYMIDDLEGOODSQTY float64 `json:"todaymiddlegoodsqty" xorm:"TODAYMIDDLEGOODSQTY"` // 套保品种变化量 (= 交易品种变化量*期货品种折算系数) RECKONPL2 float64 `json:"reckonpl2" xorm:"RECKONPL2"` // 结算逐笔盈亏 OUTERGROUPCODE string `json:"outergroupcode" xorm:"'OUTERGROUPCODE'"` // 交易品种代码 GOODSGROUPNAME string `json:"goodsgroupname" xorm:"'GOODSGROUPNAME'"` // 交易品种名称 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'"` // 商品合约单位 QTYDECIMALPLACE int `json:"qtydecimalplace" xorm:"'QTYDECIMALPLACE'"` // 成交量小数位 ACCOUNTNAME string `json:"accountname" xorm:"'ACCOUNTNAME'"` // 交易账号名称 MIDDLEGOODSID int32 `json:"middlegoodsid" xorm:"'MIDDLEGOODSID'"` // 套保品种id MIDDLEGOODSNAME string `json:"middlegoodsname" xorm:"'MIDDLEGOODSNAME'"` // 套保商品名称 MIDDLEGOODSCODE string `json:"middlgoodscode" xorm:"'MIDDLEGOODSCODE'"` // 套保商品代码 OPENAVGPRICE SFLOAT64 `json:"openavgprice"` // 开仓均价 CLOSEAVGPRICE SFLOAT64 `json:"closeavgprice"` // 平仓均价 ENUMDICNAME string `json:"enumdicname"` // 单位名称 CURRENCYNAME string `json:"currencyname"` // 币种名称 QueryType int32 `json:"-"` // 查询类型 1-报表 2-报表明细 BeginDate string `json:"begindate"` // 开始交易日 EndDate string `json:"enddate"` // 结束交易日 } func (r *Ermcp3TaFutuReDataReport) calc() { if r.TODAYOPENQTY != 0 && r.AGREEUNIT > 1e-8 { r.TODAYOPENQTY.Power10(r.QTYDECIMALPLACE * -1) r.OPENAVGPRICE = SFLOAT64(r.TODAYOPENAMOUNT / r.TODAYOPENQTY.Float64() / r.AGREEUNIT) r.OPENAVGPRICE.Round(3) } if r.TODAYCLOSEQTY != 0 && r.AGREEUNIT > 1e-8 { r.TODAYCLOSEQTY.Power10(r.QTYDECIMALPLACE * -1) r.CLOSEAVGPRICE = SFLOAT64(r.TODAYCLOSEAMOUNT / r.TODAYCLOSEQTY.Float64() / r.AGREEUNIT) r.CLOSEAVGPRICE.Round(3) if r.CLOSEAVGPRICE < 1e-10 { // #3073 // 报表中的平仓数量改为负数了, 所以如果算出来的均价是负数需要取反 r.CLOSEAVGPRICE *= -1 } } r.HOLDQTY.Power10(r.QTYDECIMALPLACE * -1) r.ORIHOLDQTY.Power10(r.QTYDECIMALPLACE * -1) r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.GOODUNITID) r.CURRENCYNAME = mtpcache.GetCurrencyName(r.CURRENCYID) } func (r *Ermcp3TaFutuReDataReport) userType() int32 { var userType int32 = 2 areaUserId := mtpcache.GetAreaUserId(r.USERID, 0) if areaUserId == r.USERID { userType = 2 } else { userType = 7 } return userType } func (r *Ermcp3TaFutuReDataReport) buildSql() string { userType := r.userType() // 日报表 if r.QueryType == 1 && r.CYCLETYPE == 0 { return r.buildSqlReckon(userType) } // 日报表明细 if r.QueryType == 2 && r.CYCLETYPE == 0 { return r.buildSqlReckonDetail(userType) } return r.buildSqlReport(userType) } // buildSqlReckon 日照表 func (r *Ermcp3TaFutuReDataReport) buildSqlReckon(userType int32) string { var sqlId utils.SQLVal = "select a.*," + " gp.outergroupcode," + " gp.goodsgroupname," + " g.goodscode," + " g.goodsname," + " g.goodunitid," + " g.agreeunit," + " g.qtydecimalplace," + " mg.middlegoodsid," + " mg.middlegoodscode," + " mg.middlegoodsname" + " from (SELECT t.RECKONDATE," + " t.GOODSID," + " t.BUYORSELL," + " t.CURRENCYID," + " t.%v USERID," + " t.GOODSGROUPID," + " t.middlegoodsid," + " sum(t.ORIHOLDQTY) ORIHOLDQTY," + " sum(t.ORIOPENAMOUNT) ORIOPENAMOUNT," + " sum(t.ORIHOLDAMOUNT) ORIHOLDAMOUNT," + " sum(t.TODAYOPENQTY) TODAYOPENQTY," + " sum(t.TODAYOPENAMOUNT) TODAYOPENAMOUNT," + " sum(t.TODAYCLOSEQTY) TODAYCLOSEQTY," + " sum(t.TODAYCLOSEAMOUNT) TODAYCLOSEAMOUNT," + " sum(t.HOLDQTY) HOLDQTY," + " sum(t.OPENAMOUNT) OPENAMOUNT," + " sum(t.HOLDAMOUNT) HOLDAMOUNT," + " sum(t.RECKONPRICE) RECKONPRICE," + " sum(t.RECKONHOLDAMOUNT) RECKONHOLDAMOUNT," + " sum(t.CHARGE) CHARGE," + " sum(t.CLOSEPL) CLOSEPL," + " sum(t.RECKONPL) RECKONPL," + " max(t.LASTPRICE) LASTPRICE," + " sum(t.LASTHOLDAMOUNT) LASTHOLDAMOUNT," + " sum(t.FLOATPL) FLOATPL," + " sum(t.GOODSGROUPSPOTQTY) GOODSGROUPSPOTQTY," + " sum(t.TODAYGOODSGROUPSPOTQTY) TODAYGOODSGROUPSPOTQTY," + " sum(t.MIDDLEGOODSQTY) MIDDLEGOODSQTY," + " sum(t.TODAYMIDDLEGOODSQTY) TODAYMIDDLEGOODSQTY," + " sum(t.Reckonpl2) RECKONPL2" + " FROM RECKON_ERMCP_TAFUTUREDATA t" + " WHERE 1 = 1 %v" + " GROUP BY t.RECKONDATE," + " t.GOODSID," + " t.BUYORSELL," + " t.CURRENCYID," + " t.%v," + " t.middlegoodsid," + " t.GOODSGROUPID) a" + " left join goodsgroup gp" + " on a.goodsgroupid = gp.goodsgroupid" + " left join goods g" + " on a.goodsid = g.goodsid" + " left join erms_middlegoods mg on a.middlegoodsid=mg.middlegoodsid" userId := "USERID" var sqlParam utils.SQLVal if userType == 2 { sqlParam.AndEx("t.userid", r.USERID, userType == 2) userId = "USERID" } else if userType == 7 { sqlParam.AndEx("t.relateduserid", r.USERID, userType == 7) userId = "RELATEDUSERID" } sqlParam.AndEx("t.reckondate", r.RECKONDATE, len(r.RECKONDATE) > 0) if len(r.BeginDate) > 0 && len(r.EndDate) > 0 { sqlParam.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate)) } sqlId.FormatParam(userId, sqlParam.String(), userId) return sqlId.String() } // buildSqlReckon 日照表明细 func (r *Ermcp3TaFutuReDataReport) buildSqlReckonDetail(userType int32) string { var sqlId utils.SQLVal = "select a.*," + " gp.outergroupcode," + " gp.goodsgroupname," + " g.goodscode," + " g.goodsname," + " g.goodunitid," + " g.agreeunit," + " g.qtydecimalplace," + " ta.accountname," + " mg.middlegoodsid," + " mg.middlegoodscode," + " mg.middlegoodsname" + " from (SELECT t.reckondate," + " t.GOODSID," + " t.BUYORSELL," + " t.CURRENCYID," + " t.%v USERID," + " t.accountid," + " t.GOODSGROUPID," + " t.middlegoodsid," + " sum(t.ORIHOLDQTY) ORIHOLDQTY," + " sum(t.ORIOPENAMOUNT) ORIOPENAMOUNT," + " sum(t.ORIHOLDAMOUNT) ORIHOLDAMOUNT," + " sum(t.TODAYOPENQTY) TODAYOPENQTY," + " sum(t.TODAYOPENAMOUNT) TODAYOPENAMOUNT," + " sum(t.TODAYCLOSEQTY) TODAYCLOSEQTY," + " sum(t.TODAYCLOSEAMOUNT) TODAYCLOSEAMOUNT," + " sum(t.HOLDQTY) HOLDQTY," + " sum(t.OPENAMOUNT) OPENAMOUNT," + " sum(t.HOLDAMOUNT) HOLDAMOUNT," + " sum(t.RECKONPRICE) RECKONPRICE," + " sum(t.RECKONHOLDAMOUNT) RECKONHOLDAMOUNT," + " sum(t.CHARGE) CHARGE," + " sum(t.CLOSEPL) CLOSEPL," + " sum(t.RECKONPL) RECKONPL," + " max(t.LASTPRICE) LASTPRICE," + " sum(t.LASTHOLDAMOUNT) LASTHOLDAMOUNT," + " sum(t.FLOATPL) FLOATPL," + " sum(t.GOODSGROUPSPOTQTY) GOODSGROUPSPOTQTY," + " sum(t.TODAYGOODSGROUPSPOTQTY) TODAYGOODSGROUPSPOTQTY," + " sum(t.MIDDLEGOODSQTY) MIDDLEGOODSQTY," + " sum(t.TODAYMIDDLEGOODSQTY) TODAYMIDDLEGOODSQTY," + " sum(t.Reckonpl2) RECKONPL2" + " FROM RECKON_ERMCP_TAFUTUREDATA t" + " WHERE 1 = 1 %v" + " GROUP BY t.reckondate," + " t.GOODSID," + " t.BUYORSELL," + " t.CURRENCYID," + " t.%v," + " t.accountid," + " t.middlegoodsid," + " t.GOODSGROUPID) a" + " left join goodsgroup gp" + " on a.goodsgroupid = gp.goodsgroupid" + " left join goods g" + " on a.goodsid = g.goodsid" + " left join taaccount ta on a.accountid = ta.accountid" + " left join erms_middlegoods mg on a.middlegoodsid=mg.middlegoodsid" userId := "USERID" var sqlParam utils.SQLVal if userType == 2 { sqlParam.AndEx("t.userid", r.USERID, userType == 2) userId = "USERID" } else if userType == 7 { sqlParam.AndEx("t.relateduserid", r.USERID, userType == 7) userId = "RELATEDUSERID" } sqlParam.And("t.CURRENCYID", r.CURRENCYID) sqlParam.And("t.GOODSGROUPID", r.GOODSGROUPID) sqlParam.And("t.GOODSID", r.GOODSID) sqlParam.And("t.BUYORSELL", r.BUYORSELL) sqlParam.AndEx("t.reckondate", r.RECKONDATE, len(r.RECKONDATE) > 0) if len(r.BeginDate) > 0 && len(r.EndDate) > 0 { sqlParam.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate)) } sqlId.FormatParam(userId, sqlParam.String(), userId) return sqlId.String() } // buildSqlReckon 报表表 func (r *Ermcp3TaFutuReDataReport) buildSqlReport(userType int32) string { var sqlId utils.SQLVal = "select a.*," + " gp.outergroupcode," + " gp.goodsgroupname," + " g.goodscode," + " g.goodsname," + " g.goodunitid," + " g.agreeunit," + " g.qtydecimalplace," + " mg.middlegoodsid," + " mg.middlegoodscode," + " mg.middlegoodsname" + " from (SELECT t.cycletype," + " t.cycletime," + " t.GOODSID," + " t.BUYORSELL," + " t.CURRENCYID," + " t.%v USERID," + " t.GOODSGROUPID," + " t.middlegoodsid," + " sum(t.ORIHOLDQTY) ORIHOLDQTY," + " sum(t.ORIOPENAMOUNT) ORIOPENAMOUNT," + " sum(t.ORIHOLDAMOUNT) ORIHOLDAMOUNT," + " sum(t.TODAYOPENQTY) TODAYOPENQTY," + " sum(t.TODAYOPENAMOUNT) TODAYOPENAMOUNT," + " sum(t.TODAYCLOSEQTY) TODAYCLOSEQTY," + " sum(t.TODAYCLOSEAMOUNT) TODAYCLOSEAMOUNT," + " sum(t.HOLDQTY) HOLDQTY," + " sum(t.OPENAMOUNT) OPENAMOUNT," + " sum(t.HOLDAMOUNT) HOLDAMOUNT," + " sum(t.RECKONPRICE) RECKONPRICE," + " sum(t.RECKONHOLDAMOUNT) RECKONHOLDAMOUNT," + " sum(t.CHARGE) CHARGE," + " sum(t.CLOSEPL) CLOSEPL," + " sum(t.RECKONPL) RECKONPL," + " max(t.LASTPRICE) LASTPRICE," + " sum(t.LASTHOLDAMOUNT) LASTHOLDAMOUNT," + " sum(t.FLOATPL) FLOATPL," + " sum(t.GOODSGROUPSPOTQTY) GOODSGROUPSPOTQTY," + " sum(t.TODAYGOODSGROUPSPOTQTY) TODAYGOODSGROUPSPOTQTY," + " sum(t.MIDDLEGOODSQTY) MIDDLEGOODSQTY," + " sum(t.TODAYMIDDLEGOODSQTY) TODAYMIDDLEGOODSQTY," + " sum(t.Reckonpl2) RECKONPL2" + " FROM REPORT_ERMCP_TAFUTUREDATA t" + " WHERE 1 = 1 %v" + " GROUP BY t.cycletype," + " t.cycletime," + " t.GOODSID," + " t.BUYORSELL," + " t.CURRENCYID," + " t.%v," + " t.middlegoodsid," + " t.GOODSGROUPID) a" + " left join goodsgroup gp" + " on a.goodsgroupid = gp.goodsgroupid" + " left join goods g" + " on a.goodsid = g.goodsid" + " left join erms_middlegoods mg on a.middlegoodsid=mg.middlegoodsid" userId := "USERID" var sqlParam utils.SQLVal if userType == 2 { sqlParam.AndEx("t.userid", r.USERID, userType == 2) userId = "USERID" } else if userType == 7 { sqlParam.AndEx("t.relateduserid", r.USERID, userType == 7) userId = "RELATEDUSERID" } sqlParam.And("t.cycletype", r.CYCLETYPE) sqlParam.And("t.cycletime", r.CYCLETIME) sqlId.FormatParam(userId, sqlParam.String(), userId) return sqlId.String() } // GetDataEx 获取期货报表 func (r *Ermcp3TaFutuReDataReport) GetDataEx() (interface{}, error) { sData := make([]Ermcp3TaFutuReDataReport, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].calc() sData[i].BeginDate = r.BeginDate sData[i].EndDate = r.EndDate } return sData, err } // ReckonDayPosition 持仓头寸日照 type ReckonDayPosition struct { ACCOUNTID int64 `json:"accountid" xorm:"ACCOUNTID"` // 账号Id GOODSID int32 `json:"goodsid" xorm:"GOODSID"` // 商品Id RECKONDATE string `json:"reckondate" xorm:"RECKONDATE"` // 日照日期(yyyyMMdd) BUYPOSITIONQTY int32 `json:"buypositionqty" xorm:"BUYPOSITIONQTY"` // 买期初持仓数量 BUYHOLDERAMOUNT float64 `json:"buyholderamount" xorm:"BUYHOLDERAMOUNT"` // 买持仓期初总金额 BUYCURPOSITIONQTY int32 `json:"buycurpositionqty" xorm:"BUYCURPOSITIONQTY"` // 买当前持仓总数量 BUYCURHOLDERAMOUNT float64 `json:"buycurholderamount" xorm:"BUYCURHOLDERAMOUNT"` // 买当前持仓总金额 BUYFROZENQTY int32 `json:"buyfrozenqty" xorm:"BUYFROZENQTY"` // 买持仓冻结 BUYOTHERFROZENQTY int32 `json:"buyotherfrozenqty" xorm:"BUYOTHERFROZENQTY"` // 买持仓其他冻结(交割冻结) BUYOPENREQQTY int32 `json:"buyopenreqqty" xorm:"BUYOPENREQQTY"` // 买开仓申请数量 BUYRECKONPL float64 `json:"buyreckonpl" xorm:"BUYRECKONPL"` // 买结算盈亏 BUYINTEREST float64 `json:"buyinterest" xorm:"BUYINTEREST"` // 买递延费(仓储费) BUYUSEDMARGIN float64 `json:"buyusedmargin" xorm:"BUYUSEDMARGIN"` // 占用保证金 BUYOPENTOTALQTY int32 `json:"buyopentotalqty" xorm:"BUYOPENTOTALQTY"` // 今日买开仓总数量 BUYCLOSETOTALQTY int32 `json:"buyclosetotalqty" xorm:"BUYCLOSETOTALQTY"` // 今日买平仓总数量 BUYCLOSETOTALPL float64 `json:"buyclosetotalpl" xorm:"BUYCLOSETOTALPL"` // 买平仓总盈亏 SELLPOSITIONQTY int32 `json:"sellpositionqty" xorm:"SELLPOSITIONQTY"` // 卖期初持仓数量 SELLHOLDERAMOUNT float64 `json:"sellholderamount" xorm:"SELLHOLDERAMOUNT"` // 卖持仓期初总金额 SELLCURPOSITIONQTY int32 `json:"sellcurpositionqty" xorm:"SELLCURPOSITIONQTY"` // 卖当前持仓数量 SELLCURHOLDERAMOUNT float64 `json:"sellcurholderamount" xorm:"SELLCURHOLDERAMOUNT"` // 卖当前持仓总金额 SELLFROZENQTY int32 `json:"sellfrozenqty" xorm:"SELLFROZENQTY"` // 卖持仓冻结 SELLOTHERFROZENQTY int32 `json:"sellotherfrozenqty" xorm:"SELLOTHERFROZENQTY"` // 卖持仓其他冻结(交割冻结) SELLOPENREQQTY int32 `json:"sellopenreqqty" xorm:"SELLOPENREQQTY"` // 卖开仓申请数量 SELLRECKONPL float64 `json:"sellreckonpl" xorm:"SELLRECKONPL"` // 卖结算盈亏 SELLINTEREST float64 `json:"sellinterest" xorm:"SELLINTEREST"` // 卖递延费 SELLUSEDMARGIN float64 `json:"sellusedmargin" xorm:"SELLUSEDMARGIN"` // 卖占用保证金-作废 SELLOPENTOTALQTY int32 `json:"sellopentotalqty" xorm:"SELLOPENTOTALQTY"` // 卖开仓总数量 SELLCLOSETOTALQTY int32 `json:"sellclosetotalqty" xorm:"SELLCLOSETOTALQTY"` // 卖平仓总数量 SELLCLOSETOTALPL float64 `json:"sellclosetotalpl" xorm:"SELLCLOSETOTALPL"` // 卖平仓总盈亏 TRADEPROPERTY int32 `json:"tradeproperty" xorm:"TRADEPROPERTY"` // 交易属性 MARGINALGORITHM int32 `json:"marginalgorithm" xorm:"MARGINALGORITHM"` // 保证金收取方式 - 1：比率 2：固定 MARGINVALUE float64 `json:"marginvalue" xorm:"MARGINVALUE"` // 保证金设置值(投资管理系统-多LongMarginValue) BUYFEEALGORITHM int32 `json:"buyfeealgorithm" xorm:"BUYFEEALGORITHM"` // 买递延费收取方式 - 1:比率 2:固定 BUYMEMBERFEEVALUE float64 `json:"buymemberfeevalue" xorm:"BUYMEMBERFEEVALUE"` // 买会员递延费设置值 BUYEXCHAGEFEEVALUE float64 `json:"buyexchagefeevalue" xorm:"BUYEXCHAGEFEEVALUE"` // 买交易所递延费设置值 SELLFEEALGORITHM int32 `json:"sellfeealgorithm" xorm:"SELLFEEALGORITHM"` // 卖递延费收取方式 - 1:比率 2:固定 SELLMEMBERFEEVALUE float64 `json:"sellmemberfeevalue" xorm:"SELLMEMBERFEEVALUE"` // 卖会员递延费设置值 SELLEXCHAGEFEEVALUE float64 `json:"sellexchagefeevalue" xorm:"SELLEXCHAGEFEEVALUE"` // 卖交易所递延费设置值 INTEREST2 float64 `json:"interest2" xorm:"INTEREST2"` // 过夜费 ACCOUNTCURRENCYID int32 `json:"accountcurrencyid" xorm:"ACCOUNTCURRENCYID"` // 账户币种ID GOODSCURRENCYID int32 `json:"goodscurrencyid" xorm:"GOODSCURRENCYID"` // 商品币种ID CUREXCHANGERATE float64 `json:"curexchangerate" xorm:"CUREXCHANGERATE"` // 当前汇率 BUYRECKONPL2 float64 `json:"buyreckonpl2" xorm:"BUYRECKONPL2"` // 买结算盈亏(逐笔) - 根据持仓单日照表字段汇率 SELLRECKONPL2 float64 `json:"sellreckonpl2" xorm:"SELLRECKONPL2"` // 结算盈亏(逐笔) - 根据持仓单日照表字段汇率 BUYCURTDPOSITION int32 `json:"buycurtdposition" xorm:"BUYCURTDPOSITION"` // 买期末今日头寸 BUYFRETDPOSITION int32 `json:"buyfretdposition" xorm:"BUYFRETDPOSITION"` // 买冻结今日头寸 SELLCURTDPOSITION int32 `json:"sellcurtdposition" xorm:"SELLCURTDPOSITION"` // 卖期末今日头寸 SELLFRETDPOSITION int32 `json:"sellfretdposition" xorm:"SELLFRETDPOSITION"` // 卖冻结今日头寸 INTERESTCHARGE float64 `json:"interestcharge" xorm:"INTERESTCHARGE"` // 利息(110) SHORTMARGINVALUE float64 `json:"shortmarginvalue" xorm:"SHORTMARGINVALUE"` // 保证金设置值(投资管理系统-空) BUYDEFERCHARGE float64 `json:"buydefercharge" xorm:"BUYDEFERCHARGE"` // 买递延费2(仓储费2) SELLDEFERCHARGE float64 `json:"selldefercharge" xorm:"SELLDEFERCHARGE"` // 卖递延费2 RELATEDUSERID int64 `json:"relateduserid" xorm:"'RELATEDUSERID'"` // 关联用户id MIDDLEGOODSID int32 `json:"middlegoodsid" xorm:"'MIDDLEGOODSID'"` // 套保商品id MIDDLEGOODSCODE string `json:"middlegoodscode" xorm:"'MIDDLEGOODSCODE'"` // 套保商品代码 MIDDLEGOODSNAME string `json:"middlegoodsname" xorm:"'MIDDLEGOODSNAME'"` // 套保商品名称 NEEDHEDGERATIO float64 `json:"needhedgeratio" xorm:"'NEEDHEDGERATIO'"` // 应套保比例 NEEDARBITRAGERATIO float64 `json:"needarbitrageratio" xorm:"'NEEDARBITRAGERATIO'"` // 应套利比例 GOODSGROUPID int32 `json:"goodsgroupid" xorm:"'GOODSGROUPID'"` // 交易品种id CONVERTRATIO float64 `json:"convertratio" xorm:"'CONVERTRATIO'"` // 折算系数 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'"` // 合约乘数 ENUMDICNAME string `json:"enumdicname"` // 单位名称(交易商品) ACCOUNTNAME string `json:"accountname" xorm:"'ACCOUNTNAME'"` // 交易账户 DiffBuyQty float64 `json:"diffbuyqty"` // 交易品种今变化量(买) DiffMgBuyQty float64 `json:"diffmgbuyqty"` // 套保品种今变化量(买) DiffNeedMgBuyQty float64 `json:"diffneedmgbuyqty"` // 应套保总量变化量(买) DiffSellQty float64 `json:"diffsellqty"` // 交易品种今变化量(卖) DiffMgSellQty float64 `json:"diffmgsellqty"` // 套保品种今变化量(卖) DiffNeedMgSellQty float64 `json:"diffneedmgsellqty"` // 应套保总量变化量(卖) BeginDate string `json:"begindate"` // 开始交易日 EndDate string `json:"enddate"` // 结束交易日 } func (r *ReckonDayPosition) calc() { r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.GOODUNITID) if r.BUYPOSITIONQTY > 0 || r.BUYCURPOSITIONQTY > 0 { r.DiffBuyQty = float64(r.BUYCURPOSITIONQTY) * r.AGREEUNIT r.DiffMgBuyQty = float64(r.BUYCURPOSITIONQTY) * r.AGREEUNIT * r.CONVERTRATIO r.DiffNeedMgBuyQty = float64(r.BUYCURPOSITIONQTY) * r.AGREEUNIT * r.CONVERTRATIO * r.NEEDHEDGERATIO } if r.SELLPOSITIONQTY > 0 || r.SELLCURPOSITIONQTY > 0 { r.DiffBuyQty = float64(r.SELLCURPOSITIONQTY) * r.AGREEUNIT r.DiffMgBuyQty = float64(r.SELLCURPOSITIONQTY) * r.AGREEUNIT * r.CONVERTRATIO r.DiffNeedMgBuyQty = float64(r.SELLCURPOSITIONQTY) * r.AGREEUNIT * r.CONVERTRATIO * r.NEEDHEDGERATIO } } func (r *ReckonDayPosition) buildSql() string { var sqlId utils.SQLVal = "with tmp as" + " (select t.middlegoodsid," + " t.middlegoodscode," + " t.middlegoodsname," + " t.needhedgeratio," + " t.needarbitrageratio," + " t.goodsgroupid," + " c.convertratio," + " g.goodsid," + " g.goodscode," + " g.goodsname," + " g.agreeunit," + " g.goodunitid" + " from erms_middlegoods t" + " inner join ermcp_ggconvertconfig c" + " on t.goodsgroupid = c.destgoodsgroupid" + " inner join goods g" + " on g.goodsgroupid = c.srcgoodsgroupid)" + "SELECT t.ACCOUNTID," + " t.GOODSID," + " t.RECKONDATE," + " t.BUYPOSITIONQTY," + " t.BUYHOLDERAMOUNT," + " t.BUYCURPOSITIONQTY," + " t.BUYCURHOLDERAMOUNT," + " t.BUYFROZENQTY," + " t.BUYOTHERFROZENQTY," + " t.BUYOPENREQQTY," + " t.BUYRECKONPL," + " t.BUYINTEREST," + " t.BUYUSEDMARGIN," + " t.BUYOPENTOTALQTY," + " t.BUYCLOSETOTALQTY," + " t.BUYCLOSETOTALPL," + " t.SELLPOSITIONQTY," + " t.SELLHOLDERAMOUNT," + " t.SELLCURPOSITIONQTY," + " t.SELLCURHOLDERAMOUNT," + " t.SELLFROZENQTY," + " t.SELLOTHERFROZENQTY," + " t.SELLOPENREQQTY," + " t.SELLRECKONPL," + " t.SELLINTEREST," + " t.SELLUSEDMARGIN," + " t.SELLOPENTOTALQTY," + " t.SELLCLOSETOTALQTY," + " t.SELLCLOSETOTALPL," + " t.TRADEPROPERTY," + " t.MARGINALGORITHM," + " t.MARGINVALUE," + " t.BUYFEEALGORITHM," + " t.BUYMEMBERFEEVALUE," + " t.BUYEXCHAGEFEEVALUE," + " t.SELLFEEALGORITHM," + " t.SELLMEMBERFEEVALUE," + " t.SELLEXCHAGEFEEVALUE," + " t.INTEREST2," + " t.ACCOUNTCURRENCYID," + " t.GOODSCURRENCYID," + " t.CUREXCHANGERATE," + " t.BUYRECKONPL2," + " t.SELLRECKONPL2," + " t.BUYCURTDPOSITION," + " t.BUYFRETDPOSITION," + " t.SELLCURTDPOSITION," + " t.SELLFRETDPOSITION," + " t.INTERESTCHARGE," + " t.SHORTMARGINVALUE," + " t.BUYDEFERCHARGE," + " t.SELLDEFERCHARGE," + " ta.relateduserid," + " ta.accountname," + " tmp.*" + " FROM RECKON_DAYPOSITION t" + " INNER JOIN tmp" + " on t.goodsid = tmp.goodsid" + " INNER JOIN TAACCOUNT ta" + " on t.accountid = ta.accountid" + " WHERE 1 = 1" + " and (t.buypositionqty > 0 or t.buycurpositionqty > 0 or t.SELLPOSITIONQTY > 0 or t.SELLCURPOSITIONQTY > 0)" sqlId.And("ta.RELATEDUSERID", r.RELATEDUSERID) sqlId.And("tmp.MIDDLEGOODSID", r.MIDDLEGOODSID) sqlId.AndEx("t.RECKONDATE", r.RECKONDATE, len(r.RECKONDATE) > 0) if len(r.BeginDate) > 0 && len(r.EndDate) > 0 { sqlId.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate)) } return sqlId.String() } // GetDataEx 获取持仓头寸日照 func (r *ReckonDayPosition) GetDataEx() ([]ReckonDayPosition, error) { sData := make([]ReckonDayPosition, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].calc() } return sData, err } // HISOUTTRADEPOSITION 外部头寸日照 type HISOUTTRADEPOSITION struct { HISTRADEDATE string `json:"histradedate" xorm:"'HISTRADEDATE'"` // 日期 ISVALIDDATA int32 `json:"isvaliddata" xorm:"'ISVALIDDATA'"` // 是否有效 ACCOUNTID int64 `json:"accountid" xorm:"ACCOUNTID"` // 资金账号[外部母账户] HEDGEGOODSID int32 `json:"hedgegoodsid" xorm:"HEDGEGOODSID"` // 对冲合约ID HEDGEACCOUNTCODE string `json:"hedgeaccountcode" xorm:"HEDGEACCOUNTCODE"` // 对冲账号 TRADEDATE string `json:"tradedate" xorm:"TRADEDATE"` // 交易日(yyyyMMdd) MARKETID int32 `json:"marketid" xorm:"MARKETID"` // 市场ID YDBUYPOSITION int32 `json:"ydbuyposition" xorm:"YDBUYPOSITION"` // 期初买头寸 CURBUYPOSITION int32 `json:"curbuyposition" xorm:"CURBUYPOSITION"` // 期末买头寸 CURYDBUYPOSITION int32 `json:"curydbuyposition" xorm:"CURYDBUYPOSITION"` // 期末上日买头寸 CURTDBUYPOSITION int32 `json:"curtdbuyposition" xorm:"CURTDBUYPOSITION"` // 期末今日买头寸 FREYDBUYPOSITION int32 `json:"freydbuyposition" xorm:"FREYDBUYPOSITION"` // 冻结上日买头寸 FRETDBUYPOSITION int32 `json:"fretdbuyposition" xorm:"FRETDBUYPOSITION"` // 冻结今日买头寸 YDSELLPOSITION int32 `json:"ydsellposition" xorm:"YDSELLPOSITION"` // 期初卖头寸 CURSELLPOSITION int32 `json:"cursellposition" xorm:"CURSELLPOSITION"` // 期末卖头寸 CURYDSELLPOSITION int32 `json:"curydsellposition" xorm:"CURYDSELLPOSITION"` // 期末上日卖头寸 CURTDSELLPOSITION int32 `json:"curtdsellposition" xorm:"CURTDSELLPOSITION"` // 期末今日卖头寸 FREYDSELLPOSITION int32 `json:"freydsellposition" xorm:"FREYDSELLPOSITION"` // 冻结上日卖头寸 FRETDSELLPOSITION int32 `json:"fretdsellposition" xorm:"FRETDSELLPOSITION"` // 冻结今日卖头寸 BUYOPENCOST float64 `json:"buyopencost" xorm:"BUYOPENCOST"` // 买开仓成本 BUYPOSITIONCOST float64 `json:"buypositioncost" xorm:"BUYPOSITIONCOST"` // 买持仓成本 BUYUSEMARGIN float64 `json:"buyusemargin" xorm:"BUYUSEMARGIN"` // 买占用保证金 SELLOPENCOST float64 `json:"sellopencost" xorm:"SELLOPENCOST"` // 卖开仓成本 SELLPOSITIONCOST float64 `json:"sellpositioncost" xorm:"SELLPOSITIONCOST"` // 卖持仓成本 SELLUSEMARGIN float64 `json:"sellusemargin" xorm:"SELLUSEMARGIN"` // 卖占用保证金 RELATEDUSERID int64 `json:"relateduserid" xorm:"'RELATEDUSERID'"` // 关联用户id MIDDLEGOODSID int32 `json:"middlegoodsid" xorm:"'MIDDLEGOODSID'"` // 套保商品id MIDDLEGOODSCODE string `json:"middlegoodscode" xorm:"'MIDDLEGOODSCODE'"` // 套保商品代码 MIDDLEGOODSNAME string `json:"middlegoodsname" xorm:"'MIDDLEGOODSNAME'"` // 套保商品名称 NEEDHEDGERATIO float64 `json:"needhedgeratio" xorm:"'NEEDHEDGERATIO'"` // 应套保比例 NEEDARBITRAGERATIO float64 `json:"needarbitrageratio" xorm:"'NEEDARBITRAGERATIO'"` // 应套利比例 GOODSGROUPID int32 `json:"goodsgroupid" xorm:"'GOODSGROUPID'"` // 交易品种id CONVERTRATIO float64 `json:"convertratio" xorm:"'CONVERTRATIO'"` // 折算系数 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'"` // 合约乘数 ENUMDICNAME string `json:"enumdicname"` // 单位名称(交易商品) ACCOUNTNAME string `json:"accountname" xorm:"'ACCOUNTNAME'"` // 交易账户 DiffBuyQty float64 `json:"diffbuyqty"` // 交易品种今变化量(买) DiffMgBuyQty float64 `json:"diffmgbuyqty"` // 套保品种今变化量(买) DiffNeedMgBuyQty float64 `json:"diffneedmgbuyqty"` // 应套保总量变化量(买) DiffSellQty float64 `json:"diffsellqty"` // 交易品种今变化量(卖) DiffMgSellQty float64 `json:"diffmgsellqty"` // 套保品种今变化量(卖) DiffNeedMgSellQty float64 `json:"diffneedmgsellqty"` // 应套保总量变化量(卖) BeginDate string `json:"begindate"` // 开始交易日 EndDate string `json:"enddate"` // 结束交易日 } func (r *HISOUTTRADEPOSITION) calc() { r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.GOODUNITID) if r.YDBUYPOSITION > 0 || r.CURBUYPOSITION > 0 { r.DiffBuyQty = float64(r.CURBUYPOSITION) * r.AGREEUNIT r.DiffMgBuyQty = float64(r.CURBUYPOSITION) * r.AGREEUNIT * r.CONVERTRATIO r.DiffNeedMgBuyQty = float64(r.CURBUYPOSITION) * r.AGREEUNIT * r.CONVERTRATIO * r.NEEDHEDGERATIO } if r.YDSELLPOSITION > 0 || r.CURSELLPOSITION > 0 { r.DiffBuyQty = float64(r.CURSELLPOSITION) * r.AGREEUNIT r.DiffMgBuyQty = float64(r.CURSELLPOSITION) * r.AGREEUNIT * r.CONVERTRATIO r.DiffNeedMgBuyQty = float64(r.CURSELLPOSITION) * r.AGREEUNIT * r.CONVERTRATIO * r.NEEDHEDGERATIO } } func (r *HISOUTTRADEPOSITION) buildSql() string { var sqlId utils.SQLVal = "with tmp as" + " (select t.middlegoodsid," + " t.middlegoodscode," + " t.middlegoodsname," + " t.needhedgeratio," + " t.needarbitrageratio," + " t.goodsgroupid," + " c.convertratio," + " g.goodsid," + " g.goodscode," + " g.goodsname," + " g.agreeunit," + " g.goodunitid" + " from erms_middlegoods t" + " inner join ermcp_ggconvertconfig c" + " on t.goodsgroupid = c.destgoodsgroupid" + " inner join goods g" + " on g.goodsgroupid = c.srcgoodsgroupid)" + "SELECT t.HISTRADEDATE," + " t.ISVALIDDATA," + " t.ACCOUNTID," + " t.HEDGEGOODSID," + " t.HEDGEACCOUNTCODE," + " t.TRADEDATE," + " t.MARKETID," + " t.YDBUYPOSITION," + " t.CURBUYPOSITION," + " t.CURYDBUYPOSITION," + " t.CURTDBUYPOSITION," + " t.FREYDBUYPOSITION," + " t.FRETDBUYPOSITION," + " t.YDSELLPOSITION," + " t.CURSELLPOSITION," + " t.CURYDSELLPOSITION," + " t.CURTDSELLPOSITION," + " t.FREYDSELLPOSITION," + " t.FRETDSELLPOSITION," + " t.BUYOPENCOST," + " t.BUYPOSITIONCOST," + " t.BUYUSEMARGIN," + " t.SELLOPENCOST," + " t.SELLPOSITIONCOST," + " t.SELLUSEMARGIN," + " ta.relateduserid," + " ta.accountname," + " tmp.*" + " FROM HIS_HEDGE_OUTTRADEPOSITION t" + " INNER JOIN tmp" + " on t.HEDGEGOODSID = tmp.goodsid" + " INNER JOIN TAACCOUNT ta" + " on t.accountid = ta.accountid" + " WHERE 1 = 1" + " and (t.YDBUYPOSITION > 0 or t.CURBUYPOSITION > 0 or t.YDSELLPOSITION > 0 or t.CURSELLPOSITION > 0)" sqlId.And("ta.RELATEDUSERID", r.RELATEDUSERID) sqlId.And("tmp.MIDDLEGOODSID", r.MIDDLEGOODSID) sqlId.AndEx("t.HISTRADEDATE", r.HISTRADEDATE, len(r.HISTRADEDATE) > 0) if len(r.BeginDate) > 0 && len(r.EndDate) > 0 { sqlId.Join(fmt.Sprintf(" and t.HISTRADEDATE >= '%v' and t.HISTRADEDATE <= '%v' ", r.BeginDate, r.EndDate)) } return sqlId.String() } // GetDataEx 获取外部头寸日照 func (r *HISOUTTRADEPOSITION) GetDataEx() ([]HISOUTTRADEPOSITION, error) { sData := make([]HISOUTTRADEPOSITION, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].calc() } return sData, err } // Ermcp3ExposureFutuDetail 敞口报表期货明细 type Ermcp3ExposureFutuDetail struct { ACCOUNTID int64 `json:"accountid"` // 交易账户id ACCOUNTNAME string `json:"accountname"` // 交易账户 GOODSID int32 `json:"goodsid"` // 商品id GOODSCODE string `json:"goodscode"` // 商品代码 GOODSNAME string `json:"goodsname"` // 商品名称 BUYORSELL int32 `json:"buyorsell"` // 方向 0-买 1-卖 ORIHOLDQTY float64 `json:"oriholdqty"` // 期初持仓量 HOLDQTY float64 `json:"holdqty"` // 期末持仓量 TODAYOPENQTY float64 `json:"todayopenqty"` // 今开仓量 TODAYCLOSEQTY float64 `json:"todaycloseqty"` // 今平仓量 DiffTradeQty float64 `json:"difftradeqty"` // 交易品种今变化量 DiffMgQty float64 `json:"diffmgqty"` // 套保品种今变化量 DiffNeedQty float64 `json:"diffneedqty"` // 应套保总量变化量 MIDDLEGOODSID int32 `json:"middlegoodsid"` // 套保商品id MIDDLEGOODSCODE string `json:"middlegoodscode"` // 套保商品代码 MIDDLEGOODSNAME string `json:"middlegoodsname"` // 套保商品名称 ENUMDICNAME string `json:"enumdicname"` // 单位名称 USERNAME string `json:"username"` // 用户名称(交易用户) USERID int64 `json:"-"` // 用户id RECKONDATE string `json:"tradedate"` // 交易日 BeginDate string `json:"begindate"` // 开始交易日 EndDate string `json:"enddate"` // 结束交易日 } func (r *Ermcp3ExposureFutuDetail) fromOutPositionBuy(v *HISOUTTRADEPOSITION) { r.ACCOUNTID = v.ACCOUNTID r.ACCOUNTNAME = v.ACCOUNTNAME r.GOODSID = v.HEDGEGOODSID r.GOODSCODE = v.GOODSCODE r.GOODSNAME = v.GOODSNAME r.BUYORSELL = 0 r.ORIHOLDQTY = float64(v.YDBUYPOSITION) r.HOLDQTY = float64(v.CURBUYPOSITION) r.TODAYOPENQTY = 0 r.TODAYCLOSEQTY = 0 r.DiffTradeQty = v.DiffBuyQty r.DiffMgQty = v.DiffMgBuyQty r.DiffNeedQty = v.DiffNeedMgBuyQty r.MIDDLEGOODSID = v.MIDDLEGOODSID r.MIDDLEGOODSCODE = v.MIDDLEGOODSCODE r.MIDDLEGOODSNAME = v.MIDDLEGOODSNAME r.ENUMDICNAME = v.ENUMDICNAME r.RECKONDATE = v.HISTRADEDATE } func (r *Ermcp3ExposureFutuDetail) fromOutPositionSell(v *HISOUTTRADEPOSITION) { r.ACCOUNTID = v.ACCOUNTID r.ACCOUNTNAME = v.ACCOUNTNAME r.GOODSID = v.HEDGEGOODSID r.GOODSCODE = v.GOODSCODE r.GOODSNAME = v.GOODSNAME r.BUYORSELL = 1 r.ORIHOLDQTY = float64(v.YDSELLPOSITION) r.HOLDQTY = float64(v.CURSELLPOSITION) r.TODAYOPENQTY = 0 r.TODAYCLOSEQTY = 0 r.DiffTradeQty = v.DiffSellQty r.DiffMgQty = v.DiffMgSellQty r.DiffNeedQty = v.DiffNeedMgSellQty r.MIDDLEGOODSID = v.MIDDLEGOODSID r.MIDDLEGOODSCODE = v.MIDDLEGOODSCODE r.MIDDLEGOODSNAME = v.MIDDLEGOODSNAME r.ENUMDICNAME = v.ENUMDICNAME r.RECKONDATE = v.HISTRADEDATE } func (r *Ermcp3ExposureFutuDetail) fromTradePositionBuy(v *ReckonDayPosition) { r.ACCOUNTID = v.ACCOUNTID r.ACCOUNTNAME = v.ACCOUNTNAME r.GOODSID = v.GOODSID r.GOODSCODE = v.GOODSCODE r.GOODSNAME = v.GOODSNAME r.BUYORSELL = 0 r.ORIHOLDQTY = float64(v.BUYPOSITIONQTY) r.HOLDQTY = float64(v.BUYCURPOSITIONQTY) r.TODAYOPENQTY = 0 r.TODAYCLOSEQTY = 0 r.DiffTradeQty = v.DiffBuyQty r.DiffMgQty = v.DiffMgBuyQty r.DiffNeedQty = v.DiffNeedMgBuyQty r.MIDDLEGOODSID = v.MIDDLEGOODSID r.MIDDLEGOODSCODE = v.MIDDLEGOODSCODE r.MIDDLEGOODSNAME = v.MIDDLEGOODSNAME r.ENUMDICNAME = v.ENUMDICNAME r.RECKONDATE = v.RECKONDATE } func (r *Ermcp3ExposureFutuDetail) fromTradePositionSell(v *ReckonDayPosition) { r.ACCOUNTID = v.ACCOUNTID r.ACCOUNTNAME = v.ACCOUNTNAME r.GOODSID = v.GOODSID r.GOODSCODE = v.GOODSCODE r.GOODSNAME = v.GOODSNAME r.BUYORSELL = 1 r.ORIHOLDQTY = float64(v.SELLPOSITIONQTY) r.HOLDQTY = float64(v.SELLCURPOSITIONQTY) r.TODAYOPENQTY = 0 r.TODAYCLOSEQTY = 0 r.DiffTradeQty = v.DiffSellQty r.DiffMgQty = v.DiffMgSellQty r.DiffNeedQty = v.DiffNeedMgSellQty r.MIDDLEGOODSID = v.MIDDLEGOODSID r.MIDDLEGOODSCODE = v.MIDDLEGOODSCODE r.MIDDLEGOODSNAME = v.MIDDLEGOODSNAME r.ENUMDICNAME = v.ENUMDICNAME r.RECKONDATE = v.RECKONDATE } // GetDataEx 获取敞口报表期货明细 func (r *Ermcp3ExposureFutuDetail) GetDataEx() (interface{}, error) { sData := make([]Ermcp3ExposureFutuDetail, 0) if mtpcache.IsAreaUserId(r.USERID) { // 查母账户头寸 m := HISOUTTRADEPOSITION{RELATEDUSERID: r.USERID, HISTRADEDATE: r.RECKONDATE, BeginDate: r.BeginDate, EndDate: r.EndDate, MIDDLEGOODSID: r.MIDDLEGOODSID} d, _ := m.GetDataEx() for _, v := range d { if v.YDBUYPOSITION > 0 || v.CURBUYPOSITION > 0 { val := Ermcp3ExposureFutuDetail{} val.fromOutPositionBuy(&v) val.BeginDate = r.BeginDate val.EndDate = r.EndDate val.USERNAME = mtpcache.GetUserNameByUserId(r.USERID) sData = append(sData, val) } if v.YDSELLPOSITION > 0 || v.CURSELLPOSITION > 0 { val := Ermcp3ExposureFutuDetail{} val.fromOutPositionSell(&v) val.BeginDate = r.BeginDate val.EndDate = r.EndDate val.USERNAME = mtpcache.GetUserNameByUserId(r.USERID) sData = append(sData, val) } } } else { // 查子账户头寸 m := ReckonDayPosition{RELATEDUSERID: r.USERID, RECKONDATE: r.RECKONDATE, BeginDate: r.BeginDate, EndDate: r.EndDate, MIDDLEGOODSID: r.MIDDLEGOODSID} d, _ := m.GetDataEx() for _, v := range d { if v.BUYPOSITIONQTY > 0 || v.BUYCURPOSITIONQTY > 0 { val := Ermcp3ExposureFutuDetail{} val.fromTradePositionBuy(&v) val.BeginDate = r.BeginDate val.EndDate = r.EndDate val.USERNAME = mtpcache.GetUserNameByUserId(r.USERID) sData = append(sData, val) } if v.SELLPOSITIONQTY > 0 || v.SELLCURPOSITIONQTY > 0 { val := Ermcp3ExposureFutuDetail{} val.fromTradePositionSell(&v) val.BeginDate = r.BeginDate val.EndDate = r.EndDate val.USERNAME = mtpcache.GetUserNameByUserId(r.USERID) sData = append(sData, val) } } } return sData, nil } // Ermcp3ExposureParamChLog 敞口报表参数调整明细 type Ermcp3ExposureParamChLog struct { LOGID string `json:"logid" xorm:"LOGID"` // 流水ID(609+Unix秒时间戳(10位)+xxxxxx) MIDDLEGOODSID int64 `json:"middlegoodsid" xorm:"MIDDLEGOODSID"` // 套保品种 AREAUSERID int64 `json:"areauserid" xorm:"AREAUSERID"` // 所属机构\交易用户ID ACCOUNTID int64 `json:"accountid" xorm:"ACCOUNTID"` // 期货账户ID (作废, 默认为0) CHANGELOGTYPE int32 `json:"changelogtype" xorm:"CHANGELOGTYPE"` // 变更流水类型 - 1:套保比率变更 2:套利比率变更 PARAMBEFOREVALUE float64 `json:"parambeforevalue" xorm:"PARAMBEFOREVALUE"` // 参数变更前(调整前参数值) PARAMAFTERVALUE float64 `json:"paramaftervalue" xorm:"PARAMAFTERVALUE"` // 参数变更后(调整后参数值) CURVALUE float64 `json:"curvalue" xorm:"CURVALUE"` // 当前套保\套利量(套保总量|套利总量) BEFOREVALUE float64 `json:"beforevalue" xorm:"BEFOREVALUE"` // 应套保\套利量变更前(调整前应套保总量) AFTERVALUE float64 `json:"aftervalue" xorm:"AFTERVALUE"` // 应套保\套利量变更后(调整后应套保总量) LOGDATETIME string `json:"logdatetime" xorm:"LOGDATETIME"` // 流水日期 TRADEDATE string `json:"tradedate" xorm:"TRADEDATE"` // 交易日(yyyyMMdd) MIDDLEGOODSCODE string `json:"middlegoodscode" xorm:"'MIDDLEGOODSCODE'"` // 套保品种代码 MIDDLEGOODSNAME string `json:"middlegoodsname" xorm:"'MIDDLEGOODSNAME'"` // 套保品种名称 DiffValue float64 `json:"diffvalue"` // 应套保总量变化量 BeginDate string `json:"begindate"` // 开始交易日 EndDate string `json:"enddate"` // 结束交易日 } func (r *Ermcp3ExposureParamChLog) calc() { r.DiffValue = r.AFTERVALUE - r.BEFOREVALUE } func (r *Ermcp3ExposureParamChLog) buildSql() string { var sqlId utils.SQLVal = "SELECT t.LOGID," + " t.MIDDLEGOODSID," + " t.AREAUSERID," + " t.ACCOUNTID," + " t.CHANGELOGTYPE," + " t.PARAMBEFOREVALUE," + " t.PARAMAFTERVALUE," + " t.CURVALUE," + " t.BEFOREVALUE," + " t.AFTERVALUE," + " to_char(t.LOGDATETIME, 'yyyy-mm-dd hh24:mi:ss') LOGDATETIME," + " t.TRADEDATE," + " g.middlegoodscode," + " g.middlegoodsname" + " FROM ERMCP_EXPOSUREPARAMCHGLOG t" + " LEFT JOIN ERMS_MIDDLEGOODS g on t.middlegoodsid=g.middlegoodsid" + " WHERE 1 = 1" sqlId.And("t.AREAUSERID", r.AREAUSERID) sqlId.AndEx("t.TRADEDATE", r.TRADEDATE, len(r.TRADEDATE) > 0) if len(r.BeginDate) > 0 && len(r.EndDate) > 0 { sqlId.Join(fmt.Sprintf(" and t.TRADEDATE >= '%v' and t.TRADEDATE <= '%v' ", r.BeginDate, r.EndDate)) } return sqlId.String() } // GetDataEx 获取敞口报表参数调整明细 func (r *Ermcp3ExposureParamChLog) GetDataEx() (interface{}, error) { sData := make([]Ermcp3ExposureParamChLog, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].calc() sData[i].BeginDate = r.BeginDate sData[i].EndDate = r.EndDate } return sData, err } // Ermcp3SCMiddleGoodsReport 定价报表 type Ermcp3SCMiddleGoodsReport struct { RECKONDATE string `json:"reckondate" xorm:"RECKONDATE" form:"reckondate"` // 日照时期(yyyyMMdd) CYCLETYPE int32 `json:"cycletype" xorm:"CYCLETYPE" form:"cycletype"` // 周期类型 - 1:月 2:季 3:年 4:周 5:全报表【原值】 CYCLETIME string `json:"cycletime" xorm:"CYCLETIME" form:"cycletime"` // 周期时间 月(YYYYMM) 季(YYYYQ) 年(YYYY) 周(YYYYIW) 全(0)【原值】 SPOTCONTRACTID string `json:"spotcontractid" xorm:"SPOTCONTRACTID" form:"spotcontractid"` // 现货合同ID(602+Unix秒时间戳(10位)+xxxxxx) MIDDLEGOODSID int64 `json:"middlegoodsid" xorm:"MIDDLEGOODSID" form:"middlegoodsid"` // 套保品种ID DELIVERYGOODSID int64 `json:"deliverygoodsid" xorm:"DELIVERYGOODSID" form:"deliverygoodsid"` // 现货品种ID WRSTANDARDID int64 `json:"wrstandardid" xorm:"WRSTANDARDID" form:"wrstandardid"` // 现货商品ID PRICEDQTY SFLOAT64 `json:"pricedqty" xorm:"PRICEDQTY"` // 已定价量(现货) MIDDLEGOODSPRICEDQTY SFLOAT64 `json:"middlegoodspricedqty" xorm:"MIDDLEGOODSPRICEDQTY"` // 已定价量(套保品种 - 税前) = PricedQty * 折算系数 MIDDLEGOODSPRICEDQTY2 SFLOAT64 `json:"middlegoodspricedqty2" xorm:"MIDDLEGOODSPRICEDQTY2"` // 已定价量(套保品种- 税后) = MiddleGoodsPricedQty * (1/(1+增值税率)) MIDDLEGOODSPRICEDQTY3 SFLOAT64 `json:"middlegoodspricedqty3" xorm:"MIDDLEGOODSPRICEDQTY3"` // 已定价量(套保品种应套保量) = MiddleGoodsPricedQty2 * 套保比率 RELATEDMIDDLEGOODSQTY SFLOAT64 `json:"relatedmiddlegoodsqty" xorm:"RELATEDMIDDLEGOODSQTY"` // 已关联量(套保品种)(期货) UPDATETIME string `json:"updatetime" xorm:"UPDATETIME"` // 更新时间 TODAYRELATEDMIDDLEGOODSQTY SFLOAT64 `json:"todayrelatedmiddlegoodsqty" xorm:"TODAYRELATEDMIDDLEGOODSQTY"` // 今日关联量(套保品-期货) TODAYRELATEDMIDDLEGOODSAMOUNT SFLOAT64 `json:"todayrelatedmiddlegoodsamount" xorm:"TODAYRELATEDMIDDLEGOODSAMOUNT"` // 今日关联额(期货) RELATEDMIDDLEGOODSAMOUNT SFLOAT64 `json:"relatedmiddlegoodsamount" xorm:"RELATEDMIDDLEGOODSAMOUNT"` // 已关联额(期货) TODAYPRICEDQTY SFLOAT64 `json:"todaypricedqty" xorm:"TODAYPRICEDQTY"` // 今日定价量(现货) TODAYPRICEDAMOUNT SFLOAT64 `json:"todaypricedamount" xorm:"TODAYPRICEDAMOUNT"` // 今日定价额(现货) PRICEDAMOUNT SFLOAT64 `json:"pricedamount" xorm:"PRICEDAMOUNT"` // 已定价额(现货) TODAYPRICEDAMOUNT2 SFLOAT64 `json:"todaypricedamount2" xorm:"TODAYPRICEDAMOUNT2"` // 今日定价额(现货-基价额) PRICEDAMOUNT2 SFLOAT64 `json:"pricedamount2" xorm:"PRICEDAMOUNT2"` // 已定价额(现货-基价额) TODAYRELATEDFUTUREQTY SFLOAT64 `json:"todayrelatedfutureqty" xorm:"'TODAYRELATEDFUTUREQTY'"` // 今日关联量(期货) USERID int64 `json:"userid" xorm:"'USERID'" form:"userid"` // 合同所属用户id CONTRACTNO string `json:"contractno" xorm:"'CONTRACTNO'" form:"contractno"` // 合同编号 CONTRACTTYPE int32 `json:"contracttype" xorm:"'CONTRACTTYPE'"` // 合同类型 1：采购 -1：销售 MATCHNAME string `json:"matchname" xorm:"'MATCHNAME'"` // 交易对手方(个人:用户名称 企业:简称) DELIVERYGOODSCODE string `json:"deliverygoodscode" xorm:"'DELIVERYGOODSCODE'"` // 品种代码 DELIVERYGOODSNAME string `json:"deliverygoodsname" xorm:"'DELIVERYGOODSNAME'"` // 品种名称 WRSTANDARDCODE string `json:"wrstandardcode" xorm:"'WRSTANDARDCODE'"` // 商品(品类) 代码 WRSTANDARDNAME string `json:"wrstandardname" xorm:"'WRSTANDARDNAME'"` // 商品(品类) 名称 VATRATE SFLOAT64 `json:"vatrate" xorm:"'VATRATE'"` // 增值税率 MIDDLEGOODSCODE string `json:"middlegoodscode" xorm:"'MIDDLEGOODSCODE'"` // 套保品种代码 MIDDLEGOODSNAME string `json:"middlegoodsname" xorm:"'MIDDLEGOODSNAME'"` // 套保品种名称 NEEDHEDGERATIO SFLOAT64 `json:"needhedgeratio" xorm:"'NEEDHEDGERATIO'"` // 套保系数 CONVERTRATIO SFLOAT64 `json:"convertratio" xorm:"'CONVERTRATIO'"` // 折算系数 TODAYAVGPRICE SFLOAT64 `json:"todayavgprice" xorm:"'TODAYAVGPRICE'"` // 今日定价均价 = TodayPricedAmount / TodayPricedQty TODAYAVGPRICE2 SFLOAT64 `json:"todayavgprice2" xorm:"'TODAYAVGPRICE2'"` // 今日点价登记均价（基价） = TodayPricedAmount2 / TodayPricedQty HEDGEPLANQTY SFLOAT64 `json:"hedgeplanqty" xorm:"'HEDGEPLANQTY'"` // 套保计划量 = TodayPricedQty * 折算系数 * (1/(1+增值税率)) * 套保比例 TODAYAVGFUTUPRICE SFLOAT64 `json:"todayavgfutuprice" xorm:"'TODAYAVGFUTUPRICE'"` // 今日期货成交均价 = TodayRelatedMiddleGoodsAmount /todayrelatedfutureqty DIFFPRICE SFLOAT64 `json:"diffprice" xorm:"'DIFFPRICE'"` // 现期价格差 = 今日点价登记均价（基价） - 今日期货成交均价 DIFFQTY SFLOAT64 `json:"diffqty" xorm:"'DIFFQTY'"` // 期现数量差 = 套保计划量 - 今日期货关联成交量 QueryType int32 `json:"-"` // 查询类型 1-报表 2-报表明细 BeginDate string `json:"begindate"` // 开始交易日 EndDate string `json:"enddate"` // 结束交易日 } func (r *Ermcp3SCMiddleGoodsReport) calc() { if r.TODAYPRICEDQTY > 0 { r.TODAYAVGPRICE = r.TODAYPRICEDAMOUNT / r.TODAYPRICEDQTY r.TODAYAVGPRICE.Round(2) r.TODAYAVGPRICE2 = r.TODAYPRICEDAMOUNT2 / r.TODAYPRICEDQTY r.TODAYAVGPRICE2.Round(2) } r.HEDGEPLANQTY = r.TODAYPRICEDQTY * r.CONVERTRATIO * (1 / (1 + r.VATRATE)) * r.NEEDHEDGERATIO if r.TODAYRELATEDFUTUREQTY > 0 { r.TODAYAVGFUTUPRICE = r.TODAYRELATEDMIDDLEGOODSAMOUNT / r.TODAYRELATEDFUTUREQTY r.TODAYAVGFUTUPRICE.Round(2) r.DIFFPRICE = r.TODAYAVGPRICE2 - r.TODAYAVGFUTUPRICE r.DIFFPRICE.Round(2) } r.DIFFQTY = r.HEDGEPLANQTY - r.TODAYRELATEDMIDDLEGOODSQTY r.DIFFQTY.Round(8) } func (r *Ermcp3SCMiddleGoodsReport) buildSql() string { if r.CYCLETYPE == 0 { return r.buildSqlDay() } else { return r.buildSqlCycle() } } func (r *Ermcp3SCMiddleGoodsReport) buildSqlDay() string { var sqlId utils.SQLVal = ` select t.reckondate, to_char(t.spotcontractid) spotcontractid, t.middlegoodsid, t.deliverygoodsid, t.wrstandardid, t.pricedqty, t.middlegoodspricedqty, t.middlegoodspricedqty2, t.middlegoodspricedqty3, t.relatedmiddlegoodsqty, t.todayrelatedmiddlegoodsqty, t.todayrelatedfutureqty, t.todayrelatedmiddlegoodsamount, t.relatedmiddlegoodsamount, t.todaypricedqty, t.todaypricedamount, t.pricedamount, t.todaypricedamount2, t.pricedamount2, s.userid, s.contractno, s.contracttype, decode(ui.userinfotype, 2, ui.nickname,u.accountname) matchname, g.deliverygoodscode, g.deliverygoodsname, w.wrstandardcode, w.wrstandardname, w.vatrate, m.middlegoodscode, m.middlegoodsname, m.needhedgeratio, c.convertratio from Reckon_ERMCP_SCMiddleGoods t inner join ermcp_spotcontract s on t.spotcontractid = s.spotcontractid left join useraccount u on u.userid = (decode(s.contracttype, 1, s.selluserid, s.buyuserid)) left join userinfo ui on u.userid=ui.userid left join deliverygoods g on t.deliverygoodsid = g.deliverygoodsid left join wrstandard w on t.wrstandardid = w.wrstandardid left join erms_middlegoods m on t.middlegoodsid = m.middlegoodsid left join erms2_wrsconvertdetail c on t.middlegoodsid = c.middlegoodsid and t.wrstandardid = c.wrstandardid where 1 = 1 ` sqlId.JoinFormat(" and %v in (s.userid, s.tradeuserid, s.saleuserid, s.tradeuserid)", r.USERID) sqlId.And("t.RECKONDATE", r.RECKONDATE) sqlId.AndEx("t.middlegoodsid", r.MIDDLEGOODSID, r.MIDDLEGOODSID > 0) sqlId.AndEx("t.deliverygoodsid", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0) sqlId.AndEx("t.wrstandardid", r.WRSTANDARDID, r.WRSTANDARDID > 0) sqlId.AndInterEx("t.spotcontractid", r.SPOTCONTRACTID, r.SPOTCONTRACTID != "") sqlId.AndLike("s.contractno", r.CONTRACTNO) return sqlId.String() } func (r *Ermcp3SCMiddleGoodsReport) buildSqlCycle() string { var sqlId utils.SQLVal = ` select t.cycletype, t.cycletime, to_char(t.spotcontractid) spotcontractid, t.middlegoodsid, t.deliverygoodsid, t.wrstandardid, t.pricedqty, t.middlegoodspricedqty, t.middlegoodspricedqty2, t.middlegoodspricedqty3, t.relatedmiddlegoodsqty, t.todayrelatedfutureqty, t.todayrelatedmiddlegoodsqty, t.todayrelatedmiddlegoodsamount, t.relatedmiddlegoodsamount, t.todaypricedqty, t.todaypricedamount, t.pricedamount, t.todaypricedamount2, t.pricedamount2, s.userid, s.contractno, s.contracttype, decode(ui.userinfotype, 2, ui.nickname, u.accountname) matchname, g.deliverygoodscode, g.deliverygoodsname, w.wrstandardcode, w.wrstandardname, w.vatrate, m.middlegoodscode, m.middlegoodsname, m.needhedgeratio, c.convertratio from Report_ERMCP_SCMiddleGoods t inner join ermcp_spotcontract s on t.spotcontractid = s.spotcontractid left join useraccount u on u.userid = (decode(s.contracttype, 1, s.selluserid, s.buyuserid)) left join userinfo ui on u.userid=ui.userid left join deliverygoods g on t.deliverygoodsid = g.deliverygoodsid left join wrstandard w on t.wrstandardid = w.wrstandardid left join erms_middlegoods m on t.middlegoodsid = m.middlegoodsid left join erms2_wrsconvertdetail c on t.middlegoodsid = c.middlegoodsid and t.wrstandardid = c.wrstandardid where 1 = 1 ` sqlId.JoinFormat(" and %v in (s.userid, s.tradeuserid, s.saleuserid, s.tradeuserid)", r.USERID) sqlId.And("t.CYCLETYPE", r.CYCLETYPE) sqlId.And("t.CYCLETIME", r.CYCLETIME) sqlId.AndEx("t.middlegoodsid", r.MIDDLEGOODSID, r.MIDDLEGOODSID > 0) sqlId.AndEx("t.deliverygoodsid", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0) sqlId.AndEx("t.wrstandardid", r.WRSTANDARDID, r.WRSTANDARDID > 0) sqlId.AndInterEx("t.spotcontractid", r.SPOTCONTRACTID, r.SPOTCONTRACTID != "") sqlId.AndLike("s.contractno", r.CONTRACTNO) return sqlId.String() } // GetDataEx 获取定价报表 func (r *Ermcp3SCMiddleGoodsReport) GetDataEx() (interface{}, error) { sData := make([]Ermcp3SCMiddleGoodsReport, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].calc() sData[i].BeginDate = r.BeginDate sData[i].EndDate = r.EndDate } return sData, err }