eta_api/models/speech_recognition/speech_recognition.go

package speech_recognition

import (
	"eta_gn/eta_api/global"
	"eta_gn/eta_api/utils"
	"fmt"
	"github.com/rdlucklib/rdluck_tools/paging"
	"strings"
	"time"
)

const (
	SpeechRecognitionFileRemoveFlag = 1 // 文件删除标记

	SpeechRecognitionStateWait    = 1
	SpeechRecognitionStateSuccess = 2
	SpeechRecognitionStateFail    = 3
)

type SpeechRecognition struct {
	SpeechRecognitionId int       `gorm:"primaryKey;column:speech_recognition_id;type:int(10) unsigned;not null"`    // 语音识别Id
	UniqueCode          string    `gorm:"column:unique_code;type:varchar(64);not null;default:''"`                   // 唯一编码
	FileName            string    `gorm:"column:file_name;type:varchar(128);not null;default:''"`                    // 文件名称
	ResourceUrl         string    `gorm:"column:resource_url;type:varchar(255);not null;default:''"`                 // 文件路径
	MenuId              int       `gorm:"index:idx_menu_id;column:menu_id;type:int(10) unsigned;not null;default:0"` // 目录Id
	SysUserId           int       `gorm:"column:sys_user_id;type:int(10) unsigned;not null;default:0"`               // 创建人Id
	SysUserName         string    `gorm:"column:sys_user_name;type:varchar(128);not null;default:''"`                // 创建人姓名
	State               int       `gorm:"column:state;type:tinyint(4) unsigned;not null;default:0"`                  // 状态：1-待转换；2-转换完成；3-转换失败
	Abstract            string    `gorm:"column:abstract;type:text"`                                                 // 摘要，取前几段内容
	Sort                int       `gorm:"column:sort;type:int(10) unsigned;not null;default:0"`                      // 目录下的排序
	FileState           int       `gorm:"column:file_state;type:tinyint(4) unsigned;not null;default:0"`             // 文件删除状态：0-正常；1-删除（该字段作为软删标识）
	FileSecond          int       `gorm:"column:file_second;type:int(10) unsigned;not null;default:0"`               // 文件时长（秒）
	FileSize            int       `gorm:"column:file_size;type:int(10) unsigned;not null;default:0"`                 // 文件大小（byte）
	ConvertRemark       string    `gorm:"column:convert_remark;type:varchar(255);not null;default:''"`               // 转写备注-失败原因
	CreateTime          time.Time `gorm:"column:create_time;type:datetime"`                                          // 创建时间
	ModifyTime          time.Time `gorm:"column:modify_time;type:datetime"`                                          // 更新时间
}

var SpeechRecognitionCols = struct {
	SpeechRecognitionId string
	UniqueCode          string
	FileName            string
	ResourceUrl         string
	MenuId              string
	MenuPath            string
	SysUserId           string
	SysUserName         string
	State               string
	Abstract            string
	Sort                string
	FileState           string
	FileSecond          string
	FileSize            string
	ConvertRemark       string
	CreateTime          string
	ModifyTime          string
}{
	SpeechRecognitionId: "speech_recognition_id",
	UniqueCode:          "unique_code",
	FileName:            "file_name",
	ResourceUrl:         "resource_url",
	MenuId:              "menu_id",
	MenuPath:            "menu_path",
	SysUserId:           "sys_user_id",
	SysUserName:         "sys_user_name",
	State:               "state",
	Abstract:            "abstract",
	Sort:                "sort",
	FileState:           "file_state",
	FileSecond:          "file_second",
	FileSize:            "file_size",
	ConvertRemark:       "convert_remark",
	CreateTime:          "create_time",
	ModifyTime:          "modify_time",
}

func (m *SpeechRecognition) TableName() string {
	return "speech_recognition"
}

func (m *SpeechRecognition) PrimaryId() string {
	return SpeechRecognitionCols.SpeechRecognitionId
}

func (m *SpeechRecognition) Create() (err error) {
	err = global.DEFAULT_DmSQL.Create(m).Error
	return
}

