hz_crm_api/models/company/company_contract.go

package company

import (
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"strings"
	"time"
)

type CompanyContract struct {
	CompanyContractId int       `orm:"column(company_contract_id);pk" description:"客户合同id"`
	ContractType      string    `description:"合同类型：枚举值：'新签合同','续约合同','补充协议'"`
	ProductId         int       `description:"产品id"`
	ProductName       string    `description:"产品名称"`
	CompanyId         int       `description:"客户id"`
	CompanyName       string    `description:"客户id"`
	CompanyProductId  int       `description:"客户产品id"`
	ContractCode      string    `description:"合同编码"`
	StartDate         string    `description:"合同开始时间"`
	EndDate           string    `description:"合同结束时间"`
	Money             float64   `description:"合同金额"`
	PayMethod         string    `description:"支付方式"`
	PayChannel        string    `description:"支付渠道"`
	ImgUrl            string    `description:"合同图片，多个用#隔开"`
	CreateTime        time.Time `description:"合同创建时间"`
	ModifyTime        time.Time `description:"合同修改时间"`
	Status            int       `description:"状态"`
	Source            string    `description:"合同来源，枚举值：上传附件、系统合同，默认上传附件"`
	PackageType       int       `description:"套餐类型，0：无，1：大套餐，2：小套餐"`
	RaiPackageType    int       `description:"权益套餐类型: 0-无; 1-70w大套餐; 2-45w大套餐"`
	RaiContractType   string    `description:"权益合同类型：枚举值：'新签合同','续约合同','补充协议'"`
	SellerIdInit      int       `description:"销售id"`
	SellerNameInit    string    `description:"销售名称"`
	ShareSellerInit   string    `description:"共享销售员"`
	ShareSellerIdInit int       `description:"共享销售员id"`
	SellerIdLast      int       `description:"合同到期之前最后所属销售id"`
	SellerNameLast    string    `description:"合同到期之前最后所属销售名称"`
	ShareSellerLast   string    `description:"合同到期之前最后所属共享销售员"`
	ShareSellerIdLast int       `description:"合同到期之前最后所属共享销售员id"`
}

