networkManage/api/internal/service/CoopHistoryService.go

package service

import (
	"context"
	"encoding/json"
	"fmt"
	"strings"
	"time"

	"app.yhyue.com/moapp/jybase/common"
	"app.yhyue.com/moapp/jybase/encrypt"
	"app.yhyue.com/moapp/jybase/redis"
	T "bp.jydev.jianyu360.cn/CRM/networkManage/api/common"
	"bp.jydev.jianyu360.cn/CRM/networkManage/api/internal/types"
	"github.com/ClickHouse/clickhouse-go/v2/lib/driver"
	"github.com/shopspring/decimal"
	"github.com/zeromicro/go-zero/core/logx"
)

const (
	NetworkManageCoopHistory = "networkManage_coopHistory_%d_%s"
)

var (
	INDEX_1   = "transaction_info_all"
	sql_2_0   = `SELECT buyer_id FROM information.transaction_info_all WHERE project_id = ?`
	sql_2_2   = `select id as buyer_id from ent_info where company_name=? order by id desc limit 1`
	sql_2_1   = `SELECT project_id, project_name, zbtime FROM information.transaction_info_all WHERE buyer_id = ? AND has(winner, ?) ORDER BY zbtime DESC`
	sql_2_1_1 = `SELECT project_id, project_name, zbtime FROM information.transaction_info_all WHERE buyer_id = ? AND agency = ? ORDER BY zbtime DESC`
)

type ResultData struct {
	SourceType   string  `json:"SourceType"`
	EntName      string  `json:"EntName"`
	EntId        string  `json:"EntId"`
	EntPerson    string  `json:"EntPerson"`
	Middleman    string  `json:"Middleman"`
	ProjectNum   int     `json:"ProjectNum"`
	TotalAmount  float64 `json:"TotalAmount"`
	RecentTime   int64   `json:"RecentTime"`
	NearlyYears  bool    `json:"NearlyYears"`
	SupEnt       string  `json:"SupEnt"`       // 上级
	NextEnt      string  `json:"NextEnt"`      // 下级
	Relationship string  `json:"Relationship"` // 关系
	BuyerId      string  `json:"BuyerId"`
	IsIgnore     bool    `json:"isIgnore"`
}

type ResultDatas []*ResultData

func (n *ResultDatas) Len() int {
	return len(*n)
}

func (n *ResultDatas) Less(i, j int) bool {
	if (*n)[i].RecentTime == (*n)[j].RecentTime {
		return (*n)[i].EntName < (*n)[j].EntName
	}
	return (*n)[i].RecentTime > (*n)[j].RecentTime
}

func (n *ResultDatas) Swap(i, j int) {
	(*n)[i], (*n)[j] = (*n)[j], (*n)[i]
}

type ProjectTmp struct {
	BuyerId string `ch:"buyer_id"`
}

type Cooperate struct {
	Near   bool
	ZbTime int64
}

type AggStruct struct {
	Id           string          `ch:"id"`
	Name         string          `ch:"name"`
	Sum          uint64          `ch:"sum"`
	ProjectMoney decimal.Decimal `ch:"project_money"`
	Zbtime       int64           `ch:"zbtime"`
}
type PrListRes struct {
	Result []*ResultData
	Size_1 int64
	Size_2 int64
	Size_3 int64
	Size_4 int64
}

