datatools/clueSync/kc.go

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"strconv"
	"strings"
	"time"

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

func kcSync() {
	log.Println("客户成功系统移交定时任务开始")
	sql := `select * from dwd_f_userbase_order_info where pay_money > 0 and autoUpdate >= "` + cfg.LastkcTime + `" order by autoUpdate asc`
	data := TiDb.SelectBySql(sql)
	if data != nil && *data != nil && len(*data) > 0 {
		for _, v := range *data {
			product_type := common.ObjToString(v["product_type"])
			data_spec := common.ObjToString(v["data_spec"])
			starttime := common.ObjToString(v["vip_starttime"])
			order_status := common.IntAll(v["order_status"])
			if (product_type == "大会员" || product_type == "企业商机管理") && order_status == 1 && data_spec != "dhy4" && !strings.HasPrefix(starttime, "2099") {
				status := kcJob(v)
				if status == 0 {
					break
				}
			}
			cfg.LastkcTime = common.ObjToString(v["autoUpdate"])
		}
	}
	common.WriteSysConfig(&cfg)
	log.Println("客户成功系统移交定时任务结束")
}

func kcJob(data map[string]interface{}) int {
	nowTime := time.Now().Format(date.Date_Full_Layout)
	uId, entId, clueId, saveMap, name, positionId, status := common.ObjToString(data["uid"]), fmt.Sprint(data["ent_id"]), int64(0), map[string]interface{}{}, "", int64(0), 1
	clueData := TiDb.FindOne("dwd_f_crm_clue_info", map[string]interface{}{"uid": uId}, "", "")
	if clueData != nil && len(*clueData) > 0 {
		clueId = common.Int64All((*clueData)["id"])
		if cicount := TiDb.Count("dwd_f_csm_customer_info", map[string]interface{}{"clue_id": clueId}); cicount > 0 {
			return 1
		}
		starttime := common.ObjToString(data["vip_starttime"])
		endtime := common.ObjToString(data["vip_endtime"])
		// user_role := common.IntAll(data["user_role"])
		buy_subject := common.IntAll(data["buy_subject"])
		product_type := common.ObjToString(data["product_type"])
		data_spec := common.ObjToString(data["data_spec"])
		userName := common.ObjToString((*clueData)["name"])
		phone := common.ObjToString((*clueData)["phone"])
		product, company_name := 0, common.ObjToString(data["company_name"])
		productMap := map[string]int{
			"dhy6":   1,
			"dhy7":   2,
			"dhy3":   4,
			"dhy1":   5,
			"dhy2":   6,
			"dhy5":   7,
			"企业商机管理": 8,
		}
		if buy_subject == 1 {
			entId = common.ObjToString(data["userid"])
		}
		if product_type == "企业商机管理" {
			product = productMap[product_type]
			powerData := TiDb.FindOne("dwd_f_data_equity_info", map[string]interface{}{"phone": phone, "product_type": "商机管理"}, "", "comeintime desc")
			if powerData != nil {
				starttime = common.ObjToString((*powerData)["starttime"])
				endtime = common.ObjToString((*powerData)["endtime"])
			} else {
				return 0
			}
		} else {
			if data_spec == "dhy6" {
				product = 1
				filter := common.ObjToString(data["filter"])
				filterMap := map[string]interface{}{}
				json.Unmarshal([]byte(filter), &filterMap)
				if len(filterMap) > 0 {
					areaCount := common.IntAll(filterMap["areaCount"])
					if areaCount == 1 {
						product = 3
					}
				}
			} else {
				product = productMap[data_spec]
			}
		}
		//同一公司名称（以客户详情-组织机构-公司名称）下的线索需分配给同1人
		//合力亿捷线索同步
		customerData := TiDbData.FindOne("customer", map[string]interface{}{"phone": phone}, "", "")
		if customerData != nil {
			status999 := common.ObjToString((*customerData)["status999"])               //线索状态
			source := common.ObjToString((*customerData)["source"])                     //销售来源
			customerNeeds := common.ObjToString((*customerData)["customerNeeds"])       //客户需求
			wantGoods := common.ObjToString((*customerData)["wantGoods"])               //意向产品
			job := common.ObjToString((*customerData)["job"])                           //职位
			createTime := common.ObjToString((*customerData)["createTime"])             //创建时间
			lastUpdateTime := common.ObjToString((*customerData)["lastUpdateTime"])     //更新时间
			area := common.ObjToString((*customerData)["area"])                         //关注区域
			customerBudget := common.ObjToString((*customerData)["customerBudget"])     //客户预算
			isPolicymaker := common.ObjToString((*customerData)["customerBudget"])      //是否为决策人
			belongToIndustry := common.ObjToString((*customerData)["belongToIndustry"]) //所属行业
			seatNumber := common.ObjToString((*customerData)["empNo"])                  //坐席号
			statusMap := map[string]string{
				"status0":     "08",
				"status1":     "07",
				"status2":     "06",
				"status3":     "05",
				"status4":     "04",
				"status6":     "00",
				"status5":     "01",
				"spaceNumber": "02",
				"phoneDown":   "02",
			}
			if status999 != "" {
				status999 = statusMap[status999]
			} else {
				status999 = "01"
			}
			top_cluetype := "172"
			sub_cluetype := ""
			cluetypeData := TiDb.FindOne("dwd_d_crm_cluetype_code", map[string]interface{}{"name": "其他-" + source}, "", "")
			if cluetypeData != nil {
				sub_cluetype = common.ObjToString((*cluetypeData)["code"])
			}
			wantGoods = strings.ReplaceAll(wantGoods, "剑鱼大会员", "大会员")
			wantGoods = strings.ReplaceAll(wantGoods, "数据导出", "数据流量包")
			if isPolicymaker == "是" {
				isPolicymaker = "决策人"
			} else {
				isPolicymaker = "使用人"
			}
			areaCodeArr := []string{}
			areaCode := ""
			for _, v := range strings.Split(area, ",") {
				areaCodeArr = append(areaCodeArr, AreaCode[v])
			}
			if len(areaCodeArr) > 0 {
				areaCode = strings.Join(areaCodeArr, ",")
			}
			SalePositionId := getSeatNumberPositionId(seatNumber)
			TiDb.Update("dwd_f_crm_clue_info", map[string]interface{}{"uid": uId}, map[string]interface{}{
				"position_id":         SalePositionId,
				"seatNumber":          seatNumber,
				"is_assign":           -2,
				"createtime":          createTime,
				"updatetime":          lastUpdateTime,
				"top_cluetype":        top_cluetype,
				"sub_cluetype":        sub_cluetype,
				"customer_demand":     customerNeeds,
				"intended_products":   wantGoods,
				"customer_budget":     customerBudget,
				"contact_type":        isPolicymaker,
				"position":            job,
				"industry":            belongToIndustry,
				"follow_project_area": areaCode,
			})
		}
		//
		entIds := common.IntAll(data["ent_id"])
		positionId, name = cAutoDraw(entIds)
		saveMap = map[string]interface{}{
			"clue_id":                   clueId,
			"transfertime":              nowTime,
			"position_id":               positionId,
			"name":                      name,
			"service_starttime":         starttime,
			"service_endtime":           endtime,
			"ent_id":                    entId,
			"is_task":                   1,
			"tasktime":                  nowTime,
			"taskstatus":                0,
			"tasksource":                "1",
			"is_admin":                  1,
			"product_access":            product,
			"buy_subject":               buy_subject,
			"relationship_building_way": 1,
			"inventory_way":             1,
			"training_way":              1,
			"is_pre_sales_training":     0,
			"service_stage":             1,
			"company_name":              company_name,
		}
		cId, ok, updateId1, updateId2, updateId3 := int64(-1), false, int64(-1), int64(-1), int64(-1)
		if TiDb.ExecTx("保存客户", func(tx *sql.Tx) bool {
			cId = TiDb.InsertByTx(tx, "dwd_f_csm_customer_info", saveMap)
			ok = TiDb.UpdateByTx(tx, "dwd_f_crm_clue_info", map[string]interface{}{"id": clueId}, map[string]interface{}{"is_transfer": 1, "updatetime": nowTime, "name": userName})
			updateId1 = 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,
			})
			updateId2 = 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,
			})
			updateId3 = 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":    "/",
				"new_value":    name,
				"createtime":   nowTime,
				"BCPCID":       common.GetRandom(32),
				"operator_id":  -1,
			})
			return cId > -1 && ok && updateId1 > -1 && updateId2 > -1 && updateId3 > -1
		}) {
			TiDb.UpdateOrDeleteBySql(`update dwd_f_csm_customer_autodraw_record set count = count + 1 where name = ?`, name)
			log.Println("保存客户成功")
		} else {
			log.Println("保存客户失败！！！", clueId, cId, ok, updateId1, updateId2, updateId3, " 用户信息 ", name, positionId, uId)
		}
		return status
	} else {
		log.Println("客户未查到线索！！！", uId)
		if isExists, _ := redis.Exists("bidx", "bidx_uId_"+uId); isExists {
			redisInt := redis.GetInt("bidx", "bidx_uId_"+uId)
			if redisInt > 2 {
				return 1
			} else {
				redis.Incr("bidx", "bidx_uId_"+uId)
				return 0
			}
		} else {
			redis.Put("bidx", "bidx_uId_"+uId, 1, 3600)
			return 0
		}
	}
}

