eta_chart_lib/models/data_manage/my_chart.go

package data_manage

import (
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"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) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM chart_info WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	//sql += " ORDER BY sort ASC,chart_info_id DESC LIMIT ?,? "
	sql += " ORDER BY create_time DESC LIMIT ?,? "
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&item)
	return
}

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

type MyChartClassify struct {
	MyChartClassifyId   int `orm:"column(my_chart_classify_id);pk"`
	MyChartClassifyName string
	Sort                int       `json:"-" description:"排序字段，值越小，排的越靠前"`
	AdminId             int       `json:"-"`
	CreateTime          time.Time `json:"-"`
	ModifyTime          time.Time `json:"-"`
}

func AddMyChartClassify(item *MyChartClassify) (err error) {
	o := orm.NewOrmUsingDB("data")
	_, err = o.Insert(item)
	return
}

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

type MyChartClassifyResp struct {
	List []*MyChartClassify
}

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

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

func GetMyChartClassifyById(adminId, myChartClassifyId int) (item *MyChartClassify, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM my_chart_classify WHERE admin_id=? AND my_chart_classify_id=? `
	err = o.Raw(sql, adminId, myChartClassifyId).QueryRow(&item)
	return
}

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

// MyChartClassifyMoveReq 移动分类请求参数
type MyChartClassifyMoveReq struct {
	MyChartClassifyId int `description:"分类id"`
	PrevClassifyId    int `description:"上一个兄弟节点分类id"`
	NextClassifyId    int `description:"下一个兄弟节点分类id"`
}

func ModifyMyChartClassify(myChartClassifyId int, myChartClassifyName string) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `UPDATE my_chart_classify SET my_chart_classify_name=?,modify_time=NOW() WHERE my_chart_classify_id=?`
	_, err = o.Raw(sql, myChartClassifyName, myChartClassifyId).Exec()
	return
}

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

func DeleteMyChartClassify(myChartClassifyId int) (err error) {
	o := orm.NewOrmUsingDB("data")
	//o.Begin()
	//
	//defer func() {
	//	if err != nil {
	//		o.Rollback()
	//	} else {
	//		o.Commit()
	//	}
	//}()

	sql := `DELETE FROM my_chart_classify WHERE my_chart_classify_id=?`
	_, err = o.Raw(sql, myChartClassifyId).Exec()
	if err != nil {
		return err
	}
	mappingList := make([]*MyChartClassifyMapping, 0)
	sql = `SELECT * FROM my_chart_classify_mapping WHERE my_chart_classify_id=?`
	_, err = o.Raw(sql, myChartClassifyId).QueryRows(&mappingList)

	sql = `DELETE FROM my_chart_classify_mapping WHERE my_chart_classify_id=?`
	_, err = o.Raw(sql, myChartClassifyId).Exec()
	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 = o.Raw(sql, v.MyChartId).QueryRow(&count)
		if err != nil {
			return err
		}
		if count <= 0 {
			sql = `DELETE FROM my_chart WHERE my_chart_id=?`
			_, err = o.Raw(sql, v.MyChartId).Exec()
			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"`
	ChartInfoId int       `description:"图表id"`
	AdminId     int       `description:"用户id"`
	CreateTime  time.Time `description:"创建时间"`
	ModifyTime  time.Time `description:"修改时间"`
}

type MyChartView struct {
	MyChartId         int       `orm:"column(my_chart_id);pk"`
	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) {
	o := orm.NewOrmUsingDB("data")
	lastId, err = o.Insert(item)
	return
}

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

func AddMyChartClassifyMapping(item *MyChartClassifyMapping) (lastId int64, err error) {
	o := orm.NewOrmUsingDB("data")
	lastId, err = o.Insert(item)
	return
}

func GetMyChartClassifyMapping(adminId, myChartId, myChartClassifyId int) (item *MyChartClassifyMapping, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT * FROM my_chart_classify_mapping WHERE admin_id=? AND my_chart_id=? AND my_chart_classify_id=? `
	err = o.Raw(sql, adminId, myChartId, myChartClassifyId).QueryRow(&item)
	return
}

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

func DeleteMyChart(myChartId, myChartClassifyId int) (err error) {
	o := orm.NewOrmUsingDB("data")
	if myChartClassifyId > 0 {
		sql := `DELETE FROM my_chart_classify_mapping WHERE my_chart_id=? AND my_chart_classify_id=? `
		_, err = o.Raw(sql, myChartId, myChartClassifyId).Exec()
		if err != nil {
			return
		}
		var count int
		sql = ` SELECT COUNT(1) AS count FROM my_chart_classify_mapping WHERE my_chart_id=? `
		err = o.Raw(sql, myChartId).QueryRow(&count)
		if err != nil {
			return
		}
		if count <= 0 {
			sql = `DELETE FROM my_chart WHERE my_chart_id=? `
			_, err = o.Raw(sql, myChartId).Exec()
			if err != nil {
				return
			}
		}
	} else {
		sql := `DELETE FROM my_chart WHERE my_chart_id=? `
		_, err = o.Raw(sql, myChartId).Exec()
		if err != nil {
			return
		}
	}
	return
}

type MyChartList struct {
	MyChartId           int `description:"我的图表主键"`
	ChartInfoId         int
	ChartName           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:"指标最新更新日期"`
}

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

func GetMyChartListByCondition(condition string, pars []interface{}, startSize, pageSize int) (item []*MyChartList, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT a.my_chart_id,c.sort,b.* 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
			WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	sql += " GROUP BY a.chart_info_id ORDER BY c.sort ASC LIMIT ?,? "
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&item)
	return
}

