datatools/clueSync/everything.go

package main

import (
	"app.yhyue.com/moapp/jybase/redis"
	"encoding/json"
	"fmt"
	"github.com/gogf/gf/util/gconv"
	"log"
	"net/url"
	"strings"
	"time"

	"app.yhyue.com/moapp/jybase/mail"
	"github.com/tealeg/xlsx"

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

	dates "app.yhyue.com/moapp/jybase/date"

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

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

func everythingSync() {
	lastEverythingTime := cfg.LastEverythingTime
	nowTime := time.Now().Format(dates.Date_Full_Layout)
	codeArr := []string{}
	if len(UserChannel) > 0 {
		for i, _ := range UserChannel {
			codeArr = append(codeArr, fmt.Sprintf(`"%s"`, i))
		}
	}
	sql := fmt.Sprintf(`select * from user_source where channel_code in (%s) and create_time > "%s" order by create_time asc`, strings.Join(codeArr, ","), lastEverythingTime)
	log.Println("三方渠道数据定时任务开始", sql)
	data := ThirdParty.SelectBySql(sql)
	if data != nil && *data != nil && len(*data) > 0 {
		for _, v := range *data {
			channel_code := gconv.String("channel_code")
			channelName := UserChannel[channel_code]
			user_id := common.ObjToString(v["user_id"])
			position_id := common.Int64All(v["position_id"])
			phone := common.ObjToString(v["phone"])
			state := common.IntAll(v["state"])
			user_mold, is_assign, is_transfer, last_login_time, registe_time, mailbox, source, clueId, order_type := 4, 0, 0, "", "", "", "", int64(0), ""
			owner, sales_lead_phone, sales_ent_name, sales_position, sales_dep, data_request, sales_leads_source := "", "", "", "", "", "", ""
			cData := TiDb.FindOne("dwd_f_crm_clue_info", map[string]interface{}{"phone": phone}, "", "")
			if cData != nil {
				is_assign = common.IntAll((*cData)["is_assign"])
				is_transfer = common.IntAll((*cData)["is_transfer"])
				clueId = common.Int64All((*cData)["id"])
			}
			udata := TiDb.FindOne("dwd_f_userbase_baseinfo", map[string]interface{}{"userid": user_id}, "", "")
			if udata != nil {
				userSource := common.ObjToString((*udata)["source"])
				if userSource == "0102" {
					continue
				}
			}
			//
			/*cuData := TiDbData.FindOne("customer", map[string]interface{}{"phone": phone}, "", "")
			if cuData != nil {
				owner = common.ObjToString((*cuData)["owner"])
				unique_id = common.ObjToString((*cuData)["unique_id"])
				belongTo = common.ObjToString((*cuData)["belongTo"])
			}*/
			vData := TiDb.FindOne("dwd_f_userbase_visit_info", map[string]interface{}{"userid": user_id}, "", "")
			if vData != nil {
				last_login_time = common.ObjToString((*vData)["date"])
			}
			dData := Mysql.Find("dataexport_order", map[string]interface{}{"user_id": user_id}, "", "", -1, -1)
			if dData != nil && len(*dData) > 0 {
				pMap := map[string]string{}
				pArr := []string{}
				for _, v := range *dData {
					product_type := common.ObjToString(v["product_type"])
					pMap[product_type] = "1"
				}
				for k := range pMap {
					pArr = append(pArr, k)
				}
				if len(pArr) > 0 {
					order_type = strings.Join(pArr, ",")
				}
			}
			uData, ok := Mgo.FindOne("user", map[string]interface{}{"_id": mongodb.StringTOBsonId(user_id)})
			if ok && uData != nil {
				l_registedate := common.Int64All((*uData)["l_registedate"])
				registe_time = time.Unix(l_registedate, 0).Format(dates.Date_Full_Layout)
			}
			sData, oks := Mgo.Find("saleLeads", map[string]interface{}{"userid": user_id}, `{"_id":-1}`, nil, false, 0, 1)
			if oks && sData != nil {
				if len(*sData) > 0 {
					sDatas := (*sData)[0]
					sales_lead_phone = common.ObjToString(sDatas["phone"])
					sales_position = common.ObjToString(sDatas["position"])
					sales_ent_name = common.ObjToString(sDatas["company"])
					sales_dep = common.ObjToString(sDatas["branch"])
					if sales_dep == "" {
						sales_dep = common.ObjToString(sDatas["department"])
					}
					data_request = common.ObjToString(sDatas["data_requirement"])
					mailbox = common.ObjToString(sDatas["mail"])
					source = common.ObjToString(sDatas["interest"])
				}
			}
			if state == 1 {
				sales_leads_source = fmt.Sprintf("%s登录", channelName)
				if is_assign == 1 || is_transfer == 1 || owner != "" {
					user_mold = 1
				} else {
					user_mold = 2
				}
			} else if state == 2 {
				sales_leads_source = fmt.Sprintf("%s注册", channelName)
				if is_assign == 1 || is_transfer == 1 || owner != "" {
					user_mold = 3
				} else {
					user_mold = 4
				}
			}
			if ThirdParty.Count("user_channel_info", map[string]interface{}{"user_id": user_id}) > 0 {
				ThirdParty.Update("user_channel_info", map[string]interface{}{"user_id": user_id}, map[string]interface{}{
					"user_mold":          user_mold,
					"last_login_time":    common.If(last_login_time != "", last_login_time, nil),
					"sales_lead_phone":   sales_lead_phone,
					"sales_ent_name":     sales_ent_name,
					"sales_position":     sales_position,
					"sales_dep":          sales_dep,
					"sales_leads_source": sales_leads_source,
					"mailbox":            mailbox,
					"order_type":         order_type,
					"data_request":       data_request,
					"source":             source,
					"update_time":        nowTime,
				})
			} else {
				ThirdParty.Insert("user_channel_info", map[string]interface{}{
					"user_id":            user_id,
					"registe_time":       registe_time,
					"phone":              phone,
					"user_mold":          user_mold,
					"last_login_time":    common.If(last_login_time != "", last_login_time, nil),
					"sales_lead_phone":   sales_lead_phone,
					"sales_ent_name":     sales_ent_name,
					"sales_position":     sales_position,
					"sales_dep":          sales_dep,
					"sales_leads_source": sales_leads_source,
					"order_type":         order_type,
					"mailbox":            mailbox,
					"data_request":       data_request,
					"source":             source,
					"update_time":        nowTime,
				})
			}
			if clueId > 0 && is_assign != 1 && is_transfer != 1 {
				TiDb.Update("dwd_f_userbase_baseinfo", map[string]interface{}{"userid": user_id}, map[string]interface{}{"belong_to": "0301"})
				TiDb.Update("dwd_f_crm_clue_info", map[string]interface{}{"phone": phone}, map[string]interface{}{"is_assign": -1})
				TiDb.Insert("dwd_f_crm_clue_change_record", map[string]interface{}{
					"clue_id":     clueId,
					"position_id": common.If(position_id > 0, position_id, -1),
					"change_type": "退出公海",
					"new_value":   fmt.Sprintf("通过合作渠道%s", channelName) + fmt.Sprint(common.If(user_mold == 1 || user_mold == 2, "登录", "注册")),
					"createtime":  nowTime,
					"BCPCID":      common.GetRandom(32),
					"operator_id": -1,
				})
			}
		}
		cfg.LastEverythingTime = common.ObjToString((*data)[len(*data)-1]["create_time"])
	}
	common.WriteSysConfig(&cfg)
	log.Println("三方渠道数据定时任务结束")
}

func saveEverything(user_id, phone, item, sourceName, sourceCode, channelName string) bool {
	if channelName == "" {
		channelName = "一切都好"
	}
	nowTime, isOk := time.Now().Format(dates.Date_Full_Layout), false
	user_mold, is_assign, is_transfer, last_login_time, registe_time, mailbox, source, order_type, sales_leads_source := 4, 0, 0, "", "", "", "", "", ""
	owner, sales_lead_phone, sales_ent_name, sales_position, sales_dep, data_request, clueId, position_id := "", "", "", "", "", "", int64(0), int64(0)
	cData := TiDb.FindOne("dwd_f_crm_clue_info", map[string]interface{}{"phone": phone}, "", "")
	if cData != nil {
		is_assign = common.IntAll((*cData)["is_assign"])
		is_transfer = common.IntAll((*cData)["is_transfer"])
		clueId = common.Int64All((*cData)["is_transfer"])
		position_id = common.Int64All((*cData)["position_id"])
	}
	cuData := TiDbData.FindOne("customer", map[string]interface{}{"phone": phone}, "", "")
	if cuData != nil {
		owner = common.ObjToString((*cuData)["owner"])
	}
	dData := Mysql.Find("dataexport_order", map[string]interface{}{"user_id": user_id}, "", "", -1, -1)
	if dData != nil && len(*dData) > 0 {
		pMap := map[string]string{}
		pArr := []string{}
		for _, v := range *dData {
			product_type := common.ObjToString(v["product_type"])
			pMap[product_type] = "1"
		}
		for k := range pMap {
			pArr = append(pArr, k)
		}
		if len(pArr) > 0 {
			order_type = strings.Join(pArr, ",")
		}
	}
	vData := TiDb.FindOne("dwd_f_userbase_visit_info", map[string]interface{}{"userid": user_id}, "", "")
	if vData != nil {
		last_login_time = common.ObjToString((*vData)["date"])
	}
	uData, ok := Mgo.FindOne("user", map[string]interface{}{"_id": mongodb.StringTOBsonId(user_id)})
	if ok && uData != nil {
		l_registedate := common.Int64All((*uData)["l_registedate"])
		registe_time = time.Unix(l_registedate, 0).Format(dates.Date_Full_Layout)
	}
	if item == "orders" {
		sales_leads_source = "订单未支付"
	} else if item == "users" || item == "xcxusers" {
		return false
	} else if item == "saleLeads" {
		sales_leads_source = sourceName
	} else {
		sales_leads_source = "用户留资"
	}
	query := map[string]interface{}{"userid": user_id}
	if sourceCode != "" {
		query["source"] = sourceCode
	}
	sData, oks := Mgo.Find("saleLeads", query, `{"_id":-1}`, nil, false, 0, 1)
	if oks && sData != nil {
		if len(*sData) > 0 {
			sDatas := (*sData)[0]
			sales_lead_phone = common.ObjToString(sDatas["phone"])
			sales_position = common.ObjToString(sDatas["position"])
			sales_ent_name = common.ObjToString(sDatas["company"])
			sales_dep = common.ObjToString(sDatas["branch"])
			if sales_dep == "" {
				sales_dep = common.ObjToString(sDatas["department"])
			}
			data_request = common.ObjToString(sDatas["data_requirement"])
			mailbox = common.ObjToString(sDatas["mail"])
			source = common.ObjToString(sDatas["interest"])
		}
	}
	if is_assign == 1 || is_transfer == 1 || owner != "" {
		user_mold = 1
	} else {
		user_mold = 2
	}
	/*if owner == "" && unique_id != "" && belongTo == "市场部" {
		token := getToken()
		if token != "" {
			sss := url.QueryEscape("合作渠道一切都好")
			urls := `https://a1.7x24cc.com/commonInte?flag=1008&account=N000000029739&accessToken=` + token + `&cusObj={"unique_id":"` + unique_id + `","empNo":"8049","owner":"8049","source":"` + sss + `"}&dbType=0001`
			bs, err := doGet(urls)
			if err != nil {
				log.Println("调用接口失败", unique_id, err)
			}
			resMap := common.StringToMap(string(bs))
			if resMap["success"] != nil && resMap["success"].(bool) {
				ok := TiDbData.Update("customer", map[string]interface{}{"unique_id": unique_id}, map[string]interface{}{"empNo": "8049", "owner": "8049", "source": "合作渠道一切都好"})
				if ok {
					log.Println("更新成功", unique_id)
				} else {
					log.Println("更新失败", unique_id)
				}
			} else {
				log.Println("调用接口失败!!", unique_id)
			}
		}
	}*/
	if ThirdParty.Count("user_channel_info", map[string]interface{}{"user_id": user_id}) > 0 {
		ThirdParty.Update("user_channel_info", map[string]interface{}{"user_id": user_id}, map[string]interface{}{
			"user_mold":          user_mold,
			"last_login_time":    common.If(last_login_time != "", last_login_time, nil),
			"sales_lead_phone":   sales_lead_phone,
			"sales_ent_name":     sales_ent_name,
			"sales_position":     sales_position,
			"sales_dep":          sales_dep,
			"sales_leads_source": sales_leads_source,
			"mailbox":            mailbox,
			"data_request":       data_request,
			"source":             source,
			"order_type":         order_type,
			"update_time":        nowTime,
		})
	} else {
		ThirdParty.Insert("user_channel_info", map[string]interface{}{
			"user_id":            user_id,
			"registe_time":       registe_time,
			"phone":              phone,
			"user_mold":          user_mold,
			"last_login_time":    common.If(last_login_time != "", last_login_time, nil),
			"sales_lead_phone":   sales_lead_phone,
			"sales_ent_name":     sales_ent_name,
			"sales_position":     sales_position,
			"sales_dep":          sales_dep,
			"sales_leads_source": sales_leads_source,
			"mailbox":            mailbox,
			"data_request":       data_request,
			"order_type":         order_type,
			"source":             source,
			"update_time":        nowTime,
		})
	}
	if clueId > 0 && is_assign != 1 && is_transfer != 1 {
		TiDb.Update("dwd_f_crm_clue_info", map[string]interface{}{"phone": phone}, map[string]interface{}{"is_assign": -1})
		TiDb.Insert("dwd_f_crm_clue_change_record", map[string]interface{}{
			"clue_id":     clueId,
			"position_id": common.If(position_id > 0, position_id, -1),
			"change_type": "退出公海",
			"new_value":   fmt.Sprintf("通过合作渠道%s登录", channelName),
			"createtime":  nowTime,
			"BCPCID":      common.GetRandom(32),
			"operator_id": -1,
		})
	} else if is_assign == 1 || is_transfer == 1 {
		isOk = true
	}
	return isOk
}

// 大客户线索-发邮件
func bigCustomer() {
	runOk := getRunOk()
	if !runOk {
		log.Println("不是工作日，任务暂停")
		return
	}
	log.Println("大客户线索定时任务开始")
	dataArr := []map[string]interface{}{}
	filterArr := []string{"-pc", "-app", "-wx", "-h5"}
	sourceMap := map[string]map[string]interface{}{} //根据留资维表：tidb/Jianyu_subjectdb/d_saleleads_code
	saleSource := TiDb.SelectBySql(`SELECT source,name,appoint_email FROM d_saleleads_code WHERE department LIKE '%大客户%' AND is_delete = 1`)
	if saleSource != nil && len(*saleSource) > 0 {
		for _, v := range *saleSource {
			saleSourceMap := v
			source := common.ObjToString(v["source"])
			name := common.ObjToString(v["name"])
			for _, s := range filterArr {
				name = strings.ReplaceAll(name, s, "")
			}
			saleSourceMap["name"] = name
			sourceMap[source] = saleSourceMap
		}
	}
	nowTime := time.Now().Format(date.Date_Full_Layout)
	bigSaleTime := cfg.BigSaleTime
	bigOrderTime := cfg.BigOrderTime
	if bigSaleTime == 0 {
		bigSaleTime = time.Now().Unix()
	}
	if bigOrderTime == "" {
		bigOrderTime = nowTime
	}
	data := FindBatchData("dk")
	saleleadsData, ok := Mgo.Find("saleLeads", map[string]interface{}{"createtime": map[string]interface{}{"$gt": bigSaleTime}}, `{"phone":1,"createtime":1}`, nil, false, -1, -1)
	if ok && saleleadsData != nil && len(*saleleadsData) > 0 {
		for _, v := range *saleleadsData {
			sources := common.ObjToString(v["source"])
			userid := common.ObjToString(v["userid"])
			uid := ""
			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"])
					uid = common.ObjToString((*userMapping)["uid"])
				}
			} else {
				userMapping := TiDb.FindOne("dwd_f_userbase_id_mapping", map[string]interface{}{"userid": userid}, "", "")
				if userMapping != nil && len(*userMapping) > 0 {
					userid = common.ObjToString((*userMapping)["userid"])
					uid = common.ObjToString((*userMapping)["uid"])
				}
			}
			if sourceMap[sources] != nil {
				sourceData := sourceMap[sources]
				source := sourceData["name"]
				phone := common.ObjToString(v["phone"])
				if IsInternal(phone) {
					continue
				}
				key := fmt.Sprintf("%s_%s", source, phone)
				if _, ok := data[key]; ok {
					continue
				}
				data[key] = true
				company := common.ObjToString(v["company"])
				job := common.ObjToString(v["position"])
				username := common.ObjToString(v["name"])
				email := common.ObjToString(v["mail"])
				interest := common.ObjToString(v["interest"])
				data_requirement := common.ObjToString(v["data_requirement"])
				belongTo, usernickname := "大客户", ""
				userData := TiDb.FindOne("dwd_f_userbase_baseinfo", map[string]interface{}{"uid": uid}, "", "")
				if userData != nil {
					usernickname = common.ObjToString((*userData)["nickname"])
					bt := common.ObjToString((*userData)["belong_to"])
					if strings.HasPrefix(bt, "03") || bt == "0102" {
						continue
					}
				}

				dataArr = append(dataArr, map[string]interface{}{
					"createTime":       nowTime,
					"lastUpdateTime":   nowTime,
					"uid":              uid,
					"userid":           userid,
					"username":         username,
					"usernickname":     usernickname,
					"company":          company,
					"job":              job,
					"phone":            phone,
					"email":            email,
					"source":           source,
					"belongTo":         belongTo,
					"interest":         interest,
					"data_requirement": data_requirement,
					"appoint_email":    sourceData["appoint_email"],
				})
			}
			if gconv.Int64(v["createtime"]) > bigSaleTime {
				bigSaleTime = gconv.Int64(v["createtime"])
			}
		}
	}
	cfg.BigSaleTime = bigSaleTime
	orderData := Mysql.SelectBySql(`SELECT * FROM dataexport_order WHERE create_time > "` + bigOrderTime + `" AND (product_type = '历史数据' or product_type = '数据流量包') AND order_status IN (0,1) AND salesperson IS NULL AND is_backstage_order = 0`)
	if orderData != nil && len(*orderData) > 0 {
		for _, v := range *orderData {
			bigOrderTime = gconv.Time(gconv.String(v["create_time"])).Format(date.Date_Full_Layout)
			phone := common.ObjToString(v["user_phone"])
			order_status := common.IntAll(v["order_status"])
			product_type := common.ObjToString(v["product_type"])
			orderUserId := gconv.String(v["user_id"])
			query := map[string]interface{}{}
			if !mongodb.IsObjectIdHex(orderUserId) {
				userMapping := TiDb.FindOne("dwd_f_userbase_id_mapping", map[string]interface{}{"position_id": orderUserId}, "", "")
				if userMapping != nil && len(*userMapping) > 0 {
					uid := common.ObjToString((*userMapping)["uid"])
					query["uid"] = uid
				}
			} else {
				query["userid"] = orderUserId
			}
			userData := TiDb.FindOne("dwd_f_userbase_baseinfo", query, "", "")
			username, usernickname, userid, uid, source, payorderinfo, unpayorderinfo := "", "", "", "", "", "", ""
			if userData != nil {
				if phone == "" {
					phone = common.ObjToString((*userData)["phone"])
				}
				if IsInternal(phone) {
					continue
				}
				username = common.ObjToString((*userData)["name"])
				usernickname = common.ObjToString((*userData)["nickname"])
				uid = common.ObjToString((*userData)["uid"])
				userid = common.ObjToString((*userData)["userid"])
				bt := common.ObjToString((*userData)["belong_to"])
				if strings.HasPrefix(bt, "03") || bt == "0102" {
					continue
				}
			}
			if product_type == "历史数据" {
				if order_status == 0 {
					source = "线上历史数据导出未支付订单"
					unpayorderinfo = "历史数据"
				} else {
					source = "线上历史数据导出已支付订单"
					payorderinfo = "历史数据" + fmt.Sprint(common.IntAll(v["pay_money"])/100)
				}
			} else {
				if order_status == 0 {
					source = "线上数据流量包未支付订单"
					unpayorderinfo = "数据流量包"
				} else {
					source = "线上数据流量包已支付订单"
					payorderinfo = "数据流量包" + fmt.Sprint(common.IntAll(v["pay_money"])/100)
				}
			}
			key := fmt.Sprintf("%s_%s", source, phone)
			if _, ok := data[key]; ok {
				continue
			}
			data[key] = true
			dataArr = append(dataArr, map[string]interface{}{
				"createTime":     nowTime,
				"lastUpdateTime": nowTime,
				"phone":          phone,
				"username":       username,
				"usernickname":   usernickname,
				"uid":            uid,
				"userid":         userid,
				"company":        v["company_name"],
				"email":          v["user_mail"],
				"belongTo":       "大客户",
				"source":         source,
				"data_count":     v["data_count"],
				"unpayorderinfo": unpayorderinfo,
				"payorderinfo":   payorderinfo, //增加客户需求
				"appoint_email":  "",
			})

		}
		cfg.BigOrderTime = bigOrderTime
	}
	saveDataToRedis(data, "dk")
	xlsxArr := []string{"用户昵称", "姓名", "公司名称", "职位", "联系人电话", "购买条数", "用户邮箱", "已支付订单信息", "未支付订单类型", "销售线索来源", "具体来源", "数据需求"}
	if len(dataArr) > 0 {
		bigArr := map[string][]map[string]interface{}{}
		for _, v := range dataArr {
			minemail := ""
			if gconv.String(v["appoint_email"]) == "" {
				minemail, _ = EmailSelect("dk")
			} else {
				minemail = gconv.String(v["appoint_email"])
			}
			bigArr[minemail] = append(bigArr[minemail], v)
		}
		for k, vb := range bigArr {
			batch := FindBatch("dk")
			fileName, detailName := time.Now().Format(date.Date_Short_Layout)+"-"+batch+"大客户销售线索", ""
			xf := xlsx.NewFile()
			style := xlsx.NewStyle()
			style.Font.Size = 12
			style.Font.Bold = true
			style.Alignment.Vertical = "center"
			style.Alignment.Horizontal = "center"
			detailName = time.Now().Format(date.Date_Short_Layout) + "数据详情请查看附件"
			sh, _ := xf.AddSheet("线索数据")
			row1 := sh.AddRow()
			for _, x := range xlsxArr {
				cell := row1.AddCell()
				cell.SetString(x)
				cell.SetStyle(style)
			}
			for _, v := range vb {
				row := sh.AddRow()
				row.AddCell().SetString(common.ObjToString(v["usernickname"]))
				row.AddCell().SetString(common.ObjToString(v["username"]))
				row.AddCell().SetString(common.ObjToString(v["company"]))
				row.AddCell().SetString(common.ObjToString(v["job"]))
				row.AddCell().SetString(common.ObjToString(v["phone"]))
				row.AddCell().SetValue(common.IntAll(v["data_count"]))
				row.AddCell().SetString(common.ObjToString(v["email"]))
				row.AddCell().SetString(common.ObjToString(v["payorderinfo"]))
				row.AddCell().SetString(common.ObjToString(v["unpayorderinfo"]))
				row.AddCell().SetString(common.ObjToString(v["source"]))
				row.AddCell().SetString(common.ObjToString(v["interest"]))
				row.AddCell().SetString(common.ObjToString(v["data_requirement"]))
				//增加客户需求
			}
			email := k
			//email = "wanghao@jianyu360.com"
			dir := "./xlsx/dk/" + fileName + ".xlsx"
			err := xf.Save(dir)
			if err != nil {
				log.Println("xls error", err, dir)
			} else {

				status := mail.GSendMail_q("剑鱼标讯", email, "", "", fileName, detailName, dir, fileName+".xlsx", &Gmail)
				if status {
					log.Println("send mail success", fileName, email)
				}
			}
		}
	}
	common.WriteSysConfig(&cfg)
	log.Println("大客户线索定时任务结束")
}

