biobank/src/dashoo.cn/backend/api/business/samplesinfo/samplesinfoService.go

package samplesinfo

import (
	"encoding/json"
	"fmt"
	"os"
	"strconv"
	"strings"
	"time"

	"github.com/astaxie/beego"

	"dashoo.cn/backend/api/business/baseUser"
	"dashoo.cn/backend/api/business/donorsinfo"
	"dashoo.cn/backend/api/business/sampleimporttemplate"

	//	"dashoo.cn/backend/api/business/samplenoteitem"
	"dashoo.cn/backend/api/business/samplesbusiness"
	"dashoo.cn/backend/api/business/sampletype"
	"dashoo.cn/backend/api/business/shelfset"
	. "dashoo.cn/backend/api/models"
	. "dashoo.cn/backend/api/mydb"
	"dashoo.cn/business2/userRole"
	"dashoo.cn/utils"
	. "dashoo.cn/utils/db"
	"github.com/go-xorm/xorm"
	"github.com/tealeg/xlsx"
)

type SamplesInfoService struct {
	MyServiceBase
}

func GetSamplesInfoService(xormEngine *xorm.Engine) *SamplesInfoService {
	s := new(SamplesInfoService)
	s.DBE = xormEngine
	return s
}

func (s *SamplesInfoService) GetPagingEntitiesWithOrderSearch(acccode string, pageIndex, itemsPerPage int64, order, where string) (int64, []SamplesInfoList) {
	var err error
	var total int64
	if order != "" {
		order = " order by " + order
	}
	//获取总记录数
	if where == "" {
		where = "1=1"
	}
	tbldetail := acccode + SamplesDetailtbName
	tblmain := acccode + SamplesMaintbName
	//当是蜜蜂所用户时，取acccode + AnimalInfoName  动物表数据
	tbldonor := ""
	if(strings.EqualFold(acccode,"sBBo4")){
		tbldonor = acccode + AnimalInfoName
	}else{
		tbldonor = acccode + DonorstbName
	}
	groupdetail := acccode + GroupDetailName

	sqlCount := " select count(*) from " + tbldetail + " a  left join " + tblmain + " b on a.SampleCode = b.SampleCode left join " + tbldonor + " c on b.SourceId = c.Id left join Shelf d on a.ShelfId=d.Id left join Box e on a.BoxId=e.Id left join Equipment f on a.EquipmentId=f.Id left join " + groupdetail + " g on g.SampleId=a.Id where " + where

	fmt.Println(sqlCount)
	var sql string

	sql = ` select a.*,b.*,c.FamilyState,c.ClinicNum,d.XStation ShelfX,d.YStation ShelfY,d.Code ShelfCode,e.XStation BoxX,e.YStation BoxY,e.Code BoxCode,f.Id Eid,f.Name EName,f.Code ECode,g.GroupId,g.GroupName from ` + tbldetail + ` a 
	left join ` + tblmain + ` b on a.SampleCode = b.SampleCode  
	left join ` + tbldonor + ` c on b.SourceId = c.Id 
	left join Shelf d on a.ShelfId=d.Id 
	left join Box e on a.BoxId=e.Id 
	left join Equipment f on a.EquipmentId=f.Id 
	left join ` + groupdetail + ` g on g.SampleId=a.Id 
	where ` + where + order + `
		 limit ` + utils.ToStr((pageIndex-1)*itemsPerPage) + "," + utils.ToStr(itemsPerPage) + ""
	fmt.Println(sql)
	List := make([]SamplesInfoList, 0)
	utils.DBE.Sql(sql).Find(&List)
	resultsSlice, err := s.DBE.Query(sqlCount)
	LogError(err)

	if len(resultsSlice) > 0 {
		results := resultsSlice[0]
		for _, value := range results {
			total, err = strconv.ParseInt(string(value), 10, 64)
			LogError(err)
			break
		}
	}
	return total, List
}

func (s *SamplesInfoService) GetPagingEntitiesWithTb(acccode string, pageIndex, itemsPerPage int64, tbldonor, order, where string) (int64, []SamplesInfoList) {
	var err error
	var total int64
	if order != "" {
		order = " order by " + order
	}
	//获取总记录数
	if where == "" {
		where = "1=1"
	}
	tbldetail := acccode + SamplesDetailtbName
	tblmain := acccode + SamplesMaintbName
	groupdetail := acccode + GroupDetailName

	sqlCount := " select count(*) from " + tbldetail + " a  left join " + tblmain + " b on a.SampleCode = b.SampleCode left join " + tbldonor + " c on b.SourceId = c.Id left join Shelf d on a.ShelfId=d.Id left join Box e on a.BoxId=e.Id left join Equipment f on a.EquipmentId=f.Id left join " + groupdetail + " g on g.SampleId=a.Id where " + where

	fmt.Println(sqlCount)
	var sql string

	//sql = ` select a.*,b.*,c.Genus,concat(c.ProvinceName,c.CityName,c.StreetName,c.Address) as AddressName,c.SurveyDate,d.XStation ShelfX,d.YStation ShelfY,d.Code ShelfCode,e.XStation BoxX,e.YStation BoxY,e.Code BoxCode,f.Id Eid,f.Name EName,f.Code ECode,g.GroupId,g.GroupName from ` + tbldetail + ` a
	sql = ` select a.*,b.*,c.Genus,concat(c.ProvinceName,c.CityName,c.StreetName,c.Address) as AddressName,c.SurveyDate,d.XStation ShelfX,d.YStation ShelfY,d.Code ShelfCode,e.XStation BoxX,e.YStation BoxY,e.Code BoxCode,f.Id Eid,f.Name EName,f.Code ECode from ` + tbldetail + ` a
	left join ` + tblmain + ` b on a.SampleCode = b.SampleCode  
	left join ` + tbldonor + ` c on b.SourceId = c.Id 
	left join Shelf d on a.ShelfId=d.Id 
	left join Box e on a.BoxId=e.Id 
	left join Equipment f on a.EquipmentId=f.Id 
	
	where ` + where + order + `
		 limit ` + utils.ToStr((pageIndex-1)*itemsPerPage) + "," + utils.ToStr(itemsPerPage) + ""
	fmt.Println(sql)
	List := make([]SamplesInfoList, 0)
	utils.DBE.Sql(sql).Find(&List)
	resultsSlice, err := s.DBE.Query(sqlCount)
	LogError(err)

	if len(resultsSlice) > 0 {
		results := resultsSlice[0]
		for _, value := range results {
			total, err = strconv.ParseInt(string(value), 10, 64)
			LogError(err)
			break
		}
	}
	return total, List
}

func (s *SamplesInfoService) GetPagingEntitiesWithDonorsTb(acccode string, pageIndex, itemsPerPage int64, tbldonor, order, where string) (int64, []SamplesInfoList) {
	var err error
	var total int64
	if order != "" {
		order = " order by " + order
	}
	//获取总记录数
	if where == "" {
		where = "1=1"
	}
	tbldetail := acccode + SamplesDetailtbName
	tblmain := acccode + SamplesMaintbName
	groupdetail := acccode + GroupDetailName

	sqlCount := " select count(*) from " + tbldetail + " a  left join " + tblmain + " b on a.SampleCode = b.SampleCode left join " + tbldonor + " c on b.SourceId = c.Id left join Shelf d on a.ShelfId=d.Id left join Box e on a.BoxId=e.Id left join Equipment f on a.EquipmentId=f.Id left join " + groupdetail + " g on g.SampleId=a.Id where " + where

	fmt.Println(sqlCount)
	var sql string

	sql = ` select a.*,b.*,c.*,d.XStation ShelfX,d.YStation ShelfY,d.Code ShelfCode,e.XStation BoxX,e.YStation BoxY,e.Code BoxCode,f.Id Eid,f.Name EName,f.Code ECode,g.GroupId,g.GroupName from ` + tbldetail + ` a 
	left join ` + tblmain + ` b on a.SampleCode = b.SampleCode  
	left join ` + tbldonor + ` c on b.SourceId = c.Id 
	left join Shelf d on a.ShelfId=d.Id 
	left join Box e on a.BoxId=e.Id 
	left join Equipment f on a.EquipmentId=f.Id 
	left join ` + groupdetail + ` g on g.SampleId=a.Id 
	where ` + where + order + `
		 limit ` + utils.ToStr((pageIndex-1)*itemsPerPage) + "," + utils.ToStr(itemsPerPage) + ""
	fmt.Println(sql)
	List := make([]SamplesInfoList, 0)
	utils.DBE.Sql(sql).Find(&List)
	resultsSlice, err := s.DBE.Query(sqlCount)
	LogError(err)

	if len(resultsSlice) > 0 {
		results := resultsSlice[0]
		for _, value := range results {
			total, err = strconv.ParseInt(string(value), 10, 64)
			LogError(err)
			break
		}
	}
	return total, List
}

