package company import ( "github.com/beego/beego/v2/client/orm" "github.com/rdlucklib/rdluck_tools/paging" "hongze/hz_crm_api/models/contract" "hongze/hz_crm_api/models/system" "hongze/hz_crm_api/services/alarm_msg" "hongze/hz_crm_api/utils" "strconv" "strings" "time" ) type CompanyApproval struct { CompanyApprovalId int `orm:"column(company_approval_id);pk"` CompanyId int ProductId int ApproveStatus string `description:"'待审批','已审批','驳回','已撤回'"` ApproveUserId int `description:"审批人"` ApproveRoleTypeCode string `description:"审批人角色类型"` ApproveRealName string `description:"审批人姓名"` ApproveTime time.Time `description:"审批时间"` ApproveContent string `description:"待审内容"` ApplyMethod int `description:"申请类型：1:试用->正式,2:冻结—>试用,3:试用延期,4:原销售申请领取流失客户，5:正式客户申请服务更新，6：正式客户补充协议"` ApplyReasons string `description:"申请原因"` ApplyUserId int `description:"申请人"` ApplyRealName string `description:"申请人姓名"` CreateTime time.Time `description:"申请时间"` ModifyTime time.Time `description:"修改时间"` CompanyContractId int `description:"合同id"` IsFormal int `description:"是否已经转正式，0是没有转正式，1是已经转过正式"` } // AddCompanyApproval 新增客户产品 func AddCompanyApproval(item *CompanyApproval) (lastId int, err error) { o := orm.NewOrm() to, err := o.Begin() if err != nil { return } defer func() { if err != nil { tmpErr := to.Rollback() if tmpErr != nil { go alarm_msg.SendAlarmMsg("AddCompanyApproval 事务回滚失败，Err："+tmpErr.Error(), 3) } } else { err = to.Commit() } }() var newId int64 newId, err = to.Insert(item) if err != nil { return } lastId = int(newId) sql := `UPDATE company_product SET approve_status=? WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, item.ApproveStatus, item.CompanyId, item.ProductId).Exec() return } func GetCompanyApprovalCountById(companyId, productId int) (count int, err error) { sql := `SELECT COUNT(1) AS count FROM company_approval WHERE company_id=? AND product_id=? AND approve_status='待审批' ` o := orm.NewOrm() err = o.Raw(sql, companyId, productId).QueryRow(&count) return } func GetCompanyApprovalById(companyId, productId int) (item *CompanyApproval, err error) { sql := `SELECT * FROM company_approval WHERE company_id=? AND product_id=? AND approve_status='待审批' order by company_approval_id desc LIMIT 1 ` o := orm.NewOrm() err = o.Raw(sql, companyId, productId).QueryRow(&item) return } func GetCompanyApprovalByApprovalId(companyApprovalId int) (item *CompanyApproval, err error) { sql := `SELECT * FROM company_approval WHERE company_approval_id=? LIMIT 1 ` o := orm.NewOrm() err = o.Raw(sql, companyApprovalId).QueryRow(&item) return } type CompanyApprovalList struct { CompanyId int ProductId int CompanyName string `description:"客户名称"` CreditCode string `description:"社会统一信用码"` CompanyType string `description:"客户类型"` IndustryName string `description:"行业名称"` SellerName string `description:"销售名称"` CreateTime string `description:"创建时间"` ApprovalTime string `description:"提交申请时间"` ApproveTime string `description:"审批时间"` Status string `description:"客户状态"` ApproveContent string `description:"待审内容"` ApproveStatus string `description:"当前状态"` ApplyMethod int `description:"申请类型：1:试用->正式,2:冻结—>试用,3:流失—>正式,4:试用延期,5:原销售申请领取流失客户，6:正式客户申请服务更新"` ApplyReasons string `description:"申请理由"` FreezeStartDate string `description:"冻结开始日期"` FreezeEndDate string `description:"冻结结束日期"` FreezeExpireDays int `description:"冻结到期天数"` Address string `description:"地址"` Source string `description:"客户来源"` ApproveRemark string `description:"驳回理由"` StartDate string `description:"开始日期"` EndDate string `description:"结束日期"` ExpireDay int `description:"服务到期天数"` CompanyApprovalId int `description:"审批记录ID"` ApprovalCount int `description:"已延期审批次数"` CompanyContractId int `description:"合同id"` DelayPermission string `description:"延期申请品种"` ApplyRealName string `description:"申请人姓名"` OpButton ApprovalButton `description:"操作权限"` ApplyContractType int `description:"申请合同类型 0 非标 1 标准"` ApplyUserId int `description:"申请人编号id" json:"-"` } // ApprovalButton 审批操作权限 type ApprovalButton struct { Cancel bool `description:"是否具有撤回权限，true才允许审核操作"` Approval bool `description:"是否具有审核操作权限，true才允许审核操作"` //Cancel bool `description:"是否具有撤回权限"` } type CompanyApprovalListResp struct { Paging *paging.PagingItem List []*CompanyApprovalList } func GetApprovalList(condition string, pars []interface{}, startSize, pageSize int) (items []*CompanyApprovalList, err error) { o := orm.NewOrm() sql := `SELECT a.company_id,b.product_id,a.company_name,a.credit_code,a.address,b.company_type,b.industry_name, b.seller_name,b.create_time,b.freeze_start_date,b.freeze_end_date,b.source,c.create_time AS approval_time,c.approve_time,b.status, c.approve_content,c.approve_status,c.apply_method,c.apply_reasons,c.approve_remark,b.start_date,b.end_date,c.company_approval_id,c.company_contract_id, c.apply_real_name,c.apply_user_id FROM company AS a INNER JOIN company_product AS b ON a.company_id=b.company_id INNER JOIN company_approval AS c ON b.company_id=c.company_id AND b.product_id=c.product_id WHERE 1=1 ` if condition != "" { sql += condition } sql += ` ORDER BY c.modify_time DESC LIMIT ?,? ` _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items) return } func GetApprovalCount(condition string, pars []interface{}) (count int, err error) { o := orm.NewOrm() sql := `SELECT COUNT(1) AS count FROM company AS a INNER JOIN company_product AS b ON a.company_id=b.company_id INNER JOIN company_approval AS c ON b.company_id=c.company_id AND b.product_id=c.product_id WHERE 1=1 ` if condition != "" { sql += condition } sql += ` ORDER BY c.modify_time DESC ` err = o.Raw(sql, pars).QueryRow(&count) return } // TryOutToFormal 试用转正式 func TryOutToFormal(companyId, productId, sellerId, companyApprovalId, companyContractId int, startDate, endDate, sellerName, productName string, packageType, raiPackageType int) (newCompanyReportPermissionList []*CompanyReportPermission, err error) { o := orm.NewOrm() to, err := o.Begin() if err != nil { return } defer func() { if err != nil { tmpErr := to.Rollback() if tmpErr != nil { go alarm_msg.SendAlarmMsg("TryOutToFormal 事务回滚失败，Err："+tmpErr.Error(), 3) } } else { err = to.Commit() } }() // 套餐类型为0时, 不更新套餐类型 sql := `UPDATE company_product SET status='正式',try_out_time=NULL,last_description_time=NULL,freeze_time=NULL,renewal_intention=0,is_suspend=0,is_formal=1,approve_status='已审批',start_date=?,end_date=?,package_type=?,rai_package_type=?,modify_time=NOW(),formal_time=NOW(),try_stage=1 WHERE company_id=? AND product_id=? ` if packageType == 0 { sql = `UPDATE company_product SET status='正式',try_out_time=NULL,last_description_time=NULL,freeze_time=NULL,renewal_intention=0,is_suspend=0,is_formal=1,approve_status='已审批',start_date=?,end_date=?,rai_package_type=?,modify_time=NOW(),formal_time=NOW(),try_stage=1 WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, startDate, endDate, raiPackageType, companyId, productId).Exec() if err != nil { return } } else { _, err = to.Raw(sql, startDate, endDate, packageType, raiPackageType, companyId, productId).Exec() if err != nil { return } } sql = `UPDATE company SET type=1,last_updated_time=NOW() WHERE company_id=? ` _, err = to.Raw(sql, companyId).Exec() if err != nil { return } sql = `UPDATE company_approval SET approve_status='已审批',approve_time=NOW(),modify_time=NOW() WHERE company_approval_id=? AND company_id=? AND product_id=? ` _, err = to.Raw(sql, companyApprovalId, companyId, productId).Exec() if err != nil { return } items := make([]*CompanyReportPermission, 0) sql = `SELECT * FROM company_report_permission WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, companyId, productId).QueryRows(&items) for _, pv := range items { cpLog := new(CompanyPermissionLog) cpLog.CompanyId = companyId cpLog.ChartPermissionId = pv.ChartPermissionId cpLog.CreateTime = time.Now() cpLog.SysUserId = sellerId cpLog.SysUserName = sellerName cpLog.StartDate = pv.StartDate cpLog.EndDate = pv.EndDate cpLog.ProductId = productId cpLog.ProductName = pv.ProductName go AddCompanyPermissionLog(cpLog) } sql = `DELETE FROM company_report_permission WHERE company_id=? AND product_id=?` _, err = to.Raw(sql, companyId, productId).Exec() if err != nil { return } contractPermission := make([]*CompanyReportPermission, 0) sql = `SELECT * FROM company_contract_permission WHERE company_contract_id=? AND company_id=? ` _, err = to.Raw(sql, companyContractId, companyId).QueryRows(&contractPermission) if err != nil { return } for _, pv := range contractPermission { tmpCompanyReportPermission := &CompanyReportPermission{ //CompanyReportPermissionId: 0, CompanyId: companyId, ReportPermissionId: pv.ChartPermissionId, CreatedTime: time.Now(), LastUpdatedTime: time.Now(), ChartPermissionId: pv.ChartPermissionId, StartDate: pv.StartDate, EndDate: pv.EndDate, ProductId: productId, ProductName: productName, CompanyContractId: companyContractId, Status: "正式", ModifyTime: time.Now(), IsUpgrade: pv.IsUpgrade, ExpensiveYx: pv.ExpensiveYx, } ////CRM13.2 升级套餐 //if tmpCompanyReportPermission.ChartPermissionId > 10000 { // tmpCompanyReportPermission.ChartPermissionId -= 10000 // tmpCompanyReportPermission.ReportPermissionId -= 10000 // tmpCompanyReportPermission.IsUpgrade = 1 //} newId, tmpErr := to.Insert(tmpCompanyReportPermission) if tmpErr != nil { err = tmpErr return } tmpCompanyReportPermission.CompanyReportPermissionId = newId newCompanyReportPermissionList = append(newCompanyReportPermissionList, tmpCompanyReportPermission) //sql = `INSERT INTO company_report_permission(company_id, report_permission_id,created_time, last_updated_time, // chart_permission_id, start_date,end_date,product_id,product_name, modify_time,company_contract_id,status,is_upgrade) // VALUES(?,?,NOW(),NOW(),?,?,?,?,?,NOW(),?,?,?) ` //_, err = to.Raw(sql, companyId, pv.ChartPermissionId, pv.ChartPermissionId, pv.StartDate, pv.EndDate, productId, productName, companyContractId, "正式", pv.IsUpgrade).Exec() //if err != nil { // return //} } sql = `UPDATE company_contract SET status=1 WHERE company_contract_id=? AND company_id=? AND product_id=? ` _, err = to.Raw(sql, companyContractId, companyId, productId).Exec() if err != nil { return } return } // TryOutToFormalXClassRai 权益客户试用转正式 func TryOutToFormalXClassRai(companyId, productId, sellerId, companyApprovalId, companyContractId int, startDate, endDate, sellerName, productName string, raiPackageType int) (newCompanyReportPermissionList []*CompanyReportPermission, err error) { o := orm.NewOrm() to, err := o.Begin() if err != nil { return } defer func() { if err != nil { tmpErr := to.Rollback() if tmpErr != nil { go alarm_msg.SendAlarmMsg("TryOutToFormalXClassRai 事务回滚失败，Err："+tmpErr.Error(), 3) } } else { err = to.Commit() } }() //123月份申请转正通过时，将试用期限延长(更新)至4.30。456月份申请转正通过时，将试用期限延长至7.31。789月份申请转正通过时，将试用期限延长至10.31。101112月份申请转正通过时，将试用期限延长至次年1.31。 endDate = utils.GetLastDayOfQuarter(time.Now()).Format(utils.FormatDate) // 套餐类型为0时, 不更新套餐类型，权益与FICC开始与结束时间一起修改 sql := `UPDATE company_product SET status='永续',try_out_time=NULL,last_description_time=NULL,freeze_time=NULL,renewal_intention=0,is_suspend=0,is_formal=1,approve_status='已审批',end_date=?,rai_package_type=?,modify_time=NOW(),formal_time=NOW(),try_stage=1 WHERE company_id=? ` _, err = to.Raw(sql, endDate, raiPackageType, companyId).Exec() if err != nil { return } sql = `UPDATE company SET type=1,last_updated_time=NOW() WHERE company_id=? ` _, err = to.Raw(sql, companyId).Exec() if err != nil { return } sql = `UPDATE company_approval SET approve_status='已审批',approve_time=NOW(),modify_time=NOW() WHERE company_approval_id=? AND company_id=? AND product_id=? ` _, err = to.Raw(sql, companyApprovalId, companyId, productId).Exec() if err != nil { return } items := make([]*CompanyReportPermission, 0) sql = `SELECT * FROM company_report_permission WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, companyId, productId).QueryRows(&items) for _, pv := range items { cpLog := new(CompanyPermissionLog) cpLog.CompanyId = companyId cpLog.ChartPermissionId = pv.ChartPermissionId cpLog.CreateTime = time.Now() cpLog.SysUserId = sellerId cpLog.SysUserName = sellerName cpLog.StartDate = pv.StartDate cpLog.EndDate = pv.EndDate cpLog.ProductId = productId cpLog.ProductName = pv.ProductName go AddCompanyPermissionLog(cpLog) } sql = `DELETE FROM company_report_permission WHERE company_id=? AND product_id=?` _, err = to.Raw(sql, companyId, productId).Exec() if err != nil { return } contractPermission := make([]*CompanyReportPermission, 0) sql = `SELECT * FROM company_contract_permission WHERE company_contract_id=? AND company_id=? ` _, err = to.Raw(sql, companyContractId, companyId).QueryRows(&contractPermission) if err != nil { return } for _, pv := range contractPermission { tmpCompanyReportPermission := &CompanyReportPermission{ //CompanyReportPermissionId: 0, CompanyId: companyId, ReportPermissionId: pv.ChartPermissionId, CreatedTime: time.Now(), LastUpdatedTime: time.Now(), ChartPermissionId: pv.ChartPermissionId, StartDate: pv.StartDate, EndDate: endDate, ProductId: productId, ProductName: productName, CompanyContractId: companyContractId, Status: "永续", ModifyTime: time.Now(), IsUpgrade: pv.IsUpgrade, ExpensiveYx: pv.ExpensiveYx, } newId, tmpErr := to.Insert(tmpCompanyReportPermission) if tmpErr != nil { err = tmpErr return } tmpCompanyReportPermission.CompanyReportPermissionId = newId newCompanyReportPermissionList = append(newCompanyReportPermissionList, tmpCompanyReportPermission) } sql = `UPDATE company_report_permission SET status='永续', end_date=? WHERE company_id=? ` // 更改权限的开始时间结束时间 _, err = to.Raw(sql, endDate, companyId).Exec() if err != nil { return } sql = `UPDATE company_contract SET status=1 WHERE company_contract_id=? AND company_id=? AND product_id=? ` _, err = to.Raw(sql, companyContractId, companyId, productId).Exec() if err != nil { return } return } // FreezeToTryOut 冻结转试用 func FreezeToTryOut(companyId, productId, sellerId, companyApprovalId, applyUserId int, sellerName, productName string) (newCompanyReportPermissionList []*CompanyReportPermission, startDate, endDate string, err error) { o := orm.NewOrm() to, err := o.Begin() if err != nil { return } defer func() { if err != nil { tmpErr := to.Rollback() if tmpErr != nil { go alarm_msg.SendAlarmMsg("FreezeToTryOut 事务回滚失败，Err："+tmpErr.Error(), 3) } } else { err = to.Commit() } }() startDate = time.Now().Format(utils.FormatDate) endDate = time.Now().AddDate(0, 2, 0).Format(utils.FormatDate) //sellerItem, err := system.GetSysAdminById(applyUserId) //if err != nil { // return //} //更新用户产品状态 //6.9 fix 未续约强制弹窗要求客户冻结转试用后不弹床，取消清空冻结时间以标记曾经被冻结过 sql := `UPDATE company_product SET status='试用',is_suspend=0,approve_status='已审批',freeze_start_date=null,freeze_end_date=null, start_date=?,end_date=?,modify_time=NOW(),try_stage=1 WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, startDate, endDate, companyId, productId).Exec() if err != nil { return } //更新用户状态 sql = `UPDATE company SET type=2,last_updated_time=NOW() WHERE company_id=? ` _, err = to.Raw(sql, companyId).Exec() if err != nil { return } //更新审批单 sql = `UPDATE company_approval SET approve_status='已审批',approve_time=NOW(),modify_time=NOW() WHERE company_approval_id=? AND company_id=? AND product_id=? ` _, err = to.Raw(sql, companyApprovalId, companyId, productId).Exec() if err != nil { return } //查询该用户所有产品权限，并把当前数据做日志（允许添加不修改的数据） items := make([]*CompanyReportPermission, 0) sql = `SELECT * FROM company_report_permission WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, companyId, productId).QueryRows(&items) for _, pv := range items { cpLog := new(CompanyPermissionLog) cpLog.CompanyId = companyId cpLog.ChartPermissionId = pv.ChartPermissionId cpLog.CreateTime = time.Now() cpLog.SysUserId = sellerId cpLog.SysUserName = sellerName cpLog.StartDate = pv.StartDate cpLog.EndDate = pv.EndDate cpLog.ProductId = productId cpLog.ProductName = pv.ProductName go AddCompanyPermissionLog(cpLog) } //更新产品（2021年03月02日14:58:50注释，调整为从申请权限表中获取数据并修改） //sql = `UPDATE company_report_permission SET start_date=?,end_date=?,modify_time=NOW() WHERE company_id=? AND product_id=? ` //_, err = to.Raw(sql, startDate, endDate, companyId, productId).Exec() //if err != nil { // return //} //查询原先是否已经存在权限，如果有权限了，那么需要先删除原有的权限 count, _ := GetCompanyReportPermissionCount(companyId, productId) if count > 0 { sql := ` DELETE FROM company_report_permission WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, companyId, productId).Exec() if err != nil { return } } //获取需要试用的权限 delayPermissionItems, tmpErr := GetDelayPermissionItems(companyId, companyApprovalId) if tmpErr != nil { err = tmpErr return } status := `试用` for _, v := range delayPermissionItems { tmpCompanyReportPermission := &CompanyReportPermission{ //CompanyReportPermissionId: 0, CompanyId: companyId, ReportPermissionId: v.ChartPermissionId, CreatedTime: time.Now(), LastUpdatedTime: time.Now(), ChartPermissionId: v.ChartPermissionId, StartDate: v.StartDate, EndDate: v.EndDate, ProductId: productId, ProductName: productName, //CompanyContractId: companyContractId, Status: status, ModifyTime: time.Now(), //IsUpgrade: pv.IsUpgrade, } newId, tmpErr := to.Insert(tmpCompanyReportPermission) if tmpErr != nil { err = tmpErr return } tmpCompanyReportPermission.CompanyReportPermissionId = newId newCompanyReportPermissionList = append(newCompanyReportPermissionList, tmpCompanyReportPermission) //sql = `INSERT INTO company_report_permission(company_id, report_permission_id,created_time, last_updated_time, // chart_permission_id, start_date,end_date,product_id,product_name, modify_time,status) // VALUES(?,?,NOW(),NOW(),?,?,?,?,?,NOW(),?) ` //_, err = to.Raw(sql, companyId, v.ChartPermissionId, v.ChartPermissionId, v.StartDate, v.EndDate, productId, productName, status).Exec() //if err != nil { // return //} } return } // FreezeToTryOutXClassRai 权益冻结转X类试用 func FreezeToTryOutXClassRai(companyId, productId, sellerId, companyApprovalId, applyUserId int, sellerName, productName string) (newCompanyReportPermissionList []*CompanyReportPermission, startDate, endDate string, err error) { o := orm.NewOrm() to, err := o.Begin() if err != nil { return } defer func() { if err != nil { tmpErr := to.Rollback() if tmpErr != nil { go alarm_msg.SendAlarmMsg("FreezeToTryOutXClassRai 事务回滚失败，Err："+tmpErr.Error(), 3) } } else { err = to.Commit() } }() startDate = time.Now().Format(utils.FormatDate) endDate = time.Now().AddDate(0, 2, 0).Format(utils.FormatDate) //sellerItem, err := system.GetSysAdminById(applyUserId) //if err != nil { // return //} //更新用户产品状态 sql := `UPDATE company_product SET status='永续',is_suspend=0,approve_status='已审批',freeze_start_date=null,freeze_end_date=null, start_date=?,end_date=?,modify_time=NOW(),try_stage=1 WHERE company_id=? ` _, err = to.Raw(sql, startDate, endDate, companyId).Exec() if err != nil { return } //更新用户状态 sql = `UPDATE company SET type=5,last_updated_time=NOW() WHERE company_id=? ` _, err = to.Raw(sql, companyId).Exec() if err != nil { return } //更新审批单 sql = `UPDATE company_approval SET approve_status='已审批',approve_time=NOW(),modify_time=NOW() WHERE company_approval_id=? AND company_id=? AND product_id=? ` _, err = to.Raw(sql, companyApprovalId, companyId, productId).Exec() if err != nil { return } //查询该用户所有产品权限，并把当前数据做日志（允许添加不修改的数据） items := make([]*CompanyReportPermission, 0) sql = `SELECT * FROM company_report_permission WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, companyId, productId).QueryRows(&items) for _, pv := range items { cpLog := new(CompanyPermissionLog) cpLog.CompanyId = companyId cpLog.ChartPermissionId = pv.ChartPermissionId cpLog.CreateTime = time.Now() cpLog.SysUserId = sellerId cpLog.SysUserName = sellerName cpLog.StartDate = pv.StartDate cpLog.EndDate = pv.EndDate cpLog.ProductId = productId cpLog.ProductName = pv.ProductName go AddCompanyPermissionLog(cpLog) } //查询原先是否已经存在权限，如果有权限了，那么需要先删除原有的权限 count, _ := GetCompanyReportPermissionCount(companyId, productId) if count > 0 { sql := ` DELETE FROM company_report_permission WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, companyId, productId).Exec() if err != nil { return } } sql = `UPDATE company_report_permission SET status='永续',start_date=?,end_date=? WHERE company_id=? ` // 更改权限的开始时间结束时间 _, err = to.Raw(sql, startDate, endDate, companyId).Exec() if err != nil { return } //获取需要试用的权限 delayPermissionItems, tmpErr := GetDelayPermissionItems(companyId, companyApprovalId) if tmpErr != nil { err = tmpErr return } status := `永续` for _, v := range delayPermissionItems { tmpCompanyReportPermission := &CompanyReportPermission{ //CompanyReportPermissionId: 0, CompanyId: companyId, ReportPermissionId: v.ChartPermissionId, CreatedTime: time.Now(), LastUpdatedTime: time.Now(), ChartPermissionId: v.ChartPermissionId, StartDate: v.StartDate, EndDate: endDate, ProductId: productId, ProductName: productName, //CompanyContractId: companyContractId, Status: status, ModifyTime: time.Now(), //IsUpgrade: pv.IsUpgrade, } newId, tmpErr := to.Insert(tmpCompanyReportPermission) if tmpErr != nil { err = tmpErr return } tmpCompanyReportPermission.CompanyReportPermissionId = newId newCompanyReportPermissionList = append(newCompanyReportPermissionList, tmpCompanyReportPermission) } return } // TryOutDelay 试用延期 func TryOutDelay(companyId, productId, sellerId, companyApprovalId int, sellerName, endDate, productName string) (newCompanyReportPermissionList []*CompanyReportPermission, newEndDate string, err error) { o := orm.NewOrm() to, err := o.Begin() if err != nil { return } defer func() { if err != nil { tmpErr := to.Rollback() if tmpErr != nil { go alarm_msg.SendAlarmMsg("TryOutDelay 事务回滚失败，Err："+tmpErr.Error(), 3) } } else { err = to.Commit() } }() endDateTime, err := time.Parse(utils.FormatDate, endDate) if err != nil { return } newEndDate = endDateTime.AddDate(0, 2, 0).Format(utils.FormatDate) //更新用户产品状态 sql := `UPDATE company_product SET approve_status='已审批',is_suspend=0,end_date=?,modify_time=NOW() WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, newEndDate, companyId, productId).Exec() if err != nil { return } //更新审批单 sql = `UPDATE company_approval SET approve_status='已审批',approve_time=NOW(),modify_time=NOW() WHERE company_approval_id=? AND company_id=? AND product_id=? ` _, err = to.Raw(sql, companyApprovalId, companyId, productId).Exec() if err != nil { return } //查询该用户所有产品权限，并把当前数据做日志（允许添加不修改的数据） items := make([]*CompanyReportPermission, 0) sql = `SELECT * FROM company_report_permission WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, companyId, productId).QueryRows(&items) for _, pv := range items { cpLog := new(CompanyPermissionLog) cpLog.CompanyId = companyId cpLog.ChartPermissionId = pv.ChartPermissionId cpLog.CreateTime = time.Now() cpLog.SysUserId = sellerId cpLog.SysUserName = sellerName cpLog.StartDate = pv.StartDate cpLog.EndDate = pv.EndDate cpLog.ProductId = productId cpLog.ProductName = pv.ProductName go AddCompanyPermissionLog(cpLog) } //获取需要延期的权限 delayPermissionItems, err := GetDelayPermissionItems(companyId, companyApprovalId) if err != nil { return } status := `试用` for _, v := range delayPermissionItems { tmpCompanyReportPermission, tmpErr := GetCompanyPermission(companyId, v.ChartPermissionId) if tmpErr != nil { //没有数据的话 if tmpErr.Error() == utils.ErrNoRow() { tmpCompanyReportPermission = &CompanyReportPermission{ //CompanyReportPermissionId: 0, CompanyId: companyId, ReportPermissionId: v.ChartPermissionId, CreatedTime: time.Now(), LastUpdatedTime: time.Now(), ChartPermissionId: v.ChartPermissionId, StartDate: v.StartDate, EndDate: newEndDate, ProductId: productId, ProductName: productName, //CompanyContractId: companyContractId, Status: status, ModifyTime: time.Now(), //IsUpgrade: pv.IsUpgrade, } newId, tmpErr := to.Insert(tmpCompanyReportPermission) if tmpErr != nil { err = tmpErr return } tmpCompanyReportPermission.CompanyReportPermissionId = newId } else { // 其他异常的话，那么就返回报错 err = tmpErr return } } else { // 修改原数据 tmpCompanyReportPermission.EndDate = newEndDate tmpCompanyReportPermission.Status = status tmpCompanyReportPermission.ModifyTime = time.Now() _, err = to.Update(tmpCompanyReportPermission, "EndDate", "Status", "ModifyTime") if err != nil { return } } newCompanyReportPermissionList = append(newCompanyReportPermissionList, tmpCompanyReportPermission) //isExist, tmpErr := GetCompanyPermissionCheck(companyId, v.ChartPermissionId) //if tmpErr != nil{ // err = tmpErr // return //} //if isExist > 0 { // sql = `UPDATE company_report_permission SET end_date=?,status=?,modify_time=NOW() WHERE company_id=? AND product_id=? AND chart_permission_id=?` // _, err = to.Raw(sql, newEndDate, status, companyId, productId, v.ChartPermissionId).Exec() // if err != nil { // return // } //} else { // sql = `INSERT INTO company_report_permission(company_id, report_permission_id,created_time, last_updated_time, // chart_permission_id, start_date,end_date,product_id,product_name, modify_time,status) // VALUES(?,?,NOW(),NOW(),?,?,?,?,?,NOW(),?) ` // _, err = to.Raw(sql, companyId, v.ChartPermissionId, v.ChartPermissionId, v.StartDate, newEndDate, productId, productName, status).Exec() // if err != nil { // return // } //} } return } // 原销售申请领取 func ApplyReceive(companyId, productId, sysUserId, companyApprovalId int, sellerItem *system.Admin, sysUserName, productName, sellerName string, sellerId int) (newCompanyReportPermissionList []*CompanyReportPermission, startDate, endDate string, err error) { o := orm.NewOrm() to, err := o.Begin() if err != nil { return } defer func() { if err != nil { tmpErr := to.Rollback() if tmpErr != nil { go alarm_msg.SendAlarmMsg("ApplyReceive 事务回滚失败，Err："+tmpErr.Error(), 3) } } else { err = to.Commit() } }() startDate = time.Now().Format(utils.FormatDate) endDate = time.Now().AddDate(0, 2, 0).Format(utils.FormatDate) //更新用户产品状态 sql := `UPDATE company_product SET status='试用',try_out_time=NULL,last_description_time=NULL,is_suspend=0,approve_status='已审批', start_date=?,end_date=?,seller_id=?,seller_name=?,group_id=?,department_id=?,modify_time=NOW(),try_stage=1 WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, startDate, endDate, sellerItem.AdminId, sellerItem.RealName, sellerItem.GroupId, sellerItem.DepartmentId, companyId, productId).Exec() if err != nil { return } //更新用户状态 sql = `UPDATE company SET type=2,last_updated_time=NOW() WHERE company_id=? ` _, err = to.Raw(sql, companyId).Exec() if err != nil { return } //更新审批单 sql = `UPDATE company_approval SET approve_status='已审批',approve_time=NOW(),modify_time=NOW() WHERE company_approval_id=? AND company_id=? AND product_id=? ` _, err = to.Raw(sql, companyApprovalId, companyId, productId).Exec() if err != nil { return } //查询该用户所有产品权限，并把当前数据做日志（允许添加不修改的数据） items := make([]*CompanyReportPermission, 0) sql = `SELECT * FROM company_report_permission WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, companyId, productId).QueryRows(&items) for _, pv := range items { cpLog := new(CompanyPermissionLog) cpLog.CompanyId = companyId cpLog.ChartPermissionId = pv.ChartPermissionId cpLog.CreateTime = time.Now() cpLog.SysUserId = sysUserId cpLog.SysUserName = sysUserName cpLog.StartDate = pv.StartDate cpLog.EndDate = pv.EndDate cpLog.ProductId = productId cpLog.ProductName = pv.ProductName go AddCompanyPermissionLog(cpLog) } //更新产品（2021年03月02日14:58:50注释，调整为从申请权限表中获取数据并修改） //sql = `UPDATE company_report_permission SET start_date=?,end_date=?,modify_time=NOW() WHERE company_id=? AND product_id=? ` //_, err = to.Raw(sql, startDate, endDate, companyId, productId).Exec() //if err != nil { // return //} //查询原先是否已经存在权限，如果有权限了，那么需要先删除原有的权限 count, _ := GetCompanyReportPermissionCount(companyId, productId) if count > 0 { sql := ` DELETE FROM company_report_permission WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, companyId, productId).Exec() if err != nil { return } } //修改 该联系人 与 当前销售的关系 sql = `UPDATE user_seller_relation SET seller_id=?,seller = ? ,modify_time=NOW() WHERE company_id = ? AND product_id=?` _, err = to.Raw(sql, sellerId, sellerName, companyId, productId).Exec() //获取需要试用的权限 delayPermissionItems, err := GetDelayPermissionItems(companyId, companyApprovalId) if err != nil { return } status := `试用` for _, v := range delayPermissionItems { tmpCompanyReportPermission := &CompanyReportPermission{ //CompanyReportPermissionId: 0, CompanyId: companyId, ReportPermissionId: v.ChartPermissionId, CreatedTime: time.Now(), LastUpdatedTime: time.Now(), ChartPermissionId: v.ChartPermissionId, StartDate: v.StartDate, EndDate: v.EndDate, ProductId: productId, ProductName: productName, //CompanyContractId: companyContractId, Status: status, ModifyTime: time.Now(), //IsUpgrade: pv.IsUpgrade, } newId, tmpErr := to.Insert(tmpCompanyReportPermission) if tmpErr != nil { err = tmpErr return } tmpCompanyReportPermission.CompanyReportPermissionId = newId newCompanyReportPermissionList = append(newCompanyReportPermissionList, tmpCompanyReportPermission) //sql = `INSERT INTO company_report_permission(company_id, report_permission_id,created_time, last_updated_time, // chart_permission_id, start_date,end_date,product_id,product_name, modify_time,status) // VALUES(?,?,NOW(),NOW(),?,?,?,?,?,NOW(),?) ` //_, err = to.Raw(sql, companyId, v.ChartPermissionId, v.ChartPermissionId, v.StartDate, v.EndDate, productId, productName, status).Exec() //if err != nil { // return err //} } return } // ApplyServiceUpdate 申请服务更新 func ApplyServiceUpdate(companyId, productId, sellerId, companyApprovalId, companyContractId int, startDate, endDate, sellerName, productName string, packageType, raiPackageType int) (newCompanyReportPermissionList []*CompanyReportPermission, err error) { o := orm.NewOrm() to, err := o.Begin() if err != nil { return } defer func() { if err != nil { tmpErr := to.Rollback() if tmpErr != nil { go alarm_msg.SendAlarmMsg("ApplyServiceUpdate 事务回滚失败，Err："+tmpErr.Error(), 3) } } else { err = to.Commit() } }() items := make([]*CompanyReportPermission, 0) sql := `SELECT * FROM company_report_permission WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, companyId, productId).QueryRows(&items) if err != nil { return } //已存在的权限map nowCompanyReportPermissionMap := make(map[int]*CompanyReportPermission) //产品服务的开始、结束日期（非产品权限） updateStartDate := startDate updateStartDateTime, err := time.Parse(utils.FormatDate, updateStartDate) if err != nil { return } updateEndDate := endDate updateEndDateTime, err := time.Parse(utils.FormatDate, updateEndDate) if err != nil { return } for _, pv := range items { cpLog := new(CompanyPermissionLog) cpLog.CompanyId = companyId cpLog.ChartPermissionId = pv.ChartPermissionId cpLog.CreateTime = time.Now() cpLog.SysUserId = sellerId cpLog.SysUserName = sellerName cpLog.StartDate = pv.StartDate cpLog.EndDate = pv.EndDate cpLog.ProductId = productId cpLog.ProductName = pv.ProductName go AddCompanyPermissionLog(cpLog) //将权限插入到已存在的权限map中 nowCompanyReportPermissionMap[pv.ChartPermissionId] = pv //校验原始数据中的开始日期是否小于合同内的开始日期，如果小于，那么变更为原先的合同开始日期 tmpStartDate, tmpErr := time.Parse(utils.FormatDate, pv.StartDate) if tmpErr != nil { err = tmpErr return } if tmpStartDate.Before(updateStartDateTime) { updateStartDateTime = tmpStartDate } //校验原始数据中的结束日期是否大于合同内的结束日期，如果大于，那么变更为原先的合同结束日期 tmpEndDate, tmpErr := time.Parse(utils.FormatDate, pv.EndDate) if tmpErr != nil { err = tmpErr return } if tmpEndDate.After(updateEndDateTime) { updateEndDateTime = tmpEndDate } } //删除所有权限 //sql = `DELETE FROM company_report_permission WHERE company_id=? AND product_id=? ` //_, err = to.Raw(sql, companyId, productId).Exec() //if err != nil { // return //} //更新客户产品信息 if packageType == 0 { sql = `UPDATE company_product SET status='正式',is_formal=1,is_suspend=0,approve_status='已审批',start_date=?,end_date=?,rai_package_type=?,modify_time=NOW(),try_stage=1 WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, updateStartDateTime, updateEndDateTime, raiPackageType, companyId, productId).Exec() if err != nil { return } } else { sql = `UPDATE company_product SET status='正式',is_formal=1,is_suspend=0,approve_status='已审批',start_date=?,end_date=?,package_type=?,rai_package_type=?,modify_time=NOW(),try_stage=1 WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, updateStartDateTime, updateEndDateTime, packageType, raiPackageType, companyId, productId).Exec() if err != nil { return } } sql = `UPDATE company_approval SET approve_status='已审批',approve_time=NOW(),modify_time=NOW() WHERE company_approval_id=? AND company_id=? AND product_id=? ` _, err = to.Raw(sql, companyApprovalId, companyId, productId).Exec() if err != nil { return } contractPermission := make([]*CompanyReportPermission, 0) sql = `SELECT * FROM company_contract_permission WHERE company_contract_id=? AND company_id=? ` _, err = to.Raw(sql, companyContractId, companyId).QueryRows(&contractPermission) if err != nil { return } for _, pv := range contractPermission { nowPermission, ok := nowCompanyReportPermissionMap[pv.ChartPermissionId] //判断是否已经存在该权限，如果存在的话，那么是修改，否则是新增 if ok { needUpdate := false if nowPermission.Status != "正式" && nowPermission.Status != "永续" { needUpdate = true } //如果 需要更新 字段 为false，那么再去校验时间 if needUpdate == false { //如果当前存该权限，那么去校验是否需要修改 nowPermissionEndDateTime, tmpErr := time.Parse(utils.FormatDate, nowPermission.EndDate) if tmpErr != nil { err = tmpErr return } contractPermissionEndDateTime, tmpErr := time.Parse(utils.FormatDate, pv.EndDate) if tmpErr != nil { err = tmpErr return } if contractPermissionEndDateTime.After(nowPermissionEndDateTime) { needUpdate = true } } //如果 需要更新 字段 为 true，那么就去更新 if needUpdate { nowPermission.EndDate = pv.EndDate nowPermission.LastUpdatedTime = time.Now() nowPermission.ModifyTime = time.Now() nowPermission.CompanyContractId = pv.CompanyContractId nowPermission.Status = "正式" nowPermission.IsUpgrade = pv.IsUpgrade nowPermission.ExpensiveYx = pv.ExpensiveYx _, err = to.Update(nowPermission, "EndDate", "LastUpdatedTime", "ModifyTime", "CompanyContractId", "Status", "IsUpgrade", "ExpensiveYx") if err != nil { return } //sql = `update company_report_permission set end_date=?,last_updated_time=now(),modify_time=now(),company_contract_id=?,status=? where company_report_permission_id=? ` //_, tmpErr := to.Raw(sql, pv.EndDate, pv.CompanyContractId, "正式", nowPermission.CompanyReportPermissionId).Exec() //err = tmpErr //if err != nil { // return //} } } else { nowPermission = &CompanyReportPermission{ //CompanyReportPermissionId: 0, CompanyId: companyId, ReportPermissionId: pv.ChartPermissionId, CreatedTime: time.Now(), LastUpdatedTime: time.Now(), ChartPermissionId: pv.ChartPermissionId, StartDate: pv.StartDate, EndDate: pv.EndDate, ProductId: productId, ProductName: productName, //CompanyContractId: companyContractId, Status: "正式", ModifyTime: time.Now(), IsUpgrade: pv.IsUpgrade, ExpensiveYx: pv.ExpensiveYx, } newId, tmpErr := to.Insert(nowPermission) if tmpErr != nil { err = tmpErr return } nowPermission.CompanyReportPermissionId = newId //sql = `INSERT INTO company_report_permission(company_id, report_permission_id,created_time, last_updated_time, // chart_permission_id, start_date,end_date,product_id,product_name, modify_time,company_contract_id,status) //VALUES(?,?,NOW(),NOW(),?,?,?,?,?,NOW(),?,?) ` //_, tmpErr := to.Raw(sql, companyId, pv.ChartPermissionId, pv.ChartPermissionId, pv.StartDate, pv.EndDate, productId, productName, companyContractId, "正式").Exec() //err = tmpErr //if err != nil { // return //} } newCompanyReportPermissionList = append(newCompanyReportPermissionList, nowPermission) } sql = `UPDATE company_contract SET status=1 WHERE company_contract_id=? AND company_id=? AND product_id=? ` _, err = to.Raw(sql, companyContractId, companyId, productId).Exec() if err != nil { return } return } // ApplyApproveContract 更新审批单、合同状态 func ApplyApproveContract(companyId, productId, companyApprovalId, companyContractId int) (err error) { o := orm.NewOrm() to, err := o.Begin() if err != nil { return } defer func() { if err != nil { tmpErr := to.Rollback() if tmpErr != nil { go alarm_msg.SendAlarmMsg("ApplyApproveContract 事务回滚失败，Err："+tmpErr.Error(), 3) } } else { err = to.Commit() } }() sql := `UPDATE company_product SET approve_status='已审批',renewal_intention=0,modify_time=NOW() WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, companyId, productId).Exec() if err != nil { return } //更新审批单 sql = `UPDATE company_approval SET approve_status='已审批',approve_time=NOW(),modify_time=NOW() WHERE company_approval_id=? AND company_id=? AND product_id=? ` _, err = to.Raw(sql, companyApprovalId, companyId, productId).Exec() if err != nil { return } //更新合同 sql = `UPDATE company_contract SET status=1 WHERE company_contract_id=? AND company_id=? AND product_id=? ` _, err = to.Raw(sql, companyContractId, companyId, productId).Exec() if err != nil { return } return } func GetCompanyApprovalDetailById(companyId, productId int) (item *CompanyApproval, err error) { sql := `SELECT * FROM company_approval WHERE company_id=? AND product_id=? AND approve_status IN('驳回','已撤回') ORDER BY modify_time DESC LIMIT 1 ` o := orm.NewOrm() err = o.Raw(sql, companyId, productId).QueryRow(&item) return } type CompanyApplyDeleteReq struct { CompanyId int `description:"客户id"` CompanyApprovalId int `description:"审批记录ID"` } // ApplyDelete 申请服务更新 func ApplyDelete(companyId, companyApprovalId, productId, companyContractId int) (err error) { o := orm.NewOrm() to, err := o.Begin() if err != nil { return } defer func() { if err != nil { tmpErr := to.Rollback() if tmpErr != nil { go alarm_msg.SendAlarmMsg("ApplyDelete 事务回滚失败，Err："+tmpErr.Error(), 3) } } else { err = to.Commit() } }() sql := `DELETE FROM company_approval WHERE company_approval_id=? ` _, err = to.Raw(sql, companyApprovalId).Exec() if err != nil { return } sql = `DELETE FROM company_contract WHERE company_contract_id=? ` _, err = to.Raw(sql, companyContractId).Exec() if err != nil { return } sql = `UPDATE company_product SET approve_status='' WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, companyId, productId).Exec() if err != nil { return } //作废消息 sql = `UPDATE company_approval_message SET message_status=2 WHERE source_type=1 AND company_approval_id=? ` _, err = to.Raw(sql, companyApprovalId).Exec() if err != nil { return } return } // ModifyCompanyApproval 修改申请信息 func ModifyCompanyApproval(companyId, productId, companyApprovalId int, approveContent, applyReasons string) (err error) { o := orm.NewOrm() to, err := o.Begin() if err != nil { return } defer func() { if err != nil { utils.FileLog.Info("试用延期申请：Err %s", err.Error()) tmpErr := to.Rollback() if tmpErr != nil { go alarm_msg.SendAlarmMsg("ModifyCompanyApproval 事务回滚失败，Err："+tmpErr.Error(), 3) } } else { err = to.Commit() } }() sql := `UPDATE company_approval SET approve_status = '待审批', approve_content=?,apply_reasons=?,modify_time=NOW() WHERE company_approval_id = ? ` _, err = to.Raw(sql, approveContent, applyReasons, companyApprovalId).Exec() if err != nil { return } sql = `UPDATE company_product SET approve_status='待审批' WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, companyId, productId).Exec() utils.FileLog.Info("试用延期申请：Err %s,%s,%d", sql, companyId, productId) return } // ModifyCompanyTurnPositive 修改申请信息 func ModifyCompanyTurnPositive(item *CompanyApplyTurnPositiveReq, productId, companyContractId, isFormal int, mapPermissionIdUpgrade map[int]int, expensiveYx int) (err error) { o := orm.NewOrm() to, err := o.Begin() if err != nil { return } defer func() { if err != nil { tmpErr := to.Rollback() if tmpErr != nil { go alarm_msg.SendAlarmMsg("ModifyCompanyTurnPositive 事务回滚失败，Err："+tmpErr.Error(), 3) } } else { err = to.Commit() } }() sql := `UPDATE company_contract SET start_date = ?, end_date = ?, money = ?, pay_method = ?, pay_channel = ?, img_url = ?, package_type = ?,rai_package_type = ?, modify_time = NOW() WHERE company_contract_id = ? ` // 套餐类型为0时, 不更新套餐类型 if item.PackageType == 0 { sql = `UPDATE company_contract SET start_date = ?, end_date = ?, money = ?, pay_method = ?, pay_channel = ?, img_url = ?, rai_package_type = ?, modify_time = NOW() WHERE company_contract_id = ? ` _, err = to.Raw(sql, item.StartDate, item.EndDate, item.Money, item.PayMethod, item.PayChannel, item.ImgUrl, item.RaiPackageType, companyContractId).Exec() if err != nil { return } } else { _, err = to.Raw(sql, item.StartDate, item.EndDate, item.Money, item.PayMethod, item.PayChannel, item.ImgUrl, item.PackageType, item.RaiPackageType, companyContractId).Exec() if err != nil { return } } sql = `DELETE FROM company_contract_permission WHERE company_contract_id = ? ` _, err = to.Raw(sql, companyContractId).Exec() if err != nil { return } permissionArr := strings.Split(item.PermissionIds, ",") for _, v := range permissionArr { contractPermission := new(CompanyContractPermission) contractPermission.CompanyId = item.CompanyId contractPermission.CompanyContractId = companyContractId chartPermissionId, tmpErr := strconv.Atoi(v) if tmpErr != nil { err = tmpErr return } // 买方研选5w if chartPermissionId == utils.CHART_PERMISSION_ID_YANXUAN { contractPermission.ExpensiveYx = expensiveYx } contractPermission.ChartPermissionId = chartPermissionId if _, ok := mapPermissionIdUpgrade[chartPermissionId]; ok { contractPermission.IsUpgrade = 1 } contractPermission.StartDate = item.StartDate contractPermission.EndDate = item.EndDate contractPermission.ModifyTime = time.Now() contractPermission.CreateTime = time.Now() tmpErr = AddCompanyContractPermission(contractPermission) if tmpErr != nil { err = tmpErr return } //新增日志 { itemLog := new(CompanyContractPermissionLog) itemLog.CompanyId = item.CompanyId itemLog.CompanyContractId = companyContractId itemLog.ChartPermissionId = v itemLog.StartDate = item.StartDate itemLog.EndDate = item.EndDate itemLog.ModifyTime = time.Now() itemLog.CreateTime = time.Now() itemLog.LogCreateTime = time.Now() go AddCompanyContractPermissionLog(itemLog) } } sql = `UPDATE company_approval SET approve_status = '待审批',modify_time=NOW(),is_formal=? WHERE company_approval_id = ? ` _, err = to.Raw(sql, isFormal, item.CompanyApprovalId).Exec() if err != nil { return } sql = `UPDATE company_product SET approve_status='待审批' WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, item.CompanyId, productId).Exec() return } // ModifyCompanyTurnPositiveBySystemContract 修改申请信息 func ModifyCompanyTurnPositiveBySystemContract(contractDetail *contract.ContractDetail, permissionArr []int, companyId, productId, companyApprovalId, companyContractId, isFormal, packageType, raiPackageType int) (err error) { o := orm.NewOrm() to, err := o.Begin() if err != nil { return } defer func() { if err != nil { tmpErr := to.Rollback() if tmpErr != nil { go alarm_msg.SendAlarmMsg("ModifyCompanyTurnPositiveBySystemContract 事务回滚失败，Err："+tmpErr.Error(), 3) } } else { err = to.Commit() } }() sql := `UPDATE company_contract SET start_date = ?, end_date = ?, money = ?, pay_method = ?, pay_channel = ?, img_url = ?,package_type = ?,rai_package_type = ?, modify_time = NOW() WHERE company_contract_id = ? ` _, err = to.Raw(sql, contractDetail.StartDate, contractDetail.EndDate, contractDetail.Price, contractDetail.PayRemark, contractDetail.PayChannel, contractDetail.CheckBackFileUrl, packageType, raiPackageType, companyContractId).Exec() if err != nil { return } sql = `DELETE FROM company_contract_permission WHERE company_contract_id = ? ` _, err = to.Raw(sql, companyContractId).Exec() if err != nil { return } for _, chartPermissionId := range permissionArr { contractPermission := new(CompanyContractPermission) contractPermission.CompanyId = companyId contractPermission.CompanyContractId = companyContractId contractPermission.ChartPermissionId = chartPermissionId contractPermission.StartDate = contractDetail.StartDate.Format(utils.FormatDate) contractPermission.EndDate = contractDetail.EndDate.Format(utils.FormatDate) contractPermission.ModifyTime = time.Now() contractPermission.CreateTime = time.Now() tmpErr := AddCompanyContractPermission(contractPermission) if tmpErr != nil { err = tmpErr return } //新增日志 { itemLog := new(CompanyContractPermissionLog) itemLog.CompanyId = companyId itemLog.CompanyContractId = companyContractId itemLog.ChartPermissionId = strconv.Itoa(chartPermissionId) itemLog.StartDate = contractDetail.StartDate.Format(utils.FormatDate) itemLog.EndDate = contractDetail.EndDate.Format(utils.FormatDate) itemLog.ModifyTime = time.Now() itemLog.CreateTime = time.Now() itemLog.LogCreateTime = time.Now() go AddCompanyContractPermissionLog(itemLog) } } sql = `UPDATE company_approval SET approve_status = '待审批',modify_time=NOW(),is_formal=? WHERE company_approval_id = ? ` _, err = to.Raw(sql, isFormal, companyApprovalId).Exec() if err != nil { return } sql = `UPDATE company_product SET approve_status='待审批' WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, companyId, productId).Exec() return } // ModifyCompanyApplyServiceUpdate 修改申请信息 func ModifyCompanyApplyServiceUpdate(item *CompanyApplyServiceUpdateReq, productId, companyContractId, isFormal int, mapPermissionIdUpgrade, mapPermissionIdExpensive map[int]int) (err error) { o := orm.NewOrm() to, err := o.Begin() if err != nil { return } defer func() { if err != nil { tmpErr := to.Rollback() if tmpErr != nil { go alarm_msg.SendAlarmMsg("ModifyCompanyApplyServiceUpdate 事务回滚失败，Err："+tmpErr.Error(), 3) } } else { err = to.Commit() } }() sql := `UPDATE company_contract SET start_date = ?, end_date = ?, money = ?, pay_method = ?, pay_channel = ?, img_url = ?,package_type = ?,rai_package_type = ?, modify_time = NOW() WHERE company_contract_id = ? ` _, err = to.Raw(sql, item.StartDate, item.EndDate, item.Money, item.PayMethod, item.PayChannel, item.ImgUrl, item.PackageType, item.RaiPackageType, companyContractId).Exec() if err != nil { return } sql = `DELETE FROM company_contract_permission WHERE company_contract_id = ? ` _, err = to.Raw(sql, companyContractId).Exec() if err != nil { return } permissionArr := strings.Split(item.PermissionIds, ",") for _, v := range permissionArr { contractPermission := new(CompanyContractPermission) contractPermission.CompanyId = item.CompanyId contractPermission.CompanyContractId = companyContractId chartPermissionId, tmpErr := strconv.Atoi(v) if tmpErr != nil { err = tmpErr return } contractPermission.ChartPermissionId = chartPermissionId contractPermission.StartDate = item.StartDate contractPermission.EndDate = item.EndDate contractPermission.ModifyTime = time.Now() contractPermission.CreateTime = time.Now() // 升级/买方研选5w p, _ := strconv.Atoi(v) contractPermission.IsUpgrade = mapPermissionIdUpgrade[p] contractPermission.ExpensiveYx = mapPermissionIdExpensive[p] tmpErr = AddCompanyContractPermission(contractPermission) if tmpErr != nil { err = tmpErr return } //新增日志 { itemLog := new(CompanyContractPermissionLog) itemLog.CompanyId = item.CompanyId itemLog.CompanyContractId = companyContractId itemLog.ChartPermissionId = v itemLog.StartDate = item.StartDate itemLog.EndDate = item.EndDate itemLog.ModifyTime = time.Now() itemLog.CreateTime = time.Now() itemLog.LogCreateTime = time.Now() go AddCompanyContractPermissionLog(itemLog) } } sql = `UPDATE company_approval SET approve_status = '待审批',modify_time=NOW(),is_formal=? WHERE company_approval_id = ? ` _, err = to.Raw(sql, isFormal, item.CompanyApprovalId).Exec() if err != nil { return } sql = `UPDATE company_product SET approve_status='待审批' WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, item.CompanyId, productId).Exec() return } // GetCompanyTryDelayApprovalCount 获取客户延期申请次数 func GetCompanyTryDelayApprovalCount(companyId, productId int) (count int, err error) { sql := `SELECT COUNT(1) AS count FROM company_approval WHERE company_id=? AND product_id=? AND approve_content='试用延期' ` o := orm.NewOrm() err = o.Raw(sql, companyId, productId).QueryRow(&count) return } // GetFreezeToTryCompanyApprovalCount 客户冻结转试用次数（某个销售下） func GetFreezeToTryCompanyApprovalCount(companyId, productId, applyUserId int) (count int, err error) { sql := `SELECT COUNT(1) AS count FROM company_approval WHERE company_id=? AND product_id=? AND approve_content='冻结转试用' and apply_user_id = ? ` o := orm.NewOrm() err = o.Raw(sql, companyId, productId, applyUserId).QueryRow(&count) return } // GetCompanyApprovalReportRecordGroupList 获取销售分组数据 func GetCompanyApprovalReportRecordGroupList(condition string, pars []interface{}) (list []*CompanyReportRecordGroup, err error) { o := orm.NewOrm() sql := ` SELECT a.apply_user_id as admin_id,count(DISTINCT(a.company_id)) num,GROUP_CONCAT(DISTINCT a.company_id SEPARATOR ',') AS company_ids FROM company_approval a join company_contract b on a.company_contract_id=b.company_contract_id WHERE 1=1 and a.company_contract_id !=0 ` if condition != "" { sql += condition } sql += ` GROUP BY a.apply_user_id` _, err = o.Raw(sql, pars).QueryRows(&list) return } // GetCompanyApprovalCount 客户申请的次数 func GetCompanyApprovalCount(companyId, productId, applyMethod int) (count int, err error) { sql := `SELECT COUNT(1) AS count FROM company_approval WHERE company_id=? AND product_id=? AND apply_method= ? ` o := orm.NewOrm() err = o.Raw(sql, companyId, productId, applyMethod).QueryRow(&count) return } type RenewCompanyGroup struct { SellerId int `description:"所属销售id"` //AdminName string `description:"所属销售名称"` Num int `description:"汇总次数"` CompanyIds string `description:"客户id字符串"` } // GetNotRenewCompanyGroupList 获取销售未续约数据 func GetNotRenewCompanyGroupList(productId int) (list []*RenewCompanyGroup, err error) { o := orm.NewOrm() sql := `SELECT cp.seller_id, count(DISTINCT(cp.company_id)) num, GROUP_CONCAT(DISTINCT cp.company_id SEPARATOR ',') AS company_ids FROM company_product AS cp INNER JOIN company_operation_record AS op WHERE op.company_id = cp.company_id AND cp.product_id = ? AND op.product_id = ? AND cp.STATUS IN ( "冻结", "流失" ) AND op.operation = "try_out" ` //if condition != "" { // sql += condition //} sql += ` GROUP BY cp.seller_id ` _, err = o.Raw(sql, productId, productId).QueryRows(&list) return } // GetRenewFollowCompanyGroupList 获取销售续约跟进数据 func GetRenewFollowCompanyGroupList(ids string, productId int) (list []*RenewCompanyGroup, err error) { o := orm.NewOrm() sql := `SELECT cp.seller_id, count(DISTINCT(cp.company_id)) num, GROUP_CONCAT(DISTINCT cp.company_id SEPARATOR ',') AS company_ids FROM company_product AS cp INNER JOIN company_operation_record AS op WHERE op.company_id = cp.company_id AND cp.product_id = ? AND op.product_id = ? AND cp.STATUS="试用" AND op.operation = "try_out" AND cp.company_id NOT IN (` + ids + `) ` //if condition != "" { // sql += condition //} sql += ` GROUP BY cp.seller_id ` _, err = o.Raw(sql, productId, productId).QueryRows(&list) return } type CompanyApproveCreateTime struct { CompanyId string CreateTime time.Time } // GetNotRenewFollowCompanyGroupList 获取历史经历过冻结或流失的客户 func GetNotRenewFollowCompanyGroupList(productId int) (list []*CompanyApproveCreateTime, err error) { o := orm.NewOrm() sql := ` SELECT * FROM( SELECT cp.company_id,op.create_time FROM company_product AS cp INNER JOIN company_operation_record AS op WHERE op.company_id = cp.company_id AND cp.product_id = ? AND op.product_id = ? AND op.operation IN ( "freeze", "loss" ) GROUP BY cp.company_id,op.create_time DESC )as a GROUP BY a.company_id ` //sql += ` GROUP BY cp.seller_id ` _, err = o.Raw(sql, productId, productId).QueryRows(&list) return } // GetCompanyApproveCreateTimeList 获取客户本轮合同开始时间 func GetCompanyApproveCreateTimeList(productId int) (list []*CompanyApproveCreateTime, err error) { o := orm.NewOrm() sql := `SELECT * FROM( SELECT cp.company_id,op.create_time FROM company_product AS cp INNER JOIN company_operation_record AS op WHERE op.company_id = cp.company_id AND cp.product_id = ? AND op.product_id = ? AND op.operation = "try_out" GROUP BY cp.company_id,op.create_time DESC )as a GROUP BY a.company_id ` //sql += ` GROUP BY cp.seller_id ` _, err = o.Raw(sql, productId, productId).QueryRows(&list) return } // GetExpiresCompanyGroupList 获取销售到期数据 func GetExpiresCompanyGroupList(startDate, endDate time.Time, productId int) (list []*RenewCompanyGroup, err error) { o := orm.NewOrm() sql := `SELECT seller_id, count( DISTINCT ( company_id )) num, GROUP_CONCAT( DISTINCT company_id SEPARATOR ',' ) AS company_ids FROM company_product WHERE product_id=? AND contract_end_date BETWEEN ? AND ? GROUP BY seller_id ` _, err = o.Raw(sql, productId, startDate, endDate).QueryRows(&list) return } // GetRenewCompanyGroupList 获取销售续约数据 func GetRenewCompanyGroupList(startDate, endDate time.Time, contractId string) (list []*RenewCompanyGroup, err error) { o := orm.NewOrm() sql := ` SELECT p.seller_id, count( DISTINCT ( c.company_id )) num, GROUP_CONCAT( DISTINCT c.company_id SEPARATOR ',' ) AS company_ids FROM company_product as p INNER JOIN company_contract as c WHERE c.modify_time BETWEEN ? AND ? AND c.company_product_id = p.company_product_id AND c.status = 1 AND c.company_contract_id IN (` + contractId + `) GROUP BY p.seller_id; ` _, err = o.Raw(sql, startDate, endDate).QueryRows(&list) return } func GetCompanyApprovalList(condition string, pars []interface{}) (items []*CompanyApproval, err error) { o := orm.NewOrm() sql := `SELECT * FROM company_approval WHERE 1 = 1 ` if condition != "" { sql += condition } _, err = o.Raw(sql, pars).QueryRows(&items) return }