func IsInternal(phone string) bool {
	if DataAnalysisService.Count("dwd_f_userbase_insider", map[string]interface{}{
		"mobile": phone,
	}) > 0 {
		return true
	}
	return false
}

func eventReg() {
	lastEventRegTime := cfg.LastEventRegTime
	sql := fmt.Sprintf(`select * from exhibition_sign_up where is_del = 0 and update_time > "%s" order by update_time asc`, lastEventRegTime)
	log.Println("活动报名表定时任务开始", sql)
	data := Jyactivities.SelectBySql(sql)
	if data != nil && *data != nil && len(*data) > 0 {
		for _, v := range *data {
			ok1, ok2, _ := FormatData(v, "eventReg")
			if !ok1 {
				common.WriteSysConfig(&cfg)
				log.Println("线索卡点", "eventReg", v, lastEventRegTime)
			} else {
				if !ok2 {
					log.Println("用户分配已达上限", "eventReg", v, lastEventRegTime)
					common.WriteSysConfig(&cfg)
				}
			}
			cfg.LastEventRegTime = common.ObjToString(v["update_time"])
		}
	}
	common.WriteSysConfig(&cfg)
	log.Println("活动报名表定时任务结束")
}

func saveHlyj(belong_to, item, phone, name, sourceName, cluename, position, nowTime string, isGroup, isCommerce int) {
	if strings.HasPrefix(belong_to, "02") && item == "eventReg" {
		saveMap := map[string]interface{}{
			"unique_id":      phone,
			"phone":          phone,
			"username":       name,
			"source":         sourceName,
			"status999":      "status5",
			"company":        cluename,
			"job":            position,
			"belongTo":       "市场部",
			"createTime":     nowTime,
			"lastUpdateTime": nowTime,
		}
		token := getToken()
		updateData := map[string]interface{}{
			"dbType":       "0001",
			"customerList": []map[string]interface{}{saveMap},
		}
		dataByte, _ := json.Marshal(&updateData)
		url := `https://a1.7x24cc.com/commonInte?flag=1007&account=N000000029739&accessToken=` + token + `&json=` + url.QueryEscape(string(dataByte))
		bs, err := doGet(url)
		if err != nil {
			log.Println("调用接口失败")
		} else {
			resMap := common.StringToMap(string(bs))
			if resMap["success"] != nil && resMap["success"].(bool) {
				saveMap["company_nature"] = isGroup
				saveMap["company_verification"] = isCommerce
				TiDbData.Insert("customer", saveMap)
			} else {
				log.Println("新增线索失败")
			}
		}
	}
}
func FindBatch(moudle string) string {
	now := time.Now().Format("2006-01-02")
	key := fmt.Sprintf("batch_%s_%s", now, moudle)
	yesterday := time.Now().AddDate(0, 0, -1).Format("2006-01-02")
	yesterdayKey := fmt.Sprintf("batch_%s_%s", yesterday, moudle)
	if ok, _ := redis.Exists("newother", yesterdayKey); ok {
		//删除之前数据
		redis.Del("newother", yesterdayKey)
	}
	batch := redis.Incr("newother", key)
	return fmt.Sprintf("%04d", batch)
}

