package oilsupplier

import (
	"encoding/json"
	"fmt"
	"github.com/tealeg/xlsx"
	"reflect"
	"strconv"
	"strings"
	"time"

	"dashoo.cn/backend/api/business/oilsupplier/goodsaptitudeclass"

	"dashoo.cn/backend/api/business/items"

	"dashoo.cn/backend/api/business/baseUser"
	"dashoo.cn/backend/api/business/oilsupplier/goodsaptitude"
	. "dashoo.cn/backend/api/controllers"
	"dashoo.cn/business2/userRole"
	"dashoo.cn/utils"
)

type OilGoodsAptitudeController struct {
	BaseController
}

// @Title 获取列表
// @Description get user by token
// @Success 200 {object} []goodsaptitude.OilGoodsAptitude
// @router /list [get]
func (this *OilGoodsAptitudeController) GetEntityList() {

	//获取分页信息
	page := this.GetPageInfoForm()
	where := " 1=1 "
	orderby := "Code"
	asc := true
	Order := this.GetString("Order")
	Prop := this.GetString("Prop")
	if Order != "" && Prop != "" {
		orderby = Prop
		if Order == "asc" {
			asc = true
		} else {
			asc = false
		}
	}
	Edition := this.GetString("Edition")
	CreateOn := this.GetString("CreateOn")
	Code := this.GetString("Code")
	Name := this.GetString("Name")
	BigClassName := this.GetString("BigClassName")
	BigClassCode := this.GetString("BigClassCode")
	MiddleClassName := this.GetString("MiddleClassName")
	SmallClassName := this.GetString("SmallClassName")
	GoodsName := this.GetString("GoodsName")
	GoodsLevel := this.GetString("GoodsLevel")
	GoodsDesc := this.GetString("GoodsDesc")
	Standard := this.GetString("Standard")
	CompanyType := this.GetString("CompanyType")

	if Edition != "" {
		where = where + " and Edition = '" + Edition + "'"
	}
	if Code != "" {
		where = where + " and Code like '%" + Code + "%'"
	}

	if Name != "" {
		where = where + " and Name like '%" + Name + "%'"
	}

	if BigClassName != "" {
		where = where + " and Name1 like '%" + BigClassName + "%'"
	}

	if BigClassCode != "" {
		where = where + " and Code1 like '%" + BigClassCode + "%'"
	}

	if MiddleClassName != "" {
		where = where + " and Name2 like '%" + MiddleClassName + "%'"
	}

	if SmallClassName != "" {
		where = where + " and  Name3 '%" + SmallClassName + "%'"
	}

	if GoodsName != "" {
		where = where + " and Name4 like '%" + GoodsName + "%'"
	}

	if GoodsLevel != "" {
		where = where + " and GoodsLevel like '%" + GoodsLevel + "%'"
	}

	if GoodsDesc != "" {
		where = where + " and GoodsDesc like '%" + GoodsDesc + "%'"
	}

	if Standard != "" {
		where = where + " and  Standard '%" + Standard + "%'"
	}

	if CompanyType != "" {
		where = where + " and  CompanyType '%" + CompanyType + "%'"
	}

	if CreateOn != "" {
		dates := strings.Split(CreateOn, ",")
		if len(dates) == 2 {
			minDate := dates[0]
			maxDate := dates[1]
			where = where + " and CreateOn>='" + minDate + "' and CreateOn<='" + maxDate + "'"
		}
	}

	svc := goodsaptitude.GetOilGoodsAptitudeService(utils.DBE)
	var list []goodsaptitude.OilGoodsAptitudeView
	total := svc.GetMyPagingEntitiesWithOrderBytbl(OilGoodsAptDetailViewName, page.CurrentPage, page.Size, orderby, asc, &list, where)
	var datainfo DataInfo
	datainfo.Items = list
	datainfo.CurrentItemCount = total
	datainfo.PageIndex = page.CurrentPage
	datainfo.ItemsPerPage = page.Size
	this.Data["json"] = &datainfo
	this.ServeJSON()
}

