jyps/xlsx/xlsx_test.go

package main

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

func TestA(T *testing.T) {
	Mgo := &mongodb.MongodbSim{
		//MongodbAddr: "172.17.189.140:27080",
		MongodbAddr: "127.0.0.1:27017",
		Size:        10,
		DbName:      "wcc",
		//UserName:    "SJZY_RWbid_ES",
		//Password:    "SJZY@B4i4D5e6S",
		//Direct:      true,
	}
	Mgo.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
	}
	cells, _ := f.GetCols("Sheet2")

	for i := 1; i < len(rows); i++ {
		log.Print(rows[i][3])
		if rows[i][2] == "省级" {
			for k, v := range cells[0] {
				if k == 0 || v == "" {
					continue
				}

				insert := make(map[string]interface{})
				insert["top"] = rows[i][0]
				insert["area"] = rows[i][3]
				insert["level"] = rows[i][2]
				insert["sub"] = v
				insert["name"] = rows[i][3] + v
				Mgo.InsertOrUpdate("wcc", "wcc_0313", insert)
			}
		}

		if rows[i][2] == "市级" {
			for k, v := range cells[1] {
				if k == 0 || v == "" {
					continue
				}
				insert := make(map[string]interface{})
				insert["top"] = rows[i][0]
				insert["area"] = rows[i][3]
				insert["city"] = rows[i][4]
				insert["level"] = rows[i][2]
				insert["sub"] = v
				insert["name"] = rows[i][4] + v
				Mgo.InsertOrUpdate("wcc", "wcc_0313", insert)
			}
		}

		if rows[i][2] == "县级" {
			for k, v := range cells[2] {
				if k == 0 || v == "" {
					continue
				}
				insert := make(map[string]interface{})
				insert["top"] = rows[i][0]
				insert["area"] = rows[i][3]
				insert["city"] = rows[i][4]
				insert["district"] = rows[i][5]
				insert["level"] = rows[i][2]
				insert["sub"] = v
				insert["name"] = rows[i][4] + rows[i][5] + v
				Mgo.InsertOrUpdate("wcc", "wcc_0313", insert)
			}
		}
	}

	fmt.Println("over")
}

func TestA2(T *testing.T) {
	Mgo := &mongodb.MongodbSim{
		MongodbAddr: "127.0.0.1:27017",
		Size:        10,
		DbName:      "wcc",
	}
	Mgo.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("Sheet3")
	if err != nil {
		fmt.Println(err)
		return
	}

	for i := 1; i < len(rows); i++ {
		log.Print(rows[i][2])
		insert := map[string]interface{}{
			"company_name": rows[i][2],
			"organizer":    rows[i][1],
		}

		err = Mgo.InsertOrUpdate("wcc", "wcc_henan_gov2", insert)
		if err != nil {
			log.Println("err", err)
		}
	}

	fmt.Println("over")
}

func TestB(T *testing.T) {
	Mgo := &mongodb.MongodbSim{
		//MongodbAddr: "172.17.189.140:27080",
		MongodbAddr: "127.0.0.1:27083",
		Size:        10,
		DbName:      "qfw",
		UserName:    "SJZY_RWbid_ES",
		Password:    "SJZY@B4i4D5e6S",
		Direct:      true,
	}
	Mgo.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++ {
		url := rows[i][2]
		log.Println(rows[i][1])
		id := GetIdByURL(url)
		re, _ := Mgo.FindById("bidding", id, nil)
		(*re)["wcc_id"] = i
		(*re)["toptype_old"] = (*re)["toptype"]
		(*re)["subtype_old"] = (*re)["subtype"]
		delete(*re, "toptype")
		delete(*re, "subtype")
		Mgo.SaveByOriID("wcc_bidding_0518", re)
	}

	log.Println("over")

}

