hongze_mobile_admin/models/tables/company/company.go

package company

import (
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"github.com/rdlucklib/rdluck_tools/paging"
	"hongze/hongze_mobile_admin/utils"
	"time"
)

type Company struct {
	CompanyId       int       `orm:"column(company_id);pk"`
	CompanyName     string    `description:"客户名称"`
	CompanyType     int       `orm:"column(type)"`
	CreditCode      string    `description:"社会统一信用码"`
	CompanyCode     string    `description:"客户编码"`
	Sort            int       `description:"优先级"`
	IsFeeCustomer   int       `description:"是否付费用户"`
	Country         string    `description:"国家编码"`
	Province        string    `description:"省"`
	City            string    `description:"市"`
	Address         string    `description:"详细地址"`
	Enabled         int       `description:"用户状态"`
	CreatedTime     time.Time `description:"创建时间"`
	LastUpdatedTime time.Time `description:"最后一次阅读时间"`
	Seller          string    `description:"销售员"`
	SellsId         int       `description:"销售员id"`
	CompanyBelong   string    `description:"客户所属，ficc：ficc客户，public_offering：公募客户，partner：合作伙伴"`
	StartDate       string    `description:"合同开始日期"`
	EndDate         string    `description:"合同结束日期"`
	LastType        int       `description:"原客户标签"`
	IsVip           int       `description:"0:普通用户,1:大客户"`
	FirstStartDate  string    `description:"首次设置为试用客户开始时间"`
	FirstEndDate    string    `description:"首次设置为试用客户结束时间"`
	DateType        int       `description:"设置流失类型，1：1个月，2：2个月，3：3个月"`
	Remark          string    `description:"备注信息"`
	RegionType      string    `description:"地区类型，国内，国外"`
}

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