// GetCompanyContractPermissionByCompanyContractId 根据合同ID获取开通的权限
func CompanyContractList() (items []*CompanyContract, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	a.company_name,
	c.* 
FROM
	company_contract AS c
	INNER JOIN company a ON a.company_id = c.company_id 
WHERE
	1 = 1 
	AND c.company_id IN ( SELECT company_id FROM company_product WHERE 1 = 1 AND status = '正式' AND product_id = 2 ) 
  	AND c.company_contract_id IN ( SELECT company_contract_id FROM company_contract_permission WHERE is_upgrade = 1 AND end_date > '2025-02-23' AND chart_permission_id = 19 ) 
	AND c.status = 1
	AND c.product_id = 2 
	AND c.end_date > '2025-02-24' 
`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// GetCompanyContractPermissionByCompanyContractId 根据合同ID获取开通的权限
func CompanyContractListInit1() (items []*CompanyContract, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	a.company_name,
	c.* 
FROM
	company_contract AS c
	INNER JOIN company a ON a.company_id = c.company_id 
WHERE
	1 = 1 
	AND c.company_id IN ( SELECT company_id FROM company_product WHERE 1 = 1 AND status = '正式' AND product_id = 2 ) 
  	AND c.company_contract_id IN ( SELECT company_contract_id FROM company_contract_permission WHERE is_upgrade = 1 AND end_date > '2025-02-26'  ) 
	AND c.status = 1
	AND c.product_id = 2 
	AND c.end_date > '2025-02-26' 
`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// GetCompanyContractPermissionByCompanyContractId 根据合同ID获取开通的权限
func CompanyContractListInit2() (items []*CompanyContract, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	a.company_name,
	c.* 
FROM
	company_contract AS c
	INNER JOIN company a ON a.company_id = c.company_id 
WHERE
	1 = 1 
	AND c.company_id IN ( SELECT company_id FROM company_product WHERE 1 = 1 AND status = '正式' AND product_id = 2 ) 
	AND c.status = 1
	AND c.product_id = 2 
	AND c.rai_package_type  > 0 
	AND c.end_date > '2025-02-26' 
`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 新增客户合同
func AddCompanyContract(item *CompanyContract) (lastId int64, err error) {
	o := orm.NewOrm()
	lastId, err = o.Insert(item)
	return
}

func GetCompanyContractByCompanyId(companyId int) (items []*CompanyContract, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM company_contract WHERE company_id =? ORDER BY modify_time DESC `
	_, err = o.Raw(sql, companyId).QueryRows(&items)
	return
}

func GetCompanyContractCode() (companyCode string, err error) {
	var num int
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) AS num FROM company_contract`
	err = o.Raw(sql).QueryRow(&num)
	if err != nil {
		return
	}
	companyCode = "HZ" + time.Now().Format("20060102") + fmt.Sprintf("%03d", num)
	return
}

type CompanyContractPermission struct {
	Id                int       `orm:"column(id);pk" description:"客户合同id"`
	CompanyId         int       `description:"客户id"`
	CompanyContractId int       `description:"合同id"`
	ChartPermissionId int       `description:"权限id"`
	IsUpgrade         int       `description:"是否升级，1是，0否"`
	ExpensiveYx       int       `description:"权益研选: 0-3w; 1-5w"`
	StartDate         string    `description:"开始日期"`
	EndDate           string    `description:"结束日期"`
	CreateTime        time.Time `description:"创建时间"`
	ModifyTime        time.Time `description:"修改时间"`
	ContractType      string    `description:"签约合同时，当前行业类型：枚举值：'行业新签','行业续约'"`
	PermissionName    string    `description:"权限名称"`
	Points            float64   `description:"研选扣点包点数"`
}

// 新增客户合同
func AddCompanyContractPermission(item *CompanyContractPermission) (err error) {
	o := orm.NewOrm()
	_, err = o.Insert(item)
	return
}

// MultiAddCompanyContractPermission 批量新增客户合同权限
func MultiAddCompanyContractPermission(items []*CompanyContractPermission) (err error) {
	if len(items) == 0 {
		return
	}
	o := orm.NewOrm()
	_, err = o.InsertMulti(len(items), items)
	return
}

type CompanyContractPermissionLog struct {
	Id                int       `orm:"column(id);pk" description:"客户合同id"`
	CompanyId         int       `description:"客户id"`
	CompanyContractId int       `description:"合同id"`
	ChartPermissionId string    `description:"权限id"`
	StartDate         string    `description:"开始日期"`
	EndDate           string    `description:"结束日期"`
	CreateTime        time.Time `description:"创建时间"`
	ModifyTime        time.Time `description:"修改时间"`
	LogCreateTime     time.Time
}

// 新增客户合同
func AddCompanyContractPermissionLog(item *CompanyContractPermissionLog) (err error) {
	o := orm.NewOrm()
	_, err = o.Insert(item)
	return
}

// MultiAddCompanyContractPermissionLog 批量新增客户合同权限日志
func MultiAddCompanyContractPermissionLog(items []*CompanyContractPermissionLog) (err error) {
	if len(items) == 0 {
		return
	}
	o := orm.NewOrm()
	_, err = o.InsertMulti(len(items), items)
	return
}

func GetCompanyContractPermissionCheck(companyId, permissionId int) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) AS count FROM company_contract_permission AS a WHERE a.company_id=? AND a.chart_permission_id=? `
	err = o.Raw(sql, companyId, permissionId).QueryRow(&count)
	return
}

func GetCompanyContractPermissionCheckByContractId(companyId, companyContractId, permissionId int) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) AS count FROM company_contract_permission AS a WHERE a.company_id=? AND company_contract_id=? AND a.chart_permission_id=? `
	err = o.Raw(sql, companyId, companyContractId, permissionId).QueryRow(&count)
	return
}

// GetCompanyContractPermissionByCompanyContractId 根据合同ID获取开通的权限
func GetCompanyContractPermissionByCompanyContractId(companyContractId int) (items []*CompanyContractPermission, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM company_contract_permission WHERE company_contract_id =? ORDER BY modify_time DESC `
	_, err = o.Raw(sql, companyContractId).QueryRows(&items)
	return
}

// 根据合同id集合字符串获取权限列表数据
type CompanyContractPermissionName struct {
	Id                  int       `orm:"column(id);pk" description:"客户合同id"`
	CompanyId           int       `description:"客户id"`
	CompanyContractId   int       `description:"合同id"`
	ChartPermissionId   int       `description:"权限id"`
	ChartPermissionName string    `description:"权限名称"`
	PermissionName      string    `description:"权限名称"`
	PermissionRemark    string    `description:"权限备注"`
	ClassifyName        string    `description:"权限分类名称"`
	StartDate           string    `description:"开始日期"`
	EndDate             string    `description:"结束日期"`
	CreateTime          time.Time `json:"-" description:"创建时间"`
	ModifyTime          time.Time `json:"-" description:"修改时间"`
	IsUpgrade           int       `description:"是否升级，1是，0否"`
}

