eta_task/models/data_manage/trade_position_analysis_guangzhou.go

package data_manage

import (
	"eta/eta_task/utils"
	"github.com/beego/beego/v2/client/orm"
	"time"
)

func MultiInsertTradeGuangzhouDataToTop(exchange string, startDate, endDate string) (err error) {
	o := orm.NewOrm()
	now := time.Now().Format(utils.FormatDateTime)

	//新增买单榜单
	sql1 := `INSERT INTO trade_position_guangzhou_top(classify_name,classify_type,deal_short_name,deal_value,deal_change,data_time,deal_type,source_type,rank,create_time,modify_time)
SELECT
	n.classify_code,n.contract,SUBSTRING_INDEX(c.index_name, '_', 1) AS index_name,a.value,a.qty_sub,a.data_time,1,0,0,?,?
FROM
	base_from_trade_guangzhou_data a
	LEFT JOIN base_from_trade_guangzhou_index c ON a.base_from_trade_guangzhou_index_id = c.base_from_trade_guangzhou_index_id 
	LEFT JOIN base_from_trade_guangzhou_contract n on c.base_from_trade_guangzhou_contract_id = n.base_from_trade_guangzhou_contract_id
WHERE
	a.data_time between ? and ?
	and c.base_from_trade_guangzhou_classify_id in (7,8)
	and c.index_name like "%持买单量%"
	and c.index_name not like "%日成交持仓排名%"`
	_, err = o.Raw(sql1, now, now, startDate, endDate).Exec()
	if err != nil {
		return
	}
	//新增卖单榜单
	sql2 := `INSERT INTO trade_position_guangzhou_top(classify_name,classify_type,deal_short_name,deal_value,deal_change,data_time,deal_type,source_type,rank,create_time,modify_time)
SELECT
	n.classify_code,n.contract,SUBSTRING_INDEX(c.index_name, '_', 1) AS index_name,a.value,a.qty_sub,a.data_time,2,0,0,?,?
FROM
	base_from_trade_guangzhou_data a
	LEFT JOIN base_from_trade_guangzhou_index c ON a.base_from_trade_guangzhou_index_id = c.base_from_trade_guangzhou_index_id 
	LEFT JOIN base_from_trade_guangzhou_contract n on c.base_from_trade_guangzhou_contract_id = n.base_from_trade_guangzhou_contract_id
WHERE
	a.data_time between ? and ?
	and c.base_from_trade_guangzhou_classify_id in (7,8)
	and c.index_name like "%持卖单量%"
	and c.index_name not like "%日成交持仓排名%"`
	_, err = o.Raw(sql2, now, now, startDate, endDate).Exec()
	if err != nil {
		return
	}
	//更新买单、卖单榜单排名字段
	sql3 := `update trade_position_guangzhou_top s
JOIN (
SELECT
  	classify_type,
	deal_short_name,
	data_time,
	deal_type,
  (@row_number := IF(@prev_year = CONCAT_WS( "_", data_time, deal_type, classify_type), @row_number + 1, 1) ) AS row_number,
  @prev_year := CONCAT_WS( "_", data_time, deal_type, classify_type)
FROM
  trade_position_guangzhou_top,(SELECT @row_number := 0, @prev_year := NULL) r
	where data_time between ? and ?
ORDER BY
  data_time asc,
  deal_type asc,
  classify_type asc,
  deal_value DESC
	) t
	ON s.classify_type = t.classify_type AND s.deal_short_name = t.deal_short_name 	
	AND s.data_time = t.data_time AND s.deal_type = t.deal_type
SET s.rank = t.row_number where s.data_time between ? and ?;`

	_, err = o.Raw(sql3, startDate, endDate, startDate, endDate).Exec()

	return
}

// GetTradePositionTopOriginGuangzhouDataTimes 获取榜单原始数据日期-正序
func GetTradePositionTopOriginGuangzhouDataTimes(exchange string) (dates []string, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	DISTINCT a.data_time
FROM
	base_from_trade_guangzhou_data a
	LEFT JOIN base_from_trade_guangzhou_index c ON a.base_from_trade_guangzhou_index_id = c.base_from_trade_guangzhou_index_id
WHERE
    c.base_from_trade_guangzhou_classify_id IN ( 7, 8 ) 
	AND ( c.index_name LIKE "%持买单量%" OR c.index_name LIKE "%持卖单量%" ) 
	AND c.index_name NOT LIKE "%日成交持仓排名%" 
ORDER BY
	a.data_time asc`
	_, err = o.Raw(sql).QueryRows(&dates)
	return
}

func GetTradePositionOriginGuangzhouClassifyCountByExchangeDataTime(exchange string, startDate, endDate string) (count int64, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	count(DISTINCT n.classify_code, n.contract ) 
FROM
	base_from_trade_guangzhou_data a
	LEFT JOIN base_from_trade_guangzhou_index c ON a.base_from_trade_guangzhou_index_id = c.base_from_trade_guangzhou_index_id
	LEFT JOIN base_from_trade_guangzhou_contract n ON c.base_from_trade_guangzhou_contract_id = n.base_from_trade_guangzhou_contract_id 
WHERE
	a.data_time between ? and ?
	AND c.base_from_trade_guangzhou_classify_id IN ( 7, 8 ) 
	AND ( c.index_name LIKE "%持买单量%" OR c.index_name LIKE "%持卖单量%" ) 
	AND c.index_name NOT LIKE "%日成交持仓排名%" 
ORDER BY
	a.value DESC`
	err = o.Raw(sql, startDate, endDate).QueryRow(&count)
	return
}

