package data_manage
import (
"eta/eta_api/utils"
"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, my_chart_classify_id asc "
_, 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 []*MyChartClassifyItem
Language string `description:"指标的展示语言，CN：中文，EN：英文"`
}
// PublicChartClassifyResp 公共分类返回数据结构体
type PublicChartClassifyResp struct {
List []PublicChartClassifyList
Language string `description:"指标的展示语言，CN：中文，EN：英文"`
}
// PublicChartClassifyItem 公共分类结构体
type PublicChartClassifyItem struct {
MyChartClassifyId int `description:"分类id"`
MyChartClassifyName string `description:"分类名称"`
AdminId int `description:"创建人id"`
RealName string `description:"创建人名称"`
IsPublic int `description:"是否公共分类"`
IsCompanyPublic int `description:"是否为客户可见"`
}
// PublicChartClassifyList 公共分类结构体
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) {
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
}
// GetMyChartClassifyMinSort 获取MY ETA 分类中排序最小的值
func GetMyChartClassifyMinSort(adminId int) (sort int, err error) {
o := orm.NewOrmUsingDB("data")
sql := ` SELECT MIN(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：商品价格曲线"`
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) {
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,b.is_join_permission `
//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
}
// UpdateMyChartSortByClassifyId 根据分类id更新排序
func UpdateMyChartSortByClassifyId(myChartClassifyId, nowSort int, prevMyChartClassifyMappingId int, updateSort string) (err error) {
o := orm.NewOrmUsingDB("data")
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 = o.Raw(sql, myChartClassifyId, nowSort, prevMyChartClassifyMappingId, nowSort).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,my_chart_classify_mapping_id ASC LIMIT 1 `
} else {
sql += ` ORDER BY sort ASC,my_chart_classify_mapping_id DESC 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
}
var minSort int
sql = ` SELECT MIN(sort) sort FROM my_chart_classify WHERE admin_id=? `
err = o.Raw(sql, adminId).QueryRow(&minSort)
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).QueryRows(&fromMyChartList)
if err != nil {
return
}
//新增我的图库分类
newMyChartClassify = &MyChartClassify{
MyChartClassifyName: newMyChartClassifyName,
AdminId: adminId,
CreateTime: time.Now(),
ModifyTime: time.Now(),
Sort: minSort - 1,
}
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
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
WHERE 1=1 `
if condition != "" {
sql += condition
}
err = o.Raw(sql, pars).QueryRow(&count)
return
}
// GetChartInfoByIdList 根据图表id集合 获取 图表列表
func GetChartInfoByIdList(chartInfoIdList []int) (items []*ChartInfo, err error) {
num := len(chartInfoIdList)
if num <= 0 {
return
}
o := orm.NewOrmUsingDB("data")
sql := ` SELECT * FROM chart_info WHERE chart_info_id in (` + utils.GetOrmInReplace(num) + `) `
_, err = o.Raw(sql, chartInfoIdList).QueryRows(&items)
return
}
func GetChartInfoViewByIdList(chartInfoIdList []int) (items []*ChartInfoView, err error) {
num := len(chartInfoIdList)
if num <= 0 {
return
}
o := orm.NewOrmUsingDB("data")
sql := ` SELECT * FROM chart_info WHERE chart_info_id in (` + utils.GetOrmInReplace(num) + `) `
_, err = o.Raw(sql, chartInfoIdList).QueryRows(&items)
return
}
// GetMyChartClassifyByClassifyId 主键获取分类
func GetMyChartClassifyByClassifyId(classifyId int) (item *MyChartClassify, err error) {
o := orm.NewOrmUsingDB("data")
sql := ` SELECT * FROM my_chart_classify WHERE my_chart_classify_id = ? `
err = o.Raw(sql, classifyId).QueryRow(&item)
return
}
// MyChartClassifyItem 我的图表分类信息
type MyChartClassifyItem struct {
MyChartClassifyId int `description:"分类ID"`
MyChartClassifyName string `description:"分类名称"`
AdminId int `description:"创建人id"`
IsPublic int `description:"是否公共分类"`
IsCompanyPublic int `description:"是否为用户公共分类"`
ChartNum int `description:"分类下的图表数量"`
}
// FormatMyChartClassify2Item 格式化我的图表信息
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
}
// MyChartClassifyIdAndNum 我的图表-分类ID及图表数
type MyChartClassifyIdAndNum struct {
MyChartClassifyId int `description:"分类ID"`
ChartNum int `description:"分类下的图表数量"`
}
// GetMyChartClassifyIdAndNum 我的图表-获取分类ID及图表数
func GetMyChartClassifyIdAndNum(cond string, pars []interface{}) (items []*MyChartClassifyIdAndNum, err error) {
o := orm.NewOrmUsingDB("data")
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 = o.Raw(sql, pars).QueryRows(&items)
return
}