hz_crm_api/models/seal/seal.go

package seal

import (
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"hongze/hz_crm_api/models/contract"
	"hongze/hz_crm_api/utils"
	"time"
)

// Seal 用印结构体
type Seal struct {
	SealId            int       `orm:"column(seal_id);pk"`
	Code              string    `description:"用印编号,长度32位"`
	UserId            int       `description:"申请人id"`
	UserName          string    `description:"申请人名称"`
	Use               string    `description:"用印用途，枚举值：'销售合同', '渠道合同', '付款通知函', '招投标', '战略合作协议'；默认：销售合同"`
	CompanyName       string    `description:"客户名称，甲方名称,长度32位"`
	UseCompanyName    string    `description:"实际使用方客户名称，长度32位"`
	CreditCode        string    `description:"社会统一信用代码,长度64位"`
	ServiceType       string    `description:"业务类型，枚举值：'新签合同','续约合同','补充协议'；默认：新签合同"`
	SealType          string    `description:"加盖印章类型，枚举值：'合同章', '公章', '法人章';默认：合同章"`
	Status            string    `description:"合同状态，枚举值：'待提交','待审批','已撤回','已审批','已驳回','已作废','已签回'"`
	Remark            string    `description:"补充内容,长度255位"`
	ApprovalRemark    string    `description:"审核备注"`
	FileUrl           string    `description:"文件附件地址"`
	CheckBackFileUrl  string    `description:"已签回的文件地址"`
	FileNum           int       `description:"文件附件份数"`
	ContractId        int       `description:"系统合同id"`
	IsDelete          int       `json:"-" description:"是否已经删除，0：未删除，1：已删除"`
	ApproveTime       time.Time `description:"审批时间"`
	InvalidTime       time.Time `description:"作废时间"`
	ModifyTime        time.Time `description:"最近一次修改时间"`
	CreateTime        time.Time `description:"添加时间"`
	CheckBackFileTime time.Time `description:"签回用印附件时间"`
	AffiliatedCompany string    `description:"归属公司"`
	ProductId         int       `description:"产品id，1：ficc；2：权益."`
	CompanyId         int       `description:"客户id."`
	StartDate         string    `description:"开始日期."`
	EndDate           string    `description:"结束日期。"`
	CompanyContractId int       `description:"合同id"`
}

var EnumUse = []string{"销售合同", "渠道合同", "付款通知函", "招投标", "战略合作协议", "代付合同", "总对总协议"}
var EnumServiceType = []string{"新签合同", "续约合同", "补充协议", "代付合同"}

//var EnumSealType = []string{"合同章", "公章", "法人章"}

// GetSealInfoById 根据用印id获取合同信息
func GetSealInfoById(sealId int) (sealInfo *Seal, err error) {
	o := orm.NewOrm()
	sql := `select * from seal where seal_id = ? `
	err = o.Raw(sql, sealId).QueryRow(&sealInfo)
	return
}

// GetSealInfoByContractId 根据合同id获取合同信息
func GetSealInfoByContractId(sealId int) (sealInfo *Seal, err error) {
	o := orm.NewOrm()
	sql := `select * from seal where contract_id = ? and status in ("已审批","已签回") order by seal_id asc limit 1`
	err = o.Raw(sql, sealId).QueryRow(&sealInfo)
	return
}

// GetSealInfoByDateAndCompanyId 根据起始时间，公司ID获取合同信息
func GetSealInfoByDateAndCompanyId(startDate, endDate string, companyId int) (sealInfo *Seal, err error) {
	o := orm.NewOrm()

	sql := `select * from seal where start_date = ? and end_date = ? AND company_id =?  AND  product_id = 2   AND  company_contract_id  = 0  order by seal_id asc limit 1 `
	err = o.Raw(sql, startDate, endDate, companyId).QueryRow(&sealInfo)
	return
}

// GetCountByDateAndCompanyId  根据起始时间，公司ID获取合同信息数量
func GetCountByDateAndCompanyId(startDate, endDate string, companyId int) (count int, err error) {
	o := orm.NewOrm()

	sql := "select count(*) AS COUNT from seal   where start_date = ? and end_date = ? AND company_id =?  AND  product_id = 2   AND  company_contract_id  = 0    "
	err = o.Raw(sql, startDate, endDate, companyId).QueryRow(&count)
	return
}

// AddSeal 用印添加
func AddSeal(sealInfo *Seal) (err error) {
	o := orm.NewOrm()
	//用印数据入库
	sealId, err := o.Insert(sealInfo)
	if err != nil {
		return
	}
	sealInfo.SealId = int(sealId)
	return
}

