package samplesfileinfo

import (
	"strconv"
	"strings"

	. "dashoo.cn/backend/api/models"
	. "dashoo.cn/backend/api/mydb"
	"dashoo.cn/utils"
	. "dashoo.cn/utils/db"
	"github.com/go-xorm/xorm"
)

type SamplesFileInfoService struct {
	MyServiceBase
}

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

func (s *SamplesFileInfoService) GetPagingEntitiesWithOrderSearch(acccode string, pageIndex, itemsPerPage int64, order, where string) (int64, []SamplesFileInfoList) {
	var err error
	var total int64
	if order != "" {
		order = " order by " + order
	}
	//获取总记录数
	if where == "" {
		where = "1=1"
	}
	tblmain := acccode + SamplesMaintbName
	tbldetail := acccode + SamplesDetailFiletbName
	//当是蜜蜂所用户时，取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 " + groupdetail + " d on d.SampleId = a.Id where " + where
	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  
	left join ` + groupdetail + ` d on d.SampleId = a.Id  
	where ` + where + order + `
		 limit ` + utils.ToStr((pageIndex-1)*itemsPerPage) + "," + utils.ToStr(itemsPerPage) + ""
	List := make([]SamplesFileInfoList, 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 *SamplesFileInfoService) GetPagingEntitiesWithTb(acccode string, pageIndex, itemsPerPage int64, tbldonor, order, where string) (int64, []AnimalSamplesFileInfoList) {
	var err error
	var total int64
	if order != "" {
		order = " order by " + order
	}
	//获取总记录数
	if where == "" {
		where = "1=1"
	}
	tblmain := acccode + SamplesMaintbName
	tbldetail := acccode + SamplesDetailFiletbName
	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 " + groupdetail + " d on d.SampleId = a.Id where " + where
	var sql string

	sql = ` select a.*,b.*,c.Genus,concat(c.ProvinceName,c.CityName,c.StreetName,c.Address) as AddressName,c.SurveyDate,d.GroupId,d.GroupName from ` + tbldetail + ` a 
	left join ` + tblmain + ` b on a.SampleCode = b.SampleCode  
	left join ` + tbldonor + ` c on b.SourceId = c.Id  
	left join ` + groupdetail + ` d on d.SampleId = a.Id  
	where ` + where + order + `
		 limit ` + utils.ToStr((pageIndex-1)*itemsPerPage) + "," + utils.ToStr(itemsPerPage) + ""
	List := make([]AnimalSamplesFileInfoList, 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 *SamplesFileInfoService) QuerySampleFileEntity(acccode string, where string) (entity SamplesFileShow) {
	tblmain := acccode + SamplesMaintbName
	tblfiledetail := acccode + SamplesDetailFiletbName
	sql := "select a.*,b.* from   " + tblfiledetail + " a left join " + tblmain +
		" b on a.SampleCode = b.SampleCode where " + where
	s.DBE.Sql(sql).Get(&entity)
	return
}
//根据barcode 从sampleFileDetail中查询归档信息
func(s * SamplesFileInfoService) QuerySampleFileByBarCode(acccode string,barcode string)(entity SamplesFileDetail){
	tableName:=acccode+SamplesDetailFiletbName
	sql:="select a.* from "+tableName+" a where  a.barcode= '"+barcode+"' and a.deletionstatecode=0 ORDER BY createon desc"
	s.DBE.SQL(sql).Get(&entity)
	return
}

//获取数量
func (s *SamplesFileInfoService) GetSampleFileCount(acccode, where string) int {
	tblmain := acccode + SamplesMaintbName
	tblfiledetail := acccode + SamplesDetailFiletbName
	var num MyInt_Id
	sql := "select count(Id) Id from " + tblfiledetail + " a left join " + tblmain +
		" b on a.SampleCode = b.SampleCode "
	if where != "" {
		sql = sql + " where " + where + " limit 1 "
	}
	s.DBE.Sql(sql).Get(&num)
	total := num.Id
	return total
}

//已归档样本条码批量打印
func (s *SamplesFileInfoService) QuerySampleList(acccode string, where string) []SamplesFileShow {
	tblmain := acccode + SamplesMaintbName
	tbldetail := acccode + SamplesDetailFiletbName
	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([]SamplesFileShow, 0)
	utils.DBE.Sql(sql).Find(&List)
	return List
}

//已归档样本条码批量打印--蜜蜂所
func (s *SamplesFileInfoService) QueryAnimalSampleList(acccode string, where string) []AnimalSamplesFileShow {
	tblmain := acccode + SamplesMaintbName
	tbldetail := acccode + SamplesDetailFiletbName
	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([]AnimalSamplesFileShow, 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
}

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

	return &template, err
}