func cAutoDraw(entId int) (positionId int64, name string) {
	query := `SELECT name,position_id FROM dwd_d_crm_department_level_succbi WHERE bi_pcode = (SELECT bi_code FROM dwd_d_crm_department_level_succbi WHERE name = "客户成功组") and resign = 0`
	data := TiDb.SelectBySql(query)
	if data != nil && len(*data) > 0 {
		if entId > 0 {
			entdata := TiDb.FindOne("dwd_f_csm_customer_info", map[string]interface{}{"ent_id": entId}, "", "")
			if entdata != nil && len(*entdata) > 0 {
				positionId = common.Int64All((*entdata)["position_id"])
				name = common.ObjToString((*entdata)["name"])
				return
			}
		}
		sql := `select a.name,a.count from dwd_f_csm_customer_autodraw_record a INNER JOIN dwd_d_crm_department_level_succbi b on (a.name = b.name) where b.resign = 0`
		countData := TiDb.SelectBySql(sql)
		if countData != nil && len(*countData) > 0 {
			for _, v := range *data {
				//判断是否有新员工
				isOk := false
				for _, vv := range *countData {
					if common.ObjToString(v["name"]) == common.ObjToString(vv["name"]) {
						isOk = true
					}
				}
				//有新员工直接分给新员工
				if !isOk {
					name = common.ObjToString(v["name"])
					rData := TiDb.FindOne("dwd_f_csm_customer_autodraw_record", map[string]interface{}{}, "", "count desc")
					TiDb.Insert("dwd_f_csm_customer_autodraw_record", map[string]interface{}{
						"name":  name,
						"count": common.Int64All((*rData)["count"]),
					})
					break
				}
			}
			res := int64(0)
			countres := 0
			for _, v := range *countData {
				if countres == 0 {
					res = common.Int64All(v["count"])
					name = common.ObjToString(v["name"])
				} else {
					if common.Int64All(v["count"]) <= res {
						res = common.Int64All(v["count"])
						name = common.ObjToString(v["name"])
					}
				}
				countres++
			}
		}
		for _, v := range *data {
			if name == common.ObjToString(v["name"]) {
				positionId = common.Int64All(v["position_id"])
			}
		}
	}
	return
}

