construction_management/dao/safe_dao.go

/*
 * @description: 安全巡检数据库操作相关
 * @Author: LanJianRong
 * @Date: 2020-11-20
 * @FilePath: \construction_management\dao\safe_dao.go
 */

package dao

import (
	"errors"
	"fmt"
	"time"

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

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

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

func (d *SafeDao) GetInIdJoinAccount(ids []int) []viewmodels.SafeList {

	datalist := make([]viewmodels.SafeList, 0)
	err := d.engine.
		Table("`cm_safe` as cs").
		Select("cs.id, cs.`create_time`, cs.`inspection_detail`, cs.`inspection`,cs.code, cs.status, pa.`name` as `audit_name`, pa.`position`,cs.`bidsection_id`").
		In("cs.id", ids).
		// Where("cs.id in ( "+ids+" ) ").
		Join("left", "cm_project_account as pa", "pa.id = cs.uid").
		Desc("id").
		Find(&datalist)
	if err != nil {
		return datalist
	} else {
		return datalist
	}
}

func (d *SafeDao) FindById(id int) *models.CmSafe {
	data := &models.CmSafe{Id: id}
	ok, err := d.engine.Get(data)
	if ok && err == nil {
		return data
	} else {
		data.Id = 0
		return data
	}
}

// id获得数据
func (d *SafeDao) GetListByBid(id int, pageNo int, pageSize int) ([]models.CmSafe, int64) {
	dataList := make([]models.CmSafe, 0)
	start := (pageNo - 1) * pageSize
	total, err := d.engine.
		Where("bidsection_id=?", id).
		Asc("id").
		Limit(pageSize, start).
		FindAndCount(&dataList)
	if err != nil {
		return dataList, 0
	}
	return dataList, total
}

// 插入单条记录
func (d *SafeDao) InsertRecord(data models.CmSafe) (bool, error) {
	affected, err := d.engine.InsertOne(data)
	return affected > 0, err
}

// 删除记录
func (d *SafeDao) DeleteRecord(id int) error {
	session := d.engine.NewSession()
	defer session.Close()
	err := session.Begin()
	if err != nil {
		session.Rollback()
		return err
	}

	safe := &models.CmSafe{Id: id}
	_, err = session.Get(safe)
	if err != nil {
		session.Rollback()
		return err
	}
	bidsectionId := safe.BidsectionId
	_, err = session.ID(id).Delete(safe)
	if err != nil {
		session.Rollback()
		return err
	}
	// 计算当前安全巡检总数
	_, err = session.Exec("update `cm_tree` set `safe_total` = if(`safe_total` >= 1, `safe_total` - 1, 0), `safe_rectification` = if(`safe_rectification` >= 1, `safe_rectification` - 1, 0) where `bidsection_id` = ?", bidsectionId)
	if err != nil {
		session.Rollback()
		return err
	}
	err = session.Commit()
	return err
}

// 根据code获取记录
func (d *SafeDao) FindByCode(code string) (bool, error) {
	data := &models.CmSafe{Code: code}
	has, err := d.engine.Get(data)
	return has, err
}

// 筛选出应用了当前规则的条数
func (d *SafeDao) CountRuleCode(bid int) (int64, error) {
	data := &models.CmSafe{}
	total, err := d.engine.Where("`bidsection_id` = ?", bid).Count(data)
	if err != nil {
		total = 0
	}
	return total, err
}

// 更改status
func (d *SafeDao) ChangeStatus(id int, status int, inspection string, inspectionDetail string, demand string, createTime time.Time) error {
	data := &models.CmSafe{Status: status, Inspection: inspection, InspectionDetail: inspectionDetail, Demand: demand, CreateTime: createTime}
	_, err := d.engine.ID(id).Cols("status", " inspection", "inspection_detail", "demand", "create_time").Update(data)
	return err
}

// 获得某状态下的安全
func (d *SafeDao) GetStatus(bidsectionId int, status int) []models.CmSafe {
	datalist := make([]models.CmSafe, 0)
	_ = d.engine.
		Where("bidsection_id = ? and status=? ", bidsectionId, status).
		Desc("id").
		Find(&datalist)
	return datalist
}

// 获得某状态下的安全
func (d *SafeDao) GetStatusByProjectAndAccount(projectId int, projectAccountId int, status int) []models.CmSafe {
	datalist := make([]models.CmSafe, 0)
	_ = d.engine.
		Where("project_id = ? and audit_id= ? and status=? ", projectId, projectAccountId, status).
		Desc("id").
		Find(&datalist)
	return datalist
}

// 获得某年份下的审批记录
func (d *SafeDao) GetTypeYear(bidsectionId int, year int) []viewmodels.SafeSurveyList {

	startYear := fmt.Sprintf("%d-01-01:00.00.00", year)
	endYear := fmt.Sprintf("%d-12-31:23.59.59", year)

	datalist := make([]viewmodels.SafeSurveyList, 0)
	_ = d.engine.Table("`cm_safe` as cs").
		// s.`update_time`, , pa.`name` as `audit_name`
		Select("cs.id ,  cs.`create_time`, cs.`inspection_detail`, cs.status").
		Where("cs.bidsection_id = ?  and cs.create_time>='"+startYear+"' and cs.create_time<='"+endYear+"' ", bidsectionId).
		// Join("left", "cm_approver as s", "cs.id = s.data_id and s.data_id=1").
		// Join("left", "cm_project_account as pa", "pa.id = s.audit_id").
		Desc("id").
		Find(&datalist)
	// _ = d.engine.Table("`cm_approver` as cs").
	// 	Select("cs.data_id as id, cs.`update_time`, s.`inspection_detail`, cs.status, pa.`name` as `audit_name`").
	// 	Where("cs.bidsection_id = ? and status=1 and progress=1 and data_type=1 and cs.update_time>='"+startYear+"' and cs.update_time<='"+endYear+"' ", bidsectionId).
	// 	Join("left", "cm_safe as s", "s.id = cs.data_id").
	// 	Join("left", "cm_project_account as pa", "pa.id = cs.audit_id").
	// 	Desc("id").
	// 	Find(&datalist)
	return datalist
}

// // 获得某年份下的安全
// func (d *SafeDao) GetTypeYear(bidsectionId int, year int) []viewmodels.SafeSurveyList {

// 	startYear := fmt.Sprintf("%d-01-01:00.00.00", year)
// 	endYear := fmt.Sprintf("%d-12-31:23.59.59", year)

// 	datalist := make([]viewmodels.SafeSurveyList, 0)
// 	_ = d.engine.Table("`cm_safe` as cs").
// 		Select("cs.id, cs.`create_time`, cs.`inspection_detail`, cs.status, pa.`name` as `audit_name`").
// 		Where("cs.bidsection_id = ? and cs.create_time>='"+startYear+"' and cs.create_time<='"+endYear+"' ", bidsectionId).
// 		Join("left", "cm_project_account as pa", "pa.id = cs.uid").
// 		Desc("id").
// 		Find(&datalist)
// 	return datalist
// }

// 根据bid
func (d *SafeDao) GetCountsByBid(bidsectionId int, status int, isAll bool) (int64, error) {
	data := &models.CmSafe{}
	if isAll == true {
		counts, err := d.engine.Where("bidsection_id = ?", bidsectionId).Count(data)
		return counts, err
	} else {
		counts, err := d.engine.Where("bidsection_id = ? and status = ?", bidsectionId, status).Count(data)
		return counts, err
	}
}

// 事务-创建新的安全巡检记录
func (d *SafeDao) CreateSafe(data models.CmSafe) error {
	session := d.engine.NewSession()
	defer session.Close()
	err := session.Begin()
	if err != nil {
		session.Rollback()
		return err
	}
	safe := models.CmSafe{}
	has, err := session.Where("code = ? and bidsection_id = ?", data.Code, data.BidsectionId).Get(&safe)
	// err或者code存在都回滚
	if err != nil {
		session.Rollback()
		return err
	}
	if has == true {
		session.Rollback()
		return errors.New("该编号已存在!")
	}
	_, err = session.Insert(data)
	if err != nil {
		session.Rollback()
		return err
	}
	// 更新cm_tree中的safe_total、safe_rectification
	_, err = session.Exec("update `cm_tree` set `safe_total` = `safe_total` + 1 , `safe_rectification` = `safe_rectification` + 1 where `bidsection_id` = ?", data.BidsectionId)
	if err != nil {
		session.Rollback()
		return err
	}
	err = session.Commit()
	if err != nil {
		return err
	}
	return nil
}