datatools/clueSync/trailContent.go

package main

import (
	"log"
	"regexp"

	"app.yhyue.com/moapp/jybase/mongodb"

	"strings"
	"time"

	"app.yhyue.com/moapp/jybase/common"
	"app.yhyue.com/moapp/jybase/date"
	"github.com/tealeg/xlsx"
)

func trail() {
	log.Println("合力亿捷跟进历史任务开始")
	nameReg := regexp.MustCompile("【.*?】")
	filePath := "./xlsx/跟进历史" + cfg.XlsxNum + ".xlsx"
	log.Println("文件名 ", filePath)
	xlFile, _ := xlsx.OpenFile(filePath)
	//获取行数
	length := len(xlFile.Sheets[0].Rows)
	log.Println("获取行数 ", length)
	//遍历sheet
	for _, sheet := range xlFile.Sheets {
		//遍历每一行
		for rowIndex, row := range sheet.Rows {
			//跳过第一行表头信息
			if rowIndex == 0 {
				continue
			}
			createtime := row.Cells[0].Value
			name := row.Cells[1].Value
			nexttype := row.Cells[2].Value
			content := row.Cells[3].Value
			phone := row.Cells[6].Value
			positionId, clueId := getSale(name, phone)
			log.Println("positionId,clueId ", positionId, clueId)
			if clueId > 0 {
				if nexttype == "无类型" {
					TiDb.Insert("dwd_f_crm_trail_content", map[string]interface{}{
						"clue_id":     clueId,
						"position_id": positionId,
						"createtime":  createtime,
						"content":     content,
						"operator_id": positionId,
					})
				} else {
					old_value := strings.ReplaceAll(strings.ReplaceAll(nameReg.FindAllString(content, -1)[0], "【", ""), "】", "")
					new_value := strings.ReplaceAll(strings.ReplaceAll(nameReg.FindAllString(content, -1)[1], "【", ""), "】", "")
					TiDb.Insert("dwd_f_crm_clue_change_record", map[string]interface{}{
						"clue_id":      clueId,
						"position_id":  positionId,
						"createtime":   createtime,
						"change_field": common.If(nexttype == "所属人变更", "position_id", "trailstatus"),
						"change_type":  common.If(nexttype == "所属人变更", nexttype, "基本信息变更"),
						"old_value":    common.If(old_value == "销售线索", "商机线索", old_value),
						"new_value":    common.If(new_value == "销售线索", "商机线索", new_value),
						"operator_id":  positionId,
					})
				}
			}
		}
	}
}

func getSale(name, phone string) (positionId, clueId int64) {
	saleData := TiDb.FindOne("dwd_f_crm_personnel_management", map[string]interface{}{"name": name, "resign": 0}, "", "")
	if saleData != nil && len(*saleData) > 0 {
		positionId = common.Int64All((*saleData)["position_id"])
	} else {
		positionId = -1
	}
	if positionId == 0 {
		positionId = -1
	}
	clueInfo := TiDb.FindOne("dwd_f_crm_clue_info", map[string]interface{}{"phone": phone}, "", "")
	if clueInfo != nil && len(*clueInfo) > 0 {
		clueId = common.Int64All((*clueInfo)["id"])
	}
	return
}

func getSeatNumberPositionId(seatNumber string) (positionId int64) {
	saleData := TiDb.FindOne("dwd_f_crm_personnel_management", map[string]interface{}{"seat_number": seatNumber}, "", "")
	if saleData != nil && len(*saleData) > 0 {
		positionId = common.Int64All((*saleData)["position_id"])
	}
	return
}

