hz_crm_api/models/company/company.go

package company

import (
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"github.com/rdlucklib/rdluck_tools/paging"
	"hongze/hz_crm_api/utils"
	"strconv"
	"strings"
	"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"`
	ShareSeller     string    `description:"共享销售员"`
	ShareSellerId   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:大客户"`
	IsShare         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:"地区类型，国内，国外"`
	OpenCompanyCode string    `description:"开放给第三方的客户编码，不让第三方定位我们的客户信息"`
	Nation          string    `description:"所属国家"`
	IsHide          int       `description:"是否隐藏:0:不隐藏,1:隐藏"`
	OverseasStatus  string    `description:"海外客户状态:'正式','试用','关闭'"`
	ResetBtn        int       `description:"转正式和重置按钮:同步过来默认为0:显示转正式为1:显示重置为2"`
}

// 新增客户
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 `json:"-" description:"分组id集合，包含ficc和权益的小组id"`
	DepartmentId    int    `description:"部门id"`
	IsSuspend       int    `description:"是否暂停:1:暂停,0:启用 "`
	ProductId       int    `description:"产品id"`
	ProductIds      string `json:"-";description:"分组id集合，包含ficc和权益的小组id"`
	IsMoveShow      int    `description:"按钮显示控制：1:显示，移动按钮，0：不显示移动按钮"`
	SellerId        int    `description:"销售id"`
	BtnItem         *ButtonPermission
	FormalTime      string `description:"转正时间"`
	FreezeStartDate string `description:"冻结开始日期"`
	FreezeEndDate   string `description:"冻结结束日期"`
	RegionType      string `description:"地区类型，国内，国外"`
	ShareSeller     string `description:"共享销售员"`
	ShareSellerId   int    `description:"共享销售员id"`
	IsShare         int    `description:"0:非共享用户,1:共享客户"`
	IsScrounge      int    `description:"是否白嫖 0不是 1是"`
	Nation          string `description:"所属国家"`
}

type CompanySearchListResp struct {
	Paging *paging.PagingItem
	List   []*CompanySearchItem
}

func GetCompanySearchList(condition string, pars []interface{}, limitParam ...int) (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,a.share_seller_id,a.share_seller,a.is_share,
            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,
			GROUP_CONCAT(DISTINCT b.product_id ORDER BY b.product_id ASC SEPARATOR '/' ) AS product_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.product_name 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 LIMIT ?,? `
	_, err = o.Raw(sql, pars, limitParam).QueryRows(&items)
	return
}

func GetCompanySearchCount(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
			WHERE a.enabled=1 `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY a.company_id `

	qsql := `SELECT COUNT(1) AS count FROM (` + sql + `) AS t`
	err = o.Raw(qsql, pars).QueryRow(&count)
	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:"流失原因"`
	RenewalReason    string `description:"续约说明"`
	FreezeReason     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"`
	ShareSeller      string `description:"共享销售员"`
	ShareSellerId    int    `description:"共享销售员id"`
	PackageTypes     string `description:"套餐类型集合，包含ficc和权益的套餐类型" json:"-"`
	FiccPackageType  int    `description:"ficc的套餐类型"`
	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:"客户来源"`
	Province         string `description:"省"`
	City             string `description:"市"`
	Address          string `description:"详细地址"`
	Reasons          string `description:"新增理由"`
	FreezeStartDate  string `description:"冻结开始日期"`
	FreezeEndDate    string `description:"冻结结束日期"`
	FreezeExpireDays int    `description:"冻结到期天数"`
	BtnItem          *ButtonPermission
	ProductId        int                  `json:"-"`
	ProductIds       string               `description:"产品id集合，包含ficc和权益的产品id" json:"-"`
	FormalTime       string               `description:"转正时间"`
	IsShared         bool                 `description:"是否共享客户"`
	RegionType       string               `description:"区域：国内，海外"`
	FiccLastViewTime string               `description:"ficc最近一次阅读时间"`
	RaiLastViewTime  string               `description:"权益最近一次阅读时间"`
	ViewTotals       string               `description:"阅读次数集合，包含ficc和权益的阅读次数" json:"-"`
	LastViewTimes    string               `description:"最近一次阅读时间集合，包含ficc和权益的最近一次阅读时间" json:"-"`
	FiccView         int                  `description:"Ficc报告阅读次数" json:"-"`
	RaiView          int                  `description:"权益报告阅读次数" json:"-"`
	RoadShowTotal    int                  `description:"累计路演次数"`
	TodoStatuss      string               `description:"任务状态"`
	TryStageStr      string               `description:"试用客户子标签"`
	TryStageSlice    []*TryStageSliceItem `description:"试用状态相关"`
	AllViewTotal     int                  `description:"总阅读数"`
	Deadline         string               `description:"未完成的todo任务的截止日期，截止目前还剩余的天数"`
	TodoEndTimeStr   string               `description:"未完成的todo任务的截止日期拼接格式"`
	TodoEndTime      time.Time            `description:"未完成的todo任务的截止日期"`
	TryOutDayTotals  string               `description:"试用天数集合，包含ficc和权益的试用天数" json:"-"`
	FiccTryOutDay    int                  `description:"Ficc试用天数" json:"-"`
	RaiTryOutDay     int                  `description:"权益试用天数" json:"-"`
	WeekViewActive   int                  `description:"周阅读活跃: 0-七日内无阅读; 1-活跃"`
	IsShare          int                  `description:"0:非共享用户,1:共享客户"`
	LastServiceTime  string               `description:"最后服务时间"`
	ServiceTimes     int                  `description:"服务次数"`
	CloseReason      string               `description:"关闭原因"`
	CloseTime        string               `description:"关闭时间"`
	Nation           string               `description:"所属国家"`
}

type CompanyListItem 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:"流失原因"`
	RenewalReason    string `description:"续约说明"`
	FreezeReason     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"`
	FiccPackageType  int    `description:"ficc的套餐类型"`
	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:"客户来源"`
	Province         string `description:"省"`
	City             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:"区域：国内，海外"`
	FiccView         int                  `description:"Ficc报告阅读次数"`
	RaiView          int                  `description:"权益报告阅读次数"`
	FiccLastViewTime string               `description:"ficc最近一次阅读时间"`
	RaiLastViewTime  string               `description:"权益最近一次阅读时间"`
	RoadShowTotal    int                  `description:"累计路演次数"`
	TodoStatus       bool                 `description:"是否存在进行中任务，true：存在待处理的任务"`
	CanConfirm       bool                 `description:"是否允许完成任务，true：允许完成任务"`
	HiddenConfirm    bool                 `description:"是否隐藏完成任务按钮，true：隐藏完成任务按钮"`
	HiddenCreate     bool                 `description:"是否隐藏新增/编辑按钮，true：隐藏新增/编辑按钮"`
	TodoButtonColor  string               `description:"任务按钮颜色：red，green，gray"`
	TryStageSlice    []*TryStageSliceItem `description:"试用状态相关"`
	AllViewTotal     int                  `description:"总阅读数"`
	Deadline         string               `description:"未完成的todo任务的截止日期，截止目前还剩余的天数"`
	FiccTryOutDay    int                  `description:"Ficc总试用天数"`
	RaiTryOutDay     int                  `description:"权益总试用天数"`
	WeekViewActive   int                  `description:"周阅读活跃: 0-七日内无阅读; 1-活跃"`
	IsShare          int                  `description:"0:非共享用户,1:共享客户"`
	ShareSeller      string               `description:"共享销售员"`
	ShareSellerId    int                  `description:"共享销售员id"`
	LastServiceTime  string               `description:"最后服务时间"`
	ServiceTimes     int                  `description:"服务次数"`
	CloseReason      string               `description:"关闭客户原因"`
	CloseTime        string               `description:"关闭客户时间"`
	IsScrounge       int                  `description:"是否白嫖 0不是 1是"`
}

