yusuan/yusuanExcelExport/main.go

package main

import (
	"fmt"
	"io"
	"log"
	"os"
	"strings"
	"time"

	"strconv"

	util "app.yhyue.com/moapp/jybase/common"
	"app.yhyue.com/moapp/jybase/date"
	mg "app.yhyue.com/moapp/jybase/mongodb"
	"github.com/gogf/gf/v2/util/gconv"
	"github.com/tealeg/xlsx"
	"github.com/xuri/excelize/v2"
)

type config struct {
	Mongodb struct {
		Main *mgoConf
	}
}

type mgoConf struct {
	Address         string
	Size            int
	DbName          string
	UserName        string
	Password        string
	Collection      string
	Collection_back string
}

var (
	Sysconfig *config
	MQFW      mg.MongodbSim
)

func init() {
	util.ReadConfig(&Sysconfig)

	MQFW = mg.MongodbSim{
		MongodbAddr: Sysconfig.Mongodb.Main.Address,
		Size:        Sysconfig.Mongodb.Main.Size,
		DbName:      Sysconfig.Mongodb.Main.DbName,
		UserName:    Sysconfig.Mongodb.Main.UserName,
		Password:    Sysconfig.Mongodb.Main.Password,
	}
	MQFW.InitPool()
}

//补齐数组
func padSliceToLength(data []string, length int) []string {
	for len(data) < length {
		data = append(data, "")
	}
	return data
}

func toLower(str string) string {
	return strings.ToLower(str)
}

