datatools/telemarketingEtl/entity/dwd_f_crm_open_sea.go

package entity

import (
	"fmt"
	"log"
	"strings"
	"telemarketingEtl/config"
	"time"

	"app.yhyue.com/moapp/jybase/date"
	"app.yhyue.com/moapp/jybase/mongodb"
	"github.com/gogf/gf/v2/frame/g"
	"github.com/gogf/gf/v2/os/gctx"
	"github.com/gogf/gf/v2/util/gconv"
)

/*
超级订阅落地页 /front/vipsubscribe/introducePage  /jyapp/vipsubscribe/introducePage
大会员落地页 /big/wx/page/landingPage /jyapp/big/page/landingPage
产品体系服务 /product/index
数据导出筛选 /front/dataExport/toSieve  /front/wx_dataExport/toSieve /jyapp/front/dataExport/toSieve
数据导出购买 /front/dataExport/toCreateOrderPage/425b420f564502000756420a4056564451535451430e490f?from=&type=2&source=
数据流量包 /front/dataPack/createOrder
超级订阅 /swordfish/page_big_pc/free/svip/buy
大会员 /big/pc/page/buy_commit
*/

/*
3.公海客户来源：
（1）一级公海：
A. 3天内点击过剑鱼付费产品介绍页（产品-供应商服务体系页、大会员落地页、超级订阅落地页、数据自助导出-筛选条件页）或进入了购买页（超级订阅、大会员购买页、数据自助导出购买页、数据流量包购买页）的客户；
B. 购买了超级订阅产品剩余使用时长不足30天的客户；
C.超时未跟进导致被自动退回至公海的处于“潜在客户”、“意向客户”、“高意向客户”
（2）二级公海：
A.最近30天内活跃天数≥15天的客户；
B.10天≤最近30天内活跃天数＜15天的客户；
C.5天≤最近30天内活跃天数＜10天的客户；
D.1天≤最近30天内活跃天数＜5天的客户；
备注：判断当日是否活跃的标准为当日是否点击查看了标讯内容。
（3）三级公海：
最近30天内活跃天数=0天的客户。
（4）回收站：
A.5个自然日内被销售人员手动退回公海的客户；
B.3个自然日内有过“已接听”的通话记录且仍处于“商机线索”状态下的客户。
（5）以上，涉及时间及量的均可配置。

*/

