hongze_mobile_admin/models/tables/company_approval/company_approval.go

package company_approval

import (
	"github.com/beego/beego/v2/client/orm"
	"hongze/hongze_mobile_admin/models/tables/admin"
	"hongze/hongze_mobile_admin/models/tables/company_delay_permission"
	"hongze/hongze_mobile_admin/models/tables/company_permission_log"
	"hongze/hongze_mobile_admin/models/tables/company_report_permission"
	"hongze/hongze_mobile_admin/utils"
	"time"
)

// CompanyApproval 客户单
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"`
}

// AddCompanyApproval 新增客户产品
func AddCompanyApproval(item *CompanyApproval) (lastId int, err error) {
	o := orm.NewOrm()
	tx, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = tx.Rollback()
		} else {
			_ = tx.Commit()
		}
	}()
	var newId int64
	newId, err = tx.Insert(item)
	lastId = int(newId)
	if err != nil {
		return
	}
	sql := `UPDATE company_product SET approve_status=? WHERE company_id=? AND product_id=? `
	_, err = tx.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
}

// GetCompanyApprovalByCompanyId 根据客户编号、产品编号获取客户单
func GetCompanyApprovalByCompanyId(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
}

// GetCompanyApprovalById 根据审批单id获取审批单详情
func GetCompanyApprovalById(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
}

// GetCompanyApprovalByApprovalId 根据客户单id获取客户单
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
}

// CompanyApprovalList 客户审批单列表
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:"提交申请时间"`
	ApproveUserId       int    `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:"申请人姓名"`
	ApproveRoleTypeCode string `description:"审批人角色类型" json:"-"`
	OpButton            bool   `description:"是否审批操作权限"`
	CurrNodeId          int    `description:"当前审批操作节点"`
	ApplyContractType   int    `description:"申请合同类型  0 非标  1 标准"`
	ApplyUserId         int    `description:"申请人编号id" json:"-"`
}

// GetApprovalList 获取客户单列表
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.approve_role_type_code,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
}

// GetApprovalInfoByApprovalId 根据客户单id获取客户客户审批单信息
func GetApprovalInfoByApprovalId(condition string, pars []interface{}, companyApprovalId int) (item *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.approve_role_type_code,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 AND company_approval_id=?`
	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY c.modify_time DESC `
	err = o.Raw(sql, companyApprovalId, pars).QueryRow(&item)
	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 int) (newCompanyReportPermissionList []*company_report_permission.CompanyReportPermission, err error) {
	o := orm.NewOrm()
	tx, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = tx.Rollback()
		} else {
			_ = tx.Commit()
		}
	}()
	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=?,modify_time=NOW(),formal_time=NOW(),try_stage=1 WHERE company_id=? AND product_id=? `
	_, err = tx.Raw(sql, startDate, endDate, packageType, companyId, productId).Exec()
	if err != nil {
		return
	}
	sql = `UPDATE company SET type=1,last_updated_time=NOW() WHERE company_id=? `
	_, err = tx.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 = tx.Raw(sql, companyApprovalId, companyId, productId).Exec()
	if err != nil {
		return
	}
	items := make([]*company_report_permission.CompanyReportPermission, 0)
	sql = `SELECT * FROM company_report_permission WHERE company_id=? AND product_id=? `
	_, err = tx.Raw(sql, companyId, productId).QueryRows(&items)
	for _, pv := range items {
		cpLog := new(company_permission_log.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 company_permission_log.AddCompanyPermissionLog(cpLog)
	}

	sql = `DELETE FROM company_report_permission WHERE company_id=? AND product_id=?`
	_, err = tx.Raw(sql, companyId, productId).Exec()
	if err != nil {
		return
	}

	contractPermission := make([]*company_report_permission.CompanyReportPermission, 0)
	sql = `SELECT * FROM company_contract_permission WHERE company_contract_id=? AND company_id=? `
	_, err = tx.Raw(sql, companyContractId, companyId).QueryRows(&contractPermission)
	if err != nil {
		return
	}
	for _, pv := range contractPermission {
		tmpCompanyReportPermission := &company_report_permission.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,
		}
		newId, tmpErr := tx.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 = tx.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 = tx.Raw(sql, companyContractId, companyId, productId).Exec()
	if err != nil {
		return
	}
	return
}

