eta_api/models/english_report.go

package models

import (
	"errors"
	"eta/eta_api/utils"
	"fmt"
	"strings"
	"time"

	"github.com/beego/beego/v2/client/orm"
	"github.com/rdlucklib/rdluck_tools/paging"
)

type EnglishReport struct {
	Id                 int       `orm:"column(id)" description:"报告Id"`
	AddType            int       `description:"新增方式：1:新增报告，2:继承报告"`
	ClassifyIdFirst    int       `description:"一级分类id"`
	ClassifyNameFirst  string    `description:"一级分类名称"`
	ClassifyIdSecond   int       `description:"二级分类id"`
	ClassifyNameSecond string    `description:"二级分类名称"`
	Title              string    `description:"标题"`
	Abstract           string    `description:"摘要"`
	Author             string    `description:"作者"`
	Frequency          string    `description:"频度"`
	CreateTime         string    `description:"创建时间"`
	ModifyTime         time.Time `description:"修改时间"`
	State              int       `description:"1:未发布；2:已发布；3-待提交；4-待审批；5-已驳回；6-已通过"`
	PublishTime        time.Time `description:"发布时间"`
	PrePublishTime     time.Time `description:"预发布时间"`
	Stage              int       `description:"期数"`
	Content            string    `description:"内容"`
	VideoUrl           string    `description:"音频文件URL"`
	VideoName          string    `description:"音频文件名称"`
	VideoPlaySeconds   string    `description:"音频播放时长"`
	VideoSize          string    `description:"音频文件大小，单位M"`
	ContentSub         string    `description:"内容前两个章节"`
	ReportCode         string    `description:"报告唯一编码"`
	Pv                 int       `description:"Pv"`
	PvEmail            int       `description:"邮箱PV"`
	UvEmail            int       `description:"邮箱UV"`
	EmailState         int       `description:"群发邮件状态: 0-未发送; 1-已发送"`
	Overview           string    `description:"英文概述部分"`
	KeyTakeaways       string    `description:"关键点"`
	FromReportId       int       `description:"继承的报告ID（英文策略报告ID）"`
	AdminId            int       `description:"创建者账号"`
	AdminRealName      string    `description:"创建者姓名"`
	ApproveTime        time.Time `description:"审批时间"`
	ApproveId          int       `description:"审批ID"`
	DetailImgUrl       string    `description:"报告详情长图地址"`
	DetailPdfUrl       string    `description:"报告详情PDF地址"`
	DetailImgUrlMobile string    `description:"报告详情长图地址-手机端"`
	DetailPdfUrlMobile string    `description:"报告详情PDF地址-手机端"`
	EmailHasFail       int       `description:"是否存在邮件发送失败的记录: 0-否; 1-是"`
}

func GetEnglishReportStage(classifyIdFirst, classifyIdSecond int) (count int, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ``
	if classifyIdSecond > 0 {
		sql = "SELECT MAX(stage) AS max_stage FROM english_report WHERE classify_id_second=? "
		o.Raw(sql, classifyIdSecond).QueryRow(&count)
	} else {
		sql = "SELECT MAX(stage) AS max_stage FROM english_report WHERE classify_id_first=? "
		o.Raw(sql, classifyIdFirst).QueryRow(&count)
	}
	return
}

func GetEnglishReportStageEdit(classifyIdFirst, classifyIdSecond, reportId int) (count int, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ``
	if classifyIdSecond > 0 {
		sql = "SELECT MAX(stage) AS max_stage FROM english_report WHERE classify_id_second=? AND id<>? "
		o.Raw(sql, classifyIdSecond, reportId).QueryRow(&count)
	} else {
		sql = "SELECT MAX(stage) AS max_stage FROM english_report WHERE classify_id_first=? AND id<>? "
		o.Raw(sql, classifyIdFirst, reportId).QueryRow(&count)
	}
	return
}

func AddEnglishReport(item *EnglishReport) (lastId int64, err error) {
	o := orm.NewOrmUsingDB("rddp")
	lastId, err = o.Insert(item)
	return
}

