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"
)

// ErmcpReportDayFR 财务日报表
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:"-"`                                                   // 结束日期
}

// Calc 数据处理
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"

	// 查询条件
	sqlId.And("t.AREAUSERID", r.AREAUSERID)
	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()
}

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

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

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

// ErmcpReportOPLog 报表合同操作记录通用查询
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"`                               // 操作人名称
	BRANDNAME      string `json:"brandname"  xorm:"'brandname'"`           // 品牌名称
	MODELNAME      string `json:"modelname"  xorm:"'modelname'"`           // 型号名称

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

// Calc 处理数据
func (r *ErmcpReportOPLog) 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:退还保证金
	// 数据库注释与返回值映身关系： 结算量->确定量, 收款->收款金额, 退款->退款金额, 付款->付款金额, 收票->收票金额, 开票->开票金额
	sDes := []string{"点价价格", "点价数量", "确定量", "其它费用", "追加保证金", "溢短金额", "调整金额", "付款", "收款", "退款",
		"收票", "开票", "提交审核(合同)", "审核通过(合同)", "审核拒绝(合同)", "合同撤回", "提交审核(计划)",
		"审核通过(计划)", "审核拒绝(计划)", "计划撤回", "正常完结(合同)", "异常终止(合同)", "退还保证金"}
	if r.OPERATELOGTYPE >= 1 && r.OPERATELOGTYPE <= 23 {
		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 *ErmcpReportOPLog) 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," +
		"       s.spotgoodsmodelid," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       w.unitid," +
		"       gb.brandname," +
		"       gm.modelname" +
		"  FROM ERMCP_CONTRACTOPERATELOG t" +
		" inner join ermcp_spotcontract s" +
		"    on t.RELATEDID = s.spotcontractid" +
		"   and t.userid = %v" +
		"  left join wrstandard w" +
		"    on t.wrstandardid = w.wrstandardid" +
		"  left join spotgoodsbrand gb" +
		"    on s.spotgoodsbrandid = gb.brandid" +
		"  left join spotgoodsmodel gm" +
		"    on s.spotgoodsmodelid = gm.modelid" +
		" WHERE t.biztype = 2"
	sqlId = utils.SQLVal(fmt.Sprintf(sqlId.String(), r.USERID))
	// 筛选条件
	sqlId.And("t.TRADEDATE", r.TRADEDATE)
	if r.WRSTANDARDID > 0 {
		sqlId.And("t.WRSTANDARDID", r.WRSTANDARDID)
	}
	if r.LogTypeFilter != "" {
		sqlId.Join(fmt.Sprintf(" and t.OPERATELOGTYPE in(%v)", r.LogTypeFilter))
	}
	return sqlId.String()
}

// GetDataEx 获取日志记录
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
}

// ErmcpReportMonthFR 财务月报表
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"`                                               // 日报表明细
}

// Calc 数据处理
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.AREAUSERID", r.AREAUSERID)
	sqlId.And("t.CYCLETYPE", 1)
	sqlId.And("t.CYCLETIME", r.CYCLETIME)
	return sqlId.String()
}

// GetDataEx 获取月报表
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
}

// ErmcpReportDayExposure 敞口日报表
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"`     // 今日变动量(期货) = (买入 - 买入期初) - (卖出 - 卖出期初)
}

// Calc 数据处理
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()
}

// GetDataEx 获取敞口日报表
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
}

