月度统计报表下载接口

zhangqian

2024-06-07 e718c2568e9712ab40dc4d2d4d20cf3635dc4c34

月度统计报表下载接口

 controllers/report_forms_controller.go

@@ -8,7 +8,6 @@
    "wms/constvar"
    "wms/extend/code"
    "wms/extend/util"
    "wms/models"
    "wms/pkg/logx"
    "wms/request"
    "wms/service"
@@ -83,11 +82,6 @@
    if err != nil {
        logx.Errorf("DownloadInventoryForms Export err:%v", err)
        util.ResponseFormat(c, code.InternalError, "导出数据到文件失败")
        return
    }

    if err != nil {
        util.ResponseFormat(c, code.RequestParamError, "导出失败")
        return
    }

@@ -306,11 +300,6 @@
        return
    }

    if err != nil {
        util.ResponseFormat(c, code.RequestParamError, "导出失败")
        return
    }

    fileContentDisposition := "attachment;filename=\"" + url.QueryEscape(filename) + "\""
    c.Header("Content-Type", "application/xlsx")
    c.Header("Content-Disposition", fileContentDisposition)
@@ -333,13 +322,57 @@
        return
    }

    list, total, err := models.NewMonthStatsSearch().SetPage(params.Page, params.PageSize).SetKeyword(params.Keyword).SetDate(params.Date).Find()
    monthFormsService := service.NewMonthFormsService()
    total, err := monthFormsService.Count(params)
    if err != nil {
        logx.Errorf("MonthStats count err:%v", err)
        util.ResponseFormat(c, code.InternalError, "查询总数失败")
        return
    }
    result, err := monthFormsService.Query(params)
    if err != nil {
        logx.Errorf("MonthStats query err:%v", err)
        util.ResponseFormat(c, code.InternalError, "查询失败")
        return
    }

    util.ResponseFormatList(c, code.Success, list, int(total))
    util.ResponseFormatList(c, code.Success, result, int(total))
}

// DownloadMonthStats
// @Tags      报表
// @Summary   下载月度统计库存报表
// @Produce   application/json
// @Param     object  body  request.GetMonthStats true  "查询参数"
// @Param     Authorization    header string true "token"
// @Success   200 {object} util.ResponseList{data=[]models.MonthStats}    "成功"
// @Router    /api-wms/v1/forms/downloadMonthStats [post]
func (slf ReportFormsController) DownloadMonthStats(c *gin.Context) {
    var params request.GetMonthStats
    if err := c.BindJSON(&params); err != nil {
        util.ResponseFormat(c, code.RequestParamError, "参数解析失败，数据类型错误")
        return
    }

    monthFormsService := service.NewMonthFormsService()
    list, err := monthFormsService.FetchAll(params)
    if err != nil {
        logx.Errorf("DownloadMonthStats FetchAll err:%v", err)
        util.ResponseFormat(c, code.InternalError, "查询失败")
        return
    }
    filename, err := monthFormsService.Export(list)
    if err != nil {
        logx.Errorf("DownloadMonthStats Export err:%v", err)
        util.ResponseFormat(c, code.InternalError, "导出数据到文件失败")
        return
    }

    fileContentDisposition := "attachment;filename=\"" + url.QueryEscape(filename) + "\""
    c.Header("Content-Type", "application/xlsx")
    c.Header("Content-Disposition", fileContentDisposition)
    c.File(filename)
    defer os.Remove(filename)
}

// DoMonthStats

 docs/docs.go