func GetFirstBaseFromTradeGuangzhouIndexByDate(exchange string) (item *GetFirstBaseFromTradeIndeDate, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	a.data_time
FROM
	base_from_trade_guangzhou_data a
	LEFT JOIN base_from_trade_guangzhou_index c ON a.base_from_trade_guangzhou_index_id = c.base_from_trade_guangzhou_index_id
WHERE
	c.base_from_trade_guangzhou_classify_id IN ( 7, 8 ) 
	AND ( c.index_name LIKE "%持买单量%" OR c.index_name LIKE "%持卖单量%" ) 
	AND c.index_name NOT LIKE "%日成交持仓排名%" 
ORDER BY
	a.data_time asc`
	err = o.Raw(sql).QueryRow(&item)
	return
}

func GetTradePositionOriginClassifyGuangzhouByExchangeDataTime(exchange, startDate, endDate string) (list []TradePositionClassifyInfo, err error) {
	o := orm.NewOrm()
	sql := `SELECT DISTINCT classify_code as classify_name, contract as classify_type FROM base_from_trade_guangzhou_contract where  trade_date >= ? and trade_date <= ?`
	_, err = o.Raw(sql, startDate, endDate).QueryRows(&list)
	return
}

func MultiInsertTradeBaseDataToTopGuangzhouByClassify(exchange string, startDate, endDate string, classifyNames, classifyTypes []string) (err error) {
	o := orm.NewOrm()
	now := time.Now().Format(utils.FormatDateTime)

	//新增买单榜单
	sql1 := `INSERT INTO trade_position_guangzhou_top(classify_name,classify_type,deal_short_name,deal_value,deal_change,data_time,deal_type,source_type,rank,create_time,modify_time)
SELECT
	n.classify_code,n.contract,SUBSTRING_INDEX(c.index_name, '_', 1) AS index_name,a.value,a.qty_sub,a.data_time,1,0,0,?,?
FROM
	base_from_trade_guangzhou_data a
	LEFT JOIN base_from_trade_guangzhou_index c ON a.base_from_trade_guangzhou_index_id = c.base_from_trade_guangzhou_index_id 
	LEFT JOIN base_from_trade_guangzhou_contract n on c.base_from_trade_guangzhou_contract_id = n.base_from_trade_guangzhou_contract_id
WHERE
	a.data_time between ? and ?
	and c.base_from_trade_guangzhou_classify_id in (7,8)
    and n.classify_code in (` + utils.GetOrmInReplace(len(classifyNames)) + `) and n.contract in (` + utils.GetOrmInReplace(len(classifyTypes)) + `)
	and c.index_name like "%持买单量%"
	and c.index_name not like "%日成交持仓排名%"`
	_, err = o.Raw(sql1, now, now, startDate, endDate, classifyNames, classifyTypes).Exec()
	if err != nil {
		return
	}
	//新增卖单榜单
	sql2 := `INSERT INTO trade_position_guangzhou_top(classify_name,classify_type,deal_short_name,deal_value,deal_change,data_time,deal_type,source_type,rank,create_time,modify_time)
SELECT
	n.classify_code,n.contract,SUBSTRING_INDEX(c.index_name, '_', 1) AS index_name,a.value,a.qty_sub,a.data_time,2,0,0,?,?
FROM
	base_from_trade_guangzhou_data a
	LEFT JOIN base_from_trade_guangzhou_index c ON a.base_from_trade_guangzhou_index_id = c.base_from_trade_guangzhou_index_id 
	LEFT JOIN base_from_trade_guangzhou_contract n on c.base_from_trade_guangzhou_contract_id = n.base_from_trade_guangzhou_contract_id
WHERE
	a.data_time between ? and ?
	and c.base_from_trade_guangzhou_classify_id in (7,8)
  	and n.classify_code in (` + utils.GetOrmInReplace(len(classifyNames)) + `) and n.contract in (` + utils.GetOrmInReplace(len(classifyTypes)) + `)
	and c.index_name like "%持卖单量%"
	and c.index_name not like "%日成交持仓排名%"`
	_, err = o.Raw(sql2, now, now, startDate, endDate, classifyNames, classifyTypes).Exec()
	if err != nil {
		return
	}
	//更新买单、卖单榜单排名字段
	sql3 := `update trade_position_guangzhou_top s
JOIN (
SELECT
  	classify_type,
	deal_short_name,
	data_time,
	deal_type,
  (@row_number := IF(@prev_year = CONCAT_WS( "_", data_time, deal_type, classify_type), @row_number + 1, 1) ) AS row_number,
  @prev_year := CONCAT_WS( "_", data_time, deal_type, classify_type)
FROM
  trade_position_guangzhou_top,(SELECT @row_number := 0, @prev_year := NULL) r
	where data_time between ? and ? and classify_type in (` + utils.GetOrmInReplace(len(classifyTypes)) + `)
ORDER BY
  data_time asc,
  deal_type asc,
  classify_type asc,
  deal_value DESC
	) t
	ON s.classify_type = t.classify_type AND s.deal_short_name = t.deal_short_name 	
	AND s.data_time = t.data_time AND s.deal_type = t.deal_type
SET s.rank = t.row_number where s.data_time between ? and ? and s.classify_type in (` + utils.GetOrmInReplace(len(classifyTypes)) + `);`

	_, err = o.Raw(sql3, startDate, endDate, classifyTypes, startDate, endDate, classifyTypes).Exec()

	return
}