type TryStageSliceItem struct {
	ProductId     int  `description:"产品ID"`
	TryStage      int  `description:"试用客户子标签1未分类、2  推进、3 跟踪、4 预备"`
	HasPermission bool `description:"是否有权限设置标签"`
}

type CompanyListResp struct {
	Paging        *paging.PagingItem
	List          []*CompanyListItem
	StatusCount   []*CompanyStatus
	TryStageCount []*CompanyTryStageCount
	AddCompanyBtn bool
	IsShareGroup  bool `description:"是否咨询组销售"`
	Status        int  `description:"是否咨询组销售"`
}

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()

	// 七天前的时间-用于查询周阅读活跃, 此处的需求是
	// FICC客户、共享客户: 以FICC最后一次阅读时间作为参考, 七日内有阅读量则为活跃
	// 权益客户: 以权益最后一次阅读时间作为参考
	t := time.Now().Local().AddDate(0, 0, -7).Format(utils.FormatDate)

	sql := `SELECT a.company_id,a.company_name,a.credit_code,a.company_code,a.created_time,a.province,a.city,a.address,a.region_type,b.group_id,b.road_show_total,a.is_share,
			CASE 
     WHEN GROUP_CONCAT(b.status) LIKE "%永续%" THEN 1
	 WHEN GROUP_CONCAT(b.status) LIKE "%正式%" THEN 2
	 WHEN GROUP_CONCAT(b.status) LIKE "%试用%" THEN 3
	 WHEN GROUP_CONCAT(b.status) LIKE "%冻结%" THEN 4
	 WHEN GROUP_CONCAT(b.status) LIKE "%流失%" THEN 5 
   ELSE 10 END
as sort_status,
CASE 
     WHEN GROUP_CONCAT(b.try_stage) LIKE "%2%" THEN 1
	 WHEN GROUP_CONCAT(b.try_stage) LIKE "%3%" THEN 2
	 WHEN GROUP_CONCAT(b.try_stage) LIKE "%4%" THEN 3
	 WHEN GROUP_CONCAT(b.try_stage) LIKE "%1%" THEN 4
   ELSE 10 END
as sort_try_stage,
CASE 
     WHEN GROUP_CONCAT(b.todo_status) LIKE "%未完成%" AND GROUP_CONCAT(b.todo_end_time) is not null THEN GROUP_CONCAT(b.todo_end_time)
		 ELSE "9999-01-01" END
as sort_todo_end_time,
			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.package_type ORDER BY b.product_id ASC SEPARATOR '/') AS package_types,
			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(DISTINCT b.product_id ORDER BY b.product_id ASC SEPARATOR '/') AS product_ids,
			GROUP_CONCAT(DISTINCT b.view_total ORDER BY b.product_id ASC SEPARATOR '/') AS view_totals,
			GROUP_CONCAT(DISTINCT b.last_view_time ORDER BY b.product_id ASC SEPARATOR '/') AS last_view_times,
			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.close_time ORDER BY b.product_id ASC SEPARATOR '/') AS close_time,
			GROUP_CONCAT(DISTINCT b.close_reason ORDER BY b.product_id ASC SEPARATOR '/') AS close_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(b.try_stage ORDER BY b.product_id ASC SEPARATOR '/') AS try_stage_str,
			GROUP_CONCAT(DISTINCT b.group_id ORDER BY b.product_id ASC SEPARATOR '/') AS group_ids,
			GROUP_CONCAT(DISTINCT b.todo_status ORDER BY b.product_id ASC SEPARATOR '/') AS todo_statuss,
            GROUP_CONCAT(DISTINCT b.todo_end_time ORDER BY b.product_id ASC SEPARATOR '/') AS todo_end_time_str,
			GROUP_CONCAT(DISTINCT b.try_out_day_total ORDER BY b.product_id ASC SEPARATOR '/') AS try_out_day_totals,
			b.is_suspend,
			b.freeze_start_date,
			b.freeze_end_date,
            b.seller_id,
			b.product_id,
            b.formal_time,
            b.freeze_time,b.freeze_reason,b.renewal_reason,
			b.view_total,
			b.last_view_time,
			a.share_seller,
			a.share_seller_id,
			MAX(b.last_view_time) as max_last_view_time,
			MIN(b.last_view_time) as min_last_view_time,
			MAX(b.end_date) as max_end_date,
			MIN(b.end_date) as min_end_date,
			SUM(b.view_total) as all_view_total,
			SUM(b.try_out_day_total) as all_try_out_day_total,
			IF(LEFT(GROUP_CONCAT(DISTINCT b.last_view_time ORDER BY b.product_id), 19) >= "` + t + `", 1, 0) AS week_view_active,
			a.nation
			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 { //正式
			// 正式客户默认按照周阅读活跃ASC, 同为不活跃则按照转正时间DESC
			sortStr = " ORDER BY week_view_active ASC, b.formal_time DESC"
			//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 if status == utils.COMPANY_STATUS_CLOSE { //关闭
			sortStr = " ORDER BY  b.close_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
}

// GetCountCygxArticleHistoryRecordByCompanyName 查研观向阅读次数
func GetCountCygxArticleHistoryRecordByCompanyName(companyName string) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT count(1) total FROM cygx_article_history_record WHERE company_name=? `
	_ = o.Raw(sql, companyName).QueryRow(&count)
	return
}

