package data_manage import ( "fmt" "github.com/beego/beego/v2/client/orm" "time" ) // ExcelInfo excel表格详情表 type ExcelInfo 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:"操作人真实姓名"` 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:"创建日期"` } // 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:"创建日期"` } // AddExcelInfo 新增表格 func AddExcelInfo(excelInfo *ExcelInfo) (err error) { o := orm.NewOrmUsingDB("data") // 表格信息入库 lastId, err := o.Insert(excelInfo) if err != nil { return } excelInfo.ExcelInfoId = int(lastId) return } // EditExcelInfo 编辑表格 func EditExcelInfo(excelInfo *ExcelInfo, updateExcelInfoParams []string) (err error) { o := orm.NewOrmUsingDB("data") // ETA表格信息变更 _, err = o.Update(excelInfo, updateExcelInfoParams...) 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,start_date 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() (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 FROM excel_info where is_delete=0 ORDER BY sort asc,create_time desc ` _, err = o.Raw(sql).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 } 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 GetExcelInfoByCondition(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 } err = o.Raw(sql, pars).QueryRow(&item) 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 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 ORDER BY a.sort ASC,b.sort asc,b.create_time desc LIMIT 1 ` err = o.Raw(sql, classifySort, classifySort, classifyId).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 asc limit 1` err = o.Raw(sql, classifyId).QueryRow(&item) return } // UpdateExcelInfoSortByClassifyId 根据表格id更新排序 func UpdateExcelInfoSortByClassifyId(classifyId, nowSort, prevExcelInfoId int, updateSort string) (err error) { o := orm.NewOrmUsingDB("data") sql := ` update excel_info set sort = ` + updateSort + ` WHERE excel_classify_id=? and sort > ? AND is_delete=0 ` if prevExcelInfoId > 0 { sql += ` or (excel_info_id > ` + fmt.Sprint(prevExcelInfoId) + ` and sort = ` + fmt.Sprint(nowSort) + `)` } _, err = o.Raw(sql, classifyId, 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 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 } 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 } // GetMyExcelListByAdminId 根据操作人id获取表格列表 func GetMyExcelListByAdminId(adminId int) (item []*MyChartView, err error) { o := orm.NewOrmUsingDB("data") //sql := ` SELECT * FROM my_chart WHERE 1=1 AND admin_id=? ` sql := ` SELECT a.*,GROUP_CONCAT(c.my_chart_classify_id SEPARATOR ',') AS my_chart_classify_id FROM my_chart AS a LEFT JOIN my_chart_classify_mapping AS b ON a.my_chart_id=b.my_chart_id AND a.admin_id=b.admin_id LEFT JOIN my_chart_classify AS c ON b.my_chart_classify_id=c.my_chart_classify_id AND b.admin_id=c.admin_id WHERE 1=1 AND a.admin_id=? AND a.source=2 GROUP BY a.chart_info_id ` _, err = o.Raw(sql, adminId).QueryRows(&item) return } // GetExcelViewInfoByExcelInfoId 根据excelInfoId 获取ETA表格详情 func GetExcelViewInfoByExcelInfoId(excelInfoId int) (item *MyExcelInfoList, 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 } // 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 }