design_quantity/dao/tree_dao.go

/*
 * @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
}