func GetPrList(req *types.CoopHistoryReq) *PrListRes {
	redisFlag := ""
	if req.Buyer != "" {
		redisFlag = req.Buyer
	} else if req.Pid != "" {
		redisFlag = req.Pid
	}
	redisKey := fmt.Sprintf(NetworkManageCoopHistory, req.PositionId, req.ChannelType+"_"+redisFlag)
	prListRes := &PrListRes{}
	if rBt, rErr := redis.GetNewBytes("newother", redisKey); rErr == nil && rBt != nil {
		json.Unmarshal(*rBt, &prListRes)
		return prListRes
	}
	pTmp := ProjectTmp{}
	var err error
	if req.Buyer != "" {
		err = T.ClickhouseConn.QueryRow(context.TODO(), sql_2_2, req.Buyer).ScanStruct(&pTmp)
	} else if req.Pid != "" {
		err = T.ClickhouseConn.QueryRow(context.TODO(), sql_2_0, req.Pid).ScanStruct(&pTmp)
	} else {
		return prListRes
	}
	if err != nil || pTmp.BuyerId == "" {
		return prListRes
	}
	// firstparty：甲方 supplier：供应商 adiffb：同甲异业 middleman：中间人 agency：招标代理机构 sup_sub: 上下级
	// 1、同甲异业数据/ 3、招标代理机构渠道
	// 中间人可介绍业主
	var r3 []map[string]interface{}
	r3 = FindMiddleman([]string{pTmp.BuyerId}, req.PositionId, r3)
	prListRes.Size_4 = int64(len(r3))
	if req.ChannelType == "0" || req.ChannelType == "4" {
		for _, m := range r3 {
			tmp := ResultData{
				SourceType:   "middleman",
				EntName:      common.ObjToString(m["b_name"]),
				EntId:        common.ObjToString(m["b_id"]),
				EntPerson:    common.ObjToString(m["personName"]),
				Relationship: "业主的关系人",
				BuyerId:      pTmp.BuyerId,
			}
			prListRes.Result = append(prListRes.Result, &tmp)
		}
	}
	// 关联单位
	var r4 []map[string]interface{}
	r4 = Findfirstparty([]string{pTmp.BuyerId}, r4)
	prListRes.Size_2 = int64(len(r4))
	if req.ChannelType == "0" || req.ChannelType == "2" {
		for _, m := range r4 {
			tmp := ResultData{
				SourceType:   "sup_sub",
				EntName:      common.ObjToString(m["b_name"]),
				EntId:        common.ObjToString(m["b_id"]),
				EntPerson:    common.ObjToString(m["personName"]),
				Relationship: common.ObjToString(m["relationship"]),
				BuyerId:      pTmp.BuyerId,
			}
			prListRes.Result = append(prListRes.Result, &tmp)
		}
	}
	scopeClass := FindBusiness(req.EntId, req.EntUserId)
	r1 := GetWinnerData(scopeClass, pTmp.BuyerId)
	prListRes.Size_1 = int64(len(r1))
	if req.ChannelType == "0" || req.ChannelType == "1" {
		for _, m := range r1 {
			tmp := ResultData{
				SourceType: "adiffb",
				EntName:    m.Name,
				ProjectNum: int(m.Sum),
				BuyerId:    pTmp.BuyerId,
				RecentTime: m.Zbtime,
			}
			tmp.TotalAmount, _ = m.ProjectMoney.Float64()
			if time.Now().AddDate(-3, 0, 0).Unix() <= tmp.RecentTime {
				tmp.NearlyYears = true
			}
			prListRes.Result = append(prListRes.Result, &tmp)
		}
	}
	r2 := GetAgencyData(pTmp.BuyerId)
	prListRes.Size_3 = int64(len(r2))
	if req.ChannelType == "0" || req.ChannelType == "3" {
		for _, m := range r2 {
			tmp := ResultData{
				SourceType: "agency",
				EntName:    m.Name,
				ProjectNum: int(m.Sum),
				BuyerId:    pTmp.BuyerId,
				RecentTime: m.Zbtime,
			}
			tmp.TotalAmount, _ = m.ProjectMoney.Float64()
			if time.Now().AddDate(-3, 0, 0).Unix() <= tmp.RecentTime {
				tmp.NearlyYears = true
			}
			prListRes.Result = append(prListRes.Result, &tmp)
		}
	}
	redis.Put("newother", redisKey, prListRes, T.NetworkCom.CacheTimeout())
	return prListRes
}
func GetWinnerData(scopeClass string, bid string) []AggStruct {
	ass := []AggStruct{}
	if scopeClass == "" {
		return ass
	}
	args := []interface{}{bid, bid}
	wh, newArgs := common.WhArgs(strings.Split(scopeClass, ","))
	args = append(args, newArgs...)
	rows, err1 := T.ClickhouseConn.Query(context.TODO(), `select distinct winner as name,winner_id as id from information.transaction_info_all ARRAY JOIN winner_id,winner
			WHERE buyer_id IN (SELECT buyer_id from information.transaction_info_all WHERE has(winner_id,?) and buyer_id<>'') and winner_id<>? AND hasAny(topscopeclass,[`+wh+`])=0`, args...)
	if err1 != nil {
		logx.Error(err1)
		return ass
	}
	winnerIds := []string{}
	for rows.Next() {
		as := AggStruct{}
		if err := rows.ScanStruct(&as); err != nil {
			logx.Error(err)
			continue
		}
		winnerIds = append(winnerIds, as.Id)
		ass = append(ass, as)
	}
	rows.Close()
	if err := rows.Err(); err != nil {
		logx.Error(err)
	}
	hz := GetWinnerDataHz(bid, winnerIds)
	for _, v := range ass {
		if _, ok := hz[v.Id]; !ok {
			continue
		}
		v.ProjectMoney = hz[v.Id].ProjectMoney
		v.Sum = hz[v.Id].Sum
		v.Zbtime = hz[v.Id].Zbtime
	}
	return ass
}

