库存报表查询及报表下载支持多单位

zhangqian

2024-06-12 ee8c1c18382dce5394677b1066385d7ef276c2f1

库存报表查询及报表下载支持多单位

 docs/docs.go

@@ -4491,6 +4491,7 @@
                    "type": "number"
                },
                "floating": {
                    "description": "是否浮动利率",
                    "type": "boolean"
                },
                "unit": {
@@ -5674,9 +5675,23 @@
                    "description": "在库数量",
                    "type": "number"
                },
                "amountMoreUnits": {
                    "description": "在库数量多单位",
                    "type": "array",
                    "items": {
                        "$ref": "#/definitions/models.UnitItems"
                    }
                },
                "availableNumber": {
                    "description": "可用库存",
                    "type": "number"
                },
                "availableNumberMoreUnits": {
                    "description": "可用库存多单位",
                    "type": "array",
                    "items": {
                        "$ref": "#/definitions/models.UnitItems"
                    }
                },
                "cost": {
                    "description": "成本",
@@ -5686,10 +5701,24 @@
                    "description": "入库",
                    "type": "number"
                },
                "inMoreUnits": {
                    "description": "入库多单位",
                    "type": "array",
                    "items": {
                        "$ref": "#/definitions/models.UnitItems"
                    }
                },
                "out": {
                    "description": "出库",
                    "type": "number"
                },
                "outMoreUnits": {
                    "description": "出库多单位",
                    "type": "array",
                    "items": {
                        "$ref": "#/definitions/models.UnitItems"
                    }
                },
                "produceId": {
                    "description": "产品id",
                    "type": "string"

 docs/swagger.json

@@ -4479,6 +4479,7 @@
                    "type": "number"
                },
                "floating": {
                    "description": "是否浮动利率",
                    "type": "boolean"
                },
                "unit": {
@@ -5662,9 +5663,23 @@
                    "description": "在库数量",
                    "type": "number"
                },
                "amountMoreUnits": {
                    "description": "在库数量多单位",
                    "type": "array",
                    "items": {
                        "$ref": "#/definitions/models.UnitItems"
                    }
                },
                "availableNumber": {
                    "description": "可用库存",
                    "type": "number"
                },
                "availableNumberMoreUnits": {
                    "description": "可用库存多单位",
                    "type": "array",
                    "items": {
                        "$ref": "#/definitions/models.UnitItems"
                    }
                },
                "cost": {
                    "description": "成本",
@@ -5674,10 +5689,24 @@
                    "description": "入库",
                    "type": "number"
                },
                "inMoreUnits": {
                    "description": "入库多单位",
                    "type": "array",
                    "items": {
                        "$ref": "#/definitions/models.UnitItems"
                    }
                },
                "out": {
                    "description": "出库",
                    "type": "number"
                },
                "outMoreUnits": {
                    "description": "出库多单位",
                    "type": "array",
                    "items": {
                        "$ref": "#/definitions/models.UnitItems"
                    }
                },
                "produceId": {
                    "description": "产品id",
                    "type": "string"

 docs/swagger.yaml

@@ -1037,6 +1037,7 @@
      amount:
        type: number
      floating:
        description: 是否浮动利率
        type: boolean
      unit:
        type: string
@@ -1858,18 +1859,38 @@
      amount:
        description: 在库数量
        type: number
      amountMoreUnits:
        description: 在库数量多单位
        items:
          $ref: '#/definitions/models.UnitItems'
        type: array
      availableNumber:
        description: 可用库存
        type: number
      availableNumberMoreUnits:
        description: 可用库存多单位
        items:
          $ref: '#/definitions/models.UnitItems'
        type: array
      cost:
        description: 成本
        type: number
      in:
        description: 入库
        type: number
      inMoreUnits:
        description: 入库多单位
        items:
          $ref: '#/definitions/models.UnitItems'
        type: array
      out:
        description: 出库
        type: number
      outMoreUnits:
        description: 出库多单位
        items:
          $ref: '#/definitions/models.UnitItems'
        type: array
      produceId:
        description: 产品id
        type: string

 models/material.go

@@ -120,7 +120,7 @@
    UnitItems struct {
        Amount   decimal.Decimal `json:"amount"`
        Unit     string          `json:"unit"`
        Floating bool            `json:"floating"`
        Floating bool            `json:"floating"` //是否浮动利率
    }
)


 response/report_forms_response.go

@@ -4,19 +4,24 @@
    "github.com/shopspring/decimal"
    "time"
    "wms/constvar"
    "wms/models"
)