func kcAuto() {
	log.Println("客户成功系统自动进入任务车定时任务开始")
	nowTime := time.Now().Format(date.Date_Full_Layout)
	sql := `select * from dwd_f_csm_customer_info where ((relationship_building_way != 1 and inventory_way = 1) or (inventory_way != 1 and training_way = 1)) and is_task = 0`
	data := TiDb.SelectBySql(sql)
	if data != nil && *data != nil && len(*data) > 0 {
		for _, v := range *data {
			relationship_building_way := common.IntAll(v["relationship_building_way"])
			inventory_way := common.IntAll(v["inventory_way"])
			training_way := common.IntAll(v["training_way"])
			positionId := common.Int64All(v["position_id"])
			clueId := common.Int64All(v["clue_id"])
			tasksource, tasksources := common.ObjToString(v["tasksource"]), ""
			taskstatus := common.IntAll(v["taskstatus"])
			if relationship_building_way != 1 && inventory_way == 1 {
				tasksources = "2"
				TiDb.Insert("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,
				})
			} else if inventory_way != 1 && training_way == 1 {
				tasksources = "3"
				TiDb.Insert("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,
				})
			}
			if taskstatus == 1 {
				tasksource = tasksources
			} else {
				if tasksource != "" {
					if !strings.Contains(tasksource, tasksources) {
						tasksource += "," + tasksources
					}
				} else {
					tasksource = tasksources
				}
			}
			TiDb.Update("dwd_f_csm_customer_info", map[string]interface{}{"clue_id": clueId}, map[string]interface{}{
				"tasksource": tasksource,
				"tasktime":   nowTime,
				"is_task":    1,
				"taskstatus": 0,
			})
		}
	}
	log.Println("客户成功系统进入任务车定时任务结束")
}

