LAPP_SJA_ME_WEB/web/models/me_part_model.go

package models

import (
	"LAPP_SJA_ME/db"
	"LAPP_SJA_ME/utils"
	"errors"
	"github.com/tealeg/xlsx"
	"xorm.io/core"
)

//零件主数据
type MePart struct {
	Finr       int    `xorm:"pk comment('工厂号') int(0)" json:"me_part-finr"`
	Partid     string `xorm:"pk comment('零件Id') VARCHAR(40)" json:"me_part-partid"`
	Projnr     string `xorm:"pk comment('项目Id') VARCHAR(40)" json:"me_part-projnr"`
	Custpartid string `xorm:"comment('客户零件号') VARCHAR(30)" json:"me_part-custpartid"`
	Intpartid  string `xorm:"comment('内部零件号') VARCHAR(30)" json:"me_part-intpartid"`
	Oripartid  string `xorm:"comment('原零件号') VARCHAR(30)" json:"me_part-oripartid"`
	Ai         string `xorm:"comment('零件版本号') VARCHAR(30)" json:"me_part-ai"`
	Status     string `xorm:"comment('状态') VARCHAR(30)" json:"me_part-status"`
	Descr1     string `xorm:"comment('描述1（英文）') VARCHAR(60)" json:"me_part-descr1"`
	Descr2     string `xorm:"comment('描述2（中文）') VARCHAR(60)" json:"me_part-descr2"`
	Uom        string `xorm:"comment('文本') VARCHAR(30)" json:"me_part-uom"`
	Lastmodif  string `xorm:"comment('最近一次更改时间') VARCHAR(14)" json:"me_part-lastmodif"`
	Lastuser   string `xorm:"comment('最近一次更改人') VARCHAR(14)" json:"me_part-lastuser"`
	Credatuz   string `xorm:"comment('创建时间') VARCHAR(20)" json:"me_part-credatuz"`
}

func (t *MePart) TableName() string {
	return "me_part"
}

// 清除string字段的右侧空格
func (t *MePart) Clipped() {
	utils.TrimStruct(t, *t)
}

//增
func (t *MePart) Add() error {
	e := db.Eloquent.Master()
	countrole := new(MePart)
	affw, err := e.Table("me_part").ID(core.PK{t.Finr, t.Partid, t.Projnr}).Count(countrole)
	if err != nil {
		return err
	}
	if affw > 0 {
		return errors.New("数据已经存在！")
	}
	_, err = e.Table("me_part").Insert(t)

	if err != nil {
		return err
	}
	return nil
}

//删
func (t *MePart) Del() bool {
	e := db.Eloquent.Master()
	_, err := e.ID(core.PK{t.Finr, t.Partid, t.Projnr}).Delete(&MePart{})
	if err != nil {
		return false
	}
	return true
}

//改
func (t *MePart) Update() bool {
	e := db.Eloquent.Master()
	_, err := e.ID(core.PK{t.Finr, t.Partid, t.Projnr}).Update(t)
	if err != nil {
		return false
	}
	return true
}

//查
func (t *MePart) SelectOne() (MePart, error) {
	e := db.Eloquent.Master()
	var data MePart
	_, err := e.ID(core.PK{t.Finr, t.Partid, t.Projnr}).Get(&data)
	if err != nil {
		return data, err
	}
	return data, nil
}

//分页
func (t *MePart) GetPage(pageSize int, pageIndex int) ([]MePart, int, error) {
	data := make([]MePart, 0)
	e := db.Eloquent.Master()
	table := e.Table("me_part").Where("finr = ? ", t.Finr)
	where := "where finr = " + "'" + utils.ValueToString(t.Finr, "") + "'"
	if t.Partid != "" {
		table = table.And("partid = ?", t.Partid)
		where += " and partid = " + "'" + utils.ValueToString(t.Partid, "") + "'"
	}
	if t.Projnr != "" {
		table = table.And("projnr = ?", t.Projnr)
		where += " and projnr = " + "'" + utils.ValueToString(t.Projnr, "") + "'"
	}
	if t.Custpartid != "" {
		table = table.And("custpartid = ?", t.Custpartid)
		where += " and custpartid = " + "'" + utils.ValueToString(t.Custpartid, "") + "'"
	}

	Offset := (pageIndex - 1) * pageSize
	err := e.SQL("SELECT TOP " + utils.ValueToString(pageSize, "") + " me_part.* FROM me_part " + where + " AND (convert(varchar(10),finr)+convert(varchar(40),partid)+convert(varchar(40),projnr) NOT IN (SELECT TOP " + utils.ValueToString(Offset, "") + " convert(varchar(10),finr)+convert(varchar(40),partid)+convert(varchar(40),projnr) FROM me_part " + where + " ORDER BY credatuz DESC)) ORDER BY credatuz DESC").Find(&data)

	pcount := new(MePart)
	count, err := table.Count(pcount)
	if err != nil {
		return nil, 0, err
	}
	for k, _ := range data {
		data[k].Clipped()
	}
	return data, int(count), nil
}

