jyps/xlsx/main.go

package main

import (
	"context"
	"fmt"
	"github.com/tealeg/xlsx"
	"github.com/xuri/excelize/v2"
	"go.mongodb.org/mongo-driver/bson"
	"go.mongodb.org/mongo-driver/mongo"
	"go.mongodb.org/mongo-driver/mongo/options"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	util "jygit.jydev.jianyu360.cn/data_processing/common_utils"
	"jygit.jydev.jianyu360.cn/data_processing/common_utils/mongodb"
	"log"
	"net/url"
	"os"
	"regexp"
	"sort"
	"strings"
)

type WinnerData struct {
	Winner   string `bson:"winner"`
	CreditNo string `bson:"credit_no"`
	Num      int    `bson:"num"`
}

func main() {
	//导出数据到Excel
	//exportBidding()

	//fmt.Println(11111)
	//HighBidding()
	//exportQy()
	//exportPhone3() // 导出联系电话
	//clickhouseData()
	//getName()
	companyStatus() //标记公司使用状态
	log.Println("over")
}

// exportWinner 导出
func exportWinner() {
	username := "SJZY_RWbid_ES"
	password := "SJZY@B4i4D5e6S"
	//addr := "172.17.189.140:27080"
	addr := "127.0.0.1:27083"
	direct := true
	if !strings.Contains(addr, "127") {
		direct = false
	}
	escapedUsername := url.QueryEscape(username)
	escapedPassword := url.QueryEscape(password)
	urls := fmt.Sprintf("mongodb://%s:%s@%s", escapedUsername, escapedPassword, addr)
	clientOptions := options.Client().ApplyURI(urls).SetDirect(direct)

	// 连接到MongoDB
	client, err := mongo.Connect(context.TODO(), clientOptions)
	if err != nil {
		log.Fatal(err)
	}
	defer func() {
		if err := client.Disconnect(context.TODO()); err != nil {
			log.Fatal(err)
		}
	}()

	// 检查连接
	err = client.Ping(context.Background(), nil)
	if err != nil {
		log.Fatal(err)
	}

	log.Println(1111)
	// 选择数据库和集合
	collection := client.Database("qfw").Collection("wcc_20240103")
	// 查询数据并排序
	// 设置查询条件
	filter := bson.D{
		//{"comeintime", bson.M{"$gte": 1640966400, "$lte": 1703952000}},
		//{"subtype", bson.M{"$in": []string{"中标", "单一", "成交", "合同"}}},
	}
	// 设置投影，排除 contenthtml 字段
	projection := bson.D{
		{"title", 1},      // 0表示不返回该字段
		{"detail", 1},     // 0表示不返回该字段
		{"href", 1},       // 0表示不返回该字段
		{"bidding_id", 1}, // 0表示不返回该字段
		{"subtype", 1},    // 0表示不返回该字段
		{"jyhref", 1},     // 0表示不返回该字段
		{"data_type", 1},  // 0表示不返回该字段
		{"toptype", 1},    // 0表示不返回该字段
	}
	//findOptions := options.Find().SetProjection(projection)
	findOptions := options.Find().SetSort(map[string]int{"num": -1}).SetLimit(100000).SetProjection(projection)
	cursor, err := collection.Find(context.Background(), filter, findOptions)
	if err != nil {
		log.Fatal(err)
	}
	defer cursor.Close(context.Background())

	// 创建 Excel 文件
	file := xlsx.NewFile()
	sheet, err := file.AddSheet("Sheet1")
	if err != nil {
		log.Fatal(err)
	}

	// 添加表头
	row := sheet.AddRow()
	row.AddCell().SetValue("Winner")
	row.AddCell().SetValue("Credit No")
	row.AddCell().SetValue("Num")

	// 遍历结果集并写入 Excel 文件
	for cursor.Next(context.Background()) {
		var winnerData WinnerData
		if err := cursor.Decode(&winnerData); err != nil {
			log.Fatal(err)
		}

		if !strings.Contains(winnerData.Winner, "公司") {
			continue
		}
		row = sheet.AddRow()
		row.AddCell().SetValue(winnerData.Winner)
		row.AddCell().SetValue(winnerData.CreditNo)
		row.AddCell().SetInt(winnerData.Num)
	}

	// 保存 Excel 文件
	outputFile, err := os.Create("exported_data.xlsx")
	if err != nil {
		log.Fatal(err)
	}
	defer outputFile.Close()

	err = file.Write(outputFile)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Println("数据已成功导出到 exported_data.xlsx")
}

