eta_mobile/models/classify.go

package models

import (
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"github.com/rdlucklib/rdluck_tools/paging"
	"hongze/hongze_ETA_mobile_api/utils"
	"time"
)

type Classify struct {
	Id                int       `orm:"column(id);pk"`
	ClassifyName      string    `description:"分类名称"`
	Sort              int       `json:"-"`
	ParentId          int       `description:"父级分类id"`
	CreateTime        time.Time `description:"创建时间"`
	ModifyTime        time.Time `description:"修改时间"`
	Abstract          string    `description:"栏目简介"`
	Descript          string    `description:"分享描述"`
	ReportAuthor      string    `description:"栏目作者"`
	AuthorDescript    string    `description:"作者简介"`
	ColumnImgUrl      string    `description:"栏目配图"`
	HeadImgUrl        string    `description:"头部banner"`
	AvatarImgUrl      string    `description:"头像"`
	ReportImgUrl      string    `description:"报告配图"`
	HomeImgUrl        string    `description:"首页配图"`
	ClassifyLabel     string    `description:"分类标签"`
	ShowType          int       `description:"展示类型：1-列表 2-专栏"`
	HasTeleconference int       `description:"是否有电话会：0-否 1-是"`
	VipTitle          string    `description:"研究员头衔"`
	IsShow            int       `description:"是否在小程序显示：1-显示 0-隐藏"`
	YbFiccSort        int       `description:"小程序FICC页排序"`
	YbFiccIcon        string    `description:"小程序FICC页icon"`
	YbFiccPcIcon      string    `description:"小程序PC端FICC页背景图"`
	YbIconUrl         string    `description:"小程序已购页icon"`
	YbBgUrl           string    `description:"小程序已购详情背景图"`
	YbListImg         string    `description:"小程序研报列表封面图"`
	YbShareBgImg      string    `description:"小程序研报详情分享背景图"`
	YbRightBanner     string    `description:"Pc端详情页，右侧，报告合集背景图"`
	RelateTel         int       `description:"是否在电话会中可选: 0-否; 1-是"`
	RelateVideo       int       `description:"是否在路演视频中可选: 0-否; 1-是"`
	IsMassSend        int       `description:"1:群发,0:非群发"`
}

type ClassifyAddReq struct {
	ClassifyName      string                 `description:"分类名称"`
	ParentId          int                    `description:"父级分类id，没有父级分类传0"`
	Abstract          string                 `description:"栏目简介"`
	Descript          string                 `description:"分享描述"`
	ReportAuthor      string                 `description:"栏目作者"`
	AuthorDescript    string                 `description:"作者简介"`
	ColumnImgUrl      string                 `description:"栏目配图"`
	ReportImgUrl      string                 `description:"报告配图"`
	HeadImgUrl        string                 `description:"头部banner"`
	AvatarImgUrl      string                 `description:"头像"`
	HomeImgUrl        string                 `description:"首页配图"`
	ClassifyLabel     string                 `description:"分类标签"`
	ShowType          int                    `description:"展示类型：1-列表 2-专栏"`
	HasTeleconference int                    `description:"是否有电话会：0-否 1-是"`
	VipTitle          string                 `description:"研究员头衔"`
	Sort              int                    `description:"后台排序"`
	IsShow            int                    `description:"是否在小程序显示：1-显示 0-隐藏"`
	YbFiccSort        int                    `description:"小程序FICC页排序"`
	YbFiccIcon        string                 `description:"小程序FICC页icon"`
	YbFiccPcIcon      string                 `description:"小程序PC端FICC页背景图"`
	YbIconUrl         string                 `description:"小程序已购页icon"`
	YbBgUrl           string                 `description:"小程序已购详情背景图"`
	YbListImg         string                 `description:"小程序研报列表封面图"`
	YbShareBgImg      string                 `description:"小程序研报详情分享背景图"`
	YbRightBanner     string                 `description:"Pc端详情页，右侧，报告合集背景图"`
	MenuList          []*ClassifyMenuSaveReq `description:"子目录列表"`
	ClassifyMenuId    int                    `description:"二级分类-子目录ID"`
	RelateTel         int                    `description:"是否在电话会中可选: 0-否; 1-是"`
	RelateVideo       int                    `description:"是否在路演视频中可选: 0-否; 1-是"`
}

