networkManage/api/internal/service/plistService.go

package service

import (
	"context"
	"encoding/json"
	"fmt"
	"math"
	"strconv"
	"strings"

	"app.yhyue.com/moapp/jybase/common"
	"app.yhyue.com/moapp/jybase/encrypt"
	"app.yhyue.com/moapp/jybase/redis"
	"app.yhyue.com/moapp/jypkg/ent/util"
	T "bp.jydev.jianyu360.cn/CRM/networkManage/api/common"
	"bp.jydev.jianyu360.cn/CRM/networkManage/api/internal/types"
	"github.com/gogf/gf/v2/util/gconv"
	"github.com/shopspring/decimal"
	"github.com/zeromicro/go-zero/core/logx"
)

const (
	sql_1                    = `SELECT buyer_id, count(1) as count FROM information.transaction_info_all WHERE buyer_id in (%s) GROUP BY buyer_id`
	sql_2                    = `SELECT relate_id, is_handle, is_ignore, is_create FROM crm.connection_status WHERE position_id = ? AND itype = 2`
	sql_3                    = `SELECT * FROM crm.connection WHERE company_id in (%s) AND status = 1`
	sql_4                    = `SELECT id, s_id FROM base_service.follow_project_monitor WHERE s_userid = ?`
	NetworkManageProjectList = "networkManage_projectList_%d_%s"
)

type ProjectData struct {
	Count       int64
	HasNextPage bool
	PList       []*ProjectEntry
}

type ProjectEntry struct {
	ProjectId     string          `ch:"project_id"`
	ProjectName   string          `ch:"project_name"`
	BusinessType  string          `ch:"business_type"`
	Buyer         string          `ch:"buyer"`
	BuyerId       string          `ch:"buyer_id"`
	Area          string          `ch:"area"`
	City          string          `ch:"city"`
	District      string          `ch:"district"`
	ZbTime        int64           `ch:"zbtime"`
	EndTime       int64           `ch:"endtime"`
	ProjectMoney  decimal.Decimal `ch:"project_money"`
	InfoId        string          `ch:"info_id"`
	InformationId string          `ch:"information_id"`
	InfoIds       []string        `ch:"info_ids"`
	Href          string          `json:"Href"`
	IsHandle      int             `json:"IsHandle"`
	IsIgnore      int             `json:"IsIgnore"`
	IsCreate      int             `json:"IsCreate"`
	MyConn        bool            `json:"MyConn"`
	ConnType      int             `json:"ConnType"`
	HighSuccess   bool            `json:"HighSuccess"`
	BId           string          `json:"BId"`
	BName         string          `json:"BName"`
	RelationShip  string          `json:"RelationShip"`
	SourceType    string          `json:"SourceType"` // firstparty：甲方 supplier：供应商 adiffb：同甲异业 middleman：中间人 agency：招标代理机构
	Person        string          `json:"Person"`
	Num           int             `json:"Num"`
	FocusId       string          `json:"FocusId"` // 监控主键
	IsFocus       int             `json:"IsFocus"`
}

func GetProjectList(req *types.ProjectListReq) *ProjectData {
	projectData := &ProjectData{
		PList: []*ProjectEntry{},
	}
	businessStr := FindBusiness(req.EntId, req.UserId)
	if businessStr == "" {
		return projectData
	}
	var plist []string
	if req.Intermediary != "" {
		plist = getIyProList(req.Intermediary, req.PositionId)
	}
	if req.PartyA == "" && len(plist) == 0 && req.Supplier == "" && req.Heterotophy == "" && req.Agency == "" {
		return projectData
	}
	//preSales := preSalesStatus(req.PositionId)
	//isSqlPage := true
	//if req.SaleStatus == "0" {
	//	isSqlPage = true // 是否sql分页
	//}
	countSql, findSql, args := getQuerySql(req, plist, businessStr)
	// 缓存
	rkey := fmt.Sprintf(NetworkManageProjectList, req.PositionId, common.GetMd5String(fmt.Sprintf("%+v", req)))
	if ret, err := redis.GetNewBytes("newother", rkey); false && err == nil && ret != nil {
		err = json.Unmarshal(*ret, &projectData)
	} else {
		logx.Info("findSql: ", findSql, args)
		logx.Info("countSql: ", countSql, args)
		rows, err := T.ClickhouseConn.Query(context.TODO(), findSql, args...)
		if err != nil {
			return projectData
		}
		defer rows.Close()
		resultList := []*ProjectEntry{}
		for rows.Next() {
			project := ProjectEntry{}
			ssc := rows.ScanStruct(&project)
			if ssc != nil {
				logx.Error(ssc)
				continue
			}
			resultList = append(resultList, &project)
		}
		projectData.PList = resultList
		projectData.Count = T.NetworkCom.Count(countSql, args...)
		moreInfo(req, plist, projectData.PList) //  补充信息
		redis.Put("newother", rkey, projectData, T.C.CacheTimeOut)
	}
	mmp := MonitorStatus(req.UserId) // 项目监控
	projectData.PList = filterData(req, projectData.PList, nil, mmp, true)
	totalPage := int(math.Ceil(float64(projectData.Count) / float64(req.PageSize)))
	if totalPage > req.PageNum {
		projectData.HasNextPage = true
	} else {
		projectData.HasNextPage = false
	}
	return projectData
}

