zmkgC/test/excel_test.go

package test

import (
	"context"
	"encoding/json"
	"fmt"
	"strconv"
	"strings"
	"testing"

	"github.com/samber/lo"
	"github.com/tiger1103/gfast/v3/api/v1/system"
	"github.com/tiger1103/gfast/v3/internal/app/system/dao"
	"github.com/tiger1103/gfast/v3/internal/app/system/model"
	"github.com/tiger1103/gfast/v3/internal/app/system/model/entity"
	"github.com/tiger1103/gfast/v3/third/excel"
	"github.com/xuri/excelize/v2"
)

// 测试 Excel 解析数据
func TestExcel(t *testing.T) {
	fileName := "test.xlsx"

	f, err := excelize.OpenFile(fileName)
	if err != nil {
		t.Fatal(err)
	}

	defer func() {
		if err := f.Close(); err != nil {
			t.Fatal(err)
		}
	}()

	rows, err := f.GetRows("方阵统计分析")
	if err != nil {
		t.Fatal(err)
	}

	type group struct {
		Name             string
		MatrixNumber     string
		NominalTiltAngle string
	}

	groups := map[string][]group{}
	// groups := []group{}

	for _, row := range rows[1:] {
		// Matrix Name
		matrixName := row[0]
		// Nominal Tilt Angle
		nominalTiltAngle := row[2]
		// Nominal Azimuth Angle
		nominalAzimuthAngle := row[3]

		// 对 matrixNumber 拆分取出第一位
		a := strings.ReplaceAll(strings.Split(matrixName, ".")[0], "G", "T")

		groups[a] = append(groups[a], group{
			Name:             matrixName,
			MatrixNumber:     nominalTiltAngle,
			NominalTiltAngle: nominalAzimuthAngle,
		})
		// groups = append(groups, group{
		// 	Name:             matrixName,
		// 	MatrixNumber:     nominalTiltAngle,
		// 	NominalTiltAngle: nominalAzimuthAngle,
		// })
	}

	for _, v := range groups {
		fmt.Println(v)
	}
}

// 测试导出 Excel
func TestExportExcel(t *testing.T) {
	fangZhenID := "1964"
	startAt := "2023-12-05"
	endAt := "2024-03-22"

	list := exportExcel(fangZhenID)
	// 打开
	f, err := excelize.OpenFile("test.xlsx")
	if err != nil {
		t.Fatal(err)
	}

	sheetName := "Sheet1"

	headerPrefixes := []string{"一、", "二、", "三、", "四、", "五、", "六、"}
	childPrefixes := []string{"1、", "2、", "3、", "4、", "5、", "6、", "7、", "8、", "9、", "10、", "11、", "12、"}

	// 修改表头
	f.SetCellValue(sheetName, "A1", fmt.Sprintf("%s (%s - %s)", fangZhenID, startAt, endAt))

	// 遍历 list
	rowIndex := 3
	lo.ForEach(list, func(item *model.WorkStatusProgressRes, index int) {
		setExcelValues(f, sheetName, rowIndex, item, headerPrefixes[index])
		rowIndex++

		// 遍历子集
		lo.ForEach(item.Children, func(child model.WorkStatusProgressRes, childIndex int) {
			setExcelValues(f, sheetName, rowIndex, &child, childPrefixes[childIndex])
			rowIndex++
		})
	})

	if err := f.SaveAs("asd.xlsx"); err != nil {
		t.Fatal(err)
	}
}

func setExcelValues(f *excelize.File, sheetName string, i int, item *model.WorkStatusProgressRes, prefix string) {
	// 单元格 A
	f.SetCellValue(sheetName, fmt.Sprintf("A%d", i), prefix+item.Name) // 父名
	f.SetCellValue(sheetName, fmt.Sprintf("B%d", i), item.Total)       // 总量
	f.SetCellValue(sheetName, fmt.Sprintf("C%d", i), item.Finished)    // 完成

	var percentComplete float64
	if item.Total != 0 {
		percentComplete = float64(item.Finished) / float64(item.Total) * 100
	} else {
		percentComplete = 0
	}
	f.SetCellValue(sheetName, fmt.Sprintf("D%d", i), fmt.Sprintf("%.2f%%", percentComplete)) // 总量完成百分比

	if item.PlanProgress != nil {
		total := 0
		if item.PlanProgress.Total != nil {
			total = *item.PlanProgress.Total
		}
		f.SetCellValue(sheetName, fmt.Sprintf("E%d", i), total) // 计划量

		finished := 0
		if item.PlanProgress.Finished != nil {
			finished = *item.PlanProgress.Finished
		}
		f.SetCellValue(sheetName, fmt.Sprintf("F%d", i), finished) // 计划完成量

		var plannedPercentComplete float64
		if item.PlanProgress != nil && item.PlanProgress.Finished != nil && item.PlanProgress.Total != nil && *item.PlanProgress.Total != 0 {
			plannedPercentComplete = float64(*item.PlanProgress.Finished) / float64(*item.PlanProgress.Total) * 100
		} else {
			plannedPercentComplete = 0
		}

		f.SetCellValue(sheetName, fmt.Sprintf("G%d", i), fmt.Sprintf("%.2f%%", plannedPercentComplete)) // 计划完成百分比
	}
}

