按仓库统计月度报表下载接口

zhangqian

2024-07-02 9d296a1c658c19c6faa51dfef31d025e59a480cb

按仓库统计月度报表下载接口

 constvar/const.go

@@ -333,6 +333,11 @@
    TakeStock                            // 盘点类型
)

const (
    InputTotalHeader  string = "入库合计"
    OutPutTotalHeader string = "出库合计"
)

func (t MiniDictType) Valid() bool {
    if t <= 0 {
        return false

 controllers/operation.go

@@ -100,6 +100,10 @@
    params.Status = constvar.OperationStatus_Ready
    //params.Number = strconv.FormatInt(time.Now().Unix(), 10)

    for _, detail := range params.Details {
        detail.BaseOperationType = params.BaseOperationType
    }

    var numberNum int64
    if err := mysqlx.GetDB().Model(&models.Operation{}).Where("number=?", params.Number).Count(&numberNum).Error; err != nil {
        util.ResponseFormat(c, code.RequestParamError, err.Error())

 controllers/report_forms_controller.go

@@ -466,6 +466,8 @@
        return
    }

    params.Preload = true

    result, err := monthFormsService.Query(params)
    if err != nil {
        logx.Errorf("MonthStats query err:%v", err)
@@ -528,3 +530,44 @@
    task.WarehouseMonthStats()
    util.ResponseFormat(c, code.Success, nil)
}

// DownloadWarehouseMonthStats
// @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/downloadWarehouseMonthStats [post]
func (slf ReportFormsController) DownloadWarehouseMonthStats(c *gin.Context) {
    var params request.GetMonthStats
    if err := c.BindJSON(&params); err != nil {
        util.ResponseFormat(c, code.RequestParamError, "参数解析失败，数据类型错误")
        return
    }

    if params.WarehouseID == 0 {
        util.ResponseFormat(c, code.RequestParamError, "仓库ID参数缺失")
        return
    }

    monthFormsService := service.NewWarehouseMonthFormsService()
    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)
}

 docs/docs.go

@@ -1033,6 +1033,58 @@
                }
            }
        },
        "/api-wms/v1/forms/downloadWarehouseMonthStats": {
            "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": [
@@ -5260,6 +5312,14 @@
                "auxiliaryUnit": {
                    "type": "string"
                },
                "baseOperationType": {
                    "description": "基础作业类型",
                    "allOf": [
                        {
                            "$ref": "#/definitions/constvar.BaseOperationType"
                        }
                    ]
                },
                "cost": {
                    "description": "成本单价",
                    "type": "number"

 docs/swagger.json

@@ -1022,6 +1022,58 @@
                }
            }
        },
        "/api-wms/v1/forms/downloadWarehouseMonthStats": {
            "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": [
@@ -5249,6 +5301,14 @@
                "auxiliaryUnit": {
                    "type": "string"
                },
                "baseOperationType": {
                    "description": "基础作业类型",
                    "allOf": [
                        {
                            "$ref": "#/definitions/constvar.BaseOperationType"
                        }
                    ]
                },
                "cost": {
                    "description": "成本单价",
                    "type": "number"

 docs/swagger.yaml

@@ -1041,6 +1041,10 @@
        type: number
      auxiliaryUnit:
        type: string
      baseOperationType:
        allOf:
        - $ref: '#/definitions/constvar.BaseOperationType'
        description: 基础作业类型
      cost:
        description: 成本单价
        type: number
@@ -2965,6 +2969,37 @@
      summary: 下载月度统计库存报表
      tags:
      - 报表
  /api-wms/v1/forms/downloadWarehouseMonthStats:
    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:

 models/operation.go

@@ -95,6 +95,12 @@
    return &OperationSearch{Orm: mysqlx.GetDB()}
}

func (slf *OperationSearch) BeforeCreate(tx *gorm.DB) {
    for k := range slf.Details {
        slf.Details[k].BaseOperationType = slf.BaseOperationType
    }
}

func (slf *OperationSearch) SetOrm(tx *gorm.DB) *OperationSearch {
    slf.Orm = tx
    return slf

 models/operation_details.go

@@ -4,6 +4,7 @@
    "fmt"
    "github.com/shopspring/decimal"
    "gorm.io/gorm"
    "wms/constvar"
    "wms/pkg/mysqlx"
)

@@ -11,9 +12,10 @@
    // OperationDetails 操作明细表
    OperationDetails struct {
        WmsModel
        Id          int    `json:"id" gorm:"column:id;primary_key;AUTO_INCREMENT"`
        OperationID int    `json:"operationId" gorm:"index;type:int;not null;comment:操作记录id"` //操作id
        ProductId   string `json:"productId" gorm:"type:varchar(191);not null;comment:产品id"`  //产品id
        Id                int                        `json:"id" gorm:"column:id;primary_key;AUTO_INCREMENT"`
        OperationID       int                        `json:"operationId" gorm:"index;type:int;not null;comment:操作记录id"`     //操作id
        BaseOperationType constvar.BaseOperationType `json:"baseOperationType" gorm:"type:tinyint;not null;comment:基础作业类型"` //基础作业类型
        ProductId         string                     `json:"productId" gorm:"type:varchar(191);not null;comment:产品id"`      //产品id
        //ProductName string          `json:"productName" gorm:"type:varchar(255);not null;comment:产品名称"` //产品名称
        Amount decimal.Decimal `json:"amount" gorm:"type:decimal(20,2);not null;comment:数量"` //数量
        //Unit        string          `json:"unit" gorm:"type:varchar(31);comment:单位"`                    //单位
@@ -106,6 +108,11 @@
    return slf
}

func (slf *OperationDetailsSearch) SetBaseOperationType(baseOperationType constvar.BaseOperationType) *OperationDetailsSearch {
    slf.BaseOperationType = baseOperationType
    return slf
}

func (slf *OperationDetailsSearch) build() *gorm.DB {
    var db = slf.Orm.Model(&OperationDetails{})

@@ -139,6 +146,10 @@
        db = db.Select(slf.Fields)
    }

    if slf.BaseOperationType != 0 {
        db = db.Where("base_operation_type = ?", slf.BaseOperationType)
    }

    return db
}


 models/warehouse_month_stats.go

@@ -306,6 +306,14 @@
    return m
}