func FindBatchData(moudle string) map[string]interface{} {
	now := time.Now().Format("2006-01-02")
	//数据提取
	keyContent := fmt.Sprintf("data_%s_%s", now, moudle)
	data := redis.Get("newother", keyContent)
	if data == nil {
		return make(map[string]interface{})
	} else {
		return gconv.Map(data)
	}

}

// 咨询组线索
func AdvisoryCommittee() {
	if !isRunning() {
		log.Println("不是工作日，任务暂停")
		return
	}
	log.Println("咨询部线索定时任务开始")

	sourceMap := buildSourceMap("咨询组")
	nowTime := time.Now().Format(date.Date_Full_Layout)
	startTime := getCommitteeTime(cfg.AdvisoryCommitteeTime)
	dataArr, endtime := processLeads("zx", startTime, sourceMap, nowTime)

	cfg.AdvisoryCommitteeTime = endtime
	exportToExcel(dataArr, "咨询服务销售线索", "zx")
	log.Println("咨询部线索定时任务结束")
	common.WriteSysConfig(&cfg)
}

// 运营部线索
func SelectionDepartment() {
	if !isRunning() {
		log.Println("不是工作日，任务暂停")
		return
	}
	log.Println("运营部线索定时任务开始")

	sourceMap := buildSourceMap("运营部")
	nowTime := time.Now().Format(date.Date_Full_Layout)
	startTime := getCommitteeTime(cfg.SelectionDepartmentTime)
	dataArr, endtime := processLeads("yy", startTime, sourceMap, nowTime)
	cfg.SelectionDepartmentTime = endtime
	exportToExcel(dataArr, "运营部销售线索", "yy")
	log.Println("运营部线索定时任务结束")
	common.WriteSysConfig(&cfg)
}