//根据企业id获取企业信息
func GetCompanyById(companyId int) (items *Company, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM company where company_id= ? `
	err = o.Raw(sql, companyId).QueryRow(&items)
	return
}

type CompanySearchItem struct {
	CompanyId       int    `orm:"column(company_id);pk"`
	CompanyName     string `description:"客户名称"`
	CreditCode      string `description:"社会统一信用码"`
	CompanyCode     string `description:"客户编码"`
	StartDate       string `description:"合同开始日期"`
	EndDate         string `description:"合同结束日期"`
	LoseReason      string `description:"流失原因"`
	LossTime        string `description:"流失时间"`
	Status          string `description:"客户状态：'试用','永续','冻结','流失','正式' "`
	CompanyType     string `description:"客户类型：ficc/权益"`
	ApproveStatus   string `description:"审批状态：'审批中','通过','驳回' 审批状态为空时，表示没有审批申请"`
	SellerName      string `description:"销售：吉根龙/颖丹"`
	SellerIds       string `description:"销售ID集合，包含ficc和权益的销售id" json:"-"`
	Duration        string `description:"时长"`
	FreezeTime      string `description:"冻结时间"`
	GroupId         int    `description:"分组id"`
	GroupIds        string `description:"分组id集合，包含ficc和权益的小组id" json:"-"`
	DepartmentId    int    `description:"部门id"`
	IsSuspend       int    `description:"是否暂停:1:暂停,0:启用 "`
	ProductId       int    `description:"产品id"`
	IsMoveShow      int    `description:"按钮显示控制：1:显示，移动按钮，0：不显示移动按钮"`
	SellerId        int    `description:"销售id"`
	BtnItem         *ButtonPermission
	FormalTime      string `description:"转正时间"`
	FreezeStartDate string `description:"冻结开始日期"`
	FreezeEndDate   string `description:"冻结结束日期"`
	RegionType      string `description:"地区类型，国内，国外"`
}

type CompanySearchListResp struct {
	List []*CompanySearchItem
}

func GetCompanySearchList(condition string, pars []interface{}) (items []*CompanySearchItem, err error) {
	o := orm.NewOrm()
	sql := `SELECT DISTINCT a.company_id,a.company_name,a.credit_code,a.company_code,a.region_type,
            GROUP_CONCAT(b.status ORDER BY b.product_id ASC SEPARATOR '/') AS status,
			GROUP_CONCAT(DISTINCT b.seller_name ORDER BY b.product_id ASC SEPARATOR '/' ) AS seller_name,
			GROUP_CONCAT(DISTINCT b.seller_id ORDER BY b.product_id ASC SEPARATOR '/') AS seller_ids,
			GROUP_CONCAT(DISTINCT b.group_id ORDER BY b.product_id ASC SEPARATOR '/' ) AS group_ids,
			b.freeze_time,b.loss_time,a.lose_reason,b.is_suspend,b.seller_id,b.product_id,b.group_id,b.formal_time,b.freeze_time,
			GROUP_CONCAT(b.start_date ORDER BY b.product_id ASC SEPARATOR '/') AS start_date,
			GROUP_CONCAT(b.end_date ORDER BY b.product_id ASC SEPARATOR '/') AS end_date,
			GROUP_CONCAT(DISTINCT b.company_type ORDER BY b.product_id ASC SEPARATOR '/') AS company_type,
            GROUP_CONCAT(DISTINCT b.approve_status ORDER BY b.product_id ASC SEPARATOR '/') AS approve_status,
            GROUP_CONCAT(DISTINCT b.freeze_start_date ORDER BY b.product_id ASC SEPARATOR '/') AS freeze_start_date,
            GROUP_CONCAT(DISTINCT b.freeze_end_date ORDER BY b.product_id ASC SEPARATOR '/') AS freeze_end_date
			FROM company AS a
			INNER JOIN company_product AS b ON a.company_id=b.company_id
			WHERE a.enabled=1 `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY a.company_id ORDER BY  a.last_updated_time DESC `
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

type CompanyItem struct {
	CompanyId        int    `orm:"column(company_id);pk"`
	CompanyName      string `description:"客户名称"`
	CreditCode       string `description:"社会统一信用码"`
	CompanyCode      string `description:"客户编码"`
	StartDate        string `description:"合同开始日期"`
	EndDate          string `description:"合同结束日期"`
	LoseReason       string `description:"流失原因"`
	LossTime         string `description:"流失时间"`
	Status           string `description:"客户状态：'试用','永续','冻结','流失','正式' "`
	CompanyType      string `description:"客户类型：ficc/权益"`
	ApproveStatus    string `description:"审批状态：'待审批','已审批','驳回' 审批状态为空时，表示没有审批申请"`
	SellerName       string `description:"销售：吉根龙/颖丹"`
	SellerId         int    `description:"销售ID"`
	SellerIds        string `description:"销售ID集合，包含ficc和权益的销售id" json:"-"`
	ExpireDay        string `description:"到期天数"`
	FreezeTime       string `description:"冻结时间"`
	GroupId          int    `description:"分组id"`
	GroupIds         string `description:"分组id集合，包含ficc和权益的小组id" json:"-"`
	DepartmentId     int    `description:"部门id"`
	IndustryName     string `description:"所属行业"`
	IsSuspend        int    `description:"是否暂停:1:暂停,0:启用 "`
	CreatedTime      string `description:"创建时间"`
	Source           string `description:"客户来源"`
	Address          string `description:"详细地址"`
	Reasons          string `description:"新增理由"`
	FreezeStartDate  string `description:"冻结开始日期"`
	FreezeEndDate    string `description:"冻结结束日期"`
	FreezeExpireDays int    `description:"冻结到期天数"`
	BtnItem          *ButtonPermission
	ProductId        int    `json:"-"`
	FormalTime       string `description:"转正时间"`
	IsShared         bool   `description:"是否共享客户"`
	RegionType       string `description:"区域：国内，海外"`
}

type CompanyListResp struct {
	Paging      *paging.PagingItem
	List        []*CompanyItem
	StatusCount []*CompanyStatus
}

func GetCompanyListCount(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(DISTINCT b.company_id) AS count
			FROM company AS a
			INNER JOIN company_product AS b ON a.company_id=b.company_id
			WHERE a.enabled=1 `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

func GetCompanyList(condition, status, sortStr string, pars []interface{}, startSize, pageSize int) (items []*CompanyItem, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.company_id,a.company_name,a.credit_code,a.company_code,a.created_time,a.address,a.region_type,b.group_id,
			GROUP_CONCAT(b.status ORDER BY b.product_id ASC SEPARATOR '/') AS status,
			GROUP_CONCAT(DISTINCT b.seller_name ORDER BY b.product_id ASC SEPARATOR '/') AS seller_name,
			GROUP_CONCAT(DISTINCT b.seller_id ORDER BY b.product_id ASC SEPARATOR '/') AS seller_ids,
			GROUP_CONCAT(DISTINCT b.industry_name ORDER BY b.product_id ASC SEPARATOR '/') AS industry_name, 
			GROUP_CONCAT(DISTINCT b.product_name ORDER BY b.product_id ASC SEPARATOR '/') AS company_type,
			GROUP_CONCAT(b.start_date ORDER BY b.product_id ASC SEPARATOR '/') AS start_date,
			GROUP_CONCAT(b.end_date ORDER BY b.product_id ASC SEPARATOR '/') AS end_date,
			GROUP_CONCAT(DISTINCT b.loss_time ORDER BY b.product_id ASC SEPARATOR '/') AS loss_time,
			GROUP_CONCAT(DISTINCT b.lose_reason ORDER BY b.product_id ASC SEPARATOR '/') AS lose_reason,
			GROUP_CONCAT(DISTINCT b.source ORDER BY b.product_id ASC SEPARATOR '/') AS source,
			GROUP_CONCAT(DISTINCT b.reasons ORDER BY b.product_id ASC SEPARATOR '/') AS reasons,
			GROUP_CONCAT(DISTINCT b.approve_status ORDER BY b.product_id ASC SEPARATOR '/') AS approve_status,
			GROUP_CONCAT(DISTINCT b.group_id ORDER BY b.product_id ASC SEPARATOR '/') AS group_ids,
			b.is_suspend,
			b.freeze_start_date,
			b.freeze_end_date,
            b.seller_id,
			b.product_id,
            b.formal_time,
            b.freeze_time
			FROM company AS a
			INNER JOIN company_product AS b ON a.company_id=b.company_id
			WHERE a.enabled=1 `
	if condition != "" {
		sql += condition
	}

	//sortStr := ``
	//如果没有传入排序字段，那么按照状态排序
	if sortStr == "" {
		if status == "全部" || status == utils.COMPANY_STATUS_TRY_OUT { //试用
			sortStr = " ORDER BY  a.created_time DESC "
		} else if status == utils.COMPANY_STATUS_FORMAL { //正式
			sortStr = " ORDER BY  b.formal_time DESC "
		} else if status == utils.COMPANY_STATUS_FREEZE { //冻结
			sortStr = " ORDER BY  b.freeze_time DESC "
		} else if status == utils.COMPANY_STATUS_LOSE { //流失
			sortStr = " ORDER BY  b.loss_time DESC "
		} else {
			sortStr = " ORDER BY  a.created_time DESC "
		}
	}

	sql += ` GROUP BY a.company_id ` + sortStr + `  LIMIT ?,? `
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

type CompanyStatus struct {
	Status string `description:"客户状态：'试用','永续','冻结','流失','正式' "`
	Count  int    `description:"数量 "`
}

func GetCompanyStatusCount(condition string, pars []interface{}) (items []*CompanyStatus, err error) {
	o := orm.NewOrm()
	sql := `  SELECT b.status,COUNT(DISTINCT a.company_id) AS count FROM company AS a 
             INNER JOIN company_product AS b ON a.company_id=b.company_id 
			 WHERE a.enabled=1 AND b.status IN('正式','试用','冻结','永续') `
	if condition != "" {
		sql += condition
	}
	//sql += `GROUP BY b.status `
	//_, err = o.Raw(sql, pars).QueryRows(&items)

	sql += `GROUP BY b.status `
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

func GetCompanyStatusLoseCount(condition string, pars []interface{}) (items *CompanyStatus, err error) {
	o := orm.NewOrm()
	sql := ` SELECT b.status,COUNT(DISTINCT a.company_id) AS count FROM company AS a 
INNER JOIN company_product AS b ON a.company_id=b.company_id 
WHERE a.enabled=1 AND b.status='流失' `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars).QueryRow(&items)
	return
}

func GetCompanyStatusFreezeCount(condition string, pars []interface{}) (items *CompanyStatus, err error) {
	o := orm.NewOrm()
	sql := ` SELECT b.status,COUNT(DISTINCT a.company_id) AS count FROM company AS a 
			INNER JOIN company_product AS b ON a.company_id=b.company_id 
			WHERE a.enabled=1 AND b.status='冻结' `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars).QueryRow(&items)
	return
}

func GetCompanyStatusForverCount(condition string, pars []interface{}) (items *CompanyStatus, err error) {
	o := orm.NewOrm()
	sql := ` SELECT b.status,COUNT(DISTINCT a.company_id) AS count FROM company AS a 
			INNER JOIN company_product AS b ON a.company_id=b.company_id 
			WHERE a.enabled=1 AND b.status='永续' `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars).QueryRow(&items)
	return
}