// FreezeToTryOut 冻结转试用
func FreezeToTryOut(companyId, productId, sellerId, companyApprovalId, applyUserId int, sellerName, productName string) (newCompanyReportPermissionList []*company_report_permission.CompanyReportPermission, startDate, endDate string, err error) {
	o := orm.NewOrm()
	tx, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = tx.Rollback()
		} else {
			_ = tx.Commit()
		}
	}()
	startDate = time.Now().Format(utils.FormatDate)
	endDate = time.Now().AddDate(0, 2, 0).Format(utils.FormatDate)

	sellerItem, err := admin.GetAdminById(applyUserId)

	if err != nil {
		return
	}

	//更新用户产品状态
	//6.9 fix 未续约强制弹窗要求客户冻结转试用后不弹床，取消清空冻结时间以标记曾经被冻结过
	sql := `UPDATE company_product SET status='试用',approve_status='已审批',freeze_start_date=null,freeze_end_date=null,
            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 = tx.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 = tx.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 = tx.Raw(sql, companyApprovalId, companyId, productId).Exec()
	if err != nil {
		return
	}

	//查询该用户所有产品权限，并把当前数据做日志（允许添加不修改的数据）
	items := make([]*company_report_permission.CompanyReportPermission, 0)
	sql = `SELECT * FROM company_report_permission WHERE company_id=? AND product_id=? `
	_, err = tx.Raw(sql, companyId, productId).QueryRows(&items)
	for _, pv := range items {
		cpLog := new(company_permission_log.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 company_permission_log.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 = tx.Raw(sql, startDate, endDate, companyId, productId).Exec()
	//if err != nil {
	//	return
	//}

	//查询原先是否已经存在权限，如果有权限了，那么需要先删除原有的权限
	count, _ := company_report_permission.GetCompanyReportPermissionCount(companyId, productId)
	if count > 0 {
		sql := ` DELETE FROM company_report_permission WHERE company_id=? AND product_id=? `
		_, err = tx.Raw(sql, companyId, productId).Exec()
		if err != nil {
			return
		}
	}
	//获取需要试用的权限
	delayPermissionItems, err := company_delay_permission.GetDelayPermissionItems(companyId, companyApprovalId)
	if err != nil {
		return
	}
	status := `试用`
	for _, v := range delayPermissionItems {
		tmpCompanyReportPermission := &company_report_permission.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 := tx.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 = tx.Raw(sql, companyId, v.ChartPermissionId, v.ChartPermissionId, v.StartDate, v.EndDate, productId, productName, status).Exec()
		//if err != nil {
		//	return err
		//}
	}
	return
}

// TryOutDelay 试用延期
func TryOutDelay(companyId, productId, sellerId, companyApprovalId int, sellerName, endDate, productName string) (newCompanyReportPermissionList []*company_report_permission.CompanyReportPermission, newEndDate string, err error) {
	o := orm.NewOrm()
	tx, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = tx.Rollback()
		} else {
			_ = tx.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='已审批',end_date=?,modify_time=NOW() WHERE company_id=? AND product_id=? `
	_, err = tx.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 = tx.Raw(sql, companyApprovalId, companyId, productId).Exec()
	if err != nil {
		return
	}

	//查询该用户所有产品权限，并把当前数据做日志（允许添加不修改的数据）
	items := make([]*company_report_permission.CompanyReportPermission, 0)
	sql = `SELECT * FROM company_report_permission WHERE company_id=? AND product_id=? `
	_, err = tx.Raw(sql, companyId, productId).QueryRows(&items)
	for _, pv := range items {
		cpLog := new(company_permission_log.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 company_permission_log.AddCompanyPermissionLog(cpLog)
	}
	//获取需要延期的权限
	delayPermissionItems, err := company_delay_permission.GetDelayPermissionItems(companyId, companyApprovalId)
	if err != nil {
		return
	}
	status := `试用`
	for _, v := range delayPermissionItems {
		tmpCompanyReportPermission, tmpErr := company_report_permission.GetCompanyPermission(companyId, v.ChartPermissionId)
		if tmpErr != nil {
			//没有数据的话
			if tmpErr.Error() == utils.ErrNoRow() {
				tmpCompanyReportPermission = &company_report_permission.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 := tx.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 = tx.Update(tmpCompanyReportPermission, "EndDate", "Status", "ModifyTime")
			if err != nil {
				return
			}
		}
		newCompanyReportPermissionList = append(newCompanyReportPermissionList, tmpCompanyReportPermission)

		//isExist, err := company_report_permission.GetCompanyPermissionCheck(companyId, v.ChartPermissionId)
		//if err != nil {
		//	return err
		//}
		//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 = tx.Raw(sql, newEndDate, status, companyId, productId, v.ChartPermissionId).Exec()
		//	if err != nil {
		//		return err
		//	}
		//} 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 = tx.Raw(sql, companyId, v.ChartPermissionId, v.ChartPermissionId, v.StartDate, newEndDate, productId, productName, status).Exec()
		//	if err != nil {
		//		return err
		//	}
		//}
	}
	return
}

// ApplyReceive 原销售申请领取
func ApplyReceive(companyId, productId, sysUserId, companyApprovalId, applyUserId int, sysUserName, productName string) (newCompanyReportPermissionList []*company_report_permission.CompanyReportPermission, startDate, endDate string, err error) {
	o := orm.NewOrm()
	tx, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = tx.Rollback()
		} else {
			_ = tx.Commit()
		}
	}()

	sellerItem, err := admin.GetAdminById(applyUserId)
	if err != nil {
		return
	}
	startDate = time.Now().Format(utils.FormatDate)
	endDate = time.Now().AddDate(0, 2, 0).Format(utils.FormatDate)

	//更新用户产品状态
	sql := `UPDATE company_product SET status='试用',approve_status='已审批',try_out_time=NULL,last_description_time=NULL, 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 = tx.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 = tx.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 = tx.Raw(sql, companyApprovalId, companyId, productId).Exec()
	if err != nil {
		return
	}
	//查询该用户所有产品权限，并把当前数据做日志（允许添加不修改的数据）
	items := make([]*company_report_permission.CompanyReportPermission, 0)
	sql = `SELECT * FROM company_report_permission WHERE company_id=? AND product_id=? `
	_, err = tx.Raw(sql, companyId, productId).QueryRows(&items)
	for _, pv := range items {
		cpLog := new(company_permission_log.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 company_permission_log.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 = tx.Raw(sql, startDate, endDate, 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 = tx.Raw(sql, sellerItem.AdminId, sellerItem.RealName, companyId, productId).Exec()

	//查询原先是否已经存在权限，如果有权限了，那么需要先删除原有的权限
	count, _ := company_report_permission.GetCompanyReportPermissionCount(companyId, productId)
	if count > 0 {
		sql := ` DELETE FROM company_report_permission WHERE company_id=? AND product_id=? `
		_, err = tx.Raw(sql, companyId, productId).Exec()
		if err != nil {
			return
		}
	}
	//获取需要试用的权限
	delayPermissionItems, err := company_delay_permission.GetDelayPermissionItems(companyId, companyApprovalId)
	if err != nil {
		return
	}
	status := `试用`
	for _, v := range delayPermissionItems {
		tmpCompanyReportPermission := &company_report_permission.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 := tx.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 = tx.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 int) (newCompanyReportPermissionList []*company_report_permission.CompanyReportPermission, err error) {
	o := orm.NewOrm()
	tx, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = tx.Rollback()
		} else {
			_ = tx.Commit()
		}
	}()

	items := make([]*company_report_permission.CompanyReportPermission, 0)

	sql := `SELECT * FROM company_report_permission WHERE company_id=? AND product_id=? `
	_, err = tx.Raw(sql, companyId, productId).QueryRows(&items)
	if err != nil {
		return
	}

	//已存在的权限map
	nowCompanyReportPermissionMap := make(map[int]*company_report_permission.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(company_permission_log.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 company_permission_log.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 = tx.Raw(sql, companyId, productId).Exec()
	//if err != nil {
	//	return
	//}

	//更新客户产品信息
	sql = `UPDATE company_product SET status='正式',is_formal=1,is_suspend=0,approve_status='已审批',start_date=?,end_date=?,package_type=?,modify_time=NOW(),try_stage=1 WHERE company_id=? AND product_id=? `
	_, err = tx.Raw(sql, updateStartDateTime, updateEndDateTime, packageType, 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 = tx.Raw(sql, companyApprovalId, companyId, productId).Exec()
	if err != nil {
		return
	}

	contractPermission := make([]*company_report_permission.CompanyReportPermission, 0)
	sql = `SELECT * FROM company_contract_permission WHERE company_contract_id=? AND company_id=? `
	_, err = tx.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
				}
				if nowPermission.IsUpgrade != pv.IsUpgrade {
					needUpdate = true
				}
			}

			//如果 需要更新 字段 为 true，那么就去更新
			if needUpdate {
				nowPermission.EndDate = pv.EndDate
				nowPermission.StartDate = pv.StartDate
				nowPermission.LastUpdatedTime = time.Now()
				nowPermission.ModifyTime = time.Now()
				nowPermission.CompanyContractId = pv.CompanyContractId
				nowPermission.Status = "正式"
				nowPermission.IsUpgrade = pv.IsUpgrade
				_, err = tx.Update(nowPermission, "EndDate","StartDate", "LastUpdatedTime", "ModifyTime", "CompanyContractId", "Status","IsUpgrade")
				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 := tx.Raw(sql, pv.EndDate, pv.CompanyContractId, "正式", nowPermission.CompanyReportPermissionId).Exec()
				//err = tmpErr
				//if err != nil {
				//	return
				//}
			}

		} else {
			nowPermission = &company_report_permission.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,
				IsUpgrade:          pv.IsUpgrade,
				//CompanyContractId:         companyContractId,
				Status:     "正式",
				ModifyTime: time.Now(),
				//IsUpgrade:                 pv.IsUpgrade,
			}
			newId, tmpErr := tx.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 := tx.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,modify_time=NOW() WHERE company_contract_id=? AND company_id=? AND product_id=? `
	_, err = tx.Raw(sql, companyContractId, companyId, productId).Exec()
	if err != nil {
		return
	}
	return

}