func GetOpenSea() {
	ctx := gctx.New()
	//
	session := config.MgoLog.GetMgoConn()
	defer config.MgoLog.DestoryMongoConn(session)
	t := time.Now()
	t = time.Date(t.Year(), t.Month(), t.Day(), 0, 0, 0, 0, time.Local)
	end := t.Unix()
	tA := t.AddDate(0, 0, -g.Cfg().MustGet(ctx, "classOneHighSeas_A").Int())

	start := tA.Unix()
	oneClassA := map[string]bool{}
	//一级公海查询条件
	queryOneClassPc := map[string]interface{}{
		"date": map[string]interface{}{
			"$gte": start,
			"$lt":  end,
		},
		"$or": []map[string]interface{}{
			map[string]interface{}{
				"url": map[string]interface{}{
					"$regex": `/front/vipsubscribe/introducePage`,
				},
			},
			map[string]interface{}{
				"url": map[string]interface{}{
					"$regex": `/big/wx/page/landingPage`,
				},
			},
			map[string]interface{}{
				"url": map[string]interface{}{
					"$regex": `/product/index`,
				},
			},
			map[string]interface{}{
				"url": map[string]interface{}{
					"$regex": `/front/dataExport/toSieve`,
				},
			},
			map[string]interface{}{
				"url": map[string]interface{}{
					"$regex": `/front/dataExport/toCreateOrderPage`,
				},
			},
			map[string]interface{}{
				"url": map[string]interface{}{
					"$regex": `/front/dataPack/createOrder`,
				},
			},
			map[string]interface{}{
				"url": map[string]interface{}{
					"$regex": `/swordfish/page_big_pc/free/svip/buy`,
				},
			},
			map[string]interface{}{
				"url": map[string]interface{}{
					"$regex": `/big/pc/page/buy_commit`,
				},
			},
			map[string]interface{}{
				"url": map[string]interface{}{
					"$regex": `/front/wx_dataExport/toSieve`,
				},
			},
		},
	}
	log.Println("queryOneClassPc:", queryOneClassPc)
	iter := session.DB("qfw").C("jy_logs").Find(queryOneClassPc).Iter()
	count := 0
	log.Println("一级公海开始.")
	//一级公海
	for thisData := map[string]interface{}{}; iter.Next(&thisData); {

		userid := gconv.String(thisData["userid"])
		if userid == "" {
			continue
		}
		if !mongodb.IsObjectIdHex(userid) {
			userid, _ = GetUserIdByPositionId(userid)
		}
		if userid == "" {
			continue
		}
		clubId := GetClueIdByUserId(userid)
		if clubId == "" {
			continue
		}
		//根据userid获取线索id
		if oneClassA[clubId] {
			continue
		}
		oneClassA[clubId] = true
		count++
		if count%100 == 0 {
			log.Printf("已完成%d条数据\n", count)
		}
		thisData = map[string]interface{}{}
	}

	log.Println("一级公海 pc  结束")
	queryOneClassApp := map[string]interface{}{
		"date": map[string]interface{}{
			"$gte": start,
			"$lt":  end,
		},
		"$or": []map[string]interface{}{
			map[string]interface{}{
				"url": map[string]interface{}{
					"$regex": `/jyapp/vipsubscribe/introducePage`,
				},
			},
			map[string]interface{}{
				"url": map[string]interface{}{
					"$regex": `jyapp/big/page/landingPage`,
				},
			},
			map[string]interface{}{
				"url": map[string]interface{}{
					"$regex": `/jyapp/front/dataExport/toSieve`,
				},
			},
		},
	}
	iter2 := session.DB("qfw").C("jyapp_logs").Find(queryOneClassApp).Iter()
	count = 0
	//一级公海
	for thisData := map[string]interface{}{}; iter2.Next(&thisData); {

		userid := gconv.String(thisData["userid"])
		if userid == "" {
			continue
		}
		if !mongodb.IsObjectIdHex(userid) {
			userid, _ = GetUserIdByPositionId(userid)
		}
		if userid == "" {
			continue
		}
		clubId := GetClueIdByUserId(userid)
		if clubId == "" {
			continue
		}
		//根据userid获取线索id
		if oneClassA[clubId] {
			continue
		}
		oneClassA[clubId] = true

		count++
		if count%10 == 0 {
			log.Printf("已完成%d条数据\n", count)
		}
		thisData = map[string]interface{}{}
	}
	//更新公海
	AddOpenSea(oneClassA, 1, "A")
	//
	oneClassB := map[string]bool{}
	vipendtime := t.AddDate(0, 0, -g.Cfg().MustGet(ctx, "classOneHighSeas_B").Int())
	classB, ok := config.Mgo.Find("user", map[string]interface{}{
		"l_vip_endtime": map[string]interface{}{
			"$lt": vipendtime.Unix(),
		},
		"i_vip_status": map[string]interface{}{
			"$gt": 0,
		},
		"i_appid": 2,
	}, nil, `{"_id":1}`, false, -1, -1)
	if classB != nil && ok && len(*classB) > 0 {
		for _, v := range *classB {
			userid := mongodb.BsonIdToSId(v["_id"])
			clubId := GetClueIdByUserId(userid)
			if clubId == "" {
				continue
			}
			if oneClassA[clubId] {
				continue
			}
			oneClassB[clubId] = true
		}
	}
	AddOpenSea(oneClassB, 1, "B")
	oneClassC := GetOneSeaC(oneClassA, oneClassB)
	AddOpenSea(oneClassC, 1, "C")
	log.Println("一级公海 更新结束。")
	//
	twoA, twoB, twoC, twoD := TwoOpenSea(oneClassA, oneClassB, oneClassC)

	AddOpenSea(twoA, 2, "A")
	AddOpenSea(twoB, 2, "B")
	AddOpenSea(twoC, 2, "C")
	AddOpenSea(twoD, 2, "D")

	//三级公海
	ThreeOpenSea(oneClassA, oneClassB, oneClassC, twoA, twoB, twoC, twoD)
	// AddOpenSea(three, 3, "D")
	//
	Recycle()
	log.Println("end")
}

