excel_info.go 10 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259
  1. package models
  2. import (
  3. "eta/eta_chart_lib/utils"
  4. "fmt"
  5. "github.com/beego/beego/v2/client/orm"
  6. "time"
  7. )
  8. // ExcelInfo excel表格详情表
  9. type ExcelInfo struct {
  10. ExcelInfoId int `orm:"column(excel_info_id);pk"`
  11. Source int `description:"表格来源,1:excel插件的表格,2:自定义表格,默认:1"`
  12. ExcelType int `description:"表格类型,1:指标列,2:日期列,默认:1"`
  13. ExcelName string `description:"表格名称"`
  14. UniqueCode string `description:"表格唯一编码"`
  15. ExcelClassifyId int `description:"表格分类id"`
  16. SysUserId int `description:"操作人id"`
  17. SysUserRealName string `description:"操作人真实姓名"`
  18. Content string `description:"表格内容"`
  19. ExcelImage string `description:"表格图片"`
  20. FileUrl string `description:"表格下载地址"`
  21. Sort int `description:"排序字段,数字越小越排前面"`
  22. IsDelete int `description:"是否删除,0:未删除,1:已删除"`
  23. ModifyTime time.Time `description:"最近修改日期"`
  24. CreateTime time.Time `description:"创建日期"`
  25. }
  26. // Update 更新 excel表格基础信息
  27. func (excelInfo *ExcelInfo) Update(cols []string) (err error) {
  28. o := orm.NewOrmUsingDB("data")
  29. _, err = o.Update(excelInfo, cols...)
  30. return
  31. }
  32. type MyExcelInfoList struct {
  33. ExcelInfoId int `orm:"column(excel_info_id);pk"`
  34. ExcelName string `description:"表格名称"`
  35. UniqueCode string `description:"表格唯一编码"`
  36. ExcelClassifyId int `description:"表格分类id"`
  37. SysUserId int `description:"操作人id"`
  38. SysUserRealName string `description:"操作人真实姓名"`
  39. ExcelImage string `description:"表格图片"`
  40. FileUrl string `description:"表格下载地址"`
  41. Sort int `description:"排序字段,数字越小越排前面"`
  42. ModifyTime time.Time `description:"最近修改日期"`
  43. CreateTime time.Time `description:"创建日期"`
  44. }
  45. // AddExcelInfo 新增表格
  46. func AddExcelInfo(excelInfo *ExcelInfo) (err error) {
  47. o := orm.NewOrmUsingDB("data")
  48. // 表格信息入库
  49. lastId, err := o.Insert(excelInfo)
  50. if err != nil {
  51. return
  52. }
  53. excelInfo.ExcelInfoId = int(lastId)
  54. return
  55. }
  56. // EditExcelInfo 编辑表格
  57. func EditExcelInfo(excelInfo *ExcelInfo, updateExcelInfoParams []string) (err error) {
  58. o := orm.NewOrmUsingDB("data")
  59. // ETA表格信息变更
  60. _, err = o.Update(excelInfo, updateExcelInfoParams...)
  61. return
  62. }
  63. // GetExcelInfoById 根据id 获取eta表格详情
  64. func GetExcelInfoById(excelInfoId int) (item *ExcelInfo, err error) {
  65. o := orm.NewOrmUsingDB("data")
  66. sql := ` SELECT * FROM excel_info WHERE excel_info_id=? AND is_delete=0 `
  67. err = o.Raw(sql, excelInfoId).QueryRow(&item)
  68. return
  69. }
  70. func GetExcelInfoViewById(excelInfoId int) (item *ExcelInfoView, err error) {
  71. o := orm.NewOrmUsingDB("data")
  72. sql := ` SELECT * FROM excel_info WHERE excel_info_id=? AND is_delete=0 `
  73. err = o.Raw(sql, excelInfoId).QueryRow(&item)
  74. return
  75. }
  76. func GetExcelInfoCountByCondition(condition string, pars []interface{}) (count int, err error) {
  77. o := orm.NewOrmUsingDB("data")
  78. sql := ` SELECT COUNT(1) AS count FROM excel_info WHERE 1=1 AND is_delete=0 `
  79. if condition != "" {
  80. sql += condition
  81. }
  82. err = o.Raw(sql, pars).QueryRow(&count)
  83. return
  84. }
  85. func GetExcelInfoByCondition(condition string, pars []interface{}) (item *ExcelInfo, err error) {
  86. o := orm.NewOrmUsingDB("data")
  87. sql := ` SELECT * FROM excel_info WHERE 1=1 AND is_delete=0 `
  88. if condition != "" {
  89. sql += condition
  90. }
  91. err = o.Raw(sql, pars).QueryRow(&item)
  92. return
  93. }
  94. // GetNextExcelInfo 根据分类id获取下一个excel表格
  95. func GetNextExcelInfo(classifyId int) (item *ExcelInfo, err error) {
  96. o := orm.NewOrmUsingDB("data")
  97. sql := ` SELECT b.* FROM excel_classify AS a
  98. INNER JOIN excel_info AS b ON a.excel_classify_id=b.excel_classify_id
  99. WHERE a.excel_classify_id>? AND is_delete=0
  100. ORDER BY a.excel_classify_id ASC
  101. LIMIT 1 `
  102. err = o.Raw(sql, classifyId).QueryRow(&item)
  103. return
  104. }
  105. // EditExcelInfoImage 修改excel表格的图片
  106. func EditExcelInfoImage(excelInfoId int, imageUrl string) (err error) {
  107. o := orm.NewOrmUsingDB("data")
  108. sql := ` UPDATE excel_info SET excel_image=?, modify_time = NOW() WHERE excel_info_id = ? AND is_delete=0 `
  109. _, err = o.Raw(sql, imageUrl, excelInfoId).Exec()
  110. if err != nil {
  111. fmt.Println("EditExcelInfoImage Err:", err.Error())
  112. return err
  113. }
  114. return
  115. }
  116. // GetExcelInfoByUniqueCode 根据unique_code来获取excel表格详情
  117. func GetExcelInfoByUniqueCode(uniqueCode string) (item *ExcelInfoView, err error) {
  118. o := orm.NewOrmUsingDB("data")
  119. sql := ` SELECT * FROM excel_info WHERE unique_code=? AND is_delete=0 `
  120. err = o.Raw(sql, uniqueCode).QueryRow(&item)
  121. return
  122. }
  123. // GetFirstExcelInfoByClassifyId 获取当前分类下,且排序数相同 的排序第一条的数据
  124. func GetFirstExcelInfoByClassifyId(classifyId int) (item *ExcelInfo, err error) {
  125. o := orm.NewOrmUsingDB("data")
  126. sql := ` SELECT * FROM excel_info WHERE excel_classify_id=? AND is_delete=0 order by sort asc,excel_info_id asc limit 1`
  127. err = o.Raw(sql, classifyId).QueryRow(&item)
  128. return
  129. }
  130. // UpdateExcelInfoSortByClassifyId 根据表格id更新排序
  131. func UpdateExcelInfoSortByClassifyId(classifyId, nowSort, prevExcelInfoId int, updateSort string) (err error) {
  132. o := orm.NewOrmUsingDB("data")
  133. sql := ` update excel_info set sort = ` + updateSort + ` WHERE excel_classify_id=? and sort > ? AND is_delete=0 `
  134. if prevExcelInfoId > 0 {
  135. sql += ` or (excel_info_id > ` + fmt.Sprint(prevExcelInfoId) + ` and sort = ` + fmt.Sprint(nowSort) + `)`
  136. }
  137. _, err = o.Raw(sql, classifyId, nowSort).Exec()
  138. return
  139. }
  140. type ExcelInfoView struct {
  141. ExcelInfoId int `orm:"column(excel_info_id);pk"`
  142. Source int `description:"表格来源,1:excel插件的表格,2:自定义表格,默认:1"`
  143. ExcelType int `description:"表格类型,1:指标列,2:日期列,默认:1"`
  144. ExcelName string `description:"表格名称"`
  145. UniqueCode string `description:"表格唯一编码"`
  146. ExcelClassifyId int `description:"表格分类id"`
  147. SysUserId int `description:"操作人id"`
  148. SysUserRealName string `description:"操作人真实姓名"`
  149. Content string `description:"表格内容"`
  150. ExcelImage string `description:"表格图片"`
  151. FileUrl string `description:"表格下载地址"`
  152. Sort int `description:"排序字段,数字越小越排前面"`
  153. IsDelete int `description:"是否删除,0:未删除,1:已删除" json:"-"`
  154. ModifyTime time.Time `description:"最近修改日期"`
  155. CreateTime time.Time `description:"创建日期"`
  156. SourcesFrom string `description:"图表来源"`
  157. }
  158. // GetExcelInfoByClassifyIdAndName 根据分类id和表格名获取表格信息
  159. func GetExcelInfoByClassifyIdAndName(classifyId int, excelName string) (item *ExcelInfo, err error) {
  160. o := orm.NewOrmUsingDB("data")
  161. sql := ` SELECT * FROM excel_info WHERE excel_classify_id = ? and excel_name=? AND is_delete=0 `
  162. err = o.Raw(sql, classifyId, excelName).QueryRow(&item)
  163. return
  164. }
  165. // GetExcelInfoListByUniqueCodeSlice 根据表格编码获取表格列表数据
  166. func GetExcelInfoListByUniqueCodeSlice(uniqueCodeSlice []string) (total int64, items []*ExcelInfo, err error) {
  167. num := len(uniqueCodeSlice)
  168. if num <= 0 {
  169. return
  170. }
  171. o := orm.NewOrmUsingDB("data")
  172. sql := ` SELECT * FROM excel_info WHERE unique_code in ("` + utils.GetOrmInReplace(num) + `") AND is_delete=0 `
  173. total, err = o.Raw(sql, uniqueCodeSlice).QueryRows(&items)
  174. return
  175. }
  176. // GetExcelListByCondition 获取excel表格列表数据
  177. func GetExcelListByCondition(condition string, pars []interface{}, startSize, pageSize int) (item []*MyExcelInfoList, err error) {
  178. o := orm.NewOrmUsingDB("data")
  179. sql := ` SELECT * FROM excel_info WHERE 1=1 AND is_delete=0 `
  180. if condition != "" {
  181. sql += condition
  182. }
  183. //sql += " ORDER BY sort ASC,chart_info_id DESC LIMIT ?,? "
  184. sql += " ORDER BY create_time DESC LIMIT ?,? "
  185. _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&item)
  186. return
  187. }
  188. // GetNoContentExcelListByCondition 获取没有content的excel表格列表数据
  189. func GetNoContentExcelListByCondition(condition string, pars []interface{}, startSize, pageSize int) (item []*MyExcelInfoList, err error) {
  190. o := orm.NewOrmUsingDB("data")
  191. sql := ` SELECT excel_info_id,excel_name,unique_code,excel_classify_id,sys_user_id,sys_user_real_name,excel_image,file_url,sort,create_time,modify_time
  192. FROM excel_info WHERE 1=1 AND is_delete=0 `
  193. if condition != "" {
  194. sql += condition
  195. }
  196. //sql += " ORDER BY sort ASC,chart_info_id DESC LIMIT ?,? "
  197. sql += " ORDER BY create_time DESC LIMIT ?,? "
  198. _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&item)
  199. return
  200. }
  201. func GetExcelListCountByCondition(condition string, pars []interface{}) (count int, err error) {
  202. o := orm.NewOrmUsingDB("data")
  203. sql := ` SELECT COUNT(1) AS count FROM excel_info WHERE 1=1 AND is_delete=0 `
  204. if condition != "" {
  205. sql += condition
  206. }
  207. err = o.Raw(sql, pars).QueryRow(&count)
  208. return
  209. }
  210. // GetExcelViewInfoByExcelInfoId 根据excelInfoId 获取ETA表格详情
  211. func GetExcelViewInfoByExcelInfoId(excelInfoId int) (item *MyExcelInfoList, err error) {
  212. o := orm.NewOrmUsingDB("data")
  213. sql := ` SELECT * FROM excel_info WHERE excel_info_id = ? AND is_delete=0 `
  214. err = o.Raw(sql, excelInfoId).QueryRow(&item)
  215. return
  216. }
  217. // GetExcelInfoCountByClassifyId 根据分类id获取名下表格数量
  218. func GetExcelInfoCountByClassifyId(classifyId int) (total int64, err error) {
  219. o := orm.NewOrmUsingDB("data")
  220. sql := ` SELECT count(1) total FROM excel_info WHERE excel_classify_id = ? AND is_delete=0 `
  221. err = o.Raw(sql, classifyId).QueryRow(&total)
  222. return
  223. }
  224. func GetExcelInfoListByCondition(condition string, pars []interface{}) (items []*ExcelInfo, err error) {
  225. o := orm.NewOrmUsingDB("data")
  226. sql := ` SELECT * FROM excel_info WHERE 1=1 AND is_delete=0 `
  227. if condition != "" {
  228. sql += condition
  229. }
  230. sql += ` ORDER BY sort asc, excel_info_id asc`
  231. _, err = o.Raw(sql, pars).QueryRows(&items)
  232. return
  233. }