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