func (m *SpeechRecognition) CreateMulti(items []*SpeechRecognition) (err error) {
	if len(items) == 0 {
		return
	}
	err = global.DEFAULT_DmSQL.CreateInBatches(items, utils.MultiAddNum).Error
	return
}

func (m *SpeechRecognition) Update(cols []string) (err error) {
	err = global.DEFAULT_DmSQL.Select(cols).Updates(m).Error
	return
}

func (m *SpeechRecognition) Del() (err error) {
	sql := fmt.Sprintf(`DELETE FROM %s WHERE %s = ? LIMIT 1`, m.TableName(), m.PrimaryId())
	err = global.DEFAULT_DmSQL.Exec(sql, m.SpeechRecognitionId).Error
	return
}

func (m *SpeechRecognition) MultiDel(menuIds []int) (err error) {
	if len(menuIds) == 0 {
		return
	}
	sql := fmt.Sprintf(`DELETE FROM %s WHERE %s IN (%s)`, m.TableName(), m.PrimaryId(), utils.GetOrmInReplace(len(menuIds)))
	err = global.DEFAULT_DmSQL.Exec(sql, menuIds).Error
	return
}

func (m *SpeechRecognition) GetItemById(id int) (item *SpeechRecognition, err error) {
	sql := fmt.Sprintf(`SELECT * FROM %s WHERE %s = ? LIMIT 1`, m.TableName(), m.PrimaryId())
	err = global.DEFAULT_DmSQL.Raw(sql, id).First(&item).Error
	return
}

func (m *SpeechRecognition) GetItemByCondition(condition string, pars []interface{}, orderRule string) (item *SpeechRecognition, err error) {
	order := ``
	if orderRule != "" {
		order = ` ORDER BY ` + orderRule
	}
	sql := fmt.Sprintf(`SELECT * FROM %s WHERE 1=1 %s %s LIMIT 1`, m.TableName(), condition, order)
	err = global.DEFAULT_DmSQL.Raw(sql, pars...).First(&item).Error
	return
}

func (m *SpeechRecognition) GetCountByCondition(condition string, pars []interface{}) (count int, err error) {
	sql := fmt.Sprintf(`SELECT COUNT(1) FROM %s WHERE 1=1 %s`, m.TableName(), condition)
	err = global.DEFAULT_DmSQL.Raw(sql, pars...).Scan(&count).Error
	return
}

func (m *SpeechRecognition) GetItemsByCondition(condition string, pars []interface{}, fieldArr []string, orderRule string) (items []*SpeechRecognition, err error) {
	fields := strings.Join(fieldArr, ",")
	if len(fieldArr) == 0 {
		fields = `*`
	}
	order := `ORDER BY create_time DESC`
	if orderRule != "" {
		order = ` ORDER BY ` + orderRule
	}
	sql := fmt.Sprintf(`SELECT %s FROM %s WHERE 1=1 %s %s`, fields, m.TableName(), condition, order)
	err = global.DEFAULT_DmSQL.Raw(sql, pars...).Find(&items).Error
	return
}

func (m *SpeechRecognition) GetPageItemsByCondition(condition string, pars []interface{}, fieldArr []string, orderRule string, startSize, pageSize int) (items []*SpeechRecognition, err error) {
	fields := strings.Join(fieldArr, ",")
	if len(fieldArr) == 0 {
		fields = `*`
	}
	order := `ORDER BY create_time DESC`
	if orderRule != "" {
		order = ` ORDER BY ` + orderRule
	}
	sql := fmt.Sprintf(`SELECT %s FROM %s WHERE 1=1 %s %s LIMIT ?,?`, fields, m.TableName(), condition, order)
	pars = append(pars, startSize)
	pars = append(pars, pageSize)
	err = global.DEFAULT_DmSQL.Raw(sql, pars...).Find(&items).Error
	return
}