func ordersClue() {
	log.Println("后台订单线索定时任务开始")
	lastOrderClueId := cfg.LastOrderClueId
	nowTime := time.Now().Format(date.Date_Full_Layout)
	sql := fmt.Sprintf(`select * from dataexport_order where autoUpdate >= "%s" order by autoUpdate asc`, lastOrderClueId)
	data := Mysql.SelectBySql(sql)
	if data != nil && *data != nil && len(*data) > 0 {
		for _, v := range *data {
			order_status := common.IntAll(v["order_status"])
			is_backstage_order := common.IntAll(v["is_backstage_order"])
			pay_money := common.IntAll(v["pay_money"])
			saleDep := common.ObjToString(v["saleDep"])
			salesperson := common.ObjToString(v["salesperson"])
			user_phone := common.ObjToString(v["user_phone"])
			createperson := common.ObjToString(v["create_person"])
			saleMoney := common.ObjToString(v["saleMoney"])
			userId, uId, cluename, seatNumber, positionId, trailstatus, clueId := common.ObjToString(v["user_id"]), "", "", "", int64(0), "", int64(0)
			product_type_str2 := `"大会员","企业商机管理","VIP订阅"`
			product_type := common.ObjToString(v["product_type"])
			if order_status == 1 && is_backstage_order == 1 && salesperson != "" && !strings.HasPrefix(user_phone, "9") && strings.Contains(product_type_str2, product_type) {
				//销售部
				query := map[string]interface{}{}
				if product_type == "企业商机管理" {
					query["phone"] = user_phone
					userInfo := TiDb.FindOne("dwd_f_userbase_baseinfo", query, "", "")
					if userInfo != nil && len(*userInfo) > 0 {
						uId = common.ObjToString((*userInfo)["uid"])
						userId = common.ObjToString((*userInfo)["userid"])
					} else {
						break
					}
				} else {
					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"])
						} else {
							break
						}
					}
					query["userid"] = userId
					userInfo := TiDb.FindOne("dwd_f_userbase_baseinfo", query, "", "")
					if userInfo != nil && len(*userInfo) > 0 {
						uId = common.ObjToString((*userInfo)["uid"])
					} else {
						break
					}
				}
				cluename = common.ObjToString(v["company_name"])
				if cluename == "" {
					cluename = user_phone
				}
				if pay_money > 0 {
					trailstatus = "08"
				} else {
					trailstatus = "01"
				}
				isGroup, isCommerce := GetCompanyType(cluename) //判断是否集团公司、工商库
				clueData := TiDb.FindOne("dwd_f_crm_clue_info", map[string]interface{}{"uid": uId}, "", "")
				if clueData == nil || len(*clueData) == 0 {
					log.Println("saleDep", saleDep)
					if strings.HasPrefix(saleDep, "01") || strings.HasPrefix(saleDep, "05") {
						//撞单
						log.Println("销售部订单", saleDep)
						if strings.Contains(salesperson, ",") {
							log.Println("销售部撞单 ", salesperson)
							if saleMoney != "" {
								var saleMoneyStu = struct {
									Data []struct {
										Name  string
										Money interface{}
									}
								}{}
								json.Unmarshal([]byte(saleMoney), &saleMoneyStu)
								areAllEqual := true
								if firstMoney, ok := saleMoneyStu.Data[0].Money.(string); ok {
									maxMoney := "0"
									log.Println("销售部订单firstMoney", saleMoneyStu, saleMoneyStu.Data[0].Money)
									for _, item := range saleMoneyStu.Data {
										if item.Name != "合计" && item.Money != firstMoney {
											areAllEqual = false
											break
										}
									}
									log.Println("销售部订单areAllEqual", areAllEqual)
									if areAllEqual {
										salesperson = createperson
									} else {
										for _, item := range saleMoneyStu.Data {
											money1, _ := strconv.ParseFloat(item.Money.(string), 64)
											money2, _ := strconv.ParseFloat(maxMoney, 64)
											if item.Name != "合计" && money1 > money2 {
												maxMoney = item.Money.(string)
												salesperson = item.Name
											}
										}
									}
								} else {
									maxMoney := 0.0
									log.Println("销售部订单firstMoney222", saleMoneyStu, saleMoneyStu.Data[0].Money)
									for _, item := range saleMoneyStu.Data {
										if item.Name != "合计" && item.Money != firstMoney {
											areAllEqual = false
											break
										}
									}
									log.Println("销售部订单areAllEqual222", areAllEqual)
									if areAllEqual {
										salesperson = createperson
									} else {
										for _, item := range saleMoneyStu.Data {
											if item.Name != "合计" && item.Money.(float64) > maxMoney {
												maxMoney = item.Money.(float64)
												salesperson = item.Name
											}
										}
									}
								}
							} else {
								salesperson = createperson
							}
						}
						log.Println("销售部订单salesperson", salesperson)
						saleData := TiDb.FindOne("dwd_f_crm_personnel_management", map[string]interface{}{"name": salesperson}, "", "")
						if saleData != nil {
							seatNumber = common.ObjToString((*saleData)["seat_number"])
							positionId = common.Int64All((*saleData)["position_id"])
						}
						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":                 user_phone,
							"phone":                user_phone,
							"comeinsource_private": 2,
							"is_task":              0,
							"taskstatus":           0,
							"company_nature":       isGroup,
							"company_verification": isCommerce,
						})
					} else if strings.HasPrefix(saleDep, "03") {
						log.Println("市场部订单", saleDep)
						clueId = TiDb.Insert("dwd_f_crm_clue_info", map[string]interface{}{
							"userid":               userId,
							"uid":                  uId,
							"is_assign":            -2,
							"createtime":           nowTime,
							"updatetime":           nowTime,
							"cluename":             cluename,
							"top_cluetype":         "4",
							"sub_cluetype":         "154",
							"trailstatus":          trailstatus,
							"name":                 user_phone,
							"phone":                user_phone,
							"is_task":              0,
							"taskstatus":           0,
							"company_nature":       isGroup,
							"company_verification": isCommerce,
						})
					}
					if clueId > 0 {
						TiDb.Insert("dwd_f_crm_clue_change_record", map[string]interface{}{
							"clue_id":     clueId,
							"position_id": common.If(positionId > 0, positionId, -1),
							"change_type": "创建线索",
							"new_value":   "系统自动创建",
							"createtime":  nowTime,
							"BCPCID":      common.GetRandom(32),
							"operator_id": -1,
						})
					}
				}
			}
			cfg.LastOrderClueId = common.ObjToString(v["autoUpdate"])
		}
	}
	common.WriteSysConfig(&cfg)
	log.Println("后台订单线索定时任务结束")
}

