networkManage/api/internal/service/plistService.go

package service

import (
	"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"
	"context"
	"encoding/json"
	"fmt"
	"github.com/gogf/gf/v2/util/gconv"
	"github.com/shopspring/decimal"
	"github.com/zeromicro/go-zero/core/logx"
	"strconv"
	"strings"
)

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 = ?`
)

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) (resultList []*ProjectEntry, hasNextPage bool, total int) {
	buyerM := BuyerList(req.PartyA, req.Supplier, req.Heterotophy, "", req.Agency, req.PositionId)
	var plist []string
	if req.Intermediary != "" {
		plist = getIyProList(req.Intermediary, req.PositionId)
	}
	if len(*buyerM) <= 0 && len(plist) <= 0 {
		return []*ProjectEntry{}, false, 0
	}
	mmp := MonitorStatus(req.UserId) // 项目监控
	var buyerArr []string
	for b := range *buyerM {
		buyerArr = append(buyerArr, b)
	}
	//preSales := preSalesStatus(req.PositionId)
	//isSqlPage := true
	//if req.SaleStatus == "0" {
	//	isSqlPage = true // 是否sql分页
	//}
	countSql, findSql := getQuerySql(req, true, buyerArr, plist)
	// 缓存
	rkey := "projectList-" + common.GetMd5String(fmt.Sprintf("%+v", req))
	if ret, err := redis.GetBytes("newother", rkey); err == nil {
		err = json.Unmarshal(*ret, &resultList)
	} else {
		logx.Info("findSql: ", findSql)
		logx.Info("countSql: ", countSql)
		rows, err := T.ClickhouseConn.Query(context.TODO(), findSql)
		defer rows.Close()
		if err != nil {
			return nil, false, 0
		}
		for rows.Next() {
			project := ProjectEntry{}
			_ = rows.ScanStruct(&project)
			resultList = append(resultList, &project)
		}
		redis.Put("newother", rkey, resultList, T.C.CacheTimeOut)
	}

	resultList = filterData(req, resultList, nil, mmp, true)
	//if !isSqlPage {
	//total = len(resultList)
	//if total > req.PageSize {
	//	hasNextPage = true
	//	start := (req.PageNum - 1) * req.PageSize
	//	end := req.PageNum * req.PageSize
	//	if end > total {
	//		end = total
	//		hasNextPage = false
	//	}
	//	resultList = resultList[start:end]
	//} else {
	//	hasNextPage = false
	//}
	//} else {
	total = int(T.NetworkCom.Count(countSql))
	if total > req.PageSize {
		hasNextPage = true
	} else {
		hasNextPage = false
	}
	//}
	moreInfo(req, plist, resultList) //  补充信息

	return
}

// @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, isPage bool, buyerArr, plist []string) (countSql, findSql string) {
	querys := []string{}
	// 左侧选中的业主id
	if len(buyerArr) > 0 || len(plist) > 0 {
		if len(buyerArr) > 0 && len(plist) > 0 {
			var arr []string
			for _, s := range buyerArr {
				arr = append(arr, fmt.Sprintf("'%s'", s))
			}
			var arr1 []string
			for _, s := range plist {
				arr1 = append(arr1, fmt.Sprintf("'%s'", s))
			}
			querys = append(querys, fmt.Sprintf(" (a.buyer_id in (%s) or a.project_id in (%s)) ", strings.Join(arr, ","), strings.Join(arr1, ",")))
		} else if len(buyerArr) > 0 {
			var arr []string
			for _, s := range buyerArr {
				arr = append(arr, fmt.Sprintf("'%s'", s))
			}
			querys = append(querys, fmt.Sprintf(" a.buyer_id in (%s) ", strings.Join(arr, ",")))
		} else if len(plist) > 0 {
			var arr1 []string
			for _, s := range plist {
				arr1 = append(arr1, fmt.Sprintf("'%s'", s))
			}
			querys = append(querys, fmt.Sprintf(" a.project_id in (%s) ", strings.Join(arr1, ",")))
		}
	}
	//	商机类型
	if req.BusinessType != "" && req.BusinessType != "全部" {
		querys = append(querys, fmt.Sprintf(" a.business_type in ('%s') ", strings.Join(strings.Split(req.BusinessType, ","), "', '")))
	}
	if req.ProjectName != "" {
		querys = append(querys, " a.project_name like '%"+req.ProjectName+"%'")
	}
	if req.StartTime > 0 && req.EntTime > 0 {
		st := req.StartTime + 90*24*60*60
		et := req.EntTime + 90*24*60*60
		querys = append(querys, fmt.Sprintf("  a.endtime>=%d and a.endtime<=%d", st, et))
	} else if req.StartTime > 0 && req.EntTime == 0 {
		st := req.StartTime + 90*24*60*60
		querys = append(querys, fmt.Sprintf(" a.endtime>=%d", st))
	} else if req.StartTime == 0 && req.EntTime > 0 {
		et := req.EntTime + 90*24*60*60
		querys = append(querys, fmt.Sprintf(" a.endtime<=%d", et))
	}
	var regionArr = []string{}
	if req.Area != "" || req.City != "" || req.District != "" {
		//城市
		if req.City != "" {
			regionArr = append(regionArr, fmt.Sprintf(" a.city in ('%s') ", req.City))
		}
		//区域
		if req.Area != "" {
			regionArr = append(regionArr, fmt.Sprintf(" a.area in ('%s') ", req.Area))
		}
		//区域
		district := []string{}
		if req.District != "" {
			for _, v := range strings.Split(req.District, ",") {
				//cityName := strings.Split(v, "_")[0]
				districtName := strings.Split(v, "_")[1]
				district = append(district, districtName)
			}
		}
		if len(district) > 0 {
			regionArr = append(regionArr, fmt.Sprintf(" a.district in ('%s') ", strings.Join(district, ",")))
		}
		if len(regionArr) > 0 {
			querys = append(querys, fmt.Sprintf("(%s)", strings.Join(regionArr, "or")))
		}
	}
	if req.SubClass != "" {
		arr := []string{}
		for _, v := range strings.Split(req.SubClass, ",") {
			arr = append(arr, fmt.Sprintf("has(a.subclass, '%s')", v))
		}
		querys = append(querys, arr...)
	}
	// 项目金额 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, fmt.Sprintf("(a.project_money<=%d)", 500000))
				} else if s == "2" {
					tempArr = append(tempArr, fmt.Sprintf("((a.project_money>=%d and a.project_money<=%d))", 500000, 1000000))
				} else if s == "3" {
					tempArr = append(tempArr, fmt.Sprintf("((a.project_money>=%d and a.project_money<=%d))", 1000000, 2000000))
				} else if s == "4" {
					tempArr = append(tempArr, fmt.Sprintf("((a.project_money>=%d and a.project_money<=%d))", 2000000, 5000000))
				} else if s == "5" {
					tempArr = append(tempArr, fmt.Sprintf("((a.project_money>=%d))", 5000000))
				}
			}
			if len(tempArr) > 0 {
				querys = append(querys, fmt.Sprintf("(%s)", strings.Join(tempArr, "or")))
			}
		}
	}
	//物业业态
	if req.PropertyForm != "" {
		arr := []string{}
		for _, v := range strings.Split(req.PropertyForm, ",") {
			arr = append(arr, fmt.Sprintf("has(a.property_form, '%s')", v))
		}
		querys = append(querys, arr...)
	}
	//过滤掉已中标的招标项目数据
	querys = append(querys, fmt.Sprintf(" a.project_bidstatus in (%s) ", "2,3,4"))
	findSql = "select a.project_id, a.project_name, a.business_type, a.buyer, a.buyer_id, a.area, a.city, a.district, a.zbtime, a.endtime, a.project_money, a.info_id, a.information_id, a.info_ids "
	if len(querys) > 0 {
		countSql = fmt.Sprintf("select count(1) from %s a where %s ", "information.transaction_info_all", strings.Join(querys, " and "))
		findSql = fmt.Sprintf("%s from %s  a  where  %s order by zbtime desc", findSql, "information.transaction_info_all", strings.Join(querys, " and "))
	} else {
		countSql = fmt.Sprintf("select count(1) from %s a ", "information.transaction_info_all")
		findSql = fmt.Sprintf("%s from %s a order by zbtime", findSql, "information.transaction_info_all")
	}
	if isPage {
		findSql += fmt.Sprintf(" limit %d,%d", (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
}