package excel

import (
	"eta_gn/eta_api/global"
	"eta_gn/eta_api/utils"
	"fmt"
	"time"
)

// ExcelEdbMapping excel与指标的关系表
type ExcelEdbMapping struct {
	ExcelEdbMappingId int       `orm:"column(excel_edb_mapping_id);pk" gorm:"primaryKey" `
	ExcelInfoId       int       `description:"excel的id"`
	Source            int       `description:"表格来源，1：excel插件的表格，2：自定义表格，3：混合表格，4：自定义分析，默认：1"`
	EdbInfoId         int       `description:"计算指标id"`
	CreateTime        time.Time `description:"创建时间"`
	ModifyTime        time.Time `description:"修改时间"`
}

// AddExcelEdbMappingMulti 批量添加excel与指标的关系
func AddExcelEdbMappingMulti(items []*ExcelEdbMapping) (err error) {
	//o := orm.NewOrmUsingDB("data")
	//_, err = o.InsertMulti(len(items), items)
	err = global.DmSQL["data"].CreateInBatches(items, utils.MultiAddNum).Error
	return
}

// Add 添加excel与指标的关系
func (e *ExcelEdbMapping) Add() (err error) {
	//o := orm.NewOrmUsingDB("data")
	//_, err = o.Insert(e)

	err = global.DmSQL["data"].Create(e).Error

	return
}

// Update 更新 excel表格基础信息
func (e *ExcelEdbMapping) Update(cols []string) (err error) {
	//o := orm.NewOrmUsingDB("data")
	//_, err = o.Update(e, cols...)
	err = global.DmSQL["data"].Select(cols).Updates(e).Error
	return
}

// GetExcelEdbMappingByEdbInfoId 根据指标id获取配置关系
func GetExcelEdbMappingByEdbInfoId(edbInfoId int) (items []*ExcelEdbMapping, err error) {
	//o := orm.NewOrmUsingDB("data")
	//sql := ` SELECT *  FROM excel_edb_mapping WHERE 1=1 AND edb_info_id = ? `
	//
	//_, err = o.Raw(sql, edbInfoId).QueryRows(&items)

	sql := ` SELECT *  FROM excel_edb_mapping WHERE 1=1 AND edb_info_id = ? `
	err = global.DmSQL["data"].Raw(sql, edbInfoId).Find(&items).Error
	return
}

// GetExcelEdbMappingByExcelInfoId 根据excel的id获取配置关系
func GetExcelEdbMappingByExcelInfoId(excelInfoId int) (items []*ExcelEdbMapping, err error) {
	//o := orm.NewOrmUsingDB("data")
	//sql := ` SELECT *  FROM excel_edb_mapping AS a
	//       join edb_info as b on a.edb_info_id = b.edb_info_id
	//       WHERE 1=1 AND a.excel_info_id = ? `
	//_, err = o.Raw(sql, excelInfoId).QueryRows(&items)

	sql := ` SELECT *  FROM excel_edb_mapping AS a
	      join edb_info as b on a.edb_info_id = b.edb_info_id
	      WHERE 1=1 AND a.excel_info_id = ? `
	err = global.DmSQL["data"].Raw(sql, excelInfoId).Find(&items).Error

	return
}

type ExcelEdbMappingItem struct {
	EdbInfoId        int    `description:"指标id"`
	UniqueCode       string `description:"唯一编码"`
	EdbName          string `description:"指标名称"`
	ClassifyId       int    `description:"分类id"`
	Frequency        string `description:"频度"`
	Unit             string `description:"单位"`
	CalculateFormula string `json:"-"`
	DateSequenceStr  string `description:"日期序列公式"`
	DataSequenceStr  string `description:"数据序列公式"`
}

// CalculateFormula 计算公式
type CalculateFormula struct {
	DateSequenceStr string `json:"DateSequenceStr"`
	DataSequenceStr string `json:"DataSequenceStr"`
}