//生成行数
func SetRow(s string, row []string, m map[string]string, typ string) []string {
	// areaMap[fmt.Sprintf("%s_%s_%s", year, file_type, area)] = gconv.String(count)
	row = padSliceToLength(row, 43)
	for k, v := range m {
		arr := strings.Split(k, "_")
		area := arr[2]
		year := arr[0]
		file_type := arr[1]

		if area == s {
			if typ == "item" {
				if year == "2024年" {
					switch toLower(file_type) {
					case "xlsx", "xls":
						c := gconv.Int(row[4])
						c += gconv.Int(v)
						row[4] = gconv.String(c)
					case "pdf":
						row[5] = v
					case "doc", "docx":
						c := gconv.Int(row[6])
						c += gconv.Int(v)
						row[6] = gconv.String(c)		

					}
				} else if year == "2023年" {
					switch toLower(file_type) {
					case "xlsx", "xls":
						c := gconv.Int(row[17])
						c += gconv.Int(v)
						row[17] = gconv.String(c)	
					case "pdf":
						row[18] = v
					case "doc", "docx":
						c := gconv.Int(row[6])
						c += gconv.Int(v)
						row[19] = gconv.String(c)
					}
				} else if year == "2025年" {
					switch toLower(file_type) {
					case "xlsx", "xls":
						c := gconv.Int(row[30])
						c += gconv.Int(v)
						row[30] = gconv.String(c)
					case "pdf":
						row[31] = v
					case "doc", "docx":
						c := gconv.Int(row[32])
						c += gconv.Int(v)
						row[32] = gconv.String(c)
					}
				}
			} else if typ == "project" {
				if year == "2024年" {
					switch toLower(file_type) {
					case "xlsx", "xls":
						c := gconv.Int(row[8])
						c += gconv.Int(v)
						row[8] = gconv.String(c)
					case "pdf":
						row[9] = v
					case "doc", "docx":
						c := gconv.Int(row[10])
						c += gconv.Int(v)
						row[10] = gconv.String(c)
					}
				} else if year == "2023年" {
					switch toLower(file_type) {
					case "xlsx", "xls":
						c := gconv.Int(row[21])
						c += gconv.Int(v)
						row[21] = gconv.String(c)
					case "pdf":
						row[22] = v
					case "doc", "docx":
						c := gconv.Int(row[23])
						c += gconv.Int(v)
						row[23] = gconv.String(c)
					}
				} else if year == "2025年" {
					switch toLower(file_type) {
					case "xlsx", "xls":
						c := gconv.Int(row[34])
						c += gconv.Int(v)
						row[34] = gconv.String(c)
					case "pdf":
						row[35] = v
					case "doc", "docx":
						c := gconv.Int(row[36])
						c += gconv.Int(v)
						row[36] = gconv.String(c)
					}
				}
			} else if typ == "purchasing" {
				if year == "2024年" {
					switch toLower(file_type) {
					case "xlsx", "xls":
						c := gconv.Int(row[12])
						c += gconv.Int(v)
						row[12] = gconv.String(c)
					case "pdf":
						row[13] = v
					case "doc", "docx":
						c := gconv.Int(row[14])
						c += gconv.Int(v)
						row[14] = gconv.String(c)
					}
				} else if year == "2023年" {
					switch toLower(file_type) {
					case "xlsx", "xls":
						c := gconv.Int(row[25])
						c += gconv.Int(v)
						row[25] = gconv.String(c)
					case "pdf":
						row[26] = v
					case "doc", "docx":
						c := gconv.Int(row[27])
						c += gconv.Int(v)
						row[27] = gconv.String(c)
					}
				} else if year == "2025年" {
					switch toLower(file_type) {
					case "xlsx", "xls":
						c := gconv.Int(row[38])
						c += gconv.Int(v)
						row[38] = gconv.String(c)
					case "pdf":
						row[39] = v
					case "doc", "docx":
						c := gconv.Int(row[40])
						c += gconv.Int(v)
						row[40] = gconv.String(c)
					}
				}
			}
		}
	}
	//文件
	if gconv.String(gconv.Int(row[4])+gconv.Int(row[5])+gconv.Int(row[6])) != "0" {
		log.Println("~1~~", row[7], gconv.String(gconv.Int(row[4])+gconv.Int(row[5])+gconv.Int(row[6])))
		log.Println("~2~~", gconv.Int(row[4]), gconv.Int(row[5]), gconv.Int(row[6]))
		row[7] = gconv.String(gconv.Int(row[4]) + gconv.Int(row[5]) + gconv.Int(row[6]))
		log.Println("~3~~", row[7])
	}
	if gconv.String(gconv.Int(row[17])+gconv.Int(row[18])+gconv.Int(row[19])) != "0" {
		row[20] = gconv.String(gconv.Int(row[17]) + gconv.Int(row[18]) + gconv.Int(row[19]))
	}
	if gconv.String(gconv.Int(row[30])+gconv.Int(row[31])+gconv.Int(row[32])) != "0" {
		row[33] = gconv.String(gconv.Int(row[30]) + gconv.Int(row[31]) + gconv.Int(row[32]))
	}
	//项目
	if gconv.String(gconv.Int(row[8])+gconv.Int(row[9])+gconv.Int(row[10])) != "0" {
		row[11] = gconv.String(gconv.Int(row[8]) + gconv.Int(row[9]) + gconv.Int(row[10]))
	}
	if gconv.String(gconv.Int(row[21])+gconv.Int(row[22])+gconv.Int(row[23])) != "0" {
		row[24] = gconv.String(gconv.Int(row[21]) + gconv.Int(row[22]) + gconv.Int(row[23]))
	}
	if gconv.String(gconv.Int(row[34])+gconv.Int(row[35])+gconv.Int(row[36])) != "0" {
		row[37] = gconv.String(gconv.Int(row[34]) + gconv.Int(row[35]) + gconv.Int(row[36]))
	}

	//明细
	if gconv.String(gconv.Int(row[12])+gconv.Int(row[13])+gconv.Int(row[14])) != "0" {
		row[15] = gconv.String(gconv.Int(row[12]) + gconv.Int(row[13]) + gconv.Int(row[14]))
	}
	if gconv.String(gconv.Int(row[25])+gconv.Int(row[26])+gconv.Int(row[27])) != "0" {
		row[28] = gconv.String(gconv.Int(row[25]) + gconv.Int(row[26]) + gconv.Int(row[27]))
	}
	if gconv.String(gconv.Int(row[38])+gconv.Int(row[39])+gconv.Int(row[40])) != "0" {
		row[41] = gconv.String(gconv.Int(row[38]) + gconv.Int(row[39]) + gconv.Int(row[40]))
	}

	//明细+绩效
	row[16] = gconv.String(gconv.Int(row[11]) + gconv.Int(row[15]))
	row[29] = gconv.String(gconv.Int(row[24]) + gconv.Int(row[28]))
	row[41] = gconv.String(gconv.Int(row[37]) + gconv.Int(row[41]))
	return row
}

