位置报表下载接口

zhangqian

2024-06-05 b327b91db1c7015845ad293e8ccc48c4611819c6

位置报表下载接口

 controllers/report_forms_controller.go

@@ -246,6 +246,7 @@
// @Tags      报表
// @Summary   获取位置报表
// @Produce   application/json
// @Param     Authorization    header string true "token"
// @Param     object  body  request.GetLocationForms true  "查询参数"
// @Success   200 {object} util.ResponseList{data=[]response.LocationForms}    "成功"
// @Router    /api-wms/v1/forms/getLocationForms [post]
@@ -255,54 +256,64 @@
        util.ResponseFormat(c, code.RequestParamError, "参数解析失败，数据类型错误")
        return
    }
    ids := make([]int, 0)
    if params.LocationId != 0 {
        ids = append(ids, params.LocationId)
    } else {
        //查询位置
        locations, err := models.NewLocationSearch().SetJointName(params.WareHouseCode).SetType(3).FindAll()
        if err != nil {
            util.ResponseFormat(c, code.RequestParamError, "查询位置失败")
            return
        }
        for _, location := range locations {
            ids = append(ids, location.Id)
        }
    }
    var (
        amounts    []*models.LocationProductAmount
        total      uint64
        totalInt64 int64
        err        error
    )
    if params.KeyWord != "" {
        amounts, total, err = service.SearchLocationReport(params.KeyWord, params.Page, params.PageSize)
    } else {
        amounts, totalInt64, err = models.NewLocationProductAmountSearch().SetPage(params.Page, params.PageSize).SetPreload(true).SetKeyword(params.KeyWord).SetProductId(params.ProductId).SetLocationIds(ids).FindByPage()
        if err != nil {
            util.ResponseFormat(c, code.RequestParamError, "查询数量失败")
            return
        }
        total = uint64(totalInt64)
    }

    var result []response.LocationForms
    for _, amount := range amounts {
        var resp response.LocationForms
        resp.Amount = amount.Amount
        resp.LocationId = amount.LocationId
        resp.LocationName = amount.Location.Name
        resp.ProduceId = amount.Product.ID
        resp.ProductName = amount.Product.Name
        resp.ProductTypeName = amount.ProductCategory.Name
        resp.Unit = amount.Product.Unit
        resp.Value = resp.Amount.Mul(amount.Product.Cost)
        result = append(result, resp)
    locationFormsService := service.NewLocationFormsService()
    total, err := locationFormsService.Count(params)
    if err != nil {
        logx.Errorf("GetLocationForms count err:%v", err)
        util.ResponseFormat(c, code.InternalError, "查询总数失败")
        return
    }
    result, err := locationFormsService.Query(params)
    if err != nil {
        logx.Errorf("GetLocationForms query err:%v", err)
        util.ResponseFormat(c, code.InternalError, "查询失败")
        return
    }

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

// DownloadLocationForms
// @Tags      报表
// @Summary   下载位置报表
// @Produce   application/json
// @Param     Authorization    header string true "token"
// @Param     object  body  request.GetLocationForms true  "查询参数"
// @Success   200 {object} util.ResponseList{data=[]response.LocationForms}    "成功"
// @Router    /api-wms/v1/forms/downloadLocationForms [post]
func (slf ReportFormsController) DownloadLocationForms(c *gin.Context) {
    var params request.GetLocationForms
    if err := c.BindJSON(&params); err != nil {
        util.ResponseFormat(c, code.RequestParamError, "参数解析失败，数据类型错误")
        return
    }
    locationFormsService := service.NewLocationFormsService()
    list, err := locationFormsService.FetchAll(params)
    if err != nil {
        logx.Errorf("DownloadLocationForms FetchAll err:%v", err)
        util.ResponseFormat(c, code.InternalError, "查询失败")
        return
    }
    filename, err := locationFormsService.Export(list)
    if err != nil {
        logx.Errorf("DownloadLocationForms Export err:%v", err)
        util.ResponseFormat(c, code.InternalError, "导出数据到文件失败")
        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)
    c.File(filename)
    defer os.Remove(filename)
}

// MonthStats
// @Tags      报表
// @Summary   月度统计库存报表

 docs/docs.go

@@ -520,6 +520,58 @@
                }
            }
        },
        "/api-wms/v1/forms/downloadLocationForms": {
            "post": {
                "produces": [
                    "application/json"
                ],
                "tags": [
                    "报表"
                ],
                "summary": "下载位置报表",
                "parameters": [
                    {
                        "type": "string",
                        "description": "token",
                        "name": "Authorization",
                        "in": "header",
                        "required": true
                    },
                    {
                        "description": "查询参数",
                        "name": "object",
                        "in": "body",
                        "required": true,
                        "schema": {
                            "$ref": "#/definitions/request.GetLocationForms"
                        }
                    }
                ],
                "responses": {
                    "200": {
                        "description": "成功",
                        "schema": {
                            "allOf": [
                                {
                                    "$ref": "#/definitions/util.ResponseList"
                                },
                                {
                                    "type": "object",
                                    "properties": {
                                        "data": {
                                            "type": "array",
                                            "items": {
                                                "$ref": "#/definitions/response.LocationForms"
                                            }
                                        }
                                    }
                                }
                            ]
                        }
                    }
                }
            }
        },
        "/api-wms/v1/forms/getHistory": {
            "post": {
                "produces": [
@@ -628,6 +680,13 @@
                "summary": "获取位置报表",
                "parameters": [
                    {
                        "type": "string",
                        "description": "token",
                        "name": "Authorization",
                        "in": "header",
                        "required": true
                    },
                    {
                        "description": "查询参数",
                        "name": "object",
                        "in": "body",

 docs/swagger.json

@@ -508,6 +508,58 @@
                }
            }
        },
        "/api-wms/v1/forms/downloadLocationForms": {
            "post": {
                "produces": [
                    "application/json"
                ],
                "tags": [
                    "报表"
                ],
                "summary": "下载位置报表",
                "parameters": [
                    {
                        "type": "string",
                        "description": "token",
                        "name": "Authorization",
                        "in": "header",
                        "required": true
                    },
                    {
                        "description": "查询参数",
                        "name": "object",
                        "in": "body",
                        "required": true,
                        "schema": {
                            "$ref": "#/definitions/request.GetLocationForms"
                        }
                    }
                ],
                "responses": {
                    "200": {
                        "description": "成功",
                        "schema": {
                            "allOf": [
                                {
                                    "$ref": "#/definitions/util.ResponseList"
                                },
                                {
                                    "type": "object",
                                    "properties": {
                                        "data": {
                                            "type": "array",
                                            "items": {
                                                "$ref": "#/definitions/response.LocationForms"
                                            }
                                        }
                                    }
                                }
                            ]
                        }
                    }
                }
            }
        },
        "/api-wms/v1/forms/getHistory": {
            "post": {
                "produces": [
@@ -616,6 +668,13 @@
                "summary": "获取位置报表",
                "parameters": [
                    {
                        "type": "string",
                        "description": "token",
                        "name": "Authorization",
                        "in": "header",
                        "required": true
                    },
                    {
                        "description": "查询参数",
                        "name": "object",
                        "in": "body",

 docs/swagger.yaml

@@ -2251,6 +2251,37 @@
      summary: 下载库存报表
      tags:
      - 报表
  /api-wms/v1/forms/downloadLocationForms:
    post:
      parameters:
      - description: token
        in: header
        name: Authorization
        required: true
        type: string
      - description: 查询参数
        in: body
        name: object
        required: true
        schema:
          $ref: '#/definitions/request.GetLocationForms'
      produces:
      - application/json
      responses:
        "200":
          description: 成功
          schema:
            allOf:
            - $ref: '#/definitions/util.ResponseList'
            - properties:
                data:
                  items:
                    $ref: '#/definitions/response.LocationForms'
                  type: array
              type: object
      summary: 下载位置报表
      tags:
      - 报表
  /api-wms/v1/forms/getHistory:
    post:
      parameters:
@@ -2311,6 +2342,11 @@
  /api-wms/v1/forms/getLocationForms:
    post:
      parameters:
      - description: token
        in: header
        name: Authorization
        required: true
        type: string
      - description: 查询参数
        in: body
        name: object

 models/location_product_amount.go

@@ -253,6 +253,9 @@
        db      = slf.build()
    )

    if slf.PageNum*slf.PageSize > 0 {
        db = db.Offset((slf.PageNum - 1) * slf.PageSize).Limit(slf.PageSize)
    }
    if err := db.Find(&records).Error; err != nil {
        return records, fmt.Errorf("find records err: %v", err)
    }

 router/router.go

@@ -157,8 +157,9 @@
        reportFormsAPI.POST("downloadInventoryForms", reportFormsController.DownloadInventoryForms) //下载库存报表
        reportFormsAPI.POST("getHistory", reportFormsController.GetHistory)                         //获取库存历史
        reportFormsAPI.POST("getLocationForms", reportFormsController.GetLocationForms)             //获取位置报表
        reportFormsAPI.POST("downloadLocationForms", reportFormsController.DownloadLocationForms)   //下载位置报表
        reportFormsAPI.POST("monthStats", reportFormsController.MonthStats)                         //获取月度统计报表
        reportFormsAPI.POST("doMonthStats", reportFormsController.DoMonthStats)                     //获取月度统计报表
        reportFormsAPI.POST("doMonthStats", reportFormsController.DoMonthStats)                     //手动跑月度统计库存报表
    }

    //重订货规则

 service/inventory_report_forms.go

@@ -174,7 +174,7 @@
        return nil, err
    }
    list = make([]*response.InventoryForms, 0, total)
    params.PageSize = 1000
    params.PageSize = 500
    page := 1
    for {
        params.Page = page
@@ -205,15 +205,16 @@
    }

    for i, v := range dataList {
        f.SetCellValue("Sheet1", "A"+strconv.Itoa(i+2), v.ProductName)
        f.SetCellValue("Sheet1", "B"+strconv.Itoa(i+2), v.ProductType)
        f.SetCellValue("Sheet1", "C"+strconv.Itoa(i+2), v.Cost)
        f.SetCellValue("Sheet1", "D"+strconv.Itoa(i+2), v.Value)
        f.SetCellValue("Sheet1", "E"+strconv.Itoa(i+2), v.Amount)
        f.SetCellValue("Sheet1", "F"+strconv.Itoa(i+2), v.AvailableNumber)
        f.SetCellValue("Sheet1", "G"+strconv.Itoa(i+2), v.In)
        f.SetCellValue("Sheet1", "H"+strconv.Itoa(i+2), v.Out)
        f.SetCellValue("Sheet1", "I"+strconv.Itoa(i+2), v.Unit)
        column := strconv.Itoa(i + 2)
        f.SetCellValue("Sheet1", "A"+column, v.ProductName)
        f.SetCellValue("Sheet1", "B"+column, v.ProductType)
        f.SetCellValue("Sheet1", "C"+column, v.Cost)
        f.SetCellValue("Sheet1", "D"+column, v.Value)
        f.SetCellValue("Sheet1", "E"+column, v.Amount)
        f.SetCellValue("Sheet1", "F"+column, v.AvailableNumber)
        f.SetCellValue("Sheet1", "G"+column, v.In)
        f.SetCellValue("Sheet1", "H"+column, v.Out)
        f.SetCellValue("Sheet1", "I"+column, v.Unit)
    }

    fileName = fmt.Sprintf("库存报表%s.xlsx", time.Now().Format("2006-01-02-1504"))

 service/location_forms.go

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

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

type LocationFormsService struct{}

func NewLocationFormsService() *LocationFormsService {
    return &LocationFormsService{}
}

func (slf *LocationFormsService) Query(params request.GetLocationForms) (result []*response.LocationForms, err error) {
    search, err := slf.BuildSearch(params)
    search = search.SetPage(params.Page, params.PageSize)
    amounts, err := search.Find()
    if err != nil {
        return
    }
    for _, amount := range amounts {
        resp := new(response.LocationForms)
        resp.Amount = amount.Amount
        resp.LocationId = amount.LocationId
        resp.LocationName = amount.Location.Name
        resp.ProduceId = amount.Product.ID
        resp.ProductName = amount.Product.Name
        resp.ProductTypeName = amount.ProductCategory.Name
        resp.Unit = amount.Product.Unit
        resp.Value = resp.Amount.Mul(amount.Product.Cost)
        result = append(result, resp)
    }

    return
}

func (slf *LocationFormsService) BuildSearch(params request.GetLocationForms) (search *models.LocationProductAmountSearch, err error) {
    ids := make([]int, 0)
    if params.LocationId != 0 {
        ids = append(ids, params.LocationId)
    } else {
        //查询位置
        locations, err := models.NewLocationSearch().SetJointName(params.WareHouseCode).SetType(3).FindAll()
        if err != nil {
            return nil, err
        }
        for _, location := range locations {
            ids = append(ids, location.Id)
        }
    }

    search = models.NewLocationProductAmountSearch().SetPreload(true).SetKeyword(params.KeyWord).SetProductId(params.ProductId).SetLocationIds(ids)

    return search, nil
}

func (slf *LocationFormsService) Count(params request.GetLocationForms) (total int64, err error) {
    search, err := slf.BuildSearch(params)
    if err != nil {
        return 0, err
    }
    return search.Count()
}

func (slf *LocationFormsService) FetchAll(params request.GetLocationForms) (list []*response.LocationForms, err error) {
    total, err := slf.Count(params)
    if err != nil {
        return nil, err
    }
    list = make([]*response.LocationForms, 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 *LocationFormsService) Export(dataList []*response.LocationForms) (filename string, err error) {
    var fileName string
    f := excelize.NewFile()

    // 自定义表头
    headers := []string{"位置", "产品", "产品类别", "在库数量", "价值"}

    // 设置表头
    for i, header := range headers {
        cell := getColumnAlphabet(i+1) + "1"
        f.SetCellValue("Sheet1", cell, header)
    }

    for i, v := range dataList {
        column := strconv.Itoa(i + 2)
        f.SetCellValue("Sheet1", "A"+column, v.LocationName)
        f.SetCellValue("Sheet1", "B"+column, v.ProductName)
        f.SetCellValue("Sheet1", "C"+column, v.ProductTypeName)
        f.SetCellValue("Sheet1", "D"+column, v.Amount)
        f.SetCellValue("Sheet1", "E"+column, v.Value)
    }

    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
}