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"
	"strings"
)

const (
	pageSize = 100

	sql_1 = `SELECT count(1) FROM information.transaction_info WHERE 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 b.company_id, b.company_name, b.contact_name, a.relate_id, a.relate_name FROM connection_introduce a INNER JOIN connection b ON b.position_id =%d ND a.connection_id = b.idAND b.state = 1AND a.type = 1AND b.type = 4AND a.relate_Id = %s`
)

type ProjectData struct {
	count       int64
	hasNextPage bool
	pList       []*ProjectEntry
}

type ProjectEntry struct {
	projectName  string
	buyer        string
	buyerId      string
	sWinner      string
	area         string
	city         string
	district     string
	startTime    int64
	entTime      int64
	subClass     string
	propertyForm string
	businessType string
}

func GetProjectList(req *types.ProjectListReq) (resultList []map[string]interface{}, hasNextPage bool, total int) {
	preSales := preSalesStatus(req.PositionId)
	isSqlPage := false
	if req.SaleStatus == 0 {
		isSqlPage = true // 是否sql分页
	}
	findSql := getQuerySql(req, isSqlPage)
	rows, err := T.ClickhouseConn.Query(context.TODO(), findSql)
	defer rows.Close()
	if err != nil {
		return nil, false, 0
	}
	i := 0
	info := make(map[string]interface{})
	for rows.Next() {
		rows.Scan(&info)
		resultList = append(resultList, info)
		i++
	}

	filterData(req, resultList, preSales, isSqlPage)
	if !isSqlPage {
		start := (req.PageNum - 1) * req.PageSize
		end := start + req.PageSize
		if req.PageNum > 1 {
			resultList = resultList[start:end]
		} else {
			resultList = resultList[:pageSize]
		}
		total = len(resultList)
		if total > 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) (findSql string) {
	querys := []string{}
	//	商机类型
	if req.BusinessType != "" {
		querys = append(querys, fmt.Sprintf(" a.business_type in (%s) ", req.BusinessType))
	}
	if req.ProjectName != "" {
		querys = append(querys, " a.projectname 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))
	} else {
		querys = append(querys, " a.endtime>0")
	}
	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.project_budget,a.project_money,a.business_type,a.buyer from"
	findSql = fmt.Sprintf("%s from %s  a  where  %s ", findSql, "information.transaction_info", strings.Join(querys, " and "))
	if isPage {
		findSql += fmt.Sprintf(" limit %d,%d", (req.PageNum-1)*pageSize, pageSize)
	}
	return
}

// @Author jianghan
// @Description 过滤数据/补充销售机会状态信息，返回分页结果数据
// @Date 2024/4/18
func filterData(req *types.ProjectListReq, resultList []map[string]interface{}, preSales map[string]interface{}, isSqlPage bool) {
	var newList []map[string]interface{}
	f := ""
	v := 0
	if req.SaleStatus == 1 {
		f = "is_handle"
		v = 0
	} else if req.SaleStatus == 2 {
		f = "is_ignore"
		v = 0
	} else if req.SaleStatus == 3 {
		f = "is_create"
		v = 1
	}
	for _, m := range resultList {
		if m1, ok := preSales[common.ObjToString(m["project_id"])].(map[string]interface{}); ok {
			m["is_handle"] = m1["is_handle"]
			m["is_ignore"] = m1["is_ignore"]
			m["is_create"] = m1["is_create"]
			if !isSqlPage && m1[f] == v {
				newList = append(newList, m)
			}
		}
	}
	if len(newList) > 0 {
		resultList = newList
	}
}

// @Author jianghan
// @Description 补充人脉 等信息
// @Date 2024/4/17
func moreInfo(req *types.ProjectListReq, list []map[string]interface{}) (result []map[string]interface{}) {
	for _, m := range list {
		// 人脉、人脉所在单位项目 conn_type: 1/人脉可转介绍项目 conn_type: 2
		field1 := ""
		query1 := map[string]interface{}{
			"position_id": req.PositionId,
			"company_id":  common.ObjToString(m["buyer_id"]),
			"status":      1,
		}
		info1 := T.CrmMysql.FindOne("connection", query1, field1, "")
		if info1 != nil && len(*info1) > 0 {
			m["my_conn"] = true
			m["conn_type"] = 1
		} else {
			m["my_conn"] = false
		}
		if m["conn_type"] != nil {
			query2 := map[string]interface{}{
				"company_id": common.ObjToString(m["buyer_id"]),
			}
			info2 := T.CrmMysql.FindOne("connection", query2, field1, "")
			if info2 != nil && len(*info2) > 0 {
				m["conn_type"] = 1
			} else {
				m["conn_type"] = 2
			}
		}
		// 转介绍成功率高标签
		count := 0
		err := T.ClickhouseConn.QueryRow(context.TODO(), sql_1, common.ObjToString(m["buyer_id"])).Scan(&count)
		if err != nil && count > 2 {
			m["high_success"] = true
		} else {
			m["high_success"] = false
		}

		// 人脉路径
		// 有我的人脉标签时不需要查询人脉路径信息
		if m["my_conn"] == false {
			//ConnectionsHandle([]string{common.ObjToString(m["buyer_id"])}, req.PositionId, false)
		}
	}

	return list
}