datatools/clueSync/autoTask.go

package main

import (
	"database/sql"
	"fmt"
	"github.com/gogf/gf/v2/util/gconv"
	"log"
	"time"

	"app.yhyue.com/moapp/jybase/common"
	"app.yhyue.com/moapp/jybase/date"
)

func autoTask() {
	log.Println("超时未跟进定时任务开始")
	t := time.Now()
	nowTime := time.Now().Format(date.Date_Full_Layout)
	statusMap := map[string]int{
		"07": 2, //待签署客户
		"06": 2, //高意向客户
		"05": 3, //意向客户
		"04": 7, //潜在客户
	}
	//判断节假日
	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 DateMap {
					if currentTime.Format(date.Date_Short_Layout) == k && v == 2 {
						isok = true
					}
				}
				if isok {
					counts++
				}
			} else {
				isok := true
				for k, v := range DateMap {
					if currentTime.Format(date.Date_Short_Layout) == k && v == 1 {
						isok = false
					}
				}
				if isok {
					counts++
				}
			}
			if counts >= statusInt {
				break
			}
		}
		statusMap[status] = count
	}
	log.Println(statusMap)
	for trailstatus := range map[string]string{
		"07": "", //待签署客户
		"06": "", //高意向客户
		"05": "", //意向客户
		"04": "", //潜在客户
	} {
		sql := `SELECT id,position_id,seatNumber,out_task_status FROM dwd_f_crm_clue_info 
				WHERE trailstatus = ?`
		argsSelect := []interface{}{trailstatus}
		intime := ""
		sql += " AND comeintime <?"
		// nt := nexttime.(time.Time)
		nt := t.AddDate(0, 0, -statusMap[trailstatus])
		intime = nt.Format(date.Date_Full_Layout)
		argsSelect = append(argsSelect, intime)
		//
		TiDb.SelectByBath(100, func(l *[]map[string]interface{}) bool {
			for _, v := range *l {
				clueId := common.Int64All(v["id"])
				position_id := common.Int64All(v["position_id"])
				out_task_status := common.IntAll(v["out_task_status"])
				args2 := []interface{}{clueId}
				//获取跟进内容
				// sql1 := `select COUNT(1) FROM dwd_f_crm_trail_content WHERE clue_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, position_id)
					args2 = append(args2, intime)
				}
				//保留未跟进线索
				// if c1, c2 := TiDb.CountBySql(sql1, clueId), TiDb.CountBySql(sql2, args2...); (c1 != 0 && c2 > 0) || out_task_status == 1 {
				// 	log.Println("不满足线索过滤", clueId)
				// 	continue
				// }
				log.Println("intime ", clueId, intime, sql2)
				if c2 := TiDb.CountBySql(sql2, args2...); c2 > 0 || out_task_status == 1 {
					log.Println("不满足线索过滤", clueId)
					continue
				}
				if TiDb.Update("dwd_f_crm_clue_info", map[string]interface{}{"id": clueId}, map[string]interface{}{
					"is_task":    1,
					"task_time":  nowTime,
					"tasktime":   time.Now().Format(date.Date_Short_Layout) + " 10:00:00",
					"taskstatus": 0,
					"tasksource": "超时未跟进自动加车",
				}) {
					TiDb.Insert("dwd_f_crm_clue_change_record", map[string]interface{}{
						"clue_id":     clueId,
						"position_id": position_id,
						"change_type": "加入任务车",
						"new_value":   "超时未跟进自动加车",
						"createtime":  nowTime,
						"BCPCID":      common.GetRandom(32),
						"operator_id": -1,
					})
				}
			}
			return true
		}, sql, argsSelect...)
	}
	log.Println("超时未跟进定时任务结束")
}

