hz_crm_api/models/cygx/cygx_user_company.go

package cygx

import (
	"github.com/beego/beego/v2/client/orm"
	"hongze/hz_crm_api/models/company"
	"strconv"
)

// 用户互动总数统计
func GetUserInteractionTableCountByCompany(companyId int) (item *GetUserInteractionTableCountResp, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			( SELECT COUNT( 1 ) FROM cygx_article_history_record_all AS h  INNER JOIN cygx_article  as art  ON  art.article_id = h.article_id  WHERE h.company_id = u.company_id AND h.is_del = 0   AND h.create_time < date_format(now(),'%Y-%m-%d') ) AS history_num,
			( SELECT COUNT( 1 ) FROM cygx_article_collect AS h WHERE h.company_id = u.company_id    AND h.create_time < date_format(now(),'%Y-%m-%d') ) AS count_num,
			( SELECT COUNT( 1 ) FROM cygx_chart_collect   AS h  INNER JOIN cygx_chart AS a ON h.chart_id = a.chart_id  WHERE h.company_id = u.company_id   AND h.create_time < date_format(now(),'%Y-%m-%d')  ) AS chart_num,
			( SELECT COUNT( 1 ) FROM cygx_industry_fllow AS h WHERE h.company_id = u.company_id AND h.type = 1   AND h.create_time < date_format(now(),'%Y-%m-%d')  ) AS industry_fllow_num,
			( SELECT COUNT( 1 ) FROM cygx_article_department_follow AS h WHERE h.company_id = u.company_id  AND h.type = 1   AND h.create_time < date_format(now(),'%Y-%m-%d')  ) AS department_follow_num,
			( SELECT COUNT( 1 ) FROM cygx_search_key_word AS h WHERE h.company_id = u.company_id   AND h.create_time < date_format(now(),'%Y-%m-%d')  ) AS key_word_num,
			( SELECT 	COUNT(1 )  as count  FROM cygx_activity_signup AS h INNER JOIN cygx_activity as a ON a.activity_id = h.activity_id  INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id  INNER JOIN wx_user as wu  ON wu.user_id = h.user_id  WHERE h.company_id = u.company_id  
					  AND t.activity_type=1 AND h.do_fail_type = 0   AND h.create_time < date_format(now(),'%Y-%m-%d') ) AS on_line_num,
			( SELECT  	COUNT( 1 )  as count  FROM cygx_activity_signup AS h INNER JOIN cygx_activity as a ON a.activity_id = h.activity_id  INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id  INNER JOIN wx_user as wu  ON wu.user_id = h.user_id WHERE h.company_id = u.company_id  
					 AND t.activity_type = 0 AND h.do_fail_type = 0 AND h.create_time < date_format(now(),'%Y-%m-%d')  ) AS office_num
		FROM
			company  as u
		WHERE
			u.company_id = ?`
	err = o.Raw(sql, companyId).QueryRow(&item)
	return
}

// 获取阅读记录数量
func GetCygxArticleHistoryCountByCompany(condition string) (count int, err error) {
	o := orm.NewOrm()
	sqlCount := ` SELECT COUNT( 1 ) AS count 
FROM
	(
	SELECT
		COUNT( 1 ) 
	FROM
		cygx_article_history_record_all AS r
		INNER JOIN cygx_article AS art ON art.article_id = r.article_id
		INNER JOIN company_product AS cp ON cp.company_id = r.company_id 
		AND cp.product_id = 2
		INNER JOIN cygx_company_interaction_num AS ci ON ci.company_id = r.company_id
		INNER JOIN cygx_user_interaction_num AS ui ON ui.user_id = r.user_id
		LEFT  JOIN cygx_report_mapping AS re ON re.category_id = art.category_id_two   WHERE   r.is_del = 0`
	if condition != "" {
		sqlCount += condition
	}
	sqlCount += `	GROUP BY r.id ) AS count `
	err = o.Raw(sqlCount).QueryRow(&count)
	return
}

// 阅读记录列表
func GetCygxArticleHistoryRecordByCompany(condition string, startSize, pageSize int) (items []*UserInteraction, err error) {
	o := orm.NewOrm()
	sql := ` SELECT
			art.title,
			art.article_id,
			art.article_id_md5,
			art.publish_date,
			art.category_name,
			re.chart_permission_name,
			re.chart_permission_id,
			r.create_time,
			r.mobile,
			r.user_id,
			r.real_name,
			r.source as source_platform,
			r.stop_time
		FROM
			cygx_article_history_record_all AS r
			INNER JOIN cygx_article AS art ON art.article_id = r.article_id
			INNER JOIN cygx_user_interaction_num AS ui ON ui.user_id = r.user_id
			LEFT JOIN cygx_report_mapping AS re ON re.category_id = art.category_id_two
			LEFT JOIN cygx_industrial_article_group_management AS man ON man.article_id = art.article_id
		WHERE
			 1=1  AND r.is_del = 0  ` + condition + `  GROUP BY r.id  ORDER BY ui.article_history_num DESC ,  r.create_time DESC   `
	if startSize > 0 || pageSize > 0 {
		sql += ` LIMIT ` + strconv.Itoa(startSize) + "," + strconv.Itoa(pageSize)
	}
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 获取用户参会记录数量
func GetActivityMeetByCompanyCount(condition string) (count int, err error) {
	o := orm.NewOrm()
	sqlCount := `SELECT
				COUNT(*) AS count 
			FROM
				cygx_activity_signup_detail AS r
				INNER JOIN cygx_activity AS a ON a.activity_id = r.activity_id
				INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id
				INNER JOIN wx_user AS u ON u.mobile = r.mobile 
			WHERE
				1 = 1 
				AND r.do_fail_type = 0`
	if condition != "" {
		sqlCount += condition
	}
	err = o.Raw(sqlCount).QueryRow(&count)
	return
}

// 获取用户参会记录
func GetActivityMeetByCompany(condition string, startSize, pageSize int) (item []*UserInteraction, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			t.activity_type,
			r.signup_type,
			r.is_meeting,
			u.mobile,
			u.real_name,
			a.activity_name,
			a.chart_permission_name,
			a.activity_type_name,
			a.label,
			a.activity_time,
			r.first_meeting_time,
			r.last_meeting_time,
			r.meeting_type_str,
			r.meeting_authentication,
			r.meeting_status_str,
			r.duration
		FROM
			cygx_activity_signup_detail AS r
			INNER  JOIN cygx_activity AS a ON a.activity_id = r.activity_id
			INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id
			INNER JOIN wx_user as u  ON u.mobile = r.mobile
			LEFT JOIN cygx_user_interaction_num AS ui ON ui.user_id = r.user_id
		WHERE
			1 = 1 
			AND r.do_fail_type = 0
			` + condition + `
		GROUP BY
			r.id 
		ORDER BY
			ui.activity_num DESC ,
			a.activity_time DESC `
	if startSize > 0 || pageSize > 0 {
		sql += ` LIMIT ` + strconv.Itoa(startSize) + "," + strconv.Itoa(pageSize)
	}
	_, err = o.Raw(sql).QueryRows(&item)
	return
}