// 市场部线索
func MarketCustomer() {
	if !isRunning() {
		log.Println("不是工作日，任务暂停")
		return
	}
	log.Println("市场部线索定时任务开始")

	sourceMap := buildSourceMap("市场组")
	nowTime := time.Now().Format(date.Date_Full_Layout)
	startTime := getCommitteeTime(cfg.MarketSaleTime)
	dataArr, endtime := processLeads("sc", startTime, sourceMap, nowTime)
	cfg.MarketSaleTime = endtime
	exportToExcel(dataArr, "商务合作销售线索", "sc")
	common.WriteSysConfig(&cfg)
	log.Println("市场部线索定时任务结束")
}

// 判断是否在工作日
func isRunning() bool {
	return getRunOk()
}

// 从数据库构建来源映射
func buildSourceMap(department string) map[string]map[string]interface{} {
	sourceMap := map[string]map[string]interface{}{}
	saleSource := TiDb.SelectBySql(fmt.Sprintf(`SELECT source,name,appoint_email FROM d_saleleads_code WHERE department LIKE '%%%s%%' AND is_delete = 1`, department))
	if saleSource != nil {
		for _, v := range *saleSource {
			source := common.ObjToString(v["source"])
			name := common.ObjToString(v["name"])
			name = filterSourceName(name)
			appointEmail := common.ObjToString(v["appoint_email"])
			sourceMap[source] = map[string]interface{}{
				"name":         name,
				"appointEmail": appointEmail,
			}
		}
	}
	return sourceMap
}