// GetAllExcelEdbMappingItemByExcelInfoId 根据品种id获取所有的指标结果集
func GetAllExcelEdbMappingItemByExcelInfoId(excelInfoId int) (items []*ExcelEdbMappingItem, err error) {
	//o := orm.NewOrmUsingDB("data")
	//sql := `SELECT a.edb_info_id,a.unique_code,a.edb_name,a.classify_id,a.frequency,a.unit,calculate_formula FROM edb_info AS a
	//     JOIN excel_edb_mapping AS b ON a.edb_info_id=b.edb_info_id
	//     WHERE b.excel_info_id = ? ORDER BY b.excel_edb_mapping_id ASC `
	//_, err = o.Raw(sql, excelInfoId).QueryRows(&items)

	sql := `SELECT a.edb_info_id,a.unique_code,a.edb_name,a.classify_id,a.frequency,a.unit,calculate_formula FROM edb_info AS a 
	    JOIN excel_edb_mapping AS b ON a.edb_info_id=b.edb_info_id 
	    WHERE b.excel_info_id = ? ORDER BY b.excel_edb_mapping_id ASC `
	err = global.DmSQL["data"].Raw(sql, excelInfoId).Find(&items).Error

	return
}

// GetNoCustomAnalysisExcelEdbMappingCount 根据指标id获取非自定义分析的关联关系
func GetNoCustomAnalysisExcelEdbMappingCount(edbInfoId int) (count int, err error) {
	//o := orm.NewOrmUsingDB("data")
	//sql := ` SELECT COUNT(1) AS count FROM excel_edb_mapping a
	//                      join excel_info b on a.excel_info_id=b.excel_info_id
	//                      WHERE edb_info_id=? AND a.source != 4 AND b.is_delete = 0`
	//err = o.Raw(sql, edbInfoId).QueryRow(&count)

	sql := ` SELECT COUNT(1) AS count FROM excel_edb_mapping a 
	                     join excel_info b on a.excel_info_id=b.excel_info_id
	                     WHERE edb_info_id=? AND a.source != 4 AND b.is_delete = 0`
	err = global.DmSQL["data"].Raw(sql, edbInfoId).Scan(&count).Error
	return
}

type ExcelEdbMappingWithParentIdItem struct {
	ExcelInfoId int
	ParentId    int
}

func GetNoCustomAnalysisExcelEdbMapping(edbInfoId int) (items []ExcelEdbMappingWithParentIdItem, err error) {
	//o := orm.NewOrmUsingDB("data")
	//sql := ` SELECT b.excel_info_id, b.parent_id FROM excel_edb_mapping a
	//                      join excel_info b on a.excel_info_id=b.excel_info_id
	//                      WHERE edb_info_id=? AND a.source != 4 AND b.is_delete = 0`
	//_, err = o.Raw(sql, edbInfoId).QueryRows(&items)
	sql := ` SELECT b.excel_info_id, b.parent_id FROM excel_edb_mapping a 
	                     join excel_info b on a.excel_info_id=b.excel_info_id
	                     WHERE edb_info_id=? AND a.source != 4 AND b.is_delete = 0`
	err = global.DmSQL["data"].Raw(sql, edbInfoId).Find(&items).Error
	return
}

// GetAllExcelEdbMappingByExcelInfoId 根据excel的id获取所有的指标
func GetAllExcelEdbMappingByExcelInfoId(excelInfoId int) (items []*ExcelEdbMapping, err error) {
	//o := orm.NewOrmUsingDB("data")
	//sql := `SELECT a.* FROM  excel_edb_mapping a
	//     WHERE a.excel_info_id = ? ORDER BY a.excel_edb_mapping_id ASC `
	//_, err = o.Raw(sql, excelInfoId).QueryRows(&items)

	sql := `SELECT a.* FROM  excel_edb_mapping a
	    WHERE a.excel_info_id = ? ORDER BY a.excel_edb_mapping_id ASC `
	err = global.DmSQL["data"].Raw(sql, excelInfoId).Find(&items).Error
	return
}

// GetAllExcelEdbMappingByExcelInfoIds 根据excel的id获取所有的指标
func GetAllExcelEdbMappingByExcelInfoIds(excelInfoIds []int) (items []*ExcelEdbMapping, err error) {
	//o := orm.NewOrmUsingDB("data")
	//sql := `SELECT a.* FROM  excel_edb_mapping a
	//     WHERE a.excel_info_id in (` + utils.GetOrmInReplace(len(excelInfoIds)) + `) ORDER BY a.excel_edb_mapping_id ASC `
	//_, err = o.Raw(sql, excelInfoIds).QueryRows(&items)

	sql := `SELECT a.* FROM  excel_edb_mapping a
	    WHERE a.excel_info_id in (` + utils.GetOrmInReplace(len(excelInfoIds)) + `) ORDER BY a.excel_edb_mapping_id ASC `
	err = global.DmSQL["data"].Raw(sql, excelInfoIds).Find(&items).Error
	return
}

