eta_mobile/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       `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) {
	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, create_time desc "
	_, err = o.Raw(sql, adminId).QueryRows(&item)
	return
}

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

type MyChartClassifyResp struct {
	List []*MyChartClassify
}

// PublicChartClassifyResp 公共分类返回数据结构体
type PublicChartClassifyResp struct {
	List []PublicChartClassifyItem
}

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

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
}

// GetMyChartClassifyMastSort 获取MY ETA 分类中排序最大的值
func GetMyChartClassifyMastSort(adminId int) (sort int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT MAX(sort) sort FROM my_chart_classify WHERE admin_id=? `
	err = o.Raw(sql, adminId).QueryRow(&sort)
	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"`
	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"`
	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) (isDelMyChart bool, 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
			}
			isDelMyChart = true
		}
	} else {
		sql := `DELETE FROM my_chart WHERE my_chart_id=? `
		_, err = o.Raw(sql, myChartId).Exec()
		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：商品价格曲线"`
}

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

	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 `
	//MyChartClassifyName string `description:"分类名称，多个用英文逗号分割"`
	//MyChartClassifyId   string `description:"分类id，多个用英文逗号分割"`
	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 ?,? "
	_, 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 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 = o.Raw(sql1, 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=? 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 = o.Raw(sql, chartClassifyId, 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
	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) {
	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")
	to, err := o.Begin()
	if err != nil {
		return
	}
	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).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 = to.Raw(sql, v.MyChartClassifyMappingId).Exec()
			if err != nil {
				return
			}
		}
	}
	//sql := `DELETE FROM my_chart_classify_mapping WHERE admin_id=? AND my_chart_id=? `
	//_, err = to.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 = to.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
			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.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
}

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

// CopyChartClassifyReq 复制我的图库分类
type CopyChartClassifyReq struct {
	MyChartClassifyId int `description:"分类id"`
}

// GetPublicMyChartClassifyById 根据分类ID获取公开分类
func GetPublicMyChartClassifyById(myChartClassifyId int) (item *MyChartClassify, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM my_chart_classify WHERE is_public=1 AND my_chart_classify_id=? `
	err = o.Raw(sql, myChartClassifyId).QueryRow(&item)
	return
}

// CopyPublicMyChartClassifyById 根据分类ID获取公开分类
func CopyPublicMyChartClassifyById(myChartClassifyId, adminId int, newMyChartClassifyName string) (newMyChartClassify *MyChartClassify, err error) {
	o := orm.NewOrmUsingDB("data")
	to, err := o.Begin()
	if err != nil {
		return
	}
	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).QueryRows(&fromMappingList)
	if err != nil {
		return
	}

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

	// 我的图表
	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).QueryRows(&fromMyChartList)
	if err != nil {
		return
	}

	//新增我的图库分类
	newMyChartClassify = &MyChartClassify{
		MyChartClassifyName: newMyChartClassifyName,
		AdminId:             adminId,
		CreateTime:          time.Now(),
		ModifyTime:          time.Now(),
	}
	newMyChartClassifyId, err := to.Insert(newMyChartClassify)
	if err != nil {
		return
	}

	newMappingList := make([]*MyChartClassifyMapping, 0)

	//将图表加入我的图库
	for _, v := range fromMyChartList {
		tmpMyChart := &MyChart{
			ChartInfoId: v.ChartInfoId,
			AdminId:     adminId,
			Source:      v.Source,
			CreateTime:  time.Now(),
			ModifyTime:  time.Now(),
		}
		tmpMyChartId, tmpErr := to.Insert(tmpMyChart)
		if tmpErr != nil {
			err = tmpErr
		}
		tmpMyChart.MyChartId = int(tmpMyChartId)

		// 往自己的分类里面新增图表关系

		//原始图表的排序
		tmpSort, ok := fromMyChartSortMap[v.MyChartId]
		if !ok {
			tmpSort = 0
		}
		tmp := &MyChartClassifyMapping{
			AdminId:           adminId,
			MyChartId:         int(tmpMyChartId),
			MyChartClassifyId: int(newMyChartClassifyId),
			CreateTime:        time.Now(),
			ModifyTime:        time.Now(),
			Sort:              tmpSort,
		}
		newMappingList = append(newMappingList, tmp)
	}

	if len(newMappingList) > 0 {
		_, err = to.InsertMulti(len(newMappingList), newMappingList)
	}
	return
}

// ModifyChartClassifyCompanyPublicReq 修改公共图库分类是否用户可见
type ModifyChartClassifyCompanyPublicReq struct {
	MyChartClassifyId int `description:"分类id"`
	IsCompanyPublic   int `description:"是否用户可见 0-否 1-是"`
}

// GetCompanyPublicClassifyList 获取用户可见的分类列表
func GetCompanyPublicClassifyList(condition string, pars []interface{}) (item []*MyChartClassify, err error) {
	o := orm.NewOrmUsingDB("data")
	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 = o.Raw(sql, pars).QueryRows(&item)
	return
}

// GetMyChartClassifyList 获取我的图表关联的分类列表数据
func GetMyChartClassifyList(myChartClassifyId int) (mappingList []*MyChartClassifyMapping, err error) {
	o := orm.NewOrmUsingDB("data")
	//o.Begin()
	//
	//defer func() {
	//	if err != nil {
	//		o.Rollback()
	//	} else {
	//		o.Commit()
	//	}
	//}()
	sql := `SELECT * FROM my_chart_classify_mapping WHERE my_chart_id=?`
	_, err = o.Raw(sql, myChartClassifyId).QueryRows(&mappingList)
	return
}

// AddMyChartToMyChartClassify 添加我的图表至我的分类
func AddMyChartToMyChartClassify(adminId, myChartId int, myChartClassifyId []int) (err error) {
	o := orm.NewOrmUsingDB("data")
	//o.Begin()

	//defer func() {
	//	if err != nil {
	//		o.Rollback()
	//	} else {
	//		o.Commit()
	//	}
	//}()
	//需要添加的分类id 集合map
	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
	}

	//遍历已存在系统中的图表分类集合，如果和需要添加的分类重复了，那么移除需要添加分类集合map里面的数据
	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 = 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
}

// GetMyChartListGroupByCharyInfoIdAndAdminIdByCondition 获取MY ETA 图表数据，根据图表id和操作员id
func GetMyChartListGroupByCharyInfoIdAndAdminIdByCondition(condition string, pars []interface{}) (item []*MyChartList, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT a.admin_id,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,a.admin_id ORDER BY c.sort DESC "
	_, err = o.Raw(sql, pars).QueryRows(&item)
	return
}

func GetRelationChartListByCondition(condition string, pars []interface{}, startSize, pageSize int) (item []*ChartInfoView, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT a.* FROM chart_info AS a 
	JOIN chart_edb_mapping AS b on a.chart_info_id = b.chart_info_id WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	//sql += " ORDER BY sort ASC,chart_info_id DESC LIMIT ?,? "
	sql += " ORDER BY a.create_time DESC LIMIT ?,? "
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&item)
	return
}

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

func GetChartItems(cond string, pars []interface{}, orderRule string) (item []*ChartInfoView, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT * FROM chart_info WHERE 1=1 `
	if cond != "" {
		sql += cond
	}
	order := ` ORDER BY create_time DESC`
	if orderRule != "" {
		order = fmt.Sprintf(` ORDER BY %s`, orderRule)
	}
	sql += order
	_, err = o.Raw(sql, pars).QueryRows(&item)
	return
}