// 过滤来源名称
func filterSourceName(name string) string {
	filterArr := []string{"-pc", "-app", "-wx", "-h5"}
	for _, s := range filterArr {
		name = strings.ReplaceAll(name, s, "")
	}
	return name
}

// 获取最后一条数据时间
func getCommitteeTime(cfgTime int64) int64 {
	if cfgTime == 0 {
		return time.Now().Unix()
	}
	return cfgTime
}

// 处理线索
func processLeads(batch string, committeeTime int64, sourceMap map[string]map[string]interface{}, nowTime string) ([]map[string]interface{}, int64) {
	dataArr := []map[string]interface{}{}
	endtime := int64(0)
	saleleadsData, ok := Mgo.Find("saleLeads", map[string]interface{}{"createtime": map[string]interface{}{"$gt": committeeTime}}, `{"phone":1,"createtime":1}`, nil, false, -1, -1)
	//saleleadsData, ok := Mgo.Find("saleLeads", map[string]interface{}{"createtime": committeeTime}, `{"phone":1,"createtime":1}`, nil, false, -1, -1)
	if ok && saleleadsData != nil {
		data := FindBatchData(batch)
		for _, v := range *saleleadsData {
			lead := map[string]interface{}{}
			lead, endtime = processLead(v, sourceMap, nowTime, committeeTime, data)
			if lead != nil {
				dataArr = append(dataArr, lead)
			}
		}
		saveDataToRedis(data, batch)
	}

	return dataArr, endtime
}

