excel_info.go 27 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721
  1. package excel
  2. import (
  3. "eta/eta_mobile/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:自定义表格,3:混合表格,4:自定义分析,默认: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. IsJoinPermission int `description:"是否加入权限管控,0:不加入;1:加入;默认:0"`
  26. ParentId int `description:"表格的父级id"`
  27. BalanceType int `description:"平衡表类型:0 动态表,1静态表"`
  28. UpdateUserId int `description:"更新人id"`
  29. UpdateUserRealName string `description:"更新人真实姓名"`
  30. RelExcelInfoId int `description:"平衡表里静态表关联的动态表excel id"`
  31. VersionName string `description:"静态表版本名称"`
  32. SourcesFrom string `description:"图表来源"`
  33. }
  34. // Update 更新 excel表格基础信息
  35. func (excelInfo *ExcelInfo) Update(cols []string) (err error) {
  36. o := orm.NewOrmUsingDB("data")
  37. _, err = o.Update(excelInfo, cols...)
  38. return
  39. }
  40. type MyExcelInfoList struct {
  41. ExcelInfoId int `orm:"column(excel_info_id);pk"`
  42. Source int `description:"表格来源,1:excel插件的表格,2:自定义表格,默认:1"`
  43. ExcelType int `description:"表格类型,1:指标列,2:日期列,默认:1"`
  44. ExcelName string `description:"表格名称"`
  45. UniqueCode string `description:"表格唯一编码"`
  46. ExcelClassifyId int `description:"表格分类id"`
  47. SysUserId int `description:"操作人id"`
  48. SysUserRealName string `description:"操作人真实姓名"`
  49. ExcelImage string `description:"表格图片"`
  50. FileUrl string `description:"表格下载地址"`
  51. Sort int `description:"排序字段,数字越小越排前面"`
  52. ModifyTime time.Time `description:"最近修改日期"`
  53. CreateTime time.Time `description:"创建日期"`
  54. IsJoinPermission int `description:"是否加入权限管控,0:不加入;1:加入;默认:0"`
  55. HaveOperaAuth bool `description:"是否有数据权限"`
  56. Button ExcelInfoDetailButton `description:"操作权限"`
  57. CanEdit bool `description:"是否可编辑"`
  58. Editor string `description:"编辑人"`
  59. }
  60. // AddExcelInfo 新增表格
  61. func AddExcelInfo(excelInfo *ExcelInfo, excelEdbMappingList []*ExcelEdbMapping) (err error) {
  62. o, err := orm.NewOrmUsingDB("data").Begin()
  63. if err != nil {
  64. return
  65. }
  66. defer func() {
  67. if err != nil {
  68. _ = o.Rollback()
  69. } else {
  70. _ = o.Commit()
  71. }
  72. }()
  73. // 表格信息入库
  74. lastId, err := o.Insert(excelInfo)
  75. if err != nil {
  76. return
  77. }
  78. excelInfo.ExcelInfoId = int(lastId)
  79. // excel与指标的关联关系
  80. dataNum := len(excelEdbMappingList)
  81. if dataNum > 0 {
  82. for k, v := range excelEdbMappingList {
  83. v.ExcelInfoId = excelInfo.ExcelInfoId
  84. excelEdbMappingList[k] = v
  85. }
  86. _, err = o.InsertMulti(dataNum, excelEdbMappingList)
  87. }
  88. return
  89. }
  90. // EditExcelInfo 编辑表格
  91. func EditExcelInfo(excelInfo *ExcelInfo, updateExcelInfoParams []string, excelEdbMappingList []*ExcelEdbMapping) (err error) {
  92. o, err := orm.NewOrmUsingDB("data").Begin()
  93. if err != nil {
  94. return
  95. }
  96. defer func() {
  97. if err != nil {
  98. _ = o.Rollback()
  99. } else {
  100. _ = o.Commit()
  101. }
  102. }()
  103. // ETA表格信息变更
  104. _, err = o.Update(excelInfo, updateExcelInfoParams...)
  105. if err != nil {
  106. return
  107. }
  108. // 删除关系表
  109. sql := `DELETE FROM excel_edb_mapping WHERE excel_info_id=? `
  110. _, err = o.Raw(sql, excelInfo.ExcelInfoId).Exec()
  111. // excel与指标的关联关系
  112. dataNum := len(excelEdbMappingList)
  113. if dataNum > 0 {
  114. for k, v := range excelEdbMappingList {
  115. v.ExcelInfoId = excelInfo.ExcelInfoId
  116. excelEdbMappingList[k] = v
  117. }
  118. _, err = o.InsertMulti(dataNum, excelEdbMappingList)
  119. }
  120. return
  121. }
  122. // GetExcelInfoAll 获取所有表格列表,用于分类展示
  123. func GetExcelInfoAll() (items []*ExcelClassifyItems, err error) {
  124. o := orm.NewOrmUsingDB("data")
  125. sql := ` SELECT excel_info_id,excel_classify_id,excel_name AS excel_classify_name,
  126. unique_code,sys_user_id,sys_user_real_name,is_join_permission
  127. FROM excel_info where is_delete=0 ORDER BY sort asc,create_time ASC `
  128. _, err = o.Raw(sql).QueryRows(&items)
  129. return
  130. }
  131. // GetNoContentExcelInfoAll 获取不含content的表格列表 用于分类展示
  132. func GetNoContentExcelInfoAll(source, userId int) (items []*ExcelClassifyItems, err error) {
  133. o := orm.NewOrmUsingDB("data")
  134. sql := ` SELECT excel_info_id,excel_classify_id,excel_name AS excel_classify_name,
  135. unique_code,sys_user_id,sys_user_real_name,sort,is_join_permission
  136. FROM excel_info where is_delete=0 AND source = ? `
  137. pars := []interface{}{source}
  138. if userId > 0 {
  139. sql += ` AND sys_user_id = ? `
  140. pars = append(pars, userId)
  141. }
  142. sql += ` ORDER BY sort asc,excel_info_id desc `
  143. _, err = o.Raw(sql, pars...).QueryRows(&items)
  144. return
  145. }
  146. // GetAllExcelInfoBySource 根据来源获取包含content的表格列表
  147. func GetAllExcelInfoBySource(source int) (items []*ExcelInfo, err error) {
  148. o := orm.NewOrmUsingDB("data")
  149. sql := ` SELECT * FROM excel_info where is_delete=0 AND source = ? ORDER BY sort asc,create_time desc `
  150. _, err = o.Raw(sql, source).QueryRows(&items)
  151. return
  152. }
  153. // GetExcelInfoById 根据id 获取eta表格详情
  154. func GetExcelInfoById(excelInfoId int) (item *ExcelInfo, err error) {
  155. o := orm.NewOrmUsingDB("data")
  156. sql := ` SELECT * FROM excel_info WHERE excel_info_id=? AND is_delete=0 `
  157. err = o.Raw(sql, excelInfoId).QueryRow(&item)
  158. return
  159. }
  160. // GetExcelInfoByUnicode 编码获取表格
  161. func GetExcelInfoByUnicode(unicode string) (item *ExcelInfo, err error) {
  162. o := orm.NewOrmUsingDB("data")
  163. sql := ` SELECT * FROM excel_info WHERE unique_code = ? AND is_delete = 0 `
  164. err = o.Raw(sql, unicode).QueryRow(&item)
  165. return
  166. }
  167. func GetExcelInfoViewById(excelInfoId int) (item *ExcelInfoView, err error) {
  168. o := orm.NewOrmUsingDB("data")
  169. sql := ` SELECT * FROM excel_info WHERE excel_info_id=? AND is_delete=0 `
  170. err = o.Raw(sql, excelInfoId).QueryRow(&item)
  171. return
  172. }
  173. func GetExcelInfoCountByCondition(condition string, pars []interface{}) (count int, err error) {
  174. o := orm.NewOrmUsingDB("data")
  175. sql := ` SELECT COUNT(1) AS count FROM excel_info WHERE 1=1 AND is_delete=0 `
  176. if condition != "" {
  177. sql += condition
  178. }
  179. err = o.Raw(sql, pars).QueryRow(&count)
  180. return
  181. }
  182. func GetNoContentExcelInfoListByCondition(condition string, pars []interface{}, startSize, pageSize int) (items []*ExcelClassifyItems, err error) {
  183. o := orm.NewOrmUsingDB("data")
  184. sql := `SELECT excel_info_id,excel_classify_id,excel_name AS excel_classify_name,
  185. unique_code,sys_user_id,sys_user_real_name,sort,is_join_permission FROM excel_info WHERE 1=1 `
  186. if condition != "" {
  187. sql += condition
  188. }
  189. sql += ` AND is_delete=0 ORDER BY excel_info_id DESC LIMIT ?,? `
  190. _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
  191. return
  192. }
  193. func GetExcelInfoByCondition(condition string, pars []interface{}) (item *ExcelInfo, err error) {
  194. o := orm.NewOrmUsingDB("data")
  195. sql := ` SELECT * FROM excel_info WHERE 1=1 AND is_delete=0 `
  196. if condition != "" {
  197. sql += condition
  198. }
  199. err = o.Raw(sql, pars).QueryRow(&item)
  200. return
  201. }
  202. // GetNextExcelInfoByCondition 根据条件获取下一个表格
  203. func GetNextExcelInfoByCondition(condition string, pars []interface{}) (item *ExcelInfo, err error) {
  204. o := orm.NewOrmUsingDB("data")
  205. sql := ` SELECT * FROM excel_info WHERE 1=1 AND is_delete=0 `
  206. if condition != "" {
  207. sql += condition
  208. }
  209. sql += " ORDER BY sort asc , create_time desc LIMIT 1 "
  210. err = o.Raw(sql, pars).QueryRow(&item)
  211. return
  212. }
  213. // GetNextExcelInfo 根据分类id获取下一个excel表格
  214. func GetNextExcelInfo(classifyId, classifySort, source int) (item *ExcelInfo, err error) {
  215. o := orm.NewOrmUsingDB("data")
  216. sql := ` SELECT b.* FROM excel_classify AS a
  217. INNER JOIN excel_info AS b ON a.excel_classify_id=b.excel_classify_id
  218. WHERE (a.sort>? OR (a.sort=? and a.excel_classify_id>?) ) AND a.is_delete=0 AND b.is_delete=0
  219. AND a.source = ? AND b.source = ?
  220. ORDER BY a.sort ASC,b.sort asc,b.create_time desc
  221. LIMIT 1 `
  222. err = o.Raw(sql, classifySort, classifySort, classifyId, source, source).QueryRow(&item)
  223. return
  224. }
  225. // EditExcelInfoImage 修改excel表格的图片
  226. func EditExcelInfoImage(excelInfoId int, imageUrl string) (err error) {
  227. o := orm.NewOrmUsingDB("data")
  228. sql := ` UPDATE excel_info SET excel_image=?, modify_time = NOW() WHERE excel_info_id = ? AND is_delete=0 `
  229. _, err = o.Raw(sql, imageUrl, excelInfoId).Exec()
  230. if err != nil {
  231. fmt.Println("EditExcelInfoImage Err:", err.Error())
  232. return err
  233. }
  234. return
  235. }
  236. // GetExcelInfoByUniqueCode 根据unique_code来获取excel表格详情
  237. func GetExcelInfoByUniqueCode(uniqueCode string) (item *ExcelInfo, err error) {
  238. o := orm.NewOrmUsingDB("data")
  239. sql := ` SELECT * FROM excel_info WHERE unique_code=? AND is_delete=0 `
  240. err = o.Raw(sql, uniqueCode).QueryRow(&item)
  241. return
  242. }
  243. func GetExcelInfoListByCondition(condition string, pars []interface{}) (items []*ExcelInfo, err error) {
  244. o := orm.NewOrmUsingDB("data")
  245. sql := ` SELECT * FROM excel_info WHERE 1=1 AND is_delete=0 `
  246. if condition != "" {
  247. sql += condition
  248. }
  249. sql += ` ORDER BY sort asc, excel_info_id asc`
  250. _, err = o.Raw(sql, pars).QueryRows(&items)
  251. return
  252. }
  253. // GetFirstExcelInfoByClassifyId 获取当前分类下,且排序数相同 的排序第一条的数据
  254. func GetFirstExcelInfoByClassifyId(classifyId int) (item *ExcelInfo, err error) {
  255. o := orm.NewOrmUsingDB("data")
  256. sql := ` SELECT * FROM excel_info WHERE excel_classify_id=? AND is_delete=0 order by sort asc,excel_info_id desc limit 1`
  257. err = o.Raw(sql, classifyId).QueryRow(&item)
  258. return
  259. }
  260. // UpdateExcelInfoSortByClassifyId 根据表格id更新排序
  261. func UpdateExcelInfoSortByClassifyId(classifyId, nowSort, prevExcelInfoId int, updateSort string, source int) (err error) {
  262. o := orm.NewOrmUsingDB("data")
  263. sql := ` update excel_info set sort = ` + updateSort + ` WHERE excel_classify_id=? AND source=? AND is_delete=0 AND ( sort > ? `
  264. // todo 前一个兄弟节点后移
  265. if prevExcelInfoId > 0 {
  266. sql += ` or (excel_info_id < ` + fmt.Sprint(prevExcelInfoId) + ` and sort = ` + fmt.Sprint(nowSort) + `)`
  267. }
  268. sql += `)`
  269. _, err = o.Raw(sql, classifyId, source, nowSort).Exec()
  270. return
  271. }
  272. type ExcelInfoView struct {
  273. ExcelInfoId int `orm:"column(excel_info_id);pk"`
  274. ExcelName string `description:"来源名称"`
  275. ExcelClassifyId int `description:"表格分类id"`
  276. ExcelClassifyName string `description:"表格名称"`
  277. SysUserId int
  278. SysUserRealName string
  279. UniqueCode string `description:"表格唯一编码"`
  280. CreateTime time.Time
  281. ModifyTime time.Time
  282. DateType int `description:"日期类型:1:00年至今,2:10年至今,3:15年至今,4:年初至今,5:自定义时间"`
  283. StartDate string `description:"自定义开始日期"`
  284. EndDate string `description:"自定义结束日期"`
  285. IsSetName int `description:"设置名称"`
  286. EdbInfoIds string `description:"指标id"`
  287. ExcelType int `description:"生成样式:1:曲线图,2:季节性图"`
  288. Calendar string `description:"公历/农历"`
  289. SeasonStartDate string `description:"季节性图开始日期"`
  290. SeasonEndDate string `description:"季节性图开始日期"`
  291. ExcelImage string `description:"表格图片"`
  292. Sort int `description:"排序字段,数字越小越排前面"`
  293. IsAdd bool `description:"true:已加入我的图库,false:未加入我的图库"`
  294. MyExcelId int
  295. MyExcelClassifyId string `description:"我的表格分类,多个用逗号隔开"`
  296. ExcelClassify []*ExcelClassifyView
  297. EdbEndDate string `description:"指标最新更新日期"`
  298. LeftMin string `description:"表格左侧最小值"`
  299. LeftMax string `description:"表格左侧最大值"`
  300. RightMin string `description:"表格右侧最小值"`
  301. RightMax string `description:"表格右侧最大值"`
  302. }
  303. // GetExcelInfoByClassifyIdAndName 根据分类id和表格名获取表格信息
  304. func GetExcelInfoByClassifyIdAndName(classifyId int, excelName string) (item *ExcelInfo, err error) {
  305. o := orm.NewOrmUsingDB("data")
  306. sql := ` SELECT * FROM excel_info WHERE excel_classify_id = ? and excel_name=? AND is_delete=0 `
  307. err = o.Raw(sql, classifyId, excelName).QueryRow(&item)
  308. return
  309. }
  310. // GetNoContentExcelListByCondition 获取没有content的excel表格列表数据
  311. func GetNoContentExcelListByCondition(condition string, pars []interface{}, startSize, pageSize int) (item []*MyExcelInfoList, err error) {
  312. o := orm.NewOrmUsingDB("data")
  313. 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
  314. FROM excel_info WHERE 1=1 AND is_delete=0 `
  315. if condition != "" {
  316. sql += condition
  317. }
  318. //sql += " ORDER BY sort ASC,chart_info_id DESC LIMIT ?,? "
  319. sql += " ORDER BY create_time DESC LIMIT ?,? "
  320. _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&item)
  321. return
  322. }
  323. func GetExcelListCountByCondition(condition string, pars []interface{}) (count int, err error) {
  324. o := orm.NewOrmUsingDB("data")
  325. sql := ` SELECT COUNT(1) AS count FROM excel_info WHERE 1=1 AND is_delete=0 `
  326. if condition != "" {
  327. sql += condition
  328. }
  329. err = o.Raw(sql, pars).QueryRow(&count)
  330. return
  331. }
  332. // GetExcelViewInfoByExcelInfoId 根据excelInfoId 获取ETA表格详情
  333. func GetExcelViewInfoByExcelInfoId(excelInfoId int) (item *MyExcelInfoList, err error) {
  334. o := orm.NewOrmUsingDB("data")
  335. 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 `
  336. err = o.Raw(sql, excelInfoId).QueryRow(&item)
  337. return
  338. }
  339. // GetExcelInfoCountByClassifyId 根据分类id获取名下表格数量
  340. func GetExcelInfoCountByClassifyId(classifyId int) (total int64, err error) {
  341. o := orm.NewOrmUsingDB("data")
  342. sql := ` SELECT count(1) total FROM excel_info WHERE excel_classify_id = ? AND is_delete=0 `
  343. err = o.Raw(sql, classifyId).QueryRow(&total)
  344. return
  345. }
  346. // UpdateExcelInfoClassifyId 更改表格分类
  347. func UpdateExcelInfoClassifyId(classifyId, excelInfoId int) (err error) {
  348. o := orm.NewOrmUsingDB("data")
  349. sql := ` update excel_info set excel_classify_id = ? WHERE excel_info_id=? `
  350. _, err = o.Raw(sql, classifyId, excelInfoId).Exec()
  351. return
  352. }
  353. // GetNoContentExcelInfoByName 根据名称 获取eta表格详情
  354. func GetNoContentExcelInfoByName(excelName string, source int) (item *MyExcelInfoList, err error) {
  355. o := orm.NewOrmUsingDB("data")
  356. 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
  357. FROM excel_info WHERE excel_name = ? AND source = ? AND is_delete=0 `
  358. err = o.Raw(sql, excelName, source).QueryRow(&item)
  359. return
  360. }
  361. // GetNoContentExcelInfoByUniqueCode 根据unique_code来获取excel表格详情
  362. func GetNoContentExcelInfoByUniqueCode(uniqueCode string) (item *MyExcelInfoList, err error) {
  363. o := orm.NewOrmUsingDB("data")
  364. 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
  365. FROM excel_info WHERE unique_code=? AND is_delete=0 `
  366. err = o.Raw(sql, uniqueCode).QueryRow(&item)
  367. return
  368. }
  369. // GetNoContentExcelInfoByExcelId 根据表格id来获取excel表格详情
  370. func GetNoContentExcelInfoByExcelId(excelInfoId int) (item *MyExcelInfoList, err error) {
  371. o := orm.NewOrmUsingDB("data")
  372. 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
  373. FROM excel_info WHERE excel_info_id=? AND is_delete=0 `
  374. err = o.Raw(sql, excelInfoId).QueryRow(&item)
  375. return
  376. }
  377. // AddExcelInfoAndSheet 新增excel
  378. func AddExcelInfoAndSheet(excelInfo *ExcelInfo, sheetParamsList []AddExcelSheetParams) (err error) {
  379. o, err := orm.NewOrmUsingDB("data").Begin()
  380. if err != nil {
  381. return
  382. }
  383. defer func() {
  384. if err != nil {
  385. _ = o.Rollback()
  386. } else {
  387. _ = o.Commit()
  388. }
  389. }()
  390. // 表格信息入库
  391. lastId, err := o.Insert(excelInfo)
  392. if err != nil {
  393. return
  394. }
  395. excelInfo.ExcelInfoId = int(lastId)
  396. // sheet信息入库
  397. for _, sheetInfo := range sheetParamsList {
  398. dataNum := len(sheetInfo.DataList)
  399. //sheet信息入库
  400. excelSheetInfo := &ExcelSheet{
  401. ExcelSheetId: 0,
  402. ExcelInfoId: excelInfo.ExcelInfoId,
  403. SheetName: sheetInfo.SheetName,
  404. PageNum: dataNum,
  405. Index: sheetInfo.Index,
  406. Sort: sheetInfo.Sort,
  407. Config: sheetInfo.Config,
  408. CalcChain: sheetInfo.CalcChain,
  409. ModifyTime: time.Now(),
  410. CreateTime: time.Now(),
  411. }
  412. sheetId, tmpErr := o.Insert(excelSheetInfo)
  413. if tmpErr != nil {
  414. err = tmpErr
  415. return
  416. }
  417. excelSheetInfo.ExcelSheetId = int(sheetId)
  418. // data信息入库
  419. if dataNum > 0 {
  420. for k, _ := range sheetInfo.DataList {
  421. sheetInfo.DataList[k].ExcelSheetId = excelSheetInfo.ExcelSheetId
  422. sheetInfo.DataList[k].ExcelInfoId = excelSheetInfo.ExcelInfoId
  423. }
  424. _, tmpErr = o.InsertMulti(dataNum, sheetInfo.DataList)
  425. if tmpErr != nil {
  426. err = tmpErr
  427. return
  428. }
  429. }
  430. }
  431. return
  432. }
  433. // SaveExcelInfoAndSheet 编辑保存
  434. func SaveExcelInfoAndSheet(excelInfo *ExcelInfo, updateExcelInfoParam []string, sheetParamsList []AddExcelSheetParams) (err error) {
  435. o, err := orm.NewOrmUsingDB("data").Begin()
  436. if err != nil {
  437. return
  438. }
  439. defer func() {
  440. if err != nil {
  441. _ = o.Rollback()
  442. } else {
  443. _ = o.Commit()
  444. }
  445. }()
  446. // 表格信息入库
  447. _, err = o.Update(excelInfo, updateExcelInfoParam...)
  448. if err != nil {
  449. return
  450. }
  451. // 先删除历史的sheet信息
  452. sql := `DELETE FROM excel_sheet WHERE excel_info_id = ?`
  453. _, err = o.Raw(sql, excelInfo.ExcelInfoId).Exec()
  454. if err != nil {
  455. return
  456. }
  457. // 再删除历史sheet中的cell data信息
  458. sql = `DELETE FROM excel_sheet_data WHERE excel_info_id = ?`
  459. _, err = o.Raw(sql, excelInfo.ExcelInfoId).Exec()
  460. if err != nil {
  461. return
  462. }
  463. // sheet信息入库
  464. for _, sheetInfo := range sheetParamsList {
  465. dataNum := len(sheetInfo.DataList)
  466. //sheet信息入库
  467. excelSheetInfo := &ExcelSheet{
  468. ExcelSheetId: 0,
  469. ExcelInfoId: excelInfo.ExcelInfoId,
  470. SheetName: sheetInfo.SheetName,
  471. PageNum: dataNum,
  472. Index: sheetInfo.Index,
  473. Sort: sheetInfo.Sort,
  474. Config: sheetInfo.Config,
  475. CalcChain: sheetInfo.CalcChain,
  476. ModifyTime: time.Now(),
  477. CreateTime: time.Now(),
  478. }
  479. sheetId, tmpErr := o.Insert(excelSheetInfo)
  480. if tmpErr != nil {
  481. err = tmpErr
  482. return
  483. }
  484. excelSheetInfo.ExcelSheetId = int(sheetId)
  485. // data信息入库
  486. if dataNum > 0 {
  487. for k, _ := range sheetInfo.DataList {
  488. sheetInfo.DataList[k].ExcelSheetId = excelSheetInfo.ExcelSheetId
  489. sheetInfo.DataList[k].ExcelInfoId = excelSheetInfo.ExcelInfoId
  490. }
  491. _, tmpErr = o.InsertMulti(dataNum, sheetInfo.DataList)
  492. if tmpErr != nil {
  493. err = tmpErr
  494. return
  495. }
  496. }
  497. }
  498. return
  499. }
  500. // BatchRefreshExcelReq 批量刷新表格请求
  501. type BatchRefreshExcelReq struct {
  502. ExcelCodes []string `description:"表格编码"`
  503. ReportId int `description:"报告ID"`
  504. ReportChapterId int `description:"报告章节ID"`
  505. Source string `description:"来源,枚举值:report、english_report、smart_report"`
  506. }
  507. // GetExcelMaxSortByClassifyId 获取当前分类下,且排序数最大的excel
  508. func GetExcelMaxSortByClassifyId(classifyId int, source int) (sort int, err error) {
  509. o := orm.NewOrmUsingDB("data")
  510. 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`
  511. err = o.Raw(sql, classifyId, source).QueryRow(&sort)
  512. return
  513. }
  514. // GetNoContentExcelListByExcelInfoIdList
  515. // @Description: 根据ETA表格ID列表获取列表信息
  516. // @param excelInfoIdList []string
  517. // @return items
  518. // @return err
  519. func GetNoContentExcelListByExcelInfoIdList(excelInfoIdList []string) (items []*MyExcelInfoList, err error) {
  520. num := len(excelInfoIdList)
  521. if num <= 0 {
  522. return
  523. }
  524. o := orm.NewOrmUsingDB("data")
  525. sql := ` SELECT * FROM excel_info WHERE excel_info_id in (` + utils.GetOrmInReplace(num) + `) order by excel_info_id DESC `
  526. _, err = o.Raw(sql, excelInfoIdList).QueryRows(&items)
  527. return
  528. }
  529. // GetNoContentExcelListByUserId
  530. // @Description: 根据ETA表格ID列表获取列表信息
  531. // @param userIdList []int
  532. // @return items
  533. // @return err
  534. func GetNoContentExcelListByUserId(userIdList []int) (items []*MyExcelInfoList, err error) {
  535. num := len(userIdList)
  536. if num <= 0 {
  537. return
  538. }
  539. o := orm.NewOrmUsingDB("data")
  540. sql := ` SELECT * FROM excel_info WHERE excel_info_id in (` + utils.GetOrmInReplace(num) + `) order by excel_info_id DESC `
  541. _, err = o.Raw(sql, userIdList).QueryRows(&items)
  542. return
  543. }
  544. // ModifyExcelInfoUserIdByCodeList
  545. // @Description: 根据编码列表修改表格信息
  546. // @author: Roc
  547. // @datetime 2024-03-26 14:16:30
  548. // @param excelIdList []string
  549. // @param userId int
  550. // @param userName string
  551. // @return err error
  552. func ModifyExcelInfoUserIdByCodeList(excelIdList []string, userId int, userName string) (err error) {
  553. num := len(excelIdList)
  554. if num <= 0 {
  555. return
  556. }
  557. o := orm.NewOrmUsingDB("data")
  558. sql := `UPDATE excel_info SET sys_user_id=?,sys_user_real_name=? WHERE excel_info_id in (` + utils.GetOrmInReplace(num) + `) `
  559. _, err = o.Raw(sql, userId, userName, excelIdList).Exec()
  560. return
  561. }
  562. // ModifyExcelInfoUserIdByOldUserId
  563. // @Description: 根据旧的用户id修改创建人
  564. // @author: Roc
  565. // @datetime 2024-03-25 19:14:59
  566. // @param oldUserId int
  567. // @param userId int
  568. // @param userName string
  569. // @return err error
  570. func ModifyExcelInfoUserIdByOldUserId(oldUserIdList []int, userId int, userName string) (err error) {
  571. num := len(oldUserIdList)
  572. if num <= 0 {
  573. return
  574. }
  575. o := orm.NewOrmUsingDB("data")
  576. sql := `UPDATE excel_info SET sys_user_id=?,sys_user_real_name=? WHERE is_delete=0 AND sys_user_id in (` + utils.GetOrmInReplace(num) + `) `
  577. _, err = o.Raw(sql, userId, userName, oldUserIdList).Exec()
  578. return
  579. }
  580. // ExcelInfoDetailButton 操作按钮
  581. type ExcelInfoDetailButton struct {
  582. RefreshButton bool `description:"是否可刷新"`
  583. CopyButton bool `description:"是否可另存为"`
  584. DownloadButton bool `description:"是否可下载"`
  585. OpButton bool `description:"是否可编辑"`
  586. DeleteButton bool `description:"是否可删除"`
  587. OpEdbButton bool `description:"是否可生成指标"`
  588. RefreshEdbButton bool `description:"是否可刷新指标"`
  589. OpWorkerButton bool `description:"是否修改协作人"`
  590. }
  591. // GetChildExcelInfoByParentId 根据id 获取eta表格详情
  592. func GetChildExcelInfoByParentId(parentId int) (items []*ExcelInfo, err error) {
  593. o := orm.NewOrmUsingDB("data")
  594. 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`
  595. _, err = o.Raw(sql, parentId).QueryRows(&items)
  596. return
  597. }
  598. func GetNoContentExcelInfoListByConditionNoPage(condition string, pars []interface{}) (items []*ExcelInfo, err error) {
  599. o := orm.NewOrmUsingDB("data")
  600. sql := `SELECT excel_info_id,excel_classify_id,excel_name,
  601. 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 `
  602. if condition != "" {
  603. sql += condition
  604. }
  605. sql += ` ORDER BY excel_info_id DESC `
  606. _, err = o.Raw(sql, pars).QueryRows(&items)
  607. return
  608. }
  609. // GetNoContentExcelListByConditionNoPage 获取没有content的excel表格列表数据
  610. func GetNoContentExcelListByConditionNoPage(condition string, pars []interface{}) (item []*MyExcelInfoList, err error) {
  611. o := orm.NewOrmUsingDB("data")
  612. 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
  613. FROM excel_info WHERE 1=1 AND is_delete=0 `
  614. if condition != "" {
  615. sql += condition
  616. }
  617. //sql += " ORDER BY sort ASC,chart_info_id DESC LIMIT ?,? "
  618. sql += " ORDER BY create_time DESC"
  619. _, err = o.Raw(sql, pars).QueryRows(&item)
  620. return
  621. }
  622. type ExcelInfoRuleMappingView struct {
  623. ExcelInfoRuleMappingId int `orm:"pk" description:"主键"`
  624. ExcelInfoId int `description:"Excel信息ID"`
  625. RuleType int `description:"规则类型"`
  626. LeftValue string `description:"左值"`
  627. LeftValueBack string `description:"左值后端存储" json:"-"`
  628. LeftValueType int `description:"左值类型"`
  629. RightValue string `description:"右值"`
  630. RightValueBack string `description:"右值后端存储" json:"-"`
  631. RightValueType int `description:"右值类型"`
  632. FontColor string `description:"字体颜色"`
  633. BackgroundColor string `description:"背景颜色"`
  634. Remark string `description:"预设颜色说明"`
  635. RemarkEn string `description:"预设颜色英文说明"`
  636. Scope string `description:"作用范围"`
  637. ScopeCoord string `description:"作用范围坐标"`
  638. ScopeShow string `description:"作用范围坐标前端显示"`
  639. CreateTime string `description:"创建时间"`
  640. }
  641. // GetExcelRuleMappingByExcelInfoId 根据excelInfoId获取规则映射信息
  642. func GetExcelRuleMappingByExcelInfoId(id int) (items []*ExcelInfoRuleMappingView, err error) {
  643. o := orm.NewOrmUsingDB("data")
  644. sql := `SELECT * FROM excel_info_rule_mapping WHERE excel_info_id = ? ORDER BY create_time ASC`
  645. _, err = o.Raw(sql, id).QueryRows(&items)
  646. return
  647. }