package excel import ( "eta_gn/eta_chart_lib/global" "eta_gn/eta_chart_lib/utils" "fmt" "time" ) type ExcelInfo struct { ExcelInfoId int `gorm:"column:excel_info_id;primaryKey" orm:"column(excel_info_id);pk"` Source int `gorm:"column:source" description:"表格来源，1：excel插件的表格，2：自定义表格，3：混合表格，4：自定义分析，默认：1"` ExcelType int `gorm:"column:excel_type" description:"表格类型，1：指标列，2：日期列，默认：1"` ExcelName string `gorm:"column:excel_name" description:"表格名称"` UniqueCode string `gorm:"column:unique_code" description:"表格唯一编码"` ExcelClassifyId int `gorm:"column:excel_classify_id" description:"表格分类id"` SysUserId int `gorm:"column:sys_user_id" description:"操作人id"` SysUserRealName string `gorm:"column:sys_user_real_name" description:"操作人真实姓名"` Content string `gorm:"column:content" description:"表格内容"` ExcelImage string `gorm:"column:excel_image" description:"表格图片"` FileUrl string `gorm:"column:file_url" description:"表格下载地址"` Sort int `gorm:"column:sort" description:"排序字段，数字越小越排前面"` IsDelete int `gorm:"column:is_delete" description:"是否删除，0：未删除，1：已删除"` ModifyTime time.Time `gorm:"column:modify_time" description:"最近修改日期"` CreateTime time.Time `gorm:"column:create_time" description:"创建日期"` IsJoinPermission int `gorm:"column:is_join_permission" description:"是否加入权限管控，0：不加入；1：加入；默认：0"` BalanceType int `gorm:"column:balance_type" description:"平衡表类型：0 动态表，1静态表"` UpdateUserId int `gorm:"column:update_user_id" description:"更新人id"` UpdateUserRealName string `gorm:"column:update_user_real_name" description:"更新人真实姓名"` RelExcelInfoId int `gorm:"column:rel_excel_info_id" description:"平衡表里静态表关联的动态表excel id"` VersionName string `gorm:"column:version_name" description:"静态表版本名称"` } func (excelInfo *ExcelInfo) Update(cols []string) (err error) { o := global.DmSQL["data"] err = o.Model(excelInfo).Select(cols).Updates(excelInfo).Error return } type MyExcelInfoList struct { ExcelInfoId int `gorm:"column:excel_info_id;primaryKey" orm:"column(excel_info_id);pk"` Source int `gorm:"column:source" description:"表格来源，1：excel插件的表格，2：自定义表格，默认：1"` ExcelType int `gorm:"column:excel_type" description:"表格类型，1：指标列，2：日期列，默认：1"` ExcelName string `gorm:"column:excel_name" description:"表格名称"` UniqueCode string `gorm:"column:unique_code" description:"表格唯一编码"` ExcelClassifyId int `gorm:"column:excel_classify_id" description:"表格分类id"` SysUserId int `gorm:"column:sys_user_id" description:"操作人id"` SysUserRealName string `gorm:"column:sys_user_real_name" description:"操作人真实姓名"` ExcelImage string `gorm:"column:excel_image" description:"表格图片"` FileUrl string `gorm:"column:file_url" description:"表格下载地址"` Sort int `gorm:"column:sort" description:"排序字段，数字越小越排前面"` ModifyTime time.Time `gorm:"column:modify_time" description:"最近修改日期"` CreateTime time.Time `gorm:"column:create_time" description:"创建日期"` IsJoinPermission int `gorm:"column:is_join_permission" description:"是否加入权限管控，0：不加入；1：加入；默认：0"` HaveOperaAuth bool `gorm:"column:have_opera_auth" description:"是否有数据权限"` } func AddExcelInfo(excelInfo *ExcelInfo, excelEdbMappingList []*ExcelEdbMapping) (err error) { o := global.DmSQL["data"].Begin() defer func() { if err != nil { _ = o.Rollback() } else { _ = o.Commit() } }() err = o.Create(excelInfo).Error if err != nil { return } dataNum := len(excelEdbMappingList) if dataNum > 0 { for k, v := range excelEdbMappingList { v.ExcelInfoId = excelInfo.ExcelInfoId excelEdbMappingList[k] = v } err = o.CreateInBatches(excelEdbMappingList, utils.MultiAddNum).Error } return } func EditExcelInfo(excelInfo *ExcelInfo, updateExcelInfoParams []string, excelEdbMappingList []*ExcelEdbMapping) (err error) { o := global.DmSQL["data"].Begin() defer func() { if err != nil { _ = o.Rollback() } else { _ = o.Commit() } }() err = o.Model(excelInfo).Select(updateExcelInfoParams).Updates(excelInfo).Error if err != nil { return } sql := `DELETE FROM excel_edb_mapping WHERE excel_info_id=? ` err = o.Exec(sql, excelInfo.ExcelInfoId).Error if err != nil { return } dataNum := len(excelEdbMappingList) if dataNum > 0 { for k, v := range excelEdbMappingList { v.ExcelInfoId = excelInfo.ExcelInfoId excelEdbMappingList[k] = v } err = o.CreateInBatches(excelEdbMappingList, utils.MultiAddNum).Error } return } func GetExcelInfoAll() (items []*ExcelClassifyItems, err error) { o := global.DmSQL["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).Scan(&items).Error return } func GetNoContentExcelInfoAll(source, userId int) (items []*ExcelClassifyItems, err error) { o := global.DmSQL["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...).Scan(&items).Error return } func GetAllExcelInfoBySource(source int) (items []*ExcelInfo, err error) { o := global.DmSQL["data"] sql := ` SELECT * FROM excel_info where is_delete=0 AND source = ? ORDER BY sort asc,create_time desc ` err = o.Raw(sql, source).Scan(&items).Error return } func GetExcelInfoById(excelInfoId int) (item *ExcelInfo, err error) { o := global.DmSQL["data"] sql := ` SELECT * FROM excel_info WHERE excel_info_id=? AND is_delete=0 ` err = o.Raw(sql, excelInfoId).First(&item).Error return } func GetExcelInfoByUnicode(unicode string) (item *ExcelInfo, err error) { o := global.DmSQL["data"] sql := ` SELECT * FROM excel_info WHERE unique_code = ? AND is_delete = 0 ` err = o.Raw(sql, unicode).First(&item).Error return } func GetExcelInfoViewById(excelInfoId int) (item *ExcelInfoView, err error) { o := global.DmSQL["data"] sql := ` SELECT * FROM excel_info WHERE excel_info_id=? AND is_delete=0 ` err = o.Raw(sql, excelInfoId).First(&item).Error return } func GetExcelInfoCountByCondition(condition string, pars []interface{}) (count int, err error) { o := global.DmSQL["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...).Scan(&count).Error return } func GetNoContentExcelInfoListByCondition(condition string, pars []interface{}, startSize, pageSize int) (items []*ExcelClassifyItems, err error) { o := global.DmSQL["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 ?,? ` pars = append(pars, startSize, pageSize) err = o.Raw(sql, pars...).Scan(&items).Error return } func GetExcelInfoByCondition(condition string, pars []interface{}) (item *ExcelInfo, err error) { o := global.DmSQL["data"] sql := ` SELECT * FROM excel_info WHERE 1=1 AND is_delete=0 ` if condition != "" { sql += condition } err = o.Raw(sql, pars...).First(&item).Error return } func GetNextExcelInfoByCondition(condition string, pars []interface{}) (item *ExcelInfo, err error) { o := global.DmSQL["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...).First(&item).Error return } func GetNextExcelInfo(classifyId, classifySort, source int) (item *ExcelInfo, err error) { o := global.DmSQL["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).First(&item).Error return } func EditExcelInfoImage(excelInfoId int, imageUrl string) (err error) { o := global.DmSQL["data"] sql := ` UPDATE excel_info SET excel_image=?, modify_time = NOW() WHERE excel_info_id = ? AND is_delete=0 ` err = o.Exec(sql, imageUrl, excelInfoId).Error if err != nil { fmt.Println("EditExcelInfoImage Err:", err.Error()) return err } return } func GetExcelInfoByUniqueCode(uniqueCode string) (item *ExcelInfo, err error) { o := global.DmSQL["data"] sql := ` SELECT * FROM excel_info WHERE unique_code=? AND is_delete=0 ` err = o.Raw(sql, uniqueCode).First(&item).Error return } func GetFirstExcelInfoByClassifyId(classifyId int) (item *ExcelInfo, err error) { o := global.DmSQL["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).First(&item).Error return } func UpdateExcelInfoSortByClassifyId(classifyId, nowSort, prevExcelInfoId int, updateSort string, source int) (err error) { o := global.DmSQL["data"] sql := ` update excel_info set sort = ` + updateSort + ` WHERE excel_classify_id=? AND source=? AND is_delete=0 AND ( sort > ? ` if prevExcelInfoId > 0 { sql += ` or (excel_info_id < ` + fmt.Sprint(prevExcelInfoId) + ` and sort = ` + fmt.Sprint(nowSort) + `)` } sql += `)` err = o.Exec(sql, classifyId, source, nowSort).Error return } type ExcelInfoView struct { ExcelInfoId int `gorm:"column:excel_info_id;primaryKey" orm:"column(excel_info_id);pk"` ExcelName string `gorm:"column:excel_name" description:"来源名称"` ExcelClassifyId int `gorm:"column:excel_classify_id" description:"表格分类id"` ExcelClassifyName string `gorm:"column:excel_classify_name" description:"表格名称"` SysUserId int `gorm:"column:sys_user_id"` SysUserRealName string `gorm:"column:sys_user_real_name"` UniqueCode string `gorm:"column:unique_code" description:"表格唯一编码"` CreateTime time.Time `gorm:"column:create_time"` ModifyTime time.Time `gorm:"column:modify_time"` DateType int `gorm:"column:date_type" description:"日期类型:1:00年至今,2:10年至今,3:15年至今，4：年初至今，5:自定义时间"` StartDate string `gorm:"column:start_date" description:"自定义开始日期"` EndDate string `gorm:"column:end_date" description:"自定义结束日期"` IsSetName int `gorm:"column:is_set_name" description:"设置名称"` EdbInfoIds string `gorm:"column:edb_info_ids" description:"指标id"` ExcelType int `gorm:"column:excel_type" description:"生成样式:1:曲线图,2:季节性图"` Calendar string `gorm:"column:calendar" description:"公历/农历"` SeasonStartDate string `gorm:"column:season_start_date" description:"季节性图开始日期"` SeasonEndDate string `gorm:"column:season_end_date" description:"季节性图结束日期"` ExcelImage string `gorm:"column:excel_image" description:"表格图片"` Sort int `gorm:"column:sort" description:"排序字段，数字越小越排前面"` IsAdd bool `gorm:"column:is_add" description:"true:已加入我的图库,false:未加入我的图库"` MyExcelId int `gorm:"column:my_excel_id"` MyExcelClassifyId string `gorm:"column:my_excel_classify_id" description:"我的表格分类，多个用逗号隔开"` ExcelClassify []*ExcelClassifyView EdbEndDate string `gorm:"column:edb_end_date" description:"指标最新更新日期"` LeftMin string `gorm:"column:left_min" description:"表格左侧最小值"` LeftMax string `gorm:"column:left_max" description:"表格左侧最大值"` RightMin string `gorm:"column:right_min" description:"表格右侧最小值"` RightMax string `gorm:"column:right_max" description:"表格右侧最大值"` } func GetExcelInfoByClassifyIdAndName(classifyId int, excelName string) (item *ExcelInfo, err error) { o := global.DmSQL["data"] sql := ` SELECT * FROM excel_info WHERE excel_classify_id = ? and excel_name=? AND is_delete=0 ` err = o.Raw(sql, classifyId, excelName).First(&item).Error return } func GetNoContentExcelListByCondition(condition string, pars []interface{}, startSize, pageSize int) (item []*MyExcelInfoList, err error) { o := global.DmSQL["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 1=1 AND is_delete=0 ` if condition != "" { sql += condition } sql += " ORDER BY create_time DESC LIMIT ?,? " pars = append(pars, startSize, pageSize) err = o.Raw(sql, pars...).Scan(&item).Error return } func GetExcelListCountByCondition(condition string, pars []interface{}) (count int, err error) { o := global.DmSQL["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...).Scan(&count).Error return } func GetExcelViewInfoByExcelInfoId(excelInfoId int) (item *MyExcelInfoList, err error) { o := global.DmSQL["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).First(&item).Error return } func GetExcelInfoCountByClassifyId(classifyId int) (total int64, err error) { o := global.DmSQL["data"] sql := ` SELECT count(1) total FROM excel_info WHERE excel_classify_id = ? AND is_delete=0 ` err = o.Raw(sql, classifyId).Scan(&total).Error return } func UpdateExcelInfoClassifyId(classifyId, excelInfoId int) (err error) { o := global.DmSQL["data"] sql := ` update excel_info set excel_classify_id = ? WHERE excel_info_id=? ` err = o.Exec(sql, classifyId, excelInfoId).Error return } func GetNoContentExcelInfoByName(excelName string, source int) (item *MyExcelInfoList, err error) { o := global.DmSQL["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_name = ? AND source = ? AND is_delete=0 ` err = o.Raw(sql, excelName, source).First(&item).Error return } func GetNoContentExcelInfoByUniqueCode(uniqueCode string) (item *MyExcelInfoList, err error) { o := global.DmSQL["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 unique_code=? AND is_delete=0 ` err = o.Raw(sql, uniqueCode).First(&item).Error return } func GetNoContentExcelInfoByExcelId(excelInfoId int) (item *MyExcelInfoList, err error) { o := global.DmSQL["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).First(&item).Error return } func AddExcelInfoAndSheet(excelInfo *ExcelInfo, sheetParamsList []AddExcelSheetParams) (err error) { o := global.DmSQL["data"].Begin() defer func() { if err != nil { _ = o.Rollback() } else { _ = o.Commit() } }() err = o.Create(excelInfo).Error if err != nil { return } for _, sheetInfo := range sheetParamsList { dataNum := len(sheetInfo.DataList) 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(), } tmpErr := o.Create(excelSheetInfo).Error if tmpErr != nil { err = tmpErr return } if dataNum > 0 { for k, _ := range sheetInfo.DataList { sheetInfo.DataList[k].ExcelSheetId = excelSheetInfo.ExcelSheetId sheetInfo.DataList[k].ExcelInfoId = excelSheetInfo.ExcelInfoId } tmpErr = o.CreateInBatches(sheetInfo.DataList, utils.MultiAddNum).Error if tmpErr != nil { err = tmpErr return } } } return } func SaveExcelInfoAndSheet(excelInfo *ExcelInfo, updateExcelInfoParam []string, sheetParamsList []AddExcelSheetParams) (err error) { o := global.DmSQL["data"].Begin() defer func() { if err != nil { _ = o.Rollback() } else { _ = o.Commit() } }() err = o.Model(excelInfo).Select(updateExcelInfoParam).Updates(excelInfo).Error if err != nil { return } sql := `DELETE FROM excel_sheet WHERE excel_info_id = ?` err = o.Exec(sql, excelInfo.ExcelInfoId).Error if err != nil { return } sql = `DELETE FROM excel_sheet_data WHERE excel_info_id = ?` err = o.Exec(sql, excelInfo.ExcelInfoId).Error if err != nil { return } for _, sheetInfo := range sheetParamsList { dataNum := len(sheetInfo.DataList) 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(), } tmpErr := o.Create(excelSheetInfo).Error if tmpErr != nil { err = tmpErr return } if dataNum > 0 { for k, _ := range sheetInfo.DataList { sheetInfo.DataList[k].ExcelSheetId = excelSheetInfo.ExcelSheetId sheetInfo.DataList[k].ExcelInfoId = excelSheetInfo.ExcelInfoId } tmpErr = o.CreateInBatches(sheetInfo.DataList, utils.MultiAddNum).Error if tmpErr != nil { err = tmpErr return } } } return } type BatchRefreshExcelReq struct { ExcelCodes []string `gorm:"column:excel_codes" description:"表格编码"` ReportId int `gorm:"column:report_id" description:"报告ID"` ReportChapterId int `gorm:"column:report_chapter_id" description:"报告章节ID"` Source string `gorm:"column:source" description:"来源，枚举值：report、english_report、smart_report"` } func GetExcelMaxSortByClassifyId(classifyId int, source int) (sort int, err error) { o := global.DmSQL["data"] sql := ` SELECT COALESCE(MAX(sort), 0) 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).Scan(&sort).Error return } func GetNoContentExcelListByExcelInfoIdList(excelInfoIdList []string) (items []*MyExcelInfoList, err error) { num := len(excelInfoIdList) if num <= 0 { return } o := global.DmSQL["data"] sql := ` SELECT * FROM excel_info WHERE excel_info_id in (` + utils.GetGormInReplace(num) + `) order by excel_info_id DESC ` err = o.Raw(sql, excelInfoIdList).Scan(&items).Error return } func GetNoContentExcelListByUserId(userIdList []int) (items []*MyExcelInfoList, err error) { num := len(userIdList) if num <= 0 { return } o := global.DmSQL["data"] sql := ` SELECT * FROM excel_info WHERE excel_info_id in (` + utils.GetGormInReplace(num) + `) order by excel_info_id DESC ` err = o.Raw(sql, userIdList).Scan(&items).Error return } func ModifyExcelInfoUserIdByCodeList(excelIdList []string, userId int, userName string) (err error) { num := len(excelIdList) if num <= 0 { return } o := global.DmSQL["data"] sql := `UPDATE excel_info SET sys_user_id=?,sys_user_real_name=? WHERE excel_info_id in (` + utils.GetGormInReplace(num) + `) ` err = o.Exec(sql, userId, userName, excelIdList).Error return } func ModifyExcelInfoUserIdByOldUserId(oldUserIdList []int, userId int, userName string) (err error) { num := len(oldUserIdList) if num <= 0 { return } o := global.DmSQL["data"] sql := `UPDATE excel_info SET sys_user_id=?,sys_user_real_name=? WHERE is_delete=0 AND sys_user_id in (` + utils.GetGormInReplace(num) + `) ` err = o.Exec(sql, userId, userName, oldUserIdList).Error return } type ExcelInfoDetail struct { ExcelInfoId int `gorm:"column:excel_info_id;primaryKey" orm:"column(excel_info_id);pk"` Source int `gorm:"column:source" description:"表格来源，1：excel插件的表格，2：自定义表格，默认：1"` ExcelType int `gorm:"column:excel_type" description:"表格类型，1：指标列，2：日期列，默认：1"` ExcelName string `gorm:"column:excel_name" description:"表格名称"` UniqueCode string `gorm:"column:unique_code" description:"表格唯一编码"` ExcelClassifyId int `gorm:"column:excel_classify_id" description:"表格分类id"` SysUserId int `gorm:"column:sys_user_id" description:"操作人id"` SysUserRealName string `gorm:"column:sys_user_real_name" description:"操作人真实姓名"` Content string `gorm:"column:content" description:"表格内容"` ExcelImage string `gorm:"column:excel_image" description:"表格图片"` FileUrl string `gorm:"column:file_url" description:"表格下载地址"` Sort int `gorm:"column:sort" description:"排序字段，数字越小越排前面"` IsDelete int `gorm:"column:is_delete" description:"是否删除，0：未删除，1：已删除"` ModifyTime time.Time `gorm:"column:modify_time" description:"最近修改日期"` CreateTime time.Time `gorm:"column:create_time" description:"创建日期"` TableData interface{} `gorm:"column:table_data" description:"表格内容"` CanEdit bool `gorm:"column:can_edit" description:"是否可编辑"` Editor string `gorm:"column:editor" description:"编辑人"` IsJoinPermission int `gorm:"column:is_join_permission" description:"是否加入权限管控，0：不加入；1：加入；默认：0"` HaveOperaAuth bool `gorm:"column:have_opera_auth" description:"是否有数据权限"` }