func autoTasks() {
	log.Println("按照跟进时间提前一天进入任务车定时任务开始")
	nowTime2 := time.Now().Format(date.Date_Full_Layout)
	nextTime := time.Now().AddDate(0, 0, 1).Format(date.Date_Full_Layout)
	TiDb.SelectByBath(100, func(l *[]map[string]interface{}) bool {
		for _, v := range *l {
			clueId := common.Int64All(v["id"])
			position_id := common.Int64All(v["position_id"])
			out_task_status := common.IntAll(v["out_task_status"])
			if position_id > 0 {
				if out_task_status != 1 && position_id > 0 {
					if TiDb.Update("dwd_f_crm_clue_info", map[string]interface{}{"id": clueId}, map[string]interface{}{
						"is_task":    1,
						"task_time":  nowTime2,
						"tasktime":   nowTime2,
						"taskstatus": 0,
						"tasksource": "即将到达下次跟进时间",
					}) {
						TiDb.Insert("dwd_f_crm_clue_change_record", map[string]interface{}{
							"clue_id":     clueId,
							"position_id": position_id,
							"change_type": "加入任务车",
							"new_value":   "即将到达下次跟进时间",
							"createtime":  nowTime2,
							"BCPCID":      common.GetRandom(32),
							"operator_id": -1,
						})
					}
				}
			}
		}
		return true
	}, `SELECT a.id,a.position_id,a.seatNumber,a.out_task_status FROM dwd_f_crm_clue_info a 
				LEFT JOIN dwd_f_crm_trail_content c ON c.clue_id=a.id 
				WHERE c.next_time >= "`+nowTime2+`" and c.next_time <= "`+nextTime+`"`)
	log.Println("按照跟进时间提前一天进入任务车定时任务结束")
}