// @Author jianghan
// @Description 销售机会线索状态
// @Date 2024/4/18
func preSalesStatus(posid int64) (m1 map[string]interface{}) {
	m1 = make(map[string]interface{})
	info := T.CrmMysql.SelectBySql(sql_2, posid)
	if info != nil && len(*info) > 0 {
		for _, m := range *info {
			m1[common.ObjToString(m["relate_id"])] = m
		}
	}
	return m1
}

// 中间人 直接查询可介绍项目
func getIyProList(iy string, positionId int64) (array []string) {
	wh, args := common.WhArgs(strings.Split(iy, ","))
	args = append(args, positionId)
	intermediaryArr := T.CrmMysql.SelectBySql(`select b.relate_id as relate_id,b.relate_name as relate_name from crm.connection a inner join crm.connection_introduce b on ( a.id in (`+wh+`) and  a.position_id=?  and a.id=b.connection_id and b.itype=2)`, args...)
	for _, m := range *intermediaryArr {
		pid := gconv.String(m["relate_id"])
		array = append(array, pid)
	}
	return
}

func getQuerySql(req *types.ProjectListReq, plist []string, businessStr string) (countSql, findSql string, allArgs []interface{}) {
	countSql = "select count(1) from information.transaction_info_all a "
	findSql = "select b.project_id as project_id,b.project_name as project_name,b.business_type as business_type,b.buyer as buyer,b.buyer_id as buyer_id,b.area as area,b.city as city,b.district as district,b.zbtime as zbtime,b.endtime as endtime,b.project_money as project_money,b.info_id as info_id,b.information_id as information_id,b.info_ids as info_ids from information.transaction_info_all a "
	allArgs = []interface{}{}
	ors := []string{}
	// 左侧选中的业主id
	if req.PartyA != "" {
		wh, args := common.WhArgs(strings.Split(req.PartyA, ","))
		ors = append(ors, "a.buyer_id in ("+wh+")")
		allArgs = append(allArgs, args...)
	}
	if len(plist) > 0 {
		wh, args := common.WhArgs(plist)
		ors = append(ors, "a.project_id in ("+wh+")")
		allArgs = append(allArgs, args...)
	}
	winner_ids := []string{}
	if req.Supplier != "" {
		winner_ids = append(winner_ids, strings.Split(req.Supplier, ",")...)
	}
	if req.Heterotophy != "" {
		winner_ids = append(winner_ids, strings.Split(req.Heterotophy, ",")...)
	}
	if len(winner_ids) > 0 {
		wh, args := common.WhArgs(winner_ids)
		ors = append(ors, "hasAny(a.winner_id,["+wh+"])")
		allArgs = append(allArgs, args...)
	}
	if req.Agency != "" {
		wh, args := common.WhArgs(strings.Split(req.Agency, ","))
		ors = append(ors, "a.agency_id in ("+wh+")")
		allArgs = append(allArgs, args...)
	}
	querys := []string{}
	//业务类型
	if businessStr != "" {
		wh, args := common.WhArgs(strings.Split(businessStr, ","))
		querys = append(querys, `hasAny(b.topscopeclass,[`+wh+`])`)
		allArgs = append(allArgs, args...)
	}
	//区域
	if req.Area != "" {
		querys = append(querys, "b.area=?")
		allArgs = append(allArgs, req.Area)
	}
	// 项目金额 0: 全部，1: 50万以下，2: 50-100万, 3: 100-200万, 4: 200-500万, 5:500万以上
	if req.Amount != "" {
		if !strings.Contains(req.Amount, "0") {
			var tempArr []string
			for _, s := range strings.Split(req.Amount, ",") {
				if s == "1" {
					tempArr = append(tempArr, "b.project_money<=?")
					allArgs = append(allArgs, 500000)
				} else if s == "2" {
					tempArr = append(tempArr, "(b.project_money>=? and b.project_money<=?)")
					allArgs = append(allArgs, 500000, 1000000)
				} else if s == "3" {
					tempArr = append(tempArr, "(b.project_money>=? and b.project_money<=?)")
					allArgs = append(allArgs, 1000000, 2000000)
				} else if s == "4" {
					tempArr = append(tempArr, "(b.project_money>=? and b.project_money<=?)")
					allArgs = append(allArgs, 2000000, 5000000)
				} else if s == "5" {
					tempArr = append(tempArr, "b.project_money>=?")
					allArgs = append(allArgs, 5000000)
				}
			}
			if len(tempArr) > 0 {
				querys = append(querys, fmt.Sprintf("(%s)", strings.Join(tempArr, " or ")))
			}
		}
	}
	//过滤掉已中标的招标项目数据
	sqlAppend := "inner join information.transaction_info_all b on((" + strings.Join(ors, " or ") + ") and a.buyer_id=b.buyer_id) where b.project_bidstatus>1 and " + strings.Join(querys, " and ")
	countSql += sqlAppend
	findSql += sqlAppend + " order by b.zbtime desc limit ?,?"
	allArgs = append(allArgs, (req.PageNum-1)*req.PageSize, req.PageSize)
	return
}