func WarehouseStatsItemMap(records []*WarehouseStatsItems) (m map[string]*WarehouseStatsItems) {
    m = make(map[string]*WarehouseStatsItems, len(records))
    for _, record := range records {
        m[record.Name] = record
    }
    return m
}

func (slf *WarehouseMonthStatsSearch) Count() (int64, error) {
    var (
        total int64

 router/router.go

@@ -179,8 +179,9 @@
        reportFormsAPI.POST("downloadMonthStats", reportFormsController.DownloadMonthStats)         //下载月度统计报表
        reportFormsAPI.POST("doMonthStats", reportFormsController.DoMonthStats)                     //手动跑月度统计库存报表

        reportFormsAPI.POST("warehouseMonthStats", reportFormsController.WarehouseMonthStats)     //按仓库获取月度统计报表
        reportFormsAPI.POST("doWarehouseMonthStats", reportFormsController.DoWareHouseMonthStats) //手动跑按仓库获取月度统计报表
        reportFormsAPI.POST("warehouseMonthStats", reportFormsController.WarehouseMonthStats)                 //按仓库获取月度统计报表
        reportFormsAPI.POST("doWarehouseMonthStats", reportFormsController.DoWareHouseMonthStats)             //手动跑按仓库获取月度统计报表
        reportFormsAPI.POST("downloadWarehouseMonthStats", reportFormsController.DownloadWarehouseMonthStats) //下载按仓库获取月度统计报表
    }

    //重订货规则

 service/dict.go

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

import (
    "wms/constvar"
    "wms/models"
)

func GetDictNameListByType(dictType constvar.MiniDictType) (names []string) {
    dictList, err := models.NewMiniDictSearch().SetType(dictType).SetOrder("id asc").FindNotTotal()
    if err != nil {
        return nil
    }

    for _, dict := range dictList {
        names = append(names, dict.Name)
    }

    return names
}

 service/warehouse_month_forms.go

@@ -1,7 +1,10 @@
package service

import (
    "fmt"
    "github.com/shopspring/decimal"
    "github.com/xuri/excelize/v2"
    "strconv"
    "time"
    "wms/constvar"
    "wms/models"
@@ -58,6 +61,101 @@
    return
}

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

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

    headers, headerLen, inputTypes, outputTypes := slf.GetHeaders()
    err = SetExcelHeader(headers, f)
    if err != nil {
        logx.Errorf("SetExcelHeader err:%v", err)
        return "", err
    }

    //表头样式
    style, err := SetHeaderStyle(f)
    if err != nil {
        return "", err
    }

    f.SetCellStyle("Sheet1", "A1", getColumnAlphabet(headerLen)+"2", style)
    // 设置列宽
    f.SetColWidth("Sheet1", "A", "F", 30)
    f.SetColWidth("Sheet1", "G", getColumnAlphabet(headerLen), 15)

    inputStart := 7
    outputStart := 7 + len(inputTypes)
    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.EndAmount)
        f.SetCellValue("Sheet1", "D"+column, v.BeginAmount)
        f.SetCellValue("Sheet1", "E"+column, v.Unit)
        f.SetCellValue("Sheet1", "F"+column, v.SalePrice)

        slf.FillDealerTypeToExcel(v.InputItems, inputStart, i+3, inputTypes, f)
        slf.FillDealerTypeToExcel(v.OutputItems, outputStart, i+3, outputTypes, f)
    }

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

    return fileName, nil
}