//二级公海
/*
	A.最近30天内活跃天数≥15天的客户；
	B.10天≤最近30天内活跃天数＜15天的客户；
	C.5天≤最近30天内活跃天数＜10天的客户；
	D.1天≤最近30天内活跃天数＜5天的客户；
*/
func TwoOpenSea(oneClassA, oneClassB, oneClassC map[string]bool) (aMap, bMap, cMap, dMap map[string]bool) {

	clubIdMap := func() map[string]string {
		c := 0
		mp := map[string]string{}
		config.JianyuSubjectdb.SelectByBath(500, func(l *[]map[string]interface{}) bool {
			for _, v := range *l {
				c++
				if c%5000 == 0 {
					log.Println("加载clue_info数据：", c)
				}
				mp[gconv.String(v["userid"])] = gconv.String(v["id"])
			}
			return true
		}, `SELECT id,userid FROM dwd_f_crm_clue_info WHERE userid !="" and is_assign = 0`)
		return mp
	}()
	log.Println("clue_info数据加载结束：", len(clubIdMap))
	//
	i_day := g.Cfg().MustGet(ctx, "classTwoHighSeaslastDay").Int()
	day := time.Now().AddDate(0, 0, -i_day).Format(date.Date_Full_Layout)
	q := fmt.Sprintf("SELECT userid,COUNT(1) as count FROM dwd_f_userbase_visit_info WHERE  createtime > '%s' AND contentnum >0 GROUP BY userid ", day)
	//
	log.Println(q)
	stmtOut, err := config.JianyuSubjectdb.DB.Prepare(q)
	log.Println("stmtOut.err：", err)
	defer func() {
		log.Println("stmtOut.Close start")
		stmtOut.Close()
		log.Println("stmtOut.Close over")
	}()

	rows, err := stmtOut.Query()
	if err != nil {
		log.Println("er:", err)
	}
	defer func() {
		log.Println("rows.Close start")
		rows.Close()
		log.Println("rows.Close over")
	}()
	aMap = map[string]bool{}
	bMap = map[string]bool{}
	cMap = map[string]bool{}
	dMap = map[string]bool{}
	count := 0
	for rows.Next() {
		var userid string
		var ct int
		err := rows.Scan(&userid, &ct)
		if err != nil {
			log.Println("row scan err:", err)
		}
		count++
		if count%5000 == 0 {
			log.Println("二级公海查询：", count)
		}
		// clubId := GetClueIdByUserId(userid)
		clubId := clubIdMap[userid]
		if clubId == "" {
			continue
		}
		if oneClassA[clubId] || oneClassB[clubId] || oneClassC[clubId] {
			continue
		}
		if ct >= 15 {
			aMap[clubId] = true
		}
		if ct < 15 && ct >= 10 {
			bMap[clubId] = true
		}
		if ct < 10 && ct >= 5 {
			cMap[clubId] = true
		}
		if ct < 5 && ct >= 1 {
			dMap[clubId] = true
		}

	}
	// Check for errors during iteration
	err = rows.Err()
	if err != nil {
		log.Println("rows err err:", err)
	}
	return
}

