MTP20_IF/models/ermcpReport.go

/**
* @Author: zou.yingbin
* @Create  : 2021/2/4 11:27
* @Modify  : 2021/2/4 11:27
 */

package models

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

// 财务日报表
type ErmcpReportDayFR struct {
	RECKONDATE           string  `json:"reckondate"  xorm:"'RECKONDATE'"`                     // 日照时期(yyyyMMdd)
	AREAUSERID           int64   `json:"areauserid"  xorm:"'AREAUSERID'"`                     // 所属机构
	COLLECTMONEYCOUNT    int32   `json:"collectmoneycount"  xorm:"'COLLECTMONEYCOUNT'"`       // 今日收款笔数
	COLLECTMONEYAMOUNT   float64 `json:"collectmoneyamount"  xorm:"'COLLECTMONEYAMOUNT'"`     // 今日收款金额
	PAYMONEYCOUNT        int32   `json:"paymoneycount"  xorm:"'PAYMONEYCOUNT'"`               // 今日付款笔数
	PAYMONEYAMOUNT       float64 `json:"paymoneyamount"  xorm:"'PAYMONEYAMOUNT'"`             // 今日付款金额
	COLLECTINVOICECOUNT  int32   `json:"collectinvoicecount"  xorm:"'COLLECTINVOICECOUNT'"`   // 今日开票笔数
	COLLECTINVOICEAMOUNT float64 `json:"collectinvoiceamount"  xorm:"'COLLECTINVOICEAMOUNT'"` // 今日开票金额
	PAYINVOICECOUNT      int32   `json:"payinvoicecount"  xorm:"'PAYINVOICECOUNT'"`           // 今日收票笔数
	PAYINVOICEAMOUNT     float64 `json:"payinvoiceamount"  xorm:"'PAYINVOICEAMOUNT'"`         // 今日收票金额
	UPDATETIME           string  `json:"updatetime"  xorm:"'UPDATETIME'"`                     // 更新时间
	BeginDate            string  `json:"-"`                                                   // 开始日期
	EndDate              string  `json:"-"`                                                   // 结束日期
}

// 数据处理
func (r *ErmcpReportDayFR) Calc() {

}

func (r *ErmcpReportDayFR) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.RECKONDATE," +
		"       t.AREAUSERID," +
		"       t.COLLECTMONEYCOUNT," +
		"       t.COLLECTMONEYAMOUNT," +
		"       t.PAYMONEYCOUNT," +
		"       t.PAYMONEYAMOUNT," +
		"       t.COLLECTINVOICECOUNT," +
		"       t.COLLECTINVOICEAMOUNT," +
		"       t.PAYINVOICECOUNT," +
		"       t.PAYINVOICEAMOUNT," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh:mi:ss') UPDATETIME" +
		"  FROM RECKON_ERMCP_AREAFR t" +
		" WHERE 1 = 1"

	// 查询条件
	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)
		}
	}
	return sqlId.String()
}

// 获取日报表
func (r *ErmcpReportDayFR) GetDataEx() (interface{}, error) {
	sData := make([]ErmcpReportDayFR, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	return sData, err
}

// 财务日报表/款项
type ErmcpReportDayFRKx struct {
	USERID    int64  `json:"userid"  xorm:"'USERID'"`       // 机构ID
	TRADEDATE string `json:"tradedate"  xorm:"'TRADEDATE'"` // 交易日(yyyyMMdd)
}

// 获取日报表款项记录
func (r *ErmcpReportDayFRKx) GetDataEx() (interface{}, error) {
	m := ErmcpReportOPLog{USERID: r.USERID, LogTypeFilter: "8,9,10", TRADEDATE: r.TRADEDATE}
	return m.GetDataEx()
}

// 报表合同操作记录通用查询
type ErmcpReportOPLog 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
	WRSTANDARDID   int64  `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`     // 现货商品ID
	RELATEDNO      string `json:"relatedno"  xorm:"'RELATEDNO'"`           // 合同编号
	WRSTANDARDNAME string `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"` // 现货商品名称
	WRSTANDARDCODE string `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"` // 现货商品代码
	UNITID         int32  `json:"-"  xorm:"'UNITID'"`                      // 现货商品单位id
	ENUMDICNAME    string `json:"enumdicName"`                             // 单位名称
	OPTYPENAME     string `json:"optypename"`                              // 流水类型名称
	LOGTYPENAME    string `json:"logtypename"`                             // 合同类型(名称)
	APPLYNAME      string `json:"applyname"`                               // 操作人名称

	LogTypeFilter string `json:"-"` // 查询日志类型, 逗号隔开(如 1,2,4)
}