//新增客户请求参数
type CompanyAddReq struct {
	CompanyName   string `description:"客户名称"`
	CreditCode    string `description:"社会统一信用码"`
	CompanyCode   string `description:"客户编码"`
	Province      string `description:"省"`
	City          string `description:"市"`
	Status        string `description:"客户状态，'试用','永续','冻结','流失','正式','潜在'"`
	CompanyType   string `description:"客户类型，ficc/权益"`
	IndustryId    int    `description:"所属行业id"`
	Source        string `description:"来源"`
	SellsId       int    `description:"销售员id"`
	Reasons       string `description:"新增理由，备注"`
	PermissionIds string `description:"权限id，多个用英文逗号隔开"`
	RegionType    string `description:"地区类型，国内，海外"`
	UserId        int    `description:"联系人ID"`
}

//新增客户请求参数
type CompanyAddResp struct {
	CompanyId int `description:"客户ID"`
}

func GetCompanyCode() (companyCode string, err error) {
	var num int
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) AS num FROM company WHERE created_time >=? `
	err = o.Raw(sql, time.Now().Format(utils.FormatDate)).QueryRow(&num)
	if err != nil {
		return
	}
	companyCode = "KH" + time.Now().Format("20060102") + fmt.Sprintf("%03d", num)
	return
}

type CompanyDetail struct {
	CompanyId   int    `orm:"column(company_id);pk"`
	CompanyName string `description:"客户名称"`
	CreditCode  string `description:"社会统一信用码"`
	CompanyCode string `description:"客户编码"`
	Province    string `description:"省"`
	City        string `description:"市"`
	Address     string `description:"详细地址"`
	RegionType  string `description:"地区类型，1：国内，2：国外"`
}

func GetCompanyDetailById(companyId int) (item *CompanyDetail, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM company WHERE company_id =? `
	err = o.Raw(sql, companyId).QueryRow(&item)
	return
}

