userActionEtl/util.go

package main

import (
	"context"
	"fmt"
	"log"
	"net/url"
	"regexp"
	"strconv"
	"strings"
	"time"

	qu "app.yhyue.com/moapp/jybase/common"
	"app.yhyue.com/moapp/jybase/encrypt"
	"app.yhyue.com/moapp/jybase/mongodb"
	"github.com/gogf/gf/v2/frame/g"
	excelize "github.com/xuri/excelize/v2"
)

// 解析ip归属
func AnalyIp(ip string) (country, p, c, isp string) {
	res, err := Reader.SearchByStr(ip)
	if err != nil {
		log.Printf("GeoIP lookup error: %v", err)
		return
	}
	arr := strings.Split(res, "|")
	if len(arr) > 3 {
		return arr[0], arr[2], arr[3], arr[4]
	} else {
		return
	}
}

// 初始化模块
func Initmodule() {
	m := map[string]string{
		"/jy_mobile/common/order/create/svip":                                         "下单",
		"/jy_mobile/common/order/create/areapack":                                     "下单",
		"/page_entbase_mobile/page/business_management/people_management_branch.html": "工作台",
		"/jyapp/front/dataExport/buyerEdit":                                           "工作台",
		"/front/wx_dataExport/buyerEdit":                                              "工作台",
		"/jyapp/big/page/unit_portrayal":                                              "画像",
		"/swordfish/page_big_pc/unit_portrayal/":                                      "画像",
		"/weixin/pay/checkout_buyerPortraitPack":                                      "支付",
		"/jyapp/pay/checkout_buyerPortraitPack":                                       "支付",
		"/jy_mobile/common/order/create/buyerpack":                                    "下单",
		"/jy_mobile/search/middle/buyer":                                              "搜索",
		"/jy_mobile/search/result/buyer":                                              "搜索",
		"/front/invoice/check_invoice.html":                                           "订单",
		"/jyapp/front/invoice/check_invoice.html":                                     "订单",
		"/jyapp/vipsubscribe/toSubVipSetPage":                                         "订阅",
		"/front/vipsubscribe/toSubVipSetPage":                                         "订阅",
		"/weixin/pay/checkout_subvip":                                                 "支付",
		"/jyapp/pay/checkout_subvip":                                                  "支付",
		"/jy_mobile/common/order/create/datapack":                                     "下单",
		"/weixin/pay/checkout_member":                                                 "支付",
		"/jyapp/pay/checkout_member":                                                  "支付",
		"/jy_mobile/common/order/create/bigmember":                                    "下单",
		"/swordfish/page_big_pc/my_client":                                            "订阅",
		"/page_workDesktop/work-bench/app/big/big_subscribe?vt=q":                     "订阅",
		"/swordfish/page_big_pc/free/ent_follow":                                      "订阅",
		"/page_workDesktop/work-bench/app/big/big_subscribe?vt=v":                     "订阅",
		"/page_workDesktop/work-bench/app/big/big_subscribe?vt=m":                     "订阅",
		"/page_workDesktop/work-bench/app/big/big_subscribe?vt=f":                     "订阅",
		"/page_workDesktop/work-bench/app/big/big_subscribe?vt=s":                     "订阅",
		"/swordfish/page_big_pc/free/project_progress":                                "订阅",
		"/jyapp/vipsubscribe/toVIPViewPage":                                           "订阅",
		"/front/vipsubscribe/toVIPViewPage":                                           "订阅",
		"/weixin/pay/checkout_filePack":                                               "支付",
		"/jyapp/pay/checkout_filePack":                                                "支付",
		"/jy_mobile/common/order/create/filepack":                                     "下单",
		"/page_workDesktop/work-bench/app/big/workspace/dashboard":                    "工作台",
		"/jyapp/article/content/.*html":                                               "标讯",
		"/article/content/.*html":                                                     "标讯",
		"/jy_mobile/search/middle/supplier":                                           "搜索",
		"/jy_mobile/search/result/supplier":                                           "搜索",
		"/jyapp/vipsubscribe/toSetKeyWordPage":                                        "订阅",
		"/front/vipsubscribe/toSetKeyWordPage":                                        "订阅",
		"/weixin/pay/checkout_integral":                                               "支付",
		"/jyapp/pay/checkout_integral":                                                "支付",
		"/jy_mobile/tabbar/home":                                                      "首页",
		"/jyapp/jylab/mainSearch?isLogin=1":                                           "首页",
		"/jylab/mainSearch":                                                           "首页",
		"/page_docs_mobile/search":                                                    "搜索",
		"/page_docs_mobile/details/":                                                  "标讯",
		"/page_docs_mobile/purchase/":                                                 "下单",
		"/front/swordfish/toMyOrder":                                                  "订单",
		"/front/invoice/showpage":                                                     "订单",
		"/jyapp/front/invoice/showpage":                                               "订单",
		"/page_entniche_new/page/sub_management/sub_management_system.html":           "订阅",
		"/page_entniche_new/page/subsetting/sub_entrance.html":                        "订阅",
		"/jyapp/big/page/ent_portrait":                                                "画像",
		"/swordfish/page_big_pc/ent_portrait/":                                        "画像",
		"/jy_mobile/search/middle/company":                                            "搜索",
		"/jy_mobile/search/result/company":                                            "搜索",
		"/jyapp/pay/checkout_entniche":                                                "支付",
		"/front/invoice/cantInvoice":                                                  "订单",
		"/jyapp/front/cantInvoice":                                                    "订单",
		"/weixin/pay/checkout_areaPack":                                               "支付",
		"/jyapp/pay/checkout_areaPack":                                                "支付",
		"/jy_mobile/common/order/create/datareport":                                   "下单",
		"/weixin/pay/checkout_dataexport":                                             "支付",
		"/jyapp/pay/checkout_dataexport":                                              "支付",
		"/jy_mobile/common/order/create/dataexport":                                   "下单",
		"/weixin/pay/checkout_dataPack":                                               "支付",
		"/jyapp/pay/checkout_dataPack":                                                "支付",
		"/jyapp/free/me":                                                              "菜单",
		"/jyapp/front/myOrder/toMyOrder":                                              "订单",
		"/front/myOrder/toMyOrder":                                                    "订单",
		"/jyapp/vipsubscribe/toOrderDetailPage":                                       "订单",
		"/front/vipsubscribe/toOrderDetailPage":                                       "订单",
		"/jyapp/front/myorder/tointegralDetail":                                       "订单",
		"/front/wxMyOrder/integralDetail/":                                            "订单",
		"/front/wx_dataExport/wxToOrderDetail":                                        "订单",
		"/jyapp/front/dataExport/toOrderDetail":                                       "订单",
		"/weixin/common/medical/orderDetail":                                          "订单",
		"/jyapp/common/medical/orderDetail":                                           "订单",
		"/weixin/pay/xs_payNow":                                                       "订单",
		"/jyxsapp/orderMsg":                                                           "订单",
		"/datareport/page/order/detail":                                               "订单",
		"/jyapp/front/myorder/toEntnicheDetail":                                       "订单",
		"/front/wxMyOrder/memberDetail":                                               "订单",
		"/jyapp/big/page/orderdetail_member":                                          "订单",
		"/front/wxMyOrder/aiForecastPackdetail":                                       "订单",
		"/jyapp/big/page/orderdetail_aiForecastPack":                                  "订单",
		"/front/wxMyOrder/subAccountDetail":                                           "订单",
		"/jyapp/big/page/orderdetail_subAccount":                                      "订单",
		"/front/wxMyOrder/bidfileDetail":                                              "订单",
		"/jyapp/front/myorder/toBidfileDetail":                                        "订单",
		"/weixin/common/dataPack/orderDetail":                                         "订单",
		"/jyapp/common/dataPack/orderDetail":                                          "订单",
		"/weixin/common/filePack/orderDetail":                                         "订单",
		"/jyapp/common/filePack/orderDetail":                                          "订单",
		"/weixin/common/areaPack/orderDetail":                                         "订单",
		"/jyapp/common/areaPack/orderDetail":                                          "订单",
		"/weixin/common/buyerPortraitPack/orderDetail":                                "订单",
		"/jyapp/common/buyerPortraitPack/orderDetail":                                 "订单",
		"/weixin/common/dataFile/orderDetail":                                         "订单",
		"/jyapp/common/dataFile/orderDetail":                                          "订单",
		"/jy_mobile/tabbar/subscribe":                                                 "订阅",
		"/jyapp/swordfish/historypush":                                                "订阅",
		"/swordfish/newhistorypush":                                                   "订阅",
		"/swordfish/frontPage/collection/sess/index":                                  "标讯",
		"/weixin/frontPage/messageCenter/sess/index":                                  "工作台",
		"/jyapp/frontPage/messageCenter/sess/index":                                   "工作台",
		"/jy_mobile/tabbar/message":                                                   "工作台",
		"/swordfish/frontPage/messageCenter/sess/index":                               "工作台",
		"/jylab/purSearch/index.html":                                                 "搜索",
		"/search/issued":                                                              "搜索",
		"/succbi/nzj/app/nzj.app/nzj_search":                                          "搜索",
		"/jylab/entSearch/index.html":                                                 "搜索",
		"/jylab/supsearch/index.html":                                                 "搜索",
		"/jylab/medical/index.html":                                                   "搜索",
		"/page_workDesktop/work-bench/app/big/big_subscribe?vt=m&v":                   "推送",
		"/jy_mobile/search/filter-history/bidding":                                    "搜索",
		"jy_mobile/search/middle/bidding":                                             "搜索",
		"/jy_mobile/search/result/bidding":                                            "搜索",
		"/weixin/pay/checkout_onlineCourse":                                           "支付",
		"/jyapp/pay/checkout_onlineCourse":                                            "支付",
		"/jy_mobile/common/order/create/course":                                       "支付",
		"/jyapp/free/register":                                                        "注册",
		"/jyapp/free/login":                                                           "登录",
		"/jyapp/free/setPwd":                                                          "注册",
		"/jyapp/account/phone/bind":                                                   "注册",
		"/front/account/phone/bind":                                                   "注册",
		"/swordfish/frontPage/userMerge/sess/bind":                                    "注册",
		"/succbi/YXT/app/medical.app/zkh_search.spg":                                  "搜索",
		"/succbi/YXT/app/medical.app/zkh_detail.spg?JGDM=":                            "搜索",
		"/succbi/YXT/app/medical.app/zhzhb_search.spg":                                "搜索",
		"/succbi/YXT/app/medical.app/zhzhb_detail.spg?jxs_hx=":                        "搜索",
		"/succbi/YXT/app/medical.app/scdc_detail.spg":                                 "搜索",
		"/succbi/YXT/app/medical.app/zsj_search.spg":                                  "搜索",
		"/jyapp/frontPage/activity/free/app-register":                                 "注册",
		"/jyapp/followent/detail/.*":                                                  "订阅",
		"/jyapp/followent/newInfo/.*":                                                 "订阅",
		"/jy_mobile/order/create/dataexport?source=d&id=.*":                           "下单",
		"/jy_mobile/redirect/unit_portrayal/":                                         "画像",
		"/jyapp/swordfish/historypush?f=push&t=free&pushtime=":                        "订阅",
		"/jyapp/swordfish/historypush?f=push&t=vip&pushtime=":                         "订阅",
		"/jy_mobile/order/create/svip?tip=":                                           "下单",
		"/swordfish/page_big_pc/recommen-list?vt=":                                    "标讯",
		"/jy_mobile/tabbar/recommendedlist":                                           "订阅",
		"/swordfish/page_big_pc/search/sun":                                           "搜索",
		"/page_workDesktop/work-bench/app/big/search/sun":                             "搜索",
		"/jy_mobile/search/middle/sun":                                                "搜索",
		"/jy_mobile/search/result/sun":                                                "搜索",
		"/search/construction":                                                        "搜索",
		"jy_mobile/ai/search":                                                         "搜索",
		"/debrisproduct/free/login":                                                   "登录",
		"/debrisproduct/orderDetail":                                                  "订单",
		"/debrisproduct/orderList":                                                    "订单",
		"/debrisproduct/getBiddingDetail":                                             "标讯",
		"/debrisproduct/createOrder":                                                  "订单",
		"/debrisproduct/info":                                                         "订阅",
		"/debrisproduct/updateArea":                                                   "订阅",
		"/debrisproduct/userInfo":                                                     "订阅",
		"/front/subscribeTransfer":                                                    "订阅",
		"/article/entservice":                                                         "标讯",
		"/biddetail/normal":                                                           "标讯",
		"/front/pcAjaxReq":                                                            "搜索",
		"/front/tenderSubscribe":                                                      "订阅",
		"/front/vipsubscribe":                                                         "订阅",
		"/jylab/entSearch":                                                            "搜索",
		"/jylab/purSearch":                                                            "搜索",
		"/jylab/supsearch":                                                            "搜索",
		"/wxkeyset/keyset":                                                            "订阅",
		"/user/phone/bind":                                                            "注册",
	}

	insertData := []map[string]interface{}{}
	ctx := context.Background()
	rows, err := Ch_userbehavior.Query(ctx, `select name,id from module`)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	moduleMap := map[string]int64{}
	for rows.Next() {
		var module_id uint64
		var href string

		if err := rows.Scan(m); err != nil {
			if err := rows.Scan(
				&href,
				&module_id,
			); err != nil {
				log.Fatal(err)
			}
			log.Println(href, module_id)
			moduleMap[href] = int64(module_id)
		}
	}
	for k, v := range m {
		insertData = append(insertData, map[string]interface{}{
			"url":       k,
			"module_id": moduleMap[v],
		})
	}
	g.DB().Model("userbehavior.module_details").Data(insertData).Insert()
}