// exportPhone 根据企业名单，导出联系人电话;凭安和标讯抽取到的
func exportPhone() {
	Mgo := &mongodb.MongodbSim{
		//MongodbAddr: "172.17.189.140:27080",
		MongodbAddr: "127.0.0.1:27083",
		Size:        10,
		DbName:      "mixdata",
		UserName:    "SJZY_RWbid_ES",
		Password:    "SJZY@B4i4D5e6S",
		Direct:      true,
	}
	Mgo.InitPool()

	//

	username := "datascbi"
	password := "Da#Bi20221111SC"
	host := "127.0.0.1:4001"
	//host := "172.17.162.25:4000"
	database := "global_common_data"
	dsn := fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=utf8mb4&parseTime=True&loc=Local", username, password, host, database)
	// 连接到数据库
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		log.Println("Failed to connect to database:", err)
		return
	}

	f, err := excelize.OpenFile("./河南省物业企业名单.xlsx")
	if err != nil {
		fmt.Println(err)
		return
	}
	defer func() {
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()

	rows, err := f.GetRows("Sheet1")
	if err != nil {
		fmt.Println(err)
		return
	}
	type BaseInfo struct {
		Name   string `json:"name"`
		NameId string `json:"name_id"`
	}
	type Contact struct {
		ContactName string `json:"contact_name"`
		ContactTel  string `json:"contact_tel"`
	}
	for i := 1; i < len(rows); i++ {
		name := rows[i][1]
		if !strings.Contains(name, "公司") {
			continue
		}
		log.Println(i, "----", name)
		stds, _ := Mgo.FindOne("qyxy_std", map[string]interface{}{"company_name": name})
		var reportsMap = make([]string, 0)
		contactsMap := make([]string, 0)
		if reports, ok := (*stds)["annual_reports"]; ok {
			if rs, ok := reports.([]interface{}); ok {
				for _, v := range rs {
					if da, ok := v.(map[string]interface{}); ok {
						if util.ObjToString(da["operator_name"]) != "" && util.ObjToString(da["company_phone"]) != "" {
							tm := util.ObjToString(da["operator_name"]) + "_" + util.ObjToString(da["company_phone"])
							if !IsInStringArray(tm, reportsMap) {
								reportsMap = append(reportsMap, tm)
							}
						} else if util.ObjToString(da["company_phone"]) != "" {
							if !IsInStringArray(util.ObjToString(da["company_phone"]), reportsMap) {
								reportsMap = append(reportsMap, util.ObjToString(da["company_phone"]))
							}
						}
					}

				}
			}
		}
		if len(reportsMap) > 0 {
			res := strings.Join(reportsMap, ",")
			f.SetCellValue("Sheet1", fmt.Sprintf("E%v", i+1), res)
		}

		baseinfo := BaseInfo{}
		db.Table("dws_f_ent_baseinfo").Select("name", "name_id").Where("name = ? ", name).Scan(&baseinfo)
		if baseinfo.NameId != "" {
			contacts := []Contact{}
			db.Table("dws_f_ent_contact").Select("contact_name", "contact_tel").Where("name_id = ? ", baseinfo.NameId).Scan(&contacts)
			if len(contacts) > 0 {
				for _, v := range contacts {
					if strings.Contains(v.ContactTel, ">") {
						continue
					}
					if v.ContactName != "" && v.ContactTel != "" {
						s := v.ContactName + "_" + v.ContactTel
						if !IsInStringArray(s, contactsMap) {
							contactsMap = append(contactsMap, s)
						}
					} else {
						if !IsInStringArray(v.ContactTel, contactsMap) {
							contactsMap = append(contactsMap, v.ContactTel)
						}
					}
				}
			}
		}
		if len(contactsMap) > 0 {
			res := strings.Join(contactsMap, ",")
			f.SetCellValue("Sheet1", fmt.Sprintf("F%v", i+1), res)
		}
	}

	f.Save()
}