// GetCountUserViewHistoryByCompanyName 晨报阅读次数
func GetCountUserViewHistoryByCompanyName(companyName string) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT count(1) total FROM user_view_history WHERE company_name=? `
	_ = o.Raw(sql, companyName).QueryRow(&count)
	return
}

// GetCountReportViewRecordByCompanyName 日评阅读次数
func GetCountReportViewRecordByCompanyName(companyName string) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT count(1) total FROM report_view_record WHERE company_name=? `
	_ = o.Raw(sql, companyName).QueryRow(&count)
	return
}

// GetCountAdvisoryUserChartArticleRecordByCompanyName 每日资讯阅读次数
func GetCountAdvisoryUserChartArticleRecordByCompanyName(companyName string) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT count(1) total FROM advisory_user_chart_article_record WHERE company_name=? `
	_ = o.Raw(sql, companyName).QueryRow(&count)
	return
}

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

type CompanyTryStageCount struct {
	TryStageStr string `description:"试用客户子标签：未分类、推进、跟踪、预备"`
	TryStage    int    `description:"试用客户子标签：1未分类、2  推进、3 跟踪、4 预备"`
	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
}

// GetCompanyTryStageCount 获取试用客户标签的总数
func GetCompanyTryStageCount(condition string, pars []interface{}) (items []*CompanyTryStageCount, err error) {
	o := orm.NewOrm()
	sql := `  SELECT b.try_stage,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
	}
	//sql += `GROUP BY b.status `
	//_, err = o.Raw(sql, pars).QueryRows(&items)

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

// GetCompanyTryOutStatusCount 获取试用状态的客户数
func GetCompanyTryOutStatusCount(condition string, pars []interface{}) (item *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
	}
	//sql += `GROUP BY b.status `
	//_, err = o.Raw(sql, pars).QueryRows(&items)

	err = o.Raw(sql, pars).QueryRow(&item)
	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"`
	Scale         string `description:"管理规模，空不填，1：:50亿以下，2：50~100亿，3：100亿以上。"`
	Nation        string `description:"所属国家"`
}

// 新增客户请求参数
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
	}
	fmt.Println("num", num)
	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：国外"`
	OpenCompanyCode string `description:"开放给第三方的客户编码，不让第三方定位我们的客户信息"`
	IsShare         int    `description:"0:非共享用户,1:共享客户"`
	ShareSeller     string `description:"共享销售员"`
	ShareSellerId   int    `description:"共享销售员id"`
	IsScrounge      int    `description:"是否白嫖 0不是 1是"`
	Nation          string `description:"所属国家"`
}

type CompanyDetailResp struct {
	Item                    *CompanyDetail        `description:"客户信息"`
	FiccItem                *CompanyProductDetail `description:"Ficc客户产品详情"`
	RaiItem                 *CompanyProductDetail `description:"权益客户产品详情"`
	CreateAuth              int                   `description:"创建人：1：ficc,2:权益"`
	ProductName             string                `description:"部门名称：ficc/权益/admin"`
	IsButtonShow            int                   `description:"1:显示编辑和删除按钮，0:不显示"`
	IsContractButtonShow    bool                  `description:"true:显示联系人操作按钮，新增、编辑、导入和删除按钮，false:不显示"`
	IsUserYanXuanButtonShow bool                  `description:"true:显示联系人开通研选试用按钮，false:不显示"`
	BtnItem                 *ButtonDetailPermission
}

// 客户权限列表
type CompanyPermissionResp struct {
	FiccPermissionList []*PermissionLookList `description:"Ficc权限列表"`
	PermissionList     []*PermissionLookList `description:"权益权限列表"`
}

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
}

// GetCompanyByCreditCode 根据社会信用码获取客户信息
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
}

// GetCompanyDetailByCreditCode 根据社会信用码获取客户详情接口
func GetCompanyDetailByCreditCode(creditCode string) (companyDetail *CompanyDetail, err error) {
	o := orm.NewOrm()
	sql := ` SELECT *  FROM company AS a WHERE a.credit_code=? `
	err = o.Raw(sql, creditCode).QueryRow(&companyDetail)
	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 50`
	}
	_, 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:"市"`
	Nation      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，多个用英文逗号隔开"`
	Scale            string `description:"管理规模，空不填，1：:50亿以下，2：50~100亿，3：100亿以上。"`
}

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 = ?,
			  nation=?
			WHERE company_id =?`
	_, err = o.Raw(sql, item.CompanyName, item.CreditCode, item.Address, item.Province, item.City, item.Nation, 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()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()
	sql := `DELETE FROM company_product WHERE company_id=? `
	_, err = to.Raw(sql, companyId).Exec()
	if err != nil {
		return
	}
	sql = `DELETE FROM company_report_permission WHERE company_id=? `
	_, err = to.Raw(sql, companyId).Exec()
	if err != nil {
		return
	}

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

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

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

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

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

	sql = `DELETE FROM company WHERE company_id=? `
	_, err = to.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:"客户类型"`
}