// ApproveRefuse 审批拒绝
func ApproveRefuse(companyId, productId int, approveStatus, approveRemark string) (err error) {
	o := orm.NewOrm()
	tx, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = tx.Rollback()
		} else {
			_ = tx.Commit()
		}
	}()
	sql := `UPDATE  company_product
			SET
			  approve_status = '驳回',
			  modify_time=NOW()
			WHERE company_id = ? AND product_id=? `
	_, err = tx.Raw(sql, companyId, productId).Exec()
	if err != nil {
		return
	}
	sql = ` UPDATE  company_approval
			SET
			  approve_status = '驳回',
			  approve_remark=?,
			  approve_time=NOW(),
			  modify_time=NOW()
			WHERE company_id = ? AND product_id=? AND approve_status='待审批' `
	_, err = tx.Raw(sql, approveRemark, companyId, productId).Exec()
	return
}

// ApplyApproveContract 更新审批单、合同状态
func ApplyApproveContract(companyId, productId, companyApprovalId, companyContractId int) (err error) {
	o := orm.NewOrm()
	tx, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = tx.Rollback()
		} else {
			_ = tx.Commit()
		}
	}()
	sql := `UPDATE company_product SET approve_status='已审批',modify_time=NOW() WHERE company_id=? AND product_id=? `
	_, err = tx.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 = tx.Raw(sql, companyApprovalId, companyId, productId).Exec()
	if err != nil {
		return
	}

	//更新合同
	sql = `UPDATE company_contract SET status=1,modify_time=NOW() WHERE company_contract_id=? AND company_id=? AND product_id=? `
	_, err = tx.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
}