func exportExcel(fangzhenID string) []*model.WorkStatusProgressRes {
	startAt := "2023-12-05"
	endAt := "2024-03-22"

	type scheduler struct {
		// 实体
		WorkStatus *entity.WorkSchedule
		// 总进度
		FinishedPtr int
		// 总量
		Total int
	}

	type Schdule map[string]scheduler

	Get := func(scheduleMap Schdule, workID string) (int, int) {
		if scheduler, ok := scheduleMap[workID]; ok {
			return scheduler.FinishedPtr, scheduler.Total
		}
		return 0, 0
	}

	ctx := context.Background()

	// 获取指定方阵ID的所有计划
	ScheduleData := []entity.WorkSchedule{}
	if err := dao.WorkSchedule.Ctx(ctx).Where(dao.WorkSchedule.Columns().FangzhenId, fangzhenID).
		Scan(&ScheduleData); err != nil {
		return nil
	}

	scheduleMap := make(Schdule)
	// 遍历 ScheduleData 将数据按照 WorkID 分组
	lo.ForEach(ScheduleData, func(item entity.WorkSchedule, _ int) {
		bb := item.StartAt.Format("Y-m-d")
		if bb > startAt {
			return
		}

		// 如果不存在则直接添加
		_, ok := scheduleMap[item.WorkId]
		if !ok {
			scheduleMap[item.WorkId] = scheduler{
				WorkStatus: &item,
			}
		}

		// 反序列化 item.Detail 字段
		var scheduleDetails []model.WorkScheduleDetail
		if err := json.Unmarshal([]byte(item.Detail), &scheduleDetails); err != nil {
			return
		}

		lo.ForEach(scheduleDetails, func(detail model.WorkScheduleDetail, _ int) {
			// 如果 Date 大于开始时间并小于结束时间
			if detail.Date <= endAt {
				scheduler := scheduleMap[item.WorkId]
				scheduler.Total += detail.PlanNum
				scheduler.FinishedPtr += detail.FinishedNum
				scheduleMap[item.WorkId] = scheduler
			}
		})
	})

	// 获取指定方阵ID的所有项目
	workStatusList := []entity.WorkStatus{}
	if err := dao.WorkStatus.Ctx(ctx).Where(dao.WorkStatus.Columns().FangzhenId, fangzhenID).Scan(&workStatusList); err != nil {
		return nil
	}

	// 父列表
	parentList := []entity.WorkStatus{}
	// 子列表，使用 map 存储，键为父元素的 ID
	childrenMap := make(map[int][]entity.WorkStatus)

	// 遍历所有数据，将父级和子集分开
	for i := 0; i < len(workStatusList); i++ {
		item := workStatusList[i]
		if item.Parent == 0 {
			parentList = append(parentList, item)
		} else {
			childrenMap[item.Parent] = append(childrenMap[item.Parent], item)
		}
	}

	index := 1

	projectList := make([]*model.WorkStatusProgressRes, 0, len(parentList))
	// 遍历父级，将子集添加到父级的字段中
	for _, parent := range parentList {
		projectItem := model.WorkStatusProgressRes{
			ID:       index,
			WorkID:   parent.WorkId,
			Startat:  parent.StartAt.String(),
			Endat:    parent.EndAt.String(),
			Name:     parent.WorkName,
			Total:    parent.Total,
			Finished: parent.Finished,
			Status:   parent.Status,
			WorkType: parent.Type,
			Children: make([]model.WorkStatusProgressRes, 0, len(workStatusList)),
		}
		index++

		// 子类的总量和完成量
		var subTotal, subDone int

		// 从 map 中获取子元素，将子元素添加到父元素的字段中
		for _, child := range childrenMap[int(parent.Id)] {
			// 如果计划数量等于实际数量，且原状态非3，则更新状态为2
			if child.Total == child.Finished && child.Status != 3 {
				// 并且 total,finished 不为 0
				if child.Total != 0 && child.Finished != 0 {
					child.Status = 2
				}
			}

			// 根据 WorkID 获取计划总量和实际进度
			finishedPtr, total := Get(scheduleMap, child.WorkId)

			subTotal += child.Total
			subDone += child.Finished

			projectItem.Children = append(projectItem.Children, model.WorkStatusProgressRes{
				ID:        index,
				WorkID:    child.WorkId,
				Startat:   child.StartAt.String(),
				Endat:     child.EndAt.String(),
				Name:      child.WorkName,
				Total:     child.Total,
				Finished:  child.Finished,
				IsPercent: child.IsPercent,
				Status:    child.Status,
				WorkType:  child.Type,
				PlanProgress: &model.PlanProgress{
					Finished: &finishedPtr,
					Total:    &total,
				},
			})
			index++
		}

		// 修改父项目的总量和进度
		projectItem.Total = subTotal
		projectItem.Finished = subDone

		projectList = append(projectList, &projectItem)
	}

	return projectList
}

