1002 lines
29 KiB
Go
1002 lines
29 KiB
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)))
|
||
}
|