// exportPhone 根据企业名单，导出联系人电话,只要抽取到的数据，联系人，联系电话 只要一个
func exportPhone2() {
	username := "datascbi"
	password := "Da#Bi20221111SC"
	host := "127.0.0.1:4001"
	//host := "172.17.162.25:4000"
	database := "global_common_data"
	dsn := fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=utf8mb4&parseTime=True&loc=Local", username, password, host, database)
	// 连接到数据库
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		log.Println("Failed to connect to database:", err)
		return
	}

	f, err := excelize.OpenFile("./北京代理机构.xlsx")
	if err != nil {
		fmt.Println(err)
		return
	}
	defer func() {
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()

	rows, err := f.GetRows("Sheet1")
	if err != nil {
		fmt.Println(err)
		return
	}
	type BaseInfo struct {
		Name   string `json:"name"`
		NameId string `json:"name_id"`
	}
	type Contact struct {
		ContactName string `json:"contact_name"`
		ContactTel  string `json:"contact_tel"`
	}
	for i := 1; i < len(rows); i++ {
		name := rows[i][0]
		if !strings.Contains(name, "公司") {
			continue
		}
		log.Println(i, "----", name)
		//contactsMap := make([]string, 0)

		//stds, _ := Mgo.FindOne("qyxy_std", map[string]interface{}{"company_name": name})
		//var reportsMap = make([]string, 0)
		//if reports, ok := (*stds)["annual_reports"]; ok {
		//	if rs, ok := reports.([]interface{}); ok {
		//		for _, v := range rs {
		//			if da, ok := v.(map[string]interface{}); ok {
		//				if util.ObjToString(da["operator_name"]) != "" && util.ObjToString(da["company_phone"]) != "" {
		//					tm := util.ObjToString(da["operator_name"]) + "_" + util.ObjToString(da["company_phone"])
		//					if !IsInStringArray(tm, reportsMap) {
		//						reportsMap = append(reportsMap, tm)
		//					}
		//				} else if util.ObjToString(da["company_phone"]) != "" {
		//					if !IsInStringArray(util.ObjToString(da["company_phone"]), reportsMap) {
		//						reportsMap = append(reportsMap, util.ObjToString(da["company_phone"]))
		//					}
		//				}
		//			}
		//
		//		}
		//	}
		//}
		//if len(reportsMap) > 0 {
		//	res := strings.Join(reportsMap, ",")
		//	f.SetCellValue("Sheet1", fmt.Sprintf("E%v", i+1), res)
		//}

		baseinfo := BaseInfo{}
		db.Table("dws_f_ent_baseinfo").Select("name", "name_id").Where("name = ? ", name).Scan(&baseinfo)
		if baseinfo.NameId != "" {
			contacts := []Contact{}
			db.Table("dws_f_ent_contact").Select("contact_name", "contact_tel").Where("name_id = ? ", baseinfo.NameId).Order("publishtime desc").Scan(&contacts)
			if len(contacts) > 0 {
				for _, v := range contacts {
					if strings.Contains(v.ContactTel, ">") {
						continue
					}
					if !validateMobileNumber(v.ContactTel) {
						continue
					}
					if v.ContactName != "" && v.ContactTel != "" {
						f.SetCellValue("Sheet1", fmt.Sprintf("D%v", i+1), v.ContactName)
						f.SetCellValue("Sheet1", fmt.Sprintf("E%v", i+1), v.ContactTel)
						break
					}
				}
			}
		}
	}

	f.Save()
}