// GetModule 模块
func GetModule() map[string]*Moduledetail {
	ctx := context.Background()
	rows, err := Ch_userbehavior.Query(ctx, `select name,id from module`)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	moduleIdMap := map[int64]string{}
	for rows.Next() {
		var module_id uint64
		var href string

		if err := rows.Scan(
			&href,
			&module_id,
		); err != nil {
			log.Fatal(err)
		}
		moduleIdMap[int64(module_id)] = href

	}

	rows, err = Ch_userbehavior.Query(ctx, `select module_id,url,desc,platform,product_name,page_name from module_details`)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	moduleMap := map[string]*Moduledetail{}
	for rows.Next() {
		var module_id uint64
		var href string
		var desc string
		var platform string
		var product_name string
		var page_name string

		if err := rows.Scan(
			&module_id,
			&href,
			&desc,
			&platform,
			&product_name,
			&page_name,
		); err != nil {
			log.Fatal(err)
		}
		//moduleMap[href] = moduleIdMap[int64(module_id)]
		moduleMap[href] = &Moduledetail{
			href,
			int64(module_id),
			desc,
			platform,
			product_name,
			page_name,
			moduleIdMap[int64(module_id)],
		}
	}
	return moduleMap
}

func MatchRegexModule() *CompiledRegexMap {
	m := GetModule()
	rm := RegexMap{}
	for k, v := range m {
		rm[k] = v
	}
	compiledRegexMap, err := NewCompiledRegexMap(rm)
	if err != nil {
		fmt.Println("Error:", err)
		return nil
	}
	return compiledRegexMap
}