// 获取整个子项目的
func getChildren(subProjectId int) []*model.WorkStatusProgressRes {
	startAt := "2023-12-05"
	endAt := "2024-03-22"

	type scheduler struct {
		// 实体
		WorkStatus *entity.WorkSchedule
		// 总进度
		FinishedPtr int
		// 总量
		Total int
	}

	type Schdule map[string]scheduler

	Get := func(scheduleMap Schdule, workID string) (int, int) {
		if scheduler, ok := scheduleMap[workID]; ok {
			return scheduler.FinishedPtr, scheduler.Total
		}
		return 0, 0
	}

	ctx := context.Background()

	// 获取指定小项目的所有计划
	ScheduleData := []entity.WorkSchedule{}
	if err := dao.WorkSchedule.Ctx(ctx).Where(dao.WorkSchedule.Columns().SubProjectid, subProjectId).
		Scan(&ScheduleData); err != nil {
		return nil
	}

	scheduleMap := make(Schdule)
	// 遍历 ScheduleData 将数据按照 WorkID 分组
	lo.ForEach(ScheduleData, func(item entity.WorkSchedule, _ int) {
		bb := item.StartAt.Format("Y-m-d")
		if bb > startAt {
			return
		}

		// 如果不存在则直接添加
		_, ok := scheduleMap[item.WorkId]
		if !ok {
			scheduleMap[item.WorkId] = scheduler{
				WorkStatus: &item,
			}
		}

		// 反序列化 item.Detail 字段
		var scheduleDetails []model.WorkScheduleDetail
		if err := json.Unmarshal([]byte(item.Detail), &scheduleDetails); err != nil {
			return
		}

		lo.ForEach(scheduleDetails, func(detail model.WorkScheduleDetail, _ int) {
			// 如果 Date 大于开始时间并小于结束时间
			if detail.Date <= endAt {
				scheduler := scheduleMap[item.WorkId]
				scheduler.Total += detail.PlanNum
				scheduler.FinishedPtr += detail.FinishedNum
				scheduleMap[item.WorkId] = scheduler
			}
		})
	})

	// 获取指定方阵ID的所有方阵数据
	workStatusList := []entity.WorkStatus{}
	if err := dao.WorkStatus.Ctx(ctx).Where(dao.WorkStatus.Columns().SubProjectid, subProjectId).Scan(&workStatusList); err != nil {
		return nil
	}

	// 父列表
	parentList := []entity.WorkStatus{}
	// 子列表，使用 map 存储，键为父元素的 ID
	childrenMap := make(map[int][]entity.WorkStatus)

	// 遍历所有数据，将父级和子集分开
	for i := 0; i < len(workStatusList); i++ {
		item := workStatusList[i]
		if item.Parent == 0 {
			parentList = append(parentList, item)
		} else {
			childrenMap[item.Parent] = append(childrenMap[item.Parent], item)
		}
	}

	index := 1

	projectList := make([]*model.WorkStatusProgressRes, 0, len(parentList))
	// 遍历父级，将子集添加到父级的字段中
	for _, parent := range parentList {
		projectItem := model.WorkStatusProgressRes{
			ID:       index,
			WorkID:   parent.WorkId,
			Startat:  parent.StartAt.String(),
			Endat:    parent.EndAt.String(),
			Name:     parent.WorkName,
			Total:    parent.Total,
			Finished: parent.Finished,
			Status:   parent.Status,
			WorkType: parent.Type,
			Children: make([]model.WorkStatusProgressRes, 0, len(workStatusList)),
		}
		index++

		// 子类的总量和完成量
		var subTotal, subDone int

		// 从 map 中获取子元素，将子元素添加到父元素的字段中
		for _, child := range childrenMap[int(parent.Id)] {
			// 如果计划数量等于实际数量，且原状态非3，则更新状态为2
			if child.Total == child.Finished && child.Status != 3 {
				// 并且 total,finished 不为 0
				if child.Total != 0 && child.Finished != 0 {
					child.Status = 2
				}
			}

			// 根据 WorkID 获取计划总量和实际进度
			finishedPtr, total := Get(scheduleMap, child.WorkId)

			subTotal += child.Total
			subDone += child.Finished

			projectItem.Children = append(projectItem.Children, model.WorkStatusProgressRes{
				ID:        index,
				WorkID:    child.WorkId,
				Startat:   child.StartAt.String(),
				Endat:     child.EndAt.String(),
				Name:      child.WorkName,
				Total:     child.Total,
				Finished:  child.Finished,
				IsPercent: child.IsPercent,
				Status:    child.Status,
				WorkType:  child.Type,
				PlanProgress: &model.PlanProgress{
					Finished: &finishedPtr,
					Total:    &total,
				},
			})
			index++
		}

		// 修改父项目的总量和进度
		projectItem.Total = subTotal
		projectItem.Finished = subDone

		projectList = append(projectList, &projectItem)
	}

	return projectList
}