// exportPhone3 根据企业名单导出联系人，联系电话，只要抽取到的
func exportPhone3() {
	username := "datascbi"
	password := "Da#Bi20221111SC"
	host := "127.0.0.1:4001"
	//host := "172.17.162.25:4000"
	database := "global_common_data"
	dsn := fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=utf8mb4&parseTime=True&loc=Local", username, password, host, database)
	// 连接到数据库
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		log.Println("Failed to connect to database:", err)
		return
	}

	f, err := excelize.OpenFile("./北京代理机构.xlsx")
	if err != nil {
		fmt.Println(err)
		return
	}
	defer func() {
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()

	rows, err := f.GetRows("Sheet1")
	if err != nil {
		fmt.Println(err)
		return
	}
	type BaseInfo struct {
		Name   string `json:"name"`
		NameId string `json:"name_id"`
	}
	type Contact struct {
		ContactName string `json:"contact_name"`
		ContactTel  string `json:"contact_tel"`
	}
	line := 1
	for i := 1; i < len(rows); i++ {
		name := rows[i][0]
		if !strings.Contains(name, "公司") {
			continue
		}
		log.Println(i, "----", name)
		//contactsMap := make([]string, 0)

		//stds, _ := Mgo.FindOne("qyxy_std", map[string]interface{}{"company_name": name})
		//var reportsMap = make([]string, 0)
		//if reports, ok := (*stds)["annual_reports"]; ok {
		//	if rs, ok := reports.([]interface{}); ok {
		//		for _, v := range rs {
		//			if da, ok := v.(map[string]interface{}); ok {
		//				if util.ObjToString(da["operator_name"]) != "" && util.ObjToString(da["company_phone"]) != "" {
		//					tm := util.ObjToString(da["operator_name"]) + "_" + util.ObjToString(da["company_phone"])
		//					if !IsInStringArray(tm, reportsMap) {
		//						reportsMap = append(reportsMap, tm)
		//					}
		//				} else if util.ObjToString(da["company_phone"]) != "" {
		//					if !IsInStringArray(util.ObjToString(da["company_phone"]), reportsMap) {
		//						reportsMap = append(reportsMap, util.ObjToString(da["company_phone"]))
		//					}
		//				}
		//			}
		//
		//		}
		//	}
		//}
		//if len(reportsMap) > 0 {
		//	res := strings.Join(reportsMap, ",")
		//	f.SetCellValue("Sheet1", fmt.Sprintf("E%v", i+1), res)
		//}

		baseinfo := BaseInfo{}
		db.Table("dws_f_ent_baseinfo").Select("name", "name_id").Where("name = ? ", name).Scan(&baseinfo)
		if baseinfo.NameId != "" {
			contacts := []Contact{}
			db.Table("dws_f_ent_contact").Select("contact_name", "contact_tel").Where("name_id = ? ", baseinfo.NameId).Order("publishtime desc").Scan(&contacts)
			if len(contacts) > 0 {
				for _, v := range contacts {
					if strings.Contains(v.ContactTel, ">") {
						continue
					}
					if !validateMobileNumber(v.ContactTel) {
						continue
					}
					if v.ContactName != "" && v.ContactTel != "" {
						f.SetCellValue("Sheet1", fmt.Sprintf("C%v", line+1), name)
						f.SetCellValue("Sheet1", fmt.Sprintf("D%v", line+1), v.ContactName)
						f.SetCellValue("Sheet1", fmt.Sprintf("E%v", line+1), v.ContactTel)
						line++
					}
				}
			}
		}
	}

	f.Save()
}

func test() {
	//Mgo := &mongodb.MongodbSim{
	//	//MongodbAddr: "172.17.189.140:27080",
	//	MongodbAddr: "192.168.3.166:27082",
	//	Size:        10,
	//	DbName:      "majiajia",
	//	//UserName:    "SJZY_RWbid_ES",
	//	//Password:    "SJZY@B4i4D5e6S",
	//	//Direct:      true,
	//}
	//Mgo.InitPool()
	MgoP := &mongodb.MongodbSim{
		//MongodbAddr: "172.17.189.140:27080",
		MongodbAddr: "127.0.0.1:27080",
		Size:        10,
		DbName:      "qfw",
		Direct:      true,
		//UserName:    "SJZY_RWbid_ES",
		//Password:    "SJZY@B4i4D5e6S",

	}
	MgoP.InitPool()

	f, err := excelize.OpenFile("./河南物业.xlsx")
	if err != nil {
		fmt.Println(err)
		return
	}
	defer func() {
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()

	rows, err := f.GetRows("Sheet1")
	if err != nil {
		fmt.Println(err)
		return
	}

	for i := 1; i < len(rows); i++ {
		id := rows[i][0]
		rs, _ := MgoP.FindById("projectset_20230904", id, nil)
		if rs == nil {
			continue
		}
		if phone, ok := (*rs)["buyer"]; ok {
			if util.ObjToString(phone) != "" {
				f.SetCellValue("Sheet1", fmt.Sprintf("K%v", i+1), phone)
			}
		}
	}
	f.Save()

}

// IsInStringArray 判断数组中是否存在字符串
func IsInStringArray(str string, arr []string) bool {
	// 先对字符串数组进行排序
	sort.Strings(arr)
	// 使用二分查找算法查找字符串
	pos := sort.SearchStrings(arr, str)
	// 如果找到了则返回 true，否则返回 false
	return pos < len(arr) && arr[pos] == str
}

func validateMobileNumber(mobileNumber string) bool {
	// 手机号码正则表达式，这里只是一个简单的示例，可能需要根据您的具体需求进行调整
	re := regexp.MustCompile(`^1[3-9]\d{9}$`)
	return re.MatchString(mobileNumber)
}