func ModifyEnglishReportCode(reportId int64, reportCode string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE english_report SET report_code=? WHERE id=? `
	_, err = o.Raw(sql, reportCode, reportId).Exec()
	return
}

type AddEnglishReportReq struct {
	AddType            int    `description:"新增方式：1:新增报告，2:继承报告"`
	ClassifyIdFirst    int    `description:"一级分类id"`
	ClassifyNameFirst  string `description:"一级分类名称"`
	ClassifyIdSecond   int    `description:"二级分类id"`
	ClassifyNameSecond string `description:"二级分类名称"`
	Title              string `description:"标题"`
	Abstract           string `description:"摘要"`
	Author             string `description:"作者"`
	Frequency          string `description:"频度"`
	State              int    `description:"状态：1:未发布，2:已发布"`
	Content            string `description:"内容"`
	CreateTime         string `description:"创建时间"`
	Overview           string `description:"英文概述部分"`
}

type AddEnglishReportResp struct {
	ReportId   int64  `description:"报告id"`
	ReportCode string `description:"报告code"`
}

type EditEnglishReportReq struct {
	ReportId           int64  `description:"报告id"`
	ClassifyIdFirst    int    `description:"一级分类id"`
	ClassifyNameFirst  string `description:"一级分类名称"`
	ClassifyIdSecond   int    `description:"二级分类id"`
	ClassifyNameSecond string `description:"二级分类名称"`
	Title              string `description:"标题"`
	Abstract           string `description:"摘要"`
	Author             string `description:"作者"`
	Frequency          string `description:"频度"`
	State              int    `description:"状态：1:未发布，2:已发布"`
	Content            string `description:"内容"`
	CreateTime         string `description:"创建时间"`
	Overview           string `description:"英文概述部分"`
}

type EditEnglishReportFromPolicyReq struct {
	ReportId   int64  `description:"报告id"`
	Title      string `description:"标题"`
	Abstract   string `description:"摘要"`
	Author     string `description:"作者"`
	Frequency  string `description:"频度"`
	CreateTime string `description:"创建时间"`
	//Overview           string `description:"英文概述部分"`
}
type EditEnglishReportResp struct {
	ReportId   int64  `description:"报告id"`
	ReportCode string `description:"报告code"`
}

type ElasticEnglishReportDetail struct {
	Id                 string `description:"报告id或者线上路演Id"`
	ReportId           int    `description:"报告id"`
	VideoId            int    `description:"线上路演Id"`
	ClassifyIdFirst    int    `description:"一级分类id"`
	ClassifyNameFirst  string `description:"一级分类名称"`
	ClassifyIdSecond   int    `description:"二级分类id"`
	ClassifyNameSecond string `description:"二级分类名称"`
	StageStr           string `description:"报告期数"`
	Title              string `description:"标题"`
	Abstract           string `description:"摘要"`
	Author             string `description:"作者"`
	Frequency          string `description:"频度"`
	PublishState       int    `description:"状态：1:未发布，2:已发布"`
	BodyContent        string `description:"内容"`
	ContentSub         string `description:"前两段内容"`
	CreateTime         string `description:"创建时间"`
	PublishTime        string `description:"发布时间"`
	ReportCode         string `description:"报告唯一编码"`
	Overview           string `description:"英文概述部分"`
}

func EditEnglishReport(item *EnglishReport, reportId int64) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE english_report
			SET
			  classify_id_first =?,
			  classify_name_first = ?,
			  classify_id_second = ?,
			  classify_name_second = ?,
			  title = ?,
			  abstract = ?,
			  author = ?,
			  frequency = ?,
			  state = ?,
			  content = ?,
			  content_sub = ?,
			  stage =?,
			  create_time = ?,
			  modify_time = ?,
			  overview = ?
			WHERE id = ? `
	_, err = o.Raw(sql, item.ClassifyIdFirst, item.ClassifyNameFirst, item.ClassifyIdSecond, item.ClassifyNameSecond, item.Title,
		item.Abstract, item.Author, item.Frequency, item.State, item.Content, item.ContentSub, item.Stage, item.CreateTime, time.Now(), item.Overview, reportId).Exec()
	return
}

type EnglishReportDetail struct {
	Id                 int    `orm:"column(id)" description:"报告Id"`
	AddType            int    `description:"新增方式：1:新增报告，2:继承报告"`
	ClassifyIdFirst    int    `description:"一级分类id"`
	ClassifyNameFirst  string `description:"一级分类名称"`
	ClassifyIdSecond   int    `description:"二级分类id"`
	ClassifyNameSecond string `description:"二级分类名称"`
	Title              string `description:"标题"`
	Abstract           string `description:"摘要"`
	Author             string `description:"作者"`
	Frequency          string `description:"频度"`
	CreateTime         string `description:"创建时间"`
	ModifyTime         string `description:"修改时间"`
	State              int    `description:"1:未发布，2:已发布"`
	PublishTime        string `description:"发布时间"`
	PrePublishTime     string `description:"预发布时间"`
	Stage              int    `description:"期数"`
	MsgIsSend          int    `description:"消息是否已发送，0:否，1:是"`
	ReportCode         string `description:"报告唯一编码"`
	Content            string `description:"内容"`
	VideoUrl           string `description:"音频文件URL"`
	VideoName          string `description:"音频文件名称"`
	VideoPlaySeconds   string `description:"音频播放时长"`
	ContentSub         string `description:"内容前两个章节"`
	Pv                 int    `description:"Pv"`
	Overview           string `description:"英文概述部分"`
	FromReportId       int    `description:"继承的报告ID（英文策略报告ID）"`
	KeyTakeaways       string `description:"关键点"`
	ClassifyIdRoot     int    `description:"顶级分类id"`
	ClassifyNameRoot   string `description:"顶级分类名称"`
}

func GetEnglishReportById(reportId int) (item *EnglishReportDetail, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * FROM english_report WHERE id=?`
	err = o.Raw(sql, reportId).QueryRow(&item)
	return
}

func GetEnglishReportItemById(reportId int) (item *EnglishReport, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * FROM english_report WHERE id = ? LIMIT 1`
	err = o.Raw(sql, reportId).QueryRow(&item)
	return
}