func main() {
	// log.Println(getExcelName())
	copyExcel()
	// _, project_cityMap, _ := yusuan_project_find_area()
	project_areaMap, project_cityMap, project_districtMap := yusuan_project_find_area()
	fileitem_areaMap, fileitem_cityMap, fileitem_districtMap := yusuan_fileitem_find_area()
	purchasing_areaMap, purchasing_cityMap, purchasing_districtMap := yusuan_purchasing_find_area()
	// 打开现有的Excel文件
	f, err := excelize.OpenFile(getExcelName())
	if err != nil {
		fmt.Println(err)
		return
	}

	// 读取数据，假设我们读取第一张表单中A列的数据
	rows, err := f.GetRows("预算数据解析汇总表")
	if err != nil {
		fmt.Println(err)
		return
	}

	// 遍历行数据，进行更新
	for i, row := range rows {
		// 假设我们要更新A列中值为"老数据"的单元格
		// if row[0] == "老数据" {
		//     // 更新数据，这里只是简单地将"老数据"替换为"新数据"
		//     rows[i][0] = "新数据"
		// }
		if i < 5 {
			continue
		}

		// if i == 5 {
		// log.Println(row)
		row = padSliceToLength(row, 4)
		key := row[1] + row[2] + row[3]
		// row = append(row, gconv.String(project_areaMap[key]))
		row = SetRow(key, row, project_areaMap, "project")
		row = SetRow(key, row, project_cityMap, "project")
		row = SetRow(key, row, project_districtMap, "project")

		row = SetRow(key, row, fileitem_areaMap, "item")
		row = SetRow(key, row, fileitem_cityMap, "item")
		row = SetRow(key, row, fileitem_districtMap, "item")

		row = SetRow(key, row, purchasing_areaMap, "purchasing")
		row = SetRow(key, row, purchasing_cityMap, "purchasing")
		row = SetRow(key, row, purchasing_districtMap, "purchasing")

		err = f.SetSheetRow("预算数据解析汇总表", fmt.Sprintf("A%d", i+1), &row)
		if err != nil {
			log.Println("errrrr:", err)
		}
		// }
		// log.Println(i, "--", row)
	}
	if err := f.SaveAs(getExcelName()); err != nil {
		fmt.Println(err)
	}

	f.Close()

	setCount()

	ccc := 0
	for _, v := range project_areaMap {
		// arr := strings.Split(k, "_")
		// area := arr[2]
		// year := arr[0]
		// file_type := arr[1]
		c := gconv.Int(v)
		ccc += c
	}
	log.Println("area:", ccc)
	ccc2 := 0
	for _, v := range project_cityMap {
		// arr := strings.Split(k, "_")
		// area := arr[2]
		// year := arr[0]
		// file_type := arr[1]
		c := gconv.Int(v)
		ccc2 += c
	}
	log.Println("area:", ccc2)
	ccc3 := 0
	for _, v := range project_districtMap {
		// arr := strings.Split(k, "_")
		// area := arr[2]
		// year := arr[0]
		// file_type := arr[1]
		c := gconv.Int(v)
		ccc3 += c
	}
	log.Println("area:", ccc3)
}

