hz_crm_api/models/company/company_approval.go

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)
	if productId == utils.COMPANY_PRODUCT_RAI_ID { // 权益客户试用期改为1个月。 需求池 1035
		endDate = time.Now().AddDate(0, 1, 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)
	if productId == utils.COMPANY_PRODUCT_RAI_ID { // 权益客户试用期改为1个月。 需求池 1035
		newEndDate = endDateTime.AddDate(0, 1, 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)
	if productId == utils.COMPANY_PRODUCT_RAI_ID { // 权益客户试用期改为1个月。 需求池 1035
		endDate = time.Now().AddDate(0, 1, 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
}