// 修改试用客户标签
type UpdateTryStageReq struct {
	CompanyId int `description:"客户id"`
	ProductId int `description:"产品ID"`
	TryStage  int `description:"试用客户子标签：1未分类、2  推进、3 跟踪、4 预备"`
}

// MoveSeller 移动客户到另外的销售（销售领取客户时使用该接口）
func MoveSeller(companyId, productId, sellerId, groupId, departmentId int, sellerName string) (items []*CompanyReportPermission, startDateTime, endDateTime time.Time, err error) {
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	startDateTime = time.Now()
	endDateTime = time.Now().AddDate(0, 2, 0)

	startDate := startDateTime.Format(utils.FormatDate)
	endDate := endDateTime.Format(utils.FormatDate)

	//更改 客户状态为 试用
	sql := `UPDATE company SET start_date=?,end_date=?,last_updated_time=NOW() WHERE company_id=? `
	_, err = to.Raw(sql, startDate, endDate, companyId).Exec()
	if err != nil {
		return
	}

	//更改 客户产品 状态为 试用
	//重置tryout时间，上次修改未续约说明时间
	sql = `UPDATE company_product SET status='试用',try_out_time=NULL,last_description_time=NULL,is_suspend=0,start_date=?,end_date=?,seller_id=?,seller_name=?,group_id=?,department_id=?,modify_time=NOW(),try_stage=1,todo_status="无任务",todo_create_time=null,todo_approve_time=null,todo_modify_time=null,todo_end_time=null WHERE company_id=? AND product_id=? `
	_, err = to.Raw(sql, startDate, endDate, sellerId, sellerName, groupId, departmentId, 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 = `UPDATE company_report_permission SET status='试用',start_date=?,end_date=?,modify_time=NOW() WHERE company_id=? AND product_id=? `
	_, err = to.Raw(sql, startDate, endDate, companyId, productId).Exec()

	//修改 该联系人 与 当前销售的关系
	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()

	//如果是权益客户，则把对应的主观客观一起开通
	if productId == 2 {
		//获取公司现有的权限，并通过现有的权限找出对应的主观客观权限
		oldPermissionId, tmpErr := GetCompanyPermissionIdById(companyId)
		if tmpErr != nil {
			err = tmpErr
			return
		}
		newPermissionId, tmpErr := GetPermissionIdById(oldPermissionId)
		if tmpErr != nil {
			err = tmpErr
			return
		}
		permissionArr := strings.Split(newPermissionId, ",")
		for _, v := range permissionArr {
			permissionId, tmpErr := strconv.Atoi(v)
			if tmpErr != nil {
				err = tmpErr
				return
			}
			//判断这个权限是否存在，如果不存在则新增
			count, tmpErr := GetCompanyPermissionCheck(companyId, permissionId)
			if tmpErr != nil {
				err = tmpErr
				return
			}
			if count == 0 {
				permission := new(CompanyReportPermission)
				permission.CompanyId = companyId
				permission.ReportPermissionId = permissionId
				permission.CreatedTime = time.Now()
				permission.LastUpdatedTime = time.Now()
				permission.ChartPermissionId = permissionId
				permission.StartDate = startDate
				permission.EndDate = endDate
				permission.ProductId = productId
				permission.ProductName = "权益"
				permission.ModifyTime = time.Now()
				permission.Status = "试用"
				err = AddCompanyReportPermission(permission)
				if err != nil {
					return
				}
				items = append(items, permission)
			}
		}
	}

	// 将历史的任务给标记删除掉
	sql = `UPDATE company_todo SET is_delete=1,modify_time=NOW() WHERE company_id=? AND product_id=? AND is_delete = 0 `
	_, err = to.Raw(sql, companyId, productId).Exec()
	if err != nil {
		return
	}
	return
}

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 and a.enabled=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
	IsSubscribe    int `description:"是否关注了公众号(弘则研究)"`
}

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,b.subscribe AS is_subscribe
			FROM wx_user AS a
			LEFT JOIN (SELECT * FROM user_record WHERE create_platform = 1 GROUP BY user_id) AS b ON a.user_id = b.user_id
			WHERE a.company_id<>1 AND a.company_id IN(` + companyId + `)
			GROUP BY a.user_id 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
	IsSuspend    int
}

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, b.is_suspend
			 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"`
}

// MoveCompanySeller 移动客户销售
func MoveCompanySeller(companyId, productId, sellerId, groupId, departmentId int, sellerName string) (err error) {
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()
	if sellerName != "" {
		sql := `UPDATE company_product SET seller_id=?,seller_name=?,group_id=?,department_id=?,modify_time=NOW(),todo_status="无任务",todo_create_time=null,todo_approve_time=null,todo_modify_time=null,todo_end_time=null WHERE company_id=? AND product_id=? `
		_, err = to.Raw(sql, sellerId, sellerName, groupId, departmentId, companyId, productId).Exec()
		if err != nil {
			return
		}
	} else {
		sql := `UPDATE company_product SET seller_id=?,group_id=?,department_id=?,modify_time=NOW(),todo_status="无任务",todo_create_time=null,todo_approve_time=null,todo_modify_time=null,todo_end_time=null WHERE company_id=? AND product_id=? `
		_, err = to.Raw(sql, sellerId, groupId, departmentId, companyId, productId).Exec()
		if err != nil {
			return
		}
	}

	// 将历史的任务给标记删除掉
	sql := `UPDATE company_todo SET is_delete=1,modify_time=NOW() WHERE company_id=? AND product_id=? AND is_delete = 0 `
	_, err = to.Raw(sql, companyId, productId).Exec()
	if err != nil {
		return
	}
	return
}