func setCount() {
	// 打开 Excel 文件
	file, err := xlsx.OpenFile(getExcelName())
	if err != nil {
		fmt.Println("Error:", err)
		return
	}

	// 获取第一个工作表
	sheet := file.Sheets[0]

	// 遍历每一列,计算从第六行开始的数据总数(除了ABCD前四列)
	columnTotals := make([]float64, sheet.MaxCol)
	for i := 4; i < sheet.MaxCol; i++ {
		total := 0.0
		for j := 5; j < sheet.MaxRow; j++ {
			cell := sheet.Cell(j, i)
			value, err := strconv.ParseFloat(cell.String(), 64)
			if err == nil {
				total += value
			} else {
				//fmt.Printf("Error parsing value in row %d, column %d: %v\n", j+1, i+1, err)
			}
		}
		columnTotals[i] = total
	}

	// 将每列的总数写入到每列的第五行
	for i := 4; i < sheet.MaxCol; i++ {
		cell := sheet.Cell(4, i)
		cell.SetString(fmt.Sprintf("%.2f", columnTotals[i]))
	}

	// 保存修改后的 Excel 文件
	err = file.Save(getExcelName())
	if err != nil {
		fmt.Println("Error:", err)
		return
	}

	fmt.Println("Excel file saved successfully.")
}

func getExcelName() string {
	return fmt.Sprintf("./预算数据统计表%s.xlsx", time.Now().Format(date.Date_yyyyMMdd))
}

func copyExcel() {
	// 源文件路径
	srcFilePath := "./预算数据统计表_模板.xlsx"
	// 目标文件路径
	dstFilePath := getExcelName()

	// 打开源文件
	srcFile, err := os.Open(srcFilePath)
	if err != nil {
		fmt.Println("Error opening source file:", err)
		return
	}
	defer srcFile.Close()

	// 创建目标文件
	dstFile, err := os.Create(dstFilePath)
	if err != nil {
		fmt.Println("Error creating destination file:", err)
		return
	}
	defer dstFile.Close()

	// 复制文件内容
	_, err = io.Copy(dstFile, srcFile)
	if err != nil {
		fmt.Println("Error copying file:", err)
		return
	}

}