// ModifyCompanyApproval 修改申请信息
func ModifyCompanyApproval(companyId, productId, companyApprovalId int, approveContent, applyReasons string) (err error) {
	o := orm.NewOrm()
	tx, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = tx.Rollback()
		} else {
			_ = tx.Commit()
		}
	}()
	sql := `UPDATE company_approval SET approve_status = '待审批', approve_content=?,apply_reasons=?,modify_time=NOW() WHERE company_approval_id = ? `
	_, err = tx.Raw(sql, approveContent, applyReasons, companyApprovalId).Exec()
	if err != nil {
		return
	}
	sql = `UPDATE company_product SET approve_status='待审批' WHERE company_id=? AND product_id=? `
	_, err = tx.Raw(sql, companyId, productId).Exec()
	utils.FileLog.Info("试用延期申请：Err %s,%s,%d", sql, companyId, productId)
	return
}

////修改申请信息
//func ModifyCompanyTurnPositive(item *CompanyApplyTurnPositiveReq, productId, companyContractId int) (err error) {
//	o := orm.NewOrm()
//	o.Begin()
//	defer func() {
//		if err != nil {
//			o.Rollback()
//		} else {
//			o.Commit()
//		}
//	}()
//	sql := `UPDATE  company_contract
//			SET
//			  start_date = ?,
//			  end_date = ?,
//			  money = ?,
//			  pay_method = ?,
//			  pay_channel = ?,
//			  img_url = ?,
//			  modify_time = NOW()
//			WHERE company_contract_id = ? `
//	_, err = o.Raw(sql, item.StartDate, item.EndDate, item.Money, item.PayMethod, item.PayChannel, item.ImgUrl, companyContractId).Exec()
//	if err != nil {
//		return
//	}
//
//	sql = `DELETE FROM company_contract_permission WHERE company_contract_id = ? `
//	_, err = o.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
//		contractPermission.ChartPermissionId = v
//		contractPermission.StartDate = item.StartDate
//		contractPermission.EndDate = item.EndDate
//		contractPermission.ModifyTime = time.Now()
//		contractPermission.CreateTime = time.Now()
//		err = AddCompanyContractPermission(contractPermission)
//		if err != nil {
//			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() WHERE company_approval_id = ? `
//	_, err = o.Raw(sql, item.CompanyApprovalId).Exec()
//	if err != nil {
//		return
//	}
//	sql = `UPDATE company_product SET approve_status='待审批' WHERE company_id=? AND product_id=? `
//	_, err = o.Raw(sql, item.CompanyId, productId).Exec()
//	return
//}
//
////修改申请信息
//func ModifyCompanyApplyServiceUpdate(item *CompanyApplyServiceUpdateReq, productId, companyContractId int) (err error) {
//	o := orm.NewOrm()
//	o.Begin()
//	defer func() {
//		if err != nil {
//			o.Rollback()
//		} else {
//			o.Commit()
//		}
//	}()
//	sql := `UPDATE  company_contract
//			SET
//			  start_date = ?,
//			  end_date = ?,
//			  money = ?,
//			  pay_method = ?,
//			  pay_channel = ?,
//			  img_url = ?,
//			  modify_time = NOW()
//			WHERE company_contract_id = ? `
//	_, err = o.Raw(sql, item.StartDate, item.EndDate, item.Money, item.PayMethod, item.PayChannel, item.ImgUrl, companyContractId).Exec()
//	if err != nil {
//		return
//	}
//
//	sql = `DELETE FROM company_contract_permission WHERE company_contract_id = ? `
//	_, err = o.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
//		contractPermission.ChartPermissionId = v
//		contractPermission.StartDate = item.StartDate
//		contractPermission.EndDate = item.EndDate
//		contractPermission.ModifyTime = time.Now()
//		contractPermission.CreateTime = time.Now()
//		err = AddCompanyContractPermission(contractPermission)
//		if err != nil {
//			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() WHERE company_approval_id = ? `
//	_, err = o.Raw(sql, item.CompanyApprovalId).Exec()
//	if err != nil {
//		return
//	}
//	sql = `UPDATE company_product SET approve_status='待审批' WHERE company_id=? AND product_id=? `
//	_, err = o.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
}