// @Title 获取列表2019版本
// @Description
// @Success 200 {object}
// @router /list_2019 [get]
func (this *OilGoodsAptitudeController) GetEntityList_2019() {

	//获取分页信息
	page := this.GetPageInfoForm()
	where := " 1=1 "
	orderby := "Code"
	asc := true
	Order := this.GetString("Order")
	Prop := this.GetString("Prop")
	if Order != "" && Prop != "" {
		orderby = Prop
		if Order == "asc" {
			asc = true
		} else {
			asc = false
		}
	}
	Edition := this.GetString("Edition")
	Code := this.GetString("Code")
	Name := this.GetString("Name")

	if Edition != "" {
		where = where + " and a.Edition = '" + Edition + "'"
	}
	if Code != "" {
		where = where + " and b.Code like '%" + Code + "%'"
	}

	if Name != "" {
		where = where + " and b.Name like '%" + Name + "%'"
	}

	svc := goodsaptitude.GetOilGoodsAptitudeService(utils.DBE)
	var list []goodsaptitude.OilGoodsAptitude_2
	total := svc.GetList_2019(OilGoodsAptitudeName, OilGoodsAptitudeClassName, page.CurrentPage, page.Size, orderby, asc, &list, where)
	var datainfo DataInfo
	datainfo.Items = list
	datainfo.CurrentItemCount = total
	datainfo.PageIndex = page.CurrentPage
	datainfo.ItemsPerPage = page.Size
	this.Data["json"] = &datainfo
	this.ServeJSON()
}

// @Title 获取字典列表
// @Description get user by token
// @Success 200 {object} map[string]interface{}
// @router /dictlist [get]
func (this *OilGoodsAptitudeController) GetDictList() {
	dictList := make(map[string]interface{})
	dictSvc := items.GetItemsService(utils.DBE)
	userSvc := baseUser.GetBaseUserService(utils.DBE)
	//customerSvc := svccustomer.GetCustomerService(utils.DBE)
	//dictList["WellNo"] = dictSvc.GetKeyValueItems("WellNo", "")
	var userEntity userRole.Base_User
	userSvc.GetEntityById(this.User.Id, &userEntity)
	dictList["Supervisers"] = userSvc.GetUserListByDepartmentId("", userEntity.Departmentid)

	dictList["AuditStep"] = dictSvc.GetKeyValueItems("AuditStep", this.User.AccCode)

	//var dictCustomer []svccustomer.Customer
	//customerSvc.GetEntitysByWhere("" + CustomerName, "", &dictCustomer)
	//dictList["EntrustCorp"] = &dictCustomer

	var datainfo DataInfo
	datainfo.Items = dictList
	this.Data["json"] = &datainfo
	this.ServeJSON()
}

// @Title 获取实体
// @Description 获取实体
// @Success 200 {object} goodsaptitude.OilGoodsAptitude
// @router /get/:id [get]
func (this *OilGoodsAptitudeController) GetEntity() {
	Id := this.Ctx.Input.Param(":id")

	var model goodsaptitude.OilGoodsAptitudeView
	svc := goodsaptitude.GetOilGoodsAptitudeService(utils.DBE)
	//svc.GetEntityByIdBytbl(""+OilGoodsAptitudeName, Id, &model)
	where := " Id  =" + Id
	svc.GetEntityByWhere(OilGoodsAptDetailViewName, where, &model)
	this.Data["json"] = &model
	this.ServeJSON()
}

