jyps/project_chuan/xlsx.go

package main

import (
	"fmt"
	"github.com/xuri/excelize/v2"
	util "jygit.jydev.jianyu360.cn/data_processing/common_utils"
	"jygit.jydev.jianyu360.cn/data_processing/common_utils/mongodb"
	"log"
	"time"
)

// dealXlsxData 处理xlsx 给的样例数据，匹配在建项目数据，然后存储MongoDB
func dealXlsxData() {
	//url := "http://172.17.4.184:19908"
	////url := "http://127.0.0.1:19908"
	//username := "jybid"
	//password := "Top2023_JEB01i@31"
	////index := "bidding" //索引名称
	//// 创建 Elasticsearch 客户端
	//client, err := elastic.NewClient(
	//	elastic.SetURL(url),
	//	elastic.SetBasicAuth(username, password),
	//	elastic.SetSniff(false),
	//)
	//if err != nil {
	//	log.Fatalf("创建 Elasticsearch 客户端失败：%s", err)
	//}

	// 2. 读取 Excel
	f, err := excelize.OpenFile("投资入库项目_202504.xlsx")
	if err != nil {
		log.Fatal("❌ 无法打开 Excel 文件:", err)
	}
	defer func() {
		f.Save()
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()

	// 3. 读取 B 列（项目名称）
	rows, err := f.GetRows("Sheet1") // 替换为正确的 Sheet 名称
	if err != nil {
		log.Fatal("❌ 无法读取 Excel:", err)
	}

	log.Println("开始处理数据--------")
	for i := 1; i < len(rows); i++ {
		p1_project_code := rows[i][0]
		p1_project_name := rows[i][1]
		p1_project_owner := rows[i][2]
		p1_project_owner_code := rows[i][3]
		p1_project_owner_area := rows[i][4]
		p1_project_total := rows[i][5]
		//
		log.Println("项目名称--------", p1_project_name)
		insert := map[string]interface{}{
			"p1_project_code":       p1_project_code,
			"p1_project_name":       p1_project_name,
			"p1_project_owner":      p1_project_owner,
			"p1_project_owner_code": p1_project_owner_code,
			"p1_project_owner_area": p1_project_owner_area,
			"p1_project_total":      p1_project_total,
		}
		start := 8
		row := rows[i]
		p1_biddings := make([]map[string]interface{}, 0)
		p1_bidding_ids := make([]string, 0)
		biddingIdMap := make(map[string]map[string]interface{}, 0)
		//projectIdMap := make(map[string]map[string]interface{}, 0) //project_id -> project map
		for i := start; i+3 < len(row); i += 4 {
			href := row[i+2]
			score := row[i+3]
			bid := GetIdByURL(href)
			p1_bidding_ids = append(p1_bidding_ids, bid)
			bidding, _ := MgoB.FindById("bidding", bid, nil)
			da := map[string]interface{}{
				"id":          bid,
				"title":       (*bidding)["title"],
				"area":        (*bidding)["area"],
				"city":        (*bidding)["city"],
				"projectname": (*bidding)["projectname"],
				"score":       score,
				"toptype":     (*bidding)["toptype"],
				"subtype":     (*bidding)["subtype"],
				"buyer":       (*bidding)["buyer"],
				"budget":      (*bidding)["budget"],
				"buyerperson": (*bidding)["buyerperson"],
				"buyertel":    (*bidding)["buyertel"],
				"s_winner":    (*bidding)["s_winner"],
				"bidamount":   (*bidding)["bidamount"],
				"winnertel":   (*bidding)["winnertel"],
				"agency":      (*bidding)["agency"],
				"publishtime": (*bidding)["publishtime"],
			}
			biddingIdMap[bid] = da
			//log.Println("aa", insert, amount, title, href, score)
			p1_biddings = append(p1_biddings, da)
		}

		//insert["biddings"] = p1_biddings
		p1_bidding_ids = removeDuplicates(p1_bidding_ids)

		for _, vv := range p1_bidding_ids {
			where2 := map[string]interface{}{
				"ids": vv,
			}
			projectset, _ := MgoP.FindOne("projectset_20230904", where2)
			if projectset != nil && len(*projectset) > 0 {
				data := map[string]interface{}{
					"project_id":  mongodb.BsonIdToSId((*projectset)["_id"]),
					"projectname": util.ObjToString((*projectset)["projectname"]),
					"bidamount":   (*projectset)["bidamount"],
					"sortprice":   (*projectset)["sortprice"],
					"area":        (*projectset)["area"],
					"city":        (*projectset)["city"],
					"district":    (*projectset)["district"],
					"bidstatus":   (*projectset)["bidstatus"],
					"buyer":       (*projectset)["buyer"],
					"firsttime":   (*projectset)["firsttime"],
					"bidtype":     (*projectset)["bidtype"],
				}

				biddData := biddingIdMap[vv]
				biddData["project"] = data
				biddingIdMap[vv] = biddData
			}
		}

		var insertBidding = make([]map[string]interface{}, 0)
		for _, v := range biddingIdMap {
			insertBidding = append(insertBidding, v)
		}

		insert["bidding"] = insertBidding
		MgoP.InsertOrUpdate("qfw", "wcc_dealXlsxData_0524", insert)
	}

	log.Println("数据处理完毕--------")
}

// exportTestData 导出拓扑统计局 样例数据
func exportTestData() {
	// 打开已有模板
	f, err := excelize.OpenFile("统计局入库项目数据目录树.xlsx")
	if err != nil {
		log.Fatal("打开模板失败:", err)
	}

	sheet := f.GetSheetName(0) // 使用第一个 Sheet，也可以改成具体 Sheet 名
	startRow := 2              // 假设第 1 行是表头，数据从第 2 行开始

	rowIndex := startRow

	sess := MgoP.GetMgoConn()
	defer MgoP.DestoryMongoConn(sess)

	queryMgo := sess.DB("qfw").C("wcc_dealXlsxData_0524").Find(nil).Select(nil).Iter()
	count := 0

	for tmp := make(map[string]interface{}); queryMgo.Next(tmp); count++ {
		if count%1000 == 0 {
			log.Println("current", count)
		}
		//
		projectname := util.ObjToString(tmp["p1_project_name"])
		where := map[string]interface{}{
			"projectname": projectname,
		}
		pro_project, _ := MgoP.FindOne("projectset_proposed", where)
		ppid := ""
		if pro_project != nil {
			ppid = mongodb.BsonIdToSId((*pro_project)["_id"])
		}
		//1.拟建原始数据
		v1 := []interface{}{
			ppid,
			tmp["p1_project_code"],
			tmp["p1_project_name"],
			tmp["p1_project_owner"],
			tmp["p1_project_owner_code"],
			tmp["p1_project_owner_area"],
		}

		// 遍历招采信息
		if biddingList, ok := tmp["bidding"].([]interface{}); ok {
			for _, bidItem := range biddingList {
				bid := bidItem.(map[string]interface{})
				project := bid["project"].(map[string]interface{})

				// 在建招采项目信息
				v2 := []interface{}{
					project["project_id"],
					project["projectname"],
					project["bidamount"],
					project["area"],
					project["city"],
					project["bidstatus"],
					project["buyer"],
					time.Unix(util.Int64All(project["firsttime"]), 0).In(time.FixedZone("CST", 8*3600)).Format("2006-01-02 15:04:05"),
					bid["score"],
				}

				// 标讯信息
				v3 := []interface{}{
					bid["id"],
					bid["title"],
					bid["subtype"],
					bid["area"],
					bid["city"],
					bid["buyer"],
					bid["budget"],
					bid["buyerperson"],
					bid["buyertel"],
					bid["s_winner"],
					bid["bidamount"],
					bid["winnertel"],
					bid["agency"],
					time.Unix(util.Int64All(bid["publishtime"]), 0).In(time.FixedZone("CST", 8*3600)).Format("2006-01-02 15:04:05"),
					//bid["publishtime"],
					bid["score"],
				}

				// 拼接成一行
				fullRow := append(append(v1, v2...), v3...)

				cell, _ := excelize.CoordinatesToCellName(1, rowIndex)
				f.SetSheetRow(sheet, cell, &fullRow)
				rowIndex++
				// 写入表格，如写入 Excel
				//excelWriter.WriteRow(fullRow) // 你需要实现 WriteRow 方法
			}

		}
	}
	// 保存为另一个文件，防止覆盖模板
	outputFile := "统计局入库项目数据导出.xlsx"
	if err := f.SaveAs(outputFile); err != nil {
		log.Fatal("保存 Excel 失败:", err)
	}
	log.Println("✅ 数据写入完成，共写入行数:", rowIndex-startRow)
}

// exportTestData1 导出统计局 拟建数据
func exportTestData1() {
	// 1. 连接 MongoDB
	sess := MgoP.GetMgoConn()
	defer MgoP.DestoryMongoConn(sess)

	// 2. 打开 Excel 模板
	f, err := excelize.OpenFile("统计局入库项目数据目录树.xlsx")
	if err != nil {
		log.Fatalf("无法打开Excel模板: %v", err)
	}
	sheet := f.GetSheetName(0)
	rowIndex := 3 // 假设第一行是表头

	where := map[string]interface{}{
		"city": "陇南市",
	}
	// 3. 查询 Mongo 数据
	queryMgo := sess.DB("qfw").C("wcc_dealProposed22_0524").Find(where).Iter()
	count := 0

	for tmp := make(map[string]interface{}); queryMgo.Next(tmp); count++ {
		if count%1000 == 0 {
			log.Println("current", count)
		}

		//projectname := util.ObjToString(tmp["p1_project_name"])
		//where := map[string]interface{}{"projectname": projectname}
		//pro_project, _ := MgoP.FindOne("projectset_proposed", where)

		//ppid := ""
		//if pro_project != nil {
		//	ppid = mongodb.BsonIdToSId((*pro_project)["_id"])
		//}

		v1 := []interface{}{
			//ppid,
			tmp["proposed_id"],
			tmp["approvecode"],
			tmp["projectname"],
			tmp["buyer"],
			tmp["credit_no"],
			fmt.Sprintf("%s-%s-%s", tmp["area"], tmp["city"], tmp["district"]),
		}

		// ----------- 核心容错处理逻辑 ------------
		if biddingListRaw, ok := tmp["bidding"]; ok {
			biddingList, ok := biddingListRaw.([]interface{})
			if !ok || len(biddingList) == 0 {
				writeEmptyRow(f, sheet, rowIndex, v1)
				rowIndex++
			} else {
				for _, bidItem := range biddingList {
					bidMap, ok := bidItem.(map[string]interface{})
					if !ok {
						continue
					}

					if util.ObjToString(bidMap["toptype"]) == "拟建" {
						continue
					}
					// v2
					v2 := make([]interface{}, 9)
					for i := range v2 {
						v2[i] = ""
					}

					if projRaw, ok := bidMap["project"]; ok {
						if project, ok := projRaw.(map[string]interface{}); ok {
							firstTimeStr := ""
							if val, ok := project["firsttime"]; ok {
								if t := util.Int64All(val); t > 0 {
									firstTimeStr = time.Unix(t, 0).In(time.FixedZone("CST", 8*3600)).Format("2006-01-02 15:04:05")
								}
							}
							v2 = []interface{}{
								project["project_id"],
								project["projectname"],
								project["bidamount"],
								project["area"],
								project["city"],
								project["bidstatus"],
								project["buyer"],
								firstTimeStr,
								bidMap["score"],
							}
						}
					}

					publishTimeStr := ""
					if val, ok := bidMap["publishtime"]; ok {
						if t := util.Int64All(val); t > 0 {
							publishTimeStr = time.Unix(t, 0).In(time.FixedZone("CST", 8*3600)).Format("2006-01-02 15:04:05")
						}
					}
					v3 := []interface{}{
						bidMap["id"],
						bidMap["title"],
						bidMap["subtype"],
						bidMap["area"],
						bidMap["city"],
						bidMap["buyer"],
						bidMap["budget"],
						bidMap["buyerperson"],
						bidMap["buyertel"],
						bidMap["s_winner"],
						bidMap["bidamount"],
						bidMap["winnertel"],
						bidMap["agency"],
						publishTimeStr,
						bidMap["score"],
					}

					fullRow := append(append(v1, v2...), v3...)
					cell, _ := excelize.CoordinatesToCellName(1, rowIndex)
					f.SetSheetRow(sheet, cell, &fullRow)
					rowIndex++
				}
			}
		} else {
			// bidding 字段不存在
			writeEmptyRow(f, sheet, rowIndex, v1)
			rowIndex++
		}
	}

	// 4. 保存结果
	if err := f.SaveAs("输出结果.xlsx"); err != nil {
		log.Fatalf("保存文件失败: %v", err)
	}

	log.Println("导出完成，保存为 输出结果.xlsx")
}

// exportTestData2 导出 陇南市 拟建数据
func exportTestData2() {
	// 1. 连接 MongoDB
	sess := MgoP.GetMgoConn()
	defer MgoP.DestoryMongoConn(sess)

	// 2. 打开 Excel 模板
	f, err := excelize.OpenFile("统计局入库项目数据目录树.xlsx")
	if err != nil {
		log.Fatalf("无法打开Excel模板: %v", err)
	}
	sheet := f.GetSheetName(0)
	rowIndex := 3 // 假设第一行是表头

	where := map[string]interface{}{
		"city": "陇南市",
	}
	// 3. 查询 Mongo 数据
	queryMgo := sess.DB("qfw").C("wcc_dealProposed22_0524").Find(where).Iter()
	count := 0

	for tmp := make(map[string]interface{}); queryMgo.Next(tmp); count++ {
		if count%1000 == 0 {
			log.Println("current", count)
		}

		//projectname := util.ObjToString(tmp["p1_project_name"])
		//where := map[string]interface{}{"projectname": projectname}
		//pro_project, _ := MgoP.FindOne("projectset_proposed", where)

		//ppid := ""
		//if pro_project != nil {
		//	ppid = mongodb.BsonIdToSId((*pro_project)["_id"])
		//}

		v1 := []interface{}{
			//ppid,
			tmp["proposed_id"],
			tmp["approvecode"],
			tmp["projectname"],
			tmp["buyer"],
			tmp["credit_no"],
		}
		//fmt.Sprintf("%s-%s-%s", tmp["area"], tmp["city"], tmp["district"]),
		acd := ""
		if util.ObjToString(tmp["area"]) != "" {
			acd = util.ObjToString(tmp["area"])
		}
		if util.ObjToString(tmp["city"]) != "" {
			acd = acd + "-" + util.ObjToString(tmp["city"])
		}
		if util.ObjToString(tmp["district"]) != "" {
			acd = acd + "-" + util.ObjToString(tmp["district"])
		}
		v1 = append(v1, acd)

		// ----------- 核心容错处理逻辑 ------------
		if biddingListRaw, ok := tmp["bidding"]; ok {
			biddingList, ok := biddingListRaw.([]interface{})
			if !ok || len(biddingList) == 0 {
				writeEmptyRow(f, sheet, rowIndex, v1)
				rowIndex++
			} else {
				for _, bidItem := range biddingList {
					bidMap, ok := bidItem.(map[string]interface{})
					if !ok {
						continue
					}

					if util.ObjToString(bidMap["toptype"]) == "拟建" {
						continue
					}
					// v2
					v2 := make([]interface{}, 9)
					for i := range v2 {
						v2[i] = ""
					}

					if projRaw, ok := bidMap["project"]; ok {
						if project, ok := projRaw.(map[string]interface{}); ok {
							firstTimeStr := ""
							if val, ok := project["firsttime"]; ok {
								if t := util.Int64All(val); t > 0 {
									firstTimeStr = time.Unix(t, 0).In(time.FixedZone("CST", 8*3600)).Format("2006-01-02 15:04:05")
								}
							}
							v2 = []interface{}{
								project["project_id"],
								project["projectname"],
								project["bidamount"],
								project["area"],
								project["city"],
								project["bidstatus"],
								project["buyer"],
								firstTimeStr,
								bidMap["score"],
							}
						}
					}

					publishTimeStr := ""
					if val, ok := bidMap["publishtime"]; ok {
						if t := util.Int64All(val); t > 0 {
							publishTimeStr = time.Unix(t, 0).In(time.FixedZone("CST", 8*3600)).Format("2006-01-02 15:04:05")
						}
					}
					v3 := []interface{}{
						bidMap["id"],
						bidMap["title"],
						bidMap["subtype"],
						bidMap["area"],
						bidMap["city"],
						bidMap["buyer"],
						bidMap["budget"],
						bidMap["buyerperson"],
						bidMap["buyertel"],
						bidMap["s_winner"],
						bidMap["bidamount"],
						bidMap["winnertel"],
						bidMap["agency"],
						publishTimeStr,
						bidMap["score"],
					}

					fullRow := append(append(v1, v2...), v3...)
					cell, _ := excelize.CoordinatesToCellName(1, rowIndex)
					f.SetSheetRow(sheet, cell, &fullRow)
					rowIndex++
				}
			}
		} else {
			// bidding 字段不存在
			writeEmptyRow(f, sheet, rowIndex, v1)
			rowIndex++
		}
	}

	// 4. 保存结果
	if err := f.SaveAs("输出结果.xlsx"); err != nil {
		log.Fatalf("保存文件失败: %v", err)
	}

	log.Println("导出完成，保存为 输出结果.xlsx")
}

func writeEmptyRow(f *excelize.File, sheet string, rowIndex int, v1 []interface{}) {
	emptyV2 := make([]interface{}, 9)
	emptyV3 := make([]interface{}, 15)
	for i := range emptyV2 {
		emptyV2[i] = ""
	}
	for i := range emptyV3 {
		emptyV3[i] = ""
	}
	fullRow := append(append(v1, emptyV2...), emptyV3...)
	cell, _ := excelize.CoordinatesToCellName(1, rowIndex)
	f.SetSheetRow(sheet, cell, &fullRow)
}

// 测试，更新approvecode 字段
func ddd() {
	sess := MgoP.GetMgoConn()
	defer MgoP.DestoryMongoConn(sess)

	// 3. 查询 Mongo 数据
	queryMgo := sess.DB("qfw").C("wcc_dealProposed22_0524").Find(nil).Iter()
	count := 0

	for tmp := make(map[string]interface{}); queryMgo.Next(tmp); count++ {
		if count%1000 == 0 {
			log.Println("current", count)
		}

		pro_id := util.ObjToString(tmp["proposed_id"])
		pro, _ := MgoP.FindById("projectset_proposed", pro_id, nil)
		if util.ObjToString((*pro)["approvecode"]) != "" {
			update := map[string]interface{}{
				"approvecode": (*pro)["approvecode"],
			}

			id := mongodb.BsonIdToSId(tmp["_id"])
			MgoP.UpdateById("wcc_dealProposed22_0524", id, map[string]interface{}{"$set": update})
		}
	}
}