func getXlsx() {
	filePath := "./code1.xlsx"
	nowTime := time.Now().Format(date.Date_Full_Layout)
	xlFile, _ := xlsx.OpenFile(filePath)
	//获取行数
	// length := len(xlFile.Sheets[0].Rows)
	//开辟除表头外的行数的数组内存
	// resourceArr := make([]map[string]interface{}, length-1)
	//遍历sheet
	for _, sheet := range xlFile.Sheets {
		//遍历每一行
		for rowIndex, row := range sheet.Rows {
			//跳过第一行表头信息
			if rowIndex == 0 {
				continue
			}
			company := row.Cells[1].Value
			name := row.Cells[2].Value
			position := row.Cells[3].Value
			phone := row.Cells[4].Value
			if phone != "" {
				data := TiDb.FindOne("dwd_f_crm_clue_info", map[string]interface{}{"phone": phone}, "", "")
				if data != nil && len(*data) > 0 {
					isAssign := common.IntAll((*data)["is_assign"])
					clueId := common.Int64All((*data)["id"])
					if isAssign == 1 {
						//在私海
						TiDb.Update("dwd_f_crm_clue_info", map[string]interface{}{"id": clueId}, map[string]interface{}{
							"is_assign":    1,
							"comeintime":   nowTime,
							"updatetime":   nowTime,
							"top_cluetype": "377",
							"sub_cluetype": "381",
						})
						TiDb.Update("dwd_f_crm_private_sea", map[string]interface{}{"clue_id": clueId}, map[string]interface{}{
							"comeintime":   nowTime,
							"comeinsource": 2,
							"is_task":      1,
							"task_time":    nowTime,
							"tasktime":     nowTime,
							"taskstatus":   0,
							"tasksource":   "线索自动分配-批量导入-6月份获取拟建项目清单留资",
						})
					} else {
						//在公海
						positionId, seatNumber, _, _, _ := autoDraw("C", "", 0, 0)
						TiDb.Update("dwd_f_crm_clue_info", map[string]interface{}{"id": clueId}, map[string]interface{}{
							"seatNumber":   seatNumber,
							"position_id":  positionId,
							"is_assign":    1,
							"comeintime":   nowTime,
							"updatetime":   nowTime,
							"trailstatus":  "01",
							"top_cluetype": "377",
							"sub_cluetype": "381",
						})
						TiDb.Delete("dwd_f_crm_open_sea", map[string]interface{}{"clue_id": clueId})
						TiDb.Insert("dwd_f_crm_private_sea", map[string]interface{}{
							"clue_id":      clueId,
							"seatNumber":   seatNumber,
							"position_id":  positionId,
							"comeintime":   nowTime,
							"comeinsource": 2,
							"is_task":      1,
							"task_time":    nowTime,
							"tasktime":     nowTime,
							"taskstatus":   0,
							"tasksource":   "线索自动分配-批量导入-6月份获取拟建项目清单留资",
						})
					}
				} else {
					//无线索
					base := TiDb.FindOne("dwd_f_userbase_baseinfo", map[string]interface{}{"phone": phone}, "", "")
					if base != nil && len(*base) > 0 {
						uId := common.ObjToString((*base)["uid"])
						userId := common.ObjToString((*base)["userid"])
						positionId, seatNumber, _, _, _ := autoDraw("C", "", 0, 0)
						clueId := TiDb.Insert("dwd_f_crm_clue_info", map[string]interface{}{
							"userid":       userId,
							"uid":          uId,
							"seatNumber":   seatNumber,
							"position_id":  positionId,
							"is_assign":    1,
							"comeintime":   nowTime,
							"createtime":   nowTime,
							"updatetime":   nowTime,
							"cluename":     common.If(company != "", company, phone),
							"top_cluetype": "377",
							"sub_cluetype": "381",
							"trailstatus":  "01",
							"name":         name,
							"phone":        phone,
							"position":     position,
						})
						if clueId > -1 {
							TiDb.Insert("dwd_f_crm_private_sea", map[string]interface{}{
								"clue_id":      clueId,
								"seatNumber":   seatNumber,
								"position_id":  positionId,
								"comeintime":   nowTime,
								"comeinsource": 2,
								"is_task":      1,
								"task_time":    nowTime,
								"tasktime":     "2023-06-12 10:00:00",
								"taskstatus":   0,
								"tasksource":   "线索自动分配-批量导入-6月份获取拟建项目清单留资",
							})
						}
					}
				}
			}
		}
	}
}