func kcHistorySync() {
	filePath := "./无线索的.xlsx"
	// SE := qu.SimpleEncrypt{Key: "topJYBX2019"}
	// log.Println(SE.DecodeString("QltHc2AmagsIUFwVV0dybyZpAwECX0YK"))
	// return
	xlFile, _ := xlsx.OpenFile(filePath)
	//获取行数
	// length := len(xlFile.Sheets[0].Rows)
	//开辟除表头外的行数的数组内存
	// resourceArr := make([]map[string]interface{}, length-1)
	//遍历sheet
	for k, sheet := range xlFile.Sheets {
		if k > 0 {
			break
		}
		//遍历每一行
		for rowIndex, row := range sheet.Rows {
			//跳过第一行表头信息
			if rowIndex == 0 {
				continue
			}
			nowTime := time.Now().Format(date.Date_Full_Layout)
			relationship_building_way := row.Cells[1].Value
			relationship_building_ways := 1
			if relationship_building_way == "个人微信" {
				relationship_building_ways = 2
			} else if relationship_building_way == "企业微信" {
				relationship_building_ways = 3
			}
			training_way := row.Cells[2].Value
			training_ways := 1
			if training_way == "发送使用资料" {
				training_ways = 5
			} else if training_way == "线上一对一" {
				training_ways = 3
			} else if training_way == "线下一对一" {
				training_ways = 4
			}
			inventory_way := row.Cells[3].Value
			inventory_ways := 1
			if inventory_way == "电话盘点" {
				inventory_ways = 3
			} else if inventory_way == "微信盘点" {
				inventory_ways = 2
			}
			service_stage := row.Cells[4].Value
			service_stages := 1
			if service_stage == "断约" {
				service_stages = 2
			} else if service_stage == "续约期" {
				service_stages = 3
			}
			renewal_intention := row.Cells[5].Value
			renewal_intentions := 1
			if renewal_intention == "中" {
				renewal_intentions = 2
			} else if renewal_intention == "高" {
				renewal_intentions = 3
			}
			positive_behavior_reminder := row.Cells[6].Value
			positive_behavior_reminders := ""
			if positive_behavior_reminder != "" {
				positive_behavior_reminders = "1,3"
			}
			// negative_behavior_reminder := ""
			name := row.Cells[13].Value
			// phoneMap := map[string]string{
			// "朱庆伟": "17600755377",
			// "程涵":  "15538709520",
			// "张铭铭": "18237530966",
			// }

			// company_name := row.Cells[10].Value
			company_address := row.Cells[14].Value
			company_website := row.Cells[15].Value
			service_starttime := row.Cells[16].Value
			service_endtime := row.Cells[17].Value

			industry_status := row.Cells[21].Value
			industry_statuss := 1
			if industry_status == "非头部" {
				industry_statuss = 2
			}
			target_customer_type := row.Cells[24].Value
			main_sale_way := row.Cells[25].Value
			main_get_info_way := row.Cells[26].Value
			bid_sales_proportion := row.Cells[27].Value
			continuous_use_judgment := row.Cells[29].Value
			bid_sales_proportions := 1
			continuous_use_judgments := 1
			if bid_sales_proportion == "10%-19%" {
				bid_sales_proportions = 2
			} else if bid_sales_proportion == "20%-49%" {
				bid_sales_proportions = 3
			} else if bid_sales_proportion == "50%-69%" {
				bid_sales_proportions = 4
			} else if bid_sales_proportion == "70%及以上" {
				bid_sales_proportions = 4
			}
			if strings.Contains(continuous_use_judgment, "预计可持续使用。需勤维护") {
				continuous_use_judgments = 2
			} else if strings.Contains(continuous_use_judgment, "需从功能层面提升客户粘性") {
				continuous_use_judgments = 3
			} else if strings.Contains(continuous_use_judgment, "需持续培育") {
				continuous_use_judgments = 4
			}
			is_pre_sales_training := row.Cells[30].Value
			is_pre_sales_trainings := 0
			if is_pre_sales_training == "是" {
				is_pre_sales_trainings = 1
			}
			shpx := row.Cells[31].Value
			if shpx == "否" {
				training_ways = 2
			}
			notes := row.Cells[32].Value
			phone := strings.TrimSpace(row.Cells[11].Value)
			log.Println("PHONE", phone, name, len(phone))
			// if phone != "" && len(phone) == 11 && phoneMap[name] != "" {
			positionId := 0
			if name == "程涵" {
				positionId = 2202735
			} else if name == "张铭铭" {
				positionId = 2202736
			}
			clueData := TiDb.FindOne("dwd_f_crm_clue_info", map[string]interface{}{"phone": phone}, "", "")
			log.Println("clueData", clueData)
			if clueData != nil {
				clueId := common.Int64All((*clueData)["id"])
				log.Println("线索存在", phone, clueId)
				role := row.Cells[18].Value
				business_scope := row.Cells[19].Value
				industry := row.Cells[20].Value
				salesperson_num := row.Cells[22].Value
				salesperson_nums := 1
				if salesperson_num == "10-19" {
					salesperson_nums = 2
				} else if salesperson_num == "20-49" {
					salesperson_nums = 3
				} else if salesperson_num == "50-99" {
					salesperson_nums = 4
				}
				area := row.Cells[23].Value
				areaArr := []string{}
				for _, v := range strings.Split(area, ",") {
					areaArr = append(areaArr, AreaCode[v])
				}
				follow_project_area := ""
				if len(areaArr) > 0 {
					follow_project_area = strings.Join(areaArr, ",")
				}
				customer_demand := row.Cells[28].Value
				updatetime := row.Cells[36].Value
				ok := TiDb.Update("dwd_f_crm_clue_info", map[string]interface{}{"phone": phone}, map[string]interface{}{
					"role":                role,
					"business_scope":      business_scope,
					"industry":            industry,
					"follow_project_area": follow_project_area,
					"customer_demand":     customer_demand,
					"updatetime":          updatetime,
					"is_transfer":         1,
					"salesperson_num":     salesperson_nums,
				})
				if ok {
					entId := "0"
					buy_subject := 1
					company_name := ""
					product := 1
					powerData := TiDb.FindOne("dwd_f_userbase_order_info", map[string]interface{}{"phone": phone}, "", "autoUpdate desc")
					if powerData != nil {
						buy_subject = common.IntAll((*powerData)["buy_subject"])
						if buy_subject == 1 {
							entId = common.ObjToString((*powerData)["userid"])
						} else {
							entId = fmt.Sprint((*powerData)["ent_id"])
						}
						company_name = common.ObjToString((*powerData)["company_name"])
						product_type := common.ObjToString((*powerData)["product_type"])
						data_spec := common.ObjToString((*powerData)["data_spec"])
						productMap := map[string]int{
							"dhy6":   1,
							"dhy7":   2,
							"dhy3":   4,
							"dhy1":   5,
							"dhy2":   6,
							"dhy5":   7,
							"企业商机管理": 8,
						}
						if product_type == "企业商机管理" {
							product = productMap[product_type]
						} else {
							if data_spec == "dhy6" {
								product = 1
								filter := common.ObjToString((*powerData)["filter"])
								filterMap := map[string]interface{}{}
								json.Unmarshal([]byte(filter), &filterMap)
								if len(filterMap) > 0 {
									areaCount := common.IntAll(filterMap["areaCount"])
									if areaCount == 1 {
										product = 3
									}
								}
							} else {
								product = productMap[data_spec]
							}
						}
					}
					cid := TiDb.Insert("dwd_f_csm_customer_info", map[string]interface{}{
						"clue_id":                    clueId,
						"transfertime":               nowTime,
						"position_id":                positionId,
						"name":                       name,
						"service_starttime":          service_starttime,
						"service_endtime":            service_endtime,
						"ent_id":                     entId,
						"is_task":                    1,
						"tasktime":                   nowTime,
						"taskstatus":                 0,
						"tasksource":                 "1",
						"is_admin":                   1,
						"product_access":             product,
						"buy_subject":                buy_subject,
						"relationship_building_way":  relationship_building_ways,
						"inventory_way":              inventory_ways,
						"training_way":               training_ways,
						"is_pre_sales_training":      is_pre_sales_trainings,
						"service_stage":              service_stages,
						"company_name":               company_name,
						"renewal_intention":          renewal_intentions,
						"positive_behavior_reminder": positive_behavior_reminders,
						"company_address":            company_address,
						"company_website":            company_website,
						"industry_status":            industry_statuss,
						"target_customer_type":       target_customer_type,
						"main_sale_way":              main_sale_way,
						"main_get_info_way":          main_get_info_way,
						// "bid_sales_proportion":    bid_sales_proportions,
						// "continuous_use_judgment": continuous_use_judgments,
						"notes": notes,
					})
					if cid > 0 {
						log.Println("保存客户成功", phone)
					} else {
						log.Println("保存客户失败！！！", phone)
					}
				} else {
					log.Println("线索修改失败！！！", phone)
				}
			} else {
				role := row.Cells[18].Value
				business_scope := row.Cells[19].Value
				industry := row.Cells[20].Value
				salesperson_num := row.Cells[22].Value
				salesperson_nums := 1
				if salesperson_num == "10-19" {
					salesperson_nums = 2
				} else if salesperson_num == "20-49" {
					salesperson_nums = 3
				} else if salesperson_num == "50-99" {
					salesperson_nums = 4
				}
				area := row.Cells[23].Value
				areaArr := []string{}
				for _, v := range strings.Split(area, ",") {
					areaArr = append(areaArr, AreaCode[v])
				}
				follow_project_area := ""
				if len(areaArr) > 0 {
					follow_project_area = strings.Join(areaArr, ",")
				}
				customer_demand := row.Cells[28].Value
				entId := "0"
				clueId := int64(0)
				buy_subject := 1
				company_name := ""
				product := 1
				powerData := TiDb.FindOne("dwd_f_userbase_order_info", map[string]interface{}{"phone": phone}, "", "autoUpdate desc")
				if powerData != nil {
					buy_subject = common.IntAll((*powerData)["buy_subject"])
					if buy_subject == 1 {
						entId = common.ObjToString((*powerData)["userid"])
					} else {
						entId = fmt.Sprint((*powerData)["ent_id"])
					}
					company_name = common.ObjToString((*powerData)["company_name"])
					product_type := common.ObjToString((*powerData)["product_type"])
					data_spec := common.ObjToString((*powerData)["data_spec"])
					productMap := map[string]int{
						"dhy6":   1,
						"dhy7":   2,
						"dhy3":   4,
						"dhy1":   5,
						"dhy2":   6,
						"dhy5":   7,
						"企业商机管理": 8,
					}
					if product_type == "企业商机管理" {
						product = productMap[product_type]
					} else {
						if data_spec == "dhy6" {
							product = 1
							filter := common.ObjToString((*powerData)["filter"])
							filterMap := map[string]interface{}{}
							json.Unmarshal([]byte(filter), &filterMap)
							if len(filterMap) > 0 {
								areaCount := common.IntAll(filterMap["areaCount"])
								if areaCount == 1 {
									product = 3
								}
							}
						} else {
							product = productMap[data_spec]
						}
					}
					//
					uId := common.ObjToString((*powerData)["uid"])
					userId := common.ObjToString((*powerData)["userid"])
					pay_money := common.IntAll((*powerData)["pay_money"])
					seatNumber, trailstatus := "", ""
					salePositionId := int64(0)
					cluename := common.ObjToString((*powerData)["company_name"])
					salesperson := common.ObjToString((*powerData)["salesperson"])
					if cluename == "" {
						cluename = phone
					}
					if pay_money > 0 {
						trailstatus = "08"
					} else {
						trailstatus = "01"
					}
					saleData := TiDb.FindOne("jy_salesperson_info", map[string]interface{}{"name": salesperson}, "", "")
					if saleData != nil {
						seatNumber = common.ObjToString((*saleData)["seat_number"])
						salePositionId = common.Int64All((*saleData)["position_id"])
					}
					clueId = TiDb.Insert("dwd_f_crm_clue_info", map[string]interface{}{
						"userid":               userId,
						"uid":                  uId,
						"seatNumber":           seatNumber,
						"position_id":          salePositionId,
						"is_assign":            1,
						"comeintime":           nowTime,
						"createtime":           nowTime,
						"updatetime":           nowTime,
						"cluename":             cluename,
						"top_cluetype":         "4",
						"sub_cluetype":         "154",
						"trailstatus":          trailstatus,
						"name":                 phone,
						"phone":                phone,
						"comeinsource_private": 2,
						"is_task":              0,
						"taskstatus":           1,
						"role":                 role,
						"business_scope":       business_scope,
						"industry":             industry,
						"follow_project_area":  follow_project_area,
						"customer_demand":      customer_demand,
						"is_transfer":          1,
						"salesperson_num":      salesperson_nums,
					})
					//
				}
				TiDb.Insert("dwd_f_csm_customer_info", map[string]interface{}{
					"clue_id":                    clueId,
					"transfertime":               nowTime,
					"position_id":                positionId,
					"name":                       name,
					"service_starttime":          service_starttime,
					"service_endtime":            service_endtime,
					"ent_id":                     entId,
					"is_task":                    0,
					"tasktime":                   nil,
					"taskstatus":                 1,
					"tasksource":                 nil,
					"is_admin":                   1,
					"product_access":             product,
					"buy_subject":                buy_subject,
					"relationship_building_way":  relationship_building_ways,
					"inventory_way":              inventory_ways,
					"training_way":               training_ways,
					"is_pre_sales_training":      is_pre_sales_trainings,
					"service_stage":              service_stages,
					"company_name":               company_name,
					"renewal_intention":          renewal_intentions,
					"positive_behavior_reminder": positive_behavior_reminders,
					"company_address":            company_address,
					"company_website":            company_website,
					"industry_status":            industry_statuss,
					"target_customer_type":       target_customer_type,
					"main_sale_way":              main_sale_way,
					"main_get_info_way":          main_get_info_way,
					"bid_sales_proportion":       bid_sales_proportions,
					"continuous_use_judgment":    continuous_use_judgments,
					"notes":                      notes,
				})
			}
			// } else {
			// 	log.Println("手机号有问题！！！", phone)
			// 	row.AddCell().SetValue("手机号有问题！！！")
			// }
		}
	}
	// err := xlFile.Save("./222.xlsx")
	// if err != nil {
	// 	log.Println("xls error")
	// }
}