// 所有人达上限退公海处理
func UpperLimitAutoExitSea(upperLimit int64) {
	countData := TiDb.SelectBySql(`SELECT
	COUNT(b.ID) AS count ,a.position_id
FROM
	dwd_f_crm_clue_info b 
right JOIN
 ( 	select  position_id from  dwd_f_crm_personnel_management where  assign_type = 1 
	AND resign = 0 ) a on  a.position_id=b.position_id    and  b.is_transfer != 1
GROUP BY
	a.position_id HAVING count<?`, upperLimit)
	if countData == nil || len(*countData) > 0 {
		return
	}
	log.Println("所有人达上限退公海处理任务开始")
	data := TiDb.SelectBySql(`select * from dwd_f_crm_clue_info where (trailstatus = "04" or  trailstatus = "03" or trailstatus = "01" ) and (is_assign = 1)`)
	if data != nil && len(*data) > 0 {
		fool := false
		for _, v := range *data {
			trailstatus := gconv.String(v["trailstatus"])
			//坐席信息查询
			clueId := common.Int64All(v["id"])
			createtime := gconv.String(v["comeintime"])
			positionId := common.Int64All(v["position_id"])
			seatNumber := common.ObjToString(v["seatNumber"])
			if trailstatus == "01" {
				//该线索最近一次分配给该电销人员后存在通话记录
				uid := gconv.String(v["uid"])
				if createtime == "" {
					continue
				}
				if !PhoneState(uid, seatNumber, createtime) {
					continue
				}
			}
			//退私海处理
			nowTime := time.Now().Format(date.Date_Full_Layout)
			positionMap := map[int64]interface{}{}
			if TiDb.ExecTx("退出线索", func(tx *sql.Tx) bool {
				positionMap[positionId] = true
				recordId := TiDb.UpdateOrDeleteBySqlByTx(tx, `UPDATE dwd_f_crm_clue_info SET is_assign=0,position_id=null,seatNumber=null,updatetime = ?,comeinsource_open=null,level_open=null,next_trail_time=null,is_task=null,tasktime=null,taskstatus=null,comeinsource_private=null,tasksource=null WHERE id = ?`, nowTime, clueId) //,start_trail_time=null,content=null
				recordId2 := TiDb.InsertByTx(tx, "dwd_f_crm_clue_change_record", map[string]interface{}{
					"clue_id":     clueId,
					"position_id": positionId,
					"change_type": "退回公海",
					"new_value":   "所有人员私海已达上限",
					"createtime":  nowTime,
					"BCPCID":      common.GetRandom(32),
					"operator_id": -1,
				})
				recordId1 := TiDb.InsertByTx(tx, "dwd_f_crm_clue_change_record", map[string]interface{}{
					"clue_id":      clueId,
					"position_id":  positionId,
					"change_field": "position_id",
					"change_type":  "所属人变更",
					"old_value":    GetPositionName(seatNumber),
					"new_value":    "/",
					"createtime":   nowTime,
					"BCPCID":       common.GetRandom(32),
					"operator_id":  -1,
				})
				return recordId > -1 && recordId1 > -1 && recordId2 > -1
			}) {
				//发送消息，找wjh
				fool = true
				log.Println("达上限自动退出线索成功")
			} else {
				log.Println("达上限自动退出线索失败")
			}
		}
		//释放邮件
		//发送信息
		if fool {
			AutoReleaseNots()
		} else {
			CantBeAssignedNots()
		}
	}
}
func autoExitSea() {
	log.Println("自动退海任务开始")
	data := TiDb.SelectBySql(`select * from dwd_f_crm_clue_info where trailstatus = "02" and (is_assign = 1 or is_assign = 0)`)
	if data != nil && len(*data) > 0 {
		for _, v := range *data {
			clueId := common.Int64All(v["id"])
			positionId := common.Int64All(v["position_id"])
			seatNumber := common.ObjToString(v["seatNumber"])
			nowTime := time.Now().Format(date.Date_Full_Layout)
			// is_assign := common.IntAll(v["is_assign"])
			if TiDb.ExecTx("退出线索", func(tx *sql.Tx) bool {
				recordId := TiDb.UpdateOrDeleteBySqlByTx(tx, `UPDATE dwd_f_crm_clue_info SET is_assign=-1,position_id=null,seatNumber=null,updatetime = ?,comeinsource_open=null,level_open=null,next_trail_time=null,is_task=null,tasktime=null,taskstatus=null,comeinsource_private=null,tasksource=null WHERE id = ?`, nowTime, clueId) //,start_trail_time=null,content=null
				// ok1 := true
				// if is_assign == 1 {
				// 	ok1 = TiDb.DeleteByTx(tx, "dwd_f_crm_private_sea", map[string]interface{}{"clue_id": clueId})
				// } else if is_assign == 0 {
				// 	ok1 = TiDb.DeleteByTx(tx, "dwd_f_crm_open_sea", map[string]interface{}{"clue_id": clueId})
				// }
				recordId1 := TiDb.InsertByTx(tx, "dwd_f_crm_clue_change_record", map[string]interface{}{
					"clue_id":      clueId,
					"position_id":  positionId,
					"change_field": "position_id",
					"change_type":  "所属人变更",
					"old_value":    GetPositionName(seatNumber),
					"new_value":    "/",
					"createtime":   nowTime,
					"BCPCID":       common.GetRandom(32),
					"operator_id":  -1,
				})
				recordId2 := TiDb.InsertByTx(tx, "dwd_f_crm_clue_change_record", map[string]interface{}{
					"clue_id":      clueId,
					"position_id":  positionId,
					"change_field": "trailstatus",
					"change_type":  "基本信息变更",
					"old_value":    "空号停机",
					"new_value":    "流失",
					"createtime":   nowTime,
					"BCPCID":       common.GetRandom(32),
					"operator_id":  -1,
				})
				recordId3 := TiDb.InsertByTx(tx, "dwd_f_crm_clue_change_record", map[string]interface{}{
					"clue_id":     clueId,
					"position_id": positionId,
					"change_type": "退出任务车",
					"new_value":   "空号停机自动从线索池删除",
					"createtime":  nowTime,
					"BCPCID":      common.GetRandom(32),
					"operator_id": -1,
				})
				recordId4 := TiDb.InsertByTx(tx, "dwd_f_crm_clue_change_record", map[string]interface{}{
					"clue_id":     clueId,
					"position_id": positionId,
					"change_type": "退出公海",
					"new_value":   "空号停机自动从线索池删除",
					"createtime":  nowTime,
					"BCPCID":      common.GetRandom(32),
					"operator_id": -1,
				})
				return recordId > -1 && recordId1 > -1 && recordId2 > -1 && recordId3 > -1 && recordId4 > -1
			}) {
				log.Println("自动退出线索成功")
			} else {
				log.Println("自动退出线索失败")
			}
		}
	}
	datas := TiDb.SelectBySql("select  * from  dwd_f_crm_clue_info where   (trailstatus='09' or   trailstatus='00') and   is_assign=1")
	if datas != nil && len(*datas) > 0 {
		for _, v := range *datas {
			trailstatus := gconv.String(v["trailstatus"])
			clueId := common.Int64All(v["id"])
			positionId := common.Int64All(v["position_id"])
			seatNumber := common.ObjToString(v["seatNumber"])
			nowTime := time.Now().Format(date.Date_Full_Layout)
			// is_assign := common.IntAll(v["is_assign"])
			if TiDb.ExecTx("自动退海", func(tx *sql.Tx) bool {
				recordId := TiDb.UpdateOrDeleteBySqlByTx(tx, `UPDATE dwd_f_crm_clue_info SET is_assign=0,position_id=null,seatNumber=null,updatetime = ?,comeintime_open = ?,comeinsource_open=?,level_open=4,next_trail_time=null,is_task=null,tasktime=null,taskstatus=null,comeinsource_private=null,tasksource=null WHERE id = ?`, nowTime, nowTime, common.If(trailstatus == "00", 8, 11), clueId) //,content=null,start_trail_time=null
				recordId1 := TiDb.InsertByTx(tx, "dwd_f_crm_clue_change_record", map[string]interface{}{
					"clue_id":      clueId,
					"position_id":  positionId,
					"change_field": "position_id",
					"change_type":  "所属人变更",
					"old_value":    GetPositionName(seatNumber),
					"new_value":    "/",
					"createtime":   nowTime,
					"BCPCID":       common.GetRandom(32),
					"operator_id":  -1,
				})
				recordId2 := TiDb.InsertByTx(tx, "dwd_f_crm_clue_change_record", map[string]interface{}{
					"clue_id":      clueId,
					"position_id":  positionId,
					"change_field": "trailstatus",
					"change_type":  "基本信息变更",
					"old_value":    common.If(trailstatus == "00", "无意向客户", "拒绝客户"),
					"new_value":    "流失",
					"createtime":   nowTime,
					"BCPCID":       common.GetRandom(32),
					"operator_id":  -1,
				})
				recordId3 := TiDb.InsertByTx(tx, "dwd_f_crm_clue_change_record", map[string]interface{}{
					"clue_id":     clueId,
					"position_id": positionId,
					"change_type": "退出任务车",
					"new_value":   common.If(trailstatus == "00", "无意向客户自动退回公海", "拒绝客户自动退回公海"),
					"createtime":  nowTime,
					"BCPCID":      common.GetRandom(32),
					"operator_id": -1,
				})
				recordId4 := TiDb.InsertByTx(tx, "dwd_f_crm_clue_change_record", map[string]interface{}{
					"clue_id":     clueId,
					"position_id": positionId,
					"change_type": "退回公海",
					"new_value":   common.If(trailstatus == "00", "无意向客户自动退回公海", "拒绝客户自动退回公海"),
					"createtime":  nowTime,
					"BCPCID":      common.GetRandom(32),
					"operator_id": -1,
				})
				return recordId > -1 && recordId1 > -1 && recordId2 > -1 && recordId3 > -1 && recordId4 > -1
			}) {
				log.Println("自动退出私海成功")
			} else {
				log.Println("自动退出私海失败")
			}
		}
	}
	log.Println("自动退海任务结束")
}