func GetActionMap() map[string]int64 {
	m := map[string]int64{}
	ctx := context.Background()
	rows, err := Ch_userbehavior.Query(ctx, `select name,id from action`)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	for rows.Next() {
		var id uint64
		var name string

		if err := rows.Scan(
			&name,
			&id,
		); err != nil {
			log.Fatal(err)
		}
		m[name] = int64(id)
	}

	return m
}

func GetIpSource(ip string) (area, city string) {
	if strings.Contains(ip, ",") {
		for _, v := range strings.Split(ip, ",") {
			if strings.Contains(v, "172.17.") {
				continue
			}
			_, area, city, _ = AnalyIp(v)
			return area, city
		}
	}
	return area, city
}

func GetReferType(refer string) string {
	refertype := "域外"
	if strings.Contains(refer, "jianyu360") {
		refertype = "域内"
	}
	return refertype
}

// debris_product_logs 查询语句
func debris_product_logs(st, et int64) map[string]interface{} {
	s, e := GetCurTimePiInfo(st, et)
	query := map[string]interface{}{
		"_id": map[string]interface{}{
			"$gte": mongodb.StringTOBsonId(s),
			"$lt":  mongodb.StringTOBsonId(e),
		},
		"url": map[string]interface{}{
			"$in": []string{
				"/debrisproduct/free/login",
				"/debrisproduct/orderDetail",
				"/debrisproduct/orderList",
				"/debrisproduct/getBiddingDetail",
				"/debrisproduct/createOrder",
				"/debrisproduct/info",
				"/debrisproduct/updateArea",
				"/debrisproduct/userInfo",
			},
		},
	}
	return query
}