// 处理数据
func (r *ErmcpReportOPLog) Calc() {
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID)
	r.APPLYNAME = mtpcache.GetUserNameByUserId(r.APPLYID)
	sDes := []string{"点价价格", "点价数量", "结算量", "其它费用", "追加保证金", "溢短金额", "调整金额", "付款金额", "收款金额", "退款金额",
		"收票金额", "开票金额", "提交审核(合同)", "审核通过(合同)", "审核拒绝(合同)", "合同撤回", "提交审核(计划)",
		"审核通过(计划)", "审核拒绝(计划)", "计划撤回", "正常完结(合同)", "异常终止(合同)", "退还保证金"}
	if r.OPERATELOGTYPE >= 1 && r.OPERATELOGTYPE <= 23 {
		r.OPTYPENAME = sDes[r.OPERATELOGTYPE-1]
	}

	if r.CONTRACTTYPE == 1 {
		r.LOGTYPENAME = "采购"
	} else if r.CONTRACTTYPE == -1 {
		r.LOGTYPENAME = "销售"
	}

	if r.BIZTYPE == 1 {
		r.LOGTYPENAME += "计划"
	} else if r.BIZTYPE == 2 {
		r.LOGTYPENAME += "合同"
	}
}

func (r *ErmcpReportOPLog) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select to_char(t.hedgeplanid) relatedid," +
		"         t.hedgeplanno relatedno," +
		"         1 as logType," +
		"         t.contracttype" +
		"    from ermcp_hedgeplan t" +
		"   where t.areauserid = %v" +
		"  union all" +
		"  select to_char(t.spotcontractid)," +
		"         t.contractno," +
		"         2 as logType," +
		"         t.contracttype" +
		"    from ermcp_spotcontract t" +
		"   where t.userid = %v)" +
		" 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," +
		"       tmp.relatedno," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       w.unitid" +
		"  FROM ERMCP_CONTRACTOPERATELOG t" +
		" inner join tmp" +
		"    on t.RELATEDID = tmp.RELATEDID" +
		"   and t.userid = %v" +
		"   left join wrstandard w on t.wrstandardid=w.wrstandardid" +
		" WHERE 1 = 1"
	sqlId = utils.SQLVal(fmt.Sprintf(sqlId.String(), r.USERID, r.USERID, r.USERID))
	// 筛选条件
	sqlId.And("t.TRADEDATE", r.TRADEDATE)
	if r.LogTypeFilter != "" {
		sqlId.Join(fmt.Sprintf(" and t.OPERATELOGTYPE in(%v)", r.LogTypeFilter))
	}
	return sqlId.String()
}

// 获取日志记录
func (r *ErmcpReportOPLog) GetDataEx() (interface{}, error) {
	sData := make([]ErmcpReportOPLog, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].Calc()
	}
	return sData, err
}

// 财务月报表
type ErmcpReportMonthFR struct {
	CYCLETYPE            int32              `json:"cycletype"  xorm:"'cycletype'"`                       // 周期类型 - 1:月 2:季 3:年 4:周 5:全报表【原值】
	CYCLETIME            string             `json:"cycletime"  xorm:"'cycletime'"`                       // 周期时间 月(YYYYMM)  季(YYYYQ) 年(YYYY) 周(YYYYIW) 全(0)【原值】
	AREAUSERID           int64              `json:"areauserid"  xorm:"'areauserid'"`                     // 所属机构【原值】
	COLLECTMONEYCOUNT    int32              `json:"collectmoneycount"  xorm:"'collectmoneycount'"`       // 今日收款笔数 【汇总】
	COLLECTMONEYAMOUNT   float64            `json:"collectmoneyamount"  xorm:"'collectmoneyamount'"`     // 今日收款金额【汇总】
	PAYMONEYCOUNT        int32              `json:"paymoneycount"  xorm:"'paymoneycount'"`               // 今日付款笔数【汇总】
	PAYMONEYAMOUNT       float64            `json:"paymoneyamount"  xorm:"'paymoneyamount'"`             // 今日付款金额【汇总】
	COLLECTINVOICECOUNT  int32              `json:"collectinvoicecount"  xorm:"'collectinvoicecount'"`   // 今日开票笔数【汇总】
	COLLECTINVOICEAMOUNT float64            `json:"collectinvoiceamount"  xorm:"'collectinvoiceamount'"` // 今日开票金额【汇总】
	PAYINVOICECOUNT      int32              `json:"payinvoicecount"  xorm:"'payinvoicecount'"`           // 今日收票笔数【汇总】
	PAYINVOICEAMOUNT     float64            `json:"payinvoiceamount"  xorm:"'payinvoiceamount'"`         // 今日收票金额【汇总】
	UPDATETIME           string             `json:"updatetime"  xorm:"'updatetime'"`                     // 更新时间
	DayFR                []ErmcpReportDayFR `json:"dayFr"`                                               // 日报表明细
}

// 数据处理
func (r *ErmcpReportMonthFR) Calc() {

}

