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) {
	//	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()

	//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 = 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
}

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

	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) {
	//	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)

	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) {
	//	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)

	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) {
	//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)

	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) {
	//	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()

	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
}