type EnglishReportList struct {
	Id                 int       `description:"报告Id"`
	AddType            int       `description:"新增方式：1:新增报告，2:继承报告"`
	ClassifyIdFirst    int       `description:"一级分类id"`
	ClassifyNameFirst  string    `description:"一级分类名称"`
	ClassifyIdSecond   int       `description:"二级分类id"`
	ClassifyNameSecond string    `description:"二级分类名称"`
	Title              string    `description:"标题"`
	Abstract           string    `description:"摘要"`
	Author             string    `description:"作者"`
	Frequency          string    `description:"频度"`
	CreateTime         string    `description:"创建时间"`
	ModifyTime         time.Time `description:"修改时间"`
	State              int       `description:"1:未发布；2:已发布；3-待提交；4-待审批；5-已驳回；6-已通过"`
	PublishTime        string    `description:"发布时间"`
	PrePublishTime     string    `description:"预发布时间"`
	Stage              int       `description:"期数"`
	Content            string    `description:"内容"`
	VideoUrl           string    `description:"音频文件URL"`
	VideoName          string    `description:"音频文件名称"`
	VideoPlaySeconds   string    `description:"音频播放时长"`
	ContentSub         string    `description:"内容前两个章节"`
	ReportCode         string    `description:"报告唯一编码"`
	Pv                 int       `description:"Pv"`
	ShareUrl           string    `description:"分享url"`
	PvEmail            int       `description:"邮箱PV"`
	UvEmail            int       `description:"邮箱UV"`
	EmailState         int       `description:"群发邮件状态: 0-未发送; 1-已发送"`
	EmailAuth          bool      `description:"是否有权限群发邮件"`
	EmailHasFail       bool      `description:"是否存在邮件发送失败的记录"`
	CanEdit            bool      `description:"是否可编辑"`
	Editor             string    `description:"编辑人"`
	FromReportId       int       `description:"继承的报告ID（英文策略报告ID）"`
	AdminId            int       `description:"创建者账号"`
	AdminRealName      string    `description:"创建者姓名"`
	FullClassifyName   string    `description:"顶级分类名/父级分类名/当前分类名"`
	ClassifyIdRoot     int       `description:"顶级分类id"`
	ClassifyNameRoot   string    `description:"顶级分类名称"`
	ApproveTime        string    `description:"审批时间"`
	DetailImgUrl       string    `description:"报告详情长图地址"`
	DetailPdfUrl       string    `description:"报告详情PDF地址"`
	DetailImgUrlMobile string    `description:"报告详情长图地址-手机端"`
	DetailPdfUrlMobile string    `description:"报告详情PDF地址-手机端"`
}

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

func GetEnglishReportListCount(condition string, pars []interface{}, companyType string) (count int, err error) {
	//产品权限
	companyTypeSqlStr := ``
	if companyType == "ficc" {
		companyTypeSqlStr = " AND classify_id_first != 40 "
	} else if companyType == "权益" {
		companyTypeSqlStr = " AND classify_id_first = 40 "
	}

	oRddp := orm.NewOrmUsingDB("rddp")
	sql := `SELECT COUNT(1) AS count  FROM english_report WHERE 1=1 ` + companyTypeSqlStr
	if condition != "" {
		sql += condition
	}
	err = oRddp.Raw(sql, pars).QueryRow(&count)
	return
}

