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 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 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) 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 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 || len(row.Cells) < 8 { //小于必填项的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 { fmt.Println("mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm", field) 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" { fmt.Println("dvvvvvvvbv ddd", field) basestation.SampleTypeName = cell.String() where2 := " AccCode='" + acccode + "' and Name='" + cell.String() + "'" sampletype_data = svc.GetSampleType(where2) fmt.Println("ddddddddddddddddddddddddd", sampletype_data) 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 } 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 }