//质控随机显示
func (s *SamplesInfoService) GetPagingEntitiesSearch(acccode string, page, size string, order, where string) (int64, []SamplesInfoList) {
	var err error
	var total int64
	if order != "" {
		order = " order by " + order
	}
	//获取总记录数
	if where == "" {
		where = "1=1"
	}
	tblmain := acccode + SamplesMaintbName
	tbldetail := acccode + SamplesDetailtbName
	tbldonor := acccode + DonorstbName

	sqlCount := " select count(*) from " + tbldetail + " a  left join " + tblmain + " b on a.SampleCode = b.SampleCode left join " + tbldonor + " c on b.SourceId = c.Id left join Shelf d on a.ShelfId=d.Id left join Box e on a.BoxId=e.Id left join Equipment f on a.EquipmentId=f.Id where " + where

	fmt.Println(sqlCount)
	var sql string

	sql = ` select a.*,b.*,c.FamilyState,c.ClinicNum,d.XStation ShelfX,d.YStation ShelfY,d.Code ShelfCode,e.XStation BoxX,e.YStation BoxY,e.Code BoxCode,f.Id Eid,f.Name EName,f.Code ECode from ` + tbldetail + ` a 
	left join ` + tblmain + ` b on a.SampleCode = b.SampleCode  
	left join ` + tbldonor + ` c on b.SourceId = c.Id 
	left join Shelf d on a.ShelfId=d.Id 
	left join Box e on a.BoxId=e.Id 
	left join Equipment f on a.EquipmentId=f.Id where ` + where + order + `
		 limit ` + page + `,` + size
	fmt.Println(sql)
	List := make([]SamplesInfoList, 0)
	utils.DBE.Sql(sql).Find(&List)
	resultsSlice, err := s.DBE.Query(sqlCount)
	LogError(err)

	if len(resultsSlice) > 0 {
		results := resultsSlice[0]
		for _, value := range results {
			total, err = strconv.ParseInt(string(value), 10, 64)
			LogError(err)
			break
		}
	}
	return total, List
}

func (s *SamplesInfoService) GetSamplesInfoListAllData(acccode string, order, where string) []SamplesInfoList {
	if order != "" {
		order = " order by " + order
	}
	//获取总记录数
	if where == "" {
		where = "1=1"
	}
	tblmain := acccode + SamplesMaintbName
	tbldetail := acccode + SamplesDetailtbName
	tbldonor := acccode + DonorstbName
	groupdetail := acccode + GroupDetailName
	var sql string

	sql = ` select a.*,b.*,c.FamilyState,c.ClinicNum,d.XStation ShelfX,d.YStation ShelfY,d.Code ShelfCode,e.XStation BoxX,e.YStation BoxY,e.Code BoxCode,f.Id Eid,f.Name EName,f.Code ECode,g.GroupName from ` + tbldetail + ` a 
	left join ` + tblmain + ` b on a.SampleCode = b.SampleCode  
	left join ` + tbldonor + ` c on b.SourceId = c.Id 
	left join Shelf d on a.ShelfId=d.Id 
	left join Box e on a.BoxId=e.Id 
	left join Equipment f on a.EquipmentId=f.Id
	left join ` + groupdetail + ` g on a.Id = g.SampleId where ` + where + order
	fmt.Println(sql)
	List := make([]SamplesInfoList, 0)
	utils.DBE.Sql(sql).Find(&List)
	return List
}

func (s *SamplesInfoService) GetArchivedSamplesInfoListAllData(acccode string, order, where string) []SamplesInfoList {
	if order != "" {
		order = " order by " + order
	}
	//获取总记录数
	if where == "" {
		where = "1=1"
	}
	tblmain := acccode + SamplesMaintbName
	tbldetail := acccode + SamplesDetailFiletbName
	tbldonor := acccode + DonorstbName
	var sql string

	sql = ` select a.*,b.*,c.FamilyState from ` + tbldetail + ` a left join ` + tblmain +
		` b on a.SampleCode = b.SampleCode  left join ` + tbldonor + ` c on b.SourceId = c.Id  where ` + where + order
	fmt.Println(sql)
	List := make([]SamplesInfoList, 0)
	utils.DBE.Sql(sql).Find(&List)
	return List
}

func (s *SamplesInfoService) QuerySampleEntity(acccode string, where string) (entity SamplesInfoShow) {
	tblmain := acccode + SamplesMaintbName
	tbldetail := acccode + SamplesDetailtbName
	sql := "select a.*,b.* from   " + tbldetail + " a left join " + tblmain +
		" b on a.SampleCode = b.SampleCode where " + where
	s.DBE.Sql(sql).Get(&entity)
	return
}

//获取样本详情 --动物
func (s *SamplesInfoService) QueryAnimalSampleEntity(acccode string, where string) (entity AnimalSamplesInfoShow) {
	tblmain := acccode + SamplesMaintbName
	tbldetail := acccode + SamplesDetailtbName
	sourcetb := acccode + AnimalInfoName

	sql := `select a.*,b.*,c.Genus,c.GenusName,concat(c.ProvinceName,c.CityName,c.StreetName,c.Address) as AddressName,c.InnerNo as SourceInner,c.Amount,c.Unit as SourceUni,c.ProjectName,c.Longitude,c.Latitude,c.Altitude,c.SurveyDate
		from   ` + tbldetail + ` a left join 
		` + tblmain + ` b on a.SampleCode = b.SampleCode 
		left join ` + sourcetb + ` c on b.SourceId = c.Id
		where ` + where
	s.DBE.Sql(sql).Get(&entity)
	return
}

//获取样本详情 2017-10-31新增，增加设备名等信息
func (s *SamplesInfoService) QuerySampleDetailEntity(acccode string, where string) (entity SamplesInDetailfoShow) {
	tblmain := acccode + SamplesMaintbName
	tbldetail := acccode + SamplesDetailtbName
	sql := `select a.*,b.*,c.Name EName,c.Code ECode,d.Code ShelfCode,e.Code BoxCode,
		d.XStation ShelfX,d.YStation ShelfY,e.XStation BoxX,e.YStation BoxY
		from   ` + tbldetail + ` a left join 
		` + tblmain + ` b on a.SampleCode = b.SampleCode 
		inner join Equipment c on a.EquipmentId=c.Id
		inner join Shelf d on a.ShelfId=d.Id
		inner join Box e on a.BoxId = e.Id
		where ` + where
	s.DBE.Sql(sql).Get(&entity)
	return
}

func (s *SamplesInfoService) DeleteDetail(tbldetail, where string) error {
	sql := "update  " + tbldetail + " set DeletionStateCode=1  where " + where
	_, err := s.DBE.Exec(sql)
	return err
}