func GetCompanyCountByCompanyName(companyName string) (count int, err error) {
	o := orm.NewOrm()
	sql := ` SELECT COUNT(1) AS COUNT FROM company AS a WHERE a.company_name=? `
	err = o.Raw(sql, companyName).QueryRow(&count)
	return
}

func GetCompanyCountByCreditCode(creditCode string) (count int, err error) {
	o := orm.NewOrm()
	sql := ` SELECT COUNT(1) AS COUNT FROM company AS a WHERE a.credit_code=? `
	err = o.Raw(sql, creditCode).QueryRow(&count)
	return
}

func GetCompanyCountByCompanyNameAndCompanyId(companyId int, companyName string) (count int, err error) {
	o := orm.NewOrm()
	sql := ` SELECT COUNT(1) AS COUNT FROM company AS a WHERE a.company_id<>? AND a.company_name=? `
	err = o.Raw(sql, companyId, companyName).QueryRow(&count)
	return
}

func GetCompanyCountByCreditCodeAndCompanyId(companyId int, creditCode string) (count int, err error) {
	o := orm.NewOrm()
	sql := ` SELECT COUNT(1) AS COUNT FROM company AS a WHERE a.company_id<>? AND a.credit_code=? `
	err = o.Raw(sql, companyId, creditCode).QueryRow(&count)
	return
}

//根据社会信用码获取客户信息
func GetCompanyByCreditCode(creditCode string) (companyInfo *Company, err error) {
	o := orm.NewOrm()
	sql := ` SELECT *  FROM company AS a WHERE a.credit_code=? `
	err = o.Raw(sql, creditCode).QueryRow(&companyInfo)
	return
}

func GetCompanyTryOutCount(status string, sellerId int) (count int, err error) {
	o := orm.NewOrm()
	sql := ` SELECT COUNT(DISTINCT a.company_id) AS count FROM company AS a 
			INNER JOIN company_product AS b ON a.company_id=b.company_id
			WHERE b.status=?
			AND b.seller_id=? `
	err = o.Raw(sql, status, sellerId).QueryRow(&count)
	return
}

type CompanySearchResp struct {
	List []*CompanyItem
}

func GetCompanyBySearch(keyWord string) (item []*CompanyItem, err error) {
	o := orm.NewOrm()
	var sql string
	if keyWord == "##" {
		sql = ` SELECT * FROM company ORDER BY RAND() LIMIT 5 `
	} else {
		sql = `SELECT * FROM company WHERE company_name LIKE '%` + keyWord + `%' LIMIT 5`
	}
	_, err = o.Raw(sql).QueryRows(&item)
	return
}