// @Title 获取实体
// @Description 获取实体
// @Success 200 {object}
// @router /get_2019/:id [get]
func (this *OilGoodsAptitudeController) GetEntity_2019() {
	Id := this.Ctx.Input.Param(":id")
	svc := goodsaptitude.GetOilGoodsAptitudeService(utils.DBE)

	var tempMap []map[string]string
	sql := `select a.Id,a.ClassId,a.F01,a.F02,a.F03,a.F04,a.F05,a.F06,a.F07,a.F08,a.F09,a.F10,
a.F11,a.F12,a.F13,a.F14,a.F15,a.F16,a.F17,a.F18,a.F19,a.F20, 
a.F21,a.F22,a.F23,a.F24,a.F25,a.F26,a.F27,a.F28,a.F29,a.F30, 
a.F31,a.F32,a.F33,a.F34,a.F35,a.F36,a.F37,a.F38,a.F39,a.F40, 
a.F41,a.F42,a.F43,a.F44,a.F45,a.F46,b.Code, b.Name `
	sql += ` from ` + OilGoodsAptitudeName + ` a `
	sql += ` left join ` + OilGoodsAptitudeClassName + " b on b.Id = a.ClassId"
	sql += ` where a.Id='` + Id + `'`
	tempMap, _ = svc.DBE.QueryString(sql)

	this.Data["json"] = &tempMap
	this.ServeJSON()
}

// @Title 添加
// @Description 新增
// @Success	200	{object} controllers.Request
// @router /add/:id [post]
func (this *OilGoodsAptitudeController) AddEntity() {
	classId := this.Ctx.Input.Param(":id")
	var model goodsaptitude.OilGoodsAptitude
	var jsonBlob = this.Ctx.Input.RequestBody
	svc := goodsaptitude.GetOilGoodsAptitudeService(utils.DBE)
	json.Unmarshal(jsonBlob, &model)
	model.CreateOn = time.Now()
	model.CreateBy = this.User.Realname
	model.CreateUserId, _ = utils.StrTo(this.User.Id).Int()
	model.ClassId, _ = strconv.Atoi(classId)
	//model.OrganizeId, _ = utils.StrTo(this.User.DepartmentId).Int()
	_, err := svc.InsertEntityBytbl(""+OilGoodsAptitudeName, &model)

	var errinfo ErrorDataInfo
	if err == nil {
		//新增
		errinfo.Message = "添加成功！"
		errinfo.Code = 0
		errinfo.Item = model.Id
		this.Data["json"] = &errinfo
		this.ServeJSON()
	} else {
		errinfo.Message = "添加失败！" + utils.AlertProcess(err.Error())
		errinfo.Code = -1
		this.Data["json"] = &errinfo
		this.ServeJSON()
	}
}

// @Title 添加
// @Description 新增
// @Success	200	{object} controllers.Request
// @router /add_2019 [post]
func (this *OilGoodsAptitudeController) AddEntity_2019() {
	svc := goodsaptitude.GetOilGoodsAptitudeService(utils.DBE)
	var model goodsaptitudeclass.OilGoodsAptitudeClass
	var jsonBlob = this.Ctx.Input.RequestBody
	json.Unmarshal(jsonBlob, &model)
	model.ParentId = -1
	model.Edition = "2"
	model.CreateOn = time.Now()
	model.CreateBy = this.User.Realname
	model.CreateUserId, _ = utils.StrTo(this.User.Id).Int()
	_, err := svc.InsertEntityBytbl(""+OilGoodsAptitudeClassName, &model)

	var model2 goodsaptitude.OilGoodsAptitude
	json.Unmarshal(jsonBlob, &model2)
	model2.ClassId = model.Id
	model2.Edition = "2"
	model2.CreateOn = time.Now()
	model2.CreateBy = this.User.Realname
	model2.CreateUserId, _ = utils.StrTo(this.User.Id).Int()

	_, err = svc.InsertEntityBytbl(""+OilGoodsAptitudeName, &model2)

	var errinfo ErrorDataInfo
	if err == nil {
		//新增
		errinfo.Message = "添加成功！"
		errinfo.Code = 0
		errinfo.Item = model2.Id
		this.Data["json"] = &errinfo
		this.ServeJSON()
	} else {
		errinfo.Message = "添加失败！" + utils.AlertProcess(err.Error())
		errinfo.Code = -1
		this.Data["json"] = &errinfo
		this.ServeJSON()
	}
}

