hongze_cygx/models/send_company_user.go

package models

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

type CompanyJson struct {
	CompanyName  string `json:"name"`       //公司名称
	ShortName    string `json:"short_name"` //公司简称
	CreditCode   string `json:"social"`     //社会信用代码
	City         string `json:"city"`       //所在地级市或直辖市
	SignType     string `json:"sign_type"`  //客户重要度（A类、B类、C类、D类）
	Fe           string `json:"f_e"`        //客户类型（FICC、权益、策略）
	IndustryName string `json:"industry"`   //所属行业（公募、私募、券商资管、保险、海外、其他类型）
	Mobile       string `json:"sale_phone"` //所属销售（销售手机号）
	Status       string `json:"status"`     //客户状态（正式、试用）
	//Permission     string            `json:"permissions"`     //客户权限（医药,科技,智造,消费,策略,专家,路演服务,研选）
	StartDate      string            `json:"start_time"`      //服务期限开始时间
	EndDate        string            `json:"end_time"`        //服务期限结束时间
	Info           string            `json:"info"`            //简介
	CreatedTime    string            `json:"create_time"`     //创建时间
	PermissionData []*PermissionData `json:"permission_data"` //创建时间
	//CreatedTimes string //创建时间
	EndDateTime time.Time //创建时间
	Cid         int       // 公司ID
}

type PermissionDataList struct {
	PermissionData []*PermissionData
}
type PermissionData struct {
	PermissionName string `json:"permission_name"` //权限名称
	StartTime      string `json:"start_time"`      //权限开始时间
	EndTime        string `json:"end_time"`        //权限结束时间
	IsTryOut       int    `json:"is_try_out"`      //权限是否试用：0=否，1=是
	Money          int    `json:"money"`           //权限是否试用：0=否，1=是
}

type UserJson struct {
	CreditCode          string `json:"social"`            //社会信用代码
	RealName            string `json:"name"`              //昵称
	Mobile              string `json:"phone"`             //手机号1
	CountryCode         string `json:"area_code"`         //国家号1（+86..）
	OutboundMobile      string `json:"phone2"`            //手机号2
	OutboundCountryCode string `json:"area_code2"`        //国家号2（+86..）
	Telephone           string `json:"machine"`           //座机
	MachineAreaCode     string `json:"machine_area_code"` //座机国家号（+86..）
	Sex                 string `json:"sex"`               //性别（男、女）
	Email               string `json:"email"`             //电子邮箱，比如 example@qq.com
	Department          string `json:"department"`        //部门
	Position            string `json:"position"`          //职位
	Level               string `json:"level"`             //职位等级（研究员、基金经理、部门总监）
	IsMaker             string `json:"policymakers"`      //是否决策人（是、否）
	CreatedTime         string `json:"create_time"`       //创建时间
}
type CompanyJsonList struct {
	SyncData *[]CompanyJson `json:"sync_data"`
}

type CompanyUnJsonList struct {
	SyncData []CompanyJson `json:"sync_data"`
}

// 删除用户
type ShangHaiCrmUserDeleteResp struct {
	Social string `json:"social"`
	Mobile string `json:"phone"`
	Name   string `json:"name"`
}

