jyps/xlsx/exportBidding.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"
	"os"
	"strconv"
	"strings"
	"time"
)

func exportBidding2() {
	//Mgo := &mongodb.MongodbSim{
	//	MongodbAddr: "192.168.3.206:27002",
	//	DbName:      "qfw_data",
	//	Size:        10,
	//	UserName:    "root",
	//	Password:    "root",
	//	Direct: true,
	//}
	//Mgo.InitPool()

	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()

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

	//start, _ := time.Parse("2006-01-02 15:04:05", "2023-09-06 21:00:00")
	//end, _ := time.Parse("2006-01-02 15:04:05", "2023-09-07 09:00:00")
	//////
	//q := map[string]interface{}{
	//	"publish": map[string]interface{}{
	//		"$gte": 1701360000,
	//		"$lte": 1706716800,
	//	},
	//	//"modifyinfo.toptype": map[string]interface{}{
	//	//	"$exists": 1,
	//	//},
	//	"toptype": "拟建",
	//	//"title": map[string]interface{}{
	//	//	"$regex": "充电",
	//	//},
	//}

	query := sess.DB("qfw").C("wcc_20250312_bidding").Find(nil).Select(nil).Iter()

	count := 0
	file := time.Now().Format("20060102") + "导出数据.xlsx"
	currentPwd, _ := os.Getwd()
	exportFile := fmt.Sprintf("%s/%s", currentPwd, file)
	xlsx := excelize.NewFile(excelize.Options{ShortDatePattern: "yyyy/m/dd"})
	styleOne, _ := xlsx.NewStyle(
		&excelize.Style{
			Alignment: &excelize.Alignment{
				Horizontal: "left",
				Vertical:   "left",
			},
		},
	)
	line := 0
	sheet := "Sheet1"
	//xlsx.NewSheet(sheet)
	//xlsx.DeleteSheet("Sheet1")
	//_ = xlsx.SetColWidth(sheet, "A", "A", 20)
	//_ = xlsx.SetColWidth(sheet, "B", "B", 25)
	//_ = xlsx.SetColWidth(sheet, "C", "C", 30)
	//_ = xlsx.SetColWidth(sheet, "D", "D", 35)
	//_ = xlsx.SetColWidth(sheet, "E", "E", 45)
	//_ = xlsx.SetColWidth(sheet, "F", "F", 20)

	subtitles := []string{"title", "area", "buyeraddr", "channel", "projectname", "site", "spidercode", "toptype", "subtype", "detail", "district", "publishtime", "s_topscopeclass", "s_subscopeclass", "jyhref", "agencyaddr", "projectperiod", "project_duration", "bidendtime", "bidendtime", "city", "budget", "buyerclass", "winner", "agencytel", "bidamount", "bidamount", "project_timeunit", "purchasing_tag", "projectcode"}
	//subtitles := []string{"bidding_id", "title", "detail", "href", "jyhref", "toptype", "new_toptype", "new_subtype"}
	line++
	//设置第一行title
	_ = xlsx.SetSheetRow(sheet, fmt.Sprintf("%s%d", "A", line), &subtitles)
	for tmp := make(map[string]interface{}); query.Next(tmp); count++ {
		if count%1000 == 0 {
			log.Println("current --- ", count)
		}
		line++

		val := []interface{}{}
		for _, v := range subtitles {
			if v == "publishtime" {
				timeObj := time.Unix(util.Int64All(tmp[v]), 0)
				formattedTime := timeObj.Format("2006-01-02 15:04:05")
				val = append(val, formattedTime)
			} else {
				val = append(val, tmp[v])
			}

		}
		err := xlsx.SetSheetRow(sheet, fmt.Sprintf("%s%d", "A", line), &val)
		if err != nil {
			log.Println(err)
			return
		}
		_ = xlsx.SetCellStyle(sheet, fmt.Sprintf("%s%d", "A", line), "BA"+strconv.Itoa(line), styleOne)

		tmp = make(map[string]interface{})

	}
	xlsx.Path = exportFile
	xlsx.Save()

	log.Println("dealTmp over ", count)

}