// 根据企业客户id获取已经审核通过的合同列表
func GetCompanyContractListByCompanyId(companyId int, condition string, pars []interface{}) (items []*CompanyContract, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM company_contract WHERE company_id =?  `

	if condition != "" {
		sql += condition
	}
	sql += " ORDER BY modify_time DESC"
	_, err = o.Raw(sql, companyId, pars).QueryRows(&items)
	return
}

// 根据合同Id集合获取合同的权限列表
func GetCompanyContractPermissionListByContractIds(companyContractIds string) (list []*CompanyContractPermissionName, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.*,b.permission_name,b.remark permission_remark,b.chart_permission_name,b.classify_name FROM company_contract_permission AS a left join chart_permission b on a.chart_permission_id = b.chart_permission_id WHERE company_contract_id in (` + companyContractIds + `) `
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// GetCompanyContractDetailByCompanyContractId 根据客户合同id获取合同信息
func GetCompanyContractDetailByCompanyContractId(companyContractId int) (item *CompanyContract, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM company_contract WHERE company_contract_id =? ORDER BY modify_time DESC `
	err = o.Raw(sql, companyContractId).QueryRow(&item)
	return
}

// GetCompanyContractDetailByCompanyContractId 根据客户合同id获取合同信息
func GetCompanyContractDetailByCompanyContractIdInit(companyId int) (item *CompanyContract, err error) {
	o := orm.NewOrm()
	sql := `SELECT  * FROM company_contract WHERE  company_id = ?   AND  product_id = 2   AND status = 1 ORDER BY company_contract_id DESC  LIMIT  1  `
	err = o.Raw(sql, companyId).QueryRow(&item)
	return
}

// GetCompanyContractPermissionListByContractId 根据合同Id获取合同的权限列表
func GetCompanyContractPermissionListByContractId(companyContractId int) (list []*CompanyContractPermissionName, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.*,b.chart_permission_name,b.classify_name FROM company_contract_permission AS a left join chart_permission b on a.chart_permission_id = b.chart_permission_id WHERE company_contract_id =? `
	_, err = o.Raw(sql, companyContractId).QueryRows(&list)
	return
}

func GetCompanyContractUpgradePermissionCheckByContractId(companyId, companyContractId, permissionId, isUpgrade int) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) AS count FROM company_contract_permission AS a WHERE a.company_id=? AND company_contract_id=? AND a.chart_permission_id=? AND a.is_upgrade=? `
	err = o.Raw(sql, companyId, companyContractId, permissionId, isUpgrade).QueryRow(&count)
	return
}

type CompanyContractResp struct {
	CompanyContractId int       `orm:"column(company_contract_id);pk" description:"客户合同id"`
	ContractType      string    `description:"合同类型：枚举值：'新签合同','续约合同','补充协议'"`
	ProductId         int       `description:"产品id"`
	ProductName       string    `description:"产品名称"`
	CompanyId         int       `description:"客户id"`
	CompanyProductId  int       `description:"客户产品id"`
	ContractCode      string    `description:"合同编码"`
	StartDate         string    `description:"合同开始时间"`
	EndDate           string    `description:"合同结束时间"`
	Money             float64   `description:"合同金额"`
	PayMethod         string    `description:"支付方式"`
	PayChannel        string    `description:"支付渠道"`
	ImgUrl            string    `description:"合同图片，多个用#隔开"`
	CreateTime        time.Time `description:"合同创建时间"`
	ModifyTime        time.Time `description:"合同修改时间"`
	Status            int       `description:"状态"`
	Source            string    `description:"合同来源，枚举值：上传附件、系统合同，默认上传附件"`
	PackageType       int       `description:"套餐类型，0：无，1：大套餐，2：小套餐"`
	RaiPackageType    int       `description:"权益套餐类型: 0-无; 1-70w大套餐; 2-45w大套餐"`
	PermissionName    string    `description:"权限名"`
}

// 获取合同列表
func GetCompanyContractList(condition string, pars []interface{}) (items []*CompanyContractResp, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM company_contract WHERE 1 = 1  `

	if condition != "" {
		sql += condition
	}
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// 获取合同对应的权限列表
func GetCompanyContractPermissionList(condition string, pars []interface{}) (items []*CompanyContractPermission, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM company_contract_permission WHERE 1 = 1  `

	if condition != "" {
		sql += condition
	}
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// UpdateCompanyContractPermissionMulti 批量修改
func UpdateCompanyContractPermissionMulti(items []*CompanyContractPermission) (err error) {
	o, err := orm.NewOrm().Begin()
	if err != nil {
		return
	}
	defer func() {
		if err == nil {
			o.Commit()
		} else {
			o.Rollback()
		}
	}()

	//批量修改
	p, err := o.Raw("UPDATE company_contract_permission SET permission_name = ? ,contract_type = ? WHERE id = ?").Prepare()
	if err != nil {
		return
	}
	defer func() {
		_ = p.Close() // 别忘记关闭 statement
	}()

	for _, v := range items {
		_, err = p.Exec(v.PermissionName, v.ContractType, v.Id)
		if err != nil {
			return
		}
	}
	return
}

// 通过ID获取详情
func GetCompanyContracDetail(condition string, pars []interface{}) (item *CompanyContractResp, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM company_contract  WHERE 1= 1 ` + condition
	err = o.Raw(sql, pars).QueryRow(&item)
	return
}