// @Title 修改实体
// @Description 修改实体
// @Success	200	{object} controllers.Request
// @router /update/:id [post]
func (this *OilGoodsAptitudeController) UpdateEntity() {
	id := this.Ctx.Input.Param(":id")
	var errinfo ErrorInfo
	if id == "" {
		errinfo.Message = "操作失败！请求信息不完整"
		errinfo.Code = -2
		this.Data["json"] = &errinfo
		this.ServeJSON()
		return
	}

	var model goodsaptitude.OilGoodsAptitude
	svc := goodsaptitude.GetOilGoodsAptitudeService(utils.DBE)

	var jsonBlob = this.Ctx.Input.RequestBody
	json.Unmarshal(jsonBlob, &model)

	model.ModifiedOn = time.Now()
	model.ModifiedBy = this.User.Realname
	model.ModifiedUserId, _ = utils.StrTo(this.User.Id).Int()

	cols := []string{

		"Id",

		"Code",

		"Name",

		"BigClassName",

		"MiddleClassName",

		"SmallClassName",

		"GoodsName",

		"GoodsLevel",

		"GoodsDesc",

		"Standard",

		"CompanyType",

		"F01",

		"F02",

		"F03",

		"F04",

		"F05",

		"F06",

		"F07",

		"F08",

		"F09",

		"F10",

		"F11",

		"F12",

		"F13",

		"F14",

		"F15",

		"F16",

		"F17",

		"F18",

		"F19",

		"F20",

		"F21",

		"F22",

		"F23",

		"F24",

		"F25",

		"F26",

		"F27",

		"F28",

		"F29",

		"F30",

		"F31",

		"F32",

		"F33",

		"F34",

		"F35",

		"F36",

		"F37",

		"F38",

		"F39",

		"F40",

		"F41",

		"F42",

		"F43",

		"F44",

		"F45",

		"F46",

		"Remark",

		"DeletionStateCode",

		"CreateOn",

		"CreateUserId",

		"CreateBy",

		"ModifiedOn",

		"ModifiedUserId",

		"ModifiedBy",
	}
	err := svc.UpdateEntityBytbl(""+OilGoodsAptitudeName, id, &model, cols)
	if err == nil {
		errinfo.Message = "修改成功！"
		errinfo.Code = 0
		this.Data["json"] = &errinfo
		this.ServeJSON()
	} else {
		errinfo.Message = "修改失败！" + utils.AlertProcess(err.Error())
		errinfo.Code = -1
		this.Data["json"] = &errinfo
		this.ServeJSON()
	}
}