// UpdateCompanyTryStage 更新试用客户标签
func UpdateCompanyTryStage(companyId, productId, tryStage int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE company_product SET try_stage=? WHERE company_id=? AND product_id=? and status="试用"`
	_, err = o.Raw(sql, tryStage, companyId, productId).Exec()
	return
}

func CheckCompanyProductByName(companyName string, productId int) (item *CompanyProductDetail, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM company AS a
			INNER JOIN company_product AS b ON a.company_id=b.company_id
			WHERE a.company_name=?
			AND b.product_id=? `
	err = o.Raw(sql, companyName, productId).QueryRow(&item)
	return
}

func CheckCompanyProductById(companyId, productId int) (item *CompanyProductDetail, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM company AS a
			INNER JOIN company_product AS b ON a.company_id=b.company_id
			WHERE a.company_id=?
			AND b.product_id=? `
	err = o.Raw(sql, companyId, productId).QueryRow(&item)
	return
}

func CheckCompanyProductByCreditCode(creditCode string, productId int) (item *CompanyProductDetail, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM company AS a
			INNER JOIN company_product AS b ON a.company_id=b.company_id
			WHERE a.credit_code=?
			AND b.product_id=? `
	err = o.Raw(sql, creditCode, productId).QueryRow(&item)
	return
}

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