// Update 更新用印信息
func (seal *Seal) Update(cols []string) (err error) {
	o := orm.NewOrm()
	_, err = o.Update(seal, cols...)
	return
}

// DeleteSeal 删除用印
func DeleteSeal(sealInfo *Seal) (err error) {
	sealInfo.IsDelete = 1
	sealInfo.ModifyTime = time.Now()
	sealInfo.InvalidTime = time.Now()
	err = sealInfo.Update([]string{"IsDelete", "ModifyTime", "InvalidTime"})
	return
}

// Invalid 作废用印
func Invalid(sealInfo *Seal) (err error) {
	sealInfo.Status = "已作废"
	sealInfo.ModifyTime = time.Now()
	sealInfo.InvalidTime = time.Now()
	//修改用印数据入库
	err = sealInfo.Update([]string{"Status", "ModifyTime", "InvalidTime"})
	return
}

// UpdateSealCompanyContractId 添加用印与合同的关系
func UpdateSealCompanyContractId(companyContractId, sealId int) (err error) {
	//修改用印数据入库
	o := orm.NewOrm()
	msql := " UPDATE seal SET company_contract_id = ? WHERE seal_id = ? "
	_, err = o.Raw(msql, companyContractId, sealId).Exec()
	return
}

// GetSealCode 生成合同编号
func GetSealCode() (code string, err error) {
	var num int
	var num2 int
	o := orm.NewOrm()
	today := utils.GetToday(utils.FormatDate)
	sql := `SELECT COUNT(1) AS num FROM seal where create_time>=?`
	err = o.Raw(sql, today).QueryRow(&num)
	if err != nil {
		return
	}

	sql = `SELECT COUNT(1) AS num FROM contract where create_time>=?` // 把正式合同的部分也算上
	err = o.Raw(sql, today).QueryRow(&num2)
	num += num2

	code = "HZ" + time.Now().Format("20060102") + fmt.Sprintf("%03d", num)
	return
}

// GetSealCodeRai 生成权益合同编号1
func GetSealCodeRai() (code string, err error) {
	var num int
	var num2 int
	o := orm.NewOrm()
	today := utils.GetToday(utils.FormatDate)
	sql := `SELECT COUNT(1) AS num FROM seal where create_time>=?`
	err = o.Raw(sql, today).QueryRow(&num)
	if err != nil {
		return
	}

	sql = `SELECT COUNT(1) AS num FROM contract where create_time>=?` // 把正式合同的部分也算上
	err = o.Raw(sql, today).QueryRow(&num2)
	num += num2

	code = "HZEQ" + time.Now().Format("20060102") + fmt.Sprintf("%03d", num)
	return
}

type CompanyNameList struct {
	CompanyName string `description:"客户名称，甲方名称,长度32位"`
}