// @Title 修改实体
// @Description 修改实体
// @Success	200	{object} controllers.Request
// @router /update_2019/:id [post]
func (this *OilGoodsAptitudeController) UpdateEntity_2019() {
	id := this.Ctx.Input.Param(":id")
	svc := goodsaptitude.GetOilGoodsAptitudeService(utils.DBE)

	var errinfo ErrorInfo
	if id == "" {
		errinfo.Message = "操作失败！请求信息不完整"
		errinfo.Code = -2
		this.Data["json"] = &errinfo
		this.ServeJSON()
		return
	}

	var model goodsaptitude.OilGoodsAptitude
	var jsonBlob = this.Ctx.Input.RequestBody
	json.Unmarshal(jsonBlob, &model)

	model.ModifiedOn = time.Now()
	model.ModifiedBy = this.User.Realname
	model.ModifiedUserId, _ = utils.StrTo(this.User.Id).Int()

	cols := []string{
		"F01",

		"F02",

		"F03",

		"F04",

		"F05",

		"F06",

		"F07",

		"F08",

		"F09",

		"F10",

		"F11",

		"F12",

		"F13",

		"F14",

		"F15",

		"F16",

		"F17",

		"F18",

		"F19",

		"F20",

		"F21",

		"F22",

		"F23",

		"F24",

		"F25",

		"F26",

		"F27",

		"F28",

		"F29",

		"F30",

		"F31",

		"F32",

		"F33",

		"F34",

		"F35",

		"F36",

		"F37",

		"F38",

		"F39",

		"F40",

		"F41",

		"F42",

		"F43",

		"F44",

		"F45",

		"F46",

		"ModifiedOn",

		"ModifiedUserId",

		"ModifiedBy",
	}
	err := svc.UpdateEntityBytbl(""+OilGoodsAptitudeName, id, &model, cols)

	var model2 goodsaptitudeclass.OilGoodsAptitudeClass
	json.Unmarshal(jsonBlob, &model2)
	model2.ModifiedOn = time.Now()
	model2.ModifiedBy = this.User.Realname
	model2.ModifiedUserId, _ = utils.StrTo(this.User.Id).Int()

	cols2 := []string{
		"Code",

		"Name",

		"ModifiedOn",

		"ModifiedUserId",

		"ModifiedBy",
	}
	err = svc.UpdateEntityBytbl(""+OilGoodsAptitudeClassName, model.ClassId, &model2, cols2)
	if err == nil {
		errinfo.Message = "修改成功！"
		errinfo.Code = 0
		this.Data["json"] = &errinfo
		this.ServeJSON()
	} else {
		errinfo.Message = "修改失败！" + utils.AlertProcess(err.Error())
		errinfo.Code = -1
		this.Data["json"] = &errinfo
		this.ServeJSON()
	}
}

