tree_contract_dao.go 17 KB

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