func (s *SamplesInfoService) InsertExcelSamplesInput(excelpath, acccode, createby string, createuserid int) (err error, warn string) {
	//公共扩展list
	//svcdnote := samplenoteitem.GetSampleNoteItemService(utils.DBE)
	//wherenote := " AccCode='" + acccode + "'"
	//notecount := svcdnote.GetNoteCount(acccode) //获取扩展项目count
	//notelist := svcdnote.GetDNoteItemList(wherenote)

	tblmain := acccode + SamplesMaintbName
	tbldetail := acccode + SamplesDetailtbName
	tblfile := acccode + SamplesDetailFiletbName
	tblbuss := acccode + SamplesBusstbName
	//tbldonor := acccode + AnimalInfoName
	//tbldonor := acccode + DonorstbName
	//当是蜜蜂所用户时，取acccode + AnimalInfoName  动物表数据
	tbldonor := ""
	if(strings.EqualFold(acccode,"sBBo4")){
		tbldonor = acccode + AnimalInfoName
	}else{
		tbldonor = acccode + DonorstbName
	}


	var datadetail SamplesDetail
	var databuss samplesbusiness.SamplesBusiness //业务记录

	dir, _ := os.Getwd()
	flag := 0
	excelpath = dir + excelpath
	xlFile, err := xlsx.OpenFile(excelpath)
	if err != nil {
		beego.Error(err)
		return
	}

	svcdonors := donorsinfo.GetDonorsInfoService(utils.DBE)
	var donors_data donorsinfo.DonorsInfo
	var sampletype_data sampletype.SampleType
	var base_userrole userRole.Base_User
	svc := sampletype.GetSampleTypeService(utils.DBE)
	svc1 := baseUser.GetBaseUserService(utils.DBE)
	var warn1 string
	for sheetindex, sheet := range xlFile.Sheets { //循环页数
		sheetrows := len(xlFile.Sheets[sheetindex].Rows) //每一页的行数
		templateName := sheet.Name

		if templateName != "默认模板" {
			svc2 := sampleimporttemplate.GetSampleImporttemplateService(utils.DBE)
			where := " AccCode = '" + acccode + "'"
			where = where + " and Name='" + templateName + "'"

			var list []sampleimporttemplate.SampleImporttemplate
			svc2.GetPagingEntitiesWithSortCode(0, 1, "Id desc", &list, where)
			if len(list) == 0 {
				beego.Error("not found template:", templateName)
				continue
			}
			fields := strings.Split(list[0].Content, ",")
			for rowindex, row := range sheet.Rows { //循环行数
				notefieldstr := ""
				notevaluestr := ""
				if rowindex == 0 { //第一行是表头，跳过
					continue
				}
				if row.Cells == nil { //小于必填项的19列，不允许导入(改为跳过)
					continue
				}

				basestation := new(SamplesInfoShow)
				for cellindex, cell := range row.Cells { //循环列数
					if cellindex >= len(fields) {
						break
					}
					field := fields[cellindex]
					if len(cell.String()) == 0 && (field == "SampleCode" || field == "SampleType") {
						//不能为空
						flag = 1
						break
					}

					if cell.String() != "" && len(cell.String()) != 0 {
						if field == "EquipmentId" {
							basestation.EquipmentId, _ = utils.StrTo(cell.String()).Int()
						} else if field == "ShelfId" {
							basestation.ShelfId, _ = utils.StrTo(cell.String()).Int()
						} else if field == "BoxId" {
							basestation.BoxId, _ = utils.StrTo(cell.String()).Int()
						} else if field == "Position" {
							spos := cell.String()
							sposs := StrSplit(spos, ";")
							basestation.Position = sposs[1] + ";" + utils.ToStr(Boxlineumber(sposs[0]))
							whereposition := " DeletionStateCode=0 and IState in (1,5) and BoxId=" + utils.ToStr(basestation.BoxId) + " and Position='" + basestation.Position + "'"
							have := s.QuerySampleInfoHas(tbldetail, whereposition)
							if have {
								flag = 1
								warn1 += cell.String() + ","
								break
							}
						} else if field == "SampleCode" { //样本编码
							basestation.SampleCode = cell.String()
						} else if field == "BarCode" { //样本条码
							whereposition := " DeletionStateCode=0 and BarCode = '" + cell.String() + "' "
							have := s.QuerySampleInfoHas(tbldetail, whereposition)
							where := " DeletionStateCode=0 and BarCode = '" + cell.String() + "'"
							has := s.QuerySampleInfoHas(tblfile, where)
							if have || has {
								flag = 1
								warn1 += cell.String() + ","
								break
							}
							basestation.BarCode = cell.String()
						} else if field == "SampleType" {
							basestation.SampleTypeName = cell.String()
							where2 := " AccCode='" + acccode + "' and Name='" + cell.String() + "'"
							sampletype_data = svc.GetSampleType(where2)
							if sampletype_data.Id == 0 {
								flag = 1
								warn1 = " "
								break
							}
							basestation.SampleType = sampletype_data.Id
						} else if field == "Name" {
							basestation.Name = cell.String()
						} else if field == "Unit" {
							basestation.Unit = cell.String()
						} else if field == "ValidityDate" {
							stime, err := utils.TimeParse(cell.Value, "2006-1-2 15:4:5")
							if err != nil {
								aa, _ := utils.StrTo(cell.Value).Float64()
								basestation.ValidityDate = xlsx.TimeFromExcelTime(aa, false)
							} else {
								basestation.ValidityDate = stime
							}
						} else if field == "ReceiveDate" {
							stime, err := utils.TimeParse(cell.Value, "2006-1-2 15:4:5")
							if err != nil {
								aa, _ := utils.StrTo(cell.Value).Float64()
								basestation.ReceiveDate = xlsx.TimeFromExcelTime(aa, false)
							} else {
								basestation.ReceiveDate = stime
							}
						} else if field == "CreateBy" {
							base_userrole = svc1.GetidByRealname(cell.String())
							basestation.CreateBy = cell.String()
							basestation.CreateUserId = base_userrole.Id
						} else if field == "SourceName" {
							basestation.SourceName = cell.String()
						} else if field == "SourceIdCard" {
							basestation.SourceIdCard = cell.String()
						} else if field == "Remark" {
							basestation.Remark = cell.String()
						} else if field == "ParentBarCode" {
							basestation.ParentBarCode = cell.String()
						} else if field == "InnerCode" {
							basestation.InnerCode = cell.String()
						} else {
							if strings.Contains(field, "ZBack") {
								notefieldstr = notefieldstr + "," + field                //扩展字段
								notevaluestr = notevaluestr + ",'" + cell.String() + "'" //扩展名称
							}
						}
						//						 else if field == "SamplingOrgan" {
						//							basestation.SamplingOrgan = cell.String()
						//						} else if field == "SamplingOrganName" {
						//							basestation.SamplingOrganName = cell.String()
						//						} else if field == "SamplingSite" {
						//							basestation.SamplingSite = cell.String()
						//						} else if field == "SamplingSiteName" {
						//							basestation.SamplingSiteName = cell.String()
						//						} else if field == "Capacity" {
						//							basestation.Capacity, _ = utils.StrTo(cell.String()).Float32()
						//							basestation.InitCapacity = basestation.Capacity
						//						}
					}

				}

				//				if flag == 1 {
				//					flag = 0
				//					continue
				//				}
				if basestation.CreateBy == "" {
					basestation.CreateBy = createby
					basestation.CreateUserId = createuserid
				}

				//				if basestation.SourceName != "" || basestation.SourceIdCard != "" {
				if basestation.SourceName != "" {
					//wheredonors := " IdCard = '" + basestation.SourceIdCard + "' and Name ='" + basestation.SourceName + "' "
					wheredonors := " Name ='" + basestation.SourceName + "' "
					donors_data = svcdonors.QueryDonorEntity(tbldonor, wheredonors)
					basestation.SourceId = donors_data.Id
				}
				basestation.AccCode = acccode
				basestation.CreateOn = time.Now()
				basestation.IState = 3
				basestation.IType = 0
				basestation.FreezingNum = 0
				wheremain := " SampleCode='" + basestation.SampleCode + "' "
				ishasmain := s.QuerySampleInfoHas(tblmain, wheremain)
				if !ishasmain { //没有主信息，全部添加
					//添加主表
					sqlfield := " AccCode,SampleCode,Name,SampleType,SampleTypeName,SourceId,"
					sqlfield = sqlfield + "SamplingOrgan,SamplingOrganName,SamplingSite,SamplingSiteName,ReceiveDate,"
					sqlfield = sqlfield + "SourceIdCard,SourceName,MCreateUserId,MCreateBy,MCreateOn,MModifiedOn"
					sqlfield = sqlfield + notefieldstr

					sqlvalue := "'" + basestation.AccCode + "','" + basestation.SampleCode + "','" + basestation.Name + "'," + utils.ToStr(basestation.SampleType) + ",'" + basestation.SampleTypeName + "',"
					sqlvalue = sqlvalue + "" + utils.ToStr(basestation.SourceId) + ","
					sqlvalue = sqlvalue + "'" + basestation.SamplingOrgan + "','" + basestation.SamplingOrganName + "','" + basestation.SamplingSite + "',"
					sqlvalue = sqlvalue + "'" + basestation.SamplingSiteName + "','" + basestation.ReceiveDate.Format("2006-1-2 15:4:5") + "','" + basestation.SourceIdCard + "','" + basestation.SourceName + "',"
					sqlvalue = sqlvalue + "" + utils.ToStr(basestation.CreateUserId) + ",'" + basestation.CreateBy + "',now(),now()"
					sqlvalue = sqlvalue + notevaluestr
					s.AddLine(tblmain, sqlfield, sqlvalue)
				}
				//				if basestation.BarCode == "" {
				//					basestation.BarCode = s.AutoGetBarCode(acccode, basestation.SampleCode)
				//				}
				datadetail.SampleCode = basestation.SampleCode
				datadetail.BarCode = basestation.BarCode
				datadetail.IState = 1
				datadetail.EquipmentId = basestation.EquipmentId
				datadetail.ShelfId = basestation.ShelfId
				datadetail.BoxId = basestation.BoxId
				datadetail.Position = basestation.Position
				datadetail.ParentBarCode = basestation.ParentBarCode
				datadetail.IType = basestation.IType
				datadetail.Capacity = basestation.Capacity
				datadetail.InitCapacity = basestation.Capacity
				datadetail.Unit = basestation.Unit
				datadetail.FreezingNum = 0
				datadetail.ValidityDate = basestation.ValidityDate
				datadetail.InnerCode = basestation.InnerCode
				datadetail.CreateUserId = basestation.CreateUserId
				datadetail.CreateBy = basestation.CreateBy
				datadetail.Remark = basestation.Remark
				fmt.Println("eeeeeeeeeeeeeeeeeeeeeeeeeeeeee3", tbldetail, &datadetail)
				affected, _ := s.InsertEntityBytbl(tbldetail, &datadetail)
				if affected > 0 {
					databuss.OpType = 7
					databuss.AccCode = basestation.AccCode
					databuss.BarCode = datadetail.BarCode
					databuss.CapacityUsed = datadetail.Capacity
					databuss.CapacityRest = datadetail.Capacity
					databuss.SampleCode = datadetail.SampleCode
					databuss.Unit = datadetail.Unit
					databuss.SampleType = basestation.SampleType
					databuss.EquipmentId = datadetail.EquipmentId
					databuss.ShelfId = datadetail.ShelfId
					databuss.BoxId = datadetail.BoxId
					databuss.Position = datadetail.Position
					databuss.PositionDesc = s.GetPostiondescByPosId(datadetail.EquipmentId, datadetail.ShelfId, datadetail.BoxId, datadetail.Position)
					databuss.CreateUserId = datadetail.CreateUserId
					databuss.CreateBy = datadetail.CreateBy
					s.InsertEntityBytbl(tblbuss, &databuss)
				}
				if rowindex == (sheetrows) {
					//s.AddLine(tbldetail, sqlfield, sqlvalue)
				}
			}
		} else {
			for rowindex, row := range sheet.Rows { //循环行数
				notefieldstr := ""
				notevaluestr := ""
				if rowindex == 0 { //第一行是表头，跳过
					continue
				}
				if row.Cells == nil || len(row.Cells) < 13 { //小于必填项的19列，不允许导入(改为跳过)
					continue
				}

				basestation := new(SamplesInfoShow)
				for cellindex, cell := range row.Cells { //循环列数
					if cellindex == 0 && len(cell.String()) == 0 { //冰箱ID不能为空
						flag = 1
						break
					}
					if cellindex == 2 && len(cell.String()) == 0 { //冻存架不能为空
						flag = 1
						break
					}
					if cellindex == 4 && len(cell.String()) == 0 { //冻存盒Id不能为空
						flag = 1
						break
					}
					if cellindex == 6 && len(cell.String()) == 0 { //位置不能为空
						flag = 1
						break
					}
					if cellindex == 7 && len(cell.String()) == 0 { //样本编码不能为空
						flag = 1
						break
					}
					if cellindex == 9 && len(cell.String()) == 0 { //样本类型不能为空
						flag = 1
						break
					}
					if cellindex == 17 && len(cell.String()) == 0 { //有效日期不能为空
						flag = 1
						break
					}
					if cellindex == 18 && len(cell.String()) == 0 { //接收日期不能为空
						flag = 1
						break
					}
					if cell.String() != "" && len(cell.String()) != 0 {
						if cellindex == 0 {
							basestation.EquipmentId, _ = utils.StrTo(cell.String()).Int()
						} else if cellindex == 2 {
							basestation.ShelfId, _ = utils.StrTo(cell.String()).Int()
						} else if cellindex == 4 {
							basestation.BoxId, _ = utils.StrTo(cell.String()).Int()
						} else if cellindex == 6 {
							spos := cell.String()
							sposs := StrSplit(spos, ";")
							basestation.Position = sposs[1] + ";" + utils.ToStr(Boxlineumber(sposs[0]))
							whereposition := " DeletionStateCode=0 and IState in (1,5) and BoxId=" + utils.ToStr(basestation.BoxId) + " and Position='" + basestation.Position + "'"
							have := s.QuerySampleInfoHas(tbldetail, whereposition)
							if have {
								flag = 1
								warn1 += cell.String() + ","
								break
							}
						} else if cellindex == 7 { //样本编码
							basestation.SampleCode = cell.String()
						} else if cellindex == 8 { //样本条码
							whereposition := " DeletionStateCode=0 and BarCode = '" + cell.String() + "' "
							have := s.QuerySampleInfoHas(tbldetail, whereposition)
							where := " DeletionStateCode=0 and BarCode = '" + cell.String() + "'"
							has := s.QuerySampleInfoHas(tblfile, where)
							if have || has {
								flag = 1
								warn1 += cell.String() + ","
								break
							}
							basestation.BarCode = cell.String()
						} else if cellindex == 9 {
							basestation.SampleTypeName = cell.String()
							where2 := " AccCode='" + acccode + "' and Name='" + cell.String() + "'"
							sampletype_data = svc.GetSampleType(where2)
							if sampletype_data.Id == 0 {
								flag = 1
								warn1 = " "
								break
							}
							basestation.SampleType = sampletype_data.Id
						} else if cellindex == 10 {
							basestation.Name = cell.String()
						} else if cellindex == 11 {
							basestation.SamplingOrgan = cell.String()
						} else if cellindex == 12 {
							basestation.SamplingOrganName = cell.String()
						} else if cellindex == 13 {
							basestation.SamplingSite = cell.String()
						} else if cellindex == 14 {
							basestation.SamplingSiteName = cell.String()
						} else if cellindex == 15 {
							basestation.Capacity, _ = utils.StrTo(cell.String()).Float32()
							basestation.InitCapacity = basestation.Capacity
						} else if cellindex == 16 {
							basestation.Unit = cell.String()
						} else if cellindex == 17 {
							stime, err := utils.TimeParse(cell.Value, "2006-1-2 15:4:5")
							if err != nil {
								aa, _ := utils.StrTo(cell.Value).Float64()
								basestation.ValidityDate = xlsx.TimeFromExcelTime(aa, false)
							} else {
								basestation.ValidityDate = stime
							}
						} else if cellindex == 18 {
							stime, err := utils.TimeParse(cell.Value, "2006-1-2 15:4:5")
							if err != nil {
								aa, _ := utils.StrTo(cell.Value).Float64()
								basestation.ReceiveDate = xlsx.TimeFromExcelTime(aa, false)
							} else {
								basestation.ReceiveDate = stime
							}
						} else if cellindex == 19 {
							base_userrole = svc1.GetidByRealname(cell.String())
							basestation.CreateBy = cell.String()
							basestation.CreateUserId = base_userrole.Id
						} else if cellindex == 20 {
							basestation.SourceName = cell.String()
						} else if cellindex == 21 {
							basestation.SourceIdCard = cell.String()
						} else if cellindex == 22 {
							basestation.Remark = cell.String()
						} else if cellindex == 23 {
							basestation.ParentBarCode = cell.String()
						} else if cellindex == 24 {
							basestation.InnerCode = cell.String()
						}
						//						else if cellindex > 24 && cellindex < 25+notecount {
						//							for i := 1; i < notecount+1; i++ {
						//								if cellindex == 24+i {
						//									notefieldstr = notefieldstr + "," + notelist[i-1].FieldName //扩展字段 因为cellindex可能小于21
						//									notevaluestr = notevaluestr + ",'" + cell.String() + "'"    //扩展名称
						//								}
						//							}
						//						}
					}
				}

				if flag == 1 {
					flag = 0
					//continue
				}

				if basestation.CreateBy == "" {
					basestation.CreateBy = createby
					basestation.CreateUserId = createuserid
				}
				if basestation.SourceName != "" || basestation.SourceIdCard != "" {
					wheredonors := " IdCard = '" + basestation.SourceIdCard + "' and Name ='" + basestation.SourceName + "' "
					donors_data = svcdonors.QueryDonorEntity(tbldonor, wheredonors)
					basestation.SourceId = donors_data.Id
				}

				basestation.AccCode = acccode
				basestation.CreateOn = time.Now()
				basestation.IState = 3
				basestation.IType = 0
				basestation.FreezingNum = 0
				wheremain := " SampleCode='" + basestation.SampleCode + "' "
				ishasmain := s.QuerySampleInfoHas(tblmain, wheremain)

				if !ishasmain { //没有主信息，全部添加
					//添加主表
					sqlfield := " AccCode,SampleCode,Name,SampleType,SampleTypeName,SourceId,"
					sqlfield = sqlfield + "SamplingOrgan,SamplingOrganName,SamplingSite,SamplingSiteName,ReceiveDate,"
					sqlfield = sqlfield + "SourceIdCard,SourceName,MCreateUserId,MCreateBy,MCreateOn,MModifiedOn"
					sqlfield = sqlfield + notefieldstr

					sqlvalue := "'" + basestation.AccCode + "','" + basestation.SampleCode + "','" + basestation.Name + "'," + utils.ToStr(basestation.SampleType) + ",'" + basestation.SampleTypeName + "',"
					sqlvalue = sqlvalue + "" + utils.ToStr(basestation.SourceId) + ","
					sqlvalue = sqlvalue + "'" + basestation.SamplingOrgan + "','" + basestation.SamplingOrganName + "','" + basestation.SamplingSite + "',"
					sqlvalue = sqlvalue + "'" + basestation.SamplingSiteName + "','" + basestation.ReceiveDate.Format("2006-1-2 15:4:5") + "','" + basestation.SourceIdCard + "','" + basestation.SourceName + "',"
					sqlvalue = sqlvalue + "" + utils.ToStr(basestation.CreateUserId) + ",'" + basestation.CreateBy + "',now(),now()"
					sqlvalue = sqlvalue + notevaluestr
					fmt.Println(sqlfield)
					fmt.Println(sqlvalue)
					s.AddLine(tblmain, sqlfield, sqlvalue)
				}
				if basestation.BarCode == "" {
					basestation.BarCode = s.AutoGetBarCode(acccode, basestation.SampleCode)
				}
				datadetail.SampleCode = basestation.SampleCode
				datadetail.BarCode = basestation.BarCode
				datadetail.IState = 1
				datadetail.EquipmentId = basestation.EquipmentId
				datadetail.ShelfId = basestation.ShelfId
				datadetail.BoxId = basestation.BoxId
				datadetail.Position = basestation.Position
				datadetail.ParentBarCode = basestation.ParentBarCode
				datadetail.IType = basestation.IType
				datadetail.Capacity = basestation.Capacity
				datadetail.InitCapacity = basestation.Capacity
				datadetail.Unit = basestation.Unit
				datadetail.FreezingNum = 0
				datadetail.ValidityDate = basestation.ValidityDate
				datadetail.InnerCode = basestation.InnerCode
				datadetail.CreateUserId = basestation.CreateUserId
				datadetail.CreateBy = basestation.CreateBy
				datadetail.Remark = basestation.Remark
				affected, _ := s.InsertEntityBytbl(tbldetail, &datadetail)
				if affected > 0 {
					databuss.OpType = 7
					databuss.AccCode = basestation.AccCode
					databuss.BarCode = datadetail.BarCode
					databuss.CapacityUsed = datadetail.Capacity
					databuss.CapacityRest = datadetail.Capacity
					databuss.SampleCode = datadetail.SampleCode
					databuss.Unit = datadetail.Unit
					databuss.SampleType = basestation.SampleType
					databuss.EquipmentId = datadetail.EquipmentId
					databuss.ShelfId = datadetail.ShelfId
					databuss.BoxId = datadetail.BoxId
					databuss.Position = datadetail.Position
					databuss.PositionDesc = s.GetPostiondescByPosId(datadetail.EquipmentId, datadetail.ShelfId, datadetail.BoxId, datadetail.Position)
					databuss.CreateUserId = datadetail.CreateUserId
					databuss.CreateBy = datadetail.CreateBy
					s.InsertEntityBytbl(tblbuss, &databuss)
				}
				if rowindex == (sheetrows) {
					//s.AddLine(tbldetail, sqlfield, sqlvalue)
				}
			}
		}
	}
	return err, warn1
}

