datatools/clueSync/everything.go

package main

import (
	"fmt"
	"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)
	sql := fmt.Sprintf(`select * from user_source where channel_code = "JyChCoopA" and create_time > "%s" order by create_time asc`, lastEverythingTime)
	log.Println("三方渠道数据定时任务开始", sql)
	data := ThirdParty.SelectBySql(sql)
	if data != nil && *data != nil && len(*data) > 0 {
		for _, v := range *data {
			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, unique_id, belongTo, 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.IntAll((*udata)["source"])
				if userSource == 6 {
					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 = "一切都好登录"
				if is_assign == 1 || is_transfer == 1 || owner != "" {
					user_mold = 1
				} else {
					user_mold = 2
				}
			} else if state == 2 {
				sales_leads_source = "一切都好注册"
				if is_assign == 1 || is_transfer == 1 || owner != "" {
					user_mold = 3
				} else {
					user_mold = 4
				}
			}
			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,
					"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.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 string) bool {
	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, unique_id, belongTo, 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 {
		unique_id = common.ObjToString((*cuData)["unique_id"])
		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" {
		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":   "通过合作渠道一切就好登录",
			"createtime":  nowTime,
			"BCPCID":      common.GetRandom(32),
			"operator_id": -1,
		})
	} else if is_assign == 1 || is_transfer == 1 {
		isOk = true
	}
	return isOk
}

// 大客户线索-发邮件
func bigCustomer() {
	/*
		2、线索来源:
			(1)留资线索
			   根据留资维表：tidb/Jianyu_subjectdb/d_saleleads_code
			   根据维表中department字段包含“大客户”的所有source
			   Source对应的中文名称用维表中的name，name要去掉"-pc","-app","-wx","-h5"
			   查询sql如下：SELECT source,name FROM Jianyu_subjectdb.d_saleleads_code WHERE department LIKE '%大客户' AND is_delete = 1
			(2)订单线索
				①线上历史数据导出未支付订单
				②线上历史数据导出已支付订单
		3、线索处理逻辑
			(1)线索每天保留最新的一条数据
			(2)"具体来源", "数据需求" 这两个字段要将该用户当天作为线索进入大客户线索池的内容去重汇总，并用逗号分隔
		4、导出明细
			购买条数据：购买产品是“历史数据”，获取对应的购买条数
			已支付订单信息：产品类型-有效期-金额
			未支付订单信息：产品类型
		5、发送邮件
			(1)邮件主题：线索日期+大客户销售线索
			(2)收件人：
				"gaoxiang@jianyu360.com",
				"sunzhenjie@jianyu360.com",
				"fanzongdong@jianyu360.com",
				"chenzhaobiao@jianyu360.com"
			(3)发送内容
				①当天有线索，邮件正文为：线索日期+数据详情请查看附件。附件名称同主题名称
				②当天无线索，邮件正文为：线索日期+无数据。
			(4)发送时间
				每天一次，每天早上8：20
	*/
	log.Println("大客户线索定时任务开始")
	dataArr := []map[string]interface{}{}
	filterArr := []string{"-pc", "-app", "-wx", "-h5"}
	sourceMap := map[string]string{}
	saleSource := TiDb.SelectBySql(`SELECT source,name FROM d_saleleads_code WHERE department LIKE '%大客户%' AND is_delete = 1`)
	if saleSource != nil && len(*saleSource) > 0 {
		for _, v := range *saleSource {
			source := common.ObjToString(v["source"])
			name := common.ObjToString(v["name"])
			for _, s := range filterArr {
				name = strings.ReplaceAll(name, s, "")
			}
			sourceMap[source] = name
		}
	}
	nowTime := time.Now().Format(date.Date_Full_Layout)
	nowTimes := time.Now().AddDate(0, 0, -1)
	startTime := time.Date(nowTimes.Year(), nowTimes.Month(), nowTimes.Day(), 0, 0, 0, 0, time.Local).Unix()
	saleleadsData, ok := Mgo.Find("saleLeads", map[string]interface{}{"createtime": map[string]interface{}{"$gte": startTime, "$lt": startTime + 86400}}, nil, 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"])
				}
			}
			if sourceMap[sources] != "" {
				company := common.ObjToString(v["company"])
				phone := common.ObjToString(v["phone"])
				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"])
				source := sourceMap[sources]
				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") {
						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,
				})
			}
		}
	}
	orderData := Mysql.SelectBySql(`SELECT * FROM dataexport_order WHERE create_time >= "` + time.Now().AddDate(0, 0, -1).Format(date.Date_Short_Layout) + " 00:00:00" + `" AND create_time < "` + time.Now().Format(date.Date_Short_Layout) + " 00:00:00" + `" AND 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 {
			phone := common.ObjToString(v["user_phone"])
			order_status := common.IntAll(v["order_status"])
			userData := TiDb.FindOne("dwd_f_userbase_baseinfo", map[string]interface{}{"phone": phone}, "", "")
			username, usernickname, userid, uid, source, payorderinfo, unpayorderinfo := "", "", "", "", "", "", ""
			if userData != nil {
				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") {
					continue
				}
			}
			if order_status == 0 {
				source = "线上历史数据导出未支付订单"
				unpayorderinfo = "历史数据"
			} else {
				source = "线上历史数据导出已支付订单"
				payorderinfo = "历史数据" + fmt.Sprint(common.IntAll(v["pay_money"])/100)
			}
			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,
			})
		}
	}
	xlsxArr := []string{"用户昵称", "姓名", "公司名称", "职位", "联系人电话", "购买条数", "用户邮箱", "已支付订单信息", "未支付订单类型", "销售线索来源", "具体来源", "数据需求"}
	xf := xlsx.NewFile()
	style := xlsx.NewStyle()
	style.Font.Size = 12
	style.Font.Bold = true
	style.Alignment.Vertical = "center"
	style.Alignment.Horizontal = "center"
	fileName, detailName := time.Now().Format(date.Date_Short_Layout)+"大客户销售线索", ""
	if len(dataArr) > 0 {
		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 dataArr {
			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"]))
		}
	} else {
		detailName = time.Now().Format(date.Date_Short_Layout) + "无线索"
	}
	email := "xuzhiheng@topnet.net.cn,gaoxiang@topnet.net.cn,fanzongdong@topnet.net.cn,sunzhenjie@topnet.net.cn"
	// email = "xuzhiheng@jianyu360.com"
	dir := "./xlsx/" + fileName + ".xlsx"
	err := xf.Save(dir)
	if err != nil && len(dataArr) > 0 {
		log.Println("xls error", err, dir)
	} else if len(dataArr) == 0 {
		log.Println("xlsx数据量为0不发邮件")
	} else {
		gmail := &mail.GmailAuth{
			SmtpHost: "smtp.exmail.qq.com",
			SmtpPort: 465,
			User:     "public03@topnet.net.cn",
			Pwd:      "ue9Rg9Sf4CVtdm5a",
		}
		status := mail.GSendMail_q("剑鱼标讯", email, "", "", fileName, detailName, dir, fileName+".xlsx", gmail)
		if status {
			log.Println("send mail success", fileName, email)
		}
	}
	log.Println("大客户线索定时任务结束")
}

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, _ := FormatData(v, "eventReg")
			if !ok1 {
				common.WriteSysConfig(&cfg)
				break
			}
			cfg.LastEventRegTime = common.ObjToString(v["update_time"])
		}
	}
	common.WriteSysConfig(&cfg)
	log.Println("活动报名表定时任务结束")
}