func GetEnglishReportList(condition string, pars []interface{}, companyType string, startSize, pageSize int, fieldArr []string) (items []*EnglishReport, err error) {
	o := orm.NewOrmUsingDB("rddp")
	//产品权限
	companyTypeSqlStr := ``
	if companyType == "ficc" {
		companyTypeSqlStr = " AND classify_id_first != 40 "
	} else if companyType == "权益" {
		companyTypeSqlStr = " AND classify_id_first = 40 "
	}

	fields := "*"
	if len(fieldArr) > 0 {
		fields = strings.Join(fieldArr, ",")
	}

	sql := fmt.Sprintf(`SELECT %s FROM english_report WHERE 1=1 %s `, fields, companyTypeSqlStr)
	if condition != "" {
		sql += condition
	}
	// 排序：1:未发布；2:已发布；3-待提交；4-待审批；5-已驳回；6-已通过
	sql += ` ORDER BY FIELD(state,3,1,4,5,6,2), modify_time DESC LIMIT ?,?`
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

func GetEnglishReportByCondition(condition string, pars []interface{}) (items []*EnglishReport, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * 
        FROM english_report WHERE 1=1  `
	if condition != "" {
		sql += condition
	}
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

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

// PublishEnglishReportById 发布报告
func PublishEnglishReportById(reportId int, publishTime string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE english_report SET state=2,publish_time=?,pre_publish_time=null,modify_time=NOW() WHERE id = ? `
	_, err = o.Raw(sql, publishTime, reportId).Exec()
	return
}

// ResetEnglishReportById 重置报告状态
func ResetEnglishReportById(reportId, state int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE english_report SET state = ?, pre_publish_time = null, modify_time = NOW() WHERE id = ?`
	_, err = o.Raw(sql, state, reportId).Exec()
	return
}

// PublishCancelEnglishReport 取消发布报告
func PublishCancelEnglishReport(reportIds, state int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` UPDATE english_report SET state=?, pre_publish_time=null WHERE id =?  `
	_, err = o.Raw(sql, state, reportIds).Exec()
	return
}

// SetPrePublishEnglishReportById 设置定时发布
func SetPrePublishEnglishReportById(reportId int, prePublishTime string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE english_report SET pre_publish_time=? WHERE id = ? and state = 1 `
	_, err = o.Raw(sql, prePublishTime, reportId).Exec()
	return
}

// DeleteEnglishReportAndChapter 删除报告及章节
func DeleteEnglishReportAndChapter(reportInfo *EnglishReportDetail) (err error) {
	reportId := reportInfo.Id
	if reportInfo.State == 2 {
		err = errors.New("报告已发布，不可删除")
		return
	}
	err = DeleteEnglishReport(reportId)
	if err != nil {
		err = errors.New("删除失败, Err: " + err.Error())
		return
	}

	return
}

// 删除报告
func DeleteEnglishReport(reportIds int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` DELETE FROM english_report WHERE id =? `
	_, err = o.Raw(sql, reportIds).Exec()
	return
}

func EditEnglishReportContent(reportId int, content, contentSub string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` UPDATE english_report SET content=?,content_sub=?,modify_time=NOW() WHERE id=? `
	_, err = o.Raw(sql, content, contentSub, reportId).Exec()
	return
}

func AddEnglishReportSaveLog(reportId, adminId int, content, contentSub, adminName string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` INSERT INTO english_report_save_log(report_id, content,content_sub,admin_id,admin_name) VALUES (?,?,?,?,?) `
	_, err = o.Raw(sql, reportId, content, contentSub, adminId, adminName).Exec()
	return
}

type EnglishClassifyList struct {
	Id            int       `orm:"column(id);pk"`
	ClassifyName  string    `description:"分类名称"`
	Sort          int       `description:"排序"`
	ParentId      int       `description:"父级分类id"`
	RootId        int       `description:"一级分类ID"`
	CreateTime    time.Time `description:"创建时间"`
	ModifyTime    time.Time `description:"修改时间"`
	ClassifyLabel string    `description:"分类标签"`
	ShowType      int       `description:"展示类型：1-列表 2-专栏"`
	IsShow        int       `description:"是否在小程序显示：1-显示 0-隐藏"`
	//ClassifyType  int       `description:"分类类型：0英文报告，1英文线上路演"`
	EnPermissions  []int `description:"英文权限IDs"`
	Enabled        int   `description:"是否可用，1可用，0禁用"`
	IsEnableDelete int   `description:"是否允许删除: 1-允许 0-不允许"`
	Child          []*EnglishClassifyList
}

type EnglishClassifyListResp struct {
	List []*EnglishClassifyList
}

// GetEnglishClassifyRootId 获取一级分类列表
func GetEnglishClassifyRootId(keyword string, enabled int) (items []*EnglishClassifyList, err error) {
	sql := ``
	o := orm.NewOrmUsingDB("rddp")
	cond := ""
	if enabled == 1 {
		cond = " AND enabled=1 "
	}
	if keyword != "" {
		sql = `SELECT * FROM (
			                   SELECT * FROM english_classify
                   WHERE parent_id=0 ` + cond + ` AND classify_name LIKE ? 
                   UNION
                   SELECT * FROM english_classify
                   WHERE id IN(SELECT parent_id FROM english_classify
                   WHERE parent_id>0 ` + cond + ` AND classify_name LIKE ? )
                   )AS t
                   ORDER BY sort ASC,create_time ASC`
		_, err = o.Raw(sql, utils.GetLikeKeyword(keyword), utils.GetLikeKeyword(keyword)).QueryRows(&items)
	} else {
		sql = `SELECT * FROM english_classify WHERE parent_id=0 ` + cond + ` ORDER BY sort ASC,create_time ASC `
		_, err = o.Raw(sql).QueryRows(&items)
	}
	return
}

func GetEnglishClassifyListByRootId(rootIds []int, keyword string, enabled int) (items []*EnglishClassifyList, err error) {
	sql := ``
	pars := make([]interface{}, 0)

	o := orm.NewOrmUsingDB("rddp")
	cond := ""
	if enabled == 1 {
		cond = " AND enabled=1 "
	}
	if keyword != "" {
		sql = `SELECT
	a.*
FROM
	english_classify a
	LEFT JOIN english_classify b ON a.root_id = b.id
	LEFT JOIN english_classify c ON a.parent_id = c.id
	WHERE a.parent_id>0  ` + cond + ` and a.classify_name LIKE ? and a.root_id IN (` + utils.GetOrmInReplace(len(rootIds)) + `)`
		pars = append(pars, utils.GetLikeKeyword(keyword))
		pars = append(pars, rootIds)
		_, err = o.Raw(sql, pars).QueryRows(&items)
	} else {
		sql = `SELECT * FROM english_classify WHERE parent_id>0  ` + cond + ` and root_id IN (` + utils.GetOrmInReplace(len(rootIds)) + `) `
		_, err = o.Raw(sql, rootIds).QueryRows(&items)
	}
	return
}

func GetEnglishClassifyChildByIds(ids []int) (items []*EnglishClassifyList, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * FROM english_classify WHERE id IN (` + utils.GetOrmInReplace(len(ids)) + `) ORDER BY create_time ASC `
	_, err = o.Raw(sql, ids).QueryRows(&items)
	return
}

func GetEnglishReportDetailByClassifyId(classifyIdFirst, classifyIdSecond int) (item *EnglishReportDetail, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` SELECT * FROM english_report WHERE 1=1 `
	if classifyIdSecond > 0 {
		sql = sql + ` AND classify_id_second=?   ORDER BY stage DESC LIMIT 1`
		err = o.Raw(sql, classifyIdSecond).QueryRow(&item)
	} else {
		sql = sql + ` AND classify_id_first=?   ORDER BY stage DESC LIMIT 1`
		err = o.Raw(sql, classifyIdFirst).QueryRow(&item)
	}
	return
}

// Update 更新
func (item *EnglishReport) Update(cols []string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	_, err = o.Update(item, cols...)
	return
}

// ModifyEnglishReportAuthor 更改英文报告作者
func ModifyEnglishReportAuthor(condition string, pars []interface{}, authorName string) (count int, err error) {
	//产品权限
	oRddp := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE english_report set author = ? WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	err = oRddp.Raw(sql, authorName, pars).QueryRow(&count)
	return
}

type EnglishClassify struct {
	Id            int       `orm:"column(id);pk"`
	ClassifyName  string    `description:"分类名称"`
	Sort          int       `description:"排序"`
	ParentId      int       `description:"父级分类id"`
	RootId        int       `description:"一级分类ID"`
	CreateTime    time.Time `description:"创建时间"`
	ModifyTime    time.Time `description:"修改时间"`
	ClassifyLabel string    `description:"分类标签"`
	ShowType      int       `description:"展示类型：1-列表 2-专栏"`
	IsShow        int       `description:"是否在小程序显示：1-显示 0-隐藏"`
	//ClassifyType  int       `description:"分类类型：0英文报告，1英文线上路演"`
	Enabled int `description:"是否可用，1可用，0禁用"`
}

func (e *EnglishClassify) Delete() (err error) {
	tx, err := orm.NewOrmUsingDB("rddp").Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			tx.Rollback()
		} else {
			tx.Commit()
		}
	}()
	_, err = tx.Delete(e)
	if err != nil {
		return
	}
	if e.Id == e.RootId {
		// 删除一级分类时，删除其下所有子分类
		sql := `DELETE FROM english_classify WHERE root_id = ?`
		_, err = tx.Raw(sql, e.Id).Exec()
		if err != nil {
			return
		}
	}
	if e.ParentId == e.RootId {
		// 删除二级分类时，更新其父级分类的子分类数量
		sql := `DELETE FROM english_classify WHERE id = ? OR parent_id = ?`
		_, err = tx.Raw(sql, e.Id, e.ParentId).Exec()
		if err != nil {
			return
		}
	}
	return
}

func AddEnglishClassify(item *EnglishClassify) (lastId int64, err error) {
	o := orm.NewOrmUsingDB("rddp")
	lastId, err = o.Insert(item)
	return
}

func ModifyEnglishClassify(item *EnglishClassify) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE english_classify
	SET
	classify_name = ?,
	sort = ?,
	parent_id = ?,
	root_id = ?,
	modify_time = ? 
	WHERE id = ? `
	_, err = o.Raw(sql, item.ClassifyName, item.Sort, item.ParentId, item.RootId, item.ModifyTime, item.Id).Exec()
	return
}

// UpdateEnglishClassifyRootIdByParentId 更新报告分类的顶级ID
func UpdateEnglishClassifyRootIdByParentId(parentId, rootId int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := " UPDATE english_classify SET root_id = ? WHERE parent_id=? "
	_, err = o.Raw(sql, rootId, parentId).Exec()
	return
}

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

	return
}

