application/api/internal/service/plistService.go

package service

import (
	"app.yhyue.com/moapp/jybase/common"
	P "app.yhyue.com/moapp/jybase/mapping"
	T "bp.jydev.jianyu360.cn/CRM/application/api/common"
	"bp.jydev.jianyu360.cn/CRM/application/api/internal/types"
	"context"
	"fmt"
	"github.com/shopspring/decimal"
	"strings"
)

const (
	sql_1 = `SELECT buyer_id, count(1) as count FROM information.transaction_info 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`
)

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"`
	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          string          `json:"Num"`
}

func GetProjectList(req *types.ProjectListReq) (resultList []*ProjectEntry, hasNextPage bool, total int) {

	buyerM := BuyerList(req.PartyA, req.Supplier, req.Heterotophy, req.Intermediary, req.Agency)
	MonitorStatusInit(req.PositionId, buyerM, "0")
	buyerArr := make([]string, len(*buyerM))
	for b := range *buyerM {
		buyerArr = append(buyerArr, b)
	}

	preSales := preSalesStatus(req.PositionId)
	isSqlPage := false
	if req.SaleStatus == "0" {
		isSqlPage = true // 是否sql分页
	}
	countSql, findSql := getQuerySql(req, isSqlPage, buyerArr)
	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)
	}

	resultList = filterData(req, resultList, preSales, isSqlPage)
	if !isSqlPage {
		total = len(resultList)
		if total > req.PageSize {
			start := (req.PageNum - 1) * req.PageSize
			end := start + req.PageSize
			if req.PageNum > 1 {
				resultList = resultList[start:end]
			} else {
				resultList = resultList[:req.PageSize]
			}
		}
	} else {
		total = int(T.NetworkCom.Count(countSql))
	}
	if total > req.PageSize {
		hasNextPage = true
	} else {
		hasNextPage = false
	}
	moreInfo(req, 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 getQuerySql(req *types.ProjectListReq, isPage bool, buyerArr []string) (countSql, findSql string) {
	querys := []string{}
	// 左侧选中的业主id
	if len(buyerArr) > 0 {
		querys = append(querys, fmt.Sprintf(" a.buyer_id in (%s) ", strings.Join(buyerArr, ",")))
	}
	//	商机类型
	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.StartTime + 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.StartTime + 90*24*60*60
		querys = append(querys, fmt.Sprintf(" a.endtime<=%d", et))
	}
	var regionArr = []string{}
	if req.Area != "" || req.City != "" || req.District != "" {
		//城市
		city := []string{}
		for _, v := range strings.Split(req.City, ",") {
			if P.BidCodeMapping.City[v] != "" {
				city = append(city, fmt.Sprint(P.BidCodeMapping.City[v]))
			}
		}
		if len(city) > 0 {
			regionArr = append(regionArr, fmt.Sprintf(" a.city in (%s) ", strings.Join(city, ",")))
		}
		//区域
		area := []string{}
		for _, v := range strings.Split(req.Area, ",") {
			if P.BidCodeMapping.Area[v] != "" {
				area = append(area, fmt.Sprint(P.BidCodeMapping.Area[v]))
			}
		}
		if len(area) > 0 {
			regionArr = append(regionArr, fmt.Sprintf(" a.area in (%s) ", strings.Join(area, ",")))
		}
		//区域
		district := []string{}
		if req.District != "" {
			for _, v := range strings.Split(req.District, ",") {
				cityName := strings.Split(v, "_")[0]
				districtName := strings.Split(v, "_")[1]
				if P.BidCodeMapping.District[cityName][districtName] != "" {
					district = append(district, fmt.Sprint(P.BidCodeMapping.District[cityName][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(`"物业_%s"`, v))
		}
		querys = append(querys, fmt.Sprintf(" a.subclass in (%s) ", strings.Join(regionArr, ",")))
	}
	// 项目金额
	if req.Amount != "" && strings.Contains(req.Amount, "-") {
		minPriceStr, maxPriceStr := strings.Split(req.Amount, "-")[0], strings.Split(req.Amount, "-")[1]
		minPrice := common.Int64All(common.Float64All(minPriceStr) * 10000) //换成元
		maxPrice := common.Int64All(common.Float64All(maxPriceStr) * 10000) //换成元
		if minPriceStr != "" && maxPriceStr != "" {
			querys = append(querys, fmt.Sprintf("((a.project_money>=%d and a.project_money<=%d))", minPrice, maxPrice))
		} else if minPriceStr != "" {
			querys = append(querys, fmt.Sprintf("(a.project_money>=%d)", minPrice))
		} else if maxPriceStr != "" {
			querys = append(querys, fmt.Sprintf("(a.project_money<=%d)", maxPrice))
		}
	}
	//物业业态
	if req.PropertyForm != "" {
		arr := []string{}
		for _, v := range strings.Split(req.PropertyForm, ",") {
			arr = append(arr, fmt.Sprintf(`"%s"`, v))
		}
		querys = append(querys, fmt.Sprintf(" a.property_form in (%s) ", strings.Join(arr, ",")))
	}
	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 "
	if len(querys) > 0 {
		countSql = fmt.Sprintf("select count(1) from %s a where %s ", "information.transaction_info", strings.Join(querys, " and "))
		findSql = fmt.Sprintf("%s from %s  a  where  %s order by zbtime", findSql, "information.transaction_info", strings.Join(querys, " and "))
	} else {
		countSql = fmt.Sprintf("select count(1) from %s a ", "information.transaction_info")
		findSql = fmt.Sprintf("%s from %s a order by zbtime", findSql, "information.transaction_info")
	}
	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 map[string]interface{}, isSqlPage bool) []*ProjectEntry {
	var newList []*ProjectEntry
	f := make(map[string]int, 3)
	if strings.Contains(req.SaleStatus, "1") {
		f["is_handle"] = 0
	} else if strings.Contains(req.SaleStatus, "2") {
		f["is_ignore"] = 1
	} else if strings.Contains(req.SaleStatus, "3") {
		f["is_create"] = 1
	}
	for _, m := range resultList {
		if m1, ok := preSales[m.ProjectId].(map[string]interface{}); ok {
			m.IsHandle = common.IntAll(m1["is_handle"])
			m.IsIgnore = common.IntAll(m1["is_ignore"])
			m.IsCreate = common.IntAll(m1["is_create"])
		}
		if !isSqlPage {
			for k, v := range f {
				if k == "is_handle" && m.IsHandle == v {
					newList = append(newList, m)
					break
				} else if k == "is_ignore" && m.IsIgnore == v {
					newList = append(newList, m)
					break
				} else if k == "is_create" && m.IsCreate == v {
					newList = append(newList, m)
					break
				}
			}
		}
	}
	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, list []*ProjectEntry) (result []*ProjectEntry) {
	var buyerIds []string
	for _, m := range list {
		if m.BuyerId != "" {
			buyerIds = append(buyerIds, m.BuyerId)
		}
	}
	countMap := make(map[string]int)
	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...)
	connMap := make(map[string]int)
	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 {
		// 人脉、人脉所在单位项目 conn_type: 1 人脉可转介绍项目; conn_type: 2 人脉所在单位项目
		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["b_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.ObjToString(m1["count"])
						break
					}
				}
			}
		}
	} else {
		companyList = Findwinner(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["b_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.ObjToString(m1["count"])
							break
						}
					}
				}
			}
		}
	}
	return list
}