func GetMyChartListCountByCondition(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT COUNT(1) AS count 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
			WHERE 1=1`
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

func GetMyChartMaxSort(adminId, myChartClassifyId int) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT COUNT(1) AS count FROM my_chart_classify_mapping WHERE 1=1 AND admin_id=? AND my_chart_classify_id=? `
	err = o.Raw(sql, adminId, myChartClassifyId).QueryRow(&count)
	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) {
	o := orm.NewOrmUsingDB("data")
	sql := ` UPDATE my_chart_classify_mapping SET sort = ?,modify_time=NOW() WHERE admin_id=? AND my_chart_id=? AND my_chart_classify_id=? `
	_, err = o.Raw(sql, sort, adminId, myChartId, myChartClassifyId).Exec()
	return
}

func GetMyChartMinSortByAdminId(adminId int) (item *MyChart, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM my_chart WHERE admin_id=? ORDER BY sort ASC LIMIT 1`
	err = o.Raw(sql, adminId).QueryRow(&item)
	return
}

type ChartRecommendListResp struct {
	List []*ChartInfoView
}

func GetChartClassify(chartClassifyId int) (chart_classify_id string, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT GROUP_CONCAT(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=?
			)AS t`
	err = o.Raw(sql, chartClassifyId, chartClassifyId).QueryRow(&chart_classify_id)
	return
}

func GetRecommendChartListByCondition(condition string, pars []interface{}, startSize, pageSize int) (item []*ChartInfoView, err error) {
	o := orm.NewOrmUsingDB("data")
	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 ?,? "
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&item)
	return
}

func GetMyChartListByAdminId(adminId int) (item []*MyChartView, err error) {
	o := orm.NewOrmUsingDB("data")
	//sql := ` SELECT * FROM my_chart WHERE 1=1 AND admin_id=? `

	sql := ` SELECT a.*,GROUP_CONCAT(c.my_chart_classify_id SEPARATOR ',') 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 `
	_, err = o.Raw(sql, adminId).QueryRows(&item)
	return
}

func GetMyChartSearchByCondition(condition string, pars []interface{}) (item []*MyChartList, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT a.my_chart_id,c.sort,b.*,GROUP_CONCAT(DISTINCT d.my_chart_classify_name) AS my_chart_classify_name,GROUP_CONCAT(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 = o.Raw(sql, pars).QueryRows(&item)
	return
}

type MyChartSearchListResp struct {
	List []*MyChartList
}

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

func GetMyChartClassifyMappingCount(adminId, myChartId, myChartClassifyId int) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	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 = o.Raw(sql, adminId, myChartId, myChartClassifyId).QueryRow(&count)
	return
}

func DeleteMyChartClassifyMapping(adminId, myChartId, myChartClassifyId int) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` DELETE FROM my_chart_classify_mapping
			WHERE admin_id=? AND my_chart_id=? AND my_chart_classify_id=? `
	_, err = o.Raw(sql, adminId, myChartId, myChartClassifyId).Exec()
	return
}

func GetMyChartSort(adminId, myChartClassifyId, sort int) (item *MyChartClassifyMapping, err error) {
	o := orm.NewOrmUsingDB("data")
	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 LIMIT 1 `
	} else {
		sql += ` ORDER BY sort ASC LIMIT 1 `
	}
	err = o.Raw(sql, adminId, myChartClassifyId).QueryRow(&item)
	return
}

func GetMyChartByCondition(condition string, pars []interface{}) (item []*MyChartView, err error) {
	o := orm.NewOrmUsingDB("data")
	//sql := ` SELECT * FROM my_chart WHERE 1=1 `
	sql := `SELECT a.*,GROUP_CONCAT(c.my_chart_classify_id SEPARATOR ',') 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  `
	_, err = o.Raw(sql, pars).QueryRows(&item)
	return
}

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

func MoveMyChartClassify(adminId, myChartId int, myChartClassifyId []int) (err error) {
	o := orm.NewOrmUsingDB("data")
	o.Begin()

	defer func() {
		if err != nil {
			o.Rollback()
		} else {
			o.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 = o.Raw(sql, adminId, myChartId).QueryRows(&mappingList)
	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 = o.Raw(sql, v.MyChartClassifyMappingId).Exec()
			if err != nil {
				return
			}
		}
	}
	//sql := `DELETE FROM my_chart_classify_mapping WHERE admin_id=? AND my_chart_id=? `
	//_, err = o.Raw(sql, adminId, myChartId).Exec()
	//if err != nil {
	//	return err
	//}
	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 = o.Raw(sql, myChartId, v).QueryRow(&count)
		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 = o.Raw(sql, adminId, myChartId, v, sort).Exec()
			if err != nil {
				return err
			}
		}
	}

	return
}

// Update 更新分类基础信息
func (myChartClassify *MyChartClassify) Update(cols []string) (err error) {
	o := orm.NewOrmUsingDB("data")
	_, err = o.Update(myChartClassify, cols...)
	return
}

// GetFirstMyChartClassifyByAdminId 获取当前账号下，排序第一条的分类数据
func GetFirstMyChartClassifyByAdminId(adminId int) (item *MyChartClassify, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM my_chart_classify WHERE admin_id=? order by sort asc,my_chart_classify_id asc limit 1`
	err = o.Raw(sql, adminId).QueryRow(&item)
	return
}

// UpdateMyChartClassifySortByClassifyId 根据分类id更新排序
func UpdateMyChartClassifySortByClassifyId(adminId, classifyId, nowSort int, updateSort string) (err error) {
	o := orm.NewOrmUsingDB("data")
	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 = o.Raw(sql, adminId, nowSort).Exec()
	return
}