// 三级公海：
// 最近30天内活跃天数=0天的客户。
func ThreeOpenSea(oneA, oneB, oneC, twoA, twoB, twoC, twoD map[string]bool) map[string]bool {
	log.Println("三级公海start")
	m := map[string]bool{}
	ctx := gctx.New()
	t := time.Now()
	t = time.Date(t.Year(), t.Month(), t.Day(), 0, 0, 0, 0, time.Local)
	day := g.Cfg().MustGet(ctx, "classThreeHighSeaslastDay").Int()
	t = t.AddDate(0, 0, -day)
	count := 0
	visitMap := func() map[string]bool {
		c := 0
		mp := map[string]bool{}
		config.JianyuSubjectdb.SelectByBath(500, func(l *[]map[string]interface{}) bool {
			for _, v := range *l {
				c++
				if c%5000 == 0 {
					log.Println("加载dwd_f_userbase_visit_info数据：", c)
				}
				mp[gconv.String(v["userid"])] = true
			}
			return true
		}, `SELECT COUNT(1),userid FROM dwd_f_userbase_visit_info WHERE  createtime > ? AND contentnum >0 GROUP BY userid`, t.Format(date.Date_Full_Layout))
		return mp
	}()
	log.Println("dwd_f_userbase_visit_info数据加载结束：", len(visitMap))

	config.JianyuSubjectdb.SelectByBath(500, func(l *[]map[string]interface{}) bool {
		ids := []interface{}{}

		for _, v := range *l {
			count++
			if count%5000 == 0 {
				log.Println("三级公海加载:", count)
			}
			clubId := gconv.String(v["id"])
			userid := gconv.String(v["userid"])
			// q := fmt.Sprintf("SELECT count(1)  FROM dwd_f_userbase_visit_info WHERE  createtime > '%s' AND contentnum >0 and userid = '%s'", t.Format(date.Date_Full_Layout), userid)
			// if config.JianyuSubjectdb.CountBySql(q) > 0 {
			// 	continue
			// }
			if visitMap[userid] {
				continue
			}
			if oneA[clubId] || oneB[clubId] || oneC[clubId] || twoA[clubId] || twoB[clubId] || twoC[clubId] || twoD[clubId] {
				continue
			}

			ids = append(ids, clubId)
		}
		whs := []string{}
		for i := 0; i < len(ids); i++ {
			whs = append(whs, "?")
		}
		wh := strings.Join(whs, ",")
		config.JianyuSubjectdb.UpdateOrDeleteBySql(`UPDATE dwd_f_crm_clue_info SET level_open = 3,clue_level='D' WHERE id in (`+wh+`)`, ids...)
		return true
	}, `SELECT id,userid FROM dwd_f_crm_clue_info where is_assign = 0`)
	log.Println("三级公海end")
	return m
}

// 根据mongodb userid 获取 线索id
func GetClueIdByUserId(userid string) (uuid string) {
	if userid == "" {
		return
	}
	data := config.JianyuSubjectdb.SelectBySql(`select id from dwd_f_crm_clue_info where is_assign = 0 and userid=? limit 1`, userid)
	if data == nil || len(*data) == 0 {
		return
	}
	return gconv.String((*data)[0]["id"])
}

// m 需要更新的Map key:uuid
// level 公海级别
// clue_level线索级别
func AddOpenSea(m map[string]bool, level int, clue_level string) {
	// createtime := time.Now().Format(date.Date_Full_Layout)
	if len(m) > 0 {
		for k, _ := range m {
			config.JianyuSubjectdb.Update("dwd_f_crm_clue_info", map[string]interface{}{"id": k}, map[string]interface{}{
				"clue_level": clue_level,
				"level_open": level,
			})
		}
	}
}

// 自动退出公海
// 线索状态为“空号停机”自动从线索池删除。
// 空号停机 删除私海， 线索表分配状态改成 -1
// func DeleteOpenSea() {
// 	config.JianyuSubjectdb.SelectByBath(500, func(l *[]map[string]interface{}) bool {
// 		ids := []interface{}{}
// 		for _, v := range *l {
// 			id := v["id"]
// 			ids = append(ids, id)
// 		}
// 		whs := []string{}
// 		for i := 0; i < len(ids); i++ {
// 			whs = append(whs, "?")
// 		}
// 		wh := strings.Join(whs, ",")
// 		count := config.JianyuSubjectdb.UpdateOrDeleteBySql(`UPDATE dwd_f_crm_clue_info SET is_assign = -1 WHERE id in (`+wh+`)`, ids...)
// 		if count > 0 {
// 			return true
// 		}
// 		return true
// 	}, `select id from dwd_f_crm_clue_info where trailstatus = ?`, "02")
// }

//自动退回公海
/*
1.处于“待签署客户”和“成交客户”状态下的客户不自动退回公海；
2.“高意向客户”超过30天未更新跟进记录自动退回公海；
3.“意向客户”超过30天未更新跟进记录自动退回公海；
4.“潜在客户”超过60天未更新跟进记录自动退回公海；
5.“沉睡客户”超过90天未更新跟进记录自动退回公海；
6.“商机线索”超过2天未更新跟进记录自动退回公海；
7.“无意向客户”自动退回公海；
*/
// func ReturnOpenSea() {
// 	ctx := gctx.New()
// 	t := time.Now()
// 	t = time.Date(t.Year(), t.Month(), t.Day(), 0, 0, 0, 0, time.Local)
// 	//高意向客户
// 	highIntentionCustomer := g.Cfg().MustGet(ctx, "highIntentionCustomer").Int() //30
// 	//意向客户
// 	intentionCustomer := g.Cfg().MustGet(ctx, "intentionCustomer").Int() //30
// 	//潜在客户
// 	latentCustomer := g.Cfg().MustGet(ctx, "latentCustomer").Int() //60
// 	//沉睡客户
// 	sleepCustomer := g.Cfg().MustGet(ctx, "sleepCustomer").Int() //90
// 	//商机线索
// 	businessLeads := g.Cfg().MustGet(ctx, "businessLeads").Int() //2
// 	//无意向客户
// 	// noIdeaCustomer := g.Cfg().MustGet(ctx, "noIdeaCustomer").Int() //

