eta_api/models/data_manage/my_chart.go

package data_manage

import (
	"eta_gn/eta_api/global"
	"eta_gn/eta_api/utils"
	"fmt"
	"github.com/rdlucklib/rdluck_tools/paging"
	"time"
)

type ChartListResp struct {
	Paging *paging.PagingItem
	List   []*ChartInfoView
}

func GetChartListByCondition(condition string, pars []interface{}, startSize, pageSize int) (item []*ChartInfoView, err error) {
	sql := ` SELECT * FROM chart_info WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	sql += " ORDER BY create_time DESC LIMIT ?,? "
	pars = append(pars, startSize)
	pars = append(pars, pageSize)
	err = global.DmSQL["data"].Raw(sql, pars...).Find(&item).Error
	return
}

func GetChartListCountByCondition(condition string, pars []interface{}) (count int, err error) {
	sql := ` SELECT COUNT(1) AS count FROM chart_info WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	err = global.DmSQL["data"].Raw(sql, pars...).Scan(&count).Error

	return
}

type MyChartClassify struct {
	MyChartClassifyId   int `orm:"column(my_chart_classify_id);pk" gorm:"primaryKey" `
	MyChartClassifyName string
	Sort                int       `json:"-" description:"排序字段，值越小，排的越靠前"`
	AdminId             int       `description:"创建人id"`
	IsPublic            int       `description:"是否公共分类"`
	IsCompanyPublic     int       `description:"是否为用户公共分类"`
	CreateTime          time.Time `json:"-"`
	ModifyTime          time.Time `json:"-"`
	PublicTime          time.Time `json:"-" description:"公开（分享）时间"`
	CompanyPublicTime   time.Time `json:"-" description:"用户可见公开时间"`
}

func AddMyChartClassify(item *MyChartClassify) (err error) {
	err = global.DmSQL["data"].Create(item).Error
	return
}

func GetMyChartClassifyAll(adminId int) (item []*MyChartClassify, err error) {
	sql := ` SELECT * FROM my_chart_classify WHERE 1=1 AND admin_id=? `
	sql += " ORDER BY sort asc, my_chart_classify_id asc "
	err = global.DmSQL["data"].Raw(sql, adminId).Find(&item).Error
	return
}

func GetPublicChartClassifyAllExceptMy(adminId int) (item []*MyChartClassify, err error) {
	sql := ` SELECT * FROM my_chart_classify WHERE 1=1 AND is_public=1 AND admin_id !=?`
	sql += " ORDER BY public_time asc"
	err = global.DmSQL["data"].Raw(sql, adminId).Find(&item).Error
	return
}

type MyChartClassifyResp struct {
	List     []*MyChartClassifyItem
	Language string `description:"指标的展示语言，CN：中文，EN：英文"`
}

type PublicChartClassifyResp struct {
	List     []PublicChartClassifyList
	Language string `description:"指标的展示语言，CN：中文，EN：英文"`
}

type PublicChartClassifyItem struct {
	MyChartClassifyId   int    `description:"分类id"`
	MyChartClassifyName string `description:"分类名称"`
	AdminId             int    `description:"创建人id"`
	RealName            string `description:"创建人名称"`
	IsPublic            int    `description:"是否公共分类"`
	IsCompanyPublic     int    `description:"是否为客户可见"`
}

type PublicChartClassifyList struct {
	MenuAdminId int                       `description:"目录创建人ID"`
	MenuName    string                    `description:"目录名称"`
	Items       []PublicChartClassifyItem `description:"分类数据"`
}

type MyChartClassifyAddReq struct {
	MyChartClassifyName string `description:"分类名称"`
}