// GetCompanyNameList 获取客户名称列表数据
func GetCompanyNameList(name string) (list []*CompanyNameList, err error) {
	o := orm.NewOrm()
	sql := `select * from seal where company_name like '%` + name + `%' group by company_name order by modify_time desc `
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// GetCompanyNameListV2 获取客户名称列表数据V2版本
func GetCompanyNameListV2(childCondition, condition string, childPars, pars []interface{}) (list []*CompanyNameList, err error) {
	o := orm.NewOrm()
	sql := `SELECT c.company_name,a.modify_time from contract_approval a
	join 
	( SELECT max( contract_approval_id ) max_id,contract_id FROM contract_approval where 1=1 and approval_type = "seal"  `
	sql += childCondition
	sql += `  GROUP BY contract_id ) b on a.contract_approval_id=b.max_id
	JOIN seal c ON c.seal_id = a.contract_id 
	JOIN contract_approval_record d on a.contract_approval_id=d.contract_approval_id 
WHERE c.is_delete = 0 AND a.approval_type="seal" `
	sql += condition
	sql += ` group by company_name order by modify_time desc`
	_, err = o.Raw(sql, childPars, pars).QueryRows(&list)
	return
}

// GetListCount 获取用印列表数据数量
func GetListCount(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrm()
	sql := "select count(*) AS COUNT from seal where 1=1 AND is_delete = 0 "
	sql += condition
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

// GetList 获取用印列表
func GetList(condition string, pars []interface{}, startSize, pageSize int) (list []*Seal, err error) {
	o := orm.NewOrm()
	sql := "select * from seal where 1=1 AND is_delete = 0 "
	sql += condition
	sql += ` order by modify_time desc LIMIT ?,? `
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
	return
}

//type SealApprovalItem struct {
//	ContractApprovalId       int                     `description:"审批单ID"`
//	ContractId               int                     `description:"合同ID"`
//	ContractApprovalRecordId int                     `description:"审批流ID"`
//	Code                     string                  `description:"合同编号"`
//	Use                      string                  `description:"用印用途,枚举值：'销售合同','渠道合同','付款通知函','招投标','战略合作协议'"`
//	ContractType             string                  `description:"合同类型,枚举值：'新签合同','续约合同','补充协议','代付合同'"`
//	Status                   string                  `description:"合同状态，枚举值：待审批','已审批','已驳回','已撤回','已签回'，默认待审批"`
//	ApproveStatus            string                  `json:"-" description:"审批单状态，枚举值：待审批','已审批','已驳回','已撤回'，默认待审批"`
//	ApplyContent             string                  `description:"申请内容"`
//	ApplyUserId              int                     `description:"申请人ID"`
//	ApplyUserName            string                  `description:"申请人名称"`
//	ContractDetail           string                  `json:"-" description:"提交审批时的合同信息；用印快照"`
//	ContractInfo             contract.ContractDetail `json:"-" description:"提交审批时的合同信息；用印快照"`
//	ApproveRemark            string                  `description:"审核备注"`
//	SealType                 string                  `description:"用印类型"`
//	CompanyName              string                  `description:"客户名称"`
//	FileUrl                  string                  `description:"合同下载地址"`
//	CurrNodeId               int                     `description:"当前审批节点id"`
//	StartNodeId              int                     `description:"开始审批节点id"`
//	//UserId                   int                     `description:"申请人id"`
//	//UserName                 string                  `description:"申请人名称"`
//	SealId               int       `description:"用印审批ID"`
//	CreateTime           time.Time `description:"发起审批的时间"`
//	ModifyTime           time.Time `description:"最后一次修改的时间"`
//	ApproveTime          time.Time `description:"审批时间"`
//	InvalidTime          time.Time `description:"作废时间"`
//	CheckBackFileUrl     string    `description:"签回附件地址"`
//	CheckBackFileTime    time.Time `description:"签回用印附件时间"`
//	CreditCode           string    `description:"社会信用码"`
//	CreateTimeStr        string    `description:"发起审批的时间(字符串)"`
//	ModifyTimeStr        string    `description:"最后一次修改的时间(字符串)"`
//	ApproveTimeStr       string    `description:"审批时间(字符串)"`
//	InvalidTimeStr       string    `description:"作废时间(字符串)"`
//	CheckBackFileTimeStr string    `description:"签回用印附件时间(字符串)"`
//	ContractCode         string    `description:"合同编码"`
//	AffiliatedCompany    string    `description:"归属公司"`
//	ProductId            int       `description:"产品id，1：ficc；2：权益。"`
//}

type SealApprovalItem struct {
	ContractApprovalId       int    `description:"审批单ID"`
	ContractId               int    `description:"合同ID"`
	ContractApprovalRecordId int    `description:"审批流ID"`
	Code                     string `description:"合同编号"`
	//ContractCode             string                  `description:"合同编号"`
	Use            string                  `description:"用印用途,枚举值：'销售合同','渠道合同','付款通知函','招投标','战略合作协议'"`
	ContractType   string                  `description:"合同类型,枚举值：'新签合同','续约合同','补充协议','代付合同'"`
	Status         string                  `description:"合同状态，枚举值：待审批','已审批','已驳回','已撤回','已签回'，默认待审批"`
	ApproveStatus  string                  `json:"-" description:"审批单状态，枚举值：待审批','已审批','已驳回','已撤回'，默认待审批"`
	ApplyContent   string                  `description:"申请内容"`
	ApplyUserId    int                     `description:"申请人ID"`
	ApplyUserName  string                  `description:"申请人名称"`
	ContractDetail string                  `json:"-" description:"提交审批时的合同信息；用印快照"`
	ContractInfo   contract.ContractDetail `json:"-" description:"提交审批时的合同信息；用印快照"`
	ApproveRemark  string                  `description:"审核备注"`
	SealType       string                  `description:"用印类型"`
	CompanyName    string                  `description:"客户名称"`
	FileUrl        string                  `description:"合同下载地址"`
	CurrNodeId     int                     `description:"当前审批节点id"`
	StartNodeId    int                     `description:"开始审批节点id"`
	//UserId                   int                     `description:"申请人id"`
	//UserName                 string                  `description:"申请人名称"`
	SealId               int       `description:"用印审批ID"`
	CreateTime           time.Time `description:"发起审批的时间"`
	ModifyTime           time.Time `description:"最后一次修改的时间"`
	ApproveTime          time.Time `description:"审批时间"`
	InvalidTime          time.Time `description:"作废时间"`
	CheckBackFileUrl     string    `description:"签回附件地址"`
	CheckBackFileTime    time.Time `description:"签回用印附件时间"`
	CreditCode           string    `description:"社会信用码"`
	CreateTimeStr        string    `description:"发起审批的时间(字符串)"`
	ModifyTimeStr        string    `description:"最后一次修改的时间(字符串)"`
	ApproveTimeStr       string    `description:"审批时间(字符串)"`
	InvalidTimeStr       string    `description:"作废时间(字符串)"`
	CheckBackFileTimeStr string    `description:"签回用印附件时间(字符串)"`
	ContractCode         string    `description:"合同编码"`
	AffiliatedCompany    string    `description:"归属公司"`
	ProductId            int       `description:"产品id，1：ficc；2：权益。"`
}

// GetSealApprovalListByWhere 获取用印审批列表-分页
//func GetSealApprovalListByWhere(condition, joinCondition string, pars []interface{}, startSize, pageSize int) (list []*SealApprovalItem, total int, err error) {
//	o := orm.NewOrm()
//	fields := `a.contract_approval_id,c.contract_id,a.apply_content,a.approve_remark,a.apply_user_id,a.apply_user_name,a.curr_node_id,a.start_node_id,c.create_time,a.modify_time,a.status approval_status,
//			   c.status,c.seal_id,c.seal_type,c.service_type contract_type,c.use,c.company_name,c.file_url,c.approve_time,c.invalid_time,c.code,c.credit_code,c.check_back_file_time,c.check_back_file_url,c.product_id,c.affiliated_company `
//	sql := `SELECT ` + fields + ` from contract_approval a JOIN ( SELECT max( contract_approval_id ) max_id,contract_id FROM contract_approval where 1=1 and approval_type = "seal" `
//	sql += ` GROUP BY contract_id ) b on a.contract_approval_id=b.max_id
//	JOIN seal c ON c.seal_id = a.contract_id
//	JOIN contract_approval_record d on a.contract_approval_id=d.contract_approval_id ` + joinCondition + `
//WHERE c.is_delete = 0 AND a.approval_type="seal" `
//	sql += condition
//	sql += ` group by c.seal_id order by c.create_time desc`
//	totalSql := `SELECT COUNT(1) total FROM (` + sql + `) z`
//	err = o.Raw(totalSql, pars).QueryRow(&total)
//	if err != nil {
//		return
//	}
//	sql += ` LIMIT ?,? `
//	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
//
//	return
//}

func GetSealApprovalListByWhere(condition, joinCondition string, pars []interface{}, startSize, pageSize int) (list []*SealApprovalItem, total int, err error) {
	//git
	o := orm.NewOrm()
	fields := `a.contract_approval_id,c.contract_id,a.apply_content,a.approve_remark,a.apply_user_id,a.apply_user_name,a.curr_node_id,a.start_node_id,c.create_time,a.modify_time,a.status approval_status,
			   c.status,c.seal_id,c.seal_type,c.service_type contract_type,c.use,c.company_name,c.file_url,c.approve_time,c.invalid_time,c.code,c.credit_code,c.check_back_file_time,c.check_back_file_url,c.product_id,c.affiliated_company `
	sql := `SELECT ` + fields + ` from contract_approval a JOIN ( SELECT max( contract_approval_id ) max_id,contract_id FROM contract_approval where 1=1 and approval_type = "seal" `
	sql += ` GROUP BY contract_id ) b on a.contract_approval_id=b.max_id
	JOIN seal c ON c.seal_id = a.contract_id 
	JOIN contract_approval_record d on a.contract_approval_id=d.contract_approval_id ` + joinCondition + `
WHERE c.is_delete = 0 AND a.approval_type="seal" `
	sql += condition
	sql += ` group by c.seal_id order by c.create_time desc`
	totalSql := `SELECT COUNT(1) total FROM (` + sql + `) z`
	err = o.Raw(totalSql, pars).QueryRow(&total)
	if err != nil {
		return
	}
	sql += ` LIMIT ?,? `
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)

	return
}

type SealApprovaExportlItem struct {
	ContractApprovalId       int `description:"审批单ID"`
	ContractId               int `description:"合同ID"`
	ContractApprovalRecordId int `description:"审批流ID"`
	//ContractCode             string                  `description:"合同编号"`
	Use            string                  `description:"用印用途,枚举值：'销售合同','渠道合同','付款通知函','招投标','战略合作协议'"`
	ContractType   string                  `description:"合同类型,枚举值：'新签合同','续约合同','补充协议','代付合同'"`
	Status         string                  `description:"合同状态，枚举值：待审批','已审批','已驳回','已撤回','已签回'，默认待审批"`
	ApproveStatus  string                  `json:"-" description:"审批单状态，枚举值：待审批','已审批','已驳回','已撤回'，默认待审批"`
	ApplyContent   string                  `description:"申请内容"`
	ApplyUserId    int                     `description:"申请人ID"`
	ApplyUserName  string                  `description:"申请人名称"`
	ContractDetail string                  `json:"-" description:"提交审批时的合同信息；用印快照"`
	ContractInfo   contract.ContractDetail `json:"-" description:"提交审批时的合同信息；用印快照"`
	ApproveRemark  string                  `description:"审核备注"`
	SealType       string                  `description:"用印类型"`
	CompanyName    string                  `description:"客户名称"`
	FileUrl        string                  `description:"合同下载地址"`
	CurrNodeId     int                     `description:"当前审批节点id"`
	StartNodeId    int                     `description:"开始审批节点id"`
	//UserId                   int                     `description:"申请人id"`
	//UserName                 string                  `description:"申请人名称"`
	SealId               int       `description:"用印审批ID"`
	CreateTime           time.Time `description:"发起审批的时间"`
	ModifyTime           time.Time `description:"最后一次修改的时间"`
	ApproveTime          time.Time `description:"审批时间"`
	InvalidTime          time.Time `description:"作废时间"`
	CheckBackFileUrl     string    `description:"签回附件地址"`
	CheckBackFileTime    time.Time `description:"签回用印附件时间"`
	CreditCode           string    `description:"社会信用码"`
	CreateTimeStr        string    `description:"发起审批的时间(字符串)"`
	ModifyTimeStr        string    `description:"最后一次修改的时间(字符串)"`
	ApproveTimeStr       string    `description:"审批时间(字符串)"`
	InvalidTimeStr       string    `description:"作废时间(字符串)"`
	CheckBackFileTimeStr string    `description:"签回用印附件时间(字符串)"`
	ContractCode         string    `description:"合同编码"`
	AffiliatedCompany    string    `description:"归属公司"`
	FirstLevelApprovers  string    `description:"一级审批人"`
	SecondLevelApprovers string    `description:"二级审批人"`
	ThirdLevelApprovers  string    `description:"三级审批人"`
	FirstLevelCC         string    `description:"一级抄送人"`
	SecondLevelCC        string    `description:"二级抄送人"`
	ThirdLevelCC         string    `description:"三级抄送人"`
}

// GetSealApprovalList 获取用印审批列表
func GetSealApprovalList(condition, joinCondition string, pars []interface{}) (list []*SealApprovaExportlItem, err error) {
	o := orm.NewOrm()
	fields := `a.contract_approval_id,c.contract_id,a.apply_content,a.approve_remark,a.apply_user_id,a.apply_user_name,a.curr_node_id,a.start_node_id,c.create_time,a.modify_time,a.status approval_status,
			   c.status,c.seal_id,c.seal_type,c.service_type contract_type,c.use,c.company_name,c.file_url,c.approve_time,c.invalid_time,c.code,c.credit_code,c.check_back_file_time,c.check_back_file_url,c.affiliated_company `
	sql := `SELECT ` + fields + ` from contract_approval a JOIN ( SELECT max( contract_approval_id ) max_id,contract_id FROM contract_approval where 1=1 and approval_type = "seal" `
	sql += ` GROUP BY contract_id ) b on a.contract_approval_id=b.max_id
	JOIN seal c ON c.seal_id = a.contract_id 
	JOIN contract_approval_record d on a.contract_approval_id=d.contract_approval_id ` + joinCondition + `
WHERE c.is_delete = 0 AND a.approval_type="seal" `
	sql += condition
	sql += ` group by c.seal_id order by c.create_time desc`
	_, err = o.Raw(sql, pars).QueryRows(&list)

	return
}