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"` 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"` } // 新增客户合同 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 } // 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 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:"权限名"` } // 通过ID获取详情 func GetCompanyContracListInit(condition string, pars []interface{}) (items []*CompanyContractRespInit, err error) { o := orm.NewOrm() sql := `SELECT MAX( company_contract_id ) AS max_company_contract_id, 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 b.company_name LIKE "%上海亘%" AND c.product_id = 2 -- AND b.company_name LIKE "%上海亘%" AND c.product_id = 2 AND a.not_renewal_hide = 0 AND a.STATUS = 1 AND c.STATUS NOT IN ( "永续", "正式", "关闭" ) AND a.company_contract_id NOT IN (SELECT company_contract_id FROM company_contract_no_renewed_ascribe ) GROUP BY a.company_id ` + condition _, 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 b.company_name LIKE "%上海亘%" AND c.product_id = 2 -- AND b.company_name LIKE "%上海亘%" AND c.product_id = 2 AND a.not_renewal_hide = 0 AND a.STATUS = 1 AND c.STATUS NOT IN ( "永续", "正式", "关闭" ) 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 }