func GetMyChartClassifyCountByCondition(condition string, pars []interface{}) (count int, err error) {
	sql := ` SELECT COUNT(1) AS count FROM my_chart_classify WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	err = global.DmSQL["data"].Raw(sql, pars...).Scan(&count).Error
	return
}

func GetMyChartClassifyById(adminId, myChartClassifyId int) (item *MyChartClassify, err error) {
	sql := ` SELECT * FROM my_chart_classify WHERE admin_id=? AND my_chart_classify_id=? `
	err = global.DmSQL["data"].Raw(sql, adminId, myChartClassifyId).First(&item).Error
	return
}

func GetMyChartClassifyMinSort(adminId int) (sort int, err error) {
	sql := ` SELECT COALESCE(MIN(sort), 0) as sort FROM my_chart_classify WHERE admin_id=? `
	err = global.DmSQL["data"].Raw(sql, adminId).Scan(&sort).Error
	return
}

type MyChartClassifyEditReq struct {
	MyChartClassifyId   int    `description:"分类ID"`
	MyChartClassifyName string `description:"分类名称"`
}

type MyChartClassifyMoveReq struct {
	MyChartClassifyId int `description:"分类id"`
	PrevClassifyId    int `description:"上一个兄弟节点分类id"`
	NextClassifyId    int `description:"下一个兄弟节点分类id"`
}

func ModifyMyChartClassify(myChartClassifyId int, myChartClassifyName string) (err error) {
	sql := `UPDATE my_chart_classify SET my_chart_classify_name=?,modify_time=NOW() WHERE my_chart_classify_id=?`
	err = global.DmSQL["data"].Exec(sql, myChartClassifyName, myChartClassifyId).Error
	return
}

type MyChartClassifyDeleteReq struct {
	MyChartClassifyId int `description:"分类ID"`
}

func DeleteMyChartClassify(myChartClassifyId int) (err error) {
	sql := `DELETE FROM my_chart_classify WHERE my_chart_classify_id=?`
	err = global.DmSQL["data"].Exec(sql, myChartClassifyId).Error
	if err != nil {
		return err
	}
	mappingList := make([]*MyChartClassifyMapping, 0)
	sql = `SELECT * FROM my_chart_classify_mapping WHERE my_chart_classify_id=?`
	err = global.DmSQL["data"].Raw(sql, myChartClassifyId).Find(&mappingList).Error

	sql = `DELETE FROM my_chart_classify_mapping WHERE my_chart_classify_id=?`
	err = global.DmSQL["data"].Exec(sql, myChartClassifyId).Error
	if err != nil {
		return err
	}
	for _, v := range mappingList {
		var count int
		sql = ` SELECT COUNT(1) AS count FROM my_chart_classify_mapping WHERE my_chart_id=?`
		err = global.DmSQL["data"].Raw(sql, v.MyChartId).First(&count).Error
		if err != nil {
			return err
		}
		if count <= 0 {
			sql = `DELETE FROM my_chart WHERE my_chart_id=?`
			err = global.DmSQL["data"].Exec(sql, v.MyChartId).Error
			if err != nil {
				return err
			}
		}
	}
	return
}

type MyChartAddReq struct {
	ChartInfoId       int   `description:"图表id"`
	MyChartClassifyId []int `description:"分类id，数组形式"`
}

type MyChartAddResp struct {
	MyChartInfoId     int    `description:"图表id"`
	MyChartClassifyId string `description:"分类id,多个用英文逗号隔开"`
}

type MyChart struct {
	MyChartId   int       `orm:"column(my_chart_id);pk" gorm:"primaryKey" `
	ChartInfoId int       `description:"图表id"`
	Source      int       `description:"1：ETA图库；2：商品价格"`
	AdminId     int       `description:"用户id"`
	CreateTime  time.Time `description:"创建时间"`
	ModifyTime  time.Time `description:"修改时间"`
}

type MyChartView struct {
	MyChartId         int       `orm:"column(my_chart_id);pk" gorm:"primaryKey" `
	ChartInfoId       int       `description:"图表id"`
	AdminId           int       `description:"用户id"`
	CreateTime        time.Time `description:"创建时间"`
	ModifyTime        time.Time `description:"修改时间"`
	MyChartClassifyId string
}

func AddMyChart(item *MyChart) (lastId int64, err error) {
	err = global.DmSQL["data"].Create(item).Error
	return
}

type MyChartClassifyMapping struct {
	MyChartClassifyMappingId int `orm:"column(my_chart_classify_mapping_id);pk" gorm:"primaryKey" `
	AdminId                  int
	MyChartId                int
	MyChartClassifyId        int
	CreateTime               time.Time
	ModifyTime               time.Time
	Sort                     float64
}

func AddMyChartClassifyMapping(item *MyChartClassifyMapping) (lastId int64, err error) {
	err = global.DmSQL["data"].Create(item).Error
	return
}

func GetMyChartClassifyMapping(adminId, myChartId, myChartClassifyId int) (item *MyChartClassifyMapping, err error) {
	sql := `SELECT * FROM my_chart_classify_mapping WHERE admin_id=? AND my_chart_id=? AND my_chart_classify_id=? `
	err = global.DmSQL["data"].Raw(sql, adminId, myChartId, myChartClassifyId).First(&item).Error
	return
}

type MyChartDeleteReq struct {
	MyChartId         int `description:"我的图表id"`
	MyChartClassifyId int `description:"我的图表分类id"`
}

func DeleteMyChart(myChartId, myChartClassifyId int) (isDelMyChart bool, err error) {
	if myChartClassifyId > 0 {
		sql := `DELETE FROM my_chart_classify_mapping WHERE my_chart_id=? AND my_chart_classify_id=? `
		err = global.DmSQL["data"].Exec(sql, myChartId, myChartClassifyId).Error
		if err != nil {
			return
		}
		var count int
		sql = ` SELECT COUNT(1) AS count FROM my_chart_classify_mapping WHERE my_chart_id=? `
		err = global.DmSQL["data"].Raw(sql, myChartId).Scan(&count).Error
		if err != nil {
			return
		}
		if count <= 0 {
			sql = `DELETE FROM my_chart WHERE my_chart_id=? `
			err = global.DmSQL["data"].Exec(sql, myChartId).Error
			if err != nil {
				return
			}
			isDelMyChart = true
		}
	} else {
		sql := `DELETE FROM my_chart WHERE my_chart_id=? `
		err = global.DmSQL["data"].Exec(sql, myChartId).Error
		if err != nil {
			return
		}
		isDelMyChart = true
	}
	return
}

type MyChartList struct {
	AdminId             int `description:"关联人id"`
	MyChartId           int `description:"我的图表主键"`
	ChartInfoId         int
	ChartName           string `description:"来源名称"`
	ChartNameEn         string `description:"英文图表名称"`
	ChartClassifyId     int    `description:"图表分类id"`
	SysUserId           int
	SysUserRealName     string
	UniqueCode          string `description:"图表唯一编码"`
	CreateTime          time.Time
	ModifyTime          time.Time
	DateType            int    `description:"日期类型:1:00年至今,2:10年至今,3:15年至今，4：年初至今，5:自定义时间"`
	StartDate           string `description:"自定义开始日期"`
	EndDate             string `description:"自定义结束日期"`
	IsSetName           int    `description:"设置名称"`
	EdbInfoIds          string `description:"指标id"`
	ChartType           int    `description:"生成样式:1:曲线图,2:季节性图"`
	Calendar            string `description:"公历/农历"`
	SeasonStartDate     string `description:"季节性图开始日期"`
	SeasonEndDate       string `description:"季节性图开始日期"`
	ChartImage          string `description:"图表图片"`
	Sort                int    `description:"排序字段，数字越小越排前面"`
	IsAdd               bool   `description:"true:已加入我的图库,false:未加入我的图库"`
	MyChartClassifyName string `description:"分类名称，多个用英文逗号分割"`
	MyChartClassifyId   string `description:"分类id，多个用英文逗号分割"`
	EdbEndDate          string `description:"指标最新更新日期"`
	IsEnChart           bool   `description:"是否展示英文标识"`
	Disabled            int    `description:"是否禁用，0：启用，1：禁用，默认：0"`
	Source              int    `description:"1：ETA图库；2：商品价格曲线"`
	IsJoinPermission    int    `description:"是否加入权限管控，0：不加入；1：加入；默认：0"`
	HaveOperaAuth       bool   `description:"是否有数据权限，默认：false"`
}

type MyChartListResp struct {
	Paging *paging.PagingItem
	List   []*MyChartList
}

func GetMyChartListByCondition(condition string, pars []interface{}, startSize, pageSize int) (item []*MyChartList, err error) {
	field := `a.my_chart_id,c.admin_id,c.sort,c.create_time as c_ctime,b.chart_info_id,b.chart_name,b.chart_name_en,b.chart_classify_id,b.sys_user_id,b.sys_user_real_name,b.unique_code,b.create_time,b.modify_time,b.date_type,b.start_date,b.end_date,b.is_set_name,b.edb_info_ids,b.chart_type,b.calendar,b.season_start_date,b.season_end_date,b.chart_image,b.edb_end_date,b.disabled,b.source,b.is_join_permission `
	sql := ` SELECT * FROM ( SELECT ` + field + ` FROM my_chart AS a
			INNER JOIN chart_info AS b ON a.chart_info_id=b.chart_info_id
			INNER JOIN my_chart_classify_mapping AS c ON a.my_chart_id=c.my_chart_id
			INNER JOIN my_chart_classify AS d ON c.my_chart_classify_id=d.my_chart_classify_id
			WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	sql += " GROUP BY a.chart_info_id) AS f ORDER BY sort ASC,c_ctime desc LIMIT ?,? "

	pars = append(pars, startSize)
	pars = append(pars, pageSize)
	err = global.DmSQL["data"].Raw(sql, pars...).Find(&item).Error
	return
}

func GetMyChartListCountByCondition(condition string, pars []interface{}) (count int, err error) {
	sql := ` select a.* from my_chart AS a
			INNER JOIN chart_info AS b ON a.chart_info_id=b.chart_info_id
			INNER JOIN my_chart_classify_mapping AS c ON a.my_chart_id=c.my_chart_id
			INNER JOIN my_chart_classify AS d ON c.my_chart_classify_id=d.my_chart_classify_id
			WHERE 1=1`
	if condition != "" {
		sql += condition
	}
	sql1 := ` SELECT COUNT(1) AS count FROM (` + sql + ` GROUP BY a.chart_info_id ) d`
	err = global.DmSQL["data"].Raw(sql1, pars...).Scan(&count).Error
	return
}

func GetMyChartMaxSort(adminId, myChartClassifyId int) (count int, err error) {
	sql := ` SELECT COUNT(1) AS count FROM my_chart_classify_mapping WHERE 1=1 AND admin_id=? AND my_chart_classify_id=? `
	err = global.DmSQL["data"].Raw(sql, adminId, myChartClassifyId).Scan(&count).Error
	return
}

type MoveMyChartReq struct {
	MyChartId         int `description:"移动的，我的图表图表ID"`
	PrevMyChartId     int `description:"移动到的位置，上一级的，我的图表ID,如果没有上一级，则传0"`
	NextMyChartId     int `description:"移动到的位置，下一级的，我的图表ID,如果没有下一级，则传0"`
	MyChartClassifyId int `description:"当前分类id"`
}

func MyChartMove(sort float64, adminId int, myChartId, myChartClassifyId int) (err error) {
	sql := ` UPDATE my_chart_classify_mapping SET sort = ?,modify_time=NOW() WHERE admin_id=? AND my_chart_id=? AND my_chart_classify_id=? `
	err = global.DmSQL["data"].Exec(sql, sort, adminId, myChartId, myChartClassifyId).Error
	return
}

func UpdateMyChartSortByClassifyId(myChartClassifyId, nowSort int, prevMyChartClassifyMappingId int, updateSort string) (err error) {
	sql := ` update my_chart_classify_mapping set sort = ` + updateSort + ` WHERE my_chart_classify_id=? AND `
	if prevMyChartClassifyMappingId > 0 {
		sql += ` ( sort > ? or ( my_chart_classify_mapping_id < ? and sort=? )) `
	}
	err = global.DmSQL["data"].Exec(sql, myChartClassifyId, nowSort, prevMyChartClassifyMappingId, nowSort).Error
	return
}

type ChartRecommendListResp struct {
	List []*ChartInfoView
}

func GetChartClassify(chartClassifyId int) (chart_classify_id string, err error) {
	sql := `SELECT LISTAGG(t.chart_classify_id, ',') WITHIN GROUP (ORDER BY t.chart_classify_id) AS chart_classify_id FROM (
				SELECT a.chart_classify_id FROM chart_classify AS a
				WHERE a.chart_classify_id=?
				UNION ALL
				SELECT a.chart_classify_id FROM chart_classify AS a
				WHERE a.parent_id=? UNION ALL
		SELECT
			chart_classify_id
		FROM
			chart_classify
	WHERE
		parent_id IN ( SELECT chart_classify_id FROM chart_classify WHERE parent_id = ? )
				)AS t`
	err = global.DmSQL["data"].Raw(sql, chartClassifyId, chartClassifyId, chartClassifyId).First(&chart_classify_id).Error
	return
}

func GetRecommendChartListByCondition(condition string, pars []interface{}, startSize, pageSize int) (item []*ChartInfoView, err error) {
	sql := ` SELECT b.* FROM my_chart AS a
			INNER JOIN chart_info AS b ON a.chart_info_id=b.chart_info_id `
	if condition != "" {
		sql += condition
	}
	sql += " ORDER BY a.create_time DESC LIMIT ?,? "

	pars = append(pars, startSize)
	pars = append(pars, pageSize)
	err = global.DmSQL["data"].Raw(sql, pars...).Find(&item).Error
	return
}

func GetMyChartListByAdminId(adminId int) (item []*MyChartView, err error) {
	sql := ` SELECT 
           a.my_chart_id,a.chart_info_id,a.source,a.admin_id,a.create_time,a.modify_time,LISTAGG(c.my_chart_classify_id, ',') WITHIN GROUP (ORDER BY c.my_chart_classify_id) AS my_chart_classify_id
            FROM my_chart AS a
			LEFT JOIN  my_chart_classify_mapping AS b ON a.my_chart_id=b.my_chart_id AND a.admin_id=b.admin_id
			LEFT JOIN my_chart_classify AS c ON b.my_chart_classify_id=c.my_chart_classify_id AND b.admin_id=c.admin_id
			WHERE 1=1 AND a.admin_id=?
			GROUP BY   
			a.chart_info_id,
			a.my_chart_id,
			a.chart_info_id,
			a.source,
			a.admin_id,
			a.create_time,
			a.modify_time `
	err = global.DmSQL["data"].Raw(sql, adminId).Find(&item).Error
	return
}

func GetMyChartSearchByCondition(condition string, pars []interface{}) (item []*MyChartList, err error) {
	sql := ` SELECT a.my_chart_id,c.sort,b.*,LISTAGG(DISTINCT d.my_chart_classify_name) AS my_chart_classify_name,LISTAGG(DISTINCT d.my_chart_classify_id) AS my_chart_classify_id
			FROM  my_chart AS a
			INNER JOIN chart_info AS b ON a.chart_info_id=b.chart_info_id
			INNER JOIN my_chart_classify_mapping AS c ON a.my_chart_id=c.my_chart_id
			INNER JOIN my_chart_classify AS d ON c.my_chart_classify_id=d.my_chart_classify_id
			WHERE 1=1 `

	if condition != "" {
		sql += condition
	}
	sql += " GROUP BY a.chart_info_id ORDER BY c.sort DESC "
	err = global.DmSQL["data"].Raw(sql, pars...).Find(&item).Error
	return
}

type MyChartSearchListResp struct {
	List   []*MyChartList
	Paging *paging.PagingItem
}

type MyChartEditReq struct {
	MyChartId                int   `description:"我的图表主键"`
	CurrentMyChartClassifyId int   `description:"当前分类ID"`
	MyChartClassifyId        []int `description:"分类id，数组形式"`
}

func GetMyChartClassifyMappingCount(adminId, myChartId, myChartClassifyId int) (count int, err error) {
	sql := ` SELECT COUNT(1) AS count
			FROM  my_chart_classify_mapping
			WHERE 1=1 AND admin_id=? AND my_chart_id=? AND my_chart_classify_id=? `
	err = global.DmSQL["data"].Raw(sql, adminId, myChartId, myChartClassifyId).Scan(&count).Error
	return
}

func DeleteMyChartClassifyMapping(adminId, myChartId, myChartClassifyId int) (err error) {
	sql := ` DELETE FROM my_chart_classify_mapping
			WHERE admin_id=? AND my_chart_id=? AND my_chart_classify_id=? `
	err = global.DmSQL["data"].Exec(sql, adminId, myChartId, myChartClassifyId).Error
	return
}

func GetMyChartSort(adminId, myChartClassifyId, sort int) (item *MyChartClassifyMapping, err error) {
	sql := ` SELECT * FROM my_chart_classify_mapping WHERE 1=1 AND admin_id=? AND my_chart_classify_id=? `
	if sort == 1 {
		sql += ` ORDER BY sort DESC,my_chart_classify_mapping_id ASC LIMIT 1 `
	} else {
		sql += ` ORDER BY sort ASC,my_chart_classify_mapping_id DESC LIMIT 1 `
	}
	err = global.DmSQL["data"].Raw(sql, adminId, myChartClassifyId).First(&item).Error
	return
}

func GetMyChartByCondition(condition string, pars []interface{}) (item []*MyChartView, err error) {
	sql := `SELECT a.my_chart_id,a.chart_info_id,a.source,a.admin_id,a.create_time,a.modify_time,LISTAGG(c.my_chart_classify_id, ',') WITHIN GROUP (ORDER BY c.my_chart_classify_id) AS my_chart_classify_id
            FROM my_chart AS a
			LEFT JOIN  my_chart_classify_mapping AS b ON a.my_chart_id=b.my_chart_id AND a.admin_id=b.admin_id
			LEFT JOIN my_chart_classify AS c ON b.my_chart_classify_id=c.my_chart_classify_id AND b.admin_id=c.admin_id
			WHERE 1=1 
			`
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY   
    a.chart_info_id,
    a.my_chart_id,
    a.chart_info_id,
    a.source,
    a.admin_id,
    a.create_time,
    a.modify_time  `
	err = global.DmSQL["data"].Raw(sql, pars...).Find(&item).Error
	return
}

func GetMyChartDetailByCondition(condition string, pars []interface{}) (item *MyChartView, err error) {
	sql := ` SELECT * FROM my_chart WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	err = global.DmSQL["data"].Raw(sql, pars...).First(&item).Error
	return
}

func MoveMyChartClassify(adminId, myChartId int, myChartClassifyId []int) (err error) {
	to := global.DmSQL["data"].Begin()

	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	classifyIdMap := make(map[int]int)
	for _, v := range myChartClassifyId {
		classifyIdMap[v] = v
	}

	mappingList := make([]*MyChartClassifyMapping, 0)
	sql := `SELECT * FROM my_chart_classify_mapping WHERE admin_id=? AND my_chart_id=?`
	err = to.Raw(sql, adminId, myChartId).Find(&mappingList).Error
	if err != nil {
		return
	}

	for _, v := range mappingList {
		if _, ok := classifyIdMap[v.MyChartClassifyId]; !ok {
			sql := ` DELETE FROM my_chart_classify_mapping WHERE my_chart_classify_mapping_id=? `
			err = to.Exec(sql, v.MyChartClassifyMappingId).Error
			if err != nil {
				return
			}
		}
	}
	for _, v := range myChartClassifyId {
		var count int
		sql := ` SELECT COUNT(1) AS count FROM my_chart_classify_mapping WHERE my_chart_id=? AND my_chart_classify_id=? `
		err = to.Raw(sql, myChartId, v).Scan(&count).Error
		if err != nil {
			return
		}
		if count <= 0 {
			if err != nil {
				return err
			}
			sort := 0
			sql = ` INSERT INTO my_chart_classify_mapping (
				  admin_id,
				  my_chart_id,
				  my_chart_classify_id,
				  create_time,
				  modify_time,
				  sort )VALUES (?,?,?,NOW(),NOW(),?) `
			err = to.Exec(sql, adminId, myChartId, v, sort).Error
			if err != nil {
				return err
			}
		}
	}

	return
}

func (myChartClassify *MyChartClassify) Update(cols []string) (err error) {
	err = global.DmSQL["data"].Select(cols).Updates(myChartClassify).Error
	return
}

func GetFirstMyChartClassifyByAdminId(adminId int) (item *MyChartClassify, err error) {
	sql := ` SELECT * FROM my_chart_classify WHERE admin_id=? order by sort asc,my_chart_classify_id asc limit 1`
	err = global.DmSQL["data"].Raw(sql, adminId).First(&item).Error
	return
}

func UpdateMyChartClassifySortByClassifyId(adminId, classifyId, nowSort int, updateSort string) (err error) {
	sql := ` update my_chart_classify set sort = ` + updateSort + ` WHERE admin_id = ? and sort > ? `
	if classifyId > 0 {
		sql += ` or ( my_chart_classify_id > ` + fmt.Sprint(classifyId) + ` and sort = ` + fmt.Sprint(nowSort) + `)`
	}
	err = global.DmSQL["data"].Exec(sql, adminId, nowSort).Error
	return
}

type ModifyChartClassifyPublicReq struct {
	MyChartClassifyId int `description:"分类id"`
	IsPublic          int `description:"是否所有人可见，0：仅自己可见，1：所有人可见"`
}

type CopyChartClassifyReq struct {
	MyChartClassifyId int `description:"分类id"`
}

func GetPublicMyChartClassifyById(myChartClassifyId int) (item *MyChartClassify, err error) {
	sql := ` SELECT * FROM my_chart_classify WHERE is_public=1 AND my_chart_classify_id=? `
	err = global.DmSQL["data"].Raw(sql, myChartClassifyId).First(&item).Error
	return
}

func CopyPublicMyChartClassifyById(myChartClassifyId, adminId int, newMyChartClassifyName string) (newMyChartClassify *MyChartClassify, err error) {
	to := global.DmSQL["data"].Begin()

	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	fromMappingList := make([]*MyChartClassifyMapping, 0)
	sql := `SELECT * FROM my_chart_classify_mapping WHERE my_chart_classify_id=?`
	err = to.Raw(sql, myChartClassifyId).Find(&fromMappingList).Error
	if err != nil {
		return
	}

	fromMyChartSortMap := make(map[int]float64)
	for _, fromMapping := range fromMappingList {
		fromMyChartSortMap[fromMapping.MyChartId] = fromMapping.Sort
	}

	var minSort int
	sql = ` SELECT COALESCE(MIN(sort), 0) as sort FROM my_chart_classify WHERE admin_id=? `
	err = to.Raw(sql, adminId).Scan(&minSort).Error
	if err != nil {
		return
	}

	fromMyChartList := make([]*MyChart, 0)
	sql = `SELECT a.* FROM my_chart a join my_chart_classify_mapping b on a.my_chart_id=b.my_chart_id
WHERE b.my_chart_classify_id=?`
	err = to.Raw(sql, myChartClassifyId).Find(&fromMyChartList).Error
	if err != nil {
		return
	}

	newMyChartClassify = &MyChartClassify{
		MyChartClassifyName: newMyChartClassifyName,
		AdminId:             adminId,
		CreateTime:          time.Now(),
		ModifyTime:          time.Now(),
		Sort:                minSort - 1,
	}
	err = to.Create(newMyChartClassify).Error
	newMappingList := make([]*MyChartClassifyMapping, 0)

	for _, v := range fromMyChartList {
		tmpMyChart := &MyChart{
			ChartInfoId: v.ChartInfoId,
			AdminId:     adminId,
			Source:      v.Source,
			CreateTime:  time.Now(),
			ModifyTime:  time.Now(),
		}
		tmpErr := to.Create(tmpMyChart)
		if tmpErr != nil {
			err = tmpErr.Error
		}

		tmpSort, ok := fromMyChartSortMap[v.MyChartId]
		if !ok {
			tmpSort = 0
		}
		tmp := &MyChartClassifyMapping{
			AdminId:   adminId,
			MyChartId: tmpMyChart.MyChartId,
			MyChartClassifyId: newMyChartClassify.MyChartClassifyId,
			CreateTime:        time.Now(),
			ModifyTime:        time.Now(),
			Sort:              tmpSort,
		}
		newMappingList = append(newMappingList, tmp)
	}

	if len(newMappingList) > 0 {
		err = to.CreateInBatches(newMappingList, utils.MultiAddNum).Error
	}
	return
}

type ModifyChartClassifyCompanyPublicReq struct {
	MyChartClassifyId int `description:"分类id"`
	IsCompanyPublic   int `description:"是否用户可见 0-否 1-是"`
}

func GetCompanyPublicClassifyList(condition string, pars []interface{}) (item []*MyChartClassify, err error) {
	sql := ` SELECT * FROM my_chart_classify WHERE 1 = 1 AND is_public = 1 AND is_company_public = 1 `
	sql += condition
	sql += " ORDER BY public_time asc"
	err = global.DmSQL["data"].Raw(sql, pars...).Find(&item).Error
	return
}

func GetMyChartClassifyList(myChartClassifyId int) (mappingList []*MyChartClassifyMapping, err error) {
	sql := `SELECT * FROM my_chart_classify_mapping WHERE my_chart_id=?`
	err = global.DmSQL["data"].Raw(sql, myChartClassifyId).Find(&mappingList).Error
	return
}

func AddMyChartToMyChartClassify(adminId, myChartId int, myChartClassifyId []int) (err error) {
	classifyIdMap := make(map[int]int)
	for _, v := range myChartClassifyId {
		classifyIdMap[v] = v
	}

	mappingList := make([]*MyChartClassifyMapping, 0)
	sql := `SELECT * FROM my_chart_classify_mapping WHERE admin_id=? AND my_chart_id=?`
	err = global.DmSQL["data"].Raw(sql, adminId, myChartId).Find(&mappingList).Error
	if err != nil {
		return
	}

	for _, v := range mappingList {
		if _, ok := classifyIdMap[v.MyChartClassifyId]; ok {
			delete(classifyIdMap, v.MyChartClassifyId)
		}
	}
	step := float64(65536)
	for _, v := range myChartClassifyId {
		var count int
		sql := ` SELECT COUNT(1) AS count FROM my_chart_classify_mapping WHERE my_chart_id=? AND my_chart_classify_id=? `
		err = global.DmSQL["data"].Raw(sql, myChartId, v).Scan(&count).Error
		if err != nil {
			return
		}
		if count <= 0 {
			total, err := GetMyChartMaxSort(adminId, v)
			if err != nil {
				return err
			}
			sort := (float64(total) + 1) * step
			sql = ` INSERT INTO my_chart_classify_mapping (
				  admin_id,
				  my_chart_id,
				  my_chart_classify_id,
				  create_time,
				  modify_time,
				  sort )VALUES (?,?,?,NOW(),NOW(),?) `
			err = global.DmSQL["data"].Exec(sql, adminId, myChartId, v, sort).Error
			if err != nil {
				return err
			}
		}
	}

	return
}

func GetMyChartListGroupByCharyInfoIdAndAdminIdByCondition(condition string, pars []interface{}) (item []*MyChartList, err error) {
	sql := `SELECT a.admin_id,a.my_chart_id,c.sort,b.chart_info_id, b.chart_name, b.unique_code, b.chart_classify_id, b.sys_user_id, b.sys_user_real_name, b.create_time, b.modify_time, b.date_type, b.start_date, b.end_date, b.is_set_name, b.edb_info_ids, b.chart_type, b.calendar, b.season_start_date, b.season_end_date, b.chart_image, b.sort, b.edb_end_date, b.x_min, b.x_max, b.left_min, b.left_max, b.right_min, b.right_max, b.right2_min, b.right2_max, b.min_max_save, b.chart_name_en, b.disabled, b.source,  b.start_year, b.chart_theme_id,  b.unit, b.unit_en, b.is_join_permission, b.forum_chart_info_id, b.chart_alias, b.date_type_num,LISTAGG(DISTINCT d.my_chart_classify_name, ',') WITHIN GROUP (ORDER BY d.my_chart_classify_name) AS my_chart_classify_name,LISTAGG(d.my_chart_classify_id, ',') WITHIN GROUP (ORDER BY d.my_chart_classify_id) AS my_chart_classify_id
FROM  my_chart AS a
INNER JOIN chart_info AS b ON a.chart_info_id=b.chart_info_id
INNER JOIN my_chart_classify_mapping AS c ON a.my_chart_id=c.my_chart_id
INNER JOIN my_chart_classify AS d ON c.my_chart_classify_id=d.my_chart_classify_id
WHERE 1=1  `
	if condition != "" {
		sql += condition
	}
	sql += " GROUP BY a.chart_info_id,a.admin_id,c.sort,a.my_chart_id,b.chart_info_id, b.chart_name, b.unique_code, b.chart_classify_id, b.sys_user_id, b.sys_user_real_name, b.create_time, b.modify_time, b.date_type, b.start_date, b.end_date, b.is_set_name, b.edb_info_ids, b.chart_type, b.calendar, b.season_start_date, b.season_end_date, b.chart_image, b.sort, b.edb_end_date, b.x_min, b.x_max, b.left_min, b.left_max, b.right_min, b.right_max, b.right2_min, b.right2_max, b.min_max_save, b.chart_name_en, b.disabled,  b.source,  b.start_year, b.chart_theme_id,  b.unit, b.unit_en, b.is_join_permission, b.forum_chart_info_id, b.chart_alias, b.date_type_num" +
		" ORDER BY c.sort DESC "
	err = global.DmSQL["data"].Raw(sql, pars...).Find(&item).Error
	return
}

func GetRelationChartListByCondition(condition string, pars []interface{}, startSize, pageSize int) (item []*ChartInfoView, err error) {
	sql := ` SELECT a.* FROM chart_info AS a
	WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	sql += " ORDER BY a.create_time DESC LIMIT ?,? "

	pars = append(pars, startSize)
	pars = append(pars, pageSize)
	err = global.DmSQL["data"].Raw(sql, pars...).Find(&item).Error
	return
}

func GetRelationChartListCountByCondition(condition string, pars []interface{}) (count int, err error) {
	sql := ` SELECT COUNT(1) AS count FROM chart_info AS a
	WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	err = global.DmSQL["data"].Raw(sql, pars...).Scan(&count).Error
	return
}

func GetChartInfoByIdList(chartInfoIdList []int) (items []*ChartInfo, err error) {
	num := len(chartInfoIdList)
	if num <= 0 {
		return
	}
	sql := ` SELECT * FROM chart_info WHERE chart_info_id in (` + utils.GetOrmInReplace(num) + `) `
	err = global.DmSQL["data"].Raw(sql, chartInfoIdList).Find(&items).Error
	return
}

func GetChartInfoViewByIdList(chartInfoIdList []int) (items []*ChartInfoView, err error) {
	num := len(chartInfoIdList)
	if num <= 0 {
		return
	}
	sql := ` SELECT * FROM chart_info WHERE chart_info_id in (` + utils.GetOrmInReplace(num) + `) `
	err = global.DmSQL["data"].Raw(sql, chartInfoIdList).Find(&items).Error
	return
}

func GetMyChartClassifyByClassifyId(classifyId int) (item *MyChartClassify, err error) {
	sql := ` SELECT * FROM my_chart_classify WHERE my_chart_classify_id = ? `
	err = global.DmSQL["data"].Raw(sql, classifyId).First(&item).Error
	return
}

type MyChartClassifyItem struct {
	MyChartClassifyId   int    `description:"分类ID"`
	MyChartClassifyName string `description:"分类名称"`
	AdminId             int    `description:"创建人id"`
	IsPublic            int    `description:"是否公共分类"`
	IsCompanyPublic     int    `description:"是否为用户公共分类"`
	ChartNum            int    `description:"分类下的图表数量"`
}

func FormatMyChartClassify2Item(origin *MyChartClassify, chartNum int) (item *MyChartClassifyItem) {
	if origin == nil {
		return
	}
	item = new(MyChartClassifyItem)
	item.MyChartClassifyId = origin.MyChartClassifyId
	item.MyChartClassifyName = origin.MyChartClassifyName
	item.AdminId = origin.AdminId
	item.IsPublic = origin.IsPublic
	item.IsCompanyPublic = origin.IsCompanyPublic
	item.ChartNum = chartNum
	return
}

type MyChartClassifyIdAndNum struct {
	MyChartClassifyId int `description:"分类ID"`
	ChartNum          int `description:"分类下的图表数量"`
}

func GetMyChartClassifyIdAndNum(cond string, pars []interface{}) (items []*MyChartClassifyIdAndNum, err error) {
	sql := fmt.Sprintf(`SELECT
			a.my_chart_classify_id,
			COUNT(1) AS chart_num
		FROM
			my_chart_classify AS a
		INNER JOIN my_chart_classify_mapping AS b ON a.my_chart_classify_id = b.my_chart_classify_id
		INNER JOIN my_chart AS c ON b.my_chart_id = c.my_chart_id
		INNER JOIN chart_info AS d ON c.chart_info_id = d.chart_info_id
		WHERE
			1 = 1 %s
		GROUP BY
			a.my_chart_classify_id`, cond)
	err = global.DmSQL["data"].Raw(sql, pars...).Find(&items).Error
	return
}