design_quantity/dao/tree_contract_dao.go

/*
 * @description: 合同项目节相关数据库操作
 * @Author: CP
 * @Date: 2020-11-02 11:37:32
 * @FilePath: \design_quantity\dao\tree_contract_dao.go
 */
package dao

import (
	"errors"
	"fmt"
	"log"
	"strings"

	"github.com/go-xorm/xorm"
	"go.mod/comm"
	"go.mod/conf"
	"go.mod/models"
	"go.mod/web/viewmodels"
)

//数据库操作引擎
type TreeContractDao struct {
	engine *xorm.Engine
}

//获得一个DAO对象
func NewTreeContractDao(engine *xorm.Engine) *TreeContractDao {
	return &TreeContractDao{
		engine: engine,
	}
}

// 获取项目节名称
func (d *TreeContractDao) GetDetailName(id int) string {
	data := &models.CmTreeContracts{}
	_, _ = d.engine.Where("id = ?", id).Get(data)
	return data.Name
}

// 获取项目节pdf文件信息
func (d *TreeContractDao) GetDetailWithPdf(id int) *viewmodels.TreeSectionDetail {
	data := &viewmodels.TreeSectionDetail{}
	_, _ = d.engine.Sql("select 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)
	if data.Fid != "" {
		fid, _ := comm.AesEncrypt(data.Fid, conf.SignSecret)
		data.Fid = fid

	}
	return data
}

// 获取项目节excel文件信息
func (d *TreeContractDao) GetDetailWithExcel(id int) *viewmodels.TreeSectionDetail {
	data := &viewmodels.TreeSectionDetail{}
	_, _ = d.engine.Sql("select 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_id2 = s.id  left join cm_upload as f on t.contract_id2 = f.id where t.id = ?", id).Get(data)
	if data.Fid != "" {
		fid, _ := comm.AesEncrypt(data.Fid, conf.SignSecret)
		data.Fid = fid

	}
	return data
}

// 获得本项目的合同项目节
func (d *TreeContractDao) Get(treeId int) *models.CmTreeContracts {

	data := &models.CmTreeContracts{}
	_, err := d.engine.
		Where("tree_id=? ", treeId).
		Get(data)

	if err != nil {
		data.Id = 0
		return data
	}
	return data
}

// 获得项目下的项目节
func (d *TreeContractDao) GetAll() []models.CmTreeContracts {

	datalist := make([]models.CmTreeContracts, 0)
	err := d.engine.
		Asc("id").
		Limit(5000, 0).
		Find(&datalist)
	if err != nil {
		return datalist
	} else {
		return datalist
	}
}

// 获得项目下的项目节
func (d *TreeContractDao) GetDepth() []models.CmTreeContracts {

	datalist := make([]models.CmTreeContracts, 0)
	err := d.engine.
		Where("depth <=? ", 1).
		Asc("id").
		Limit(5000, 0).
		Find(&datalist)
	if err != nil {
		return datalist
	} else {
		return datalist
	}
}

// 获得项目下的项目节不包含合同
func (d *TreeContractDao) GetAllNotContract(bidsectionId int, projectId int, treeType int) []models.CmTreeContracts {

	datalist := make([]models.CmTreeContracts, 0)
	err := d.engine.
		Asc("id").
		Where("bidsection_id =? and project_id=? and tree_type=? and contract_id=0", bidsectionId, projectId, treeType).
		Limit(5000, 0).
		Find(&datalist)
	if err != nil {
		return datalist
	} else {
		return datalist
	}
}

// 获得最新ID
func (d *TreeContractDao) GetLastId() *models.CmTreeContracts {
	data := &models.CmTreeContracts{}
	_, err := d.engine.
		Desc("id").
		Get(data)
	if err != nil {
		data.Id = 0
		return data
	}
	return data
}

// 获得项目节所有合同
func (d *TreeContractDao) GetContractAll(bidsectionId int, projectId int) []models.CmTreeContracts {
	datalist := make([]models.CmTreeContracts, 0)
	err := d.engine.
		Asc("id").
		Where("bidsection_id =? and project_id=? and contract_id!=0 ", bidsectionId, projectId).
		Find(&datalist)
	if err != nil {
		return datalist
	} else {
		return datalist
	}
}

// 获得标段 项目节中已有的合同
func (d *TreeContractDao) GetContract(bidsectionId int, projectId int, treeType int) []models.CmTreeContracts {
	datalist := make([]models.CmTreeContracts, 0)
	err := d.engine.
		Asc("id").
		Where("bidsection_id =? and project_id=? and contract_id!=0 and tree_type=?", bidsectionId, projectId, treeType).
		Find(&datalist)
	if err != nil {
		return datalist
	} else {
		return datalist
	}
}

// 获得节点的孩子
func (d *TreeContractDao) GetChildren(parentId int) []models.CmTreeContracts {
	datalist := make([]models.CmTreeContracts, 0)
	err := d.engine.
		Asc("serial").
		Where("parent_id=?", parentId).
		Find(&datalist)
	if err != nil {
		return datalist
	} else {
		return datalist
	}
}

//根据序号和深度获得前一个兄弟节点
func (d *TreeContractDao) GetElderBrother(serial int, depth int, parentId int) []models.CmTreeContracts {
	datalist := make([]models.CmTreeContracts, 0)
	err := d.engine.
		Desc("serial").
		Where("serial < ? and depth = ? and  parent_id =? ", serial, depth, parentId).
		Find(&datalist)
	if err != nil {
		return datalist
	} else {
		return datalist
	}
}

//根据序号和深度获得后一个兄弟节点
func (d *TreeContractDao) GetYoungerBrother(serial int, depth int, parentId int) []models.CmTreeContracts {
	datalist := make([]models.CmTreeContracts, 0)
	err := d.engine.
		Asc("serial").
		Where("serial > ? and depth = ? and  parent_id =? ", serial, depth, parentId).
		Find(&datalist)
	if err != nil {
		return datalist
	} else {
		return datalist
	}
}

// 获得最后一条项目节
func (d *TreeContractDao) GetLast(projectId int, treeType int) *models.CmTreeContracts {
	data := &models.CmTreeContracts{}
	_, err := d.engine.
		Desc("id").
		Where("project_id=? and tree_type=?", projectId, treeType).
		Get(data)
	if err != nil {
		data.Id = 0
		return data
	}
	return data
}

// 获得谋归属下的项目节
func (d *TreeContractDao) GetAttribution(attribution string) []models.CmTreeContracts {
	datalist := make([]models.CmTreeContracts, 0)
	err := d.engine.
		Asc("serial").
		Where("attribution like ? ", attribution+"%").
		Find(&datalist)
	if err != nil {
		return datalist
	} else {
		return datalist
	}
}

// 获得谋归属下的项目节-合同
func (d *TreeContractDao) GetAttributionContract(section *models.CmTreeContracts) []models.CmTreeContracts {

	// attribution := section.Attribution
	attribution := fmt.Sprintf("%s-", section.Code)

	datalist := make([]models.CmTreeContracts, 0)
	err := d.engine.
		Asc("serial").
		Where("(attribution like ? or id =? ) and project_id=? contract_id!=0", attribution+"%", section.Id).
		Find(&datalist)
	if err != nil {
		return datalist
	} else {
		return datalist
	}
}

// 项目节升降级
func (d *TreeContractDao) MoveDepth(section *models.CmTreeContracts, elderBrother *models.CmTreeContracts, operation string) error {

	session := d.engine.NewSession()
	defer session.Close()
	err := session.Begin()
	if err != nil {
		return errors.New("操作失败-db")
	}
	// 降级
	if operation == "downDepth" {
		// 1.前一个兄弟节点
		// fmt.Println(elderBrother)
		// 2.把节点移动到兄弟节点的下级，成为兄弟的孩子
		// 2-1 节点的父亲为兄弟的ID
		// 2-2 节点的深度 +1
		// 2-3 节点归属为兄弟归属+序号
		attribution := fmt.Sprintf("%s%d-", elderBrother.Attribution, elderBrother.Serial)
		// 2-4 序号 没有孩子序号为1，有孩子 最大孩子序号+1
		// 2-4-1 获得上一位哥的孩子们
		elderBrotherChildren := d.GetChildren(elderBrother.TreeId)
		serial := 1
		if len(elderBrotherChildren) != 0 {
			serial = elderBrotherChildren[len(elderBrotherChildren)-1].Serial + 1
		}
		// 2-5 项目节编号
		// 原编号
		// section.Code
		// 移动后编号
		moveCode := fmt.Sprintf("%s%d", attribution, serial)
		_, err = session.Exec("UPDATE  cm_tree_contracts SET `parent_id` = ?,attribution= ? , serial = ? ,`code` = replace(`code`, '"+section.Code+"', '"+moveCode+"')"+
			",`depth` =`depth` + ? where id = ?", elderBrother.TreeId, attribution, serial, 1, section.Id)
		if err != nil {
			session.Rollback()
			return errors.New("降级失败")
		}
		// 3.更新节点下的归属
		// 3-1 节点的所有孩子的归属
		// 原节点 孩子的归属
		attributionChildren := fmt.Sprintf("%s%d-", section.Attribution, section.Serial)
		// 降级后的 孩子归属
		moveAttributionChildren := fmt.Sprintf("%s%d-", attribution, serial)
		// 3-2 降级 深度+1
		_, err = session.Exec("UPDATE  cm_tree_contracts SET  "+
			"`depth` =`depth` + ? where attribution like ? ", 1, attributionChildren+"%")
		if err != nil {
			session.Rollback()
			return errors.New("降级失败")
		}
		// 3-3--替换 归属和编号
		// "`attribution` = replace(`attribution`, '"+attributionChildren+"', '"+moveAttributionChildren+"') ,`code` = replace(`code`, '"+section.Code+"', '"+moveCode+"'),"
		err = d.replaceContractAttribution(session, attributionChildren, moveAttributionChildren, section.Code, moveCode)
		if err != nil {
			session.Rollback()
			return errors.New("降级失败")
		}
	} else if operation == "upDepth" {
		// 升级
		// 1.父亲节点
		sectionFather := d.Get(section.ParentId)
		if sectionFather.Id == 0 {
			session.Rollback()
			return errors.New("升级-未找到上级项目节")
		}
		// 2.原节点的父亲ID字段升级为爷爷ID
		// 2-1 升级 深度-1
		// 2-2 序号 爷爷的孩子们的 序号+1
		grandpaChildren := d.GetChildren(sectionFather.ParentId)
		serial := 1
		if len(grandpaChildren) != 0 {
			serial = grandpaChildren[len(grandpaChildren)-1].Serial + 1
		}
		// 2-3 归属 原父亲的归属
		// 移动后编号-需替换原编号 节点2-1-1 升级后的父节点归属 2- 加上爷爷最大孩子的序号+1
		moveCode := fmt.Sprintf("%s%d", sectionFather.Attribution, serial)
		// 升级的项目节
		_, err = session.Exec("UPDATE  cm_tree_contracts SET `parent_id` = ?,attribution= ? , serial = ? ,`code` = replace(`code`, '"+section.Code+"', '"+moveCode+"')"+
			",`depth` =`depth` - ? where id = ? ", sectionFather.ParentId, sectionFather.Attribution, serial, 1, section.Id)
		if err != nil {
			session.Rollback()
			return errors.New("升级失败")
		}
		// 3.更新节点下的归属,深度
		// 原节点 孩子的归属
		attributionChildren := fmt.Sprintf("%s%d-", section.Attribution, section.Serial)
		// 升级后的 孩子归属
		moveAttributionChildren := fmt.Sprintf("%s%d-", sectionFather.Attribution, serial)
		// 深度 -1
		_, err = session.Exec("UPDATE  cm_tree_contracts SET `attribution` = replace(`attribution`, '"+attributionChildren+"', '"+moveAttributionChildren+"') "+
			",`depth` =`depth` - ? where attribution like ? ", 1, attributionChildren+"%")
		if err != nil {
			session.Rollback()
			return errors.New("升级失败")
		}
		// 3-1
		err = d.replaceContractAttribution(session, attributionChildren, moveAttributionChildren, section.Code, moveCode)
		if err != nil {
			session.Rollback()
			return errors.New("升级失败")
		}

	} else {
		return errors.New("参数错误")
	}

	err = session.Commit()
	if err != nil {
		session.Rollback()
		return errors.New("操作失败-db")
	}
	return nil
}

// 合同项目节上下移动
func (d *TreeContractDao) MoveSerial(section *models.CmTreeContracts, brother *models.CmTreeContracts, operation string) error {
	session := d.engine.NewSession()
	defer session.Close()
	err := session.Begin()
	if err != nil {
		return errors.New("操作失败-db")
	}

	//1.上下移
	// 1.项目节序号替换为兄弟序号
	_, err = session.Exec("UPDATE  cm_tree_contracts SET  serial = ? , `code` = replace(`code`, '"+section.Code+"', '"+brother.Code+"')  where id = ?", brother.Serial, section.Id)
	if err != nil {
		session.Rollback()
		return errors.New("移动失败")
	}
	// 兄弟序号替换为项目节序号
	_, err = session.Exec("UPDATE  cm_tree_contracts SET  serial = ? , `code` = replace(`code`, '"+brother.Code+"', '"+section.Code+"')  where id = ?", section.Serial, brother.Id)
	if err != nil {
		session.Rollback()
		return errors.New("移动失败")
	}
	//2.项目节孩子们 归属设置
	// 原节点 孩子的归属
	attributionChildren := fmt.Sprintf("%s%d-", section.Attribution, section.Serial)
	// 移动后的 孩子归属和编号
	moveAttributionChildren := fmt.Sprintf("%s%d-", brother.Attribution, brother.Serial)

	err = d.replaceContractAttribution(session, attributionChildren, moveAttributionChildren, section.Code, brother.Code)
	if err != nil {
		session.Rollback()
		return errors.New("移动失败")
	}

	// _, err = session.Exec("UPDATE  cm_tree_contracts SET `attribution` = replace(`attribution`, '"+attributionChildren+"', '"+moveAttributionChildren+"') "+
	// 	"`code` = replace(`code`, '"+attributionChildren+"', '"+moveAttributionChildren+"') where attribution like ? and project_id=? and bidsection_id=? ", attributionChildren+"%", projectId, bidsectionId)
	// if err != nil {
	// 	session.Rollback()
	// 	return errors.New("移动失败")
	// }

	// 3.兄弟节点孩子们 归属设置
	// 兄弟节点 孩子的归属
	attributionChildren = fmt.Sprintf("%s%d-", brother.Attribution, brother.Serial)
	// 移动后的 孩子归属
	moveAttributionChildren = fmt.Sprintf("%s%d-", section.Attribution, section.Serial)
	err = d.replaceContractAttribution(session, attributionChildren, moveAttributionChildren, brother.Code, section.Code)
	if err != nil {
		session.Rollback()
		return errors.New("移动失败")
	}

	err = session.Commit()
	if err != nil {
		session.Rollback()
		return errors.New("操作失败-db")
	}
	return nil
}

// 修改项目节序号
func (d *TreeContractDao) UpdateSerial(section *models.CmTreeContracts, serial int) error {
	session := d.engine.NewSession()
	defer session.Close()
	err := session.Begin()
	if err != nil {
		return errors.New("操作失败-db")
	}

	// 1.更新项目节序号和项目节编号
	moveCode := fmt.Sprintf("%s%d", section.Attribution, serial)
	_, err = session.Exec("UPDATE  cm_tree_contracts SET  serial = ? , `code` = ?  where id = ? ", serial, moveCode, section.Id)
	if err != nil {
		session.Rollback()
		log.Println("合同项目节序号更新 error=", err)
		return errors.New("更新序号失败")
	}
	// 2.更新项目节子孙们的序号和归属
	// 2-1归属 项目节 孩子归属
	attributionChildren := fmt.Sprintf("%s%d-", section.Attribution, section.Serial)
	// 2-2 序号更新后的 孩子归属
	moveAttributionChildren := fmt.Sprintf("%s%d-", section.Attribution, serial)
	// 2-3 项目节 孩子的编号
	code := fmt.Sprintf("%s%d", section.Attribution, section.Serial)
	err = d.replaceContractAttribution(session, attributionChildren, moveAttributionChildren, code, moveCode)
	if err != nil {
		session.Rollback()
		log.Println("合同项目节序号更新 error=", err)
		return errors.New("更新序号失败")
	}

	err = session.Commit()
	if err != nil {
		session.Rollback()
		return errors.New("操作失败-db")
	}
	return nil
}

// 插入多条数据
func (d *TreeContractDao) CreateAll(data []*models.CmTreeContracts) error {
	session := d.engine.NewSession()
	defer session.Close()
	err := session.Begin()
	if err != nil {
		return errors.New("新增失败-db")
	}
	// 新增
	for _, item := range data {
		_, err := session.Insert(item)
		if err != nil {
			log.Println(" error=", err)
			session.Rollback()
			return errors.New("新增失败")
		}
	}

	err = session.Commit()
	if err != nil {
		session.Rollback()
		return errors.New("新增失败-db")
	}
	return nil
}

// 新增项目节
func (d *TreeContractDao) Create(data *models.CmTreeContracts) (*models.CmTreeContracts, error) {

	session := d.engine.NewSession()
	defer session.Close()
	err := session.Begin()
	if err != nil {
		return nil, errors.New("新增失败-db")
	}

	_, err = session.Insert(data)
	if err != nil {
		log.Println(" error=", err)
		return nil, errors.New("新增失败")
	}
	// 插入成功后，ID自动赋值到data.Id里
	// fmt.Println("=======================================")
	// fmt.Println(data)

	// 更新合同节树ID
	// data.TreeId = data.Id
	// _, err = session.Id(data.Id).Update(data)
	// if err != nil {
	// 	log.Println(" error=", err)
	// 	return errors.New("新增失败")
	// }

	err = session.Commit()
	if err != nil {
		session.Rollback()
		return nil, errors.New("新增失败-db")
	}
	return data, nil
}

// 保存项目节
func (d *TreeContractDao) Save(section *models.CmTreeContracts, columns []string) error {
	_, err := d.engine.Id(section.Id).MustCols(columns...).Update(section)
	if err != nil {
		return errors.New("保存失败")
	}
	return nil
}

// 删除项目节
func (d *TreeContractDao) Delete(section *models.CmTreeContracts) error {
	session := d.engine.NewSession()
	defer session.Close()
	err := session.Begin()
	if err != nil {
		return errors.New("删除失败-db")
	}
	// 1. 删除项目节
	_, err = session.Where("id = ? ", section.Id).Delete(section)
	if err != nil {
		session.Rollback()
		return errors.New("删除失败")
	}

	// 孩子们的归属
	attribution := fmt.Sprintf("%s%d-", section.Attribution, section.Serial)
	// 2. 删除项目节孩子们
	_, err = session.Exec("DELETE FROM `cm_tree_contracts` WHERE attribution like ? ", attribution+"%")
	if err != nil {
		session.Rollback()
		return errors.New("删除失败")
	}

	err = session.Commit()
	if err != nil {
		session.Rollback()
		return errors.New("删除失败-db")
	}
	return nil
}

//替换项目节归属
func (d *TreeContractDao) replaceContractAttribution(session *xorm.Session, attributionChildren string, moveAttributionChildren string, code string, moveCode string) error {
	// 1.获得需要替换的数据
	sectionData := d.GetAttribution(attributionChildren)
	if len(sectionData) == 0 {
		return nil
	}

	attributionSql := " attribution = case id "
	codeSql := " code = case id "
	idList := make([]int, 0)
	for _, item := range sectionData {
		// section := &models.CmTreeContracts{}
		// section.Id = item.Id
		// 替换归属
		attributionSql += fmt.Sprintf("when %d then '%s' ", item.Id, strings.Replace(item.Attribution, attributionChildren, moveAttributionChildren, 1))
		//section.Attribution = strings.Replace(item.Attribution, attributionChildren, moveAttributionChildren, 1)
		// 替换编号
		codeSql += fmt.Sprintf("when %d then '%s' ", item.Id, strings.Replace(item.Code, code, moveCode, 1))
		// section.Code = strings.Replace(item.Code, code, moveCode, 1)
		idList = append(idList, item.Id)
	}
	attributionSql += " end, "
	codeSql += " end "
	id := strings.Replace(strings.Trim(fmt.Sprint(idList), "[]"), " ", ",", -1)
	sql := "update cm_tree_contracts set  " + attributionSql + codeSql + " WHERE id IN (" + id + ")"

	_, err := session.Exec(sql)
	if err != nil {
		log.Println("替换项目节归属, error=", err)
		return err
	}
	return nil
}

// 更新合同id
func (d *TreeContractDao) UpdateContract(id int, contractId int) error {
	file := &models.CmUpload{}
	_, err := d.engine.Where("id = ?", contractId).Get(file)
	if err != nil {
		return err
	}
	if file.Ext == "application/vnd.ms-excel" {
		data := &models.CmTreeContracts{Id: id, ContractId2: contractId}
		_, err := d.engine.Where("id = ?", id).Cols("contract_id2").Update(data)
		return err
	} else if file.Ext == "application/pdf" {
		data := &models.CmTreeContracts{Id: id, ContractId: contractId}
		_, err := d.engine.Where("id = ?", id).Cols("contract_id").Update(data)
		return err
	}
	return nil
}