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 } func GetChartClassifyByIds(chartClassifyIds []string) (chart_classify_ids string, err error) { o := orm.NewOrmUsingDB("data") sql := `SELECT GROUP_CONCAT(DISTINCT t.chart_classify_id) AS chart_classify_ids FROM ( SELECT chart_classify_id FROM chart_classify WHERE chart_classify_id IN (` + utils.GetOrmInReplace(len(chartClassifyIds)) + `) OR parent_id IN (` + utils.GetOrmInReplace(len(chartClassifyIds)) + `) OR parent_id IN ( SELECT chart_classify_id FROM chart_classify WHERE parent_id IN (` + utils.GetOrmInReplace(len(chartClassifyIds)) + `) ) ) AS t;` err = o.Raw(sql, chartClassifyIds, chartClassifyIds, chartClassifyIds).QueryRow(&chart_classify_ids) return } func GetChartClassifyByIdsNoSubClassify(chartClassifyIds []string) (chart_classify_ids string, err error) { o := orm.NewOrmUsingDB("data") sql := `SELECT GROUP_CONCAT(DISTINCT t.chart_classify_id) AS chart_classify_ids FROM ( SELECT chart_classify_id FROM chart_classify WHERE chart_classify_id IN (` + utils.GetOrmInReplace(len(chartClassifyIds)) + `) ) AS t;` err = o.Raw(sql, chartClassifyIds).QueryRow(&chart_classify_ids) return }