// 测试子项目
func Test_subProject(t *testing.T) {
	// 根据子项目ID获取所有的方阵
	subProjectID := 23

	fangzhenList := []entity.QianqiFangzhen{}
	if err := dao.QianqiFangzhen.Ctx(context.Background()).
		Where(dao.QianqiFangzhen.Columns().ProjectId, subProjectID).Scan(&fangzhenList); err != nil {
		t.Fatal(err)
	}

	cumulativeProjects := []*model.WorkStatusProgressRes{}

	// 用于判断是否为第一次迭代
	firstTime := true

	// 遍历项目列表
	for index := 0; index < len(fangzhenList); index++ {
		// 获取当前项目
		project := fangzhenList[index]

		// 获取当前项目的子项目
		childProjects := exportExcel(strconv.Itoa(project.Id))

		// 如果是第一次迭代，将子项目列表赋值给累积项目列表
		if firstTime {
			cumulativeProjects = childProjects
			// 更新标志变量，表示已经不是第一次迭代
			firstTime = false
			continue
		}

		// 遍历子项目列表
		for childIndex := 0; childIndex < len(childProjects); childIndex++ {
			// 获取当前子项目和对应的累积项目
			singleChild := childProjects[childIndex]
			cumulativeChild := cumulativeProjects[childIndex]

			// 更新累积项目的总数和完成数
			cumulativeChild.Total += singleChild.Total
			cumulativeChild.Finished += singleChild.Finished

			// 如果当前子项目还有子项目，也进行同样的操作
			if len(singleChild.Children) > 0 {
				for subChildIndex := 0; subChildIndex < len(singleChild.Children); subChildIndex++ {
					// 获取当前子项目的子项目和对应的累积项目
					singleSubChild := singleChild.Children[subChildIndex]
					cumulativeSubChild := cumulativeChild.Children[subChildIndex]

					// 更新累积项目的总数和完成数
					cumulativeSubChild.Total += singleSubChild.Total
					cumulativeSubChild.Finished += singleSubChild.Finished
				}
			}
		}
	}

	// 序列化 cumulativeProjects
	data, err := json.Marshal(cumulativeProjects)
	if err != nil {
		t.Fatal(err)
	}

	fmt.Println(string(data))
}

// 根据方阵ID获取子项目名
func Test_GetSubName(t *testing.T) {
	excel.GetSubProjectName("1959")
}