//编辑客户请求参数
type CompanyEditReq struct {
	CompanyId   int                      `description:"客户id"`
	CompanyName string                   `description:"客户名称"`
	CreditCode  string                   `description:"社会统一信用码"`
	Province    string                   `description:"省"`
	City        string                   `description:"市"`
	Products    []*CompanyProductEditReq `description:"产品信息"`
}

//编辑客户信息请求参数
type CompanyInfoEditReq struct {
	CompanyId    int    `description:"客户id"`
	CompanyName  string `description:"客户名称"`
	CreditCode   string `description:"社会统一信用码"`
	Province     string `description:"省"`
	City         string `description:"市"`
	IndustryId   int    `description:"所属行业id"`
	IndustryName string `description:"所属行业id"`
	Source       string `description:"来源"`
}

type CompanyProductEditReq struct {
	CompanyProductId int    `description:"客户产品id"`
	Status           string `description:"客户状态，试用,永续"`
	CompanyType      string `description:"客户类型，ficc/权益"`
	IndustryId       int    `description:"所属行业id"`
	Source           string `description:"来源"`
	SellsId          int    `description:"销售员id"`
	Reasons          string `description:"新增理由"`
	PermissionIds    string `description:"权限id，多个用英文逗号隔开"`
}

type CompanyFuzzySearch struct {
	CompanyId   int    `orm:"column(company_id);pk"`
	CompanyName string `description:"客户名称"`
	CreditCode  string `description:"社会统一信用码"`
	CompanyCode string `description:"客户编码"`
}

//新增客户请求参数
type CompanyFuzzySearchReq struct {
	List []*CompanyFuzzySearch
}

func GetCompanyFuzzySearch(keyWord string) (items []*CompanyFuzzySearch, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM company WHERE company_name LIKE '%` + keyWord + `%' `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

//修改客户
func ModifyCompany(item *Company) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE  company
			SET
			  company_name = ?,
			  credit_code = ?,
			  address = ?,
			  last_updated_time = NOW(),
			  province = ?,
			  city = ?
			WHERE company_id =?`
	_, err = o.Raw(sql, item.CompanyName, item.CreditCode, item.Address, item.Province, item.City, item.CompanyId).Exec()
	return
}

//更新客户基础信息
func (company *Company) Update(cols []string) (err error) {
	o := orm.NewOrm()
	_, err = o.Update(company, cols...)
	return
}

type DeletePptReq struct {
	CompanyId int `description:"客户id"`
}

func DeleteCompanyById(companyId 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 := `DELETE FROM company_product WHERE company_id=? `
	_, err = tx.Raw(sql, companyId).Exec()
	if err != nil {
		return
	}
	sql = `DELETE FROM company_report_permission WHERE company_id=? `
	_, err = tx.Raw(sql, companyId).Exec()
	if err != nil {
		return
	}

	//联系人表
	sql = `DELETE FROM wx_user WHERE company_id=? `
	_, err = tx.Raw(sql, companyId).Exec()
	if err != nil {
		return
	}

	//联系人 与 销售 关系表
	sql = `DELETE FROM user_seller_relation WHERE company_id=? `
	_, err = tx.Raw(sql, companyId).Exec()
	if err != nil {
		return
	}

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

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

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

	sql = `DELETE FROM company WHERE company_id=? `
	_, err = tx.Raw(sql, companyId).Exec()
	return
}

type CompanyReceiveItem struct {
	CompanyId    int    `orm:"column(company_id);pk"`
	CompanyName  string `description:"客户名称"`
	StartDate    string `description:"合同开始日期"`
	EndDate      string `description:"合同结束日期"`
	Status       string `description:"客户状态：'试用','永续','冻结','流失','正式' "`
	ProductName  string `description:"客户类型：ficc/权益"`
	SellerName   string `description:"销售：吉根龙/颖丹"`
	CreatedTime  string `description:"创建时间"`
	ExpireDay    string `description:"到期天数"`
	IndustryName string `description:"行业"`
}

type CompanyReceiveListResp struct {
	Paging *paging.PagingItem
	List   []*CompanyReceiveItem
}

