kpt_xdmy/apiserver/service/stock.go

package service

import (
	"github.com/360EntSecGroup-Skylar/excelize"
	"github.com/astaxie/beego/logs"
	"github.com/gin-gonic/gin"
	"kpt.xdmy/apiserver/model"
	"strings"
	"time"
)

func (s *Service) GetStockList(req *model.GetStockListReq) (*model.GetStockListResp, error) {
	resp := new(model.GetStockListResp)
	tx := s.d.DB.Table("stock")
	tx.Joins("JOIN pasture ON pasture.id = stock.pastureId")
	tx.Joins("JOIN department ON department.id = stock.departmentId")
	tx.Joins("JOIN equipment ON equipment.id = stock.eqId")
	tx.Joins(" left JOIN emp ON emp.id = stock.checkTaker")

	tx.Select("stock.id, stock.pastureId, stock.departmentId, stock.eqId, date_format(stock.rzdate ,'%Y-%m-%d') rzdate , stock.unit, " +
		"stock.specification, stock.quantity, stock.yuanzhi, stock.count, stock.profit, stock.tag, " +
		"date_format(stock.importdate ,'%Y-%m-%d %H:%i:%s')  importdate, date_format(stock.inventorydate ,'%Y-%m-%d %H:%i:%s') inventorydate, stock.checktaker, stock.remark,pasture.name,department.name departmentName ," +
		"equipment.eqCode,equipment.eqName,equipment.assetCode,emp.empname empname")

	if req.PastureId != "" && req.PastureId != "18" {
		tx.Where(" stock.pastureId = ? ", req.PastureId)
	}
	if req.DepartmentId != "" {
		tx.Where(" stock.departmentId = ? ", req.DepartmentId)
	}
	if req.StartDate != "" {
		tx.Where(" stock.inventorydate between ? and ?  ", req.StartDate, req.EndDate)
	}

	stockAndPastureList := make([]*model.StockAndPasture, 0)
	//tx.Limit(req.PageCount).Offset(req.Offset)
	err := tx.Debug().Order(" importdate desc,id asc  ").Limit(req.Pagecount).Offset(req.Offset * 10).Find(&stockAndPastureList).Error
	if err != nil {
		logs.Error(err)
		return resp, err
	}

	tx1 := s.d.DB.Table("stock")
	tx1.Joins("JOIN pasture ON pasture.id = stock.pastureId")
	tx1.Joins("JOIN department ON department.id = stock.departmentId")
	tx1.Joins("JOIN equipment ON equipment.id = stock.eqId")
	tx1.Joins(" left JOIN emp ON emp.id = stock.checkTaker")
	tx1.Select("stock.id, stock.pastureId, stock.departmentId, stock.eqId, date_format(stock.rzdate ,'%Y-%m-%d') rzdate , stock.unit, " +
		"stock.specification, stock.quantity, stock.yuanzhi, stock.count, stock.profit, stock.tag, " +
		"date_format(stock.importdate ,'%Y-%m-%d %H:%i:%s')  importdate, date_format(stock.inventorydate ,'%Y-%m-%d %H:%i:%s') inventorydate, stock.checktaker, stock.remark,pasture.name,department.name departmentName ," +
		"equipment.eqCode,equipment.eqName,equipment.assetCode,emp.empname empname")

	if req.PastureId != "" && req.PastureId != "18" {
		tx1.Where(" stock.pastureId = ? ", req.PastureId)
	}
	if req.DepartmentId != "" {
		tx1.Where(" stock.departmentId = ? ", req.DepartmentId)
	}
	if req.StartDate != "" {
		tx1.Where(" stock.inventorydate between ? and ?  ", req.StartDate, req.EndDate)
	}

	var count int64
	err = tx1.Count(&count).Error
	if err != nil {
		logs.Error(err)
		return resp, err
	}

	stockList := make([]*model.StockInfo, 0)
	for _, item := range stockAndPastureList {
		s := new(model.StockInfo)
		s.ID = item.Stock.ID
		s.PastureId = item.Stock.PastureId
		s.PastureName = item.Pasture.Name
		s.DepartmentId = item.Stock.DepartmentId
		s.DepartmentName = item.DepartmentName
		s.EqId = item.EqId
		s.EqCode = item.EqCode
		s.EqName = item.EqName
		s.AssetCode = item.AssetCode
		s.RzDate = item.RzDate
		s.Unit = item.Stock.Unit
		s.Specification = item.Stock.Specification
		s.Quantity = item.Stock.Quantity
		s.Yuanzhi = item.Stock.Yuanzhi
		s.Count = item.Count
		s.Profit = item.Profit
		s.Tag = item.Tag
		s.ImportDate = item.ImportDate
		s.InventoryDate = item.InventoryDate
		s.CheckTaker = item.CheckTaker
		s.CheckTakerName = item.Empname
		s.Remark = item.Stock.Remark
		if item.CheckTaker != "" {
			s.Status = 1
		}
		stockList = append(stockList, s)
	}
	resp.StockList = stockList
	resp.Count = count
	return resp, nil
}