// SpeechRecognitionItem 语音识别信息
type SpeechRecognitionItem struct {
	SpeechRecognitionId int
	UniqueCode          string `description:"唯一编码"`
	FileName            string `description:"文件名称"`
	ResourceUrl         string `description:"文件路径"`
	MenuId              int    `description:"目录ID"`
	SysUserId           int    `description:"创建人ID"`
	SysUserName         string `description:"创建人姓名"`
	State               int    `description:"状态：1-待转换；2-转换完成；3-转换失败"`
	Abstract            string `description:"摘要，取前几段内容"`
	Sort                int    `description:"目录下的排序"`
	ConvertRemark       string `description:"转写备注-失败原因"`
	CreateTime          string `description:"创建时间"`
	ModifyTime          string `description:"修改时间"`
}

func FormatSpeechRecognition2Item(origin *SpeechRecognition) (item *SpeechRecognitionItem) {
	if origin == nil {
		return
	}
	item = new(SpeechRecognitionItem)
	item.SpeechRecognitionId = origin.SpeechRecognitionId
	item.UniqueCode = origin.UniqueCode
	item.FileName = origin.FileName
	item.ResourceUrl = origin.ResourceUrl
	if origin.FileState == SpeechRecognitionFileRemoveFlag {
		item.ResourceUrl = ""
	}
	item.MenuId = origin.MenuId
	item.SysUserId = origin.SysUserId
	item.SysUserName = origin.SysUserName
	item.State = origin.State
	item.Abstract = origin.Abstract
	item.Sort = origin.Sort
	item.ConvertRemark = origin.ConvertRemark
	item.CreateTime = utils.TimeTransferString(utils.FormatDateTime, origin.CreateTime)
	item.ModifyTime = utils.TimeTransferString(utils.FormatDateTime, origin.ModifyTime)
	return
}

// SpeechRecognitionSaveReq 保存请求体
type SpeechRecognitionSaveReq struct {
	SpeechRecognitionId int                               `description:"语音识别ID"`
	FileName            string                            `description:"文件名称"`
	TagIds              []int                             `description:"标签IDs"`
	Contents            []SpeechRecognitionSaveContentReq `description:"保存内容"`
}

// SpeechRecognitionSaveContentReq 保存内容
type SpeechRecognitionSaveContentReq struct {
	SpeechRecognitionContentId int    `description:"语音识别内容ID"`
	Content                    string `description:"段落内容"`
}

// SpeechRecognitionRenameReq 重命名
type SpeechRecognitionRenameReq struct {
	SpeechRecognitionId int    `description:"语音识别ID"`
	FileName            string `description:"文件名称"`
}

// SpeechRecognitionRemoveReq 删除
type SpeechRecognitionRemoveReq struct {
	SpeechRecognitionId int `description:"语音识别ID"`
}

// SpeechRecognitionRemoveFileReq 删除文件
type SpeechRecognitionRemoveFileReq struct {
	SpeechRecognitionId int `description:"语音识别ID"`
}

// SpeechRecognitionSaveTagReq 保存标签
type SpeechRecognitionSaveTagReq struct {
	SpeechRecognitionId int   `description:"语音识别ID"`
	TagIds              []int `description:"标签IDs"`
}

// SpeechRecognitionConvertReq 批量转写
type SpeechRecognitionConvertReq struct {
	MenuId int                             `description:"目录ID"`
	Files  []SpeechRecognitionConvertFiles `description:"转写文件"`
}

// SpeechRecognitionConvertFiles 批量转写文件
type SpeechRecognitionConvertFiles struct {
	FileName    string `description:"文件名称"`
	ResourceUrl string `description:"文件地址"`
	FileSecond  int    `description:"文件时长(秒)"`
	FileSize    int    `description:"文件大小(byte)"`
}

// UpdateSpeechAndApiLog 更新语音识别及API记录
func UpdateSpeechAndApiLog(speechItem *SpeechRecognition, speechCols []string, apiLogItem *SpeechRecognitionApiLog, logCols []string) (err error) {
	if speechItem == nil || apiLogItem == nil {
		err = fmt.Errorf("speechItem nil or apiLogItem nil")
		return
	}
	tx := global.DEFAULT_DmSQL.Begin()
	defer func() {
		if err != nil {
			_ = tx.Rollback()
			return
		}
		_ = tx.Commit()
	}()

	e := tx.Select(speechCols).Updates(speechItem).Error
	if e != nil {
		err = fmt.Errorf("update speech err: %v", e)
		return
	}
	e = tx.Select(logCols).Updates(apiLogItem).Error
	if e != nil {
		err = fmt.Errorf("update api err: %v", e)
		return
	}
	return
}