func GetCompanyReceiveListCount(condition string, pars []interface{}, productId, receiveProductId int) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT 
			COUNT(DISTINCT a.company_id ) AS COUNT
			FROM company AS a
			LEFT JOIN wx_user AS b ON a.company_id=b.company_id
			LEFT JOIN company_product AS c ON a.company_id=c.company_id
			WHERE a.enabled=1
			AND c.product_id=? 
            AND c.company_id NOT IN (SELECT company_id FROM company_product WHERE product_id=?) `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, receiveProductId, productId, pars).QueryRow(&count)
	return
}

func GetCompanyReceiveList(condition string, pars []interface{}, productId, receiveProductId, startSize, pageSize int) (items []*CompanyReceiveItem, err error) {
	o := orm.NewOrm()
	sql := `SELECT 
			c.company_id,a.company_name,c.product_name,c.industry_name,c.seller_name,c.status,c.start_date,c.end_date,a.created_time
			FROM company AS a
			LEFT JOIN wx_user AS b ON a.company_id=b.company_id
			LEFT JOIN company_product AS c ON a.company_id=c.company_id
			WHERE a.enabled=1
			AND c.product_id=? 
            AND c.company_id NOT IN (SELECT company_id FROM company_product WHERE product_id=?) `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY a.company_id  ORDER BY  a.last_updated_time DESC LIMIT ?,? `
	_, err = o.Raw(sql, receiveProductId, productId, pars, startSize, pageSize).QueryRows(&items)
	return
}

//编辑客户请求参数
type CompanyReceiveReq struct {
	CompanyId     int    `description:"客户id"`
	Status        string `description:"客户状态，试用,永续"`
	IndustryId    int    `description:"所属行业id"`
	Source        string `description:"来源"`
	SellsId       int    `description:"销售员id,流失状态下直接领取，请传 0"`
	Reasons       string `description:"新增理由"`
	PermissionIds string `description:"权限id，多个用英文逗号隔开"`
	CompanyType   string `description:"客户类型"`
}

//移动销售
type MoveSellerReq struct {
	CompanyId   int    `description:"客户id"`
	SellsId     int    `description:"销售员id"`
	CompanyType string `description:"客户类型"`
}