func (r *ErmcpReportMonthFR) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.CYCLETYPE," +
		"       t.CYCLETIME," +
		"       t.AREAUSERID," +
		"       t.COLLECTMONEYCOUNT," +
		"       t.COLLECTMONEYAMOUNT," +
		"       t.PAYMONEYCOUNT," +
		"       t.PAYMONEYAMOUNT," +
		"       t.COLLECTINVOICECOUNT," +
		"       t.COLLECTINVOICEAMOUNT," +
		"       t.PAYINVOICECOUNT," +
		"       t.PAYINVOICEAMOUNT," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh:mi:ss') UPDATETIME" +
		"  FROM REPORT_ERMCP_AREAFR t" +
		" WHERE 1 = 1"
	sqlId.And("t.CYCLETYPE", 1)
	sqlId.And("t.CYCLETIME", r.CYCLETIME)
	return sqlId.String()
}

// 获取月报表
func (r *ErmcpReportMonthFR) GetDataEx() (interface{}, error) {
	sData := make([]ErmcpReportMonthFR, 0)
	// 月报表
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)

	// 日报表明细
	if len(r.CYCLETIME) != 6 {
		// 月报表的日期应是6位,如 202101
		return nil, nil
	}
	t1 := r.CYCLETIME + "01"
	t2 := r.CYCLETIME + "31"

	for i := range sData {
		sData[i].Calc()
		dayM := ErmcpReportDayFR{AREAUSERID: r.AREAUSERID, BeginDate: t1, EndDate: t2}
		if dObj, err := dayM.GetDataEx(); err == nil {
			if d, ok := dObj.([]ErmcpReportDayFR); ok {
				sData[i].DayFR = d
			}
		}
	}

	return sData, err
}

// 敞口日报表
type ErmcpReportDayExposure struct {
	RECKONDATE            string  `json:"reckondate"  xorm:"'RECKONDATE'"`                       // 日照时期(yyyyMMdd)
	MIDDLEGOODSID         int64   `json:"middlegoodsid"  xorm:"'MIDDLEGOODSID'"`                 // 套保品种ID
	AREAUSERID            int64   `json:"areauserid"  xorm:"'AREAUSERID'"`                       // 所属机构
	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'"`               // 应套保敞口比例
	MIDDLEGOODSNAME       string  `json:"middlegoodsname"  xorm:"'MIDDLEGOODSNAME'"`             // 套保品种名称
	MIDDLEGOODSCODE       string  `json:"middlegoodscode"  xorm:"'MIDDLEGOODSCODE'"`             // 套保品种代码
	MIDDLEGOODSHEDGERATIO float64 `json:"middlegoodshedgeratio"  xorm:"'MIDDLEGOODSHEDGERATIO'"` // 应套保比例
	GOODSUNITID           int32   `json:"-"  xorm:"'GOODSUNITID'"`                               // 套保商品单位id

	ENUMDICNAME     string  `json:"enumdicname"`     // 单位名称
	OriTotalSpotQty float64 `json:"oriTotalSpotQty"` // 期初现货数量=(期初销售计划数量-期初销售合同已定价数量)-(期初采购计划数量-期初采购合同已定价数量)
	OriTotalFutuQty float64 `json:"oriTotalFutuQty"` // 期初期货数量=期初买入期货数量-期初卖出期货数量
	DiffSpotQty     float64 `json:"diffSpotQty"`     // 今日变动量(现货) = 现货数量 - 期初现货数量
	DiffFutuQty     float64 `json:"diffFutuQty"`     // 今日变动量(期货) = (买入 - 买入期初) - (卖出 - 卖出期初)
}

// 数据处理
func (r *ErmcpReportDayExposure) Calc() {
	r.OriTotalSpotQty = (r.ORISELLPLANQTY - r.ORISELLPRICEDQTY) - (r.ORIBUYPLANQTY - r.ORIBUYPRICEDQTY)
	r.OriTotalFutuQty = r.ORIBUYFUTUREQTY - r.ORISELLFUTUREQTY
	r.DiffSpotQty = r.TOTALSPOTQTY - r.OriTotalSpotQty
	r.DiffFutuQty = (r.BUYFUTUREQTY - r.ORIBUYFUTUREQTY) - (r.SELLFUTUREQTY - r.ORISELLFUTUREQTY)
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.GOODSUNITID)
}

func (r *ErmcpReportDayExposure) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.RECKONDATE," +
		"       t.MIDDLEGOODSID," +
		"       t.AREAUSERID," +
		"       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," +
		"       g.middlegoodsname," +
		"       g.middlegoodscode," +
		"       g.needhedgeratio MIDDLEGOODSHEDGERATIO," +
		"       g.goodsunitid" +
		"  FROM RECKON_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.RECKONDATE", r.RECKONDATE)
	return sqlId.String()
}

// 获取敞口日报表
func (r *ErmcpReportDayExposure) GetDataEx() (interface{}, error) {
	sData := make([]ErmcpReportDayExposure, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].Calc()
	}
	return sData, err
}