package samplessource

import (
	"fmt"
	"github.com/tealeg/xlsx"
	"os"
	"time"

	//"fmt"
	"strconv"

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

type SamplesSourceService struct {
	MyServiceBase
}

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

//获取扩展字段列
func (s *SamplesSourceService) QueryZBackList(acccode string) []String_Id {
	List := make([]String_Id, 0)
	sql := "select distinct FieldName Id from DonorsNoteItem where FieldNo>10  and AccCode ='" + acccode + "' order by Id "
	utils.DBE.Sql(sql).Find(&List)
	return List
}

//判断委托单号是否存在，返回总数
func (s *SamplesSourceService) GetInnerNo(maintable string, where string) int64 {
	var sql string
	var total int64
	sql = `select count(*) from ` + maintable + ` where ` + where
	resultsSlice, err := s.DBE.Query(sql)
	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
}

//获取样本来源信息用于导出
func (s *SamplesSourceService) GetSampleSourceInfo(donors, testmain, donorsdetail, where string) []DonorsInfoWithDetail {
	if where == "" {
		where = " 1=1 "
	}
	var sql string
	sql = `select * from ( select a.AccCode,a.Id,a.IdCard,a.InnerNo,a.Name,a.Sex,a.CompanyName,a.Duty,a.Birthday,a.Age,a.Telephone,a.Email,a.Nation,a.HomeAddress,a.IllnessName,a.Remark, b.InspectionNum,  group_concat(c.PathologicalNum) as PathologicalNum 
	from ` + donors + ` a 
	left join ` + testmain + ` b on a.Id=b.SourceId
	left join ` + donorsdetail + ` c on c.ParentId=a.Id
	where ` + where + ` group by a.Id,a.IdCard,a.Name,b.InspectionNum) as m `
	List := make([]DonorsInfoWithDetail, 0)
	utils.DBE.Sql(sql).Find(&List)
	return List
}

//样本源导入--动物
func (s *SamplesSourceService) InsertExcelAnimal(excelpath, tablename, accode, createby string, createuserid int) (err error, warn string) {
	//svcdnote := extend.GetExtendService(utils.DBE)
	//notecount := svcdnote.GetNoteCount(accode) //获取扩展项目count
	//wherenote := " AccCode='" + accode + "'"
	//notelist := svcdnote.GetDNoteItemList(wherenote) //获取扩展项目List
	dir, _ := os.Getwd()
	excelpath = dir + excelpath
	xlFile, _ := xlsx.OpenFile(excelpath)
	var warn1 string
	fmt.Println("ssdsdsd",xlFile.Sheets)
	for sheetindex, sheet := range xlFile.Sheets { //循环页数
		sheetrows := len(xlFile.Sheets[sheetindex].Rows)
		for rowindex, row := range sheet.Rows { //循环行数
			notefieldstr := ""
			notevaluestr := ""
			if rowindex == 0 {
				continue
			}
			if row.Cells == nil || len(row.Cells) < 2 { //少于两列退出姓名不能为空
				break
			}
			basestation := new(AnimalInfo)
			for cellindex, cell := range row.Cells { //循环列数
				if cell.String() != "" {
					if cellindex == 0 {
						basestation.ProvinceName = cell.String()
					} else if cellindex == 1 {
						if cell.String() == "" {
							basestation.Name = "其他"
							basestation.Genus = "其他"
						} else {
							basestation.Name = cell.String()
							basestation.Genus = cell.String()
						}
					} else if cellindex == 2 {
						basestation.CityName = cell.String()
					} else if cellindex == 3 {
						basestation.StreetName = cell.String()
					} else if cellindex == 4 {
						basestation.Address = cell.String()
					} else if cellindex == 5 {
						aa, _ := utils.StrTo(cell.Value).Float64()
						basestation.SurveyDate = xlsx.TimeFromExcelTime(aa, false)
					} else if cellindex == 6 {
						basestation.InnerNo = cell.String()
					} else if cellindex == 7 {
						basestation.Amount, _ = utils.StrTo(cell.String()).Int()
					} else if cellindex == 8 {
						basestation.Longitude = cell.String()
					} else if cellindex == 9 {
						basestation.Latitude = cell.String()
					} else if cellindex == 10 {
						basestation.Altitude = cell.String()
					} else if cellindex == 11 {
						basestation.AreaCode = cell.String()
					} else if cellindex == 12 {
						basestation.Province = cell.String()
					} else if cellindex == 13 {
						basestation.City = cell.String()
					} else if cellindex == 14 {
						basestation.Street = cell.String()
					} else if cellindex == 15 {
						basestation.ZBack17 = cell.String()
					} else if cellindex == 16 {
						basestation.ZBack11 = cell.String()
					}
				}
			}

			basestation.AccCode = accode
			basestation.Unit = "群"
			basestation.ProjectId = 0
			basestation.ProjectName = "技术体系"
			basestation.CreateOn = time.Now()
			basestation.CreateBy = createby
			basestation.CreateUserId = createuserid

			sqlfield := "AccCode,Genus,InnerNo,Name,Amount,Unit,Province,ProvinceName,City,CityName,"
			sqlfield = sqlfield + "Street,StreetName,Address,AreaCode,Longitude,Latitude,Altitude,SurveyDate"
			sqlfield = sqlfield + "ProjectId,ProjectName,"
			sqlfield = sqlfield + "CreateUserId,CreateBy,CreateOn"
			sqlfield = sqlfield + notefieldstr

			sqlvalue := "'" + basestation.AccCode + "','" + basestation.Genus + "','" + basestation.InnerNo + "','" + basestation.Name + "'," + utils.ToStr(basestation.Amount) + ",'" + basestation.Unit + "',"
			sqlvalue = sqlvalue + "'" + basestation.Province + "','" + basestation.ProvinceName + "','" + basestation.City + "','" + basestation.CityName + "','"+ basestation.Street + "',"
			sqlvalue = sqlvalue + "'" + basestation.StreetName + "','" + basestation.Address + "','" + basestation.AreaCode + "','" + basestation.Longitude + "','" + basestation.Latitude + "',"
			sqlvalue = sqlvalue + "'" + basestation.Altitude + "','" + basestation.SurveyDate.Format("2006-1-2 15:4:5") + "'," + utils.ToStr(basestation.ProjectId) + ",'" + basestation.ProjectName + "',"
			sqlvalue = sqlvalue + "" + utils.ToStr(basestation.CreateUserId) + ",'" + basestation.CreateBy + "','" + "now(),now()'"
			sqlvalue = sqlvalue + notevaluestr
			s.AddLine(tablename, sqlfield, sqlvalue)

			if rowindex == (sheetrows) {
				s.AddLine(tablename, sqlfield, sqlvalue)
			}
		}
	}
	return err, warn1
}

//数据写入数据库
func (s *SamplesSourceService) AddLine(tableName, fieldName, fieldValue string) error {
	sql := "insert into " + tableName + "(" + fieldName + ") values(" + fieldValue + ")"
	fmt.Println(sql)
	_, err := s.DBE.Exec(sql)
	return err
}