func getXlsx2() {
	filePath := "./code2.xlsx"
	nowTime := time.Now().Format(date.Date_Full_Layout)
	xlFile, _ := xlsx.OpenFile(filePath)
	//获取行数
	// length := len(xlFile.Sheets[0].Rows)
	//开辟除表头外的行数的数组内存
	// resourceArr := make([]map[string]interface{}, length-1)
	//遍历sheet
	for _, sheet := range xlFile.Sheets {
		//遍历每一行
		for rowIndex, row := range sheet.Rows {
			//跳过第一行表头信息
			if rowIndex == 0 {
				continue
			}
			userId := row.Cells[0].Value
			company := row.Cells[5].Value
			name := row.Cells[1].Value
			position := row.Cells[6].Value
			phone := row.Cells[7].Value
			positionId, seatNumber := int64(2047781), "8040"
			if phone != "" {
				data := TiDb.FindOne("dwd_f_crm_clue_info", map[string]interface{}{"userid": userId}, "", "")
				if data != nil && len(*data) > 0 {
					isAssign := common.IntAll((*data)["is_assign"])
					clueId := common.Int64All((*data)["id"])
					if isAssign != 1 {
						//不在私海
						TiDb.Update("dwd_f_crm_clue_info", map[string]interface{}{"id": clueId}, map[string]interface{}{
							"seatNumber":   seatNumber,
							"position_id":  positionId,
							"is_assign":    1,
							"comeintime":   nowTime,
							"updatetime":   nowTime,
							"trailstatus":  "01",
							"top_cluetype": "377",
							"sub_cluetype": "382",
						})
						TiDb.Delete("dwd_f_crm_open_sea", map[string]interface{}{"clue_id": clueId})
						TiDb.Insert("dwd_f_crm_private_sea", map[string]interface{}{
							"clue_id":      clueId,
							"seatNumber":   seatNumber,
							"position_id":  positionId,
							"comeintime":   nowTime,
							"comeinsource": 2,
							"is_task":      1,
							"task_time":    nowTime,
							"tasktime":     nowTime,
							"taskstatus":   0,
							"tasksource":   "线索自动分配-批量导入-医械厂商线索",
						})
					}
				} else {
					//无线索
					base := TiDb.FindOne("dwd_f_userbase_baseinfo", map[string]interface{}{"userid": userId}, "", "")
					if base != nil && len(*base) > 0 {
						uId := common.ObjToString((*base)["uid"])
						clueId := TiDb.Insert("dwd_f_crm_clue_info", map[string]interface{}{
							"userid":       userId,
							"uid":          uId,
							"seatNumber":   seatNumber,
							"position_id":  positionId,
							"is_assign":    1,
							"comeintime":   nowTime,
							"createtime":   nowTime,
							"updatetime":   nowTime,
							"cluename":     common.If(company != "", company, phone),
							"top_cluetype": "377",
							"sub_cluetype": "382",
							"trailstatus":  "01",
							"name":         name,
							"phone":        phone,
							"position":     position,
						})
						if clueId > -1 {
							TiDb.Insert("dwd_f_crm_private_sea", map[string]interface{}{
								"clue_id":      clueId,
								"seatNumber":   seatNumber,
								"position_id":  positionId,
								"comeintime":   nowTime,
								"comeinsource": 2,
								"is_task":      1,
								"task_time":    nowTime,
								"tasktime":     "2023-06-12 10:00:00",
								"taskstatus":   0,
								"tasksource":   "线索自动分配-批量导入-医械厂商线索",
							})
						}
					}
				}
			}
		}
	}
}