type InventoryForms struct {
    ProduceId       string          `json:"produceId"`       //产品id
    ProductName     string          `json:"productName"`     //产品名称
    ProductType     string          `json:"productType"`     //产品类型
    Cost            decimal.Decimal `json:"cost"`            //成本
    Value           decimal.Decimal `json:"value"`           //总价值
    Amount          decimal.Decimal `json:"amount"`          //在库数量
    AvailableNumber decimal.Decimal `json:"availableNumber"` //可用库存
    In              decimal.Decimal `json:"in"`              //入库
    Out             decimal.Decimal `json:"out"`             //出库
    Unit            string          `json:"unit"`            //单位
    ProduceId                string             `json:"produceId"`                //产品id
    ProductName              string             `json:"productName"`              //产品名称
    ProductType              string             `json:"productType"`              //产品类型
    Cost                     decimal.Decimal    `json:"cost"`                     //成本
    Value                    decimal.Decimal    `json:"value"`                    //总价值
    Amount                   decimal.Decimal    `json:"amount"`                   //在库数量
    AmountMoreUnits          []models.UnitItems `json:"amountMoreUnits"`          //在库数量多单位
    AvailableNumber          decimal.Decimal    `json:"availableNumber"`          //可用库存
    AvailableNumberMoreUnits []models.UnitItems `json:"availableNumberMoreUnits"` //可用库存多单位
    In                       decimal.Decimal    `json:"in"`                       //入库
    InMoreUnits              []models.UnitItems `json:"inMoreUnits"`              //入库多单位
    Out                      decimal.Decimal    `json:"out"`                      //出库
    OutMoreUnits             []models.UnitItems `json:"outMoreUnits"`             //出库多单位
    Unit                     string             `json:"unit"`                     //单位
}

type InventoryHistory struct {

 service/history_forms.go

@@ -100,7 +100,7 @@

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


 service/inventory_report_forms.go

@@ -109,6 +109,14 @@
            }
        }
        data.AvailableNumber = data.Amount.Sub(available)

        if *material.MoreUnit {
            data.AmountMoreUnits = CreateMoreUnit(data.Amount, material.MoreUnitList)
            data.AvailableNumberMoreUnits = CreateMoreUnit(data.AvailableNumber, material.MoreUnitList)
            data.InMoreUnits = CreateMoreUnit(data.In, material.MoreUnitList)
            data.OutMoreUnits = CreateMoreUnit(data.Out, material.MoreUnitList)
        }

        result = append(result, data)
    }
    return
