application/api/internal/service/CoopHistoryService.go

package service

import (
	"app.yhyue.com/moapp/jybase/common"
	elastic "app.yhyue.com/moapp/jybase/es"
	T "bp.jydev.jianyu360.cn/CRM/application/api/common"
	"bp.jydev.jianyu360.cn/CRM/application/api/internal/types"
	"context"
	"encoding/json"
	"fmt"
	"github.com/zeromicro/go-zero/core/logx"
	"time"
)

var (
	INDEX_1   = "transaction_info"
	sql_2_0   = `SELECT buyer, buyer_id, agency, agency_id, property_form FROM information.transaction_info WHERE project_id = ?`
	sql_2_1   = `SELECT project_id, project_name, zbtime FROM information.transaction_info WHERE buyer_id = ? AND has(winner_id, ?) ORDER BY zbtime DESC`
	sql_2_2   = `select DISTINCT  b.company_id,b.company_name,a.relate_id,a.relate_name,b.contact_person   from  connection_introduce a  INNER JOIN connection b on  a.position_id= ? and a.connection_id=b.id  and   a.relate_Id = ? and  a.itype =1  and  b.itype=4 and  b.status=1 `
	sql_2_3   = `select a.a_id as a_id, a.b_id as b_id, a.a_name as a_name, a.b_name as b_name, a.code as code, c.legal_person as c_person, d.legal_person d_person from crm.ent_map_code a left join ent_info c on (a.a_id = ? or a.b_id = ? ) and a.code in('0101', '0201') and a.a_id = c.id left join ent_info d on (a.a_id = ? or a.b_id = ? ) and a.code in('0101', '0201') and a.b_id = d.id where (a.a_id = ? or a.b_id = ? ) and a.code in('0101', '0201')`
	es_query  = `{"query": {"bool": {"must": [{"term": {"buyer": "%s"}}],"must_not": {"term": {"property_form": "%s"}}}},"aggs": {"winner_count": {"terms": {"field": "winner","size": 1000,"order": {"_count": "desc"}},"aggs": {"amount_all": {"sum": {"field": "bidamount"}}}}},"size": 1}`
	es_query1 = `{"query": {"bool": {"must": [{"term": {"buyer": "%s"}}]}},"aggs": {"agency_count": {"terms": {"field": "agency","size": 1000,"order": {"_count": "desc"}},"aggs": {"amount_all": {"sum": {"field": "bidamount"}}}}},"size": 1}`
)

type ResultData struct {
	SourceType   string  `json:"SourceType"`
	EntName      string  `json:"EntName"`
	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"` // 关系
}

type ProjectTmp struct {
	Buyer        string   `ch:"buyer"`
	BuyerId      string   `ch:"buyer_id"`
	Agency       string   `ch:"agency"`
	AgencyId     string   `ch:"agency_id"`
	PropertyForm []string `ch:"property_form"`
}

func GetPrList(req *types.CoopHistoryReq) (result []*ResultData) {
	pTmp := ProjectTmp{}
	err := T.ClickhouseConn.QueryRow(context.TODO(), sql_2_0, req.Pid).ScanStruct(&pTmp)
	if err != nil {
		return nil
	}
	propertyForm := ""
	m1 := T.CrmMysql.FindOne("config_tenant", map[string]interface{}{"account_id": req.EntAccountId}, "probusfor", "")
	if m1 != nil && len(*m1) > 0 {
		propertyForm = common.ObjToString((*m1)["probusfor"])
	}

	// firstparty：甲方 supplier：供应商 adiffb：同甲异业 middleman：中间人 agency：招标代理机构 sup_sub: 上下级
	// 1、同甲异业数据/招标代理机构渠道
	r1, r2 := GetData(propertyForm, pTmp.BuyerId)
	if r1 != nil && len(r1) > 0 {
		for _, m := range r1 {
			near, zbtime := LastTimeCoop(pTmp.BuyerId, common.ObjToString(m["name"]), "adiffb")
			tmp := ResultData{
				SourceType:  "adiffb",
				EntName:     common.ObjToString(m["name"]),
				ProjectNum:  common.IntAll(m["coop_size"]),
				TotalAmount: common.Float64All(m["coop_amount"]),
				RecentTime:  zbtime,
				NearlyYears: near,
			}
			result = append(result, &tmp)
		}
	}
	if r2 != nil && len(r2) > 0 {
		for _, m := range r2 {
			near, zbtime := LastTimeCoop(pTmp.BuyerId, common.ObjToString(m["name"]), "agency")
			tmp := ResultData{
				SourceType:  "agency",
				EntName:     common.ObjToString(m["name"]),
				ProjectNum:  common.IntAll(m["coop_size"]),
				TotalAmount: common.Float64All(m["coop_amount"]),
				RecentTime:  zbtime,
				NearlyYears: near,
			}
			result = append(result, &tmp)
		}
	}
	if pTmp.BuyerId != "" {
		// 中间人可介绍业主
		var r3 []map[string]interface{}
		r3 = FindMiddleman([]string{pTmp.BuyerId}, req.PositionId, r3)
		if r3 != nil && len(r3) > 0 {
			for _, m := range r3 {
				tmp := ResultData{
					SourceType:   "middleman",
					EntName:      common.ObjToString(m["b_name"]),
					EntPerson:    common.ObjToString(m["personName"]),
					Relationship: "业主的关系人",
				}
				result = append(result, &tmp)
			}
		}
		// 关联单位
		var r4 []map[string]interface{}
		r4 = Findfirstparty([]string{pTmp.BuyerId}, r4)
		if r4 != nil && len(r4) > 0 {
			for _, m := range r3 {
				tmp := ResultData{
					SourceType:   "sup_sub",
					EntName:      common.ObjToString(m["b_name"]),
					EntPerson:    common.ObjToString(m["personName"]),
					Relationship: common.ObjToString(m["relationship"]),
				}
				result = append(result, &tmp)
			}
		}
	}
	return
}