//
func yusuan_project_find_area() (map[string]string, map[string]string, map[string]string) {
	//聚合查询语句
	areaMap := map[string]string{}
	cityMap := map[string]string{}
	districtMap := map[string]string{}
	log.Println("开始计算 项目 省份数据。。")
	pipeline := []map[string]interface{}{
		map[string]interface{}{
			"$match": map[string]interface{}{
				"$expr": map[string]interface{}{
					"$eq": []string{"$city", "$area"},
				},
			},
		},
		map[string]interface{}{
			"$group": map[string]interface{}{
				"_id": map[string]interface{}{
					"year":      "$year",
					"area":      "$area",
					"city":      "$city",
					"file_type": "$file_type",
				},
				"count": map[string]interface{}{"$sum": 1},
			},
		},
		map[string]interface{}{
			"$project": map[string]interface{}{
				"year":      "$_id.year",
				"area":      "$_id.area",
				"count":     1,
				"file_type": "$_id.file_type",
			},
		},
	}
	sess := MQFW.GetMgoConn()
	defer MQFW.DestoryMongoConn(sess)
	it := sess.DB("yusuan").C("yusuan_project").Pipe(pipeline).Iter()
	for tp := make(map[string]interface{}); it.Next(&tp); {
		log.Println("省份项目：", tp)
		area := gconv.String(tp["area"])
		area = municipality("", area)
		count := gconv.Int(tp["count"])
		year := gconv.String(tp["year"])
		file_type := gconv.String(tp["file_type"])
		areaMap[fmt.Sprintf("%s_%s_%s", year, file_type, area)] = gconv.String(count)
		tp = make(map[string]interface{})
	}
	log.Println("结束计算 项目 省份数据。。")
	log.Println("开始计算 项目 城市数据。。")
	//市区
	pipeline_city := []map[string]interface{}{
		map[string]interface{}{
			"$match": map[string]interface{}{
				"$or": []map[string]interface{}{
					map[string]interface{}{"district": ""},
					map[string]interface{}{"district": nil},
					map[string]interface{}{"district": map[string]interface{}{"$exists": false}},
				},
				"$expr": map[string]interface{}{
					"$ne": []string{"$city", "$area"},
				},
			},
		},
		map[string]interface{}{
			"$group": map[string]interface{}{
				"_id": map[string]interface{}{
					"year":      "$year",
					"area":      "$area",
					"city":      "$city",
					"file_type": "$file_type",
				},
				"count": map[string]interface{}{"$sum": 1},
			},
		},
		map[string]interface{}{
			"$project": map[string]interface{}{
				"year":      "$_id.year",
				"area":      "$_id.area",
				"city":      "$_id.city",
				"count":     1,
				"file_type": "$_id.file_type",
			},
		},
	}
	sess2 := MQFW.GetMgoConn()
	defer MQFW.DestoryMongoConn(sess2)
	it2 := sess2.DB("yusuan").C("yusuan_project").Pipe(pipeline_city).Iter()
	for tp := make(map[string]interface{}); it2.Next(&tp); {
		log.Println("城市项目：", tp)
		area := gconv.String(tp["area"])
		area = municipality(area, "")
		city := gconv.String(tp["city"])
		count := gconv.Int(tp["count"])
		year := gconv.String(tp["year"])
		file_type := gconv.String(tp["file_type"])
		cityMap[fmt.Sprintf("%s_%s_%s", year, file_type, area+city)] = gconv.String(count)
		tp = make(map[string]interface{})
	}
	log.Println("结束计算 项目 城市数据。。")

	log.Println("开始计算 项目 区县数据。。")
	//区县
	pipeline_district := []map[string]interface{}{
		map[string]interface{}{
			"$match": map[string]interface{}{
				"$or": []map[string]interface{}{
					map[string]interface{}{
						"district": map[string]interface{}{
							"$exists": true,
							"$nin":    []interface{}{nil, ""}},
					},
				},
				"$expr": map[string]interface{}{
					"$not": map[string]interface{}{
						"$regexMatch": map[string]interface{}{
							"input": "$district",
							"regex": "[0-9]",
						},
					},
				},
			},
		},
		map[string]interface{}{
			"$group": map[string]interface{}{
				"_id": map[string]interface{}{
					"year":      "$year",
					"area":      "$area",
					"city":      "$city",
					"district":  "$district",
					"file_type": "$file_type",
				},
				"count": map[string]interface{}{"$sum": 1},
			},
		},
		map[string]interface{}{
			"$project": map[string]interface{}{
				"year":      "$_id.year",
				"area":      "$_id.area",
				"count":     1,
				"city":      "$_id.city",
				"district":  "$_id.district",
				"file_type": "$_id.file_type",
			},
		},
	}
	sess3 := MQFW.GetMgoConn()
	defer MQFW.DestoryMongoConn(sess3)
	it3 := sess3.DB("yusuan").C("yusuan_project").Pipe(pipeline_district).Iter()
	for tp := make(map[string]interface{}); it3.Next(&tp); {
		log.Println("区县项目：", tp)
		area := gconv.String(tp["area"])
		city := gconv.String(tp["city"])
		count := gconv.Int(tp["count"])
		year := gconv.String(tp["year"])
		district := gconv.String(tp["district"])
		file_type := gconv.String(tp["file_type"])
		districtMap[fmt.Sprintf("%s_%s_%s", year, file_type, area+city+district)] = gconv.String(count)
		tp = make(map[string]interface{})
	}
	log.Println("结束计算 项目 区县数据。。")

	return areaMap, cityMap, districtMap
}