type CheckCompanyInfoRepeatResp struct {
	Status       int    `description:"检测状态:1:跳转到领取页面，0：不跳转，继续操作"`
	RepeatStatus int    `description:"检测状态:0:无重复 1:信用码重复 2:公司名重复"`
	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不显示"`
	BtnRemarkEdit    bool `description:"编辑备注按钮:true显示，false不显示"`
	BtnRemarkView    bool `description:"查看备注按钮:true显示，false不显示"`
	BtnShare         bool `description:"设置共享按钮:true显示，false不显示"`
	BtnServiceRecord bool `description:"查看服务记录按钮:true显示，false不显示"`
	BtnClose         bool `description:"关闭客户按钮:true显示，false不显示"`
	BtnLoss          bool `description:"转流失按钮:true显示，false不显示"`
	IsResearchShow   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, freezeReason string) (err error) {
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

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

	// ficc是六个月的冻结期
	if productId == 1 {
		freezeEndDate = time.Now().AddDate(0, 6, 0).Format(utils.FormatDate)
	}

	// 权益也调整为 两个月的冻结期
	if productId == 2 {
		freezeEndDate = time.Now().AddDate(0, 2, 0).Format(utils.FormatDate)
	}

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

	//修改产品权限状态为关闭
	sql = `UPDATE company_report_permission SET status='关闭',modify_time=NOW() WHERE company_id= ? AND product_id = ? `
	_, err = to.Raw(sql, 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 = to.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
}

// CompanyTryOutReasonReq 编辑客户未续约情况说明请求参数
type CompanyTryOutReasonReq struct {
	CompanyId int    `description:"客户id"`
	ProductId int    `description:"客户产品id：ficc：1；权益：2"`
	Remark    string `description:"备注"`
}

// RenewalReasonReq 新增客户未续约情况说明请求参数
type RenewalReasonReq struct {
	CompanyId   int    `description:"客户id"`
	ProductId   int    `description:"客户产品id：ficc：1；权益：2"`
	Remark      string `description:"未续约理由"`
	Renew       int    `description:"是否勾选不再跟进 1:勾选,0:未勾选"`
	RenewalTodo string `description:"待办事项说明"`
}

// TryOutReq 新增客户未续约情况说明请求参数
type TryOutReq struct {
	CompanyId   int    `description:"客户id"`
	ProductId   int    `description:"客户产品id：ficc：1；权益：2"`
	OperationId int    `description:"operation record id"`
	Remark      string `description:"未续约理由"`
	Renew       int    `description:"是否勾选不再跟进 1:勾选,0:未勾选"`
	RenewalTodo string `description:"待办事项说明"`
}

func GetFiccReportListByMobile(mobiles string) (items []*ViewReportList, err error) {
	dataName := ""
	sql := ``
	if utils.RunMode == "debug" {
		dataName = "test_v2_hongze_rddp"
	} else {
		dataName = "hongze_rddp"
	}

	ficcSql := `SELECT
	rr.research_report_name,
		rr.type AS report_type,
		'ficc' AS txt_type,
		'--' AS match_type_name,
		'--' AS stop_time,
	uvh.created_time AS created_time
	FROM
	user_view_history uvh
	LEFT JOIN research_report rr ON rr.research_report_id = uvh.research_report_id
	WHERE
	uvh.mobile in (` + mobiles + `)
	UNION ALL
	SELECT
	r.title AS research_report_name,
		'rddp' AS report_type,
		'ficc' AS txt_type,
		r.classify_name_first AS match_type_name,
		'--' AS stop_time,
		rvr.create_time AS created_time
	FROM %s.report_view_record rvr
	LEFT JOIN %s.report r ON r.id = rvr.report_id
	WHERE
	rvr.mobile in (` + mobiles + `)
	UNION ALL
	SELECT
	cha.permission_name AS research_report_name,
		'advisory' AS report_type,
		'ficc' AS txt_type,
		cha.classify_name AS match_type_name,
		'--' AS stop_time,
		auc.create_time AS created_time
	FROM
	advisory_user_chart_article_record auc
	LEFT JOIN chart_permission cha ON cha.chart_permission_id = auc.chart_permission_id
	WHERE
	auc.mobile in (` + mobiles + `)`

	sql = ` SELECT * FROM ( ` + ficcSql + `
	      )AS t ORDER BY t.created_time DESC`

	o := orm.NewOrm()

	sql = fmt.Sprintf(sql, dataName, dataName)
	_, err = o.Raw(sql).QueryRows(&items)

	return
}

func GetRightsReportListByMobile(mobiles string) (items []*ViewReportList, err error) {
	rightsSql := `SELECT
		art.title AS research_report_name,
		'cygx' AS report_type,
		'rights' AS txt_type,
		art.match_type_name,
		h.stop_time,
		h.create_time AS created_time 
	FROM
		cygx_article_history_record_newpv h
		LEFT JOIN cygx_article art ON art.article_id = h.article_id
	WHERE
		h.mobile in (` + mobiles + `) AND h.company_id != 16 `

	sql := ` SELECT * FROM ( ` + rightsSql + `
	      )AS t ORDER BY t.created_time DESC`

	o := orm.NewOrm()
	_, err = o.Raw(sql).QueryRows(&items)

	return
}

func GetFiccReportListByEmail(emails string) (items []*ViewReportList, err error) {
	dataName := ""
	sql := ``
	if utils.RunMode == "debug" {
		dataName = "test_v2_hongze_rddp"
	} else {
		dataName = "hongze_rddp"
	}

	ficcSql := `SELECT
	rr.research_report_name,
		rr.type AS report_type,
		'ficc' AS txt_type,
		'--' AS match_type_name,
		'--' AS stop_time,
	uvh.created_time AS created_time
	FROM
	user_view_history uvh
	INNER JOIN research_report rr ON rr.research_report_id = uvh.research_report_id
	WHERE
	uvh.email in (` + emails + `) 
	UNION ALL
	SELECT
	r.title AS research_report_name,
		'rddp' AS report_type,
		'ficc' AS txt_type,
		r.classify_name_first AS match_type_name,
		'--' AS stop_time,
		rvr.create_time AS created_time
	FROM %s.report_view_record rvr
	INNER JOIN %s.report r ON r.id = rvr.report_id
	WHERE
	rvr.email in (` + emails + `) 
	UNION ALL
	SELECT
	cha.permission_name AS research_report_name,
		'advisory' AS report_type,
		'ficc' AS txt_type,
		cha.classify_name AS match_type_name,
		'--' AS stop_time,
		auc.create_time AS created_time
	FROM
	advisory_user_chart_article_record auc
	INNER JOIN chart_permission cha ON cha.chart_permission_id = auc.chart_permission_id
	WHERE
	auc.email in (` + emails + `) `

	sql = ` SELECT * FROM ( ` + ficcSql + `
	      )AS t ORDER BY t.created_time DESC`

	o := orm.NewOrm()

	sql = fmt.Sprintf(sql, dataName, dataName)
	_, err = o.Raw(sql).QueryRows(&items)

	return
}

func GetRightsReportListByEmail(emails string) (items []*ViewReportList, err error) {
	rightsSql := `SELECT
		art.title AS research_report_name,
		'cygx' AS report_type,
		'rights' AS txt_type,
		art.match_type_name,
		h.stop_time,
		h.create_time AS created_time 
	FROM
		cygx_article_history_record_newpv h
		INNER JOIN cygx_article art ON art.article_id = h.article_id
	WHERE
		h.email in (` + emails + `) AND h.company_id != 16 `

	sql := ` SELECT * FROM ( ` + rightsSql + `
	      )AS t ORDER BY t.created_time DESC`

	o := orm.NewOrm()
	_, err = o.Raw(sql).QueryRows(&items)

	return
}

// ModifyCompanyProductLastViewData 修改客户产品的总共阅读次数以及最近阅读时间
func ModifyCompanyProductLastViewData(companyId, productId, viewTotal int, lastViewTime string) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE company_product SET view_total=?,last_view_time=? WHERE company_id=? AND product_id=? `
	_, err = o.Raw(sql, viewTotal, lastViewTime, companyId, productId).Exec()
	if err != nil {
		return err
	}
	return
}

// RemarkReq 新增备注请求
type RemarkReq struct {
	CompanyId int    `description:"客户id"`
	Remark    string `description:"备注"`
}

// RemarkDelReq 删除备注请求
type RemarkDelReq struct {
	RemarkId int `description:"备注ID"`
}

// RemarkResp 备注说明
type RemarkResp struct {
	RemarkId    int    `description:"备注ID"`
	Remark      string `description:"未续约说明"`
	ProductName string `description:"客户产品：ficc：权益："`
	CreateTime  string `description:"添加时间"`
	ButtonDel   bool   `description:"删除权限"`
}

type RoadShowTotalSlice struct {
	Total     int `description:"总路演数"`
	CompanyId int `description:"用户id"`
}

// 根据用户id字符串获取企业用户的路演总次数
func GetRoadShowTotal(companyIds, date, endTime string) (items []*RoadShowTotalSlice, err error) {
	//如果companyIds是空串，那么直接返回
	if companyIds == "" {
		return
	}
	o := orm.NewOrm()
	sql := `SELECT
	COUNT(1) total,
	t.company_id 
FROM
	(
	SELECT
		a.rs_calendar_id,
		a.company_id 
	FROM
		rs_calendar AS a
		INNER JOIN rs_calendar_researcher AS b ON a.rs_calendar_id = b.rs_calendar_id 
	WHERE
		a.company_id in (` + companyIds + `) 
		AND a.activity_type = '路演' 
		AND b.status = 2 
		AND b.end_date <= ? 
		AND b.end_time < ? 
	) AS t 
GROUP BY
	company_id`
	_, err = o.Raw(sql, date, endTime).QueryRows(&items)
	return
}

type RoadShowCalendar struct {
	RsCalendarId     int    `orm:"column(rs_calendar_id);pk"`
	SysUserId        int    `description:"创建人id"`
	SysUserRealName  string `description:"创建人名称"`
	ActivityType     string `description:"活动类型"`
	RoadshowType     string `description:"路演形式"`
	RoadshowPlatform string `description:"路演平台"`
	CompanyId        int    `description:"客户id"`
	CompanyName      string `description:"客户名称"`
	Province         string `description:"省"`
	ProvinceCode     string `description:"省编码"`
	City             string `description:"市"`
	CityCode         string `description:"市编码"`
	Theme            string `description:"会议主题"`
	CooperationName  string `description:"合作方名称"`
	Title            string `description:"展示在日历的标题"`
	Source           int8   `description:"来源，0：自系统，1：上海方的"`
	CreateTime       time.Time
	ModifyTime       time.Time
	ActivityCategory string `description:"活动类别"`
	IsSynced         int    `description:"是否与上海同步 0:未同步 1:已同步"`
	EndDate          time.Time
}

func GetRoadShowList(companyId, englishCompany int) (items []*RoadShowCalendar, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	a.*,b.end_date
FROM
	rs_calendar AS a
	INNER JOIN rs_calendar_researcher AS b ON a.rs_calendar_id = b.rs_calendar_id 
WHERE
	a.source = 0 
	AND a.company_id=?
	AND b.status = 2 
    AND a.english_company=?
	AND NOW()> DATE_FORMAT( CONCAT( end_date, " ", end_time ), '%Y-%m-%d %H:%i:%S' ) 
	GROUP BY rs_calendar_id  ORDER BY create_time DESC `
	_, err = o.Raw(sql, companyId, englishCompany).QueryRows(&items)
	return
}