//判断是否存在此样本
func (s *SamplesInfoService) QuerySampleInfoHas(tablename, where string) bool {
	var num MyInt_Id
	sql := "select count(1) Id from " + tablename
	if where != "" {
		sql = sql + " where " + where + " limit 1 "
	}
	s.DBE.Sql(sql).Get(&num)
	total := num.Id
	if total > 0 {
		return true
	} else {
		return false
	}
}

func (s *SamplesInfoService) AddLine(tablename, fieldName, fieldValue string) error {
	sql := "insert into " + tablename + "(" + fieldName + ") values(" + fieldValue + ")"
	_, err := s.DBE.Exec(sql)
	return err
}

func (s *SamplesInfoService) AutoGetBarCode(acccode, samplecode string) string {
	var num MyInt_Id
	tbldetail := acccode + SamplesDetailtbName
	tblfile := acccode + SamplesDetailFiletbName
	slikestring := samplecode
	//	slikestring := samplecode + "-"
	sql := "select MAX(REPLACE(BarCode,'" + slikestring + "','')+1) Id from "
	sql = sql + "( select BarCode from " + tbldetail
	sql = sql + " where DeletionStateCode=0 and BarCode like '" + slikestring + "%' "
	sql = sql + " union select BarCode from " + tblfile
	sql = sql + " where DeletionStateCode=0 and BarCode like '" + slikestring + "%') as A "
	s.DBE.Sql(sql).Get(&num)
	total := num.Id
	if total == 0 {
		total = 1
	}
	if total < 10 {
		slikestring = samplecode + "0"
	}
	return slikestring + utils.ToStr(total)
}