func subscribepay(st, et int64) map[string]interface{} {
	s, e := GetCurTimePiInfo(st, et)
	query := map[string]interface{}{
		"_id": map[string]interface{}{
			"$gte": mongodb.StringTOBsonId(s),
			"$lt":  mongodb.StringTOBsonId(e),
		},
		"url": map[string]interface{}{
			"$in": []string{
				"/jypay/user/phone/bind",
				"/jypay/callback/aliPay",
				"/jypay/callback/wxPay",
			},
		},
	}
	return query
}

func jylog_jyapplog(st, et int64) map[string]interface{} {
	s, e := GetCurTimePiInfo(st, et)
	query := map[string]interface{}{
		"_id": map[string]interface{}{
			"$gte": mongodb.StringTOBsonId(s),
			"$lt":  mongodb.StringTOBsonId(e),
		},
		"$or": []map[string]interface{}{
			//map[string]interface{}{
			//	"url": map[string]interface{}{
			//		"$in": []string{
			//			"/jypay/user/phone/bind",
			//		},
			//	},
			//},
			map[string]interface{}{
				"url": map[string]interface{}{
					"$regex": "/content/",
				},
			},
			map[string]interface{}{
				"url": map[string]interface{}{
					"$regex": "/login",
				},
			},
			map[string]interface{}{
				"url": map[string]interface{}{
					"$regex": "/register",
				},
			},
		},
	}
	return query
}