func GetClassifyByName(classifyName string, parentId int) (item *Classify, err error) {
	sql := `SELECT * FROM classify WHERE classify_name=? AND parent_id=? `
	o := orm.NewOrmUsingDB("rddp")
	err = o.Raw(sql, classifyName, parentId).QueryRow(&item)
	return
}

func GetClassifyById(classifyId int) (item *Classify, err error) {
	sql := `SELECT * FROM classify WHERE id=? `
	o := orm.NewOrmUsingDB("rddp")
	err = o.Raw(sql, classifyId).QueryRow(&item)
	return
}

// 添加分类
func AddClassify(item *Classify) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	id, err := o.Insert(item)
	if err != nil {
		return
	}
	item.Id = int(id)
	return
}

func GetReportCountByClassifyId(classifyId int) (count int, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT COUNT(1) AS count FROM report WHERE classify_id_second=? `
	err = o.Raw(sql, classifyId).QueryRow(&count)
	return
}

func GetClassifySubCountByClassifyId(classifyId int) (count int, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT COUNT(1) as num FROM classify AS a
        INNER JOIN report AS b ON a.id=b.classify_id_second
        WHERE a.parent_id=? `
	err = o.Raw(sql, classifyId).QueryRow(&count)
	return
}

func GetClassifySubCountByParentId(classifyId int) (count int, err error) {
	sqlCount := `
	SELECT COUNT(1) as num FROM classify AS a
	WHERE a.parent_id=? `
	o := orm.NewOrmUsingDB("rddp")
	err = o.Raw(sqlCount, classifyId).QueryRow(&count)
	return
}

// 删除分类
func DeleteClassify(classifyId int) (err error) {
	sql := `DELETE FROM classify WHERE id=? `
	o := orm.NewOrmUsingDB("rddp")
	_, err = o.Raw(sql, classifyId).Exec()
	if err != nil {
		return
	}
	deleteImgSql := `DELETE FROM banner WHERE classify_id=? `
	_, err = o.Raw(deleteImgSql, classifyId).Exec()
	return
}