// exportBidding 导出bidding 数据 excel
func exportBidding() {
	//Mgo := &mongodb.MongodbSim{
	//	MongodbAddr: "192.168.3.206:27002",
	//	DbName:      "qfw_data",
	//	Size:        10,
	//	UserName:    "root",
	//	Password:    "root",
	//	Direct: true,
	//}
	//Mgo.InitPool()

	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()

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

	//start, _ := time.Parse("2006-01-02 15:04:05", "2023-09-06 21:00:00")
	//end, _ := time.Parse("2006-01-02 15:04:05", "2023-09-07 09:00:00")
	//////
	q := map[string]interface{}{
		"publish": map[string]interface{}{
			"$gte": 1701360000,
			"$lte": 1706716800,
		},
		//"modifyinfo.toptype": map[string]interface{}{
		//	"$exists": 1,
		//},
		"toptype": "拟建",
		//"title": map[string]interface{}{
		//	"$regex": "充电",
		//},
	}

	query := sess.DB("qfw").C("bidding").Find(q).Select(nil).Iter()

	count := 0
	file := time.Now().Format("20060102") + "导出数据.xlsx"
	currentPwd, _ := os.Getwd()
	exportFile := fmt.Sprintf("%s/%s", currentPwd, file)
	xlsx := excelize.NewFile(excelize.Options{ShortDatePattern: "yyyy/m/dd"})
	styleOne, _ := xlsx.NewStyle(
		&excelize.Style{
			Alignment: &excelize.Alignment{
				Horizontal: "left",
				Vertical:   "left",
			},
		},
	)
	line := 0
	sheet := "Sheet1"
	//xlsx.NewSheet(sheet)
	//xlsx.DeleteSheet("Sheet1")
	//_ = xlsx.SetColWidth(sheet, "A", "A", 20)
	//_ = xlsx.SetColWidth(sheet, "B", "B", 25)
	//_ = xlsx.SetColWidth(sheet, "C", "C", 30)
	//_ = xlsx.SetColWidth(sheet, "D", "D", 35)
	//_ = xlsx.SetColWidth(sheet, "E", "E", 45)
	//_ = xlsx.SetColWidth(sheet, "F", "F", 20)

	subtitles := []string{"title", "projectname", "href", "jyhref", "toptype", "subtype"}
	//subtitles := []string{"bidding_id", "title", "detail", "href", "jyhref", "toptype", "new_toptype", "new_subtype"}
	line++
	//设置第一行title
	_ = xlsx.SetSheetRow(sheet, fmt.Sprintf("%s%d", "A", line), &subtitles)
	for tmp := make(map[string]interface{}); query.Next(tmp); count++ {
		if count%1000 == 0 {
			log.Println("current --- ", count)
		}
		line++

		title := util.ObjToString(tmp["title"])
		if !strings.Contains(title, "充电") {
			continue
		}
		id := mongodb.BsonIdToSId(tmp["_id"])
		val := []interface{}{}
		for _, v := range subtitles {
			if v == "jyhref" {
				val = append(val, GetJyURLByID(id))
			} else if v == "publishtime" {
				timeObj := time.Unix(util.Int64All(tmp[v]), 0)
				formattedTime := timeObj.Format("2006-01-02 15:04:05")
				val = append(val, formattedTime)
			} else {
				val = append(val, tmp[v])
			}

		}
		err := xlsx.SetSheetRow(sheet, fmt.Sprintf("%s%d", "A", line), &val)
		if err != nil {
			log.Println(err)
			return
		}
		_ = xlsx.SetCellStyle(sheet, fmt.Sprintf("%s%d", "A", line), "BA"+strconv.Itoa(line), styleOne)

		tmp = make(map[string]interface{})

	}
	xlsx.Path = exportFile
	xlsx.Save()

	log.Println("dealTmp over ", count)

}

// GetJyURLByID 获取剑鱼地址
func GetJyURLByID(id string) string {
	var Url = "https://www.jianyu360.com/article/content/%s.html"
	url := fmt.Sprintf(Url, util.CommonEncodeArticle("content", id))

	return url
}

// GetIdByURL 解密url，获取bidding ID
func GetIdByURL(url string) string {
	if strings.Contains(url, "work-bench") {
		return ""
	}
	if strings.Contains(url, "/article/content") {
		urls := strings.Split(url, "content/")
		res := strings.Split(urls[1], ".html")
		ids := util.CommonDecodeArticle("content", res[0])
		return ids[0]
	}
	if strings.HasSuffix(url, "appid") {
		urls := strings.Split(url, "entservice/")
		res := strings.Split(urls[1], ".html")
		se := util.SimpleEncrypt{Key: "entservice"}
		id := se.DecodeString(res[0])
		return id
	}

	return ""
}