// 测试导出
func TestExport(t *testing.T) {
	req := system.WorkScheduleExportWeekReq{
		FangzhenID: "1964",
		StartTime:  "2023-09-07",
		EndTime:    "2024-10-22",
		ProjectID:  "45",
	}

	ctx := context.Background()

	var (
		list []*model.WorkStatusProgressRes
		err  error

		projectName, subProjectName string

		excelMap = make(map[string]excel.ExcelInfo)
	)

	// 导出指定方阵的情况下
	if req.FangzhenID != "" {
		// 获取方阵下的所有工作计划
		list, excelMap, err = excel.ExportExcel(req.FangzhenID, req.StartTime, req.EndTime)
		if err != nil {
			t.Fatal(err)
		}
		// 子项目名，方阵名
		projectName, subProjectName = excel.GetSubProjectName(req.FangzhenID)
	} else {
		// 导出一个子项目的情况
		list, excelMap, err = excel.AccumulateProject(req.SubProjectID, req.StartTime, req.EndTime)
		if err != nil {
			t.Fatal(err)
		}
		// 大项目名，子项目名
		projectName, subProjectName = excel.GetNameById(req.SubProjectID)
	}

	_, _, _ = projectName, subProjectName, list

	_ = excelMap
	// f, err := excelize.OpenFile("../resource/book.xlsx")
	// if err != nil {
	// 	t.Fatal(err)
	// }

	f, err := excel.CreateExcelTemplate()
	if err != nil {
		t.Fatal(err)
	}

	// 设置表头 k2 日期
	// startat - endat
	f.SetCellValue("Sheet1", "K2", fmt.Sprintf("%s - %s", req.StartTime, req.EndTime))

	// 获取周数
	week, err := excel.GetWeekNumbers(req.EndTime)
	if err != nil {
		t.Fatal(err)
	}

	// i2 周数
	f.SetCellValue("Sheet1", "I2", week)

	// D4 开工日期: 2023-03-20
	projectInfo := entity.SysProject{}
	err = dao.SysProject.Ctx(ctx).Where(dao.SysProject.Columns().Id, req.ProjectID).Scan(&projectInfo)
	if err != nil {
		t.Fatal(err)
	}

	// A1 项目名称 + "周报"
	f.SetCellValue("Sheet1", "A1", projectInfo.ProjectName+"周报")

	// C2 项目名称
	f.SetCellValue("Sheet1", "C2", projectInfo.ProjectName)

	f.SetCellValue("Sheet1", "D4", "开工日期："+projectInfo.OnStreamTime.Format("Y-m-d"))

	sheetName := "Sheet1"
	index := 10
	for i := 0; i < len(list); i++ {
		postionName := list[i].Name

		for j := 0; j < len(list[i].Children); j++ {
			setExcelValue(f, sheetName, index, list[i].Children[j], excelMap, postionName)
			// 如果 C 的下一格为 "设备材料名称" 则新增一行
			if lo.Must(f.GetCellValue(sheetName, fmt.Sprintf("C%d", index+1))) == "设备材料名称" && index+1 != 38 {
				f.InsertRows(sheetName, index+1, 1)
			}

			index++
		}
	}

	// A9 ,B9 向下合并到 index
	_ = f.MergeCell(sheetName, "A9", fmt.Sprintf("A%d", index-1))
	f.SetCellStyle(sheetName, "A9", fmt.Sprintf("A%d", index-1), lo.Must(f.NewStyle(&GlobalStyle)))
	_ = f.MergeCell(sheetName, "B9", fmt.Sprintf("B%d", index-1))
	f.SetCellStyle(sheetName, "B9", fmt.Sprintf("B%d", index-1), lo.Must(f.NewStyle(&GlobalStyle)))

	if err := f.SaveAs("test.xlsx"); err != nil {
		t.Fatal(err)
	}
}

var GlobalStyle = excelize.Style{
	// 边框
	Border: []excelize.Border{
		{Type: "left", Color: "000000", Style: 1},
		{Type: "top", Color: "000000", Style: 1},
		{Type: "bottom", Color: "000000", Style: 1},
		{Type: "right", Color: "000000", Style: 1},
	},
	// 居中
	Alignment: &excelize.Alignment{
		Vertical:   "center", // 上下居中
		Horizontal: "center", // 左右居中
		WrapText:   true,     // 自动换行
	},
}

func CalculateTotalProgress(list []*model.WorkStatusProgressRes) float64 {
	var totalFinished, total int

	var calculate func(w model.WorkStatusProgressRes)
	calculate = func(w model.WorkStatusProgressRes) {
		totalFinished += w.Finished
		total += w.Total
		for _, child := range w.Children {
			calculate(child)
		}
	}

	for _, item := range list {
		calculate(*item)
	}

	if total == 0 {
		return 0
	}

	return (float64(totalFinished) / float64(total)) * 100
}