// 	codeMap := dwd_d_crm_trailstatus_code()

// 	now := time.Now().Format(date.Date_Full_Layout)

// 	//判断节假日
// 	statusMap := map[string]int{
// 		"06": highIntentionCustomer,
// 		"05": intentionCustomer,
// 		"04": latentCustomer,
// 		"03": sleepCustomer,
// 		"01": businessLeads,
// 	}
// 	for status, statusInt := range statusMap {
// 		count, counts := 0, 0
// 		for {
// 			count++
// 			currentTime := t.AddDate(0, 0, -count)
// 			if currentTime.Weekday() == time.Sunday || currentTime.Weekday() == time.Saturday {
// 				isok := false
// 				for k, v := range config.DateMap {
// 					if currentTime.Format(date.Date_Short_Layout) == k && v == 2 {
// 						isok = true
// 					}
// 				}
// 				if isok {
// 					counts++
// 				}
// 			} else {
// 				isok := true
// 				for k, v := range config.DateMap {
// 					if currentTime.Format(date.Date_Short_Layout) == k && v == 1 {
// 						isok = false
// 					}
// 				}
// 				if isok {
// 					counts++
// 				}
// 			}
// 			if counts >= statusInt {
// 				break
// 			}
// 		}
// 		statusMap[status] = count
// 	}
// 	//

// 	//2.“高意向客户”超过30天未更新跟进记录自动退回公海；
// 	for trailstatus, _ := range map[string]string{
// 		"06": "",
// 		"05": "",
// 		"04": "",
// 		"03": "",
// 		"01": "",
// 		"00": "",
// 	} {
// 		sql := `SELECT a.clue_id,a.position_id,a.seatNumber FROM dwd_f_crm_private_sea a
// 				LEFT JOIN dwd_f_crm_clue_info b ON a.clue_id=b.id
// 				WHERE a.position_id is not null and b.trailstatus =?`
// 		argsSelect := []interface{}{trailstatus}

// 		intime := ""
// 		if trailstatus != "00" {
// 			sql += " AND a.comeintime <?"
// 			nt := t.AddDate(0, 0, -statusMap[trailstatus])
// 			// nt, _ := nexttime.(time.Time)
// 			intime = nt.Format(date.Date_Full_Layout)
// 			argsSelect = append(argsSelect, intime)
// 		}
// 		log.Println(sql)
// 		log.Println(argsSelect)
// 		//
// 		config.JianyuSubjectdb.SelectByBath(500, func(l *[]map[string]interface{}) bool {
// 			//1新增 2私海手动退回 3私海高意向客户自动退回 4私海意向客户退回 5私海潜在客户退回 6私海沉睡客户退回 7私海商机线索退回 8私海无意向客户退回
// 			comeinsource := GetComeSource()[trailstatus]
// 			ids := []interface{}{}
// 			args := []interface{}{}
// 			changeArgs1 := []interface{}{}
// 			changeArgs2 := []interface{}{}
// 			changeArgs3 := []interface{}{}
// 			changeArgs4 := []interface{}{}
// 			for _, v := range *l {
// 				id := v["clue_id"] //
// 				position_id := v["position_id"]
// 				seatNumber := gconv.String(v["seatNumber"])

// 				args2 := []interface{}{id}
// 				//获取跟进内容
// 				sql2 := `SELECT COUNT(1) FROM dwd_f_crm_trail_content WHERE clue_id = ? and position_id = ?`
// 				if intime != "" {
// 					sql2 += ` and createtime > ?`
// 					args2 = append(args2, intime)
// 					args2 = append(args2, position_id)
// 				}
// 				//保留未跟进线索
// 				if config.JianyuSubjectdb.CountBySql(sql2, args2...) > 0 {
// 					log.Println("不满足线索过滤", id)
// 					continue
// 				}