func (s *Service) StockExcel(c *gin.Context) map[string]interface{} {

	if err := c.Request.ParseForm(); err != nil {
		return nil
	}

	fileHeader, err := c.FormFile("file")
	file, err := fileHeader.Open()
	if err != nil {
		return nil
	}

	xlsx1, err := excelize.OpenReader(file)

	if err != nil {
		return nil
	}

	rows := xlsx1.GetRows("资产盘点模板")
	if len(rows) > 10000 {
		rows = rows[:10000]
	}
	paramslist := []string{"牧场", "部门", "资产编号", "资产名称", "设备内部编号", "实物入账日期", "计量单位", "规格",
		"资产数量", "资产原值", "实际盘点数", "盈亏量", "有无标签", "备注"}

	ok := 0
	error := 0
	queryData := make([]interface{}, 0)
	createdate := time.Now().Format("2006-01-02 15:04:05")
	for i, row := range rows {
		if i > 0 {
			//s_params := make([]interface{}, 0)
			//entry := make(map[string]string)
			outentry := make(map[string]string)

			for _, value := range paramslist {

				for j, colCell := range rows[0] {
					if strings.Trim(value, " ") == colCell {
						for k, colCell1 := range row {
							if j == k {
								outentry[colCell] = colCell1
								//entry[colCell] = colCell1
								//inputdata = inputdata + colCell + ":" + colCell1 + ";"
							}
						}
					}
				}
			}

			sw, err := time.Parse("2006-01-02", outentry["实物入账日期"])
			if err != nil {
				logs.Error(err)
				outentry["error_msg"] = err.Error()
				error++
				queryData = append(queryData, outentry)
				continue
			}

			pasture := new(model.Pasture)
			s.d.DB.Table("pasture").Where("name = ?", outentry["牧场"]).First(&pasture)
			if pasture.ID == 0 {
				outentry["error_msg"] = "牧场不存在！"
				error++
				queryData = append(queryData, outentry)
				continue
			}
			department := new(model.Department)
			s.d.DB.Table("department").Where("name = ?", outentry["部门"]).Where("pastureId = ? ", pasture.ID).First(&department)
			if department.ID == 0 {
				outentry["error_msg"] = "部门不存在！"
				error++
				queryData = append(queryData, outentry)
				continue
			}
			equipment := new(model.Equipment)
			s.d.DB.Table("equipment").Where("eqCode = ? ", outentry["设备内部编号"]).First(&equipment)
			if department.ID == 0 {
				outentry["error_msg"] = "资产不存在！"
				error++
				queryData = append(queryData, outentry)
				continue
			}

			//paramslist := []string{"牧场", "部门", "资产编号", "资产名称", "设备内部编号", "实物入账日期", "计量单位", "规格",
			//	"资产数量", "资产原值", "实际盘点数", "盈亏量", "有无标签", "备注"}
			err = s.d.DB.Debug().Omit("inventorydate,checktaker").Create(&model.Stock{PastureId: pasture.ID,
				DepartmentId:  department.ID,
				EqId:          equipment.Id,
				RzDate:        sw.Format("2006-01-02"),
				Unit:          outentry["计量单位"],
				Specification: outentry["规格"],
				Quantity:      outentry["资产数量"],
				Yuanzhi:       outentry["资产原值"],
				Count:         outentry["实际盘点数"],
				Profit:        outentry["盈亏量"],
				Tag:           outentry["有无标签"],
				ImportDate:    createdate,
				Remark:        outentry["备注"],
				CreateDate:    time.Now().Format("2006-01-02")}).Error

			if err != nil {
				logs.Error(err)
				outentry["error_msg"] = err.Error()
				error++
				queryData = append(queryData, outentry)
				continue
			}
		}
	}

	data := make(map[string]interface{})
	data["result"] = queryData
	data["success"] = ok
	data["err_count"] = error
	return data
}

func (s *Service) DelStockList(idList []string) error {
	err := s.d.DB.Debug().Exec(`delete from stock  where id in(?) `, idList).Error
	if err != nil {
		logs.Error(err)
		return err
	}
	return nil
}