// @Title 修改实体
// @Description 修改实体
// @Success	200	{object} controllers.Request
// @router /updatecode/:id [post]
func (this *OilGoodsAptitudeController) UpdateCodeEntity() {
	var errinfo ErrorInfo
	var err error
	id := this.Ctx.Input.Param(":id")
	if id == "" {
		errinfo.Message = "操作失败！请求信息不完整"
		errinfo.Code = -2
		this.Data["json"] = &errinfo
		this.ServeJSON()
		return
	}

	var classall goodsaptitude.OilGoodsAptitudeClassView
	var model goodsaptitudeclass.OilGoodsAptitudeClass
	var classmodel []goodsaptitudeclass.OilGoodsAptitudeClass
	svc := goodsaptitude.GetOilGoodsAptitudeService(utils.DBE)
	var jsonBlob = this.Ctx.Input.RequestBody
	json.Unmarshal(jsonBlob, &classall)
	model.ModifiedOn = time.Now()
	model.ModifiedBy = this.User.Realname
	model.ModifiedUserId, _ = utils.StrTo(this.User.Id).Int()
	colsName := []string{"Name"}
	colsCode := []string{"Code"}
	//svc.GetEntityById(id, &model)
	if classall.Name1 != "" {
		where := " Name = '" + classall.Name1 + "' and Id  <> " + utils.ToStr(classall.Id1)
		svc.GetEntitysByWhere(OilGoodsAptitudeClassName, where, &classmodel)
		if len(classmodel) > 0 {
			errinfo.Message = "大类名称已存在，请重新添加！"
			errinfo.Code = -1
			this.Data["json"] = &errinfo
			this.ServeJSON()
			return
		} else {
			model.Name = classall.Name1
			err = svc.UpdateEntityBytbl(OilGoodsAptitudeClassName, classall.Id1, &model, colsName)
		}
	}
	if classall.Name2 != "" {
		where := " Name = '" + classall.Name2 + "' and Id  <> " + utils.ToStr(classall.Id2)
		svc.GetEntitysByWhere(OilGoodsAptitudeClassName, where, &classmodel)
		if len(classmodel) > 0 {
			errinfo.Message = "中类名称已存在，请重新添加！"
			errinfo.Code = -1
			this.Data["json"] = &errinfo
			this.ServeJSON()
			return
		} else {
			model.Name = classall.Name2
			err = svc.UpdateEntityBytbl(OilGoodsAptitudeClassName, classall.Id2, &model, colsName)
		}
	}
	if classall.Name3 != "" {
		where := " Name = '" + classall.Name3 + "' and Id  <> " + utils.ToStr(classall.Id3)
		svc.GetEntitysByWhere(OilGoodsAptitudeClassName, where, &classmodel)
		if len(classmodel) > 0 {
			errinfo.Message = "小类名称已存在，请重新添加！"
			errinfo.Code = -1
			this.Data["json"] = &errinfo
			this.ServeJSON()
			return
		} else {
			model.Name = classall.Name3
			err = svc.UpdateEntityBytbl(OilGoodsAptitudeClassName, classall.Id3, &model, colsName)
		}
	}
	if classall.Name4 != "" {
		where := " Name = '" + classall.Name4 + "' and Id  <> " + utils.ToStr(classall.Id4)
		svc.GetEntitysByWhere(OilGoodsAptitudeClassName, where, &classmodel)
		if len(classmodel) > 0 {
			errinfo.Message = "品名已存在，请重新添加！"
			errinfo.Code = -1
			this.Data["json"] = &errinfo
			this.ServeJSON()
			return
		} else {
			model.Name = classall.Name4
			err = svc.UpdateEntityBytbl(OilGoodsAptitudeClassName, classall.Id4, &model, colsName)
		}
	}
	if classall.Code1 != "" {
		where := " Code = " + classall.Code1 + " and Id <> " + utils.ToStr(classall.Id1)
		svc.GetEntitysByWhere(OilGoodsAptitudeClassName, where, &classmodel)
		if len(classmodel) > 0 {
			errinfo.Message = "大类编码已存在，请重新添加！"
			errinfo.Code = -1
			this.Data["json"] = &errinfo
			this.ServeJSON()
			return
		} else {
			model.Code = classall.Code1
			err = svc.UpdateEntityBytbl(OilGoodsAptitudeClassName, classall.Id1, &model, colsCode)
		}
	}
	if classall.Code2 != "" {
		where := " Code = " + classall.Code2 + " and Id <> " + utils.ToStr(classall.Id2)
		svc.GetEntitysByWhere(OilGoodsAptitudeClassName, where, &classmodel)
		if len(classmodel) > 0 {
			errinfo.Message = "中类编码已存在，请重新添加！"
			errinfo.Code = -1
			this.Data["json"] = &errinfo
			this.ServeJSON()
			return
		} else {
			model.Code = classall.Code2
			err = svc.UpdateEntityBytbl(OilGoodsAptitudeClassName, classall.Id2, &model, colsCode)
		}
	}
	if classall.Code3 != "" {
		where := " Code = " + classall.Code3 + " and Id <> " + utils.ToStr(classall.Id3)
		svc.GetEntitysByWhere(OilGoodsAptitudeClassName, where, &classmodel)
		if len(classmodel) > 0 {
			errinfo.Message = "小类编码已存在，请重新添加！"
			errinfo.Code = -1
			this.Data["json"] = &errinfo
			this.ServeJSON()
			return
		} else {
			model.Code = classall.Code3
			err = svc.UpdateEntityBytbl(OilGoodsAptitudeClassName, classall.Id3, &model, colsCode)
		}
	}
	if classall.Code4 != "" {
		where := " Code = " + classall.Code4 + " and Id <> " + utils.ToStr(classall.Id4)
		svc.GetEntitysByWhere(OilGoodsAptitudeClassName, where, &classmodel)
		if len(classmodel) > 0 {
			errinfo.Message = "品名编码已存在，请重新添加！"
			errinfo.Code = -1
			this.Data["json"] = &errinfo
			this.ServeJSON()
			return
		} else {
			model.Code = classall.Code4
			err = svc.UpdateEntityBytbl(OilGoodsAptitudeClassName, classall.Id4, &model, colsCode)
		}
	}
	if err == nil {
		errinfo.Message = "修改成功！"
		errinfo.Code = 0
		this.Data["json"] = &errinfo
		this.ServeJSON()
	} else {
		errinfo.Message = "修改失败！" + utils.AlertProcess(err.Error())
		errinfo.Code = -1
		this.Data["json"] = &errinfo
		this.ServeJSON()
	}
}