// Test_ExportWeek 导出周报
func Test_ExportWeek(t *testing.T) {
	req := system.WorkScheduleExportWeekReq{
		ProjectID:  "45",
		FangzhenID: "1964",
		StartTime:  "2023-09-07",
		EndTime:    "2024-10-22",
	}

	var (
		list []*model.WorkStatusProgressRes
		err  error

		projectName, subProjectName string

		excelMap = make(map[string]excel.ExcelInfo)
	)

	// 判断传入的方阵或子项目是否为空
	// if !service.WorkSchedule().IsExistWorkSchedule(ctx, req.SubProjectID, req.FangzhenID, req.StartTime, req.EndTime) {
	// 	list = []*model.WorkStatusProgressRes{}
	// }

	// 导出指定方阵的情况下
	if req.FangzhenID != "" {
		// 获取方阵下的所有工作计划
		list, excelMap, err = excel.ExportExcel(req.FangzhenID, req.StartTime, req.EndTime)
		if err != nil {
			t.Fatal(err)
		}

		// 子项目名，方阵名
		projectName, subProjectName = excel.GetSubProjectName(req.FangzhenID)
	} else {
		// 导出一个子项目的情况
		list, excelMap, err = excel.AccumulateProject(req.SubProjectID, req.StartTime, req.EndTime)
		if err != nil {
			t.Fatal(err)
		}

		// 大项目名，子项目名
		projectName, subProjectName = excel.GetNameById(req.SubProjectID)
	}

	_, _, _ = projectName, subProjectName, list

	// dd, _ := json.Marshal(list)
	// fmt.Println(string(dd))

	ddd := CalculateTotalProgress(list)

	fmt.Println(ddd)

	// 创建 Excel 模板
	f, err := excel.CreateExcelTemplate()
	if err != nil {
		t.Fatal(err)
	}

	sheetName := "Sheet1"
	index := 10

	for _, item := range list {
		postionName := item.Name

		for _, child := range item.Children {
			excel.SetExcelValue(f, sheetName, index, child, excelMap, postionName)

			if lo.Must(f.GetCellValue(sheetName, fmt.Sprintf("C%d", index+1))) == "设备材料名称" && index+1 != 38 {
				f.InsertRows(sheetName, index+1, 1)
			}

			index++
		}
	}

	// A9 ,B9 向下合并到 index
	mergeAndStyle := func(col string) {
		cellRange := fmt.Sprintf("%s9", col)
		_ = f.MergeCell(sheetName, cellRange, fmt.Sprintf("%s%d", col, index-1))
		f.SetCellStyle(sheetName, cellRange, fmt.Sprintf("%s%d", col, index-1), lo.Must(f.NewStyle(excel.Style)))
	}

	mergeAndStyle("A")
	mergeAndStyle("B")

	// 材料
	equipmentMaterials, err := excel.GetEquipmentMaterials(req.ProjectID, req.StartTime, req.EndTime)
	if err != nil {
		t.Fatal(err)
	}

	index = 39 // 从第 39 行开始

	for _, item := range equipmentMaterials {

		// 如果 B + index 为 "存在问题及需要协调的事项" 则新增一行
		if lo.Must(f.GetCellValue(sheetName, fmt.Sprintf("B%d", index))) == "存在问题及需要协调的事项" {
			f.InsertRows(sheetName, index, 1)
		}

		f.SetCellValue(sheetName, fmt.Sprintf("C%d", index), item.EquipmentMaterialsName) // 设备材料名称
		f.SetCellStyle(sheetName, fmt.Sprintf("C%d", index), fmt.Sprintf("C%d", index), lo.Must(f.NewStyle(excel.Style)))

		f.SetCellStyle(sheetName, fmt.Sprintf("D%d", index), fmt.Sprintf("D%d", index), lo.Must(f.NewStyle(excel.Style)))
		f.SetCellStyle(sheetName, fmt.Sprintf("E%d", index), fmt.Sprintf("E%d", index), lo.Must(f.NewStyle(excel.Style)))

		f.SetCellValue(sheetName, fmt.Sprintf("F%d", index), item.TotalQuantityCount) // 设计数量
		f.SetCellStyle(sheetName, fmt.Sprintf("F%d", index), fmt.Sprintf("F%d", index), lo.Must(f.NewStyle(excel.Style)))

		f.SetCellValue(sheetName, fmt.Sprintf("G%d", index), item.TotalNumber) // 本周
		f.SetCellStyle(sheetName, fmt.Sprintf("G%d", index), fmt.Sprintf("G%d", index), lo.Must(f.NewStyle(excel.Style)))

		f.SetCellValue(sheetName, fmt.Sprintf("H%d", index), item.CumulativeArrivalQuantity) // 累计到货数量
		f.SetCellStyle(sheetName, fmt.Sprintf("H%d", index), fmt.Sprintf("H%d", index), lo.Must(f.NewStyle(excel.Style)))

		// 累计到货率
		cumulativeArrivalRate := 0.0
		if item.TotalQuantityCount != 0 {
			cumulativeArrivalRate = float64(item.CumulativeArrivalQuantity) / float64(item.TotalQuantityCount)
		}
		f.SetCellValue(sheetName, fmt.Sprintf("I%d", index), cumulativeArrivalRate)
		f.SetCellStyle(sheetName, fmt.Sprintf("I%d", index), fmt.Sprintf("I%d", index), lo.Must(f.NewStyle(excel.Style)))

		// 合并 jk 单元格
		_ = f.MergeCell(sheetName, fmt.Sprintf("J%d", index), fmt.Sprintf("K%d", index))
		f.SetCellStyle(sheetName, fmt.Sprintf("J%d", index), fmt.Sprintf("K%d", index), lo.Must(f.NewStyle(excel.Style)))

		// 水平居中 jk 单元格
		f.SetCellStyle(sheetName, fmt.Sprintf("J%d", index), fmt.Sprintf("K%d", index), lo.Must(f.NewStyle(&excelize.Style{
			Border: []excelize.Border{
				{Type: "left", Color: "000000", Style: 1},
				{Type: "top", Color: "000000", Style: 1},
				{Type: "bottom", Color: "000000", Style: 1},
				{Type: "right", Color: "000000", Style: 1},
			},
			Fill: excelize.Fill{},
			Font: nil,
			Alignment: &excelize.Alignment{
				Horizontal:      "center", // 水平居中
				Indent:          0,
				JustifyLastLine: false,
				ReadingOrder:    0,
				RelativeIndent:  0,
				ShrinkToFit:     false,
				TextRotation:    0,
				Vertical:        "", // 垂直居中
				WrapText:        false,
			},
		})))

		f.SetCellStyle(sheetName, fmt.Sprintf("%s%d", "L", index), fmt.Sprintf("%s%d", "L", index), lo.Must(f.NewStyle(excel.Style)))
		index++
	}

	// 合并 A38 和 B38 至 index
	f.MergeCell(sheetName, "A38", fmt.Sprintf("A%d", index-1))
	f.SetCellStyle(sheetName, "A38", fmt.Sprintf("A%d", index-1), lo.Must(f.NewStyle(excel.Style)))

	f.MergeCell(sheetName, "B38", fmt.Sprintf("B%d", index-1))
	f.SetCellStyle(sheetName, "B38", fmt.Sprintf("B%d", index-1), lo.Must(f.NewStyle(excel.Style)))

	// j4 总进度完成率
	f.SetCellValue(sheetName, "J4", excel.CalculateTotalProgress(list))

	if err := f.SaveAs("test.xlsx"); err != nil {
		t.Fatal(err)
	}
}

