excel_info.go 9.2 KB

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