// @Title 删除单条信息
// @Description
// @Success 200 {object} ErrorInfo
// @Failure 403 :id 为空
// @router /delete/:Id [delete]
func (this *OilGoodsAptitudeController) DeleteEntity() {
	Id := this.Ctx.Input.Param(":Id")
	var errinfo ErrorInfo
	if Id == "" {
		errinfo.Message = "操作失败！请求信息不完整"
		errinfo.Code = -2
		this.Data["json"] = &errinfo
		this.ServeJSON()
		return
	}
	var model goodsaptitude.OilGoodsAptitude
	var entityempty goodsaptitude.OilGoodsAptitude
	svc := goodsaptitude.GetOilGoodsAptitudeService(utils.DBE)
	opdesc := "删除-" + Id
	err := svc.DeleteOperationAndWriteLogBytbl(""+OilGoodsAptitudeName, BaseOperationLogName, Id, &model, &entityempty, utils.ToStr(this.User.Id), this.User.Username, opdesc, "", "钻井日报")
	if err == nil {
		errinfo.Message = "删除成功"
		errinfo.Code = 0
		this.Data["json"] = &errinfo
		this.ServeJSON()
	} else {
		errinfo.Message = "删除失败！" + utils.AlertProcess(err.Error())
		errinfo.Code = -1
		this.Data["json"] = &errinfo
		this.ServeJSON()
	}
}

// @Title get 查询
// @Description get SampleType by token
// @Success 200 {object} sampletype.SampleType
// @router /goodsparentlist/:name [get]
func (this *OilGoodsAptitudeController) GoodsParentList() {
	Name := this.Ctx.Input.Param(":name")
	if Name != "" {
		svc := goodsaptitude.GetOilGoodsAptitudeService(utils.DBE)
		where1 := "1=1"
		where1 += " AND Name LIKE '%" + Name + "%' OR Code LIKE '" + Name + "%' "

		sqlStr := "SELECT Id, Code, Name, concat(Code, '  ', Name) as CodeName, ParentId FROM OilGoodsAptitudeClass " +
			"WHERE " + where1 + " AND Edition='1' AND DeletionStateCode = 0 ORDER BY Code"
		list, _ := svc.DBE.QueryString(sqlStr)
		/*
			sql := "SELECT Id, Code, Name, concat(Code, '  ', Name) as CodeName, ParentId FROM OilGoodsAptitudeClass " +
					 "WHERE DeletionStateCode = 0 AND " +  where1 + " OR Id = (" + sqlStr + ")" +" ORDER BY Code"
			list, _ := svc.DBE.QueryString(sql)*/
		var datainfo DataInfo
		datainfo.Items = list
		this.Data["json"] = &datainfo
		this.ServeJSON()
	}
}

// @Title get 获取子类
// @Description get SampleType by token
// @Success 200 {object} sampletype.SampleType
// @router /goodschildlist/:id [get]
func (this *OilGoodsAptitudeController) GoodsChildList() {
	ParentId := this.Ctx.Input.Param(":id")
	sqlStr := "SELECT Id, `Code`, `Name`, concat(Code, '  ', Name) as CodeName, ParentId FROM OilGoodsAptitudeClass WHERE FIND_IN_SET(ParentId, fun_getOilGoodsAptitudeClasschildlist(" + ParentId + ")) AND DeletionStateCode = 0 order by Code"
	svc := goodsaptitude.GetOilGoodsAptitudeService(utils.DBE)
	list, _ := svc.DBE.QueryString(sqlStr)
	var datainfo DataInfo
	datainfo.Items = list
	this.Data["json"] = &datainfo
	this.ServeJSON()
}