// 材料到货情况
func Test_Materials(t *testing.T) {
	// SELECT
	//   bem.equipment_materials_name,
	//   SUM(bmi.number) AS total_number,
	//   SUM(bem.quantity_count) AS total_quantity_count,
	//   (
	//     SELECT
	//       SUM(bi.number)
	//     FROM
	//       `bus_equipment_materials_inventory` bi
	//     WHERE
	//       bi.equipment_materials_id = bmi.equipment_materials_id
	//       AND bi.deleted_at IS NULL
	//       AND bi.out_put = 2
	//   ) AS cumulative_arrival_quantity
	// FROM
	//   `bus_equipment_materials_inventory` AS bmi
	//   LEFT JOIN `bus_equipment_materials` bem ON (
	//     bmi.equipment_materials_id = bem.equipment_materials_id
	//   )
	// WHERE
	//   bem.project_id = 45
	//   AND bmi.out_put = 2
	//   AND bmi.created_at >= '2022-09-07'
	//   AND bmi.created_at <= '2024-10-22'
	//   AND bmi.deleted_at IS NULL
	//   AND bem.deleted_at IS NULL
	// GROUP BY
	//   bem.equipment_materials_name;

	var list []struct {
		EquipmentMaterialsName    string `json:"equipment_materials_name"`    // 设备材料名称
		TotalNumber               int    `json:"total_number"`                // 本周到货量
		TotalQuantityCount        int    `json:"total_quantity_count"`        // 设计数量
		CumulativeArrivalQuantity int    `json:"cumulative_arrival_quantity"` // 累计到货量
	}

	startAT := "2022-09-07"
	endAT := "2024-10-22"

	// 查询
	if err := dao.BusEquipmentMaterialsInventory.Ctx(context.Background()).As("bmi").
		Fields("bem.equipment_materials_name, SUM(bmi.number) AS total_number, SUM(bem.quantity_count) AS total_quantity_count, (SELECT SUM(bi.number) FROM `bus_equipment_materials_inventory` bi WHERE bi.equipment_materials_id = bmi.equipment_materials_id AND bi.deleted_at IS NULL AND bi.out_put = 2) AS cumulative_arrival_quantity").
		LeftJoin("bus_equipment_materials bem", "bmi.equipment_materials_id = bem.equipment_materials_id").
		Where("bem.project_id = ?", 45).
		Where("bmi.out_put = ?", 2).
		Where("bmi.created_at >= ?", startAT).
		Where("bmi.created_at <= ?", endAT).
		Group("bem.equipment_materials_name").
		Scan(&list); err != nil {
		t.Fatal(err)
	}

	// // C39

	// index := 39

	// for _, item := range list {
	// 	// 设备材料名称
	// 	equipmentMaterialsName := item.EquipmentMaterialsName
	// 	// 设计数量
	// 	totalQuantityCount := item.TotalQuantityCount
	// 	// 本周到货量
	// 	totalNumber := item.TotalNumber
	// 	// 累计到货量
	// 	cumulativeArrivalQuantity := item.CumulativeArrivalQuantity
	// 	// 累计到货率
	// 	cumulativeArrivalRate := 0.0
	// 	if totalQuantityCount != 0 {
	// 		cumulativeArrivalRate = float64(cumulativeArrivalQuantity) / float64(totalQuantityCount) * 100
	// 	}

	// }
}

