datatools/clueSync/autoTask.go

package main

import (
	"app.yhyue.com/moapp/jybase/mongodb"
	"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": "超时未跟进自动加车",
				}) {
					AddChangeRecord(nil, clueId, position_id, "", "加入任务车", "超时未跟进自动加车", "", nowTime, "", -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": "即将到达下次跟进时间",
					}) {
						AddChangeRecord(nil, clueId, position_id, "", "加入任务车", "即将到达下次跟进时间", "", nowTime2, "", -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"])
			clueName := gconv.String(v["cluename"])
			positionId := common.Int64All(v["position_id"])
			lockStatus := common.Int64All(v["lock_status"])
			seatNumber := common.ObjToString(v["seatNumber"])
			isGroup, isCommerce := gconv.Int(v["company_nature"]), gconv.Int(v["company_verification"])
			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 := AddChangeRecord(tx, clueId, positionId, "", "退回公海", "所有人员私海已达上限", "", nowTime, "", -1)
				recordId1 := AddChangeRecord(tx, clueId, positionId, "position_id", "所属人变更", "/", FindSeatNumberToName(seatNumber), nowTime, "", -1)
				AddLockeStatusChange(tx, clueId, positionId, 0, lockStatus, isGroup, isCommerce, clueName, nowTime, "delete")
				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"])
			clueName := gconv.String(v["cluename"])
			lockStatus := common.Int64All(v["lock_status"])
			isGroup, isCommerce := gconv.Int(v["company_nature"]), gconv.Int(v["company_verification"])
			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 := AddChangeRecord(tx, clueId, positionId, "position_id", "所属人变更", FindSeatNumberToName(seatNumber), "/", nowTime, "", -1)
				recordId2 := AddChangeRecord(tx, clueId, positionId, "trailstatus", "基本信息变更", "流失", "空号停机", nowTime, "", -1)
				recordId3 := AddChangeRecord(tx, clueId, positionId, "", "退出任务车", "空号停机自动从线索池删除", "", nowTime, "", -1)
				recordId4 := AddChangeRecord(tx, clueId, positionId, "", "退出公海", "空号停机自动从线索池删除", "", nowTime, "", -1)
				AddLockeStatusChange(tx, clueId, positionId, 0, lockStatus, isGroup, isCommerce, clueName, nowTime, "delete")
				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)
			clueName := gconv.String(v["cluename"])
			lockStatus := common.Int64All(v["lock_status"])
			isGroup, isCommerce := gconv.Int(v["company_nature"]), gconv.Int(v["company_verification"])
			// 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 := AddChangeRecord(tx, clueId, positionId, "position_id", "所属人变更", "/", FindSeatNumberToName(seatNumber), nowTime, "", -1)
				recordId2 := AddChangeRecord(tx, clueId, positionId, "trailstatus", "基本信息变更", "流失", gconv.String(common.If(trailstatus == "00", "无意向客户", "拒绝客户")), nowTime, "", -1)
				recordId3 := AddChangeRecord(tx, clueId, positionId, "", "退出任务车", gconv.String(common.If(trailstatus == "00", "无意向客户自动退回公海", "拒绝客户自动退回公海")), "", nowTime, "", -1)
				recordId4 := AddChangeRecord(tx, clueId, positionId, "", "退回公海", gconv.String(common.If(trailstatus == "00", "无意向客户自动退回公海", "拒绝客户自动退回公海")), "", nowTime, "", -1)
				AddLockeStatusChange(tx, clueId, positionId, 0, lockStatus, isGroup, isCommerce, clueName, nowTime, "delete")
				return recordId > -1 && recordId1 > -1 && recordId2 > -1 && recordId3 > -1 && recordId4 > -1
			}) {
				log.Println("自动退出私海成功")
			} else {
				log.Println("自动退出私海失败")
			}
		}
	}
	log.Println("自动退海任务结束")
}

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)
			clueName := gconv.String(v["cluename"])
			lockStatus := common.Int64All(v["lock_status"])
			isGroup, isCommerce := gconv.Int(v["company_nature"]), gconv.Int(v["company_verification"])
			freezeStr := gconv.String(v["FREEZE_TIME"])
			if freezeStr != "" {
				t, _ := time.ParseInLocation(layout, freezeStr, time.Local)
				freezeInt64 = t.Unix()
			}
			if freezeInt64 < nowTime.Unix() {
				nowTime := time.Now().Format(date.Date_Full_Layout)
				//超三天处理
				if TiDb.Update("dwd_f_crm_clue_info", map[string]interface{}{"id": clueId}, map[string]interface{}{
					"is_assign":   0,
					"position_id": 0,
					"seatNumber":  nil,
				}) {
					AddChangeRecord(nil, clueId, positionId, "position_id", "所属人变更", "/", nameMap[positionId], nowTime, "", -1)
				}
				AddChangeRecord(nil, clueId, positionId, "position_id", "线索解冻", "自动退回公海", "", nowTime, "", -1)
				AddLockeStatusChange(nil, clueId, positionId, 0, lockStatus, isGroup, isCommerce, clueName, nowTime, "delete")
			} else {
				//分配状态改改
				if !FindUpperLimit(gconv.String(positionId), "", false) {
					nowTime := time.Now().Format(date.Date_Full_Layout)
					if TiDb.Update("dwd_f_crm_clue_info", map[string]interface{}{"id": clueId}, map[string]interface{}{
						"is_assign": 1,
					}) {
						AddChangeRecord(nil, clueId, positionId, "position_id", "所属人变更", nameMap[positionId], "/", nowTime, "", -1)
					}
					AddChangeRecord(nil, clueId, positionId, "position_id", "线索解冻", "销售人员私海线索已释放", "", nowTime, "", -1)
					AddLockeStatusChange(nil, clueId, positionId, positionId, lockStatus, isGroup, isCommerce, clueName, nowTime, "add")
				}
			}
		}
		return true
	}, `select * 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,
	})
}

// 大会员子账号不作为电销线索
func ClueToDxTask() {
	//把所-5有的用户刷至公海
	ok := TiDb.Update("dwd_f_crm_clue_info", map[string]interface{}{"is_assign": -5}, map[string]interface{}{"is_assign": 0, "seatNumber": nil, "position_id": 0})
	if !ok {
		log.Println("更新dwd_f_crm_clue_info处理子账号出错")
		return
	}
	//查询在客成的用户
	res := TiDb.Query("SELECT clue_id,ent_id FROM `dwd_f_csm_customer_info` WHERE is_transfer = 0")
	if res == nil || len(*res) <= 0 {
		log.Println("查dwd_f_csm_customer_info 客成用户异常")
		return
	}
	log.Println("客成用户量:", len(*res))
	for _, val := range *res {
		//log.Println("ent_id", val["ent_id"])
		clue_id := gconv.Int(val["clue_id"])
		childIds := []string{}
		if gconv.String(val["ent_id"]) == "" || gconv.String(val["ent_id"]) == "0" {
			continue
		}
		if mongodb.IsObjectIdHex(gconv.String(val["ent_id"])) { //mongodb  个人身份大会员
			//查询个人大会员下的子账号
			query := map[string]interface{}{"s_member_mainid": gconv.String(val["ent_id"])}
			childAccount, _ := Mgo.Find("user", query, "", `"_id":1`, false, -1, -1)
			if childAccount != nil && len(*childAccount) > 0 {
				log.Println("个人大会员子账号数量：", gconv.String(val["ent_id"]), len(*childAccount))
				for _, v := range *childAccount {
					childIds = append(childIds, mongodb.BsonIdToSId(v["_id"]))
				}
			}
		} else { //企业大会员
			//根据企业id查询企业下的员工
			if gconv.Int(val["ent_id"]) == 0 {
				continue
			}
			data := TiDb.Query("SELECT userid,uid FROM dwd_f_userbase_id_mapping WHERE ent_id = ?", gconv.Int(val["ent_id"]))
			if data != nil && len(*data) > 0 {
				log.Println("企业下员工数：", gconv.Int(val["ent_id"]), len(*data))
				for _, vv := range *data {
					childIds = append(childIds, gconv.String(vv["userid"]))
				}
			}
		}
		//更新线索信息
		if len(childIds) > 0 {
			ids := ""
			for k, vv := range childIds {
				if k == 0 {
					ids += fmt.Sprintf("'%s'", vv)
				} else {
					ids += fmt.Sprintf(",'%s'", vv)
				}
			}
			//log.Println("线索ids:", ids)
			sql := fmt.Sprintf("UPDATE dwd_f_crm_clue_info SET is_assign = -5 WHERE id != %d and userid IN (%s)", clue_id, ids)
			log.Println("sql:", sql)
			up := TiDb.UpdateOrDeleteBySql(sql)
			if up < 0 {
				log.Println("更新线索为-5出错", childIds)
				return
			}
		}
	}
	log.Println("定时任务完成")
}