// 获取每天0点数据
func GetCurTimePiInfo(start, end int64) (string, string) {
	gteid := strconv.FormatInt(start, 16) + "0000000000000000"
	ltid := strconv.FormatInt(end, 16) + "0000000000000000"
	return gteid, ltid
}

// GetQuery 获取查询语句
func GetQuery(dbname string, s, e int64) (query map[string]interface{}) {
	if dbname == "subscribepay_logs" {
		return subscribepay(s, e)
	}
	if dbname == "jy_logs" || dbname == "jyapp_logs" {
		return jylog_jyapplog(s, e)
	}
	if dbname == "debris_product_logs" {
		return debris_product_logs(s, e)
	}
	return nil
}

// TimeStampToDate时间戳转时间
func TimeStampToDate(timestamp int64) time.Time {
	// 判断时间戳的长度
	if timestamp > 9999999999 { // 毫秒级时间戳通常大于10位
		// 毫秒级时间戳，除以1000转换为秒级时间戳
		return time.Unix(timestamp/1000, (timestamp%1000)*int64(time.Millisecond)).UTC()
	}
	return time.Unix(timestamp, 0).UTC()

}

// Unescape解析url
func Unescape(href string) string {
	str, err := url.QueryUnescape(href)
	if err != nil {
		log.Println(err)
	}
	return str
}

func GetPlatform(dbname, client, refer string) string {
	if dbname == "jyapp_logs" {
		// 检查是否为 Android 客户端
		if strings.Contains(client, "Android") {
			return "android"
		}
		// 检查是否为 iOS 客户端
		if strings.Contains(client, "iPhone") || strings.Contains(client, "iPad") || strings.Contains(client, "iPod") {
			return "ios"
		}
		return "ios"
	} else {
		if RegWx.MatchString(client) {
			return "wx"
		} else {
			if strings.Contains(refer, "h5.") {
				return "h5"
			} else {
				return "pc"
			}
		}
	}
	return ""
}