type RoadShowListResp struct {
	SysUserRealName  string `description:"对接销售"`
	RoadshowType     string `description:"路演形式"`
	RoadshowPlatform string `description:"路演平台：线上时为平台，线下时为省市"`
	RoadShowTime     string `description:"路演时间"`
	Researchers      string `description:"研究员"`
}

func GetRoadShowResearchers(calendarId int) (items []*string, err error) {
	o := orm.NewOrm()
	sql := `SELECT researcher_name FROM rs_calendar_researcher WHERE rs_calendar_id=? AND status=2 AND NOW()> DATE_FORMAT( CONCAT( end_date, " ", end_time ), '%Y-%m-%d %H:%i:%S' ) `
	_, err = o.Raw(sql, calendarId).QueryRows(&items)
	return
}

type CompanyNameAndId struct {
	CompanyId   int    `description:"公司id"`
	CompanyName string `description:"公司名称"`
}

type CompanyNameAndIdListResp struct {
	List []*CompanyNameAndId
}

// GetCompanyIdListByproductName 获取不同种类不同状态下的公司ID
func GetCompanyIdListByproductName(condition string, pars []interface{}) (items []*CompanyNameAndId, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	a.company_id,a.company_name
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).QueryRows(&items)
	return
}

// CompanyTryOutDayListResp 客户累计试用天数
type CompanyTryOutDayListResp struct {
	Paging *paging.PagingItem
	List   []*CompanyProductTryOutUpdateLog
}

// 共享客户
type CompanyShareReq struct {
	CompanyId int `description:"客户id"`
	IsShare   int `description:"0取消共享 1共享"`
}