func clueInfoSync() {
	TiDb.SelectByBath(500, func(l *[]map[string]interface{}) bool {
		for _, v := range *l {
			id := common.Int64All(v["id"])
			is_assign := common.IntAll(v["is_assign"])
			comeintime := common.ObjToString(v["comeintime"])
			comeintime_open := common.ObjToString(v["comeintime_open"])
			trail_time, trail_times, next_trail_time := common.ObjToString(v["createtime"]), "", ""
			tasktime := common.ObjToString(v["tasktime"])
			task_time := common.ObjToString(v["task_time"])
			is_task := common.IntAll(v["is_task"])
			taskstatus := common.IntAll(v["taskstatus"])
			comeinsource_private := common.IntAll(v["comeinsource_private"])
			tasksource := common.ObjToString(v["tasksource"])
			out_task_time := common.ObjToString(v["out_task_time"])
			out_task_status := common.IntAll(v["out_task_status"])
			comeinsource_open := common.IntAll(v["comeinsource_open"])
			level_open := common.IntAll(v["level_open"])
			clue_level := common.ObjToString(v["clue_level"])
			var (
				trail_time_s time.Time
				comeintime_s time.Time
			)
			if trail_time != "" && comeintime != "" {
				trail_time_s, _ = time.ParseInLocation(date.Date_Full_Layout, trail_time, time.Local)
				comeintime_s, _ = time.ParseInLocation(date.Date_Full_Layout, comeintime, time.Local)
				if trail_time_s.Unix() > comeintime_s.Unix() {
					trail_times = trail_time
					next_trail_time = common.ObjToString(v["next_time"])
				}
			}
			updateData := map[string]interface{}{
				"comeintime":      common.If(comeintime != "", comeintime, nil),
				"comeintime_open": common.If(comeintime_open != "", comeintime_open, nil),
			}
			if is_assign == 1 {
				updateData["trail_time"] = common.If(trail_times != "", trail_times, nil)
				updateData["next_trail_time"] = common.If(next_trail_time != "", next_trail_time, nil)
				updateData["is_task"] = is_task
				updateData["tasktime"] = common.If(tasktime != "", tasktime, nil)
				updateData["task_time"] = common.If(task_time != "", task_time, nil)
				updateData["taskstatus"] = taskstatus
				updateData["comeinsource_private"] = comeinsource_private
				updateData["tasksource"] = tasksource
				updateData["out_task_time"] = common.If(out_task_time != "", out_task_time, nil)
				updateData["out_task_status"] = out_task_status
			} else if is_assign == 0 {
				updateData["comeinsource_open"] = comeinsource_open
				updateData["level_open"] = level_open
				updateData["clue_level"] = clue_level
			}
			ok := TiDb.Update("dwd_f_crm_clue_info", map[string]interface{}{"id": id}, updateData)
			if ok {
				log.Println("线索表同步成功 ", id)
			} else {
				log.Println("线索表同步失败！！！ ", id, updateData)
			}
		}
		return true
	}, `SELECT a.id,a.is_assign,c.comeintime,b.comeintime as comeintime_open,d.createtime,d.next_time,c.tasktime,c.task_time,c.is_task,c.taskstatus,c.comeinsource as comeinsource_private,c.tasksource,c.out_task_time,c.out_task_status,b.comeinsource as comeinsource_open,b.level as level_open,b.clue_level
 			FROM dwd_f_crm_clue_info a 
				LEFT JOIN dwd_f_crm_open_sea b ON b.clue_id=a.id 
				LEFT JOIN dwd_f_crm_private_sea c ON c.clue_id=a.id 
				LEFT JOIN dwd_f_crm_trail_content d ON d.clue_id=a.id and d.position_id=a.position_id`)
}

func app_xzcyh_sync() {
	query := map[string]interface{}{"source": "app_xzcyh"}
	session := Mgo.GetMgoConn()
	defer func() {
		Mgo.DestoryMongoConn(session)
	}()
	iter := session.DB(cfg.Mgo.DbName).C("saleLeads").Find(&query).Sort("_id").Iter()
	thisData := map[string]interface{}{}
	for {
		if !iter.Next(&thisData) {
			break
		}
		userId := common.ObjToString(thisData["userid"])
		keyword := common.ObjArrToStringArr(thisData["keyword"].([]interface{}))
		if !mongodb.IsObjectIdHex(userId) {
			userMapping := TiDb.FindOne("dwd_f_userbase_id_mapping", map[string]interface{}{"position_id": userId}, "", "")
			if userMapping != nil && len(*userMapping) > 0 {
				userId = common.ObjToString((*userMapping)["userid"])
			}
		}
		data := TiDb.FindOne("dwd_f_crm_clue_info", map[string]interface{}{"userid": userId}, "", "")
		if data != nil && len(*data) > 0 {
			business_scope := common.ObjToString((*data)["business_scope"])
			clueUpdateData := map[string]interface{}{}
			keywords := ""
			if len(keyword) > 0 && keyword[0] != "" {
				keywords = strings.Join(keyword, ",")
				if business_scope != "" {
					clueUpdateData["business_scope"] = business_scope + "," + keywords
				} else {
					clueUpdateData["business_scope"] = keywords
				}
				ok := TiDb.Update("dwd_f_crm_clue_info", map[string]interface{}{"userid": userId}, clueUpdateData)
				if ok {
					log.Println("主营业务修改成功", userId, keywords)
				} else {
					log.Println("主营业务修改失败！！！", userId, keywords)
				}
			}
		}
	}
}