// @Title get 获取子类
// @Description get SampleType by token
// @Success 200 {object} sampletype.SampleType
// @router /goodschildlistbypid/:parentid [get]
func (this *OilGoodsAptitudeController) GoodsChildLisByParentId() {
	ParentId := this.Ctx.Input.Param(":parentid")
	sqlStr := "SELECT Id, `Code`, `Name`, concat(Code, '  ', Name) as CodeName, ParentId, (CASE WHEN length(Code)>=8 THEN '1' ELSE '0' END) as Leaf FROM OilGoodsAptitudeClass WHERE ParentId = '" + ParentId + "' AND Edition='1' AND DeletionStateCode = 0 order by Code"
	svc := goodsaptitude.GetOilGoodsAptitudeService(utils.DBE)
	list, _ := svc.DBE.QueryString(sqlStr)
	var datainfo DataInfo
	datainfo.Items = list
	this.Data["json"] = &datainfo
	this.ServeJSON()
}

// @Title get 导出ex
// @Description get SampleType by token
// @Success 200 {object} sampletype.SampleType
// @router /exportexcelall [get]
func (this *OilGoodsAptitudeController) ExportExcelAll() {
	//获取分页信息
	//page := this.GetPageInfoForm()
	where := " 1=1 "
	orderby := "Code"
	asc := true
	Order := this.GetString("Order")
	Prop := this.GetString("Prop")
	if Order != "" && Prop != "" {
		orderby = Prop
		if Order == "asc" {
			asc = true
		} else {
			asc = false
		}
	}
	Edition := this.GetString("Edition")

	if Edition != "" {
		where = where + " and Edition = '" + Edition + "'"
	}

	t := time.Now()
	svc := goodsaptitude.GetOilGoodsAptitudeService(utils.DBE)
	var list []goodsaptitude.OilGoodsAptitudeView
	svc.GetMyPagingEntitiesWithOrderBytbl(OilGoodsAptDetailViewName, 0, 0, orderby, asc, &list, where)

	var title []string
	filetitle := "物资类"
	//自定义显示列
	showcolumnarr := this.GetString("showcolumnarr")
	showcolumnnamearr := this.GetString("showcolumnnamearr")
	titlestring := showcolumnnamearr
	title = strings.Split(titlestring, ",")
	f := xlsx.NewFile()
	sheet, _ := f.AddSheet(filetitle)
	//rowhead := sheet.AddRow()
	//rowhead.WriteSlice(&title, -1)
	cellname := strings.Split(showcolumnarr, ",")
	row := sheet.AddRow()
	row.WriteSlice(&cellname, -1)

	for _, item := range list {
		var enumModel goodsaptitude.OilGoodsAptitudeView
		tmpModel := &item
		enumModel = *tmpModel
		immumodel := reflect.ValueOf(&enumModel)
		elem := immumodel.Elem()
		row := sheet.AddRow()
		for _, name := range title {
			cell := row.AddCell()
			if strings.HasPrefix(name, "F") {
				var val = elem.FieldByName(name).String()
				if val == "1" {
					cell.Value = "是"
				} else {
					cell.Value = ""
				}
			} else {
				cell.Value = elem.FieldByName(name).String()
			}
		}
	}

	for c, cl := 0, len(sheet.Cols); c < cl; c++ {
		sheet.Cols[c].Width = 20
	}
	//this.SaveSampleXlsx(filetitle, title, showcolumnarr, list, f)

	dir := "static/file/excel/report/" + this.GetAccode()
	SaveDirectory(dir)
	path := dir + "/" + utils.TimeFormat(time.Now(), "200612") + filetitle + ".xlsx"
	f.Save(path)
	this.Data["json"] = this.Ctx.Request.Host + "/" + path
	this.ServeJSON()
	elapsed := time.Since(t)
	fmt.Println(elapsed)
}