func kcHistorySyncs() {
	filePath := "./有线索的.xlsx"
	// SE := qu.SimpleEncrypt{Key: "topJYBX2019"}
	// log.Println(SE.DecodeString("QltHc2AmagsIUFwVV0dybyZpAwECX0YK"))
	// return
	xlFile, _ := xlsx.OpenFile(filePath)
	//获取行数
	// length := len(xlFile.Sheets[0].Rows)
	//开辟除表头外的行数的数组内存
	// resourceArr := make([]map[string]interface{}, length-1)
	//遍历sheet
	for k, sheet := range xlFile.Sheets {
		if k > 0 {
			break
		}
		//遍历每一行
		for rowIndex, row := range sheet.Rows {
			//跳过第一行表头信息
			if rowIndex == 0 {
				continue
			}
			bid_sales_proportion := row.Cells[2].Value
			continuous_use_judgment := row.Cells[3].Value
			phone := row.Cells[0].Value
			bid_sales_proportions := 1
			continuous_use_judgments := 1
			if bid_sales_proportion == "10%-19%" {
				bid_sales_proportions = 2
			} else if bid_sales_proportion == "20%-49%" {
				bid_sales_proportions = 3
			} else if bid_sales_proportion == "50%-69%" {
				bid_sales_proportions = 4
			} else if bid_sales_proportion == "70%及以上" {
				bid_sales_proportions = 4
			}
			if strings.Contains(continuous_use_judgment, "预计可持续使用。需勤维护") {
				continuous_use_judgments = 2
			} else if strings.Contains(continuous_use_judgment, "需从功能层面提升客户粘性") {
				continuous_use_judgments = 3
			} else if strings.Contains(continuous_use_judgment, "需持续培育") {
				continuous_use_judgments = 4
			}
			if phone != "" {
				clueData := TiDb.FindOne("dwd_f_crm_clue_info", map[string]interface{}{"phone": phone}, "", "")
				if clueData != nil {
					clueId := common.Int64All((*clueData)["id"])
					log.Println("线索存在", phone, clueId)
					salesperson_num := row.Cells[1].Value
					salesperson_nums := 1
					if salesperson_num == "10-19" {
						salesperson_nums = 2
					} else if salesperson_num == "20-49" {
						salesperson_nums = 3
					} else if salesperson_num == "50-99" {
						salesperson_nums = 4
					}
					ok := TiDb.Update("dwd_f_crm_clue_info", map[string]interface{}{"phone": phone}, map[string]interface{}{
						"salesperson_num": salesperson_nums,
					})
					if ok {
						oks := TiDb.Update("dwd_f_csm_customer_info", map[string]interface{}{"clue_id": clueId}, map[string]interface{}{
							"bid_sales_proportion":    bid_sales_proportions,
							"continuous_use_judgment": continuous_use_judgments,
						})
						if oks {
							log.Println("修改客户成功", phone)
						} else {
							log.Println("修改客户失败！！！", phone)
						}
					} else {
						log.Println("线索修改失败！！！", phone)
					}
				}
			}
		}
	}
}