// TestAAA 处理专项债券数据
func TestAAA(t *testing.T) {
	//87 竞品
	Mgo := &mongodb.MongodbSim{
		//MongodbAddr: "172.17.189.140:27080",
		MongodbAddr: "127.0.0.1:27081",
		Size:        10,
		DbName:      "py_theme",
		UserName:    "",
		Password:    "",
		Direct:      true,
	}
	Mgo.InitPool()
	sess := Mgo.GetMgoConn()
	defer Mgo.DestoryMongoConn(sess)

	f, err := excelize.OpenFile("./湖北新增专项债.xlsx")
	if err != nil {
		fmt.Println(err)
		return
	}
	defer func() {
		f.Save()
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()

	rows, err := f.GetRows("项目列表")
	if err != nil {
		fmt.Println(err)
		return
	}

	keys := []string{"projectName", "totalInvestment", "regionName", "cityName", "countyName", "capital", "applyDebt", "portfolioFinancing", "specialDebtAsCapital",
		"expectedReturn", "projectCost", "projectTypeName3", "projectSubject", "startDate", "endDate", "operationStartDate",
		"operationEndDate", "sourceIncome", "constructionContent", "remarks", "portfolioFinancingSource", "costIncomePercent", "coverageMultiple", "implementingAgency",
		"accountingFirm", "lawFirm", "createTime", "updateTime"}
	titles := []string{"项目名称", "总投资（万元）", "省份", "地市", "区县", "不含专项债资本金（万元）", "申请专项债总额（万元）", "其他债务融资（万元）", "专项债作资本金（万元）", "预期收入（万元）", "成本",
		"项目领域", "项目业主", "建设期开始", "建设期结束", "运营期开始", "运营期结束", "收入来源", "建设内容", "特殊情况备注", "其他债务融资来源", "成本/收入（%）", "覆盖倍数", "主管部门", "会计所", "律所", "入库时间", "更新时间"}

	keys2 := []string{"bondName", "bondShortName", "bondNo", "regionName", "bondType1Name", "bondType2Name", "projectType1Name", "totalAmount", "issueDate", "issuePlaceName", "issueTerm", "issueInterestRate", "issuePhase", "payInterestMethodName",
		"newBondAmount", "counterBondAmount", "refinancingBondAmount", "redemptionMethod", "valueDate", "expiryDate", "payInterestDate", "latelyPayInterestDate", "lastPayInterest", "cumulativePayInterest"}

	titles2 := []string{"项目名称", "债券名称", "债券简称", "债券编码", "省份", "债券性质", "债券类型", "官方项目类型", "发行金额（万元）", "发行日期", "发行场所", "发行期限(年)", "发行利率(%)", "发行期数", "付息方式", "新增债券（亿元）", "置换债券（亿元）", "再融资债券（亿元）", "赎回方式",
		"起息日", "到息日", "付息日", "最近付息日", "上期已付息（亿元）", "累计付息（亿元）"}
	line := 0
	line++
	f.NewSheet("项目详情")
	f.NewSheet("债券详情")
	bondLine := 0
	//设置第一行title
	_ = f.SetSheetRow("项目详情", fmt.Sprintf("%s%d", "A", line), &titles)
	_ = f.SetSheetRow("债券详情", fmt.Sprintf("%s%d", "A", line), &titles2)
	//
	for i := 1; i < len(rows); i++ {
		projectName := rows[i][0]
		//projectName := "新建南通至宁波高速铁路（海盐段）"
		log.Println(projectName)
		line++
		where := map[string]interface{}{
			"project.jcxx.projectName": projectName,
		}
		detail, _ := Mgo.FindOne("specialbond_detail_202407", where)
		if len(*detail) == 0 {
			continue
		}
		insert := make([]interface{}, 0)
		if project, ok := (*detail)["project"].(map[string]interface{}); ok {
			if jcxx, ok := project["jcxx"].(map[string]interface{}); ok {
				for _, v := range keys {
					insert = append(insert, jcxx[v])
				}
			}
		}

		err := f.SetSheetRow("项目详情", fmt.Sprintf("%s%d", "A", line), &insert)
		if err != nil {
			log.Println(err)
			return
		}

		// 债券基本信息
		if bonds, ok := (*detail)["bond"].([]interface{}); ok {
			for _, bond := range bonds {
				if bondData, ok := bond.(map[string]interface{}); ok {
					if jcxx, ok := bondData["jbxx"].(map[string]interface{}); ok {
						//if strings.Contains(util.ObjToString(jcxx["bondName"]), "2024") {
						bondInsert := make([]interface{}, 0)
						bondInsert = append(bondInsert, projectName)
						for _, v := range keys2 {
							bondInsert = append(bondInsert, jcxx[v])
						}
						bondLine++
						f.SetSheetRow("债券详情", fmt.Sprintf("%s%d", "A", line), &bondInsert)
						//}
					}
				}
			}
		}
	}

	//query := sess.DB("py_theme").C("specialbond_detail").Find(nil).Select(nil).Iter()
	//count := 0
	//for tmp := make(map[string]interface{}); query.Next(tmp); count++ {
	//	if count%10000 == 0 {
	//		log.Println("current:", count)
	//	}
	//
	//	id := mongodb.BsonIdToSId(tmp["_id"])
	//	log.Println(id)
	//}

}

func TestGetIdByURL(t *testing.T) {
	url := "https://www.jianyu360.cn/article/content/AHnY1xBfikoAjYsNGd4cE8JIzAvFj1jcXNlKDhROC4eWlFwA15UCZQ%3D.html"
	fmt.Println(GetIdByURL(url))
}

// TestArea 中国行政区划数据-民政部
func TestArea(t *testing.T) {
	Mgo := &mongodb.MongodbSim{
		//MongodbAddr: "172.17.189.140:27080",
		MongodbAddr: "127.0.0.1:27083",
		Size:        10,
		DbName:      "qfw",
		UserName:    "SJZY_RWbid_ES",
		Password:    "SJZY@B4i4D5e6S",
		Direct:      true,
	}
	Mgo.InitPool()

	f, err := excelize.OpenFile("./20240530_民政部统计数据.xlsx")
	if err != nil {
		fmt.Println(err)
		return
	}
	defer func() {
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()

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

	for i := 1; i < len(rows); i++ {
		data := map[string]interface{}{}
		if rows[i][0] == "省份" {
			data["level"] = 1
		} else if rows[i][0] == "城市" {
			data["level"] = 2
		} else {
			data["level"] = 3
		}
		log.Println(rows[i][1])
		parts := strings.Split(rows[i][1], "（")
		data["province"] = parts[0]
		data["province_code"] = rows[i][2]
		if len(rows[i]) > 3 {
			data["city"] = rows[i][3]
		}
		if len(rows[i]) > 4 {
			data["city_code"] = rows[i][4]
		}
		if len(rows[i]) > 5 {
			data["area"] = rows[i][5]
		}
		if len(rows[i]) > 6 {
			data["area_code"] = rows[i][6]
		}

		Mgo.Save("wcc_area_minzhengbu", data)
	}
}

// TestArea2 中国行政区划数据-统计局
func TestArea2(t *testing.T) {
	Mgo := &mongodb.MongodbSim{
		//MongodbAddr: "172.17.189.140:27080",
		MongodbAddr: "127.0.0.1:27083",
		Size:        10,
		DbName:      "qfw",
		UserName:    "SJZY_RWbid_ES",
		Password:    "SJZY@B4i4D5e6S",
		Direct:      true,
	}
	Mgo.InitPool()

	f, err := excelize.OpenFile("./20240530_统计局统计数据.xlsx")
	if err != nil {
		fmt.Println(err)
		return
	}
	defer func() {
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()

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

	for i := 1; i < len(rows); i++ {
		data := map[string]interface{}{}
		data["level"] = rows[i][0]
		log.Println(rows[i][1], rows[3])
		data["province"] = rows[i][1]
		data["province_code"] = rows[i][2]
		if len(rows[i]) > 3 {
			data["city"] = rows[i][3]
		}
		if len(rows[i]) > 4 {
			data["city_code"] = rows[i][4]
		}
		if len(rows[i]) > 5 {
			data["area"] = rows[i][5]
		}
		if len(rows[i]) > 6 {
			data["area_code"] = rows[i][6]
		}

		Mgo.Save("wcc_area_tongjiju", data)
	}
}

func TestHN(t *testing.T) {
	Mgo := &mongodb.MongodbSim{
		//MongodbAddr: "172.17.189.140:27080",
		MongodbAddr: "127.0.0.1:27083",
		Size:        10,
		DbName:      "qfw",
		UserName:    "SJZY_RWbid_ES",
		Password:    "SJZY@B4i4D5e6S",
		Direct:      true,
	}
	Mgo.InitPool()

	//181
	Mgo2 := &mongodb.MongodbSim{
		//MongodbAddr: "172.17.4.181:27001",
		MongodbAddr: "127.0.0.1:27001",
		DbName:      "mixdata",
		Size:        10,
		UserName:    "",
		Password:    "",
		Direct:      true,
	}
	Mgo2.InitPool()

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

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

	for i := 1; i < len(rows); i++ {
		name := rows[i][0]
		log.Println(i, "----", name)
		//res, _ := Mgo2.FindOne("special_enterprise", map[string]interface{}{"company_name": name})
		//if res != nil && len(*res) > 0 {
		//	f.SetCellValue(sheet, fmt.Sprintf("%s%d", "E", i+1), "special_enterprise")
		//	f.SetCellValue(sheet, fmt.Sprintf("%s%d", "F", i+1), (*res)["company_status"])
		//	f.SetCellValue(sheet, fmt.Sprintf("%s%d", "G", i+1), (*res)["company_type"])
		//} else {
		//	ra, _ := Mgo2.FindOne("special_gov_unit", map[string]interface{}{"company_name": name})
		//	if ra != nil && len(*ra) > 0 {
		//		f.SetCellValue(sheet, fmt.Sprintf("%s%d", "E", i+1), "special_gov_unit")
		//		f.SetCellValue(sheet, fmt.Sprintf("%s%d", "G", i+1), (*ra)["company_type"])
		//		if util.ObjToString((*ra)["company_status"]) != "" {
		//			f.SetCellValue(sheet, fmt.Sprintf("%s%d", "F", i+1), (*ra)["company_status"])
		//		} else {
		//			if util.IntAll((*ra)["use_flag"]) < 5 {
		//				f.SetCellValue(sheet, fmt.Sprintf("%s%d", "F", i+1), "正常")
		//			} else {
		//				f.SetCellValue(sheet, fmt.Sprintf("%s%d", "F", i+1), "废弃")
		//			}
		//		}
		//	}
		//}

		//
		data := map[string]interface{}{
			"detail": name,
		}

		res := getAreaInfo(data)
		if res != nil {
			city := res["city"]
			district := res["district"]
			f.SetCellValue("单位", fmt.Sprintf("%s%d", "B", i+1), city)
			f.SetCellValue("单位", fmt.Sprintf("%s%d", "C", i+1), district)
		}
	}

	f.Save()
	log.Println("结束")
}