func GetData(propertyForm, bid string) (result1, result2 []map[string]interface{}) {
	type AggStruct struct {
		Buckets []struct {
			Key        string `json:"key,omitempty"`
			Doc_count  int64  `json:"doc_count,omitempty"`
			Amount_all struct {
				Value float64 `json:"value,omitempty"`
			} `json:"amount_all"`
		} `json:"buckets"`
	}
	m1 := make(map[string]interface{}) //采购单位-中标单位
	m2 := make(map[string]interface{}) //采购单位-代理机构
	if propertyForm != "" {
		//同甲异态 中标企业
		aggs, count, res := elastic.GetAggs("bidding", "bidding", fmt.Sprintf(es_query, propertyForm, "中国计量大学"))
		logx.Info("es聚合查询结果:", aggs, count, res)
		var m1Buckets = AggStruct{}
		if aggs != nil && aggs["winner_count"] != nil {
			bs, err := aggs["winner_count"].MarshalJSON()
			if err != nil {
				logx.Info(err)
			} else {
				if len(bs) == 0 {
					logx.Info(err)
				} else {
					err := json.Unmarshal(bs, &m1Buckets)
					logx.Info(err)
					if len(m1Buckets.Buckets) > 0 {
						for _, v := range m1Buckets.Buckets {
							m1[v.Key] = map[string]interface{}{"name": v.Key, "amount": v.Amount_all.Value, "count": v.Doc_count}
						}
					}
				}
			}
		}
		for k, v := range m1 {
			v1 := v.(map[string]interface{})
			tmp := make(map[string]interface{})
			tmp["name"] = k
			tmp["coop_size"] = v1["count"]
			tmp["coop_amount"] = v1["amount"]
			result1 = append(result1, tmp)
		}
	}
	//代理机构
	aggs1, count1, res1 := elastic.GetAggs("bidding", "bidding", fmt.Sprintf(es_query1, "中国计量大学"))
	logx.Info("es聚合查询结果:", aggs1, count1, res1)
	var m2Buckets = AggStruct{}
	if aggs1 != nil && aggs1["agency_count"] != nil {
		bs, err := aggs1["agency_count"].MarshalJSON()
		if err != nil {
			logx.Info(err)
		} else {
			if len(bs) == 0 {
				logx.Info(err)
			} else {
				err = json.Unmarshal(bs, &m2Buckets)
				logx.Info(err)
				if len(m2Buckets.Buckets) > 0 {
					for _, v := range m2Buckets.Buckets {
						m2[v.Key] = map[string]interface{}{"name": v.Key, "amount": v.Amount_all.Value, "count": v.Doc_count}
					}
				}
			}
		}
	}
	for k, v := range m2 {
		v1 := v.(map[string]interface{})
		tmp := make(map[string]interface{})
		tmp["name"] = k
		tmp["coop_size"] = v1["count"]
		tmp["coop_amount"] = v1["amount"]
		result2 = append(result2, tmp)
	}
	return
}

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

// @Author jianghan
// @Description 合作历史
// @Date 2024/4/20
func GetData3(buyerId, winnerId string) (result []*P_History) {
	rows, _ := T.ClickhouseConn.Query(context.TODO(), sql_2_1, buyerId, winnerId)
	for rows.Next() {
		pHis := P_History{}
		_ = rows.ScanStruct(&pHis)
		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 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 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
}