//
func yusuan_fileitem_find_area() (map[string]string, map[string]string, map[string]string) {
	//聚合查询语句
	areaMap := map[string]string{}
	cityMap := map[string]string{}
	districtMap := map[string]string{}
	log.Println("开始计算 文件 省份数据。。")
	pipeline := []map[string]interface{}{
		map[string]interface{}{
			"$match": map[string]interface{}{
				"$expr": map[string]interface{}{
					"$eq": []string{"$city", "$area"},
				},
			},
		},
		map[string]interface{}{
			"$group": map[string]interface{}{
				"_id": map[string]interface{}{
					"year":      "$year",
					"area":      "$area",
					"city":      "$city",
					"file_type": "$file_type",
				},
				"count": map[string]interface{}{"$sum": 1},
			},
		},
		map[string]interface{}{
			"$project": map[string]interface{}{
				"year":      "$_id.year",
				"area":      "$_id.area",
				"count":     1,
				"file_type": "$_id.file_type",
			},
		},
	}
	sess := MQFW.GetMgoConn()
	defer MQFW.DestoryMongoConn(sess)
	it := sess.DB("yusuan").C("yusuan_fileitem").Pipe(pipeline).Iter()
	for tp := make(map[string]interface{}); it.Next(&tp); {
		log.Println("省份文件：", tp)
		area := gconv.String(tp["area"])
		area = municipality("", area)
		count := gconv.Int(tp["count"])
		year := gconv.String(tp["year"])
		file_type := gconv.String(tp["file_type"])
		areaMap[fmt.Sprintf("%s_%s_%s", year, file_type, area)] = gconv.String(count)
		tp = make(map[string]interface{})
	}
	log.Println("结束计算 文件 省份数据。。")
	log.Println("开始计算 文件 城市数据。。")
	//市区
	pipeline_city := []map[string]interface{}{
		map[string]interface{}{
			"$match": map[string]interface{}{
				"$or": []map[string]interface{}{
					map[string]interface{}{"district": ""},
					map[string]interface{}{"district": nil},
					map[string]interface{}{"district": map[string]interface{}{"$exists": false}},
				},
				"$expr": map[string]interface{}{
					"$ne": []string{"$city", "$area"},
				},
			},
		},
		map[string]interface{}{
			"$group": map[string]interface{}{
				"_id": map[string]interface{}{
					"year":      "$year",
					"area":      "$area",
					"city":      "$city",
					"file_type": "$file_type",
				},
				"count": map[string]interface{}{"$sum": 1},
			},
		},
		map[string]interface{}{
			"$project": map[string]interface{}{
				"year":      "$_id.year",
				"area":      "$_id.area",
				"city":      "$_id.city",
				"count":     1,
				"file_type": "$_id.file_type",
			},
		},
	}
	sess2 := MQFW.GetMgoConn()
	defer MQFW.DestoryMongoConn(sess2)
	it2 := sess2.DB("yusuan").C("yusuan_fileitem").Pipe(pipeline_city).Iter()
	for tp := make(map[string]interface{}); it2.Next(&tp); {
		log.Println("城市文件：", tp)
		area := gconv.String(tp["area"])
		area = municipality(area, "")
		city := gconv.String(tp["city"])
		count := gconv.Int(tp["count"])
		year := gconv.String(tp["year"])
		file_type := gconv.String(tp["file_type"])
		cityMap[fmt.Sprintf("%s_%s_%s", year, file_type, area+city)] = gconv.String(count)
		tp = make(map[string]interface{})
	}
	log.Println("结束计算 文件 城市数据。。")

	log.Println("开始计算 文件 区县数据。。")
	//区县
	pipeline_district := []map[string]interface{}{
		map[string]interface{}{
			"$match": map[string]interface{}{
				"$or": []map[string]interface{}{
					map[string]interface{}{
						"district": map[string]interface{}{
							"$exists": true,
							"$nin":    []interface{}{nil, ""}},
					},
				},
				"$expr": map[string]interface{}{
					"$not": map[string]interface{}{
						"$regexMatch": map[string]interface{}{
							"input": "$district",
							"regex": "[0-9]",
						},
					},
				},
			},
		},
		map[string]interface{}{
			"$group": map[string]interface{}{
				"_id": map[string]interface{}{
					"year":      "$year",
					"area":      "$area",
					"city":      "$city",
					"district":  "$district",
					"file_type": "$file_type",
				},
				"count": map[string]interface{}{"$sum": 1},
			},
		},
		map[string]interface{}{
			"$project": map[string]interface{}{
				"year":      "$_id.year",
				"area":      "$_id.area",
				"count":     1,
				"city":      "$_id.city",
				"district":  "$_id.district",
				"file_type": "$_id.file_type",
			},
		},
	}
	sess3 := MQFW.GetMgoConn()
	defer MQFW.DestoryMongoConn(sess3)
	it3 := sess3.DB("yusuan").C("yusuan_fileitem").Pipe(pipeline_district).Iter()
	for tp := make(map[string]interface{}); it3.Next(&tp); {
		log.Println("区县文件：", tp)
		area := gconv.String(tp["area"])
		city := gconv.String(tp["city"])
		count := gconv.Int(tp["count"])
		year := gconv.String(tp["year"])
		district := gconv.String(tp["district"])
		file_type := gconv.String(tp["file_type"])
		districtMap[fmt.Sprintf("%s_%s_%s", year, file_type, area+city+district)] = gconv.String(count)
		tp = make(map[string]interface{})
	}
	log.Println("结束计算 文件 区县数据。。")

	return areaMap, cityMap, districtMap
}