// 获取公司
func GetSendCompanyList(condition string) (items []*CompanyJson, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := ` SELECT
			c.company_name,
			c.company_id as cid, 
			c.credit_code,
			c.city,
			p.industry_name,
			am.real_name,
			am.mobile,
			p.status,
			p.end_date as end_date_time,
			UNIX_TIMESTAMP( p.start_date ) AS start_date,
			UNIX_TIMESTAMP( p.end_date ) AS end_date,
			UNIX_TIMESTAMP( a.created_time ) AS created_time,
			(SELECT GROUP_CONCAT(DISTINCT b.chart_permission_name  ORDER BY b.sort ASC  SEPARATOR ',')
					FROM company_report_permission AS a
					INNER JOIN chart_permission AS b ON a.chart_permission_id=b.chart_permission_id
					INNER JOIN company_product AS c ON a.company_id=c.company_id AND a.product_id=c.product_id
					WHERE  a.company_id=cid
					AND c.enabled = 1 
					AND b.cygx_auth=1
					AND a.STATUS IN ( '正式', '试用', '永续' )) AS permission 
		FROM
			company_report_permission AS a
			INNER JOIN chart_permission AS b ON a.chart_permission_id = b.chart_permission_id
			INNER JOIN company_product AS p ON a.company_id = p.company_id
			INNER JOIN company AS c ON c.company_id = a.company_id
			INNER JOIN admin AS am ON am.admin_id = p.seller_id 
			AND a.product_id = p.product_id 
		WHERE
			1 = 1 
			AND c.enabled = 1 
			AND b.cygx_auth = 1
			AND p.STATUS IN ( '正式', '试用','冻结','流失' ,'流失', '永续' )
			AND( c.credit_code LIKE '91%'  OR  c.credit_code LIKE 'HZ%' )
			AND p.product_id = 2 ` + condition +
		`GROUP BY 
			c.company_id  `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

type PermissionDataResp struct {
	CompanyId      int       `description:"公司ID"`
	PermissionName string    `description:"行业名称"`
	Status         string    `description:"状态"`
	StartDate      time.Time `description:"行业名称"`
	EndDate        time.Time `description:"行业名称"`
}

func GetSendCompanyPermissionDataList(condition string, pars []interface{}) (items []*PermissionDataResp, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := ` SELECT
				company_id,
				c.permission_name,
				cp.status,
				cp.end_date,
				cp.start_date 
			FROM
				company_report_permission AS cp
				INNER JOIN chart_permission AS c ON cp.chart_permission_id = c.chart_permission_id 
			WHERE
				1 = 1
				AND cp.STATUS IN ( '正式', '试用', '永续' )
				AND c.chart_permission_name IN ( '医药', '消费', '科技', '智造', '策略', '研选扣点包', '专家', '路演服务' ) ` + condition + ` GROUP BY c.permission_name, cp.company_id `
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// 不做正式，试用，永续权限的校验
func GetSendCompanyPermissionDataListNoChekcStatus(condition string, pars []interface{}) (items []*PermissionDataResp, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := ` SELECT
				company_id,
				c.permission_name,
				cp.status,
				cp.end_date,
				cp.start_date 
			FROM
				company_report_permission AS cp
				INNER JOIN chart_permission AS c ON cp.chart_permission_id = c.chart_permission_id 
			WHERE
				1 = 1
				AND cp.STATUS  NOT  IN ( '正式', '试用', '永续' )
				AND c.chart_permission_name IN ( '医药', '消费', '科技', '智造', '策略', '研选扣点包', '专家', '路演服务' ) ` + condition + ` GROUP BY c.permission_name, cp.company_id `
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// 获取FICC公司
func GetSendCompanyFiccList(condition string) (items []*CompanyJson, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := ` SELECT
			c.company_name,
			c.company_id AS cid,
			c.credit_code,
			c.city,
			p.industry_name,
			am.real_name,
			am.mobile,
			p.status,
			p.end_date AS end_date_time,
			UNIX_TIMESTAMP( p.start_date ) AS start_date,
			UNIX_TIMESTAMP( p.end_date ) AS end_date,
			UNIX_TIMESTAMP( a.created_time ) AS created_time,
			(
			SELECT
				GROUP_CONCAT( DISTINCT b.chart_permission_name ORDER BY b.sort ASC SEPARATOR ',' ) 
			FROM
				company_report_permission AS a
				INNER JOIN chart_permission AS b ON a.chart_permission_id = b.chart_permission_id
				INNER JOIN company_product AS c ON a.company_id = c.company_id 
				AND a.product_id = c.product_id 
			WHERE
				a.company_id = cid 
				AND c.enabled = 1 
				AND b.cygx_auth = 1 
				AND a.STATUS IN ( '正式', '试用', '永续' )) AS permission 
		FROM
			company_report_permission AS a
			INNER JOIN chart_permission AS b ON a.chart_permission_id = b.chart_permission_id
			INNER JOIN company_product AS p ON a.company_id = p.company_id
			INNER JOIN company AS c ON c.company_id = a.company_id
			INNER JOIN admin AS am ON am.admin_id = p.seller_id 
			AND a.product_id = p.product_id 
		WHERE
			1 = 1 
			AND c.enabled = 1 
			AND b.cygx_auth = 1
			AND p.STATUS IN ( '正式', '试用','冻结','流失' )
			AND ( c.credit_code LIKE '91%' OR c.credit_code LIKE 'HZ%' ) 
			AND p.product_id = 1 ` + condition +
		`AND c.company_id NOT IN (
			SELECT
				c.company_id 
			FROM
				company_report_permission AS a
				INNER JOIN chart_permission AS b ON a.chart_permission_id = b.chart_permission_id
				INNER JOIN company_product AS p ON a.company_id = p.company_id
				INNER JOIN company AS c ON c.company_id = a.company_id
				INNER JOIN admin AS am ON am.admin_id = p.seller_id 
				AND a.product_id = p.product_id 
			WHERE
				1 = 1 
				AND c.enabled = 1 
				AND b.cygx_auth = 1 
				AND p.STATUS IN  ( '正式', '试用' ,'冻结','流失', '永续')
				AND ( c.credit_code LIKE '91%' OR c.credit_code LIKE 'HZ%' ) 
				AND p.product_id = 2
			GROUP BY
				c.company_id 
			) 
		GROUP BY
			c.company_id  `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 获取用户
func GetSendUserList(condition string) (items []*UserJson, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := ` SELECT
			c.credit_code,
			u.real_name,
			u.mobile,
			u.country_code,
			u.outbound_mobile,
			u.outbound_country_code,
			u.telephone,
			u.sex,
			u.is_maker,
			c.company_id,
			UNIX_TIMESTAMP( us.create_time ) AS created_time 
		FROM
			wx_user AS u
			INNER JOIN company AS c ON c.company_id = u.company_id
			INNER JOIN user_seller_relation AS us ON us.user_id = u.user_id 
		WHERE
			1 = 1 
			AND u.company_id IN (
			SELECT
				c.company_id 
			FROM
				company_report_permission AS a
				INNER JOIN chart_permission AS b ON a.chart_permission_id = b.chart_permission_id
				INNER JOIN company_product AS p ON a.company_id = p.company_id
				INNER JOIN company AS c ON c.company_id = a.company_id
				INNER JOIN admin AS am ON am.admin_id = p.seller_id 
				AND a.product_id = p.product_id 
			WHERE
				1 = 1 
				AND c.enabled = 1 
				AND b.cygx_auth = 1 
				AND p.STATUS IN ( '正式', '试用','冻结','流失','流失', '永续' )
				AND us.create_time  != '' ` + condition +
		`GROUP BY
				c.company_id 
			)
			AND us.create_time != '' 
			AND u.real_name != '' 
			AND u.mobile != '' 
			AND ( c.credit_code LIKE '91%' OR c.credit_code LIKE 'HZ%' ) GROUP BY u.user_id   ORDER BY  c.company_id  DESC `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

type WxUserOpLogResp struct {
	CompanyId int    `description:"客户ID"`
	UserId    int    `description:"用户ID"`
	Mobile    string `description:"用户ID"`
}

type WxUserOpLogDeleteResp struct {
	CompanyId        int    `description:"客户ID"`
	UserId           int    `description:"用户ID"`
	Mobile           string `description:"用户ID"`
	OriginalUserInfo string `description:"用户详情"`
	CreditCode       string `description:"社会信用码"`
	RealName         string `description:"真实姓名"`
}

// 获取指定时间内更新的用户
func GetWxUserOpLog(createTime string) (items []*WxUserOpLogResp, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := ` SELECT
			u.company_id 
		FROM
			wx_user_op_log AS l
			INNER JOIN wx_user AS u ON u.mobile = l.mobile 
		WHERE
			 l.create_time >=  '` + createTime + `'  GROUP BY u.company_id `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 获取指定时间内删除的用户
func GetWxUserOpLogDelete(createTime string) (items []*WxUserOpLogDeleteResp, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := `SELECT
			l.*,
			c.credit_code 
		FROM
			wx_user_op_log AS l
			INNER JOIN company AS c ON c.company_id = l.company_id  WHERE l.log_type = 'delete' AND l.create_time >=  '` + createTime + `'`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 获取指定时间内被暂停的客户
func GetWxUserOpLogSuspend(createTime string) (items []*WxUserOpLogDeleteResp, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := `SELECT
				* 
			FROM
				company_operation_record AS p 
			WHERE
				remark = '暂停' 
				AND p.id IN ( SELECT MAX( p.id ) AS max_id FROM company_operation_record AS p WHERE 1 = 1 AND p.create_time > '` + createTime + `' )  `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 获取指定时间内被移动的客户
func GetCompanyOperationRecord(createTime string) (items []*WxUserOpLogDeleteResp, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := `SELECT
				* 
			FROM
				company_operation_record AS p 
			WHERE
				 1=1
				 AND p.create_time > '` + createTime + `'  GROUP BY company_id  `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 获取指定时间内试用转冻结，冻结转流失的客户
func GetCompanyFreezeAndLoss(createTime string) (items []*WxUserOpLogResp, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := ` SELECT * FROM  company_operation_record WHERE status IN ('冻结','流失') AND  create_time >  '` + createTime + `'  GROUP BY company_id `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 获取指定时间内被移动的用户
func GetWxUserOpLogList(startDate, endDate string) (items []*WxUserOpLogResp, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := ` SELECT company_id,user_id,mobile FROM wx_user_op_log WHERE  log_type IN ('move','add') AND create_time >=  '` + startDate + `' AND create_time <=  '` + endDate + `'    GROUP BY user_id `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 获取指定时间内被删除的用户
func GetWxUserOpLogDeleteList(startDate, endDate string) (items []*WxUserOpLogResp, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := ` SELECT company_id,user_id,mobile FROM wx_user_op_log WHERE  log_type IN ('delete') AND create_time >=  '` + startDate + `' AND create_time <=  '` + endDate + `'   GROUP BY user_id `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}