// 通过ID获取详情
func GetCompanyContracList(condition string, pars []interface{}) (items []*CompanyContractResp, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM company_contract  WHERE 1= 1 ` + condition
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

type GetCompanyContractDetailResp struct {
	Detail *CompanyContractResp
}

type GetCompanyContractDetailListResp struct {
	List []*CompanyContractResp
}

// UpdateCompanyContractPackageDifference 更改合同与上一份合同金额对比信息
func UpdateCompanyContractPackageDifference(packageDifference string, companyContractId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE company_contract SET package_difference = ?  WHERE company_contract_id=? `
	_, err = o.Raw(sql, packageDifference, companyContractId).Exec()
	return
}

// 更新合同类型
func UpdateCompanyContractType(contractType string, companyContractId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE company_contract SET contract_type = ?  WHERE company_contract_id=? `
	_, err = o.Raw(sql, contractType, companyContractId).Exec()
	return
}

// GetLastContractListByEndDate 通过最近一份合同的日期获取早于该合同的最晚一份合同
func GetLastContractListByEndDate(companyId, productId int, endDate string) (item *CompanyContract, err error) {
	o := orm.NewOrm()
	sql := "SELECT * FROM company_contract where company_id = ? AND product_id= ? AND end_date < ? AND status = 1 ORDER BY end_date desc"
	err = o.Raw(sql, companyId, productId, endDate).QueryRow(&item)
	return
}

// 获取权益近一年是否有新签合同
func GetCompanyContractCountRaiByLastYear(companyId int, startDate string) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) AS total FROM company_contract AS a WHERE  product_id = 2  AND   rai_contract_type = '新签合同'  AND  company_id = ?  AND start_date > ? `
	err = o.Raw(sql, companyId, startDate).QueryRow(&count)
	return
}

// UpdateCompanyContractRaiContractType  更新某些合同权益的标识，为新签合同
func UpdateCompanyContractRaiContractTypeInit(companyContractId string) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE company_contract SET rai_contract_type = '新签合同'  WHERE company_contract_id IN  (` + companyContractId + `) `
	_, err = o.Raw(sql).Exec()
	return
}

// UpdateCompanyContractCompanyAscribeId 更改权益未续约归因
func UpdateCompanyContractCompanyAscribeId(companyAscribId, companyContractId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE company_contract SET company_ascribe_id = ?  WHERE company_contract_id=? `
	_, err = o.Raw(sql, companyAscribId, companyContractId).Exec()
	return
}

// GetFirstContractRai 权益获取第一份合同
func GetFirstContractRai(companyId int) (item *CompanyContract, err error) {
	o := orm.NewOrm()
	sql := " SELECT * FROM company_contract WHERE company_id = ?  AND product_id= 2  AND status = 1   ORDER BY   start_date  ASC  LIMIT  1  "
	err = o.Raw(sql, companyId).QueryRow(&item)
	return
}

// 更新合同类型
func UpdateCompanyContractTypeinit16_1_01(share_seller_init string, share_seller_id_init, companyId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE company_contract SET share_seller_init = ? , share_seller_id_init = ?  WHERE company_id = ?  AND product_id= 2 `
	_, err = o.Raw(sql, share_seller_init, share_seller_id_init, companyId).Exec()
	return
}

// 更新合同类型
func UpdateCompanyContractTypeinit16_1_02(seller_name_init string, seller_id_init, company_contract_id int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE company_contract SET seller_name_init = ? , seller_id_init = ?  WHERE company_contract_id = ?  AND product_id= 2 `
	_, err = o.Raw(sql, seller_name_init, seller_id_init, company_contract_id).Exec()
	return
}