// 处理单个线索
func processLead(v map[string]interface{}, sourceMap map[string]map[string]interface{}, nowTime string, committeeTime int64, data map[string]interface{}) (map[string]interface{}, int64) {
	sources := common.ObjToString(v["source"])
	// 更新委员会时间
	if gconv.Int64(v["createtime"]) > committeeTime {
		committeeTime = gconv.Int64(v["createtime"])
	}
	sourceData, exist := sourceMap[sources]
	if !exist {
		return nil, committeeTime
	}
	phone := common.ObjToString(v["phone"])
	if IsInternal(phone) {
		return nil, committeeTime
	}
	// 获取用户信息
	userData := getUserData(v)
	if userData == nil || isUserInBlackList(userData) {
		return nil, committeeTime
	}
	key := fmt.Sprintf("%s_%s_%s", sourceMap[sources], phone, common.ObjToString(v["interest"]))
	if _, exists := data[key]; exists {
		return nil, committeeTime
	}
	data[key] = true

	return map[string]interface{}{
		"createTime":       nowTime,
		"createtime":       v["createtime"],
		"username":         common.ObjToString(v["name"]),
		"company":          common.ObjToString(v["company"]),
		"phone":            phone,
		"source":           gconv.String(sourceData["name"]),
		"belongTo":         "咨询组/市场组", // 根据需要修改
		"interest":         common.ObjToString(v["interest"]),
		"data_requirement": common.ObjToString(v["data_requirement"]),
		"branch":           v["branch"],
		"job":              v["position"],
		"email":            common.ObjToString(v["mail"]),
		"appointEmail":     gconv.String(sourceData["appointEmail"]),
	}, committeeTime
}