// DeleteEnglishClassify 删除英文分类
func DeleteEnglishClassify(classifyId int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` DELETE FROM english_classify WHERE id =? `
	_, err = o.Raw(sql, classifyId).Exec()
	return
}

func GetEnglishClassifyChildCounts(parentId int) (count int, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT COUNT(1) AS count FROM english_classify WHERE parent_id=? `
	err = o.Raw(sql, parentId).QueryRow(&count)
	return
}

func GetEnglishReportCounts(classifyId, parentId int) (count int, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ``
	if parentId == 0 {
		sql = `SELECT COUNT(1) AS count FROM english_report WHERE classify_id_first=? `
	} else {
		sql = `SELECT COUNT(1) AS count FROM english_report WHERE classify_id_second=? `
	}
	err = o.Raw(sql, classifyId).QueryRow(&count)
	return
}

func GetEnglishClassifyCountsByName(name string, parentId int) (count int, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT COUNT(1) AS count FROM english_classify WHERE classify_name=? AND parent_id = ? `
	err = o.Raw(sql, name, parentId).QueryRow(&count)
	return
}

// GetEnglishFirstClassifyList 获取一级、二级分类列表
func GetEnglishFirstClassifyList() (items []*EnglishClassifyList, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * FROM english_classify WHERE parent_id=0  ORDER BY sort ASC,create_time`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// GetEnglishSecondClassifyList 获取一级、二级分类列表
func GetEnglishSecondClassifyList(rootIds []int) (items []*EnglishClassifyList, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * FROM english_classify WHERE root_id IN (` + utils.GetOrmInReplace(len(rootIds)) + `) and parent_id>0 and root_id=parent_id ORDER BY sort ASC,create_time ASC`
	_, err = o.Raw(sql, rootIds).QueryRows(&items)
	return
}