func GetShareCompanyListCount(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 GetShareCompanyList(condition, sortStr string, pars []interface{}, startSize, pageSize int) (items []*CompanyItem, err error) {
	o := orm.NewOrm()

	// 七天前的时间-用于查询周阅读活跃, 此处的需求是
	// FICC客户、共享客户: 以FICC最后一次阅读时间作为参考, 七日内有阅读量则为活跃
	// 权益客户: 以权益最后一次阅读时间作为参考
	t := time.Now().Local().AddDate(0, 0, -7).Format(utils.FormatDate)

	sql := `SELECT a.company_id,a.company_name,a.credit_code,a.company_code,a.created_time,a.province,a.city,
a.address,a.region_type,b.group_id,b.road_show_total,a.is_share,a.share_seller_id,a.share_seller,
			CASE 
     WHEN GROUP_CONCAT(b.status) LIKE "%永续%" THEN 1
	 WHEN GROUP_CONCAT(b.status) LIKE "%正式%" THEN 2
	 WHEN GROUP_CONCAT(b.status) LIKE "%试用%" THEN 3
	 WHEN GROUP_CONCAT(b.status) LIKE "%冻结%" THEN 4
	 WHEN GROUP_CONCAT(b.status) LIKE "%流失%" THEN 5 
   ELSE 10 END
as sort_status,
CASE 
     WHEN GROUP_CONCAT(b.try_stage) LIKE "%2%" THEN 1
	 WHEN GROUP_CONCAT(b.try_stage) LIKE "%3%" THEN 2
	 WHEN GROUP_CONCAT(b.try_stage) LIKE "%4%" THEN 3
	 WHEN GROUP_CONCAT(b.try_stage) LIKE "%1%" THEN 4
   ELSE 10 END
as sort_try_stage,
CASE 
     WHEN GROUP_CONCAT(b.todo_status) LIKE "%未完成%" AND GROUP_CONCAT(b.todo_end_time) is not null THEN GROUP_CONCAT(b.todo_end_time)
		 ELSE "9999-01-01" END
as sort_todo_end_time,
			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.package_type ORDER BY b.product_id ASC SEPARATOR '/') AS package_types,
			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(DISTINCT b.product_id ORDER BY b.product_id ASC SEPARATOR '/') AS product_ids,
			GROUP_CONCAT(DISTINCT b.view_total ORDER BY b.product_id ASC SEPARATOR '/') AS view_totals,
			GROUP_CONCAT(DISTINCT b.last_view_time ORDER BY b.product_id ASC SEPARATOR '/') AS last_view_times,
			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(b.try_stage ORDER BY b.product_id ASC SEPARATOR '/') AS try_stage_str,
			GROUP_CONCAT(DISTINCT b.group_id ORDER BY b.product_id ASC SEPARATOR '/') AS group_ids,
			GROUP_CONCAT(DISTINCT b.todo_status ORDER BY b.product_id ASC SEPARATOR '/') AS todo_statuss,
            GROUP_CONCAT(DISTINCT b.todo_end_time ORDER BY b.product_id ASC SEPARATOR '/') AS todo_end_time_str,
			GROUP_CONCAT(DISTINCT b.try_out_day_total ORDER BY b.product_id ASC SEPARATOR '/') AS try_out_day_totals,
			b.is_suspend,
			b.freeze_start_date,
			b.freeze_end_date,
            b.seller_id,
			b.product_id,
            b.formal_time,
            b.freeze_time,b.freeze_reason,b.renewal_reason,
			b.view_total,
			b.last_view_time,
			MAX(b.last_view_time) as max_last_view_time,
			MIN(b.last_view_time) as min_last_view_time,
			MAX(b.end_date) as max_end_date,
			MIN(b.end_date) as min_end_date,
			SUM(b.view_total) as all_view_total,
			SUM(b.try_out_day_total) as all_try_out_day_total,
			IF(LEFT(GROUP_CONCAT(DISTINCT b.last_view_time ORDER BY b.product_id), 19) >= "` + t + `", 1, 0) AS week_view_active,
	  		(SELECT MAX(create_time) FROM company_service_record WHERE company_id = a.company_id) AS last_service_time,
		 	(SELECT COUNT(company_service_record_id) FROM company_service_record WHERE company_id = a.company_id) AS service_times
			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 == "" {
		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 MoveShareSellerReq struct {
	CompanyId int `description:"客户id"`
	SellsId   int `description:"销售员id"`
}

// ExportUserCompanyStatus 联系人导出客户状态
type ExportUserCompanyStatus struct {
	CompanyId     int    `description:"客户ID"`
	CompanyName   string `description:"客户名称"`
	CompanyStatus string `description:"客户状态: FICC状态/权益状态"`
}

// GetExportUserCompanyStatus 获取联系人导出客户状态
func GetExportUserCompanyStatus(condition string, pars []interface{}) (items []*ExportUserCompanyStatus, err error) {
	o := orm.NewOrm()
	sql := `SELECT
				a.company_id,
				a.company_name,
				GROUP_CONCAT(b.status SEPARATOR '/') AS company_status
			FROM
				company AS a
			INNER JOIN company_product AS b ON a.company_id = b.company_id
			WHERE 1 = 1 `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY a.company_id `
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

func GetExportUserByProductId(companyId string, productId int) (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,b.subscribe AS is_subscribe
			FROM wx_user AS a
			LEFT JOIN (SELECT * FROM user_record WHERE create_platform = 1 GROUP BY user_id) AS b ON a.user_id = b.user_id
			LEFT JOIN user_seller_relation AS c ON a.user_id = c.user_id
			WHERE a.company_id<>1 AND a.company_id IN(` + companyId + `) AND c.product_id = ?
			GROUP BY a.user_id ORDER BY a.company_id ASC `
	_, err = o.Raw(sql, productId).QueryRows(&items)
	return
}

// CloseCompanyReq 关闭客户请求体
type CloseCompanyReq struct {
	CompanyId int    `description:"客户id"`
	Remark    string `description:"备注"`
}

// CloseCompany 关闭客户
func CloseCompany(companyId, productId int, reason string) (err error) {
	o := orm.NewOrm()
	tx, e := o.Begin()
	if e != nil {
		err = fmt.Errorf("orm begin err: %s", e.Error())
		return
	}
	defer func() {
		if err != nil {
			_ = tx.Rollback()
			return
		}
		_ = tx.Commit()
	}()

	// 修改产品状态
	sql := `UPDATE company_product SET status = '关闭', is_formal = 0, close_reason = ?, close_time = NOW(), modify_time=NOW(), try_stage = 1, todo_status = "无任务", todo_create_time = null, todo_approve_time = null, todo_modify_time = null, todo_end_time = null WHERE company_id = ? AND product_id = ?`
	_, e = tx.Raw(sql, reason, companyId, productId).Exec()
	if e != nil {
		err = fmt.Errorf("update company product err: %s", e.Error())
		return
	}

	// 修改产品权限
	sql = `UPDATE company_report_permission SET status='关闭', modify_time=NOW() WHERE company_id = ? AND product_id = ?`
	_, e = tx.Raw(sql, companyId, productId).Exec()
	if e != nil {
		err = fmt.Errorf("update company report permission err: %s", e.Error())
		return
	}

	// 将历史的任务给标记删除掉
	sql = `UPDATE company_todo SET is_delete = 1, modify_time=NOW() WHERE company_id = ? AND product_id = ? AND is_delete = 0`
	_, e = tx.Raw(sql, companyId, productId).Exec()
	if e != nil {
		err = fmt.Errorf("update company todo err: %s", e.Error())
		return
	}
	return
}

// Close2LossReq 关闭转流失请求体
type Close2LossReq struct {
	CompanyId int `description:"客户id"`
}

// Close2Loss 关闭转流失
func Close2Loss(companyId, productId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE company_product SET status = '流失', is_formal=0, loss_time = NOW(), modify_time = NOW(), lose_reason = '关闭转流失', try_stage = 1, todo_status = "无任务", todo_create_time = null, todo_approve_time = null, todo_modify_time = null, todo_end_time = null, close_reason = '', close_time = null WHERE company_id = ? AND product_id = ?`
	_, err = o.Raw(sql, companyId, productId).Exec()
	return
}

// CompanyMarkReq 标记请求
type CompanyMarkReq struct {
	CompanyServiceRecordId int `description:"服务记录ID"`
	Status                 int `description:"0取消标记 1标记"`
}

// GetCompanyListByCondition 获取客户列表
func GetCompanyListByCondition(condition string, pars []interface{}, fieldArr []string, orderRule string) (items []*Company, err error) {
	o := orm.NewOrm()
	fields := strings.Join(fieldArr, ",")
	if len(fieldArr) == 0 {
		fields = `*`
	}
	order := `ORDER BY created_time DESC`
	if orderRule != "" {
		order = ` ORDER BY ` + orderRule
	}
	sql := fmt.Sprintf(`SELECT %s FROM company WHERE 1=1 %s %s`, fields, condition, order)
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}