//
func yusuan_purchasing_find_area() (map[string]string, map[string]string, map[string]string) {
	//聚合查询语句
	areaMap := map[string]string{}
	cityMap := map[string]string{}
	districtMap := map[string]string{}
	log.Println("开始计算 明细 省份数据。。")
	pipeline := []map[string]interface{}{
		map[string]interface{}{
			"$match": map[string]interface{}{
				"$expr": map[string]interface{}{
					"$eq": []string{"$city", "$area"},
				},
			},
		},
		map[string]interface{}{
			"$group": map[string]interface{}{
				"_id": map[string]interface{}{
					"year":      "$year",
					"area":      "$area",
					"city":      "$city",
					"file_type": "$file_type",
				},
				"count": map[string]interface{}{"$sum": 1},
			},
		},
		map[string]interface{}{
			"$project": map[string]interface{}{
				"year":      "$_id.year",
				"area":      "$_id.area",
				"count":     1,
				"file_type": "$_id.file_type",
			},
		},
	}
	sess := MQFW.GetMgoConn()
	defer MQFW.DestoryMongoConn(sess)
	it := sess.DB("yusuan").C("yusuan_purchasing").Pipe(pipeline).Iter()
	for tp := make(map[string]interface{}); it.Next(&tp); {
		log.Println("省份明细：", tp)
		area := gconv.String(tp["area"])
		area = municipality("", area)
		count := gconv.Int(tp["count"])
		year := gconv.String(tp["year"])
		file_type := gconv.String(tp["file_type"])
		areaMap[fmt.Sprintf("%s_%s_%s", year, file_type, area)] = gconv.String(count)
		tp = make(map[string]interface{})
	}
	log.Println("结束计算 明细 省份数据。。")
	log.Println("开始计算 明细 城市数据。。")
	//市区
	pipeline_city := []map[string]interface{}{
		map[string]interface{}{
			"$match": map[string]interface{}{
				"$or": []map[string]interface{}{
					map[string]interface{}{"district": ""},
					map[string]interface{}{"district": nil},
					map[string]interface{}{"district": map[string]interface{}{"$exists": false}},
				},
				"$expr": map[string]interface{}{
					"$ne": []string{"$city", "$area"},
				},
			},
		},
		map[string]interface{}{
			"$group": map[string]interface{}{
				"_id": map[string]interface{}{
					"year":      "$year",
					"area":      "$area",
					"city":      "$city",
					"file_type": "$file_type",
				},
				"count": map[string]interface{}{"$sum": 1},
			},
		},
		map[string]interface{}{
			"$project": map[string]interface{}{
				"year":      "$_id.year",
				"area":      "$_id.area",
				"city":      "$_id.city",
				"count":     1,
				"file_type": "$_id.file_type",
			},
		},
	}
	sess2 := MQFW.GetMgoConn()
	defer MQFW.DestoryMongoConn(sess2)
	it2 := sess2.DB("yusuan").C("yusuan_purchasing").Pipe(pipeline_city).Iter()
	for tp := make(map[string]interface{}); it2.Next(&tp); {
		log.Println("城市明细：", tp)
		area := gconv.String(tp["area"])
		area = municipality(area, "")
		city := gconv.String(tp["city"])
		count := gconv.Int(tp["count"])
		year := gconv.String(tp["year"])
		file_type := gconv.String(tp["file_type"])
		cityMap[fmt.Sprintf("%s_%s_%s", year, file_type, area+city)] = gconv.String(count)
		tp = make(map[string]interface{})
	}
	log.Println("结束计算 明细 城市数据。。")

	log.Println("开始计算 明细 区县数据。。")
	//区县
	pipeline_district := []map[string]interface{}{
		map[string]interface{}{
			"$match": map[string]interface{}{
				"$or": []map[string]interface{}{
					map[string]interface{}{
						"district": map[string]interface{}{
							"$exists": true,
							"$nin":    []interface{}{nil, ""}},
					},
				},
				"$expr": map[string]interface{}{
					"$not": map[string]interface{}{
						"$regexMatch": map[string]interface{}{
							"input": "$district",
							"regex": "[0-9]",
						},
					},
				},
			},
		},
		map[string]interface{}{
			"$group": map[string]interface{}{
				"_id": map[string]interface{}{
					"year":      "$year",
					"area":      "$area",
					"city":      "$city",
					"district":  "$district",
					"file_type": "$file_type",
				},
				"count": map[string]interface{}{"$sum": 1},
			},
		},
		map[string]interface{}{
			"$project": map[string]interface{}{
				"year":      "$_id.year",
				"area":      "$_id.area",
				"count":     1,
				"city":      "$_id.city",
				"district":  "$_id.district",
				"file_type": "$_id.file_type",
			},
		},
	}
	sess3 := MQFW.GetMgoConn()
	defer MQFW.DestoryMongoConn(sess3)
	it3 := sess3.DB("yusuan").C("yusuan_purchasing").Pipe(pipeline_district).Iter()
	for tp := make(map[string]interface{}); it3.Next(&tp); {
		log.Println("区县明细：", tp)
		area := gconv.String(tp["area"])
		city := gconv.String(tp["city"])
		count := gconv.Int(tp["count"])
		year := gconv.String(tp["year"])
		district := gconv.String(tp["district"])
		file_type := gconv.String(tp["file_type"])
		districtMap[fmt.Sprintf("%s_%s_%s", year, file_type, area+city+district)] = gconv.String(count)
		tp = make(map[string]interface{})
	}
	log.Println("结束计算 明细 区县数据。。")

	return areaMap, cityMap, districtMap
}

//直辖市转换
func municipality(city, area string) string {
	m := map[string]bool{
		"上海": true,
		"北京": true,
		"天津": true,
		"重庆": true,
		"中央": true,
	}
	if m[city] {
		return city + city + "市"
	}
	if m[area] {
		return area + area
	}

	if city != "" {
		return city
	}

	if area != "" {
		return area
	}
	return ""
}