func GetEnglishFirstClassifyListCount() (count int, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sqlCount := `SELECT COUNT(1) AS count FROM english_classify WHERE parent_id=0`
	err = o.Raw(sqlCount).QueryRow(&count)
	return
}

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

func GetEnglishReportClassifyByIds(classifyIds []int) (list []*EnglishClassify, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * FROM english_classify WHERE id IN (` + utils.GetOrmInReplace(len(classifyIds)) + `)`
	_, err = o.Raw(sql, classifyIds).QueryRows(&list)
	return
}

// UpdateEnglishReportSecondClassifyNameByClassifyId 更新报告分类名称字段
func UpdateEnglishReportSecondClassifyNameByClassifyId(classifyId int, classifyName string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := " UPDATE english_report SET classify_name_second = ? WHERE classify_id_second = ? "
	_, err = o.Raw(sql, classifyName, classifyId).Exec()
	return
}

// UpdateEnglishReportFirstClassifyNameByClassifyId 更新报告分类名称字段
func UpdateEnglishReportFirstClassifyNameByClassifyId(classifyId int, classifyName string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := " UPDATE english_report SET classify_name_first = ? WHERE classify_id_first = ? "
	_, err = o.Raw(sql, classifyName, classifyId).Exec()
	return
}

// UpdateEnglishReportFirstClassifyNameByClassifyId 更新报告分类名称字段
func UpdateEnglishReportByClassifyId(classifyFirstName, classifySecondName string, firstClassifyId, secondClassifyId int, ids string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := " UPDATE english_report SET classify_name_first = ?,classify_name_second = ?,classify_id_first=?, classify_id_second =?  WHERE id IN (" + ids + ") "
	_, err = o.Raw(sql, classifyFirstName, classifySecondName, firstClassifyId, secondClassifyId).Exec()
	return
}

func UpdateEnglishReportClassifyByFirstSecondClassifyId(classifyId, parentId int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	// 更新相关联的二级分类的parentId,和classify_name_second
	sql := `update english_report r
LEFT JOIN english_classify c ON r.classify_id_second = c.id
SET r.classify_id_first=c.parent_id, r.classify_name_second=c.classify_name
where (r.classify_id_first != c.parent_id or r.classify_name_second != c.classify_name) and r.classify_id_second =?`
	_, err = o.Raw(sql, classifyId).Exec()
	if err != nil {
		return
	}
	//更新一级分类名
	sql = `update english_report r
    LEFT JOIN english_classify c ON r.classify_id_first = c.id
    SET r.classify_name_first=c.classify_name
where r.classify_name_first	!= c.classify_name and r.classify_id_first=?`
	_, err = o.Raw(sql, parentId).Exec()
	if err != nil {
		return
	}
	//更新一级分类名
	sql = `update english_report r
    LEFT JOIN english_classify c ON r.classify_id_first = c.id
    SET r.classify_name_first=c.classify_name
where r.classify_name_first	!= c.classify_name and r.classify_id_first=?`
	_, err = o.Raw(sql, classifyId).Exec()
	return
}

// FetchEnglishReportById 主键获取英文报告
func FetchEnglishReportById(reportId int) (item *EnglishReport, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * FROM english_report WHERE id=?`
	err = o.Raw(sql, reportId).QueryRow(&item)
	return
}

// UpdateReport 更新英文报告
func (reportInfo *EnglishReport) UpdateReport(cols []string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	_, err = o.Update(reportInfo, cols...)

	return
}

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