//
func GetWinnerDataHz(buyerId string, winnerIds []string) map[string]AggStruct {
	res := map[string]AggStruct{}
	if len(winnerIds) == 0 {
		return res
	}
	args := []interface{}{buyerId}
	wh, newArgs := common.WhArgs(winnerIds)
	args = append(args, newArgs...)
	rows, err1 := T.ClickhouseConn.Query(context.TODO(), `select winner_id as id,sum(1) as sum,sum(project_money) as project_money,max(zbtime) as zbtime from information.transaction_info_all ARRAY JOIN winner_id
			WHERE winner_id in(`+wh+`) and buyer_id=? group by winner_id`, args...)
	if err1 != nil {
		logx.Error(err1)
		return res
	}
	for rows.Next() {
		as := AggStruct{}
		if err := rows.ScanStruct(&as); err != nil {
			logx.Error(err)
			continue
		}
		res[as.Id] = as
	}
	rows.Close()
	if err := rows.Err(); err != nil {
		logx.Error(err)
	}
	return res
}

//代理机构
func GetAgencyData(bid string) []AggStruct {
	ass := []AggStruct{}
	rows, err1 := T.ClickhouseConn.Query(context.TODO(), `select agency as name,agency_id as id,sum(1) as sum,sum(project_money) as project_money,max(zbtime) as zbtime from information.transaction_info_all where buyer_id=? and agency<>'' and agency_id<>'' GROUP by agency,agency_id`, bid)
	if err1 != nil {
		logx.Error(err1)
		return ass
	}
	for rows.Next() {
		as := AggStruct{}
		if err := rows.ScanStruct(&as); err != nil {
			logx.Error(err)
			continue
		}
		ass = append(ass, as)
	}
	rows.Close()
	if err := rows.Err(); err != nil {
		logx.Error(err)
	}
	return ass
}

type P_History struct {
	ProjectId   string `ch:"project_id"`
	ProjectName string `ch:"project_name"`
	ZbTime      int64  `ch:"zbtime"`
	Href        string `ch:"href"`
}

// @Author jianghan
// @Description 合作历史
// @Date 2024/4/20
func GetData3(source string, buyerId, winnerId string) (result []*P_History) {
	var rows driver.Rows
	if source == "1" {
		rows, _ = T.ClickhouseConn.Query(context.TODO(), sql_2_1_1, buyerId, winnerId)
	} else if source == "2" {
		rows, _ = T.ClickhouseConn.Query(context.TODO(), sql_2_1, buyerId, winnerId)
	}
	for rows.Next() {
		pHis := P_History{}
		_ = rows.ScanStruct(&pHis)
		pHis.Href = fmt.Sprintf("/article/content/%s.html", encrypt.CommonEncodeArticle("content", pHis.ProjectId))
		result = append(result, &pHis)
	}
	return
}