// CreateContentAndUpdateSpeechAndApiLog 新增语音识别内容并更新语音识别及API记录
func CreateContentAndUpdateSpeechAndApiLog(contents []*SpeechRecognitionContent, speechItem *SpeechRecognition, speechCols []string, apiLogItem *SpeechRecognitionApiLog, logCols []string) (err error) {
	if speechItem == nil || apiLogItem == nil {
		err = fmt.Errorf("speechItem nil or apiLogItem nil")
		return
	}
	tx := global.DEFAULT_DmSQL.Begin()
	defer func() {
		if err != nil {
			_ = tx.Rollback()
			return
		}
		_ = tx.Commit()
	}()

	e := tx.Select(speechCols).Updates(speechItem).Error
	if e != nil {
		err = fmt.Errorf("update speech err: %v", e)
		return
	}
	e = tx.Select(logCols).Updates(apiLogItem).Error
	if e != nil {
		err = fmt.Errorf("update api err: %v", e)
		return
	}
	if len(contents) > 0 {
		e = tx.CreateInBatches(contents, utils.MultiAddNum).Error
		if e != nil {
			err = fmt.Errorf("insert multi contents err: %s", e.Error())
			return
		}
	}
	return
}

// SpeechRecognitionDetailItem 语音识别详情信息
type SpeechRecognitionDetailItem struct {
	SpeechRecognitionId int
	UniqueCode          string                          `description:"唯一编码"`
	FileName            string                          `description:"文件名称"`
	FileExt             string                          `description:"文件后缀名"`
	ResourceUrl         string                          `description:"文件地址"`
	MenuId              int                             `description:"目录ID"`
	MenuPath            []*SpeechRecognitionMenuItem    `description:"目录全路径, 多级并列为一个数组"`
	SysUserId           int                             `description:"创建人ID"`
	SysUserName         string                          `description:"创建人姓名"`
	Abstract            string                          `description:"摘要，取前几段内容"`
	Sort                int                             `description:"目录下的排序"`
	FileSecond          string                          `description:"文件时长(HH:MM:SS/MM:SS)"`
	FileSize            string                          `description:"文件大小(MB)"`
	CreateTime          string                          `description:"创建时间"`
	ModifyTime          string                          `description:"修改时间"`
	Contents            []*SpeechRecognitionContentItem `description:"语音识别内容"`
	Tags                []*SpeechRecognitionDetailTag   `description:"标签"`
}

func FormatSpeechRecognition2DetailItem(origin *SpeechRecognition, contents []*SpeechRecognitionContentItem, tags []*SpeechRecognitionDetailTag, menuPath []*SpeechRecognitionMenuItem) (item *SpeechRecognitionDetailItem) {
	if origin == nil {
		return
	}
	item = new(SpeechRecognitionDetailItem)
	item.SpeechRecognitionId = origin.SpeechRecognitionId
	item.UniqueCode = origin.UniqueCode
	item.FileName = origin.FileName
	item.ResourceUrl = origin.ResourceUrl
	if item.ResourceUrl != "" {
		pointArr := strings.Split(item.ResourceUrl, ".")
		if len(pointArr) > 0 {
			item.FileExt = pointArr[len(pointArr)-1]
		}
	}
	item.ResourceUrl = origin.ResourceUrl
	if origin.FileState == SpeechRecognitionFileRemoveFlag {
		item.ResourceUrl = ""
	}
	item.MenuId = origin.MenuId
	item.MenuPath = menuPath
	item.SysUserId = origin.SysUserId
	item.SysUserName = origin.SysUserName
	item.Abstract = origin.Abstract
	item.Sort = origin.Sort
	item.FileSecond = utils.SecondsToHHMMSS(origin.FileSecond)
	item.FileSize = fmt.Sprintf("%.2fM", utils.ByteToMB(origin.FileSize))
	item.CreateTime = utils.TimeTransferString(utils.FormatDateTime, origin.CreateTime)
	item.ModifyTime = utils.TimeTransferString(utils.FormatDateTime, origin.ModifyTime)
	item.Contents = contents
	item.Tags = tags
	return
}