@@ -624,6 +624,58 @@
                }
            }
        },
        "/api-wms/v1/forms/downloadMonthStats": {
            "post": {
                "produces": [
                    "application/json"
                ],
                "tags": [
                    "报表"
                ],
                "summary": "下载月度统计库存报表",
                "parameters": [
                    {
                        "description": "查询参数",
                        "name": "object",
                        "in": "body",
                        "required": true,
                        "schema": {
                            "$ref": "#/definitions/request.GetMonthStats"
                        }
                    },
                    {
                        "type": "string",
                        "description": "token",
                        "name": "Authorization",
                        "in": "header",
                        "required": true
                    }
                ],
                "responses": {
                    "200": {
                        "description": "成功",
                        "schema": {
                            "allOf": [
                                {
                                    "$ref": "#/definitions/util.ResponseList"
                                },
                                {
                                    "type": "object",
                                    "properties": {
                                        "data": {
                                            "type": "array",
                                            "items": {
                                                "$ref": "#/definitions/models.MonthStats"
                                            }
                                        }
                                    }
                                }
                            ]
                        }
                    }
                }
            }
        },
        "/api-wms/v1/forms/getHistory": {
            "post": {
                "produces": [

 docs/swagger.json

@@ -612,6 +612,58 @@
                }
            }
        },
        "/api-wms/v1/forms/downloadMonthStats": {
            "post": {
                "produces": [
                    "application/json"
                ],
                "tags": [
                    "报表"
                ],
                "summary": "下载月度统计库存报表",
                "parameters": [
                    {
                        "description": "查询参数",
                        "name": "object",
                        "in": "body",
                        "required": true,
                        "schema": {
                            "$ref": "#/definitions/request.GetMonthStats"
                        }
                    },
                    {
                        "type": "string",
                        "description": "token",
                        "name": "Authorization",
                        "in": "header",
                        "required": true
                    }
                ],
                "responses": {
                    "200": {
                        "description": "成功",
                        "schema": {
                            "allOf": [
                                {
                                    "$ref": "#/definitions/util.ResponseList"
                                },
                                {
                                    "type": "object",
                                    "properties": {
                                        "data": {
                                            "type": "array",
                                            "items": {
                                                "$ref": "#/definitions/models.MonthStats"
                                            }
                                        }
                                    }
                                }
                            ]
                        }
                    }
                }
            }
        },
        "/api-wms/v1/forms/getHistory": {
            "post": {
                "produces": [

 docs/swagger.yaml

@@ -2320,6 +2320,37 @@
      summary: 下载位置报表
      tags:
      - 报表
  /api-wms/v1/forms/downloadMonthStats:
    post:
      parameters:
      - description: 查询参数
        in: body
        name: object
        required: true
        schema:
          $ref: '#/definitions/request.GetMonthStats'
      - description: token
        in: header
        name: Authorization
        required: true
        type: string
      produces:
      - application/json
      responses:
        "200":
          description: 成功
          schema:
            allOf:
            - $ref: '#/definitions/util.ResponseList'
            - properties:
                data:
                  items:
                    $ref: '#/definitions/models.MonthStats'
                  type: array
              type: object
      summary: 下载月度统计库存报表
      tags:
      - 报表
  /api-wms/v1/forms/getHistory:
    post:
      parameters:

 example.xlsx

Binary files differ

 models/month_stats.go

@@ -347,3 +347,12 @@
    }
    return m
}

func (slf *MonthStatsSearch) Count() (int64, error) {
    var (
        total int64
        db    = slf.build()
    )
    err := db.Count(&total).Error
    return total, err
}

 router/router.go

@@ -160,6 +160,7 @@
        reportFormsAPI.POST("getLocationForms", reportFormsController.GetLocationForms)             //获取位置报表
        reportFormsAPI.POST("downloadLocationForms", reportFormsController.DownloadLocationForms)   //下载位置报表
        reportFormsAPI.POST("monthStats", reportFormsController.MonthStats)                         //获取月度统计报表
        reportFormsAPI.POST("downloadMonthStats", reportFormsController.DownloadMonthStats)         //下载月度统计报表
        reportFormsAPI.POST("doMonthStats", reportFormsController.DoMonthStats)                     //手动跑月度统计库存报表
    }


 service/month_forms.go

New file
@@ -0,0 +1,181 @@
package service

import (
    "fmt"
    "github.com/xuri/excelize/v2"
    "strconv"
    "time"
    "wms/models"
    "wms/pkg/logx"
    "wms/request"
)

type MonthFormsService struct{}

func NewMonthFormsService() *MonthFormsService {
    return &MonthFormsService{}
}

func (slf *MonthFormsService) Query(params request.GetMonthStats) (result []*models.MonthStats, err error) {
    search := slf.BuildSearch(params)
    search = search.SetPage(params.Page, params.PageSize)
    return search.FindNotTotal()
}

func (slf *MonthFormsService) BuildSearch(params request.GetMonthStats) (search *models.MonthStatsSearch) {
    search = models.NewMonthStatsSearch().SetKeyword(params.Keyword).SetDate(params.Date)
    return search
}

func (slf *MonthFormsService) Count(params request.GetMonthStats) (total int64, err error) {
    search := slf.BuildSearch(params)
    return search.Count()
}

func (slf *MonthFormsService) FetchAll(params request.GetMonthStats) (list []*models.MonthStats, err error) {
    total, err := slf.Count(params)
    if err != nil {
        return nil, err
    }
    list = make([]*models.MonthStats, 0, total)
    params.PageSize = 500
    page := 1
    for {
        params.Page = page
        data, err := slf.Query(params)
        if err != nil {
            return nil, err
        }
        if len(data) == 0 {
            break
        }
        list = append(list, data...)
        page++
    }
    return
}