// ErmcpReportDaySpot 现货日报表
type ErmcpReportDaySpot struct {
	BUYPRICEDQTY       float64 `json:"-"  xorm:"'BUYPRICEDQTY'"`                        // 期末采购定价量
	SELLPRICEDQTY      float64 `json:"-"  xorm:"'SELLPRICEDQTY'"`                       // 期末销售定价量
	ORIBUYPRICEDQTY    float64 `json:"-"  xorm:"'ORIBUYPRICEDQTY'"`                     // 期初采购定价量
	ORISELLPRICEDQTY   float64 `json:"-"  xorm:"'ORISELLPRICEDQTY'"`                    // 期初销售定价量
	TODAYBUYRECKONQTY  float64 `json:"todaybuyreckonqty"  xorm:"'TODAYBUYRECKONQTY'"`   // 采购确定量
	TODAYSELLRECKONQTY float64 `json:"todaysellreckonqty"  xorm:"'TODAYSELLRECKONQTY'"` // 销售确定量
	RECKONDATE         string  `json:"reckondate"  xorm:"'RECKONDATE'"`                 // 日照时期(yyyyMMdd)
	WRSTANDARDID       int64   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`             // 现货商品ID
	AREAUSERID         int64   `json:"areauserid"  xorm:"'AREAUSERID'"`                 // 所属机构T
	WRSTANDARDNAME     string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`         // 现货商品名称
	WRSTANDARDCODE     string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`         // 现货商品代码
	UNITID             int32   `json:"-"  xorm:"'UNITID'"`                              // 现货商品单位id
	BUYINQTY           float64 `json:"buyinqty"  xorm:"'BUYINQTY'"`                     // 采购入库量
	SELLOUTQTY         float64 `json:"selloutqty"  xorm:"'SELLOUTQTY'"`                 // 销售出库量

	ENUMDICNAME        string  `json:"enumdicname"`        // 现货商品单位名称
	TOTALBUYPRICEDQTY  float64 `json:"totalbuypricedqty"`  // 采购定价量 = 期末 - 期初
	TOTALSELLPRICEDQTY float64 `json:"totalsellpricedqty"` // 销售定价量 = 期末 - 期初
	BeginDate          string  `json:"-"`                  // 开始日期
	EndDate            string  `json:"-"`                  // 结束日期
}

// Calc 数据处理
func (r *ErmcpReportDaySpot) Calc() {
	r.TOTALBUYPRICEDQTY = r.BUYPRICEDQTY - r.ORIBUYPRICEDQTY
	r.TOTALSELLPRICEDQTY = r.SELLPRICEDQTY - r.ORISELLPRICEDQTY
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID)
}

func (r *ErmcpReportDaySpot) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select t.userid," +
		"         t.wrstandardid," +
		"         t.reckondate," +
		"         sum(t.todaybuyinqty) todaybuyinqty," +
		"         sum(t.todayselloutqty) todayselloutqty" +
		"    from Reckon_ERMCP_AreaStock t" +
		"   group by t.userid, t.wrstandardid,t.reckondate)" +
		"select t.reckondate," +
		"       t.BUYPRICEDQTY," +
		"       t.SELLPRICEDQTY," +
		"       t.Oribuypricedqty," +
		"       t.Orisellpricedqty," +
		"       t.TODAYBUYRECKONQTY," +
		"       t.TODAYSELLRECKONQTY," +
		"       t.RECKONDATE," +
		"       t.WRSTANDARDID," +
		"       t.AREAUSERID," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       w.unitid," +
		"       s.todaybuyinqty      buyinqty," +
		"       s.todayselloutqty    selloutqty" +
		"  from RECKON_ERMCP_AREASPOT t" +
		"  left join tmp s" +
		"    on t.reckondate = s.reckondate" +
		"   and t.areauserid = s.userid" +
		"   and t.wrstandardid = s.wrstandardid" +
		"  left join wrstandard w" +
		"    on t.wrstandardid = w.wrstandardid" +
		" where 1 = 1"
	sqlId.And("t.AREAUSERID", r.AREAUSERID)
	if r.WRSTANDARDID > 0 {
		sqlId.And("t.WRSTANDARDID", r.WRSTANDARDID)
	}
	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 *ErmcpReportDaySpot) GetDataEx() (interface{}, error) {
	sData := make([]ErmcpReportDaySpot, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].Calc()
	}
	return sData, err
}

// ErmcpReportMonSpot 现货月报表
type ErmcpReportMonSpot struct {
	CYCLETYPE          int32   `json:"cycletype"  xorm:"'cycletype'"`                   // 周期类型 - 1:月 2:季 3:年 4:周 5:全报表【原值】
	CYCLETIME          string  `json:"cycletime"  xorm:"'cycletime'"`                   // 周期时间 月(YYYYMM)  季(YYYYQ) 年(YYYY) 周(YYYYIW) 全(0)【原值】
	BUYPRICEDQTY       float64 `json:"-"  xorm:"'BUYPRICEDQTY'"`                        // 期末采购定价量
	SELLPRICEDQTY      float64 `json:"-"  xorm:"'SELLPRICEDQTY'"`                       // 期末销售定价量
	ORIBUYPRICEDQTY    float64 `json:"-"  xorm:"'ORIBUYPRICEDQTY'"`                     // 期初采购定价量
	ORISELLPRICEDQTY   float64 `json:"-"  xorm:"'ORISELLPRICEDQTY'"`                    // 期初销售定价量
	TODAYBUYRECKONQTY  float64 `json:"todaybuyreckonqty"  xorm:"'TODAYBUYRECKONQTY'"`   // 采购确定量
	TODAYSELLRECKONQTY float64 `json:"todaysellreckonqty"  xorm:"'TODAYSELLRECKONQTY'"` // 销售确定量
	WRSTANDARDID       int64   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`             // 现货商品ID
	AREAUSERID         int64   `json:"areauserid"  xorm:"'AREAUSERID'"`                 // 所属机构T
	WRSTANDARDNAME     string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`         // 现货商品名称
	WRSTANDARDCODE     string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`         // 现货商品代码
	UNITID             int32   `json:"-"  xorm:"'UNITID'"`                              // 现货商品单位id
	BUYINQTY           float64 `json:"buyinqty"  xorm:"'BUYINQTY'"`                     // 采购入库量
	SELLOUTQTY         float64 `json:"selloutqty"  xorm:"'SELLOUTQTY'"`                 // 销售出库量

	ENUMDICNAME        string  `json:"enumdicname"`        // 现货商品单位名称
	TOTALBUYPRICEDQTY  float64 `json:"totalbuypricedqty"`  // 采购定价量 = 期末 - 期初
	TOTALSELLPRICEDQTY float64 `json:"totalsellpricedqty"` // 销售定价量 = 期末 - 期初
}

