hz_crm_api/models/statistic_report/company_area_statistics.go

package statistic_report

import (
	"github.com/beego/beego/v2/client/orm"
)

type CompanyAreaGroup struct {
	Province   string `description:"所属省份"`
	City       string `description:"所属城市"`
	AdminName  string `description:"所属销售名称"`
	Num        int    `description:"汇总次数"`
	CompanyIds string `description:"客户id字符串"`
	TryStage   int    `description:"试用客户标签：1未分类、2  推进、3 跟踪、4 预备"`
}

// GetGroupCompanyList 获取城市分组数据
func GetGroupCompanyAreaList(condition string, pars []interface{}) (list []*CompanyAreaGroup, err error) {
	o := orm.NewOrm()
	sql := ` SELECT c.province,c.city,count(1) num, GROUP_CONCAT(DISTINCT c.company_id SEPARATOR ',') AS company_ids
				FROM  company AS c 
				INNER JOIN company_product AS p 
				WHERE 1=1  
				AND c.company_id = p.company_id
				AND c.enabled = 1
				
 `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY c.city 	`
	_, err = o.Raw(sql, pars).QueryRows(&list)

	return
}

// GetTryGroupCompanyAreaList 获取城市试用分组数据
func GetTryGroupCompanyAreaList(productId int) (list []*CompanyAreaGroup, err error) {
	o := orm.NewOrm()
	sql := ` SELECT c.province,c.city,count(1) num,b.try_stage, GROUP_CONCAT(DISTINCT c.company_id SEPARATOR ',') AS company_ids
				FROM  company AS c
				left JOIN (select * from company_product where product_id=? AND status = "试用") b on b.company_id= c.company_id 
				WHERE 1=1  
    AND c.enabled = 1 and b.status = "试用" and b.product_id = ? GROUP BY c.city, b.try_stage`
	_, err = o.Raw(sql, productId, productId).QueryRows(&list)

	return
}

// CompanyViewTotalAreaSlice 获取客户的浏览次数
type CompanyViewTotalAreaSlice struct {
	CompanyIds string `description:"客户id字符串"`
	Num        int    `description:"用户浏览次数"`
	Province   string `description:"所属省份"`
	City       string `description:"所属城市"`
}


// GetCompanyViewTotalAreaList 获取客户的浏览数
func GetCompanyViewTotalAreaList(condition string, pars []interface{}, viewNum int, sumNumType string) (items []*CompanyViewTotalAreaSlice, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	count(
	DISTINCT ( company_id )) num,
	GROUP_CONCAT( DISTINCT company_id SEPARATOR ',' ) AS company_ids,
	city,
	province 
FROM
	(
	SELECT
		sum( a.view_num ) sum_num,
		a.company_id,
		c.city,
		c.province,
		b.product_id 
	FROM
		company_view_statistics a
		JOIN company c ON a.company_id = c.company_id 
		JOIN company_product b ON a.company_id = b.company_id 
WHERE
	1 =1
	AND c.enabled = 1`
	if condition != "" {
		sql += condition
	}

	childSql := ` sum_num >= `
	if sumNumType == "<" {
		childSql = ` sum_num ` + sumNumType + ` `
	}

	sql += ` GROUP BY company_id) b where 1 = 1 and ` + childSql + `? GROUP BY city`
	_, err = o.Raw(sql, pars, viewNum).QueryRows(&items)
	return
}

func GetCompanyCountGroupByCity(condition string) (list []*CompanyAreaGroup, err error) {
	o := orm.NewOrm()
	sqlCount := ` SELECT
	c.province,
	c.city,
	count(
	DISTINCT ( c.company_id )) num,
	GROUP_CONCAT( DISTINCT c.company_id SEPARATOR ',' ) AS company_ids
	FROM company_product AS p
	INNER JOIN company AS c
	WHERE 1=1 AND p.status="试用" AND p.company_id = c.company_id" ` + condition
	if condition != "" {
		sqlCount += condition
	}
	sqlCount += ` GROUP BY c.city `
	_, err = o.Raw(sqlCount).QueryRows(&list)
	return
}

func GetAllCompanyArea() (list []*CompanyAreaGroup, err error) {
	o := orm.NewOrm()
	sql := ` SELECT province,city FROM company GROUP BY city `
	_, err = o.Raw(sql).QueryRows(&list)
	return
}