//查
func (t *MePart) SelectAll() ([]MePart, error) {
	e := db.Eloquent.Master()

	var data []MePart
	err := e.Where("finr = ? and projnr", t.Finr, t.Projnr).Find(&data)
	if err != nil {
		return data, err
	}
	for k, _ := range data {
		data[k].Clipped()
	}
	return data, nil
}

//导出报表
func (t *MePart) ReadData() (string, string, error) {
	datalist := make([]MePart, 0)
	e := db.Eloquent.Master()
	table := e.Table("me_part").Where("finr = ? ", t.Finr)
	if t.Partid != "" {
		table = table.And("partid = ?", t.Partid)
	}
	if t.Projnr != "" {
		table = table.And("projnr = ?", t.Projnr)
	}
	if t.Custpartid != "" {
		table = table.And("custpartid = ?", t.Custpartid)
	}
	err := table.Find(&datalist)
	if err != nil {
		return "", "", err
	}
	for k, _ := range datalist {
		datalist[k].Clipped()
	}

	var file *xlsx.File
	var sheet *xlsx.Sheet
	var row *xlsx.Row
	var cell *xlsx.Cell

	file = xlsx.NewFile()
	sheet, err = file.AddSheet("Sheet1")
	if err != nil {
		return "", "", err
	}

	tableHead := true

	for _, val := range datalist {
		if tableHead {
			//添加表头
			row = sheet.AddRow()
			//零件Id
			cell = row.AddCell()
			cell.Value = "零件Id"
			//项目号
			cell = row.AddCell()
			cell.Value = "项目号"
			//描述1(英文)
			cell = row.AddCell()
			cell.Value = "描述1(英文)"
			//描述2(中文)
			cell = row.AddCell()
			cell.Value = "描述2(中文)"
			//状态
			cell = row.AddCell()
			cell.Value = "状态"
			//客户零件号
			cell = row.AddCell()
			cell.Value = "客户零件号"
			//内部零件号
			cell = row.AddCell()
			cell.Value = "内部零件号"
			//原零件号
			cell = row.AddCell()
			cell.Value = "原零件号"
			//文本
			cell = row.AddCell()
			cell.Value = "文本"
			//修改人
			cell = row.AddCell()
			cell.Value = "修改人"
			//更改时间
			cell = row.AddCell()
			cell.Value = "更改时间"
			//创建时间
			cell = row.AddCell()
			cell.Value = "创建时间"
			tableHead = false
		}
		row = sheet.AddRow()
		//零件Id
		cell = row.AddCell()
		cell.Value = utils.ValueToString(val.Partid, "")
		//项目号
		cell = row.AddCell()
		cell.Value = utils.ValueToString(val.Projnr, "")
		//描述1(英文)
		cell = row.AddCell()
		cell.Value = utils.ValueToString(val.Descr1, "")
		//描述2(中文)
		cell = row.AddCell()
		cell.Value = utils.ValueToString(val.Descr2, "")
		//状态
		cell = row.AddCell()
		cell.Value = utils.ValueToString(val.Status, "")
		//客户零件号
		cell = row.AddCell()
		cell.Value = utils.ValueToString(val.Custpartid, "")
		//内部零件号
		cell = row.AddCell()
		cell.Value = utils.ValueToString(val.Intpartid, "")
		//原零件号
		cell = row.AddCell()
		cell.Value = utils.ValueToString(val.Oripartid, "")
		//文本
		cell = row.AddCell()
		cell.Value = utils.ValueToString(val.Ai, "")
		//修改人
		cell = row.AddCell()
		cell.Value = utils.ValueToString(val.Lastuser, "")
		//更改时间
		cell = row.AddCell()
		cell.Value = utils.ValueToString(val.Lastmodif, "")
		//创建时间
		cell = row.AddCell()
		cell.Value = utils.ValueToString(val.Credatuz, "")
	}
	name := utils.MakeOrderSn("零件主数据")
	filename := name + ".xlsx" //文件名

	exceldir,_ := utils.GetCurrentPath("web/public/uploadxlsx") // 目录+文件名
	excelfile := exceldir + "/" + filename
	err = file.Save(excelfile)
	if err != nil {
		return "", "", err
	}
	return excelfile, filename, nil
}