// 				ids = append(ids, id)
// 				//
// 				args = append(args, id, now, comeinsource)
// 				changeArgs1 = append(changeArgs1, id, position_id, "trailstatus", "线索状态变更", codeMap[trailstatus], "流失", now, -1)
// 				changeArgs2 = append(changeArgs2, id, position_id, "position_id", "所属人变更", GetPositionName(seatNumber), "/", now, -1)
// 				changeArgs3 = append(changeArgs3, id, position_id, "退出任务车", "未更新跟进记录自动退回公海", now, -1)
// 				changeArgs4 = append(changeArgs3, id, position_id, "退回公海", "未更新跟进记录自动退回公海", now, -1)

// 			}
// 			whs := []string{}
// 			for i := 0; i < len(ids); i++ {
// 				whs = append(whs, "?")
// 			}
// 			wh := strings.Join(whs, ",")
// 			//退出私海
// 			if len(ids) > 0 {
// 				config.JianyuSubjectdb.UpdateOrDeleteBySql(`delete from dwd_f_crm_private_sea  where clue_id in (`+wh+`)`, ids...)
// 				//改线索表 职位id  坐席号 弄成空 分配状态改成未分配
// 				up1 := config.JianyuSubjectdb.UpdateOrDeleteBySql(`UPDATE dwd_f_crm_clue_info SET seatNumber ='',position_id=0,is_assign=0  WHERE id in (`+wh+`)`, ids...)
// 				if up1 == -1 {
// 					log.Println("修改记录失败", `UPDATE dwd_f_crm_clue_info SET seatNumber ='',position_id=0,is_assign=0  WHERE id in (`+wh+`)`, ids)
// 				}
// 			}
// 			//进入公海
// 			if len(args) > 0 {
// 				config.JianyuSubjectdb.InsertIgnoreBatch("dwd_f_crm_clue_info", []string{"clue_id", "comeintime", "comeinsource"}, args)
// 			}

// 			// 添加线索修改记录

// 			//线索状态变更记录
// 			if len(changeArgs1) > 0 {
// 				i1, _ := config.JianyuSubjectdb.InsertBatch("dwd_f_crm_clue_change_record", []string{"clue_id", "position_id", "change_field", "change_type", "old_value", "new_value", "createtime", "operator_id"}, changeArgs1)
// 				if i1 <= 0 {
// 					log.Println("i1失败", changeArgs1)
// 				}
// 			}
// 			if len(changeArgs1) > 0 {
// 				i2, _ := config.JianyuSubjectdb.InsertBatch("dwd_f_crm_clue_change_record", []string{"clue_id", "position_id", "change_field", "change_type", "old_value", "new_value", "createtime", "operator_id"}, changeArgs2)
// 				if i2 <= 0 {
// 					log.Println("i2失败", changeArgs2)
// 				}
// 			}
// 			if len(changeArgs1) > 0 {
// 				i3, _ := config.JianyuSubjectdb.InsertBatch("dwd_f_crm_clue_change_record", []string{"clue_id", "position_id", "change_type", "new_value", "createtime", "operator_id"}, changeArgs3)
// 				if i3 <= 0 {
// 					log.Println("i3失败", changeArgs3)
// 				}
// 			}
// 			if len(changeArgs1) > 0 {
// 				i4, _ := config.JianyuSubjectdb.InsertBatch("dwd_f_crm_clue_change_record", []string{"clue_id", "position_id", "change_type", "new_value", "createtime", "operator_id"}, changeArgs4)
// 				if i4 <= 0 {
// 					log.Println("i4失败", changeArgs4)
// 				}
// 			}
// 			return true
// 		}, sql, argsSelect...)
// 	}
// 	log.Println("return sea end")
// }

// 2私海手动退回 3私海高意向客户自动退回 4私海意向客户退回 5私海潜在客户退回 6私海沉睡客户退回 7私海商机线索退回 8私海无意向客户退回
func GetComeSource() map[string]int {
	return map[string]int{
		"06": 3,
		"05": 4,
		"04": 5,
		"03": 6,
		"01": 7,
		"00": 8,
	}
}