// GetApprovalListByApprovalIds 根据申请id字符串来获取申请单列表
func GetApprovalListByApprovalIds(companyApprovalIds string) (list []*CompanyApproval, err error) {
	o := orm.NewOrm()
	sql := `SELECT * from company_approval WHERE  company_approval_id in (` + companyApprovalIds + `)`
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

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

// GetCompanyNameList 获取客户名称列表数据
func GetCompanyNameListOld(userId int, userRoleTypeCode, keyword string) (list []*CompanyNameList, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.company_name 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 (c.apply_user_id=? or ( (c.approve_user_id =?  or c.approve_user_id = 0) and approve_role_type_code=?)  ) and( a.company_name like '%` + keyword + `%' or  a.credit_code like '%` + keyword + `%') and c.approve_status !="已撤回"
group by a.company_name order by c.modify_time DESC `
	_, err = o.Raw(sql, userId, userId, userRoleTypeCode).QueryRows(&list)
	return
}

// GetCompanyNameList 获取客户名称列表数据
func GetCompanyNameList(userId int, keyword string) (list []*CompanyNameList, err error) {
	o := orm.NewOrm()

	sql := `SELECT e.company_name from contract_approval a
	join  ( SELECT max( contract_approval_id ) max_id,contract_id FROM contract_approval where 1=1 and approval_type="company"  GROUP BY contract_id ) b on a.contract_approval_id=b.max_id
			INNER JOIN company_approval AS c ON a.contract_id=c.company_approval_id
			INNER JOIN company_product AS d ON d.company_id=c.company_id AND d.product_id=c.product_id
			INNER JOIN company AS e ON e.company_id=d.company_id
	JOIN contract_approval_record f on a.contract_approval_id=f.contract_approval_id 
 			WHERE 1=1 and  a.approval_type="company"  AND (c.apply_user_id = ? or (f.approve_user_id = ? and f.node_id <= a.curr_node_id) ) AND c.approve_status !="已撤回" and( e.company_name like '%` + keyword + `%' or  e.credit_code like '%` + keyword + `%')  group by company_name`
	_, err = o.Raw(sql, userId, userId).QueryRows(&list)
	return
}