func setExcelValue(f *excelize.File, sheetName string, index int, item model.WorkStatusProgressRes, excelmap map[string]excel.ExcelInfo, postionName string) {
	// 设计数量 F10
	total := item.Total
	// 本周完成量 G10
	finished := item.Finished

	// 累计完成量和下周计划量
	accumulated, nextWeek := 0, 0

	if data, exists := excelmap[item.WorkID]; exists {
		accumulated = data.Accumulated
		nextWeek = data.NextWeek
	}

	// 累计完成百分比 单元格 I10
	percentComplete := 0.0
	if total != 0 {
		percentComplete = float64(accumulated) / float64(total) * 100
	}

	// 名称
	f.SetCellValue(sheetName, fmt.Sprintf("%s%d", "C", index), item.Name)
	f.SetCellStyle(sheetName, fmt.Sprintf("%s%d", "C", index), fmt.Sprintf("%s%d", "C", index), lo.Must(f.NewStyle(&GlobalStyle)))

	// 位置
	f.SetCellValue(sheetName, fmt.Sprintf("%s%d", "D", index), postionName)
	f.SetCellStyle(sheetName, fmt.Sprintf("%s%d", "D", index), fmt.Sprintf("%s%d", "D", index), lo.Must(f.NewStyle(&GlobalStyle)))

	// 单位 样式
	f.SetCellStyle(sheetName, fmt.Sprintf("%s%d", "E", index), fmt.Sprintf("%s%d", "E", index), lo.Must(f.NewStyle(&GlobalStyle)))

	// 设计数量
	f.SetCellValue(sheetName, fmt.Sprintf("%s%d", "F", index), total)
	f.SetCellStyle(sheetName, fmt.Sprintf("%s%d", "F", index), fmt.Sprintf("%s%d", "F", index), lo.Must(f.NewStyle(&GlobalStyle)))

	// 本周
	f.SetCellValue(sheetName, fmt.Sprintf("%s%d", "G", index), finished)
	f.SetCellStyle(sheetName, fmt.Sprintf("%s%d", "G", index), fmt.Sprintf("%s%d", "G", index), lo.Must(f.NewStyle(&GlobalStyle)))

	// 累计完成量
	f.SetCellValue(sheetName, fmt.Sprintf("%s%d", "H", index), accumulated)
	f.SetCellStyle(sheetName, fmt.Sprintf("%s%d", "H", index), fmt.Sprintf("%s%d", "H", index), lo.Must(f.NewStyle(&GlobalStyle)))

	// 累计完成百分比
	f.SetCellValue(sheetName, fmt.Sprintf("%s%d", "I", index), fmt.Sprintf("%.f%%", percentComplete))
	f.SetCellStyle(sheetName, fmt.Sprintf("%s%d", "I", index), fmt.Sprintf("%s%d", "I", index), lo.Must(f.NewStyle(&GlobalStyle)))

	// 合并 jk 单元格
	_ = f.MergeCell(sheetName, fmt.Sprintf("J%d", index), fmt.Sprintf("K%d", index))
	f.SetCellStyle(sheetName, fmt.Sprintf("J%d", index), fmt.Sprintf("K%d", index), lo.Must(f.NewStyle(&GlobalStyle)))

	// 水平居中 jk 单元格
	f.SetCellStyle(sheetName, fmt.Sprintf("J%d", index), fmt.Sprintf("K%d", index), lo.Must(f.NewStyle(&excelize.Style{
		Border: []excelize.Border{
			{Type: "left", Color: "000000", Style: 1},
			{Type: "top", Color: "000000", Style: 1},
			{Type: "bottom", Color: "000000", Style: 1},
			{Type: "right", Color: "000000", Style: 1},
		},
		Fill: excelize.Fill{},
		Font: nil,
		Alignment: &excelize.Alignment{
			Horizontal:      "center", // 水平居中
			Indent:          0,
			JustifyLastLine: false,
			ReadingOrder:    0,
			RelativeIndent:  0,
			ShrinkToFit:     false,
			TextRotation:    0,
			Vertical:        "", // 垂直居中
			WrapText:        false,
		},
	})))

	// 下周计划量
	f.SetCellValue(sheetName, fmt.Sprintf("%s%d", "J", index), nextWeek)

	// L
	f.SetCellStyle(sheetName, fmt.Sprintf("%s%d", "L", index), fmt.Sprintf("%s%d", "L", index), lo.Must(f.NewStyle(&GlobalStyle)))
}