jyps/xlsx/qyxy.go

package main

import (
	"context"
	"encoding/json"
	"fmt"
	"github.com/olivere/elastic/v7"
	"github.com/xuri/excelize/v2"
	"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"
	"strings"
)

// exportQy  导出企业相关信息，匹配表格中的，企业所在地、企业联系人、职位、联系方式、2023年的中标项目数以及总金额
func exportQy() {
	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("./2023年数字中国参展商名录.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"`
	}

	//
	url2 := "http://127.0.0.1:19905"
	username2 := "jybid"
	password2 := "Top2023_JEB01i@31"
	// 创建 Elasticsearch 客户端
	client2, err := elastic.NewClient(
		elastic.SetURL(url2),
		elastic.SetBasicAuth(username2, password2),
		elastic.SetSniff(false),
	)
	if err != nil {
		log.Fatalf("创建 Elasticsearch 客户端失败：%s", err)
	}

	subtitles := []string{"企业名称", "企业省份", "企业城市", "标讯-联系方式", "凭安-联系方式", "2023中标项目数", "2023中标总金额"}

	results := make([][]interface{}, 0)
	for i := 1; i < len(rows); i++ {
		name := rows[i][0]
		//1.标讯抽取的联系方式
		contactsMap := make([]string, 0) //联系人信息
		baseinfo := BaseInfo{}
		log.Println(name)
		db.Table("dws_f_ent_baseinfo").Select("name", "name_id").Where("name = ? ", name).Order("publishtime desc").Limit(10).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)
						}
					}
				}
			}
		}
		//2.凭安联系方式
		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"]))
							}
						}
					}
				}
			}
		}

		//查询企业中标数据
		count, amount := getAmountData(client2, "bidding", name)
		results = append(results, []interface{}{name, (*stds)["company_area"], (*stds)["company_city"], strings.Join(contactsMap, ","), strings.Join(reportsMap, ","), count, amount})
	}

	sheet := "联系方式"
	f.NewSheet(sheet)
	line := 0
	line++
	//设置第一行title
	_ = f.SetSheetRow(sheet, fmt.Sprintf("%s%d", "A", line), &subtitles)

	for k, _ := range results {
		line++
		err = f.SetSheetRow(sheet, fmt.Sprintf("%s%d", "A", line), &results[k])
		if err != nil {
			log.Println(err)
		}
	}
	f.Save()
}

// getAmountData  根据企业名称，获取2023年中标数量，以及中标总金额
func getAmountData(client *elastic.Client, index string, companyName string) (int64, float32) {
	query := elastic.NewBoolQuery().
		Must(elastic.NewTermQuery("s_winner", companyName)).
		Must(elastic.NewRangeQuery("publishtime").Gte(1672502400).Lt(1704038400)) //2023-01-01 -- 2024-01-01
	//ctx := context.Background()

	//
	searchResult, err := client.Search().Size(10000).
		Index(index).
		Query(query).
		Do(context.Background())
	if err != nil {
		log.Fatalf("Error executing search: %s", err)
	}

	totalAmount := float32(0)
	if len(searchResult.Hits.Hits) > 0 {
		for _, hit := range searchResult.Hits.Hits {
			var doc map[string]interface{}
			err := json.Unmarshal(hit.Source, &doc)
			if err != nil {
				log.Printf("解析文档失败：%s", err)
				continue
			}

			bidamount := float32(util.Float64All(doc["bidamount"]))
			totalAmount += bidamount
		}

	}
	return searchResult.TotalHits(), totalAmount
	//////////

	//开始滚动搜索
	//scrollID := ""
	//scroll := "10m"
	//searchSource := elastic.NewSearchSource().
	//	Query(query).
	//	Size(10000).
	//	Sort("_doc", true) //升序排序
	////Sort("_doc", false) //降序排序
	//
	//searchService := client.Scroll(index).
	//	Size(10000).
	//	Scroll(scroll).
	//	SearchSource(searchSource)
	//
	//res, err := searchService.Do(ctx)
	//if err != nil {
	//	if err == io.EOF {
	//		fmt.Println("没有数据")
	//	} else {
	//		panic(err)
	//	}
	//
	//}
	////defer client.ClearScroll().ScrollId(scrollID).Do(ctx) // 在退出时清理资源
	//fmt.Println(companyName, "总数是：", res.TotalHits())
	//total := 0
	//totalAmount = float32(0)
	//for len(res.Hits.Hits) > 0 {
	//	for _, hit := range res.Hits.Hits {
	//		var doc map[string]interface{}
	//		err := json.Unmarshal(hit.Source, &doc)
	//		if err != nil {
	//			log.Printf("解析文档失败：%s", err)
	//			continue
	//		}
	//
	//		bidamount := float32(util.Float64All(doc["bidamount"]))
	//		totalAmount += bidamount
	//	}
	//
	//	total = total + len(res.Hits.Hits)
	//	scrollID = res.ScrollId
	//	res, err = client.Scroll().ScrollId(scrollID).Scroll(scroll).Do(ctx)
	//	log.Println("current count:", total)
	//	if err != nil {
	//		if err == io.EOF {
	//			// 滚动到最后一批数据，退出循环
	//			break
	//		}
	//		log.Println("滚动搜索失败：", err, res)
	//		break // 处理错误时退出循环
	//	}
	//}
	//// 在循环外调用 ClearScroll
	//_, err = client.ClearScroll().ScrollId(scrollID).Do(ctx)
	//if err != nil {
	//	log.Printf("清理滚动搜索失败：%s", err)
	//}
	//
	//fmt.Println(companyName, "结束~~~~~~~~~~~~~~~")
	//
	//return res.TotalHits(), totalAmount
}