package main import ( "fmt" "github.com/spf13/viper" "github.com/wcc4869/common_utils" "github.com/wcc4869/common_utils/log" util "jygit.jydev.jianyu360.cn/data_processing/common_utils" //mongodb "github.com/wcc4869/common_utils/mongo" "github.com/xuri/excelize/v2" "go.mongodb.org/mongo-driver/bson" "jygit.jydev.jianyu360.cn/data_processing/common_utils/mongodb" "os" "reflect" "sort" "strconv" "strings" "time" ) var ( GF Conf noExists = make([]string, 0) //不存在的企业名称 //notables = make([]string, 0) //不存在的表 //companyMap = make(map[string]string, 0) //企业ID=>企业名称 Mgo, MgoQ *mongodb.MongodbSim //count int exportFile string //xlsx *excelize.File //styleOne, mergeStyle int saveSize int //保存临时数据bidding_tmp_cp savePool chan map[string]interface{} saveSp chan bool compares = make([]string, 0) //需要对比，排除不在内的企业 //lock sync.Mutex LastData = make(map[string][]interface{}, 0) //L0=>[L2] L0names = make([]string, 0) links = make([]map[string]string, 0) //存储设置 单元格超链接 lineMap = make(map[string]int, 0) lastRes = make(map[string][]interface{}, 0) //最后一个sheet L0, L2 string ) func init() { InitConfig() Mgo = &mongodb.MongodbSim{ MongodbAddr: GF.Mongo.Host, DbName: GF.Mongo.DB, Size: 10, } Mgo.InitPool() // 查询省市区使用 MgoQ = &mongodb.MongodbSim{ MongodbAddr: GF.Mongoq.Host, DbName: GF.Mongoq.DB, UserName: GF.Mongoq.Username, Password: GF.Mongoq.Password, Size: 10, } MgoQ.InitPool() saveSize = 200 saveSp = make(chan bool, 2) savePool = make(chan map[string]interface{}, 5000) } // InitConfig init config func InitConfig() { // 优先级 // 1. -d 添加目录 // 2. SERVER_TOML 环境变量 // 3. ./ 添加目录 // 4. ./conf/ 添加目录 viper.SetConfigFile("config.toml") // 指定配置文件路径 viper.SetConfigName("config") // 配置文件名称(无扩展名) viper.SetConfigType("toml") // 如果配置文件的名称中没有扩展名，则需要配置此项 viper.AddConfigPath("./") viper.AddConfigPath("./conf/") // 还可以在工作目录中查找配置 viper.AddConfigPath("../conf/") // 还可以在工作目录中查找配置 err := viper.ReadInConfig() // 查找并读取配置文件 if err != nil { // 处理读取配置文件的错误 fmt.Println("ReadInConfig err =>", err) } err = viper.Unmarshal(&GF) if err != nil { panic(err) } } func main2() { //setCG() fmt.Println("啦啦啦啦啦啦啦啦啦啦啦") } func main4() { readCompany() } func main() { rs, _ := common_utils.FileExist(GF.Readfile.Path) if rs { readCompany() //compares = readCompare() //if GF.Env.Compare > 0 { // compares = readCompare() //} setSheet1(L0names) //var subLine1 int //公司的光标 //var subLine2 int //公司的光标 //var subLine3 int //公司的光标 //var pline1, pline2, pline3 int //投资上级 //var brline1 int // 记录分支机构写入 //var brpLine1, brpLine2, brpLine3 int var xlsx *excelize.File exists, _ := common_utils.FileExist(exportFile) //设置文件 if exists { xlsx, _ = excelize.OpenFile(exportFile) } else { xlsx = excelize.NewFile() } defer func() { xlsx.Path = exportFile xlsx.Save() }() for i, name := range L0names { L0 = name L2 = compares[i] fmt.Println("iiiii", i) //1.查询基础表，获取公司信息 name = dealName(name) company := getCompany(name) if company == nil { noExists = append(noExists, name) xlsx.SetCellValue("公司列表", fmt.Sprintf("%s%d", "F", i+2), "公司不存在") continue //company = getHistory(name) //if company == nil { // noExists = append(noExists, name) // xlsx.SetCellValue("公司列表", fmt.Sprintf("%s%d", "F", i+2), "公司不存在") // continue //} else { // xlsx.SetCellValue("公司列表", fmt.Sprintf("%s%d", "F", i+2), "曾用名") //} } subSheet1 := "投资下级1" subSheet2 := "投资下级2" subSheet3 := "投资下级3" subSheet4 := "投资下级4" subSheet5 := "投资下级5" //pSheet1 := "投资上级1" //pSheet2 := "投资上级2" //pSheet3 := "投资上级3" brsheet1 := "分支下级1" brsheet2 := "分支下级2" brsheet3 := "分支下级3" //brpSheet1 := "分支上级1" //brpSheet2 := "分支上级2" //brpSheet3 := "分支上级3" fmt.Println("开始投资公司--------------------------------") //投资下级 if GF.Env.Invesl > 0 { inves := getInvests(company["company_id"].(string)) var invest1 = make([]map[string]interface{}, 0) invest1 = dealInves(inves) if len(invest1) > 0 { subLine1 := lineMap[subSheet1] fmt.Println("开始投资公司下级1---------------", name) now := time.Now() _, subLine1 = setInvest(xlsx, invest1, name, subSheet1, subLine1) lineMap[subSheet1] = subLine1 fmt.Printf("%s: setInvest 下级 处理时长为：%v \n", name, time.Since(now)) //公司列表的 下级 linkCell := fmt.Sprintf("B%d", i+2) linkAddr := fmt.Sprintf("%s!C%d", subSheet1, subLine1-len(invest1)+1) link := map[string]string{ "sheet": "公司列表", "cell": linkCell, "addr": linkAddr, "value": "下级", } links = append(links, link) //公司列表的 上级 AlinkAddr := fmt.Sprintf("%s!A%d", "公司列表", i+2) for t := subLine1 - len(invest1) + 1; t <= subLine1; t++ { AlinkCell := fmt.Sprintf("A%d", t) Alink := map[string]string{ "sheet": subSheet1, "cell": AlinkCell, "addr": AlinkAddr, "value": "上级", } links = append(links, Alink) } //traverseDataInvest(xlsx, invest1, 2) //二级投资公司 if GF.Env.Invesl > 1 { for k1, v1 := range invest1 { inves2 := getInvests(v1["company_id"].(string)) var invest2 = make([]map[string]interface{}, 0) invest2 = dealInves(inves2) if len(invest2) > 0 { fmt.Println("开始投资公司下级22---------------", v1["company_name"]) subLine2 := lineMap[subSheet2] _, subLine2 = setInvest(xlsx, invest2, ObjToString(v1["company_name"]), subSheet2, subLine2) lineMap[subSheet2] = subLine2 linkCell := fmt.Sprintf("B%d", subLine1-len(invest1)+1+k1) linkAddr := fmt.Sprintf("%s!C%d", subSheet2, subLine2-len(invest2)+1) link := map[string]string{ "sheet": subSheet1, "cell": linkCell, "addr": linkAddr, "value": "下级", } links = append(links, link) // AlinkAddr := fmt.Sprintf("%s!C%d", subSheet1, subLine1-len(invest1)+1+k1) for t := subLine2 - len(invest2) + 1; t <= subLine2; t++ { AlinkCell := fmt.Sprintf("A%d", t) Alink := map[string]string{ "sheet": subSheet2, "cell": AlinkCell, "addr": AlinkAddr, "value": "上级", } links = append(links, Alink) } // 第三层级 if GF.Env.Invesl > 2 { for k2, v2 := range invest2 { inves3 := getInvests(v2["company_id"].(string)) var invest3 = make([]map[string]interface{}, 0) invest3 = dealInves(inves3) if len(invest3) > 0 { fmt.Println("开始投资公司下级333--------------", v2["company_name"]) subLine3 := lineMap[subSheet3] _, subLine3 = setInvest(xlsx, invest3, ObjToString(v2["company_name"]), subSheet3, subLine3) lineMap[subSheet3] = subLine3 linkCell := fmt.Sprintf("B%d", subLine2-len(invest2)+1+k2) linkAddr := fmt.Sprintf("%s!C%d", subSheet3, subLine3-len(invest3)+1) link := map[string]string{ "sheet": subSheet2, "cell": linkCell, "addr": linkAddr, "value": "下级", } links = append(links, link) AlinkAddr := fmt.Sprintf("%s!C%d", subSheet2, subLine2-len(invest2)+1+k2) for t := subLine3 - len(invest3) + 1; t <= subLine3; t++ { AlinkCell := fmt.Sprintf("A%d", t) Alink := map[string]string{ "sheet": subSheet3, "cell": AlinkCell, "addr": AlinkAddr, "value": "上级", } links = append(links, Alink) } // 第4层级 if GF.Env.Invesl > 3 { for k3, v3 := range invest3 { inves4 := getInvests(v3["company_id"].(string)) var invest4 = make([]map[string]interface{}, 0) invest4 = dealInves(inves4) if len(invest4) > 0 { fmt.Println("开始投资公司下级4444-------------", v3["company_name"]) subLine4 := lineMap[subSheet4] _, subLine4 = setInvest(xlsx, invest4, ObjToString(v3["company_name"]), subSheet4, subLine4) lineMap[subSheet4] = subLine4 linkCell := fmt.Sprintf("B%d", subLine3-len(invest3)+1+k3) linkAddr := fmt.Sprintf("%s!C%d", subSheet4, subLine4-len(invest4)+1) link := map[string]string{ "sheet": subSheet3, "cell": linkCell, "addr": linkAddr, "value": "下级", } links = append(links, link) AlinkAddr := fmt.Sprintf("%s!C%d", subSheet3, subLine3-len(invest3)+1+k3) for t := subLine4 - len(invest4) + 1; t <= subLine4; t++ { AlinkCell := fmt.Sprintf("A%d", t) Alink := map[string]string{ "sheet": subSheet4, "cell": AlinkCell, "addr": AlinkAddr, "value": "上级", } links = append(links, Alink) } // 第5层级 if GF.Env.Invesl > 4 { for k4, v4 := range invest4 { inves5 := getInvests(v4["company_id"].(string)) var invest5 = make([]map[string]interface{}, 0) invest5 = dealInves(inves5) if len(invest5) > 0 { fmt.Println("开始投资公司下级55555--------------", v4["company_name"]) subLine5 := lineMap[subSheet5] _, subLine5 = setInvest(xlsx, invest5, ObjToString(v4["company_name"]), subSheet5, subLine5) lineMap[subSheet5] = subLine5 linkCell := fmt.Sprintf("B%d", subLine4-len(invest4)+1+k4) linkAddr := fmt.Sprintf("%s!C%d", subSheet5, subLine5-len(invest5)+1) link := map[string]string{ "sheet": subSheet4, "cell": linkCell, "addr": linkAddr, "value": "下级", } links = append(links, link) AlinkAddr := fmt.Sprintf("%s!C%d", subSheet4, subLine4-len(invest4)+1+k4) for t := subLine5 - len(invest5) + 1; t <= subLine5; t++ { AlinkCell := fmt.Sprintf("A%d", t) Alink := map[string]string{ "sheet": subSheet5, "cell": AlinkCell, "addr": AlinkAddr, "value": "上级", } links = append(links, Alink) } } } } } } } } } } } } } } } //// 投资上级 //if GF.Env.Pinvesl > 0 { // fmt.Println("开始投资公司上级 1111111111---------------", name) // //设置上级连接 // pinves1 := getParentsByPartner(company["company_id"].(string)) // if len(pinves1) > 0 { // var datas = make([]map[string]interface{}, 0) // datas, isbreak := dealPinves(pinves1, 1) // if isbreak { // continue // } // // now := time.Now() // pline1 := lineMap[pSheet1] // _, pline1 = setInvestP(xlsx, datas, name, pSheet1, pline1) // fmt.Printf("%s: setInvest 上级 处理时长为：%v \n", name, time.Since(now)) // lineMap[pSheet1] = pline1 // AlinkAddr := fmt.Sprintf("%s!C%d", pSheet1, pline1-len(datas)+1) // AlinkCell := fmt.Sprintf("C%d", i+2) // // Alink := map[string]string{ // "sheet": "公司列表", // "cell": AlinkCell, // "addr": AlinkAddr, // "value": "上级", // } // links = append(links, Alink) // // AlinkAddr2 := fmt.Sprintf("%s!A%d", "公司列表", i+2) // // for t := pline1 - len(datas) + 1; t <= pline1; t++ { // AlinkCell2 := fmt.Sprintf("A%d", t) // Alink2 := map[string]string{ // "sheet": pSheet1, // "cell": AlinkCell2, // "addr": AlinkAddr2, // "value": "下级", // } // links = append(links, Alink2) // } // // traverInvestP(xlsx, datas, 2) // } //} //fmt.Println("结束投资公司--------------------------------") //分支机构下级 if GF.Env.Subl > 0 { // 查询分支机构子公司 subs := getSubCompany(company["company_id"].(string)) if len(subs) > 0 { fmt.Println("开始 分支机构 下级 1111111111111----------", name) now := time.Now() data := dealSubs(subs) brline1 := lineMap[brsheet1] fmt.Printf("%s: setSubCompany 下级 整理数据时长为：%v \n", name, time.Since(now)) _, brline1 = setSubCompany(xlsx, data, name, brsheet1, brline1) fmt.Printf("%s: setSubCompany 下级 处理时长为：%v \n", name, time.Since(now)) // linkAddr := fmt.Sprintf("%s!C%d", brsheet1, brline1-len(data)+1) linkCell := fmt.Sprintf("D%d", i+2) link := map[string]string{ "sheet": "公司列表", "cell": linkCell, "addr": linkAddr, "value": "下级", } links = append(links, link) // AlinkAddr := fmt.Sprintf("%s!A%d", "公司列表", i+2) for t := brline1 - len(data) + 1; t <= brline1; t++ { AlinkCell := fmt.Sprintf("A%d", t) Alink := map[string]string{ "sheet": brsheet1, "cell": AlinkCell, "addr": AlinkAddr, "value": "上级", } links = append(links, Alink) } if GF.Env.Subl > 1 { //traverseDataSub(xlsx, data, 2) //二级分支机构 for k1, v := range subs { if v["branch_company_id"] == nil { continue } subs2 := getSubCompany(v["branch_company_id"].(string)) if len(subs2) > 0 { fmt.Println("开始 分支机构 下级 2222222222222----------", v["branch_name"]) data := dealSubs(subs2) brline2 := lineMap[brsheet2] _, brline2 = setSubCompany(xlsx, data, v["branch_name"].(string), brsheet2, brline2) lineMap[brsheet2] = brline2 linkCell := fmt.Sprintf("B%d", brline1-len(subs)+1+k1) linkAddr := fmt.Sprintf("%s!C%d", brsheet2, brline2-len(subs2)+1) link := map[string]string{ "sheet": brsheet1, "cell": linkCell, "addr": linkAddr, "value": "下级", } links = append(links, link) AlinkAddr := fmt.Sprintf("%s!C%d", brsheet1, brline1-len(subs)+1+k1) for t := brline2 - len(subs2) + 1; t <= brline2; t++ { AlinkCell := fmt.Sprintf("A%d", t) Alink := map[string]string{ "sheet": brsheet2, "cell": AlinkCell, "addr": AlinkAddr, "value": "上级", } links = append(links, Alink) } //三级层级 if GF.Env.Subl > 2 { for k2, v2 := range subs2 { if v2["branch_company_id"] == nil { continue } subs3 := getSubCompany(v2["branch_company_id"].(string)) if len(subs3) > 0 { fmt.Println("开始 分支机构 下级 333333333333333----------", v2["branch_name"]) data := dealSubs(subs3) brline3 := lineMap[brsheet3] _, brline3 = setSubCompany(xlsx, data, v2["branch_name"].(string), brsheet3, brline3) lineMap[brsheet3] = brline3 linkCell := fmt.Sprintf("B%d", brline2-len(subs2)+1+k2) linkAddr := fmt.Sprintf("%s!C%d", brsheet3, brline3-len(subs3)+1) link := map[string]string{ "sheet": brsheet2, "cell": linkCell, "addr": linkAddr, "value": "下级", } links = append(links, link) AlinkAddr := fmt.Sprintf("%s!C%d", brsheet2, brline2-len(subs2)+1+k2) for t := brline3 - len(subs3) + 1; t <= brline3; t++ { AlinkCell := fmt.Sprintf("A%d", t) Alink := map[string]string{ "sheet": brsheet3, "cell": AlinkCell, "addr": AlinkAddr, "value": "上级", } links = append(links, Alink) } } } } } } } } } //// 分支机构上级 //if GF.Env.Psubl > 0 { // pbranchs1 := getParentsByBranch(company["company_id"].(string)) // if len(pbranchs1) > 0 { // now := time.Now() // data := dealPsubs(pbranchs1) // fmt.Println("开始 分支机构 上级 11111111111111----------", name) // brpLine1 := lineMap[brpSheet1] // _, brpLine1 = setSubP(xlsx, data, name, brpSheet1, brpLine1) // lineMap[brpSheet1] = brpLine1 // fmt.Printf("%s: setSubCompany 上级 处理时长为：%v \n", name, time.Since(now)) // AlinkAddr := fmt.Sprintf("%s!C%d", brpSheet1, brpLine1-len(data)+1) // AlinkCell := fmt.Sprintf("E%d", i+2) // Alink := map[string]string{ // "sheet": "公司列表", // "cell": AlinkCell, // "addr": AlinkAddr, // "value": "上级", // } // links = append(links, Alink) // // AlinkAddr2 := fmt.Sprintf("%s!A%d", "公司列表", i+2) // // for t := brpLine1 - len(data) + 1; t <= brpLine1; t++ { // AlinkCell2 := fmt.Sprintf("A%d", t) // Alink2 := map[string]string{ // "sheet": brpSheet1, // "cell": AlinkCell2, // "addr": AlinkAddr2, // "value": "下级", // } // links = append(links, Alink2) // } // // traverSubP(xlsx, data, 2) // } //} //fmt.Println("结束分支机构--------------------------------") fmt.Printf("%s -------- 企业数据导出成功\n", name) } dealLast(xlsx) if len(links) > 0 { setCellContentLink(xlsx, links) } fmt.Println("总共企业数量为：", len(L0names), "不存在的企业数量为：", len(noExists)) if len(noExists) > 0 { fmt.Println("具体不存在的企业名称如下：", noExists) } } else { fmt.Println("读取的企业列表文件不存在，请检查") } fmt.Println("所有企业导出结束，") //c := make(chan bool, 1) //<-c } // setSheet1 设置第一个sheet func setSheet1(data []string) (err error) { //导出文件 var xlsx *excelize.File currentPwd, _ := os.Getwd() path := fmt.Sprintf("%s/%s", currentPwd, "导出数据") ex, _ := common_utils.FileExist(path) if !ex { os.MkdirAll(path, 0777) } exportFile = fmt.Sprintf("%s/%v-%v.xlsx", path, "结果", time.Now().Format("2006.01.02 15.04.05")) exists, _ := common_utils.FileExist(exportFile) //设置文件 if exists { xlsx, _ = excelize.OpenFile(exportFile) } else { xlsx = excelize.NewFile() } styleOne, _ := xlsx.NewStyle( &excelize.Style{ Alignment: &excelize.Alignment{ Horizontal: "left", Vertical: "left", }, }, ) //合并单元格样式 mergeStyle, _ := xlsx.NewStyle( &excelize.Style{ Alignment: &excelize.Alignment{ Horizontal: "center", Vertical: "center", }, Fill: excelize.Fill{Type: "pattern", Color: []string{"#E0EBF5"}, Pattern: 1}, Font: &excelize.Font{ Bold: true, Italic: false, Family: "Times New Roman", Size: 22, Color: "#777777", }, }) line := 0 sheet := "公司列表" xlsx.NewSheet(sheet) xlsx.DeleteSheet("Sheet1") _ = xlsx.SetColWidth(sheet, "A", "E", 25) _ = xlsx.SetRowHeight(sheet, 1, 30) subtitles := []interface{}{"公司名称", "投资下级", "投资上级", "分支下级", "分支上级"} //subkeys := []string{"branch_name", "company_status", "authority", "branch_code", "n_company_status", "branch_credit_no", "establish_date"} line++ //设置第一行title _ = xlsx.SetSheetRow(sheet, fmt.Sprintf("%s%d", "A", line), &subtitles) _ = xlsx.SetCellStyle(sheet, fmt.Sprintf("%s%d", "A", line), "E"+strconv.Itoa(line), mergeStyle) for k, _ := range data { line++ val := []interface{}{ data[k], } err = xlsx.SetSheetRow(sheet, fmt.Sprintf("%s%d", "A", line), &val) if err != nil { log.Debug("set sheetrow line err", log.Any(fmt.Sprintf("%s%d", "A", line), err)) return } _ = xlsx.SetCellStyle(sheet, fmt.Sprintf("%s%d", "A", line), "BA"+strconv.Itoa(line), styleOne) } xlsx.Path = exportFile xlsx.Save() return } // setSubCompany 设置子公司 func setSubCompany(xlsx *excelize.File, subs []map[string]interface{}, name, sheet string, li int) (err error, line int) { line = li if len(subs) > 0 { styleOne, _ := xlsx.NewStyle( &excelize.Style{ Alignment: &excelize.Alignment{ Horizontal: "left", Vertical: "left", }, }, ) xlsx.NewSheet(sheet) _ = xlsx.SetColWidth(sheet, "C", "L", 20) //subtitles := []interface{}{"分支机构名称", "登记状态", "分支机构登记机关", "分支机构注册号", "处理后的登记状态", "分支机构统一信用代码", "分支机构成立日期", "省份", "城市", "区域/街道"} //subkeys := []string{"branch_name", "company_status", "authority", "branch_code", "n_company_status", "branch_credit_no", "establish_date", "company_area", "company_city", "company_district"} subtitles := []interface{}{"L0", "分支机构名称", "登记状态", "分支机构登记机关", "分支机构注册号", "处理后的登记状态", "分支机构统一信用代码", "分支机构成立日期"} subkeys := []string{"branch_name", "company_status", "authority", "branch_code", "n_company_status", "branch_credit_no", "establish_date"} if GF.Env.City > 0 { subtitles = append(subtitles, "省份", "城市", "区域/街道", "来源公司") subkeys = append(subkeys, "company_area", "company_city", "company_district") } else { subtitles = append(subtitles, "来源公司") } if line == 0 { line++ //设置第一行title _ = xlsx.SetSheetRow(sheet, fmt.Sprintf("%s%d", "C", line), &subtitles) } for _, data := range subs { line++ subvalus := make([]interface{}, 0) subvalus = append(subvalus, L0) for _, key := range subkeys { if data[key] == nil { subvalus = append(subvalus, "") } else { subvalus = append(subvalus, data[key]) } } subvalus = append(subvalus, name) err = xlsx.SetSheetRow(sheet, fmt.Sprintf("%s%d", "C", line), &subvalus) if err != nil { log.Debug("set sheetrow line err", log.Any(fmt.Sprintf("%s%d", "A", line), err)) return } _ = xlsx.SetCellStyle(sheet, fmt.Sprintf("%s%d", "A", line), "BA"+strconv.Itoa(line), styleOne) } //xlsx.Path = exportFile //xlsx.Save() } return } // setSubP 设置分支上级 func setSubP(xlsx *excelize.File, subs []map[string]interface{}, name, sheet string, li int) (err error, line int) { line = li if len(subs) > 0 { styleOne, _ := xlsx.NewStyle( &excelize.Style{ Alignment: &excelize.Alignment{ Horizontal: "left", Vertical: "left", }, }, ) xlsx.NewSheet(sheet) _ = xlsx.SetColWidth(sheet, "C", "L", 20) subtitles := []interface{}{"L2", "机构名称", "登记状态", "机构登记机关", "机构注册号", "处理后的登记状态", "机构统一信用代码", "机构成立日期"} subkeys := []string{"company_name", "company_status", "authority", "branch_code", "n_company_status", "branch_credit_no", "establish_date"} if GF.Env.City > 0 { subtitles = append(subtitles, "省份", "城市", "区域/街道", "来源公司") subkeys = append(subkeys, "company_area", "company_city", "company_district") } else { subtitles = append(subtitles, "来源公司") } if line == 0 { line++ //设置第一行title _ = xlsx.SetSheetRow(sheet, fmt.Sprintf("%s%d", "C", line), &subtitles) } for _, data := range subs { line++ subvalus := make([]interface{}, 0) subvalus = append(subvalus, L0) for _, key := range subkeys { if data[key] == nil { subvalus = append(subvalus, "") } else { subvalus = append(subvalus, data[key]) } } subvalus = append(subvalus, name) err = xlsx.SetSheetRow(sheet, fmt.Sprintf("%s%d", "C", line), &subvalus) if err != nil { log.Debug("set sheetrow line err", log.Any(fmt.Sprintf("%s%d", "A", line), err)) return } _ = xlsx.SetCellStyle(sheet, fmt.Sprintf("%s%d", "A", line), "BA"+strconv.Itoa(line), styleOne) } //xlsx.Path = exportFile //xlsx.Save() } return } // setInvest 设置投资公司 func setInvest(xlsx *excelize.File, subs []map[string]interface{}, name, sheet string, li int) (err error, line int) { line = li if len(subs) > 0 { styleOne, _ := xlsx.NewStyle( &excelize.Style{ Alignment: &excelize.Alignment{ Horizontal: "left", Vertical: "left", }, }, ) xlsx.NewSheet(sheet) xlsx.DeleteSheet("Sheet1") _ = xlsx.SetColWidth(sheet, "C", "G", 20) _ = xlsx.SetRowHeight(sheet, line, 20) //subtitles := []interface{}{"企业名称", "企业注册号", "股东类型", "股东名称", "占股比例", "省份", "城市", "区域/街道", "来源公司"} //subkeys := []string{"company_name", "credit_no", "stock_type", "stock_name", "stock_proportion", "company_area", "company_city", "company_district"} subtitles := []interface{}{"L0", "企业名称", "统一社会信用代码", "股东类型", "股东名称", "占股比例"} subkeys := []string{"company_name", "credit_no", "stock_type", "stock_name", "stock_proportion"} if GF.Env.City > 0 { subtitles = append(subtitles, "省份", "城市", "区域/街道", "来源公司") subkeys = append(subkeys, "company_area", "company_city", "company_district") } else { subtitles = append(subtitles, "来源公司") } // 设置一次表头 if line == 0 { line++ //设置第一行title err = xlsx.SetSheetRow(sheet, fmt.Sprintf("%s%d", "C", line), &subtitles) if err != nil { log.Error("setInvest ", log.Any("SetSheetRow", err)) return } } for _, data := range subs { line++ subvalus := make([]interface{}, 0) subvalus = append(subvalus, L0) for _, key := range subkeys { subvalus = append(subvalus, data[key]) } subvalus = append(subvalus, name) err = xlsx.SetSheetRow(sheet, fmt.Sprintf("%s%d", "C", line), &subvalus) if err != nil { log.Debug("set sheetrow line err", log.Any(fmt.Sprintf("%s%d", "A", line), err)) return } _ = xlsx.SetCellStyle(sheet, fmt.Sprintf("%s%d", "C", line), "BA"+strconv.Itoa(line), styleOne) } // //xlsx.Path = exportFile //xlsx.Save() } return } func setInvestP(xlsx *excelize.File, subs []map[string]interface{}, name, sheet string, li int) (err error, line int) { line = li if len(subs) > 0 { styleOne, _ := xlsx.NewStyle( &excelize.Style{ Alignment: &excelize.Alignment{ Horizontal: "left", Vertical: "left", }, }, ) xlsx.NewSheet(sheet) xlsx.DeleteSheet("Sheet1") _ = xlsx.SetColWidth(sheet, "C", "G", 20) _ = xlsx.SetRowHeight(sheet, line, 20) subtitles := []interface{}{"L2", "企业名称", "统一社会信用代码", "股东类型", "股东名称", "占股比例"} subkeys := []string{"company_name", "credit_no", "stock_type", "stock_name", "stock_proportion"} if GF.Env.City > 0 { subtitles = append(subtitles, "省份", "城市", "区域/街道", "来源公司") subkeys = append(subkeys, "company_area", "company_city", "company_district") } else { subtitles = append(subtitles, "来源公司") } // 设置一次表头 if line == 0 { line++ //设置第一行title err = xlsx.SetSheetRow(sheet, fmt.Sprintf("%s%d", "C", line), &subtitles) if err != nil { log.Error("setInvest ", log.Any("SetSheetRow", err)) return } } for _, data := range subs { line++ subvalus := make([]interface{}, 0) subvalus = append(subvalus, L0) for _, key := range subkeys { var value interface{} if key == "credit_no" { base := getCompany(name) value = base["credit_no"] } else { value = data[key] } subvalus = append(subvalus, value) } subvalus = append(subvalus, name) err = xlsx.SetSheetRow(sheet, fmt.Sprintf("%s%d", "C", line), &subvalus) if err != nil { log.Debug("set sheetrow line err", log.Any(fmt.Sprintf("%s%d", "A", line), err)) return } _ = xlsx.SetCellStyle(sheet, fmt.Sprintf("%s%d", "C", line), "BA"+strconv.Itoa(line), styleOne) } } return } // getSubCompany 获取分支机构 func getSubCompany(companyId string) (res []map[string]interface{}) { q := map[string]interface{}{"company_id": companyId, "use_flag": 0} info, _ := Mgo.Find("company_branch", q, nil, nil, false, -1, -1) return *info } // getInvests 获取对外投资公司 func getInvests(companyId string) []map[string]interface{} { q := map[string]interface{}{"stock_name_id": companyId, "use_flag": 0, "is_history": 0} info, _ := Mgo.Find("company_partner", q, nil, nil, false, -1, -1) return *info } // getParents 获取母公司 func getParentsByBranch(companyId string) (res []map[string]interface{}) { q := map[string]interface{}{"branch_company_id": companyId, "use_flag": 0} info, _ := Mgo.Find("company_branch", q, nil, nil, false, -1, -1) return *info } // getParentsByPartner 获取母公司 func getParentsByPartner(companyId string) (res []map[string]interface{}) { q := map[string]interface{}{"company_id": companyId, "use_flag": 0, "is_history": 0} info, _ := Mgo.Find("company_partner", q, nil, nil, false, -1, -1) return *info } // getCompany 获取公司基本信息 func getCompany(name string) (res map[string]interface{}) { info, _ := Mgo.FindOne("company_base", map[string]interface{}{"company_name": name}) return *info } // getHistory 根据曾用名查看历史 func getHistory(historyName string) (res map[string]interface{}) { info, _ := Mgo.Find("company_history_name", map[string]interface{}{"history_name": historyName}, map[string]interface{}{"update_time": -1}, nil, true, -1, 1) for _, v := range *info { res = v } return } func dealName(name string) string { name = strings.Replace(name, "(", "（", -1) name = strings.Replace(name, ")", "）", -1) return name } // getCompanyById 获取公司 func getCompanyById(id string) (res map[string]interface{}) { info, _ := Mgo.FindOne("company_base", map[string]interface{}{"company_id": id, "use_flag": 0}) return *info } // readCompany 读取文件获取企业名称 func readCompany() { //读取文件 f, err := excelize.OpenFile(GF.Readfile.Path) 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 } seen := make(map[string]bool) for i := 1; i < len(rows); i++ { if len(rows[i]) > 0 { name := rows[i][0] if !seen[name] { seen[name] = true L0names = append(L0names, name) } l2name := rows[i][1] compares = append(compares, l2name) datas := LastData[name] datas = append(datas, []interface{}{name, l2name}) LastData[name] = datas } } //for _, v := range L0names { // //fmt.Println(v, "->", LastData[v]) // fmt.Println(v, "->", len(LastData[v])) // //oldData := LastData[v] // // //for _, v2 := range oldData { // // if v2d, ok := v2.([]interface{}); ok { // // fmt.Println(v2d) // // } // //} // //} return } // readCompare 读取排除企业名单 func readCompare() (names []string) { //读取文件 f, err := excelize.OpenFile(GF.Readfile.Path) 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 } //seen := make(map[string]bool) for i := 1; i < len(rows); i++ { if len(rows[i]) > 0 { name := rows[i][1] names = append(names, dealName(name)) } } return } // setCellContentLink 设置单元格内容和超链接 func setCellContentLink(xlsx *excelize.File, data []map[string]string) { for _, v := range data { //fmt.Println(v) xlsx.SetCellValue(ObjToString(v["sheet"]), ObjToString(v["cell"]), ObjToString(v["value"])) style, _ := xlsx.NewStyle(&excelize.Style{ Font: &excelize.Font{Color: "1265BE", Underline: "single"}, }) xlsx.SetCellStyle(ObjToString(v["sheet"]), ObjToString(v["cell"]), ObjToString(v["cell"]), style) xlsx.SetCellHyperLink(ObjToString(v["sheet"]), ObjToString(v["cell"]), ObjToString(v["addr"]), "Location") //setCellContentLink(v["sheet"], v["cell"], v["addr"], v["value"]) } } // parsePartner 处理 投资公司，添加省市区字段 func getStd(id string) (res map[string]interface{}) { filter := bson.M{"_id": id} info, _ := MgoQ.FindOne("qyxy_std", filter) return *info } func getStdByName(name string) (res map[string]interface{}) { filter := bson.M{"company_name": name} info, _ := MgoQ.FindOne("qyxy_std", filter) return *info } // dealInves 针对 投资公司下级，做排序，过滤处理 func dealInves(ss []map[string]interface{}) []map[string]interface{} { data := dealProportion(ss) if len(data) > 0 { var invest1 = make([]map[string]interface{}, 0) for _, v := range data { v["wcc_type"] = 1 //需要排除右侧企业时 //if GF.Env.Compare > 0 && len(compares) > 0 { // if IsInStringArray(v["company_name"].(string), compares) { // continue // } //} proportion, _ := v["stock_proportion"].(float64) //1.大于0.5 if proportion >= GF.Env.Proportion { if GF.Env.City > 0 { std := getStd(ObjToString(v["company_id"])) v["company_city"] = std["company_city"] v["company_area"] = std["company_area"] v["company_district"] = std["company_district"] v["company_status"] = std["company_status"] v["credit_no"] = std["credit_no"] } if !isInMapArray(v, invest1) { invest1 = append(invest1, v) } } //else { // //2.小于0.34 直接过滤 // if proportion < 0.34 { // continue // } // //3. 找到所有上级，判断是否是最大投资股东 // pars := getParentsByPartner(v["company_id"].(string)) // if len(pars) > 0 { // pares := dealProportion(pars) // if pares[0]["stock_name_id"] != v["company_id"] { // continue // } // } // //3.投资比例大于0.34，并且是最大股东，才展示 // if GF.Env.City > 0 { // std := getStd(ObjToString(v["company_id"])) // v["company_city"] = std["company_city"] // v["company_area"] = std["company_area"] // v["company_district"] = std["company_district"] // v["company_status"] = std["company_status"] // v["credit_no"] = std["credit_no"] // } // if !isInMapArray(v, invest1) { // invest1 = append(invest1, v) // } //} } for _, v := range invest1 { var company_name, company_status, credit_no, company_area, company_city, company_district string if v["company_name"] != nil { company_name = v["company_name"].(string) } if v["company_status"] != nil { company_status = v["company_status"].(string) } if v["credit_no"] != nil { credit_no = v["credit_no"].(string) } if v["company_area"] != nil { company_area = v["company_area"].(string) } if v["company_city"] != nil { company_city = v["company_city"].(string) } if v["company_district"] != nil { company_district = v["company_district"].(string) } tmp := []interface{}{L0, company_name, v["stock_proportion"], company_status, credit_no, company_area, company_city, company_district} if !IsContained(tmp, lastRes[L0]) { lastRes[L0] = append(lastRes[L0], tmp) } if GF.Env.Savecoll != "" { delete(v, "_id") Mgo.Save(GF.Env.Savecoll, v) } } return invest1 } return []map[string]interface{}{} } // dealPinves 处理投资上级数据 func dealPinves(ss []map[string]interface{}, level int) ([]map[string]interface{}, bool) { var data = make([]map[string]interface{}, 0) var res = make([]map[string]interface{}, 0) isbreak := false for _, v := range ss { if v["stock_name"] == nil { continue } if proportion, ok := v["stock_proportion"]; ok { va := reflect.ValueOf(proportion) switch va.Kind() { case reflect.String: proportionD, _ := strconv.ParseFloat(proportion.(string), 64) v["stock_proportion"] = proportionD data = append(data, v) case reflect.Float64: v["stock_proportion"] = proportion data = append(data, v) } } } if len(data) > 0 { sort.Slice(data, func(i, j int) bool { proportion, _ := data[i]["stock_proportion"].(float64) proportion2, _ := data[j]["stock_proportion"].(float64) return proportion > proportion2 }) for _, v := range data { v["wcc_type"] = 2 if v["stock_name"] == nil { continue } if v["stock_name_id"] == nil { isbreak = true } if GF.Env.Compare > 0 && len(compares) > 0 { if IsInStringArray(v["company_name"].(string), compares) { continue } } proportion, _ := v["stock_proportion"].(float64) if proportion >= GF.Env.Proportion { if GF.Env.City > 0 { std := getStd(ObjToString(v["stock_name_id"])) v["company_city"] = std["company_city"] v["company_area"] = std["company_area"] v["company_district"] = std["company_district"] v["company_status"] = std["company_status"] v["credit_no"] = std["credit_no"] } //投资公司是他本身时，停止 if v["stock_name"] == L0 { isbreak = true } res = append(res, v) break } else { isbreak = true v["is_break"] = true if level == 1 { break } if GF.Env.City > 0 { std := getStd(ObjToString(v["stock_name_id"])) v["company_city"] = std["company_city"] v["company_area"] = std["company_area"] v["company_district"] = std["company_district"] v["company_status"] = std["company_status"] v["credit_no"] = std["credit_no"] } res = append(res, v) if GF.Env.Savecoll != "" { //savePool <- v delete(v, "_id") Mgo.Save(GF.Env.Savecoll, v) } break } } // 最后一个sheet if len(res) > 0 { for _, v := range res { if v["stock_type"] == "自然人股东" || v["stock_type"] == "外国（地区）企业" && (v["company_area"] == "" || v["company_area"] == nil) || strings.HasSuffix(v["stock_name"].(string), "国务院") || strings.HasSuffix(v["stock_name"].(string), "国资委") || strings.HasSuffix(v["stock_name"].(string), "国有资产监督管理委员会") || strings.Contains(v["stock_name"].(string), "财政厅") || v["is_break"] == true { var company_name, company_status, credit_no, company_area, company_city, company_district string if v["company_name"] != nil { company_name = v["company_name"].(string) } if v["company_status"] != nil { company_status = v["company_status"].(string) } if v["credit_no"] != nil { credit_no = v["credit_no"].(string) } if v["company_area"] != nil { company_area = v["company_area"].(string) } if v["company_city"] != nil { company_city = v["company_city"].(string) } if v["company_district"] != nil { company_district = v["company_district"].(string) } tmp := []interface{}{L0, company_name, company_status, credit_no, company_area, company_city, company_district} if !IsContained(tmp, lastRes[L0]) { if level > 1 { lastRes[L0] = append(lastRes[L0], tmp) } } } } } } return res, isbreak } // dealSubs 处理分支机构下级数据 func dealSubs(ss []map[string]interface{}) []map[string]interface{} { var data = make([]map[string]interface{}, 0) for _, s := range ss { if s["branch_name"] == nil || s["branch_name"] == "" { continue } if GF.Env.Compare > 0 && len(compares) > 0 { if IsInStringArray(s["branch_name"].(string), compares) { continue } } if util.ObjToString(s["company_status"]) == "已吊销" || util.ObjToString(s["company_status"]) == "吊销" || util.ObjToString(s["company_status"]) == "注销" { continue } s["wcc_type"] = 3 if GF.Env.City > 0 { std := getStd(ObjToString(s["branch_company_id"])) s["company_city"] = std["company_city"] s["company_area"] = std["company_area"] s["company_district"] = std["company_district"] s["credit_no"] = std["credit_no"] s["company_status"] = std["company_status"] data = append(data, s) } else { data = append(data, s) } // 最后一个sheet tmp := []interface{}{L0, s["branch_name"], 1, s["company_status"], s["credit_no"], s["company_area"], s["company_city"], s["company_district"]} if !IsContained(tmp, lastRes[L0]) { lastRes[L0] = append(lastRes[L0], tmp) } if GF.Env.Savecoll != "" { //savePool <- s delete(s, "_id") Mgo.Save(GF.Env.Savecoll, s) } } return data } // dealPsub 处理分支机构上级数据 func dealPsubs(ss []map[string]interface{}) []map[string]interface{} { var data = make([]map[string]interface{}, 0) for _, s := range ss { if s["company_id"] == nil || s["company_id"] == "" { continue } std := getStd(ObjToString(s["company_id"])) s["company_name"] = std["company_name"] if GF.Env.City > 0 { s["company_city"] = std["company_city"] s["company_area"] = std["company_area"] s["company_district"] = std["company_district"] s["company_status"] = std["company_status"] s["credit_no"] = std["credit_no"] s["wcc_type"] = 4 data = append(data, s) } else { s["wcc_type"] = 4 data = append(data, s) } if GF.Env.Savecoll != "" { //savePool <- s delete(s, "_id") Mgo.Save(GF.Env.Savecoll, s) } var company_name, company_status, credit_no, company_area, company_city, company_district string if s["company_name"] != nil { company_name = s["company_name"].(string) } if s["company_status"] != nil { company_status = s["company_status"].(string) } if s["credit_no"] != nil { credit_no = s["credit_no"].(string) } if s["company_area"] != nil { company_area = s["company_area"].(string) } if s["company_city"] != nil { company_city = s["company_city"].(string) } if s["company_district"] != nil { company_district = s["company_district"].(string) } tmp := []interface{}{L0, company_name, company_status, credit_no, company_area, company_city, company_district} if !IsContained(tmp, lastRes[L0]) { lastRes[L0] = append(lastRes[L0], tmp) } } return data } // dealProportion 处理投资比例，并倒序排序 func dealProportion(ss []map[string]interface{}) []map[string]interface{} { var data = make([]map[string]interface{}, 0) //1.转换成float for _, v := range ss { if v["company_name"] == nil { continue } if proportion, ok := v["stock_proportion"]; ok { va := reflect.ValueOf(proportion) switch va.Kind() { case reflect.String: proportionD, _ := strconv.ParseFloat(proportion.(string), 64) v["stock_proportion"] = proportionD data = append(data, v) case reflect.Float64: v["stock_proportion"] = proportion data = append(data, v) } } } //2. 排序，倒序排序 sort.Slice(data, func(i, j int) bool { proportion, _ := data[i]["stock_proportion"].(float64) proportion2, _ := data[j]["stock_proportion"].(float64) return proportion > proportion2 }) return data }