func GetOneSeaC(oneClassA, oneClassB map[string]bool) map[string]bool {
	m := map[string]bool{}
	config.JianyuSubjectdb.SelectByBath(500, func(l *[]map[string]interface{}) bool {
		for _, v := range *l {
			clueid := gconv.String(v["id"])
			if oneClassA[clueid] || oneClassB[clueid] {
				continue
			}
			m[clueid] = true
		}
		return true
	}, `select id from dwd_f_crm_clue_info where is_assign = 0 and comeinsource_open in(3,4,5)`)
	return m
}

func dwd_d_crm_trailstatus_code() map[string]string {
	m := map[string]string{}
	data := config.JianyuSubjectdb.SelectBySql(`SELECT CODE,NAME FROM dwd_d_crm_trailstatus_code;`)
	for _, v := range *data {
		code := gconv.String(v["CODE"])
		name := gconv.String(v["NAME"])
		m[code] = name
	}
	return m
}

func GetPositionName(seatNumber string) string {
	data := config.JianyuSubjectdb.SelectBySql(`select name from jy_salesperson_info where status = 0 and position != 0 and seatNumber = ? limit 1`, seatNumber)
	if data != nil && len(*data) > 0 {
		return gconv.String((*data)[0]["name"])
	}
	return ""
}

// （4）回收站：
// A.5个自然日内被销售人员手动退回公海的客户；
// B.3个自然日内有过“已接听”的通话记录且仍处于“商机线索”状态下的客户。
func Recycle() {
	log.Println("回收站开始")
	ctx := gctx.New()
	t := time.Now()
	t = time.Date(t.Year(), t.Month(), t.Day(), 0, 0, 0, 0, time.Local)
	handReturn := g.Cfg().MustGet(ctx, "handReturn").Int()
	tA := t.AddDate(0, 0, -handReturn)
	recycleB := g.Cfg().MustGet(ctx, "recycleB").Int()
	tB := t.AddDate(0, 0, -recycleB)
	for sql, clue_level := range map[string]interface{}{
		fmt.Sprintf(`select id from dwd_f_crm_clue_info where (comeinsource_open=2 or comeinsource_open=8) and is_assign = 0 and comeintime_open > "%s" ;`, tA.Format(date.Date_Full_Layout)): "A",
		fmt.Sprintf(`SELECT a.uid,b.phone,c.createTime,a.id FROM dwd_f_crm_clue_info a 
					LEFT JOIN dwd_f_userbase_contacts b ON b.baseinfo_id=a.uid
					LEFT JOIN Call_Accounting.voice_record c ON c.CalledNo = b.phone
					WHERE a.is_assign = 0 AND a.trailstatus=01 AND a.uid !="" AND b.phone IS NOT NULL AND c.State="dealing" AND c.createTime >"%s"
					`, tB.Format(date.Date_Full_Layout)): "B",
	} {
		func(sql string) {
			config.JianyuSubjectdb.SelectByBath(500, func(l *[]map[string]interface{}) bool {
				ids := []interface{}{}
				for _, v := range *l {
					id := v["id"]
					//不包括管理员退回
					if clue_level == "A" {
						rData := config.JianyuSubjectdb.FindOne("dwd_f_crm_clue_change_record", map[string]interface{}{"clue_id": id, "change_type": "退回公海"}, "", "createtime desc")
						if rData != nil && len(*rData) > 0 {
							position_id := gconv.Int64((*rData)["position_id"])
							operator_id := gconv.Int64((*rData)["operator_id"])
							if position_id != operator_id && operator_id != -1 {
								continue
							}
						}
					}
					ids = append(ids, id)
				}
				whs := []string{}
				for i := 0; i < len(ids); i++ {
					whs = append(whs, "?")
				}
				wh := strings.Join(whs, ",")
				config.JianyuSubjectdb.UpdateOrDeleteBySql(`UPDATE dwd_f_crm_clue_info SET level_open = 4,clue_level = "`+fmt.Sprint(clue_level)+`" WHERE id in (`+wh+`)`, ids...)
				return true
			}, sql)
		}(sql)
	}

	log.Println("回收站结束")

}

/*
dwd_f_crm_clue_info.uid    trailstatus==01
dwd_f_userbase_contacts.baseinfo_id ==dwd_f_crm_clue_info.uid
 >> dwd_f_userbase_contacts.phone== Call_Accounting.voice_record.CalledNo &&State=="dealing"
*/