eta_task/models/data_manage/trade_position_analysis_guangzhou.go

package data_manage

import (
	"eta/eta_task/global"
	"eta/eta_task/utils"
	"time"
)

func MultiInsertTradeGuangzhouDataToTop(exchange string, startDate, endDate string) (err error) {
	o := global.DbMap[utils.DbNameIndex]
	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.Exec(sql1, now, now, startDate, endDate).Error
	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.Exec(sql2, now, now, startDate, endDate).Error
	if err != nil {
		return
	}

	//更新买单、卖单榜单排名字段
	var sql3 string
	var updatePars []interface{}
	if utils.DbDriverName == utils.DbDriverByDm {
		sql3 = `WITH ranked_data AS (
				SELECT
					classify_type,
					deal_short_name,
					data_time,
					deal_type,
					ROW_NUMBER() OVER (
						PARTITION BY data_time, deal_type, classify_type
						ORDER BY deal_value DESC
					) AS row_number
				FROM
					trade_position_guangzhou_top
				WHERE
					data_time BETWEEN ? AND ?
			)
			UPDATE trade_position_guangzhou_top s
			SET s.rank = (
				SELECT t.row_number
				FROM ranked_data t
				WHERE 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
			)
			WHERE s.data_time BETWEEN ? AND ?`
		updatePars = append(updatePars, startDate, endDate, startDate, endDate)
	} else {
		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 ?;`
		updatePars = append(updatePars, startDate, endDate, startDate, endDate)
	}

	err = o.Exec(sql3, updatePars...).Error
	return
}

// GetTradePositionTopOriginGuangzhouDataTimes 获取榜单原始数据日期-正序
func GetTradePositionTopOriginGuangzhouDataTimes(exchange string) (dates []string, err error) {
	o := global.DbMap[utils.DbNameIndex]
	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).Scan(&dates).Error
	return
}

func GetTradePositionOriginGuangzhouClassifyCountByExchangeDataTime(exchange string, startDate, endDate string) (count int64, err error) {
	o := global.DbMap[utils.DbNameIndex]
	var sql string
	var pars []interface{}
	if utils.DbDriverName == utils.DbDriverByDm {
		sql = `SELECT COUNT(*)
			FROM (
				SELECT 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 '%日成交持仓排名%'
			) AS distinct_records`
		pars = append(pars, startDate, endDate)
	} else {
		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`
		pars = append(pars, startDate, endDate)
	}

	err = o.Raw(sql, pars...).Scan(&count).Error
	return
}

func GetFirstBaseFromTradeGuangzhouIndexByDate(exchange string) (item *GetFirstBaseFromTradeIndeDate, err error) {
	o := global.DbMap[utils.DbNameIndex]
	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).First(&item).Error
	return
}

func GetTradePositionOriginClassifyGuangzhouByExchangeDataTime(exchange, startDate, endDate string) (list []TradePositionClassifyInfo, err error) {
	o := global.DbMap[utils.DbNameIndex]
	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).Scan(&list).Error
	return
}

func MultiInsertTradeBaseDataToTopGuangzhouByClassify(exchange string, startDate, endDate string, classifyNames, classifyTypes []string) (err error) {
	o := global.DbMap[utils.DbNameIndex]
	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.Exec(sql1, now, now, startDate, endDate, classifyNames, classifyTypes).Error
	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.Exec(sql2, now, now, startDate, endDate, classifyNames, classifyTypes).Error
	if err != nil {
		return
	}

	//更新买单、卖单榜单排名字段
	var sql3 string
	var updatePars []interface{}
	if utils.DbDriverName == utils.DbDriverByDm {
		// TODO:
		sql3 = `WITH ranked_data AS (
			SELECT
				classify_type,
				deal_short_name,
				data_time,
				deal_type,
				ROW_NUMBER() OVER (
					PARTITION BY data_time, deal_type, classify_type
					ORDER BY deal_value DESC
				) AS row_number
			FROM
				trade_position_guangzhou_top
			WHERE
				data_time BETWEEN ? AND ?
				AND classify_type IN (?)
		)
		UPDATE trade_position_guangzhou_top s
		SET s.rank = (
			SELECT t.row_number
			FROM ranked_data t
			WHERE 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
		)
		WHERE s.data_time BETWEEN ? AND ?
		  AND s.classify_type IN (?)`
		updatePars = append(updatePars, startDate, endDate, classifyTypes, startDate, endDate, classifyTypes)
	} else {
		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)) + `);`
		updatePars = append(updatePars, startDate, endDate, classifyTypes, startDate, endDate, classifyTypes)
	}

	// sql3 := `UPDATE trade_position_guangzhou_top s
	// SET s.rank = t.row_number
	// FROM (
	// 	SELECT
	// 		classify_type,
	// 		deal_short_name,
	// 		data_time,
	// 		deal_type,
	// 		ROW_NUMBER() OVER (
	// 			PARTITION BY data_time, deal_type, classify_type
	// 			ORDER BY deal_value DESC
	// 		) as row_number
	// 	FROM trade_position_guangzhou_top
	// 	WHERE data_time BETWEEN ? AND ?
	// 	AND classify_type IN (` + utils.GetOrmInReplace(len(classifyTypes)) + `)
	// ) t
	// WHERE 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
	// AND s.data_time BETWEEN ? AND ?
	// AND s.classify_type IN (` + utils.GetOrmInReplace(len(classifyTypes)) + `)`

	err = o.Exec(sql3, updatePars...).Error
	return
}