// Calc 数据处理
func (r *ErmcpReportMonSpot) Calc() {
	r.TOTALBUYPRICEDQTY = r.BUYPRICEDQTY - r.ORIBUYPRICEDQTY
	r.TOTALSELLPRICEDQTY = r.SELLPRICEDQTY - r.ORISELLPRICEDQTY
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID)
}

func (r *ErmcpReportMonSpot) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select t.userid," +
		"         t.wrstandardid," +
		"         t.cycletime," +
		"         t.cycletype," +
		"         sum(t.todaybuyinqty) todaybuyinqty," +
		"         sum(t.todayselloutqty) todayselloutqty" +
		"    from Report_ERMCP_AreaStock t" +
		"   group by t.userid, t.wrstandardid, t.cycletime, t.cycletype)" +
		"select t.BUYPRICEDQTY," +
		"       t.SELLPRICEDQTY," +
		"       t.Oribuypricedqty," +
		"       t.Orisellpricedqty," +
		"       t.TODAYBUYRECKONQTY," +
		"       t.TODAYSELLRECKONQTY," +
		"       t.cycletype," +
		"       t.cycletime," +
		"       t.WRSTANDARDID," +
		"       t.AREAUSERID," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       w.unitid," +
		"       s.todaybuyinqty      buyinqty," +
		"       s.todayselloutqty    selloutqty" +
		"  from Report_ERMCP_AreaSpot t" +
		"  left join tmp s" +
		"    on t.cycletime = s.cycletime" +
		"   and t.cycletype = s.cycletype" +
		"   and t.areauserid = s.userid" +
		"   and t.wrstandardid = s.wrstandardid" +
		"  left join wrstandard w" +
		"    on t.wrstandardid = w.wrstandardid" +
		" where t.cycletype = 1"
	sqlId.And("t.AREAUSERID", r.AREAUSERID)
	sqlId.And("t.cycletime", r.CYCLETIME)
	return sqlId.String()
}

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

// ErmcpReportAreaSpotPL 现货损益日/月表
type ErmcpReportAreaSpotPL struct {
	AREAUSERID            int64   `json:"areauserid"  xorm:"'AREAUSERID'"`                       // 所属机构
	WRSTANDARDID          int64   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`                   // 现货商品ID
	SPOTGOODSMODELID      int32   `json:"spotgoodsmodelid"  xorm:"'SPOTGOODSMODELID'"`           // 现货品类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       float64 `json:"oriaverageprice"  xorm:"'ORIAVERAGEPRICE'"`             // 期初均价
	ORIAMOUNT             float64 `json:"oriamount"  xorm:"'ORIAMOUNT'"`                         // 期初额
	TODAYBUYQTY           float64 `json:"todaybuyqty"  xorm:"'TODAYBUYQTY'"`                     // 今日采购量(采购增量)
	TODAYBUYAMOUNT        float64 `json:"todaybuyamount"  xorm:"'TODAYBUYAMOUNT'"`               // 今日采购额
	TODAYBUYAVERAGEPRICE  float64 `json:"todaybuyaverageprice"  xorm:"'TODAYBUYAVERAGEPRICE'"`   // 今日采购均价(采购均价)
	TODAYSELLQTY          float64 `json:"todaysellqty"  xorm:"'TODAYSELLQTY'"`                   // 今日销售量(销售增量)
	TODAYSELLAMOUNT       float64 `json:"todaysellamount"  xorm:"'TODAYSELLAMOUNT'"`             // 今日销售额
	TODAYSELLAVERAGEPRICE float64 `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       float64 `json:"curaverageprice"  xorm:"'CURAVERAGEPRICE'"`             // 期末均价
	CURAMOUNT             float64 `json:"curamount"  xorm:"'CURAMOUNT'"`                         // 期末额
	CURSPOTPRICE          float64 `json:"curspotprice"  xorm:"'CURSPOTPRICE'"`                   // 参考市价
	CURMARKETVALUE        float64 `json:"curmarketvalue"  xorm:"'CURMARKETVALUE'"`               // 参考市值
	ACTUALPL              float64 `json:"actualpl"  xorm:"'ACTUALPL'"`                           // 实际损益
	FLOATPL               float64 `json:"floatpl"  xorm:"'FLOATPL'"`                             // 浮动损益
	UPDATETIME            string  `json:"updatetime"  xorm:"'UPDATETIME'"`                       // 更新时间
	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'"`                         // 品牌名称
	MODELNAME             string  `json:"modelname"  xorm:"'MODELNAME'"`                         // 品类名称
	GBUNITID              int32   `json:"gbunitid"  xorm:"'GBUNITID'"`                           // 品类单位id

	ENUMDICNAME   string `json:"enumdicname"`   // 现货商品单位名称
	GBENUMDICNAME string `json:"gbenumdicname"` // 品类单位名称

	ReportType int32  `json:"-"` // 报表类型 1-日报表 2-月报表
	ReportDate string `json:"-"` // 格式 日报表(YYYYMMDD) 月报表(YYYYMM)
}