// @Author jianghan
// @Description 过滤数据/补充销售机会状态信息，返回分页结果数据
// @Date 2024/4/18
func filterData(req *types.ProjectListReq, resultList []*ProjectEntry, preSales, mmp map[string]interface{}, isSqlPage bool) []*ProjectEntry {
	var newList []*ProjectEntry
	f := make(map[string]int, 1)
	if req.SaleStatus == "1" {
		f["is_handle"] = 0
	} else if req.SaleStatus == "2" {
		f["is_ignore"] = 1
	} else if req.SaleStatus == "3" {
		f["is_create"] = 1
	}
	for _, m := range resultList {
		// 处理/忽略/销售机会
		//if m1, ok := preSales[m.ProjectId].(map[string]interface{}); ok {
		//	m.IsIgnore = common.IntAll(m1["is_handle"])
		//	m.IsIgnore = common.IntAll(m1["is_ignore"])
		//	m.IsCreate = common.IntAll(m1["is_create"])
		//}
		// 监控
		for _, s := range strings.Split(m.InfoId, ",") {
			if mmp[s] != nil {
				m.IsFocus = 1
				m.FocusId = common.ObjToString(mmp[s])
				break
			}
		}
		if !isSqlPage {
			for k, v := range f {
				if k == "is_handle" && m.IsHandle == v {
					newList = append(newList, m)
				} else if k == "is_ignore" && m.IsIgnore == v {
					newList = append(newList, m)
				} else if k == "is_create" && m.IsCreate == v {
					newList = append(newList, m)
				}
			}
		}
	}
	if !isSqlPage {
		if newList == nil {
			resultList = make([]*ProjectEntry, 0)
		} else {
			resultList = newList
		}
	}
	return resultList
}