func (s *SamplesInfoService) QueryZBackList(acccode string) []String_Id {
	List := make([]String_Id, 0)
	sql := "select FieldName Id from STypeNoteItem where FieldNo>10 and AccCode ='" + acccode + "' union select FieldName Id from SamplesNoteItem where FieldNo>10  and AccCode ='" + acccode + "'"
	utils.DBE.Sql(sql).Find(&List)
	return List
}
func (s *SamplesInfoService) DelSDetailInfo(acccode string, id int) error {
	tbldetail := acccode + SamplesDetailtbName
	sql := " delete from " + tbldetail + " where Id=" + utils.ToStr(id) + ""
	_, err := s.DBE.Exec(sql)
	return err
}
func (s *SamplesInfoService) DelSMainInfo(acccode string, id int) error {
	tbldetail := acccode + SamplesMaintbName
	sql := " delete from " + tbldetail + " where MId=" + utils.ToStr(id) + ""
	_, err := s.DBE.Exec(sql)
	return err
}
func (s *SamplesInfoService) QuerySampleList(acccode string, where string) []SamplesInfoShow {
	tblmain := acccode + SamplesMaintbName
	tbldetail := acccode + SamplesDetailtbName
	sql := "select a.*,b.* from   " + tbldetail + " a left join " + tblmain +
		" b on a.SampleCode = b.SampleCode where " + where + " order by a.Id desc"
	List := make([]SamplesInfoShow, 0)
	utils.DBE.Sql(sql).Find(&List)
	return List
}

func (s *SamplesInfoService) QueryAnimalSampleList(acccode string, where string) []AnimalSamplesInfoShow {
	tblmain := acccode + SamplesMaintbName
	tbldetail := acccode + SamplesDetailtbName
	animaltb := acccode + AnimalInfoName
	sql := "select a.*,b.*,c.Genus,c.GenusName,concat(c.ProvinceName,c.CityName,c.StreetName,c.Address) as AddressName,c.InnerNo as SourceInner,c.Amount,c.Unit as SourceUni,c.ProjectName,c.Longitude,c.Latitude,c.Altitude,c.SurveyDate,c.ZBack11 as BeeKeepers,c.ZBack17 as TakeAway from   " + tbldetail + " a left join " + tblmain +
		" b on a.SampleCode = b.SampleCode left join " + animaltb +
		" c on b.SourceId = c.Id where " + where + " order by a.Id desc"
	List := make([]AnimalSamplesInfoShow, 0)
	utils.DBE.Sql(sql).Find(&List)

	for i := 0; i < len(List); i++ {
		//List[i].AllbarCode = "蜂种:" + List[i].Genus + "&&蜂种备注:" + List[i].GenusName + "&&项目属性:" + List[i].ProjectName + "&&采样地址:" + List[i].AddressName + "&&采样时间:" + List[i].SurveyDate.Format("2006-01-02") + "&&群数:" + strconv.Itoa(List[i].Amount) + "&&经度:" + List[i].Longitude + "&&纬度:" + List[i].Latitude + "&&海拔:" + List[i].Altitude + "&&采集人:" + List[i].TakeAway + "&&供蜂人:" + List[i].BeeKeepers + "&&样本编码:" + List[i].BarCode
		List[i].ShortbarCode = List[i].BarCode[0:14]
		List[i].AllbarCode = List[i].BarCode + "·" + List[i].Genus + "-" + List[i].GenusName + "·" + List[i].AddressName + "·" + List[i].SurveyDate.Format("2006-01-02") + "·" + List[i].Altitude + "米·" + List[i].ProjectName
	}
	return List
}

type SampleCount struct {
	IState int
	SCount int
}