// classifyName, abstract, descript string, parentId, classifyId int
// 修改分类
func EditClassify(req *EditClassifyReq) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE classify SET classify_name = ?,abstract=?, parent_id= ?,descript=?,report_author=?,author_descript=?,column_img_url=?,head_img_url=?,avatar_img_url=?,report_img_url=?,home_img_url=?,classify_label=?,show_type=?,has_teleconference=?,vip_title=?,modify_time= NOW() WHERE id = ? `
	_, err = o.Raw(sql, req.ClassifyName, req.Abstract, req.ParentId, req.Descript, req.ReportAuthor, req.AuthorDescript, req.ColumnImgUrl, req.HeadImgUrl, req.AvatarImgUrl, req.ReportImgUrl, req.HomeImgUrl, req.ClassifyLabel, req.ShowType, req.HasTeleconference, req.VipTitle, req.ClassifyId).Exec()
	return
}

//获取父级分类

func ParentClassify() (items []*Classify, err error) {
	sql := `SELECT * FROM classify WHERE parent_id=0 order by id desc `
	o := orm.NewOrmUsingDB("rddp")
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 根据id获取分类详情
func FindByIdClassify(classifyId int) (item *Classify, err error) {
	sql := `SELECT * FROM classify WHERE id=? `
	o := orm.NewOrmUsingDB("rddp")
	err = o.Raw(sql, classifyId).QueryRow(&item)
	return
}

type ClassifyList struct {
	Id                int       `orm:"column(id);pk"`
	ClassifyName      string    `description:"分类名称"`
	Sort              int       `description:"排序"`
	ParentId          int       `description:"父级分类id"`
	CreateTime        time.Time `description:"创建时间"`
	ModifyTime        time.Time `description:"修改时间"`
	Abstract          string    `description:"简介"`
	Descript          string    `description:"描述"`
	ClassifyLabel     string    `description:"分类标签"`
	ShowType          int       `description:"展示类型：1-列表 2-专栏"`
	HasTeleconference int       `description:"是否有电话会：0-否 1-是"`
	IsShow            int       `description:"是否在小程序显示：1-显示 0-隐藏"`
	YbFiccSort        int       `description:"小程序FICC页排序"`
	YbFiccIcon        string    `description:"小程序FICC页icon"`
	YbFiccPcIcon      string    `description:"小程序PC端FICC页背景图"`
	YbIconUrl         string    `description:"小程序已购页icon"`
	YbBgUrl           string    `description:"小程序已购详情背景图"`
	YbListImg         string    `description:"小程序研报列表封面图"`
	YbShareBgImg      string    `description:"小程序研报详情分享背景图"`
	YbRightBanner     string    `description:"Pc端详情页，右侧，报告合集背景图"`
	RelateTel         int       `description:"是否在电话会中可选: 0-否; 1-是"`
	RelateVideo       int       `description:"是否在路演视频中可选: 0-否; 1-是"`
	Child             []*ClassifyItem
	ClassifyMenuList  []*ClassifyMenu
}

type ClassifyItem struct {
	Classify
	ClassifyMenuId   int `description:"二级分类-子目录ID"`
	ClassifyMenuList []*ClassifyMenu
}

type ClassifyListResp struct {
	List   []*ClassifyList
	Paging *paging.PagingItem `description:"分页数据"`
}

type ClassifyPermissionListResp struct {
	List   []*ClassifyList
	Paging *paging.PagingItem `description:"分页数据"`
}

// 获取分类列表
func GetClassifyList(startSize, pageSize int, keyWord, companyType string, hideDayWeek int) (items []*ClassifyList, err error) {
	sql := ``
	companyTypeSqlStr := ``
	if companyType == "ficc" {
		companyTypeSqlStr = " AND id != 40 AND parent_id != 40 "
	} else if companyType == "权益" {
		companyTypeSqlStr = " AND (id = 40 or parent_id = 40)  "
	}
	if keyWord != "" {
		sql = `SELECT * FROM (
                   SELECT * FROM classify
                   WHERE parent_id=0 ` + companyTypeSqlStr + `  AND classify_name LIKE '%` + keyWord + `%'
                   UNION
                   SELECT * FROM classify
                   WHERE id IN(SELECT parent_id FROM classify
                   WHERE parent_id>0 ` + companyTypeSqlStr + `  AND classify_name LIKE '%` + keyWord + `%')
                   )AS t
                   ORDER BY sort ASC,create_time ASC
                   LIMIT ?,? `
	} else {
		sql = `SELECT * FROM classify WHERE parent_id=0 ` + companyTypeSqlStr
		if hideDayWeek == 1 {
			sql += ` AND classify_name <> '晨报' AND classify_name <> '周报' `
		}
		sql += ` ORDER BY sort ASC, create_time ASC LIMIT ?,? `
	}
	o := orm.NewOrmUsingDB("rddp")
	_, err = o.Raw(sql, startSize, pageSize).QueryRows(&items)
	return
}

func GetClassifyListCount(keyWord, companyType string, hideDayWeek int) (count int, err error) {
	sqlCount := ``

	companyTypeSqlStr := ``
	if companyType == "ficc" {
		companyTypeSqlStr = " AND id != 40 AND parent_id != 40 "
	} else if companyType == "权益" {
		companyTypeSqlStr = " AND (id = 40 or parent_id = 40)  "
	}
	if keyWord != "" {
		sqlCount = `SELECT  COUNT(1) AS count FROM (
               SELECT * FROM classify
               WHERE parent_id=0 ` + companyTypeSqlStr + `  AND classify_name LIKE '%` + keyWord + `%'
               UNION
               SELECT * FROM classify
               WHERE id IN(SELECT parent_id FROM classify
               WHERE parent_id>0 ` + companyTypeSqlStr + `  AND classify_name LIKE '%` + keyWord + `%')
               )AS t `

	} else {
		sqlCount = `SELECT COUNT(1) AS count FROM classify WHERE parent_id=0 ` + companyTypeSqlStr
		if hideDayWeek == 1 {
			sqlCount += ` AND classify_name <> '晨报' AND classify_name <> '周报' `
		}
	}
	o := orm.NewOrmUsingDB("rddp")
	err = o.Raw(sqlCount).QueryRow(&count)
	return
}

type CheckDeleteClassifyReq struct {
	ClassifyId int `description:"分类ID"`
}

type CheckDeleteClassifyResp struct {
	Code int    `description:"编码：0:检测成功，可进行删除,1：分类不存在，2：该分类有关联报告，不允许删除,3:二级分类有关联报告，不允许删除,4:该分类下有关联分类，是否确认全部删除"`
	Msg  string `description:"描述信息"`
}

type DeleteClassifyReq struct {
	ClassifyId int `description:"分类ID"`
}

type EditClassifyReq struct {
	ClassifyId int `description:"分类ID"`
	ClassifyAddReq
}

type FindByIdClassifyReq struct {
	ClassifyId int `description:"分类ID"`
}

func GetClassifyChild(parentId int, keyWord string) (items []*Classify, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ``
	if keyWord != "" {
		sql = `SELECT * FROM classify WHERE parent_id=? AND classify_name LIKE '%` + keyWord + `%' ORDER BY create_time ASC `
	} else {
		sql = `SELECT * FROM classify WHERE parent_id=? ORDER BY create_time ASC `
	}
	_, err = o.Raw(sql, parentId).QueryRows(&items)
	return
}

func GetClassifyChildByParentIds(parentId []int, keyWord string) (items []*Classify, err error) {
	parentIdLen := len(parentId)
	if parentIdLen == 0 {
		return
	}
	o := orm.NewOrmUsingDB("rddp")
	sql := ``
	if keyWord != "" {
		sql = `SELECT * FROM classify WHERE parent_id IN (` + utils.GetOrmInReplace(parentIdLen) + `) AND classify_name LIKE '%` + keyWord + `%' ORDER BY create_time ASC `
	} else {
		sql = `SELECT * FROM classify WHERE parent_id IN (` + utils.GetOrmInReplace(parentIdLen) + `) ORDER BY create_time ASC `
	}
	_, err = o.Raw(sql, parentId).QueryRows(&items)
	return
}

// EditClassifyPermissionReq 编辑分类权限请求
type EditClassifyPermissionReq struct {
	ClassifyId            int   `description:"分类ID"`
	ChartPermissionIdList []int `description:"权限id数组"`
}

// GetAllClassify 获取所有分类
func GetAllClassify() (list []*Classify, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` SELECT * FROM classify `
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// GetClassifyByKeyword 名称获取分类
func GetClassifyByKeyword(keyword string) (item Classify, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` SELECT * FROM classify WHERE classify_name = ? LIMIT 1 `
	err = o.Raw(sql, keyword).QueryRow(&item)
	return
}

// UpdateClassify 更新分类
func (classifyInfo *Classify) UpdateClassify(cols []string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	_, err = o.Update(classifyInfo, cols...)

	return
}

// SimpleClassifyList 简版分类列表
type SimpleClassifyList struct {
	Id           int    `description:"分类ID"`
	ClassifyName string `description:"分类名称"`
	ParentId     int    `description:"父级ID"`
	Sort         int    `description:"排序"`
	Child        []*SimpleClassifyList
}

// GetClassifyByCondition 获取分类列表
func GetClassifyByCondition(condition, orderRule string, pars []interface{}) (items []*SimpleClassifyList, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * FROM classify WHERE 1 = 1 `
	if condition != `` {
		sql += condition
	}
	order := `sort ASC, create_time ASC`
	if orderRule != `` {
		order = orderRule
	}
	sql += ` ORDER BY ` + order
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// UpdateChildClassifyRelateSetting 更新子分类关联设置
func UpdateChildClassifyRelateSetting(parentId, relateTel, relateVideo int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE classify SET relate_tel = ?, relate_video = ? WHERE parent_id = ?`
	_, err = o.Raw(sql, relateTel, relateVideo, parentId).Exec()
	return
}

// RelateTelSecClassifyWithPermissions 关联了电话会的二级分类及权限
type RelateTelSecClassifyWithPermissions struct {
	Id                 int    `description:"分类ID"`
	ClassifyName       string `description:"分类名称"`
	ChartPermissionIds string `description:"权限IDs"`
}

// GetRelateTelSecClassifyAndChartPermissions 获取关联了电话会的二级分类及权限
func GetRelateTelSecClassifyAndChartPermissions() (items []*RelateTelSecClassifyWithPermissions, err error) {
	dbName := `weekly_report`
	if utils.RunMode == "debug" {
		dbName = `test_v2_weekly_report`
	}
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT
				a.id,
				a.classify_name,
				GROUP_CONCAT(b.chart_permission_id) AS chart_permission_ids
			FROM classify AS a
			LEFT JOIN %s.chart_permission_search_key_word_mapping AS b ON b.from = 'rddp' AND a.classify_name = b.key_word
			WHERE
				a.parent_id > 0 AND a.relate_tel = 1
			GROUP BY a.id`
	sql = fmt.Sprintf(sql, dbName)
	_, err = o.Raw(sql).QueryRows(&items)
	return
}