// @Author jianghan
// @Description 补充人脉 等信息
// @Date 2024/4/17
func moreInfo(req *types.ProjectListReq, plist []string, list []*ProjectEntry) (result []*ProjectEntry) {
	var buyerIds []string
	for _, m := range list {
		if m.BuyerId != "" {
			buyerIds = append(buyerIds, m.BuyerId)
		}
	}
	countMap := make(map[string]int)
	connMap := make(map[string]int)
	if buyerIds != nil && len(buyerIds) > 0 {
		str1, arr1 := common.WhArgs(buyerIds)
		info1, err := T.ClickhouseConn.Query(context.TODO(), fmt.Sprintf(sql_1, str1), arr1...)
		if err == nil {
			for info1.Next() {
				var buyerId string
				var count uint64
				_ = info1.Scan(&buyerId, &count)
				countMap[buyerId] = int(count)
			}
		}
		info2 := T.CrmMysql.SelectBySql(fmt.Sprintf(sql_3, str1), arr1...)
		if info2 != nil && len(*info2) > 0 {
			for _, m := range *info2 {
				if req.PositionId == common.Int64All(m["position_id"]) {
					connMap[common.ObjToString(m["company_id"])] = 1 // 我的人脉
				} else {
					connMap[common.ObjToString(m["company_id"])] = 2
				}
			}
		}
	}
	for _, m := range list {
		// 中间人的项目 默认 我的人脉
		if len(plist) > 0 {
			for _, pid := range plist {
				if m.ProjectId == pid {
					m.MyConn = true
					m.ConnType = 1
					break
				}
			}
		}
		// 补充跳转链接
		//if m.BusinessType == "采购意向" || m.BusinessType == "招标项目" {
		m.Href = fmt.Sprintf("/article/content/%s.html", encrypt.CommonEncodeArticle("content", m.InfoId))
		//}
		m.ProjectId = util.EncodeId(m.ProjectId)
		// 人脉、人脉所在单位项目 conn_type: 1 人脉可转介绍项目; conn_type: 2 人脉所在单位项目
		if !m.MyConn {
			if connMap[m.BuyerId] == 1 {
				m.MyConn = true // 我的人脉
				m.ConnType = 1
			} else {
				m.MyConn = false
			}
		}
		if m.ConnType == 0 {
			if connMap[m.BuyerId] != 0 {
				m.ConnType = 1
			} else {
				m.ConnType = 2
			}
		}
		// 转介绍成功率高标签
		if countMap[m.BuyerId] > 2 {
			m.HighSuccess = true
		} else {
			m.HighSuccess = false
		}
	}
	// 人脉路径
	var bArr []string
	for _, m := range list {
		// 有我的人脉标签时不需要查询人脉路径信息
		if m.MyConn == false && m.BuyerId != "" {
			bArr = append(bArr, fmt.Sprintf("'%s'", m.BuyerId))
		}
	}
	companyList := Findfirstparty(bArr, nil)
	if companyList != nil && len(companyList) > 0 {
		for _, m := range list {
			if m.MyConn == false {
				for _, m1 := range companyList {
					if m.BuyerId == common.ObjToString(m1["a_id"]) {
						m.BId = common.ObjToString(m1["b_id"])
						m.BName = common.ObjToString(m1["b_name"])
						m.RelationShip = common.ObjToString(m1["relationship"])
						m.SourceType = common.ObjToString(m1["sourceType"])
						m.Person = common.ObjToString(m1["person"])
						m.Num = common.IntAll(m1["count"])
						break
					}
				}
			}
		}
	} else {
		companyList = Findwinner(bArr)
		if companyList != nil && len(companyList) > 0 {
			for _, m := range list {
				if m.MyConn == false {
					for _, m1 := range companyList {
						if m.BuyerId == common.ObjToString(m1["a_id"]) {
							m.BId = common.ObjToString(m1["b_id"])
							m.BName = common.ObjToString(m1["b_name"])
							m.RelationShip = common.ObjToString(m1["relationship"])
							m.SourceType = common.ObjToString(m1["sourceType"])
							m.Person = common.ObjToString(m1["person"])
							m.Num = common.IntAll(m1["count"])
							break
						}
					}
				}
			}
		}
	}
	return list
}

func MonitorStatus(uid string) map[string]interface{} {
	m1 := make(map[string]interface{})
	info := T.BaseMysql.SelectBySql(sql_4, uid)
	for _, m := range *info {
		m1[common.ObjToString(m["s_id"])] = util.EncodeId(strconv.Itoa(common.IntAll(m["id"])))
	}
	return m1
}