@@ -142,7 +150,7 @@
    //查询产品
    search = models.NewMaterialSearch()
    search.Orm = search.Orm.Model(&models.Material{}).
        Select(`material.id, material.name, material.cost, material.amount, material.unit, wms_product_category.name as category_name `).
        Select(`material.id, material.name, material.cost, material.amount, material.unit, material.more_unit, material.more_unit_value, wms_product_category.name as category_name `).
        Joins("left join wms_product_category on material.category_id = wms_product_category.id")
    if len(params.CategoryIds) > 0 {
        search.Orm.Where("material.category_id in (?)", params.CategoryIds)
@@ -196,25 +204,78 @@
    f := excelize.NewFile()

    // 自定义表头
    headers := []string{"产品", "产品类别", "单位成本", "总价值", "在库", "可用库存", "入库", "出库", "单位"}

    headers := []string{"产品", "产品类别", "单位成本", "总价值", "在库", "可用库存", "入库", "出库"}
    // 设置表头
    for i, header := range headers {
        cell := getColumnAlphabet(i+1) + "1"
        cell := getColumnAlphabet(i) + "1"
        f.SetCellValue("Sheet1", cell, header)
    }

    f.SetCellValue("Sheet1", getColumnAlphabet(0)+"1", "产品")
    f.SetCellValue("Sheet1", getColumnAlphabet(1)+"1", "产品类别")
    f.SetCellValue("Sheet1", getColumnAlphabet(2)+"1", "单位成本")
    f.SetCellValue("Sheet1", getColumnAlphabet(3)+"1", "总价值")
    f.SetCellValue("Sheet1", getColumnAlphabet(4)+"1", "在库")
    f.MergeCell("Sheet1", getColumnAlphabet(4)+"1", getColumnAlphabet(7)+"1") // 合并单元格
    f.SetCellValue("Sheet1", getColumnAlphabet(8)+"1", "可用库存")
    f.MergeCell("Sheet1", getColumnAlphabet(8)+"1", getColumnAlphabet(11)+"1") // 合并单元格
    f.SetCellValue("Sheet1", getColumnAlphabet(12)+"1", "入库")
    f.MergeCell("Sheet1", getColumnAlphabet(12)+"1", getColumnAlphabet(15)+"1") // 合并单元格
    f.SetCellValue("Sheet1", getColumnAlphabet(16)+"1", "出库")
    f.MergeCell("Sheet1", getColumnAlphabet(16)+"1", getColumnAlphabet(19)+"1") // 合并单元格

    unitData := []string{"件", "匹", "米", "重量"}
    for i, header := range unitData {
        f.SetCellValue("Sheet1", getColumnAlphabet(i+4)+"2", header)
        f.SetCellValue("Sheet1", getColumnAlphabet(i+8)+"2", header)
        f.SetCellValue("Sheet1", getColumnAlphabet(i+12)+"2", header)
        f.SetCellValue("Sheet1", getColumnAlphabet(i+16)+"2", 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 {
        return
    }

    lastColumn := getColumnAlphabet(19)
    f.SetCellStyle("Sheet1", "A1", lastColumn+"2", titleStyle)
    // 设置列宽
    f.SetColWidth("Sheet1", "A", "D", 30)
    f.SetColWidth("Sheet1", "E", lastColumn, 15)

    for i, v := range dataList {
        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)
        column := strconv.Itoa(i + 3)
        f.SetCellValue("Sheet1", getColumnAlphabet(0)+column, v.ProductName)
        f.SetCellValue("Sheet1", getColumnAlphabet(1)+column, v.ProductType)
        f.SetCellValue("Sheet1", getColumnAlphabet(2)+column, v.Cost)
        f.SetCellValue("Sheet1", getColumnAlphabet(3)+column, v.Value)

        f.SetCellValue("Sheet1", getColumnAlphabet(4)+column, v.Amount)
        FillMoreUnitToExcel(v.Amount, v.AmountMoreUnits, 5, i+3, f)

        f.SetCellValue("Sheet1", getColumnAlphabet(8)+column, v.AvailableNumber)
        FillMoreUnitToExcel(v.AvailableNumber, v.AvailableNumberMoreUnits, 9, i+3, f)

        f.SetCellValue("Sheet1", getColumnAlphabet(12)+column, v.In)
        FillMoreUnitToExcel(v.In, v.AmountMoreUnits, 13, i+3, f)

        f.SetCellValue("Sheet1", getColumnAlphabet(16)+column, v.Out)
        FillMoreUnitToExcel(v.Out, v.AmountMoreUnits, 17, i+3, f)
    }

    fileName = fmt.Sprintf("库存报表%s.xlsx", time.Now().Format("2006-01-02-1504"))
@@ -229,7 +290,7 @@
func getColumnAlphabet(index int) string {
    const alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    if index <= 26 {
        return string(alphabet[index-1])
        return string(alphabet[index])
    }
    return getColumnAlphabet((index-1)/26) + getColumnAlphabet((index-1)%26+1)
    return getColumnAlphabet((index)/26) + getColumnAlphabet((index)%26+1)
}

 service/month_forms.go

@@ -111,7 +111,6 @@
    }
    titleStyle, err := f.NewStyle(style)
    if err != nil {
        fmt.Println(err)
        return
    }
    f.SetCellStyle("Sheet1", "A1", "R2", titleStyle)

 service/more_units.go

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

import (
    "github.com/shopspring/decimal"
    "github.com/xuri/excelize/v2"
    "strconv"
    "wms/models"
)

func CreateMoreUnit(amount decimal.Decimal, units []models.UnitItems) []models.UnitItems {
    moreValueArr := make([]models.UnitItems, 0, len(units))
    for _, unitItem := range units {
        moreValueArr = append(moreValueArr, models.UnitItems{
            Amount:   amount.Mul(unitItem.Amount),
            Unit:     unitItem.Unit,
            Floating: unitItem.Floating,
        })
    }
    return moreValueArr
}

func FillMoreUnitToExcel(amount decimal.Decimal, units []models.UnitItems, startIndex int, column int, f *excelize.File) {
    columnStr := strconv.Itoa(column)
    for _, v := range units {
        switch v.Unit {
        case "匹":
            f.SetCellValue("Sheet1", getColumnAlphabet(startIndex)+columnStr, v.Amount.Mul(amount))
        case "米":
            f.SetCellValue("Sheet1", getColumnAlphabet(startIndex+1)+columnStr, v.Amount.Mul(amount))
        case "重量":
            f.SetCellValue("Sheet1", getColumnAlphabet(startIndex+2)+columnStr, v.Amount.Mul(amount))
        }
    }
    return
}