// GetEnglishReportByIds 根据IDs获取英文报告列表
func GetEnglishReportByIds(reportIds []int, fieldArr []string) (list []*EnglishReport, err error) {
	listLen := len(reportIds)
	if listLen == 0 {
		return
	}
	fields := ` * `
	if len(fieldArr) > 0 {
		fields = strings.Join(fieldArr, ",")
	}
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT ` + fields + ` FROM english_report WHERE id IN (` + utils.GetOrmInReplace(listLen) + `)`
	_, err = o.Raw(sql, reportIds).QueryRows(&list)
	return
}

// MarkEditEnReport 标记编辑英文研报的请求数据
type MarkEditEnReport struct {
	ReportId int `description:"研报id"`
	Status   int `description:"标记状态，1：编辑中，2：编辑完成"`
}

type EnglishClassifyNameParentName struct {
	Id                 int       `description:"分类ID"`
	ClassifyName       string    `description:"分类名称"`
	Sort               int       `description:"排序"`
	ParentId           int       `description:"父级分类id"`
	CreateTime         time.Time `description:"创建时间"`
	ModifyTime         time.Time `description:"修改时间"`
	ClassifyLabel      string    `description:"分类标签"`
	ShowType           int       `description:"展示类型：1-列表 2-专栏"`
	IsShow             int       `description:"是否在小程序显示：1-显示 0-隐藏"`
	ParentClassifyName string    `description:"父级分类名称"`
}

func GetEnglishClassifyByClassifyNameAndParentName(parentClassiyName, classifyName string) (item EnglishClassifyNameParentName, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := "SELECT c1.*, c2.classify_name as parent_classify_name FROM english_classify c1 LEFT JOIN english_classify c2 on c1.parent_id = c2.id where c1.parent_id > 0 and c2.classify_name = ? and c1.classify_name= ?"
	err = o.Raw(sql, parentClassiyName, classifyName).QueryRow(&item)
	return
}

type RSClassifyList []*EnglishClassifyList

func (m RSClassifyList) Len() int {
	return len(m)
}

func (m RSClassifyList) Less(i, j int) bool {
	return m[i].Sort < m[j].Sort
}

func (m RSClassifyList) Swap(i, j int) {
	m[i], m[j] = m[j], m[i]
}

type RSChildClassifyList []*EnglishClassifyList

func (m RSChildClassifyList) Len() int {
	return len(m)
}

func (m RSChildClassifyList) Less(i, j int) bool {
	return m[i].Sort < m[j].Sort
}

func (m RSChildClassifyList) Swap(i, j int) {
	m[i], m[j] = m[j], m[i]
}

// GetEnglishClassifyByClassifyNameParentId 获取英文分类
func GetEnglishClassifyByClassifyNameParentId(classifyName string, parentId int) (item *Classify, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` SELECT * FROM english_classify where classify_name = ? and parent_id = ? `
	err = o.Raw(sql, classifyName, parentId).QueryRow(&item)
	return
}

type EnglishClassifyFullName struct {
	Id           int    `description:"分类ID"`
	ParentId     int    `description:"父级分类id"`
	RootId       int    `description:"一级分类ID"`
	RootName     string `description:"一级分类名"`
	ParentName   string `description:"二级分类名"`
	ClassifyName string `description:"分类名称"`
}

// GetEnglishClassifyFullNameByIds 获取英文分类名一级/二级/三级
func GetEnglishClassifyFullNameByIds(classifyIds []int) (list []*EnglishClassifyFullName, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` SELECT
	a.id,
	a.parent_id,
	a.root_id,
	a.classify_name,
	b.classify_name AS root_name,
	c.classify_name AS parent_name 
FROM
	english_classify a
	LEFT JOIN english_classify b ON a.root_id = b.id
	LEFT JOIN english_classify c ON a.parent_id = c.id 
where a.id IN (` + utils.GetOrmInReplace(len(classifyIds)) + `)`
	_, err = o.Raw(sql, classifyIds).QueryRows(&list)
	return
}

func (m *EnglishReport) GetItemById(id int) (item *EnglishReport, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * FROM english_report WHERE id = ? LIMIT 1`
	err = o.Raw(sql, id).QueryRow(&item)
	return
}

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

// GetEnglishReportStateCount 获取指定状态的报告数量
func GetEnglishReportStateCount(state int) (count int, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT COUNT(1) AS count FROM english_report WHERE state = ?`
	err = o.Raw(sql, state).QueryRow(&count)
	return
}

// UpdateEnglishReportsStateByCond 批量更新报告状态
func UpdateEnglishReportsStateByCond(classifyFirstId, classifySecondId, oldState, newState int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	cond := ``
	if classifyFirstId > 0 {
		cond += fmt.Sprintf(` AND classify_id_first = %d`, classifyFirstId)
	}
	if classifySecondId > 0 {
		cond += fmt.Sprintf(` AND classify_id_second = %d`, classifySecondId)
	}
	sql := fmt.Sprintf(`UPDATE english_report SET state = ?, pre_publish_time = NULL WHERE state = ? %s`, cond)
	_, err = o.Raw(sql, newState, oldState).Exec()
	return
}

// UpdateEnglishReportsStateBySecondIds 批量更新二级分类报告状态
func UpdateEnglishReportsStateBySecondIds(oldState, newState int, secondIds []int) (err error) {
	if len(secondIds) <= 0 {
		return
	}
	o := orm.NewOrmUsingDB("rddp")
	// (有审批流的)未发布->待提交
	sql := fmt.Sprintf(`UPDATE english_report SET state = ?, pre_publish_time = NULL WHERE state = ? AND classify_id_second IN (%s)`, utils.GetOrmInReplace(len(secondIds)))
	_, err = o.Raw(sql, newState, oldState, secondIds).Exec()
	if err != nil {
		return
	}
	// (无审批流的)待提交->未发布
	sql = fmt.Sprintf(`UPDATE english_report SET state = ?, pre_publish_time = NULL WHERE state = ? AND classify_id_second NOT IN (%s)`, utils.GetOrmInReplace(len(secondIds)))
	_, err = o.Raw(sql, oldState, newState, secondIds).Exec()
	return
}

type EnglishClassifySetEnabledReq struct {
	ClassifyId int `description:"分类ID"`
	Enabled    int `description:"是否可用，1可用，0禁用"`
}

func (classifyInfo *EnglishClassify) SetEnabled(id, enabled int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()
	sql := ` UPDATE english_classify SET enabled =?  WHERE id = ?`
	_, err = to.Raw(sql, enabled, id).Exec()
	if err != nil {
		return
	}
	sql = ` UPDATE english_classify SET enabled =?  WHERE parent_id = ? or root_id = ?`
	_, err = to.Raw(sql, enabled, id, id).Exec()
	if err != nil {
		return
	}
	return
}

func ModifyEnglishReportPdfUrl(reportId int, detailPdfUrl string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE english_report SET detail_pdf_url=? WHERE id=? `
	_, err = o.Raw(sql, detailPdfUrl, reportId).Exec()
	return
}