// 合同未生效更新对应销售的信息
func UpdateCompanyContractSellerNotEffective(sellerId, shareSellerInit int, sellerName, shareSeller string, companyContractId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE company_contract SET seller_id_init = ? , seller_name_init = ? ,                         
                            share_seller_id_init = ? , share_seller_init = ? ,
                            seller_id_last = ? , seller_name_last = ? ,                          
                           share_seller_id_last = ? ,    share_seller_last= ?
                            WHERE company_contract_id = ?  AND product_id= 2 `
	_, err = o.Raw(sql, sellerId, sellerName, shareSellerInit, shareSeller, sellerId, sellerName, shareSellerInit, shareSeller, companyContractId).Exec()
	return
}

// 合同未到期更新对应销售的信息
func UpdateCompanyContractSellerUnexpired(sellerId, shareSellerInit int, sellerName, shareSeller string, companyContractId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE company_contract SET
                            seller_id_last = ? , 
                            seller_name_last = ? , 
                            share_seller_id_last  = ? , 
                            share_seller_last= ?  WHERE company_contract_id = ?  AND product_id= 2 `
	_, err = o.Raw(sql, sellerId, sellerName, shareSellerInit, shareSeller, companyContractId).Exec()
	return
}

type CompanyContractRespInit struct {
	MaxCompanyContractId int
	CompanyContractId    int       `orm:"column(company_contract_id);pk" description:"客户合同id"`
	ContractType         string    `description:"合同类型：枚举值：'新签合同','续约合同','补充协议'"`
	ProductId            int       `description:"产品id"`
	ProductName          string    `description:"产品名称"`
	CompanyId            int       `description:"客户id"`
	CompanyProductId     int       `description:"客户产品id"`
	ContractCode         string    `description:"合同编码"`
	StartDate            string    `description:"合同开始时间"`
	EndDate              time.Time `description:"合同结束时间"`
	MaxStartDate         time.Time `description:"合同结束时间"`
	Money                float64   `description:"合同金额"`
	PayMethod            string    `description:"支付方式"`
	PayChannel           string    `description:"支付渠道"`
	ImgUrl               string    `description:"合同图片，多个用#隔开"`
	CreateTime           time.Time `description:"合同创建时间"`
	ModifyTime           time.Time `description:"合同修改时间"`
	Status               int       `description:"状态"`
	Source               string    `description:"合同来源，枚举值：上传附件、系统合同，默认上传附件"`
	PackageType          int       `description:"套餐类型，0：无，1：大套餐，2：小套餐"`
	RaiPackageType       int       `description:"权益套餐类型: 0-无; 1-70w大套餐; 2-45w大套餐"`
	PermissionName       string    `description:"权限名"`
}

// 通过ID获取详情
func GetCompanyContracListMaxStartDate(condition string, pars []interface{}) (items []*CompanyContractRespInit, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			MAX( a.start_date ) AS max_start_date,
			COUNT(a.company_id) AS cc ,
			a.* 
		FROM
			company_contract a
			JOIN company b ON a.company_id = b.company_id
			JOIN company_product c ON a.company_id = c.company_id 
			AND a.product_id = c.product_id 
		WHERE
			1 = 1 
			
			AND c.product_id = 2 
			AND a.not_renewal_hide = 0 
			AND a.STATUS = 1 
			AND a.company_contract_id  NOT IN (SELECT company_contract_id FROM company_contract_no_renewed_ascribe )` + condition + ` GROUP BY a.company_id `
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// 通过ID获取详情
func GetCompanyContracListInit_CRM_16_1(condition string, pars []interface{}) (items []*CompanyContractRespInit, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			a.* 
		FROM
			company_contract a
			JOIN company b ON a.company_id = b.company_id
			JOIN company_product c ON a.company_id = c.company_id 
			AND a.product_id = c.product_id 
		WHERE
			1 = 1 
			AND c.product_id = 2
			AND a.not_renewal_hide = 0 
			AND a.STATUS = 1 
			AND a.company_contract_id  NOT IN (SELECT company_contract_id FROM company_contract_no_renewed_ascribe ) ` + condition
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// 合同未生效更新对应销售的信息
func UpdateCompanyContracthide(companyContractId []string) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE company_contract SET not_renewal_hide = 1                                
                            WHERE company_contract_id  IN  (` + strings.Join(companyContractId, ",") + `) `
	_, err = o.Raw(sql).Exec()
	return
}