// 初始化模块
func InitUrl() {

	insertData := []map[string]interface{}{}
	ctx := context.Background()
	rows, err := Ch_userbehavior.Query(ctx, `select name,id from module`)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	moduleMap := map[string]int64{}
	for rows.Next() {
		var module_id uint64
		var href string

		if err := rows.Scan(
			&href,
			&module_id,
		); err != nil {
			log.Fatal(err)
		}
		//log.Println(href, module_id)
		moduleMap[href] = int64(module_id)
	}
	f, err := excelize.OpenFile("./url.xlsx")
	if err != nil {
		fmt.Println(err)
		return
	}
	defer func() {
		// 关闭文件
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()

	// 获取工作表名称
	sheetName := "sheet1"
	// 获取工作表中的所有行
	rowsx, err := f.GetRows(sheetName)
	if err != nil {
		fmt.Println(err)
		return
	}

	for i, row := range rowsx {
		log.Println("count:", i)
		if i == 0 {
			continue
		}
		href := row[0]
		mid := int64(0)
		if len(row) > 1 {
			mid = qu.Int64All(qu.If(row[1] == "", 0, moduleMap[row[1]]))
		}
		desc := ""
		if len(row) > 2 {
			desc = qu.If(row[2] == "", "", row[2]).(string)
		}
		platform := ""
		productname := ""
		pagename := ""
		if len(row) > 3 {
			platform = qu.If(row[3] == "", "", row[3]).(string)
		}
		if len(row) > 4 {
			productname = qu.If(row[4] == "", "", row[4]).(string)
		}
		if len(row) > 5 {
			pagename = qu.If(row[5] == "", "", row[5]).(string)
		}
		insertData = append(insertData, map[string]interface{}{
			"url":          href,
			"module_id":    mid,
			"desc":         desc,
			"platform":     platform,
			"product_name": productname,
			"page_name":    pagename,
		})
	}

	g.DB().Model("userbehavior.module_details").Data(insertData).Insert()
}

// ExtractOrderCode 从URL中提取order code
func ExtractOrderCode(url string) string {
	// 正则表达式，匹配 odercode=xxx, orderCode=xxx 或 order_code=xxx
	matches := OderCodeReg.FindStringSubmatch(url)
	if len(matches) >= 3 {
		//log.Println(matches)
		return matches[2]
	}
	// 没有找到order code
	return ""
}

var (
	ArticleIdReg = regexp.MustCompile(".*article/content/(.*)\\.html")
	OderCodeReg  = regexp.MustCompile(`(?i)(odercode|orderCode|order_code)=([a-zA-Z0-9]+)`)
)

// GetArticleId 获取三级页id
func GetArticleId(url string) string {
	ur := ArticleIdReg.FindStringSubmatch(url)
	if len(ur) > 1 {
		uarr := BDecodeArticleId2ByCheck(ur[1], encrypt.SE, encrypt.SE2)
		if len(uarr) > 0 {
			return uarr[0]
		}
	}
	return ""
}

// 短地址解密,二次解密带校验和
func BDecodeArticleId2ByCheck(id string, s1, s2 *encrypt.SimpleEncrypt) []string {
	if !strings.Contains(id, "+") { //新加密算法解密
		id, _ = url.QueryUnescape(id)
	}
	if len(id) > 2 {
		kstr := s2.DecodeStringByCheck(id[3:])
		return strings.Split(s1.DecodeStringByCheck(kstr), ",")
	}
	return []string{}
}

//TODO
//select * from data_analysis.dwd_f_personnel_behavior where breaker_name='登录'
//平台  应用  模块 页面 元素

// AddPathCodeId 获取id，自增
func AddPathCodeId() int64 {
	data := Ch.SelectBySql(`select id from userbehavior.path_code_id order by id desc limit 1`)
	if data != nil && len(*data) > 0 {
		id := qu.Int64All((*data)[0]["id"])
		newId := id + 1
		Ch.Insert("path_code_id", map[string]interface{}{
			"id": newId,
		})
		return newId
	} else {
		Ch.Insert("path_code_id", map[string]interface{}{
			"id": 1,
		})
		return 1
	}
}

// MgoTrustedIdToCkTrustedId mgo的匿名id和clickhouse的不一致 需要转换
func MgoTrustedIdToCkTrustedId(s string) string {
	if s == "" {
		return ""
	}
	return encrypt.SE.DecodeString(s)
}