/* * @description: 树结构数据库操作相关 * @Author: CP * @Date: 2020-09-11 14:49:27 * @FilePath: \construction_management\dao\tree_dao.go */ package dao import ( "errors" "fmt" "log" "strconv" "strings" "github.com/go-xorm/xorm" "go.mod/models" ) //数据库操作引擎 type TreeDao struct { engine *xorm.Engine } //获得一个DAO对象 func NewTreeDao(engine *xorm.Engine) *TreeDao { return &TreeDao{ engine: engine, } } //id获得数据 func (d *TreeDao) Get(id int, projectId int) *models.CmTree { // data := &models.CmTree{Id: id, ProjectId: projectId, Isdelete: 0} // // Get取到值后,会自动赋值到data中 // ok, err := d.engine.Get(data) // if ok && err == nil { // return data // } else { // data.Id = 0 // return data // } // fmt.Println("sqlid=") // fmt.Println(id) data := &models.CmTree{} _, err := d.engine. Where("id=? and project_id=? and isdelete=0", id, projectId). Get(data) // fmt.Println(data) if err != nil { data.Id = 0 return data } return data } // 获得该目录下的标段 func (d *TreeDao) GetBidsection(id int) []models.CmTree { datalist := make([]models.CmTree, 0) err := d.engine. Asc("serial"). Where("parent_id=? and isfolder=0 and isdelete=0", id). Find(&datalist) if err != nil { return datalist } else { return datalist } } // 获得某一深度的 结构数据(不包含子集) 正序 func (d *TreeDao) GetAllDepth(depth int, projectId int) []models.CmTree { datalist := make([]models.CmTree, 0) err := d.engine. Asc("serial"). Where("depth=? and project_id=? and isdelete=0", depth, projectId). Find(&datalist) if err != nil { return datalist } else { return datalist } } // 获得该目录下所有的目录 func (d *TreeDao) GetChildFolder(id int) []models.CmTree { datalist := make([]models.CmTree, 0) err := d.engine. Asc("serial"). Where("parent_id=? and isfolder=1 and isdelete=0", id). Find(&datalist) if err != nil { return datalist } else { return datalist } } // 获得某一深度的某一归属 结构数据(不包含子集) 正序 func (d *TreeDao) GetALLDepthByAttribution(depth int, projectId int, attribution string) []models.CmTree { datalist := make([]models.CmTree, 0) err := d.engine. Asc("serial"). Where("depth=? and project_id=? and attribution like ? and isdelete=0", depth, projectId, attribution+"%"). Find(&datalist) if err != nil { return datalist } else { return datalist } } // 获得该目录下所有的目录和标段 func (d *TreeDao) GetFolderAndBid(projectId int, attribution string) []models.CmTree { datalist := make([]models.CmTree, 0) err := d.engine. Asc("serial"). Where("project_id=? and attribution like ? and isdelete=0", projectId, attribution+"%"). Find(&datalist) if err != nil { return datalist } else { return datalist } } // 获得标段的目录 func (d *TreeDao) GetBidParentId(bidsectionId int, projectId int) *models.CmTree { data := &models.CmTree{} _, err := d.engine. Where("bidsection_id=? and project_id=? and isdelete=0", bidsectionId, projectId). Get(data) if err != nil { data.Id = 0 return data } return data } // 获得谋归属下的项目节 func (d *TreeDao) GetAttribution(attribution string, projectId int) []models.CmTree { datalist := make([]models.CmTree, 0) err := d.engine. Asc("serial"). Where("attribution like ? and project_id=? and isdelete=0", attribution+"%", projectId). Find(&datalist) if err != nil { return datalist } else { return datalist } } // 删除目录以及下属目录所有数据 func (d *TreeDao) DeleteFolderAndBid(id int, projectId int, attribution string) error { session := d.engine.NewSession() defer session.Close() err := session.Begin() if err != nil { return errors.New("删除出错-db") } // 删除树结构中 目录和资源 data := &models.CmTree{Isdelete: 1} _, err = session. Where("id=? or (project_id=? and attribution like ?) and isdelete=0", id, projectId, attribution+"%"). Update(data) if err != nil { session.Rollback() return errors.New("删除目录出错") } // 获得已删除不是目录的资源 datalist := make([]models.CmTree, 0) err = d.engine. Where("project_id=? and isdelete=1 and isfolder=0", projectId). Find(&datalist) // 删除标段 if len(datalist) > 0 { idList := []string{} for _, bidData := range datalist { idList = append(idList, strconv.Itoa(bidData.Id)) } inId := strings.Join(idList, ",") _, err = session.Exec("UPDATE cm_bidsection SET `isdelete` = 1 where id in (?)", inId) if err != nil { session.Rollback() return errors.New("删除标段出错") } } err = session.Commit() if err != nil { session.Rollback() return errors.New("删除出错-db") } return nil } // 移动目录 func (d *TreeDao) Move(treeNode *models.CmTree, moveFolder *models.CmTree) error { session := d.engine.NewSession() defer session.Close() err := session.Begin() if err != nil { return errors.New("移动出错-db") } // 原目录父节点替换目标目录的ID _, err = session.Exec("UPDATE cm_tree SET `parent_id` = ? where id = ? and isdelete=0", moveFolder.Id, treeNode.Id) if err != nil { return err //errors.New("移动目录出错---") } // data := &models.CmTree{ParentId: moveFolder.Id} // _, err = session. // Where("id=? and isdelete=0", treeNode.Id). // Update(data) // if err != nil { // session.Rollback() // return err //errors.New("移动目录出错---") // } // 移动目录的归属和标段的归属关系--TODO 效率问题在修改 // 1-原来目录的归属 attribution := fmt.Sprintf("%s%d-", treeNode.Attribution, treeNode.Serial) // 2-移动后目录的归属--跟目录的话,所属为空 movrAttribution := "" if moveFolder.Id != 0 { movrAttribution = fmt.Sprintf("%s%d-", moveFolder.Attribution, moveFolder.Serial) } // 3-获得移动后最大序列号 depth := moveFolder.Depth + 1 datalist := d.GetALLDepthByAttribution(depth, moveFolder.ProjectId, movrAttribution) maxIndex := len(datalist) serial := 0 if maxIndex != 0 { serial = datalist[maxIndex-1].Serial + 1 } // 3-1 深度差=原目录depth-目标目录depth difference := (treeNode.Depth - 1) - moveFolder.Depth // 移动后目录depth=原目录depth-差 // 4-移动原目录或标段-最大序号 _, err = session.Exec("UPDATE cm_tree SET `attribution` = ?,`serial` = ?,`depth` =`depth` - ? where id = ? and isdelete=0", movrAttribution, serial, difference, treeNode.Id) if err != nil { session.Rollback() log.Println("移动目录或标段1, error=", err) return errors.New("移动目录或标段出错1") } // // 5-移动原目录下所有子目录和标段-项目下 movrAttribution = fmt.Sprintf("%s%d-", movrAttribution, serial) //`attribution` = replace(`attribution`, '"+attribution+"', '"+movrAttribution+"') _, err = session.Exec("UPDATE cm_tree SET "+ "`depth` =`depth` - ? where attribution like ? and project_id=? and isdelete=0", difference, attribution+"%", treeNode.ProjectId) //_, err = session.Exec("UPDATE from cm_tree SET `attribution` = replace(`attribution`, ?, ?) where attribution like ?", attribution,movrAttribution,attribution+"%") if err != nil { session.Rollback() log.Println("移动目录或标段2, error=", err) return errors.New("移动目录或标段出错2") } // 6-目录孩子们的归属设置 err = d.replaceContractAttribution(session, attribution, movrAttribution, treeNode.ProjectId) if err != nil { session.Rollback() return errors.New("移动失败") } err = session.Commit() if err != nil { session.Rollback() return errors.New("移动出错-db") } return nil } // 获得项目文件夹 func (d *TreeDao) GetAllTree(projectId int) []models.CmTree { datalist := make([]models.CmTree, 0) err := d.engine. Asc("id"). Where("project_id=? and isdelete=0", projectId). Find(&datalist) if err != nil { return datalist } else { return datalist } } // treeNode重命名 func (d *TreeDao) Rename(data *models.CmTree, columns []string) error { session := d.engine.NewSession() defer session.Close() err := session.Begin() if err != nil { return errors.New("重命名失败-db") } // 重命名treeNode _, err = session.Id(data.Id).MustCols(columns...).Update(data) if err != nil { session.Rollback() return errors.New("标段重命名失败") } // 重命名标段 bidsection := models.CmBidsection{} bidsection.Id = data.BidsectionId bidsection.Name = data.Name _, err = session.Id(bidsection.Id).MustCols(columns...).Update(bidsection) if err != nil { session.Rollback() return errors.New("标段重命名失败-bid") } err = session.Commit() if err != nil { session.Rollback() return errors.New("重命名失败-db") } return nil } //创建 func (d *TreeDao) Create(data *models.CmTree) error { _, err := d.engine.Insert(data) return err } // 更新 func (d *TreeDao) Update(data *models.CmTree, columns []string) error { _, err := d.engine.Id(data.Id).MustCols(columns...).Update(data) return err } // 更新标段目录上合同金额和总数 func (d *TreeDao) UpdateContractsAndPayPrice(projectId int, bidsectionId int, contractTotal int, priceTotal float64) error { // 1.更新标段 上的合同总金额 _, err := d.engine.Exec("UPDATE cm_tree SET `contracts` = ?,`contracts_pay` = ? "+ "where project_id = ? and bidsection_id = ? ", contractTotal, priceTotal, projectId, bidsectionId) if err != nil { return errors.New("编辑标段目录-金额合计出错") } return nil } // 更新标段目录上合同金额和总数 func (d *TreeDao) UpdateContractsAndIncomePrice(projectId int, bidsectionId int, contractTotal int, priceTotal float64) error { // 1.更新标段 上的合同总金额 _, err := d.engine.Exec("UPDATE cm_tree SET `contracts` = ?,`contracts_income` = ? "+ "where project_id = ? and bidsection_id = ? ", contractTotal, priceTotal, projectId, bidsectionId) if err != nil { return errors.New("编辑标段目录-金额合计出错") } // _, err :=d.engine.Where("project_id = ? and bidsection_id = ?",projectId,bidsectionId).MustCols().Update(treeCm) // _, err := d.engine.Id(data.Id).MustCols(columns...).Update(data) return nil } //替换项目节归属 func (d *TreeDao) replaceContractAttribution(session *xorm.Session, attributionChildren string, moveAttributionChildren string, projectId int) error { // 1.获得需要替换的数据 sectionData := d.GetAttribution(attributionChildren, projectId) if len(sectionData) == 0 { return nil } attributionSql := " attribution = case id " idList := make([]int, 0) for _, item := range sectionData { // 替换归属 attributionSql += fmt.Sprintf("when %d then '%s' ", item.Id, strings.Replace(item.Attribution, attributionChildren, moveAttributionChildren, 1)) idList = append(idList, item.Id) } attributionSql += " end " id := strings.Replace(strings.Trim(fmt.Sprint(idList), "[]"), " ", ",", -1) sql := "update cm_tree set " + attributionSql + " WHERE id IN (" + id + ")" _, err := session.Exec(sql) if err != nil { log.Println("替换标段归属, error=", err) return err } return nil } // 根据bid更新记录中的安全、质量巡检相关字段 func (d *TreeDao) UpdateCounts(key string, value int64, bid int) error { attributionSql := fmt.Sprintf("%s = %d ", key, value) sql := "update cm_tree set " + attributionSql + "where bidsection_id = ?" _, err := d.engine.Exec(sql, bid) return err }