func ModifyEnglishReportImgUrl(reportId int, detailImgUrl string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE english_report SET detail_img_url=? WHERE id=? `
	_, err = o.Raw(sql, detailImgUrl, reportId).Exec()
	return
}

func ModifyEnglishReportPdfUrlMobile(reportId int, detailPdfUrlMobile string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE english_report SET detail_pdf_url_mobile=? WHERE id=? `
	_, err = o.Raw(sql, detailPdfUrlMobile, reportId).Exec()
	return
}

func ModifyEnglishReportImgUrlMobile(reportId int, detailImgUrlMobile string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE english_report SET detail_img_url_mobile=? WHERE id=? `
	_, err = o.Raw(sql, detailImgUrlMobile, reportId).Exec()
	return
}

func FormatEnglishReport2ListItem(origin *EnglishReport) (item *EnglishReportList) {
	if origin == nil {
		return
	}
	item = new(EnglishReportList)
	item.Id = origin.Id
	item.AddType = origin.AddType
	item.ClassifyIdFirst = origin.ClassifyIdFirst
	item.ClassifyNameFirst = origin.ClassifyNameFirst
	item.ClassifyIdSecond = origin.ClassifyIdSecond
	item.ClassifyNameSecond = origin.ClassifyNameSecond
	item.Title = origin.Title
	item.Abstract = origin.Abstract
	item.Author = origin.Author
	item.Frequency = origin.Frequency
	item.CreateTime = origin.CreateTime
	item.ModifyTime = origin.ModifyTime
	item.State = origin.State
	item.PublishTime = utils.TimeTransferString(utils.FormatDateTime, origin.PublishTime)
	item.PrePublishTime = utils.TimeTransferString(utils.FormatDateTime, origin.PrePublishTime)
	item.Stage = origin.Stage
	//item.Content = origin.Content
	item.VideoUrl = origin.VideoUrl
	item.VideoName = origin.VideoName
	item.VideoPlaySeconds = origin.VideoPlaySeconds
	item.ContentSub = origin.ContentSub
	item.ReportCode = origin.ReportCode
	item.Pv = origin.Pv
	item.PvEmail = origin.PvEmail
	item.UvEmail = origin.UvEmail
	// 邮箱PV大于0的时候, 不展示最初版本的PV
	if item.PvEmail > 0 {
		item.Pv = 0
	}
	item.EmailState = origin.EmailState
	if origin.EmailHasFail == 1 {
		item.EmailHasFail = true
	}
	item.FromReportId = origin.FromReportId
	item.AdminId = origin.AdminId
	item.AdminRealName = origin.AdminRealName
	item.ApproveTime = utils.TimeTransferString(utils.FormatDateTime, origin.ApproveTime)
	item.DetailImgUrl = origin.DetailImgUrl
	item.DetailPdfUrl = origin.DetailPdfUrl
	item.DetailImgUrlMobile = origin.DetailImgUrlMobile
	item.DetailPdfUrlMobile = origin.DetailPdfUrlMobile
	return
}

// UpdateEnglishReportEmailHasFail 标记报告邮件发送失败
func UpdateEnglishReportEmailHasFail(reportId int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE english_report SET email_has_fail = 1 WHERE id = ?`
	_, err = o.Raw(sql, reportId).Exec()
	return
}

// UpdatePdfUrlEnglishReportById 清空pdf相关字段
func UpdatePdfUrlEnglishReportById(reportId int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE english_report SET detail_img_url = '',detail_pdf_url='',modify_time=NOW() WHERE id = ? `
	_, err = o.Raw(sql, reportId).Exec()
	return
}

func GetEnglishReportFieldsByIds(ids []int, fields []string) (items []*EnglishReport, err error) {
	if len(ids) == 0 {
		return
	}
	o := orm.NewOrmUsingDB("rddp")
	field := " * "
	if len(fields) > 0 {
		field = fmt.Sprintf(" %s ", strings.Join(fields, ","))
	}
	sql := fmt.Sprintf(`SELECT %s FROM english_report WHERE id IN (%s)`, field, utils.GetOrmInReplace(len(ids)))
	_, err = o.Raw(sql, ids).QueryRows(&items)
	return
}

func GetExistEnglishReportClassifyIdByClassifyIds(classifyIds []int) (existClassifyIds []*EnglishReport, err error) {
	if len(classifyIds) == 0 {
		return
	}
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT classify_id_first, classify_id_second FROM english_report WHERE 1=1 `
	sql += fmt.Sprintf(` AND (classify_id_first IN (%s) OR classify_id_second IN (%s))`, utils.GetOrmInReplace(len(classifyIds)), utils.GetOrmInReplace(len(classifyIds)))

	_, err = o.Raw(sql, classifyIds, classifyIds).QueryRows(&existClassifyIds)
	return
}