// 收藏列表数量
func GetCygxArticleCollectCountByCompany(condition string) (count int, err error) {
	o := orm.NewOrm()
	sqlCount := ` SELECT COUNT(1) AS count  FROM cygx_article_collect as r INNER JOIN cygx_article AS art ON art.article_id = r.article_id
			INNER JOIN wx_user as u  ON u.user_id = r.user_id  WHERE   1=1 ` + condition
	err = o.Raw(sqlCount).QueryRow(&count)
	return
}

// 收藏列表
func GetCygxArticleCollectByCompany(startSize, pageSize int, condition string) (items []*UserInteraction, err error) {
	o := orm.NewOrm()
	sql := ` SELECT
			art.title,
			art.article_id,
			art.article_id_md5,
			art.publish_date,
			re.chart_permission_name,
			re.chart_permission_id,
			r.create_time,
			r.mobile,
			r.real_name,
			re.match_type_name,
			(
			SELECT
				GROUP_CONCAT( DISTINCT s.subject_name SEPARATOR '/' ) 
			FROM
				cygx_industrial_subject AS s 
			WHERE
				s.industrial_subject_id IN ( SELECT industrial_subject_id FROM cygx_industrial_article_group_subject AS sg WHERE sg.article_id = art.article_id ) 
			) AS subject_name_str,
			(
			SELECT
				GROUP_CONCAT( DISTINCT man.industry_name SEPARATOR '/' ) 
			FROM
				cygx_industrial_management AS man 
			WHERE
				man.industrial_management_id IN ( SELECT industrial_management_id FROM cygx_industrial_article_group_management AS man_g WHERE man_g.article_id = art.article_id ) 
			) AS industry_name 
		FROM
			cygx_article_collect AS r
			INNER JOIN cygx_article AS art ON art.article_id = r.article_id
			INNER JOIN wx_user as u  ON u.user_id = r.user_id
			LEFT JOIN cygx_user_interaction_num AS ui ON ui.user_id = r.user_id
			LEFT JOIN cygx_report_mapping AS re ON re.category_id = art.category_id_two
			LEFT JOIN cygx_industrial_article_group_management AS man ON man.article_id = art.article_id 
		WHERE
			 1=1 ` + condition + ` GROUP BY r.id 
			ORDER BY ui.article_count_num DESC ,  r.create_time DESC  `
	if startSize > 0 || pageSize > 0 {
		sql += ` LIMIT ` + strconv.Itoa(startSize) + "," + strconv.Itoa(pageSize)
	}
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 关注产业数量
func GetCygxIndustryFllowCountByCompany(condition string) (count int, err error) {
	o := orm.NewOrm()
	sqlCount := ` SELECT COUNT(1) AS count  FROM cygx_industry_fllow AS r
			INNER JOIN cygx_industrial_management AS m ON m.industrial_management_id = r.industrial_management_id 
			INNER JOIN wx_user as u  ON u.user_id = r.user_id  WHERE   type = 1  ` + condition
	err = o.Raw(sqlCount).QueryRow(&count)
	return
}

// 关注产业列表
func GetCygxIndustryFllowByCompany(condition string, startSize, pageSize int) (items []*UserInteraction, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			m.industry_name,
			u.mobile,
			u.real_name,
			r.modify_time as create_time,
			( SELECT GROUP_CONCAT( DISTINCT s.subject_name SEPARATOR '/' ) FROM cygx_industrial_subject AS s WHERE s.industrial_management_id = m.industrial_management_id ) AS subject_name_str 
		FROM
			cygx_industry_fllow AS r
			LEFT  JOIN wx_user as u  ON u.mobile = r.mobile
			LEFT  JOIN cygx_user_interaction_num as ui  ON ui.user_id = u.user_id
			INNER JOIN cygx_industrial_management AS m ON m.industrial_management_id = r.industrial_management_id 
		WHERE  type = 1 ` + condition + ` ORDER BY ui.industry_fllow_num DESC, r.create_time DESC  LIMIT ?,? `
	_, err = o.Raw(sql, startSize, pageSize).QueryRows(&items)
	return
}

// 关注作者数量
func GetCygArticleDepartmentFollowCountByCompany(condition string) (count int, err error) {
	o := orm.NewOrm()
	sqlCount := ` SELECT COUNT(1) AS count  FROM cygx_article_department_follow AS r
			INNER JOIN cygx_article_department AS m ON m.department_id = r.department_id
			INNER JOIN wx_user as u  ON u.user_id = r.user_id  WHERE    type = 1  ` + condition
	err = o.Raw(sqlCount).QueryRow(&count)
	return
}

// 关注作者列表
func GetCygArticleDepartmentFollowByCompany(condition string, startSize, pageSize int) (items []*UserInteraction, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			m.department_id,
			m.nick_name,
			r.modify_time as  create_time,
			r.mobile,
			r.real_name,
			m.department_id,
			i.industry_name,
			a.title,
			a.article_id,
			( SELECT MAX( create_time ) FROM cygx_article_department_follow WHERE user_id = r.user_id ) AS max_time,
			( SELECT GROUP_CONCAT( DISTINCT i.industry_name SEPARATOR '/' ) FROM cygx_industrial_management AS i WHERE i.industrial_management_id = mg.industrial_management_id ) AS industry_name,
			( SELECT GROUP_CONCAT( DISTINCT s.subject_name SEPARATOR '/' ) FROM cygx_industrial_subject AS s WHERE s.industrial_management_id = i.industrial_management_id ) AS subject_name_str
		FROM
			cygx_article_department_follow AS r
			INNER JOIN cygx_article_department AS m ON m.department_id = r.department_id
			INNER JOIN cygx_article AS a ON a.department_id = m.department_id
			INNER JOIN wx_user as u  ON u.user_id = r.user_id
			LEFT JOIN cygx_user_interaction_num AS ui ON ui.user_id = r.user_id
			LEFT JOIN cygx_industrial_article_group_management AS mg ON mg.article_id = a.article_id
			LEFT JOIN cygx_industrial_management AS i ON i.industrial_management_id = mg.industrial_management_id
			WHERE  r.type = 1 ` + condition + `  GROUP BY 	r.id  
			ORDER BY  ui.department_follow_num DESC ,	max_time DESC  LIMIT ?,? `
	_, err = o.Raw(sql, startSize, pageSize).QueryRows(&items)
	return
}

// 用户搜索关键词统计
func GetCygxSearchKeyWordCountBuCompany(condition string) (count int, err error) {
	o := orm.NewOrm()
	sqlCount := ` SELECT COUNT(1) AS count  FROM cygx_search_key_word  as r  WHERE  1= 1  ` + condition
	err = o.Raw(sqlCount).QueryRow(&count)
	return
}

// 用户搜索关键词列表
func GetCygxSearchKeyWordByCompany(condition string, startSize, pageSize int) (items []*UserInteraction, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			r.key_word,
			r.create_time,
			r.mobile,
			r.real_name
		FROM
			cygx_search_key_word as r
			LEFT JOIN cygx_user_interaction_num AS ui ON ui.user_id = r.user_id
			WHERE 1= 1 ` + condition + `
			ORDER BY  ui.key_word_num DESC, r.create_time DESC  LIMIT ?,? `
	_, err = o.Raw(sql, startSize, pageSize).QueryRows(&items)
	return
}

// 用户专项产业调研统计
func GetCygxActivitySpecialTripCountByCompany(condition string) (count int, err error) {
	o := orm.NewOrm()
	sqlCount := ` SELECT
			COUNT( 1 ) 
		FROM
			cygx_activity_special_meeting_detail AS r
			INNER JOIN cygx_activity_special AS a ON a.activity_id = r.activity_id
		WHERE
			1 = 1 
			 AND a.publish_status = 1 AND a.activity_time_end < NOW()   ` + condition
	err = o.Raw(sqlCount).QueryRow(&count)
	return
}

// 用户专项产业调研列表
func CygxActivitySpecialTripByCompany(condition string, startSize, pageSize int) (items []*UserInteraction, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			a.research_theme AS activity_name,
			a.chart_permission_name,
			a.activity_time_text_by_day AS activity_time_text,
			a.city,
			a.special_type AS activity_type,
			r.mobile,
			r.activity_id,
			r.real_name,
			r.is_meeting 
		FROM
			cygx_activity_special_meeting_detail AS r
			INNER JOIN cygx_activity_special AS a ON a.activity_id = r.activity_id
			LEFT JOIN cygx_user_interaction_num AS ui ON ui.user_id = r.user_id
			WHERE 1= 1   AND a.publish_status = 1 AND a.activity_time_end < NOW() ` + condition + `
			ORDER BY  ui.trip_num DESC, r.create_time DESC  LIMIT ?,? `
	_, err = o.Raw(sql, startSize, pageSize).QueryRows(&items)
	return
}

// 用户音视频浏览统计
func GetCygxRoadshowCountByCompany(condition string) (count int, err error) {
	o := orm.NewOrm()
	sqlCount := `SELECT
	COUNT( 1 ) total 
FROM
	(
	SELECT
		"" 
	FROM
		cygx_micro_roadshow_video_history AS r
		INNER JOIN cygx_micro_roadshow_video AS v ON r.video_id = v.video_id
	WHERE 1=1 ` + condition + `  UNION ALL
	SELECT
		"" 
	FROM
		cygx_activity_video_history AS r
		INNER JOIN cygx_activity_video AS v ON r.video_id = v.video_id
	WHERE 1=1 ` + condition + ` UNION ALL
	SELECT
		"" 
	FROM
		cygx_activity_voice_history AS r
		INNER JOIN cygx_activity_voice AS v ON r.activity_id = v.activity_id
	WHERE 1=1 ` + condition + `)z  `
	err = o.Raw(sqlCount).QueryRow(&count)
	return
}

// 用户音视频浏览列表
func GetCygxRoadshowByCompany(condition string, startSize, pageSize int) (items []*UserInteraction, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			v.video_name AS media_title,
			r.create_time,
			r.real_name,
			r.mobile,
			ui.roadshow_num,
			"逻辑解析" AS file_type 
		FROM
			cygx_micro_roadshow_video_history AS r
			INNER JOIN cygx_micro_roadshow_video AS v ON r.video_id = v.video_id
			LEFT JOIN cygx_user_interaction_num AS ui ON ui.user_id = r.user_id 
		WHERE 1=1 ` + condition + `  UNION ALL
		SELECT
			v.video_name AS media_title,
			r.create_time,
			r.real_name,
			r.mobile,
			ui.roadshow_num,
			"路演回放" AS file_type 
		FROM
			cygx_activity_video_history AS r
			INNER JOIN cygx_activity_video AS v ON r.video_id = v.video_id
			LEFT JOIN cygx_user_interaction_num AS ui ON ui.user_id = r.user_id 
		WHERE 1=1 ` + condition + `   UNION ALL
		SELECT
			v.voice_name AS media_title,
			r.create_time,
			r.real_name,
			r.mobile,
			ui.roadshow_num,
			"路演回放" AS file_type 
		FROM
			cygx_activity_voice_history AS r
			INNER JOIN cygx_activity_voice AS v ON r.activity_id = v.activity_id
			LEFT JOIN cygx_user_interaction_num AS ui ON ui.user_id = r.user_id 
		WHERE 1=1 ` + condition + `  
		ORDER BY roadshow_num DESC ,create_time DESC   LIMIT ?,? `
	_, err = o.Raw(sql, startSize, pageSize).QueryRows(&items)
	return
}

// 用户音视频浏览列表
func GetCygxRoadshowByCompanyList(condition string, startSize, pageSize int) (items []*UserInteraction, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			v.video_name AS media_title,
			r.create_time,
			r.real_name,
			r.mobile,
			r.company_name,
			cp.seller_name,
			ci.roadshow_num,
			ui.roadshow_num AS ui_roadshow_num,
			"逻辑解析" AS file_type 
		FROM
			cygx_micro_roadshow_video_history AS r
			INNER JOIN cygx_micro_roadshow_video AS v ON r.video_id = v.video_id
			LEFT JOIN company_product AS cp ON cp.company_id = r.company_id AND cp.product_id = 2
			LEFT JOIN cygx_company_interaction_num AS ci ON ci.company_id = r.company_id
			LEFT JOIN cygx_user_interaction_num AS ui ON ui.user_id = r.user_id 
		WHERE 1=1 ` + condition + `  UNION ALL
		SELECT
			v.video_name AS media_title,
			r.create_time,
			r.real_name,
			r.mobile,
			r.company_name,
			cp.seller_name,
			ci.roadshow_num,
			ui.roadshow_num AS ui_roadshow_num,
			"路演回放" AS file_type 
		FROM
			cygx_activity_video_history AS r
			INNER JOIN cygx_activity_video AS v ON r.video_id = v.video_id
			LEFT JOIN company_product AS cp ON cp.company_id = r.company_id AND cp.product_id = 2
			LEFT JOIN cygx_company_interaction_num AS ci ON ci.company_id = r.company_id
			LEFT JOIN cygx_user_interaction_num AS ui ON ui.user_id = r.user_id 
		WHERE 1=1 ` + condition + `   UNION ALL
		SELECT
			v.voice_name AS media_title,
			r.create_time,
			r.real_name,
			r.mobile,
			r.company_name,
			cp.seller_name,
			ci.roadshow_num,
			ui.roadshow_num AS ui_roadshow_num,
			"路演回放" AS file_type 
		FROM
			cygx_activity_voice_history AS r
			INNER JOIN cygx_activity_voice AS v ON r.activity_id = v.activity_id
			LEFT JOIN company_product AS cp ON cp.company_id = r.company_id AND cp.product_id = 2
			LEFT JOIN cygx_company_interaction_num AS ci ON ci.company_id = r.company_id
			LEFT JOIN cygx_user_interaction_num AS ui ON ui.user_id = r.user_id 
		WHERE 1=1 ` + condition + `  
		ORDER BY 	roadshow_num DESC ,ui_roadshow_num DESC ,create_time DESC  LIMIT ?,? `
	_, err = o.Raw(sql, startSize, pageSize).QueryRows(&items)
	return
}

// 根据时间获取机构互动总数统计
func GetCompanyInteractionTableCountBytime(companyId int, startDate, endDate string) (item *GetUserInteractionTableCountResp, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			( SELECT COUNT( 1 ) FROM cygx_article_history_record_all AS h  INNER JOIN cygx_article  as art  ON  art.article_id = h.article_id  WHERE h.company_id = u.company_id AND h.is_del = 0  	AND create_time BETWEEN '` + startDate + `'AND '` + endDate + `'   AND h.create_time < date_format(now(),'%Y-%m-%d')  ) AS history_num,
			( SELECT COUNT( 1 ) FROM cygx_article_collect AS h WHERE h.company_id = u.company_id  AND create_time BETWEEN '` + startDate + `'AND '` + endDate + `'  AND h.create_time < date_format(now(),'%Y-%m-%d') ) AS count_num,
			( SELECT COUNT( 1 ) FROM cygx_chart_collect AS h WHERE h.company_id = u.company_id  AND create_time BETWEEN '` + startDate + `'AND '` + endDate + `'  AND h.create_time < date_format(now(),'%Y-%m-%d') ) AS chart_num,
			( SELECT COUNT( 1 ) FROM cygx_industry_fllow AS h WHERE h.company_id = u.company_id AND h.type = 1  AND create_time BETWEEN '` + startDate + `'AND '` + endDate + `'   AND h.create_time < date_format(now(),'%Y-%m-%d') ) AS industry_fllow_num,
			( SELECT COUNT( 1 ) FROM cygx_article_department_follow AS h WHERE h.company_id = u.company_id  AND h.type = 1 AND create_time BETWEEN '` + startDate + `'AND '` + endDate + `'   AND h.create_time < date_format(now(),'%Y-%m-%d') ) AS department_follow_num,
			( SELECT COUNT( 1 ) FROM cygx_search_key_word AS h WHERE h.company_id = u.company_id AND create_time BETWEEN '` + startDate + `'AND '` + endDate + `'  AND h.create_time < date_format(now(),'%Y-%m-%d') ) AS key_word_num,
			( SELECT 	COUNT( DISTINCT a.activity_id )  as count  FROM cygx_activity_signup AS h INNER JOIN cygx_activity as a ON a.activity_id = h.activity_id  INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id   WHERE h.company_id = u.company_id  
					 AND t.activity_type=1 AND h.do_fail_type = 0 AND a.activity_time BETWEEN '` + startDate + `'AND '` + endDate + `'   AND h.create_time < date_format(now(),'%Y-%m-%d') ) AS on_line_num,
			( SELECT  	COUNT( DISTINCT a.activity_id )  as count  FROM cygx_activity_signup AS h INNER JOIN cygx_activity as a ON a.activity_id = h.activity_id  INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id   WHERE h.company_id = u.company_id  
					 AND t.activity_type = 0 AND h.do_fail_type = 0  AND a.activity_time BETWEEN '` + startDate + `'AND '` + endDate + `'  AND h.create_time < date_format(now(),'%Y-%m-%d') ) AS office_num
		FROM
			company  as u
		WHERE
			u.company_id = ?`
	err = o.Raw(sql, companyId).QueryRow(&item)
	return
}

// 机构阅读记录列表
func GetCygxArticleHistoryRecordByCompanyList(condition string, startSize, pageSize int) (items []*UserInteraction, err error) {
	o := orm.NewOrm()
	sql := ` SELECT
			art.title,
			art.article_id,
			art.article_id_md5,
			art.publish_date,
			art.category_name,
			re.chart_permission_name,
			re.chart_permission_id,
			r.create_time,
			r.mobile,
			r.user_id,
			r.company_name,
			cp.seller_name,
			r.real_name,
			r.stop_time,
			r.source as source_platform,
			ui.article_history_num
		FROM
			cygx_article_history_record_all AS r
			INNER JOIN cygx_article AS art ON art.article_id = r.article_id
			INNER JOIN company_product AS cp ON cp.company_id = r.company_id AND cp.product_id = 2
			INNER JOIN cygx_company_interaction_num AS ci ON ci.company_id = r.company_id
			INNER JOIN cygx_user_interaction_num AS ui ON ui.user_id = r.user_id
			LEFT  JOIN cygx_report_mapping AS re ON re.category_id = art.category_id_two 
		WHERE
			 1=1  AND r.is_del = 0  ` + condition + `  GROUP BY r.id  ORDER BY ci.article_history_num DESC ,ui.article_history_num DESC ,  r.create_time DESC   `
	if startSize > 0 || pageSize > 0 {
		sql += ` LIMIT ` + strconv.Itoa(startSize) + "," + strconv.Itoa(pageSize)
	}
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 获取机构参会记录
func GetActivityMeetByCompanyList(condition string, startSize, pageSize int) (item []*UserInteraction, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			t.activity_type,
			r.signup_type,
			r.is_meeting,
			u.mobile,
			u.real_name,
			a.activity_name,
			a.chart_permission_name,
			a.activity_type_name,
			a.label,
			a.activity_time,
			r.first_meeting_time,
			r.last_meeting_time,
			r.meeting_type_str,
			r.meeting_authentication,
			r.meeting_status_str,
			r.company_name,
			cp.seller_name,
			r.real_name,
			r.duration
		FROM
			cygx_activity_signup_detail AS r
			INNER  JOIN cygx_activity AS a ON a.activity_id = r.activity_id
			INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id
			INNER JOIN wx_user as u  ON u.mobile = r.mobile
			LEFT JOIN company_product AS cp ON cp.company_id = r.company_id AND cp.product_id = 2
			LEFT JOIN cygx_company_interaction_num AS ci ON ci.company_id = r.company_id
			LEFT JOIN cygx_user_interaction_num AS ui ON ui.user_id = r.user_id
		WHERE
			1 = 1 
			AND r.do_fail_type = 0
			` + condition + `
		GROUP BY
			r.id 
		ORDER BY
			ci.activity_num DESC ,
			ui.activity_num DESC ,
			a.activity_time DESC `
	if startSize > 0 || pageSize > 0 {
		sql += ` LIMIT ` + strconv.Itoa(startSize) + "," + strconv.Itoa(pageSize)
	}
	_, err = o.Raw(sql).QueryRows(&item)
	return
}

// 机构收藏列表
func GetCygxArticleCollectByCompanyList(startSize, pageSize int, condition string) (items []*UserInteraction, err error) {
	o := orm.NewOrm()
	sql := ` SELECT
			art.title,
			art.article_id,
			art.article_id_md5,
			art.publish_date,
			re.chart_permission_name,
			re.chart_permission_id,
			r.create_time,
			r.mobile,
			r.real_name,
			re.match_type_name,
			r.company_name,
			cp.seller_name,
			(
			SELECT
				GROUP_CONCAT( DISTINCT s.subject_name SEPARATOR '/' ) 
			FROM
				cygx_industrial_subject AS s 
			WHERE
				s.industrial_subject_id IN ( SELECT industrial_subject_id FROM cygx_industrial_article_group_subject AS sg WHERE sg.article_id = art.article_id ) 
			) AS subject_name_str,
			(
			SELECT
				GROUP_CONCAT( DISTINCT man.industry_name SEPARATOR '/' ) 
			FROM
				cygx_industrial_management AS man 
			WHERE
				man.industrial_management_id IN ( SELECT industrial_management_id FROM cygx_industrial_article_group_management AS man_g WHERE man_g.article_id = art.article_id ) 
			) AS industry_name 
		FROM
			cygx_article_collect AS r
			INNER JOIN cygx_article AS art ON art.article_id = r.article_id
			INNER JOIN wx_user as u  ON u.user_id = r.user_id
			LEFT JOIN company_product AS cp ON cp.company_id = r.company_id AND cp.product_id = 2
			LEFT JOIN cygx_company_interaction_num AS ci ON ci.company_id = r.company_id
			LEFT JOIN cygx_user_interaction_num AS ui ON ui.user_id = r.user_id
			LEFT JOIN cygx_report_mapping AS re ON re.category_id = art.category_id_two
			LEFT JOIN cygx_industrial_article_group_management AS man ON man.article_id = art.article_id 
		WHERE
			 1=1 ` + condition + ` GROUP BY r.id 
			ORDER BY ui.article_count_num DESC ,ui.article_count_num DESC ,  r.create_time DESC  `
	if startSize > 0 || pageSize > 0 {
		sql += ` LIMIT ` + strconv.Itoa(startSize) + "," + strconv.Itoa(pageSize)
	}
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 关注产业列表
func GetCygxIndustryFllowByCompanyList(condition string, startSize, pageSize int) (items []*UserInteraction, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			m.industry_name,
			u.mobile,
			u.real_name,
			r.modify_time as create_time,
			cp.company_name,
			cp.seller_name,
			( SELECT GROUP_CONCAT( DISTINCT s.subject_name SEPARATOR '/' ) FROM cygx_industrial_subject AS s WHERE s.industrial_management_id = m.industrial_management_id ) AS subject_name_str 
		FROM
			cygx_industry_fllow AS r
			INNER JOIN cygx_industrial_management AS m ON m.industrial_management_id = r.industrial_management_id 
			INNER JOIN wx_user as u  ON u.user_id = r.user_id
			LEFT JOIN company_product AS cp ON cp.company_id = r.company_id AND cp.product_id = 2
			LEFT JOIN cygx_company_interaction_num AS ci ON ci.company_id = r.company_id
			LEFT  JOIN cygx_user_interaction_num as ui  ON ui.user_id = u.user_id
			
		WHERE  type = 1 ` + condition + ` ORDER BY ci.industry_fllow_num DESC,ui.industry_fllow_num DESC, r.create_time DESC  LIMIT ?,? `
	_, err = o.Raw(sql, startSize, pageSize).QueryRows(&items)
	return
}

// 关注作者机构列表
func GetCygArticleDepartmentFollowByCompanyList(condition string, startSize, pageSize int) (items []*UserInteraction, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			m.department_id,
			m.nick_name,
			r.modify_time as  create_time,
			r.mobile,
			r.real_name,
			cp.company_name,
			cp.seller_name,
			m.department_id,
			i.industry_name,
			a.title,
			a.article_id,
			( SELECT MAX( create_time ) FROM cygx_article_department_follow WHERE user_id = r.user_id ) AS max_time,
			( SELECT GROUP_CONCAT( DISTINCT i.industry_name SEPARATOR '/' ) FROM cygx_industrial_management AS i WHERE i.industrial_management_id = mg.industrial_management_id ) AS industry_name,
			( SELECT GROUP_CONCAT( DISTINCT s.subject_name SEPARATOR '/' ) FROM cygx_industrial_subject AS s WHERE s.industrial_management_id = i.industrial_management_id ) AS subject_name_str
		FROM
			cygx_article_department_follow AS r
			INNER JOIN cygx_article_department AS m ON m.department_id = r.department_id
			INNER JOIN cygx_article AS a ON a.department_id = m.department_id
			INNER JOIN wx_user as u  ON u.user_id = r.user_id 
			LEFT JOIN cygx_user_interaction_num AS ui ON ui.user_id = r.user_id
			LEFT JOIN company_product AS cp ON cp.company_id = r.company_id AND cp.product_id = 2
			LEFT JOIN cygx_company_interaction_num AS ci ON ci.company_id = r.company_id
			LEFT JOIN cygx_industrial_article_group_management AS mg ON mg.article_id = a.article_id
			LEFT JOIN cygx_industrial_management AS i ON i.industrial_management_id = mg.industrial_management_id
			WHERE  r.type = 1 ` + condition + `  GROUP BY 	r.id  
			ORDER BY  ci.department_follow_num DESC ,ui.department_follow_num DESC ,	max_time DESC  LIMIT ?,? `
	_, err = o.Raw(sql, startSize, pageSize).QueryRows(&items)
	return
}

// 用户搜索关键词列表
func GetCygxSearchKeyWordByCompanyList(condition string, startSize, pageSize int) (items []*UserInteraction, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			r.key_word,r.create_time,
			r.mobile,
			r.source,
			cp.company_name,
			cp.seller_name,
			r.real_name
		FROM
			cygx_search_key_word as r
			LEFT JOIN company_product AS cp ON cp.company_id = r.company_id AND cp.product_id = 2
			LEFT JOIN cygx_company_interaction_num AS ci ON ci.company_id = r.company_id
			LEFT JOIN cygx_user_interaction_num AS ui ON ui.user_id = r.user_id
			WHERE 1= 1 ` + condition + `
			ORDER BY  ci.key_word_num DESC,ui.key_word_num DESC, r.create_time DESC  LIMIT ?,? `
	_, err = o.Raw(sql, startSize, pageSize).QueryRows(&items)
	return
}

// 用户搜索关键词列表
func GetCygxActivitySpecialTripByCompanyList(condition string, startSize, pageSize int) (items []*UserInteraction, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			a.research_theme AS activity_name,
			a.chart_permission_name,
			a.activity_time_text_by_day AS activity_time_text,
			a.city,
			a.special_type AS activity_type,
			r.mobile,
			r.real_name,
			r.is_meeting,
			r.activity_id,
			cp.company_name,
			cp.seller_name,
			r.real_name
		FROM
			cygx_activity_special_trip as r
			INNER JOIN cygx_activity_special AS a ON a.activity_id = r.activity_id
			LEFT JOIN company_product AS cp ON cp.company_id = r.company_id AND cp.product_id = 2
			LEFT JOIN cygx_company_interaction_num AS ci ON ci.company_id = r.company_id
			LEFT JOIN cygx_user_interaction_num AS ui ON ui.user_id = r.user_id
			WHERE 1= 1 ` + condition + `
			ORDER BY  ci.trip_num DESC,ui.trip_num DESC, r.create_time DESC  LIMIT ?,? `
	_, err = o.Raw(sql, startSize, pageSize).QueryRows(&items)
	return
}

// 通过纪要ID获取活动详情
func GetTableCompanyList() (item *GetUserInteractionTableCountResp, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	SUM( article_history_num ) AS history_num,
	SUM( article_count_num ) AS count_num,
	SUM( industry_fllow_num ) AS industry_fllow_num,
	SUM( department_follow_num ) AS department_follow_num,
	SUM( key_word_num ) AS key_word_num,
	SUM( activity_num ) AS activity_num,
	SUM( chart_count_num ) AS chart_num 
FROM
	cygx_company_interaction_num 
	LIMIT 1`
	err = o.Raw(sql).QueryRow(&item)
	return
}

// 获取用户参会记录根据销售分组
func GetActivityMeetBySeller(condition string) (list []*company.CompanyReportRecordGroup, err error) {
	o := orm.NewOrm()
	sqlCount := `				SELECT
				p.seller_id as admin_id,count(DISTINCT(r.company_id)) num,GROUP_CONCAT(DISTINCT r.company_id SEPARATOR ',') AS company_ids
			FROM
				cygx_activity_signup AS r
				INNER JOIN cygx_activity AS a ON a.activity_id = r.activity_id
				INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id
				INNER JOIN wx_user AS u ON u.user_id = r.user_id 
				INNER JOIN company_product AS p ON p.company_id=r.company_id
			WHERE
				1 = 1 
				AND r.do_fail_type = 0
				AND p.product_id = 2
				`
	if condition != "" {
		sqlCount += condition
	}
	sqlCount += ` GROUP BY p.seller_id `
	_, err = o.Raw(sqlCount).QueryRows(&list)
	return
}

// 收藏列表数量根据销售分组
func GetCygxArticleCollectCountBySeller(condition string) (list []*company.CompanyReportRecordGroup, err error) {
	o := orm.NewOrm()
	sqlCount := ` SELECT
	p.seller_id as admin_id,count(DISTINCT(r.company_id)) num,GROUP_CONCAT(DISTINCT r.company_id SEPARATOR ',') AS company_ids
FROM
	cygx_article_collect AS r
	INNER JOIN cygx_article AS art ON art.article_id = r.article_id
	INNER JOIN wx_user AS u ON u.user_id = r.user_id 
	INNER JOIN company_product AS p ON p.company_id=r.company_id
WHERE
	1 = 1
	AND p.product_id=2  ` + condition

	sqlCount += ` GROUP BY p.seller_id `
	_, err = o.Raw(sqlCount).QueryRows(&list)
	return
}

// 关注产业数量根据销售分组
func GetCygxIndustryFllowCountBySeller(condition string) (list []*company.CompanyReportRecordGroup, err error) {
	o := orm.NewOrm()
	sqlCount := ` SELECT
	p.seller_id AS admin_id,
	count(
	DISTINCT ( r.company_id )) num,
	GROUP_CONCAT( DISTINCT r.company_id SEPARATOR ',' ) AS company_ids 
FROM
	cygx_industry_fllow AS r
	INNER JOIN cygx_industrial_management AS m ON m.industrial_management_id = r.industrial_management_id
	INNER JOIN wx_user AS u ON u.user_id = r.user_id
	INNER JOIN company_product AS p ON p.company_id = r.company_id 
WHERE
	type = 1 
	AND p.product_id = 2 
  ` + condition

	sqlCount += ` GROUP BY p.seller_id `
	_, err = o.Raw(sqlCount).QueryRows(&list)
	return
}

// 关注作者数量根据销售分组
func GetCygArticleDepartmentFollowCountBySeller(condition string) (list []*company.CompanyReportRecordGroup, err error) {
	o := orm.NewOrm()
	sqlCount := ` SELECT
	p.seller_id AS admin_id,
	count(
	DISTINCT ( r.company_id )) num,
	GROUP_CONCAT( DISTINCT r.company_id SEPARATOR ',' ) AS company_ids 
FROM
	cygx_article_department_follow AS r
	INNER JOIN cygx_article_department AS m ON m.department_id = r.department_id
	INNER JOIN wx_user AS u ON u.user_id = r.user_id
	INNER JOIN company_product AS p ON p.company_id = r.company_id 
WHERE
	type = 1 
	AND p.product_id = 2  ` + condition

	sqlCount += ` GROUP BY p.seller_id `
	_, err = o.Raw(sqlCount).QueryRows(&list)
	return
}

// 用户搜索关键词统计根据销售分组
func GetCygxSearchKeyWordCountBySeller(condition string) (list []*company.CompanyReportRecordGroup, err error) {
	o := orm.NewOrm()
	sqlCount := ` SELECT
	p.seller_id AS admin_id,
	count(
	DISTINCT ( r.company_id )) num,
	GROUP_CONCAT( DISTINCT r.company_id SEPARATOR ',' ) AS company_ids 
FROM
	cygx_search_key_word AS r 
	INNER JOIN company_product AS p ON p.company_id = r.company_id 
WHERE
	1 = 1
	AND p.product_id = 2  ` + condition
	sqlCount += ` GROUP BY p.seller_id `
	_, err = o.Raw(sqlCount).QueryRows(&list)
	return
}

type CompanyAndCount struct {
	CompanyId int
	Count     int
}

// 获取用户参会记录数量根据公司分组
func GetActivityMeetGroupByCompany(condition string) (list []*CompanyAndCount, err error) {
	o := orm.NewOrm()
	sqlCount := `	SELECT
	r.company_id,COUNT(*) AS count  
FROM
	cygx_activity_signup AS r
	INNER JOIN cygx_activity AS a ON a.activity_id = r.activity_id
	INNER JOIN cygx_activity_type AS t ON t.activity_type_id = a.activity_type_id
	INNER JOIN wx_user AS u ON u.user_id = r.user_id 
WHERE
	1 = 1 
	AND r.do_fail_type = 0 `
	if condition != "" {
		sqlCount += condition
	}
	sqlCount += ` GROUP BY r.company_id `
	_, err = o.Raw(sqlCount).QueryRows(&list)
	return
}

// 收藏列表数量根据公司分组
func GetCygxArticleCollectCountGroupByCompany(condition string) (list []*CompanyAndCount, err error) {
	o := orm.NewOrm()
	sqlCount := `	SELECT
	r.company_id,
	COUNT(*) AS count 
FROM
	cygx_article_collect AS r
	INNER JOIN cygx_article AS art ON art.article_id = r.article_id
	INNER JOIN wx_user AS u ON u.user_id = r.user_id 
WHERE
	1 = 1 `
	if condition != "" {
		sqlCount += condition
	}
	sqlCount += ` GROUP BY r.company_id `
	_, err = o.Raw(sqlCount).QueryRows(&list)
	return
}

// 关注产业数量根据公司分组
func GetCygxIndustryFllowCountGroupByCompany(condition string) (list []*CompanyAndCount, err error) {
	o := orm.NewOrm()
	sqlCount := `		SELECT
	r.company_id,
	COUNT(*) AS count 
FROM
	cygx_industry_fllow AS r
	INNER JOIN cygx_industrial_management AS m ON m.industrial_management_id = r.industrial_management_id
	INNER JOIN wx_user AS u ON u.user_id = r.user_id 
WHERE
	type = 1 `
	if condition != "" {
		sqlCount += condition
	}
	sqlCount += ` GROUP BY r.company_id `
	_, err = o.Raw(sqlCount).QueryRows(&list)
	return
}

// 关注作者数量根据公司分组
func GetCygArticleDepartmentFollowCountGroupByCompany(condition string) (list []*CompanyAndCount, err error) {
	o := orm.NewOrm()
	sqlCount := `	SELECT
	r.company_id,
	COUNT(*) AS count 
FROM
	cygx_article_department_follow AS r
	INNER JOIN cygx_article_department AS m ON m.department_id = r.department_id
	INNER JOIN wx_user AS u ON u.user_id = r.user_id 
WHERE
	type = 1 `
	if condition != "" {
		sqlCount += condition
	}
	sqlCount += ` GROUP BY r.company_id `
	_, err = o.Raw(sqlCount).QueryRows(&list)
	return
}

// 用户搜索关键词统计
func GetCygxSearchKeyWordCountGroupByCompany(condition string) (list []*CompanyAndCount, err error) {
	o := orm.NewOrm()
	sqlCount := ` SELECT
	r.company_id,
	COUNT(*) AS count 
FROM
	cygx_search_key_word AS r 
WHERE
	1 = 1 ` + condition
	if condition != "" {
		sqlCount += condition
	}
	sqlCount += ` GROUP BY r.company_id `
	_, err = o.Raw(sqlCount).QueryRows(&list)
	return
}

func GetChartCountGroupByCompany(condition string) (list []*CompanyAndCount, err error) {
	o := orm.NewOrm()
	sqlCount := ` SELECT
	r.company_id,
	COUNT(*) AS count 
FROM
	cygx_chart_collect AS r
	INNER JOIN cygx_chart_all AS a ON a.chart_id = r.chart_id 
WHERE
	1 = 1 ` + condition
	if condition != "" {
		sqlCount += condition
	}
	sqlCount += ` GROUP BY r.company_id `
	_, err = o.Raw(sqlCount).QueryRows(&list)
	return
}

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

// 获取文章阅读记录根据公司分组
func GetArticleHistoryGroupByCompany(condition string) (list []*CompanyAndCount, err error) {
	o := orm.NewOrm()
	sqlCount := `	SELECT
	r.company_id,COUNT(*) AS count  
FROM
	cygx_article_history_record_all AS r
	
WHERE
	1 = 1 
	AND r.is_del = 0
 	AND r.company_id >0 `
	if condition != "" {
		sqlCount += condition
	}
	sqlCount += ` GROUP BY r.company_id `
	_, err = o.Raw(sqlCount).QueryRows(&list)
	return
}