// SpeechRecognitionDetailTag 语音识别详情标签
type SpeechRecognitionDetailTag struct {
	TagId   int    `description:"标签ID" gorm:"tag_id"`
	TagName string `description:"标签名称" gorm:"tag_name"`
}

// GetSpeechRecognitionTagBySpeechId 获取语音识别标签
func GetSpeechRecognitionTagBySpeechId(speechId int) (items []*SpeechRecognitionDetailTag, err error) {
	sql := `SELECT a.speech_recognition_tag_id AS tag_id, a.tag_name FROM speech_recognition_tag AS a
		JOIN speech_recognition_tag_mapping AS b ON a.speech_recognition_tag_id = b.tag_id
		WHERE b.speech_recognition_id = ?`
	err = global.DEFAULT_DmSQL.Raw(sql, speechId).Find(&items).Error
	return
}

// SpeechRecognitionMappingTags 语音识别标签
type SpeechRecognitionMappingTags struct {
	SpeechRecognitionId int    `description:"语音识别ID" gorm:"speech_recognition_id"`
	TagId               int    `description:"标签ID" gorm:"tag_id"`
	TagName             string `description:"标签名称" gorm:"tag_name"`
}

// GetSpeechRecognitionTagsBySpeechIds 根据语音识别IDs获取标签
func GetSpeechRecognitionTagsBySpeechIds(speechIds []int) (items []*SpeechRecognitionMappingTags, err error) {
	if len(speechIds) == 0 {
		return
	}
	sql := fmt.Sprintf(`SELECT a.speech_recognition_id, a.tag_id, b.tag_name FROM speech_recognition_tag_mapping AS a
		JOIN speech_recognition_tag AS b ON a.tag_id = b.speech_recognition_tag_id
		WHERE a.speech_recognition_id IN (%s)`, utils.GetOrmInReplace(len(speechIds)))
	err = global.DEFAULT_DmSQL.Raw(sql, speechIds).Find(&items).Error
	return
}

// SpeechRecognitionListReq 语音识别列表请求体
type SpeechRecognitionListReq struct {
	PageSize     int    `form:"PageSize"`
	CurrentIndex int    `form:"CurrentIndex"`
	FileName     string `form:"FileName" description:"文件名称"`
	StartTime    string `form:"StartTime" description:"开始时间"`
	EndTime      string `form:"EndTime" description:"结束时间"`
	CreateUserId string `form:"CreateUserId" description:"创建人IDs"`
	TagId        int    `form:"TagId" description:"标签ID"`
	TagIds       string `form:"TagIds" description:"标签IDs, 英文逗号分隔"`
	MenuId       int    `form:"MenuId" description:"目录ID"`
	IsTagMenu    bool   `form:"IsTagMenu" description:"是否为标签目录"`
}

// SpeechRecognitionListResp 语音识别列表响应体
type SpeechRecognitionListResp struct {
	List   []*SpeechRecognitionDetailItem
	Paging *paging.PagingItem `description:"分页数据"`
}

// SpeechRecognitionContentExportReq 导出内容
type SpeechRecognitionContentExportReq struct {
	SpeechRecognitionId int  `description:"语音识别ID"`
	ExportType          int  `description:"导出类型：1-txt；2-doc；3-pdf"`
	Timestamp           bool `description:"是否显示时间戳"`
}