//获取数量
func (s *SamplesInfoService) GetSampleOwnCount(tablename, where string) int {
	var num MyInt_Id
	sql := "select count(Id) Id from " + tablename
	if where != "" {
		sql = sql + " where " + where
	}
	s.DBE.Sql(sql).Get(&num)
	total := num.Id
	return total
}
//获取数量
func (s *SamplesInfoService) GetSampleOwnCountBySourceId(acccode, where string) int {
	tblmain := acccode + SamplesMaintbName
	tbldetail := acccode + SamplesDetailtbName
	var num MyInt_Id
	sql := "select count(Id) Id  from " + tbldetail + " a left join " + tblmain +
		" b on a.SampleCode = b.SampleCode "
	if where != "" {
		sql = sql + " where " + where
	}
	s.DBE.Sql(sql).Get(&num)
	total := num.Id
	return total
}

//获取样本数量分样本类型
type SampleCountType struct {
	SampleType int
	SCount     int
}

func (s *SamplesInfoService) GetSampleCountbySampleType(tablename, where, groupstr string) []SampleCountType {
	sql := " select SampleType,Count(Id) SCount from " + tablename
	if where != "" {
		sql = sql + " where " + where
	}
	if groupstr != "" {
		sql = sql + groupstr
	}
	List := make([]SampleCountType, 0)
	utils.DBE.Sql(sql).Find(&List)
	return List
}

func (s *SamplesInfoService) GetPreSampleCountbySampleType(acccode, where, groupstr string) []SampleCountType {
	tblmain := acccode + SamplesMaintbName
	tbldetail := acccode + SamplesDetailtbName
	sql := " select SampleType,Count(Id) SCount from " + tbldetail + " a left join " + tblmain +
		" b on a.SampleCode = b.SampleCode "
	if where != "" {
		sql = sql + " where " + where
	}
	if groupstr != "" {
		sql = sql + groupstr
	}
	List := make([]SampleCountType, 0)
	utils.DBE.Sql(sql).Find(&List)
	return List
}

//获取样本数量分样本类型
type SampleOutCountOpType struct {
	SampleType int
	OpType     int
	SCount     int
}

func (s *SamplesInfoService) GetSampleOutCountbyOpType(tablename, where, groupstr string) []SampleOutCountOpType {
	sql := " select SampleType,OpType,Count(Id) SCount from " + tablename
	if where != "" {
		sql = sql + " where " + where
	}
	if groupstr != "" {
		sql = sql + groupstr
	}
	List := make([]SampleOutCountOpType, 0)
	utils.DBE.Sql(sql).Find(&List)
	return List
}

func (s *SamplesInfoService) GetSumSampleLogCountbyOpType(tablename, where, groupstr string) []SampleOutCountOpType {
	sql := " select OpType,Count(Id) SCount from " + tablename
	if where != "" {
		sql = sql + " where " + where
	}
	if groupstr != "" {
		sql = sql + groupstr
	}
	List := make([]SampleOutCountOpType, 0)
	utils.DBE.Sql(sql).Find(&List)
	return List
}

func (s *SamplesInfoService) InsertExcelSampleBox(excelpath string, boxentity shelfset.Box, stypeid int, stypename string, capacity float32, unit string) (err error, warn string) {

	tblmain := boxentity.AccCode + SamplesMaintbName
	tbldetail := boxentity.AccCode + SamplesDetailtbName
	tblfile := boxentity.AccCode + SamplesDetailFiletbName

	boxrow := boxentity.RowNum

	positionitem := 2

	var datamain SamplesMain
	var datadetail SamplesDetail

	dir, _ := os.Getwd()
	flag := 0
	excelpath = dir + excelpath
	xlFile, _ := xlsx.OpenFile(excelpath)

	var warn1 string
	for sheetindex, sheet := range xlFile.Sheets { //循环页数
		sheetrows := len(xlFile.Sheets[sheetindex].Rows) //每一页的行数
		fmt.Println("sheetindex", sheetindex)
		for rowindex, row := range sheet.Rows { //循环行数
			scellrow := ""
			scolumn := ""
			srealrow := ""
			realrow := 0
			if rowindex == 0 { //第一行是表头，跳过
				continue
			}
			if row.Cells == nil || len(row.Cells) < 3 { //小于必填项的3列，不允许导入
				break
			}
			basestation := new(SamplesInfoShow)
			for cellindex, cell := range row.Cells { //循环列数
				if cellindex == 0 && len(cell.String()) == 0 { //条码不能为空
					flag = 1
					break
				}
				if cellindex == 1 && len(cell.String()) == 0 { //行数不能为空
					flag = 1
					break
				}
				if cellindex == 2 && len(cell.String()) == 0 { //列数不能为空
					flag = 1
					break
				}
				if cell.String() != "" && len(cell.String()) != 0 {
					if cellindex == 0 {
						if cell.String() == "notfound" || cell.String() == "notpipe" {
							flag = 1
							break
						}
						basestation.BarCode = cell.String()
					} else if cellindex == 1 {
						scellrow = cell.String()
						if positionitem == 2 {
							rowint, _ := utils.StrTo(scellrow).Int()
							realrow = boxrow + 1 - rowint
							srealrow = utils.ToStr(realrow)
						} else {
							srealrow = scellrow
						}
					} else if cellindex == 2 {
						scolumn = cell.String()
						basestation.Position = utils.ToStr(Boxlineumber(scolumn)) + ";" + srealrow
					}
				}
			}
			fmt.Println("qqq---", basestation.Position, positionitem)
			if flag == 1 {
				flag = 0
				continue
			}

			if basestation.CreateBy == "" {
				basestation.CreateBy = boxentity.CreateBy
				basestation.CreateUserId = boxentity.CreateUserId
			}

			basestation.SampleCode = basestation.BarCode
			basestation.Capacity = capacity
			basestation.InitCapacity = basestation.Capacity
			basestation.Unit = unit
			basestation.SampleTypeName = stypename
			basestation.SampleType = stypeid
			basestation.AccCode = boxentity.AccCode
			basestation.CreateOn = time.Now()
			basestation.IType = 0
			basestation.FreezingNum = 0

			wheremain := " SampleCode='" + basestation.SampleCode + "' "
			ishasmain := s.QuerySampleInfoHas(tblmain, wheremain)

			where := " DeletionStateCode=0 and BarCode = '" + basestation.BarCode + "'"
			have := s.QuerySampleInfoHas(tbldetail, where)
			has := s.QuerySampleInfoHas(tblfile, where)

			if !ishasmain && !has && !have {
				datamain.SampleCode = basestation.SampleCode
				datamain.SampleType = basestation.SampleType
				datamain.SampleTypeName = basestation.SampleTypeName
				datamain.AccCode = basestation.AccCode
				datamain.MCreateUserId = basestation.CreateUserId
				datamain.MCreateBy = basestation.CreateBy
				s.InsertEntityBytbl(tblmain, &datamain)

				datadetail.EquipmentId = boxentity.EquipmentId
				datadetail.ShelfId = boxentity.ShelfId
				datadetail.BoxId = boxentity.Id
				datadetail.SampleCode = basestation.SampleCode
				datadetail.BarCode = basestation.BarCode
				datadetail.IState = 11
				datadetail.Position = basestation.Position
				datadetail.IType = basestation.IType
				datadetail.Capacity = basestation.Capacity
				datadetail.InitCapacity = basestation.Capacity
				datadetail.Unit = basestation.Unit
				datadetail.FreezingNum = 0
				datadetail.CreateUserId = basestation.CreateUserId
				datadetail.CreateBy = basestation.CreateBy
				datadetail.Remark = basestation.Remark
				s.InsertEntityBytbl(tbldetail, &datadetail)
			}
			if rowindex == (sheetrows) {
				//s.AddLine(tbldetail, sqlfield, sqlvalue)
			}
		}
	}
	return err, warn1
}