func kcVisitSync() {
	filePath := "./222.xlsx"
	// SE := qu.SimpleEncrypt{Key: "topJYBX2019"}
	// log.Println(SE.DecodeString("QltHc2AmagsIUFwVV0dybyZpAwECX0YK"))
	// return
	xlFile, _ := xlsx.OpenFile(filePath)
	//获取行数
	// length := len(xlFile.Sheets[0].Rows)
	//开辟除表头外的行数的数组内存
	// resourceArr := make([]map[string]interface{}, length-1)
	//遍历sheet
	for k, sheet := range xlFile.Sheets {
		if k == 1 {
			//遍历每一行
			for rowIndex, row := range sheet.Rows {
				//跳过第一行表头信息
				if rowIndex == 0 {
					continue
				}
				tasksource := row.Cells[1].Value
				tasksources := 1
				if tasksource == "客户情况盘点" {
					tasksources = 2
				} else if tasksource == "未培训" {
					tasksources = 3
				} else if tasksource == "日常沟通" {
					tasksources = 4
				} else if tasksource == "续约沟通" {
					tasksources = 5
				} else if tasksource == "月度回访" {
					tasksources = 6
				} else if tasksource == "培训首周回访" {
					tasksources = 7
				} else if tasksource == "建联" {
					tasksources = 1
				}
				phone := row.Cells[2].Value
				trail_way := row.Cells[3].Value
				trail_ways := 1
				if trail_way == "电话沟通" {
					trail_ways = 2
				} else if trail_way == "线下面访" {
					trail_ways = 3
				} else if trail_way == "腾讯会议" {
					trail_ways = 4
				}
				content := row.Cells[4].Value
				name := row.Cells[5].Value
				positionId := 0
				if name == "程涵" {
					positionId = 2202735
				} else if name == "张铭铭" {
					positionId = 2202736
				} else {
					positionId = -1
				}
				createtime := row.Cells[6].Value
				clueData := TiDb.FindOne("dwd_f_crm_clue_info", map[string]interface{}{"phone": phone}, "", "")
				if clueData != nil {
					clueId := common.Int64All((*clueData)["id"])
					TiDb.Insert("dwd_f_crm_trail_content", map[string]interface{}{
						"clue_id":     clueId,
						"position_id": positionId,
						"operator_id": -1,
						"content":     content,
						"createtime":  createtime,
						"tasksource":  tasksources,
						"trail_way":   trail_ways,
					})
				} else {
					row.AddCell().SetValue("没有线索")
				}
			}
		} else if k == 2 {
			//遍历每一行
			for rowIndex, row := range sheet.Rows {
				//跳过第一行表头信息
				if rowIndex == 0 {
					continue
				}
				phone := row.Cells[2].Value
				contact_type := row.Cells[4].Value
				clueData := TiDb.FindOne("dwd_f_crm_clue_info", map[string]interface{}{"phone": phone}, "", "")
				if clueData != nil {
					clueId := common.Int64All((*clueData)["id"])
					TiDb.Update("dwd_f_crm_clue_info", map[string]interface{}{"id": clueId}, map[string]interface{}{"contact_type": contact_type})
				} else {
					row.AddCell().SetValue("没有线索")
				}
			}
		}
	}
	err := xlFile.Save("./333.xlsx")
	if err != nil {
		log.Println("xls error")
	}
}