timed_tasks/zhaiquan/export.go

package main

import (
	"fmt"
	"github.com/xuri/excelize/v2"
	"go.uber.org/zap"
	utils "jygit.jydev.jianyu360.cn/data_processing/common_utils"
	"jygit.jydev.jianyu360.cn/data_processing/common_utils/log"
	"os"
	"strconv"
	"time"
)

// exportData 导出专项债数据
func exportData() {
	//查询条件
	area := "湖北省" //省份
	area_list := "湖北"
	start := "2025" // 发售时间

	//
	file := area + 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",
			},
		},
	)
	//1、处理项目数据
	line := 0
	sheet := "项目详情"
	xlsx.NewSheet(sheet)
	xlsx.DeleteSheet("Sheet1")

	projectIDS := make([]int, 0)
	var projects = make([]map[string]interface{}, 0)
	//1.导出项目数据
	err := MysqlDB.Model(&ProjectBaseInfo{}).Where("area = ? AND create_date >= ?", area, start).Find(&projects).Error
	if err != nil {
		log.Error("exportData；Error checking for existing project", zap.Error(err))
	}

	keys := []string{"project_name", "total_investment", "area", "city", "district", "capital", "apply_total_bonds", "other_debt_finacing", "special_debt_capital",
		"expected_return", "project_cost", "project_domain", "project_owner", "start_date", "end_date", "operation_start_date",
		"operation_end_date", "source_income", "construction_content", "remarks", "other_debt_finacing_source", "cost_income_percent", "coverage_multiple", "competent_department",
		"accounting_firm", "law_firm", "create_date", "update_date"}
	titles := []string{"项目名称", "总投资（万元）", "省份", "地市", "区县", "不含专项债资本金（万元）", "申请专项债总额（万元）", "其他债务融资（万元）", "专项债作资本金（万元）", "预期收入（万元）", "成本",
		"项目领域", "项目业主", "建设期开始", "建设期结束", "运营期开始", "运营期结束", "收入来源", "建设内容", "特殊情况备注", "其他债务融资来源", "成本/收入（%）", "覆盖倍数", "主管部门", "会计所", "律所", "入库时间", "更新时间"}

	line++
	//设置第一行title
	_ = xlsx.SetSheetRow(sheet, fmt.Sprintf("%s%d", "A", line), &titles)

	for _, v := range projects {
		projectIDS = append(projectIDS, utils.IntAll(v["id"]))
		//处理数据到文件
		line++
		val := []interface{}{}
		for _, vv := range keys {
			val = append(val, v[vv])
		}
		err := xlsx.SetSheetRow(sheet, fmt.Sprintf("%s%d", "A", line), &val)
		if err != nil {
			log.Error("exportData；Error ", zap.Error(err))
			return
		}
		_ = xlsx.SetCellStyle(sheet, fmt.Sprintf("%s%d", "A", line), "BA"+strconv.Itoa(line), styleOne)
	}

	//保存文件

	log.Info("exportData", zap.Any("项目数据处理完毕,总数是:", len(projects)))
	// 2、处理债券数据
	var bondIds = make([]int, 0)
	err = MysqlDB.Model(&ProjectBondRelation{}).Where("project_id in ? ", projectIDS).Select("bond_id").Find(&bondIds).Error
	if err != nil {
		log.Error("exportData；Error ", zap.Error(err))
	}

	bondIds = removeDuplicates(bondIds)
	//一次性查出所有债权
	var bonds = make([]map[string]interface{}, 0)
	err = MysqlDB.Debug().Model(&BondInfo{}).Where("id in ? ", bondIds).Find(&bonds).Error
	if err != nil {
		log.Error("exportData；Error ", zap.Error(err))
	}

	line2 := 0
	sheet2 := "债券详情"
	xlsx.NewSheet(sheet2)

	keys2 := []string{"bond_name", "bond_short_name", "bond_no", "area", "bond_nature", "bond_type", "official_project_type", "total_amount", "issue_date", "issue_place", "issue_term", "issue_interest_rate", "issue_phase", "way_of_pay_interest",
		"new_bond_amount", "counter_bond_amount", "refinancing_bond_amount", "redemption_method", "value_date", "expiry_date", "pay_interest_date", "late_pay_interest_date", "last_pay_interest", "cumulative_pay_interest"}

	titles2 := []string{"债券名称", "债券简称", "债券编码", "省份", "债券性质", "债券类型", "官方项目类型", "发行金额（万元）", "发行日期", "发行场所", "发行期限(年)", "发行利率(%)", "发行期数", "付息方式", "新增债券（亿元）", "置换债券（亿元）", "再融资债券（亿元）", "赎回方式",
		"起息日", "到息日", "付息日", "最近付息日", "上期已付息（亿元）", "累计付息（亿元）"}

	line2++
	//设置第一行title
	_ = xlsx.SetSheetRow(sheet2, fmt.Sprintf("%s%d", "A", line2), &titles2)

	for _, bondDatas := range bonds {
		line2++
		val := []interface{}{}
		for _, vv := range keys2 {
			val = append(val, bondDatas[vv])
		}
		err = xlsx.SetSheetRow(sheet2, fmt.Sprintf("%s%d", "A", line2), &val)
		if err != nil {
			log.Error("exportData；Error ", zap.Error(err))
		}
		_ = xlsx.SetCellStyle(sheet2, fmt.Sprintf("%s%d", "A", line2), "BA"+strconv.Itoa(line2), styleOne)
	}

	log.Info("exportData", zap.Any("债券数据处理完毕,总数是:", len(bonds)))

	//3.项目列表
	sheet3 := "项目列表"
	xlsx.NewSheet(sheet3)
	line3 := 0
	project_lists := make([]map[string]interface{}, 0)
	err = MysqlDB.Model(&ProjectListInfo{}).Where(" area = ?  AND  issue_date >= ? ", area_list, start).Find(&project_lists).Error
	if err != nil {
		log.Error("exportData；Error checking for existing project", zap.Error(err))
	}
	keys3 := []string{"project_name", "total_investment", "area", "city", "district", "present_issue_amount", "issue_term", "issue_interest_rate", "issue_date"}
	titles3 := []string{"项目名称", "总投资（万元）", "省份", "地市", "区县", "累计发行额（亿元）", "发行期限（年）", "发行利率（%）", "发行时间"}

	line3++
	//设置第一行title
	_ = xlsx.SetSheetRow(sheet3, fmt.Sprintf("%s%d", "A", line3), &titles3)
	//
	for _, v := range project_lists {
		//处理数据到文件
		line3++
		val := []interface{}{}
		for _, vv := range keys3 {
			val = append(val, v[vv])
		}
		err := xlsx.SetSheetRow(sheet3, fmt.Sprintf("%s%d", "A", line3), &val)
		if err != nil {
			log.Error("exportData；Error ", zap.Error(err))
			return
		}
		_ = xlsx.SetCellStyle(sheet3, fmt.Sprintf("%s%d", "A", line3), "BA"+strconv.Itoa(line3), styleOne)
	}
	log.Info("exportData", zap.Any("项目列表数据处理完毕,总数是:", len(project_lists)))

	//4. 项目债券关系
	/**
	SELECT d.*
	FROM zxz_project_issue_details d
	JOIN zxz_project_base_info p ON d.project_name = p.project_name
	WHERE p.area = '天津市';
	*/
	sheet4 := "项目债券关系"
	xlsx.NewSheet(sheet4)
	line4 := 0
	keys4 := []string{"project_name", "project_bach_name", "bond_name", "first_publish_date", "batch_num", "present_issue_amount", "issue_interest_rate", "total_issue_amount"}
	titles4 := []string{"项目名称", "项目批次名称", "债券名称", "发布时间", "批次", "发行额", "发行利率", "累计发行金额"}

	line4++
	//设置第一行title
	_ = xlsx.SetSheetRow(sheet4, fmt.Sprintf("%s%d", "A", line4), &titles4)
	// 查询天津市的债券数据
	var issues = make([]map[string]interface{}, 0)
	err = MysqlDB.Table("zxz_project_issue_details d").
		Select("d.*").
		Joins("JOIN zxz_project_base_info p ON d.project_name = p.project_name ").
		Where("p.area =  ?  AND p.create_date >= ? ", area, start).
		Find(&issues).Error
	for _, v := range issues {
		//处理数据到文件
		line4++
		val := []interface{}{}
		for _, vv := range keys4 {
			val = append(val, v[vv])
		}
		err := xlsx.SetSheetRow(sheet4, fmt.Sprintf("%s%d", "A", line4), &val)
		if err != nil {
			log.Error("exportData；Error ", zap.Error(err))
			return
		}
		_ = xlsx.SetCellStyle(sheet4, fmt.Sprintf("%s%d", "A", line4), "BA"+strconv.Itoa(line4), styleOne)
	}

	//
	//保存文件
	xlsx.Path = exportFile
	xlsx.Save()
	log.Info("exportData", zap.Any("项目债券关系处理完毕,总数是:", len(issues)))
}