func (slf *WarehouseMonthFormsService) GetHeaders() (headers []interface{}, headerLen int, inputTypes, outputTypes []string) {
    // 自定义表头

    //查询入库类型
    inputTypes = GetDictNameListByType(constvar.StorageType)
    inputTypes = append(inputTypes, constvar.InputTotalHeader)

    headerLen += len(inputTypes)

    //查询入库类型
    outputTypes = GetDictNameListByType(constvar.StockoutType)
    outputTypes = append(outputTypes, constvar.OutPutTotalHeader)

    headerLen += len(inputTypes)

    headerLen += 6

    headers = []interface{}{"物料编号", "物料编码", "月末结存", "月初结存", "单位", "单价", map[string][]string{"本月入库": inputTypes}, map[string][]string{"本月出库": outputTypes}}
    return headers, headerLen, inputTypes, outputTypes
}

func (slf *WarehouseMonthFormsService) SumItems(items []*models.WarehouseStatsItems) (sum decimal.Decimal) {
    for _, v := range items {
        sum = sum.Add(v.Amount)
    }
    return sum
}

func (slf *WarehouseMonthFormsService) FillDealerTypeToExcel(items []*models.WarehouseStatsItems, startIndex int, column int, dealerTypes []string, f *excelize.File) {
    columnStr := strconv.Itoa(column)
    sum := slf.SumItems(items)
    detailMap := models.WarehouseStatsItemMap(items)

    for i := 0; i < len(dealerTypes); i++ {
        var amount decimal.Decimal
        if detailMap[dealerTypes[i]] != nil {
            amount = detailMap[dealerTypes[i]].Amount
        } else if dealerTypes[i] == constvar.InputTotalHeader {
            amount = sum
        }
        f.SetCellValue("Sheet1", getColumnAlphabet(startIndex+i)+columnStr, amount)
    }
    return
}

func GetCurrentWarehouseStats(date string, warehouseId int, productIds []string) (statRecords []*models.WarehouseMonthStats, err error) {
    //本月期初数量/上月结余数量
    groupSumList, err := models.NewLocationProductAmountSearch().SetProductIds(productIds).GroupSum("product_id", "amount")