func GetPositionName(seatNumber string) string {
	data := TiDb.SelectBySql(`select name from dwd_f_crm_personnel_management where resign = 0 and seat_number = ? limit 1`, seatNumber)
	if data != nil && len(*data) > 0 {
		return common.ObjToString((*data)[0]["name"])
	}
	return ""
}

func Thaw() {
	//3个工作日查询
	k := db.ThawDay
	nowTime := time.Now()
	for {
		if k == 0 {
			break
		}
		nowTime = nowTime.AddDate(0, 0, -1)

		if !IsBreak(nowTime) {
			continue
		}
		k--
	}
	nameMap := map[int64]string{}
	nameList := TiDb.SelectBySql("select  name,position_id from  dwd_f_crm_personnel_management")
	if nameList != nil && len(*nameList) > 0 {
		for _, v := range *nameList {
			nameMap[gconv.Int64(v["position_id"])] = gconv.String(v["name"])
		}
	}
	//查询
	layout := "2006-01-02 15:04:05"
	//三天以后退公海处理
	TiDb.SelectByBath(100, func(l *[]map[string]interface{}) bool {
		for _, v := range *l {
			clueId := gconv.Int64(v["id"])
			positionId := gconv.Int64(v["position_id"])
			freezeInt64 := int64(0)
			freezeStr := gconv.String(v["FREEZE_TIME"])
			if freezeStr != "" {
				t, _ := time.ParseInLocation(layout, freezeStr, time.Local)
				freezeInt64 = t.Unix()
			}
			if freezeInt64 < nowTime.Unix() {
				//超三天处理
				//分配状态改改
				if TiDb.Update("dwd_f_crm_clue_info", map[string]interface{}{"id": clueId}, map[string]interface{}{
					"is_assign":   0,
					"position_id": 0,
				}) {
					TiDb.Insert("dwd_f_crm_clue_change_record", map[string]interface{}{
						"clue_id":      clueId,
						"position_id":  0,
						"change_field": "position_id",
						"change_type":  "所属人变更",
						"old_value":    nameMap[positionId],
						"new_value":    "/",
						"createtime":   time.Now().Format(date.Date_Full_Layout),
						"BCPCID":       common.GetRandom(32),
						"operator_id":  -1,
					})
				}
				TiDb.Insert("dwd_f_crm_clue_change_record", map[string]interface{}{
					"clue_id":      clueId,
					"position_id":  0,
					"change_field": "position_id",
					"change_type":  "线索解冻",
					"new_value":    "自动退回公海",
					"createtime":   time.Now().Format(date.Date_Full_Layout),
					"BCPCID":       common.GetRandom(32),
					"operator_id":  -1,
				})
			} else {
				if !FindUpperLimit(gconv.String(positionId), "", false) {
					if TiDb.Update("dwd_f_crm_clue_info", map[string]interface{}{"id": clueId}, map[string]interface{}{
						"is_assign": 1,
					}) {
						TiDb.Insert("dwd_f_crm_clue_change_record", map[string]interface{}{
							"clue_id":      clueId,
							"position_id":  positionId,
							"change_field": "position_id",
							"change_type":  "所属人变更",
							"old_value":    "/",
							"new_value":    nameMap[positionId],
							"createtime":   time.Now().Format(date.Date_Full_Layout),
							"BCPCID":       common.GetRandom(32),
							"operator_id":  -1,
						})
					}
					TiDb.Insert("dwd_f_crm_clue_change_record", map[string]interface{}{
						"clue_id":      clueId,
						"position_id":  positionId,
						"change_field": "position_id",
						"change_type":  "线索解冻",
						"new_value":    "销售人员私海线索已释放",
						"createtime":   time.Now().Format(date.Date_Full_Layout),
						"BCPCID":       common.GetRandom(32),
						"operator_id":  -1,
					})
				}
			}
		}
		return true
	}, `select id,position_id,FREEZE_TIME from dwd_f_crm_clue_info where is_assign = -3 `)
}
func IsBreak(currentTime time.Time) bool {
	if currentTime.Weekday() == time.Sunday {
		isok := false
		for k, v := range DateMap {
			if currentTime.Format(date.Date_Short_Layout) == k && v == 2 {
				isok = true
			}
		}
		return isok
	} else {
		isok := true
		for k, v := range DateMap {
			if currentTime.Format(date.Date_Short_Layout) == k && v == 1 {
				isok = false
			}
		}
		return isok
	}
}
func PhoneState(uid, seatNumber, createtime string) bool {
	phoneData := TiDb.SelectBySql("select DISTINCT phone from  dwd_f_userbase_contacts  where   baseinfo_id= ?", uid)
	if phoneData == nil || len(*phoneData) == 0 {
		//数据不对跳过去
		return false
	}
	for _, v1 := range *phoneData {
		phone := gconv.String(v1["phone"])
		callData := TiDb.SelectBySql("select DISTINCT Exten  from  Call_Accounting.voice_record  where  CalledNo=? and  createtime>=? ORDER BY createTime", phone, createtime)
		if callData == nil || len(*callData) == 0 {
			//手机号没有通话记录
			continue
		}
		for _, v2 := range *callData {
			exten := gconv.String(v2["Exten"])
			if seatNumber == exten {
				return true
			}
		}
	}
	return false
}
func LockHandle() {
	time.Now()
	log.Println("线索锁定定时任务开始")
	timeData := -1 * (db.CornExp8 + 5)
	timeDate := time.Now().Add(time.Duration(timeData) * time.Second)
	timeStr := timeDate.Format("2006-01-02 15:04:05")
	a := 1
	fool := false
	page := 1
	pageSize := 1000
	for !fool {
		sqlStr := fmt.Sprintf(`select  id,cluename,position_id,updatetime,company_nature,company_verification,lock_status from  dwd_f_crm_clue_info  where  updatetime>="%s"  order by  updatetime limit %d OFFSET  %d`, timeStr, pageSize, (page-1)*pageSize)
		data := TiDb.SelectBySql(sqlStr)
		if len(*data) == 0 {
			fool = true
			break
		}
		for _, v := range *data {
			log.Println(a, gconv.String(v["updatetime"]))
			a++
			clue_id := gconv.Int64(v["id"])
			cluename := gconv.String(v["cluename"])
			position_id := gconv.String(v["position_id"])
			company_nature := gconv.Int64(v["company_nature"])
			company_verification := gconv.Int64(v["company_verification"])
			lock_status := gconv.Int64(v["lock_status"])
			//非集团在工商需要查询其他数据是否分配的有人
			if company_nature == 0 && company_verification == 1 {
				status4Data := TiDb.SelectBySql(fmt.Sprintf(`select   GROUP_CONCAT(DISTINCT position_id) as  position_id from   dwd_f_crm_clue_info  where    cluename="%s" and     position_id>0  `, cluename))
				if len(*status4Data) > 0 || status4Data != nil {
					position_id = gconv.String((*status4Data)[0]["position_id"])
				}
			}
			if company_nature == 1 && lock_status != 3 {
				//无需锁定处理
				if lock_status != 3 {
					go UpdateData(3, map[string]interface{}{
						"id": clue_id,
					}, 3, "")
				}
			} else if company_nature == 0 && company_verification == 1 {
				//已锁定处理
				if position_id != "" {
					go UpdateData(1, map[string]interface{}{
						"cluename":             cluename,
						"company_verification": 1,
						"company_nature":       0,
					}, 1, position_id)
				} else {
					go UpdateData(2, map[string]interface{}{
						"cluename":             cluename,
						"company_verification": 1,
						"company_nature":       0,
					}, 2, "")
				}
			} else {
				//未锁定处理
				if lock_status != 2 {
					go UpdateData(2, map[string]interface{}{
						"id": clue_id,
					}, 2, "")
				}
			}
		}
		page++
	}
	log.Println("线索锁定定时任务结束")
}

func UpdateData(lockStatus int64, query map[string]interface{}, status int, positionId string) {
	TiDb.Update("dwd_f_crm_clue_info", query, map[string]interface{}{
		"lock_status":      lockStatus,
		"lock_position_id": positionId,
	})
}