// @Author jianghan
// @Description 上次合作时间
// @Date 2024/4/24
func LastTimeCoop(buyerId, ent, stype string) (bool, int64) {
	zbtime := int64(0)
	near := false
	if stype == "adiffb" {
		sql := `SELECT zbtime FROM information.transaction_info_all WHERE buyer_id = ? AND has(winner, ?) ORDER BY zbtime DESC LIMIT 1`
		_ = T.ClickhouseConn.QueryRow(context.TODO(), sql, buyerId, ent).Scan(&zbtime)
	} else if stype == "agency" {
		sql := `SELECT zbtime FROM information.transaction_info_all WHERE buyer_id = ? AND agency = ? ORDER BY zbtime DESC LIMIT 1`
		_ = T.ClickhouseConn.QueryRow(context.TODO(), sql, buyerId, ent).Scan(&zbtime)
	}
	timestamp := time.Now().AddDate(-3, 0, 0).Unix()
	if timestamp <= zbtime {
		near = true
	}
	return near, zbtime
}

func LastTimeCoopBath(positionId int64, buyerId string, winners, agencys []string) (map[string]*Cooperate, map[string]*Cooperate) {
	adiffb, agency := map[string]*Cooperate{}, map[string]*Cooperate{}
	if buyerId == "" {
		return adiffb, agency
	}
	var toSearch = func(tp int, query string, ws []string) {
		logx.Info("LastTimeCoopBath once start ", positionId)
		defer logx.Info("LastTimeCoopBath once over ", positionId)
		wh, newArgs := common.WhArgs(ws)
		query = fmt.Sprintf(query, wh)
		args := []interface{}{buyerId}
		args = append(args, newArgs...)
		rows, err := T.ClickhouseConn.Query(context.Background(), query, args...)
		if err != nil {
			logx.Error(err)
			return
		}
		for rows.Next() {
			var (
				one    string
				zbtime int64
			)
			if err := rows.Scan(&one, &zbtime); err != nil {
				logx.Error(err)
				continue
			}
			c := &Cooperate{
				ZbTime: zbtime,
			}
			timestamp := time.Now().AddDate(-3, 0, 0).Unix()
			if timestamp <= c.ZbTime {
				c.Near = true
			}
			if tp == 1 {
				adiffb[one] = c
			} else {
				agency[one] = c
			}
		}
		rows.Close()
		if err := rows.Err(); err != nil {
			logx.Error(err)
		}
	}
	if len(winners) > 0 {
		key := fmt.Sprintf("networkManage_lastTimeCoop_winner_%s", common.GetMd5String(buyerId+"_"+strings.Join(winners, ",")))
		wb, err := redis.GetBytes("newother", key)
		if err == nil {
			json.Unmarshal(*wb, &adiffb)
		} else {
			sql := `SELECT winner_one,max(zbtime) FROM information.transaction_info_all ARRAY JOIN winner AS winner_one WHERE buyer_id=? AND hasAny(winner,[%s])) group by winner_one`
			wns := []string{}
			for _, v := range winners {
				wns = append(wns, v)
				if len(wns) == 50 {
					toSearch(1, sql, wns)
					wns = []string{}
				}
			}
			if len(wns) > 0 {
				toSearch(1, sql, wns)
			}
			redis.Put("newother", key, adiffb, T.NetworkCom.CacheTimeout())
		}
	}
	if len(agencys) > 0 {
		key := fmt.Sprintf("networkManage_lastTimeCoop_agency_%s", common.GetMd5String(buyerId+"_"+strings.Join(agencys, ",")))
		wb, err := redis.GetBytes("newother", key)
		if err == nil {
			json.Unmarshal(*wb, &agency)
		} else {
			sql := `SELECT agency,max(zbtime) FROM information.transaction_info_all WHERE buyer_id=? AND agency in (%s) group by agency`
			acs := []string{}
			for _, v := range agencys {
				acs = append(acs, v)
				if len(acs) == 50 {
					toSearch(2, sql, acs)
					acs = []string{}
				}
			}
			if len(acs) > 0 {
				toSearch(2, sql, acs)
			}
			redis.Put("newother", key, agency, T.NetworkCom.CacheTimeout())
		}
	}
	return adiffb, agency
}