package excel import ( "eta/eta_api/models/data_manage" "eta/eta_api/utils" "fmt" "github.com/beego/beego/v2/client/orm" "strconv" "time" ) // ExcelInfo excel表格详情表 type ExcelInfo struct { ExcelInfoId int `orm:"column(excel_info_id);pk"` Source int `description:"表格来源，1：excel插件的表格，2：自定义表格，3：混合表格，4：自定义分析，默认：1"` ExcelType int `description:"表格类型，1：指标列，2：日期列，默认：1"` ExcelName string `description:"表格名称"` UniqueCode string `description:"表格唯一编码"` ExcelClassifyId int `description:"表格分类id"` SysUserId int `description:"操作人id"` SysUserRealName string `description:"操作人真实姓名"` Content string `description:"表格内容"` ExcelImage string `description:"表格图片"` FileUrl string `description:"表格下载地址"` Sort int `description:"排序字段，数字越小越排前面"` IsDelete int `description:"是否删除，0：未删除，1：已删除"` ModifyTime time.Time `description:"最近修改日期"` CreateTime time.Time `description:"创建日期"` IsJoinPermission int `description:"是否加入权限管控，0：不加入；1：加入；默认：0"` ParentId int `description:"表格的父级id"` BalanceType int `description:"平衡表类型：0 动态表，1静态表"` UpdateUserId int `description:"更新人id"` UpdateUserRealName string `description:"更新人真实姓名"` RelExcelInfoId int `description:"平衡表里静态表关联的动态表excel id"` VersionName string `description:"静态表版本名称"` SourcesFrom string `description:"图表来源"` } // Update 更新 excel表格基础信息 func (excelInfo *ExcelInfo) Update(cols []string) (err error) { o := orm.NewOrmUsingDB("data") _, err = o.Update(excelInfo, cols...) return } type MyExcelInfoList struct { ExcelInfoId int `orm:"column(excel_info_id);pk"` Source int `description:"表格来源，1：excel插件的表格，2：自定义表格，默认：1"` ExcelType int `description:"表格类型，1：指标列，2：日期列，默认：1"` ExcelName string `description:"表格名称"` UniqueCode string `description:"表格唯一编码"` ExcelClassifyId int `description:"表格分类id"` SysUserId int `description:"操作人id"` SysUserRealName string `description:"操作人真实姓名"` ExcelImage string `description:"表格图片"` FileUrl string `description:"表格下载地址"` Sort int `description:"排序字段，数字越小越排前面"` ModifyTime time.Time `description:"最近修改日期"` CreateTime time.Time `description:"创建日期"` IsJoinPermission int `description:"是否加入权限管控，0：不加入；1：加入；默认：0"` HaveOperaAuth bool `description:"是否有数据权限"` UpdateUserId int `description:"更新人id"` UpdateUserRealName string `description:"更新人真实姓名"` Button ExcelInfoDetailButton `description:"操作权限"` CanEdit bool `description:"是否可编辑"` Editor string `description:"编辑人"` } // AddExcelInfo 新增表格 func AddExcelInfo(excelInfo *ExcelInfo, excelEdbMappingList []*ExcelEdbMapping, childExcel *ExcelInfo) (err error) { o, err := orm.NewOrmUsingDB("data").Begin() if err != nil { return } defer func() { if err != nil { _ = o.Rollback() } else { _ = o.Commit() } }() // 表格信息入库 lastId, err := o.Insert(excelInfo) if err != nil { return } excelInfo.ExcelInfoId = int(lastId) // todo 判断如果是平衡表的父级，则新增一个名叫平衡表的子表, 内容为空 if childExcel != nil { // 表格信息入库 childExcel.ParentId = excelInfo.ExcelInfoId _, err = o.Insert(childExcel) if err != nil { err = fmt.Errorf("新增子表失败：%v", err) return } } // excel与指标的关联关系 dataNum := len(excelEdbMappingList) if dataNum > 0 { for k, v := range excelEdbMappingList { v.ExcelInfoId = excelInfo.ExcelInfoId excelEdbMappingList[k] = v } _, err = o.InsertMulti(dataNum, excelEdbMappingList) } return } // EditExcelInfo 编辑表格 func EditExcelInfo(excelInfo *ExcelInfo, updateExcelInfoParams []string, excelEdbMappingList []*ExcelEdbMapping) (err error) { o, err := orm.NewOrmUsingDB("data").Begin() if err != nil { return } defer func() { if err != nil { _ = o.Rollback() } else { _ = o.Commit() } }() // ETA表格信息变更 _, err = o.Update(excelInfo, updateExcelInfoParams...) if err != nil { return } //更新父级分类时，同时更新子集分类 if excelInfo.Source == utils.BALANCE_TABLE && excelInfo.ParentId == 0 { // 同步更新子表分类 sql := `UPDATE FROM excel_info set excel_classify_id = ? WHERE parent_id=? ` _, err = o.Raw(sql, excelInfo.ExcelClassifyId, excelInfo.ExcelInfoId).Exec() } // 删除关系表 sql := `DELETE FROM excel_edb_mapping WHERE excel_info_id=? ` _, err = o.Raw(sql, excelInfo.ExcelInfoId).Exec() // excel与指标的关联关系 dataNum := len(excelEdbMappingList) if dataNum > 0 { for k, v := range excelEdbMappingList { v.ExcelInfoId = excelInfo.ExcelInfoId excelEdbMappingList[k] = v } _, err = o.InsertMulti(dataNum, excelEdbMappingList) } return } // GetExcelInfoAll 获取所有表格列表，用于分类展示 func GetExcelInfoAll() (items []*ExcelClassifyItems, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT excel_info_id,excel_classify_id,excel_name AS excel_classify_name, unique_code,sys_user_id,sys_user_real_name,is_join_permission FROM excel_info where is_delete=0 ORDER BY sort asc,create_time ASC ` _, err = o.Raw(sql).QueryRows(&items) return } // GetNoContentExcelInfoAll 获取不含content的表格列表 用于分类展示 func GetNoContentExcelInfoAll(source, userId int) (items []*ExcelClassifyItems, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT excel_info_id,excel_classify_id,excel_name AS excel_classify_name, unique_code,sys_user_id,sys_user_real_name,sort,is_join_permission FROM excel_info where is_delete=0 AND source = ? ` pars := []interface{}{source} if userId > 0 { sql += ` AND sys_user_id = ? ` pars = append(pars, userId) } sql += ` ORDER BY sort asc,excel_info_id desc ` _, err = o.Raw(sql, pars...).QueryRows(&items) return } // GetBalanceNoContentExcelInfoAll 获取不含content的平衡表表格列表 用于分类展示 func GetBalanceNoContentExcelInfoAll(source int, excelInfoIds []int, userId int) (items []*ExcelClassifyItems, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT excel_info_id,excel_classify_id,excel_name AS excel_classify_name, unique_code,sys_user_id,sys_user_real_name,sort,is_join_permission FROM excel_info where is_delete=0 AND source = ? AND parent_id = 0 AND balance_type=0 ` pars := []interface{}{source} if userId > 0 { if len(excelInfoIds) > 0 { sql += ` AND (excel_info_id in (` + utils.GetOrmInReplace(len(excelInfoIds)) + `) or sys_user_id = ?)` pars = append(pars, excelInfoIds, userId) } else { sql += ` AND sys_user_id = ? ` pars = append(pars, userId) } } sql += ` ORDER BY sort asc,excel_info_id desc ` _, err = o.Raw(sql, pars...).QueryRows(&items) return } // GetAllExcelInfoBySource 根据来源获取包含content的表格列表 func GetAllExcelInfoBySource(source int) (items []*ExcelInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM excel_info where is_delete=0 AND source = ? ORDER BY sort asc,create_time desc ` _, err = o.Raw(sql, source).QueryRows(&items) return } // GetExcelInfoById 根据id 获取eta表格详情 func GetExcelInfoById(excelInfoId int) (item *ExcelInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM excel_info WHERE excel_info_id=? AND is_delete=0 ` err = o.Raw(sql, excelInfoId).QueryRow(&item) return } // GetExcelInfoByUnicode 编码获取表格 func GetExcelInfoByUnicode(unicode string) (item *ExcelInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM excel_info WHERE unique_code = ? AND is_delete = 0 ` err = o.Raw(sql, unicode).QueryRow(&item) return } func GetExcelInfoViewById(excelInfoId int) (item *ExcelInfoView, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM excel_info WHERE excel_info_id=? AND is_delete=0 ` err = o.Raw(sql, excelInfoId).QueryRow(&item) return } func GetExcelInfoCountByCondition(condition string, pars []interface{}) (count int, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT COUNT(1) AS count FROM excel_info WHERE 1=1 AND is_delete=0 ` if condition != "" { sql += condition } err = o.Raw(sql, pars).QueryRow(&count) return } func GetNoContentExcelInfoListByCondition(condition string, pars []interface{}, startSize, pageSize int) (items []*ExcelClassifyItems, err error) { o := orm.NewOrmUsingDB("data") sql := `SELECT excel_info_id,excel_classify_id,excel_name AS excel_classify_name, unique_code,sys_user_id,sys_user_real_name,sort,is_join_permission FROM excel_info WHERE 1=1 ` if condition != "" { sql += condition } sql += ` AND is_delete=0 ORDER BY excel_info_id DESC LIMIT ?,? ` _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items) return } func GetNoContentExcelInfoListByConditionNoPage(condition string, pars []interface{}) (items []*ExcelInfo, err error) { o := orm.NewOrmUsingDB("data") sql := `SELECT excel_info_id,excel_classify_id,excel_name, unique_code,sys_user_id,sys_user_real_name,sort,is_join_permission, parent_id, balance_type, update_user_id,update_user_real_name,rel_excel_info_id,version_name FROM excel_info WHERE is_delete=0 ` if condition != "" { sql += condition } sql += ` ORDER BY excel_info_id DESC ` _, err = o.Raw(sql, pars).QueryRows(&items) return } func GetExcelInfoListByCondition(condition string, pars []interface{}) (items []*ExcelInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM excel_info WHERE 1=1 AND is_delete=0 ` if condition != "" { sql += condition } sql += ` ORDER BY sort asc, excel_info_id asc` _, err = o.Raw(sql, pars).QueryRows(&items) return } // GetNextExcelInfoByCondition 根据条件获取下一个表格 func GetNextExcelInfoByCondition(condition string, pars []interface{}) (item *ExcelInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM excel_info WHERE 1=1 AND is_delete=0 ` if condition != "" { sql += condition } sql += " ORDER BY sort asc , create_time desc LIMIT 1 " err = o.Raw(sql, pars).QueryRow(&item) return } // GetNextExcelInfo 根据分类id获取下一个excel表格 func GetNextExcelInfo(classifyId, classifySort, source int) (item *ExcelInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT b.* FROM excel_classify AS a INNER JOIN excel_info AS b ON a.excel_classify_id=b.excel_classify_id WHERE (a.sort>? OR (a.sort=? and a.excel_classify_id>?) ) AND a.is_delete=0 AND b.is_delete=0 AND a.source = ? AND b.source = ? ORDER BY a.sort ASC,b.sort asc,b.create_time desc LIMIT 1 ` err = o.Raw(sql, classifySort, classifySort, classifyId, source, source).QueryRow(&item) return } // EditExcelInfoImage 修改excel表格的图片 func EditExcelInfoImage(excelInfoId int, imageUrl string) (err error) { o := orm.NewOrmUsingDB("data") sql := ` UPDATE excel_info SET excel_image=?, modify_time = NOW() WHERE excel_info_id = ? AND is_delete=0 ` _, err = o.Raw(sql, imageUrl, excelInfoId).Exec() if err != nil { fmt.Println("EditExcelInfoImage Err:", err.Error()) return err } return } // GetExcelInfoByUniqueCode 根据unique_code来获取excel表格详情 func GetExcelInfoByUniqueCode(uniqueCode string) (item *ExcelInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM excel_info WHERE unique_code=? AND is_delete=0 ` err = o.Raw(sql, uniqueCode).QueryRow(&item) return } // GetFirstExcelInfoByClassifyId 获取当前分类下，且排序数相同 的排序第一条的数据 func GetFirstExcelInfoByClassifyId(classifyId int) (item *ExcelInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM excel_info WHERE excel_classify_id=? AND is_delete=0 order by sort asc,excel_info_id desc limit 1` err = o.Raw(sql, classifyId).QueryRow(&item) return } // UpdateExcelInfoSortByClassifyId 根据表格id更新排序 func UpdateExcelInfoSortByClassifyId(classifyId, nowSort, prevExcelInfoId int, updateSort string, source int) (err error) { o := orm.NewOrmUsingDB("data") sql := ` update excel_info set sort = ` + updateSort + ` WHERE excel_classify_id=? AND source=? AND is_delete=0 AND ( sort > ? ` // todo 前一个兄弟节点后移 if prevExcelInfoId > 0 { sql += ` or (excel_info_id < ` + fmt.Sprint(prevExcelInfoId) + ` and sort = ` + fmt.Sprint(nowSort) + `)` } sql += `)` _, err = o.Raw(sql, classifyId, source, nowSort).Exec() return } type ExcelInfoView struct { ExcelInfoId int `orm:"column(excel_info_id);pk"` ExcelName string `description:"来源名称"` ExcelClassifyId int `description:"表格分类id"` ExcelClassifyName string `description:"表格名称"` 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"` ExcelType int `description:"生成样式:1:曲线图,2:季节性图"` Calendar string `description:"公历/农历"` SeasonStartDate string `description:"季节性图开始日期"` SeasonEndDate string `description:"季节性图开始日期"` ExcelImage string `description:"表格图片"` Sort int `description:"排序字段，数字越小越排前面"` IsAdd bool `description:"true:已加入我的图库,false:未加入我的图库"` MyExcelId int MyExcelClassifyId string `description:"我的表格分类，多个用逗号隔开"` ExcelClassify []*ExcelClassifyView EdbEndDate string `description:"指标最新更新日期"` LeftMin string `description:"表格左侧最小值"` LeftMax string `description:"表格左侧最大值"` RightMin string `description:"表格右侧最小值"` RightMax string `description:"表格右侧最大值"` } // GetExcelInfoByClassifyIdAndName 根据分类id和表格名获取表格信息 func GetExcelInfoByClassifyIdAndName(classifyId int, excelName string) (item *ExcelInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM excel_info WHERE excel_classify_id = ? and excel_name=? AND is_delete=0 ` err = o.Raw(sql, classifyId, excelName).QueryRow(&item) return } // GetNoContentExcelListByCondition 获取没有content的excel表格列表数据 func GetNoContentExcelListByCondition(condition string, pars []interface{}, startSize, pageSize int) (item []*MyExcelInfoList, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT excel_info_id,source,excel_type,excel_name,unique_code,excel_classify_id,sys_user_id,sys_user_real_name,excel_image,file_url,sort,create_time,modify_time,is_join_permission,update_user_id,update_user_real_name FROM excel_info WHERE 1=1 AND is_delete=0 ` 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 } // GetNoContentExcelListByConditionNoPage 获取没有content的excel表格列表数据 func GetNoContentExcelListByConditionNoPage(condition string, pars []interface{}) (item []*MyExcelInfoList, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT excel_info_id,source,excel_type,excel_name,unique_code,excel_classify_id,sys_user_id,sys_user_real_name,excel_image,file_url,sort,create_time,modify_time,is_join_permission,update_user_id,update_user_real_name FROM excel_info WHERE 1=1 AND is_delete=0 ` if condition != "" { sql += condition } //sql += " ORDER BY sort ASC,chart_info_id DESC LIMIT ?,? " sql += " ORDER BY create_time DESC" _, err = o.Raw(sql, pars).QueryRows(&item) return } func GetExcelListCountByCondition(condition string, pars []interface{}) (count int, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT COUNT(1) AS count FROM excel_info WHERE 1=1 AND is_delete=0 ` if condition != "" { sql += condition } err = o.Raw(sql, pars).QueryRow(&count) return } // GetExcelViewInfoByExcelInfoId 根据excelInfoId 获取ETA表格详情 func GetExcelViewInfoByExcelInfoId(excelInfoId int) (item *MyExcelInfoList, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT excel_info_id,source,excel_type,excel_name,unique_code,excel_classify_id,sys_user_id,sys_user_real_name,excel_image,file_url,sort,create_time,modify_time,is_join_permission FROM excel_info WHERE excel_info_id = ? AND is_delete=0 ` err = o.Raw(sql, excelInfoId).QueryRow(&item) return } // GetExcelInfoCountByClassifyId 根据分类id获取名下表格数量 func GetExcelInfoCountByClassifyId(classifyId int) (total int64, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT count(1) total FROM excel_info WHERE excel_classify_id = ? AND is_delete=0 ` err = o.Raw(sql, classifyId).QueryRow(&total) return } // UpdateExcelInfoClassifyId 更改表格分类 func UpdateExcelInfoClassifyId(classifyId, excelInfoId int) (err error) { o := orm.NewOrmUsingDB("data") sql := ` update excel_info set excel_classify_id = ? WHERE excel_info_id=? ` _, err = o.Raw(sql, classifyId, excelInfoId).Exec() return } // GetNoContentExcelInfoByName 根据名称 获取eta表格详情 func GetNoContentExcelInfoByName(excelName string, source, adminId int) (item *MyExcelInfoList, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT excel_info_id,source,excel_type,excel_name,unique_code,excel_classify_id,sys_user_id,sys_user_real_name,excel_image,file_url,sort,create_time,modify_time,is_join_permission,update_user_id,update_user_real_name FROM excel_info WHERE excel_name = ? AND source = ? AND is_delete=0 AND sys_user_id = ? ` err = o.Raw(sql, excelName, source, adminId).QueryRow(&item) return } // GetNoContentExcelInfoByUniqueCode 根据unique_code来获取excel表格详情 func GetNoContentExcelInfoByUniqueCode(uniqueCode string) (item *MyExcelInfoList, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT excel_info_id,source,excel_type,excel_name,unique_code,excel_classify_id,sys_user_id,sys_user_real_name,excel_image,file_url,sort,create_time,modify_time,is_join_permission,update_user_id,update_user_real_name FROM excel_info WHERE unique_code=? AND is_delete=0 ` err = o.Raw(sql, uniqueCode).QueryRow(&item) return } // GetNoContentExcelInfoByExcelId 根据表格id来获取excel表格详情 func GetNoContentExcelInfoByExcelId(excelInfoId int) (item *MyExcelInfoList, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT excel_info_id,source,excel_type,excel_name,unique_code,excel_classify_id,sys_user_id,sys_user_real_name,excel_image,file_url,sort,create_time,modify_time,is_join_permission,update_user_id,update_user_real_name FROM excel_info WHERE excel_info_id=? AND is_delete=0 ` err = o.Raw(sql, excelInfoId).QueryRow(&item) return } // AddExcelInfoAndSheet 新增excel func AddExcelInfoAndSheet(excelInfo *ExcelInfo, sheetParamsList []AddExcelSheetParams) (err error) { o, err := orm.NewOrmUsingDB("data").Begin() if err != nil { return } defer func() { if err != nil { _ = o.Rollback() } else { _ = o.Commit() } }() // 表格信息入库 lastId, err := o.Insert(excelInfo) if err != nil { return } excelInfo.ExcelInfoId = int(lastId) // sheet信息入库 for _, sheetInfo := range sheetParamsList { dataNum := len(sheetInfo.DataList) //sheet信息入库 excelSheetInfo := &ExcelSheet{ ExcelSheetId: 0, ExcelInfoId: excelInfo.ExcelInfoId, SheetName: sheetInfo.SheetName, PageNum: dataNum, Index: sheetInfo.Index, Sort: sheetInfo.Sort, Config: sheetInfo.Config, CalcChain: sheetInfo.CalcChain, ModifyTime: time.Now(), CreateTime: time.Now(), } sheetId, tmpErr := o.Insert(excelSheetInfo) if tmpErr != nil { err = tmpErr return } excelSheetInfo.ExcelSheetId = int(sheetId) // data信息入库 if dataNum > 0 { for k, _ := range sheetInfo.DataList { sheetInfo.DataList[k].ExcelSheetId = excelSheetInfo.ExcelSheetId sheetInfo.DataList[k].ExcelInfoId = excelSheetInfo.ExcelInfoId } _, tmpErr = o.InsertMulti(dataNum, sheetInfo.DataList) if tmpErr != nil { err = tmpErr return } } } return } // SaveExcelInfoAndSheet 编辑保存 func SaveExcelInfoAndSheet(excelInfo *ExcelInfo, updateExcelInfoParam []string, sheetParamsList []AddExcelSheetParams) (err error) { o, err := orm.NewOrmUsingDB("data").Begin() if err != nil { return } defer func() { if err != nil { _ = o.Rollback() } else { _ = o.Commit() } }() // 表格信息入库 _, err = o.Update(excelInfo, updateExcelInfoParam...) if err != nil { return } // 先删除历史的sheet信息 sql := `DELETE FROM excel_sheet WHERE excel_info_id = ?` _, err = o.Raw(sql, excelInfo.ExcelInfoId).Exec() if err != nil { return } // 再删除历史sheet中的cell data信息 sql = `DELETE FROM excel_sheet_data WHERE excel_info_id = ?` _, err = o.Raw(sql, excelInfo.ExcelInfoId).Exec() if err != nil { return } // sheet信息入库 for _, sheetInfo := range sheetParamsList { dataNum := len(sheetInfo.DataList) //sheet信息入库 excelSheetInfo := &ExcelSheet{ ExcelSheetId: 0, ExcelInfoId: excelInfo.ExcelInfoId, SheetName: sheetInfo.SheetName, PageNum: dataNum, Index: sheetInfo.Index, Sort: sheetInfo.Sort, Config: sheetInfo.Config, CalcChain: sheetInfo.CalcChain, ModifyTime: time.Now(), CreateTime: time.Now(), } sheetId, tmpErr := o.Insert(excelSheetInfo) if tmpErr != nil { err = tmpErr return } excelSheetInfo.ExcelSheetId = int(sheetId) // data信息入库 if dataNum > 0 { for k, _ := range sheetInfo.DataList { sheetInfo.DataList[k].ExcelSheetId = excelSheetInfo.ExcelSheetId sheetInfo.DataList[k].ExcelInfoId = excelSheetInfo.ExcelInfoId } _, tmpErr = o.InsertMulti(dataNum, sheetInfo.DataList) if tmpErr != nil { err = tmpErr return } } } return } // BatchRefreshExcelReq 批量刷新表格请求 type BatchRefreshExcelReq struct { ExcelCodes []string `description:"表格编码"` PrimaryId int `description:"研报/智能研报/PPT主键"` SubId int `description:"报告章节ID"` Source string `description:"来源，枚举值：report、english_report、smart_report"` } // GetExcelMaxSortByClassifyId 获取当前分类下，且排序数最大的excel func GetExcelMaxSortByClassifyId(classifyId int, source int) (sort int, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT Max(sort) AS sort FROM excel_info WHERE excel_classify_id=? AND source = ? AND is_delete=0 order by sort desc,excel_info_id desc limit 1` err = o.Raw(sql, classifyId, source).QueryRow(&sort) return } // GetNoContentExcelListByExcelInfoIdList // @Description: 根据ETA表格ID列表获取列表信息 // @param excelInfoIdList []string // @return items // @return err func GetNoContentExcelListByExcelInfoIdList(excelInfoIdList []string) (items []*MyExcelInfoList, err error) { num := len(excelInfoIdList) if num <= 0 { return } o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM excel_info WHERE excel_info_id in (` + utils.GetOrmInReplace(num) + `) order by excel_info_id DESC ` _, err = o.Raw(sql, excelInfoIdList).QueryRows(&items) return } func GetNoContentExcelListByExcelInfoIdAndParentId(excelInfoIdList []string) (items []*MyExcelInfoList, err error) { num := len(excelInfoIdList) if num <= 0 { return } o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM excel_info WHERE excel_info_id in (` + utils.GetOrmInReplace(num) + `) OR parent_id in (` + utils.GetOrmInReplace(num) + `) order by excel_info_id DESC ` _, err = o.Raw(sql, excelInfoIdList, excelInfoIdList).QueryRows(&items) return } // GetNoContentExcelListByUserId // @Description: 根据ETA表格ID列表获取列表信息 // @param userIdList []int // @return items // @return err func GetNoContentExcelListByUserId(userIdList []int) (items []*MyExcelInfoList, err error) { num := len(userIdList) if num <= 0 { return } o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM excel_info WHERE sys_user_id in (` + utils.GetOrmInReplace(num) + `) order by excel_info_id DESC ` _, err = o.Raw(sql, userIdList).QueryRows(&items) return } // ModifyExcelInfoUserIdByCodeList // @Description: 根据编码列表修改表格信息 // @author: Roc // @datetime 2024-03-26 14:16:30 // @param excelIdList []string // @param userId int // @param userName string // @return err error func ModifyExcelInfoUserIdByCodeList(excelIdList []string, userId int, userName string) (err error) { num := len(excelIdList) if num <= 0 { return } o := orm.NewOrmUsingDB("data") sql := `UPDATE excel_info SET sys_user_id=?,sys_user_real_name=? WHERE excel_info_id in (` + utils.GetOrmInReplace(num) + `) ` _, err = o.Raw(sql, userId, userName, excelIdList).Exec() return } // ModifyExcelInfoUserIdByOldUserId // @Description: 根据旧的用户id修改创建人 // @author: Roc // @datetime 2024-03-25 19:14:59 // @param oldUserId int // @param userId int // @param userName string // @return err error func ModifyExcelInfoUserIdByOldUserId(oldUserIdList []int, userId int, userName string) (err error) { num := len(oldUserIdList) if num <= 0 { return } o := orm.NewOrmUsingDB("data") sql := `UPDATE excel_info SET sys_user_id=?,sys_user_real_name=? WHERE is_delete=0 AND sys_user_id in (` + utils.GetOrmInReplace(num) + `) ` _, err = o.Raw(sql, userId, userName, oldUserIdList).Exec() return } func GetExcelBaseInfoByExcelInfoIdList(excelInfoIdList []int) (items []*data_manage.ExcelBaseInfo, err error) { num := len(excelInfoIdList) if num <= 0 { return } o := orm.NewOrmUsingDB("data") sql := ` SELECT excel_info_id,source,excel_type,excel_name,unique_code,excel_classify_id,sys_user_id,sys_user_real_name FROM excel_info WHERE excel_info_id in (` + utils.GetOrmInReplace(num) + `) order by excel_info_id DESC ` _, err = o.Raw(sql, excelInfoIdList).QueryRows(&items) return } // ReplaceEdbInExcel 替换表格中的指标 func ReplaceEdbInExcel(oldEdbInfoId, newEdbInfoId int, updateExcelList []*ExcelInfo) (err error) { var errmsg string logMsg := `` // 记录替换的日志 replaceTotal := 0 o := orm.NewOrmUsingDB("data") to, err := o.Begin() if err != nil { return } defer func() { if err != nil { _ = to.Rollback() } else { _ = to.Commit() if logMsg != `` { utils.FileLog.Info(fmt.Sprintf("替换表格中的指标记录，替换总表格数：%d,旧的指标id:%d，新的指标id:%d;%s", replaceTotal, oldEdbInfoId, newEdbInfoId, logMsg)) } } if errmsg != "" { fmt.Println("errmsg:" + errmsg) } if err != nil && errmsg != "" { //go alarm_msg.SendAlarmMsg("替换替换配置中的指标记录失败提醒,errmsg："+errmsg, 3) } }() // 替换表格中的内容 for _, excelInfo := range updateExcelList { //更新配置中的指标A sql := `UPDATE excel_info SET content=?, modify_time=? WHERE excel_info_id=?` _, err = to.Raw(sql, excelInfo.Content, time.Now(), excelInfo.ExcelInfoId).Exec() if err != nil { errmsg = "更新表格内容失败：Err:" + err.Error() return } //更新配置中的指标B // 判断是否已存在绑定，如果存在则删除 sql = `DELETE FROM excel_edb_mapping WHERE excel_info_id=? and edb_info_id=?` _, err = to.Raw(sql, excelInfo.ExcelInfoId, newEdbInfoId).Exec() if err != nil { errmsg = "删除指标B关联图表配置信息失败：Err:" + err.Error() return } // 插入新的绑定 sql = `UPDATE excel_edb_mapping SET edb_info_id=?, modify_time=? WHERE excel_info_id=? and edb_info_id=?` _, err = to.Raw(sql, newEdbInfoId, time.Now(), excelInfo.ExcelInfoId, oldEdbInfoId).Exec() if err != nil { errmsg = "更新指标B关联图表配置信息失败：Err:" + err.Error() return } logMsg += `涉及到的表格id：` + strconv.Itoa(excelInfo.ExcelInfoId) + ";" replaceTotal += 1 } return } // GetChildExcelInfoByParentId 根据id 获取eta表格详情 func GetChildExcelInfoByParentId(parentId int) (items []*ExcelInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT excel_info_id,source,excel_type,excel_name,unique_code,excel_classify_id,sys_user_id,sys_user_real_name,excel_image,file_url,sort,create_time,modify_time,is_join_permission, parent_id, balance_type, update_user_id,update_user_real_name FROM excel_info WHERE parent_id=? AND is_delete=0 order by sort asc, excel_info_id asc` _, err = o.Raw(sql, parentId).QueryRows(&items) return } // ExcelInfoDetailButton 操作按钮 type ExcelInfoDetailButton struct { RefreshButton bool `description:"是否可刷新"` CopyButton bool `description:"是否可另存为"` DownloadButton bool `description:"是否可下载"` OpButton bool `description:"是否可编辑"` DeleteButton bool `description:"是否可删除"` OpEdbButton bool `description:"是否可生成指标"` RefreshEdbButton bool `description:"是否可刷新指标"` OpWorkerButton bool `description:"是否修改协作人"` } func UpdateExcelInfoClassifyIdByIds(classifyId int, excelIds []int) (err error) { if len(excelIds) == 0 { return } o := orm.NewOrmUsingDB("data") sql := fmt.Sprintf(`UPDATE excel_info SET excel_classify_id = ? WHERE excel_info_id IN (%s)`, utils.GetOrmInReplace(len(excelIds))) _, err = o.Raw(sql, classifyId, excelIds).Exec() return }