// 获取用户数据
func getUserData(v map[string]interface{}) map[string]interface{} {
	userid := common.ObjToString(v["userid"])
	userMapping := &map[string]interface{}{}
	if !mongodb.IsObjectIdHex(userid) {
		userMapping = TiDb.FindOne("dwd_f_userbase_id_mapping", map[string]interface{}{"position_id": userid}, "", "")
	} else {
		userMapping = TiDb.FindOne("dwd_f_userbase_id_mapping", map[string]interface{}{"userid": userid}, "", "")
	}

	if userMapping != nil && len(*userMapping) > 0 {
		return *userMapping
	}
	return nil
}

// 判断用户是否在黑名单
func isUserInBlackList(userData map[string]interface{}) bool {
	bt := common.ObjToString(userData["belong_to"])
	return strings.HasPrefix(bt, "03") || bt == "0102"
}

// 判断是否是安博会线索
func isABHLead(lead map[string]interface{}) bool {
	s_sourceid := common.ObjToString(lead["s_sourceid"])
	return s_sourceid == db.Sourceid
}

// 导出到 Excel
func exportToExcel(dataArr []map[string]interface{}, title, batch string) {
	if len(dataArr) == 0 {
		return
	}
	bigArr := map[string][]map[string]interface{}{}
	for _, v := range dataArr {
		appointEmail := gconv.String(v["appointEmail"])
		if appointEmail == "" {
			appointEmail, _ = EmailSelect(batch)
		}
		bigArr[appointEmail] = append(bigArr[appointEmail], v)
	}
	for email, arr := range bigArr {
		data := FindBatch(batch)
		xlsxArr := []string{"姓名", "联系方式", "职位", "部门", "邮箱", "公司名称", "咨询需求", "销售线索来源", "具体来源", "留资时间"}
		fileName, detailName := fmt.Sprintf("%s %s-%s", title, time.Now().Format(date.Date_Short_Layout), data), "今日新增销售线索，请查收附件，及时跟进。"
		xf := xlsx.NewFile()
		style := xlsx.NewStyle()
		style.Font.Size = 12
		style.Font.Bold = true
		style.Alignment.Vertical = "center"
		style.Alignment.Horizontal = "center"
		sh, _ := xf.AddSheet("线索数据")
		row1 := sh.AddRow()
		for _, x := range xlsxArr {
			cell := row1.AddCell()
			cell.SetString(x)
			cell.SetStyle(style)
		}

		for _, v := range arr {
			row := sh.AddRow()
			row.AddCell().SetString(common.ObjToString(v["username"]))
			row.AddCell().SetString(common.ObjToString(v["phone"]))
			row.AddCell().SetString(common.ObjToString(v["job"]))
			row.AddCell().SetString(common.ObjToString(v["branch"]))
			row.AddCell().SetString(common.ObjToString(v["email"]))
			row.AddCell().SetString(common.ObjToString(v["company"]))
			row.AddCell().SetString(common.ObjToString(v["data_requirement"]))
			row.AddCell().SetString(common.ObjToString(v["source"]))
			row.AddCell().SetString(common.ObjToString(v["interest"]))
			row.AddCell().SetString(gconv.Time(gconv.Int64(v["createtime"])).Format(date.Date_Full_Layout))
		}
		dir := fmt.Sprintf("./xlsx/%s/%s.xlsx", batch, fileName)
		if err := xf.Save(dir); err != nil {
			log.Println("xls error", err, dir)
			return
		}
		sendEmail(fileName, detailName, dir, email)
	}
}

