/** * @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"` // 结束交易日 } 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 { 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() } 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", } 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 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) } 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"` // 单位名称 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 sData = append(sData, val) } if v.YDSELLPOSITION > 0 || v.CURSELLPOSITION > 0 { val := Ermcp3ExposureFutuDetail{} val.fromOutPositionSell(&v) val.BeginDate = r.BeginDate val.EndDate = r.EndDate 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 sData = append(sData, val) } if v.SELLPOSITIONQTY > 0 || v.SELLCURPOSITIONQTY > 0 { val := Ermcp3ExposureFutuDetail{} val.fromTradePositionSell(&v) val.BeginDate = r.BeginDate val.EndDate = r.EndDate 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"` // 已定价额(现货-基价额) 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 /TodayRelatedMiddleGoodsQty 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.TODAYRELATEDMIDDLEGOODSQTY > 0 { r.TODAYAVGFUTUPRICE = r.TODAYRELATEDMIDDLEGOODSAMOUNT / r.TODAYRELATEDMIDDLEGOODSQTY 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.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.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 }