// GetExcelEdbMappingByEdbInfoIdAndSource 根据指标id获取配置关系
func GetExcelEdbMappingByEdbInfoIdAndSource(edbInfoId int, sources []int) (items []*ExcelEdbMapping, err error) {
	//o := orm.NewOrmUsingDB("data")
	//sql := ` SELECT *  FROM excel_edb_mapping WHERE 1=1 AND edb_info_id = ? AND source in (` + utils.GetOrmInReplace(len(sources)) + `) `
	//
	//_, err = o.Raw(sql, edbInfoId, sources).QueryRows(&items)

	sql := ` SELECT *  FROM excel_edb_mapping WHERE 1=1 AND edb_info_id = ? AND source in (` + utils.GetOrmInReplace(len(sources)) + `) `
	err = global.DmSQL["data"].Raw(sql, edbInfoId, sources).Find(&items).Error
	return
}

// DeleteCustomAnalysisExcelEdbMappingByEdbInfoId
// @Description: 根据指标id删除与自定义分析表格的关系
// @author: Roc
// @datetime 2023-11-02 13:20:02
// @param edbInfoId int
// @return err error
func DeleteCustomAnalysisExcelEdbMappingByEdbInfoId(edbInfoId int) (err error) {
	//o := orm.NewOrmUsingDB("data")
	//sql := `DELETE FROM excel_edb_mapping WHERE source = ? AND edb_info_id = ? LIMIT 1`
	//_, err = o.Raw(sql, utils.CUSTOM_ANALYSIS_TABLE, edbInfoId).Exec()

	sql := `DELETE FROM excel_edb_mapping WHERE source = ? AND edb_info_id = ? LIMIT 1`
	err = global.DmSQL["data"].Exec(sql, utils.CUSTOM_ANALYSIS_TABLE, edbInfoId).Error
	return
}

// GetExcelEdbMappingItemByExcelInfoIdOrKeyword 根据表格ID或关键词获取指标
func GetExcelEdbMappingItemByExcelInfoIdOrKeyword(excelInfoId int, keyword string) (items []*ExcelEdbMappingItem, err error) {
	//o := orm.NewOrmUsingDB("data")
	//cond := `b.excel_info_id = ?`
	//pars := make([]interface{}, 0)
	//pars = append(pars, excelInfoId)
	//if keyword != "" {
	//	cond += ` AND (a.edb_code LIKE ? OR a.edb_name LIKE ?)`
	//	pars = append(pars, keyword, keyword)
	//}
	//sql := fmt.Sprintf(`SELECT
	//		a.edb_info_id,
	//		a.unique_code,
	//		a.edb_name,
	//		a.classify_id,
	//		a.frequency,
	//		a.unit,
	//		calculate_formula
	//	FROM
	//		edb_info AS a
	//	JOIN excel_edb_mapping AS b ON a.edb_info_id = b.edb_info_id
	//	WHERE
	//		%s
	//	ORDER BY
	//		b.excel_edb_mapping_id ASC`, cond)
	//_, err = o.Raw(sql, pars).QueryRows(&items)

	cond := `b.excel_info_id = ?`
	pars := make([]interface{}, 0)
	pars = append(pars, excelInfoId)
	if keyword != "" {
		cond += ` AND (a.edb_code LIKE ? OR a.edb_name LIKE ?)`
		pars = append(pars, keyword, keyword)
	}
	sql := fmt.Sprintf(`SELECT
			a.edb_info_id,
			a.unique_code,
			a.edb_name,
			a.classify_id,
			a.frequency,
			a.unit,
			calculate_formula
		FROM
			edb_info AS a
		JOIN excel_edb_mapping AS b ON a.edb_info_id = b.edb_info_id
		WHERE
			%s
		ORDER BY
			b.excel_edb_mapping_id ASC`, cond)
	err = global.DmSQL["data"].Raw(sql, pars...).Find(&items).Error
	return
}