eta_task/models/data_manage/trade_position_analysis_guangzhou.go

package data_manage

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

func MultiInsertTradeGuangzhouDataToTop(exchange string, startDate, endDate string) (err error) {

	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 = global.DEFAULT_DmSQL.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 = global.DEFAULT_DmSQL.Exec(sql2, now, now, startDate, endDate).Error
	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 = global.DEFAULT_DmSQL.Exec(sql3, startDate, endDate, startDate, endDate).Error
	return
}

func GetTradePositionTopOriginGuangzhouDataTimes(exchange string) (dates []string, err error) {

	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 = global.DEFAULT_DmSQL.Raw(sql).Find(&dates).Error
	return
}

func GetTradePositionOriginGuangzhouClassifyCountByExchangeDataTime(exchange string, startDate, endDate string) (count int64, err error) {

	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 = global.DEFAULT_DmSQL.Raw(sql, startDate, endDate).Scan(&count).Error
	return
}

func GetFirstBaseFromTradeGuangzhouIndexByDate(exchange string) (item *GetFirstBaseFromTradeIndeDate, err error) {

	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 = global.DEFAULT_DmSQL.Raw(sql).First(&item).Error
	return
}

func GetTradePositionOriginClassifyGuangzhouByExchangeDataTime(exchange, startDate, endDate string) (list []TradePositionClassifyInfo, err error) {

	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 = global.DEFAULT_DmSQL.Raw(sql, startDate, endDate).Find(&list).Error
	return
}

func MultiInsertTradeBaseDataToTopGuangzhouByClassify(exchange string, startDate, endDate string, classifyNames, classifyTypes []string) (err error) {

	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 = global.DEFAULT_DmSQL.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 = global.DEFAULT_DmSQL.Exec(sql2, now, now, startDate, endDate, classifyNames, classifyTypes).Error
	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 = global.DEFAULT_DmSQL.Exec(sql3, startDate, endDate, classifyTypes, startDate, endDate, classifyTypes).Error
	return
}