// UpdateSortByMenuId 根据分类ID更新排序
func (m *SpeechRecognition) UpdateSortByMenuId(menuId, nowSort int, prevSpeechId int, updateSort string) (err error) {
	sql := fmt.Sprintf(`UPDATE %s SET %s = %s WHERE %s = ? `, m.TableName(), SpeechRecognitionCols.Sort, updateSort, SpeechRecognitionCols.MenuId)
	if prevSpeechId > 0 {
		sql += fmt.Sprintf(` AND (%s > ? OR (%s > %d AND %s = %d))`, SpeechRecognitionCols.Sort, SpeechRecognitionCols.SpeechRecognitionId, prevSpeechId, SpeechRecognitionCols.Sort, nowSort)
	} else {
		sql += fmt.Sprintf(` AND %s > ?`, SpeechRecognitionCols.Sort)
	}
	err = global.DEFAULT_DmSQL.Exec(sql, menuId, nowSort).Error
	return
}

// GetMaxSortByMenuId 获取分类下最大Sort
func (m *SpeechRecognition) GetMaxSortByMenuId(menuId int) (sort int, err error) {
	sql := fmt.Sprintf(`SELECT COALESCE(MAX(sort),0) AS sort FROM %s WHERE %s = ?`, m.TableName(), SpeechRecognitionCols.MenuId)
	err = global.DEFAULT_DmSQL.Raw(sql, menuId).Scan(&sort).Error
	return
}

// GetFirstByMenuId 获取目录下排序第一的数据
func (m *SpeechRecognition) GetFirstByMenuId(menuId int) (item *SpeechRecognition, err error) {
	sql := fmt.Sprintf(`SELECT * FROM %s WHERE %s = ? ORDER BY %s ASC, %s ASC LIMIT 1`, m.TableName(), SpeechRecognitionCols.MenuId, SpeechRecognitionCols.Sort, SpeechRecognitionCols.SpeechRecognitionId)
	err = global.DEFAULT_DmSQL.Raw(sql, menuId).First(&item).Error
	return
}

// SpeechRecognitionConvertCheckNameReq 校验文件重名请求体
type SpeechRecognitionConvertCheckNameReq struct {
	FileName string `description:"文件名称"`
}

// SpeechSave 更新内容、摘要及标签
func (m *SpeechRecognition) SpeechSave(speechItem *SpeechRecognition, speechCols []string, contents []SpeechRecognitionSaveContentReq, tagMappings []*SpeechRecognitionTagMapping) (err error) {
	if speechItem == nil {
		err = fmt.Errorf("speech nil")
		return
	}
	tx := global.DEFAULT_DmSQL.Begin()
	defer func() {
		if err != nil {
			_ = tx.Rollback()
			return
		}
		_ = tx.Commit()
	}()

	// 转写文件
	if len(speechCols) > 0 {
		e := tx.Select(speechCols).Updates(speechItem).Error
		if e != nil {
			err = fmt.Errorf("update speech err: %s", e.Error())
			return
		}
	}

	// 转写内容
	if len(contents) > 0 {
		sql := fmt.Sprintf(`UPDATE %s SET %s = ?, %s = 1, %s = NOW() WHERE %s = ?`, "speech_recognition_content", SpeechRecognitionContentCols.Content, SpeechRecognitionContentCols.IsUpdate, SpeechRecognitionContentCols.ModifyTime, SpeechRecognitionContentCols.SpeechRecognitionContentId)
		for _, v := range contents {
			e := tx.Exec(sql, v.Content, v.SpeechRecognitionContentId).Error
			if e != nil {
				err = fmt.Errorf("update exec err: %s", e.Error())
				return
			}
		}
	}

	// 标签
	sql := fmt.Sprintf(`DELETE FROM %s WHERE %s = ?`, "speech_recognition_tag_mapping", SpeechRecognitionTagMappingCols.SpeechRecognitionId)
	e := tx.Exec(sql, speechItem.SpeechRecognitionId).Error
	if e != nil {
		err = fmt.Errorf("remove tag mappings err: %s", e.Error())
		return
	}
	if len(tagMappings) > 0 {
		e = tx.CreateInBatches(tagMappings, utils.MultiAddNum).Error
		if e != nil {
			err = fmt.Errorf("insert tag mappings err: %s", e.Error())
			return
		}
	}
	return
}