func lzhSync() {
	nowTime, count := time.Now().Format("2006-01-02 15:04:05"), 0
	data := TiDbData.SelectBySql(`SELECT * FROM customer where owner in (2646,8050) and status999 in ("status0","status1","status2","status3")`)
	if data != nil {
		for _, v := range *data {
			count++
			owner := common.ObjToString(v["owner"])
			positionId, seatNumber, trailstatus, uId, userId := int64(0), "", "", "", ""
			if owner == "2646" {
				positionId = 1205321180
				seatNumber = "2646"
			} else {
				positionId = 1205321184
				seatNumber = "8050"
			}
			phone := common.ObjToString(v["phone"])
			status := common.ObjToString(v["status999"])
			cluename := common.ObjToString(v["company"])
			name := common.ObjToString(v["username"])
			position := common.ObjToString(v["job"])
			if status == "status0" {
				trailstatus = "08"
			} else if status == "status1" {
				trailstatus = "07"
			} else if status == "status2" {
				trailstatus = "06"
			} else {
				trailstatus = "05"
			}
			cdata := TiDb.FindOne("dwd_f_userbase_contacts", map[string]interface{}{"phone": phone}, "", "")
			if cdata != nil {
				uId = common.ObjToString((*cdata)["baseinfo_id"])
				cluedata := TiDb.FindOne("dwd_f_crm_clue_info", map[string]interface{}{"uid": uId}, "", "")
				if cluedata != nil {
					log.Println("第", count, "条已有线索", phone, uId)
					seatNumbersss := common.ObjToString((*cluedata)["seatNumber"])
					if seatNumbersss == "" {
						TiDb.Update("dwd_f_crm_clue_info", map[string]interface{}{"uid": uId}, map[string]interface{}{
							"seatNumber":           seatNumber,
							"position_id":          positionId,
							"is_assign":            1,
							"comeintime":           nowTime,
							"updatetime":           nowTime,
							"trailstatus":          trailstatus,
							"is_task":              1,
							"task_time":            nowTime,
							"tasktime":             nowTime,
							"taskstatus":           0,
							"comeinsource_private": 2,
						})
					}
				} else {
					udata := TiDb.FindOne("dwd_f_userbase_baseinfo", map[string]interface{}{"uid": uId}, "", "")
					if udata != nil {
						userId = common.ObjToString((*udata)["userid"])
						clueId := TiDb.Insert("dwd_f_crm_clue_info", map[string]interface{}{
							"userid":               userId,
							"uid":                  uId,
							"seatNumber":           seatNumber,
							"position_id":          positionId,
							"is_assign":            1,
							"comeintime":           nowTime,
							"createtime":           nowTime,
							"updatetime":           nowTime,
							"cluename":             cluename,
							"top_cluetype":         "4",
							"sub_cluetype":         "154",
							"trailstatus":          trailstatus,
							"name":                 name,
							"phone":                phone,
							"position":             position,
							"comeinsource_private": 2,
							"is_task":              1,
							"task_time":            nowTime,
							"tasktime":             nowTime,
							"taskstatus":           0,
						})
						if clueId > 0 {
							log.Println("第", count, "条保存成功", phone, uId)
						}
					}
				}
			}

		}
	}
}