tree_contract_dao.go 18 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591
  1. /*
  2. * @description: 合同项目节相关数据库操作
  3. * @Author: CP
  4. * @Date: 2020-11-02 11:37:32
  5. * @FilePath: \design_quantity\dao\tree_contract_dao.go
  6. */
  7. package dao
  8. import (
  9. "errors"
  10. "fmt"
  11. "log"
  12. "strings"
  13. "github.com/go-xorm/xorm"
  14. "go.mod/comm"
  15. "go.mod/conf"
  16. "go.mod/models"
  17. "go.mod/web/viewmodels"
  18. )
  19. //数据库操作引擎
  20. type TreeContractDao struct {
  21. engine *xorm.Engine
  22. }
  23. //获得一个DAO对象
  24. func NewTreeContractDao(engine *xorm.Engine) *TreeContractDao {
  25. return &TreeContractDao{
  26. engine: engine,
  27. }
  28. }
  29. func (d *TreeContractDao) GetDetail(id int) *viewmodels.TreeSectionDetail {
  30. data := &viewmodels.TreeSectionDetail{}
  31. _, _ = d.engine.Sql("select t.name, s.content, f.ext, f.path as filepath, f.id as fid, f.name as filename from cm_tree_contracts as t left join cm_contracts as s on t.contract_id = s.id left join cm_upload as f on t.contract_id = f.id where t.id = ?", id).Get(data)
  32. fmt.Println(data)
  33. if data.Fid != "" {
  34. fid, _ := comm.AesEncrypt(data.Fid, conf.SignSecret)
  35. data.Fid = fid
  36. }
  37. return data
  38. }
  39. // 获得本项目的合同项目节
  40. func (d *TreeContractDao) Get(treeId int) *models.CmTreeContracts {
  41. data := &models.CmTreeContracts{}
  42. _, err := d.engine.
  43. Where("tree_id=? ", treeId).
  44. Get(data)
  45. if err != nil {
  46. data.Id = 0
  47. return data
  48. }
  49. return data
  50. }
  51. // 获得项目下的项目节
  52. func (d *TreeContractDao) GetAll() []models.CmTreeContracts {
  53. datalist := make([]models.CmTreeContracts, 0)
  54. err := d.engine.
  55. Asc("id").
  56. Limit(5000, 0).
  57. Find(&datalist)
  58. if err != nil {
  59. return datalist
  60. } else {
  61. return datalist
  62. }
  63. }
  64. // 获得项目下的项目节
  65. func (d *TreeContractDao) GetDepth() []models.CmTreeContracts {
  66. datalist := make([]models.CmTreeContracts, 0)
  67. err := d.engine.
  68. Where("depth <=? ", 1).
  69. Asc("id").
  70. Limit(5000, 0).
  71. Find(&datalist)
  72. if err != nil {
  73. return datalist
  74. } else {
  75. return datalist
  76. }
  77. }
  78. // 获得项目下的项目节不包含合同
  79. func (d *TreeContractDao) GetAllNotContract(bidsectionId int, projectId int, treeType int) []models.CmTreeContracts {
  80. datalist := make([]models.CmTreeContracts, 0)
  81. err := d.engine.
  82. Asc("id").
  83. Where("bidsection_id =? and project_id=? and tree_type=? and contract_id=0", bidsectionId, projectId, treeType).
  84. Limit(5000, 0).
  85. Find(&datalist)
  86. if err != nil {
  87. return datalist
  88. } else {
  89. return datalist
  90. }
  91. }
  92. // 获得最新ID
  93. func (d *TreeContractDao) GetLastId() *models.CmTreeContracts {
  94. data := &models.CmTreeContracts{}
  95. _, err := d.engine.
  96. Desc("id").
  97. Get(data)
  98. if err != nil {
  99. data.Id = 0
  100. return data
  101. }
  102. return data
  103. }
  104. // 获得项目节所有合同
  105. func (d *TreeContractDao) GetContractAll(bidsectionId int, projectId int) []models.CmTreeContracts {
  106. datalist := make([]models.CmTreeContracts, 0)
  107. err := d.engine.
  108. Asc("id").
  109. Where("bidsection_id =? and project_id=? and contract_id!=0 ", bidsectionId, projectId).
  110. Find(&datalist)
  111. if err != nil {
  112. return datalist
  113. } else {
  114. return datalist
  115. }
  116. }
  117. // 获得标段 项目节中已有的合同
  118. func (d *TreeContractDao) GetContract(bidsectionId int, projectId int, treeType int) []models.CmTreeContracts {
  119. datalist := make([]models.CmTreeContracts, 0)
  120. err := d.engine.
  121. Asc("id").
  122. Where("bidsection_id =? and project_id=? and contract_id!=0 and tree_type=?", bidsectionId, projectId, treeType).
  123. Find(&datalist)
  124. if err != nil {
  125. return datalist
  126. } else {
  127. return datalist
  128. }
  129. }
  130. // 获得节点的孩子
  131. func (d *TreeContractDao) GetChildren(parentId int) []models.CmTreeContracts {
  132. datalist := make([]models.CmTreeContracts, 0)
  133. err := d.engine.
  134. Asc("serial").
  135. Where("parent_id=?", parentId).
  136. Find(&datalist)
  137. if err != nil {
  138. return datalist
  139. } else {
  140. return datalist
  141. }
  142. }
  143. //根据序号和深度获得前一个兄弟节点
  144. func (d *TreeContractDao) GetElderBrother(serial int, depth int, parentId int) []models.CmTreeContracts {
  145. datalist := make([]models.CmTreeContracts, 0)
  146. err := d.engine.
  147. Desc("serial").
  148. Where("serial < ? and depth = ? and parent_id =? ", serial, depth, parentId).
  149. Find(&datalist)
  150. if err != nil {
  151. return datalist
  152. } else {
  153. return datalist
  154. }
  155. }
  156. //根据序号和深度获得后一个兄弟节点
  157. func (d *TreeContractDao) GetYoungerBrother(serial int, depth int, parentId int) []models.CmTreeContracts {
  158. datalist := make([]models.CmTreeContracts, 0)
  159. err := d.engine.
  160. Asc("serial").
  161. Where("serial > ? and depth = ? and parent_id =? ", serial, depth, parentId).
  162. Find(&datalist)
  163. if err != nil {
  164. return datalist
  165. } else {
  166. return datalist
  167. }
  168. }
  169. // 获得最后一条项目节
  170. func (d *TreeContractDao) GetLast(projectId int, treeType int) *models.CmTreeContracts {
  171. data := &models.CmTreeContracts{}
  172. _, err := d.engine.
  173. Desc("id").
  174. Where("project_id=? and tree_type=?", projectId, treeType).
  175. Get(data)
  176. if err != nil {
  177. data.Id = 0
  178. return data
  179. }
  180. return data
  181. }
  182. // 获得谋归属下的项目节
  183. func (d *TreeContractDao) GetAttribution(attribution string) []models.CmTreeContracts {
  184. datalist := make([]models.CmTreeContracts, 0)
  185. err := d.engine.
  186. Asc("serial").
  187. Where("attribution like ? ", attribution+"%").
  188. Find(&datalist)
  189. if err != nil {
  190. return datalist
  191. } else {
  192. return datalist
  193. }
  194. }
  195. // 获得谋归属下的项目节-合同
  196. func (d *TreeContractDao) GetAttributionContract(section *models.CmTreeContracts) []models.CmTreeContracts {
  197. // attribution := section.Attribution
  198. attribution := fmt.Sprintf("%s-", section.Code)
  199. datalist := make([]models.CmTreeContracts, 0)
  200. err := d.engine.
  201. Asc("serial").
  202. Where("(attribution like ? or id =? ) and project_id=? contract_id!=0", attribution+"%", section.Id).
  203. Find(&datalist)
  204. if err != nil {
  205. return datalist
  206. } else {
  207. return datalist
  208. }
  209. }
  210. // 项目节升降级
  211. func (d *TreeContractDao) MoveDepth(section *models.CmTreeContracts, elderBrother *models.CmTreeContracts, operation string) error {
  212. session := d.engine.NewSession()
  213. defer session.Close()
  214. err := session.Begin()
  215. if err != nil {
  216. return errors.New("操作失败-db")
  217. }
  218. // 降级
  219. if operation == "downDepth" {
  220. // 1.前一个兄弟节点
  221. // fmt.Println(elderBrother)
  222. // 2.把节点移动到兄弟节点的下级,成为兄弟的孩子
  223. // 2-1 节点的父亲为兄弟的ID
  224. // 2-2 节点的深度 +1
  225. // 2-3 节点归属为兄弟归属+序号
  226. attribution := fmt.Sprintf("%s%d-", elderBrother.Attribution, elderBrother.Serial)
  227. // 2-4 序号 没有孩子序号为1,有孩子 最大孩子序号+1
  228. // 2-4-1 获得上一位哥的孩子们
  229. elderBrotherChildren := d.GetChildren(elderBrother.TreeId)
  230. serial := 1
  231. if len(elderBrotherChildren) != 0 {
  232. serial = elderBrotherChildren[len(elderBrotherChildren)-1].Serial + 1
  233. }
  234. // 2-5 项目节编号
  235. // 原编号
  236. // section.Code
  237. // 移动后编号
  238. moveCode := fmt.Sprintf("%s%d", attribution, serial)
  239. _, err = session.Exec("UPDATE cm_tree_contracts SET `parent_id` = ?,attribution= ? , serial = ? ,`code` = replace(`code`, '"+section.Code+"', '"+moveCode+"')"+
  240. ",`depth` =`depth` + ? where id = ?", elderBrother.TreeId, attribution, serial, 1, section.Id)
  241. if err != nil {
  242. session.Rollback()
  243. return errors.New("降级失败")
  244. }
  245. // 3.更新节点下的归属
  246. // 3-1 节点的所有孩子的归属
  247. // 原节点 孩子的归属
  248. attributionChildren := fmt.Sprintf("%s%d-", section.Attribution, section.Serial)
  249. // 降级后的 孩子归属
  250. moveAttributionChildren := fmt.Sprintf("%s%d-", attribution, serial)
  251. // 3-2 降级 深度+1
  252. _, err = session.Exec("UPDATE cm_tree_contracts SET "+
  253. "`depth` =`depth` + ? where attribution like ? ", 1, attributionChildren+"%")
  254. if err != nil {
  255. session.Rollback()
  256. return errors.New("降级失败")
  257. }
  258. // 3-3--替换 归属和编号
  259. // "`attribution` = replace(`attribution`, '"+attributionChildren+"', '"+moveAttributionChildren+"') ,`code` = replace(`code`, '"+section.Code+"', '"+moveCode+"'),"
  260. err = d.replaceContractAttribution(session, attributionChildren, moveAttributionChildren, section.Code, moveCode)
  261. if err != nil {
  262. session.Rollback()
  263. return errors.New("降级失败")
  264. }
  265. } else if operation == "upDepth" {
  266. // 升级
  267. // 1.父亲节点
  268. sectionFather := d.Get(section.ParentId)
  269. if sectionFather.Id == 0 {
  270. session.Rollback()
  271. return errors.New("升级-未找到上级项目节")
  272. }
  273. // 2.原节点的父亲ID字段升级为爷爷ID
  274. // 2-1 升级 深度-1
  275. // 2-2 序号 爷爷的孩子们的 序号+1
  276. grandpaChildren := d.GetChildren(sectionFather.ParentId)
  277. serial := 1
  278. if len(grandpaChildren) != 0 {
  279. serial = grandpaChildren[len(grandpaChildren)-1].Serial + 1
  280. }
  281. // 2-3 归属 原父亲的归属
  282. // 移动后编号-需替换原编号 节点2-1-1 升级后的父节点归属 2- 加上爷爷最大孩子的序号+1
  283. moveCode := fmt.Sprintf("%s%d", sectionFather.Attribution, serial)
  284. // 升级的项目节
  285. _, err = session.Exec("UPDATE cm_tree_contracts SET `parent_id` = ?,attribution= ? , serial = ? ,`code` = replace(`code`, '"+section.Code+"', '"+moveCode+"')"+
  286. ",`depth` =`depth` - ? where id = ? ", sectionFather.ParentId, sectionFather.Attribution, serial, 1, section.Id)
  287. if err != nil {
  288. session.Rollback()
  289. return errors.New("升级失败")
  290. }
  291. // 3.更新节点下的归属,深度
  292. // 原节点 孩子的归属
  293. attributionChildren := fmt.Sprintf("%s%d-", section.Attribution, section.Serial)
  294. // 升级后的 孩子归属
  295. moveAttributionChildren := fmt.Sprintf("%s%d-", sectionFather.Attribution, serial)
  296. // 深度 -1
  297. _, err = session.Exec("UPDATE cm_tree_contracts SET `attribution` = replace(`attribution`, '"+attributionChildren+"', '"+moveAttributionChildren+"') "+
  298. ",`depth` =`depth` - ? where attribution like ? ", 1, attributionChildren+"%")
  299. if err != nil {
  300. session.Rollback()
  301. return errors.New("升级失败")
  302. }
  303. // 3-1
  304. err = d.replaceContractAttribution(session, attributionChildren, moveAttributionChildren, section.Code, moveCode)
  305. if err != nil {
  306. session.Rollback()
  307. return errors.New("升级失败")
  308. }
  309. } else {
  310. return errors.New("参数错误")
  311. }
  312. err = session.Commit()
  313. if err != nil {
  314. session.Rollback()
  315. return errors.New("操作失败-db")
  316. }
  317. return nil
  318. }
  319. // 合同项目节上下移动
  320. func (d *TreeContractDao) MoveSerial(section *models.CmTreeContracts, brother *models.CmTreeContracts, operation string) error {
  321. session := d.engine.NewSession()
  322. defer session.Close()
  323. err := session.Begin()
  324. if err != nil {
  325. return errors.New("操作失败-db")
  326. }
  327. //1.上下移
  328. // 1.项目节序号替换为兄弟序号
  329. _, err = session.Exec("UPDATE cm_tree_contracts SET serial = ? , `code` = replace(`code`, '"+section.Code+"', '"+brother.Code+"') where id = ?", brother.Serial, section.Id)
  330. if err != nil {
  331. session.Rollback()
  332. return errors.New("移动失败")
  333. }
  334. // 兄弟序号替换为项目节序号
  335. _, err = session.Exec("UPDATE cm_tree_contracts SET serial = ? , `code` = replace(`code`, '"+brother.Code+"', '"+section.Code+"') where id = ?", section.Serial, brother.Id)
  336. if err != nil {
  337. session.Rollback()
  338. return errors.New("移动失败")
  339. }
  340. //2.项目节孩子们 归属设置
  341. // 原节点 孩子的归属
  342. attributionChildren := fmt.Sprintf("%s%d-", section.Attribution, section.Serial)
  343. // 移动后的 孩子归属和编号
  344. moveAttributionChildren := fmt.Sprintf("%s%d-", brother.Attribution, brother.Serial)
  345. err = d.replaceContractAttribution(session, attributionChildren, moveAttributionChildren, section.Code, brother.Code)
  346. if err != nil {
  347. session.Rollback()
  348. return errors.New("移动失败")
  349. }
  350. // _, err = session.Exec("UPDATE cm_tree_contracts SET `attribution` = replace(`attribution`, '"+attributionChildren+"', '"+moveAttributionChildren+"') "+
  351. // "`code` = replace(`code`, '"+attributionChildren+"', '"+moveAttributionChildren+"') where attribution like ? and project_id=? and bidsection_id=? ", attributionChildren+"%", projectId, bidsectionId)
  352. // if err != nil {
  353. // session.Rollback()
  354. // return errors.New("移动失败")
  355. // }
  356. // 3.兄弟节点孩子们 归属设置
  357. // 兄弟节点 孩子的归属
  358. attributionChildren = fmt.Sprintf("%s%d-", brother.Attribution, brother.Serial)
  359. // 移动后的 孩子归属
  360. moveAttributionChildren = fmt.Sprintf("%s%d-", section.Attribution, section.Serial)
  361. err = d.replaceContractAttribution(session, attributionChildren, moveAttributionChildren, brother.Code, section.Code)
  362. if err != nil {
  363. session.Rollback()
  364. return errors.New("移动失败")
  365. }
  366. err = session.Commit()
  367. if err != nil {
  368. session.Rollback()
  369. return errors.New("操作失败-db")
  370. }
  371. return nil
  372. }
  373. // 修改项目节序号
  374. func (d *TreeContractDao) UpdateSerial(section *models.CmTreeContracts, serial int) error {
  375. session := d.engine.NewSession()
  376. defer session.Close()
  377. err := session.Begin()
  378. if err != nil {
  379. return errors.New("操作失败-db")
  380. }
  381. // 1.更新项目节序号和项目节编号
  382. moveCode := fmt.Sprintf("%s%d", section.Attribution, serial)
  383. _, err = session.Exec("UPDATE cm_tree_contracts SET serial = ? , `code` = ? where id = ? ", serial, moveCode, section.Id)
  384. if err != nil {
  385. session.Rollback()
  386. log.Println("合同项目节序号更新 error=", err)
  387. return errors.New("更新序号失败")
  388. }
  389. // 2.更新项目节子孙们的序号和归属
  390. // 2-1归属 项目节 孩子归属
  391. attributionChildren := fmt.Sprintf("%s%d-", section.Attribution, section.Serial)
  392. // 2-2 序号更新后的 孩子归属
  393. moveAttributionChildren := fmt.Sprintf("%s%d-", section.Attribution, serial)
  394. // 2-3 项目节 孩子的编号
  395. code := fmt.Sprintf("%s%d", section.Attribution, section.Serial)
  396. err = d.replaceContractAttribution(session, attributionChildren, moveAttributionChildren, code, moveCode)
  397. if err != nil {
  398. session.Rollback()
  399. log.Println("合同项目节序号更新 error=", err)
  400. return errors.New("更新序号失败")
  401. }
  402. err = session.Commit()
  403. if err != nil {
  404. session.Rollback()
  405. return errors.New("操作失败-db")
  406. }
  407. return nil
  408. }
  409. // 插入多条数据
  410. func (d *TreeContractDao) CreateAll(data []*models.CmTreeContracts) error {
  411. session := d.engine.NewSession()
  412. defer session.Close()
  413. err := session.Begin()
  414. if err != nil {
  415. return errors.New("新增失败-db")
  416. }
  417. // 新增
  418. for _, item := range data {
  419. _, err := session.Insert(item)
  420. if err != nil {
  421. log.Println(" error=", err)
  422. session.Rollback()
  423. return errors.New("新增失败")
  424. }
  425. }
  426. err = session.Commit()
  427. if err != nil {
  428. session.Rollback()
  429. return errors.New("新增失败-db")
  430. }
  431. return nil
  432. }
  433. // 新增项目节
  434. func (d *TreeContractDao) Create(data *models.CmTreeContracts) (*models.CmTreeContracts, error) {
  435. session := d.engine.NewSession()
  436. defer session.Close()
  437. err := session.Begin()
  438. if err != nil {
  439. return nil, errors.New("新增失败-db")
  440. }
  441. _, err = session.Insert(data)
  442. if err != nil {
  443. log.Println(" error=", err)
  444. return nil, errors.New("新增失败")
  445. }
  446. // 插入成功后,ID自动赋值到data.Id里
  447. // fmt.Println("=======================================")
  448. // fmt.Println(data)
  449. // 更新合同节树ID
  450. // data.TreeId = data.Id
  451. // _, err = session.Id(data.Id).Update(data)
  452. // if err != nil {
  453. // log.Println(" error=", err)
  454. // return errors.New("新增失败")
  455. // }
  456. err = session.Commit()
  457. if err != nil {
  458. session.Rollback()
  459. return nil, errors.New("新增失败-db")
  460. }
  461. return data, nil
  462. }
  463. // 保存项目节
  464. func (d *TreeContractDao) Save(section *models.CmTreeContracts, columns []string) error {
  465. _, err := d.engine.Id(section.Id).MustCols(columns...).Update(section)
  466. if err != nil {
  467. return errors.New("保存失败")
  468. }
  469. return nil
  470. }
  471. // 删除项目节
  472. func (d *TreeContractDao) Delete(section *models.CmTreeContracts) error {
  473. session := d.engine.NewSession()
  474. defer session.Close()
  475. err := session.Begin()
  476. if err != nil {
  477. return errors.New("删除失败-db")
  478. }
  479. // 1. 删除项目节
  480. _, err = session.Where("id = ? ", section.Id).Delete(section)
  481. if err != nil {
  482. session.Rollback()
  483. return errors.New("删除失败")
  484. }
  485. // 孩子们的归属
  486. attribution := fmt.Sprintf("%s%d-", section.Attribution, section.Serial)
  487. // 2. 删除项目节孩子们
  488. _, err = session.Exec("DELETE FROM `cm_tree_contracts` WHERE attribution like ? ", attribution+"%")
  489. if err != nil {
  490. session.Rollback()
  491. return errors.New("删除失败")
  492. }
  493. err = session.Commit()
  494. if err != nil {
  495. session.Rollback()
  496. return errors.New("删除失败-db")
  497. }
  498. return nil
  499. }
  500. //替换项目节归属
  501. func (d *TreeContractDao) replaceContractAttribution(session *xorm.Session, attributionChildren string, moveAttributionChildren string, code string, moveCode string) error {
  502. // 1.获得需要替换的数据
  503. sectionData := d.GetAttribution(attributionChildren)
  504. if len(sectionData) == 0 {
  505. return nil
  506. }
  507. attributionSql := " attribution = case id "
  508. codeSql := " code = case id "
  509. idList := make([]int, 0)
  510. for _, item := range sectionData {
  511. // section := &models.CmTreeContracts{}
  512. // section.Id = item.Id
  513. // 替换归属
  514. attributionSql += fmt.Sprintf("when %d then '%s' ", item.Id, strings.Replace(item.Attribution, attributionChildren, moveAttributionChildren, 1))
  515. //section.Attribution = strings.Replace(item.Attribution, attributionChildren, moveAttributionChildren, 1)
  516. // 替换编号
  517. codeSql += fmt.Sprintf("when %d then '%s' ", item.Id, strings.Replace(item.Code, code, moveCode, 1))
  518. // section.Code = strings.Replace(item.Code, code, moveCode, 1)
  519. idList = append(idList, item.Id)
  520. }
  521. attributionSql += " end, "
  522. codeSql += " end "
  523. id := strings.Replace(strings.Trim(fmt.Sprint(idList), "[]"), " ", ",", -1)
  524. sql := "update cm_tree_contracts set " + attributionSql + codeSql + " WHERE id IN (" + id + ")"
  525. _, err := session.Exec(sql)
  526. if err != nil {
  527. log.Println("替换项目节归属, error=", err)
  528. return err
  529. }
  530. return nil
  531. }
  532. // 更新合同id
  533. func (d *TreeContractDao) UpdateContract(id int, contractId int) error {
  534. data := &models.CmTreeContracts{Id: id, ContractId: contractId}
  535. _, err := d.engine.Where("id = ?", id).Cols("contract_id").Update(data)
  536. return err
  537. }