func (r *ErmcpReportAreaSpotPL) calc() {
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID)
	r.GBENUMDICNAME = mtpcache.GetEnumDicitemName(r.GBUNITID)
}

func (r *ErmcpReportAreaSpotPL) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.AREAUSERID," +
		"       t.WRSTANDARDID," +
		"       t.SPOTGOODSMODELID," +
		"       t.SPOTGOODSBRANDID," +
		"       t.ORIBUYQTY," +
		"       t.ORIBUYAMOUNT," +
		"       t.ORISELLQTY," +
		"       t.ORISELLAMOUNT," +
		"       t.ORIQTY," +
		"       t.ORIAVERAGEPRICE," +
		"       t.ORIAMOUNT," +
		"       t.TODAYBUYQTY," +
		"       t.TODAYBUYAMOUNT," +
		"       t.TODAYBUYAVERAGEPRICE," +
		"       t.TODAYSELLQTY," +
		"       t.TODAYSELLAMOUNT," +
		"       t.TODAYSELLAVERAGEPRICE," +
		"       t.CURBUYQTY," +
		"       t.CURBUYAMOUNT," +
		"       t.CURSELLQTY," +
		"       t.CURSELLAMOUNT," +
		"       t.CURQTY," +
		"       t.CURAVERAGEPRICE," +
		"       t.CURAMOUNT," +
		"       t.CURSPOTPRICE," +
		"       t.CURMARKETVALUE," +
		"       t.ACTUALPL," +
		"       t.FLOATPL," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       w.wrstandardcode," +
		"       w.wrstandardname," +
		"       w.unitid," +
		"       gb.brandname," +
		"       gm.modelname," +
		"       gm.unitid gbunitid" +
		"  FROM %v t" +
		"  left join wrstandard w" +
		"    on t.wrstandardid = w.wrstandardid" +
		"  left join spotgoodsbrand gb" +
		"    on t.spotgoodsbrandid = gb.brandid" +
		"  left join spotgoodsmodel gm" +
		"    on t.spotgoodsmodelid = gm.modelid" +
		" WHERE 1 = 1"
	sqlId.And("t.AREAUSERID", r.AREAUSERID)
	if r.ReportType == 1 {
		// 日报表
		sqlId.FormatParam("RECKON_ERMCP_AREASPOTPL")
		sqlId.And("t.reckondate", r.ReportDate)
	} else {
		// 月报表
		sqlId.FormatParam("REPORT_ERMCP_AREASPOTPL")
		sqlId.And("t.cycletype", 1)
		sqlId.And("t.cycletime", r.ReportDate)
	}
	if r.WRSTANDARDID > 0 {
		sqlId.And("t.wrstandardid", r.WRSTANDARDID)
	}
	if r.SPOTGOODSBRANDID > 0 {
		sqlId.And("t.spotgoodsbrandid", r.SPOTGOODSBRANDID)
	}
	if r.SPOTGOODSMODELID > 0 {
		sqlId.And("t.spotgoodsmodelid", r.SPOTGOODSMODELID)
	}
	return sqlId.String()
}

// GetDataEx 获取现货损益日(月)报表
func (r *ErmcpReportAreaSpotPL) GetDataEx() (interface{}, error) {
	sData := make([]ErmcpReportAreaSpotPL, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}