jyps/getEs/buyer_clickhouse.go

package main

import (
	"fmt"
	"github.com/gogf/gf/v2/frame/g"
	"github.com/gogf/gf/v2/os/gctx"
	"github.com/xuri/excelize/v2"
	util "jygit.jydev.jianyu360.cn/data_processing/common_utils"
	"jygit.jydev.jianyu360.cn/data_processing/common_utils/mongodb"
	"log"
	"strconv"
	"strings"
)

type Buyer struct {
	ID               uint32 `gorm:"column:id;comment:'自增ID'"`
	CompanyID        string `gorm:"column:company_id;comment:'公司ID'"`
	CompanyName      string `gorm:"column:company_name;comment:'公司名称'"`
	OrgCode          string `gorm:"column:org_code;comment:'组织机构代码'"`
	CompanyType      string `gorm:"column:company_type;comment:'公司类型'"`
	CompanyAddress   string `gorm:"column:company_address;comment:'通讯地址'"`
	CreditNo         string `gorm:"column:credit_no;comment:'统一信用代码'"`
	Area             string `gorm:"column:area;comment:'省份'"`
	City             string `gorm:"column:city;comment:'城市'"`
	District         string `gorm:"column:district;comment:'区县'"`
	CompanyShortname string `gorm:"column:company_shortname;comment:'企业简称'"`
	Capital          string `gorm:"column:capital;comment:'注册资本'"`
	ProvinceShort    string `gorm:"column:province_short;comment:'省份简称'"`
	Table            string `gorm:"column:table;comment:'来源表'"`
}

// 操作类型  登记机关  经营范围  注册资本  地址  company_id  company_status  company_type  credit_no  organizer  province_short
func buyer() {
	//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()

	//mixdata.qyxy_std
	MgoS := &mongodb.MongodbSim{
		//MongodbAddr: "127.0.0.1:27083",
		MongodbAddr: "172.17.189.140:27080",
		DbName:      "mixdata",
		Size:        10,
		UserName:    "SJZY_RWbid_ES",
		Password:    "SJZY@B4i4D5e6S",
		//Direct:   true,
	}
	MgoS.InitPool()

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

	rows, err := f.GetRows("招标-采购单位名单")
	for i := 1; i < len(rows); i++ {
		if i%1000 == 0 {
			log.Println("current", i)
		}

		if len(rows[i]) == 0 {
			continue
		}
		name := strings.TrimSpace(rows[i][0])
		query := map[string]interface{}{
			"company_name": name,
		}
		data, _ := MgoS.FindOne("qyxy_std", query)
		if data != nil && len(*data) > 0 {
			buyer := Buyer{
				CompanyID:        util.ObjToString((*data)["_id"]),
				CompanyName:      name,
				OrgCode:          util.ObjToString((*data)["org_code"]),
				CompanyType:      util.ObjToString((*data)["company_type"]),
				CompanyAddress:   util.ObjToString((*data)["company_address"]),
				CreditNo:         util.ObjToString((*data)["credit_no"]),
				Area:             util.ObjToString((*data)["company_area"]),
				City:             util.ObjToString((*data)["company_city"]),
				District:         util.ObjToString((*data)["company_district"]),
				CompanyShortname: util.ObjToString((*data)["company_shortname"]),
				Table:            "qyxy_std",
			}

			if capital, ok := (*data)["capital"].(string); ok {
				buyer.Capital = capital
			} else {
				capital := util.Float64All((*data)["capital"])
				// 将 float64 转换为字符串，保留两位小数
				strValue := strconv.FormatFloat(capital, 'f', 2, 64)
				buyer.Capital = strValue
			}
			_, err := g.DB().Insert(gctx.New(), "buyers", buyer)
			if err != nil {
				log.Println("clickhouse 写入失败;", err, name)
			}
			continue
		} else {
			data, _ := Mgo2.FindOne("special_gov_unit", query)
			if data != nil && len(*data) > 0 {
				buyer := Buyer{
					CompanyID:      util.ObjToString((*data)["company_id"]),
					CompanyType:    util.ObjToString((*data)["company_type"]),
					CompanyAddress: util.ObjToString((*data)["company_address"]),
					CreditNo:       util.ObjToString((*data)["credit_no"]),
					ProvinceShort:  util.ObjToString((*data)["province_short"]),
					Table:          "special_gov_unit",
					CompanyName:    name,
				}
				_, err := g.DB().Insert(gctx.New(), "buyers", buyer)
				if err != nil {
					log.Println("clickhouse 写入失败;", err, name)
				}
				continue
			}

			data, _ = Mgo2.FindOne("special_enterprise", query)
			if data != nil && len(*data) > 0 {
				buyer := Buyer{
					CompanyID:      util.ObjToString((*data)["company_id"]),
					CompanyType:    util.ObjToString((*data)["company_type"]),
					CompanyAddress: util.ObjToString((*data)["company_address"]),
					CreditNo:       util.ObjToString((*data)["credit_no"]),
					ProvinceShort:  util.ObjToString((*data)["province_short"]),
					Table:          "special_enterprise",
					CompanyName:    name,
					Capital:        util.ObjToString((*data)["capital"]),
				}
				_, err := g.DB().Insert(gctx.New(), "buyers", buyer)
				if err != nil {
					log.Println("clickhouse 写入失败;", err, name)
				}
				continue
			}

			data, _ = Mgo2.FindOne("special_social_organ", query)
			if data != nil && len(*data) > 0 {
				buyer := Buyer{
					CompanyID:      util.ObjToString((*data)["company_id"]),
					CompanyType:    util.ObjToString((*data)["company_type"]),
					CompanyAddress: util.ObjToString((*data)["company_address"]),
					CreditNo:       util.ObjToString((*data)["credit_no"]),
					ProvinceShort:  util.ObjToString((*data)["province_short"]),
					Table:          "special_social_organ",
					CompanyName:    name,
					Capital:        util.ObjToString((*data)["capital"]),
				}
				_, err := g.DB().Insert(gctx.New(), "buyers", buyer)
				if err != nil {
					log.Println("clickhouse 写入失败;", err, name)
				}
				continue
			}

		}

	}

	log.Println("over")
}