func GetExportCompanyIds(condition string, pars []interface{}) (company_id string, err error) {
	o := orm.NewOrm()
	sql := `SELECT 
			GROUP_CONCAT(DISTINCT b.company_id SEPARATOR ',') AS company_id
			FROM company AS a
			INNER JOIN company_product AS b ON a.company_id=b.company_id
			LEFT JOIN wx_user AS c ON a.company_id=c.company_id
			WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars).QueryRow(&company_id)
	return
}

type ExportUser struct {
	RealName       string
	Mobile         string
	Email          string
	Position       string
	IsMaker        int
	DepartmentName string
	CompanyId      int
}

func GetExportUser(companyId string) (items []*ExportUser, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.real_name,a.mobile,a.email,a.position,a.is_maker,department_name,a.company_id 
			FROM wx_user AS a
			WHERE a.company_id<>1 AND a.company_id IN(` + companyId + `)
			ORDER BY a.company_id ASC `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

type ExportCompanyProduct struct {
	CompanyName  string
	ProductId    int
	ProductName  string
	IndustryName string
	SellerName   string
	Status       string
	StartDate    time.Time
	EndDate      time.Time
	CreateTime   string
	Source       string
	CreditCode   string
	RegionType   string
	LossTime     string
}

func GetExportCompanyProduct(companyId int) (items []*ExportCompanyProduct, err error) {
	o := orm.NewOrm()
	sql := ` SELECT a.company_name,b.product_id,b.product_name,b.industry_name,b.seller_name,b.status,b.start_date,b.end_date,b.create_time,
             b.source,a.credit_code,a.region_type,b.loss_time
			 FROM company AS a
			 INNER JOIN company_product AS b ON a.company_id=b.company_id
			 WHERE a.company_id=?
			 ORDER BY b.product_id ASC `
	_, err = o.Raw(sql, companyId).QueryRows(&items)
	return
}

func GetExportPermissionName(companyId, productId int) (chart_permission_name string, err error) {
	o := orm.NewOrm()
	sql := ` SELECT GROUP_CONCAT(DISTINCT  c.permission_name ORDER BY sort ASC  SEPARATOR '/') AS chart_permission_name FROM company_product AS a
			INNER JOIN company_report_permission AS b ON a.company_id=b.company_id AND a.product_id=b.product_id
			INNER JOIN chart_permission AS c ON b.chart_permission_id=c.chart_permission_id AND b.product_id=c.product_id
			WHERE a.company_id=? AND a.product_id=? `
	err = o.Raw(sql, companyId, productId).QueryRow(&chart_permission_name)
	return
}

//移动销售
type SuspendReq struct {
	CompanyId int `description:"客户id"`
}

func MoveCompanySeller(companyId, productId, sellerId, groupId, departmentId int, sellerName string) (err error) {
	o := orm.NewOrm()
	if sellerName != "" {
		sql := `UPDATE company_product SET seller_id=?,seller_name=?,group_id=?,department_id=?,modify_time=NOW() WHERE company_id=? AND product_id=? `
		_, err = o.Raw(sql, sellerId, sellerName, groupId, departmentId, companyId, productId).Exec()
	} else {
		sql := `UPDATE company_product SET seller_id=?,group_id=?,department_id=?,modify_time=NOW() WHERE company_id=? AND product_id=? `
		_, err = o.Raw(sql, sellerId, groupId, departmentId, companyId, productId).Exec()
	}
	return
}

//新增客户请求参数
type CheckCompanyInfoResp struct {
	Status      int    `description:"检测状态:1:跳转到领取页面，0：不跳转，继续操作"`
	CompanyId   int    `description:"客户id"`
	ProductName string `description:"产品名称"`
}

type ButtonPermission struct {
	BtnView         bool `description:"查看按钮权限:true显示，false不显示"`
	BtnEdit         bool `description:"编辑按钮权限:true显示，false不显示"`
	BtnDelete       bool `description:"删除按钮权限:true显示，false不显示"`
	BtnSuspend      bool `description:"暂停/启用按钮权限:true显示，false不显示"`
	BtnDelay        bool `description:"申请延期按钮权限:true显示，false不显示"`
	BtnTurnPositive bool `description:"申请转正按钮权限:true显示，false不显示"`
	BtnUpdate       bool `description:"服务更新按钮权限:true显示，false不显示"`
	BtnAddAgreement bool `description:"补充协议按钮权限:true显示，false不显示"`
	BtnThaw         bool `description:"申请解冻按钮权限:true显示，false不显示"`
	BtnApplyReceive bool `description:"申请领取按钮权限:true显示，false不显示"`
	BtnReceive      bool `description:"领取客户按钮权限:true显示，false不显示"`
	BtnModifySeller bool `description:"修改销售按钮权限：true显示，false不显示"`
	BtnReceiveOther bool `description:"领取其他部门客户按钮权限:true显示，false不显示"`
	BtnFreeze       bool `description:"冻结按钮权限:true显示，false不显示"`
	BtnTryOut       bool `description:"增开试用:true显示，false不显示"`
	BtnDetail       bool `description:"详情按钮:true显示，false不显示"`
}

type ButtonSearchPermission struct {
	BtnView         bool `description:"查看按钮权限:true显示，false不显示"`
	BtnUpdate       bool `description:"服务更新按钮权限:true显示，false不显示"`
	BtnEdit         bool `description:"编辑按钮权限:true显示，false不显示"`
	BtnDelete       bool `description:"删除按钮权限:true显示，false不显示"`
	BtnSuspend      bool `description:"暂停/启用按钮权限:true显示，false不显示"`
	BtnDelay        bool `description:"申请延期按钮权限:true显示，false不显示"`
	BtnTurnPositive bool `description:"申请转正按钮权限:true显示，false不显示"`
	BtnThaw         bool `description:"申请解冻按钮权限:true显示，false不显示"`
	BtnApplyReceive bool `description:"申请领取按钮权限:true显示，false不显示"`
	BtnReceive      bool `description:"领取客户按钮权限:true显示，false不显示"`
	BtnModifySeller bool `description:"修改销售按钮权限：true显示，false不显示"`
	BtnReceiveOther bool `description:"领取其他部门客户按钮权限:true显示，false不显示"`
}

type ButtonDetailPermission struct {
	BtnEdit        bool `description:"编辑按钮权限:true显示，false不显示"`
	BtnDelete      bool `description:"删除按钮权限:true显示，false不显示"`
	BtnHistoryList bool `description:"历史签约按钮权限:true显示，false不显示"`

	BaseInfoEdit bool `description:"客户基础信息编辑操作:true可操作，false不可操作"`
	FiccEdit     bool `description:"ficc类型，编辑按钮权限:true显示，false不显示"`
	FiccDelete   bool `description:"ficc类型，删除按钮权限:true显示，false不显示"`
	RaiEdit      bool `description:"权益类型，编辑按钮权限:true显示，false不显示"`
	RaiDelete    bool `description:"权益类型，删除按钮权限:true显示，false不显示"`
}

func GetCompanyListExport(condition string, pars []interface{}) (items []*CompanyItem, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.company_id,a.company_name,a.credit_code,a.company_code,a.created_time,a.address,
			GROUP_CONCAT(b.status ORDER BY b.product_id ASC SEPARATOR '/') AS status,
			GROUP_CONCAT(DISTINCT b.seller_name ORDER BY b.product_id ASC SEPARATOR '/') AS seller_name,
			GROUP_CONCAT(DISTINCT b.industry_name ORDER BY b.product_id ASC SEPARATOR '/') AS industry_name, 
			GROUP_CONCAT(DISTINCT b.product_name ORDER BY b.product_id ASC SEPARATOR '/') AS company_type,
			GROUP_CONCAT(b.start_date ORDER BY b.product_id ASC SEPARATOR '/') AS start_date,
			GROUP_CONCAT(b.end_date ORDER BY b.product_id ASC SEPARATOR '/') AS end_date,
			GROUP_CONCAT(DISTINCT b.loss_time ORDER BY b.product_id ASC SEPARATOR '/') AS loss_time,
			GROUP_CONCAT(DISTINCT b.lose_reason ORDER BY b.product_id ASC SEPARATOR '/') AS lose_reason,
			GROUP_CONCAT(DISTINCT b.source ORDER BY b.product_id ASC SEPARATOR '/') AS source,
			GROUP_CONCAT(DISTINCT b.reasons ORDER BY b.product_id ASC SEPARATOR '/') AS reasons,
			GROUP_CONCAT(DISTINCT b.approve_status ORDER BY b.product_id ASC SEPARATOR '/') AS approve_status,
			GROUP_CONCAT(DISTINCT b.group_id ORDER BY b.product_id ASC SEPARATOR '/') AS group_id,
			b.is_suspend,
			b.freeze_start_date,
			b.freeze_end_date,
            b.seller_id,
			b.product_id
			FROM company AS a
			INNER JOIN company_product AS b ON a.company_id=b.company_id
			WHERE a.enabled=1 AND a.company_id<>1 `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY a.company_id  ORDER BY  a.last_updated_time DESC `
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

//冻结客户请求参数
type CompanyFreezeReq struct {
	CompanyId   int    `description:"客户id"`
	CompanyType string `description:"客户类型：ficc/权益"`
	Remark      string `description:"备注"`
}

//冻结客户
func CompanyFreeze(companyId, productId int) (err error) {
	o := orm.NewOrm()
	tx, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = tx.Rollback()
		} else {
			_ = tx.Commit()
		}
	}()

	freezeStartDate := time.Now().Format(utils.FormatDate)
	freezeEndDate := time.Now().AddDate(0, 3, 0).Format(utils.FormatDate)

	//修改用户产品状态
	sql := `UPDATE company_product SET status='冻结',freeze_time=NOW(),modify_time=NOW(),start_date=?,end_date=?,freeze_start_date=?,freeze_end_date=?,try_stage=1 WHERE company_id=? AND product_id=? `
	_, err = tx.Raw(sql, freezeStartDate, freezeEndDate, freezeStartDate, freezeEndDate, companyId, productId).Exec()
	if err != nil {
		return err
	}

	//修改产品权限状态为关闭
	sql = `UPDATE company_report_permission SET status='关闭',start_date=?,end_date=?,modify_time=NOW() WHERE company_id= ? AND product_id = ? `
	_, err = tx.Raw(sql, freezeStartDate, freezeEndDate, companyId, productId).Exec()
	if err != nil {
		return err
	}

	//修改客户为关闭
	sql = `UPDATE company SET type=3,last_updated_time=NOW(),start_date=?,end_date=? WHERE company_id=? `
	_, err = tx.Raw(sql, freezeStartDate, freezeEndDate, companyId).Exec()
	if err != nil {
		return err
	}
	return
}

func ModifyCompanyGroupId(sellerId, groupId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE company_product SET group_id=? WHERE seller_id=? `
	_, err = o.Raw(sql, groupId, sellerId).Exec()
	return
}

func ModifyCompanyDepartmentId(sellerId, departmentId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE company_product SET department_id=? WHERE seller_id=? `
	_, err = o.Raw(sql, departmentId, sellerId).Exec()
	return
}

type CompanyNameList struct {
	CompanyId   int    `description:"客户ID"`
	CompanyName string `description:"客户名称"`
	CreditCode  string `description:"社会统一信用码"`
}

// GetCompanyNameList 获取客户名称列表
func GetCompanyNameList(condition string, pars []interface{}, startSize, pageSize int) (list []*CompanyNameList, err error) {
	o := orm.NewOrm()
	sql := ` SELECT company_id,company_name,credit_code FROM company WHERE 1=1 `
	sql += condition
	sql += ` LIMIT ?,? `
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
	return
}