//扫描导入无冻存盒导入冻存管
func (s *SamplesInfoService) ExportSampleBox(dongcun string, boxentity shelfset.Box, stypeid int, stypename string, capacity float32, unit string) (err error, issuess int) {

	dongcuns := strings.Split(dongcun, ",")
	if len(dongcuns) == 48 || len(dongcuns) == 49 {
		tblmain := boxentity.AccCode + SamplesMaintbName
		tbldetail := boxentity.AccCode + SamplesDetailtbName
		tblfile := boxentity.AccCode + SamplesDetailFiletbName

		var datamain SamplesMain
		var datadetail SamplesDetail
		//添加冻存管数据
		for i := 1; i <= 12; i++ { //盒子有12列，8行，交替显示
			for j := 1; j <= 4; j++ { //实际每列只有4个
				guanx, guany := 0, 0
				if i%2 == 1 { //奇数列从第2行开始2，4，6，8
					guanx, guany = i, 2*j
				} else {
					guanx, guany = i, 2*j-1
				}
				basestation := new(SamplesInfoShow)
				basestation.BarCode = dongcuns[4*(i-1)+(j-1)]
				//无冻存管是字符串：No Tube
				if strings.Index(basestation.BarCode, "No Tube") > -1 {
					continue
				}
				basestation.Position = fmt.Sprintf("%v;%v", guanx, guany) //位置
				basestation.SampleCode = basestation.BarCode

				if basestation.CreateBy == "" {
					basestation.CreateBy = boxentity.CreateBy
					basestation.CreateUserId = boxentity.CreateUserId
				}
				basestation.Capacity = capacity
				basestation.InitCapacity = basestation.Capacity
				basestation.Unit = unit
				basestation.SampleTypeName = stypename
				basestation.SampleType = stypeid
				basestation.AccCode = boxentity.AccCode
				basestation.CreateOn = time.Now()
				basestation.IType = 0
				basestation.FreezingNum = 0

				wheremain := " SampleCode='" + basestation.SampleCode + "' "
				ishasmain := s.QuerySampleInfoHas(tblmain, wheremain)

				where := " DeletionStateCode=0 and BarCode = '" + basestation.BarCode + "'"
				have := s.QuerySampleInfoHas(tbldetail, where)
				has := s.QuerySampleInfoHas(tblfile, where)

				if !ishasmain && !has && !have {
					datamain.SampleCode = basestation.SampleCode
					datamain.SampleType = basestation.SampleType
					datamain.SampleTypeName = basestation.SampleTypeName
					datamain.AccCode = basestation.AccCode
					datamain.MCreateUserId = basestation.CreateUserId
					datamain.MCreateBy = basestation.CreateBy
					s.InsertEntityBytbl(tblmain, &datamain)

					datadetail.EquipmentId = boxentity.EquipmentId
					datadetail.ShelfId = boxentity.ShelfId
					datadetail.BoxId = boxentity.Id
					datadetail.SampleCode = basestation.SampleCode
					datadetail.BarCode = basestation.BarCode
					datadetail.IState = 1
					datadetail.Position = basestation.Position
					datadetail.IType = basestation.IType
					datadetail.Capacity = basestation.Capacity
					datadetail.InitCapacity = basestation.Capacity
					datadetail.Unit = basestation.Unit
					datadetail.FreezingNum = 0
					datadetail.CreateUserId = basestation.CreateUserId
					datadetail.CreateBy = basestation.CreateBy
					datadetail.Remark = basestation.Remark
					s.InsertEntityBytbl(tbldetail, &datadetail)
				}
			}
		}

		//添加补充位置的数据
		for i := 1; i <= 12; i++ { //盒子有12列，8行，交替显示
			for j := 1; j <= 4; j++ { //实际每列只有4个
				guanx, guany := 0, 0
				if i%2 == 1 { //奇数列从第2行开始2，4，6，8
					guanx, guany = i, 2*j-1
				} else {
					guanx, guany = i, 2*j
				}

				basestation := new(SamplesInfoShow)
				basestation.BarCode = "-99"                               //特殊，不需要
				basestation.Position = fmt.Sprintf("%v;%v", guanx, guany) //位置
				basestation.SampleCode = basestation.BarCode

				if basestation.CreateBy == "" {
					basestation.CreateBy = boxentity.CreateBy
					basestation.CreateUserId = boxentity.CreateUserId
				}
				basestation.Capacity = 0
				basestation.InitCapacity = basestation.Capacity
				basestation.Unit = unit
				basestation.SampleTypeName = stypename
				basestation.SampleType = stypeid
				basestation.AccCode = boxentity.AccCode
				basestation.CreateOn = time.Now()
				basestation.IType = 0
				basestation.FreezingNum = 0

				where := " DeletionStateCode=0 and BarCode = '" + basestation.BarCode + "' and Position = '" +
					basestation.Position + "' and EquipmentId=" + utils.ToStr(boxentity.EquipmentId) + " and ShelfId = " +
					utils.ToStr(boxentity.ShelfId) + " and BoxId = " + utils.ToStr(boxentity.Id)
				have := s.QuerySampleInfoHas(tbldetail, where)

				if !have {

					datadetail.EquipmentId = boxentity.EquipmentId
					datadetail.ShelfId = boxentity.ShelfId
					datadetail.BoxId = boxentity.Id
					datadetail.SampleCode = basestation.SampleCode
					datadetail.BarCode = basestation.BarCode
					datadetail.IState = 99
					datadetail.Position = basestation.Position
					datadetail.IType = basestation.IType
					datadetail.Capacity = basestation.Capacity
					datadetail.InitCapacity = basestation.Capacity
					datadetail.Unit = basestation.Unit
					datadetail.FreezingNum = 0
					datadetail.CreateUserId = basestation.CreateUserId
					datadetail.CreateBy = basestation.CreateBy
					datadetail.Remark = basestation.Remark
					s.InsertEntityBytbl(tbldetail, &datadetail)
				}
			}
		}

		return err, 1
	} else {
		return nil, 0
	}

}

//扫描导入有冻存盒导入冻存管
func (s *SamplesInfoService) ExportSampleBoxFull(dongcun string, boxentity shelfset.Box, stypeid int, stypename string, capacity float32, unit string) (err error, issuess int) {

	dongcuns := strings.Split(dongcun, ",")
	if len(dongcuns) == 48 || len(dongcuns) == 49 {
		tblmain := boxentity.AccCode + SamplesMaintbName
		tbldetail := boxentity.AccCode + SamplesDetailtbName
		tblfile := boxentity.AccCode + SamplesDetailFiletbName

		var datamain SamplesMain
		var datadetail SamplesDetail
		//添加冻存管数据
		for i := 1; i <= 12; i++ { //盒子有12列，8行，交替显示
			for j := 1; j <= 4; j++ { //实际每列只有4个
				guanx, guany := 0, 0
				if i%2 == 1 { //奇数列从第2行开始2，4，6，8
					guanx, guany = i, 2*j
				} else {
					guanx, guany = i, 2*j-1
				}
				basestation := new(SamplesInfoShow)
				basestation.BarCode = dongcuns[4*(i-1)+(j-1)]
				//无冻存管是字符串：No Tube
				if strings.Index(basestation.BarCode, "No Tube") > -1 {
					continue
				}
				basestation.Position = fmt.Sprintf("%v;%v", guanx, guany) //位置
				basestation.SampleCode = basestation.BarCode

				if basestation.CreateBy == "" {
					basestation.CreateBy = boxentity.CreateBy
					basestation.CreateUserId = boxentity.CreateUserId
				}
				basestation.Capacity = capacity
				basestation.InitCapacity = basestation.Capacity
				basestation.Unit = unit
				basestation.SampleTypeName = stypename
				basestation.SampleType = stypeid
				basestation.AccCode = boxentity.AccCode
				basestation.CreateOn = time.Now()
				basestation.IType = 0
				basestation.FreezingNum = 0

				wheremain := " SampleCode='" + basestation.SampleCode + "' "
				ishasmain := s.QuerySampleInfoHas(tblmain, wheremain)

				where := " DeletionStateCode=0 and BarCode = '" + basestation.BarCode + "'"
				have := s.QuerySampleInfoHas(tbldetail, where)
				has := s.QuerySampleInfoHas(tblfile, where)
				//当前位置为空
				whereempty := " Position = '" + basestation.Position +
					"' and EquipmentId=" + utils.ToStr(boxentity.EquipmentId) + " and ShelfId = " +
					utils.ToStr(boxentity.ShelfId) + " and BoxId = " + utils.ToStr(boxentity.Id)
				haveempty := s.QuerySampleInfoHas(tbldetail, whereempty)
				if !ishasmain && !has && !have && !haveempty {
					datamain.SampleCode = basestation.SampleCode
					datamain.SampleType = basestation.SampleType
					datamain.SampleTypeName = basestation.SampleTypeName
					datamain.AccCode = basestation.AccCode
					datamain.MCreateUserId = basestation.CreateUserId
					datamain.MCreateBy = basestation.CreateBy
					s.InsertEntityBytbl(tblmain, &datamain)

					datadetail.EquipmentId = boxentity.EquipmentId
					datadetail.ShelfId = boxentity.ShelfId
					datadetail.BoxId = boxentity.Id
					datadetail.SampleCode = basestation.SampleCode
					datadetail.BarCode = basestation.BarCode
					datadetail.IState = 1
					datadetail.Position = basestation.Position
					datadetail.IType = basestation.IType
					datadetail.Capacity = basestation.Capacity
					datadetail.InitCapacity = basestation.Capacity
					datadetail.Unit = basestation.Unit
					datadetail.FreezingNum = 0
					datadetail.CreateUserId = basestation.CreateUserId
					datadetail.CreateBy = basestation.CreateBy
					datadetail.Remark = basestation.Remark
					s.InsertEntityBytbl(tbldetail, &datadetail)
				}
			}
		}

		return err, 1
	} else {
		return nil, 0
	}

}