func (slf *MonthFormsService) Export(dataList []*models.MonthStats) (filename string, err error) {
    // 创建一个新的 Excel 文件
    f := excelize.NewFile()

    if err != nil {
        logx.Errorf("NewSheet err:%v", err)
        return "", err
    }

    // 设置表头
    f.SetCellValue("Sheet1", "A1", "产品编码")
    f.SetCellValue("Sheet1", "B1", "产品名称")
    f.SetCellValue("Sheet1", "C1", "期初库存")
    f.MergeCell("Sheet1", "C1", "F1") // 合并单元格
    f.SetCellValue("Sheet1", "G1", "本月入库")
    f.MergeCell("Sheet1", "G1", "J1") // 合并单元格
    f.SetCellValue("Sheet1", "K1", "本月出库")
    f.MergeCell("Sheet1", "K1", "N1") // 合并单元格
    f.SetCellValue("Sheet1", "O1", "期末库存")
    f.MergeCell("Sheet1", "O1", "R1") // 合并单元格

    unitData := []string{"件", "匹", "米", "重量"}
    for i, header := range unitData {
        cell := getColumnAlphabet(i+3) + "2"
        f.SetCellValue("Sheet1", cell, header)
    }
    for i, header := range unitData {
        cell := getColumnAlphabet(i+7) + "2"
        f.SetCellValue("Sheet1", cell, header)
    }
    for i, header := range unitData {
        cell := getColumnAlphabet(i+11) + "2"
        f.SetCellValue("Sheet1", cell, header)
    }
    for i, header := range unitData {
        cell := getColumnAlphabet(i+15) + "2"
        f.SetCellValue("Sheet1", cell, header)
    }

    // 设置表头样式
    style := &excelize.Style{
        Border: nil,
        Fill: excelize.Fill{
            Type:    "pattern",
            Pattern: 1,
            Shading: 0,
        },
        Font: &excelize.Font{
            Bold: true,
        },
        Alignment: &excelize.Alignment{
            Horizontal: "center",
        },
    }
    titleStyle, err := f.NewStyle(style)
    if err != nil {
        fmt.Println(err)
        return
    }
    f.SetCellStyle("Sheet1", "A1", "R2", titleStyle)
    // 设置列宽
    f.SetColWidth("Sheet1", "A", "B", 30)
    f.SetColWidth("Sheet1", "C", "R", 15)

    for i, v := range dataList {
        column := strconv.Itoa(i + 3)
        f.SetCellValue("Sheet1", "A"+column, v.ProductId)
        f.SetCellValue("Sheet1", "B"+column, v.ProductName)
        f.SetCellValue("Sheet1", "C"+column, v.BeginAmount)
        for _, v := range v.BeginMoreUnitsArr {
            switch v.Unit {
            case "件":
                f.SetCellValue("Sheet1", "D"+column, v.Amount)
            case "匹":
                f.SetCellValue("Sheet1", "E"+column, v.Amount)
            case "米":
                f.SetCellValue("Sheet1", "F"+column, v.Amount)
            }
        }

        f.SetCellValue("Sheet1", "G"+column, v.InputAmount)
        for _, v := range v.InputMoreUnitsArr {
            switch v.Unit {
            case "件":
                f.SetCellValue("Sheet1", "H"+column, v.Amount)
            case "匹":
                f.SetCellValue("Sheet1", "I"+column, v.Amount)
            case "米":
                f.SetCellValue("Sheet1", "J"+column, v.Amount)
            }
        }

        f.SetCellValue("Sheet1", "K"+column, v.OutputAmount)
        for _, v := range v.OutputMoreUnitsArr {
            switch v.Unit {
            case "件":
                f.SetCellValue("Sheet1", "L"+column, v.Amount)
            case "匹":
                f.SetCellValue("Sheet1", "M"+column, v.Amount)
            case "米":
                f.SetCellValue("Sheet1", "N"+column, v.Amount)
            }
        }

        f.SetCellValue("Sheet1", "O"+column, v.EndAmount)
        for _, v := range v.EndMoreUnitsArr {
            switch v.Unit {
            case "件":
                f.SetCellValue("Sheet1", "P"+column, v.Amount)
            case "匹":
                f.SetCellValue("Sheet1", "Q"+column, v.Amount)
            case "米":
                f.SetCellValue("Sheet1", "R"+column, v.Amount)
            }
        }
    }

    fileName := fmt.Sprintf("月度统计报表%s.xlsx", time.Now().Format("2006-01-02-1504"))
    if err := f.SaveAs(fileName); err != nil {
        return fileName, err
    }

    return fileName, nil
}