// 发送邮件
func sendEmail(fileName, detailName, dir, email string) {
	gmail := &mail.GmailAuth{
		SmtpHost: db.Mail.SmtpHost,
		SmtpPort: db.Mail.SmtpPort,
		User:     db.Mail.User,
		Pwd:      db.Mail.Pwd,
	}
	if status := mail.GSendMail_q("剑鱼标讯", email, "", "", fileName, detailName, dir, fileName+".xlsx", gmail); status {
		log.Println("send mail success", fileName, email)
	}
}

// 保存数据到 Redis
func saveDataToRedis(data map[string]interface{}, batch string) {
	keyContent := fmt.Sprintf("data_%s_%s", time.Now().Format("2006-01-02"), batch)
	redis.Put("newother", keyContent, data, 86400)
}

// 邮箱选择处理
func EmailSelect(batch string) (string, string) {
	name := ""
	minemail := ""
	bigData := TiDb.SelectBySql("select * from dwd_f_crm_clue_big_autodraw_record where   type =?  ", batch)
	if bigData != nil && len(*bigData) > 0 {
		mincount := common.IntAll((*bigData)[0]["count"])
		for _, vv := range *bigData {
			vcount := common.IntAll(vv["count"])
			vemail := common.ObjToString(vv["email"])
			vName := common.ObjToString(vv["name"])
			if vcount <= mincount {
				mincount = vcount
				minemail = vemail
				name = vName
			}
		}
	}
	TiDb.UpdateOrDeleteBySql(`update dwd_f_crm_clue_big_autodraw_record set count = count + 1 where name = ? and   type=?`, name, batch)
	return minemail, name
}