// 获取统计数据按样本类型分组
func (s *SamplesInfoService) GetTJBysampletype(acccode string, where string) (list []TJGroupModel) {
	tblmain := acccode + SamplesMaintbName
	tbldetail := acccode + SamplesDetailtbName
	donorsinfo := acccode + DonorstbName
	//	sql := `select * from (
	//		 select b.SampleTypeName Name,count(1) Num from  ` + tbldetail + ` a left join
	//		 ` + tblmain + ` b on a.SampleCode = b.SampleCode
	//		 where a.DeletionStateCode=0 group by b.SampleTypeName) m order by Num desc`
	sql := `select * from (
		 select b.SampleTypeName Name,count(1) Num from  ` + tbldetail + ` a 
		 left join ` + tblmain + ` b on a.SampleCode = b.SampleCode 
		 left join ` + donorsinfo + ` c on b.SourceId=c.Id
		 where ` + where + ` group by b.SampleTypeName) m order by Num desc`
	s.DBE.Sql(sql).Find(&list)
	return
}

// 获取统计数据按设备分组
func (s *SamplesInfoService) GetTJBydevice(acccode, where string) (list []TJGroupModel) {
	tbldetail := acccode + SamplesDetailtbName
	sql := `select Name,sum(num) Num  from (
		select a.Id,a.Name,case when b.Id is null then 0 else 1 end num from Equipment a left join
		` + tbldetail + ` b on a.Id=b.EquipmentId where ` + where + `
		) m group by Id`
	s.DBE.Sql(sql).Find(&list)
	return
}

//多中心管理分组统计
func (s *SamplesInfoService) GetTJBygroup(acccode string, where string) (list []GroupSamples) {
	tbldetail := acccode + SamplesDetailtbName
	tbgroup := acccode + GroupDetailName

	sql := `select * from (
		 select a.IState ,c.GroupName as Name,count(1) Num from  ` + tbldetail + ` a 
		 left join ` + tbgroup + ` c on c.SampleId = a.Id 
		 where ` + where + ` group by IState,Name) m order by IState desc`
	s.DBE.Sql(sql).Find(&list)
	return
}

func (s *SamplesInfoService) GetStationByCode(acccode string, where string) SamplesInfoList {
	if where == "" {
		where = "1=1"
	}
	tblmain := acccode + SamplesMaintbName
	tbldetail := acccode + SamplesDetailtbName
	sql := ` select a.*,b.*,d.XStation ShelfX,d.YStation ShelfY,d.Code ShelfCode,e.XStation BoxX,e.YStation BoxY,e.Code BoxCode,f.Id Eid,f.Name EName,f.Code ECode from ` + tbldetail + ` a left join ` + tblmain +
		` b on a.SampleCode = b.SampleCode  left join Shelf d on a.ShelfId=d.Id left join Box e on a.BoxId=e.Id left join Equipment f on a.EquipmentId=f.Id where ` + where + `
		 limit 1`
	var model SamplesInfoList
	utils.DBE.Sql(sql).Get(&model)

	return model
}

func (s *SamplesInfoService) QuerySampleBoxDataList(tablename, where string) []SampleBoxCount {
	sql := "select BoxId,Count(Id) CurrCount,100 AllCount from " + tablename + where + " group by BoxId "
	List := make([]SampleBoxCount, 0)
	utils.DBE.Sql(sql).Find(&List)
	return List
}

//查询日志数据列表
func (s *SamplesInfoService) GetContantById(tablename, id string) *SamplesLog {
	entity := new(SamplesLog)
	sql := "select * from " + tablename + " where Id=" + id + ""
	s.DBE.Sql(sql).Get(entity)
	return entity
}

//获取主表修改记录的下一条
func (s *SamplesInfoService) GetNextContantById(logtablename, id, tblmain, mid string, model interface{}) string {
	entity := new(SamplesLog)
	sql := "select * from " + logtablename + " where TableName='SamplesMain' and  MId = '" + mid + "' and Id > '" + id + "' order by Id limit 1"
	has, _ := s.DBE.Sql(sql).Get(entity)
	if !has {
		sql = "select * from " + tblmain + " where MId = '" + mid + "' limit 1"
		has, _ := s.DBE.Sql(sql).Get(model)
		if has {
			j, _ := json.Marshal(model)
			fmt.Println(j)
			return string(j)
		}
	}
	return entity.RecordData
}

//查询下一条数据列表
func (s *SamplesInfoService) GetNextContantShowInfoById(logtablename, id, recordId string, model interface{}, acccode string) string {
	entity := new(SamplesLog)
	sql := "select * from " + logtablename + " where RecordId = '" + recordId + "' and Id > '" + id + "' order by Id limit 1"
	has, _ := s.DBE.Sql(sql).Get(entity)
	if !has {
		tblmain := acccode + SamplesMaintbName
		tbldetail := acccode + SamplesDetailtbName
		sql := "select a.*,b.* from   " + tbldetail + " a left join " + tblmain +
			" b on a.SampleCode = b.SampleCode where   Id=" + recordId + "  and DeletionStateCode =0 limit 1"
		has, _ := s.DBE.Sql(sql).Get(model)
		if has {
			j, _ := json.Marshal(model)
			return string(j)
		}
	}
	return entity.RecordData
}

func NumtoUpChar(x int) string {
	return string(64 + x)
}

func (s *SamplesInfoService) GetPostiondescByPosId(eid, sid, bid int, posti string) string {
	var entity SamplesInDetailfoShow
	sql := `select c.Code ECode,b.XStation ShelfX,b.YStation ShelfY,a.XStation BoxX,a.YStation BoxY from Box a 
		left join Shelf b on a.ShelfId=b.Id
		left join Equipment c on a.EquipmentId=c.Id
		where a.Id=` + utils.ToStr(bid) + ` and a.ShelfId=` + utils.ToStr(sid) + ` and a.EquipmentId=` + utils.ToStr(eid)
	s.DBE.Sql(sql).Get(&entity)

	pos := strings.Split(posti, ";")
	position := posti
	if len(pos) == 2 {
		posyint, _ := utils.StrTo(pos[1]).Int()
		position = NumtoUpChar(posyint) + pos[0]
	}
	return fmt.Sprintf("%v-%v%v-%v%v-%v", entity.ECode, NumtoUpChar(entity.ShelfY), entity.ShelfX, NumtoUpChar(entity.BoxY), entity.BoxX, position)
}

func (s *SamplesInfoService) UpsertSearchTemplate(tablename, name, template string) error {
	var num MyInt_Id
	sql := "select count(1) Id from " + tablename
	sql = sql + " where Name='" + name + "' limit 1 "
	s.DBE.Sql(sql).Get(&num)
	total := num.Id
	var err error
	if total > 0 {
		// 更新
		sql := fmt.Sprintf("update %s set Template='%s' where name='%s'", tablename, template, name)
		_, err = s.DBE.Exec(sql)
	} else {
		//插入
		//sql := "insert into " + tablename + "(Name, Template) values('" + name + "','" + template + "')"
		//_, err = s.DBE.Exec(sql)
		_, err = s.DBE.Table(tablename).Cols("Id", "Name", "Template").Insert(&SamplesSearchTemplate{
			Name:     name,
			Template: template,
		})
	}

	return err
}

func (s *SamplesInfoService) GetAllSearchTemplate(tablename string) ([]*SamplesSearchTemplate, error) {
	templates := make([]*SamplesSearchTemplate, 0)
	err := s.DBE.Table(tablename).Find(&templates)

	return templates, err
}

func (s *SamplesInfoService) GetSearchTemplateByName(tablename, name string) (*SamplesSearchTemplate, error) {
	var template SamplesSearchTemplate
	_, err := s.DBE.Table(tablename).Where("Name='" + name + "'").Get(&template)

	return &template, err
}

func (s *SamplesInfoService) DeleteSearchTemplate(tablename, name string) error {
	sql := fmt.Sprintf("delete from %s where name='%s'", tablename, name)
	_, err := s.DBE.Exec(sql)

	return err
}

func (s *SamplesInfoService) CreateTemplateTable(tablename string) error {
	sql := fmt.Sprintf(`create table %s (Id int(10) NOT NULL AUTO_INCREMENT, Name varchar(25), Template Text, PRIMARY KEY (Id), UNIQUE (Name))`, tablename)
	_, err := s.DBE.Exec(sql)

	return err
}